Sunday, June 9, 2019

GL Breakup

GL - AR Breakup
================================


SELECT gjh.name jv_name,
       gjh.je_category jv_category,
       gjh.je_source jv_source,
       gsl.je_line_num,
       gjl.period_name period,
       TO_CHAR (gsl.accounting_date, 'DD-Mon-YYYY') accounting_date,
       NVL (gjl.accounted_cr, 0) - NVL (gjl.accounted_dr, 0) gl_amount,
       DECODE (gsl.gl_sl_link_id,
               NULL, NVL (gjl.accounted_cr, 0) - NVL (gjl.accounted_dr, 0),
               NVL (gsl.accounted_cr, 0) - NVL (gsl.accounted_dr, 0))
          sla_amount,
       rcta.interface_header_attribute2 plot_number,
       rcta.trx_number invoice_number,
       NULL adjustiment_number,
       NULL receipt_number,
       ac.customer_name,
       ac.customer_number,
       gsl.description
  FROM (SELECT /*+ leading(ir,l,h) cardinality(ir,10)  */
              ir.je_header_id,
               ir.je_line_num,
               TO_NUMBER (te.source_id_int_1) transaction_id,
               l.code_combination_id,
               l.accounted_dr,
               l.accounted_cr,
               l.currency_code,
               h.accounting_date,
               l.gl_sl_link_id,
               l.gl_sl_link_table,
               h.period_name,
               h.je_category_name,
               h.application_id,
               h.description
          FROM xla.xla_ae_headers h,
               xla.xla_ae_lines l,
               xla.xla_events e,
               xla.xla_transaction_entities te,
               apps.gl_import_references ir
         WHERE     e.entity_id = te.entity_id
               AND e.application_id = te.application_id
               AND te.application_id = h.application_id
               AND te.entity_id = h.entity_id
               AND e.event_id = h.event_id
               AND e.application_id = h.application_id
               AND h.ae_header_id = l.ae_header_id
               AND h.application_id = l.application_id
               AND l.gl_sl_link_id = ir.gl_sl_link_id
               AND l.gl_sl_link_table = ir.gl_sl_link_table) gsl,
       apps.gl_je_headers gjh,
       apps.gl_je_lines gjl,
       apps.ra_customer_trx_all rcta,
       apps.ar_customers ac
 WHERE     gsl.je_header_id(+) = gjl.je_header_id
       AND gjl.je_header_id = gjh.je_header_id
       AND gjl.je_line_num = gsl.je_line_num(+)
       AND gjl.code_combination_id = :p_code_combination_id
       AND gjl.period_name LIKE '%' || :p_year
       AND gjh.status = 'P'          --This will pick all posted journal entry
       AND gjh.actual_flag = 'A' -- This is for Actual entry A-Actual, B-Budget
       AND rcta.customer_trx_id(+) = transaction_id
       AND ac.customer_id(+) = rcta.bill_to_customer_id
       AND je_category = 'Sales Invoices'
UNION ALL
SELECT gjh.name jv_name,
       gjh.je_category jv_category,
       gjh.je_source jv_source,
       gsl.je_line_num,
       gjl.period_name period,
       TO_CHAR (gsl.accounting_date, 'DD-Mon-YYYY') accounting_date,
       NVL (gjl.accounted_cr, 0) - NVL (gjl.accounted_dr, 0) gl_amount,
       DECODE (gsl.gl_sl_link_id,
               NULL, NVL (gjl.accounted_cr, 0) - NVL (gjl.accounted_dr, 0),
               NVL (gsl.accounted_cr, 0) - NVL (gsl.accounted_dr, 0))
          sla_amount,
       rcta.interface_header_attribute2 plot_number,
       rcta.trx_number invoice_number,
       NULL adjustiment_number,
       NULL receipt_number,
       ac.customer_name,
       ac.customer_number,
       gsl.description
  FROM (SELECT /*+ leading(ir,l,h) cardinality(ir,10)  */
              ir.je_header_id,
               ir.je_line_num,
               TO_NUMBER (te.source_id_int_1) transaction_id,
               l.code_combination_id,
               l.accounted_dr,
               l.accounted_cr,
               l.currency_code,
               h.accounting_date,
               l.gl_sl_link_id,
               l.gl_sl_link_table,
               h.period_name,
               h.je_category_name,
               h.application_id,
               h.description
          FROM xla.xla_ae_headers h,
               xla.xla_ae_lines l,
               xla.xla_events e,
               xla.xla_transaction_entities te,
               apps.gl_import_references ir
         WHERE     e.entity_id = te.entity_id
               AND e.application_id = te.application_id
               AND te.application_id = h.application_id
               AND te.entity_id = h.entity_id
               AND e.event_id = h.event_id
               AND e.application_id = h.application_id
               AND h.ae_header_id = l.ae_header_id
               AND h.application_id = l.application_id
               AND l.gl_sl_link_id = ir.gl_sl_link_id
               AND l.gl_sl_link_table = ir.gl_sl_link_table) gsl,
       apps.gl_je_headers gjh,
       apps.gl_je_lines gjl,
       apps.ra_customer_trx_all rcta,
       apps.ar_customers ac
 WHERE     gsl.je_header_id(+) = gjl.je_header_id
       AND gjl.je_header_id = gjh.je_header_id
       AND gjl.je_line_num = gsl.je_line_num(+)
       AND gjl.code_combination_id = :p_code_combination_id
       AND gjl.period_name LIKE '%' || :p_year
       AND gjh.status = 'P'          --This will pick all posted journal entry
       AND gjh.actual_flag = 'A' -- This is for Actual entry A-Actual, B-Budget
       AND rcta.customer_trx_id(+) = transaction_id
       AND ac.customer_id(+) = rcta.bill_to_customer_id
       AND je_category = 'Credit Memos'
