Procure To Pay Cycle

1) Create Requisition:

Requisition is nothing but a formal request to buy something (like Inventory material, office supplies etc) needed for the enterprise. Only an employee can create one. There are two types of requisitions:
Internal Requisition: Internal requisitions provide the mechanism for requesting and transferring material from one inventory to other inventory.
Purchase requisition: Unlike Internal requisitions, Purchase requisitions are used for requesting material from suppliers.
Once the requistion is created, the status of the requisition will be “Incomplete”. And now the Approve button is highlighted. The requisition needs to be approved first before proceeding further by the concerned authority. Submit this requisition for Approval by clicking on the Approve button. The status will now be updated to “In Process”. The workflow then will send an Approval notification to the concerned person (derived based on hierarchy used - Position or Supervisor hierarchy) using which he can Approve or Reject the requisition.

Underlying Tables:

PO_REQUISITION_HEADERS_ALL
PO_REQUISITION_LINES_ALL
PO_REQ_DISTRIBUTIONS_ALL

2) Create Purchase Order:

Create Purchase order  by entering Header, Line and Shipment details. There can be multiple distributions for each shipment. Requisition Number is entered in More tab of Shipment Distributions window

Underlying Tables:

PO_HEADERS_ALLPO_LINES_ALL
PO_LINE_LOCATIONS_ALL
PO_DISTRIBUTIONS_ALL


3) Create PO Receipt:

Create a receipt to receive the items in the Purchase Order.

Receipt Tables are:

RCV_SHIPMENT_HEADERS
RCV_SHIPMENT_LINES (Lines Table has PO_HEADER_ID)

4) Create Invoice in Payables:

Once the goods are received, it’s time to pay the vendor for the goods purchased and hence the invoices are created. Then match the invoice to either Purchase Order or Receipt (depending on the Invoice Match option specified on the PO).

Invoice Tables:
AP_INVOICES_ALL
AP_INVOICE_LINES_ALLAP_INVOICE_DISTRIBUTIONS_ALL

After the invoice is created, validate the invoice and create the accounting

Accounting Entries Tables:
AP_ACCOUNTING_EVENTS_ALL
AP_AE_HEADERS_ALL
AP_AE_LINES_ALL

5) Making Invoice Payment:

Go to the Invoice window and query the invoice you want to pay. After the payment is done, create accounting for payments. You can also pay the invoices using Payment Batch screen

Payment Tables:

IBY_PAYMENTS_ALL or AP_INVOICE_PAYMENTS_ALL
IBY_EXTERNAL_BANK_ACCOUNTS or AP_BANK_ACCOUNTS_ALL


6) Transfer to General Ledger:

Run the concurrent program “Payables Transfer to General Ledger” with the below required parameters.

Set of Books Name
Journal Category
Validate Accounts (Yes/ No)
Transfer to GL Interface (In Detail)
Submit Journal Import (Yes/ No)

IBY_FD_EXTRACT_EXT_PUB

Oracle Payments provides the IBY_FD_EXTRACT_EXT_PUB extensibility package to construct custom XML element structure that can be added to the payment XML extract generated by Oracle Payments. The package specification and body definition can be found in files ibyfdxes.pls and ibyfdxeb.pls respectively. These files are located in the $IBY_TOP/patch/115/sql directory.

The package allows custom elements to be created at following levels.

• Instruction

• Payment

• Document Payable

• Document Payable Line
• Payment Process Request

You cannot customize the package specification, but package body contains stubbed functions that you can customize. The five functions are as follows:

• FUNCTION Get_Ins_Ext_Agg(p_payment_instruction_id IN NUMBER) RETURN XMLTYPE

This function allows XML element to be introduced at instruction level and run only once for the instruction.

• FUNCTION Get_Pmt_Ext_Agg(p_payment_id IN NUMBER) RETURN XMLTYPE

This function allows XML element to be introduced at payment level and run once for each payment in the instruction.

• FUNCTION Get_Doc_Ext_Agg(p_document_payable_id IN NUMBER) RETURN XMLTYPE

This function allows XML element to be introduced at document payable level and run once for each document payable in the instruction.

• FUNCTION Get_Docline_Ext_Agg(p_document_payable_id IN NUMBER, p_line_number IN NUMBER) RETURN XMLTYPE

This function allows XML element to be introduced at document payable line level and run once for each document payable line in the instruction.

• FUNCTION Get_Ppr_Ext_Agg(p_payment_service_request_id IN NUMBER) RETURN XMLTYPE

This function allows XML element to be introduced at document payable level and run once for each payment process request.

Example using the Extensibility Utility Function

The following example shows how custom XML element ReferenceFormatCode is introduced at the payment instruction level.

The function Get_Ins_Ext_Agg in the IBY_FD_EXTRACT_EXT_PUB package is altered as shown, to build custom XML elements, that are included in the XML payment extract at the Payment Instruction level. The sample code that builds a new XML element ReferenceFormatCode using the payment_instruction_id is as follows:

PACKAGE : IBY_FD_EXTRACT_EXT_PUB

/*PAYMENT INSTRUCTION LEVEL*/

/*Before Change*/

FUNCTION Get_Ins_Ext_Agg(p_payment_instruction_id IN NUMBER)

RETURN XMLTYPE

IS

BEGIN

RETURN NULL;

