AP Table Handler APIs

These APIs are handful when you want to do Insert, Update or Delete in some AP Base tables i.e. AP_INVOICES_ALL, AP_INVOICE_LINES_ALL, AP_INVOICE_DISTRIBUTIONS, AP_INVOICE_PAYMENTS and AP_CHECKS_ALL programmatically for some business requirements (rare cases!).

1] AP_AI_TABLE_HANDLER_PKG:

• Procedure Insert_Row:  Inserts a row in AP_INVOICES_ALL table.
• Procedure Update_Row:  Updates a row in AP_INVOICES_ALL table.
• Procedure Delete_Row:  Deletes a row in AP_INVOICES_ALL table. Also subsequently delete rows in the related tables like AP_INVOICE_LINES_ALL, AP_INVOICE_DISTRIBUTIONS_ALL, AP_PAYMENT_SCHEDULES_ALL, AP_HOLDS_ALL and AP_SELF_ASSESSED_TAX_DIST_ALL.

2] AP_AIL_TABLE_HANDLER_PKG:

• Procedure CHECK_UNIQUE: Check the Uniqueness of a Row.
• Procedure Insert_Row:  Inserts a row in AP_INVOICE_LINES_ALL table.
• Procedure Update_Row:  Updates a row in AP_INVOICE_LINES_ALL table.
• Procedure Delete_Row:  Deletes a row in AP_INVOICE_LINES_ALL table. Also subsequently delete rows in the related tables like AP_INVOICE_DISTRIBUTIONS_ALL.

3] AP_AID_TABLE_HANDLER_PKG:

• Procedure CHECK_UNIQUE: Check the Uniqueness of a Row.
• Procedure Insert_Row:  Inserts a row in AP_INVOICE_DISTRIBUTIONS table.
• Procedure Update_Row:  Updates a row in AP_INVOICE_DISTRIBUTIONS table.
• Procedure Delete_Row:  Deletes a row in AP_INVOICE_DISTRIBUTIONS table.

4] AP_AIP_TABLE_HANDLER_PKG:

• Procedure Insert_Row:  Inserts a row in AP_INVOICE_PAYMENTS table.
• Procedure Update_Amounts: Update amounts in AP_INVOICE_PAYMENTS table.

5] AP_AC_TABLE_HANDLER_PKG:

• Procedure Insert_Row:  Inserts a row in AP_CHECKS_ALL table.
• Procedure Update_Row:  Updates a row in AP_CHECKS table.
• Procedure Delete_Row:  Deletes a row in AP_CHECKS table.
• Procedure Update_Amounts: Update amounts in AP_CHECKS table.

Format Customization in Oracle Payments using Extensibility Utility Package

From Oracle Accounts Payable, user submits the invoices to Oracle Payments as a Payment Process Request. Oracle Payments uses the invoice information submitted in the Payment Request to create Documents Payable and then groups them into Payments and Payment Instructions for processing payments. This processed information is then recorded in the database tables. The processed payment information is retrieved by Oracle Payments database views to generate the XML extract. The generated XML extract is used in conjunction with the RTF/ETEXT template by Business Intelligence Publisher to generate output.

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. 

First determine which function within IBY_FD_EXTRACT_EXT_PUB should be modified. Then write a PL/SQL block similar to the structure given below into the package body function definition, replacing the lines “BEGIN” and “RETURN NULL”, and retaining all other lines:

   <local_xml_variable>  XMLTYPE;

    CURSOR <cursor_name> (<cursor_parameter_name> IN NUMBER) IS
    SELECT XMLConcat(
             XMLElement("Extend",
               XMLElement("Name", '<xml_tag_name1>'),
               XMLElement("Value", <xml_tag_value2>)),
             XMLElement("Extend",
               XMLElement("Name", '<xml_tag_name2>'),
               XMLElement("Value", < xml_tag_value2>))
           )
     FROM <data_table_name>
   WHERE <table_identifier_column> = <cursor_parameter_name>;

  BEGIN

    OPEN <cursor_name> (<function_parameter_name>);
    FETCH <cursor_name> INTO <local_xml_variable>;
    CLOSE <cursor_name>;

    RETURN <local_xml_variable>;

A sample xml file with extended tags will look like:

Then modify the eText template using the new custom elements. Use the following syntax to select the appropriate Name/Value pair.

…/Extend[Name='<xml_tag_name1>’]/Value

You can use BI Publisher Template Viewer to view the final eText Outputs.

Reference Articles in MOS:

• Format Customization in Oracle Payments for Oracle Applications Release 12 [ID 787467.1]
• R12: Can Users Customize XML Extract? [ID 457539.1]
• R12 Oracle Payments Processing ‘How To’ documents [ID 579132.1]
• R12: How To Assign/Modify XML Publisher Payment Templates [ID 414336.1]

FAQs in Account Payable

FAQs in Account Payable

 

How many types of Invoices we can create in Oracle Payables?
A. Standard
B. Debit Memo
C. Credit Memo
D. Pre-Payment
E. Expense Report
F. Withholding Tax Invoice
G. Miscellaneous Invoice

Describe the 4-Way Matching?
2 – Way Matching: The Invoice Amount Should match with PO, (Invoice with PO)
3 – Way Matching: The Receipts Should match with Invoice, (Invoice, PO with Receipts)
4 – Way Match: The Goods Should match with Inspection/Accepted (Invoice, PO, Inspections & Receipts)

2-Way (Invoice to Purchase Order)
Quantity billed vs. quantity ordered on shipment line
Invoice unit price vs. purchase order line unit price

3-Way (Invoice to Purchase Order and Receipt)
2-Way match criteria AND
Quantity billed vs. quantity received
Item = Receipt Required

4-Way (Invoice to Purchase Order and Receipt and Inspection)
3-Way match criteria AND
Quantity billed vs. quantity accepted
Note: Quantity accepted = (Quantity received – quantity rejected)
Item = Inspection Required

How many Holds we have?
System Holds: Tax, Quantity Match, Po amount with Invoice Amount
Manual Holds: Invoice Limit, Hold on Invoice

Can you Release Manual Holds? If Yes, How?
Yes. Holds – Release Holds

How many ways you can pay the Invoice Amount?
Apply in Full
Schedule Payments
Installments

What is the difference between Debit and Credit Memo?
Debit Memo will raise the Customer
Credit Memo will raise the Vendor

Can you create a Duplicate Vendor ?
No

Vendor Number Should be Generate Automatic or Manually?
Both, Manual And Automatic

How many key flexfields are there in Payables?
No key flexfields in PO,AP

What are Payment Terms?
Payables uses payment terms to automatically calculate due dates, discount dates, and discount amounts for each invoice you enter. Payment terms will default from the supplier site. If you need to change the payment terms and the terms you want to use are not on the list of values, you can define additional terms in the Payment Terms window.

What are the Payment Methods available?
• Check – You can pay with a manual payment, a Quick payment, or in a payment batch.
• Clearing – Used for recording invoice payments to internal suppliers.
• Electronic – You generate an electronic payment file that you deliver to your bank to create payments. Use Electronic if the invoice will be paid using EFT or EDI.
• Wire – Used to manually record a wire transfer of funds between your bank and your supplier’s bank.

What are the line types of an Invoice?
Item: Item lines capture the details of the goods and services billed on your invoice.
Freight: Freight lines capture the details of your freight charges.
Miscellaneous: Miscellaneous lines capture the details of other charges on your invoices such as installation or service.
Tax: Payables integrates with Oracle E-Business Tax to automatically determine and calculate the applicable tax lines for your invoices.

What are the Invoice Distribution Types?
Item
Freight
Miscellaneous
Tax
Withholding
Prepayment

What are Holds in AP?
Holds can be placed by the system and some holds can be placed manually. Holds prevent payment and, sometimes, accounting of an invoice. Payables applies holds to invoices that fail the Invoice Validation process. Release holds from invoices to allow payment and accounting entry creation for the invoices.

What are the Interface Tables in AP?
AP_INVOICES_INTERFACE
AP_INVOICE_LINES_INTERFACE
AP_INTERFACE_CONTROLS
————————————–
AP_SUPPLIERS_INT
AP_SUPPLIER_SITES_INT
AP_SUP_SITE_CONTACT_INT
AP_SUPPLIER_INT_REJECTIONS

What is the API to cancel single AP Invoice?
AP_CANCEL_PKG.AP_CANCEL_SINGLE_INVOICE

What is the API to find invoice status?
AP_INVOICES_PKG.GET_APPROVAL_STATUS

Give some of the Oracle Payables interface?
a) Credit Card Transaction Interface
b) Invoice Import Interface
c) Payables Open Interface
d) Purchase Order Matching

