Monday, 19 June 2017

GL Transactions for Payments

SELECT DISTINCT aca.payment_method_lookup_code trans_type,
                TO_CHAR (aca.check_number) invoice_num,
                TO_CHAR (aca.check_date) invoice_date,
                xle.event_type_code ponum_inv_linenum,
                ael.currency_code line_desc,
                   (TO_CHAR (CASE
                                WHEN xle.event_type_code =
                                                        'PAYMENT CANCELLATION'
                                   THEN (aca.amount * -1)
                                ELSE aca.amount
                   ) requestor_qty_inv,
                NULL appr_date_unitprice, NULL doc_seq_revamt,
                NULL acct_class, NULL PERCENT,
                  DECODE (ael.entered_dr, NULL, 0, ael.entered_dr)
                - DECODE (ael.entered_cr, NULL, 0, ael.entered_cr) amount,
                  DECODE (ael.accounted_dr,
                          NULL, 0,
                - DECODE (ael.accounted_cr, NULL, 0, ael.accounted_cr)
           FROM xla_ae_headers aeh,
                xla_ae_lines ael,
                xla_events xle,
                xla.xla_transaction_entities ent,              
                xla_distribution_links xdl,
                ap_checks_all aca,
                ap_payment_hist_dists aphd,
                ap_payment_history_all aph
          WHERE 1 = 1
            AND ael.application_id = aeh.application_id
            AND ael.ae_header_id = aeh.ae_header_id
            AND xle.application_id = aeh.application_id
            AND xle.event_id = aeh.event_id          
            AND ent.application_id = xle.application_id
            AND ent.entity_id = xle.entity_id
            AND xdl.ae_header_id = aeh.ae_header_id
            AND xdl.ae_line_num = ael.ae_line_num
            AND aphd.payment_hist_dist_id = xdl.source_distribution_id_num_1
            AND xdl.source_distribution_type = 'AP_PMT_DIST'
            AND ent.transaction_number = aca.check_number
            AND xdl.application_id = 200
            AND aphd.payment_history_id = aph.payment_history_id
            AND aph.check_id = aca.check_id
            AND ael.ae_header_id = p_hdrid
            AND ael.ae_line_num = p_linenum;

GL Transactions for AR

SELECT rctt.description trans_type, TO_CHAR (rct.trx_number) invoice_num,
       TO_CHAR (rct.trx_date) invoice_date,
       TO_CHAR (rcl.line_number) ponum_inv_linenum, rcl.description line_desc,
       TO_CHAR (rcl.quantity_invoiced) requestor_qty_inv,
       TO_CHAR (rcl.unit_selling_price) appr_date_unitprice,
       TO_CHAR (rcl.revenue_amount) doc_seq_revamt,
       rctg.account_class acct_class, TO_CHAR (rctg.PERCENT) PERCENT,
       (rctg.amount * -1) amount, (rctg.acctd_amount * -1) dist_amt
  FROM xla_ae_headers aeh,
       xla_ae_lines ael,
       xla_events xle,
       xla.xla_transaction_entities ent,
       xla_distribution_links xdl,
       ra_customer_trx_all rct,
       ra_customer_trx_lines_all rcl,
       ra_cust_trx_types_all rctt,
       ra_cust_trx_line_gl_dist_all rctg
 WHERE 1 = 1
   AND ael.application_id = aeh.application_id
   AND ael.ae_header_id = aeh.ae_header_id
   AND xle.application_id = aeh.application_id
   AND xle.event_id = aeh.event_id
   AND ent.application_id = xle.application_id
   AND ent.entity_id = xle.entity_id
   AND xdl.ae_header_id = aeh.ae_header_id
   AND xdl.ae_line_num = ael.ae_line_num
   AND rcl.customer_trx_line_id = rctg.customer_trx_line_id
   AND rct.customer_trx_id = rcl.customer_trx_id
   AND rctt.cust_trx_type_id = rct.cust_trx_type_id
   AND rct.trx_number = ent.transaction_number
   AND xdl.source_distribution_id_num_1 = rctg.cust_trx_line_gl_dist_id
   AND xdl.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
   AND ael.ae_header_id = p_hdrid
   AND ael.ae_line_num = p_linenum;

GL Transactions for AR Adjustments

SELECT NULL trans_type, ent.transaction_number invoice_num,
       (SELECT TO_CHAR (trx_date)
          FROM ra_customer_trx_all
         WHERE customer_trx_id = aaa.customer_trx_id) invoice_date,
       NULL ponum_inv_linenum, art.description line_desc,
       NULL requestor_qty_inv, NULL appr_date_unitprice, NULL doc_seq_revamt,
       NULL acct_class, NULL PERCENT,
       (  DECODE (ada.amount_dr, NULL, 0, ada.amount_dr)
        - DECODE (ada.amount_cr, NULL, 0, ada.amount_cr)
       ) amount,
       (  DECODE (ada.acctd_amount_dr, NULL, 0, ada.acctd_amount_dr)
        - DECODE (ada.acctd_amount_cr, NULL, 0, ada.acctd_amount_cr)
       ) dist_amt
  FROM xla_ae_headers aeh,
       xla_ae_lines ael,
       xla_events xle,
       xla.xla_transaction_entities ent,
       xla_distribution_links xdl,
       ar_distributions_all ada,
       ar_payment_schedules_all aps,
       ar_adjustments_all aaa,
       ar_receivables_trx_all art
 WHERE 1 = 1
   AND ael.application_id = aeh.application_id
   AND ael.ae_header_id = aeh.ae_header_id
   AND xle.application_id = aeh.application_id
   AND xle.event_id = aeh.event_id
   AND ent.application_id = xle.application_id
   AND ent.entity_id = xle.entity_id
   AND xdl.ae_header_id = aeh.ae_header_id
   AND xdl.ae_line_num = ael.ae_line_num
   AND aaa.payment_schedule_id = aps.payment_schedule_id
   AND aaa.receivables_trx_id = art.receivables_trx_id
   AND xdl.source_distribution_id_num_1 = ada.line_id
   AND ent.source_id_int_1 = aaa.adjustment_id
   AND ael.ae_header_id = p_hdrid
   AND ael.ae_line_num = p_linenum;

GL Transactions for AP

SELECT /*+ leading (ael aeh) */
       aia.invoice_type_lookup_code trans_type,
       ent.transaction_number invoice_num,
       TO_CHAR (aia.invoice_date) invoice_date,
       TO_CHAR (pod.po_number) ponum_inv_linenum,
             THEN aia.description
          ELSE aid.description
       END line_desc,
       TO_CHAR (pod.requested_by) requestor_qty_inv,
       TO_CHAR (pod.approved_date) appr_date_unitprice,
       TO_CHAR (aia.doc_sequence_value) doc_seq_revamt, NULL acct_class,
       NULL PERCENT, NULL amount,
       DECODE (aid.base_amount, NULL, aid.amount, aid.base_amount) dist_amt
  FROM xla_ae_headers aeh,
       xla_ae_lines ael,
       xla_events xle,
       xla.xla_transaction_entities ent,
       xla_distribution_links xdl,
       apps.ap_invoices_all aia,
       apps.ap_invoice_distributions_all aid,
       (SELECT poh.segment1 po_number, poh.approved_date approved_date,
               pod.po_distribution_id po_dist_id, pod.code_combination_id,
               pap.full_name requested_by
          FROM apps.po_headers_all poh,
               apps.po_distributions_all pod,
               (SELECT pap1.person_id, pap2.full_name
                  FROM (SELECT   MAX (effective_end_date) end_date,
                                 person_id person_id
                            FROM apps.per_all_people_f pap
                        GROUP BY person_id) pap1,
                       (SELECT full_name, person_id,
                               effective_end_date end_date
                          FROM apps.per_all_people_f) pap2
                 WHERE pap1.person_id = pap2.person_id
                   AND pap1.end_date = pap2.end_date) pap
         WHERE poh.po_header_id = pod.po_header_id
           AND pod.deliver_to_person_id = pap.person_id) pod
 WHERE 1 = 1
   AND ael.application_id = aeh.application_id
   AND ael.ae_header_id = aeh.ae_header_id
   AND xle.application_id = aeh.application_id
   AND xle.event_id = aeh.event_id
   AND ent.application_id = xle.application_id
   AND ent.entity_id = xle.entity_id
   AND xdl.ae_header_id = aeh.ae_header_id
   AND xdl.ae_line_num = ael.ae_line_num
   AND ent.transaction_number = aia.invoice_num
   AND xdl.source_distribution_id_num_1 = aid.invoice_distribution_id
   AND xdl.source_distribution_type = 'AP_INV_DIST'
   AND aid.invoice_id = aia.invoice_id
   AND ent.entity_code = 'AP_INVOICES'
   AND NVL (aid.amount, 0) <> 0
   AND aid.po_distribution_id = pod.po_dist_id(+)
   AND ael.ae_header_id = p_hdrid
   AND ael.ae_line_num = p_linenum;

Thursday, 30 March 2017

Extract for Payments - Check and EFT

 SELECT     papf.employee_number  employee_number
          , aps.vendor_name        payee_name
          , ieba.bank_account_num  bank_account_number
          , hp_bank.party_name     bank_name
          , hp_bank.organization_name_phonetic     bank_number
          , hps_branch.party_name  branch_name        
          , ipa.org_id             org_id
          , ipa.payment_date       payment_date
          , ieba.bank_account_type bank_account_type
          , ieba.bank_id
          , cba.bank_account_type  int_bank_account_type
          , cba.bank_account_num   int_bank_account_num
          , round(sum(ifdpv.payment_amount  *
                            'User', --l_exchange_rate_type
                                                               'CONFIRM')))),0) payment_amount
          , MIN(ifdpv.calling_app_doc_ref_number)  reference
          , MIN(ifdpv.document_description)        document_description
       FROM iby_pay_instructions_all      ipia
          , iby_payments_all              ipa
          , iby_external_payees_all       iepa
          , ap_supplier_sites_all         assa
          , ap_suppliers                  aps
          , iby_ext_bank_accounts         ieba
          , hz_parties                    hp_bank
          , hz_parties                    hps_branch
          , iby_fd_docs_payable_v         ifdpv
          , ap_inv_selection_criteria_all aivsc
          , ap_system_parameters_all      asp
          , ce_bank_accounts              cba
 , per_all_people_f              papf
      WHERE ipia.payment_instruction_id       = p_payment_instruction_id
        AND ipia.payment_instruction_id       = ipa.payment_instruction_id
        AND ipa.ext_payee_id                  = iepa.ext_payee_id
        AND iepa.supplier_site_id             = assa.vendor_site_id
        AND assa.vendor_id                    = aps.vendor_id
        AND ipa.external_bank_account_id      = ieba.ext_bank_account_id (+)
        AND ieba.bank_id                      = hp_bank.party_id (+)
        AND ieba.branch_id                    = hps_branch.party_id (+)
        AND ipa.payment_id                    = ifdpv.payment_id
        AND ifdpv.calling_app_doc_unique_ref1 = aivsc.checkrun_id
        AND ipa.org_id                        = asp.org_id
        AND ipa.internal_bank_account_id      = cba.bank_account_id