UNION ALL
SELECT gjh.name jv_name,
       gjh.je_category jv_category,
       gjh.je_source jv_source,
       gsl.je_line_num,
       gjl.period_name period,
       TO_CHAR (gsl.accounting_date, 'DD-Mon-YYYY') accounting_date,
       NVL (gjl.accounted_cr, 0) - NVL (gjl.accounted_dr, 0) gl_amount,
       DECODE (gsl.gl_sl_link_id,
               NULL, NVL (gjl.accounted_cr, 0) - NVL (gjl.accounted_dr, 0),
               NVL (gsl.accounted_cr, 0) - NVL (gsl.accounted_dr, 0))
          sla_amount,
       rcta.interface_header_attribute2 plot_number,
       rcta.trx_number invoice_number,
       NULL adjustiment_number,
       NULL receipt_number,
       ac.customer_name,
       ac.customer_number,
       gsl.description
  FROM (SELECT /*+ leading(ir,l,h) cardinality(ir,10)  */
              ir.je_header_id,
               ir.je_line_num,
               TO_NUMBER (te.source_id_int_1) transaction_id,
               l.code_combination_id,
               l.accounted_dr,
               l.accounted_cr,
               l.currency_code,
               h.accounting_date,
               l.gl_sl_link_id,
               l.gl_sl_link_table,
               h.period_name,
               h.je_category_name,
               h.application_id,
               h.description
          FROM xla.xla_ae_headers h,
               xla.xla_ae_lines l,
               xla.xla_events e,
               xla.xla_transaction_entities te,
               apps.gl_import_references ir
         WHERE     e.entity_id = te.entity_id
               AND e.application_id = te.application_id
               AND te.application_id = h.application_id
               AND te.entity_id = h.entity_id
               AND e.event_id = h.event_id
               AND e.application_id = h.application_id
               AND h.ae_header_id = l.ae_header_id
               AND h.application_id = l.application_id
               AND l.gl_sl_link_id = ir.gl_sl_link_id
               AND l.gl_sl_link_table = ir.gl_sl_link_table) gsl,
       apps.gl_je_headers gjh,
       apps.gl_je_lines gjl,
       apps.ra_customer_trx_all rcta,
       apps.ar_customers ac
 WHERE     gsl.je_header_id(+) = gjl.je_header_id
       AND gjl.je_header_id = gjh.je_header_id
       AND gjl.je_line_num = gsl.je_line_num(+)
       AND gjl.code_combination_id = :p_code_combination_id
       AND gjl.period_name LIKE '%' || :p_year
       AND gjh.status = 'P'          --This will pick all posted journal entry
       AND gjh.actual_flag = 'A' -- This is for Actual entry A-Actual, B-Budget
       AND rcta.customer_trx_id(+) = transaction_id
       AND ac.customer_id(+) = rcta.bill_to_customer_id
       AND je_category = 'Debit Memos'
UNION ALL
SELECT gjh.name jv_name,
       gjh.je_category jv_category,
       gjh.je_source jv_source,
       gsl.je_line_num,
       gjl.period_name period,
       TO_CHAR (gsl.accounting_date, 'DD-Mon-YYYY') accounting_date,
       NVL (gjl.accounted_cr, 0) - NVL (gjl.accounted_dr, 0) gl_amount,
       DECODE (gsl.gl_sl_link_id,
               NULL, NVL (gjl.accounted_cr, 0) - NVL (gjl.accounted_dr, 0),
               NVL (gsl.accounted_cr, 0) - NVL (gsl.accounted_dr, 0))
          sla_amount,
       rcta.interface_header_attribute2 plot_number,
       rcta.trx_number invoice_number,
       ADJUSTMENT_NUMBER  adjustiment_number,
       NULL receipt_number,
       ac.customer_name,
       ac.customer_number,
       gsl.description
  FROM (SELECT /*+ leading(ir,l,h) cardinality(ir,10)  */
              ir.je_header_id,
               ir.je_line_num,
               TO_NUMBER (te.source_id_int_1) transaction_id,
               l.code_combination_id,
               l.accounted_dr,
               l.accounted_cr,
               l.currency_code,
               h.accounting_date,
               l.gl_sl_link_id,
               l.gl_sl_link_table,
               h.period_name,
               h.je_category_name,
               h.application_id,
               h.description
          FROM xla.xla_ae_headers h,
               xla.xla_ae_lines l,
               xla.xla_events e,
               xla.xla_transaction_entities te,
               apps.gl_import_references ir
         WHERE     e.entity_id = te.entity_id
               AND e.application_id = te.application_id
               AND te.application_id = h.application_id
               AND te.entity_id = h.entity_id
               AND e.event_id = h.event_id
               AND e.application_id = h.application_id
               AND h.ae_header_id = l.ae_header_id
               AND h.application_id = l.application_id
               AND l.gl_sl_link_id = ir.gl_sl_link_id
               AND l.gl_sl_link_table = ir.gl_sl_link_table) gsl,
       apps.gl_je_headers gjh,
       apps.gl_je_lines gjl,
       apps.ar_adjustments_all aaa,
       apps.ra_customer_trx_all rcta,
       apps.ar_customers ac
 WHERE     gsl.je_header_id(+) = gjl.je_header_id
       AND gjl.je_header_id = gjh.je_header_id
       AND gjl.je_line_num = gsl.je_line_num(+)
       AND gjl.code_combination_id = :p_code_combination_id
       AND gjl.period_name LIKE '%' || :p_year
       AND gjh.status = 'P'          --This will pick all posted journal entry
       AND gjh.actual_flag = 'A' -- This is for Actual entry A-Actual, B-Budget
       AND aaa.adjustment_id(+) = transaction_id
       AND rcta.customer_trx_id(+) = aaa.customer_trx_id
       AND ac.customer_id(+) = rcta.bill_to_customer_id
       AND je_category = 'Adjustment'