END Get_Ins_Ext_Agg;

/*After Change */

FUNCTION Get_Ins_Ext_Agg(p_payment_instruction_id IN NUMBER)

RETURN XMLTYPE

IS

l_ins_ext_agg XMLTYPE;

BEGIN

SELECT XMLElement("ReferenceFormatCode", reference_format_code)

INTO l_ins_ext_agg

FROM iby_pay_instructions_all ipmtins

WHERE ipmtins.payment_instruction_id = p_payment_instruction_id;

RETURN l_ins_ext_agg;

END Get_Ins_Ext_Agg;;

Similar customizations are carried out on the other customizable functions described above.

Following is the list of views used for building the Funds Disbursement’s Payment XML Extract:

• IBY_XML_HR_ADDR_1_0_V

• IBY_XML_HZ_ADDR_1_0_V
• IBY_XML_FD_PAYER_1_0_V
• IBY_XML_FD_PAYEE_1_0_V
• IBY_XML_FD_PEBA_1_0_V
• IBY_XML_FD_PRBA_1_0_V
• IBY_XML_FD_DOCLINE_1_0_V
• IBY_XML_FD_DOC_1_0_V
• IBY_XML_FD_PMT_1_0_V

• IBY_XML_FD_INS_1_0_V

• IBY_XML_FD_DOC_ERR_1_0_V

• IBY_XML_FD_PMT_ERR_1_0_V
• IBY_XML_FD_PPR_1_0_V

• IBY_XML_FD_PEBAM_1_0_V

• IBY_XML_FD_PAYEEM_1_0_V
• IBY_XML_FD_PEBA_1_0_VD
• IBY_XML_FD_PEBAM_1_0_VD
• IBY_XML_FD_INS_1_0_V0
• IBY_XML_FD_PMT_1_0_V0

• IBY_XML_FD_PRBA_1_0_V0

• IBY_XML_FD_ACCT_SETTINGS_1_0_V

R12 Supplier Bank/Branch Creation Code

DECLARE

x_result_rec_type iby_fndcpt_common_pub.result_rec_type;
v_error_reason VARCHAR2 (2000) := NULL;
v_msg_data VARCHAR2 (1000) := NULL;
v_msg_count NUMBER := NULL;
v_return_status VARCHAR2 (100) := NULL;
v_extbank_rec_type iby_ext_bankacct_pub.extbank_rec_type;
v_extbankbranch_rec_type iby_ext_bankacct_pub.extbankbranch_rec_type;

v_bank_id NUMBER;
x_bank_id NUMBER;
v_branch_id NUMBER;
x_branch_id NUMBER;

CURSOR c
IS
SELECT * FROM XXAP_BANK_BRANCH_STG;

BEGIN
FOR i IN c
LOOP
BEGIN

v_error_reason := NULL;
v_return_status := NULL;
v_msg_count := NULL;
v_msg_data := NULL;

v_extbank_rec_type.object_version_number := 1.0;
v_extbank_rec_type.bank_name := i.bank_name;
v_extbank_rec_type.bank_number := i.bank_num;
v_extbank_rec_type.institution_type := i.institution_type;
v_extbank_rec_type.country_code := NVL(i.country, 'US');
v_extbank_rec_type.description := i.description;

DBMS_OUTPUT.put_line ('BEFORE BANK API');

iby_ext_bankacct_pub.create_ext_bank
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_ext_bank_rec => v_extbank_rec_type,
x_bank_id => x_bank_id,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data,
x_response => x_result_rec_type
);

DBMS_OUTPUT.put_line ('AFTER BANK API');
DBMS_OUTPUT.put_line (v_return_status);
DBMS_OUTPUT.put_line (v_msg_count);
DBMS_OUTPUT.put_line (v_msg_data);

IF v_return_status <> fnd_api.g_ret_sts_success THEN
IF v_msg_count >= 1 THEN
FOR i IN 1 .. v_msg_count
LOOP
IF v_error_reason IS NULL THEN
v_error_reason := SUBSTR (fnd_msg_pub.get (p_encoded => fnd_api.g_false), 1, 255);
ELSE
v_error_reason := v_error_reason|| ' ,'|| SUBSTR (fnd_msg_pub.get (p_encoded => fnd_api.g_false), 1, 255);
END IF;
DBMS_OUTPUT.put_line ('BANK API ERROR-' || v_error_reason);
END LOOP;
END IF;
ROLLBACK;
ELSE
v_bank_id := x_bank_id;
DBMS_OUTPUT.put_line ('BANK ID-' || v_bank_id);
COMMIT;
END IF;

v_error_reason := NULL;
v_return_status := NULL;
v_msg_count := NULL;
v_msg_data := NULL;

v_extbankbranch_rec_type.bank_party_id := v_bank_id;
v_extbankbranch_rec_type.branch_name := i.bank_branch_name;
v_extbankbranch_rec_type.branch_type := i.bank_branch_type;
v_extbankbranch_rec_type.attribute14 := i.bank_branch_id;
v_extbankbranch_rec_type.attribute15 := 'DOMESTIC';

DBMS_OUTPUT.put_line ('BEFORE BRANCH API');

iby_ext_bankacct_pub.create_ext_bank_branch
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_ext_bank_branch_rec => v_extbankbranch_rec_type,
x_branch_id => x_branch_id,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data,
x_response => x_result_rec_type
);