AND aps.employee_id                   = papf.person_id (+)
      GROUP BY papf.employee_number
          , aps.vendor_name
          , ieba.bank_account_num
          , hp_bank.party_name
          , hp_bank.organization_name_phonetic
          , hps_branch.party_name        
          , ipa.org_id
          , ipa.payment_date
          , ieba.bank_account_type
          , ieba.bank_id
          , cba.bank_account_type
          , cba.bank_account_num;

Monday, 20 March 2017

Steps to Create Excel for Apps Report

  • GL Wand needs no desktop installation and it is very user friendly for creating many financial reports. It is a very good reporting tool for Oracle E-Business suite.

Pre-Requisites : Users should have Excel4apps Wands responsibility to open the template and run the report.. Once the responsibility is assigned, on clicking the Excel4apps Wands responsibility, new add-ins will be added in Excel named “Reports Wand”

Step 1:

  •  After framing sql query, we need to go to Excel4apps responsibility and create Report Definition.
  •  Click Create as highlighted in the above screenshot for Creating a new Report definition and Edit for  updating the Report definition. While updating the Report definition, click on Import Icon next to Edit button(Refer screen above) and provide the short name of the Program. Modify the SQL Section and              follow the steps as below.

  • On clicking create, below screen will appear

  • In Program Information, Provide the short name, Report name, Description. Category will be the application to which the report has to be attached

  • Clicking on Next will take you to SQL Window where we need to paste the sql query. If the Report has any bind variables, please attach in the Bind Variable section

  • Attach the report to the request group then, and complete the Report definition Template.

  • Once we click on ‘Finish’ button, Excel will be opened as below

  • In the Action column, double click on the LOV (as in the figure above). When we are creating the Report definition, Action Column should be Create. While updating it should be ‘Update’ and while deleting, it should be delete.

  • After selecting ‘Create’ from LOV in the ‘Action column, click on Process Icon next to Import button.

  • It will have two drill downs ‘Process All’ and ‘Process Single’. Select Process All and the Report definition will be created successfully and displayed in Error Column. If any Errors, it will be displayed as ‘Error’

  • On clicking Proceed, will lead updating the Error Column in the Report definition as shown below

