How to Change AP Formats in R12 Robert Rentz – Cambridge Solutions
How to Change AP Formats in R12 Robert Rentz – [email protected] Christine Courty – [email protected] Cambridge SolutionsM Agenda Introduction Concepts Back in 11i… Template Setup Data Definition View Changes BI Publisher Format File Conclusion Cambridge Solutions 2 Introduction In R12, Payment documents are delivered through BI Publisher (Also known as XML Publisher) Oracle Reports is no longer used XSD Data Definition is configured through Views in the IBY schema Custom fields are added to these views using functions that append data to the XSD Oracle provides a package where you can add your custom fields (e.g. Attribute fields) Payment Format process creates the XSD based on the payment parameters and calls the BI template Cambridge Solutions 3 Concepts 1. Data Definition 1. 2. Format File 1. 3. XML Data Definition File containing the structure and data MS Word RTF file created by BI Publisher that uses the Data Definition to create a specific output format Template 1. Definition of the link between a Data Definition and a Format File that is linked to a Payment Format Cambridge Solutions 4 Concepts 1. Create the templates for your payment outputs 1. Can use Payment Setups but XML Publisher Administrator is also available. 2. Create your BI Format File using BI Publisher and MS Word 3. Import the format file into your template 4. Run the Payment Format process to get the sample Data Definition (XSD) 5. Modify your BI Format File to use the fields that you added 6. Re-import and save your new BI Format File Cambridge Solutions 5 Back in 11i… 1. You created a Payment Format using Oracle Reports 2. You linked that format to a payment type 3. You changed the Report Output to XML to generate the XSD 4. You developed your BI format file to use the template 5. You registered the Data Definition 6. You registered the Template with the BI Format File 7. When you run the Payment Format the output was generated in the BI format Cambridge Solutions 6 Template Setup Cambridge Solutions 7 Template Setup Cambridge Solutions 8 Template Setup –Template Definition Cambridge Solutions 9 Template Setup –Template Definition Cambridge Solutions 10 Template Setup – Define Format File Cambridge Solutions 11 Template Setup – Export Default Format File Cambridge Solutions 12 Template Setup – Export Default Format File Cambridge Solutions 13 Template Setup – Export Default Format File 1. 2. Save this to your local drive Assign it the name you want to use as your Format File Name Cambridge Solutions 14 Template Setup – Define Format File Cambridge Solutions 15 Template Setup – Define Format File Cambridge Solutions 16 Data Definition 1. Prepare an invoice for payment 2. Run the Payments Manager process to format a check 3. When the Pending Actions displays, go to “Monitor Requests” 4. View the Log of the “Format Payment Instructions” program Cambridge Solutions 17 Data Definition 1. Find the line beginning “<OutboundPaymentInstruction>” 2. Cut and paste the entire line and save as with a .XML extension 3. Save it in the directory where you will be developing your BI Format file Cambridge Solutions 18 View Changes To add additional fields to the default XML data definition: 1. 2. 3. Open the package “IBY_FD_EXTRACT_EXT_PUB” Create a cursor that uses the XML_CONCAT and XML_ELEMENT functions Save and compile the package Cambridge Solutions 19 View Changes – Using the Seeded Example FUNCTION Get_Docline_Ext_Agg(p_document_payable_id IN NUMBER, p_line_number IN NUMBER) RETURN XMLTYPE IS l_ins_ext_agg XMLTYPE; CURSOR l_ins_ext_csr IS SELECT XMLConcat( XMLElement("Extend", XMLElement("Name", 'HundredK'), XMLElement("Value", nhssc_positive_pay_pkg.words(apdl.line_gross_amount,1))), XMLElement("Extend", XMLElement("Name", 'TenK'), XMLElement("Value", nhssc_positive_pay_pkg.words(apdl.line_gross_amount,2))), XMLElement("Extend", XMLElement("Name", 'Thousand'), XMLElement("Value", nhssc_positive_pay_pkg.words(apdl.line_gross_amount,3))), XMLElement("Extend", XMLElement("Name", 'Hundred'), XMLElement("Value", nhssc_positive_pay_pkg.words(apdl.line_gross_amount,4))), XMLElement("Extend", XMLElement("Name", 'Tens'), XMLElement("Value", nhssc_positive_pay_pkg.words(apdl.line_gross_amount,5))), XMLElement("Extend", XMLElement("Name", 'Units'), XMLElement("Value", nhssc_positive_pay_pkg.words(apdl.line_gross_amount,6))) ) FROM ap_document_lines_v apdl, iby_docs_payable_all ibydoc, iby_pay_service_requests ibypsr WHERE ibydoc.document_payable_id = p_document_payable_id 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 ibydoc.calling_app_id = apdl.calling_app_id AND ibypsr.call_app_pay_service_req_code = apdl.call_app_pay_service_req_code AND ibydoc.payment_service_request_id = ibypsr.payment_service_request_id; BEGIN OPEN l_ins_ext_csr ; FETCH l_ins_ext_csr INTO l_ins_ext_agg; CLOSE l_ins_ext_csr; RETURN l_ins_ext_agg; END Get_Docline_Ext_Agg; Cambridge Solutions 20 View Changes – Using the Seeded Example <Extend> <Name>HundredK</Name> <Value>ZERO</Value> </Extend> Issues 1. <Extend> <Name>TenK</Name> <Value>ZERO</Value> </Extend> 2. <Extend> <Name>Thousand</Name> <Value>ONE</Value> </Extend> <Extend> 3. There is an “Extend” group tag and “Name” and “Value” tags for each custom field You would need to loop through each “Extend” and perform a “When” on the name to see if that is the “Value” you want to print Way too cumbersome <Name>Hundred</Name> <Value>TWO</Value> </Extend> <Extend> <Name>Tens</Name> <Value>THREE</Value> </Extend> <Extend> <Name>Units</Name> <Value>FOUR</Value> </Extend> Cambridge Solutions 21 View Changes – Recommended Example FUNCTION Get_Docline_Ext_Agg(p_document_payable_id IN NUMBER, p_line_number IN NUMBER) RETURN XMLTYPE IS l_ins_ext_agg XMLTYPE; CURSOR l_ins_ext_csr IS SELECT XMLConcat( XMLElement("Custom", XMLElement("HundredK",nhssc_positive_pay_pkg.words(apdl.line_gross_amount,1)), XMLElement("TenK", nhssc_positive_pay_pkg.words(apdl.line_gross_amount,2)), XMLElement("Thousand", nhssc_positive_pay_pkg.words(apdl.line_gross_amount,3)), XMLElement("Hundred", nhssc_positive_pay_pkg.words(apdl.line_gross_amount,4)), XMLElement("Tens", nhssc_positive_pay_pkg.words(apdl.line_gross_amount,5)), XMLElement("Units", nhssc_positive_pay_pkg.words(apdl.line_gross_amount,6)) ) ) FROM ap_document_lines_v apdl, iby_docs_payable_all ibydoc, iby_pay_service_requests ibypsr WHERE ibydoc.document_payable_id = p_document_payable_id 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 ibydoc.calling_app_id = apdl.calling_app_id AND ibypsr.call_app_pay_service_req_code = apdl.call_app_pay_service_req_code AND ibydoc.payment_service_request_id = ibypsr.payment_service_request_id; BEGIN OPEN l_ins_ext_csr ; FETCH l_ins_ext_csr INTO l_ins_ext_agg; CLOSE l_ins_ext_csr; RETURN l_ins_ext_agg; END Get_Docline_Ext_Agg; Cambridge Solutions 22 View Changes – Recommended Example <Custom> Better: <HundredK>ZERO</HundredK> <TenK>ZERO</TenK> 1. <Thousand>ONE</Thousand> <Hundred>TWO</Hundred> 2. <Tens>THREE</Tens> <Units>FOUR</Units> 3. Only one “Custom” tag Can reference the field directly No looping </Custom> Cambridge Solutions 23 BI Publisher Format File 1. Open BI Publisher 2. Open the format file you exported earlier and renamed 3. Open the XML data definition you copied from the “Format Payment Instructions” concurrent program 4. Modify / add the fields to this format file 5. Save and update the format file in the Format screen of the Template in Oracle Payments setup Cambridge Solutions 24 BI Publisher Format File Demonstration Cambridge Solutions 25 Conclusion and Tips 1. 2. 3. 4. 5. The R12 technical architecture is vastly different than 11i Get a copy of the XML Data Definition before thinking that you need to add fields Attribute fields are NOT in the XML Data Definition so you will need to add those if you want to display them on your payment documents Use a single invoice to test your changes. There is a limit of 1 million bytes that will print the XML Data Definition in the log file For testing purposes, void and recreate the same check. It bypasses the need to keep creating invoices to pay Cambridge Solutions 26 How to Change AP Formats in R12 Robert Rentz – [email protected] Christine Courty – [email protected] Cambridge SolutionsM
© Copyright 2025