How to find the Oracle Fusion Instance name from Backend using sql

 Table to find out the fusion Instance name: Fusion.ASK_DEPLOYED_DOMAINS

Query:-

SELECT
        external_virtual_host,

        SUBSTR (external_virtual_host, 1,instr(external_virtual_host,'.') -1)
FROM
        fusion.ask_deployed_domains ad
WHERE
        ad.deployed_domain_name='FADomain';

Sequence in Oracle

 

SYNTAX:-

CREATE SEQUENCE SEQUENCE_NAME

START WITH 

INCREMENT BY 

NOCACHE

NOCYCLE;

EXAMPLE:-

Customers_Seq is Sequnce Name, It will start with 1000 and increment by 1 each time.

CREATE SEQUENCE customers_seq

START  WITH 1000

INCREMENT BY 1

NOCACHE

NOCYCLE;

NOCACHE /  CACHE:-

The cache option specifies how many sequence values will be stored in memory for faster access.

The downside of creating a sequence with cache is that if a system failure occurs, all the cache sequence values that have not been used will be lost.

This results a gap in t he assigned sequence values. When the system comes back, Oracle will cache new numbers from where it left off in the sequence, ignoring the so called "last  sequence values".

TIP: To recover the lost sequence values, you can always execute an ALTER SEQUENCE command to reset the counter to the correct value

NoCache means, that none of the values are stored in memory. This option may sacrifice some performance. However, you should not encounter a gap in the assigned sequence values.

NOCYCLE

Use NOCYCLE if you want the sequence to stop generating the next value when it reaches its limit. 

BI Publisher Bursting in Oracle Fusion

• Bursting is the method of automatically splitting and submitting the report output via email or fax. 
• Bursting aids in the output of the report. With the aid of Bursting, we can send the report output to our customer after running it through some logic. 
• Bursting operates by splitting the report output according to our logic and then sending it to the customers or some email address. We have the Bursting Functionality in Reports framework in Oracle Cloud, just like EBS. 

I’ll go into the Bursting Configuration in Oracle Cloud BIP Files in depth below.

BIP Bursting Steps in Oracle Fusion

1. Create a BIP Data Model based on SQL queries.
2. From the SQL Data Model, create a BIP Report.
3. Go to the Data Model for the BIP report that we want to burst.
4. The report data model as seen below, and I’d like to build the bursting according to the vendor’s guidance. As an example, the report output can be divided and sent according to the supplier, so the important column of this report is VENDOR_ID, which we will use to split the report output for different suppliers.
5. We need to go to Bursting Open in the report Data Model, as seen below.
6. To build the bursting Logic for this report, press the ‘+’ button.
7. Oracle has designed Fusion BIP Bursting logic in two parts (1) Split by (2) Deliver by

Split By: By using split by method we can divide the output of the reports based on which column of the report we’re looking at. For example, we would use the Vendor_id column to split the report output by vendor, and if the report output has three vendors, Bursting will divide and split the report output into three parts based on Vendor_Id.

Deliver By: Deliver by method is used to submit the report output via email. We must also define the report column depending on which the report output should be delivered.

The parent column is ‘Split By‘ and the child column is ‘Deliver By‘ , Bursting email deliver logic is based on ‘Deliver By‘.

8. In the Split By and Deliver By LOV, I used the report’s common Vendor_ID column.

9. We’ll use the SQL Query below in the SQL Query block.

SELECT

               VENDOR_ID AS "KEY",

              'BIP_REPORT_RTF_NAME.rtf' TEMPLATE,

              'en-US' LOCALE,

               'PDF' OUTPUT_FORMAT,

               VENDOR_NAME OUTPUT_NAME,

                'EMAIL' DEL_CHANNEL,

                'To Email Address' PARAMETER1,

                'Cc Email Address' PARAMETER2,

                'TestEmail.email.com' PARAMETER3,

                'The Emails Subject' PARAMETER4,

                'Hi

                '||'The body of the email that will be reflected in the Bursting Email.

                '||'Thanks & Regards

                Person Name' PARAMETER5,

                'True' PARAMETER6,

                :p_from_email  PARAMETER7

FROM PO_VENDORS;

10. The final Bursting Logic written in the BIP report is shown below.

How to create dependent LOV in Oracle Fusion BI Reports

 

Steps

1. Create a Data Model with SQL query
2. Create 2 list of values(LOV), pass parameter in dependent LOV query.
3. Create 2 Parameters, select their respective list of value(LOV).
4. Save the Data Model.
5. Run BIP Report, Dependent LOV is changing based on Independent values.




For Example: We are creating 2 parameters and when the independent LOV value changes, the dependent LOV changes as well.

1. Header_ID : Independent Parameter
2. Line_ID: Dependent Parameter

(1) Create a Data Model

Navigation:  Navigator > More > Report and Analytics > Browse Catalogue > New > Data Model

Data Model SQL Query: 

Select PO_Header_id, Type_lookup_code, segment1,Vendor_id

From PO_Headers_all;

(2) Create 2 list of values(LOV)

Create 2 list of values, one is independent and another one will dependent, pass parameter in dependent list of value SQL query



Write SQL query for each list of Value(LOV)



Pass parameter values in SQL query for dependent LOV



(3) Create 2 Parameters



Select highlighted options for both parameters



(4) Save the BIP Report

Click on Save icon from the right top of the window and choose the location where you want to save the data model.

(5) Run BIP Report

To run the BIP report click on View Data button from the right top window screen, choose any Header_ID from the first LOV and you will find second parameter Line_ID list of values are changing.

Concurrent Program assigned to Responsibility in oracle apps

SELECT  frt.responsibility_name, 

        frg.request_group_name,

       fcpt.user_concurrent_program_name

FROM fnd_Responsibility fr, 

     fnd_responsibility_tl frt,
     fnd_request_groups frg, 

     fnd_request_group_units frgu,
     fnd_concurrent_programs_tl fcpt

WHERE frt.responsibility_id = fr.responsibility_id

AND frg.request_group_id = fr.request_group_id
AND frgu.request_group_id = frg.request_group_id
AND fcpt.concurrent_program_id = frgu.request_unit_id
AND frt.LANGUAGE = USERENV('LANG')
AND fcpt.LANGUAGE = USERENV('LANG')
AND fcpt.user_concurrent_program_name = :conc_prg_name

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>