select distinct
       to_char(to_date(to_char(a.aprvdte,'dd/MM/yyyy','NLS_CALENDAR=''THAI BUDDHA'' NLS_DATE_LANGUAGE=THAI'),'dd/MM/yyyy'),'dd/MM/yyyy') as วันที่ทำสัญญา,
       a.applno as "Appl No.",
       a.offcde as สาขา,
       a.contno as เลขที่สัญญา,
       a.custno as รหัสลูกค้า,
       a.idno as เลขบัตรลูกค้า,
       case l.namformat when 'O' then rtrim(l.cusname) when 'N' then  m.thdesc || l.thname || ' ' || l.thsurn end as ชื่อลูกค้า,
       a.deacde as "Dealer Code",
       z1.thname as "ชื่อผู้แทนจำหน่าย",
       a.loanoff as "Marketing Code",
       nvl(d.thname,' ') as "เจ้าหน้าที่การตลาด",
       case when dd.oldnew = 'O' and nvl(a.carvat, 0.00) > 0 Then 'รถเก่ามีภาษีซื้อ'
            when dd.oldnew = 'O' and nvl(a.carvat, 0.00) = 0 Then 'รถเก่าไม่มีภาษีซื้อ'
            when dd.oldnew = 'N' and nvl(a.carvat, 0.00) > 0 Then 'รถใหม่มีภาษีซื้อ'
         else 'รถใหม่ไม่มีภาษีซื้อ'
       end สภาพรถ, 

       dd.brandname as ยี่ห้อรถ,
       dd.modelname as รายละเอียดรุ่น, 

       dd.engno as เลขเครื่อง,
       dd.chasno as เลขตัวถัง,
       dd.horpwr as "CC",
       --------------------
       z.prmcde as CAMPAIGN,
       z.thdesc as "CAMPAIGN Desc",
       a.loantyp as รหัสประเภทสัญญา, 
       e.thdesc as ประเภทสัญญา, 
       --------------------       
       a.firstlasttyp as "ประเภทการชำระ E/B",     
       h.category as รหัสลักษณะรถ,
       h.thdesc as ลักษณะรถ,
       dd.licno as ทะเบียน,
       dd.provName as จังหวัด, 
       nvl(a.caramt, 0.00) as "ราคารถ(ก่อน VAT)",
       nvl(a.carvat, 0.00) as "ราคารถ(VAT)",
       nvl(a.caramt, 0.00)+nvl(a.carvat, 0.00) as "ราคารถ (รวมVAT)",       
       nvl(a.downpay,0) as "เงินดาวน์(ก่อน VAT)",
       nvl(a.downvat,0) as "เงินดาวน์(VAT)",
       nvl(a.downpay,0)+nvl(a.downvat,0) as "เงินดาวน์(รวม VAT)",       
       -------------------        
       /*nvl(a.hirpamt, 0.00) as "ยอดจัด(ก่อน VAT)",
       nvl(a.hirpvat, 0.00) as "ยอดจัด(VAT)",
       nvl(a.hirpamt, 0.00)+nvl(a.hirpvat, 0.00) as "ยอดจัด(รวม VAT)",*/
       nvl(a.finamt,0) as "ยอดจัด(ก่อน VAT)",
       nvl(a.finvat,0) as "ยอดจัด(VAT)",
       nvl(a.finamt,0) + nvl(a.finvat,0) as "ยอดจัด(รวม VAT)",
       -------------------      
       a.term as จำนวนงวด,
       a.rate as อัตราดอกเบี้ย,       
       nvl(a.instamt,0) as "ค่างวด(ก่อน VAT)",
       nvl(a.instvat,0) as "ค่างวด(VAT)",       
       nvl(a.instamt,0) + nvl(instvat,0)  as "ค่างวด(หลัง VAT)",       
       nvl(a.lastinstallamt,0) as "ค่างวดสุดท้าย(ก่อน VAT)",
       nvl(a.lastinstallvat,0) as "ค่างวดสุดท้าย(VAT)",
       nvl(a.lastinstallamt,0)+nvl(a.lastinstallvat,0) as"ค่างวดสุดท้าย(หลัง VAT)",              
       nvl(a.hiramt, 0.00) as "ดอกผลเช่าซื้อ(ก่อน VAT)",
       nvl(a.hirvat, 0.00) as "ดอกผลเช่าซื้อ(VAT)",       
       nvl(a.hiramt, 0.00) + nvl(a.hirvat, 0.00) as "ดอกผลเช่าซื้อ(หลัง VAT)",    
       -------------------   
       nvl(a.hirpamt, 0.00) as "ยอดเช่าซื้อ (ก่อน VAT)",
       nvl(a.hirpvat, 0.00) as "ยอดเช่าซื้อ (VAT)",
       nvl(a.hirpamt, 0.00)+nvl(a.hirpvat, 0.00) as "ยอดเช่าซื้อ (หลัง VAT)",       
       -------------------
       to_char(to_date(to_char(a.firstdte,'dd/MM/yyyy','NLS_CALENDAR=''THAI BUDDHA'' NLS_DATE_LANGUAGE=THAI'),'dd/MM/yyyy'),'dd/MM/yyyy') as "วันที่ชำระงวดแรก",  
       a.spehirch as "รายได้ชดเชย (ก่อน VAT)",       
       --a.spehirchvat as "รายได้ชดเชย (VAT)"
       --a.spehirchtax as "รายได้ชดเชย (TAX)"
       --a.spehirchnet as "รายได้ชดเชย",
       a.spehirchper as "Subsidy%",
       nvl(CH1.comperCH,0) as "Com%",
       nvl(CH1.commexamtCH,0) as "ค่าคอม (ก่อน VAT)",
       nvl(CL1.commexamtCL,0) as "Extra(ก่อน VAT)",
       --nvl(CH1.netamtCH,0) as ค่าคอม,
       --nvl(CL1.netamtCL,0) as Extra,
       nvl(CH3.comperCH,0) as "Com% Marketing",
       nvl(CH3.commexamtCH,0) as "ค่าคอม (ก่อน VAT) Marketing",
       nvl(CL3.commexamtCL,0) as "Extra (ก่อน VAT) Marketing",
       a.dutyamt as อากร, 
       a.EFFRATE as EFFRATE,
       ----------- 
       round(a.IRRRATE,4) as IRRRATE,
       -----------
       nvl(IV.INSCMP,'') as "Code ประกันภัย",
       nvl(IV.inscmpname,'') as บริษัทประกัน,
       nvl(IV.instypname,'') as ชั้นประกัน,
       nvl(IV.notifyno,'') as เลขที่รับแจ้ง,
       nvl(IV.gropremamt,'') as ค่าเบี้ยสมัครใจ,
       nvl(IV.whtaxamt,0) as "หัก ณ ที่จ่าย",
       to_char(to_date(to_char(IV.efftdte,'dd/MM/yyyy','NLS_CALENDAR=''THAI BUDDHA'' NLS_DATE_LANGUAGE=THAI'),'dd/MM/yyyy'),'dd/MM/yyyy') as วันเริ่มคุ้มครอง,
       to_char(to_date(to_char(IV.expdte,'dd/MM/yyyy','NLS_CALENDAR=''THAI BUDDHA'' NLS_DATE_LANGUAGE=THAI'),'dd/MM/yyyy'),'dd/MM/yyyy') as วันสิ้นสุดคุ้มครอง,
       nvl(IV.recpretype,'') as จ่ายประกันโดย, 
       case  when IV.gtyPayTyp = 'C' Then 'เงินสด'
             when IV.gtyPayTyp = 'D' Then 'หักหน้าเช็ครวมยอดจัด'
             when IV.gtyPayTyp = 'Q' Then 'เงินโอน'
             else ' '
       end as "วิธีการชำระเงิน", 
       nvl(IC.inscmpname,'') as "บริษัทประกันภัย ",
       nvl(IC.instypname,'') as "ประกันภัย(ภาคบังคับ)",
       nvl(IC.gropremamt,'') as "ค่าเบี้ย พรบ.",
       nvl(IC.whtaxamt,0) as "หัก ณ ที่จ่าย ",
       to_char(to_date(to_char(IC.efftdte,'dd/MM/yyyy','NLS_CALENDAR=''THAI BUDDHA'' NLS_DATE_LANGUAGE=THAI'),'dd/MM/yyyy'),'dd/MM/yyyy') as "วันเริ่มคุ้มครอง ",
       to_char(to_date(to_char(IC.expdte,'dd/MM/yyyy','NLS_CALENDAR=''THAI BUDDHA'' NLS_DATE_LANGUAGE=THAI'),'dd/MM/yyyy'),'dd/MM/yyyy') as "วันสิ้นสุดคุ้มครอง ",
       nvl(IC.recpretype,'') as "จ่ายประกันโดย ",
       case  when IC.gtyPayTyp = 'C' Then 'เงินสด'
             when IC.gtyPayTyp = 'D' Then 'หักหน้าเช็ครวมยอดจัด'
             when IC.gtyPayTyp = 'Q' Then 'เงินโอน'
             else ' '
       end as "วิธีการชำระเงิน ", 
       nvl(IM.inscmpname,'') as บริษัทประกันชีวิต,
       nvl(IM.instypname,'') as ประเภทประกันชีวิต,
       nvl(IM.gropremamt,'') as ค่าเบี้ยประกันชีวิต,
       nvl(IM.whtaxamt,0) as "หัก ณ ที่จ่าย  ",
       to_char(to_date(to_char(IM.efftdte,'dd/MM/yyyy','NLS_CALENDAR=''THAI BUDDHA'' NLS_DATE_LANGUAGE=THAI'),'dd/MM/yyyy'),'dd/MM/yyyy') as "วันเริ่มคุ้มครอง  ",
       to_char(to_date(to_char(IM.expdte,'dd/MM/yyyy','NLS_CALENDAR=''THAI BUDDHA'' NLS_DATE_LANGUAGE=THAI'),'dd/MM/yyyy'),'dd/MM/yyyy') as "วันสิ้นสุดคุ้มครอง  ",
       nvl(IM.recpretype,'') as "จ่ายประกันโดย  ",
       case  when IM.gtyPayTyp = 'C' Then 'เงินสด'
             when IM.gtyPayTyp = 'D' Then 'หักหน้าเช็ครวมยอดจัด'
             when IM.gtyPayTyp = 'Q' Then 'เงินโอน'
             else ' '
       end as "วิธีการชำระเงิน  ",

       f.gtycustno1 as "รหัสผู้ค้ำ1",
       f.GIdno1 as "เลขบัตรผู้ค้ำ1",
       f.GName1 as "ชื่อผู้ค้ำ1",
       f.gtycustno2 as "รหัสผู้ค้ำ2",
       f.GIdno2 as "เลขบัตรผู้ค้ำ2",
       f.GName2 as "ชื่อผู้ค้ำ2",
       f.gtycustno3 as "รหัสผู้ค้ำ3",
       f.GIdno3 as "เลขบัตรผู้ค้ำ3",
       f.GName3 as "ชื่อผู้ค้ำ3",
       f.gtycustno4 as "รหัสผู้ค้ำ4",
       f.GIdno4 as "เลขบัตรผู้ค้ำ4",
       f.GName4 as "ชื่อผู้ค้ำ4", 
       case when nvl(a.contsts,' ') = 'A' then 'Active' 
            when nvl(a.contsts,' ') = 'E' then 'ยกเลิก'
            else ''
       end as "สถานะ",
       case when nvl(a.contsts,' ') = 'E' then 
            to_char(to_date(to_char(a.settledte,'dd/MM/yyyy','NLS_CALENDAR=''THAI BUDDHA'' NLS_DATE_LANGUAGE=THAI'),'dd/MM/yyyy'),'dd/MM/yyyy') 
       else null end as "วันที่ยกเลิก"

