[Home] [Help]
PACKAGE BODY: APPS.OKS_IMPORT_VALIDATE
Source
1 PACKAGE BODY OKS_IMPORT_VALIDATE AS
2 -- $Header: OKSPKIMPVALB.pls 120.16 2008/06/06 12:26:05 cgopinee noship $
3 --+=======================================================================+
4 --| Copyright (c) 2003 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| OKSPKIMPVALB.pls Created By Vamshi Mutyala |
10 --| |
11 --| DESCRIPTION |
12 --| Service Contracts Import Validations Package |
13 --| |
14 --+========================================================================
15
16 --===================
17 -- GLOBALS
18 --===================
19
20 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OKS_IMPORT_VALIDATE';
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 : Validate_header PRIVATE
43 -- PARAMETERS: P_batch_id IN Batch Id
44 -- P_rowid_from IN AD worker start rowid
45 -- P_rowid_to IN AD worker end rowid
46 -- COMMENT : This procedure will perform the validation needed
47 -- on the headers interface records.
48 --=========================================================================
49 PROCEDURE Validate_header (P_batch_id IN VARCHAR2,
50 P_rowid_from IN ROWID,
51 P_rowid_to IN ROWID)
52 IS
53 l_stmt_num NUMBER := 0;
54 l_routine CONSTANT VARCHAR2(30) := 'Validate_header';
55 l_int_count NUMBER := 0;
56 l_stg_count NUMBER := 0;
57 BEGIN
58 IF G_PROCEDURE_LOG THEN
59 fnd_log.string(fnd_log.level_procedure,
60 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
61 'Entering with '||
62 'P_batch_id = '|| P_batch_id ||','||
63 'P_rowid_from = '|| P_rowid_from ||','||
64 'P_rowid_to = '|| P_rowid_to);
65 END IF;
66
67 l_stmt_num := 10;
68
69 INSERT ALL
70 WHEN (1 = 1) THEN
71 INTO OKS_INT_HEADER_STG_TEMP
72 (HEADER_INTERFACE_ID
73 ,HEADER_INTERFACE_ROWID
74 ,CONTRACT_NUMBER
75 ,CONTRACT_NUMBER_MODIFIER
76 ,CATEGORY
77 ,STATUS_CODE
78 ,START_DATE
79 ,END_DATE
80 ,KNOWN_AS
81 ,DESCRIPTION
82 ,OPERATING_UNIT_ID
83 ,BILL_TO_SITE_USAGE_ID
84 ,SHIP_TO_SITE_USAGE_ID
85 ,CONTRACT_CURRENCY_CODE
86 ,CURRENCY_CONVERSION_TYPE
87 ,CURRENCY_CONVERSION_RATE
88 ,CURRENCY_CONVERSION_DATE
89 ,PRICE_LIST_ID
90 ,PAYMENT_TERMS_ID
91 ,PAYMENT_INSTRUCTION
92 ,PO_REQUIRED
93 ,PAYMENT_INSTRUCTION_DETAILS
94 ,INVOICING_RULE_ID
95 ,SERVICE_CHRG_PREPAY_REQ
96 ,RENEWAL_PROCESS
97 ,APPROVAL_REQUIRED
98 ,RENEW_UP_TO
99 ,DATE_APPROVED
100 ,DATE_SIGNED
101 ,DATE_CANCELED
102 ,CANCELLATION_REASON
103 ,QA_CHECKLIST
104 ,PAYMENT_METHOD_CODE
105 ,COMMITMENT_ID
106 ,TAX_EXEMPTION_CONTROL
107 ,TAX_EXEMPTION_NUMBER
108 ,BILL_SERVICES
109 ,BILLING_TRANSACTION_TYPE_ID
110 ,ACCOUNTING_RULE_ID
111 ,HOLD_CREDITS
112 ,SUMMARY_PRINT
113 ,SUMMARY_TRANSACTIONS
114 ,SERVICE_CHARGES_PO_REQUIRED
115 ,SERVICE_CHARGES_PO_NUMBER
116 ,PRICING_METHOD
117 ,RENEWAL_PRICE_LIST_ID
118 ,RENEWAL_MARKUP
119 ,RENEWAL_BILLING_PROFILE_ID
120 ,RENEWAL_PO_NUMBER
121 ,RENEWAL_PO_REQUIRED
122 ,RENEWAL_GRACE_DURATION
123 ,RENEWAL_GRACE_PERIOD
124 ,RENEWAL_ESTIMATED_PERCENT
125 ,RENEWAL_ESTIMATED_DURATION
126 ,RENEWAL_ESTIMATED_PERIOD
127 ,QUOTE_TO_PARTY_SITE
128 ,QUOTE_TO_CONTACT
129 ,QUOTE_TO_PHONE
130 ,QUOTE_TO_FAX
131 ,QUOTE_TO_EMAIL
132 ,GRACE_DURATION
133 ,GRACE_PERIOD
134 ,ESTIMATION_PERCENT
135 ,ESTIMATION_DATE
136 ,FOLLOW_UP_DUE_DATE
137 ,FOLLOW_UP_ACTION
138 ,CUSTOMER_PARTY_ID
139 ,THIRD_PARTY_ID
140 ,CONTRACT_GROUP_ID
141 ,APPROVAL_PROCESS_ID
142 ,AGREEMENT_ID
143 ,SALESPERSON_ID
144 ,CUSTOMER_CONTRACT_ADMIN_ID
145 ,INV_ORGANIZATION_ID
146 ,EXEMPT_REASON_CODE
147 ,SALESGROUP_ID
148 ,FULLY_BILLED
149 ,SOURCE
150 ,DOCUMENT)
151 VALUES (HEADER_INTERFACE_ID
152 ,HEADER_INTERFACE_ROWID
153 ,CONTRACT_NUMBER
154 ,CONTRACT_NUMBER_MODIFIER
155 ,CATEGORY
156 ,STATUS_CODE
157 ,START_DATE
158 ,END_DATE
159 ,KNOWN_AS
160 ,DESCRIPTION
161 ,OPERATING_UNIT_ID
162 ,BILL_TO_SITE_USAGE_ID
163 ,SHIP_TO_SITE_USAGE_ID
164 ,CONTRACT_CURRENCY_CODE
165 ,CURRENCY_CONVERSION_TYPE
166 ,CURRENCY_CONVERSION_RATE
167 ,CURRENCY_CONVERSION_DATE
168 ,PRICE_LIST_ID
169 ,PAYMENT_TERMS_ID
170 ,PAYMENT_INSTRUCTION
171 ,PO_REQUIRED
172 ,PAYMENT_INSTRUCTION_DETAILS
173 ,INVOICING_RULE_ID
174 ,SERVICE_CHRG_PREPAY_REQ
175 ,RENEWAL_PROCESS
176 ,APPROVAL_REQUIRED
177 ,RENEW_UP_TO
178 ,DATE_APPROVED
179 ,DATE_SIGNED
180 ,DATE_CANCELED
181 ,CANCELLATION_REASON
182 ,QA_CHECKLIST
183 ,PAYMENT_METHOD_CODE
184 ,COMMITMENT_ID
185 ,TAX_EXEMPTION_CONTROL
186 ,TAX_EXEMPTION_NUMBER
187 ,BILL_SERVICES
188 ,BILLING_TRANSACTION_TYPE_ID
189 ,ACCOUNTING_RULE_ID
190 ,HOLD_CREDITS
191 ,SUMMARY_PRINT
192 ,SUMMARY_TRANSACTIONS
193 ,SERVICE_CHARGES_PO_REQUIRED
194 ,SERVICE_CHARGES_PO_NUMBER
195 ,PRICING_METHOD
196 ,RENEWAL_PRICE_LIST_ID
197 ,RENEWAL_MARKUP
198 ,RENEWAL_BILLING_PROFILE_ID
199 ,RENEWAL_PO_NUMBER
200 ,RENEWAL_PO_REQUIRED
201 ,RENEWAL_GRACE_DURATION
202 ,RENEWAL_GRACE_PERIOD
203 ,RENEWAL_ESTIMATED_PERCENT
204 ,RENEWAL_ESTIMATED_DURATION
205 ,RENEWAL_ESTIMATED_PERIOD
206 ,QUOTE_TO_PARTY_SITE
207 ,QUOTE_TO_CONTACT
208 ,QUOTE_TO_PHONE
209 ,QUOTE_TO_FAX
210 ,QUOTE_TO_EMAIL
211 ,GRACE_DURATION
212 ,GRACE_PERIOD
213 ,ESTIMATION_PERCENT
214 ,ESTIMATION_DATE
215 ,FOLLOW_UP_DUE_DATE
216 ,FOLLOW_UP_ACTION
217 ,CUSTOMER_PARTY_ID
218 ,THIRD_PARTY_ID
219 ,CONTRACT_GROUP_ID
220 ,APPROVAL_PROCESS_ID
221 ,AGREEMENT_ID
222 ,SALESPERSON_ID
223 ,CUSTOMER_CONTRACT_ADMIN_ID
224 ,INV_ORGANIZATION_ID
225 ,EXEMPT_REASON_CODE
226 ,SALESGROUP_ID
227 ,FULLY_BILLED
228 ,SOURCE
229 ,DOCUMENT)
230 WHEN (OPERATING_UNIT_ID IS NULL) THEN
231 INTO OKS_INT_ERROR_STG_TEMP
232 (CONCURRENT_REQUEST_ID
233 ,HEADER_INTERFACE_ROWID
234 ,INTERFACE_SOURCE_TABLE
235 ,INTERFACE_ID
236 ,ERROR_MSG
237 ,MSG_TOKENS)
238 VALUES (G_WORKER_REQ_ID
239 ,HEADER_INTERFACE_ROWID
240 ,'OKS_HEADERS_INTERFACE'
241 ,HEADER_INTERFACE_ID
242 ,'OKS_IMP_HDR_INVALID_OU'
243 ,NULL)
244 WHEN (BILL_TO_SITE_USAGE_ID IS NULL) THEN
245 INTO OKS_INT_ERROR_STG_TEMP
246 (CONCURRENT_REQUEST_ID
247 ,HEADER_INTERFACE_ROWID
248 ,INTERFACE_SOURCE_TABLE
249 ,INTERFACE_ID
250 ,ERROR_MSG
251 ,MSG_TOKENS)
252 VALUES (G_WORKER_REQ_ID
253 ,HEADER_INTERFACE_ROWID
254 ,'OKS_HEADERS_INTERFACE'
255 ,HEADER_INTERFACE_ID
256 ,'OKS_IMP_HDR_INVALID_BTSU'
257 ,NULL)
258 WHEN (SHIP_TO_SITE_USAGE_ID IS NULL) THEN
259 INTO OKS_INT_ERROR_STG_TEMP
260 (CONCURRENT_REQUEST_ID
261 ,HEADER_INTERFACE_ROWID
262 ,INTERFACE_SOURCE_TABLE
263 ,INTERFACE_ID
264 ,ERROR_MSG
265 ,MSG_TOKENS)
266 VALUES (G_WORKER_REQ_ID
267 ,HEADER_INTERFACE_ROWID
268 ,'OKS_HEADERS_INTERFACE'
269 ,HEADER_INTERFACE_ID
270 ,'OKS_IMP_HDR_INVALID_STSU'
271 ,NULL)
272 WHEN (CONTRACT_CURRENCY_CODE IS NULL) THEN
273 INTO OKS_INT_ERROR_STG_TEMP
274 (CONCURRENT_REQUEST_ID
275 ,HEADER_INTERFACE_ROWID
276 ,INTERFACE_SOURCE_TABLE
277 ,INTERFACE_ID
278 ,ERROR_MSG
279 ,MSG_TOKENS)
280 VALUES (G_WORKER_REQ_ID
281 ,HEADER_INTERFACE_ROWID
282 ,'OKS_HEADERS_INTERFACE'
283 ,HEADER_INTERFACE_ID
284 ,'OKS_IMP_HDR_INVALID_CUR_CODE'
285 ,NULL)
286 WHEN (CONTRACT_CURRENCY_CODE IS NOT NULL
287 AND CONTRACT_CURRENCY_CODE <> OU_LEDGER_CUR_CODE
288 AND CURRENCY_CONVERSION_TYPE IS NULL) THEN
289 INTO OKS_INT_ERROR_STG_TEMP
290 (CONCURRENT_REQUEST_ID
291 ,HEADER_INTERFACE_ROWID
292 ,INTERFACE_SOURCE_TABLE
293 ,INTERFACE_ID
294 ,ERROR_MSG
295 ,MSG_TOKENS)
296 VALUES (G_WORKER_REQ_ID
297 ,HEADER_INTERFACE_ROWID
298 ,'OKS_HEADERS_INTERFACE'
299 ,HEADER_INTERFACE_ID
300 ,'OKS_IMP_HDR_INVALID_CONV_TYPE'
301 ,NULL)
302 WHEN (CONTRACT_CURRENCY_CODE IS NOT NULL
303 AND CONTRACT_CURRENCY_CODE <> OU_LEDGER_CUR_CODE
304 AND CURRENCY_CONVERSION_TYPE = 'User'
305 AND (CURRENCY_CONVERSION_RATE IS NULL OR CURRENCY_CONVERSION_RATE <= 0)) THEN
306 INTO OKS_INT_ERROR_STG_TEMP
307 (CONCURRENT_REQUEST_ID
308 ,HEADER_INTERFACE_ROWID
309 ,INTERFACE_SOURCE_TABLE
310 ,INTERFACE_ID
311 ,ERROR_MSG
312 ,MSG_TOKENS)
313 VALUES (G_WORKER_REQ_ID
314 ,HEADER_INTERFACE_ROWID
315 ,'OKS_HEADERS_INTERFACE'
316 ,HEADER_INTERFACE_ID
317 ,'OKS_IMP_HDR_INVALID_CONV_RATE'
318 ,NULL)
319 WHEN (CONTRACT_CURRENCY_CODE IS NOT NULL
320 AND CONTRACT_CURRENCY_CODE <> OU_LEDGER_CUR_CODE
321 AND CURRENCY_CONVERSION_DATE IS NULL) THEN
322 INTO OKS_INT_ERROR_STG_TEMP
323 (CONCURRENT_REQUEST_ID
324 ,HEADER_INTERFACE_ROWID
325 ,INTERFACE_SOURCE_TABLE
326 ,INTERFACE_ID
327 ,ERROR_MSG
328 ,MSG_TOKENS)
329 VALUES (G_WORKER_REQ_ID
330 ,HEADER_INTERFACE_ROWID
331 ,'OKS_HEADERS_INTERFACE'
332 ,HEADER_INTERFACE_ID
333 ,'OKS_IMP_HDR_INVALID_CONV_DATE'
334 ,NULL)
335 WHEN (CONTRACT_CURRENCY_CODE IS NOT NULL
336 AND CONTRACT_CURRENCY_CODE <> OU_LEDGER_CUR_CODE
337 AND CURRENCY_CONVERSION_TYPE <> 'User'
338 AND HDR_CURRENCY_CONVERSION_RATE IS NOT NULL) THEN
339 INTO OKS_INT_ERROR_STG_TEMP
340 (CONCURRENT_REQUEST_ID
341 ,HEADER_INTERFACE_ROWID
342 ,INTERFACE_SOURCE_TABLE
343 ,INTERFACE_ID
344 ,ERROR_MSG
345 ,MSG_TOKENS)
346 VALUES (G_WORKER_REQ_ID
347 ,HEADER_INTERFACE_ROWID
348 ,'OKS_HEADERS_INTERFACE'
349 ,HEADER_INTERFACE_ID
350 ,'OKS_IMP_HDR_NULL_CONV_RATE'
351 ,NULL)
352 WHEN (CONTRACT_CURRENCY_CODE IS NOT NULL
353 AND CONTRACT_CURRENCY_CODE = OU_LEDGER_CUR_CODE
354 AND (CURRENCY_CONVERSION_TYPE IS NOT NULL
355 OR HDR_CURRENCY_CONVERSION_RATE IS NOT NULL
356 OR CURRENCY_CONVERSION_DATE IS NOT NULL)) THEN
357 INTO OKS_INT_ERROR_STG_TEMP
358 (CONCURRENT_REQUEST_ID
359 ,HEADER_INTERFACE_ROWID
360 ,INTERFACE_SOURCE_TABLE
361 ,INTERFACE_ID
362 ,ERROR_MSG
363 ,MSG_TOKENS)
364 VALUES (G_WORKER_REQ_ID
365 ,HEADER_INTERFACE_ROWID
366 ,'OKS_HEADERS_INTERFACE'
367 ,HEADER_INTERFACE_ID
368 ,'OKS_IMP_HDR_NULL_CONV'
369 ,NULL)
370 WHEN (PRICE_LIST_ID IS NULL) THEN
371 INTO OKS_INT_ERROR_STG_TEMP
372 (CONCURRENT_REQUEST_ID
373 ,HEADER_INTERFACE_ROWID
374 ,INTERFACE_SOURCE_TABLE
375 ,INTERFACE_ID
376 ,ERROR_MSG
377 ,MSG_TOKENS)
378 VALUES (G_WORKER_REQ_ID
379 ,HEADER_INTERFACE_ROWID
380 ,'OKS_HEADERS_INTERFACE'
381 ,HEADER_INTERFACE_ID
382 ,'OKS_IMP_HDR_INVALID_PRICELIST'
383 ,NULL)
384 WHEN (PAYMENT_TERMS_ID IS NULL) THEN
385 INTO OKS_INT_ERROR_STG_TEMP
386 (CONCURRENT_REQUEST_ID
387 ,HEADER_INTERFACE_ROWID
388 ,INTERFACE_SOURCE_TABLE
389 ,INTERFACE_ID
390 ,ERROR_MSG
391 ,MSG_TOKENS)
392 VALUES (G_WORKER_REQ_ID
393 ,HEADER_INTERFACE_ROWID
394 ,'OKS_HEADERS_INTERFACE'
395 ,HEADER_INTERFACE_ID
396 ,'OKS_IMP_HDR_INVALID_PAYTERM'
397 ,NULL)
398 WHEN (HDR_PAYMENT_INSTRUCTION IS NOT NULL AND PAYMENT_INSTRUCTION IS NULL) THEN
399 INTO OKS_INT_ERROR_STG_TEMP
400 (CONCURRENT_REQUEST_ID
401 ,HEADER_INTERFACE_ROWID
402 ,INTERFACE_SOURCE_TABLE
403 ,INTERFACE_ID
404 ,ERROR_MSG
405 ,MSG_TOKENS)
406 VALUES (G_WORKER_REQ_ID
407 ,HEADER_INTERFACE_ROWID
408 ,'OKS_HEADERS_INTERFACE'
409 ,HEADER_INTERFACE_ID
410 ,'OKS_IMP_HDR_INVALID_PAYINSTR'
411 ,NULL)
412 WHEN (INVOICING_RULE_ID IS NULL) THEN
413 INTO OKS_INT_ERROR_STG_TEMP
414 (CONCURRENT_REQUEST_ID
415 ,HEADER_INTERFACE_ROWID
416 ,INTERFACE_SOURCE_TABLE
417 ,INTERFACE_ID
418 ,ERROR_MSG
419 ,MSG_TOKENS)
420 VALUES (G_WORKER_REQ_ID
421 ,HEADER_INTERFACE_ROWID
422 ,'OKS_HEADERS_INTERFACE'
423 ,HEADER_INTERFACE_ID
424 ,'OKS_IMP_HDR_INVALID_INVRULE'
425 ,NULL)
426 WHEN (HDR_PAYMENT_METHOD_CODE IS NOT NULL AND PAYMENT_METHOD_CODE IS NULL) THEN
427 INTO OKS_INT_ERROR_STG_TEMP
428 (CONCURRENT_REQUEST_ID
429 ,HEADER_INTERFACE_ROWID
430 ,INTERFACE_SOURCE_TABLE
431 ,INTERFACE_ID
432 ,ERROR_MSG
433 ,MSG_TOKENS)
434 VALUES (G_WORKER_REQ_ID
435 ,HEADER_INTERFACE_ROWID
436 ,'OKS_HEADERS_INTERFACE'
437 ,HEADER_INTERFACE_ID
438 ,'OKS_IMP_HDR_INVALID_PMC'
439 ,NULL)
440 WHEN ((PAYMENT_METHOD_CODE IS NOT NULL OR HDR_COMMITMENT_ID IS NOT NULL) AND COMMITMENT_ID IS NULL) THEN
441 INTO OKS_INT_ERROR_STG_TEMP
442 (CONCURRENT_REQUEST_ID
443 ,HEADER_INTERFACE_ROWID
444 ,INTERFACE_SOURCE_TABLE
445 ,INTERFACE_ID
446 ,ERROR_MSG
447 ,MSG_TOKENS)
448 VALUES (G_WORKER_REQ_ID
449 ,HEADER_INTERFACE_ROWID
450 ,'OKS_HEADERS_INTERFACE'
451 ,HEADER_INTERFACE_ID
452 ,'OKS_IMP_HDR_INVALID_COMMIT'
453 ,NULL)
454 WHEN (ACCOUNTING_RULE_ID IS NULL) THEN
455 INTO OKS_INT_ERROR_STG_TEMP
456 (CONCURRENT_REQUEST_ID
457 ,HEADER_INTERFACE_ROWID
458 ,INTERFACE_SOURCE_TABLE
459 ,INTERFACE_ID
460 ,ERROR_MSG
461 ,MSG_TOKENS)
462 VALUES (G_WORKER_REQ_ID
463 ,HEADER_INTERFACE_ROWID
464 ,'OKS_HEADERS_INTERFACE'
465 ,HEADER_INTERFACE_ID
466 ,'OKS_IMP_HDR_INVALID_ACCRULE'
467 ,NULL)
468 WHEN (PRICING_METHOD = 'LST' AND RENEWAL_PRICE_LIST_ID IS NULL)
469 OR (RENEWAL_PRICE_LIST_ID IS NOT NULL AND (PRICING_METHOD NOT IN ('LST', 'PCT') OR PRICING_METHOD IS NULL ) ) THEN
470 INTO OKS_INT_ERROR_STG_TEMP
471 (CONCURRENT_REQUEST_ID
472 ,HEADER_INTERFACE_ROWID
473 ,INTERFACE_SOURCE_TABLE
474 ,INTERFACE_ID
475 ,ERROR_MSG
476 ,MSG_TOKENS)
477 VALUES (G_WORKER_REQ_ID
478 ,HEADER_INTERFACE_ROWID
479 ,'OKS_HEADERS_INTERFACE'
480 ,HEADER_INTERFACE_ID
481 ,'OKS_IMP_HDR_RENPL_MANDATORY'
482 ,NULL)
483 WHEN (REN_PL_PROVIDED = 'Y' AND RENEWAL_PRICE_LIST_ID IS NULL) THEN
484 INTO OKS_INT_ERROR_STG_TEMP
485 (CONCURRENT_REQUEST_ID
486 ,HEADER_INTERFACE_ROWID
487 ,INTERFACE_SOURCE_TABLE
488 ,INTERFACE_ID
489 ,ERROR_MSG
490 ,MSG_TOKENS)
491 VALUES (G_WORKER_REQ_ID
492 ,HEADER_INTERFACE_ROWID
493 ,'OKS_HEADERS_INTERFACE'
494 ,HEADER_INTERFACE_ID
495 ,'OKS_IMP_HDR_INVALID_RENPL'
496 ,NULL)
497 WHEN (CUSTOMER_PARTY_ID IS NULL) THEN
498 INTO OKS_INT_ERROR_STG_TEMP
499 (CONCURRENT_REQUEST_ID
500 ,HEADER_INTERFACE_ROWID
501 ,INTERFACE_SOURCE_TABLE
502 ,INTERFACE_ID
503 ,ERROR_MSG
504 ,MSG_TOKENS)
505 VALUES (G_WORKER_REQ_ID
506 ,HEADER_INTERFACE_ROWID
507 ,'OKS_HEADERS_INTERFACE'
508 ,HEADER_INTERFACE_ID
509 ,'OKS_IMP_HDR_INVALID_CTPARTY'
510 ,NULL)
511 WHEN (TP_PROVIDED = 'Y' AND THIRD_PARTY_ID IS NULL) THEN
512 INTO OKS_INT_ERROR_STG_TEMP
513 (CONCURRENT_REQUEST_ID
514 ,HEADER_INTERFACE_ROWID
515 ,INTERFACE_SOURCE_TABLE
516 ,INTERFACE_ID
517 ,ERROR_MSG
518 ,MSG_TOKENS)
519 VALUES (G_WORKER_REQ_ID
520 ,HEADER_INTERFACE_ROWID
521 ,'OKS_HEADERS_INTERFACE'
522 ,HEADER_INTERFACE_ID
523 ,'OKS_IMP_HDR_INVALID_TPPARTY'
524 ,NULL)
525 WHEN (CONTRACT_GROUP_ID IS NULL) THEN
526 INTO OKS_INT_ERROR_STG_TEMP
527 (CONCURRENT_REQUEST_ID
528 ,HEADER_INTERFACE_ROWID
529 ,INTERFACE_SOURCE_TABLE
530 ,INTERFACE_ID
531 ,ERROR_MSG
532 ,MSG_TOKENS)
533 VALUES (G_WORKER_REQ_ID
534 ,HEADER_INTERFACE_ROWID
535 ,'OKS_HEADERS_INTERFACE'
536 ,HEADER_INTERFACE_ID
537 ,'OKS_IMP_HDR_INVALID_CG'
538 ,NULL)
539 WHEN (AGREEMENT_PROVIDED = 'Y' AND AGREEMENT_ID IS NULL) THEN
540 INTO OKS_INT_ERROR_STG_TEMP
541 (CONCURRENT_REQUEST_ID
542 ,HEADER_INTERFACE_ROWID
543 ,INTERFACE_SOURCE_TABLE
544 ,INTERFACE_ID
545 ,ERROR_MSG
546 ,MSG_TOKENS)
547 VALUES (G_WORKER_REQ_ID
548 ,HEADER_INTERFACE_ROWID
549 ,'OKS_HEADERS_INTERFACE'
550 ,HEADER_INTERFACE_ID
551 ,'OKS_IMP_HDR_INVALID_PAGRMT'
552 ,NULL)
553 WHEN (SALESPERSON_PROVIDED = 'Y' AND SALESPERSON_ID IS NULL) THEN
554 INTO OKS_INT_ERROR_STG_TEMP
555 (CONCURRENT_REQUEST_ID
556 ,HEADER_INTERFACE_ROWID
557 ,INTERFACE_SOURCE_TABLE
558 ,INTERFACE_ID
559 ,ERROR_MSG
560 ,MSG_TOKENS)
561 VALUES (G_WORKER_REQ_ID
562 ,HEADER_INTERFACE_ROWID
563 ,'OKS_HEADERS_INTERFACE'
564 ,HEADER_INTERFACE_ID
565 ,'OKS_IMP_HDR_INVALID_SALEPERS'
566 ,NULL)
567 WHEN (CONTRACT_ADMIN_PROVIDED = 'Y' AND CUSTOMER_CONTRACT_ADMIN_ID IS NULL) THEN
568 INTO OKS_INT_ERROR_STG_TEMP
569 (CONCURRENT_REQUEST_ID
570 ,HEADER_INTERFACE_ROWID
571 ,INTERFACE_SOURCE_TABLE
572 ,INTERFACE_ID
573 ,ERROR_MSG
574 ,MSG_TOKENS)
575 VALUES (G_WORKER_REQ_ID
576 ,HEADER_INTERFACE_ROWID
577 ,'OKS_HEADERS_INTERFACE'
578 ,HEADER_INTERFACE_ID
579 ,'OKS_IMP_HDR_INVALID_CCADMIN'
580 ,NULL)
581 WHEN DESCRIPTION IS NULL THEN
582 INTO OKS_INT_ERROR_STG_TEMP
583 (CONCURRENT_REQUEST_ID
584 ,HEADER_INTERFACE_ROWID
585 ,INTERFACE_SOURCE_TABLE
586 ,INTERFACE_ID
587 ,ERROR_MSG
588 ,MSG_TOKENS)
589 VALUES (G_WORKER_REQ_ID
590 ,HEADER_INTERFACE_ROWID
591 ,'OKS_HEADERS_INTERFACE'
592 ,HEADER_INTERFACE_ID
593 ,'OKS_IMP_HDR_INVALID_DESC'
594 ,NULL)
595 WHEN FULLY_BILLED IS NULL THEN
596 INTO OKS_INT_ERROR_STG_TEMP
597 (CONCURRENT_REQUEST_ID
598 ,HEADER_INTERFACE_ROWID
599 ,INTERFACE_SOURCE_TABLE
600 ,INTERFACE_ID
601 ,ERROR_MSG
602 ,MSG_TOKENS)
603 VALUES (G_WORKER_REQ_ID
604 ,HEADER_INTERFACE_ROWID
605 ,'OKS_HEADERS_INTERFACE'
606 ,HEADER_INTERFACE_ID
607 ,'OKS_IMP_HDR_INVALID_FULB'
608 ,NULL)
609 WHEN FULLY_BILLED_INV ='Y' THEN
610 INTO OKS_INT_ERROR_STG_TEMP
611 (CONCURRENT_REQUEST_ID
612 ,HEADER_INTERFACE_ROWID
613 ,INTERFACE_SOURCE_TABLE
614 ,INTERFACE_ID
615 ,ERROR_MSG
616 ,MSG_TOKENS)
617 VALUES (G_WORKER_REQ_ID
618 ,HEADER_INTERFACE_ROWID
619 ,'OKS_HEADERS_INTERFACE'
620 ,HEADER_INTERFACE_ID
621 ,'OKS_IMP_HDR_INV_FULB_STS'
622 ,NULL)
623
624 SELECT OHI.HEADER_INTERFACE_ID HEADER_INTERFACE_ID
625 ,OHI.ROWID HEADER_INTERFACE_ROWID
626 ,rtrim(OHI.CONTRACT_NUMBER) CONTRACT_NUMBER
627 ,rtrim(OHI.CONTRACT_NUMBER_MODIFIER) CONTRACT_NUMBER_MODIFIER
628 ,OHI.CATEGORY CATEGORY
629 ,OHI.STATUS_CODE STATUS_CODE
630 ,OHI.START_DATE START_DATE
631 ,OHI.END_DATE END_DATE
632 ,rtrim(OHI.KNOWN_AS) KNOWN_AS
633 ,rtrim(OHI.DESCRIPTION) DESCRIPTION
634 ,(CASE WHEN (COUNT (DISTINCT HOU.ROWID) OVER (PARTITION BY OHI.ROWID) = 1
635 AND COUNT (DISTINCT HOI.ROWID) OVER (PARTITION BY OHI.ROWID) = 1)
636 THEN OHI.OPERATING_UNIT_ID
637 ELSE NULL
638 END) OPERATING_UNIT_ID
639 ,(CASE WHEN COUNT(DISTINCT BTSUI.ROWID) OVER (PARTITION BY OHI.ROWID) = 1
640 THEN OHI.BILL_TO_SITE_USAGE_ID
641 ELSE NULL
642 END) BILL_TO_SITE_USAGE_ID
643 ,(CASE WHEN COUNT(DISTINCT STSUI.ROWID) OVER (PARTITION BY OHI.ROWID) = 1
644 THEN OHI.SHIP_TO_SITE_USAGE_ID
645 ELSE NULL
646 END) SHIP_TO_SITE_USAGE_ID
647 ,(CASE WHEN COUNT(DISTINCT FCUR.ROWID) OVER (PARTITION BY OHI.ROWID) = 1
648 THEN OHI.CONTRACT_CURRENCY_CODE
649 ELSE NULL
650 END) CONTRACT_CURRENCY_CODE
651 ,(CASE WHEN COUNT(DISTINCT GLCT.ROWID) OVER (PARTITION BY OHI.ROWID) = 1
652 THEN OHI.CURRENCY_CONVERSION_TYPE
653 ELSE NULL
654 END) CURRENCY_CONVERSION_TYPE
655 ,trunc(OHI.CURRENCY_CONVERSION_DATE) CURRENCY_CONVERSION_DATE
656 ,(CASE WHEN OHI.CURRENCY_CONVERSION_TYPE = 'User' THEN OHI.CURRENCY_CONVERSION_RATE
657 ELSE GLDR.CONVERSION_RATE
658 END) CURRENCY_CONVERSION_RATE
659 ,OHI.CURRENCY_CONVERSION_RATE HDR_CURRENCY_CONVERSION_RATE
660 ,(CASE WHEN NAMEID_Q.QPI_COUNT = 1 THEN NAMEID_Q.QPI_ID
661 WHEN NAMEID_Q.QPN_COUNT = 1 THEN NAMEID_Q.QPN_ID
662 ELSE NULL
663 END) PRICE_LIST_ID
664 ,(CASE WHEN NAMEID_Q.PTI_COUNT = 1 THEN NAMEID_Q.PTI_ID
665 WHEN NAMEID_Q.PTN_COUNT = 1 THEN NAMEID_Q.PTN_ID
666 ELSE NULL
667 END) PAYMENT_TERMS_ID
668 ,(CASE WHEN COUNT(DISTINCT FL.ROWID) OVER (PARTITION BY OHI.ROWID) = 1
669 THEN OHI.PAYMENT_INSTRUCTION
670 ELSE NULL
671 END) PAYMENT_INSTRUCTION
672 ,OHI.PAYMENT_INSTRUCTION HDR_PAYMENT_INSTRUCTION
673 ,OHI.PO_REQUIRED PO_REQUIRED
674 ,rtrim(OHI.PAYMENT_INSTRUCTION_DETAILS) PAYMENT_INSTRUCTION_DETAILS
675 ,(CASE WHEN NAMEID_Q.IRI_COUNT = 1 THEN NAMEID_Q.IRI_ID
676 WHEN NAMEID_Q.IRN_COUNT = 1 THEN NAMEID_Q.IRN_ID
677 ELSE NULL
678 END) INVOICING_RULE_ID
679 ,OHI.SERVICE_CHRG_PREPAY_REQ SERVICE_CHRG_PREPAY_REQ
680 ,OHI.RENEWAL_PROCESS RENEWAL_PROCESS
681 ,OHI.APPROVAL_REQUIRED APPROVAL_REQUIRED
682 ,OHI.RENEW_UP_TO RENEW_UP_TO
683 ,OHI.DATE_APPROVED DATE_APPROVED
684 ,OHI.DATE_SIGNED DATE_SIGNED
685 ,OHI.DATE_CANCELED DATE_CANCELED
686 ,OHI.CANCELLATION_REASON CANCELLATION_REASON
687 ,OHI.QA_CHECKLIST QA_CHECKLIST
688 ,(CASE WHEN COUNT(DISTINCT PMC.ROWID) OVER (PARTITION BY OHI.ROWID) = 1
689 THEN OHI.PAYMENT_METHOD_CODE
690 ELSE NULL
691 END) PAYMENT_METHOD_CODE
692 ,OHI.PAYMENT_METHOD_CODE HDR_PAYMENT_METHOD_CODE
693 ,(CASE WHEN COUNT(DISTINCT CNI.ROWID) OVER (PARTITION BY OHI.ROWID) = 1
694 THEN OHI.COMMITMENT_ID
695 ELSE NULL
696 END) COMMITMENT_ID
697 ,OHI.COMMITMENT_ID HDR_COMMITMENT_ID
698 ,OHI.TAX_EXEMPTION_CONTROL TAX_EXEMPTION_CONTROL
699 ,OHI.TAX_EXEMPTION_NUMBER TAX_EXEMPTION_NUMBER
700 ,OHI.BILL_SERVICES BILL_SERVICES
701 ,OHI.BILLING_TRANSACTION_TYPE_ID BILLING_TRANSACTION_TYPE_ID
702 ,(CASE WHEN ARI_ID_VAL_FLAG = 'Y' AND NAMEID_Q.ARI_COUNT = 1 THEN NAMEID_Q.ARI_ID
703 WHEN ARN_ID_VAL_FLAG = 'Y' AND NAMEID_Q.ARN_COUNT = 1 THEN NAMEID_Q.ARN_ID
704 ELSE NULL
705 END) ACCOUNTING_RULE_ID
706 ,OHI.HOLD_CREDITS HOLD_CREDITS
707 ,OHI.SUMMARY_PRINT SUMMARY_PRINT
708 ,OHI.SUMMARY_TRANSACTIONS SUMMARY_TRANSACTIONS
709 ,OHI.SERVICE_CHARGES_PO_REQUIRED SERVICE_CHARGES_PO_REQUIRED
710 ,rtrim(OHI.SERVICE_CHARGES_PO_NUMBER) SERVICE_CHARGES_PO_NUMBER
711 ,OHI.PRICING_METHOD PRICING_METHOD
712 ,(CASE WHEN NAMEID_Q.RNPLI_COUNT = 1 THEN NAMEID_Q.RNPLI_ID
713 WHEN NAMEID_Q.RNPLN_COUNT = 1 THEN NAMEID_Q.RNPLN_ID
714 ELSE NULL
715 END) RENEWAL_PRICE_LIST_ID
716 ,(CASE WHEN (OHI.RENEWAL_PRICE_LIST_ID IS NOT NULL OR OHI.RENEWAL_PRICE_LIST_NAME IS NOT NULL)
717 THEN 'Y'
718 ELSE 'N'
719 END) REN_PL_PROVIDED
720 ,OHI.RENEWAL_MARKUP RENEWAL_MARKUP
721 ,OHI.RENEWAL_BILLING_PROFILE_ID RENEWAL_BILLING_PROFILE_ID
722 ,rtrim(OHI.RENEWAL_PO_NUMBER) RENEWAL_PO_NUMBER
723 ,OHI.RENEWAL_PO_REQUIRED RENEWAL_PO_REQUIRED
724 ,OHI.RENEWAL_GRACE_DURATION RENEWAL_GRACE_DURATION
725 ,OHI.RENEWAL_GRACE_PERIOD RENEWAL_GRACE_PERIOD
726 ,OHI.RENEWAL_ESTIMATED_PERCENT RENEWAL_ESTIMATED_PERCENT
727 ,OHI.RENEWAL_ESTIMATED_DURATION RENEWAL_ESTIMATED_DURATION
728 ,OHI.RENEWAL_ESTIMATED_PERIOD RENEWAL_ESTIMATED_PERIOD
729 ,OHI.QUOTE_TO_PARTY_SITE QUOTE_TO_PARTY_SITE
730 ,OHI.QUOTE_TO_CONTACT QUOTE_TO_CONTACT
731 ,OHI.QUOTE_TO_PHONE QUOTE_TO_PHONE
732 ,OHI.QUOTE_TO_FAX QUOTE_TO_FAX
733 ,OHI.QUOTE_TO_EMAIL QUOTE_TO_EMAIL
734 ,OHI.GRACE_DURATION GRACE_DURATION
735 ,OHI.GRACE_PERIOD GRACE_PERIOD
736 ,OHI.ESTIMATION_PERCENT ESTIMATION_PERCENT
737 ,OHI.ESTIMATION_DATE ESTIMATION_DATE
738 ,OHI.FOLLOW_UP_DUE_DATE FOLLOW_UP_DUE_DATE
739 ,OHI.FOLLOW_UP_ACTION FOLLOW_UP_ACTION
740 ,(CASE WHEN NAMEID_Q.HPCUSTI_COUNT = 1 THEN NAMEID_Q.HPCUSTI_ID
741 WHEN NAMEID_Q.HPCUSTNUM_COUNT = 1 THEN NAMEID_Q.HPCUSTNUM_ID
742 WHEN NAMEID_Q.HPCUSTN_COUNT = 1 THEN NAMEID_Q.HPCUSTN_ID
743 ELSE NULL
744 END) CUSTOMER_PARTY_ID
745 ,(CASE WHEN NAMEID_Q.HPTPI_COUNT = 1 THEN NAMEID_Q.HPTPI_ID
746 WHEN NAMEID_Q.HPTPNUM_COUNT = 1 THEN NAMEID_Q.HPTPNUM_ID
747 WHEN NAMEID_Q.HPTPN_COUNT = 1 THEN NAMEID_Q.HPTPN_ID
748 ELSE NULL
749 END) THIRD_PARTY_ID
750 ,(CASE WHEN (OHI.THIRD_PARTY_ID IS NOT NULL OR OHI.THIRD_PARTY_NUMBER IS NOT NULL OR OHI.THIRD_PARTY_NAME IS NOT NULL)
751 THEN 'Y'
752 ELSE 'N'
753 END) TP_PROVIDED
754 ,(CASE WHEN CGI_ID_VAL_FLAG = 'Y' AND NAMEID_Q.CGI_COUNT = 1 THEN NAMEID_Q.CGI_ID
755 WHEN CGN_ID_VAL_FLAG = 'Y' AND NAMEID_Q.CGN_COUNT = 1 THEN NAMEID_Q.CGN_ID
756 ELSE NULL
757 END) CONTRACT_GROUP_ID
758 ,OHI.APPROVAL_PROCESS_ID APPROVAL_PROCESS_ID
759 ,(CASE WHEN NAMEID_Q.AGI_COUNT = 1 THEN NAMEID_Q.AGI_ID
760 WHEN NAMEID_Q.AGN_COUNT = 1 THEN NAMEID_Q.AGN_ID
761 ELSE NULL
762 END) AGREEMENT_ID
763 ,(CASE WHEN (OHI.AGREEMENT_ID IS NOT NULL OR OHI.AGREEMENT_NAME IS NOT NULL)
764 THEN 'Y'
765 ELSE 'N'
766 END) AGREEMENT_PROVIDED
767 ,(CASE WHEN NAMEID_Q.SPI_COUNT = 1 THEN NAMEID_Q.SPI_ID
768 WHEN NAMEID_Q.SPN_COUNT = 1 THEN NAMEID_Q.SPN_ID
769 ELSE NULL
770 END) SALESPERSON_ID
771 ,(CASE WHEN (OHI.SALESPERSON_ID IS NOT NULL OR OHI.SALESPERSON_NAME IS NOT NULL)
772 THEN 'Y'
773 ELSE 'N'
774 END) SALESPERSON_PROVIDED
775 ,(CASE WHEN NAMEID_Q.CCAI_COUNT = 1 THEN NAMEID_Q.CCAI_ID
776 WHEN NAMEID_Q.CCAN_COUNT = 1 THEN NAMEID_Q.CCAN_ID
777 ELSE NULL
778 END) CUSTOMER_CONTRACT_ADMIN_ID
779 ,(CASE WHEN (OHI.CUSTOMER_CONTRACT_ADMIN_ID IS NOT NULL OR OHI.CUSTOMER_CONTRACT_ADMIN_NAME IS NOT NULL)
780 THEN 'Y'
781 ELSE 'N'
782 END) CONTRACT_ADMIN_PROVIDED
783 ,OHI.INV_ORGANIZATION_ID INV_ORGANIZATION_ID
784 ,OHI.EXEMPT_REASON_CODE EXEMPT_REASON_CODE
785 ,NAMEID_Q.OU_LEDGER_CUR_CODE OU_LEDGER_CUR_CODE
786 ,OHI.SALESGROUP_ID SALESGROUP_ID
787 ,(CASE WHEN OHI.FULLY_BILLED IN ('Y', 'N') THEN OHI.FULLY_BILLED ELSE NULL END) FULLY_BILLED
788 ,(CASE WHEN OHI.FULLY_BILLED ='Y' AND OHI.STATUS_CODE IN ('ENTERED','CANCELLED') THEN 'Y'
789 ELSE 'N' END) FULLY_BILLED_INV
790 ,OHI.SOURCE SOURCE
791 ,rtrim(OHI.DOCUMENT) DOCUMENT
792 FROM OKS_HEADERS_INTERFACE OHI
793 ,HR_ALL_ORGANIZATION_UNITS HOU
794 ,HR_ORGANIZATION_INFORMATION HOI
795 ,HZ_CUST_SITE_USES_ALL BTSUI
796 ,HZ_CUST_SITE_USES_ALL STSUI
797 ,FND_CURRENCIES FCUR
798 ,GL_DAILY_CONVERSION_TYPES GLCT
799 ,GL_DAILY_RATES GLDR
800 ,FND_LOOKUPS FL
801 ,RA_CUSTOMER_TRX_ALL CNI
802 ,FND_LOOKUPS PMC
803 ,(SELECT distinct(HDI.HEADER_INTERFACE_ID) -- to avoid duplicate joins with tables when some names match with multiple records
804 ,COUNT(DISTINCT HPCUSTI.ROWID) OVER (PARTITION BY HDI.ROWID) HPCUSTI_COUNT
805 ,MAX(HDI.CUSTOMER_PARTY_ID) OVER (PARTITION BY HDI.ROWID) HPCUSTI_ID -- CUSTOMER PARTY ID Based on Id
806 ,COUNT(DISTINCT HPCUSTN.ROWID) OVER (PARTITION BY HDI.ROWID) HPCUSTN_COUNT
807 ,MAX(HPCUSTN.PARTY_ID) OVER (PARTITION BY HDI.ROWID) HPCUSTN_ID -- CUSTOMER PARTY ID Based on name
808 ,COUNT(DISTINCT HPCUSTNUM.ROWID) OVER (PARTITION BY HDI.ROWID) HPCUSTNUM_COUNT
809 ,MAX(HPCUSTNUM.PARTY_ID) OVER (PARTITION BY HDI.ROWID) HPCUSTNUM_ID -- CUSTOMER PARTY ID Based on number
810 ,COUNT(DISTINCT HPTPI.ROWID) OVER (PARTITION BY HDI.ROWID) HPTPI_COUNT
811 ,MAX(HDI.THIRD_PARTY_ID) OVER (PARTITION BY HDI.ROWID) HPTPI_ID -- THIRD PARTY ID Based on Id
812 ,COUNT(DISTINCT HPTPN.ROWID) OVER (PARTITION BY HDI.ROWID) HPTPN_COUNT
813 ,MAX(HPTPN.PARTY_ID) OVER (PARTITION BY HDI.ROWID) HPTPN_ID -- THIRD PARTY ID Based on name
814 ,COUNT(DISTINCT HPTPNUM.ROWID) OVER (PARTITION BY HDI.ROWID) HPTPNUM_COUNT
815 ,MAX(HPTPNUM.PARTY_ID) OVER (PARTITION BY HDI.ROWID) HPTPNUM_ID -- THIRD PARTY ID Based on number
816 ,COUNT(DISTINCT SPI.ROWID) OVER (PARTITION BY HDI.ROWID) SPI_COUNT
817 ,MAX(HDI.SALESPERSON_ID) OVER (PARTITION BY HDI.ROWID) SPI_ID -- Sales Person Id Based on Id
818 ,COUNT(DISTINCT SPN.ROW_ID) OVER (PARTITION BY HDI.ROWID) SPN_COUNT
819 ,MAX(SPN.SALESREP_ID) OVER (PARTITION BY HDI.ROWID) SPN_ID -- Sales Person Id Based on Name
820 ,COUNT(DISTINCT AGI.ROWID) OVER (PARTITION BY HDI.ROWID) AGI_COUNT
821 ,MAX(HDI.AGREEMENT_ID) OVER (PARTITION BY HDI.ROWID) AGI_ID -- Agreement Id based on Id
822 ,COUNT(DISTINCT AGTLN.ROWID) OVER (PARTITION BY HDI.ROWID) AGN_COUNT
823 ,MAX(AGTLN.AGREEMENT_ID) OVER (PARTITION BY HDI.ROWID) AGN_ID -- Agreement Id based on Name
824 ,COUNT(DISTINCT QPI.ROWID) OVER (PARTITION BY HDI.ROWID) QPI_COUNT
825 ,MAX(HDI.PRICE_LIST_ID) OVER (PARTITION BY HDI.ROWID) QPI_ID -- PRICE LIST ID based on Id
826 ,COUNT(DISTINCT QPTLN.ROW_ID) OVER (PARTITION BY HDI.ROWID) QPN_COUNT
827 ,MAX(QPTLN.LIST_HEADER_ID) OVER (PARTITION BY HDI.ROWID) QPN_ID -- PRICE LIST ID based on Name
828 ,COUNT(DISTINCT RNPLI.ROWID) OVER (PARTITION BY HDI.ROWID) RNPLI_COUNT
829 ,MAX(HDI.RENEWAL_PRICE_LIST_ID) OVER (PARTITION BY HDI.ROWID) RNPLI_ID -- RENEWAL PRICE LIST ID based on Id
830 ,COUNT(DISTINCT RNPLN.ROW_ID) OVER (PARTITION BY HDI.ROWID) RNPLN_COUNT
831 ,MAX(RNPLN.LIST_HEADER_ID) OVER (PARTITION BY HDI.ROWID) RNPLN_ID -- RENEWAL PRICE LIST ID based on Name
832 ,COUNT(DISTINCT ARI.ROWID) OVER (PARTITION BY HDI.ROWID) ARI_COUNT
833 ,MAX(HDI.ACCOUNTING_RULE_ID) OVER (PARTITION BY HDI.ROWID) ARI_ID -- Accounting Rule ID based on Id
834 ,(CASE WHEN ARI.TYPE IN ('A','ACC_DUR','PP_DR_ALL','PP_DR_PP')
835 THEN 'Y'
836 ELSE 'N'
837 END) ARI_ID_VAL_FLAG
838 ,COUNT(DISTINCT ARN.ROWID) OVER (PARTITION BY HDI.ROWID) ARN_COUNT
839 ,MAX(ARN.RULE_ID) OVER (PARTITION BY HDI.ROWID) ARN_ID -- Accounting Rule ID based on Name
840 ,(CASE WHEN ARN.TYPE IN ('A','ACC_DUR','PP_DR_ALL','PP_DR_PP')
841 THEN 'Y'
842 ELSE 'N'
843 END) ARN_ID_VAL_FLAG
844 ,COUNT(DISTINCT IRI.ROWID) OVER (PARTITION BY HDI.ROWID) IRI_COUNT
845 ,MAX(HDI.INVOICING_RULE_ID) OVER (PARTITION BY HDI.ROWID) IRI_ID -- Invoicing Rule ID based on Id
846 ,COUNT(DISTINCT IRN.ROWID) OVER (PARTITION BY HDI.ROWID) IRN_COUNT
847 ,MAX(IRN.RULE_ID) OVER (PARTITION BY HDI.ROWID) IRN_ID -- Invoicing Rule ID based on Name
848 ,COUNT(DISTINCT PTI.ROWID) OVER (PARTITION BY HDI.ROWID) PTI_COUNT
849 ,MAX(HDI.PAYMENT_TERMS_ID) OVER (PARTITION BY HDI.ROWID) PTI_ID -- Payment Terms ID based on Id
850 ,COUNT(DISTINCT PTTLN.ROWID) OVER (PARTITION BY HDI.ROWID) PTN_COUNT
851 ,MAX(PTTLN.TERM_ID) OVER (PARTITION BY HDI.ROWID) PTN_ID -- Payment Terms ID based on Name
852 ,COUNT(DISTINCT CGI.ROWID) OVER (PARTITION BY HDI.ROWID) CGI_COUNT
853 ,MAX(HDI.CONTRACT_GROUP_ID) OVER (PARTITION BY HDI.ROWID) CGI_ID -- Contracts Group ID based on Id
854 ,(CASE WHEN (CGI.PUBLIC_YN = 'Y' OR CGI.USER_ID = FND_GLOBAL.USER_ID)
855 THEN 'Y'
856 ELSE 'N'
857 END) CGI_ID_VAL_FLAG
858 ,COUNT(DISTINCT CGBN.ROWID) OVER (PARTITION BY HDI.ROWID) CGN_COUNT
859 ,MAX(CGBN.ID) OVER (PARTITION BY HDI.ROWID) CGN_ID -- Contracts Group ID based on Name /*modified for Bug:6839334*/
860 ,(CASE WHEN ((HDI.CONTRACT_GROUP_ID IS NULL) AND (CGBN.PUBLIC_YN = 'Y' OR CGBN.USER_ID = FND_GLOBAL.USER_ID))THEN 'Y'
861 WHEN ((HDI.CONTRACT_GROUP_ID IS NOT NULL) AND (CGI.PUBLIC_YN = 'Y' OR CGI.USER_ID = FND_GLOBAL.USER_ID))THEN 'Y'
862 ELSE 'N'
863 END) CGN_ID_VAL_FLAG
864 ,COUNT(DISTINCT CCAI.ROWID) OVER (PARTITION BY HDI.ROWID) CCAI_COUNT
865 ,MAX(HDI.CUSTOMER_CONTRACT_ADMIN_ID) OVER (PARTITION BY HDI.ROWID) CCAI_ID -- Customer Contracts Admin ID based on Id
866 ,COUNT(DISTINCT CCAN.ROWID) OVER (PARTITION BY HDI.ROWID) CCAN_COUNT
867 ,MAX(CCAN.ID1) OVER (PARTITION BY HDI.ROWID) CCAN_ID -- Customer Contracts Admin ID based on Name
868 ,GL.CURRENCY_CODE OU_LEDGER_CUR_CODE
869 ,HDI.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
870 ,trunc(HDI.CURRENCY_CONVERSION_DATE) CURRENCY_CONVERSION_DATE
871 ,HDI.CONTRACT_CURRENCY_CODE CONTRACT_CURRENCY_CODE
872 FROM OKS_HEADERS_INTERFACE HDI
873 ,HZ_PARTIES HPCUSTI
874 ,HZ_PARTIES HPCUSTN
875 ,HZ_PARTIES HPCUSTNUM
876 ,HZ_PARTIES HPTPI
877 ,HZ_PARTIES HPTPN
878 ,HZ_PARTIES HPTPNUM
879 ,JTF_RS_SALESREPS SPI
880 ,(SELECT SP.ROWID ROW_ID, SPTL.RESOURCE_NAME, SP.ORG_ID, SP.SALESREP_ID
881 FROM JTF_RS_RESOURCE_EXTNS_TL SPTL, JTF_RS_SALESREPS SP
882 WHERE SP.RESOURCE_ID = SPTL.RESOURCE_ID
883 AND SPTL.LANGUAGE = USERENV('LANG')) SPN
884 ,OE_AGREEMENTS_B AGI
885 ,OE_AGREEMENTS_TL AGTLN
886 ,QP_LIST_HEADERS_B QPI
887 ,(SELECT QP.ROWID ROW_ID, QPTL.NAME, QP.CURRENCY_CODE, QP.LIST_HEADER_ID
888 FROM QP_LIST_HEADERS_B QP, QP_LIST_HEADERS_TL QPTL
889 WHERE QP.LIST_HEADER_ID = QPTL.LIST_HEADER_ID
890 AND QPTL.LANGUAGE = USERENV('LANG')) QPTLN
891 ,QP_LIST_HEADERS_B RNPLI
892 ,(SELECT QP.ROWID ROW_ID, QPTL.NAME, QP.CURRENCY_CODE, QP.LIST_HEADER_ID
893 FROM QP_LIST_HEADERS_B QP, QP_LIST_HEADERS_TL QPTL
894 WHERE QP.LIST_HEADER_ID = QPTL.LIST_HEADER_ID
895 AND QPTL.LANGUAGE = USERENV('LANG')) RNPLN
896 ,RA_RULES ARI
897 ,RA_RULES ARN
898 ,RA_RULES IRI
899 ,RA_RULES IRN
900 ,RA_TERMS_B PTI
901 ,RA_TERMS_TL PTTLN
902 ,OKC_K_GROUPS_B CGI
903 ,OKC_K_GROUPS_TL CGTLN
904 ,OKC_K_GROUPS_B CGBN
905 ,OKX_PARTY_CONTACTS_V CCAI
906 ,OKX_PARTY_CONTACTS_V CCAN
907 ,HR_ORGANIZATION_INFORMATION HOIPL
908 ,GL_LEDGERS GL
909 WHERE HDI.CUSTOMER_PARTY_ID = HPCUSTI.PARTY_ID (+)
910 AND HDI.CUSTOMER_PARTY_NAME = HPCUSTN.PARTY_NAME (+)
911 AND HDI.CUSTOMER_PARTY_NUMBER = HPCUSTNUM.PARTY_NUMBER (+)
912 AND HDI.THIRD_PARTY_ID = HPTPI.PARTY_ID (+)
913 AND HDI.THIRD_PARTY_NAME = HPTPN.PARTY_NAME (+)
914 AND HDI.THIRD_PARTY_NUMBER = HPTPNUM.PARTY_NUMBER (+)
915 AND HDI.SALESPERSON_ID = SPI.SALESREP_ID (+)
916 AND HDI.OPERATING_UNIT_ID = SPI.ORG_ID (+)
917 AND HDI.SALESPERSON_NAME = SPN.RESOURCE_NAME (+)
918 AND HDI.OPERATING_UNIT_ID = SPN.ORG_ID (+)
919 AND HDI.AGREEMENT_ID = AGI.AGREEMENT_ID (+)
920 AND HDI.AGREEMENT_NAME = AGTLN.NAME (+)
921 AND AGTLN.LANGUAGE(+) = USERENV('LANG')
922 AND HDI.PRICE_LIST_ID = QPI.LIST_HEADER_ID (+)
923 AND HDI.CONTRACT_CURRENCY_CODE = QPI.CURRENCY_CODE (+)
924 AND HDI.PRICE_LIST_NAME = QPTLN.NAME (+)
925 AND HDI.CONTRACT_CURRENCY_CODE = QPTLN.CURRENCY_CODE (+)
926 --AND QPTLN.VERSION_NO (+) = 1
927 AND HDI.RENEWAL_PRICE_LIST_ID = RNPLI.LIST_HEADER_ID (+)
928 AND HDI.CONTRACT_CURRENCY_CODE = RNPLI.CURRENCY_CODE (+)
929 AND HDI.RENEWAL_PRICE_LIST_NAME = RNPLN.NAME (+)
930 AND HDI.CONTRACT_CURRENCY_CODE = RNPLN.CURRENCY_CODE (+)
931 --AND RNPLN.VERSION_NO (+) = 1
932 AND HDI.ACCOUNTING_RULE_ID = ARI.RULE_ID (+)
933 AND HDI.ACCOUNTING_RULE_NAME = ARN.NAME (+)
934 AND HDI.INVOICING_RULE_ID = IRI.RULE_ID (+)
935 AND IRI.TYPE(+) = 'I'
936 AND HDI.INVOICING_RULE_NAME = IRN.NAME (+)
937 AND IRN.TYPE(+) = 'I'
938 AND HDI.PAYMENT_TERMS_ID = PTI.TERM_ID (+)
939 AND HDI.PAYMENT_TERMS_NAME = PTTLN.NAME (+)
940 AND PTTLN.LANGUAGE(+) = USERENV('LANG')
941 AND HDI.CONTRACT_GROUP_ID = CGI.ID (+)
942 AND HDI.CONTRACT_GROUP_NAME = CGTLN.NAME (+)
943 AND CGTLN.LANGUAGE(+) = USERENV('LANG')
944 AND CGTLN.ID = CGBN.ID(+)
945 AND ((CGBN.PUBLIC_YN = 'Y' OR CGBN.USER_ID = FND_GLOBAL.USER_ID) OR (CGI.PUBLIC_YN = 'Y' OR CGI.USER_ID = FND_GLOBAL.USER_ID)) /*modified for bug:6839334*/
946 AND HDI.CUSTOMER_PARTY_ID = CCAI.PARTY_ID (+)
947 AND HDI.CUSTOMER_CONTRACT_ADMIN_ID = CCAI.ID1 (+)
948 AND HDI.CUSTOMER_PARTY_ID = CCAN.PARTY_ID (+)
949 AND HDI.CUSTOMER_CONTRACT_ADMIN_NAME = CCAN.NAME (+)
950 AND HDI.OPERATING_UNIT_ID = HOIPL.ORGANIZATION_ID (+)
951 AND HOIPL.ORG_INFORMATION_CONTEXT (+)= 'Operating Unit Information'
952 AND HOIPL.ORG_INFORMATION3 = GL.LEDGER_ID (+)
953 AND HDI.ROWID between P_rowid_from AND P_rowid_to
954 AND HDI.batch_id = P_batch_id
955 AND (HDI.INTERFACE_STATUS IS NULL OR HDI.INTERFACE_STATUS = 'R')) NAMEID_Q
956 WHERE OHI.HEADER_INTERFACE_ID = NAMEID_Q.HEADER_INTERFACE_ID
957 AND OHI.OPERATING_UNIT_ID = HOI.ORGANIZATION_ID (+)
958 AND OHI.OPERATING_UNIT_ID = HOU.ORGANIZATION_ID (+)
959 AND HOI.ORG_INFORMATION_CONTEXT (+)= 'CLASS'
960 AND HOI.ORG_INFORMATION1 (+)= 'OPERATING_UNIT'
961 AND HOI.ORG_INFORMATION2 (+)= 'Y'
962 AND OHI.BILL_TO_SITE_USAGE_ID = BTSUI.SITE_USE_ID (+)
963 AND BTSUI.SITE_USE_CODE (+)= 'BILL_TO'
964 AND OHI.OPERATING_UNIT_ID = BTSUI.ORG_ID (+)
965 AND OHI.SHIP_TO_SITE_USAGE_ID = STSUI.SITE_USE_ID (+)
966 AND STSUI.SITE_USE_CODE (+)= 'SHIP_TO'
967 AND OHI.OPERATING_UNIT_ID = STSUI.ORG_ID (+)
968 AND OHI.CONTRACT_CURRENCY_CODE = FCUR.CURRENCY_CODE (+)
969 AND OHI.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE (+)
970 AND NAMEID_Q.CURRENCY_CONVERSION_TYPE = GLDR.CONVERSION_TYPE (+)
971 AND NAMEID_Q.CURRENCY_CONVERSION_DATE = GLDR.CONVERSION_DATE (+)
972 AND NAMEID_Q.CONTRACT_CURRENCY_CODE = GLDR.FROM_CURRENCY (+)
973 AND NAMEID_Q.OU_LEDGER_CUR_CODE = GLDR.TO_CURRENCY (+)
974 AND OHI.PAYMENT_INSTRUCTION = FL.LOOKUP_CODE (+)
975 AND FL.LOOKUP_TYPE (+) = 'OKS_PAYMENT_INST_TYPE'
976 AND OHI.COMMITMENT_ID = CNI.CUSTOMER_TRX_ID (+)
977 AND OHI.PAYMENT_METHOD_CODE = PMC.LOOKUP_CODE (+)
978 AND PMC.LOOKUP_TYPE (+) = 'OKS_PAYMENT_METHODS'
979 AND PMC.LOOKUP_CODE (+) = 'COM'
980 AND OHI.ROWID between P_rowid_from AND P_rowid_to
981 AND OHI.batch_id = P_batch_id
982 AND (OHI.INTERFACE_STATUS IS NULL OR OHI.INTERFACE_STATUS = 'R');
983
984 IF G_STMT_LOG THEN
985 SELECT count(1) INTO l_int_count FROM OKS_HEADERS_INTERFACE
986 WHERE ROWID between P_rowid_from AND P_rowid_to
987 AND batch_id = P_batch_id
988 AND (INTERFACE_STATUS IS NULL OR INTERFACE_STATUS = 'R');
989
990 SELECT count(1) INTO l_stg_count FROM OKS_INT_HEADER_STG_TEMP;
991
992 fnd_log.string(fnd_log.level_statement,
993 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
994 'Number of records in interface table being processed = ' || l_int_count ||', '||
995 'Number of records inserted into staging table = '|| l_stg_count);
996 END IF;
997
998 l_stmt_num := 20;
999
1000 INSERT ALL
1001 WHEN (CONTRACT_NUMBER IS NULL) THEN
1002 INTO OKS_INT_ERROR_STG_TEMP
1003 (CONCURRENT_REQUEST_ID
1004 ,HEADER_INTERFACE_ROWID
1005 ,INTERFACE_SOURCE_TABLE
1006 ,INTERFACE_ID
1007 ,ERROR_MSG
1008 ,MSG_TOKENS)
1009 VALUES (G_WORKER_REQ_ID
1010 ,HEADER_INTERFACE_ROWID
1011 ,'OKS_HEADERS_INTERFACE'
1012 ,HEADER_INTERFACE_ID
1013 ,'OKS_IMP_HDR_INVALID_NUM'
1014 ,NULL)
1015 WHEN (NUMBER_COUNT_IN_HDR > 0) THEN
1016 INTO OKS_INT_ERROR_STG_TEMP
1017 (CONCURRENT_REQUEST_ID
1018 ,HEADER_INTERFACE_ROWID
1019 ,INTERFACE_SOURCE_TABLE
1020 ,INTERFACE_ID
1021 ,ERROR_MSG
1022 ,MSG_TOKENS)
1023 VALUES (G_WORKER_REQ_ID
1024 ,HEADER_INTERFACE_ROWID
1025 ,'OKS_HEADERS_INTERFACE'
1026 ,HEADER_INTERFACE_ID
1027 ,'OKS_IMP_HDR_NONUNIQ_NUM_HDR'
1028 ,NULL)
1029 WHEN (NUMBER_NOTUNIQ_IN_INT = 'Y') THEN
1030 INTO OKS_INT_ERROR_STG_TEMP
1031 (CONCURRENT_REQUEST_ID
1032 ,HEADER_INTERFACE_ROWID
1033 ,INTERFACE_SOURCE_TABLE
1034 ,INTERFACE_ID
1035 ,ERROR_MSG
1036 ,MSG_TOKENS)
1037 VALUES (G_WORKER_REQ_ID
1038 ,HEADER_INTERFACE_ROWID
1039 ,'OKS_HEADERS_INTERFACE'
1040 ,HEADER_INTERFACE_ID
1041 ,'OKS_IMP_HDR_NONUNIQ_NUM_INT'
1042 ,NULL)
1043 WHEN (CAT_COUNT <> 1) THEN
1044 INTO OKS_INT_ERROR_STG_TEMP
1045 (CONCURRENT_REQUEST_ID
1046 ,HEADER_INTERFACE_ROWID
1047 ,INTERFACE_SOURCE_TABLE
1048 ,INTERFACE_ID
1049 ,ERROR_MSG
1050 ,MSG_TOKENS)
1051 VALUES (G_WORKER_REQ_ID
1052 ,HEADER_INTERFACE_ROWID
1053 ,'OKS_HEADERS_INTERFACE'
1054 ,HEADER_INTERFACE_ID
1055 ,'OKS_IMP_HDR_INVALID_CAT'
1056 ,NULL)
1057 WHEN (DATE_VALID = 'N') THEN
1058 INTO OKS_INT_ERROR_STG_TEMP
1059 (CONCURRENT_REQUEST_ID
1060 ,HEADER_INTERFACE_ROWID
1061 ,INTERFACE_SOURCE_TABLE
1062 ,INTERFACE_ID
1063 ,ERROR_MSG
1064 ,MSG_TOKENS)
1065 VALUES (G_WORKER_REQ_ID
1066 ,HEADER_INTERFACE_ROWID
1067 ,'OKS_HEADERS_INTERFACE'
1068 ,HEADER_INTERFACE_ID
1069 ,'OKS_IMP_HDR_INVALID_DATE'
1070 ,NULL)
1071 WHEN (STATUS_VALID = 'N') THEN
1072 INTO OKS_INT_ERROR_STG_TEMP
1073 (CONCURRENT_REQUEST_ID
1074 ,HEADER_INTERFACE_ROWID
1075 ,INTERFACE_SOURCE_TABLE
1076 ,INTERFACE_ID
1077 ,ERROR_MSG
1078 ,MSG_TOKENS)
1079 VALUES (G_WORKER_REQ_ID
1080 ,HEADER_INTERFACE_ROWID
1081 ,'OKS_HEADERS_INTERFACE'
1082 ,HEADER_INTERFACE_ID
1083 ,'OKS_IMP_HDR_INVALID_STATUS'
1084 ,NULL)
1085 WHEN (STATUS_X_DATE_VALID = 'N') THEN
1086 INTO OKS_INT_ERROR_STG_TEMP
1087 (CONCURRENT_REQUEST_ID
1088 ,HEADER_INTERFACE_ROWID
1089 ,INTERFACE_SOURCE_TABLE
1090 ,INTERFACE_ID
1091 ,ERROR_MSG
1092 ,MSG_TOKENS)
1093 VALUES (G_WORKER_REQ_ID
1094 ,HEADER_INTERFACE_ROWID
1095 ,'OKS_HEADERS_INTERFACE'
1096 ,HEADER_INTERFACE_ID
1097 ,'OKS_IMP_HDR_STATUS_X_DATE'
1098 ,NULL)
1099 WHEN (DATE_APPROVED_VALID = 'N') THEN
1100 INTO OKS_INT_ERROR_STG_TEMP
1101 (CONCURRENT_REQUEST_ID
1102 ,HEADER_INTERFACE_ROWID
1103 ,INTERFACE_SOURCE_TABLE
1104 ,INTERFACE_ID
1105 ,ERROR_MSG
1106 ,MSG_TOKENS)
1107 VALUES (G_WORKER_REQ_ID
1108 ,HEADER_INTERFACE_ROWID
1109 ,'OKS_HEADERS_INTERFACE'
1110 ,HEADER_INTERFACE_ID
1111 ,'OKS_IMP_HDR_INVALID_DATE_APRV'
1112 ,NULL)
1113 WHEN (DATE_APPROVED_NULL_ERROR = 'Y') THEN
1114 INTO OKS_INT_ERROR_STG_TEMP
1115 (CONCURRENT_REQUEST_ID
1116 ,HEADER_INTERFACE_ROWID
1117 ,INTERFACE_SOURCE_TABLE
1118 ,INTERFACE_ID
1119 ,ERROR_MSG
1120 ,MSG_TOKENS)
1121 VALUES (G_WORKER_REQ_ID
1122 ,HEADER_INTERFACE_ROWID
1123 ,'OKS_HEADERS_INTERFACE'
1124 ,HEADER_INTERFACE_ID
1125 ,'OKS_IMP_HDR_NULL_DATE_APRV'
1126 ,NULL)
1127 WHEN (DATE_CANCELED_VALID = 'N') THEN
1128 INTO OKS_INT_ERROR_STG_TEMP
1129 (CONCURRENT_REQUEST_ID
1130 ,HEADER_INTERFACE_ROWID
1131 ,INTERFACE_SOURCE_TABLE
1132 ,INTERFACE_ID
1133 ,ERROR_MSG
1134 ,MSG_TOKENS)
1135 VALUES (G_WORKER_REQ_ID
1136 ,HEADER_INTERFACE_ROWID
1137 ,'OKS_HEADERS_INTERFACE'
1138 ,HEADER_INTERFACE_ID
1139 ,'OKS_IMP_HDR_INVALID_DATE_CANC'
1140 ,NULL)
1141 WHEN (DATE_CANCELED_NULL_ERROR = 'Y') THEN
1142 INTO OKS_INT_ERROR_STG_TEMP
1143 (CONCURRENT_REQUEST_ID
1144 ,HEADER_INTERFACE_ROWID
1145 ,INTERFACE_SOURCE_TABLE
1146 ,INTERFACE_ID
1147 ,ERROR_MSG
1148 ,MSG_TOKENS)
1149 VALUES (G_WORKER_REQ_ID
1150 ,HEADER_INTERFACE_ROWID
1151 ,'OKS_HEADERS_INTERFACE'
1152 ,HEADER_INTERFACE_ID
1153 ,'OKS_IMP_HDR_NULL_DATE_CANC'
1154 ,NULL)
1155 WHEN (CR_VALID = 'N') THEN
1156 INTO OKS_INT_ERROR_STG_TEMP
1157 (CONCURRENT_REQUEST_ID
1158 ,HEADER_INTERFACE_ROWID
1159 ,INTERFACE_SOURCE_TABLE
1160 ,INTERFACE_ID
1161 ,ERROR_MSG
1162 ,MSG_TOKENS)
1163 VALUES (G_WORKER_REQ_ID
1164 ,HEADER_INTERFACE_ROWID
1165 ,'OKS_HEADERS_INTERFACE'
1166 ,HEADER_INTERFACE_ID
1167 ,'OKS_IMP_HDR_INVALID_CANC_RSN'
1168 ,NULL)
1169 WHEN (CR_NULL_ERROR = 'Y') THEN
1170 INTO OKS_INT_ERROR_STG_TEMP
1171 (CONCURRENT_REQUEST_ID
1172 ,HEADER_INTERFACE_ROWID
1173 ,INTERFACE_SOURCE_TABLE
1174 ,INTERFACE_ID
1175 ,ERROR_MSG
1176 ,MSG_TOKENS)
1177 VALUES (G_WORKER_REQ_ID
1178 ,HEADER_INTERFACE_ROWID
1179 ,'OKS_HEADERS_INTERFACE'
1180 ,HEADER_INTERFACE_ID
1181 ,'OKS_IMP_HDR_NULL_CANC_RSN'
1182 ,NULL)
1183 WHEN (DATE_SIGNED_VALID = 'N') THEN
1184 INTO OKS_INT_ERROR_STG_TEMP
1185 (CONCURRENT_REQUEST_ID
1186 ,HEADER_INTERFACE_ROWID
1187 ,INTERFACE_SOURCE_TABLE
1188 ,INTERFACE_ID
1189 ,ERROR_MSG
1190 ,MSG_TOKENS)
1191 VALUES (G_WORKER_REQ_ID
1192 ,HEADER_INTERFACE_ROWID
1193 ,'OKS_HEADERS_INTERFACE'
1194 ,HEADER_INTERFACE_ID
1195 ,'OKS_IMP_HDR_INVALID_DATE_SGND'
1196 ,NULL)
1197 WHEN (DATE_SIGNED_NULL_ERROR = 'Y') THEN
1198 INTO OKS_INT_ERROR_STG_TEMP
1199 (CONCURRENT_REQUEST_ID
1200 ,HEADER_INTERFACE_ROWID
1201 ,INTERFACE_SOURCE_TABLE
1202 ,INTERFACE_ID
1203 ,ERROR_MSG
1204 ,MSG_TOKENS)
1205 VALUES (G_WORKER_REQ_ID
1206 ,HEADER_INTERFACE_ROWID
1207 ,'OKS_HEADERS_INTERFACE'
1208 ,HEADER_INTERFACE_ID
1209 ,'OKS_IMP_HDR_NULL_DATE_SGND'
1210 ,NULL)
1211 WHEN (RENP_VALID = 'N') THEN
1212 INTO OKS_INT_ERROR_STG_TEMP
1213 (CONCURRENT_REQUEST_ID
1214 ,HEADER_INTERFACE_ROWID
1215 ,INTERFACE_SOURCE_TABLE
1216 ,INTERFACE_ID
1217 ,ERROR_MSG
1218 ,MSG_TOKENS)
1219 VALUES (G_WORKER_REQ_ID
1220 ,HEADER_INTERFACE_ROWID
1221 ,'OKS_HEADERS_INTERFACE'
1222 ,HEADER_INTERFACE_ID
1223 ,'OKS_IMP_HDR_INVALID_REN_PROC'
1224 ,NULL)
1225 WHEN (RENUPTO_VALID = 'N') THEN
1226 INTO OKS_INT_ERROR_STG_TEMP
1227 (CONCURRENT_REQUEST_ID
1228 ,HEADER_INTERFACE_ROWID
1229 ,INTERFACE_SOURCE_TABLE
1230 ,INTERFACE_ID
1231 ,ERROR_MSG
1232 ,MSG_TOKENS)
1233 VALUES (G_WORKER_REQ_ID
1234 ,HEADER_INTERFACE_ROWID
1235 ,'OKS_HEADERS_INTERFACE'
1236 ,HEADER_INTERFACE_ID
1237 ,'OKS_IMP_HDR_INVALID_REN_UPTO'
1238 ,NULL)
1239 WHEN (RENUPTO_NULL_ERROR = 'Y') THEN
1240 INTO OKS_INT_ERROR_STG_TEMP
1241 (CONCURRENT_REQUEST_ID
1242 ,HEADER_INTERFACE_ROWID
1243 ,INTERFACE_SOURCE_TABLE
1244 ,INTERFACE_ID
1245 ,ERROR_MSG
1246 ,MSG_TOKENS)
1247 VALUES (G_WORKER_REQ_ID
1248 ,HEADER_INTERFACE_ROWID
1249 ,'OKS_HEADERS_INTERFACE'
1250 ,HEADER_INTERFACE_ID
1251 ,'OKS_IMP_HDR_NULL_REN_UPTO'
1252 ,NULL)
1253 WHEN (APRV_REQ_VALID = 'N') THEN
1254 INTO OKS_INT_ERROR_STG_TEMP
1255 (CONCURRENT_REQUEST_ID
1256 ,HEADER_INTERFACE_ROWID
1257 ,INTERFACE_SOURCE_TABLE
1258 ,INTERFACE_ID
1259 ,ERROR_MSG
1260 ,MSG_TOKENS)
1261 VALUES (G_WORKER_REQ_ID
1262 ,HEADER_INTERFACE_ROWID
1263 ,'OKS_HEADERS_INTERFACE'
1264 ,HEADER_INTERFACE_ID
1265 ,'OKS_IMP_HDR_INVALID_APRV_REQ'
1266 ,NULL)
1267 WHEN (APRV_REQ_NULL_ERROR = 'Y') THEN
1268 INTO OKS_INT_ERROR_STG_TEMP
1269 (CONCURRENT_REQUEST_ID
1270 ,HEADER_INTERFACE_ROWID
1271 ,INTERFACE_SOURCE_TABLE
1272 ,INTERFACE_ID
1273 ,ERROR_MSG
1274 ,MSG_TOKENS)
1275 VALUES (G_WORKER_REQ_ID
1276 ,HEADER_INTERFACE_ROWID
1277 ,'OKS_HEADERS_INTERFACE'
1278 ,HEADER_INTERFACE_ID
1279 ,'OKS_IMP_HDR_NULL_APRV_REQ'
1280 ,NULL)
1281 WHEN (PRM_VALID = 'N') THEN
1282 INTO OKS_INT_ERROR_STG_TEMP
1283 (CONCURRENT_REQUEST_ID
1284 ,HEADER_INTERFACE_ROWID
1285 ,INTERFACE_SOURCE_TABLE
1286 ,INTERFACE_ID
1287 ,ERROR_MSG
1288 ,MSG_TOKENS)
1289 VALUES (G_WORKER_REQ_ID
1290 ,HEADER_INTERFACE_ROWID
1291 ,'OKS_HEADERS_INTERFACE'
1292 ,HEADER_INTERFACE_ID
1293 ,'OKS_IMP_HDR_INVALID_PRC_METHOD'
1294 ,NULL)
1295 WHEN (PRM_NULL_ERROR = 'Y') THEN
1296 INTO OKS_INT_ERROR_STG_TEMP
1297 (CONCURRENT_REQUEST_ID
1298 ,HEADER_INTERFACE_ROWID
1299 ,INTERFACE_SOURCE_TABLE
1300 ,INTERFACE_ID
1301 ,ERROR_MSG
1302 ,MSG_TOKENS)
1303 VALUES (G_WORKER_REQ_ID
1304 ,HEADER_INTERFACE_ROWID
1305 ,'OKS_HEADERS_INTERFACE'
1306 ,HEADER_INTERFACE_ID
1307 ,'OKS_IMP_HDR_NULL_PRC_METHOD'
1308 ,NULL)
1309 WHEN (MKP_VALID = 'N') THEN
1310 INTO OKS_INT_ERROR_STG_TEMP
1311 (CONCURRENT_REQUEST_ID
1312 ,HEADER_INTERFACE_ROWID
1313 ,INTERFACE_SOURCE_TABLE
1314 ,INTERFACE_ID
1315 ,ERROR_MSG
1316 ,MSG_TOKENS)
1317 VALUES (G_WORKER_REQ_ID
1318 ,HEADER_INTERFACE_ROWID
1319 ,'OKS_HEADERS_INTERFACE'
1320 ,HEADER_INTERFACE_ID
1321 ,'OKS_IMP_HDR_INVALID_MARKUP'
1322 ,NULL)
1323 WHEN (MKP_NULL_ERROR = 'Y') THEN
1324 INTO OKS_INT_ERROR_STG_TEMP
1325 (CONCURRENT_REQUEST_ID
1326 ,HEADER_INTERFACE_ROWID
1327 ,INTERFACE_SOURCE_TABLE
1328 ,INTERFACE_ID
1329 ,ERROR_MSG
1330 ,MSG_TOKENS)
1331 VALUES (G_WORKER_REQ_ID
1332 ,HEADER_INTERFACE_ROWID
1333 ,'OKS_HEADERS_INTERFACE'
1334 ,HEADER_INTERFACE_ID
1335 ,'OKS_IMP_HDR_NULL_MARKUP'
1336 ,NULL)
1337 WHEN (BP_VALID = 'N') THEN
1338 INTO OKS_INT_ERROR_STG_TEMP
1339 (CONCURRENT_REQUEST_ID
1340 ,HEADER_INTERFACE_ROWID
1341 ,INTERFACE_SOURCE_TABLE
1342 ,INTERFACE_ID
1343 ,ERROR_MSG
1344 ,MSG_TOKENS)
1345 VALUES (G_WORKER_REQ_ID
1346 ,HEADER_INTERFACE_ROWID
1347 ,'OKS_HEADERS_INTERFACE'
1348 ,HEADER_INTERFACE_ID
1349 ,'OKS_IMP_HDR_INVALID_BILLPROF'
1350 ,NULL)
1351 WHEN (RENPOREQ_VALID = 'N') THEN
1352 INTO OKS_INT_ERROR_STG_TEMP
1353 (CONCURRENT_REQUEST_ID
1354 ,HEADER_INTERFACE_ROWID
1355 ,INTERFACE_SOURCE_TABLE
1356 ,INTERFACE_ID
1357 ,ERROR_MSG
1358 ,MSG_TOKENS)
1359 VALUES (G_WORKER_REQ_ID
1360 ,HEADER_INTERFACE_ROWID
1361 ,'OKS_HEADERS_INTERFACE'
1362 ,HEADER_INTERFACE_ID
1363 ,'OKS_IMP_HDR_INVALID_RENPO_REQ'
1364 ,NULL)
1365 WHEN (nvl(RENEWAL_GRACE_DURATION, 1) <= 0 OR floor(nvl(RENEWAL_GRACE_DURATION, 1)) <> nvl(RENEWAL_GRACE_DURATION, 1)) THEN
1366 INTO OKS_INT_ERROR_STG_TEMP
1367 (CONCURRENT_REQUEST_ID
1368 ,HEADER_INTERFACE_ROWID
1369 ,INTERFACE_SOURCE_TABLE
1370 ,INTERFACE_ID
1371 ,ERROR_MSG
1372 ,MSG_TOKENS)
1373 VALUES (G_WORKER_REQ_ID
1374 ,HEADER_INTERFACE_ROWID
1375 ,'OKS_HEADERS_INTERFACE'
1376 ,HEADER_INTERFACE_ID
1377 ,'OKS_IMP_HDR_INVALID_RENGR_DUR'
1378 ,NULL)
1379 WHEN (RENGRPER_VALID = 'N') THEN
1380 INTO OKS_INT_ERROR_STG_TEMP
1381 (CONCURRENT_REQUEST_ID
1382 ,HEADER_INTERFACE_ROWID
1383 ,INTERFACE_SOURCE_TABLE
1384 ,INTERFACE_ID
1385 ,ERROR_MSG
1386 ,MSG_TOKENS)
1387 VALUES (G_WORKER_REQ_ID
1388 ,HEADER_INTERFACE_ROWID
1389 ,'OKS_HEADERS_INTERFACE'
1390 ,HEADER_INTERFACE_ID
1391 ,'OKS_IMP_HDR_INVALID_RENGR_PER'
1392 ,NULL)
1393 WHEN (RENGRPER_NULL_ERROR = 'Y') THEN
1394 INTO OKS_INT_ERROR_STG_TEMP
1395 (CONCURRENT_REQUEST_ID
1396 ,HEADER_INTERFACE_ROWID
1397 ,INTERFACE_SOURCE_TABLE
1398 ,INTERFACE_ID
1399 ,ERROR_MSG
1400 ,MSG_TOKENS)
1401 VALUES (G_WORKER_REQ_ID
1402 ,HEADER_INTERFACE_ROWID
1403 ,'OKS_HEADERS_INTERFACE'
1404 ,HEADER_INTERFACE_ID
1405 ,'OKS_IMP_HDR_NULL_RENGR_PER'
1406 ,NULL)
1407 /* WHEN (nvl(RENEWAL_ESTIMATED_PERCENT, 1) < 0 OR nvl(RENEWAL_ESTIMATED_PERCENT, 1) > 100) THEN
1408 INTO OKS_INT_ERROR_STG_TEMP
1409 (CONCURRENT_REQUEST_ID
1410 ,HEADER_INTERFACE_ROWID
1411 ,INTERFACE_SOURCE_TABLE
1412 ,INTERFACE_ID
1413 ,ERROR_MSG
1414 ,MSG_TOKENS)
1415 VALUES (G_WORKER_REQ_ID
1416 ,HEADER_INTERFACE_ROWID
1417 ,'OKS_HEADERS_INTERFACE'
1418 ,HEADER_INTERFACE_ID
1419 ,'OKS_IMP_HDR_NULL_EST_PERC'
1420 ,NULL) */
1421 /* WHEN (nvl(RENEWAL_ESTIMATED_DURATION, 1) <= 0 ) THEN
1422 INTO OKS_INT_ERROR_STG_TEMP
1423 (CONCURRENT_REQUEST_ID
1424 ,HEADER_INTERFACE_ROWID
1425 ,INTERFACE_SOURCE_TABLE
1426 ,INTERFACE_ID
1427 ,ERROR_MSG
1428 ,MSG_TOKENS)
1429 VALUES (G_WORKER_REQ_ID
1430 ,HEADER_INTERFACE_ROWID
1431 ,'OKS_HEADERS_INTERFACE'
1432 ,HEADER_INTERFACE_ID
1433 ,'OKS_IMP_HDR_INVALID_EST_DUR'
1434 ,NULL) */
1435 WHEN (REN_EST_DURPER_VALID = 'N') THEN
1436 INTO OKS_INT_ERROR_STG_TEMP
1437 (CONCURRENT_REQUEST_ID
1438 ,HEADER_INTERFACE_ROWID
1439 ,INTERFACE_SOURCE_TABLE
1440 ,INTERFACE_ID
1441 ,ERROR_MSG
1442 ,MSG_TOKENS)
1443 VALUES (G_WORKER_REQ_ID
1444 ,HEADER_INTERFACE_ROWID
1445 ,'OKS_HEADERS_INTERFACE'
1446 ,HEADER_INTERFACE_ID
1447 ,'OKS_IMP_HDR_INVALID_DATE_EST'
1448 ,NULL)
1449 WHEN (REN_EST_DURPER_NULL_ERROR = 'Y') THEN
1450 INTO OKS_INT_ERROR_STG_TEMP
1451 (CONCURRENT_REQUEST_ID
1452 ,HEADER_INTERFACE_ROWID
1453 ,INTERFACE_SOURCE_TABLE
1454 ,INTERFACE_ID
1455 ,ERROR_MSG
1456 ,MSG_TOKENS)
1457 VALUES (G_WORKER_REQ_ID
1458 ,HEADER_INTERFACE_ROWID
1459 ,'OKS_HEADERS_INTERFACE'
1460 ,HEADER_INTERFACE_ID
1461 ,'OKS_IMP_HDR_NULL_REN_EST'
1462 ,NULL)
1463 SELECT HST.CONTRACT_NUMBER CONTRACT_NUMBER
1464 ,HST.HEADER_INTERFACE_ROWID HEADER_INTERFACE_ROWID
1465 ,HST.HEADER_INTERFACE_ID HEADER_INTERFACE_ID
1466 ,COUNT(DISTINCT HDR.ROWID) OVER (PARTITION BY HST.ROWID) NUMBER_COUNT_IN_HDR
1467 ,(CASE WHEN EXISTS (SELECT 1 FROM OKS_HEADERS_INTERFACE
1468 WHERE HST.CONTRACT_NUMBER = CONTRACT_NUMBER
1469 AND nvl(HST.CONTRACT_NUMBER_MODIFIER, 'XgArBaGe!@#') = nvl(CONTRACT_NUMBER_MODIFIER (+), 'XgArBaGe!@#')
1470 AND HST.HEADER_INTERFACE_ID <> HEADER_INTERFACE_ID (+))
1471 THEN 'Y' ELSE 'N'
1472 END) NUMBER_NOTUNIQ_IN_INT
1473 ,COUNT(DISTINCT CAT.ROWID) OVER (PARTITION BY HST.ROWID) CAT_COUNT
1474 ,(CASE WHEN HST.START_DATE IS NULL OR HST.END_DATE IS NULL OR HST.START_DATE > HST.END_DATE
1475 THEN 'N' ELSE 'Y' END) DATE_VALID
1476 ,(CASE WHEN STS.STE_CODE IN ('ENTERED', 'ACTIVE', 'CANCELLED', 'SIGNED', 'EXPIRED')
1477 THEN 'Y' ELSE 'N' END) STATUS_VALID
1478 ,(CASE WHEN (HST.START_DATE > SYSDATE AND STS.STE_CODE IN ('ACTIVE', 'EXPIRED'))
1479 OR (HST.END_DATE <= SYSDATE AND STS.STE_CODE = 'SIGNED')
1480 OR (SYSDATE between HST.START_DATE and HST.END_DATE AND STS.STE_CODE IN ('SIGNED', 'EXPIRED'))
1481 THEN 'N' ELSE 'Y' END) STATUS_X_DATE_VALID
1482 ,(CASE WHEN (HST.DATE_APPROVED IS NULL AND STS.STE_CODE IN ('ACTIVE', 'SIGNED', 'EXPIRED'))
1483 THEN 'N' ELSE 'Y' END) DATE_APPROVED_VALID
1484 ,(CASE WHEN (HST.DATE_APPROVED IS NOT NULL AND STS.STE_CODE = 'ENTERED')
1485 THEN 'Y' ELSE 'N' END) DATE_APPROVED_NULL_ERROR
1486 ,(CASE WHEN (HST.DATE_CANCELED IS NULL AND STS.STE_CODE = 'CANCELLED')
1487 THEN 'N' ELSE 'Y' END) DATE_CANCELED_VALID
1488 ,(CASE WHEN (HST.DATE_CANCELED IS NOT NULL AND STS.STE_CODE <> 'CANCELLED')
1489 THEN 'Y' ELSE 'N' END) DATE_CANCELED_NULL_ERROR
1490 ,(CASE WHEN (STS.STE_CODE = 'CANCELLED'
1491 AND ( COUNT(DISTINCT CR1.ROWID) OVER (PARTITION BY HST.ROWID) <> 1
1492 AND COUNT(DISTINCT CR2.ROWID) OVER (PARTITION BY HST.ROWID) <> 1)
1493 )
1494 THEN 'N' ELSE 'Y' END) CR_VALID
1495 ,(CASE WHEN (STS.STE_CODE <> 'CANCELLED' AND HST.CANCELLATION_REASON IS NOT NULL)
1496 THEN 'Y' ELSE 'N' END) CR_NULL_ERROR
1497 ,(CASE WHEN (HST.DATE_SIGNED IS NULL AND STS.STE_CODE IN ('ACTIVE', 'SIGNED', 'EXPIRED'))
1498 THEN 'N' ELSE 'Y' END) DATE_SIGNED_VALID
1499 ,(CASE WHEN (HST.DATE_SIGNED IS NOT NULL AND STS.STE_CODE = 'ENTERED')
1500 THEN 'Y' ELSE 'N' END) DATE_SIGNED_NULL_ERROR
1501 ,(CASE WHEN (HST.RENEWAL_PROCESS IS NOT NULL
1502 AND COUNT(DISTINCT RENP.ROWID) OVER (PARTITION BY HST.ROWID) <> 1)
1503 THEN 'N' ELSE 'Y' END) RENP_VALID
1504 ,(CASE WHEN (HST.RENEWAL_PROCESS = 'EVN' AND (HST.RENEW_UP_TO IS NULL /*OR HST.RENEW_UP_TO <= HST.END_DATE*/))
1505 THEN 'N' ELSE 'Y' END) RENUPTO_VALID
1506 ,(CASE WHEN (HST.RENEW_UP_TO IS NOT NULL AND HST.RENEWAL_PROCESS <> 'EVN')
1507 THEN 'Y' ELSE 'N' END) RENUPTO_NULL_ERROR
1508 ,(CASE WHEN (HST.RENEWAL_PROCESS IN ('EVN', 'NSR')
1509 AND NOT EXISTS (SELECT 'X' FROM FND_LOOKUPS
1510 WHERE LOOKUP_TYPE = 'OKS_REN_MANUAL_APPROVAL'
1511 AND LOOKUP_CODE = HST.APPROVAL_REQUIRED))
1512 OR (HST.RENEWAL_PROCESS = 'ERN'
1513 AND NOT EXISTS (SELECT 'X' FROM FND_LOOKUPS
1514 WHERE LOOKUP_TYPE = 'OKS_REN_ONLINE_APPROVAL'
1515 AND LOOKUP_CODE = HST.APPROVAL_REQUIRED))
1516 THEN 'N' ELSE 'Y' END) APRV_REQ_VALID
1517 ,(CASE WHEN (HST.APPROVAL_REQUIRED IS NOT NULL AND (HST.RENEWAL_PROCESS IS NULL OR HST.RENEWAL_PROCESS = 'DNR'))
1518 THEN 'Y' ELSE 'N' END) APRV_REQ_NULL_ERROR
1519 ,(CASE WHEN (HST.PRICING_METHOD IS NOT NULL AND (HST.RENEWAL_PROCESS IN ('EVN', 'NSR', 'ERN') OR HST.RENEWAL_PROCESS IS NULL)
1520 AND NOT EXISTS (SELECT 'X' FROM FND_LOOKUPS
1521 WHERE LOOKUP_TYPE = 'OKC_RENEWAL_PRICING_TYPE'
1522 AND LOOKUP_CODE = HST.PRICING_METHOD))
1523 THEN 'N' ELSE 'Y' END) PRM_VALID
1524 ,(CASE WHEN (HST.PRICING_METHOD IS NOT NULL AND HST.RENEWAL_PROCESS = 'DNR')
1525 THEN 'Y' ELSE 'N' END) PRM_NULL_ERROR
1526 ,(CASE WHEN (HST.PRICING_METHOD = 'PCT' AND (HST.RENEWAL_MARKUP IS NULL OR HST.RENEWAL_MARKUP < -100))
1527 THEN 'N' ELSE 'Y' END) MKP_VALID
1528 ,(CASE WHEN (HST.RENEWAL_MARKUP IS NOT NULL AND HST.PRICING_METHOD <> 'PCT')
1529 THEN 'Y' ELSE 'N' END) MKP_NULL_ERROR
1530 ,(CASE WHEN (HST.RENEWAL_BILLING_PROFILE_ID IS NOT NULL
1531 AND NOT EXISTS (SELECT 'X'
1532 FROM OKS_BILLING_PROFILES_B
1533 WHERE ID = HST.RENEWAL_BILLING_PROFILE_ID
1534 AND (OWNED_PARTY_ID1 IS NULL
1535 OR (OWNED_PARTY_ID1 = HST.CUSTOMER_PARTY_ID AND
1536 BILL_TO_ADDRESS_ID1 IS NULL))
1537 UNION
1538 SELECT 'X'
1539 FROM OKS_BILLING_PROFILES_B A,
1540 HZ_CUST_SITE_USES_ALL CS,
1541 HZ_PARTY_SITES PS,
1542 HZ_CUST_ACCT_SITES_ALL CA
1543 WHERE A.ID = HST.RENEWAL_BILLING_PROFILE_ID
1544 AND A.OWNED_PARTY_ID1 = HST.CUSTOMER_PARTY_ID
1545 AND PS.PARTY_SITE_ID = CA.PARTY_SITE_ID
1546 AND CA.CUST_ACCT_SITE_ID = CS.CUST_ACCT_SITE_ID
1547 AND PS.PARTY_ID = A.OWNED_PARTY_ID1
1548 AND CS.SITE_USE_CODE = 'BILL_TO'
1549 AND CS.SITE_USE_ID = A.BILL_TO_ADDRESS_ID1
1550 AND CS.ORG_ID = HST.OPERATING_UNIT_ID))
1551 THEN 'N' ELSE 'Y' END) BP_VALID
1552 ,(CASE WHEN (HST.RENEWAL_PO_REQUIRED IS NOT NULL
1553 AND COUNT(DISTINCT RENPOREQ.ROWID) OVER (PARTITION BY HST.ROWID) <> 1)
1554 THEN 'N' ELSE 'Y' END) RENPOREQ_VALID
1555 ,HST.RENEWAL_GRACE_DURATION RENEWAL_GRACE_DURATION
1556 ,(CASE WHEN (HST.RENEWAL_GRACE_DURATION IS NOT NULL
1557 AND COUNT(DISTINCT RENGRPER.ROWID) OVER (PARTITION BY HST.ROWID) <> 1)
1558 THEN 'N' ELSE 'Y' END) RENGRPER_VALID
1559 ,(CASE WHEN (HST.RENEWAL_GRACE_PERIOD IS NOT NULL AND HST.RENEWAL_GRACE_DURATION IS NULL)
1560 THEN 'Y' ELSE 'N' END) RENGRPER_NULL_ERROR
1561 ,HST.RENEWAL_ESTIMATED_PERCENT RENEWAL_ESTIMATED_PERCENT
1562 ,HST.RENEWAL_ESTIMATED_DURATION RENEWAL_ESTIMATED_DURATION
1563 ,(CASE WHEN (HST.RENEWAL_ESTIMATED_PERCENT IS NOT NULL
1564 AND (HST.RENEWAL_ESTIMATED_DURATION IS NULL
1565 OR COUNT(DISTINCT RENESTPER.ROWID) OVER (PARTITION BY HST.ROWID) <> 1))
1566 THEN 'N' ELSE 'Y' END) REN_EST_DURPER_VALID
1567 ,(CASE WHEN ((HST.RENEWAL_ESTIMATED_PERIOD IS NOT NULL OR HST.RENEWAL_ESTIMATED_DURATION IS NOT NULL)
1568 AND HST.RENEWAL_ESTIMATED_PERCENT IS NULL)
1569 THEN 'Y' ELSE 'N' END) REN_EST_DURPER_NULL_ERROR
1570 FROM OKS_INT_HEADER_STG_TEMP HST
1571 ,OKC_SUBCLASSES_B CAT
1572 ,OKC_K_HEADERS_ALL_B HDR
1573 ,OKC_STATUSES_B STS
1574 ,FND_LOOKUPS RENP
1575 ,FND_LOOKUPS RENPOREQ
1576 ,OKC_TIME_CODE_UNITS_B RENGRPER
1577 ,OKC_TIME_CODE_UNITS_B RENESTPER
1578 ,FND_LOOKUPS CR1
1579 ,FND_LOOKUPS CR2
1580 WHERE HST.CATEGORY = CAT.CODE (+)
1581 AND CAT.CLS_CODE(+) = 'SERVICE'
1582 AND HST.CONTRACT_NUMBER = HDR.CONTRACT_NUMBER (+)
1583 AND nvl(HST.CONTRACT_NUMBER_MODIFIER , 'XgArBaGe!@#') = nvl(HDR.CONTRACT_NUMBER_MODIFIER (+), 'XgArBaGe!@#')
1584 AND HST.STATUS_CODE = STS.CODE (+)
1585 AND HST.RENEWAL_PROCESS = RENP.LOOKUP_CODE (+)
1586 AND RENP.LOOKUP_TYPE (+) = 'OKS_RENEWAL_TYPE'
1587 AND RENPOREQ.LOOKUP_TYPE (+) = 'OKS_Y_N'
1588 AND HST.RENEWAL_PO_REQUIRED = RENPOREQ.LOOKUP_CODE (+)
1589 AND HST.RENEWAL_GRACE_PERIOD = RENGRPER.UOM_CODE (+)
1590 AND HST.RENEWAL_ESTIMATED_PERIOD = RENESTPER.UOM_CODE (+)
1591 AND HST.CANCELLATION_REASON = CR1.LOOKUP_CODE (+)
1592 AND CR1.LOOKUP_TYPE (+) = 'OKS_CANCEL_REASON'
1593 AND HST.CANCELLATION_REASON = CR2.LOOKUP_CODE (+)
1594 AND CR2.LOOKUP_TYPE (+) = 'OKC_STS_CHG_REASON';
1595
1596 l_stmt_num := 30;
1597
1598 INSERT ALL
1599 WHEN (SALESGROUP_V = 'N') THEN
1600 INTO OKS_INT_ERROR_STG_TEMP
1601 (CONCURRENT_REQUEST_ID
1602 ,HEADER_INTERFACE_ROWID
1603 ,INTERFACE_SOURCE_TABLE
1604 ,INTERFACE_ID
1605 ,ERROR_MSG
1606 ,MSG_TOKENS)
1607 VALUES (G_WORKER_REQ_ID
1608 ,HEADER_INTERFACE_ROWID
1609 ,'OKS_HEADERS_INTERFACE'
1610 ,HEADER_INTERFACE_ID
1611 ,'OKS_IMP_HDR_INVALID_SALES_G'
1612 ,NULL)
1613 WHEN (QUOTE_TO_PARTY_SITE_V = 'N') THEN
1614 INTO OKS_INT_ERROR_STG_TEMP
1615 (CONCURRENT_REQUEST_ID
1616 ,HEADER_INTERFACE_ROWID
1617 ,INTERFACE_SOURCE_TABLE
1618 ,INTERFACE_ID
1619 ,ERROR_MSG
1620 ,MSG_TOKENS)
1621 VALUES (G_WORKER_REQ_ID
1622 ,HEADER_INTERFACE_ROWID
1623 ,'OKS_HEADERS_INTERFACE'
1624 ,HEADER_INTERFACE_ID
1625 ,'OKS_IMP_HDR_INVALID_QPS'
1626 ,NULL)
1627 WHEN (QUOTE_TO_CONTACT_V = 'N') THEN
1628 INTO OKS_INT_ERROR_STG_TEMP
1629 (CONCURRENT_REQUEST_ID
1630 ,HEADER_INTERFACE_ROWID
1631 ,INTERFACE_SOURCE_TABLE
1632 ,INTERFACE_ID
1633 ,ERROR_MSG
1634 ,MSG_TOKENS)
1635 VALUES (G_WORKER_REQ_ID
1636 ,HEADER_INTERFACE_ROWID
1637 ,'OKS_HEADERS_INTERFACE'
1638 ,HEADER_INTERFACE_ID
1639 ,'OKS_IMP_HDR_INVALID_QTC'
1640 ,NULL)
1641 WHEN (QUOTE_TO_PHONE_V = 'N') THEN
1642 INTO OKS_INT_ERROR_STG_TEMP
1643 (CONCURRENT_REQUEST_ID
1644 ,HEADER_INTERFACE_ROWID
1645 ,INTERFACE_SOURCE_TABLE
1646 ,INTERFACE_ID
1647 ,ERROR_MSG
1648 ,MSG_TOKENS)
1649 VALUES (G_WORKER_REQ_ID
1650 ,HEADER_INTERFACE_ROWID
1651 ,'OKS_HEADERS_INTERFACE'
1652 ,HEADER_INTERFACE_ID
1653 ,'OKS_IMP_HDR_INVALID_QTP'
1654 ,NULL)
1655 WHEN (QUOTE_TO_FAX_V = 'N') THEN
1656 INTO OKS_INT_ERROR_STG_TEMP
1657 (CONCURRENT_REQUEST_ID
1658 ,HEADER_INTERFACE_ROWID
1659 ,INTERFACE_SOURCE_TABLE
1660 ,INTERFACE_ID
1661 ,ERROR_MSG
1662 ,MSG_TOKENS)
1663 VALUES (G_WORKER_REQ_ID
1664 ,HEADER_INTERFACE_ROWID
1665 ,'OKS_HEADERS_INTERFACE'
1666 ,HEADER_INTERFACE_ID
1667 ,'OKS_IMP_HDR_INVALID_QTF'
1668 ,NULL)
1669 WHEN (QUOTE_TO_EMAIL_V = 'N') THEN
1670 INTO OKS_INT_ERROR_STG_TEMP
1671 (CONCURRENT_REQUEST_ID
1672 ,HEADER_INTERFACE_ROWID
1673 ,INTERFACE_SOURCE_TABLE
1674 ,INTERFACE_ID
1675 ,ERROR_MSG
1676 ,MSG_TOKENS)
1677 VALUES (G_WORKER_REQ_ID
1678 ,HEADER_INTERFACE_ROWID
1679 ,'OKS_HEADERS_INTERFACE'
1680 ,HEADER_INTERFACE_ID
1681 ,'OKS_IMP_HDR_INVALID_QTE'
1682 ,NULL)
1683 WHEN (nvl(GRACE_DURATION, 1) <= 0 OR floor(nvl(GRACE_DURATION, 1)) <> nvl(GRACE_DURATION, 1)) THEN
1684 INTO OKS_INT_ERROR_STG_TEMP
1685 (CONCURRENT_REQUEST_ID
1686 ,HEADER_INTERFACE_ROWID
1687 ,INTERFACE_SOURCE_TABLE
1688 ,INTERFACE_ID
1689 ,ERROR_MSG
1690 ,MSG_TOKENS)
1691 VALUES (G_WORKER_REQ_ID
1692 ,HEADER_INTERFACE_ROWID
1693 ,'OKS_HEADERS_INTERFACE'
1694 ,HEADER_INTERFACE_ID
1695 ,'OKS_IMP_HDR_INVALID_GRD'
1696 ,NULL)
1697 WHEN (GRPER_V = 'N') THEN
1698 INTO OKS_INT_ERROR_STG_TEMP
1699 (CONCURRENT_REQUEST_ID
1700 ,HEADER_INTERFACE_ROWID
1701 ,INTERFACE_SOURCE_TABLE
1702 ,INTERFACE_ID
1703 ,ERROR_MSG
1704 ,MSG_TOKENS)
1705 VALUES (G_WORKER_REQ_ID
1706 ,HEADER_INTERFACE_ROWID
1707 ,'OKS_HEADERS_INTERFACE'
1708 ,HEADER_INTERFACE_ID
1709 ,'OKS_IMP_HDR_INVALID_GRP'
1710 ,NULL)
1711 WHEN (GRPER_NULL_ERROR = 'Y') THEN
1712 INTO OKS_INT_ERROR_STG_TEMP
1713 (CONCURRENT_REQUEST_ID
1714 ,HEADER_INTERFACE_ROWID
1715 ,INTERFACE_SOURCE_TABLE
1716 ,INTERFACE_ID
1717 ,ERROR_MSG
1718 ,MSG_TOKENS)
1719 VALUES (G_WORKER_REQ_ID
1720 ,HEADER_INTERFACE_ROWID
1721 ,'OKS_HEADERS_INTERFACE'
1722 ,HEADER_INTERFACE_ID
1723 ,'OKS_IMP_HDR_NULL_GRP'
1724 ,NULL)
1725 /* WHEN (nvl(ESTIMATION_PERCENT, 1) < 0 OR nvl(ESTIMATION_PERCENT, 1) > 100) THEN
1726 INTO OKS_INT_ERROR_STG_TEMP
1727 (CONCURRENT_REQUEST_ID
1728 ,HEADER_INTERFACE_ROWID
1729 ,INTERFACE_SOURCE_TABLE
1730 ,INTERFACE_ID
1731 ,ERROR_MSG
1732 ,MSG_TOKENS)
1733 VALUES (G_WORKER_REQ_ID
1734 ,HEADER_INTERFACE_ROWID
1735 ,'OKS_HEADERS_INTERFACE'
1736 ,HEADER_INTERFACE_ID
1737 ,'OKS_IMP_HDR_INVALID_ESTPERC'
1738 ,NULL) */
1739 WHEN (ESTIMATION_PERCENT IS NOT NULL AND ESTIMATION_DATE IS NULL) THEN
1740 INTO OKS_INT_ERROR_STG_TEMP
1741 (CONCURRENT_REQUEST_ID
1742 ,HEADER_INTERFACE_ROWID
1743 ,INTERFACE_SOURCE_TABLE
1744 ,INTERFACE_ID
1745 ,ERROR_MSG
1746 ,MSG_TOKENS)
1747 VALUES (G_WORKER_REQ_ID
1748 ,HEADER_INTERFACE_ROWID
1749 ,'OKS_HEADERS_INTERFACE'
1750 ,HEADER_INTERFACE_ID
1751 ,'OKS_IMP_HDR_INVALID_ESTDATE'
1752 ,NULL)
1753 WHEN (EST_DATE_NULL_ERROR = 'Y') THEN
1754 INTO OKS_INT_ERROR_STG_TEMP
1755 (CONCURRENT_REQUEST_ID
1756 ,HEADER_INTERFACE_ROWID
1757 ,INTERFACE_SOURCE_TABLE
1758 ,INTERFACE_ID
1759 ,ERROR_MSG
1760 ,MSG_TOKENS)
1761 VALUES (G_WORKER_REQ_ID
1762 ,HEADER_INTERFACE_ROWID
1763 ,'OKS_HEADERS_INTERFACE'
1764 ,HEADER_INTERFACE_ID
1765 ,'OKS_IMP_HDR_NULL_ESTDATE'
1766 ,NULL)
1767 WHEN (FA_V = 'N') THEN
1768 INTO OKS_INT_ERROR_STG_TEMP
1769 (CONCURRENT_REQUEST_ID
1770 ,HEADER_INTERFACE_ROWID
1771 ,INTERFACE_SOURCE_TABLE
1772 ,INTERFACE_ID
1773 ,ERROR_MSG
1774 ,MSG_TOKENS)
1775 VALUES (G_WORKER_REQ_ID
1776 ,HEADER_INTERFACE_ROWID
1777 ,'OKS_HEADERS_INTERFACE'
1778 ,HEADER_INTERFACE_ID
1779 ,'OKS_IMP_HDR_INVALID_FA'
1780 ,NULL)
1781 WHEN (FA_NULL_ERROR = 'Y') THEN
1782 INTO OKS_INT_ERROR_STG_TEMP
1783 (CONCURRENT_REQUEST_ID
1784 ,HEADER_INTERFACE_ROWID
1785 ,INTERFACE_SOURCE_TABLE
1786 ,INTERFACE_ID
1787 ,ERROR_MSG
1788 ,MSG_TOKENS)
1789 VALUES (G_WORKER_REQ_ID
1790 ,HEADER_INTERFACE_ROWID
1791 ,'OKS_HEADERS_INTERFACE'
1792 ,HEADER_INTERFACE_ID
1793 ,'OKS_IMP_HDR_NULL_FA'
1794 ,NULL)
1795 WHEN (QAC_V = 'N') THEN
1796 INTO OKS_INT_ERROR_STG_TEMP
1797 (CONCURRENT_REQUEST_ID
1798 ,HEADER_INTERFACE_ROWID
1799 ,INTERFACE_SOURCE_TABLE
1800 ,INTERFACE_ID
1801 ,ERROR_MSG
1802 ,MSG_TOKENS)
1803 VALUES (G_WORKER_REQ_ID
1804 ,HEADER_INTERFACE_ROWID
1805 ,'OKS_HEADERS_INTERFACE'
1806 ,HEADER_INTERFACE_ID
1807 ,'OKS_IMP_HDR_INVALID_QAC'
1808 ,NULL)
1809 WHEN (AP_V = 'N') THEN
1810 INTO OKS_INT_ERROR_STG_TEMP
1811 (CONCURRENT_REQUEST_ID
1812 ,HEADER_INTERFACE_ROWID
1813 ,INTERFACE_SOURCE_TABLE
1814 ,INTERFACE_ID
1815 ,ERROR_MSG
1816 ,MSG_TOKENS)
1817 VALUES (G_WORKER_REQ_ID
1818 ,HEADER_INTERFACE_ROWID
1819 ,'OKS_HEADERS_INTERFACE'
1820 ,HEADER_INTERFACE_ID
1821 ,'OKS_IMP_HDR_INVALID_AP'
1822 ,NULL)
1823 WHEN (SALESGROUP_NULL_ERROR = 'Y') THEN
1824 INTO OKS_INT_ERROR_STG_TEMP
1825 (CONCURRENT_REQUEST_ID
1826 ,HEADER_INTERFACE_ROWID
1827 ,INTERFACE_SOURCE_TABLE
1828 ,INTERFACE_ID
1829 ,ERROR_MSG
1830 ,MSG_TOKENS)
1831 VALUES (G_WORKER_REQ_ID
1832 ,HEADER_INTERFACE_ROWID
1833 ,'OKS_HEADERS_INTERFACE'
1834 ,HEADER_INTERFACE_ID
1835 ,'OKS_IMP_HDR_NULL_SALESG'
1836 ,NULL)
1837 SELECT HST.HEADER_INTERFACE_ROWID HEADER_INTERFACE_ROWID
1838 ,HST.HEADER_INTERFACE_ID HEADER_INTERFACE_ID
1839 ,(CASE WHEN HST.SALESGROUP_ID IS NOT NULL
1840 AND HST.SALESGROUP_ID <> -1
1841 AND NOT EXISTS (SELECT 'X'
1842 FROM JTF_RS_GROUP_MEMBERS MEM
1843 ,JTF_RS_SALESREPS SRP
1844 ,JTF_RS_GROUP_USAGES USG
1845 WHERE SRP.RESOURCE_ID = MEM.RESOURCE_ID
1846 AND MEM.GROUP_ID = USG.GROUP_ID
1847 AND USG.USAGE = 'SALES'
1848 AND SRP.SALESREP_ID = HST.SALESPERSON_ID
1849 AND SRP.ORG_ID = HST.OPERATING_UNIT_ID
1850 AND MEM.GROUP_ID = HST.SALESGROUP_ID)
1851 THEN 'N' ELSE 'Y' END) SALESGROUP_V
1852 ,(CASE WHEN HST.SALESPERSON_ID IS NOT NULL AND HST.SALESGROUP_ID IS NULL THEN 'Y' ELSE 'N' END) SALESGROUP_NULL_ERROR
1853 ,(CASE WHEN HST.QUOTE_TO_PARTY_SITE IS NOT NULL
1854 AND COUNT(DISTINCT QPS.ROWID) OVER (PARTITION BY HST.ROWID) <> 1
1855 THEN 'N' ELSE 'Y' END) QUOTE_TO_PARTY_SITE_V
1856 ,(CASE WHEN HST.QUOTE_TO_PARTY_SITE IS NOT NULL
1857 AND COUNT(DISTINCT QTC.ROWID) OVER (PARTITION BY HST.ROWID) <> 1
1858 THEN 'N' ELSE 'Y' END) QUOTE_TO_CONTACT_V
1859 ,(CASE WHEN HST.QUOTE_TO_PHONE IS NOT NULL
1860 AND COUNT(DISTINCT QTP.ROWID) OVER (PARTITION BY HST.ROWID) <> 1
1861 THEN 'N' ELSE 'Y' END) QUOTE_TO_PHONE_V
1862 ,(CASE WHEN HST.QUOTE_TO_FAX IS NOT NULL
1863 AND COUNT(DISTINCT QTF.ROWID) OVER (PARTITION BY HST.ROWID) <> 1
1864 THEN 'N' ELSE 'Y' END) QUOTE_TO_FAX_V
1865 ,(CASE WHEN HST.QUOTE_TO_EMAIL IS NOT NULL
1866 AND COUNT(DISTINCT QTE.ROWID) OVER (PARTITION BY HST.ROWID) <> 1
1867 THEN 'N' ELSE 'Y' END) QUOTE_TO_EMAIL_V
1868 ,HST.GRACE_DURATION GRACE_DURATION
1869 ,(CASE WHEN (HST.GRACE_DURATION IS NOT NULL
1870 AND COUNT(DISTINCT GRPER.ROWID) OVER (PARTITION BY HST.ROWID) <> 1)
1871 THEN 'N' ELSE 'Y' END) GRPER_V
1872 ,(CASE WHEN (HST.GRACE_PERIOD IS NOT NULL AND HST.GRACE_DURATION IS NULL)
1873 THEN 'Y' ELSE 'N' END) GRPER_NULL_ERROR
1874 ,HST.ESTIMATION_PERCENT ESTIMATION_PERCENT
1875 ,HST.ESTIMATION_DATE ESTIMATION_DATE
1876 ,(CASE WHEN (HST.ESTIMATION_DATE IS NOT NULL AND HST.ESTIMATION_PERCENT IS NULL)
1877 THEN 'Y' ELSE 'N' END) EST_DATE_NULL_ERROR
1878 ,(CASE WHEN (HST.FOLLOW_UP_DUE_DATE IS NOT NULL
1879 AND COUNT(DISTINCT FOA.ROWID) OVER (PARTITION BY HST.ROWID) <> 1)
1880 THEN 'N' ELSE 'Y' END) FA_V
1881 ,(CASE WHEN (HST.FOLLOW_UP_DUE_DATE IS NULL AND HST.FOLLOW_UP_ACTION IS NOT NULL)
1882 THEN 'Y' ELSE 'N' END) FA_NULL_ERROR
1883 ,(CASE WHEN COUNT(DISTINCT QAC.ROWID) OVER (PARTITION BY HST.ROWID) <> 1
1884 THEN 'N' ELSE 'Y' END) QAC_V
1885 ,(CASE WHEN COUNT(DISTINCT AP.ROWID) OVER (PARTITION BY HST.ROWID) <> 1
1886 THEN 'N' ELSE 'Y' END) AP_V
1887 FROM OKS_INT_HEADER_STG_TEMP HST
1888 ,HZ_CUST_ACCT_SITES_ALL QPS
1889 ,HZ_CUST_ACCOUNT_ROLES QTC
1890 ,HZ_CONTACT_POINTS QTP
1891 ,HZ_CONTACT_POINTS QTF
1892 ,HZ_CONTACT_POINTS QTE
1893 ,OKC_TIME_CODE_UNITS_B GRPER
1894 ,FND_LOOKUPS FOA
1895 ,OKC_QA_CHECK_LISTS_B QAC
1896 ,OKC_PROCESS_DEFS_B AP
1897 WHERE HST.OPERATING_UNIT_ID = QPS.ORG_ID (+)
1898 AND HST.QUOTE_TO_PARTY_SITE = QPS.CUST_ACCT_SITE_ID (+)
1899 AND HST.QUOTE_TO_CONTACT = QTC.CUST_ACCOUNT_ROLE_ID (+)
1900 AND QTC.ROLE_TYPE (+)= 'CONTACT'
1901 AND QTP.CONTACT_POINT_TYPE (+) = 'PHONE'
1902 AND HST.QUOTE_TO_PHONE = QTP.CONTACT_POINT_ID (+)
1903 AND NVL(QTP.PHONE_LINE_TYPE (+) ,'GEN') = 'GEN'
1904 AND QTF.CONTACT_POINT_TYPE (+) = 'PHONE'
1905 AND HST.QUOTE_TO_FAX = QTF.CONTACT_POINT_ID (+)
1906 AND QTF.PHONE_LINE_TYPE (+) = 'FAX'
1907 AND QTE.CONTACT_POINT_TYPE (+) = 'EMAIL'
1908 AND HST.QUOTE_TO_EMAIL = QTE.CONTACT_POINT_ID (+)
1909 AND HST.GRACE_PERIOD = GRPER.UOM_CODE (+)
1910 AND HST.FOLLOW_UP_ACTION = FOA.LOOKUP_CODE (+)
1911 AND FOA.LOOKUP_TYPE (+) = 'OKS_FOLLOWUP_ACTION'
1912 AND HST.QA_CHECKLIST = QAC.ID (+)
1913 AND HST.APPROVAL_PROCESS_ID = AP.ID (+);
1914
1915 l_stmt_num := 40;
1916
1917 INSERT ALL
1918 WHEN (TEC_V = 'N') THEN
1919 INTO OKS_INT_ERROR_STG_TEMP
1920 (CONCURRENT_REQUEST_ID
1921 ,HEADER_INTERFACE_ROWID
1922 ,INTERFACE_SOURCE_TABLE
1923 ,INTERFACE_ID
1924 ,ERROR_MSG
1925 ,MSG_TOKENS)
1926 VALUES (G_WORKER_REQ_ID
1927 ,HEADER_INTERFACE_ROWID
1928 ,'OKS_HEADERS_INTERFACE'
1929 ,HEADER_INTERFACE_ID
1930 ,'OKS_IMP_HDR_INVALID_TEC'
1931 ,NULL)
1932 WHEN (TUCEF_OU_V = 'N') THEN
1933 INTO OKS_INT_ERROR_STG_TEMP
1934 (CONCURRENT_REQUEST_ID
1935 ,HEADER_INTERFACE_ROWID
1936 ,INTERFACE_SOURCE_TABLE
1937 ,INTERFACE_ID
1938 ,ERROR_MSG
1939 ,MSG_TOKENS)
1940 VALUES (G_WORKER_REQ_ID
1941 ,HEADER_INTERFACE_ROWID
1942 ,'OKS_HEADERS_INTERFACE'
1943 ,HEADER_INTERFACE_ID
1944 ,'OKS_IMP_HDR_INVALID_TUCEF_OU'
1945 ,NULL)
1946 WHEN (TEN_V = 'N') THEN
1947 INTO OKS_INT_ERROR_STG_TEMP
1948 (CONCURRENT_REQUEST_ID
1949 ,HEADER_INTERFACE_ROWID
1950 ,INTERFACE_SOURCE_TABLE
1951 ,INTERFACE_ID
1952 ,ERROR_MSG
1953 ,MSG_TOKENS)
1954 VALUES (G_WORKER_REQ_ID
1955 ,HEADER_INTERFACE_ROWID
1956 ,'OKS_HEADERS_INTERFACE'
1957 ,HEADER_INTERFACE_ID
1958 ,'OKS_IMP_HDR_INVALID_TEN'
1959 ,NULL)
1960 WHEN (TEN_NULL_ERROR = 'Y') THEN
1961 INTO OKS_INT_ERROR_STG_TEMP
1962 (CONCURRENT_REQUEST_ID
1963 ,HEADER_INTERFACE_ROWID
1964 ,INTERFACE_SOURCE_TABLE
1965 ,INTERFACE_ID
1966 ,ERROR_MSG
1967 ,MSG_TOKENS)
1968 VALUES (G_WORKER_REQ_ID
1969 ,HEADER_INTERFACE_ROWID
1970 ,'OKS_HEADERS_INTERFACE'
1971 ,HEADER_INTERFACE_ID
1972 ,'OKS_IMP_HDR_NULL_TEN'
1973 ,NULL)
1974 WHEN (POREQ_V = 'N') THEN
1975 INTO OKS_INT_ERROR_STG_TEMP
1976 (CONCURRENT_REQUEST_ID
1977 ,HEADER_INTERFACE_ROWID
1978 ,INTERFACE_SOURCE_TABLE
1979 ,INTERFACE_ID
1980 ,ERROR_MSG
1981 ,MSG_TOKENS)
1982 VALUES (G_WORKER_REQ_ID
1983 ,HEADER_INTERFACE_ROWID
1984 ,'OKS_HEADERS_INTERFACE'
1985 ,HEADER_INTERFACE_ID
1986 ,'OKS_IMP_HDR_INVALID_POREQ'
1987 ,NULL)
1988 WHEN (POREQ_NULL_ERROR = 'Y') THEN
1989 INTO OKS_INT_ERROR_STG_TEMP
1990 (CONCURRENT_REQUEST_ID
1991 ,HEADER_INTERFACE_ROWID
1992 ,INTERFACE_SOURCE_TABLE
1993 ,INTERFACE_ID
1994 ,ERROR_MSG
1995 ,MSG_TOKENS)
1996 VALUES (G_WORKER_REQ_ID
1997 ,HEADER_INTERFACE_ROWID
1998 ,'OKS_HEADERS_INTERFACE'
1999 ,HEADER_INTERFACE_ID
2000 ,'OKS_IMP_HDR_NULL_POREQ'
2001 ,NULL)
2002 WHEN (BS_V = 'N') THEN
2003 INTO OKS_INT_ERROR_STG_TEMP
2004 (CONCURRENT_REQUEST_ID
2005 ,HEADER_INTERFACE_ROWID
2006 ,INTERFACE_SOURCE_TABLE
2007 ,INTERFACE_ID
2008 ,ERROR_MSG
2009 ,MSG_TOKENS)
2010 VALUES (G_WORKER_REQ_ID
2011 ,HEADER_INTERFACE_ROWID
2012 ,'OKS_HEADERS_INTERFACE'
2013 ,HEADER_INTERFACE_ID
2014 ,'OKS_IMP_HDR_INVALID_BS'
2015 ,NULL)
2016 WHEN (BTXNTYPE_V = 'N') THEN
2017 INTO OKS_INT_ERROR_STG_TEMP
2018 (CONCURRENT_REQUEST_ID
2019 ,HEADER_INTERFACE_ROWID
2020 ,INTERFACE_SOURCE_TABLE
2021 ,INTERFACE_ID
2022 ,ERROR_MSG
2023 ,MSG_TOKENS)
2024 VALUES (G_WORKER_REQ_ID
2025 ,HEADER_INTERFACE_ROWID
2026 ,'OKS_HEADERS_INTERFACE'
2027 ,HEADER_INTERFACE_ID
2028 ,'OKS_IMP_HDR_INVALID_BTXNTYPE'
2029 ,NULL)
2030 WHEN (HC_V = 'N') THEN
2031 INTO OKS_INT_ERROR_STG_TEMP
2032 (CONCURRENT_REQUEST_ID
2033 ,HEADER_INTERFACE_ROWID
2034 ,INTERFACE_SOURCE_TABLE
2035 ,INTERFACE_ID
2036 ,ERROR_MSG
2037 ,MSG_TOKENS)
2038 VALUES (G_WORKER_REQ_ID
2039 ,HEADER_INTERFACE_ROWID
2040 ,'OKS_HEADERS_INTERFACE'
2041 ,HEADER_INTERFACE_ID
2042 ,'OKS_IMP_HDR_INVALID_HC'
2043 ,NULL)
2044 WHEN (SP_V = 'N') THEN
2045 INTO OKS_INT_ERROR_STG_TEMP
2046 (CONCURRENT_REQUEST_ID
2047 ,HEADER_INTERFACE_ROWID
2048 ,INTERFACE_SOURCE_TABLE
2049 ,INTERFACE_ID
2050 ,ERROR_MSG
2051 ,MSG_TOKENS)
2052 VALUES (G_WORKER_REQ_ID
2053 ,HEADER_INTERFACE_ROWID
2054 ,'OKS_HEADERS_INTERFACE'
2055 ,HEADER_INTERFACE_ID
2056 ,'OKS_IMP_HDR_INVALID_SP'
2057 ,NULL)
2058 WHEN (ST_V = 'N') THEN
2059 INTO OKS_INT_ERROR_STG_TEMP
2060 (CONCURRENT_REQUEST_ID
2061 ,HEADER_INTERFACE_ROWID
2062 ,INTERFACE_SOURCE_TABLE
2063 ,INTERFACE_ID
2064 ,ERROR_MSG
2065 ,MSG_TOKENS)
2066 VALUES (G_WORKER_REQ_ID
2067 ,HEADER_INTERFACE_ROWID
2068 ,'OKS_HEADERS_INTERFACE'
2069 ,HEADER_INTERFACE_ID
2070 ,'OKS_IMP_HDR_INVALID_ST'
2071 ,NULL)
2072 WHEN (SER_CHRG_PREP_REQ_V = 'N') THEN
2073 INTO OKS_INT_ERROR_STG_TEMP
2074 (CONCURRENT_REQUEST_ID
2075 ,HEADER_INTERFACE_ROWID
2076 ,INTERFACE_SOURCE_TABLE
2077 ,INTERFACE_ID
2078 ,ERROR_MSG
2079 ,MSG_TOKENS)
2080 VALUES (G_WORKER_REQ_ID
2081 ,HEADER_INTERFACE_ROWID
2082 ,'OKS_HEADERS_INTERFACE'
2083 ,HEADER_INTERFACE_ID
2084 ,'OKS_IMP_HDR_INVALID_SCPREQ'
2085 ,NULL)
2086 WHEN (SER_CHRG_PO_REQ_V = 'N') THEN
2087 INTO OKS_INT_ERROR_STG_TEMP
2088 (CONCURRENT_REQUEST_ID
2089 ,HEADER_INTERFACE_ROWID
2090 ,INTERFACE_SOURCE_TABLE
2091 ,INTERFACE_ID
2092 ,ERROR_MSG
2093 ,MSG_TOKENS)
2094 VALUES (G_WORKER_REQ_ID
2095 ,HEADER_INTERFACE_ROWID
2096 ,'OKS_HEADERS_INTERFACE'
2097 ,HEADER_INTERFACE_ID
2098 ,'OKS_IMP_HDR_INVALID_SCPOREQ'
2099 ,NULL)
2100 WHEN (SOURCE_V = 'N') THEN
2101 INTO OKS_INT_ERROR_STG_TEMP
2102 (CONCURRENT_REQUEST_ID
2103 ,HEADER_INTERFACE_ROWID
2104 ,INTERFACE_SOURCE_TABLE
2105 ,INTERFACE_ID
2106 ,ERROR_MSG
2107 ,MSG_TOKENS)
2108 VALUES (G_WORKER_REQ_ID
2109 ,HEADER_INTERFACE_ROWID
2110 ,'OKS_HEADERS_INTERFACE'
2111 ,HEADER_INTERFACE_ID
2112 ,'OKS_IMP_HDR_INVALID_SOURCE'
2113 ,NULL)
2114 WHEN (HOI_COUNT = 0) THEN
2115 INTO OKS_INT_ERROR_STG_TEMP
2116 (CONCURRENT_REQUEST_ID
2117 ,HEADER_INTERFACE_ROWID
2118 ,INTERFACE_SOURCE_TABLE
2119 ,INTERFACE_ID
2120 ,ERROR_MSG
2121 ,MSG_TOKENS)
2122 VALUES (G_WORKER_REQ_ID
2123 ,HEADER_INTERFACE_ROWID
2124 ,'OKS_HEADERS_INTERFACE'
2125 ,HEADER_INTERFACE_ID
2126 ,'OKS_IMP_HST_INVALID_INVORG'
2127 ,NULL)
2128 WHEN (ERC_NULL_ERROR = 'Y') THEN
2129 INTO OKS_INT_ERROR_STG_TEMP
2130 (CONCURRENT_REQUEST_ID
2131 ,HEADER_INTERFACE_ROWID
2132 ,INTERFACE_SOURCE_TABLE
2133 ,INTERFACE_ID
2134 ,ERROR_MSG
2135 ,MSG_TOKENS)
2136 VALUES (G_WORKER_REQ_ID
2137 ,HEADER_INTERFACE_ROWID
2138 ,'OKS_HEADERS_INTERFACE'
2139 ,HEADER_INTERFACE_ID
2140 ,'OKS_IMP_HDR_NULL_ERC'
2141 ,NULL)
2142
2143 SELECT HST.HEADER_INTERFACE_ROWID HEADER_INTERFACE_ROWID
2144 ,HST.HEADER_INTERFACE_ID HEADER_INTERFACE_ID
2145 ,(CASE WHEN HST.TAX_EXEMPTION_CONTROL IS NOT NULL
2146 AND COUNT(DISTINCT TEC.ROWID) OVER (PARTITION BY HST.ROWID) <> 1
2147 THEN 'N' ELSE 'Y' END) TEC_V
2148 ,(CASE WHEN HST.TAX_EXEMPTION_CONTROL = 'E'
2149 AND NOT EXISTS (SELECT 'X' FROM ZX_PRODUCT_OPTIONS
2150 WHERE ORG_ID = HST.OPERATING_UNIT_ID
2151 AND TAX_METHOD_CODE <> 'LTE'
2152 AND APPLICATION_ID = 222
2153 AND TAX_USE_CUSTOMER_EXEMPT_FLAG = 'Y')
2154 THEN 'N' ELSE 'Y' END) TUCEF_OU_V
2155 ,(CASE WHEN HST.TAX_EXEMPTION_CONTROL = 'E'
2156 AND COUNT(DISTINCT TEN.ROW_ID) OVER (PARTITION BY HST.ROWID) = 0
2157 THEN 'N' ELSE 'Y' END) TEN_V
2158 ,(CASE WHEN (HST.TAX_EXEMPTION_CONTROL <> 'E' OR HST.TAX_EXEMPTION_CONTROL IS NULL)
2159 AND HST.TAX_EXEMPTION_NUMBER IS NOT NULL
2160 THEN 'Y' ELSE 'N' END) TEN_NULL_ERROR
2161 ,(CASE WHEN (HST.TAX_EXEMPTION_CONTROL <> 'E' OR HST.TAX_EXEMPTION_CONTROL IS NULL)
2162 AND HST.EXEMPT_REASON_CODE IS NOT NULL
2163 THEN 'Y' ELSE 'N' END) ERC_NULL_ERROR
2164 ,(CASE WHEN HST.PAYMENT_INSTRUCTION = 'PON' AND (HST.PO_REQUIRED NOT IN ('Y', 'N') OR HST.PO_REQUIRED IS NULL) THEN 'N' ELSE 'Y' END) POREQ_V
2165 ,(CASE WHEN HST.PO_REQUIRED IS NOT NULL AND (HST.PAYMENT_INSTRUCTION <> 'PON' OR HST.PAYMENT_INSTRUCTION IS NULL)
2166 THEN 'Y' ELSE 'N' END) POREQ_NULL_ERROR
2167 ,(CASE WHEN HST.BILL_SERVICES IS NOT NULL
2168 AND COUNT(DISTINCT BS.ROWID) OVER (PARTITION BY HST.ROWID) <> 1
2169 THEN 'N' ELSE 'Y' END) BS_V
2170 ,(CASE WHEN HST.BILLING_TRANSACTION_TYPE_ID IS NOT NULL
2171 AND COUNT(DISTINCT BTXNTYPE.ROWID) OVER (PARTITION BY HST.ROWID) <> 1
2172 THEN 'N' ELSE 'Y' END) BTXNTYPE_V
2173 ,(CASE WHEN HST.HOLD_CREDITS IS NOT NULL AND HST.HOLD_CREDITS NOT IN ('Y', 'N')
2174 THEN 'N' ELSE 'Y' END) HC_V
2175 ,(CASE WHEN HST.SUMMARY_PRINT IS NOT NULL AND HST.SUMMARY_PRINT NOT IN ('Y', 'N')
2176 THEN 'N' ELSE 'Y' END) SP_V
2177 ,(CASE WHEN HST.SUMMARY_TRANSACTIONS IS NOT NULL AND HST.SUMMARY_TRANSACTIONS NOT IN ('Y', 'N')
2178 THEN 'N' ELSE 'Y' END) ST_V
2179 ,(CASE WHEN HST.SERVICE_CHRG_PREPAY_REQ IS NOT NULL AND HST.SERVICE_CHRG_PREPAY_REQ NOT IN ('Y', 'N')
2180 THEN 'N' ELSE 'Y' END) SER_CHRG_PREP_REQ_V
2181 ,(CASE WHEN HST.SERVICE_CHARGES_PO_REQUIRED IS NOT NULL AND HST.SERVICE_CHARGES_PO_REQUIRED NOT IN ('Y', 'N')
2182 THEN 'N' ELSE 'Y' END) SER_CHRG_PO_REQ_V
2183 ,(CASE WHEN HST.SOURCE IS NOT NULL
2184 AND COUNT(DISTINCT SRC.ROWID) OVER (PARTITION BY HST.ROWID) = 0
2185 THEN 'N' ELSE 'Y' END) SOURCE_V
2186 ,COUNT(DISTINCT HOI.ROWID) OVER (PARTITION BY HST.ROWID) HOI_COUNT
2187 FROM OKS_INT_HEADER_STG_TEMP HST
2188 ,FND_LOOKUPS TEC
2189 ,(SELECT EX.ROWID ROW_ID, EX.EXEMPT_CERTIFICATE_NUMBER, EX.EXEMPT_REASON_CODE
2190 FROM ZX_EXEMPTIONS EX, AR_LOOKUPS ERC
2191 WHERE EX.EXEMPT_REASON_CODE = ERC.LOOKUP_CODE (+)
2192 AND ERC.LOOKUP_TYPE(+) = 'TAX_REASON') TEN
2193 ,FND_LOOKUPS BS
2194 ,RA_CUST_TRX_TYPES_ALL BTXNTYPE
2195 ,FND_LOOKUPS SRC
2196 ,HR_ORGANIZATION_INFORMATION HOI
2197 WHERE HST.TAX_EXEMPTION_CONTROL = TEC.LOOKUP_CODE (+)
2198 AND TEC.LOOKUP_TYPE (+) = 'ZX_EXEMPTION_CONTROL'
2199 AND HST.TAX_EXEMPTION_NUMBER = TEN.EXEMPT_CERTIFICATE_NUMBER (+)
2200 AND HST.EXEMPT_REASON_CODE = TEN.EXEMPT_REASON_CODE (+)
2201 AND HST.BILL_SERVICES = BS.LOOKUP_CODE (+)
2202 AND BS.LOOKUP_TYPE (+) = 'OKS_AR_INTERFACE'
2203 AND HST.OPERATING_UNIT_ID = BTXNTYPE.ORG_ID (+)
2204 AND HST.BILLING_TRANSACTION_TYPE_ID = BTXNTYPE.CUST_TRX_TYPE_ID (+)
2205 AND BTXNTYPE.TYPE (+) = 'INV'
2206 AND SRC.LOOKUP_TYPE (+) = 'OKC_CONTRACT_SOURCES'
2207 AND HST.SOURCE = SRC.LOOKUP_CODE (+)
2208 AND HST.INV_ORGANIZATION_ID = HOI.ORGANIZATION_ID (+)
2209 AND HOI.ORG_INFORMATION_CONTEXT (+) = 'CLASS'
2210 AND HOI.ORG_INFORMATION1 (+) = 'INV';
2211
2212 /*IF G_PROCEDURE_LOG THEN
2213 fnd_log.string(fnd_log.level_procedure,
2214 G_MODULE_HEAD || l_routine || '.' || l_stmt_num,
2215 'Exit.');
2216 END IF; */
2217 EXCEPTION
2218 WHEN FND_API.G_EXC_ERROR THEN
2219 RAISE FND_API.G_EXC_ERROR;
2220 WHEN OTHERS THEN
2221 FND_MESSAGE.Set_Name('OKS', 'OKS_IMPORT_UNEXPECTED');
2222 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
2223 FND_MESSAGE.set_token('MESSAGE', 'stmt_num '||l_stmt_num||' ('||SQLCODE||') '||SQLERRM);
2224 FND_MSG_PUB.Add;
2225 RAISE FND_API.G_EXC_ERROR;
2226 END Validate_header;
2227
2228 --========================================================================
2229 -- PROCEDURE : Validate_lines PRIVATE
2230 -- PARAMETERS:
2231 -- COMMENT : This procedure will perform the validation needed
2232 -- on the lines interface records.
2233 --=========================================================================
2234
2235 PROCEDURE Validate_lines
2236 IS
2237 l_stmt_num NUMBER := 0;
2238 l_routine CONSTANT VARCHAR2(30) := 'Validate_lines';
2239 l_int_count NUMBER := 0;
2240 l_stg_count NUMBER := 0;
2241 BEGIN
2242 IF G_PROCEDURE_LOG THEN
2243 fnd_log.string(fnd_log.level_procedure,
2244 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
2245 'Begin');
2246 END IF;
2247
2248 l_stmt_num := 10;
2249
2250 INSERT ALL
2251 WHEN (1 = 1) THEN
2252 INTO OKS_INT_LINE_STG_TEMP
2253 (LINE_INTERFACE_ID
2254 ,LINE_NUMBER
2255 ,HEADER_INTERFACE_ID
2256 ,HEADER_INTERFACE_ROWID
2257 ,LINE_INTERFACE_ROWID
2258 ,LSE_ID
2259 ,LINE_TYPE
2260 ,ITEM_ORGANIZATION_ID
2261 ,ITEM_ID
2262 ,REFERENCE_TEMPLATE_ID
2263 ,LINE_REFERENCE
2264 ,STATUS_CODE
2265 ,START_DATE
2266 ,END_DATE
2267 ,BILL_TO_SITE_USAGE_ID
2268 ,SHIP_TO_SITE_USAGE_ID
2269 ,CUSTOMER_BILLING_CONTACT_ID
2270 ,CUSTOMER_SHIPPING_CONTACT_ID
2271 ,RENEWAL_TYPE_CODE
2272 ,CANCELLATION_DATE
2273 ,CANCELLATION_REASON
2274 ,PRICE_LIST_ID
2275 ,INVOICE_TEXT
2276 ,PRINT_INVOICE
2277 ,SUBTOTAL
2278 ,TAX_AMOUNT
2279 ,TAX_EXEMPTION_CONTROL
2280 ,TAX_EXEMPTION_NUMBER
2281 ,TAX_CLASSIFICATION_CODE
2282 ,PAYMENT_INSTRUCTION
2283 ,PO_REQUIRED
2284 ,PAYMENT_INSTRUCTION_DETAILS
2285 ,PAYMENT_METHOD_CODE
2286 ,COMMITMENT_ID
2287 ,ACCOUNTING_RULE_ID
2288 ,INVOICING_RULE_ID
2289 ,RECUR_BILL_OCCURANCES
2290 ,BILLING_INTERVAL_DURATION
2291 ,BILLING_INTERVAL_PERIOD
2292 ,FIRST_BILL_UPTO_DATE
2293 ,LAST_BILL_FROM_DATE
2294 ,SUBSCRIPTION_QUANTITY
2295 ,QUANTITY_UOM
2296 ,PRICE_UOM
2297 ,UNIT_PRICE
2298 ,FIRST_BILLED_AMOUNT
2299 ,LAST_BILLED_AMOUNT
2300 ,USAGE_TYPE
2301 ,USAGE_PERIOD
2302 ,AVERAGING_INTERVAL
2303 ,SETTLEMENT_INTERVAL
2304 ,USAGE_TERMINATION_METHOD)
2305 VALUES (LINE_INTERFACE_ID
2306 ,LINE_NUMBER
2307 ,HEADER_INTERFACE_ID
2308 ,HEADER_INTERFACE_ROWID
2309 ,LINE_INTERFACE_ROWID
2310 ,LSE_ID
2311 ,LINE_TYPE
2312 ,ITEM_ORGANIZATION_ID
2313 ,ITEM_ID
2314 ,REFERENCE_TEMPLATE_ID
2315 ,LINE_REFERENCE
2316 ,STATUS_CODE
2317 ,START_DATE
2318 ,END_DATE
2319 ,BILL_TO_SITE_USAGE_ID
2320 ,SHIP_TO_SITE_USAGE_ID
2321 ,CUSTOMER_BILLING_CONTACT_ID
2322 ,CUSTOMER_SHIPPING_CONTACT_ID
2323 ,RENEWAL_TYPE_CODE
2324 ,CANCELLATION_DATE
2325 ,CANCELLATION_REASON
2326 ,PRICE_LIST_ID
2327 ,INVOICE_TEXT
2328 ,PRINT_INVOICE
2329 ,SUBTOTAL
2330 ,TAX_AMOUNT
2331 ,TAX_EXEMPTION_CONTROL
2332 ,TAX_EXEMPTION_NUMBER
2333 ,TAX_CLASSIFICATION_CODE
2334 ,PAYMENT_INSTRUCTION
2335 ,PO_REQUIRED
2336 ,PAYMENT_INSTRUCTION_DETAILS
2337 ,PAYMENT_METHOD_CODE
2338 ,COMMITMENT_ID
2339 ,ACCOUNTING_RULE_ID
2340 ,INVOICING_RULE_ID
2341 ,RECUR_BILL_OCCURANCES
2342 ,BILLING_INTERVAL_DURATION
2343 ,BILLING_INTERVAL_PERIOD
2344 ,FIRST_BILL_UPTO_DATE
2345 ,LAST_BILL_FROM_DATE
2346 ,SUBSCRIPTION_QUANTITY
2347 ,QUANTITY_UOM
2348 ,PRICE_UOM
2349 ,UNIT_PRICE
2350 ,FIRST_BILLED_AMOUNT
2351 ,LAST_BILLED_AMOUNT
2352 ,USAGE_TYPE
2353 ,USAGE_PERIOD
2354 ,AVERAGING_INTERVAL
2355 ,SETTLEMENT_INTERVAL
2356 ,USAGE_TERMINATION_METHOD)
2357 WHEN (LSE_ID IS NULL) THEN
2358 INTO OKS_INT_ERROR_STG_TEMP
2359 (CONCURRENT_REQUEST_ID
2360 ,HEADER_INTERFACE_ROWID
2361 ,INTERFACE_SOURCE_TABLE
2362 ,INTERFACE_ID
2363 ,ERROR_MSG
2364 ,MSG_TOKENS)
2365 VALUES (G_WORKER_REQ_ID
2366 ,HEADER_INTERFACE_ROWID
2367 ,'OKS_LINES_INTERFACE'
2368 ,LINE_INTERFACE_ID
2369 ,'OKS_IMP_LIN_INVALID_LTYPE'
2370 ,NULL)
2371 WHEN (ITEM_ID IS NULL) THEN
2372 INTO OKS_INT_ERROR_STG_TEMP
2373 (CONCURRENT_REQUEST_ID
2374 ,HEADER_INTERFACE_ROWID
2375 ,INTERFACE_SOURCE_TABLE
2376 ,INTERFACE_ID
2377 ,ERROR_MSG
2378 ,MSG_TOKENS)
2379 VALUES (G_WORKER_REQ_ID
2380 ,HEADER_INTERFACE_ROWID
2381 ,'OKS_LINES_INTERFACE'
2382 ,LINE_INTERFACE_ID
2383 ,'OKS_IMP_LIN_INVALID_ITEM'
2384 ,NULL)
2385 WHEN (BILL_TO_SITE_USAGE_ID IS NULL) THEN
2386 INTO OKS_INT_ERROR_STG_TEMP
2387 (CONCURRENT_REQUEST_ID
2388 ,HEADER_INTERFACE_ROWID
2389 ,INTERFACE_SOURCE_TABLE
2390 ,INTERFACE_ID
2391 ,ERROR_MSG
2392 ,MSG_TOKENS)
2393 VALUES (G_WORKER_REQ_ID
2394 ,HEADER_INTERFACE_ROWID
2395 ,'OKS_LINES_INTERFACE'
2396 ,LINE_INTERFACE_ID
2397 ,'OKS_IMP_LIN_INVALID_BTSU'
2398 ,NULL)
2399 WHEN (SHIP_TO_SITE_USAGE_ID IS NULL) THEN
2400 INTO OKS_INT_ERROR_STG_TEMP
2401 (CONCURRENT_REQUEST_ID
2402 ,HEADER_INTERFACE_ROWID
2403 ,INTERFACE_SOURCE_TABLE
2404 ,INTERFACE_ID
2405 ,ERROR_MSG
2406 ,MSG_TOKENS)
2407 VALUES (G_WORKER_REQ_ID
2408 ,HEADER_INTERFACE_ROWID
2409 ,'OKS_LINES_INTERFACE'
2410 ,LINE_INTERFACE_ID
2411 ,'OKS_IMP_LIN_INVALID_STSU'
2412 ,NULL)
2413 WHEN (LINE_TYPE IN ('SERVICE', 'EXT_WARRANTY') AND INVOICE_TEXT IS NULL) THEN
2414 INTO OKS_INT_ERROR_STG_TEMP
2415 (CONCURRENT_REQUEST_ID
2416 ,HEADER_INTERFACE_ROWID
2417 ,INTERFACE_SOURCE_TABLE
2418 ,INTERFACE_ID
2419 ,ERROR_MSG
2420 ,MSG_TOKENS)
2421 VALUES (G_WORKER_REQ_ID
2422 ,HEADER_INTERFACE_ROWID
2423 ,'OKS_LINES_INTERFACE'
2424 ,LINE_INTERFACE_ID
2425 ,'OKS_IMP_LIN_INVALID_INVTXT'
2426 ,NULL)
2427 WHEN (LINE_TYPE IN ('SERVICE', 'EXT_WARRANTY') AND CUSTOMER_BILLING_CONTACT_ID IS NULL)
2428 OR (BILLING_CONTACT_PROVIDED = 'Y' AND CUSTOMER_BILLING_CONTACT_ID IS NULL) THEN
2429 INTO OKS_INT_ERROR_STG_TEMP
2430 (CONCURRENT_REQUEST_ID
2431 ,HEADER_INTERFACE_ROWID
2432 ,INTERFACE_SOURCE_TABLE
2433 ,INTERFACE_ID
2434 ,ERROR_MSG
2435 ,MSG_TOKENS)
2436 VALUES (G_WORKER_REQ_ID
2437 ,HEADER_INTERFACE_ROWID
2438 ,'OKS_LINES_INTERFACE'
2439 ,LINE_INTERFACE_ID
2440 ,'OKS_IMP_LIN_INVALID_BC'
2441 ,NULL)
2442 WHEN (LINE_TYPE IN ('SERVICE', 'EXT_WARRANTY') AND CUSTOMER_SHIPPING_CONTACT_ID IS NULL)
2443 OR (SHIPPING_CONTACT_PROVIDED = 'Y' AND CUSTOMER_SHIPPING_CONTACT_ID IS NULL) THEN
2444 INTO OKS_INT_ERROR_STG_TEMP
2445 (CONCURRENT_REQUEST_ID
2446 ,HEADER_INTERFACE_ROWID
2447 ,INTERFACE_SOURCE_TABLE
2448 ,INTERFACE_ID
2449 ,ERROR_MSG
2450 ,MSG_TOKENS)
2451 VALUES (G_WORKER_REQ_ID
2452 ,HEADER_INTERFACE_ROWID
2453 ,'OKS_LINES_INTERFACE'
2454 ,LINE_INTERFACE_ID
2455 ,'OKS_IMP_LIN_INVALID_SC'
2456 ,NULL)
2457 WHEN (PL_PROVIDED = 'Y' AND PRICE_LIST_ID IS NULL) THEN
2458 INTO OKS_INT_ERROR_STG_TEMP
2459 (CONCURRENT_REQUEST_ID
2460 ,HEADER_INTERFACE_ROWID
2461 ,INTERFACE_SOURCE_TABLE
2462 ,INTERFACE_ID
2463 ,ERROR_MSG
2464 ,MSG_TOKENS)
2465 VALUES (G_WORKER_REQ_ID
2466 ,HEADER_INTERFACE_ROWID
2467 ,'OKS_LINES_INTERFACE'
2468 ,LINE_INTERFACE_ID
2469 ,'OKS_IMP_LIN_INVALID_QP'
2470 ,NULL)
2471 WHEN (LIN_PAYMENT_INSTRUCTION IS NOT NULL AND PAYMENT_INSTRUCTION IS NULL) THEN
2472 INTO OKS_INT_ERROR_STG_TEMP
2473 (CONCURRENT_REQUEST_ID
2474 ,HEADER_INTERFACE_ROWID
2475 ,INTERFACE_SOURCE_TABLE
2476 ,INTERFACE_ID
2477 ,ERROR_MSG
2478 ,MSG_TOKENS)
2479 VALUES (G_WORKER_REQ_ID
2480 ,HEADER_INTERFACE_ROWID
2481 ,'OKS_LINES_INTERFACE'
2482 ,LINE_INTERFACE_ID
2483 ,'OKS_IMP_LIN_INVALID_PAY_INST'
2484 ,NULL)
2485 WHEN (ACCOUNTING_RULE_ID IS NULL) THEN
2486 INTO OKS_INT_ERROR_STG_TEMP
2487 (CONCURRENT_REQUEST_ID
2488 ,HEADER_INTERFACE_ROWID
2489 ,INTERFACE_SOURCE_TABLE
2490 ,INTERFACE_ID
2491 ,ERROR_MSG
2492 ,MSG_TOKENS)
2493 VALUES (G_WORKER_REQ_ID
2494 ,HEADER_INTERFACE_ROWID
2495 ,'OKS_LINES_INTERFACE'
2496 ,LINE_INTERFACE_ID
2497 ,'OKS_IMP_LIN_INVALID_AR'
2498 ,NULL)
2499 WHEN (INVOICING_RULE_ID IS NULL) THEN
2500 INTO OKS_INT_ERROR_STG_TEMP
2501 (CONCURRENT_REQUEST_ID
2502 ,HEADER_INTERFACE_ROWID
2503 ,INTERFACE_SOURCE_TABLE
2504 ,INTERFACE_ID
2505 ,ERROR_MSG
2506 ,MSG_TOKENS)
2507 VALUES (G_WORKER_REQ_ID
2508 ,HEADER_INTERFACE_ROWID
2509 ,'OKS_LINES_INTERFACE'
2510 ,LINE_INTERFACE_ID
2511 ,'OKS_IMP_LIN_INVALID_IR'
2512 ,NULL)
2513 WHEN (LIN_PAYMENT_METHOD_CODE IS NOT NULL AND PAYMENT_METHOD_CODE IS NULL) THEN
2514 INTO OKS_INT_ERROR_STG_TEMP
2515 (CONCURRENT_REQUEST_ID
2516 ,HEADER_INTERFACE_ROWID
2517 ,INTERFACE_SOURCE_TABLE
2518 ,INTERFACE_ID
2519 ,ERROR_MSG
2520 ,MSG_TOKENS)
2521 VALUES (G_WORKER_REQ_ID
2522 ,HEADER_INTERFACE_ROWID
2523 ,'OKS_LINES_INTERFACE'
2524 ,LINE_INTERFACE_ID
2525 ,'OKS_IMP_LIN_INVALID_PMC'
2526 ,NULL)
2527 WHEN ((PAYMENT_METHOD_CODE IS NOT NULL OR LIN_COMMITMENT_ID IS NOT NULL) AND COMMITMENT_ID IS NULL) THEN
2528 INTO OKS_INT_ERROR_STG_TEMP
2529 (CONCURRENT_REQUEST_ID
2530 ,HEADER_INTERFACE_ROWID
2531 ,INTERFACE_SOURCE_TABLE
2532 ,INTERFACE_ID
2533 ,ERROR_MSG
2534 ,MSG_TOKENS)
2535 VALUES (G_WORKER_REQ_ID
2536 ,HEADER_INTERFACE_ROWID
2537 ,'OKS_LINES_INTERFACE'
2538 ,LINE_INTERFACE_ID
2539 ,'OKS_IMP_LIN_INVALID_CN'
2540 ,NULL)
2541 WHEN (LINE_TYPE = 'SUBSCRIPTION' AND (TAX_AMOUNT IS NULL OR TAX_AMOUNT < 0 )) THEN
2542 INTO OKS_INT_ERROR_STG_TEMP
2543 (CONCURRENT_REQUEST_ID
2544 ,HEADER_INTERFACE_ROWID
2545 ,INTERFACE_SOURCE_TABLE
2546 ,INTERFACE_ID
2547 ,ERROR_MSG
2548 ,MSG_TOKENS)
2549 VALUES (G_WORKER_REQ_ID
2550 ,HEADER_INTERFACE_ROWID
2551 ,'OKS_LINES_INTERFACE'
2552 ,LINE_INTERFACE_ID
2553 ,'OKS_IMP_LIN_INVALID_TAX'
2554 ,NULL)
2555 WHEN (LINE_TYPE = 'SUBSCRIPTION' AND (SUBTOTAL IS NULL OR SUBTOTAL < 0 )) THEN
2556 INTO OKS_INT_ERROR_STG_TEMP
2557 (CONCURRENT_REQUEST_ID
2558 ,HEADER_INTERFACE_ROWID
2559 ,INTERFACE_SOURCE_TABLE
2560 ,INTERFACE_ID
2561 ,ERROR_MSG
2562 ,MSG_TOKENS)
2563 VALUES (G_WORKER_REQ_ID
2564 ,HEADER_INTERFACE_ROWID
2565 ,'OKS_LINES_INTERFACE'
2566 ,LINE_INTERFACE_ID
2567 ,'OKS_IMP_LIN_INVALID_TOT'
2568 ,NULL)
2569 SELECT OLI.LINE_INTERFACE_ID LINE_INTERFACE_ID
2570 ,OLI.HEADER_INTERFACE_ID HEADER_INTERFACE_ID
2571 ,HDR.HEADER_INTERFACE_ROWID HEADER_INTERFACE_ROWID
2572 ,OLI.ROWID LINE_INTERFACE_ROWID
2573 ,OLI.LINE_NUMBER LINE_NUMBER
2574 ,(CASE WHEN COUNT(DISTINCT LS.ROW_ID) OVER (PARTITION BY OLI.ROWID) = 1
2575 AND ((HDR.CATEGORY = 'SERVICE' AND OLI.LINE_TYPE IN ('SERVICE', 'USAGE'))
2576 OR (HDR.CATEGORY = 'WARRANTY' AND OLI.LINE_TYPE IN ('EXT_WARRANTY', 'WARRANTY'))
2577 OR (HDR.CATEGORY = 'SUBSCRIPTION' AND OLI.LINE_TYPE IN ('SERVICE', 'SUBSCRIPTION', 'USAGE')))
2578 THEN LS.ID
2579 ELSE NULL
2580 END) LSE_ID
2581 ,OLI.LINE_TYPE LINE_TYPE
2582 ,OLI.ITEM_ORGANIZATION_ID ITEM_ORGANIZATION_ID
2583 ,(CASE WHEN NAMEID_Q.MSI_COUNT = 1 AND NAMEID_Q.MSI_V = 'Y' THEN NAMEID_Q.MSI_ID
2584 WHEN NAMEID_Q.MSN_COUNT = 1 AND NAMEID_Q.MSN_V = 'Y' THEN NAMEID_Q.MSN_ID
2585 ELSE NULL
2586 END) ITEM_ID
2587 ,(CASE WHEN OLI.REFERENCE_TEMPLATE_ID IS NOT NULL THEN OLI.REFERENCE_TEMPLATE_ID
2588 WHEN NAMEID_Q.MSI_COUNT = 1 AND NAMEID_Q.MSI_V = 'Y' THEN NAMEID_Q.MSI_REF_TEMPLATE_ID
2589 WHEN NAMEID_Q.MSN_COUNT = 1 AND NAMEID_Q.MSN_V = 'Y' THEN NAMEID_Q.MSN_REF_TEMPLATE_ID
2590 ELSE NULL
2591 END) REFERENCE_TEMPLATE_ID
2592 ,RTRIM(OLI.LINE_REFERENCE) LINE_REFERENCE
2593 ,OLI.STATUS_CODE STATUS_CODE
2594 ,OLI.START_DATE START_DATE
2595 ,OLI.END_DATE END_DATE
2596 ,(CASE WHEN NAMEID_Q.BTSUI_COUNT = 1 AND NAMEID_Q.BTSUI_V = 'Y' THEN NAMEID_Q.BTSUI_ID
2597 WHEN NAMEID_Q.BTSUN_COUNT = 1 THEN NAMEID_Q.BTSUN_ID
2598 ELSE NULL END) BILL_TO_SITE_USAGE_ID
2599 ,(CASE WHEN NAMEID_Q.STSUI_COUNT = 1 AND NAMEID_Q.STSUI_V = 'Y' THEN NAMEID_Q.STSUI_ID
2600 WHEN NAMEID_Q.STSUN_COUNT = 1 THEN NAMEID_Q.STSUN_ID
2601 ELSE NULL END) SHIP_TO_SITE_USAGE_ID
2602 ,(CASE WHEN NAMEID_Q.BCI_COUNT = 1 THEN NAMEID_Q.BCI_ID
2603 /* WHEN NAMEID_Q.BCN_COUNT = 1 THEN NAMEID_Q.BCN_ID */
2604 ELSE NULL
2605 END) CUSTOMER_BILLING_CONTACT_ID
2606 ,(CASE WHEN OLI.BILLING_CONTACT_ID IS NOT NULL /*OR OLI.BILLING_CONTACT_NAME IS NOT NULL*/ THEN 'Y' ELSE 'N' END) BILLING_CONTACT_PROVIDED
2607 ,(CASE WHEN OLI.SHIPPING_CONTACT_ID IS NOT NULL /*OR OLI.SHIPPING_CONTACT_NAME IS NOT NULL*/ THEN 'Y' ELSE 'N' END) SHIPPING_CONTACT_PROVIDED
2608 ,(CASE WHEN NAMEID_Q.SCI_COUNT = 1 THEN NAMEID_Q.SCI_ID
2609 /* WHEN NAMEID_Q.SCN_COUNT = 1 THEN NAMEID_Q.SCN_ID */
2610 ELSE NULL
2611 END) CUSTOMER_SHIPPING_CONTACT_ID
2612 ,OLI.RENEWAL_TYPE_CODE RENEWAL_TYPE_CODE
2613 ,OLI.CANCELLATION_DATE CANCELLATION_DATE
2614 ,OLI.CANCELLATION_REASON CANCELLATION_REASON
2615 ,(CASE WHEN NAMEID_Q.QPI_V = 'Y' AND NAMEID_Q.QPI_COUNT = 1 THEN NAMEID_Q.QPI_ID
2616 WHEN NAMEID_Q.QPTLN_V = 'Y' AND NAMEID_Q.QPN_COUNT = 1 THEN NAMEID_Q.QPN_ID
2617 ELSE NULL
2618 END) PRICE_LIST_ID
2619 ,(CASE WHEN OLI.PRICE_LIST_ID IS NOT NULL OR OLI.PRICE_LIST_NAME IS NOT NULL THEN 'Y' ELSE 'N' END) PL_PROVIDED
2620 ,rtrim(OLI.INVOICE_TEXT) INVOICE_TEXT
2621 ,OLI.PRINT_INVOICE PRINT_INVOICE
2622 ,OLI.SUBTOTAL SUBTOTAL
2623 ,OLI.TAX_AMOUNT TAX_AMOUNT
2624 ,OLI.TAX_EXEMPTION_CONTROL TAX_EXEMPTION_CONTROL
2625 ,OLI.TAX_EXEMPTION_NUMBER TAX_EXEMPTION_NUMBER
2626 ,OLI.TAX_CLASSIFICATION_CODE TAX_CLASSIFICATION_CODE
2627 ,(CASE WHEN COUNT(DISTINCT FL.ROWID) OVER (PARTITION BY OLI.ROWID) = 1
2628 THEN OLI.PAYMENT_INSTRUCTION
2629 ELSE NULL
2630 END) PAYMENT_INSTRUCTION
2631 ,OLI.PAYMENT_INSTRUCTION LIN_PAYMENT_INSTRUCTION
2632 ,OLI.PO_REQUIRED PO_REQUIRED
2633 ,rtrim(OLI.PAYMENT_INSTRUCTION_DETAILS) PAYMENT_INSTRUCTION_DETAILS
2634 ,(CASE WHEN COUNT(DISTINCT PMC.ROWID) OVER (PARTITION BY OLI.ROWID) = 1
2635 THEN OLI.PAYMENT_METHOD_CODE
2636 ELSE NULL
2637 END) PAYMENT_METHOD_CODE
2638 ,OLI.PAYMENT_METHOD_CODE LIN_PAYMENT_METHOD_CODE
2639 ,(CASE WHEN COUNT(DISTINCT CNI.ROWID) OVER (PARTITION BY OLI.ROWID) = 1
2640 THEN OLI.COMMITMENT_ID
2641 ELSE NULL
2642 END) COMMITMENT_ID
2643 ,OLI.COMMITMENT_ID LIN_COMMITMENT_ID
2644 ,(CASE WHEN ARI_ID_VAL_FLAG = 'Y' AND NAMEID_Q.ARI_COUNT = 1 THEN NAMEID_Q.ARI_ID
2645 WHEN ARN_ID_VAL_FLAG = 'Y' AND NAMEID_Q.ARN_COUNT = 1 THEN NAMEID_Q.ARN_ID
2646 ELSE NULL
2647 END) ACCOUNTING_RULE_ID
2648 ,(CASE WHEN NAMEID_Q.IRI_COUNT = 1 THEN NAMEID_Q.IRI_ID
2649 WHEN NAMEID_Q.IRN_COUNT = 1 THEN NAMEID_Q.IRN_ID
2650 ELSE NULL
2651 END) INVOICING_RULE_ID
2652 ,OLI.BILLING_INTERVAL_DURATION BILLING_INTERVAL_DURATION
2653 ,OLI.BILLING_INTERVAL_PERIOD BILLING_INTERVAL_PERIOD
2654 ,trunc(OLI.FIRST_BILL_UPTO_DATE) FIRST_BILL_UPTO_DATE
2655 ,OLI.SUBSCRIPTION_QUANTITY SUBSCRIPTION_QUANTITY
2656 ,OLI.QUANTITY_UOM QUANTITY_UOM
2657 ,OLI.PRICE_UOM PRICE_UOM
2658 ,OLI.UNIT_PRICE UNIT_PRICE
2659 ,OLI.FIRST_BILLED_AMOUNT FIRST_BILLED_AMOUNT
2660 ,OLI.LAST_BILLED_AMOUNT LAST_BILLED_AMOUNT
2661 ,OLI.USAGE_TYPE USAGE_TYPE
2662 ,OLI.USAGE_PERIOD USAGE_PERIOD
2663 ,OLI.AVERAGING_INTERVAL AVERAGING_INTERVAL
2664 ,OLI.SETTLEMENT_INTERVAL SETTLEMENT_INTERVAL
2665 ,OLI.USAGE_TERMINATION_METHOD USAGE_TERMINATION_METHOD
2666 ,OLI.RECUR_BILL_OCCURANCES RECUR_BILL_OCCURANCES
2667 ,(CASE WHEN OLI.RECUR_BILL_OCCURANCES > 0 AND OLI.BILLING_INTERVAL_DURATION > 0
2668 THEN (CASE WHEN OLI.BILLING_INTERVAL_PERIOD = 'YR'
2669 THEN add_months(NAMEID_Q.BILL_START_DATE, OLI.RECUR_BILL_OCCURANCES * 12 * OLI.BILLING_INTERVAL_DURATION)
2670 WHEN OLI.BILLING_INTERVAL_PERIOD = 'QRT'
2671 THEN add_months(NAMEID_Q.BILL_START_DATE, OLI.RECUR_BILL_OCCURANCES * 3 * OLI.BILLING_INTERVAL_DURATION)
2672 WHEN OLI.BILLING_INTERVAL_PERIOD = 'MTH'
2673 THEN add_months(NAMEID_Q.BILL_START_DATE, OLI.RECUR_BILL_OCCURANCES * OLI.BILLING_INTERVAL_DURATION)
2674 WHEN OLI.BILLING_INTERVAL_PERIOD = 'WK'
2675 THEN NAMEID_Q.BILL_START_DATE + OLI.RECUR_BILL_OCCURANCES * 7 * OLI.BILLING_INTERVAL_DURATION
2676 WHEN OLI.BILLING_INTERVAL_PERIOD = 'DAY'
2677 THEN NAMEID_Q.BILL_START_DATE + OLI.RECUR_BILL_OCCURANCES * OLI.BILLING_INTERVAL_DURATION
2678 ELSE NULL
2679 END)
2680 ELSE NULL
2681 END) LAST_BILL_FROM_DATE
2682 FROM OKS_LINES_INTERFACE OLI
2683 ,OKS_INT_HEADER_STG_TEMP HDR
2684 ,FND_LOOKUPS FL
2685 ,RA_CUSTOMER_TRX_ALL CNI
2686 ,FND_LOOKUPS PMC
2687 ,(SELECT LSE.ROWID ROW_ID, LSE.LTY_CODE, LSE.ID ID
2688 FROM OKC_LINE_STYLES_B LSE, FND_APPLICATION FA
2689 WHERE LSE.APPLICATION_ID = FA.APPLICATION_ID
2690 AND FA.APPLICATION_SHORT_NAME = 'OKS') LS
2691 ,(SELECT distinct(LNI.LINE_INTERFACE_ID)
2692 ,COUNT(DISTINCT QPI.ROWID) OVER (PARTITION BY LNI.ROWID) QPI_COUNT
2693 ,MAX(LNI.PRICE_LIST_ID) OVER (PARTITION BY LNI.ROWID) QPI_ID -- PRICE LIST ID based on Id
2694 ,(CASE WHEN HST.CONTRACT_CURRENCY_CODE = QPI.CURRENCY_CODE THEN 'Y' ELSE 'N' END) QPI_V
2695 ,COUNT(DISTINCT QPTLN.ROW_ID) OVER (PARTITION BY LNI.ROWID) QPN_COUNT
2696 ,MAX(QPTLN.LIST_HEADER_ID) OVER (PARTITION BY LNI.ROWID) QPN_ID -- PRICE LIST ID based on Name
2697 ,(CASE WHEN HST.CONTRACT_CURRENCY_CODE = QPTLN.CURRENCY_CODE THEN 'Y' ELSE 'N' END) QPTLN_V
2698 ,COUNT(DISTINCT ARI.ROWID) OVER (PARTITION BY LNI.ROWID) ARI_COUNT
2699 ,MAX(LNI.ACCOUNTING_RULE_ID) OVER (PARTITION BY LNI.ROWID) ARI_ID -- Accounting Rule ID based on Id
2700 ,(CASE WHEN ARI.TYPE IN ('A','ACC_DUR','PP_DR_ALL','PP_DR_PP')
2701 THEN 'Y'
2702 ELSE 'N'
2703 END) ARI_ID_VAL_FLAG
2704 ,COUNT(DISTINCT ARN.ROWID) OVER (PARTITION BY LNI.ROWID) ARN_COUNT
2705 ,MAX(ARN.RULE_ID) OVER (PARTITION BY LNI.ROWID) ARN_ID -- Accounting Rule ID based on Name
2706 ,(CASE WHEN ARN.TYPE IN ('A','ACC_DUR','PP_DR_ALL','PP_DR_PP')
2707 THEN 'Y'
2708 ELSE 'N'
2709 END) ARN_ID_VAL_FLAG
2710 ,COUNT(DISTINCT IRI.ROWID) OVER (PARTITION BY LNI.ROWID) IRI_COUNT
2711 ,MAX(LNI.INVOICING_RULE_ID) OVER (PARTITION BY LNI.ROWID) IRI_ID -- Invoicing Rule ID based on Id
2712 ,COUNT(DISTINCT IRN.ROWID) OVER (PARTITION BY LNI.ROWID) IRN_COUNT
2713 ,MAX(IRN.RULE_ID) OVER (PARTITION BY LNI.ROWID) IRN_ID -- Invoicing Rule ID based on Name
2714 ,COUNT(DISTINCT MSI.ROWID) OVER (PARTITION BY LNI.ROWID) MSI_COUNT
2715 ,MAX(LNI.ITEM_ID) OVER (PARTITION BY LNI.ROWID) MSI_ID -- Item id based on id
2716 ,(CASE WHEN (LNI.LINE_TYPE = 'SERVICE'
2717 AND (MSI.VENDOR_WARRANTY_FLAG = 'Y' OR nvl(MSI.SERVICE_ITEM_FLAG, 'N') <> 'Y'))
2718 OR (LNI.LINE_TYPE = 'WARRANTY' AND MSI.VENDOR_WARRANTY_FLAG = 'N')
2719 OR (LNI.LINE_TYPE = 'USAGE' AND nvl(MSI.USAGE_ITEM_FLAG, 'N') <> 'Y')
2720 OR (LNI.LINE_TYPE = 'SUBSCRIPTION'
2721 AND (nvl(MSI.CONTRACT_ITEM_TYPE_CODE,'A') <> 'SUBSCRIPTION'
2722 OR nvl(MSI.INVOICE_ENABLED_FLAG, 'N') <> 'Y'
2723 OR (nvl(MSI.CUSTOMER_ORDER_ENABLED_FLAG, 'N') = 'N' AND nvl(MSI.INTERNAL_ORDER_ENABLED_FLAG, 'N') = 'N')))
2724 THEN 'N' ELSE 'Y' END) MSI_V
2725 ,MAX(MSI.COVERAGE_SCHEDULE_ID) OVER (PARTITION BY LNI.ROWID) MSI_REF_TEMPLATE_ID
2726 ,COUNT(DISTINCT MSN.ROWID) OVER (PARTITION BY LNI.ROWID) MSN_COUNT
2727 ,MAX(MSN.INVENTORY_ITEM_ID) OVER (PARTITION BY LNI.ROWID) MSN_ID -- Item id based on Name
2728 ,(CASE WHEN (LNI.LINE_TYPE = 'SERVICE'
2729 AND (MSN.VENDOR_WARRANTY_FLAG = 'Y' OR nvl(MSN.SERVICE_ITEM_FLAG, 'N') <> 'Y'))
2730 OR (LNI.LINE_TYPE = 'WARRANTY' AND MSN.VENDOR_WARRANTY_FLAG = 'N')
2731 OR (LNI.LINE_TYPE = 'USAGE' AND nvl(MSN.USAGE_ITEM_FLAG, 'N') <> 'Y')
2732 OR (LNI.LINE_TYPE = 'SUBSCRIPTION'
2733 AND (nvl(MSN.CONTRACT_ITEM_TYPE_CODE,'A') <> 'SUBSCRIPTION'
2734 OR nvl(MSN.INVOICE_ENABLED_FLAG, 'N') <> 'Y'
2735 OR (nvl(MSN.CUSTOMER_ORDER_ENABLED_FLAG, 'N') = 'N' AND nvl(MSN.INTERNAL_ORDER_ENABLED_FLAG, 'N') = 'N')))
2736 THEN 'N' ELSE 'Y' END) MSN_V
2737 ,MAX(MSN.COVERAGE_SCHEDULE_ID) OVER (PARTITION BY LNI.ROWID) MSN_REF_TEMPLATE_ID
2738 ,COUNT(DISTINCT BTSUI.ROWID) OVER (PARTITION BY LNI.ROWID) BTSUI_COUNT
2739 ,MAX(LNI.BILL_TO_SITE_USAGE_ID) OVER (PARTITION BY LNI.ROWID) BTSUI_ID -- Bill To Site Usage id based on id
2740 ,(CASE WHEN HST.OPERATING_UNIT_ID = BTSUI.ORG_ID THEN 'Y' ELSE 'N' END) BTSUI_V
2741 ,COUNT(DISTINCT STSUI.ROWID) OVER (PARTITION BY LNI.ROWID) STSUI_COUNT
2742 ,MAX(LNI.SHIP_TO_SITE_USAGE_ID) OVER (PARTITION BY LNI.ROWID) STSUI_ID -- Ship To Site Usage id based on id
2743 ,(CASE WHEN HST.OPERATING_UNIT_ID = STSUI.ORG_ID THEN 'Y' ELSE 'N' END) STSUI_V
2744 ,decode(trunc(LNI.FIRST_BILL_UPTO_DATE), NULL, LNI.START_DATE, trunc(LNI.FIRST_BILL_UPTO_DATE) + 1) BILL_START_DATE
2745 ,COUNT(DISTINCT BCI.ROW_ID) OVER (PARTITION BY LNI.ROWID) BCI_COUNT
2746 ,MAX(LNI.BILLING_CONTACT_ID) OVER (PARTITION BY LNI.ROWID) BCI_ID -- Billing contact id based on id
2747 ,COUNT(DISTINCT SCI.ROW_ID) OVER (PARTITION BY LNI.ROWID) SCI_COUNT
2748 ,MAX(LNI.SHIPPING_CONTACT_ID) OVER (PARTITION BY LNI.ROWID) SCI_ID -- Shipping contact id based on id
2749 ,COUNT(DISTINCT BTSUN.ROW_ID) OVER (PARTITION BY LNI.ROWID) BTSUN_COUNT
2750 ,MAX(BTSUN.SITE_USE_ID) OVER (PARTITION BY LNI.ROWID) BTSUN_ID -- Bill To Site Usage id based on Name
2751 ,COUNT(DISTINCT STSUN.ROW_ID) OVER (PARTITION BY LNI.ROWID) STSUN_COUNT
2752 ,MAX(STSUN.SITE_USE_ID) OVER (PARTITION BY LNI.ROWID) STSUN_ID -- Ship To Site Usage id based on Name
2753 /*,COUNT(DISTINCT BCN.ROW_ID) OVER (PARTITION BY LNI.ROWID) BCN_COUNT
2754 ,MAX(BCN.CONTACT_ID) OVER (PARTITION BY LNI.ROWID) BCN_ID -- Billing contact id based on Name
2755 ,COUNT(DISTINCT SCN.ROW_ID) OVER (PARTITION BY LNI.ROWID) SCN_COUNT
2756 ,MAX(SCN.CONTACT_ID) OVER (PARTITION BY LNI.ROWID) SCN_ID -- Shipping contact id based on Name */
2757 FROM OKS_LINES_INTERFACE LNI
2758 ,OKS_INT_HEADER_STG_TEMP HST
2759 ,QP_LIST_HEADERS_B QPI
2760 ,(SELECT QP.ROWID ROW_ID, QPTL.NAME, QP.CURRENCY_CODE, QP.LIST_HEADER_ID
2761 FROM QP_LIST_HEADERS_B QP, QP_LIST_HEADERS_TL QPTL
2762 WHERE QP.LIST_HEADER_ID = QPTL.LIST_HEADER_ID
2763 AND QPTL.LANGUAGE = USERENV('LANG')) QPTLN
2764 ,RA_RULES ARI
2765 ,RA_RULES ARN
2766 ,RA_RULES IRI
2767 ,RA_RULES IRN
2768 ,MTL_SYSTEM_ITEMS_B MSI
2769 ,MTL_SYSTEM_ITEMS_B_KFV MSN
2770 ,(SELECT CAR.ROWID ROW_ID, CAR.CUST_ACCOUNT_ROLE_ID CONTACT_ID, P.PARTY_NAME CONTACT_NAME, R.OBJECT_ID PARTY_ID
2771 FROM HZ_CUST_ACCOUNT_ROLES CAR,HZ_PARTIES P, HZ_RELATIONSHIPS R , HZ_ORG_CONTACTS OC
2772 WHERE CAR.ROLE_TYPE = 'CONTACT'
2773 AND R.PARTY_ID = CAR.PARTY_ID
2774 AND R.CONTENT_SOURCE_TYPE = 'USER_ENTERED'
2775 AND P.PARTY_ID = R.SUBJECT_ID
2776 AND OC.PARTY_RELATIONSHIP_ID = R.RELATIONSHIP_ID
2777 AND R.DIRECTIONAL_FLAG = 'F') BCI
2778
2779 ,(SELECT CAR.ROWID ROW_ID, CAR.CUST_ACCOUNT_ROLE_ID CONTACT_ID, P.PARTY_NAME CONTACT_NAME, R.OBJECT_ID PARTY_ID
2780 FROM HZ_CUST_ACCOUNT_ROLES CAR,HZ_PARTIES P, HZ_RELATIONSHIPS R , HZ_ORG_CONTACTS OC
2781 WHERE CAR.ROLE_TYPE = 'CONTACT'
2782 AND R.PARTY_ID = CAR.PARTY_ID
2783 AND R.CONTENT_SOURCE_TYPE = 'USER_ENTERED'
2784 AND P.PARTY_ID = R.SUBJECT_ID
2785 AND OC.PARTY_RELATIONSHIP_ID = R.RELATIONSHIP_ID
2786 AND R.DIRECTIONAL_FLAG = 'F') SCI
2787 /*,(SELECT CAR.ROWID ROW_ID, CAR.CUST_ACCOUNT_ROLE_ID CONTACT_ID, P.PARTY_NAME CONTACT_NAME, R.OBJECT_ID PARTY_ID
2788 FROM HZ_CUST_ACCOUNT_ROLES CAR,HZ_PARTIES P, HZ_RELATIONSHIPS R , HZ_ORG_CONTACTS OC
2789 WHERE CAR.ROLE_TYPE = 'CONTACT'
2790 AND R.PARTY_ID = CAR.PARTY_ID
2791 AND R.CONTENT_SOURCE_TYPE = 'USER_ENTERED'
2792 AND P.PARTY_ID = R.SUBJECT_ID
2793 AND OC.PARTY_RELATIONSHIP_ID = R.RELATIONSHIP_ID
2794 AND R.DIRECTIONAL_FLAG = 'F') BCN
2795 ,(SELECT CAR.ROWID ROW_ID, CAR.CUST_ACCOUNT_ROLE_ID CONTACT_ID, P.PARTY_NAME CONTACT_NAME, R.OBJECT_ID PARTY_ID
2796 FROM HZ_CUST_ACCOUNT_ROLES CAR,HZ_PARTIES P, HZ_RELATIONSHIPS R , HZ_ORG_CONTACTS OC
2797 WHERE CAR.ROLE_TYPE = 'CONTACT'
2798 AND R.PARTY_ID = CAR.PARTY_ID
2799 AND R.CONTENT_SOURCE_TYPE = 'USER_ENTERED'
2800 AND P.PARTY_ID = R.SUBJECT_ID
2801 AND OC.PARTY_RELATIONSHIP_ID = R.RELATIONSHIP_ID
2802 AND R.DIRECTIONAL_FLAG = 'F') SCN */
2803 ,HZ_CUST_SITE_USES_ALL BTSUI
2804 ,HZ_CUST_SITE_USES_ALL STSUI
2805 ,(SELECT CSU.ROWID ROW_ID, CSU.SITE_USE_ID, CSU.LOCATION, HT.HEADER_INTERFACE_ID
2806 FROM HZ_CUST_SITE_USES_ALL CSU, HZ_CUST_ACCT_SITES_ALL CAS, HZ_PARTY_SITES PS, OKS_INT_HEADER_STG_TEMP HT
2807 WHERE CSU.SITE_USE_CODE = 'BILL_TO'
2808 AND CSU.CUST_ACCT_SITE_ID = CAS.CUST_ACCT_SITE_ID
2809 AND PS.PARTY_SITE_ID = CAS.PARTY_SITE_ID
2810 AND PS.PARTY_ID = HT.CUSTOMER_PARTY_ID
2811 AND CSU.ORG_ID = HT.OPERATING_UNIT_ID) BTSUN
2812 ,(SELECT CSU.ROWID ROW_ID, CSU.SITE_USE_ID, CSU.LOCATION, HT.HEADER_INTERFACE_ID
2813 FROM HZ_CUST_SITE_USES_ALL CSU, HZ_CUST_ACCT_SITES_ALL CAS, HZ_PARTY_SITES PS, OKS_INT_HEADER_STG_TEMP HT
2814 WHERE CSU.SITE_USE_CODE = 'SHIP_TO'
2815 AND CSU.CUST_ACCT_SITE_ID = CAS.CUST_ACCT_SITE_ID
2816 AND PS.PARTY_SITE_ID = CAS.PARTY_SITE_ID
2817 AND PS.PARTY_ID = HT.CUSTOMER_PARTY_ID
2818 AND CSU.ORG_ID = HT.OPERATING_UNIT_ID) STSUN
2819 WHERE LNI.HEADER_INTERFACE_ID = HST.HEADER_INTERFACE_ID
2820 AND LNI.PRICE_LIST_ID = QPI.LIST_HEADER_ID (+)
2821 AND LNI.PRICE_LIST_NAME = QPTLN.NAME (+)
2822 --AND QPTLN.VERSION_NO (+) = 1
2823 AND LNI.ACCOUNTING_RULE_ID = ARI.RULE_ID (+)
2824 AND LNI.ACCOUNTING_RULE_NAME = ARN.NAME (+)
2825 AND LNI.INVOICING_RULE_ID = IRI.RULE_ID (+)
2826 AND IRI.TYPE(+) = 'I'
2827 AND LNI.INVOICING_RULE_NAME = IRN.NAME (+)
2828 AND IRN.TYPE(+) = 'I'
2829 AND LNI.ITEM_ID = MSI.INVENTORY_ITEM_ID (+)
2830 AND LNI.ITEM_ORGANIZATION_ID = MSI.ORGANIZATION_ID (+)
2831 AND LNI.ITEM_NAME = MSN.CONCATENATED_SEGMENTS (+)
2832 AND LNI.ITEM_ORGANIZATION_ID = MSN.ORGANIZATION_ID (+)
2833 AND LNI.BILL_TO_SITE_USAGE_ID = BTSUI.SITE_USE_ID (+)
2834 AND BTSUI.SITE_USE_CODE (+)= 'BILL_TO'
2835 AND LNI.SHIP_TO_SITE_USAGE_ID = STSUI.SITE_USE_ID (+)
2836 AND STSUI.SITE_USE_CODE (+)= 'SHIP_TO'
2837 AND LNI.BILLING_CONTACT_ID = BCI.CONTACT_ID (+)
2838 AND LNI.SHIPPING_CONTACT_ID = SCI.CONTACT_ID (+)
2839 /*AND LNI.SHIPPING_CONTACT_NAME = SCN.CONTACT_NAME (+)
2840 AND LNI.BILLING_CONTACT_NAME = BCN.CONTACT_NAME (+) */
2841 AND LNI.BILL_TO_SITE_USAGE_CODE = BTSUN.LOCATION (+)
2842 AND LNI.HEADER_INTERFACE_ID = BTSUN.HEADER_INTERFACE_ID (+)
2843 AND LNI.SHIP_TO_SITE_USAGE_CODE = STSUN.LOCATION (+)
2844 AND LNI.HEADER_INTERFACE_ID = STSUN.HEADER_INTERFACE_ID (+)) NAMEID_Q
2845 WHERE OLI.LINE_INTERFACE_ID = NAMEID_Q.LINE_INTERFACE_ID
2846 AND OLI.HEADER_INTERFACE_ID = HDR.HEADER_INTERFACE_ID
2847 AND OLI.PAYMENT_INSTRUCTION = FL.LOOKUP_CODE (+)
2848 AND FL.LOOKUP_TYPE (+) = 'OKS_PAYMENT_INST_TYPE'
2849 AND OLI.COMMITMENT_ID = CNI.CUSTOMER_TRX_ID (+)
2850 AND OLI.LINE_TYPE = LS.LTY_CODE (+)
2851 AND OLI.PAYMENT_METHOD_CODE = PMC.LOOKUP_CODE (+)
2852 AND PMC.LOOKUP_TYPE (+) = 'OKS_PAYMENT_METHODS'
2853 AND PMC.LOOKUP_CODE (+) = 'COM';
2854
2855 IF G_STMT_LOG THEN
2856
2857 SELECT count(1) INTO l_int_count FROM OKS_LINES_INTERFACE OLI
2858 WHERE EXISTS (SELECT 'X' FROM OKS_INT_HEADER_STG_TEMP
2859 WHERE HEADER_INTERFACE_ID = OLI.HEADER_INTERFACE_ID);
2860
2861 SELECT count(1) INTO l_stg_count FROM OKS_INT_LINE_STG_TEMP;
2862 /* to avoid gscc failure */
2863 IF G_STMT_LOG THEN
2864 fnd_log.string(fnd_log.level_statement,
2865 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
2866 'Number of records in interface table being processed = ' || l_int_count ||', '||
2867 'Number of records inserted into staging table = '|| l_stg_count);
2868 END IF;
2869 END IF;
2870
2871 l_stmt_num := 20;
2872
2873 INSERT ALL
2874 WHEN (LINE_NUMBER_V = 'N') THEN
2875 INTO OKS_INT_ERROR_STG_TEMP
2876 (CONCURRENT_REQUEST_ID
2877 ,HEADER_INTERFACE_ROWID
2878 ,INTERFACE_SOURCE_TABLE
2879 ,INTERFACE_ID
2880 ,ERROR_MSG
2881 ,MSG_TOKENS)
2882 VALUES (G_WORKER_REQ_ID
2883 ,HEADER_INTERFACE_ROWID
2884 ,'OKS_LINES_INTERFACE'
2885 ,LINE_INTERFACE_ID
2886 ,'OKS_IMP_LIN_INVALID_LINENUM'
2887 ,NULL)
2888 WHEN (HOI_COUNT = 0) THEN
2889 INTO OKS_INT_ERROR_STG_TEMP
2890 (CONCURRENT_REQUEST_ID
2891 ,HEADER_INTERFACE_ROWID
2892 ,INTERFACE_SOURCE_TABLE
2893 ,INTERFACE_ID
2894 ,ERROR_MSG
2895 ,MSG_TOKENS)
2896 VALUES (G_WORKER_REQ_ID
2897 ,HEADER_INTERFACE_ROWID
2898 ,'OKS_LINES_INTERFACE'
2899 ,LINE_INTERFACE_ID
2900 ,'OKS_IMP_LIN_INVALID_ITMORG'
2901 ,NULL)
2902 WHEN (COVTEM_V = 'N') THEN
2903 INTO OKS_INT_ERROR_STG_TEMP
2904 (CONCURRENT_REQUEST_ID
2905 ,HEADER_INTERFACE_ROWID
2906 ,INTERFACE_SOURCE_TABLE
2907 ,INTERFACE_ID
2908 ,ERROR_MSG
2909 ,MSG_TOKENS)
2910 VALUES (G_WORKER_REQ_ID
2911 ,HEADER_INTERFACE_ROWID
2912 ,'OKS_LINES_INTERFACE'
2913 ,LINE_INTERFACE_ID
2914 ,'OKS_IMP_LIN_INVALID_COVTEM'
2915 ,NULL)
2916 WHEN (STATUS_VALID = 'N') THEN
2917 INTO OKS_INT_ERROR_STG_TEMP
2918 (CONCURRENT_REQUEST_ID
2919 ,HEADER_INTERFACE_ROWID
2920 ,INTERFACE_SOURCE_TABLE
2921 ,INTERFACE_ID
2922 ,ERROR_MSG
2923 ,MSG_TOKENS)
2924 VALUES (G_WORKER_REQ_ID
2925 ,HEADER_INTERFACE_ROWID
2926 ,'OKS_LINES_INTERFACE'
2927 ,LINE_INTERFACE_ID
2928 ,'OKS_IMP_LIN_INVALID_STS'
2929 ,NULL)
2930 WHEN (STATUS_X_DATE_VALID = 'N') THEN
2931 INTO OKS_INT_ERROR_STG_TEMP
2932 (CONCURRENT_REQUEST_ID
2933 ,HEADER_INTERFACE_ROWID
2934 ,INTERFACE_SOURCE_TABLE
2935 ,INTERFACE_ID
2936 ,ERROR_MSG
2937 ,MSG_TOKENS)
2938 VALUES (G_WORKER_REQ_ID
2939 ,HEADER_INTERFACE_ROWID
2940 ,'OKS_LINES_INTERFACE'
2941 ,LINE_INTERFACE_ID
2942 ,'OKS_IMP_LIN_INVALID_STSXDATE'
2943 ,NULL)
2944 WHEN (STS_X_HDRSTS = 'N') THEN
2945 INTO OKS_INT_ERROR_STG_TEMP
2946 (CONCURRENT_REQUEST_ID
2947 ,HEADER_INTERFACE_ROWID
2948 ,INTERFACE_SOURCE_TABLE
2949 ,INTERFACE_ID
2950 ,ERROR_MSG
2951 ,MSG_TOKENS)
2952 VALUES (G_WORKER_REQ_ID
2953 ,HEADER_INTERFACE_ROWID
2954 ,'OKS_LINES_INTERFACE'
2955 ,LINE_INTERFACE_ID
2956 ,'OKS_IMP_LIN_INVALID_STSXHDR'
2957 ,NULL)
2958 WHEN (DATE_VALID = 'N') THEN
2959 INTO OKS_INT_ERROR_STG_TEMP
2960 (CONCURRENT_REQUEST_ID
2961 ,HEADER_INTERFACE_ROWID
2962 ,INTERFACE_SOURCE_TABLE
2963 ,INTERFACE_ID
2964 ,ERROR_MSG
2965 ,MSG_TOKENS)
2966 VALUES (G_WORKER_REQ_ID
2967 ,HEADER_INTERFACE_ROWID
2968 ,'OKS_LINES_INTERFACE'
2969 ,LINE_INTERFACE_ID
2970 ,'OKS_IMP_LIN_INVALID_DATE'
2971 ,NULL)
2972 WHEN (DATE_CANCELED_VALID = 'N') THEN
2973 INTO OKS_INT_ERROR_STG_TEMP
2974 (CONCURRENT_REQUEST_ID
2975 ,HEADER_INTERFACE_ROWID
2976 ,INTERFACE_SOURCE_TABLE
2977 ,INTERFACE_ID
2978 ,ERROR_MSG
2979 ,MSG_TOKENS)
2980 VALUES (G_WORKER_REQ_ID
2981 ,HEADER_INTERFACE_ROWID
2982 ,'OKS_LINES_INTERFACE'
2983 ,LINE_INTERFACE_ID
2984 ,'OKS_IMP_LIN_INVALID_DATECAN'
2985 ,NULL)
2986 WHEN (DATE_CANCELED_NULL_ERROR = 'Y') THEN
2987 INTO OKS_INT_ERROR_STG_TEMP
2988 (CONCURRENT_REQUEST_ID
2989 ,HEADER_INTERFACE_ROWID
2990 ,INTERFACE_SOURCE_TABLE
2991 ,INTERFACE_ID
2992 ,ERROR_MSG
2993 ,MSG_TOKENS)
2994 VALUES (G_WORKER_REQ_ID
2995 ,HEADER_INTERFACE_ROWID
2996 ,'OKS_LINES_INTERFACE'
2997 ,LINE_INTERFACE_ID
2998 ,'OKS_IMP_LIN_NULL_DATECAN'
2999 ,NULL)
3000 WHEN (CR_VALID = 'N') THEN
3001 INTO OKS_INT_ERROR_STG_TEMP
3002 (CONCURRENT_REQUEST_ID
3003 ,HEADER_INTERFACE_ROWID
3004 ,INTERFACE_SOURCE_TABLE
3005 ,INTERFACE_ID
3006 ,ERROR_MSG
3007 ,MSG_TOKENS)
3008 VALUES (G_WORKER_REQ_ID
3009 ,HEADER_INTERFACE_ROWID
3010 ,'OKS_LINES_INTERFACE'
3011 ,LINE_INTERFACE_ID
3012 ,'OKS_IMP_LIN_INVALID_CR'
3013 ,NULL)
3014 WHEN (CR_NULL_ERROR = 'Y') THEN
3015 INTO OKS_INT_ERROR_STG_TEMP
3016 (CONCURRENT_REQUEST_ID
3017 ,HEADER_INTERFACE_ROWID
3018 ,INTERFACE_SOURCE_TABLE
3019 ,INTERFACE_ID
3020 ,ERROR_MSG
3021 ,MSG_TOKENS)
3022 VALUES (G_WORKER_REQ_ID
3023 ,HEADER_INTERFACE_ROWID
3024 ,'OKS_LINES_INTERFACE'
3025 ,LINE_INTERFACE_ID
3026 ,'OKS_IMP_LIN_NULL_CR'
3027 ,NULL)
3028 WHEN (TEC_V = 'N') THEN
3029 INTO OKS_INT_ERROR_STG_TEMP
3030 (CONCURRENT_REQUEST_ID
3031 ,HEADER_INTERFACE_ROWID
3032 ,INTERFACE_SOURCE_TABLE
3033 ,INTERFACE_ID
3034 ,ERROR_MSG
3035 ,MSG_TOKENS)
3036 VALUES (G_WORKER_REQ_ID
3037 ,HEADER_INTERFACE_ROWID
3038 ,'OKS_LINES_INTERFACE'
3039 ,LINE_INTERFACE_ID
3040 ,'OKS_IMP_LIN_INVALID_TEC'
3041 ,NULL)
3042 WHEN (TUCEF_OU_V = 'N') THEN
3043 INTO OKS_INT_ERROR_STG_TEMP
3044 (CONCURRENT_REQUEST_ID
3045 ,HEADER_INTERFACE_ROWID
3046 ,INTERFACE_SOURCE_TABLE
3047 ,INTERFACE_ID
3048 ,ERROR_MSG
3049 ,MSG_TOKENS)
3050 VALUES (G_WORKER_REQ_ID
3051 ,HEADER_INTERFACE_ROWID
3052 ,'OKS_LINES_INTERFACE'
3053 ,LINE_INTERFACE_ID
3054 ,'OKS_IMP_LIN_INVALID_TUCEF_OU'
3055 ,NULL)
3056 WHEN (TEN_V = 'N') THEN
3057 INTO OKS_INT_ERROR_STG_TEMP
3058 (CONCURRENT_REQUEST_ID
3059 ,HEADER_INTERFACE_ROWID
3060 ,INTERFACE_SOURCE_TABLE
3061 ,INTERFACE_ID
3062 ,ERROR_MSG
3063 ,MSG_TOKENS)
3064 VALUES (G_WORKER_REQ_ID
3065 ,HEADER_INTERFACE_ROWID
3066 ,'OKS_LINES_INTERFACE'
3067 ,LINE_INTERFACE_ID
3068 ,'OKS_IMP_LIN_INVALID_TEN'
3069 ,NULL)
3070 WHEN (TEN_NULL_ERROR = 'Y') THEN
3071 INTO OKS_INT_ERROR_STG_TEMP
3072 (CONCURRENT_REQUEST_ID
3073 ,HEADER_INTERFACE_ROWID
3074 ,INTERFACE_SOURCE_TABLE
3075 ,INTERFACE_ID
3076 ,ERROR_MSG
3077 ,MSG_TOKENS)
3078 VALUES (G_WORKER_REQ_ID
3079 ,HEADER_INTERFACE_ROWID
3080 ,'OKS_LINES_INTERFACE'
3081 ,LINE_INTERFACE_ID
3082 ,'OKS_IMP_LIN_NULL_TEN'
3083 ,NULL)
3084 WHEN (ERC_NULL_ERROR = 'Y') THEN
3085 INTO OKS_INT_ERROR_STG_TEMP
3086 (CONCURRENT_REQUEST_ID
3087 ,HEADER_INTERFACE_ROWID
3088 ,INTERFACE_SOURCE_TABLE
3089 ,INTERFACE_ID
3090 ,ERROR_MSG
3091 ,MSG_TOKENS)
3092 VALUES (G_WORKER_REQ_ID
3093 ,HEADER_INTERFACE_ROWID
3094 ,'OKS_LINES_INTERFACE'
3095 ,LINE_INTERFACE_ID
3096 ,'OKS_IMP_LIN_NULL_ERC'
3097 ,NULL)
3098 WHEN (TCC_VALID = 'N') THEN
3099 INTO OKS_INT_ERROR_STG_TEMP
3100 (CONCURRENT_REQUEST_ID
3101 ,HEADER_INTERFACE_ROWID
3102 ,INTERFACE_SOURCE_TABLE
3103 ,INTERFACE_ID
3104 ,ERROR_MSG
3105 ,MSG_TOKENS)
3106 VALUES (G_WORKER_REQ_ID
3107 ,HEADER_INTERFACE_ROWID
3108 ,'OKS_LINES_INTERFACE'
3109 ,LINE_INTERFACE_ID
3110 ,'OKS_IMP_LIN_INVALID_TCC'
3111 ,NULL)
3112 WHEN (REN_TYPE_VALID = 'N') THEN
3113 INTO OKS_INT_ERROR_STG_TEMP
3114 (CONCURRENT_REQUEST_ID
3115 ,HEADER_INTERFACE_ROWID
3116 ,INTERFACE_SOURCE_TABLE
3117 ,INTERFACE_ID
3118 ,ERROR_MSG
3119 ,MSG_TOKENS)
3120 VALUES (G_WORKER_REQ_ID
3121 ,HEADER_INTERFACE_ROWID
3122 ,'OKS_LINES_INTERFACE'
3123 ,LINE_INTERFACE_ID
3124 ,'OKS_IMP_LIN_INVALID_RENTYPE'
3125 ,NULL)
3126 WHEN (PRINT_INVOICE_V = 'N') THEN
3127 INTO OKS_INT_ERROR_STG_TEMP
3128 (CONCURRENT_REQUEST_ID
3129 ,HEADER_INTERFACE_ROWID
3130 ,INTERFACE_SOURCE_TABLE
3131 ,INTERFACE_ID
3132 ,ERROR_MSG
3133 ,MSG_TOKENS)
3134 VALUES (G_WORKER_REQ_ID
3135 ,HEADER_INTERFACE_ROWID
3136 ,'OKS_LINES_INTERFACE'
3137 ,LINE_INTERFACE_ID
3138 ,'OKS_IMP_LIN_INVALID_PRNTINV'
3139 ,NULL)
3140 WHEN (POREQ_V = 'N') THEN
3141 INTO OKS_INT_ERROR_STG_TEMP
3142 (CONCURRENT_REQUEST_ID
3143 ,HEADER_INTERFACE_ROWID
3144 ,INTERFACE_SOURCE_TABLE
3145 ,INTERFACE_ID
3146 ,ERROR_MSG
3147 ,MSG_TOKENS)
3148 VALUES (G_WORKER_REQ_ID
3149 ,HEADER_INTERFACE_ROWID
3150 ,'OKS_LINES_INTERFACE'
3151 ,LINE_INTERFACE_ID
3152 ,'OKS_IMP_LIN_INVALID_POREQ'
3153 ,NULL)
3154 WHEN (POREQ_NULL_ERROR = 'Y') THEN
3155 INTO OKS_INT_ERROR_STG_TEMP
3156 (CONCURRENT_REQUEST_ID
3157 ,HEADER_INTERFACE_ROWID
3158 ,INTERFACE_SOURCE_TABLE
3159 ,INTERFACE_ID
3160 ,ERROR_MSG
3161 ,MSG_TOKENS)
3162 VALUES (G_WORKER_REQ_ID
3163 ,HEADER_INTERFACE_ROWID
3164 ,'OKS_LINES_INTERFACE'
3165 ,LINE_INTERFACE_ID
3166 ,'OKS_IMP_LIN_NULL_POREQ'
3167 ,NULL)
3168 WHEN (TANGIBLE_SUB = 'Y') THEN
3169 INTO OKS_INT_ERROR_STG_TEMP
3170 (CONCURRENT_REQUEST_ID
3171 ,HEADER_INTERFACE_ROWID
3172 ,INTERFACE_SOURCE_TABLE
3173 ,INTERFACE_ID
3174 ,ERROR_MSG
3175 ,MSG_TOKENS)
3176 VALUES (G_WORKER_REQ_ID
3177 ,HEADER_INTERFACE_ROWID
3178 ,'OKS_LINES_INTERFACE'
3179 ,LINE_INTERFACE_ID
3180 ,'OKS_IMP_LIN_TAN_SUB'
3181 ,NULL)
3182 SELECT LST.LINE_INTERFACE_ID LINE_INTERFACE_ID
3183 ,HDR.HEADER_INTERFACE_ROWID HEADER_INTERFACE_ROWID
3184 ,(CASE WHEN LST.LINE_NUMBER IS NULL
3185 OR EXISTS (SELECT 1 FROM OKS_INT_LINE_STG_TEMP LUNIQ
3186 WHERE LST.LINE_INTERFACE_ID <> LUNIQ.LINE_INTERFACE_ID
3187 AND LST.LINE_NUMBER = LUNIQ.LINE_NUMBER
3188 AND LST.HEADER_INTERFACE_ID = LUNIQ.HEADER_INTERFACE_ID)
3189 OR LST.LINE_NUMBER <= 0
3190 OR floor(LST.LINE_NUMBER) <> LST.LINE_NUMBER
3191 THEN 'N' ELSE 'Y' END) LINE_NUMBER_V
3192 ,COUNT(DISTINCT HOI.ROWID) OVER (PARTITION BY LST.ROWID) HOI_COUNT
3193 ,(CASE WHEN LST.LINE_TYPE <> 'USAGE' AND COUNT(DISTINCT COVTEM.ID) OVER (PARTITION BY LST.ROWID) = 0
3194 THEN 'N' ELSE 'Y' END) COVTEM_V
3195 ,(CASE WHEN STS.STE_CODE IN ('ENTERED', 'ACTIVE', 'CANCELLED', 'SIGNED', 'EXPIRED')
3196 THEN 'Y' ELSE 'N' END) STATUS_VALID
3197 ,(CASE WHEN (LST.START_DATE > SYSDATE AND STS.STE_CODE IN ('ACTIVE', 'EXPIRED'))
3198 OR (LST.END_DATE <= SYSDATE AND STS.STE_CODE = 'SIGNED')
3199 OR (SYSDATE between LST.START_DATE and LST.END_DATE AND STS.STE_CODE IN ('SIGNED', 'EXPIRED'))
3200 THEN 'N' ELSE 'Y' END) STATUS_X_DATE_VALID
3201 ,(CASE WHEN (HDRSTS.STE_CODE = 'ENTERED' AND STS.STE_CODE IN ('ENTERED', 'CANCELLED'))
3202 OR (HDRSTS.STE_CODE = 'ACTIVE' AND STS.STE_CODE IN ('ACTIVE', 'SIGNED', 'EXPIRED'))
3203 OR (HDRSTS.STE_CODE = 'CANCELLED' AND STS.STE_CODE IN ('CANCELLED'))
3204 OR (HDRSTS.STE_CODE = 'SIGNED' AND STS.STE_CODE IN ('SIGNED'))
3205 OR (HDRSTS.STE_CODE = 'EXPIRED' AND STS.STE_CODE IN ('EXPIRED'))
3206 THEN 'Y' ELSE 'N' END) STS_X_HDRSTS
3207 ,(CASE WHEN LST.START_DATE IS NULL OR LST.END_DATE IS NULL OR LST.START_DATE > LST.END_DATE
3208 OR LST.START_DATE < HDR.START_DATE OR LST.END_DATE > HDR.END_DATE
3209 THEN 'N' ELSE 'Y' END) DATE_VALID
3210 ,(CASE WHEN (LST.CANCELLATION_DATE IS NULL AND STS.STE_CODE = 'CANCELLED')
3211 THEN 'N' ELSE 'Y' END) DATE_CANCELED_VALID
3212 ,(CASE WHEN (LST.CANCELLATION_DATE IS NOT NULL AND STS.STE_CODE <> 'CANCELLED')
3213 THEN 'Y' ELSE 'N' END) DATE_CANCELED_NULL_ERROR
3214 ,(CASE WHEN (STS.STE_CODE = 'CANCELLED'
3215 AND ( COUNT(DISTINCT CR1.ROWID) OVER (PARTITION BY LST.ROWID) <> 1
3216 AND COUNT(DISTINCT CR2.ROWID) OVER (PARTITION BY LST.ROWID) <> 1)
3217 )
3218 THEN 'N' ELSE 'Y' END) CR_VALID
3219 ,(CASE WHEN (STS.STE_CODE <> 'CANCELLED' AND LST.CANCELLATION_REASON IS NOT NULL)
3220 THEN 'Y' ELSE 'N' END) CR_NULL_ERROR
3221 ,(CASE WHEN LST.TAX_EXEMPTION_CONTROL IS NOT NULL
3222 AND COUNT(DISTINCT TEC.ROWID) OVER (PARTITION BY LST.ROWID) <> 1
3223 THEN 'N' ELSE 'Y' END) TEC_V
3224 ,(CASE WHEN LST.TAX_EXEMPTION_CONTROL = 'E'
3225 AND NOT EXISTS (SELECT 'X' FROM ZX_PRODUCT_OPTIONS
3226 WHERE ORG_ID = HDR.OPERATING_UNIT_ID
3227 AND TAX_METHOD_CODE <> 'LTE'
3228 AND APPLICATION_ID = 222
3229 AND TAX_USE_CUSTOMER_EXEMPT_FLAG = 'Y')
3230 THEN 'N' ELSE 'Y' END) TUCEF_OU_V
3231 ,(CASE WHEN LST.TAX_EXEMPTION_CONTROL = 'E'
3232 AND COUNT(DISTINCT TEN.ROW_ID) OVER (PARTITION BY LST.ROWID) = 0
3233 THEN 'N' ELSE 'Y' END) TEN_V
3234 ,(CASE WHEN (LST.TAX_EXEMPTION_CONTROL <> 'E' OR LST.TAX_EXEMPTION_CONTROL IS NULL)
3235 AND LST.TAX_EXEMPTION_NUMBER IS NOT NULL
3236 THEN 'Y' ELSE 'N' END) TEN_NULL_ERROR
3237 ,(CASE WHEN (LST.TAX_EXEMPTION_CONTROL <> 'E' OR LST.TAX_EXEMPTION_CONTROL IS NULL)
3238 AND LST.EXEMPT_REASON_CODE IS NOT NULL
3239 THEN 'Y' ELSE 'N' END) ERC_NULL_ERROR
3240 ,(CASE WHEN LST.TAX_CLASSIFICATION_CODE IS NOT NULL
3241 AND NOT EXISTS ( SELECT 'X' FROM ZX_OUTPUT_CLASSIFICATIONS_V
3242 WHERE LST.TAX_CLASSIFICATION_CODE = LOOKUP_CODE
3243 AND ORG_ID IN (HDR.OPERATING_UNIT_ID, -99))
3244 THEN 'N' ELSE 'Y' END) TCC_VALID
3245 ,(CASE WHEN LST.RENEWAL_TYPE_CODE IS NOT NULL
3246 AND COUNT(DISTINCT RTC.ROWID) OVER (PARTITION BY LST.ROWID) = 0
3247 THEN 'N' ELSE 'Y' END) REN_TYPE_VALID
3248 ,(CASE WHEN LST.PRINT_INVOICE IS NOT NULL AND LST.PRINT_INVOICE NOT IN ('Y', 'N')
3249 THEN 'N' ELSE 'Y' END) PRINT_INVOICE_V
3250 ,(CASE WHEN LST.PAYMENT_INSTRUCTION = 'PON'
3251 AND (LST.PO_REQUIRED NOT IN ('Y', 'N') OR LST.PO_REQUIRED IS NULL) THEN 'N' ELSE 'Y' END) POREQ_V
3252 ,(CASE WHEN LST.PO_REQUIRED IS NOT NULL AND (LST.PAYMENT_INSTRUCTION <> 'PON' OR LST.PAYMENT_INSTRUCTION IS NULL)
3253 THEN 'Y' ELSE 'N' END) POREQ_NULL_ERROR
3254 ,(CASE WHEN LST.LINE_TYPE = 'SUBSCRIPTION'
3255 AND COUNT(DISTINCT TANGIBLE.ROWID) OVER (PARTITION BY LST.ROWID) = 1
3256 THEN 'Y' ELSE 'N' END) TANGIBLE_SUB
3257 FROM OKS_INT_LINE_STG_TEMP LST
3258 ,OKS_INT_HEADER_STG_TEMP HDR
3259 ,HR_ORGANIZATION_INFORMATION HOI
3260 ,OKS_COVERAGE_TEMPLTS_V COVTEM
3261 ,OKC_STATUSES_B STS
3262 ,OKC_STATUSES_B HDRSTS
3263 ,FND_LOOKUPS CR1
3264 ,FND_LOOKUPS CR2
3265 ,FND_LOOKUPS TEC
3266 ,(SELECT EX.ROWID ROW_ID, EX.EXEMPT_CERTIFICATE_NUMBER, EX.EXEMPT_REASON_CODE
3267 FROM ZX_EXEMPTIONS EX, AR_LOOKUPS ERC
3268 WHERE EX.EXEMPT_REASON_CODE = ERC.LOOKUP_CODE (+)
3269 AND ERC.LOOKUP_TYPE(+) = 'TAX_REASON') TEN
3270 ,FND_LOOKUPS RTC
3271 ,OKS_SUBSCR_HEADER_B TANGIBLE
3272 WHERE LST.HEADER_INTERFACE_ID = HDR.HEADER_INTERFACE_ID
3273 AND LST.ITEM_ORGANIZATION_ID = HOI.ORGANIZATION_ID (+)
3274 AND HOI.ORG_INFORMATION_CONTEXT (+) = 'CLASS'
3275 AND HOI.ORG_INFORMATION1 (+) = 'INV'
3276 AND LST.REFERENCE_TEMPLATE_ID = COVTEM.ID (+)
3277 AND DECODE(LST.LINE_TYPE,'EXT_WARRANTY' , 'SERVICE',LST.LINE_TYPE) = COVTEM.ITEM_TYPE (+)
3278 AND LST.STATUS_CODE = STS.CODE (+)
3279 AND HDR.STATUS_CODE = HDRSTS.CODE (+)
3280 AND LST.CANCELLATION_REASON = CR1.LOOKUP_CODE (+)
3281 AND CR1.LOOKUP_TYPE (+) = 'OKS_CANCEL_REASON'
3282 AND LST.CANCELLATION_REASON = CR2.LOOKUP_CODE (+)
3283 AND CR2.LOOKUP_TYPE (+) = 'OKC_STS_CHG_REASON'
3284 AND LST.TAX_EXEMPTION_CONTROL = TEC.LOOKUP_CODE (+)
3285 AND TEC.LOOKUP_TYPE (+) = 'ZX_EXEMPTION_CONTROL'
3286 AND LST.TAX_EXEMPTION_NUMBER = TEN.EXEMPT_CERTIFICATE_NUMBER (+)
3287 AND LST.EXEMPT_REASON_CODE = TEN.EXEMPT_REASON_CODE (+)
3288 AND LST.RENEWAL_TYPE_CODE = RTC.LOOKUP_CODE (+)
3289 AND RTC.LOOKUP_TYPE (+) = 'OKC_LINE_RENEWAL_TYPE'
3290 AND LST.REFERENCE_TEMPLATE_ID = TANGIBLE.ID (+)
3291 AND TANGIBLE.FULFILLMENT_CHANNEL (+) = 'OM';
3292
3293 l_stmt_num := 30;
3294
3295 INSERT ALL
3296 WHEN (BILL_INT_DUR_V = 'N') THEN
3297 INTO OKS_INT_ERROR_STG_TEMP
3298 (CONCURRENT_REQUEST_ID
3299 ,HEADER_INTERFACE_ROWID
3300 ,INTERFACE_SOURCE_TABLE
3301 ,INTERFACE_ID
3302 ,ERROR_MSG
3303 ,MSG_TOKENS)
3304 VALUES (G_WORKER_REQ_ID
3305 ,HEADER_INTERFACE_ROWID
3306 ,'OKS_LINES_INTERFACE'
3307 ,LINE_INTERFACE_ID
3308 ,'OKS_IMP_LIN_INVALID_BID'
3309 ,NULL)
3310 WHEN (REC_BILL_OCC_V = 'N') THEN
3311 INTO OKS_INT_ERROR_STG_TEMP
3312 (CONCURRENT_REQUEST_ID
3313 ,HEADER_INTERFACE_ROWID
3314 ,INTERFACE_SOURCE_TABLE
3315 ,INTERFACE_ID
3316 ,ERROR_MSG
3317 ,MSG_TOKENS)
3318 VALUES (G_WORKER_REQ_ID
3319 ,HEADER_INTERFACE_ROWID
3320 ,'OKS_LINES_INTERFACE'
3321 ,LINE_INTERFACE_ID
3322 ,'OKS_IMP_LIN_INVALID_RBO'
3323 ,NULL)
3324 WHEN (BILL_INT_DUR_NULL_ERROR = 'Y') THEN
3325 INTO OKS_INT_ERROR_STG_TEMP
3326 (CONCURRENT_REQUEST_ID
3327 ,HEADER_INTERFACE_ROWID
3328 ,INTERFACE_SOURCE_TABLE
3329 ,INTERFACE_ID
3330 ,ERROR_MSG
3331 ,MSG_TOKENS)
3332 VALUES (G_WORKER_REQ_ID
3333 ,HEADER_INTERFACE_ROWID
3334 ,'OKS_LINES_INTERFACE'
3335 ,LINE_INTERFACE_ID
3336 ,'OKS_IMP_LIN_NULL_BID'
3337 ,NULL)
3338 WHEN (BILL_INT_PERIOD_V = 'N') THEN
3339 INTO OKS_INT_ERROR_STG_TEMP
3340 (CONCURRENT_REQUEST_ID
3341 ,HEADER_INTERFACE_ROWID
3342 ,INTERFACE_SOURCE_TABLE
3343 ,INTERFACE_ID
3344 ,ERROR_MSG
3345 ,MSG_TOKENS)
3346 VALUES (G_WORKER_REQ_ID
3347 ,HEADER_INTERFACE_ROWID
3348 ,'OKS_LINES_INTERFACE'
3349 ,LINE_INTERFACE_ID
3350 ,'OKS_IMP_LIN_INVALID_BIP'
3351 ,NULL)
3352 WHEN (FIRST_BILL_V = 'N') THEN
3353 INTO OKS_INT_ERROR_STG_TEMP
3354 (CONCURRENT_REQUEST_ID
3355 ,HEADER_INTERFACE_ROWID
3356 ,INTERFACE_SOURCE_TABLE
3357 ,INTERFACE_ID
3358 ,ERROR_MSG
3359 ,MSG_TOKENS)
3360 VALUES (G_WORKER_REQ_ID
3361 ,HEADER_INTERFACE_ROWID
3362 ,'OKS_LINES_INTERFACE'
3363 ,LINE_INTERFACE_ID
3364 ,'OKS_IMP_LIN_INVALID_FBILL'
3365 ,NULL)
3366 WHEN (SUBQTY_V = 'N') THEN
3367 INTO OKS_INT_ERROR_STG_TEMP
3368 (CONCURRENT_REQUEST_ID
3369 ,HEADER_INTERFACE_ROWID
3370 ,INTERFACE_SOURCE_TABLE
3371 ,INTERFACE_ID
3372 ,ERROR_MSG
3373 ,MSG_TOKENS)
3374 VALUES (G_WORKER_REQ_ID
3375 ,HEADER_INTERFACE_ROWID
3376 ,'OKS_LINES_INTERFACE'
3377 ,LINE_INTERFACE_ID
3378 ,'OKS_IMP_LIN_INVALID_SQTY'
3379 ,NULL)
3380 WHEN (SUOM_VALID = 'N') THEN
3381 INTO OKS_INT_ERROR_STG_TEMP
3382 (CONCURRENT_REQUEST_ID
3383 ,HEADER_INTERFACE_ROWID
3384 ,INTERFACE_SOURCE_TABLE
3385 ,INTERFACE_ID
3386 ,ERROR_MSG
3387 ,MSG_TOKENS)
3388 VALUES (G_WORKER_REQ_ID
3389 ,HEADER_INTERFACE_ROWID
3390 ,'OKS_LINES_INTERFACE'
3391 ,LINE_INTERFACE_ID
3392 ,'OKS_IMP_LIN_INVALID_SUOM'
3393 ,NULL)
3394 WHEN (SUB_NULL_ERROR = 'Y') THEN
3395 INTO OKS_INT_ERROR_STG_TEMP
3396 (CONCURRENT_REQUEST_ID
3397 ,HEADER_INTERFACE_ROWID
3398 ,INTERFACE_SOURCE_TABLE
3399 ,INTERFACE_ID
3400 ,ERROR_MSG
3401 ,MSG_TOKENS)
3402 VALUES (G_WORKER_REQ_ID
3403 ,HEADER_INTERFACE_ROWID
3404 ,'OKS_LINES_INTERFACE'
3405 ,LINE_INTERFACE_ID
3406 ,'OKS_IMP_LIN_NULL_SUB'
3407 ,NULL)
3408 WHEN (PRICE_UOM_V = 'N') THEN
3409 INTO OKS_INT_ERROR_STG_TEMP
3410 (CONCURRENT_REQUEST_ID
3411 ,HEADER_INTERFACE_ROWID
3412 ,INTERFACE_SOURCE_TABLE
3413 ,INTERFACE_ID
3414 ,ERROR_MSG
3415 ,MSG_TOKENS)
3416 VALUES (G_WORKER_REQ_ID
3417 ,HEADER_INTERFACE_ROWID
3418 ,'OKS_LINES_INTERFACE'
3419 ,LINE_INTERFACE_ID
3420 ,'OKS_IMP_LIN_INVALID_PUOM'
3421 ,NULL)
3422 WHEN (USG_TYPE_V = 'N') THEN
3423 INTO OKS_INT_ERROR_STG_TEMP
3424 (CONCURRENT_REQUEST_ID
3425 ,HEADER_INTERFACE_ROWID
3426 ,INTERFACE_SOURCE_TABLE
3427 ,INTERFACE_ID
3428 ,ERROR_MSG
3429 ,MSG_TOKENS)
3430 VALUES (G_WORKER_REQ_ID
3431 ,HEADER_INTERFACE_ROWID
3432 ,'OKS_LINES_INTERFACE'
3433 ,LINE_INTERFACE_ID
3434 ,'OKS_IMP_LIN_INVALID_USGTYPE'
3435 ,NULL)
3436 WHEN (USG_TYPE_NULL_ERROR = 'Y') THEN
3437 INTO OKS_INT_ERROR_STG_TEMP
3438 (CONCURRENT_REQUEST_ID
3439 ,HEADER_INTERFACE_ROWID
3440 ,INTERFACE_SOURCE_TABLE
3441 ,INTERFACE_ID
3442 ,ERROR_MSG
3443 ,MSG_TOKENS)
3444 VALUES (G_WORKER_REQ_ID
3445 ,HEADER_INTERFACE_ROWID
3446 ,'OKS_LINES_INTERFACE'
3447 ,LINE_INTERFACE_ID
3448 ,'OKS_IMP_LIN_NULL_USGTYPE'
3449 ,NULL)
3450 WHEN (USG_PER_V = 'N') THEN
3451 INTO OKS_INT_ERROR_STG_TEMP
3452 (CONCURRENT_REQUEST_ID
3453 ,HEADER_INTERFACE_ROWID
3454 ,INTERFACE_SOURCE_TABLE
3455 ,INTERFACE_ID
3456 ,ERROR_MSG
3457 ,MSG_TOKENS)
3458 VALUES (G_WORKER_REQ_ID
3459 ,HEADER_INTERFACE_ROWID
3460 ,'OKS_LINES_INTERFACE'
3461 ,LINE_INTERFACE_ID
3462 ,'OKS_IMP_LIN_INVALID_USGPER'
3463 ,NULL)
3464 WHEN (USG_PER_NULL_ERROR = 'Y') THEN
3465 INTO OKS_INT_ERROR_STG_TEMP
3466 (CONCURRENT_REQUEST_ID
3467 ,HEADER_INTERFACE_ROWID
3468 ,INTERFACE_SOURCE_TABLE
3469 ,INTERFACE_ID
3470 ,ERROR_MSG
3471 ,MSG_TOKENS)
3472 VALUES (G_WORKER_REQ_ID
3473 ,HEADER_INTERFACE_ROWID
3474 ,'OKS_LINES_INTERFACE'
3475 ,LINE_INTERFACE_ID
3476 ,'OKS_IMP_LIN_NULL_USGPER'
3477 ,NULL)
3478 WHEN (AVG_INT_V = 'N') THEN
3479 INTO OKS_INT_ERROR_STG_TEMP
3480 (CONCURRENT_REQUEST_ID
3481 ,HEADER_INTERFACE_ROWID
3482 ,INTERFACE_SOURCE_TABLE
3483 ,INTERFACE_ID
3484 ,ERROR_MSG
3485 ,MSG_TOKENS)
3486 VALUES (G_WORKER_REQ_ID
3487 ,HEADER_INTERFACE_ROWID
3488 ,'OKS_LINES_INTERFACE'
3489 ,LINE_INTERFACE_ID
3490 ,'OKS_IMP_LIN_INVALID_AVGINT'
3491 ,NULL)
3492 WHEN (AVG_INT_NULL_ERROR = 'Y') THEN
3493 INTO OKS_INT_ERROR_STG_TEMP
3494 (CONCURRENT_REQUEST_ID
3495 ,HEADER_INTERFACE_ROWID
3496 ,INTERFACE_SOURCE_TABLE
3497 ,INTERFACE_ID
3498 ,ERROR_MSG
3499 ,MSG_TOKENS)
3500 VALUES (G_WORKER_REQ_ID
3501 ,HEADER_INTERFACE_ROWID
3502 ,'OKS_LINES_INTERFACE'
3503 ,LINE_INTERFACE_ID
3504 ,'OKS_IMP_LIN_NULL_AVGINT'
3505 ,NULL)
3506 WHEN (STM_INT_V = 'N') THEN
3507 INTO OKS_INT_ERROR_STG_TEMP
3508 (CONCURRENT_REQUEST_ID
3509 ,HEADER_INTERFACE_ROWID
3510 ,INTERFACE_SOURCE_TABLE
3511 ,INTERFACE_ID
3512 ,ERROR_MSG
3513 ,MSG_TOKENS)
3514 VALUES (G_WORKER_REQ_ID
3515 ,HEADER_INTERFACE_ROWID
3516 ,'OKS_LINES_INTERFACE'
3517 ,LINE_INTERFACE_ID
3518 ,'OKS_IMP_LIN_INVALID_STMINT'
3519 ,NULL)
3520 WHEN (STM_INT_NULL_ERROR = 'Y') THEN
3521 INTO OKS_INT_ERROR_STG_TEMP
3522 (CONCURRENT_REQUEST_ID
3523 ,HEADER_INTERFACE_ROWID
3524 ,INTERFACE_SOURCE_TABLE
3525 ,INTERFACE_ID
3526 ,ERROR_MSG
3527 ,MSG_TOKENS)
3528 VALUES (G_WORKER_REQ_ID
3529 ,HEADER_INTERFACE_ROWID
3530 ,'OKS_LINES_INTERFACE'
3531 ,LINE_INTERFACE_ID
3532 ,'OKS_IMP_LIN_NULL_STMINT'
3533 ,NULL)
3534 WHEN (UTM_V = 'N') THEN
3535 INTO OKS_INT_ERROR_STG_TEMP
3536 (CONCURRENT_REQUEST_ID
3537 ,HEADER_INTERFACE_ROWID
3538 ,INTERFACE_SOURCE_TABLE
3539 ,INTERFACE_ID
3540 ,ERROR_MSG
3541 ,MSG_TOKENS)
3542 VALUES (G_WORKER_REQ_ID
3543 ,HEADER_INTERFACE_ROWID
3544 ,'OKS_LINES_INTERFACE'
3545 ,LINE_INTERFACE_ID
3546 ,'OKS_IMP_LIN_INVALID_UTM'
3547 ,NULL)
3548 WHEN (UTM_NULL_ERROR = 'Y') THEN
3549 INTO OKS_INT_ERROR_STG_TEMP
3550 (CONCURRENT_REQUEST_ID
3551 ,HEADER_INTERFACE_ROWID
3552 ,INTERFACE_SOURCE_TABLE
3553 ,INTERFACE_ID
3554 ,ERROR_MSG
3555 ,MSG_TOKENS)
3556 VALUES (G_WORKER_REQ_ID
3557 ,HEADER_INTERFACE_ROWID
3558 ,'OKS_LINES_INTERFACE'
3559 ,LINE_INTERFACE_ID
3560 ,'OKS_IMP_LIN_NULL_UTM'
3561 ,NULL)
3562 WHEN (FIRST_BILLED_AMT_V = 'N') THEN
3563 INTO OKS_INT_ERROR_STG_TEMP
3564 (CONCURRENT_REQUEST_ID
3565 ,HEADER_INTERFACE_ROWID
3566 ,INTERFACE_SOURCE_TABLE
3567 ,INTERFACE_ID
3568 ,ERROR_MSG
3569 ,MSG_TOKENS)
3570 VALUES (G_WORKER_REQ_ID
3571 ,HEADER_INTERFACE_ROWID
3572 ,'OKS_LINES_INTERFACE'
3573 ,LINE_INTERFACE_ID
3574 ,'OKS_IMP_LIN_INVALID_FBA'
3575 ,NULL)
3576 WHEN (FIRST_BILLED_DATE_V = 'N') THEN
3577 INTO OKS_INT_ERROR_STG_TEMP
3578 (CONCURRENT_REQUEST_ID
3579 ,HEADER_INTERFACE_ROWID
3580 ,INTERFACE_SOURCE_TABLE
3581 ,INTERFACE_ID
3582 ,ERROR_MSG
3583 ,MSG_TOKENS)
3584 VALUES (G_WORKER_REQ_ID
3585 ,HEADER_INTERFACE_ROWID
3586 ,'OKS_LINES_INTERFACE'
3587 ,LINE_INTERFACE_ID
3588 ,'OKS_IMP_LIN_INVALID_FBD'
3589 ,NULL)
3590 WHEN (LAST_BILLED_AMT_V = 'N') THEN
3591 INTO OKS_INT_ERROR_STG_TEMP
3592 (CONCURRENT_REQUEST_ID
3593 ,HEADER_INTERFACE_ROWID
3594 ,INTERFACE_SOURCE_TABLE
3595 ,INTERFACE_ID
3596 ,ERROR_MSG
3597 ,MSG_TOKENS)
3598 VALUES (G_WORKER_REQ_ID
3599 ,HEADER_INTERFACE_ROWID
3600 ,'OKS_LINES_INTERFACE'
3601 ,LINE_INTERFACE_ID
3602 ,'OKS_IMP_LIN_INVALID_LBA'
3603 ,NULL)
3604 WHEN (LAST_BILL_DATE_V = 'N') THEN
3605 INTO OKS_INT_ERROR_STG_TEMP
3606 (CONCURRENT_REQUEST_ID
3607 ,HEADER_INTERFACE_ROWID
3608 ,INTERFACE_SOURCE_TABLE
3609 ,INTERFACE_ID
3610 ,ERROR_MSG
3611 ,MSG_TOKENS)
3612 VALUES (G_WORKER_REQ_ID
3613 ,HEADER_INTERFACE_ROWID
3614 ,'OKS_LINES_INTERFACE'
3615 ,LINE_INTERFACE_ID
3616 ,'OKS_IMP_LIN_INVALID_LBD'
3617 ,NULL)
3618 SELECT LST.LINE_INTERFACE_ID LINE_INTERFACE_ID
3619 ,HDR.HEADER_INTERFACE_ROWID HEADER_INTERFACE_ROWID
3620 ,(CASE WHEN (LST.LINE_TYPE <> 'WARRANTY' AND LST.BILLING_INTERVAL_DURATION IS NULL)
3621 OR LST.BILLING_INTERVAL_DURATION <= 0
3622 OR floor(LST.BILLING_INTERVAL_DURATION) <> LST.BILLING_INTERVAL_DURATION
3623 THEN 'N' ELSE 'Y' END) BILL_INT_DUR_V
3624 ,(CASE WHEN (LST.LINE_TYPE <> 'WARRANTY' AND LST.RECUR_BILL_OCCURANCES IS NULL)
3625 OR LST.RECUR_BILL_OCCURANCES <= 0
3626 OR floor(LST.RECUR_BILL_OCCURANCES) <> LST.RECUR_BILL_OCCURANCES
3627 THEN 'N' ELSE 'Y' END) REC_BILL_OCC_V
3628 ,(CASE WHEN LST.LINE_TYPE <> 'WARRANTY'
3629 AND (COUNT(DISTINCT BIP.ROWID) OVER (PARTITION BY LST.ROWID) <> 1
3630 OR LST.BILLING_INTERVAL_PERIOD NOT IN ('DAY','MTH', 'WK', 'YR', 'QRT') )
3631 THEN 'N' ELSE 'Y' END) BILL_INT_PERIOD_V
3632 ,(CASE WHEN LST.LINE_TYPE = 'WARRANTY'
3633 AND (LST.RECUR_BILL_OCCURANCES IS NOT NULL
3634 AND (LST.BILLING_INTERVAL_DURATION IS NULL OR LST.BILLING_INTERVAL_PERIOD IS NULL))
3635 THEN 'Y' ELSE 'N' END) BILL_INT_DUR_NULL_ERROR
3636 ,(CASE WHEN LST.FIRST_BILL_UPTO_DATE IS NOT NULL
3637 AND (LST.FIRST_BILL_UPTO_DATE < LST.START_DATE OR LST.FIRST_BILL_UPTO_DATE > LST.END_DATE)
3638 THEN 'N' ELSE 'Y' END) FIRST_BILL_V
3639 ,(CASE WHEN LST.LINE_TYPE = 'SUBSCRIPTION'
3640 AND (LST.SUBSCRIPTION_QUANTITY <= 0 OR LST.SUBSCRIPTION_QUANTITY IS NULL
3641 OR floor(LST.SUBSCRIPTION_QUANTITY) <> LST.SUBSCRIPTION_QUANTITY)
3642 THEN 'N' ELSE 'Y' END) SUBQTY_V
3643 ,(CASE WHEN LST.LINE_TYPE = 'SUBSCRIPTION' AND
3644 COUNT(DISTINCT SUOM.ROWID) OVER (PARTITION BY LST.ROWID) <> 1
3645 THEN 'N' ELSE 'Y' END) SUOM_VALID
3646 ,(CASE WHEN LST.LINE_TYPE <> 'SUBSCRIPTION'
3647 AND (LST.SUBSCRIPTION_QUANTITY IS NOT NULL OR LST.QUANTITY_UOM IS NOT NULL)
3648 THEN 'Y' ELSE 'N' END) SUB_NULL_ERROR
3649 ,(CASE WHEN LST.PRICE_UOM IS NOT NULL
3650 AND COUNT(DISTINCT PUOM.ROWID) OVER (PARTITION BY LST.ROWID) <> 1
3651 THEN 'N' ELSE 'Y' END) PRICE_UOM_V
3652 ,(CASE WHEN (LST.LINE_TYPE = 'USAGE' AND LST.USAGE_TYPE IN ('NPR', 'FRT', 'VRT', 'QTY'))
3653 OR LST.LINE_TYPE <> 'USAGE'
3654 THEN 'Y' ELSE 'N' END) USG_TYPE_V
3655 ,(CASE WHEN LST.LINE_TYPE <> 'USAGE' AND LST.USAGE_TYPE IS NOT NULL THEN 'Y' ELSE 'N' END) USG_TYPE_NULL_ERROR
3656 ,(CASE WHEN LST.USAGE_PERIOD IS NOT NULL
3657 AND COUNT(DISTINCT USGP.ROWID) OVER (PARTITION BY LST.ROWID) <> 1
3658 THEN 'N' ELSE 'Y' END) USG_PER_V
3659 ,(CASE WHEN (LST.LINE_TYPE <> 'USAGE' OR LST.USAGE_TYPE = 'NPR') AND LST.USAGE_PERIOD IS NOT NULL
3660 THEN 'Y' ELSE 'N' END) USG_PER_NULL_ERROR
3661 ,(CASE WHEN LST.AVERAGING_INTERVAL IS NOT NULL AND LST.AVERAGING_INTERVAL < 0
3662 THEN 'N' ELSE 'Y' END) AVG_INT_V
3663 ,(CASE WHEN (LST.LINE_TYPE <> 'USAGE' OR LST.USAGE_TYPE <> 'VRT') AND LST.AVERAGING_INTERVAL IS NOT NULL
3664 THEN 'Y' ELSE 'N' END) AVG_INT_NULL_ERROR
3665 ,(CASE WHEN LST.SETTLEMENT_INTERVAL IS NOT NULL
3666 AND COUNT(DISTINCT STMI.ROWID) OVER (PARTITION BY LST.ROWID) <> 1 THEN 'N' ELSE 'Y' END) STM_INT_V
3667 ,(CASE WHEN (LST.LINE_TYPE <> 'USAGE' OR LST.USAGE_TYPE <> 'VRT') AND LST.SETTLEMENT_INTERVAL IS NOT NULL
3668 THEN 'Y' ELSE 'N' END) STM_INT_NULL_ERROR
3669 ,(CASE WHEN LST.USAGE_TERMINATION_METHOD IS NOT NULL
3670 AND COUNT(DISTINCT UTM.ROWID) OVER (PARTITION BY LST.ROWID) <> 1 THEN 'N' ELSE 'Y' END) UTM_V
3671 ,(CASE WHEN (LST.LINE_TYPE <> 'USAGE' OR LST.USAGE_TYPE <> 'VRT') AND LST.USAGE_TERMINATION_METHOD IS NOT NULL
3672 THEN 'Y' ELSE 'N' END) UTM_NULL_ERROR
3673 ,(CASE WHEN LST.LINE_TYPE = 'SUBSCRIPTION'
3674 AND (LST.FIRST_BILLED_AMOUNT is not null and LST.FIRST_BILLED_AMOUNT < 0 )
3675 -- OR (LST.FIRST_BILL_UPTO_DATE IS NOT NULL AND LST.FIRST_BILLED_AMOUNT IS NULL))
3676 THEN 'N' ELSE 'Y' END) FIRST_BILLED_AMT_V
3677 ,(CASE WHEN LST.LINE_TYPE = 'SUBSCRIPTION'
3678 AND LST.FIRST_BILLED_AMOUNT IS NOT NULL AND LST.FIRST_BILL_UPTO_DATE IS NULL
3679 THEN 'N' ELSE 'Y' END) FIRST_BILLED_DATE_V
3680 ,(CASE WHEN LST.LINE_TYPE = 'SUBSCRIPTION' AND LST.LAST_BILLED_AMOUNT < 0
3681 THEN 'N' ELSE 'Y' END) LAST_BILLED_AMT_V
3682 ,(CASE WHEN LST.LAST_BILL_FROM_DATE - 1 > LST.END_DATE THEN 'N'
3683 WHEN LST.LINE_TYPE = 'SUBSCRIPTION' AND LST.LAST_BILLED_AMOUNT IS NOT NULL
3684 AND LST.LAST_BILL_FROM_DATE > LST.END_DATE THEN 'N'
3685 ELSE 'Y' END) LAST_BILL_DATE_V
3686 FROM OKS_INT_LINE_STG_TEMP LST
3687 ,OKS_INT_HEADER_STG_TEMP HDR
3688 ,OKC_TIME_CODE_UNITS_B BIP
3689 ,MTL_UNITS_OF_MEASURE SUOM
3690 ,OKC_TIME_CODE_UNITS_B PUOM
3691 ,OKC_TIME_CODE_UNITS_B USGP
3692 ,FND_LOOKUPS STMI
3693 ,FND_LOOKUPS UTM
3694 WHERE LST.HEADER_INTERFACE_ID = HDR.HEADER_INTERFACE_ID
3695 AND LST.BILLING_INTERVAL_PERIOD = BIP.UOM_CODE (+)
3696 AND LST.QUANTITY_UOM = SUOM.UOM_CODE (+)
3697 AND LST.PRICE_UOM = PUOM.UOM_CODE (+)
3698 AND LST.USAGE_PERIOD = USGP.UOM_CODE (+)
3699 AND LST.SETTLEMENT_INTERVAL = STMI.LOOKUP_CODE (+)
3700 AND STMI.LOOKUP_TYPE (+) = 'OKS_SETTLEMENT_INTERVAL'
3701 AND LST.USAGE_TERMINATION_METHOD = UTM.LOOKUP_CODE (+)
3702 AND UTM.LOOKUP_TYPE (+) = 'OKS_TRM_MTHD';
3703
3704 /* IF G_PROCEDURE_LOG THEN
3705 fnd_log.string(fnd_log.level_procedure,
3706 G_MODULE_HEAD || l_routine || '.' || l_stmt_num,
3707 'Exit.');
3708 END IF; */
3709
3710 EXCEPTION
3711 WHEN FND_API.G_EXC_ERROR THEN
3712 RAISE FND_API.G_EXC_ERROR;
3713 WHEN OTHERS THEN
3714 FND_MESSAGE.Set_Name('OKS', 'OKS_IMPORT_UNEXPECTED');
3715 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
3716 FND_MESSAGE.set_token('MESSAGE', 'stmt_num '||l_stmt_num||' ('||SQLCODE||') '||SQLERRM);
3717 FND_MSG_PUB.Add;
3718 RAISE FND_API.G_EXC_ERROR;
3719 END Validate_lines;
3720
3721 --========================================================================
3722 -- PROCEDURE : Validate_covered_levels PRIVATE
3723 -- PARAMETERS:
3724 -- COMMENT : This procedure will perform the validation needed
3725 -- on the coverage levels interface records.
3726 --=========================================================================
3727 PROCEDURE Validate_covered_levels
3728 IS
3729 l_stmt_num NUMBER := 0;
3730 l_routine CONSTANT VARCHAR2(30) := 'Validate_covered_levels';
3731 l_int_count NUMBER := 0;
3732 l_stg_count NUMBER := 0;
3733 BEGIN
3734 IF G_PROCEDURE_LOG THEN
3735 fnd_log.string(fnd_log.level_procedure,
3736 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
3737 'Begin');
3738 END IF;
3739
3740
3741 l_stmt_num := 10;
3742
3743 INSERT ALL
3744 WHEN (COVERED_INSTANCE_ID IS NOT NULL ) THEN
3745 INTO OKS_COVERED_INSTANCE_STG_TEMP
3746 (COVERED_LEVEL_INTERFACE_ID
3747 ,LINE_INTERFACE_ID
3748 ,HEADER_INTERFACE_ID
3749 ,COVERED_INSTANCE_ID
3750 ,COVERED_INSTANCE_NUMBER
3751 ,COVERED_SERIAL_NUMBER
3752 ,STATUS_CODE)
3753 VALUES (COVERED_LEVEL_INTERFACE_ID
3754 ,LINE_INTERFACE_ID
3755 ,HEADER_INTERFACE_ID
3756 ,COVERED_INSTANCE_ID
3757 ,COVERED_INSTANCE_NUMBER
3758 ,COVERED_SERIAL_NUMBER
3759 ,STATUS_CODE)
3760
3761 SELECT OCLI.COVERED_LEVEL_INTERFACE_ID COVERED_LEVEL_INTERFACE_ID
3762 ,OLSTG.HEADER_INTERFACE_ROWID HEADER_INTERFACE_ROWID
3763 ,OCLI.LINE_INTERFACE_ID LINE_INTERFACE_ID
3764
3765 ,OLSTG.HEADER_INTERFACE_ID HEADER_INTERFACE_ID
3766
3767 ,OCLI.COVERED_SERIAL_NUMBER COVERED_SERIAL_NUMBER
3768
3769 ,OCLI.COVERED_INSTANCE_NUMBER COVERED_INSTANCE_NUMBER
3770
3771 ,(CASE WHEN NAMEID_Q.CITMI_COUNT = 1 THEN NAMEID_Q.CITMI_ID
3772 WHEN NAMEID_Q.CITMN_COUNT = 1 THEN NAMEID_Q.CITMN_ID
3773 WHEN NAMEID_Q.CITMSER_COUNT = 1 THEN NAMEID_Q.CITMSER_ID
3774 ELSE NULL
3775 END) COVERED_INSTANCE_ID
3776
3777 ,OCLI.STATUS_CODE STATUS_CODE
3778
3779 FROM OKS_COVERED_LEVELS_INTERFACE OCLI
3780
3781 ,OKS_INT_LINE_STG_TEMP OLSTG
3782
3783 ,(SELECT distinct (CLI.COVERED_LEVEL_INTERFACE_ID)
3784 ,COUNT(DISTINCT CITMI.ROWID) OVER (PARTITION BY CLI.ROWID) CITMI_COUNT
3785 ,MAX(CLI.COVERED_INSTANCE_ID) OVER (PARTITION BY CLI.ROWID) CITMI_ID --INSTANCE ID Based on Id
3786 ,COUNT(DISTINCT CITMN.ROWID) OVER (PARTITION BY CLI.ROWID) CITMN_COUNT
3787 ,MAX(CITMN.INSTANCE_ID) OVER (PARTITION BY CLI.ROWID) CITMN_ID -- ITEM INSTANCE ID Based on Number
3788
3789 ,COUNT(DISTINCT CITMSER.ROWID) OVER (PARTITION BY CLI.ROWID) CITMSER_COUNT
3790 ,MAX(CITMSER.INSTANCE_ID) OVER (PARTITION BY CLI.ROWID) CITMSER_ID -- ITEM INSTANCE ID Based on Serial number
3791
3792 FROM OKS_COVERED_LEVELS_INTERFACE CLI
3793 ,CSI_ITEM_INSTANCES CITMI
3794 ,CSI_ITEM_INSTANCES CITMN
3795 ,CSI_ITEM_INSTANCES CITMSER
3796 WHERE CLI.COVERED_INSTANCE_ID = CITMI.INSTANCE_ID (+)
3797 AND CLI.COVERED_INSTANCE_NUMBER = CITMN.INSTANCE_NUMBER (+)
3798 AND CLI.COVERED_SERIAL_NUMBER = CITMSER.SERIAL_NUMBER(+)) NAMEID_Q
3799
3800
3801 WHERE OCLI.COVERED_LEVEL_INTERFACE_ID = NAMEID_Q.COVERED_LEVEL_INTERFACE_ID
3802 AND OCLI.LINE_INTERFACE_ID = OLSTG.LINE_INTERFACE_ID ;
3803
3804 l_stmt_num := 20;
3805
3806 INSERT ALL
3807 WHEN (COVERED_ITEM_ID IS NOT NULL) THEN
3808 INTO OKS_COVERED_ITEM_STG_TEMP
3809 (COVERED_LEVEL_INTERFACE_ID
3810 ,LINE_INTERFACE_ID
3811 ,HEADER_INTERFACE_ID
3812 ,COVERED_ITEM_ID
3813 ,COVERED_ITEM_NAME
3814 ,COVERED_ITEM_ORG_ID)
3815 VALUES (COVERED_LEVEL_INTERFACE_ID
3816 ,LINE_INTERFACE_ID
3817 ,HEADER_INTERFACE_ID
3818 ,COVERED_ITEM_ID
3819 ,COVERED_ITEM_NAME
3820 ,COVERED_ITEM_ORG_ID )
3821
3822 SELECT OCLI.COVERED_LEVEL_INTERFACE_ID COVERED_LEVEL_INTERFACE_ID
3823 ,OLSTG.HEADER_INTERFACE_ROWID HEADER_INTERFACE_ROWID
3824 ,OCLI.LINE_INTERFACE_ID LINE_INTERFACE_ID
3825
3826 ,OLSTG.HEADER_INTERFACE_ID HEADER_INTERFACE_ID
3827
3828 ,OCLI.COVERED_ITEM_NAME COVERED_ITEM_NAME
3829
3830 ,(CASE WHEN NAMEID_Q.CMSII_COUNT = 1 THEN NAMEID_Q.CMSII_ID
3831 WHEN NAMEID_Q.CMSIN_COUNT = 1 THEN NAMEID_Q.CMSIN_ID
3832 ELSE NULL
3833 END) COVERED_ITEM_ID
3834
3835 ,OCLI.COVERED_ITEM_ORG_ID COVERED_ITEM_ORG_ID
3836
3837 FROM OKS_COVERED_LEVELS_INTERFACE OCLI
3838
3839 ,OKS_INT_LINE_STG_TEMP OLSTG
3840
3841 ,(SELECT distinct (CLI.COVERED_LEVEL_INTERFACE_ID)
3842 ,COUNT(DISTINCT CMSII.ROWID) OVER (PARTITION BY CLI.ROWID ) CMSII_COUNT
3843 ,MAX(CLI.COVERED_ITEM_ID ) OVER (PARTITION BY CLI.ROWID ) CMSII_ID --COVERED ITEM ID Based on Id
3844
3845 ,COUNT(DISTINCT CMSIN.ROWID) OVER (PARTITION BY CLI.ROWID) CMSIN_COUNT
3846 ,MAX(CMSIN.INVENTORY_ITEM_ID) OVER (PARTITION BY CLI.ROWID) CMSIN_ID --COVERED ITEM ID Based on Name
3847
3848 FROM OKS_COVERED_LEVELS_INTERFACE CLI
3849 ,MTL_SYSTEM_ITEMS CMSII
3850 ,MTL_SYSTEM_ITEMS CMSIN
3851 WHERE CLI.COVERED_INSTANCE_ID = CMSII.INVENTORY_ITEM_ID (+)
3852 AND CLI.COVERED_ITEM_ORG_ID = CMSII.ORGANIZATION_ID(+)
3853
3854 AND CLI.COVERED_ITEM_NAME = CMSIN.SEGMENT1 (+)
3855 AND CLI.COVERED_ITEM_ORG_ID = CMSIN.ORGANIZATION_ID (+)) NAMEID_Q
3856
3857 WHERE OCLI.COVERED_LEVEL_INTERFACE_ID = NAMEID_Q.COVERED_LEVEL_INTERFACE_ID
3858 AND OCLI.LINE_INTERFACE_ID = OLSTG.LINE_INTERFACE_ID ;
3859
3860 l_stmt_num := 30;
3861
3862 INSERT ALL
3863 WHEN (COVERED_PARTY_ID IS NOT NULL) THEN
3864 INTO OKS_COVERED_PARTY_STG_TEMP
3865 (COVERED_LEVEL_INTERFACE_ID
3866 ,LINE_INTERFACE_ID
3867 ,HEADER_INTERFACE_ID
3868 ,COVERED_PARTY_ID
3869 ,COVERED_PARTY_NUMBER
3870 ,COVERED_PARTY_NAME)
3871 VALUES (COVERED_LEVEL_INTERFACE_ID
3872 ,LINE_INTERFACE_ID
3873 ,HEADER_INTERFACE_ID
3874 ,COVERED_PARTY_ID
3875 ,COVERED_PARTY_NUMBER
3876 ,COVERED_PARTY_NAME)
3877
3878 SELECT OCLI.COVERED_LEVEL_INTERFACE_ID COVERED_LEVEL_INTERFACE_ID
3879 ,OLSTG.HEADER_INTERFACE_ROWID HEADER_INTERFACE_ROWID
3880 ,OCLI.LINE_INTERFACE_ID LINE_INTERFACE_ID
3881
3882 ,OLSTG.HEADER_INTERFACE_ID HEADER_INTERFACE_ID
3883
3884 ,OCLI.COVERED_PARTY_NAME COVERED_PARTY_NAME
3885
3886 ,OCLI.COVERED_PARTY_NUMBER COVERED_PARTY_NUMBER
3887
3888 ,(CASE WHEN NAMEID_Q.HPI_COUNT = 1 THEN NAMEID_Q.HPI_ID
3889 WHEN NAMEID_Q.HPINUM_COUNT = 1 THEN NAMEID_Q.HPINUM_ID
3890 WHEN NAMEID_Q.HPIN_COUNT = 1 THEN NAMEID_Q.HPIN_ID
3891 ELSE NULL
3892 END) COVERED_PARTY_ID
3893
3894
3895 FROM OKS_COVERED_LEVELS_INTERFACE OCLI
3896
3897 ,OKS_INT_LINE_STG_TEMP OLSTG
3898
3899 ,(SELECT distinct (CLI.COVERED_LEVEL_INTERFACE_ID)
3900 ,COUNT(DISTINCT HPI.ROWID) OVER (PARTITION BY CLI.ROWID) HPI_COUNT
3901 ,MAX(CLI.COVERED_PARTY_ID) OVER (PARTITION BY CLI.ROWID) HPI_ID --COVERED PARTY ID Based on Id
3902
3903
3904 ,COUNT(DISTINCT HPINUM.ROWID) OVER (PARTITION BY CLI.ROWID) HPINUM_COUNT
3905 ,MAX(HPINUM.PARTY_ID) OVER (PARTITION BY CLI.ROWID) HPINUM_ID --COVERED PARTY ID Based on Number
3906
3907 ,COUNT(DISTINCT HPIN.ROWID) OVER (PARTITION BY CLI.ROWID) HPIN_COUNT
3908 ,MAX(HPIN.PARTY_ID) OVER (PARTITION BY CLI.ROWID) HPIN_ID --COVERED PARTY ID Based on Name
3909
3910 FROM OKS_COVERED_LEVELS_INTERFACE CLI
3911 ,HZ_PARTIES HPI
3912 ,HZ_PARTIES HPINUM
3913 ,HZ_PARTIES HPIN
3914
3915 WHERE CLI.COVERED_PARTY_ID = HPI.PARTY_ID (+)
3916 AND CLI.COVERED_PARTY_NAME = HPIN.PARTY_NAME (+)
3917 AND CLI.COVERED_PARTY_NUMBER = HPINUM.PARTY_NUMBER (+))NAMEID_Q
3918
3919 WHERE OCLI.COVERED_LEVEL_INTERFACE_ID = NAMEID_Q.COVERED_LEVEL_INTERFACE_ID
3920 AND OCLI.LINE_INTERFACE_ID = OLSTG.LINE_INTERFACE_ID ;
3921
3922
3923 l_stmt_num := 40;
3924
3925 INSERT ALL
3926 WHEN (COVERED_ACCOUNT_ID IS NOT NULL) THEN
3927 INTO OKS_COVERED_ACCOUNT_STG_TEMP
3928 (COVERED_LEVEL_INTERFACE_ID
3929 ,LINE_INTERFACE_ID
3930 ,HEADER_INTERFACE_ID
3931 ,COVERED_ACCOUNT_ID
3932 ,COVERED_ACCOUNT_NUMBER)
3933 VALUES (COVERED_LEVEL_INTERFACE_ID
3934 ,LINE_INTERFACE_ID
3935 ,HEADER_INTERFACE_ID
3936 ,COVERED_ACCOUNT_ID
3937 ,COVERED_ACCOUNT_NUMBER)
3938
3939 SELECT OCLI.COVERED_LEVEL_INTERFACE_ID COVERED_LEVEL_INTERFACE_ID
3940 ,OLSTG.HEADER_INTERFACE_ROWID HEADER_INTERFACE_ROWID
3941 ,OCLI.LINE_INTERFACE_ID LINE_INTERFACE_ID
3942
3943 ,OLSTG.HEADER_INTERFACE_ID HEADER_INTERFACE_ID
3944
3945 ,OCLI.COVERED_ACCOUNT_NUMBER COVERED_ACCOUNT_NUMBER
3946
3947 ,(CASE WHEN NAMEID_Q.HCAI_COUNT = 1 THEN NAMEID_Q.HCAI_ID
3948 WHEN NAMEID_Q.HCAN_COUNT = 1 THEN NAMEID_Q.HCAN_ID
3949 ELSE NULL
3950 END) COVERED_ACCOUNT_ID
3951
3952 FROM OKS_COVERED_LEVELS_INTERFACE OCLI
3953
3954 ,OKS_INT_LINE_STG_TEMP OLSTG
3955
3956 ,(SELECT distinct (CLI.COVERED_LEVEL_INTERFACE_ID)
3957 ,COUNT(DISTINCT HCAI.ROWID) OVER (PARTITION BY CLI.ROWID) HCAI_COUNT
3958 ,MAX(CLI.COVERED_ACCOUNT_ID) OVER (PARTITION BY CLI.ROWID) HCAI_ID --COVERED ACCOUNT ID Based on Id
3959
3960 ,COUNT(DISTINCT HCAN.ROWID) OVER (PARTITION BY CLI.ROWID) HCAN_COUNT
3961 ,MAX(HCAN.CUST_ACCOUNT_ID) OVER (PARTITION BY CLI.ROWID) HCAN_ID -- COVERED ACCOUNT ID Based on Number
3962
3963
3964 FROM OKS_COVERED_LEVELS_INTERFACE CLI
3965 ,HZ_CUST_ACCOUNTS HCAI
3966 ,HZ_CUST_ACCOUNTS HCAN
3967
3968 WHERE CLI.COVERED_ACCOUNT_ID = HCAI.CUST_ACCOUNT_ID (+)
3969 AND CLI.COVERED_ACCOUNT_NUMBER = HCAN.ACCOUNT_NUMBER (+)) NAMEID_Q
3970
3971 WHERE OCLI.COVERED_LEVEL_INTERFACE_ID = NAMEID_Q.COVERED_LEVEL_INTERFACE_ID
3972 AND OCLI.LINE_INTERFACE_ID = OLSTG.LINE_INTERFACE_ID ;
3973
3974 l_stmt_num := 50;
3975
3976 INSERT ALL
3977 WHEN (COVERED_SITE_ID IS NOT NULL) THEN
3978 INTO OKS_COVERED_SITE_STG_TEMP
3979 (COVERED_LEVEL_INTERFACE_ID
3980 ,LINE_INTERFACE_ID
3981 ,HEADER_INTERFACE_ID
3982 ,COVERED_SITE_ID
3983 ,COVERED_SITE_NUMBER)
3984 VALUES (COVERED_LEVEL_INTERFACE_ID
3985 ,LINE_INTERFACE_ID
3986 ,HEADER_INTERFACE_ID
3987 ,COVERED_SITE_ID
3988 ,COVERED_SITE_NUMBER)
3989
3990 SELECT OCLI.COVERED_LEVEL_INTERFACE_ID COVERED_LEVEL_INTERFACE_ID
3991
3992 ,OLSTG.HEADER_INTERFACE_ROWID HEADER_INTERFACE_ROWID
3993
3994 ,OCLI.LINE_INTERFACE_ID LINE_INTERFACE_ID
3995
3996 ,OLSTG.HEADER_INTERFACE_ID HEADER_INTERFACE_ID
3997
3998 ,OCLI.COVERED_SITE_NUMBER COVERED_SITE_NUMBER
3999
4000 ,(CASE WHEN NAMEID_Q.CSITEID_COUNT = 1 THEN NAMEID_Q.CSITEID_ID
4001 WHEN NAMEID_Q.CSITENUM_COUNT =1 THEN NAMEID_Q.CSITENUM_ID
4002 ELSE NULL
4003 END) COVERED_SITE_ID
4004
4005 FROM OKS_COVERED_LEVELS_INTERFACE OCLI
4006
4007 ,OKS_INT_LINE_STG_TEMP OLSTG
4008
4009 ,(SELECT distinct (CLI.COVERED_LEVEL_INTERFACE_ID)
4010
4011 ,COUNT(DISTINCT CSITEID.ROWID) OVER (PARTITION BY CLI.ROWID) CSITEID_COUNT
4012 ,MAX(CLI.COVERED_SITE_ID) OVER (PARTITION BY CLI.ROWID) CSITEID_ID -- COVERED SITE ID Based on Id
4013
4014 ,COUNT(DISTINCT CSITENUM.ROWID) OVER (PARTITION BY CLI.ROWID) CSITENUM_COUNT
4015 ,MAX(CSITENUM.PARTY_SITE_ID) OVER (PARTITION BY CLI.ROWID) CSITENUM_ID -- COVERED SITE ID Based on Number
4016
4017 FROM OKS_COVERED_LEVELS_INTERFACE CLI
4018 ,HZ_PARTY_SITES CSITEID
4019 ,HZ_PARTY_SITES CSITENUM
4020
4021 WHERE CLI.COVERED_SITE_ID = CSITEID.PARTY_SITE_ID (+)
4022 AND CLI.COVERED_SITE_NUMBER = CSITENUM.PARTY_SITE_NUMBER (+)) NAMEID_Q
4023
4024 WHERE OCLI.COVERED_LEVEL_INTERFACE_ID = NAMEID_Q.COVERED_LEVEL_INTERFACE_ID
4025
4026 AND OCLI.LINE_INTERFACE_ID = OLSTG.LINE_INTERFACE_ID ;
4027
4028
4029 l_stmt_num := 60;
4030
4031 INSERT ALL
4032 WHEN (COVERED_SYSTEM_ID IS NOT NULL) THEN
4033 INTO OKS_COVERED_SYSTEM_STG_TEMP
4034 (COVERED_LEVEL_INTERFACE_ID
4035 ,LINE_INTERFACE_ID
4036 ,HEADER_INTERFACE_ID
4037 ,COVERED_SYSTEM_ID)
4038 VALUES (COVERED_LEVEL_INTERFACE_ID
4039 ,LINE_INTERFACE_ID
4040 ,HEADER_INTERFACE_ID
4041 ,COVERED_SYSTEM_ID)
4042
4043 SELECT OCLI.COVERED_LEVEL_INTERFACE_ID COVERED_LEVEL_INTERFACE_ID
4044
4045 ,OLSTG.HEADER_INTERFACE_ROWID HEADER_INTERFACE_ROWID
4046
4047 ,OCLI.LINE_INTERFACE_ID LINE_INTERFACE_ID
4048
4049 ,OLSTG.HEADER_INTERFACE_ID HEADER_INTERFACE_ID
4050
4051 ,(CASE WHEN NAMEID_Q.CSYSID_COUNT =1 THEN NAMEID_Q.CSYSID_ID
4052 ELSE NULL
4053 END) COVERED_SYSTEM_ID
4054
4055 FROM OKS_COVERED_LEVELS_INTERFACE OCLI
4056
4057 ,OKS_INT_LINE_STG_TEMP OLSTG
4058
4059 ,(SELECT distinct (CLI.COVERED_LEVEL_INTERFACE_ID)
4060
4061 ,COUNT(DISTINCT CSYSID.ROWID) OVER (PARTITION BY CLI.ROWID) CSYSID_COUNT
4062 ,MAX(CLI.COVERED_SYSTEM_ID) OVER (PARTITION BY CLI.ROWID) CSYSID_ID --COVERED SYSTEM ID Based on Id
4063
4064 FROM OKS_COVERED_LEVELS_INTERFACE CLI
4065 ,CSI_SYSTEMS_B CSYSID
4066
4067 WHERE CLI.COVERED_SYSTEM_ID = CSYSID.SYSTEM_ID (+)) NAMEID_Q
4068
4069 WHERE OCLI.COVERED_LEVEL_INTERFACE_ID = NAMEID_Q.COVERED_LEVEL_INTERFACE_ID
4070
4071 AND OCLI.LINE_INTERFACE_ID = OLSTG.LINE_INTERFACE_ID ;
4072
4073 l_stmt_num := 70;
4074
4075 INSERT ALL
4076 WHEN (1=1) THEN
4077 INTO OKS_INT_COVERED_LEVEL_STG_TEMP
4078 (COVERED_LEVEL_INTERFACE_ID
4079 ,LINE_INTERFACE_ID
4080 ,HEADER_INTERFACE_ID
4081 ,COVERED_INSTANCE_ID
4082 ,COVERED_ITEM_ID
4083 ,COVERED_ACCOUNT_ID
4084 ,COVERED_SITE_ID
4085 ,COVERED_PARTY_ID
4086 ,COVERED_SYSTEM_ID)
4087 VALUES (COVERED_LEVEL_INTERFACE_ID
4088 ,LINE_INTERFACE_ID
4089 ,HEADER_INTERFACE_ID
4090 ,COVERED_INSTANCE_ID
4091 ,COVERED_ITEM_ID
4092 ,COVERED_ACCOUNT_ID
4093 ,COVERED_SITE_ID
4094 ,COVERED_PARTY_ID
4095 ,COVERED_SYSTEM_ID)
4096 WHEN (COV_LINE_INTERFACE_ID IS NOT NULL AND LINE_INTERFACE_ID IS NULL ) THEN
4097 INTO OKS_INT_ERROR_STG_TEMP
4098 (CONCURRENT_REQUEST_ID
4099 ,HEADER_INTERFACE_ROWID
4100 ,INTERFACE_SOURCE_TABLE
4101 ,INTERFACE_ID
4102 ,ERROR_MSG
4103 ,MSG_TOKENS)
4104 VALUES (G_WORKER_REQ_ID
4105 ,HEADER_INTERFACE_ROWID
4106 ,'OKS_COVERED_LEVELS_INTERFACE'
4107 ,COVERED_LEVEL_INTERFACE_ID
4108 ,'OKS_IMP_CLVL_INVALID_LINTID'
4109 ,NULL )
4110 WHEN (LINE_NUMBER IS NULL OR DUP_LINE_NUMBER_COUNT >0 OR LINE_NUMBER <= 0 OR floor(LINE_NUMBER) <> LINE_NUMBER) THEN
4111 INTO OKS_INT_ERROR_STG_TEMP
4112 (CONCURRENT_REQUEST_ID
4113 ,HEADER_INTERFACE_ROWID
4114 ,INTERFACE_SOURCE_TABLE
4115 ,INTERFACE_ID
4116 ,ERROR_MSG
4117 ,MSG_TOKENS)
4118 VALUES (G_WORKER_REQ_ID
4119 ,HEADER_INTERFACE_ROWID
4120 ,'OKS_COVERED_LEVELS_INTERFACE'
4121 ,COVERED_LEVEL_INTERFACE_ID
4122 ,'OKS_IMP_CLVL_INVALID_LINNUM'
4123 ,NULL)
4124 WHEN ( STATUS_CODE IS NULL OR STATUS_CLVL_VALID ='N' OR STATUS_LINLVL_VALID='N') THEN
4125 INTO OKS_INT_ERROR_STG_TEMP
4126 (CONCURRENT_REQUEST_ID
4127 ,HEADER_INTERFACE_ROWID
4128 ,INTERFACE_SOURCE_TABLE
4129 ,INTERFACE_ID
4130 ,ERROR_MSG
4131 ,MSG_TOKENS)
4132 VALUES (G_WORKER_REQ_ID
4133 ,HEADER_INTERFACE_ROWID
4134 ,'OKS_COVERED_LEVELS_INTERFACE'
4135 ,COVERED_LEVEL_INTERFACE_ID
4136 ,'OKS_IMP_CLVL_INVALID_STSCODE'
4137 ,NULL)
4138 WHEN (DATE_CLVL_VALID ='N' OR DATE_LINLVL_VALID='N') THEN
4139 INTO OKS_INT_ERROR_STG_TEMP
4140 (CONCURRENT_REQUEST_ID
4141 ,HEADER_INTERFACE_ROWID
4142 ,INTERFACE_SOURCE_TABLE
4143 ,INTERFACE_ID
4144 ,ERROR_MSG
4145 ,MSG_TOKENS)
4146 VALUES (G_WORKER_REQ_ID
4147 ,HEADER_INTERFACE_ROWID
4148 ,'OKS_COVERED_LEVELS_INTERFACE'
4149 ,COVERED_LEVEL_INTERFACE_ID
4150 ,'OKS_IMP_CLVL_INVALID_DTRANGE'
4151 ,NULL)
4152 WHEN (DATE_CLVL_STS_VALID ='N' ) THEN
4153 INTO OKS_INT_ERROR_STG_TEMP
4154 (CONCURRENT_REQUEST_ID
4155 ,HEADER_INTERFACE_ROWID
4156 ,INTERFACE_SOURCE_TABLE
4157 ,INTERFACE_ID
4158 ,ERROR_MSG
4159 ,MSG_TOKENS)
4160 VALUES (G_WORKER_REQ_ID
4161 ,HEADER_INTERFACE_ROWID
4162 ,'OKS_COVERED_LEVELS_INTERFACE'
4163 ,COVERED_LEVEL_INTERFACE_ID
4164 ,'OKS_IMP_CLVL_INVALID_STS_DT'
4165 ,NULL)
4166 WHEN (ST_DT_FST_BILL_YN ='N' ) THEN
4167 INTO OKS_INT_ERROR_STG_TEMP
4168 (CONCURRENT_REQUEST_ID
4169 ,HEADER_INTERFACE_ROWID
4170 ,INTERFACE_SOURCE_TABLE
4171 ,INTERFACE_ID
4172 ,ERROR_MSG
4173 ,MSG_TOKENS)
4174 VALUES (G_WORKER_REQ_ID
4175 ,HEADER_INTERFACE_ROWID
4176 ,'OKS_COVERED_LEVELS_INTERFACE'
4177 ,COVERED_LEVEL_INTERFACE_ID
4178 ,'OKS_IMP_CLVL_INVALID_SDT_FBDT'
4179 ,NULL)
4180
4181 WHEN (RNWL_TYPE_VALID ='N' ) THEN
4182 INTO OKS_INT_ERROR_STG_TEMP
4183 (CONCURRENT_REQUEST_ID
4184 ,HEADER_INTERFACE_ROWID
4185 ,INTERFACE_SOURCE_TABLE
4186 ,INTERFACE_ID
4187 ,ERROR_MSG
4188 ,MSG_TOKENS)
4189 VALUES (G_WORKER_REQ_ID
4190 ,HEADER_INTERFACE_ROWID
4191 ,'OKS_COVERED_LEVELS_INTERFACE'
4192 ,COVERED_LEVEL_INTERFACE_ID
4193 ,'OKS_IMP_CLVL_INVALID_RNMLCD'
4194 ,NULL)
4195 WHEN (DATE_CANC_VALID ='N' OR DATE_CANC_NULL ='Y') THEN
4196 INTO OKS_INT_ERROR_STG_TEMP
4197 (CONCURRENT_REQUEST_ID
4198 ,HEADER_INTERFACE_ROWID
4199 ,INTERFACE_SOURCE_TABLE
4200 ,INTERFACE_ID
4201 ,ERROR_MSG
4202 ,MSG_TOKENS)
4203 VALUES (G_WORKER_REQ_ID
4204 ,HEADER_INTERFACE_ROWID
4205 ,'OKS_COVERED_LEVELS_INTERFACE'
4206 ,COVERED_LEVEL_INTERFACE_ID
4207 ,'OKS_IMP_CLVL_INVALID_CNCLDT'
4208 ,NULL)
4209 WHEN (CR_VALID ='N' OR CR_NULL ='Y') THEN
4210 INTO OKS_INT_ERROR_STG_TEMP
4211 (CONCURRENT_REQUEST_ID
4212 ,HEADER_INTERFACE_ROWID
4213 ,INTERFACE_SOURCE_TABLE
4214 ,INTERFACE_ID
4215 ,ERROR_MSG
4216 ,MSG_TOKENS)
4217 VALUES (G_WORKER_REQ_ID
4218 ,HEADER_INTERFACE_ROWID
4219 ,'OKS_COVERED_LEVELS_INTERFACE'
4220 ,COVERED_LEVEL_INTERFACE_ID
4221 ,'OKS_IMP_CLVL_INVALID_CNCLRSN'
4222 ,NULL)
4223 WHEN (INVOICE_TEXT IS NULL ) THEN
4224 INTO OKS_INT_ERROR_STG_TEMP
4225 (CONCURRENT_REQUEST_ID
4226 ,HEADER_INTERFACE_ROWID
4227 ,INTERFACE_SOURCE_TABLE
4228 ,INTERFACE_ID
4229 ,ERROR_MSG
4230 ,MSG_TOKENS)
4231 VALUES (G_WORKER_REQ_ID
4232 ,HEADER_INTERFACE_ROWID
4233 ,'OKS_COVERED_LEVELS_INTERFACE'
4234 ,COVERED_LEVEL_INTERFACE_ID
4235 ,'OKS_IMP_CLVL_INVALID_INVTXT'
4236 ,NULL)
4237 WHEN (PRINT_INVOICE IS NULL OR (PRINT_INVOICE IS NOT NULL AND PRINT_INVOICE <> 'Y' AND PRINT_INVOICE <>'N')) THEN
4238 INTO OKS_INT_ERROR_STG_TEMP
4239 (CONCURRENT_REQUEST_ID
4240 ,HEADER_INTERFACE_ROWID
4241 ,INTERFACE_SOURCE_TABLE
4242 ,INTERFACE_ID
4243 ,ERROR_MSG
4244 ,MSG_TOKENS)
4245 VALUES (G_WORKER_REQ_ID
4246 ,HEADER_INTERFACE_ROWID
4247 ,'OKS_COVERED_LEVELS_INTERFACE'
4248 ,COVERED_LEVEL_INTERFACE_ID
4249 ,'OKS_IMP_CLVL_INVALID_PRNTINV'
4250 ,NULL)
4251 WHEN (QUANTITY_COVERED IS NULL OR QUANTITY_COVERED <= 0 OR FLOOR(QUANTITY_COVERED) <> QUANTITY_COVERED ) THEN
4252 INTO OKS_INT_ERROR_STG_TEMP
4253 (CONCURRENT_REQUEST_ID
4254 ,HEADER_INTERFACE_ROWID
4255 ,INTERFACE_SOURCE_TABLE
4256 ,INTERFACE_ID
4257 ,ERROR_MSG
4258 ,MSG_TOKENS)
4259 VALUES (G_WORKER_REQ_ID
4260 ,HEADER_INTERFACE_ROWID
4261 ,'OKS_COVERED_LEVELS_INTERFACE'
4262 ,COVERED_LEVEL_INTERFACE_ID
4263 ,'OKS_IMP_CLVL_INVALID_QUNTCOV'
4264 ,NULL)
4265 WHEN( QUOM_VALID = 'N' ) THEN
4266 INTO OKS_INT_ERROR_STG_TEMP
4267 (CONCURRENT_REQUEST_ID
4268 ,HEADER_INTERFACE_ROWID
4269 ,INTERFACE_SOURCE_TABLE
4270 ,INTERFACE_ID
4271 ,ERROR_MSG
4272 ,MSG_TOKENS)
4273 VALUES (G_WORKER_REQ_ID
4274 ,HEADER_INTERFACE_ROWID
4275 ,'OKS_COVERED_LEVELS_INTERFACE'
4276 ,COVERED_LEVEL_INTERFACE_ID
4277 ,'OKS_IMP_CLVL_INVALID_QUOM'
4278 ,NULL)
4279 WHEN ((COVERED_INSTANCE_ID IS NOT NULL OR COVERED_ITEM_ID IS NOT NULL)
4280 AND (PRICE_UOM IS NULL OR PRUOM_VALID ='N')) THEN
4281 INTO OKS_INT_ERROR_STG_TEMP
4282 (CONCURRENT_REQUEST_ID
4283 ,HEADER_INTERFACE_ROWID
4284 ,INTERFACE_SOURCE_TABLE
4285 ,INTERFACE_ID
4286 ,ERROR_MSG
4287 ,MSG_TOKENS)
4288 VALUES (G_WORKER_REQ_ID
4289 ,HEADER_INTERFACE_ROWID
4290 ,'OKS_COVERED_LEVELS_INTERFACE'
4291 ,COVERED_LEVEL_INTERFACE_ID
4292 ,'OKS_IMP_CLVL_INVALID_PRUOM'
4293 ,NULL)
4294 WHEN ((COVERED_INSTANCE_ID IS NULL AND COVERED_ITEM_ID IS NULL) AND PRICE_UOM IS NOT NULL) THEN
4295 INTO OKS_INT_ERROR_STG_TEMP
4296 (CONCURRENT_REQUEST_ID
4297 ,HEADER_INTERFACE_ROWID
4298 ,INTERFACE_SOURCE_TABLE
4299 ,INTERFACE_ID
4300 ,ERROR_MSG
4301 ,MSG_TOKENS)
4302 VALUES (G_WORKER_REQ_ID
4303 ,HEADER_INTERFACE_ROWID
4304 ,'OKS_COVERED_LEVELS_INTERFACE'
4305 ,COVERED_LEVEL_INTERFACE_ID
4306 ,'OKS_IMP_CLVL_INVALID_PRUOMNUL'
4307 ,NULL)
4308 WHEN (SUBTOTAL IS NULL OR SUBTOTAL <0 ) THEN
4309 INTO OKS_INT_ERROR_STG_TEMP
4310 (CONCURRENT_REQUEST_ID
4311 ,HEADER_INTERFACE_ROWID
4312 ,INTERFACE_SOURCE_TABLE
4313 ,INTERFACE_ID
4314 ,ERROR_MSG
4315 ,MSG_TOKENS)
4316 VALUES (G_WORKER_REQ_ID
4317 ,HEADER_INTERFACE_ROWID
4318 ,'OKS_COVERED_LEVELS_INTERFACE'
4319 ,COVERED_LEVEL_INTERFACE_ID
4320 ,'OKS_IMP_CLVL_INVALID_SUBTOT'
4321 ,NULL)
4322 WHEN( TAX_AMOUNT IS NULL OR TAX_AMOUNT < 0 ) THEN
4323 INTO OKS_INT_ERROR_STG_TEMP
4324 (CONCURRENT_REQUEST_ID
4325 ,HEADER_INTERFACE_ROWID
4326 ,INTERFACE_SOURCE_TABLE
4327 ,INTERFACE_ID
4328 ,ERROR_MSG
4329 ,MSG_TOKENS)
4330 VALUES (G_WORKER_REQ_ID
4331 ,HEADER_INTERFACE_ROWID
4332 ,'OKS_COVERED_LEVELS_INTERFACE'
4333 ,COVERED_LEVEL_INTERFACE_ID
4334 ,'OKS_IMP_CLVL_INVALID_TAXAMT'
4335 ,NULL)
4336
4337 WHEN ( TOT_COV_LVL_ATTR <> 1 ) THEN
4338 INTO OKS_INT_ERROR_STG_TEMP
4339 (CONCURRENT_REQUEST_ID
4340 ,HEADER_INTERFACE_ROWID
4341 ,INTERFACE_SOURCE_TABLE
4342 ,INTERFACE_ID
4343 ,ERROR_MSG
4344 ,MSG_TOKENS)
4345 VALUES (G_WORKER_REQ_ID
4346 ,HEADER_INTERFACE_ROWID
4347 ,'OKS_COVERED_LEVELS_INTERFACE'
4348 ,COVERED_LEVEL_INTERFACE_ID
4349 ,'OKS_IMP_CLVL_INVALID_CLVLID'
4350 ,NULL)
4351 /* WHEN (FIRST_BILL_AMT_VALID ='N' ) THEN
4352 INTO OKS_INT_ERROR_STG_TEMP
4353 (CONCURRENT_REQUEST_ID
4354 ,HEADER_INTERFACE_ROWID
4355 ,INTERFACE_SOURCE_TABLE
4356 ,INTERFACE_ID
4357 ,ERROR_MSG
4358 ,MSG_TOKENS)
4359 VALUES (G_WORKER_REQ_ID
4360 ,HEADER_INTERFACE_ROWID
4361 ,'OKS_COVERED_LEVELS_INTERFACE'
4362 ,COVERED_LEVEL_INTERFACE_ID
4363 ,'OKS_IMP_CLVL_INVALID_FBILAMT'
4364 ,NULL)
4365 WHEN (LAST_BILL_AMT_VALID ='N' ) THEN
4366 INTO OKS_INT_ERROR_STG_TEMP
4367 (CONCURRENT_REQUEST_ID
4368 ,HEADER_INTERFACE_ROWID
4369 ,INTERFACE_SOURCE_TABLE
4370 ,INTERFACE_ID
4371 ,ERROR_MSG
4372 ,MSG_TOKENS)
4373 VALUES (G_WORKER_REQ_ID
4374 ,HEADER_INTERFACE_ROWID
4375 ,'OKS_COVERED_LEVELS_INTERFACE'
4376 ,COVERED_LEVEL_INTERFACE_ID
4377 ,'OKS_IMP_CLVL_INVALID_LBILAMT'
4378 ,NULL) */
4379
4380 SELECT OCLI.COVERED_LEVEL_INTERFACE_ID COVERED_LEVEL_INTERFACE_ID
4381 ,OLSTG.HEADER_INTERFACE_ROWID HEADER_INTERFACE_ROWID
4382 ,OLSTG.HEADER_INTERFACE_ID HEADER_INTERFACE_ID
4383
4384 ,OCLI.LINE_INTERFACE_ID COV_LINE_INTERFACE_ID
4385
4386 ,(CASE WHEN COUNT(DISTINCT OLSTG.ROWID) OVER (PARTITION BY OCLI.ROWID) = 1
4387 THEN OCLI.LINE_INTERFACE_ID
4388 ELSE NULL END) LINE_INTERFACE_ID
4389
4390 ,OLSTG.LSE_ID LINE_LSE_ID
4391
4392 ,OKSINST_STG.COVERED_INSTANCE_ID
4393
4394 ,OKSITM_STG.COVERED_ITEM_ID
4395
4396 ,OKSPRTY_STG.COVERED_PARTY_ID
4397
4398 ,OKSACC_STG.COVERED_ACCOUNT_ID
4399
4400 ,OKSSITE_STG.COVERED_SITE_ID
4401
4402 ,OKSSYS_STG.COVERED_SYSTEM_ID
4403
4404 ,DECODE(NVL(OKSINST_STG.COVERED_INSTANCE_ID ,0),0,0,1) + DECODE(NVL(OKSITM_STG.COVERED_ITEM_ID,0),0,0,1)
4405 + DECODE(NVL(OKSPRTY_STG.COVERED_PARTY_ID,0),0,0,1) +DECODE(NVL(OKSACC_STG.COVERED_ACCOUNT_ID,0),0,0,1)
4406 +DECODE(NVL(OKSSITE_STG.COVERED_SITE_ID,0),0,0,1) +DECODE(NVL(OKSSYS_STG.COVERED_SYSTEM_ID,0),0,0,1) TOT_COV_LVL_ATTR
4407
4408 ,OCLI.LINE_NUMBER LINE_NUMBER
4409
4410 ,COUNT(DISTINCT OCLIN.ROWID) OVER (PARTITION BY OCLI.ROWID) DUP_LINE_NUMBER_COUNT
4411
4412 ,RTRIM(OCLI.LINE_REFERENCE) LINE_REFERENCE
4413
4414 ,OCLI.STATUS_CODE STATUS_CODE
4415
4416 ,(CASE WHEN STS.STE_CODE IN ('ENTERED','ACTIVE','CANCELLED','SIGNED','EXPIRED')
4417 THEN 'Y'
4418 ELSE 'N' END) STATUS_CLVL_VALID
4419
4420 ,(CASE WHEN OLSTG.STATUS_CODE IN ('ENTERED') AND OCLI.STATUS_CODE IN ('ENTERED','CANCELLED') THEN 'Y'
4421 WHEN OLSTG.STATUS_CODE IN ('ACTIVE') AND OCLI.STATUS_CODE IN ('SIGNED','ACTIVE','EXPIRED','CANCELLED') THEN 'Y'
4422 WHEN OLSTG.STATUS_CODE IN ('CANCELLED') AND OCLI.STATUS_CODE IN ('CANCELLED') THEN 'Y'
4423 WHEN OLSTG.STATUS_CODE IN ('SIGNED') AND OCLI.STATUS_CODE IN ('SIGNED' , 'CANCELLED') THEN 'Y'
4424 WHEN OLSTG.STATUS_CODE IN ('EXPIRED') AND OCLI.STATUS_CODE IN ('EXPIRED', 'CANCELLED') THEN 'Y'
4425 ELSE 'N'
4426 END) STATUS_LINLVL_VALID
4427
4428 ,TRUNC(OCLI.START_DATE) START_DATE
4429
4430 ,TRUNC(OLSTG.START_DATE) LINE_START_DATE
4431
4432 ,TRUNC(OCLI.END_DATE) END_DATE
4433
4434 ,TRUNC(OLSTG.END_DATE) LINE_END_DATE
4435
4436 ,(CASE WHEN OCLI.START_DATE >=OLSTG.START_DATE AND OCLI.END_DATE <=OLSTG.END_DATE
4437 THEN 'Y'
4438 ELSE 'N' END ) DATE_LINLVL_VALID
4439
4440 ,(CASE WHEN OCLI.START_DATE IS NULL OR OCLI.END_DATE IS NULL OR OCLI.START_DATE >OCLI.END_DATE
4441 THEN 'N'
4442 ELSE 'Y' END) DATE_CLVL_VALID
4443
4444 ,(CASE WHEN ( TRUNC( OCLI.START_DATE) > SYSDATE) AND STS.STE_CODE IN ('ACTIVE','EXPIRED')
4445 OR (TRUNC(OCLI.END_DATE) <=SYSDATE) AND STS.STE_CODE ='SIGNED'
4446 OR (SYSDATE BETWEEN TRUNC(OCLI.START_DATE) AND TRUNC(OCLI.END_DATE))
4447 AND (STS.STE_CODE IN ('SIGNED','EXPIRED'))
4448 THEN 'N'
4449 ELSE 'Y' END) DATE_CLVL_STS_VALID
4450
4451 ,(CASE WHEN OLSTG.FIRST_BILL_UPTO_DATE IS NOT NULL AND OLSTG.FIRST_BILL_UPTO_DATE < OCLI.START_DATE THEN 'N'
4452 ELSE 'Y'
4453 END) ST_DT_FST_BILL_YN
4454
4455 ,OCLI.RENEWAL_TYPE_CODE RENEWAL_TYPE_CODE
4456
4457 ,(CASE WHEN OCLI.RENEWAL_TYPE_CODE IS NOT NULL
4458 AND (COUNT(DISTINCT RNWLC.ROWID) OVER (PARTITION BY OCLI.ROWID) <> 1)
4459 THEN 'N' ELSE 'Y'
4460 END) RNWL_TYPE_VALID
4461
4462 ,OCLI.CANCELLATION_DATE CANCELLATION_DATE
4463
4464 ,(CASE WHEN (OCLI.CANCELLATION_DATE IS NULL AND STS.STE_CODE ='CANCELLED')
4465 THEN 'N'
4466 ELSE 'Y' END) DATE_CANC_VALID
4467
4468 ,(CASE WHEN (OCLI.CANCELLATION_DATE IS NOT NULL AND STS.STE_CODE <> 'CANCELLED')
4469 THEN 'Y'
4470 ELSE 'N' END) DATE_CANC_NULL
4471
4472 ,OCLI.CANCELLATION_REASON CANCELLATION_REASON
4473
4474 ,(CASE WHEN (STS.STE_CODE = 'CANCELLED'
4475 AND ( COUNT(DISTINCT CR1.ROWID) OVER (PARTITION BY OCLI.ROWID) <>1
4476 AND COUNT(DISTINCT CR2.ROWID) OVER (PARTITION BY OCLI.ROWID) <>1))
4477 THEN 'N'
4478 ELSE 'Y' END) CR_VALID
4479
4480 ,(CASE WHEN(STS.STE_CODE <>'CANCELLED' AND OCLI.CANCELLATION_REASON IS NOT NULL )
4481 THEN 'Y' ELSE 'N' END) CR_NULL
4482
4483 ,RTRIM(OCLI.INVOICE_TEXT) INVOICE_TEXT
4484
4485 ,OCLI.PRINT_INVOICE PRINT_INVOICE
4486
4487 ,OCLI.QUANTITY_COVERED QUANTITY_COVERED
4488
4489 ,OCLI.QUANTITY_UOM QUANTITY_UOM
4490
4491 ,(CASE WHEN ((OCLI.QUANTITY_UOM IS NOT NULL) AND
4492 (COUNT(DISTINCT QUOM.ROWID) OVER (PARTITION BY OCLI.ROWID)) <> 1)
4493 THEN 'N'
4494 ELSE 'Y' END) QUOM_VALID
4495
4496 ,OCLI.PRICE_UOM PRICE_UOM
4497
4498 ,(CASE WHEN ((OCLI.PRICE_UOM IS NOT NULL) AND
4499 (COUNT(DISTINCT PRUOM.ROWID ) OVER (PARTITION BY OCLI.ROWID)) <>1)
4500 THEN 'N'
4501 ELSE 'Y' END) PRUOM_VALID
4502
4503 ,OCLI.SUBTOTAL SUBTOTAL
4504 ,OCLI.TAX_AMOUNT TAX_AMOUNT
4505 ,OCLI.FIRST_BILL_AMOUNT
4506 ,OCLI.LAST_BILL_AMOUNT
4507 /*
4508 ,(CASE WHEN OCLI.FIRST_BILL_AMOUNT IS NOT NULL AND OLSTG.FIRST_BILL_UPTO_DATE IS NULL THEN 'N'
4509 ELSE 'Y'
4510 END) FIRST_BILL_AMT_VALID
4511
4512 ,(CASE WHEN OCLI.LAST_BILL_AMOUNT IS NULL AND OLSTG.LAST_BILL_FROM_DATE -1 > OCLI.END_DATE THEN 'N'
4513 WHEN OCLI.LAST_BILL_AMOUNT IS NOT NULL AND OLSTG.LAST_BILL_FROM_DATE > OCLI.END_DATE THEN 'N'
4514 ELSE 'Y'
4515 END) LAST_BILL_AMT_VALID
4516 */
4517
4518 FROM OKS_COVERED_LEVELS_INTERFACE OCLI
4519
4520 ,OKS_COVERED_LEVELS_INTERFACE OCLIN
4521
4522 ,OKS_INT_LINE_STG_TEMP OLSTG
4523
4524 ,OKC_STATUSES_B STS
4525
4526 ,FND_LOOKUPS CR1
4527
4528 ,FND_LOOKUPS CR2
4529
4530 ,OKC_TIME_CODE_UNITS_V PRUOM
4531
4532 ,OKX_UNITS_OF_MEASURE_V QUOM
4533
4534 ,FND_LOOKUPS RNWLC
4535
4536 ,OKS_COVERED_INSTANCE_STG_TEMP OKSINST_STG
4537
4538 ,OKS_COVERED_ITEM_STG_TEMP OKSITM_STG
4539
4540 ,OKS_COVERED_PARTY_STG_TEMP OKSPRTY_STG
4541
4542 ,OKS_COVERED_ACCOUNT_STG_TEMP OKSACC_STG
4543
4544 ,OKS_COVERED_SITE_STG_TEMP OKSSITE_STG
4545
4546 ,OKS_COVERED_SYSTEM_STG_TEMP OKSSYS_STG
4547
4548
4549 WHERE OCLI.LINE_INTERFACE_ID = OLSTG.LINE_INTERFACE_ID
4550
4551 AND OCLI.COVERED_LEVEL_INTERFACE_ID = OKSINST_STG.COVERED_LEVEL_INTERFACE_ID (+)
4552
4553 AND OCLI.COVERED_LEVEL_INTERFACE_ID = OKSITM_STG.COVERED_LEVEL_INTERFACE_ID (+)
4554
4555 AND OCLI.COVERED_LEVEL_INTERFACE_ID = OKSPRTY_STG.COVERED_LEVEL_INTERFACE_ID (+)
4556
4557 AND OCLI.COVERED_LEVEL_INTERFACE_ID = OKSACC_STG.COVERED_LEVEL_INTERFACE_ID (+)
4558
4559 AND OCLI.COVERED_LEVEL_INTERFACE_ID = OKSSITE_STG.COVERED_LEVEL_INTERFACE_ID (+)
4560
4561 AND OCLI.COVERED_LEVEL_INTERFACE_ID = OKSSYS_STG.COVERED_LEVEL_INTERFACE_ID (+)
4562
4563 AND OCLI.STATUS_CODE = STS.CODE (+)
4564
4565 AND OCLI.CANCELLATION_REASON = CR1.LOOKUP_CODE (+)
4566
4567 AND CR1.LOOKUP_TYPE (+) = 'OKS_CANCEL_REASON'
4568
4569 AND OCLI.CANCELLATION_REASON = CR2.LOOKUP_CODE (+)
4570
4571 AND CR2.LOOKUP_TYPE (+) = 'OKC_STS_CHG_REASON'
4572
4573 AND OCLI.PRICE_UOM = PRUOM.UOM_CODE(+)
4574
4575 AND PRUOM.ACTIVE_FLAG(+) ='Y'
4576
4577 AND OCLI.QUANTITY_UOM = QUOM.UOM_CODE (+)
4578
4579 AND TRUNC( NVL( QUOM.DISABLE_DATE (+), SYSDATE)) >= TRUNC(SYSDATE)
4580
4581 AND OCLI.RENEWAL_TYPE_CODE = RNWLC.LOOKUP_CODE (+)
4582
4583 AND RNWLC.LOOKUP_TYPE (+) = 'OKC_LINE_RENEWAL_TYPE'
4584
4585 AND OCLIN.LINE_NUMBER (+) =OCLI.LINE_NUMBER
4586
4587 AND OCLIN.LINE_INTERFACE_ID(+) = OCLI.LINE_INTERFACE_ID
4588
4589 AND OCLIN.COVERED_LEVEL_INTERFACE_ID(+) <> OCLI.COVERED_LEVEL_INTERFACE_ID ;
4590
4591
4592
4593 IF G_STMT_LOG THEN
4594
4595 SELECT count(1) INTO l_int_count FROM OKS_COVERED_LEVELS_INTERFACE OCLI
4596 WHERE EXISTS (SELECT 'X' FROM OKS_INT_LINE_STG_TEMP
4597 WHERE LINE_INTERFACE_ID = OCLI.LINE_INTERFACE_ID);
4598
4599 SELECT count(1) INTO l_stg_count FROM OKS_INT_COVERED_LEVEL_STG_TEMP;
4600 /* to avoid gscc failure */
4601 IF G_STMT_LOG THEN
4602 fnd_log.string(fnd_log.level_statement,
4603 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
4604 'Number of records in interface table being processed = ' || l_int_count ||', '||
4605 'Number of records inserted into staging table = '|| l_stg_count);
4606 END IF;
4607 END IF;
4608 IF G_PROCEDURE_LOG THEN
4609 fnd_log.string(fnd_log.level_procedure,
4610 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
4611 'Exit.');
4612 END IF;
4613 EXCEPTION
4614 WHEN FND_API.G_EXC_ERROR THEN
4615 RAISE FND_API.G_EXC_ERROR;
4616 WHEN OTHERS THEN
4617 FND_MESSAGE.Set_Name('OKS', 'OKS_IMPORT_UNEXPECTED');
4618 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
4619 FND_MESSAGE.set_token('MESSAGE', 'stmt_num '||l_stmt_num||' ('||SQLCODE||') '||SQLERRM);
4620 FND_MSG_PUB.Add;
4621 RAISE FND_API.G_EXC_ERROR;
4622 END Validate_covered_levels;
4623
4624 --========================================================================
4625 -- PROCEDURE : Validate_usage_counters PRIVATE
4626 -- PARAMETERS:
4627 -- COMMENT : This procedure will perform the validation needed
4628 -- on the usage counters interface records.
4629 --=========================================================================
4630
4631 PROCEDURE Validate_usage_counters
4632 IS
4633 l_stmt_num NUMBER := 0;
4634 l_routine CONSTANT VARCHAR2(30) := 'Validate_usage_counters';
4635 l_int_count NUMBER := 0;
4636 l_stg_count NUMBER := 0;
4637 BEGIN
4638 IF G_PROCEDURE_LOG THEN
4639 fnd_log.string(fnd_log.level_procedure,
4640 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
4641 'Begin');
4642 END IF;
4643
4644 l_stmt_num := 10;
4645 INSERT ALL
4646 WHEN (1=1) THEN
4647 INTO OKS_INT_USAGE_COUNTER_STG_TEMP
4648 (USAGE_COUNTER_INTERFACE_ID
4649 ,LINE_INTERFACE_ID
4650 ,HEADER_INTERFACE_ROWID
4651 ,LINE_INTERFACE_ROWID
4652 ,USAGE_COUNTER_INTERFACE_ROWID
4653 ,COUNTER_ID
4654 ,LINE_NUMBER
4655 ,LINE_REFERENCE
4656 ,STATUS_CODE
4657 ,START_DATE
4658 ,END_DATE
4659 ,RENEWAL_TYPE_CODE
4660 ,CANCELLATION_DATE
4661 ,CANCELLATION_REASON
4662 ,PRINT_INVOICE
4663 ,SUBTOTAL
4664 ,TAX_AMOUNT
4665 ,FIXED_USG_CTR
4666 ,MINIMUM_USG_CTR
4667 ,DEFAULT_USG_CTR
4668 ,FILL_YN
4669 ,ESTIMATION_METHOD
4670 ,ESTIMATION_START_DATE
4671 ,LEVEL_YN )
4672 VALUES (USAGE_COUNTER_INTERFACE_ID
4673 ,LINE_INTERFACE_ID
4674 ,HEADER_INTERFACE_ROWID
4675 ,LINE_INTERFACE_ROWID
4676 ,USAGE_COUNTER_INTERFACE_ROWID
4677 ,COUNTER_ID
4678 ,LINE_NUMBER
4679 ,LINE_REFERENCE
4680 ,STATUS_CODE
4681 ,START_DATE
4682 ,END_DATE
4683 ,RENEWAL_TYPE_CODE
4684 ,CANCELLATION_DATE
4685 ,CANCELLATION_REASON
4686 ,PRINT_INVOICE
4687 ,SUBTOTAL
4688 ,TAX_AMOUNT
4689 ,FIXED_USG_CNT
4690 ,MINIMUM_USG_CNT
4691 ,DEFAULT_USG_CNT
4692 ,FILL_YN
4693 ,ESTIMATION_METHOD
4694 ,ESTIMATION_START_DATE
4695 ,LEVEL_YN )
4696 WHEN (LINE_INTERFACE_ID IS NOT NULL AND LINSTG_LINE_INTERFACE_ID IS NULL ) THEN
4697 INTO OKS_INT_ERROR_STG_TEMP
4698 (CONCURRENT_REQUEST_ID
4699 ,HEADER_INTERFACE_ROWID
4700 ,INTERFACE_SOURCE_TABLE
4701 ,INTERFACE_ID
4702 ,ERROR_MSG
4703 ,MSG_TOKENS)
4704 VALUES (G_WORKER_REQ_ID
4705 ,HEADER_INTERFACE_ROWID
4706 ,'OKS_USAGE_COUNTERS_INTERFACE'
4707 , USAGE_COUNTER_INTERFACE_ID
4708 ,'OKS_IMP_UCTR_INVALID_LINTID'
4709 ,NULL )
4710 WHEN (LINE_NUMBER IS NULL OR DUP_LINE_NUMBER_COUNT >0 OR LINE_NUMBER <= 0 OR floor(LINE_NUMBER) <> LINE_NUMBER) THEN
4711 INTO OKS_INT_ERROR_STG_TEMP
4712 (CONCURRENT_REQUEST_ID
4713 ,HEADER_INTERFACE_ROWID
4714 ,INTERFACE_SOURCE_TABLE
4715 ,INTERFACE_ID
4716 ,ERROR_MSG
4717 ,MSG_TOKENS)
4718 VALUES (G_WORKER_REQ_ID
4719 ,HEADER_INTERFACE_ROWID
4720 ,'OKS_USAGE_COUNTERS_INTERFACE'
4721 ,USAGE_COUNTER_INTERFACE_ID
4722 ,'OKS_IMP_UCTR_INVALID_LINNUM'
4723 ,NULL)
4724 WHEN (CNTR_VALID_YN = 'N' ) THEN
4725 INTO OKS_INT_ERROR_STG_TEMP
4726 (CONCURRENT_REQUEST_ID
4727 ,HEADER_INTERFACE_ROWID
4728 ,INTERFACE_SOURCE_TABLE
4729 ,INTERFACE_ID
4730 ,ERROR_MSG
4731 ,MSG_TOKENS)
4732 VALUES (G_WORKER_REQ_ID
4733 ,HEADER_INTERFACE_ROWID
4734 ,'OKS_USAGE_COUNTERS_INTERFACE'
4735 ,USAGE_COUNTER_INTERFACE_ID
4736 ,'OKS_IMP_UCTR_INVALID_CNTRID'
4737 ,NULL)
4738 WHEN (START_DATE IS NULL OR START_DATE_VALID ='N' ) THEN
4739 INTO OKS_INT_ERROR_STG_TEMP
4740 (CONCURRENT_REQUEST_ID
4741 ,HEADER_INTERFACE_ROWID
4742 ,INTERFACE_SOURCE_TABLE
4743 ,INTERFACE_ID
4744 ,ERROR_MSG
4745 ,MSG_TOKENS)
4746 VALUES (G_WORKER_REQ_ID
4747 ,HEADER_INTERFACE_ROWID
4748 ,'OKS_USAGE_COUNTERS_INTERFACE'
4749 ,USAGE_COUNTER_INTERFACE_ID
4750 ,'OKS_IMP_UCTR_INVALID_STRDT'
4751 ,NULL)
4752
4753 WHEN (END_DATE IS NULL OR END_DATE_VALID='N' ) THEN
4754 INTO OKS_INT_ERROR_STG_TEMP
4755 (CONCURRENT_REQUEST_ID
4756 ,HEADER_INTERFACE_ROWID
4757 ,INTERFACE_SOURCE_TABLE
4758 ,INTERFACE_ID
4759 ,ERROR_MSG
4760 ,MSG_TOKENS)
4761 VALUES (G_WORKER_REQ_ID
4762 ,HEADER_INTERFACE_ROWID
4763 ,'OKS_USAGE_COUNTERS_INTERFACE'
4764 ,USAGE_COUNTER_INTERFACE_ID
4765 ,'OKS_IMP_UCTR_INVALID_ENDDT'
4766 ,NULL)
4767
4768 WHEN ((STATUS_CODE IS NULL) OR STATUS_CNTLVL_VALID='N' OR STATUS_USGLINLVL_VALID='N') THEN
4769 INTO OKS_INT_ERROR_STG_TEMP
4770 (CONCURRENT_REQUEST_ID
4771 ,HEADER_INTERFACE_ROWID
4772 ,INTERFACE_SOURCE_TABLE
4773 ,INTERFACE_ID
4774 ,ERROR_MSG
4775 ,MSG_TOKENS)
4776 VALUES (G_WORKER_REQ_ID
4777 ,HEADER_INTERFACE_ROWID
4778 ,'OKS_USAGE_COUNTERS_INTERFACE'
4779 ,USAGE_COUNTER_INTERFACE_ID
4780 ,'OKS_IMP_UCTR_INVALID_STSCODE'
4781 ,NULL)
4782 WHEN (DATE_USGCNT_STS_VALID ='N') THEN
4783 INTO OKS_INT_ERROR_STG_TEMP
4784 (CONCURRENT_REQUEST_ID
4785 ,HEADER_INTERFACE_ROWID
4786 ,INTERFACE_SOURCE_TABLE
4787 ,INTERFACE_ID
4788 ,ERROR_MSG
4789 ,MSG_TOKENS)
4790 VALUES (G_WORKER_REQ_ID
4791 ,HEADER_INTERFACE_ROWID
4792 ,'OKS_USAGE_COUNTERS_INTERFACE'
4793 ,USAGE_COUNTER_INTERFACE_ID
4794 ,'OKS_IMP_USGCNT_INVALID_STS_DT'
4795 ,NULL)
4796
4797 WHEN (RENEWAL_TYPE_CODE_VALID = 'N') THEN
4798 INTO OKS_INT_ERROR_STG_TEMP
4799 (CONCURRENT_REQUEST_ID
4800 ,HEADER_INTERFACE_ROWID
4801 ,INTERFACE_SOURCE_TABLE
4802 ,INTERFACE_ID
4803 ,ERROR_MSG
4804 ,MSG_TOKENS)
4805 VALUES (G_WORKER_REQ_ID
4806 ,HEADER_INTERFACE_ROWID
4807 ,'OKS_USAGE_COUNTERS_INTERFACE'
4808 ,USAGE_COUNTER_INTERFACE_ID
4809 ,'OKS_IMP_UCTR_INVALID_RNWLCD'
4810 ,NULL)
4811 WHEN (DATE_CANC_VALID ='N' OR DATE_CANC_NULL ='Y') THEN
4812 INTO OKS_INT_ERROR_STG_TEMP
4813 (CONCURRENT_REQUEST_ID
4814 ,HEADER_INTERFACE_ROWID
4815 ,INTERFACE_SOURCE_TABLE
4816 ,INTERFACE_ID
4817 ,ERROR_MSG
4818 ,MSG_TOKENS)
4819 VALUES (G_WORKER_REQ_ID
4820 ,HEADER_INTERFACE_ROWID
4821 ,'OKS_USAGE_COUNTERS_INTERFACE'
4822 ,USAGE_COUNTER_INTERFACE_ID
4823 ,'OKS_IMP_UCTR_INVALID_CNCLDT'
4824 ,NULL)
4825 WHEN (CR_VALID ='N' OR CR_NULL ='Y') THEN
4826 INTO OKS_INT_ERROR_STG_TEMP
4827 (CONCURRENT_REQUEST_ID
4828 ,HEADER_INTERFACE_ROWID
4829 ,INTERFACE_SOURCE_TABLE
4830 ,INTERFACE_ID
4831 ,ERROR_MSG
4832 ,MSG_TOKENS)
4833 VALUES (G_WORKER_REQ_ID
4834 ,HEADER_INTERFACE_ROWID
4835 ,'OKS_USAGE_COUNTERS_INTERFACE'
4836 ,USAGE_COUNTER_INTERFACE_ID
4837 ,'OKS_IMP_UCTR_INVALID_CNCLRSN'
4838 ,NULL)
4839 WHEN (PRINT_INVOICE IS NULL OR (PRINT_INVOICE IS NOT NULL AND PRINT_INVOICE <> 'Y' AND PRINT_INVOICE <> 'N')) THEN
4840 INTO OKS_INT_ERROR_STG_TEMP
4841 (CONCURRENT_REQUEST_ID
4842 ,HEADER_INTERFACE_ROWID
4843 ,INTERFACE_SOURCE_TABLE
4844 ,INTERFACE_ID
4845 ,ERROR_MSG
4846 ,MSG_TOKENS)
4847 VALUES (G_WORKER_REQ_ID
4848 ,HEADER_INTERFACE_ROWID
4849 ,'OKS_USAGE_COUNTERS_INTERFACE'
4850 ,USAGE_COUNTER_INTERFACE_ID
4851 ,'OKS_IMP_UCTR_INVALID_PRINV'
4852 ,NULL)
4853 WHEN ( SUBTOTAL_VALID = 'N' OR SUBTOTAL <0) THEN
4854 INTO OKS_INT_ERROR_STG_TEMP
4855 (CONCURRENT_REQUEST_ID
4856 ,HEADER_INTERFACE_ROWID
4857 ,INTERFACE_SOURCE_TABLE
4858 ,INTERFACE_ID
4859 ,ERROR_MSG
4860 ,MSG_TOKENS)
4861 VALUES (G_WORKER_REQ_ID
4862 ,HEADER_INTERFACE_ROWID
4863 ,'OKS_USAGE_COUNTERS_INTERFACE'
4864 ,USAGE_COUNTER_INTERFACE_ID
4865 ,'OKS_IMP_UCTR_INVALID_STOT'
4866 ,NULL)
4867 WHEN (TAX_VALID ='N' OR TAX_AMOUNT < 0 ) THEN
4868 INTO OKS_INT_ERROR_STG_TEMP
4869 (CONCURRENT_REQUEST_ID
4870 ,HEADER_INTERFACE_ROWID
4871 ,INTERFACE_SOURCE_TABLE
4872 ,INTERFACE_ID
4873 ,ERROR_MSG
4874 ,MSG_TOKENS)
4875 VALUES (G_WORKER_REQ_ID
4876 ,HEADER_INTERFACE_ROWID
4877 ,'OKS_USAGE_COUNTERS_INTERFACE'
4878 ,USAGE_COUNTER_INTERFACE_ID
4879 ,'OKS_IMP_UCTR_INVALID_TAXINV'
4880 ,NULL)
4881 WHEN (FIXED_USG_CNT_VALID = 'N' OR FIXED_USG_CNT_VALID IS NULL OR FIXED_USG_CNT <0 ) THEN
4882 INTO OKS_INT_ERROR_STG_TEMP
4883 (CONCURRENT_REQUEST_ID
4884 ,HEADER_INTERFACE_ROWID
4885 ,INTERFACE_SOURCE_TABLE
4886 ,INTERFACE_ID
4887 ,ERROR_MSG
4888 ,MSG_TOKENS)
4889 VALUES (G_WORKER_REQ_ID
4890 ,HEADER_INTERFACE_ROWID
4891 ,'OKS_USAGE_COUNTERS_INTERFACE'
4892 ,USAGE_COUNTER_INTERFACE_ID
4893 ,'OKS_IMP_UCTR_INVALID_FIXEDUSG'
4894 ,NULL)
4895 WHEN ( MINIMUM_USG_CNT_VALID ='N' OR MINIMUM_USG_CNT_VALID IS NULL OR MINIMUM_USG_CNT <0 ) THEN
4896 INTO OKS_INT_ERROR_STG_TEMP
4897 (CONCURRENT_REQUEST_ID
4898 ,HEADER_INTERFACE_ROWID
4899 ,INTERFACE_SOURCE_TABLE
4900 ,INTERFACE_ID
4901 ,ERROR_MSG
4902 ,MSG_TOKENS)
4903 VALUES (G_WORKER_REQ_ID
4904 ,HEADER_INTERFACE_ROWID
4905 ,'OKS_USAGE_COUNTERS_INTERFACE'
4906 ,USAGE_COUNTER_INTERFACE_ID
4907 ,'OKS_IMP_UCTR_INVALID_MINUSG'
4908 ,NULL)
4909 WHEN ( DEFAULT_USG_CNT_VALID='N' OR DEFAULT_USG_CNT_VALID IS NULL OR DEFAULT_USG_CNT < 0 ) THEN
4910 INTO OKS_INT_ERROR_STG_TEMP
4911 (CONCURRENT_REQUEST_ID
4912 ,HEADER_INTERFACE_ROWID
4913 ,INTERFACE_SOURCE_TABLE
4914 ,INTERFACE_ID
4915 ,ERROR_MSG
4916 ,MSG_TOKENS)
4917 VALUES (G_WORKER_REQ_ID
4918 ,HEADER_INTERFACE_ROWID
4919 ,'OKS_USAGE_COUNTERS_INTERFACE'
4920 ,USAGE_COUNTER_INTERFACE_ID
4921 ,'OKS_IMP_UCTR_INVALID_DEFUSG'
4922 ,NULL)
4923 WHEN (FILL_YN_VALID = 'N' OR ( FILL_YN <> 'Y' AND FILL_YN <> 'N')) THEN
4924 INTO OKS_INT_ERROR_STG_TEMP
4925 (CONCURRENT_REQUEST_ID
4926 ,HEADER_INTERFACE_ROWID
4927 ,INTERFACE_SOURCE_TABLE
4928 ,INTERFACE_ID
4929 ,ERROR_MSG
4930 ,MSG_TOKENS)
4931 VALUES (G_WORKER_REQ_ID
4932 ,HEADER_INTERFACE_ROWID
4933 ,'OKS_USAGE_COUNTERS_INTERFACE'
4934 ,USAGE_COUNTER_INTERFACE_ID
4935 ,'OKS_IMP_UCTR_INVALID_FILLYN'
4936 ,NULL)
4937 WHEN (ESTIMATION_METHOD_VALID='N' OR ESTMTHD_LKUP_VALID='N') THEN
4938 INTO OKS_INT_ERROR_STG_TEMP
4939 (CONCURRENT_REQUEST_ID
4940 ,HEADER_INTERFACE_ROWID
4941 ,INTERFACE_SOURCE_TABLE
4942 ,INTERFACE_ID
4943 ,ERROR_MSG
4944 ,MSG_TOKENS)
4945 VALUES (G_WORKER_REQ_ID
4946 ,HEADER_INTERFACE_ROWID
4947 ,'OKS_USAGE_COUNTERS_INTERFACE'
4948 ,USAGE_COUNTER_INTERFACE_ID
4949 ,'OKS_IMP_UCTR_INVALID_ESTMTHD'
4950 ,NULL)
4951 WHEN (EST_STRDT_VALID='N') THEN
4952 INTO OKS_INT_ERROR_STG_TEMP
4953 (CONCURRENT_REQUEST_ID
4954 ,HEADER_INTERFACE_ROWID
4955 ,INTERFACE_SOURCE_TABLE
4956 ,INTERFACE_ID
4957 ,ERROR_MSG
4958 ,MSG_TOKENS)
4959 VALUES (G_WORKER_REQ_ID
4960 ,HEADER_INTERFACE_ROWID
4961 ,'OKS_USAGE_COUNTERS_INTERFACE'
4962 ,USAGE_COUNTER_INTERFACE_ID
4963 ,'OKS_IMP_UCTR_INVALID_ESTSRTDT'
4964 ,NULL)
4965 WHEN (LEVEL_YN_VALID ='N' OR (LEVEL_YN is not NULL and LEVEL_YN <>'Y' AND LEVEL_YN <>'N')) THEN
4966 INTO OKS_INT_ERROR_STG_TEMP
4967 (CONCURRENT_REQUEST_ID
4968 ,HEADER_INTERFACE_ROWID
4969 ,INTERFACE_SOURCE_TABLE
4970 ,INTERFACE_ID
4971 ,ERROR_MSG
4972 ,MSG_TOKENS)
4973 VALUES (G_WORKER_REQ_ID
4974 ,HEADER_INTERFACE_ROWID
4975 ,'OKS_USAGE_COUNTERS_INTERFACE'
4976 ,USAGE_COUNTER_INTERFACE_ID
4977 ,'OKS_IMP_UCTR_INVALID_LVLYN'
4978 ,NULL)
4979
4980 SELECT
4981 OUSGCNTR.USAGE_COUNTER_INTERFACE_ID USAGE_COUNTER_INTERFACE_ID
4982 ,OLSTG.HEADER_INTERFACE_ROWID HEADER_INTERFACE_ROWID
4983 ,OLSTG.LINE_INTERFACE_ROWID LINE_INTERFACE_ROWID
4984 ,OUSGCNTR.ROWID USAGE_COUNTER_INTERFACE_ROWID
4985 ,OUSGCNTR.LINE_INTERFACE_ID LINE_INTERFACE_ID
4986 ,OLSTG.LINE_INTERFACE_ID LINSTG_LINE_INTERFACE_ID
4987 ,OUSGCNTR.COUNTER_ID COUNTER_ID -- validation to be done
4988 ,OUSGCNTR.LINE_NUMBER LINE_NUMBER
4989 ,COUNT(DISTINCT OUSGLIN.ROWID) OVER (PARTITION BY OUSGCNTR.ROWID) DUP_LINE_NUMBER_COUNT
4990 ,OLSTG.USAGE_TYPE USAGE_TYPE
4991 ,OUSGCNTR.FIXED_USG_CNT FIXED_USG_CNT
4992 ,(CASE WHEN OLSTG.USAGE_TYPE ='FRT' AND OUSGCNTR.FIXED_USG_CNT IS NOT NULL THEN 'Y'
4993 WHEN OLSTG.USAGE_TYPE ='FRT' AND OUSGCNTR.FIXED_USG_CNT IS NULL THEN 'N'
4994 WHEN OLSTG.USAGE_TYPE <>'FRT' AND OUSGCNTR.FIXED_USG_CNT IS NOT NULL THEN 'N'
4995 WHEN OLSTG.USAGE_TYPE <>'FRT' AND OUSGCNTR.FIXED_USG_CNT IS NULL THEN 'Y'
4996 END) FIXED_USG_CNT_VALID
4997 ,OUSGCNTR.MINIMUM_USG_CNT MINIMUM_USG_CNT
4998 ,(CASE WHEN (OLSTG.USAGE_TYPE='FRT' OR OLSTG.USAGE_TYPE='NPR') AND (OUSGCNTR.MINIMUM_USG_CNT IS NULL)
4999 THEN 'Y'
5000 WHEN (OLSTG.USAGE_TYPE='FRT' OR OLSTG.USAGE_TYPE='NPR') AND (OUSGCNTR.MINIMUM_USG_CNT IS NOT NULL)
5001 THEN 'N'
5002 WHEN (OLSTG.USAGE_TYPE='VRT' OR OLSTG.USAGE_TYPE='QTY') AND (OUSGCNTR.MINIMUM_USG_CNT IS NOT NULL
5003 AND OUSGCNTR.MINIMUM_USG_CNT <= OUSGCNTR.DEFAULT_USG_CNT )
5004 THEN 'Y'
5005 WHEN (OLSTG.USAGE_TYPE='VRT' OR OLSTG.USAGE_TYPE='QTY') AND (OUSGCNTR.MINIMUM_USG_CNT IS NULL)
5006 THEN 'Y'
5007 ELSE 'N'
5008 END) MINIMUM_USG_CNT_VALID
5009 ,OUSGCNTR.DEFAULT_USG_CNT DEFAULT_USG_CNT
5010 ,(CASE WHEN (OLSTG.USAGE_TYPE='FRT' OR OLSTG.USAGE_TYPE='NPR') AND OUSGCNTR.DEFAULT_USG_CNT IS NULL THEN 'Y'
5011 WHEN (OLSTG.USAGE_TYPE='FRT' OR OLSTG.USAGE_TYPE='NPR' ) AND OUSGCNTR.DEFAULT_USG_CNT IS NOT NULL THEN 'N'
5012 WHEN (OLSTG.USAGE_TYPE='VRT' OR OLSTG.USAGE_TYPE='QTY') AND OUSGCNTR.DEFAULT_USG_CNT IS NULL THEN 'N'
5013 WHEN (OLSTG.USAGE_TYPE='VRT' OR OLSTG.USAGE_TYPE='QTY') AND OUSGCNTR.DEFAULT_USG_CNT IS NOT NULL THEN 'Y'
5014 END) DEFAULT_USG_CNT_VALID
5015 ,OUSGCNTR.FILL_YN FILL_YN
5016 ,(CASE WHEN OLSTG.USAGE_TYPE<>'VRT' AND OUSGCNTR.FILL_YN IS NOT NULL THEN 'N'
5017 ELSE 'Y'
5018 END) FILL_YN_VALID
5019 ,OUSGCNTR.ESTIMATION_METHOD ESTIMATION_METHOD
5020 ,(CASE WHEN OLSTG.USAGE_TYPE='VRT' AND OUSGCNTR.FILL_YN ='Y' AND OUSGCNTR.ESTIMATION_METHOD IS NULL THEN 'N'
5021 WHEN OLSTG.USAGE_TYPE<>'VRT' AND OUSGCNTR.ESTIMATION_METHOD IS NOT NULL THEN 'N'
5022 ELSE 'Y'
5023 END) ESTIMATION_METHOD_VALID
5024 ,(CASE WHEN ((OUSGCNTR.ESTIMATION_METHOD IS NOT NULL) AND
5025 (COUNT(DISTINCT ESTMTHD.ROWID) OVER (PARTITION BY OUSGCNTR.ROWID)) <> 1)
5026 THEN 'N'
5027 ELSE 'Y'
5028 END) ESTMTHD_LKUP_VALID
5029 ,OUSGCNTR.ESTIMATION_START_DATE ESTIMATION_START_DATE
5030 ,(CASE WHEN OLSTG.USAGE_TYPE <> 'VRT' AND OUSGCNTR.ESTIMATION_START_DATE IS NOT NULL THEN 'N'
5031 WHEN OLSTG.USAGE_TYPE ='VRT' AND OUSGCNTR.ESTIMATION_METHOD ='CSR' AND
5032 OUSGCNTR.ESTIMATION_START_DATE IS NULL
5033 THEN 'N'
5034 WHEN (OLSTG.USAGE_TYPE='VRT' AND OUSGCNTR.ESTIMATION_METHOD = 'AMCV') AND
5035 OUSGCNTR.ESTIMATION_START_DATE IS NOT NULL THEN 'N'
5036 ELSE 'Y'
5037 END) EST_STRDT_VALID
5038 ,OUSGCNTR.LEVEL_YN LEVEL_YN
5039 /*,(CASE WHEN OLSTG.USAGE_TYPE <>'VRT' AND (OUSGCNTR.LEVEL_YN IS NOT NULL AND OUSGCNTR.LEVEL_YN <>'N') THEN 'N'
5040 WHEN OLSTG.USAGE_TYPE= 'VRT' AND OUSGCNTR.LEVEL_YN IS NULL THEN 'N'
5041 ELSE 'Y'
5042 END) LEVEL_YN_VALID -- NEED TO CHECK IN INSERT FOR VALID VALUES Y OR N*/
5043
5044 , (CASE WHEN (OLSTG.USAGE_TYPE ='FRT' OR OLSTG.USAGE_TYPE='NPR') AND OUSGCNTR.LEVEL_YN IS NOT NULL THEN 'N'
5045 ELSE 'Y'
5046 END) LEVEL_YN_VALID
5047
5048 ,OUSGCNTR.STATUS_CODE STATUS_CODE
5049 ,(CASE WHEN STS.STE_CODE IN ('ENTERED','ACTIVE','CANCELLED','SIGNED','EXPIRED')
5050 THEN 'Y'
5051 ELSE 'N' END) STATUS_CNTLVL_VALID
5052 ,(CASE WHEN OLSTG.STATUS_CODE IN ('ENTERED') AND OUSGCNTR.STATUS_CODE IN ('ENTERED','CANCELLED') THEN 'Y'
5053 WHEN OLSTG.STATUS_CODE IN ('ACTIVE') AND OUSGCNTR.STATUS_CODE IN ('SIGNED','ACTIVE','EXPIRED','CANCELLED') THEN 'Y'
5054 WHEN OLSTG.STATUS_CODE IN ('CANCELLED') AND OUSGCNTR.STATUS_CODE IN ('CANCELLED') THEN 'Y'
5055 WHEN OLSTG.STATUS_CODE IN ('SIGNED') AND OUSGCNTR.STATUS_CODE IN ('SIGNED' , 'CANCELLED') THEN 'Y'
5056 WHEN OLSTG.STATUS_CODE IN ('EXPIRED') AND OUSGCNTR.STATUS_CODE IN ('EXPIRED', 'CANCELLED') THEN 'Y'
5057 ELSE 'N'
5058 END) STATUS_USGLINLVL_VALID
5059 ,(CASE WHEN ( TRUNC( OUSGCNTR.START_DATE) > SYSDATE) AND OUSGCNTR.STATUS_CODE IN ('ACTIVE','EXPIRED')
5060 OR (TRUNC(OUSGCNTR.END_DATE) <=SYSDATE) AND OUSGCNTR.STATUS_CODE ='SIGNED'
5061 OR (SYSDATE BETWEEN TRUNC(OUSGCNTR.START_DATE) AND TRUNC(OUSGCNTR.END_DATE))
5062 AND (OUSGCNTR.STATUS_CODE IN ('SIGNED','EXPIRED'))
5063 THEN 'N'
5064 ELSE 'Y' END) DATE_USGCNT_STS_VALID
5065
5066 ,OUSGCNTR.START_DATE START_DATE
5067 ,OLSTG.START_DATE ULIN_START_DATE
5068 ,OUSGCNTR.END_DATE END_DATE
5069 ,OLSTG.END_DATE ULIN_END_DATE
5070 ,(CASE WHEN OUSGCNTR.START_DATE = OLSTG.START_DATE THEN 'Y'
5071 ELSE 'N'
5072 END) START_DATE_VALID
5073 ,(CASE WHEN OUSGCNTR.END_DATE = OLSTG.END_DATE THEN 'Y'
5074 ELSE 'N'
5075 END) END_DATE_VALID
5076 ,OUSGCNTR.RENEWAL_TYPE_CODE RENEWAL_TYPE_CODE
5077 ,OLSTG.RENEWAL_TYPE_CODE USAGELINE_RENEWAL_CODE
5078 ,(CASE WHEN OUSGCNTR.RENEWAL_TYPE_CODE IS NULL THEN 'Y'
5079 WHEN OUSGCNTR.RENEWAL_TYPE_CODE IS NOT NULL AND
5080 OUSGCNTR.RENEWAL_TYPE_CODE=OLSTG.RENEWAL_TYPE_CODE THEN 'Y'
5081 ELSE 'N'
5082 END) RENEWAL_TYPE_CODE_VALID
5083 ,OUSGCNTR.CANCELLATION_DATE CANCELLATION_DATE
5084 ,(CASE WHEN (OUSGCNTR.CANCELLATION_DATE IS NULL AND STS.STE_CODE ='CANCELLED')
5085 THEN 'N'
5086 ELSE 'Y' END) DATE_CANC_VALID
5087 ,(CASE WHEN (OUSGCNTR.CANCELLATION_DATE IS NOT NULL AND STS.STE_CODE <> 'CANCELLED')
5088 THEN 'Y'
5089 ELSE 'N' END) DATE_CANC_NULL
5090 ,OUSGCNTR.CANCELLATION_REASON CANCELLATION_REASON
5091 ,(CASE WHEN (STS.STE_CODE = 'CANCELLED'
5092 AND ( COUNT(DISTINCT CR1.ROWID) OVER (PARTITION BY OUSGCNTR.ROWID) <>1
5093 AND COUNT(DISTINCT CR2.ROWID) OVER (PARTITION BY OUSGCNTR.ROWID) <>1))
5094 THEN 'N'
5095 ELSE 'Y' END) CR_VALID
5096 ,(CASE WHEN(STS.STE_CODE <>'CANCELLED' AND OUSGCNTR.CANCELLATION_REASON IS NOT NULL )
5097 THEN 'Y' ELSE 'N' END) CR_NULL
5098 ,OUSGCNTR.PRINT_INVOICE PRINT_INVOICE
5099 ,RTRIM(OUSGCNTR.INVOICE_TEXT) INVOICE_TEXT
5100 ,OUSGCNTR.SUBTOTAL SUBTOTAL
5101 ,(CASE WHEN OLSTG.USAGE_TYPE <> 'NPR' AND OUSGCNTR.SUBTOTAL IS NOT NULL THEN 'N'
5102 WHEN OLSTG.USAGE_TYPE ='NPR' AND OUSGCNTR.SUBTOTAL IS NULL THEN 'N'
5103 ELSE 'Y'
5104 END) SUBTOTAL_VALID
5105 ,OUSGCNTR.TAX_AMOUNT
5106 ,(CASE WHEN OLSTG.USAGE_TYPE <> 'NPR' AND OUSGCNTR.TAX_AMOUNT IS NOT NULL THEN 'N'
5107 WHEN OLSTG.USAGE_TYPE ='NPR' AND OUSGCNTR.TAX_AMOUNT IS NULL THEN 'N'
5108 ELSE 'Y' END) TAX_VALID
5109 ,RTRIM(OUSGCNTR.LINE_REFERENCE) LINE_REFERENCE
5110 ,(CASE WHEN (COUNT(DISTINCT CSI_CNTR.ROWID) OVER (PARTITION BY OUSGCNTR.ROWID)) <> 1 THEN 'N'
5111 WHEN CSI_CNTR.USAGE_ITEM_ID <> OLSTG.ITEM_ID THEN 'N'
5112 ELSE 'Y'
5113 END ) CNTR_VALID_YN
5114
5115 FROM OKS_USAGE_COUNTERS_INTERFACE OUSGCNTR
5116 ,OKS_USAGE_COUNTERS_INTERFACE OUSGLIN
5117 ,OKS_INT_LINE_STG_TEMP OLSTG
5118 ,FND_LOOKUPS ESTMTHD
5119 ,OKC_STATUSES_B STS
5120 ,FND_LOOKUPS CR1
5121 ,FND_LOOKUPS CR2
5122 ,CSI_COUNTERS_B CSI_CNTR
5123
5124 WHERE OUSGCNTR.LINE_INTERFACE_ID = OLSTG.LINE_INTERFACE_ID
5125 AND OUSGCNTR.ESTIMATION_METHOD = ESTMTHD.LOOKUP_CODE(+)
5126 AND ESTMTHD.LOOKUP_TYPE(+) = 'OKS_CTR_EST_MTHD'
5127 AND OUSGCNTR.STATUS_CODE = STS.CODE (+)
5128 AND OUSGCNTR.CANCELLATION_REASON = CR1.LOOKUP_CODE (+)
5129 AND CR1.LOOKUP_TYPE (+) = 'OKS_CANCEL_REASON'
5130 AND OUSGCNTR.CANCELLATION_REASON = CR2.LOOKUP_CODE (+)
5131 AND CR2.LOOKUP_TYPE (+) = 'OKC_STS_CHG_REASON'
5132 AND OUSGLIN.LINE_NUMBER (+) =OUSGCNTR.LINE_NUMBER
5133 AND OUSGLIN.LINE_INTERFACE_ID(+) = OUSGCNTR.LINE_INTERFACE_ID
5134 AND OUSGCNTR.COUNTER_ID = CSI_CNTR.COUNTER_ID (+)
5135 AND OUSGLIN.USAGE_COUNTER_INTERFACE_ID(+) <> OUSGCNTR.USAGE_COUNTER_INTERFACE_ID;
5136
5137
5138 IF G_STMT_LOG THEN
5139
5140 SELECT count(1) INTO l_int_count FROM OKS_INT_USAGE_COUNTER_STG_TEMP OUCI
5141 WHERE EXISTS (SELECT 'X' FROM OKS_INT_LINE_STG_TEMP
5142 WHERE LINE_INTERFACE_ID = OUCI.LINE_INTERFACE_ID);
5143
5144 SELECT count(1) INTO l_stg_count FROM OKS_INT_USAGE_COUNTER_STG_TEMP;
5145 /* to avoid gscc failure */
5146 IF G_STMT_LOG THEN
5147 fnd_log.string(fnd_log.level_statement,
5148 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
5149 'Number of records in interface table being processed = ' || l_int_count ||', '||
5150 'Number of records inserted into staging table = '|| l_stg_count);
5151 END IF;
5152 END IF;
5153
5154 IF G_PROCEDURE_LOG THEN
5155 fnd_log.string(fnd_log.level_procedure,
5156 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
5157 'Exit.');
5158 END IF;
5159 EXCEPTION
5160 WHEN FND_API.G_EXC_ERROR THEN
5161 RAISE FND_API.G_EXC_ERROR;
5162 WHEN OTHERS THEN
5163 FND_MESSAGE.Set_Name('OKS', 'OKS_IMPORT_UNEXPECTED');
5164 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
5165 FND_MESSAGE.set_token('MESSAGE', 'stmt_num '||l_stmt_num||' ('||SQLCODE||') '||SQLERRM);
5166 FND_MSG_PUB.Add;
5167 RAISE FND_API.G_EXC_ERROR;
5168 END Validate_usage_counters;
5169 --========================================================================
5170 -- PROCEDURE : Validate_Sales_Credits PRIVATE
5171 -- PARAMETERS:
5172 -- COMMENT : This procedure will perform the validation needed
5173 -- on the Sales Credits interface records.
5174 --=========================================================================
5175
5176 PROCEDURE Validate_Sales_Credits
5177 IS
5178 l_stmt_num NUMBER := 0;
5179 l_routine CONSTANT VARCHAR2(30) := 'Validate_Sales_Credits';
5180 l_int_count NUMBER := 0;
5181 l_stg_count NUMBER := 0;
5182 BEGIN
5183 IF G_PROCEDURE_LOG THEN
5184 fnd_log.string(fnd_log.level_procedure,
5185 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
5186 'Begin');
5187 END IF;
5188
5189 l_stmt_num := 10;
5190
5191 INSERT ALL
5192 WHEN (1=1) THEN
5193 INTO OKS_INT_SALES_CREDIT_STG_TEMP
5194 (SALES_CREDIT_INTERFACE_ID
5195 ,HEADER_INTERFACE_ID
5196 ,HEADER_INTERFACE_ROWID
5197 ,SALESPERSON_ID
5198 ,SALESGROUP_ID
5199 ,SALES_CREDIT_TYPE_ID
5200 ,PERCENT)
5201 VALUES
5202 (SALES_CREDIT_INTERFACE_ID
5203 ,HEADER_INTERFACE_ID
5204 ,HEADER_INTERFACE_ROWID
5205 ,SALESPERSON_ID
5206 ,SALESGROUP_ID
5207 ,SALES_CREDIT_TYPE_ID
5208 ,PERCENT)
5209 WHEN (SALESPERSON_ID IS NULL) THEN
5210 INTO OKS_INT_ERROR_STG_TEMP
5211 (CONCURRENT_REQUEST_ID
5212 ,HEADER_INTERFACE_ROWID
5213 ,INTERFACE_SOURCE_TABLE
5214 ,INTERFACE_ID
5215 ,ERROR_MSG
5216 ,MSG_TOKENS)
5217 VALUES (G_WORKER_REQ_ID
5218 ,HEADER_INTERFACE_ROWID
5219 ,'OKS_SALES_CREDITS_INTERFACE'
5220 ,SALES_CREDIT_INTERFACE_ID
5221 ,'OKS_IMP_SC_INVALID_SALEPERS'
5222 ,NULL)
5223 WHEN (SALESGROUP_V = 'N') THEN
5224 INTO OKS_INT_ERROR_STG_TEMP
5225 (CONCURRENT_REQUEST_ID
5226 ,HEADER_INTERFACE_ROWID
5227 ,INTERFACE_SOURCE_TABLE
5228 ,INTERFACE_ID
5229 ,ERROR_MSG
5230 ,MSG_TOKENS)
5231 VALUES (G_WORKER_REQ_ID
5232 ,HEADER_INTERFACE_ROWID
5233 ,'OKS_SALES_CREDITS_INTERFACE'
5234 ,SALES_CREDIT_INTERFACE_ID
5235 ,'OKS_IMP_SC_INVALID_SALEGRP'
5236 ,NULL)
5237 WHEN (SALES_CREDIT_TYPE_ID IS NULL) THEN
5238 INTO OKS_INT_ERROR_STG_TEMP
5239 (CONCURRENT_REQUEST_ID
5240 ,HEADER_INTERFACE_ROWID
5241 ,INTERFACE_SOURCE_TABLE
5242 ,INTERFACE_ID
5243 ,ERROR_MSG
5244 ,MSG_TOKENS)
5245 VALUES (G_WORKER_REQ_ID
5246 ,HEADER_INTERFACE_ROWID
5247 ,'OKS_SALES_CREDITS_INTERFACE'
5248 ,SALES_CREDIT_INTERFACE_ID
5249 ,'OKS_IMP_SC_INVALID_SCT'
5250 ,NULL)
5251 WHEN (QUOTA_FLAG = 'Y' AND PERCENT_SUM <> 100) THEN
5252 INTO OKS_INT_ERROR_STG_TEMP
5253 (CONCURRENT_REQUEST_ID
5254 ,HEADER_INTERFACE_ROWID
5255 ,INTERFACE_SOURCE_TABLE
5256 ,INTERFACE_ID
5257 ,ERROR_MSG
5258 ,MSG_TOKENS)
5259 VALUES (G_WORKER_REQ_ID
5260 ,HEADER_INTERFACE_ROWID
5261 ,'OKS_SALES_CREDITS_INTERFACE'
5262 ,SALES_CREDIT_INTERFACE_ID
5263 ,'OKS_IMP_SC_INVALID_PERSUM'
5264 ,NULL)
5265 WHEN (PERCENT IS NULL OR PERCENT < 0 OR PERCENT > 100) THEN
5266 INTO OKS_INT_ERROR_STG_TEMP
5267 (CONCURRENT_REQUEST_ID
5268 ,HEADER_INTERFACE_ROWID
5269 ,INTERFACE_SOURCE_TABLE
5270 ,INTERFACE_ID
5271 ,ERROR_MSG
5272 ,MSG_TOKENS)
5273 VALUES (G_WORKER_REQ_ID
5274 ,HEADER_INTERFACE_ROWID
5275 ,'OKS_SALES_CREDITS_INTERFACE'
5276 ,SALES_CREDIT_INTERFACE_ID
5277 ,'OKS_IMP_SC_INVALID_PER'
5278 ,NULL)
5279 WHEN (UNIQ = 'N') THEN
5280 INTO OKS_INT_ERROR_STG_TEMP
5281 (CONCURRENT_REQUEST_ID
5282 ,HEADER_INTERFACE_ROWID
5283 ,INTERFACE_SOURCE_TABLE
5284 ,INTERFACE_ID
5285 ,ERROR_MSG
5286 ,MSG_TOKENS)
5287 VALUES (G_WORKER_REQ_ID
5288 ,HEADER_INTERFACE_ROWID
5289 ,'OKS_SALES_CREDITS_INTERFACE'
5290 ,SALES_CREDIT_INTERFACE_ID
5291 ,'OKS_IMP_SC_SP_SCT_NUNIQ'
5292 ,NULL)
5293 SELECT OSC.SALES_CREDIT_INTERFACE_ID SALES_CREDIT_INTERFACE_ID
5294 ,OSC.HEADER_INTERFACE_ID HEADER_INTERFACE_ID
5295 ,HDR.HEADER_INTERFACE_ROWID HEADER_INTERFACE_ROWID
5296 ,NAMEID_Q.SALESPERSON_ID SALESPERSON_ID
5297 ,(CASE WHEN OSC.SALESGROUP_ID IS NULL OR
5298 (OSC.SALESGROUP_ID <> -1
5299 AND NOT EXISTS (SELECT 'X'
5300 FROM JTF_RS_GROUP_MEMBERS MEM
5301 ,JTF_RS_SALESREPS SRP
5302 ,JTF_RS_GROUP_USAGES USG
5303 WHERE SRP.RESOURCE_ID = MEM.RESOURCE_ID
5304 AND MEM.GROUP_ID = USG.GROUP_ID
5305 AND USG.USAGE = 'SALES'
5306 AND SRP.SALESREP_ID = NAMEID_Q.SALESPERSON_ID
5307 AND SRP.ORG_ID = HDR.OPERATING_UNIT_ID
5308 AND MEM.GROUP_ID = OSC.SALESGROUP_ID))
5309 THEN 'N' ELSE 'Y' END) SALESGROUP_V
5310 ,OSC.SALESGROUP_ID SALESGROUP_ID
5311 ,(CASE WHEN COUNT(DISTINCT SCT.ROWID) OVER (PARTITION BY OSC.ROWID) = 1
5312 THEN MAX(SCT.SALES_CREDIT_TYPE_ID) OVER (PARTITION BY OSC.ROWID) ELSE NULL END) SALES_CREDIT_TYPE_ID
5313 ,(CASE WHEN COUNT(DISTINCT SCT.ROWID) OVER (PARTITION BY OSC.ROWID) = 1
5314 THEN MAX(SCT.QUOTA_FLAG) OVER (PARTITION BY OSC.ROWID) ELSE NULL END) QUOTA_FLAG
5315 , SUM(OSC.PERCENT) OVER (PARTITION BY OSC.HEADER_INTERFACE_ID, SCT.QUOTA_FLAG) PERCENT_SUM
5316 , OSC.PERCENT PERCENT
5317 ,(CASE WHEN EXISTS (SELECT 'X' FROM OKS_SALES_CREDITS_INTERFACE
5318 WHERE SALES_CREDIT_INTERFACE_ID <> OSC.SALES_CREDIT_INTERFACE_ID
5319 AND HEADER_INTERFACE_ID = OSC.HEADER_INTERFACE_ID
5320 AND (SALESPERSON_ID = OSC.SALESPERSON_ID OR SALESPERSON_NAME = OSC.SALESPERSON_NAME)
5321 AND SALES_CREDIT_TYPE = OSC.SALES_CREDIT_TYPE)
5322 THEN 'N' ELSE 'Y' END) UNIQ
5323 FROM OKS_SALES_CREDITS_INTERFACE OSC
5324 ,OKS_INT_HEADER_STG_TEMP HDR
5325 ,OE_SALES_CREDIT_TYPES SCT
5326 ,(SELECT distinct(OSCI.SALES_CREDIT_INTERFACE_ID) -- to avoid duplicate joins with tables when some names match with multiple records
5327 ,(CASE WHEN COUNT(DISTINCT SPI.ROWID) OVER (PARTITION BY OSCI.ROW_ID) = 1
5328 THEN MAX(OSCI.SALESPERSON_ID) OVER (PARTITION BY OSCI.ROW_ID) -- Sales Person Id Based on Id
5329 WHEN COUNT(DISTINCT SPN.ROW_ID) OVER (PARTITION BY OSCI.ROW_ID) = 1
5330 THEN MAX(SPN.SALESREP_ID) OVER (PARTITION BY OSCI.ROW_ID) -- Sales Person Id Based on Name
5331 ELSE NULL
5332 END) SALESPERSON_ID
5333 FROM (SELECT SCI.ROWID ROW_ID, SCI.SALES_CREDIT_INTERFACE_ID, SCI.SALESPERSON_NAME, SCI.SALESPERSON_ID,
5334 SCI.SALESGROUP_ID, SCI.SALES_CREDIT_TYPE, SCI.PERCENT, HST.OPERATING_UNIT_ID
5335 FROM OKS_SALES_CREDITS_INTERFACE SCI, OKS_INT_HEADER_STG_TEMP HST
5336 WHERE SCI.HEADER_INTERFACE_ID = HST.HEADER_INTERFACE_ID) OSCI
5337 ,JTF_RS_SALESREPS SPI
5338 ,(SELECT SP.ROWID ROW_ID, SPTL.RESOURCE_NAME, SP.ORG_ID, SP.SALESREP_ID
5339 FROM JTF_RS_RESOURCE_EXTNS_TL SPTL, JTF_RS_SALESREPS SP
5340 WHERE SP.RESOURCE_ID = SPTL.RESOURCE_ID
5341 AND SPTL.LANGUAGE = USERENV('LANG')) SPN
5342 WHERE OSCI.SALESPERSON_ID = SPI.SALESREP_ID (+)
5343 AND OSCI.OPERATING_UNIT_ID = SPI.ORG_ID (+)
5344 AND OSCI.SALESPERSON_NAME = SPN.RESOURCE_NAME (+)
5345 AND OSCI.OPERATING_UNIT_ID = SPN.ORG_ID (+)) NAMEID_Q
5346 WHERE OSC.SALES_CREDIT_INTERFACE_ID = NAMEID_Q.SALES_CREDIT_INTERFACE_ID
5347 AND OSC.HEADER_INTERFACE_ID = HDR.HEADER_INTERFACE_ID
5348 AND OSC.SALES_CREDIT_TYPE = SCT.NAME (+);
5349
5350 IF G_STMT_LOG THEN
5351
5352 SELECT count(1) INTO l_int_count FROM OKS_SALES_CREDITS_INTERFACE OSCI
5353 WHERE EXISTS (SELECT 'X' FROM OKS_INT_HEADER_STG_TEMP
5354 WHERE HEADER_INTERFACE_ID = OSCI.HEADER_INTERFACE_ID);
5355
5356 SELECT count(1) INTO l_stg_count FROM OKS_INT_SALES_CREDIT_STG_TEMP;
5357
5358 fnd_log.string(fnd_log.level_statement,
5359 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
5360 'Number of records in interface table being processed = ' || l_int_count ||', '||
5361 'Number of records inserted into staging table = '|| l_stg_count);
5362 END IF;
5363
5364 IF G_PROCEDURE_LOG THEN
5365 fnd_log.string(fnd_log.level_procedure,
5366 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
5367 'Exit.');
5368 END IF;
5369 EXCEPTION
5370 WHEN FND_API.G_EXC_ERROR THEN
5371 RAISE FND_API.G_EXC_ERROR;
5372 WHEN OTHERS THEN
5373 FND_MESSAGE.Set_Name('OKS', 'OKS_IMPORT_UNEXPECTED');
5374 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
5375 FND_MESSAGE.set_token('MESSAGE', 'stmt_num '||l_stmt_num||' ('||SQLCODE||') '||SQLERRM);
5376 FND_MSG_PUB.Add;
5377 RAISE FND_API.G_EXC_ERROR;
5378 END Validate_Sales_Credits;
5379
5380 --========================================================================
5381 -- PROCEDURE : Validate_notes PRIVATE
5382 -- PARAMETERS:
5383 -- COMMENT : This procedure will perform the validation needed
5384 -- on the notes interface records.
5385 --=========================================================================
5386
5387 PROCEDURE Validate_notes
5388 IS
5389 l_stmt_num NUMBER := 0;
5390 l_routine CONSTANT VARCHAR2(30) := 'Validate_notes';
5391 l_header_notes_count NUMBER := 0;
5392 l_line_notes_count NUMBER := 0;
5393 BEGIN
5394 IF G_PROCEDURE_LOG THEN
5395 fnd_log.string(fnd_log.level_procedure,
5396 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
5397 'Begin');
5398 END IF;
5399
5400 l_stmt_num := 10;
5401
5402 INSERT ALL
5403 WHEN (NOTE_STATUS_VALID ='N' ) THEN
5404 INTO OKS_INT_ERROR_STG_TEMP
5405 (CONCURRENT_REQUEST_ID
5406 ,HEADER_INTERFACE_ROWID
5407 ,INTERFACE_SOURCE_TABLE
5408 ,INTERFACE_ID
5409 ,ERROR_MSG
5410 ,MSG_TOKENS)
5411 VALUES (G_WORKER_REQ_ID
5412 ,HEADER_INTERFACE_ROWID
5413 ,'OKS_NOTES_INTERFACE'
5414 ,NOTES_INTERFACE_ID
5415 ,'OKS_IMP_NTINT_INVALID_NTSTAT'
5416 ,NULL)
5417 WHEN (NOTE_TYPE_VALID='N') THEN
5418 INTO OKS_INT_ERROR_STG_TEMP
5419 (CONCURRENT_REQUEST_ID
5420 ,HEADER_INTERFACE_ROWID
5421 ,INTERFACE_SOURCE_TABLE
5422 ,INTERFACE_ID
5423 ,ERROR_MSG
5424 ,MSG_TOKENS)
5425 VALUES (G_WORKER_REQ_ID
5426 ,HEADER_INTERFACE_ROWID
5427 ,'OKS_NOTES_INTERFACE'
5428 ,NOTES_INTERFACE_ID
5429 ,'OKS_IMP_NTINT_INVALID_NTTYPE'
5430 ,NULL)
5431 WHEN (ENTERED_BY_VALID ='N') THEN
5432 INTO OKS_INT_ERROR_STG_TEMP
5433 (CONCURRENT_REQUEST_ID
5434 ,HEADER_INTERFACE_ROWID
5435 ,INTERFACE_SOURCE_TABLE
5436 ,INTERFACE_ID
5437 ,ERROR_MSG
5438 ,MSG_TOKENS)
5439 VALUES (G_WORKER_REQ_ID
5440 ,HEADER_INTERFACE_ROWID
5441 ,'OKS_NOTES_INTERFACE'
5442 ,NOTES_INTERFACE_ID
5443 ,'OKS_IMP_NTINT_INVALID_USR'
5444 ,NULL)
5445 WHEN (ENTERED_DT_VALID ='N') THEN
5446 INTO OKS_INT_ERROR_STG_TEMP
5447 (CONCURRENT_REQUEST_ID
5448 ,HEADER_INTERFACE_ROWID
5449 ,INTERFACE_SOURCE_TABLE
5450 ,INTERFACE_ID
5451 ,ERROR_MSG
5452 ,MSG_TOKENS)
5453 VALUES (G_WORKER_REQ_ID
5454 ,HEADER_INTERFACE_ROWID
5455 ,'OKS_NOTES_INTERFACE'
5456 ,NOTES_INTERFACE_ID
5457 ,'OKS_IMP_NTINT_INVALID_ENTDT'
5458 ,NULL)
5459 WHEN (NOTES IS NULL ) THEN
5460 INTO OKS_INT_ERROR_STG_TEMP
5461 (CONCURRENT_REQUEST_ID
5462 ,HEADER_INTERFACE_ROWID
5463 ,INTERFACE_SOURCE_TABLE
5464 ,INTERFACE_ID
5465 ,ERROR_MSG
5466 ,MSG_TOKENS)
5467 VALUES (G_WORKER_REQ_ID
5468 ,HEADER_INTERFACE_ROWID
5469 ,'OKS_NOTES_INTERFACE'
5470 ,NOTES_INTERFACE_ID
5471 ,'OKS_IMP_NTINT_INVALID_NOTES'
5472 ,NULL)
5473
5474 SELECT ONI.NOTES_INTERFACE_ID NOTES_INTERFACE_ID
5475 ,ONI.HEADER_INTERFACE_ID HEADER_INTERFACE_ID
5476 ,OHSTG.HEADER_INTERFACE_ROWID
5477 ,ONI.LINE_INTERFACE_ID LINE_INTERFACE_ID
5478 ,RTRIM(ONI.NOTES) NOTES
5479 ,ONI.NOTES_DETAIL NOTES_DETAIL
5480 ,ONI.NOTE_STATUS NOTE_STATUS
5481 ,(CASE WHEN NOTE_STATUS IS NULL THEN 'N'
5482 WHEN COUNT(DISTINCT NTSTATUS.ROWID) OVER (PARTITION BY ONI.ROWID) <> 1 THEN 'N'
5483 ELSE 'Y'
5484 END) NOTE_STATUS_VALID
5485 ,ONI.NOTE_TYPE NOTE_TYPE
5486 ,(CASE WHEN NOTE_TYPE IS NULL THEN 'N'
5487 WHEN COUNT(DISTINCT NTTYPE.ROWID) OVER (PARTITION BY ONI.ROWID) <>1 THEN 'N'
5488 ELSE 'Y'
5489 END) NOTE_TYPE_VALID
5490 ,ONI.ENTERED_BY ENTERED_BY
5491 ,(CASE WHEN COUNT(DISTINCT USR.ROWID) OVER (PARTITION BY ONI.ROWID) <>1 THEN 'N'
5492 ELSE 'Y'
5493 END) ENTERED_BY_VALID
5494 ,ONI.ENTERED_DATE ENTERED_DATE
5495 ,(CASE WHEN ONI.ENTERED_DATE > SYSDATE THEN 'N'
5496 ELSE 'Y'
5497 END) ENTERED_DT_VALID
5498
5499 FROM OKS_NOTES_INTERFACE ONI
5500 ,OKS_INT_HEADER_STG_TEMP OHSTG
5501 ,FND_LOOKUPS NTSTATUS
5502 ,FND_LOOKUPS NTTYPE
5503 ,FND_USER USR
5504
5505 WHERE ONI.HEADER_INTERFACE_ID = OHSTG.HEADER_INTERFACE_ID
5506 AND ONI.LINE_INTERFACE_ID IS NULL
5507 AND ONI.NOTE_STATUS =NTSTATUS.LOOKUP_CODE (+)
5508 AND NTSTATUS.LOOKUP_TYPE (+) ='JTF_NOTE_STATUS'
5509 AND ONI.NOTE_TYPE = NTTYPE.LOOKUP_CODE (+)
5510 AND NTTYPE.LOOKUP_TYPE (+) ='JTF_NOTE_TYPE'
5511 AND ONI.ENTERED_BY = USR.USER_ID (+);
5512
5513 l_stmt_num := 20;
5514
5515 INSERT ALL
5516 WHEN (NOTE_STATUS_VALID ='N' ) THEN
5517 INTO OKS_INT_ERROR_STG_TEMP
5518 (CONCURRENT_REQUEST_ID
5519 ,HEADER_INTERFACE_ROWID
5520 ,INTERFACE_SOURCE_TABLE
5521 ,INTERFACE_ID
5522 ,ERROR_MSG
5523 ,MSG_TOKENS)
5524 VALUES (G_WORKER_REQ_ID
5525 ,HEADER_INTERFACE_ROWID
5526 ,'OKS_NOTES_INTERFACE'
5527 ,NOTES_INTERFACE_ID
5528 ,'OKS_IMP_NTINT_INVALID_NTSTAT'
5529 ,NULL)
5530 WHEN (NOTE_TYPE_VALID='N') THEN
5531 INTO OKS_INT_ERROR_STG_TEMP
5532 (CONCURRENT_REQUEST_ID
5533 ,HEADER_INTERFACE_ROWID
5534 ,INTERFACE_SOURCE_TABLE
5535 ,INTERFACE_ID
5536 ,ERROR_MSG
5537 ,MSG_TOKENS)
5538 VALUES (G_WORKER_REQ_ID
5539 ,HEADER_INTERFACE_ROWID
5540 ,'OKS_NOTES_INTERFACE'
5541 ,NOTES_INTERFACE_ID
5542 ,'OKS_IMP_NTINT_INVALID_NTTYPE'
5543 ,NULL)
5544 WHEN (ENTERED_BY_VALID ='N') THEN
5545 INTO OKS_INT_ERROR_STG_TEMP
5546 (CONCURRENT_REQUEST_ID
5547 ,HEADER_INTERFACE_ROWID
5548 ,INTERFACE_SOURCE_TABLE
5549 ,INTERFACE_ID
5550 ,ERROR_MSG
5551 ,MSG_TOKENS)
5552 VALUES (G_WORKER_REQ_ID
5553 ,HEADER_INTERFACE_ROWID
5554 ,'OKS_NOTES_INTERFACE'
5555 ,NOTES_INTERFACE_ID
5556 ,'OKS_IMP_NTINT_INVALID_USR'
5557 ,NULL)
5558 WHEN (ENTERED_DT_VALID ='N') THEN
5559 INTO OKS_INT_ERROR_STG_TEMP
5560 (CONCURRENT_REQUEST_ID
5561 ,HEADER_INTERFACE_ROWID
5562 ,INTERFACE_SOURCE_TABLE
5563 ,INTERFACE_ID
5564 ,ERROR_MSG
5565 ,MSG_TOKENS)
5566 VALUES (G_WORKER_REQ_ID
5567 ,HEADER_INTERFACE_ROWID
5568 ,'OKS_NOTES_INTERFACE'
5569 ,NOTES_INTERFACE_ID
5570 ,'OKS_IMP_NTINT_INVALID_ENTDT'
5571 ,NULL)
5572 WHEN (NOTES IS NULL ) THEN
5573 INTO OKS_INT_ERROR_STG_TEMP
5574 (CONCURRENT_REQUEST_ID
5575 ,HEADER_INTERFACE_ROWID
5576 ,INTERFACE_SOURCE_TABLE
5577 ,INTERFACE_ID
5578 ,ERROR_MSG
5579 ,MSG_TOKENS)
5580 VALUES (G_WORKER_REQ_ID
5581 ,HEADER_INTERFACE_ROWID
5582 ,'OKS_NOTES_INTERFACE'
5583 ,NOTES_INTERFACE_ID
5584 ,'OKS_IMP_NTINT_INVALID_NOTES'
5585 ,NULL)
5586
5587 SELECT ONI.NOTES_INTERFACE_ID NOTES_INTERFACE_ID
5588 ,ONI.HEADER_INTERFACE_ID HEADER_INTERFACE_ID
5589 ,OLSTG.HEADER_INTERFACE_ROWID HEADER_INTERFACE_ROWID
5590 ,ONI.LINE_INTERFACE_ID LINE_INTERFACE_ID
5591 ,RTRIM(ONI.NOTES) NOTES
5592 ,ONI.NOTES_DETAIL NOTES_DETAIL
5593 ,ONI.NOTE_STATUS NOTE_STATUS
5594 ,(CASE WHEN NOTE_STATUS IS NULL THEN 'N'
5595 WHEN COUNT(DISTINCT NTSTATUS.ROWID) OVER (PARTITION BY ONI.ROWID) <> 1 THEN 'N'
5596 ELSE 'Y'
5597 END) NOTE_STATUS_VALID
5598 ,ONI.NOTE_TYPE NOTE_TYPE
5599 ,(CASE WHEN NOTE_TYPE IS NULL THEN 'N'
5600 WHEN COUNT(DISTINCT NTTYPE.ROWID) OVER (PARTITION BY ONI.ROWID) <>1 THEN 'N'
5601 ELSE 'Y'
5602 END) NOTE_TYPE_VALID
5603 ,ONI.ENTERED_BY ENTERED_BY
5604 ,(CASE WHEN COUNT(DISTINCT USR.ROWID) OVER (PARTITION BY ONI.ROWID) <>1 THEN 'N'
5605 ELSE 'Y'
5606 END) ENTERED_BY_VALID
5607 ,ONI.ENTERED_DATE ENTERED_DATE
5608 ,(CASE WHEN ONI.ENTERED_DATE > SYSDATE THEN 'N'
5609 ELSE 'Y'
5610 END) ENTERED_DT_VALID
5611
5612 FROM OKS_NOTES_INTERFACE ONI
5613 ,OKS_INT_LINE_STG_TEMP OLSTG
5614 ,FND_LOOKUPS NTSTATUS
5615 ,FND_LOOKUPS NTTYPE
5616 ,FND_USER USR
5617
5618 WHERE ONI.LINE_INTERFACE_ID = OLSTG.LINE_INTERFACE_ID
5619 AND ONI.NOTE_STATUS =NTSTATUS.LOOKUP_CODE (+)
5620 AND NTSTATUS.LOOKUP_TYPE (+) ='JTF_NOTE_STATUS'
5621 AND ONI.NOTE_TYPE = NTTYPE.LOOKUP_CODE (+)
5622 AND NTTYPE.LOOKUP_TYPE (+) ='JTF_NOTE_TYPE'
5623 AND ONI.ENTERED_BY = USR.USER_ID (+);
5624
5625
5626 IF G_STMT_LOG THEN
5627
5628 SELECT count(1) INTO l_line_notes_count FROM OKS_NOTES_INTERFACE ONI
5629 WHERE EXISTS (SELECT 'X' FROM OKS_INT_LINE_STG_TEMP
5630 WHERE LINE_INTERFACE_ID = ONI.LINE_INTERFACE_ID);
5631
5632 SELECT count(1) INTO l_header_notes_count FROM OKS_NOTES_INTERFACE ONI
5633 WHERE EXISTS (SELECT 'X' FROM OKS_INT_HEADER_STG_TEMP
5634 WHERE HEADER_INTERFACE_ID = ONI.HEADER_INTERFACE_ID)
5635 AND ONI.LINE_INTERFACE_ID IS NULL;
5636
5637 fnd_log.string(fnd_log.level_statement,
5638 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
5639 'Number of header notes = ' || l_header_notes_count ||', '||
5640 'Number of line notes = '|| l_line_notes_count);
5641 END IF;
5642
5643 IF G_PROCEDURE_LOG THEN
5644 fnd_log.string(fnd_log.level_procedure,
5645 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
5646 'Exit.');
5647 END IF;
5648 EXCEPTION
5649 WHEN FND_API.G_EXC_ERROR THEN
5650 RAISE FND_API.G_EXC_ERROR;
5651 WHEN OTHERS THEN
5652 FND_MESSAGE.Set_Name('OKS', 'OKS_IMPORT_UNEXPECTED');
5653 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
5654 FND_MESSAGE.set_token('MESSAGE', 'stmt_num '||l_stmt_num||' ('||SQLCODE||') '||SQLERRM);
5655 FND_MSG_PUB.Add;
5656 RAISE FND_API.G_EXC_ERROR;
5657 END Validate_notes;
5658
5659 --========================================================================
5660 -- PROCEDURE : Validate_Contracts PUBLIC
5661 -- PARAMETERS: P_batch_id IN Batch Id
5662 -- P_rowid_from IN AD worker start rowid
5663 -- P_rowid_to IN AD worker end rowid
5664 -- COMMENT : This procedure will perform the validation needed
5665 -- on the interface records before importing service contracts
5666 --=========================================================================
5667
5668 PROCEDURE Validate_Contracts(P_batch_id IN VARCHAR2,
5669 P_rowid_from IN ROWID,
5670 P_rowid_to IN ROWID)
5671 IS
5672 l_stmt_num NUMBER := 0;
5673 l_routine CONSTANT VARCHAR2(30) := 'Validate_Contracts';
5674 BEGIN
5675 IF G_PROCEDURE_LOG THEN
5676 fnd_log.string(fnd_log.level_procedure,
5677 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
5678 'Entering with '||
5679 'P_batch_id = '|| P_batch_id ||','||
5680 'P_rowid_from = '|| P_rowid_from ||','||
5681 'P_rowid_to = '|| P_rowid_to);
5682 END IF;
5683
5684 FND_MSG_PUB.initialize;
5685
5686 l_stmt_num := 10;
5687 /* delete from errors table all the records related to the contracts which are going to be validated by this worker.*/
5688 DELETE FROM OKS_IMP_ERRORS
5689 WHERE HEADER_INTERFACE_ID IN (SELECT HEADER_INTERFACE_ID
5690 FROM OKS_HEADERS_INTERFACE
5691 WHERE rowid between P_rowid_from and P_rowid_to
5692 AND (INTERFACE_STATUS IS NULL OR INTERFACE_STATUS = 'R'));
5693 l_stmt_num := 20;
5694 Validate_header (P_batch_id,
5695 P_rowid_from,
5696 P_rowid_to);
5697
5698 l_stmt_num := 30;
5699
5700 Validate_lines;
5701 Validate_covered_levels;
5702 Validate_usage_counters;
5703 Validate_Sales_Credits;
5704 Validate_notes;
5705
5706 IF G_PROCEDURE_LOG THEN
5707 fnd_log.string(fnd_log.level_procedure,
5708 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
5709 'Exit.');
5710 END IF;
5711 EXCEPTION
5712 WHEN FND_API.G_EXC_ERROR THEN
5713 -- ROLLBACK;
5714 RAISE FND_API.G_EXC_ERROR;
5715 WHEN OTHERS THEN
5716 -- ROLLBACK;
5717 FND_MESSAGE.Set_Name('OKS', 'OKS_IMPORT_UNEXPECTED');
5718 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
5719 FND_MESSAGE.set_token('MESSAGE', 'stmt_num '||l_stmt_num||' ('||SQLCODE||') '||SQLERRM);
5720 FND_MSG_PUB.Add;
5721 RAISE FND_API.G_EXC_ERROR;
5722 END Validate_Contracts;
5723
5724 END OKS_IMPORT_VALIDATE;