DBMS_OUTPUT.put_line ('AFTER BRANCH API');
DBMS_OUTPUT.put_line (v_return_status);
DBMS_OUTPUT.put_line (v_msg_count);
DBMS_OUTPUT.put_line (v_msg_data);

IF v_return_status <> fnd_api.g_ret_sts_success THEN
IF v_msg_count >= 1 THEN
FOR i IN 1 .. v_msg_count
LOOP
IF v_error_reason IS NULL THEN
v_error_reason := SUBSTR (fnd_msg_pub.get (p_encoded => fnd_api.g_false), 1, 255);
ELSE
v_error_reason := v_error_reason|| ' ,'|| SUBSTR (fnd_msg_pub.get (p_encoded => fnd_api.g_false), 1, 255);
END IF;
END LOOP;
DBMS_OUTPUT.put_line ('BRANCH API ERROR-' || v_error_reason);
END IF;
ROLLBACK;
ELSE
v_branch_id := x_branch_id;
DBMS_OUTPUT.put_line ('BRANCH ID-' || v_branch_id);
COMMIT;
END IF;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('ERROR INSIDE LOOP-' || SQLERRM);
END;
END LOOP;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line (SQLERRM || '-' || SQLCODE);
END;

AP-GL Reconciliation

Month End reconciliation between GL and AP is highly recommended. If AP is interfaced to GL, verifying the balance between the two applications is usually done through comparing account balances of the liability (A/P) account.

To reconcile your accounts payable activity for April, make the following calculation:
"Accounts Payable Trial Balance" as of March 31 +
"Posted Invoice Register" for the period between April 1 and April 30 -
"Posted Payment Register" for the period between April 1 and April 30 =
"Accounts Payable Trial Balance" as of April 30

> If total is not matching you will need to find out the root casue of difference:
Eg. Invalidated Invoice for the period
> For the PTD activity check the "period Close Exception" if there are any invoices and payments which are not transferred to GL.
> If the current period does not reconcile, please complete the reconciliation process for all prior periodsfrom the most recent to the earliest until you get to one that reconciles.

Common Reasons for balance mismatch between AP & GL
1) Manual journal entries in the general ledger that involve an AP liability account will cause the AP Trialbalance not to reconcile to the GL. These entries are not included in the AP subledger so they will not be reflected on the AP Trial Balance Report.
Run the "GL Account Analysis" report for the liability account and for the date range in question. Look for transactions with a source other than Payables. This can quickly pinpoint any transactions incorrectly charged to the account.

2) You performed a datafix in the past where you used the undo accounting script and swept a transaction forward from a closed period to reaccount it, this will cause an imbalance between AP and GL. The imbalancewill be corrected in the period in which you made a GL adjustment to account for the fix.

3) Any correction you make during the journal import process will result in the line being changed in the general ledger, but not in AP

4) If you have deleted any AP batches or lines from AP batches out of the GL Interface, this will cause AP and GL to be out of balance

5) If the AP batch is still in the GL Interface, it will not be reflected in the GL reports and this will cause a difference between AP and GL.

6) Any AP batches that are unposted in GL will cause a difference between AP and GL

R12 Payment Query for Checkrun Name

