PROCEDURE main (
errbuff OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_file_name IN VARCHAR2
)
IS
BEGIN
start_process (p_file_name);
— write_output;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
PROCEDURE start_process (p_filename IN VARCHAR2)
IS
l_query VARCHAR2 (500);
BEGIN
DEBUG (‘Inserting Data to External Table ‘);
l_query :=
‘alter table XXXX_PO_ACCRUAL_WRITE_OFF_TBL location(‘
|| ””
|| p_filename
|| ””
|| ‘)’;
apps.fnd_file.put_line (apps.fnd_file.LOG, l_query);
EXECUTE IMMEDIATE l_query;
INSERT INTO apps.xxxx_po_accrual_write_off_stg
(liability_account, trans_date, vendor_name, po_number, item,
accounted_amount, entered_amount, ap_balance, po_balance,
total_balance, curr_code, ap_invoice_number, ap_invoice_lines,
inventory_org, receipt_number, po_release, po_line, po_ship,
po_distribution, transaction_qty, transaction_uom,
writeoff_bal, — NUMBER in STG
trans_type, SOURCE, destination, age_in_days,
process_flag, status, error_message, created_by,
creation_date, last_updated_date, last_updated_by, request_id)
SELECT “LIABILITY_ACCOUNT”, “TRANS_DATE”, “VENDOR_NAME”, “PO_NUMBER”,
“ITEM”, “ACCOUNTED_AMOUNT”, “ENTERED_AMOUNT”, “AP_BALANCE”,
“PO_BALANCE”, “TOTAL_BALANCE”, “CURR_CODE”, “AP_INVOICE_NUMBER”,
“AP_INVOICE_LINES”, “INVENTORY_ORG”, “RECEIPT_NUMBER”,
“PO_RELEASE”, “PO_LINE”, “PO_SHIP”, “PO_DISTRIBUTION”,
“TRANSACTION_QTY”, “TRANSACTION_UOM”,
/* SAFE Conversion for WRITEOFF_BAL */
TO_NUMBER (REGEXP_REPLACE (“WRITEOFF_BAL”, ‘[^0-9\.\-]’, ”)),
“TRANS_TYPE”, “SOURCE”, “DESTINATION”, “AGE_IN_DAYS”,
“PROCESS_FLAG”, “STATUS”, “ERROR_MESSAGE”, “CREATED_BY”,
“CREATION_DATE”, “LAST_UPDATED_DATE”, “LAST_UPDATED_BY”,
“REQUEST_ID”
FROM xxxx_po_accrual_write_off_tbl
WHERE NVL (process_flag, ‘N’) ‘Y’;
–AND trans_type = ‘Write Off’;
process_po;
END;
PROCEDURE process_po
IS
CURSOR cur_process_po
IS
SELECT *
FROM xxxx_po_accrual_write_off_stg
WHERE NVL (process_flag, ‘N’) ‘Y’ AND trans_type = ‘Write Off’;
l_po_header_id NUMBER;
l_po_line_id VARCHAR2 (50);
l_po_distribution_id VARCHAR2 (50);
l_return_status VARCHAR2 (1000);
l_po_shipment_id NUMBER;
l_msg_data VARCHAR2 (4000);
c_reason_id NUMBER := 161;
l_msg_count NUMBER;
x_action CONSTANT VARCHAR2 (20) := ‘FINALLY CLOSE’;
x_calling_mode CONSTANT VARCHAR2 (2) := ‘PO’;
x_conc_flag CONSTANT VARCHAR2 (1) := ‘N’;
x_return_code_h VARCHAR2 (100);
x_auto_close CONSTANT VARCHAR2 (1) := ‘N’;
x_origin_doc_id NUMBER;
x_returned BOOLEAN := NULL;
l_stmt_num NUMBER;
l_rows NUMBER;
l_ent_sum NUMBER;
l_off_id NUMBER;
l_erv_id NUMBER;
l_wo_cc VARCHAR2 (30);
l_wo_ct VARCHAR2 (30);
l_wo_cr NUMBER;
l_wo_cd DATE;
ln_count NUMBER;
l_err_msg1 VARCHAR2 (4000);
l_err_msg2 VARCHAR2 (4000);
l_update_sum NUMBER;
l_update_rows NUMBER;
l_err_num1 NUMBER;
l_err_code1 NUMBER;
l_insert_count NUMBER;
l_err_num2 NUMBER;
l_err_code2 NUMBER;
po_close BOOLEAN;
l_close_return_code VARCHAR2 (100);
l_msg VARCHAR2 (1000);
ln_amount NUMBER;
l_stmt VARCHAR2 (1000);
ln_description VARCHAR2 (100);
BEGIN
mo_global.set_policy_context (‘S’, ‘3’);
fnd_global.apps_initialize (gn_user_id, gn_resp_id, gn_resp_apid);
FOR rec IN cur_process_po
LOOP
DEBUG ( ‘Processing PO: ‘
|| rec.po_number
|| ‘ WRITEOFF: ‘
|| rec.writeoff_bal
);
BEGIN
SELECT pol.po_line_id, pod.po_distribution_id, poh.po_header_id
INTO l_po_line_id, l_po_distribution_id, l_po_header_id
FROM apps.po_lines_all pol,
apps.po_headers_all poh,
apps.po_distributions_all pod
WHERE poh.po_header_id = pol.po_header_id
AND pod.po_header_id = pol.po_header_id
AND pod.po_line_id = pol.po_line_id
AND poh.segment1 = rec.po_number
AND poh.org_id = 3
AND EXISTS (
SELECT 1
FROM apps.cst_reconciliation_summary
WHERE po_distribution_id = pod.po_distribution_id
— AND write_off_balance != 0
AND ROWNUM = 1);
DEBUG (‘po_line_id ‘ || l_po_line_id || ‘ ‘ || l_po_distribution_id);
EXCEPTION
WHEN OTHERS
THEN
l_po_line_id := NULL;
END;
———————–Update Custom table with po_distribution_details
BEGIN
UPDATE xxxx_po_accrual_write_off_stg
SET po_distribution = l_po_distribution_id,
po_line = l_po_line_id,
request_id = gn_request_id,
created_by = gn_user_id,
creation_date = SYSDATE,
last_updated_by = gn_user_id,
last_updated_date = SYSDATE
WHERE po_number = rec.po_number;
EXCEPTION
WHEN OTHERS
THEN
DEBUG (‘in exeption’);
DEBUG (SQLERRM);
END;
COMMIT;
BEGIN
SELECT COUNT (*)
INTO ln_count
FROM apps.cst_reconciliation_summary
WHERE po_distribution_id = l_po_distribution_id;
EXCEPTION
WHEN OTHERS
THEN
ln_count := 0;
DEBUG (‘Count ‘ || ln_count);
END;
DEBUG (‘Count’ || ln_count);
IF l_po_distribution_id IS NULL
THEN
DEBUG (‘No Distribution ID ‘ || rec.po_number);
UPDATE xxxx_po_accrual_write_off_stg
SET process_flag = ‘N’,
status = ‘ERROR’,
error_message =
‘NO PO DISTRIBUTION FOUND for PO: ‘
|| NVL (rec.po_number, ‘NULL’)
WHERE po_number = rec.po_number;
ELSE
DEBUG (‘Check the Limit’);
————————Check the limit against Cost Center
BEGIN
SELECT TRIM (description)
INTO ln_description
FROM apps.fnd_lookup_values
WHERE lookup_type = ‘XXXX_PO_WRITEOFF_SETUP’
AND SYSDATE BETWEEN NVL (start_date_active, SYSDATE)
AND NVL (end_date_active, SYSDATE)
AND meaning = rec.liability_account;
EXCEPTION
WHEN OTHERS
THEN
ln_description := 0;
END;
IF ln_description IS NULL
THEN
ln_amount := 0;
ELSE
DBMS_OUTPUT.put_line (‘inside if’);
ln_amount := TO_NUMBER (ln_description);
END IF;
DEBUG (rec.liability_account || ‘ ‘ || ln_amount || rec.writeoff_bal);
IF TO_NUMBER (rec.writeoff_bal) >= ln_amount
THEN
DEBUG (‘Write off Balance is greater than the Limit Defined’);
UPDATE xxxx_po_accrual_write_off_stg
SET process_flag = ‘N’,
status = ‘ERROR’,
error_message =
error_message
|| ‘Write off Balance is greater than the Limit Defined’
WHERE po_number = rec.po_number;
ELSIF TO_NUMBER (rec.writeoff_bal) l_stmt,
p_prog => 0,
p_ou_id => 3,
x_out => l_update_sum,
x_tot => l_update_rows,
x_err_num => l_err_num1,
x_err_code => l_err_code1,
x_err_msg => l_err_msg1
);
EXCEPTION
WHEN OTHERS
THEN
l_err_msg1 := ‘update_all failed: ‘ || SQLERRM;
l_update_rows := 0;
END;
DBMS_OUTPUT.put_line (l_err_msg1);
IF l_update_rows IS NULL OR l_update_rows = 0
THEN
UPDATE xxxx_po_accrual_write_off_stg
SET process_flag = ‘N’,
status = ‘ERROR’,
error_message =
‘No matching cst_reconciliation_summary rows found for distribution: ‘
|| TO_CHAR (l_po_distribution_id)
WHERE po_number = rec.po_number;
COMMIT;
CONTINUE;
END IF;
ELSE
DBMS_OUTPUT.put_line (‘Nothing to process’);
DBMS_OUTPUT.put_line (ln_amount);
END IF;
DEBUG (‘ Processing Write off with Reason ID’);
BEGIN
cst_accrual_rec_pvt.insert_appo_data_all
(p_wo_date => SYSDATE,
p_rea_id => c_reason_id,
p_comments => ‘AUTO WRITEOFF via batch’,
p_sob_id => 1,
p_ou_id => 3,
x_count => l_insert_count,
x_err_num => l_err_num2,
x_err_code => l_err_code2,
x_err_msg => l_err_msg2
);
EXCEPTION
WHEN OTHERS
THEN
l_err_msg2 := ‘insert_appo_data_all failed: ‘ || SQLERRM;
l_insert_count := -1;
END;
IF l_insert_count > 0
THEN
DEBUG (‘Closing PO ||l_po_header_id’);
BEGIN
po_close :=
po_actions.close_po (p_docid => l_po_header_id,
p_doctyp => ‘PO’,
p_docsubtyp => ‘STANDARD’,
p_lineid => NULL,
p_shipid => NULL,
p_action => ‘CLOSE’,
p_reason => ‘Close PO’,
p_calling_mode => x_calling_mode,
p_conc_flag => x_conc_flag,
p_return_code => x_return_code_h,
p_auto_close => x_auto_close,
p_action_date => SYSDATE,
p_origin_doc_id => NULL
);
EXCEPTION
WHEN OTHERS
THEN
po_close := FALSE;
l_msg := ‘PO close failed: ‘ || SQLERRM;
END;
UPDATE xxxx_po_accrual_write_off_stg
SET process_flag = ‘Y’,
status = ‘PROCESSED’,
error_message =
CASE
WHEN l_err_msg2 IS NOT NULL
THEN l_err_msg2
WHEN l_err_msg1 IS NOT NULL
THEN l_err_msg1
ELSE NULL
END
WHERE po_number = rec.po_number;
ELSE
UPDATE xxxx_po_accrual_write_off_stg
SET process_flag = ‘N’,
status = ‘ERROR’,
error_message =
‘Write-off not created. update_all rows: ‘
|| NVL (TO_CHAR (l_update_rows), ‘0’)
|| ‘ insert_count: ‘
|| NVL (TO_CHAR (l_insert_count), ‘NULL’)
|| ‘ err_from_update_all: ‘
|| NVL (l_err_msg1, ‘-‘)
|| ‘ err_from_insert: ‘
|| NVL (l_err_msg2, ‘-‘)
WHERE po_number = rec.po_number;
END IF;
END IF;
COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END process_po;
