Below procedure script will help you apply a hold on AP invoice in oracle apps 11i,R12.
This procedure can be registered as a Concurrent Executable and Concurrent Program.
API: AP_HOLDS_PKG.INSERT_SINGLE_HOLD
/*+===============================================================*
* PROCEDURE
*
*
*
* assign_invhold_prc *
* *
* DESCRIPTION *
* =========== *
* The following procedure is called by the concurrent program is used to apply *
* Invoice Hold *
* PARAMETERS *
* ========== *
* NAME TYPE DESCRIPTION *
* -----------------------------------------------------------------------------------*
* retcode OUT Standard Conc Program parameter *
* errbuf OUT Standard Conc Program parameter *
* p_invoice_hold IN Invoice Hold Type *
* p_inv_lookup IN Invoice Type(STANDARD,MIXED etc.) *
* ------------------------------------------------------------------------------------*
* CALLED BY *
* Concurrent Program *
*+=================================================================*/
* assign_invhold_prc *
* *
* DESCRIPTION *
* =========== *
* The following procedure is called by the concurrent program is used to apply *
* Invoice Hold *
* PARAMETERS *
* ========== *
* NAME TYPE DESCRIPTION *
* -----------------------------------------------------------------------------------*
* retcode OUT Standard Conc Program parameter *
* errbuf OUT Standard Conc Program parameter *
* p_invoice_hold IN Invoice Hold Type *
* p_inv_lookup IN Invoice Type(STANDARD,MIXED etc.) *
* ------------------------------------------------------------------------------------*
* CALLED BY *
* Concurrent Program *
*+=================================================================*/
PROCEDURE assign_invhold_prc (errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_invoice_hold IN VARCHAR2,
p_inv_lookup IN VARCHAR2)
IS
--Declare local variables
lv_hold_type ap_hold_codes_v.hold_type%TYPE DEFAULT NULL;
lv_lookup_type ap_lookup_codes.lookup_type%TYPE := 'HOLD CODE';
lv_hold_lookup_code ap_hold_codes_v.hold_lookup_code%TYPE := p_invoice_hold;
lv_hold_reason ap_hold_codes_v.description%TYPE;
lv_invoice_id PLS_INTEGER;
lv_invtype_lkpcode ap_invoices_all.invoice_type_lookup_code%TYPE := p_inv_lookup;
lv_fnd_user fnd_user.user_id%TYPE := fnd_profile.VALUE ('USER_ID');
lv_org_id ap_invoices_all.org_id%TYPE := fnd_profile.value('ORG_ID');
lv_sql_count NUMBER :=0;
-- Cursor to derive invoices to be placed on hold
CURSOR cur_inv
IS
SELECT invoice_id, invoice_num
FROM (SELECT ai.invoice_id invoice_id, ai.invoice_num invoice_num
FROM ap_invoices_all ai
WHERE 1 = 1
AND ai.org_id = lv_org_id
AND ai.invoice_type_lookup_code = lv_invtype_lkpcode
AND ai.payment_status_flag IN ('N','P')
WHERE NOT EXISTS (
SELECT 1
FROM ap_holds_all
WHERE hold_lookup_code = lv_hold_lookup_code
AND org_id = lv_org_id
AND invoice_id = ai.invoice_id);
BEGIN
-- Derive Hold Type Code
BEGIN
SELECT ahc.hold_type, ahc.hold_lookup_code, ahc.description
INTO lv_hold_type, lv_hold_lookup_code, lv_hold_reason
FROM ap_lookup_codes alc, ap_hold_codes ahc
WHERE alc.lookup_type = lv_lookup_type
AND alc.lookup_code = lv_hold_lookup_code
AND UPPER (ahc.hold_lookup_code) = UPPER (alc.lookup_code);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Error deriving hold lookup -' || SQLERRM
);
END;
fnd_file.put_line
(fnd_file.output,
'Following Invoices are put on ' || p_invoice_hold || ' Hold:'
);
fnd_file.put_line (fnd_file.output,
' ');
fnd_file.put_line (fnd_file.output,
'Invoice Number | Invoice ID ');
fnd_file.put_line (fnd_file.output,
'------------------ ---------------');
--Loop begins
FOR inv_rec IN cur_inv
LOOP
lv_sql_count := lv_sql_count+1;
--API to apply hold
ap_holds_pkg.insert_single_hold
(x_invoice_id => inv_rec.invoice_id,
x_hold_lookup_code => lv_hold_lookup_code,
x_hold_type => lv_hold_type,
x_hold_reason => lv_hold_reason,
x_held_by => lv_fnd_user,
x_calling_sequence => NULL
);
fnd_file.put_line (fnd_file.output,
RPAD (inv_rec.invoice_num, 18, ' ')
|| '| '
|| RPAD (inv_rec.invoice_id, 15, ' ')
);
END LOOP;
IF lv_sql_count = 0 THEN
fnd_file.put_line (fnd_file.output,' *******No Data Found*******');
END IF;
--Loop Ends
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
IF (SQLCODE <> -20001)
THEN
fnd_message.set_name ('SQLAP', 'AP_DEBUG');
fnd_message.set_token ('ERROR', SQLERRM);
END IF;
fnd_file.put_line (fnd_file.LOG,
'Error in prc: assign_invhold_prc -' || SQLERRM
);
app_exception.raise_exception;
END assign_invhold_prc;
Note: Use ap_holds_pkg.release_single_hold to release hold by passing required parameters like invoice_id, hold_lookup_code