SELECT asic.selected_check_id, asic.currency_code,
asic.check_amount -- ibypsr.payment_amount,
'A' paystatus, 'APPAYMENT' cashflowtype, 'EFT' paymentmethod,
TO_CHAR (ibydoc.payment_date, 'MM/DD/YYYY') transdate,
TO_CHAR (ibydoc.payment_date, 'MM/DD/YYYY') valuedate,
asu.segment1 descriptionreference, cba.description entityid,
'Y' supplypayingaccount, cba.bank_account_num entbankacctnum,
cba.currency_code entbankcurrcode, 'Y' supplycptydetails,
ieba.bank_account_name counterpartyname,
SUBSTR
(CONCAT (CONCAT (CONCAT (assa.address_line1, ' '),
CONCAT (assa.address_line2, ' ')
),
assa.address_line3
),
1,
35
) counterpartyaddress,
assa.city counterpartycity, assa.state counterpartystate,
assa.country counterpartycountry, assa.zip counterpartyzip,
cbbv.bank_branch_name cptybankcode,
DECODE (cbbv.bank_branch_type,
'SWIFT', 'S',
'IS', 'S',
'A'
) cptybankcodetype,
cbbv.bank_branch_type cptybankbranchid, cbbv.description cptybankname,
SUBSTR (CONCAT (CONCAT (CONCAT (cbbv.address_line1, ' '),
CONCAT (cbbv.address_line2, ' ')
),
cbbv.address_line3
),
1,
35
) cptybankaddress,
cbbv.city cptybankcity, cbbv.state cptybankstate, cbbv.zip cptybankzip,
ieba.bank_account_num cptybankacctnum,
ieba.attribute1 cptybankacctnumtype, ieba.country_code cptybankcountry,
ieba.currency_code cptybankacctcurrcode, ieba.attribute5 taxpayerid,
ieba.attribute14 intmedbankprecept, iia.bank_name intmedbankname,
iia.bank_code intmedbankcode,
DECODE (ieba.attribute14, 'IS', 'S', 'A') intmedbankcodetype,
iia.account_number intmedbankacctnum, ieba.attribute2 bankinstructions,
ieba.attribute3 cptymessage, '2' finchrgalloc,
asic.check_number custrefnum, '' chequenum,
ieba.attribute7 personalidnum,
apdl.call_app_pay_service_req_code custbatchrefid,
ieba.attribute6 reasonforpay, '' transtypecode
FROM
ap_selected_invoice_checks_all asic,
ap_document_lines_v apdl,
iby_docs_payable_all ibydoc,
iby_pay_service_requests ibypsr,
ap_suppliers asu,
ap_supplier_sites_all assa,
ce_bank_accounts cba,
iby_ext_bank_accounts ieba,
iby_external_payees_all iepa,
iby_pmt_instr_uses_all ipisa,
ce_bank_branches_v cbbv,
iby_intermediary_accts iia
WHERE
apdl.call_app_pay_service_req_code = :p_checkrun_name
AND apdl.call_app_pay_service_req_code = asic.checkrun_name
AND ibydoc.calling_app_id = apdl.calling_app_id
AND ibydoc.payment_service_request_id = ibypsr.payment_service_request_id
AND ibypsr.call_app_pay_service_req_code =
apdl.call_app_pay_service_req_code
AND NVL (ibydoc.calling_app_doc_unique_ref1, -99) =
NVL (apdl.calling_app_doc_unique_ref1,
-99)
AND NVL (ibydoc.calling_app_doc_unique_ref2, -99) =
NVL (apdl.calling_app_doc_unique_ref2,
-99)
AND NVL (ibydoc.calling_app_doc_unique_ref3, -99) =
NVL (apdl.calling_app_doc_unique_ref3,
-99)
AND NVL (ibydoc.calling_app_doc_unique_ref4, -99) =
NVL (apdl.calling_app_doc_unique_ref4,
-99)
AND NVL (ibydoc.calling_app_doc_unique_ref5, -99) =
NVL (apdl.calling_app_doc_unique_ref5,
-99)
AND assa.vendor_id = asu.vendor_id
AND assa.vendor_site_id = ibydoc.supplier_site_id
AND assa.party_site_id = ibydoc.party_site_id
AND asu.party_id = ibydoc.payee_party_id
AND cba.bank_account_id = ibydoc.internal_bank_account_id
AND iepa.ext_payee_id = ibydoc.ext_payee_id
AND ipisa.ext_pmt_party_id = iepa.ext_payee_id
AND ieba.ext_bank_account_id = ipisa.instrument_id
AND cbbv.branch_party_id = ieba.branch_id
AND cbbv.bank_party_id = ieba.bank_id
AND iia.bank_acct_id = ieba.ext_bank_account_id

AP Supplier API

ap_po_vendors_apis_pkg.insert_new_vendor
(p_vendor_name => v_vendor.vendor_name,
p_vendor_type_lookup_code => l_lookup_code,
p_taxpayer_id => NULL,
p_tax_registration_id => NULL,
p_women_owned_flag => NULL,
p_small_business_flag => NULL,
p_minority_group_lookup_code => NULL,
p_supplier_number => v_vendor.vendor_no,
x_vendor_id,
x_status,
x_exception_msg);

ap_po_vendors_apis_pkg.insert_new_vendor_site
(p_vendor_site_code => v_vendor_site.vendor_site_code,
p_vendor_id => l_vendor_id,
p_org_id => l_org_id,
p_address_line1 => v_vendor_site.address_line1,
p_address_line2 => v_vendor_site.address_line2,
p_address_line3 => v_vendor_site.address_line3,
p_address_line4 => v_vendor_site.address_line4,
p_city => v_vendor_site.city,
p_state => v_vendor_site.state,
p_zip => v_vendor_site.zip,
p_province => NULL,
p_county => v_vendor_site.county,
p_country => v_vendor_site.country,
p_area_code => v_vendor_site.area_code,
p_phone => v_vendor_site.phone,
p_fax_area_code => v_vendor_site.fax_area_code,
p_fax => v_vendor_site.fax,
p_email_address => NULL,
p_purchasing_site_flag => v_vendor_site.purchasing_site_flag,
p_pay_site_flag => NULL,
p_rfq_only_site_flag => NULL,
x_vendor_site_id,
x_status,
x_exception_msg);

ap_po_vendors_apis_pkg.insert_new_vendor_contact
(p_vendor_site_id => l_vendor_site_id,
p_first_name => v_vendor_site_contact.FIRST_NAME,
p_last_name => v_vendor_site_contact.LAST_NAME,
p_middle_name => v_vendor_site_contact.MIDDLE_NAME,
p_prefix => v_vendor_site_contact.PREFIX,
p_title => v_vendor_site_contact.TITLE,
p_mail_stop => v_vendor_site_contact.MAIL_STOP,
p_area_code => v_vendor_site_contact.CONTACT_AREA_CODE,
p_phone => v_vendor_site_contact.CONTACT_PHONE,
p_alt_area_code => NULL,
p_alt_phone => NULL,
p_fax_area_code => NULL,
p_fax => NULL,
p_email_address => NULL,
p_url => NULL,
x_vendor_contact_id,
x_status,
x_exception_msg);

AP-PO Link Queries

-- VENDOR, PO AND INVOICE DETAILS (2 WAY MATCH)