Steps on creating/Running the Template 

  • Click on Create button as shown below. Before clicking the ‘Create’ button, we should make sure we are in the correct Responsibility as shown in the figure below. We can even change the Responsibility from here (E4A Tool) as in the figure below 

  • Click on ‘Create button’ and select the Report definition’s short name as in the figure below and click Select

  • Clicking on ‘Select’ will take us to the below screen where we can add drop columns from the Query. Clicking on ‘Cross’ button will remove the columns from the template.

  • Now click ‘Create’ as in the above figure. 
  • Now the Template is created. Click on ‘Execute’ button to run the template.
  •  Click on ‘Execute’ Report and the report will run successfully and the output will be generated.
  • The template to be saved and shared to ‘Users’ and check in different Instances.

Thursday, 16 March 2017

ERROR ORA -00980 Synonym Translation no longer valid

Error Message :  ORA -00980 Synonym Translation no longer Valid.

Error Area : it is related Database (Table/view)  not related to forms

When it happens :  Synonym is in inconsistent state, in my case  i have modified the view on which synonym was created (dependent) .

How to confirm the issue : when query the data using the synonym  in TOAD/sqldeveloper .....  it shows error message  ORA -00980 Synonym Translation no longer valid.

Action Plan/solution:  drop the synonym and recreate  synonym(after the  base view/table modified)

