EBS: How to view Workflow processes and Notifications owned by all users

 

When I open the status monitor page from Workflow administrator, I am unable to view the workflow notifications from frontend.

Issue: Workflow owned by is showing my name "SDEVAKI" but I need to view all the workflows so that I can see the status of any workflow.

In order to view all the workflows, I need to change the settings of workflow administrator.

Login with SYSADMIN user.

Navigate to Workflow Administrator web applications responsibility and go to administration tab and change the below value from SYSADMIN to *.

Updated to below screenshot.

Now, Navigate to your login and see the value for workflow owned by is changed.

Oracle APPS Open Payable Summary Report

SELECT DISTINCT    invoice.org_name,    invoice.vendor_name,
    invoice.vendor_number,
    invoice.vendor_type_lookup_code,
    invoice.vendor_site_code,
    invoice.source,
    invoice.invoice_num,
    to_char(invoice.invoice_date, 'DD-MON-RRRR') invoice_date,
    to_char(invoice.due_date, 'DD-MON-RRRR') due_date,
    invoice.terms,
    invoice.supplier_site_terms,
    invoice.payment_method_code,
    invoice.invoice_currency_code,
    invoice.invoice_amount,
    holds.hold,
    invoice.check_date,
    ( to_date(invoice.check_date) - ( invoice.invoice_date ) ) days_to_pay,
    :p_recipient_email_address recipient_email_address
FROM
    (
        SELECT
            aip.org_id,
            hro.name      org_name,
            av.vendor_name,
            av.segment1   vendor_number,
            av.vendor_type_lookup_code,
            ass.vendor_site_code,
            aip.invoice_id,
            aip.invoice_num,
            aip.invoice_date,
            aip.source,
            aps.due_date,
            apt.name      terms,
            apts.name     supplier_site_terms,
            aip.payment_method_code,
            aip.invoice_currency_code,
            aip.invoice_amount,
--'31-MAR-16' Check_Date
            :p_check_date check_date 
        FROM
            ap.ap_invoices_all             aip,
            ap.ap_payment_schedules_all    aps,
            ap.ap_supplier_sites_all       ass,
            ap.ap_suppliers                av,
            ap.ap_terms_tl                 apt,
            ap.ap_terms_tl                 apts,
            hr.hr_all_organization_units   hro
        WHERE
            aip.invoice_id = aps.invoice_id
            AND ass.vendor_site_id = aip.vendor_site_id
            AND ass.vendor_id = av.vendor_id
            AND av.vendor_name = nvl(:p_vendor_name, av.vendor_name)
            AND apts.term_id = ass.terms_id
            AND aip.terms_id = apt.term_id
            AND aip.org_id = hro.organization_id
            AND hro.name = nvl(:p_organization, hro.name)
            AND aip.payment_status_flag = 'N'
            AND aip.cancelled_date IS NULL
            AND apt.language = 'US'
            AND aps.due_date <= :p_due_date_through
            AND --'31-MAR-2016' and
             aip.payment_method_code = nvl(:p_payment_method, aip.payment_method_code)
            AND aip.invoice_amount <> 0
    ) invoice,
    (
        SELECT DISTINCT
            ah.invoice_id,
            'Invoice_on_hold' hold
        FROM
            ap_holds_all ah
        WHERE
            ah.release_reason IS NULL
    ) holds
WHERE
    invoice.invoice_id = holds.invoice_id (+)
ORDER BY
    invoice.org_name

Oracle APPS Bank Statement Detail Report

 <?xml version = '1.0' encoding = 'UTF-8'?>

<!-- $Header: CEXSTMRR_XML.xml 120.0 2007/12/28 08:05:11 abraghun noship $ -->

<!-- dbdrv: exec java oracle/apps/xdo/oa/util XDOLoader.class java &phase=dat checkfile:~PROD:patch/115/publisher/defs:CEXSTMRR_XML.xml UPLOAD -DB_USERNAME &un_apps -DB_PASSWORD &pw_apps -JDBC_CONNECTION &jdbc_db_addr -LOB_TYPE DATA_TEMPLATE -APPS_SHORT_NAME ~PROD -LOB_CODE CEXSTMRR_XML -LANGUAGE 00 -XDO_FILE_TYPE XML-DATA-TEMPLATE -FILE_NAME &fullpath:~PROD:patch/115/publisher/defs:CEXSTMRR_XML.xml -->

<!-- GSCC_Category="Xdo Data Definition" -->

<dataTemplate name="CEXSTMRR" defaultPackage="CE_CEXSTMRR_XMLP_PKG" version="1.0">

   <properties>

      <property name="xml_tag_case" value="upper"/>

   </properties>

   <parameters>

      <!-- <parameter name="P_CONC_REQUEST_ID" dataType="number" defaultValue="0"></parameter> -->

      <parameter name="P_SQL_TRACE" dataType="character" defaultValue="N"/>

      <parameter name="P_TEST_LAYOUT" dataType="character" defaultValue="N"/>

      <parameter name="P_DEBUG_MODE" dataType="character" defaultValue="N"/>

      <parameter name="P_STAT_DATE_FROM" dataType="date"/>

      <parameter name="P_STAT_DATE_TO" dataType="date"/>

      <parameter name="P_BANK_ACCOUNT_ID" dataType="number"/>

      <parameter name="P_STATEMENT_OR_ACCOUNT" dataType="character" defaultValue="STATEMENT"/>

      <parameter name="P_STAT_NUMBER_FROM" dataType="character"/>

      <parameter name="P_STAT_NUMBER_TO" dataType="character"/>

      <parameter name="P_STMT_LINE_STATUS" dataType="character" defaultValue="ALL"/>

      <parameter name="C_TRX_REFERENCE_TYPE" dataType="character"/>

   </parameters>

   <lexicals/>

   <dataQuery>

      <sqlStatement name="Q_STATEMENT_LINES">

         <![CDATA[

   WITH Q1 AS(     

  

  SELECT DECODE(sl.status, 'EXTERNAL', 'RECONCILED',DECODE(sr.reference_type,NULL,'UNRECONCILED','RECONCILED') ) C_STAT_TYPE,

DECODE(:P_STATEMENT_OR_ACCOUNT,'STATEMENT',to_char(sl.line_number),sh.statement_number||'/'||sl.line_number)  C_SORT,

sh.statement_header_id C_STAT_HEADER_ID,

sl.statement_line_id C_STAT_LINE_ID,

sh.doc_sequence_value C_STAT_DOC_SEQ,

aba.bank_account_id C_BANK_ACCOUNT_ID,

l1.meaning C_SL_TYPE_DSP,

sl.trx_type C_SL_TYPE,

aba.bank_account_name C_BANK_ACCOUNT_NAME,

aba.bank_account_num C_BANK_ACCOUNT_NUM,

aba.currency_code C_BANK_ACC_CURRENCY,

abb.bank_branch_name C_BANK_BRANCH_NAME,

abb.bank_name C_BANK_NAME,

DECODE(:P_STATEMENT_OR_ACCOUNT,'STATEMENT',to_number(to_char(sh.statement_date,'J')),aba.bank_account_id)

C_ORDERING,

DECODE(:P_STATEMENT_OR_ACCOUNT,'STATEMENT',sl.line_number,to_number(to_char(sh.statement_date,'J')))

C_ORDER_BY,

DECODE(:P_STATEMENT_OR_ACCOUNT, 'STATEMENT', sh.statement_number, aba.bank_account_num)  C_STAT_NUMBER,

  sl.line_number         C_SL_NUMBER,

DECODE(:P_STATEMENT_OR_ACCOUNT,'STATEMENT',to_char(sl.trx_date, :C_DATEFORMAT),sh.statement_number) C_SL_DATE,

NVL(sl.amount,0) C_SL_AMOUNT,

DECODE(sl.trx_type,'DEBIT', NVL(-sl.amount, 0),'CREDIT', NVL(sl.amount,0),'MISC_DEBIT',NVL(-sl.amount,0),'MISC_CREDIT',NVL(sl.amount,0),'NSF',NVL(-sl.amount,0),'STOP',NVL(sl.amount,0),'REJECTED',NVL(-sl.amount,0),'SWEEP_IN',NVL(sl.amount,0),'SWEEP_OUT',NVL(-sl.amount,0),0) C_SL_SIGNED_AMOUNT,

DECODE(sl.trx_type,'DEBIT',NVL(sl.amount,0),0) C_SL_DR,

DECODE(sl.trx_type,'MISC_DEBIT',NVL(sl.amount,0),0) C_SL_MISC_DR,

DECODE(sl.trx_type,'CREDIT',NVL(sl.amount,0),0) C_SL_CR,

DECODE(sl.trx_type,'MISC_CREDIT',NVL(sl.amount,0),0) C_SL_MISC_CR,

DECODE(sl.trx_type,'NSF',NVL(sl.amount,0),0) C_SL_NSF,

DECODE(sl.trx_type,'STOP',NVL(sl.amount,0),0) C_SL_STOP,

DECODE(sl.trx_type,'REJECTED',NVL(sl.amount,0),0)                C_SL_REJECTED,

DECODE(sl.trx_type,'SWEEP_IN',NVL(sl.amount,0),0) C_SL_SWP_IN,

DECODE(sl.trx_type,'SWEEP_OUT',NVL(sl.amount,0),0) C_SL_SWP_OUT,

sl.status C_STAT_LINE_STATUS,

l2.meaning   C_STAT_LINE_STATUS_DSP,

DECODE(sl.status,'ERROR',

   DECODE(sl.trx_type,'CREDIT',NVL(sl.amount,0),

   'MISC_CREDIT',NVL(sl.amount,0),

   'SWEEP_IN',NVL(sl.amount,0),0),0) C_SL_CR_ERRORS,

DECODE(sl.status,'ERROR',

   DECODE(sl.trx_type,'DEBIT',sl.amount,

   'MISC_DEBIT',NVL(sl.amount,0),

   'SWEEP_OUT',NVL(sl.amount,0),0),0) C_SL_DR_ERRORS,

sl.bank_trx_number C_SL_TRX_NUMBER,

sl.charges_amount C_SL_BANK_CHARGES,

sh.control_end_balance C_STAT_CONTROL_END,

sh.statement_date C_STAT_STATEMENT_DATE,

  sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0)) C_SL_REC_AMOUNT,

(DECODE (sl.status , 'EXTERNAL', 0, NVL( sl.amount , 0)) -

sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0)) )  C_SL_UNREC_AMOUNT,

CE_CEXSTMRR_XMLP_PKG.c_stat_sum_rec_lineformula(:C_STAT_SUM_LINE_AMOUNT, :C_STAT_UNREC_SUM_LINE) C_STAT_SUM_REC_LINE,

CE_CEXSTMRR_XMLP_PKG.c_sl_unrec_signed_amountformul(DECODE(sl.trx_type,'DEBIT', NVL(-sl.amount, 0),'CREDIT', NVL(sl.amount,0),'MISC_DEBIT',NVL(-sl.amount,0),'MISC_CREDIT',NVL(sl.amount,0),'NSF',NVL(-sl.amount,0),'STOP',NVL(sl.amount,0),'REJECTED',NVL(-sl.amount,0),'SWEEP_IN',NVL(sl.amount,0),'SWEEP_OUT',NVL(-sl.amount,0),0),sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0))) C_SL_UNREC_SIGNED_AMOUNT,

CE_CEXSTMRR_XMLP_PKG.c_sl_unrec_pos_amountformula(sl.status,NVL(sl.amount,0),sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0)))  C_SL_POS_UNREC_AMOUNT,

CE_CEXSTMRR_XMLP_PKG.c_sl_swp_in2formula((DECODE (sl.status , 'EXTERNAL', 0, NVL( sl.amount , 0)) -

sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0)) ),DECODE(sl.trx_type,'SWEEP_IN',NVL(sl.amount,0),0)) C_SL_SWP_IN2,

CE_CEXSTMRR_XMLP_PKG.c_sl_rec_drformula(sl.trx_type,sl.status,NVL(sl.amount,0),(DECODE (sl.status , 'EXTERNAL', 0, NVL( sl.amount , 0)) -

sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0)) ),sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0))) C_SL_REC_DR,

CE_CEXSTMRR_XMLP_PKG.c_sl_rec_dr_miscformula(sl.trx_type,sl.status,NVL(sl.amount,0),(DECODE (sl.status , 'EXTERNAL', 0, NVL( sl.amount , 0)) -

sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0)) ),sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0))) C_SL_REC_DR_MISC,

CE_CEXSTMRR_XMLP_PKG.c_sl_rec_crformula(sl.trx_type,sl.status,NVL(sl.amount,0),(DECODE (sl.status , 'EXTERNAL', 0, NVL( sl.amount , 0)) -

sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0)) ),sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0)))  C_SL_REC_CR,

CE_CEXSTMRR_XMLP_PKG.c_sl_rec_cr_miscformula(sl.trx_type,sl.status,NVL(sl.amount,0),(DECODE (sl.status , 'EXTERNAL', 0, NVL( sl.amount , 0)) -

sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0)) ),sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0))) C_SL_REC_CR_MISC,

CE_CEXSTMRR_XMLP_PKG.c_sl_rec_nsfformula(sl.trx_type,sl.status,NVL(sl.amount,0),(DECODE (sl.status , 'EXTERNAL', 0, NVL( sl.amount , 0)) -

sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0)) ),sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0))) C_SL_REC_NSF,

