Oracle E-Business Suite: 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

Oracle E-Business Suite

Sunday, June 9, 2019

GL Breakup

No comments:

Post a Comment

Followers

Traffic