SELECT DISTINCT
a.org_id "ORG ID", e.segment1 "VENDOR NUM",
e.vendor_name "SUPPLIER NAME",
UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
f.vendor_site_code "VENDOR SITE CODE", f.address_line1 "ADDRESS",
f.city "CITY", f.country "COUNTRY",
TO_CHAR (TRUNC (d.creation_date)) "PO DATE", d.segment1 "PO NUM",
d.type_lookup_code "PO TYPE", c.quantity_ordered "QTY ORDERED",
c.quantity_cancelled "QTY CANCELLED", g1.quantity_received, g1.quantity_rejected, g1.quantity_billed,
g.item_id "ITEM ID",
g.item_description "ITEM DESCRIPTION", g.unit_price "UNIT PRICE",
(NVL (c.quantity_ordered, 0) - NVL (c.quantity_cancelled, 0)
)
* NVL (g.unit_price, 0) "PO LINE AMOUNT",
(SELECT DECODE (ph.approved_flag, 'Y', 'Approved')
FROM po.po_headers_all ph
WHERE ph.po_header_id = d.po_header_id) "PO APPROVED?",
a.invoice_type_lookup_code "INVOICE TYPE",
a.invoice_amount "INVOICE AMOUNT",
TO_CHAR (TRUNC (a.invoice_date)) "INVOICE DATE",
a.invoice_num "INVOICE NUMBER",
(SELECT DECODE (x.match_status_flag,
'A', 'Approved'
)
FROM ap.ap_invoice_distributions_all x
WHERE x.invoice_distribution_id = b.invoice_distribution_id)
"INVOICE APPROVED?",
a.amount_paid
FROM ap.ap_invoices_all a,
ap.ap_invoice_distributions_all b,
po.po_distributions_all c,
po.po_headers_all d,
ap.ap_suppliers e,
ap.ap_supplier_sites_all f,
po.po_lines_all g,
po.po_line_locations_all g1
WHERE a.invoice_id = b.invoice_id
AND b.po_distribution_id = c.po_distribution_id(+)
AND c.po_header_id = d.po_header_id(+)
AND e.vendor_id(+) = d.vendor_id
AND f.vendor_site_id(+) = d.vendor_site_id
AND d.po_header_id = g.po_header_id
AND g1.po_header_id = c.po_header_id
AND g1.po_line_id = g.po_line_id
AND c.po_line_id = g.po_line_id
AND c.line_location_id = g1.line_location_id
AND g1.inspection_required_flag = 'N'
AND g1.receipt_required_flag = 'N'

-- VENDOR, PO AND INVOICE DETAILS (3 WAY MATCH)
SELECT DISTINCT
a.org_id "ORG ID", e.segment1 "VENDOR NUM",
e.vendor_name "SUPPLIER NAME",
UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
f.vendor_site_code "VENDOR SITE CODE", f.address_line1 "ADDRESS",
f.city "CITY", f.country "COUNTRY",
TO_CHAR (TRUNC (d.creation_date)) "PO DATE", d.segment1 "PO NUM",
d.type_lookup_code "PO TYPE", c.quantity_ordered "QTY ORDERED",
c.quantity_cancelled "QTY CANCELLED", g1.quantity_received, g1.quantity_rejected, g1.quantity_billed,
g.item_id "ITEM ID",
g.item_description "ITEM DESCRIPTION", g.unit_price "UNIT PRICE",
(NVL (c.quantity_ordered, 0) - NVL (c.quantity_cancelled, 0)
)
* NVL (g.unit_price, 0) "PO LINE AMOUNT",
(SELECT DECODE (ph.approved_flag, 'Y', 'Approved')
FROM po.po_headers_all ph
WHERE ph.po_header_id = d.po_header_id) "PO APPROVED?",
a.invoice_type_lookup_code "INVOICE TYPE",
a.invoice_amount "INVOICE AMOUNT",
TO_CHAR (TRUNC (a.invoice_date)) "INVOICE DATE",
a.invoice_num "INVOICE NUMBER",
(SELECT DECODE (x.match_status_flag,
'A', 'Approved'
)
FROM ap.ap_invoice_distributions_all x
WHERE x.invoice_distribution_id = b.invoice_distribution_id)
"INVOICE APPROVED?",
a.amount_paid
FROM ap.ap_invoices_all a,
ap.ap_invoice_distributions_all b,
po.po_distributions_all c,
po.po_headers_all d,
ap.ap_suppliers e,
ap.ap_supplier_sites_all f,
po.po_lines_all g,
po.po_line_locations_all g1
WHERE a.invoice_id = b.invoice_id
AND b.po_distribution_id = c.po_distribution_id(+)
AND c.po_header_id = d.po_header_id(+)
AND e.vendor_id(+) = d.vendor_id
AND f.vendor_site_id(+) = d.vendor_site_id
AND d.po_header_id = g.po_header_id
AND g1.po_header_id = c.po_header_id
AND g1.po_line_id = g.po_line_id
AND c.po_line_id = g.po_line_id
AND c.line_location_id = g1.line_location_id
AND g1.inspection_required_flag = 'N'
AND g1.receipt_required_flag = 'Y'