UNION ALL
SELECT gjh.name jv_name,
       gjh.je_category jv_category,
       gjh.je_source jv_source,
       gsl.je_line_num,
       gjl.period_name period,
       TO_CHAR (gsl.accounting_date, 'DD-Mon-YYYY') accounting_date,
       NVL (gjl.accounted_cr, 0) - NVL (gjl.accounted_dr, 0) gl_amount,
       DECODE (gsl.gl_sl_link_id,
               NULL, NVL (gjl.accounted_cr, 0) - NVL (gjl.accounted_dr, 0),
               NVL (gsl.accounted_cr, 0) - NVL (gsl.accounted_dr, 0))
          sla_amount,
       (SELECT location_code
          FROM apps.pn_locations_all
         WHERE TO_CHAR (location_id) = rcta.attribute2)
          plot_number,
       NULL invoice_number,
       NULL adjustiment_number,
       rcta.receipt_number receipt_number,
       ac.customer_name,
       ac.customer_number,
       gsl.description
  FROM (SELECT /*+ leading(ir,l,h) cardinality(ir,10)  */
              ir.je_header_id,
               ir.je_line_num,
               TO_NUMBER (te.source_id_int_1) transaction_id,
               l.code_combination_id,
               l.accounted_dr,
               l.accounted_cr,
               l.currency_code,
               h.accounting_date,
               l.gl_sl_link_id,
               l.gl_sl_link_table,
               h.period_name,
               h.je_category_name,
               h.application_id,
               h.description
          FROM xla.xla_ae_headers h,
               xla.xla_ae_lines l,
               xla.xla_events e,
               xla.xla_transaction_entities te,
               apps.gl_import_references ir
         WHERE     e.entity_id = te.entity_id
               AND e.application_id = te.application_id
               AND te.application_id = h.application_id
               AND te.entity_id = h.entity_id
               AND e.event_id = h.event_id
               AND e.application_id = h.application_id
               AND h.ae_header_id = l.ae_header_id
               AND h.application_id = l.application_id
               AND l.gl_sl_link_id = ir.gl_sl_link_id
               AND l.gl_sl_link_table = ir.gl_sl_link_table) gsl,
       apps.gl_je_headers gjh,
       apps.gl_je_lines gjl,
       apps.ar_cash_receipts_all rcta,
       apps.ar_customers ac
 WHERE     gsl.je_header_id(+) = gjl.je_header_id
       AND gjl.je_header_id = gjh.je_header_id
       AND gjl.je_line_num = gsl.je_line_num(+)
       AND gjl.code_combination_id = :p_code_combination_id
       AND gjl.period_name LIKE '%' || :p_year
       AND gjh.status = 'P'          --This will pick all posted journal entry
       AND gjh.actual_flag = 'A' -- This is for Actual entry A-Actual, B-Budget
       AND rcta.cash_receipt_id(+) = transaction_id
       AND ac.customer_id(+) = rcta.pay_from_customer
       AND je_category = 'Receipts'
UNION ALL
SELECT gjh.name jv_name,
       gjh.je_category jv_category,
       gjh.je_source jv_source,
       gsl.je_line_num,
       gjl.period_name period,
       TO_CHAR (gsl.accounting_date, 'DD-Mon-YYYY') accounting_date,
       NVL (gjl.accounted_cr, 0) - NVL (gjl.accounted_dr, 0) gl_amount,
       DECODE (gsl.gl_sl_link_id,
               NULL, NVL (gjl.accounted_cr, 0) - NVL (gjl.accounted_dr, 0),
               NVL (gsl.accounted_cr, 0) - NVL (gsl.accounted_dr, 0))
          sla_amount,
       NULL plot_number,
       NULL invoice_number,
       NULL adjustiment_number,
       NULL receipt_number,
       NULL customer_name,
       NULL customer_number,
       NULL description
  FROM (SELECT /*+ leading(ir,l,h) cardinality(ir,10)  */
              ir.je_header_id,
               ir.je_line_num,
               TO_NUMBER (te.source_id_int_1) transaction_id,
               l.code_combination_id,
               l.accounted_dr,
               l.accounted_cr,
               l.currency_code,
               h.accounting_date,
               l.gl_sl_link_id,
               l.gl_sl_link_table,
               h.period_name,
               h.je_category_name,
               h.application_id,
               h.description
          FROM xla.xla_ae_headers h,
               xla.xla_ae_lines l,
               xla.xla_events e,
               xla.xla_transaction_entities te,
               apps.gl_import_references ir
         WHERE     e.entity_id = te.entity_id
               AND e.application_id = te.application_id
               AND te.application_id = h.application_id
               AND te.entity_id = h.entity_id
               AND e.event_id = h.event_id
               AND e.application_id = h.application_id
               AND h.ae_header_id = l.ae_header_id
               AND h.application_id = l.application_id
               AND l.gl_sl_link_id = ir.gl_sl_link_id
               AND l.gl_sl_link_table = ir.gl_sl_link_table) gsl,
       apps.gl_je_headers gjh,
       apps.gl_je_lines gjl,
       apps.ar_cash_receipts_all rcta,
       apps.ar_customers ac
 WHERE     gsl.je_header_id(+) = gjl.je_header_id
       AND gjl.je_header_id = gjh.je_header_id
       AND gjl.je_line_num = gsl.je_line_num(+)
       AND gjl.code_combination_id = :p_code_combination_id
       AND gjl.period_name LIKE '%' || :p_year
       AND gjh.status = 'P'          --This will pick all posted journal entry
       AND gjh.actual_flag = 'A' -- This is for Actual entry A-Actual, B-Budget
       AND rcta.cash_receipt_id(+) = transaction_id
       AND ac.customer_id(+) = rcta.pay_from_customer
       AND je_category = '210'