from hpmshp05 a
left join HPTBHP31 d on (a.cmpcde=d.cmpcde and a.loanoff=d.loanoff and d.recsts='A')
left join HPMSHP00 l on (a.cmpcde=l.cmpcde and a.custno=l.custno)
left join hptbhp03 m on (l.cmpcde=m.cmpcde and l.titcde=m.titcde and m.recsts='A' )
left join (select d.cmpcde,d.offcde,d.contno,d.category,d.expdte,d.engno,d.oldnew,d.horpwr,
                   d.chasno,d.province,d.licno,j.thdesc as modelname,e.thdesc as brandname,f.thdesc as provName
               from hpmsrg01 d
              left join hptbhp27 e
                on (d.cmpcde = e.cmpcde and d.brand = e.brand)
              left join hptbhp28_g j
                on (d.cmpcde = j.cmpcde and d.brand = j.brand and
                   d.modelgrpcde = j.modelgrpcde)              
              left join hptbhp14 f
                on (d.cmpcde = f.cmpcde and d.province = f.province)
                ) dd 
   on (a.cmpcde = dd.cmpcde and a.offcde = dd.offcde and a.contno = dd.contno)
left join hptbhp25 h on (dd.cmpcde = h.cmpcde and dd.category = h.category)
left join hptbhp29 z1 on (a.cmpcde = z1.cmpcde and a.deacde = z1.deacde)
left join hptbhp38 e on (a.cmpcde=e.cmpcde and a.loantyp=e.loantyp and e.recsts='A' )
left  join hpmshp09h f on (a.cmpcde = f.cmpcde and a.offcde = f.offcde and a.contno = f.contno)
/*ค่า Commission*/
LEFT JOIN (select a.cmpcde,a.offcde,a.contno,a.commcde,
                    sum(a.commamt) as commamtCH,sum(a.netamt) as netamtCH,sum(a.commexamt) as commexamtCH,sum(a.comper) as comperCH
             from HPMSHP05_C a 
             where a.recsts = 'A' and commcde = 'CH1'
             group by a.cmpcde,a.offcde,a.contno,a.commcde) CH1 on a.cmpcde = CH1.cmpcde and a.offcde = CH1.offcde and a.contno = CH1.contno