verify:  Query the data by using the synonym  then it should fetch the data

Venkata GANNE

Friday, 3 March 2017

OATS Installation issue - Struck/Hang for Long Time

Installation Issue :  ' Spawning C:\OracleATS\bin\deploy.bat C:\OracleATS\install\oats.ear'   and never completes for Long Time ( even for Days)

When it Happen : While Installing OATS Software

Action Needs to Taken :  STOP/Disable   Anti Virus Software(s)  and/  Disable Firewall.

Venkata GANNE.

How to Create a User in Oracle Load Testing (OLT)

How to Create a User for ORACLE Load Testing LogIn

Clink on Start Button Oracle Application Testing Suite Administrator

Login with Administrator User as shown below
Note: Ensure DataBase should be OLT Database while login

After logged in appears as below

Click on Add icon
Enter User Details

Select Access type

If you want provide administrator access to the user then check the check box

Click ok


Now Open Oracle Load Testing in Browser  as shown below

Or you can login with url  like   http:localhost:8088/

Login with recently Created User 

Venkata GANNE

please feel free to ask any query on this.

Friday, 3 February 2017

XML Publisher Report in Oracle Transportation Management (OTM)

Please follow the below steps to create XML Publisher Report in Oracle Transportation Management:

Query Template

Format Template

Adding to SHIPMENT_MANAGER Report Group

Now report appears under Shipment Management

Report Output in PDF

Report Output in Email

Monday, 16 January 2017