-- VENDOR, PO AND INVOICE DETAILS (4 WAY MATCH)
SELECT DISTINCT
a.org_id "ORG ID", e.segment1 "VENDOR NUM",
e.vendor_name "SUPPLIER NAME",
UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
f.vendor_site_code "VENDOR SITE CODE", f.address_line1 "ADDRESS",
f.city "CITY", f.country "COUNTRY",
TO_CHAR (TRUNC (d.creation_date)) "PO DATE", d.segment1 "PO NUM",
d.type_lookup_code "PO TYPE", c.quantity_ordered "QTY ORDERED",
c.quantity_cancelled "QTY CANCELLED", g1.quantity_received, g1.quantity_rejected, g1.quantity_billed,
g.item_id "ITEM ID",
g.item_description "ITEM DESCRIPTION", g.unit_price "UNIT PRICE",
(NVL (c.quantity_ordered, 0) - NVL (c.quantity_cancelled, 0)
)
* NVL (g.unit_price, 0) "PO LINE AMOUNT",
(SELECT DECODE (ph.approved_flag, 'Y', 'Approved')
FROM po.po_headers_all ph
WHERE ph.po_header_id = d.po_header_id) "PO APPROVED?",
a.invoice_type_lookup_code "INVOICE TYPE",
a.invoice_amount "INVOICE AMOUNT",
TO_CHAR (TRUNC (a.invoice_date)) "INVOICE DATE",
a.invoice_num "INVOICE NUMBER",
(SELECT DECODE (x.match_status_flag,
'A', 'Approved'
)
FROM ap.ap_invoice_distributions_all x
WHERE x.invoice_distribution_id = b.invoice_distribution_id)
"INVOICE APPROVED?",
a.amount_paid
FROM ap.ap_invoices_all a,
ap.ap_invoice_distributions_all b,
po.po_distributions_all c,
po.po_headers_all d,
ap.ap_suppliers e,
ap.ap_supplier_sites_all f,
po.po_lines_all g,
po.po_line_locations_all g1
WHERE a.invoice_id = b.invoice_id
AND b.po_distribution_id = c.po_distribution_id(+)
AND c.po_header_id = d.po_header_id(+)
AND e.vendor_id(+) = d.vendor_id
AND f.vendor_site_id(+) = d.vendor_site_id
AND d.po_header_id = g.po_header_id
AND g1.po_header_id = c.po_header_id
AND g1.po_line_id = g.po_line_id
AND c.po_line_id = g.po_line_id
AND c.line_location_id = g1.line_location_id
AND g1.inspection_required_flag = 'Y'
AND g1.receipt_required_flag = 'Y'

-- VENDOR, PO, INVOICE AND PAYMENT DETAILS
SELECT DISTINCT a.org_id "ORG ID", e.segment1 "VENDOR NUMBER",
e.vendor_name "VENDOR NAME",
UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
f.vendor_site_code "VENDOR SITE CODE",
TO_CHAR (TRUNC (d.creation_date)) "PO DATE",
d.segment1 "PO NUM", d.type_lookup_code "PO TYPE",
c.quantity_ordered "QTY ORDERED",
c.quantity_cancelled "QTY CANCELLED",
g.item_description "ITEM DESCRIPTION",
g.unit_price "UNIT PRICE",
(NVL (c.quantity_ordered, 0) - NVL (c.quantity_cancelled, 0)
)
* NVL (g.unit_price, 0) "PO LINE AMOUNT",
(SELECT DECODE (ph.approved_flag,
'Y', 'Approved'
)
FROM po.po_headers_all ph
WHERE ph.po_header_id = d.po_header_id) "PO APPROVED?",
a.invoice_type_lookup_code "INVOICE TYPE",
a.invoice_amount "INVOICE AMOUNT",
TO_CHAR (TRUNC (a.invoice_date)) "INVOICE DATE",
a.invoice_num "INVOICE NUMBER",
(SELECT DECODE (x.match_status_flag,
'A', 'Approved'
)
FROM ap.ap_invoice_distributions_all x
WHERE x.invoice_distribution_id = b.invoice_distribution_id)
"INVOICE APPROVED?",
a.amount_paid, h.check_id, i.check_number,
h.invoice_payment_id,
TO_CHAR (TRUNC (i.check_date)) "PAYMENT DATE"
FROM ap.ap_invoices_all a,
ap.ap_invoice_distributions_all b,
po.po_distributions_all c,
po.po_headers_all d,
ap.ap_suppliers e,
ap.ap_supplier_sites_all f,
po.po_lines_all g,
ap.ap_invoice_payments_all h,
ap.ap_checks_all i
WHERE a.invoice_id = b.invoice_id
AND b.po_distribution_id = c.po_distribution_id(+)
AND c.po_header_id = d.po_header_id(+)
AND e.vendor_id(+) = d.vendor_id
AND f.vendor_site_id(+) = d.vendor_site_id
AND d.po_header_id = g.po_header_id
AND c.po_line_id = g.po_line_id
AND a.invoice_id = h.invoice_id
AND h.check_id = i.check_id
AND c.po_header_id IS NOT NULL
AND a.payment_status_flag = 'Y'
AND d.type_lookup_code != 'BLANKET'
ORDER BY E.VENDOR_NAME

R12 Supplier Bank API's

In order to load Supplier external bank, branches and bank accounts, one needs to use the Oracle Supplied Package IBY_EXT_BANKACCT_PUB.

Below API's are used to create Bank and Branch

IBY_EXT_BANKACCT_PUB.create_ext_bank:

It is used to create the External Bank, please note that the Bank name and Home Country Name are mandatory for creating an External Bank. Once you create the Bank, Bank Party ID gets created and you can check it from IBY_EXT_BANKS_V view.

IBY_EXT_BANKACCT_PUB.create_ext_bank
(p_api_version => 1.0
,p_init_msg_list => FND_API.G_TRUE
,p_ext_bank_rec => x_bank_rec
,x_bank_id => x_bank_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_response => x_response_rec
);

IBY_EXT_BANKACCT_PUB.create_ext_bank_branch

It is used to create a Bank Branch, so that an account could be created in the same branch. Once a Bank Branch is created, a record gets inserted into IBY_EXT_BANK_BRANCHES_V view.

IBY_EXT_BANKACCT_PUB.create_ext_bank_branch
(p_api_version => 1.0
,p_init_msg_list => FND_API.G_TRUE
,p_ext_bank_branch_rec => x_bank_branch_rec
,x_branch_id => x_branch_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_response => x_response_rec
);

After the bank and branches are created, the table iby.iby_temp_ext_bank_accounts can be populated to create the bank accounts and associate to the supplier or supplier site

Note: Table must be populated prior to running the supplier interface

Below API's are used internally by oracle to create payee and associate bank account to supplier or supplier site.

IBY_EXT_BANKACCT_PUB.create_ext_bank_acct
(p_api_version => 1.0
,p_init_msg_list => FND_API.G_TRUE
,p_ext_bank_acct_rec => x_bank_acct_rec
,x_acct_id => x_acct_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_response => x_response_rec
);

IBY_DISBURSEMENT_SETUP_PUB.Create_External_Payee
(p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_ext_payee_tab => v_external_payee_tab_type,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data,
x_ext_payee_id_tab => x_ext_payee_id_tab,
x_ext_payee_status_tab => x_ext_payee_status_tab);

IBY_DISBURSEMENT_SETUP_PUB.Set_Payee_Instr_Assignment
(p_api_version => 1.0
,p_init_msg_list => FND_API.G_TRUE
,p_payee => x_rec
,p_assignment_attribs => x_assign
,x_assign_id => x_assign_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_response => x_response_rec);

R12 Bank Accounts- Supplier and Customer

- Banks and their Branches are now each stored as Parties (HZ_PARTIES) in their own right. They are linked together through Relationships(HZ_RELATIONSHIP). There is a separate link for both Bank to Branch and also from Branch to Bank.

- The Bank Accounts themselves are now stored in the new Oracle Payments Application

- Below are the Key tables where the Bank Account information is stored

IBY_EXTERNAL_PAYEES_ALL
IBY_EXTERNAL_PAYERS_ALL
IBY_EXT_BANK_ACCOUNTS
IBY_PMT_INSTR_USES_ALL

- Below are the Key tables where the Bank Data of R12 TCA is stored

HZ_PARTIES
HZ_PARTY_SITES
HZ_LOCATIONS
HZ_ORGANIZATION_PROFILES
HZ_CONTACT_POINTS
HZ_ORG_CONTACT
HZ_ORG_CONTACT_ROLES

• The following query gives you the links required for matching a Bank Account to its Supplier Site Record

SELECT party_supp.party_name supplier_name
, aps.segment1 supplier_number
, ass.vendor_site_code supplier_site
, ieb.bank_account_num
, ieb.bank_account_name
, party_bank.party_name bank_name
, branch_prof.bank_or_branch_number bank_number
, party_branch.party_name branch_name
, branch_prof.bank_or_branch_number branch_number
FROM hz_parties party_supp
, ap_suppliers aps
, hz_party_sites site_supp
, ap_supplier_sites_all ass
, iby_external_payees_all iep
, iby_pmt_instr_uses_all ipi
, iby_ext_bank_accounts ieb
, hz_parties party_bank
, hz_parties party_branch
, hz_organization_profiles bank_prof
, hz_organization_profiles branch_prof
WHERE party_supp.party_id = aps.party_id
AND party_supp.party_id = site_supp.party_id
AND site_supp.party_site_id = ass.party_site_id
AND ass.vendor_id = aps.vendor_id
AND iep.payee_party_id = party_supp.party_id
AND iep.party_site_id = site_supp.party_site_id
AND iep.supplier_site_id = ass.vendor_site_id
AND iep.ext_payee_id = ipi.ext_pmt_party_id
AND ipi.instrument_id = ieb.ext_bank_account_id
AND ieb.bank_id = party_bank.party_id
AND ieb.bank_id = party_branch.party_id
AND party_branch.party_id = branch_prof.party_id
AND party_bank.party_id = bank_prof.party_id
ORDER BY party_supp.party_name
, ass.vendor_site_code;

• The following query gives you the links required for matching a Bank Account to its Customer Site Record:

