Query to Display Module Wise Reports in oracle apps

/* Formatted on 11/14/2016 3:47:18 PM (QP5 v5.114.809.3010) */
  SELECT   fa.application_short_name,
           fcpv.user_concurrent_program_name,
           description,
           DECODE (fcpv.execution_method_code,
                   'B', 'Request Set Stage Function',
                   'Q', 'SQL*Plus',
                   'H', 'Host',
                   'L', 'SQL*Loader',
                   'A', 'Spawned',
                   'I', 'PL/SQL Stored Procedure',
                   'P', 'Oracle Reports',
                   'S', 'Immediate',
                   fcpv.execution_method_code)
              exe_method,
           output_file_type,
           program_type,
           printer_name,
           minimum_width,
           minimum_length,
           concurrent_program_name,
           concurrent_program_id
    FROM   fnd_concurrent_programs_vl fcpv, fnd_application fa
   WHERE   fcpv.application_id = fa.application_id
ORDER BY   1


      ********************************************************************
 Module Wise Count:-

/* Formatted on 11/14/2016 3:49:03 PM (QP5 v5.114.809.3010) */
  SELECT   fa.application_short_name,
           DECODE (fcpv.execution_method_code,
                   'B', 'Request Set Stage Function',
                   'Q', 'SQL*Plus',
                   'H', 'Host',
                   'L', 'SQL*Loader',
                   'A', 'Spawned',
                   'I', 'PL/SQL Stored Procedure',
                   'P', 'Oracle Reports',
                   'S', 'Immediate',
                   fcpv.execution_method_code)
              exe_method,
           COUNT (concurrent_program_id) COUNT
    FROM   fnd_concurrent_programs_vl fcpv, fnd_application fa
   WHERE   fcpv.application_id = fa.application_id
GROUP BY   fa.application_short_name, fcpv.execution_method_code
ORDER BY   1

Supplier Prepayment Balance Report in Oracle apps

SELECT AIA.INVOICE_ID                                          ,
       aia.description,
       aia.org_id                                              ,
       aia.INVOICE_NUM                                         ,
       aia.INVOICE_DATE                                        ,
       aia.INVOICE_AMOUNT                                      ,
       aia.EXCHANGE_RATE                                       ,
       aia.INVOICE_AMOUNT*NVL(aia.EXCHANGE_RATE,1) Invoice_Inr ,
aia.attribute10 requested_by,
       aps.VENDOR_NAME                                         ,
 apC.CHECK_id,    
 apC.CHECK_NUMBER                                         ,
       apc.CHECK_DATE                      payment_date                             ,
       (apc.amount )                         paid_amount                              ,
       --apa.amount*NVL(apa.EXCHANGE_RATE,1) Paid_Inr                                 ,
       avp.invoice_num                     app_std_inv_num                          ,
       aia.invoice_amount                                                           ,
       avp.PREPAY_AMOUNT_APPLIED                                                    ,
       avp.INVOICE_ID APP_INVOICE_ID                                                ,
       avp.ACCOUNTING_DATE                                                          ,
       aia.invoice_currency_code                                                    ,
       hou.name                                                                     ,
       apss.VENDOR_SITE_CODE                                                        ,
       DECODE(EARLIEST_SETTLEMENT_DATE,'','PERMENENT','TEMPORARY') STATUS
FROM   AP_INVOICES_ALL aia            ,
       AP_INVOICE_PAYMENTS_ALL APA    ,
       AP_SUPPLIERS aps               ,
       ap_checks_all apc              ,
       AP_VIEW_PREPAYS_FR_PREPAY_V avp,
       hr_operating_units hou         ,
       ap_supplier_sites_All apss
WHERE  INVOICE_TYPE_LOOKUP_CODE='PREPAYMENT'
AND    aps.vendor_id           =aia.vendor_id
AND    AIA.INVOICE_ID          =APA.INVOICE_ID
AND    apa.check_id            =apc.check_id
AND    aia.invoice_id          =avp.prepay_invoice_id
AND    APA.INVOICE_ID          =avp.prepay_invoice_id
AND    nvl(apa.reversal_flag,'a')      !='Y'
AND    aia.org_id              =hou.organization_id
AND    aia.vendor_id           =apss.vendor_id
AND    aia.vendor_site_id      =apss.vendor_site_id
AND    aps.VENDOR_id BETWEEN NVL(:P_VENDOR_NAME_FROM,aps.VENDOR_id) AND    NVL(:P_VENDOR_NAME_TO,aps.VENDOR_id)
AND    aps.VENDOR_id BETWEEN NVL(:P_VENDOR_NUM_FROM,aps.VENDOR_id) AND    NVL(:P_VENDOR_NUM_TO,aps.VENDOR_id)
AND    HOU.NAME=NVL(:P_OPERATING_UNIT,HOU.NAME)
AND    APA.ACCOUNTING_DATE<=nvl(:P_AS_ON_DATE,sysdate)
AND    AVP.ACCOUNTING_DATE<=nvl(:P_AS_ON_DATE,sysdate)