===========================================================

Encumbrance Query

=============

/* Formatted on 18/09/2017 09:21:20 (QP5 v5.256.13226.35510) */
  SELECT 'Requisitions' "GL Entry Type",
         prha.creation_date,
         prha.segment1 "REQ Number",
         TO_CHAR (prha.creation_date, 'DD-Mon-YYYY') "REQ Creation Date",
         gsl.description "JV REQ Number",
         SUM (
              DECODE (
                 gsl.gl_sl_link_id,
                 NULL, NVL (gjl.accounted_cr, 0) - NVL (gjl.accounted_dr, 0),
                 NVL (gsl.accounted_cr, 0) - NVL (gsl.accounted_dr, 0))
            * -1)
            "Amount"
    FROM (SELECT /*+ leading(ir,l,h) cardinality(ir,10)  */
                ir.je_header_id,
                 ir.je_line_num,
                 TO_NUMBER (te.source_id_int_1) transaction_id,
                 l.code_combination_id,
                 l.accounted_dr,
                 l.accounted_cr,
                 l.currency_code,
                 h.accounting_date,
                 l.gl_sl_link_id,
                 l.gl_sl_link_table,
                 h.period_name,
                 h.je_category_name,
                 h.application_id,
                 h.description,
                 e.event_id,
                 h.ae_header_id,
                 l.ae_line_num
            FROM xla.xla_ae_headers h,
                 xla.xla_ae_lines l,
                 xla.xla_events e,
                 xla.xla_transaction_entities te,
                 apps.gl_import_references ir
           WHERE     e.entity_id = te.entity_id
                 AND e.application_id = te.application_id
                 AND te.application_id = h.application_id
                 AND te.entity_id = h.entity_id
                 AND e.event_id = h.event_id
                 AND e.application_id = h.application_id
                 AND h.ae_header_id = l.ae_header_id
                 AND h.application_id = l.application_id
                 AND l.gl_sl_link_id = ir.gl_sl_link_id
                 AND l.gl_sl_link_table = ir.gl_sl_link_table) gsl,
         apps.gl_je_headers gjh,
         apps.gl_je_lines gjl,
         apps.po_requisition_headers_all prha
   WHERE     gsl.je_header_id(+) = gjl.je_header_id
         AND gjl.je_header_id = gjh.je_header_id
         AND gjl.je_line_num = gsl.je_line_num(+)
         AND gjl.code_combination_id = :p_code_combination_id
         AND gjl.period_name LIKE '%' || :p_year
         AND gjh.status = 'P'        --This will pick all posted journal entry
         AND gjh.actual_flag = 'E' -- This is for Actual entry A-Actual, B-Budget
         AND je_category = 'Requisitions'
         AND prha.requisition_header_id(+) = gsl.transaction_id
GROUP BY prha.segment1, gsl.description, prha.creation_date
  HAVING SUM (
              DECODE (
                 gsl.gl_sl_link_id,
                 NULL, NVL (gjl.accounted_cr, 0) - NVL (gjl.accounted_dr, 0),
                 NVL (gsl.accounted_cr, 0) - NVL (gsl.accounted_dr, 0))
            * -1) <> 0
