AP Invoice Hold Data Migration Process

How to Apply holds on invoices from back end?
API to apply holds on invoices?
Mandatory Validation or Derivations for Creating holds on invoices?

AP Invoice Hold Data Migration Process:


Mandatory Columns:
1. Hold_Id
2. Invoice_Id
3. Lookup_Code
4. Held_By
5. Hold_Reason
6. Org_Id

Important Tables:
1. AP_HOLD_CODES
2. AP_HOLDS_ALL
3. AP_HOLDS_S

Important Packages:
1. AP_HOLDS_PKG

Important Validation/Derivation:

1. Derive Org_id:
SELECT organization_id
FROM hr_all_organization_units
WHERE upper(name)=upper(p_operating_unit);

2. Check for Invoice Number exists or not:
SELECT vendor_id
INTO l_vendor_id
FROM ap_suppliers asp
WHERE segment1 = p_supplier_num
AND enabled_flag = 'Y';

SELECT ap.invoice_id
FROM ap_invoices_all ap
WHERE ap.invoice_num = p_invoice_num
AND ap.vendor_id = l_vendor_id
AND ap.org_id = p_org_id;

3. Check Hold Lookup Code.
SELECT hold_lookup_code
from   ap_hold_codes
where  hold_type = p_ hold_type;

4. Check user exist or not.
SELECT user_id HELD_BY
FROM fnd_user
WHERE upper(user_name)=upper(p_user_name);
OR
SELECT person_id HELD_BY
FROM PER_ALL_PEOPLE_F
where employee_number = p_employee_number;

Load the data to API:

AP_HOLDS_PKG.Insert_Row
(
X_Rowid                                    =>   X_Rowid,    
x_hold_id                                    =>   apps.ap_holds_s.nextval,
X_Invoice_Id                              =>   p_invoice_id,
X_Line_Location_Id                   =>   NULL,
X_Hold_Lookup_Code              =>   p_hold_lookup_code,
X_Last_Update_Date                 =>   SYSDATE,
X_Last_Updated_By                  =>   fnd_global.user_id,
X_Held_By                                =>   p_held_by,
X_Hold_Date                             =>   SYSDATE,
X_Hold_Reason                         =>   p_hold_reason,
X_Release_Lookup_Code         =>   NULL,
X_Release_Reason              =>   NULL,
X_Status_Flag                     =>   NULL,
X_Last_Update_Login        =>   fnd_global.login_id,
X_Creation_Date               =>   SYSDATE,
X_Created_By                  =>   fnd_global.user_id ,
X_Responsibility_Id           =>   NULL,
X_Attribute1                  =>   NULL,
X_Attribute2                  =>   NULL,
X_Attribute3                  =>   NULL,
X_Attribute4                  =>   NULL,
X_Attribute5                  =>   NULL,
X_Attribute6                  =>   NULL,
X_Attribute7                  =>   NULL,
X_Attribute8                  =>   NULL,
X_Attribute9                  =>   NULL,
X_Attribute10                 =>   NULL,
X_Attribute11                 =>   NULL,
X_Attribute12                 =>   NULL,
X_Attribute13                 =>   NULL,
X_Attribute14                 =>   NULL,
X_Attribute15                 =>   NULL,
X_Attribute_Category        =>   NULL,
X_Org_Id                          =>   p_org_id,
X_calling_sequence            =>   NULL
);