UNION

SELECT AIA.INVOICE_ID                                          ,
       aia.description,
       aia.org_id                                              ,
       aia.INVOICE_NUM                                         ,
       aia.INVOICE_DATE                                        ,
       aia.INVOICE_AMOUNT                                      ,
       aia.EXCHANGE_RATE                                       ,
       aia.INVOICE_AMOUNT*NVL(aia.EXCHANGE_RATE,1) Invoice_Inr ,
aia.attribute10 requested_by,
       aps.VENDOR_NAME                                         ,
 apC.CHECK_id,         
   apC.CHECK_NUMBER                                         ,
       apc.CHECK_DATE                      payment_date                             ,
      (apc.amount)                          paid_amount                              ,
     --  apa.amount*NVL(apa.EXCHANGE_RATE,1) Paid_Inr                                 ,
       NULL                                                                         ,
       aia.invoice_amount                                                           ,
       NULL                                                                         ,
       NULL                                                                         ,
       NULL                                                                         ,
       aia.invoice_currency_code                                                    ,
       hou.name                                                                     ,
       apss.VENDOR_SITE_CODE                                                        ,
       DECODE(EARLIEST_SETTLEMENT_DATE,'','PERMENENT','TEMPORARY') STATUS
FROM   AP_INVOICES_ALL aia         ,
       AP_INVOICE_PAYMENTS_ALL APA ,
       AP_SUPPLIERS aps            ,
       ap_checks_all apc           ,
       hr_operating_units hou      ,
       ap_supplier_sites_All apss
WHERE  INVOICE_TYPE_LOOKUP_CODE='PREPAYMENT'
AND    aps.vendor_id           =aia.vendor_id
AND    AIA.INVOICE_ID          =APA.INVOICE_ID
AND    apa.check_id            =apc.check_id
AND    nvl(apa.reversal_flag,'a')      !='Y'
AND    aia.org_id              =hou.organization_id
AND    aia.vendor_id           =apss.vendor_id
AND    aia.vendor_site_id      =apss.vendor_site_id
AND    aps.VENDOR_id BETWEEN NVL(:P_VENDOR_NAME_FROM,aps.VENDOR_id) AND    NVL(:P_VENDOR_NAME_TO,aps.VENDOR_id)
AND    aps.VENDOR_id BETWEEN NVL(:P_VENDOR_NUM_FROM,aps.VENDOR_id) AND    NVL(:P_VENDOR_NUM_TO,aps.VENDOR_id)
AND    HOU.NAME=NVL(:P_OPERATING_UNIT,HOU.NAME)
AND    APA.ACCOUNTING_DATE<=nvl(:P_AS_ON_DATE,sysdate)

Get Report List With Parameters

/* Formatted on 7/18/2014 9:54:43 AM (QP5 v5.115.810.9015) */
SELECT a.concurrent_program_name AS concurrent_program_name,
       a.user_concurrent_program_name AS user_concurrent_program_name,
       c.application_short_name AS application_short_name,
       b.column_seq_num AS column_seq_num,
       b.srw_param AS param_seq,
       b.form_left_prompt AS prompt,
       d.flex_value_set_name AS values_set_name
FROM fnd_concurrent_programs_vl a,
     fnd_descr_flex_col_usage_vl b,
     fnd_application c,
     fnd_flex_value_sets d
WHERE a.enabled_flag = 'Y'
      AND a.concurrent_program_name =
            SUBSTR (b.descriptive_flexfield_name, 7, 100)
      AND a.application_id = c.application_id
      AND b.enabled_flag = 'Y'
      AND b.flex_value_set_id = d.flex_value_set_id
ORDER BY a.concurrent_program_id, b.column_seq_num

Expense report Extract Queries

/* Formatted on 7/7/2014 4:05:31 PM (QP5 v5.115.810.9015) */
-- To extract Expense reports for Companies that are based on France and Italy Operating Units

SELECT hou.name organization_name,
       ven.vendor_name,
       ven.vendor_type_lookup_code vendor_type,
       inv.doc_sequence_value voucher_number,
       inv.gl_date gl_date,
       inv.invoice_num invoice_number,
       inv.invoice_currency_code currency,
       inv.invoice_amount,
       invd.amount distribution_amount,
       invd.description distribution_description,
       gcc.code_combination_id,
       gcc.segment1 company,
       gcc.segment2 department,
       gcc.segment3 account,
       gcc.segment5 product,
       gcc.segment4 region,
       gcc.segment6 future,
       DECODE (aip.invoice_payment_type,
          'PREPAY', inv2.invoice_num,
          ac.check_number)
          document_number,
       invd.period_name,
       jh.je_source,
       jh.name journal_entry,
       jl.description line_description,
       jl.accounted_dr,
       jl.accounted_cr,
       gps.period_year gl_date_year,
       gps.quarter_num gl_date_quarter,
       TO_CHAR (inv.gl_date, 'MON') gl_date_month,
       TO_CHAR (inv.gl_date, 'DD') gl_date_day