UNION ALL
  SELECT 'Purchases',
         prha.creation_date,
         prha.segment1 "PO Number",
         TO_CHAR (prha.creation_date, 'DD-Mon-YYYY') "PO Creation Date",
         gsl.description "JV PO Number",
         SUM (
              DECODE (
                 gsl.gl_sl_link_id,
                 NULL, NVL (gjl.accounted_cr, 0) - NVL (gjl.accounted_dr, 0),
                 NVL (gsl.accounted_cr, 0) - NVL (gsl.accounted_dr, 0))
            * -1)
            "Amount"
    FROM (SELECT /*+ leading(ir,l,h) cardinality(ir,10)  */
                ir.je_header_id,
                 ir.je_line_num,
                 TO_NUMBER (te.source_id_int_1) transaction_id,
                 l.code_combination_id,
                 l.accounted_dr,
                 l.accounted_cr,
                 l.currency_code,
                 h.accounting_date,
                 l.gl_sl_link_id,
                 l.gl_sl_link_table,
                 h.period_name,
                 h.je_category_name,
                 h.application_id,
                 h.description,
                 e.event_id,
                 h.ae_header_id,
                 l.ae_line_num
            FROM xla.xla_ae_headers h,
                 xla.xla_ae_lines l,
                 xla.xla_events e,
                 xla.xla_transaction_entities te,
                 apps.gl_import_references ir
           WHERE     e.entity_id = te.entity_id
                 AND e.application_id = te.application_id
                 AND te.application_id = h.application_id
                 AND te.entity_id = h.entity_id
                 AND e.event_id = h.event_id
                 AND e.application_id = h.application_id
                 AND h.ae_header_id = l.ae_header_id
                 AND h.application_id = l.application_id
                 AND l.gl_sl_link_id = ir.gl_sl_link_id
                 AND l.gl_sl_link_table = ir.gl_sl_link_table) gsl,
         apps.gl_je_headers gjh,
         apps.gl_je_lines gjl,
         apps.po_headers_all prha
   WHERE     gsl.je_header_id(+) = gjl.je_header_id
         AND gjl.je_header_id = gjh.je_header_id
         AND gjl.je_line_num = gsl.je_line_num(+)
         AND gjl.code_combination_id = :p_code_combination_id
         AND gjl.period_name LIKE '%' || :p_year
         AND gjh.status = 'P'        --This will pick all posted journal entry
         AND gjh.actual_flag = 'E' -- This is for Actual entry A-Actual, B-Budget
         AND je_category = 'Purchases'
         AND prha.po_header_id(+) = gsl.transaction_id
GROUP BY prha.segment1, gsl.description, prha.creation_date
  HAVING SUM (
              DECODE (
                 gsl.gl_sl_link_id,
                 NULL, NVL (gjl.accounted_cr, 0) - NVL (gjl.accounted_dr, 0),
                 NVL (gsl.accounted_cr, 0) - NVL (gsl.accounted_dr, 0))
            * -1) <> 0
UNION ALL
  SELECT 'Receiving',
         pha.creation_date,
         pha.segment1 "PO Number",
         TO_CHAR (pha.creation_date, 'DD-Mon-YYYY') "PO Creation Date",
         gsl.description "JV PO Number",
         SUM (
              DECODE (
                 gsl.gl_sl_link_id,
                 NULL, NVL (gjl.accounted_cr, 0) - NVL (gjl.accounted_dr, 0),
                 NVL (gsl.accounted_cr, 0) - NVL (gsl.accounted_dr, 0))
            * -1)
            "Amount"
    FROM (SELECT /*+ leading(ir,l,h) cardinality(ir,10)  */
                ir.je_header_id,
                 ir.je_line_num,
                 TO_NUMBER (te.source_id_int_1) transaction_id,
                 l.code_combination_id,
                 l.accounted_dr,
                 l.accounted_cr,
                 l.currency_code,
                 h.accounting_date,
                 l.gl_sl_link_id,
                 l.gl_sl_link_table,
                 h.period_name,
                 h.je_category_name,
                 h.application_id,
                 h.description,
                 e.event_id,
                 h.ae_header_id,
                 l.ae_line_num,
                 prha.source_distribution_id_num_1,
                 TO_NUMBER (rrsl.reference3) po_dist_id
            FROM xla.xla_ae_headers h,
                 xla.xla_ae_lines l,
                 xla.xla_events e,
                 xla.xla_transaction_entities te,
                 apps.gl_import_references ir,
                 apps.xla_distribution_links prha,
                 apps.rcv_receiving_sub_ledger rrsl
           WHERE     e.entity_id = te.entity_id
                 AND e.application_id = te.application_id
                 AND te.application_id = h.application_id
                 AND te.entity_id = h.entity_id
                 AND e.event_id = h.event_id
                 AND e.application_id = h.application_id
                 AND h.ae_header_id = l.ae_header_id
                 AND h.application_id = l.application_id
                 AND l.gl_sl_link_id = ir.gl_sl_link_id
                 AND l.gl_sl_link_table = ir.gl_sl_link_table
                 AND prha.source_distribution_type = 'RCV_RECEIVING_SUB_LEDGER'
                 AND prha.ae_header_id = h.ae_header_id
                 AND prha.ae_line_num = l.ae_line_num
                 AND rrsl.rcv_sub_ledger_id = prha.source_distribution_id_num_1)
         gsl,
         apps.gl_je_headers gjh,
         apps.gl_je_lines gjl,
         apps.po_distributions_all pda,
         apps.po_headers_all pha
   WHERE     gsl.je_header_id(+) = gjl.je_header_id
         AND gjl.je_header_id = gjh.je_header_id
         AND gjl.je_line_num = gsl.je_line_num(+)
         AND gjl.code_combination_id = :p_code_combination_id
         AND gjl.period_name LIKE '%' || :p_year
         AND gjh.status = 'P'        --This will pick all posted journal entry
         AND gjh.actual_flag = 'E' -- This is for Actual entry A-Actual, B-Budget
         AND je_category = 'Receiving'
         AND gsl.po_dist_id = pda.po_distribution_id(+)
         AND pda.po_header_id = pha.po_header_id(+)
GROUP BY pha.segment1, gsl.description, pha.creation_date
  HAVING SUM (
              DECODE (
                 gsl.gl_sl_link_id,
                 NULL, NVL (gjl.accounted_cr, 0) - NVL (gjl.accounted_dr, 0),
                 NVL (gsl.accounted_cr, 0) - NVL (gsl.accounted_dr, 0))
            * -1) <> 0
