[Home] [Help]
PACKAGE BODY: APPS.OKS_IMPORT_TEST_INSERT
Source
1 PACKAGE BODY OKS_IMPORT_TEST_INSERT AS
2 -- $Header: OKSPKIMPUTB.pls 120.2 2008/06/06 12:23:39 cgopinee noship $
3 --+=======================================================================+
4 --| Copyright (c) 2003 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| OKSPKIMPUTB.pls Created By Vamshi Mutyala |
10 --| |
11 --| DESCRIPTION |
12 --| Service Contracts Import QA Test facilitator Package |
13 --| |
14 --+========================================================================
15
16 --===================
17 -- GLOBALS
18 --===================
19
20 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OKS_IMPORT_TEST_INSERT';
21
22 --========================================================================
23 -- PRIVATE CONSTANTS AND VARIABLES
24 --========================================================================
25 G_MODULE_NAME CONSTANT VARCHAR2(50) := 'oks.plsql.import.' || G_PKG_NAME;
26 G_WORKER_REQ_ID CONSTANT NUMBER := FND_GLOBAL.conc_request_id;
27 G_MODULE_HEAD CONSTANT VARCHAR2(60) := G_MODULE_NAME || '(Req Id = '||G_WORKER_REQ_ID||').';
28 G_LOG_LEVEL CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
29 G_UNEXPECTED_LOG CONSTANT BOOLEAN := fnd_log.level_unexpected >= G_LOG_LEVEL AND
30 fnd_log.TEST(fnd_log.level_unexpected, G_MODULE_HEAD);
31 G_ERROR_LOG CONSTANT BOOLEAN := G_UNEXPECTED_LOG AND fnd_log.level_error >= G_LOG_LEVEL;
32 G_EXCEPTION_LOG CONSTANT BOOLEAN := G_ERROR_LOG AND fnd_log.level_exception >= G_LOG_LEVEL;
33 G_EVENT_LOG CONSTANT BOOLEAN := G_EXCEPTION_LOG AND fnd_log.level_event >= G_LOG_LEVEL;
34 G_PROCEDURE_LOG CONSTANT BOOLEAN := G_EVENT_LOG AND fnd_log.level_procedure >= G_LOG_LEVEL;
35 G_STMT_LOG CONSTANT BOOLEAN := G_PROCEDURE_LOG AND fnd_log.level_statement >= G_LOG_LEVEL;
36
37 --=========================
38 -- PROCEDURES AND FUNCTIONS
39 --=========================
40 --========================================================================
41 -- PROCEDURE : Insert_Interface_Records PUBLIC
42 -- PARAMETERS: X_errbuf out Error message buffer
43 -- X_retcode out Return status code
44 -- p_contract_number IN Model contract number
45 -- p_num_scenarios IN Number of copies to be made
46 -- COMMENT : This procedure will insert records into Service Contracts
47 -- interface tables with data from model contract with unique
48 -- interface ids and modifier number.
49 --=========================================================================
50 PROCEDURE Insert_Interface_Records (X_errbuf OUT NOCOPY VARCHAR2,
51 X_retcode OUT NOCOPY VARCHAR2,
52 p_contract_number IN VARCHAR2,
53 p_contract_modifier IN VARCHAR2,
54 p_target_contract IN VARCHAR2,
55 p_num_scenarios IN NUMBER)
56 IS
57 l_stmt_num NUMBER := 0;
58 l_routine CONSTANT VARCHAR2(30) := 'Insert_Interface_Records';
59 l_batch_id NUMBER;
60 l_msg_data VARCHAR2(2000);
61 l_msg_count NUMBER;
62 l_norecords_exception EXCEPTION;
63 BEGIN
64
65 l_stmt_num := 10;
66
67 SELECT OKS_HEADERS_INT_BATCH_S.nextval into l_batch_id from dual;
68
69 l_stmt_num := 20;
70
71 INSERT INTO OKS_HEADERS_INTERFACE
72 (
73 HEADER_INTERFACE_ID
74 ,CONTRACT_NUMBER
75 ,CONTRACT_NUMBER_MODIFIER
76 ,CATEGORY
77 ,START_DATE
78 ,END_DATE
79 ,STATUS_CODE
80 ,KNOWN_AS
81 ,DESCRIPTION
82 ,OPERATING_UNIT_ID
83 ,CUSTOMER_PARTY_NAME
84 ,CUSTOMER_PARTY_NUMBER
85 ,CUSTOMER_PARTY_ID
86 ,THIRD_PARTY_NAME
87 ,THIRD_PARTY_NUMBER
88 ,THIRD_PARTY_ID
89 ,BILL_TO_SITE_USAGE_CODE
90 ,BILL_TO_SITE_USAGE_ID
91 ,SHIP_TO_SITE_USAGE_CODE
92 ,SHIP_TO_SITE_USAGE_ID
93 ,SALESPERSON_NAME
94 ,SALESPERSON_ID
95 ,SALESGROUP_ID
96 ,CUSTOMER_CONTRACT_ADMIN_NAME
97 ,CUSTOMER_CONTRACT_ADMIN_ID
98 ,CONTRACT_CURRENCY_CODE
99 ,CURRENCY_CONVERSION_TYPE
100 ,CURRENCY_CONVERSION_RATE
101 ,CURRENCY_CONVERSION_DATE
102 ,AGREEMENT_NAME
103 ,AGREEMENT_ID
104 ,PRICE_LIST_NAME
105 ,PRICE_LIST_ID
106 ,PAYMENT_TERMS_NAME
107 ,PAYMENT_TERMS_ID
108 ,PAYMENT_INSTRUCTION
109 ,PO_REQUIRED
110 ,PAYMENT_INSTRUCTION_DETAILS
111 ,PAYMENT_METHOD_CODE
112 ,COMMITMENT_ID
113 ,TAX_EXEMPTION_CONTROL
114 ,TAX_EXEMPTION_NUMBER
115 ,EXEMPT_REASON_CODE
116 ,BILL_SERVICES
117 ,BILLING_TRANSACTION_TYPE_ID
118 ,ACCOUNTING_RULE_NAME
119 ,ACCOUNTING_RULE_ID
120 ,INVOICING_RULE_NAME
121 ,INVOICING_RULE_ID
122 ,HOLD_CREDITS
123 ,SUMMARY_PRINT
124 ,SUMMARY_TRANSACTIONS
125 ,SERVICE_CHRG_PREPAY_REQ
126 ,SERVICE_CHARGES_PO_REQUIRED
127 ,SERVICE_CHARGES_PO_NUMBER
128 ,RENEWAL_PROCESS
129 ,APPROVAL_REQUIRED
130 ,RENEW_UP_TO
131 ,PRICING_METHOD
132 ,RENEWAL_PRICE_LIST_NAME
133 ,RENEWAL_PRICE_LIST_ID
134 ,RENEWAL_MARKUP
135 ,RENEWAL_BILLING_PROFILE_ID
136 ,RENEWAL_PO_NUMBER
137 ,RENEWAL_PO_REQUIRED
138 ,RENEWAL_GRACE_DURATION
139 ,RENEWAL_GRACE_PERIOD
140 ,RENEWAL_ESTIMATED_PERCENT
141 ,RENEWAL_ESTIMATED_DURATION
142 ,RENEWAL_ESTIMATED_PERIOD
143 ,QUOTE_TO_PARTY_SITE
144 ,QUOTE_TO_CONTACT
145 ,QUOTE_TO_PHONE
146 ,QUOTE_TO_FAX
147 ,QUOTE_TO_EMAIL
148 ,DATE_APPROVED
149 ,DATE_SIGNED
150 ,DATE_CANCELED
151 ,CANCELLATION_REASON
152 ,GRACE_DURATION
153 ,GRACE_PERIOD
154 ,ESTIMATION_PERCENT
155 ,ESTIMATION_DATE
156 ,FOLLOW_UP_DUE_DATE
157 ,FOLLOW_UP_ACTION
158 ,QA_CHECKLIST
159 ,CONTRACT_GROUP_NAME
160 ,CONTRACT_GROUP_ID
161 ,APPROVAL_PROCESS_ID
162 ,FULLY_BILLED
163 ,INTERFACE_STATUS
164 ,INV_ORGANIZATION_ID
165 ,BATCH_ID
166 ,SOURCE
167 ,DOCUMENT
168 )
169 SELECT OKS_HEADERS_INT_ID_S.nextval AS HEADER_INTERFACE_ID
170 ,p_target_contract AS CONTRACT_NUMBER
171 ,NUM_VIEW.CONTRACT_NUMBER_MODIFIER AS CONTRACT_NUMBER_MODIFIER
172 ,H.SCS_CODE AS CATEGORY
173 ,H.START_DATE AS START_DATE
174 ,H.END_DATE AS END_DATE
175 ,H.STS_CODE AS STATUS_CODE
176 ,HL.COGNOMEN AS KNOWN_AS
177 ,HL.SHORT_DESCRIPTION AS DESCRIPTION
178 ,H.ORG_ID AS OPERATING_UNIT_ID
179 ,HZP.PARTY_NAME AS CUSTOMER_PARTY_NAME
180 ,HZP.PARTY_NUMBER AS CUSTOMER_PARTY_NUMBER
181 ,HZP.PARTY_ID AS CUSTOMER_PARTY_ID
182 ,HZP3.PARTY_NAME AS THIRD_PARTY_NAME
183 ,HZP3.PARTY_NUMBER AS THIRD_PARTY_NUMBER
184 ,HZP3.PARTY_ID AS THIRD_PARTY_ID
185 ,BSU.LOCATION AS BILL_TO_SITE_USAGE_CODE
186 ,H.BILL_TO_SITE_USE_ID AS BILL_TO_SITE_USAGE_ID
187 ,SSU.LOCATION AS SHIP_TO_SITE_USAGE_CODE
188 ,H.SHIP_TO_SITE_USE_ID AS SHIP_TO_SITE_USAGE_ID
189 ,SLSREPNAME.RESOURCE_NAME AS SALESPERSON_NAME
190 ,SLSREPID.SALESREP_ID AS SALESPERSON_ID
191 ,SLSREP.SALES_GROUP_ID AS SALESGROUP_ID
192 ,CONADMNNAME.NAME AS CUSTOMER_CONTRACT_ADMIN_NAME
193 ,CONADM.OBJECT1_ID1 AS CUSTOMER_CONTRACT_ADMIN_ID
194 ,H.CURRENCY_CODE AS CONTRACT_CURRENCY_CODE
195 ,H.CONVERSION_TYPE AS CURRENCY_CONVERSION_TYPE
196 ,H.CONVERSION_RATE AS CURRENCY_CONVERSION_RATE
197 ,H.CONVERSION_RATE_DATE AS CURRENCY_CONVERSION_DATE
198 ,AGRNAME.NAME AS AGREEMENT_NAME
199 ,AGRID.ISA_AGREEMENT_ID AS AGREEMENT_ID
200 ,PRLNAME.NAME AS PRICE_LIST_NAME
201 ,H.PRICE_LIST_ID AS PRICE_LIST_ID
202 ,PAYTERMNAME.NAME AS PAYMENT_TERMS_NAME
203 ,H.PAYMENT_TERM_ID AS PAYMENT_TERMS_ID
204 ,H.PAYMENT_INSTRUCTION_TYPE AS PAYMENT_INSTRUCTION
205 ,H.CUST_PO_NUMBER_REQ_YN AS PO_REQUIRED
206 ,H.CUST_PO_NUMBER AS PAYMENT_INSTRUCTION_DETAILS
207 ,HS.PAYMENT_TYPE AS PAYMENT_METHOD_CODE
208 ,HS.COMMITMENT_ID AS COMMITMENT_ID
209 ,HS.TAX_STATUS AS TAX_EXEMPTION_CONTROL
210 ,HS.EXEMPT_CERTIFICATE_NUMBER AS TAX_EXEMPTION_NUMBER
211 ,HS.EXEMPT_REASON_CODE AS EXEMPT_REASON_CODE
212 ,HS.AR_INTERFACE_YN AS BILL_SERVICES
213 ,HS.INV_TRX_TYPE AS BILLING_TRANSACTION_TYPE_ID
214 ,nvl(ACCTRULE.NAME, 'Immediate') AS ACCOUNTING_RULE_NAME
215 ,ACCTRULE.ID1 AS ACCOUNTING_RULE_ID
216 ,nvl(INVRULE.NAME, 'Advance Invoice') AS INVOICING_RULE_NAME
217 ,INVRULE.ID1 AS INVOICING_RULE_ID
218 ,HS.HOLD_BILLING AS HOLD_CREDITS
219 ,HS.INV_PRINT_PROFILE AS SUMMARY_PRINT
220 ,HS.SUMMARY_TRX_YN AS SUMMARY_TRANSACTIONS
221 ,H.PRE_PAY_REQ_YN AS SERVICE_CHRG_PREPAY_REQ
222 ,HS.SERVICE_PO_REQUIRED AS SERVICE_CHARGES_PO_REQUIRED
223 ,HS.SERVICE_PO_NUMBER AS SERVICE_CHARGES_PO_NUMBER
224 ,H.RENEWAL_TYPE_CODE AS RENEWAL_PROCESS
225 ,H.APPROVAL_TYPE AS APPROVAL_REQUIRED
226 ,H.RENEWAL_END_DATE AS RENEW_UP_TO
227 ,HS.RENEWAL_PRICING_TYPE AS RENEWAL_PRICING_METHOD
228 ,RENPRLLST.NAME AS RENEWAL_PRICE_LIST_NAME
229 ,HS.RENEWAL_PRICE_LIST AS RENEWAL_PRICE_LIST_ID
230 ,HS.RENEWAL_MARKUP_PERCENT AS RENEWAL_MARKUP
231 ,HS.BILLING_PROFILE_ID AS RENEWAL_BILLING_PROFILE_ID
232 ,HS.RENEWAL_PO_NUMBER AS RENEWAL_PO_NUMBER
233 ,HS.RENEWAL_PO_REQUIRED AS RENEWAL_PO_REQUIRED
234 ,HS.RENEWAL_GRACE_DURATION AS RENEWAL_GRACE_DURATION
235 ,HS.RENEWAL_GRACE_PERIOD AS RENEWAL_GRACE_PERIOD
236 ,HS.RENEWAL_EST_REV_PERCENT AS RENEWAL_ESTIMATED_PERCENT
237 ,HS.RENEWAL_EST_REV_DURATION AS RENEWAL_ESTIMATED_DURATION
238 ,HS.RENEWAL_EST_REV_PERIOD AS RENEWAL_ESTIMATED_PERIOD
239 ,HS.QUOTE_TO_SITE_ID AS QUOTE_TO_PARTY_SITE
240 ,HS.QUOTE_TO_CONTACT_ID AS QUOTE_TO_CONTACT
241 ,HS.QUOTE_TO_PHONE_ID AS QUOTE_TO_PHONE
242 ,HS.QUOTE_TO_FAX_ID AS QUOTE_TO_FAX
243 ,HS.QUOTE_TO_EMAIL_ID AS QUOTE_TO_EMAIL
244 ,H.DATE_APPROVED AS DATE_APPROVED
245 ,H.DATE_SIGNED AS DATE_SIGNED
246 ,H.DATETIME_CANCELLED AS DATE_CANCELED
247 ,H.TRN_CODE AS CANCELLATION_REASON
248 ,HS.GRACE_DURATION AS GRACE_DURATION
249 ,HS.GRACE_PERIOD AS GRACE_PERIOD
250 ,HS.EST_REV_PERCENT AS ESTIMATION_PERCENT
251 ,HS.EST_REV_DATE AS ESTIMATION_DATE
252 ,HS.FOLLOW_UP_DATE AS FOLLOW_UP_DUE_DATE
253 ,HS.FOLLOW_UP_ACTION AS FOLLOW_UP_ACTION
254 ,H.QCL_ID AS QA_CHECKLIST
255 ,GRTL.NAME AS CONTRACT_GROUP_NAME
256 ,GRTL.ID AS CONTRACT_GROUP_ID
257 ,KPROCS.PDF_ID AS APPROVAL_PROCESS_ID
258 ,'N' AS FULLY_BILLED
259 ,NULL AS INTERFACE_STATUS
260 ,H.INV_ORGANIZATION_ID AS INV_ORGANIZATION_ID
261 ,l_batch_id
262 ,'OKS_IMPORT'
263 ,'Doc'||l_batch_id
264 FROM
265 OKC_K_HEADERS_ALL_B H
266 ,OKS_K_HEADERS_B HS
267 ,OKC_K_HEADERS_TL HL
268 ,OKC_K_PARTY_ROLES_B PTY
269 ,HZ_PARTIES HZP
270 ,OKC_K_PARTY_ROLES_B PTY3
271 ,HZ_PARTIES HZP3
272 ,HZ_CUST_SITE_USES_ALL BSU
273 ,HZ_CUST_SITE_USES_ALL SSU
274 ,OKC_K_PARTY_ROLES_B VNDRPTY
275 ,OKC_CONTACTS SLSREP
276 ,JTF_RS_SALESREPS SLSREPID
277 ,JTF_RS_RESOURCE_EXTNS_VL SLSREPNAME
278 ,OKC_CONTACTS CONADM
279 ,OKX_PARTY_CONTACTS_V CONADMNNAME
280 ,OE_AGREEMENTS_VL AGRNAME
281 ,OKC_GOVERNANCES AGRID
282 ,QP_LIST_HEADERS_VL PRLNAME
283 ,RA_TERMS_TL PAYTERMNAME
284 ,OKX_RULES_V ACCTRULE
285 ,OKX_RULES_V INVRULE
286 ,QP_LIST_HEADERS_VL RENPRLLST
287 ,OKC_K_GROUPS_TL GRTL
288 ,OKC_K_GRPINGS GRPNG
289 ,OKC_K_PROCESSES KPROCS
290 ,(SELECT ROWNUM AS CONTRACT_NUMBER_MODIFIER FROM DUAL CONNECT BY LEVEL <=p_num_scenarios) NUM_VIEW
291 WHERE
292 H.ID = HS.CHR_ID
293 AND H.ID = HL.ID
294 AND HL.LANGUAGE = USERENV('LANG')
295 AND H.ID = PTY.CHR_ID
296 AND PTY.OBJECT1_ID1 = HZP.PARTY_ID
297 AND PTY.JTOT_OBJECT1_CODE = 'OKX_PARTY'
298 AND PTY.RLE_CODE IN ('CUSTOMER','SUBSCRIBER')
299 AND H.ID = PTY3.CHR_ID(+)
300 AND PTY3.OBJECT1_ID1 = HZP3.PARTY_ID(+)
301 AND PTY3.JTOT_OBJECT1_CODE(+) = 'OKX_PARTY'
302 AND PTY3.RLE_CODE(+) = 'THIRD_PARTY'
303 AND H.BILL_TO_SITE_USE_ID = BSU.SITE_USE_ID
304 AND H.SHIP_TO_SITE_USE_ID = SSU.SITE_USE_ID
305 AND H.ID = VNDRPTY.CHR_ID
306 AND VNDRPTY.RLE_CODE IN ('VENDOR','MERCHANT')
307 AND VNDRPTY.ID = SLSREP.CPL_ID
308 AND SLSREP.CRO_CODE = 'SALESPERSON'
309 AND SLSREP.OBJECT1_ID1 = SLSREPID.SALESREP_ID
310 AND SLSREPID.ORG_ID = H.AUTHORING_ORG_ID
311 AND SLSREPID.RESOURCE_ID = SLSREPNAME.RESOURCE_ID
312 AND PTY.ID = CONADM.CPL_ID(+)
313 AND CONADM.CRO_CODE(+) = 'ADMIN'
314 AND CONADM.OBJECT1_ID1 = CONADMNNAME.ID1(+)
315 AND CONADM.JTOT_OBJECT1_CODE(+) = 'OKX_PCONTACT'
316 AND H.ID = AGRID.CHR_ID(+)
317 AND AGRID.ISA_AGREEMENT_ID = AGRNAME.AGREEMENT_ID(+)
318 AND H.PRICE_LIST_ID = PRLNAME.LIST_HEADER_ID(+)
319 AND H.PAYMENT_TERM_ID = PAYTERMNAME.TERM_ID(+)
320 AND PAYTERMNAME.LANGUAGE (+) = USERENV('LANG')
321 AND HS.ACCT_RULE_ID = ACCTRULE.ID1 (+)
322 AND ACCTRULE.TYPE (+) = 'A'
323 AND H.INV_RULE_ID = INVRULE.ID1 (+)
324 AND INVRULE.TYPE (+) = 'I'
325 AND HS.RENEWAL_PRICE_LIST = RENPRLLST.LIST_HEADER_ID(+)
326 AND H.ID = GRPNG.INCLUDED_CHR_ID(+)
327 AND GRPNG.CGP_PARENT_ID = GRTL.ID(+)
328 AND GRTL.LANGUAGE(+) = USERENV('LANG')
329 AND H.ID = KPROCS.CHR_ID(+)
330 AND H.CONTRACT_NUMBER = p_contract_number
331 AND nvl(H.CONTRACT_NUMBER_MODIFIER, '1') = nvl(p_contract_modifier, '1');
332
333 IF SQL%ROWCOUNT = 0 THEN
334 RAISE l_norecords_exception;
335 END IF;
336
337 FND_FILE.put_line(FND_FILE.OUTPUT, 'Batch Id '|| l_batch_id ||' inserted into interface tables');
338
339 l_stmt_num := 30;
340
341 INSERT INTO OKS_LINES_INTERFACE
342 (
343 LINE_INTERFACE_ID
344 ,HEADER_INTERFACE_ID
345 ,LINE_NUMBER
346 ,LINE_TYPE
347 ,ITEM_ORGANIZATION_ID
348 ,ITEM_NAME
349 ,ITEM_ID
350 ,REFERENCE_TEMPLATE_ID
351 ,LINE_REFERENCE
352 ,STATUS_CODE
353 ,START_DATE
354 ,END_DATE
355 ,BILL_TO_SITE_USAGE_CODE
356 ,BILL_TO_SITE_USAGE_ID
357 ,SHIP_TO_SITE_USAGE_CODE
358 ,SHIP_TO_SITE_USAGE_ID
359 ,BILLING_CONTACT_NAME
360 ,BILLING_CONTACT_ID
361 ,SHIPPING_CONTACT_NAME
362 ,SHIPPING_CONTACT_ID
363 ,RENEWAL_TYPE_CODE
364 ,CANCELLATION_DATE
365 ,CANCELLATION_REASON
366 ,PRICE_LIST_NAME
367 ,PRICE_LIST_ID
368 ,INVOICE_TEXT
369 ,PRINT_INVOICE
370 ,SUBTOTAL
371 ,TAX_AMOUNT
372 ,TAX_EXEMPTION_CONTROL
373 ,TAX_EXEMPTION_NUMBER
374 ,EXEMPT_REASON_CODE
375 ,TAX_CLASSIFICATION_CODE
376 ,PAYMENT_INSTRUCTION
377 ,PO_REQUIRED
378 ,PAYMENT_INSTRUCTION_DETAILS
379 ,PAYMENT_METHOD_CODE
380 ,COMMITMENT_ID
381 ,ACCOUNTING_RULE_NAME
382 ,ACCOUNTING_RULE_ID
383 ,INVOICING_RULE_NAME
384 ,INVOICING_RULE_ID
385 ,RECUR_BILL_OCCURANCES
386 ,BILLING_INTERVAL_DURATION
387 ,BILLING_INTERVAL_PERIOD
388 ,FIRST_BILL_UPTO_DATE
389 ,SUBSCRIPTION_QUANTITY
390 ,QUANTITY_UOM
391 ,PRICE_UOM
392 ,UNIT_PRICE
393 ,FIRST_BILLED_AMOUNT
394 ,LAST_BILLED_AMOUNT
395 ,USAGE_TYPE
396 ,USAGE_PERIOD
397 ,AVERAGING_INTERVAL
398 ,SETTLEMENT_INTERVAL
399 ,USAGE_TERMINATION_METHOD
400 )
401 SELECT
402 OKS_LINES_INT_ID_S.nextval AS LINE_INTERFACE_ID
403 ,OHI.HEADER_INTERFACE_ID AS HEADER_INTERFACE_ID
404 ,LINE.LINE_NUMBER AS LINE_NUMBER
405 ,LTYST.LTY_CODE AS LINE_TYPE
406 ,MTL.ORGANIZATION_ID AS ITEM_ORGANIZATION_ID
407 ,MTL.CONCATENATED_SEGMENTS AS ITEM_NAME
408 ,MTL.INVENTORY_ITEM_ID AS ITEM_ID
409 ,MTL.COVERAGE_SCHEDULE_ID AS REFERENCE_TEMPLATE_ID
410 ,LINE.COGNOMEN AS LINE_REFERENCE
411 ,LINE.STS_CODE AS STATUS_CODE
412 ,LINE.START_DATE AS START_DATE
413 ,LINE.END_DATE AS END_DATE
414 ,BSU.LOCATION AS BILL_TO_SITE_USAGE_CODE
415 ,LINE.BILL_TO_SITE_USE_ID AS BILL_TO_SITE_USAGE_ID
416 ,SSU.LOCATION AS SHIP_TO_SITE_USAGE_CODE
417 ,LINE.SHIP_TO_SITE_USE_ID AS SHIP_TO_SITE_USAGE_ID
418 ,BILLTOCONTNAME.CONTACT_NAME AS BILLING_CONTACT_NAME
419 ,BILLTOCONT.OBJECT1_ID1 AS BILLING_CONTACT_ID
420 ,SHIPTOCONTNAME.CONTACT_NAME AS SHIPPING_CONTACT_NAME
421 ,SHIPTOCONT.OBJECT1_ID1 AS SHIPPING_CONTACT_ID
422 ,LINE.LINE_RENEWAL_TYPE_CODE AS RENEWAL_TYPE_CODE
423 ,LINE.DATE_CANCELLED AS CANCELLATION_DATE
424 ,LINE.TRN_CODE AS CANCELLATION_REASON
425 ,PRLNAME.NAME AS PRICE_LIST_NAME
426 ,LINE.PRICE_LIST_ID AS PRICE_LIST_ID
427 ,LS.INVOICE_TEXT AS INVOICE_TEXT
428 ,LS.INV_PRINT_FLAG AS PRINT_INVOICE
429 ,LINE.PRICE_NEGOTIATED AS SUBTOTAL
430 ,LS.TAX_AMOUNT AS TAX_AMOUNT
431 ,LS.TAX_STATUS AS TAX_EXEMPTION_CONTROL
432 ,LS.EXEMPT_CERTIFICATE_NUMBER AS TAX_EXEMPTION_NUMBER
433 ,LS.EXEMPT_REASON_CODE AS EXEMPT_REASON_CODE
434 ,LS.TAX_CLASSIFICATION_CODE AS TAX_CLASSIFICATION_CODE
435 ,LINE.PAYMENT_INSTRUCTION_TYPE AS PAYMENT_INSTRUCTION
436 ,LS.CUST_PO_NUMBER_REQ_YN AS PO_REQUIRED
437 ,LS.CUST_PO_NUMBER AS PAYMENT_INSTRUCTION_DETAILS
438 ,LS.PAYMENT_TYPE AS PAYMENT_METHOD_CODE
439 ,LS.COMMITMENT_ID AS COMMITMENT_ID
440 ,nvl(ACCTRULE.NAME, 'Immediate') AS ACCOUNTING_RULE_NAME
441 ,ACCTRULE.ID1 AS ACCOUNTING_RULE_ID
442 ,nvl(INVRULE.NAME, 'Advance Invoice') AS INVOICING_RULE_NAME
443 ,INVRULE.ID1 AS INVOICING_RULE_ID
444 ,decode(LTYST.LTY_CODE, 'WARRANTY', NULL,12) AS RECUR_BILL_OCCURANCES
445 ,decode(LTYST.LTY_CODE, 'WARRANTY', NULL,12) AS BILLING_INTERVAL_DURATION
446 ,decode(LTYST.LTY_CODE, 'WARRANTY', NULL,'MTH') AS BILLING_INTERVAL_PERIOD
447 ,(LINE.START_DATE + 1) AS FIRST_BILL_UPTO_DATE
448 ,DECODE(LINE.LSE_ID,46,KITEMS.NUMBER_OF_ITEMS,NULL) AS SUBSCRIPTION_QUANTITY
449 ,DECODE(LINE.LSE_ID,46,KITEMS.UOM_CODE,NULL) AS QUANTITY_UOM
450 ,LS.PRICE_UOM AS PRICE_UOM
451 ,LINE.PRICE_UNIT AS UNIT_PRICE
452 ,20 AS FIRST_BILLED_AMOUNT
453 ,10 AS LAST_BILLED_AMOUNT
454 ,LS.USAGE_TYPE AS USAGE_TYPE
455 ,LS.USAGE_PERIOD AS USAGE_PERIOD
456 ,LS.AVERAGING_INTERVAL AS AVERAGING_INTERVAL
457 ,LS.SETTLEMENT_INTERVAL AS SETTLEMENT_INTERVAL
458 ,LS.TERMN_METHOD AS USAGE_TERMINATION_METHOD
459 FROM
460 OKC_K_HEADERS_ALL_B H
461 ,OKC_K_LINES_V LINE
462 ,OKS_K_LINES_V LS
463 ,OKC_LINE_STYLES_V LTYST
464 ,OKC_K_ITEMS KITEMS
465 ,MTL_SYSTEM_ITEMS_B_KFV MTL
466 ,HZ_CUST_SITE_USES_ALL BSU
467 ,HZ_CUST_SITE_USES_ALL SSU
468 ,OKC_K_PARTY_ROLES_B PTY
469 ,OKC_CONTACTS BILLTOCONT
470 ,OKC_CONTACTS SHIPTOCONT
471 ,(SELECT CAR.CUST_ACCOUNT_ROLE_ID CONTACT_ID, P.PARTY_NAME CONTACT_NAME, R.OBJECT_ID PARTY_ID
472 FROM HZ_CUST_ACCOUNT_ROLES CAR,HZ_PARTIES P, HZ_RELATIONSHIPS R , HZ_ORG_CONTACTS OC
473 WHERE CAR.ROLE_TYPE = 'CONTACT'
474 AND R.PARTY_ID = CAR.PARTY_ID
475 AND R.CONTENT_SOURCE_TYPE = 'USER_ENTERED'
476 AND P.PARTY_ID = R.SUBJECT_ID
477 AND OC.PARTY_RELATIONSHIP_ID = R.RELATIONSHIP_ID
478 AND R.DIRECTIONAL_FLAG = 'F') BILLTOCONTNAME
479 ,(SELECT CAR.CUST_ACCOUNT_ROLE_ID CONTACT_ID, P.PARTY_NAME CONTACT_NAME, R.OBJECT_ID PARTY_ID
480 FROM HZ_CUST_ACCOUNT_ROLES CAR,HZ_PARTIES P, HZ_RELATIONSHIPS R , HZ_ORG_CONTACTS OC
481 WHERE CAR.ROLE_TYPE = 'CONTACT'
482 AND R.PARTY_ID = CAR.PARTY_ID
483 AND R.CONTENT_SOURCE_TYPE = 'USER_ENTERED'
484 AND P.PARTY_ID = R.SUBJECT_ID
485 AND OC.PARTY_RELATIONSHIP_ID = R.RELATIONSHIP_ID
486 AND R.DIRECTIONAL_FLAG = 'F') SHIPTOCONTNAME
487 ,QP_LIST_HEADERS_VL PRLNAME
488 ,OKX_RULES_V ACCTRULE
489 ,OKX_RULES_V INVRULE
490 ,OKS_HEADERS_INTERFACE OHI
491 WHERE
492 H.ID = LINE.CHR_ID
493 AND LINE.LSE_ID IN (1,12,14,19,46)
494 AND LINE.ID = LS.CLE_ID
495 AND LINE.LSE_ID = LTYST.ID
496 AND KITEMS.CLE_ID = LINE.ID
497 AND MTL.INVENTORY_ITEM_ID = KITEMS.OBJECT1_ID1
498 AND MTL.ORGANIZATION_ID = KITEMS.OBJECT1_ID2
499 AND KITEMS.JTOT_OBJECT1_CODE IN ('OKX_WARRANTY','OKX_SERVICE','OKS_SUBSCRIPTION','OKX_USAGE')
500 AND LINE.BILL_TO_SITE_USE_ID = BSU.SITE_USE_ID
501 AND LINE.SHIP_TO_SITE_USE_ID = SSU.SITE_USE_ID
502 AND LINE.ID = PTY.CLE_ID
503 --AND PTY.RLE_CODE IN ('CUSTOMER','SUBSCRIBER')
504 AND PTY.ID = BILLTOCONT.CPL_ID
505 AND BILLTOCONT.CRO_CODE = 'CUST_BILLING'
506 AND PTY.ID = SHIPTOCONT.CPL_ID
507 AND SHIPTOCONT.CRO_CODE = 'CUST_SHIPPING'
508 AND BILLTOCONT.OBJECT1_ID1 = BILLTOCONTNAME.CONTACT_ID
509 AND BILLTOCONT.JTOT_OBJECT1_CODE = 'OKX_CONTBILL'
510 AND SHIPTOCONT.OBJECT1_ID1 = SHIPTOCONTNAME.CONTACT_ID
511 AND SHIPTOCONT.JTOT_OBJECT1_CODE = 'OKX_CONTSHIP'
512 AND LINE.PRICE_LIST_ID = PRLNAME.LIST_HEADER_ID(+)
513 AND LS.ACCT_RULE_ID = ACCTRULE.ID1 (+)
514 AND ACCTRULE.TYPE (+) = 'A'
515 AND LINE.INV_RULE_ID = INVRULE.ID1 (+)
516 AND INVRULE.TYPE (+) = 'I'
517 AND H.CONTRACT_NUMBER = p_contract_number
518 AND nvl(H.CONTRACT_NUMBER_MODIFIER, '1') = nvl(p_contract_modifier, '1')
519 AND nvl(OHI.CONTRACT_NUMBER, '1') = nvl(p_target_contract, '1')
520 AND OHI.BATCH_ID = l_batch_id;
521
522 l_stmt_num := 40;
523
524 INSERT INTO OKS_SALES_CREDITS_INTERFACE
525 (
526 SALES_CREDIT_INTERFACE_ID
527 ,HEADER_INTERFACE_ID
528 ,SALESPERSON_NAME
529 ,SALESPERSON_ID
530 ,SALESGROUP_ID
531 ,SALES_CREDIT_TYPE
532 ,PERCENT
533 )
534 SELECT OKS_SALES_CREDITS_INT_ID_S.nextval AS SALES_CREDIT_INTERFACE_ID
535 ,OHI.HEADER_INTERFACE_ID AS HEADER_INTERFACE_ID
536 ,SLSREPNAME.RESOURCE_NAME AS SALESPERSON_NAME
537 ,SLSREPID.SALESREP_ID AS SALESPERSON_ID
538 ,OSC.SALES_GROUP_ID AS SALESGROUP_ID
539 ,SCT.NAME AS SALES_CREDIT_TYPE
540 ,PERCENT
541 FROM OKS_K_SALES_CREDITS OSC
542 ,OKC_K_HEADERS_ALL_B H
543 ,JTF_RS_SALESREPS SLSREPID
544 ,JTF_RS_RESOURCE_EXTNS_VL SLSREPNAME
545 ,OE_SALES_CREDIT_TYPES SCT
546 ,OKS_HEADERS_INTERFACE OHI
547 WHERE H.ID = OSC.CHR_ID
548 AND OSC.CLE_ID IS NULL
549 AND OSC.CTC_ID = SLSREPID.SALESREP_ID
550 AND SLSREPID.ORG_ID = H.AUTHORING_ORG_ID
551 AND SLSREPID.RESOURCE_ID = SLSREPNAME.RESOURCE_ID
552 AND OSC.SALES_CREDIT_TYPE_ID1 = SCT.SALES_CREDIT_TYPE_ID
553 AND H.CONTRACT_NUMBER = p_contract_number
554 AND nvl(H.CONTRACT_NUMBER_MODIFIER, '1') = nvl(p_contract_modifier, '1')
555 AND nvl(OHI.CONTRACT_NUMBER, '1') = nvl(p_target_contract, '1')
556 AND OHI.BATCH_ID = l_batch_id;
557
558 l_stmt_num := 50;
559
560 INSERT INTO OKS_COVERED_LEVELS_INTERFACE
561 (COVERED_LEVEL_INTERFACE_ID
562 ,LINE_INTERFACE_ID
563 ,LINE_NUMBER
564 ,COVERED_SERIAL_NUMBER
565 ,COVERED_INSTANCE_NUMBER
566 ,COVERED_INSTANCE_ID
567 ,COVERED_ITEM_NAME
568 ,COVERED_ITEM_ID
569 ,COVERED_ITEM_ORG_ID
570 ,COVERED_SYSTEM_ID
571 ,COVERED_ACCOUNT_NUMBER
572 ,COVERED_ACCOUNT_ID
573 ,COVERED_SITE_NUMBER
574 ,COVERED_SITE_ID
575 ,COVERED_PARTY_NAME
576 ,COVERED_PARTY_NUMBER
577 ,COVERED_PARTY_ID
578 ,LINE_REFERENCE
579 ,STATUS_CODE
580 ,START_DATE
581 ,END_DATE
582 ,RENEWAL_TYPE_CODE
583 ,CANCELLATION_DATE
584 ,CANCELLATION_REASON
585 ,INVOICE_TEXT
586 ,PRINT_INVOICE
587 ,QUANTITY_COVERED
588 ,QUANTITY_UOM
589 ,PRICE_UOM
590 ,SUBTOTAL
591 ,TAX_AMOUNT
592 )
593 SELECT OKS_CLVL_INT_ID_S.NEXTVAL COVERED_LEVEL_INTERFACE_ID
594 ,LININT.LINE_INTERFACE_ID LINE_INTERFACE_ID
595
596 ,OKCLINB_subline.LINE_NUMBER LINE_NUMBER
597
598 ,null COVERED_SERIAL_NUMBER
599 ,(CASE WHEN OKCLINB_subline.LSE_ID IN (9,18,25) THEN CSIITMINST.INSTANCE_NUMBER
600 ELSE NULL
601 END) COVERED_INSTANCE_NUMBER
602 ,(CASE WHEN OKCLINB_subline.LSE_ID IN (9,18,25) THEN OKCITM.OBJECT1_ID1
603 ELSE NULL
604 END) COVERED_INSTANCE_ID
605 ,(CASE WHEN OKCLINB_subline.LSE_ID = 7 THEN MTLSYSITM.concatenated_segments
606 else null
607 end) COVERED_ITEM_NAME
608 ,(CASE WHEN OKCLINB_subline.LSE_ID = 7 THEN OKCITM.OBJECT1_ID1
609 ELSE NULL
610 END) COVERED_ITEM_ID
611 ,(CASE when OKCITM.OBJECT1_ID2 <>'#' then OKCITM.OBJECT1_ID2
612 else null
613 end)COVERED_ITEM_ORG_ID
614 ,(CASE WHEN OKCLINB_subline.LSE_ID = 11 THEN OKCITM.OBJECT1_ID1
615 ELSE NULL
616 END) COVERED_SYSTEM_ID
617 ,(case when OKCLINB_subline.LSE_ID = 35 then CUSTACC.ACCOUNT_NUMBER
618 else null
619 end) COVERED_ACCOUNT_NUMBER
620 , (CASE WHEN OKCLINB_subline.LSE_ID = 35 THEN OKCITM.OBJECT1_ID1
621 ELSE NULL
622 END) COVERED_ACCOUNT_ID
623 ,(CASE WHEN OKCLINB_subline.LSE_ID = 10 then HZPRTSIT.PARTY_SITE_NUMBER
624 else null
625 end) COVERED_SITE_NUMBER
626 ,(CASE WHEN OKCLINB_subline.LSE_ID = 10 THEN OKCITM.OBJECT1_ID1
627 ELSE NULL
628 END) COVERED_SITE_ID
629
630 ,(CASE WHEN OKCLINB_subline.LSE_ID = 8 THEN HZPRT.PARTY_NAME
631 else null
632 end) COVERED_PARTY_NAME
633 ,(CASE WHEN OKCLINB_subline.LSE_ID = 8 THEN HZPRT.PARTY_NUMBER
634 else null
635 end) COVERED_PARTY_NUMBER
636 ,(CASE WHEN OKCLINB_subline.LSE_ID = 8 THEN OKCITM.OBJECT1_ID1
637 ELSE NULL
638 END) COVERED_PARTY_ID
639 , OKCLINTL.COGNOMEN LINE_REFERENCE
640 , OKCLINB_subline.STS_CODE STATUS_CODE
641 , OKCLINB_subline.START_DATE START_DATE
642 , OKCLINB_subline.END_DATE END_DATE
643 , OKCLINB_subline.LINE_RENEWAL_TYPE_CODE RENEWAL_TYPE_CODE
644 , OKCLINB_subline.DATE_CANCELLED CANCELLATION_DATE
645 , OKCLINB_subline.TRN_CODE CANCELLATION_REASON
646 , OKSLINTL.INVOICE_TEXT INVOICE_TEXT
647 , OKSLINB.INV_PRINT_FLAG PRINT_INVOICE
648 , OKCITM.NUMBER_OF_ITEMS QUANTITY_COVERED
649 , OKCITM.UOM_CODE QUANTITY_UOM
650 , OKSLINB.PRICE_UOM PRICE_UOM
651 , OKCLINB_subline.PRICE_NEGOTIATED SUBTOTAL
652 , OKSLINB.TAX_AMOUNT TAX_AMOUNT
653
654 FROM OKS_LINES_INTERFACE LININT
655 ,OKS_HEADERS_INTERFACE HDRINT
656
657 ,OKC_K_HEADERS_ALL_B OKCHDRB
658 ,OKC_K_ITEMS OKCITM
659 ,CSI_ITEM_INSTANCES CSIITMINST
660 ,HZ_CUST_ACCOUNTS CUSTACC
661 ,MTL_SYSTEM_ITEMS_B_kfv MTLSYSITM
662 ,HZ_PARTIES HZPRT
663 ,HZ_PARTY_SITES HZPRTSIT
664 ,CSI_SYSTEMS_B CSISYS
665 ,OKC_K_LINES_TL OKCLINTL
666 ,OKS_K_LINES_B OKSLINB
667 ,OKS_K_LINES_TL OKSLINTL
668 ,okc_k_lines_b okclinb_line
669 ,OKC_K_LINES_B OKCLINB_subline
670
671 WHERE LININT.HEADER_INTERFACE_ID = HDRINT.HEADER_INTERFACE_ID
672
673 and OKCLINB_subline.dnz_chr_id = okchdrb.id
674 and OKCLINB_subline.cle_id =okclinb_line.id
675 and okclinb_line.line_number = linint.line_number
676 and okclinb_line.chr_id = okchdrb.id
677 and okclinb_line.dnz_chr_id =okchdrb.id
678
679 AND OKCITM.CLE_ID= OKCLINB_subline.id
680 and OKCITM.DNZ_CHR_ID = OKCLINB_subline.DNZ_CHR_ID
681 AND OKCITM.OBJECT1_ID1 = CSIITMINST.INSTANCE_ID (+)
682 AND OKCITM.OBJECT1_ID1 = CUSTACC.CUST_ACCOUNT_ID (+)
683 AND OKCITM.OBJECT1_ID1 = MTLSYSITM.INVENTORY_ITEM_ID (+)
684 AND OKCITM.OBJECT1_ID2 = to_char(MTLSYSITM.ORGANIZATION_ID (+))
685 AND OKCITM.OBJECT1_ID1 = HZPRT.PARTY_ID (+)
686 AND OKCITM.OBJECT1_ID1 = CSISYS.SYSTEM_ID(+)
687 and OKCITM.OBJECT1_ID1 = HZPRTSIT.party_site_id (+)
688 and OKCLINTL.ID = OKCLINB_subline.id
689 AND OKSLINB.CLE_ID =OKCLINB_subline.id
690 AND OKSLINB.DNZ_CHR_ID=OKCLINB_subline.dnz_chr_id
691 AND OKSLINB.ID = OKSLINTL.ID
692 and OKCLINB_subline.lse_id in (7,8,9,10,11,18,25,35)
693 AND OKSLINTL.LANGUAGE = USERENV('LANG')
694 AND OKCLINTL.LANGUAGE = USERENV('LANG')
695 AND OKCHDRB.CONTRACT_NUMBER = p_contract_number
696 AND nvl(OKCHDRB.CONTRACT_NUMBER_MODIFIER, '1') = nvl(p_contract_modifier, '1')
697 AND nvl(HDRINT.CONTRACT_NUMBER, '1') = nvl(p_target_contract, '1')
698 AND HDRINT.BATCH_ID = l_batch_id;
699
700
701 l_stmt_num := 60;
702
703 INSERT INTO OKS_USAGE_COUNTERS_INTERFACE
704
705 (USAGE_COUNTER_INTERFACE_ID
706 ,LINE_INTERFACE_ID
707 ,LINE_NUMBER
708 ,COUNTER_ID
709 ,LINE_REFERENCE
710 ,STATUS_CODE
711 ,START_DATE
712 ,END_DATE
713 ,RENEWAL_TYPE_CODE
714 ,CANCELLATION_DATE
715 ,CANCELLATION_REASON
716 ,INVOICE_TEXT
717 ,PRINT_INVOICE
718 ,SUBTOTAL
719 ,FIXED_USG_CNT
720 ,MINIMUM_USG_CNT
721 ,DEFAULT_USG_CNT
722 ,FILL_YN
723 ,ESTIMATION_METHOD
724 ,ESTIMATION_START_DATE
725 ,LEVEL_YN
726 ,TAX_AMOUNT)
727 SELECT OKS_USGCNTR_INT_ID_S.NEXTVAL USAGE_COUNTER_INTERFACE_ID
728 ,LININT.LINE_INTERFACE_ID LINE_INTERFACE_ID
729 ,OKCLINB_subline.LINE_NUMBER LINE_NUMBER
730 ,OKCITM.OBJECT1_ID1 COUNTER_ID -- open issue, need clarification
731 ,OKCLINTL.COGNOMEN LINE_REFERENCE
732 ,OKCLINB_subline.STS_CODE STATUS_CODE
733 ,OKCLINB_subline.START_DATE START_DATE
734 ,OKCLINB_subline.END_DATE END_DATE
735 ,OKCLINB_subline.LINE_RENEWAL_TYPE_CODE RENEWAL_TYPE_CODE
736 ,OKCLINB_subline.DATE_CANCELLED CANCELLATION_DATE
737 ,OKCLINB_subline.TRN_CODE CANCELLATION_REASON
738 ,OKSLINTL.INVOICE_TEXT INVOICE_TEXT
739 ,OKSLINB.INV_PRINT_FLAG PRINT_INVOICE
740 ,OKCLINB_subline.PRICE_NEGOTIATED SUBTOTAL
741 ,OKSLINB.FIXED_QUANTITY FIXED_USG_CNT
742 ,OKSLINB.MINIMUM_QUANTITY MINIMUM_USG_CNT
743 ,OKSLINB.DEFAULT_QUANTITY DEFAULT_USG_CNT
744 ,OKSLINB.USAGE_EST_YN FILL_YN
745 ,OKSLINB.USAGE_EST_METHOD ESTIMATION_METHOD
746 ,OKSLINB.USAGE_EST_START_DATE ESTIMATION_START_DATE
747 ,OKSLINB.LEVEL_YN LEVEL_YN
748 ,OKSLINB.TAX_AMOUNT TAX_AMOUNT
749 FROM OKS_LINES_INTERFACE LININT
750 ,OKS_K_LINES_B OKSLINB
751 ,OKC_K_HEADERS_ALL_B OKCHDRB
752 ,OKS_HEADERS_INTERFACE HDRINT
753 ,OKC_K_LINES_TL OKCLINTL
754 ,OKS_K_LINES_TL OKSLINTL
755 ,OKC_K_LINES_B OKCLINB_LINE
756 ,OKC_K_LINES_B OKCLINB_SUBLINE
757 ,okc_k_items okcitm
758 WHERE HDRINT.HEADER_INTERFACE_ID = LININT.HEADER_INTERFACE_ID
759 AND LININT.LINE_TYPE = 'USAGE'
760 AND LININT.LINE_NUMBER= OKCLINB_LINE.LINE_NUMBER
761 AND OKCLINB_LINE.DNZ_CHR_ID = OKCHDRB.ID
762 AND OKCLINB_LINE.CHR_ID = OKCHDRB.ID
763 AND OKCLINB_LINE.LSE_ID = 12 -- defaulted to 13 for usage counters
764 AND OKCLINB_SUBLINE.LSE_ID = 13
765 AND OKCLINB_SUBLINE.CLE_ID = OKCLINB_LINE.ID
766 AND OKCLINB_SUBLINE.DNZ_CHR_ID = OKCHDRB.ID
767 AND OKSLINB.CLE_ID = OKCLINB_SUBLINE.ID
768 AND OKSLINB.DNZ_CHR_ID = OKCHDRB.ID
769 AND OKCLINTL.ID = OKCLINB_SUBLINE.ID
770 AND OKCLINTL.LANGUAGE = USERENV('LANG')
771 AND OKSLINTL.ID = OKSLINB.ID
772 AND OKSLINTL.LANGUAGE = USERENV('LANG')
773 and OKCITM.dnz_chr_id = okchdrb.id
774 and okcitm.cle_id = okclinb_subline.id
775 AND OKCHDRB.CONTRACT_NUMBER = p_contract_number
776 AND nvl(OKCHDRB.CONTRACT_NUMBER_MODIFIER, '1') = nvl(p_contract_modifier, '1')
777 AND nvl(HDRINT.CONTRACT_NUMBER, '1') = nvl(p_target_contract, '1')
778 AND HDRINT.BATCH_ID = l_batch_id;
779
780 l_stmt_num := 70;
781 -- For Notes of Header Interface
782
783 INSERT INTO OKS_NOTES_INTERFACE
784 (NOTES_INTERFACE_ID
785 ,HEADER_INTERFACE_ID
786 ,LINE_INTERFACE_ID
787 ,NOTES
788 ,NOTES_DETAIL
789 ,NOTE_STATUS
790 ,NOTE_TYPE
791 ,ENTERED_BY
792 ,ENTERED_DATE)
793
794 SELECT oks_notes_int_id_s.NEXTVAL NOTES_INTERFACE_ID
795 ,HDRINT.HEADER_INTERFACE_ID HEADER_INTERFACE_ID
796 ,null LINE_INTERFACE_ID
797 ,JTFNTL.NOTES NOTES
798 ,JTFNTL.NOTES_DETAIL NOTES_DETAIL
799 ,JTFNTB.NOTE_STATUS NOTE_STATUS
800 ,JTFNTB.NOTE_TYPE NOTE_TYPE
801 ,JTFNTB.ENTERED_BY ENTERED_BY
802 ,JTFNTB.ENTERED_DATE ENTERED_DATE
803
804 FROM JTF_NOTES_B JTFNTB
805 ,JTF_NOTES_TL JTFNTL
806 ,OKS_HEADERS_INTERFACE HDRINT
807 ,OKC_K_HEADERS_ALL_B OKCHDRB
808
809 WHERE JTFNTB.SOURCE_OBJECT_CODE = 'OKS_HDR_NOTE'
810 AND JTFNTB.JTF_NOTE_ID = JTFNTL.JTF_NOTE_ID
811 AND JTFNTL.LANGUAGE = USERENV('LANG')
812 AND JTFNTL.SOURCE_LANG= USERENV('LANG')
813 AND JTFNTB.SOURCE_OBJECT_ID =OKCHDRB.ID
814 AND OKCHDRB.CONTRACT_NUMBER = p_contract_number
815 AND nvl(okchdrb.CONTRACT_NUMBER_MODIFIER, '1') = nvl(p_contract_modifier, '1')
816 AND nvl(HDRINT.CONTRACT_NUMBER, '1') = nvl(p_target_contract, '1')
817 AND HDRINT.BATCH_ID = l_batch_id ;
818
819 l_stmt_num := 80;
820 -- Insert for Notes for Lines
821
822 INSERT INTO OKS_NOTES_INTERFACE
823 (NOTES_INTERFACE_ID
824 ,HEADER_INTERFACE_ID
825 ,LINE_INTERFACE_ID
826 ,NOTES
827 ,NOTES_DETAIL
828 ,NOTE_STATUS
829 ,NOTE_TYPE
830 ,ENTERED_BY
831 ,ENTERED_DATE)
832 SELECT oks_notes_int_id_s.NEXTVAL NOTES_INTERFACE_ID
833 ,NULL HEADER_INTERFACE_ID
834 ,LININT.LINE_INTERFACE_ID LINE_INTERFACE_ID
835 ,JTFNTL.NOTES NOTES
836 ,JTFNTL.NOTES_DETAIL NOTES_DETAIL
837 ,JTFNTB.NOTE_STATUS NOTE_STATUS
838 ,JTFNTB.NOTE_TYPE NOTE_TYPE
839 ,JTFNTB.ENTERED_BY ENTERED_BY
840 ,JTFNTB.ENTERED_DATE ENTERED_DATE
841
842 FROM JTF_NOTES_B JTFNTB
843 ,JTF_NOTES_TL JTFNTL
844 ,OKS_HEADERS_INTERFACE HDRINT
845 ,OKC_K_HEADERS_ALL_B OKCHDRB
846 ,OKC_K_LINES_B OKCLINB
847 ,OKS_LINES_INTERFACE LININT
848
849 WHERE LININT.HEADER_INTERFACE_ID = HDRINT.HEADER_INTERFACE_ID
850 AND JTFNTB.SOURCE_OBJECT_CODE = 'OKS_COV_NOTE'
851 AND JTFNTB.JTF_NOTE_ID = JTFNTL.JTF_NOTE_ID
852 AND JTFNTL.LANGUAGE = USERENV('LANG')
853 AND JTFNTL.SOURCE_LANG= USERENV('LANG')
854 AND OKCLINB.DNZ_CHR_ID = OKCHDRB.ID
855 AND JTFNTB.SOURCE_OBJECT_ID = OKCLINB.ID
856 AND LININT.LINE_NUMBER = OKCLINB.LINE_NUMBER
857 AND OKCHDRB.CONTRACT_NUMBER = p_contract_number
858 AND nvl(okchdrb.CONTRACT_NUMBER_MODIFIER, '1') = nvl(p_contract_modifier, '1')
859 AND nvl(HDRINT.CONTRACT_NUMBER, '1') = nvl(p_target_contract, '1')
860 AND HDRINT.BATCH_ID = l_batch_id ;
861
862
863 EXCEPTION
864 WHEN l_norecords_exception THEN
865 X_retcode := '2';
866 X_errbuf := 'Could not the model contract or it may not have Bill to/Ship to Site Use, Sales person, Accounting rule, Invoicing Rule.';
867 WHEN FND_API.G_EXC_ERROR THEN
868
869 FND_MSG_PUB.Count_And_Get
870 (p_encoded => FND_API.G_FALSE
871 ,p_count => l_msg_count
872 ,p_data => l_msg_data
873 );
874 X_retcode := '2';
875 X_errbuf := l_msg_data;
876
877 WHEN OTHERS THEN
878 X_errbuf := 'Stmt '||l_stmt_num||' '||SQLCODE || substr(SQLERRM, 1, 200);
879 X_retcode := '2';
880 FND_MSG_PUB.Count_And_Get
881 (p_encoded => FND_API.G_FALSE
882 ,p_count => l_msg_count
883 ,p_data => l_msg_data
884 );
885
886 IF G_EXCEPTION_LOG THEN
887 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
888 , G_MODULE_HEAD || l_routine ||'.others_exc'
889 , 'others: ' || X_errbuf || ' ' || substr(l_msg_data, 1,250)
890 );
891 END IF;
892
893 END Insert_Interface_Records;
894
895 END OKS_IMPORT_TEST_INSERT;