CE_CEXSTMRR_XMLP_PKG.c_sl_rec_rejectedformula(sl.trx_type,sl.status,NVL(sl.amount,0),(DECODE (sl.status , 'EXTERNAL', 0, NVL( sl.amount , 0)) -

sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0)) ),sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0))) C_SL_REC_REJECTED,

CE_CEXSTMRR_XMLP_PKG.c_sl_rec_swp_outformula(sl.trx_type,sl.status,NVL(sl.amount,0),(DECODE (sl.status , 'EXTERNAL', 0, NVL( sl.amount , 0)) -

sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0)) ),sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0))) C_SL_REC_SWP_OUT,

CE_CEXSTMRR_XMLP_PKG.c_sl_rec_swp_informula(sl.trx_type,sl.status,NVL(sl.amount,0),(DECODE (sl.status , 'EXTERNAL', 0, NVL( sl.amount , 0)) -

sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0)) ),sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0))) C_SL_REC_SWP_IN,

CE_CEXSTMRR_XMLP_PKG.c_sl_rec_stopformula(sl.trx_type,sl.status,NVL(sl.amount,0),(DECODE (sl.status , 'EXTERNAL', 0, NVL( sl.amount , 0)) -

sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0)) ),sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0))) C_SL_REC_STOP,

CE_CEXSTMRR_XMLP_PKG.c_sl_unrec_drformula(sl.trx_type,DECODE(sl.status, 'EXTERNAL', 'RECONCILED',DECODE(sr.reference_type,NULL,'UNRECONCILED','RECONCILED') ),(DECODE (sl.status , 'EXTERNAL', 0, NVL( sl.amount , 0)) -

sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0)) ))   C_SL_UNREC_DR,

CE_CEXSTMRR_XMLP_PKG.c_sl_unrec_dr_miscformula(sl.trx_type,DECODE(sl.status, 'EXTERNAL', 'RECONCILED',DECODE(sr.reference_type,NULL,'UNRECONCILED','RECONCILED') ),(DECODE (sl.status , 'EXTERNAL', 0, NVL( sl.amount , 0)) -

sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0)) )) C_SL_UNREC_DR_MISC,

CE_CEXSTMRR_XMLP_PKG.c_sl_unrec_crformula(sl.trx_type,DECODE(sl.status, 'EXTERNAL', 'RECONCILED',DECODE(sr.reference_type,NULL,'UNRECONCILED','RECONCILED') ),(DECODE (sl.status , 'EXTERNAL', 0, NVL( sl.amount , 0)) -

sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0)) )) C_SL_UNREC_CR,

CE_CEXSTMRR_XMLP_PKG.c_sl_unrec_swp_informula(sl.trx_type,DECODE(sl.status, 'EXTERNAL', 'RECONCILED',DECODE(sr.reference_type,NULL,'UNRECONCILED','RECONCILED') ),(DECODE (sl.status , 'EXTERNAL', 0, NVL( sl.amount , 0)) -

sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0)) )) C_SL_UNREC_SWP_IN,

CE_CEXSTMRR_XMLP_PKG.c_sl_unrec_swp_outformula(sl.trx_type,DECODE(sl.status, 'EXTERNAL', 'RECONCILED',DECODE(sr.reference_type,NULL,'UNRECONCILED','RECONCILED') ),(DECODE (sl.status , 'EXTERNAL', 0, NVL( sl.amount , 0)) -

sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0)) )) C_SL_UNREC_SWP_OUT,

CE_CEXSTMRR_XMLP_PKG.c_sl_overrec_crformula(sl.trx_type,DECODE(sl.status, 'EXTERNAL', 'RECONCILED',DECODE(sr.reference_type,NULL,'UNRECONCILED','RECONCILED') ),(DECODE (sl.status , 'EXTERNAL', 0, NVL( sl.amount , 0)) -

sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0)) )) C_SL_OVERREC_CR,

CE_CEXSTMRR_XMLP_PKG.c_sl_overrec_cr_miscformula(sl.trx_type,DECODE(sl.status, 'EXTERNAL', 'RECONCILED',DECODE(sr.reference_type,NULL,'UNRECONCILED','RECONCILED') ),(DECODE (sl.status , 'EXTERNAL', 0, NVL( sl.amount , 0)) -

sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0)) )) C_SL_OVERREC_CR_MISC,

CE_CEXSTMRR_XMLP_PKG.c_sl_overrec_drformula(sl.trx_type,DECODE(sl.status, 'EXTERNAL', 'RECONCILED',DECODE(sr.reference_type,NULL,'UNRECONCILED','RECONCILED') ),(DECODE (sl.status , 'EXTERNAL', 0, NVL( sl.amount , 0)) -

sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0)) )) C_SL_OVERREC_DR,

CE_CEXSTMRR_XMLP_PKG.c_sl_overrec_dr_miscformula(sl.trx_type,DECODE(sl.status, 'EXTERNAL', 'RECONCILED',DECODE(sr.reference_type,NULL,'UNRECONCILED','RECONCILED') ),(DECODE (sl.status , 'EXTERNAL', 0, NVL( sl.amount , 0)) -

sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0)) )) C_SL_OVERREC_DR_MISC,

CE_CEXSTMRR_XMLP_PKG.c_sl_overrec_nsfformula(sl.trx_type,DECODE(sl.status, 'EXTERNAL', 'RECONCILED',DECODE(sr.reference_type,NULL,'UNRECONCILED','RECONCILED') ),(DECODE (sl.status , 'EXTERNAL', 0, NVL( sl.amount , 0)) -

sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0)) )) C_SL_OVERREC_NSF,

CE_CEXSTMRR_XMLP_PKG.c_sl_overrec_rejectedformula(sl.trx_type,DECODE(sl.status, 'EXTERNAL', 'RECONCILED',DECODE(sr.reference_type,NULL,'UNRECONCILED','RECONCILED') ),(DECODE (sl.status , 'EXTERNAL', 0, NVL( sl.amount , 0)) -

sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0)) )) C_SL_OVERREC_REJECTED,

CE_CEXSTMRR_XMLP_PKG.c_sl_overrec_swp_outformula(sl.trx_type,DECODE(sl.status, 'EXTERNAL', 'RECONCILED',DECODE(sr.reference_type,NULL,'UNRECONCILED','RECONCILED') ),(DECODE (sl.status , 'EXTERNAL', 0, NVL( sl.amount , 0)) -

sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0)) )) C_SL_OVERREC_SWP_OUT,

CE_CEXSTMRR_XMLP_PKG.c_sl_overrec_swp_informula(sl.trx_type,DECODE(sl.status, 'EXTERNAL', 'RECONCILED',DECODE(sr.reference_type,NULL,'UNRECONCILED','RECONCILED') ),(DECODE (sl.status , 'EXTERNAL', 0, NVL( sl.amount , 0)) -

sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0)) )) C_SL_OVERREC_SWP_IN,

CE_CEXSTMRR_XMLP_PKG.c_sl_overrec_stopformula(sl.trx_type,DECODE(sl.status, 'EXTERNAL', 'RECONCILED',DECODE(sr.reference_type,NULL,'UNRECONCILED','RECONCILED') ),(DECODE (sl.status , 'EXTERNAL', 0, NVL( sl.amount , 0)) -

sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0)) )) C_SL_OVERREC_STOP,

CE_CEXSTMRR_XMLP_PKG.c_sl_unrec_cr_miscformula(sl.trx_type,DECODE(sl.status, 'EXTERNAL', 'RECONCILED',DECODE(sr.reference_type,NULL,'UNRECONCILED','RECONCILED') ),(DECODE (sl.status , 'EXTERNAL', 0, NVL( sl.amount , 0)) -

sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0)) )) C_SL_UNREC_CR_MISC,

CE_CEXSTMRR_XMLP_PKG.c_sl_unrec_nsfformula(sl.trx_type,DECODE(sl.status, 'EXTERNAL', 'RECONCILED',DECODE(sr.reference_type,NULL,'UNRECONCILED','RECONCILED') ),(DECODE (sl.status , 'EXTERNAL', 0, NVL( sl.amount , 0)) -

sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0)) )) C_SL_UNREC_NSF,

CE_CEXSTMRR_XMLP_PKG.c_sl_unrec_rejectedformula(sl.trx_type,DECODE(sl.status, 'EXTERNAL', 'RECONCILED',DECODE(sr.reference_type,NULL,'UNRECONCILED','RECONCILED') ),(DECODE (sl.status , 'EXTERNAL', 0, NVL( sl.amount , 0)) -

sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0)) )) C_SL_UNREC_REJECTED,

CE_CEXSTMRR_XMLP_PKG.c_sl_unrec_stopformula(sl.trx_type,DECODE(sl.status, 'EXTERNAL', 'RECONCILED',DECODE(sr.reference_type,NULL,'UNRECONCILED','RECONCILED') ),(DECODE (sl.status , 'EXTERNAL', 0, NVL( sl.amount , 0)) -

sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0)) )) C_SL_UNREC_STOP,

CE_CEXSTMRR_XMLP_PKG.c_sl_dr2formula((DECODE (sl.status , 'EXTERNAL', 0, NVL( sl.amount , 0)) -

sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0)) ),DECODE(sl.trx_type,'DEBIT',NVL(sl.amount,0),0)) C_SL_DR2,

CE_CEXSTMRR_XMLP_PKG.c_sl_misc_dr2formula((DECODE (sl.status , 'EXTERNAL', 0, NVL( sl.amount , 0)) -

sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0)) ),DECODE(sl.trx_type,'MISC_DEBIT',NVL(sl.amount,0),0)) C_SL_MISC_DR2,

CE_CEXSTMRR_XMLP_PKG.c_sl_cr2formula((DECODE (sl.status , 'EXTERNAL', 0, NVL( sl.amount , 0)) -

sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0)) ),DECODE(sl.trx_type,'CREDIT',NVL(sl.amount,0),0)) C_SL_CR2,

CE_CEXSTMRR_XMLP_PKG.c_sl_misc_cr2formula((DECODE (sl.status , 'EXTERNAL', 0, NVL( sl.amount , 0)) -

sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0)) ),DECODE(sl.trx_type,'MISC_CREDIT',NVL(sl.amount,0),0)) C_SL_MISC_CR2,

CE_CEXSTMRR_XMLP_PKG.c_sl_nsf2formula((DECODE (sl.status , 'EXTERNAL', 0, NVL( sl.amount , 0)) -

sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0)) ),DECODE(sl.trx_type,'NSF',NVL(sl.amount,0),0)) C_SL_NSF2,

CE_CEXSTMRR_XMLP_PKG.c_sl_rejected2formula((DECODE (sl.status , 'EXTERNAL', 0, NVL( sl.amount , 0)) -

sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0)) ),DECODE(sl.trx_type,'REJECTED',NVL(sl.amount,0),0)) C_SL_REJECTED2,

CE_CEXSTMRR_XMLP_PKG.c_sl_swp_out2formula((DECODE (sl.status , 'EXTERNAL', 0, NVL( sl.amount , 0)) -

sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0)) ),DECODE(sl.trx_type,'SWEEP_OUT',NVL(sl.amount,0),0)) C_SL_SWP_OUT2,

CE_CEXSTMRR_XMLP_PKG.c_sl_stop2formula((DECODE (sl.status , 'EXTERNAL', 0, NVL( sl.amount , 0)) -

sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0)) ),DECODE(sl.trx_type,'STOP',NVL(sl.amount,0),0)) C_SL_STOP2,

CE_CEXSTMRR_XMLP_PKG.c_tx_sign_sl_unrec_amountformu(sl.trx_type,(DECODE (sl.status , 'EXTERNAL', 0, NVL( sl.amount , 0)) -

sum(nvl(decode (sl.trx_type, 'MISC_CREDIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type),

'PAYMENT', nvl(-sr.amount, 0), nvl(sr.amount,0)),

'RECEIPT' , DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(- sr.amount, 0), nvl(sr.amount, 0 ) ) ,

'CREDIT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'PAYMENT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'MISC_DEBIT', DECODE(decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT', nvl(-sr.amount,0), nvl(sr.amount, 0 ) ) ,

'PAYMENT' , DECODE ( decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount , 0 ) ) ,

'DEBIT' , DECODE (  decode(sr.reference_type , 'DM REVERSAL', 'RECEIPT', sr.reference_type), 'RECEIPT' , nvl(-sr.amount,0), nvl(sr.amount,0) ) ,

nvl ( sr.amount , 0 ) ),0)) )) C_TX_SIGN_SL_UNREC_AMOUNT

FROM  ce_bank_branches_v abb,

ce_bank_accts_gt_v aba,

ce_lookups l1,

ce_lookups l2,

ce_statement_reconcils_all sr,

ce_statement_lines sl,

ce_statement_headers sh

WHERE l1.lookup_type = 'BANK_TRX_TYPE'

AND l1.lookup_code = sl.trx_type

AND l2.lookup_type = 'STATEMENT_LINE_STATUS'

AND l2.lookup_code = sl.status

AND abb.branch_party_id = aba.bank_branch_id

AND aba.bank_account_id = NVL(:P_BANK_ACCOUNT_ID,aba.bank_account_id)

