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