XTR Term Money - Retail Term Money Statements

select a.deal_no           DEAL_NUM,
       a.cparty_code       CPARTY,
       :AS_AT_DATE2    S_DATE,
       a.start_date        COMMENCE,
       a.face_value_amount S_AMOUNT,
       decode(a.deal_subtype ,'INVEST',:z_loan_no,:z_dep_no)    REF_NUM,
       decode(a.deal_subtype
         ,'INVEST',:z_of_your||' '||rtrim(p.product_desc)||' '||:z_loan_follow
         ,:z_of_your||' '||rtrim(p.product_desc)||' '||:z_dep_follow)
                           D_SUB,
       b.short_name        NAME,
       b.title_description  TITLE,
       b.address_1         ADDR_1,
       b.address_2         ADDR_2,
       b.address_3         ADDR_3,
       b.address_4         ADDR_4,
       b.address_5         ADDR_5,
       d.short_name        COMP_NAME,
       d.address_1         COMP_ADDR_1,
       d.address_2         COMP_ADDR_2,
       d.address_3         COMP_ADDR_3,
       d.address_4         COMP_ADDR_4,
       d.address_5         COMP_ADDR_5,
	   &LAS_AT_DATE2 STATE_DATE
from XTR_DEALS_V a,
     XTR_PARTIES_V b,
     XTR_PARTIES_V d,
     XTR_PRODUCT_TYPES_V p
where a.DEAL_NO like nvl(:DEAL_NO2, a.DEAL_NO)
and a.DEAL_TYPE = 'RTMM'
and a.CPARTY_CODE like nvl(upper(:CPARTY_CODE2),  a.CPARTY_CODE)
and ((a.START_DATE >= :AS_AT_DATE3) and (a.START_DATE <= :AS_AT_DATE2))
and a.PRODUCT_TYPE = p.PRODUCT_TYPE (+)
and p.DEAL_TYPE = 'RTMM'
and ((a.DEAL_SUBTYPE = 'INVEST' and UPPER(:DEAL_SUBTYPE2) = 'ADVANCE') or
     (a.DEAL_SUBTYPE = 'FUND' and upper(:DEAL_SUBTYPE2) = 'DEPOSIT') or
     (a.DEAL_SUBTYPE IN('FUND','INVEST') and nvl(:DEAL_SUBTYPE2,'%') = '%'))
and b.PARTY_CODE = a.CPARTY_CODE
and a.COMPANY_CODE = d.PARTY_CODE