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.
Advertisements

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]