· 8 years ago · Jun 03, 2018, 10:24 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 spd.rfFMService, pd.rfFMPriceDetail, 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 CREATE INDEX #tmp_idle_service_idx1 ON #tmp_idle_service (rfFMService)
104 CREATE INDEX #tmp_working_idx1 ON #tmp_working (rfFMService)
105
106 -- Force an Update Stats to tell Sybase that the indexes are ready for use.
107 -- If you dont do an update stats then the indexes wont be available to the
108 -- Sybase Optimizer
109 UPDATE STATISTICS #tmp_idle_service
110 UPDATE STATISTICS #tmp_working
111
112--END
113GO
114
115--BEGIN
116 -- we have got the services we need now... so lets get the rest of the info
117 DECLARE
118 @rfBPCustomerBillingPeriod NUMERIC(12),
119 @rfFMCustomer NUMERIC(12),
120 @daEffective DATETIME
121
122 SELECT @rfBPCustomerBillingPeriod = rfBPCustomerBillingPeriod
123 ,@rfFMCustomer = rfFMCustomer
124 ,@daEffective = daEffective
125 FROM #ref
126
127 -- Get details for services which are in the #tmp_Working table
128 SELECT DISTINCT SD.rfFMService
129 , (SELECT rfFMServiceClass FROM FMServiceDetail ssd
130 WHERE ssd.rfFMServiceDetail=sd.rfFMServiceDetail) AS rfFMServiceClass
131 , SD.rfFMServiceStatus
132 , BUD.txDescription
133 , t.mnAmount
134 , t.mnGST
135 , @daEffective AS daEffective
136 , CONVERT(NUMERIC(12,0),0) AS rfFMServiceProvider
137 INTO #tmp_services
138 FROM #tmp_idle_service t
139 , #tmp_working SD
140 , FMAllocation AL
141 , FMBusinessUnitDetail BUD
142 WHERE SD.rfFMService = t.rfFMService
143 AND AL.rfFMService = SD.rfFMService
144 AND AL.rfFMBusinessUnit = BUD.rfFMBusinessUnit
145 AND @daEffective BETWEEN AL.daFrom AND AL.daTo
146 AND @daEffective BETWEEN BUD.daFrom AND BUD.daTo
147
148 -- Get the most recently billed FMServiceProvider ref value
149 -- If more than one for the same daLastBilled then use the record with
150 -- the max rfFMServiceProvide value
151 UPDATE #tmp_Services
152 SET rfFMServiceProvider =
153 ISNULL((SELECT MAX(rfFMServiceProvider) FROM FMServiceProvider WHERE rfFMService = t.rfFMService
154 AND daLastBilled = (SELECT MAX(daLastBilled) FROM FMServiceProvider WHERE rfFMService = t.rfFMService)), 0)
155 FROM #tmp_services t
156
157--END
158
159-- Output Section
160--BEGIN
161 SELECT DISTINCT sv.txServiceNumber AS Service
162 , sv.txExtensionNumber AS Extension
163 , CONVERT(VARCHAR,sv.dtCreateDateTimeStamp,103) AS Date_Created
164 , t.txDescription AS Cost_Centre_Desc
165 , (SELECT txDescription FROM FMSupplier su, FMServiceProvider sp
166 WHERE su.rfFMSupplier=sp.rfFMSupplier AND sp.rfFMServiceProvider = t.rfFMServiceProvider) AS Supplier
167 , (SELECT txAccountCode FROM BPBillingAccount ba , FMServiceProvider sp
168 WHERE ba.rfBPBillingAccount = sp.rfBPBillingAccount AND sp.rfFMServiceProvider = t.rfFMServiceProvider ) AS Billing_Account
169 , sc.txDescription Service_Class
170 , (SELECT CONVERT(VARCHAR,daLastBilled,103) FROM FMServiceProvider WHERE rfFMServiceProvider = t.rfFMServiceProvider) AS Date_Last_Billed
171 , ss.txDescription AS Status
172 , ISNULL(SELECT ff.txBrief FROM #tmp_Fixed_Fee ff WHERE ff.rfFMService = sv.rfFMService, "") AS Prod_Code_Brief
173 , ISNULL(SELECT ff.txDescription FROM #tmp_Fixed_Fee ff WHERE ff.rfFMService = sv.rfFMService, "") AS Prod_Code_Desc
174 , ISNULL(SELECT ff.mnAmount FROM #tmp_Fixed_Fee ff WHERE ff.rfFMService = sv.rfFMService, 0) AS FMFee_Amt
175 , mnAMount
176 , mnGST
177 FROM #tmp_services t
178 , FMService sv
179 , FMServiceClass sc
180 , FMServiceStatus ss
181 WHERE t.rfFMService = sv.rfFMService
182 AND t.rfFMServiceStatus = ss.rfFMServiceStatus
183 AND t.rfFMServiceClass = sc.rfFMServiceClass
184 ORDER BY sv.txServiceNumber, sv.txExtensionNumber
185
186--END
187
188-- Tidy up
189--BEGIN
190 /*
191 DROP TABLE #tmp_Fixed_Fee
192 DROP TABLE #tmp_services
193 DROP TABLE #tmp_idle_service
194 DROP TABLE #tmp_child_du
195 DROP TABLE #tmp_parent_ds
196 DROP TABLE #tmp_working
197 DROP TABLE #ref
198 */
199--END
200GO