AND aba.bank_account_id = sh.bank_account_id

AND sr.status_flag(+) = 'M'

AND sr.current_record_flag(+) = 'Y'

AND sr.statement_line_id(+) = sl.statement_line_id

AND sl.statement_header_id  =  sh.statement_header_id

AND sl.status LIKE decode(:P_STMT_LINE_STATUS, 'RECONCILED', :P_STMT_LINE_STATUS,

           'UNRECONCILED', '%RECONCILED', sl.status)

AND &C_STAT_DATE_SQL_LEX

AND &C_STAT_NUMBER_SQL_LEX

GROUP BY

DECODE(sl.status, 'EXTERNAL', 'RECONCILED',DECODE(sr.reference_type,NULL,'UNRECONCILED','RECONCILED') )

,

DECODE(:P_STATEMENT_OR_ACCOUNT,'STATEMENT',to_char(sl.line_number),sh.statement_number||'/'||sl.line_number) ,

sh.statement_header_id ,

sl.statement_line_id ,

sh.doc_sequence_value ,

aba.bank_account_id ,

l1.meaning ,

sl.trx_type ,

aba.bank_account_name ,

aba.bank_account_num ,

aba.currency_code ,

abb.bank_branch_name ,

abb.bank_name ,

DECODE(:P_STATEMENT_OR_ACCOUNT,'STATEMENT',to_number(to_char(sh.statement_date,'J')),aba.bank_account_id)

,

DECODE(:P_STATEMENT_OR_ACCOUNT,'STATEMENT',sl.line_number,to_number(to_char(sh.statement_date,'J')))

,

DECODE(:P_STATEMENT_OR_ACCOUNT, 'STATEMENT', sh.statement_number, aba.bank_account_num) ,

  sl.line_number         ,

DECODE(:P_STATEMENT_OR_ACCOUNT,'STATEMENT',to_char(sl.trx_date, :C_DATEFORMAT),sh.statement_number) ,

NVL(sl.amount,0) ,

DECODE(sl.trx_type,'DEBIT', NVL(-sl.amount, 0),

'CREDIT', NVL(sl.amount,0),

'MISC_DEBIT',NVL(-sl.amount,0),

'MISC_CREDIT',NVL(sl.amount,0),

'NSF',NVL(-sl.amount,0),

'STOP',NVL(sl.amount,0),

'REJECTED',NVL(-sl.amount,0),

'SWEEP_IN',NVL(sl.amount,0),

'SWEEP_OUT',NVL(-sl.amount,0),

0) ,

DECODE(sl.trx_type,'DEBIT',NVL(sl.amount,0),0) ,

DECODE(sl.trx_type,'MISC_DEBIT',NVL(sl.amount,0),0) ,

DECODE(sl.trx_type,'CREDIT',NVL(sl.amount,0),0) ,

DECODE(sl.trx_type,'MISC_CREDIT',NVL(sl.amount,0),0) ,

DECODE(sl.trx_type,'NSF',NVL(sl.amount,0),0) ,

DECODE(sl.trx_type,'STOP',NVL(sl.amount,0),0) ,

DECODE(sl.trx_type,'REJECTED',NVL(sl.amount,0),0) ,

DECODE(sl.trx_type,'SWEEP_IN',NVL(sl.amount,0),0) ,

DECODE(sl.trx_type,'SWEEP_OUT',NVL(sl.amount,0),0) ,

sl.status ,

l2.meaning   ,

DECODE(sl.status,'ERROR',

   DECODE(sl.trx_type,'CREDIT',NVL(sl.amount,0),

   'MISC_CREDIT',NVL(sl.amount,0),

   'SWEEP_IN',NVL(sl.amount,0),0),0) ,

DECODE(sl.status,'ERROR',

   DECODE(sl.trx_type,'DEBIT',sl.amount,

   'MISC_DEBIT',NVL(sl.amount,0),

   'SWEEP_OUT',NVL(sl.amount,0),0),0) ,

sl.bank_trx_number ,

sl.charges_amount ,

sh.control_end_balance ,

sh.statement_date

order by 10,14,16,1,15,17,2,38,39,28,29,4,35)

SELECT q1.* from q1

]]>

      </sqlStatement>

      <sqlStatement name="Q_TX">

         <![CDATA[

WITH Q2 AS (

SELECT    'PAYMENT' C_TX_TYPE,

                c.check_id C_TX_ID,

1 C_JE_HEADER_ID,

c.check_date C_TX_RECEIPT_DATE,

l1.meaning C_TX_TYPE_MEANING,

c.status_lookup_code C_TX_STATUS,

l2.displayed_field C_TX_STATUS_MEANING,

to_char(c.check_number) C_TX_NUMBER,

        DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,NVL(c.base_amount,NVL(c.amount,0)), NVL(c.amount,0)) C_TX_BA_AMOUNT,

DECODE(c.status_lookup_code, 'VOIDED', DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,NVL(c.base_amount,NVL(c.amount,0)), NVL(c.amount,0)),'STOP INITIATED', DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,NVL(c.base_amount,NVL(c.amount,0)), NVL(c.amount,0)),DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,NVL(-c.base_amount,NVL(-c.amount,0)), NVL(-c.amount,0))) C_TX_SIGN_BA_AMOUNT,

        DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,DECODE(DECODE(c.status_lookup_code,'CLEARED BUT UNACCOUNTED','RECONCILED','RECONCILED BUT UNACCOUNTED','RECONCILED'),'RECONCILED',NVL(c.cleared_base_amount,c.cleared_amount)),DECODE(DECODE(c.status_lookup_code,'CLEARED BUT UNACCOUNTED','RECONCILED','RECONCILED BUT UNACCOUNTED','RECONCILED'),'RECONCILED',NVL(c.cleared_amount,0)))  C_TX_AMOUNT_CLEARED,

to_number(NULL) C_TX_SIGN_AMOUNT_CLEARED,

DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,NVL(c.cleared_charges_base_amount,NVL(c.cleared_charges_amount,0)),NVL(c.cleared_charges_amount,0))  C_TX_BANK_CHARGES,

DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,NVL(c.cleared_error_base_amount,NVL(c.cleared_error_amount,0)),NVL(c.cleared_error_amount,0)) C_TX_BANK_ERRORS,

NULL C_TX_DR_CR,

--NVL(cs.future_pay_code_combination_id,-1) C_TX_CLEARING_CCID,

NVL(c.exchange_rate,0) C_TX_EXCHANGE_RATE

--added as fix

,CE_CEXSTMRR_XMLP_PKG.c_tx_amount_for2formula('PAYMENT',DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,NVL(c.base_amount,NVL(c.amount,0)), NVL(c.amount,0)),DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,DECODE(DECODE(c.status_lookup_code,'CLEARED BUT UNACCOUNTED','RECONCILED','RECONCILED BUT UNACCOUNTED','RECONCILED'),'RECONCILED',NVL(c.cleared_base_amount,c.cleared_amount)),DECODE(DECODE(c.status_lookup_code,'CLEARED BUT UNACCOUNTED','RECONCILED','RECONCILED BUT UNACCOUNTED','RECONCILED'),'RECONCILED',NVL(c.cleared_amount,0))),:C_SL_TYPE) C_TX_AMOUNT_FOR2

,CE_CEXSTMRR_XMLP_PKG.c_tx_amount_forformula('PAYMENT',:C_SL_TYPE,DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,NVL(c.base_amount,NVL(c.amount,0)), NVL(c.amount,0)),NULL,DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,DECODE(DECODE(c.status_lookup_code,'CLEARED BUT UNACCOUNTED','RECONCILED','RECONCILED BUT UNACCOUNTED','RECONCILED'),'RECONCILED',NVL(c.cleared_base_amount,c.cleared_amount)),DECODE(DECODE(c.status_lookup_code,'CLEARED BUT UNACCOUNTED','RECONCILED','RECONCILED BUT UNACCOUNTED','RECONCILED'),'RECONCILED',NVL(c.cleared_amount,0)))) C_TX_AMOUNT_FOR

,CE_CEXSTMRR_XMLP_PKG.c_tx_sign_amount_forformula('PAYMENT',DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,NVL(-c.base_amount,NVL(-c.amount,0)), NVL(-c.amount,0)))  C_TX_SIGN_AMOUNT_FOR

,CE_CEXSTMRR_XMLP_PKG.c_tx_sign_amount_cleared_forfo('PAYMENT',:C_SL_AMOUNT_CLEARED,to_number(NULL),DECODE(c.status_lookup_code, 'VOIDED', DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,NVL(c.base_amount,NVL(c.amount,0)), NVL(c.amount,0)),'STOP INITIATED', DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,NVL(c.base_amount,NVL(c.amount,0)), NVL(c.amount,0)),DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,NVL(-c.base_amount,NVL(-c.amount,0)), NVL(-c.amount,0))),NULL) C_TX_SIGN_AMOUNT_CLEARED_FOR

,CE_CEXSTMRR_XMLP_PKG.c_tx_amount_cleared_forformula(:C_SL_TYPE,:C_SL_AMOUNT_CLEARED) C_TX_AMOUNT_CLEARED_FOR

,CE_CEXSTMRR_XMLP_PKG.c_tx_bank_charges_forformula(DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,NVL(c.cleared_charges_base_amount,NVL(c.cleared_charges_amount,0)),NVL(c.cleared_charges_amount,0))) C_TX_BANK_CHARGES_FOR

,CE_CEXSTMRR_XMLP_PKG.c_tx_bank_errors_forformula(DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,NVL(c.cleared_error_base_amount,NVL(c.cleared_error_amount,0)),NVL(c.cleared_error_amount,0))) C_TX_BANK_ERRORS_FOR

/*,CE_CEXSTMRR_XMLP_PKG.c_tx_amount_cleared_forformula(:C_SL_TYPE,:C_SL_AMOUNT_CLEARED) C_TX_AMOUNT_CLEARED_FOR

,CE_CEXSTMRR_XMLP_PKG.c_tx_bank_charges_forformula(DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,NVL(c.cleared_charges_base_amount,NVL(c.cleared_charges_amount,0)),NVL(c.cleared_charges_amount,0))) C_TX_BANK_CHARGES_FOR

,CE_CEXSTMRR_XMLP_PKG.c_tx_bank_errors_forformula(DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,NVL(c.cleared_error_base_amount,NVL(c.cleared_error_amount,0)),NVL(c.cleared_error_amount,0))) C_TX_BANK_ERRORS_FOR*/

FROM ap_lookup_codes l2,

ce_lookups l1,

ap_checks c

--ap_check_stocks cs

WHERE --cs.check_stock_id(+) = c.check_stock_id AND

l2.lookup_type = 'CHECK STATE'                    AND

        l2.lookup_code = c.status_lookup_code AND

l1.lookup_type  = 'TRX_TYPE' AND

        l1.lookup_code = 'PAYMENT'

and 'PAYMENT' =:C_TRX_REFERENCE_TYPE

and c.check_id =:C_TRX_REFERENCE_ID

and 1=:C_TRX_JE_HEADER_ID

UNION ALL

SELECT 'RECEIPT' C_TX_TYPE,

                crh.cash_receipt_history_id C_TX_ID,

1 C_JE_HEADER_ID,

cr.receipt_date C_TX_RECEIPT_DATE,

l1.meaning C_TX_TYPE_MEANING,

DECODE(arp_cashbook.receipt_debit_memo_reversed(crh.cash_receipt_id), 'Y',

  DECODE(:C_SL_TYPE, 'CREDIT', crh.status, 'REVERSED'), crh.status) C_TX_STATUS,

l2.meaning C_TX_STATUS_MEANING,

cr.receipt_number C_TX_NUMBER,

                  DECODE(:C_BANK_ACC_CURRENCY, :C_GL_CURRENCY_CODE,crh.acctd_amount,NVL(crh.amount,0)) C_TX_BA_AMOUNT,

DECODE(DECODE(arp_cashbook.receipt_debit_memo_reversed(crh.cash_receipt_id), 'Y',DECODE(:C_SL_TYPE, 'CREDIT', 'CLEARED', 'REVERSED'), crh.status),'REVERSED', DECODE(:C_BANK_ACC_CURRENCY, :C_GL_CURRENCY_CODE,-crh.acctd_amount,NVL(-crh.amount,0)),DECODE(:C_BANK_ACC_CURRENCY, :C_GL_CURRENCY_CODE,crh.acctd_amount,NVL(crh.amount,0))) C_TX_SIGN_BA_AMOUNT,

        DECODE(:C_BANK_ACC_CURRENCY, :C_GL_CURRENCY_CODE,DECODE(DECODE(arp_cashbook.receipt_debit_memo_reversed(crh.cash_receipt_id), 'Y', 'REVERSED', crh.status),'CLEARED',NVL(crh.acctd_amount,0),'REVERSED',NVL(crh.amount,0)),DECODE(DECODE(arp_cashbook.receipt_debit_memo_reversed(crh.cash_receipt_id),'Y', 'REVERSED', crh.status),'CLEARED',NVL(crh.amount,0),'REVERSED',NVL(crh.amount,0)))   C_TX_AMOUNT_CLEARED,