FROM ap_invoices_all inv,
     ap_invoice_distributions_all invd,
     hr_organization_units hou,
     po_vendors ven,
     gl_code_combinations gcc,
     ap_invoices_all inv2,
     ap_invoice_payments_all aip,
     ap_checks_all ac,
     ax_events ae,
     ax_sle_headers ash,
     ax_sle_lines asl,
     gl_je_lines jl,
     gl_je_headers jh,
     hr_operating_units ou,
     gl_period_statuses gps
WHERE     inv.invoice_id = invd.invoice_id
      AND hou.organization_id = invd.org_id
      AND ven.vendor_id = inv.vendor_id
      --AND inv.invoice_num IN ( 'TEST_197525' , '215245')
      AND inv.invoice_type_lookup_code = 'EXPENSE REPORT'
      AND gcc.code_combination_id = invd.dist_code_combination_id
      AND gcc.segment1 = '38'
      AND inv.gl_date BETWEEN '01-JAN-2009' AND '31-DEC-2009'
      AND inv.invoice_id = aip.invoice_id(+)
      AND aip.other_invoice_id = inv2.invoice_id(+)
      AND ac.check_id(+) = aip.check_id
      AND ae.event_id = invd.accounting_event_id
      AND asl.source_id = invd.invoice_distribution_id
      AND ash.event_id = ae.event_id
      AND asl.sle_header_id = ash.sle_header_id
      AND jl.reference_10 = asl.reference_10
      AND jl.reference_9 = asl.reference_9
      AND jl.subledger_doc_sequence_value = asl.sle_header_id
      AND jl.set_of_books_id = asl.set_of_books_id
      AND jh.je_header_id = jl.je_header_id
      AND ou.organization_id = hou.organization_id
      AND (inv.gl_date BETWEEN gps.start_date AND gps.end_date)
      AND gps.set_of_books_id = ou.set_of_books_id
      AND gps.application_id = 200
UNION ALL
SELECT hou.name organization_name,
       ven.vendor_name,
       ven.vendor_type_lookup_code vendor_type,
       inv.doc_sequence_value voucher_number,
       inv.gl_date gl_date,
       inv.invoice_num invoice_number,
       inv.invoice_currency_code currency,
       inv.invoice_amount,
       NULL distribution_amount,
       NULL distribution_description,
       gcc.code_combination_id,
       gcc.segment1 company,
       gcc.segment2 department,
       gcc.segment3 account,
       gcc.segment5 product,
       gcc.segment4 region,
       gcc.segment6 future,
       DECODE (aip.invoice_payment_type,
          'PREPAY', inv2.invoice_num,
          ac.check_number)
          document_number,
       gps.period_name,
       jh.je_source,
       jh.name journal_entry,
       jl.description line_description,
       jl.accounted_dr,
       jl.accounted_cr,
       gps.period_year gl_date_year,
       gps.quarter_num gl_date_quarter,
       TO_CHAR (inv.gl_date, 'MON') gl_date_month,
       TO_CHAR (inv.gl_date, 'DD') gl_date_day
FROM ap_invoices_all inv,
     hr_organization_units hou,
     po_vendors ven,
     gl_code_combinations gcc,
     ap_invoices_all inv2,
     ap_invoice_payments_all aip,
     ap_checks_all ac,
     ax_sle_lines asl,
     gl_je_lines jl,
     gl_je_headers jh,
     hr_operating_units ou,
     gl_period_statuses gps
WHERE     1 = 1
      AND hou.organization_id = inv.org_id
      AND ven.vendor_id = inv.vendor_id
      AND inv.invoice_type_lookup_code = 'EXPENSE REPORT'
      AND gcc.code_combination_id = asl.code_combination_id
      AND gcc.segment1 = '38'
      AND inv.gl_date BETWEEN '01-JAN-2009' AND '31-DEC-2009'
      AND inv.invoice_id = aip.invoice_id(+)
      AND aip.other_invoice_id = inv2.invoice_id(+)
      AND ac.check_id(+) = aip.check_id
      AND asl.source_table = 'AP_INVOICES'
      AND asl.source_id = inv.invoice_id
      AND jl.reference_10 = asl.reference_10
      AND jl.reference_9 = asl.reference_9
      AND jl.subledger_doc_sequence_value = asl.sle_header_id
      AND jl.set_of_books_id = asl.set_of_books_id
      AND jh.je_header_id = jl.je_header_id
      AND ou.organization_id = hou.organization_id
      AND (inv.gl_date BETWEEN gps.start_date AND gps.end_date)
      AND gps.set_of_books_id = ou.set_of_books_id
      AND gps.application_id = 200
ORDER BY 5, 6, 9