What are the key tables where supplier bank information are stored?
IBY_EXTERNAL_PAYEES_ALL
IBY_EXTERNAL_PAYERS_ALL
IBY_EXT_BANK_ACCOUNTS
IBY_PMT_INSTR_USES_ALL

What is meant by with-holding tax invoice?
A separate tax invoice generated for withholding tax amount. Later this invoice will be knocked of when making the payment made to the authority.

What are all the Modules Interacting with AP?
Cash Management
Oracle iExpenses
General Ledger
Oracle Assets
Subledger Accounting (R12)
HRMS
Project Accounting
Purchasing/iprocurement
Global Accounting Engine (11i)

What are the mandatory setups in AP?
1- Financial Options
2- Define Suppliers
3- Define Payment Terms
4- Define Payment Methods
5- Define Banks and Banks Accounts And Banks Accounts Documents
6- Open AP Accounts Periods

What is pay date basis?
The Pay Date Basis for a supplier determines the pay date for a supplier’s invoices.
• Due
• Discount

What is the difference between quick payment and manual payment?

Quick Payment: It allows you to make a single payment against one or more invoices at a time to one supplier through payables.

Manual Payment: This is the process of entering the check details which has been paid manually in some emergency requirements into the payment form and selecting the invoices of the concerned supplier and check whether the total of the invoices and the paid amount at the header are same and save.

What is terms date basis?

Terms Date Basis is to calculate due date.

Due date is calculated 4way. Eg: payment term is 30days

• Due date = Sysdate + 30days
• Due date = Invoice date + 30days
• Due date = Goods Receive Date + 30days
• Due date = Invoice Received date + 30days

How you will transfer payables to general ledger?
When the payable accounting entries are created, then run the program called ‘Payables Transfer to GL’ Program, which sends the invoice entries and payable entries to GL interface. Then submit a request called Journal import to import journal entries to GL.

What are Aging Periods?
Aging periods are nothing but the periods that we setup to control and maintain the supplier outstanding bill towards the invoice. From this we can able to study the due date of the supplier form the generation of invoice.

What is a “Mixed” Invoice and how do I enter one?
Mixed Invoices are invoices or credit/debit memos for which you can perform both positive and negative matching to purchase orders and to other invoices.
For example, you can enter an invoice for -$100 with Invoice Type Mixed. You can match to an invoice for $-200, and match to a purchase order for $100.
To enter a Mixed invoice:
1. Enter the invoice or credit/debit memo in the Invoices Summary, and enter Mixed as the invoice Type. You can enter either a positive or negative invoice amount.
2. Match to purchase orders, and/or invoices.

AP-SLA-GL Link Query

AP-SLA-GL Link Query

SELECT
    aia.INVOICE_ID "Invoice Id",
    aia.INVOICE_NUM "Invoice Number",
    aia.INVOICE_DATE "Invoice Date",
    aia.INVOICE_AMOUNT "Amount",
    xal.ENTERED_DR "Entered DR in SLA",
    xal.ENTERED_CR "Entered CR in SLA",
    xal.ACCOUNTED_DR "Accounted DR in SLA",
    xal.ACCOUNTED_CR "Accounted CR in SLA",
    gjl.ENTERED_DR "Entered DR in GL",
    gjl.ACCOUNTED_DR "Accounted DR in GL",
    xal.ACCOUNTING_CLASS_CODE "Accounting Class",
    gcc.SEGMENT1||'.'||gcc.SEGMENT2||'.'
        ||gcc.SEGMENT3||'.'||gcc.SEGMENT4||'.'
        ||gcc.SEGMENT5||'.'||gcc.SEGMENT6||'.'
        ||gcc.SEGMENT7 "Code Combination",
    aia.INVOICE_CURRENCY_CODE "Inv Curr Code",
    aia.PAYMENT_CURRENCY_CODE "Pay Curr Code",
    aia.GL_DATE "GL Date",
    xah.PERIOD_NAME "Period",
    aia.PAYMENT_METHOD_CODE "Payment Method",
    aia.VENDOR_ID "Vendor Id",
    aps.VENDOR_NAME "Vendor Name",
    xah.JE_CATEGORY_NAME "JE Category Name"