LEFT JOIN (select a.cmpcde,a.offcde,a.contno,a.commcde,
                 sum(a.commamt) as commamtCL,sum(a.netamt) as netamtCL,sum(a.commexamt) as commexamtCL
           from HPMSHP05_C a
           where a.recsts = 'A' and commcde = 'CL1'
           group by a.cmpcde,a.offcde,a.contno,a.commcde) CL1 on a.cmpcde = CL1.cmpcde and a.offcde = CL1.offcde and a.contno = CL1.contno
/*ค่าคอมฯ พนักงานขาย (Marketing)*/ 
LEFT JOIN (select a.cmpcde,a.offcde,a.contno,a.commcde,
                    sum(a.commamt) as commamtCH,sum(a.netamt) as netamtCH,sum(a.commexamt) as commexamtCH,sum(a.comper) as comperCH
             from HPMSHP05_C a 
             where a.recsts = 'A' and commcde = 'CH3'
             group by a.cmpcde,a.offcde,a.contno,a.commcde) CH3 on a.cmpcde = CH3.cmpcde and a.offcde = CH3.offcde and a.contno = CH3.contno
LEFT JOIN (select a.cmpcde,a.offcde,a.contno,a.commcde,
                 sum(a.commamt) as commamtCL,sum(a.netamt) as netamtCL,sum(a.commexamt) as commexamtCL
           from HPMSHP05_C a
           where a.recsts = 'A' and commcde = 'CL3'
           group by a.cmpcde,a.offcde,a.contno,a.commcde) CL3 on a.cmpcde = CL3.cmpcde and a.offcde = CL3.offcde and a.contno = CL3.contno
