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
);
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
);
COMMENTS