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 |
|