FROM
    apps.ap_invoices_all aia,
    xla.xla_transaction_entities XTE,
    apps.xla_events xev,
    apps.xla_ae_headers XAH,
    apps.xla_ae_lines XAL,
    apps.GL_IMPORT_REFERENCES gir,
    apps.gl_je_headers gjh,
    apps.gl_je_lines  gjl,
    apps.gl_code_combinations gcc,
    apps.ap_suppliers aps,
    (select aid1.invoice_id,
            pa.project_id,
            nvl(pa.segment1,'NO PROJECT') Project
    from    apps.ap_invoice_distributions_all aid1,
            apps.PA_PROJECTS_ALL pa
    where aid1.rowid in
        (select MAx(rowid)
        from apps.ap_invoice_distributions_all aid2
        where aid1.INvoice_ID=aid2.INvoice_ID
        group by aid1.invoice_id)
    and aid1.project_id=pa.project_id(+)) sql1,
    (select aid1.invoice_id,
            pt.task_id,
            nvl(pt.task_number,'NO TASK') Task
    from    apps.ap_invoice_distributions_all aid1,
            apps.PA_TASKS pt
    where aid1.rowid in
        (select MAx(rowid)
        from apps.ap_invoice_distributions_all aid2
        where aid1.INvoice_ID=aid2.INvoice_ID
        group by aid1.invoice_id)
    and aid1.task_id=pt.task_id(+)) sql2
WHERE
    aia.INVOICE_ID = xte.source_id_int_1
    and aia.INVOICE_ID=sql1.Invoice_ID
    and aia.INVOICE_ID=sql2.Invoice_ID
    and xev.entity_id= xte.entity_id
    and xah.entity_id= xte.entity_id
    and xah.event_id= xev.event_id
    and XAH.ae_header_id = XAL.ae_header_id
    and XAH.je_category_name = 'Purchase Invoices'
    and XAH.gl_transfer_status_code= 'Y'
    and XAL.GL_SL_LINK_ID=gir.GL_SL_LINK_ID
    and gir.GL_SL_LINK_TABLE = xal.GL_SL_LINK_TABLE
    and gjl.JE_HEADER_ID=gjh.JE_HEADER_ID
    and gjh.JE_HEADER_ID=gir.JE_HEADER_ID
    and gjl.JE_HEADER_ID=gir.JE_HEADER_ID
    and gir.JE_LINE_NUM=gjl.JE_LINE_NUM
    and gcc.CODE_COMBINATION_ID=XAL.CODE_COMBINATION_ID
    and gcc.CODE_COMBINATION_ID=gjl.CODE_COMBINATION_ID
    and aia.VENDOR_ID=aps.VENDOR_ID
    and gjh.STATUS='P'
    and gjh.Actual_flag='A'
    and gjh.CURRENCY_CODE='USD'
    and aia.Invoice_id=&Invoice_Id;

Supplier Conversion in R12

Supplier Conversion in R12 

Vendor conversion program will load the Supplier Master, Sites and Contacts data from data files to the staging tables, validate the data and then load the data into Interface tables, finally Validated data will import into Oracle Supplier Standard Tables by using Oracle Standard Supplier Import Programs.

Pre-requisites setup’s are: Payment terms, Pay Groups, CCID, Supplier classifications, Bank Accounts , Employees (if employees have to set up as vendors).

The Interface Tables are:

• AP_SUPPLIERS_INT
• AP_SUPPLIER_SITES_INT
• AP_SUP_SITE_CONTACT_INT

AP_SUPPLIERS_INT:

This is the open interface table for AP Suppliers. It holds Supplier information which is loaded by the user for import. The columns in the table map to corresponding columns in the PO_VENDORS table. The Oracle Payables application uses this information to create a new Supplier record when the Supplier Open Interface Import program is submitted. Each row in the table will be identified by a unique identifier, the VENDOR_INTERFACE_ID.