=========================================================
-- To extract Expense reports for all Companies (Except France and Italy Operating Units) and Date Range
/* Formatted on 7/7/2014 4:05:15 PM (QP5 v5.115.810.9015) */
/* Formatted on 7/7/2014 4:05:43 PM (QP5 v5.115.810.9015) */
SELECT hou.name organization_name,
       ven.vendor_name,
       ven.vendor_type_lookup_code vendor_type,
       inv.doc_sequence_value voucher_number,
       inv.gl_date gl_date,
       inv.invoice_num invoice_number,
       inv.invoice_currency_code currency,
       inv.invoice_amount,
       invd.amount distribution_amount,
       invd.description distribution_description,
       gcc.code_combination_id,
       gcc.segment1 company,
       gcc.segment2 department,
       gcc.segment3 account,
       gcc.segment5 product,
       gcc.segment4 region,
       gcc.segment6 future,
       DECODE (aip.invoice_payment_type,
          'PREPAY', inv2.invoice_num,
          ac.check_number)
          document_number,
       invd.period_name,
       jh.je_source,
       jh.name journal_entry,
       jl.description line_description,
       jl.accounted_dr,
       jl.accounted_cr,
       gps.period_year gl_date_year,
       gps.quarter_num gl_date_quarter,
       TO_CHAR (inv.gl_date, 'MON') gl_date_month,
       TO_CHAR (inv.gl_date, 'DD') gl_date_day
FROM ap_invoices_all inv,
     ap_invoice_distributions_all invd,
     hr_organization_units hou,
     po_vendors ven,
     gl_code_combinations gcc,
     ap_invoices_all inv2,
     ap_invoice_payments_all aip,
     ap_checks_all ac,
     ap_accounting_events_all ae,
     ap_ae_headers_all aeh,
     ap_ae_lines_all ael,
     gl_je_lines jl,
     gl_je_headers jh,
     hr_operating_units ou,
     gl_period_statuses gps
WHERE     inv.invoice_id = invd.invoice_id
      AND hou.organization_id = invd.org_id
      AND ven.vendor_id = inv.vendor_id
      --AND inv.invoice_num in ( '226685','227482','227650')
      AND inv.invoice_type_lookup_code = 'EXPENSE REPORT'
      AND gcc.code_combination_id = invd.dist_code_combination_id
      AND gcc.segment1 = '37'
      AND inv.gl_date BETWEEN '01-APR-2009' AND '31-MAR-2010'
      AND inv.invoice_id = aip.invoice_id(+)
      AND aip.other_invoice_id = inv2.invoice_id(+)
      AND ac.check_id(+) = aip.check_id
      AND ae.accounting_event_id = invd.accounting_event_id
      AND aeh.accounting_event_id = ae.accounting_event_id
      AND ael.ae_header_id = aeh.ae_header_id
      AND ael.source_id = invd.invoice_distribution_id
      AND jl.gl_sl_link_id = ael.gl_sl_link_id
      AND jh.je_header_id = jl.je_header_id
      AND ou.organization_id = hou.organization_id
      AND (inv.gl_date BETWEEN gps.start_date AND gps.end_date)
      AND gps.set_of_books_id = ou.set_of_books_id
      AND gps.application_id = 200
UNION ALL
SELECT hou.name organization_name,
       ven.vendor_name,
       ven.vendor_type_lookup_code vendor_type,
       inv.doc_sequence_value voucher_number,
       inv.gl_date gl_date,
       inv.invoice_num invoice_number,
       inv.invoice_currency_code currency,
       inv.invoice_amount,
       NULL distribution_amount,
       NULL distribution_description,
       gcc.code_combination_id,
       gcc.segment1 company,
       gcc.segment2 department,
       gcc.segment3 account,
       gcc.segment5 product,
       gcc.segment4 region,
       gcc.segment6 future,
       DECODE (aip.invoice_payment_type,
          'PREPAY', inv2.invoice_num,
          ac.check_number)
          document_number,
       gps.period_name period_name,
       jh.je_source,
       jh.name journal_entry,
       jl.description line_description,
       jl.accounted_dr,
       jl.accounted_cr,
       gps.period_year gl_date_year,
       gps.quarter_num gl_date_quarter,
       TO_CHAR (inv.gl_date, 'MON') gl_date_month,
       TO_CHAR (inv.gl_date, 'DD') gl_date_day
FROM ap_invoices_all inv,
     hr_organization_units hou,
     po_vendors ven,
     gl_code_combinations gcc,
     ap_invoices_all inv2,
     ap_invoice_payments_all aip,
     ap_checks_all ac,
     ap_ae_lines_all ael,
     gl_je_lines jl,
     gl_je_headers jh,
     hr_operating_units ou,
     gl_period_statuses gps