DECODE(:C_BANK_ACC_CURRENCY, :C_GL_CURRENCY_CODE,DECODE(DECODE(arp_cashbook.receipt_debit_memo_reversed(crh.cash_receipt_id), 'Y',DECODE(:C_SL_TYPE, 'CREDIT', 'CLEARED', 'REVERSED'), crh.status),'CLEARED',NVL(crh.acctd_amount,0),'REVERSED',NVL(-crh.acctd_amount,0)),DECODE(DECODE(arp_cashbook.receipt_debit_memo_reversed(crh.cash_receipt_id), 'Y',DECODE(:C_SL_TYPE, 'CREDIT', 'CLEARED', 'REVERSED'), crh.status),'CLEARED',NVL(crh.amount,0),'REVERSED',NVL(-crh.amount,0))) C_TX_SIGN_AMOUNT_CLEARED,

        DECODE(:C_BANK_ACC_CURRENCY, :C_GL_CURRENCY_CODE,

          NVL(crh.acctd_factor_discount_amount,0), NVL(crh.factor_discount_amount,0))

C_TX_BANK_CHARGES,

        to_number(NULL) C_TX_BANK_ERRORS,

NULL C_TX_DR_CR,

--to_number(NULL) C_TX_CLEARING_CCID,

to_number(NULL) C_TX_EXCHANGE_RATE

/*ADDED AS FIX*/

,CE_CEXSTMRR_XMLP_PKG.c_tx_amount_for2formula('RECEIPT',DECODE(:C_BANK_ACC_CURRENCY, :C_GL_CURRENCY_CODE,crh.acctd_amount,NVL(crh.amount,0)),DECODE(:C_BANK_ACC_CURRENCY, :C_GL_CURRENCY_CODE,DECODE(DECODE(arp_cashbook.receipt_debit_memo_reversed(crh.cash_receipt_id), 'Y', 'REVERSED', crh.status),'CLEARED',NVL(crh.acctd_amount,0),'REVERSED',NVL(crh.amount,0)),DECODE(DECODE(arp_cashbook.receipt_debit_memo_reversed(crh.cash_receipt_id),'Y', 'REVERSED', crh.status),'CLEARED',NVL(crh.amount,0),'REVERSED',NVL(crh.amount,0))),:C_SL_TYPE) C_TX_AMOUNT_FOR2

,CE_CEXSTMRR_XMLP_PKG.c_tx_amount_forformula('RECEIPT',:C_SL_TYPE,DECODE(:C_BANK_ACC_CURRENCY, :C_GL_CURRENCY_CODE,crh.acctd_amount,NVL(crh.amount,0)),NULL,DECODE(:C_BANK_ACC_CURRENCY, :C_GL_CURRENCY_CODE,DECODE(DECODE(arp_cashbook.receipt_debit_memo_reversed(crh.cash_receipt_id), 'Y', 'REVERSED', crh.status),'CLEARED',NVL(crh.acctd_amount,0),'REVERSED',NVL(crh.amount,0)),DECODE(DECODE(arp_cashbook.receipt_debit_memo_reversed(crh.cash_receipt_id),'Y', 'REVERSED', crh.status),'CLEARED',NVL(crh.amount,0),'REVERSED',NVL(crh.amount,0)))) C_TX_AMOUNT_FOR

,CE_CEXSTMRR_XMLP_PKG.c_tx_sign_amount_forformula('RECEIPT',DECODE(DECODE(arp_cashbook.receipt_debit_memo_reversed(crh.cash_receipt_id), 'Y',

  DECODE(:C_SL_TYPE, 'CREDIT', 'CLEARED', 'REVERSED'), crh.status),

  'REVERSED', DECODE(:C_BANK_ACC_CURRENCY, :C_GL_CURRENCY_CODE,

   -crh.acctd_amount,NVL(-crh.amount,0)),

     DECODE(:C_BANK_ACC_CURRENCY, :C_GL_CURRENCY_CODE,

                         crh.acctd_amount,NVL(crh.amount,0)))) C_TX_SIGN_AMOUNT_FOR

,CE_CEXSTMRR_XMLP_PKG.c_tx_sign_amount_cleared_forfo('RECEIPT',:C_SL_AMOUNT_CLEARED,DECODE(:C_BANK_ACC_CURRENCY, :C_GL_CURRENCY_CODE,DECODE(DECODE(arp_cashbook.receipt_debit_memo_reversed(crh.cash_receipt_id), 'Y',DECODE(:C_SL_TYPE, 'CREDIT', 'CLEARED', 'REVERSED'), crh.status),'CLEARED',NVL(crh.acctd_amount,0),'REVERSED',NVL(-crh.acctd_amount,0)),DECODE(DECODE(arp_cashbook.receipt_debit_memo_reversed(crh.cash_receipt_id), 'Y',DECODE(:C_SL_TYPE, 'CREDIT', 'CLEARED', 'REVERSED'), crh.status),'CLEARED',NVL(crh.amount,0),'REVERSED',NVL(-crh.amount,0))),DECODE(DECODE(arp_cashbook.receipt_debit_memo_reversed(crh.cash_receipt_id), 'Y',DECODE(:C_SL_TYPE, 'CREDIT', 'CLEARED', 'REVERSED'), crh.status),'REVERSED', DECODE(:C_BANK_ACC_CURRENCY, :C_GL_CURRENCY_CODE,-crh.acctd_amount,NVL(-crh.amount,0)),DECODE(:C_BANK_ACC_CURRENCY, :C_GL_CURRENCY_CODE,crh.acctd_amount,NVL(crh.amount,0))),NULL) C_TX_SIGN_AMOUNT_CLEARED_FOR

,CE_CEXSTMRR_XMLP_PKG.c_tx_amount_cleared_forformula(:C_SL_TYPE,:C_SL_AMOUNT_CLEARED) C_TX_AMOUNT_CLEARED_FOR

,CE_CEXSTMRR_XMLP_PKG.c_tx_bank_charges_forformula(DECODE(:C_BANK_ACC_CURRENCY, :C_GL_CURRENCY_CODE,

          NVL(crh.acctd_factor_discount_amount,0), NVL(crh.factor_discount_amount,0)))  C_TX_BANK_CHARGES_FOR

,CE_CEXSTMRR_XMLP_PKG.c_tx_bank_errors_forformula(to_number(NULL)) C_TX_BANK_ERRORS

FROM  ar_lookups l2,

ce_lookups l1,

ar_cash_receipts cr,

ar_cash_receipt_history_all crh

WHERE     l2.lookup_type = 'RECEIPT_CREATION_STATUS' AND

        l2.lookup_code =

                           DECODE(arp_cashbook.receipt_debit_memo_reversed(crh.cash_receipt_id), 'Y',

            DECODE(:C_SL_TYPE, 'CREDIT', crh.status, 'REVERSED'), crh.status) AND

l1.lookup_type = 'TRX_TYPE' AND

        l1.lookup_code(+) = cr.type  AND

cr.cash_receipt_id = crh.cash_receipt_id

and 'RECEIPT' =:C_TRX_REFERENCE_TYPE

and crh.cash_receipt_history_id =:C_TRX_REFERENCE_ID

and 1=:C_TRX_JE_HEADER_ID

UNION ALL

SELECT 'JE_LINE' C_TX_TYPE,

                jel.je_line_num C_TX_ID,

                  jel.je_header_id C_JE_HEADER_ID,

jel.effective_date C_TX_RECEIPT_DATE,

l1.meaning C_TX_TYPE_MEANING,

jel.status C_TX_STATUS,

l2.meaning C_TX_STATUS_MEANING,

jel.description C_TX_NUMBER,

        DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,DECODE(NVL(jel.accounted_dr,0),0,jel.accounted_cr,jel.accounted_dr),DECODE(NVL(jel.entered_dr,0),0,jel.entered_cr,jel.entered_dr)) C_TX_BA_AMOUNT,

DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,

DECODE(NVL(jel.accounted_dr,0),0,-1*jel.accounted_cr,jel.accounted_dr),

                               DECODE(NVL(jel.entered_dr,0),0,-1*jel.entered_cr,jel.entered_dr))

C_TX_SIGN_BA_AMOUNT,

        DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,

DECODE(NVL(jel.accounted_dr,0),0,jel.accounted_cr,jel.accounted_dr),DECODE(NVL(jel.entered_dr,0),0,jel.entered_cr,jel.entered_dr)) C_TX_AMOUNT_CLEARED,

to_number(NULL) C_TX_SIGN_AMOUNT_CLEARED,

to_number(NULL) C_TX_BANK_CHARGES,

to_number(NULL) C_TX_BANK_ERRORS,

DECODE(DECODE(NVL(jel.entered_dr,0),0,NVL(jel.accounted_dr,0), jel.entered_dr), 0,'CR','DR')

C_TX_DR_CR,

--to_number(NULL) C_TX_CLEARING_CCID,

to_number(NULL) C_TX_EXCHANGE_RATE

--add as fix

,CE_CEXSTMRR_XMLP_PKG.c_tx_amount_for2formula('JE_LINE',DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,DECODE(NVL(jel.accounted_dr,0),0,jel.accounted_cr,jel.accounted_dr),DECODE(NVL(jel.entered_dr,0),0,jel.entered_cr,jel.entered_dr)),DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,

DECODE(NVL(jel.accounted_dr,0),0,jel.accounted_cr,jel.accounted_dr),DECODE(NVL(jel.entered_dr,0),0,jel.entered_cr,jel.entered_dr)),:C_SL_TYPE) C_TX_AMOUNT_FOR2

,CE_CEXSTMRR_XMLP_PKG.c_tx_amount_forformula('JE_LINE',:C_SL_TYPE,DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,DECODE(NVL(jel.accounted_dr,0),0,jel.accounted_cr,jel.accounted_dr),DECODE(NVL(jel.entered_dr,0),0,jel.entered_cr,jel.entered_dr)),null,DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,

DECODE(NVL(jel.accounted_dr,0),0,jel.accounted_cr,jel.accounted_dr),DECODE(NVL(jel.entered_dr,0),0,jel.entered_cr,jel.entered_dr))) C_TX_AMOUNT_FOR

,CE_CEXSTMRR_XMLP_PKG.c_tx_sign_amount_forformula('JE_LINE',DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,

DECODE(NVL(jel.accounted_dr,0),0,-1*jel.accounted_cr,jel.accounted_dr),

                               DECODE(NVL(jel.entered_dr,0),0,-1*jel.entered_cr,jel.entered_dr)))

,CE_CEXSTMRR_XMLP_PKG.c_tx_sign_amount_cleared_forfo('JE_LINE',:C_SL_AMOUNT_CLEARED,to_number(NULL),DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,

DECODE(NVL(jel.accounted_dr,0),0,-1*jel.accounted_cr,jel.accounted_dr),

                               DECODE(NVL(jel.entered_dr,0),0,-1*jel.entered_cr,jel.entered_dr)),NULL) C_TX_SIGN_AMOUNT_CLEARED_FOR

,CE_CEXSTMRR_XMLP_PKG.c_tx_amount_cleared_forformula(:C_SL_TYPE,:C_SL_AMOUNT_CLEARED) C_TX_AMOUNT_CLEARED_FOR

,CE_CEXSTMRR_XMLP_PKG.c_tx_bank_charges_forformula(to_number(NULL)) C_TX_BANK_CHARGES_FOR

,CE_CEXSTMRR_XMLP_PKG.c_tx_bank_errors_forformula(to_number(NULL)) C_TX_BANK_ERRORS_FOR

FROM gl_lookups l2,

ce_lookups l1,

gl_je_headers jeh,

gl_je_lines jel

WHERE l2.lookup_type = 'MJE_BATCH_STATUS' AND

l2.lookup_code = jel.status AND

l1.lookup_type  = 'TRX_TYPE' AND

        l1.lookup_code =  DECODE(NVL(jel.entered_dr,0),0,'JE_CREDIT','JE_DEBIT')   AND

jeh.je_header_id = jel.je_header_id

and 'JE_LINE' =:C_TRX_REFERENCE_TYPE

and jel.je_line_num =:C_TRX_REFERENCE_ID

and jel.je_header_id=:C_TRX_JE_HEADER_ID

UNION ALL

SELECT 'ROI_LINE' C_TX_TYPE,

                roi.trx_id C_TX_ID,

              1 C_JE_HEADER_ID,

roi.trx_date C_TX_RECEIPT_DATE,

roi.trx_type_dsp C_TX_TYPE_MEANING,

roi.status C_TX_STATUS,

roi.status_dsp C_TX_STATUS_MEANING,

roi.trx_number C_TX_NUMBER,

DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,NVL(roi.acctd_amount,roi.amount),roi.amount) C_TX_BA_AMOUNT,

DECODE(roi.trx_type, 'PAYMENT',DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,NVL(-roi.acctd_amount,-roi.amount),-roi.amount), DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,NVL(roi.acctd_amount,roi.amount),roi.amount) ) C_TX_SIGN_BA_AMOUNT,

        DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,NVL(roi.acctd_amount,roi.amount),roi.amount) C_TX_AMOUNT_CLEARED,

to_number(NULL) C_TX_SIGN_AMOUNT_CLEARED,

DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,roi.acctd_charges_amount,roi.charges_amount) C_TX_BANK_CHARGES,

DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,roi.acctd_error_amount,roi.error_amount) C_TX_BANK_ERRORS,

roi.trx_type C_TX_DR_CR,

