ORACLE EBS PL/SQL

Monday, 25 May 2015

BANK BOOK Reconcilation Report Query.

SELECT gjh.default_effective_date,
GJH.DOC_SEQUENCE_ID,
TO_CHAR(gjh.default_effective_date, 'DD-Mon-YYYY') gl_date,
(SELECT NAME
FROM fnd_document_sequences A
WHERE SUBSTR(INITIAL_VALUE, 1, 3) =
SUBSTR(gjh.doc_sequence_value, 1, 3)
AND A.DOC_SEQUENCE_ID = GJH.DOC_SEQUENCE_ID) Sequence_Name,
gjh.doc_sequence_value voucher_number,
null FLOOR_FUNDING_BANK, --CODE ADDED BY SWATI
ai.invoice_num txn_num,
null cash_receipt_id,
pv.vendor_name party_name,
--aid.description narration,
ai.description narration, --CODE ADDED BY SWATI
decode(sign(xdl.unrounded_accounted_dr), 1, 'D', 'C') d_c,
xal.CURRENCY_CODE,
SUM(DECODE(XAL.CURRENCY_CODE,
'INR',
NULL,
(NVL(XAL.ENTERED_DR, 0) - NVL(XAL.ENTERED_CR, 0)) * -1)) FCY
,
AI.EXCHANGE_RATE EX_RATE,
SUM(DECODE(SIGN(xdl.unrounded_accounted_dr),
1,
xdl.unrounded_accounted_dr)) amount_dr,
SUM(DECODE(SIGN(xdl.unrounded_accounted_dr),
-1,
ABS(xdl.unrounded_accounted_dr),
xdl.unrounded_accounted_cr)) amount_cr,
gjh.je_source SOURCE,
gjc.user_je_category_name CATEGORY,
null status,
null reconcile_status,
fu.user_name user_name,
NVL(xdl.unrounded_accounted_dr, (-1 * xdl.unrounded_accounted_cr))
running
FROM gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl,
gl_je_categories gjc,
gl_import_references gir,
xla_ae_lines xal,
xla_ae_headers xah,
xla_distribution_links xdl,
ap_invoices_all ai,
--AP_INVOICE_LINES_ALL AIL,
ap_batches_all ab,
ap_invoice_distributions_all aid,
po_vendors pv,
po_vendor_sites_all pvs,
gl_code_combinations_kfv gcc,
fnd_user fu,
ap_invoice_lines_all ail
WHERE gjb.je_batch_id = gjh.je_batch_id
AND gjl.je_header_id = gjh.je_header_id
AND gir.je_batch_id = gjb.je_batch_id
AND gir.je_header_id = gjh.je_header_id
AND gir.je_line_num = gjl.je_line_num
AND gjc.je_category_name = gjh.je_category
AND xal.gl_sl_link_id = gir.gl_sl_link_id
AND xah.ae_header_id = xal.ae_header_id
AND xdl.ae_header_id = xah.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND ab.batch_id = ai.batch_id
AND aid.invoice_distribution_id = xdl.source_distribution_id_num_1
AND ai.invoice_id = aid.invoice_id
AND pv.vendor_id = ai.vendor_id
AND pvs.vendor_site_id = ai.vendor_site_id
AND gcc.code_combination_id = gjl.code_combination_id
AND fu.user_id = ai.created_by
AND gcc.segment1 = :P_ORG_ID
AND (xal.accounted_dr <> 0 or xal.accounted_cr <> 0)
--CODE ADDED BY SWATI
AND gcc.segment3 IN
(SELECT DISTINCT gcc.segment3
FROM gl_code_combinations gcc,
fnd_flex_values_vl ffv,
fnd_flex_value_sets ffvs,
CE_BANK_ACCOUNTS CBA
WHERE gcc.segment3 = ffv.flex_value
AND ffv.flex_value_set_id = ffvs.flex_value_set_id
AND flex_value_set_name = 'HMI_Account'
and ffv.FLEX_VALUE IN(:p_bank_ac)
-- AND ffv.ATTRIBUTE1='BANK BOOK'
/*AND (ffv.description LIKE
substr(BANK_ACCOUNT_NAME,
1,
instr(BANK_ACCOUNT_NAME, 'CURRENT') - 2) || '%BANK%')*/
AND CBA.BANK_ACCOUNT_NAME = :BANK_ACCT_NAME)
--AND CBA.BANK_ACCOUNT_NUM=:BANK_ACCT_NUM
AND NVL(pv.vendor_name, 1) = NVL(:P_PARTY_NAME, NVL(PV.VENDOR_NAME, 1))
AND xal.CURRENCY_CODE = NVL(:P_CURRENCY, xal.CURRENCY_CODE)
AND TRUNC(gjh.default_effective_date) >=
NVL((SELECT TRUNC(gp.start_date)
FROM gl_periods gp
WHERE gp.period_set_name LIKE 'HMI_Calendar'
AND gp.period_name = :p_period_fr),
TRUNC(gjh.default_effective_date))
AND TRUNC(gjh.default_effective_date) <=
NVL((SELECT TRUNC(gp.end_date)
FROM gl_periods gp
WHERE gp.period_set_name LIKE 'HMI_Calendar'
AND gp.period_name = :p_period_to),
TRUNC(gjh.default_effective_date))
AND gjc.user_je_category_name IN ('Purchase Invoices')
AND gjh.je_source = 'Payables'
AND gjh.status = 'P'
AND ail.invoice_id = ai.invoice_id
AND ail.invoice_id = aid.invoice_id
AND ail.line_number = aid.invoice_line_number
AND GJL.LEDGER_ID='2022' --CODE ADDED BY SWATI
GROUP BY gjh.default_effective_date,
GJH.DOC_SEQUENCE_ID,
TO_CHAR(gjh.default_effective_date, 'DD-Mon-YYYY'),
decode(gjh.je_source, 'Payables', 'BPV', 'BRV'),
gjh.doc_sequence_value,
ai.invoice_num,
pv.vendor_name,
--aid.description narration,
ai.description, --CODE ADDED BY SWATI
decode(sign(xdl.unrounded_accounted_dr), 1, 'D', 'C'),
xal.CURRENCY_CODE,
AI.EXCHANGE_RATE,
gjh.je_source,
gjc.user_je_category_name,
fu.user_name,
NVL(xdl.unrounded_accounted_dr, (-1 * xdl.unrounded_accounted_cr))
UNION ALL
SELECT gjh.default_effective_date,
GJH.DOC_SEQUENCE_ID,
TO_CHAR(gjh.default_effective_date, 'DD-Mon-YYYY') gl_date,
(SELECT NAME
FROM fnd_document_sequences A
WHERE SUBSTR(INITIAL_VALUE, 1, 3) =
SUBSTR(gjh.doc_sequence_value, 1, 3)
AND A.DOC_SEQUENCE_ID = GJH.DOC_SEQUENCE_ID) Sequence_Name,
gjh.doc_sequence_value voucher_number,
null FLOOR_FUNDING_BANK, --CODE ADDED BY SWATI
TO_CHAR(ac.check_number) txn_num,
ac.check_id cash_receipt_id,
pv.vendor_name party_name,
--GJL.DESCRIPTION narration,
AC.DESCRIPTION narration, --CODE ADDED BY SWATI
decode(sign(xal.accounted_dr), 1, 'D', 'C') d_c,
xal.CURRENCY_CODE,
SUM(DECODE(XAL.CURRENCY_CODE,
'INR',
NULL,
(NVL(XAL.ENTERED_DR, 0) - NVL(XAL.ENTERED_CR, 0)) * -1)) FCY
,
AC.EXCHANGE_RATE EX_RATE,
DECODE(SIGN(xal.accounted_dr), 1, xal.accounted_dr) amount_dr,
DECODE(SIGN(xal.accounted_dr),
-1,
ABS(xal.accounted_dr),
xal.accounted_cr) amount_cr,
gjh.je_source SOURCE,
gjc.user_je_category_name CATEGORY,
(select status_lookup_code
from ap_checks_v
where check_id = TO_CHAR(ac.check_id)) status,
(SELECT CSL.STATUS
from CE_STATEMENT_HEADERS CSH,
CE_BANK_ACCOUNTS CBA,
CE_STATEMENT_LINES CSL
WHERE CSH.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID
AND CSH.STATEMENT_HEADER_ID = CSL.STATEMENT_HEADER_ID
AND CBA.BANK_ACCOUNT_NAME = :BANK_ACCT_NAME
AND CSL.bank_trx_number = TO_CHAR(ac.check_number))
reconcile_status,
fu.user_name user_name,
NVL(xal.accounted_dr, (-1 * xal.accounted_cr)) running
FROM gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl,
gl_je_categories gjc,
gl_import_references gir,
xla_ae_lines xal,
xla_ae_headers xah,
xla_transaction_entities xte,
ap_checks_all ac,
po_vendors pv,
po_vendor_sites_all pvs,
gl_code_combinations_kfv gcc,
fnd_user fu
WHERE gjb.je_batch_id = gjh.je_batch_id
AND gjl.je_header_id = gjh.je_header_id
AND gir.je_batch_id = gjb.je_batch_id
AND gir.je_header_id = gjh.je_header_id
AND gir.je_line_num = gjl.je_line_num
AND gjc.je_category_name = gjh.je_category
AND xal.gl_sl_link_id = gir.gl_sl_link_id
AND xah.ae_header_id = xal.ae_header_id
AND xte.entity_id = xah.entity_id
AND ac.check_id = xte.source_id_int_1
AND pv.vendor_id(+) = ac.vendor_id
AND pvs.vendor_site_id(+) = ac.vendor_site_id
AND gcc.code_combination_id = gjl.code_combination_id
AND fu.user_id = ac.created_by
AND gcc.segment1 = :P_ORG_ID
AND (xal.accounted_dr <> 0 or xal.accounted_cr <> 0)
--CODE ADDED BY SWATI
AND gcc.segment3 /*=:BANK_ACCT_NAME*/
IN
(SELECT DISTINCT gcc.segment3
FROM gl_code_combinations gcc,
fnd_flex_values_vl ffv,
fnd_flex_value_sets ffvs,
CE_BANK_ACCOUNTS CBA
WHERE gcc.segment3 = ffv.flex_value
AND ffv.flex_value_set_id = ffvs.flex_value_set_id
AND flex_value_set_name = 'HMI_Account'
and ffv.FLEX_VALUE IN(:p_bank_ac)
-- AND ffv.ATTRIBUTE1='BANK BOOK'
/*AND (ffv.description LIKE
substr(BANK_ACCOUNT_NAME,
1,
instr(BANK_ACCOUNT_NAME, 'CURRENT') - 2) || '%BANK%')*/
AND CBA.BANK_ACCOUNT_NAME = :BANK_ACCT_NAME)
--AND CBA.BANK_ACCOUNT_NUM=:BANK_ACCT_NUM
AND NVL(pv.vendor_name, 1) = NVL(:P_PARTY_NAME, NVL(pv.vendor_name, 1))
AND xal.CURRENCY_CODE = NVL(:P_CURRENCY, xal.CURRENCY_CODE)
AND TRUNC(gjh.default_effective_date) >=
NVL((SELECT TRUNC(gp.start_date)
FROM gl_periods gp
WHERE gp.period_set_name LIKE 'HMI_Calendar'
AND gp.period_name = :p_period_fr),
TRUNC(gjh.default_effective_date))
AND TRUNC(gjh.default_effective_date) <=
NVL((SELECT TRUNC(gp.end_date)
FROM gl_periods gp
WHERE gp.period_set_name LIKE 'HMI_Calendar'
AND gp.period_name = :p_period_to),
TRUNC(gjh.default_effective_date))
AND gjc.user_je_category_name IN ('Reconciled Payments', 'Payments')
AND gjh.je_source = 'Payables'
AND gjh.status = 'P'
AND GJL.LEDGER_ID='2022' --CODE ADDED BY SWATI
group by gjh.default_effective_date,
GJH.DOC_SEQUENCE_ID,
TO_CHAR(gjh.default_effective_date, 'DD-Mon-YYYY'),
decode(gjh.je_source, 'Payables', 'BPV', 'BRV'),
gjh.doc_sequence_value,
TO_CHAR(ac.check_number),
pv.vendor_name,
--GJL.DESCRIPTION narration,
AC.DESCRIPTION, --CODE ADDED BY SWATI
decode(sign(xal.accounted_dr), 1, 'D', 'C'),
xal.CURRENCY_CODE,
AC.EXCHANGE_RATE,
DECODE(SIGN(xal.accounted_dr), 1, xal.accounted_dr),
DECODE(SIGN(xal.accounted_dr),
-1,
ABS(xal.accounted_dr),
xal.accounted_cr),
gjh.je_source,
gjc.user_je_category_name,
fu.user_name,
NVL(xal.accounted_dr, (-1 * xal.accounted_cr)),
ac.check_number,
ac.check_id
UNION ALL
SELECT gjh.default_effective_date,
GJH.DOC_SEQUENCE_ID,
TO_CHAR(gjh.default_effective_date, 'DD-Mon-YYYY') gl_date,
(SELECT NAME
FROM fnd_document_sequences A
WHERE SUBSTR(INITIAL_VALUE, 1, 3) =
SUBSTR(gjh.doc_sequence_value, 1, 3)
AND A.DOC_SEQUENCE_ID = GJH.DOC_SEQUENCE_ID) Sequence_Name,
gjh.doc_sequence_value voucher_number,
(select hcsua.attribute3 from hz_cust_site_uses_all hcsua
where hcsua.site_use_code = 'BILL_TO' and hcsua.site_use_id=hp.
site_use_id) FLOOR_FUNDING_BANK, --CODE ADDED BY SWATI
rct.trx_number txn_num,
null cash_receipt_id,
hp.party_name party_name,
GJL.DESCRIPTION narration,
decode(sign(xal.accounted_dr), 1, 'D', 'C') d_c,
xal.CURRENCY_CODE,
SUM(DECODE(XAL.CURRENCY_CODE,
'INR',
NULL,
(NVL(XAL.ENTERED_DR, 0) - NVL(XAL.ENTERED_CR, 0)) * -1)) FCY
,
rct.EXCHANGE_RATE EX_RATE,
DECODE(SIGN(xal.accounted_dr), 1, xal.accounted_dr) amount_dr,
DECODE(SIGN(xal.accounted_dr),
-1,
ABS(xal.accounted_dr),
xal.accounted_cr) amount_cr,
gjh.je_source SOURCE,
gjc.user_je_category_name CATEGORY,
null status,
null reconcile_status,
fu.user_name user_name,
NVL(xal.accounted_dr, (-1 * xal.accounted_cr)) running
FROM gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl,
gl_je_categories gjc,
xla_ae_lines xal,
xla_ae_headers xah,
xla_transaction_entities xte,
gl_import_references gir,
ra_customer_trx_all rct,
(SELECT hca.cust_account_id cust_account_id,
hcsu.site_use_code site_use_code,
hcsu.LOCATION LOCATION,
hcsu.site_use_id site_use_id,
hp.party_name
FROM hz_parties hp,
hz_cust_accounts_all hca,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu
WHERE hcas.cust_account_id = hca.cust_account_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hp.party_id = hca.party_id
AND hcsu.site_use_code = 'BILL_TO') hp,
gl_code_combinations_kfv gcc,
fnd_user fu
WHERE gjb.je_batch_id = gjh.je_batch_id
AND gjl.je_header_id = gjh.je_header_id
AND gir.je_batch_id = gjb.je_batch_id
AND gir.je_header_id = gjh.je_header_id
AND gir.je_line_num = gjl.je_line_num
AND gjc.je_category_name = gjh.je_category
AND xal.gl_sl_link_id = gir.gl_sl_link_id
AND xah.ae_header_id = xal.ae_header_id
AND xte.entity_id = xah.entity_id
AND rct.customer_trx_id = xte.source_id_int_1
AND hp.cust_account_id = rct.BILL_TO_CUSTOMER_ID
--sold_to_customer_id (BY IMRAN FOR DD068B/05.05.11 -- TRX NUMBER)
AND hp.site_use_id = rct.bill_to_site_use_id
AND gcc.code_combination_id = gjl.code_combination_id
AND fu.user_id = rct.created_by
AND gcc.segment1 = :P_ORG_ID
AND (xal.accounted_dr <> 0 or xal.accounted_cr <> 0)
--CODE ADDED BY SWATI
AND gcc.segment3 IN /* =:BANK_ACCT_NAME*/
(SELECT DISTINCT gcc.segment3
FROM gl_code_combinations gcc,
fnd_flex_values_vl ffv,
fnd_flex_value_sets ffvs,
CE_BANK_ACCOUNTS CBA
WHERE gcc.segment3 = ffv.flex_value
AND ffv.flex_value_set_id = ffvs.flex_value_set_id
and ffv.FLEX_VALUE IN(:p_bank_ac)
--AND ffv.ATTRIBUTE1='BANK BOOK'
AND flex_value_set_name = 'HMI_Account'
/*AND (ffv.description LIKE
substr(BANK_ACCOUNT_NAME,
1,
instr(BANK_ACCOUNT_NAME, 'CURRENT') - 2) || '%BANK%')*/
AND CBA.BANK_ACCOUNT_NAME = :BANK_ACCT_NAME)
--AND CBA.BANK_ACCOUNT_NUM=:BANK_ACCT_NUM
AND NVL(hp.party_name, 1) = NVL(:P_PARTY_NAME, NVL(hp.party_name, 1))
AND xal.CURRENCY_CODE = NVL(:P_CURRENCY, xal.CURRENCY_CODE)
AND TRUNC(gjh.default_effective_date) >=
NVL((SELECT TRUNC(gp.start_date)
FROM gl_periods gp
WHERE gp.period_set_name LIKE 'HMI_Calendar'
AND gp.period_name = :p_period_fr),
TRUNC(gjh.default_effective_date))
AND TRUNC(gjh.default_effective_date) <=
NVL((SELECT TRUNC(gp.end_date)
FROM gl_periods gp
WHERE gp.period_set_name LIKE 'HMI_Calendar'
AND gp.period_name = :p_period_to),
TRUNC(gjh.default_effective_date))
AND gjc.user_je_category_name IN ('Sales Invoices', 'Credit Memos')
AND gjh.je_source = 'Receivables'
AND gjh.status = 'P'
AND GJL.LEDGER_ID='2022' --CODE ADDED BY SWATI
group by gjh.default_effective_date,
GJH.DOC_SEQUENCE_ID,
TO_CHAR(gjh.default_effective_date, 'DD-Mon-YYYY'),
decode(gjh.je_source, 'Payables', 'BPV', 'BRV'),
gjh.doc_sequence_value,
hp.site_use_id, --CODE ADDED BY SWATI
rct.TRX_NUMBER,
hp.party_name,
GJL.DESCRIPTION,
decode(sign(xal.accounted_dr), 1, 'D', 'C'),
xal.CURRENCY_CODE,
rct.EXCHANGE_RATE,
DECODE(SIGN(xal.accounted_dr), 1, xal.accounted_dr),
DECODE(SIGN(xal.accounted_dr),
-1,
ABS(xal.accounted_dr),
xal.accounted_cr),
gjh.je_source,
gjc.user_je_category_name,
fu.user_name,
NVL(xal.accounted_dr, (-1 * xal.accounted_cr))
UNION ALL
SELECT gjh.default_effective_date,
GJH.DOC_SEQUENCE_ID,
TO_CHAR(gjh.default_effective_date, 'DD-Mon-YYYY') gl_date,
(SELECT NAME
FROM fnd_document_sequences A
WHERE SUBSTR(INITIAL_VALUE, 1, 3) =
SUBSTR(gjh.doc_sequence_value, 1, 3)
AND A.DOC_SEQUENCE_ID = GJH.DOC_SEQUENCE_ID) Sequence_Name,
gjh.doc_sequence_value voucher_number,
(select hcsua.attribute3 from hz_cust_site_uses_all hcsua
where hcsua.site_use_code = 'BILL_TO' and hcsua.site_use_id=hp.
site_use_id) FLOOR_FUNDING_BANK, --CODE ADDED BY SWATI
acr.receipt_number txn_num,
acr.cash_receipt_id,
hp.party_name party_name,
--GJL.DESCRIPTION narration,
ACR.COMMENTS narration, --CODE ADDED BY SWATI
decode(sign(xal.accounted_dr), 1, 'D', 'C') d_c,
xal.CURRENCY_CODE,
SUM(DECODE(XAL.CURRENCY_CODE,
'INR',
NULL,
(NVL(XAL.ENTERED_DR, 0) - NVL(XAL.ENTERED_CR, 0)) * -1)) FCY
,
acr.EXCHANGE_RATE EX_RATE,
DECODE(SIGN(xal.accounted_dr), 1, xal.accounted_dr) amount_dr,
DECODE(SIGN(xal.accounted_dr),
-1,
ABS(xal.accounted_dr),
xal.accounted_cr) amount_cr,
gjh.je_source SOURCE,
gjc.user_je_category_name CATEGORY,
(select receipt_status_dsp
from ar_cash_receipts_v
where cash_receipt_id = acr.cash_receipt_id) status,
(SELECT decode(status, 'CLEARED', 'RECONCILED', 'UNRECONCILED')
from AR_CASH_RECEIPT_HISTORY
WHERE CASH_RECEIPT_ID = acr.cash_receipt_id
and current_record_flag = 'Y') reconcile_status,
fu.user_name user_name,
NVL(xal.accounted_dr, (-1 * xal.accounted_cr)) running
FROM gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl,
gl_je_categories gjc,
gl_import_references gir,
xla_ae_lines xal,
xla_ae_headers xah,
xla_transaction_entities xte,
ar_cash_receipts_all acr,
(SELECT hca.cust_account_id cust_account_id,
hcsu.site_use_code site_use_code,
hcsu.LOCATION LOCATION,
hcsu.site_use_id site_use_id,
hp.party_name
FROM hz_parties hp,
hz_cust_accounts_all hca,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu
WHERE hcas.cust_account_id = hca.cust_account_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hp.party_id = hca.party_id
AND hcsu.site_use_code = 'BILL_TO') hp,
gl_code_combinations_kfv gcc,
fnd_user fu
WHERE gjb.je_batch_id = gjh.je_batch_id
AND gjl.je_header_id = gjh.je_header_id
AND gir.je_batch_id = gjb.je_batch_id
AND gir.je_header_id = gjh.je_header_id
AND gir.je_line_num = gjl.je_line_num
AND gjc.je_category_name = gjh.je_category
AND xal.gl_sl_link_id = gir.gl_sl_link_id
AND xah.ae_header_id = xal.ae_header_id
AND xte.entity_id = xah.entity_id
AND acr.cash_receipt_id = xte.source_id_int_1
AND hp.cust_account_id(+) = acr.pay_from_customer
AND hp.site_use_id(+) = acr.customer_site_use_id
AND gcc.code_combination_id = gjl.code_combination_id
AND fu.user_id = acr.created_by
AND gcc.segment1 = :P_ORG_ID
AND (xal.accounted_dr <> 0 or xal.accounted_cr <> 0)
--CODE ADDED BY SWATI
AND gcc.segment3 IN /*=:BANK_ACCT_NAME*/
(SELECT DISTINCT gcc.segment3
FROM gl_code_combinations gcc,
fnd_flex_values_vl ffv,
fnd_flex_value_sets ffvs,
CE_BANK_ACCOUNTS CBA
WHERE gcc.segment3 = ffv.flex_value
AND ffv.flex_value_set_id = ffvs.flex_value_set_id
AND flex_value_set_name = 'HMI_Account'
and ffv.FLEX_VALUE IN(:p_bank_ac)
--AND ffv.ATTRIBUTE1='BANK BOOK'
/*AND (ffv.description LIKE
substr(BANK_ACCOUNT_NAME,
1,
instr(BANK_ACCOUNT_NAME, 'CURRENT') - 2) || '%BANK%')*/
AND CBA.BANK_ACCOUNT_NAME = :BANK_ACCT_NAME)
--AND CBA.BANK_ACCOUNT_NUM=:BANK_ACCT_NUM)
AND NVL(hp.party_name, 1) = NVL(:P_PARTY_NAME, NVL(hp.party_name, 1))
AND xal.CURRENCY_CODE = NVL(:P_CURRENCY, xal.CURRENCY_CODE)
AND TRUNC(gjh.default_effective_date) >=
NVL((SELECT TRUNC(gp.start_date)
FROM gl_periods gp
WHERE gp.period_set_name LIKE 'HMI_Calendar'
AND gp.period_name = :p_period_fr),
TRUNC(gjh.default_effective_date))
AND TRUNC(gjh.default_effective_date) <=
NVL((SELECT TRUNC(gp.end_date)
FROM gl_periods gp
WHERE gp.period_set_name LIKE 'HMI_Calendar'
AND gp.period_name = :p_period_to),
TRUNC(gjh.default_effective_date))
AND gjc.user_je_category_name = 'Receipts'
AND gjh.je_source = 'Receivables'
AND gjh.status = 'P'
AND GJL.LEDGER_ID='2022' --CODE ADDED BY SWATI
group by gjh.default_effective_date,
GJH.DOC_SEQUENCE_ID,
TO_CHAR(gjh.default_effective_date, 'DD-Mon-YYYY'),
decode(gjh.je_source, 'Payables', 'BPV', 'BRV'),
gjh.doc_sequence_value,
hp.site_use_id, --CODE ADDED BY SWATI
acr.RECEIPT_NUMBER,
hp.party_name,
--GJL.DESCRIPTION narration,
ACR.COMMENTS, --CODE ADDED BY SWATI
decode(sign(xal.accounted_dr), 1, 'D', 'C'),
xal.CURRENCY_CODE,
acr.EXCHANGE_RATE,
DECODE(SIGN(xal.accounted_dr), 1, xal.accounted_dr),
DECODE(SIGN(xal.accounted_dr),
-1,
ABS(xal.accounted_dr),
xal.accounted_cr),
gjh.je_source,
gjc.user_je_category_name,
GJH.DOC_SEQUENCE_ID,
fu.user_name,
NVL(xal.accounted_dr, (-1 * xal.accounted_cr)),
acr.cash_receipt_id
UNION ALL
SELECT gjh.default_effective_date,
GJH.DOC_SEQUENCE_ID,
TO_CHAR(gjh.default_effective_date, 'DD-Mon-YYYY') gl_date,
(SELECT NAME
FROM fnd_document_sequences A
WHERE SUBSTR(INITIAL_VALUE, 1, 3) =
SUBSTR(gjh.doc_sequence_value, 1, 3)
AND A.DOC_SEQUENCE_ID = GJH.DOC_SEQUENCE_ID) Sequence_Name,
gjh.doc_sequence_value voucher_number,
null FLOOR_FUNDING_BANK, --CODE ADDED BY SWATI
null txn_num,
null cash_receipt_id,
null party_name,
GJL.DESCRIPTION narration,
decode(sign(gjl.accounted_dr), 1, 'D', 'C') d_c,
GJH.CURRENCY_CODE,
null FCY,
null EX_RATE,
DECODE(SIGN(gjl.accounted_dr), 1, gjl.accounted_dr) amount_dr,
DECODE(SIGN(gjl.accounted_dr),
-1,
ABS(gjl.accounted_dr),
gjl.accounted_cr) amount_cr,
gjh.je_source SOURCE,
gjc.user_je_category_name CATEGORY,
null status,
null reconcile_status,
fu.user_name user_name,
NVL(gjl.accounted_dr, (-1 * gjl.accounted_cr)) running
FROM gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl,
gl_je_categories gjc,
gl_code_combinations_kfv gcc,
fnd_user fu
WHERE gjb.je_batch_id = gjh.je_batch_id
AND gjl.je_header_id = gjh.je_header_id
AND gjc.je_category_name = gjh.je_category
AND gcc.code_combination_id = gjl.code_combination_id
AND fu.user_id = gjh.created_by
AND gcc.segment1 = :P_ORG_ID
AND GJH.CURRENCY_CODE = NVL(:P_CURRENCY, GJH.CURRENCY_CODE)
&P_WHERE
AND gcc.segment3 IN /*=:BANK_ACCT_NAME*/
(SELECT DISTINCT gcc.segment3
FROM gl_code_combinations gcc,
fnd_flex_values_vl ffv,
fnd_flex_value_sets ffvs,
CE_BANK_ACCOUNTS CBA
WHERE gcc.segment3 = ffv.flex_value
AND ffv.flex_value_set_id = ffvs.flex_value_set_id
AND flex_value_set_name = 'HMI_Account'
and ffv.FLEX_VALUE IN(:p_bank_ac)
--AND ffv.ATTRIBUTE1='BANK BOOK'
/*AND (ffv.description LIKE
substr(BANK_ACCOUNT_NAME,
1,
instr(BANK_ACCOUNT_NAME, 'CURRENT') - 2) || '%BANK%')*/
AND CBA.BANK_ACCOUNT_NAME = :BANK_ACCT_NAME)
--AND CBA.BANK_ACCOUNT_NUM=:BANK_ACCT_NUM)
AND TRUNC(gjh.default_effective_date) >=
NVL((SELECT TRUNC(gp.start_date)
FROM gl_periods gp
WHERE gp.period_set_name LIKE 'HMI_Calendar'
AND gp.period_name = :p_period_fr),
TRUNC(gjh.default_effective_date))
AND TRUNC(gjh.default_effective_date) <=
NVL((SELECT TRUNC(gp.end_date)
FROM gl_periods gp
WHERE gp.period_set_name LIKE 'HMI_Calendar'
AND gp.period_name = :p_period_to),
TRUNC(gjh.default_effective_date))
AND gjh.status = 'P'
AND gjc.user_je_category_name NOT IN
('Purchase Invoices', 'Sales Invoices', 'Receipts', 'Payments',
'Reconciled Payments', 'Credit Memos')
AND GJL.LEDGER_ID='2022' --CODE ADDED BY SWATI
ORDER BY 1

