Thursday, 13 July 2017

Query for finding MTD/YTD/ITD Cost/Revenue and Fee Details for a Project in PA Module



WITH PARAMETER AS (SELECT :PERIOD_NAME AS PERIOD_NAME FROM DUAL)
SELECT distinct prj.project_id,prj.segment1,
  (SELECT ROUND(SUM(tot_burdened_cost),2)
  FROM apps.pa_txn_accum
  WHERE project_id=prj.project_id
  AND gl_period   =PARAMETER.PERIOD_NAME
    -- and organization_id=TXN.organization_id
  ) "MTD Cost",
  (SELECT ROUND(SUM(unbilled_receivable_dr),2)--round(SUM(tot_revenue),2)
  FROM apps.pa_draft_revenues_all
  WHERE project_id              =prj.project_id
  AND TO_CHAR(gl_date,'MON-YY') =PARAMETER.PERIOD_NAME
  ) "MTD Revenue",
  (SELECT ROUND(SUM(tot_burdened_cost),2)
  FROM apps.pa_txn_accum
  WHERE project_id=prj.project_id
  AND gl_period  IN
    (SELECT PERIOD_NAME
    FROM gl.GL_PERIODS
    WHERE PERIOD_YEAR          = TO_CHAR(SYSDATE, 'RRRR')
    AND UPPER(PERIOD_TYPE)    <> 'WEEK'
    AND ADJUSTMENT_PERIOD_FLAG = 'N'
    AND TO_DATE('01-'
      ||PERIOD_NAME, 'DD-MON-RR') <= TO_DATE('01-'
      ||PARAMETER.PERIOD_NAME , 'DD-MON-RR')
    AND TO_CHAR(TO_DATE('01-'
      ||PERIOD_NAME, 'DD-MON-RR') , 'RR') = TO_CHAR(TO_DATE('01-'
      ||PARAMETER.PERIOD_NAME , 'DD-MON-RR') , 'RR')
    )
    -- and organization_id=TXN.organization_id
  ) "YTD Cost",
  (SELECT ROUND(SUM(tot_revenue),2)
  FROM apps.pa_txn_accum
  WHERE project_id=prj.project_id
  AND gl_period  IN
    (SELECT PERIOD_NAME
    FROM gl.GL_PERIODS
    WHERE PERIOD_YEAR          = TO_CHAR(SYSDATE, 'RRRR')
    AND UPPER(PERIOD_TYPE)    <> 'WEEK'
    AND ADJUSTMENT_PERIOD_FLAG = 'N'
    AND TO_DATE('01-'
      ||PERIOD_NAME, 'DD-MON-RR') <= TO_DATE('01-'
      ||PARAMETER.PERIOD_NAME , 'DD-MON-RR')
    AND TO_CHAR(TO_DATE('01-'
      ||PERIOD_NAME, 'DD-MON-RR') , 'RR') = TO_CHAR(TO_DATE('01-'
      ||PARAMETER.PERIOD_NAME , 'DD-MON-RR') , 'RR')
    )
     ) "YTD Revenue",
  (SELECT ROUND(SUM(tot_burdened_cost),2)
  FROM pa.pa_txn_accum
  WHERE project_id=prj.project_id
  ) "ITD Cost",
  (SELECT ROUND(SUM(tot_revenue),2)
  FROM pa.pa_txn_accum
  WHERE project_id=prj.project_id
  )"ITD Revenue",
  (SELECT ROUND(SUM(bill_amount),2)
  FROM pa.pa_events
  WHERE project_id                   =prj.project_id
  AND TO_CHAR(creation_date,'MON-YY')=PARAMETER.PERIOD_NAME
  )"MTD Fee",
  (SELECT ROUND(SUM(bill_amount),2)
  FROM pa.pa_events
  WHERE project_id                     =prj.project_id
  AND TO_CHAR(creation_date,'MON-YY') IN
    (SELECT PERIOD_NAME
    FROM gl.GL_PERIODS
    WHERE PERIOD_YEAR          = TO_CHAR(SYSDATE, 'RRRR')
    AND UPPER(PERIOD_TYPE)    <> 'WEEK'
    AND ADJUSTMENT_PERIOD_FLAG = 'N'
    AND TO_DATE('01-'
      ||PERIOD_NAME, 'DD-MON-RR') <= TO_DATE('01-'
      ||PARAMETER.PERIOD_NAME , 'DD-MON-RR')
    AND TO_CHAR(TO_DATE('01-'
      ||PERIOD_NAME, 'DD-MON-RR') , 'RR') = TO_CHAR(TO_DATE('01-'
      ||PARAMETER.PERIOD_NAME , 'DD-MON-RR') , 'RR')
    )
  )"YTD Fee",
  (SELECT ROUND(SUM(bill_amount),2)
  FROM pa.pa_events
  WHERE project_id=prj.project_id
  )"ITD Fee",
  (SELECT SUM(allocated_amount)
  FROM pa_project_fundings
  WHERE project_id=prj.project_id
  )"Funded Revenue",
  (SELECT SUM(a.amount) "Revenue To Date"
  FROM pa.PA_DRAFT_REVENUE_ITEMS# a,
    pa.PA_DRAFT_REVENUES_ALL# b,
    pa.pa_projects_all ppa
  WHERE a.project_id      = ppa.project_id
  AND a.DRAFT_REVENUE_NUM = b.DRAFT_REVENUE_NUM
  AND a.project_id        = b.project_id
  AND b.GL_DATE          <=
    (SELECT END_DATE
    FROM gl.gl_period_statuses
    WHERE end_date =
      (SELECT MAX(end_date)
      FROM gl.gl_period_statuses
      WHERE application_id       = 101
      AND set_of_books_id        = 2022
      AND closing_status         = 'C'
      AND adjustment_period_flag = 'N'
      )
    AND application_id         = 101
    AND set_of_books_id        = 2022
    AND adjustment_period_flag = 'N'
    )
  AND ppa.project_id=prj.project_id
  )"GL Close Revenue"
