GL daily Rates Import

Please validate the data and populate in to GL_DAILY_RATES_INTERFACE table.
After inserting the validate records, Please run the standard program.

Program - Daily Rates Import and Calculation


INSERT INTO GL_DAILY_RATES_INTERFACE
        (
         FROM_CURRENCY
        ,TO_CURRENCY
        ,FROM_CONVERSION_DATE
        ,TO_CONVERSION_DATE
        ,USER_CONVERSION_TYPE
        ,CONVERSION_RATE
        ,MODE_FLAG
        ,USER_ID
        )
        VALUES
        (CREC.FROM_CURRENCY
        ,CREC.TO_CURRENCY
        ,CREC.FROM_CONVERSION_DATE
        ,CREC.TO_CONVERSION_DATE
        ,L_USER_CONVERSION_TYPE
        ,CREC.CONVERSION_RATE
        ,'I'
        ,FND_PROFILE.VALUE('USER_ID')
        );

Error: Duplicate Row or Overlapping Date Range found.

When loading rates using Program - Daily Rates Import and Calculation, it is failing with the
following error.
Error: Duplicate Row or Overlapping Date Range found.


Alternately following script run as APPS should work as well. This trigger is only used for synchronizing exchange rate data with OPM tables. Thus setting trigger status as 'DISABLED' should not cause any issue for customers not using OPM.


ALTER TRIGGER GMF_GL_DAILY_RATES_BIUR_TG DISABLE;

Sending Email through PL/SQL

DECLARE 
v_From      VARCHAR2(80) := 'abc@mycompany.com'; 
v_Recipient VARCHAR2(80) := 'abc@mycompany.com'; 
v_Subject   VARCHAR2(80) := 'Hi subject'; 
v_Mail_Host VARCHAR2(30) := 'abc.mycompany.com'; 
v_Mail_Conn utl_smtp.Connection; 
crlf        VARCHAR2(2)  := chr(13)||chr(10);
BEGIN
v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);
utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);
utl_smtp.Mail(v_Mail_Conn, v_From);
utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);
utl_smtp.Data(v_Mail_Conn,   'Date: '   || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss')
                || crlf ||   'From: '   || v_From || crlf ||   'Subject: '|| v_Subject
    || crlf ||   'To: '     || v_Recipient || crlf ||   crlf ||   'some message text'|| crlf ||  
   -- Message body   'more message text'|| crlf );
utl_smtp.Quit(v_mail_conn);
EXCEPTION
WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then  
raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);
END;

Installing Oracle BI Publisher / XML Publisher Desktop

Getting Oracle BI Publisher Desktop: The current version of Oracle BI Publisher Desktop is available on Oracle Technology Network (OTN) at:http://www.oracle.com/technology/software/products/publishing/index.html
From this site, download Oracle XML Publisher Desktop 5.6.2 for Microsoft Windows. To use Oracle BI Publisher desktop, you must meet the following requirements:

•          Microsoft Windows 2000 or later

•          Microsoft Word 2000 or later

•          Java Runtime Environment (JRE) 1.4 or later

Getting Oracle BI Publisher Desktop for E-Business Suite:  The current version of Oracle BI Publisher Desktop is available on MetaLink in patch 5027437. http://updates.oracle.com/download/5027437.html. Always check MetaLink to see that you obtain the most current version. Versions are likely to change.

Installing Oracle BI Publisher Desktop: To install Oracle BI Publisher Desktop, perform the following basic steps:

•          Download the patch.

•          Unzip the patch.

•          Run setup.exe included with the patch.

•          Follow the onscreen instructions.

After this is successfully installed. we should be able to see the Add-ins tab in the menu.

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;

Viewing Output XSL Templates

This explains Viewing Output XSL Templates like RTF Templates

In notepad ++

got to - Plugins - Plugin Manager - Show Plugin Manager

Under

available tab select XML Tools and install - restart notepad ++

Open the XML data file from any of the request in notepad ++ - go to Plugins - XML Tools - XML Transformation - select XSL (will prompt you to chose the xsl file) - click transform

This will create a new tab with transformed xml output. that's it :) 

Payment Detail Formula in Payments (IBY)

A payment detail formula is a custom PL/SQL expression that drives the informational text that is sent to the bank or payment system for each payment.

An example of a payment detail formula is concatenate Invoice number,Invoice date and Amount.

CALLING_APP_DOC_REF_NUMBER||

',' ||DOCUMENT_DATE|| ',' ||DOCUMENT_AMOUNT|| ';'

Result : A1,23-JUL-13,100;

 

Navigation : Payables / Payment Administrator Click on Payment Process Profiles
  

Select the Payment Process Profile and click on update.

Then there is text box for Payment Detail Formula.

Enter text with what columns you need and enter 240 in the maximum payment length and save.

Below is example :

CALLING_APP_DOC_REF_NUMBER|| ',' ||DOCUMENT_DATE|| ',' ||DOCUMENT_AMOUNT|| ',' ||PAYMENT_AMOUNT|| ';'

Mapping Table : 

IBY_DOCS_PAYABLE_ALL

After adding these details then make a payment and check the Format Payment Instructions with Text Output log file.In the standard XML extract you will see the concatenated string values in 'PaymentDetails' Tag