This condition is used in the RDF for getting the data correctly.. and accordingly the last query will run.

IF :P_PARTY_NAME IS NOT NULL THEN
:P_WHERE:='AND 1=2';
else
:p_where:='AND 1=1';
end if;

The openning balance Query of the back book

SELECT decode('PTD',
'PTD',
SUM(DECODE('T',
'T',
NVL(BEGIN_BALANCE_DR, 0) - NVL(BEGIN_BALANCE_CR, 0),
'S',
NVL(BEGIN_BALANCE_DR, 0) - NVL(BEGIN_BALANCE_CR, 0),
'E',
DECODE(BAL.TRANSLATED_FLAG,
'R',
NVL(BEGIN_BALANCE_DR, 0) -
NVL(BEGIN_BALANCE_CR, 0),
NVL(BEGIN_BALANCE_DR_BEQ, 0) -
NVL(BEGIN_BALANCE_CR_BEQ, 0))))
) x
INTO OPENING_BAL
FROM GL_BALANCES BAL,
GL_CODE_COMBINATIONS CC,
GL_LEDGERS L,
GL_LEDGER_SET_ASSIGNMENTS ASG,
GL_LEDGER_RELATIONSHIPS LR
WHERE BAL.ACTUAL_FLAG = 'A'
AND BAL.CURRENCY_CODE = 'INR'
AND BAL.PERIOD_NAME =:P_PERIOD_FR
AND BAL.CODE_COMBINATION_ID = CC.CODE_COMBINATION_ID
AND CC.CHART_OF_ACCOUNTS_ID = 50348
AND CC.TEMPLATE_ID IS NULL
AND CC.SUMMARY_FLAG = 'N'
AND L.LEDGER_ID = 2022
AND ASG.LEDGER_SET_ID(+) = L.LEDGER_ID
AND LR.TARGET_LEDGER_ID = NVL(ASG.LEDGER_ID, L.LEDGER_ID)
AND LR.SOURCE_LEDGER_ID = NVL(ASG.LEDGER_ID, L.LEDGER_ID)
AND LR.TARGET_CURRENCY_CODE = 'INR'
AND LR.SOURCE_LEDGER_ID = BAL.LEDGER_ID
AND LR.TARGET_LEDGER_ID = BAL.LEDGER_ID
and cc.segment1 =:p_org_id
and cc.segment3 in (
SELECT DISTINCT gcc.segment3
FROM gl_code_combinations gcc,
fnd_flex_values_vl ffv,
fnd_flex_value_sets ffvs,
CE_BANK_ACCOUNTS CBA
WHERE gcc.segment3 = ffv.flex_value
AND ffv.flex_value_set_id = ffvs.flex_value_set_id
AND flex_value_set_name = 'HMI_Account'
--AND ffv.ATTRIBUTE1='BANK BOOK'
and ffv.FLEX_VALUE=:P_BANK_AC
--AND (ffv.description LIKE substr(BANK_ACCOUNT_NAME,1,instr(BANK_ACCOUNT_NAME,'CURRENT')-2)||'%BANK%')
--AND (ffv.description NOT LIKE substr(BANK_ACCOUNT_NAME,1,instr(BANK_ACCOUNT_NAME,'CURRENT')-2)||'%BANK%CLEARING%')
AND CBA.BANK_ACCOUNT_NAME=:BANK_ACCT_NAME
-- AND CBA.BANK_ACCOUNT_NUM=:BANK_ACCT_NUM
);
IF SIGN(OPENING_BAL)=1 THEN
:CP_OPENING_BANK_DR:=OPENING_BAL;
:CP_OPENING_BANK_CR:=0;
:CP_DR_CR:='D';
ELSE
:CP_OPENING_BANK_DR:=0;
:CP_OPENING_BANK_CR:=ABS(OPENING_BAL);
:CP_DR_CR:='C';
END IF;