The tool-tip displaying report column while hovering on the another report column.

l   ENAME, DEPARTMENT and MANAGER  is the report Column.
l   Make the ENAME column’s display type as “Display As Text”. DEPARTMENT and MANAGER column’s display type as hidden.
l   In Column Formatting, apply this Html code in the HTML Expression
        <span data-tooltip="Department : #DEPT# &#xa;Manager : #MGR#">#ENAME#</span>

l   “Department : #DEPT# &#xa;Manager : #MGR#” this will be displayed while hovering on to the employee name and “&#xa” this is used to display text in the new line.

l    Click on the edit page icon
l    Go to css
l    Apply this code in the inline Region

 /* Add this attribute to the element that needs a tooltip */
[data-tooltip] {
  position: relative;
  display: inline-block;

/* Hide the tooltip content by default */
[data-tooltip]:after {
  visibility: hidden;
  opacity: 0;
  pointer-events: none;

/* Position tooltip above the element */
[data-tooltip]:before {
  position: absolute;
  padding: 10px;
  width: 160px;
  border-radius: 10px;
  background-color: #000;
  color: #fff;
  content: attr(data-tooltip);

/* Show tooltip content on hover */
[data-tooltip]:hover:after {
  visibility: visible;
  z-index: 999;
  opacity: 1;

.t-Body-nav {
      overflow: hidden;

Angel M

Monday, 9 January 2017

Performance Using hint

Performance issue.

 The issue was due to joins across remote sites where oracle optimizer might 
 Wrongly choose the driving site. 
 This can be resolved by using driving site hint. 
 It is always good to do all manipulations in one site and fetch the final
 Results to the target site.



Select t1.column1, t2.column2, t2.column3
  From table1 t1, xxsc.table2@db1todb2 t2
 Where t1.column1 = t2.column1

Query which was taking around 47 msecs for execution.

Select /*+driving_site(t2)*/  t1.column1, t2.column2, t2.column3
  From table1 t1, xxsc.table2@db1todb2 t2
 Where t1.column1 = t2.column1
Query which was taking around 15 msecs for execution.

Query to find open invoices with the Supplier and PO details

SELECT   i.invoice_num "Invoice Number",
         (SELECT MAX (pha.segment1) po_number
            FROM apps.ap_invoices_all aia,
                 apps.ap_invoice_lines_all aila,
                 apps.ap_invoice_distributions_all aida,
                 apps.po_headers_all pha,
                 apps.po_lines_all pla,
                 apps.po_distributions_all pda
           WHERE pha.po_header_id = pla.po_header_id
             AND pla.po_line_id = pda.po_line_id
             AND aida.po_distribution_id = pda.po_distribution_id
             AND aida.invoice_id = aia.invoice_id
             AND aia.invoice_id = aila.invoice_id
             AND aia.invoice_id = i.invoice_id
             AND aila.line_number = ail.line_number
             AND aia.vendor_id = i.vendor_id) "PO Number",
         v.segment1 "Supplier Number", v.vendor_name "Supplier Name",
         vs.vendor_site_code "Supplier Site", i.invoice_date "Invoice Date",
         i.description "Invoice Description",
         ail.description "Invoice Line Description", SUM (ail.amount)
         DECODE (i.cancelled_date, NULL, 'NO', 'YES') "Cancel Status"
    FROM po_vendors v,
         po_vendor_sites_all vs,
         ap_invoices_all i,
         apps.ap_invoice_lines_all ail
   WHERE v.vendor_id = vs.vendor_id
     AND i.invoice_id = ail.invoice_id
     AND i.vendor_id = v.vendor_id
     AND i.vendor_site_id = vs.vendor_site_id
--and     i.invoice_num = '10190183'
     AND i.invoice_date BETWEEN '01-JAN-2015' AND '31-DEC-2015'
               SELECT 1
                 FROM apps.ap_invoice_distributions_all d
                WHERE d.invoice_id = i.invoice_id
                      AND d.match_status_flag = 'A')
GROUP BY v.vendor_name,

ORDER BY v.vendor_name, i.invoice_num;
Sivachandaran S