SELECT cust.party_name customer_name
, cust_acct.account_number
, cust_uses.site_use_code
, cust_loc.address1
, cust_loc.address2
, cust_loc.address3
, cust_loc.address4
, cust_loc.city
, cust_loc.postal_code
, bank.party_name bank_name
, bank_prof.home_country
, branch.party_name branch_name
, branch_prof.bank_or_branch_number branch_number
, account.bank_account_num
, account.bank_account_name
FROM hz_parties bank
, hz_relationships rel
, hz_parties branch
, hz_organization_profiles bank_prof
, hz_organization_profiles branch_prof
, iby_ext_bank_accounts account
, iby_account_owners acc_owner
, iby_external_payers_all ext_payer
, iby_pmt_instr_uses_all acc_instr
, hz_parties cust
, hz_cust_accounts cust_acct
, hz_cust_acct_sites_all cust_site
, hz_cust_site_uses_all cust_uses
, hz_locations cust_loc
WHERE 1=1
AND bank.party_id = rel.object_id
and bank.party_type = rel.object_type
AND rel.object_table_name = 'HZ_PARTIES'
AND rel.relationship_code = 'BRANCH_OF'
AND rel.subject_id = branch.party_id
AND rel.subject_type = branch.party_type
AND rel.subject_table_name = 'HZ_PARTIES'
AND bank.party_id = bank_prof.party_id
AND branch.party_id = branch_prof.party_id
AND bank.party_id = account.bank_id
AND branch.party_id = account.branch_id
AND account.ext_bank_account_id = acc_owner.ext_bank_account_id
AND acc_owner.account_owner_party_id = cust.party_id
AND account.ext_bank_account_id = acc_instr.instrument_id
AND acc_instr.ext_pmt_party_id = ext_payer.ext_payer_id
AND ext_payer.cust_account_id = cust_acct.cust_account_id
AND cust_acct.cust_account_id = cust_site.cust_account_id
AND cust_site.cust_acct_site_id = cust_uses.cust_acct_site_id
AND cust_uses.site_use_id = ext_payer.acct_site_use_id
AND cust_uses.location = cust_loc.location_id
AND cust.party_id = cust_acct.party_id;

Check Printing in R12

Login to XML Publisher Administrator, download below template and start customizing report. IBYDC_STD1.rtf file is the initial starting point for Check printing report customization in R12

Template = Standard Check Format

Data Definition = Oracle Payments Funds Disbursement Payment Instruction Extract 1.0

Code = IBY_FD_INSTRUCTION_1_0

File = IBYDC_STD1.rtf


There are always lot of challenges to design check printing report as per clients requirement. I tried to cover most of them below:

1) Below link specifies all steps required for MICR Font Setup on our desktop and application server

http://www.centroid.com/Blog/oracle/check-printing-using-micr-fonts-in-r12/

For more details, refer to Metalink Note- 312353.1. This document describes how to set up the MICR font used in check printing.

2) Perform below steps to add signature in your RTF Template

a) Insert Picture tag in your template and click on Tab 'Web'
b) Insert url in that tab (Example given in separate article)
c) Finally copy your signature file Sample_Sig.gif in $OA_MEDIA top at Application server

You can refer separate article on this RTF Template- Signature Printing

3) In order to fix total number of rows on each page and then do pagebreak, go thru below link

http://apps2fusion.com/apps/apps/155-xml-publisher-developing-reports-printed-on-pre-printed-stationary

AP Payment Testing in R12

1) Login to Payables Manager and Navigate to Payments > Entry > Payments Manager

2) Click on Payment Process Request Tab and then click submit single request button. Specify PPR Name and fill all values and click on submit button. Alternatively, you can also create template with all values such as payment attributes, payment priority, pay thru days..etc and attach it while creating PPR. Once request is submitted, it submits program- Auto Select (Payment Process Request Program) in SRS window.

3) Go back to PPR tab and search for your request by name or date. Click on Refresh Status Button until Status=Invoices Pending Review and Start Action button turns green.

4) Click on Start Action, review payments and click on submit button. It ran multiple programs in SRS like- Scheduled Payment Selection Report, Build Payments and Format Payment Instructions

5) Go back to PPR tab again and search for your request. Click on Refresh Status Button until Status=Formatted.

6) Review the output file for Format Payment Instructions. It will be XML template output attached to Data Definition=Oracle Payments Funds Disbursement Payment Instruction Extract 1.0

7) Finally Click on Start Action, choose printer and submit check for printing. Status will become Printed

Note: AP Payment has been moved to new module in R12 called as iPayments. All base tables starts with IBY in R12. Below are the key tables in R12 for Payments

IBY_DOCS_PAYABLE_ALL

IBY_PAYMENTS_ALL

IBY_PAY_SERVICE_REQUESTS

IBY_PAY_INSTRUCTIONS_ALL
IBY_PMT_INSTR_USES_ALL

AP Payment Administrator Setup in R12

1) Login to Payables Manager and Navigate to Setup > Payment > Payment Administrator

2) Click on XML Publisher format Template and create XML Template with below details. Attach required RTF Template for Check Printing
Data Definition = Oracle Payments Funds Disbursement Payment Instruction Extract 1.0
Code = IBY_FD_INSTRUCTION_1_0
3) Create Format of Type=Disbursement Payment Instruction and attach XML Template to it
Note- If you want to get XML output, attach XML Template= Extract Identity to your Format and run through complete Payment process request. XML code comes as a single line in output file.

4) Create Payment Process Profile (PPP) and attach above Payment Instruction Format to it.

5) Now Navigate to Setup > Payment > Bank and Bank Branches and Create Bank and Branch

6) Finally Navigate to Setup > Payment > Bank Accounts and create Bank Account with above Bank and Branch defined. Then click on Manage Payment Documents Tab and attach Payment Instruction Format defined above to Bank Account.