[Home] [Help]
PACKAGE BODY: APPS.OKS_IMPORT_INSERT
Source
1 PACKAGE BODY OKS_IMPORT_INSERT AS
2 -- $Header: OKSPKIMPINSB.pls 120.14.12020000.6 2013/03/21 04:55:19 skuchima ship $
3 --+=======================================================================+
4 --| Copyright (c) 2003 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| OKSPKIMPINSB.pls Created By Mihira Karra |
10 --| |
11 --| DESCRIPTION |
12 --| Service Contracts Import Insert Routines Package |
13 --| |
14 --| HISTORY: 07-28-08 -- Svaidya Created |
15 --| |
16 --| mchandak 13-SEP-2012 BUG#14601764, Modified UOM_CODE that should be|
17 --| inserted in OKC_K_ITEMS table |
18 --| skuchima 21-mar-2013 Bug#16468231 |
19 --+========================================================================
20
21 --===================
22 -- GLOBALS
23 --===================
24
25 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OKS_IMPORT_INSERT';
26
27 --========================================================================
28 -- PRIVATE CONSTANTS AND VARIABLES
29 --========================================================================
30 G_MODULE_NAME CONSTANT VARCHAR2(50) := 'oks.plsql.import.' || G_PKG_NAME;
31 G_WORKER_REQ_ID CONSTANT NUMBER := FND_GLOBAL.conc_request_id;
32 G_MODULE_HEAD CONSTANT VARCHAR2(60) := G_MODULE_NAME || '(Req Id = '||G_WORKER_REQ_ID||').';
33 G_LOG_LEVEL CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
34 G_UNEXPECTED_LOG CONSTANT BOOLEAN := fnd_log.level_unexpected >= G_LOG_LEVEL AND
35 fnd_log.TEST(fnd_log.level_unexpected, G_MODULE_HEAD);
36 G_ERROR_LOG CONSTANT BOOLEAN := G_UNEXPECTED_LOG AND fnd_log.level_error >= G_LOG_LEVEL;
37 G_EXCEPTION_LOG CONSTANT BOOLEAN := G_ERROR_LOG AND fnd_log.level_exception >= G_LOG_LEVEL;
38 G_EVENT_LOG CONSTANT BOOLEAN := G_EXCEPTION_LOG AND fnd_log.level_event >= G_LOG_LEVEL;
39 G_PROCEDURE_LOG CONSTANT BOOLEAN := G_EVENT_LOG AND fnd_log.level_procedure >= G_LOG_LEVEL;
40 G_STMT_LOG CONSTANT BOOLEAN := G_PROCEDURE_LOG AND fnd_log.level_statement >= G_LOG_LEVEL;
41
42 --=========================
43 -- PROCEDURES AND FUNCTIONS
44 --=========================
45
46 --========================================================================
47 -- PROCEDURE : Insert_okc_header PRIVATE
48 -- PARAMETERS:
49 -- COMMENT : This procedure will insert into Okc Contract Header
50 --=========================================================================
51
52 PROCEDURE Insert_okc_header
53 IS
54 l_stmt_num NUMBER := 0;
55 l_routine CONSTANT VARCHAR2(30) := 'Insert_okc_header';
56 l_int_count NUMBER := 0;
57 l_stg_count NUMBER := 0;
58 BEGIN
59 IF G_PROCEDURE_LOG THEN
60 fnd_log.string(fnd_log.level_procedure,
61 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
62 'Entering ');
63
64 END IF;
65
66 l_stmt_num := 10;
67
68 INSERT
69 INTO OKC_K_HEADERS_ALL_B
70 (ID
71 ,CONTRACT_NUMBER
72 ,AUTHORING_ORG_ID
73 ,CONTRACT_NUMBER_MODIFIER
74 ,INV_ORGANIZATION_ID
75 ,STS_CODE
76 ,QCL_ID
77 ,SCS_CODE
78 ,TRN_CODE
79 ,CURRENCY_CODE
80 ,ARCHIVED_YN
81 ,DELETED_YN
82 ,TEMPLATE_YN
83 ,CHR_TYPE
84 ,OBJECT_VERSION_NUMBER
85 ,CREATED_BY
86 ,CREATION_DATE
87 ,LAST_UPDATED_BY
88 ,CUST_PO_NUMBER_REQ_YN
89 ,PRE_PAY_REQ_YN
90 ,CUST_PO_NUMBER
91 ,DATE_APPROVED
92 ,DATETIME_CANCELLED
93 ,DATE_SIGNED
94 ,START_DATE
95 ,END_DATE
96 ,LAST_UPDATE_LOGIN
97 ,LAST_UPDATE_DATE
98 ,APPLICATION_ID
99 ,ORIG_SYSTEM_SOURCE_CODE
100 ,ORIG_SYSTEM_ID1
101 ,ORIG_SYSTEM_REFERENCE1
102 ,PROGRAM_APPLICATION_ID
103 ,PROGRAM_ID
104 ,PROGRAM_UPDATE_DATE
105 ,REQUEST_ID
106 ,PRICE_LIST_ID
107 ,CONVERSION_TYPE
108 ,CONVERSION_RATE
109 ,CONVERSION_RATE_DATE
110 ,BILL_TO_SITE_USE_ID
111 ,INV_RULE_ID
112 ,RENEWAL_TYPE_CODE
113 ,RENEWAL_END_DATE
114 ,SHIP_TO_SITE_USE_ID
115 ,PAYMENT_TERM_ID
116 ,APPROVAL_TYPE
117 ,PAYMENT_INSTRUCTION_TYPE
118 ,ORG_ID
119 ,CANCELLED_AMOUNT
120 ,BILLED_AT_SOURCE
121 ,BUY_OR_SELL
122 ,ISSUE_OR_RECEIVE
123 ,ESTIMATED_AMOUNT)
124
125 SELECT okc_k_headers_b_s.nextval HDRBID , ST.*
126
127 FROM
128 (SELECT HDRSTG.CONTRACT_NUMBER CONTRACT_NUMBER
129 ,HDRSTG.OPERATING_UNIT_ID OPERATING_UNIT_ID
130 ,HDRSTG.CONTRACT_NUMBER_MODIFIER CONTRACT_NUMBER_MODIFIER
131 ,HDRSTG.INV_ORGANIZATION_ID INV_ORGANIZATION_ID
132 ,HDRSTG.STATUS_CODE STATUS_CODE
133 ,HDRSTG.QA_CHECKLIST QA_CHECKLIST
134 ,HDRSTG.CATEGORY CATEGORY
135 ,HDRSTG.CANCELLATION_REASON CANCELLATION_REASON
136 ,HDRSTG.CONTRACT_CURRENCY_CODE CONTRACT_CURRENCY_CODE
137 ,'N' ARCHIVED_YN
138 ,'N' DELETED_YN
139 ,'N' TEMPLATE_YN
140 ,'CYA' CHR_TYPE
141 ,1 OBJECT_VERSION_NUMBER
142 ,FND_GLOBAL.USER_ID CREATED_BY
143 ,SYSDATE CREATION_DATE
144 ,FND_GLOBAL.USER_ID LAST_UPDATED_BY
145 ,NVL(HDRSTG.PO_REQUIRED,'N') PO_REQUIRED
146 ,HDRSTG.SERVICE_CHRG_PREPAY_REQ SERVICE_CHRG_PREPAY_REQ
147 ,HDRSTG.PAYMENT_INSTRUCTION_DETAILS PAYMENT_INSTRUCTION_DETAILS
148 ,HDRSTG.DATE_APPROVED DATE_APPROVED
149 ,HDRSTG.DATE_CANCELED DATE_CANCELLED
150 ,HDRSTG.DATE_SIGNED DATE_SIGNED
151 ,HDRSTG.START_DATE START_DATE
152 ,HDRSTG.END_DATE END_DATE
153 ,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
154 ,SYSDATE LAST_UPDATE_DATE
155 ,515 APPLICATION_ID
156 ,HDRSTG.SOURCE ORIG_SYSTEM_SOURCE_CODE
157 ,null ORIG_SYSTEM_ID1
158 ,HDRSTG.DOCUMENT ORIG_SYSTEM_REFERENCE1
159 ,515 PROGRAM_APPLICATION_ID
160 ,FND_GLOBAL.CONC_PROGRAM_ID PROGRAM_ID
161 ,SYSDATE PROGRAM_UPDATE_DATE
162 ,FND_GLOBAL.CONC_REQUEST_ID REQUEST_ID
163 ,HDRSTG.PRICE_LIST_ID PRICE_LIST_ID
164 ,HDRSTG.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
165 ,HDRSTG.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
166 ,HDRSTG.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
167 ,HDRSTG.BILL_TO_SITE_USAGE_ID BILL_TO_SITE_USAGE_ID
168 ,HDRSTG.INVOICING_RULE_ID INVOICING_RULE_ID
169 ,HDRSTG.RENEWAL_PROCESS RENEWAL_PROCESS
170 ,HDRSTG.RENEW_UP_TO RENEW_UP_TO
171 ,HDRSTG.SHIP_TO_SITE_USAGE_ID SHIP_TO_SITE_USAGE_ID
172 ,HDRSTG.PAYMENT_TERMS_ID PAYMENT_TERMS_ID
173 ,HDRSTG.APPROVAL_REQUIRED APPROVAL_REQUIRED
174 ,HDRSTG.PAYMENT_INSTRUCTION PAYMENT_INSTRUCTION
175 ,HDRSTG.OPERATING_UNIT_ID ORG_ID
176 ,null CANCELLED_AMOUNT -- cancelled_amount, no corresponding field found
177 ,HDRSTG.FULLY_BILLED BILLED_AT_SOURCE
178 ,'S' BUY_OR_SELL
179 ,'I' ISSUE_OR_RECEIVE
180 ,NVL(INNER_2.TOTAL,0) ESTIMATED_AMOUNT
181
182 FROM OKS_INT_HEADER_STG_TEMP HDRSTG
183 ,(SELECT INNER_1.HEADER_INTERFACE_ID
184 ,SUM(ESTIMATED_AMOUNT) TOTAL
185 FROM ((SELECT HDR.HEADER_INTERFACE_ID
186 ,NVL(SUM(INNER2_CVL.CVL_SUBTOTAL),0) + NVL(SUM(INNER2_USG.USG_SUBTOTAL),0) as ESTIMATED_AMOUNT
187 FROM OKS_INT_HEADER_STG_TEMP HDR
188 ,OKS_INT_LINE_STG_TEMP LSTG
189
190 ,(SELECT SUM(NVL(COV.SUBTOTAL,0)) AS CVL_SUBTOTAL
191 ,COV.LINE_INTERFACE_ID
192 FROM OKS_COVERED_LEVELS_INTERFACE COV
193 ,OKS_INT_COVERED_LEVEL_STG_TEMP CVL
194 WHERE CVL.COVERED_LEVEL_INTERFACE_ID= COV.COVERED_LEVEL_INTERFACE_ID
195 AND COV.STATUS_CODE <>'CANCELLED'
196 GROUP BY COV.LINE_INTERFACE_ID ) INNER2_CVL
197
198 ,(SELECT SUM(NVL(USG.SUBTOTAL,0)) AS USG_SUBTOTAL
199 ,USG.LINE_INTERFACE_ID
200 FROM OKS_INT_USAGE_COUNTER_STG_TEMP USG
201 WHERE USG.STATUS_CODE <>'CANCELLED'
202 GROUP BY USG.LINE_INTERFACE_ID) INNER2_USG
203
204 WHERE LSTG.LINE_INTERFACE_ID = INNER2_CVL.LINE_INTERFACE_ID(+)
205 AND LSTG.LINE_INTERFACE_ID = INNER2_USG.LINE_INTERFACE_ID(+)
206 AND LSTG.HEADER_INTERFACE_ID = HDR.HEADER_INTERFACE_ID
207 AND LSTG.STATUS_CODE <> 'CANCELLED'
208 GROUP BY HDR.HEADER_INTERFACE_ID)
209
210 UNION
211
212 (SELECT LST.HEADER_INTERFACE_ID
213 ,SUM(LST.SUBTOTAL) ESTIMATED_AMOUNT
214 FROM OKS_INT_LINE_STG_TEMP LST
215 WHERE LST.LINE_TYPE ='SUBSCRIPTION'
216 AND LST.STATUS_CODE <>'CANCELLED'
217 GROUP BY LST.HEADER_INTERFACE_ID)) INNER_1
218 GROUP BY INNER_1.HEADER_INTERFACE_ID) INNER_2
219 WHERE HDRSTG.INTERFACE_STATUS ='S'
220 AND INNER_2.HEADER_INTERFACE_ID (+) = HDRSTG.HEADER_INTERFACE_ID )ST ;
221
222 IF G_STMT_LOG THEN
223
224 l_int_count := SQL%ROWCOUNT;
225 fnd_log.string(fnd_log.level_statement,
226 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
227 'Number of records successfully inserted = ' || l_int_count );
228 END IF;
229
230 l_stmt_num :=20;
231
232 INSERT ALL
233 WHEN (1=1) THEN
234 INTO OKC_K_HEADERS_TL
235 (ID
236 ,LANGUAGE
237 ,SOURCE_LANG
238 ,SFWT_FLAG
239 ,SHORT_DESCRIPTION
240 ,COMMENTS
241 ,DESCRIPTION
242 ,COGNOMEN
243 ,CREATED_BY
244 ,CREATION_DATE
245 ,LAST_UPDATED_BY
246 ,LAST_UPDATE_DATE
247 ,LAST_UPDATE_LOGIN )
248 VALUES (HDRB_ID
249 ,LANGUAGE
250 ,SOURCE_LANG
251 ,SFWT_FLAG
252 ,SHORT_DESCRIPTION
253 ,COMMENTS
254 ,DESCRIPTION
255 ,KNOWN_AS
256 ,CREATED_BY
257 ,CREATION_DATE
258 ,LAST_UPDATED_BY
259 ,LAST_UPDATE_DATE
260 ,LAST_UPDATE_LOGIN )
261
262 INTO OKC_K_PROCESSES
263 (ID
264 ,PDF_ID
265 ,CHR_ID
266 ,OBJECT_VERSION_NUMBER
267 ,CREATED_BY
268 ,CREATION_DATE
269 ,LAST_UPDATED_BY
270 ,LAST_UPDATE_DATE
271 ,LAST_UPDATE_LOGIN)
272 VALUES (OKCKPRID
273 ,APPROVAL_PROCESS_ID
274 ,HDRB_ID
275 ,OBJECT_VERSION_NUMBER
276 ,CREATED_BY
277 ,CREATION_DATE
278 ,LAST_UPDATED_BY
279 ,LAST_UPDATE_DATE
280 ,LAST_UPDATE_LOGIN)
281
282 INTO OKC_K_VERS_NUMBERS
283 (CHR_ID
284 ,MAJOR_VERSION
285 ,MINOR_VERSION
286 ,OBJECT_VERSION_NUMBER
287 ,CREATED_BY
288 ,CREATION_DATE
289 ,LAST_UPDATED_BY
290 ,LAST_UPDATE_DATE
291 ,LAST_UPDATE_LOGIN )
292
293 VALUES (HDRB_ID
294 ,0
295 ,1
296 ,OBJECT_VERSION_NUMBER
297 ,CREATED_BY
298 ,CREATION_DATE
299 ,LAST_UPDATED_BY
300 ,LAST_UPDATE_DATE
301 ,LAST_UPDATE_LOGIN )
302
303 WHEN(AGREEMENT_ID IS NOT NULL ) THEN
304 INTO OKC_GOVERNANCES
305 (ID
306 ,DNZ_CHR_ID
307 ,CHR_ID
308 ,CLE_ID
309 ,ISA_AGREEMENT_ID
310 ,COPIED_ONLY_YN
311 ,OBJECT_VERSION_NUMBER
312 ,CREATED_BY
313 ,CREATION_DATE
314 ,LAST_UPDATED_BY
315 ,LAST_UPDATE_DATE
316 ,LAST_UPDATE_LOGIN)
317 VALUES ( OKCGID
318 ,HDRB_ID
319 ,HDRB_ID
320 ,null
321 ,AGREEMENT_ID
322 ,'N'
323 ,OBJECT_VERSION_NUMBER
324 ,CREATED_BY
325 ,CREATION_DATE
326 ,LAST_UPDATED_BY
327 ,LAST_UPDATE_DATE
328 ,LAST_UPDATE_LOGIN)
329
330 SELECT OKCHDRB.ID HDRB_ID
331 ,okc_p_util.raw_to_number(sys_guid()) OKCGID
332 ,okc_p_util.raw_to_number(sys_guid()) OKCKPRID
333 ,USERENV('LANG') LANGUAGE
334 ,USERENV('LANG') SOURCE_LANG
335 ,'N' SFWT_FLAG
336 ,HDRSTG.DESCRIPTION SHORT_DESCRIPTION -- no matching column as short_description
337 ,null COMMENTS
338 ,HDRSTG.DESCRIPTION DESCRIPTION
339 ,HDRSTG.KNOWN_AS KNOWN_AS
340 ,HDRSTG.AGREEMENT_ID AGREEMENT_ID
341 ,HDRSTG.APPROVAL_PROCESS_ID APPROVAL_PROCESS_ID
342 ,1 OBJECT_VERSION_NUMBER
343 ,FND_GLOBAL.USER_ID CREATED_BY
344 ,SYSDATE CREATION_DATE
345 ,FND_GLOBAL.USER_ID LAST_UPDATED_BY
346 ,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
347 ,SYSDATE LAST_UPDATE_DATE
348 FROM OKS_INT_HEADER_STG_TEMP HDRSTG
349 ,OKC_K_HEADERS_ALL_B OKCHDRB
350 WHERE HDRSTG.INTERFACE_STATUS ='S'
351 AND HDRSTG.CONTRACT_NUMBER=OKCHDRB.CONTRACT_NUMBER
352 AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') ;
353
354 IF G_STMT_LOG THEN
355
356 l_int_count := SQL%ROWCOUNT;
357 fnd_log.string(fnd_log.level_statement,
358 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
359 'Number of records successfully inserted = ' || l_int_count );
360 END IF;
361
362 IF G_PROCEDURE_LOG THEN
363
364 fnd_log.string(fnd_log.level_procedure,
365 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
366 'Exit with ' ||
367 'Number of records Inserted = '|| l_int_count);
368 END IF;
369
370 EXCEPTION
371 WHEN FND_API.G_EXC_ERROR THEN
372 RAISE FND_API.G_EXC_ERROR;
373 WHEN OTHERS THEN
374 FND_MESSAGE.Set_Name('OKS', 'OKS_IMPORT_UNEXPECTED');
375 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
376 FND_MESSAGE.set_token('MESSAGE', 'stmt_num '||l_stmt_num||' ('||SQLCODE||') '||SQLERRM);
377 FND_MSG_PUB.Add;
378 RAISE FND_API.G_EXC_ERROR;
379 END Insert_okc_header ;
380
381
382 --========================================================================
383 -- PROCEDURE : Insert_oks_header PRIVATE
384 -- PARAMETERS:
385 -- COMMENT : This procedure will insert into Oks Contract Header
386 --=========================================================================
387
388 PROCEDURE Insert_oks_header
389 IS
390 l_stmt_num NUMBER := 0;
391 l_routine CONSTANT VARCHAR2(30) := 'Insert_oks_header';
392 l_int_count NUMBER := 0;
393 l_stg_count NUMBER := 0;
394 BEGIN
395 IF G_PROCEDURE_LOG THEN
396 fnd_log.string(fnd_log.level_procedure,
397 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
398 'Begin');
399 END IF;
400
401 l_stmt_num := 10;
402
403 INSERT ALL
404 WHEN (1=1) THEN
405 INTO OKS_K_HEADERS_B
406 (ID
407 ,CHR_ID
408 ,ACCT_RULE_ID
409 ,PAYMENT_TYPE
410 ,COMMITMENT_ID
411 ,GRACE_DURATION
412 ,GRACE_PERIOD
413 ,EST_REV_PERCENT
414 ,EST_REV_DATE
415 ,TAX_STATUS
416 ,BILLING_PROFILE_ID
417 ,QUOTE_TO_CONTACT_ID
418 ,QUOTE_TO_SITE_ID
419 ,QUOTE_TO_EMAIL_ID
420 ,QUOTE_TO_PHONE_ID
421 ,QUOTE_TO_FAX_ID
422 ,PAYMENT_TYPE
423 ,RENEWAL_PO_REQUIRED
424 ,RENEWAL_PO_NUMBER
425 ,RENEWAL_PRICE_LIST
426 ,RENEWAL_MARKUP_PERCENT
427 ,RENEWAL_GRACE_DURATION
428 ,RENEWAL_GRACE_PERIOD
429 ,RENEWAL_EST_REV_PERCENT
430 ,RENEWAL_EST_REV_DURATION
431 ,RENEWAL_EST_REV_PERIOD
432 ,RENEWAL_PRICING_TYPE
433 ,INV_TRX_TYPE
434 ,INV_PRINT_PROFILE
435 ,AR_INTERFACE_YN
436 ,HOLD_BILLING
437 ,SUMMARY_TRX_YN
438 ,SERVICE_PO_NUMBER
439 ,SERVICE_PO_REQUIRED
440 ,OBJECT_VERSION_NUMBER
441 ,CREATED_BY
442 ,CREATION_DATE
443 ,LAST_UPDATED_BY
444 ,LAST_UPDATE_DATE
445 ,LAST_UPDATE_LOGIN
446 ,FOLLOW_UP_ACTION
447 ,FOLLOW_UP_DATE
448 ,TRXN_EXTENSION_ID
449 ,EXEMPT_CERTIFICATE_NUMBER
450 ,EXEMPT_REASON_CODE
451 ,TAX_AMOUNT
452 ,BILLING_SCHEDULE_TYPE)
453
454 VALUES (OKSHDRB_ID
455 ,CHR_ID
456 ,ACCOUNTING_RULE_ID
457 ,PAYMENT_INSTRUCTION
458 ,COMMITMENT_NUMBER_ID
459 ,GRACE_DURATION
460 ,GRACE_PERIOD
461 ,ESTIMATION_PERCENT
462 ,ESTIMATION_DATE
463 ,TAX_EXEMPTION_CONTROL
464 ,RENEWAL_BILLING_PROFILE_ID
465 ,QUOTE_TO_CONTACT
466 ,QUOTE_TO_PARTY_SITE
467 ,QUOTE_TO_EMAIL
468 ,QUOTE_TO_PHONE
469 ,QUOTE_TO_FAX
470 ,PAYMENT_METHOD_CODE
471 ,RENEWAL_PO_REQUIRED
472 ,RENEWAL_PO_NUMBER
473 ,RENEWAL_PRICE_LIST_ID
474 ,RENEWAL_MARKUP
475 ,RENEWAL_GRACE_DURATION
476 ,RENEWAL_GRACE_PERIOD
477 ,RENEWAL_ESTIMATED_PERCENT
478 ,RENEWAL_ESTIMATED_DURATION
479 ,RENEWAL_ESTIMATED_PERIOD
480 ,RENEWAL_PRICING_TYPE
481 ,BILLING_TRANSACTION_TYPE_ID
482 ,SUMMARY_PRINT
483 ,BILL_SERVICES
484 ,HOLD_CREDITS
485 ,SUMMARY_TRANSACTIONS
486 ,SERVICE_CHARGES_PO_NUMBER
487 ,SERVICE_CHARGES_PO_REQUIRED
488 ,OBJECT_VERSION_NUMBER
489 ,CREATED_BY
490 ,CREATION_DATE
491 ,LAST_UPDATED_BY
492 ,LAST_UPDATE_DATE
493 ,LAST_UPDATE_LOGIN
494 ,FOLLOW_UP_ACTION
495 ,FOLLOW_UP_DUE_DATE
496 ,CREDIT_CARD_TRXN_EXTENSION_ID
497 ,TAX_EXEMPTION_NUMBER
498 ,EXEMPT_REASON_CODE
499 ,TAX_AMOUNT
500 ,BILLING_SCHEDULE_TYPE)
501
502 SELECT okc_p_util.raw_to_number(sys_guid()) OKSHDRB_ID
503 ,OKCHDRB.ID CHR_ID
504 ,HDRSTG.ACCOUNTING_RULE_ID ACCOUNTING_RULE_ID
505 ,HDRSTG.PAYMENT_INSTRUCTION PAYMENT_INSTRUCTION
506 ,HDRSTG.COMMITMENT_ID COMMITMENT_NUMBER_ID
507 ,HDRSTG.GRACE_DURATION GRACE_DURATION
508 ,HDRSTG.GRACE_PERIOD GRACE_PERIOD
509 ,HDRSTG.ESTIMATION_PERCENT ESTIMATION_PERCENT
510 ,HDRSTG.ESTIMATION_DATE ESTIMATION_DATE
511 ,HDRSTG.TAX_EXEMPTION_CONTROL TAX_EXEMPTION_CONTROL
512 ,HDRSTG.RENEWAL_BILLING_PROFILE_ID RENEWAL_BILLING_PROFILE_ID
513 ,HDRSTG.QUOTE_TO_CONTACT QUOTE_TO_CONTACT
514 ,HDRSTG.QUOTE_TO_PARTY_SITE QUOTE_TO_PARTY_SITE
515 ,HDRSTG.QUOTE_TO_EMAIL QUOTE_TO_EMAIL
516 ,HDRSTG.QUOTE_TO_PHONE QUOTE_TO_PHONE
517 ,HDRSTG.QUOTE_TO_FAX QUOTE_TO_FAX
518 ,HDRSTG.PAYMENT_METHOD_CODE PAYMENT_METHOD_CODE
519 ,HDRSTG.RENEWAL_PO_REQUIRED RENEWAL_PO_REQUIRED
520 ,HDRSTG.RENEWAL_PO_NUMBER RENEWAL_PO_NUMBER
521 ,HDRSTG.RENEWAL_PRICE_LIST_ID RENEWAL_PRICE_LIST_ID
522 ,HDRSTG.RENEWAL_MARKUP RENEWAL_MARKUP
523 ,HDRSTG.RENEWAL_GRACE_DURATION RENEWAL_GRACE_DURATION
524 ,HDRSTG.RENEWAL_GRACE_PERIOD RENEWAL_GRACE_PERIOD
525 ,HDRSTG.RENEWAL_ESTIMATED_PERCENT RENEWAL_ESTIMATED_PERCENT
526 ,HDRSTG.RENEWAL_ESTIMATED_DURATION RENEWAL_ESTIMATED_DURATION
527 ,HDRSTG.RENEWAL_ESTIMATED_PERIOD RENEWAL_ESTIMATED_PERIOD
528 ,HDRSTG.PRICING_METHOD RENEWAL_PRICING_TYPE
529 ,HDRSTG.BILLING_TRANSACTION_TYPE_ID BILLING_TRANSACTION_TYPE_ID
530 ,HDRSTG.SUMMARY_PRINT SUMMARY_PRINT
531 ,HDRSTG.BILL_SERVICES BILL_SERVICES
532 ,HDRSTG.HOLD_CREDITS HOLD_CREDITS
533 ,HDRSTG.SUMMARY_TRANSACTIONS SUMMARY_TRANSACTIONS
534 ,HDRSTG.SERVICE_CHARGES_PO_NUMBER SERVICE_CHARGES_PO_NUMBER
535 ,HDRSTG.SERVICE_CHARGES_PO_REQUIRED SERVICE_CHARGES_PO_REQUIRED
536 ,1 OBJECT_VERSION_NUMBER
537 ,FND_GLOBAL.USER_ID CREATED_BY
538 ,SYSDATE CREATION_DATE
539 ,FND_GLOBAL.USER_ID LAST_UPDATED_BY
540 ,SYSDATE LAST_UPDATE_DATE
541 ,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
542 ,HDRSTG.FOLLOW_UP_ACTION FOLLOW_UP_ACTION
543 ,HDRSTG.FOLLOW_UP_DUE_DATE FOLLOW_UP_DUE_DATE
544 ,null CREDIT_CARD_TRXN_EXTENSION_ID
545 ,HDRSTG.TAX_EXEMPTION_NUMBER TAX_EXEMPTION_NUMBER
546 ,HDRSTG.EXEMPT_REASON_CODE EXEMPT_REASON_CODE
547 ,NVL(INNER_2.TAX_AMOUNT ,0) TAX_AMOUNT
548 ,'T' BILLING_SCHEDULE_TYPE
549
550 FROM OKS_INT_HEADER_STG_TEMP HDRSTG
551 ,OKC_K_HEADERS_ALL_B OKCHDRB
552 ,(SELECT INNER_1.HEADER_INTERFACE_ID
553 ,SUM(TAX_AMOUNT) TAX_AMOUNT
554 FROM
555 ((SELECT HDR.HEADER_INTERFACE_ID
556 ,NVL(SUM(INNER2_CVL.CVL_TAX_AMT),0) + NVL(SUM(INNER2_USG.USG_TAX_AMT),0) as TAX_AMOUNT
557
558 FROM OKS_INT_HEADER_STG_TEMP HDR
559 ,OKS_INT_LINE_STG_TEMP LSTG
560 ,(SELECT SUM(NVL(COV.TAX_AMOUNT,0)) AS CVL_TAX_AMT
561 ,COV.LINE_INTERFACE_ID
562 FROM OKS_COVERED_LEVELS_INTERFACE COV
563 ,OKS_INT_COVERED_LEVEL_STG_TEMP CVL
564 WHERE CVL.COVERED_LEVEL_INTERFACE_ID= COV.COVERED_LEVEL_INTERFACE_ID
565 AND COV.STATUS_CODE <>'CANCELLED'
566 GROUP BY COV.LINE_INTERFACE_ID ) INNER2_CVL
567 ,(SELECT SUM(NVL(USG.TAX_AMOUNT,0)) AS USG_TAX_AMT
568 ,USG.LINE_INTERFACE_ID
569 FROM OKS_INT_USAGE_COUNTER_STG_TEMP USG
570 WHERE USG.STATUS_CODE <>'CANCELLED'
571 GROUP BY USG.LINE_INTERFACE_ID) INNER2_USG
572 WHERE LSTG.LINE_INTERFACE_ID = INNER2_CVL.LINE_INTERFACE_ID(+)
573 AND LSTG.LINE_INTERFACE_ID = INNER2_USG.LINE_INTERFACE_ID(+)
574 AND LSTG.HEADER_INTERFACE_ID = HDR.HEADER_INTERFACE_ID
575 AND LSTG.STATUS_CODE <> 'CANCELLED'
576 GROUP BY HDR.HEADER_INTERFACE_ID)
577 UNION
578 (SELECT LST.HEADER_INTERFACE_ID
579 ,SUM(LST.TAX_AMOUNT) TAX_AMOUNT
580 FROM OKS_INT_LINE_STG_TEMP LST
581 WHERE LST.LINE_TYPE ='SUBSCRIPTION'
582 AND LST.STATUS_CODE <>'CANCELLED'
583 GROUP BY LST.HEADER_INTERFACE_ID)) INNER_1
584 GROUP BY INNER_1.HEADER_INTERFACE_ID) INNER_2
585
586 WHERE HDRSTG.INTERFACE_STATUS ='S'
587 AND HDRSTG.CONTRACT_NUMBER=OKCHDRB.CONTRACT_NUMBER
588 AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
589 AND INNER_2.HEADER_INTERFACE_ID (+) = HDRSTG.HEADER_INTERFACE_ID;
590
591 IF G_STMT_LOG THEN
592
593 l_int_count := SQL%ROWCOUNT;
594
595 fnd_log.string(fnd_log.level_statement,
596 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
597 'Number of records successfully inserted = ' || l_int_count );
598 END IF;
599
600 IF G_PROCEDURE_LOG THEN
601 fnd_log.string(fnd_log.level_procedure,
602 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
603 'Exit with ' ||
604 'Number of records Inserted = '|| l_int_count);
605 END IF;
606 EXCEPTION
607 WHEN FND_API.G_EXC_ERROR THEN
608 RAISE FND_API.G_EXC_ERROR;
609 WHEN OTHERS THEN
610 FND_MESSAGE.Set_Name('OKS', 'OKS_IMPORT_UNEXPECTED');
611 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
612 FND_MESSAGE.set_token('MESSAGE', 'stmt_num '||l_stmt_num||' ('||SQLCODE||') '||SQLERRM);
613 FND_MSG_PUB.Add;
614 RAISE FND_API.G_EXC_ERROR;
615 END Insert_oks_header;
616
617
618 --========================================================================
619 -- PROCEDURE : Insert_Contract_Groups PRIVATE
620 -- PARAMETERS:
621 -- COMMENT : This procedure will insert into Okc Contract Groups
622 --=========================================================================
623
624 PROCEDURE Insert_Contract_Groups
625 IS
626 l_stmt_num NUMBER := 0;
627 l_routine CONSTANT VARCHAR2(30) := 'Insert_Contract_Groups';
628 l_int_count NUMBER := 0;
629 l_stg_count NUMBER := 0;
630 BEGIN
631 IF G_PROCEDURE_LOG THEN
632 fnd_log.string(fnd_log.level_procedure,
633 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
634 'Begin');
635 END IF;
636
637 l_stmt_num := 10;
638
639 INSERT ALL
640 WHEN (1=1) THEN
641 INTO OKC_K_GRPINGS
642 (ID
643 ,OBJECT_VERSION_NUMBER
644 ,CGP_PARENT_ID
645 ,INCLUDED_CHR_ID
646 ,SCS_CODE
647 ,CREATED_BY
648 ,CREATION_DATE
649 ,LAST_UPDATED_BY
650 ,LAST_UPDATE_DATE
651 ,LAST_UPDATE_LOGIN )
652 VALUES (OKCGRP_ID
653 ,OBJECT_VERSION_NUMBER
654 ,CONTRACT_GROUP_ID
655 ,INCLUDED_CHR_ID
656 ,CATEGORY
657 ,CREATED_BY
658 ,CREATION_DATE
659 ,LAST_UPDATED_BY
660 ,LAST_UPDATE_DATE
661 ,LAST_UPDATE_LOGIN )
662
663 SELECT okc_p_util.raw_to_number(sys_guid()) OKCGRP_ID
664 ,1 OBJECT_VERSION_NUMBER
665 ,HDRSTG.CONTRACT_GROUP_ID CONTRACT_GROUP_ID
666 ,OKCHDRB.ID INCLUDED_CHR_ID
667 ,HDRSTG.CATEGORY CATEGORY
668 ,FND_GLOBAL.USER_ID CREATED_BY
669 ,SYSDATE CREATION_DATE
670 ,FND_GLOBAL.USER_ID LAST_UPDATED_BY
671 ,SYSDATE LAST_UPDATE_DATE
672 ,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
673
674 FROM OKS_INT_HEADER_STG_TEMP HDRSTG
675 ,OKC_K_HEADERS_ALL_B OKCHDRB
676 WHERE HDRSTG.INTERFACE_STATUS ='S'
677 AND HDRSTG.CONTRACT_NUMBER=OKCHDRB.CONTRACT_NUMBER
678 AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') ;
679
680 IF G_STMT_LOG THEN
681
682 l_int_count := SQL%ROWCOUNT;
683
684 fnd_log.string(fnd_log.level_statement,
685 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
686 'Number of records successfully inserted = ' || l_int_count );
687 END IF;
688
689 IF G_PROCEDURE_LOG THEN
690 fnd_log.string(fnd_log.level_procedure,
691 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
692 'Exit with ' ||
693 'Number of records Inserted = '|| l_int_count);
694 END IF;
695 EXCEPTION
696 WHEN FND_API.G_EXC_ERROR THEN
697 RAISE FND_API.G_EXC_ERROR;
698 WHEN OTHERS THEN
699 FND_MESSAGE.Set_Name('OKS', 'OKS_IMPORT_UNEXPECTED');
700 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
701 FND_MESSAGE.set_token('MESSAGE', 'stmt_num '||l_stmt_num||' ('||SQLCODE||') '||SQLERRM);
702 FND_MSG_PUB.Add;
703 RAISE FND_API.G_EXC_ERROR;
704 END Insert_Contract_Groups;
705
706
707 --========================================================================
708 -- PROCEDURE : Insert_Party_Roles PRIVATE
709 -- PARAMETERS: X_errbuf out Error message buffer
710 -- X_retcode out Return status code
711 -- COMMENT : This procedure will insert into Okc Party Roles
712 --=========================================================================
713
714 PROCEDURE Insert_Party_Roles
715 IS
716
717 l_stmt_num NUMBER := 0;
718 l_routine CONSTANT VARCHAR2(30) := 'Insert_Party_Roles';
719 l_int_count NUMBER := 0;
720 l_stg_count NUMBER := 0;
721 l_msg_data VARCHAR2(2000);
722 l_msg_count NUMBER;
723 l_norecords_exception EXCEPTION;
724 BEGIN
725 IF G_PROCEDURE_LOG THEN
726 fnd_log.string(fnd_log.level_procedure,
727 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
728 'Begin');
729 END IF;
730
731 l_stmt_num := 10;
732
733 INSERT ALL
734 WHEN (OPERATING_UNIT_ID is not null ) THEN
735 INTO OKC_K_PARTY_ROLES_B
736 (ID
737 ,CHR_ID
738 ,CLE_ID
739 ,DNZ_CHR_ID
740 ,RLE_CODE
741 ,OBJECT1_ID1
742 ,OBJECT1_ID2
743 ,JTOT_OBJECT1_CODE
744 ,OBJECT_VERSION_NUMBER
745 ,CREATED_BY
746 ,CREATION_DATE
747 ,LAST_UPDATED_BY
748 ,LAST_UPDATE_DATE)
749 VALUES (okc_p_util.raw_to_number(sys_guid())
750 ,CHR_ID
751 ,CLE_ID
752 ,DNZ_CHR_ID
753 ,RLE_CODE_OPUNIT
754 ,OPERATING_UNIT_ID
755 ,'#'
756 ,'OKX_OPERUNIT'
757 ,OBJECT_VERSION_NUMBER
758 ,CREATED_BY
759 ,CREATION_DATE
760 ,LAST_UPDATED_BY
761 ,LAST_UPDATE_DATE)
762
763 WHEN (CUSTOMER_PARTY_ID IS NOT NULL ) THEN
764 INTO OKC_K_PARTY_ROLES_B
765 (ID
766 ,CHR_ID
767 ,CLE_ID
768 ,DNZ_CHR_ID
769 ,RLE_CODE
770 ,OBJECT1_ID1
771 ,OBJECT1_ID2
772 ,JTOT_OBJECT1_CODE
773 ,OBJECT_VERSION_NUMBER
774 ,CREATED_BY
775 ,CREATION_DATE
776 ,LAST_UPDATED_BY
777 ,LAST_UPDATE_DATE)
778 VALUES (okc_p_util.raw_to_number(sys_guid())
779 ,CHR_ID
780 ,CLE_ID
781 ,DNZ_CHR_ID
782 ,RLE_CODE_CUST
783 ,CUSTOMER_PARTY_ID
784 ,'#'
785 ,'OKX_PARTY'
786 ,OBJECT_VERSION_NUMBER
787 ,CREATED_BY
788 ,CREATION_DATE
789 ,LAST_UPDATED_BY
790 ,LAST_UPDATE_DATE)
791 WHEN (THIRD_PARTY_ID IS NOT NULL ) THEN
792 INTO OKC_K_PARTY_ROLES_B
793 (ID
794 ,CHR_ID
795 ,CLE_ID
796 ,DNZ_CHR_ID
797 ,RLE_CODE
798 ,OBJECT1_ID1
799 ,OBJECT1_ID2
800 ,JTOT_OBJECT1_CODE
801 ,OBJECT_VERSION_NUMBER
802 ,CREATED_BY
803 ,CREATION_DATE
804 ,LAST_UPDATED_BY
805 ,LAST_UPDATE_DATE)
806 VALUES (okc_p_util.raw_to_number(sys_guid())
807 ,CHR_ID
808 ,CLE_ID
809 ,DNZ_CHR_ID
810 ,'THIRD_PARTY'
811 ,THIRD_PARTY_ID
812 ,'#'
813 ,'OKX_PARTY'
814 ,OBJECT_VERSION_NUMBER
815 ,CREATED_BY
816 ,CREATION_DATE
817 ,LAST_UPDATED_BY
818 ,LAST_UPDATE_DATE)
819 SELECT okc_p_util.raw_to_number(sys_guid()) OKCKPTYRL_ID
820 ,OKCHDRB.ID CHR_ID
821 ,null CLE_ID
822 ,OKCHDRB.ID DNZ_CHR_ID
823 ,HDRSTG.OPERATING_UNIT_ID OPERATING_UNIT_ID
824 ,HDRSTG.CUSTOMER_PARTY_ID CUSTOMER_PARTY_ID
825 ,HDRSTG.THIRD_PARTY_ID THIRD_PARTY_ID
826 ,(CASE WHEN HDRSTG.CATEGORY IN ('SERVICE','WARRANTY') THEN 'VENDOR'
827 WHEN HDRSTG.CATEGORY IN ('SUBSCRIPTION') THEN 'MERCHANT'
828 END) RLE_CODE_OPUNIT
829 ,(CASE WHEN HDRSTG.CATEGORY IN ('SERVICE','WARRANTY') THEN 'CUSTOMER'
830 WHEN HDRSTG.CATEGORY IN ('SUBSCRIPTION') THEN 'SUBSCRIBER'
831 END) RLE_CODE_CUST
832 ,1 OBJECT_VERSION_NUMBER
833 ,FND_GLOBAL.USER_ID CREATED_BY
834 ,SYSDATE CREATION_DATE
835 ,FND_GLOBAL.USER_ID LAST_UPDATED_BY
836 ,SYSDATE LAST_UPDATE_DATE
837
838 FROM OKS_INT_HEADER_STG_TEMP HDRSTG
839 ,OKC_K_HEADERS_ALL_B OKCHDRB
840
841 WHERE HDRSTG.INTERFACE_STATUS ='S'
842 AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
843 AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') ;
844
845 IF G_STMT_LOG THEN
846
847 l_int_count := SQL%ROWCOUNT;
848
849 fnd_log.string(fnd_log.level_statement,
850 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
851 'Number of records successfully inserted = ' || l_int_count );
852 END IF;
853
854 l_stmt_num := 40;
855
856 INSERT ALL
857 WHEN (1=1) THEN
858 INTO OKC_K_PARTY_ROLES_B
859 (ID
860 ,CHR_ID
861 ,CLE_ID
862 ,DNZ_CHR_ID
863 ,RLE_CODE
864 ,OBJECT1_ID1
865 ,OBJECT1_ID2
866 ,JTOT_OBJECT1_CODE
867 ,OBJECT_VERSION_NUMBER
868 ,CREATED_BY
869 ,CREATION_DATE
870 ,LAST_UPDATED_BY
871 ,LAST_UPDATE_DATE
872 ,LAST_UPDATE_LOGIN)
873 VALUES (OKCKPTYRL_ID
874 ,CHR_ID
875 ,CLE_ID
876 ,DNZ_CHR_ID
877 ,RLE_CODE
878 ,OBJECT1_ID1
879 ,'#'
880 ,JTOT_OBJECT1_CODE
881 ,OBJECT_VERSION_NUMBER
882 ,CREATED_BY
883 ,CREATION_DATE
884 ,LAST_UPDATED_BY
885 ,LAST_UPDATE_DATE
886 ,LAST_UPDATE_LOGIN)
887 SELECT okc_p_util.raw_to_number(sys_guid()) OKCKPTYRL_ID
888 ,null CHR_ID
889 ,OKCLINB.ID CLE_ID
890 ,OKCHDRB.ID DNZ_CHR_ID
891 ,HDRSTG.CUSTOMER_PARTY_ID OBJECT1_ID1
892 ,(CASE WHEN HDRSTG.CATEGORY IN ('SERVICE','WARRANTY') THEN 'CUSTOMER'
893 WHEN HDRSTG.CATEGORY IN ('SUBSCRIPTION') THEN 'SUBSCRIBER'
894 END) RLE_CODE
895 ,1 OBJECT_VERSION_NUMBER
896 ,'OKX_PARTY' JTOT_OBJECT1_CODE
897 ,FND_GLOBAL.USER_ID CREATED_BY
898 ,SYSDATE CREATION_DATE
899 ,FND_GLOBAL.USER_ID LAST_UPDATED_BY
900 ,SYSDATE LAST_UPDATE_DATE
901 ,USERENV('LANG') LANGUAGE
902 ,USERENV('LANG') SOURCE_LANG
903 ,'N' SFWT_FLAG
904 ,null COGNOMEN
905 ,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
906 FROM OKS_INT_HEADER_STG_TEMP HDRSTG
907 ,OKC_K_HEADERS_ALL_B OKCHDRB
908 ,OKC_K_LINES_B OKCLINB
909 ,OKS_LINES_INTERFACE LININT
910 ,OKS_INT_LINE_STG_TEMP LINSTG
911
912 WHERE HDRSTG.INTERFACE_STATUS ='S'
913 AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
914 AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
915 AND LININT.HEADER_INTERFACE_ID = HDRSTG.HEADER_INTERFACE_ID
916 AND LINSTG.LINE_INTERFACE_ID= LININT.LINE_INTERFACE_ID
917 and OKCLINB.line_number= linstg.line_number
918 AND OKCLINB.CHR_ID =OKCHDRB.ID;
919
920 IF G_STMT_LOG THEN
921
922 l_int_count := SQL%ROWCOUNT;
923
924 fnd_log.string(fnd_log.level_statement,
925 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
926 'Number of records successfully inserted = ' || l_int_count );
927 END IF;
928
929 l_stmt_num := 50;
930
931 INSERT all
932 INTO OKC_K_PARTY_ROLES_TL
933 (ID
934 ,LANGUAGE
935 ,SOURCE_LANG
936 ,SFWT_FLAG
937 ,CREATED_BY
938 ,CREATION_DATE
939 ,LAST_UPDATED_BY
940 ,LAST_UPDATE_DATE
941 ,COGNOMEN
942 ,LAST_UPDATE_LOGIN)
943 VALUES (OKCKPTYRL_ID
944 ,LANGUAGE
945 ,SOURCE_LANG
946 ,SFWT_FLAG
947 ,CREATED_BY
948 ,CREATION_DATE
949 ,LAST_UPDATED_BY
950 ,LAST_UPDATE_DATE
951 ,COGNOMEN
952 ,LAST_UPDATE_LOGIN)
953 SELECT OKCPTY.ID OKCKPTYRL_ID
954 ,USERENV('LANG') LANGUAGE
955 ,USERENV('LANG') SOURCE_LANG
956 ,'N' SFWT_FLAG
957 ,FND_GLOBAL.USER_ID CREATED_BY
958 ,SYSDATE CREATION_DATE
959 ,FND_GLOBAL.USER_ID LAST_UPDATED_BY
960 ,SYSDATE LAST_UPDATE_DATE
961 ,null COGNOMEN
962 ,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
963
964 FROM OKC_K_PARTY_ROLES_B OKCPTY
965 ,OKC_K_HEADERS_ALL_B OKCHDRB
966 ,OKS_INT_HEADER_STG_TEMP HDRSTG
967
968 WHERE HDRSTG.INTERFACE_STATUS ='S'
969 AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
970 AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
971 AND OKCPTY.DNZ_CHR_ID = OKCHDRB.ID;
972
973 IF G_STMT_LOG THEN
974
975 l_int_count := SQL%ROWCOUNT;
976
977 fnd_log.string(fnd_log.level_statement,
978 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
979 'Number of records successfully inserted = ' || l_int_count );
980 END IF;
981
982 IF G_PROCEDURE_LOG THEN
983 fnd_log.string(fnd_log.level_procedure,
984 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
985 'Exit with ' ||
986 'Number of records Inserted = '|| l_int_count);
987 END IF;
988 EXCEPTION
989
990 WHEN FND_API.G_EXC_ERROR THEN
991 RAISE FND_API.G_EXC_ERROR;
992 WHEN OTHERS THEN
993 FND_MESSAGE.Set_Name('OKS', 'OKS_IMPORT_UNEXPECTED');
994 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
995 FND_MESSAGE.set_token('MESSAGE', 'stmt_num '||l_stmt_num||' ('||SQLCODE||') '||SQLERRM);
996 FND_MSG_PUB.Add;
997 RAISE FND_API.G_EXC_ERROR;
998
999 END Insert_Party_Roles;
1000
1001 --========================================================================
1002 -- PROCEDURE : Insert_Contacts PRIVATE
1003 -- PARAMETERS:
1004 -- COMMENT : This procedure will insert into Okc Contacts
1005 --=========================================================================
1006
1007 PROCEDURE Insert_Contacts
1008 IS
1009 l_stmt_num NUMBER := 0;
1010 l_routine CONSTANT VARCHAR2(30) := 'Insert_Contacts';
1011 l_int_count NUMBER := 0;
1012 l_stg_count NUMBER := 0;
1013 BEGIN
1014 IF G_PROCEDURE_LOG THEN
1015 fnd_log.string(fnd_log.level_procedure,
1016 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
1017 'Begin ' );
1018 END IF;
1019
1020 l_stmt_num := 10;
1021
1022 INSERT ALL
1023 WHEN (CUSTOMER_CONTRACT_ADMIN_ID IS NOT NULL ) THEN
1024 INTO OKC_CONTACTS
1025 (ID
1026 ,CPL_ID
1027 ,CRO_CODE
1028 ,DNZ_CHR_ID
1029 ,OBJECT1_ID1
1030 ,OBJECT1_ID2
1031 ,OBJECT_VERSION_NUMBER
1032 ,JTOT_OBJECT1_CODE
1033 ,CREATED_BY
1034 ,CREATION_DATE
1035 ,LAST_UPDATED_BY
1036 ,LAST_UPDATE_DATE)
1037 VALUES (OKCCONTACTS_ID
1038 ,CPL_ID
1039 ,'ADMIN'
1040 ,DNZ_CHR_ID
1041 ,CUSTOMER_CONTRACT_ADMIN_ID
1042 ,'#'
1043 ,OBJECT_VERSION_NUMBER
1044 ,'OKX_PCONTACT'
1045 ,CREATED_BY
1046 ,CREATION_DATE
1047 ,LAST_UPDATED_BY
1048 ,LAST_UPDATE_DATE)
1049 SELECT okc_p_util.raw_to_number(sys_guid()) OKCCONTACTS_ID
1050 ,OKCKPTYRL.ID CPL_ID -- ID of okc_k_party_roles_b table.
1051 ,HDRSTG.CUSTOMER_CONTRACT_ADMIN_ID CUSTOMER_CONTRACT_ADMIN_ID
1052 ,OKCHDRB.ID DNZ_CHR_ID
1053 ,1 OBJECT_VERSION_NUMBER
1054 ,FND_GLOBAL.USER_ID CREATED_BY
1055 ,SYSDATE CREATION_DATE
1056 ,FND_GLOBAL.USER_ID LAST_UPDATED_BY
1057 ,SYSDATE LAST_UPDATE_DATE
1058 FROM OKS_INT_HEADER_STG_TEMP HDRSTG
1059 ,OKC_K_PARTY_ROLES_B OKCKPTYRL
1060 ,OKC_K_HEADERS_ALL_B OKCHDRB
1061 WHERE HDRSTG.INTERFACE_STATUS='S'
1062 AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
1063 AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
1064 AND OKCKPTYRL.DNZ_CHR_ID = OKCHDRB.ID
1065 AND OKCKPTYRL.RLE_CODE IN ('CUSTOMER','SUBSCRIBER')
1066 AND OKCKPTYRL.CLE_ID IS null;
1067
1068 IF G_STMT_LOG THEN
1069 l_int_count := SQL%ROWCOUNT;
1070 fnd_log.string(fnd_log.level_statement,
1071 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
1072 'Number of records successfully inserted = ' || l_int_count );
1073 END IF;
1074
1075 l_stmt_num := 20;
1076
1077 INSERT ALL
1078 WHEN (SALESPERSON_ID IS NOT NULL ) THEN
1079 INTO OKC_CONTACTS
1080 (ID
1081 ,CPL_ID
1082 ,CRO_CODE
1083 ,DNZ_CHR_ID
1084 ,OBJECT1_ID1
1085 ,OBJECT1_ID2
1086 ,OBJECT_VERSION_NUMBER
1087 ,JTOT_OBJECT1_CODE
1088 ,SALES_GROUP_ID
1089 ,CREATED_BY
1090 ,CREATION_DATE
1091 ,LAST_UPDATED_BY
1092 ,LAST_UPDATE_DATE)
1093 VALUES (OKCCONTACTS_ID
1094 ,CPL_ID
1095 ,'SALESPERSON'
1096 ,DNZ_CHR_ID
1097 ,SALESPERSON_ID
1098 ,'#'
1099 ,OBJECT_VERSION_NUMBER
1100 ,'OKX_SALEPERS'
1101 ,SALESGROUP_ID
1102 ,CREATED_BY
1103 ,CREATION_DATE
1104 ,LAST_UPDATED_BY
1105 ,LAST_UPDATE_DATE)
1106
1107 SELECT okc_p_util.raw_to_number(sys_guid()) OKCCONTACTS_ID
1108 ,OKCKPTYRL.ID CPL_ID -- ID of okc_k_party_roles_b table.
1109 ,HDRSTG.SALESPERSON_ID SALESPERSON_ID
1110 ,OKCHDRB.ID DNZ_CHR_ID
1111 ,1 OBJECT_VERSION_NUMBER
1112 ,HDRSTG.SALESGROUP_ID SALESGROUP_ID
1113 ,FND_GLOBAL.USER_ID CREATED_BY
1114 ,SYSDATE CREATION_DATE
1115 ,FND_GLOBAL.USER_ID LAST_UPDATED_BY
1116 ,SYSDATE LAST_UPDATE_DATE
1117 FROM OKS_INT_HEADER_STG_TEMP HDRSTG
1118 ,OKC_K_PARTY_ROLES_B OKCKPTYRL
1119 ,OKC_K_HEADERS_ALL_B OKCHDRB
1120 WHERE HDRSTG.INTERFACE_STATUS='S'
1121 AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
1122 AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
1123 AND OKCKPTYRL.DNZ_CHR_ID = OKCHDRB.ID
1124 AND OKCKPTYRL.RLE_CODE IN ('VENDOR','MERCHANT') ;
1125
1126 IF G_STMT_LOG THEN
1127 l_int_count := SQL%ROWCOUNT;
1128 fnd_log.string(fnd_log.level_statement,
1129 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
1130 'Number of records successfully inserted = ' || l_int_count );
1131 END IF;
1132
1133 l_stmt_num := 25;
1134
1135 INSERT ALL
1136 WHEN (CUSTOMER_BILLING_CONTACT_ID is not null ) THEN
1137 INTO OKC_CONTACTS
1138 (ID
1139 ,CPL_ID
1140 ,CRO_CODE
1141 ,DNZ_CHR_ID
1142 ,OBJECT1_ID1
1143 ,OBJECT1_ID2
1144 ,JTOT_OBJECT1_CODE
1145 ,OBJECT_VERSION_NUMBER
1146 ,CREATED_BY
1147 ,CREATION_DATE
1148 ,LAST_UPDATED_BY
1149 ,LAST_UPDATE_DATE
1150 ,LAST_UPDATE_LOGIN )
1151 VALUES (okc_p_util.raw_to_number(sys_guid())
1152 ,CPL_ID
1153 ,'CUST_BILLING'
1154 ,DNZ_CHR_ID
1155 ,CUSTOMER_BILLING_CONTACT_ID
1156 ,'#'
1157 ,'OKX_CONTBILL'
1158 ,OBJECT_VERSION_NUMBER
1159 ,CREATED_BY
1160 ,CREATION_DATE
1161 ,LAST_UPDATED_BY
1162 ,LAST_UPDATE_DATE
1163 ,LAST_UPDATE_LOGIN )
1164 WHEN (CUSTOMER_SHIPPING_CONTACT_ID is not null ) THEN
1165 INTO OKC_CONTACTS
1166 (ID
1167 ,CPL_ID
1168 ,CRO_CODE
1169 ,DNZ_CHR_ID
1170 ,OBJECT1_ID1
1171 ,OBJECT1_ID2
1172 ,JTOT_OBJECT1_CODE
1173 ,OBJECT_VERSION_NUMBER
1174 ,CREATED_BY
1175 ,CREATION_DATE
1176 ,LAST_UPDATED_BY
1177 ,LAST_UPDATE_DATE
1178 ,LAST_UPDATE_LOGIN )
1179 VALUES (okc_p_util.raw_to_number(sys_guid())
1180 ,CPL_ID
1181 ,'CUST_SHIPPING'
1182 ,DNZ_CHR_ID
1183 ,CUSTOMER_SHIPPING_CONTACT_ID
1184 ,'#'
1185 ,'OKX_CONTSHIP'
1186 ,OBJECT_VERSION_NUMBER
1187 ,CREATED_BY
1188 ,CREATION_DATE
1189 ,LAST_UPDATED_BY
1190 ,LAST_UPDATE_DATE
1191 ,LAST_UPDATE_LOGIN )
1192
1193 SELECT OKCKPTYRL.ID CPL_ID -- okc_party_roles ID column
1194 ,OKCHDRB.ID DNZ_CHR_ID
1195 ,LINSTG.CUSTOMER_BILLING_CONTACT_ID CUSTOMER_BILLING_CONTACT_ID
1196 ,LINSTG.CUSTOMER_SHIPPING_CONTACT_ID CUSTOMER_SHIPPING_CONTACT_ID
1197 ,(CASE WHEN LINSTG.CUSTOMER_BILLING_CONTACT_ID IS NOT NULL THEN 'CUST_BILLING'
1198 WHEN LINSTG.CUSTOMER_SHIPPING_CONTACT_ID IS NOT NULL THEN 'CUST_SHIPPING'
1199 END ) CRO_CODE
1200 ,(CASE WHEN LINSTG.CUSTOMER_BILLING_CONTACT_ID IS NOT NULL THEN LINSTG.CUSTOMER_BILLING_CONTACT_ID
1201 WHEN LINSTG.CUSTOMER_SHIPPING_CONTACT_ID IS NOT NULL THEN LINSTG.CUSTOMER_SHIPPING_CONTACT_ID
1202 END) OBJECT1_ID1
1203 ,(CASE WHEN LINSTG.CUSTOMER_BILLING_CONTACT_ID IS NOT NULL THEN 'OKX_CONTBILL'
1204 WHEN LINSTG.CUSTOMER_SHIPPING_CONTACT_ID IS NOT NULL THEN 'OKX_CONTSHIP'
1205 END ) JTOT_OBJECT1_CODE
1206 ,1 OBJECT_VERSION_NUMBER
1207 ,FND_GLOBAL.USER_ID CREATED_BY
1208 ,SYSDATE CREATION_DATE
1209 ,FND_GLOBAL.USER_ID LAST_UPDATED_BY
1210 ,SYSDATE LAST_UPDATE_DATE
1211 ,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
1212
1213 FROM OKS_INT_LINE_STG_TEMP LINSTG
1214 ,OKS_INT_HEADER_STG_TEMP HDRSTG
1215 ,OKC_K_HEADERS_ALL_B OKCHDRB
1216 ,OKC_K_PARTY_ROLES_B OKCKPTYRL
1217 ,OKC_K_LINES_B OKCLINB
1218
1219 WHERE HDRSTG.INTERFACE_STATUS='S'
1220 AND LINSTG.HEADER_INTERFACE_ID = HDRSTG.HEADER_INTERFACE_ID
1221 AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
1222 AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
1223 AND OKCKPTYRL.DNZ_CHR_ID = OKCHDRB.ID
1224 AND OKCLINB.DNZ_CHR_ID = OKCHDRB.ID
1225 AND OKCLINB.CHR_ID = OKCHDRB.ID
1226 AND OKCLINB.LINE_NUMBER = LINSTG.LINE_NUMBER
1227 AND OKCKPTYRL.CLE_ID = OKCLINB.ID ;
1228
1229
1230 IF G_STMT_LOG THEN
1231 l_int_count := SQL%ROWCOUNT;
1232 fnd_log.string(fnd_log.level_statement,
1233 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
1234 'Number of records successfully inserted = ' || l_int_count );
1235 END IF;
1236
1237 IF G_PROCEDURE_LOG THEN
1238 fnd_log.string(fnd_log.level_procedure,
1239 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
1240 'Exit with ' ||
1241 'Number of records Inserted = '|| l_int_count);
1242 END IF;
1243 EXCEPTION
1244 WHEN FND_API.G_EXC_ERROR THEN
1245 RAISE FND_API.G_EXC_ERROR;
1246 WHEN OTHERS THEN
1247 FND_MESSAGE.Set_Name('OKS', 'OKS_IMPORT_UNEXPECTED');
1248 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
1249 FND_MESSAGE.set_token('MESSAGE', 'stmt_num '||l_stmt_num||' ('||SQLCODE||') '||SQLERRM);
1250 FND_MSG_PUB.Add;
1251 RAISE FND_API.G_EXC_ERROR;
1252 END Insert_Contacts;
1253
1254
1255 --========================================================================
1256 -- PROCEDURE : Insert_Sales_Credits PRIVATE
1257 -- PARAMETERS:
1258 -- COMMENT : This procedure will insert into Okc Contacts
1259 --=========================================================================
1260
1261 PROCEDURE Insert_Sales_Credits
1262 IS
1263 l_stmt_num NUMBER := 0;
1264 l_routine CONSTANT VARCHAR2(30) := 'Insert_Sales_Credits';
1265 l_int_count NUMBER := 0;
1266 l_stg_count NUMBER := 0;
1267 BEGIN
1268 IF G_PROCEDURE_LOG THEN
1269 fnd_log.string(fnd_log.level_procedure,
1270 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
1271 'Entered ' );
1272 END IF;
1273
1274 l_stmt_num := 10;
1275
1276 INSERT ALL
1277 WHEN (1=1) THEN
1278 INTO OKS_K_SALES_CREDITS
1279 (ID
1280 ,PERCENT
1281 ,CHR_ID
1282 ,CLE_ID
1283 ,CTC_ID
1284 ,SALES_CREDIT_TYPE_ID1
1285 ,SALES_CREDIT_TYPE_ID2
1286 ,OBJECT_VERSION_NUMBER
1287 ,CREATED_BY
1288 ,CREATION_DATE
1289 ,LAST_UPDATED_BY
1290 ,LAST_UPDATE_DATE
1291 ,SALES_GROUP_ID)
1292
1293 VALUES (OKCSALESCR_ID
1294 ,PERCENT
1295 ,CHR_ID
1296 ,CLE_ID
1297 ,SALESPERSON_ID
1298 ,SALES_CREDIT_TYPE_ID
1299 ,SALES_CREDIT_TYPE_ID2
1300 ,OBJECT_VERSION_NUMBER
1301 ,CREATED_BY
1302 ,CREATION_DATE
1303 ,LAST_UPDATED_BY
1304 ,LAST_UPDATE_DATE
1305 ,SALESGROUP_ID)
1306
1307 SELECT okc_p_util.raw_to_number(sys_guid()) OKCSALESCR_ID
1308 ,OKSCRSTG.PERCENT PERCENT
1309 ,OKCHDRB.ID CHR_ID
1310 ,null CLE_ID
1311 ,OKSCRSTG.SALESPERSON_ID SALESPERSON_ID
1312 ,OKSCRSTG.SALES_CREDIT_TYPE_ID SALES_CREDIT_TYPE_ID
1313 ,OKSCRSTG.SALES_CREDIT_TYPE_ID SALES_CREDIT_TYPE_ID2 -- need to confirm what could be the value
1314 ,1 OBJECT_VERSION_NUMBER
1315 ,FND_GLOBAL.USER_ID CREATED_BY
1316 ,SYSDATE CREATION_DATE
1317 ,FND_GLOBAL.USER_ID LAST_UPDATED_BY
1318 ,SYSDATE LAST_UPDATE_DATE
1319 ,OKSCRSTG.SALESGROUP_ID SALESGROUP_ID
1320
1321 FROM OKS_INT_SALES_CREDIT_STG_TEMP OKSCRSTG
1322 ,OKS_INT_HEADER_STG_TEMP HDRSTG
1323 ,OKC_K_HEADERS_ALL_B OKCHDRB
1324
1325 WHERE HDRSTG.HEADER_INTERFACE_ID = OKSCRSTG.HEADER_INTERFACE_ID
1326 AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
1327 AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
1328 AND HDRSTG.INTERFACE_STATUS ='S';
1329
1330
1331 l_stmt_num := 20;
1332
1333 INSERT ALL
1334 WHEN (1=1) THEN
1335 INTO OKS_K_SALES_CREDITS
1336 (ID
1337 ,PERCENT
1338 ,CHR_ID
1339 ,CLE_ID
1340 ,CTC_ID
1341 ,SALES_CREDIT_TYPE_ID1
1342 ,SALES_CREDIT_TYPE_ID2
1343 ,OBJECT_VERSION_NUMBER
1344 ,CREATED_BY
1345 ,CREATION_DATE
1346 ,LAST_UPDATED_BY
1347 ,LAST_UPDATE_DATE
1348 ,SALES_GROUP_ID)
1349 VALUES (OKCSALESCR_ID
1350 ,PERCENT
1351 ,CHR_ID
1352 ,CLE_ID
1353 ,SALESPERSON_ID
1354 ,SALES_CREDIT_TYPE_ID
1355 ,SALES_CREDIT_TYPE_ID2
1356 ,OBJECT_VERSION_NUMBER
1357 ,CREATED_BY
1358 ,CREATION_DATE
1359 ,LAST_UPDATED_BY
1360 ,LAST_UPDATE_DATE
1361 ,SALESGROUP_ID
1362 )
1363 SELECT okc_p_util.raw_to_number(sys_guid()) OKCSALESCR_ID
1364 ,OKSCRSTG.PERCENT PERCENT
1365 ,OKCHDRB.ID CHR_ID
1366 ,OKCLINB.ID CLE_ID
1367 ,OKSCRSTG.SALESPERSON_ID SALESPERSON_ID
1368 ,OKSCRSTG.SALES_CREDIT_TYPE_ID SALES_CREDIT_TYPE_ID
1369 ,OKSCRSTG.SALES_CREDIT_TYPE_ID SALES_CREDIT_TYPE_ID2 -- need to confirm what could be the value
1370 ,1 OBJECT_VERSION_NUMBER
1371 ,FND_GLOBAL.USER_ID CREATED_BY
1372 ,SYSDATE CREATION_DATE
1373 ,FND_GLOBAL.USER_ID LAST_UPDATED_BY
1374 ,SYSDATE LAST_UPDATE_DATE
1375 ,OKSCRSTG.SALESGROUP_ID SALESGROUP_ID
1376
1377 FROM OKS_INT_SALES_CREDIT_STG_TEMP OKSCRSTG
1378 ,OKS_INT_HEADER_STG_TEMP HDRSTG
1379 ,OKC_K_HEADERS_ALL_B OKCHDRB
1380 ,OKC_K_LINES_B OKCLINB
1381
1382 WHERE HDRSTG.HEADER_INTERFACE_ID = OKSCRSTG.HEADER_INTERFACE_ID
1383 AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
1384 AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
1385 AND HDRSTG.INTERFACE_STATUS ='S'
1386 AND OKCLINB.DNZ_CHR_ID = OKCHDRB.ID
1387 AND OKCLINB.CHR_ID = OKCHDRB.ID;
1388
1389
1390 IF G_STMT_LOG THEN
1391 l_int_count := SQL%ROWCOUNT;
1392 fnd_log.string(fnd_log.level_statement,
1393 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
1394 'Number of records successfully inserted = ' || l_int_count );
1395 END IF;
1396
1397 IF G_PROCEDURE_LOG THEN
1398 fnd_log.string(fnd_log.level_procedure,
1399 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
1400 'Exit with ' ||
1401 'Number of records Inserted = '|| l_int_count);
1402 END IF;
1403
1404 EXCEPTION
1405 WHEN FND_API.G_EXC_ERROR THEN
1406 RAISE FND_API.G_EXC_ERROR;
1407 WHEN OTHERS THEN
1408 FND_MESSAGE.Set_Name('OKS', 'OKS_IMPORT_UNEXPECTED');
1409 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
1410 FND_MESSAGE.set_token('MESSAGE', 'stmt_num '||l_stmt_num||' ('||SQLCODE||') '||SQLERRM);
1411 FND_MSG_PUB.Add;
1412 RAISE FND_API.G_EXC_ERROR;
1413
1414 END Insert_Sales_Credits;
1415
1416 --========================================================================
1417 -- PROCEDURE : Insert_okc_toplines PRIVATE
1418 -- PARAMETERS:
1419 -- COMMENT : This procedure will insert into Okc Lines
1420 --=========================================================================
1421
1422 PROCEDURE Insert_okc_toplines
1423 IS
1424 l_stmt_num NUMBER := 0;
1425 l_routine CONSTANT VARCHAR2(30) := 'Insert_okc_toplines ';
1426 l_int_count NUMBER := 0;
1427 l_stg_count NUMBER := 0;
1428 BEGIN
1429 IF G_PROCEDURE_LOG THEN
1430 fnd_log.string(fnd_log.level_procedure,
1431 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
1432 'Entering ');
1433 END IF;
1434
1435 l_stmt_num := 10;
1436
1437 INSERT ALL
1438 WHEN (1=1) THEN
1439 INTO OKC_K_LINES_B
1440 (ID
1441 ,LINE_NUMBER
1442 ,CHR_ID
1443 ,CLE_ID
1444 ,DNZ_CHR_ID
1445 ,STS_CODE
1446 ,TRN_CODE
1447 ,LSE_ID
1448 ,PRICE_LEVEL_IND
1449 ,OBJECT_VERSION_NUMBER
1450 ,CREATED_BY
1451 ,CREATION_DATE
1452 ,LAST_UPDATED_BY
1453 ,LAST_UPDATE_DATE
1454 ,CURRENCY_CODE
1455 ,LAST_UPDATE_LOGIN
1456 ,START_DATE
1457 ,END_DATE
1458 ,ATTRIBUTE_CATEGORY
1459 ,ORIG_SYSTEM_SOURCE_CODE
1460 ,ORIG_SYSTEM_ID1
1461 ,ORIG_SYSTEM_REFERENCE1
1462 ,PRICE_LIST_ID
1463 ,CUST_ACCT_ID
1464 ,BILL_TO_SITE_USE_ID
1465 ,INV_RULE_ID
1466 ,LINE_RENEWAL_TYPE_CODE
1467 ,SHIP_TO_SITE_USE_ID
1468 ,DATE_CANCELLED
1469 ,PAYMENT_INSTRUCTION_TYPE
1470 ,CANCELLED_AMOUNT
1471 ,DISPLAY_SEQUENCE
1472 ,EXCEPTION_YN
1473 ,ANNUALIZED_FACTOR
1474 ,PRICE_NEGOTIATED
1475 ,PRICE_UNIT
1476 ,CUST_ACCT_ID)
1477
1478 VALUES (OKCLINB_ID
1479 ,LINE_NUMBER
1480 ,CHR_ID
1481 ,CLE_ID
1482 ,DNZ_CHR_ID
1483 ,STATUS_CODE
1484 ,CANCELLATION_REASON
1485 ,LSE_ID
1486 ,'Y'
1487 ,OBJECT_VERSION_NUMBER
1488 ,CREATED_BY
1489 ,CREATION_DATE
1490 ,LAST_UPDATED_BY
1491 ,LAST_UPDATE_DATE
1492 ,CURRENCY_CODE
1493 ,LAST_UPDATE_LOGIN
1494 ,START_DATE
1495 ,END_DATE
1496 ,CATEGORY
1497 ,ORIG_SYSTEM_SOURCE_CODE
1498 ,ORIG_SYSTEM_ID1
1499 ,ORIG_SYSTEM_REFERENCE1
1500 ,PRICE_LIST_ID
1501 ,BILL_TO_ACCOUNT_ID
1502 ,BILL_TO_SITE_USAGE_ID
1503 ,INVOICING_RULE_ID
1504 ,RENEWAL_TYPE_CODE
1505 ,SHIP_TO_SITE_USAGE_ID
1506 ,CANCELLATION_DATE
1507 ,PAYMENT_INSTRUCTION
1508 ,CANCELLED_AMOUNT
1509 ,1
1510 ,EXCEPTION_YN
1511 ,ANNUALIZED_FACTOR
1512 ,PRICE_NEGOTIATED
1513 ,PRICE_UNIT
1514 ,CUST_ACCT_ID)
1515
1516 SELECT okc_p_util.raw_to_number(sys_guid()) OKCLINB_ID
1517 ,OLSTG.LINE_NUMBER LINE_NUMBER
1518 ,OKCHDRB.ID CHR_ID
1519 , null CLE_ID
1520 ,OKCHDRB.ID DNZ_CHR_ID
1521 ,OLSTG.STATUS_CODE STATUS_CODE
1522 ,OLSTG.CANCELLATION_REASON CANCELLATION_REASON
1523 ,OLSTG.LSE_ID LSE_ID
1524 ,1 OBJECT_VERSION_NUMBER
1525 ,FND_GLOBAL.USER_ID CREATED_BY
1526 ,SYSDATE CREATION_DATE
1527 ,FND_GLOBAL.USER_ID LAST_UPDATED_BY
1528 ,SYSDATE LAST_UPDATE_DATE
1529
1530 ,OHSTG.CONTRACT_CURRENCY_CODE CURRENCY_CODE
1531 ,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
1532 ,OLSTG.START_DATE START_DATE
1533 ,OLSTG.END_DATE END_DATE
1534 ,OHSTG.CATEGORY CATEGORY
1535 ,null ORIG_SYSTEM_SOURCE_CODE
1536 ,null ORIG_SYSTEM_ID1
1537 ,null ORIG_SYSTEM_REFERENCE1
1538 ,OLSTG.PRICE_LIST_ID PRICE_LIST_ID
1539 ,null BILL_TO_ACCOUNT_ID
1540 ,OLSTG.BILL_TO_SITE_USAGE_ID BILL_TO_SITE_USAGE_ID
1541 ,OLSTG.INVOICING_RULE_ID INVOICING_RULE_ID
1542 ,OLSTG.RENEWAL_TYPE_CODE RENEWAL_TYPE_CODE
1543 ,OLSTG.SHIP_TO_SITE_USAGE_ID SHIP_TO_SITE_USAGE_ID
1544 ,OLSTG.CANCELLATION_DATE CANCELLATION_DATE
1545 ,OLSTG.PAYMENT_INSTRUCTION PAYMENT_INSTRUCTION
1546 ,null CANCELLED_AMOUNT -- no matching filed for this in stg table
1547 ,USERENV('LANG') LANGUAGE
1548 ,USERENV('LANG') SOURCE_LANG
1549 ,OLSTG.LINE_REFERENCE COGNOMEN
1550 ,'N' EXCEPTION_YN
1551 ,OLSTG.UNIT_PRICE PRICE_UNIT
1552 ,CSTACCT.CUST_ACCOUNT_ID CUST_ACCT_ID
1553 ,(CASE WHEN OLSTG.LINE_TYPE IN ('SERVICE','WARRANTY','EXT_WARRANTY') THEN INNER_CLVL.SUBTOTAL
1554 WHEN OLSTG.LINE_TYPE ='USAGE' THEN INNER_USG.SUBTOTAL
1555 ELSE OLSTG.SUBTOTAL
1556 END ) PRICE_NEGOTIATED
1557 ,(ADD_MONTHS(OLSTG.START_DATE, (INNER_Q.NYEARS+1)*12) - OLSTG.START_DATE -
1558 DECODE(ADD_MONTHS(OLSTG.END_DATE, -12),( OLSTG.END_DATE-366), 0,
1559 DECODE(ADD_MONTHS(OLSTG.START_DATE, (INNER_Q.NYEARS+1)*12)
1560 - ADD_MONTHS(OLSTG.START_DATE, INNER_Q.NYEARS*12), 366, 1, 0)))
1561 / (INNER_Q.NYEARS+1) /(OLSTG.END_DATE-OLSTG.START_DATE+ 1) ANNUALIZED_FACTOR
1562
1563 FROM OKS_INT_LINE_STG_TEMP OLSTG
1564 ,OKS_INT_HEADER_STG_TEMP OHSTG
1565 ,OKC_K_HEADERS_ALL_B OKCHDRB
1566 ,HZ_CUST_SITE_USES_ALL CSTSITE
1567 ,HZ_CUST_ACCT_SITES_ALL CSTACCT
1568 ,(SELECT trunc(MONTHS_BETWEEN(LINSTG.END_DATE, LINSTG.START_DATE)/12) NYEARS, LINE_INTERFACE_ID
1569 FROM OKS_INT_LINE_STG_TEMP LINSTG
1570 WHERE LINSTG.LSE_ID in (1,12,14,19,46,7,8,9,10,11,13,18,25,35)) INNER_Q
1571
1572 ,(SELECT SUM(CVLINT.SUBTOTAL) AS SUBTOTAL
1573 ,CVLSTG.LINE_INTERFACE_ID
1574 FROM OKS_COVERED_LEVELS_INTERFACE CVLINT
1575 ,OKS_INT_COVERED_LEVEL_STG_TEMP CVLSTG
1576 WHERE CVLINT.COVERED_LEVEL_INTERFACE_ID = CVLSTG.COVERED_LEVEL_INTERFACE_ID
1577 AND cvlint.status_code <>'CANCELLED'
1578 GROUP BY CVLSTG.LINE_INTERFACE_ID) INNER_CLVL
1579 ,(SELECT SUM(USGSTG.SUBTOTAL ) AS SUBTOTAL
1580 ,USGSTG.LINE_INTERFACE_ID
1581 FROM OKS_INT_USAGE_COUNTER_STG_TEMP USGSTG
1582 WHERE USGSTG.STATUS_CODE <> 'CANCELLED'
1583 GROUP BY LINE_INTERFACE_ID )INNER_USG
1584 WHERE OHSTG.HEADER_INTERFACE_ID=OLSTG.HEADER_INTERFACE_ID
1585 AND OLSTG.LINE_INTERFACE_ID =INNER_Q.LINE_INTERFACE_ID
1586 AND INNER_CLVL.LINE_INTERFACE_ID(+) = OLSTG.LINE_INTERFACE_ID
1587 AND INNER_USG.LINE_INTERFACE_ID (+) = OLSTG.LINE_INTERFACE_ID
1588 AND OHSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
1589 AND NVL(CSTSITE.BILL_TO_SITE_USE_ID,CSTSITE.SITE_USE_ID) = OLSTG.BILL_TO_SITE_USAGE_ID
1590 AND CSTSITE.SITE_USE_CODE = 'BILL_TO'
1591 AND CSTSITE.CUST_ACCT_SITE_ID = CSTACCT.CUST_ACCT_SITE_ID
1592 AND NVL(OHSTG.CONTRACT_NUMBER_MODIFIER, 'Xwqwewe@!&*aQ1')= NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER, 'Xwqwewe@!&*aQ1')
1593 AND OHSTG.INTERFACE_STATUS ='S';
1594
1595 IF G_STMT_LOG THEN
1596 l_int_count := SQL%ROWCOUNT;
1597 /* fnd_log.string(fnd_log.level_statement,
1598 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
1599 'Number of records successfully inserted = ' || l_int_count );*/
1600 END IF;
1601
1602 l_stmt_num := 20;
1603
1604 INSERT ALL
1605 WHEN(1=1) THEN
1606 INTO OKC_K_LINES_TL
1607 (ID
1608 ,LANGUAGE
1609 ,SOURCE_LANG
1610 ,CREATED_BY
1611 ,CREATION_DATE
1612 ,LAST_UPDATED_BY
1613 ,LAST_UPDATE_DATE
1614 ,LAST_UPDATE_LOGIN
1615 ,COGNOMEN
1616 ,SFWT_FLAG )
1617 VALUES (OKCLINB_ID
1618 ,LANGUAGE
1619 ,SOURCE_LANG
1620 ,CREATED_BY
1621 ,CREATION_DATE
1622 ,LAST_UPDATED_BY
1623 ,LAST_UPDATE_DATE
1624 ,LAST_UPDATE_LOGIN
1625 ,COGNOMEN
1626 ,'N')
1627 WHEN (1=1) then
1628 INTO OKC_K_ITEMS
1629 (ID
1630 ,CLE_ID
1631 ,CHR_ID
1632 ,DNZ_CHR_ID
1633 ,OBJECT1_ID1
1634 ,OBJECT1_ID2
1635 ,JTOT_OBJECT1_CODE
1636 ,UOM_CODE
1637 ,EXCEPTION_YN
1638 ,NUMBER_OF_ITEMS
1639 ,PRICED_ITEM_YN
1640 ,OBJECT_VERSION_NUMBER
1641 ,CREATED_BY
1642 ,CREATION_DATE
1643 ,LAST_UPDATED_BY
1644 ,LAST_UPDATE_DATE
1645 ,LAST_UPDATE_LOGIN )
1646 VALUES ( OKCKITEM_ID
1647 ,CLE_ID
1648 ,null
1649 ,DNZ_CHR_ID
1650 ,ITEM_ID
1651 ,ITEM_ORGANIZATION_ID
1652 ,JTOT_OBJECT1_CODE
1653 ,UOM_CODE
1654 ,EXCEPTION_YN
1655 ,NUMBER_OF_ITEMS
1656 ,PRICED_ITEM_YN
1657 ,OBJECT_VERSION_NUMBER
1658 ,CREATED_BY
1659 ,CREATION_DATE
1660 ,LAST_UPDATED_BY
1661 ,LAST_UPDATE_DATE
1662 ,LAST_UPDATE_LOGIN)
1663
1664 SELECT OKCLINB.ID OKCLINB_ID
1665 ,USERENV('LANG') LANGUAGE
1666 ,USERENV('LANG') SOURCE_LANG
1667 ,OLSTG.LINE_REFERENCE COGNOMEN
1668 ,FND_GLOBAL.USER_ID CREATED_BY
1669 ,SYSDATE CREATION_DATE
1670 ,FND_GLOBAL.USER_ID LAST_UPDATED_BY
1671 ,SYSDATE LAST_UPDATE_DATE
1672 ,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
1673 ,okc_p_util.raw_to_number(sys_guid()) OKCKITEM_ID
1674 ,OKCLINB.ID CLE_ID
1675 ,OKCHDRB.ID DNZ_CHR_ID
1676 ,null CHR_ID
1677 ,OLSTG.ITEM_ID ITEM_ID
1678 ,OLSTG.ITEM_ORGANIZATION_ID ITEM_ORGANIZATION_ID
1679 ,(CASE WHEN OLSTG.LINE_TYPE IN ('SERVICE','EXT_WARRANTY') THEN 'OKX_SERVICE'
1680 WHEN OLSTG.LINE_TYPE IN ('WARRANTY') THEN 'OKX_WARRANTY'
1681 WHEN OLSTG.LINE_TYPE IN ('USAGE') THEN 'OKX_USAGE'
1682 WHEN OLSTG.LINE_TYPE IN ('SUBSCRIPTION') THEN 'OKS_SUBSCRIPTION'
1683 END) JTOT_OBJECT1_CODE
1684
1685 ,'N' EXCEPTION_YN
1686 /* Commented for Bug#14601764 */
1687 --,OLSTG.billing_interval_period UOM_CODE -- need to confirm wat values can go
1688 /* Added for Bug#14601764 */
1689 ,(CASE WHEN OLSTG.LINE_TYPE IN ('SUBSCRIPTION') THEN OLSTG.quantity_uom
1690 ELSE OLSTG.billing_interval_period
1691 END) UOM_CODE
1692 ,(CASE WHEN OLSTG.LINE_TYPE IN ('SUBSCRIPTION') THEN OLSTG.SUBSCRIPTION_QUANTITY
1693 WHEN OLSTG.LINE_TYPE IN ('SERVICE','EXT_WARRANTY','WARRANTY') THEN TO_NUMBER(olstg.quantity_uom)
1694 END) NUMBER_OF_ITEMS
1695 ,(CASE WHEN OLSTG.LSE_ID IN (1,14,19)
1696 THEN 'N'
1697 ELSE 'Y'
1698 END ) PRICED_ITEM_YN
1699 ,1 OBJECT_VERSION_NUMBER
1700
1701 FROM OKC_K_LINES_B OKCLINB
1702 ,OKS_INT_LINE_STG_TEMP OLSTG
1703 ,OKS_INT_HEADER_STG_TEMP HDRSTG
1704 ,OKC_K_HEADERS_ALL_B OKCHDRB
1705
1706 WHERE OLSTG.HEADER_INTERFACE_ID = HDRSTG.HEADER_INTERFACE_ID
1707 AND HDRSTG.CONTRACT_NUMBER= OKCHDRB.CONTRACT_NUMBER
1708 AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER, 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER, 'Xwqwewe@!&*aQ1')
1709 AND OKCLINB.DNZ_CHR_ID = OKCHDRB.ID
1710 AND OKCLINB.LINE_NUMBER= OLSTG.LINE_NUMBER
1711 AND OKCLINB.CLE_ID IS NULL
1712 AND HDRSTG.INTERFACE_STATUS ='S' ;
1713
1714
1715 /*IF G_PROCEDURE_LOG THEN
1716 fnd_log.string(fnd_log.level_procedure,
1717 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
1718 'Exit with ' ||
1719 'Number of records Inserted = '|| l_int_count);
1720 END IF; */
1721
1722 EXCEPTION
1723 WHEN FND_API.G_EXC_ERROR THEN
1724 RAISE FND_API.G_EXC_ERROR;
1725 WHEN OTHERS THEN
1726 FND_MESSAGE.Set_Name('OKS', 'OKS_IMPORT_UNEXPECTED');
1727 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
1728 FND_MESSAGE.set_token('MESSAGE', 'stmt_num '||l_stmt_num||' ('||SQLCODE||') '||SQLERRM);
1729 FND_MSG_PUB.Add;
1730 RAISE FND_API.G_EXC_ERROR;
1731 END Insert_okc_toplines;
1732
1733
1734 --========================================================================
1735 -- PROCEDURE : Insert_oks_toplines PRIVATE
1736 -- PARAMETERS:
1737 -- COMMENT : This procedure will insert into Oks Lines
1738 --=========================================================================
1739
1740 PROCEDURE Insert_oks_toplines
1741 IS
1742 l_stmt_num NUMBER := 0;
1743 l_routine CONSTANT VARCHAR2(30) := 'Insert_oks_toplines';
1744 l_int_count NUMBER := 0;
1745 l_stg_count NUMBER := 0;
1746 BEGIN
1747 IF G_PROCEDURE_LOG THEN
1748 fnd_log.string(fnd_log.level_procedure,
1749 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
1750 'Entering ');
1751 END IF;
1752
1753 l_stmt_num := 10;
1754
1755 INSERT ALL
1756 WHEN (1=1) THEN
1757 INTO OKS_K_LINES_B
1758 (ID
1759 ,CLE_ID
1760 ,DNZ_CHR_ID
1761 ,ACCT_RULE_ID
1762 ,PAYMENT_TYPE
1763 ,COMMITMENT_ID
1764 ,CUST_PO_NUMBER_REQ_YN
1765 ,CUST_PO_NUMBER
1766 ,INV_PRINT_FLAG
1767 ,TAX_STATUS
1768 ,PRICE_UOM
1769 ,USAGE_PERIOD
1770 ,USAGE_TYPE
1771 ,uom_quantified
1772 ,CREATED_BY
1773 ,CREATION_DATE
1774 ,LAST_UPDATED_BY
1775 ,LAST_UPDATE_DATE
1776 ,LAST_UPDATE_LOGIN
1777 ,TAX_STATUS
1778 ,TAX_AMOUNT
1779 ,TAX_CLASSIFICATION_CODE
1780 ,TAX_EXEMPTION_ID
1781 ,EXEMPT_REASON_CODE
1782 ,COVERAGE_ID
1783 ,STANDARD_COV_YN
1784 ,BILLING_SCHEDULE_TYPE
1785 ,ORIG_SYSTEM_ID1
1786 ,ORIG_SYSTEM_REFERENCE1
1787 ,ORIG_SYSTEM_SOURCE_CODE
1788 ,AVERAGING_INTERVAL
1789 ,SETTLEMENT_INTERVAL
1790 ,TERMN_METHOD
1791 ,OBJECT_VERSION_NUMBER
1792 ,pm_program_id
1793 ,pm_sch_exists_yn
1794 ,pm_conf_req_yn)
1795 VALUES (OKSLINB_ID
1796 ,cle_id
1797 ,DNZ_CHR_ID
1798 ,ACCOUNTING_RULE_ID
1799 ,PAYMENT_METHOD_CODE
1800 ,COMMITMENT_ID
1801 ,PO_REQUIRED
1802 ,PAYMENT_INSTRUCTION_DETAILS
1803 ,PRINT_INVOICE
1804 ,TAX_EXEMPTION_CONTROL
1805 ,PRICE_UOM
1806 ,USAGE_PERIOD
1807 ,USAGE_TYPE
1808 ,uom_quantified
1809 ,CREATED_BY
1810 ,CREATION_DATE
1811 ,LAST_UPDATED_BY
1812 ,LAST_UPDATE_DATE
1813 ,LAST_UPDATE_LOGIN
1814 ,TAX_EXEMPTION_CONTROL
1815 ,TAX_AMOUNT
1816 ,TAX_CLASSIFICATION_CODE
1817 ,TAX_EXEMPTION_NUMBER
1818 ,EXEMPT_REASON_CODE
1819 ,COVERAGE_ID
1820 ,STANDARD_COV_YN
1821 ,BILLING_SCHEDULE_TYPE
1822 ,ORIG_SYSTEM_ID1
1823 ,ORIG_SYSTEM_REFERENCE1
1824 ,ORIG_SYSTEM_SOURCE_CODE
1825 ,AVERAGING_INTERVAL
1826 ,SETTLEMENT_INTERVAL
1827 ,TERMN_METHOD
1828 ,1
1829 ,pm_program_id
1830 ,pm_sch_exists_yn
1831 ,pm_conf_req_yn)
1832 SELECT okc_p_util.raw_to_number(sys_guid()) OKSLINB_ID
1833 ,OKCLINB.ID CLE_ID
1834 ,OKCHDRB.ID DNZ_CHR_ID
1835 ,OLSTG.ACCOUNTING_RULE_ID ACCOUNTING_RULE_ID
1836 ,OLSTG.PAYMENT_METHOD_CODE PAYMENT_METHOD_CODE
1837 ,OLSTG.COMMITMENT_ID COMMITMENT_ID
1838 ,OLSTG.PO_REQUIRED PO_REQUIRED
1839 ,OLSTG.PAYMENT_INSTRUCTION_DETAILS PAYMENT_INSTRUCTION_DETAILS
1840 ,OLSTG.PRINT_INVOICE PRINT_INVOICE
1841 ,OLSTG.TAX_EXEMPTION_CONTROL TAX_EXEMPTION_CONTROL
1842 ,FND_GLOBAL.USER_ID CREATED_BY
1843 ,SYSDATE CREATION_DATE
1844 ,FND_GLOBAL.USER_ID LAST_UPDATED_BY
1845 ,SYSDATE LAST_UPDATE_DATE
1846 ,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
1847 ,OLSTG.TAX_CLASSIFICATION_CODE TAX_CLASSIFICATION_CODE
1848 ,OLSTG.TAX_EXEMPTION_NUMBER TAX_EXEMPTION_NUMBER
1849 ,OLSTG.EXEMPT_REASON_CODE EXEMPT_REASON_CODE
1850 ,OLSTG.REFERENCE_TEMPLATE_ID COVERAGE_ID
1851 ,null ORIG_SYSTEM_ID1
1852 ,null ORIG_SYSTEM_REFERENCE1
1853 ,null ORIG_SYSTEM_SOURCE_CODE
1854 ,USERENV('LANG') LANGUAGE
1855 ,USERENV('LANG') SOURCE_LANG
1856 ,OLSTG.INVOICE_TEXT INVOICE_TEXT
1857 ,OLSTG.PRICE_UOM PRICE_UOM
1858 ,OLSTG.USAGE_PERIOD USAGE_PERIOD
1859 ,OLSTG.USAGE_TYPE USAGE_TYPE
1860 ,olstg.quantity_uom uom_quantified
1861 ,'Y' STANDARD_COV_YN
1862 ,(CASE WHEN OLSTG.LINE_TYPE='SUBSCRIPTION' THEN 'E'
1863 ELSE 'T'
1864 END ) BILLING_SCHEDULE_TYPE
1865 ,OLSTG.AVERAGING_INTERVAL AVERAGING_INTERVAL
1866 ,OLSTG.SETTLEMENT_INTERVAL SETTLEMENT_INTERVAL
1867 ,OLSTG.USAGE_TERMINATION_METHOD TERMN_METHOD
1868 ,(CASE WHEN OLSTG.LINE_TYPE IN('SERVICE','WARRANTY','EXT_WARRANTY') THEN INNER_CLVL.TAX_AMOUNT
1869 WHEN OLSTG.LINE_TYPE ='USAGE' THEN INNER_USG.TAX_AMOUNT
1870 ELSE OLSTG.TAX_AMOUNT
1871 END ) TAX_AMOUNT
1872 ,COV_IN_QUERY.PM_PROGRAM_ID PM_PROGRAM_ID
1873 ,COV_IN_QUERY.PM_SCH_EXISTS_YN PM_SCH_EXISTS_YN
1874 ,COV_IN_QUERY.PM_CONF_REQ_YN PM_CONF_REQ_YN
1875 FROM OKS_INT_LINE_STG_TEMP OLSTG
1876 ,OKS_INT_HEADER_STG_TEMP HDRSTG
1877 ,OKC_K_HEADERS_ALL_B OKCHDRB
1878 ,OKC_K_LINES_B OKCLINB
1879 ,(SELECT SUM(CVLINT.TAX_AMOUNT) AS TAX_AMOUNT
1880 ,CVLSTG.LINE_INTERFACE_ID
1881 FROM OKS_COVERED_LEVELS_INTERFACE CVLINT , OKS_INT_COVERED_LEVEL_STG_TEMP CVLSTG
1882 WHERE CVLINT.COVERED_LEVEL_INTERFACE_ID = CVLSTG.COVERED_LEVEL_INTERFACE_ID
1883 AND CVLINT.STATUS_CODE <>'CANCELLED'
1884 GROUP BY CVLSTG.LINE_INTERFACE_ID) INNER_CLVL
1885 ,(SELECT SUM(USGSTG.TAX_AMOUNT) AS TAX_AMOUNT
1886 ,USGSTG.LINE_INTERFACE_ID
1887 FROM OKS_INT_USAGE_COUNTER_STG_TEMP USGSTG
1888 WHERE USGSTG.STATUS_CODE <> 'CANCELLED'
1889 GROUP BY LINE_INTERFACE_ID )INNER_USG
1890 ,(SELECT OKSLB.PM_PROGRAM_ID AS PM_PROGRAM_ID
1891 ,OKSLB.PM_SCH_EXISTS_YN AS PM_SCH_EXISTS_YN
1892 ,OKSLB.PM_CONF_REQ_YN AS PM_CONF_REQ_YN
1893 ,LIN.LINE_INTERFACE_ID
1894 ,LIN.LINE_NUMBER
1895 FROM OKS_K_LINES_B OKSLB
1896 , OKS_INT_LINE_STG_TEMP LIN
1897 WHERE OKSLB.CLE_ID = LIN.REFERENCE_TEMPLATE_ID) COV_IN_QUERY
1898 WHERE OLSTG.HEADER_INTERFACE_ID = HDRSTG.HEADER_INTERFACE_ID
1899 AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
1900 AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
1901 AND OKCLINB.DNZ_CHR_ID = OKCHDRB.ID
1902 AND COV_IN_QUERY.LINE_INTERFACE_ID(+) = OLSTG.LINE_INTERFACE_ID
1903 AND OLSTG.LINE_NUMBER = COV_IN_QUERY.LINE_NUMBER(+)
1904 AND INNER_CLVL.LINE_INTERFACE_ID(+) = OLSTG.LINE_INTERFACE_ID
1905 AND INNER_USG.LINE_INTERFACE_ID (+) = OLSTG.LINE_INTERFACE_ID
1906 AND OKCLINB.LINE_NUMBER = OLSTG.LINE_NUMBER
1907 AND HDRSTG.INTERFACE_STATUS ='S'
1908 AND OKCLINB.CLE_ID IS NULL;
1909
1910 IF G_STMT_LOG THEN
1911 l_int_count := SQL%ROWCOUNT;
1912 /*fnd_log.string(fnd_log.level_statement,
1913 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
1914 'Number of records successfully inserted = ' || l_int_count ); */
1915 END IF;
1916
1917 l_stmt_num := 20;
1918
1919 INSERT ALL
1920 WHEN (1=1) THEN
1921 INTO OKS_K_LINES_TL
1922 (ID
1923 ,LANGUAGE
1924 ,SOURCE_LANG
1925 ,INVOICE_TEXT
1926 ,CREATED_BY
1927 ,CREATION_DATE
1928 ,LAST_UPDATED_BY
1929 ,LAST_UPDATE_DATE
1930 ,LAST_UPDATE_LOGIN
1931 ,SFWT_FLAG)
1932 VALUES (OKSLINB_ID
1933 ,LANGUAGE
1934 ,SOURCE_LANG
1935 ,INVOICE_TEXT
1936 ,CREATED_BY
1937 ,CREATION_DATE
1938 ,LAST_UPDATED_BY
1939 ,LAST_UPDATE_DATE
1940 ,LAST_UPDATE_LOGIN
1941 ,'N')
1942
1943 SELECT OKSLINB.ID OKSLINB_ID
1944 ,USERENV('LANG') LANGUAGE
1945 ,USERENV('LANG') SOURCE_LANG
1946 ,FND_GLOBAL.USER_ID CREATED_BY
1947 ,SYSDATE CREATION_DATE
1948 ,FND_GLOBAL.USER_ID LAST_UPDATED_BY
1949 ,SYSDATE LAST_UPDATE_DATE
1950 ,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
1951 ,OLSTG.INVOICE_TEXT INVOICE_TEXT
1952 FROM OKS_INT_LINE_STG_TEMP OLSTG
1953 ,OKS_INT_HEADER_STG_TEMP HDRSTG
1954 ,OKC_K_HEADERS_ALL_B OKCHDRB
1955 ,OKS_K_LINES_B OKSLINB
1956 ,OKC_K_LINES_B OKCLINB
1957
1958 WHERE OLSTG.HEADER_INTERFACE_ID = HDRSTG.HEADER_INTERFACE_ID
1959 AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
1960 AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
1961 AND HDRSTG.INTERFACE_STATUS ='S'
1962 AND OKSLINB.DNZ_CHR_ID = OKCHDRB.ID
1963 AND OKCLINB.DNZ_CHR_ID = OKCHDRB.ID
1964 AND OKCLINB.LINE_NUMBER =OLSTG.LINE_NUMBER
1965 AND OKSLINB.CLE_ID=OKCLINB.ID
1966 AND OKCLINB.CLE_ID IS NULL;
1967
1968 l_stmt_num := 30;
1969
1970 INSERT ALL
1971 WHEN (1=1) THEN
1972 INTO OKS_SUBSCR_HEADER_B
1973 (ID
1974 ,CLE_ID
1975 ,DNZ_CHR_ID
1976 ,INSTANCE_ID
1977 ,SUBSCRIPTION_TYPE
1978 ,ITEM_TYPE
1979 ,MEDIA_TYPE
1980 ,STATUS
1981 ,FREQUENCY
1982 ,FULFILLMENT_CHANNEL
1983 ,OFFSET
1984 ,OBJECT_VERSION_NUMBER
1985 ,CREATED_BY
1986 ,CREATION_DATE
1987 ,LAST_UPDATED_BY
1988 ,LAST_UPDATE_DATE
1989 ,LAST_UPDATE_LOGIN )
1990 VALUES (SUB_HDR_ID
1991 ,CLE_ID
1992 ,DNZ_CHR_ID
1993 ,INSTANCE_ID
1994 ,SUBSCRIPTION_TYPE
1995 ,ITEM_TYPE
1996 ,MEDIA_TYPE
1997 ,STATUS
1998 ,FREQUENCY
1999 ,FULFILLMENT_CHANNEL
2000 ,OFFSET
2001 ,OBJECT_VERSION_NUMBER
2002 ,CREATED_BY
2003 ,CREATION_DATE
2004 ,LAST_UPDATED_BY
2005 ,LAST_UPDATE_DATE
2006 ,LAST_UPDATE_LOGIN)
2007
2008 SELECT okc_p_util.raw_to_number(sys_guid()) SUB_HDR_ID
2009 ,OKCLINB.ID CLE_ID
2010 ,OKCHDRB.ID DNZ_CHR_ID
2011 ,SUB_HDR.INSTANCE_ID INSTANCE_ID
2012 ,SUB_HDR.SUBSCRIPTION_TYPE SUBSCRIPTION_TYPE
2013 ,SUB_HDR.ITEM_TYPE ITEM_TYPE
2014 ,SUB_HDR.MEDIA_TYPE MEDIA_TYPE
2015 ,SUB_HDR.STATUS STATUS
2016 ,SUB_HDR.FREQUENCY FREQUENCY
2017 ,SUB_HDR.FULFILLMENT_CHANNEL FULFILLMENT_CHANNEL
2018 ,SUB_HDR.OFFSET OFFSET
2019 ,1 OBJECT_VERSION_NUMBER
2020 ,FND_GLOBAL.USER_ID CREATED_BY
2021 ,SYSDATE CREATION_DATE
2022 ,FND_GLOBAL.USER_ID LAST_UPDATED_BY
2023 ,SYSDATE LAST_UPDATE_DATE
2024 ,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
2025 FROM OKC_K_LINES_B OKCLINB
2026 ,OKS_INT_LINE_STG_TEMP OLSTG
2027 ,OKS_INT_HEADER_STG_TEMP HDRSTG
2028 ,OKS_SUBSCR_HEADER_B SUB_HDR
2029 ,OKC_K_HEADERS_ALL_B OKCHDRB
2030 WHERE OLSTG.HEADER_INTERFACE_ID = HDRSTG.HEADER_INTERFACE_ID
2031 AND HDRSTG.INTERFACE_STATUS ='S'
2032 AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
2033 AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
2034 AND OKCLINB.DNZ_CHR_ID = OKCHDRB.ID
2035 AND OKCLINB.LINE_NUMBER =OLSTG.LINE_NUMBER
2036 AND OKCLINB.CLE_ID IS NULL
2037 AND OLSTG.LINE_TYPE ='SUBSCRIPTION'
2038 AND SUB_HDR.ID = OLSTG.REFERENCE_TEMPLATE_ID;
2039
2040 l_stmt_num :=40;
2041
2042 INSERT ALL
2043 WHEN (1=1) THEN
2044 INTO OKS_SUBSCR_HEADER_TL
2045 (ID
2046 ,NAME
2047 ,DESCRIPTION
2048 ,LANGUAGE
2049 ,SOURCE_LANG
2050 ,SFWT_FLAG
2051 ,COMMENTS
2052 ,CREATED_BY
2053 ,CREATION_DATE
2054 ,LAST_UPDATED_BY
2055 ,LAST_UPDATE_DATE
2056 ,LAST_UPDATE_LOGIN )
2057 VALUES ( TL_ID
2058 ,NAME
2059 ,DESCRIPTION
2060 ,LANGUAGE
2061 ,SOURCE_LANG
2062 ,SFWT_FLAG
2063 ,COMMENTS
2064 ,CREATED_BY
2065 ,CREATION_DATE
2066 ,LAST_UPDATED_BY
2067 ,LAST_UPDATE_DATE
2068 ,LAST_UPDATE_LOGIN)
2069
2070 SELECT SUB_HDR_2.ID TL_ID
2071 ,SUB_HDR_TL.NAME NAME
2072 ,SUB_HDR_TL.DESCRIPTION DESCRIPTION
2073 ,USERENV('LANG') LANGUAGE
2074 ,USERENV('LANG') SOURCE_LANG
2075 ,'N' SFWT_FLAG
2076 ,SUB_HDR_TL.COMMENTS COMMENTS
2077 ,FND_GLOBAL.USER_ID CREATED_BY
2078 ,SYSDATE CREATION_DATE
2079 ,FND_GLOBAL.USER_ID LAST_UPDATED_BY
2080 ,SYSDATE LAST_UPDATE_DATE
2081 ,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
2082
2083 FROM OKC_K_LINES_B OKCLINB
2084 ,OKS_INT_LINE_STG_TEMP OLSTG
2085 ,OKS_INT_HEADER_STG_TEMP HDRSTG
2086 ,OKS_SUBSCR_HEADER_B SUB_HDR_1
2087 ,OKS_SUBSCR_HEADER_B SUB_HDR_2
2088 ,OKS_SUBSCR_HEADER_TL SUB_HDR_TL
2089 ,OKC_K_HEADERS_ALL_B OKCHDRB
2090 WHERE OLSTG.HEADER_INTERFACE_ID = HDRSTG.HEADER_INTERFACE_ID
2091 AND HDRSTG.INTERFACE_STATUS ='S'
2092 AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
2093 AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
2094 AND OKCLINB.DNZ_CHR_ID = OKCHDRB.ID
2095 AND OKCLINB.LINE_NUMBER =OLSTG.LINE_NUMBER
2096 AND OKCLINB.CLE_ID IS NULL
2097 AND OLSTG.LINE_TYPE ='SUBSCRIPTION'
2098 AND SUB_HDR_1.ID = OLSTG.REFERENCE_TEMPLATE_ID
2099 AND SUB_HDR_1.ID = SUB_HDR_TL.ID
2100 AND SUB_HDR_2.DNZ_CHR_ID = OKCHDRB.ID
2101 AND SUB_HDR_2.CLE_ID = OKCLINB.ID
2102 AND SUB_HDR_TL.LANGUAGE = USERENV('LANG');
2103
2104 IF G_STMT_LOG THEN
2105 l_int_count := SQL%ROWCOUNT;
2106 fnd_log.string(fnd_log.level_statement,
2107 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
2108 'Number of records successfully inserted = ' || l_int_count );
2109 END IF;
2110
2111 IF G_PROCEDURE_LOG THEN
2112 fnd_log.string(fnd_log.level_procedure,
2113 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
2114 'Exit with ' ||
2115 'Number of records Inserted = '|| l_int_count);
2116 END IF;
2117 EXCEPTION
2118 WHEN FND_API.G_EXC_ERROR THEN
2119 RAISE FND_API.G_EXC_ERROR;
2120 WHEN OTHERS THEN
2121 FND_MESSAGE.Set_Name('OKS', 'OKS_IMPORT_UNEXPECTED');
2122 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
2123 FND_MESSAGE.set_token('MESSAGE', 'stmt_num '||l_stmt_num||' ('||SQLCODE||') '||SQLERRM);
2124 FND_MSG_PUB.Add;
2125 RAISE FND_API.G_EXC_ERROR;
2126 END Insert_oks_toplines;
2127
2128
2129 --========================================================================
2130 -- PROCEDURE : Insert_covlevel_and_usgcounter PRIVATE
2131 -- PARAMETERS:
2132 -- COMMENT : This procedure will insert sublines for a contract
2133 --=========================================================================
2134
2135 PROCEDURE Insert_covlevel_and_usgcounter
2136 IS
2137 l_stmt_num NUMBER := 0;
2138 l_routine CONSTANT VARCHAR2(30) := 'Insert_covlevel_and_usgcounter';
2139 l_int_count NUMBER := 0;
2140 l_stg_count NUMBER := 0;
2141 BEGIN
2142 IF G_PROCEDURE_LOG THEN
2143 fnd_log.string(fnd_log.level_procedure,
2144 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
2145 'Entering ');
2146 END IF;
2147
2148 /* Insert query for Sublines - Covered levels */
2149 l_stmt_num := 10;
2150
2151 INSERT ALL
2152 WHEN (1=1) THEN
2153 INTO OKC_K_LINES_B
2154 (ID
2155 ,CLE_ID
2156 ,LINE_NUMBER
2157 ,DNZ_CHR_ID
2158 ,STS_CODE
2159 ,LSE_ID
2160 ,OBJECT_VERSION_NUMBER
2161 ,CREATED_BY
2162 ,CREATION_DATE
2163 ,LAST_UPDATED_BY
2164 ,LAST_UPDATE_DATE
2165 ,PRICE_NEGOTIATED
2166 ,PRICE_UNIT
2167 ,CURRENCY_CODE
2168 ,START_DATE
2169 ,END_DATE
2170 ,ORIG_SYSTEM_SOURCE_CODE
2171 ,ORIG_SYSTEM_ID1
2172 ,ORIG_SYSTEM_REFERENCE1
2173 ,LINE_RENEWAL_TYPE_CODE
2174 ,DATE_CANCELLED
2175 ,TRN_CODE
2176 ,DISPLAY_SEQUENCE
2177 ,EXCEPTION_YN
2178 ,ANNUALIZED_FACTOR
2179 ,PRICE_LEVEL_IND)
2180 VALUES (OKCLINB_ID
2181 ,CLE_ID
2182 ,LINE_NUMBER
2183 ,DNZ_CHR_ID
2184 ,STATUS_CODE
2185 ,LSE_ID
2186 ,OBJECT_VERSION_NUMBER
2187 ,CREATED_BY
2188 ,CREATION_DATE
2189 ,LAST_UPDATED_BY
2190 ,LAST_UPDATE_DATE
2191 ,SUBTOTAL
2192 ,PRICE_UNIT
2193 ,CURRENCY_CODE
2194 ,START_DATE
2195 ,END_DATE
2196 ,ORIG_SYSTEM_SOURCE_CODE
2197 ,ORIG_SYSTEM_ID1
2198 ,ORIG_SYSTEM_REFERENCE1
2199 ,RENEWAL_TYPE_CODE
2200 ,CANCELLATION_DATE
2201 ,CANCELLATION_REASON
2202 ,2
2203 ,'N'
2204 ,ANNUALIZED_FACTOR
2205 ,PRICE_LEVEL_IND)
2206 SELECT okc_p_util.raw_to_number(sys_guid()) OKCLINB_ID
2207 ,OKCLINB.ID CLE_ID
2208 ,OCLI.LINE_NUMBER LINE_NUMBER
2209 ,OKCHDRB.ID DNZ_CHR_ID
2210 ,null CHR_ID
2211 ,OCLI.STATUS_CODE STATUS_CODE
2212 ,(CASE WHEN OCLVLSTG.COVERED_INSTANCE_ID IS NOT NULL AND OKCLINB.lse_id=1 THEN 9 /*skuchima bug 16468231 */
2213 WHEN OCLVLSTG.COVERED_INSTANCE_ID IS NOT NULL AND OKCLINB.lse_id=14 THEN 18
2214 WHEN OCLVLSTG.COVERED_INSTANCE_ID IS NOT NULL AND OKCLINB.lse_id=19 THEN 25
2215 WHEN OCLVLSTG.COVERED_ACCOUNT_ID IS NOT NULL THEN 35
2216 WHEN OCLVLSTG.COVERED_ITEM_ID IS NOT NULL THEN 7
2217 WHEN OCLVLSTG.COVERED_PARTY_ID IS NOT NULL THEN 8
2218 WHEN OCLVLSTG.COVERED_SITE_ID IS NOT NULL THEN 10
2219 WHEN OCLVLSTG.COVERED_SYSTEM_ID IS NOT NULL THEN 11
2220 END) LSE_ID
2221 ,HDRSTG.CONTRACT_CURRENCY_CODE CURRENCY_CODE
2222 ,1 OBJECT_VERSION_NUMBER
2223 ,FND_GLOBAL.USER_ID CREATED_BY
2224 ,SYSDATE CREATION_DATE
2225 ,FND_GLOBAL.USER_ID LAST_UPDATED_BY
2226 ,SYSDATE LAST_UPDATE_DATE
2227 ,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
2228 ,OCLI.SUBTOTAL SUBTOTAL
2229 ,(CASE WHEN OCLI.SUBTOTAL IS NOT NULL AND OCLI.SUBTOTAL > 0
2230 AND OCLI.QUANTITY_COVERED IS NOT NULL AND OCLI.QUANTITY_COVERED > 0
2231 THEN OCLI.SUBTOTAL/OCLI.QUANTITY_COVERED
2232 ELSE NULL
2233 END) PRICE_UNIT --Imports program doesn't derive any value, for any column, so commented this part of the code.
2234 ,OCLI.START_DATE START_DATE
2235 ,OCLI.END_DATE END_DATE
2236 ,null ORIG_SYSTEM_SOURCE_CODE
2237 ,null ORIG_SYSTEM_ID1
2238 ,null ORIG_SYSTEM_REFERENCE1
2239 ,OCLI.RENEWAL_TYPE_CODE RENEWAL_TYPE_CODE
2240 ,OCLI.CANCELLATION_DATE CANCELLATION_DATE
2241 ,OCLI.CANCELLATION_REASON CANCELLATION_REASON
2242 ,(ADD_MONTHS(OKSLINSTG.START_DATE, (INNER_Q.NYEARS+1)*12) - OKSLINSTG.START_DATE -
2243 DECODE(ADD_MONTHS(OKSLINSTG.END_DATE, -12),(OKSLINSTG.END_DATE-366), 0,
2244 DECODE(ADD_MONTHS(OKSLINSTG.START_DATE, (INNER_Q.NYEARS+1)*12)
2245 - ADD_MONTHS(OKSLINSTG.START_DATE, INNER_Q.NYEARS*12), 366, 1, 0)))
2246 / (INNER_Q.NYEARS+1) /(OKSLINSTG.END_DATE - OKSLINSTG.START_DATE+ 1) ANNUALIZED_FACTOR
2247 ,'Y' PRICE_LEVEL_IND -- defaulted
2248
2249 FROM OKS_COVERED_LEVELS_INTERFACE OCLI
2250 ,OKS_INT_COVERED_LEVEL_STG_TEMP OCLVLSTG
2251 ,OKC_K_HEADERS_ALL_B OKCHDRB
2252 ,OKC_K_LINES_B OKCLINB
2253 ,OKS_INT_HEADER_STG_TEMP HDRSTG
2254 ,OKS_INT_LINE_STG_TEMP OKSLINSTG
2255 ,(SELECT trunc(MONTHS_BETWEEN(LINSTG.END_DATE, LINSTG.START_DATE)/12) NYEARS, COVERED_LEVEL_INTERFACE_ID
2256 FROM OKS_INT_COVERED_LEVEL_STG_TEMP COVSTG
2257 ,OKS_INT_LINE_STG_TEMP LINSTG
2258 WHERE COVSTG.LINE_INTERFACE_ID = LINSTG.LINE_INTERFACE_ID
2259 AND LINSTG.LSE_ID in (1,12,14,19,46,7,8,9,10,11,13,18,25,35)) INNER_Q
2260
2261 WHERE OCLI.COVERED_LEVEL_INTERFACE_ID = OCLVLSTG.COVERED_LEVEL_INTERFACE_ID
2262 AND OCLVLSTG.COVERED_LEVEL_INTERFACE_ID = INNER_Q.COVERED_LEVEL_INTERFACE_ID
2263 AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
2264 AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
2265 AND OCLI.LINE_INTERFACE_ID = OKSLINSTG.LINE_INTERFACE_ID
2266 AND OKSLINSTG.HEADER_INTERFACE_ID = HDRSTG.HEADER_INTERFACE_ID
2267 AND OKCLINB.CHR_ID = OKCHDRB.ID
2268 AND HDRSTG.INTERFACE_STATUS ='S'
2269 AND OKCLINB.LINE_NUMBER = OKSLINSTG.LINE_NUMBER;
2270
2271 l_stmt_num := 20;
2272
2273 INSERT ALL
2274 WHEN (1=1) THEN
2275 INTO OKS_K_LINES_B
2276 (ID
2277 ,CLE_ID
2278 ,DNZ_CHR_ID
2279 ,INV_PRINT_FLAG
2280 ,PRICE_UOM
2281 ,TAX_AMOUNT
2282 ,BILLING_SCHEDULE_TYPE
2283 ,OBJECT_VERSION_NUMBER
2284 ,CREATED_BY
2285 ,CREATION_DATE
2286 ,LAST_UPDATED_BY
2287 ,LAST_UPDATE_DATE
2288 ,LAST_UPDATE_LOGIN)
2289 VALUES (OKSLINB_ID
2290 ,CLE_ID
2291 ,DNZ_CHR_ID
2292 ,PRINT_INVOICE
2293 ,PRICE_UOM
2294 ,TAX_AMOUNT
2295 ,BILLING_SCHEDULE_TYPE
2296 ,OBJECT_VERSION_NUMBER
2297 ,CREATED_BY
2298 ,CREATION_DATE
2299 ,LAST_UPDATED_BY
2300 ,LAST_UPDATE_DATE
2301 ,LAST_UPDATE_LOGIN)
2302 SELECT okc_p_util.raw_to_number(sys_guid()) OKSLINB_ID
2303 ,OKCLINB_subline.ID CLE_ID
2304 ,OKCHDRB.ID DNZ_CHR_ID
2305 ,null CHR_ID
2306 ,OCLI.PRINT_INVOICE PRINT_INVOICE
2307 ,OCLI.PRICE_UOM PRICE_UOM
2308 ,OCLI.TAX_AMOUNT TAX_AMOUNT
2309 ,'T' BILLING_SCHEDULE_TYPE
2310 ,1 OBJECT_VERSION_NUMBER
2311 ,FND_GLOBAL.USER_ID CREATED_BY
2312 ,SYSDATE CREATION_DATE
2313 ,FND_GLOBAL.USER_ID LAST_UPDATED_BY
2314 ,SYSDATE LAST_UPDATE_DATE
2315 ,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
2316
2317 FROM OKS_COVERED_LEVELS_INTERFACE OCLI
2318 ,OKS_INT_COVERED_LEVEL_STG_TEMP OCLVLSTG
2319 ,OKC_K_HEADERS_ALL_B OKCHDRB
2320 ,OKS_INT_HEADER_STG_TEMP HDRSTG
2321 ,OKS_INT_LINE_STG_TEMP OKSLINSTG
2322 ,OKC_K_LINES_B OKCLINB_SUBLINE
2323 ,OKC_K_LINES_B OKCLINB_LINE
2324
2325 WHERE OCLI.COVERED_LEVEL_INTERFACE_ID = OCLVLSTG.COVERED_LEVEL_INTERFACE_ID
2326 AND OKCLINB_SUBLINE.DNZ_CHR_ID = OKCHDRB.ID
2327 AND OKCLINB_SUBLINE.CLE_ID = OKCLINB_LINE.ID
2328 AND OCLI.LINE_NUMBER = OKCLINB_SUBLINE.LINE_NUMBER
2329 AND OKCLINB_LINE.LINE_NUMBER= OKSLINSTG.LINE_NUMBER
2330 AND OKCLINB_LINE.CHR_ID = OKCHDRB.ID
2331 AND OKCLINB_LINE.DNZ_CHR_ID = OKCHDRB.ID
2332 AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
2333 AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
2334 AND OCLI.LINE_INTERFACE_ID = OKSLINSTG.LINE_INTERFACE_ID
2335 AND OKSLINSTG.HEADER_INTERFACE_ID = HDRSTG.HEADER_INTERFACE_ID
2336 AND HDRSTG.INTERFACE_STATUS ='S';
2337
2338 /* IF G_STMT_LOG THEN
2339 l_int_count := SQL%ROWCOUNT;
2340 fnd_log.string(fnd_log.level_statement,
2341 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
2342 'Number of records successfully inserted = ' || l_int_count );
2343 END IF; */
2344
2345 l_stmt_num := 30;
2346
2347 INSERT ALL
2348 WHEN (1=1) THEN
2349 INTO OKS_K_LINES_TL
2350 (ID
2351 ,LANGUAGE
2352 ,SOURCE_LANG
2353 ,SFWT_FLAG
2354 ,INVOICE_TEXT
2355 ,CREATED_BY
2356 ,CREATION_DATE
2357 ,LAST_UPDATED_BY
2358 ,LAST_UPDATE_DATE
2359 ,LAST_UPDATE_LOGIN)
2360 VALUES (OKSLINB_ID
2361 ,LANGUAGE
2362 ,SOURCE_LANG
2363 ,OKS_SFWT_FLAG
2364 ,INVOICE_TEXT
2365 ,CREATED_BY
2366 ,CREATION_DATE
2367 ,LAST_UPDATED_BY
2368 ,LAST_UPDATE_DATE
2369 ,LAST_UPDATE_LOGIN)
2370 SELECT OKSLINB.ID OKSLINB_ID
2371 ,USERENV('LANG') LANGUAGE
2372 ,USERENV('LANG') SOURCE_LANG
2373 ,'S' OKS_SFWT_FLAG
2374 ,OCLI.INVOICE_TEXT INVOICE_TEXT
2375 ,FND_GLOBAL.USER_ID CREATED_BY
2376 ,SYSDATE CREATION_DATE
2377 ,FND_GLOBAL.USER_ID LAST_UPDATED_BY
2378 ,SYSDATE LAST_UPDATE_DATE
2379 ,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
2380
2381 FROM OKS_COVERED_LEVELS_INTERFACE OCLI
2382 ,OKS_INT_COVERED_LEVEL_STG_TEMP OCLVLSTG
2383 ,OKC_K_HEADERS_aLL_B OKCHDRB
2384 ,OKC_K_LINES_B OKCLINB_LINE
2385 ,OKC_K_LINES_B OKCLINB_SUBLINE
2386 ,OKS_K_LINES_B OKSLINB
2387 ,OKS_INT_LINE_STG_TEMP OKSLINSTG
2388 ,OKS_INT_HEADER_STG_TEMP HDRSTG
2389
2390 WHERE OCLI.COVERED_LEVEL_INTERFACE_ID = OCLVLSTG.COVERED_LEVEL_INTERFACE_ID
2391 AND OCLVLSTG.LINE_INTERFACE_ID = OKSLINSTG.LINE_INTERFACE_ID
2392 AND OKSLINSTG.HEADER_INTERFACE_ID = HDRSTG.HEADER_INTERFACE_ID
2393 AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
2394 AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
2395 AND HDRSTG.INTERFACE_STATUS ='S'
2396 AND OKCLINB_LINE.DNZ_CHR_ID = OKCHDRB.ID
2397 AND OKCLINB_LINE.CHR_ID =OKCHDRB.ID
2398 AND OKCLINB_LINE.LINE_NUMBER = OKSLINSTG.LINE_NUMBER
2399 AND OKCLINB_SUBLINE.DNZ_CHR_ID = OKCHDRB.ID
2400 AND OKCLINB_SUBLINE.CLE_ID = OKCLINB_LINE.ID
2401 AND OKCLINB_SUBLINE.LINE_NUMBER=OCLI.LINE_NUMBER
2402 AND OKSLINB.CLE_ID = OKCLINB_SUBLINE.ID
2403 AND OKSLINB.DNZ_CHR_ID = OKCHDRB.ID;
2404
2405 /* IF G_STMT_LOG THEN
2406 l_int_count := SQL%ROWCOUNT;
2407 fnd_log.string(fnd_log.level_statement,
2408 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
2409 'Number of records successfully inserted = ' || l_int_count );
2410 END IF; */
2411
2412 l_stmt_num := 40;
2413
2414 INSERT ALL
2415 WHEN (1=1) THEN
2416 INTO OKC_K_ITEMS
2417 (ID
2418 ,CLE_ID
2419 ,CHR_ID
2420 ,DNZ_CHR_ID
2421 ,OBJECT1_ID1
2422 ,OBJECT1_ID2
2423 ,JTOT_OBJECT1_CODE
2424 ,UOM_CODE
2425 ,EXCEPTION_YN
2426 ,NUMBER_OF_ITEMS
2427 ,OBJECT_VERSION_NUMBER
2428 ,CREATION_DATE
2429 ,created_by
2430 ,LAST_UPDATED_BY
2431 ,LAST_UPDATE_DATE)
2432 VALUES ( OKCKITEMS_ID
2433 ,CLE_ID
2434 ,null
2435 ,DNZ_CHR_ID
2436 ,OBJECT1_ID1
2437 ,OBJECT1_ID2
2438 ,JTOT_OBJECT1_CODE
2439 ,UOM_CODE
2440 ,EXCEPTION_YN
2441 ,NUMBER_OF_ITEMS
2442 ,OBJECT_VERSION_NUMBER
2443 ,CREATION_DATE
2444 ,created_by
2445 ,LAST_UPDATED_BY
2446 ,LAST_UPDATE_DATE)
2447 INTO OKC_K_LINES_TL
2448 (ID
2449 ,LANGUAGE
2450 ,SOURCE_LANG
2451 ,SFWT_FLAG
2452 ,CREATED_BY
2453 ,CREATION_DATE
2454 ,LAST_UPDATED_BY
2455 ,LAST_UPDATE_DATE
2456 ,LAST_UPDATE_LOGIN
2457 ,COGNOMEN)
2458 VALUES (OKCLINB_ID
2459 ,LANGUAGE
2460 ,SOURCE_LANG
2461 ,OKC_SFWT_FLAG
2462 ,CREATED_BY
2463 ,CREATION_DATE
2464 ,LAST_UPDATED_BY
2465 ,LAST_UPDATE_DATE
2466 ,LAST_UPDATE_LOGIN
2467 ,LINE_REFERENCE)
2468 SELECT okc_p_util.raw_to_number(sys_guid()) OKCKITEMS_ID
2469 ,OKCLINB_SUBLINE.ID CLE_ID
2470 ,OKCLINB_SUBLINE.ID OKCLINB_ID
2471 ,OKCHDRB.ID DNZ_CHR_ID
2472 ,null CHR_ID
2473 ,(CASE WHEN OCLVLSTG.COVERED_INSTANCE_ID IS NOT NULL THEN OCLVLSTG.COVERED_INSTANCE_ID
2474 WHEN OCLVLSTG.COVERED_ACCOUNT_ID IS NOT NULL THEN OCLVLSTG.COVERED_ACCOUNT_ID
2475 WHEN OCLVLSTG.COVERED_ITEM_ID IS NOT NULL THEN OCLVLSTG.COVERED_ITEM_ID
2476 WHEN OCLVLSTG.COVERED_PARTY_ID IS NOT NULL THEN OCLVLSTG.COVERED_PARTY_ID
2477 WHEN OCLVLSTG.COVERED_SITE_ID IS NOT NULL THEN OCLVLSTG.COVERED_SITE_ID
2478 WHEN OCLVLSTG.COVERED_SYSTEM_ID IS NOT NULL THEN OCLVLSTG.COVERED_SYSTEM_ID
2479 END) OBJECT1_ID1
2480
2481 ,(CASE WHEN OCLVLSTG.COVERED_ITEM_ID IS NOT NULL THEN TO_CHAR(OCLI.COVERED_ITEM_ORG_ID)
2482 ELSE '#'
2483 END) OBJECT1_ID2
2484
2485 ,(CASE WHEN OCLVLSTG.COVERED_INSTANCE_ID IS NOT NULL THEN 'OKX_CUSTPROD'
2486 WHEN OCLVLSTG.COVERED_ACCOUNT_ID IS NOT NULL THEN 'OKX_CUSTACCT'
2487 WHEN OCLVLSTG.COVERED_ITEM_ID IS NOT NULL THEN 'OKX_COVITEM'
2488 WHEN OCLVLSTG.COVERED_PARTY_ID IS NOT NULL THEN 'OKX_PARTY'
2489 WHEN OCLVLSTG.COVERED_SITE_ID IS NOT NULL THEN 'OKX_PARTYSITE'
2490 WHEN OCLVLSTG.COVERED_SYSTEM_ID IS NOT NULL THEN 'OKX_COVSYST'
2491 END) JTOT_OBJECT1_CODE
2492
2493 ,OCLI.QUANTITY_UOM UOM_CODE
2494 ,'N' EXCEPTION_YN
2495 ,OCLI.QUANTITY_COVERED NUMBER_OF_ITEMS
2496 ,1 OBJECT_VERSION_NUMBER
2497 ,FND_GLOBAL.USER_ID CREATED_BY
2498 ,SYSDATE CREATION_DATE
2499 ,FND_GLOBAL.USER_ID LAST_UPDATED_BY
2500 ,SYSDATE LAST_UPDATE_DATE
2501 ,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
2502 ,USERENV('LANG') LANGUAGE
2503 ,USERENV('LANG') SOURCE_LANG
2504 ,'N' OKC_SFWT_FLAG
2505 ,OCLI.LINE_REFERENCE LINE_REFERENCE
2506
2507 FROM OKS_COVERED_LEVELS_INTERFACE OCLI
2508 ,OKS_INT_COVERED_LEVEL_STG_TEMP OCLVLSTG
2509 ,OKC_K_HEADERS_aLL_B OKCHDRB
2510 ,OKC_K_LINES_B OKCLINB_LINE
2511 ,OKC_K_LINES_B OKCLINB_SUBLINE
2512 ,OKS_K_LINES_B OKSLINB
2513 ,OKS_INT_LINE_STG_TEMP OKSLINSTG
2514 ,OKS_INT_HEADER_STG_TEMP HDRSTG
2515
2516 WHERE OCLI.COVERED_LEVEL_INTERFACE_ID = OCLVLSTG.COVERED_LEVEL_INTERFACE_ID
2517 AND OCLVLSTG.LINE_INTERFACE_ID = OKSLINSTG.LINE_INTERFACE_ID
2518 AND OKSLINSTG.HEADER_INTERFACE_ID = HDRSTG.HEADER_INTERFACE_ID
2519 AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
2520 AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER, 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER, 'Xwqwewe@!&*aQ1')
2521 AND HDRSTG.INTERFACE_STATUS ='S'
2522 AND OKCLINB_LINE.DNZ_CHR_ID = OKCHDRB.ID
2523 AND OKCLINB_LINE.CHR_ID =OKCHDRB.ID
2524 AND OKCLINB_LINE.LINE_NUMBER = OKSLINSTG.LINE_NUMBER
2525 AND OKCLINB_SUBLINE.DNZ_CHR_ID = OKCHDRB.ID
2526 AND OKCLINB_SUBLINE.CLE_ID = OKCLINB_LINE.ID
2527 AND OKCLINB_SUBLINE.LINE_NUMBER=OCLI.LINE_NUMBER
2528 AND OKSLINB.CLE_ID = OKCLINB_SUBLINE.ID
2529 AND OKSLINB.DNZ_CHR_ID = OKCHDRB.ID;
2530
2531
2532 -- Insert query for Usage Counters
2533
2534 l_stmt_num :=50;
2535
2536 INSERT ALL
2537 WHEN (1=1) THEN
2538 INTO OKC_K_LINES_B
2539 (ID
2540 ,LINE_NUMBER
2541 ,CHR_ID
2542 ,CLE_ID
2543 ,DNZ_CHR_ID
2544 ,STS_CODE
2545 ,TRN_CODE
2546 ,LSE_ID
2547 ,EXCEPTION_YN
2548 ,OBJECT_VERSION_NUMBER
2549 ,display_sequence
2550 ,CREATED_BY
2551 ,CREATION_DATE
2552 ,LAST_UPDATED_BY
2553 ,LAST_UPDATE_DATE
2554 ,PRICE_NEGOTIATED
2555 ,PRICE_LEVEL_IND
2556 ,CURRENCY_CODE
2557 ,START_DATE
2558 ,END_DATE
2559 ,ORIG_SYSTEM_SOURCE_CODE
2560 ,ORIG_SYSTEM_ID1
2561 ,ORIG_SYSTEM_REFERENCE1
2562 ,DATE_CANCELLED
2563 ,ANNUALIZED_FACTOR
2564 ,CANCELLED_AMOUNT
2565 ,LINE_RENEWAL_TYPE_CODE)
2566 VALUES (OKCLINB_ID
2567 ,LINE_NUMBER
2568 ,CHR_ID
2569 ,CLE_ID
2570 ,DNZ_CHR_ID
2571 ,STATUS_CODE
2572 ,CANCELLATION_REASON
2573 ,LSE_ID
2574 ,EXCEPTION_YN
2575 ,OBJECT_VERSION_NUMBER
2576 ,2
2577 ,CREATED_BY
2578 ,CREATION_DATE
2579 ,LAST_UPDATED_BY
2580 ,LAST_UPDATE_DATE
2581 ,SUBTOTAL
2582 ,PRICED_YN
2583 ,CURRENCY_CODE
2584 ,START_DATE
2585 ,END_DATE
2586 ,ORIG_SYSTEM_SOURCE_CODE
2587 ,ORIG_SYSTEM_ID1
2588 ,ORIG_SYSTEM_REFERENCE1
2589 ,CANCELLATION_DATE
2590 ,ANNUALIZED_FACTOR
2591 ,CANCELLED_AMOUNT
2592 ,RENEWAL_TYPE_CODE)
2593
2594 SELECT okc_p_util.raw_to_number(sys_guid()) OKCLINB_ID
2595 ,OUSGCNTSTG.LINE_NUMBER LINE_NUMBER
2596 ,null CHR_ID
2597 ,OKCLINB_LINE.ID CLE_ID
2598 ,OKCHDRB.ID DNZ_CHR_ID
2599 ,OUSGCNTSTG.STATUS_CODE STATUS_CODE
2600 ,OUSGCNTSTG.CANCELLATION_REASON CANCELLATION_REASON
2601 ,13 LSE_ID
2602 ,'N' EXCEPTION_YN
2603 ,1 OBJECT_VERSION_NUMBER
2604 ,FND_GLOBAL.USER_ID CREATED_BY
2605 ,SYSDATE CREATION_DATE
2606 ,FND_GLOBAL.USER_ID LAST_UPDATED_BY
2607 ,SYSDATE LAST_UPDATE_DATE
2608 ,OUSGCNTSTG.SUBTOTAL SUBTOTAL
2609 ,'Y' PRICED_YN
2610 ,HDRSTG.CONTRACT_CURRENCY_CODE CURRENCY_CODE
2611 ,OUSGCNTSTG.START_DATE START_DATE
2612 ,OUSGCNTSTG.END_DATE END_DATE
2613 ,null ORIG_SYSTEM_SOURCE_CODE
2614 ,null ORIG_SYSTEM_ID1
2615 ,null ORIG_SYSTEM_REFERENCE1
2616 ,OUSGCNTSTG.CANCELLATION_DATE CANCELLATION_DATE
2617 ,OUSGCNTSTG.RENEWAL_TYPE_CODE RENEWAL_TYPE_CODE
2618 , (ADD_MONTHS(OLSTG.START_DATE, (INNER_Q.NYEARS+1)*12) - OLSTG.START_DATE -
2619 DECODE(ADD_MONTHS(OLSTG.END_DATE, -12),( OLSTG.END_DATE-366), 0,
2620 DECODE(ADD_MONTHS(OLSTG.START_DATE, (INNER_Q.NYEARS+1)*12)
2621 - ADD_MONTHS(OLSTG.START_DATE, INNER_Q.NYEARS*12), 366, 1, 0)))
2622 / (INNER_Q.NYEARS+1) /(OLSTG.END_DATE-OLSTG.START_DATE+ 1) ANNUALIZED_FACTOR
2623 ,null CANCELLED_AMOUNT
2624
2625 FROM OKS_INT_USAGE_COUNTER_STG_TEMP OUSGCNTSTG
2626 ,OKS_USAGE_COUNTERS_INTERFACE OUSGCNTINT
2627 ,OKS_INT_LINE_STG_TEMP OLSTG
2628 ,OKS_INT_HEADER_STG_TEMP HDRSTG
2629 ,OKC_K_HEADERS_ALL_B OKCHDRB
2630 ,OKC_K_LINES_B OKCLINB_LINE
2631 ,(SELECT trunc(MONTHS_BETWEEN(LINSTG.END_DATE, LINSTG.START_DATE)/12) NYEARS, USAGE_COUNTER_INTERFACE_ID
2632 FROM OKS_INT_USAGE_COUNTER_STG_TEMP USGSTG
2633 ,OKS_INT_LINE_STG_TEMP LINSTG
2634 WHERE
2635 USGSTG.LINE_INTERFACE_ID = LINSTG.LINE_INTERFACE_ID
2636 AND LINSTG.LSE_ID in (1,12,14,19,46,7,8,9,10,11,13,18,25,35)
2637 ) INNER_Q
2638
2639 WHERE OUSGCNTINT.USAGE_COUNTER_INTERFACE_ID =OUSGCNTSTG.USAGE_COUNTER_INTERFACE_ID
2640 AND OUSGCNTSTG.USAGE_COUNTER_INTERFACE_ID = INNER_Q.USAGE_COUNTER_INTERFACE_ID
2641 AND OUSGCNTSTG.LINE_INTERFACE_ID =OLSTG.LINE_INTERFACE_ID
2642 AND OLSTG.HEADER_INTERFACE_ID = HDRSTG.HEADER_INTERFACE_ID
2643 AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
2644 AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
2645 AND OKCLINB_LINE.CHR_ID = OKCHDRB.ID
2646 AND OKCLINB_LINE.LINE_NUMBER= OLSTG.LINE_NUMBER
2647 AND HDRSTG.INTERFACE_STATUS = 'S';
2648
2649
2650
2651 l_stmt_num :=60;
2652
2653 INSERT ALL
2654 WHEN (1=1 ) THEN
2655 INTO OKS_K_LINES_B
2656 (ID
2657 ,CLE_ID
2658 ,DNZ_CHR_ID
2659 ,USAGE_EST_YN
2660 ,USAGE_EST_METHOD
2661 ,USAGE_EST_START_DATE
2662 ,BILLING_SCHEDULE_TYPE
2663 ,TAX_AMOUNT
2664 ,MINIMUM_QUANTITY
2665 ,DEFAULT_QUANTITY
2666 ,FIXED_QUANTITY
2667 ,LEVEL_YN
2668 ,PRICE_UOM
2669 ,USAGE_PERIOD
2670 ,USAGE_TYPE
2671 ,UOM_QUANTIFIED
2672 ,INV_PRINT_FLAG
2673 ,CREATED_BY
2674 ,CREATION_DATE
2675 ,LAST_UPDATED_BY
2676 ,LAST_UPDATE_DATE
2677 ,LAST_UPDATE_LOGIN
2678 ,ORIG_SYSTEM_ID1
2679 ,ORIG_SYSTEM_REFERENCE1
2680 ,ORIG_SYSTEM_SOURCE_CODE
2681 ,OBJECT_VERSION_NUMBER)
2682 VALUES ( OKSLINB_ID
2683 ,CLE_ID
2684 ,DNZ_CHR_ID
2685 ,FILL_YN
2686 ,ESTIMATION_METHOD
2687 ,ESTIMATION_START_DATE
2688 ,BILLING_SCHEDULE_TYPE
2689 ,TAX_AMOUNT
2690 ,MINIMUM_USG_CTR
2691 ,DEFAULT_USG_CTR
2692 ,FIXED_USG_CTR
2693 ,LEVEL_YN
2694 ,PRICE_UOM
2695 ,USAGE_PERIOD
2696 ,USAGE_TYPE
2697 ,UOM_QUANTIFIED
2698 ,INV_PRINT_FLAG
2699 ,CREATED_BY
2700 ,CREATION_DATE
2701 ,LAST_UPDATED_BY
2702 ,LAST_UPDATE_DATE
2703 ,LAST_UPDATE_LOGIN
2704 ,ORIG_SYSTEM_ID1
2705 ,ORIG_SYSTEM_REFERENCE1
2706 ,ORIG_SYSTEM_SOURCE_CODE
2707 ,1)
2708 SELECT okc_p_util.raw_to_number(sys_guid()) OKSLINB_ID
2709 ,OKCLINB_SUBLINE.ID CLE_ID
2710 ,OKCHDRB.ID DNZ_CHR_ID
2711 ,OUSGCNTSTG.FILL_YN FILL_YN
2712 ,OUSGCNTSTG.ESTIMATION_METHOD ESTIMATION_METHOD
2713 ,OUSGCNTSTG.ESTIMATION_START_DATE ESTIMATION_START_DATE
2714 ,OUSGCNTSTG.TAX_AMOUNT TAX_AMOUNT
2715 ,OUSGCNTSTG.MINIMUM_USG_CTR MINIMUM_USG_CTR
2716 ,OUSGCNTSTG.DEFAULT_USG_CTR DEFAULT_USG_CTR
2717 ,OUSGCNTSTG.FIXED_USG_CTR FIXED_USG_CTR
2718 ,OUSGCNTSTG.LEVEL_YN LEVEL_YN
2719 ,OLSTG.PRICE_UOM PRICE_UOM -- need to confirm
2720 ,NULL USAGE_PERIOD
2721 ,NULL USAGE_TYPE
2722 ,NULL UOM_QUANTIFIED
2723 ,OUSGCNTSTG.PRINT_INVOICE INV_PRINT_FLAG
2724 ,FND_GLOBAL.USER_ID CREATED_BY
2725 ,SYSDATE CREATION_DATE
2726 ,FND_GLOBAL.USER_ID LAST_UPDATED_BY
2727 ,SYSDATE LAST_UPDATE_DATE
2728 ,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
2729 ,null ORIG_SYSTEM_SOURCE_CODE
2730 ,null ORIG_SYSTEM_ID1
2731 ,null ORIG_SYSTEM_REFERENCE1
2732 ,'T' BILLING_SCHEDULE_TYPE
2733 FROM OKS_INT_USAGE_COUNTER_STG_TEMP OUSGCNTSTG
2734 ,OKS_USAGE_COUNTERS_INTERFACE OUSGCNTINT
2735 ,OKS_INT_LINE_STG_TEMP OLSTG
2736 ,OKS_INT_HEADER_STG_TEMP HDRSTG
2737 ,OKC_K_HEADERS_ALL_B OKCHDRB
2738 ,OKC_K_LINES_B OKCLINB_SUBLINE
2739 ,OKC_K_LINES_B OKCLINB_LINE
2740 WHERE OUSGCNTINT.USAGE_COUNTER_INTERFACE_ID = OUSGCNTSTG.USAGE_COUNTER_INTERFACE_ID
2741 AND OKCLINB_SUBLINE.DNZ_CHR_ID = OKCHDRB.ID
2742 and OKCLINB_SUBLINE.CLE_ID = OKCLINB_LINE.ID
2743 and OUSGCNTSTG.LINE_NUMBER = OKCLINB_SUBLINE.LINE_NUMBER
2744 and OKCLINB_LINE.LINE_NUMBER= OLSTG.LINE_NUMBER
2745 AND OKCLINB_LINE.CHR_ID = OKCHDRB.ID
2746 AND OKCLINB_LINE.DNZ_CHR_ID = OKCHDRB.ID
2747 AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
2748 AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER , 'Xwqwewe@!&*aQ1')
2749 AND OUSGCNTSTG.LINE_INTERFACE_ID =OLSTG.LINE_INTERFACE_ID
2750 AND OLSTG.HEADER_INTERFACE_ID = HDRSTG.HEADER_INTERFACE_ID
2751 AND HDRSTG.INTERFACE_STATUS ='S';
2752
2753
2754 l_stmt_num :=70;
2755
2756 INSERT ALL
2757 WHEN (1=1) THEN
2758 INTO OKC_K_LINES_TL
2759 (ID
2760 ,LANGUAGE
2761 ,SOURCE_LANG
2762 ,SFWT_FLAG
2763 ,CREATED_BY
2764 ,CREATION_DATE
2765 ,LAST_UPDATED_BY
2766 ,LAST_UPDATE_DATE
2767 ,LAST_UPDATE_LOGIN
2768 ,COGNOMEN)
2769 VALUES ( OKCLINB_ID
2770 ,LANGUAGE
2771 ,SOURCE_LANG
2772 ,SFWT_FLAG
2773 ,CREATED_BY
2774 ,CREATION_DATE
2775 ,LAST_UPDATED_BY
2776 ,LAST_UPDATE_DATE
2777 ,LAST_UPDATE_LOGIN
2778 ,COGNOMEN)
2779 WHEN (1=1 ) THEN
2780 INTO OKS_K_LINES_TL
2781 (ID
2782 ,LANGUAGE
2783 ,SOURCE_LANG
2784 ,SFWT_FLAG
2785 ,INVOICE_TEXT
2786 ,CREATED_BY
2787 ,CREATION_DATE
2788 ,LAST_UPDATED_BY
2789 ,LAST_UPDATE_DATE
2790 ,LAST_UPDATE_LOGIN)
2791 VALUES (OKSLINB_ID
2792 ,LANGUAGE
2793 ,SOURCE_LANG
2794 ,'S'
2795 ,INVOICE_TEXT
2796 ,CREATED_BY
2797 ,CREATION_DATE
2798 ,LAST_UPDATED_BY
2799 ,LAST_UPDATE_DATE
2800 ,LAST_UPDATE_LOGIN)
2801 WHEN (1=1) THEN
2802 INTO OKC_K_ITEMS
2803 (ID
2804 ,CLE_ID
2805 ,CHR_ID
2806 ,DNZ_CHR_ID
2807 ,OBJECT1_ID1
2808 ,OBJECT1_ID2
2809 ,JTOT_OBJECT1_CODE
2810 ,EXCEPTION_YN
2811 ,NUMBER_OF_ITEMS
2812 ,PRICED_ITEM_YN
2813 ,OBJECT_VERSION_NUMBER
2814 ,CREATED_BY
2815 ,CREATION_DATE
2816 ,LAST_UPDATED_BY
2817 ,LAST_UPDATE_DATE
2818 ,LAST_UPDATE_LOGIN)
2819 VALUES (OKCKITM_ID
2820 ,CLE_ID
2821 ,null
2822 ,DNZ_CHR_ID
2823 ,OBJECT1_ID1
2824 ,OBJECT1_ID2
2825 ,JTOT_OBJECT1_CODE
2826 ,EXCEPTION_YN
2827 ,NUMBER_OF_ITEMS
2828 ,PRICED_ITEM_YN
2829 ,OBJECT_VERSION_NUMBER
2830 ,CREATED_BY
2831 ,CREATION_DATE
2832 ,LAST_UPDATED_BY
2833 ,LAST_UPDATE_DATE
2834 ,LAST_UPDATE_LOGIN)
2835
2836 SELECT OKCLINB_SUBLINE.ID OKCLINB_ID
2837 ,USERENV('LANG') LANGUAGE
2838 ,USERENV('LANG') SOURCE_LANG
2839 ,'N' SFWT_FLAG
2840 ,FND_GLOBAL.USER_ID CREATED_BY
2841 ,SYSDATE CREATION_DATE
2842 ,FND_GLOBAL.USER_ID LAST_UPDATED_BY
2843 ,SYSDATE LAST_UPDATE_DATE
2844 ,FND_GLOBAL.LOGIN_ID LAST_UPDATE_LOGIN
2845 ,OUSGCNTSTG.LINE_REFERENCE COGNOMEN
2846 ,OKSLINB.ID OKSLINB_ID
2847 ,OUSGCNTINT.INVOICE_TEXT INVOICE_TEXT
2848 ,okc_p_util.raw_to_number(sys_guid()) OKCKITM_ID
2849 ,OKCLINB_SUBLINE.ID CLE_ID
2850 ,null CHR_ID
2851 ,OKCHDRB.ID DNZ_CHR_ID
2852 ,OUSGCNTINT.COUNTER_ID OBJECT1_ID1
2853 ,'#' OBJECT1_ID2
2854 ,'OKX_COUNTER' JTOT_OBJECT1_CODE -- default value for usagel lines
2855 ,'N' EXCEPTION_YN
2856 ,null NUMBER_OF_ITEMS
2857 ,'Y' PRICED_ITEM_YN
2858 ,1 OBJECT_VERSION_NUMBER
2859
2860 FROM OKS_INT_USAGE_COUNTER_STG_TEMP OUSGCNTSTG
2861 ,OKS_USAGE_COUNTERS_INTERFACE OUSGCNTINT
2862 ,OKS_INT_LINE_STG_TEMP OLSTG
2863 ,OKS_INT_HEADER_STG_TEMP HDRSTG
2864 ,OKC_K_HEADERS_ALL_B OKCHDRB
2865 ,OKC_K_LINES_B OKCLINB_SUBLINE
2866 ,OKC_K_LINES_B OKCLINB_LINE
2867 ,OKS_K_LINES_B OKSLINB
2868
2869 WHERE OUSGCNTINT.USAGE_COUNTER_INTERFACE_ID = OUSGCNTSTG.USAGE_COUNTER_INTERFACE_ID
2870 AND OUSGCNTSTG.LINE_INTERFACE_ID =OLSTG.LINE_INTERFACE_ID
2871 AND OLSTG.HEADER_INTERFACE_ID = HDRSTG.HEADER_INTERFACE_ID
2872 AND HDRSTG.CONTRACT_NUMBER = OKCHDRB.CONTRACT_NUMBER
2873 AND NVL(HDRSTG.CONTRACT_NUMBER_MODIFIER, 'Xwqwewe@!&*aQ1') = NVL(OKCHDRB.CONTRACT_NUMBER_MODIFIER, 'Xwqwewe@!&*aQ1')
2874 AND OKCLINB_SUBLINE.DNZ_CHR_ID = OKCHDRB.ID
2875 AND OKCLINB_SUBLINE.CLE_ID =OKCLINB_LINE.ID
2876 AND OUSGCNTSTG.LINE_NUMBER=OKCLINB_SUBLINE.LINE_NUMBER
2877 AND OKCLINB_LINE.LINE_NUMBER= OLSTG.LINE_NUMBER
2878 AND OKCLINB_LINE.CHR_ID = OKCHDRB.ID
2879 AND OKCLINB_LINE.DNZ_CHR_ID =OKCHDRB.ID
2880 AND OKSLINB.DNZ_CHR_ID = OKCHDRB.ID
2881 AND OKSLINB.CLE_ID = OKCLINB_SUBLINE.ID
2882 AND HDRSTG.INTERFACE_STATUS = 'S';
2883
2884 IF G_STMT_LOG THEN
2885 null;
2886 /*l_int_count := SQL%ROWCOUNT;
2887 fnd_log.string(fnd_log.level_statement,
2888 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
2889 'Number of records successfully inserted = ' || l_int_count );*/
2890 END IF;
2891
2892 IF G_PROCEDURE_LOG THEN
2893 fnd_log.string(fnd_log.level_procedure,
2894 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
2895 'Exit.');
2896 END IF;
2897
2898 EXCEPTION
2899 WHEN FND_API.G_EXC_ERROR THEN
2900 RAISE FND_API.G_EXC_ERROR;
2901 WHEN OTHERS THEN
2902 FND_MESSAGE.Set_Name('OKS', 'OKS_IMPORT_UNEXPECTED');
2903 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
2904 FND_MESSAGE.set_token('MESSAGE', 'stmt_num '||l_stmt_num||' ('||SQLCODE||') '||SQLERRM);
2905 FND_MSG_PUB.Add;
2906 RAISE FND_API.G_EXC_ERROR;
2907 END Insert_covlevel_and_usgcounter;
2908
2909
2910 --========================================================================
2911 -- PROCEDURE : Insert_Contracts PUBLIC
2912 -- PARAMETERS:
2913 -- COMMENT : This procedure will perform the Insert routines
2914 --=========================================================================
2915
2916 PROCEDURE Insert_Contracts IS
2917
2918 l_stmt_num NUMBER := 0;
2919 l_routine CONSTANT VARCHAR2(30) := 'Insert_Contracts';
2920 X_errbuf VARCHAR2(2000);
2921 X_retcode VARCHAR2(1);
2922 BEGIN
2923 IF G_PROCEDURE_LOG THEN
2924 fnd_log.string(fnd_log.level_procedure,
2925 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
2926 'Entering .' );
2927
2928 END IF;
2929
2930 FND_MSG_PUB.initialize;
2931
2932 l_stmt_num := 10;
2933
2934 Insert_okc_header;
2935 Insert_oks_header;
2936 Insert_Contract_groups;
2937 Insert_okc_toplines;
2938 Insert_oks_toplines;
2939 Insert_Party_Roles;
2940 Insert_Contacts;
2941 Insert_Sales_Credits ;
2942 Insert_covlevel_and_usgcounter;
2943
2944 IF G_PROCEDURE_LOG THEN
2945 fnd_log.string(fnd_log.level_procedure,
2946 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
2947 'Exit.');
2948 END IF;
2949
2950 EXCEPTION
2951 WHEN FND_API.G_EXC_ERROR THEN
2952 -- ROLLBACK;
2953 RAISE FND_API.G_EXC_ERROR;
2954 WHEN OTHERS THEN
2955 -- ROLLBACK;
2956 FND_MESSAGE.Set_Name('OKS', 'OKS_IMPORT_UNEXPECTED');
2957 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
2958 FND_MESSAGE.set_token('MESSAGE', 'stmt_num '||l_stmt_num||' ('||SQLCODE||') '||SQLERRM);
2959 FND_MSG_PUB.Add;
2960 RAISE FND_API.G_EXC_ERROR;
2961 END Insert_Contracts;
2962
2963 END OKS_IMPORT_INSERT;