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