· 8 years ago · Jun 03, 2018, 10:18 PM
1IF OBJECT_ID("#ref") IS NOT NULL
2 DROP TABLE #ref
3 GO
4
5 IF OBJECT_ID("#tmp_working") IS NOT NULL
6 DROP TABLE #tmp_working
7 GO
8
9 IF OBJECT_ID("#tmp_parent_ds") IS NOT NULL
10 DROP TABLE #tmp_parent_ds
11 GO
12
13 IF OBJECT_ID("#tmp_child_du") IS NOT NULL
14 DROP TABLE #tmp_child_du
15 GO
16
17 IF OBJECT_ID("#tmp_idle_service") IS NOT NULL
18 DROP TABLE #tmp_idle_service
19 GO
20
21 IF OBJECT_ID("#tmp_services") IS NOT NULL
22 DROP TABLE #tmp_services
23 GO
24
25 IF OBJECT_ID("#tmp_Fixed_Fee") IS NOT NULL
26 DROP TABLE #tmp_Fixed_Fee
27 GO
28
29--BEGIN
30 DECLARE
31 @rfBPCustomerBillingPeriod NUMERIC(12),
32 @rfFMCustomer NUMERIC(12),
33 @daEffective DATETIME
34
35 SELECT cbp.rfBPCustomerBillingPeriod
36 , cbp.rfFMCustomer
37 , cbp.daEffective
38 INTO #ref
39 FROM BPCustomerBillingPeriod cbp
40 WHERE rfBPCustomerBillingPeriod = 11757 --:rfBPCustomerBillingPeriod
41
42 SELECT @rfBPCustomerBillingPeriod = rfBPCustomerBillingPeriod
43 , @rfFMCustomer = rfFMCustomer
44 , @daEffective = daEffective
45 FROM #ref
46
47 -- Get List of working services
48 SELECT sd.rfFMService
49 , sd.rfFMServiceDetail
50 , sd.rfFMServiceStatus
51 INTO #tmp_working
52 FROM FMServiceDetail sd
53 , FMServiceStatus ss
54 WHERE ss.rfFMServiceStatus = sd.rfFMServiceStatus
55 AND sd.rfFMCustomer = @rfFMCustomer
56 AND @daEffective BETWEEN sd.daFrom AND sd.daTo
57
58
59 -- Get list of services which have been billed into BPChargeLoad (parent) with DS
60 SELECT rfBPChargeLoad, parent_rfBPChargeLoad, rfFMService, mnAmount, mnGST
61 INTO #tmp_parent_ds
62 FROM BPInvoiceLoad il
63 , BPChargeLoad cl
64 WHERE il.rfBPInvoiceLoad = cl.rfBPInvoiceLoad
65 AND cl.txSourceTransType = "DS"
66 AND il.rfBPCustomerBillingperiod = @rfBPCustomerBillingperiod
67
68
69 -- Get list of services which have been billed into BPChargeLoad (child) with DU
70 SELECT rfBPChargeLoad, parent_rfBPChargeLoad, rfFMService, mnAmount, mnGST
71 INTO #tmp_child_du
72 FROM BPInvoiceLoad il
73 , BPChargeLoad cl
74 WHERE il.rfBPInvoiceLoad = cl.rfBPInvoiceLoad
75 AND cl.parent_rfBPChargeLoad IS NOT NULL
76 AND cl.txSourceTransType = "DU"
77 AND il.rfBPCustomerBillingperiod = @rfBPCustomerBillingperiod
78
79
80 -- Get List of Idle/Unused services without usage
81 SELECT rfFMService, SUM(mnAmount) mnAmount, SUM(mnGST) mnGST
82 INTO #tmp_idle_service
83 FROM #tmp_parent_ds ds
84 WHERE NOT EXISTS (SELECT "x" FROM #tmp_child_du du WHERE du.parent_rfBPChargeload = ds.rfBPChargeload)
85 GROUP BY rfFMService
86
87
88 -- Get List of Fixed Fee's details
89 SELECT pd.rfFMService, pd.FMPriceDetail, pd.txBrief, pd.txDescription, 0 AS mnAmount
90 INTO #tmp_Fixed_Fee
91 FROM FMServicePriceDetail spd, FMPriceDetail pd, #tmp_idle_service s
92 WHERE spd.rfFMPriceDetail = pd.rfFMPriceDetail
93 AND spd.rfBPCustomerBillingPeriod = @rfBPCustomerBillingperiod
94 AND spd.rfFMService = s.rfFMService
95
96 UPDATE #tmp_Fixed_Fee
97 SET mnAmount =
98 ISNULL(SELECT pdc.mnAmount FROM FMPriceDetailCharge pdc
99 WHERE (pdc.rfFMPriceDetail = t.rfFMPriceDetail)
100 AND (@daEffective BETWEEN pdc.daFrom AND pdc.daTo), 0)
101 FROM #tmp_Fixed_Fee t
102/*
103 SELECT pd.rfFMService, pd.txBrief, pd.txDescription
104 , (SELECT pdc.mnAmount FROM FMPriceDetailCharge pdc
105 WHERE (pdc.rfFMPriceDetail = pd.rfFMPriceDetail)
106 AND (@daEffective BETWEEN pdc.daFrom AND pdc.daTo)) AS mnAmount
107 INTO #tmp_Fixed_Fee
108 FROM FMServicePriceDetail spd, FMPriceDetail pd, #tmp_idle_service s
109 WHERE spd.rfFMPriceDetail = pd.rfFMPriceDetail
110 AND spd.rfBPCustomerBillingPeriod = @rfBPCustomerBillingperiod
111 AND spd.rfFMService = s.rfFMService
112*/
113
114 CREATE INDEX #tmp_idle_service_idx1 ON #tmp_idle_service (rfFMService)
115 CREATE INDEX #tmp_working_idx1 ON #tmp_working (rfFMService)
116
117 -- Force an Update Stats to tell Sybase that the indexes are ready for use.
118 -- If you dont do an update stats then the indexes wont be available to the
119 -- Sybase Optimizer
120 UPDATE STATISTICS #tmp_idle_service
121 UPDATE STATISTICS #tmp_working
122
123--END
124GO
125
126--BEGIN
127 -- we have got the services we need now... so lets get the rest of the info
128 DECLARE
129 @rfBPCustomerBillingPeriod NUMERIC(12),
130 @rfFMCustomer NUMERIC(12),
131 @daEffective DATETIME
132
133 SELECT @rfBPCustomerBillingPeriod = rfBPCustomerBillingPeriod
134 ,@rfFMCustomer = rfFMCustomer
135 ,@daEffective = daEffective
136 FROM #ref
137
138 -- Get details for services which are in the #tmp_Working table
139 SELECT DISTINCT SD.rfFMService
140 , (SELECT rfFMServiceClass FROM FMServiceDetail ssd
141 WHERE ssd.rfFMServiceDetail=sd.rfFMServiceDetail) AS rfFMServiceClass
142 , SD.rfFMServiceStatus
143 , BUD.txDescription
144 , t.mnAmount
145 , t.mnGST
146 , @daEffective AS daEffective
147 , CONVERT(NUMERIC(12,0),0) AS rfFMServiceProvider
148 INTO #tmp_services
149 FROM #tmp_idle_service t
150 , #tmp_working SD
151 , FMAllocation AL
152 , FMBusinessUnitDetail BUD
153 WHERE SD.rfFMService = t.rfFMService
154 AND AL.rfFMService = SD.rfFMService
155 AND AL.rfFMBusinessUnit = BUD.rfFMBusinessUnit
156 AND @daEffective BETWEEN AL.daFrom AND AL.daTo
157 AND @daEffective BETWEEN BUD.daFrom AND BUD.daTo
158
159 -- Get the most recently billed FMServiceProvider ref value
160 -- If more than one for the same daLastBilled then use the record with
161 -- the max rfFMServiceProvide value
162 UPDATE #tmp_Services
163 SET rfFMServiceProvider =
164 ISNULL((SELECT MAX(rfFMServiceProvider) FROM FMServiceProvider WHERE rfFMService = t.rfFMService
165 AND daLastBilled = (SELECT MAX(daLastBilled) FROM FMServiceProvider WHERE rfFMService = t.rfFMService)), 0)
166 FROM #tmp_services t
167
168--END
169
170-- Output Section
171--BEGIN
172 SELECT DISTINCT sv.txServiceNumber AS Service
173 , sv.txExtensionNumber AS Extension
174 , CONVERT(VARCHAR,sv.dtCreateDateTimeStamp,103) AS Date_Created
175 , t.txDescription AS Cost_Centre_Desc
176 , (SELECT txDescription FROM FMSupplier su, FMServiceProvider sp
177 WHERE su.rfFMSupplier=sp.rfFMSupplier AND sp.rfFMServiceProvider = t.rfFMServiceProvider) AS Supplier
178 , (SELECT txAccountCode FROM BPBillingAccount ba , FMServiceProvider sp
179 WHERE ba.rfBPBillingAccount = sp.rfBPBillingAccount AND sp.rfFMServiceProvider = t.rfFMServiceProvider ) AS Billing_Account
180 , sc.txDescription Service_Class
181 , (SELECT CONVERT(VARCHAR,daLastBilled,103) FROM FMServiceProvider WHERE rfFMServiceProvider = t.rfFMServiceProvider) AS Date_Last_Billed
182 , ss.txDescription AS Status
183 , ISNULL(ff.txBrief, "") AS Prod_Code_Brief
184 , ISNULL(ff.txDescription, "") AS Prod_Code_Desc
185 , ISNULL(ff.mnAmount, 0) AS FMFee_Amt
186 , mnAMount
187 , mnGST
188 FROM #tmp_services t
189 , #tmp_Fixed_Fee ff
190 , FMService sv
191 , FMServiceClass sc
192 , FMServiceStatus ss
193 WHERE t.rfFMService = sv.rfFMService
194 AND ff.rfFMService =* sv.rfFMService
195 AND t.rfFMServiceStatus = ss.rfFMServiceStatus
196 AND t.rfFMServiceClass = sc.rfFMServiceClass
197 ORDER BY sv.txServiceNumber, sv.txExtensionNumber
198
199--END
200
201-- Tidy up
202--BEGIN
203 /*
204 DROP TABLE #tmp_Fixed_Fee
205 DROP TABLE #tmp_services
206 DROP TABLE #tmp_idle_service
207 DROP TABLE #tmp_child_du
208 DROP TABLE #tmp_parent_ds
209 DROP TABLE #tmp_working
210 DROP TABLE #ref
211 */
212--END
213GO