[Home] [Help]
PACKAGE BODY: APPS.OKS_OCINT_PUB
Source
1 PACKAGE BODY oks_ocint_pub AS
2 /* $Header: OKSPOCIB.pls 120.27.12010000.2 2008/10/22 12:50:45 ssreekum 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 --General
748 l_index NUMBER;
749 l_process BOOLEAN;
750 l_order_rec l_order_csr%ROWTYPE;
751 l_ref_order_rec l_order_csr%ROWTYPE;
752 l_cp_tbl cp_tbl_type;
753 l_cp_ctr NUMBER := 0;
754 l_hdr_rec oks_extwarprgm_pvt.k_header_rec_type;
755 l_line_rec oks_extwarprgm_pvt.k_line_service_rec_type;
756 l_covd_rec oks_extwarprgm_pvt.k_line_covered_level_rec_type;
757 l_chrid NUMBER := NULL;
758 l_lineid NUMBER := NULL;
759 l_rnrl_rec_out oks_renew_util_pvt.rnrl_rec_type;
760 l_renewal_rec oks_extwar_util_pvt.renewal_rec_type;
761 l_contact_tbl oks_extwarprgm_pvt.contact_tbl;
762 l_ptr1 NUMBER := 0;
763 l_covlvl_id NUMBER;
764 l_rule_id NUMBER;
765 l_rule_group_id NUMBER;
766 l_api_version CONSTANT NUMBER := 1.0;
767 l_update_line VARCHAR2 (1);
768 l_duration NUMBER;
769 l_timeunits VARCHAR2 (240);
770 l_sll_tbl oks_bill_sch.streamlvl_tbl;
771 l_bil_sch_out oks_bill_sch.itembillsch_tbl;
772 x_msg_count NUMBER;
773 x_msg_data VARCHAR2 (2000);
774 x_return_status VARCHAR2 (1) := okc_api.g_ret_sts_success;
775 l_repv_tbl oks_rep_pvt.repv_tbl_type;
776 l_reproc_line_rec oks_rep_pvt.repv_rec_type;
777 l_serv_ref_rec l_serv_ref_csr%ROWTYPE;
778 l_upd_tbl oks_rep_pvt.repv_tbl_type;
779 l_out_repv_rec oks_rep_pvt.repv_rec_type;
780 l_conc_rec oks_rep_pvt.repv_rec_type;
781 i NUMBER;
782 l_ctr1 NUMBER;
783 l_cont_line_id NUMBER;
784 l_dummy VARCHAR2 (30);
785 l_error_temp VARCHAR2 (2000);
786 l_error_msg VARCHAR2 (2000);
787
788 l_process_status VARCHAR2(20);
789
790 PROCEDURE create_contract (
791 p_reproc_line_rec oks_rep_pvt.repv_rec_type,
792 x_upd_tbl OUT NOCOPY oks_rep_pvt.repv_tbl_type,
793 x_return_status OUT NOCOPY VARCHAR2
794 )
795 IS
796 CURSOR l_party_csr
797 IS
798 SELECT NAME
799 FROM okx_parties_v
800 WHERE id1 = l_k_header_rec.party_id;
801
802 -- cursor to get ship and installation dates
803 -- vigandhi 04-jun-2002
804 CURSOR l_get_dates_csr (p_cp_id NUMBER)
805 IS
806 SELECT csi.install_date, ol.actual_shipment_date,
807 mtl.service_starting_delay
808 FROM csi_item_instances csi,
809 oe_order_lines_all ol,
810 okx_system_items_v mtl
811 WHERE csi.instance_id = p_cp_id
812 AND csi.last_oe_order_line_id = ol.line_id
813 AND csi.inventory_item_id = mtl.id1
814 AND ROWNUM < 2;
815
816 CURSOR l_hdr_scs_csr (p_chr_id NUMBER)
817 IS
818 SELECT scs_code
819 FROM okc_k_headers_v
820 WHERE ID = p_chr_id;
821
822 CURSOR l_inv_csr (p_ordline_id NUMBER)
823 IS
824 SELECT inventory_item_id
825 FROM oe_order_lines --mmadhavi for MOAC
826 WHERE line_id = p_ordline_id;
827
828 CURSOR l_lndates_csr (p_id NUMBER)
829 IS
830 SELECT start_date, end_date
831 FROM okc_k_lines_b
832 WHERE ID = p_id;
833
834 CURSOR l_hdrdates_csr (p_id NUMBER)
835 IS
836 SELECT start_date, end_date, sts_code
837 FROM okc_k_headers_b
838 WHERE ID = p_id;
839
840 CURSOR l_refnum_csr (p_cp_id NUMBER)
841 IS
842 SELECT instance_number
843 FROM csi_item_instances
844 WHERE instance_id = p_cp_id;
845
846
847 -- Cursor to roll up the tax amount from sublines to the topline
848
849 CURSOR c_extwar_line_amount(p_chr_id IN NUMBER, p_line_id IN NUMBER) IS
850 SELECT
851 SUM(NVL(slines.tax_amount,0)) tax_amount
852 FROM
853 okc_k_lines_b clines
854 ,oks_k_lines_b slines
855 WHERE
856 clines.dnz_chr_id = p_chr_id
857 and clines.cle_id = p_line_id
858 and clines.id = slines.cle_id;
859
860 -- Cursor to rollup the tax amount from toplines to header
861
862 CURSOR c_extwar_hdr_amount(p_chr_id IN NUMBER) IS
863 SELECT
864 SUM(NVL(slines.tax_amount,0)) tax_amount
865 FROM
866 okc_k_lines_b clines
867 ,oks_k_lines_b slines
868 WHERE
869 clines.dnz_chr_id = p_chr_id
870 AND clines.lse_id IN (1, 19)
871 AND clines.id = slines.cle_id;
872
873 l_hdr_scs_code VARCHAR2 (30);
874 l_party_name okx_parties_v.NAME%TYPE;
875 l_get_dates_rec l_get_dates_csr%ROWTYPE;
876 l_sts_code VARCHAR2 (30);
877 l_ste_code VARCHAR2 (30);
878 l_ship_date DATE;
879 l_installation_date DATE;
880 l_strmlvl_id NUMBER;
881 l_cp_inventory NUMBER;
882 l_inv_item_id NUMBER;
883 l_serv_ref_rec l_serv_ref_csr%ROWTYPE;
884 l_order_error VARCHAR2 (2000);
885 l_lndates_rec l_lndates_csr%ROWTYPE;
886 l_hdrdates_rec l_hdrdates_csr%ROWTYPE;
887 l_ref_num VARCHAR2 (30);
888 l_inst_dtls_rec oks_ihd_pvt.ihdv_rec_type;
889 l_insthist_rec oks_ins_pvt.insv_rec_type;
890 x_inst_dtls_rec oks_ihd_pvt.ihdv_rec_type;
891 x_insthist_rec oks_ins_pvt.insv_rec_type;
892 l_error VARCHAR2 (1) := 'N';
893 l_header_id NUMBER;
894 l_period_start oks_k_headers_v.period_start%TYPE;
895 l_period_type oks_k_headers_v.period_type%TYPE;
896 l_price_uom oks_k_headers_v.price_uom%TYPE;
897 l_line_tax_amount NUMBER;
898 l_header_tax_amount NUMBER;
899
900 -- Added fro fix of bug# 5165947
901
902 l_BOM_instance_id NUMBER;
903 l_BOM_instance_flag VARCHAR2(10);
904 l_eff_line_upd_flag VARCHAR2(10);
905
906 l_prev_line_amt NUMBER;
907 l_curr_line_amt NUMBER;
908
909 cursor c_line_ammt_ckeck(c_line_id in number)
910 is select nvl(price_negotiated,0) from okc_k_lines_b where id = c_line_id;
911
912 -- Added fro fix of bug# 5165947
913
914 BEGIN
915 x_upd_tbl.DELETE;
916
917 OPEN l_get_dates_csr (l_cp_tbl (1).cp_id);
918
919 FETCH l_get_dates_csr
920 INTO l_get_dates_rec;
921
922 CLOSE l_get_dates_csr;
923 /* Fix for 6389290 */
924 x_upd_tbl (1).order_line_id := p_reproc_line_rec.order_line_id;
925 /* Fix Ends */
926 l_ship_date :=
927 TRUNC (l_get_dates_rec.actual_shipment_date)
928 + NVL (l_get_dates_rec.service_starting_delay, 0);
929 l_installation_date := TRUNC (l_get_dates_rec.install_date);
930 oks_extwar_util_pvt.get_contract_header_info
931 (p_order_line_id => p_reproc_line_rec.order_line_id,
932 p_cp_id => NULL,
933 p_caller => 'OC',
934 x_order_error => l_order_error,
935 x_return_status => l_return_status,
936 x_header_rec => l_k_header_rec
937 );
938 fnd_file.put_line
939 (fnd_file.LOG,
940 'OC INTERFACE :- get_contract_header_info status '
941 || l_return_status
942 );
943
944 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
945 THEN
946 IF l_order_error IS NULL
947 THEN
948 l_order_error := '#';
949
950 FOR i IN 1 .. fnd_msg_pub.count_msg
951 LOOP
952 fnd_msg_pub.get (p_msg_index => i,
953 p_encoded => 'T',
954 p_data => l_msg_data,
955 p_msg_index_out => l_index
956 );
957 l_order_error := l_order_error || l_msg_data || '#';
958 fnd_message.set_encoded (l_msg_data);
959 l_msg_data := fnd_message.get;
960 fnd_file.put_line (fnd_file.LOG,
961 'GET CONTRACT HDR FAILURE ' || l_msg_data
962 );
963 END LOOP;
964 END IF;
965
966 x_upd_tbl (1).ERROR_TEXT := l_order_error;
967 x_upd_tbl (1).success_flag := 'E';
968 x_upd_tbl (1).order_line_id := p_reproc_line_rec.order_line_id;
969 x_upd_tbl (1).ID := p_reproc_line_rec.ID;
970 RAISE g_exception_halt_validation;
971 END IF;
972
973 oks_extwar_util_pvt.get_k_service_line
974 (p_order_line_id => p_reproc_line_rec.order_line_id,
975 p_cp_id => l_cp_tbl (1).cp_id,
976 p_shipped_date => l_ship_date,
977 p_installation_date => l_installation_date,
978 p_caller => 'OC',
979 x_order_error => l_order_error,
980 x_return_status => l_return_status,
981 x_line_rec => l_k_line_rec
982 );
983 fnd_file.put_line (fnd_file.LOG,
984 'OC INTERFACE :- get_k_service_line status '
985 || l_return_status
986 );
987
988 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
989 THEN
990 IF l_order_error IS NULL
991 THEN
992 l_order_error := '#';
993
994 FOR i IN 1 .. fnd_msg_pub.count_msg
995 LOOP
996 fnd_msg_pub.get (p_msg_index => i,
997 p_encoded => 'T',
998 p_data => l_msg_data,
999 p_msg_index_out => l_index
1000 );
1001 l_order_error := l_order_error || l_msg_data || '#';
1002 fnd_message.set_encoded (l_msg_data);
1003 l_msg_data := fnd_message.get;
1004 fnd_file.put_line (fnd_file.LOG,
1005 'GET ORDER LINE FAILURE ' || l_msg_data
1006 );
1007 END LOOP;
1008 END IF;
1009
1010 x_upd_tbl (1).ERROR_TEXT := l_order_error;
1011 x_upd_tbl (1).success_flag := 'E';
1012 x_upd_tbl (1).order_line_id := p_reproc_line_rec.order_line_id;
1013 x_upd_tbl (1).ID := p_reproc_line_rec.ID;
1014 RAISE g_exception_halt_validation;
1015 END IF;
1016
1017 oks_renew_util_pub.get_renew_rules
1018 (p_api_version => 1.0,
1019 p_init_msg_list => 'T',
1020 x_return_status => l_return_status,
1021 x_msg_count => l_msg_count,
1022 x_msg_data => l_msg_data,
1023 p_chr_id => NULL,
1024 p_party_id => l_k_header_rec.party_id,
1025 p_org_id => l_k_header_rec.authoring_org_id,
1026 p_date => SYSDATE,
1027 p_rnrl_rec => NULL,
1028 x_rnrl_rec => l_rnrl_rec_out
1029 );
1030 fnd_file.put_line (fnd_file.LOG,
1031 'OC INTERFACE :- get_renew_rules status '
1032 || l_return_status
1033 );
1034
1035 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
1036 THEN
1037 FOR i IN 1 .. fnd_msg_pub.count_msg
1038 LOOP
1039 fnd_msg_pub.get (p_msg_index => -1,
1040 p_encoded => 'F',
1041 p_data => l_msg_data,
1042 p_msg_index_out => l_index
1043 );
1044 fnd_file.put_line (fnd_file.LOG,
1045 'RENEWAL RULE FAILURE ' || l_msg_data
1046 );
1047 END LOOP;
1048
1049 RAISE g_exception_halt_validation;
1050 END IF;
1051
1052 oks_integration_util_pub.create_k_order_details
1053 (p_header_id => l_k_header_rec.order_hdr_id,
1054 x_return_status => l_return_status,
1055 x_msg_count => l_msg_count,
1056 x_msg_data => l_msg_data
1057 );
1058 fnd_file.put_line (fnd_file.LOG,
1059 'OC INTERFACE :- get_K_order_details status '
1060 || l_return_status
1061 );
1062
1063 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
1064 THEN
1065 FOR i IN 1 .. fnd_msg_pub.count_msg
1066 LOOP
1067 fnd_msg_pub.get (p_msg_index => -1,
1068 p_encoded => 'F',
1069 p_data => l_msg_data,
1070 p_msg_index_out => l_index
1071 );
1072 fnd_file.put_line (fnd_file.LOG,
1073 'ORDER DETAIL FAILURE ' || l_msg_data
1074 );
1075 END LOOP;
1076
1077 RAISE g_exception_halt_validation;
1078 END IF;
1079
1080 oks_extwar_util_pvt.get_k_order_details
1081 (p_reproc_line_rec.order_line_id,
1082 l_renewal_rec
1083 );
1084 --mmadhavi added following logic for Partial Periods Computation
1085 oks_renew_util_pub.get_period_defaults
1086 (p_hdr_id => NULL,
1087 p_org_id => l_k_header_rec.authoring_org_id,
1088 x_period_type => l_period_type,
1089 x_period_start => l_period_start,
1090 x_price_uom => l_price_uom,
1091 x_return_status => l_return_status
1092 );
1093
1094 --25-JAN-2006 mchoudha for partial periods CR-003
1095 --All the extended warranty contracts created from order management will have Service
1096 --stamped on them if GCD is setup. Period Type will be pulled from GCD.
1097 IF l_period_start IS NOT NULL AND l_period_type IS NOT NULL
1098 THEN
1099 l_period_start := 'SERVICE';
1100 --22-MAR-2006 mchoudha Changes for partial periods CR3
1101 --Period type will be picked up from GCD and not hard coded
1102 --l_period_type := 'FIXED';
1103 END IF;
1104
1105 fnd_file.put_line (fnd_file.LOG,
1106 'OC INTERFACE :- get_period_defaults '
1107 || l_return_status
1108 );
1109 fnd_file.put_line
1110 (fnd_file.LOG,
1111 'OC INTERFACE :- get_period_defaults l_period_start '
1112 || l_period_start
1113 );
1114 fnd_file.put_line
1115 (fnd_file.LOG,
1116 'OC INTERFACE :- get_period_defaults l_period_type '
1117 || l_period_type
1118 );
1119
1120 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
1121 THEN
1122 FOR i IN 1 .. fnd_msg_pub.count_msg
1123 LOOP
1124 fnd_msg_pub.get (p_msg_index => -1,
1125 p_encoded => 'F',
1126 p_data => l_msg_data,
1127 p_msg_index_out => l_index
1128 );
1129 fnd_file.put_line (fnd_file.LOG,
1130 'get_period_defaults FAILURE ' || l_msg_data
1131 );
1132 END LOOP;
1133
1134 RAISE g_exception_halt_validation;
1135 END IF;
1136
1137 --mmadhavi end Partial Periods logic
1138 IF l_renewal_rec.chr_id IS NOT NULL
1139 OR l_renewal_rec.link_chr_id IS NOT NULL
1140 THEN
1141 l_hdr_rec.merge_type := 'LTC';
1142 l_hdr_rec.merge_object_id :=
1143 NVL (l_renewal_rec.chr_id, l_renewal_rec.link_chr_id);
1144 ELSE
1145 l_hdr_rec.merge_type := 'NEW';
1146 l_hdr_rec.merge_object_id := NULL;
1147 END IF;
1148
1149 fnd_file.put_line (fnd_file.LOG,
1150 'OC INTERFACE :- Create K Hdr status '
1151 || l_return_status
1152 );
1153
1154 OPEN l_party_csr;
1155
1156 FETCH l_party_csr
1157 INTO l_party_name;
1158
1159 CLOSE l_party_csr;
1160
1161 fnd_file.put_line (fnd_file.LOG,
1162 'OC INTERFACE :- party name ' || l_party_name
1163 );
1164
1165 IF fnd_profile.VALUE ('OKS_CONTRACT_CATEGORY') = 'SERVICE'
1166 THEN
1167 l_line_rec.warranty_flag := 'S';
1168 l_covd_rec.warranty_flag := 'S';
1169 l_hdr_rec.scs_code := 'SERVICE';
1170 l_hdr_rec.short_description :=
1171 'CUSTOMER: ' || l_party_name || ' Contract';
1172 ELSIF fnd_profile.VALUE ('OKS_CONTRACT_CATEGORY') = 'SUBSCRIPTION'
1173 THEN
1174 l_line_rec.warranty_flag := 'SU';
1175 l_hdr_rec.scs_code := 'SUBSCRIPTION';
1176 l_covd_rec.warranty_flag := 'SU';
1177 l_hdr_rec.short_description :=
1178 'CUSTOMER: ' || l_party_name || ' Contract';
1179 ELSIF fnd_profile.VALUE ('OKS_CONTRACT_CATEGORY') = 'WARRANTY'
1180 OR fnd_profile.VALUE ('OKS_CONTRACT_CATEGORY') IS NULL
1181 THEN
1182 l_line_rec.warranty_flag := 'E';
1183 l_hdr_rec.scs_code := 'WARRANTY';
1184 l_covd_rec.warranty_flag := 'E';
1185 l_hdr_rec.short_description :=
1186 'CUSTOMER: '
1187 || l_party_name
1188 || ' Warranty/Extended Warranty Contract';
1189 END IF;
1190
1191 --changing l_extwar_rec.merge...to l_hdr_rec
1192 IF l_hdr_rec.merge_object_id IS NOT NULL
1193 THEN
1194 OPEN l_hdr_scs_csr (l_hdr_rec.merge_object_id);
1195
1196 FETCH l_hdr_scs_csr
1197 INTO l_hdr_scs_code;
1198
1199 CLOSE l_hdr_scs_csr;
1200
1201 IF l_hdr_scs_code <> fnd_profile.VALUE ('OKS_CONTRACT_CATEGORY')
1202 THEN
1203 l_return_status := okc_api.g_ret_sts_error;
1204 --OKC_API.set_message(G_APP_NAME,'OKS_CONTRACT_CATEGORY','Cat',l_hdr_scs_code);
1205 fnd_message.set_name ('OKS', 'OKS_CONTRACT_CATEGORY');
1206 fnd_message.set_token (token => 'Cat',
1207 VALUE => l_hdr_scs_code);
1208 l_order_error := '#' || fnd_message.get_encoded || '#';
1209 x_upd_tbl (1).ERROR_TEXT := l_order_error;
1210 x_upd_tbl (1).success_flag := 'E';
1211 x_upd_tbl (1).order_line_id := p_reproc_line_rec.order_line_id;
1212 x_upd_tbl (1).ID := p_reproc_line_rec.ID;
1213 RAISE g_exception_halt_validation;
1214 END IF;
1215 END IF;
1216
1217 l_contact_tbl.DELETE;
1218
1219 IF l_k_header_rec.invoice_to_contact_id IS NOT NULL
1220 THEN
1221 l_contact_tbl (1).party_role := 'CUSTOMER';
1222 l_contact_tbl (1).contact_role := 'BILLING';
1223 l_contact_tbl (1).contact_object_code := 'OKX_PCONTACT';
1224 l_contact_tbl (1).contact_id :=
1225 l_k_header_rec.invoice_to_contact_id;
1226 l_contact_tbl (1).flag := 'H';
1227 l_ptr1 := 2;
1228 ELSE
1229 l_ptr1 := 1;
1230 END IF;
1231
1232 FOR l_contact_rec IN l_contact_csr (p_reproc_line_rec.order_line_id)
1233 LOOP
1234 l_contact_tbl (l_ptr1).party_role := 'CUSTOMER';
1235 l_contact_tbl (l_ptr1).contact_role := l_contact_rec.cro_code;
1236 l_contact_tbl (l_ptr1).contact_object_code := 'OKX_PCONTACT';
1237 l_contact_tbl (l_ptr1).contact_id := l_contact_rec.object1_id1;
1238 l_contact_tbl (l_ptr1).flag := 'K'; -- changed 17-jul-2003
1239 l_ptr1 := l_ptr1 + 1;
1240 END LOOP;
1241
1242 --OKC_CONTEXT.SET_OKC_ORG_CONTEXT ( p_org_id => l_k_header_rec.authoring_org_id,
1243 -- p_organization_id => Null
1244 -- );
1245
1246 ---mmadhavi sales credit bug 4174921
1247 l_salescredit_tbl_in.DELETE;
1248 l_header_id := p_reproc_line_rec.order_id;
1249 oks_extwar_util_pvt.salescredit_header
1250 (p_order_hdr_id => p_reproc_line_rec.order_id,
1251 x_salescredit_tbl => l_salescredit_tbl_in,
1252 x_return_status => l_return_status
1253 );
1254 fnd_file.put_line (fnd_file.LOG,
1255 'OC INTERFACE :- SalesCredit at Header '
1256 || l_return_status
1257 );
1258
1259 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
1260 THEN
1261 FOR i IN 1 .. fnd_msg_pub.count_msg
1262 LOOP
1263 fnd_msg_pub.get (p_msg_index => -1,
1264 p_encoded => 'F',
1265 p_data => l_msg_data,
1266 p_msg_index_out => l_index
1267 );
1268 fnd_file.put_line (fnd_file.LOG,
1269 'READ SALES CREDIT ERROR IN HEADER'
1270 || l_msg_data
1271 );
1272 END LOOP;
1273
1274 RAISE g_exception_halt_validation;
1275 END IF;
1276
1277 --mmadhavi bug 4069827
1278
1279 -- rty_code has been added, (Extwarranty consolidation) vigandhi
1280 l_hdr_rec.contract_number := okc_api.g_miss_char;
1281 l_hdr_rec.rty_code := 'CONTRACTSERVICESORDER';
1282 l_hdr_rec.start_date := l_k_line_rec.srv_sdt;
1283 l_hdr_rec.end_date := l_k_line_rec.srv_edt;
1284 -- l_hdr_rec.sts_code := 'ACTIVE';
1285 l_hdr_rec.class_code := 'SVC';
1286 l_hdr_rec.authoring_org_id := l_k_header_rec.authoring_org_id;
1287 --l_hdr_rec.org_id := l_k_header_rec.org_id; --MMadhavi MOAC : need to add in rec type also
1288 l_hdr_rec.party_id := l_k_header_rec.party_id;
1289 l_hdr_rec.third_party_role := l_rnrl_rec_out.rle_code;
1290 l_hdr_rec.bill_to_id := l_k_header_rec.bill_to_id;
1291 l_hdr_rec.ship_to_id := l_k_header_rec.ship_to_id;
1292 l_hdr_rec.chr_group := l_rnrl_rec_out.cgp_new_id;
1293 l_hdr_rec.cust_po_number := l_k_header_rec.cust_po_number;
1294 l_hdr_rec.agreement_id := l_k_header_rec.agreement_id;
1295 --party name to be done
1296 l_hdr_rec.currency := l_k_header_rec.currency;
1297 l_hdr_rec.accounting_rule_id :=
1298 NVL (l_k_header_rec.accounting_rule_id, 1);
1299 l_hdr_rec.invoice_rule_id := NVL (l_k_header_rec.invoice_rule_id, -2);
1300 l_hdr_rec.order_hdr_id := l_k_header_rec.order_hdr_id;
1301 l_hdr_rec.price_list_id := l_k_header_rec.price_list_id;
1302 l_hdr_rec.payment_term_id := l_k_header_rec.hdr_payment_term_id;
1303 l_hdr_rec.renewal_type := l_renewal_rec.renewal_type;
1304 l_hdr_rec.RENEWAL_APPROVAL_FLAG := l_renewal_rec.RENEWAL_APPROVAL_FLAG; --Bug# 5173373
1305 l_hdr_rec.renewal_markup := l_renewal_rec.markup_percent;
1306 l_hdr_rec.renewal_pricing_type := l_renewal_rec.renewal_pricing_type;
1307 l_hdr_rec.renewal_price_list_id := l_renewal_rec.price_list_id1;
1308 l_hdr_rec.cvn_type := l_k_header_rec.hdr_cvn_type;
1309 l_hdr_rec.cvn_rate := l_k_header_rec.hdr_cvn_rate;
1310 l_hdr_rec.cvn_date := l_k_header_rec.hdr_cvn_date;
1311 l_hdr_rec.cvn_euro_rate := NULL;
1312 l_hdr_rec.tax_status_flag := l_k_header_rec.hdr_tax_status_flag;
1313 l_hdr_rec.tax_exemption_id := l_k_header_rec.hdr_tax_exemption_id;
1314 l_hdr_rec.renewal_type := l_renewal_rec.renewal_type;
1315 l_hdr_rec.RENEWAL_APPROVAL_FLAG := l_renewal_rec.RENEWAL_APPROVAL_FLAG; --Bug# 5173373
1316
1317 l_hdr_rec.renewal_pricing_type := l_renewal_rec.renewal_pricing_type;
1318 l_hdr_rec.renewal_price_list_id := l_renewal_rec.price_list_id1;
1319 l_hdr_rec.renewal_markup := l_renewal_rec.markup_percent;
1320 l_hdr_rec.renewal_po := l_renewal_rec.po_required_yn;
1321 l_hdr_rec.contact_id := l_k_header_rec.ship_to_contact_id;
1322 l_hdr_rec.qto_contact_id := l_renewal_rec.contact_id;
1323 l_hdr_rec.qto_email_id := l_renewal_rec.email_id;
1324 l_hdr_rec.qto_phone_id := l_renewal_rec.phone_id;
1325 l_hdr_rec.qto_fax_id := l_renewal_rec.fax_id;
1326 l_hdr_rec.qto_site_id := l_renewal_rec.site_id;
1327 l_hdr_rec.order_line_id := p_reproc_line_rec.order_line_id;
1328 l_hdr_rec.billing_profile_id := l_renewal_rec.billing_profile_id;
1329 --new parameter added -vigandhi (May29-02)
1330 l_hdr_rec.qcl_id := l_rnrl_rec_out.qcl_id;
1331 l_hdr_rec.salesrep_id := l_k_header_rec.salesrep_id;
1332 l_hdr_rec.pdf_id := l_rnrl_rec_out.pdf_id;
1333 l_hdr_rec.ccr_number := l_k_header_rec.ccr_number;
1334 l_hdr_rec.ccr_exp_date := l_k_header_rec.ccr_exp_date;
1335 --mmadhavi added for Partial Periods Computation
1336 l_hdr_rec.period_start := l_period_start;
1337 l_hdr_rec.period_type := l_period_type;
1338 l_hdr_rec.price_uom := l_price_uom;
1339
1340 --mmadhavi end Partial Periods Computation
1341 IF l_hdr_rec.start_date > SYSDATE
1342 THEN
1343 oks_extwarprgm_pvt.get_sts_code ('SIGNED',
1344 NULL,
1345 l_ste_code,
1346 l_sts_code
1347 );
1348 ELSE
1349 oks_extwarprgm_pvt.get_sts_code ('ACTIVE',
1350 NULL,
1351 l_ste_code,
1352 l_sts_code
1353 );
1354 END IF;
1355
1356 l_hdr_rec.sts_code := l_sts_code;
1357 -- Added by JVARGHES for 12.0 enhancements.
1358 l_hdr_rec.renewal_status := 'COMPLETE';
1359 l_hdr_rec.grace_period := l_rnrl_rec_out.grace_period; --Bug# 4549857
1360 l_hdr_rec.grace_duration := l_rnrl_rec_out.grace_duration;
1361 --Bug# 4549857
1362 --
1363
1364 -- Added as part of bug fix 5008188
1365 -- l_hdr_rec.tax_classification_code := l_k_header_rec.tax_classification_code; -- Fix for bug# 5403061
1366 l_hdr_rec.tax_classification_code := NULL; -- Fix for bug# 5403061
1367 l_hdr_rec.exemption_certificate_number := l_k_header_rec.exemption_certificate_number;
1368 l_hdr_rec.exemption_reason_code := l_k_header_rec.exemption_reason_code;
1369 --
1370
1371 /* Added by sjanakir for Bug#7445270 */
1372 l_hdr_rec.ar_interface_yn := 'Y';
1373
1374 oks_extwarprgm_pvt.create_k_hdr
1375 (p_k_header_rec => l_hdr_rec,
1376 p_contact_tbl => l_contact_tbl,
1377 p_salescredit_tbl_in => l_salescredit_tbl_in,
1378 --mmadhavi bug 4174921
1379 p_caller => 'OC',
1380 x_order_error => l_order_error,
1381 x_chr_id => l_chrid,
1382 x_return_status => l_return_status,
1383 x_msg_count => l_msg_count,
1384 x_msg_data => l_msg_data
1385 );
1386 fnd_file.put_line (fnd_file.LOG,
1387 'OC INTERFACE :- Create K Hdr ID = '
1388 || TO_CHAR (l_chrid)
1389 );
1390 fnd_file.put_line (fnd_file.LOG,
1391 'OC INTERFACE :- Create K Hdr status '
1392 || l_return_status
1393 );
1394
1395 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
1396 THEN
1397 IF l_order_error IS NULL
1398 THEN
1399 l_order_error := '#';
1400
1401 FOR i IN 1 .. fnd_msg_pub.count_msg
1402 LOOP
1403 fnd_msg_pub.get (p_msg_index => i,
1404 p_encoded => 'T',
1405 p_data => l_msg_data,
1406 p_msg_index_out => l_index
1407 );
1408 l_order_error := l_order_error || l_msg_data || '#';
1409 fnd_message.set_encoded (l_msg_data);
1410 l_msg_data := fnd_message.get;
1411 fnd_file.put_line (fnd_file.LOG,
1412 'Create_k_hdr FAILURE ' || l_msg_data
1413 );
1414 END LOOP;
1415 END IF;
1416
1417 x_upd_tbl (1).ERROR_TEXT := l_order_error;
1418 x_upd_tbl (1).success_flag := 'E';
1419 x_upd_tbl (1).order_line_id := p_reproc_line_rec.order_line_id;
1420 x_upd_tbl (1).ID := p_reproc_line_rec.ID;
1421 RAISE g_exception_halt_validation;
1422 END IF;
1423
1424 x_upd_tbl (1).contract_id := l_chrid;
1425
1426 -- Fix for Bug 2805292
1427 IF l_hdr_rec.order_hdr_id IS NOT NULL
1428 AND l_hdr_rec.merge_type = 'NEW'
1429 THEN
1430 okc_oc_int_pub.create_k_relationships
1431 (p_api_version => l_api_version,
1432 p_init_msg_list => l_init_msg_list,
1433 p_commit => okc_api.g_false,
1434 p_sales_contract_id => okc_api.g_miss_num,
1435 p_service_contract_id => l_chrid,
1436 p_quote_id => okc_api.g_miss_num,
1437 p_quote_line_tab => okc_oc_int_pub.g_miss_ql_tab,
1438 p_order_id => l_hdr_rec.order_hdr_id,
1439 p_order_line_tab => okc_oc_int_pub.g_miss_ol_tab,
1440 p_trace_mode => NULL,
1441 x_return_status => l_return_status,
1442 x_msg_count => l_msg_count,
1443 x_msg_data => l_msg_data
1444 );
1445
1446 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
1447 THEN
1448 fnd_log.STRING
1449 (fnd_log.level_event,
1450 g_module_current || '.ORDER_REPROCESS.CREATE_CONTRACT',
1451 ' okc_oc_int_pub.create_k_relationships(Return status = '
1452 || l_return_status
1453 || ')'
1454 );
1455 END IF;
1456
1457 --OKS_RENEW_PVT.DEBUG_LOG( '(OKS_EXTWARPRGM_PVT).okc_oc_int_pub.create_k_relationships '|| l_return_status);
1458 fnd_file.put_line
1459 (fnd_file.LOG,
1460 '(OKS_EXTWARPRGM_PVT).okc_oc_int_pub.create_k_relationships '
1461 || l_return_status
1462 );
1463
1464 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
1465 THEN
1466 FOR i IN 1 .. fnd_msg_pub.count_msg
1467 LOOP
1468 fnd_msg_pub.get (p_msg_index => -1,
1469 p_encoded => 'F',
1470 p_data => l_msg_data,
1471 p_msg_index_out => l_index
1472 );
1473 fnd_file.put_line (fnd_file.LOG,
1474 'K HDR CREATION ERROR ' || l_msg_data
1475 );
1476 END LOOP;
1477
1478 RAISE g_exception_halt_validation;
1479 END IF;
1480 END IF;
1481
1482 oks_extwar_util_pvt.salescredit
1483 (p_order_line_id => p_reproc_line_rec.order_line_id,
1484 x_salescredit_tbl => l_salescredit_tbl_in,
1485 x_return_status => l_return_status
1486 );
1487 fnd_file.put_line (fnd_file.LOG,
1488 'OC INTERFACE :- SalesCredit ' || l_return_status
1489 );
1490
1491 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
1492 THEN
1493 FOR i IN 1 .. fnd_msg_pub.count_msg
1494 LOOP
1495 fnd_msg_pub.get (p_msg_index => -1,
1496 p_encoded => 'F',
1497 p_data => l_msg_data,
1498 p_msg_index_out => l_index
1499 );
1500 fnd_file.put_line (fnd_file.LOG,
1501 'READ SALES CREDIT ERROR ' || l_msg_data
1502 );
1503 END LOOP;
1504
1505 RAISE g_exception_halt_validation;
1506 END IF;
1507
1508
1509 -- If the line level credit sales credit is not specified for an order line
1510 -- in OM, then default the header sales credits to the line as well
1511
1512 IF l_salescredit_tbl_in.count = 0 THEN
1513 oks_extwar_util_pvt.salescredit_header
1514 (p_order_hdr_id => p_reproc_line_rec.order_id,
1515 x_salescredit_tbl => l_salescredit_tbl_in,
1516 x_return_status => l_return_status
1517 );
1518 fnd_file.put_line (fnd_file.LOG,
1519 'OC INTERFACE :- SalesCredit at Header - defaulting for order line id = '||p_reproc_line_rec.order_id
1520 || l_return_status
1521 );
1522
1523 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
1524 THEN
1525 FOR i IN 1 .. fnd_msg_pub.count_msg
1526 LOOP
1527 fnd_msg_pub.get (p_msg_index => -1,
1528 p_encoded => 'F',
1529 p_data => l_msg_data,
1530 p_msg_index_out => l_index
1531 );
1532 fnd_file.put_line (fnd_file.LOG,
1533 'READ SALES CREDIT ERROR IN HEADER'
1534 || l_msg_data
1535 );
1536 END LOOP;
1537
1538 RAISE g_exception_halt_validation;
1539 END IF;
1540 END IF;
1541
1542 fnd_file.put_line (fnd_file.LOG,
1543 'OC_INT ...... Cov Temp Id = '
1544 || NVL (l_k_line_rec.coverage_schd_id, -12345)
1545 );
1546 l_line_rec.k_id := l_chrid;
1547 l_line_rec.k_line_number := okc_api.g_miss_char;
1548 -- l_line_rec.line_sts_code := 'ACTIVE';
1549 l_line_rec.cust_account := l_k_line_rec.customer_acct_id;
1550 l_line_rec.org_id := l_k_header_rec.authoring_org_id;
1551 -- mmadhavi should change to org_id for MOAC
1552 l_line_rec.srv_id := l_k_line_rec.srv_id;
1553 l_line_rec.object_name := l_k_line_rec.srv_desc;
1554 l_line_rec.srv_segment1 := l_k_line_rec.srv_segment1;
1555 l_line_rec.srv_desc := l_k_line_rec.srv_desc;
1556 l_line_rec.srv_sdt := l_k_line_rec.srv_sdt;
1557 l_line_rec.srv_edt := l_k_line_rec.srv_edt;
1558 l_line_rec.bill_to_id := l_k_line_rec.bill_to_id;
1559 l_line_rec.ship_to_id := l_k_line_rec.ship_to_id;
1560 l_line_rec.order_line_id := p_reproc_line_rec.order_line_id;
1561 --l_line_rec.warranty_flag := 'E';
1562 l_line_rec.coverage_template_id := l_k_line_rec.coverage_schd_id;
1563 l_line_rec.currency := l_k_header_rec.currency;
1564 l_line_rec.line_renewal_type := l_renewal_rec.line_renewal_type;
1565 l_line_rec.accounting_rule_id := NVL (l_k_line_rec.accounting_rule_id, 1);
1566 l_line_rec.invoicing_rule_id := NVL (l_k_line_rec.invoicing_rule_id, -2);
1567 l_line_rec.SOURCE := 'NEW';
1568 l_line_rec.upg_orig_system_ref := 'ORDER';
1569 l_line_rec.upg_orig_system_ref_id := NULL;
1570 l_line_rec.commitment_id := l_k_line_rec.commitment_id;
1571 --l_line_rec.tax_amount := l_k_line_rec.tax_amount;
1572 l_line_rec.ln_price_list_id := l_k_line_rec.ln_price_list_id;
1573 --22-NOV-2005 mchoudha PPC
1574 l_line_rec.price_uom := l_price_uom;
1575
1576 --End PPC
1577 IF l_line_rec.srv_sdt > SYSDATE
1578 THEN
1579 oks_extwarprgm_pvt.get_sts_code ('SIGNED',
1580 NULL,
1581 l_ste_code,
1582 l_sts_code
1583 );
1584 ELSE
1585 oks_extwarprgm_pvt.get_sts_code ('ACTIVE',
1586 NULL,
1587 l_ste_code,
1588 l_sts_code
1589 );
1590 END IF;
1591
1592 l_line_rec.line_sts_code := l_sts_code;
1593 -- Added by JVARGHES for 12.0 enhancements
1594 l_line_rec.standard_cov_yn := 'Y';
1595 --
1596 -- Added as part of bug fix 5008188
1597 l_line_rec.tax_classification_code := l_k_line_rec.tax_classification_code;
1598 l_line_rec.exemption_certificate_number := l_k_line_rec.exemption_certificate_number;
1599 l_line_rec.exemption_reason_code := l_k_line_rec.exemption_reason_code;
1600 l_line_rec.tax_status := l_k_line_rec.tax_status;
1601 --
1602 oks_extwarprgm_pvt.create_k_service_lines
1603 (p_k_line_rec => l_line_rec,
1604 p_contact_tbl => l_contact_tbl,
1605 p_salescredit_tbl_in => l_salescredit_tbl_in,
1606 p_caller => 'OC',
1607 x_order_error => l_order_error,
1608 x_service_line_id => l_lineid,
1609 x_return_status => l_return_status,
1610 x_msg_count => l_msg_count,
1611 x_msg_data => l_msg_data
1612 );
1613 fnd_file.put_line (fnd_file.LOG,
1614 'OC INTERFACE :- Create K Line ID = '
1615 || NVL (l_lineid, -1234)
1616 );
1617 fnd_file.put_line (fnd_file.LOG,
1618 'OC INTERFACE :- Create K Line status '
1619 || l_return_status
1620 );
1621
1622 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
1623 THEN
1624 IF l_order_error IS NULL
1625 THEN
1626 l_order_error := '#';
1627
1628 FOR i IN 1 .. fnd_msg_pub.count_msg
1629 LOOP
1630 fnd_msg_pub.get (p_msg_index => i,
1631 p_encoded => 'T',
1632 p_data => l_msg_data,
1633 p_msg_index_out => l_index
1634 );
1635 l_order_error := l_order_error || l_msg_data || '#';
1636 fnd_message.set_encoded (l_msg_data);
1637 l_msg_data := fnd_message.get;
1638 fnd_file.put_line (fnd_file.LOG,
1639 'Create_k_service_line FAILURE '
1640 || l_msg_data
1641 );
1642 END LOOP;
1643 END IF;
1644
1645 x_upd_tbl (1).ERROR_TEXT := l_order_error;
1646 x_upd_tbl (1).success_flag := 'E';
1647 x_upd_tbl (1).order_line_id := p_reproc_line_rec.order_line_id;
1648 x_upd_tbl (1).ID := p_reproc_line_rec.ID;
1649 fnd_file.put_line (fnd_file.LOG,
1650 'Inserting error msg ...'
1651 || x_upd_tbl (1).ERROR_TEXT
1652 );
1653 RAISE g_exception_halt_validation;
1654 END IF;
1655
1656 --Copy line id for Reprocessing
1657 x_upd_tbl (1).contract_line_id := l_lineid;
1658 l_error := 'N';
1659
1660 FOR cp_ctr IN 1 .. l_cp_tbl.COUNT
1661 LOOP
1662 IF (l_error <> 'Y')
1663 THEN
1664 OPEN l_cp_csr (l_cp_tbl (cp_ctr).cp_id);
1665
1666 FETCH l_cp_csr
1667 INTO l_cp_id, l_cp_inventory, l_cp_name, l_cp_desc, l_cp_qty,
1668 l_cp_uom;
1669
1670 CLOSE l_cp_csr;
1671
1672 oks_extwar_util_pvt.get_pricing_attributes
1673 (p_order_line_id => p_reproc_line_rec.order_line_id,
1674 x_pricing_att => l_pricing_attributes_in,
1675 x_return_status => l_return_status
1676 );
1677
1678 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
1679 THEN
1680 FOR i IN 1 .. fnd_msg_pub.count_msg
1681 LOOP
1682 fnd_msg_pub.get (p_msg_index => -1,
1683 p_encoded => 'F',
1684 p_data => l_msg_data,
1685 p_msg_index_out => l_index
1686 );
1687 fnd_file.put_line (fnd_file.LOG,
1688 'READ PRICING ATTRIBS ERROR '
1689 || l_msg_data
1690 );
1691 END LOOP;
1692
1693 RAISE g_exception_halt_validation;
1694 END IF;
1695
1696 IF fnd_profile.VALUE ('OKS_ITEM_DISPLAY_PREFERENCE') =
1697 'DISPLAY_NAME'
1698 THEN
1699 l_covd_rec.product_segment1 := l_cp_desc;
1700 l_covd_rec.product_desc := l_cp_name;
1701 ELSE
1702 l_covd_rec.product_segment1 := l_cp_name;
1703 l_covd_rec.product_desc := l_cp_desc;
1704 END IF;
1705
1706 -- rty_code has been added (Extwarranty consolidation) vigandhi
1707 ----mmadhavi added bom_explosion
1708 OPEN l_serv_ref_csr (p_reproc_line_rec.order_line_id);
1709
1710 FETCH l_serv_ref_csr
1711 INTO l_serv_ref_rec;
1712
1713 CLOSE l_serv_ref_csr;
1714
1715 IF l_serv_ref_rec.service_reference_type_code = 'ORDER'
1716 THEN
1717 OPEN l_inv_csr (l_serv_ref_rec.service_reference_line_id);
1718
1719 FETCH l_inv_csr
1720 INTO l_inv_item_id;
1721
1722 CLOSE l_inv_csr;
1723
1724 IF l_inv_item_id = l_cp_inventory
1725 THEN
1726 l_covd_rec.negotiated_amount :=
1727 l_k_line_rec.unit_selling_price * l_cp_qty;
1728 l_covd_rec.list_price := l_k_line_rec.unit_selling_price;
1729 l_covd_rec.tax_amount := l_k_line_rec.tax_amount; -- Bug# 5274971
1730 l_BOM_instance_flag := 'Y'; -- Bug# 5165947
1731
1732 ELSE
1733 l_covd_rec.negotiated_amount := 0;
1734 l_covd_rec.list_price := 0;
1735 l_covd_rec.tax_amount := 0; -- Bug# 5274971
1736 l_BOM_instance_flag := 'N'; -- Bug# 5165947
1737
1738 END IF;
1739 ELSE
1740 l_covd_rec.negotiated_amount :=
1741 l_k_line_rec.unit_selling_price * l_cp_qty;
1742 l_covd_rec.list_price := l_k_line_rec.unit_selling_price;
1743 l_covd_rec.tax_amount := l_k_line_rec.tax_amount; -- Bug# 5274971
1744 l_BOM_instance_flag := 'Y'; -- Bug# 5165947
1745
1746 END IF;
1747
1748 -- mmadhavi end of BOM explosion
1749 l_covd_rec.k_id := l_chrid;
1750 l_covd_rec.attach_2_line_id := l_lineid;
1751 l_covd_rec.line_number := okc_api.g_miss_char;
1752 --l_covd_rec.product_sts_code := 'ACTIVE';
1753 l_covd_rec.customer_product_id := l_cp_tbl (cp_ctr).cp_id;
1754 --l_covd_rec.Product_Segment1 := l_cp_name;
1755 --l_covd_rec.Product_Desc := l_cp_desc;
1756 --bug 3761489
1757 l_covd_rec.prod_item_id := l_cp_inventory;
1758 --bug 3761489
1759 l_covd_rec.product_start_date := l_k_line_rec.srv_sdt;
1760 l_covd_rec.product_end_date := l_k_line_rec.srv_edt;
1761 l_covd_rec.quantity := l_cp_qty;
1762 l_covd_rec.uom_code := l_cp_uom;
1763 --l_covd_rec.negotiated_amount := l_k_line_rec.unit_selling_price * l_cp_qty;
1764 --l_covd_rec.warranty_flag := 'E';
1765 l_covd_rec.line_renewal_type := l_renewal_rec.line_renewal_type;
1766 --l_covd_rec.list_price := l_k_line_rec.unit_selling_price;
1767 l_covd_rec.currency_code := l_k_header_rec.currency;
1768 l_covd_rec.order_line_id := p_reproc_line_rec.order_line_id;
1769 l_covd_rec.attach_2_line_desc := l_k_line_rec.srv_desc;
1770 l_covd_rec.rty_code := 'CONTRACTSERVICESORDER';
1771 l_covd_rec.upg_orig_system_ref := 'ORDER_LINE';
1772 l_covd_rec.upg_orig_system_ref_id :=
1773 p_reproc_line_rec.order_line_id;
1774
1775 -- Bug# 5274971
1776 -- l_covd_rec.tax_amount := l_k_line_rec.tax_amount;
1777 --
1778 --22-NOV-2005 mchoudha PPC
1779 l_covd_rec.toplvl_uom_code := l_k_line_rec.pricing_quantity_uom;
1780 l_covd_rec.price_uom := l_k_line_rec.order_quantity_uom;
1781 --mchoudha added for bug#5233956
1782 l_covd_rec.toplvl_price_qty := l_k_line_rec.pricing_quantity;
1783
1784 --End PPC
1785 IF l_covd_rec.product_start_date > SYSDATE
1786 THEN
1787 oks_extwarprgm_pvt.get_sts_code ('SIGNED',
1788 NULL,
1789 l_ste_code,
1790 l_sts_code
1791 );
1792 ELSE
1793 oks_extwarprgm_pvt.get_sts_code ('ACTIVE',
1794 NULL,
1795 l_ste_code,
1796 l_sts_code
1797 );
1798 END IF;
1799
1800 l_covd_rec.product_sts_code := l_sts_code;
1801 oks_extwarprgm_pvt.create_k_covered_levels
1802 (p_k_covd_rec => l_covd_rec,
1803 p_price_attribs => l_pricing_attributes_in,
1804 p_caller => 'OC',
1805 x_order_error => l_order_error,
1806 x_covlvl_id => l_covlvl_id,
1807 x_update_line => l_update_line,
1808 x_return_status => l_return_status,
1809 x_msg_count => l_msg_count,
1810 x_msg_data => l_msg_data
1811 );
1812
1813 -- Added for fix of bug# 5165947
1814
1815 fnd_file.put_line
1816 (fnd_file.LOG,
1817 'l_BOM_instance_flag = '|| l_BOM_instance_flag
1818 );
1819
1820 IF l_BOM_instance_flag = 'Y' THEN
1821 l_BOM_instance_id := l_covlvl_id;
1822
1823 fnd_file.put_line
1824 (fnd_file.LOG,
1825 'l_BOM_CovLvl_id = '|| TO_CHAR (l_BOM_instance_id)
1826 );
1827
1828 END IF;
1829
1830 IF l_update_line = 'Y' THEN
1831 l_eff_line_upd_flag := 'Y';
1832
1833 fnd_file.put_line
1834 (fnd_file.LOG,
1835 'l_eff_line_upd_flag = '|| l_eff_line_upd_flag
1836 );
1837
1838 END IF;
1839
1840 -- Added for fix of bug# 5165947
1841
1842 fnd_file.put_line
1843 (fnd_file.LOG,
1844 'OC INTERFACE :- Create K Covd Line Subline ID = '
1845 || TO_CHAR (l_covlvl_id)
1846 );
1847 fnd_file.put_line
1848 (fnd_file.LOG,
1849 'OC INTERFACE :- Create K Covd Line status '
1850 || l_return_status
1851 );
1852
1853 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
1854 THEN
1855 IF l_order_error IS NULL
1856 THEN
1857 l_order_error := '#';
1858
1859 FOR i IN 1 .. fnd_msg_pub.count_msg
1860 LOOP
1861 fnd_msg_pub.get (p_msg_index => i,
1862 p_encoded => 'T',
1863 p_data => l_msg_data,
1864 p_msg_index_out => l_index
1865 );
1866 l_order_error := l_order_error || l_msg_data || '#';
1867 fnd_message.set_encoded (l_msg_data);
1868 l_msg_data := fnd_message.get;
1869 fnd_file.put_line
1870 (fnd_file.LOG,
1871 'Create_K_Covered_levels FAILURE '
1872 || l_msg_data
1873 );
1874 END LOOP;
1875 END IF;
1876
1877 x_upd_tbl (1).ERROR_TEXT := l_order_error;
1878 x_upd_tbl (1).success_flag := 'E';
1879 x_upd_tbl (1).order_line_id :=
1880 p_reproc_line_rec.order_line_id;
1881 x_upd_tbl (1).ID := p_reproc_line_rec.ID;
1882 l_error := 'Y';
1883 RAISE g_exception_halt_validation;
1884 END IF;
1885
1886 x_upd_tbl (cp_ctr).subline_id := l_covlvl_id;
1887 x_upd_tbl (cp_ctr).contract_line_id := l_lineid;
1888 x_upd_tbl (cp_ctr).contract_id := l_chrid;
1889
1890 OPEN l_lndates_csr (l_lineid);
1891
1892 FETCH l_lndates_csr
1893 INTO l_lndates_rec;
1894
1895 CLOSE l_lndates_csr;
1896
1897 OPEN l_hdrdates_csr (l_chrid);
1898
1899 FETCH l_hdrdates_csr
1900 INTO l_hdrdates_rec;
1901
1902 CLOSE l_hdrdates_csr;
1903
1904 OPEN l_refnum_csr (l_cp_tbl (cp_ctr).cp_id);
1905
1906 FETCH l_refnum_csr
1907 INTO l_ref_num;
1908
1909 CLOSE l_refnum_csr;
1910
1911 l_insthist_rec.instance_id := l_cp_tbl (cp_ctr).cp_id;
1912 l_insthist_rec.transaction_type := 'NEW';
1913 l_insthist_rec.transaction_date := SYSDATE;
1914 l_insthist_rec.reference_number := l_ref_num;
1915 oks_ins_pvt.insert_row (p_api_version => 1.0,
1916 p_init_msg_list => 'T',
1917 x_return_status => l_return_status,
1918 x_msg_count => l_msg_count,
1919 x_msg_data => l_msg_data,
1920 p_insv_rec => l_insthist_rec,
1921 x_insv_rec => x_insthist_rec
1922 );
1923 fnd_file.put_line (fnd_file.LOG,
1924 ' instance history Status : '
1925 || l_return_status
1926 );
1927 x_return_status := l_return_status;
1928
1929 IF NOT l_return_status = okc_api.g_ret_sts_success
1930 THEN
1931 x_return_status := l_return_status;
1932 RAISE g_exception_halt_validation;
1933 END IF;
1934
1935 l_inst_dtls_rec.ins_id := x_insthist_rec.ID;
1936 l_inst_dtls_rec.transaction_date := SYSDATE;
1937 l_inst_dtls_rec.transaction_type := 'NEW';
1938 l_inst_dtls_rec.instance_id_new := l_cp_tbl (cp_ctr).cp_id;
1939 l_inst_dtls_rec.instance_amt_new :=
1940 l_covd_rec.negotiated_amount;
1941 --l_k_line_rec.unit_selling_price * l_cp_qty;
1942 l_inst_dtls_rec.instance_qty_new := l_cp_qty;
1943 l_inst_dtls_rec.new_contract_id := l_chrid;
1944 l_inst_dtls_rec.new_contact_start_date :=
1945 l_hdrdates_rec.start_date;
1946 l_inst_dtls_rec.new_contract_end_date :=
1947 l_hdrdates_rec.end_date;
1948 l_inst_dtls_rec.new_service_line_id := l_lineid;
1949 l_inst_dtls_rec.new_service_start_date :=
1950 l_lndates_rec.start_date;
1951 l_inst_dtls_rec.new_service_end_date := l_lndates_rec.end_date;
1952 l_inst_dtls_rec.new_subline_id := l_covlvl_id;
1953 l_inst_dtls_rec.new_subline_start_date := l_k_line_rec.srv_sdt;
1954 l_inst_dtls_rec.new_subline_end_date := l_k_line_rec.srv_edt;
1955 l_inst_dtls_rec.new_customer := l_k_line_rec.customer_acct_id;
1956 l_inst_dtls_rec.new_k_status := l_hdrdates_rec.sts_code;
1957 oks_ihd_pvt.insert_row (p_api_version => 1.0,
1958 p_init_msg_list => 'T',
1959 x_return_status => l_return_status,
1960 x_msg_count => l_msg_count,
1961 x_msg_data => l_msg_data,
1962 p_ihdv_rec => l_inst_dtls_rec,
1963 x_ihdv_rec => x_inst_dtls_rec
1964 );
1965 fnd_file.put_line (fnd_file.LOG,
1966 ' instance history details Status : '
1967 || l_return_status
1968 );
1969 x_return_status := l_return_status;
1970
1971 IF NOT l_return_status = okc_api.g_ret_sts_success
1972 THEN
1973 x_return_status := l_return_status;
1974 RAISE g_exception_halt_validation;
1975 END IF;
1976 ELSE
1977 EXIT;
1978 END IF;
1979 END LOOP;
1980
1981 --Added for fix of bug# 5165947
1982
1983 fnd_file.put_line (fnd_file.LOG,
1984 'l_eff_line_upd_flag : '||l_eff_line_upd_flag
1985 );
1986
1987 IF l_eff_line_upd_flag = 'Y' THEN
1988 NULL;
1989 ELSE
1990 open c_line_ammt_ckeck(c_line_id => l_lineid);
1991 fetch c_line_ammt_ckeck into l_prev_line_amt;
1992 close c_line_ammt_ckeck;
1993
1994 fnd_file.put_line (fnd_file.LOG,
1995 'l_prev_line_amt : '||to_char(l_prev_line_amt)
1996 );
1997 END IF;
1998
1999 --Added for fix of bug# 5165947
2000
2001
2002 --Added for fix of bug# 5274971
2003
2004 UPDATE okc_k_lines_b
2005 SET price_negotiated =
2006 (SELECT NVL (SUM (NVL (price_negotiated, 0)), 0)
2007 FROM okc_k_lines_b
2008 WHERE cle_id = l_lineid AND dnz_chr_id = l_chrid)
2009 WHERE ID = l_lineid;
2010
2011 UPDATE okc_k_headers_all_b --mmadhavi _all for MOAC
2012 SET estimated_amount =
2013 (SELECT NVL (SUM (NVL (price_negotiated, 0)), 0)
2014 FROM okc_k_lines_b
2015 WHERE dnz_chr_id = l_chrid AND lse_id IN (1, 19))
2016 WHERE ID = l_chrid;
2017
2018
2019 -- Rollup the subline tax amount to topline
2020 OPEN c_extwar_line_amount(l_chrid, l_lineid);
2021 FETCH c_extwar_line_amount INTO l_line_tax_amount;
2022 CLOSE c_extwar_line_amount;
2023
2024 -- Update the topline with the tax amount
2025 UPDATE oks_k_lines_b
2026 SET tax_amount = l_line_tax_amount
2027 WHERE cle_id = l_lineid;
2028
2029 -- Rollup the topline tax amount to header
2030 OPEN c_extwar_hdr_amount(l_chrid);
2031 FETCH c_extwar_hdr_amount INTO l_header_tax_amount;
2032 CLOSE c_extwar_hdr_amount;
2033
2034 -- Update the header with the tax amount
2035 UPDATE oks_k_headers_b
2036 SET tax_amount = l_header_tax_amount
2037 WHERE chr_id = l_chrid;
2038
2039 --Added for fix of bug# 5274971
2040
2041 -- create billing schedule
2042 l_strmlvl_id := check_strmlvl_exists (l_lineid);
2043
2044 IF l_strmlvl_id IS NULL
2045 THEN
2046 l_sll_tbl (1).cle_id := l_lineid;
2047 --l_sll_tbl(1).billing_type := 'T';
2048 l_sll_tbl (1).sequence_no := '1';
2049 l_sll_tbl (1).level_periods := '1';
2050 l_sll_tbl (1).start_date := l_k_line_rec.srv_sdt;
2051 l_sll_tbl (1).advance_periods := NULL;
2052 l_sll_tbl (1).level_amount := NULL;
2053 l_sll_tbl (1).invoice_offset_days := NULL;
2054 l_sll_tbl (1).interface_offset_days := NULL;
2055
2056 --22-NOV-2005 mchoudha added for PPC
2057 IF l_period_start IS NOT NULL
2058 AND l_period_type IS NOT NULL
2059 AND l_period_start = 'CALENDAR'
2060 THEN
2061 l_sll_tbl (1).uom_code := 'DAY';
2062 l_sll_tbl (1).uom_per_period :=
2063 l_k_line_rec.srv_edt - l_k_line_rec.srv_sdt + 1;
2064 ELSE
2065 okc_time_util_pub.get_duration
2066 (p_start_date => l_k_line_rec.srv_sdt,
2067 p_end_date => l_k_line_rec.srv_edt,
2068 x_duration => l_duration,
2069 x_timeunit => l_timeunits,
2070 x_return_status => l_return_status
2071 );
2072
2073 --OKS_RENEW_PVT.DEBUG_LOG( '(OKS_EXTWARPRGM_PVT).Create_Billing_Schd :: Get_Duration Status : '|| l_return_status );
2074 --OKS_RENEW_PVT.DEBUG_LOG( '(OKS_EXTWARPRGM_PVT).Create_Billing_Schd :: Duration : '|| l_duration );
2075 --OKS_RENEW_PVT.DEBUG_LOG( '(OKS_EXTWARPRGM_PVT).Create_Billing_Schd :: Time Unit : '|| l_timeunits );
2076 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
2077 THEN
2078 fnd_log.STRING
2079 (fnd_log.level_event,
2080 g_module_current || '.ORDER_REPROCESS.CREATE_CONTRACT',
2081 ' Okc_time_util_pub.get_duration(Return status = '
2082 || l_return_status
2083 || ' ,Duration = '
2084 || l_duration
2085 || ' ,Time Unit = '
2086 || l_timeunits
2087 || ')'
2088 );
2089 END IF;
2090
2091 IF NOT l_return_status = 'S'
2092 THEN
2093 RAISE g_exception_halt_validation;
2094 END IF;
2095
2096 l_sll_tbl (1).uom_code := l_timeunits;
2097 l_sll_tbl (1).uom_per_period := l_duration;
2098 END IF;
2099
2100 oks_bill_sch.create_bill_sch_rules
2101 (p_billing_type => 'T',
2102 p_sll_tbl => l_sll_tbl,
2103 p_invoice_rule_id => l_line_rec.invoicing_rule_id,
2104 x_bil_sch_out_tbl => l_bil_sch_out,
2105 x_return_status => l_return_status
2106 );
2107 fnd_file.put_line
2108 (fnd_file.LOG,
2109 'OKS_BILL_SCH.Create_Bill_Sch_Rules(Return status = '
2110 || l_return_status
2111 || ')'
2112 );
2113
2114 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
2115 THEN
2116 fnd_log.STRING
2117 (fnd_log.level_event,
2118 g_module_current || '.ORDER_REPROCESS.CREATE_CONTRACT',
2119 ' OKS_BILL_SCH.Create_Bill_Sch_Rules(Return status = '
2120 || l_return_status
2121 || ')'
2122 );
2123 END IF;
2124
2125 --OKS_RENEW_PVT.DEBUG_LOG( '(OKS_EXTWARPRGM_PVT).Create_Billing_Schd :: Create_Bill_Sch_Rules Status : '|| l_return_status );
2126 IF l_return_status <> okc_api.g_ret_sts_success
2127 THEN
2128 okc_api.set_message (g_app_name,
2129 g_required_value,
2130 g_col_name_token,
2131 'Sched Billing Rule (LINE)'
2132 );
2133 RAISE g_exception_halt_validation;
2134 END IF;
2135
2136 oks_bill_util_pub.create_bcl_for_om
2137 (p_line_id => l_lineid,
2138 x_return_status => l_return_status
2139 );
2140 fnd_file.put_line (fnd_file.LOG,
2141 'Create_Contract :- CREATE_BCL_FOR_OM '
2142 || l_return_status
2143 );
2144
2145 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
2146 THEN
2147 fnd_log.STRING
2148 (fnd_log.level_event,
2149 g_module_current || '.ORDER_REPROCESS.CREATE_CONTRACT',
2150 ' OKS_BILL_UTIL_PUB.CREATE_BCL_FOR_OM(Return status = '
2151 || l_return_status
2152 || ')'
2153 );
2154 END IF;
2155
2156 --OKS_RENEW_PVT.DEBUG_LOG( '(OKS_EXTWARPRGM_PVT).Create_contract_Ibnew :: CREATE_BCL_FOR_OM '|| l_return_status);
2157 IF NOT l_return_status = 'S'
2158 THEN
2159 RAISE g_exception_halt_validation;
2160 END IF;
2161 ELSE
2162 IF check_lvlelements_exists (l_lineid)
2163 THEN
2164
2165 --Added for fix of bug# 5165947
2166
2167 fnd_file.put_line (fnd_file.LOG,
2168 'l_eff_line_upd_flag : '||l_eff_line_upd_flag
2169 );
2170
2171 IF l_eff_line_upd_flag = 'Y' THEN
2172 NULL;
2173 ELSE
2174 open c_line_ammt_ckeck(c_line_id => l_lineid);
2175 fetch c_line_ammt_ckeck into l_curr_line_amt;
2176 close c_line_ammt_ckeck;
2177
2178 fnd_file.put_line (fnd_file.LOG,
2179 'l_curr_line_amt : '||to_char(l_curr_line_amt)
2180 );
2181
2182 if nvl(l_curr_line_amt,0) <> nvl(l_prev_line_amt,0) THEN
2183 l_eff_line_upd_flag := 'Y';
2184
2185 fnd_file.put_line (fnd_file.LOG,
2186 'l_eff_line_upd_flag (final) : '||l_eff_line_upd_flag
2187 );
2188
2189 end if;
2190
2191 END IF;
2192
2193 l_update_line := NVL(l_eff_line_upd_flag,l_update_line);
2194
2195 --Added for fix of bug# 5165947
2196
2197
2198 IF l_update_line = 'Y'
2199 THEN
2200 oks_bill_sch.update_om_sll_date
2201 (p_top_line_id => l_lineid,
2202 x_return_status => l_return_status,
2203 x_msg_count => x_msg_count,
2204 x_msg_data => x_msg_data
2205 );
2206 fnd_file.put_line (fnd_file.LOG,
2207 'IBNEW :- Update_OM_SLL_Date '
2208 || l_return_status
2209 );
2210
2211 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level
2212 )
2213 THEN
2214 fnd_log.STRING
2215 (fnd_log.level_event,
2216 g_module_current
2217 || '.ORDER_REPROCESS.CREATE_CONTRACT',
2218 ' OKS_BILL_SCH.UPDATE_OM_SLL_DATE(Return status = '
2219 || l_return_status
2220 || ')'
2221 );
2222 END IF;
2223
2224 --OKS_RENEW_PVT.DEBUG_LOG('(OKS_EXTWARPRGM_PVT).Create_contract_Ibnew :: Update_OM_SLL_Date ' || l_return_status);
2225 IF NOT l_return_status = 'S'
2226 THEN
2227 RAISE g_exception_halt_validation;
2228 END IF;
2229 ELSE
2230 oks_bill_sch.create_bill_sch_cp
2231 (p_top_line_id => l_lineid,
2232 p_cp_line_id => l_covlvl_id,
2233 p_cp_new => 'Y',
2234 x_return_status => l_return_status,
2235 x_msg_count => x_msg_count,
2236 x_msg_data => x_msg_data
2237 );
2238 fnd_file.put_line
2239 (fnd_file.LOG,
2240 'Create_Contract :- Create_Bill_Sch_CP '
2241 || l_return_status
2242 );
2243
2244 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level
2245 )
2246 THEN
2247 fnd_log.STRING
2248 (fnd_log.level_event,
2249 g_module_current
2250 || '.ORDER_REPROCESS.CREATE_CONTRACT',
2251 ' OKS_BILL_SCH.CREATE_BILL_SCH_CP(Return status = '
2252 || l_return_status
2253 || ' ,'
2254 || TO_CHAR (SYSDATE, 'dd-mon-yyyy HH24:MI:SS')
2255 || ')'
2256 );
2257 END IF;
2258
2259 /*
2260 OKS_RENEW_PVT.DEBUG_LOG(TO_CHAR( SYSDATE, 'dd-mon-yyyy HH24:MI:SS')
2261 || '(OKS_EXTWARPRGM_PVT).Create_contract_Ibnew :: Create_Bill_Sch_CP '
2262 || l_return_status
2263 ); */
2264 IF NOT l_return_status = 'S'
2265 THEN
2266 RAISE g_exception_halt_validation;
2267 END IF;
2268 END IF;
2269
2270 oks_bill_util_pub.create_bcl_for_om
2271 (p_line_id => l_lineid,
2272 x_return_status => l_return_status
2273 );
2274 fnd_file.put_line (fnd_file.LOG,
2275 'Create_Contract :- CREATE_BCL_FOR_OM '
2276 || l_return_status
2277 );
2278
2279 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
2280 THEN
2281 fnd_log.STRING
2282 (fnd_log.level_event,
2283 g_module_current || '.ORDER_REPROCESS.CREATE_CONTRACT',
2284 ' OKS_BILL_UTIL_PUB.CREATE_BCL_FOR_OM(Return status = '
2285 || l_return_status
2286 || ')'
2287 );
2288 END IF;
2289
2290 --OKS_RENEW_PVT.DEBUG_LOG( '(OKS_EXTWARPRGM_PVT).Create_contract_Ibnew :: CREATE_BCL_FOR_OM '|| l_return_status);
2291 IF NOT l_return_status = 'S'
2292 THEN
2293 RAISE g_exception_halt_validation;
2294 END IF;
2295 ELSE
2296 okc_api.set_message (g_app_name,
2297 g_required_value,
2298 g_col_name_token,
2299 'level elements NOT EXIST'
2300 );
2301 RAISE g_exception_halt_validation;
2302 END IF;
2303 END IF;
2304
2305 -- Commented out for fix of bug# 5274971
2306 --
2307 -- UPDATE okc_k_lines_b
2308 -- SET price_negotiated =
2309 -- (SELECT NVL (SUM (NVL (price_negotiated, 0)), 0)
2310 -- FROM okc_k_lines_b
2311 -- WHERE cle_id = l_lineid AND dnz_chr_id = l_chrid)
2312 -- WHERE ID = l_lineid;
2313 --
2314 -- UPDATE okc_k_headers_all_b --mmadhavi _all for MOAC
2315 -- SET estimated_amount =
2316 -- (SELECT NVL (SUM (NVL (price_negotiated, 0)), 0)
2317 -- FROM okc_k_lines_b
2318 -- WHERE dnz_chr_id = l_chrid AND lse_id IN (1, 19))
2319 -- WHERE ID = l_chrid;
2320 --
2321 --
2322 -- -- Rollup the subline tax amount to topline
2323 -- OPEN c_extwar_line_amount(l_chrid, l_lineid);
2324 -- FETCH c_extwar_line_amount INTO l_line_tax_amount;
2325 -- CLOSE c_extwar_line_amount;
2326 --
2327 -- -- Update the topline with the tax amount
2328 -- UPDATE oks_k_lines_b
2329 -- SET tax_amount = l_line_tax_amount
2330 -- WHERE cle_id = l_lineid;
2331 --
2332 -- -- Rollup the topline tax amount to header
2333 -- OPEN c_extwar_hdr_amount(l_chrid);
2334 -- FETCH c_extwar_hdr_amount INTO l_header_tax_amount;
2335 -- CLOSE c_extwar_hdr_amount;
2336 --
2337 -- -- Update the header with the tax amount
2338 -- UPDATE oks_k_headers_b
2339 -- SET tax_amount = l_header_tax_amount
2340 -- WHERE chr_id = l_chrid;
2341 --
2342 -- Commented out for fix of bug# 5274971
2343
2344 EXCEPTION
2345 WHEN g_exception_halt_validation
2346 THEN
2347 x_return_status := l_return_status;
2348 IF c_extwar_line_amount%ISOPEN THEN
2349 CLOSE c_extwar_line_amount;
2350 END IF;
2351 IF c_extwar_hdr_amount%ISOPEN THEN
2352 CLOSE c_extwar_hdr_amount;
2353 END IF;
2354 WHEN OTHERS
2355 THEN
2356 x_return_status := okc_api.g_ret_sts_unexp_error;
2357 IF c_extwar_line_amount%ISOPEN THEN
2358 CLOSE c_extwar_line_amount;
2359 END IF;
2360 IF c_extwar_hdr_amount%ISOPEN THEN
2361 CLOSE c_extwar_hdr_amount;
2362 END IF;
2363 fnd_file.put_line (fnd_file.LOG,
2364 'Oracle Error Code is -' || TO_CHAR (SQLCODE)
2365 );
2366 fnd_file.put_line (fnd_file.LOG,
2367 'Oracle Error Message is -'
2368 || SUBSTR (SQLERRM, 1, 512)
2369 );
2370 END create_contract;
2371
2372 PROCEDURE get_order_details (
2373 p_option IN VARCHAR2,
2374 p_source IN VARCHAR2,
2375 x_return_status OUT NOCOPY VARCHAR2,
2376 x_repv_tbl OUT NOCOPY oks_rep_pvt.repv_tbl_type
2377 )
2378 IS
2379 --mmadhavi modified cursors for MOAC
2380 CURSOR l_order_line_sub_csr
2381 IS
2382 SELECT ID, order_id, order_line_id, success_flag, source_flag,
2383 rep.order_number
2384 FROM oks_reprocessing_v rep, oe_order_headers oh
2385 WHERE success_flag = 'R'
2386 AND rep.order_id = oh.header_id
2387 AND conc_request_id IS NULL;
2388
2389 CURSOR l_order_line_sel_csr
2390 IS
2391 SELECT ID, order_id, order_line_id, success_flag, source_flag,
2392 rep.order_number
2393 FROM oks_reprocessing_v rep, oe_order_headers oh
2394 WHERE reprocess_yn = 'Y' --success_flag IN ('R','N')
2395 AND rep.order_id = oh.header_id
2396 AND conc_request_id IS NULL;
2397
2398 CURSOR l_order_line_all_csr
2399 IS
2400 SELECT ID, order_id, order_line_id, success_flag, source_flag,
2401 rep.order_number
2402 FROM oks_reprocessing_v rep, oe_order_headers oh
2403 WHERE NVL (success_flag, 'E') IN ('E', 'N') ---IN ('R','N','E')
2404 AND rep.order_id = oh.header_id
2405 AND conc_request_id IS NULL;
2406
2407 l_repv_tbl oks_rep_pvt.repv_tbl_type;
2408 l_return_status VARCHAR2 (1) := okc_api.g_ret_sts_success;
2409 l_ordline_rec oks_rep_pvt.repv_rec_type;
2410 l_ptr NUMBER;
2411 l_source VARCHAR2 (30);
2412
2413 PROCEDURE get_order_lines (
2414 p_id IN NUMBER,
2415 p_order_id IN NUMBER,
2416 p_ord_num IN NUMBER,
2417 p_success_flag IN VARCHAR2,
2418 p_source_flag IN VARCHAR2,
2419 x_repv_tbl OUT NOCOPY oks_rep_pvt.repv_tbl_type,
2420 x_return_status OUT NOCOPY VARCHAR2
2421 )
2422 IS
2423 CURSOR get_order_lines_csr (p_ord_num NUMBER)
2424 IS
2425 SELECT ol.line_id, NVL (fulfilled_quantity, 0),
2426 service_reference_type_code, service_reference_line_id
2427 FROM oe_order_lines_all ol, oe_order_headers oh
2428 WHERE oh.header_id = ol.header_id
2429 AND oh.order_number = p_ord_num;
2430
2431 --and Oh.org_id = okc_context.get_okc_org_id;
2432
2433 /*
2434 Cursor get_ordlines_dtls_csr(p_ord_line_id NUMBER)
2435 Is
2436 Select Nvl(FULFILLED_QUANTITY,0),SERVICE_REFERENCE_TYPE_CODE,SERVICE_REFERENCE_LINE_ID
2437 From oe_order_lines_all
2438 Where line_id = p_ord_line_id
2439 and header_id IN (select header_id from oe_order_headers_all where org_id = okc_context.get_okc_org_id);
2440 */
2441
2442 --mmadhavi modified the cursor for MOAC
2443 CURSOR check_duplicate_csr (p_ordline_id NUMBER)
2444 IS
2445 SELECT 'x'
2446 FROM oks_reprocessing rep, oe_order_headers hdr
2447 WHERE rep.order_line_id = p_ordline_id
2448 AND rep.order_id = hdr.header_id;
2449
2450 l_ord_line_id NUMBER;
2451 serv_ref_type VARCHAR2 (30);
2452 serv_ref_id NUMBER;
2453 l_fulfilled_qty NUMBER;
2454 l_exists VARCHAR2 (1) := 'Y';
2455 l_ptr1 NUMBER;
2456 l_first_order_line NUMBER;
2457 l_init_msg_list VARCHAR2 (1) := okc_api.g_false;
2458 l_return_status VARCHAR2 (1) := okc_api.g_ret_sts_success;
2459 l_msg_count NUMBER := 0;
2460 l_msg_data VARCHAR2 (2000);
2461 l_repv_rec oks_rep_pvt.repv_rec_type;
2462 l_out_repv_rec oks_rep_pvt.repv_rec_type;
2463 BEGIN
2464 fnd_file.put_line (fnd_file.LOG,
2465 'Inside procedure Get Order Lines'
2466 );
2467 l_first_order_line := NULL;
2468 l_ptr1 := 0;
2469
2470 -- mmadhavi commenting for MOAC
2471 --FND_FILE.PUT_LINE (FND_FILE.LOG, ' Okc_context = ' || nvl(okc_context.get_okc_org_id,-999));
2472 OPEN get_order_lines_csr (p_ord_num);
2473
2474 LOOP
2475 FETCH get_order_lines_csr
2476 INTO l_ord_line_id, l_fulfilled_qty, serv_ref_type,
2477 serv_ref_id;
2478
2479 EXIT WHEN get_order_lines_csr%NOTFOUND;
2480 l_exists := 'y';
2481
2482 OPEN check_duplicate_csr (l_ord_line_id);
2483
2484 FETCH check_duplicate_csr
2485 INTO l_exists;
2486
2487 CLOSE check_duplicate_csr;
2488
2489 IF (l_exists <> 'x')
2490 THEN
2491 fnd_file.put_line (fnd_file.LOG, 'No duplicate line..');
2492
2493 /*
2494 Open get_ordlines_dtls_csr(l_ord_line_id);
2495 Fetch get_ordlines_dtls_csr into l_fulfilled_qty,serv_ref_type, serv_ref_id;
2496 Close get_ordlines_dtls_csr;
2497 */
2498 IF ( NVL (serv_ref_type, 'REF_TYPE') IN
2499 ('CUSTOMER_PRODUCT', 'ORDER')
2500 AND serv_ref_id IS NOT NULL
2501 )
2502 THEN
2503 fnd_file.put_line (fnd_file.LOG, 'Valid line...');
2504
2505 IF (p_source_flag = 'ASO')
2506 THEN
2507 IF (l_fulfilled_qty > 0)
2508 THEN
2509 l_ptr1 := l_ptr1 + 1;
2510 fnd_file.put_line (fnd_file.LOG,
2511 'From ASO ..Fulfilled..'
2512 );
2513
2514 IF (l_ptr1 = 1)
2515 THEN
2516 l_first_order_line := l_ord_line_id;
2517 fnd_file.put_line (fnd_file.LOG,
2518 'First Order line..'
2519 );
2520 ELSE
2521 fnd_file.put_line (fnd_file.LOG,
2522 'More lines ..Inserting..'
2523 );
2524 l_repv_rec.order_id := p_order_id;
2525 l_repv_rec.order_line_id := l_ord_line_id;
2526 l_repv_rec.success_flag := p_success_flag;
2527 l_repv_rec.source_flag := p_source_flag;
2528 l_repv_rec.order_number := p_ord_num;
2529 l_repv_rec.reprocess_yn := 'N';
2530 oks_rep_pub.insert_row
2531 (p_api_version => 1.0,
2532 p_init_msg_list => l_init_msg_list,
2533 x_return_status => l_return_status,
2534 x_msg_count => l_msg_count,
2535 x_msg_data => l_msg_data,
2536 p_repv_rec => l_repv_rec,
2537 x_repv_rec => l_out_repv_rec
2538 );
2539 fnd_file.put_line (fnd_file.LOG,
2540 'Return status = '
2541 || l_return_status
2542 );
2543
2544 IF (l_return_status <> 'S')
2545 THEN
2546 x_return_status := l_return_status;
2547 RAISE g_exception_halt_validation;
2548 END IF;
2549
2550 fnd_file.put_line (fnd_file.LOG,
2551 'Insert status = '
2552 || l_return_status
2553 );
2554 x_repv_tbl (l_ptr1).ID := l_out_repv_rec.ID;
2555 x_repv_tbl (l_ptr1).order_id :=
2556 l_out_repv_rec.order_id;
2557 x_repv_tbl (l_ptr1).order_line_id :=
2558 l_out_repv_rec.order_line_id;
2559 x_repv_tbl (l_ptr1).success_flag :=
2560 l_out_repv_rec.success_flag;
2561 x_repv_tbl (l_ptr1).source_flag :=
2562 l_out_repv_rec.source_flag;
2563 x_repv_tbl (l_ptr1).order_number :=
2564 l_out_repv_rec.order_number;
2565 END IF;
2566 END IF;
2567 ELSE
2568 fnd_file.put_line (fnd_file.LOG, 'Source = MANUAL');
2569 l_ptr1 := l_ptr1 + 1;
2570
2571 IF (l_ptr1 = 1)
2572 THEN
2573 l_first_order_line := l_ord_line_id;
2574 fnd_file.put_line (fnd_file.LOG,
2575 'First Order Line...'
2576 );
2577 ELSE
2578 fnd_file.put_line (fnd_file.LOG,
2579 'Next Order lines...'
2580 );
2581 l_repv_rec.order_id := p_order_id;
2582 l_repv_rec.order_line_id := l_ord_line_id;
2583 l_repv_rec.success_flag := p_success_flag;
2584 l_repv_rec.source_flag := p_source_flag;
2585 l_repv_rec.order_number := p_ord_num;
2586 l_repv_rec.reprocess_yn := 'N';
2587 oks_rep_pub.insert_row
2588 (p_api_version => 1.0,
2589 p_init_msg_list => l_init_msg_list,
2590 x_return_status => l_return_status,
2591 x_msg_count => l_msg_count,
2592 x_msg_data => l_msg_data,
2593 p_repv_rec => l_repv_rec,
2594 x_repv_rec => l_out_repv_rec
2595 );
2596 fnd_file.put_line
2597 (fnd_file.LOG,
2598 'oks_rep_pub.insert_row : return status => '
2599 || l_return_status
2600 );
2601
2602 IF (l_return_status <> 'S')
2603 THEN
2604 x_return_status := l_return_status;
2605 RAISE g_exception_halt_validation;
2606 END IF;
2607
2608 fnd_file.put_line (fnd_file.LOG,
2609 'Insert status ...'
2610 || l_return_status
2611 );
2612 x_repv_tbl (l_ptr1).ID := l_out_repv_rec.ID;
2613 x_repv_tbl (l_ptr1).order_id :=
2614 l_out_repv_rec.order_id;
2615 x_repv_tbl (l_ptr1).order_line_id :=
2616 l_out_repv_rec.order_line_id;
2617 x_repv_tbl (l_ptr1).success_flag :=
2618 l_out_repv_rec.success_flag;
2619 x_repv_tbl (l_ptr1).source_flag :=
2620 l_out_repv_rec.source_flag;
2621 x_repv_tbl (l_ptr1).order_number :=
2622 l_out_repv_rec.order_number;
2623 END IF;
2624 END IF; -- Source = ASO
2625 END IF; -- Service line
2626 END IF; -- duplicate line
2627 END LOOP;
2628
2629 fnd_file.put_line (fnd_file.LOG, 'End of Loop...');
2630
2631 IF (l_first_order_line IS NOT NULL)
2632 THEN
2633 x_repv_tbl (1).ID := p_id;
2634 x_repv_tbl (1).order_id := p_order_id;
2635 x_repv_tbl (1).order_line_id := l_first_order_line;
2636 x_repv_tbl (1).success_flag := p_success_flag;
2637 x_repv_tbl (1).source_flag := p_source_flag;
2638 x_repv_tbl (1).order_number := p_ord_num;
2639 END IF;
2640
2641 fnd_file.put_line (fnd_file.LOG, 'Exiting Get Order lines');
2642 EXCEPTION
2643 WHEN g_exception_halt_validation
2644 THEN
2645 fnd_file.put_line (fnd_file.LOG,
2646 ' Error in Get_Order_lines : '
2647 || SQLCODE
2648 || ':'
2649 || SQLERRM
2650 );
2651 okc_api.set_message (g_app_name,
2652 g_unexpected_error,
2653 g_sqlcode_token,
2654 SQLCODE,
2655 g_sqlerrm_token,
2656 SQLERRM
2657 );
2658 WHEN OTHERS
2659 THEN
2660 x_return_status := okc_api.g_ret_sts_unexp_error;
2661 fnd_file.put_line (fnd_file.LOG,
2662 ' Error in Get_Order_lines : '
2663 || SQLCODE
2664 || ':'
2665 || SQLERRM
2666 );
2667 okc_api.set_message (g_app_name,
2668 g_unexpected_error,
2669 g_sqlcode_token,
2670 SQLCODE,
2671 g_sqlerrm_token,
2672 SQLERRM
2673 );
2674 END get_order_lines;
2675 BEGIN
2676 fnd_file.put_line (fnd_file.LOG,
2677 'Inside procedure Get Order Details'
2678 );
2679 l_source := NVL (p_source, 'Auto');
2680
2681 IF (l_source <> 'FORM')
2682 THEN
2683 l_ptr := 0;
2684
2685 IF p_option = 'SEL'
2686 THEN
2687 FOR l_ordline_rec IN l_order_line_sel_csr
2688 LOOP
2689 l_ptr := l_ptr + 1;
2690
2691 IF (l_ordline_rec.order_line_id IS NULL)
2692 THEN
2693 get_order_lines
2694 (p_id => l_ordline_rec.ID,
2695 p_order_id => l_ordline_rec.order_id,
2696 p_ord_num => l_ordline_rec.order_number,
2697 p_success_flag => l_ordline_rec.success_flag,
2698 p_source_flag => l_ordline_rec.source_flag,
2699 x_repv_tbl => l_repv_tbl,
2700 x_return_status => l_return_status
2701 );
2702
2703 IF (l_return_status <> 'S')
2704 THEN
2705 x_return_status := l_return_status;
2706 RAISE g_exception_halt_validation;
2707 END IF;
2708
2709 FOR i IN 1 .. l_repv_tbl.COUNT
2710 LOOP
2711 x_repv_tbl (l_ptr).ID := l_repv_tbl (i).ID;
2712 x_repv_tbl (l_ptr).order_id :=
2713 l_repv_tbl (i).order_id;
2714 x_repv_tbl (l_ptr).order_line_id :=
2715 l_repv_tbl (i).order_line_id;
2716 x_repv_tbl (l_ptr).success_flag :=
2717 l_repv_tbl (i).success_flag;
2718 x_repv_tbl (l_ptr).source_flag :=
2719 l_repv_tbl (i).source_flag;
2720 x_repv_tbl (l_ptr).order_number :=
2721 l_repv_tbl (i).order_number;
2722 l_ptr := l_ptr + 1;
2723 END LOOP;
2724
2725 l_ptr := l_ptr - 1;
2726 ELSE
2727 x_repv_tbl (l_ptr).ID := l_ordline_rec.ID;
2728 x_repv_tbl (l_ptr).order_id := l_ordline_rec.order_id;
2729 x_repv_tbl (l_ptr).order_line_id :=
2730 l_ordline_rec.order_line_id;
2731 x_repv_tbl (l_ptr).success_flag :=
2732 l_ordline_rec.success_flag;
2733 x_repv_tbl (l_ptr).source_flag :=
2734 l_ordline_rec.source_flag;
2735 x_repv_tbl (l_ptr).order_number :=
2736 l_ordline_rec.order_number;
2737 END IF;
2738 END LOOP;
2739 ELSE
2740 FOR l_ordline_rec IN l_order_line_all_csr
2741 LOOP
2742 l_ptr := l_ptr + 1;
2743
2744 IF (l_ordline_rec.order_line_id IS NULL)
2745 THEN
2746 get_order_lines
2747 (p_id => l_ordline_rec.ID,
2748 p_order_id => l_ordline_rec.order_id,
2749 p_ord_num => l_ordline_rec.order_number,
2750 p_success_flag => l_ordline_rec.success_flag,
2751 p_source_flag => l_ordline_rec.source_flag,
2752 x_repv_tbl => l_repv_tbl,
2753 x_return_status => l_return_status
2754 );
2755
2756 IF (l_return_status <> 'S')
2757 THEN
2758 x_return_status := l_return_status;
2759 RAISE g_exception_halt_validation;
2760 END IF;
2761
2762 FOR i IN 1 .. l_repv_tbl.COUNT
2763 LOOP
2764 x_repv_tbl (l_ptr).ID := l_repv_tbl (i).ID;
2765 x_repv_tbl (l_ptr).order_id :=
2766 l_repv_tbl (i).order_id;
2767 x_repv_tbl (l_ptr).order_line_id :=
2768 l_repv_tbl (i).order_line_id;
2769 x_repv_tbl (l_ptr).success_flag :=
2770 l_repv_tbl (i).success_flag;
2771 x_repv_tbl (l_ptr).source_flag :=
2772 l_repv_tbl (i).source_flag;
2773 x_repv_tbl (l_ptr).order_number :=
2774 l_repv_tbl (i).order_number;
2775 l_ptr := l_ptr + 1;
2776 END LOOP;
2777
2778 l_ptr := l_ptr - 1;
2779 ELSE
2780 x_repv_tbl (l_ptr).ID := l_ordline_rec.ID;
2781 x_repv_tbl (l_ptr).order_id := l_ordline_rec.order_id;
2782 x_repv_tbl (l_ptr).order_line_id :=
2783 l_ordline_rec.order_line_id;
2784 x_repv_tbl (l_ptr).success_flag :=
2785 l_ordline_rec.success_flag;
2786 x_repv_tbl (l_ptr).source_flag :=
2787 l_ordline_rec.source_flag;
2788 x_repv_tbl (l_ptr).order_number :=
2789 l_ordline_rec.order_number;
2790 END IF;
2791 END LOOP;
2792 END IF;
2793 ELSE
2794 fnd_file.put_line (fnd_file.LOG, 'Choice - Submitted');
2795 l_ptr := 0;
2796
2797 FOR l_ordline_rec IN l_order_line_sub_csr
2798 LOOP
2799 l_ptr := l_ptr + 1;
2800
2801 IF (l_ordline_rec.order_line_id IS NULL)
2802 THEN
2803 fnd_file.put_line (fnd_file.LOG,
2804 'Calling get_order_lines...'
2805 );
2806 get_order_lines
2807 (p_id => l_ordline_rec.ID,
2808 p_order_id => l_ordline_rec.order_id,
2809 p_ord_num => l_ordline_rec.order_number,
2810 p_success_flag => l_ordline_rec.success_flag,
2811 p_source_flag => l_ordline_rec.source_flag,
2812 x_repv_tbl => l_repv_tbl,
2813 x_return_status => l_return_status
2814 );
2815
2816 IF (l_return_status <> 'S')
2817 THEN
2818 x_return_status := l_return_status;
2819 RAISE g_exception_halt_validation;
2820 END IF;
2821
2822 FOR i IN 1 .. l_repv_tbl.COUNT
2823 LOOP
2824 x_repv_tbl (l_ptr).ID := l_repv_tbl (i).ID;
2825 x_repv_tbl (l_ptr).order_id := l_repv_tbl (i).order_id;
2826 x_repv_tbl (l_ptr).order_line_id :=
2827 l_repv_tbl (i).order_line_id;
2828 x_repv_tbl (l_ptr).success_flag :=
2829 l_repv_tbl (i).success_flag;
2830 x_repv_tbl (l_ptr).source_flag :=
2831 l_repv_tbl (i).source_flag;
2832 x_repv_tbl (l_ptr).order_number :=
2833 l_repv_tbl (i).order_number;
2834 l_ptr := l_ptr + 1;
2835 END LOOP;
2836
2837 l_ptr := l_ptr - 1;
2838 ELSE
2839 x_repv_tbl (l_ptr).ID := l_ordline_rec.ID;
2840 x_repv_tbl (l_ptr).order_id := l_ordline_rec.order_id;
2841 x_repv_tbl (l_ptr).order_line_id :=
2842 l_ordline_rec.order_line_id;
2843 x_repv_tbl (l_ptr).success_flag :=
2844 l_ordline_rec.success_flag;
2845 x_repv_tbl (l_ptr).source_flag := l_ordline_rec.source_flag;
2846 x_repv_tbl (l_ptr).order_number :=
2847 l_ordline_rec.order_number;
2848 END IF;
2849 END LOOP;
2850 END IF;
2851
2852 x_return_status := l_return_status;
2853 fnd_file.put_line (fnd_file.LOG, 'Exiting Get Order Details');
2854 EXCEPTION
2855 WHEN g_exception_halt_validation
2856 THEN
2857 fnd_file.put_line (fnd_file.LOG,
2858 ' Error in Get_Order_details : '
2859 || SQLCODE
2860 || ':'
2861 || SQLERRM
2862 );
2863 okc_api.set_message (g_app_name,
2864 g_unexpected_error,
2865 g_sqlcode_token,
2866 SQLCODE,
2867 g_sqlerrm_token,
2868 SQLERRM
2869 );
2870 WHEN OTHERS
2871 THEN
2872 x_return_status := okc_api.g_ret_sts_unexp_error;
2873 fnd_file.put_line (fnd_file.LOG,
2874 ' Error in Get_Order_Details : '
2875 || SQLCODE
2876 || ':'
2877 || SQLERRM
2878 );
2879 okc_api.set_message (g_app_name,
2880 g_unexpected_error,
2881 g_sqlcode_token,
2882 SQLCODE,
2883 g_sqlerrm_token,
2884 SQLERRM
2885 );
2886 END get_order_details;
2887 BEGIN
2888 --Okc_context.set_okc_org_context (l_order_rec.org_id, NULL ); --mmadhavi commenting for MOAC
2889 l_prog_id := fnd_global.conc_program_id;
2890 l_req_id := fnd_global.conc_request_id;
2891 fnd_file.put_line (fnd_file.LOG, 'conc_prog_id = ' || l_prog_id);
2892 fnd_file.put_line (fnd_file.LOG, 'conc_req_id = ' || l_req_id);
2893 fnd_file.put_line (fnd_file.LOG, 'Source = ' || NVL (p_source, 'Auto'));
2894 SAVEPOINT oks_reprocessing;
2895 fnd_file.put_line (fnd_file.LOG, 'Start of Reprocessing.....');
2896 fnd_file.put_line (fnd_file.LOG,
2897 'The parameter value is ..' || p_option
2898 );
2899 fnd_file.put_line (fnd_file.LOG, 'Calling Get Order Details....');
2900 get_order_details (p_option => p_option,
2901 p_source => p_source,
2902 x_return_status => l_return_status,
2903 x_repv_tbl => l_repv_tbl
2904 );
2905 fnd_file.put_line (fnd_file.LOG,
2906 'Get Order Details : l_return_status = '
2907 || l_return_status
2908 );
2909 l_line_count := l_repv_tbl.COUNT;
2910
2911 IF (l_line_count <= 0)
2912 THEN
2913 fnd_file.put_line (fnd_file.LOG, 'No lines to Reprocess..');
2914 ELSE
2915 fnd_file.put_line (fnd_file.LOG,
2916 'ReProcessing Order Lines... Line count = '
2917 || l_line_count
2918 );
2919
2920 FOR l_count IN 1 .. l_line_count
2921 LOOP
2922
2923 --OM INT User Hook Start Bug# 4462061
2924
2925 --Call out to Pre-Integration
2926 --This is done as part of License Migration
2927 --Call out starts here
2928 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2929 THEN
2930 fnd_log.string(FND_LOG.LEVEL_STATEMENT
2931 ,G_MODULE_CURRENT||'.ORDER_REPROCES'
2932 ,'Before OKS_OMIB_EXTNS_PUB.pre_integration call: ' ||
2933 ' ,p_api_version = '|| '1.0' ||
2934 ' ,p_init_msg_list = ' || 'T' ||
2935 ' ,p_from_integration = OREP' ||
2936 ' ,p_transaction_type = ' || NULL ||
2937 ' ,p_transaction_date = ' || NULL ||
2938 ' ,p_order_line_id = ' || l_repv_tbl(l_count).order_line_id ||
2939 ' ,p_old_instance_id = ' || NULL ||
2940 ' ,p_new_instance_id = ' || NULL);
2941 END IF;
2942
2943 OKS_OMIB_INT_EXTNS_PUB.pre_integration
2944 (p_api_version => 1.0
2945 ,p_init_msg_list => 'T'
2946 ,p_from_integration => 'OREP'
2947 ,p_transaction_type => NULL
2948 ,p_transaction_date => NULL
2949 ,p_order_line_id => l_repv_tbl(l_count).order_line_id
2950 ,p_old_instance_id => NULL
2951 ,p_new_instance_id => NULL
2952 ,x_process_status => l_process_status
2953 ,x_return_status => x_return_status
2954 ,x_msg_count => x_msg_count
2955 ,x_msg_data => x_msg_data);
2956
2957 IF fnd_log.level_event >= fnd_log.g_current_runtime_level
2958 THEN
2959 fnd_log.string(FND_LOG.LEVEL_EVENT
2960 ,G_MODULE_CURRENT||'.IB_INTERFACE'
2961 ,'After OKS_OMIB_INT_EXTNS_PUB.pre_integration Call: ' ||
2962 ' ,x_process_status = ' || l_process_status ||
2963 ' ,x_return_status = ' || x_return_status);
2964 END IF;
2965 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
2966 THEN
2967 RAISE G_EXCEPTION_HALT_VALIDATION;
2968 END IF;
2969 --Call out ends here
2970
2971 IF l_process_status = 'C'
2972 THEN
2973
2974 --OM INT User Hook End
2975
2976 l_upd_tbl.DELETE;
2977 l_conc_rec.conc_request_id := l_req_id;
2978 l_conc_rec.order_line_id := l_repv_tbl (l_count).order_line_id;
2979 l_conc_rec.ID := l_repv_tbl (l_count).ID;
2980 fnd_file.put_line (fnd_file.LOG,
2981 'Updating record with Conc req id'
2982 );
2983 handle_order_error (x_return_status => l_return_status,
2984 p_upd_rec => l_conc_rec
2985 );
2986 fnd_file.put_line (fnd_file.LOG,
2987 'l_return_status = ' || l_return_status
2988 );
2989
2990 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
2991 THEN
2992 RAISE g_exception_halt_validation;
2993 END IF;
2994
2995 l_fulfill := 'Y';
2996 l_order_rec.fqty := 0;
2997 l_order_rec.rolineid := NULL;
2998 l_order_rec.header_id := NULL;
2999
3000 OPEN l_order_csr (l_repv_tbl (l_count).order_line_id);
3001
3002 FETCH l_order_csr
3003 INTO l_order_rec;
3004
3005 IF l_order_csr%NOTFOUND
3006 THEN
3007 fnd_file.put_line (fnd_file.LOG, 'l_order_csr not found ');
3008 l_order_rec.fqty := 0;
3009 END IF;
3010
3011 CLOSE l_order_csr;
3012
3013 okc_context.set_okc_org_context (l_order_rec.org_id, NULL);
3014
3015 --FND_FILE.PUT_LINE (FND_FILE.LOG, 'success_flag = '||NVL(l_repv_tbl(l_count).success_flag,'S'));
3016 IF (NVL (l_repv_tbl (l_count).source_flag, 'S') = 'MANUAL')
3017 THEN
3018 fnd_file.put_line (fnd_file.LOG,
3019 'Order line entered from Reprocesing UI'
3020 );
3021
3022 IF (l_order_rec.fqty <= 0)
3023 THEN
3024 fnd_file.put_line (fnd_file.LOG,
3025 'Order line '
3026 || l_repv_tbl (l_count).order_line_id
3027 || ' not fulfilled'
3028 );
3029 l_upd_tbl (1).ID := l_repv_tbl (l_count).ID;
3030 l_upd_tbl (1).ERROR_TEXT := '#';
3031 fnd_message.set_name ('OKS', 'OKS_REQUEST');
3032 fnd_message.set_token (token => 'ID', VALUE => l_req_id);
3033 l_upd_tbl (1).ERROR_TEXT :=
3034 l_upd_tbl (1).ERROR_TEXT || fnd_message.get_encoded
3035 || '#';
3036 fnd_message.set_name ('OKS', 'OKS_LINE_NOT_FULFILLED');
3037 fnd_message.set_token
3038 (token => 'ORD_LINE',
3039 VALUE => l_repv_tbl (l_count).order_line_id
3040 );
3041 l_upd_tbl (1).ERROR_TEXT :=
3042 l_upd_tbl (1).ERROR_TEXT || fnd_message.get_encoded
3043 || '#';
3044 l_upd_tbl (1).success_flag := 'E';
3045 l_upd_tbl (1).conc_request_id := NULL;
3046 handle_order_error (x_return_status => l_return_status,
3047 p_upd_rec => l_upd_tbl (1)
3048 );
3049
3050 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
3051 THEN
3052 RAISE g_exception_halt_validation;
3053 END IF;
3054
3055 l_fulfill := 'N';
3056 END IF;
3057 END IF;
3058
3059 IF (l_fulfill <> 'N')
3060 THEN
3061 fnd_file.put_line (fnd_file.LOG, 'Order line fulfilled..');
3062
3063 OPEN l_serv_ref_csr (l_repv_tbl (l_count).order_line_id);
3064
3065 FETCH l_serv_ref_csr
3066 INTO l_serv_ref_rec;
3067
3068 CLOSE l_serv_ref_csr;
3069
3070 fnd_file.put_line (fnd_file.LOG,
3071 'Processing Order Line '
3072 || l_repv_tbl (l_count).order_line_id
3073 );
3074 fnd_file.put_line (fnd_file.LOG,
3075 'Service Ref Type Code '
3076 || l_serv_ref_rec.service_reference_type_code
3077 );
3078 fnd_file.put_line (fnd_file.LOG,
3079 'Service line Id '
3080 || l_serv_ref_rec.service_reference_line_id
3081 );
3082 --FND_FILE.PUT_LINE (FND_FILE.LOG, 'Profile option value ' || Fnd_Profile.Value('OKS_CONTRACTS_VALIDATION_SOURCE') );
3083
3084 /*
3085 If Fnd_Profile.Value('OKS_CONTRACTS_VALIDATION_SOURCE') IN ('IB', 'MO') OR
3086 Fnd_Profile.Value('OKS_CONTRACTS_VALIDATION_SOURCE') Is NULL Then
3087
3088 Okc_context.set_okc_org_context (l_order_rec.org_id, NULL );
3089
3090 Elsif Fnd_Profile.Value('OKS_CONTRACTS_VALIDATION_SOURCE') = 'SO' Then
3091
3092 l_organization_id := Null;
3093 If l_order_rec.sold_from_org_id Is Not Null Then
3094 Open l_organization_csr(l_order_rec.sold_from_org_id);
3095 Fetch l_organization_csr into l_organization_id;
3096 Close l_organization_csr;
3097 Else
3098 l_organization_id := Null;
3099 End If;
3100
3101 Okc_context.set_okc_org_context (l_order_rec.org_id, l_organization_id);
3102
3103 Elsif Fnd_Profile.Value('OKS_CONTRACTS_VALIDATION_SOURCE') = 'SH' Then
3104
3105 Okc_context.set_okc_org_context (l_order_rec.org_id, l_order_rec.ship_from_org_id);
3106
3107 End If;
3108 */
3109 l_cp_tbl.DELETE;
3110
3111 IF NVL (l_serv_ref_rec.service_reference_type_code, 'REF_TYPE') =
3112 'ORDER'
3113 THEN ------REF TYPE ORDER
3114 OPEN l_order_csr (l_order_rec.rolineid);
3115
3116 FETCH l_order_csr
3117 INTO l_ref_order_rec;
3118
3119 IF l_order_csr%NOTFOUND
3120 THEN
3121 l_order_rec.fqty := 0;
3122 END IF;
3123
3124 CLOSE l_order_csr;
3125
3126 fnd_file.put_line (fnd_file.LOG, 'ORDER : L_ORDER_CSR ');
3127
3128 IF l_order_rec.header_id = l_ref_order_rec.header_id
3129 THEN -----ORDER HEADER ID EQLS
3130 fnd_file.put_line
3131 (fnd_file.LOG,
3132 'ORDER HEADER ID EQLS : L_PROCESS TRUE '
3133 );
3134 l_process := TRUE;
3135 l_cp_ctr := 1;
3136
3137 /*modified for bug6181908 --fp bug6006309*/
3138 FOR rec IN
3139 l_custprod_csr
3140 (l_serv_ref_rec.service_reference_line_id, okc_context.get_okc_organization_id)
3141 LOOP
3142 fnd_file.put_line (fnd_file.LOG,
3143 'L_CP_TBL CP ID '
3144 || rec.cp_id
3145 || ' Cnt '
3146 || l_cp_ctr
3147 );
3148 l_cp_tbl (l_cp_ctr).cp_id := rec.cp_id;
3149 l_cp_ctr := l_cp_ctr + 1;
3150 END LOOP;
3151
3152 IF l_cp_ctr = 1
3153 THEN
3154 l_process := FALSE;
3155 END IF;
3156 ELSE
3157 l_process := TRUE;
3158 fnd_file.put_line
3159 (fnd_file.LOG,
3160 'ORDER HEADER ID NOT EQLS : L_CUSTPROD '
3161 );
3162 l_cp_ctr := 1;
3163
3164 /*modified for bug6181908 -- fp bug6006309*/
3165 FOR rec IN
3166 l_custprod_csr
3167 (l_serv_ref_rec.service_reference_line_id, okc_context.get_okc_organization_id)
3168 LOOP
3169 fnd_file.put_line (fnd_file.LOG,
3170 'L_CP_TBL CP ID '
3171 || rec.cp_id
3172 || ' Cnt '
3173 || l_cp_ctr
3174 );
3175 l_cp_tbl (l_cp_ctr).cp_id := rec.cp_id;
3176 l_cp_ctr := l_cp_ctr + 1;
3177 END LOOP;
3178
3179 IF l_cp_ctr = 1
3180 THEN
3181 l_process := FALSE;
3182 END IF;
3183 END IF; -----ORDER HEADER ID EQLS
3184 ELSE ------REF TYPE CUST PROD
3185 fnd_file.put_line (fnd_file.LOG, 'ElSE L_PROCESS TRUE ');
3186 l_cp_tbl (1).cp_id :=
3187 l_serv_ref_rec.service_reference_line_id;
3188 l_process := TRUE;
3189 END IF; ------REF TYPE ORDER
3190
3191 /* Bug 2324668
3192 In case Order Capture Integration is run when SFM is down then
3193 Customer product would not exist, and the Service line will
3194 get processed without creating any Contract.Now in case SFM is up
3195 and running , but still if there is a timing issue for fulfillment
3196 of lines then no contract will get created.So the next time
3197 Order Capture is run, the line does'nt get picked and no Contract
3198 is created at all.
3199 To fix this issue, populating the exception queue when the product
3200 is not in IB, so that the line will be picked again.
3201
3202 */
3203 IF NOT l_process
3204 THEN
3205 fnd_file.put_line (fnd_file.LOG, 'Cannot process line ...');
3206 l_upd_tbl (1).ID := l_repv_tbl (l_count).ID;
3207 l_upd_tbl (1).ERROR_TEXT := '#';
3208 fnd_message.set_name ('OKS', 'OKS_REQUEST');
3209 fnd_message.set_token (token => 'ID', VALUE => l_req_id);
3210 l_upd_tbl (1).ERROR_TEXT :=
3211 l_upd_tbl (1).ERROR_TEXT || fnd_message.get_encoded
3212 || '#';
3213 fnd_message.set_name ('OKS', 'OKS_PRODUCT_NOT_FOUND');
3214 l_upd_tbl (1).ERROR_TEXT :=
3215 l_upd_tbl (1).ERROR_TEXT || fnd_message.get_encoded
3216 || '#';
3217 --'Referenced Product not present in the Installed Base';
3218 l_upd_tbl (1).success_flag := 'E';
3219 l_upd_tbl (1).conc_request_id := NULL;
3220 handle_order_error (x_return_status => l_return_status,
3221 p_upd_rec => l_upd_tbl (1)
3222 );
3223
3224 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
3225 THEN
3226 RAISE g_exception_halt_validation;
3227 END IF;
3228 END IF;
3229
3230 IF l_process
3231 THEN
3232 l_k_line_id := NULL;
3233 l_ctr1 := 0;
3234 l_upd_tbl (1).subline_id := NULL;
3235
3236 OPEN l_contract_csr (l_repv_tbl (l_count).order_line_id);
3237
3238 LOOP
3239 l_ctr1 := l_ctr1 + 1;
3240
3241 FETCH l_contract_csr
3242 INTO l_upd_tbl (l_ctr1).subline_id,
3243 l_upd_tbl (l_ctr1).contract_id;
3244
3245 --FND_FILE.PUT_LINE (FND_FILE.LOG, 'cov_id = '||l_upd_tbl(l_ctr1).subline_id);
3246 EXIT WHEN l_contract_csr%NOTFOUND;
3247 END LOOP;
3248
3249 CLOSE l_contract_csr;
3250
3251 fnd_file.put_line (fnd_file.LOG, 'l_ctr1 = ' || l_ctr1);
3252 fnd_file.put_line (fnd_file.LOG,
3253 'DUPLICATE CHECK l_K_LINE_ID '
3254 || NVL (l_upd_tbl (1).subline_id, -12345)
3255 );
3256
3257 IF l_upd_tbl (1).subline_id IS NULL
3258 THEN --Duplication Check
3259 DBMS_TRANSACTION.SAVEPOINT ('OKS_REPROC');
3260 l_reproc_line_rec := l_repv_tbl (l_count);
3261 create_contract (p_reproc_line_rec => l_reproc_line_rec,
3262 x_upd_tbl => l_upd_tbl,
3263 x_return_status => l_return_status
3264 );
3265 fnd_file.put_line (fnd_file.LOG,
3266 'Create_Contract status ::'
3267 || l_return_status
3268 );
3269
3270 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
3271 THEN
3272 l_error_msg := '#';
3273
3274 FOR i IN 1 .. fnd_msg_pub.count_msg
3275 LOOP
3276 fnd_msg_pub.get (p_msg_index => i,
3277 p_encoded => 'T',
3278 p_data => l_msg_data,
3279 p_msg_index_out => l_index
3280 );
3281 l_error_msg := l_error_msg || l_msg_data || '#';
3282 fnd_message.set_encoded (l_msg_data);
3283 l_msg_data := fnd_message.get;
3284 fnd_file.put_line (fnd_file.LOG,
3285 'GET ORDER LINE FAILURE '
3286 || l_msg_data
3287 );
3288 END LOOP;
3289
3290 DBMS_TRANSACTION.rollback_savepoint ('OKS_REPROC');
3291 l_error_temp := '#';
3292 fnd_message.set_name ('OKS', 'OKS_REQUEST');
3293 fnd_message.set_token (token => 'ID',
3294 VALUE => l_req_id
3295 );
3296 l_error_temp :=
3297 l_error_temp || fnd_message.get_encoded;
3298
3299 IF (l_upd_tbl (1).ERROR_TEXT = okc_api.g_miss_char)
3300 THEN
3301 l_upd_tbl (1).ERROR_TEXT :=
3302 l_error_temp || l_error_msg;
3303 l_upd_tbl (1).success_flag := 'E';
3304 ELSE
3305 l_upd_tbl (1).ERROR_TEXT :=
3306 l_error_temp || l_upd_tbl (1).ERROR_TEXT;
3307 END IF;
3308
3309 l_upd_tbl (1).ID := l_repv_tbl (l_count).ID;
3310 l_upd_tbl (1).contract_id := NULL;
3311 l_upd_tbl (1).subline_id := NULL;
3312 l_upd_tbl (1).contract_line_id := NULL;
3313 l_upd_tbl (1).conc_request_id := NULL;
3314 handle_order_error
3315 (x_return_status => l_return_status,
3316 p_upd_rec => l_upd_tbl
3317 (1)
3318 );
3319
3320 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
3321 THEN
3322 RAISE g_exception_halt_validation;
3323 END IF;
3324 ELSE
3325
3326 -- OM INT User Hook Start Bug# 4462061
3327
3328 --Add Post_integration call out as part of code re-structuring for
3329 --license migrations.
3330 --call out starts here
3331
3332 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
3333 THEN
3334 fnd_log.string(FND_LOG.LEVEL_STATEMENT
3335 ,G_MODULE_CURRENT||'.ORDER_REPROCESS'
3336 ,'Before OKS_OMIB_EXTNS_PUB.post_integration call: ' ||
3337 ' ,p_transaction_type = ' || NULL ||
3338 ' ,p_transaction_date = ' || NULL ||
3339 ' ,p_order_line_id = ' || l_upd_tbl(1).order_line_id ||
3340 ' ,p_old_instance_id = ' || NULL ||
3341 ' ,p_new_instance_id = ' || NULL ||
3342 ' ,p_chr_id = ' || l_upd_tbl(1).contract_id ||
3343 ' ,p_topline_id = ' || l_upd_tbl(1).contract_line_id ||
3344 ' ,p_subline_id = ' || l_upd_tbl(1).subline_id);
3345 END IF;
3346 OKS_OMIB_INT_EXTNS_PUB.post_integration
3347 (p_api_version => 1.0
3348 ,p_init_msg_list => 'T'
3349 ,p_from_integration => 'OREP'
3350 ,p_transaction_type => NULL
3351 ,p_transaction_date => NULL
3352 ,p_order_line_id => l_upd_tbl(1).order_line_id
3353 ,p_old_instance_id => NULL
3354 ,p_new_instance_id => NULL
3355 ,p_chr_id => l_upd_tbl(1).contract_id
3356 ,p_topline_id => l_upd_tbl(1).contract_line_id
3357 ,p_subline_id => l_upd_tbl(1).subline_id
3358 ,x_return_status => x_return_status
3359 ,x_msg_count => x_msg_count
3360 ,x_msg_data => x_msg_data);
3361 IF fnd_log.level_event >= fnd_log.g_current_runtime_level
3362 THEN
3363 fnd_log.string(FND_LOG.LEVEL_EVENT
3364 ,G_MODULE_CURRENT||'.IB_INTERFACE'
3365 ,'After OKS_OMIB_INT_EXTNS_PUB.post_integration Call: ' ||
3366 ' ,x_return_status = ' || x_return_status);
3367 END IF;
3368 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
3369 THEN
3370 RAISE G_EXCEPTION_HALT_VALIDATION;
3371 END IF;
3372
3373 --call out ends here
3374
3375 -- OM INT User Hook End
3376
3377 l_error_temp := '#';
3378 fnd_message.set_name ('OKS', 'OKS_REQUEST');
3379 fnd_message.set_token (token => 'ID',
3380 VALUE => l_req_id
3381 );
3382 l_error_temp :=
3383 l_error_temp || fnd_message.get_encoded || '#';
3384 l_upd_tbl (1).ID := l_repv_tbl (l_count).ID;
3385 fnd_message.set_name ('OKS', 'OKS_CONTRACT_SUCCESS');
3386 l_upd_tbl (1).ERROR_TEXT := fnd_message.get_encoded;
3387 --'Contract Successfully created';
3388 l_upd_tbl (1).ERROR_TEXT :=
3389 l_error_temp || l_upd_tbl (1).ERROR_TEXT || '#';
3390 l_upd_tbl (1).success_flag := 'S';
3391 l_upd_tbl (1).conc_request_id := NULL;
3392 handle_order_error
3393 (x_return_status => l_return_status,
3394 p_upd_rec => l_upd_tbl
3395 (1)
3396 );
3397
3398 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
3399 THEN
3400 RAISE g_exception_halt_validation;
3401 END IF;
3402
3403 FOR i IN 2 .. l_upd_tbl.LAST
3404 LOOP
3405 fnd_message.set_name ('OKS', 'OKS_REQUEST');
3406 fnd_message.set_token (token => 'ID',
3407 VALUE => l_req_id
3408 );
3409 l_upd_tbl (i).ERROR_TEXT :=
3410 '#' || fnd_message.get_encoded || '#';
3411 fnd_message.set_name ('OKS',
3412 'OKS_CONTRACT_SUCCESS');
3413 l_upd_tbl (i).ERROR_TEXT :=
3414 l_upd_tbl (i).ERROR_TEXT
3415 || fnd_message.get_encoded
3416 || '#'; --'Contract Successfully created';
3417 l_upd_tbl (i).success_flag := 'S';
3418 l_upd_tbl (i).conc_request_id := NULL;
3419 l_upd_tbl (i).order_line_id :=
3420 l_repv_tbl (l_count).order_line_id;
3421 l_upd_tbl (i).order_number :=
3422 l_repv_tbl (l_count).order_number;
3423 l_upd_tbl (i).order_id :=
3424 l_repv_tbl (l_count).order_id;
3425 l_upd_tbl (i).source_flag :=
3426 l_repv_tbl (l_count).source_flag;
3427 --'ASO';
3428 l_upd_tbl (i).reprocess_yn := 'N';
3429 oks_rep_pub.insert_row
3430 (p_api_version => 1.0,
3431 p_init_msg_list => l_init_msg_list,
3432 x_return_status => l_return_status,
3433 x_msg_count => l_msg_count,
3434 x_msg_data => l_msg_data,
3435 p_repv_rec => l_upd_tbl
3436 (i),
3437 x_repv_rec => l_out_repv_rec
3438 );
3439 fnd_file.put_line (fnd_file.LOG,
3440 'L_return_status :'
3441 || l_return_status
3442 );
3443
3444 IF NOT (l_return_status = fnd_api.g_ret_sts_success
3445 )
3446 THEN
3447 FOR i IN 1 .. fnd_msg_pub.count_msg
3448 LOOP
3449 fnd_msg_pub.get (p_msg_index => -1,
3450 p_encoded => 'F',
3451 p_data => l_msg_data,
3452 p_msg_index_out => l_index
3453 );
3454 fnd_file.put_line
3455 (fnd_file.LOG,
3456 'ORDER_REPROCESS.Insert_row'
3457 || l_msg_data
3458 );
3459 END LOOP;
3460 END IF;
3461 END LOOP;
3462 END IF;
3463 ELSE /* If Duplicate */
3464 l_ctr1 := l_ctr1 - 1;
3465
3466 OPEN l_contract_line_csr (l_upd_tbl (1).subline_id);
3467
3468 FETCH l_contract_line_csr
3469 INTO l_cont_line_id;
3470
3471 CLOSE l_contract_line_csr;
3472
3473 l_upd_tbl (1).contract_line_id := l_cont_line_id;
3474 l_upd_tbl (1).success_flag := 'S';
3475 fnd_message.set_name ('OKS', 'OKS_REQUEST');
3476 fnd_message.set_token (token => 'ID',
3477 VALUE => l_req_id);
3478 l_upd_tbl (1).ERROR_TEXT :=
3479 '#' || fnd_message.get_encoded || '#';
3480 fnd_message.set_name ('OKS', 'OKS_DUPLICATE_ORD_LINE');
3481 l_upd_tbl (1).ERROR_TEXT :=
3482 l_upd_tbl (1).ERROR_TEXT
3483 || fnd_message.get_encoded
3484 || '#';
3485 l_upd_tbl (1).order_line_id :=
3486 l_repv_tbl (l_count).order_line_id;
3487 l_upd_tbl (1).ID := l_repv_tbl (l_count).ID;
3488 l_upd_tbl (1).conc_request_id := NULL;
3489 handle_order_error (x_return_status => l_return_status,
3490 p_upd_rec => l_upd_tbl (1)
3491 );
3492
3493 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
3494 THEN
3495 RAISE g_exception_halt_validation;
3496 END IF;
3497
3498 --FND_FILE.PUT_LINE (FND_FILE.LOG, 'l_ctr1 = '||l_ctr1);
3499 IF (l_ctr1 > 1)
3500 THEN
3501 FOR i IN 2 .. l_ctr1
3502 LOOP
3503 l_upd_tbl (i).contract_line_id := l_cont_line_id;
3504 l_upd_tbl (i).success_flag := 'S';
3505 fnd_message.set_name ('OKS', 'OKS_REQUEST');
3506 fnd_message.set_token (token => 'ID',
3507 VALUE => l_req_id
3508 );
3509 l_upd_tbl (i).ERROR_TEXT :=
3510 '#' || fnd_message.get_encoded || '#';
3511 fnd_message.set_name ('OKS',
3512 'OKS_DUPLICATE_ORD_LINE'
3513 );
3514 l_upd_tbl (i).ERROR_TEXT :=
3515 l_upd_tbl (i).ERROR_TEXT
3516 || fnd_message.get_encoded
3517 || '#';
3518 l_upd_tbl (i).order_line_id :=
3519 l_repv_tbl (l_count).order_line_id;
3520 l_upd_tbl (i).order_number :=
3521 l_repv_tbl (l_count).order_number;
3522 l_upd_tbl (i).order_id :=
3523 l_repv_tbl (l_count).order_id;
3524 l_upd_tbl (i).source_flag :=
3525 l_repv_tbl (l_count).source_flag;
3526 l_upd_tbl (i).conc_request_id := NULL;
3527 l_upd_tbl (i).reprocess_yn := 'N';
3528 oks_rep_pub.insert_row
3529 (p_api_version => 1.0,
3530 p_init_msg_list => l_init_msg_list,
3531 x_return_status => l_return_status,
3532 x_msg_count => l_msg_count,
3533 x_msg_data => l_msg_data,
3534 p_repv_rec => l_upd_tbl
3535 (i),
3536 x_repv_rec => l_out_repv_rec
3537 );
3538 fnd_file.put_line (fnd_file.LOG,
3539 'L_return_status :'
3540 || l_return_status
3541 );
3542
3543 IF NOT (l_return_status = fnd_api.g_ret_sts_success
3544 )
3545 THEN
3546 FOR i IN 1 .. fnd_msg_pub.count_msg
3547 LOOP
3548 fnd_msg_pub.get (p_msg_index => -1,
3549 p_encoded => 'F',
3550 p_data => l_msg_data,
3551 p_msg_index_out => l_index
3552 );
3553 fnd_file.put_line
3554 (fnd_file.LOG,
3555 'ORDER_REPROCESS.Insert_row'
3556 || l_msg_data
3557 );
3558 END LOOP;
3559 END IF;
3560 END LOOP;
3561 END IF;
3562 END IF; -- Duplicate Check
3563 END IF; -- l_process is true
3564 END IF;
3565
3566 -- OM INT USER HOOK START Bug# 4462061
3567
3568 ELSE -- else of l_process_status from pre_integration
3569
3570 --Call out to Post starts here if l_process_status <> 'C'(dont want to continue with
3571 --the existing logic above
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_REPROCESS'
3576 ,'Before OKS_OMIB_EXTNS_PUB.post_integration call: ' ||
3577 ' ,p_transaction_type = ' || NULL ||
3578 ' ,p_transaction_date = ' || NULL ||
3579 ' ,p_order_line_id = ' || l_repv_tbl(l_count).order_line_id ||
3580 ' ,p_old_instance_id = ' || NULL ||
3581 ' ,p_new_instance_id = ' || NULL ||
3582 ' ,p_chr_id = ' || NULL ||
3583 ' ,p_topline_id = ' || NULL ||
3584 ' ,p_subline_id = ' || NULL);
3585 END IF;
3586
3587 OKS_OMIB_INT_EXTNS_PUB.post_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 ,p_chr_id => NULL
3597 ,p_topline_id => NULL
3598 ,p_subline_id => NULL
3599 ,x_return_status => x_return_status
3600 ,x_msg_count => x_msg_count
3601 ,x_msg_data => x_msg_data);
3602 IF fnd_log.level_event >= fnd_log.g_current_runtime_level
3603 THEN
3604 fnd_log.string(FND_LOG.LEVEL_EVENT
3605 ,G_MODULE_CURRENT||'.IB_INTERFACE'
3606 ,'After OKS_OMIB_INT_EXTNS_PUB.post_integration Call: ' ||
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 END IF; --IF of l_process status check
3614 --Call out to Post ends here
3615 -- OM INT USER HOOK END
3616
3617 END LOOP; -- end loop on l_line_count tbl
3618 END IF; -- For l_line_count
3619
3620 EXCEPTION
3621 WHEN g_exception_halt_validation
3622 THEN
3623 x_return_status := l_return_status;
3624 fnd_file.put_line
3625 (fnd_file.LOG,
3626 ' Error in Order Reprocess - l_return_status = '
3627 || l_return_status
3628 );
3629 WHEN OTHERS
3630 THEN
3631 fnd_file.put_line (fnd_file.LOG,
3632 'In OTHERS Exception of Order_Reprocess'
3633 );
3634 x_return_status := okc_api.g_ret_sts_unexp_error;
3635 fnd_file.put_line (fnd_file.LOG,
3636 ' Error in Order Reprocess : '
3637 || SQLCODE
3638 || ':'
3639 || SQLERRM
3640 );
3641 okc_api.set_message (g_app_name,
3642 g_unexpected_error,
3643 g_sqlcode_token,
3644 SQLCODE,
3645 g_sqlerrm_token,
3646 SQLERRM
3647 );
3648 END order_reprocess;
3649
3650 PROCEDURE oks_order_purge (
3651 errbuf OUT NOCOPY VARCHAR2,
3652 retcode OUT NOCOPY NUMBER
3653 )
3654 IS
3655 l_return_status VARCHAR2 (1) := okc_api.g_ret_sts_success;
3656 l_msg_count NUMBER;
3657 l_msg_data VARCHAR2 (2000);
3658 l_user_id NUMBER;
3659 l_del_rec oks_rep_pvt.repv_rec_type;
3660 l_repv_tbl oks_rep_pvt.repv_tbl_type;
3661 l_index NUMBER;
3662 l_line_count NUMBER;
3663 l_api_version CONSTANT NUMBER := 1.0;
3664 l_init_msg_list VARCHAR2 (1) DEFAULT fnd_api.g_false;
3665
3666 PROCEDURE get_order_lines (
3667 x_return_status OUT NOCOPY VARCHAR2,
3668 x_msg_count OUT NOCOPY NUMBER,
3669 x_msg_data OUT NOCOPY VARCHAR2,
3670 x_repv_tbl OUT NOCOPY oks_rep_pvt.repv_tbl_type
3671 )
3672 IS
3673 CURSOR l_order_line_csr
3674 IS
3675 SELECT ID
3676 FROM oks_reprocessing_v
3677 WHERE success_flag = 'S';
3678
3679 l_repv_tbl oks_rep_pvt.repv_tbl_type;
3680 l_return_status VARCHAR2 (1) := okc_api.g_ret_sts_success;
3681 l_ordline_rec oks_rep_pvt.repv_rec_type;
3682 l_ptr NUMBER;
3683 BEGIN
3684 fnd_file.put_line (fnd_file.LOG, 'Inside procedure Get Order lines');
3685 l_ptr := 0;
3686
3687 FOR l_ordline_rec IN l_order_line_csr
3688 LOOP
3689 l_ptr := l_ptr + 1;
3690 x_repv_tbl (l_ptr).ID := l_ordline_rec.ID;
3691 --X_Repv_tbl(l_ptr).order_line_id := l_ordline_rec.order_line_id;
3692 END LOOP;
3693
3694 x_return_status := l_return_status;
3695 fnd_file.put_line (fnd_file.LOG, 'Exiting Get Order Details');
3696 EXCEPTION
3697 WHEN OTHERS
3698 THEN
3699 x_return_status := okc_api.g_ret_sts_unexp_error;
3700 fnd_file.put_line (fnd_file.LOG,
3701 ' Error in Get_Order_Details : '
3702 || SQLCODE
3703 || ':'
3704 || SQLERRM
3705 );
3706 okc_api.set_message (g_app_name,
3707 g_unexpected_error,
3708 g_sqlcode_token,
3709 SQLCODE,
3710 g_sqlerrm_token,
3711 SQLERRM
3712 );
3713 END get_order_lines;
3714 BEGIN
3715 fnd_file.put_line (fnd_file.LOG, 'Start of OKS_ORDER_PURGE_PVT...');
3716 l_user_id := fnd_global.user_id;
3717 fnd_file.put_line (fnd_file.LOG, 'User Id : ' || TO_CHAR (l_user_id));
3718 get_order_lines (x_return_status => l_return_status,
3719 x_msg_count => l_msg_count,
3720 x_msg_data => l_msg_data,
3721 x_repv_tbl => l_repv_tbl
3722 );
3723 fnd_file.put_line (fnd_file.LOG,
3724 'Get Order Lines: l_return_status = '
3725 || l_return_status
3726 );
3727
3728 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
3729 THEN
3730 FOR i IN 1 .. fnd_msg_pub.count_msg
3731 LOOP
3732 fnd_msg_pub.get (p_msg_index => -1,
3733 p_encoded => 'F',
3734 p_data => l_msg_data,
3735 p_msg_index_out => l_index
3736 );
3737 fnd_file.put_line (fnd_file.LOG,
3738 'GET ORDER LINES: ' || l_msg_data);
3739 END LOOP;
3740
3741 RAISE g_exception_halt_validation;
3742 END IF;
3743
3744 l_line_count := l_repv_tbl.COUNT;
3745
3746 IF (l_line_count <= 0)
3747 THEN
3748 fnd_file.put_line (fnd_file.LOG, 'No lines to Purge..');
3749 ELSE
3750 fnd_file.put_line (fnd_file.LOG,
3751 'Purging Order Lines... Line count = '
3752 || l_line_count
3753 );
3754
3755 FOR l_count IN 1 .. l_line_count
3756 LOOP
3757 l_del_rec.ID := l_repv_tbl (l_count).ID;
3758 oks_rep_pub.delete_row (p_api_version => l_api_version,
3759 p_init_msg_list => l_init_msg_list,
3760 x_return_status => l_return_status,
3761 x_msg_count => l_msg_count,
3762 x_msg_data => l_msg_data,
3763 p_repv_rec => l_del_rec
3764 );
3765 END LOOP;
3766 END IF;
3767
3768 errbuf := '';
3769 retcode := 0;
3770 EXCEPTION
3771 WHEN OTHERS
3772 THEN
3773 ROLLBACK;
3774 -- Retrieve error message into errbuf
3775 errbuf := SQLERRM;
3776 retcode := 2;
3777 fnd_file.put_line (fnd_file.LOG,
3778 'Oracle Error Code is -' || TO_CHAR (SQLCODE)
3779 );
3780 fnd_file.put_line (fnd_file.LOG,
3781 'Oracle Error Message is -'
3782 || SUBSTR (SQLERRM, 1, 512)
3783 );
3784 END oks_order_purge;
3785
3786 PROCEDURE migrate_aso_queue (
3787 errbuf OUT NOCOPY VARCHAR2,
3788 retcode OUT NOCOPY NUMBER
3789 )
3790 IS
3791 l_init_msg_list VARCHAR2 (1) DEFAULT fnd_api.g_false;
3792 l_commit VARCHAR2 (1) DEFAULT fnd_api.g_false;
3793 l_return_status VARCHAR2 (1);
3794 l_msg_count NUMBER;
3795 l_msg_data VARCHAR2 (2000);
3796 l_wait NUMBER DEFAULT DBMS_AQ.no_wait;
3797 l_no_more_messages VARCHAR2 (240);
3798 l_header_rec oe_order_pub.header_rec_type;
3799 l_old_header_rec oe_order_pub.header_rec_type;
3800 l_header_adj_tbl oe_order_pub.header_adj_tbl_type;
3801 l_old_header_adj_tbl oe_order_pub.header_adj_tbl_type;
3802 l_header_price_att_tbl oe_order_pub.header_price_att_tbl_type;
3803 l_old_header_price_att_tbl oe_order_pub.header_price_att_tbl_type;
3804 l_header_adj_att_tbl oe_order_pub.header_adj_att_tbl_type;
3805 l_old_header_adj_att_tbl oe_order_pub.header_adj_att_tbl_type;
3806 l_header_adj_assoc_tbl oe_order_pub.header_adj_assoc_tbl_type;
3807 l_old_header_adj_assoc_tbl oe_order_pub.header_adj_assoc_tbl_type;
3808 l_header_scredit_tbl oe_order_pub.header_scredit_tbl_type;
3809 l_old_header_scredit_tbl oe_order_pub.header_scredit_tbl_type;
3810 l_line_tbl oe_order_pub.line_tbl_type;
3811 l_old_line_tbl oe_order_pub.line_tbl_type;
3812 l_line_adj_tbl oe_order_pub.line_adj_tbl_type;
3813 l_old_line_adj_tbl oe_order_pub.line_adj_tbl_type;
3814 l_line_price_att_tbl oe_order_pub.line_price_att_tbl_type;
3815 l_old_line_price_att_tbl oe_order_pub.line_price_att_tbl_type;
3816 l_line_adj_att_tbl oe_order_pub.line_adj_att_tbl_type;
3817 l_old_line_adj_att_tbl oe_order_pub.line_adj_att_tbl_type;
3818 l_line_adj_assoc_tbl oe_order_pub.line_adj_assoc_tbl_type;
3819 l_old_line_adj_assoc_tbl oe_order_pub.line_adj_assoc_tbl_type;
3820 l_line_scredit_tbl oe_order_pub.line_scredit_tbl_type;
3821 l_old_line_scredit_tbl oe_order_pub.line_scredit_tbl_type;
3822 l_lot_serial_tbl oe_order_pub.lot_serial_tbl_type;
3823 l_old_lot_serial_tbl oe_order_pub.lot_serial_tbl_type;
3824 l_action_request_tbl oe_order_pub.request_tbl_type;
3825 l_oe_line_rec oe_order_pub.line_rec_type;
3826 l_dequeue_mode VARCHAR2 (240) DEFAULT DBMS_AQ.remove;
3827 l_navigation VARCHAR2 (240)
3828 DEFAULT DBMS_AQ.next_message;
3829 l_repv_rec oks_rep_pvt.repv_rec_type;
3830 l_out_repv_rec oks_rep_pvt.repv_rec_type;
3831 l_request_id NUMBER;
3832 --General Variables
3833 l_ctr NUMBER;
3834 l_oldline_count NUMBER;
3835 l_newline_count NUMBER;
3836 l_user_id NUMBER;
3837 l_index NUMBER;
3838 index1 NUMBER;
3839 err_msg VARCHAR2 (1000) := '';
3840 l_hdrid NUMBER := NULL;
3841 dup_val VARCHAR2 (1) := '';
3842 l_ord_num NUMBER;
3843 l_exists VARCHAR2 (1);
3844
3845 CURSOR l_order_hdr_csr (p_ordlineid NUMBER)
3846 IS
3847 SELECT oh.header_id, oh.order_number
3848 FROM oe_order_lines_all ol, oe_order_headers_all oh
3849 WHERE ol.line_id = (p_ordlineid)
3850 AND oh.org_id = okc_context.get_okc_org_id
3851 AND oh.header_id = ol.header_id;
3852
3853 CURSOR l_order_csr (p_ordlineid NUMBER)
3854 IS
3855 SELECT org_id, ship_from_org_id, sold_from_org_id,
3856 NVL (fulfilled_quantity, 0) fqty,
3857 service_reference_line_id rolineid, header_id
3858 FROM okx_order_lines_v
3859 WHERE id1 = (p_ordlineid);
3860
3861 CURSOR check_ordline_exists (p_ordlineid NUMBER)
3862 IS
3863 SELECT 'x'
3864 FROM oks_reprocessing
3865 WHERE order_line_id = p_ordlineid;
3866
3867 --Fix for bug 3492335
3868 CURSOR is_ib_trackable (
3869 l_ref_order_line_id NUMBER,
3870 l_organization_id NUMBER
3871 )
3872 IS
3873 SELECT comms_nl_trackable_flag
3874 FROM mtl_system_items_b
3875 WHERE inventory_item_id = (SELECT inventory_item_id
3876 FROM oe_order_lines_all
3877 WHERE line_id = l_ref_order_line_id)
3878 AND organization_id = l_organization_id;
3879
3880 l_order_rec l_order_csr%ROWTYPE;
3881 l_api_version CONSTANT NUMBER := 1.0;
3882 x_msg_count NUMBER;
3883 x_msg_data VARCHAR2 (2000);
3884 ib_flag VARCHAR2 (1);
3885 --
3886 aso_handle_exception EXCEPTION;
3887 aso_handle_normal EXCEPTION;
3888 BEGIN
3889 SAVEPOINT oks_migrate_aso_queue;
3890 fnd_file.put_line (fnd_file.LOG, 'Start of OC_interface...');
3891 l_user_id := fnd_global.user_id;
3892 fnd_file.put_line (fnd_file.LOG, 'User Id : ' || TO_CHAR (l_user_id));
3893 -- Set policy context to ALL
3894 mo_global.set_policy_context ('B', NULL);
3895
3896 LOOP
3897 l_oldline_count := 0;
3898 l_newline_count := 0;
3899 aso_order_feedback_pub.get_notice
3900 (p_api_version => 1.0,
3901 p_init_msg_list => l_init_msg_list,
3902 p_commit => l_commit,
3903 x_return_status => l_return_status,
3904 x_msg_count => l_msg_count,
3905 x_msg_data => l_msg_data,
3906 p_app_short_name => 'OKS',
3907 p_wait => l_wait,
3908 x_no_more_messages => l_no_more_messages,
3909 x_header_rec => l_header_rec,
3910 x_old_header_rec => l_old_header_rec,
3911 x_header_adj_tbl => l_header_adj_tbl,
3912 x_old_header_adj_tbl => l_old_header_adj_tbl,
3913 x_header_price_att_tbl => l_header_price_att_tbl,
3914 x_old_header_price_att_tbl => l_old_header_price_att_tbl,
3915 x_header_adj_att_tbl => l_header_adj_att_tbl,
3916 x_old_header_adj_att_tbl => l_old_header_adj_att_tbl,
3917 x_header_adj_assoc_tbl => l_header_adj_assoc_tbl,
3918 x_old_header_adj_assoc_tbl => l_old_header_adj_assoc_tbl,
3919 x_header_scredit_tbl => l_header_scredit_tbl,
3920 x_old_header_scredit_tbl => l_old_header_scredit_tbl,
3921 x_line_tbl => l_line_tbl,
3922 x_old_line_tbl => l_old_line_tbl,
3923 x_line_adj_tbl => l_line_adj_tbl,
3924 x_old_line_adj_tbl => l_old_line_adj_tbl,
3925 x_line_price_att_tbl => l_line_price_att_tbl,
3926 x_old_line_price_att_tbl => l_old_line_price_att_tbl,
3927 x_line_adj_att_tbl => l_line_adj_att_tbl,
3928 x_old_line_adj_att_tbl => l_old_line_adj_att_tbl,
3929 x_line_adj_assoc_tbl => l_line_adj_assoc_tbl,
3930 x_old_line_adj_assoc_tbl => l_old_line_adj_assoc_tbl,
3931 x_line_scredit_tbl => l_line_scredit_tbl,
3932 x_old_line_scredit_tbl => l_old_line_scredit_tbl,
3933 x_lot_serial_tbl => l_lot_serial_tbl,
3934 x_old_lot_serial_tbl => l_old_lot_serial_tbl,
3935 x_action_request_tbl => l_action_request_tbl
3936 );
3937
3938 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
3939 THEN
3940 FOR i IN 1 .. fnd_msg_pub.count_msg
3941 LOOP
3942 fnd_msg_pub.get (p_msg_index => -1,
3943 p_encoded => 'F',
3944 p_data => l_msg_data,
3945 p_msg_index_out => l_index
3946 );
3947 fnd_file.put_line (fnd_file.LOG, l_msg_data);
3948 END LOOP;
3949
3950 ROLLBACK TO oks_migrate_aso_queue;
3951 RETURN;
3952 END IF;
3953
3954 l_newline_count := l_line_tbl.COUNT;
3955 fnd_file.put_line (fnd_file.LOG,
3956 'Lines to process = ' || l_newline_count
3957 );
3958 EXIT WHEN l_no_more_messages = fnd_api.g_true;
3959
3960 IF l_newline_count <= 0
3961 THEN --Order Line Record Found
3962 fnd_file.put_line (fnd_file.LOG, 'No lines to insert');
3963 ELSE
3964 fnd_file.put_line (fnd_file.LOG,
3965 'Processing the order lines... line count='
3966 || TO_CHAR (l_newline_count)
3967 );
3968
3969 FOR l_count IN 1 .. l_newline_count
3970 LOOP
3971 l_exists := 'y';
3972 fnd_file.put_line (fnd_file.LOG,
3973 'Processing Order Line '
3974 || l_line_tbl (l_count).line_id
3975 );
3976 fnd_file.put_line
3977 (fnd_file.LOG,
3978 'Service Ref Type Code '
3979 || l_line_tbl (l_count).service_reference_type_code
3980 );
3981 fnd_file.put_line
3982 (fnd_file.LOG,
3983 'Service line Id '
3984 || l_line_tbl (l_count).service_reference_line_id
3985 );
3986 fnd_file.put_line
3987 (fnd_file.LOG,
3988 'Profile option value '
3989 || fnd_profile.VALUE
3990 ('OKS_CONTRACTS_VALIDATION_SOURCE')
3991 );
3992
3993 OPEN check_ordline_exists (l_line_tbl (l_count).line_id);
3994
3995 FETCH check_ordline_exists
3996 INTO l_exists;
3997
3998 IF check_ordline_exists%NOTFOUND
3999 THEN
4000 l_exists := 'y';
4001 END IF;
4002
4003 CLOSE check_ordline_exists;
4004
4005 --Check Delayed Service
4006 IF (l_exists <> 'x')
4007 THEN
4008 fnd_file.put_line (fnd_file.LOG,
4009 'Order line not present already...'
4010 );
4011
4012 IF NVL (l_line_tbl (l_count).service_reference_type_code,
4013 'REF_TYPE'
4014 ) IN ('CUSTOMER_PRODUCT', 'ORDER')
4015 AND l_line_tbl (l_count).service_reference_line_id IS NOT NULL
4016 THEN --Checking Delayed Service
4017 --Check Fulfillment
4018 l_order_rec.fqty := 0;
4019 l_order_rec.rolineid := NULL;
4020 l_order_rec.header_id := NULL;
4021
4022 OPEN l_order_csr (l_line_tbl (l_count).line_id);
4023
4024 FETCH l_order_csr
4025 INTO l_order_rec;
4026
4027 IF l_order_csr%NOTFOUND
4028 THEN
4029 l_order_rec.fqty := 0;
4030 END IF;
4031
4032 CLOSE l_order_csr;
4033
4034 fnd_file.put_line (fnd_file.LOG,
4035 'Fulfillment Quantity '
4036 || l_order_rec.fqty
4037 );
4038 fnd_file.put_line (fnd_file.LOG,
4039 'Order Org Id '
4040 || l_order_rec.org_id
4041 );
4042 fnd_file.put_line (fnd_file.LOG,
4043 'Order Organization Id '
4044 || l_order_rec.ship_from_org_id
4045 );
4046 fnd_file.put_line (fnd_file.LOG,
4047 'Sold from Org Id '
4048 || l_order_rec.sold_from_org_id
4049 );
4050 okc_context.set_okc_org_context (l_order_rec.org_id,
4051 NULL);
4052 fnd_file.put_line (fnd_file.LOG,
4053 'org_context - '
4054 || TO_CHAR (okc_context.get_okc_org_id)
4055 );
4056 fnd_file.put_line
4057 (fnd_file.LOG,
4058 'organization_context - '
4059 || TO_CHAR
4060 (okc_context.get_okc_organization_id)
4061 );
4062
4063 --Check IB Trackable flag
4064 IF NVL (l_line_tbl (l_count).service_reference_type_code,
4065 'REF_TYPE'
4066 ) = 'ORDER'
4067 THEN
4068 OPEN is_ib_trackable
4069 (l_line_tbl (l_count).service_reference_line_id,
4070 okc_context.get_okc_organization_id
4071 );
4072
4073 FETCH is_ib_trackable
4074 INTO ib_flag;
4075
4076 CLOSE is_ib_trackable;
4077 ELSE
4078 ib_flag := 'Y';
4079 END IF;
4080
4081 IF NVL (ib_flag, 'N') = 'Y'
4082 THEN
4083 IF l_order_rec.fqty > 0
4084 THEN
4085 OPEN l_order_hdr_csr (l_line_tbl (l_count).line_id);
4086
4087 FETCH l_order_hdr_csr
4088 INTO l_hdrid, l_ord_num;
4089
4090 IF l_order_hdr_csr%NOTFOUND
4091 THEN
4092 fnd_file.put_line (fnd_file.LOG,
4093 ' Invalid Order line ID'
4094 );
4095
4096 CLOSE l_order_hdr_csr;
4097 ELSE
4098 CLOSE l_order_hdr_csr;
4099
4100 fnd_file.put_line (fnd_file.LOG,
4101 'Order Header ID ' || l_hdrid
4102 );
4103 l_repv_rec.order_id := l_hdrid;
4104 l_repv_rec.order_line_id :=
4105 l_line_tbl (l_count).line_id;
4106 l_repv_rec.order_number := l_ord_num;
4107 l_repv_rec.success_flag := 'N';
4108 l_repv_rec.source_flag := 'ASO';
4109 l_repv_rec.reprocess_yn := 'Y';
4110 SAVEPOINT before_insert;
4111 oks_rep_pub.insert_row
4112 (p_api_version => 1.0,
4113 p_init_msg_list => l_init_msg_list,
4114 x_return_status => l_return_status,
4115 x_msg_count => l_msg_count,
4116 x_msg_data => l_msg_data,
4117 p_repv_rec => l_repv_rec,
4118 x_repv_rec => l_out_repv_rec
4119 );
4120 fnd_file.put_line
4121 (fnd_file.LOG,
4122 'OKS_REP_PUB - RETURN STATUS : '
4123 || l_return_status
4124 );
4125
4126 IF NOT (l_return_status =
4127 fnd_api.g_ret_sts_success
4128 )
4129 THEN
4130 FOR i IN 1 .. fnd_msg_pub.count_msg
4131 LOOP
4132 fnd_msg_pub.get
4133 (p_msg_index => -1,
4134 p_encoded => 'F',
4135 p_data => l_msg_data,
4136 p_msg_index_out => l_index
4137 );
4138
4139 SELECT INSTR (l_msg_data,
4140 'ORA-00001',
4141 1,
4142 1
4143 )
4144 INTO index1
4145 FROM DUAL;
4146
4147 IF (index1 > 0)
4148 THEN
4149 dup_val := 'Y';
4150 EXIT;
4151 END IF;
4152
4153 fnd_file.put_line
4154 (fnd_file.LOG,
4155 'oks_rep_pub.insert_row: '
4156 || l_msg_data
4157 );
4158 END LOOP;
4159
4160 IF (dup_val <> 'Y')
4161 THEN
4162 RAISE g_exception_halt_validation;
4163 END IF;
4164
4165 l_return_status := fnd_api.g_ret_sts_success;
4166 END IF;
4167 END IF;
4168 END IF;
4169 END IF;
4170 END IF;
4171 ELSE
4172 fnd_file.put_line (fnd_file.LOG, 'Duplicate Order Line');
4173 END IF;
4174 END LOOP;
4175 END IF;
4176 END LOOP;
4177
4178 errbuf := '';
4179 retcode := 0;
4180 COMMIT WORK;
4181 fnd_file.put_line (fnd_file.LOG, 'Order Capture INT Program finished.');
4182 EXCEPTION
4183 WHEN g_exception_halt_validation
4184 THEN
4185 ROLLBACK TO before_insert;
4186 WHEN OTHERS
4187 THEN
4188 ROLLBACK TO before_insert;
4189 -- Retrieve error message into errbuf
4190 errbuf := SQLERRM;
4191 retcode := 2;
4192 fnd_file.put_line (fnd_file.LOG,
4193 'Oracle Error Code is -' || TO_CHAR (SQLCODE)
4194 );
4195 fnd_file.put_line (fnd_file.LOG,
4196 'Oracle Error Message is -'
4197 || SUBSTR (SQLERRM, 1, 512)
4198 );
4199 END migrate_aso_queue;
4200 END oks_ocint_pub;