Mandatory Columns:

• VENDOR_INTERFACE_ID (ap_suppliers_int_s.NEXTVAL)- Supplier interface record unique identifier
• VENDOR_NAME – Supplier name

Other important columns:

• SEGMENT1 – Supplier Number
• VENDOR_TYPE_LOOKUP_CODE – Supplier type
• SHIP_TO_LOCATION_CODE – Default ship-to-location name
• BILL_TO_LOCATION_CODE – Default bill-to-location name
• TERMS_NAME – Payment terms name
• TAX_VERIFICATION_DATE – Tax verification date(1099)
• VAT_REGISTRATION_NUM – Tax registration number
• ATTRIBUTE1 -15 – Descriptive Flexfield Segments
• PAY_GROUP_LOOKUP_CODE – Payment group type
• INVOICE_CURRENCY_CODE – Default currency unique identifier
• PAYMENT_CURRENCY_CODE – Default payment currency unique identifier
• NUM_1099 – Tax identification number (1099)
• VAT_CODE – Default invoice tax code
• HOLD_FLAG – Indicates whether or not the supplier is on purchasing hold
• SUMMARY_FLAG – Key flexfield summary flag
• ENABLED_FLAG – Key flexfield enable flag
• EMPLOYEE_ID – Employee unique identifier if supplier is an employee

AP_SUPPLIER_SITES_INT:

This is the open interface table for AP Supplier Sites. It holds Supplier Site information which is loaded by the user for import. The columns in the table map to corresponding columns in PO_VENDOR_SITES_ALL table. The Oracle Payables application uses this information to create a new Supplier Site record when the Supplier Sites Open Interface Import program is submitted. Each row in the table will be joined to the appropriate Supplier using the VENDOR_ID column.

Mandatory Columns:

• VENDOR_SITE_INTERFACE_ID (ap_supplier_sites_int_s.NEXTVAL) – Supplier Site interface record unique identifier
• VENDOR_SITE_CODE – Supplier Site name

Other important columns:

• ADDRESS_LINE1 – First line of supplier address
• ADDRESS_LINE2 – Second line of supplier address
• ADDRESS_LINE3 – Third line of supplier address
• CITY – City name
• STATE – State name or abbreviation
• ZIP – Postal code
• COUNTRY – Country name
• PHONE – Phone number
• FAX – Supplier site facsimile number
• SHIP_TO_LOCATION_CODE – Default ship-to-location name
• BILL_TO_LOCATION_CODE – Default bill-to-location name
• PAYMENT_METHOD_LOOKUP_CODE – Default payment method type
• VAT_CODE – Invoice default tax code
• PAY_GROUP_LOOKUP_CODE – Payment group type
• TERMS_NAME – Payment terms name
• INVOICE_CURRENCY_CODE – Default currency unique identifier
• PAYMENT_CURRENCY_CODE – Default payment currency unique identifier
• EMAIL_ADDRESS – E-mail address of the supplier contact
• PURCHASING_SITE_FLAG – Indicates whether purchasing is allowed from this site
• AUTO_TAX_CALC_FLAG – Level of automatic tax calculation for supplier
• HOLD_ALL_PAYMENTS_FLAG – Indicates if Oracle Payables should place payments for this supplier on hold

AP_SUP_SITE_CONTACT_INT:

This is the open interface table for AP Supplier Site Contacts. It holds Supplier contact data. The columns in the table map to corresponding columns in PO_VENDOR_CONTACTS table. The Oracle Payables application uses this information to create a new Supplier Contact record when the Supplier Sites Open Interface Import program is submitted. Each row in the table will be joined to the appropriate Supplier Site using the VENDOR_SITE_CODE  and ORG_ID.

Mandatory Columns:

• VENDOR_INTERFACE_ID – Supplier interface record unique identifier
• VENDOR_CONTACT_INTERFACE_ID (AP_SUP_SITE_CONTACT_INT_S.NEXTVAL) – Vendor Contact Interface Identifier
• VENDOR_SITE_CODE – Supplier Site name

Other important columns:

• FIRST_NAME – Contact First name
• LAST_NAME – Contact last name
• AREA_CODE – Area code of contact phone number
• PHONE – Contact phone number
• FIRST_NAME_ALT – Alternate Supplier contact first name
• LAST_NAME_ALT – Alternate Supplier contact last name
• EMAIL_ADDRESS – Email address for the Supplier Site contact
• FAX – Facsimile number for the Supplier Site contact
• VENDOR_ID – Supplier unique identifier

Validations:

• Vendor Number (Check for duplicate records in ap_suppliers table)
• Vendor Name (Check for duplicate records in staging as well as in ap_suppliers table)
• Terms Name (Check for proper record in ap_terms_tl table)
• Pay Group (Check for proper record in fnd_lookup_values_vl table where lookup_type = ‘PAY GROUP’)
• Employee Id (Check for proper employee record in per_all_people_f table)
• Vendor Type (Check for proper record in po_lookup_codes table where lookup_type = ‘VENDOR TYPE’)
• Vendor Site Code (Check for duplicate records in ap_supplier_sites_all table)
• Country Code ( Check for proper country code in fnd_territories_vl table)
• Payment Method (Check for proper payment method in iby_payment_methods_vl table)

Interface programs:

1. Supplier Open Interface Import
2. Supplier Sites Open Interface Import
3. Supplier Site Contacts Open Interface Import

The data inserted via these interfaces are automatically populated into TCA tables.

Note: AP_SUPPLIER_INT_REJECTIONS table contains suppliers, sites, contacts rejections information.

AP Invoice Types

AP Invoice Types

1] Standard

An invoice from a supplier representing an amount due for goods or services purchased. Standard invoices can be either matched to a purchase order or not matched.

2] Credit Memo

A memo from a supplier representing a credit amount toward goods or services.

3] Debit Memo

An invoice you enter to record a credit for a supplier who does not send you a credit memo.

4] Mixed

An invoice type you enter for matching to both purchase orders and invoices. You can enter either a positive or a negative amount for a Mixed invoice type.

5] Prepayment

A type of invoice you enter to pay an advance payment for expenses to a supplier or employee.

6] Expense Report

An invoice representing an amount due to an employee for business-related expenses.

7] Withholding Tax

An invoice you enter to remit taxes withheld to the appropriate tax authority.

8] Interest

If you allow interest invoices, payables will automatically calculate interest for overdue invoices and create interest invoices for selected suppliers.

9] Retainage Release

Invoices created for complex work and advance contract financing.

10] Transportation Invoices

Invoices from freight payment

Supplier API in R12

Supplier API in R12

The Following API’s are used in oracle apps R12  to upload supplier data :

API Header : POS_VENDOR_PUB_PKG.CREATE_VENDOR ( parameters)

PROCEDURE Create_Vendor(
p_vendor_rec IN AP_VENDOR_PUB_PKG.r_vendor_rec_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_vendor_id OUT NOCOPY NUMBER,
x_party_id OUT NOCOPY NUMBER
);

SCRIPT:

DECLARE

l_vendor_rec 	ap_vendor_pub_pkg.r_vendor_rec_type;
l_return_status 	VARCHAR2(10);
l_msg_count 	NUMBER;
l_msg_data 	VARCHAR2(1000);
l_vendor_id	NUMBER;
l_party_id 	NUMBER;

BEGIN

--Required
l_vendor_rec.segment1 := '00002359'; --ID
l_vendor_rec.vendor_name := 'ABC'; --Supplier Name
--Optional
l_vendor_rec.match_option:='R' ;

pos_vendor_pub_pkg.create_vendor
( 
p_vendor_rec => l_vendor_rec, 
x_return_status => l_return_status, 
x_msg_count => l_msg_count,
x_msg_data => l_msg_data, 
x_vendor_id => l_vendor_id, 
x_party_id => l_party_id
);

COMMIT;

dbms_output.put_line('return_status: '||l_return_status);
dbms_output.put_line('msg_data: '||l_msg_data);
dbms_output.put_line('vendor_id: '||l_vendor_id);
dbms_output.put_line('party_id: '||l_party_id);

END;

 

