[Home] [Help]
PACKAGE BODY: APPS.OKS_OCINT_PUB
Source
1 PACKAGE BODY oks_ocint_pub AS
2 /* $Header: OKSPOCIB.pls 120.31.12020000.8 2013/05/21 10:03:51 vgujarat ship $ */
3 ----------------------------------------------------------------------------------------
4 g_unexpected_error CONSTANT VARCHAR2 (200)
5 := 'OKC_CONTRACTS_UNEXP_ERROR';
6 g_sqlcode_token CONSTANT VARCHAR2 (200) := 'SQLcode';
7 g_sqlerrm_token CONSTANT VARCHAR2 (200) := 'SQLerrm';
8 g_required_value CONSTANT VARCHAR2 (200) := okc_api.g_required_value;
9 g_col_name_token CONSTANT VARCHAR2 (200) := okc_api.g_col_name_token;
10 ----------------------------------------------------------------------------------------
11 -- Constants used for Message Logging
12 g_level_unexpected CONSTANT NUMBER := fnd_log.level_unexpected;
13 g_level_error CONSTANT NUMBER := fnd_log.level_error;
14 g_level_exception CONSTANT NUMBER := fnd_log.level_exception;
15 g_level_event CONSTANT NUMBER := fnd_log.level_event;
16 g_level_procedure CONSTANT NUMBER := fnd_log.level_procedure;
17 g_level_statement CONSTANT NUMBER := fnd_log.level_statement;
18 g_level_current CONSTANT NUMBER := fnd_log.g_current_runtime_level;
19 g_module_current CONSTANT VARCHAR2 (255)
20 := 'oks.plsql.oks_int_ocint_pub';
21
22 ----------------------------------------------------------------------------------------
23 FUNCTION check_strmlvl_exists (p_cle_id IN NUMBER)
24 RETURN NUMBER
25 IS
26 CURSOR l_billsch_csr (p_cle_id IN NUMBER)
27 IS
28 SELECT ID
29 FROM oks_stream_levels_v
30 WHERE cle_id = p_cle_id;
31
32 l_strmlvl_id NUMBER;
33 BEGIN
34 OPEN l_billsch_csr (p_cle_id);
35
36 FETCH l_billsch_csr
37 INTO l_strmlvl_id;
38
39 IF (l_billsch_csr%FOUND)
40 THEN
41 RETURN (l_strmlvl_id);
42 ELSE
43 RETURN (NULL);
44 END IF;
45
46 CLOSE l_billsch_csr;
47 EXCEPTION
48 WHEN NO_DATA_FOUND
49 THEN
50 RETURN (NULL);
51 END;
52
53 FUNCTION check_lvlelements_exists (p_cle_id IN NUMBER)
54 RETURN BOOLEAN
55 IS
56 CURSOR l_billsll_csr (p_cle_id IN NUMBER)
57 IS
58 SELECT 'x'
59 FROM oks_stream_levels_v sll, oks_level_elements lvl
60 WHERE lvl.rul_id = sll.ID AND sll.cle_id = p_cle_id;
61
62 v_flag BOOLEAN := FALSE;
63 v_temp VARCHAR2 (5);
64 BEGIN
65 OPEN l_billsll_csr (p_cle_id);
66
67 FETCH l_billsll_csr
68 INTO v_temp;
69
70 IF (l_billsll_csr%FOUND)
71 THEN
72 v_flag := TRUE;
73 ELSE
74 v_flag := FALSE;
75 END IF;
76
77 CLOSE l_billsll_csr;
78
79 RETURN (v_flag);
80 END;
81
82 PROCEDURE oc_interface (
83 errbuf OUT NOCOPY VARCHAR2,
84 retcode OUT NOCOPY NUMBER
85 )
86 IS
87 l_init_msg_list VARCHAR2 (1) DEFAULT fnd_api.g_false;
88 l_commit VARCHAR2 (1) DEFAULT fnd_api.g_false;
89 l_return_status VARCHAR2 (1);
90 l_msg_count NUMBER;
91 l_msg_data VARCHAR2 (2000);
92 l_wait NUMBER DEFAULT DBMS_AQ.no_wait;
93 l_no_more_messages VARCHAR2 (240);
94 l_header_rec oe_order_pub.header_rec_type;
95 l_old_header_rec oe_order_pub.header_rec_type;
96 l_header_adj_tbl oe_order_pub.header_adj_tbl_type;
97 l_old_header_adj_tbl oe_order_pub.header_adj_tbl_type;
98 l_header_price_att_tbl oe_order_pub.header_price_att_tbl_type;
99 l_old_header_price_att_tbl oe_order_pub.header_price_att_tbl_type;
100 l_header_adj_att_tbl oe_order_pub.header_adj_att_tbl_type;
101 l_old_header_adj_att_tbl oe_order_pub.header_adj_att_tbl_type;
102 l_header_adj_assoc_tbl oe_order_pub.header_adj_assoc_tbl_type;
103 l_old_header_adj_assoc_tbl oe_order_pub.header_adj_assoc_tbl_type;
104 l_header_scredit_tbl oe_order_pub.header_scredit_tbl_type;
105 l_old_header_scredit_tbl oe_order_pub.header_scredit_tbl_type;
106 l_line_tbl oe_order_pub.line_tbl_type;
107 l_old_line_tbl oe_order_pub.line_tbl_type;
108 l_line_adj_tbl oe_order_pub.line_adj_tbl_type;
109 l_old_line_adj_tbl oe_order_pub.line_adj_tbl_type;
110 l_line_price_att_tbl oe_order_pub.line_price_att_tbl_type;
111 l_old_line_price_att_tbl oe_order_pub.line_price_att_tbl_type;
112 l_line_adj_att_tbl oe_order_pub.line_adj_att_tbl_type;
113 l_old_line_adj_att_tbl oe_order_pub.line_adj_att_tbl_type;
114 l_line_adj_assoc_tbl oe_order_pub.line_adj_assoc_tbl_type;
115 l_old_line_adj_assoc_tbl oe_order_pub.line_adj_assoc_tbl_type;
116 l_line_scredit_tbl oe_order_pub.line_scredit_tbl_type;
117 l_old_line_scredit_tbl oe_order_pub.line_scredit_tbl_type;
118 l_lot_serial_tbl oe_order_pub.lot_serial_tbl_type;
119 l_old_lot_serial_tbl oe_order_pub.lot_serial_tbl_type;
120 l_action_request_tbl oe_order_pub.request_tbl_type;
121 l_oe_line_rec oe_order_pub.line_rec_type;
122 l_dequeue_mode VARCHAR2 (240) DEFAULT DBMS_AQ.remove;
123 l_navigation VARCHAR2 (240)
124 DEFAULT DBMS_AQ.next_message;
125 l_repv_rec oks_rep_pvt.repv_rec_type;
126 l_out_repv_rec oks_rep_pvt.repv_rec_type;
127 l_request_id NUMBER;
128 --General Variables
129 l_ctr NUMBER;
130 l_oldline_count NUMBER;
131 l_newline_count NUMBER;
132 l_user_id NUMBER;
133 l_index NUMBER;
134 index1 NUMBER;
135 err_msg VARCHAR2 (1000) := '';
136 l_hdrid NUMBER := NULL;
137 dup_val VARCHAR2 (1) := '';
138 l_ord_num NUMBER;
139 l_exists VARCHAR2 (1);
140
141 CURSOR l_order_hdr_csr (p_ordlineid NUMBER)
142 IS
143 SELECT oh.header_id, oh.order_number
144 FROM oe_order_lines_all ol, oe_order_headers_all oh
145 WHERE ol.line_id = (p_ordlineid)
146 AND oh.org_id = okc_context.get_okc_org_id
147 AND oh.header_id = ol.header_id;
148
149 CURSOR l_order_csr (p_ordlineid NUMBER)
150 IS
151 SELECT org_id, ship_from_org_id, sold_from_org_id,
152 NVL (fulfilled_quantity, 0) fqty,
153 service_reference_line_id rolineid, header_id
154 FROM okx_order_lines_v
155 WHERE id1 = (p_ordlineid);
156
157 CURSOR check_ordline_exists (p_ordlineid NUMBER)
158 IS
159 SELECT 'x'
160 FROM oks_reprocessing
161 WHERE order_line_id = p_ordlineid;
162
163 --Fix for bug 3492335
164 CURSOR is_ib_trackable (
165 l_ref_order_line_id NUMBER,
166 l_organization_id NUMBER
167 )
168 IS
169 SELECT comms_nl_trackable_flag
170 FROM mtl_system_items_b
171 WHERE inventory_item_id = (SELECT inventory_item_id
172 FROM oe_order_lines_all
173 WHERE line_id = l_ref_order_line_id)
174 AND organization_id = l_organization_id
175 AND serviceable_product_flag = 'Y' ; /*BUG6181908 -- FP Bug#6006309*/
176
177 --BUG6181908 --FP Bug 6006309
178
179 Cursor csi_ib_trackable(l_ref_order_line_id Number , l_organization_id Number)
180 Is
181 Select mtl.comms_nl_trackable_flag
182 From mtl_system_items_b mtl
183 ,csi_item_instances csi
184 Where csi.instance_id = l_ref_order_line_id
185 and mtl.inventory_item_id = csi.inventory_item_id
186 and mtl.organization_id = l_organization_id
187 and mtl. serviceable_product_flag = 'Y';
188
189 --BUG6181908 --FP Bug 6006309
190
191 l_order_rec l_order_csr%ROWTYPE;
192 l_api_version CONSTANT NUMBER := 1.0;
193 x_msg_count NUMBER;
194 x_msg_data VARCHAR2 (2000);
195 ib_flag VARCHAR2 (1);
196 --
197 aso_handle_exception EXCEPTION;
198 aso_handle_normal EXCEPTION;
199 BEGIN
200 SAVEPOINT oks_ocinterface_pub;
201 fnd_file.put_line (fnd_file.LOG, 'Start of OC_interface...');
202 l_user_id := fnd_global.user_id;
203 fnd_file.put_line (fnd_file.LOG, 'User Id : ' || TO_CHAR (l_user_id));
204
205 LOOP
206 l_oldline_count := 0;
207 l_newline_count := 0;
208 aso_order_feedback_pub.get_notice
209 (p_api_version => 1.0,
210 p_init_msg_list => l_init_msg_list,
211 p_commit => l_commit,
212 x_return_status => l_return_status,
213 x_msg_count => l_msg_count,
214 x_msg_data => l_msg_data,
215 p_app_short_name => 'OKS',
216 p_wait => l_wait,
217 x_no_more_messages => l_no_more_messages,
218 x_header_rec => l_header_rec,
219 x_old_header_rec => l_old_header_rec,
220 x_header_adj_tbl => l_header_adj_tbl,
221 x_old_header_adj_tbl => l_old_header_adj_tbl,
222 x_header_price_att_tbl => l_header_price_att_tbl,
223 x_old_header_price_att_tbl => l_old_header_price_att_tbl,
224 x_header_adj_att_tbl => l_header_adj_att_tbl,
225 x_old_header_adj_att_tbl => l_old_header_adj_att_tbl,
226 x_header_adj_assoc_tbl => l_header_adj_assoc_tbl,
227 x_old_header_adj_assoc_tbl => l_old_header_adj_assoc_tbl,
228 x_header_scredit_tbl => l_header_scredit_tbl,
229 x_old_header_scredit_tbl => l_old_header_scredit_tbl,
230 x_line_tbl => l_line_tbl,
231 x_old_line_tbl => l_old_line_tbl,
232 x_line_adj_tbl => l_line_adj_tbl,
233 x_old_line_adj_tbl => l_old_line_adj_tbl,
234 x_line_price_att_tbl => l_line_price_att_tbl,
235 x_old_line_price_att_tbl => l_old_line_price_att_tbl,
236 x_line_adj_att_tbl => l_line_adj_att_tbl,
237 x_old_line_adj_att_tbl => l_old_line_adj_att_tbl,
238 x_line_adj_assoc_tbl => l_line_adj_assoc_tbl,
239 x_old_line_adj_assoc_tbl => l_old_line_adj_assoc_tbl,
240 x_line_scredit_tbl => l_line_scredit_tbl,
241 x_old_line_scredit_tbl => l_old_line_scredit_tbl,
242 x_lot_serial_tbl => l_lot_serial_tbl,
243 x_old_lot_serial_tbl => l_old_lot_serial_tbl,
244 x_action_request_tbl => l_action_request_tbl
245 );
246
247 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
248 THEN
249 FOR i IN 1 .. fnd_msg_pub.count_msg
250 LOOP
251 fnd_msg_pub.get (p_msg_index => -1,
252 p_encoded => 'F',
253 p_data => l_msg_data,
254 p_msg_index_out => l_index
255 );
256 fnd_file.put_line (fnd_file.LOG, l_msg_data);
257 END LOOP;
258
259 ROLLBACK TO oks_ocinterface_pub;
260 RETURN;
261 END IF;
262
263 l_newline_count := l_line_tbl.COUNT;
264 fnd_file.put_line (fnd_file.LOG,
265 'Lines to process = ' || l_newline_count
266 );
267 EXIT WHEN l_no_more_messages = fnd_api.g_true;
268
269 IF l_newline_count <= 0
270 THEN --Order Line Record Found
271 fnd_file.put_line (fnd_file.LOG, 'No lines to insert');
272 ELSE
273 fnd_file.put_line (fnd_file.LOG,
274 'Processing the order lines... line count='
275 || TO_CHAR (l_newline_count)
276 );
277
278 FOR l_count IN 1 .. l_newline_count
279 LOOP
280 l_exists := 'y';
281 fnd_file.put_line (fnd_file.LOG,
282 'Processing Order Line '
283 || l_line_tbl (l_count).line_id
284 );
285 fnd_file.put_line
286 (fnd_file.LOG,
287 'Service Ref Type Code '
288 || l_line_tbl (l_count).service_reference_type_code
289 );
290 fnd_file.put_line
291 (fnd_file.LOG,
292 'Service line Id '
293 || l_line_tbl (l_count).service_reference_line_id
294 );
295 fnd_file.put_line
296 (fnd_file.LOG,
297 'Profile option value '
298 || fnd_profile.VALUE
299 ('OKS_CONTRACTS_VALIDATION_SOURCE')
300 );
301
302 OPEN check_ordline_exists (l_line_tbl (l_count).line_id);
303
304 FETCH check_ordline_exists
305 INTO l_exists;
306
307 IF check_ordline_exists%NOTFOUND
308 THEN
309 l_exists := 'y';
310 END IF;
311
312 CLOSE check_ordline_exists;
313
314 --Check Delayed Service
315 IF (l_exists <> 'x')
316 THEN
317 fnd_file.put_line (fnd_file.LOG,
318 'Order line not present already...'
319 );
320
321 IF NVL (l_line_tbl (l_count).service_reference_type_code,
322 'REF_TYPE'
323 ) IN ('CUSTOMER_PRODUCT', 'ORDER')
324 AND l_line_tbl (l_count).service_reference_line_id IS NOT NULL
325 THEN --Checking Delayed Service
326 --Check Fulfillment
327 l_order_rec.fqty := 0;
328 l_order_rec.rolineid := NULL;
329 l_order_rec.header_id := NULL;
330
331 OPEN l_order_csr (l_line_tbl (l_count).line_id);
332
333 FETCH l_order_csr
334 INTO l_order_rec;
335
336 IF l_order_csr%NOTFOUND
337 THEN
338 l_order_rec.fqty := 0;
339 END IF;
340
341 CLOSE l_order_csr;
342
343 fnd_file.put_line (fnd_file.LOG,
344 'Fulfillment Quantity '
345 || l_order_rec.fqty
346 );
347 fnd_file.put_line (fnd_file.LOG,
348 'Order Org Id '
349 || l_order_rec.org_id
350 );
351 fnd_file.put_line (fnd_file.LOG,
352 'Order Organization Id '
353 || l_order_rec.ship_from_org_id
354 );
355 fnd_file.put_line (fnd_file.LOG,
356 'Sold from Org Id '
357 || l_order_rec.sold_from_org_id
358 );
359 okc_context.set_okc_org_context (l_order_rec.org_id,
360 NULL);
361 fnd_file.put_line (fnd_file.LOG,
362 'org_context - '
363 || TO_CHAR (okc_context.get_okc_org_id)
364 );
365 fnd_file.put_line
366 (fnd_file.LOG,
367 'organization_context - '
368 || TO_CHAR
369 (okc_context.get_okc_organization_id)
370 );
371
372 --Check IB Trackable flag
373 IF NVL (l_line_tbl (l_count).service_reference_type_code,
374 'REF_TYPE'
375 ) = 'ORDER'
376 THEN
377 OPEN is_ib_trackable
378 (l_line_tbl (l_count).service_reference_line_id,
379 okc_context.get_okc_organization_id
380 );
381
382 FETCH is_ib_trackable
383 INTO ib_flag;
384
385 CLOSE is_ib_trackable;
386 ELSE
387 --BUG6181908 --FP Bug 6006309
388 -- ib_flag := 'Y';
389 Open csi_ib_trackable(l_line_tbl(l_count).service_reference_line_id, okc_context.get_okc_organization_id);
390 Fetch csi_ib_trackable into ib_flag;
391 Close csi_ib_trackable;
392 --BUG6181908 --FP Bug 6006309
393
394 END IF;
395
396 IF NVL (ib_flag, 'N') = 'Y'
397 THEN
398 IF l_order_rec.fqty > 0
399 THEN
400 OPEN l_order_hdr_csr (l_line_tbl (l_count).line_id);
401
402 FETCH l_order_hdr_csr
403 INTO l_hdrid, l_ord_num;
404
405 IF l_order_hdr_csr%NOTFOUND
406 THEN
407 fnd_file.put_line (fnd_file.LOG,
408 ' Invalid Order line ID'
409 );
410
411 CLOSE l_order_hdr_csr;
412 ELSE
413 CLOSE l_order_hdr_csr;
414
415 fnd_file.put_line (fnd_file.LOG,
416 'Order Header ID ' || l_hdrid
417 );
418 l_repv_rec.order_id := l_hdrid;
419 l_repv_rec.order_line_id :=
420 l_line_tbl (l_count).line_id;
421 l_repv_rec.order_number := l_ord_num;
422 l_repv_rec.success_flag := 'N';
423 l_repv_rec.source_flag := 'ASO';
424 l_repv_rec.reprocess_yn := 'Y';
425 SAVEPOINT before_insert;
426 oks_rep_pub.insert_row
427 (p_api_version => 1.0,
428 p_init_msg_list => l_init_msg_list,
429 x_return_status => l_return_status,
430 x_msg_count => l_msg_count,
431 x_msg_data => l_msg_data,
432 p_repv_rec => l_repv_rec,
433 x_repv_rec => l_out_repv_rec
434 );
435 fnd_file.put_line
436 (fnd_file.LOG,
437 'OKS_REP_PUB - RETURN STATUS : '
438 || l_return_status
439 );
440
441 IF NOT (l_return_status =
442 fnd_api.g_ret_sts_success
443 )
444 THEN
445 FOR i IN 1 .. fnd_msg_pub.count_msg
446 LOOP
447 fnd_msg_pub.get
448 (p_msg_index => -1,
449 p_encoded => 'F',
450 p_data => l_msg_data,
451 p_msg_index_out => l_index
452 );
453
454 SELECT INSTR (l_msg_data,
455 'ORA-00001',
456 1,
457 1
458 )
459 INTO index1
460 FROM DUAL;
461
462 IF (index1 > 0)
463 THEN
464 dup_val := 'Y';
465 EXIT;
466 END IF;
467
468 fnd_file.put_line
469 (fnd_file.LOG,
470 'oks_rep_pub.insert_row: '
471 || l_msg_data
472 );
473 END LOOP;
474
475 IF (dup_val <> 'Y')
476 THEN
477 RAISE g_exception_halt_validation;
478 END IF;
479
480 l_return_status := fnd_api.g_ret_sts_success;
481 END IF;
482 END IF;
483 END IF;
484 END IF;
485 END IF;
486 ELSE
487 fnd_file.put_line (fnd_file.LOG, 'Duplicate Order Line');
488 END IF;
489 END LOOP;
490 END IF;
491 END LOOP;
492
493 errbuf := '';
494 retcode := 0;
495 --Modified for 12.0 ASO Queue Replacement Project (JVARGHES)
496 COMMIT WORK;
497 --
498 fnd_file.put_line (fnd_file.LOG, 'Order Capture INT Program finished.');
499 --Modified for 12.0 ASO Queue Replacement Project (JVARGHES)
500 --
501 --l_request_id := FND_REQUEST.SUBMIT_REQUEST('OKS','OKSREPROC','','',FALSE,'SEL','');
502 --if (l_request_id > 0) then
503 -- COMMIT WORK;
504 --end if;
505 --FND_FILE.PUT_LINE (FND_FILE.LOG, 'Firing Order Reprocess concurrent program');
506 --FND_FILE.PUT_LINE (FND_FILE.LOG, 'Request Id - '||to_char(l_request_id));
507 --
508 --
509 EXCEPTION
510 WHEN g_exception_halt_validation
511 THEN
512 ROLLBACK TO before_insert;
513 WHEN OTHERS
514 THEN
515 ROLLBACK TO before_insert;
516 -- Retrieve error message into errbuf
517 errbuf := SQLERRM;
518 retcode := 2;
519 fnd_file.put_line (fnd_file.LOG,
520 'Oracle Error Code is -' || TO_CHAR (SQLCODE)
521 );
522 fnd_file.put_line (fnd_file.LOG,
523 'Oracle Error Message is -'
524 || SUBSTR (SQLERRM, 1, 512)
525 );
526 END oc_interface;
527
528 PROCEDURE handle_order_error (
529 x_return_status OUT NOCOPY VARCHAR2,
530 p_upd_rec IN oks_rep_pvt.repv_rec_type
531 )
532 IS
533 CURSOR l_old_repv_csr (p_id NUMBER)
534 IS
535 SELECT object_version_number
536 FROM oks_reprocessing_v
537 WHERE ID = p_id;
538
539 --l_old_repv_rec l_old_repv_csr%ROWTYPE;
540 l_obj_vers_num NUMBER;
541 l_new_repv_rec oks_rep_pvt.repv_rec_type;
542 l_repv_rec oks_rep_pvt.repv_rec_type;
543 l_return_status VARCHAR2 (1) := okc_api.g_ret_sts_success;
544 l_msg_count NUMBER;
545 l_msg_data VARCHAR2 (2000);
546 l_index NUMBER;
547 BEGIN
548 x_return_status := l_return_status;
549
550 OPEN l_old_repv_csr (p_upd_rec.ID);
551
552 FETCH l_old_repv_csr
553 INTO l_obj_vers_num;
554
555 CLOSE l_old_repv_csr;
556
557 l_repv_rec.ID := p_upd_rec.ID;
558
559 IF (p_upd_rec.order_line_id IS NOT NULL)
560 THEN
561 l_repv_rec.order_line_id := p_upd_rec.order_line_id;
562 END IF;
563
564 l_repv_rec.object_version_number := l_obj_vers_num;
565 l_repv_rec.ERROR_TEXT := p_upd_rec.ERROR_TEXT;
566 l_repv_rec.success_flag := p_upd_rec.success_flag;
567 l_repv_rec.contract_id := p_upd_rec.contract_id;
568 l_repv_rec.contract_line_id := p_upd_rec.contract_line_id;
569 l_repv_rec.subline_id := p_upd_rec.subline_id;
570 l_repv_rec.conc_request_id := p_upd_rec.conc_request_id;
571 l_repv_rec.reprocess_yn := 'N';
572 oks_rep_pub.update_row (p_api_version => 1.0,
573 p_init_msg_list => 'T',
574 x_return_status => l_return_status,
575 x_msg_count => l_msg_count,
576 x_msg_data => l_msg_data,
577 p_repv_rec => l_repv_rec,
578 x_repv_rec => l_new_repv_rec
579 );
580 fnd_file.put_line (fnd_file.LOG,
581 'Update Row : Return Status = ' || l_return_status
582 );
583
584 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
585 THEN
586 FOR i IN 1 .. fnd_msg_pub.count_msg
587 LOOP
588 fnd_msg_pub.get (p_msg_index => -1,
589 p_encoded => 'F',
590 p_data => l_msg_data,
591 p_msg_index_out => l_index
592 );
593 fnd_file.put_line (fnd_file.LOG,
594 'Update Row Error : ' || l_msg_data
595 );
596 END LOOP;
597
598 RAISE g_exception_halt_validation;
599 END IF;
600 EXCEPTION
601 WHEN g_exception_halt_validation
602 THEN
603 fnd_file.put_line
604 (fnd_file.LOG,
605 'Error in Handle Order Error : Oracle Error Code is -'
606 || TO_CHAR (SQLCODE)
607 );
608 fnd_file.put_line
609 (fnd_file.LOG,
610 'Error in Handle Order Error : Oracle Error Message is -'
611 || SUBSTR (SQLERRM, 1, 512)
612 );
613 x_return_status := l_return_status;
614 WHEN OTHERS
615 THEN
616 x_return_status := okc_api.g_ret_sts_unexp_error;
617 fnd_file.put_line (fnd_file.LOG,
618 'Oracle Error Code is -' || TO_CHAR (SQLCODE)
619 );
620 fnd_file.put_line (fnd_file.LOG,
621 'Oracle Error Message is -'
622 || SUBSTR (SQLERRM, 1, 512)
623 );
624 END handle_order_error;
625
626 PROCEDURE order_reprocess (
627 errbuf OUT NOCOPY VARCHAR2,
628 retcode OUT NOCOPY NUMBER,
629 p_option IN VARCHAR2,
630 p_source IN VARCHAR2
631 )
632 IS
633 l_init_msg_list VARCHAR2 (1) DEFAULT fnd_api.g_false;
634 l_commit VARCHAR2 (1) DEFAULT fnd_api.g_false;
635 l_return_status VARCHAR2 (1);
636 l_msg_count NUMBER;
637 l_msg_data VARCHAR2 (2000);
638 l_wait NUMBER DEFAULT DBMS_AQ.no_wait;
639 l_dequeue_mode VARCHAR2 (240) DEFAULT DBMS_AQ.remove;
640 l_navigation VARCHAR2 (240) DEFAULT DBMS_AQ.next_message;
641 --General Variables
642 l_ctr NUMBER;
643 l_line_count NUMBER;
644 l_user_id NUMBER;
645 l_organization_id NUMBER;
646 l_k_line_id NUMBER := NULL;
647 --Header
648 l_k_header_rec oks_extwar_util_pvt.header_rec_type;
649 --Line
650 l_k_line_rec oks_extwar_util_pvt.line_rec_type;
651 --SalesCredit
652 l_salescredit_tbl_in oks_extwarprgm_pvt.salescredit_tbl;
653 --Pricing Attributes
654 l_pricing_attributes_in oks_extwarprgm_pvt.pricing_attributes_type;
655 --For Creating Contract
656 l_extwar_rec oks_extwarprgm_pvt.extwar_rec_type;
657 l_cp_id NUMBER;
658 l_cp_name VARCHAR2 (240);
659 l_cp_desc VARCHAR2 (240);
660 l_cp_qty NUMBER;
661 l_cp_uom VARCHAR2 (3);
662 l_fulfill VARCHAR2 (1) := 'Y';
663 l_prog_id NUMBER;
664 l_req_id NUMBER;
665
666 CURSOR l_cp_csr (cpid NUMBER)
667 IS
668 SELECT csi.instance_id cp_id, csi.inventory_item_id, mtl.NAME NAME,
669 mtl.description description, csi.quantity quantity,
670 csi.unit_of_measure uom_code
671 FROM csi_item_instances csi, okx_system_items_v mtl
672 WHERE csi.instance_id = cpid
673 AND mtl.inventory_item_id = csi.inventory_item_id
674 AND ROWNUM < 2;
675
676 TYPE cp_rec_type IS RECORD (
677 cp_id NUMBER
678 );
679
680 TYPE cp_tbl_type IS TABLE OF cp_rec_type
681 INDEX BY BINARY_INTEGER;
682
683 CURSOR l_order_csr (p_ordlineid NUMBER)
684 IS
685 SELECT org_id, ship_from_org_id, sold_from_org_id,
686 NVL (fulfilled_quantity, 0) fqty,
687 service_reference_line_id rolineid, header_id
688 FROM okx_order_lines_v
689 WHERE id1 = (p_ordlineid);
690
691 CURSOR l_contract_csr (p_ordlineid NUMBER)
692 IS
693 SELECT rel.cle_id, rel.chr_id
694 FROM okc_k_rel_objs_v rel, okc_k_lines_b line
695 WHERE rel.object1_id1 = TO_CHAR (p_ordlineid)
696 AND rel.jtot_object1_code = 'OKX_ORDERLINE'
697 AND line.ID = rel.cle_id
698 AND line.lse_id IN (9, 25);
699
700 CURSOR l_contact_csr (p_line_id NUMBER)
701 IS
702 SELECT oc.object1_id1, oc.cro_code
703 FROM oks_k_order_contacts_v oc, oks_k_order_details_v od
704 WHERE oc.cod_id = od.ID AND od.order_line_id1 = p_line_id;
705 /*COMMENTED AND MODIFIED FOR BUG6181908 -- FP BUG6006309
706 CURSOR l_custprod_csr (p_ordlineid NUMBER)
707 IS
708 SELECT csi.instance_id cp_id
709 FROM csi_item_instances csi, csi_instance_statuses st
710 WHERE csi.last_oe_order_line_id = p_ordlineid
711 AND NVL (st.service_order_allowed_flag, 'N') = 'Y'
712 AND csi.instance_status_id = st.instance_status_id;
713 */
714
715 Cursor l_custprod_csr (p_ordlineid Number
716 ,c_organization_id NUMBER) IS
717 SELECT csi.instance_id cp_id
718 FROM csi_item_instances csi
719 ,csi_instance_statuses st
720 ,mtl_system_items_b mtl
721 WHERE csi.last_oe_order_line_id = p_ordlineid
722 AND csi.instance_status_id = st.instance_status_id
723 AND mtl.inventory_item_id = csi.inventory_item_id
724 AND mtl.organization_id = c_organization_id
725 AND Nvl(st.service_order_allowed_flag,'N') = 'Y'
726 AND mtl.comms_nl_trackable_flag = 'Y'
727 AND mtl.serviceable_product_flag = 'Y' ;
728
729 CURSOR l_organization_csr (p_org_id NUMBER)
730 IS
731 SELECT master_organization_id
732 FROM oe_system_parameters_all
733 WHERE org_id = p_org_id;
734
735 CURSOR l_serv_ref_csr (p_ordline_id NUMBER)
736 IS
737 SELECT service_reference_type_code, service_reference_line_id
738 FROM okx_order_lines_v
739 WHERE id1 = p_ordline_id;
740
741 CURSOR l_contract_line_csr (p_subline_id NUMBER)
742 IS
743 SELECT cle_id
744 FROM okc_k_lines_b
745 WHERE ID = p_subline_id;
746
747 /*cgopinee bugfix for bug 8276025*/
748 /* CURSOR l_pricing_qty_csr(p_ordline_id Number)
749 IS
750 SELECT pricing_quantity
751 FROM oe_order_lines_all
752 WHERE line_id=p_ordline_id;*/
753
754 /* Commented above cursor as part of bug#13655785 */
755 CURSOR l_ordered_qty_csr(p_ordline_id Number)
756 IS
757 SELECT ordered_quantity
758 FROM oe_order_lines_all
759 WHERE line_id=p_ordline_id;
760
761 --General
762 l_index NUMBER;
763 l_process BOOLEAN;
764 l_order_rec l_order_csr%ROWTYPE;
765 l_ref_order_rec l_order_csr%ROWTYPE;
766 l_cp_tbl cp_tbl_type;
767 l_cp_ctr NUMBER := 0;
768 l_hdr_rec oks_extwarprgm_pvt.k_header_rec_type;
769 l_line_rec oks_extwarprgm_pvt.k_line_service_rec_type;
770 l_covd_rec oks_extwarprgm_pvt.k_line_covered_level_rec_type;
771 l_chrid NUMBER := NULL;
772 l_lineid NUMBER := NULL;
773 l_rnrl_rec_out oks_renew_util_pvt.rnrl_rec_type;
774 l_renewal_rec oks_extwar_util_pvt.renewal_rec_type;
775 l_contact_tbl oks_extwarprgm_pvt.contact_tbl;
776 l_ptr1 NUMBER := 0;
777 l_covlvl_id NUMBER;
778 l_rule_id NUMBER;
779 l_rule_group_id NUMBER;
780 l_api_version CONSTANT NUMBER := 1.0;
781 l_update_line VARCHAR2 (1);
782 l_duration NUMBER;
783 l_timeunits VARCHAR2 (240);
784 l_sll_tbl oks_bill_sch.streamlvl_tbl;
785 l_bil_sch_out oks_bill_sch.itembillsch_tbl;
786 x_msg_count NUMBER;
787 x_msg_data VARCHAR2 (2000);
788 x_return_status VARCHAR2 (1) := okc_api.g_ret_sts_success;
789 l_repv_tbl oks_rep_pvt.repv_tbl_type;
790 l_reproc_line_rec oks_rep_pvt.repv_rec_type;
791 l_serv_ref_rec l_serv_ref_csr%ROWTYPE;
792 l_ordered_qty NUMBER;
793 l_upd_tbl oks_rep_pvt.repv_tbl_type;
794 l_out_repv_rec oks_rep_pvt.repv_rec_type;
795 l_conc_rec oks_rep_pvt.repv_rec_type;
796 i NUMBER;
797 l_ctr1 NUMBER;
798 l_cont_line_id NUMBER;
799 l_dummy VARCHAR2 (30);
800 l_error_temp VARCHAR2 (2000);
801 l_error_msg VARCHAR2 (2000);
802
803 l_process_status VARCHAR2(20);
804
805 PROCEDURE create_contract (
806 p_reproc_line_rec oks_rep_pvt.repv_rec_type,
807 x_upd_tbl OUT NOCOPY oks_rep_pvt.repv_tbl_type,
808 x_return_status OUT NOCOPY VARCHAR2
809 )
810 IS
811 CURSOR l_party_csr
812 IS
813 SELECT NAME
814 FROM okx_parties_v
815 WHERE id1 = l_k_header_rec.party_id;
816
817 -- cursor to get ship and installation dates
818 -- vigandhi 04-jun-2002
819 CURSOR l_get_dates_csr (p_cp_id NUMBER)
820 IS
821 SELECT csi.install_date, ol.actual_shipment_date,
822 mtl.service_starting_delay
823 FROM csi_item_instances csi,
824 oe_order_lines_all ol,
825 okx_system_items_v mtl
826 WHERE csi.instance_id = p_cp_id
827 AND csi.last_oe_order_line_id = ol.line_id
828 AND csi.inventory_item_id = mtl.id1
829 AND ROWNUM < 2;
830
831 CURSOR l_hdr_scs_csr (p_chr_id NUMBER)
832 IS
833 SELECT scs_code
834 FROM okc_k_headers_v
835 WHERE ID = p_chr_id;
836
837 /*added for bugfix 14486901 by ansraj*/
838 Cursor l_hdr_ste_csr(p_chr_id Number) Is
839 Select sv.ste_code,
840 kh.contract_number||Decode(kh.contract_number_modifier,NULL,NULL,
841 ' - '||kh.contract_number_modifier) k_number
842 From OKC_STATUSES_V sv,
843 OKC_K_HEADERS_V kh
844 Where kh.id = p_chr_id
845 and kh.sts_code = sv.CODE;
846 /*end added for bugfix 14486901 by ansraj*/
847
848 CURSOR l_inv_csr (p_ordline_id NUMBER)
849 IS
850 SELECT inventory_item_id
851 FROM oe_order_lines --mmadhavi for MOAC
852 WHERE line_id = p_ordline_id;
853
854 CURSOR l_lndates_csr (p_id NUMBER)
855 IS
856 SELECT start_date, end_date
857 FROM okc_k_lines_b
858 WHERE ID = p_id;
859
860 CURSOR l_hdrdates_csr (p_id NUMBER)
861 IS
862 SELECT start_date, end_date, sts_code
863 FROM okc_k_headers_b
864 WHERE ID = p_id;
865
866 CURSOR l_refnum_csr (p_cp_id NUMBER)
867 IS
868 SELECT instance_number
869 FROM csi_item_instances
870 WHERE instance_id = p_cp_id;
871
872
873 -- Cursor to roll up the tax amount from sublines to the topline
874
875 CURSOR c_extwar_line_amount(p_chr_id IN NUMBER, p_line_id IN NUMBER) IS
876 SELECT
877 SUM(NVL(slines.tax_amount,0)) tax_amount
878 FROM
879 okc_k_lines_b clines
880 ,oks_k_lines_b slines
881 WHERE
882 clines.dnz_chr_id = p_chr_id
883 and clines.cle_id = p_line_id
884 and clines.id = slines.cle_id;
885
886 -- Cursor to rollup the tax amount from toplines to header
887
888 CURSOR c_extwar_hdr_amount(p_chr_id IN NUMBER) IS
889 SELECT
890 SUM(NVL(slines.tax_amount,0)) tax_amount
891 FROM
892 okc_k_lines_b clines
893 ,oks_k_lines_b slines
894 WHERE
895 clines.dnz_chr_id = p_chr_id
896 AND clines.lse_id IN (1, 19, 46) ---Modified for ER 16039680
897 AND clines.id = slines.cle_id;
898
899 l_hdr_scs_code VARCHAR2 (30);
900 l_hdr_ste_code Varchar2(30);/* bugfix for 14486901 */
901 l_k_number Varchar2(250);/* bugfix for 14486901 */
902 l_party_name okx_parties_v.NAME%TYPE;
903 l_get_dates_rec l_get_dates_csr%ROWTYPE;
904 l_sts_code VARCHAR2 (30);
905 l_ste_code VARCHAR2 (30);
906 l_ship_date DATE;
907 l_installation_date DATE;
908 l_strmlvl_id NUMBER;
909 l_cp_inventory NUMBER;
910 l_inv_item_id NUMBER;
911 l_serv_ref_rec l_serv_ref_csr%ROWTYPE;
912 l_order_error VARCHAR2 (2000);
913 l_lndates_rec l_lndates_csr%ROWTYPE;
914 l_hdrdates_rec l_hdrdates_csr%ROWTYPE;
915 l_ref_num VARCHAR2 (30);
916 l_inst_dtls_rec oks_ihd_pvt.ihdv_rec_type;
917 l_insthist_rec oks_ins_pvt.insv_rec_type;
918 x_inst_dtls_rec oks_ihd_pvt.ihdv_rec_type;
919 x_insthist_rec oks_ins_pvt.insv_rec_type;
920 l_error VARCHAR2 (1) := 'N';
921 l_header_id NUMBER;
922 l_period_start oks_k_headers_v.period_start%TYPE;
923 l_period_type oks_k_headers_v.period_type%TYPE;
924 l_price_uom oks_k_headers_v.price_uom%TYPE;
925 l_line_tax_amount NUMBER;
926 l_header_tax_amount NUMBER;
927
928 -- Added fro fix of bug# 5165947
929
930 l_BOM_instance_id NUMBER;
931 l_BOM_instance_flag VARCHAR2(10);
932 l_eff_line_upd_flag VARCHAR2(10);
933
934 l_prev_line_amt NUMBER;
935 l_curr_line_amt NUMBER;
936
937 /*Added for CLOUD ER 16039680*/
938 CURSOR get_item_type(l_instance_id NUMBER) IS
939 SELECT b.contract_item_type_code
940 FROM csi_item_instances csi,oe_order_lines_all ol,
941 mtl_system_items_b_kfv b,
942 mtl_system_items_tl t
943 WHERE csi.last_oe_order_line_id=ol.line_id
944 AND csi.instance_id = l_instance_id
945 AND b.inventory_item_id = ol.inventory_item_id
946 AND b.inventory_item_id = t.inventory_item_id
947 AND b.organization_id = t.organization_id
948 AND t.LANGUAGE = USERENV ('LANG')
949 AND b.organization_id = okc_context.get_okc_organization_id;
950
951 CURSOR Check_Contract_Exists(p_instance_id NUMBER,p_order_header_id NUMBER) IS
952 SELECT okro.chr_id
953 FROM okc_k_rel_objs okro,oe_order_lines_all ol,csi_item_instances csi
954 WHERE ol.line_id=okro.object1_id1
955 AND csi.last_oe_order_line_id=ol.line_id
956 AND csi.instance_id=p_instance_id
957 AND ol.header_id=p_order_header_id;
958
959 CURSOR get_billing_dtls_OM(p_order_line_id NUMBER)
960 IS
961 SELECT Nvl(SERVICE_BILL_OPTION_CODE,'FBOM'),SERVICE_BILL_PROFILE_ID,ordered_quantity
962 FROM oe_order_lines_all
963 WHERE line_id=p_order_line_id;
964
965
966
967 l_rec OKS_BILLING_PROFILES_PUB.Billing_profile_rec;
968 l_sll_tbl_out OKS_BILLING_PROFILES_PUB.Stream_Level_tbl;
969 l_billing_type VARCHAR2(10);
970 l_pr_qty NUMBER;
971 l_sub_qty NUMBER;
972 l_ordered_qty NUMBER;
973 l_pr_uomcode VARCHAR2(100);
974 l_negotiated_amount NUMBER;
975 l_list_price NUMBER;
976 l_date_end DATE;
977 l_pavv_tbl_in okc_price_adjustment_pvt.pavv_tbl_type;
978 l_pavv_tbl_out okc_price_adjustment_pvt.pavv_tbl_type;
979 l_priceattrib_id NUMBER;
980 l_api_version CONSTANT NUMBER := 1.0;
981 l_init_msg_list CONSTANT VARCHAR2 (1) := 'F';
982 l_return_status VARCHAR2 (1) := 'S';
983 l_crjv_tbl_in okc_k_rel_objs_pub.crjv_tbl_type;
984 l_crjv_tbl_out okc_k_rel_objs_pub.crjv_tbl_type;
985 l_chr_id_old NUMBER;
986 l_contract_item_type_code VARCHAR2(100);
987 l_bill_option VARCHAR2(100);
988 l_bill_profile_id NUMBER;
989 l_merge_scs_code VARCHAR2(100);
990
991 /*Added for CLOUD ER 16039680*/
992
993 cursor c_line_ammt_ckeck(c_line_id in number)
994 is select nvl(price_negotiated,0) from okc_k_lines_b where id = c_line_id;
995
996 -- Added fro fix of bug# 5165947
997
998 BEGIN
999 x_upd_tbl.DELETE;
1000
1001 g_last_oe_order_line_id := -1; /*Added for ER 16039680*/
1002
1003 OPEN l_get_dates_csr (l_cp_tbl (1).cp_id);
1004
1005 FETCH l_get_dates_csr
1006 INTO l_get_dates_rec;
1007
1008 CLOSE l_get_dates_csr;
1009 /* Fix for 6389290 */
1010 x_upd_tbl (1).order_line_id := p_reproc_line_rec.order_line_id;
1011 /* Fix Ends */
1012 l_ship_date :=
1013 TRUNC (l_get_dates_rec.actual_shipment_date)
1014 + NVL (l_get_dates_rec.service_starting_delay, 0);
1015 l_installation_date := TRUNC (l_get_dates_rec.install_date);
1016 oks_extwar_util_pvt.get_contract_header_info
1017 (p_order_line_id => p_reproc_line_rec.order_line_id,
1018 p_cp_id => NULL,
1019 p_caller => 'OC',
1020 x_order_error => l_order_error,
1021 x_return_status => l_return_status,
1022 x_header_rec => l_k_header_rec
1023 );
1024 fnd_file.put_line
1025 (fnd_file.LOG,
1026 'OC INTERFACE :- get_contract_header_info status '
1027 || l_return_status
1028 );
1029
1030 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
1031 THEN
1032 IF l_order_error IS NULL
1033 THEN
1034 l_order_error := '#';
1035
1036 FOR i IN 1 .. fnd_msg_pub.count_msg
1037 LOOP
1038 fnd_msg_pub.get (p_msg_index => i,
1039 p_encoded => 'T',
1040 p_data => l_msg_data,
1041 p_msg_index_out => l_index
1042 );
1043 l_order_error := l_order_error || l_msg_data || '#';
1044 fnd_message.set_encoded (l_msg_data);
1045 l_msg_data := fnd_message.get;
1046 fnd_file.put_line (fnd_file.LOG,
1047 'GET CONTRACT HDR FAILURE ' || l_msg_data
1048 );
1049 END LOOP;
1050 END IF;
1051
1052 x_upd_tbl (1).ERROR_TEXT := l_order_error;
1053 x_upd_tbl (1).success_flag := 'E';
1054 x_upd_tbl (1).order_line_id := p_reproc_line_rec.order_line_id;
1055 x_upd_tbl (1).ID := p_reproc_line_rec.ID;
1056 RAISE g_exception_halt_validation;
1057 END IF;
1058
1059 oks_extwar_util_pvt.get_k_service_line
1060 (p_order_line_id => p_reproc_line_rec.order_line_id,
1061 p_cp_id => l_cp_tbl (1).cp_id,
1062 p_shipped_date => l_ship_date,
1063 p_installation_date => l_installation_date,
1064 p_caller => 'OC',
1065 x_order_error => l_order_error,
1066 x_return_status => l_return_status,
1067 x_line_rec => l_k_line_rec
1068 );
1069 fnd_file.put_line (fnd_file.LOG,
1070 'OC INTERFACE :- get_k_service_line status '
1071 || l_return_status
1072 );
1073
1074 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
1075 THEN
1076 IF l_order_error IS NULL
1077 THEN
1078 l_order_error := '#';
1079
1080 FOR i IN 1 .. fnd_msg_pub.count_msg
1081 LOOP
1082 fnd_msg_pub.get (p_msg_index => i,
1083 p_encoded => 'T',
1084 p_data => l_msg_data,
1085 p_msg_index_out => l_index
1086 );
1087 l_order_error := l_order_error || l_msg_data || '#';
1088 fnd_message.set_encoded (l_msg_data);
1089 l_msg_data := fnd_message.get;
1090 fnd_file.put_line (fnd_file.LOG,
1091 'GET ORDER LINE FAILURE ' || l_msg_data
1092 );
1093 END LOOP;
1094 END IF;
1095
1096 x_upd_tbl (1).ERROR_TEXT := l_order_error;
1097 x_upd_tbl (1).success_flag := 'E';
1098 x_upd_tbl (1).order_line_id := p_reproc_line_rec.order_line_id;
1099 x_upd_tbl (1).ID := p_reproc_line_rec.ID;
1100 RAISE g_exception_halt_validation;
1101 END IF;
1102
1103 oks_renew_util_pub.get_renew_rules
1104 (p_api_version => 1.0,
1105 p_init_msg_list => 'T',
1106 x_return_status => l_return_status,
1107 x_msg_count => l_msg_count,
1108 x_msg_data => l_msg_data,
1109 p_chr_id => NULL,
1110 p_party_id => l_k_header_rec.party_id,
1111 p_org_id => l_k_header_rec.authoring_org_id,
1112 p_date => SYSDATE,
1113 p_rnrl_rec => NULL,
1114 x_rnrl_rec => l_rnrl_rec_out
1115 );
1116 fnd_file.put_line (fnd_file.LOG,
1117 'OC INTERFACE :- get_renew_rules status '
1118 || l_return_status
1119 );
1120
1121 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
1122 THEN
1123 FOR i IN 1 .. fnd_msg_pub.count_msg
1124 LOOP
1125 fnd_msg_pub.get (p_msg_index => -1,
1126 p_encoded => 'F',
1127 p_data => l_msg_data,
1128 p_msg_index_out => l_index
1129 );
1130 fnd_file.put_line (fnd_file.LOG,
1131 'RENEWAL RULE FAILURE ' || l_msg_data
1132 );
1133 END LOOP;
1134
1135 RAISE g_exception_halt_validation;
1136 END IF;
1137
1138 oks_integration_util_pub.create_k_order_details
1139 (p_header_id => l_k_header_rec.order_hdr_id,
1140 x_return_status => l_return_status,
1141 x_msg_count => l_msg_count,
1142 x_msg_data => l_msg_data
1143 );
1144 fnd_file.put_line (fnd_file.LOG,
1145 'OC INTERFACE :- get_K_order_details status '
1146 || l_return_status
1147 );
1148
1149 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
1150 THEN
1151 FOR i IN 1 .. fnd_msg_pub.count_msg
1152 LOOP
1153 fnd_msg_pub.get (p_msg_index => -1,
1154 p_encoded => 'F',
1155 p_data => l_msg_data,
1156 p_msg_index_out => l_index
1157 );
1158 fnd_file.put_line (fnd_file.LOG,
1159 'ORDER DETAIL FAILURE ' || l_msg_data
1160 );
1161 END LOOP;
1162
1163 RAISE g_exception_halt_validation;
1164 END IF;
1165
1166 oks_extwar_util_pvt.get_k_order_details
1167 (p_reproc_line_rec.order_line_id,
1168 l_renewal_rec
1169 );
1170 --mmadhavi added following logic for Partial Periods Computation
1171 oks_renew_util_pub.get_period_defaults
1172 (p_hdr_id => NULL,
1173 p_org_id => l_k_header_rec.authoring_org_id,
1174 x_period_type => l_period_type,
1175 x_period_start => l_period_start,
1176 x_price_uom => l_price_uom,
1177 x_return_status => l_return_status
1178 );
1179
1180 --25-JAN-2006 mchoudha for partial periods CR-003
1181 --All the extended warranty contracts created from order management will have Service
1182 --stamped on them if GCD is setup. Period Type will be pulled from GCD.
1183 IF l_period_start IS NOT NULL AND l_period_type IS NOT NULL
1184 THEN
1185 l_period_start := 'SERVICE';
1186 --22-MAR-2006 mchoudha Changes for partial periods CR3
1187 --Period type will be picked up from GCD and not hard coded
1188 --l_period_type := 'FIXED';
1189 END IF;
1190
1191 fnd_file.put_line (fnd_file.LOG,
1192 'OC INTERFACE :- get_period_defaults '
1193 || l_return_status
1194 );
1195 fnd_file.put_line
1196 (fnd_file.LOG,
1197 'OC INTERFACE :- get_period_defaults l_period_start '
1198 || l_period_start
1199 );
1200 fnd_file.put_line
1201 (fnd_file.LOG,
1202 'OC INTERFACE :- get_period_defaults l_period_type '
1203 || l_period_type
1204 );
1205
1206 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
1207 THEN
1208 FOR i IN 1 .. fnd_msg_pub.count_msg
1209 LOOP
1210 fnd_msg_pub.get (p_msg_index => -1,
1211 p_encoded => 'F',
1212 p_data => l_msg_data,
1213 p_msg_index_out => l_index
1214 );
1215 fnd_file.put_line (fnd_file.LOG,
1216 'get_period_defaults FAILURE ' || l_msg_data
1217 );
1218 END LOOP;
1219
1220 RAISE g_exception_halt_validation;
1221 END IF;
1222
1223 fnd_file.put_line (fnd_file.LOG,
1224 'OC INTERFACE :- Create K Hdr status '
1225 || l_return_status
1226 );
1227
1228 OPEN l_party_csr;
1229
1230 FETCH l_party_csr
1231 INTO l_party_name;
1232
1233 CLOSE l_party_csr;
1234
1235 fnd_file.put_line (fnd_file.LOG,
1236 'OC INTERFACE :- party name ' || l_party_name
1237 );
1238 /*Modified for ER 16039680 */
1239 IF l_k_line_rec.coverage_schd_id IS NOT NULL THEN
1240 IF fnd_profile.VALUE ('OKS_CONTRACT_CATEGORY') = 'SERVICE'
1241 THEN
1242 l_line_rec.warranty_flag := 'S';
1243 l_covd_rec.warranty_flag := 'S';
1244 l_hdr_rec.scs_code := 'SERVICE';
1245 l_hdr_rec.short_description :=
1246 'CUSTOMER: ' || l_party_name || ' Contract';
1247 ELSIF fnd_profile.VALUE ('OKS_CONTRACT_CATEGORY') = 'SUBSCRIPTION'
1248 THEN
1249 l_line_rec.warranty_flag := 'SU';
1250 l_hdr_rec.scs_code := 'SUBSCRIPTION';
1251 l_covd_rec.warranty_flag := 'SU';
1252 l_hdr_rec.short_description :=
1253 'CUSTOMER: ' || l_party_name || ' Contract';
1254 ELSIF fnd_profile.VALUE ('OKS_CONTRACT_CATEGORY') = 'WARRANTY'
1255 OR fnd_profile.VALUE ('OKS_CONTRACT_CATEGORY') IS NULL
1256 THEN
1257 l_line_rec.warranty_flag := 'E';
1258 l_hdr_rec.scs_code := 'WARRANTY';
1259 l_covd_rec.warranty_flag := 'E';
1260 l_hdr_rec.short_description :=
1261 'CUSTOMER: '
1262 || l_party_name
1263 || ' Warranty/Extended Warranty Contract';
1264 END IF;
1265 ELSE
1266 l_line_rec.warranty_flag := 'SU';
1267 l_hdr_rec.scs_code := 'SUBSCRIPTION';
1268 l_covd_rec.warranty_flag := 'SU';
1269 l_hdr_rec.short_description :=
1270 'CUSTOMER: ' || l_party_name || ' Contract';
1271 END IF;
1272 /*Modified for CLOUD ER 16039680*/
1273
1274 /*Modified for cloud ER*/
1275 --mmadhavi end Partial Periods logic
1276 IF l_renewal_rec.chr_id IS NOT NULL
1277 OR l_renewal_rec.link_chr_id IS NOT NULL
1278 THEN
1279 l_hdr_rec.merge_type := 'LTC';
1280 l_hdr_rec.merge_object_id :=
1281 NVL (l_renewal_rec.chr_id, l_renewal_rec.link_chr_id);
1282
1283 OPEN l_hdr_scs_csr (l_hdr_rec.merge_object_id);
1284
1285 FETCH l_hdr_scs_csr
1286 INTO l_merge_scs_code;
1287
1288 CLOSE l_hdr_scs_csr;
1289
1290 IF l_merge_scs_code ='SUBSCRIPTION' THEN
1291 l_line_rec.warranty_flag := 'SU';
1292 l_covd_rec.warranty_flag := 'SU';
1293 END IF;
1294
1295 ELSE
1296 OPEN get_item_type(l_cp_tbl (1).cp_id);
1297 FETCH get_item_type INTO l_contract_item_type_code;
1298 IF get_item_type%FOUND THEN
1299 IF l_contract_item_type_code ='SUBSCRIPTION' THEN
1300 OPEN Check_Contract_Exists(l_cp_tbl (1).cp_id,p_reproc_line_rec.order_id);
1301 FETCH Check_Contract_Exists INTO l_chr_id_old;
1302 IF Check_Contract_Exists%FOUND THEN
1303 l_hdr_rec.merge_type := 'LTC';
1304 l_hdr_rec.merge_object_id := l_chr_id_old;
1305 l_line_rec.warranty_flag := 'SU';
1306 l_covd_rec.warranty_flag := 'SU';
1307 ELSE
1308 l_hdr_rec.merge_type := 'NEW';
1309 l_hdr_rec.merge_object_id := NULL;
1310 END IF;
1311 CLOSE Check_Contract_Exists;
1312 ELSE
1313 l_hdr_rec.merge_type := 'NEW';
1314 l_hdr_rec.merge_object_id := NULL;
1315 END IF;
1316 ELSE
1317 l_hdr_rec.merge_type := 'NEW';
1318 l_hdr_rec.merge_object_id := NULL;
1319 END IF;
1320 END IF;
1321
1322 /*Modified for CLOUD ER 16039680*/
1323
1324 fnd_file.put_line (fnd_file.LOG,
1325 'OC INTERFACE :- l_hdr_rec.merge_object_id '
1326 || l_hdr_rec.merge_object_id
1327 );
1328
1329 fnd_file.put_line (fnd_file.LOG,
1330 'OC INTERFACE :- l_hdr_rec.merge_object_id '
1331 || l_hdr_rec.merge_type
1332 );
1333
1334 --changing l_extwar_rec.merge...to l_hdr_rec
1335 IF l_hdr_rec.merge_object_id IS NOT NULL
1336 THEN
1337 OPEN l_hdr_scs_csr (l_hdr_rec.merge_object_id);
1338
1339 FETCH l_hdr_scs_csr
1340 INTO l_hdr_scs_code;
1341
1342 CLOSE l_hdr_scs_csr;
1343
1344 IF l_contract_item_type_code <> 'SUBSCRIPTION' THEN
1345 IF l_hdr_scs_code <> l_hdr_rec.scs_code /* Commented for CLOUD ER fnd_profile.VALUE ('OKS_CONTRACT_CATEGORY') */
1346 THEN
1347 l_return_status := okc_api.g_ret_sts_error;
1348 --OKC_API.set_message(G_APP_NAME,'OKS_CONTRACT_CATEGORY','Cat',l_hdr_scs_code);
1349 fnd_message.set_name ('OKS', 'OKS_CONTRACT_CATEGORY');
1350 fnd_message.set_token (token => 'Cat',
1351 VALUE => l_hdr_scs_code);
1352 l_order_error := '#' || fnd_message.get_encoded || '#';
1353 x_upd_tbl (1).ERROR_TEXT := l_order_error;
1354 x_upd_tbl (1).success_flag := 'E';
1355 x_upd_tbl (1).order_line_id := p_reproc_line_rec.order_line_id;
1356 x_upd_tbl (1).ID := p_reproc_line_rec.ID;
1357 RAISE g_exception_halt_validation;
1358 END IF;
1359 END IF;
1360 END IF;
1361
1362 /*added for bugfix 14486901 by ansraj*/
1363 If l_hdr_rec.merge_object_id Is Not Null Then
1364 Open l_hdr_ste_csr(l_hdr_rec.merge_object_id);
1365 Fetch l_hdr_ste_csr into l_hdr_ste_code,l_k_number;
1366 Close l_hdr_ste_csr;
1367
1368 If l_hdr_ste_code = 'HOLD' Then
1369 l_return_status := OKC_API.G_RET_STS_ERROR;
1370 fnd_message.set_name('OKS','OKS_INVALID_STS');
1371 fnd_message.set_token(token => 'Token',value => l_k_number);
1372 l_order_error := '#'||fnd_message.get_encoded||'#';
1373 FND_FILE.PUT_LINE (FND_FILE.LOG, 'OC INTERFACE :- Error: Trying to add service line to a contract in Hold Status:'||l_return_status);
1374 x_upd_tbl(1).error_text := l_order_error;
1375 x_upd_tbl(1).success_flag := 'E';
1376 x_upd_tbl(1).order_line_id := p_reproc_line_rec.order_line_id;
1377 x_upd_tbl(1).id := p_reproc_line_rec.id;
1378 Raise G_EXCEPTION_HALT_VALIDATION;
1379 End if;
1380 End If;
1381 /*end of bugfix 14486901 */
1382
1383 l_contact_tbl.DELETE;
1384
1385 IF l_k_header_rec.invoice_to_contact_id IS NOT NULL
1386 THEN
1387 l_contact_tbl (1).party_role := 'CUSTOMER';
1388 l_contact_tbl (1).contact_role := 'BILLING';
1389 l_contact_tbl (1).contact_object_code := 'OKX_PCONTACT';
1390 l_contact_tbl (1).contact_id :=
1391 l_k_header_rec.invoice_to_contact_id;
1392 l_contact_tbl (1).flag := 'H';
1393 l_ptr1 := 2;
1394 ELSE
1395 l_ptr1 := 1;
1396 END IF;
1397
1398 FOR l_contact_rec IN l_contact_csr (p_reproc_line_rec.order_line_id)
1399 LOOP
1400 l_contact_tbl (l_ptr1).party_role := 'CUSTOMER';
1401 l_contact_tbl (l_ptr1).contact_role := l_contact_rec.cro_code;
1402 l_contact_tbl (l_ptr1).contact_object_code := 'OKX_PCONTACT';
1403 l_contact_tbl (l_ptr1).contact_id := l_contact_rec.object1_id1;
1404 l_contact_tbl (l_ptr1).flag := 'K'; -- changed 17-jul-2003
1405 l_ptr1 := l_ptr1 + 1;
1406 END LOOP;
1407
1408 --OKC_CONTEXT.SET_OKC_ORG_CONTEXT ( p_org_id => l_k_header_rec.authoring_org_id,
1409 -- p_organization_id => Null
1410 -- );
1411
1412 ---mmadhavi sales credit bug 4174921
1413 l_salescredit_tbl_in.DELETE;
1414 l_header_id := p_reproc_line_rec.order_id;
1415 oks_extwar_util_pvt.salescredit_header
1416 (p_order_hdr_id => p_reproc_line_rec.order_id,
1417 x_salescredit_tbl => l_salescredit_tbl_in,
1418 x_return_status => l_return_status
1419 );
1420 fnd_file.put_line (fnd_file.LOG,
1421 'OC INTERFACE :- SalesCredit at Header '
1422 || l_return_status
1423 );
1424
1425 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
1426 THEN
1427 FOR i IN 1 .. fnd_msg_pub.count_msg
1428 LOOP
1429 fnd_msg_pub.get (p_msg_index => -1,
1430 p_encoded => 'F',
1431 p_data => l_msg_data,
1432 p_msg_index_out => l_index
1433 );
1434 fnd_file.put_line (fnd_file.LOG,
1435 'READ SALES CREDIT ERROR IN HEADER'
1436 || l_msg_data
1437 );
1438 END LOOP;
1439
1440 RAISE g_exception_halt_validation;
1441 END IF;
1442
1443 --mmadhavi bug 4069827
1444
1445 -- rty_code has been added, (Extwarranty consolidation) vigandhi
1446 l_hdr_rec.contract_number := okc_api.g_miss_char;
1447 l_hdr_rec.rty_code := 'CONTRACTSERVICESORDER';
1448 l_hdr_rec.start_date := l_k_line_rec.srv_sdt;
1449 l_hdr_rec.end_date := l_k_line_rec.srv_edt;
1450 -- l_hdr_rec.sts_code := 'ACTIVE';
1451 l_hdr_rec.class_code := 'SVC';
1452 l_hdr_rec.authoring_org_id := l_k_header_rec.authoring_org_id;
1453 --l_hdr_rec.org_id := l_k_header_rec.org_id; --MMadhavi MOAC : need to add in rec type also
1454 l_hdr_rec.party_id := l_k_header_rec.party_id;
1455 l_hdr_rec.third_party_role := l_rnrl_rec_out.rle_code;
1456 l_hdr_rec.bill_to_id := l_k_header_rec.bill_to_id;
1457 l_hdr_rec.ship_to_id := l_k_header_rec.ship_to_id;
1458 l_hdr_rec.chr_group := l_rnrl_rec_out.cgp_new_id;
1459 l_hdr_rec.cust_po_number := l_k_header_rec.cust_po_number;
1460 l_hdr_rec.agreement_id := l_k_header_rec.agreement_id;
1461 --party name to be done
1462 l_hdr_rec.currency := l_k_header_rec.currency;
1463 l_hdr_rec.accounting_rule_id :=
1464 NVL (l_k_header_rec.accounting_rule_id, 1);
1465 l_hdr_rec.invoice_rule_id := NVL (l_k_header_rec.invoice_rule_id, -2);
1466 l_hdr_rec.order_hdr_id := l_k_header_rec.order_hdr_id;
1467 l_hdr_rec.price_list_id := l_k_header_rec.price_list_id;
1468 l_hdr_rec.payment_term_id := l_k_header_rec.hdr_payment_term_id;
1469 l_hdr_rec.renewal_type := l_renewal_rec.renewal_type;
1470 l_hdr_rec.RENEWAL_APPROVAL_FLAG := l_renewal_rec.RENEWAL_APPROVAL_FLAG; --Bug# 5173373
1471 l_hdr_rec.renewal_markup := l_renewal_rec.markup_percent;
1472 l_hdr_rec.renewal_pricing_type := l_renewal_rec.renewal_pricing_type;
1473 l_hdr_rec.renewal_price_list_id := l_renewal_rec.price_list_id1;
1474 l_hdr_rec.cvn_type := l_k_header_rec.hdr_cvn_type;
1475 l_hdr_rec.cvn_rate := l_k_header_rec.hdr_cvn_rate;
1476 l_hdr_rec.cvn_date := l_k_header_rec.hdr_cvn_date;
1477 l_hdr_rec.cvn_euro_rate := NULL;
1478 l_hdr_rec.tax_status_flag := l_k_header_rec.hdr_tax_status_flag;
1479 l_hdr_rec.tax_exemption_id := l_k_header_rec.hdr_tax_exemption_id;
1480 l_hdr_rec.renewal_type := l_renewal_rec.renewal_type;
1481 l_hdr_rec.RENEWAL_APPROVAL_FLAG := l_renewal_rec.RENEWAL_APPROVAL_FLAG; --Bug# 5173373
1482
1483 l_hdr_rec.renewal_pricing_type := l_renewal_rec.renewal_pricing_type;
1484 l_hdr_rec.renewal_price_list_id := l_renewal_rec.price_list_id1;
1485 l_hdr_rec.renewal_markup := l_renewal_rec.markup_percent;
1486 l_hdr_rec.renewal_po := l_renewal_rec.po_required_yn;
1487 l_hdr_rec.contact_id := l_k_header_rec.ship_to_contact_id;
1488 l_hdr_rec.qto_contact_id := l_renewal_rec.contact_id;
1489 l_hdr_rec.qto_email_id := l_renewal_rec.email_id;
1490 l_hdr_rec.qto_phone_id := l_renewal_rec.phone_id;
1491 l_hdr_rec.qto_fax_id := l_renewal_rec.fax_id;
1492 l_hdr_rec.qto_site_id := l_renewal_rec.site_id;
1493 l_hdr_rec.order_line_id := p_reproc_line_rec.order_line_id;
1494 l_hdr_rec.billing_profile_id := l_renewal_rec.billing_profile_id;
1495 --new parameter added -vigandhi (May29-02)
1496 l_hdr_rec.qcl_id := l_rnrl_rec_out.qcl_id;
1497 l_hdr_rec.salesrep_id := l_k_header_rec.salesrep_id;
1498 l_hdr_rec.pdf_id := l_rnrl_rec_out.pdf_id;
1499 l_hdr_rec.ccr_number := l_k_header_rec.ccr_number;
1500 l_hdr_rec.ccr_exp_date := l_k_header_rec.ccr_exp_date;
1501 --mmadhavi added for Partial Periods Computation
1502 l_hdr_rec.period_start := l_period_start;
1503 l_hdr_rec.period_type := l_period_type;
1504 l_hdr_rec.price_uom := l_price_uom;
1505
1506 --mmadhavi end Partial Periods Computation
1507
1508 /*modified for bug 16562247*/
1509 IF l_hdr_rec.start_date > SYSDATE
1510 THEN
1511 oks_extwarprgm_pvt.get_sts_code ('SIGNED',
1512 NULL,
1513 l_ste_code,
1514 l_sts_code
1515 );
1516 ELSIF l_hdr_rec.start_date <= SYSDATE AND l_hdr_rec.end_date >= SYSDATE THEN
1517 oks_extwarprgm_pvt.get_sts_code ('ACTIVE',
1518 NULL,
1519 l_ste_code,
1520 l_sts_code
1521 );
1522 ELSIF l_hdr_rec.end_date < SYSDATE THEN
1523 oks_extwarprgm_pvt.get_sts_code ('EXPIRED',
1524 NULL,
1525 l_ste_code,
1526 l_sts_code
1527 );
1528 END IF;
1529
1530 l_hdr_rec.sts_code := l_sts_code;
1531 -- Added by JVARGHES for 12.0 enhancements.
1532 l_hdr_rec.renewal_status := 'COMPLETE';
1533 l_hdr_rec.grace_period := l_rnrl_rec_out.grace_period; --Bug# 4549857
1534 l_hdr_rec.grace_duration := l_rnrl_rec_out.grace_duration;
1535 --Bug# 4549857
1536 --
1537
1538 -- Added as part of bug fix 5008188
1539 -- l_hdr_rec.tax_classification_code := l_k_header_rec.tax_classification_code; -- Fix for bug# 5403061
1540 l_hdr_rec.tax_classification_code := NULL; -- Fix for bug# 5403061
1541 l_hdr_rec.exemption_certificate_number := l_k_header_rec.exemption_certificate_number;
1542 l_hdr_rec.exemption_reason_code := l_k_header_rec.exemption_reason_code;
1543 --
1544
1545 /* Added by sjanakir for Bug#7445270 */
1546 l_hdr_rec.ar_interface_yn := 'Y';
1547
1548 oks_extwarprgm_pvt.create_k_hdr
1549 (p_k_header_rec => l_hdr_rec,
1550 p_contact_tbl => l_contact_tbl,
1551 p_salescredit_tbl_in => l_salescredit_tbl_in,
1552 --mmadhavi bug 4174921
1553 p_caller => 'OC',
1554 x_order_error => l_order_error,
1555 x_chr_id => l_chrid,
1556 x_return_status => l_return_status,
1557 x_msg_count => l_msg_count,
1558 x_msg_data => l_msg_data
1559 );
1560 fnd_file.put_line (fnd_file.LOG,
1561 'OC INTERFACE :- Create K Hdr ID = '
1562 || TO_CHAR (l_chrid)
1563 );
1564 fnd_file.put_line (fnd_file.LOG,
1565 'OC INTERFACE :- Create K Hdr status '
1566 || l_return_status
1567 );
1568
1569 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
1570 THEN
1571 IF l_order_error IS NULL
1572 THEN
1573 l_order_error := '#';
1574
1575 FOR i IN 1 .. fnd_msg_pub.count_msg
1576 LOOP
1577 fnd_msg_pub.get (p_msg_index => i,
1578 p_encoded => 'T',
1579 p_data => l_msg_data,
1580 p_msg_index_out => l_index
1581 );
1582 l_order_error := l_order_error || l_msg_data || '#';
1583 fnd_message.set_encoded (l_msg_data);
1584 l_msg_data := fnd_message.get;
1585 fnd_file.put_line (fnd_file.LOG,
1586 'Create_k_hdr FAILURE ' || l_msg_data
1587 );
1588 END LOOP;
1589 END IF;
1590
1591 x_upd_tbl (1).ERROR_TEXT := l_order_error;
1592 x_upd_tbl (1).success_flag := 'E';
1593 x_upd_tbl (1).order_line_id := p_reproc_line_rec.order_line_id;
1594 x_upd_tbl (1).ID := p_reproc_line_rec.ID;
1595 RAISE g_exception_halt_validation;
1596 END IF;
1597
1598 x_upd_tbl (1).contract_id := l_chrid;
1599
1600 -- Fix for Bug 2805292
1601 IF l_hdr_rec.order_hdr_id IS NOT NULL
1602 AND l_hdr_rec.merge_type = 'NEW'
1603 THEN
1604 okc_oc_int_pub.create_k_relationships
1605 (p_api_version => l_api_version,
1606 p_init_msg_list => l_init_msg_list,
1607 p_commit => okc_api.g_false,
1608 p_sales_contract_id => okc_api.g_miss_num,
1609 p_service_contract_id => l_chrid,
1610 p_quote_id => okc_api.g_miss_num,
1611 p_quote_line_tab => okc_oc_int_pub.g_miss_ql_tab,
1612 p_order_id => l_hdr_rec.order_hdr_id,
1613 p_order_line_tab => okc_oc_int_pub.g_miss_ol_tab,
1614 p_trace_mode => NULL,
1615 x_return_status => l_return_status,
1616 x_msg_count => l_msg_count,
1617 x_msg_data => l_msg_data
1618 );
1619
1620 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
1621 THEN
1622 fnd_log.STRING
1623 (fnd_log.level_event,
1624 g_module_current || '.ORDER_REPROCESS.CREATE_CONTRACT',
1625 ' okc_oc_int_pub.create_k_relationships(Return status = '
1626 || l_return_status
1627 || ')'
1628 );
1629 END IF;
1630
1631 --OKS_RENEW_PVT.DEBUG_LOG( '(OKS_EXTWARPRGM_PVT).okc_oc_int_pub.create_k_relationships '|| l_return_status);
1632 fnd_file.put_line
1633 (fnd_file.LOG,
1634 '(OKS_EXTWARPRGM_PVT).okc_oc_int_pub.create_k_relationships '
1635 || l_return_status
1636 );
1637
1638 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
1639 THEN
1640 FOR i IN 1 .. fnd_msg_pub.count_msg
1641 LOOP
1642 fnd_msg_pub.get (p_msg_index => -1,
1643 p_encoded => 'F',
1644 p_data => l_msg_data,
1645 p_msg_index_out => l_index
1646 );
1647 fnd_file.put_line (fnd_file.LOG,
1648 'K HDR CREATION ERROR ' || l_msg_data
1649 );
1650 END LOOP;
1651
1652 RAISE g_exception_halt_validation;
1653 END IF;
1654 END IF;
1655
1656 oks_extwar_util_pvt.salescredit
1657 (p_order_line_id => p_reproc_line_rec.order_line_id,
1658 x_salescredit_tbl => l_salescredit_tbl_in,
1659 x_return_status => l_return_status
1660 );
1661 fnd_file.put_line (fnd_file.LOG,
1662 'OC INTERFACE :- SalesCredit ' || l_return_status
1663 );
1664
1665 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
1666 THEN
1667 FOR i IN 1 .. fnd_msg_pub.count_msg
1668 LOOP
1669 fnd_msg_pub.get (p_msg_index => -1,
1670 p_encoded => 'F',
1671 p_data => l_msg_data,
1672 p_msg_index_out => l_index
1673 );
1674 fnd_file.put_line (fnd_file.LOG,
1675 'READ SALES CREDIT ERROR ' || l_msg_data
1676 );
1677 END LOOP;
1678
1679 RAISE g_exception_halt_validation;
1680 END IF;
1681
1682
1683 -- If the line level credit sales credit is not specified for an order line
1684 -- in OM, then default the header sales credits to the line as well
1685
1686 IF l_salescredit_tbl_in.count = 0 THEN
1687 oks_extwar_util_pvt.salescredit_header
1688 (p_order_hdr_id => p_reproc_line_rec.order_id,
1689 x_salescredit_tbl => l_salescredit_tbl_in,
1690 x_return_status => l_return_status
1691 );
1692 fnd_file.put_line (fnd_file.LOG,
1693 'OC INTERFACE :- SalesCredit at Header - defaulting for order line id = '||p_reproc_line_rec.order_id
1694 || l_return_status
1695 );
1696
1697 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
1698 THEN
1699 FOR i IN 1 .. fnd_msg_pub.count_msg
1700 LOOP
1701 fnd_msg_pub.get (p_msg_index => -1,
1702 p_encoded => 'F',
1703 p_data => l_msg_data,
1704 p_msg_index_out => l_index
1705 );
1706 fnd_file.put_line (fnd_file.LOG,
1707 'READ SALES CREDIT ERROR IN HEADER'
1708 || l_msg_data
1709 );
1710 END LOOP;
1711
1712 RAISE g_exception_halt_validation;
1713 END IF;
1714 END IF;
1715
1716 fnd_file.put_line (fnd_file.LOG,
1717 'OC_INT ...... Cov Temp Id = '
1718 || NVL (l_k_line_rec.coverage_schd_id, -12345)
1719 );
1720 l_line_rec.k_id := l_chrid;
1721 l_line_rec.k_line_number := okc_api.g_miss_char;
1722 -- l_line_rec.line_sts_code := 'ACTIVE';
1723 l_line_rec.cust_account := l_k_line_rec.customer_acct_id;
1724 l_line_rec.org_id := l_k_header_rec.authoring_org_id;
1725 -- mmadhavi should change to org_id for MOAC
1726 l_line_rec.srv_id := l_k_line_rec.srv_id;
1727 l_line_rec.object_name := l_k_line_rec.srv_desc;
1728 l_line_rec.srv_segment1 := l_k_line_rec.srv_segment1;
1729 l_line_rec.srv_desc := l_k_line_rec.srv_desc;
1730 l_line_rec.srv_sdt := l_k_line_rec.srv_sdt;
1731 l_line_rec.srv_edt := l_k_line_rec.srv_edt;
1732 l_line_rec.bill_to_id := l_k_line_rec.bill_to_id;
1733 l_line_rec.ship_to_id := l_k_line_rec.ship_to_id;
1734 l_line_rec.order_line_id := p_reproc_line_rec.order_line_id;
1735 --l_line_rec.warranty_flag := 'E';
1736 l_line_rec.coverage_template_id := l_k_line_rec.coverage_schd_id;
1737 l_line_rec.currency := l_k_header_rec.currency;
1738 l_line_rec.line_renewal_type := l_renewal_rec.line_renewal_type;
1739 l_line_rec.accounting_rule_id := NVL (l_k_line_rec.accounting_rule_id, 1);
1740 l_line_rec.invoicing_rule_id := NVL (l_k_line_rec.invoicing_rule_id, -2);
1741 l_line_rec.SOURCE := 'NEW';
1742 l_line_rec.upg_orig_system_ref := 'ORDER';
1743 l_line_rec.upg_orig_system_ref_id := NULL;
1744 l_line_rec.commitment_id := l_k_line_rec.commitment_id;
1745 --l_line_rec.tax_amount := l_k_line_rec.tax_amount;
1746 l_line_rec.ln_price_list_id := l_k_line_rec.ln_price_list_id;
1747 --22-NOV-2005 mchoudha PPC
1748 l_line_rec.price_uom := l_price_uom;
1749
1750 --End PPC
1751 /*modified for bug 16562247*/
1752 IF l_line_rec.srv_sdt > SYSDATE
1753 THEN
1754 oks_extwarprgm_pvt.get_sts_code ('SIGNED',
1755 NULL,
1756 l_ste_code,
1757 l_sts_code
1758 );
1759 ELSIF l_line_rec.srv_sdt <= SYSDATE and l_line_rec.srv_edt >= SYSDATE THEN
1760 oks_extwarprgm_pvt.get_sts_code ('ACTIVE',
1761 NULL,
1762 l_ste_code,
1763 l_sts_code
1764 );
1765 ELSIF l_line_rec.srv_edt < SYSDATE THEN
1766 oks_extwarprgm_pvt.get_sts_code ('EXPIRED',
1767 NULL,
1768 l_ste_code,
1769 l_sts_code
1770 );
1771 END IF;
1772
1773 l_line_rec.line_sts_code := l_sts_code;
1774 -- Added by JVARGHES for 12.0 enhancements
1775 /*Modified for CLOUD ER 16039680*/
1776 IF l_k_line_rec.coverage_schd_id IS NULL THEN
1777 l_line_rec.standard_cov_yn := NULL;
1778 g_last_oe_order_line_id :=p_reproc_line_rec.order_line_id;
1779 ELSE
1780 l_line_rec.standard_cov_yn := 'Y';
1781 END IF;
1782 --
1783 -- Added as part of bug fix 5008188
1784 l_line_rec.tax_classification_code := l_k_line_rec.tax_classification_code;
1785 l_line_rec.exemption_certificate_number := l_k_line_rec.exemption_certificate_number;
1786 l_line_rec.exemption_reason_code := l_k_line_rec.exemption_reason_code;
1787 l_line_rec.tax_status := l_k_line_rec.tax_status;
1788 --
1789 oks_extwarprgm_pvt.create_k_service_lines
1790 (p_k_line_rec => l_line_rec,
1791 p_contact_tbl => l_contact_tbl,
1792 p_salescredit_tbl_in => l_salescredit_tbl_in,
1793 p_caller => 'OC',
1794 x_order_error => l_order_error,
1795 x_service_line_id => l_lineid,
1796 x_return_status => l_return_status,
1797 x_msg_count => l_msg_count,
1798 x_msg_data => l_msg_data
1799 );
1800 fnd_file.put_line (fnd_file.LOG,
1801 'OC INTERFACE :- Create K Line ID = '
1802 || NVL (l_lineid, -1234)
1803 );
1804 fnd_file.put_line (fnd_file.LOG,
1805 'OC INTERFACE :- Create K Line status '
1806 || l_return_status
1807 );
1808
1809 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
1810 THEN
1811 IF l_order_error IS NULL
1812 THEN
1813 l_order_error := '#';
1814
1815 FOR i IN 1 .. fnd_msg_pub.count_msg
1816 LOOP
1817 fnd_msg_pub.get (p_msg_index => i,
1818 p_encoded => 'T',
1819 p_data => l_msg_data,
1820 p_msg_index_out => l_index
1821 );
1822 l_order_error := l_order_error || l_msg_data || '#';
1823 fnd_message.set_encoded (l_msg_data);
1824 l_msg_data := fnd_message.get;
1825 fnd_file.put_line (fnd_file.LOG,
1826 'Create_k_service_line FAILURE '
1827 || l_msg_data
1828 );
1829 END LOOP;
1830 END IF;
1831
1832 x_upd_tbl (1).ERROR_TEXT := l_order_error;
1833 x_upd_tbl (1).success_flag := 'E';
1834 x_upd_tbl (1).order_line_id := p_reproc_line_rec.order_line_id;
1835 x_upd_tbl (1).ID := p_reproc_line_rec.ID;
1836 fnd_file.put_line (fnd_file.LOG,
1837 'Inserting error msg ...'
1838 || x_upd_tbl (1).ERROR_TEXT
1839 );
1840 RAISE g_exception_halt_validation;
1841 END IF;
1842
1843 --Copy line id for Reprocessing
1844 x_upd_tbl (1).contract_line_id := l_lineid;
1845 l_error := 'N';
1846 IF l_k_line_rec.coverage_schd_id IS NOT NULL THEN /*Added for CLOUD ER 16039680*/
1847 FOR cp_ctr IN 1 .. l_cp_tbl.COUNT
1848 LOOP
1849 IF (l_error <> 'Y')
1850 THEN
1851 OPEN l_cp_csr (l_cp_tbl (cp_ctr).cp_id);
1852
1853 FETCH l_cp_csr
1854 INTO l_cp_id, l_cp_inventory, l_cp_name, l_cp_desc, l_cp_qty,
1855 l_cp_uom;
1856
1857 CLOSE l_cp_csr;
1858
1859 oks_extwar_util_pvt.get_pricing_attributes
1860 (p_order_line_id => p_reproc_line_rec.order_line_id,
1861 x_pricing_att => l_pricing_attributes_in,
1862 x_return_status => l_return_status
1863 );
1864
1865 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
1866 THEN
1867 FOR i IN 1 .. fnd_msg_pub.count_msg
1868 LOOP
1869 fnd_msg_pub.get (p_msg_index => -1,
1870 p_encoded => 'F',
1871 p_data => l_msg_data,
1872 p_msg_index_out => l_index
1873 );
1874 fnd_file.put_line (fnd_file.LOG,
1875 'READ PRICING ATTRIBS ERROR '
1876 || l_msg_data
1877 );
1878 END LOOP;
1879
1880 RAISE g_exception_halt_validation;
1881 END IF;
1882
1883 IF fnd_profile.VALUE ('OKS_ITEM_DISPLAY_PREFERENCE') =
1884 'DISPLAY_NAME'
1885 THEN
1886 l_covd_rec.product_segment1 := l_cp_desc;
1887 l_covd_rec.product_desc := l_cp_name;
1888 ELSE
1889 l_covd_rec.product_segment1 := l_cp_name;
1890 l_covd_rec.product_desc := l_cp_desc;
1891 END IF;
1892
1893 -- rty_code has been added (Extwarranty consolidation) vigandhi
1894 ----mmadhavi added bom_explosion
1895 OPEN l_serv_ref_csr (p_reproc_line_rec.order_line_id);
1896
1897 FETCH l_serv_ref_csr
1898 INTO l_serv_ref_rec;
1899
1900 CLOSE l_serv_ref_csr;
1901
1902 /*cgopinee bugfix for bug8276025*/
1903 /*Use cursor l_ordered_qty_csr bug#13655785 */
1904 OPEN l_ordered_qty_csr(p_reproc_line_rec.order_line_id);
1905 FETCH l_ordered_qty_csr into l_ordered_qty;
1906 CLOSE l_ordered_qty_csr;
1907
1908 IF l_serv_ref_rec.service_reference_type_code = 'ORDER'
1909 THEN
1910 OPEN l_inv_csr (l_serv_ref_rec.service_reference_line_id);
1911
1912 FETCH l_inv_csr
1913 INTO l_inv_item_id;
1914
1915 CLOSE l_inv_csr;
1916
1917 IF l_inv_item_id = l_cp_inventory
1918 THEN
1919 l_covd_rec.negotiated_amount :=
1920 l_k_line_rec.unit_selling_price * l_cp_qty;
1921 l_covd_rec.list_price := l_k_line_rec.unit_selling_price;
1922 /* Divide the tax amount only when the ordered_qty is not same as cp_qty
1923 due to profile CSI_AUTO_SPLIT_INSTANCES - bug13655785 */
1924
1925 IF (l_ordered_qty<>l_cp_qty)
1926 THEN
1927 l_covd_rec.tax_amount := l_k_line_rec.tax_amount * (l_cp_qty/l_ordered_qty); -- Bug# 13655785
1928 ELSE
1929 l_covd_rec.tax_amount := l_k_line_rec.tax_amount;
1930 END IF;
1931
1932 l_BOM_instance_flag := 'Y'; -- Bug# 5165947
1933
1934 ELSE
1935 l_covd_rec.negotiated_amount := 0;
1936 l_covd_rec.list_price := 0;
1937 l_covd_rec.tax_amount := 0; -- Bug# 5274971
1938 l_BOM_instance_flag := 'N'; -- Bug# 5165947
1939
1940 END IF;
1941 ELSE
1942 l_covd_rec.negotiated_amount :=
1943 l_k_line_rec.unit_selling_price * l_cp_qty;
1944 l_covd_rec.list_price := l_k_line_rec.unit_selling_price;
1945 l_covd_rec.tax_amount := l_k_line_rec.tax_amount; -- Bug# 5274971
1946 l_BOM_instance_flag := 'Y'; -- Bug# 5165947
1947
1948 END IF;
1949
1950 -- mmadhavi end of BOM explosion
1951 l_covd_rec.k_id := l_chrid;
1952 l_covd_rec.attach_2_line_id := l_lineid;
1953 l_covd_rec.line_number := okc_api.g_miss_char;
1954 --l_covd_rec.product_sts_code := 'ACTIVE';
1955 l_covd_rec.customer_product_id := l_cp_tbl (cp_ctr).cp_id;
1956 --l_covd_rec.Product_Segment1 := l_cp_name;
1957 --l_covd_rec.Product_Desc := l_cp_desc;
1958 --bug 3761489
1959 l_covd_rec.prod_item_id := l_cp_inventory;
1960 --bug 3761489
1961 l_covd_rec.product_start_date := l_k_line_rec.srv_sdt;
1962 l_covd_rec.product_end_date := l_k_line_rec.srv_edt;
1963 l_covd_rec.quantity := l_cp_qty;
1964 l_covd_rec.uom_code := l_cp_uom;
1965 --l_covd_rec.negotiated_amount := l_k_line_rec.unit_selling_price * l_cp_qty;
1966 --l_covd_rec.warranty_flag := 'E';
1967 l_covd_rec.line_renewal_type := l_renewal_rec.line_renewal_type;
1968 --l_covd_rec.list_price := l_k_line_rec.unit_selling_price;
1969 l_covd_rec.currency_code := l_k_header_rec.currency;
1970 l_covd_rec.order_line_id := p_reproc_line_rec.order_line_id;
1971 l_covd_rec.attach_2_line_desc := l_k_line_rec.srv_desc;
1972 l_covd_rec.rty_code := 'CONTRACTSERVICESORDER';
1973 l_covd_rec.upg_orig_system_ref := 'ORDER_LINE';
1974 l_covd_rec.upg_orig_system_ref_id :=
1975 p_reproc_line_rec.order_line_id;
1976
1977 -- Bug# 5274971
1978 -- l_covd_rec.tax_amount := l_k_line_rec.tax_amount;
1979 --
1980 --22-NOV-2005 mchoudha PPC
1981 l_covd_rec.toplvl_uom_code := l_k_line_rec.pricing_quantity_uom;
1982 l_covd_rec.price_uom := l_k_line_rec.order_quantity_uom;
1983 --mchoudha added for bug#5233956
1984 l_covd_rec.toplvl_price_qty := l_k_line_rec.pricing_quantity;
1985
1986 --End PPC
1987
1988 /*modified for bug 16562247*/
1989 IF l_covd_rec.product_start_date > SYSDATE
1990 THEN
1991 oks_extwarprgm_pvt.get_sts_code ('SIGNED',
1992 NULL,
1993 l_ste_code,
1994 l_sts_code
1995 );
1996 ELSIF l_covd_rec.product_start_date <= SYSDATE AND l_covd_rec.product_end_date >= SYSDATE THEN
1997 oks_extwarprgm_pvt.get_sts_code ('ACTIVE',
1998 NULL,
1999 l_ste_code,
2000 l_sts_code
2001 );
2002 ELSIF l_covd_rec.product_end_date < SYSDATE THEN
2003 oks_extwarprgm_pvt.get_sts_code ('EXPIRED',
2004 NULL,
2005 l_ste_code,
2006 l_sts_code
2007 );
2008 END IF;
2009
2010 l_covd_rec.product_sts_code := l_sts_code;
2011 oks_extwarprgm_pvt.create_k_covered_levels
2012 (p_k_covd_rec => l_covd_rec,
2013 p_price_attribs => l_pricing_attributes_in,
2014 p_caller => 'OC',
2015 x_order_error => l_order_error,
2016 x_covlvl_id => l_covlvl_id,
2017 x_update_line => l_update_line,
2018 x_return_status => l_return_status,
2019 x_msg_count => l_msg_count,
2020 x_msg_data => l_msg_data
2021 );
2022
2023 -- Added for fix of bug# 5165947
2024
2025 fnd_file.put_line
2026 (fnd_file.LOG,
2027 'l_BOM_instance_flag = '|| l_BOM_instance_flag
2028 );
2029
2030 IF l_BOM_instance_flag = 'Y' THEN
2031 l_BOM_instance_id := l_covlvl_id;
2032
2033 fnd_file.put_line
2034 (fnd_file.LOG,
2035 'l_BOM_CovLvl_id = '|| TO_CHAR (l_BOM_instance_id)
2036 );
2037
2038 END IF;
2039
2040 IF l_update_line = 'Y' THEN
2041 l_eff_line_upd_flag := 'Y';
2042
2043 fnd_file.put_line
2044 (fnd_file.LOG,
2045 'l_eff_line_upd_flag = '|| l_eff_line_upd_flag
2046 );
2047
2048 END IF;
2049
2050 -- Added for fix of bug# 5165947
2051
2052 fnd_file.put_line
2053 (fnd_file.LOG,
2054 'OC INTERFACE :- Create K Covd Line Subline ID = '
2055 || TO_CHAR (l_covlvl_id)
2056 );
2057 fnd_file.put_line
2058 (fnd_file.LOG,
2059 'OC INTERFACE :- Create K Covd Line status '
2060 || l_return_status
2061 );
2062
2063 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
2064 THEN
2065 IF l_order_error IS NULL
2066 THEN
2067 l_order_error := '#';
2068
2069 FOR i IN 1 .. fnd_msg_pub.count_msg
2070 LOOP
2071 fnd_msg_pub.get (p_msg_index => i,
2072 p_encoded => 'T',
2073 p_data => l_msg_data,
2074 p_msg_index_out => l_index
2075 );
2076 l_order_error := l_order_error || l_msg_data || '#';
2077 fnd_message.set_encoded (l_msg_data);
2078 l_msg_data := fnd_message.get;
2079 fnd_file.put_line
2080 (fnd_file.LOG,
2081 'Create_K_Covered_levels FAILURE '
2082 || l_msg_data
2083 );
2084 END LOOP;
2085 END IF;
2086
2087 x_upd_tbl (1).ERROR_TEXT := l_order_error;
2088 x_upd_tbl (1).success_flag := 'E';
2089 x_upd_tbl (1).order_line_id :=
2090 p_reproc_line_rec.order_line_id;
2091 x_upd_tbl (1).ID := p_reproc_line_rec.ID;
2092 l_error := 'Y';
2093 RAISE g_exception_halt_validation;
2094 END IF;
2095
2096 x_upd_tbl (cp_ctr).subline_id := l_covlvl_id;
2097 x_upd_tbl (cp_ctr).contract_line_id := l_lineid;
2098 x_upd_tbl (cp_ctr).contract_id := l_chrid;
2099
2100 OPEN l_lndates_csr (l_lineid);
2101
2102 FETCH l_lndates_csr
2103 INTO l_lndates_rec;
2104
2105 CLOSE l_lndates_csr;
2106
2107 OPEN l_hdrdates_csr (l_chrid);
2108
2109 FETCH l_hdrdates_csr
2110 INTO l_hdrdates_rec;
2111
2112 CLOSE l_hdrdates_csr;
2113
2114 OPEN l_refnum_csr (l_cp_tbl (cp_ctr).cp_id);
2115
2116 FETCH l_refnum_csr
2117 INTO l_ref_num;
2118
2119 CLOSE l_refnum_csr;
2120
2121 l_insthist_rec.instance_id := l_cp_tbl (cp_ctr).cp_id;
2122 l_insthist_rec.transaction_type := 'NEW';
2123 l_insthist_rec.transaction_date := SYSDATE;
2124 l_insthist_rec.reference_number := l_ref_num;
2125 oks_ins_pvt.insert_row (p_api_version => 1.0,
2126 p_init_msg_list => 'T',
2127 x_return_status => l_return_status,
2128 x_msg_count => l_msg_count,
2129 x_msg_data => l_msg_data,
2130 p_insv_rec => l_insthist_rec,
2131 x_insv_rec => x_insthist_rec
2132 );
2133 fnd_file.put_line (fnd_file.LOG,
2134 ' instance history Status : '
2135 || l_return_status
2136 );
2137 x_return_status := l_return_status;
2138
2139 IF NOT l_return_status = okc_api.g_ret_sts_success
2140 THEN
2141 x_return_status := l_return_status;
2142 RAISE g_exception_halt_validation;
2143 END IF;
2144
2145 l_inst_dtls_rec.ins_id := x_insthist_rec.ID;
2146 l_inst_dtls_rec.transaction_date := SYSDATE;
2147 l_inst_dtls_rec.transaction_type := 'NEW';
2148 l_inst_dtls_rec.instance_id_new := l_cp_tbl (cp_ctr).cp_id;
2149 l_inst_dtls_rec.instance_amt_new :=
2150 l_covd_rec.negotiated_amount;
2151 --l_k_line_rec.unit_selling_price * l_cp_qty;
2152 l_inst_dtls_rec.instance_qty_new := l_cp_qty;
2153 l_inst_dtls_rec.new_contract_id := l_chrid;
2154 l_inst_dtls_rec.new_contact_start_date :=
2155 l_hdrdates_rec.start_date;
2156 l_inst_dtls_rec.new_contract_end_date :=
2157 l_hdrdates_rec.end_date;
2158 l_inst_dtls_rec.new_service_line_id := l_lineid;
2159 l_inst_dtls_rec.new_service_start_date :=
2160 l_lndates_rec.start_date;
2161 l_inst_dtls_rec.new_service_end_date := l_lndates_rec.end_date;
2162 l_inst_dtls_rec.new_subline_id := l_covlvl_id;
2163 l_inst_dtls_rec.new_subline_start_date := l_k_line_rec.srv_sdt;
2164 l_inst_dtls_rec.new_subline_end_date := l_k_line_rec.srv_edt;
2165 l_inst_dtls_rec.new_customer := l_k_line_rec.customer_acct_id;
2166 l_inst_dtls_rec.new_k_status := l_hdrdates_rec.sts_code;
2167 oks_ihd_pvt.insert_row (p_api_version => 1.0,
2168 p_init_msg_list => 'T',
2169 x_return_status => l_return_status,
2170 x_msg_count => l_msg_count,
2171 x_msg_data => l_msg_data,
2172 p_ihdv_rec => l_inst_dtls_rec,
2173 x_ihdv_rec => x_inst_dtls_rec
2174 );
2175 fnd_file.put_line (fnd_file.LOG,
2176 ' instance history details Status : '
2177 || l_return_status
2178 );
2179 x_return_status := l_return_status;
2180
2181 IF NOT l_return_status = okc_api.g_ret_sts_success
2182 THEN
2183 x_return_status := l_return_status;
2184 RAISE g_exception_halt_validation;
2185 END IF;
2186 ELSE
2187 EXIT;
2188 END IF;
2189 END LOOP;
2190
2191 --Added for fix of bug# 5165947
2192
2193 END IF; /*Added for CLOUD ER*/
2194
2195 /*Added for CLOUD ER*/
2196 IF l_k_line_rec.coverage_schd_id IS NULL THEN
2197
2198 l_crjv_tbl_in (1).cle_id := l_lineid;
2199 l_crjv_tbl_in (1).chr_id := l_chrid; -- Fix for Bug 2844603
2200 l_crjv_tbl_in (1).object1_id1 := p_reproc_line_rec.order_line_id;
2201 l_crjv_tbl_in (1).object1_id2 := '#';
2202 l_crjv_tbl_in (1).jtot_object1_code := 'OKX_ORDERLINE';
2203 l_crjv_tbl_in(1).rty_code := 'CONTRACTSERVICESORDER';
2204
2205 okc_k_rel_objs_pub.create_row (p_api_version => l_api_version,
2206 p_init_msg_list => l_init_msg_list,
2207 x_return_status => l_return_status,
2208 x_msg_count => x_msg_count,
2209 x_msg_data => x_msg_data,
2210 p_crjv_tbl => l_crjv_tbl_in,
2211 x_crjv_tbl => l_crjv_tbl_out
2212 );
2213
2214 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
2215 THEN
2216 fnd_log.STRING
2217 (fnd_log.level_event,
2218 g_module_current
2219 || '.Create_Obj_Rel.external_call_line.after',
2220 'okc_k_rel_objs_pub.create_row(Return status = '
2221 || l_return_status
2222 || ')'
2223 );
2224 END IF;
2225
2226 IF l_return_status = 'S'
2227 THEN
2228 x_return_status := l_return_status;
2229 END IF;
2230
2231
2232 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
2233 THEN
2234 fnd_log.STRING
2235 (fnd_log.level_event,
2236 g_module_current
2237 || '.Create_Subscription_Line.create.object_relation',
2238 'create_obj_rel(Return status = '
2239 || l_return_status
2240 || ')'
2241 );
2242 END IF;
2243
2244 IF NOT l_return_status = okc_api.g_ret_sts_success
2245 THEN
2246 okc_api.set_message (g_app_name,
2247 g_required_value,
2248 g_col_name_token,
2249 'Order Line Id ( SUBSCRIPTION LINE)'
2250 );
2251 RAISE g_exception_halt_validation;
2252 END IF;
2253
2254
2255 oks_extwar_util_pvt.get_pricing_attributes
2256 (p_order_line_id => p_reproc_line_rec.order_line_id,
2257 x_pricing_att => l_pricing_attributes_in,
2258 x_return_status => l_return_status
2259 );
2260
2261 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
2262 THEN
2263 FOR i IN 1 .. fnd_msg_pub.count_msg
2264 LOOP
2265 fnd_msg_pub.get (p_msg_index => -1,
2266 p_encoded => 'F',
2267 p_data => l_msg_data,
2268 p_msg_index_out => l_index
2269 );
2270 fnd_file.put_line (fnd_file.LOG,
2271 'READ PRICING ATTRIBS ERROR '
2272 || l_msg_data
2273 );
2274 END LOOP;
2275
2276 RAISE g_exception_halt_validation;
2277 END IF;
2278
2279
2280 IF l_pricing_attributes_in.pricing_context IS NOT NULL
2281 THEN
2282 l_pavv_tbl_in (1).cle_id := l_lineid;
2283 l_pavv_tbl_in (1).flex_title := 'QP_ATTR_DEFNS_PRICING';
2284 l_pavv_tbl_in (1).pricing_context := l_pricing_attributes_in.pricing_context;
2285 l_pavv_tbl_in (1).pricing_attribute1 :=
2286 l_pricing_attributes_in.pricing_attribute1;
2287 l_pavv_tbl_in (1).pricing_attribute2 :=
2288 l_pricing_attributes_in.pricing_attribute2;
2289 l_pavv_tbl_in (1).pricing_attribute3 :=
2290 l_pricing_attributes_in.pricing_attribute3;
2291 l_pavv_tbl_in (1).pricing_attribute4 :=
2292 l_pricing_attributes_in.pricing_attribute4;
2293 l_pavv_tbl_in (1).pricing_attribute5 :=
2294 l_pricing_attributes_in.pricing_attribute5;
2295 l_pavv_tbl_in (1).pricing_attribute6 :=
2296 l_pricing_attributes_in.pricing_attribute6;
2297 l_pavv_tbl_in (1).pricing_attribute7 :=
2298 l_pricing_attributes_in.pricing_attribute7;
2299 l_pavv_tbl_in (1).pricing_attribute8 :=
2300 l_pricing_attributes_in.pricing_attribute8;
2301 l_pavv_tbl_in (1).pricing_attribute9 :=
2302 l_pricing_attributes_in.pricing_attribute9;
2303 l_pavv_tbl_in (1).pricing_attribute10 :=
2304 l_pricing_attributes_in.pricing_attribute10;
2305 l_pavv_tbl_in (1).pricing_attribute11 :=
2306 l_pricing_attributes_in.pricing_attribute11;
2307 l_pavv_tbl_in (1).pricing_attribute12 :=
2308 l_pricing_attributes_in.pricing_attribute12;
2309 l_pavv_tbl_in (1).pricing_attribute13 :=
2310 l_pricing_attributes_in.pricing_attribute13;
2311 l_pavv_tbl_in (1).pricing_attribute14 :=
2312 l_pricing_attributes_in.pricing_attribute14;
2313 l_pavv_tbl_in (1).pricing_attribute15 :=
2314 l_pricing_attributes_in.pricing_attribute15;
2315 l_pavv_tbl_in (1).pricing_attribute16 :=
2316 l_pricing_attributes_in.pricing_attribute16;
2317 l_pavv_tbl_in (1).pricing_attribute17 :=
2318 l_pricing_attributes_in.pricing_attribute17;
2319 l_pavv_tbl_in (1).pricing_attribute18 :=
2320 l_pricing_attributes_in.pricing_attribute18;
2321 l_pavv_tbl_in (1).pricing_attribute19 :=
2322 l_pricing_attributes_in.pricing_attribute19;
2323 l_pavv_tbl_in (1).pricing_attribute20 :=
2324 l_pricing_attributes_in.pricing_attribute20;
2325 l_pavv_tbl_in (1).pricing_attribute21 :=
2326 l_pricing_attributes_in.pricing_attribute21;
2327 l_pavv_tbl_in (1).pricing_attribute22 :=
2328 l_pricing_attributes_in.pricing_attribute22;
2329 l_pavv_tbl_in (1).pricing_attribute23 :=
2330 l_pricing_attributes_in.pricing_attribute23;
2331 l_pavv_tbl_in (1).pricing_attribute24 :=
2332 l_pricing_attributes_in.pricing_attribute24;
2333 l_pavv_tbl_in (1).pricing_attribute25 :=
2334 l_pricing_attributes_in.pricing_attribute25;
2335 l_pavv_tbl_in (1).pricing_attribute26 :=
2336 l_pricing_attributes_in.pricing_attribute26;
2337 l_pavv_tbl_in (1).pricing_attribute27 :=
2338 l_pricing_attributes_in.pricing_attribute27;
2339 l_pavv_tbl_in (1).pricing_attribute28 :=
2340 l_pricing_attributes_in.pricing_attribute28;
2341 l_pavv_tbl_in (1).pricing_attribute29 :=
2342 l_pricing_attributes_in.pricing_attribute29;
2343 l_pavv_tbl_in (1).pricing_attribute30 :=
2344 l_pricing_attributes_in.pricing_attribute30;
2345 l_pavv_tbl_in (1).pricing_attribute31 :=
2346 l_pricing_attributes_in.pricing_attribute31;
2347 l_pavv_tbl_in (1).pricing_attribute32 :=
2348 l_pricing_attributes_in.pricing_attribute32;
2349 l_pavv_tbl_in (1).pricing_attribute33 :=
2350 l_pricing_attributes_in.pricing_attribute33;
2351 l_pavv_tbl_in (1).pricing_attribute34 :=
2352 l_pricing_attributes_in.pricing_attribute34;
2353 l_pavv_tbl_in (1).pricing_attribute35 :=
2354 l_pricing_attributes_in.pricing_attribute35;
2355 l_pavv_tbl_in (1).pricing_attribute36 :=
2356 l_pricing_attributes_in.pricing_attribute36;
2357 l_pavv_tbl_in (1).pricing_attribute37 :=
2358 l_pricing_attributes_in.pricing_attribute37;
2359 l_pavv_tbl_in (1).pricing_attribute38 :=
2360 l_pricing_attributes_in.pricing_attribute38;
2361 l_pavv_tbl_in (1).pricing_attribute39 :=
2362 l_pricing_attributes_in.pricing_attribute39;
2363 l_pavv_tbl_in (1).pricing_attribute40 :=
2364 l_pricing_attributes_in.pricing_attribute40;
2365 l_pavv_tbl_in (1).pricing_attribute41 :=
2366 l_pricing_attributes_in.pricing_attribute41;
2367 l_pavv_tbl_in (1).pricing_attribute42 :=
2368 l_pricing_attributes_in.pricing_attribute42;
2369 l_pavv_tbl_in (1).pricing_attribute43 :=
2370 l_pricing_attributes_in.pricing_attribute43;
2371 l_pavv_tbl_in (1).pricing_attribute44 :=
2372 l_pricing_attributes_in.pricing_attribute44;
2373 l_pavv_tbl_in (1).pricing_attribute45 :=
2374 l_pricing_attributes_in.pricing_attribute45;
2375 l_pavv_tbl_in (1).pricing_attribute46 :=
2376 l_pricing_attributes_in.pricing_attribute46;
2377 l_pavv_tbl_in (1).pricing_attribute47 :=
2378 l_pricing_attributes_in.pricing_attribute47;
2379 l_pavv_tbl_in (1).pricing_attribute48 :=
2380 l_pricing_attributes_in.pricing_attribute48;
2381 l_pavv_tbl_in (1).pricing_attribute49 :=
2382 l_pricing_attributes_in.pricing_attribute49;
2383 l_pavv_tbl_in (1).pricing_attribute50 :=
2384 l_pricing_attributes_in.pricing_attribute50;
2385 l_pavv_tbl_in (1).pricing_attribute51 :=
2386 l_pricing_attributes_in.pricing_attribute51;
2387 l_pavv_tbl_in (1).pricing_attribute52 :=
2388 l_pricing_attributes_in.pricing_attribute52;
2389 l_pavv_tbl_in (1).pricing_attribute53 :=
2390 l_pricing_attributes_in.pricing_attribute53;
2391 l_pavv_tbl_in (1).pricing_attribute54 :=
2392 l_pricing_attributes_in.pricing_attribute54;
2393 l_pavv_tbl_in (1).pricing_attribute55 :=
2394 l_pricing_attributes_in.pricing_attribute55;
2395 l_pavv_tbl_in (1).pricing_attribute56 :=
2396 l_pricing_attributes_in.pricing_attribute56;
2397 l_pavv_tbl_in (1).pricing_attribute57 :=
2398 l_pricing_attributes_in.pricing_attribute57;
2399 l_pavv_tbl_in (1).pricing_attribute58 :=
2400 l_pricing_attributes_in.pricing_attribute58;
2401 l_pavv_tbl_in (1).pricing_attribute59 :=
2402 l_pricing_attributes_in.pricing_attribute59;
2403 l_pavv_tbl_in (1).pricing_attribute60 :=
2404 l_pricing_attributes_in.pricing_attribute60;
2405 l_pavv_tbl_in (1).pricing_attribute61 :=
2406 l_pricing_attributes_in.pricing_attribute61;
2407 l_pavv_tbl_in (1).pricing_attribute62 :=
2408 l_pricing_attributes_in.pricing_attribute62;
2409 l_pavv_tbl_in (1).pricing_attribute63 :=
2410 l_pricing_attributes_in.pricing_attribute63;
2411 l_pavv_tbl_in (1).pricing_attribute64 :=
2412 l_pricing_attributes_in.pricing_attribute64;
2413 l_pavv_tbl_in (1).pricing_attribute65 :=
2414 l_pricing_attributes_in.pricing_attribute65;
2415 l_pavv_tbl_in (1).pricing_attribute66 :=
2416 l_pricing_attributes_in.pricing_attribute66;
2417 l_pavv_tbl_in (1).pricing_attribute67 :=
2418 l_pricing_attributes_in.pricing_attribute67;
2419 l_pavv_tbl_in (1).pricing_attribute68 :=
2420 l_pricing_attributes_in.pricing_attribute68;
2421 l_pavv_tbl_in (1).pricing_attribute69 :=
2422 l_pricing_attributes_in.pricing_attribute69;
2423 l_pavv_tbl_in (1).pricing_attribute70 :=
2424 l_pricing_attributes_in.pricing_attribute70;
2425 l_pavv_tbl_in (1).pricing_attribute71 :=
2426 l_pricing_attributes_in.pricing_attribute71;
2427 l_pavv_tbl_in (1).pricing_attribute72 :=
2428 l_pricing_attributes_in.pricing_attribute72;
2429 l_pavv_tbl_in (1).pricing_attribute73 :=
2430 l_pricing_attributes_in.pricing_attribute73;
2431 l_pavv_tbl_in (1).pricing_attribute74 :=
2432 l_pricing_attributes_in.pricing_attribute74;
2433 l_pavv_tbl_in (1).pricing_attribute75 :=
2434 l_pricing_attributes_in.pricing_attribute75;
2435 l_pavv_tbl_in (1).pricing_attribute76 :=
2436 l_pricing_attributes_in.pricing_attribute76;
2437 l_pavv_tbl_in (1).pricing_attribute77 :=
2438 l_pricing_attributes_in.pricing_attribute77;
2439 l_pavv_tbl_in (1).pricing_attribute78 :=
2440 l_pricing_attributes_in.pricing_attribute78;
2441 l_pavv_tbl_in (1).pricing_attribute79 :=
2442 l_pricing_attributes_in.pricing_attribute79;
2443 l_pavv_tbl_in (1).pricing_attribute80 :=
2444 l_pricing_attributes_in.pricing_attribute80;
2445 l_pavv_tbl_in (1).pricing_attribute81 :=
2446 l_pricing_attributes_in.pricing_attribute81;
2447 l_pavv_tbl_in (1).pricing_attribute82 :=
2448 l_pricing_attributes_in.pricing_attribute82;
2449 l_pavv_tbl_in (1).pricing_attribute83 :=
2450 l_pricing_attributes_in.pricing_attribute83;
2451 l_pavv_tbl_in (1).pricing_attribute84 :=
2452 l_pricing_attributes_in.pricing_attribute84;
2453 l_pavv_tbl_in (1).pricing_attribute85 :=
2454 l_pricing_attributes_in.pricing_attribute85;
2455 l_pavv_tbl_in (1).pricing_attribute86 :=
2456 l_pricing_attributes_in.pricing_attribute86;
2457 l_pavv_tbl_in (1).pricing_attribute87 :=
2458 l_pricing_attributes_in.pricing_attribute87;
2459 l_pavv_tbl_in (1).pricing_attribute88 :=
2460 l_pricing_attributes_in.pricing_attribute88;
2461 l_pavv_tbl_in (1).pricing_attribute89 :=
2462 l_pricing_attributes_in.pricing_attribute89;
2463 l_pavv_tbl_in (1).pricing_attribute90 :=
2464 l_pricing_attributes_in.pricing_attribute90;
2465 l_pavv_tbl_in (1).pricing_attribute91 :=
2466 l_pricing_attributes_in.pricing_attribute91;
2467 l_pavv_tbl_in (1).pricing_attribute92 :=
2468 l_pricing_attributes_in.pricing_attribute92;
2469 l_pavv_tbl_in (1).pricing_attribute93 :=
2470 l_pricing_attributes_in.pricing_attribute93;
2471 l_pavv_tbl_in (1).pricing_attribute94 :=
2472 l_pricing_attributes_in.pricing_attribute94;
2473 l_pavv_tbl_in (1).pricing_attribute95 :=
2474 l_pricing_attributes_in.pricing_attribute95;
2475 l_pavv_tbl_in (1).pricing_attribute96 :=
2476 l_pricing_attributes_in.pricing_attribute96;
2477 l_pavv_tbl_in (1).pricing_attribute97 :=
2478 l_pricing_attributes_in.pricing_attribute97;
2479 l_pavv_tbl_in (1).pricing_attribute98 :=
2480 l_pricing_attributes_in.pricing_attribute98;
2481 l_pavv_tbl_in (1).pricing_attribute99 :=
2482 l_pricing_attributes_in.pricing_attribute99;
2483 l_pavv_tbl_in (1).pricing_attribute100 :=
2484 l_pricing_attributes_in.pricing_attribute100;
2485 okc_price_adjustment_pvt.create_price_att_value
2486 (p_api_version => l_api_version,
2487 p_init_msg_list => l_init_msg_list,
2488 x_return_status => l_return_status,
2489 x_msg_count => x_msg_count,
2490 x_msg_data => x_msg_data,
2491 p_pavv_tbl => l_pavv_tbl_in,
2492 x_pavv_tbl => l_pavv_tbl_out
2493 );
2494
2495 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
2496 THEN
2497 fnd_log.STRING
2498 (fnd_log.level_event,
2499 g_module_current
2500 || '.Create_K_Service_Lines.after_create.price_att',
2501 'okc_price_adjustment_pvt.create_price_att_value(Return status = '
2502 || l_return_status
2503 || ')'
2504 );
2505 END IF;
2506
2507 IF l_return_status = 'S'
2508 THEN
2509 l_priceattrib_id := l_pavv_tbl_out (1).ID;
2510 ELSE
2511 okc_api.set_message (g_app_name,
2512 g_required_value,
2513 g_col_name_token,
2514 'PRICE ATTRIBUTES (SUBSCRIPTION LINE)'
2515 );
2516 RAISE g_exception_halt_validation;
2517 END IF;
2518 END IF;
2519
2520 END IF;
2521 /*Added for CLOUD ER 16039680*/
2522
2523 fnd_file.put_line (fnd_file.LOG,
2524 'l_eff_line_upd_flag : '||l_eff_line_upd_flag
2525 );
2526
2527 IF l_eff_line_upd_flag = 'Y' THEN
2528 NULL;
2529 ELSE
2530 open c_line_ammt_ckeck(c_line_id => l_lineid);
2531 fetch c_line_ammt_ckeck into l_prev_line_amt;
2532 close c_line_ammt_ckeck;
2533
2534 fnd_file.put_line (fnd_file.LOG,
2535 'l_prev_line_amt : '||to_char(l_prev_line_amt)
2536 );
2537 END IF;
2538
2539 --Added for fix of bug# 5165947
2540
2541
2542 --Added for fix of bug# 5274971
2543
2544 /*Added for CLOUD ER*/
2545 OPEN get_billing_dtls_OM(p_reproc_line_rec.order_line_id);
2546 FETCH get_billing_dtls_OM INTO l_bill_option,l_bill_profile_id,l_ordered_qty;
2547 CLOSE get_billing_dtls_OM;
2548
2549 IF l_k_line_rec.coverage_schd_id IS NULL THEN
2550
2551 UPDATE oks_k_lines_b
2552 SET toplvl_price_qty = (SELECT Sum(quantity)
2553 FROM OKS_SUBSCR_ELEMENTS_V
2554 WHERE DNZ_CLE_ID=l_lineid),
2555 toplvl_uom_code = (SELECT uom_code
2556 FROM okc_k_items
2557 WHERE cle_id=l_lineid)
2558 WHERE cle_id=l_lineid;
2559
2560 l_sub_qty :=l_ordered_qty;
2561
2562 oks_subscription_pub.get_subs_qty
2563 (p_cle_id => l_lineid,
2564 x_return_status => x_return_status,
2565 x_quantity => l_pr_qty,
2566 x_uom_code => l_pr_uomcode
2567 );
2568 l_negotiated_amount :=l_k_line_rec.unit_selling_price * l_sub_qty ;
2569 l_list_price := (l_k_line_rec.unit_selling_price * l_sub_qty)/nvl(l_pr_qty,1);
2570
2571 UPDATE okc_k_lines_b
2572 SET price_negotiated =l_negotiated_amount,
2573 price_unit =l_list_price
2574 WHERE id=l_lineid;
2575
2576
2577
2578 END IF;
2579
2580 /*Added for CLOUD ER*/
2581
2582
2583 --Added for fix of bug# 5274971
2584 IF l_k_line_rec.coverage_schd_id IS NOT NULL THEN /*Added for CLOUD ER*/
2585
2586 UPDATE okc_k_lines_b
2587 SET price_negotiated =
2588 (SELECT NVL (SUM (NVL (price_negotiated, 0)), 0)
2589 FROM okc_k_lines_b
2590 WHERE cle_id = l_lineid AND dnz_chr_id = l_chrid)
2591 WHERE ID = l_lineid;
2592 END IF;
2593 UPDATE okc_k_headers_all_b --mmadhavi _all for MOAC
2594 SET estimated_amount =
2595 (SELECT NVL (SUM (NVL (price_negotiated, 0)), 0)
2596 FROM okc_k_lines_b
2597 WHERE dnz_chr_id = l_chrid AND lse_id IN (1, 19, 46)) ---Modified for ER16039680
2598 WHERE ID = l_chrid;
2599
2600
2601 -- Rollup the subline tax amount to topline
2602 OPEN c_extwar_line_amount(l_chrid, l_lineid);
2603 FETCH c_extwar_line_amount INTO l_line_tax_amount;
2604 CLOSE c_extwar_line_amount;
2605
2606 -- Update the topline with the tax amount
2607 UPDATE oks_k_lines_b
2608 SET tax_amount = l_line_tax_amount
2609 WHERE cle_id = l_lineid;
2610
2611 -- Rollup the topline tax amount to header
2612 OPEN c_extwar_hdr_amount(l_chrid);
2613 FETCH c_extwar_hdr_amount INTO l_header_tax_amount;
2614 CLOSE c_extwar_hdr_amount;
2615
2616 -- Update the header with the tax amount
2617 UPDATE oks_k_headers_b
2618 SET tax_amount = l_header_tax_amount
2619 WHERE chr_id = l_chrid;
2620
2621 --Added for fix of bug# 5274971
2622
2623 -- create billing schedule
2624 l_strmlvl_id := check_strmlvl_exists (l_lineid);
2625
2626 IF l_strmlvl_id IS NULL
2627 THEN
2628
2629 /*Modified for CLOUD ER*/
2630 IF l_bill_profile_id IS NOT NULL THEN
2631
2632 l_rec.start_date := l_k_line_rec.srv_sdt;
2633 l_rec.end_date := l_k_line_rec.srv_edt;
2634 l_rec.cle_Id := l_lineid;
2635 l_rec.chr_Id := l_chrid;
2636 l_rec.Billing_Profile_Id := l_bill_profile_id;
2637
2638 OKS_BILLING_PROFILES_PUB.Get_Billing_Schedule
2639 (p_api_version => 1.0,
2640 p_init_msg_list =>'T',
2641 p_billing_profile_rec => l_rec,
2642 x_sll_tbl_out => l_sll_tbl_out,
2643 x_return_status => l_return_status,
2644 x_msg_count => l_msg_count,
2645 x_msg_data => l_msg_data);
2646
2647 IF l_k_line_rec.coverage_schd_id IS NULL THEN
2648 l_billing_type := 'E';
2649 ELSE
2650 l_billing_type :=l_sll_tbl_out(1).billing_type;
2651 END IF;
2652
2653 l_sll_tbl(1).Sequence_no := l_sll_tbl_out(1).seq_no;
2654 l_sll_tbl(1).uom_code := l_sll_tbl_out(1).timeunit;
2655 l_sll_tbl(1).uom_per_period := l_sll_tbl_out(1).duration;
2656 l_sll_tbl(1).level_periods := l_sll_tbl_out(1).target_quantity;
2657 l_sll_tbl(1).invoice_offset_days := l_sll_tbl_out(1).Invoice_Offset;
2658 l_sll_tbl(1).interface_offset_days := l_sll_tbl_out(1).Interface_Offset;
2659 l_sll_tbl(1).Cle_Id := l_sll_tbl_out(1).Cle_Id;
2660 l_sll_tbl (1).start_date := l_k_line_rec.srv_sdt;
2661 l_sll_tbl (1).advance_periods := NULL;
2662 l_sll_tbl (1).level_amount := l_sll_tbl_out(1).amount;
2663
2664 ELSE
2665 l_sll_tbl (1).cle_id := l_lineid;
2666 /*Cloud ER*/
2667 IF l_k_line_rec.coverage_schd_id IS NULL THEN
2668 l_billing_type := 'E';
2669 ELSE
2670 l_billing_type := 'T';
2671 END IF;
2672 /*Cloud ER*/
2673 --l_sll_tbl(1).billing_type := 'T';
2674 l_sll_tbl (1).sequence_no := '1';
2675 l_sll_tbl (1).level_periods := '1';
2676 l_sll_tbl (1).start_date := l_k_line_rec.srv_sdt;
2677 l_sll_tbl (1).advance_periods := NULL;
2678 l_sll_tbl (1).level_amount := NULL;
2679 l_sll_tbl (1).invoice_offset_days := NULL;
2680 l_sll_tbl (1).interface_offset_days := NULL;
2681
2682 --22-NOV-2005 mchoudha added for PPC
2683 IF l_period_start IS NOT NULL
2684 AND l_period_type IS NOT NULL
2685 AND l_period_start = 'CALENDAR'
2686 THEN
2687 l_sll_tbl (1).uom_code := 'DAY';
2688 l_sll_tbl (1).uom_per_period :=
2689 l_k_line_rec.srv_edt - l_k_line_rec.srv_sdt + 1;
2690 ELSE
2691 okc_time_util_pub.get_duration
2692 (p_start_date => l_k_line_rec.srv_sdt,
2693 p_end_date => l_k_line_rec.srv_edt,
2694 x_duration => l_duration,
2695 x_timeunit => l_timeunits,
2696 x_return_status => l_return_status
2697 );
2698
2699 --OKS_RENEW_PVT.DEBUG_LOG( '(OKS_EXTWARPRGM_PVT).Create_Billing_Schd :: Get_Duration Status : '|| l_return_status );
2700 --OKS_RENEW_PVT.DEBUG_LOG( '(OKS_EXTWARPRGM_PVT).Create_Billing_Schd :: Duration : '|| l_duration );
2701 --OKS_RENEW_PVT.DEBUG_LOG( '(OKS_EXTWARPRGM_PVT).Create_Billing_Schd :: Time Unit : '|| l_timeunits );
2702 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
2703 THEN
2704 fnd_log.STRING
2705 (fnd_log.level_event,
2706 g_module_current || '.ORDER_REPROCESS.CREATE_CONTRACT',
2707 ' Okc_time_util_pub.get_duration(Return status = '
2708 || l_return_status
2709 || ' ,Duration = '
2710 || l_duration
2711 || ' ,Time Unit = '
2712 || l_timeunits
2713 || ')'
2714 );
2715 END IF;
2716
2717 IF NOT l_return_status = 'S'
2718 THEN
2719 RAISE g_exception_halt_validation;
2720 END IF;
2721
2722 l_sll_tbl (1).uom_code := l_timeunits;
2723 l_sll_tbl (1).uom_per_period := l_duration;
2724 END IF;
2725 END IF;
2726
2727 oks_bill_sch.create_bill_sch_rules
2728 (p_billing_type => l_billing_type,
2729 p_sll_tbl => l_sll_tbl,
2730 p_invoice_rule_id => l_line_rec.invoicing_rule_id,
2731 x_bil_sch_out_tbl => l_bil_sch_out,
2732 x_return_status => l_return_status
2733 );
2734 fnd_file.put_line
2735 (fnd_file.LOG,
2736 'OKS_BILL_SCH.Create_Bill_Sch_Rules(Return status = '
2737 || l_return_status
2738 || ')'
2739 );
2740
2741 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
2742 THEN
2743 fnd_log.STRING
2744 (fnd_log.level_event,
2745 g_module_current || '.ORDER_REPROCESS.CREATE_CONTRACT',
2746 ' OKS_BILL_SCH.Create_Bill_Sch_Rules(Return status = '
2747 || l_return_status
2748 || ')'
2749 );
2750 END IF;
2751
2752 --OKS_RENEW_PVT.DEBUG_LOG( '(OKS_EXTWARPRGM_PVT).Create_Billing_Schd :: Create_Bill_Sch_Rules Status : '|| l_return_status );
2753 IF l_return_status <> okc_api.g_ret_sts_success
2754 THEN
2755 okc_api.set_message (g_app_name,
2756 g_required_value,
2757 g_col_name_token,
2758 'Sched Billing Rule (LINE)'
2759 );
2760 RAISE g_exception_halt_validation;
2761 END IF;
2762
2763 /* Modified for CLOUD ER*/
2764 IF l_bill_option ='FPOM' AND l_bill_profile_id IS NOT NULL THEN
2765
2766 SELECT date_end INTO l_date_end FROM oks_level_elements WHERE cle_id=l_lineid and date_start =l_k_line_rec.srv_sdt;
2767
2768 oks_bill_util_pub.create_bcl_for_om_oks_billing
2769 (p_line_id => l_lineid,
2770 p_date_to => l_date_end,
2771 x_return_status => l_return_status
2772 );
2773 fnd_file.put_line (fnd_file.LOG,
2774 'Create_Contract :- CREATE_BCL_FOR_OM_OKS_BILLING '
2775 || l_return_status
2776 );
2777 ELSIF l_bill_option ='FBOM' THEN
2778
2779 oks_bill_util_pub.create_bcl_for_om
2780 (p_line_id => l_lineid,
2781 x_return_status => l_return_status
2782 );
2783 fnd_file.put_line (fnd_file.LOG,
2784 'Create_Contract :- CREATE_BCL_FOR_OM '
2785 || l_return_status
2786 );
2787 END IF;/*Added for ER 16039680*/
2788
2789 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
2790 THEN
2791 fnd_log.STRING
2792 (fnd_log.level_event,
2793 g_module_current || '.ORDER_REPROCESS.CREATE_CONTRACT',
2794 ' OKS_BILL_UTIL_PUB.CREATE_BCL_FOR_OM(Return status = '
2795 || l_return_status
2796 || ')'
2797 );
2798 END IF;
2799
2800 --OKS_RENEW_PVT.DEBUG_LOG( '(OKS_EXTWARPRGM_PVT).Create_contract_Ibnew :: CREATE_BCL_FOR_OM '|| l_return_status);
2801 IF NOT l_return_status = 'S'
2802 THEN
2803 RAISE g_exception_halt_validation;
2804 END IF;
2805 ELSE
2806 IF check_lvlelements_exists (l_lineid)
2807 THEN
2808
2809 --Added for fix of bug# 5165947
2810
2811 fnd_file.put_line (fnd_file.LOG,
2812 'l_eff_line_upd_flag : '||l_eff_line_upd_flag
2813 );
2814
2815 IF l_eff_line_upd_flag = 'Y' THEN
2816 NULL;
2817 ELSE
2818 open c_line_ammt_ckeck(c_line_id => l_lineid);
2819 fetch c_line_ammt_ckeck into l_curr_line_amt;
2820 close c_line_ammt_ckeck;
2821
2822 fnd_file.put_line (fnd_file.LOG,
2823 'l_curr_line_amt : '||to_char(l_curr_line_amt)
2824 );
2825
2826 if nvl(l_curr_line_amt,0) <> nvl(l_prev_line_amt,0) THEN
2827 l_eff_line_upd_flag := 'Y';
2828
2829 fnd_file.put_line (fnd_file.LOG,
2830 'l_eff_line_upd_flag (final) : '||l_eff_line_upd_flag
2831 );
2832
2833 end if;
2834
2835 END IF;
2836
2837 l_update_line := NVL(l_eff_line_upd_flag,l_update_line);
2838
2839 --Added for fix of bug# 5165947
2840
2841
2842 IF l_update_line = 'Y'
2843 THEN
2844 oks_bill_sch.update_om_sll_date
2845 (p_top_line_id => l_lineid,
2846 x_return_status => l_return_status,
2847 x_msg_count => x_msg_count,
2848 x_msg_data => x_msg_data
2849 );
2850 fnd_file.put_line (fnd_file.LOG,
2851 'IBNEW :- Update_OM_SLL_Date '
2852 || l_return_status
2853 );
2854
2855 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level
2856 )
2857 THEN
2858 fnd_log.STRING
2859 (fnd_log.level_event,
2860 g_module_current
2861 || '.ORDER_REPROCESS.CREATE_CONTRACT',
2862 ' OKS_BILL_SCH.UPDATE_OM_SLL_DATE(Return status = '
2863 || l_return_status
2864 || ')'
2865 );
2866 END IF;
2867
2868 --OKS_RENEW_PVT.DEBUG_LOG('(OKS_EXTWARPRGM_PVT).Create_contract_Ibnew :: Update_OM_SLL_Date ' || l_return_status);
2869 IF NOT l_return_status = 'S'
2870 THEN
2871 RAISE g_exception_halt_validation;
2872 END IF;
2873 ELSE
2874 oks_bill_sch.create_bill_sch_cp
2875 (p_top_line_id => l_lineid,
2876 p_cp_line_id => l_covlvl_id,
2877 p_cp_new => 'Y',
2878 x_return_status => l_return_status,
2879 x_msg_count => x_msg_count,
2880 x_msg_data => x_msg_data
2881 );
2882 fnd_file.put_line
2883 (fnd_file.LOG,
2884 'Create_Contract :- Create_Bill_Sch_CP '
2885 || l_return_status
2886 );
2887
2888 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level
2889 )
2890 THEN
2891 fnd_log.STRING
2892 (fnd_log.level_event,
2893 g_module_current
2894 || '.ORDER_REPROCESS.CREATE_CONTRACT',
2895 ' OKS_BILL_SCH.CREATE_BILL_SCH_CP(Return status = '
2896 || l_return_status
2897 || ' ,'
2898 || TO_CHAR (SYSDATE, 'dd-mon-yyyy HH24:MI:SS')
2899 || ')'
2900 );
2901 END IF;
2902
2903 /*
2904 OKS_RENEW_PVT.DEBUG_LOG(TO_CHAR( SYSDATE, 'dd-mon-yyyy HH24:MI:SS')
2905 || '(OKS_EXTWARPRGM_PVT).Create_contract_Ibnew :: Create_Bill_Sch_CP '
2906 || l_return_status
2907 ); */
2908 IF NOT l_return_status = 'S'
2909 THEN
2910 RAISE g_exception_halt_validation;
2911 END IF;
2912 END IF;
2913
2914 oks_bill_util_pub.create_bcl_for_om
2915 (p_line_id => l_lineid,
2916 x_return_status => l_return_status
2917 );
2918 fnd_file.put_line (fnd_file.LOG,
2919 'Create_Contract :- CREATE_BCL_FOR_OM '
2920 || l_return_status
2921 );
2922
2923 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
2924 THEN
2925 fnd_log.STRING
2926 (fnd_log.level_event,
2927 g_module_current || '.ORDER_REPROCESS.CREATE_CONTRACT',
2928 ' OKS_BILL_UTIL_PUB.CREATE_BCL_FOR_OM(Return status = '
2929 || l_return_status
2930 || ')'
2931 );
2932 END IF;
2933
2934 --OKS_RENEW_PVT.DEBUG_LOG( '(OKS_EXTWARPRGM_PVT).Create_contract_Ibnew :: CREATE_BCL_FOR_OM '|| l_return_status);
2935 IF NOT l_return_status = 'S'
2936 THEN
2937 RAISE g_exception_halt_validation;
2938 END IF;
2939 ELSE
2940 okc_api.set_message (g_app_name,
2941 g_required_value,
2942 g_col_name_token,
2943 'level elements NOT EXIST'
2944 );
2945 RAISE g_exception_halt_validation;
2946 END IF;
2947 END IF;
2948
2949 -- Commented out for fix of bug# 5274971
2950 --
2951 -- UPDATE okc_k_lines_b
2952 -- SET price_negotiated =
2953 -- (SELECT NVL (SUM (NVL (price_negotiated, 0)), 0)
2954 -- FROM okc_k_lines_b
2955 -- WHERE cle_id = l_lineid AND dnz_chr_id = l_chrid)
2956 -- WHERE ID = l_lineid;
2957 --
2958 -- UPDATE okc_k_headers_all_b --mmadhavi _all for MOAC
2959 -- SET estimated_amount =
2960 -- (SELECT NVL (SUM (NVL (price_negotiated, 0)), 0)
2961 -- FROM okc_k_lines_b
2962 -- WHERE dnz_chr_id = l_chrid AND lse_id IN (1, 19))
2963 -- WHERE ID = l_chrid;
2964 --
2965 --
2966 -- -- Rollup the subline tax amount to topline
2967 -- OPEN c_extwar_line_amount(l_chrid, l_lineid);
2968 -- FETCH c_extwar_line_amount INTO l_line_tax_amount;
2969 -- CLOSE c_extwar_line_amount;
2970 --
2971 -- -- Update the topline with the tax amount
2972 -- UPDATE oks_k_lines_b
2973 -- SET tax_amount = l_line_tax_amount
2974 -- WHERE cle_id = l_lineid;
2975 --
2976 -- -- Rollup the topline tax amount to header
2977 -- OPEN c_extwar_hdr_amount(l_chrid);
2978 -- FETCH c_extwar_hdr_amount INTO l_header_tax_amount;
2979 -- CLOSE c_extwar_hdr_amount;
2980 --
2981 -- -- Update the header with the tax amount
2982 -- UPDATE oks_k_headers_b
2983 -- SET tax_amount = l_header_tax_amount
2984 -- WHERE chr_id = l_chrid;
2985 --
2986 -- Commented out for fix of bug# 5274971
2987
2988 EXCEPTION
2989 WHEN g_exception_halt_validation
2990 THEN
2991 x_return_status := l_return_status;
2992 IF c_extwar_line_amount%ISOPEN THEN
2993 CLOSE c_extwar_line_amount;
2994 END IF;
2995 IF c_extwar_hdr_amount%ISOPEN THEN
2996 CLOSE c_extwar_hdr_amount;
2997 END IF;
2998 WHEN OTHERS
2999 THEN
3000 x_return_status := okc_api.g_ret_sts_unexp_error;
3001 IF c_extwar_line_amount%ISOPEN THEN
3002 CLOSE c_extwar_line_amount;
3003 END IF;
3004 IF c_extwar_hdr_amount%ISOPEN THEN
3005 CLOSE c_extwar_hdr_amount;
3006 END IF;
3007 fnd_file.put_line (fnd_file.LOG,
3008 'Oracle Error Code is -' || TO_CHAR (SQLCODE)
3009 );
3010 fnd_file.put_line (fnd_file.LOG,
3011 'Oracle Error Message is -'
3012 || SUBSTR (SQLERRM, 1, 512)
3013 );
3014 END create_contract;
3015
3016 PROCEDURE get_order_details (
3017 p_option IN VARCHAR2,
3018 p_source IN VARCHAR2,
3019 x_return_status OUT NOCOPY VARCHAR2,
3020 x_repv_tbl OUT NOCOPY oks_rep_pvt.repv_tbl_type
3021 )
3022 IS
3023 --mmadhavi modified cursors for MOAC
3024 CURSOR l_order_line_sub_csr
3025 IS
3026 SELECT ID, order_id, order_line_id, success_flag, source_flag,
3027 rep.order_number
3028 FROM oks_reprocessing_v rep, oe_order_headers oh
3029 WHERE success_flag = 'R'
3030 AND rep.order_id = oh.header_id
3031 AND conc_request_id IS NULL;
3032
3033 CURSOR l_order_line_sel_csr
3034 IS
3035 SELECT ID, order_id, order_line_id, success_flag, source_flag,
3036 rep.order_number
3037 FROM oks_reprocessing_v rep, oe_order_headers oh
3038 WHERE reprocess_yn = 'Y' --success_flag IN ('R','N')
3039 AND rep.order_id = oh.header_id
3040 AND conc_request_id IS NULL;
3041
3042 CURSOR l_order_line_all_csr
3043 IS
3044 SELECT ID, order_id, order_line_id, success_flag, source_flag,
3045 rep.order_number
3046 FROM oks_reprocessing_v rep, oe_order_headers oh
3047 WHERE NVL (success_flag, 'E') IN ('E', 'N') ---IN ('R','N','E')
3048 AND rep.order_id = oh.header_id
3049 AND conc_request_id IS NULL;
3050
3051 l_repv_tbl oks_rep_pvt.repv_tbl_type;
3052 l_return_status VARCHAR2 (1) := okc_api.g_ret_sts_success;
3053 l_ordline_rec oks_rep_pvt.repv_rec_type;
3054 l_ptr NUMBER;
3055 l_source VARCHAR2 (30);
3056
3057 PROCEDURE get_order_lines (
3058 p_id IN NUMBER,
3059 p_order_id IN NUMBER,
3060 p_ord_num IN NUMBER,
3061 p_success_flag IN VARCHAR2,
3062 p_source_flag IN VARCHAR2,
3063 x_repv_tbl OUT NOCOPY oks_rep_pvt.repv_tbl_type,
3064 x_return_status OUT NOCOPY VARCHAR2
3065 )
3066 IS
3067 CURSOR get_order_lines_csr (p_ord_num NUMBER)
3068 IS
3069 SELECT ol.line_id, NVL (fulfilled_quantity, 0),
3070 service_reference_type_code, service_reference_line_id
3071 FROM oe_order_lines_all ol, oe_order_headers oh
3072 WHERE oh.header_id = ol.header_id
3073 AND oh.order_number = p_ord_num;
3074
3075 --and Oh.org_id = okc_context.get_okc_org_id;
3076
3077 /*
3078 Cursor get_ordlines_dtls_csr(p_ord_line_id NUMBER)
3079 Is
3080 Select Nvl(FULFILLED_QUANTITY,0),SERVICE_REFERENCE_TYPE_CODE,SERVICE_REFERENCE_LINE_ID
3081 From oe_order_lines_all
3082 Where line_id = p_ord_line_id
3083 and header_id IN (select header_id from oe_order_headers_all where org_id = okc_context.get_okc_org_id);
3084 */
3085
3086 --mmadhavi modified the cursor for MOAC
3087 CURSOR check_duplicate_csr (p_ordline_id NUMBER)
3088 IS
3089 SELECT 'x'
3090 FROM oks_reprocessing rep, oe_order_headers hdr
3091 WHERE rep.order_line_id = p_ordline_id
3092 AND rep.order_id = hdr.header_id;
3093
3094 l_ord_line_id NUMBER;
3095 serv_ref_type VARCHAR2 (30);
3096 serv_ref_id NUMBER;
3097 l_fulfilled_qty NUMBER;
3098 l_exists VARCHAR2 (1) := 'Y';
3099 l_ptr1 NUMBER;
3100 l_first_order_line NUMBER;
3101 l_init_msg_list VARCHAR2 (1) := okc_api.g_false;
3102 l_return_status VARCHAR2 (1) := okc_api.g_ret_sts_success;
3103 l_msg_count NUMBER := 0;
3104 l_msg_data VARCHAR2 (2000);
3105 l_repv_rec oks_rep_pvt.repv_rec_type;
3106 l_out_repv_rec oks_rep_pvt.repv_rec_type;
3107 BEGIN
3108 fnd_file.put_line (fnd_file.LOG,
3109 'Inside procedure Get Order Lines'
3110 );
3111 l_first_order_line := NULL;
3112 l_ptr1 := 0;
3113
3114 -- mmadhavi commenting for MOAC
3115 --FND_FILE.PUT_LINE (FND_FILE.LOG, ' Okc_context = ' || nvl(okc_context.get_okc_org_id,-999));
3116 OPEN get_order_lines_csr (p_ord_num);
3117
3118 LOOP
3119 FETCH get_order_lines_csr
3120 INTO l_ord_line_id, l_fulfilled_qty, serv_ref_type,
3121 serv_ref_id;
3122
3123 EXIT WHEN get_order_lines_csr%NOTFOUND;
3124 l_exists := 'y';
3125
3126 OPEN check_duplicate_csr (l_ord_line_id);
3127
3128 FETCH check_duplicate_csr
3129 INTO l_exists;
3130
3131 CLOSE check_duplicate_csr;
3132
3133 IF (l_exists <> 'x')
3134 THEN
3135 fnd_file.put_line (fnd_file.LOG, 'No duplicate line..');
3136
3137 /*
3138 Open get_ordlines_dtls_csr(l_ord_line_id);
3139 Fetch get_ordlines_dtls_csr into l_fulfilled_qty,serv_ref_type, serv_ref_id;
3140 Close get_ordlines_dtls_csr;
3141 */
3142 IF ( NVL (serv_ref_type, 'REF_TYPE') IN
3143 ('CUSTOMER_PRODUCT', 'ORDER')
3144 AND serv_ref_id IS NOT NULL
3145 )
3146 THEN
3147 fnd_file.put_line (fnd_file.LOG, 'Valid line...');
3148
3149 IF (p_source_flag = 'ASO')
3150 THEN
3151 IF (l_fulfilled_qty > 0)
3152 THEN
3153 l_ptr1 := l_ptr1 + 1;
3154 fnd_file.put_line (fnd_file.LOG,
3155 'From ASO ..Fulfilled..'
3156 );
3157
3158 IF (l_ptr1 = 1)
3159 THEN
3160 l_first_order_line := l_ord_line_id;
3161 fnd_file.put_line (fnd_file.LOG,
3162 'First Order line..'
3163 );
3164 ELSE
3165 fnd_file.put_line (fnd_file.LOG,
3166 'More lines ..Inserting..'
3167 );
3168 l_repv_rec.order_id := p_order_id;
3169 l_repv_rec.order_line_id := l_ord_line_id;
3170 l_repv_rec.success_flag := p_success_flag;
3171 l_repv_rec.source_flag := p_source_flag;
3172 l_repv_rec.order_number := p_ord_num;
3173 l_repv_rec.reprocess_yn := 'N';
3174 oks_rep_pub.insert_row
3175 (p_api_version => 1.0,
3176 p_init_msg_list => l_init_msg_list,
3177 x_return_status => l_return_status,
3178 x_msg_count => l_msg_count,
3179 x_msg_data => l_msg_data,
3180 p_repv_rec => l_repv_rec,
3181 x_repv_rec => l_out_repv_rec
3182 );
3183 fnd_file.put_line (fnd_file.LOG,
3184 'Return status = '
3185 || l_return_status
3186 );
3187
3188 IF (l_return_status <> 'S')
3189 THEN
3190 x_return_status := l_return_status;
3191 RAISE g_exception_halt_validation;
3192 END IF;
3193
3194 fnd_file.put_line (fnd_file.LOG,
3195 'Insert status = '
3196 || l_return_status
3197 );
3198 x_repv_tbl (l_ptr1).ID := l_out_repv_rec.ID;
3199 x_repv_tbl (l_ptr1).order_id :=
3200 l_out_repv_rec.order_id;
3201 x_repv_tbl (l_ptr1).order_line_id :=
3202 l_out_repv_rec.order_line_id;
3203 x_repv_tbl (l_ptr1).success_flag :=
3204 l_out_repv_rec.success_flag;
3205 x_repv_tbl (l_ptr1).source_flag :=
3206 l_out_repv_rec.source_flag;
3207 x_repv_tbl (l_ptr1).order_number :=
3208 l_out_repv_rec.order_number;
3209 END IF;
3210 END IF;
3211 ELSE
3212 fnd_file.put_line (fnd_file.LOG, 'Source = MANUAL');
3213 l_ptr1 := l_ptr1 + 1;
3214
3215 IF (l_ptr1 = 1)
3216 THEN
3217 l_first_order_line := l_ord_line_id;
3218 fnd_file.put_line (fnd_file.LOG,
3219 'First Order Line...'
3220 );
3221 ELSE
3222 fnd_file.put_line (fnd_file.LOG,
3223 'Next Order lines...'
3224 );
3225 l_repv_rec.order_id := p_order_id;
3226 l_repv_rec.order_line_id := l_ord_line_id;
3227 l_repv_rec.success_flag := p_success_flag;
3228 l_repv_rec.source_flag := p_source_flag;
3229 l_repv_rec.order_number := p_ord_num;
3230 l_repv_rec.reprocess_yn := 'N';
3231 oks_rep_pub.insert_row
3232 (p_api_version => 1.0,
3233 p_init_msg_list => l_init_msg_list,
3234 x_return_status => l_return_status,
3235 x_msg_count => l_msg_count,
3236 x_msg_data => l_msg_data,
3237 p_repv_rec => l_repv_rec,
3238 x_repv_rec => l_out_repv_rec
3239 );
3240 fnd_file.put_line
3241 (fnd_file.LOG,
3242 'oks_rep_pub.insert_row : return status => '
3243 || l_return_status
3244 );
3245
3246 IF (l_return_status <> 'S')
3247 THEN
3248 x_return_status := l_return_status;
3249 RAISE g_exception_halt_validation;
3250 END IF;
3251
3252 fnd_file.put_line (fnd_file.LOG,
3253 'Insert status ...'
3254 || l_return_status
3255 );
3256 x_repv_tbl (l_ptr1).ID := l_out_repv_rec.ID;
3257 x_repv_tbl (l_ptr1).order_id :=
3258 l_out_repv_rec.order_id;
3259 x_repv_tbl (l_ptr1).order_line_id :=
3260 l_out_repv_rec.order_line_id;
3261 x_repv_tbl (l_ptr1).success_flag :=
3262 l_out_repv_rec.success_flag;
3263 x_repv_tbl (l_ptr1).source_flag :=
3264 l_out_repv_rec.source_flag;
3265 x_repv_tbl (l_ptr1).order_number :=
3266 l_out_repv_rec.order_number;
3267 END IF;
3268 END IF; -- Source = ASO
3269 END IF; -- Service line
3270 END IF; -- duplicate line
3271 END LOOP;
3272
3273 fnd_file.put_line (fnd_file.LOG, 'End of Loop...');
3274
3275 IF (l_first_order_line IS NOT NULL)
3276 THEN
3277 x_repv_tbl (1).ID := p_id;
3278 x_repv_tbl (1).order_id := p_order_id;
3279 x_repv_tbl (1).order_line_id := l_first_order_line;
3280 x_repv_tbl (1).success_flag := p_success_flag;
3281 x_repv_tbl (1).source_flag := p_source_flag;
3282 x_repv_tbl (1).order_number := p_ord_num;
3283 END IF;
3284
3285 fnd_file.put_line (fnd_file.LOG, 'Exiting Get Order lines');
3286 EXCEPTION
3287 WHEN g_exception_halt_validation
3288 THEN
3289 fnd_file.put_line (fnd_file.LOG,
3290 ' Error in Get_Order_lines : '
3291 || SQLCODE
3292 || ':'
3293 || SQLERRM
3294 );
3295 okc_api.set_message (g_app_name,
3296 g_unexpected_error,
3297 g_sqlcode_token,
3298 SQLCODE,
3299 g_sqlerrm_token,
3300 SQLERRM
3301 );
3302 WHEN OTHERS
3303 THEN
3304 x_return_status := okc_api.g_ret_sts_unexp_error;
3305 fnd_file.put_line (fnd_file.LOG,
3306 ' Error in Get_Order_lines : '
3307 || SQLCODE
3308 || ':'
3309 || SQLERRM
3310 );
3311 okc_api.set_message (g_app_name,
3312 g_unexpected_error,
3313 g_sqlcode_token,
3314 SQLCODE,
3315 g_sqlerrm_token,
3316 SQLERRM
3317 );
3318 END get_order_lines;
3319 BEGIN
3320 fnd_file.put_line (fnd_file.LOG,
3321 'Inside procedure Get Order Details'
3322 );
3323 l_source := NVL (p_source, 'Auto');
3324
3325 IF (l_source <> 'FORM')
3326 THEN
3327 l_ptr := 0;
3328
3329 IF p_option = 'SEL'
3330 THEN
3331 FOR l_ordline_rec IN l_order_line_sel_csr
3332 LOOP
3333 l_ptr := l_ptr + 1;
3334
3335 IF (l_ordline_rec.order_line_id IS NULL)
3336 THEN
3337 get_order_lines
3338 (p_id => l_ordline_rec.ID,
3339 p_order_id => l_ordline_rec.order_id,
3340 p_ord_num => l_ordline_rec.order_number,
3341 p_success_flag => l_ordline_rec.success_flag,
3342 p_source_flag => l_ordline_rec.source_flag,
3343 x_repv_tbl => l_repv_tbl,
3344 x_return_status => l_return_status
3345 );
3346
3347 IF (l_return_status <> 'S')
3348 THEN
3349 x_return_status := l_return_status;
3350 RAISE g_exception_halt_validation;
3351 END IF;
3352
3353 FOR i IN 1 .. l_repv_tbl.COUNT
3354 LOOP
3355 x_repv_tbl (l_ptr).ID := l_repv_tbl (i).ID;
3356 x_repv_tbl (l_ptr).order_id :=
3357 l_repv_tbl (i).order_id;
3358 x_repv_tbl (l_ptr).order_line_id :=
3359 l_repv_tbl (i).order_line_id;
3360 x_repv_tbl (l_ptr).success_flag :=
3361 l_repv_tbl (i).success_flag;
3362 x_repv_tbl (l_ptr).source_flag :=
3363 l_repv_tbl (i).source_flag;
3364 x_repv_tbl (l_ptr).order_number :=
3365 l_repv_tbl (i).order_number;
3366 l_ptr := l_ptr + 1;
3367 END LOOP;
3368
3369 l_ptr := l_ptr - 1;
3370 ELSE
3371 x_repv_tbl (l_ptr).ID := l_ordline_rec.ID;
3372 x_repv_tbl (l_ptr).order_id := l_ordline_rec.order_id;
3373 x_repv_tbl (l_ptr).order_line_id :=
3374 l_ordline_rec.order_line_id;
3375 x_repv_tbl (l_ptr).success_flag :=
3376 l_ordline_rec.success_flag;
3377 x_repv_tbl (l_ptr).source_flag :=
3378 l_ordline_rec.source_flag;
3379 x_repv_tbl (l_ptr).order_number :=
3380 l_ordline_rec.order_number;
3381 END IF;
3382 END LOOP;
3383 ELSE
3384 FOR l_ordline_rec IN l_order_line_all_csr
3385 LOOP
3386 l_ptr := l_ptr + 1;
3387
3388 IF (l_ordline_rec.order_line_id IS NULL)
3389 THEN
3390 get_order_lines
3391 (p_id => l_ordline_rec.ID,
3392 p_order_id => l_ordline_rec.order_id,
3393 p_ord_num => l_ordline_rec.order_number,
3394 p_success_flag => l_ordline_rec.success_flag,
3395 p_source_flag => l_ordline_rec.source_flag,
3396 x_repv_tbl => l_repv_tbl,
3397 x_return_status => l_return_status
3398 );
3399
3400 IF (l_return_status <> 'S')
3401 THEN
3402 x_return_status := l_return_status;
3403 RAISE g_exception_halt_validation;
3404 END IF;
3405
3406 FOR i IN 1 .. l_repv_tbl.COUNT
3407 LOOP
3408 x_repv_tbl (l_ptr).ID := l_repv_tbl (i).ID;
3409 x_repv_tbl (l_ptr).order_id :=
3410 l_repv_tbl (i).order_id;
3411 x_repv_tbl (l_ptr).order_line_id :=
3412 l_repv_tbl (i).order_line_id;
3413 x_repv_tbl (l_ptr).success_flag :=
3414 l_repv_tbl (i).success_flag;
3415 x_repv_tbl (l_ptr).source_flag :=
3416 l_repv_tbl (i).source_flag;
3417 x_repv_tbl (l_ptr).order_number :=
3418 l_repv_tbl (i).order_number;
3419 l_ptr := l_ptr + 1;
3420 END LOOP;
3421
3422 l_ptr := l_ptr - 1;
3423 ELSE
3424 x_repv_tbl (l_ptr).ID := l_ordline_rec.ID;
3425 x_repv_tbl (l_ptr).order_id := l_ordline_rec.order_id;
3426 x_repv_tbl (l_ptr).order_line_id :=
3427 l_ordline_rec.order_line_id;
3428 x_repv_tbl (l_ptr).success_flag :=
3429 l_ordline_rec.success_flag;
3430 x_repv_tbl (l_ptr).source_flag :=
3431 l_ordline_rec.source_flag;
3432 x_repv_tbl (l_ptr).order_number :=
3433 l_ordline_rec.order_number;
3434 END IF;
3435 END LOOP;
3436 END IF;
3437 ELSE
3438 fnd_file.put_line (fnd_file.LOG, 'Choice - Submitted');
3439 l_ptr := 0;
3440
3441 FOR l_ordline_rec IN l_order_line_sub_csr
3442 LOOP
3443 l_ptr := l_ptr + 1;
3444
3445 IF (l_ordline_rec.order_line_id IS NULL)
3446 THEN
3447 fnd_file.put_line (fnd_file.LOG,
3448 'Calling get_order_lines...'
3449 );
3450 get_order_lines
3451 (p_id => l_ordline_rec.ID,
3452 p_order_id => l_ordline_rec.order_id,
3453 p_ord_num => l_ordline_rec.order_number,
3454 p_success_flag => l_ordline_rec.success_flag,
3455 p_source_flag => l_ordline_rec.source_flag,
3456 x_repv_tbl => l_repv_tbl,
3457 x_return_status => l_return_status
3458 );
3459
3460 IF (l_return_status <> 'S')
3461 THEN
3462 x_return_status := l_return_status;
3463 RAISE g_exception_halt_validation;
3464 END IF;
3465
3466 FOR i IN 1 .. l_repv_tbl.COUNT
3467 LOOP
3468 x_repv_tbl (l_ptr).ID := l_repv_tbl (i).ID;
3469 x_repv_tbl (l_ptr).order_id := l_repv_tbl (i).order_id;
3470 x_repv_tbl (l_ptr).order_line_id :=
3471 l_repv_tbl (i).order_line_id;
3472 x_repv_tbl (l_ptr).success_flag :=
3473 l_repv_tbl (i).success_flag;
3474 x_repv_tbl (l_ptr).source_flag :=
3475 l_repv_tbl (i).source_flag;
3476 x_repv_tbl (l_ptr).order_number :=
3477 l_repv_tbl (i).order_number;
3478 l_ptr := l_ptr + 1;
3479 END LOOP;
3480
3481 l_ptr := l_ptr - 1;
3482 ELSE
3483 x_repv_tbl (l_ptr).ID := l_ordline_rec.ID;
3484 x_repv_tbl (l_ptr).order_id := l_ordline_rec.order_id;
3485 x_repv_tbl (l_ptr).order_line_id :=
3486 l_ordline_rec.order_line_id;
3487 x_repv_tbl (l_ptr).success_flag :=
3488 l_ordline_rec.success_flag;
3489 x_repv_tbl (l_ptr).source_flag := l_ordline_rec.source_flag;
3490 x_repv_tbl (l_ptr).order_number :=
3491 l_ordline_rec.order_number;
3492 END IF;
3493 END LOOP;
3494 END IF;
3495
3496 x_return_status := l_return_status;
3497 fnd_file.put_line (fnd_file.LOG, 'Exiting Get Order Details');
3498 EXCEPTION
3499 WHEN g_exception_halt_validation
3500 THEN
3501 fnd_file.put_line (fnd_file.LOG,
3502 ' Error in Get_Order_details : '
3503 || SQLCODE
3504 || ':'
3505 || SQLERRM
3506 );
3507 okc_api.set_message (g_app_name,
3508 g_unexpected_error,
3509 g_sqlcode_token,
3510 SQLCODE,
3511 g_sqlerrm_token,
3512 SQLERRM
3513 );
3514 WHEN OTHERS
3515 THEN
3516 x_return_status := okc_api.g_ret_sts_unexp_error;
3517 fnd_file.put_line (fnd_file.LOG,
3518 ' Error in Get_Order_Details : '
3519 || SQLCODE
3520 || ':'
3521 || SQLERRM
3522 );
3523 okc_api.set_message (g_app_name,
3524 g_unexpected_error,
3525 g_sqlcode_token,
3526 SQLCODE,
3527 g_sqlerrm_token,
3528 SQLERRM
3529 );
3530 END get_order_details;
3531 BEGIN
3532 --Okc_context.set_okc_org_context (l_order_rec.org_id, NULL ); --mmadhavi commenting for MOAC
3533 l_prog_id := fnd_global.conc_program_id;
3534 l_req_id := fnd_global.conc_request_id;
3535 fnd_file.put_line (fnd_file.LOG, 'conc_prog_id = ' || l_prog_id);
3536 fnd_file.put_line (fnd_file.LOG, 'conc_req_id = ' || l_req_id);
3537 fnd_file.put_line (fnd_file.LOG, 'Source = ' || NVL (p_source, 'Auto'));
3538 SAVEPOINT oks_reprocessing;
3539 fnd_file.put_line (fnd_file.LOG, 'Start of Reprocessing.....');
3540 fnd_file.put_line (fnd_file.LOG,
3541 'The parameter value is ..' || p_option
3542 );
3543 fnd_file.put_line (fnd_file.LOG, 'Calling Get Order Details....');
3544 get_order_details (p_option => p_option,
3545 p_source => p_source,
3546 x_return_status => l_return_status,
3547 x_repv_tbl => l_repv_tbl
3548 );
3549 fnd_file.put_line (fnd_file.LOG,
3550 'Get Order Details : l_return_status = '
3551 || l_return_status
3552 );
3553 l_line_count := l_repv_tbl.COUNT;
3554
3555 IF (l_line_count <= 0)
3556 THEN
3557 fnd_file.put_line (fnd_file.LOG, 'No lines to Reprocess..');
3558 ELSE
3559 fnd_file.put_line (fnd_file.LOG,
3560 'ReProcessing Order Lines... Line count = '
3561 || l_line_count
3562 );
3563
3564 FOR l_count IN 1 .. l_line_count
3565 LOOP
3566
3567 --OM INT User Hook Start Bug# 4462061
3568
3569 --Call out to Pre-Integration
3570 --This is done as part of License Migration
3571 --Call out starts here
3572 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
3573 THEN
3574 fnd_log.string(FND_LOG.LEVEL_STATEMENT
3575 ,G_MODULE_CURRENT||'.ORDER_REPROCES'
3576 ,'Before OKS_OMIB_EXTNS_PUB.pre_integration call: ' ||
3577 ' ,p_api_version = '|| '1.0' ||
3578 ' ,p_init_msg_list = ' || 'T' ||
3579 ' ,p_from_integration = OREP' ||
3580 ' ,p_transaction_type = ' || NULL ||
3581 ' ,p_transaction_date = ' || NULL ||
3582 ' ,p_order_line_id = ' || l_repv_tbl(l_count).order_line_id ||
3583 ' ,p_old_instance_id = ' || NULL ||
3584 ' ,p_new_instance_id = ' || NULL);
3585 END IF;
3586
3587 OKS_OMIB_INT_EXTNS_PUB.pre_integration
3588 (p_api_version => 1.0
3589 ,p_init_msg_list => 'T'
3590 ,p_from_integration => 'OREP'
3591 ,p_transaction_type => NULL
3592 ,p_transaction_date => NULL
3593 ,p_order_line_id => l_repv_tbl(l_count).order_line_id
3594 ,p_old_instance_id => NULL
3595 ,p_new_instance_id => NULL
3596 ,x_process_status => l_process_status
3597 ,x_return_status => x_return_status
3598 ,x_msg_count => x_msg_count
3599 ,x_msg_data => x_msg_data);
3600
3601 IF fnd_log.level_event >= fnd_log.g_current_runtime_level
3602 THEN
3603 fnd_log.string(FND_LOG.LEVEL_EVENT
3604 ,G_MODULE_CURRENT||'.IB_INTERFACE'
3605 ,'After OKS_OMIB_INT_EXTNS_PUB.pre_integration Call: ' ||
3606 ' ,x_process_status = ' || l_process_status ||
3607 ' ,x_return_status = ' || x_return_status);
3608 END IF;
3609 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
3610 THEN
3611 RAISE G_EXCEPTION_HALT_VALIDATION;
3612 END IF;
3613 --Call out ends here
3614
3615 IF l_process_status = 'C'
3616 THEN
3617
3618 --OM INT User Hook End
3619
3620 l_upd_tbl.DELETE;
3621 l_conc_rec.conc_request_id := l_req_id;
3622 l_conc_rec.order_line_id := l_repv_tbl (l_count).order_line_id;
3623 l_conc_rec.ID := l_repv_tbl (l_count).ID;
3624 fnd_file.put_line (fnd_file.LOG,
3625 'Updating record with Conc req id'
3626 );
3627 handle_order_error (x_return_status => l_return_status,
3628 p_upd_rec => l_conc_rec
3629 );
3630 fnd_file.put_line (fnd_file.LOG,
3631 'l_return_status = ' || l_return_status
3632 );
3633
3634 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
3635 THEN
3636 RAISE g_exception_halt_validation;
3637 END IF;
3638
3639 l_fulfill := 'Y';
3640 l_order_rec.fqty := 0;
3641 l_order_rec.rolineid := NULL;
3642 l_order_rec.header_id := NULL;
3643
3644 OPEN l_order_csr (l_repv_tbl (l_count).order_line_id);
3645
3646 FETCH l_order_csr
3647 INTO l_order_rec;
3648
3649 IF l_order_csr%NOTFOUND
3650 THEN
3651 fnd_file.put_line (fnd_file.LOG, 'l_order_csr not found ');
3652 l_order_rec.fqty := 0;
3653 END IF;
3654
3655 CLOSE l_order_csr;
3656
3657 okc_context.set_okc_org_context (l_order_rec.org_id, NULL);
3658
3659 --FND_FILE.PUT_LINE (FND_FILE.LOG, 'success_flag = '||NVL(l_repv_tbl(l_count).success_flag,'S'));
3660 IF (NVL (l_repv_tbl (l_count).source_flag, 'S') = 'MANUAL')
3661 THEN
3662 fnd_file.put_line (fnd_file.LOG,
3663 'Order line entered from Reprocesing UI'
3664 );
3665
3666 IF (l_order_rec.fqty <= 0)
3667 THEN
3668 fnd_file.put_line (fnd_file.LOG,
3669 'Order line '
3670 || l_repv_tbl (l_count).order_line_id
3671 || ' not fulfilled'
3672 );
3673 l_upd_tbl (1).ID := l_repv_tbl (l_count).ID;
3674 l_upd_tbl (1).ERROR_TEXT := '#';
3675 fnd_message.set_name ('OKS', 'OKS_REQUEST');
3676 fnd_message.set_token (token => 'ID', VALUE => l_req_id);
3677 l_upd_tbl (1).ERROR_TEXT :=
3678 l_upd_tbl (1).ERROR_TEXT || fnd_message.get_encoded
3679 || '#';
3680 fnd_message.set_name ('OKS', 'OKS_LINE_NOT_FULFILLED');
3681 fnd_message.set_token
3682 (token => 'ORD_LINE',
3683 VALUE => l_repv_tbl (l_count).order_line_id
3684 );
3685 l_upd_tbl (1).ERROR_TEXT :=
3686 l_upd_tbl (1).ERROR_TEXT || fnd_message.get_encoded
3687 || '#';
3688 l_upd_tbl (1).success_flag := 'E';
3689 l_upd_tbl (1).conc_request_id := NULL;
3690 handle_order_error (x_return_status => l_return_status,
3691 p_upd_rec => l_upd_tbl (1)
3692 );
3693
3694 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
3695 THEN
3696 RAISE g_exception_halt_validation;
3697 END IF;
3698
3699 l_fulfill := 'N';
3700 END IF;
3701 END IF;
3702
3703 IF (l_fulfill <> 'N')
3704 THEN
3705 fnd_file.put_line (fnd_file.LOG, 'Order line fulfilled..');
3706
3707 OPEN l_serv_ref_csr (l_repv_tbl (l_count).order_line_id);
3708
3709 FETCH l_serv_ref_csr
3710 INTO l_serv_ref_rec;
3711
3712 CLOSE l_serv_ref_csr;
3713
3714 fnd_file.put_line (fnd_file.LOG,
3715 'Processing Order Line '
3716 || l_repv_tbl (l_count).order_line_id
3717 );
3718 fnd_file.put_line (fnd_file.LOG,
3719 'Service Ref Type Code '
3720 || l_serv_ref_rec.service_reference_type_code
3721 );
3722 fnd_file.put_line (fnd_file.LOG,
3723 'Service line Id '
3724 || l_serv_ref_rec.service_reference_line_id
3725 );
3726 --FND_FILE.PUT_LINE (FND_FILE.LOG, 'Profile option value ' || Fnd_Profile.Value('OKS_CONTRACTS_VALIDATION_SOURCE') );
3727
3728 /*
3729 If Fnd_Profile.Value('OKS_CONTRACTS_VALIDATION_SOURCE') IN ('IB', 'MO') OR
3730 Fnd_Profile.Value('OKS_CONTRACTS_VALIDATION_SOURCE') Is NULL Then
3731
3732 Okc_context.set_okc_org_context (l_order_rec.org_id, NULL );
3733
3734 Elsif Fnd_Profile.Value('OKS_CONTRACTS_VALIDATION_SOURCE') = 'SO' Then
3735
3736 l_organization_id := Null;
3737 If l_order_rec.sold_from_org_id Is Not Null Then
3738 Open l_organization_csr(l_order_rec.sold_from_org_id);
3739 Fetch l_organization_csr into l_organization_id;
3740 Close l_organization_csr;
3741 Else
3742 l_organization_id := Null;
3743 End If;
3744
3745 Okc_context.set_okc_org_context (l_order_rec.org_id, l_organization_id);
3746
3747 Elsif Fnd_Profile.Value('OKS_CONTRACTS_VALIDATION_SOURCE') = 'SH' Then
3748
3749 Okc_context.set_okc_org_context (l_order_rec.org_id, l_order_rec.ship_from_org_id);
3750
3751 End If;
3752 */
3753 l_cp_tbl.DELETE;
3754
3755 IF NVL (l_serv_ref_rec.service_reference_type_code, 'REF_TYPE') =
3756 'ORDER'
3757 THEN ------REF TYPE ORDER
3758 OPEN l_order_csr (l_order_rec.rolineid);
3759
3760 FETCH l_order_csr
3761 INTO l_ref_order_rec;
3762
3763 IF l_order_csr%NOTFOUND
3764 THEN
3765 l_order_rec.fqty := 0;
3766 END IF;
3767
3768 CLOSE l_order_csr;
3769
3770 fnd_file.put_line (fnd_file.LOG, 'ORDER : L_ORDER_CSR ');
3771
3772 IF l_order_rec.header_id = l_ref_order_rec.header_id
3773 THEN -----ORDER HEADER ID EQLS
3774 fnd_file.put_line
3775 (fnd_file.LOG,
3776 'ORDER HEADER ID EQLS : L_PROCESS TRUE '
3777 );
3778 l_process := TRUE;
3779 l_cp_ctr := 1;
3780
3781 /*modified for bug6181908 --fp bug6006309*/
3782 FOR rec IN
3783 l_custprod_csr
3784 (l_serv_ref_rec.service_reference_line_id, okc_context.get_okc_organization_id)
3785 LOOP
3786 fnd_file.put_line (fnd_file.LOG,
3787 'L_CP_TBL CP ID '
3788 || rec.cp_id
3789 || ' Cnt '
3790 || l_cp_ctr
3791 );
3792 l_cp_tbl (l_cp_ctr).cp_id := rec.cp_id;
3793 l_cp_ctr := l_cp_ctr + 1;
3794 END LOOP;
3795
3796 IF l_cp_ctr = 1
3797 THEN
3798 l_process := FALSE;
3799 END IF;
3800 ELSE
3801 l_process := TRUE;
3802 fnd_file.put_line
3803 (fnd_file.LOG,
3804 'ORDER HEADER ID NOT EQLS : L_CUSTPROD '
3805 );
3806 l_cp_ctr := 1;
3807
3808 /*modified for bug6181908 -- fp bug6006309*/
3809 FOR rec IN
3810 l_custprod_csr
3811 (l_serv_ref_rec.service_reference_line_id, okc_context.get_okc_organization_id)
3812 LOOP
3813 fnd_file.put_line (fnd_file.LOG,
3814 'L_CP_TBL CP ID '
3815 || rec.cp_id
3816 || ' Cnt '
3817 || l_cp_ctr
3818 );
3819 l_cp_tbl (l_cp_ctr).cp_id := rec.cp_id;
3820 l_cp_ctr := l_cp_ctr + 1;
3821 END LOOP;
3822
3823 IF l_cp_ctr = 1
3824 THEN
3825 l_process := FALSE;
3826 END IF;
3827 END IF; -----ORDER HEADER ID EQLS
3828 ELSE ------REF TYPE CUST PROD
3829 fnd_file.put_line (fnd_file.LOG, 'ElSE L_PROCESS TRUE ');
3830 l_cp_tbl (1).cp_id :=
3831 l_serv_ref_rec.service_reference_line_id;
3832 l_process := TRUE;
3833 END IF; ------REF TYPE ORDER
3834
3835 /* Bug 2324668
3836 In case Order Capture Integration is run when SFM is down then
3837 Customer product would not exist, and the Service line will
3838 get processed without creating any Contract.Now in case SFM is up
3839 and running , but still if there is a timing issue for fulfillment
3840 of lines then no contract will get created.So the next time
3841 Order Capture is run, the line does'nt get picked and no Contract
3842 is created at all.
3843 To fix this issue, populating the exception queue when the product
3844 is not in IB, so that the line will be picked again.
3845
3846 */
3847 IF NOT l_process
3848 THEN
3849 fnd_file.put_line (fnd_file.LOG, 'Cannot process line ...');
3850 l_upd_tbl (1).ID := l_repv_tbl (l_count).ID;
3851 l_upd_tbl (1).ERROR_TEXT := '#';
3852 fnd_message.set_name ('OKS', 'OKS_REQUEST');
3853 fnd_message.set_token (token => 'ID', VALUE => l_req_id);
3854 l_upd_tbl (1).ERROR_TEXT :=
3855 l_upd_tbl (1).ERROR_TEXT || fnd_message.get_encoded
3856 || '#';
3857 fnd_message.set_name ('OKS', 'OKS_PRODUCT_NOT_FOUND');
3858 l_upd_tbl (1).ERROR_TEXT :=
3859 l_upd_tbl (1).ERROR_TEXT || fnd_message.get_encoded
3860 || '#';
3861 --'Referenced Product not present in the Installed Base';
3862 l_upd_tbl (1).success_flag := 'E';
3863 l_upd_tbl (1).conc_request_id := NULL;
3864 handle_order_error (x_return_status => l_return_status,
3865 p_upd_rec => l_upd_tbl (1)
3866 );
3867
3868 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
3869 THEN
3870 RAISE g_exception_halt_validation;
3871 END IF;
3872 END IF;
3873
3874 IF l_process
3875 THEN
3876 l_k_line_id := NULL;
3877 l_ctr1 := 0;
3878 l_upd_tbl (1).subline_id := NULL;
3879
3880 OPEN l_contract_csr (l_repv_tbl (l_count).order_line_id);
3881
3882 LOOP
3883 l_ctr1 := l_ctr1 + 1;
3884
3885 FETCH l_contract_csr
3886 INTO l_upd_tbl (l_ctr1).subline_id,
3887 l_upd_tbl (l_ctr1).contract_id;
3888
3889 --FND_FILE.PUT_LINE (FND_FILE.LOG, 'cov_id = '||l_upd_tbl(l_ctr1).subline_id);
3890 EXIT WHEN l_contract_csr%NOTFOUND;
3891 END LOOP;
3892
3893 CLOSE l_contract_csr;
3894
3895 fnd_file.put_line (fnd_file.LOG, 'l_ctr1 = ' || l_ctr1);
3896 fnd_file.put_line (fnd_file.LOG,
3897 'DUPLICATE CHECK l_K_LINE_ID '
3898 || NVL (l_upd_tbl (1).subline_id, -12345)
3899 );
3900
3901 IF l_upd_tbl (1).subline_id IS NULL
3902 THEN --Duplication Check
3903 DBMS_TRANSACTION.SAVEPOINT ('OKS_REPROC');
3904 l_reproc_line_rec := l_repv_tbl (l_count);
3905 create_contract (p_reproc_line_rec => l_reproc_line_rec,
3906 x_upd_tbl => l_upd_tbl,
3907 x_return_status => l_return_status
3908 );
3909 fnd_file.put_line (fnd_file.LOG,
3910 'Create_Contract status ::'
3911 || l_return_status
3912 );
3913
3914 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
3915 THEN
3916 l_error_msg := '#';
3917
3918 FOR i IN 1 .. fnd_msg_pub.count_msg
3919 LOOP
3920 fnd_msg_pub.get (p_msg_index => i,
3921 p_encoded => 'T',
3922 p_data => l_msg_data,
3923 p_msg_index_out => l_index
3924 );
3925 l_error_msg := l_error_msg || l_msg_data || '#';
3926 fnd_message.set_encoded (l_msg_data);
3927 l_msg_data := fnd_message.get;
3928 fnd_file.put_line (fnd_file.LOG,
3929 'GET ORDER LINE FAILURE '
3930 || l_msg_data
3931 );
3932 END LOOP;
3933
3934 DBMS_TRANSACTION.rollback_savepoint ('OKS_REPROC');
3935 l_error_temp := '#';
3936 fnd_message.set_name ('OKS', 'OKS_REQUEST');
3937 fnd_message.set_token (token => 'ID',
3938 VALUE => l_req_id
3939 );
3940 l_error_temp :=
3941 l_error_temp || fnd_message.get_encoded;
3942
3943 IF (l_upd_tbl (1).ERROR_TEXT = okc_api.g_miss_char)
3944 THEN
3945 l_upd_tbl (1).ERROR_TEXT :=
3946 l_error_temp || l_error_msg;
3947 l_upd_tbl (1).success_flag := 'E';
3948 ELSE
3949 l_upd_tbl (1).ERROR_TEXT :=
3950 l_error_temp || l_upd_tbl (1).ERROR_TEXT;
3951 END IF;
3952
3953 l_upd_tbl (1).ID := l_repv_tbl (l_count).ID;
3954 l_upd_tbl (1).contract_id := NULL;
3955 l_upd_tbl (1).subline_id := NULL;
3956 l_upd_tbl (1).contract_line_id := NULL;
3957 l_upd_tbl (1).conc_request_id := NULL;
3958 handle_order_error
3959 (x_return_status => l_return_status,
3960 p_upd_rec => l_upd_tbl
3961 (1)
3962 );
3963
3964 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
3965 THEN
3966 RAISE g_exception_halt_validation;
3967 END IF;
3968 ELSE
3969
3970 -- OM INT User Hook Start Bug# 4462061
3971
3972 --Add Post_integration call out as part of code re-structuring for
3973 --license migrations.
3974 --call out starts here
3975
3976 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
3977 THEN
3978 fnd_log.string(FND_LOG.LEVEL_STATEMENT
3979 ,G_MODULE_CURRENT||'.ORDER_REPROCESS'
3980 ,'Before OKS_OMIB_EXTNS_PUB.post_integration call: ' ||
3981 ' ,p_transaction_type = ' || NULL ||
3982 ' ,p_transaction_date = ' || NULL ||
3983 ' ,p_order_line_id = ' || l_upd_tbl(1).order_line_id ||
3984 ' ,p_old_instance_id = ' || NULL ||
3985 ' ,p_new_instance_id = ' || NULL ||
3986 ' ,p_chr_id = ' || l_upd_tbl(1).contract_id ||
3987 ' ,p_topline_id = ' || l_upd_tbl(1).contract_line_id ||
3988 ' ,p_subline_id = ' || l_upd_tbl(1).subline_id);
3989 END IF;
3990 OKS_OMIB_INT_EXTNS_PUB.post_integration
3991 (p_api_version => 1.0
3992 ,p_init_msg_list => 'T'
3993 ,p_from_integration => 'OREP'
3994 ,p_transaction_type => NULL
3995 ,p_transaction_date => NULL
3996 ,p_order_line_id => l_upd_tbl(1).order_line_id
3997 ,p_old_instance_id => NULL
3998 ,p_new_instance_id => NULL
3999 ,p_chr_id => l_upd_tbl(1).contract_id
4000 ,p_topline_id => l_upd_tbl(1).contract_line_id
4001 ,p_subline_id => l_upd_tbl(1).subline_id
4002 ,x_return_status => x_return_status
4003 ,x_msg_count => x_msg_count
4004 ,x_msg_data => x_msg_data);
4005 IF fnd_log.level_event >= fnd_log.g_current_runtime_level
4006 THEN
4007 fnd_log.string(FND_LOG.LEVEL_EVENT
4008 ,G_MODULE_CURRENT||'.IB_INTERFACE'
4009 ,'After OKS_OMIB_INT_EXTNS_PUB.post_integration Call: ' ||
4010 ' ,x_return_status = ' || x_return_status);
4011 END IF;
4012 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
4013 THEN
4014 RAISE G_EXCEPTION_HALT_VALIDATION;
4015 END IF;
4016
4017 --call out ends here
4018
4019 -- OM INT User Hook End
4020
4021 l_error_temp := '#';
4022 fnd_message.set_name ('OKS', 'OKS_REQUEST');
4023 fnd_message.set_token (token => 'ID',
4024 VALUE => l_req_id
4025 );
4026 l_error_temp :=
4027 l_error_temp || fnd_message.get_encoded || '#';
4028 l_upd_tbl (1).ID := l_repv_tbl (l_count).ID;
4029 fnd_message.set_name ('OKS', 'OKS_CONTRACT_SUCCESS');
4030 l_upd_tbl (1).ERROR_TEXT := fnd_message.get_encoded;
4031 --'Contract Successfully created';
4032 l_upd_tbl (1).ERROR_TEXT :=
4033 l_error_temp || l_upd_tbl (1).ERROR_TEXT || '#';
4034 l_upd_tbl (1).success_flag := 'S';
4035 l_upd_tbl (1).conc_request_id := NULL;
4036 handle_order_error
4037 (x_return_status => l_return_status,
4038 p_upd_rec => l_upd_tbl
4039 (1)
4040 );
4041
4042 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
4043 THEN
4044 RAISE g_exception_halt_validation;
4045 END IF;
4046
4047 FOR i IN 2 .. l_upd_tbl.LAST
4048 LOOP
4049 fnd_message.set_name ('OKS', 'OKS_REQUEST');
4050 fnd_message.set_token (token => 'ID',
4051 VALUE => l_req_id
4052 );
4053 l_upd_tbl (i).ERROR_TEXT :=
4054 '#' || fnd_message.get_encoded || '#';
4055 fnd_message.set_name ('OKS',
4056 'OKS_CONTRACT_SUCCESS');
4057 l_upd_tbl (i).ERROR_TEXT :=
4058 l_upd_tbl (i).ERROR_TEXT
4059 || fnd_message.get_encoded
4060 || '#'; --'Contract Successfully created';
4061 l_upd_tbl (i).success_flag := 'S';
4062 l_upd_tbl (i).conc_request_id := NULL;
4063 l_upd_tbl (i).order_line_id :=
4064 l_repv_tbl (l_count).order_line_id;
4065 l_upd_tbl (i).order_number :=
4066 l_repv_tbl (l_count).order_number;
4067 l_upd_tbl (i).order_id :=
4068 l_repv_tbl (l_count).order_id;
4069 l_upd_tbl (i).source_flag :=
4070 l_repv_tbl (l_count).source_flag;
4071 --'ASO';
4072 l_upd_tbl (i).reprocess_yn := 'N';
4073 oks_rep_pub.insert_row
4074 (p_api_version => 1.0,
4075 p_init_msg_list => l_init_msg_list,
4076 x_return_status => l_return_status,
4077 x_msg_count => l_msg_count,
4078 x_msg_data => l_msg_data,
4079 p_repv_rec => l_upd_tbl
4080 (i),
4081 x_repv_rec => l_out_repv_rec
4082 );
4083 fnd_file.put_line (fnd_file.LOG,
4084 'L_return_status :'
4085 || l_return_status
4086 );
4087
4088 IF NOT (l_return_status = fnd_api.g_ret_sts_success
4089 )
4090 THEN
4091 FOR i IN 1 .. fnd_msg_pub.count_msg
4092 LOOP
4093 fnd_msg_pub.get (p_msg_index => -1,
4094 p_encoded => 'F',
4095 p_data => l_msg_data,
4096 p_msg_index_out => l_index
4097 );
4098 fnd_file.put_line
4099 (fnd_file.LOG,
4100 'ORDER_REPROCESS.Insert_row'
4101 || l_msg_data
4102 );
4103 END LOOP;
4104 END IF;
4105 END LOOP;
4106 END IF;
4107 ELSE /* If Duplicate */
4108 l_ctr1 := l_ctr1 - 1;
4109
4110 OPEN l_contract_line_csr (l_upd_tbl (1).subline_id);
4111
4112 FETCH l_contract_line_csr
4113 INTO l_cont_line_id;
4114
4115 CLOSE l_contract_line_csr;
4116
4117 l_upd_tbl (1).contract_line_id := l_cont_line_id;
4118 l_upd_tbl (1).success_flag := 'S';
4119 fnd_message.set_name ('OKS', 'OKS_REQUEST');
4120 fnd_message.set_token (token => 'ID',
4121 VALUE => l_req_id);
4122 l_upd_tbl (1).ERROR_TEXT :=
4123 '#' || fnd_message.get_encoded || '#';
4124 fnd_message.set_name ('OKS', 'OKS_DUPLICATE_ORD_LINE');
4125 l_upd_tbl (1).ERROR_TEXT :=
4126 l_upd_tbl (1).ERROR_TEXT
4127 || fnd_message.get_encoded
4128 || '#';
4129 l_upd_tbl (1).order_line_id :=
4130 l_repv_tbl (l_count).order_line_id;
4131 l_upd_tbl (1).ID := l_repv_tbl (l_count).ID;
4132 l_upd_tbl (1).conc_request_id := NULL;
4133 handle_order_error (x_return_status => l_return_status,
4134 p_upd_rec => l_upd_tbl (1)
4135 );
4136
4137 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
4138 THEN
4139 RAISE g_exception_halt_validation;
4140 END IF;
4141
4142 --FND_FILE.PUT_LINE (FND_FILE.LOG, 'l_ctr1 = '||l_ctr1);
4143 IF (l_ctr1 > 1)
4144 THEN
4145 FOR i IN 2 .. l_ctr1
4146 LOOP
4147 l_upd_tbl (i).contract_line_id := l_cont_line_id;
4148 l_upd_tbl (i).success_flag := 'S';
4149 fnd_message.set_name ('OKS', 'OKS_REQUEST');
4150 fnd_message.set_token (token => 'ID',
4151 VALUE => l_req_id
4152 );
4153 l_upd_tbl (i).ERROR_TEXT :=
4154 '#' || fnd_message.get_encoded || '#';
4155 fnd_message.set_name ('OKS',
4156 'OKS_DUPLICATE_ORD_LINE'
4157 );
4158 l_upd_tbl (i).ERROR_TEXT :=
4159 l_upd_tbl (i).ERROR_TEXT
4160 || fnd_message.get_encoded
4161 || '#';
4162 l_upd_tbl (i).order_line_id :=
4163 l_repv_tbl (l_count).order_line_id;
4164 l_upd_tbl (i).order_number :=
4165 l_repv_tbl (l_count).order_number;
4166 l_upd_tbl (i).order_id :=
4167 l_repv_tbl (l_count).order_id;
4168 l_upd_tbl (i).source_flag :=
4169 l_repv_tbl (l_count).source_flag;
4170 l_upd_tbl (i).conc_request_id := NULL;
4171 l_upd_tbl (i).reprocess_yn := 'N';
4172 oks_rep_pub.insert_row
4173 (p_api_version => 1.0,
4174 p_init_msg_list => l_init_msg_list,
4175 x_return_status => l_return_status,
4176 x_msg_count => l_msg_count,
4177 x_msg_data => l_msg_data,
4178 p_repv_rec => l_upd_tbl
4179 (i),
4180 x_repv_rec => l_out_repv_rec
4181 );
4182 fnd_file.put_line (fnd_file.LOG,
4183 'L_return_status :'
4184 || l_return_status
4185 );
4186
4187 IF NOT (l_return_status = fnd_api.g_ret_sts_success
4188 )
4189 THEN
4190 FOR i IN 1 .. fnd_msg_pub.count_msg
4191 LOOP
4192 fnd_msg_pub.get (p_msg_index => -1,
4193 p_encoded => 'F',
4194 p_data => l_msg_data,
4195 p_msg_index_out => l_index
4196 );
4197 fnd_file.put_line
4198 (fnd_file.LOG,
4199 'ORDER_REPROCESS.Insert_row'
4200 || l_msg_data
4201 );
4202 END LOOP;
4203 END IF;
4204 END LOOP;
4205 END IF;
4206 END IF; -- Duplicate Check
4207 END IF; -- l_process is true
4208 END IF;
4209
4210 -- OM INT USER HOOK START Bug# 4462061
4211
4212 ELSE -- else of l_process_status from pre_integration
4213
4214 --Call out to Post starts here if l_process_status <> 'C'(dont want to continue with
4215 --the existing logic above
4216 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
4217 THEN
4218 fnd_log.string(FND_LOG.LEVEL_STATEMENT
4219 ,G_MODULE_CURRENT||'.ORDER_REPROCESS'
4220 ,'Before OKS_OMIB_EXTNS_PUB.post_integration call: ' ||
4221 ' ,p_transaction_type = ' || NULL ||
4222 ' ,p_transaction_date = ' || NULL ||
4223 ' ,p_order_line_id = ' || l_repv_tbl(l_count).order_line_id ||
4224 ' ,p_old_instance_id = ' || NULL ||
4225 ' ,p_new_instance_id = ' || NULL ||
4226 ' ,p_chr_id = ' || NULL ||
4227 ' ,p_topline_id = ' || NULL ||
4228 ' ,p_subline_id = ' || NULL);
4229 END IF;
4230
4231 OKS_OMIB_INT_EXTNS_PUB.post_integration
4232 (p_api_version => 1.0
4233 ,p_init_msg_list => 'T'
4234 ,p_from_integration => 'OREP'
4235 ,p_transaction_type => NULL
4236 ,p_transaction_date => NULL
4237 ,p_order_line_id => l_repv_tbl(l_count).order_line_id
4238 ,p_old_instance_id => NULL
4239 ,p_new_instance_id => NULL
4240 ,p_chr_id => NULL
4241 ,p_topline_id => NULL
4242 ,p_subline_id => NULL
4243 ,x_return_status => x_return_status
4244 ,x_msg_count => x_msg_count
4245 ,x_msg_data => x_msg_data);
4246 IF fnd_log.level_event >= fnd_log.g_current_runtime_level
4247 THEN
4248 fnd_log.string(FND_LOG.LEVEL_EVENT
4249 ,G_MODULE_CURRENT||'.IB_INTERFACE'
4250 ,'After OKS_OMIB_INT_EXTNS_PUB.post_integration Call: ' ||
4251 ' ,x_return_status = ' || x_return_status);
4252 END IF;
4253 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
4254 THEN
4255 RAISE G_EXCEPTION_HALT_VALIDATION;
4256 END IF;
4257 END IF; --IF of l_process status check
4258 --Call out to Post ends here
4259 -- OM INT USER HOOK END
4260
4261 END LOOP; -- end loop on l_line_count tbl
4262 END IF; -- For l_line_count
4263
4264 EXCEPTION
4265 WHEN g_exception_halt_validation
4266 THEN
4267 x_return_status := l_return_status;
4268 fnd_file.put_line
4269 (fnd_file.LOG,
4270 ' Error in Order Reprocess - l_return_status = '
4271 || l_return_status
4272 );
4273 WHEN OTHERS
4274 THEN
4275 fnd_file.put_line (fnd_file.LOG,
4276 'In OTHERS Exception of Order_Reprocess'
4277 );
4278 x_return_status := okc_api.g_ret_sts_unexp_error;
4279 fnd_file.put_line (fnd_file.LOG,
4280 ' Error in Order Reprocess : '
4281 || SQLCODE
4282 || ':'
4283 || SQLERRM
4284 );
4285 okc_api.set_message (g_app_name,
4286 g_unexpected_error,
4287 g_sqlcode_token,
4288 SQLCODE,
4289 g_sqlerrm_token,
4290 SQLERRM
4291 );
4292 END order_reprocess;
4293
4294 PROCEDURE oks_order_purge (
4295 errbuf OUT NOCOPY VARCHAR2,
4296 retcode OUT NOCOPY NUMBER
4297 )
4298 IS
4299 l_return_status VARCHAR2 (1) := okc_api.g_ret_sts_success;
4300 l_msg_count NUMBER;
4301 l_msg_data VARCHAR2 (2000);
4302 l_user_id NUMBER;
4303 l_del_rec oks_rep_pvt.repv_rec_type;
4304 l_repv_tbl oks_rep_pvt.repv_tbl_type;
4305 l_index NUMBER;
4306 l_line_count NUMBER;
4307 l_api_version CONSTANT NUMBER := 1.0;
4308 l_init_msg_list VARCHAR2 (1) DEFAULT fnd_api.g_false;
4309
4310 PROCEDURE get_order_lines (
4311 x_return_status OUT NOCOPY VARCHAR2,
4312 x_msg_count OUT NOCOPY NUMBER,
4313 x_msg_data OUT NOCOPY VARCHAR2,
4314 x_repv_tbl OUT NOCOPY oks_rep_pvt.repv_tbl_type
4315 )
4316 IS
4317 CURSOR l_order_line_csr
4318 IS
4319 SELECT ID
4320 FROM oks_reprocessing_v
4321 WHERE success_flag = 'S';
4322
4323 l_repv_tbl oks_rep_pvt.repv_tbl_type;
4324 l_return_status VARCHAR2 (1) := okc_api.g_ret_sts_success;
4325 l_ordline_rec oks_rep_pvt.repv_rec_type;
4326 l_ptr NUMBER;
4327 BEGIN
4328 fnd_file.put_line (fnd_file.LOG, 'Inside procedure Get Order lines');
4329 l_ptr := 0;
4330
4331 FOR l_ordline_rec IN l_order_line_csr
4332 LOOP
4333 l_ptr := l_ptr + 1;
4334 x_repv_tbl (l_ptr).ID := l_ordline_rec.ID;
4335 --X_Repv_tbl(l_ptr).order_line_id := l_ordline_rec.order_line_id;
4336 END LOOP;
4337
4338 x_return_status := l_return_status;
4339 fnd_file.put_line (fnd_file.LOG, 'Exiting Get Order Details');
4340 EXCEPTION
4341 WHEN OTHERS
4342 THEN
4343 x_return_status := okc_api.g_ret_sts_unexp_error;
4344 fnd_file.put_line (fnd_file.LOG,
4345 ' Error in Get_Order_Details : '
4346 || SQLCODE
4347 || ':'
4348 || SQLERRM
4349 );
4350 okc_api.set_message (g_app_name,
4351 g_unexpected_error,
4352 g_sqlcode_token,
4353 SQLCODE,
4354 g_sqlerrm_token,
4355 SQLERRM
4356 );
4357 END get_order_lines;
4358 BEGIN
4359 fnd_file.put_line (fnd_file.LOG, 'Start of OKS_ORDER_PURGE_PVT...');
4360 l_user_id := fnd_global.user_id;
4361 fnd_file.put_line (fnd_file.LOG, 'User Id : ' || TO_CHAR (l_user_id));
4362 get_order_lines (x_return_status => l_return_status,
4363 x_msg_count => l_msg_count,
4364 x_msg_data => l_msg_data,
4365 x_repv_tbl => l_repv_tbl
4366 );
4367 fnd_file.put_line (fnd_file.LOG,
4368 'Get Order Lines: l_return_status = '
4369 || l_return_status
4370 );
4371
4372 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
4373 THEN
4374 FOR i IN 1 .. fnd_msg_pub.count_msg
4375 LOOP
4376 fnd_msg_pub.get (p_msg_index => -1,
4377 p_encoded => 'F',
4378 p_data => l_msg_data,
4379 p_msg_index_out => l_index
4380 );
4381 fnd_file.put_line (fnd_file.LOG,
4382 'GET ORDER LINES: ' || l_msg_data);
4383 END LOOP;
4384
4385 RAISE g_exception_halt_validation;
4386 END IF;
4387
4388 l_line_count := l_repv_tbl.COUNT;
4389
4390 IF (l_line_count <= 0)
4391 THEN
4392 fnd_file.put_line (fnd_file.LOG, 'No lines to Purge..');
4393 ELSE
4394 fnd_file.put_line (fnd_file.LOG,
4395 'Purging Order Lines... Line count = '
4396 || l_line_count
4397 );
4398
4399 FOR l_count IN 1 .. l_line_count
4400 LOOP
4401 l_del_rec.ID := l_repv_tbl (l_count).ID;
4402 oks_rep_pub.delete_row (p_api_version => l_api_version,
4403 p_init_msg_list => l_init_msg_list,
4404 x_return_status => l_return_status,
4405 x_msg_count => l_msg_count,
4406 x_msg_data => l_msg_data,
4407 p_repv_rec => l_del_rec
4408 );
4409 END LOOP;
4410 END IF;
4411
4412 errbuf := '';
4413 retcode := 0;
4414 EXCEPTION
4415 WHEN OTHERS
4416 THEN
4417 ROLLBACK;
4418 -- Retrieve error message into errbuf
4419 errbuf := SQLERRM;
4420 retcode := 2;
4421 fnd_file.put_line (fnd_file.LOG,
4422 'Oracle Error Code is -' || TO_CHAR (SQLCODE)
4423 );
4424 fnd_file.put_line (fnd_file.LOG,
4425 'Oracle Error Message is -'
4426 || SUBSTR (SQLERRM, 1, 512)
4427 );
4428 END oks_order_purge;
4429
4430 PROCEDURE migrate_aso_queue (
4431 errbuf OUT NOCOPY VARCHAR2,
4432 retcode OUT NOCOPY NUMBER
4433 )
4434 IS
4435 l_init_msg_list VARCHAR2 (1) DEFAULT fnd_api.g_false;
4436 l_commit VARCHAR2 (1) DEFAULT fnd_api.g_false;
4437 l_return_status VARCHAR2 (1);
4438 l_msg_count NUMBER;
4439 l_msg_data VARCHAR2 (2000);
4440 l_wait NUMBER DEFAULT DBMS_AQ.no_wait;
4441 l_no_more_messages VARCHAR2 (240);
4442 l_header_rec oe_order_pub.header_rec_type;
4443 l_old_header_rec oe_order_pub.header_rec_type;
4444 l_header_adj_tbl oe_order_pub.header_adj_tbl_type;
4445 l_old_header_adj_tbl oe_order_pub.header_adj_tbl_type;
4446 l_header_price_att_tbl oe_order_pub.header_price_att_tbl_type;
4447 l_old_header_price_att_tbl oe_order_pub.header_price_att_tbl_type;
4448 l_header_adj_att_tbl oe_order_pub.header_adj_att_tbl_type;
4449 l_old_header_adj_att_tbl oe_order_pub.header_adj_att_tbl_type;
4450 l_header_adj_assoc_tbl oe_order_pub.header_adj_assoc_tbl_type;
4451 l_old_header_adj_assoc_tbl oe_order_pub.header_adj_assoc_tbl_type;
4452 l_header_scredit_tbl oe_order_pub.header_scredit_tbl_type;
4453 l_old_header_scredit_tbl oe_order_pub.header_scredit_tbl_type;
4454 l_line_tbl oe_order_pub.line_tbl_type;
4455 l_old_line_tbl oe_order_pub.line_tbl_type;
4456 l_line_adj_tbl oe_order_pub.line_adj_tbl_type;
4457 l_old_line_adj_tbl oe_order_pub.line_adj_tbl_type;
4458 l_line_price_att_tbl oe_order_pub.line_price_att_tbl_type;
4459 l_old_line_price_att_tbl oe_order_pub.line_price_att_tbl_type;
4460 l_line_adj_att_tbl oe_order_pub.line_adj_att_tbl_type;
4461 l_old_line_adj_att_tbl oe_order_pub.line_adj_att_tbl_type;
4462 l_line_adj_assoc_tbl oe_order_pub.line_adj_assoc_tbl_type;
4463 l_old_line_adj_assoc_tbl oe_order_pub.line_adj_assoc_tbl_type;
4464 l_line_scredit_tbl oe_order_pub.line_scredit_tbl_type;
4465 l_old_line_scredit_tbl oe_order_pub.line_scredit_tbl_type;
4466 l_lot_serial_tbl oe_order_pub.lot_serial_tbl_type;
4467 l_old_lot_serial_tbl oe_order_pub.lot_serial_tbl_type;
4468 l_action_request_tbl oe_order_pub.request_tbl_type;
4469 l_oe_line_rec oe_order_pub.line_rec_type;
4470 l_dequeue_mode VARCHAR2 (240) DEFAULT DBMS_AQ.remove;
4471 l_navigation VARCHAR2 (240)
4472 DEFAULT DBMS_AQ.next_message;
4473 l_repv_rec oks_rep_pvt.repv_rec_type;
4474 l_out_repv_rec oks_rep_pvt.repv_rec_type;
4475 l_request_id NUMBER;
4476 --General Variables
4477 l_ctr NUMBER;
4478 l_oldline_count NUMBER;
4479 l_newline_count NUMBER;
4480 l_user_id NUMBER;
4481 l_index NUMBER;
4482 index1 NUMBER;
4483 err_msg VARCHAR2 (1000) := '';
4484 l_hdrid NUMBER := NULL;
4485 dup_val VARCHAR2 (1) := '';
4486 l_ord_num NUMBER;
4487 l_exists VARCHAR2 (1);
4488
4489 CURSOR l_order_hdr_csr (p_ordlineid NUMBER)
4490 IS
4491 SELECT oh.header_id, oh.order_number
4492 FROM oe_order_lines_all ol, oe_order_headers_all oh
4493 WHERE ol.line_id = (p_ordlineid)
4494 AND oh.org_id = okc_context.get_okc_org_id
4495 AND oh.header_id = ol.header_id;
4496
4497 CURSOR l_order_csr (p_ordlineid NUMBER)
4498 IS
4499 SELECT org_id, ship_from_org_id, sold_from_org_id,
4500 NVL (fulfilled_quantity, 0) fqty,
4501 service_reference_line_id rolineid, header_id
4502 FROM okx_order_lines_v
4503 WHERE id1 = (p_ordlineid);
4504
4505 CURSOR check_ordline_exists (p_ordlineid NUMBER)
4506 IS
4507 SELECT 'x'
4508 FROM oks_reprocessing
4509 WHERE order_line_id = p_ordlineid;
4510
4511 --Fix for bug 3492335
4512 CURSOR is_ib_trackable (
4513 l_ref_order_line_id NUMBER,
4514 l_organization_id NUMBER
4515 )
4516 IS
4517 SELECT comms_nl_trackable_flag
4518 FROM mtl_system_items_b
4519 WHERE inventory_item_id = (SELECT inventory_item_id
4520 FROM oe_order_lines_all
4521 WHERE line_id = l_ref_order_line_id)
4522 AND organization_id = l_organization_id;
4523
4524 l_order_rec l_order_csr%ROWTYPE;
4525 l_api_version CONSTANT NUMBER := 1.0;
4526 x_msg_count NUMBER;
4527 x_msg_data VARCHAR2 (2000);
4528 ib_flag VARCHAR2 (1);
4529 --
4530 aso_handle_exception EXCEPTION;
4531 aso_handle_normal EXCEPTION;
4532 BEGIN
4533 SAVEPOINT oks_migrate_aso_queue;
4534 fnd_file.put_line (fnd_file.LOG, 'Start of OC_interface...');
4535 l_user_id := fnd_global.user_id;
4536 fnd_file.put_line (fnd_file.LOG, 'User Id : ' || TO_CHAR (l_user_id));
4537 -- Set policy context to ALL
4538 mo_global.set_policy_context ('B', NULL);
4539
4540 LOOP
4541 l_oldline_count := 0;
4542 l_newline_count := 0;
4543 aso_order_feedback_pub.get_notice
4544 (p_api_version => 1.0,
4545 p_init_msg_list => l_init_msg_list,
4546 p_commit => l_commit,
4547 x_return_status => l_return_status,
4548 x_msg_count => l_msg_count,
4549 x_msg_data => l_msg_data,
4550 p_app_short_name => 'OKS',
4551 p_wait => l_wait,
4552 x_no_more_messages => l_no_more_messages,
4553 x_header_rec => l_header_rec,
4554 x_old_header_rec => l_old_header_rec,
4555 x_header_adj_tbl => l_header_adj_tbl,
4556 x_old_header_adj_tbl => l_old_header_adj_tbl,
4557 x_header_price_att_tbl => l_header_price_att_tbl,
4558 x_old_header_price_att_tbl => l_old_header_price_att_tbl,
4559 x_header_adj_att_tbl => l_header_adj_att_tbl,
4560 x_old_header_adj_att_tbl => l_old_header_adj_att_tbl,
4561 x_header_adj_assoc_tbl => l_header_adj_assoc_tbl,
4562 x_old_header_adj_assoc_tbl => l_old_header_adj_assoc_tbl,
4563 x_header_scredit_tbl => l_header_scredit_tbl,
4564 x_old_header_scredit_tbl => l_old_header_scredit_tbl,
4565 x_line_tbl => l_line_tbl,
4566 x_old_line_tbl => l_old_line_tbl,
4567 x_line_adj_tbl => l_line_adj_tbl,
4568 x_old_line_adj_tbl => l_old_line_adj_tbl,
4569 x_line_price_att_tbl => l_line_price_att_tbl,
4570 x_old_line_price_att_tbl => l_old_line_price_att_tbl,
4571 x_line_adj_att_tbl => l_line_adj_att_tbl,
4572 x_old_line_adj_att_tbl => l_old_line_adj_att_tbl,
4573 x_line_adj_assoc_tbl => l_line_adj_assoc_tbl,
4574 x_old_line_adj_assoc_tbl => l_old_line_adj_assoc_tbl,
4575 x_line_scredit_tbl => l_line_scredit_tbl,
4576 x_old_line_scredit_tbl => l_old_line_scredit_tbl,
4577 x_lot_serial_tbl => l_lot_serial_tbl,
4578 x_old_lot_serial_tbl => l_old_lot_serial_tbl,
4579 x_action_request_tbl => l_action_request_tbl
4580 );
4581
4582 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
4583 THEN
4584 FOR i IN 1 .. fnd_msg_pub.count_msg
4585 LOOP
4586 fnd_msg_pub.get (p_msg_index => -1,
4587 p_encoded => 'F',
4588 p_data => l_msg_data,
4589 p_msg_index_out => l_index
4590 );
4591 fnd_file.put_line (fnd_file.LOG, l_msg_data);
4592 END LOOP;
4593
4594 ROLLBACK TO oks_migrate_aso_queue;
4595 RETURN;
4596 END IF;
4597
4598 l_newline_count := l_line_tbl.COUNT;
4599 fnd_file.put_line (fnd_file.LOG,
4600 'Lines to process = ' || l_newline_count
4601 );
4602 EXIT WHEN l_no_more_messages = fnd_api.g_true;
4603
4604 IF l_newline_count <= 0
4605 THEN --Order Line Record Found
4606 fnd_file.put_line (fnd_file.LOG, 'No lines to insert');
4607 ELSE
4608 fnd_file.put_line (fnd_file.LOG,
4609 'Processing the order lines... line count='
4610 || TO_CHAR (l_newline_count)
4611 );
4612
4613 FOR l_count IN 1 .. l_newline_count
4614 LOOP
4615 l_exists := 'y';
4616 fnd_file.put_line (fnd_file.LOG,
4617 'Processing Order Line '
4618 || l_line_tbl (l_count).line_id
4619 );
4620 fnd_file.put_line
4621 (fnd_file.LOG,
4622 'Service Ref Type Code '
4623 || l_line_tbl (l_count).service_reference_type_code
4624 );
4625 fnd_file.put_line
4626 (fnd_file.LOG,
4627 'Service line Id '
4628 || l_line_tbl (l_count).service_reference_line_id
4629 );
4630 fnd_file.put_line
4631 (fnd_file.LOG,
4632 'Profile option value '
4633 || fnd_profile.VALUE
4634 ('OKS_CONTRACTS_VALIDATION_SOURCE')
4635 );
4636
4637 OPEN check_ordline_exists (l_line_tbl (l_count).line_id);
4638
4639 FETCH check_ordline_exists
4640 INTO l_exists;
4641
4642 IF check_ordline_exists%NOTFOUND
4643 THEN
4644 l_exists := 'y';
4645 END IF;
4646
4647 CLOSE check_ordline_exists;
4648
4649 --Check Delayed Service
4650 IF (l_exists <> 'x')
4651 THEN
4652 fnd_file.put_line (fnd_file.LOG,
4653 'Order line not present already...'
4654 );
4655
4656 IF NVL (l_line_tbl (l_count).service_reference_type_code,
4657 'REF_TYPE'
4658 ) IN ('CUSTOMER_PRODUCT', 'ORDER')
4659 AND l_line_tbl (l_count).service_reference_line_id IS NOT NULL
4660 THEN --Checking Delayed Service
4661 --Check Fulfillment
4662 l_order_rec.fqty := 0;
4663 l_order_rec.rolineid := NULL;
4664 l_order_rec.header_id := NULL;
4665
4666 OPEN l_order_csr (l_line_tbl (l_count).line_id);
4667
4668 FETCH l_order_csr
4669 INTO l_order_rec;
4670
4671 IF l_order_csr%NOTFOUND
4672 THEN
4673 l_order_rec.fqty := 0;
4674 END IF;
4675
4676 CLOSE l_order_csr;
4677
4678 fnd_file.put_line (fnd_file.LOG,
4679 'Fulfillment Quantity '
4680 || l_order_rec.fqty
4681 );
4682 fnd_file.put_line (fnd_file.LOG,
4683 'Order Org Id '
4684 || l_order_rec.org_id
4685 );
4686 fnd_file.put_line (fnd_file.LOG,
4687 'Order Organization Id '
4688 || l_order_rec.ship_from_org_id
4689 );
4690 fnd_file.put_line (fnd_file.LOG,
4691 'Sold from Org Id '
4692 || l_order_rec.sold_from_org_id
4693 );
4694 okc_context.set_okc_org_context (l_order_rec.org_id,
4695 NULL);
4696 fnd_file.put_line (fnd_file.LOG,
4697 'org_context - '
4698 || TO_CHAR (okc_context.get_okc_org_id)
4699 );
4700 fnd_file.put_line
4701 (fnd_file.LOG,
4702 'organization_context - '
4703 || TO_CHAR
4704 (okc_context.get_okc_organization_id)
4705 );
4706
4707 --Check IB Trackable flag
4708 IF NVL (l_line_tbl (l_count).service_reference_type_code,
4709 'REF_TYPE'
4710 ) = 'ORDER'
4711 THEN
4712 OPEN is_ib_trackable
4713 (l_line_tbl (l_count).service_reference_line_id,
4714 okc_context.get_okc_organization_id
4715 );
4716
4717 FETCH is_ib_trackable
4718 INTO ib_flag;
4719
4720 CLOSE is_ib_trackable;
4721 ELSE
4722 ib_flag := 'Y';
4723 END IF;
4724
4725 IF NVL (ib_flag, 'N') = 'Y'
4726 THEN
4727 IF l_order_rec.fqty > 0
4728 THEN
4729 OPEN l_order_hdr_csr (l_line_tbl (l_count).line_id);
4730
4731 FETCH l_order_hdr_csr
4732 INTO l_hdrid, l_ord_num;
4733
4734 IF l_order_hdr_csr%NOTFOUND
4735 THEN
4736 fnd_file.put_line (fnd_file.LOG,
4737 ' Invalid Order line ID'
4738 );
4739
4740 CLOSE l_order_hdr_csr;
4741 ELSE
4742 CLOSE l_order_hdr_csr;
4743
4744 fnd_file.put_line (fnd_file.LOG,
4745 'Order Header ID ' || l_hdrid
4746 );
4747 l_repv_rec.order_id := l_hdrid;
4748 l_repv_rec.order_line_id :=
4749 l_line_tbl (l_count).line_id;
4750 l_repv_rec.order_number := l_ord_num;
4751 l_repv_rec.success_flag := 'N';
4752 l_repv_rec.source_flag := 'ASO';
4753 l_repv_rec.reprocess_yn := 'Y';
4754 SAVEPOINT before_insert;
4755 oks_rep_pub.insert_row
4756 (p_api_version => 1.0,
4757 p_init_msg_list => l_init_msg_list,
4758 x_return_status => l_return_status,
4759 x_msg_count => l_msg_count,
4760 x_msg_data => l_msg_data,
4761 p_repv_rec => l_repv_rec,
4762 x_repv_rec => l_out_repv_rec
4763 );
4764 fnd_file.put_line
4765 (fnd_file.LOG,
4766 'OKS_REP_PUB - RETURN STATUS : '
4767 || l_return_status
4768 );
4769
4770 IF NOT (l_return_status =
4771 fnd_api.g_ret_sts_success
4772 )
4773 THEN
4774 FOR i IN 1 .. fnd_msg_pub.count_msg
4775 LOOP
4776 fnd_msg_pub.get
4777 (p_msg_index => -1,
4778 p_encoded => 'F',
4779 p_data => l_msg_data,
4780 p_msg_index_out => l_index
4781 );
4782
4783 SELECT INSTR (l_msg_data,
4784 'ORA-00001',
4785 1,
4786 1
4787 )
4788 INTO index1
4789 FROM DUAL;
4790
4791 IF (index1 > 0)
4792 THEN
4793 dup_val := 'Y';
4794 EXIT;
4795 END IF;
4796
4797 fnd_file.put_line
4798 (fnd_file.LOG,
4799 'oks_rep_pub.insert_row: '
4800 || l_msg_data
4801 );
4802 END LOOP;
4803
4804 IF (dup_val <> 'Y')
4805 THEN
4806 RAISE g_exception_halt_validation;
4807 END IF;
4808
4809 l_return_status := fnd_api.g_ret_sts_success;
4810 END IF;
4811 END IF;
4812 END IF;
4813 END IF;
4814 END IF;
4815 ELSE
4816 fnd_file.put_line (fnd_file.LOG, 'Duplicate Order Line');
4817 END IF;
4818 END LOOP;
4819 END IF;
4820 END LOOP;
4821
4822 errbuf := '';
4823 retcode := 0;
4824 COMMIT WORK;
4825 fnd_file.put_line (fnd_file.LOG, 'Order Capture INT Program finished.');
4826 EXCEPTION
4827 WHEN g_exception_halt_validation
4828 THEN
4829 ROLLBACK TO before_insert;
4830 WHEN OTHERS
4831 THEN
4832 ROLLBACK TO before_insert;
4833 -- Retrieve error message into errbuf
4834 errbuf := SQLERRM;
4835 retcode := 2;
4836 fnd_file.put_line (fnd_file.LOG,
4837 'Oracle Error Code is -' || TO_CHAR (SQLCODE)
4838 );
4839 fnd_file.put_line (fnd_file.LOG,
4840 'Oracle Error Message is -'
4841 || SUBSTR (SQLERRM, 1, 512)
4842 );
4843 END migrate_aso_queue;
4844 /*Added for 16039680*/
4845 Procedure OM_subscription_schedule
4846 ( p_api_version IN NUMBER,
4847 p_init_msg_list IN VARCHAR2,
4848 x_return_status OUT NOCOPY VARCHAR2,
4849 x_msg_count OUT NOCOPY Number,
4850 x_msg_data OUT NOCOPY VARCHAR2,
4851 p_cle_id IN NUMBER,
4852 p_template_id IN NUMBER DEFAULT NULL
4853 ) IS
4854 Cursor kl_cur Is
4855 Select KL.dnz_chr_id, KL.start_date, NVL(KL.date_terminated - 1,KL.end_date) end_date,
4856 KI.number_of_items, KI.uom_code,
4857 NVL(MTL.contract_item_type_code,'NON-SUB'), MTL.coverage_schedule_id, MTL.comms_nl_trackable_flag
4858 From okc_k_lines_b KL,
4859 okc_k_items KI,
4860 mtl_system_items MTL
4861 Where KL.id = p_cle_id
4862 and KI.cle_id = p_cle_id
4863 and MTL.inventory_item_id = TO_NUMBER(KI.object1_id1)
4864 and MTL.organization_id = TO_NUMBER(KI.object1_id2);
4865
4866 Cursor osh_cur(p_template_id In Number) Is
4867 Select name,
4868 description,
4869 cle_id,
4870 dnz_chr_id,
4871 subscription_type,
4872 media_type,
4873 frequency,
4874 fulfillment_channel,
4875 comments,
4876 status,
4877 item_type
4878 From oks_subscr_header_v
4879 Where id = p_template_id;
4880
4881 CURSOR l_refnum_csr (p_cp_id NUMBER)
4882 IS
4883 SELECT instance_number
4884 FROM csi_item_instances
4885 WHERE instance_id = p_cp_id;
4886
4887 CURSOR l_contract_hdr_dtls IS
4888 SELECT okhb.start_date,okhb.end_date,okcl.cust_acct_id,okcl.sts_code
4889 FROM okc_k_headers_all_b okhb,okc_k_lines_b okcl
4890 WHERE okhb.id=okcl.chr_id
4891 AND okcl.id=p_cle_id;
4892
4893
4894 l_hdr_tbl_in OKS_SUBSCR_HDR_PVT.schv_tbl_type;
4895 l_hdr_tbl_out OKS_SUBSCR_HDR_PVT.schv_tbl_type;
4896 l_ptrns_tbl_in OKS_SUBSCR_PTRNS_PVT.scpv_tbl_type;
4897 l_ptrns_tbl_out OKS_SUBSCR_PTRNS_PVT.scpv_tbl_type;
4898 l_elems_tbl_in OKS_SUBSCR_ELEMS_PVT.scev_tbl_type;
4899 l_elems_tbl_out OKS_SUBSCR_ELEMS_PVT.scev_tbl_type;
4900 l_pattern_tbl OKS_SUBSCRIPTION_SCH_PVT.pattern_tbl;
4901 l_delivery_tbl OKS_SUBSCRIPTION_SCH_PVT.del_tbl;
4902 -- Pricing Parameters
4903 l_price_details_in OKS_QP_PKG.INPUT_DETAILS;
4904 x_price_details_out OKS_QP_PKG.PRICE_DETAILS;
4905 x_mo_details QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
4906 x_pb_details OKS_QP_PKG.G_PRICE_BREAK_TBL_TYPE;
4907 -- Pricing Parameters
4908 g_chr_id Number;
4909 l_start_date Date;
4910 l_end_date Date;
4911 l_qty Number;
4912 l_uom Varchar2(10);
4913 l_template_id Number;
4914 l_instance_id Number := NULL;
4915 l_status Varchar2(10);
4916 l_itype Varchar2(30);
4917 l_tangible Varchar2(1);
4918 l_return_status Varchar2(20);
4919 l_msg_count Number;
4920 l_msg_data Varchar2(2000);
4921 i Number;
4922 idx Number;
4923 tot_qty Number;
4924 gen_exit EXCEPTION;
4925
4926 l_hdr_start_date DATE;
4927 l_hdr_end_date DATE;
4928 l_ref_num NUMBER;
4929 l_cust_acct_id NUMBER;
4930 l_sts_code VARCHAR2(100);
4931
4932 l_insthist_rec oks_ins_pvt.insv_rec_type;
4933 x_insthist_rec oks_ins_pvt.insv_rec_type;
4934 l_inst_dtls_rec oks_ihd_pvt.ihdv_rec_type;
4935 x_inst_dtls_rec oks_ihd_pvt.ihdv_rec_type;
4936
4937
4938
4939 Begin
4940 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4941 fnd_log.string(FND_LOG.LEVEL_PROCEDURE ,G_MODULE_CURRENT||'.OM_subscription_schedule.begin'
4942 ,'p_cle_id = '||p_cle_id);
4943 END IF;
4944 x_return_status := 'S';
4945 OKC_API.init_msg_list(p_init_msg_list);
4946 l_status := 'I';
4947 Open kl_cur;
4948 Fetch kl_cur Into g_chr_id, l_start_date, l_end_date, l_qty, l_uom, l_itype, l_template_id, l_tangible;
4949 IF kl_cur%NotFound THEN
4950 Close kl_cur;
4951 x_return_status := OKC_API.G_RET_STS_ERROR;
4952 OKC_API.set_message
4953 ( p_app_name => 'OKS',
4954 p_msg_name => 'OKS_SUB_INVAL_LINE',
4955 p_token1 => 'LINEID',
4956 p_token1_value => p_cle_id
4957 );
4958 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4959 fnd_log.string(FND_LOG.LEVEL_ERROR,G_MODULE_CURRENT||'.OM_subscription_schedule.ERROR','Invalid Line');
4960 END IF;
4961 Raise gen_exit;
4962 END IF;
4963 Close kl_cur;
4964
4965 /*OM can map a subscription template for the item in sales order which should be considered during contract creation*/
4966 IF p_template_id <> 1 THEN
4967 l_template_id :=p_template_id;
4968 END IF;
4969
4970 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4971 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.OM_subscription_schedule.line_details',
4972 'Header Id = '||g_chr_id||' ,Start Date = '||to_char(l_start_date,'DD-MON-YYYY')
4973 ||' ,End Date = '||to_char(l_end_date,'DD-MON-YYYY')||' ,Quantity = '||l_qty
4974 ||' ,UOM = '||l_uom||', Item Type Code = '||l_itype||' , Template Id = '||l_template_id
4975 ||' ,NL Trackable(For Non-Subscription Items) = '||l_tangible
4976 );
4977 END IF;
4978 IF l_itype = 'SUBSCRIPTION' Or l_tangible = 'Y' THEN
4979 -- For Subscription Items, get the subscription header details from the template
4980 If l_itype = 'SUBSCRIPTION' Then -- Tangible or Intangible Subscription Item
4981 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4982 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.OM_subscription_schedule.subs','it is a subscription item');
4983 END IF;
4984 if l_template_id is null then
4985 x_return_status := OKC_API.G_RET_STS_ERROR;
4986 OKC_API.set_message
4987 ( p_app_name => 'OKS',
4988 p_msg_name => 'OKS_SUB_NO_TMPL'
4989 );
4990 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4991 fnd_log.string(FND_LOG.LEVEL_ERROR,G_MODULE_CURRENT||'.OM_subscription_schedule.ERROR','No Template');
4992 END IF;
4993 Raise gen_exit;
4994 end if;
4995 For osh_rec In osh_cur(l_template_id) Loop
4996 l_hdr_tbl_in(1).name := osh_rec.name;
4997 l_hdr_tbl_in(1).description := osh_rec.description;
4998 l_hdr_tbl_in(1).cle_id := p_cle_id;
4999 l_hdr_tbl_in(1).dnz_chr_id := g_chr_id;
5000 l_hdr_tbl_in(1).subscription_type := osh_rec.subscription_type;
5001 l_hdr_tbl_in(1).media_type := osh_rec.media_type;
5002 l_hdr_tbl_in(1).frequency := osh_rec.frequency;
5003 l_hdr_tbl_in(1).fulfillment_channel := osh_rec.fulfillment_channel;
5004 l_hdr_tbl_in(1).comments := osh_rec.comments;
5005 l_hdr_tbl_in(1).item_type := osh_rec.item_type;
5006 l_status := osh_rec.status;
5007 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5008 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.OM_subscription_schedule.template_details',
5009 'Name = '||osh_rec.name||', Description = '||osh_rec.description
5010 ||', Subs. Type = '||osh_rec.subscription_type||', Media Type = '||osh_rec.media_type
5011 ||', Frequency = '||osh_rec.frequency||', Fulfill. Channel = '||osh_rec.fulfillment_channel
5012 ||', Item Type = '||osh_rec.item_type||', Status = '||osh_rec.status
5013 ||', Comments = '||osh_rec.comments);
5014 END IF;
5015 Exit;
5016 End Loop;
5017 if NVL(l_status, 'A') <> 'A' then
5018 x_return_status := OKC_API.G_RET_STS_ERROR;
5019 OKC_API.set_message
5020 ( p_app_name => 'OKS',
5021 p_msg_name => 'OKS_SUB_INACT_TMPL',
5022 p_token1 => 'TMPL',
5023 p_token1_value => l_hdr_tbl_in(1).name
5024 );
5025 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5026 fnd_log.string(FND_LOG.LEVEL_ERROR,G_MODULE_CURRENT||'.OM_subscription_schedule.ERROR','Inactive Template');
5027 END IF;
5028 Raise gen_exit;
5029 end if;
5030 -- Create Item Instance in the Installed Base --
5031 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5032 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.OM_subscription_schedule.external_call.before',
5033 'oks_auth_util_pub.create_cii_for_subscription(p_cle_id = '||p_cle_id||')');
5034 END IF;
5035
5036 OKS_AUTH_UTIL_PUB.CREATE_CII_FOR_SUBSCRIPTION
5037 (
5038 p_api_version => p_api_version,
5039 p_init_msg_list => p_init_msg_list,
5040 x_return_status => x_return_status,
5041 x_msg_count => x_msg_count,
5042 x_msg_data => x_msg_data,
5043 p_cle_id => p_cle_id,
5044 p_quantity => l_qty,
5045 x_instance_id => l_instance_id
5046 );
5047 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5048 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.OM_subscription_schedule.external_call.after',
5049 'oks_auth_util_pub.create_cii_for_subscription(x_return_status = '||x_return_status
5050 ||', x_instance_id = '||l_instance_id||')');
5051 END IF;
5052 If NVL(x_return_status,'!') <> OKC_API.G_RET_STS_SUCCESS Then
5053 Raise gen_exit;
5054 End If;
5055 l_hdr_tbl_in(1).instance_id := l_instance_id;
5056
5057 Else -- Tangible Non-Subscription Item
5058 -- Create dummy subscription header for Tangible Non-Subscription Items
5059 l_hdr_tbl_in(1).name := 'Non-Sub Item'; -- Dummy Hard Coded Value, change this
5060 l_hdr_tbl_in(1).description := 'Non-Subscription Item (Tangible)'; -- Dummy Hard Coded Value, change this
5061 l_hdr_tbl_in(1).cle_id := p_cle_id;
5062 l_hdr_tbl_in(1).dnz_chr_id := g_chr_id;
5063 l_hdr_tbl_in(1).subscription_type := 'JRNL'; -- Dummy Hard Coded Value, make this a nullable column
5064 l_hdr_tbl_in(1).frequency := 'D';
5065 l_hdr_tbl_in(1).fulfillment_channel := 'OM';
5066 l_hdr_tbl_in(1).item_type := 'NT';
5067 End If;
5068 l_hdr_tbl_in(1).status := 'A';
5069 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5070 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.OM_subscription_schedule.external_call.before',
5071 'oks_subscr_hdr_pub.insert_row');
5072 END IF;
5073 OKS_SUBSCR_HDR_PUB.insert_row
5074 (
5075 p_api_version => p_api_version,
5076 p_init_msg_list => p_init_msg_list,
5077 x_return_status => x_return_status,
5078 x_msg_count => x_msg_count,
5079 x_msg_data => x_msg_data,
5080 p_schv_tbl => l_hdr_tbl_in,
5081 x_schv_tbl => l_hdr_tbl_out
5082 );
5083 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5084 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.OM_subscription_schedule.external_call.after',
5085 'oks_subscr_hdr_pub.insert_row(x_return_status = '||x_return_status||')');
5086 END IF;
5087 If NVL(x_return_status,'!') <> OKC_API.G_RET_STS_SUCCESS Then
5088 Raise gen_exit;
5089 End If;
5090
5091 -- FOR TANGIBLE ITEMS(SUBSCRIPTION OR NON-SUB), CREATE DEFAULT PATTERN --
5092 If l_hdr_tbl_out(1).fulfillment_channel <> 'NONE' then
5093 -- CREATE DEFAULT SCHEDULE PATTERN --
5094 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5095 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.OM_subscription_schedule.tangible','it is a tangible item');
5096 END IF;
5097 l_ptrns_tbl_in(1).osh_id := l_hdr_tbl_out(1).id;
5098 l_ptrns_tbl_in(1).dnz_chr_id := g_chr_id;
5099 l_ptrns_tbl_in(1).dnz_cle_id := p_cle_id;
5100 l_ptrns_tbl_in(1).seq_no := 1;
5101 if l_itype = 'SUBSCRIPTION' then
5102 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5103 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.OM_subscription_schedule.tansub',
5104 'it is a tangible subscription item. creating default pattern');
5105 END IF;
5106 l_ptrns_tbl_in(1).year := '*';
5107 if l_hdr_tbl_out(1).frequency = 'M' then
5108 l_ptrns_tbl_in(1).month := '*';
5109 elsif l_hdr_tbl_out(1).frequency = 'W' then
5110 l_ptrns_tbl_in(1).month := '*';
5111 l_ptrns_tbl_in(1).week := '*';
5112 elsif l_hdr_tbl_out(1).frequency = 'D' then
5113 l_ptrns_tbl_in(1).month := '*';
5114 l_ptrns_tbl_in(1).day := '*';
5115 end if;
5116 else -- non-subscription item, it will be shippable
5117 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5118 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.OM_subscription_schedule.tannonsub',
5119 'it is a tangible non-subscription item. creating one-time schedule pattern');
5120 END IF;
5121 -- CREATE PATTERN FOR ONE-TIME SCHEDULE IF SHIPPABLE NON-SUBSCRIPTION ITEM
5122 l_ptrns_tbl_in(1).year := to_char(l_start_date,'YYYY');
5123 l_ptrns_tbl_in(1).month := to_char(l_start_date,'MM');
5124 l_ptrns_tbl_in(1).day := to_char(l_start_date,'DD');
5125 end if;
5126 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5127 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.OM_subscription_schedule.external_call.before',
5128 'oks_subscr_ptrns_pub.insert_row');
5129 END IF;
5130 OKS_SUBSCR_PTRNS_PUB.insert_row
5131 (
5132 p_api_version => p_api_version,
5133 p_init_msg_list => p_init_msg_list,
5134 x_return_status => x_return_status,
5135 x_msg_count => x_msg_count,
5136 x_msg_data => x_msg_data,
5137 p_scpv_tbl => l_ptrns_tbl_in,
5138 x_scpv_tbl => l_ptrns_tbl_out
5139 );
5140 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5141 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.OM_subscription_scheduleexternal_call.after',
5142 'oks_subscr_ptrns_pub.insert_row(x_return_status = '||x_return_status||')');
5143 END IF;
5144 if NVL(x_return_status,'!') <> OKC_API.G_RET_STS_SUCCESS then
5145 Raise gen_exit;
5146 end if;
5147
5148 -- CALCULATE DEFAULT DELIVERY SCHEDULE --
5149 l_pattern_tbl(1).yr_pattern := l_ptrns_tbl_out(1).year;
5150 l_pattern_tbl(1).mth_pattern := l_ptrns_tbl_out(1).month;
5151 l_pattern_tbl(1).week_pattern := l_ptrns_tbl_out(1).week;
5152 l_pattern_tbl(1).wday_pattern := l_ptrns_tbl_out(1).week_day;
5153 l_pattern_tbl(1).day_pattern := l_ptrns_tbl_out(1).day;
5154 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5155 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.OM_subscription_scheduleexternal_call.before',
5156 'oks_subscription_sch_pvt.calc_delivery_date');
5157 END IF;
5158 OKS_SUBSCRIPTION_SCH_PVT.calc_delivery_date
5159 (
5160 p_start_dt => l_start_date,
5161 p_end_dt => l_end_date,
5162 p_offset_dy => NULL,
5163 p_freq => l_hdr_tbl_out(1).frequency,
5164 p_pattern_tbl => l_pattern_tbl,
5165 x_delivery_tbl => l_delivery_tbl,
5166 x_return_status => x_return_status
5167 );
5168 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5169 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.OM_subscription_schedule.external_call.after',
5170 'oks_subscription_sch_pvt.calc_delivery_date(x_return_status = '||x_return_status||')');
5171 END IF;
5172 if NVL(x_return_status,'!') <> OKC_API.G_RET_STS_SUCCESS then
5173 Raise gen_exit;
5174 end if;
5175
5176 -- CREATE SCHEDULE ELEMENTS --
5177 if l_delivery_tbl.COUNT > 0 then
5178 idx := l_delivery_tbl.FIRST;
5179 i := 1;
5180 LOOP
5181 l_elems_tbl_in(i).osh_id := l_hdr_tbl_out(1).id;
5182 l_elems_tbl_in(i).dnz_chr_id := g_chr_id;
5183 l_elems_tbl_in(i).dnz_cle_id := p_cle_id;
5184 l_elems_tbl_in(i).seq_no := 1;
5185 l_elems_tbl_in(i).om_interface_date := l_delivery_tbl(idx).delivery_date;
5186 l_elems_tbl_in(i).start_date := l_delivery_tbl(idx).start_date;
5187 l_elems_tbl_in(i).end_date := l_delivery_tbl(idx).end_date;
5188 l_elems_tbl_in(i).quantity := l_qty;
5189 l_elems_tbl_in(i).uom_code := l_uom;
5190 Exit When idx = l_delivery_tbl.LAST;
5191 idx := l_delivery_tbl.NEXT(idx);
5192 i := i + 1;
5193 END LOOP;
5194 tot_qty := i * l_qty;
5195 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5196 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.OM_subscription_schedule.external_call.before',
5197 'oks_subscr_elems_pub.insert_row');
5198 END IF;
5199 OKS_SUBSCR_ELEMS_PUB.insert_row
5200 (
5201 p_api_version => p_api_version,
5202 p_init_msg_list => p_init_msg_list,
5203 x_return_status => x_return_status,
5204 x_msg_count => x_msg_count,
5205 x_msg_data => x_msg_data,
5206 p_scev_tbl => l_elems_tbl_in,
5207 x_scev_tbl => l_elems_tbl_out
5208 );
5209 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5210 fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.OM_subscription_schedule.external_call.after',
5211 'oks_subscr_elems_pub.insert_row(x_return_status = '||x_return_status||')');
5212 END IF;
5213 if NVL(x_return_status,'!') <> OKC_API.G_RET_STS_SUCCESS then
5214 Raise gen_exit;
5215 end if;
5216 else
5217 Null; -- No Schedule for this line. Handle exception here if needed
5218 end if; -- l_delivery_tbl.COUNT
5219 Else
5220 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5221 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.OM_subscription_schedule.intansub',
5222 'it is an intangible subscription item. only header is created');
5223 END IF;
5224 End If;
5225 ELSE
5226 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5227 fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.OM_subscription_schedule.intannonsub',
5228 'it is an intangible non-subscription item. nothing created (only pricing will be called)');
5229 END IF;
5230 END IF;
5231 IF NVL(x_return_status,'!') <> OKC_API.G_RET_STS_SUCCESS THEN
5232 Raise gen_exit;
5233 END IF;
5234 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5235 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,G_MODULE_CURRENT||'.OM_subscription_schedule.end',' ');
5236 END IF;
5237 Exception
5238 When gen_exit Then
5239 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5240 fnd_log.string(FND_LOG.LEVEL_EXCEPTION,G_MODULE_CURRENT||'.OM_subscription_schedule.EXCEPTION','gen_exit');
5241 END IF;
5242 When OTHERS Then
5243 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5244 fnd_log.string(FND_LOG.LEVEL_UNEXPECTED,G_MODULE_CURRENT||'.OM_subscription_schedule.UNEXPECTED',
5245 'sqlcode = '||sqlcode||', sqlerrm = '||sqlerrm);
5246 END IF;
5247 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
5248 OKC_API.set_message
5249 ( p_app_name => 'OKS',
5250 p_msg_name => 'OKC_CONTRACTS_UNEXP_ERROR',
5251 p_token1 => 'ERROR_CODE',
5252 p_token1_value => sqlcode,
5253 p_token2 => 'ERROR_MESSAGE',
5254 p_token2_value => sqlerrm
5255 );
5256 End OM_subscription_schedule;
5257 /*Added for 16039680*/
5258 END oks_ocint_pub;