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;