UNION ALL
  SELECT 'Purchase Invoices',
         prha.creation_date,
         prha.invoice_num "Invoice Number",
         TO_CHAR (prha.creation_date, 'DD-Mon-YYYY') "Invoice Creation Date",
         gsl.description "JV INV Number",
         SUM (
              DECODE (
                 gsl.gl_sl_link_id,
                 NULL, NVL (gjl.accounted_cr, 0) - NVL (gjl.accounted_dr, 0),
                 NVL (gsl.accounted_cr, 0) - NVL (gsl.accounted_dr, 0))
            * -1)
            "Amount"
    FROM (SELECT /*+ leading(ir,l,h) cardinality(ir,10)  */
                ir.je_header_id,
                 ir.je_line_num,
                 TO_NUMBER (te.source_id_int_1) transaction_id,
                 l.code_combination_id,
                 l.accounted_dr,
                 l.accounted_cr,
                 l.currency_code,
                 h.accounting_date,
                 l.gl_sl_link_id,
                 l.gl_sl_link_table,
                 h.period_name,
                 h.je_category_name,
                 h.application_id,
                 h.description,
                 e.event_id,
                 h.ae_header_id,
                 l.ae_line_num
            FROM xla.xla_ae_headers h,
                 xla.xla_ae_lines l,
                 xla.xla_events e,
                 xla.xla_transaction_entities te,
                 apps.gl_import_references ir
           WHERE     e.entity_id = te.entity_id
                 AND e.application_id = te.application_id
                 AND te.application_id = h.application_id
                 AND te.entity_id = h.entity_id
                 AND e.event_id = h.event_id
                 AND e.application_id = h.application_id
                 AND h.ae_header_id = l.ae_header_id
                 AND h.application_id = l.application_id
                 AND l.gl_sl_link_id = ir.gl_sl_link_id
                 AND l.gl_sl_link_table = ir.gl_sl_link_table) gsl,
         apps.gl_je_headers gjh,
         apps.gl_je_lines gjl,
         apps.ap_invoices_all prha
   WHERE     gsl.je_header_id(+) = gjl.je_header_id
         AND gjl.je_header_id = gjh.je_header_id
         AND gjl.je_line_num = gsl.je_line_num(+)
         AND gjl.code_combination_id = :p_code_combination_id
         AND gjl.period_name LIKE '%' || :p_year
         AND gjh.status = 'P'        --This will pick all posted journal entry
         AND gjh.actual_flag = 'E' -- This is for Actual entry A-Actual, B-Budget
         AND je_category = 'Purchase Invoices'
         AND prha.invoice_id(+) = gsl.transaction_id
GROUP BY prha.invoice_num, gsl.description, prha.creation_date
  HAVING SUM (
              DECODE (
                 gsl.gl_sl_link_id,
                 NULL, NVL (gjl.accounted_cr, 0) - NVL (gjl.accounted_dr, 0),
                 NVL (gsl.accounted_cr, 0) - NVL (gsl.accounted_dr, 0))
            * -1) <> 0
ORDER BY 1, 2


============================

Remove Next Line from Sql
=============================

   regexp_replace(acr.comments ,'[[:space:]]',' ')

KEY: 3-28634-01281-67446-26577
MSG: ARABIAN DANISH PAINT COMPANY
KEY: 1-49900-63081-22632-88752
MSG: DUBAI WORLD


Print Negitive amount in Bracket - select to_char(-133133,'9999999999D99PR')  from dual


PDC
======================

 SELECT ac.customer_name,
         ac.customer_number,
         acr.receipt_number,
         acr.receipt_date,
         aps.due_date,
         acr.amount,
         SUM (NVL (ad.amount_dr, 0) - NVL (ad.amount_cr, 0))
    FROM apps.ar_cash_receipts_all acr,
         apps.ar_receipt_method_accounts_all arma,
         apps.gl_code_combinations_kfv gc,
         apps.ar_payment_schedules_all aps,
         apps.ar_customers ac,
         apps.ar_cash_receipt_history_all crh,
         apps.ar_distributions_all ad,
         apps.gl_code_combinations_kfv gc2
   WHERE     acr.org_id = 2965
         AND ac.customer_id = acr.pay_from_customer
         AND arma.receipt_method_id = acr.receipt_method_id
         AND arma.remit_bank_acct_use_id = acr.remit_bank_acct_use_id
         AND arma.cash_ccid = gc.code_combination_id
         AND aps.cash_receipt_id = acr.cash_receipt_id
         AND crh.cash_receipt_id = acr.cash_receipt_id
         AND gc.segment1 = '0811'
         AND ad.source_table = 'CRH'
         AND ad.source_id = crh.cash_receipt_history_id
         AND gc2.code_combination_id = ad.code_combination_id
         AND gc2.concatenated_segments =
--         AND crh.gl_date <= '30-JUN-2016'
         AND crh.gl_posted_date between '01-SEP-2016'  AND '30-SEP-2016'
GROUP BY ac.customer_name,
         ac.customer_number,
         acr.receipt_number,
         acr.receipt_date,
         aps.due_date,
         acr.amount
  HAVING SUM (NVL (ad.amount_dr, 0) - NVL (ad.amount_cr, 0)) <> 0
================================

AR Aging 
==============