-- Insurance -- 
left join (select a.cmpcde,a.offcde,a.contno,a.inscmp,b.thname as inscmpname,
                  a.instyp,c.thdesc as instypname,a.seq,
                  a.notifyno,a.gropremamt,d.thdesc as recpretype,
                  a.efftdte,a.expdte,a.Ownins,a.whtaxamt,a.gtyPayTyp
           from hpmsis01 a
           left join hptbis01 b on (a.cmpcde = b.cmpcde and a.inscmp = b.inscmp and b.recsts = 'A')
           left join hptbis02 c on (a.cmpcde = c.cmpcde and a.instyp = c.instyp and c.recsts = 'A')
           left join hptbhp93 d on (a.cmpcde = d.cmpcde and d.prmtyp = 'INSOWNER' and a.Ownins = d.prmflag)
           where a.recsts = 'A' and c.instypflag = 'V' and a.insyearno = 1
           )IV on a.cmpcde = IV.cmpcde and a.offcde = IV.offcde and a.contno = IV.contno
left join (select a.cmpcde,a.offcde,a.contno,a.inscmp,b.thname as inscmpname,
                  a.instyp,c.thdesc as instypname,a.seq,
                  a.notifyno,a.gropremamt,d.thdesc as recpretype,
                  a.efftdte,a.expdte,a.Ownins,a.whtaxamt,a.gtyPayTyp
           from hpmsis01 a
           left join hptbis01 b on (a.cmpcde = b.cmpcde and a.inscmp = b.inscmp and b.recsts = 'A')
           left join hptbis02 c on (a.cmpcde = c.cmpcde and a.instyp = c.instyp and c.recsts = 'A')
           left join hptbhp93 d on (a.cmpcde = d.cmpcde and d.prmtyp = 'INSOWNER' and a.Ownins = d.prmflag)
           where a.recsts = 'A' and c.instypflag = 'C' and a.insyearno = 1
           )IC on a.cmpcde = IC.cmpcde and a.offcde = IC.offcde and a.contno = IC.contno
