Friday, 6 June 2008

Part 2: 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’:

Free Image Hosting at www.ImageShack.us

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 Instruction
This 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.


Here is an example Payments file viewed using XML notepad:

Free Image Hosting at www.ImageShack.us

Additional XML tags can be added at each of these 4 levels by coding different PL/SQL functions in IBY_FD_EXTRACT_EXT_PUB.
The following table lists the functions you need to modify to add additional tags to each level of the XML file:



XML File LevelIBY_FD_EXTRACT_EXT_PUB Function To ModifyExample of Parameter Usage
OutboundPaymentInstructionGet_Ins_Ext_Agg(p_payment_instruction_id IN NUMBER)SELECT *
FROM iby_pay_instructions_all
WHERE payment_instruction_id = p_payment_instruction_id;
OutboundPaymentGet_Pmt_Ext_Agg(p_payment_id IN NUMBER)SELECT *
FROM iby_payments_all ipa
WHERE ipa.payment_id = p_payment_id;
DocumentPayableGet_Doc_Ext_Agg(p_document_payable_id IN NUMBER)SELECT *
FROM iby_docs_payable_all dp WHERE dp.document_payable_id = P_document_payable_id;
DocumentPayableLineGet_Docline_Ext_Agg(p_document_payable_id IN NUMBER, p_line_number IN NUMBER)
PaymentProcessProfileGet_Ppr_Ext_Agg(p_payment_service_request_id IN NUMBER)SELECT *
FROM iby_pay_service_requests WHERE payment_service_request_id = p_payment_service_request_id;

As shown in the example below, it is best practice to group all of your custom tags within a parent tag called 'Extend'

EXAMPLE:

The following is an example of adding an 'Extend' tag and some additional invoice data to the ‘DocumentPayable’ level of the XML file:

--
-- 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
v_att1 ap_invoices_all.attribute1%TYPE;
v_att2 ap_invoices_all.attribute2%TYPE;
v_att3 ap_invoices_all.attribute3%TYPE;
v_detail_line VARCHAR2(255);
v_result XMLTYPE;
BEGIN

SELECT ai.attribute1,ai.attribute2,ai.attribute3
INTO v_att1, v_att2, v_att3
FROM iby_docs_payable_all dp,ap_invoices_all ai
WHERE dp.calling_app_doc_unique_ref2 = ai.invoice_id
AND dp.document_payable_id = p_document_payable_id;

v_detail_line := xxcsd_build_inv_line(p_document_payable_id);

--now build the XML string
SELECT XMLConcat(XMLElement("Extend",
XMLElement("XXCSD_INVOICE_DFF1", v_att1),
XMLElement("XXCSD_INVOICE_DFF2", v_att2),
XMLElement("XXCSD_INVOICE_DFF3", v_att3),
XMLElement("XXCSD_INV_DETAIL", v_detail_line)
)
)
INTO v_result
FROM dual;

RETURN v_result;

END Get_Doc_Ext_Agg;



As you can see, I’ve added 4 new tags ‘XXCSD_INVOICE_DFF1’, ‘XXCSD_INVOICE_DFF2’, ‘XXCSD_INVOICE_DFF3’ and ‘XXCSD_INV_DETAIL’ to the XML File:

Free Image Hosting at www.ImageShack.us

These new XML tags can now be referenced in my XML Publisher presentation template and added to my cheque or electronic payment file layout.

For example, here is the tag ‘XXCSD_INV_DETAIL’ used in an ETEXT template:

6 comments:

Ben said...

Hey Kevin,

I would be interested in seeing (as in downloading) source XML, eText RTF and resultant flat files from this series of posts you're doing.

The Oracle documentation for the eText format is terrible.

Thanks!

Anonymous said...

.

Suresh said...

Thanks for a great article Kevin. I'm new to this and still learning the process and your article helped me a lot.

I have modified the first 3 functions of IBY_FD_EXTRACT_EXT_PUB...
- Get_Ins_Ext_Agg
- Get_Pmt_Ext_Agg
- Get_Doc_Ext_Agg
I have given "Extend" tag to indicate that these are customized tags.

But, when I see the xml output in iby_trxn_documents, the Extend Tag appears under different groupings... like under "OutboundPaymentInstruction" for the output from the first function, "OutboundPayment" for the next one and so forth. So creating an RTF template based on this is not displaying any data. Is there a way to accomplish this? Also, is there a way to generate only the information shown in the format template as an XML output? Thanks for all your help.

Jayesh said...

Hi,
I want is to publish AP Check output in PDF form in R12, similar to what we have in 11.5.10 like Evergreen Checks.
I got the data successfully loaded using the following thread:
http://kr.forums.oracle.com/forums/thread.jspa?threadID=556367
Now when I am trying to create a new XML Template, Template has been created successfully but the same template doesn’t appear in the LOV when I create a new format for payments.
I tried to add a RTF file using "add another file" but the output doesn’t reflect the change.
Could you please advise me how I can see the AP Check output using custom template in XML publisher.
Thanks,
Jayesh

Anil said...

Hi Kevin,

I have modified FUNCTION Get_Pmt_Ext_Agg but still custom xml tags are missing in the final xml output, Please let me know if we need to do any other setup.

Thanks,
Anil Kumar

repository said...

Hi Kevin,

This is a very helpful link. Keep going with such articles.

Thanks,
Kiran