WHERE     1 = 1
      AND hou.organization_id = inv.org_id
      AND ven.vendor_id = inv.vendor_id
      AND inv.invoice_type_lookup_code = 'EXPENSE REPORT'
      AND gcc.code_combination_id = ael.code_combination_id
      AND gcc.segment1 = '37'
      AND inv.gl_date BETWEEN '01-APR-2009' AND '31-MAR-2010'
      AND inv.invoice_id = aip.invoice_id(+)
      AND aip.other_invoice_id = inv2.invoice_id(+)
      AND ac.check_id(+) = aip.check_id
      AND ael.source_table = 'AP_INVOICES'
      AND ael.source_id = inv.invoice_id
      AND jl.gl_sl_link_id = ael.gl_sl_link_id
      AND jh.je_header_id = jl.je_header_id
      AND ou.organization_id = hou.organization_id
      AND (inv.gl_date BETWEEN gps.start_date AND gps.end_date)
      AND gps.set_of_books_id = ou.set_of_books_id
      AND gps.application_id = 200
ORDER BY 5, 6, 9

Creditor Combine Report

/* Formatted on 7/4/2014 3:23:49 PM (QP5 v5.115.810.9015) */
SELECT vendor_type_lookup_code,
       org_id,
       vendor_num,
       vendor_name,
       vendor_site_code,
       code_combination,
       NVL (SUM(DECODE (invoice_type_lookup_code,
                        'CREDIT',
                        invoice_amount + (paid_amount + adusted_amount),
                        'DEBIT',
                        invoice_amount + (paid_amount + adusted_amount),
                        'STANDARD',
                        invoice_amount + (paid_amount + adusted_amount)
                )),
            0
       )
       + NVL (SUM(DECODE (invoice_type_lookup_code,
                          'PREPAYMENT',
                          (invoice_amount + (paid_amount + adjusted_amount))
                  )),
              0
         )
          "AMOUNT",
       NVL (SUM(DECODE (invoice_type_lookup_code,
                        'CREDIT',
                        invoice_amount + (paid_amount + adusted_amount),
                        'DEBIT',
                        invoice_amount + (paid_amount + adusted_amount),
                        'STANDARD',
                        invoice_amount + (paid_amount + adusted_amount)
                )),
            0
       )
          "LIABILITY",
       NVL (SUM(DECODE (invoice_type_lookup_code,
                        'PREPAYMENT',
                        (invoice_amount + (paid_amount + adjusted_amount))
                )),
            0
       )
          "PREPAYMENT"
FROM (SELECT DISTINCT
             ai.vendor_id,
             aps.vendor_name,
             aps.segment1 "VENDOR_NUM",
             aps.vendor_type_lookup_code,
             apss1.vendor_site_code,
             ai.org_id,
             ai.invoice_id,
             ai.invoice_num,
             ai.invoice_date,
             ai.invoice_type_lookup_code,
             ROUND (DECODE (ai.invoice_type_lookup_code,
                            'PREPAYMENT',
                            ai.invoice_amount * NVL (ai.exchange_rate, 1),
                            ai.invoice_amount * NVL (ai.exchange_rate, 1) * -1
                    ),
                    2
             )
                invoice_amount,
             ai.payment_status_flag,
             NVL ( (SELECT ROUND (SUM(NVL (aip.amount, 0)
                                      * NVL (ai.exchange_rate, 1)),
                                  2
                           )
                              amount_paid
                    FROM apps.ap_invoice_payments_all aip
                    WHERE     aip.invoice_id = ai.invoice_id
                          AND TRUNC (aip.accounting_date) <= :p89_date
                          AND ai.invoice_type_lookup_code <> 'PREPAYMENT'),
                  0
             )
                paid_amount,
             NVL (cc.code_combination,
                  (SELECT gk.concatenated_segments
                   FROM apps.gl_code_combinations_kfv gk
                   WHERE gk.code_combination_id =
                            ai.accts_pay_code_combination_id)
             )
                code_combination,
             ROUND ( (SELECT NVL (SUM (amount) * -1, 0)
                      FROM apps.ap_invoice_lines_all aia
                      WHERE     aia.invoice_id = ai.invoice_id
                            AND aia.org_id = ai.org_id
                            AND aia.line_type_lookup_code = 'PREPAY'
                            AND TRUNC (aia.accounting_date) <= :p89_date)
                    * NVL (ai.exchange_rate, 1),
                    2
             )
                adusted_amount,
             ROUND ( (SELECT NVL (SUM (amount), 0)
                      FROM apps.ap_invoice_lines_all aim
                      WHERE     aim.prepay_invoice_id = ai.invoice_id
                            AND aim.org_id = ai.org_id
                            AND TRUNC (aim.accounting_date) <= :p89_date)
                    * NVL (ai.exchange_rate, 1),
                    2
             )
                adjusted_amount
      FROM apps.ap_invoices_all ai,
           apps.ap_suppliers aps,
           apps.ap_supplier_sites_all apss1,
           apps.ap_invoice_distributions_all ad,
           apps.ap_invoice_lines_all ail,
           (SELECT DISTINCT
                   xd.applied_to_source_id_num_1,
                   DECODE (xal.accounting_class_code,
                      'PREPAID_EXPENSE', 'PREPAYMENT',
                      xal.accounting_class_code)
                      "CODE",
                      gcc.segment1
                   || '.'
                   || gcc.segment2
                   || '.'
                   || gcc.segment3
                   || '.'
                   || gcc.segment4
                   || '.'
                   || gcc.segment5
                   || '.'
                   || gcc.segment6
                   || '.'
                   || gcc.segment7
                      code_combination
            FROM apps.xla_ae_lines xal,
                 apps.xla_distribution_links xd,
                 apps.gl_code_combinations gcc
            WHERE xd.ae_header_id = xal.ae_header_id
                  AND xd.ae_line_num = xal.ae_line_num
                  AND xal.accounting_class_code IN
                           ('LIABILITY', 'PREPAID_EXPENSE')
                  AND xal.code_combination_id = gcc.code_combination_id) cc
      WHERE     ai.invoice_id = ad.invoice_id
            AND ad.match_status_flag = 'A'
            AND ai.vendor_id = aps.vendor_id
            AND ai.vendor_id = apss1.vendor_id
            AND ai.vendor_site_id = apss1.vendor_site_id
            AND ai.org_id = apss1.org_id
            AND ai.invoice_id = cc.applied_to_source_id_num_1(+)
            AND ai.invoice_id = ail.invoice_id
            AND ai.org_id = ail.org_id
            AND DECODE (ai.invoice_type_lookup_code,
                  'PREPAYMENT', ai.invoice_type_lookup_code,
                  'LIABILITY') = cc.code(+)
            AND ad.line_type_lookup_code <> 'AWT'
            AND ai.org_id = :p89_org_id
            AND NOT (ai.invoice_type_lookup_code = 'PREPAYMENT'
                     AND payment_status_flag = 'N')
            AND TRUNC (ad.accounting_date) <= :p89_date
      ORDER BY aps.segment1)
