with    TABLEDAY as (select 
                            Trunc(T.DREP,'MM') DBEG,
                            Last_Day(T.DREP)   DEND
                       from (select 
                                    To_Date('01-01-2017','DD-MM-YYYY') DREP
                               from DUAL
                            ) T        
                    ),
        TRN_LIST as (select 
                            Sys.OdciVarchar2List('CT2111','CT0040','CT0041','CT0042','CT0043','CT0047','CT0052','CT2011','CT2013','CT2031','CT2033','CT2113','CT2131','CT2133') ATM,
                            Sys.OdciVarchar2List('CT0044','CT0045','CT0047','CT0048','CT0049','CT0052','CT0053','CT0054','CT0065','CT0066','CT2021','CT2121') POS,
                            Sys.OdciVarchar2List(         'CT0040','CT0041','CT0042','CT0043') USB
                       from DUAL
                    ),   
      RCC_KREDIT as (select /*+ Materialize */
                            A.CEEIB_S_UA_DEAL                                             ID_CONTRACT_SAP,
                            Max(Nvl(G.ACTIVE,B.S_UA_DFRO))                                ACTIVE, 
                            Max('RCC')                                                    PROGRAM,
                            Max(D.TXTMD)                                                  NETWORK,
                            Max(B.SCHEMA)                                                 CREDIT_TYPE,
                            'ALL'                                                         TECHNOLOGY,
                            Sum(A.DEB_CRE_LC)                                             TOTAL_PRICE,
                            Round(Sum(Case when A.TB_TRNTYPE in(select * from table(select POS from TRN_LIST)) then 0
                                           when      Trunc(Months_Between(Nvl(G.ACTIVE,B.S_UA_DFRO),C.DBEG))=0 then F.OPENING_RATE1
                                           when A.TB_TRNTYPE in(select * from table(select USB from TRN_LIST)) then F.OPENING_RATE_UKRSIB       
                                                                                                               else F.OPENING_RATE_OTHER
                                      end * A.DEB_CRE_LC
                                     )
                                     /Sum(A.DEB_CRE_LC)
                                 ,8
                                 )                                                        OPENING_RATE,
                            0                                                             MONTHLY_RATE,
                            Round(Sum(Case when A.TB_TRNTYPE in(select * from table(select ATM from TRN_LIST)) then E.B0_NDUE/100 
                                                                                                               else E.B1_NDUE/100 
                                      end * A.DEB_CRE_LC
                                     )
                                     /Sum(A.DEB_CRE_LC)
                                 ,8
                                 )                                                        ACT_RATE,
                            Max(P.VALUE)                                                  FTP_RATE,     
                            Round(Sum(Greatest(E.B0_PMIN/100,0.00000001)*A.DEB_CRE_LC)/Sum(A.DEB_CRE_LC),8) MIN_RATE,     
                            Round(Sum(         F.COMIS_RKO        *A.DEB_CRE_LC)/Sum(A.DEB_CRE_LC),8) COMIS_RKO,  
                            Greatest(Max(Ceil(Months_Between(Add_Months(Nvl(G.ACTIVE,B.S_UA_DFRO),
                                                                        Case when Add_Months(Nvl(G.ACTIVE,B.S_UA_DFRO),24)<C.DBEG then 48 else 24 end
                                                                       )
                                                             ,C.DBEG
                                                            )-1 
                                             )
                                        )                   
                                    ,1
                                    )                                                     DURATION,       
                            Round(Sum(Case when A.TB_TRNTYPE in(select * from table(select ATM from TRN_LIST)) then F.GRACE 
                                                                                                               else       0 
                                      end * A.DEB_CRE_LC
                                     )
                                     /Sum(A.DEB_CRE_LC)
                                 ,8
                                 ) GRACE
                       from ADBUS.V_CFA_PAYM A
                       left join ADBUS.V_CFA_CTX_DEAL B                                             
                         on (B.CEEIB_S_UA_DEAL=A.CEEIB_S_UA_DEAL)
                       left join ADBUS.V_CFA_CTX_DIC_CARD_PRODUCT D
                         on (D.CTX_CRPR=B.CARD_PRODUCT)
                       left join ADBUS.V_CFA_CTX_SCHEME E 
                         on (    'CRTX\'||E.CTX_PROF||'\'||E.CRTX_SCH=B.SCHEMA
                             and A.PSTNG_DATE between E.DATE_FROM and E.DATE_TO
                            )
                       left join PF_REPORT.PM_CFA_CARD_PRODUCT F
                         on (    F.CARD_PRODUCT=B.CARD_PRODUCT
                             and A.PSTNG_DATE between F.DATE_FROM and F.DATE_TO
                            )
                       left join (select ACC_NUMBER  ID_CONTRACT_SAP,
                                         Min(DATE_FROM) ACTIVE
                                    from ADBUS.V_CFA_CTX_CARD
                                   where CARD_STATUS='CHST0'
                                   group by ACC_NUMBER
                                 ) G
                         on (G.ID_CONTRACT_SAP=Ltrim(A.CEEIB_S_UA_DEAL,'CTX'))
                       left join PM_FTP P
                         on (    P.DAY =Last_Day(Nvl(G.ACTIVE,B.S_UA_DFRO))
                             and P.KIND='bullet'
                             and P.RATE=0.0
                             and P.SPAN=24
                            ),
                            TABLEDAY C
                      where A.PSTNG_DATE between C.DBEG and C.DEND
                        and A.CEEIB_S_UA_DEAL like 'CTX2625%'
                        and A.TB_TRNTYPE in(select * 
                                              from table(select ATM from TRN_LIST)
                                             union all
                                            select * 
                                              from table(select POS from TRN_LIST)               
                                           )
                        and A.ACCDEBCRED='D'
                      group by
                            A.CEEIB_S_UA_DEAL
                    )
select A.*
  from (select C.*
          from (select /*+ No_Merge */
                       b.id_contract_sap,
                       b.program,
                       b.network,
                       b.credit_type,
                       b.technology,
                       b.TOTAL_PRICE,
                       b.OPENING_RATE,
                       b.monthly_rate,
                       b.act_rate,
                       b.ftp_rate,
                       b.min_rate,
                       b.comis_rko,
                       b.DURATION,
                       b.grace,
                       null MODES
                  from RCC_KREDIT b
               ) C
       ) A,
       table(ttRcc(A.id_contract_sap, 
                   A.program,
                   A.network,
                   A.credit_type,
                   A.technology,
                   A.total_price,
                   A.opening_rate,
                   A.monthly_rate,
                   A.act_rate,
                   A.ftp_rate,
                   A.min_rate,
                   A.comis_rko,
                   A.duration,
                   A.grace,
                   A.modes 
                  ).Show()
            ) T