This is the Query used for the closing balance of the bank book report.

SELECT decode('PTD',
'PTD',
SUM(DECODE('T',
'T',
NVL(BEGIN_BALANCE_DR, 0) - NVL(BEGIN_BALANCE_CR, 0),
'S',
NVL(BEGIN_BALANCE_DR, 0) - NVL(BEGIN_BALANCE_CR, 0),
'E',
DECODE(BAL.TRANSLATED_FLAG,
'R',
NVL(BEGIN_BALANCE_DR, 0) -
NVL(BEGIN_BALANCE_CR, 0),
NVL(BEGIN_BALANCE_DR_BEQ, 0) -
NVL(BEGIN_BALANCE_CR_BEQ, 0))))
) x
INTO OPENING_BAL
FROM GL_BALANCES BAL,
GL_CODE_COMBINATIONS CC,
GL_LEDGERS L,
GL_LEDGER_SET_ASSIGNMENTS ASG,
GL_LEDGER_RELATIONSHIPS LR
WHERE BAL.ACTUAL_FLAG = 'A'
AND BAL.CURRENCY_CODE = 'INR'
AND BAL.PERIOD_NAME =:P_PERIOD_FR
AND BAL.CODE_COMBINATION_ID = CC.CODE_COMBINATION_ID
AND CC.CHART_OF_ACCOUNTS_ID = 50348
AND CC.TEMPLATE_ID IS NULL
AND CC.SUMMARY_FLAG = 'N'
AND L.LEDGER_ID = 2022
AND ASG.LEDGER_SET_ID(+) = L.LEDGER_ID
AND LR.TARGET_LEDGER_ID = NVL(ASG.LEDGER_ID, L.LEDGER_ID)
AND LR.SOURCE_LEDGER_ID = NVL(ASG.LEDGER_ID, L.LEDGER_ID)
AND LR.TARGET_CURRENCY_CODE = 'INR'
AND LR.SOURCE_LEDGER_ID = BAL.LEDGER_ID
AND LR.TARGET_LEDGER_ID = BAL.LEDGER_ID
and cc.segment1 = :p_org_id
and cc.segment3 in (
SELECT DISTINCT gcc.segment3
FROM gl_code_combinations gcc,
fnd_flex_values_vl ffv,
fnd_flex_value_sets ffvs,
CE_BANK_ACCOUNTS CBA
WHERE gcc.segment3 = ffv.flex_value
AND ffv.flex_value_set_id = ffvs.flex_value_set_id
AND flex_value_set_name = 'HMI_Account'
--AND ffv.ATTRIBUTE1='BANK BOOK'
and ffv.FLEX_VALUE=:p_bank_cl
--AND CBA.ASSET_CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
--AND (ffv.description LIKE substr(BANK_ACCOUNT_NAME,1,instr(BANK_ACCOUNT_NAME,'CURRENT')-2)||'%BANK%CLEARING%')
AND CBA.BANK_ACCOUNT_NAME=:BANK_ACCT_NAME
-- AND CBA.BANK_ACCOUNT_NUM=:BANK_ACCT_NUM
);
IF SIGN(OPENING_BAL)=1 THEN
:CP_OPENING_BANK_CL_DR:=OPENING_BAL;
:CP_OPENING_BANK_CL_CR:=0;
:CP_DR_CR_b:='D';
ELSE
:CP_OPENING_BANK_CL_DR:=0;
:CP_OPENING_BANK_CL_CR:=ABS(OPENING_BAL);
:CP_DR_CR_b:='C';
END IF;