GROUP BY vendor_type_lookup_code,
         org_id,
         vendor_num,
         vendor_name,
         vendor_site_code,
         code_combination

Employee Termination Report

SELECT ppf.employee_number
       , ppf.full_name, ppf.email_address, pps.date_start "Start Date",
       pps.actual_termination_date
                                      ,
       (SELECT l.meaning
          FROM hr_lookups l
         WHERE l.lookup_type(+) = 'LEAV_REAS'
               AND l.lookup_code(+) = pps.leaving_reason) leaving_reason
                                                                       
       ,
       pj.NAME job, houa.NAME organization_name,
       fu.user_name "Oracle User Name", fu.start_date "Oracle Start Date",
       fu.end_date "Oracle End Date",
       (SELECT MAX (start_time)
          FROM fnd_logins
         WHERE user_id = fu.user_id) "Last Logon Date",
       pps.last_update_date "Terminated On"              
                                           ,
       NVL2 (fu.end_date,
             fu.end_date - pps.actual_termination_date,
             NULL
            ) "Oracle end date after term"        
                                          ,
       NVL2 ((SELECT MAX (start_time)
                FROM fnd_logins
               WHERE user_id = fu.user_id),
             (SELECT MAX (start_time)
                FROM fnd_logins
               WHERE user_id = fu.user_id) - pps.actual_termination_date,
             NULL
            ) "Logon after term"                  
  FROM per_people_f ppf,
       per_people_f ppfs,
       per_assignments_f paf,
       per_assignment_status_types past,
       per_grades pg,
       per_jobs pj,
       per_job_groups pjg,
       per_pay_bases ppb,
       per_person_types ppt,
       pay_people_groups ppg,
       pay_payrolls_f pay,
       per_periods_of_service pps,
       hr_locations_all hl,
       hr_all_organization_units houg,
       hr_organization_units hougtl,
       hr_organization_units houa,
       hr_organization_units houb,
       hr_soft_coding_keyflex hsck,
       per_positions pap,
       per_person_type_usages_f pptu,
       fnd_user fu
 WHERE ppf.person_id = paf.person_id
   AND paf.business_group_id + 0 = houb.organization_id
   AND paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id(+)
   AND paf.organization_id = houa.organization_id(+)
   AND paf.job_id = pj.job_id(+)
   AND paf.grade_id = pg.grade_id(+)
   AND paf.people_group_id = ppg.people_group_id(+)
   AND paf.pay_basis_id = ppb.pay_basis_id(+)
   AND paf.payroll_id = pay.payroll_id(+)
   AND paf.period_of_service_id = pps.period_of_service_id(+)
   AND paf.assignment_status_type_id = past.assignment_status_type_id
   AND paf.supervisor_id = ppfs.person_id(+)
   AND paf.position_id = pap.position_id(+)
   AND pptu.person_type_id = ppt.person_type_id
   AND pptu.person_id = ppf.person_id
   AND pj.job_group_id = pjg.job_group_id(+)
   AND ppt.system_person_type IN
                        ('EMP', 'EMP_APL', 'EX_EMP', 'EX_EMP_APL', 'RETIREE')
   AND hsck.segment1 = TO_CHAR (houg.organization_id(+))
   AND houg.organization_id = hougtl.organization_id(+)
   AND paf.location_id = hl.location_id(+)
   AND paf.organization_id = houa.organization_id(+)
   AND paf.effective_end_date BETWEEN NVL (ppfs.effective_start_date,
                                           paf.effective_end_date
                                          )
                                  AND NVL (ppfs.effective_end_date,
                                           paf.effective_end_date
                                          )
   AND paf.effective_end_date BETWEEN NVL (pay.effective_start_date,
                                           paf.effective_end_date
                                          )
                                  AND NVL (pay.effective_end_date,
                                           paf.effective_end_date
                                          )
   AND pps.actual_termination_date BETWEEN paf.effective_start_date
                                       AND paf.effective_end_date
   AND pps.actual_termination_date BETWEEN ppf.effective_start_date
                                       AND ppf.effective_end_date
   AND NVL (pps.actual_termination_date, SYSDATE) >=
               NVL (NVL (:p_start_date, pps.actual_termination_date), SYSDATE)
   AND NVL (pps.actual_termination_date, SYSDATE) <=
                 NVL (NVL (:p_end_date, pps.actual_termination_date), SYSDATE)
   AND NVL (ppf.current_employee_flag, 'N') = 'Y'
   AND NVL (paf.primary_flag, 'N') = 'Y'
   AND paf.assignment_type = 'E'
   AND pps.actual_termination_date BETWEEN pptu.effective_start_date
                                       AND pptu.effective_end_date
   AND system_person_type = 'EMP'
   AND ppf.person_id = fu.employee_id(+)
   AND (SELECT person_type_id
          FROM (SELECT   person_id, object_version_number, person_type_id
                    FROM per_people_f
                ORDER BY person_id, object_version_number DESC) x
         WHERE ROWNUM = 1 AND person_id = ppf.person_id) NOT IN (
                                              SELECT person_type_id
                                                FROM per_person_types
                                               WHERE system_person_type =
                                                                         'EMP');