API Sites: POS_VENDOR_PUB_PKG.CREATE_VENDOR_SITE( parameters)
PROCEDURE Create_Vendor_Site(

p_vendor_site_rec IN AP_VENDOR_PUB_PKG.r_vendor_site_rec_type,

x_return_status OUT NOCOPY VARCHAR2,

x_msg_count OUT NOCOPY NUMBER,

x_msg_data OUT NOCOPY VARCHAR2,

x_vendor_site_id OUT NOCOPY NUMBER,

x_party_site_id OUT NOCOPY NUMBER,

x_location_id OUT NOCOPY NUMBER

);
Script:

DECLARE

l_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;
l_return_status 	VARCHAR2(10);
l_msg_count 	NUMBER;
l_msg_data 	VARCHAR2(1000);
l_vendor_site_id 	NUMBER;
l_party_site_id 	NUMBER;
l_location_id 	NUMBER;

BEGIN

--Required
l_vendor_site_rec.vendor_id :='123456';
l_vendor_site_rec.vendor_site_code := 'xyz';
l_vendor_site_rec.address_line1 := 'A-602';
l_vendor_site_rec.country := 'IN';
l_vendor_site_rec.org_id := '80';

--Optional

l_vendor_site_rec.purchasing_site_flag:='Y';
l_vendor_site_rec.pay_site_flag :='Y';
l_vendor_site_rec.rfq_only_site_flag :='N';
l_vendor_site_rec.phone := '67615006';

pos_vendor_pub_pkg.create_vendor_site
(
p_vendor_site_rec => l_vendor_site_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_vendor_site_id => l_vendor_site_id,
x_party_site_id => l_party_site_id,
x_location_id => l_location_id
);

COMMIT;

dbms_output.put_line('return_status: '||l_return_status);
dbms_output.put_line('msg_data: '||l_msg_data);
dbms_output.put_line('vendor_site_id: '||l_vendor_site_id);
dbms_output.put_line('party_site_id: '||l_party_site_id);
dbms_output.put_line('location_id: '||l_location_id);

END;


 

Other procedures of this package are:
POS_VENDOR_PUB_PKG.Update_Vendor

POS_VENDOR_PUB_PKG.Validate_Vendor

POS_VENDOR_PUB_PKG.Update_Vendor_Site

POS_VENDOR_PUB_PKG.Validate_Vendor_Site

POS_VENDOR_PUB_PKG.Create_Vendor_Contact
 


				

R12 Supplier Bank Accounts

R12 Supplier Bank Accounts

In Release 12, Payables Supplier Bank Information is stored somewhere else instead of PO_VENDORS table as in 11i. The supplier (or External) bank account information are stored in the table called IBY_EXT_BANK_ACCOUNTS. The bank and bank branches information are stored in the table HZ_PARTIES. They are linked together through Relationships (in HZ_RELATIONSHIP). There is a separate link for both Bank to Branch and also from Branch to Bank. Bank sites and Location information are stored in tables: HZ_PARTY_SITES and HZ_LOCATIONS. The bank_id and branch_id fields of IBY_EXT_BANK_ACCOUNTS table link the Bank Account to the relevant Bank and Branch Parties in the HZ_PARTIES table (IBY_EXT_BANK_ACCOUNTS.BANK_id = hz_paties.party_id).

Supplier (or External) bank accounts are created in Payables, in the Supplier Entry forms. Navigate to Suppliers -> Entry. Query or create your supplier. Click on Banking Details and then choose Create. After you have created the bank account, you can assign the bank account to the supplier site.

When the Bank is assigned to Vendors then it will be updated in a table called HZ_CODE_ASSIGNMENTS. Payment related details of supplier are also inserted in iby_external_payees_all as well as iby_ext_party_pmt_mthds.

The bank branch number can be found in the table: HZ_ORGANIZATION_PROFILES .The HZ_ORGANIZATION_PROFILES table stores a variety of information about a party. This table gets populated when a party of the Organization type is created. IBY_EXTERNAL_PAYEES_ALL stores payment-related attributes for the funds disbursement payment process for external party payees.

Queries:

SELECT  aps.vendor_name "VERDOR NAME",
        apss.vendor_site_code "VENDOR SITE CODE",
        ieb.bank_name "BANK NAME",
        iebb.bank_branch_name "BANK BRANCH NAME",
        iebb.branch_number "BRANCH NUMBER",
        ieba.BANK_ACCOUNT_NUM "BANK ACCOUNT NUMBER",
        ieba.BANK_ACCOUNT_NAME "BANK ACCOUNT NAME"
