|
1
|
select distinct
|
|
2
|
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 วันที่ทำสัญญา,
|
|
3
|
a.applno as "Appl No.",
|
|
4
|
a.offcde as สาขา,
|
|
5
|
a.contno as เลขที่สัญญา,
|
|
6
|
a.custno as รหัสลูกค้า,
|
|
7
|
a.idno as เลขบัตรลูกค้า,
|
|
8
|
case l.namformat when 'O' then rtrim(l.cusname) when 'N' then m.thdesc || l.thname || ' ' || l.thsurn end as ชื่อลูกค้า,
|
|
9
|
a.deacde as "Dealer Code",
|
|
10
|
z1.thname as "ชื่อผู้แทนจำหน่าย",
|
|
11
|
a.loanoff as "Marketing Code",
|
|
12
|
nvl(d.thname,' ') as "เจ้าหน้าที่การตลาด",
|
|
13
|
case when dd.oldnew = 'O' and nvl(a.carvat, 0.00) > 0 Then 'รถเก่ามีภาษีซื้อ'
|
|
14
|
when dd.oldnew = 'O' and nvl(a.carvat, 0.00) = 0 Then 'รถเก่าไม่มีภาษีซื้อ'
|
|
15
|
when dd.oldnew = 'N' and nvl(a.carvat, 0.00) > 0 Then 'รถใหม่มีภาษีซื้อ'
|
|
16
|
else 'รถใหม่ไม่มีภาษีซื้อ'
|
|
17
|
end สภาพรถ,
|
|
18
|
|
|
19
|
dd.brandname as ยี่ห้อรถ,
|
|
20
|
dd.modelname as รายละเอียดรุ่น,
|
|
21
|
|
|
22
|
dd.engno as เลขเครื่อง,
|
|
23
|
dd.chasno as เลขตัวถัง,
|
|
24
|
dd.horpwr as "CC",
|
|
25
|
--------------------
|
|
26
|
z.prmcde as CAMPAIGN,
|
|
27
|
z.thdesc as "CAMPAIGN Desc",
|
|
28
|
a.loantyp as รหัสประเภทสัญญา,
|
|
29
|
e.thdesc as ประเภทสัญญา,
|
|
30
|
--------------------
|
|
31
|
a.firstlasttyp as "ประเภทการชำระ E/B",
|
|
32
|
h.category as รหัสลักษณะรถ,
|
|
33
|
h.thdesc as ลักษณะรถ,
|
|
34
|
dd.licno as ทะเบียน,
|
|
35
|
dd.provName as จังหวัด,
|
|
36
|
nvl(a.caramt, 0.00) as "ราคารถ(ก่อน VAT)",
|
|
37
|
nvl(a.carvat, 0.00) as "ราคารถ(VAT)",
|
|
38
|
nvl(a.caramt, 0.00)+nvl(a.carvat, 0.00) as "ราคารถ (รวมVAT)",
|
|
39
|
nvl(a.downpay,0) as "เงินดาวน์(ก่อน VAT)",
|
|
40
|
nvl(a.downvat,0) as "เงินดาวน์(VAT)",
|
|
41
|
nvl(a.downpay,0)+nvl(a.downvat,0) as "เงินดาวน์(รวม VAT)",
|
|
42
|
-------------------
|
|
43
|
/*nvl(a.hirpamt, 0.00) as "ยอดจัด(ก่อน VAT)",
|
|
44
|
nvl(a.hirpvat, 0.00) as "ยอดจัด(VAT)",
|
|
45
|
nvl(a.hirpamt, 0.00)+nvl(a.hirpvat, 0.00) as "ยอดจัด(รวม VAT)",*/
|
|
46
|
nvl(a.finamt,0) as "ยอดจัด(ก่อน VAT)",
|
|
47
|
nvl(a.finvat,0) as "ยอดจัด(VAT)",
|
|
48
|
nvl(a.finamt,0) + nvl(a.finvat,0) as "ยอดจัด(รวม VAT)",
|
|
49
|
-------------------
|
|
50
|
a.term as จำนวนงวด,
|
|
51
|
a.rate as อัตราดอกเบี้ย,
|
|
52
|
nvl(a.instamt,0) as "ค่างวด(ก่อน VAT)",
|
|
53
|
nvl(a.instvat,0) as "ค่างวด(VAT)",
|
|
54
|
nvl(a.instamt,0) + nvl(instvat,0) as "ค่างวด(หลัง VAT)",
|
|
55
|
nvl(a.lastinstallamt,0) as "ค่างวดสุดท้าย(ก่อน VAT)",
|
|
56
|
nvl(a.lastinstallvat,0) as "ค่างวดสุดท้าย(VAT)",
|
|
57
|
nvl(a.lastinstallamt,0)+nvl(a.lastinstallvat,0) as"ค่างวดสุดท้าย(หลัง VAT)",
|
|
58
|
nvl(a.hiramt, 0.00) as "ดอกผลเช่าซื้อ(ก่อน VAT)",
|
|
59
|
nvl(a.hirvat, 0.00) as "ดอกผลเช่าซื้อ(VAT)",
|
|
60
|
nvl(a.hiramt, 0.00) + nvl(a.hirvat, 0.00) as "ดอกผลเช่าซื้อ(หลัง VAT)",
|
|
61
|
-------------------
|
|
62
|
nvl(a.hirpamt, 0.00) as "ยอดเช่าซื้อ (ก่อน VAT)",
|
|
63
|
nvl(a.hirpvat, 0.00) as "ยอดเช่าซื้อ (VAT)",
|
|
64
|
nvl(a.hirpamt, 0.00)+nvl(a.hirpvat, 0.00) as "ยอดเช่าซื้อ (หลัง VAT)",
|
|
65
|
-------------------
|
|
66
|
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 "วันที่ชำระงวดแรก",
|
|
67
|
a.spehirch as "รายได้ชดเชย (ก่อน VAT)",
|
|
68
|
--a.spehirchvat as "รายได้ชดเชย (VAT)"
|
|
69
|
--a.spehirchtax as "รายได้ชดเชย (TAX)"
|
|
70
|
--a.spehirchnet as "รายได้ชดเชย",
|
|
71
|
a.spehirchper as "Subsidy%",
|
|
72
|
nvl(CH1.comperCH,0) as "Com%",
|
|
73
|
nvl(CH1.commexamtCH,0) as "ค่าคอม (ก่อน VAT)",
|
|
74
|
nvl(CL1.commexamtCL,0) as "Extra(ก่อน VAT)",
|
|
75
|
--nvl(CH1.netamtCH,0) as ค่าคอม,
|
|
76
|
--nvl(CL1.netamtCL,0) as Extra,
|
|
77
|
nvl(CH3.comperCH,0) as "Com% Marketing",
|
|
78
|
nvl(CH3.commexamtCH,0) as "ค่าคอม (ก่อน VAT) Marketing",
|
|
79
|
nvl(CL3.commexamtCL,0) as "Extra (ก่อน VAT) Marketing",
|
|
80
|
a.dutyamt as อากร,
|
|
81
|
a.EFFRATE as EFFRATE,
|
|
82
|
-----------
|
|
83
|
round(a.IRRRATE,4) as IRRRATE,
|
|
84
|
-----------
|
|
85
|
nvl(IV.INSCMP,'') as "Code ประกันภัย",
|
|
86
|
nvl(IV.inscmpname,'') as บริษัทประกัน,
|
|
87
|
nvl(IV.instypname,'') as ชั้นประกัน,
|
|
88
|
nvl(IV.notifyno,'') as เลขที่รับแจ้ง,
|
|
89
|
nvl(IV.gropremamt,'') as ค่าเบี้ยสมัครใจ,
|
|
90
|
nvl(IV.whtaxamt,0) as "หัก ณ ที่จ่าย",
|
|
91
|
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 วันเริ่มคุ้มครอง,
|
|
92
|
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 วันสิ้นสุดคุ้มครอง,
|
|
93
|
nvl(IV.recpretype,'') as จ่ายประกันโดย,
|
|
94
|
case when IV.gtyPayTyp = 'C' Then 'เงินสด'
|
|
95
|
when IV.gtyPayTyp = 'D' Then 'หักหน้าเช็ครวมยอดจัด'
|
|
96
|
when IV.gtyPayTyp = 'Q' Then 'เงินโอน'
|
|
97
|
else ' '
|
|
98
|
end as "วิธีการชำระเงิน",
|
|
99
|
nvl(IC.inscmpname,'') as "บริษัทประกันภัย ",
|
|
100
|
nvl(IC.instypname,'') as "ประกันภัย(ภาคบังคับ)",
|
|
101
|
nvl(IC.gropremamt,'') as "ค่าเบี้ย พรบ.",
|
|
102
|
nvl(IC.whtaxamt,0) as "หัก ณ ที่จ่าย ",
|
|
103
|
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 "วันเริ่มคุ้มครอง ",
|
|
104
|
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 "วันสิ้นสุดคุ้มครอง ",
|
|
105
|
nvl(IC.recpretype,'') as "จ่ายประกันโดย ",
|
|
106
|
case when IC.gtyPayTyp = 'C' Then 'เงินสด'
|
|
107
|
when IC.gtyPayTyp = 'D' Then 'หักหน้าเช็ครวมยอดจัด'
|
|
108
|
when IC.gtyPayTyp = 'Q' Then 'เงินโอน'
|
|
109
|
else ' '
|
|
110
|
end as "วิธีการชำระเงิน ",
|
|
111
|
nvl(IM.inscmpname,'') as บริษัทประกันชีวิต,
|
|
112
|
nvl(IM.instypname,'') as ประเภทประกันชีวิต,
|
|
113
|
nvl(IM.gropremamt,'') as ค่าเบี้ยประกันชีวิต,
|
|
114
|
nvl(IM.whtaxamt,0) as "หัก ณ ที่จ่าย ",
|
|
115
|
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 "วันเริ่มคุ้มครอง ",
|
|
116
|
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 "วันสิ้นสุดคุ้มครอง ",
|
|
117
|
nvl(IM.recpretype,'') as "จ่ายประกันโดย ",
|
|
118
|
case when IM.gtyPayTyp = 'C' Then 'เงินสด'
|
|
119
|
when IM.gtyPayTyp = 'D' Then 'หักหน้าเช็ครวมยอดจัด'
|
|
120
|
when IM.gtyPayTyp = 'Q' Then 'เงินโอน'
|
|
121
|
else ' '
|
|
122
|
end as "วิธีการชำระเงิน ",
|
|
123
|
|
|
124
|
f.gtycustno1 as "รหัสผู้ค้ำ1",
|
|
125
|
f.GIdno1 as "เลขบัตรผู้ค้ำ1",
|
|
126
|
f.GName1 as "ชื่อผู้ค้ำ1",
|
|
127
|
f.gtycustno2 as "รหัสผู้ค้ำ2",
|
|
128
|
f.GIdno2 as "เลขบัตรผู้ค้ำ2",
|
|
129
|
f.GName2 as "ชื่อผู้ค้ำ2",
|
|
130
|
f.gtycustno3 as "รหัสผู้ค้ำ3",
|
|
131
|
f.GIdno3 as "เลขบัตรผู้ค้ำ3",
|
|
132
|
f.GName3 as "ชื่อผู้ค้ำ3",
|
|
133
|
f.gtycustno4 as "รหัสผู้ค้ำ4",
|
|
134
|
f.GIdno4 as "เลขบัตรผู้ค้ำ4",
|
|
135
|
f.GName4 as "ชื่อผู้ค้ำ4",
|
|
136
|
case when nvl(a.contsts,' ') = 'A' then 'Active'
|
|
137
|
when nvl(a.contsts,' ') = 'E' then 'ยกเลิก'
|
|
138
|
else ''
|
|
139
|
end as "สถานะ",
|
|
140
|
case when nvl(a.contsts,' ') = 'E' then
|
|
141
|
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')
|
|
142
|
else null end as "วันที่ยกเลิก"
|
|
143
|
|
|
144
|
from hpmshp05 a
|
|
145
|
left join HPTBHP31 d on (a.cmpcde=d.cmpcde and a.loanoff=d.loanoff and d.recsts='A')
|
|
146
|
left join HPMSHP00 l on (a.cmpcde=l.cmpcde and a.custno=l.custno)
|
|
147
|
left join hptbhp03 m on (l.cmpcde=m.cmpcde and l.titcde=m.titcde and m.recsts='A' )
|
|
148
|
left join (select d.cmpcde,d.offcde,d.contno,d.category,d.expdte,d.engno,d.oldnew,d.horpwr,
|
|
149
|
d.chasno,d.province,d.licno,j.thdesc as modelname,e.thdesc as brandname,f.thdesc as provName
|
|
150
|
from hpmsrg01 d
|
|
151
|
left join hptbhp27 e
|
|
152
|
on (d.cmpcde = e.cmpcde and d.brand = e.brand)
|
|
153
|
left join hptbhp28_g j
|
|
154
|
on (d.cmpcde = j.cmpcde and d.brand = j.brand and
|
|
155
|
d.modelgrpcde = j.modelgrpcde)
|
|
156
|
left join hptbhp14 f
|
|
157
|
on (d.cmpcde = f.cmpcde and d.province = f.province)
|
|
158
|
) dd
|
|
159
|
on (a.cmpcde = dd.cmpcde and a.offcde = dd.offcde and a.contno = dd.contno)
|
|
160
|
left join hptbhp25 h on (dd.cmpcde = h.cmpcde and dd.category = h.category)
|
|
161
|
left join hptbhp29 z1 on (a.cmpcde = z1.cmpcde and a.deacde = z1.deacde)
|
|
162
|
left join hptbhp38 e on (a.cmpcde=e.cmpcde and a.loantyp=e.loantyp and e.recsts='A' )
|
|
163
|
left join hpmshp09h f on (a.cmpcde = f.cmpcde and a.offcde = f.offcde and a.contno = f.contno)
|
|
164
|
/*ค่า Commission*/
|
|
165
|
LEFT JOIN (select a.cmpcde,a.offcde,a.contno,a.commcde,
|
|
166
|
sum(a.commamt) as commamtCH,sum(a.netamt) as netamtCH,sum(a.commexamt) as commexamtCH,sum(a.comper) as comperCH
|
|
167
|
from HPMSHP05_C a
|
|
168
|
where a.recsts = 'A' and commcde = 'CH1'
|
|
169
|
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
|
|
170
|
LEFT JOIN (select a.cmpcde,a.offcde,a.contno,a.commcde,
|
|
171
|
sum(a.commamt) as commamtCL,sum(a.netamt) as netamtCL,sum(a.commexamt) as commexamtCL
|
|
172
|
from HPMSHP05_C a
|
|
173
|
where a.recsts = 'A' and commcde = 'CL1'
|
|
174
|
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
|
|
175
|
/*ค่าคอมฯ พนักงานขาย (Marketing)*/
|
|
176
|
LEFT JOIN (select a.cmpcde,a.offcde,a.contno,a.commcde,
|
|
177
|
sum(a.commamt) as commamtCH,sum(a.netamt) as netamtCH,sum(a.commexamt) as commexamtCH,sum(a.comper) as comperCH
|
|
178
|
from HPMSHP05_C a
|
|
179
|
where a.recsts = 'A' and commcde = 'CH3'
|
|
180
|
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
|
|
181
|
LEFT JOIN (select a.cmpcde,a.offcde,a.contno,a.commcde,
|
|
182
|
sum(a.commamt) as commamtCL,sum(a.netamt) as netamtCL,sum(a.commexamt) as commexamtCL
|
|
183
|
from HPMSHP05_C a
|
|
184
|
where a.recsts = 'A' and commcde = 'CL3'
|
|
185
|
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
|
|
186
|
-- Insurance --
|
|
187
|
left join (select a.cmpcde,a.offcde,a.contno,a.inscmp,b.thname as inscmpname,
|
|
188
|
a.instyp,c.thdesc as instypname,a.seq,
|
|
189
|
a.notifyno,a.gropremamt,d.thdesc as recpretype,
|
|
190
|
a.efftdte,a.expdte,a.Ownins,a.whtaxamt,a.gtyPayTyp
|
|
191
|
from hpmsis01 a
|
|
192
|
left join hptbis01 b on (a.cmpcde = b.cmpcde and a.inscmp = b.inscmp and b.recsts = 'A')
|
|
193
|
left join hptbis02 c on (a.cmpcde = c.cmpcde and a.instyp = c.instyp and c.recsts = 'A')
|
|
194
|
left join hptbhp93 d on (a.cmpcde = d.cmpcde and d.prmtyp = 'INSOWNER' and a.Ownins = d.prmflag)
|
|
195
|
where a.recsts = 'A' and c.instypflag = 'V' and a.insyearno = 1
|
|
196
|
)IV on a.cmpcde = IV.cmpcde and a.offcde = IV.offcde and a.contno = IV.contno
|
|
197
|
left join (select a.cmpcde,a.offcde,a.contno,a.inscmp,b.thname as inscmpname,
|
|
198
|
a.instyp,c.thdesc as instypname,a.seq,
|
|
199
|
a.notifyno,a.gropremamt,d.thdesc as recpretype,
|
|
200
|
a.efftdte,a.expdte,a.Ownins,a.whtaxamt,a.gtyPayTyp
|
|
201
|
from hpmsis01 a
|
|
202
|
left join hptbis01 b on (a.cmpcde = b.cmpcde and a.inscmp = b.inscmp and b.recsts = 'A')
|
|
203
|
left join hptbis02 c on (a.cmpcde = c.cmpcde and a.instyp = c.instyp and c.recsts = 'A')
|
|
204
|
left join hptbhp93 d on (a.cmpcde = d.cmpcde and d.prmtyp = 'INSOWNER' and a.Ownins = d.prmflag)
|
|
205
|
where a.recsts = 'A' and c.instypflag = 'C' and a.insyearno = 1
|
|
206
|
)IC on a.cmpcde = IC.cmpcde and a.offcde = IC.offcde and a.contno = IC.contno
|
|
207
|
left join (select a.cmpcde,a.offcde,a.contno,a.inscmp,b.thname as inscmpname,
|
|
208
|
a.instyp,c.thdesc as instypname,a.seq,
|
|
209
|
a.notifyno,a.gropremamt,d.thdesc as recpretype,
|
|
210
|
a.efftdte,a.expdte,a.Ownins,a.whtaxamt,a.gtyPayTyp
|
|
211
|
from hpmsis01 a
|
|
212
|
left join hptbis01 b on (a.cmpcde = b.cmpcde and a.inscmp = b.inscmp and b.recsts = 'A')
|
|
213
|
left join hptbis02 c on (a.cmpcde = c.cmpcde and a.instyp = c.instyp and c.recsts = 'A')
|
|
214
|
left join hptbhp93 d on (a.cmpcde = d.cmpcde and d.prmtyp = 'INSOWNER' and a.Ownins = d.prmflag)
|
|
215
|
where a.recsts = 'A' and c.instypflag in ('M','L') and a.insyearno = 1
|
|
216
|
)IM on a.cmpcde = IM.cmpcde and a.offcde = IM.offcde and a.contno = IM.contno
|
|
217
|
/*left join hptbhp91 z on (a.cmpcde = z.cmpcde and z.prmtyp = 'CONTRACTYP' and a.contyp = z.prmcde) */
|
|
218
|
left join hptbhp91 z on (a.cmpcde = z.cmpcde and z.prmtyp = 'HPLOANTYPE' and a.applLoanTyp = z.prmcde)
|
|
219
|
where
|
|
220
|
nvl(a.appcde,' ')<>'FL'
|
|
221
|
and a.cmpcde = :cmpcde
|
|
222
|
/*and a.recsts = 'A'*/
|
|
223
|
and nvl(a.offcde,' ') = case nvl(:offcde,' ') when ' ' then nvl(a.offcde,' ') else nvl(:offcde,' ') end
|
|
224
|
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')
|
|
225
|
else case when nvl(:status,' ') = 'E' then to_date(to_char(a.settledte, 'dd/MM/yyyy'), 'dd/MM/yyyy')
|
|
226
|
else to_date(to_char(a.aprvdte, 'dd/MM/yyyy'), 'dd/MM/yyyy') end end BETWEEN
|
|
227
|
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')
|
|
228
|
else to_date(:fromdte,'dd/MM/yyyy') end AND
|
|
229
|
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')
|
|
230
|
else to_date(:todte,'dd/MM/yyyy')
|
|
231
|
end
|
|
232
|
and case when nvl(:status,' ') in(' ','T') then '1' else nvl(a.contsts, ' ') end =
|
|
233
|
case when nvl(:status,' ') in(' ','T') then '1' else nvl(:status,' ') end
|
|
234
|
--order by a.offcde,a.loantyp,a.contno
|
|
235
|
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
|
