· 8 years ago · Aug 24, 2018, 01:42 AM
1IF OBJECT_ID("#tmpResults") IS NOT NULL
2 DROP TABLE #tmpResults
3GO
4
5BEGIN
6 CREATE TABLE #tmpResults
7 ( Id NUMERIC(12) IDENTITY,
8 ServiceNumber VARCHAR(25) NULL,
9 Supplier VARCHAR(8) NULL,
10 InvoiceNo VARCHAR(20) NULL,
11 InvoiceDate VARCHAR(12) NULL,
12 AccountNo VARCHAR(40) NULL,
13 BillingType VARCHAR(8) NULL,
14 LineClass VARCHAR(40) NULL,
15 ServiceClass VARCHAR(40) NULL,
16 ServiceType VARCHAR(8) NULL,
17 ServiceTypeDesc VARCHAR(40) NULL,
18 TransTypeCode VARCHAR(8) NULL,
19 TransTypeDesc VARCHAR(40) NULL,
20 CallTypeCode VARCHAR(8) NULL,
21 CallTypeDesc VARCHAR(40) NULL,
22 Level4Code VARCHAR(8) NULL,
23 Level4Desc VARCHAR(75) NULL,
24 Cost MONEY NULL,
25 GSTCost MONEY NULL,
26 Quantity FLOAT NULL,
27 Duration INT NULL,
28 NewExisting VARCHAR(1) NULL,
29 JustNonJust VARCHAR(1) NULL,
30 VarProductCode VARCHAR(40) NULL,
31 ChargeRule VARCHAR(40) NULL,
32 RuleCode NUMERIC(12) NULL,
33 Revenue MONEY NULL,
34 GST MONEY NULL,
35 BilledTotal MONEY NULL,
36 ServiceGroup VARCHAR(40) NULL,
37 Alloc_rfFMBusinessUnit NUMERIC(12) NULL,
38 ProductStream VARCHAR(40) NULL,
39 ProductProfile VARCHAR(40) NULL,
40 BillingPoint VARCHAR(40) NULL,
41 Terms INTEGER NULL,
42 RevInvoiceRefID NUMERIC(12) NULL,
43 RevInvoiceNo VARCHAR(20) NULL,
44 RevInvoicePurpose VARCHAR(40) NULL,
45 Disputed VARCHAR(1) NULL,
46 ChargeLevel INTEGER NULL,
47 L1_rfFMBusinessUnit NUMERIC(12) NULL,
48 L2_rfFMBusinessUnit NUMERIC(12) NULL,
49 L3_rfFMBusinessUnit NUMERIC(12) NULL,
50 L4_rfFMBusinessUnit NUMERIC(12) NULL,
51 L5_rfFMBusinessUnit NUMERIC(12) NULL,
52 L6_rfFMBusinessUnit NUMERIC(12) NULL,
53 L7_rfFMBusinessUnit NUMERIC(12) NULL,
54 L8_rfFMBusinessUnit NUMERIC(12) NULL,
55 L9_rfFMBusinessUnit NUMERIC(12) NULL,
56 L10_rfFMBusinessUnit NUMERIC(12) NULL,
57 L1_txBrief VARCHAR(30) NULL,
58 L2_txBrief VARCHAR(30) NULL,
59 L3_txBrief VARCHAR(30) NULL,
60 L4_txBrief VARCHAR(30) NULL,
61 L5_txBrief VARCHAR(30) NULL,
62 L6_txBrief VARCHAR(30) NULL,
63 L7_txBrief VARCHAR(30) NULL,
64 L8_txBrief VARCHAR(30) NULL,
65 L9_txBrief VARCHAR(30) NULL,
66 L10_txBrief VARCHAR(30) NULL,
67 L1_txDescription VARCHAR(40) NULL,
68 L2_txDescription VARCHAR(40) NULL,
69 L3_txDescription VARCHAR(40) NULL,
70 L4_txDescription VARCHAR(40) NULL,
71 L5_txDescription VARCHAR(40) NULL,
72 L6_txDescription VARCHAR(40) NULL,
73 L7_txDescription VARCHAR(40) NULL,
74 L8_txDescription VARCHAR(40) NULL,
75 L9_txDescription VARCHAR(40) NULL,
76 L10_txDescription VARCHAR(40) NULL,
77 rfBPCharge NUMERIC(12) NULL,
78 ReportCodetxBrief VARCHAR(8) NULL,
79 ReportCodetxDescription VARCHAR(40) NULL,
80 PreLoadCharges INTEGER NULL,
81 CustomerBillingPeriod NUMERIC(12) NULL,
82 blPrimary INTEGER NULL,
83 SplitAllocation INTEGER NULL)
84
85 CREATE INDEX #BOB ON #tmpResults (rfBPCharge)
86END
87GO
88
89
90BEGIN
91 DECLARE @rfBPCustomerBillingPeriod NUMERIC(12,0)
92 , @rfFMCustomer NUMERIC(12,0)
93 , @txBRIEF VARCHAR(15)
94 , @txDESC VARCHAR(15)
95
96SET @rfBPCustomerBillingPeriod = 16741
97
98SELECT @rfFMCustomer= rfFMCustomer
99 , @txBRIEF = "txBrief"
100 , @txDESC = "txDescription"
101 FROM BPCustomerBillingPeriod
102 WHERE rfBPCustomerBillingPeriod = @rfBPCustomerBillingPeriod
103
104-- This section is purposely to handle the dynamic title display
105SELECT "ServiceNumber" = "Service Number"
106 , "Supplier" = "Supplier"
107 , "InvoiceNo" = "Invoice No"
108 , "InvoiceDate" = "Invoice Date"
109 , "AccountNo" = "Account No"
110 , "BillingType" = "Supplier Billing Type"
111 , "LineClass" = "Line Class"
112 , "ServiceClass" = "Service Class"
113 , "ServiceType" = "Service Type"
114 , "ServiceTypeDesc" = "Service Type Description"
115 , "TransTypeCode" = "Transaction Type Code"
116 , "TransTypeDesc" = "Transaction Type Description"
117 , "CallTypeCode" = "Call Type Code"
118 , "CallTypeDesc" = "Call Type Description"
119 , "Level4Code" = "Level4 Code"
120 , "Level4Desc" = "Level4 Desc"
121 , "Cost" = "Cost"
122 , "GSTCost" = "GST Cost"
123 , "Quantity" = "Quantity"
124 , "Duration" = "Duration"
125 , "NewExisting" = "Landline New/Existing"
126 , "JustNonJust" = "Mobile Just/NonJust"
127 , "VarProductCode" = "Variable Product Code"
128 , "ChargeRule" = "Rule"
129 , "RuleCode" = "Rule Code"
130 , "Revenue" = "Revenue"
131 , "GST" = "GST"
132 , "BilledTotal" = "Billed Total"
133 , "Disputed" = "Disputed"
134 , "ChargeLevel" = "Charge Level"
135 , "ServiceGroup" = "Service Group (if applicable)"
136 , "BillingPoint" = "BillingPoint"
137 , "Terms" = "Terms"
138 , "RevInvoiceNo" = "Revenue Invoice No"
139 , "RevInvoicePurpose" = "Revenue Invoice Purpose"
140 , "ProductStream" = "Product Stream"
141 , "ProductProfile" = "Product Profile"
142 , "L1_txBrief" = (SELECT ISNULL(txDescription||" "||@txBRIEF, " ") FROM FMLevel
143 WHERE rfFMCustomer = @rfFMCustomer
144 AND inRank = 1)
145 , "L1_txDescription" = (SELECT ISNULL(txDescription||" "||@txDESC, " ") FROM FMLevel
146 WHERE rfFMCustomer = @rfFMCustomer
147 AND inRank = 1)
148 , "L2_txBrief" = (SELECT ISNULL(txDescription||" "||@txBRIEF, " ") FROM FMLevel
149 WHERE rfFMCustomer = @rfFMCustomer
150 AND inRank = 2)
151 , "L2_txDescription" = (SELECT ISNULL(txDescription||" "||@txDESC, " ") FROM FMLevel
152 WHERE rfFMCustomer = @rfFMCustomer
153 AND inRank = 2)
154 , "L3_txBrief" = (SELECT ISNULL(txDescription||" "||@txBRIEF, " ") FROM FMLevel
155 WHERE rfFMCustomer = @rfFMCustomer
156 AND inRank = 3)
157 , "L3_txDescription" = (SELECT ISNULL(txDescription||" "||@txDESC, " ") FROM FMLevel
158 WHERE rfFMCustomer = @rfFMCustomer
159 AND inRank = 3)
160 , "L4_txBrief" = (SELECT ISNULL(txDescription||" "||@txBRIEF, " ") FROM FMLevel
161 WHERE rfFMCustomer = @rfFMCustomer
162 AND inRank = 4)
163 , "L4_txDescription" = (SELECT ISNULL(txDescription||" "||@txDESC, " ") FROM FMLevel
164 WHERE rfFMCustomer = @rfFMCustomer
165 AND inRank = 4)
166 , "L5_txBrief" = (SELECT ISNULL(txDescription||" "||@txBRIEF, " ") FROM FMLevel
167 WHERE rfFMCustomer = @rfFMCustomer
168 AND inRank = 5)
169 , "L5_txDescription" = (SELECT ISNULL(txDescription||" "||@txDESC, " ") FROM FMLevel
170 WHERE rfFMCustomer = @rfFMCustomer
171 AND inRank = 5)
172 , "L6_txBrief" = (SELECT ISNULL(txDescription||" "||@txBRIEF, " ") FROM FMLevel
173 WHERE rfFMCustomer = @rfFMCustomer
174 AND inRank = 6)
175 , "L6_txDescription" = (SELECT ISNULL(txDescription||" "||@txDESC, " ") FROM FMLevel
176 WHERE rfFMCustomer = @rfFMCustomer
177 AND inRank = 6)
178 , "L7_txBrief" = (SELECT ISNULL(txDescription||" "||@txBRIEF, " ") FROM FMLevel
179 WHERE rfFMCustomer = @rfFMCustomer
180 AND inRank = 7)
181 , "L7_txDescription" = (SELECT ISNULL(txDescription||" "||@txDESC, " ") FROM FMLevel
182 WHERE rfFMCustomer = @rfFMCustomer
183 AND inRank = 7)
184 , "L8_txBrief" = (SELECT ISNULL(txDescription||" "||@txBRIEF, " ") FROM FMLevel
185 WHERE rfFMCustomer = @rfFMCustomer
186 AND inRank = 8)
187 , "L8_txDescription" = (SELECT ISNULL(txDescription||" "||@txDESC, " ") FROM FMLevel
188 WHERE rfFMCustomer = @rfFMCustomer
189 AND inRank = 8)
190 , "L9_txBrief" = (SELECT ISNULL(txDescription||" "||@txBRIEF, " ") FROM FMLevel
191 WHERE rfFMCustomer = @rfFMCustomer
192 AND inRank = 9)
193 , "L9_txDescription" = (SELECT ISNULL(txDescription||" "||@txDESC, " ") FROM FMLevel
194 WHERE rfFMCustomer = @rfFMCustomer
195 AND inRank = 9)
196 , "L10_txBrief" = (SELECT ISNULL(txDescription||" "||@txBRIEF, " ") FROM FMLevel
197 WHERE rfFMCustomer = @rfFMCustomer
198 AND inRank = 10)
199 , "L10_txDescription" = (SELECT ISNULL(txDescription||" "||@txDESC, " ") FROM FMLevel
200 WHERE rfFMCustomer = @rfFMCustomer
201 AND inRank = 10)
202END
203GO
204
205
206
207
208BEGIN
209 DECLARE @rfBPCustomerBillingPeriod NUMERIC(12)
210 , @daEffective DATETIME
211 , @rfFMCustomer NUMERIC(12)
212 , @rfBPChargeStatus NUMERIC(12)
213 , @IgnoreFCGInvoices INTEGER
214 , @rfDistributed NUMERIC(12)
215 , @SplitAllocation INTEGER
216 , @IsLoaded INTEGER
217
218 SELECT @IgnoreFCGInvoices = 1 -- Always TRUE for Billing Rules Analysis Report
219
220 SELECT @rfBPCustomerBillingPeriod = rfBPCustomerBillingPeriod
221 , @rfFMCustomer = rfFMCustomer
222 , @daEffective = daEffective
223 FROM BPCustomerBillingPeriod
224 WHERE rfBPCustomerBillingPeriod = 16741
225
226 SELECT @SplitAllocation = blSplitAllocation
227 FROM FMCustomerContract CC
228 WHERE rfFMCustomer = @rfFMCustomer
229 AND @daEffective BETWEEN CC.daFrom AND CC.daTo
230
231 SELECT @IsLoaded = COUNT(*)
232 FROM DWBillingPointCharge
233 WHERE rfBPCustomerBillingPeriod = @rfBPCustomerBillingPeriod
234
235 SELECT @rfBPChargeStatus = rfBPChargeStatus
236 FROM BPChargeStatus
237 WHERE txBrief = "DISPUTED"
238
239 SELECT @rfDistributed = rfBPChargeStatus
240 FROM BPChargeStatus
241 WHERE txBrief = "DISTRIB"
242
243 -- Supplier Charges
244 INSERT INTO #tmpResults
245 (ServiceNumber, Supplier, InvoiceNo, InvoiceDate, AccountNo,
246 BillingType, LineClass, ServiceClass, ServiceType, ServiceTypeDesc,
247 TransTypeCode, TransTypeDesc, CallTypeCode, CallTypeDesc,
248 Level4Code, Level4Desc, Cost, GSTCost,
249 Quantity, Duration, NewExisting, JustNonJust, VarProductCode,
250 ChargeRule, RuleCode, Revenue, GST, BilledTotal, ServiceGroup,
251 Alloc_rfFMBusinessUnit, BillingPoint, Terms, Disputed, ChargeLevel,
252 RevInvoiceRefID, ProductStream, ProductProfile,rfBPCharge,ReportCodetxBrief,
253 ReportCodetxDescription, PreLoadCharges, CustomerBillingPeriod, blPrimary,
254 SplitAllocation)
255 SELECT "ServiceNumber" = cl.txServiceNumber
256 ,"Supplier" = sup.txBrief
257 ,"InvoiceNo" = il.txInvoiceNo
258 ,"InvoiceDate" = CONVERT(CHAR(12), il.daIssue, 103)
259 ,"AccountNo" = il.txBillingAccount
260 ,"BillingType" = (Select txBrief from BPBillingType bt where bt.rfBPBillingType = tt.rfBPBillingType)
261 ,"LineClass" = (Select txDescription from FmLineClass lc where lc.rfFMLineClass = sd.rfFMLineClass)
262 ,"ServiceClass" = (Select txDescription from FmServiceClass sc where sc.rfFMServiceClass = sd.rfFMServiceClass)
263 ,"ServiceType" = st.txBrief
264 ,"ServiceTypeDesc" = st.txDescription
265 ,"TransTypeCode" = tt.txTransTypeCode
266 ,"TransTypeDesc" = tt.txDescription
267 ,"CallTypeCode" = cl.txCallTypeCode
268 ,"CallTypeDesc" = ct.txDescription
269 ,"Level4Code" = L4.txBrief
270 ,"Level4Desc" = L4.txLongDescr
271 ,"Cost" =
272 CASE
273 WHEN ((@SplitAllocation = 1) AND (al.blPrimary <> 1))
274 THEN 0
275 ELSE cl.mnAmount
276 END
277 ,"GSTCost" =
278 CASE
279 WHEN ((@SplitAllocation = 1) AND (al.blPrimary <> 1))
280 THEN 0
281 ELSE cl.mnGST
282 END
283 ,"Quantity" = cl.qtQuantity
284 ,"Duration" = cl.inCallDuration
285 ,"NewExisting" =
286 CASE
287 WHEN sd.blNewService IS NULL THEN " "
288 WHEN sd.blNewService = 0 THEN "E"
289 ELSE "N"
290 END
291 ,"JustNonJust" =
292 CASE
293 WHEN sd.blJustified IS NULL THEN " "
294 WHEN sd.blJustified = 0 THEN "N"
295 ELSE "J"
296 END
297 ,"VarProductCode" =
298 CASE
299 WHEN pd.txBrief IS NULL
300 THEN "Passthrough"
301 ELSE pd.txBrief
302 END
303 ,"ChargeRule" =
304 CASE
305 WHEN pd.txDescription IS NULL
306 THEN "Passthrough"
307 ELSE pd.txDescription
308 END
309 ,"RuleCode" = cl.rfFMPriceDetail
310 ,"Revenue" =
311 CASE
312 WHEN (rc.blSkip = 1) OR ((@SplitAllocation = 1) AND (al.blPrimary <> 1))
313 THEN 0
314 ELSE chg.mnAmount
315 END
316 ,"GST" =
317 CASE
318 WHEN (rc.blSkip = 1) OR ((@SplitAllocation = 1) AND (al.blPrimary <> 1))
319 THEN 0
320 ELSE chg.mnGST
321 END
322 ,"BilledTotal" =
323 CASE
324 WHEN (rc.blSkip = 1) OR ((@SplitAllocation = 1) AND (al.blPrimary <> 1))
325 THEN 0
326 ELSE chg.mnAmount + chg.mnGST
327 END
328 ,"ServiceGroup" =
329 CASE WHEN sg.txDescription IS NULL
330 THEN "n/a"
331 ELSE sg.txDescription
332 END
333 ,"Alloc_rfFMBusinessUnit" = al.rfFMBusinessUnit
334 ,"BillingPoint" =
335 CASE
336 WHEN ((bud.blBillable IS NULL) OR (bud.blBillable <> 0)) AND
337 (cl.txSourceTransType in ("DB","DP","DA","DJ","DR")
338 OR EXISTS(SELECT "EXISTS"
339 FROM BPMLH1 MLH
340 ,BPMLHPoint MLHP
341 ,BPBillingPoint BP
342 WHERE MLH.target_rfFMBusinessUnit = al.rfFMBusinessUnit
343 AND MLH.rfBPCustomerBillingPeriod = @rfBPCustomerBillingPeriod
344 AND MLH.inMLHDistance = 1
345 AND MLHP.rfFMBusinessUnit = MLH.target_rfFMBusinessUnit
346 AND MLHP.rfBPCustomerBillingPeriod = @rfBPCustomerBillingPeriod
347 AND BP.rfBPBillingPoint = MLHP.rfBPBillingPoint
348 AND BP.blConsolidated = 1))
349 THEN ""
350 ELSE "Not Billed"
351 END
352 ,"Terms" = 0
353 ,"Disputed" =
354 CASE
355 WHEN (chg.rfBPChargeStatus = @rfBPChargeStatus) THEN "T"
356 END
357 ,"ChargeLevel" =
358 CASE
359 WHEN cl.txSourceTransType IN ("DB","DP","DA","DJ","DR") THEN 1
360 WHEN cl.txSourceTransType IN ("DE","DO") THEN 4
361 ELSE 3
362 END
363 ,"RevInvoiceRefID" = chg.rfBPInvoice
364 ,"ProductStream" =
365 CASE
366 WHEN cl.txSourceTransType IN ("DB","DP","DA","DJ","DR")
367 THEN "Account Level"
368 ELSE ps.txDescription
369 END
370 ,"ProductProfile" =
371 CASE
372 WHEN cl.txSourceTransType IN ("DB","DP","DA","DJ","DR")
373 THEN "Account Level"
374 ELSE pp.txDescription
375 END
376 ,"rfBPCharge" = cl.rfBPChargeLoad
377 ,"ReportCodetxBrief" =
378 CASE
379 WHEN rc.rfBPReportCode IS NULL
380 THEN ""
381 ELSE rc.txReportCode
382 END
383 ,"ReportCodetxDescription" =
384 CASE
385 WHEN rc.rfBPReportCode IS NULL
386 THEN ""
387 ELSE rc.txDescription
388 END
389 ,"PreLoadCharges" = @IsLoaded
390 ,"CustomerBillingPeriod" = @rfBPCustomerBillingPeriod
391 ,"blPrimary" = al.blPrimary
392 ,"SplitAllocation" = @SplitAllocation
393
394 FROM BPInvoiceLoad il (Index ixBPInvoiceLoad2CstmrBlngPrd)
395 ,FMSupplier sup (Index PK_FMSUPPLIER)
396 ,BPChargeLoad cl (Index ixBPChrgLd_BPILparBPChrgLd)
397 ,BPCharge chg (Index pkBPCharge)
398 ,FMServiceDetail sd (Index FMService_FK)
399 ,BPTransactionType tt (Index PK_BPTRANSACTIONTYPE)
400 ,BPCallType ct (Index PK_BPCALLTYPE)
401 ,BPLevel4Code L4 (Index pkBPLevel4Code)
402 ,FMPriceDetail pd (Index PK_FMPRICEDETAIL)
403 ,FMServiceGroup sg (Index FMServiceGroup_PK)
404 ,BPServiceType st (Index PK_BPSERVICETYPE)
405 ,FMAllocation al (Index ixService)
406 ,FMBusinessUnitDetail bud (Index ixBusinessUnitDelta)
407 ,FMProductStream ps (index pkFMProductStream)
408 ,FMProductProfile pp (index pkFMProductProfile)
409 ,BPReportCode rc (Index PK_BPReportCode)
410 WHERE il.rfBPCustomerBillingPeriod = @rfBPCustomerBillingPeriod
411 AND sup.rfFMSupplier = il.rfFMSupplier
412 AND cl.rfBPInvoiceLoad = il.rfBPInvoiceLoad
413 AND ((cl.txSourceTransType IN ("DS", "DE", "DO")) OR
414 ((cl.txSourceTransType IN ("DB","DP","DA","DJ")) AND
415 (NOT EXISTS(SELECT "EXISTS"
416 FROM BPChargeLoad Child
417 WHERE Child.rfBPInvoiceLoad = cl.rfBPInvoiceLoad
418 AND Child.Parent_rfBPChargeLoad = cl.rfBPChargeLoad))))
419 AND chg.rfBPCharge =* cl.rfBPChargeLoad
420 AND chg.rfBPChargeStatus <> @rfDistributed
421 AND sd.rfFMService =* cl.rfFMService
422 AND @daEffective BETWEEN sd.daFrom AND sd.daTo
423 AND tt.rfBPTransactionType = cl.rfBPTransactionType
424 AND ct.rfBPCallType =* cl.rfBPCallType
425 AND L4.rfBPLevel4Code =* cl.rfBPLevel4Code
426 AND pd.rfFMPriceDetail =* cl.rfFMPriceDetail
427 AND sg.rfFMServiceGroup =* sd.rfFMServiceGroup
428 AND st.rfBPServiceType =* ct.rfBPServiceType
429 AND al.rfFMService =* sd.rfFMService
430 AND @daEffective BETWEEN al.daFrom AND al.daTo
431 AND bud.rfFMBusinessUnit =* al.rfFMBusinessUnit
432 AND @daEffective BETWEEN bud.daFrom AND bud.daTo
433 AND ps.rfFMProductStream =* ct.rfFMProductStream
434 AND pp.rfFMProductProfile =* ct.rfFMProductProfile
435 AND rc.rfBPReportCode =* chg.rfBPReportCode
436
437 -- Post Trans generated Charges
438 INSERT INTO #tmpResults
439 (ServiceNumber, Supplier, InvoiceNo, InvoiceDate, AccountNo,
440 BillingType, LineClass, ServiceClass, ServiceType, ServiceTypeDesc,
441 TransTypeCode, TransTypeDesc, CallTypeCode, CallTypeDesc,
442 Level4Code, Level4Desc, Cost, GSTCost,
443 Quantity, Duration, NewExisting, JustNonJust, VarProductCode,
444 ChargeRule, RuleCode, Revenue, GST, BilledTotal, ServiceGroup,
445 Alloc_rfFMBusinessUnit, BillingPoint, Terms, Disputed, ChargeLevel,
446 RevInvoiceRefID, ProductStream, ProductProfile,rfBPCharge,ReportCodetxBrief,
447 ReportCodetxDescription, PreLoadCharges, CustomerBillingPeriod, blPrimary,
448 SplitAllocation)
449 SELECT "ServiceNumber" = svc.txServiceNumber
450 ,"Supplier" = sup.txBrief
451 ,"InvoiceNo" = i.txInvoiceNo
452 ,"InvoiceDate" = CONVERT(CHAR(12), i.daIssue, 103)
453 ,"AccountNo" = (Select txAccountCode from BPBillingAccount ba where ba.rfBPBillingAccount = i.rfBPBillingAccount)
454 ,"BillingType" = (Select txBrief from BPBillingType bt where bt.rfBPBillingType = tt.rfBPBillingType)
455 ,"LineClass" = (Select txDescription from FmLineClass lc where lc.rfFMLineClass = sd.rfFMLineClass)
456 ,"ServiceClass" = (Select txDescription from FmServiceClass sc where sc.rfFMServiceClass = sd.rfFMServiceClass)
457 ,"ServiceType" = st.txBrief
458 ,"ServiceTypeDesc" = st.txDescription
459 ,"TransTypeCode" = tt.txTransTypeCode
460 ,"TransTypeDesc" = tt.txDescription
461 ,"CallTypeCode" = ct.txBrief
462 ,"CallTypeDesc" = ct.txDescription
463 ,"Level4Code" = ""
464 ,"Level4Desc" = ""
465 ,"Cost" = 0
466 ,"GSTCost" = 0
467 ,"Quantity" = 0
468 ,"Duration" = 0
469 ,"NewExisting" =
470 CASE
471 WHEN sd.blNewService IS NULL THEN " "
472 WHEN sd.blNewService = 0 THEN "E"
473 ELSE "N"
474 END
475 ,"JustNonJust" =
476 CASE
477 WHEN sd.blJustified IS NULL THEN " "
478 WHEN sd.blJustified = 0 THEN "N"
479 ELSE "J"
480 END
481 ,"VarProductCode" =
482 CASE
483 WHEN pd.txBrief IS NULL
484 THEN "Passthrough"
485 ELSE pd.txBrief
486 END
487 ,"ChargeRule" =
488 CASE
489 WHEN pd.txDescription IS NULL
490 THEN "Passthrough"
491 ELSE pd.txDescription
492 END
493 ,"RuleCode" = chg.rfFMPriceDetail
494 ,"Revenue" =
495 CASE
496 WHEN (rc.blSkip = 1) OR ((@SplitAllocation = 1) AND (al.blPrimary <> 1))
497 THEN 0
498 ELSE chg.mnAmount
499 END
500 ,"GST" =
501 CASE
502 WHEN (rc.blSkip = 1) OR ((@SplitAllocation = 1) AND (al.blPrimary <> 1))
503 THEN 0
504 ELSE chg.mnGST
505 END
506 ,"BilledTotal" =
507 CASE
508 WHEN (rc.blSkip = 1) OR ((@SplitAllocation = 1) AND (al.blPrimary <> 1))
509 THEN 0
510 ELSE chg.mnAmount + chg.mnGST
511 END
512 ,"ServiceGroup" =
513 CASE WHEN sg.txDescription IS NULL
514 THEN "n/a"
515 ELSE sg.txDescription
516 END
517 ,"Alloc_rfFMBusinessUnit" = al.rfFMBusinessUnit
518 ,"BillingPoint" =
519 CASE
520 WHEN ((bud.blBillable IS NULL) OR (bud.blBillable <> 0)) AND
521 (CHG.txSourceTransType in ("DB","DP","DA","DJ","DR")
522 OR EXISTS(SELECT "EXISTS"
523 FROM BPMLH1 MLH
524 ,BPMLHPoint MLHP
525 ,BPBillingPoint BP
526 WHERE MLH.target_rfFMBusinessUnit = al.rfFMBusinessUnit
527 AND MLH.rfBPCustomerBillingPeriod = @rfBPCustomerBillingPeriod
528 AND MLH.inMLHDistance = 1
529 AND MLHP.rfFMBusinessUnit = MLH.target_rfFMBusinessUnit
530 AND MLHP.rfBPCustomerBillingPeriod = @rfBPCustomerBillingPeriod
531 AND BP.rfBPBillingPoint = MLHP.rfBPBillingPoint
532 AND BP.blConsolidated = 1))
533 THEN ""
534 ELSE "Not Billed"
535 END
536 ,"Terms" = 0
537 ,"Disputed" =
538 CASE
539 WHEN (chg.rfBPChargeStatus = @rfBPChargeStatus) THEN "T"
540 END
541 ,"ChargeLevel" =
542 CASE
543 WHEN chg.txSourceTransType IN ("DB","DP","DA","DJ","DR")
544 THEN 1
545 ELSE 3
546 END
547 ,"RevInvoiceRefID" = chg.rfBPInvoice
548 ,"ProductStream" =
549 CASE
550 WHEN chg.txSourceTransType IN ("DB","DP","DA","DJ","DR")
551 THEN "Account Level"
552 ELSE ps.txDescription
553 END
554 ,"ProductProfile" =
555 CASE
556 WHEN chg.txSourceTransType IN ("DB","DP","DA","DJ","DR")
557 THEN "Account Level"
558 ELSE pp.txDescription
559 END
560 ,"rfBPCharge" = chg.rfBPCharge
561 ,"ReportCodetxBrief" =
562 CASE
563 WHEN rc.rfBPReportCode IS NULL
564 THEN ""
565 ELSE rc.txReportCode
566 END
567 ,"ReportCodetxDescription" =
568 CASE
569 WHEN rc.rfBPReportCode IS NULL
570 THEN ""
571 ELSE rc.txDescription
572 END
573 ,"PreLoadCharges" = @IsLoaded
574 ,"CustomerBillingPeriod" = @rfBPCustomerBillingPeriod
575 ,"blPrimary" = al.blPrimary
576 ,"SplitAllocation" = @SplitAllocation
577
578 FROM BPInvoice i (index ixBPInvoice2CstmrBlgPrd_BlgAct)
579 ,FMSupplier sup (Index PK_FMSUPPLIER)
580 ,BPCharge chg (Index ixBPCharge_BPInvc_prntChrg)
581 ,BPTransactionType tt
582 ,FMService svc
583 ,FMServiceDetail sd
584 ,BPCallType ct
585 ,FMPriceDetail pd
586 ,FMServiceGroup sg
587 ,BPServiceType st
588 ,FMAllocation al
589 ,FMBusinessUnitDetail bud
590 ,FMProductStream ps
591 ,FMProductProfile pp
592 ,BPReportCode rc
593 WHERE i.rfBPCustomerBillingPeriod = @rfBPCustomerBillingPeriod
594 AND NOT EXISTS (SELECT "EXISTS"
595 FROM BPCustomerProcesses CP, BPProcesses P
596 WHERE @IgnoreFCGInvoices = 1
597 AND CP.rfBPCustomerProcesses = I.rfBPCustomerProcesses
598 AND P.rfBPProcesses = CP.rfBPProcesses
599 AND P.txBrief = "FXCHGEN") --Stops from selecting FCG Invoices
600 AND sup.rfFMSupplier = i.rfFMSupplier
601 AND NOT EXISTS (SELECT "EXISTS"
602 FROM BPProcessTo PT
603 WHERE PT.rfBPInvoice = I.rfBPInvoice)
604 AND NOT EXISTS (SELECT "EXISTS"
605 FROM BPCustomerProcesses CP, BPProcesses P
606 WHERE CP.rfBPCustomerProcesses = I.rfBPCustomerProcesses
607 AND P.rfBPProcesses = CP.rfBPProcesses
608 AND P.txBrief = "DISPCHRG") --Stops from selecting Resolved Disputes Invoices
609 AND chg.rfBPInvoice = i.rfBPInvoice
610 AND ((chg.txSourceTransType = "DS")
611 OR ((chg.txSourceTransType IN ("DB","DP","DA","DJ"))
612 AND (NOT EXISTS(SELECT "EXISTS"
613 FROM BPCharge Child
614 WHERE Child.rfBPInvoice = chg.rfBPInvoice
615 AND Child.Parent_rfBPCharge = chg.rfBPCharge))))
616 AND chg.rfBPChargeStatus <> @rfDistributed
617 AND tt.rfBPTransactionType = chg.rfBPTransactionType
618 AND svc.rfFMService =* chg.rfFMService
619 AND sd.rfFMService =* chg.rfFMService
620 AND @daEffective BETWEEN sd.daFrom AND sd.daTo
621 AND ct.rfBPCallType =* chg.rfBPCallType
622 AND pd.rfFMPriceDetail =* chg.rfFMPriceDetail
623 AND sg.rfFMServiceGroup =* sd.rfFMServiceGroup
624 AND st.rfBPServiceType =* ct.rfBPServiceType
625 AND al.rfFMService =* sd.rfFMService
626 AND @daEffective BETWEEN al.daFrom AND al.daTo
627 AND bud.rfFMBusinessUnit =* al.rfFMBusinessUnit
628 AND @daEffective BETWEEN bud.daFrom AND bud.daTo
629 AND ps.rfFMProductStream =* ct.rfFMProductStream
630 AND pp.rfFMProductProfile =* ct.rfFMProductProfile
631 AND rc.rfBPReportCode =* chg.rfBPReportCode
632
633 -- Resolved Dispute Charges
634 INSERT INTO #tmpResults
635 (ServiceNumber, Supplier, InvoiceNo, InvoiceDate, AccountNo,
636 BillingType, LineClass, ServiceClass, ServiceType, ServiceTypeDesc,
637 TransTypeCode, TransTypeDesc, CallTypeCode, CallTypeDesc,
638 Level4Code, Level4Desc, Cost, GSTCost,
639 Quantity, Duration, NewExisting, JustNonJust, VarProductCode,
640 ChargeRule, RuleCode, Revenue, GST, BilledTotal, ServiceGroup,
641 Alloc_rfFMBusinessUnit, BillingPoint, Terms, Disputed, ChargeLevel,
642 RevInvoiceRefID, ProductStream, ProductProfile,rfBPCharge,ReportCodetxBrief,
643 ReportCodetxDescription, PreLoadCharges, CustomerBillingPeriod, blPrimary,
644 SplitAllocation)
645 SELECT "ServiceNumber" = svc.txServiceNumber
646 ,"Supplier" = sup.txBrief
647 ,"InvoiceNo" = il.txInvoiceNo
648 ,"InvoiceDate" = CONVERT(CHAR(12), il.daIssue, 103)
649 ,"AccountNo" = (Select txAccountCode from BPBillingAccount ba where ba.rfBPBillingAccount = il.rfBPBillingAccount)
650 ,"BillingType" = (Select txBrief from BPBillingType bt where bt.rfBPBillingType = tt.rfBPBillingType)
651 ,"LineClass" = (Select txDescription from FmLineClass lc where lc.rfFMLineClass = sd.rfFMLineClass)
652 ,"ServiceClass" = (Select txDescription from FmServiceClass sc where sc.rfFMServiceClass = sd.rfFMServiceClass)
653 ,"ServiceType" = st.txBrief
654 ,"ServiceTypeDesc" = st.txDescription
655 ,"TransTypeCode" = tt.txTransTypeCode
656 ,"TransTypeDesc" = tt.txDescription
657 ,"CallTypeCode" = ct.txBrief
658 ,"CallTypeDesc" = ct.txDescription
659 ,"Level4Code" = ""
660 ,"Level4Desc" = ""
661 ,"Cost" = 0 --cl.mnAmount
662 ,"GSTCost" = 0 --cl.mnGST
663 ,"Quantity" = cl.qtQuantity
664 ,"Duration" = cl.inCallDuration
665 ,"NewExisting" =
666 CASE
667 WHEN sd.blNewService IS NULL THEN " "
668 WHEN sd.blNewService = 0 THEN "E"
669 ELSE "N"
670 END
671 ,"JustNonJust" =
672 CASE
673 WHEN sd.blJustified IS NULL THEN " "
674 WHEN sd.blJustified = 0 THEN "N"
675 ELSE "J"
676 END
677 ,"VarProductCode" =
678 CASE
679 WHEN pd.txBrief IS NULL
680 THEN "Passthrough"
681 ELSE pd.txBrief
682 END
683 ,"ChargeRule" =
684 CASE
685 WHEN pd.txDescription IS NULL
686 THEN "Passthrough"
687 ELSE pd.txDescription
688 END
689 ,"RuleCode" = cl.rfFMPriceDetail
690 ,"Revenue" =
691 CASE
692 WHEN (rc.blSkip = 1) OR ((@SplitAllocation = 1) AND (al.blPrimary <> 1))
693 THEN 0
694 ELSE chg.mnAmount
695 END
696 ,"GST" =
697 CASE
698 WHEN (rc.blSkip = 1) OR ((@SplitAllocation = 1) AND (al.blPrimary <> 1))
699 THEN 0
700 ELSE chg.mnGST
701 END
702 ,"BilledTotal" =
703 CASE
704 WHEN (rc.blSkip = 1) OR ((@SplitAllocation = 1) AND (al.blPrimary <> 1))
705 THEN 0
706 ELSE chg.mnAmount + chg.mnGST
707 END
708 ,"ServiceGroup" =
709 CASE WHEN sg.txDescription IS NULL
710 THEN "n/a"
711 ELSE sg.txDescription
712 END
713 ,"Alloc_rfFMBusinessUnit" = al.rfFMBusinessUnit
714 ,"BillingPoint" =
715 CASE
716 WHEN ((bud.blBillable IS NULL) OR (bud.blBillable <> 0)) AND
717 (CHG.txSourceTransType in ("DB","DP","DA","DJ","DR")
718 OR EXISTS(SELECT "EXISTS"
719 FROM BPMLH1 MLH
720 ,BPMLHPoint MLHP
721 ,BPBillingPoint BP
722 WHERE MLH.target_rfFMBusinessUnit = al.rfFMBusinessUnit
723 AND MLHP.rfFMBusinessUnit = MLH.target_rfFMBusinessUnit
724 AND MLH.rfBPCustomerBillingPeriod = @rfBPCustomerBillingPeriod
725 AND MLHP.rfBPCustomerBillingPeriod = @rfBPCustomerBillingPeriod
726 AND MLH.inMLHDistance = 1
727 AND BP.rfBPBillingPoint = MLHP.rfBPBillingPoint
728 AND BP.blConsolidated = 1))
729
730 THEN ""
731 ELSE "Not Billed"
732 END
733 ,"Terms" = cr.inAging
734 ,"Disputed" =
735 CASE
736 WHEN (chg.rfBPChargeStatus = @rfBPChargeStatus) THEN "T"
737 END
738 ,"ChargeLevel" =
739 CASE
740 WHEN cl.txSourceTransType IN ("DB","DP","DA","DJ","DR") THEN 1
741 WHEN cl.txSourceTransType IN ("DE","DO") THEN 4
742 ELSE 3
743 END
744 ,"RevInvoiceRefID" = chg.rfBPInvoice
745 ,"ProductStream" =
746 CASE
747 WHEN cl.txSourceTransType IN ("DB","DP","DA","DJ","DR")
748 THEN "Account Level"
749 ELSE ps.txDescription
750 END
751 ,"ProductProfile" =
752 CASE
753 WHEN cl.txSourceTransType IN ("DB","DP","DA","DJ","DR")
754 THEN "Account Level"
755 ELSE pp.txDescription
756 END
757 ,"rfBPCharge" = chg.rfBPCharge
758 ,"ReportCodetxBrief" =
759 CASE
760 WHEN rc.rfBPReportCode IS NULL
761 THEN ""
762 ELSE rc.txReportCode
763 END
764 ,"ReportCodetxDescription" =
765 CASE
766 WHEN rc.rfBPReportCode IS NULL
767 THEN ""
768 ELSE rc.txDescription
769 END
770 ,"PreLoadCharges" = @IsLoaded
771 ,"CustomerBillingPeriod" = @rfBPCustomerBillingPeriod
772 ,"blPrimary" = al.blPrimary
773 ,"SplitAllocation" = @SplitAllocation
774
775 FROM BPDisputeReceipt dr
776 ,BPChargeReceipt cr
777 ,BPCharge chg
778 ,BPChargeLoad cl
779 ,BPInvoiceLoad il
780 ,BPTransactionType tt
781 ,FMSupplier sup
782 ,FMServiceDetail sd
783 ,FMService svc
784 ,BPCallType ct
785 ,FMPriceDetail pd
786 ,FMServiceGroup sg
787 ,BPServiceType st
788 ,FMAllocation al
789 ,FMBusinessUnitDetail bud
790 ,FMProductStream ps
791 ,FMProductProfile pp
792 ,BPReportCode rc
793 WHERE dr.rfBPCustomerBillingPeriod = @rfBPCustomerBillingPeriod
794 AND cr.rfBPDisputeReceipt = dr.rfBPDisputeReceipt
795 AND ((chg.rfBPCharge = cr.rfBPCharge_Invc)
796 OR (chg.parent_rfBPCharge = cr.rfBPCharge_Invc))
797 AND chg.rfBPChargeStatus <> @rfDistributed
798 AND chg.txSourceTransType <> "DR"
799 AND cl.rfBPChargeLoad =* cr.rfBPCharge
800 AND il.rfBPInvoiceLoad =* cl.rfBPInvoiceLoad
801 AND tt.rfBPTransactionType =* chg.rfBPTransactionType
802 AND sup.rfFMSupplier =* tt.rfFMSupplier
803 AND svc.rfFMService =* chg.rfFMService
804 AND sd.rfFMService =* chg.rfFMService
805 AND @daEffective BETWEEN sd.daFrom AND sd.daTo
806 AND ct.rfBPCallType =* chg.rfBPCallType
807 AND pd.rfFMPriceDetail =* chg.rfFMPriceDetail
808 AND sg.rfFMServiceGroup =* sd.rfFMServiceGroup
809 AND st.rfBPServiceType =* ct.rfBPServiceType
810 AND al.rfFMService =* sd.rfFMService
811 AND @daEffective BETWEEN al.daFrom AND al.daTo
812 AND bud.rfFMBusinessUnit =* al.rfFMBusinessUnit
813 AND @daEffective BETWEEN bud.daFrom AND bud.daTo
814 AND ps.rfFMProductStream =* ct.rfFMProductStream
815 AND pp.rfFMProductProfile =* ct.rfFMProductProfile
816 AND rc.rfBPReportCode =* chg.rfBPReportCode
817
818 -- Distributed Charges
819 INSERT INTO #tmpResults
820 (ServiceNumber, Supplier, InvoiceNo, InvoiceDate, AccountNo,
821 BillingType, LineClass, ServiceClass, ServiceType, ServiceTypeDesc,
822 TransTypeCode, TransTypeDesc, CallTypeCode, CallTypeDesc,
823 Level4Code, Level4Desc, Cost, GSTCost,
824 Quantity, Duration, NewExisting, JustNonJust, VarProductCode,
825 ChargeRule, RuleCode, Revenue, GST, BilledTotal, ServiceGroup,
826 Alloc_rfFMBusinessUnit, BillingPoint, Terms, Disputed, ChargeLevel,
827 RevInvoiceRefID, ProductStream, ProductProfile,rfBPCharge,ReportCodetxBrief,
828 ReportCodetxDescription, PreLoadCharges, CustomerBillingPeriod, blPrimary,
829 SplitAllocation)
830 SELECT "ServiceNumber" = svc.txServiceNumber
831 ,"Supplier" = sup.txBrief
832 ,"InvoiceNo" = il.txInvoiceNo
833 ,"InvoiceDate" = CONVERT(CHAR(12), il.daIssue, 103)
834 ,"AccountNo" = il.txBillingAccount
835 ,"BillingType" = (Select txBrief from BPBillingType bt where bt.rfBPBillingType = tt.rfBPBillingType)
836 ,"LineClass" = (Select txDescription from FmLineClass lc where lc.rfFMLineClass = sd.rfFMLineClass)
837 ,"ServiceClass" = (Select txDescription from FmServiceClass sc where sc.rfFMServiceClass = sd.rfFMServiceClass)
838 ,"ServiceType" = st.txBrief
839 ,"ServiceTypeDesc" = st.txDescription
840 ,"TransTypeCode" = tt.txTransTypeCode
841 ,"TransTypeDesc" = tt.txDescription
842 ,"CallTypeCode" = ct.txBrief
843 ,"CallTypeDesc" = ct.txDescription
844 ,"Level4Code" = ""
845 ,"Level4Desc" = ""
846 ,"Cost" = 0
847 ,"GSTCost" = 0
848 ,"Quantity" = 0
849 ,"Duration" = 0
850 ,"NewExisting" =
851 CASE
852 WHEN sd.blNewService IS NULL THEN " "
853 WHEN sd.blNewService = 0 THEN "E"
854 ELSE "N"
855 END
856 ,"JustNonJust" =
857 CASE
858 WHEN sd.blJustified IS NULL THEN " "
859 WHEN sd.blJustified = 0 THEN "N"
860 ELSE "J"
861 END
862 ,"VarProductCode" =
863 CASE
864 WHEN pd.txBrief IS NULL
865 THEN "Passthrough"
866 ELSE pd.txBrief
867 END
868 ,"ChargeRule" =
869 CASE
870 WHEN pd.txDescription IS NULL
871 THEN "Passthrough"
872 ELSE pd.txDescription
873 END
874 ,"RuleCode" = chg.rfFMPriceDetail
875 ,"Revenue" =
876 CASE
877 WHEN (rc.blSkip = 1) OR ((@SplitAllocation = 1) AND (al.blPrimary <> 1))
878 THEN 0
879 ELSE chg.mnAmount
880 END
881 ,"GST" =
882 CASE
883 WHEN (rc.blSkip = 1) OR ((@SplitAllocation = 1) AND (al.blPrimary <> 1))
884 THEN 0
885 ELSE chg.mnGST
886 END
887 ,"BilledTotal" =
888 CASE
889 WHEN (rc.blSkip = 1) OR ((@SplitAllocation = 1) AND (al.blPrimary <> 1))
890 THEN 0
891 ELSE chg.mnAmount + chg.mnGST
892 END
893 ,"ServiceGroup" =
894 CASE WHEN sg.txDescription IS NULL
895 THEN "n/a"
896 ELSE sg.txDescription
897 END
898 ,"Alloc_rfFMBusinessUnit" = al.rfFMBusinessUnit
899 ,"BillingPoint" =
900 CASE
901 WHEN ((bud.blBillable IS NULL) OR (bud.blBillable <> 0)) AND
902 (CHG.txSourceTransType in ("DB","DP","DA","DJ","DR")
903 OR EXISTS(SELECT "EXISTS"
904 FROM BPMLH1 MLH
905 ,BPMLHPoint MLHP
906 ,BPBillingPoint BP
907 WHERE MLH.target_rfFMBusinessUnit = al.rfFMBusinessUnit
908 AND MLH.rfBPCustomerBillingPeriod = @rfBPCustomerBillingPeriod
909 AND MLH.inMLHDistance = 1
910 AND MLHP.rfFMBusinessUnit = MLH.target_rfFMBusinessUnit
911 AND MLHP.rfBPCustomerBillingPeriod = @rfBPCustomerBillingPeriod
912 AND BP.rfBPBillingPoint = MLHP.rfBPBillingPoint
913 AND BP.blConsolidated = 1))
914 THEN ""
915 ELSE "Not Billed"
916 END
917 ,"Terms" = 0
918 ,"Disputed" =
919 CASE
920 WHEN (chg.rfBPChargeStatus = @rfBPChargeStatus) THEN "T"
921 END
922 ,"ChargeLevel" =
923 CASE
924 WHEN chg.txSourceTransType IN ("DB","DP","DA","DJ","DR")
925 THEN 1
926 ELSE 3
927 END
928 ,"RevInvoiceRefID" = chg.rfBPInvoice
929 ,"ProductStream" =
930 CASE
931 WHEN chg.txSourceTransType IN ("DB","DP","DA","DJ","DR")
932 THEN "Account Level"
933 ELSE ps.txDescription
934 END
935 ,"ProductProfile" =
936 CASE
937 WHEN chg.txSourceTransType IN ("DB","DP","DA","DJ","DR")
938 THEN "Account Level"
939 ELSE pp.txDescription
940 END
941 ,"rfBPCharge" = chg.rfBPCharge
942 ,"ReportCodetxBrief" =
943 CASE
944 WHEN rc.rfBPReportCode IS NULL
945 THEN ""
946 ELSE rc.txReportCode
947 END
948 ,"ReportCodetxDescription" =
949 CASE
950 WHEN rc.rfBPReportCode IS NULL
951 THEN ""
952 ELSE rc.txDescription
953 END
954 ,"PreLoadCharges" = @IsLoaded
955 ,"CustomerBillingPeriod" = @rfBPCustomerBillingPeriod
956 ,"blPrimary" = al.blPrimary
957 ,"SplitAllocation" = @SplitAllocation
958
959 FROM BPInvoice i (index ixBPInvoice2CstmrBlgPrd_BlgAct)
960 ,BPInvoiceLoad il
961 ,FMSupplier sup
962 ,BPCharge chg
963 ,BPTransactionType tt
964 ,FMService svc
965 ,FMServiceDetail sd
966 ,BPCallType ct
967 ,FMPriceDetail pd
968 ,FMServiceGroup sg
969 ,BPServiceType st
970 ,FMAllocation al
971 ,FMBusinessUnitDetail bud
972 ,FMProductStream ps
973 ,FMProductProfile pp
974 ,BPReportCode rc
975 WHERE i.rfBPCustomerBillingPeriod = @rfBPCustomerBillingPeriod
976 AND il.rfBPInvoiceLoad =* i.rfBPInvoiceLoad
977 AND sup.rfFMSupplier = i.rfFMSupplier
978 AND EXISTS (SELECT "EXISTS"
979 FROM BPProcessTo PT
980 WHERE PT.rfBPInvoice = I.rfBPInvoice)
981 AND chg.rfBPInvoice = i.rfBPInvoice
982
983 AND ((chg.txSourceTransType = "DS")
984 OR ((chg.txSourceTransType IN ("DB","DP","DA","DJ"))
985 AND (NOT EXISTS(SELECT "EXISTS"
986 FROM BPCharge Child
987 WHERE Child.rfBPInvoice = chg.rfBPInvoice
988 AND Child.Parent_rfBPCharge = chg.rfBPCharge))))
989 AND NOT EXISTS (SELECT "EXISTS"
990 FROM #tmpResults XX
991 WHERE XX.rfBPCharge = chg.rfBPCharge)
992 AND chg.rfBPChargeStatus <> @rfDistributed
993 AND tt.rfBPTransactionType = chg.rfBPTransactionType
994 AND svc.rfFMService =* chg.rfFMService
995 AND sd.rfFMService =* chg.rfFMService
996 AND @daEffective BETWEEN sd.daFrom AND sd.daTo
997 AND ct.rfBPCallType =* chg.rfBPCallType
998 AND pd.rfFMPriceDetail =* chg.rfFMPriceDetail
999 AND sg.rfFMServiceGroup =* sd.rfFMServiceGroup
1000 AND st.rfBPServiceType =* ct.rfBPServiceType
1001 AND al.rfFMService =* sd.rfFMService
1002 AND @daEffective BETWEEN al.daFrom AND al.daTo
1003 AND bud.rfFMBusinessUnit =* al.rfFMBusinessUnit
1004 AND @daEffective BETWEEN bud.daFrom AND bud.daTo
1005 AND ps.rfFMProductStream =* ct.rfFMProductStream
1006 AND pp.rfFMProductProfile =* ct.rfFMProductProfile
1007 AND rc.rfBPReportCode =* chg.rfBPReportCode
1008END
1009
1010GO
1011
1012
1013
1014
1015BEGIN
1016 DECLARE @rfBPCustomerBillingPeriod NUMERIC(12)
1017 , @daEffective DATETIME
1018
1019 SELECT @rfBPCustomerBillingPeriod = rfBPCustomerBillingPeriod
1020 , @daEffective = daEffective
1021 FROM BPCustomerBillingPeriod
1022 WHERE rfBPCustomerBillingPeriod = 16741
1023
1024 -- Set the Revenue Invoice details
1025 -- First from Consolidated
1026 UPDATE #tmpResults
1027 SET RevInvoiceNo = ci.txInvoiceNo
1028 ,RevInvoicePurpose = ba.txDescription
1029 FROM #tmpResults tr
1030 ,BPInvoiceSummary i
1031 ,BPConsolidatedInvoice ci
1032 ,BPBillingAccount ba
1033 WHERE ((RevInvoiceNo IS NULL) OR (RevInvoicePurpose IS NULL))
1034 AND i.rfBPInvoice = tr.RevInvoiceRefID
1035 AND ci.rfBPConsolidatedInvoice = i.rfBPConsolidatedInvoice
1036 AND ba.rfBPBillingAccount = ci.rfBPBillingAccount
1037
1038 -- Then if still NULL from BPInvoice
1039 UPDATE #tmpResults
1040 SET RevInvoiceNo = i.txInvoiceNo
1041 ,RevInvoicePurpose = ba.txDescription
1042 FROM #tmpResults tr
1043 ,BPInvoice i
1044 ,BPBillingAccount ba
1045 WHERE ((RevInvoiceNo IS NULL) OR (RevInvoicePurpose IS NULL))
1046 AND i.rfBPInvoice = tr.RevInvoiceRefID
1047 AND ba.rfBPBillingAccount = i.rfBPBillingAccount
1048
1049 -- Now update the heirarchy
1050 UPDATE #tmpResults
1051 SET L1_rfFMBusinessUnit = Alloc_rfFMBusinessUnit
1052 FROM #tmpResults tr
1053 , FMBusinessUnit bu
1054 , FMLevel l
1055 WHERE tr.Alloc_rfFMBusinessUnit = bu.rfFMBusinessUnit
1056 AND bu.rfFMLevel = l.rfFMLevel
1057 AND l.inRank = 1
1058
1059 UPDATE #tmpResults
1060 SET L2_rfFMBusinessUnit = Alloc_rfFMBusinessUnit
1061 FROM #tmpResults tr
1062 , FMBusinessUnit bu
1063 , FMLevel l
1064 WHERE tr.Alloc_rfFMBusinessUnit = bu.rfFMBusinessUnit
1065 AND bu.rfFMLevel = l.rfFMLevel
1066 AND l.inRank = 2
1067
1068 UPDATE #tmpResults
1069 SET L3_rfFMBusinessUnit = Alloc_rfFMBusinessUnit
1070 FROM #tmpResults tr
1071 , FMBusinessUnit bu
1072 , FMLevel l
1073 WHERE tr.Alloc_rfFMBusinessUnit = bu.rfFMBusinessUnit
1074 AND bu.rfFMLevel = l.rfFMLevel
1075 AND l.inRank = 3
1076
1077 UPDATE #tmpResults
1078 SET L4_rfFMBusinessUnit = Alloc_rfFMBusinessUnit
1079 FROM #tmpResults tr
1080 , FMBusinessUnit bu
1081 , FMLevel l
1082 WHERE tr.Alloc_rfFMBusinessUnit = bu.rfFMBusinessUnit
1083 AND bu.rfFMLevel = l.rfFMLevel
1084 AND l.inRank = 4
1085
1086 UPDATE #tmpResults
1087 SET L5_rfFMBusinessUnit = Alloc_rfFMBusinessUnit
1088 FROM #tmpResults tr
1089 , FMBusinessUnit bu
1090 , FMLevel l
1091 WHERE tr.Alloc_rfFMBusinessUnit = bu.rfFMBusinessUnit
1092 AND bu.rfFMLevel = l.rfFMLevel
1093 AND l.inRank = 5
1094
1095 -- Now update the exTENded heirarchy
1096 UPDATE #tmpResults
1097 SET L6_rfFMBusinessUnit = Alloc_rfFMBusinessUnit
1098 FROM #tmpResults tr
1099 , FMBusinessUnit bu
1100 , FMLevel l
1101 WHERE tr.Alloc_rfFMBusinessUnit = bu.rfFMBusinessUnit
1102 AND bu.rfFMLevel = l.rfFMLevel
1103 AND l.inRank = 6
1104
1105 UPDATE #tmpResults
1106 SET L7_rfFMBusinessUnit = Alloc_rfFMBusinessUnit
1107 FROM #tmpResults tr
1108 , FMBusinessUnit bu
1109 , FMLevel l
1110 WHERE tr.Alloc_rfFMBusinessUnit = bu.rfFMBusinessUnit
1111 AND bu.rfFMLevel = l.rfFMLevel
1112 AND l.inRank = 7
1113
1114 UPDATE #tmpResults
1115 SET L8_rfFMBusinessUnit = Alloc_rfFMBusinessUnit
1116 FROM #tmpResults tr
1117 , FMBusinessUnit bu
1118 , FMLevel l
1119 WHERE tr.Alloc_rfFMBusinessUnit = bu.rfFMBusinessUnit
1120 AND bu.rfFMLevel = l.rfFMLevel
1121 AND l.inRank = 8
1122
1123 UPDATE #tmpResults
1124 SET L9_rfFMBusinessUnit = Alloc_rfFMBusinessUnit
1125 FROM #tmpResults tr
1126 , FMBusinessUnit bu
1127 , FMLevel l
1128 WHERE tr.Alloc_rfFMBusinessUnit = bu.rfFMBusinessUnit
1129 AND bu.rfFMLevel = l.rfFMLevel
1130 AND l.inRank = 9
1131
1132 UPDATE #tmpResults
1133 SET L10_rfFMBusinessUnit = Alloc_rfFMBusinessUnit
1134 FROM #tmpResults tr
1135 , FMBusinessUnit bu
1136 , FMLevel l
1137 WHERE tr.Alloc_rfFMBusinessUnit = bu.rfFMBusinessUnit
1138 AND bu.rfFMLevel = l.rfFMLevel
1139 AND l.inRank = 10
1140
1141
1142 UPDATE #tmpResults
1143 SET L9_rfFMBusinessUnit = mlh.source_rfFMBusinessUnit
1144 FROM #tmpResults tr
1145 , BPMLH1 mlh
1146 WHERE tr.L10_rfFMBusinessUnit = mlh.target_rfFMBusinessUnit
1147 AND mlh.inMLHDistance = 1
1148 AND tr.L9_rfFMBusinessUnit IS NULL
1149 AND mlh.rfBPCustomerBillingPeriod = @rfBPCustomerBillingPeriod
1150
1151 UPDATE #tmpResults
1152 SET L8_rfFMBusinessUnit = mlh.source_rfFMBusinessUnit
1153 FROM #tmpResults tr
1154 , BPMLH1 mlh
1155 WHERE tr.L9_rfFMBusinessUnit = mlh.target_rfFMBusinessUnit
1156 AND mlh.inMLHDistance = 1
1157 AND tr.L8_rfFMBusinessUnit IS NULL
1158 AND mlh.rfBPCustomerBillingPeriod = @rfBPCustomerBillingPeriod
1159
1160 UPDATE #tmpResults
1161 SET L7_rfFMBusinessUnit = mlh.source_rfFMBusinessUnit
1162 FROM #tmpResults tr
1163 , BPMLH1 mlh
1164 WHERE tr.L8_rfFMBusinessUnit = mlh.target_rfFMBusinessUnit
1165 AND mlh.inMLHDistance = 1
1166 AND tr.L7_rfFMBusinessUnit IS NULL
1167 AND mlh.rfBPCustomerBillingPeriod = @rfBPCustomerBillingPeriod
1168
1169 UPDATE #tmpResults
1170 SET L6_rfFMBusinessUnit = mlh.source_rfFMBusinessUnit
1171 FROM #tmpResults tr
1172 , BPMLH1 mlh
1173 WHERE tr.L7_rfFMBusinessUnit = mlh.target_rfFMBusinessUnit
1174 AND mlh.inMLHDistance = 1
1175 AND tr.L6_rfFMBusinessUnit IS NULL
1176 AND mlh.rfBPCustomerBillingPeriod = @rfBPCustomerBillingPeriod
1177
1178 UPDATE #tmpResults
1179 SET L5_rfFMBusinessUnit = mlh.source_rfFMBusinessUnit
1180 FROM #tmpResults tr
1181 , BPMLH1 mlh
1182 WHERE tr.L6_rfFMBusinessUnit = mlh.target_rfFMBusinessUnit
1183 AND mlh.inMLHDistance = 1
1184 AND tr.L5_rfFMBusinessUnit IS NULL
1185 AND mlh.rfBPCustomerBillingPeriod = @rfBPCustomerBillingPeriod
1186
1187
1188 UPDATE #tmpResults
1189 SET L4_rfFMBusinessUnit = mlh.source_rfFMBusinessUnit
1190 FROM #tmpResults tr
1191 , BPMLH1 mlh
1192 WHERE tr.L5_rfFMBusinessUnit = mlh.target_rfFMBusinessUnit
1193 AND mlh.inMLHDistance = 1
1194 AND tr.L4_rfFMBusinessUnit IS NULL
1195 AND mlh.rfBPCustomerBillingPeriod = @rfBPCustomerBillingPeriod
1196
1197 UPDATE #tmpResults
1198 SET L3_rfFMBusinessUnit = mlh.source_rfFMBusinessUnit
1199 FROM #tmpResults tr
1200 , BPMLH1 mlh
1201 WHERE tr.L4_rfFMBusinessUnit = mlh.target_rfFMBusinessUnit
1202 AND mlh.inMLHDistance = 1
1203 AND tr.L3_rfFMBusinessUnit IS NULL
1204 AND mlh.rfBPCustomerBillingPeriod = @rfBPCustomerBillingPeriod
1205
1206 UPDATE #tmpResults
1207 SET L2_rfFMBusinessUnit = mlh.source_rfFMBusinessUnit
1208 FROM #tmpResults tr
1209 , BPMLH1 mlh
1210 WHERE tr.L3_rfFMBusinessUnit = mlh.target_rfFMBusinessUnit
1211 AND mlh.inMLHDistance = 1
1212 AND tr.L2_rfFMBusinessUnit IS NULL
1213 AND mlh.rfBPCustomerBillingPeriod = @rfBPCustomerBillingPeriod
1214
1215 UPDATE #tmpResults
1216 SET L1_rfFMBusinessUnit = mlh.source_rfFMBusinessUnit
1217 FROM #tmpResults tr
1218 , BPMLH1 mlh
1219 WHERE tr.L2_rfFMBusinessUnit = mlh.target_rfFMBusinessUnit
1220 AND mlh.inMLHDistance = 1
1221 AND tr.L1_rfFMBusinessUnit IS NULL
1222 AND mlh.rfBPCustomerBillingPeriod = @rfBPCustomerBillingPeriod
1223
1224 UPDATE #tmpResults
1225 SET L1_txBrief = bud1.cdCustomerRef
1226 , L1_txDescription = bud1.txDescription
1227 , L2_txBrief = bud2.cdCustomerRef
1228 , L2_txDescription = bud2.txDescription
1229 , L3_txBrief = bud3.cdCustomerRef
1230 , L3_txDescription = bud3.txDescription
1231 , L4_txBrief = bud4.cdCustomerRef
1232 , L4_txDescription = bud4.txDescription
1233 , L5_txBrief = bud5.cdCustomerRef
1234 , L5_txDescription = bud5.txDescription
1235 , L6_txBrief = bud6.cdCustomerRef
1236 , L6_txDescription = bud6.txDescription
1237 , L7_txBrief = bud7.cdCustomerRef
1238 , L7_txDescription = bud7.txDescription
1239 , L8_txBrief = bud8.cdCustomerRef
1240 , L8_txDescription = bud8.txDescription
1241 , L9_txBrief = bud9.cdCustomerRef
1242 , L9_txDescription = bud9.txDescription
1243 , L10_txBrief = bud10.cdCustomerRef
1244 , L10_txDescription = bud10.txDescription
1245
1246 FROM #tmpResults tr
1247 , FMBusinessUnitDetail bud1
1248 , FMBusinessUnitDetail bud2
1249 , FMBusinessUnitDetail bud3
1250 , FMBusinessUnitDetail bud4
1251 , FMBusinessUnitDetail bud5
1252 , FMBusinessUnitDetail bud6
1253 , FMBusinessUnitDetail bud7
1254 , FMBusinessUnitDetail bud8
1255 , FMBusinessUnitDetail bud9
1256 , FMBusinessUnitDetail bud10
1257
1258 WHERE tr.L1_rfFMBusinessUnit *= bud1.rfFMBusinessUnit
1259 AND tr.L2_rfFMBusinessUnit *= bud2.rfFMBusinessUnit
1260 AND tr.L3_rfFMBusinessUnit *= bud3.rfFMBusinessUnit
1261 AND tr.L4_rfFMBusinessUnit *= bud4.rfFMBusinessUnit
1262 AND tr.L5_rfFMBusinessUnit *= bud5.rfFMBusinessUnit
1263 AND tr.L6_rfFMBusinessUnit *= bud6.rfFMBusinessUnit
1264 AND tr.L7_rfFMBusinessUnit *= bud7.rfFMBusinessUnit
1265 AND tr.L8_rfFMBusinessUnit *= bud8.rfFMBusinessUnit
1266 AND tr.L9_rfFMBusinessUnit *= bud9.rfFMBusinessUnit
1267 AND tr.L10_rfFMBusinessUnit *= bud10.rfFMBusinessUnit
1268
1269 AND @daEffective BETWEEN bud1.daFrom AND bud1.daTo
1270 AND @daEffective BETWEEN bud2.daFrom AND bud2.daTo
1271 AND @daEffective BETWEEN bud3.daFrom AND bud3.daTo
1272 AND @daEffective BETWEEN bud4.daFrom AND bud4.daTo
1273 AND @daEffective BETWEEN bud5.daFrom AND bud5.daTo
1274 AND @daEffective BETWEEN bud6.daFrom AND bud6.daTo
1275 AND @daEffective BETWEEN bud7.daFrom AND bud7.daTo
1276 AND @daEffective BETWEEN bud8.daFrom AND bud8.daTo
1277 AND @daEffective BETWEEN bud9.daFrom AND bud9.daTo
1278 AND @daEffective BETWEEN bud10.daFrom AND bud10.daTo
1279
1280END
1281GO
1282
1283
1284
1285BEGIN
1286 DELETE
1287 FROM #tmpResults
1288 WHERE blPrimary <> 1
1289
1290 SELECT ServiceNumber
1291 , Supplier
1292 , InvoiceNo
1293 , InvoiceDate
1294 , AccountNo
1295 , BillingType
1296 , LineClass
1297 , ServiceClass
1298 , ServiceType
1299 , ServiceTypeDesc
1300 , TransTypeCode
1301 , TransTypeDesc
1302 , CallTypeCode
1303 , CallTypeDesc
1304 , Level4Code
1305 , Level4Desc
1306 , CONVERT(VARCHAR,CONVERT(NUMERIC(12,4),Cost)) Cost
1307 , CONVERT(VARCHAR,CONVERT(NUMERIC(12,4),GSTCost)) GSTCost
1308 , CONVERT(VARCHAR,CONVERT(NUMERIC(12,4),Quantity)) Quantity
1309 , Duration
1310 , NewExisting
1311 , JustNonJust
1312 , VarProductCode
1313 , ChargeRule
1314 , CONVERT(VARCHAR,CONVERT(NUMERIC(12,4),RuleCode)) RuleCode
1315 , CONVERT(VARCHAR,CONVERT(NUMERIC(12,4),Revenue)) Revenue
1316 , CONVERT(VARCHAR,CONVERT(NUMERIC(12,4),GST)) GST
1317 , CONVERT(VARCHAR,CONVERT(NUMERIC(12,4),BilledTotal)) BilledTotal
1318 , Disputed
1319 , ChargeLevel
1320 , ServiceGroup
1321 , BillingPoint
1322 , Terms
1323 , RevInvoiceNo
1324 , RevInvoicePurpose
1325 , ProductStream
1326 , ProductProfile
1327 , L1_txBrief
1328 , L1_txDescription
1329 , L2_txBrief
1330 , L2_txDescription
1331 , L3_txBrief
1332 , L3_txDescription
1333 , L4_txBrief
1334 , L4_txDescription
1335 , L5_txBrief
1336 , L5_txDescription
1337 , L6_txBrief
1338 , L6_txDescription
1339 , L7_txBrief
1340 , L7_txDescription
1341 , L8_txBrief
1342 , L8_txDescription
1343 , L9_txBrief
1344 , L9_txDescription
1345 , L10_txBrief
1346 , L10_txDescription
1347 FROM #tmpResults
1348END
1349GO