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;