left join (select a.cmpcde,a.offcde,a.contno,a.inscmp,b.thname as inscmpname, 
                  a.instyp,c.thdesc as instypname,a.seq,
                  a.notifyno,a.gropremamt,d.thdesc as recpretype,
                  a.efftdte,a.expdte,a.Ownins,a.whtaxamt,a.gtyPayTyp
           from hpmsis01 a 
           left join hptbis01 b on (a.cmpcde = b.cmpcde and a.inscmp = b.inscmp and b.recsts = 'A')
           left join hptbis02 c on (a.cmpcde = c.cmpcde and a.instyp = c.instyp and c.recsts = 'A')
           left join hptbhp93 d on (a.cmpcde = d.cmpcde and d.prmtyp = 'INSOWNER' and a.Ownins = d.prmflag)
           where a.recsts = 'A' and c.instypflag in ('M','L') and a.insyearno = 1
           )IM on a.cmpcde = IM.cmpcde and a.offcde = IM.offcde and a.contno = IM.contno
/*left join hptbhp91 z on (a.cmpcde = z.cmpcde and z.prmtyp = 'CONTRACTYP' and a.contyp = z.prmcde) */
left join hptbhp91 z on (a.cmpcde = z.cmpcde and z.prmtyp = 'HPLOANTYPE' and a.applLoanTyp = z.prmcde)
where 
   nvl(a.appcde,' ')<>'FL'
   and a.cmpcde = :cmpcde
   /*and a.recsts = 'A'*/
   and nvl(a.offcde,' ') = case nvl(:offcde,' ') when ' ' then nvl(a.offcde,' ') else nvl(:offcde,' ') end
   and case when to_date(nvl(:fromdte, '01/01/1990'), 'dd/MM/yyyy') = to_date('01/01/1990', 'dd/MM/yyyy') then to_date('01/01/1990', 'dd/MM/yyyy')
       else case when nvl(:status,' ') = 'E' then to_date(to_char(a.settledte, 'dd/MM/yyyy'), 'dd/MM/yyyy') 
                else to_date(to_char(a.aprvdte, 'dd/MM/yyyy'), 'dd/MM/yyyy') end end BETWEEN
            case when to_date(nvl(:fromdte, '01/01/1990'), 'dd/MM/yyyy') = to_date('01/01/1990', 'dd/MM/yyyy')  then to_date('01/01/1990', 'dd/MM/yyyy')
            else to_date(:fromdte,'dd/MM/yyyy') end AND
            case when to_date(nvl(:todte, '01/01/1990'), 'dd/MM/yyyy') = to_date('01/01/1990', 'dd/MM/yyyy')  then to_date('01/01/1990', 'dd/MM/yyyy')
            else to_date(:todte,'dd/MM/yyyy')
   end 
   and case when nvl(:status,' ') in(' ','T') then '1' else nvl(a.contsts, ' ') end = 
       case when nvl(:status,' ') in(' ','T') then '1' else nvl(:status,' ') end
--order by a.offcde,a.loantyp,a.contno
order by to_char(to_date(to_char(a.aprvdte,'dd/MM/yyyy','NLS_CALENDAR=''THAI BUDDHA'' NLS_DATE_LANGUAGE=THAI'),'dd/MM/yyyy'),'dd/MM/yyyy'),a.contno