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