[Home] [Help]
PACKAGE BODY: APPS.OKS_IMPORT_POST_INSERT
Source
1 PACKAGE BODY OKS_IMPORT_POST_INSERT AS
2 -- $Header: OKSPKIMPPOIB.pls 120.7 2007/11/16 11:49:08 mkarra noship $
3 --+=======================================================================+
4 --| Copyright (c) 2003 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| OKSPKIMPPOIB.pls Created By Mihira Karra |
10 --| |
11 --| DESCRIPTION |
12 --| Service Contracts Import Post Insert Routines Package |
13 --| |
14 --+========================================================================
15
16 --===================
17 -- GLOBALS
18 --===================
19
20 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OKS_IMPORT_INSERT';
21
22 --========================================================================
23 -- PRIVATE CONSTANTS AND VARIABLES
24 --========================================================================
25 G_MODULE_NAME CONSTANT VARCHAR2(50) := 'oks.plsql.import.' || G_PKG_NAME;
26 G_WORKER_REQ_ID CONSTANT NUMBER := FND_GLOBAL.conc_request_id;
27 G_MODULE_HEAD CONSTANT VARCHAR2(60) := G_MODULE_NAME || '(Req Id = '||G_WORKER_REQ_ID||').';
28 G_LOG_LEVEL CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
29 G_UNEXPECTED_LOG CONSTANT BOOLEAN := fnd_log.level_unexpected >= G_LOG_LEVEL AND
30 fnd_log.TEST(fnd_log.level_unexpected, G_MODULE_HEAD);
31 G_ERROR_LOG CONSTANT BOOLEAN := G_UNEXPECTED_LOG AND fnd_log.level_error >= G_LOG_LEVEL;
32 G_EXCEPTION_LOG CONSTANT BOOLEAN := G_ERROR_LOG AND fnd_log.level_exception >= G_LOG_LEVEL;
33 G_EVENT_LOG CONSTANT BOOLEAN := G_EXCEPTION_LOG AND fnd_log.level_event >= G_LOG_LEVEL;
34 G_PROCEDURE_LOG CONSTANT BOOLEAN := G_EVENT_LOG AND fnd_log.level_procedure >= G_LOG_LEVEL;
35 G_STMT_LOG CONSTANT BOOLEAN := G_PROCEDURE_LOG AND fnd_log.level_statement >= G_LOG_LEVEL;
36
37 --==========================
38 -- PROCEDURES AND FUNCTIONS
39 --==========================
40
41
42 --========================================================================
43 -- PROCEDURE : Generate_bil_sch_Subs_lines PRIVATE
44 -- PARAMETERS:
45 -- COMMENT : This procedure will generate Billing Streams and schedules
46 -- for Subscription Lines
47 --=========================================================================
48
49 PROCEDURE Generate_bil_sch_Subs_lines
50 IS
51 l_stmt_num NUMBER := 0;
52 l_routine CONSTANT VARCHAR2(30) := 'Generate_bil_sch_Subs_lines';
53 l_int_count NUMBER := 0;
54 l_stg_count NUMBER := 0;
55 l_recur_bill_occurance NUMBER := 0 ;
56
57 BEGIN
58 IF G_PROCEDURE_LOG THEN
59 fnd_log.string(fnd_log.level_procedure,
60 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
61 'Entering ');
62
63 END IF;
64
65
66
67 -- Generates Billing Streams for subscription lines
68 l_stmt_num := 10;
69
70 INSERT ALL
71 WHEN (FREQUENCY IS NOT NULL) THEN
72 INTO OKS_STREAM_LEVELS_B
73 (ID
74 ,CHR_ID
75 ,CLE_ID
76 ,DNZ_CHR_ID
77 ,SEQUENCE_NO
78 ,UOM_CODE
79 ,START_DATE
80 ,END_DATE
81 ,LEVEL_PERIODS
82 ,UOM_PER_PERIOD
83 ,LEVEL_AMOUNT
84 ,OBJECT_VERSION_NUMBER
85 ,REQUEST_ID
86 ,CREATED_BY
87 ,CREATION_DATE
88 ,LAST_UPDATED_BY
89 ,LAST_UPDATE_DATE
90 ,LAST_UPDATE_LOGIN)
91
92 VALUES (ID
93 ,CHR_ID
94 ,CLE_ID
95 ,DNZ_CHR_ID
96 ,SEQUENCE_NO
97 ,UOM_CODE
98 ,START_DATE
99 ,END_DATE
100 ,LEVEL_PERIODS
101 ,UOM_PER_PERIOD
102 ,LEVEL_AMOUNT
103 ,OBJECT_VERSION_NUMBER
104 ,REQUEST_ID
105 ,CREATED_BY
106 ,CREATION_DATE
107 ,LAST_UPDATED_BY
108 ,LAST_UPDATE_DATE
109 ,LAST_UPDATE_LOGIN)
110
111
112 SELECT okc_p_util.raw_to_number(sys_guid()) ID
113 ,INNER_Q2.SEQ SEQUENCE_NO
114 ,(CASE WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NOT NULL THEN 'DAY'
115 WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NULL THEN INNER_Q1.BILLING_INTERVAL_PERIOD
116 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS not NULL THEN INNER_Q1.BILLING_INTERVAL_PERIOD
117 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS null THEN 'DAY'
118 WHEN INNER_Q2.SEQ = 3 THEN 'DAY'
119 ELSE INNER_Q1.BILLING_INTERVAL_PERIOD
120 END) UOM_CODE
121 ,(CASE WHEN INNER_Q2.SEQ = 1 THEN INNER_Q1.LIN_STR_DT
122 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date is not null THEN INNER_Q1.FIRST_BILL_UPTO_DATE + 1
123 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NULL THEN INNER_Q1.LAST_BILL_FROM_DATE
124 WHEN INNER_Q2.SEQ = 3 THEN INNER_Q1.LAST_BILL_FROM_DATE
125 END) START_DATE
126 ,(CASE WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NOT NULL THEN INNER_Q1.FIRST_BILL_UPTO_DATE
127 WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NULL THEN INNER_Q1.LAST_BILL_FROM_DATE - 1
128 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NULL THEN INNER_Q1.LIN_END_DT
129 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NOT NULL THEN INNER_Q1.LAST_BILL_FROM_DATE - 1
130 WHEN INNER_Q2.SEQ = 3 THEN INNER_Q1.LIN_END_DT
131 END) END_DATE
132 ,(CASE WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NOT NULL THEN 1
133 WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NULL THEN INNER_Q1.RECUR_BILL_OCCURANCES
134 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NOT NULL THEN INNER_Q1.RECUR_BILL_OCCURANCES
135 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NULL THEN 1
136 WHEN INNER_Q2.SEQ = 3 THEN 1
137 END) LEVEL_PERIODS
138 ,(CASE WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NOT NULL THEN INNER_Q1.FIRST_BILL_UPTO_DATE - INNER_Q1.LIN_STR_DT +1 -- including the days between the difference
139 WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NULL THEN INNER_Q1.BILLING_INTERVAL_DURATION
140 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NOT NULL THEN INNER_Q1.BILLING_INTERVAL_DURATION
141 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date is NULL THEN INNER_Q1.LIN_END_DT -(INNER_Q1.LAST_BILL_FROM_DATE - 1 )
142 WHEN INNER_Q2.SEQ = 3 THEN INNER_Q1.LIN_END_DT -(INNER_Q1.LAST_BILL_FROM_DATE - 1 )
143 ELSE INNER_Q1.BILLING_INTERVAL_DURATION
144 END) UOM_PER_PERIOD
145 ,(CASE WHEN INNER_Q1.FBILL IS NOT NULL AND INNER_Q1.LBILL IS NOT NULL
146 THEN (CASE WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NOT NULL -- first stream
147 THEN INNER_Q1.FBILL
148 WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NULL -- normal stream
149 THEN (INNER_Q1.SUBTOTAL - nvl(INNER_Q1.FBILL,0) - nvl(INNER_Q1.LBILL,0))
150 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NOT NULL --normal stream
151 THEN (INNER_Q1.SUBTOTAL - nvl(INNER_Q1.FBILL,0) - nvl(INNER_Q1.LBILL,0))
152 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NULL -- last stream
153 THEN INNER_Q1.LBILL
154 WHEN INNER_Q2.SEQ = 3 THEN INNER_Q1.LBILL
155 END)
156 WHEN INNER_Q1.FBILL IS NULL AND INNER_Q1.LBILL IS NULL
157 THEN (CASE WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NOT NULL -- first stream
158 THEN ROUND((INNER_Q1.SUBTOTAL/INNER_Q1.NO_OF_DAYS) * (INNER_Q1.FIRST_BILL_UPTO_DATE - INNER_Q1.LIN_STR_DT +1) *1,2)
159 WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NULL -- normal stream
160 THEN ROUND((INNER_Q1.SUBTOTAL/CALC_BILL_PERIOD_1) * INNER_Q1.BILLING_INTERVAL_DURATION * INNER_Q1.RECUR_BILL_OCCURANCES,2)
161 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NOT NULL --normal stream
162 THEN ROUND((INNER_Q1.SUBTOTAL -
163 ROUND((INNER_Q1.SUBTOTAL/INNER_Q1.NO_OF_DAYS) * (INNER_Q1.FIRST_BILL_UPTO_DATE - INNER_Q1.LIN_STR_DT +1) *1,2))/CALC_BILL_PERIOD_2
164 *INNER_Q1.BILLING_INTERVAL_DURATION * INNER_Q1.RECUR_BILL_OCCURANCES,2)
165 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NULL -- last stream
166 THEN inner_q1.subtotal -
167 ROUND((INNER_Q1.SUBTOTAL/CALC_BILL_PERIOD_1) * INNER_Q1.BILLING_INTERVAL_DURATION * INNER_Q1.RECUR_BILL_OCCURANCES,2)
168 WHEN INNER_Q2.SEQ = 3
169 THEN inner_q1.subtotal -
170 ROUND((INNER_Q1.SUBTOTAL/INNER_Q1.NO_OF_DAYS),2) * (INNER_Q1.FIRST_BILL_UPTO_DATE - INNER_Q1.LIN_STR_DT +1) *1
171 - round((INNER_Q1.SUBTOTAL - (ROUND((INNER_Q1.SUBTOTAL/INNER_Q1.NO_OF_DAYS),2) *
172 (INNER_Q1.FIRST_BILL_UPTO_DATE - INNER_Q1.LIN_STR_DT +1) *1))/CALC_BILL_PERIOD_2 ,2)
173 *INNER_Q1.BILLING_INTERVAL_DURATION * INNER_Q1.RECUR_BILL_OCCURANCES
174 END)
175 WHEN INNER_Q1.FBILL IS NULL AND INNER_Q1.LBILL IS NOT NULL
176 THEN (CASE WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NOT NULL -- first stream
177 THEN ROUND((INNER_Q1.SUBTOTAL - INNER_Q1.LBILL)/DAY_FIRST_MID_STR * (INNER_Q1.FIRST_BILL_UPTO_DATE - INNER_Q1.LIN_STR_DT +1) * 1 ,2)
178 WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NULL -- normal stream
179 THEN INNER_Q1.SUBTOTAL - INNER_Q1.LBILL
180 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NOT NULL --normal stream
181 THEN INNER_Q1.SUBTOTAL - ROUND((INNER_Q1.SUBTOTAL - INNER_Q1.LBILL)/DAY_FIRST_MID_STR
182 * (INNER_Q1.FIRST_BILL_UPTO_DATE - INNER_Q1.LIN_STR_DT +1) * 1 ,2) - INNER_Q1.LBILL
183 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NULL -- last stream
184 THEN INNER_Q1.LBILL
185 WHEN INNER_Q2.SEQ = 3 THEN INNER_Q1.LBILL
186 END)
187 WHEN INNER_Q1.FBILL IS NOT NULL AND INNER_Q1.LBILL IS NULL
188 THEN (CASE WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NOT NULL -- first stream
189 THEN INNER_Q1.FBILL
190 WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NULL -- normal stream
191 THEN ROUND((INNER_Q1.SUBTOTAL)/CALC_BILL_PERIOD_1 * INNER_Q1.BILLING_INTERVAL_DURATION * INNER_Q1.RECUR_BILL_OCCURANCES,2)
192 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NOT NULL --normal stream
193 THEN ROUND((INNER_Q1.SUBTOTAL - INNER_Q1.FBILL)/CALC_BILL_PERIOD_2 * INNER_Q1.BILLING_INTERVAL_DURATION * INNER_Q1.RECUR_BILL_OCCURANCES,2)
194 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NULL -- last stream
195 THEN INNER_Q1.SUBTOTAL - ROUND((INNER_Q1.SUBTOTAL)/CALC_BILL_PERIOD_1 * INNER_Q1.BILLING_INTERVAL_DURATION * INNER_Q1.RECUR_BILL_OCCURANCES,2)
196 WHEN INNER_Q2.SEQ = 3
197 THEN INNER_Q1.SUBTOTAL - INNER_Q1.FBILL
198 - ROUND((INNER_Q1.SUBTOTAL - INNER_Q1.FBILL)/CALC_BILL_PERIOD_2 *
199 INNER_Q1.BILLING_INTERVAL_DURATION * INNER_Q1.RECUR_BILL_OCCURANCES,2)
200 END)
201 END) LEVEL_AMOUNT
202 ,INNER_Q1.RECUR_BILL_OCCURANCES FREQUENCY
203 ,INNER_Q1.*
204 ,INNER_Q2.*
205 FROM
206 (SELECT OKCLINB_LINE.ID LINE_ID
207 ,null CHR_ID
208 ,OKCLINB_LINE.ID CLE_ID
209 ,OKCHDRB.ID DNZ_CHR_ID
210 ,OLSTG.FIRST_BILL_UPTO_DATE FIRST_BILL_UPTO_DATE
211 ,OLSTG.FIRST_BILLED_AMOUNT FBILL
212 ,OLSTG.LAST_BILLED_AMOUNT LBILL
213 ,(CASE WHEN OLSTG.FIRST_BILL_UPTO_DATE IS NULL AND (OLSTG.LAST_BILL_FROM_DATE -1 )= OLSTG.END_DATE THEN 1
214 WHEN OLSTG.FIRST_BILL_UPTO_DATE IS NOT NULL AND (OLSTG.LAST_BILL_FROM_DATE -1) = OLSTG.END_DATE THEN 2
215 WHEN OLSTG.FIRST_BILL_UPTO_DATE IS NULL AND (OLSTG.LAST_BILL_FROM_DATE -1 ) < OLSTG.END_DATE THEN 2
216 WHEN OLSTG.FIRST_BILL_UPTO_DATE IS NOT NULL AND (OLSTG.LAST_BILL_FROM_DATE -1 ) < OLSTG.END_DATE THEN 3
217 END) NUM_STREAMS
218 ,OLSTG.BILLING_INTERVAL_PERIOD BILLING_INTERVAL_PERIOD
219 ,(CASE WHEN OLSTG.BILLING_INTERVAL_PERIOD ='DAY' THEN (OLSTG.END_DATE - OLSTG.START_DATE)+1 --no_of_day
220 WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'WK' THEN ((OLSTG.END_DATE - OLSTG.START_DATE)+1)/7 -- no of weeks
221 WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'MTH' THEN MONTHS_BETWEEN(OLSTG.END_DATE + 1 ,OLSTG.START_DATE) -- no of months
222 WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'QRT' THEN MONTHS_BETWEEN(OLSTG.END_DATE + 1,OLSTG.START_DATE)/3 -- no of quarter
223 WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'YR' THEN MONTHS_BETWEEN(OLSTG.END_DATE + 1,OLSTG.START_DATE)/12 -- no of years
224 END) CALC_BILL_PERIOD_1
225 ,(CASE WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'DAY' THEN (OLSTG.END_dATE -OLSTG.FIRST_BILL_UPTO_DATE +1) +1 --no of days
226 WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'WK' THEN ((OLSTG.END_DATE - OLSTG.FIRST_BILL_UPTO_DATE + 1) +1 )/7 -- no of weeks
227 WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'MTH' THEN MONTHS_BETWEEN(OLSTG.END_DATE + 1,(OLSTG.FIRST_BILL_UPTO_DATE+1))-- no of months
228 WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'QRT' THEN MONTHS_BETWEEN(OLSTG.END_DATE + 1,(OLSTG.FIRST_BILL_UPTO_DATE+1))/3 -- no of quarter
229 WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'YR' THEN MONTHS_BETWEEN(OLSTG.END_DATE + 1,(OLSTG.FIRST_BILL_UPTO_DATE+1))/12 -- no of years
230 END) CALC_BILL_PERIOD_2
231 ,(OLSTG.END_DATE - OLSTG.START_DATE)+1 NO_OF_DAYS
232 ,OLSTG.LAST_BILL_FROM_DATE - OLSTG.START_DATE DAY_FIRST_MID_STR
233 ,OLSTG.LAST_BILL_FROM_DATE -(OLSTG.FIRST_BILL_UPTO_DATE +1) +1 DAY_MID_STR
234 ,OLSTG.LINE_TYPE LINE_TYPE
235 ,OLSTG.RECUR_BILL_OCCURANCES RECUR_BILL_OCCURANCES
236 ,OLSTG.BILLING_INTERVAL_DURATION BILLING_INTERVAL_DURATION
240 ,OLSTG.LAST_BILL_FROM_DATE LAST_BILL_FROM_DATE
237 ,nvl(OKCLINB_LINE.PRICE_NEGOTIATED,0) SUBTOTAL
238 ,OLSTG.START_DATE LIN_STR_DT
239 ,OLSTG.END_DATE LIN_END_DT
241 ,1 OBJECT_VERSION_NUMBER
242 ,null REQUEST_ID -- need to confirm
243 ,FND_GLOBAL.USER_ID CREATED_BY
244 ,SYSDATE CREATION_DATE
245 ,FND_GLOBAL.USER_ID LAST_UPDATED_BY
246 ,SYSDATE LAST_UPDATE_DATE
247 ,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
248
249 FROM OKS_INT_LINE_STG_TEMP OLSTG
250 ,OKS_INT_HEADER_STG_TEMP HDRSTG
251 ,OKC_K_HEADERS_ALL_B OKCHDRB
252 ,OKC_K_LINES_B OKCLINB_LINE
253 WHERE OLSTG.HEADER_INTERFACE_ID = HDRSTG.HEADER_INTERFACE_ID
254 AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
255 AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
256 AND HDRSTG.INTERFACE_STATUS ='S'
257 AND OKCLINB_LINE.DNZ_CHR_ID = OKCHDRB.ID
258 AND OKCLINB_LINE.CHR_ID = OKCHDRB.ID
259 AND OKCLINB_LINE.LINE_NUMBER = OLSTG.LINE_NUMBER
260 AND OLSTG.LINE_TYPE='SUBSCRIPTION' ) INNER_Q1
261
262 ,(SELECT ROWNUM AS SEQ FROM DUAL CONNECT BY LEVEL <= 3) INNER_Q2
263 WHERE INNER_Q2.SEQ <= INNER_Q1.NUM_STREAMS;
264
265 /* IF G_STMT_LOG THEN
266
267 fnd_log.string(fnd_log.level_statement,
268 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
269 'Number of records successfully inserted = ' || l_int_count );
270 END IF; */
271
272 l_stmt_num :=20;
273
274 SELECT MAX(RECUR_BILL_OCCURANCES) INTO l_recur_bill_occurance FROM OKS_INT_LINE_STG_TEMP ;
275
276 --This query creates billing schedules from streams for Subscription lines
277
278 l_stmt_num :=30 ;
279
280 INSERT ALL
281 WHEN (FREQUENCY IS NOT NULL ) then
282 INTO OKS_LEVEL_ELEMENTS
283 (ID
284 ,SEQUENCE_NUMBER
285 ,DATE_START
286 ,AMOUNT
287 ,DATE_TRANSACTION
288 ,DATE_TO_INTERFACE
289 ,DATE_COMPLETED
290 ,OBJECT_VERSION_NUMBER
291 ,RUL_ID
292 ,CREATED_BY
293 ,CREATION_DATE
294 ,LAST_UPDATED_BY
295 ,LAST_UPDATE_DATE
296 ,CLE_ID
297 ,DNZ_CHR_ID
298 ,PARENT_CLE_ID
299 ,DATE_END)
300 VALUES (ID
301 ,SEQUENCE_NUMBER
302 ,DATE_START
303 ,AMOUNT
304 ,DATE_TRANSACTION
305 ,DATE_TO_INTERFACE
306 ,DATE_COMPLETED
307 ,OBJECT_VERSION_NUMBER
308 ,RUL_ID
309 ,CREATED_BY
310 ,CREATION_DATE
311 ,LAST_UPDATED_BY
312 ,LAST_UPDATE_DATE
313 ,CLE_ID
314 ,DNZ_CHR_ID
315 ,PARENT_CLE_ID
316 ,DATE_END)
317 SELECT SUBS_SCH_DT.*
318 ,(CASE WHEN SUBS_SCH_DT.INVOICING_RULE_ID = -2 THEN
319 (CASE WHEN SUBS_SCH_DT.DATE_START >= SYSDATE THEN SUBS_SCH_DT.DATE_START
320 ELSE SYSDATE
321 END)
322 WHEN SUBS_SCH_DT.INVOICING_RULE_ID = -3 THEN
323 (CASE WHEN SUBS_SCH_DT.DATE_END >= SYSDATE THEN SUBS_SCH_DT.DATE_END
324 ELSE SYSDATE
325 END)
326 END) DATE_TRANSACTION
327
328 ,(CASE WHEN SUBS_SCH_DT.INVOICING_RULE_ID = -2 THEN SUBS_SCH_DT.DATE_START
329 WHEN SUBS_SCH_DT.INVOICING_RULE_ID = -3 THEN SUBS_SCH_DT.DATE_END +1
330 END) DATE_TO_INTERFACE
331 FROM
332 (SELECT okc_p_util.raw_to_number(sys_guid()) ID
333 ,INNER_Q2.SEQ SEQUENCE_NUMBER
334
335 ,(CASE WHEN INNER_Q2.SEQ=1 THEN INNER_Q1.STRM_START_DATE
336 -- IN OTHER CASES
337 ELSE DECODE (INNER_Q1.BILLING_INTERVAL_PERIOD
338 ,'DAY' , MID_SM_STR_DT + (INNER_Q2.SEQ -1 )
339 ,'WK' , MID_SM_STR_DT + (7 * (INNER_Q2.SEQ -1 ))
340 ,'MTH' , ADD_MONTHS(MID_SM_STR_DT , (INNER_Q2.SEQ -1 ))
341 ,'QRT' , ADD_MONTHS(MID_SM_STR_DT , 3 * (INNER_Q2.SEQ -1 ))
342 ,'YR' , ADD_MONTHS(MID_SM_STR_DT , 12 * (INNER_Q2.SEQ -1 )) )
343 END) DATE_START
344
345 ,(CASE WHEN INNER_Q1.SEQUENCE_NO = 1 AND INNER_Q1.FIRST_BILL_UPTO_DATE IS NOT NULL --first stream
346 THEN INNER_Q1.LEVEL_AMOUNT
347 WHEN INNER_Q1.SEQUENCE_NO = 1 AND INNER_Q1.FIRST_BILL_UPTO_DATE IS NULL -- normal stream
348 THEN
349 (CASE WHEN INNER_Q1.LEVEL_AMOUNT - ROUND(INNER_Q1.LEVEL_AMOUNT/INNER_Q1.LEVEL_PERIODS,2) *(INNER_Q2.SEQ-1) >0
350 THEN (CASE WHEN INNER_Q2.SEQ =INNER_Q1.LEVEL_PERIODS --if it is the last schedule for the stream, value difference due to rounding is to be adjusted
351 AND ROUND(INNER_Q1.LEVEL_AMOUNT/INNER_Q1.LEVEL_PERIODS,2) = 0
352 THEN INNER_Q1.LEVEL_AMOUNT
353 WHEN INNER_Q2.SEQ =INNER_Q1.LEVEL_PERIODS AND ROUND(INNER_Q1.LEVEL_AMOUNT/INNER_Q1.LEVEL_PERIODS,2) >0
354 THEN INNER_Q1.LEVEL_AMOUNT - ROUND(INNER_Q1.LEVEL_AMOUNT/INNER_Q1.LEVEL_PERIODS,2)*(INNER_Q2.SEQ-1)
355 ELSE ROUND(INNER_Q1.LEVEL_AMOUNT/INNER_Q1.LEVEL_PERIODS,2)
356 END)
357 ELSE 0
358 END)
359 WHEN INNER_Q1.SEQUENCE_NO = 2 AND INNER_Q1.FIRST_BILL_UPTO_DATE IS NOT NULL -- then it is normal stream
360 THEN
361 (CASE WHEN INNER_Q1.LEVEL_AMOUNT - ROUND(INNER_Q1.LEVEL_AMOUNT/INNER_Q1.LEVEL_PERIODS,2)*(INNER_Q2.SEQ-1) >0
362 THEN (CASE WHEN INNER_Q2.SEQ =INNER_Q1.LEVEL_PERIODS AND ROUND(INNER_Q1.LEVEL_AMOUNT/INNER_Q1.LEVEL_PERIODS,2) = 0
363 THEN INNER_Q1.LEVEL_AMOUNT
364 WHEN INNER_Q2.SEQ = INNER_Q1.LEVEL_PERIODS AND ROUND(INNER_Q1.LEVEL_AMOUNT/INNER_Q1.LEVEL_PERIODS,2) >0
365 THEN INNER_Q1.LEVEL_AMOUNT - ROUND(INNER_Q1.LEVEL_AMOUNT/INNER_Q1.LEVEL_PERIODS,2)*(INNER_Q2.SEQ-1)
366 ELSE ROUND(INNER_Q1.LEVEL_AMOUNT/INNER_Q1.LEVEL_PERIODS,2)
367 END)
368 ELSE 0
369 END)
370 WHEN INNER_Q1.SEQUENCE_NO = 2 AND INNER_Q1.FIRST_BILL_UPTO_DATE IS NULL -- last bill stream
371 THEN INNER_Q1.LEVEL_AMOUNT
372 WHEN INNER_Q1.SEQUENCE_NO = 3 THEN INNER_Q1.LEVEL_AMOUNT
376 ELSE NULL
373 END) AMOUNT
374
375 ,(CASE WHEN INNER_Q1.FULLY_BILLED = 'Y' THEN SYSDATE
377 END) DATE_COMPLETED
378 ,INNER_Q1.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
379 ,INNER_Q1.OKS_STRM_LVL_ID RUL_ID
380 ,FND_GLOBAL.USER_ID CREATED_BY
381 ,SYSDATE CREATION_DATE
382 ,FND_GLOBAL.USER_ID LAST_UPDATED_BY
383 ,SYSDATE LAST_UPDATE_DATE
384 ,INNER_Q1.CLE_ID CLE_ID
385 ,INNER_Q1.DNZ_CHR_ID DNZ_CHR_ID
386 ,INNER_Q1.PARENT_CLE_ID PARENT_CLE_ID
387 ,(CASE WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.FIRST_BILL_UPTO_DATE IS NOT NULL
388 AND INNER_Q1.STRM_END_DATE = INNER_Q1.FIRST_BILL_UPTO_DATE --first stream
389 THEN INNER_Q1.FIRST_BILL_UPTO_DATE
390 WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.STRM_START_DATE = INNER_Q1.LAST_BILL_FROM_DATE --last stream
391 THEN INNER_Q1.LIN_END_DT
392 -- IN OTHER CASES
393 ELSE DECODE( INNER_Q1.BILLING_INTERVAL_PERIOD
394 , 'DAY' , MID_SM_STR_DT - 1 + INNER_Q2.SEQ
395 , 'WK' , MID_SM_STR_DT - 1 + (7 * (INNER_Q2.SEQ))
396 , 'MTH' , ADD_MONTHS(MID_SM_STR_DT - 1 , (INNER_Q2.SEQ ))
397 , 'QRT' , ADD_MONTHS(MID_SM_STR_DT - 1 , 3 * (INNER_Q2.SEQ ))
398 , 'YR' , ADD_MONTHS(MID_SM_STR_DT - 1 , 12 * (INNER_Q2.SEQ )) )
399
400 END) DATE_END
401 ,INNER_Q1.RECUR_BILL_OCCURANCES FREQUENCY
402 ,INNER_Q1.INVOICING_RULE_ID INVOICING_RULE_ID
403 FROM
404 (SELECT OLSTG.LINE_INTERFACE_ID LINE_INTERFACE_ID
405 ,OKS_STRM_LVL.ID OKS_STRM_LVL_ID
406 ,OKCLINB_LINE.ID CLE_ID
407 ,OKCHDRB.ID DNZ_CHR_ID
408 ,OKCLINB_LINE.ID PARENT_CLE_ID
409 ,OKCLINB_LINE.INV_RULE_ID INVOICING_RULE_ID
410 ,1 OBJECT_VERSION_NUMBER
411 ,nvl(OKCLINB_LINE.PRICE_NEGOTIATED,0) SUBTOTAL
412 ,OLSTG.LAST_BILL_FROM_DATE LAST_BILL_FROM_DATE
413 ,OLSTG.BILLING_INTERVAL_PERIOD BILLING_INTERVAL_PERIOD
414 ,OLSTG.BILLING_INTERVAL_DURATION BILLING_INTERVAL_DURATION
415 ,OLSTG.RECUR_BILL_OCCURANCES RECUR_BILL_OCCURANCES
416 ,OLSTG.START_DATE LIN_START_DT
417 ,OLSTG.END_DATE LIN_END_DT
418 ,NVL(OLSTG.FIRST_BILLED_AMOUNT,0) FIRST_BILL_AMOUNT
419 ,NVL(OLSTG.LAST_BILLED_AMOUNT,0) LAST_BILL_AMOUNT
420 ,OLSTG.FIRST_BILL_UPTO_DATE FIRST_BILL_UPTO_DATE
421 ,OKS_STRM_LVL.LEVEL_PERIODS LEVEL_PERIODS
422 ,OKS_STRM_LVL.SEQUENCE_NO SEQUENCE_NO
423 ,nvl(OKS_STRM_LVL.LEVEL_AMOUNT,0) LEVEL_AMOUNT
424 ,HDRSTG.FULLY_BILLED FULLY_BILLED
425 ,OKS_STRM_LVL.START_DATE STRM_START_DATE
426 ,OKS_STRM_LVL.END_DATE STRM_END_DATE
427 ,(CASE WHEN OLSTG.FIRST_BILL_UPTO_DATE IS NOT NULL THEN OLSTG.FIRST_BILL_UPTO_DATE +1
428 ELSE OLSTG.START_DATE
429 END) MID_SM_STR_DT
430 , OLSTG.LAST_BILL_FROM_DATE - 1 MID_SM_END_DT
431 ,OLSTG.LINE_TYPE LINE_TYPE
432
433 FROM OKS_INT_LINE_STG_TEMP OLSTG
434 ,OKC_K_LINES_B OKCLINB_LINE
435 ,OKC_K_HEADERS_ALL_B OKCHDRB
436 ,OKS_INT_HEADER_STG_TEMP HDRSTG
437 ,OKS_STREAM_LEVELS_B OKS_STRM_LVL
438
439 WHERE OLSTG.HEADER_INTERFACE_ID = HDRSTG.HEADER_INTERFACE_ID
440 AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
441 AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
442 AND HDRSTG.INTERFACE_STATUS ='S'
443 AND OKCLINB_LINE.DNZ_CHR_ID = OKCHDRB.ID
444 AND OKCLINB_LINE.CHR_ID = OKCHDRB.ID
445 AND OKS_STRM_LVL.DNZ_CHR_ID = OKCHDRB.ID
446 AND OKCLINB_LINE.LINE_NUMBER = OLSTG.LINE_NUMBER
447 AND OKS_STRM_LVL.CLE_ID = OKCLINB_LINE.ID
448 AND OLSTG.LINE_TYPE='SUBSCRIPTION') INNER_Q1
449
450 ,(SELECT ROWNUM AS SEQ FROM DUAL CONNECT BY LEVEL <= l_recur_bill_occurance ) INNER_Q2
451
452 WHERE INNER_Q2.SEQ <= INNER_Q1.LEVEL_PERIODS)SUBS_SCH_DT;
453
454 /*IF G_STMT_LOG THEN
455 fnd_log.string(fnd_log.level_statement,
456 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
457 'Number of records successfully inserted = ' || l_int_count );
458 END IF; */
459
460
461 /* IF G_PROCEDURE_LOG THEN
462 fnd_log.string(fnd_log.level_procedure,
463 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
464 'Exit.');
465
466 END IF; */
467
468 EXCEPTION
469 WHEN FND_API.G_EXC_ERROR THEN
470 -- ROLLBACK;
471 RAISE FND_API.G_EXC_ERROR;
472 WHEN OTHERS THEN
473 -- ROLLBACK;
474 FND_MESSAGE.Set_Name('OKS', 'OKS_IMPORT_UNEXPECTED');
475 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
476 FND_MESSAGE.set_token('MESSAGE', 'stmt_num '||l_stmt_num||' ('||SQLCODE||') '||SQLERRM);
477 FND_MSG_PUB.Add;
478 RAISE FND_API.G_EXC_ERROR;
479
480 END Generate_bil_sch_Subs_lines;
481
482 --========================================================================
483 -- PROCEDURE : Generate_bil_sch_Usage_lines PRIVATE
484 -- PARAMETERS :
485 -- COMMENT : This procedure will invoke the API's to generate
486 -- the Billing Streams and schedules for Usage Lines
487 --=========================================================================
488
489 PROCEDURE Generate_bil_sch_Usage_lines
490 IS
491 l_stmt_num NUMBER := 0;
492 l_routine CONSTANT VARCHAR2(30) := 'Generate_bil_sch_Subs_lines';
493 l_int_count NUMBER := 0;
494 l_stg_count NUMBER := 0;
495 l_recur_bill_occurance NUMBER := 0 ;
496 BEGIN
497
498 IF G_PROCEDURE_LOG THEN
499 fnd_log.string(fnd_log.level_procedure,
500 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
501 'Entering ');
502
503 END IF;
504
505 --- Generates billing Streams for usage Counters
506 l_stmt_num := 10;
507
508 INSERT ALL
509 WHEN (FREQUENCY IS NOT NULL ) then
510 INTO OKS_STREAM_LEVELS_B
514 ,DNZ_CHR_ID
511 (ID
512 ,CHR_ID
513 ,CLE_ID
515 ,SEQUENCE_NO
516 ,UOM_CODE
517 ,START_DATE
518 ,END_DATE
519 ,LEVEL_PERIODS
520 ,UOM_PER_PERIOD
521 ,LEVEL_AMOUNT
522 ,OBJECT_VERSION_NUMBER
523 ,REQUEST_ID
524 ,CREATED_BY
525 ,CREATION_DATE
526 ,LAST_UPDATED_BY
527 ,LAST_UPDATE_DATE
528 ,LAST_UPDATE_LOGIN)
529
530 VALUES (ID
531 ,CHR_ID
532 ,CLE_ID
533 ,DNZ_CHR_ID
534 ,SEQUENCE_NO
535 ,UOM_CODE
536 ,START_DATE
537 ,END_DATE
538 ,LEVEL_PERIODS
539 ,UOM_PER_PERIOD
540 ,LEVEL_AMOUNT
541 ,OBJECT_VERSION_NUMBER
542 ,REQUEST_ID
543 ,CREATED_BY
544 ,CREATION_DATE
545 ,LAST_UPDATED_BY
546 ,LAST_UPDATE_DATE
547 ,LAST_UPDATE_LOGIN)
548
549 SELECT okc_p_util.raw_to_number(sys_guid()) ID
550 ,INNER_Q2.SEQ SEQUENCE_NO
551 ,(CASE WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NOT NULL THEN 'DAY'
552 WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NULL THEN INNER_Q1.BILLING_INTERVAL_PERIOD
553 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS not NULL THEN INNER_Q1.BILLING_INTERVAL_PERIOD
554 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS null THEN 'DAY'
555 WHEN INNER_Q2.SEQ = 3 THEN 'DAY'
556 else INNER_Q1.BILLING_INTERVAL_PERIOD
557 END) UOM_CODE
558
559 ,(CASE WHEN INNER_Q2.SEQ = 1 THEN INNER_Q1.LIN_STR_DT
560 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date is not null THEN INNER_Q1.FIRST_BILL_UPTO_DATE + 1
561 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NULL THEN INNER_Q1.LAST_BILL_FROM_DATE
562 WHEN INNER_Q2.SEQ = 3 THEN INNER_Q1.LAST_BILL_FROM_DATE
563 END) START_DATE
564
565 ,(CASE WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NOT NULL THEN INNER_Q1.FIRST_BILL_UPTO_DATE
566 WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NULL THEN INNER_Q1.LAST_BILL_FROM_DATE - 1
567 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NULL THEN INNER_Q1.LIN_END_DT
568 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NOT NULL THEN INNER_Q1.LAST_BILL_FROM_DATE - 1
569 WHEN INNER_Q2.SEQ = 3 THEN INNER_Q1.LIN_END_DT
570 END) END_DATE
571
572 ,(CASE WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NOT NULL THEN 1
573 WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NULL THEN INNER_Q1.RECUR_BILL_OCCURANCES
574 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS not NULL THEN INNER_Q1.RECUR_BILL_OCCURANCES
575 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NULL THEN 1
576 WHEN INNER_Q2.SEQ = 3 THEN 1
577 END) LEVEL_PERIODS
578
579 ,(CASE WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NOT NULL
580 THEN INNER_Q1.FIRST_BILL_UPTO_DATE - INNER_Q1.CVL_START_DT + 1 -- difference in the days with the days inclusive
581 WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NULL THEN INNER_Q1.BILLING_INTERVAL_DURATION
582 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NOT NULL THEN INNER_Q1.BILLING_INTERVAL_DURATION
583 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NULL THEN INNER_Q1.CVL_END_DT -(INNER_Q1.LAST_BILL_FROM_DATE - 1)
584 WHEN INNER_Q2.SEQ = 3 THEN INNER_Q1.CVL_END_DT -(INNER_Q1.LAST_BILL_FROM_DATE - 1 )
585 ELSE INNER_Q1.BILLING_INTERVAL_DURATION
586 END) UOM_PER_PERIOD
587
588 ,(CASE WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NOT NULL -- first stream
589 THEN ROUND((INNER_Q1.SUBTOTAL/INNER_Q1.NO_OF_DAYS) * (INNER_Q1.FIRST_BILL_UPTO_DATE - INNER_Q1.LIN_STR_DT +1) *1,2)
590 WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NULL -- normal stream
591 THEN ROUND((INNER_Q1.SUBTOTAL/CALC_BILL_PERIOD_1) * INNER_Q1.BILLING_INTERVAL_DURATION * INNER_Q1.RECUR_BILL_OCCURANCES,2)
592 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NOT NULL --normal stream
593 THEN ROUND((INNER_Q1.SUBTOTAL -
594 ROUND((INNER_Q1.SUBTOTAL/INNER_Q1.NO_OF_DAYS) *
595 (INNER_Q1.FIRST_BILL_UPTO_DATE - INNER_Q1.LIN_STR_DT +1) *1,2))/CALC_BILL_PERIOD_2
596 *INNER_Q1.BILLING_INTERVAL_DURATION * INNER_Q1.RECUR_BILL_OCCURANCES,2)
597 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NULL -- last stream
598 THEN INNER_Q1.SUBTOTAL -
599 ROUND((INNER_Q1.SUBTOTAL/CALC_BILL_PERIOD_1) *
600 INNER_Q1.BILLING_INTERVAL_DURATION * INNER_Q1.RECUR_BILL_OCCURANCES,2)
601 WHEN INNER_Q2.SEQ = 3
602 THEN
603 INNER_Q1.SUBTOTAL
604 - ROUND((INNER_Q1.SUBTOTAL/INNER_Q1.NO_OF_DAYS) * (INNER_Q1.FIRST_BILL_UPTO_DATE - INNER_Q1.LIN_STR_DT +1) *1,2)
605 - ROUND((INNER_Q1.SUBTOTAL -
606 ROUND((INNER_Q1.SUBTOTAL/INNER_Q1.NO_OF_DAYS) *
607 (INNER_Q1.FIRST_BILL_UPTO_DATE - INNER_Q1.LIN_STR_DT +1) *1,2))/CALC_BILL_PERIOD_2
608 *INNER_Q1.BILLING_INTERVAL_DURATION * INNER_Q1.RECUR_BILL_OCCURANCES,2)
609
610 END) LEVEL_AMOUNT
611 ,INNER_Q1.RECUR_BILL_OCCURANCES FREQUENCY
612 ,INNER_Q1.*
613 ,INNER_Q2.*
614 FROM
615 (SELECT OKCLINB_SUBLINE.ID CLE_ID
616 ,null CHR_ID -- can be null for sublines
617 ,OKCHDRB.ID DNZ_CHR_ID
618 ,(CASE WHEN OLSTG.FIRST_BILL_UPTO_DATE IS NULL AND (OLSTG.LAST_BILL_FROM_DATE -1 ) = OLSTG.END_DATE THEN 1
619 WHEN OLSTG.FIRST_BILL_UPTO_DATE IS NOT NULL AND (OLSTG.LAST_BILL_FROM_DATE -1) = OLSTG.END_DATE THEN 2
620 WHEN OLSTG.FIRST_BILL_UPTO_DATE IS NULL AND (OLSTG.LAST_BILL_FROM_DATE -1 ) < OLSTG.END_DATE THEN 2
621 WHEN OLSTG.FIRST_BILL_UPTO_DATE IS NOT NULL AND (OLSTG.LAST_BILL_FROM_DATE -1 ) < OLSTG.END_DATE THEN 3
622 END) NUM_STREAMS
623 ,1 OBJECT_VERSION_NUMBER
624 ,null REQUEST_ID
625 ,FND_GLOBAL.USER_ID CREATED_BY
626 ,SYSDATE CREATION_DATE
627 ,FND_GLOBAL.USER_ID LAST_UPDATED_BY
628 ,SYSDATE LAST_UPDATE_DATE
629 ,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
633 ,OLSTG.RECUR_BILL_OCCURANCES RECUR_BILL_OCCURANCES
630 ,OLSTG.LAST_BILL_FROM_DATE LAST_BILL_FROM_DATE
631 ,OLSTG.BILLING_INTERVAL_PERIOD BILLING_INTERVAL_PERIOD
632 ,OLSTG.BILLING_INTERVAL_DURATION BILLING_INTERVAL_DURATION
634 ,INNER_1.STR_DT CVL_START_DT
635 ,INNER_1.END_DT CVL_END_DT
636 ,OLSTG.FIRST_BILL_UPTO_DATE FIRST_BILL_UPTO_DATE
637 ,nvl(INNER_1.STOTAL,0) SUBTOTAL
638 ,OLSTG.LINE_TYPE LINE_TYPE
639 ,OLSTG.START_DATE LIN_STR_DT
640 ,OLSTG.END_DATE LIN_END_DT
641 ,(INNER_1.END_DT - INNER_1.STR_DT)+1 NO_OF_DAYS
642 ,(CASE WHEN OLSTG.BILLING_INTERVAL_PERIOD ='DAY' THEN (OLSTG.END_DATE - OLSTG.START_DATE)+1 --no_of_day
643 WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'WK' THEN ((OLSTG.END_DATE - OLSTG.START_DATE)+1)/7 -- no of weeks
644 WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'MTH' THEN MONTHS_BETWEEN(OLSTG.END_DATE + 1,OLSTG.START_DATE) -- no of months
645 WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'QRT' THEN MONTHS_BETWEEN(OLSTG.END_DATE + 1,OLSTG.START_DATE)/3 -- no of quarter
646 WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'YR' THEN MONTHS_BETWEEN(OLSTG.END_DATE + 1,OLSTG.START_DATE)/12 -- no of years
647 END) CALC_BILL_PERIOD_1
648 ,(CASE WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'DAY' THEN (OLSTG.END_dATE -OLSTG.FIRST_BILL_UPTO_DATE +1) +1 --no of days
649 WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'WK' THEN ((OLSTG.END_DATE - OLSTG.FIRST_BILL_UPTO_DATE + 1) +1 )/7 -- no of weeks
650 WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'MTH' THEN MONTHS_BETWEEN(OLSTG.END_DATE + 1,(OLSTG.FIRST_BILL_UPTO_DATE+1))-- no of months
651 WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'QRT' THEN MONTHS_BETWEEN(OLSTG.END_DATE + 1,(OLSTG.FIRST_BILL_UPTO_DATE+1))/3 -- no of quarter
652 WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'YR' THEN MONTHS_BETWEEN(OLSTG.END_DATE + 1,(OLSTG.FIRST_BILL_UPTO_DATE+1))/12 -- no of years
653 END) CALC_BILL_PERIOD_2
654 FROM OKS_INT_LINE_STG_TEMP OLSTG
655
656 ,(SELECT USGSTG.LINE_INTERFACE_ID LINE_INTERFACE_ID
657 ,USGSTG.LINE_NUMBER LINE_NUMBER
658 ,USGSTG.START_DATE STR_DT
659 ,USGSTG.END_DATE END_DT
660 ,USGSTG.SUBTOTAL STOTAL
661 FROM OKS_INT_USAGE_COUNTER_STG_TEMP USGSTG )INNER_1
662
663 ,OKC_K_LINES_B OKCLINB_LINE
664 ,OKC_K_LINES_B OKCLINB_SUBLINE
665 ,OKC_K_HEADERS_ALL_B OKCHDRB
666 ,OKS_INT_HEADER_STG_TEMP HDRSTG
667 WHERE INNER_1.LINE_INTERFACE_ID = OLSTG.LINE_INTERFACE_ID
668 AND OLSTG.HEADER_INTERFACE_ID = HDRSTG.HEADER_INTERFACE_ID
669 AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
670 AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
671 AND HDRSTG.INTERFACE_STATUS ='S'
672 AND OKCLINB_LINE.DNZ_CHR_ID = OKCHDRB.ID
673 AND OKCLINB_LINE.CHR_ID = OKCHDRB.ID
674 AND OLSTG.LINE_NUMBER = OKCLINB_LINE.LINE_NUMBER
675 AND OKCLINB_SUBLINE.DNZ_CHR_ID = OKCHDRB.ID
676 AND OKCLINB_SUBLINE.CLE_ID = OKCLINB_LINE.ID
677 AND OKCLINB_SUBLINE.LINE_NUMBER = INNER_1.LINE_NUMBER
678 AND OLSTG.LINE_TYPE = 'USAGE') INNER_Q1
679
680 ,(SELECT ROWNUM AS SEQ FROM DUAL CONNECT BY LEVEL <= 3) INNER_Q2
681
682 WHERE INNER_Q2.SEQ <= INNER_Q1.NUM_STREAMS;
683
684 l_int_count := SQL%ROWCOUNT;
685 /* IF G_STMT_LOG THEN
686 fnd_log.string(fnd_log.level_statement,
687 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
688 'Number of records successfully inserted = ' || l_int_count );
689
690 END IF; */
691
692 l_stmt_num :=20;
693
694 SELECT MAX(RECUR_BILL_OCCURANCES) INTO l_recur_bill_occurance FROM OKS_INT_LINE_STG_TEMP ;
695
696 /* IF G_STMT_LOG THEN
697
698 fnd_log.string(fnd_log.level_statement,
699 G_MODULE_HEAD || l_routine || '.' || l_stmt_num,
700 'Value of max Recur Bill Occurance = ' || l_recur_bill_occurance );
701 END IF; */
702
703
704 l_stmt_num :=30;
705
706 -- this query inserts records into level elements as schedules for billing streams for Usage Counters
707
708 INSERT ALL
709 WHEN (FREQUENCY IS NOT NULL ) then
710 INTO OKS_LEVEL_ELEMENTS
711 (ID
712 ,SEQUENCE_NUMBER
713 ,DATE_START
714 ,AMOUNT
715 ,DATE_TRANSACTION
716 ,DATE_TO_INTERFACE
717 ,DATE_COMPLETED
718 ,OBJECT_VERSION_NUMBER
719 ,RUL_ID
720 ,CREATED_BY
721 ,CREATION_DATE
722 ,LAST_UPDATED_BY
723 ,LAST_UPDATE_DATE
724 ,CLE_ID
725 ,DNZ_CHR_ID
726 ,PARENT_CLE_ID
727 ,DATE_END)
728 VALUES (ID
729 ,SEQUENCE_NUMBER
730 ,DATE_START
731 ,AMOUNT
732 ,DATE_TRANSACTION
733 ,DATE_TO_INTERFACE
734 ,DATE_COMPLETED
735 ,OBJECT_VERSION_NUMBER
736 ,RUL_ID
737 ,CREATED_BY
738 ,CREATION_DATE
739 ,LAST_UPDATED_BY
740 ,LAST_UPDATE_DATE
741 ,CLE_ID
742 ,DNZ_CHR_ID
743 ,PARENT_CLE_ID
744 ,DATE_END)
745 SELECT COV_SCH_DT.*
746 ,(CASE WHEN COV_SCH_DT.INVOICING_RULE_ID = -2
747 THEN
748 (CASE WHEN COV_SCH_DT.DATE_START >= SYSDATE THEN COV_SCH_DT.DATE_START
749 ELSE SYSDATE
750 END)
751 WHEN COV_SCH_DT.INVOICING_RULE_ID = -3
752 THEN
753 (CASE WHEN COV_SCH_DT.DATE_END > = SYSDATE THEN COV_SCH_DT.DATE_END
754 ELSE SYSDATE
755 END)
756 END) DATE_TRANSACTION
757
758 ,(CASE WHEN COV_SCH_DT.INVOICING_RULE_ID = -2 THEN COV_SCH_DT.DATE_START
759 WHEN COV_SCH_DT.INVOICING_RULE_ID = -3 THEN COV_SCH_DT.DATE_END +1
760 END) DATE_TO_INTERFACE
761
762 FROM
763 (SELECT okc_p_util.raw_to_number(sys_guid()) ID
764 ,INNER_Q2.SEQ SEQUENCE_NUMBER
765 ,(CASE WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.STRM_END_DATE = INNER_Q1.FIRST_BILL_UPTO_DATE -- first bill stream
766 AND INNER_Q1.FIRST_BILL_UPTO_DATE IS NOT NULL
770 THEN INNER_Q1.last_bill_from_date
767 THEN INNER_Q1.CVL_START_DT
768
769 WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.STRM_START_DATE = inner_q1.last_bill_from_date -- last bill stream
771 -- IN OTHER CASES
772 ELSE DECODE (INNER_Q1.BILLING_INTERVAL_PERIOD
773 ,'DAY' , MID_SM_STR_DT + (INNER_Q2.SEQ -1 )
774 ,'WK' , MID_SM_STR_DT + (7 * (INNER_Q2.SEQ -1 ))
775 ,'MTH' , ADD_MONTHS(MID_SM_STR_DT , (INNER_Q2.SEQ -1 ))
776 ,'QRT' , ADD_MONTHS(MID_SM_STR_DT , 3 * (INNER_Q2.SEQ -1 ))
777 ,'YR' , ADD_MONTHS(MID_SM_STR_DT , 12 * (INNER_Q2.SEQ -1 )) )
778 END ) DATE_START
779
780 ,(CASE WHEN INNER_Q1.SEQUENCE_NO = 1 AND INNER_Q1.FIRST_BILL_UPTO_DATE IS NOT NULL
781 THEN INNER_Q1.LEVEL_AMOUNT
782 WHEN INNER_Q1.SEQUENCE_NO = 1 AND INNER_Q1.FIRST_BILL_UPTO_DATE IS NULL -- normal stream
783 THEN (CASE WHEN INNER_Q1.LEVEL_AMOUNT - ROUND(INNER_Q1.LEVEL_AMOUNT/INNER_Q1.LEVEL_PERIODS,2) *(INNER_Q2.SEQ-1) >0
784 THEN (CASE WHEN INNER_Q2.SEQ =INNER_Q1.LEVEL_PERIODS --if it is the last schedule for the stream, value difference due to rounding is to be adjusted
785 AND ROUND(INNER_Q1.LEVEL_AMOUNT/INNER_Q1.LEVEL_PERIODS,2) = 0
786 THEN INNER_Q1.LEVEL_AMOUNT
787 WHEN INNER_Q2.SEQ =INNER_Q1.LEVEL_PERIODS AND ROUND(INNER_Q1.LEVEL_AMOUNT/INNER_Q1.LEVEL_PERIODS,2) >0
788 THEN INNER_Q1.LEVEL_AMOUNT -
789 ROUND(INNER_Q1.LEVEL_AMOUNT/INNER_Q1.LEVEL_PERIODS,2)*(INNER_Q2.SEQ-1)
790 ELSE ROUND(INNER_Q1.LEVEL_AMOUNT/INNER_Q1.LEVEL_PERIODS,2)
791 END)
792 ELSE 0
793 END)
794 WHEN INNER_Q1.SEQUENCE_NO = 2 AND INNER_Q1.FIRST_BILL_UPTO_DATE IS NOT NULL -- then it is normal stream
795 THEN (CASE WHEN INNER_Q1.LEVEL_AMOUNT - ROUND(INNER_Q1.LEVEL_AMOUNT/INNER_Q1.LEVEL_PERIODS,2)*(INNER_Q2.SEQ-1) >0
796 THEN (CASE WHEN INNER_Q2.SEQ =INNER_Q1.LEVEL_PERIODS
797 AND ROUND(INNER_Q1.LEVEL_AMOUNT/INNER_Q1.LEVEL_PERIODS,2) = 0
798 THEN INNER_Q1.LEVEL_AMOUNT
799 WHEN INNER_Q2.SEQ = INNER_Q1.LEVEL_PERIODS AND ROUND(INNER_Q1.LEVEL_AMOUNT/INNER_Q1.LEVEL_PERIODS,2) >0
800 THEN INNER_Q1.LEVEL_AMOUNT -
801 ROUND(INNER_Q1.LEVEL_AMOUNT/INNER_Q1.LEVEL_PERIODS,2)*(INNER_Q2.SEQ-1)
802 ELSE ROUND(INNER_Q1.LEVEL_AMOUNT/INNER_Q1.LEVEL_PERIODS,2)
803 END)
804 ELSE 0
805 END)
806 WHEN INNER_Q1.SEQUENCE_NO = 2 AND INNER_Q1.FIRST_BILL_UPTO_DATE IS NULL -- last bill stream
807 THEN INNER_Q1.LEVEL_AMOUNT
808 WHEN INNER_Q1.SEQUENCE_NO = 3 THEN INNER_Q1.LEVEL_AMOUNT
809 END) AMOUNT
810 ,(CASE WHEN INNER_Q1.FULLY_BILLED = 'Y' THEN SYSDATE
811 ELSE NULL
812 END) DATE_COMPLETED
813 ,INNER_Q1.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
814 ,INNER_Q1.OKS_STRM_LVL_ID RUL_ID
815 ,FND_GLOBAL.USER_ID CREATED_BY
816 ,SYSDATE CREATION_DATE
817 ,FND_GLOBAL.USER_ID LAST_UPDATED_BY
818 ,SYSDATE LAST_UPDATE_DATE
819 ,INNER_Q1.CLE_ID CLE_ID
820 ,INNER_Q1.DNZ_CHR_ID DNZ_CHR_ID
821 ,INNER_Q1.PARENT_CLE_ID PARENT_CLE_ID
822 ,(CASE WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.STRM_END_DATE = INNER_Q1.FIRST_BILL_UPTO_DATE -- first bill stream
823 AND INNER_Q1.FIRST_BILL_UPTO_DATE IS NOT NULL
824 THEN INNER_Q1.FIRST_BILL_UPTO_DATE
825 WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.STRM_START_DATE = inner_q1.last_bill_from_date -- last bill stream
826 THEN INNER_Q1.CVL_END_DT
827 -- IN OTHER CASES
828 ELSE DECODE( INNER_Q1.BILLING_INTERVAL_PERIOD
829 , 'DAY' , MID_SM_STR_DT + INNER_Q2.SEQ -1
830 , 'WK' , MID_SM_STR_DT + (7 * (INNER_Q2.SEQ)) -1
831 , 'MTH' , ADD_MONTHS(MID_SM_STR_DT , (INNER_Q2.SEQ )) -1
832 , 'QRT' , ADD_MONTHS(MID_SM_STR_DT , 3 * (INNER_Q2.SEQ )) -1
833 , 'YR' , ADD_MONTHS(MID_SM_STR_DT , 12 * (INNER_Q2.SEQ )) -1 )
834
835 END ) DATE_END
836 ,INNER_Q1.RECUR_BILL_OCCURANCES FREQUENCY
837 ,INNER_Q1.INVOICING_RULE_ID INVOICING_RULE_ID
838 FROM
839 (SELECT OKCLINB_SUBLINE.ID CLE_ID
840 ,OKS_STRM_LVL.ID OKS_STRM_LVL_ID
841 ,null CHR_ID -- can be null for sublines
842 ,OKCHDRB.ID DNZ_CHR_ID
843 ,OKCLINB_LINE.ID PARENT_CLE_ID
844 ,OKCLINB_LINE.INV_RULE_ID INVOICING_RULE_ID
845 ,1 OBJECT_VERSION_NUMBER
846 ,NVL(INNER_1.STOTAL,0) SUBTOTAL
847 ,OLSTG.LAST_BILL_FROM_DATE LAST_BILL_FROM_DATE
848 ,OLSTG.BILLING_INTERVAL_PERIOD BILLING_INTERVAL_PERIOD
849 ,OLSTG.BILLING_INTERVAL_DURATION BILLING_INTERVAL_DURATION
850 ,OLSTG.RECUR_BILL_OCCURANCES RECUR_BILL_OCCURANCES
851 ,INNER_1.STR_DT CVL_START_DT
852 ,INNER_1.END_DT CVL_END_DT
853 ,OLSTG.FIRST_BILL_UPTO_DATE FIRST_BILL_UPTO_DATE
854 ,OKS_STRM_LVL.LEVEL_PERIODS LEVEL_PERIODS
855 ,OKS_STRM_LVL.LEVEL_AMOUNT LEVEL_AMOUNT
856 ,HDRSTG.FULLY_BILLED FULLY_BILLED
857 ,OKS_STRM_LVL.START_DATE STRM_START_DATE
858 ,OKS_STRM_LVL.END_DATE STRM_END_DATE
859 ,OLSTG.LINE_TYPE LINE_TYPE
860 ,OKS_STRM_LVL.SEQUENCE_NO SEQUENCE_NO
861 ,(CASE WHEN OLSTG.FIRST_BILL_UPTO_DATE IS NOT NULL THEN OLSTG.FIRST_BILL_UPTO_DATE +1
862 ELSE INNER_1.STR_DT
863 END) MID_SM_STR_DT
864 ,OLSTG.LAST_BILL_FROM_DATE -1 MID_SM_END_DT
865
866 FROM OKS_INT_LINE_STG_TEMP OLSTG
867 ,(SELECT USGSTG.LINE_INTERFACE_ID LINE_INTERFACE_ID
868 ,USGSTG.LINE_NUMBER LINE_NUMBER
869 ,USGSTG.START_DATE STR_DT
870 ,USGSTG.END_DATE END_DT
871 ,USGSTG.SUBTOTAL STOTAL
872 FROM OKS_INT_USAGE_COUNTER_STG_TEMP USGSTG ) INNER_1
873 ,OKC_K_LINES_B OKCLINB_LINE
874 ,OKC_K_LINES_B OKCLINB_SUBLINE
875 ,OKC_K_HEADERS_ALL_B OKCHDRB
876 ,OKS_INT_HEADER_STG_TEMP HDRSTG
877 ,OKS_STREAM_LEVELS_B OKS_STRM_LVL
878 WHERE INNER_1.LINE_INTERFACE_ID = OLSTG.LINE_INTERFACE_ID
882 AND HDRSTG.INTERFACE_STATUS ='S'
879 AND OLSTG.HEADER_INTERFACE_ID = HDRSTG.HEADER_INTERFACE_ID
880 AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
881 AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
883 AND OKCLINB_LINE.DNZ_CHR_ID = OKCHDRB.ID
884 AND OKCLINB_LINE.CHR_ID = OKCHDRB.ID
885 AND OLSTG.LINE_NUMBER = OKCLINB_LINE.LINE_NUMBER
886 AND OKCLINB_SUBLINE.DNZ_CHR_ID = OKCHDRB.ID
887 AND OKCLINB_SUBLINE.CLE_ID = OKCLINB_LINE.ID
888 AND OKCLINB_SUBLINE.LINE_NUMBER = INNER_1.LINE_NUMBER
889 AND OKS_STRM_LVL.DNZ_CHR_ID = OKCHDRB.ID
890 AND OKS_STRM_LVL.CLE_ID = OKCLINB_SUBLINE.ID
891 AND OKS_STRM_LVL.CHR_ID IS NULL
892 AND OLSTG.LINE_TYPE = 'USAGE') INNER_Q1
893
894 ,(SELECT ROWNUM AS SEQ FROM DUAL CONNECT BY LEVEL <= l_recur_bill_occurance ) INNER_Q2
895
896 WHERE INNER_Q2.SEQ <= INNER_Q1.LEVEL_PERIODS) COV_SCH_DT;
897
898
899 --Generates Billing Streams for Usage Lines
900
901 l_stmt_num :=40;
902
903 INSERT ALL
904 WHEN (FREQUENCY IS NOT NULL) THEN
905 INTO OKS_STREAM_LEVELS_B
906 (ID
907 ,CHR_ID
908 ,CLE_ID
909 ,DNZ_CHR_ID
910 ,SEQUENCE_NO
911 ,UOM_CODE
912 ,START_DATE
913 ,END_DATE
914 ,LEVEL_PERIODS
915 ,UOM_PER_PERIOD
916 ,LEVEL_AMOUNT
917 ,OBJECT_VERSION_NUMBER
918 ,REQUEST_ID
919 ,CREATED_BY
920 ,CREATION_DATE
921 ,LAST_UPDATED_BY
922 ,LAST_UPDATE_DATE
923 ,LAST_UPDATE_LOGIN)
924
925 VALUES (ID
926 ,CHR_ID
927 ,CLE_ID
928 ,DNZ_CHR_ID
929 ,SEQUENCE_NO
930 ,UOM_CODE
931 ,START_DATE
932 ,END_DATE
933 ,LEVEL_PERIODS
934 ,UOM_PER_PERIOD
935 ,LEVEL_AMOUNT
936 ,OBJECT_VERSION_NUMBER
937 ,REQUEST_ID
938 ,CREATED_BY
939 ,CREATION_DATE
940 ,LAST_UPDATED_BY
941 ,LAST_UPDATE_DATE
942 ,LAST_UPDATE_LOGIN)
943 SELECT okc_p_util.raw_to_number(sys_guid()) ID
944 ,INNER_Q2.SEQ SEQUENCE_NO
945 ,(CASE WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NOT NULL THEN 'DAY'
946 WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NULL THEN INNER_Q1.BILLING_INTERVAL_PERIOD
947 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NOT NULL THEN INNER_Q1.BILLING_INTERVAL_PERIOD
948 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NULL THEN 'DAY'
949 WHEN INNER_Q2.SEQ = 3 THEN 'DAY'
950 ELSE INNER_Q1.BILLING_INTERVAL_PERIOD
951 END) UOM_CODE
952 ,(CASE WHEN INNER_Q2.SEQ = 1 THEN INNER_Q1.LIN_STR_DT
953 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NOT NULL THEN INNER_Q1.FIRST_BILL_UPTO_DATE + 1
954 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NULL THEN INNER_Q1.LAST_BILL_FROM_DATE
955 WHEN INNER_Q2.SEQ = 3 THEN INNER_Q1.LAST_BILL_FROM_DATE
956 END) START_DATE
957 ,(CASE WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NOT NULL THEN INNER_Q1.FIRST_BILL_UPTO_DATE
958 WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NULL THEN INNER_Q1.LAST_BILL_FROM_DATE - 1
959 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NULL THEN INNER_Q1.LIN_END_DT
960 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NOT NULL THEN INNER_Q1.LAST_BILL_FROM_DATE - 1
961 WHEN INNER_Q2.SEQ = 3 THEN INNER_Q1.LIN_END_DT
962 END) END_DATE
963 ,(CASE WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NOT NULL THEN 1
964 WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NULL THEN INNER_Q1.RECUR_BILL_OCCURANCES
965 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS not NULL THEN INNER_Q1.RECUR_BILL_OCCURANCES
966 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NULL THEN 1
967 WHEN INNER_Q2.SEQ = 3 THEN 1
968 END) LEVEL_PERIODS
969 ,(CASE WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NOT NULL
970 THEN INNER_Q1.FIRST_BILL_UPTO_DATE - INNER_Q1.LIN_STR_DT +1 -- including the days between the difference
971 WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NULL THEN INNER_Q1.BILLING_INTERVAL_DURATION
972 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NOT NULL THEN INNER_Q1.BILLING_INTERVAL_DURATION
973 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NULL THEN INNER_Q1.LIN_END_DT -(INNER_Q1.LAST_BILL_FROM_DATE - 1 )
974 WHEN INNER_Q2.SEQ = 3 THEN INNER_Q1.LIN_END_DT -(INNER_Q1.LAST_BILL_FROM_DATE - 1 )
975 ELSE INNER_Q1.BILLING_INTERVAL_DURATION
976 END) UOM_PER_PERIOD
977 ,(CASE WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NOT NULL -- first stream
978 THEN ROUND((INNER_Q1.SUBTOTAL/INNER_Q1.NO_OF_DAYS) * (INNER_Q1.FIRST_BILL_UPTO_DATE - INNER_Q1.LIN_STR_DT +1) *1,2)
979 WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NULL -- normal stream
980 THEN ROUND((INNER_Q1.SUBTOTAL/CALC_BILL_PERIOD_1) * INNER_Q1.BILLING_INTERVAL_DURATION * INNER_Q1.RECUR_BILL_OCCURANCES,2)
981 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NOT NULL --normal stream
982 THEN ROUND((INNER_Q1.SUBTOTAL -
983 ROUND((INNER_Q1.SUBTOTAL/INNER_Q1.NO_OF_DAYS) *
984 (INNER_Q1.FIRST_BILL_UPTO_DATE - INNER_Q1.LIN_STR_DT +1) *1,2))/CALC_BILL_PERIOD_2
985 *INNER_Q1.BILLING_INTERVAL_DURATION * INNER_Q1.RECUR_BILL_OCCURANCES,2)
986 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NULL -- last stream
987 THEN INNER_Q1.SUBTOTAL -
988 ROUND((INNER_Q1.SUBTOTAL/CALC_BILL_PERIOD_1) *
989 INNER_Q1.BILLING_INTERVAL_DURATION * INNER_Q1.RECUR_BILL_OCCURANCES,2)
990 WHEN INNER_Q2.SEQ = 3
991 THEN INNER_Q1.SUBTOTAL -
992 ROUND((INNER_Q1.SUBTOTAL/INNER_Q1.NO_OF_DAYS) * (INNER_Q1.FIRST_BILL_UPTO_DATE - INNER_Q1.LIN_STR_DT +1) *1,2)
993 - ROUND((INNER_Q1.SUBTOTAL -
997
994 ROUND((INNER_Q1.SUBTOTAL/INNER_Q1.NO_OF_DAYS) *
995 (INNER_Q1.FIRST_BILL_UPTO_DATE - INNER_Q1.LIN_STR_DT +1) *1,2))/CALC_BILL_PERIOD_2
996 *INNER_Q1.BILLING_INTERVAL_DURATION * INNER_Q1.RECUR_BILL_OCCURANCES,2)
998 END) LEVEL_AMOUNT
999 ,INNER_Q1.RECUR_BILL_OCCURANCES FREQUENCY
1000 ,INNER_Q1.*
1001 ,INNER_Q2.*
1002 FROM
1003 (SELECT OKCLINB_LINE.ID LINE_ID
1004 ,null CHR_ID
1005 ,OKCLINB_LINE.ID CLE_ID
1006 ,OKCHDRB.ID DNZ_CHR_ID
1007 ,OLSTG.FIRST_BILL_UPTO_DATE FIRST_BILL_UPTO_DATE
1008 ,OLSTG.FIRST_BILLED_AMOUNT SUM_FBILL
1009 ,OLSTG.LAST_BILLED_AMOUNT SUM_LBILL
1010 ,(CASE WHEN OLSTG.FIRST_BILL_UPTO_DATE IS NULL AND (OLSTG.LAST_BILL_FROM_DATE -1 ) = OLSTG.END_DATE THEN 1
1011 WHEN OLSTG.FIRST_BILL_UPTO_DATE IS NOT NULL AND (OLSTG.LAST_BILL_FROM_DATE -1) = OLSTG.END_DATE THEN 2
1012 WHEN OLSTG.FIRST_BILL_UPTO_DATE IS NULL AND (OLSTG.LAST_BILL_FROM_DATE -1 ) < OLSTG.END_DATE THEN 2
1013 WHEN OLSTG.FIRST_BILL_UPTO_DATE IS NOT NULL AND (OLSTG.LAST_BILL_FROM_DATE -1 ) < OLSTG.END_DATE THEN 3
1014 END) NUM_STREAMS
1015 ,OLSTG.BILLING_INTERVAL_PERIOD BILLING_INTERVAL_PERIOD
1016 ,(CASE WHEN OLSTG.BILLING_INTERVAL_PERIOD ='DAY' THEN (OLSTG.END_DATE - OLSTG.START_DATE)+1 --no_of_day
1017 WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'WK' THEN ((OLSTG.END_DATE - OLSTG.START_DATE)+1)/7 -- no of weeks
1018 WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'MTH' THEN MONTHS_BETWEEN(OLSTG.END_DATE + 1 ,OLSTG.START_DATE) -- no of months
1019 WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'QRT' THEN MONTHS_BETWEEN(OLSTG.END_DATE + 1,OLSTG.START_DATE)/3 -- no of quarter
1020 WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'YR' THEN MONTHS_BETWEEN(OLSTG.END_DATE + 1,OLSTG.START_DATE)/12 -- no of years
1021 END) CALC_BILL_PERIOD_1
1022 ,(CASE WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'DAY' THEN (OLSTG.END_dATE -OLSTG.FIRST_BILL_UPTO_DATE +1) +1 --no of days
1023 WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'WK' THEN ((OLSTG.END_DATE - OLSTG.FIRST_BILL_UPTO_DATE + 1) +1 )/7 -- no of weeks
1024 WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'MTH' THEN MONTHS_BETWEEN(OLSTG.END_DATE + 1,(OLSTG.FIRST_BILL_UPTO_DATE+1))-- no of months
1025 WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'QRT' THEN MONTHS_BETWEEN(OLSTG.END_DATE + 1,(OLSTG.FIRST_BILL_UPTO_DATE+1))/3 -- no of quarter
1026 WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'YR' THEN MONTHS_BETWEEN(OLSTG.END_DATE + 1,(OLSTG.FIRST_BILL_UPTO_DATE+1))/12 -- no of years
1027 END) CALC_BILL_PERIOD_2
1028 ,OLSTG.LINE_TYPE LINE_TYPE
1029 ,OLSTG.RECUR_BILL_OCCURANCES RECUR_BILL_OCCURANCES
1030 ,OLSTG.BILLING_INTERVAL_DURATION BILLING_INTERVAL_DURATION
1031 ,NVL(OKCLINB_LINE.PRICE_NEGOTIATED,0) SUBTOTAL
1032 ,OLSTG.START_DATE LIN_STR_DT
1033 ,OLSTG.END_DATE LIN_END_DT
1034 ,(OLSTG.END_DATE - OLSTG.START_DATE)+1 NO_OF_DAYS
1035 ,OLSTG.LAST_BILL_FROM_DATE LAST_BILL_FROM_DATE
1036 ,1 OBJECT_VERSION_NUMBER
1037 ,NULL REQUEST_ID -- need to confirm
1038 ,FND_GLOBAL.USER_ID CREATED_BY
1039 ,SYSDATE CREATION_DATE
1040 ,FND_GLOBAL.USER_ID LAST_UPDATED_BY
1041 ,SYSDATE LAST_UPDATE_DATE
1042 ,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
1043
1044 FROM OKS_INT_LINE_STG_TEMP OLSTG
1045 ,OKS_INT_HEADER_STG_TEMP HDRSTG
1046 ,OKC_K_HEADERS_ALL_B OKCHDRB
1047 ,OKC_K_LINES_B OKCLINB_LINE
1048 WHERE OLSTG.HEADER_INTERFACE_ID = HDRSTG.HEADER_INTERFACE_ID
1049 AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
1050 AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
1051 AND HDRSTG.INTERFACE_STATUS ='S'
1052 AND OKCLINB_LINE.DNZ_CHR_ID = OKCHDRB.ID
1053 AND OKCLINB_LINE.CHR_ID = OKCHDRB.ID
1054 AND OKCLINB_LINE.LINE_NUMBER = OLSTG.LINE_NUMBER
1055 AND OLSTG.LINE_TYPE ='USAGE') INNER_Q1
1056 ,(SELECT ROWNUM AS SEQ FROM DUAL CONNECT BY LEVEL <= 3) INNER_Q2
1057 WHERE INNER_Q2.SEQ <= INNER_Q1.NUM_STREAMS;
1058
1059 /* l_int_count := SQL%ROWCOUNT;
1060 IF G_STMT_LOG THEN
1061 null;
1062 fnd_log.string(fnd_log.level_statement,
1063 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
1064 'Number of records successfully inserted = ' || l_int_count );
1065 END IF;*/
1066
1067 -- Generates Shedules for usage lines
1068 l_stmt_num :=50;
1069
1070 INSERT ALL
1071 WHEN (FREQUENCY IS NOT NULL ) then
1072 INTO OKS_LEVEL_ELEMENTS
1073 (ID
1074 ,SEQUENCE_NUMBER
1075 ,DATE_START
1076 ,AMOUNT
1077 ,DATE_TRANSACTION
1078 ,DATE_TO_INTERFACE
1079 ,DATE_COMPLETED
1080 ,OBJECT_VERSION_NUMBER
1081 ,RUL_ID
1082 ,CREATED_BY
1083 ,CREATION_DATE
1084 ,LAST_UPDATED_BY
1085 ,LAST_UPDATE_DATE
1086 ,CLE_ID
1087 ,DNZ_CHR_ID
1088 ,PARENT_CLE_ID
1089 ,DATE_END)
1090 VALUES (ID
1091 ,SEQUENCE_NUMBER
1092 ,DATE_START
1093 ,AMOUNT
1094 ,DATE_TRANSACTION
1095 ,DATE_TO_INTERFACE
1096 ,DATE_COMPLETED
1097 ,OBJECT_VERSION_NUMBER
1098 ,RUL_ID
1099 ,CREATED_BY
1100 ,CREATION_DATE
1101 ,LAST_UPDATED_BY
1102 ,LAST_UPDATE_DATE
1103 ,CLE_ID
1104 ,DNZ_CHR_ID
1105 ,PARENT_CLE_ID
1106 ,DATE_END)
1107
1108 SELECT USG_SCH_DT.*
1109 ,(CASE WHEN USG_SCH_DT.INVOICING_RULE_ID = -2
1110 THEN (CASE WHEN USG_SCH_DT.DATE_START >= SYSDATE THEN USG_SCH_DT.DATE_START
1111 ELSE SYSDATE
1112 END)
1113 WHEN USG_SCH_DT.INVOICING_RULE_ID = -3
1114 THEN (CASE WHEN USG_SCH_DT.DATE_END >= SYSDATE THEN USG_SCH_DT.DATE_END
1115 ELSE SYSDATE
1116 END)
1117 END) DATE_TRANSACTION
1118 ,(CASE WHEN USG_SCH_DT.INVOICING_RULE_ID = -2 THEN USG_SCH_DT.DATE_START
1122 (SELECT okc_p_util.raw_to_number(sys_guid()) ID
1119 WHEN USG_SCH_DT.INVOICING_RULE_ID = -3 THEN USG_SCH_DT.DATE_END +1
1120 END) DATE_TO_INTERFACE
1121 FROM
1123 ,INNER_Q2.SEQ SEQUENCE_NUMBER
1124 ,(CASE WHEN INNER_Q2.SEQ=1 THEN INNER_Q1.STRM_START_DATE
1125 -- IN OTHER CASES
1126 ELSE DECODE (INNER_Q1.BILLING_INTERVAL_PERIOD
1127 ,'DAY' , MID_SM_STR_DT + (INNER_Q2.SEQ -1 )
1128 ,'WK' , MID_SM_STR_DT + (7 * (INNER_Q2.SEQ -1 ))
1129 ,'MTH' , ADD_MONTHS(MID_SM_STR_DT , (INNER_Q2.SEQ -1 ))
1130 ,'QRT' , ADD_MONTHS(MID_SM_STR_DT , 3 * (INNER_Q2.SEQ -1 ))
1131 ,'YR' , ADD_MONTHS(MID_SM_STR_DT , 12 * (INNER_Q2.SEQ -1 )) )
1132 END) DATE_START
1133 ,(CASE WHEN INNER_Q1.SEQUENCE_NO = 1 AND INNER_Q1.FIRST_BILL_UPTO_DATE IS NOT NULL --first stream
1134 THEN INNER_Q1.LEVEL_AMOUNT
1135 WHEN INNER_Q1.SEQUENCE_NO = 1 AND INNER_Q1.FIRST_BILL_UPTO_DATE IS NULL -- normal stream
1136 THEN
1137 (CASE WHEN INNER_Q1.LEVEL_AMOUNT - ROUND(INNER_Q1.LEVEL_AMOUNT/INNER_Q1.LEVEL_PERIODS,2) *(INNER_Q2.SEQ-1) >0
1138 THEN (CASE WHEN INNER_Q2.SEQ =INNER_Q1.LEVEL_PERIODS --if it is the last schedule for the stream, value difference due to rounding is to be adjusted
1139 AND ROUND(INNER_Q1.LEVEL_AMOUNT/INNER_Q1.LEVEL_PERIODS,2) = 0 THEN INNER_Q1.LEVEL_AMOUNT
1140 WHEN INNER_Q2.SEQ =INNER_Q1.LEVEL_PERIODS AND ROUND(INNER_Q1.LEVEL_AMOUNT/INNER_Q1.LEVEL_PERIODS,2) >0 THEN
1141 INNER_Q1.LEVEL_AMOUNT - ROUND(INNER_Q1.LEVEL_AMOUNT/INNER_Q1.LEVEL_PERIODS,2)*(INNER_Q2.SEQ-1)
1142 ELSE ROUND(INNER_Q1.LEVEL_AMOUNT/INNER_Q1.LEVEL_PERIODS,2)
1143 END)
1144 ELSE 0
1145 END)
1146 WHEN INNER_Q1.SEQUENCE_NO = 2 AND INNER_Q1.FIRST_BILL_UPTO_DATE IS NOT NULL -- then it is normal stream
1147 THEN
1148 (CASE WHEN INNER_Q1.LEVEL_AMOUNT - ROUND(INNER_Q1.LEVEL_AMOUNT/INNER_Q1.LEVEL_PERIODS,2)*(INNER_Q2.SEQ-1) >0
1149 THEN (CASE WHEN INNER_Q2.SEQ =INNER_Q1.LEVEL_PERIODS AND ROUND(INNER_Q1.LEVEL_AMOUNT/INNER_Q1.LEVEL_PERIODS,2) = 0
1150 THEN INNER_Q1.LEVEL_AMOUNT
1151 WHEN INNER_Q2.SEQ = INNER_Q1.LEVEL_PERIODS AND ROUND(INNER_Q1.LEVEL_AMOUNT/INNER_Q1.LEVEL_PERIODS,2) >0
1152 THEN INNER_Q1.LEVEL_AMOUNT - ROUND(INNER_Q1.LEVEL_AMOUNT/INNER_Q1.LEVEL_PERIODS,2)
1153 *(INNER_Q2.SEQ-1)
1154 ELSE ROUND(INNER_Q1.LEVEL_AMOUNT/INNER_Q1.LEVEL_PERIODS,2)
1155 END)
1156 ELSE 0
1157 END)
1158 WHEN INNER_Q1.SEQUENCE_NO = 2 AND INNER_Q1.FIRST_BILL_UPTO_DATE IS NULL -- last bill stream
1159 THEN INNER_Q1.LEVEL_AMOUNT
1160 WHEN INNER_Q1.SEQUENCE_NO = 3 THEN INNER_Q1.LEVEL_AMOUNT
1161 END) AMOUNT
1162 ,(CASE WHEN INNER_Q1.FULLY_BILLED = 'Y' THEN SYSDATE
1163 ELSE NULL
1164 END) DATE_COMPLETED
1165 ,INNER_Q1.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
1166 ,INNER_Q1.OKS_STRM_LVL_ID RUL_ID
1167 ,FND_GLOBAL.USER_ID CREATED_BY
1168 ,SYSDATE CREATION_DATE
1169 ,FND_GLOBAL.USER_ID LAST_UPDATED_BY
1170 ,SYSDATE LAST_UPDATE_DATE
1171 ,INNER_Q1.CLE_ID CLE_ID
1172 ,INNER_Q1.DNZ_CHR_ID DNZ_CHR_ID
1173 ,INNER_Q1.PARENT_CLE_ID PARENT_CLE_ID
1174 ,(CASE WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.FIRST_BILL_UPTO_DATE IS NOT NULL --first stream
1175 AND INNER_Q1.STRM_END_DATE = INNER_Q1.FIRST_BILL_UPTO_DATE
1176 THEN INNER_Q1.FIRST_BILL_UPTO_DATE
1177 WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.STRM_START_DATE = INNER_Q1.LAST_BILL_FROM_DATE --last stream
1178 THEN INNER_Q1.LIN_END_DT
1179 -- IN OTHER CASES
1180 ELSE DECODE( INNER_Q1.BILLING_INTERVAL_PERIOD
1181 , 'DAY' , MID_SM_STR_DT - 1 + INNER_Q2.SEQ
1182 , 'WK' , MID_SM_STR_DT - 1 + (7 * (INNER_Q2.SEQ))
1183 , 'MTH' , ADD_MONTHS(MID_SM_STR_DT - 1 , (INNER_Q2.SEQ ))
1184 , 'QRT' , ADD_MONTHS(MID_SM_STR_DT - 1 , 3 * (INNER_Q2.SEQ ))
1185 , 'YR' , ADD_MONTHS(MID_SM_STR_DT - 1 , 12 * (INNER_Q2.SEQ )) )
1186
1187 END) DATE_END
1188 ,INNER_Q1.RECUR_BILL_OCCURANCES FREQUENCY
1189 ,INNER_Q1.INVOICING_RULE_ID INVOICING_RULE_ID
1190 FROM
1191 (SELECT OLSTG.LINE_INTERFACE_ID LINE_INTERFACE_ID
1192 ,OKS_STRM_LVL.ID OKS_STRM_LVL_ID
1193 ,OKCLINB_LINE.ID CLE_ID
1194 ,OKCHDRB.ID DNZ_CHR_ID
1195 ,OKCLINB_LINE.ID PARENT_CLE_ID
1196 ,OKCLINB_LINE.INV_RULE_ID INVOICING_RULE_ID
1197 ,1 OBJECT_VERSION_NUMBER
1198 ,nvl(OKCLINB_LINE.PRICE_NEGOTIATED,0) SUBTOTAL
1199 ,OLSTG.LAST_BILL_FROM_DATE LAST_BILL_FROM_DATE
1200 ,OLSTG.BILLING_INTERVAL_PERIOD BILLING_INTERVAL_PERIOD
1201 ,OLSTG.BILLING_INTERVAL_DURATION BILLING_INTERVAL_DURATION
1202 ,OLSTG.RECUR_BILL_OCCURANCES RECUR_BILL_OCCURANCES
1203 ,OLSTG.START_DATE LIN_START_DT
1204 ,OLSTG.END_DATE LIN_END_DT
1205 ,NVL(OLSTG.FIRST_BILLED_AMOUNT,0) FIRST_BILL_AMOUNT
1206 ,NVL(OLSTG.LAST_BILLED_AMOUNT,0) LAST_BILL_AMOUNT
1207 ,OLSTG.FIRST_BILL_UPTO_DATE FIRST_BILL_UPTO_DATE
1208 ,OKS_STRM_LVL.LEVEL_PERIODS LEVEL_PERIODS
1209 ,OKS_STRM_LVL.SEQUENCE_NO SEQUENCE_NO
1210 ,nvl(OKS_STRM_LVL.LEVEL_AMOUNT,0) LEVEL_AMOUNT
1211 ,HDRSTG.FULLY_BILLED FULLY_BILLED
1212 ,OKS_STRM_LVL.START_DATE STRM_START_DATE
1213 ,OKS_STRM_LVL.END_DATE STRM_END_DATE
1214 ,(CASE WHEN OLSTG.FIRST_BILL_UPTO_DATE IS NOT NULL THEN OLSTG.FIRST_BILL_UPTO_DATE +1
1215 ELSE OLSTG.START_DATE
1216 END) MID_SM_STR_DT
1217 ,OLSTG.LAST_BILL_FROM_DATE - 1 MID_SM_END_DT
1218 ,OLSTG.LINE_TYPE LINE_TYPE
1219
1220 FROM OKS_INT_LINE_STG_TEMP OLSTG
1221 ,OKC_K_LINES_B OKCLINB_LINE
1222 ,OKC_K_HEADERS_ALL_B OKCHDRB
1223 ,OKS_INT_HEADER_STG_TEMP HDRSTG
1224 ,OKS_STREAM_LEVELS_B OKS_STRM_LVL
1225
1226 WHERE OLSTG.HEADER_INTERFACE_ID = HDRSTG.HEADER_INTERFACE_ID
1227 AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
1228 AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
1229 AND HDRSTG.INTERFACE_STATUS ='S'
1233 AND OKCLINB_LINE.LINE_NUMBER = OLSTG.LINE_NUMBER
1230 AND OKCLINB_LINE.DNZ_CHR_ID = OKCHDRB.ID
1231 AND OKCLINB_LINE.CHR_ID = OKCHDRB.ID
1232 AND OKS_STRM_LVL.DNZ_CHR_ID = OKCHDRB.ID
1234 AND OKS_STRM_LVL.CLE_ID = OKCLINB_LINE.ID
1235 AND OLSTG.LINE_TYPE='USAGE' ) INNER_Q1
1236 ,(SELECT ROWNUM AS SEQ FROM DUAL CONNECT BY LEVEL <= l_recur_bill_occurance ) INNER_Q2
1237 WHERE INNER_Q2.SEQ <= INNER_Q1.LEVEL_PERIODS)USG_SCH_DT;
1238
1239
1240 /*IF G_STMT_LOG THEN
1241 fnd_log.string(fnd_log.level_statement,
1242 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
1243 'Number of records successfully inserted = ' || l_int_count );
1244 END IF; */
1245
1246 /* IF G_PROCEDURE_LOG THEN
1247 fnd_log.string(fnd_log.level_procedure,
1248 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
1249 'Exit.');
1250 null;
1251 END IF; */
1252
1253 EXCEPTION
1254 WHEN FND_API.G_EXC_ERROR THEN
1255 -- ROLLBACK;
1256 RAISE FND_API.G_EXC_ERROR;
1257 WHEN OTHERS THEN
1258 -- ROLLBACK;
1259 FND_MESSAGE.Set_Name('OKS', 'OKS_IMPORT_UNEXPECTED');
1260 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
1261 FND_MESSAGE.set_token('MESSAGE', 'stmt_num '||l_stmt_num||' ('||SQLCODE||') '||SQLERRM);
1262 FND_MSG_PUB.Add;
1263 RAISE FND_API.G_EXC_ERROR;
1264
1265 END Generate_bil_sch_Usage_lines;
1266
1267 --========================================================================
1268 -- PROCEDURE : Generate_bil_sch_Service_line PRIVATE
1269 -- PARAMETERS :
1270 -- COMMENT : This procedure will generate the Billing Streams
1271 -- and schedules for Service Lines and sublines
1272 --=========================================================================
1273
1274 PROCEDURE Generate_bil_sch_Service_line
1275 IS
1276 l_stmt_num NUMBER := 0;
1277 l_routine CONSTANT VARCHAR2(30) := 'Generate_bil_sch_Service_line';
1278 l_int_count NUMBER := 0;
1279 l_stg_count NUMBER := 0;
1280 l_recur_bill_occurance NUMBER := 0 ;
1281 BEGIN
1282
1283 IF G_PROCEDURE_LOG THEN
1284 fnd_log.string(fnd_log.level_procedure,
1285 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
1286 'Entering ');
1287
1288 END IF;
1289
1290 -- Generates Billing Streams for Service sublines
1291
1292 l_stmt_num := 10;
1293
1294 INSERT ALL
1295 WHEN (FREQUENCY IS NOT NULL ) then
1296 INTO OKS_STREAM_LEVELS_B
1297 (ID
1298 ,CHR_ID
1299 ,CLE_ID
1300 ,DNZ_CHR_ID
1301 ,SEQUENCE_NO
1302 ,UOM_CODE
1303 ,START_DATE
1304 ,END_DATE
1305 ,LEVEL_PERIODS
1306 ,UOM_PER_PERIOD
1307 ,LEVEL_AMOUNT
1308 ,OBJECT_VERSION_NUMBER
1309 ,REQUEST_ID
1310 ,CREATED_BY
1311 ,CREATION_DATE
1312 ,LAST_UPDATED_BY
1313 ,LAST_UPDATE_DATE
1314 ,LAST_UPDATE_LOGIN)
1315
1316 VALUES (ID
1317 ,CHR_ID
1318 ,CLE_ID
1319 ,DNZ_CHR_ID
1320 ,SEQUENCE_NO
1321 ,UOM_CODE
1322 ,START_DATE
1323 ,END_DATE
1324 ,LEVEL_PERIODS
1325 ,UOM_PER_PERIOD
1326 ,LEVEL_AMOUNT
1327 ,OBJECT_VERSION_NUMBER
1328 ,REQUEST_ID
1329 ,CREATED_BY
1330 ,CREATION_DATE
1331 ,LAST_UPDATED_BY
1332 ,LAST_UPDATE_DATE
1333 ,LAST_UPDATE_LOGIN)
1334
1335 SELECT okc_p_util.raw_to_number(sys_guid()) ID
1336 ,INNER_Q2.SEQ SEQUENCE_NO
1337 ,(CASE WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NOT NULL THEN 'DAY'
1338 WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NULL THEN INNER_Q1.BILLING_INTERVAL_PERIOD
1339 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS not NULL THEN INNER_Q1.BILLING_INTERVAL_PERIOD
1340 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS null THEN 'DAY'
1341 WHEN INNER_Q2.SEQ = 3 THEN 'DAY'
1342 else INNER_Q1.BILLING_INTERVAL_PERIOD
1343 END) UOM_CODE
1344
1345 ,(CASE WHEN INNER_Q2.SEQ = 1 THEN INNER_Q1.LIN_STR_DT
1346 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date is not null THEN INNER_Q1.FIRST_BILL_UPTO_DATE + 1
1347 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NULL THEN INNER_Q1.LAST_BILL_FROM_DATE
1348 WHEN INNER_Q2.SEQ = 3 THEN INNER_Q1.LAST_BILL_FROM_DATE
1349 END) START_DATE
1350
1351 ,(CASE WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NOT NULL THEN INNER_Q1.FIRST_BILL_UPTO_DATE
1352 WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NULL THEN INNER_Q1.LAST_BILL_FROM_DATE - 1
1353 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NULL THEN INNER_Q1.LIN_END_DT
1354 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NOT NULL THEN INNER_Q1.LAST_BILL_FROM_DATE - 1
1355 WHEN INNER_Q2.SEQ = 3 THEN INNER_Q1.LIN_END_DT
1356 END) END_DATE
1357
1358 ,(CASE WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NOT NULL THEN 1
1359 WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NULL THEN INNER_Q1.RECUR_BILL_OCCURANCES
1360 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS not NULL THEN INNER_Q1.RECUR_BILL_OCCURANCES
1361 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NULL THEN 1
1362 WHEN INNER_Q2.SEQ = 3 THEN 1
1363 END) LEVEL_PERIODS
1364
1365 ,(CASE WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NOT NULL
1366 THEN INNER_Q1.FIRST_BILL_UPTO_DATE - INNER_Q1.CVL_START_DT + 1 -- difference in the days with the days inclusive
1367 WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NULL THEN INNER_Q1.BILLING_INTERVAL_DURATION
1368 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NOT NULL THEN INNER_Q1.BILLING_INTERVAL_DURATION
1369 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NULL THEN INNER_Q1.CVL_END_DT -(INNER_Q1.LAST_BILL_FROM_DATE - 1)
1370 WHEN INNER_Q2.SEQ = 3 THEN INNER_Q1.CVL_END_DT -(INNER_Q1.LAST_BILL_FROM_DATE - 1 )
1374 ,(CASE WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NOT NULL -- first stream
1371 ELSE INNER_Q1.BILLING_INTERVAL_DURATION
1372 END) UOM_PER_PERIOD
1373
1375 THEN ROUND((INNER_Q1.SUBTOTAL/INNER_Q1.NO_OF_DAYS) * (INNER_Q1.FIRST_BILL_UPTO_DATE - INNER_Q1.LIN_STR_DT +1) *1,2)
1376 WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NULL -- normal stream
1377 THEN ROUND((INNER_Q1.SUBTOTAL/CALC_BILL_PERIOD_1) * INNER_Q1.BILLING_INTERVAL_DURATION * INNER_Q1.RECUR_BILL_OCCURANCES,2)
1378 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NOT NULL --normal stream
1379 THEN ROUND((INNER_Q1.SUBTOTAL -
1380 ROUND((INNER_Q1.SUBTOTAL/INNER_Q1.NO_OF_DAYS) *
1381 (INNER_Q1.FIRST_BILL_UPTO_DATE - INNER_Q1.LIN_STR_DT +1) *1,2))/CALC_BILL_PERIOD_2
1382 *INNER_Q1.BILLING_INTERVAL_DURATION * INNER_Q1.RECUR_BILL_OCCURANCES,2)
1383 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NULL -- last stream
1384 THEN INNER_Q1.SUBTOTAL -
1385 ROUND((INNER_Q1.SUBTOTAL/CALC_BILL_PERIOD_1) *
1386 INNER_Q1.BILLING_INTERVAL_DURATION * INNER_Q1.RECUR_BILL_OCCURANCES,2)
1387 WHEN INNER_Q2.SEQ = 3
1388 THEN
1389 INNER_Q1.SUBTOTAL
1390 - ROUND((INNER_Q1.SUBTOTAL/INNER_Q1.NO_OF_DAYS) * (INNER_Q1.FIRST_BILL_UPTO_DATE - INNER_Q1.LIN_STR_DT +1) *1,2)
1391 - ROUND((INNER_Q1.SUBTOTAL -
1392 ROUND((INNER_Q1.SUBTOTAL/INNER_Q1.NO_OF_DAYS) *
1393 (INNER_Q1.FIRST_BILL_UPTO_DATE - INNER_Q1.LIN_STR_DT +1) *1,2))/CALC_BILL_PERIOD_2
1394 *INNER_Q1.BILLING_INTERVAL_DURATION * INNER_Q1.RECUR_BILL_OCCURANCES,2)
1395
1396 END) LEVEL_AMOUNT
1397 ,INNER_Q1.RECUR_BILL_OCCURANCES FREQUENCY
1398 ,INNER_Q1.*
1399 ,INNER_Q2.*
1400 FROM
1401 (SELECT OKCLINB_SUBLINE.ID CLE_ID
1402 ,null CHR_ID -- can be null for sublines
1403 ,OKCHDRB.ID DNZ_CHR_ID
1404 ,(CASE WHEN OLSTG.FIRST_BILL_UPTO_DATE IS NULL AND (OLSTG.LAST_BILL_FROM_DATE -1 ) = OLSTG.END_DATE THEN 1
1405 WHEN OLSTG.FIRST_BILL_UPTO_DATE IS NOT NULL AND (OLSTG.LAST_BILL_FROM_DATE -1) = OLSTG.END_DATE THEN 2
1406 WHEN OLSTG.FIRST_BILL_UPTO_DATE IS NULL AND (OLSTG.LAST_BILL_FROM_DATE -1 ) < OLSTG.END_DATE THEN 2
1407 WHEN OLSTG.FIRST_BILL_UPTO_DATE IS NOT NULL AND (OLSTG.LAST_BILL_FROM_DATE -1 ) < OLSTG.END_DATE THEN 3
1408 END) NUM_STREAMS
1409 ,1 OBJECT_VERSION_NUMBER
1410 ,null REQUEST_ID
1411 ,FND_GLOBAL.USER_ID CREATED_BY
1412 ,SYSDATE CREATION_DATE
1413 ,FND_GLOBAL.USER_ID LAST_UPDATED_BY
1414 ,SYSDATE LAST_UPDATE_DATE
1415 ,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
1416 ,OLSTG.LAST_BILL_FROM_DATE LAST_BILL_FROM_DATE
1417 ,OLSTG.BILLING_INTERVAL_PERIOD BILLING_INTERVAL_PERIOD
1418 ,OLSTG.BILLING_INTERVAL_DURATION BILLING_INTERVAL_DURATION
1419 ,OLSTG.RECUR_BILL_OCCURANCES RECUR_BILL_OCCURANCES
1420 ,INNER_1.STR_DT CVL_START_DT
1421 ,INNER_1.END_DT CVL_END_DT
1422 ,OLSTG.FIRST_BILL_UPTO_DATE FIRST_BILL_UPTO_DATE
1423 ,nvl(INNER_1.STOTAL,0) SUBTOTAL
1424 ,OLSTG.LINE_TYPE LINE_TYPE
1425 ,OLSTG.START_DATE LIN_STR_DT
1426 ,OLSTG.END_DATE LIN_END_DT
1427 ,(INNER_1.END_DT - INNER_1.STR_DT)+1 NO_OF_DAYS
1428 ,(CASE WHEN OLSTG.BILLING_INTERVAL_PERIOD ='DAY' THEN (OLSTG.END_DATE - OLSTG.START_DATE)+1 --no_of_day
1429 WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'WK' THEN ((OLSTG.END_DATE - OLSTG.START_DATE)+1)/7 -- no of weeks
1430 WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'MTH' THEN MONTHS_BETWEEN(OLSTG.END_DATE + 1,OLSTG.START_DATE) -- no of months
1431 WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'QRT' THEN MONTHS_BETWEEN(OLSTG.END_DATE + 1,OLSTG.START_DATE)/3 -- no of quarter
1432 WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'YR' THEN MONTHS_BETWEEN(OLSTG.END_DATE + 1,OLSTG.START_DATE)/12 -- no of years
1433 END) CALC_BILL_PERIOD_1
1434 ,(CASE WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'DAY' THEN (OLSTG.END_dATE -OLSTG.FIRST_BILL_UPTO_DATE +1) +1 --no of days
1435 WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'WK' THEN ((OLSTG.END_DATE - OLSTG.FIRST_BILL_UPTO_DATE + 1) +1 )/7 -- no of weeks
1436 WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'MTH' THEN MONTHS_BETWEEN(OLSTG.END_DATE + 1,(OLSTG.FIRST_BILL_UPTO_DATE+1))-- no of months
1437 WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'QRT' THEN MONTHS_BETWEEN(OLSTG.END_DATE + 1,(OLSTG.FIRST_BILL_UPTO_DATE+1))/3 -- no of quarter
1438 WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'YR' THEN MONTHS_BETWEEN(OLSTG.END_DATE + 1,(OLSTG.FIRST_BILL_UPTO_DATE+1))/12 -- no of years
1439 END) CALC_BILL_PERIOD_2
1440 FROM OKS_INT_LINE_STG_TEMP OLSTG
1441
1442 ,(SELECT CVLSTG.LINE_INTERFACE_ID LINE_INTERFACE_ID
1443 ,CLI.LINE_NUMBER LINE_NUMBER
1444 ,CLI.START_DATE STR_DT
1445 ,CLI.END_DATE END_DT
1446 ,CLI.SUBTOTAL STOTAL
1447 FROM OKS_COVERED_LEVELS_INTERFACE CLI
1448 ,OKS_INT_COVERED_LEVEL_STG_TEMP CVLSTG
1449 WHERE CLI.COVERED_LEVEL_INTERFACE_ID = CVLSTG.COVERED_LEVEL_INTERFACE_ID )INNER_1
1450
1451 ,OKC_K_LINES_B OKCLINB_LINE
1452 ,OKC_K_LINES_B OKCLINB_SUBLINE
1453 ,OKC_K_HEADERS_ALL_B OKCHDRB
1454 ,OKS_INT_HEADER_STG_TEMP HDRSTG
1455 WHERE INNER_1.LINE_INTERFACE_ID = OLSTG.LINE_INTERFACE_ID
1456 AND OLSTG.HEADER_INTERFACE_ID = HDRSTG.HEADER_INTERFACE_ID
1457 AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
1458 AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
1459 AND HDRSTG.INTERFACE_STATUS ='S'
1460 AND OKCLINB_LINE.DNZ_CHR_ID = OKCHDRB.ID
1461 AND OKCLINB_LINE.CHR_ID = OKCHDRB.ID
1462 AND OLSTG.LINE_NUMBER = OKCLINB_LINE.LINE_NUMBER
1463 AND OKCLINB_SUBLINE.DNZ_CHR_ID = OKCHDRB.ID
1464 AND OKCLINB_SUBLINE.CLE_ID = OKCLINB_LINE.ID
1465 AND OKCLINB_SUBLINE.LINE_NUMBER = INNER_1.LINE_NUMBER
1466 AND OLSTG.LINE_TYPE NOT IN ('SUBSCRIPTION','USAGE')) INNER_Q1
1467
1468 ,(SELECT ROWNUM AS SEQ FROM DUAL CONNECT BY LEVEL <= 3) INNER_Q2
1469
1470 WHERE INNER_Q2.SEQ <= INNER_Q1.NUM_STREAMS;
1471
1472 l_int_count := SQL%ROWCOUNT;
1473 /* IF G_STMT_LOG THEN
1477
1474 fnd_log.string(fnd_log.level_statement,
1475 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
1476 'Number of records successfully inserted = ' || l_int_count );
1478 END IF; */
1479
1480 l_stmt_num :=20;
1481
1482 SELECT MAX(RECUR_BILL_OCCURANCES) INTO l_recur_bill_occurance FROM OKS_INT_LINE_STG_TEMP ;
1483
1484 /* IF G_STMT_LOG THEN
1485
1486 fnd_log.string(fnd_log.level_statement,
1487 G_MODULE_HEAD || l_routine || '.' || l_stmt_num,
1488 'Value of max Recur Bill Occurance = ' || l_recur_bill_occurance );
1489 END IF; */
1490
1491
1492 l_stmt_num :=30;
1493
1494 -- this query inserts records into level elements as schedules for billing streams for sublines
1495
1496 INSERT ALL
1497 WHEN (FREQUENCY IS NOT NULL ) then
1498 INTO OKS_LEVEL_ELEMENTS
1499 (ID
1500 ,SEQUENCE_NUMBER
1501 ,DATE_START
1502 ,AMOUNT
1503 ,DATE_TRANSACTION
1504 ,DATE_TO_INTERFACE
1505 ,DATE_COMPLETED
1506 ,OBJECT_VERSION_NUMBER
1507 ,RUL_ID
1508 ,CREATED_BY
1509 ,CREATION_DATE
1510 ,LAST_UPDATED_BY
1511 ,LAST_UPDATE_DATE
1512 ,CLE_ID
1513 ,DNZ_CHR_ID
1514 ,PARENT_CLE_ID
1515 ,DATE_END)
1516 VALUES (ID
1517 ,SEQUENCE_NUMBER
1518 ,DATE_START
1519 ,AMOUNT
1520 ,DATE_TRANSACTION
1521 ,DATE_TO_INTERFACE
1522 ,DATE_COMPLETED
1523 ,OBJECT_VERSION_NUMBER
1524 ,RUL_ID
1525 ,CREATED_BY
1526 ,CREATION_DATE
1527 ,LAST_UPDATED_BY
1528 ,LAST_UPDATE_DATE
1529 ,CLE_ID
1530 ,DNZ_CHR_ID
1531 ,PARENT_CLE_ID
1532 ,DATE_END)
1533 SELECT COV_SCH_DT.*
1534 ,(CASE WHEN COV_SCH_DT.INVOICING_RULE_ID = -2
1535 THEN
1536 (CASE WHEN COV_SCH_DT.DATE_START >= SYSDATE THEN COV_SCH_DT.DATE_START
1537 ELSE SYSDATE
1538 END)
1539 WHEN COV_SCH_DT.INVOICING_RULE_ID = -3
1540 THEN
1541 (CASE WHEN COV_SCH_DT.DATE_END > = SYSDATE THEN COV_SCH_DT.DATE_END
1542 ELSE SYSDATE
1543 END)
1544 END) DATE_TRANSACTION
1545
1546 ,(CASE WHEN COV_SCH_DT.INVOICING_RULE_ID = -2 THEN COV_SCH_DT.DATE_START
1547 WHEN COV_SCH_DT.INVOICING_RULE_ID = -3 THEN COV_SCH_DT.DATE_END +1
1548 END) DATE_TO_INTERFACE
1549
1550 FROM
1551 (SELECT okc_p_util.raw_to_number(sys_guid()) ID
1552 ,INNER_Q2.SEQ SEQUENCE_NUMBER
1553 ,(CASE WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.STRM_END_DATE = INNER_Q1.FIRST_BILL_UPTO_DATE -- first bill stream
1554 AND INNER_Q1.FIRST_BILL_UPTO_DATE IS NOT NULL
1555 THEN INNER_Q1.CVL_START_DT
1556
1557 WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.STRM_START_DATE = inner_q1.last_bill_from_date -- last bill stream
1558 THEN INNER_Q1.last_bill_from_date
1559 -- IN OTHER CASES
1560 ELSE DECODE (INNER_Q1.BILLING_INTERVAL_PERIOD
1561 ,'DAY' , MID_SM_STR_DT + (INNER_Q2.SEQ -1 )
1562 ,'WK' , MID_SM_STR_DT + (7 * (INNER_Q2.SEQ -1 ))
1563 ,'MTH' , ADD_MONTHS(MID_SM_STR_DT , (INNER_Q2.SEQ -1 ))
1564 ,'QRT' , ADD_MONTHS(MID_SM_STR_DT , 3 * (INNER_Q2.SEQ -1 ))
1565 ,'YR' , ADD_MONTHS(MID_SM_STR_DT , 12 * (INNER_Q2.SEQ -1 )) )
1566 END ) DATE_START
1567
1568 ,(CASE WHEN INNER_Q1.SEQUENCE_NO = 1 AND INNER_Q1.FIRST_BILL_UPTO_DATE IS NOT NULL
1569 THEN INNER_Q1.LEVEL_AMOUNT
1570 WHEN INNER_Q1.SEQUENCE_NO = 1 AND INNER_Q1.FIRST_BILL_UPTO_DATE IS NULL -- normal stream
1571 THEN (CASE WHEN INNER_Q1.LEVEL_AMOUNT - ROUND(INNER_Q1.LEVEL_AMOUNT/INNER_Q1.LEVEL_PERIODS,2) *(INNER_Q2.SEQ-1) >0
1572 THEN (CASE WHEN INNER_Q2.SEQ =INNER_Q1.LEVEL_PERIODS --if it is the last schedule for the stream, value difference due to rounding is to be adjusted
1573 AND ROUND(INNER_Q1.LEVEL_AMOUNT/INNER_Q1.LEVEL_PERIODS,2) = 0
1574 THEN INNER_Q1.LEVEL_AMOUNT
1575 WHEN INNER_Q2.SEQ =INNER_Q1.LEVEL_PERIODS AND ROUND(INNER_Q1.LEVEL_AMOUNT/INNER_Q1.LEVEL_PERIODS,2) >0
1576 THEN INNER_Q1.LEVEL_AMOUNT -
1577 ROUND(INNER_Q1.LEVEL_AMOUNT/INNER_Q1.LEVEL_PERIODS,2)*(INNER_Q2.SEQ-1)
1578 ELSE ROUND(INNER_Q1.LEVEL_AMOUNT/INNER_Q1.LEVEL_PERIODS,2)
1579 END)
1580 ELSE 0
1581 END)
1582 WHEN INNER_Q1.SEQUENCE_NO = 2 AND INNER_Q1.FIRST_BILL_UPTO_DATE IS NOT NULL -- then it is normal stream
1583 THEN (CASE WHEN INNER_Q1.LEVEL_AMOUNT - ROUND(INNER_Q1.LEVEL_AMOUNT/INNER_Q1.LEVEL_PERIODS,2)*(INNER_Q2.SEQ-1) >0
1584 THEN (CASE WHEN INNER_Q2.SEQ =INNER_Q1.LEVEL_PERIODS
1585 AND ROUND(INNER_Q1.LEVEL_AMOUNT/INNER_Q1.LEVEL_PERIODS,2) = 0
1586 THEN INNER_Q1.LEVEL_AMOUNT
1587 WHEN INNER_Q2.SEQ = INNER_Q1.LEVEL_PERIODS AND ROUND(INNER_Q1.LEVEL_AMOUNT/INNER_Q1.LEVEL_PERIODS,2) >0
1588 THEN INNER_Q1.LEVEL_AMOUNT -
1589 ROUND(INNER_Q1.LEVEL_AMOUNT/INNER_Q1.LEVEL_PERIODS,2)*(INNER_Q2.SEQ-1)
1590 ELSE ROUND(INNER_Q1.LEVEL_AMOUNT/INNER_Q1.LEVEL_PERIODS,2)
1591 END)
1592 ELSE 0
1593 END)
1594 WHEN INNER_Q1.SEQUENCE_NO = 2 AND INNER_Q1.FIRST_BILL_UPTO_DATE IS NULL -- last bill stream
1595 THEN INNER_Q1.LEVEL_AMOUNT
1596 WHEN INNER_Q1.SEQUENCE_NO = 3 THEN INNER_Q1.LEVEL_AMOUNT
1597 END) AMOUNT
1598 ,(CASE WHEN INNER_Q1.FULLY_BILLED = 'Y' THEN SYSDATE
1599 ELSE NULL
1600 END) DATE_COMPLETED
1601 ,INNER_Q1.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
1602 ,INNER_Q1.OKS_STRM_LVL_ID RUL_ID
1603 ,FND_GLOBAL.USER_ID CREATED_BY
1604 ,SYSDATE CREATION_DATE
1605 ,FND_GLOBAL.USER_ID LAST_UPDATED_BY
1606 ,SYSDATE LAST_UPDATE_DATE
1607 ,INNER_Q1.CLE_ID CLE_ID
1608 ,INNER_Q1.DNZ_CHR_ID DNZ_CHR_ID
1609 ,INNER_Q1.PARENT_CLE_ID PARENT_CLE_ID
1610 ,(CASE WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.STRM_END_DATE = INNER_Q1.FIRST_BILL_UPTO_DATE -- first bill stream
1614 THEN INNER_Q1.CVL_END_DT
1611 AND INNER_Q1.FIRST_BILL_UPTO_DATE IS NOT NULL
1612 THEN INNER_Q1.FIRST_BILL_UPTO_DATE
1613 WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.STRM_START_DATE = inner_q1.last_bill_from_date -- last bill stream
1615 -- IN OTHER CASES
1616 ELSE DECODE( INNER_Q1.BILLING_INTERVAL_PERIOD
1617 , 'DAY' , MID_SM_STR_DT + INNER_Q2.SEQ -1
1618 , 'WK' , MID_SM_STR_DT + (7 * (INNER_Q2.SEQ)) -1
1619 , 'MTH' , ADD_MONTHS(MID_SM_STR_DT , (INNER_Q2.SEQ )) -1
1620 , 'QRT' , ADD_MONTHS(MID_SM_STR_DT , 3 * (INNER_Q2.SEQ )) -1
1621 , 'YR' , ADD_MONTHS(MID_SM_STR_DT , 12 * (INNER_Q2.SEQ )) -1 )
1622
1623 END ) DATE_END
1624 ,INNER_Q1.RECUR_BILL_OCCURANCES FREQUENCY
1625 ,INNER_Q1.INVOICING_RULE_ID INVOICING_RULE_ID
1626 FROM
1627 (SELECT OKCLINB_SUBLINE.ID CLE_ID
1628 ,OKS_STRM_LVL.ID OKS_STRM_LVL_ID
1629 ,null CHR_ID -- can be null for sublines
1630 ,OKCHDRB.ID DNZ_CHR_ID
1631 ,OKCLINB_LINE.ID PARENT_CLE_ID
1632 ,OKCLINB_LINE.INV_RULE_ID INVOICING_RULE_ID
1633 ,1 OBJECT_VERSION_NUMBER
1634 ,NVL(INNER_1.STOTAL,0) SUBTOTAL
1635 ,OLSTG.LAST_BILL_FROM_DATE LAST_BILL_FROM_DATE
1636 ,OLSTG.BILLING_INTERVAL_PERIOD BILLING_INTERVAL_PERIOD
1637 ,OLSTG.BILLING_INTERVAL_DURATION BILLING_INTERVAL_DURATION
1638 ,OLSTG.RECUR_BILL_OCCURANCES RECUR_BILL_OCCURANCES
1639 ,INNER_1.STR_DT CVL_START_DT
1640 ,INNER_1.END_DT CVL_END_DT
1641 ,OLSTG.FIRST_BILL_UPTO_DATE FIRST_BILL_UPTO_DATE
1642 ,OKS_STRM_LVL.LEVEL_PERIODS LEVEL_PERIODS
1643 ,OKS_STRM_LVL.LEVEL_AMOUNT LEVEL_AMOUNT
1644 ,HDRSTG.FULLY_BILLED FULLY_BILLED
1645 ,OKS_STRM_LVL.START_DATE STRM_START_DATE
1646 ,OKS_STRM_LVL.END_DATE STRM_END_DATE
1647 ,OLSTG.LINE_TYPE LINE_TYPE
1648 ,OKS_STRM_LVL.SEQUENCE_NO SEQUENCE_NO
1649 ,(CASE WHEN OLSTG.FIRST_BILL_UPTO_DATE IS NOT NULL THEN OLSTG.FIRST_BILL_UPTO_DATE +1
1650 ELSE INNER_1.STR_DT
1651 END) MID_SM_STR_DT
1652 ,OLSTG.LAST_BILL_FROM_DATE -1 MID_SM_END_DT
1653
1654 FROM OKS_INT_LINE_STG_TEMP OLSTG
1655 ,(SELECT CVLSTG.LINE_INTERFACE_ID LINE_INTERFACE_ID
1656 ,CLI.LINE_NUMBER LINE_NUMBER
1657 ,CLI.START_DATE STR_DT
1658 ,CLI.END_DATE END_DT
1659 ,CLI.SUBTOTAL STOTAL
1660 FROM OKS_COVERED_LEVELS_INTERFACE CLI
1661 ,OKS_INT_COVERED_LEVEL_STG_TEMP CVLSTG
1662 WHERE CLI.COVERED_LEVEL_INTERFACE_ID = CVLSTG.COVERED_LEVEL_INTERFACE_ID ) INNER_1
1663 ,OKC_K_LINES_B OKCLINB_LINE
1664 ,OKC_K_LINES_B OKCLINB_SUBLINE
1665 ,OKC_K_HEADERS_ALL_B OKCHDRB
1666 ,OKS_INT_HEADER_STG_TEMP HDRSTG
1667 ,OKS_STREAM_LEVELS_B OKS_STRM_LVL
1668 WHERE INNER_1.LINE_INTERFACE_ID = OLSTG.LINE_INTERFACE_ID
1669 AND OLSTG.HEADER_INTERFACE_ID = HDRSTG.HEADER_INTERFACE_ID
1670 AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
1671 AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
1672 AND HDRSTG.INTERFACE_STATUS ='S'
1673 AND OKCLINB_LINE.DNZ_CHR_ID = OKCHDRB.ID
1674 AND OKCLINB_LINE.CHR_ID = OKCHDRB.ID
1675 AND OLSTG.LINE_NUMBER = OKCLINB_LINE.LINE_NUMBER
1676 AND OKCLINB_SUBLINE.DNZ_CHR_ID = OKCHDRB.ID
1677 AND OKCLINB_SUBLINE.CLE_ID = OKCLINB_LINE.ID
1678 AND OKCLINB_SUBLINE.LINE_NUMBER = INNER_1.LINE_NUMBER
1679 AND OKS_STRM_LVL.DNZ_CHR_ID = OKCHDRB.ID
1680 AND OKS_STRM_LVL.CLE_ID = OKCLINB_SUBLINE.ID
1681 AND OKS_STRM_LVL.CHR_ID IS NULL
1682 AND OLSTG.LINE_TYPE NOT IN ('SUBSCRIPTION','USAGE')) INNER_Q1
1683
1684 ,(SELECT ROWNUM AS SEQ FROM DUAL CONNECT BY LEVEL <= l_recur_bill_occurance ) INNER_Q2
1685
1686 WHERE INNER_Q2.SEQ <= INNER_Q1.LEVEL_PERIODS) COV_SCH_DT;
1687
1688 -- Billing Streams and Schedules for Service Lines
1689
1690 l_stmt_num := 40;
1691
1692 INSERT ALL
1693 WHEN (FREQUENCY IS NOT NULL) THEN
1694 INTO OKS_STREAM_LEVELS_B
1695 (ID
1696 ,CHR_ID
1697 ,CLE_ID
1698 ,DNZ_CHR_ID
1699 ,SEQUENCE_NO
1700 ,UOM_CODE
1701 ,START_DATE
1702 ,END_DATE
1703 ,LEVEL_PERIODS
1704 ,UOM_PER_PERIOD
1705 ,LEVEL_AMOUNT
1706 ,OBJECT_VERSION_NUMBER
1707 ,REQUEST_ID
1708 ,CREATED_BY
1709 ,CREATION_DATE
1710 ,LAST_UPDATED_BY
1711 ,LAST_UPDATE_DATE
1712 ,LAST_UPDATE_LOGIN)
1713
1714 VALUES (ID
1715 ,CHR_ID
1716 ,CLE_ID
1717 ,DNZ_CHR_ID
1718 ,SEQUENCE_NO
1719 ,UOM_CODE
1720 ,START_DATE
1721 ,END_DATE
1722 ,LEVEL_PERIODS
1723 ,UOM_PER_PERIOD
1724 ,LEVEL_AMOUNT
1725 ,OBJECT_VERSION_NUMBER
1726 ,REQUEST_ID
1727 ,CREATED_BY
1728 ,CREATION_DATE
1729 ,LAST_UPDATED_BY
1730 ,LAST_UPDATE_DATE
1731 ,LAST_UPDATE_LOGIN)
1732
1733 SELECT okc_p_util.raw_to_number(sys_guid()) ID
1734
1735 ,INNER_Q2.SEQ SEQUENCE_NO
1736
1737 ,(CASE WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NOT NULL THEN 'DAY'
1738 WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NULL THEN INNER_Q1.BILLING_INTERVAL_PERIOD
1739 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS not NULL THEN INNER_Q1.BILLING_INTERVAL_PERIOD
1740 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS null THEN 'DAY'
1741 WHEN INNER_Q2.SEQ = 3 THEN 'DAY'
1742 else INNER_Q1.BILLING_INTERVAL_PERIOD
1743 END) UOM_CODE
1744
1745 ,(CASE WHEN INNER_Q2.SEQ = 1 THEN INNER_Q1.LIN_STR_DT
1746 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date is not null THEN INNER_Q1.FIRST_BILL_UPTO_DATE + 1
1747 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NULL THEN INNER_Q1.LAST_BILL_FROM_DATE
1748 WHEN INNER_Q2.SEQ = 3 THEN INNER_Q1.LAST_BILL_FROM_DATE
1752 WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NULL THEN INNER_Q1.LAST_BILL_FROM_DATE - 1
1749 END) START_DATE
1750
1751 ,(CASE WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NOT NULL THEN INNER_Q1.FIRST_BILL_UPTO_DATE
1753 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NULL THEN INNER_Q1.LIN_END_DT
1754 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NOT NULL THEN INNER_Q1.LAST_BILL_FROM_DATE - 1
1755 WHEN INNER_Q2.SEQ = 3 THEN INNER_Q1.LIN_END_DT
1756 END) END_DATE
1757
1758 ,(CASE WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NOT NULL THEN 1
1759 WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NULL THEN INNER_Q1.RECUR_BILL_OCCURANCES
1760 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NOT NULL THEN INNER_Q1.RECUR_BILL_OCCURANCES
1761 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NULL THEN 1
1762 WHEN INNER_Q2.SEQ = 3 THEN 1
1763 END) LEVEL_PERIODS
1764
1765 ,(CASE WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NOT NULL
1766 THEN INNER_Q1.FIRST_BILL_UPTO_DATE - INNER_Q1.LIN_STR_DT +1 -- including the days between the difference
1767
1768 WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NULL THEN INNER_Q1.BILLING_INTERVAL_DURATION
1769 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NOT NULL THEN INNER_Q1.BILLING_INTERVAL_DURATION
1770 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NULL THEN INNER_Q1.LIN_END_DT -(INNER_Q1.LAST_BILL_FROM_DATE - 1 )
1771 WHEN INNER_Q2.SEQ = 3 THEN INNER_Q1.LIN_END_DT -(INNER_Q1.LAST_BILL_FROM_DATE - 1 )
1772 ELSE INNER_Q1.BILLING_INTERVAL_DURATION
1773 END) UOM_PER_PERIOD
1774 ,(CASE WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NOT NULL -- first stream
1775 THEN ROUND((INNER_Q1.SUBTOTAL/INNER_Q1.NO_OF_DAYS) * (INNER_Q1.FIRST_BILL_UPTO_DATE - INNER_Q1.LIN_STR_DT +1) *1,2)
1776
1777 WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.first_bill_upto_date IS NULL -- normal stream
1778 THEN ROUND((INNER_Q1.SUBTOTAL/CALC_BILL_PERIOD_1) * INNER_Q1.BILLING_INTERVAL_DURATION * INNER_Q1.RECUR_BILL_OCCURANCES,2)
1779
1780 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NOT NULL --normal stream
1781 THEN ROUND((INNER_Q1.SUBTOTAL -
1782 ROUND((INNER_Q1.SUBTOTAL/INNER_Q1.NO_OF_DAYS) *
1783 (INNER_Q1.FIRST_BILL_UPTO_DATE - INNER_Q1.LIN_STR_DT +1) *1,2))/CALC_BILL_PERIOD_2
1784 * INNER_Q1.BILLING_INTERVAL_DURATION * INNER_Q1.RECUR_BILL_OCCURANCES,2)
1785 WHEN INNER_Q2.SEQ = 2 AND INNER_Q1.first_bill_upto_date IS NULL -- last stream
1786 THEN INNER_Q1.SUBTOTAL -
1787 ROUND((INNER_Q1.SUBTOTAL/CALC_BILL_PERIOD_1) *
1788 INNER_Q1.BILLING_INTERVAL_DURATION * INNER_Q1.RECUR_BILL_OCCURANCES,2)
1789 WHEN INNER_Q2.SEQ = 3
1790 THEN
1791 INNER_Q1.SUBTOTAL
1792 - ROUND((INNER_Q1.SUBTOTAL/INNER_Q1.NO_OF_DAYS) * (INNER_Q1.FIRST_BILL_UPTO_DATE - INNER_Q1.LIN_STR_DT +1) *1,2)
1793 - ROUND((INNER_Q1.SUBTOTAL -
1794 ROUND((INNER_Q1.SUBTOTAL/INNER_Q1.NO_OF_DAYS) *
1795 (INNER_Q1.FIRST_BILL_UPTO_DATE - INNER_Q1.LIN_STR_DT +1) *1,2))/CALC_BILL_PERIOD_2
1796 * INNER_Q1.BILLING_INTERVAL_DURATION * INNER_Q1.RECUR_BILL_OCCURANCES,2)
1797 END) LEVEL_AMOUNT
1798 ,INNER_Q1.RECUR_BILL_OCCURANCES FREQUENCY
1799 ,INNER_Q1.*
1800 ,INNER_Q2.*
1801 FROM
1802 (SELECT OKCLINB_LINE.ID LINE_ID
1803 ,null CHR_ID
1804 ,OKCLINB_LINE.ID CLE_ID
1805 ,OKCHDRB.ID DNZ_CHR_ID
1806 ,OLSTG.FIRST_BILL_UPTO_DATE FIRST_BILL_UPTO_DATE
1807 ,(CASE WHEN OLSTG.FIRST_BILL_UPTO_DATE IS NULL AND (OLSTG.LAST_BILL_FROM_DATE -1 ) = OLSTG.END_DATE THEN 1
1808 WHEN OLSTG.FIRST_BILL_UPTO_DATE IS NOT NULL AND (OLSTG.LAST_BILL_FROM_DATE -1) = OLSTG.END_DATE THEN 2
1809 WHEN OLSTG.FIRST_BILL_UPTO_DATE IS NULL AND (OLSTG.LAST_BILL_FROM_DATE -1 ) < OLSTG.END_DATE THEN 2
1810 WHEN OLSTG.FIRST_BILL_UPTO_DATE IS NOT NULL AND (OLSTG.LAST_BILL_FROM_DATE -1 ) < OLSTG.END_DATE THEN 3
1811 END) NUM_STREAMS
1812 ,OLSTG.BILLING_INTERVAL_PERIOD BILLING_INTERVAL_PERIOD
1813 ,(CASE WHEN OLSTG.BILLING_INTERVAL_PERIOD ='DAY' THEN (OLSTG.END_DATE - OLSTG.START_DATE)+1 --no_of_day
1814 WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'WK' THEN ((OLSTG.END_DATE - OLSTG.START_DATE)+1)/7 -- no of weeks
1815 WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'MTH' THEN MONTHS_BETWEEN(OLSTG.END_DATE + 1,OLSTG.START_DATE) -- no of months
1816 WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'QRT' THEN MONTHS_BETWEEN(OLSTG.END_DATE + 1,OLSTG.START_DATE)/3 -- no of quarter
1817 WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'YR' THEN MONTHS_BETWEEN(OLSTG.END_DATE + 1,OLSTG.START_DATE)/12 -- no of years
1818 END) CALC_BILL_PERIOD_1
1819 ,(CASE WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'DAY' THEN (OLSTG.END_dATE -OLSTG.FIRST_BILL_UPTO_DATE +1) +1 --no of days
1820 WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'WK' THEN ((OLSTG.END_DATE - OLSTG.FIRST_BILL_UPTO_DATE + 1) +1 )/7 -- no of weeks
1821 WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'MTH' THEN MONTHS_BETWEEN(OLSTG.END_DATE + 1,(OLSTG.FIRST_BILL_UPTO_DATE+1))-- no of months
1822 WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'QRT' THEN MONTHS_BETWEEN(OLSTG.END_DATE + 1,(OLSTG.FIRST_BILL_UPTO_DATE+1))/3 -- no of quarter
1823 WHEN OLSTG.BILLING_INTERVAL_PERIOD = 'YR' THEN MONTHS_BETWEEN(OLSTG.END_DATE + 1,(OLSTG.FIRST_BILL_UPTO_DATE+1))/12 -- no of years
1824 END) CALC_BILL_PERIOD_2
1825 ,OLSTG.LINE_TYPE LINE_TYPE
1826 ,OLSTG.RECUR_BILL_OCCURANCES RECUR_BILL_OCCURANCES
1827 ,OLSTG.BILLING_INTERVAL_DURATION BILLING_INTERVAL_DURATION
1828 ,nvl(OKCLINB_LINE.PRICE_NEGOTIATED,0) SUBTOTAL
1829 ,OLSTG.START_DATE LIN_STR_DT
1830 ,OLSTG.END_DATE LIN_END_DT
1831 ,(OLSTG.END_DATE - OLSTG.START_DATE)+1 NO_OF_DAYS
1832 ,OLSTG.LAST_BILL_FROM_DATE LAST_BILL_FROM_DATE
1833 ,1 OBJECT_VERSION_NUMBER
1834 ,null REQUEST_ID -- need to confirm
1835 ,FND_GLOBAL.USER_ID CREATED_BY
1836 ,SYSDATE CREATION_DATE
1840 FROM OKS_INT_LINE_STG_TEMP OLSTG
1837 ,FND_GLOBAL.USER_ID LAST_UPDATED_BY
1838 ,SYSDATE LAST_UPDATE_DATE
1839 ,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
1841 ,OKS_INT_HEADER_STG_TEMP HDRSTG
1842 ,OKC_K_HEADERS_ALL_B OKCHDRB
1843 ,OKC_K_LINES_B OKCLINB_LINE
1844 WHERE OLSTG.HEADER_INTERFACE_ID = HDRSTG.HEADER_INTERFACE_ID
1845 AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
1846 AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
1847 AND HDRSTG.INTERFACE_STATUS ='S'
1848 AND OKCLINB_LINE.DNZ_CHR_ID = OKCHDRB.ID
1849 AND OKCLINB_LINE.CHR_ID = OKCHDRB.ID
1850 AND OKCLINB_LINE.LINE_NUMBER = OLSTG.LINE_NUMBER
1851 AND OLSTG.LINE_TYPE NOT IN ('SUBSCRIPTION', 'USAGE'))INNER_Q1
1852
1853 ,(SELECT ROWNUM AS SEQ FROM DUAL CONNECT BY LEVEL <= 3) INNER_Q2
1854
1855 WHERE INNER_Q2.SEQ <= INNER_Q1.NUM_STREAMS;
1856
1857 l_int_count := SQL%ROWCOUNT;
1858
1859 /*IF G_STMT_LOG THEN
1860
1861 fnd_log.string(fnd_log.level_statement,
1862 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
1863 'Number of records successfully inserted = ' || l_int_count );
1864 END IF; */
1865
1866 l_stmt_num := 50;
1867
1868 --This query creates billing schedules from streams for lines
1869 INSERT ALL
1870 WHEN (FREQUENCY IS NOT NULL ) then
1871 INTO OKS_LEVEL_ELEMENTS
1872 (ID
1873 ,SEQUENCE_NUMBER
1874 ,DATE_START
1875 ,AMOUNT
1876 ,DATE_TRANSACTION
1877 ,DATE_TO_INTERFACE
1878 ,DATE_COMPLETED
1879 ,OBJECT_VERSION_NUMBER
1880 ,RUL_ID
1881 ,CREATED_BY
1882 ,CREATION_DATE
1883 ,LAST_UPDATED_BY
1884 ,LAST_UPDATE_DATE
1885 ,CLE_ID
1886 ,DNZ_CHR_ID
1887 ,PARENT_CLE_ID
1888 ,DATE_END)
1889 VALUES (ID
1890 ,SEQUENCE_NUMBER
1891 ,DATE_START
1892 ,AMOUNT
1893 ,DATE_TRANSACTION
1894 ,DATE_TO_INTERFACE
1895 ,DATE_COMPLETED
1896 ,OBJECT_VERSION_NUMBER
1897 ,RUL_ID
1898 ,CREATED_BY
1899 ,CREATION_DATE
1900 ,LAST_UPDATED_BY
1901 ,LAST_UPDATE_DATE
1902 ,CLE_ID
1903 ,DNZ_CHR_ID
1904 ,PARENT_CLE_ID
1905 ,DATE_END)
1906
1907 SELECT okc_p_util.raw_to_number(sys_guid()) ID
1908 ,SCH_LIN_INSERT . *
1909 FROM
1910 (SELECT DISTINCT COV_LVL_ELEM.PARENT_CLE_ID AS PAR_CLE_ID
1911 ,LIN_SCH_DT.*
1912
1913 ,SUM(COV_LVL_ELEM.AMOUNT) OVER (PARTITION BY COV_LVL_ELEM.PARENT_CLE_ID, LIN_SCH_DT.DATE_START ) AMOUNT
1914
1915 ,(CASE WHEN LIN_SCH_DT.INVOICING_RULE_ID = -2
1916 THEN (CASE WHEN LIN_SCH_DT.DATE_START >= SYSDATE THEN LIN_SCH_DT.DATE_START
1917 ELSE SYSDATE
1918 END)
1919 WHEN LIN_SCH_DT.INVOICING_RULE_ID = -3
1920 THEN (CASE WHEN LIN_SCH_DT.DATE_END > = SYSDATE THEN LIN_SCH_DT.DATE_END
1921 ELSE SYSDATE
1922 END)
1923 END) DATE_TRANSACTION
1924
1925 ,(CASE WHEN LIN_SCH_DT.INVOICING_RULE_ID = -2 THEN LIN_SCH_DT.DATE_START
1926 WHEN LIN_SCH_DT.INVOICING_RULE_ID = -3 THEN LIN_SCH_DT.DATE_END +1
1927 END) DATE_TO_INTERFACE
1928
1929 FROM OKS_LEVEL_ELEMENTS COV_LVL_ELEM
1930 ,(SELECT INNER_Q2.SEQ SEQUENCE_NUMBER
1931 ,(CASE WHEN INNER_Q2.SEQ=1 THEN INNER_Q1.STRM_START_DATE
1932 -- IN OTHER CASES
1933 ELSE DECODE (INNER_Q1.BILLING_INTERVAL_PERIOD
1934 ,'DAY' , MID_SM_STR_DT + (INNER_Q2.SEQ -1 )
1935 ,'WK' , MID_SM_STR_DT + (7 * (INNER_Q2.SEQ -1 ))
1936 ,'MTH' , ADD_MONTHS(MID_SM_STR_DT , (INNER_Q2.SEQ -1 ))
1937 ,'QRT' , ADD_MONTHS(MID_SM_STR_DT , 3 * (INNER_Q2.SEQ -1 ))
1938 ,'YR' , ADD_MONTHS(MID_SM_STR_DT , 12 * (INNER_Q2.SEQ -1 )) )
1939 END ) DATE_START
1940 ,(CASE WHEN INNER_Q1.FULLY_BILLED = 'Y' THEN SYSDATE
1941 ELSE NULL
1942 END) DATE_COMPLETED
1943
1944 ,INNER_Q1.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
1945 ,INNER_Q1.OKS_STRM_LVL_ID RUL_ID
1946 ,FND_GLOBAL.USER_ID CREATED_BY
1947 ,SYSDATE CREATION_DATE
1948 ,FND_GLOBAL.USER_ID LAST_UPDATED_BY
1949 ,SYSDATE LAST_UPDATE_DATE
1950 ,INNER_Q1.CLE_ID CLE_ID
1951 ,INNER_Q1.DNZ_CHR_ID DNZ_CHR_ID
1952 ,INNER_Q1.PARENT_CLE_ID PARENT_CLE_ID
1953 ,(CASE WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.FIRST_BILL_UPTO_DATE IS NOT NULL --first stream
1954 AND INNER_Q1.STRM_END_DATE = INNER_Q1.FIRST_BILL_UPTO_DATE
1955 THEN INNER_Q1.FIRST_BILL_UPTO_DATE
1956 WHEN INNER_Q2.SEQ = 1 AND INNER_Q1.STRM_START_DATE = INNER_Q1.LAST_BILL_FROM_DATE --last stream
1957 THEN INNER_Q1.LIN_END_DT
1958 -- IN OTHER CASES
1959 ELSE DECODE( INNER_Q1.BILLING_INTERVAL_PERIOD
1960 , 'DAY' , MID_SM_STR_DT - 1 + INNER_Q2.SEQ
1961 , 'WK' , MID_SM_STR_DT - 1 + (7 * (INNER_Q2.SEQ))
1962 , 'MTH' , ADD_MONTHS(MID_SM_STR_DT - 1 , (INNER_Q2.SEQ ))
1963 , 'QRT' , ADD_MONTHS(MID_SM_STR_DT - 1 , 3 * (INNER_Q2.SEQ ))
1964 , 'YR' , ADD_MONTHS(MID_SM_STR_DT - 1 , 12 * (INNER_Q2.SEQ )) )
1965
1966 END) DATE_END
1967 ,INNER_Q1.RECUR_BILL_OCCURANCES FREQUENCY
1968 ,INNER_Q1.INVOICING_RULE_ID INVOICING_RULE_ID
1969 FROM
1970 (SELECT OLSTG.LINE_INTERFACE_ID LINE_INTERFACE_ID
1971 ,OKS_STRM_LVL.ID OKS_STRM_LVL_ID
1972 ,OKCLINB_LINE.ID CLE_ID
1973 ,OKCHDRB.ID DNZ_CHR_ID
1974 ,OKCLINB_LINE.ID PARENT_CLE_ID
1975 ,OKCLINB_LINE.INV_RULE_ID INVOICING_RULE_ID
1976 ,1 OBJECT_VERSION_NUMBER
1977 ,nvl(OKCLINB_LINE.PRICE_NEGOTIATED,0) SUBTOTAL
1978 ,OLSTG.LAST_BILL_FROM_DATE LAST_BILL_FROM_DATE
1979 ,OLSTG.BILLING_INTERVAL_PERIOD BILLING_INTERVAL_PERIOD
1980 ,OLSTG.BILLING_INTERVAL_DURATION BILLING_INTERVAL_DURATION
1981 ,OLSTG.RECUR_BILL_OCCURANCES RECUR_BILL_OCCURANCES
1985 ,OKS_STRM_LVL.LEVEL_PERIODS LEVEL_PERIODS
1982 ,OLSTG.START_DATE LIN_START_DT
1983 ,OLSTG.END_DATE LIN_END_DT
1984 ,OLSTG.FIRST_BILL_UPTO_DATE FIRST_BILL_UPTO_DATE
1986 ,OKS_STRM_LVL.SEQUENCE_NO SEQUENCE_NO
1987 ,nvl(OKS_STRM_LVL.LEVEL_AMOUNT,0) LEVEL_AMOUNT
1988 ,HDRSTG.FULLY_BILLED FULLY_BILLED
1989 ,OKS_STRM_LVL.START_DATE STRM_START_DATE
1990 ,OKS_STRM_LVL.END_DATE STRM_END_DATE
1991 ,(CASE WHEN OLSTG.FIRST_BILL_UPTO_DATE IS NOT NULL THEN OLSTG.FIRST_BILL_UPTO_DATE +1
1992 ELSE OLSTG.START_DATE
1993 END) MID_SM_STR_DT
1994 ,OLSTG.LAST_BILL_FROM_DATE - 1 MID_SM_END_DT
1995 ,OLSTG.LINE_TYPE LINE_TYPE
1996 FROM OKS_INT_LINE_STG_TEMP OLSTG
1997 ,OKC_K_LINES_B OKCLINB_LINE
1998 ,OKC_K_HEADERS_ALL_B OKCHDRB
1999 ,OKS_INT_HEADER_STG_TEMP HDRSTG
2000 ,OKS_STREAM_LEVELS_B OKS_STRM_LVL
2001 WHERE OLSTG.HEADER_INTERFACE_ID = HDRSTG.HEADER_INTERFACE_ID
2002 AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
2003 AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
2004 AND HDRSTG.INTERFACE_STATUS ='S'
2005 AND OKCLINB_LINE.DNZ_CHR_ID = OKCHDRB.ID
2006 AND OKCLINB_LINE.CHR_ID = OKCHDRB.ID
2007 AND OKS_STRM_LVL.DNZ_CHR_ID = OKCHDRB.ID
2008 AND OKCLINB_LINE.LINE_NUMBER = OLSTG.LINE_NUMBER
2009 AND OKS_STRM_LVL.CLE_ID = OKCLINB_LINE.ID
2010 AND OLSTG.LINE_TYPE NOT IN ('SUBSCRIPTION', 'USAGE')) INNER_Q1
2011 ,(SELECT ROWNUM AS SEQ FROM DUAL CONNECT BY LEVEL <= l_recur_bill_occurance ) INNER_Q2
2012 WHERE INNER_Q2.SEQ <= INNER_Q1.LEVEL_PERIODS) LIN_SCH_DT
2013 WHERE COV_LVL_ELEM.PARENT_CLE_ID = LIN_SCH_DT.CLE_ID
2014 AND COV_LVL_ELEM.DATE_START(+) >= LIN_SCH_DT.DATE_START
2015 AND COV_LVL_ELEM.DATE_END(+) <= LIN_SCH_DT.DATE_END ) SCH_LIN_INSERT;
2016
2017 /* IF G_STMT_LOG THEN
2018 fnd_log.string(fnd_log.level_statement,
2019 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
2020 'Number of records successfully inserted = ' || l_int_count );
2021 END IF; */
2022
2023 /* IF G_PROCEDURE_LOG THEN
2024 fnd_log.string(fnd_log.level_procedure,
2025 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
2026 'Exit.');
2027 null;
2028 END IF; */
2029
2030 EXCEPTION
2031 WHEN FND_API.G_EXC_ERROR THEN
2032 -- ROLLBACK;
2033 RAISE FND_API.G_EXC_ERROR;
2034 WHEN OTHERS THEN
2035 -- ROLLBACK;
2036 FND_MESSAGE.Set_Name('OKS', 'OKS_IMPORT_UNEXPECTED');
2037 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
2038 FND_MESSAGE.set_token('MESSAGE', 'stmt_num '||l_stmt_num||' ('||SQLCODE||') '||SQLERRM);
2039 FND_MSG_PUB.Add;
2040 RAISE FND_API.G_EXC_ERROR;
2041
2042 END Generate_bil_sch_Service_line;
2043
2044 --========================================================================
2045 -- PROCEDURE : Generate_billing_schedules PRIVATE
2046 -- PARAMETERS:
2047 -- COMMENT : This procedure will generate Billing Streams and schedules
2048 -- for sublines and lines
2049 --=========================================================================
2050
2051 PROCEDURE Generate_billing_schedules
2052 IS
2053 l_stmt_num NUMBER := 0;
2054 l_routine CONSTANT VARCHAR2(30) := 'Generate_billing_schedules';
2055 l_int_count NUMBER := 0;
2056 l_stg_count NUMBER := 0;
2057 l_recur_bill_occurance NUMBER := 0 ;
2058 BEGIN
2059 IF G_PROCEDURE_LOG THEN
2060 fnd_log.string(fnd_log.level_procedure,
2061 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
2062 'Entering ');
2063
2064 END IF;
2065
2066
2067 l_stmt_num := 10;
2068
2069 Generate_bil_sch_Service_line;
2070 l_stmt_num :=20 ;
2071 Generate_bil_sch_Subs_lines;
2072
2073 l_stmt_num := 30;
2074 Generate_bil_sch_Usage_lines;
2075
2076 IF G_STMT_LOG THEN
2077 fnd_log.string(fnd_log.level_statement,
2078 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
2079 'Number of records successfully inserted = ' || l_int_count );
2080 END IF;
2081
2082 IF G_PROCEDURE_LOG THEN
2083 fnd_log.string(fnd_log.level_procedure,
2084 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
2085 'Exit.');
2086 null;
2087 END IF;
2088
2089 EXCEPTION
2090 WHEN FND_API.G_EXC_ERROR THEN
2091 -- ROLLBACK;
2092 RAISE FND_API.G_EXC_ERROR;
2093 WHEN OTHERS THEN
2094 -- ROLLBACK;
2095 FND_MESSAGE.Set_Name('OKS', 'OKS_IMPORT_UNEXPECTED');
2096 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
2097 FND_MESSAGE.set_token('MESSAGE', 'stmt_num '||l_stmt_num||' ('||SQLCODE||') '||SQLERRM);
2098 FND_MSG_PUB.Add;
2099 RAISE FND_API.G_EXC_ERROR;
2100
2101 END Generate_billing_schedules;
2102
2103 --========================================================================
2104 -- PROCEDURE : Generate_PM_schedules PRIVATE
2105 -- PARAMETERS :
2106 -- COMMENT : This procedure will invoke the API's to generate
2107 -- the Preventive maintenance schedules
2108 --=========================================================================
2109
2110 PROCEDURE Generate_PM_schedules
2111 IS
2112
2113 CURSOR get_line_cnt_details
2114 IS
2115
2116 SELECT OKSLINB_LINE.COVERAGE_ID as reference_template_id
2117 ,OKCLINB_LINE.id as cle_id
2118 ,OKCLINB_LINE.start_date as start_date
2119 ,OKCLINB_LINE.end_date as end_date
2120
2121 FROM OKS_INT_LINE_STG_TEMP OLSTG
2122 ,OKS_INT_HEADER_STG_TEMP HDRSTG
2123 ,OKC_K_LINES_B OKCLINB_LINE
2124 ,OKS_K_LINES_B OKSLINB_LINE
2125 ,OKC_K_HEADERS_ALL_B OKCHDRB
2126 WHERE OLSTG.HEADER_INTERFACE_ID = HDRSTG.HEADER_INTERFACE_ID
2130 AND OKCLINB_LINE.DNZ_CHR_ID = OKCHDRB.ID
2127 AND HDRSTG.INTERFACE_STATUS ='S'
2128 AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
2129 AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
2131 AND OKCLINB_LINE.CHR_ID = OKCHDRB.ID
2132 AND OKCLINB_LINE.LINE_NUMBER = OLSTG.LINE_NUMBER
2133 AND OKSLINB_LINE.CLE_ID = OKCLINB_LINE.ID
2134 AND OLSTG.LINE_TYPE <> 'USAGE';
2135
2136
2137 -- ===========================
2138 -- VARIABLES
2139 -- ===========================
2140
2141 l_stmt_num NUMBER := 0;
2142 l_routine CONSTANT VARCHAR2(30) := 'Generate_PM_schedules';
2143
2144
2145 l_api_version CONSTANT NUMBER := 1.0;
2146 l_init_msg_list CONSTANT VARCHAR2(1):= 'F';
2147 l_return_status VARCHAR2(1);
2148 l_msg_count NUMBER;
2149 l_msg_data VARCHAR2(2000):=null;
2150
2151 TYPE cur_line_cnt_txn_tab IS TABLE OF get_line_cnt_details%rowtype INDEX BY BINARY_INTEGER;
2152 l_cur_line_cnt_txn_tab cur_line_cnt_txn_tab;
2153 l_empty_txn_tab cur_line_cnt_txn_tab;
2154
2155 l_current_index BINARY_INTEGER := 0;
2156 l_batch_size NUMBER := 200;
2157 l_loop_count NUMBER := 0;
2158
2159 BEGIN
2160
2161 IF G_PROCEDURE_LOG THEN
2162 fnd_log.string(fnd_log.level_procedure,
2163 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
2164 'Entering .' );
2165 END IF;
2166
2167 FND_MSG_PUB.initialize;
2168
2169 l_stmt_num := 10;
2170
2171 IF NOT get_line_cnt_details%ISOPEN THEN
2172 OPEN get_line_cnt_details ;
2173 END IF;
2174
2175 LOOP
2176 l_cur_line_cnt_txn_tab := l_empty_txn_tab;
2177 FETCH get_line_cnt_details BULK COLLECT INTO l_cur_line_cnt_txn_tab LIMIT l_batch_size;
2178
2179 l_loop_count := l_cur_line_cnt_txn_tab.count;
2180
2181 FOR i IN 1..l_loop_count
2182 LOOP
2183 l_stmt_num := 20;
2184
2185 OKS_PM_PROGRAMS_PVT.CREATE_PM_PROGRAM_SCHEDULE
2186 (p_api_version => l_api_version ,
2187 p_init_msg_list => l_init_msg_list,
2188 x_return_status => l_return_status ,
2189 x_msg_count => l_msg_count ,
2190 x_msg_data => l_msg_data ,
2191 p_template_cle_id => l_cur_line_cnt_txn_tab(i).reference_template_id ,
2192 p_cle_id => l_cur_line_cnt_txn_tab(i).cle_id ,
2193 p_cov_start_date => l_cur_line_cnt_txn_tab(i).start_date ,
2194 p_cov_end_date => l_cur_line_cnt_txn_tab(i).end_date );
2195
2196 END LOOP; -- FOR i IN 1..l_loop_count
2197
2198 IF G_EXCEPTION_LOG THEN
2199 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION, G_MODULE_HEAD || l_routine , l_msg_data || 'Return Status ' || l_return_status);
2200 END IF;
2201
2202 EXIT WHEN get_line_cnt_details%NOTFOUND;
2203
2204 END LOOP; -- fetch loop
2205
2206 CLOSE get_line_cnt_details;
2207
2208 IF G_STMT_LOG THEN
2209
2210 fnd_log.string(fnd_log.level_statement,
2211 G_MODULE_HEAD || l_routine || '.' || l_stmt_num,
2212 ' Succesffully Created Preventive Maintainence Schedules ' );
2213 END IF;
2214
2215 IF G_PROCEDURE_LOG THEN
2216 fnd_log.string(fnd_log.level_procedure,
2217 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
2218 'Exit.');
2219 END IF;
2220
2221 EXCEPTION
2222 WHEN FND_API.G_EXC_ERROR THEN
2223 -- ROLLBACK;
2224 RAISE FND_API.G_EXC_ERROR;
2225 WHEN OTHERS THEN
2226 -- ROLLBACK;
2227 FND_MESSAGE.Set_Name('OKS', 'OKS_IMPORT_UNEXPECTED');
2228 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
2229 FND_MESSAGE.set_token('MESSAGE', 'stmt_num '||l_stmt_num||' ('||SQLCODE||') '||SQLERRM);
2230 FND_MSG_PUB.Add;
2231 RAISE FND_API.G_EXC_ERROR;
2232
2233 END Generate_PM_schedules ;
2234
2235 --========================================================================
2236 -- PROCEDURE : Create_JTF_notes PRIVATE
2237 -- PARAMETERS :
2238 -- COMMENT : This procedure will invoke the API to create JTF notes
2239 -- for headers and lines.
2240 --=========================================================================
2241
2242 PROCEDURE Create_JTF_notes
2243 IS
2244
2245 CURSOR get_hdr_notes
2246 IS
2247
2248 SELECT 'OKS_HDR_NOTE' SOURCE_OBJECT_CODE
2249 ,OKCHDRB.ID SOURCE_OBJECT_ID
2250 ,OKS_NT_INT.NOTES NOTES
2251 ,OKS_NT_INT.NOTES_DETAIL NOTES_DETAIL
2252 ,OKS_NT_INT.NOTE_STATUS NOTE_STATUS
2253 ,OKS_NT_INT.NOTE_TYPE NOTE_TYPE
2254 ,OKS_NT_INT.ENTERED_BY ENTERED_BY
2255 ,OKS_NT_INT.ENTERED_DATE ENTERED_DATE
2256 FROM OKS_NOTES_INTERFACE OKS_NT_INT
2257 ,OKS_int_header_stg_temp HDRSTG
2258 ,OKC_K_HEADERS_ALL_B OKCHDRB
2259 WHERE OKS_NT_INT.HEADER_INTERFACE_ID = HDRSTG.HEADER_INTERFACE_ID
2260 AND OKS_NT_INT.LINE_INTERFACE_ID IS NULL
2261 AND HDRSTG.INTERFACE_STATUS ='S'
2262 AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
2263 AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') ;
2264
2265
2266 CURSOR get_line_notes
2267 IS
2268
2269 SELECT 'OKS_COV_NOTE' SOURCE_OBJECT_CODE
2270 ,OKCLINB.ID SOURCE_OBJECT_ID
2271 ,OKS_NT_INT.NOTES NOTES
2272 ,OKS_NT_INT.NOTES_DETAIL NOTES_DETAIL
2273 ,OKS_NT_INT.NOTE_STATUS NOTE_STATUS
2274 ,OKS_NT_INT.NOTE_TYPE NOTE_TYPE
2275 ,OKS_NT_INT.ENTERED_BY ENTERED_BY
2276 ,OKS_NT_INT.ENTERED_DATE ENTERED_DATE
2277
2278 FROM OKS_NOTES_INTERFACE OKS_NT_INT
2279 ,OKS_INT_LINE_STG_TEMP OLSTG
2280 ,OKS_INT_HEADER_STG_TEMP HDRSTG
2281 ,OKC_K_HEADERS_ALL_B OKCHDRB
2282 ,OKC_K_LINES_B OKCLINB
2283
2284 WHERE OKS_NT_INT.LINE_INTERFACE_ID = OLSTG.LINE_INTERFACE_ID
2288 AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
2285 AND OLSTG.HEADER_INTERFACE_ID = HDRSTG.HEADER_INTERFACE_ID
2286 AND OKS_NT_INT.LINE_INTERFACE_ID IS NOT NULL
2287 AND HDRSTG.INTERFACE_STATUS ='S'
2289 AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
2290 AND OKCLINB.CHR_ID = OKCHDRB.ID
2291 AND OKCLInB.DNZ_CHR_ID = OKCHDRB.ID
2292 AND OKCLINB.LINE_NUMBER = OLSTG.LINE_NUMBER;
2293
2294 -- ================================
2295 -- VARIABLES
2296 -- ================================
2297
2298 l_stmt_num NUMBER := 0;
2299 l_routine CONSTANT VARCHAR2(30) := 'Create_JTF_notes' ;
2300
2301 l_return_status VARCHAR2(1);
2302 l_msg_count NUMBER;
2303 l_msg_data VARCHAR2(2000) := null ;
2304 l_jtf_note_id NUMBER;
2305 l_jtf_note_contexts_tab jtf_notes_pub.jtf_note_contexts_tbl_type ;
2306
2307
2308 TYPE cur_get_hdr_notes IS TABLE OF get_hdr_notes%rowtype INDEX BY BINARY_INTEGER;
2309 l_cur_hdr_notes_txn_tab cur_get_hdr_notes;
2310 l_empty_hdr_txn_tab cur_get_hdr_notes;
2311
2312 TYPE cur_get_line_notes IS TABLE OF get_line_notes%rowtype INDEX BY BINARY_INTEGER;
2313 l_cur_line_notes_txn_tab cur_get_line_notes;
2314 l_empty_line_txn_tab cur_get_line_notes;
2315
2316 l_current_index BINARY_INTEGER := 0;
2317 l_batch_size NUMBER := 200;
2318 l_loop_count NUMBER := 0;
2319
2320 BEGIN
2321
2322 IF G_PROCEDURE_LOG THEN
2323 fnd_log.string(fnd_log.level_procedure,
2324 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
2325 'Entering .' );
2326 END IF;
2327
2328 FND_MSG_PUB.initialize;
2329
2330 l_stmt_num := 10;
2331
2332 IF NOT get_hdr_notes%ISOPEN THEN
2333 OPEN get_hdr_notes ;
2334 END IF;
2335
2336 LOOP
2337 l_cur_hdr_notes_txn_tab := l_empty_hdr_txn_tab;
2338 FETCH get_hdr_notes BULK COLLECT INTO l_cur_hdr_notes_txn_tab LIMIT l_batch_size;
2339
2340 l_loop_count := l_cur_hdr_notes_txn_tab.count;
2341
2342 FOR i IN 1..l_loop_count
2343 LOOP
2344 l_stmt_num := 20;
2345
2346 JTF_NOTES_PUB.create_note
2347 (p_jtf_note_id => NULL
2348 , p_api_version => 1.0
2349 , p_init_msg_list => 'F'
2350 , p_commit => 'F'
2351 , p_validation_level => 0
2352 , x_return_status => l_return_status
2353 , x_msg_count => l_msg_count
2354 , x_msg_data => l_msg_data
2355 , p_source_object_code => l_cur_hdr_notes_txn_tab(i).source_object_code
2356 , p_source_object_id => l_cur_hdr_notes_txn_tab(i).source_object_id
2357 , p_notes => l_cur_hdr_notes_txn_tab(i).notes
2358 , p_notes_detail => l_cur_hdr_notes_txn_tab(i).notes_detail
2359 , p_note_status => l_cur_hdr_notes_txn_tab(i).note_status
2360 , p_note_type => l_cur_hdr_notes_txn_tab(i).note_type
2361 , p_entered_by => l_cur_hdr_notes_txn_tab(i).entered_by
2362 , p_entered_date => l_cur_hdr_notes_txn_tab(i).entered_date
2363 , x_jtf_note_id => l_jtf_note_id
2364 , p_creation_date => SYSDATE
2365 , p_created_by => FND_GLOBAL.USER_ID
2366 , p_last_update_date => SYSDATE
2367 , p_last_updated_by => FND_GLOBAL.USER_ID
2368 , p_last_update_login => FND_GLOBAL.LOGIN_ID
2369 , p_attribute1 => NULL
2370 , p_attribute2 => NULL
2371 , p_attribute3 => NULL
2372 , p_attribute4 => NULL
2373 , p_attribute5 => NULL
2374 , p_attribute6 => NULL
2375 , p_attribute7 => NULL
2376 , p_attribute8 => NULL
2377 , p_attribute9 => NULL
2378 , p_attribute10 => NULL
2379 , p_attribute11 => NULL
2380 , p_attribute12 => NULL
2381 , p_attribute13 => NULL
2382 , p_attribute14 => NULL
2383 , p_attribute15 => NULL
2384 , p_context => NULL
2385 , p_jtf_note_contexts_tab => l_jtf_note_contexts_tab);
2386
2387 END LOOP; -- FOR i IN 1..l_loop_count
2388
2389 IF G_EXCEPTION_LOG THEN
2390 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION, G_MODULE_HEAD || l_routine , l_msg_data || 'Return Status ' || l_return_status);
2391 END IF;
2392
2393 EXIT WHEN get_hdr_notes%NOTFOUND;
2394
2395 END LOOP; -- fetch loop
2396
2397 CLOSE get_hdr_notes;
2398
2399 IF G_STMT_LOG THEN
2400
2401 fnd_log.string(fnd_log.level_statement,
2402 G_MODULE_HEAD || l_routine || '.' || l_stmt_num,
2403 ' Succesffully Created JTF Notes for headers ' );
2404 END IF;
2405
2406 l_stmt_num := 20;
2407
2408 IF NOT get_line_notes%ISOPEN THEN
2409 OPEN get_line_notes ;
2410 END IF;
2411
2412 LOOP
2413 l_cur_line_notes_txn_tab := l_empty_line_txn_tab;
2414 FETCH get_line_notes BULK COLLECT INTO l_cur_line_notes_txn_tab LIMIT l_batch_size;
2415
2416 l_loop_count := l_cur_line_notes_txn_tab.count;
2417
2418 FOR i IN 1..l_loop_count
2419
2420 LOOP
2421 l_stmt_num := 20;
2422
2423 JTF_NOTES_PUB.create_note
2424 (p_jtf_note_id => NULL
2425 , p_api_version => 1.0
2426 , p_init_msg_list => 'F'
2427 , p_commit => 'F'
2428 , p_validation_level => 0
2429 , x_return_status => l_return_status
2430 , x_msg_count => l_msg_count
2431 , x_msg_data => l_msg_data
2432 , p_source_object_code => l_cur_line_notes_txn_tab(i).source_object_code
2436 , p_note_status => l_cur_line_notes_txn_tab(i).note_status
2433 , p_source_object_id => l_cur_line_notes_txn_tab(i).source_object_id
2434 , p_notes => l_cur_line_notes_txn_tab(i).notes
2435 , p_notes_detail => l_cur_line_notes_txn_tab(i).notes_detail
2437 , p_note_type => l_cur_line_notes_txn_tab(i).note_type
2438 , p_entered_by => l_cur_line_notes_txn_tab(i).entered_by
2439 , p_entered_date => l_cur_line_notes_txn_tab(i).entered_date
2440 , x_jtf_note_id => l_jtf_note_id
2441 , p_creation_date => SYSDATE
2442 , p_created_by => FND_GLOBAL.USER_ID
2443 , p_last_update_date => SYSDATE
2444 , p_last_updated_by => FND_GLOBAL.USER_ID
2445 , p_last_update_login => FND_GLOBAL.LOGIN_ID
2446 , p_attribute1 => NULL
2447 , p_attribute2 => NULL
2448 , p_attribute3 => NULL
2449 , p_attribute4 => NULL
2450 , p_attribute5 => NULL
2451 , p_attribute6 => NULL
2452 , p_attribute7 => NULL
2453 , p_attribute8 => NULL
2454 , p_attribute9 => NULL
2455 , p_attribute10 => NULL
2456 , p_attribute11 => NULL
2457 , p_attribute12 => NULL
2458 , p_attribute13 => NULL
2459 , p_attribute14 => NULL
2460 , p_attribute15 => NULL
2461 , p_context => NULL
2462 , p_jtf_note_contexts_tab => l_jtf_note_contexts_tab);
2463
2464 END LOOP; -- FOR i IN 1..l_loop_count
2465
2466 IF G_EXCEPTION_LOG THEN
2467 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION, G_MODULE_HEAD || l_routine , l_msg_data || 'Return Status ' || l_return_status);
2468 END IF;
2469
2470 EXIT WHEN get_line_notes%NOTFOUND;
2471
2472 END LOOP; -- fetch loop
2473
2474 CLOSE get_line_notes;
2475
2476 IF G_STMT_LOG THEN
2477
2478 fnd_log.string(fnd_log.level_statement,
2479 G_MODULE_HEAD || l_routine || '.' || l_stmt_num,
2480 ' Succesffully Created JTF Notes for Lines ' );
2481 END IF;
2482
2483 IF G_PROCEDURE_LOG THEN
2484 fnd_log.string(fnd_log.level_procedure,
2485 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
2486 'Exit.');
2487 END IF;
2488
2489
2490 EXCEPTION
2491 WHEN FND_API.G_EXC_ERROR THEN
2492 -- ROLLBACK;
2493 RAISE FND_API.G_EXC_ERROR;
2494 WHEN OTHERS THEN
2495 -- ROLLBACK;
2496 FND_MESSAGE.Set_Name('OKS', 'OKS_IMPORT_UNEXPECTED');
2497 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
2498 FND_MESSAGE.set_token('MESSAGE', 'stmt_num '||l_stmt_num||' ('||SQLCODE||') '||SQLERRM);
2499 FND_MSG_PUB.Add;
2500 RAISE FND_API.G_EXC_ERROR;
2501
2502 END Create_JTF_notes ;
2503
2504 --========================================================================
2505 -- PROCEDURE : Instantiate_srvc_ctr_events PRIVATE
2506 -- PARAMETERS:
2507 -- COMMENT : This procedure will invoke the API used to instantiate
2508 -- (a) Service counters associated with the service item and
2509 -- (b) events that have been defined for the item.
2510 --=========================================================================
2511
2512 PROCEDURE Instantiate_srvc_ctr_events
2513 IS
2514
2515 CURSOR get_line_itm_id
2516 IS
2517 SELECT OLSTG.ITEM_ID SERVICE_ITEM_ID
2518 ,OKCHDRB.ID HDRB_ID
2519 ,OKCLINB.ID LINB_ID
2520
2521 FROM OKS_INT_LINE_STG_TEMP OLSTG
2522 ,OKC_K_LINES_B OKCLINB
2523 ,OKC_K_HEADERS_ALL_B OKCHDRB
2524 ,OKS_INT_HEADER_STG_TEMP HDRSTG
2525
2526 WHERE OLSTG.HEADER_INTERFACE_ID = HDRSTG.HEADER_INTERFACE_ID
2527 AND HDRSTG.INTERFACE_STATUS ='S'
2528 AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
2529 AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
2530 AND OLSTG.LINE_NUMBER = OKCLINB.LINE_NUMBER
2531 AND OKCLINB.DNZ_CHR_ID = OKCHDRB.ID
2532 AND OKCLINB.CHR_ID = OKCHDRB.ID
2533 AND OLSTG.LINE_TYPE ='SERVICE';
2534
2535 -- ===========================
2536 -- VARIABLES
2537 -- ===========================
2538
2539 l_stmt_num NUMBER := 0;
2540 l_routine CONSTANT VARCHAR2(30) := 'Instantiate_srvc_ctr_events';
2541
2542 l_return_status VARCHAR2(1);
2543 l_msg_count NUMBER;
2544 l_msg_data VARCHAR2(2000):=null;
2545
2546
2547 l_ctr_grp_id_template NUMBER;
2548 l_ctr_grp_id_instance NUMBER;
2549 l_instcnd_inp_rec OKC_INST_CND_PUB.instcnd_inp_rec;
2550
2551 TYPE cur_line_itm_txn_tab IS TABLE OF get_line_itm_id%rowtype INDEX BY BINARY_INTEGER;
2552 l_cur_line_itm_txn_tab cur_line_itm_txn_tab;
2553 l_empty_txn_tab cur_line_itm_txn_tab;
2554
2555 l_current_index BINARY_INTEGER := 0;
2556 l_batch_size NUMBER := 200;
2557 l_loop_count NUMBER := 0;
2558
2559
2560 BEGIN
2561
2562 IF G_PROCEDURE_LOG THEN
2563 fnd_log.string(fnd_log.level_procedure,
2564 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
2565 'Entering .' );
2566 END IF;
2567
2568 FND_MSG_PUB.initialize;
2569
2570 l_stmt_num := 10;
2571
2572 IF NOT get_line_itm_id%ISOPEN THEN
2573 OPEN get_line_itm_id ;
2574 END IF;
2575
2576 l_stmt_num :=20;
2577 LOOP
2578
2579 l_cur_line_itm_txn_tab := l_empty_txn_tab;
2580 FETCH get_line_itm_id BULK COLLECT INTO l_cur_line_itm_txn_tab LIMIT l_batch_size;
2581
2582 l_loop_count := l_cur_line_itm_txn_tab.count;
2583
2584 FOR i IN 1..l_loop_count
2585 LOOP
2586 CS_COUNTERS_PUB.AUTOINSTANTIATE_COUNTERS(
2587 p_api_version => 1.0 ,
2588 p_init_msg_list => okc_api.g_false,
2589 x_return_status => l_return_status,
2590 x_msg_count => l_msg_count,
2591 x_msg_data => l_msg_data,
2592 p_commit => 'F',
2593 p_source_object_id_template => l_cur_line_itm_txn_tab(i).service_item_id,
2594 p_source_object_id_instance => l_cur_line_itm_txn_tab(i).linb_id,
2595 x_ctr_grp_id_template => l_ctr_grp_id_template,
2596 x_ctr_grp_id_instance => l_ctr_grp_id_instance);
2597
2598 IF G_STMT_LOG THEN
2599 fnd_log.string(fnd_log.level_statement,
2600 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
2601 'Auto instatianted Counters for Record number' || i || ' Return Status is '
2602 || l_return_status );
2603 END IF;
2604
2605 l_instcnd_inp_rec.ins_ctr_grp_id := l_ctr_grp_id_instance;
2606 l_instcnd_inp_rec.tmp_ctr_grp_id := l_ctr_grp_id_template;
2607 l_instcnd_inp_rec.chr_id := l_cur_line_itm_txn_tab(i).hdrb_id;
2608 l_instcnd_inp_rec.cle_id := l_cur_line_itm_txn_tab(i).linb_id ;
2609 l_instcnd_inp_rec.jtot_object_code := 'OKC_K_LINE';
2610 l_instcnd_inp_rec.inv_item_id := l_cur_line_itm_txn_tab(i).service_item_id ;
2611
2612 OKC_INST_CND_PUB.INST_CONDITION(
2613 p_api_version => 1.0 ,
2614 p_init_msg_list => okc_api.g_false,
2615 x_return_status => l_return_status,
2616 x_msg_count => l_msg_count,
2617 x_msg_data => l_msg_data,
2618 p_instcnd_inp_rec => l_instcnd_inp_rec);
2619
2620 IF G_STMT_LOG THEN
2621 fnd_log.string(fnd_log.level_statement,
2622 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
2623 'Instantiated events for Record number' || i || ' Return Status is '
2624 || l_return_status );
2625 END IF;
2626
2627 END LOOP; -- FOR i IN 1..l_loop_count
2628
2629 IF G_EXCEPTION_LOG THEN
2630 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION, G_MODULE_HEAD || l_routine , l_msg_data || 'Return Status ' || l_return_status);
2631 END IF;
2632
2633 EXIT WHEN get_line_itm_id%NOTFOUND ;
2634
2635 END LOOP; -- fetch loop
2636
2637 CLOSE get_line_itm_id;
2638
2639 IF G_PROCEDURE_LOG THEN
2640 fnd_log.string(fnd_log.level_procedure,
2641 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
2642 'Exit.');
2643 END IF;
2644
2645 EXCEPTION
2646 WHEN FND_API.G_EXC_ERROR THEN
2647 -- ROLLBACK;
2648 RAISE FND_API.G_EXC_ERROR;
2649 WHEN OTHERS THEN
2650 -- ROLLBACK;
2651 FND_MESSAGE.Set_Name('OKS', 'OKS_IMPORT_UNEXPECTED');
2652 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
2653 FND_MESSAGE.set_token('MESSAGE', 'stmt_num '||l_stmt_num||' ('||SQLCODE||') '||SQLERRM);
2654 FND_MSG_PUB.Add;
2655 RAISE FND_API.G_EXC_ERROR;
2656
2657 END Instantiate_srvc_ctr_events ;
2658
2659
2660 --========================================================================
2661 -- PROCEDURE : Import_Post_Insert PUBLIC
2662 -- PARAMETERS:
2663 -- COMMENT : This procedure will invoke the procedures to implement
2664 -- the Post Insert Process
2665 --=========================================================================
2666
2667
2668 PROCEDURE Import_Post_Insert
2669 IS
2670
2671 l_stmt_num NUMBER := 0;
2672 l_routine CONSTANT VARCHAR2(30) := 'Import_Post_Insert';
2673 BEGIN
2674
2675 IF G_PROCEDURE_LOG THEN
2676 fnd_log.string(fnd_log.level_procedure,
2677 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
2678 'Entering .' );
2679
2680 END IF;
2681
2682 FND_MSG_PUB.initialize;
2683
2684 l_stmt_num := 10;
2685
2686 Generate_billing_schedules ;
2687 Generate_PM_schedules ;
2688 Create_JTF_notes ;
2689 Instantiate_srvc_ctr_events;
2690
2691 IF G_PROCEDURE_LOG THEN
2692 fnd_log.string(fnd_log.level_procedure,
2693 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
2694 'Exit.');
2695 END IF;
2696
2697 EXCEPTION
2698 WHEN FND_API.G_EXC_ERROR THEN
2699 -- ROLLBACK;
2700 RAISE FND_API.G_EXC_ERROR;
2701 WHEN OTHERS THEN
2702 -- ROLLBACK;
2703 FND_MESSAGE.Set_Name('OKS', 'OKS_IMPORT_UNEXPECTED');
2704 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
2705 FND_MESSAGE.set_token('MESSAGE', 'stmt_num '||l_stmt_num||' ('||SQLCODE||') '||SQLERRM);
2706 FND_MSG_PUB.Add;
2707 RAISE FND_API.G_EXC_ERROR;
2708
2709 END Import_Post_Insert;
2710
2711 END OKS_IMPORT_POST_INSERT;