Difference Between date in Time formate

SELECT FLOOR ( ( (sysdate - (sysdate-1)) * 24 * 60 * 60) / 3600) || ' HOURS ' ||
FLOOR((((sysdate - (sysdate-1))*24*60*60) -
FLOOR(((sysdate - (sysdate-1))*24*60*60)/3600)*3600)/60)
|| ' MINUTES ' ||
ROUND((((sysdate - (sysdate-1))*24*60*60) -
FLOOR(((sysdate - (sysdate-1))*24*60*60)/3600)*3600 -
(FLOOR((((sysdate - (sysdate-1))*24*60*60) -
FLOOR(((sysdate - (sysdate-1))*24*60*60)/3600)*3600)/60)*60) ))
|| ' SECS ' time_difference from dual

Customer Ledger/ Debtor Ledger

SELECT ORG_ID,
CUSTOMER_NAME,
CUSTOMER_NUMBER,
CUSTOMER_ID,
TRX_DATE,
TRX_NUMBER,
CUSTOMER_TRX_ID,
VOUCHER_NO,
DUE_DATE,
AMOUNT_DR,
AMOUNT_CR,
USER_NAME,
EXCISE_INVOICE_NO,
TAX_INVOICE_NO,
GL_DATE,
CODE_COMBINATION,
ACCOUNT,
AC_DESC,
NAME,
VOC_SEQ
FROM ( SELECT HR.NAME ORG_ID,
ARC.CUSTOMER_NAME,
ARC.CUSTOMER_NUMBER,
ARC.CUSTOMER_ID,
RCT.TRX_DATE,
RCT.TRX_NUMBER,
RCT.CUSTOMER_TRX_ID,
RCT.DOC_SEQUENCE_VALUE VOUCHER_NO,
(SELECT DUE_DATE
FROM AR_PAYMENT_SCHEDULES_ALL
WHERE CUSTOMER_TRX_ID =
RCT.CUSTOMER_TRX_ID
AND ROWNUM = 1)
DUE_DATE,
NVL (
DECODE (
RCTT.TYPE,
'INV',
DECODE (
RCT.INVOICE_CURRENCY_CODE,
'INR',
SUM (RCTL.AMOUNT),
SUM (RCTL.AMOUNT)
* RCT.EXCHANGE_RATE
),
'DM',
DECODE (
RCT.INVOICE_CURRENCY_CODE,
'INR',
SUM (RCTL.AMOUNT),
SUM (RCTL.AMOUNT)
* RCT.EXCHANGE_RATE
),
'DEP',
DECODE (
RCT.INVOICE_CURRENCY_CODE,
'INR',
SUM (RCTL.AMOUNT),
SUM (RCTL.AMOUNT)
* RCT.EXCHANGE_RATE
)
),
0
)
AMOUNT_DR,
NVL (
DECODE (
RCTT.TYPE,
'CM',
DECODE (
RCT.INVOICE_CURRENCY_CODE,
'INR',
ABS (SUM (RCTL.AMOUNT)),
ABS (SUM (RCTL.AMOUNT))
* RCT.EXCHANGE_RATE
)
),
0
)
AMOUNT_CR,
FU.USER_NAME,
(SELECT JATL.EXCISE_INVOICE_NO
FROM JAI_AR_TRX_LINES JATL
WHERE JATL.CUSTOMER_TRX_ID =
RCT.CUSTOMER_TRX_ID
AND ROWNUM = 1)
EXCISE_INVOICE_NO,
RCTL.GL_DATE,
GCC.CONCATENATED_SEGMENTS CODE_COMBINATION,
GCC.SEGMENT3 ACCOUNT,
(SELECT DESCRIPTION
FROM RA_CUSTOMER_TRX_LINES_ALL
WHERE CUSTOMER_TRX_ID =
RCT.CUSTOMER_TRX_ID
AND ROWNUM = 1)
AC_DESC,
RCTT.NAME,
(SELECT NAME
FROM FND_DOCUMENT_SEQUENCES FDS
WHERE FDS.DOC_SEQUENCE_ID =
RCT.DOC_SEQUENCE_ID)
VOC_SEQ,
(SELECT NVL (JOW.VAT_INVOICE_NO,
WND.ATTRIBUTE1)
FROM RA_CUSTOMER_TRX_LINES_ALL RCTL,
JAI_OM_WSH_LINES_ALL JOW,
WSH_NEW_DELIVERIES WND
WHERE RCTL.INTERFACE_LINE_ATTRIBUTE3 =
TO_CHAR (JOW.DELIVERY_ID)
AND WND.DELIVERY_ID =
JOW.DELIVERY_ID
AND RCTL.CUSTOMER_TRX_ID =
RCT.CUSTOMER_TRX_ID
AND ROWNUM = 1)
TAX_INVOICE_NO
FROM RA_CUSTOMER_TRX_ALL RCT,
RA_CUST_TRX_LINE_GL_DIST_ALL RCTL,
FND_USER FU,
RA_CUST_TRX_TYPES_ALL RCTT,
GL_CODE_COMBINATIONS_KFV GCC,
FND_FLEX_VALUES_VL FFVL,
AR_CUSTOMERS ARC,
HR_OPERATING_UNITS HR,
HZ_CUST_ACCOUNTS HCA
WHERE RCTL.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
AND RCTL.ACCOUNT_CLASS = 'REC'
AND FU.USER_ID = RCT.CREATED_BY
AND RCTT.CUST_TRX_TYPE_ID =
RCT.CUST_TRX_TYPE_ID
AND GCC.CODE_COMBINATION_ID =
RCTL.CODE_COMBINATION_ID
AND GCC.SEGMENT3 = FFVL.FLEX_VALUE
AND FFVL.FLEX_VALUE_SET_ID = 1014163
AND ARC.CUSTOMER_ID =
RCT.BILL_TO_CUSTOMER_ID
AND RCT.ORG_ID = RCTT.ORG_ID
--AND RCT.TRX_NUMBER = '84002579'
AND RCT.COMPLETE_FLAG = 'Y'
AND ARC.CUSTOMER_ID = HCA.CUST_ACCOUNT_ID
AND TRUNC (RCTL.GL_DATE) BETWEEN P_FROM_DATE
AND P_TO_DATE
AND ARC.CUSTOMER_ID =
NVL (P_CUSTOMER_ID, ARC.CUSTOMER_ID)
-- AND GCC.SEGMENT3 = NVL(:P_ACCOUNT, GCC.SEGMENT3)
--AND RCT.ORG_ID = 81
---NVL(:P_ORG_ID, RCT.ORG_ID)
--- AND SUBSTR(HCA.CUSTOMER_CLASS_CODE, 1,2) = NVL(:P_BUSINESS_LINE, SUBSTR(HCA.CUSTOMER_CLASS_CODE, 1,2))
AND RCT.ORG_ID = HR.ORGANIZATION_ID
AND NVL (ARC.CUSTOMER_CATEGORY_CODE, '~') =
NVL (
P_CUST_TYPE,
NVL (ARC.CUSTOMER_CATEGORY_CODE,
'~')
)
GROUP BY ARC.CUSTOMER_NAME,
ARC.CUSTOMER_NUMBER,
ARC.CUSTOMER_ID,
RCT.TRX_DATE,
RCT.TRX_NUMBER,
RCT.DOC_SEQUENCE_VALUE,
RCT.CUSTOMER_TRX_ID,
FU.USER_NAME,
RCTL.GL_DATE,
RCTT.NAME,
GCC.CONCATENATED_SEGMENTS,
GCC.SEGMENT3,
FFVL.DESCRIPTION,
RCT.INVOICE_CURRENCY_CODE,
RCT.EXCHANGE_RATE,
RCTT.TYPE,
RCT.ORG_ID,
RCT.DOC_SEQUENCE_ID,
HR.NAME
UNION
SELECT HR.NAME ORG_ID,
ARC.CUSTOMER_NAME,
ARC.CUSTOMER_NUMBER,
ARC.CUSTOMER_ID,
ACR.RECEIPT_DATE TRX_DATE,
ACR.RECEIPT_NUMBER TRX_NUMBER,
ACR.CASH_RECEIPT_ID CUSTOMER_TRX_ID,
ACR.DOC_SEQUENCE_VALUE VOUCHER_NO,
ACR.RECEIPT_DATE DUE_DATE,
NVL (SUM (ADR.ACCTD_AMOUNT_CR), 0)
AMOUNT_DR,
NVL (SUM (ADR.ACCTD_AMOUNT_DR), 0)
- NVL (
(SELECT SUM (ARA.AMOUNT_APPLIED)
FROM AR_RECEIVABLE_APPLICATIONS_V ARA
WHERE ARA.CASH_RECEIPT_ID =
ACR.CASH_RECEIPT_ID
AND ARA.TRX_NUMBER =
'Refund'),
0
)
AMOUNT_CR,
FU.USER_NAME,
NULL EXCISE_INVOICE_NO,
ACRH.GL_DATE GL_DATE,
GCC.CONCATENATED_SEGMENTS CODE_COMBINATION,
GCC.SEGMENT3 ACCOUNT,
NULL /*FFVL.DESCRIPTION*/
AC_DESC,
ARM.NAME NAME,
(SELECT NAME
FROM FND_DOCUMENT_SEQUENCES FDS
WHERE FDS.DOC_SEQUENCE_ID =
ACR.DOC_SEQUENCE_ID)
VOC_SEQ,
NULL TAX_INVOICE_NO
FROM AR_CASH_RECEIPTS_ALL ACR,
AR_CASH_RECEIPT_HISTORY_ALL ACRH,
AR_DISTRIBUTIONS_ALL ADR,
FND_USER FU,
AR_CUSTOMERS ARC,
GL_CODE_COMBINATIONS_KFV GCC,
FND_FLEX_VALUES_VL FFVL,
AR_RECEIPT_METHODS ARM,
HR_OPERATING_UNITS HR,
HZ_CUST_ACCOUNTS HCA
WHERE ACR.CASH_RECEIPT_ID = ACRH.CASH_RECEIPT_ID
AND FU.USER_ID = ACR.CREATED_BY
AND ARM.RECEIPT_METHOD_ID =
ACR.RECEIPT_METHOD_ID
AND ARC.CUSTOMER_ID = ACR.PAY_FROM_CUSTOMER
AND GCC.CODE_COMBINATION_ID =
ADR.CODE_COMBINATION_ID
AND ADR.SOURCE_TYPE IN
('REMITTANCE', 'CASH')
--AND ACRH.STATUS='CLEARED'
--AND ADR.SOURCE_TYPE='CASH'
AND ADR.SOURCE_ID =
ACRH.CASH_RECEIPT_HISTORY_ID
AND GCC.SEGMENT3 = FFVL.FLEX_VALUE
AND FFVL.FLEX_VALUE_SET_ID = 1014163
AND ARC.CUSTOMER_ID = HCA.CUST_ACCOUNT_ID
-- AND ACR.RECEIPT_NUMBER ='SR0600200'--'SUPP. INV.NO.-10107033'
AND TRUNC (ACRH.GL_DATE) BETWEEN P_FROM_DATE
AND P_TO_DATE
AND ARC.CUSTOMER_ID =
NVL (P_CUSTOMER_ID, ARC.CUSTOMER_ID)
--AND ACR.ORG_ID = 81
--NVL(:P_ORG_ID, ACR.ORG_ID)
--AND ARC.CUSTOMER_ID BETWEEN
-- NVL(:P_FROM_CUSTOMER_ID, ARC.CUSTOMER_ID) AND
-- NVL(:P_TO_CUSTOMER_ID, ARC.CUSTOMER_ID)
------ AND GCC.SEGMENT3 = NVL(:P_ACCOUNT, GCC.SEGMENT3)
---AND SUBSTR(HCA.CUSTOMER_CLASS_CODE, 1,2) = NVL(:P_BUSINESS_LINE, SUBSTR(HCA.CUSTOMER_CLASS_CODE, 1,2))
AND NVL (ARC.CUSTOMER_CATEGORY_CODE, '~') =
NVL (
P_CUST_TYPE,
NVL (ARC.CUSTOMER_CATEGORY_CODE,
'~')
)
AND CONFIRMED_FLAG = 'Y'
AND ACR.ORG_ID = HR.ORGANIZATION_ID
GROUP BY ACR.RECEIPT_DATE,
ACR.RECEIPT_NUMBER,
ACR.DOC_SEQUENCE_VALUE,
ACR.RECEIPT_DATE,
FU.USER_NAME,
ARC.CUSTOMER_NAME,
ARC.CUSTOMER_NUMBER,
ARC.CUSTOMER_ID,
ACR.CASH_RECEIPT_ID,
ACRH.GL_DATE,
GCC.CONCATENATED_SEGMENTS,
GCC.SEGMENT3,
FFVL.DESCRIPTION,
ACR.ORG_ID,
HR.NAME,
ACR.DOC_SEQUENCE_ID,
ARM.NAME) A
WHERE A.DUE_DATE = NVL (NULL, A.DUE_DATE)
AND AMOUNT_DR <> AMOUNT_CR
ORDER BY TRX_DATE, GL_DATE;