--to_number(NULL) C_TX_CLEARING_CCID,

to_number(NULL) C_TX_EXCHANGE_RATE

--ADDED AS FIX

,CE_CEXSTMRR_XMLP_PKG.c_tx_amount_for2formula('ROI_LINE',DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,NVL(roi.acctd_amount,roi.amount),roi.amount),DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,NVL(roi.acctd_amount,roi.amount),roi.amount),:C_SL_TYPE) C_TX_AMOUNT_FOR2

,CE_CEXSTMRR_XMLP_PKG.c_tx_amount_forformula('ROI_LINE',:C_SL_TYPE,DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,NVL(roi.acctd_amount,roi.amount),roi.amount),roi.trx_type,DECODE(roi.trx_type, 'PAYMENT',DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,NVL(-roi.acctd_amount,-roi.amount),-roi.amount), DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,NVL(roi.acctd_amount,roi.amount),roi.amount))) C_TX_AMOUNT_FOR

,CE_CEXSTMRR_XMLP_PKG.c_tx_sign_amount_forformula('ROI_LINE',DECODE(roi.trx_type, 'PAYMENT',DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,NVL(-roi.acctd_amount,-roi.amount),-roi.amount), DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,NVL(roi.acctd_amount,roi.amount),roi.amount) )) C_TX_SIGN_AMOUNT_FOR

,CE_CEXSTMRR_XMLP_PKG.c_tx_sign_amount_cleared_forfo('ROI_LINE',:C_SL_AMOUNT_CLEARED,to_number(NULL),DECODE(roi.trx_type, 'PAYMENT',DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,NVL(-roi.acctd_amount,-roi.amount),-roi.amount), DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,NVL(roi.acctd_amount,roi.amount),roi.amount)),roi.trx_type) C_TX_SIGN_AMOUNT_CLEARED_FOR

,CE_CEXSTMRR_XMLP_PKG.c_tx_amount_cleared_forformula(:C_SL_TYPE,:C_SL_AMOUNT_CLEARED) C_TX_AMOUNT_CLEARED_FOR

,CE_CEXSTMRR_XMLP_PKG.c_tx_bank_charges_forformula(DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,roi.acctd_charges_amount,roi.charges_amount)) C_TX_BANK_CHARGES_FOR

,CE_CEXSTMRR_XMLP_PKG.c_tx_bank_errors_forformula(DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,roi.acctd_error_amount,roi.error_amount)) C_TX_BANK_ERRORS_FOR

FROM CE_999_INTERFACE_V roi

where 'ROI_LINE' =:C_TRX_REFERENCE_TYPE

and roi.trx_id =:C_TRX_REFERENCE_ID

and 1=:C_TRX_JE_HEADER_ID

UNION ALL

SELECT 'XTR_LINE' C_TX_TYPE,

                xtr.trx_id C_TX_ID,

              1 C_JE_HEADER_ID,

xtr.trx_date C_TX_RECEIPT_DATE,

xtr.type_meaning C_TX_TYPE_MEANING,

xtr.status C_TX_STATUS,

xtr.status_dsp C_TX_STATUS_MEANING,

xtr.trx_number C_TX_NUMBER,

DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,NVL(xtr.bank_account_amount,xtr.amount),xtr.amount) C_TX_BA_AMOUNT,

DECODE(xtr.trx_type, 'PAYMENT',DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,NVL(-xtr.bank_account_amount,-xtr.amount),-xtr.amount), DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,NVL(xtr.bank_account_amount,xtr.amount),xtr.amount) ) C_TX_SIGN_BA_AMOUNT,

        DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,NVL(xtr.bank_account_amount,xtr.amount),xtr.amount) C_TX_AMOUNT_CLEARED,

to_number(NULL) C_TX_SIGN_AMOUNT_CLEARED,

DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,xtr.bank_charges,xtr.bank_charges) C_TX_BANK_CHARGES,

DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,xtr.bank_errors,xtr.bank_errors) C_TX_BANK_ERRORS,

xtr.trx_type C_TX_DR_CR,

--to_number(NULL) C_TX_CLEARING_CCID,

to_number(NULL) C_TX_EXCHANGE_RATE

,CE_CEXSTMRR_XMLP_PKG.c_tx_amount_for2formula('XTR_LINE',DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,NVL(xtr.bank_account_amount,xtr.amount),xtr.amount),DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,NVL(xtr.bank_account_amount,xtr.amount),xtr.amount),:C_SL_TYPE) C_TX_AMOUNT_FOR2

,CE_CEXSTMRR_XMLP_PKG.c_tx_amount_forformula('XTR_LINE',:C_SL_TYPE,DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,NVL(xtr.bank_account_amount,xtr.amount),xtr.amount),xtr.trx_type,DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,NVL(xtr.bank_account_amount,xtr.amount),xtr.amount)) C_TX_AMOUNT_FOR

,CE_CEXSTMRR_XMLP_PKG.c_tx_sign_amount_forformula('XTR_LINE',DECODE(xtr.trx_type, 'PAYMENT',DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,NVL(-xtr.bank_account_amount,-xtr.amount),-xtr.amount), DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,NVL(xtr.bank_account_amount,xtr.amount),xtr.amount) )) C_TX_SIGN_AMOUNT_FOR

,CE_CEXSTMRR_XMLP_PKG.c_tx_sign_amount_cleared_forfo('XTR_LINE',:C_SL_AMOUNT_CLEARED,to_number(NULL),DECODE(xtr.trx_type, 'PAYMENT',DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,NVL(-xtr.bank_account_amount,-xtr.amount),-xtr.amount), DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,NVL(xtr.bank_account_amount,xtr.amount),xtr.amount) ),xtr.trx_type) C_TX_SIGN_AMOUNT_CLEARED_FOR

,CE_CEXSTMRR_XMLP_PKG.c_tx_amount_cleared_forformula(:C_SL_TYPE,:C_SL_AMOUNT_CLEARED) C_TX_AMOUNT_CLEARED_FOR

,CE_CEXSTMRR_XMLP_PKG.c_tx_bank_charges_forformula(DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,xtr.bank_charges,xtr.bank_charges)) C_TX_BANK_CHARGES_FOR

,CE_CEXSTMRR_XMLP_PKG.c_tx_bank_errors_forformula(DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,xtr.bank_errors,xtr.bank_errors)) C_TX_BANK_ERRORS_FOR

FROM CE_185_reconciled_V xtr

where 'XTR_LINE'=:C_TRX_REFERENCE_TYPE

and xtr.trx_id =:C_TRX_REFERENCE_ID

and 1=:C_TRX_JE_HEADER_ID

UNION ALL

SELECT 'PAY' C_TX_TYPE,

                c.trx_id C_TX_ID,

              1 C_JE_HEADER_ID,

c.trx_date C_TX_RECEIPT_DATE,

c.type_meaning C_TX_TYPE_MEANING,

c.status C_TX_STATUS,

c.status_dsp C_TX_STATUS_MEANING,

c.trx_number C_TX_NUMBER,

DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,c.bank_account_amount, c.amount)

C_TX_BA_AMOUNT,

        DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,-c.bank_account_amount, -c.amount)

C_TX_SIGN_BA_AMOUNT,

        DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,c.amount_cleared, c.actual_amount_cleared)

C_TX_AMOUNT_CLEARED,

                  to_number(NULL) C_TX_SIGN_AMOUNT_CLEARED,

to_number(NULL) C_TX_BANK_CHARGES,

to_number(NULL) C_TX_BANK_ERRORS,

c.trx_type C_TX_DR_CR,

--to_number(NULL) C_TX_CLEARING_CCID,

to_number(NULL) C_TX_EXCHANGE_RATE

--added as fix

,CE_CEXSTMRR_XMLP_PKG.c_tx_amount_for2formula('PAY',DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,c.bank_account_amount, c.amount),DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,c.amount_cleared, c.actual_amount_cleared),:C_SL_TYPE) C_TX_AMOUNT_FOR2

    ,CE_CEXSTMRR_XMLP_PKG.c_tx_amount_forformula('PAY',:C_SL_TYPE,DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,c.bank_account_amount, c.amount),c.trx_type,DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,c.amount_cleared, c.actual_amount_cleared)) C_TX_AMOUNT_FOR

    ,CE_CEXSTMRR_XMLP_PKG.c_tx_sign_amount_forformula('PAY',DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,-c.bank_account_amount, -c.amount)) C_TX_SIGN_AMOUNT_FOR

    ,CE_CEXSTMRR_XMLP_PKG.c_tx_sign_amount_cleared_forfo('PAY',:C_SL_AMOUNT_CLEARED,to_number(NULL),DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,-c.bank_account_amount, -c.amount),c.trx_type)  C_TX_SIGN_AMOUNT_CLEARED_FOR

    ,CE_CEXSTMRR_XMLP_PKG.c_tx_amount_cleared_forformula(:C_SL_TYPE,:C_SL_AMOUNT_CLEARED) C_TX_AMOUNT_CLEARED_FOR

    ,CE_CEXSTMRR_XMLP_PKG.c_tx_bank_charges_forformula(to_number(NULL)) C_TX_BANK_CHARGES_FOR

    ,CE_CEXSTMRR_XMLP_PKG.c_tx_bank_errors_forformula(to_number(NULL)) C_TX_BANK_ERRORS_FOR

FROM CE_801_RECONCILED_V c

where 'PAY'=:C_TRX_REFERENCE_TYPE

and c.trx_id =:C_TRX_REFERENCE_ID

and 1=:C_TRX_JE_HEADER_ID

UNION ALL

SELECT 'PAY_EFT' C_TX_TYPE,

                c.trx_id C_TX_ID,

              1 C_JE_HEADER_ID,

c.trx_date C_TX_RECEIPT_DATE,

c.type_meaning C_TX_TYPE_MEANING,

c.status C_TX_STATUS,

c.status_dsp C_TX_STATUS_MEANING,

c.trx_number C_TX_NUMBER,

DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,c.bank_account_amount, c.amount)

C_TX_BA_AMOUNT,

        DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,-c.bank_account_amount, -c.amount)

C_TX_SIGN_BA_AMOUNT,

        DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,c.amount_cleared, c.actual_amount_cleared)

C_TX_AMOUNT_CLEARED,

                  to_number(NULL) C_TX_SIGN_AMOUNT_CLEARED,

to_number(NULL) C_TX_BANK_CHARGES,

to_number(NULL) C_TX_BANK_ERRORS,

c.trx_type C_TX_DR_CR,

--to_number(NULL) C_TX_CLEARING_CCID,

to_number(NULL) C_TX_EXCHANGE_RATE

,CE_CEXSTMRR_XMLP_PKG.c_tx_amount_for2formula('PAY_EFT',DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,c.bank_account_amount, c.amount),DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,c.amount_cleared, c.actual_amount_cleared),:C_SL_TYPE) C_TX_AMOUNT_FOR2

    ,CE_CEXSTMRR_XMLP_PKG.c_tx_amount_forformula('PAY_EFT',:C_SL_TYPE,DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,c.bank_account_amount, c.amount),c.trx_type,DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,c.amount_cleared, c.actual_amount_cleared)) C_TX_AMOUNT_FOR

    ,CE_CEXSTMRR_XMLP_PKG.c_tx_sign_amount_forformula('PAY_EFT',DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,-c.bank_account_amount, -c.amount)) C_TX_SIGN_AMOUNT_FOR

    ,CE_CEXSTMRR_XMLP_PKG.c_tx_sign_amount_cleared_forfo('PAY_EFT',:C_SL_AMOUNT_CLEARED,to_number(NULL),DECODE(:C_BANK_ACC_CURRENCY,:C_GL_CURRENCY_CODE,-c.bank_account_amount, -c.amount),c.trx_type) C_TX_SIGN_AMOUNT_CLEARED_FOR

    ,CE_CEXSTMRR_XMLP_PKG.c_tx_amount_cleared_forformula(:C_SL_TYPE, :C_SL_AMOUNT_CLEARED) C_TX_AMOUNT_CLEARED_FOR

    ,CE_CEXSTMRR_XMLP_PKG.c_tx_bank_charges_forformula(to_number(NULL)) C_TX_BANK_CHARGES_FOR

    ,CE_CEXSTMRR_XMLP_PKG.c_tx_bank_errors_forformula(to_number(NULL)) C_TX_BANK_ERRORS_FOR

FROM CE_801_EFT_RECONCILED_V c

where 'PAY_EFT'=:C_TRX_REFERENCE_TYPE

and c.trx_id =:C_TRX_REFERENCE_ID

and 1=:C_TRX_JE_HEADER_ID

UNION ALL

SELECT 'STATEMENT' C_TX_TYPE,

sr.trx_id C_TX_ID,

1 C_JE_HEADER_ID,

sr.receipt_date C_TX_RECEIPT_DATE,

l1.meaning C_TX_TYPE_MEANING,

sr.status C_TX_STATUS,

sr.status_dsp C_TX_STATUS_MEANING,

sr.trx_number C_TX_NUMBER,

DECODE(sr.line_trx_type, 'MISC_DEBIT', DECODE(sr.trx_type, 'CREDIT', NVL(-sr.amount,0),

            'MISC_CREDIT', NVL(sr.amount,0),

            NVL(sr.amount,0)),

      'MISC_CREDIT', DECODE(sr.trx_type, 'DEBIT', NVL(-sr.amount,0),

              NVL(sr.amount,0)),

       NVL(sr.amount,0)) C_TX_BA_AMOUNT,