Trial Balance Report Output mismatched egin_balance and end_balance amounts in oracle apps

/* Formatted on 11/16/2016 1:21:22 PM (QP5 v5.114.809.3010) */
  SELECT   GCC.SEGMENT5 "ACCOUNT",
           FND.DESCRIPTION "DESCRIPTION",
           GJH.JE_SOURCE "SOURCE",
           (SELECT   SUM (NVL (GJL2.ACCOUNTED_DR, 0))
                     - SUM (NVL (GJL2.ACCOUNTED_CR, 0))
              FROM   GL_JE_LINES GJL2, GL_JE_HEADERS GJH2
             WHERE       1 = 1
                     AND GJL2.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
                     AND GJL2.LEDGER_ID = 2021
                     AND TRUNC (GJL2.EFFECTIVE_DATE) <
                           (SELECT   TRUNC (START_DATE)
                              FROM   GL_PERIODS
                             WHERE   PERIOD_NAME = 'Apr-16')
                     AND GJL2.JE_HEADER_ID = GJH2.JE_HEADER_ID
                     AND GJH2.ACTUAL_FLAG = 'A'
                     AND GJH2.JE_SOURCE != 'Consolidation'
                     AND GJH2.LEDGER_ID = GJL2.LEDGER_ID
                     AND GJH2.JE_SOURCE = GJH.JE_SOURCE
                     AND GJL2.STATUS = 'P')
              "BEGIN_BALANCE",
           (  SELECT   SUM (NVL (GJL1.ACCOUNTED_DR, 0))
                FROM   GL_JE_LINES GJL1, GL_JE_HEADERS GJH1
               WHERE       1 = 1
                       AND GJL1.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
                       AND GJL1.LEDGER_ID = 2021
                       AND GJH1.LEDGER_ID = GJL1.LEDGER_ID
                       AND GJH1.ACTUAL_FLAG = 'A'
                       AND GJH1.JE_SOURCE != 'Consolidation'
                       AND GJH1.JE_HEADER_ID = GJL1.JE_HEADER_ID
                       AND TRUNC (GJL1.EFFECTIVE_DATE) BETWEEN (SELECT   TRUNC(START_DATE)
                                                                  FROM   GL_PERIODS
                                                                 WHERE   PERIOD_SET_NAME =
                                                                            'CORPORATE'
                                                                         AND PERIOD_NAME =
                                                                               'Apr-16')
                                                           AND  (SELECT   TRUNC(END_DATE)
                                                                   FROM   GL_PERIODS
                                                                  WHERE   PERIOD_SET_NAME =
                                                                             'CORPORATE'
                                                                          AND PERIOD_NAME =
                                                                                'Apr-16')
                       AND GJH1.JE_SOURCE = GJH.JE_SOURCE
                       AND GJL1.STATUS = 'P'
            GROUP BY   GCC.SEGMENT5, FND.DESCRIPTION, GJH.JE_SOURCE)
              "DEBIT",
           (  SELECT   SUM (NVL (GJL1.ACCOUNTED_CR, 0))
                FROM   GL_JE_LINES GJL1, GL_JE_HEADERS GJH1
               WHERE       1 = 1
                       AND GJL1.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
                       AND GJL1.LEDGER_ID = 2021
                       AND GJH1.LEDGER_ID = GJL1.LEDGER_ID
                       AND GJH1.ACTUAL_FLAG = 'A'
                       AND GJH1.JE_SOURCE != 'Consolidation'
                       AND GJH1.JE_HEADER_ID = GJL1.JE_HEADER_ID
                       AND TRUNC (GJL1.EFFECTIVE_DATE) BETWEEN (SELECT   TRUNC(START_DATE)
                                                                  FROM   GL_PERIODS
                                                                 WHERE   PERIOD_SET_NAME =
                                                                            'CORPORATE'
                                                                         AND PERIOD_NAME =
                                                                               'Apr-16')
                                                           AND  (SELECT   TRUNC(END_DATE)
                                                                   FROM   GL_PERIODS
                                                                  WHERE   PERIOD_SET_NAME =
                                                                             'CORPORATE'
                                                                          AND PERIOD_NAME =
                                                                                'Apr-16')
                       AND GJH1.JE_SOURCE = GJH.JE_SOURCE
                       AND GJL1.STATUS = 'P'
            GROUP BY   GCC.SEGMENT5, FND.DESCRIPTION, GJH.JE_SOURCE)
              "CREDIT",
           (SELECT   SUM (NVL (GJL2.ACCOUNTED_DR, 0))
                     - SUM (NVL (GJL2.ACCOUNTED_CR, 0))
              FROM   GL_JE_LINES GJL2, GL_JE_HEADERS GJH2
             WHERE       1 = 1
                     AND GJL2.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
                     AND GJL2.LEDGER_ID = 2021
                     AND UPPER (GJL2.EFFECTIVE_DATE) <=
                           (SELECT   TRUNC (END_DATE)
                              FROM   GL_PERIODS
                             WHERE   PERIOD_NAME = 'Apr-16')
                     AND GJL2.JE_HEADER_ID = GJH2.JE_HEADER_ID
                     AND GJH2.ACTUAL_FLAG = 'A'
                     AND GJH2.JE_SOURCE != 'Consolidation'
                     AND GJH2.LEDGER_ID = GJL2.LEDGER_ID
                     AND GJH2.JE_SOURCE = GJH.JE_SOURCE
                     AND GJL2.STATUS = 'P')
              "END_BALANCE"
    FROM   GL_CODE_COMBINATIONS GCC,
           FND_FLEX_VALUES_VL FND,
           GL_JE_LINES GJL,
           GL_JE_HEADERS GJH
   WHERE       1 = 1
           AND GCC.CHART_OF_ACCOUNTS_ID = 50420