Customer Ledger Basic Query

SELECT TRX_NUMBER,
TRX_DATE,
GL_DATE,
CUSTOMER_NAME,
NAME,
INVOICE_CURRENCY_CODE,
CASE
WHEN t.type in ('INV', 'DM') THEN
AMOUNT
END DR,
CASE
WHEN T.TYPE = 'CM' THEN
AMOUNT * -1
END CR,
B.SEGMENT1,
B.SEGMENT2,
B.SEGMENT3,
B.SEGMENT4,
B.SEGMENT5,
B.SEGMENT6,
B.SEGMENT7,
B.SEGMENT8
FROM AR.RA_CUST_TRX_LINE_GL_DIST_ALL D,
GL.GL_CODE_COMBINATIONS B,
AR.RA_CUSTOMER_TRX_ALL C,
APPS.AR_CUSTOMERS M,
AR.RA_CUST_TRX_TYPES_ALL T,
AR.RA_CUSTOMER_TRX_LINES_ALL L
WHERE D.CODE_COMBINATION_ID = b.CODE_COMBINATION_ID
AND c.CUSTOMER_TRX_ID = d.CUSTOMER_TRX_ID
AND c.org_id = '83'
AND M.CUSTOMER_ID = c.BILL_TO_CUSTOMER_ID
AND T.CUST_TRX_TYPE_ID = c.CUST_TRX_TYPE_ID
AND L.CUSTOMER_TRX_LINE_ID(+) = D.CUSTOMER_TRX_LINE_ID
AND GL_POSTED_DATE IS NOT NULL
and ACCOUNT_CLASS = 'REC'
and ACCOUNTING_AFFECT_FLAG = 'Y'
union all
SELECT RECEIPT_NUMBER,
RECEIPT_DATE,
ACRH.GL_DATE,
CUSTOMER_NAME,
'RECEIPT',
ACR.CURRENCY_CODE,
AMOUNT_CR,
AMOUNT_DR,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5,
GCC.SEGMENT6,
GCC.SEGMENT7,
GCC.SEGMent8
FROM AR.AR_CASH_RECEIPTS_ALL ACR,
AR.AR_CASH_RECEIPT_HISTORY_ALL ACRH,
AR.AR_DISTRIBUTIONS_ALL ADR,
APPS.AR_CUSTOMERS ARC,
APPS.GL_CODE_COMBINATIONS_KFV GCC
where ACR.CASH_RECEIPT_ID = ACRH.CASH_RECEIPT_ID
AND ARC.CUSTOMER_ID = ACR.PAY_FROM_CUSTOMER
AND GCC.CODE_COMBINATION_ID = ADR.CODE_COMBINATION_ID
AND ADR.SOURCE_TYPE IN ('REMITTANCE', 'CASH')
AND acr.org_id = 83
AND ADR.SOURCE_ID = ACRH.CASH_RECEIPT_HISTORY_ID

Monday, 25 May 2015

BANK BOOK Reconcilation Report Query.

Difference Between date in Time formate

Customer Ledger/ Debtor Ledger

Customer Ledger Basic Query

Query to Find Receipt Class and its GL Combinition Query