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