DECODE(sr.line_trx_type, 'MISC_DEBIT', DECODE(sr.trx_type, 'CREDIT', NVL(sr.amount,0),

            'MISC_CREDIT', NVL(-sr.amount,0),

            NVL(-sr.amount,0)),

       'MISC_CREDIT', DECODE(sr.trx_type, 'DEBIT', NVL(sr.amount,0),

            NVL(-sr.amount,0)),

       NVL(-sr.amount,0)) C_TX_SIGN_BA_AMOUNT,

DECODE(sr.line_trx_type, 'MISC_DEBIT', DECODE(sr.trx_type, 'CREDIT', NVL(-sr.amount_cleared,0),

            'MISC_CREDIT', NVL(sr.amount_cleared,0),

            NVL(sr.amount_cleared,0)),

       'MISC_CREDIT', DECODE(sr.trx_type, 'DEBIT', NVL(-sr.amount_cleared,0),

              NVL(sr.amount_cleared,0)),

       NVL(sr.amount_cleared,0)) C_TX_AMOUNT_CLEARED,

DECODE(sr.line_trx_type, 'MISC_DEBIT', DECODE(sr.trx_type, 'CREDIT', NVL(sr.amount_cleared,0),

            'MISC_CREDIT', NVL(-sr.amount_cleared,0),

            NVL(-sr.amount_cleared,0)),

       'MISC_CREDIT', DECODE(sr.trx_type, 'DEBIT', NVL(sr.amount_cleared,0),

              NVL(-sr.amount_cleared,0)),

       NVL(-sr.amount_cleared,0)) C_TX_SIGN_AMOUNT_CLEARED,

sr.bank_charges C_TX_BANK_CHARGES,

to_number(NULL) C_TX_BANK_ERRORS,

DECODE(sr.trx_type, 'CREDIT', 'CR',

                 'MISC_CREDIT', 'MC',

                 'DEBIT', 'DR',

                 'MISC_DEBIT',  'MD',

                 NULL) C_TX_DR_CR,

--to_number(NULL) C_TX_CLEARING_CCID,

to_number(NULL) C_TX_EXCHANGE_RATE

--add as fix

,CE_CEXSTMRR_XMLP_PKG.c_tx_amount_for2formula('STATEMENT',DECODE(sr.line_trx_type, 'MISC_DEBIT', DECODE(sr.trx_type, 'CREDIT', NVL(-sr.amount,0),

            'MISC_CREDIT', NVL(sr.amount,0),

            NVL(sr.amount,0)),

      'MISC_CREDIT', DECODE(sr.trx_type, 'DEBIT', NVL(-sr.amount,0),

              NVL(sr.amount,0)),

       NVL(sr.amount,0)),DECODE(sr.line_trx_type, 'MISC_DEBIT', DECODE(sr.trx_type, 'CREDIT', NVL(-sr.amount_cleared,0),

            'MISC_CREDIT', NVL(sr.amount_cleared,0),

            NVL(sr.amount_cleared,0)),

       'MISC_CREDIT', DECODE(sr.trx_type, 'DEBIT', NVL(-sr.amount_cleared,0),

              NVL(sr.amount_cleared,0)),

       NVL(sr.amount_cleared,0)),:C_SL_TYPE) C_TX_AMOUNT_FOR2

,CE_CEXSTMRR_XMLP_PKG.c_tx_amount_forformula('STATEMENT',:C_SL_TYPE,DECODE(sr.line_trx_type, 'MISC_DEBIT', DECODE(sr.trx_type, 'CREDIT', NVL(-sr.amount,0),

            'MISC_CREDIT', NVL(sr.amount,0),

            NVL(sr.amount,0)),

      'MISC_CREDIT', DECODE(sr.trx_type, 'DEBIT', NVL(-sr.amount,0),

              NVL(sr.amount,0)),

       NVL(sr.amount,0)),DECODE(sr.trx_type, 'CREDIT', 'CR',

                 'MISC_CREDIT', 'MC',

                 'DEBIT', 'DR',

                 'MISC_DEBIT',  'MD',

                 NULL),DECODE(sr.line_trx_type, 'MISC_DEBIT', DECODE(sr.trx_type, 'CREDIT', NVL(-sr.amount_cleared,0),

            'MISC_CREDIT', NVL(sr.amount_cleared,0),

            NVL(sr.amount_cleared,0)),

       'MISC_CREDIT', DECODE(sr.trx_type, 'DEBIT', NVL(-sr.amount_cleared,0),

              NVL(sr.amount_cleared,0)),

       NVL(sr.amount_cleared,0))) C_TX_AMOUNT_FOR

,CE_CEXSTMRR_XMLP_PKG.c_tx_sign_amount_forformula('STATEMENT',DECODE(sr.line_trx_type, 'MISC_DEBIT', DECODE(sr.trx_type, 'CREDIT', NVL(sr.amount,0),

            'MISC_CREDIT', NVL(-sr.amount,0),

            NVL(-sr.amount,0)),

       'MISC_CREDIT', DECODE(sr.trx_type, 'DEBIT', NVL(sr.amount,0),

            NVL(-sr.amount,0)),

       NVL(-sr.amount,0))) C_TX_SIGN_AMOUNT_FOR

,CE_CEXSTMRR_XMLP_PKG.c_tx_sign_amount_cleared_forfo('STATEMENT',:C_SL_AMOUNT_CLEARED,DECODE(sr.line_trx_type, 'MISC_DEBIT', DECODE(sr.trx_type, 'CREDIT', NVL(sr.amount_cleared,0),

            'MISC_CREDIT', NVL(-sr.amount_cleared,0),

            NVL(-sr.amount_cleared,0)),

       'MISC_CREDIT', DECODE(sr.trx_type, 'DEBIT', NVL(sr.amount_cleared,0),

              NVL(-sr.amount_cleared,0)),

       NVL(-sr.amount_cleared,0)), DECODE(sr.line_trx_type, 'MISC_DEBIT', DECODE(sr.trx_type, 'CREDIT', NVL(sr.amount,0),

            'MISC_CREDIT', NVL(-sr.amount,0),

            NVL(-sr.amount,0)),

       'MISC_CREDIT', DECODE(sr.trx_type, 'DEBIT', NVL(sr.amount,0),

            NVL(-sr.amount,0)),

       NVL(-sr.amount,0)),DECODE(sr.trx_type, 'CREDIT', 'CR',

                 'MISC_CREDIT', 'MC',

                 'DEBIT', 'DR',

                 'MISC_DEBIT',  'MD',

                 NULL)) C_TX_SIGN_AMOUNT_CLEARED_FOR

,CE_CEXSTMRR_XMLP_PKG.c_tx_amount_cleared_forformula(:C_SL_TYPE,:C_SL_AMOUNT_CLEARED) C_TX_AMOUNT_CLEARED_FOR

,CE_CEXSTMRR_XMLP_PKG.c_tx_bank_charges_forformula(sr.bank_charges) C_TX_BANK_CHARGES_FOR

,CE_CEXSTMRR_XMLP_PKG.c_tx_bank_errors_forformula(to_number(NULL)) C_TX_BANK_ERRORS_FOR

FROM CE_260_RECONCILED_V sr,

CE_LOOKUPS l1

WHERE l1.lookup_type  = 'BANK_TRX_TYPE' AND

        l1.lookup_code = sr.clearing_trx_type

AND  sr.statement_line_id = :C_STAT_LINE_ID

and 'STATEMENT'=:C_TRX_REFERENCE_TYPE

and sr.trx_id =:C_TRX_REFERENCE_ID