SELECT a.*,
       CASE WHEN (TRUNC (SYSDATE) - due_date + 1) <= 0 THEN tot ELSE 0 END
          future_payment,
       CASE
          WHEN (TRUNC (SYSDATE) - due_date + 1) BETWEEN 1 AND 30 THEN tot
          ELSE 0
       END
          day_1_30,
       CASE
          WHEN (TRUNC (SYSDATE) - due_date + 1) BETWEEN 31 AND 60 THEN tot
          ELSE 0
       END
          day_31_60,
       CASE
          WHEN (TRUNC (SYSDATE) - due_date + 1) BETWEEN 61 AND 90 THEN tot
          ELSE 0
       END
          day_61_90,
       CASE
          WHEN (TRUNC (SYSDATE) - due_date + 1) BETWEEN 91 AND 180 THEN tot
          ELSE 0
       END
          day_91_180,
       CASE
          WHEN (TRUNC (SYSDATE) - due_date + 1) BETWEEN 181 AND 365 THEN tot
          ELSE 0
       END
          day_181_365,
       CASE WHEN (TRUNC (SYSDATE) - due_date + 1) > 365 THEN tot ELSE 0 END
          day_above_365
  FROM (  SELECT ac.customer_name,
                 ac.customer_number,
                 rct.trx_number,
                 rct.trx_date,
                 rct.ct_reference,
                 aps.due_date,
                 aps.amount_due_original,
                 SUM (
                      (aps.amount_due_original)
                    + (NVL (
                          (SELECT SUM (amount)
                             FROM apps.ar_adjustments_all
                            WHERE     customer_trx_id = rct.customer_trx_id --           AND apply_date   < = :p_date
                                  AND gl_date <= TO_DATE ( :p_date)),
                          0))
                    - (NVL (
                          (SELECT SUM (acctd_amount_applied_from)
                             FROM apps.ar_receivable_applications_all
                            WHERE     1 = 1
                                  --                                             apply_date                 < =to_date(:p_date)
                                  AND gl_date <= TO_DATE ( :p_date)
                                  AND application_type = 'CASH'
                                  AND applied_payment_schedule_id =
                                         aps.payment_schedule_id),
                          0))
                    + (NVL (
                          (SELECT SUM (acctd_amount_applied_from)
                             FROM apps.ar_receivable_applications_all
                            WHERE     1 = 1
                                  --                                             apply_date                 < =to_date(:p_date)
                                  AND gl_date <= TO_DATE ( :p_date)
                                  AND application_type = 'CM'
                                  AND customer_trx_id = aps.customer_trx_id),
                          0))
                    - (NVL (
                          (SELECT SUM (acctd_amount_applied_from)
                             FROM apps.ar_receivable_applications_all
                            WHERE     1 = 1
                                  --                                             apply_date                 < =to_date(:p_date)
                                  AND gl_date <= TO_DATE ( :p_date)
                                  AND application_type = 'CM'
                                  AND applied_customer_trx_id =
                                         aps.customer_trx_id),
                          0))                               --             ,0)
                             )
                    tot
            FROM apps.ra_customer_trx_all rct,
                 apps.ra_cust_trx_line_gl_dist_all rctgl,
                 apps.gl_code_combinations_kfv gc,
                 apps.ar_payment_schedules_all aps,
                 apps.ar_customers ac
           WHERE     rct.customer_trx_id = rctgl.customer_trx_id
                 AND ac.customer_id = rct.bill_to_customer_id
                 AND 'Y' =
                        DECODE (rct.invoicing_rule_id,
                                NULL, 'Y',
                                rctgl.account_set_flag)
                 AND rctgl.code_combination_id = gc.code_combination_id
                 AND gc.segment1 = '0811'
                 AND gc.segment3 = '121100'
                 AND rctgl.account_class = 'REC'
                 AND aps.customer_trx_id = rct.customer_trx_id
                 AND rct.org_id = 2965
                 AND aps.gl_date <= :p_date
        GROUP BY ac.customer_name,
                 rct.trx_number,
                 rct.trx_date,
                 rct.ct_reference,
                     ac.customer_number,
                 aps.due_date,
                 aps.amount_due_original
          HAVING SUM (
                      (aps.amount_due_original)
                    + (NVL (
                          (SELECT SUM (amount)
                             FROM apps.ar_adjustments_all
                            WHERE     customer_trx_id = rct.customer_trx_id --           AND apply_date   < = :p_date
                                  AND gl_date <= TO_DATE ( :p_date)),
                          0))
                    - (NVL (
                          (SELECT SUM (acctd_amount_applied_from)
                             FROM apps.ar_receivable_applications_all
                            WHERE     1 = 1
                                  --                                             apply_date                 < =to_date(:p_date)
                                  AND gl_date <= TO_DATE ( :p_date)
                                  AND application_type = 'CASH'
                                  AND applied_payment_schedule_id =
                                         aps.payment_schedule_id),
                          0))
                    + (NVL (
                          (SELECT SUM (acctd_amount_applied_from)
                             FROM apps.ar_receivable_applications_all
                            WHERE     1 = 1
                                  --                                             apply_date                 < =to_date(:p_date)
                                  AND gl_date <= TO_DATE ( :p_date)
                                  AND application_type = 'CM'
                                  AND customer_trx_id = aps.customer_trx_id),
                          0))
                    - (NVL (
                          (SELECT SUM (acctd_amount_applied_from)
                             FROM apps.ar_receivable_applications_all
                            WHERE     1 = 1
                                  --                                             apply_date                 < =to_date(:p_date)
                                  AND gl_date <= TO_DATE ( :p_date)
                                  AND application_type = 'CM'
                                  AND applied_customer_trx_id =
                                         aps.customer_trx_id),
                          0))                               --             ,0)
                             ) <> 0) a

