DECLARE
p_invoice_id ap_invoices_all.invoice_id % TYPE := -1; /* Change invoice id to correct value */
p_inv_batch ap_batches_all.batch_name % TYPE := NULL;
p_hold_code ap_holds_all.hold_lookup_code % TYPE := 'Encumbrance Acctg Fail';
v_hold_cnt NUMBER;
v_apprvl_sts ap_invoices.wfapproval_status % TYPE;
v_release_reason ap_lookup_codes.description % TYPE;
v_release_code ap_lookup_codes.lookup_code % TYPE := 'Encumbrance Acctg Ok';
CURSOR c_inv_on_hold IS
SELECT hld.hold_lookup_code
, hld.invoice_id
FROM ap_holds hld
, ap_invoices inv
, ap_batches_all btch
WHERE hld.invoice_id = inv.invoice_id
AND btch.batch_id = inv.batch_id
AND (p_invoice_id IS NULL or p_invoice_id = inv.invoice_id )
AND (p_inv_batch IS NULL or p_inv_batch = btch.batch_name )
AND (p_hold_code IS NULL or p_hold_code = hld.hold_lookup_code)
;
BEGIN
mo_global.set_policy_context('S',':ORG_ID');
SELECT description
INTO v_release_reason
FROM ap_lookup_codes
WHERE lookup_type = 'HOLD CODE'
AND lookup_code = v_release_code
AND enabled_flag = 'Y'
AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (start_date_active, SYSDATE - 1))
AND TRUNC (NVL (inactive_date , SYSDATE + 1))
;
FOR l_rec in c_inv_on_hold
LOOP
dbms_output.put_line('Inside Loop : ' || l_rec.invoice_id);
ap_holds_pkg.quick_release
( x_invoice_id => l_rec.invoice_id
, x_hold_lookup_code => l_rec.hold_lookup_code
, x_release_lookup_code => v_release_code
, x_release_reason => v_release_reason
, x_responsibility_id => fnd_global.resp_id
, x_last_updated_by => fnd_global.user_id
, x_last_update_date => SYSDATE
, x_holds_count => v_hold_cnt
, x_approval_status_lookup_code => v_apprvl_sts
, x_calling_sequence => 'xxap_invoice_util_pkg.release_holds'
) ;
dbms_output.put_line('Hold count = ' || v_hold_cnt ||
', Approval Status:' || v_apprvl_sts);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Exception:' || sqlerrm);
END;
p_invoice_id ap_invoices_all.invoice_id % TYPE := -1; /* Change invoice id to correct value */
p_inv_batch ap_batches_all.batch_name % TYPE := NULL;
p_hold_code ap_holds_all.hold_lookup_code % TYPE := 'Encumbrance Acctg Fail';
v_hold_cnt NUMBER;
v_apprvl_sts ap_invoices.wfapproval_status % TYPE;
v_release_reason ap_lookup_codes.description % TYPE;
v_release_code ap_lookup_codes.lookup_code % TYPE := 'Encumbrance Acctg Ok';
CURSOR c_inv_on_hold IS
SELECT hld.hold_lookup_code
, hld.invoice_id
FROM ap_holds hld
, ap_invoices inv
, ap_batches_all btch
WHERE hld.invoice_id = inv.invoice_id
AND btch.batch_id = inv.batch_id
AND (p_invoice_id IS NULL or p_invoice_id = inv.invoice_id )
AND (p_inv_batch IS NULL or p_inv_batch = btch.batch_name )
AND (p_hold_code IS NULL or p_hold_code = hld.hold_lookup_code)
;
BEGIN
mo_global.set_policy_context('S',':ORG_ID');
SELECT description
INTO v_release_reason
FROM ap_lookup_codes
WHERE lookup_type = 'HOLD CODE'
AND lookup_code = v_release_code
AND enabled_flag = 'Y'
AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (start_date_active, SYSDATE - 1))
AND TRUNC (NVL (inactive_date , SYSDATE + 1))
;
FOR l_rec in c_inv_on_hold
LOOP
dbms_output.put_line('Inside Loop : ' || l_rec.invoice_id);
ap_holds_pkg.quick_release
( x_invoice_id => l_rec.invoice_id
, x_hold_lookup_code => l_rec.hold_lookup_code
, x_release_lookup_code => v_release_code
, x_release_reason => v_release_reason
, x_responsibility_id => fnd_global.resp_id
, x_last_updated_by => fnd_global.user_id
, x_last_update_date => SYSDATE
, x_holds_count => v_hold_cnt
, x_approval_status_lookup_code => v_apprvl_sts
, x_calling_sequence => 'xxap_invoice_util_pkg.release_holds'
) ;
dbms_output.put_line('Hold count = ' || v_hold_cnt ||
', Approval Status:' || v_apprvl_sts);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Exception:' || sqlerrm);
END;
It should be noted that according to Oracle document ID 2094785.1, the AP_HOLDS_PKG is a private package, and is not a public API. It's use is not supported.
ReplyDelete