FROM
    PA.PA_PROJECTS_ALL# PRJ,
  PA.PA_PROJECT_TYPES_ALL# PRJT,
  PA.PA_TXN_ACCUM# TXN,PARAMETER
WHERE prj.project_id                              = txn.project_id
AND prj.project_status_code                     ='APPROVED'
AND prj.PROJECT_TYPE                            = PRJT.PROJECT_TYPE(+)
AND prjt.project_type_class_code                ='CONTRACT'
AND TO_CHAR(week_ending_date , 'MON-RR') = PARAMETER.PERIOD_NAME

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,
                apps.imie_remove_special_chars.remove_special_chars
                   (TO_CHAR (CASE
                                WHEN xle.event_type_code =
                                                        'PAYMENT CANCELLATION'
                                   THEN (aca.amount * -1)
                                ELSE aca.amount
                             END
                            )
                   ) 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,
                          ael.accounted_dr
                         )
                - DECODE (ael.accounted_cr, NULL, 0, ael.accounted_cr)
                                                                     dist_amt
           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,
       CASE
          WHEN aia.SOURCE = 'RECURRING INVOICE'
             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  *
                decode(ipa.payment_currency_code,
                     asp.base_currency_code,
                     1,
                     decode(aivsc.exchange_rate_type,
                            'User', --l_exchange_rate_type
                            ap_pmt_callout_pkg.get_user_rate(asp.base_currency_code,
                                                             ipa.payment_currency_code,
                                                             ifdpv.calling_app_doc_unique_ref1),
                            ap_utilities_pkg.get_exchange_rate(ipa.payment_currency_code,
                                                               asp.base_currency_code,
                                                               aivsc.exchange_rate_type,
                                                               aivsc.check_date,
                                                               '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.