· 8 years ago · Aug 24, 2018, 07:44 AM
1create or replace
2PACKAGE BODY XXECS_MWS_BOM_CREATION_PKG
3AS
4/* $Header: XXECS_MWS_BOM_CREATION_PKG.pkb 1.4 2017/06/16 16:21:38EDT Narender Karola (A81287) Exp $ */
5/***************************************************************************************************************************************
6* Type : Package Body *
7* Name : xxecs_mws_bom_creation_pkg *
8* *
9* Procedures : XXECS_BOM_CREATION_PROC *
10* *
11* *
12* *
13* Purpose : Definition of procedures and functions belonging to the *
14* custom package xxecs_mws_bom_creation_pkg *
15* *
16* Company : *
17 * Created Date : 11-oct-2016 *
18 * Last Reviewed By : *
19 * Last Reviewed Date : *
20 * Modified Date Modified By Version Reviewed By Reviewed Date Modification Type Modification Detail *
21 * ------------- ------------ -------- --------------- -------------- ----------------- -------------------- *
22 * 11-oct-2016 Paramdeep 1.0 Sreejith 11-oct-2016 O Created *
23 * 22-may-2017 Narender Karola 1.1 Sreejith 22-may-2017 O Setting Ato Model Item Quantity to 1 *
24 ***************************************************************************************************************************/
25-- This variable holds the request id of the concurrent program
26--
27 g_num_request_id NUMBER (15) := fnd_global.conc_request_id;
28--
29-- This variable holds the user_id of the current user
30--
31 g_num_user_id NUMBER (15) := fnd_global.user_id;
32--
33-- This variable holds the login_id of the current user
34--
35 g_num_login_id NUMBER (15) := fnd_global.conc_login_id;
36--
37-- This variable holds the Program Application Id
38--
39 g_num_program_appl_id NUMBER (15) := fnd_global.prog_appl_id;
40--
41-- This variable holds the Program Id
42--
43 g_num_program_id NUMBER (15) := fnd_global.conc_program_id;
44--
45-- This variable holds the System Date
46--
47 g_dat_sys_date DATE := SYSDATE;
48--
49-- This varible holds the application_code
50--
51 g_chr_application_code VARCHAR2 (15) := 'XXECS_DIST';
52 g_chr_debug VARCHAR2 (1)
53 := NVL (fnd_profile.VALUE ('XXECS_DEBUG_FLAG')
54 ,'N');
55 g_num_operating_unit_id NUMBER
56 := TO_NUMBER (fnd_profile.VALUE ('ORG_ID'));
57 g_chr_success_code CONSTANT VARCHAR2 (1) := '0';
58 g_chr_warning_code CONSTANT VARCHAR2 (1) := '1';
59 g_chr_failure_code CONSTANT VARCHAR2 (1) := '2';
60
61
62 --OM RULE for Generic
63
64 PROCEDURE xxecs_if62b_update_line_proc (
65 out_chr_errbuf OUT VARCHAR2
66 ,out_chr_retcode OUT VARCHAR2
67 ,in_num_header_id NUMBER
68 ,in_chr_order_type VARCHAR2)
69 IS
70 /********************************************************************************************************************
71 * Type : Procedure *
72 * Name : XXECS_IF62B_UPDATE_LINE_PROC *
73 * Input Parameters : in_num_header_id *
74 * *
75 * Output Parameters : out_chr_errbuf, out_chr_retcode *
76 * Purpose : This procedure to derive Line type *
77 * Company : Infosys Technologies Ltd. *
78 * Created By : Infosys(Kunal ) *
79 * Created Date : 20-OCT-2008 *
80 * Last Reviewed By : Infosys(Kunal) *
81 * Last Reviewed Date : 20-OCT-2008 *
82 *********************************************************************************************************************
83 * Modified Date Modified By Version Reviewed By Reviewed Date Modification Type Modification Details *
84 * ------------- ----------- -------- --------------- -------------- ----------------- --------------------------*
85 * 20-OCT-2008 Infosys(Kunal) 1.0 Infosys(Kunal) 20-OCT-2008 O Created *
86 ********************************************************************************************************************/
87 TYPE tbl_header_id IS TABLE OF xxecs_mws_config_line_ad.header_id%TYPE
88 INDEX BY BINARY_INTEGER;
89
90 TYPE tbl_line_id IS TABLE OF xxecs_mws_config_line_ad.line_id%TYPE
91 INDEX BY BINARY_INTEGER;
92
93 TYPE tbl_line_item_id IS TABLE OF xxecs_mws_config_line_ad.line_item_id%TYPE
94 INDEX BY BINARY_INTEGER;
95
96 TYPE tbl_line_type_id IS TABLE OF xxecs_mws_config_line_ad.line_type_id%TYPE
97 INDEX BY BINARY_INTEGER;
98
99 TYPE tbl_config_line_type IS TABLE OF xxecs_mws_config_line_ad.config_line_type%TYPE
100 INDEX BY BINARY_INTEGER;
101
102 TYPE tbl_sub_line_id IS TABLE OF xxecs_mws_config_subline_ad.sub_line_id%TYPE
103 INDEX BY BINARY_INTEGER;
104
105 TYPE tbl_bom_flag IS TABLE OF xxecs_mws_config_subline_ad.bom_flag%TYPE
106 INDEX BY BINARY_INTEGER;
107
108 TYPE tbl_process_flag IS TABLE OF xxecs_mws_config_subline_ad.process_status%TYPE
109 INDEX BY BINARY_INTEGER;
110
111 TYPE tbl_quantity IS TABLE OF xxecs_mws_config_line_ad.quantity%TYPE
112 INDEX BY BINARY_INTEGER;
113
114 TYPE l_config_line_rec IS RECORD (
115 header_id tbl_header_id
116 ,line_id tbl_line_id
117 ,line_item_id tbl_line_item_id
118 ,line_type_id tbl_line_type_id
119 ,config_line_type tbl_config_line_type
120 ,process_status tbl_process_flag
121 );
122
123 TYPE l_config_subline_rec IS RECORD (
124 header_id tbl_header_id
125 ,line_id tbl_line_id
126 ,sub_line_id tbl_sub_line_id
127 ,line_type_id tbl_config_line_type
128 ,bom_flag tbl_bom_flag
129 ,process_status tbl_process_flag
130 ,quantity tbl_quantity
131 );
132
133 l_config_update_line_rec l_config_line_rec;
134 l_config_update_subline_rec l_config_subline_rec;
135
136 CURSOR c_line_data
137 IS
138 SELECT eqcl.header_id
139 ,eqcl.line_id
140 ,eqcl.line_item_id
141 ,eqcl.quantity
142 FROM xxecs_mws_config_line_ad eqcl
143 WHERE eqcl.header_id = in_num_header_id
144 --AND eqcl.process_status = 1
145 AND NVL (eqcl.removal_flag
146 ,'N') = 'N'
147 AND EXISTS (SELECT 'x'
148 FROM xxecs_mws_config_subline_ad eqcs
149 WHERE eqcs.line_id = eqcl.line_id);
150
151 CURSOR c_pick_ship_line_data
152 IS
153 SELECT eqcl.header_id
154 ,eqcl.line_id
155 ,eqcl.line_item_id
156 ,eqcl.quantity
157 FROM xxecs_mws_config_line_ad eqcl
158 WHERE eqcl.header_id = in_num_header_id
159 --AND eqcl.process_status = 1
160 AND NVL (eqcl.removal_flag
161 ,'N') = 'N';
162
163 CURSOR c_subline_data (
164 p_num_line_id NUMBER)
165 IS
166 SELECT eqcs.header_id
167 ,eqcs.line_id
168 ,eqcs.sub_line_id
169 ,eqcs.subline_feature_qty
170 FROM xxecs_mws_config_subline_ad eqcs
171 WHERE header_id = in_num_header_id
172 AND line_id = p_num_line_id;
173
174 /*variables Declaration*/
175 l_chr_errmsg VARCHAR2 (4000);
176 l_num_line_count NUMBER := 0;
177 l_num_subline_count NUMBER := 0;
178 l_chr_config_type xxecs_mws_config_line_ad.config_line_type%TYPE;
179 l_chr_bom_flag xxecs_mws_config_subline_ad.bom_flag%TYPE;
180 l_num_line_type_id xxecs_mws_config_subline_ad.line_type_id%TYPE;
181 l_chr_org_id VARCHAR2 (100)
182 := fnd_profile.VALUE ('ORG_ID');
183 exc_error_exit EXCEPTION;
184 BEGIN
185 IF ( in_chr_order_type = 'INTEGRATION'
186 OR in_chr_order_type = 'MIXED')
187 THEN
188 /*Fetching line_type_id from Lookup XXECS_DIST_VCF_ORDER_LINE_TYPE*/
189 BEGIN
190 SELECT attribute2
191 INTO l_num_line_type_id
192 FROM fnd_lookup_values_vl flv
193 WHERE flv.lookup_type = 'XXECS_DIST_VCF_ORDER_LINE_TYPE'
194 AND TO_NUMBER (flv.attribute1) = l_chr_org_id
195 AND flv.enabled_flag = 'Y'
196 AND flv.description = 'INTEGRATION'
197 AND NVL (flv.start_date_active
198 ,SYSDATE) <= SYSDATE
199 AND NVL (flv.end_date_active
200 ,SYSDATE) >= SYSDATE;
201 EXCEPTION
202 WHEN OTHERS
203 THEN
204 l_chr_errmsg :=
205 'Error while fetching the line type id from XXECS_DIST_VCF_ORDER_LINE_TYPE for Program'
206 || ' xxecs_update_integ_line_proc'
207 || ' : '
208 || SQLERRM;
209 RAISE exc_error_exit;
210 END;
211
212 l_chr_config_type := 'INTEGRATION';
213 l_chr_bom_flag := 'Y';
214 ELSIF (in_chr_order_type = 'PICKSHIP')
215 THEN
216 /*Fetching line_type_id from Lookup XXECS_DIST_VCF_ORDER_LINE_TYPE*/
217 BEGIN
218 SELECT attribute2
219 INTO l_num_line_type_id
220 FROM fnd_lookup_values_vl flv
221 WHERE flv.lookup_type = 'XXECS_DIST_VCF_ORDER_LINE_TYPE'
222 AND TO_NUMBER (flv.attribute1) = l_chr_org_id
223 AND flv.enabled_flag = 'Y'
224 AND flv.description = 'PICKSHIP'
225 AND NVL (flv.start_date_active
226 ,SYSDATE) <= SYSDATE
227 AND NVL (flv.end_date_active
228 ,SYSDATE) >= SYSDATE;
229 EXCEPTION
230 WHEN OTHERS
231 THEN
232 l_chr_errmsg :=
233 'Error while fetching the line type id from XXECS_DIST_VCF_ORDER_LINE_TYPE for Program'
234 || ' xxecs_update_integ_line_proc'
235 || ' : '
236 || SQLERRM;
237 RAISE exc_error_exit;
238 END;
239
240 l_chr_config_type := 'PICKSHIP';
241 l_chr_bom_flag := 'N';
242 -- Changes start for version 1.3
243
244 --
245 -- fetching the line type details for REBILL Orders
246 --
247 ELSIF (in_chr_order_type = 'REBILL')
248 THEN
249 /*Fetching line_type_id from Lookup XXECS_DIST_VCF_ORDER_LINE_TYPE*/
250 BEGIN
251 SELECT attribute2
252 INTO l_num_line_type_id
253 FROM fnd_lookup_values_vl flv -- Modified for version 1.4
254 WHERE flv.lookup_type = 'XXECS_DIST_VCF_ORDER_LINE_TYPE'
255 AND TO_NUMBER (flv.attribute1) = l_chr_org_id
256 AND flv.enabled_flag = 'Y'
257 AND flv.description = 'REBILL'
258 AND NVL (flv.start_date_active
259 ,SYSDATE) <= SYSDATE
260 AND NVL (flv.end_date_active
261 ,SYSDATE) >= SYSDATE;
262 EXCEPTION
263 WHEN OTHERS
264 THEN
265 l_chr_errmsg :=
266 'Error while fetching the line type id from XXECS_DIST_VCF_ORDER_LINE_TYPE for Program'
267 || ' xxecs_update_integ_line_proc'
268 || ' : '
269 || SQLERRM;
270 RAISE exc_error_exit;
271 END;
272
273 l_chr_config_type := 'REBILL';
274 l_chr_bom_flag := 'N';
275 -- changes end for version 1.3
276 ELSE
277 /*Fetching line_type_id from Lookup XXECS_DIST_VCF_ORDER_LINE_TYPE*/
278 BEGIN
279 SELECT attribute2
280 INTO l_num_line_type_id
281 FROM fnd_lookup_values_vl flv
282 WHERE flv.lookup_type = 'XXECS_DIST_VCF_ORDER_LINE_TYPE'
283 AND TO_NUMBER (flv.attribute1) = l_chr_org_id
284 AND flv.enabled_flag = 'Y'
285 AND flv.description = 'DROPSHIP'
286 AND NVL (flv.start_date_active
287 ,SYSDATE) <= SYSDATE
288 AND NVL (flv.end_date_active
289 ,SYSDATE) >= SYSDATE;
290 EXCEPTION
291 WHEN OTHERS
292 THEN
293 l_chr_errmsg :=
294 'Error while fetching the line type id from XXECS_DIST_VCF_ORDER_LINE_TYPE for Program'
295 || ' xxecs_update_integ_line_proc'
296 || ' : '
297 || SQLERRM;
298 RAISE exc_error_exit;
299 END;
300
301 l_chr_config_type := 'DROPSHIP';
302 l_chr_bom_flag := 'N';
303 END IF;
304
305 /*Validation based on Order Header Type
306 IF (in_chr_order_type = 'INTEGRATION')
307 THEN
308 l_chr_config_type := 'INTEGRATION';
309 ELSIF (in_chr_order_type ='MIXED')
310 THEN
311 l_chr_config_type := 'MI';
312
313 /*Updating Line table*/
314 /*BEGIN
315 UPDATE xxecs_mws_config_line_ad
316 SET line_type_id = l_num_line_type_id
317 ,config_line_type = 'INTEGRATION'
318 ,process_status = 2
319 ,
320 WHERE header_id = in_num_header_id;
321 EXCEPTION
322 WHEN OTHERS
323 THEN
324 l_chr_errmsg :=
325 'Error while updating records into xxecs_mws_config_subline_ad table : '
326 || SQLERRM;
327 RAISE exc_error_exit;
328 NULL;
329 END;*/
330
331 /*Updating Subline table*/
332 /* BEGIN
333 UPDATE xxecs_mws_config_subline_ad
334 SET line_type_id = l_num_line_type_id
335 ,bom_flag = 'Y'
336 ,process_status = 2
337 WHERE header_id = in_num_header_id;
338 EXCEPTION
339 WHEN OTHERS
340 THEN
341 l_chr_errmsg :=
342 'Error while updating records into xxecs_mws_config_subline_ad table : '
343 || SQLERRM;
344 RAISE exc_error_exit;
345 END;
346
347 ELSIF(in_chr_order_type = 'MIXED')--IF (in_chr_order_type = 'INTEGRATION')
348 THEN */
349 IF (in_chr_order_type <> 'PICKSHIP')
350 THEN
351 FOR c_line_data_rec IN c_line_data
352 LOOP
353 l_num_line_count := l_num_line_count + 1;
354 l_config_update_line_rec.header_id (l_num_line_count) :=
355 c_line_data_rec.header_id;
356 l_config_update_line_rec.line_id (l_num_line_count) :=
357 c_line_data_rec.line_id;
358 l_config_update_line_rec.config_line_type (l_num_line_count) :=
359 l_chr_config_type;
360 l_config_update_line_rec.line_type_id (l_num_line_count) :=
361 l_num_line_type_id;
362 l_config_update_line_rec.process_status (l_num_line_count) :=
363 2;
364
365 FOR c_subline_data_rec IN
366 c_subline_data (c_line_data_rec.line_id)
367 LOOP
368 l_num_subline_count := l_num_subline_count + 1;
369 l_config_update_subline_rec.header_id
370 (l_num_subline_count) :=
371 c_subline_data_rec.header_id;
372 l_config_update_subline_rec.line_id (l_num_subline_count) :=
373 c_subline_data_rec.line_id;
374 l_config_update_subline_rec.sub_line_id
375 (l_num_subline_count) :=
376 c_subline_data_rec.sub_line_id;
377 l_config_update_subline_rec.line_type_id
378 (l_num_subline_count) :=
379 l_num_line_type_id;
380 l_config_update_subline_rec.bom_flag (l_num_subline_count) :=
381 l_chr_bom_flag;
382 l_config_update_subline_rec.process_status
383 (l_num_subline_count) :=
384 2;
385
386 IF ( l_chr_config_type = 'PICKSHIP'
387 OR l_chr_config_type = 'DROPSHIP')
388 THEN
389 l_config_update_subline_rec.quantity
390 (l_num_subline_count) :=
391 NULL;
392 ELSE
393 l_config_update_subline_rec.quantity
394 (l_num_subline_count) :=
395 c_subline_data_rec.subline_feature_qty;
396 END IF;
397 END LOOP;
398 END LOOP;
399 END IF;
400
401 /* IF (in_chr_order_type = 'PICKSHIP')
402 THEN
403 FOR c_line_pick_data_rec IN c_pick_ship_line_data
404 LOOP
405 l_num_line_count := l_num_line_count + 1;
406 l_config_update_line_rec.header_id (l_num_line_count) :=
407 c_line_pick_data_rec.header_id;
408 l_config_update_line_rec.line_id (l_num_line_count) :=
409 c_line_pick_data_rec.line_id;
410 l_config_update_line_rec.config_line_type (l_num_line_count) :=
411 l_chr_config_type;
412 l_config_update_line_rec.line_type_id (l_num_line_count) :=
413 l_num_line_type_id;
414 l_config_update_line_rec.process_status (l_num_line_count) :=
415 2;
416
417 FOR c_subline_data_rec IN
418 c_subline_data (c_line_pick_data_rec.line_id)
419 LOOP
420 l_num_subline_count := l_num_subline_count + 1;
421 l_config_update_subline_rec.header_id
422 (l_num_subline_count) :=
423 c_subline_data_rec.header_id;
424 l_config_update_subline_rec.line_id (l_num_subline_count) :=
425 c_subline_data_rec.line_id;
426 l_config_update_subline_rec.sub_line_id
427 (l_num_subline_count) :=
428 c_subline_data_rec.sub_line_id;
429 l_config_update_subline_rec.line_type_id
430 (l_num_subline_count) :=
431 l_num_line_type_id;
432 l_config_update_subline_rec.bom_flag (l_num_subline_count) :=
433 l_chr_bom_flag;
434 l_config_update_subline_rec.process_status
435 (l_num_subline_count) :=
436 2;
437
438 IF ( l_chr_config_type = 'PICKSHIP'
439 OR l_chr_config_type = 'DROPSHIP')
440 THEN
441 l_config_update_subline_rec.quantity
442 (l_num_subline_count) :=
443 NULL;
444 ELSE
445 l_config_update_subline_rec.quantity
446 (l_num_subline_count) :=
447 c_subline_data_rec.subline_feature_qty;
448 END IF;
449 END LOOP;
450 END LOOP;
451 END IF;*/ --no need in MWS paramdsa
452
453 -- Changes start for version 1.3
454
455 --
456 -- Assigning the line detail to PL/SQL tables for rebill orders.
457 --
458 /* IF (in_chr_order_type = 'REBILL')
459 THEN
460 FOR c_line_pick_data_rec IN c_pick_ship_line_data
461 LOOP
462 l_num_line_count := l_num_line_count + 1;
463 l_config_update_line_rec.header_id (l_num_line_count) :=
464 c_line_pick_data_rec.header_id;
465 l_config_update_line_rec.line_id (l_num_line_count) :=
466 c_line_pick_data_rec.line_id;
467 l_config_update_line_rec.config_line_type (l_num_line_count) :=
468 l_chr_config_type;
469 l_config_update_line_rec.line_type_id (l_num_line_count) :=
470 l_num_line_type_id;
471 l_config_update_line_rec.process_status (l_num_line_count) :=
472 2;
473
474 FOR c_subline_data_rec IN
475 c_subline_data (c_line_pick_data_rec.line_id)
476 LOOP
477 l_num_subline_count := l_num_subline_count + 1;
478 l_config_update_subline_rec.header_id
479 (l_num_subline_count) :=
480 c_subline_data_rec.header_id;
481 l_config_update_subline_rec.line_id (l_num_subline_count) :=
482 c_subline_data_rec.line_id;
483 l_config_update_subline_rec.sub_line_id
484 (l_num_subline_count) :=
485 c_subline_data_rec.sub_line_id;
486 l_config_update_subline_rec.line_type_id
487 (l_num_subline_count) :=
488 l_num_line_type_id;
489 l_config_update_subline_rec.bom_flag (l_num_subline_count) :=
490 l_chr_bom_flag;
491 l_config_update_subline_rec.process_status
492 (l_num_subline_count) :=
493 2;
494 l_config_update_subline_rec.quantity (l_num_subline_count) :=
495 c_subline_data_rec.subline_feature_qty;
496 END LOOP;
497 END LOOP;
498 END IF;*/ --no need for MWS paramdsa
499
500 -- Changes end for version 1.3
501 BEGIN
502 FORALL l_num_loop_count IN 1 .. l_num_line_count
503 UPDATE xxecs_mws_config_line_ad
504 SET line_type_id =
505 l_config_update_line_rec.line_type_id
506 (l_num_loop_count)
507 ,config_line_type =
508 l_config_update_line_rec.config_line_type
509 (l_num_loop_count)
510 ,process_status =
511 l_config_update_line_rec.process_status
512 (l_num_loop_count)
513 WHERE header_id =
514 l_config_update_line_rec.header_id
515 (l_num_loop_count)
516 AND line_id =
517 l_config_update_line_rec.line_id (l_num_loop_count);
518 EXCEPTION
519 WHEN OTHERS
520 THEN
521 l_chr_errmsg :=
522 'Error while updating records into xxecs_mws_config_subline_ad table : '
523 || SQLERRM;
524 RAISE exc_error_exit;
525 NULL;
526 END;
527
528 BEGIN
529 FORALL l_num_sub_count IN 1 .. l_num_subline_count
530 UPDATE xxecs_mws_config_subline_ad
531 SET line_type_id =
532 l_config_update_subline_rec.line_type_id
533 (l_num_sub_count)
534 ,bom_flag =
535 l_config_update_subline_rec.bom_flag
536 (l_num_sub_count)
537 ,process_status =
538 l_config_update_subline_rec.process_status
539 (l_num_sub_count)
540 ,rollup_qty =
541 l_config_update_subline_rec.quantity
542 (l_num_sub_count)
543 WHERE header_id =
544 l_config_update_subline_rec.header_id
545 (l_num_sub_count)
546 AND line_id =
547 l_config_update_subline_rec.line_id
548 (l_num_sub_count)
549 AND sub_line_id =
550 l_config_update_subline_rec.sub_line_id
551 (l_num_sub_count);
552 EXCEPTION
553 WHEN OTHERS
554 THEN
555 l_chr_errmsg :=
556 'Error while updating records into xxecs_mws_config_subline_ad table : '
557 || SQLERRM;
558 RAISE exc_error_exit;
559 END;
560
561 --END IF;
562 COMMIT;
563 out_chr_retcode := g_chr_success_code;
564 EXCEPTION
565 WHEN exc_error_exit
566 THEN
567 NULL;
568 out_chr_errbuf := l_chr_errmsg;
569 out_chr_retcode := g_chr_failure_code;
570 WHEN OTHERS
571 THEN
572 out_chr_retcode := g_chr_failure_code;
573 out_chr_errbuf := 'Unexpected Error : ' || SQLERRM;
574 NULL;
575 END xxecs_if62b_update_line_proc;
576
577
578--OM Rule Proc ends
579
580
581
582 PROCEDURE xxecs_bom_creation_proc (
583 out_chr_errbuff OUT VARCHAR2
584 ,out_chr_retcode OUT VARCHAR2
585 ,in_num_header_id IN NUMBER)
586 IS
587/***************************************************************************************************************************************
588* Type : Procedure *
589* Name : XXECS_BOM_CREATION_PROC *
590* Input Parameters : 1. in_num_header_id *
591* *
592* Purpose : Procedure to create the BOM for configured items in Bills of material *
593* *
594* Company : Infosys Technologies Ltd. *
595* Created By : Infosys (Paramdeep Singh Aneja) *
596* Created Date : 02-JAN-2008 *
597* Last Reviewed By : Infosys (Deepak Sharma) *
598* Last Reviewed Date : 12-JAN-2008 *
599****************************************************************************************************************************************
600* Modified Date Modified By Version Reviewed By Reviewed Date Modification Type Modification Details *
601* ------------- ----------- -------- --------------- -------------- ----------------- -------------------------- *
602* 11-JAN-2008 Infosys(Paramdeep) 1.0 Infosys (Deepak ) 12-JAN-2008 O Created. *
603* 15-MAY-2008 Infosys(Kunal) 1.1 Infosys(Kunal) 15-MAY-2008 M Modified to resolve issues. *
604* 26-MAY-2008 Infosys(Kunal) 1.2 Infosys(Kunal) 27-MAY-2008 M Modified to add logic for *
605* CR-306. *
606* 06-22-2015 Binay 1.3 Arrow Extension Team Add ORGANIZATION_ID *
607****************************************************************************************************************************************/
608----------------------------------------------------------------------------------------------------------------------------------------
609--
610-- LOCAL VARIABLES DECLARATION SECTION BEGINS
611--
612 l_num_organization_id NUMBER;
613 --:= fnd_profile.VALUE ('XXECS_DIST_BOM_ORGANIZATION'); --Commented by Binay 1.3
614 -- Added in version 1.1
615 --fnd_profile.VALUE ('XXECS_DIST_ITEM_ORGANIZATION'); --Removed in version 1.1
616 l_num_component_seq_count NUMBER := 10;
617 l_num_sql_req_id NUMBER;
618 l_num_header_id NUMBER := in_num_header_id;
619 l_num_line_count NUMBER := 0;
620 l_num_subline_count NUMBER := 0;
621 l_num_item_count NUMBER;
622 l_chr_def_sub_inv VARCHAR2 (100);
623 l_num_request_id NUMBER;
624 l_bool_wait_return BOOLEAN;
625 l_num_interval NUMBER := 1;
626 l_num_assembly_type NUMBER := 1;
627 l_chr_transaction_type VARCHAR2 (20) := 'CREATE';
628 l_num_process_flag NUMBER := 1;
629 l_num_operation_seq_num NUMBER := 10;
630 l_num_chk_exist_line NUMBER := 0;
631 l_num_supply_type NUMBER;
632 l_num_bom_count NUMBER := 0;
633 l_num_min_transfer_qty NUMBER := 0;
634 --Added in version 1.2 for CR-306
635 l_num_count_point_type NUMBER := 1;
636 --Added in version 1.2 for CR-306
637 l_num_backflush_flag NUMBER := 1;
638 --Added in version 1.2 for CR-306
639 l_num_option_dep_flag NUMBER := 2;
640 --Added in version 1.2 for CR-306
641 l_chr_depart_id VARCHAR2 (150);
642 --Added in version 1.2 for CR-306
643--variable to hold department id
644 l_chr_resource_id VARCHAR2 (150);
645 --Added in version 1.2 for CR-306
646--variable to hold resource id
647 l_chr_dept_code bom_departments.department_code%TYPE;
648 --Added in version 1.2 for CR-306
649--variable to hold department code
650 l_num_bom_op_seq NUMBER;
651 --Added in version 1.2 for CR-306
652 l_dte_disable_date DATE := SYSDATE + 5475;
653 --Added in version 1.2 for CR-306
654 exc_error_exit EXCEPTION;
655 l_chr_error_code VARCHAR2 (4000) := '';
656 l_chr_retcod VARCHAR2 (1000) := '';
657--max wait time, in secs; here, set to 1 Day
658 l_num_max_wait NUMBER := 60 * 60 * 24;
659 l_count NUMBER := 0;
660 l_num_line_type_id NUMBER;
661 l_num_prev_line_id NUMBER;
662 l_num_curr_line_id NUMBER;
663 l_num_count NUMBER := 0;
664 l_chr_phase VARCHAR2 (80);
665 l_num_optional NUMBER := 1;
666--OUT variable; request phase
667 l_chr_status VARCHAR2 (80);
668--OUT variable; request completion status
669 l_chr_dev_phase VARCHAR2 (30);
670--OUT variable; request phase (dev)
671 l_chr_dev_status VARCHAR2 (30);
672--OUT variable; request completion status(dev)
673 l_chr_message VARCHAR2 (1000);
674--OUT variable; request completion message
675 l_num_conc_request_id NUMBER := 0;
676
677--
678--Cursor to fetch the Supply Sub Inventory
679--
680 CURSOR c_def_sub_inv
681 IS
682 SELECT default_pull_supply_subinv
683 FROM wip_parameters
684 WHERE organization_id = l_num_organization_id;
685
686--
687--Cursor to fetch the Order Line Type ID
688--
689/* CURSOR c_order_line_type
690IS
691 SELECT attribute2
692 FROM fnd_lookup_values_vl
693 WHERE lookup_type = 'XXECS_DIST_VENCFG_LINE_TYPE'
694 AND enabled_flag = 'Y'
695 AND UPPER (description) = 'INTEGRATION'
696 AND attribute1 = g_num_operating_unit_id
697 AND NVL (start_date_active
698 , SYSDATE - 1) < SYSDATE
699 AND NVL (end_date_active
700 , SYSDATE + 1) > SYSDATE;
701*/
702--
703--Cursor to check the existance of assemblty Item Id
704--
705 CURSOR c_bom_assembly_item (
706 p_num_assembly_item_id NUMBER)
707 IS
708 SELECT COUNT (1) item_count
709 FROM bom_bill_of_materials
710 WHERE organization_id = l_num_organization_id
711 AND assembly_item_id = p_num_assembly_item_id;
712
713--
714--Cursor to fetch the Line details on the basi
715--
716 CURSOR c_line_data
717 IS
718 SELECT eqcl.header_id
719 ,eqcl.line_id
720 ,eqcl.line_item_id
721 ,eqcl.ato_model_item_id
722 ,eqcl.line_type_id
723 ,eqcl.quantity
724 ,1 supply_type
725 FROM xxecs_mws_config_line_ad eqcl
726 WHERE NVL (eqcl.bom_created_flag
727 ,'N') = 'N'
728 AND eqcl.header_id = l_num_header_id
729 AND NVL (eqcl.removal_flag
730 ,'N') = 'N';
731 /* AND NOT EXISTS (
732 SELECT ottt.transaction_type_id line_type_id
733 FROM oe_transaction_types_tl ottt
734 ,fnd_lookup_values_vl flv
735 WHERE flv.lookup_type = 'XXECS_DIST_VENCFG_DROPSHIP'
736 AND flv.enabled_flag = 'Y'
737 AND ottt.NAME = flv.description
738 AND flv.tag = 'DROPSHIP'
739 AND eqcl.line_type_id = ottt.transaction_type_id
740 AND NVL (flv.start_date_active
741 , SYSDATE - 1) < SYSDATE
742 AND NVL (flv.end_date_active
743 , SYSDATE + 1) > SYSDATE)
744 AND EXISTS (
745 SELECT 1
746 FROM fnd_lookup_values_vl flv
747 WHERE flv.lookup_type = 'XXECS_DIST_VENCFG_LINE_TYPE'
748 AND flv.enabled_flag = 'Y'
749 AND UPPER (flv.description) IN
750 ('INTEGRATION', 'MIXED')
751 -- 'PICKSHIP')--Modified in Version 1.1
752 AND ( eqcl.line_type_id = flv.attribute3
753 OR eqcl.line_type_id = flv.attribute4
754 OR eqcl.line_type_id = flv.attribute5
755 --Added in Version 1.1
756 )
757 AND attribute1 = eqcl.org_id
758 AND NVL (start_date_active
759 , SYSDATE - 1) < SYSDATE
760 AND NVL (end_date_active
761 , SYSDATE + 1) > SYSDATE);
762*/
763--
764--Cursor to fetch the BOM details after the BOM Program is submitted
765--
766 CURSOR c_bom_created_data
767 IS
768 SELECT bici.process_flag
769 ,bici.attribute11
770 ,bici.attribute12
771 ,bici.component_item_id
772 ,eqcs.header_id
773 FROM bom_inventory_comps_interface bici
774 ,xxecs_mws_config_subline_ad eqcs
775 WHERE bici.attribute11 = eqcs.line_id
776 AND bici.attribute12 = eqcs.sub_line_id
777 AND eqcs.header_id = l_num_header_id
778 -- AND bici.request_id = l_num_request_id /*Commented By Dhana For Performance*/
779 AND bom_flag = 'Y'
780 AND NVL (eqcs.removal_flag
781 ,'N') = 'N'
782 UNION ALL
783 SELECT bici.process_flag
784 ,bici.attribute11
785 ,bici.attribute12
786 ,bici.component_item_id
787 ,eqcl.header_id
788 FROM bom_inventory_comps_interface bici
789 ,xxecs_mws_config_line_ad eqcl
790 WHERE bici.attribute11 = eqcl.line_id
791 AND bici.attribute12 IS NULL
792 -- AND bici.request_id = l_num_request_id /*Commented By Dhana For Performance*/
793 AND eqcl.header_id = l_num_header_id
794 AND NVL (eqcl.bom_created_flag
795 ,'N') = 'N'
796 AND NVL (eqcl.removal_flag
797 ,'N') = 'N'
798 ORDER BY attribute11;
799
800/* AND NVL (eqcl.bom_created_flag,'N') = 'N';*/
801
802 --
803--Cursor to fetch the Subline Level Details
804--
805 CURSOR c_subline_data (
806 p_num_line_id NUMBER)
807 IS
808 SELECT header_id
809 ,line_id
810 ,sub_line_id
811 ,line_item_id
812 ,rollup_qty
813 ,1 supply_type -- 1 for Push Supply type
814 FROM xxecs_mws_config_subline_ad
815 WHERE line_id = p_num_line_id
816 AND header_id = l_num_header_id
817 AND NVL (removal_flag
818 ,'N') = 'N'
819 AND bom_flag = 'Y'
820 AND rollup_qty > 0; --Added by Kunal 15-OCT-2008
821
822--
823--Cursor to fetch the the datails of Items For which BOm is to be deleted
824--
825 CURSOR c_bom_deletion_data
826 IS
827 SELECT l.ato_model_item_id
828 ,l.bom_created_flag
829 ,h.org_id
830 FROM xxecs_mws_config_line_ad l,
831 xxecs_mws_config_hdr_ad h
832 WHERE l.header_id = l_num_header_id
833 AND l.header_id = h.header_id
834 AND line_item_id IS NOT NULL
835 AND ato_model_item_id IS NOT NULL
836 -- AND request_id = g_num_request_id commented in MWS -- need to test and recheck
837 AND NVL (removal_flag
838 ,'N') = 'N';
839
840 TYPE tbl_assembly_item_id IS TABLE OF bom_bill_of_mtls_interface.assembly_item_id%TYPE
841 INDEX BY BINARY_INTEGER;
842
843 TYPE tbl_process_flag IS TABLE OF bom_bill_of_mtls_interface.process_flag%TYPE
844 INDEX BY BINARY_INTEGER;
845
846 TYPE tbl_component_item_id IS TABLE OF bom_inventory_comps_interface.component_item_id%TYPE
847 INDEX BY BINARY_INTEGER;
848
849 TYPE tbl_component_quantity IS TABLE OF bom_inventory_comps_interface.component_quantity%TYPE
850 INDEX BY BINARY_INTEGER;
851
852 TYPE tbl_item_num IS TABLE OF bom_inventory_comps_interface.item_num%TYPE
853 INDEX BY BINARY_INTEGER;
854
855 TYPE tbl_operation_seq_num IS TABLE OF bom_inventory_comps_interface.operation_seq_num%TYPE
856 INDEX BY BINARY_INTEGER;
857
858 TYPE tbl_supply_subinventory IS TABLE OF bom_inventory_comps_interface.supply_subinventory%TYPE
859 INDEX BY BINARY_INTEGER;
860
861 TYPE tbl_attribute11 IS TABLE OF bom_inventory_comps_interface.attribute11%TYPE
862 INDEX BY BINARY_INTEGER;
863
864 TYPE tbl_attribute12 IS TABLE OF bom_inventory_comps_interface.attribute12%TYPE
865 INDEX BY BINARY_INTEGER;
866
867 TYPE tbl_wip_supply_type IS TABLE OF bom_inventory_comps_interface.wip_supply_type%TYPE
868 INDEX BY BINARY_INTEGER;
869
870 TYPE tbl_bom_created_flag IS TABLE OF xxecs_mws_config_line_ad.bom_created_flag%TYPE
871 INDEX BY BINARY_INTEGER;
872
873 TYPE tbl_line_id IS TABLE OF xxecs_mws_config_line_ad.line_id%TYPE
874 INDEX BY BINARY_INTEGER;
875
876 TYPE assembly_items_rec IS RECORD (
877 assembly_item_id tbl_assembly_item_id
878 );
879
880 TYPE component_items_rec IS RECORD (
881 assembly_item_id tbl_assembly_item_id
882 ,component_item_id tbl_component_item_id
883 ,item_num tbl_item_num
884 ,attribute11 tbl_attribute11
885 ,attribute12 tbl_attribute12
886 ,component_quantity tbl_component_quantity
887 ,wip_supply_type tbl_wip_supply_type
888 );
889
890 TYPE ecs_bom_created_rec IS RECORD (
891 process_flag tbl_process_flag
892 ,attribute11 tbl_attribute11
893 ,attribute12 tbl_attribute12
894 ,component_item_id tbl_component_item_id
895 );
896
897 TYPE ecs_bom_created_flag_rec IS RECORD (
898 bom_created_flag tbl_bom_created_flag
899 ,line_id tbl_line_id
900 );
901
902 l_component_items_rec component_items_rec;
903 l_ecs_bom_created_rec ecs_bom_created_rec;
904 l_ecs_bom_created_flag_rec ecs_bom_created_flag_rec;
905 l_assembly_items_rec assembly_items_rec;
906
907
908 -- START ADDED in version 1.3
909 l_chr_email_address VARCHAR2(500);
910 l_chr_mesg VARCHAR2(32767);
911 l_chr_error_code1 VARCHAR2 (4000) := '';
912 l_chr_retcod1 VARCHAR2 (1000) := '';
913 l_chr_config_name VARCHAR2(2000) := '';
914 l_chr_crlf VARCHAR2 (2) := CHR (13) || CHR (10);
915 -- END ADDED in version 1.3
916 --
917 --Start of procedure XXECS_BOM_CREATION_PROC
918 --
919 BEGIN
920 fnd_file.put_line
921 (fnd_file.LOG
922 ,'************ASCENT BOM Creation Program************');
923 fnd_file.new_line (fnd_file.LOG
924 ,1);
925 fnd_file.put_line (fnd_file.LOG
926 ,'Input Parameters:');
927 fnd_file.put_line (fnd_file.LOG
928 , 'Config Header id (in_num_header_id):'
929 || in_num_header_id);
930 fnd_file.new_line (fnd_file.LOG
931 ,2);
932
933 IF g_chr_debug = 'Y'
934 THEN
935 fnd_file.put_line (fnd_file.LOG
936 , 'Starting Processing for Config Header id:'
937 || in_num_header_id);
938 END IF;
939
940 IF g_chr_debug = 'Y'
941 THEN
942 fnd_file.put_line
943 (fnd_file.LOG
944 ,'Starting Item Creation program to create ATO items for the lines');
945 END IF;
946
947 --<BEGIN 1.3 ADD ORGANIZATION_ID by BINAY>--
948 BEGIN
949 SELECT ORGANIZATION_ID
950 INTO l_num_organization_id
951 FROM xxecs_mws_config_hdr_ad
952 WHERE HEADER_ID = l_num_header_id;
953 EXCEPTION
954 WHEN NO_DATA_FOUND
955 THEN
956 out_chr_errbuff :=
957 'No Data Found while fetching record for ORGANIZATION_ID: '
958 || l_num_organization_id;
959 out_chr_retcode := g_chr_failure_code;
960 RAISE exc_error_exit;
961 WHEN OTHERS
962 THEN
963 out_chr_errbuff :=
964 'Error Occured while fetching record for ORGANIZATION_ID:'
965 || SQLERRM;
966 out_chr_retcode := g_chr_failure_code;
967 RAISE exc_error_exit;
968 END;
969
970 --<END 1.3 ADD ORGANIZATION_ID BY BINAY>--
971
972 l_num_organization_id := 263; --paramdsa hard coded remove it asap
973 BEGIN
974
975 xxecs_if62b_update_line_proc (
976 l_chr_error_code
977 ,l_chr_retcod
978 ,l_num_header_id
979 ,'INTEGRATION');
980
981 EXCEPTION
982 WHEN OTHERS
983 THEN
984 out_chr_errbuff :=
985 'Unexpected error occurs while calling XXECS_IF62B_UPDATE_LINE_PROC'
986 || SQLERRM;
987 out_chr_retcode := g_chr_failure_code;
988 END;
989
990
991 BEGIN
992 xxecs_mws_create_ato_item_pkg.xxecs_inv_create_ato_item_proc
993 (l_chr_error_code
994 ,l_chr_retcod
995 ,l_num_header_id);
996 EXCEPTION
997 WHEN OTHERS
998 THEN
999 out_chr_errbuff :=
1000 'Unexpected error occurs while calling XXECS_INV_CREATE_ATO_ITEM_PKG.XXECS_INV_CREATE_ATO_ITEM_PROC'
1001 || SQLERRM;
1002 out_chr_retcode := g_chr_failure_code;
1003 END;
1004
1005 --
1006 --Fetching Flush-back Inventory
1007 --
1008 FOR c_def_sub_inv_rec IN c_def_sub_inv
1009 LOOP
1010 l_chr_def_sub_inv := c_def_sub_inv_rec.default_pull_supply_subinv;
1011
1012 IF g_chr_debug = 'Y'
1013 THEN
1014 fnd_file.put_line (fnd_file.LOG
1015 , 'Default Subinv value:'
1016 || l_chr_def_sub_inv);
1017 END IF;
1018 END LOOP;
1019
1020 /*--
1021 --Fetching Line Type Id
1022 --
1023 FOR c_order_line_type_rec IN c_order_line_type
1024 LOOP
1025 l_num_line_type_id := c_order_line_type_rec.attribute2;
1026
1027 IF g_chr_debug = 'Y'
1028 THEN
1029 fnd_file.put_line (fnd_file.LOG
1030 , 'Order Line Type Id is :'
1031 || l_num_line_type_id);
1032 END IF;
1033 END LOOP;*/
1034
1035 --
1036 --Fetching Line Level data
1037 --
1038 FOR c_line_data_rec IN c_line_data
1039 LOOP
1040 IF g_chr_debug = 'Y'
1041 THEN
1042 fnd_file.put_line (fnd_file.LOG
1043 , 'Processing the Line Record with'
1044 || 'Line id: '
1045 || c_line_data_rec.line_id
1046 || ',Line Item Id:'
1047 || c_line_data_rec.line_item_id
1048 || ',ATO_model_item_id:'
1049 || c_line_data_rec.ato_model_item_id);
1050 END IF;
1051
1052 --
1053 --Checking if Config Line Machine Item is NULL
1054 --
1055 IF (c_line_data_rec.line_item_id IS NULL)
1056 THEN
1057 fnd_file.put_line
1058 (fnd_file.LOG
1059 ,'Config Line Machine Item is not present in the system');
1060 ELSIF (c_line_data_rec.ato_model_item_id IS NOT NULL)
1061 THEN
1062 l_num_chk_exist_line := l_num_chk_exist_line + 1;
1063
1064 --
1065 --Fetching the Assembly item Count
1066 --
1067 FOR c_bom_assembly_item_rec IN
1068 c_bom_assembly_item (c_line_data_rec.ato_model_item_id)
1069 LOOP
1070 l_num_item_count := c_bom_assembly_item_rec.item_count;
1071 END LOOP;
1072
1073 IF (l_num_item_count > 0)
1074 THEN
1075 IF g_chr_debug = 'Y'
1076 THEN
1077 fnd_file.put_line
1078 (fnd_file.LOG
1079 , 'ATO_model_item_id:'
1080 || c_line_data_rec.ato_model_item_id
1081 || ' BOM already created');
1082 END IF;
1083 ELSE
1084 IF g_chr_debug = 'Y'
1085 THEN
1086 fnd_file.put_line
1087 (fnd_file.LOG
1088 , 'ATO_model_item_id:'
1089 || c_line_data_rec.ato_model_item_id
1090 || ' BOM does not exist');
1091 END IF;
1092
1093 l_num_component_seq_count := 10;
1094 --
1095 -- Assign values to pl/sql table l_assembly_items_rec for assembly items
1096 --
1097 l_num_line_count := l_num_line_count + 1;
1098
1099 IF g_chr_debug = 'Y'
1100 THEN
1101 fnd_file.put_line
1102 (fnd_file.LOG
1103 , 'Assignment to Assembly Item:'
1104 || c_line_data_rec.ato_model_item_id
1105 || ' record begins (l_num_line_count):'
1106 || l_num_line_count);
1107 END IF;
1108
1109 l_assembly_items_rec.assembly_item_id (l_num_line_count) :=
1110 c_line_data_rec.ato_model_item_id;
1111
1112 --
1113 -- Assign line item values to pl/sql table l_component_items_rec for component items
1114 --
1115 IF g_chr_debug = 'Y'
1116 THEN
1117 fnd_file.put_line
1118 (fnd_file.LOG
1119 , 'Assignment to Assembly Item:'
1120 || c_line_data_rec.ato_model_item_id
1121 || ' record ends (l_num_line_count):'
1122 || l_num_line_count);
1123 END IF;
1124
1125 l_num_subline_count := l_num_subline_count + 1;
1126
1127 IF g_chr_debug = 'Y'
1128 THEN
1129 fnd_file.put_line
1130 (fnd_file.LOG
1131 , 'Assignment to Component Line Item:'
1132 || c_line_data_rec.line_item_id
1133 || ' record begins (l_num_subline_count):'
1134 || l_num_subline_count);
1135 END IF;
1136
1137 l_component_items_rec.assembly_item_id
1138 (l_num_subline_count) :=
1139 c_line_data_rec.ato_model_item_id;
1140 l_component_items_rec.component_item_id
1141 (l_num_subline_count) :=
1142 c_line_data_rec.line_item_id;
1143 l_component_items_rec.component_quantity
1144 (l_num_subline_count) :=
1145 /* commented and added below line by Narender -- 1.1*/
1146 -- c_line_data_rec.quantity;
1147 1;
1148 l_component_items_rec.wip_supply_type (l_num_subline_count) :=
1149 c_line_data_rec.supply_type;
1150 l_component_items_rec.item_num (l_num_subline_count) :=
1151 l_num_component_seq_count;
1152 l_component_items_rec.attribute11 (l_num_subline_count) :=
1153 c_line_data_rec.line_id;
1154 l_component_items_rec.attribute12 (l_num_subline_count) :=
1155 NULL;
1156
1157 IF g_chr_debug = 'Y'
1158 THEN
1159 fnd_file.put_line
1160 (fnd_file.LOG
1161 , 'Assignment to Component Line Item:'
1162 || c_line_data_rec.line_item_id
1163 || ' record ends (l_num_subline_count):'
1164 || l_num_subline_count);
1165 END IF;
1166
1167 --
1168 -- Fetching the Subline Level data
1169 --
1170 FOR c_subline_data_rec IN
1171 c_subline_data (c_line_data_rec.line_id)
1172 LOOP
1173 --
1174 -- Assign Sub line Item Values to pl/sql table l_component_items_rec for component items
1175 --
1176 l_num_subline_count := l_num_subline_count + 1;
1177 l_num_component_seq_count :=
1178 l_num_component_seq_count + 10;
1179
1180 IF g_chr_debug = 'Y'
1181 THEN
1182 fnd_file.put_line
1183 (fnd_file.LOG
1184 , 'Assignment to Component SubLine Item:'
1185 || c_subline_data_rec.line_item_id
1186 || ' record begins (l_num_subline_count):'
1187 || l_num_subline_count);
1188 END IF;
1189
1190 l_component_items_rec.assembly_item_id
1191 (l_num_subline_count) :=
1192 c_line_data_rec.ato_model_item_id;
1193 l_component_items_rec.component_item_id
1194 (l_num_subline_count) :=
1195 c_subline_data_rec.line_item_id;
1196 l_component_items_rec.component_quantity
1197 (l_num_subline_count) :=
1198 c_subline_data_rec.rollup_qty;
1199 l_component_items_rec.wip_supply_type
1200 (l_num_subline_count) :=
1201 c_subline_data_rec.supply_type;
1202 l_component_items_rec.item_num (l_num_subline_count) :=
1203 l_num_component_seq_count;
1204 l_component_items_rec.attribute11 (l_num_subline_count) :=
1205 c_subline_data_rec.line_id;
1206 l_component_items_rec.attribute12 (l_num_subline_count) :=
1207 c_subline_data_rec.sub_line_id;
1208
1209 IF g_chr_debug = 'Y'
1210 THEN
1211 fnd_file.put_line
1212 (fnd_file.LOG
1213 , ' Sub Line Details:ato_model_item_id:'
1214 || c_line_data_rec.ato_model_item_id
1215 || ',line_item_id:'
1216 || c_subline_data_rec.line_item_id
1217 || ',rollup_qty:'
1218 || c_subline_data_rec.rollup_qty
1219 || ',supply type:'
1220 || c_subline_data_rec.supply_type);
1221 END IF;
1222 END LOOP;
1223 END IF; --IF (l_num_item_count > 0)
1224 ELSE
1225 fnd_file.put_line
1226 (fnd_file.LOG
1227 ,'ATO Model Item does not exists for this line');
1228 END IF; --IF (c_line_data_rec.line_item_id IS NULL)
1229 END LOOP;
1230
1231 IF (l_num_chk_exist_line = 0)
1232 THEN
1233 out_chr_errbuff :=
1234 'No Lines Found for the given Header Id :'
1235 || l_num_header_id;
1236 RAISE exc_error_exit;
1237 END IF;
1238
1239 BEGIN
1240 FORALL l_num_loop_count IN 1 .. l_num_line_count
1241 INSERT INTO bom_bill_of_mtls_interface
1242 (assembly_item_id
1243 ,organization_id
1244 ,assembly_type
1245 ,transaction_type
1246 ,process_flag)
1247 VALUES (l_assembly_items_rec.assembly_item_id
1248 (l_num_loop_count)
1249 ,l_num_organization_id
1250 ,l_num_assembly_type
1251 ,l_chr_transaction_type
1252 ,l_num_process_flag);
1253 EXCEPTION
1254 WHEN OTHERS
1255 THEN
1256 out_chr_errbuff :=
1257 'Error Occur while inserting data into bom_bill_of_mtls_interface: '
1258 || SQLERRM;
1259 out_chr_retcode := g_chr_failure_code;
1260 RAISE exc_error_exit;
1261 END;
1262
1263 IF g_chr_debug = 'Y'
1264 THEN
1265 fnd_file.put_line
1266 (fnd_file.LOG
1267 , 'Number of records inserted into bom_bill_of_mtls_interface:'
1268 || l_num_line_count);
1269 END IF;
1270
1271
1272 --Added in version 1.2 for CR-306
1273 --Start of code for CR-306
1274 BEGIN
1275 SELECT flv.attribute3
1276 ,flv.attribute4
1277 ,bod.department_code
1278 INTO l_chr_depart_id
1279 ,l_chr_resource_id
1280 ,l_chr_dept_code
1281 FROM fnd_lookup_values_vl flv
1282 ,bom_departments bod
1283 WHERE flv.lookup_type = 'XXECS_DIST_VENCFG_ORGANIZATION'
1284 AND flv.enabled_flag = 'Y'
1285 AND NVL (flv.start_date_active
1286 ,SYSDATE) <= SYSDATE
1287 AND NVL (flv.end_date_active
1288 ,SYSDATE) >= SYSDATE
1289 AND flv.attribute2 = l_num_organization_id
1290 AND flv.attribute1 = g_num_operating_unit_id
1291 AND bod.organization_id = l_num_organization_id
1292 AND bod.department_id = TO_NUMBER (flv.attribute3);
1293 EXCEPTION
1294 WHEN NO_DATA_FOUND
1295 THEN
1296 out_chr_errbuff :=
1297 'No Data Found while fetching record for department and
1298 resource from look up XXECS_DIST_VENCFG_ORGANIZATION
1299 for Organization : '
1300 || l_num_organization_id;
1301 out_chr_retcode := g_chr_failure_code;
1302 RAISE exc_error_exit;
1303 WHEN OTHERS
1304 THEN
1305 out_chr_errbuff :=
1306 'Error Occured while fetching record for department and
1307 resource from look up XXECS_DIST_VENCFG_ORGANIZATION :'
1308 || SQLERRM;
1309 out_chr_retcode := g_chr_failure_code;
1310 RAISE exc_error_exit;
1311 END;
1312
1313 IF g_chr_debug = 'Y'
1314 THEN
1315 fnd_file.put_line (fnd_file.LOG
1316 , 'Department code is:'
1317 || l_chr_dept_code
1318 || ' Department id is:'
1319 || l_chr_depart_id
1320 || ' Resource id is:'
1321 || l_chr_resource_id);
1322 END IF;
1323
1324 FOR l_num_lop_count IN 1 .. l_num_line_count
1325 LOOP
1326 BEGIN
1327 SELECT bom_operation_sequences_s.NEXTVAL
1328 INTO l_num_bom_op_seq
1329 FROM DUAL;
1330
1331 INSERT INTO bom_op_routings_interface
1332 (assembly_item_id
1333 ,organization_id
1334 ,transaction_type
1335 ,process_flag
1336 ,routing_sequence_id)
1337 VALUES (l_assembly_items_rec.assembly_item_id
1338 (l_num_lop_count)
1339 ,l_num_organization_id
1340 ,l_chr_transaction_type
1341 ,l_num_process_flag
1342 ,bom_operational_routings_s.NEXTVAL
1343 -- Changed By Dhana
1344 );
1345
1346 INSERT INTO bom_op_sequences_interface
1347 (process_flag
1348 ,routing_sequence_id
1349 , -- Changed By Dhana
1350 operation_sequence_id
1351 , -- Changed By Dhana
1352 last_update_date
1353 ,last_updated_by
1354 ,creation_date
1355 ,created_by
1356 ,department_id
1357 ,department_code
1358 ,minimum_transfer_quantity
1359 ,count_point_type
1360 ,effectivity_date
1361 ,disable_date
1362 ,backflush_flag
1363 ,assembly_item_id
1364 ,option_dependent_flag
1365 ,organization_id
1366 ,resource_id1
1367 ,operation_seq_num
1368 ,transaction_type)
1369 VALUES (l_num_process_flag
1370 ,bom_operational_routings_s.CURRVAL
1371 , -- Changed By Dhana
1372 l_num_bom_op_seq
1373 , -- Changed By Dhana
1374 g_dat_sys_date
1375 ,g_num_user_id
1376 ,g_dat_sys_date
1377 ,g_num_user_id
1378 ,TO_NUMBER (l_chr_depart_id)
1379 ,l_chr_dept_code
1380 ,l_num_min_transfer_qty
1381 ,l_num_count_point_type
1382 ,g_dat_sys_date
1383 ,l_dte_disable_date
1384 ,l_num_backflush_flag
1385 ,l_assembly_items_rec.assembly_item_id
1386 (l_num_lop_count)
1387 ,l_num_option_dep_flag
1388 ,l_num_organization_id
1389 ,TO_NUMBER (l_chr_resource_id)
1390 ,l_num_operation_seq_num
1391 ,l_chr_transaction_type);
1392 EXCEPTION
1393 WHEN OTHERS
1394 THEN
1395 out_chr_errbuff :=
1396 'Error Occur while inserting data into bom_op_routings_interface
1397 and bom_op_sequences_interface: '
1398 || SQLERRM;
1399 out_chr_retcode := g_chr_failure_code;
1400 RAISE exc_error_exit;
1401 END;
1402 END LOOP;
1403
1404 IF g_chr_debug = 'Y'
1405 THEN
1406 fnd_file.put_line
1407 (fnd_file.LOG
1408 , 'Number of records inserted into bom_op_routings_interface
1409 and bom_op_sequences_interface :'
1410 || l_num_line_count);
1411 END IF;
1412
1413 --End of code for CR-306
1414 BEGIN
1415 --
1416 -- Inserting values into the interface table bom_inventory_comps_interface
1417 --
1418 FORALL l_num_loop_count IN 1 .. l_num_subline_count
1419 INSERT INTO bom_inventory_comps_interface
1420 (assembly_item_id
1421 ,component_item_id
1422 ,organization_id
1423 ,transaction_type
1424 ,process_flag
1425 ,component_quantity
1426 ,attribute11
1427 ,attribute12
1428 ,item_num
1429 ,operation_seq_num
1430 ,supply_subinventory
1431 ,wip_supply_type
1432 ,optional)
1433 VALUES (l_component_items_rec.assembly_item_id
1434 (l_num_loop_count)
1435 ,l_component_items_rec.component_item_id
1436 (l_num_loop_count)
1437 ,l_num_organization_id
1438 ,l_chr_transaction_type
1439 ,l_num_process_flag
1440 ,l_component_items_rec.component_quantity
1441 (l_num_loop_count)
1442 ,l_component_items_rec.attribute11
1443 (l_num_loop_count)
1444 ,l_component_items_rec.attribute12
1445 (l_num_loop_count)
1446 ,l_component_items_rec.item_num (l_num_loop_count)
1447 ,l_num_operation_seq_num
1448 ,l_chr_def_sub_inv
1449 ,l_component_items_rec.wip_supply_type
1450 (l_num_loop_count)
1451 ,l_num_optional);
1452 EXCEPTION
1453 WHEN OTHERS
1454 THEN
1455 out_chr_errbuff :=
1456 'Error Occur while inserting data into bom_inventory_comps_interface: '
1457 || SQLERRM;
1458 out_chr_retcode := g_chr_failure_code;
1459 RAISE exc_error_exit;
1460 END;
1461
1462 IF g_chr_debug = 'Y'
1463 THEN
1464 fnd_file.put_line
1465 (fnd_file.LOG
1466 , 'Number of records inserted into bom_inventory_comps_interface:'
1467 || l_num_subline_count);
1468 END IF;
1469
1470 --
1471 -- Launching the BOM Creation Concurrent Program*/
1472 --
1473 IF g_chr_debug = 'Y'
1474 THEN
1475 fnd_file.put_line
1476 (fnd_file.LOG
1477 ,'Launching the Concurrent Program - Bills and Routing Interface Program');
1478 END IF;
1479
1480 BEGIN
1481 l_num_request_id :=
1482 fnd_request.submit_request
1483 (application => 'BOM'
1484 ,program => 'BMCOIN'
1485 ,start_time => NULL
1486 ,sub_request => FALSE
1487 ,argument1 => l_num_organization_id
1488 ,argument2 => 2
1489 ,argument3 => 1
1490 ,argument4 => 1
1491 ,argument5 => 2);
1492
1493 IF g_chr_debug = 'Y'
1494 THEN
1495 fnd_file.put_line (fnd_file.LOG
1496 , 'Concurrent Program Request id:'
1497 || l_num_request_id);
1498 END IF;
1499
1500 COMMIT;
1501
1502 IF g_chr_debug = 'Y'
1503 THEN
1504 fnd_file.put_line (fnd_file.LOG
1505 , 'Waiting for Request id:'
1506 || l_num_request_id
1507 || ' to complete.');
1508 END IF;
1509
1510 l_bool_wait_return :=
1511 fnd_concurrent.wait_for_request
1512 (l_num_request_id
1513 , --the request id
1514 l_num_interval
1515 , --interval between checks
1516 l_num_max_wait
1517 , --max wait time
1518 l_chr_phase
1519 , --OUT; request phase
1520 l_chr_status
1521 , --OUT; request status
1522 l_chr_dev_phase
1523 , --OUT; request phase (dev)
1524 l_chr_dev_status
1525 ,
1526 --OUT; request status (dev)
1527 l_chr_message);
1528
1529 --OUT; request completion message
1530 IF ( UPPER (l_chr_dev_phase) = 'COMPLETE'
1531 AND UPPER (l_chr_dev_status) = 'NORMAL')
1532 THEN
1533 IF g_chr_debug = 'Y'
1534 THEN
1535 fnd_file.put_line
1536 (fnd_file.LOG
1537 ,'BOM Creation Program completed successfully.');
1538 END IF;
1539
1540 out_chr_retcode := g_chr_success_code;
1541 --
1542 --check if program completed with error
1543 --
1544 ELSIF ( UPPER (l_chr_dev_phase) = 'COMPLETE'
1545 AND UPPER (l_chr_dev_status) = 'ERROR')
1546 THEN
1547 IF g_chr_debug = 'Y'
1548 THEN
1549 fnd_file.put_line
1550 (fnd_file.LOG
1551 ,' BOM Creation Program completed with error.');
1552 END IF;
1553
1554 out_chr_retcode := g_chr_failure_code;
1555 --
1556 --for any other exit phase and/or status, log the details
1557 --
1558 ELSE
1559 IF g_chr_debug = 'Y'
1560 THEN
1561 fnd_file.put_line (fnd_file.LOG
1562 , ' Program Phase: "'
1563 || l_chr_dev_phase
1564 || '"; Status: "'
1565 || l_chr_dev_status
1566 || '"');
1567 END IF;
1568
1569 out_chr_retcode := g_chr_warning_code;
1570 END IF;
1571--IF (UPPER (l_chr_dev_phase) = 'COMPLETE' AND UPPER (l_chr_dev_status) = 'NORMAL')
1572 EXCEPTION
1573 WHEN OTHERS
1574 THEN
1575 out_chr_errbuff :=
1576 'Error while launching Bills and Routing Interface Program: '
1577 || SQLERRM;
1578 out_chr_retcode := g_chr_failure_code;
1579 RAISE exc_error_exit;
1580 END;
1581
1582 fnd_file.put_line
1583 (fnd_file.output
1584 ,'--------------ASCENT BOM Creation Program: BOM Creation Details (SUCCESS/ERROR)--------------');
1585 fnd_file.new_line (fnd_file.output
1586 ,2);
1587 fnd_file.put_line
1588 (fnd_file.output
1589 , 'Please refer the log of Program - Bills and Routing Interface Program for details. Request id:'
1590 || l_num_request_id);
1591 fnd_file.new_line (fnd_file.output
1592 ,2);
1593 fnd_file.put_line (fnd_file.log
1594 , 'LINE ID'
1595 || CHR (9)
1596 || CHR (9)
1597 || 'SUB LINE ID'
1598 || CHR (9)
1599 || 'COMPONENT ITEM ID'
1600 || CHR (9)
1601 || 'STATUS');
1602
1603 --
1604 -- Fetching the data for which Bom Creation Program was launch
1605 --
1606 FOR c_bom_created_data_rec IN c_bom_created_data
1607 LOOP
1608 l_num_bom_count := l_num_bom_count + 1;
1609 l_ecs_bom_created_rec.process_flag (l_num_bom_count) :=
1610 c_bom_created_data_rec.process_flag;
1611 l_ecs_bom_created_rec.attribute11 (l_num_bom_count) :=
1612 c_bom_created_data_rec.attribute11;
1613 l_ecs_bom_created_rec.attribute12 (l_num_bom_count) :=
1614 c_bom_created_data_rec.attribute12;
1615 l_ecs_bom_created_rec.component_item_id (l_num_bom_count) :=
1616 c_bom_created_data_rec.component_item_id;
1617
1618 IF (c_bom_created_data_rec.process_flag = '7')
1619 THEN
1620 IF g_chr_debug = 'Y'
1621 THEN
1622 fnd_file.put_line
1623 (fnd_file.log
1624 , c_bom_created_data_rec.attribute11
1625 || CHR (9)
1626 || CHR (9)
1627 || c_bom_created_data_rec.attribute12
1628 || CHR (9)
1629 || CHR (9)
1630 || c_bom_created_data_rec.component_item_id
1631 || CHR (9)
1632 || CHR (9)
1633 || CHR (9)
1634 || 'SUCCESS');
1635 END IF;
1636 ELSE
1637 fnd_file.put_line
1638 (fnd_file.log
1639 , c_bom_created_data_rec.attribute11
1640 || CHR (9)
1641 || CHR (9)
1642 || c_bom_created_data_rec.attribute12
1643 || CHR (9)
1644 || CHR (9)
1645 || c_bom_created_data_rec.component_item_id
1646 || CHR (9)
1647 || CHR (9)
1648 || CHR (9)
1649 || 'ERROR');
1650 END IF; --IF (c_bom_created_data_rec.process_flag = '7')
1651
1652 fnd_file.new_line (fnd_file.log
1653 ,1);
1654
1655
1656 END LOOP;
1657
1658 l_num_prev_line_id := -1;
1659
1660 FOR c_bom_created_data_rec IN c_bom_created_data
1661 LOOP
1662 l_num_curr_line_id := c_bom_created_data_rec.attribute11;
1663
1664 --
1665 -- Assigning the Line Id and BOm creation flag to the plsql table
1666 --
1667 IF (l_num_prev_line_id <> l_num_curr_line_id)
1668 THEN
1669 l_num_count := l_num_count + 1;
1670 l_ecs_bom_created_flag_rec.line_id (l_num_count) :=
1671 c_bom_created_data_rec.attribute11;
1672
1673 IF (c_bom_created_data_rec.process_flag = 7)
1674 THEN
1675 l_ecs_bom_created_flag_rec.bom_created_flag (l_num_count) :=
1676 'Y';
1677 ELSE
1678 l_ecs_bom_created_flag_rec.bom_created_flag (l_num_count) :=
1679 'N';
1680 END IF;
1681 END IF;
1682
1683 IF ( (l_ecs_bom_created_flag_rec.bom_created_flag (l_num_count) =
1684 'Y')
1685 AND (c_bom_created_data_rec.process_flag <> 7))
1686 THEN
1687 l_ecs_bom_created_flag_rec.bom_created_flag (l_num_count) :=
1688 'N';
1689 END IF;
1690
1691 l_num_prev_line_id := l_num_curr_line_id;
1692 END LOOP;
1693
1694 --
1695 -- Updating the bom creation flag for the lines in table xxecs_mws_config_line_ad
1696 --
1697 BEGIN
1698 FORALL l_num_loop_count IN 1 .. l_num_count
1699 UPDATE xxecs_mws_config_line_ad eqcl
1700 SET bom_created_flag =
1701 l_ecs_bom_created_flag_rec.bom_created_flag
1702 (l_num_loop_count)
1703 ,last_updated_by = g_num_user_id
1704 ,last_update_date = g_dat_sys_date
1705 ,last_update_login = g_num_login_id
1706 ,request_id = g_num_request_id
1707 ,program_id = g_num_program_id
1708 ,program_application_id = g_num_program_appl_id
1709 WHERE eqcl.line_id =
1710 l_ecs_bom_created_flag_rec.line_id
1711 (l_num_loop_count);
1712 EXCEPTION
1713 WHEN OTHERS
1714 THEN
1715 out_chr_errbuff :=
1716 'Error Occur while upadting data into xxecs_mws_config_subline_ad: '
1717 || SQLERRM;
1718 out_chr_retcode := g_chr_failure_code;
1719 RAISE exc_error_exit;
1720 END;
1721
1722 IF g_chr_debug = 'Y'
1723 AND l_num_bom_count > 0
1724 THEN
1725 fnd_file.put_line
1726 (fnd_file.LOG
1727 , 'Number of records updated in xxecs_mws_config_line_ad: '
1728 || l_num_count);
1729 END IF;
1730
1731 COMMIT;
1732
1733 IF g_chr_debug = 'Y'
1734 AND l_num_bom_count > 0
1735 THEN
1736 fnd_file.put_line
1737 (fnd_file.LOG
1738 ,'Fetching the BOM details for deletion in case some error was found during creation');
1739 END IF;
1740
1741 --
1742 -- Fetching the data for bom deletion
1743 --
1744 BEGIN
1745 FOR c_bom_deletion_data_rec IN c_bom_deletion_data
1746 LOOP
1747 fnd_file.put_line(fnd_file.LOG ,'in c_bom_deletion_data_rec');
1748
1749 IF (NVL (c_bom_deletion_data_rec.bom_created_flag
1750 ,'N') = 'N')
1751 THEN
1752 xxecs_mws_bom_creation_pkg.xxecs_bom_deletion_proc
1753 (l_chr_error_code
1754 ,l_chr_retcod
1755 ,c_bom_deletion_data_rec.ato_model_item_id
1756 ,l_num_organization_id --Added 1.3 By Binay
1757 );
1758
1759 IF (l_chr_retcod <> '0')
1760 THEN
1761 fnd_file.put_line
1762 (fnd_file.LOG
1763 , ' Bill For Item '
1764 || c_bom_deletion_data_rec.ato_model_item_id
1765 || ' in organization '
1766 || l_num_organization_id
1767 || ' could not be deleted');
1768 fnd_file.put_line (fnd_file.LOG
1769 ,'');
1770 out_chr_errbuff := l_chr_error_code;
1771 RAISE exc_error_exit;
1772 ELSE
1773 fnd_file.put_line
1774 (fnd_file.LOG
1775 , 'Existing Bill For Assembly Item '
1776 || c_bom_deletion_data_rec.ato_model_item_id
1777 || ' in organization '
1778 || l_num_organization_id
1779 || ' has been deleted succesfully');
1780 fnd_file.put_line (fnd_file.LOG
1781 ,'');
1782 END IF;
1783 ELSE
1784 fnd_file.put_line(fnd_file.LOG ,'calling for common BOM creation ');
1785 xxecs_mws_bom_creation_pkg.xxecs_bom_assign_invorg_proc
1786 (l_chr_error_code
1787 ,l_chr_retcod
1788 ,c_bom_deletion_data_rec.ato_model_item_id
1789 ,c_bom_deletion_data_rec.org_id
1790 ,l_num_organization_id --Added 1.3 By Binay
1791 );
1792 END IF;
1793 END LOOP;
1794 xxecs_mws_bom_creation_pkg.xxecs_config_auto_proc (l_chr_error_code
1795 ,l_chr_retcod
1796 ,in_num_header_id
1797 ,l_num_organization_id); --Added 1.3 By Binay
1798 --
1799 --Deleting the PLsql tables
1800 --
1801 l_assembly_items_rec.assembly_item_id.DELETE;
1802 l_component_items_rec.assembly_item_id.DELETE;
1803 l_component_items_rec.component_item_id.DELETE;
1804 l_component_items_rec.item_num.DELETE;
1805 l_component_items_rec.attribute11.DELETE;
1806 l_component_items_rec.attribute12.DELETE;
1807 l_component_items_rec.component_quantity.DELETE;
1808 l_component_items_rec.wip_supply_type.DELETE;
1809 l_ecs_bom_created_rec.process_flag.DELETE;
1810 l_ecs_bom_created_rec.attribute11.DELETE;
1811 l_ecs_bom_created_rec.attribute12.DELETE;
1812 l_ecs_bom_created_rec.component_item_id.DELETE;
1813 l_ecs_bom_created_flag_rec.bom_created_flag.DELETE;
1814 l_ecs_bom_created_flag_rec.line_id.DELETE;
1815 COMMIT;
1816 EXCEPTION
1817 WHEN OTHERS
1818 THEN
1819 out_chr_errbuff := 'Unexpected error occurs' || SQLERRM;
1820 out_chr_retcode := g_chr_failure_code;
1821 END;
1822
1823 --
1824 -- Calling the procedure for generating the output file for BOM status
1825 --
1826 xxecs_mws_bom_creation_pkg.xxecs_bom_status_log_proc(l_chr_error_code
1827 ,l_chr_retcod
1828 ,l_num_header_id
1829 );
1830
1831
1832 -- START ADDED in version 1.3
1833 BEGIN
1834 SELECT email_address
1835 INTO l_chr_email_address
1836 FROM fnd_user
1837 WHERE user_id = g_num_user_id;
1838 EXCEPTION
1839 WHEN OTHERS
1840 THEN
1841 fnd_file.put_line (fnd_file.LOG, 'ERROR fetching email address for the user id' ||g_num_user_id||SQLERRM);
1842 l_chr_email_address := NULL;
1843 END;
1844
1845l_chr_email_address := 'rsuri@arrow.com';
1846
1847 BEGIN
1848 SELECT config_file || '-' || config_id || '-' || revision
1849 INTO l_chr_config_name
1850 FROM xxecs_mws_config_hdr_ad
1851 WHERE header_id = l_num_header_id;
1852 EXCEPTION
1853 WHEN OTHERS
1854 THEN
1855 fnd_file.put_line (fnd_file.LOG, 'ERROR fetching email address for the user id' ||g_num_user_id||SQLERRM);
1856 l_chr_email_address := NULL;
1857 END;
1858
1859 l_chr_mesg:= 'MIME-Version: 1.0' || l_chr_crlf;
1860 l_chr_mesg:= l_chr_mesg || 'From: ' || 'arrow_ecs_applmgr@arrow.com' || l_chr_crlf;
1861 l_chr_mesg:= l_chr_mesg || 'To: ' || l_chr_email_address || l_chr_crlf;
1862 l_chr_mesg:= l_chr_mesg || 'Cc: ' || NULL || l_chr_crlf;
1863 l_chr_mesg:= l_chr_mesg || 'Subject: ' || 'BOM Status' || l_chr_crlf;
1864
1865 --
1866 -- Calling the procedure to send the status of BOM in email
1867 --
1868 IF (l_chr_retcod = g_chr_success_code)
1869 THEN
1870 l_chr_mesg := l_chr_mesg||'BOM creation program completed SUCCESSFULLY for vendor config header id: '||l_num_header_id
1871 ||' and Config File: '||l_chr_config_name||
1872 '. To view more details please go to '||l_chr_crlf
1873 ||'View --> Requests --> Find'||l_chr_crlf
1874 ||'where Request id is :'||g_num_request_id;
1875
1876 ELSIF (l_chr_retcod = g_chr_warning_code)
1877 THEN
1878 l_chr_mesg := l_chr_mesg||'BOM creation program ended in WARNING for vendor config header id: '||l_num_header_id
1879 ||' and Config File: '||l_chr_config_name||' : WARNING: '||l_chr_error_code||
1880 '. To view more details please go to '||l_chr_crlf
1881 ||'View --> Requests --> Find'||l_chr_crlf
1882 ||'where Request id is :'||g_num_request_id;
1883 ELSE
1884 l_chr_mesg := l_chr_mesg||'BOM creation program ended in ERROR for vendor config header id: '||l_num_header_id
1885 ||' and Config File: '||l_chr_config_name||' : ERROR: '||l_chr_error_code||
1886 '. To view more details please go to '||l_chr_crlf
1887 ||'View --> Requests --> Find'||l_chr_crlf
1888 ||'where Request id is :'||g_num_request_id;
1889 END IF;
1890
1891 IF (l_chr_email_address IS NOT NULL)
1892 THEN
1893 fnd_file.put_line (fnd_file.LOG, 'l_chr_mesg: ' ||l_chr_mesg||' l_chr_email_address: '||l_chr_email_address);
1894 xxecs_cmn_mail_send_pkg.xxecs_cmn_mail_sending_proc ( l_chr_error_code1
1895 ,l_chr_retcod1
1896 ,in_chr_message => l_chr_mesg
1897 ,in_chr_recepient_cc => NULL
1898 ,in_chr_recepient_to => l_chr_email_address
1899 );
1900
1901 fnd_file.put_line (fnd_file.LOG, 'l_chr_retcod1: ' ||l_chr_retcod1);
1902
1903 ELSE
1904 fnd_file.put_line (fnd_file.LOG, 'Status Could not be emailed as, email address not specified for current user id' ||g_num_user_id);
1905 END IF;
1906 -- END ADDED in version 1.3
1907
1908
1909 EXCEPTION
1910 WHEN exc_error_exit
1911 THEN
1912 out_chr_retcode := g_chr_failure_code;
1913 fnd_file.put_line (fnd_file.LOG
1914 , out_chr_errbuff || SQLERRM);
1915
1916 -- START ADDED in version 1.3
1917 BEGIN
1918 SELECT email_address
1919 INTO l_chr_email_address
1920 FROM fnd_user
1921 WHERE user_id = g_num_user_id;
1922 EXCEPTION
1923 WHEN OTHERS
1924 THEN
1925 fnd_file.put_line (fnd_file.LOG, 'ERROR fetching email address for the user id' ||g_num_user_id||SQLERRM);
1926 l_chr_email_address := NULL;
1927 END;
1928
1929 BEGIN
1930 SELECT config_file || '-' || config_id || '-' || revision
1931 INTO l_chr_config_name
1932 FROM xxecs_mws_config_hdr_ad
1933 WHERE header_id = in_num_header_id;
1934 EXCEPTION
1935 WHEN OTHERS
1936 THEN
1937 fnd_file.put_line (fnd_file.LOG, 'ERROR fetching config name for header id ' ||in_num_header_id||SQLERRM);
1938 END;
1939
1940 l_chr_mesg:= 'MIME-Version: 1.0' || l_chr_crlf;
1941 l_chr_mesg:= l_chr_mesg || 'From: ' || 'arrow_ecs_applmgr@arrow.com' || l_chr_crlf;
1942 l_chr_mesg:= l_chr_mesg || 'To: ' || l_chr_email_address || l_chr_crlf;
1943 l_chr_mesg:= l_chr_mesg || 'Cc: ' || NULL || l_chr_crlf;
1944 l_chr_mesg:= l_chr_mesg || 'Subject: ' || 'BOM Status' || l_chr_crlf;
1945
1946
1947 l_chr_mesg := 'BOM creation program ended in ERROR for vendor config header id: '||in_num_header_id
1948 ||' and Config File: '||l_chr_config_name||' ERROR : '||SQLERRM||
1949 '. To view more details please go to '||l_chr_crlf
1950 ||'View --> Requests --> Find'||l_chr_crlf
1951 ||'where Request id is :'||g_num_request_id;
1952
1953 IF (l_chr_email_address IS NOT NULL)
1954 THEN
1955 fnd_file.put_line (fnd_file.LOG, 'l_chr_mesg: ' ||l_chr_mesg||' l_chr_email_address: '||l_chr_email_address);
1956 xxecs_cmn_mail_send_pkg.xxecs_cmn_mail_sending_proc ( l_chr_error_code1
1957 ,l_chr_retcod1
1958 ,in_chr_message => l_chr_mesg
1959 ,in_chr_recepient_cc => NULL
1960 ,in_chr_recepient_to => l_chr_email_address
1961 );
1962
1963 fnd_file.put_line (fnd_file.LOG, 'l_chr_retcod1: ' ||l_chr_retcod1);
1964
1965 ELSE
1966 fnd_file.put_line (fnd_file.LOG, 'Status Could not be emailed as, email address not specified for current user id' ||g_num_user_id);
1967 END IF;
1968 -- END ADDED in version 1.3
1969
1970
1971 ROLLBACK;
1972 WHEN OTHERS
1973 THEN
1974 out_chr_errbuff := 'Unexpected error occurs' || SQLERRM;
1975
1976 -- START ADDED in version 1.3
1977 BEGIN
1978 SELECT email_address
1979 INTO l_chr_email_address
1980 FROM fnd_user
1981 WHERE user_id = g_num_user_id;
1982 EXCEPTION
1983 WHEN OTHERS
1984 THEN
1985 fnd_file.put_line (fnd_file.LOG, 'ERROR fetching email address for the user id' ||g_num_user_id||SQLERRM);
1986 l_chr_email_address := NULL;
1987 END;
1988
1989 BEGIN
1990 SELECT config_file || '-' || config_id || '-' || revision
1991 INTO l_chr_config_name
1992 FROM xxecs_mws_config_hdr_ad
1993 WHERE header_id = in_num_header_id;
1994 EXCEPTION
1995 WHEN OTHERS
1996 THEN
1997 fnd_file.put_line (fnd_file.LOG, 'ERROR fetching config name for header id ' ||in_num_header_id||SQLERRM);
1998 END;
1999 l_chr_mesg:= 'MIME-Version: 1.0' || l_chr_crlf;
2000 l_chr_mesg:= l_chr_mesg || 'From: ' || 'arrow_ecs_applmgr@arrow.com' || l_chr_crlf;
2001 l_chr_mesg:= l_chr_mesg || 'To: ' || l_chr_email_address || l_chr_crlf;
2002 l_chr_mesg:= l_chr_mesg || 'Cc: ' || NULL || l_chr_crlf;
2003 l_chr_mesg:= l_chr_mesg || 'Subject: ' || 'BOM Status' || l_chr_crlf;
2004
2005
2006 l_chr_mesg := 'BOM creation program ended in ERROR for vendor config header id: '||in_num_header_id
2007 ||' and Config File: '||l_chr_config_name||' ERROR : '||out_chr_errbuff||
2008 '. To view more details please go to '||l_chr_crlf
2009 ||'View --> Requests --> Find'||l_chr_crlf
2010 ||'Where Request id is :'||g_num_request_id;
2011
2012 IF (l_chr_email_address IS NOT NULL)
2013 THEN
2014 fnd_file.put_line (fnd_file.LOG, 'l_chr_mesg: ' ||l_chr_mesg||' l_chr_email_address: '||l_chr_email_address);
2015 xxecs_cmn_mail_send_pkg.xxecs_cmn_mail_sending_proc ( l_chr_error_code1
2016 ,l_chr_retcod1
2017 ,in_chr_message => l_chr_mesg
2018 ,in_chr_recepient_cc => NULL
2019 ,in_chr_recepient_to => l_chr_email_address
2020 );
2021
2022 fnd_file.put_line (fnd_file.LOG, 'l_chr_retcod1: ' ||l_chr_retcod1);
2023
2024 ELSE
2025 fnd_file.put_line (fnd_file.LOG, 'Status Could not be emailed as, email address not specified for current user id' ||g_num_user_id);
2026 END IF;
2027 -- END ADDED in version 1.3
2028 out_chr_retcode := g_chr_failure_code;
2029 END xxecs_bom_creation_proc;
2030
2031 PROCEDURE xxecs_bom_deletion_proc (
2032 out_chr_error_msg OUT VARCHAR2
2033 ,out_chr_retcod OUT VARCHAR2
2034 ,in_num_assembly_item_id IN NUMBER
2035 ,in_num_organization_id IN NUMBER) --Added 1.3 By Binay
2036 AS
2037/***************************************************************************************************************************************
2038* Type : Procedure *
2039* Name : XXECS_BOM_DELETION_PROC *
2040* Input Parameters : 1. in_num_assembly_item_id *
2041* *
2042* Purpose : Procedure to delete the BOM in case of errors *
2043* *
2044* Company : Infosys Technologies Ltd. *
2045* Created By : Infosys (Paramdeep Singh Aneja) *
2046* Created Date : 11-JAN-2008 *
2047* Last Reviewed By : Infosys (Deepak Sharma) *
2048* Last Reviewed Date : 12-JAN-2008 *
2049****************************************************************************************************************************************
2050* Modified Date Modified By Version Reviewed By Reviewed Date Modification Type Modification Details *
2051* ------------- ----------- -------- --------------- -------------- ----------------- -------------------------- *
2052* 11-JAN-2008 Infosys(Paramdeep) 1.0 Infosys (Deepak ) 12-JAN-2008 O Created.
2053* 26-JUN-2015 Infosys(BINAY) 1.1 Added organization_id. *
2054****************************************************************************************************************************************/
2055--
2056--Defining Local Variables
2057--
2058 l_num_bill_seq_id NUMBER;
2059 l_num_assembly_item_id NUMBER := in_num_assembly_item_id;
2060 --l_num_organization_id NUMBER --Commented By 1.1 Binay
2061 --:= fnd_profile.VALUE ('XXECS_DIST_BOM_ORGANIZATION');--Commented By 1.1 Binay
2062 l_num_organization_id NUMBER := in_num_organization_id;
2063 -- fnd_profile.VALUE ('XXECS_DIST_ITEM_ORGANIZATION');
2064 l_num_delete_group_id NUMBER;
2065 l_num_delete_entity_id NUMBER;
2066 l_num_assembly_type NUMBER := 1;
2067 l_num_item_desc VARCHAR2 (2000);
2068 l_chr_assembly_item_name VARCHAR2 (1000);
2069 l_chr_del_group_name VARCHAR2 (100);
2070 l_chr_error_msg VARCHAR2 (2000);
2071 l_chr_retcod VARCHAR2 (1000);
2072 l_num_delete_org_type NUMBER (10) := 1;
2073 l_num_delete_type NUMBER := 2;
2074 l_num_action_type NUMBER := 2;
2075 l_num_common_bill_flag NUMBER := 2;
2076 l_num_delete_entity_type NUMBER := 2;
2077 l_num_delete_status_type NUMBER := 1;
2078 l_num_prior_process_flag NUMBER := 1;
2079 l_num_prior_commit_flag NUMBER := 1;
2080 l_num_archive_type NUMBER := 2;
2081 exc_error_exit EXCEPTION;
2082
2083--
2084--Cursor for fetching the item information
2085--
2086 CURSOR c_item_info
2087 IS
2088 SELECT description
2089 ,concatenated_segments
2090 FROM mtl_system_items_b_kfv
2091 WHERE inventory_item_id = l_num_assembly_item_id
2092 AND organization_id = l_num_organization_id;
2093
2094--
2095--Cursor for fetching the Bill Sequence ID
2096--
2097 CURSOR c_bill_sequence
2098 IS
2099 SELECT bill_sequence_id
2100 FROM bom_bill_of_materials
2101 WHERE assembly_item_id = l_num_assembly_item_id
2102 AND organization_id = l_num_organization_id
2103 AND NVL (alternate_bom_designator
2104 ,'NONE') = 'NONE';
2105 BEGIN
2106 --
2107 -- fetching the item information
2108 --
2109 FOR c_item_info_rec IN c_item_info
2110 LOOP
2111 l_num_item_desc := c_item_info_rec.description;
2112 l_chr_assembly_item_name := c_item_info_rec.concatenated_segments;
2113 END LOOP;
2114
2115 IF g_chr_debug = 'Y'
2116 THEN
2117 fnd_file.put_line (fnd_file.LOG
2118 , 'Processing the Assmebly Item :'
2119 || l_chr_assembly_item_name
2120 || 'for Deletion');
2121 END IF;
2122
2123 --
2124 -- fetching the bill sequence Id
2125 --
2126 FOR c_bill_sequence_rec IN c_bill_sequence
2127 LOOP
2128 l_num_bill_seq_id := c_bill_sequence_rec.bill_sequence_id;
2129 END LOOP;
2130
2131 IF g_chr_debug = 'Y'
2132 THEN
2133 fnd_file.put_line
2134 (fnd_file.LOG
2135 , 'Bill Sequence Id of the BOM Created for Item '
2136 || l_chr_assembly_item_name
2137 || ' is :'
2138 || l_num_bill_seq_id);
2139 END IF;
2140
2141 --
2142 -- fetching the Nextvalue for Sequence bom_delete_groups_s
2143 --
2144 SELECT bom_delete_groups_s.NEXTVAL
2145 INTO l_num_delete_group_id
2146 FROM DUAL;
2147
2148 --
2149 -- fetching the Nextvalue for Sequence bom_delete_entities_s
2150 --
2151 SELECT bom_delete_entities_s.NEXTVAL
2152 INTO l_num_delete_entity_id
2153 FROM DUAL;
2154
2155 --
2156 -- Getting the Delete Group Name
2157 --
2158 IF (LENGTH (l_num_delete_group_id) > 7)
2159 THEN
2160 l_chr_del_group_name :=
2161 SUBSTR (l_num_delete_group_id
2162 , LENGTH (l_num_delete_group_id) - 6
2163 ,7);
2164 ELSE
2165 l_chr_del_group_name := l_num_delete_group_id;
2166 END IF; --IF (LENGTH (l_num_delete_group_id) > 7)
2167
2168 --
2169 -- Inserting the data into the bom_delete_groups table
2170 --
2171 BEGIN
2172 INSERT INTO bom_delete_groups
2173 (delete_group_sequence_id
2174 ,delete_group_name
2175 ,delete_org_type
2176 ,organization_id
2177 ,delete_type
2178 ,action_type
2179 ,delete_common_bill_flag
2180 ,engineering_flag
2181 ,last_update_date
2182 ,last_updated_by
2183 ,creation_date
2184 ,created_by)
2185 VALUES (l_num_delete_group_id
2186 , 'ECS' || l_chr_del_group_name
2187 ,l_num_delete_org_type
2188 ,l_num_organization_id
2189 ,l_num_delete_type
2190 ,l_num_action_type
2191 ,l_num_common_bill_flag
2192 ,l_num_assembly_type
2193 ,g_dat_sys_date
2194 ,g_num_user_id
2195 ,g_dat_sys_date
2196 ,g_num_user_id);
2197
2198 IF g_chr_debug = 'Y'
2199 THEN
2200 fnd_file.put_line (fnd_file.LOG
2201 , 'Delete Group Created as:'
2202 || 'ECS'
2203 || l_chr_del_group_name);
2204 END IF;
2205
2206 IF g_chr_debug = 'Y'
2207 THEN
2208 fnd_file.put_line
2209 (fnd_file.LOG
2210 , 'Number of records inserted into table bom_delete_groups:'
2211 || SQL%ROWCOUNT);
2212 END IF;
2213 EXCEPTION
2214 WHEN OTHERS
2215 THEN
2216 out_chr_error_msg :=
2217 'Error Occur while inserting data into bom_delete_groups: '
2218 || SQLERRM;
2219 out_chr_retcod := g_chr_failure_code;
2220 RAISE exc_error_exit;
2221 END;
2222
2223 --
2224 -- Inserting the data into the bom_delete_entities table
2225 --
2226 BEGIN
2227 INSERT INTO bom_delete_entities
2228 (delete_entity_sequence_id
2229 ,delete_group_sequence_id
2230 ,delete_entity_type
2231 ,delete_status_type
2232 ,bill_sequence_id
2233 ,inventory_item_id
2234 ,organization_id
2235 ,item_description
2236 ,alternate_designator
2237 ,item_concat_segments
2238 ,prior_process_flag
2239 ,prior_commit_flag
2240 ,last_update_date
2241 ,last_updated_by
2242 ,creation_date
2243 ,created_by)
2244 VALUES (l_num_delete_entity_id
2245 ,l_num_delete_group_id
2246 ,l_num_delete_entity_type
2247 ,l_num_delete_status_type
2248 ,l_num_bill_seq_id
2249 ,l_num_assembly_item_id
2250 ,l_num_organization_id
2251 ,l_num_item_desc
2252 ,NULL
2253 ,l_chr_assembly_item_name
2254 ,l_num_prior_process_flag
2255 ,l_num_prior_commit_flag
2256 ,g_dat_sys_date
2257 ,g_num_user_id
2258 ,g_dat_sys_date
2259 ,g_num_user_id);
2260
2261 IF g_chr_debug = 'Y'
2262 THEN
2263 fnd_file.put_line
2264 (fnd_file.LOG
2265 , 'Number of records inserted into table bom_delete_entities:'
2266 || SQL%ROWCOUNT);
2267 END IF;
2268 EXCEPTION
2269 WHEN OTHERS
2270 THEN
2271 out_chr_error_msg :=
2272 'Error Occur while inserting data into bom_delete_entities: '
2273 || SQLERRM;
2274 out_chr_retcod := g_chr_failure_code;
2275 RAISE exc_error_exit;
2276 END;
2277
2278 IF g_chr_debug = 'Y'
2279 THEN
2280 fnd_file.put_line (fnd_file.LOG
2281 , 'Calling the API for BOM deletion with '
2282 || 'Delete Group ID: '
2283 || l_num_delete_group_id
2284 || ',action_type:'
2285 || l_num_action_type
2286 || ',delete_type:'
2287 || l_num_delete_type
2288 || ',archive'
2289 || l_num_archive_type);
2290 END IF;
2291
2292 --
2293 --Calling the api to delete the bom ;
2294 --
2295 bom_delete_groups_api.delete_groups
2296 (errbuf => l_chr_error_msg
2297 ,retcode => l_chr_retcod
2298 ,delete_group_id => l_num_delete_group_id
2299 ,action_type => l_num_action_type
2300 ,delete_type => l_num_delete_type
2301 ,ARCHIVE => l_num_archive_type);
2302 out_chr_error_msg := l_chr_error_msg;
2303 out_chr_retcod := l_chr_retcod;
2304 EXCEPTION
2305 WHEN exc_error_exit
2306 THEN
2307 out_chr_retcod := g_chr_failure_code;
2308 fnd_file.put_line (fnd_file.LOG
2309 , out_chr_error_msg || SQLERRM);
2310 ROLLBACK;
2311 WHEN OTHERS
2312 THEN
2313 out_chr_error_msg :=
2314 'Unexpected error occurs in xxecs_mws_bom_creation_pkg.XXECS_BOM_DELETION_PROC :'
2315 || SQLERRM;
2316 out_chr_retcod := g_chr_failure_code;
2317 END xxecs_bom_deletion_proc;
2318
2319 PROCEDURE xxecs_bom_assign_invorg_proc (
2320 out_chr_error_msg OUT VARCHAR2
2321 ,out_chr_retcod OUT VARCHAR2
2322 ,in_num_assembly_item_id IN NUMBER
2323 ,in_num_org_id IN NUMBER
2324 ,in_num_organization_id IN NUMBER) --Added By 1.0 Binay
2325 AS
2326 /***************************************************************************************************************************************
2327 * Type : Procedure *
2328 * Name : XXECS_BOM_ASSIGN_INVORG_PROC *
2329 * Input Parameters : 1. in_num_assembly_item_id *
2330 * : 2. in_num_org_id *
2331 * *
2332 * Purpose : Procedure to assign Inventory Organization the BOM in case of errors *
2333 * *
2334 * Company : Infosys Technologies Ltd. *
2335 * Created By : Infosys (Paramdeep Singh Aneja) *
2336 * Created Date : 25-JAN-2008 *
2337 * Last Reviewed By : Infosys (Deepak Sharma) *
2338 * Last Reviewed Date : *
2339 ****************************************************************************************************************************************
2340 * Modified Date Modified By Version Reviewed By Reviewed Date Modification Type Modification Details *
2341 * ------------- ----------- -------- --------------- -------------- ----------------- -------------------------- *
2342 * 25-JAN-2008 Infosys(Paramdeep) 0.0 Infosys (Deepak ) 12-JAN-2008 O Created. *
2343 * 26-JUN-2015 BINAY 1.0 Arrow Extension Added in_num_organization_id. *
2344 ****************************************************************************************************************************************/
2345 l_num_scope NUMBER := 1;
2346 l_num_from_organization_id NUMBER := in_num_organization_id;
2347 -- l_num_from_organization_id NUMBER := fnd_profile.VALUE ('XXECS_DIST_BOM_ORGANIZATION'); --Commented by 1.0 Binay
2348
2349 --fnd_profile.VALUE ('XXECS_DIST_ITEM_ORGANIZATION');
2350 l_num_assembly_item_id NUMBER := in_num_assembly_item_id;
2351 l_num_to_organization_id NUMBER;
2352 l_num_org_id NUMBER := in_num_org_id;
2353 --max wait time, in secs; here, set to 1 Day
2354 l_num_max_wait NUMBER := 60 * 60 * 24;
2355 l_count NUMBER := 0;
2356 l_num_line_type_id NUMBER;
2357 l_num_prev_line_id NUMBER;
2358 l_num_curr_line_id NUMBER;
2359 l_chr_phase VARCHAR2 (80);
2360 l_num_request_id NUMBER;
2361 l_bool_wait_return BOOLEAN;
2362 l_num_interval NUMBER := 1;
2363 l_num_count NUMBER := 0;
2364 l_num_cnt NUMBER := 1;
2365 --OUT variable; request phase
2366 l_chr_status VARCHAR2 (80);
2367 --OUT variable; request completion status
2368 l_chr_dev_phase VARCHAR2 (30);
2369 --OUT variable; request phase (dev)
2370 l_chr_dev_status VARCHAR2 (30);
2371 --OUT variable; request completion status(dev)
2372 l_chr_message VARCHAR2 (1000);
2373 --OUT variable; request completion message
2374 l_num_conc_request_id NUMBER := 0;
2375
2376 TYPE tbl_request_id IS TABLE OF NUMBER
2377 INDEX BY BINARY_INTEGER;
2378
2379 TYPE tbl_organization_id IS TABLE OF fnd_lookup_values_vl.attribute2%TYPE
2380 INDEX BY BINARY_INTEGER;
2381
2382 TYPE ecs_request_rec IS RECORD (
2383 request_id tbl_request_id
2384 ,organization_id tbl_organization_id
2385 );
2386
2387 l_ecs_request_id ecs_request_rec;
2388
2389 --
2390 -- Declaring Cursor for fetching the Organizations
2391 --
2392 CURSOR c_invorg_to_assign
2393 IS
2394 SELECT attribute2 inv_org_id
2395 FROM fnd_lookup_values_vl flv
2396 WHERE flv.lookup_type = 'XXECS_DIST_VENCFG_ORGANIZATION'
2397 AND flv.enabled_flag = 'Y'
2398 AND attribute2 <> l_num_from_organization_id
2399 AND attribute1 = l_num_org_id
2400 AND NVL (start_date_active
2401 , SYSDATE - 1) < SYSDATE
2402 AND NVL (end_date_active
2403 , SYSDATE + 1) > SYSDATE
2404 AND attribute2 <>
2405 l_num_from_organization_id; --Added 1.0 by Binay
2406 --fnd_profile.VALUE ('XXECS_DIST_BOM_ORGANIZATION');--Commented 1.0 by Binay
2407 BEGIN
2408 FOR c_invorg_to_assign_rec IN c_invorg_to_assign
2409 LOOP
2410 l_num_count := l_num_count + 1;
2411
2412 BEGIN
2413 l_num_to_organization_id := c_invorg_to_assign_rec.inv_org_id;
2414 fnd_file.put_line
2415 (fnd_file.LOG
2416 , 'Process initiated to assign Assembly Item :'
2417 || l_num_assembly_item_id
2418 || ' to inventory Organization :'
2419 || l_num_to_organization_id);
2420 --
2421 -- Launching the Concurrent Program "Create Common Bills"
2422 --
2423 l_ecs_request_id.request_id (l_num_count) :=
2424 fnd_request.submit_request
2425 (application => 'BOM'
2426 ,program => 'BOMPCMBM'
2427 ,start_time => NULL
2428 ,sub_request => FALSE
2429 ,argument1 => l_num_scope
2430 ,argument2 => NULL
2431 ,argument3 => l_num_from_organization_id
2432 ,argument4 => l_num_assembly_item_id
2433 ,argument5 => NULL
2434 ,argument6 => l_num_to_organization_id
2435 ,argument7 => l_num_assembly_item_id);
2436 COMMIT;
2437 l_ecs_request_id.organization_id (l_num_count) :=
2438 l_num_to_organization_id;
2439
2440 IF g_chr_debug = 'Y'
2441 THEN
2442 fnd_file.put_line
2443 (fnd_file.LOG
2444 , 'Concurrent Program "Create Common Bill" launched with Request id:'
2445 || l_num_request_id);
2446 END IF;
2447 END;
2448 END LOOP;
2449
2450 WHILE (l_num_cnt <= l_num_count)
2451 LOOP
2452 l_num_request_id := l_ecs_request_id.request_id (l_num_cnt);
2453 l_num_to_organization_id :=
2454 l_ecs_request_id.organization_id (l_num_cnt);
2455
2456 BEGIN
2457 IF g_chr_debug = 'Y'
2458 THEN
2459 fnd_file.put_line (fnd_file.LOG
2460 , 'Waiting for Request id:'
2461 || l_num_request_id
2462 || ' to complete.');
2463 END IF;
2464
2465 --
2466 -- Waiting for the Concurrent Program to complete
2467 --
2468 l_bool_wait_return :=
2469 fnd_concurrent.wait_for_request
2470 (l_num_request_id
2471 , --the request id
2472 l_num_interval
2473 , --interval between checks
2474 l_num_max_wait
2475 , --max wait time
2476 l_chr_phase
2477 , --OUT; request phase
2478 l_chr_status
2479 , --OUT; request status
2480 l_chr_dev_phase
2481 , --OUT; request phase (dev)
2482 l_chr_dev_status
2483 ,
2484 --OUT; request status (dev)
2485 l_chr_message);
2486
2487 --OUT; request completion message
2488 IF ( UPPER (l_chr_dev_phase) = 'COMPLETE'
2489 AND UPPER (l_chr_dev_status) = 'NORMAL')
2490 THEN
2491 IF g_chr_debug = 'Y'
2492 THEN
2493 fnd_file.put_line
2494 (fnd_file.LOG
2495 , 'Assembly Item :'
2496 || l_num_assembly_item_id
2497 || 'assigned to inventory Organization :'
2498 || l_num_to_organization_id
2499 || ' successfully');
2500 END IF;
2501
2502 out_chr_retcod := g_chr_success_code;
2503 --
2504 --check if program completed with error
2505 --
2506 ELSIF ( UPPER (l_chr_dev_phase) = 'COMPLETE'
2507 AND ( UPPER (l_chr_dev_status) = 'ERROR'
2508 OR UPPER (l_chr_dev_status) = 'WARNING'))
2509 THEN
2510 IF g_chr_debug = 'Y'
2511 THEN
2512 fnd_file.put_line
2513 (fnd_file.LOG
2514 , 'Error in assigning Assembly Item :'
2515 || l_num_assembly_item_id
2516 || 'to inventory Organization :'
2517 || l_num_to_organization_id
2518 || ' for details please refere request id'
2519 || l_num_request_id);
2520 END IF;
2521
2522 out_chr_retcod := g_chr_failure_code;
2523 --
2524 --for any other exit phase and/or status, log the details
2525 --
2526 ELSE
2527 IF g_chr_debug = 'Y'
2528 THEN
2529 fnd_file.put_line (fnd_file.LOG
2530 , ' Program Phase: "'
2531 || l_chr_dev_phase
2532 || '"; Status: "'
2533 || l_chr_dev_status
2534 || '"');
2535 END IF;
2536
2537 out_chr_retcod := g_chr_warning_code;
2538 END IF;
2539 EXCEPTION
2540 WHEN OTHERS
2541 THEN
2542 fnd_file.put_line
2543 (fnd_file.LOG
2544 , 'Some unexpected error occured in assigning Assembly item'
2545 || l_num_assembly_item_id
2546 || 'to Inevntory Organization'
2547 || l_num_to_organization_id
2548 || ' Error:'
2549 || SQLERRM);
2550 END;
2551
2552 l_num_cnt := l_num_cnt + 1;
2553 END LOOP;
2554 EXCEPTION
2555 WHEN OTHERS
2556 THEN
2557 out_chr_error_msg :=
2558 'Error in xxecs_mws_bom_creation_pkg.XXECS_BOM_ASSIGN_INVORG_PROC'
2559 || SQLERRM;
2560 out_chr_retcod := g_chr_failure_code;
2561 END xxecs_bom_assign_invorg_proc;
2562
2563 PROCEDURE xxecs_config_auto_proc (
2564 out_chr_errbuff OUT VARCHAR2
2565 ,out_chr_retcode OUT VARCHAR2
2566 ,in_num_header_id IN NUMBER
2567 ,in_num_organization_id IN NUMBER) --Added 1.0 by Binay
2568 AS
2569 l_num_request_id NUMBER;
2570 l_num_max_wait NUMBER := 60 * 60 * 24;
2571 l_bool_wait_return BOOLEAN;
2572 l_num_interval NUMBER := 1;
2573 l_chr_phase VARCHAR2 (80);
2574 l_chr_status VARCHAR2 (80);
2575 l_chr_dev_phase VARCHAR2 (30);
2576 l_chr_dev_status VARCHAR2 (30);
2577 l_chr_message VARCHAR2 (1000);
2578 l_num_conc_request_id NUMBER := 0;
2579 l_num_organization_id NUMBER := in_num_organization_id; --Added 1.0 by Binay
2580 exc_error_exit EXCEPTION;
2581
2582 CURSOR c_fetch_org_details
2583 IS
2584 SELECT flv.description description
2585 ,mp.organization_id organization_id
2586 FROM fnd_lookup_values flv
2587 ,mtl_parameters mp
2588 WHERE flv.lookup_type = 'XXECS_DIST_VENCFG_ORGANIZATION'
2589 AND flv.enabled_flag = 'Y'
2590 AND flv.attribute1 = g_num_operating_unit_id
2591 AND NVL (flv.start_date_active
2592 , SYSDATE - 1) < SYSDATE
2593 AND NVL (flv.end_date_active
2594 , SYSDATE + 1) > SYSDATE
2595 AND flv.attribute2 <> fnd_profile.VALUE ('XXECS_DIST_ITEM_ORGANIZATION') --Added back the orginal code Aug-066-15 by Binay 1.3
2596 --l_num_organization_id --Added by Binay 1.0 l_num_organization_id --Removed By Binay Aug-06-15
2597 -- fnd_profile.VALUE ('XXECS_DIST_ITEM_ORGANIZATION') --commented by Binay 1.0
2598 AND mp.organization_code = flv.description
2599 AND flv.description NOT IN ('USV', 'PS');
2600
2601 CURSOR c_config_item_details (
2602 l_organization_id NUMBER)
2603 IS
2604 SELECT msi.segment1 || '|' || msi.segment2 bom_item
2605 FROM mtl_system_items_b msi
2606 ,xxecs_mws_config_line_ad eqc
2607 WHERE msi.inventory_item_id = eqc.ato_model_item_id
2608 AND eqc.header_id = in_num_header_id
2609 AND eqc.ato_model_item_id IS NOT NULL
2610 AND msi.organization_id = l_organization_id;
2611 BEGIN
2612 FOR c_fetch_org_details_rec IN c_fetch_org_details
2613 LOOP
2614 FOR c_config_item_details_rec IN
2615 c_config_item_details
2616 (c_fetch_org_details_rec.organization_id)
2617 LOOP
2618 --
2619 -- Launching the Ascent AutoConfiguration Models Concurrent Program*/
2620 --
2621 IF g_chr_debug = 'Y'
2622 THEN
2623 fnd_file.put_line
2624 (fnd_file.LOG
2625 ,'Launching the Concurrent Program - Ascent AutoConfiguration Models');
2626 END IF;
2627
2628 BEGIN
2629 l_num_request_id :=
2630 fnd_request.submit_request
2631 (application => 'XXECS_DIST'
2632 ,program => 'ASCCONMOD'
2633 ,start_time => NULL
2634 ,sub_request => FALSE
2635 ,argument1 => c_fetch_org_details_rec.description
2636 ,argument2 => c_fetch_org_details_rec.organization_id
2637 ,argument3 => c_config_item_details_rec.bom_item);
2638
2639 IF g_chr_debug = 'Y'
2640 THEN
2641 fnd_file.put_line
2642 (fnd_file.LOG
2643 , 'Concurrent Program Request id:'
2644 || l_num_request_id);
2645 END IF;
2646
2647 COMMIT;
2648
2649 IF g_chr_debug = 'Y'
2650 THEN
2651 fnd_file.put_line (fnd_file.LOG
2652 , 'Waiting for Request id:'
2653 || l_num_request_id
2654 || ' to complete.');
2655 END IF;
2656
2657 l_bool_wait_return :=
2658 fnd_concurrent.wait_for_request (l_num_request_id
2659 ,l_num_interval
2660 ,l_num_max_wait
2661 ,l_chr_phase
2662 ,l_chr_status
2663 ,l_chr_dev_phase
2664 ,l_chr_dev_status
2665 ,l_chr_message);
2666
2667 --Request completion message
2668 IF ( UPPER (l_chr_dev_phase) = 'COMPLETE'
2669 AND UPPER (l_chr_dev_status) = 'NORMAL')
2670 THEN
2671 IF g_chr_debug = 'Y'
2672 THEN
2673 fnd_file.put_line
2674 (fnd_file.LOG
2675 ,'Ascent AutoConfiguration Models Program completed successfully.');
2676 END IF;
2677
2678 out_chr_retcode := g_chr_success_code;
2679 --
2680 --Check if program completed with error
2681 --
2682 ELSIF ( UPPER (l_chr_dev_phase) = 'COMPLETE'
2683 AND UPPER (l_chr_dev_status) = 'ERROR')
2684 THEN
2685 IF g_chr_debug = 'Y'
2686 THEN
2687 fnd_file.put_line
2688 (fnd_file.LOG
2689 ,' Ascent AutoConfiguration Models Program completed with error.');
2690 END IF;
2691
2692 out_chr_retcode := g_chr_failure_code;
2693 --
2694 --For any other exit phase and/or status
2695 --
2696 ELSE
2697 IF g_chr_debug = 'Y'
2698 THEN
2699 fnd_file.put_line (fnd_file.LOG
2700 , ' Program Phase: "'
2701 || l_chr_dev_phase
2702 || '"; Status: "'
2703 || l_chr_dev_status
2704 || '"');
2705 END IF;
2706
2707 out_chr_retcode := g_chr_warning_code;
2708 END IF;
2709 EXCEPTION
2710 WHEN OTHERS
2711 THEN
2712 out_chr_errbuff :=
2713 'Error while launching Ascent AutoConfiguration Models: '
2714 || SQLERRM;
2715 out_chr_retcode := g_chr_failure_code;
2716 RAISE exc_error_exit;
2717 END;
2718 END LOOP;
2719 END LOOP;
2720 EXCEPTION
2721 WHEN OTHERS
2722 THEN
2723 out_chr_errbuff :=
2724 'Error occured in xxecs_mws_bom_creation_pkg.xxecs_config_auto_proc';
2725 out_chr_retcode := g_chr_failure_code;
2726
2727 IF g_chr_debug = 'Y'
2728 THEN
2729 fnd_file.put_line
2730 (fnd_file.LOG
2731 , 'Error occured in xxecs_mws_bom_creation_pkg.xxecs_config_auto_proc '
2732 || SQLERRM);
2733 END IF;
2734
2735 RAISE exc_error_exit;
2736 END xxecs_config_auto_proc;
2737--
2738-- START ADDED in Package Version 1.2
2739--
2740
2741PROCEDURE xxecs_bom_status_log_proc ( p_out_chr_errbuff OUT NOCOPY VARCHAR2,
2742 p_out_chr_retcode OUT NOCOPY VARCHAR2,
2743 p_in_num_header_id IN NUMBER
2744 )
2745AS
2746/********************************************************************************************************************
2747* Type : Procedure *
2748* Name : XXECS_BOM_STATUS_LOG_PROC *
2749* Input Parameters : in_chr_po_number *
2750* Output Parameters : *
2751* Purpose : This procedure prints the log messages of BOM Status in output file *
2752* Company : Infosys Technologies Ltd. *
2753* Created By : Infosys(Kulbir Kaur) *
2754* Created Date : 05-AUG-2009 *
2755* Last Reviewed By : Infosys(Param) *
2756* Last Reviewed Date : 05-AUG-2009 *
2757*********************************************************************************************************************
2758* Modified Date Modified By Version Reviewed By Reviewed Date Modification Type Modification Details *
2759* ------------- ------------ -------- --------------- -------------- ----------------- -------------------------- *
2760* 05-AUG-2009 Infosys(Kulbir) 0.0 Infosys(Param) 05-AUG-2009 O Created. *
2761********************************************************************************************************************/
2762
2763--Exception Declaration
2764l_exc_exit_error EXCEPTION;
2765l_chr_errmsg VARCHAR2 (1000);
2766PRAGMA EXCEPTION_INIT(l_exc_exit_error, -10101);
2767l_chr_config_file VARCHAR2(1000);
2768l_num_fail_count NUMBER := 0;
2769l_num_success_count NUMBER := 0;
2770
2771-- Declaration of cursor
2772CURSOR c_bom_status_detls
2773IS
2774SELECT NVL(eqcl.model_number,eqcl.part_no) model_number
2775 ,DECODE (eqcl.bom_created_flag,'Y','SUCCESS','N','FAILED','NOT-PROCESSED') status
2776 ,msib.segment1||'|'||msib.segment2 ato_item_name
2777 FROM xxecs_mws_config_line_ad eqcl
2778 ,mtl_system_items_b msib
2779 WHERE eqcl.header_id = p_in_num_header_id
2780 AND msib.inventory_item_id(+) = eqcl.ato_model_item_id
2781 AND msib.organization_id(+) = fnd_profile.VALUE ('XXECS_DIST_ITEM_ORGANIZATION')
2782 AND NVL (eqcl.removal_flag
2783 ,'N') = 'N';
2784 /*AND NOT EXISTS (
2785 SELECT ottt.transaction_type_id line_type_id
2786 FROM oe_transaction_types_tl ottt
2787 ,fnd_lookup_values_vl flv
2788 WHERE flv.lookup_type = 'XXECS_DIST_VENCFG_DROPSHIP'
2789 AND flv.enabled_flag = 'Y'
2790 AND ottt.NAME = flv.description
2791 AND flv.tag = 'DROPSHIP'
2792 AND eqcl.line_type_id = ottt.transaction_type_id
2793 AND NVL (flv.start_date_active
2794 , SYSDATE - 1) < SYSDATE
2795 AND NVL (flv.end_date_active
2796 , SYSDATE + 1) > SYSDATE)
2797 AND EXISTS (
2798 SELECT 1
2799 FROM fnd_lookup_values_vl flv
2800 WHERE flv.lookup_type = 'XXECS_DIST_VENCFG_LINE_TYPE'
2801 AND flv.enabled_flag = 'Y'
2802 AND UPPER (flv.description) IN
2803 ('INTEGRATION', 'MIXED')
2804 AND ( eqcl.line_type_id = flv.attribute3
2805 OR eqcl.line_type_id = flv.attribute4
2806 OR eqcl.line_type_id = flv.attribute5
2807 )
2808 AND attribute1 = eqcl.org_id
2809 AND NVL (start_date_active
2810 , SYSDATE - 1) < SYSDATE
2811 AND NVL (end_date_active
2812 , SYSDATE + 1) > SYSDATE);*/
2813--
2814-- Actual code begins here
2815--
2816BEGIN
2817 p_out_chr_retcode:= g_chr_success_code;
2818 p_out_chr_errbuff := NULL;
2819
2820 BEGIN
2821 SELECT eqhl.config_file||'-'||eqhl.config_id||'-'||eqhl.version_no||'-'||eqhl.revision config_file
2822 INTO l_chr_config_file
2823 FROM xxecs_mws_config_hdr_ad eqhl
2824 WHERE eqhl.header_id = p_in_num_header_id;
2825 EXCEPTION
2826 WHEN OTHERS
2827 THEN
2828 fnd_file.put_line(fnd_file.LOG,'Error while fetching config file from xxecs_mws_config_hdr_ad');
2829 l_chr_errmsg := 'Error while fetching config file from xxecs_mws_config_hdr_ad:'|| SQLERRM;
2830 RAISE l_exc_exit_error;
2831 END;
2832
2833 fnd_file.new_line (fnd_file.output
2834 ,2);
2835
2836 fnd_file.put_line (fnd_file.OUTPUT,'---------------------------------------------'
2837 ||'-------------------------------------------'
2838 );
2839 fnd_file.put_line (fnd_file.OUTPUT,'PROCESSING BOM STATUS FOR CONFIG FILE: '||l_chr_config_file);
2840
2841 BEGIN
2842 SELECT COUNT(1)
2843 INTO l_num_success_count
2844 FROM xxecs_mws_config_line_ad eqcl
2845 WHERE eqcl.header_id = p_in_num_header_id
2846 AND eqcl.bom_created_flag = 'Y';
2847 EXCEPTION
2848 WHEN OTHERS
2849 THEN
2850 fnd_file.put_line(fnd_file.LOG,'Error while counting SUCCESS records from xxecs_mws_config_line_ad');
2851 l_chr_errmsg := 'Error while deriving SUCCESS records from xxecs_mws_config_line_ad:'|| SQLERRM;
2852 RAISE l_exc_exit_error;
2853 END;
2854
2855 BEGIN
2856 SELECT COUNT(1)
2857 INTO l_num_fail_count
2858 FROM xxecs_mws_config_line_ad eqcl
2859 WHERE eqcl.header_id = p_in_num_header_id
2860 AND NVL(eqcl.bom_created_flag,'X') = 'N';
2861 EXCEPTION
2862 WHEN OTHERS
2863 THEN
2864 fnd_file.put_line(fnd_file.LOG,'Error while counting FAILED records from xxecs_mws_config_line_ad');
2865 l_chr_errmsg := 'Error while deriving FAILED records from xxecs_mws_config_line_ad:'|| SQLERRM;
2866 RAISE l_exc_exit_error;
2867 END;
2868 fnd_file.put_line (fnd_file.OUTPUT,'TOTAL NUMBER OF BOM SUCCESSFULLY CREATED: '
2869 ||l_num_success_count
2870 );
2871
2872 fnd_file.put_line (fnd_file.OUTPUT,'TOTAL NUMBER OF BOM FAILED: '
2873 ||l_num_fail_count
2874 );
2875 fnd_file.new_line (fnd_file.output
2876 ,2);
2877
2878 fnd_file.put_line (fnd_file.OUTPUT,'MODEL NUMBER'|| CHR(9)|| CHR(9)|| CHR(9)
2879 ||'ATO MODEL ITEM'||CHR(9)|| CHR(9)|| CHR(9)
2880 ||'BOM_STATUS'
2881 );
2882
2883 fnd_file.put_line (fnd_file.OUTPUT,'==========================================='
2884 ||'==========================================='
2885 );
2886
2887 FOR c_bom_status_detls_rec IN c_bom_status_detls
2888 LOOP
2889
2890 fnd_file.put_line (fnd_file.OUTPUT,c_bom_status_detls_rec.model_number
2891 || CHR(9)|| CHR(9)|| CHR(9)
2892 ||c_bom_status_detls_rec.ato_item_name
2893 || CHR(9)|| CHR(9)|| CHR(9)
2894 ||c_bom_status_detls_rec.status
2895 );
2896 END LOOP;
2897
2898EXCEPTION
2899 WHEN l_exc_exit_error
2900 THEN
2901 p_out_chr_errbuff := 'ERROR while fetching status details for Config File: '
2902 ||l_chr_config_file
2903 ||' ERROR: '
2904 ||l_chr_errmsg;
2905 p_out_chr_retcode := g_chr_warning_code;
2906 WHEN OTHERS
2907 THEN
2908 p_out_chr_errbuff := 'ERROR while fetching status details for Config File: '
2909 ||l_chr_config_file
2910 ||' ERROR: '
2911 ||SQLERRM;
2912 p_out_chr_retcode := g_chr_warning_code;
2913END xxecs_bom_status_log_proc;
2914
2915-- END ADDED in Package Version 1.2
2916
2917END xxecs_mws_bom_creation_pkg;