==========================

AR Revenue

============

SELECT gc.segment1 bu,
         gc.concatenated_segments,
         --gc.segment3  Account,
         rct.trx_number,
         ac.customer_name,
         ac.customer_number,
         pl.lease_num,
         plu.location_code,
         space_type plot_type,
         (SELECT ptta.name
            FROM apps.pn_term_templates_all ptta
           WHERE ptta.term_template_id(+) = ppt.term_template_id)
            payment_term_type,
         gc.segment2,
         gc.segment3 gl_account,
         TO_CHAR ( (rctg.gl_date)) gl_date,
         SUM (rctg.amount) gl_amount
    FROM apps.ra_customer_trx_all rct,
         apps.ra_cust_trx_line_gl_dist_all rctg,
         apps.gl_code_combinations_kfv gc,
         apps.ra_customer_trx_lines_all rctl,
         apps.pn_leases_all pl,
         apps.pn_payment_items_all ppi,
         apps.pn_payment_terms_all ppt,
         apps.xxfin_pn_locations_pub_v plu,
         apps.ar_customers ac
   WHERE     rct.batch_source_id = 24
         AND ac.customer_id = pl.customer_id
         AND ppt.location_id = plu.location_id
         AND rct.customer_trx_id = rctg.customer_trx_id
         AND rctl.customer_trx_id = rct.customer_trx_id
         AND rctl.customer_trx_line_id = rctg.customer_trx_line_id
         AND gc.code_combination_id = rctg.code_combination_id
         AND NVL (rctg.account_set_flag, 'N') <> 'Y'
         AND rctg.account_class = 'REV'
         AND rctl.interface_line_attribute10 = pl.lease_num
         AND ppt.lease_id = pl.lease_id
         AND rct.org_id = 2965
         AND gc.segment3 = '612530'
         AND rctg.org_id = rct.org_id
         --and NVL(fnd_profile.value('XXFIN_PN_SALE_ACCESS'),'N') = decode(tenure_code,'L','N','Y')
         AND ppi.payment_term_id = ppt.payment_term_id
         AND rctl.interface_line_attribute1 =
                pl.lease_num || ' - ' || ppi.payment_item_id
         --and gc.segment3 = nvl(:p_seg3,gc.segment3)
         AND rctg.gl_date >= NVL ( :p_from_date, rctg.gl_date)
         AND rctg.gl_date <= NVL ( :p_to_date, rctg.gl_date)
GROUP BY gc.segment1,
         rct.trx_number,
         ac.customer_name,
         gc.concatenated_segments,
         ac.customer_number,
         pl.lease_num,
         plu.location_code,
         space_type,
         gc.segment2,
         gc.segment3,
         rctg.gl_date,
         ppt.term_template_id
UNION ALL
  SELECT gc.segment1 bu,
         gc.concatenated_segments,
         --gc.segment3  Account,
         rct.trx_number,
         ac.customer_name,
         ac.customer_number,
         NULL lease_num,
         NULL location_code,
         NULL plot_type,
         NULL payment_term_type,
         gc.segment2,
         gc.segment3 gl_account,
         TO_CHAR ( (rctg.gl_date)) gl_date,
         SUM (rctg.amount) gl_amount
    FROM apps.ra_customer_trx_all rct,
         apps.ra_cust_trx_line_gl_dist_all rctg,
         apps.gl_code_combinations_kfv gc,
         apps.ra_customer_trx_lines_all rctl,
         --         apps.pn_leases_all pl,
         --         apps.pn_payment_items_all ppi,
         --         apps.pn_payment_terms_all ppt,
         --         apps.xxfin_pn_locations_pub_v plu,
         apps.ar_customers ac
   WHERE    1=1
--   AND  rct.batch_source_id = 24
         AND ac.customer_id = rct.bill_to_customer_id
         --         AND ppt.location_id = plu.location_id
         AND rct.customer_trx_id = rctg.customer_trx_id
         AND rctl.customer_trx_id = rct.customer_trx_id
         AND rctl.customer_trx_line_id = rctg.customer_trx_line_id
         AND gc.code_combination_id = rctg.code_combination_id
         AND NVL (rctg.account_set_flag, 'N') <> 'Y'
         AND rctg.account_class = 'REV'
         AND rctl.interface_line_attribute10 IS NULL
         --         AND ppt.lease_id = pl.lease_id
         AND rct.org_id = 2965
         AND gc.segment3 = '612530'
         AND rctg.org_id = rct.org_id
         --and NVL(fnd_profile.value('XXFIN_PN_SALE_ACCESS'),'N') = decode(tenure_code,'L','N','Y')
         --         AND ppi.payment_term_id = ppt.payment_term_id
         AND rctl.interface_line_attribute1 IS NULL
         --and gc.segment3 = nvl(:p_seg3,gc.segment3)
         AND rctg.gl_date >= NVL ( :p_from_date, rctg.gl_date)
         AND rctg.gl_date <= NVL ( :p_to_date, rctg.gl_date)
GROUP BY gc.segment1,
         rct.trx_number,
         ac.customer_name,
         gc.concatenated_segments,
         ac.customer_number,
         --         pl.lease_num,
         --         plu.location_code,
         --         space_type,
         gc.segment2,
         gc.segment3,
         rctg.gl_date
--         ppt.term_template_id



No comments:

Post a Comment