FROM    ap.ap_suppliers aps,
        ap.ap_supplier_sites_all apss,
        apps.iby_ext_bank_accounts ieba,
        apps.iby_account_owners iao,
        apps.iby_ext_banks_v ieb,
        apps.iby_ext_bank_branches_v iebb
WHERE   aps.vendor_id = apss.vendor_id
        and iao.account_owner_party_id = aps.party_id
        and ieba.ext_bank_account_id = iao.ext_bank_account_id
        and ieb.bank_party_id = iebb.bank_party_id
        and ieba.branch_id = iebb.branch_party_id
        and ieba.bank_id = ieb.bank_party_id;

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;

Supplier data in R12

Supplier data in R12

The below query will tell you about the various tables used to store supplier information and their links with different modules like TCA, AP, E-Business Tax and Payment.

SELECT hp.party_id
      ,hp.party_number
      ,hp.party_name
      ,hp.party_type
      ,hp.orig_system_reference
      ,hp.tax_reference
      ,hpua.party_usg_assignment_id
      ,hpua.party_usage_code
      ,hpua.effective_start_date
      ,hpua.effective_end_date
      ,hop.organization_profile_id
      ,hop.organization_name_phonetic
      ,hop.organization_type
      ,hps.party_site_id
      ,hps.party_site_number
      ,hps.orig_system_reference party_site_reference
      ,hps.party_site_name
      ,aps.vendor_id
      ,aps.vendor_name
      ,aps.vendor_name_alt
      ,aps.segment1
      ,aps.vendor_type_lookup_code
      ,aps.payment_currency_code
      ,aps.invoice_currency_code
      ,aps.hold_all_payments_flag
      ,aps.vat_registration_num
      ,aps.match_option
      ,apss.vendor_site_id
      ,apss.vendor_site_code
      ,apss.purchasing_site_flag
      ,apss.rfq_only_site_flag
      ,apss.pay_site_flag
      ,apss.address_line1
      ,apss.address_line2
      ,apss.zip
      ,apss.country
      ,apss.bill_to_location_id
      ,apss.accts_pay_code_combination_id
      ,iep.ext_payee_id
      ,iep.payment_function
      ,iep.org_type
      ,iep.default_payment_method_code
      ,iep.remit_advice_delivery_method
      ,iep.remit_advice_fax
      ,zxtp.party_type_code
      ,zxtp.rep_registration_number

FROM   hz_parties hp
      ,hz_party_usg_assignments hpua
      ,hz_organization_profiles hop
      ,hz_party_sites hps
      ,ap_suppliers aps
      ,ap_supplier_sites_all apss
      ,iby_external_payees_all iep
      ,zx_party_tax_profile zxtp

WHERE  1 = 1
AND    hp.party_id        = hpua.party_id
AND    hp.party_id        = hop.party_id
AND    hp.party_id        = hps.party_id
AND    hp.party_id        = aps.party_id
AND    aps.vendor_id      = apss.vendor_id
AND    hps.party_site_id  = apss.party_site_id
AND    hp.party_id        = iep.payee_party_id
AND    apss.vendor_site_id= iep.supplier_site_id
AND    hps.party_site_id  = iep.party_site_id
AND    hp.party_id        = zxtp.party_id
AND    hp.party_id        = &party_id;

How can you query the bank account associated with a Supplier Site in R12?

How can you query the bank account associated with a Supplier Site in R12?

In R12 a Supplier Site is stored, in TCA, as a Party_Site. The Party Site has the Party ID of the Party that represents the Supplier record.

SELECT BANK_ACCOUNT_NAME, BANK_ACCOUNT_NUM
FROM IBY_EXT_BANK_ACCOUNTS
WHERE EXT_BANK_ACCOUNT_ID IN
(SELECT EXT_BANK_ACCOUNT_ID
FROM IBY_ACCOUNT_OWNERS
WHERE ACCOUNT_OWNER_PARTY_ID IN
(SELECT party_id
FROM hz_party_sites
WHERE party_site_name = ‘<site name>’));