--           AND GCC.SEGMENT1 BETWEEN P_SEGMENT1_LOW AND P_SEGMENT1_HIGH
           AND GCC.SUMMARY_FLAG = 'N'
           AND GCC.TEMPLATE_ID IS NULL
           AND FND.FLEX_VALUE = GCC.SEGMENT5
           AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
           AND GJL.LEDGER_ID = 2021
           AND GJL.STATUS = 'P'
           AND GJH.CURRENCY_CODE = 'USD'
           AND GJH.LEDGER_ID = GJL.LEDGER_ID
           AND GJH.ACTUAL_FLAG = 'A'
           AND GJH.JE_SOURCE != 'Consolidation'
           AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
           --AND NVL2(P_SOURCE,GJH.JE_SOURCE,1) = NVL2(P_SOURCE,P_SOURCE,1)
           --ADDED ADDITIONAL LOGIC FOR RESTRICTING ADDITIONAL ACCOUNTS WHICH ARE NOT OCCURING IN TRIAL BALANCE REPORT
           AND (   (SELECT   SUM (BEGIN_BALANCE_DR) - SUM (BEGIN_BALANCE_CR)
                      FROM   GL_BALANCES
                     WHERE       CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
                             AND LEDGER_ID = GJL.LEDGER_ID
                             AND CURRENCY_CODE = 'USD'
                             AND PERIOD_NAME IN ('Apr-16')) != 0
                OR (SELECT   SUM (PERIOD_NET_DR)
                      FROM   GL_BALANCES
                     WHERE       CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
                             AND LEDGER_ID = GJL.LEDGER_ID
                             AND CURRENCY_CODE = 'USD'
                             AND PERIOD_NAME IN ('Apr-16')) != 0
                OR (SELECT   SUM (PERIOD_NET_CR)
                      FROM   GL_BALANCES
                     WHERE       CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
                             AND LEDGER_ID = GJL.LEDGER_ID
                             AND CURRENCY_CODE = 'USD'
                             AND PERIOD_NAME IN ('Apr-16')) != 0)
---------------------END OF ADDITIONAL LOGIC
GROUP BY   GCC.SEGMENT5,
           FND.DESCRIPTION,
           GJH.JE_SOURCE,
           GCC.CODE_COMBINATION_ID
ORDER BY   1;