Adding new fields to the R12 Payment Funds Disbursement XML file

IBY_FD_EXTRACT_EXT_PUB is a standard PL/SQL package that is used to extend (i.e. add additional tags to) the XML file generated during a R12 Oracle Payments ‘Payment Process Request’:



This XML file is then used as the data source for the XML Publisher cheque or electronic file presentation layout.

To understand how to use IBY_FD_EXTRACT_EXT_PUB, we have to understand the structure of the XML file created by the Payments process request.

There are 4 main levels to the file. These are:

Top Level: Outbound Payment InstructionThis is the top level of the XML File and there is one Outbound Payment Instruction per Payment process request.

Level 2: Outbound Payment:
This is the Payment Level i.e. an individual cheque or BACS payment amount to a supplier. There can be multiple Outbound Payments per Outbound Payment Instruction.

Level 3: Document Payable:
Details the documents (i.e. invoices) being paid. There can be multiple Document Payable tags per Outbound Payment

Level 4: Document Payable Line:This level details the invoice line. There can be multiple Document Payable Line tags per Document Payable.


CREATE OR REPLACE PACKAGE BODY APPS.iby_fd_extract_ext_pub
AS
/* $Header: ibyfdxeb.pls 120.2 2006/09/20 18:52:12 frzhang noship $ */
   --
   -- This API is called once only for the payment instruction.
   -- Implementor should construct the extract extension elements
   -- at the payment instruction level as a SQLX XML Aggregate
   -- and return the aggregate.
   --
   -- Below is an example implementation:
   FUNCTION get_ins_ext_agg (p_payment_instruction_id IN NUMBER)
      RETURN XMLTYPE
   IS
      l_ins_ext_agg   XMLTYPE;
      CURSOR l_ins_ext_csr (p_payment_instruction_id IN NUMBER)
      IS
         SELECT XMLCONCAT (XMLELEMENT ("Extend",
                                       --XMLElement("Name",sum(PAYMENT_AMOUNT)),
                                       XMLELEMENT ("CtrlSum",
                                                   SUM (payment_amount)
                                                  )
                                      )
                          )
           FROM iby_payments_all ext_table
          WHERE payment_instruction_id = p_payment_instruction_id;
   BEGIN
      OPEN l_ins_ext_csr (p_payment_instruction_id);
      FETCH l_ins_ext_csr
       INTO l_ins_ext_agg;
      CLOSE l_ins_ext_csr;
      RETURN l_ins_ext_agg;
   END get_ins_ext_agg;
   --
   -- This API is called once per payment.
   -- Implementor should construct the extract extension elements
   -- at the payment level as a SQLX XML Aggregate
   -- and return the aggregate.
   --
   FUNCTION get_pmt_ext_agg (p_payment_id IN NUMBER)
      RETURN XMLTYPE
   IS
      l_pmt_ext_agg   XMLTYPE;
      CURSOR l_pmt_ext_csr (p_payment_id IN NUMBER)
      IS
         SELECT XMLCONCAT
                   (XMLELEMENT ("PMTExtend",
                                XMLELEMENT ("CLEARSYSMEMID",
                                            DECODE (NVL (ibb.eft_user_number,
                                                         iby.ext_bank_number
                                                        ),
                                                    '', '',
                                                       'XXXXX'
                                                    || NVL
                                                          (ibb.eft_user_number,
                                                           iby.ext_bank_number
                                                          )
                                                   )
                                           )
                               )
                   )
           FROM iby_payments_all iby, iby_ext_bank_branches_v ibb
          WHERE payment_id = p_payment_id
            AND iby.ext_branch_number = ibb.branch_number;
   BEGIN
      OPEN l_pmt_ext_csr (p_payment_id);
      FETCH l_pmt_ext_csr
       INTO l_pmt_ext_agg;
      CLOSE l_pmt_ext_csr;
      RETURN l_pmt_ext_agg;
   END get_pmt_ext_agg;
   --
   -- This API is called once per document payable.
   -- Implementor should construct the extract extension elements
   -- at the document level as a SQLX XML Aggregate
   -- and return the aggregate.
   --
   FUNCTION get_doc_ext_agg (p_document_payable_id IN NUMBER)
      RETURN XMLTYPE
   IS
   BEGIN
      RETURN NULL;
   END get_doc_ext_agg;
   --
   -- This API is called once per document payable line.
   -- Implementor should construct the extract extension elements
   -- at the doc line level as a SQLX XML Aggregate
   -- and return the aggregate.
   --
   -- Parameters:
   --   p_document_payable_id: primary key of IBY iby_docs_payable_all table
   --   p_line_number: calling app doc line number. For AP this is
   --   ap_invoice_lines_all.line_number.
   --
   -- The combination of p_document_payable_id and p_line_number
   -- can uniquely locate a document line.
   -- For example if the calling product of a doc is AP
   -- p_document_payable_id can locate
   -- iby_docs_payable_all/ap_documents_payable.calling_app_doc_unique_ref2,
   -- which is ap_invoice_all.invoice_id. The combination of invoice_id and
   -- p_line_number will uniquely identify the doc line.
   --
   FUNCTION get_docline_ext_agg (
      p_document_payable_id   IN   NUMBER,
      p_line_number           IN   NUMBER
   )
      RETURN XMLTYPE
   IS
   BEGIN
      RETURN NULL;
   END get_docline_ext_agg;
   --
   -- This API is called once only for the payment process request.
   -- Implementor should construct the extract extension elements
   -- at the payment request level as a SQLX XML Aggregate
   -- and return the aggregate.
   --
   FUNCTION get_ppr_ext_agg (p_payment_service_request_id IN NUMBER)
      RETURN XMLTYPE
   IS
   BEGIN
      RETURN NULL;
   END get_ppr_ext_agg;
END iby_fd_extract_ext_pub;