Script to Convert AP Invoices to Approved Status
Script to Convert AP Invoices in Approved Status
PROCEDURE approve_converted_invoices (
errorbuf VARCHAR2,
retcode NUMBER,
ip_op_unit VARCHAR2
)
IS
CURSOR app_inv_cur
IS
SELECT DISTINCT aia.invoice_num inv_number, stg.ls_op_unit op_unit
FROM ap_invoices_all aia, xxx.xxx_ap_invoices_conv_stg stg
WHERE 1 = 1
AND aia.invoice_num = stg.ls_sup_inv_num
AND aia.invoice_num IN (SELECT ls_sup_inv_num
FROM xxx.xxx_ap_invoices_conv_stg
WHERE status_stg = 'VAL')
AND aia.wfapproval_status = 'REQUIRED'
AND stg.ls_op_unit = ip_op_unit;
--'VLAIDATED');
l_sub_request_id NUMBER := NULL;
-- l_user_id NUMBER := 1581;
--apps.fnd_global.user_id; --
l_resp_id NUMBER := NULL;
l_app_id NUMBER := NULL;
v_request_completed BOOLEAN;
v_request_id NUMBER;
v_phase VARCHAR2 (80) := NULL;
v_status VARCHAR2 (80) := NULL;
v_dev_phase VARCHAR2 (30) := NULL;
v_dev_status VARCHAR2 (30) := NULL;
v_message VARCHAR2 (240);
BEGIN
FOR app_inv_rec IN app_inv_cur
LOOP
BEGIN
SELECT DISTINCT fr.responsibility_id, frx.application_id
INTO l_resp_id, l_app_id
FROM apps.fnd_responsibility frx,
apps.fnd_responsibility_tl fr
WHERE fr.responsibility_id = frx.responsibility_id
AND UPPER (fr.responsibility_name) LIKE
UPPER (DECODE (app_inv_rec.op_unit,
'Payables Manager'
)
);
-- fnd_client_info.set_org_context (85);
IF l_user_id IS NOT NULL
AND l_resp_id IS NOT NULL
AND l_app_id IS NOT NULL
THEN
DBMS_OUTPUT.put_line ('aa');
apps.fnd_global.apps_initialize (l_user_id, l_resp_id, l_app_id);
l_sub_request_id :=
apps.fnd_request.submit_request ('SQLAP',
'APXIAWRE',
'Invoice Approval Workflow',
NULL,
FALSE,
NULL,
app_inv_rec.inv_number,
NULL,
NULL
);
COMMIT;
LOOP
v_request_completed :=
apps.fnd_concurrent.wait_for_request
(l_sub_request_id -- Request ID
,
20
-- Time Interval
,
0
-- Total Time to wait
,
v_phase
-- Phase displyed on screen
,
v_status -- Status displayed on screen
,
v_dev_phase
-- Phase available for developer
,
v_dev_status -- Status available for developer
,
v_message
-- Execution Message
);
EXIT WHEN v_request_completed;
END LOOP;
END IF;
DBMS_OUTPUT.put_line (l_sub_request_id);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ( 'Failed in submiting the Program: '
|| SQLERRM
);
END;
END LOOP;
END approve_converted_invoices;
errorbuf VARCHAR2,
retcode NUMBER,
ip_op_unit VARCHAR2
)
IS
CURSOR app_inv_cur
IS
SELECT DISTINCT aia.invoice_num inv_number, stg.ls_op_unit op_unit
FROM ap_invoices_all aia, xxx.xxx_ap_invoices_conv_stg stg
WHERE 1 = 1
AND aia.invoice_num = stg.ls_sup_inv_num
AND aia.invoice_num IN (SELECT ls_sup_inv_num
FROM xxx.xxx_ap_invoices_conv_stg
WHERE status_stg = 'VAL')
AND aia.wfapproval_status = 'REQUIRED'
AND stg.ls_op_unit = ip_op_unit;
--'VLAIDATED');
l_sub_request_id NUMBER := NULL;
-- l_user_id NUMBER := 1581;
--apps.fnd_global.user_id; --
l_resp_id NUMBER := NULL;
l_app_id NUMBER := NULL;
v_request_completed BOOLEAN;
v_request_id NUMBER;
v_phase VARCHAR2 (80) := NULL;
v_status VARCHAR2 (80) := NULL;
v_dev_phase VARCHAR2 (30) := NULL;
v_dev_status VARCHAR2 (30) := NULL;
v_message VARCHAR2 (240);
BEGIN
FOR app_inv_rec IN app_inv_cur
LOOP
BEGIN
SELECT DISTINCT fr.responsibility_id, frx.application_id
INTO l_resp_id, l_app_id
FROM apps.fnd_responsibility frx,
apps.fnd_responsibility_tl fr
WHERE fr.responsibility_id = frx.responsibility_id
AND UPPER (fr.responsibility_name) LIKE
UPPER (DECODE (app_inv_rec.op_unit,
'Payables Manager'
)
);
-- fnd_client_info.set_org_context (85);
IF l_user_id IS NOT NULL
AND l_resp_id IS NOT NULL
AND l_app_id IS NOT NULL
THEN
DBMS_OUTPUT.put_line ('aa');
apps.fnd_global.apps_initialize (l_user_id, l_resp_id, l_app_id);
l_sub_request_id :=
apps.fnd_request.submit_request ('SQLAP',
'APXIAWRE',
'Invoice Approval Workflow',
NULL,
FALSE,
NULL,
app_inv_rec.inv_number,
NULL,
NULL
);
COMMIT;
LOOP
v_request_completed :=
apps.fnd_concurrent.wait_for_request
(l_sub_request_id -- Request ID
,
20
-- Time Interval
,
0
-- Total Time to wait
,
v_phase
-- Phase displyed on screen
,
v_status -- Status displayed on screen
,
v_dev_phase
-- Phase available for developer
,
v_dev_status -- Status available for developer
,
v_message
-- Execution Message
);
EXIT WHEN v_request_completed;
END LOOP;
END IF;
DBMS_OUTPUT.put_line (l_sub_request_id);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ( 'Failed in submiting the Program: '
|| SQLERRM
);
END;
END LOOP;
END approve_converted_invoices;
No comments:
Post a Comment