create or replace PROCEDURE XX_VALIDATE_DEBIT_INVOICE(errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_batch_run_id IN NUMBER,
p_org_id IN NUMBER,
P_stg_id IN NUMBER,
p_debit_memo_num IN VARCHAR2)
IS
L_ORG_ID NUMBER:=p_org_id;
L_SOB_ID NUMBER;
L_USER_ID NUMBER;
l_resp_id NUMBER;
l_appl_id NUMBER;
l_request_id NUMBER;
l_header_id NUMBER;
l_invoice_status VARCHAR2(50);
lc_phase VARCHAR2(50);
lc_status VARCHAR2(50);
lc_dev_phase VARCHAR2(50);
lc_dev_status VARCHAR2(50);
lc_message VARCHAR2(50);
l_req_return_status BOOLEAN;
CURSOR C1 IS
SELECT aia.invoice_id
FROM ap_invoices_all AIA,
ttk.xx_rma_stg stg
WHERE AIA.ORG_ID=L_ORG_ID
AND AIA.INVOICE_NUM= SUBSTR (P_DEBIT_MEMO_NUM,
– (INSTR (P_DEBIT_MEMO_NUM, ‘,’, 1) – 1))
AND STG.STG_ID=P_STG_ID
AND STG.BATCH_RUN_ID=P_BATCH_RUN_ID;
BEGIN
FND_CLIENT_INFO.SET_ORG_CONTEXT(L_ORG_ID);
BEGIN
SELECT USER_ID
INTO L_USER_ID
FROM FND_USER
WHERE USER_NAME = ‘ARUN’;
EXCEPTION
WHEN OTHERS THEN
L_USER_ID:=0;
END;
BEGIN
SELECT responsibility_id,
application_id
INTO l_resp_id,
l_appl_id
FROM fnd_responsibility_vl
WHERE responsibility_name = ‘India Local Payables’
AND NVL(TRUNC(end_date), TRUNC(sysdate)) >= TRUNC(sysdate);
END;
FND_GLOBAL.APPS_INITIALIZE(L_USER_ID, L_RESP_ID, L_APPL_ID);
FOR C1_REC IN C1
LOOP
l_invoice_status:=NULL;
BEGIN
SELECT AP_INVOICES_PKG.GET_APPROVAL_STATUS
(
I.INVOICE_ID
,I.INVOICE_AMOUNT
,I.PAYMENT_STATUS_FLAG
,I.INVOICE_TYPE_LOOKUP_CODE
)
INTO l_invoice_status
FROM AP_INVOICES_all I
WHERE I.invoice_id = c1_rec.invoice_id;
EXCEPTION
WHEN OTHERS THEN
l_invoice_status:=NULL;
END;
IF l_invoice_status != ‘APPROVED’ THEN
l_request_id := fnd_request.submit_request (
application => ‘SQLAP’,
program => ‘APPRVL’,
description => ‘Invoice Validation’,
start_time => sysdate,
sub_request => FALSE,
argument1 => NULL,
argument2 => ‘All’,
argument3 => l_header_id,
argument4 => NULL,
argument5 => NULL,
argument6 => NULL,
argument7 => NULL,
argument8 => c1_rec.invoice_id,
argument9 => NULL,
argument10 => ‘N’,
argument11 => 1000,
argument12 => 1,
argument13 => ‘N’
);
IF l_request_id = 0 THEN
dbms_output.put_line(‘Concurrent request failed to submit’);
UPDATE xx_rma_stg stg
SET
AP_inv_request=NULL,
error_message=’ERROR -AP INVOICE VALIDATED’
WHERE STG.STG_ID=P_STG_ID
AND STG.BATCH_RUN_ID=P_BATCH_RUN_ID;
COMMIT;
ELSE
dbms_output.put_line(‘Successfully Submitted the Concurrent Request: ‘ || l_request_id);
LOOP
–To make process execution to wait for 1st program to complete
l_req_return_status :=
fnd_concurrent.wait_for_request (request_id => l_request_id
,INTERVAL => 2 –interval Number of seconds to wait between checks
,max_wait => 15 –Maximum number of seconds to wait for the request completion
— out arguments
,phase => lc_phase
,STATUS => lc_status
,dev_phase => lc_dev_phase
,dev_status => lc_dev_status
,message => lc_message
);
EXIT
WHEN UPPER (lc_phase) = ‘COMPLETED’ OR UPPER (lc_status) IN (‘CANCELLED’, ‘ERROR’, ‘TERMINATED’);
UPDATE xx_rma_stg stg
SET AP_inv_request=l_request_id,
status=’AP INVOICE VALIDATED’
WHERE STG.STG_ID=P_STG_ID
AND STG.BATCH_RUN_ID=P_BATCH_RUN_ID;
COMMIT;
dbms_output.put_line(‘Request ID: ‘||l_request_id);
dbms_output.put_line(‘invoice_id: ‘||c1_rec.invoice_id);
END LOOP;
END IF;
END IF;
END LOOP; —MAIN lOOP
— COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘Unexpected error-‘||substr(Sqlerrm,1,200));
END;