and 1=:C_TRX_JE_HEADER_ID )

 select Q2.* from Q2

]]>

      </sqlStatement>

      <sqlStatement name="Q_CRE">

         <![CDATA[

WITH Q3 AS

(select

decode (:C_SL_TYPE, 'MISC_CREDIT' , DECODE (:C_TRX_REFERENCE_TYPE , 'PAYMENT' , nvl ( - sr.amount , 0 ) , nvl ( sr.amount , 0 ) ) ,'RECEIPT' , DECODE ( :C_TRX_REFERENCE_TYPE , 'PAYMENT' , nvl ( - sr.amount , 0 ) , nvl ( sr.amount , 0 ) ) ,'CREDIT' , DECODE ( :C_TRX_REFERENCE_TYPE , 'PAYMENT' , nvl ( - sr.amount , 0 ) , nvl ( sr.amount , 0 ) ) ,'MISC_DEBIT' , DECODE ( :C_TRX_REFERENCE_TYPE , 'RECEIPT' , nvl ( - sr.amount , 0 ) , nvl ( sr.amount , 0 ) ) ,'PAYMENT' , DECODE ( :C_TRX_REFERENCE_TYPE , 'RECEIPT' , nvl ( - sr.amount , 0 ) , nvl ( sr.amount , 0 ) ) ,'DEBIT' , DECODE ( :C_TRX_REFERENCE_TYPE , 'RECEIPT' , nvl ( - sr.amount , 0 ) , nvl ( sr.amount , 0 ) ) ,nvl ( sr.amount , 0 ) ) C_SL_AMOUNT_CLEARED,

DECODE(sr.reference_type, 'DM REVERSAL', 'RECEIPT', sr.reference_type) C_TRX_REFERENCE_TYPE,

sr.reference_id C_TRX_REFERENCE_ID,

NVL(sr.je_header_id,1) C_TRX_JE_HEADER_ID,

sr.statement_line_id C_TRX_STMT_LN_ID,

gl.set_of_books_id C_SET_OF_BOOKS_ID,

gl.currency_code C_GL_CURRENCY_CODE

FROM  ce_statement_reconcils_all sr , ce_system_parameters sys,

ce_statement_lines sl,

ce_statement_headers sh,

ce_bank_accounts ba,

gl_sets_of_books gl

where sr.status_flag = 'M'

and sr.current_record_flag = 'Y'

and sr.statement_line_id = sl.statement_line_id

and sl.statement_header_id = sh.statement_header_id

and sh.bank_account_id = ba.bank_account_id

and ba.account_owner_org_id = sys.legal_entity_id

and gl.set_of_books_id = sys.set_of_books_id

 and sr.statement_line_id=:C_STAT_LINE_ID)

 select Q3.* from Q3

 ]]>

      </sqlStatement>

   </dataQuery>

   <dataTrigger name="beforeReportTrigger" source="CE_CEXSTMRR_XMLP_PKG.beforereport"/>

   <dataStructure>

      <element name="P_CONC_REQUEST_ID" dataType="number" value="CE_CEXSTMRR_XMLP_PKG.P_CONC_REQUEST_ID"/>

      <element name="P_STAT_DATE_FROM_T" dataType="varchar2" value="CE_CEXSTMRR_XMLP_PKG.P_STAT_DATE_FROM_T"/>

      <element name="P_STAT_DATE_TO_T" dataType="varchar2" value="CE_CEXSTMRR_XMLP_PKG.P_STAT_DATE_TO_T"/>

      <group name="G_BANK_ACCOUNT" dataType="varchar2" source="Q_STATEMENT_LINES" groupFilter="CE_CEXSTMRR_XMLP_PKG.g_bank_accountgroupfilter(:G_BANK_ACCOUNT.C_BANK_ACCOUNT_NAME)">

         <element name="C_BANK_ACCOUNT_NUM" dataType="varchar2" value="C_BANK_ACCOUNT_NUM"/>

         <element name="C_BANK_BRANCH_NAME" dataType="varchar2" value="C_BANK_BRANCH_NAME"/>

         <element name="C_BANK_NAME" dataType="varchar2" value="C_BANK_NAME"/>

         <element name="C_BANK_ACCOUNT_NAME" dataType="varchar2" value="C_BANK_ACCOUNT_NAME"/>

         <element name="C_BANK_ACC_CURRENCY" dataType="varchar2" value="C_BANK_ACC_CURRENCY"/>

         <element name="C_BANK_ACCOUNT_ID" dataType="number" value="C_BANK_ACCOUNT_ID"/>

         <group name="G_STATEMENT" dataType="varchar2" source="Q_STATEMENT_LINES">

            <element name="C_ORDERING" dataType="number" value="C_ORDERING"/>

            <element name="C_STAT_NUMBER" dataType="varchar2" value="C_STAT_NUMBER"/>

            <element name="C_STAT_STATEMENT_DATE" dataType="date" value="C_STAT_STATEMENT_DATE"/>

            <element name="C_STAT_DOC_SEQ" dataType="number" value="C_STAT_DOC_SEQ"/>

            <element name="C_STAT_HEADER_ID1" dataType="number" value="C_STAT_HEADER_ID"/>

            <element name="C_STAT_CONTROL_END1" dataType="number" value="C_STAT_CONTROL_END"/>

            <element name="C_STAT_CR_ERRORS" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_CR_ERRORS"/>

            <element name="C_STAT_CR_ERRORS_DISP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_STAT_CR_ERRORS" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

            <element name="C_STAT_DR_ERRORS_DISP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_STAT_DR_ERRORS" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

            <element name="C_STAT_COUNT_TRX" function="count" dataType="number" value="G_C_SL_AMOUNT_CLEARED.C_TRX_REFERENCE_TYPE1"/>

            <element name="C_STAT_SUM_LINE_AMOUNT" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_SIGNED_AMOUNT"/>

            <element name="C_STAT_UNREC_SUM_LINE" function="sum" dataType="number" value="G_STAT_TYPE.C_TYPE_UNREC_SUM_LINE"/>

            <element name="C_STAT_DR_ERRORS" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_DR_ERRORS"/>

            <element name="C_STAT_LINE_AMOUNT_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_STAT_SUM_LINE_AMOUNT" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

            <element name="C_STAT_SUM_REC_LINE_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_STAT_SUM_REC_LINE" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

            <element name="C_STAT_UNREC_SUM_LINE_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_STAT_UNREC_SUM_LINE" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

            <element name="C_STAT_DR" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_DR2"/>

            <element name="C_STAT_SUM_REC_LINE" dataType="number" value="C_STAT_SUM_REC_LINE"/>

            <element name="C_STAT_DR_MISC" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_MISC_DR2"/>

            <element name="C_STAT_CR" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_CR2"/>

            <element name="C_STAT_CR_MISC" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_MISC_CR2"/>

            <element name="C_STAT_NSF" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_NSF2"/>

            <element name="C_STAT_REJECTED" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_REJECTED2"/>

            <element name="C_STAT_SWP_IN" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_SWP_IN2"/>

            <element name="C_STAT_SWP_OUT" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_SWP_OUT2"/>

            <element name="C_STAT_SWP_OUT_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_STAT_SWP_OUT" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

            <element name="C_STAT_STOP" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_STOP2"/>

            <element name="C_STAT_REC_DR" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_REC_DR"/>

            <element name="C_STAT_REC_DR_MISC" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_REC_DR_MISC"/>

            <element name="C_STAT_REC_CR" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_REC_CR"/>

            <element name="C_STAT_REC_CR_MISC" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_REC_CR_MISC"/>

            <element name="C_STAT_REC_NSF" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_REC_NSF"/>

            <element name="C_STAT_REC_REJECTED" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_REC_REJECTED"/>

            <element name="C_STAT_REC_SWP_IN" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_REC_SWP_IN"/>

            <element name="C_STAT_REC_STOP" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_REC_STOP"/>

            <element name="C_STAT_REC_SWP_OUT" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_REC_SWP_OUT"/>

            <element name="C_STAT_REC_SWP_OUT_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_STAT_REC_SWP_OUT" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

            <element name="C_STAT_UNREC_DR" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_UNREC_DR"/>

            <element name="C_STAT_UNREC_DR_MISC" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_UNREC_DR_MISC"/>

            <element name="C_STAT_UNREC_CR" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_UNREC_CR"/>

            <element name="C_STAT_UNREC_CR_MISC" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_UNREC_CR_MISC"/>

            <element name="C_STAT_UNREC_NSF" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_UNREC_NSF"/>

            <element name="C_STAT_UNREC_REJECTED" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_UNREC_REJECTED"/>

            <element name="C_STAT_UNREC_SWP_IN" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_UNREC_SWP_IN"/>

            <element name="C_STAT_UNREC_STOP" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_UNREC_STOP"/>

            <element name="C_STAT_UNREC_SWP_OUT" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_UNREC_SWP_OUT"/>

            <element name="C_STAT_UNREC_SWP_OUT_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_STAT_UNREC_SWP_OUT" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

            <element name="C_STAT_OVERREC_CR_MISC" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_OVERREC_CR_MISC"/>

            <element name="C_STAT_OVERREC_CR" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_OVERREC_CR"/>

            <element name="C_STAT_OVERREC_DR_MISC" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_OVERREC_DR_MISC"/>

            <element name="C_STAT_OVERREC_DR" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_OVERREC_DR"/>

            <element name="C_STAT_OVERREC_REJECTED" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_OVERREC_REJECTED"/>

            <element name="C_STAT_OVERREC_STOP" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_OVERREC_STOP"/>

            <element name="C_STAT_OVERREC_NSF" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_OVERREC_NSF"/>

            <element name="C_STAT_OVERREC_SWP_IN" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_OVERREC_SWP_IN"/>

            <element name="C_STAT_OVERREC_SWP_OUT" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_OVERREC_SWP_OUT"/>

            <element name="C_STAT_OVERREC_SWP_OUT_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_STAT_OVERREC_SWP_OUT" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

            <element name="C_STAT_REC_CR_MISC_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_STAT_REC_CR_MISC" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

            <element name="C_STAT_REC_DR_MISC_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_STAT_REC_DR_MISC" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

            <element name="C_STAT_REC_CR_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_STAT_REC_CR" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

            <element name="C_STAT_REC_REJECTED_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_STAT_REC_REJECTED" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

            <element name="C_STAT_REC_STOP_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_STAT_REC_STOP" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

            <element name="C_STAT_REC_NSF_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_STAT_REC_NSF" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

            <element name="C_STAT_REC_DR_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_STAT_REC_DR" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

            <element name="C_STAT_REC_SWP_IN_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_STAT_REC_SWP_IN" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

            <element name="C_STAT_DR_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_STAT_DR" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

            <element name="C_STAT_CR_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_STAT_CR" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

            <element name="C_STAT_DR_MISC_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_STAT_DR_MISC" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

            <element name="C_STAT_CR_MISC_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_STAT_CR_MISC" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

            <element name="C_STAT_NSF_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_STAT_NSF" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

            <element name="C_STAT_SWP_IN_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_STAT_SWP_IN" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

            <element name="C_STAT_REJECTED_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_STAT_REJECTED" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

            <element name="C_STAT_STOP_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_STAT_STOP" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

            <element name="C_STAT_UNREC_DR_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_STAT_UNREC_DR" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

            <element name="C_STAT_UNREC_CR_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_STAT_UNREC_CR" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

            <element name="C_STAT_UNREC_DR_MISC_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_STAT_UNREC_DR_MISC" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

            <element name="C_STAT_UNREC_CR_MISC_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_STAT_UNREC_CR_MISC" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

            <element name="C_STAT_UNREC_NSF_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_STAT_UNREC_NSF" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

            <element name="C_STAT_UNREC_REJECTED_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_STAT_UNREC_REJECTED" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

            <element name="C_STAT_UNREC_STOP_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_STAT_UNREC_STOP" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

            <element name="C_STAT_UNREC_SWP_IN_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_STAT_UNREC_SWP_IN" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

            <element name="C_STAT_OVERREC_DR_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_STAT_OVERREC_DR" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

            <element name="C_STAT_OVERREC_DR_MISC_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_STAT_OVERREC_DR_MISC" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

            <element name="C_STAT_OVERREC_CR_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_STAT_OVERREC_CR" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

            <element name="C_STAT_OVERREC_SWP_IN_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_STAT_OVERREC_SWP_IN" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

            <element name="C_STAT_OVERREC_CR_MISC_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_STAT_OVERREC_CR_MISC" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

            <element name="C_STAT_OVERREC_NSF_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_STAT_OVERREC_NSF" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

            <element name="C_STAT_OVERREC_REJECTED_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_STAT_OVERREC_REJECTED" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

            <element name="C_STAT_OVERREC_STOP_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_STAT_OVERREC_STOP" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

            <group name="G_STAT_TYPE" dataType="varchar2" source="Q_STATEMENT_LINES">

               <element name="C_STAT_TYPE" dataType="varchar2" value="C_STAT_TYPE"/>

               <element name="C_TYPE_SUM_TRX_AMOUNT" function="sum" dataType="number" value="G_STATEMENT_TRANSACTIONS.C_TRX_SIGN_AMOUNT"/>

               <element name="C_TYPE_SUM_TRX_AMOUNT_DISP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_TYPE_SUM_TRX_AMOUNT" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

               <element name="C_TYPE_SUM_TRX_AMOUNT_CLEARED" function="sum" dataType="number" value="G_STATEMENT_TRANSACTIONS.C_TRX_SIGN_AMOUNT_CLEARED"/>

               <element name="C_TYPE_SUM_TRX_AMT_CLEARED_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_TYPE_SUM_TRX_AMOUNT_CLEARED" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

               <element name="C_TYPE_SUM_STAT_REC_AMOUNT" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_UNREC_AMOUNT"/>

               <element name="C_TYPE_SUM_STAT_REC_AMT_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_TYPE_SUM_STAT_REC_AMOUNT" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

               <element name="C_TYPE_REC_SUM_STAT_LINE_AMT" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_REC_AMOUNT"/>

               <element name="C_TYPE_REC_SUM_STAT_LINE_AMT_D" function="CURRENCY_FORMAT" dataType="varchar2" value="C_TYPE_REC_SUM_STAT_LINE_AMT" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

               <element name="C_TYPE_SUM_TRX_CHARGES" function="sum" dataType="number" value="G_STATEMENT_TRANSACTIONS.C_TRX_BANK_CHARGES"/>

               <element name="C_TYPE_SUM_TRX_CHARGES_DISP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_TYPE_SUM_TRX_CHARGES" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

               <element name="C_TYPE_SUM_TRX_ERRORS" function="sum" dataType="number" value="G_STATEMENT_TRANSACTIONS.C_TRX_BANK_ERRORS"/>

               <element name="C_TYPE_SUM_TRX_ERRORS_DISP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_TYPE_SUM_TRX_ERRORS" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

               <element name="C_TYPE_UNREC_SUM_LINE" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_UNREC_AMOUNT"/>

               <element name="C_TYPE_UNREC_SUM_LINE_DISP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_TYPE_UNREC_SUM_LINE" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

               <element name="C_TYPE_SUM_STAT_LINE_AMT" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_SIGNED_AMOUNT"/>

               <element name="C_TYPE_SUM_STAT_LINE_AMT_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_TYPE_SUM_STAT_LINE_AMT" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

               <element name="C_TYPE_SUM_UNREC_LINE_AMT" function="sum" dataType="number" value="G_STATEMENT_TRANSACTIONS.C_TX_SIGN_SL_UNREC_AMOUNT"/>

               <element name="C_TYPE_SUM_UNREC_LINE_AMT_DSP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_TYPE_SUM_UNREC_LINE_AMT" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

               <element name="C_TYPE_SUM_UNREC_POS_AMT" function="sum" dataType="number" value="G_STATEMENT_LINES.C_SL_POS_UNREC_AMOUNT"/>

               <group name="G_STATEMENT_LINES" dataType="varchar2" source="Q_STATEMENT_LINES">

                  <element name="C_ORDER_BY" dataType="number" value="C_ORDER_BY"/>

                  <element name="C_SL_NUMBER" dataType="number" value="C_SL_NUMBER"/>

                  <element name="C_SORT" dataType="varchar2" value="C_SORT"/>

                  <element name="C_SL_TYPE" dataType="varchar2" value="C_SL_TYPE"/>

                  <element name="C_SL_TRX_NUMBER" dataType="varchar2" value="C_SL_TRX_NUMBER"/>

                  <element name="C_STAT_LINE_STATUS_DSP" dataType="varchar2" value="C_STAT_LINE_STATUS_DSP"/>

                  <element name="C_STAT_LINE_STATUS" dataType="varchar2" value="C_STAT_LINE_STATUS"/>

                  <element name="C_SL_DATE" dataType="varchar2" value="C_SL_DATE"/>

                  <element name="C_SL_AMOUNT" dataType="number" value="C_SL_AMOUNT"/>

                  <element name="C_SL_SIGNED_AMOUNT" dataType="number" value="C_SL_SIGNED_AMOUNT"/>

                  <element name="C_SL_DR_ERRORS" dataType="number" value="C_SL_DR_ERRORS"/>

                  <element name="C_SL_CR_ERRORS" dataType="number" value="C_SL_CR_ERRORS"/>

                  <element name="C_SL_REC_SIGNED_AMOUNT" function="sum" dataType="number" value="G_STATEMENT_TRANSACTIONS.C_TRX_SIGN_AMOUNT"/>

                  <element name="C_SL_REC_AMOUNT1" dataType="number" value="C_SL_REC_AMOUNT"/>

                  <element name="C_SL_UNREC_SIGNED_AMOUNT" dataType="number" value="C_SL_UNREC_SIGNED_AMOUNT"/>

                  <element name="C_SL_POS_UNREC_AMOUNT" dataType="number" value="C_SL_POS_UNREC_AMOUNT"/>

                  <element name="C_SL_UNREC_AMOUNT1" dataType="number" value="C_SL_UNREC_AMOUNT"/>

                  <element name="C_SL_AMOUNT_DISP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_SL_AMOUNT" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

                  <element name="C_SL_UNREC_AMOUNT_DISP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_SL_UNREC_AMOUNT" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

                  <element name="C_SL_REC_AMOUNT_DISP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_SL_REC_AMOUNT" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

                  <element name="C_SL_DR" dataType="number" value="C_SL_DR"/>

                  <element name="C_SL_TYPE_DSP" dataType="varchar2" value="C_SL_TYPE_DSP"/>

                  <element name="C_SL_MISC_DR" dataType="number" value="C_SL_MISC_DR"/>

                  <element name="C_SL_CR" dataType="number" value="C_SL_CR"/>

                  <element name="C_SL_NSF" dataType="number" value="C_SL_NSF"/>

                  <element name="C_SL_MISC_CR" dataType="number" value="C_SL_MISC_CR"/>

                  <element name="C_SL_SWP_IN2" dataType="number" value="C_SL_SWP_IN2"/>

                  <element name="C_SL_SWP_IN" dataType="number" value="C_SL_SWP_IN"/>

                  <element name="C_SL_SWP_OUT" dataType="number" value="C_SL_SWP_OUT"/>

                  <element name="C_SL_STOP" dataType="number" value="C_SL_STOP"/>

                  <element name="C_SL_REJECTED" dataType="number" value="C_SL_REJECTED"/>

                  <element name="C_SL_REC_DR" dataType="number" value="C_SL_REC_DR"/>

                  <element name="C_SL_REC_DR_MISC" dataType="number" value="C_SL_REC_DR_MISC"/>

                  <element name="C_SL_REC_CR" dataType="number" value="C_SL_REC_CR"/>

                  <element name="C_SL_REC_CR_MISC" dataType="number" value="C_SL_REC_CR_MISC"/>

                  <element name="C_SL_REC_NSF" dataType="number" value="C_SL_REC_NSF"/>

                  <element name="C_SL_REC_REJECTED" dataType="number" value="C_SL_REC_REJECTED"/>

                  <element name="C_SL_REC_SWP_OUT" dataType="number" value="C_SL_REC_SWP_OUT"/>

                  <element name="C_SL_REC_SWP_IN" dataType="number" value="C_SL_REC_SWP_IN"/>

                  <element name="C_SL_REC_STOP" dataType="number" value="C_SL_REC_STOP"/>

                  <element name="C_SL_UNREC_DR" dataType="number" value="C_SL_UNREC_DR"/>

                  <element name="C_SL_UNREC_DR_MISC" dataType="number" value="C_SL_UNREC_DR_MISC"/>

                  <element name="C_SL_UNREC_CR" dataType="number" value="C_SL_UNREC_CR"/>

                  <element name="C_SL_UNREC_SWP_IN" dataType="number" value="C_SL_UNREC_SWP_IN"/>

                  <element name="C_SL_UNREC_SWP_OUT" dataType="number" value="C_SL_UNREC_SWP_OUT"/>

                  <element name="C_SL_OVERREC_CR" dataType="number" value="C_SL_OVERREC_CR"/>

                  <element name="C_SL_OVERREC_CR_MISC" dataType="number" value="C_SL_OVERREC_CR_MISC"/>

                  <element name="C_SL_OVERREC_DR" dataType="number" value="C_SL_OVERREC_DR"/>

                  <element name="C_SL_OVERREC_DR_MISC" dataType="number" value="C_SL_OVERREC_DR_MISC"/>

                  <element name="C_SL_OVERREC_NSF" dataType="number" value="C_SL_OVERREC_NSF"/>

                  <element name="C_SL_OVERREC_REJECTED" dataType="number" value="C_SL_OVERREC_REJECTED"/>

                  <element name="C_SL_OVERREC_SWP_OUT" dataType="number" value="C_SL_OVERREC_SWP_OUT"/>

                  <element name="C_SL_OVERREC_SWP_IN" dataType="number" value="C_SL_OVERREC_SWP_IN"/>

                  <element name="C_SL_OVERREC_STOP" dataType="number" value="C_SL_OVERREC_STOP"/>

                  <element name="C_SL_UNREC_CR_MISC" dataType="number" value="C_SL_UNREC_CR_MISC"/>

                  <element name="C_SL_UNREC_NSF" dataType="number" value="C_SL_UNREC_NSF"/>

                  <element name="C_SL_UNREC_REJECTED" dataType="number" value="C_SL_UNREC_REJECTED"/>

                  <element name="C_SL_UNREC_STOP" dataType="number" value="C_SL_UNREC_STOP"/>

                  <element name="C_SL_DR2" dataType="number" value="C_SL_DR2"/>

                  <element name="C_SL_MISC_DR2" dataType="number" value="C_SL_MISC_DR2"/>

                  <element name="C_SL_CR2" dataType="number" value="C_SL_CR2"/>

                  <element name="C_SL_MISC_CR2" dataType="number" value="C_SL_MISC_CR2"/>

                  <element name="C_SL_NSF2" dataType="number" value="C_SL_NSF2"/>

                  <element name="C_SL_REJECTED2" dataType="number" value="C_SL_REJECTED2"/>

                  <element name="C_SL_SWP_OUT2" dataType="number" value="C_SL_SWP_OUT2"/>

                  <element name="C_SL_STOP2" dataType="number" value="C_SL_STOP2"/>

                  <group name="G_STATEMENT_TRANSACTIONS" dataType="varchar2" source="Q_STATEMENT_LINES">

                     <element name="C_STAT_LINE_ID" dataType="number" value="C_STAT_LINE_ID"/>

                     <element name="C_SL_BANK_CHARGES" dataType="number" value="C_SL_BANK_CHARGES"/>

                     <element name="C_TRX_SIGN_AMOUNT_CLEARED" function="sum" dataType="number" value="G_TX.C_TX_SIGN_AMOUNT_CLEARED_FOR"/>

                     <element name="C_TRX_SIGN_AMOUNT" function="sum" dataType="number" value="G_TX.C_TX_SIGN_AMOUNT_FOR"/>

                     <element name="C_TRX_BANK_CHARGES" function="sum" dataType="number" value="G_TX.C_TX_BANK_CHARGES_FOR"/>

                     <element name="C_TRX_BANK_CHARGES_DISP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_TRX_BANK_CHARGES" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

                     <element name="C_TRX_BANK_ERRORS" function="sum" dataType="number" value="G_TX.C_TX_BANK_ERRORS_FOR"/>

                     <element name="C_TRX_BANK_ERRORS_DISP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_TRX_BANK_ERRORS" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

                     <element name="C_TRX_SIGN_SL_UNREC_AMOUNT" function="sum" dataType="number" value="G_STATEMENT_TRANSACTIONS.C_TX_SIGN_SL_UNREC_AMOUNT"/>

                     <element name="C_TX_SIGN_SL_UNREC_AMOUNT" dataType="number" value="C_TX_SIGN_SL_UNREC_AMOUNT"/>

                     <group name="G_C_SL_AMOUNT_CLEARED" dataType="varchar2" source="Q_CRE">

                        <element name="C_SET_OF_BOOKS_ID1" dataType="number" value="C_SET_OF_BOOKS_ID"/>

                        <element name="C_GL_CURRENCY_CODE1" dataType="varchar2" value="C_GL_CURRENCY_CODE"/>

                        <element name="C_SL_AMOUNT_CLEARED" dataType="number" value="C_SL_AMOUNT_CLEARED"/>

                        <element name="C_TRX_REFERENCE_TYPE" dataType="varchar2" value="C_TRX_REFERENCE_TYPE"/>

                        <element name="C_TRX_REFERENCE_ID" dataType="number" value="C_TRX_REFERENCE_ID"/>

                        <element name="C_TRX_JE_HEADER_ID" dataType="number" value="C_TRX_JE_HEADER_ID"/>

                        <element name="C_TRX_STMT_LN_ID" dataType="number" value="C_TRX_STMT_LN_ID"/>

                        <group name="G_TX" dataType="varchar2" source="Q_TX">

                           <element name="C_TX_BANK_ERRORS" dataType="number" value="C_TX_BANK_ERRORS"/>

                           <element name="C_TX_BANK_CHARGES" dataType="number" value="C_TX_BANK_CHARGES"/>

                           <element name="C_TX_ID" dataType="number" value="C_TX_ID"/>

                           <element name="C_TX_TYPE" dataType="varchar2" value="C_TX_TYPE"/>

                           <element name="C_JE_HEADER_ID" dataType="number" value="C_JE_HEADER_ID"/>

                           <element name="C_TX_RECEIPT_DATE" dataType="date" value="C_TX_RECEIPT_DATE"/>

                           <element name="C_TX_TYPE_MEANING" dataType="varchar2" value="C_TX_TYPE_MEANING"/>

                           <element name="C_TX_STATUS_MEANING" dataType="varchar2" value="C_TX_STATUS_MEANING"/>

                           <element name="C_TX_NUMBER" dataType="varchar2" value="C_TX_NUMBER"/>

                           <element name="C_TX_SIGN_BA_AMOUNT" dataType="number" value="C_TX_SIGN_BA_AMOUNT"/>

                           <element name="C_TX_AMOUNT_CLEARED" dataType="number" value="C_TX_AMOUNT_CLEARED"/>

                           <element name="C_TX_BA_AMOUNT" dataType="number" value="C_TX_BA_AMOUNT"/>

                           <element name="C_TX_DR_CR" dataType="varchar2" value="C_TX_DR_CR"/>

                           <element name="C_TX_STATUS" dataType="varchar2" value="C_TX_STATUS"/>

                           <element name="C_TRX_BA_AMOUNT_DISP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_TX_AMOUNT_FOR2" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

                           <element name="C_TX_AMOUNT_FOR2" dataType="number" value="C_TX_AMOUNT_FOR2"/>

                           <element name="C_TX_AMOUNT_FOR" dataType="number" value="C_TX_AMOUNT_FOR"/>

                           <element name="C_TX_SIGN_AMOUNT_FOR" dataType="number" value="C_TX_SIGN_AMOUNT_FOR"/>

                           <element name="C_TX_SIGN_AMOUNT_CLEARED_FOR" dataType="number" value="C_TX_SIGN_AMOUNT_CLEARED_FOR"/>

                           <element name="C_TRX_BA_AMOUNT_CLEARED_DISP" function="CURRENCY_FORMAT" dataType="varchar2" value="C_TX_AMOUNT_CLEARED_FOR" currencyCode="C_BANK_ACC_CURRENCY" width="19"/>

                           <element name="C_TX_AMOUNT_CLEARED_FOR" dataType="number" value="C_TX_AMOUNT_CLEARED_FOR"/>

                           <element name="C_TX_BANK_CHARGES_FOR" dataType="number" value="C_TX_BANK_CHARGES_FOR"/>

                           <element name="C_TX_BANK_ERRORS_FOR" dataType="number" value="C_TX_BANK_ERRORS_FOR"/>

                           <element name="C_TX_EXCHANGE_RATE" dataType="number" value="C_TX_EXCHANGE_RATE"/>

                           <element name="C_TX_BANK_ERRORS" dataType="number" value="C_TX_SIGN_AMOUNT_CLEARED"/>

                        </group>

                     </group>

                  </group>

               </group>

            </group>

         </group>

      </group>

      <element name="C_STAT_DATE_SQL_LEX" dataType="varchar2" value="CE_CEXSTMRR_XMLP_PKG.C_STAT_DATE_SQL_LEX_p"/>

      <element name="C_BANK_NAME_DSP" dataType="varchar2" value="CE_CEXSTMRR_XMLP_PKG.C_BANK_NAME_DSP_p"/>

      <element name="C_ACCOUNT_NAME_DSP" dataType="varchar2" value="CE_CEXSTMRR_XMLP_PKG.C_ACCOUNT_NAME_DSP_p"/>

      <element name="C_BANK_BRANCH_DSP" dataType="varchar2" value="CE_CEXSTMRR_XMLP_PKG.C_BANK_BRANCH_DSP_p"/>

      <element name="C_EXTERNAL_TRANSLATION" dataType="varchar2" value="CE_CEXSTMRR_XMLP_PKG.C_EXTERNAL_TRANSLATION_p"/>

      <element name="C_ALL_TRANSLATION" dataType="varchar2" value="CE_CEXSTMRR_XMLP_PKG.C_ALL_TRANSLATION_p"/>

      <element name="C_STAT_NUMBER_SQL_LEX" dataType="varchar2" value="CE_CEXSTMRR_XMLP_PKG.C_STAT_NUMBER_SQL_LEX_p"/>

      <element name="C_THE_END" dataType="varchar2" value="CE_CEXSTMRR_XMLP_PKG.C_THE_END_p"/>

      <element name="C_GL_CURRENCY_CODE" dataType="varchar2" value="CE_CEXSTMRR_XMLP_PKG.C_GL_CURRENCY_CODE_p"/>

      <element name="C_NAME" dataType="varchar2" value="CE_CEXSTMRR_XMLP_PKG.C_NAME_p"/>

      <element name="C_SET_OF_BOOKS_ID" dataType="number" value="CE_CEXSTMRR_XMLP_PKG.C_SET_OF_BOOKS_ID_p"/>

      <element name="C_BANK_CURR_DSP" dataType="varchar2" value="CE_CEXSTMRR_XMLP_PKG.C_BANK_CURR_DSP_p"/>

      <element name="C_ACCOUNT_NUMBER_DSP" dataType="varchar2" value="CE_CEXSTMRR_XMLP_PKG.C_ACCOUNT_NUMBER_DSP_p"/>

      <element name="C_DATEFORMAT" dataType="varchar2" value="CE_CEXSTMRR_XMLP_PKG.c_dateformatformula(' ')"/>

      <element name="C_DISPLAY_MESSAGE" dataType="varchar2" value="CE_CEXSTMRR_XMLP_PKG.C_DISPLAY_MESSAGE_p"/>

   </dataStructure>

   <dataTrigger name="afterReportTrigger" source="CE_CEXSTMRR_XMLP_PKG.afterreport()"/>

</dataTemplate>