[Home] [Help]
PACKAGE BODY: APPS.BOM_IMPORT_PUB
Source
1 PACKAGE BODY Bom_Import_Pub AS
2 /* $Header: BOMSIMPB.pls 120.94 2007/10/16 05:45:40 dikrishn ship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- BOMSIMPB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package Bom_Import_Pub
15 --
16 -- NOTES
17 --
18 --
19 --
20 -- HISTORY
21 --
22 -- 04-May-2005 Sreejith Nelloliyil Initial Creation
23 --
24 -- 05-May-2005 Dinu Krishnan Created hte APIs
25 -- 1.RESOLVE_XREFS_FOR_BATCH
26 -- 2.Update Match Data
27 -- 3.Update Bill Info
28 -- 4.Check Component Exist
29 -- 07-May-2005 Sreejith Nelloliyil Added Code for data separation
30 --
31 BULKLOAD_PVT_PKG.PROCESS_BOM_INTERFACE_LINES
32 ***************************************************************************/
33 /* Package Globals */
34 pG_batch_options BATCH_OPTIONS;
35 pG_ouputFileName VARCHAR2(30) := 'BOM_IMPORT_PUB';
36
37 /****************** Local functions/procedures Section ******************/
38
39 FUNCTION Init_Debug RETURN BOOLEAN
40 IS
41 CURSOR c_get_utl_file_dir IS
42 SELECT VALUE
43 FROM V$PARAMETER
44 WHERE NAME = 'utl_file_dir';
45 l_debug_file VARCHAR2(80) := pG_ouputFileName||TO_CHAR(SYSDATE,'DDMONYYHH24MISS');
46
47 l_out_dir VARCHAR2(240);
48 l_message_list Error_Handler.Error_Tbl_Type;
49 l_debug_error_status VARCHAR2(1);
50 l_debug_error_mesg VARCHAR2(2000);
51
52 BEGIN
53 IF Error_Handler.Get_Debug <> 'Y'
54 THEN
55 OPEN c_get_utl_file_dir;
56 FETCH c_get_utl_file_dir INTO l_out_dir;
57 IF c_get_utl_file_dir%FOUND THEN
58 ------------------------------------------------------
59 -- Trim to get only the first directory in the list --
60 ------------------------------------------------------
61 IF INSTR(l_out_dir,',') <> 0 THEN
62 l_out_dir := SUBSTR(l_out_dir, 1, INSTR(l_out_dir, ',') - 1);
63 END IF;
64 END IF;
65 Error_Handler.Initialize;
66 Error_Handler.Set_Debug ('Y');
67 Bom_Globals.Set_Debug ('Y');
68
69 Error_Handler.Open_Debug_Session
70 ( p_debug_filename => l_debug_file
71 , p_output_dir => l_out_dir
72 , x_return_status => l_debug_error_status
73 , x_error_mesg => l_debug_error_mesg
74 );
75 ELSE
76 l_debug_error_status := 'S';
77 END IF;
78
79 IF l_debug_error_status <> 'S'
80 THEN
81 RETURN FALSE;
82 END IF;
83
84 RETURN TRUE;
85 END Init_Debug;
86
87 FUNCTION Check_Header_Exists
88 (
89 p_parent_id IN NUMBER,
90 p_org_id IN NUMBER,
91 p_str_name IN VARCHAR2
92 )RETURN NUMBER
93 IS
94 l_bill_seq_id NUMBER;
95 l_item_id NUMBER;
96 l_org_id NUMBER;
97
98 CURSOR Get_Bill_Details
99 IS
100 SELECT bill_sequence_id
101 FROM bom_structures_b
102 WHERE assembly_item_id = p_parent_id
103 AND organization_id = p_org_id
104 AND nvl(alternate_bom_designator,'Primary') = nvl(p_str_name,'Primary');
105
106 BEGIN
107
108 SELECT bill_sequence_id
109 INTO l_bill_seq_id
110 FROM bom_structures_b
111 WHERE assembly_item_id = p_parent_id
112 AND organization_id = p_org_id
113 AND nvl(alternate_bom_designator,'Primary') = nvl(p_str_name,'Primary');
114
115 RETURN l_bill_seq_id;
116
117 EXCEPTION WHEN NO_DATA_FOUND THEN
118 RETURN null;
119
120 END Check_Header_Exists;
121
122 /**
123 * This API will process the reference designators for an All
124 * Components Batch.It will compare the reference designators in the
125 * interface structure and those in target PIMDH structure and disable those
126 * reference designators that are not mentioned in the batch.
127 */
128 PROCEDURE Disable_Refds
129 (
130 p_batch_id IN NUMBER
131 , p_comp_seq_id IN NUMBER
132 , p_comp_id IN NUMBER
133 , p_parent_id IN NUMBER
134 , p_eff_date IN DATE
135 , p_op_seq_num IN NUMBER
136 , p_org_id IN NUMBER
137 )
138 IS
139 CURSOR Get_Src_RefDs
140 IS
141 SELECT *
142 FROM bom_ref_desgs_interface
143 WHERE batch_id = p_batch_id
144 AND (component_sequence_id = p_comp_seq_id
145 OR (component_item_id = p_comp_id
146 AND organization_id = p_org_id
147 AND assembly_item_id = p_parent_id
148 AND nvl(effectivity_date,sysdate) = nvl(p_eff_date,sysdate)
149 AND nvl(operation_seq_num,1) = nvl(p_op_seq_num,1)
150 )
151 )
152 ORDER BY component_reference_designator;
153
154 CURSOR Get_PIMDH_RefDs
155 IS
156 SELECT *
157 FROM bom_reference_designators
158 WHERE component_sequence_id = p_comp_seq_id
159 ORDER BY component_reference_designator;
160
161 TYPE ref_intf_type IS TABLE OF bom_ref_desgs_interface%ROWTYPE;
162 TYPE ref_pimdh_type IS TABLE OF bom_reference_designators%ROWTYPE;
163
164 l_src_refds ref_intf_type;
165 l_pimdh_refds ref_pimdh_type;
166 l_src_count NUMBER;
167 l_pimdh_count NUMBER;
168 l_delete BOOLEAN;
169
170 BEGIN
171
172
173 OPEN Get_PIMDH_RefDs;
174 FETCH Get_PIMDH_RefDs BULK COLLECT INTO l_pimdh_refds;
175 CLOSE Get_PIMDH_RefDs;
176
177
178
179 OPEN Get_Src_RefDs;
180 FETCH Get_Src_RefDs BULK COLLECT INTO l_src_refds;
181 CLOSE Get_Src_RefDs;
182
183 l_src_count := l_src_refds.COUNT;
184 l_pimdh_count := l_pimdh_refds.COUNT;
185
186
187 IF l_src_count >= l_pimdh_count THEN
188 FOR i in 1..l_src_count LOOP
189 FOR j in 1..l_pimdh_count LOOP
190 IF l_pimdh_refds(j).component_reference_designator = l_src_refds(i).component_reference_designator THEN
191 l_pimdh_refds(j).attribute1 := 'Y';
192 END IF;
193 END LOOP; -- pimdh loop
194 END LOOP; -- src loop
195 FOR i in 1..l_pimdh_count LOOP
196 IF nvl(l_pimdh_refds(i).attribute1,'N') <> 'Y' THEN
197 INSERT INTO bom_ref_desgs_interface
198 (
199 COMPONENT_REFERENCE_DESIGNATOR,
200 REF_DESIGNATOR_COMMENT,
201 CHANGE_NOTICE,
202 COMPONENT_SEQUENCE_ID,
203 batch_id,
204 transaction_type,
205 process_flag,
206 component_item_id,
207 assembly_item_id,
208 organization_id
209 )
210 VALUES
211 (
212 l_pimdh_refds(i).component_reference_designator,
213 l_pimdh_refds(i).REF_DESIGNATOR_COMMENT,
214 l_pimdh_refds(i).CHANGE_NOTICE,
215 l_pimdh_refds(i).component_sequence_id,
216 p_batch_id,
217 'DELETE',
218 1,
219 p_comp_id,
220 p_parent_id,
221 p_org_id
222 );
223 END IF;
224 END LOOP;
225 ELSE
226
227 FOR i in 1..l_pimdh_count LOOP
228 l_delete := true;
229 FOR j in 1..l_src_count LOOP
230 IF l_src_refds(j).component_reference_designator = l_pimdh_refds(i).component_reference_designator THEN
231 l_delete := false;
232 END IF;
233 END LOOP; -- pimdh loop
234 IF l_delete THEN
235 INSERT INTO bom_ref_desgs_interface
236 (
237 COMPONENT_REFERENCE_DESIGNATOR,
238 REF_DESIGNATOR_COMMENT,
239 CHANGE_NOTICE,
240 COMPONENT_SEQUENCE_ID,
241 batch_id,
242 transaction_type,
243 process_flag,
244 component_item_id,
245 assembly_item_id,
246 organization_id
247 )
248 VALUES
249 (
250 l_pimdh_refds(i).component_reference_designator,
251 l_pimdh_refds(i).REF_DESIGNATOR_COMMENT,
252 l_pimdh_refds(i).CHANGE_NOTICE,
253 l_pimdh_refds(i).component_sequence_id,
254 p_batch_id,
255 'DELETE',
256 1,
257 p_comp_id,
258 p_parent_id,
259 p_org_id
260 );
261 END IF;
262 END LOOP; -- src loop
263 END IF; --src_count > pimdh_count
264
265 END Disable_Refds;
266
267
268 FUNCTION Header_Not_In_Intf
269 (
270 p_bill_seq_id IN NUMBER,
271 p_item_id IN NUMBER,
272 p_org_id IN NUMBER,
273 p_str_name IN VARCHAR2,
274 p_batch_id IN NUMBER,
275 p_request_id IN NUMBER,
276 p_bundle_id IN NUMBER
277 )RETURN BOOLEAN
278 IS
279 l_temp VARCHAR2(250);
280 BEGIN
281
282 SELECT item_number
283 INTO l_temp
284 FROM bom_bill_of_mtls_interface BBMI,mtl_system_items_vl MSIVL,mtl_parameters MP,bom_structures_b BSB
285 WHERE BBMI.batch_id = p_batch_id
286 AND BSB.bill_sequence_id = p_bill_seq_id
287 AND (BBMI.process_flag = 1 OR BBMI.process_flag = 5 )
288 AND (( BBMI.request_id IS NOT NULL AND BBMI.request_id = p_request_id ) OR (BBMI.bundle_id IS NOT NULL AND BBMI.bundle_id = p_bundle_id))
289 AND ( BBMI.bill_sequence_id = p_bill_seq_id OR
290 ( (BBMI.assembly_item_id = p_item_id OR BBMI.item_number = MSIVL.concatenated_segments OR BBMI.source_system_reference = MSIVL.concatenated_segments )
291 AND (BBMI.organization_id = p_org_id OR BBMI.organization_code = MP.organization_code)
292 AND NVL(BBMI.alternate_bom_designator,'Primary') = NVL(p_str_name,'Primary')
293 )
294 )
295 AND MSIVL.inventory_item_id = p_item_id
296 AND MSIVl.organization_id = p_org_id
297 AND MP.organization_id = p_org_id;
298
299 IF l_temp IS NOT NULL THEN
300 RETURN true;
301 END IF;
302
303 EXCEPTION WHEN NO_DATA_FOUND THEN
304 RETURN FALSE;
305
306 END Header_Not_In_Intf;
307
308 Procedure write_debug
309 (
310 p_message in VARCHAR2
311 )
312 IS
313 l_debug BOOLEAN := Init_Debug();
314 BEGIN
315 IF l_debug = true THEN
316 Error_Handler.write_debug(p_message);
317 END IF;
318 END;
319
320 --Update any rows with null transactionids with proper sequence
321 --Only updates the rows with null txn ids and processflag 1
322 Procedure update_transaction_ids
323 (
324 p_batch_id IN NUMBER
325 )
326 is
327 BEGIN
328 update
329 BOM_BILL_OF_MTLS_INTERFACE
330 set
331 transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
332 where
333 transaction_id is null
334 and batch_id = p_batch_id
335 and process_flag = 1;
336
337 update
338 BOM_INVENTORY_COMPS_INTERFACE
339 set
340 transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
341 where
342 transaction_id is null
343 and batch_id = p_batch_id
344 and process_flag = 1;
345
346 update
347 BOM_SUB_COMPS_INTERFACE
348 set
349 transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
350 where
351 transaction_id is null
352 and batch_id = p_batch_id
353 and process_flag = 1;
354
355 update
356 BOM_REF_DESGS_INTERFACE
357 set
358 transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
359 where
360 transaction_id is null
361 and batch_id = p_batch_id
362 and process_flag = 1;
363 END update_transaction_ids;
364
365 --remove this
366 FUNCTION Does_Batch_Exist
367 (
368 p_batch_id IN NUMBER
369 )
370 RETURN BOOLEAN
371 IS
372 l_dummy VARCHAR2(20);
373 BEGIN
374 IF p_batch_id IS NOT NULL
375 THEN
376 BEGIN
377 SELECT 'Exist'
378 INTO l_dummy
379 FROM EGO_IMPORT_BATCHES_B
380 WHERE batch_id = p_batch_id
381 AND batch_type = 'BOM_STRUCTURE';
382
383 EXCEPTION
384 WHEN NO_DATA_FOUND THEN
385 RETURN FALSE;
386 END;
387 END IF;
388 RETURN TRUE;
389 END Does_Batch_Exist;
390 --remove this
391
392 /* Setting the Rows for Enabling Change Management APIs to Pickup
393 */
394 PROCEDURE Process_Batch_Options
395 (p_batch_id IN NUMBER)
396 IS
397 BEGIN
398 --Update Structure Name:Start
399 IF (pg_batch_options.structure_name IS NOT NULL
400 AND pg_batch_options.structure_name <> bom_globals.get_primary_ui)
401 THEN
402 UPDATE bom_bill_of_mtls_interface
403 SET alternate_bom_designator = pg_batch_options.structure_name
404 WHERE batch_id = p_batch_id
405 AND (process_flag = 1 OR process_flag = 5);
406 -- AND alternate_bom_designator IS NOT NULL;
407
408 UPDATE bom_inventory_comps_interface
409 SET alternate_bom_designator = pg_batch_options.structure_name
410 WHERE batch_id = p_batch_id
411 AND ( process_flag = 1 OR process_flag = 5);
412 -- AND alternate_bom_designator IS NOT NULL;
413 END IF;
414 --Update Structure Name:End
415
416 IF pg_batch_options.structure_type_id IS NOT NULL
417 THEN
418 UPDATE bom_bill_of_mtls_interface
419 SET structure_type_id = pg_batch_options.structure_type_id
420 WHERE batch_id = p_batch_id
421 AND (process_flag = 1 OR process_flag = 5)
422 AND structure_type_id IS NULL;
423
424 UPDATE bom_bill_of_mtls_interface
425 SET structure_type_name = (SELECT STV1.structure_type_name
426 FROM bom_structure_types_vl STV1 where
427 STV1.structure_type_id = pg_batch_options.structure_type_id)
428 WHERE batch_id = p_batch_id
429 AND structure_type_name IS NULL
430 AND (process_flag = 1 OR process_flag = 5)
431 AND exists (select STV2.structure_type_name from bom_structure_types_vl STV2
432 WHERE STV2.structure_type_id = pg_batch_options.structure_type_id);
433
434 END IF;
435
436
437 --Update Effectivity Details:Start
438 IF ( pg_batch_options.STRUCTURE_EFFECTIVITY_TYPE = 1 ) THEN
439 UPDATE
440 bom_bill_of_mtls_interface
441 SET
442 EFFECTIVITY_CONTROL = 1
443 WHERE
444 batch_id = p_batch_id
445 AND (process_flag = 1 OR process_flag = 5)
446 AND EFFECTIVITY_CONTROL is NULL;
447
448 IF (pg_batch_options.EFFECTIVITY_DATE IS NOT NULL) THEN
449 UPDATE
450 bom_inventory_comps_interface
451 SET
452 EFFECTIVITY_DATE = pg_batch_options.EFFECTIVITY_DATE
453 WHERE
454 EFFECTIVITY_DATE IS NULL
455 AND BATCH_ID = P_BATCH_ID
456 AND EFFECTIVITY_DATE IS NULL
457 AND (PROCESS_FLAG = 1 OR PROCESS_FLAG =5); --Check New effectivity date
458 END IF;
459 ELSIF (pg_batch_options.STRUCTURE_EFFECTIVITY_TYPE = 2) THEN
460 UPDATE
461 bom_bill_of_mtls_interface
462 SET
463 EFFECTIVITY_CONTROL = 2
464 WHERE
465 batch_id = p_batch_id
466 AND (process_flag = 1 OR process_flag = 5)
467 AND EFFECTIVITY_CONTROL is NULL;
468 IF (pg_batch_options.FROM_END_ITEM_UNIT_NUMBER IS NOT NULL) THEN
469 UPDATE
470 bom_inventory_comps_interface
471 SET
472 FROM_END_ITEM_UNIT_NUMBER = pg_batch_options.FROM_END_ITEM_UNIT_NUMBER
473 WHERE
474 FROM_END_ITEM_UNIT_NUMBER IS NULL
475 AND BATCH_ID = P_BATCH_ID
476 AND FROM_END_ITEM_UNIT_NUMBER IS NULL
477 AND (PROCESS_FLAG = 1 OR PROCESS_FLAG =5); --Check New effectivity date
478 END IF;
479 END IF;
480 --Update Effectivity Details:End
481 END Process_Batch_Options;
482
483
484
485 /* Start: Retrieve_Batch_Options Proecudre to retrieve batch option from EGO
486 * tables and store in pG_batch_options Global Variable. This checks whether
487 * the value isalready filled in prior to executing the query
488 */
489 /*PROCEDURE Retrieve_Batch_Options
490 (p_batch_id in number,
491 x_error_message OUT NOCOPY varchar2,
492 x_error_code OUT NOCOPY number)*/
493
494 PROCEDURE Retrieve_Batch_Options
495 ( p_batch_id IN NUMBER,
496 x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type,
497 x_error_code IN OUT NOCOPY VARCHAR2)
498 IS
499 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
500 l_Token_Tbl Error_Handler.Token_Tbl_Type;
501 CURSOR batch_options_cr IS
502 SELECT
503 b.SOURCE_SYSTEM_ID,
504 b.BATCH_TYPE,
505 b.ASSIGNEE,
506 b.BATCH_STATUS,
507 o.MATCH_ON_DATA_LOAD,
508 o.IMPORT_ON_DATA_LOAD,
509 nvl(o.IMPORT_XREF_ONLY,'N'),
510 o.STRUCTURE_TYPE_ID,
511 o.STRUCTURE_NAME,
512 o.STRUCTURE_EFFECTIVITY_TYPE,
513 o.EFFECTIVITY_DATE,
514 o.FROM_END_ITEM_UNIT_NUMBER,
515 o.STRUCTURE_CONTENT,
516 o.CHANGE_NOTICE,
517 NVL(o.CHANGE_ORDER_CREATION, 'I'), --I, ignore change,
518 DECODE(NVL(b.SOURCE_SYSTEM_ID,0), G_PDH_SRCSYS_ID, 'Y', 'N'),
519 o.add_all_to_change_flag
520 FROM
521 EGO_IMPORT_BATCHES_B b, ego_import_option_sets o
522 WHERE
523 b.BATCH_ID = o.BATCH_ID
524 AND b.BATCH_ID = p_batch_id;
525 BEGIN
526 IF (pg_batch_options.SOURCE_SYSTEM_ID IS NULL)
527 THEN
528 OPEN batch_options_cr;
529 FETCH batch_options_cr INTO pG_batch_options;
530 IF batch_options_cr%ROWCOUNT = 0
531 THEN
532 SELECT
533 G_PDH_SRCSYS_ID,
534 'BOM_STRUCTURE',
535 null,
536 'A',
537 null,
538 'Y',
539 'N',
540 103,
541 'PIM_PBOM_S',
542 1,
543 null,
544 null,
545 'C',
546 null,
547 'I', --I, ignore change,
548 'Y',
549 null
550 INTO
551 pg_batch_options
552 FROM
553 dual;
554 /*
555 l_Token_Tbl(1).token_name := 'BATCH_ID';
556 l_Token_Tbl(1).token_value := p_batch_id;
557 Error_Handler.Add_Error_Token
558 (
559 p_message_name => 'BOM_SOURCE_SYSTEM_INVALID'
560 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
561 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
562 , p_Token_Tbl => l_Token_Tbl
563 );
564 x_error_code := 'E';
565 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
566 */
567
568 END IF;
569 CLOSE batch_options_cr;
570 END IF;
571 x_error_code := 'S';
572 EXCEPTION
573 WHEN OTHERS THEN
574 l_Token_Tbl(1).token_name := 'BATCH_ID';
575 l_Token_Tbl(1).token_value := p_batch_id;
576 Error_Handler.Add_Error_Token
577 (
578 p_message_name => 'BOM_SOURCE_SYSTEM_INVALID'
579 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
580 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
581 , p_Token_Tbl => l_Token_Tbl
582 );
583 x_error_code := 'E';
584 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
585 END Retrieve_Batch_Options;
586 /* End: Retrieve_Batch_Options Proecudre */
587
588 FUNCTION CHECK_COMP_EXIST(
589 p_bill_seq_id IN NUMBER
590 , p_effec_control IN NUMBER
591 , p_batch_id IN NUMBER
592 , p_comp_rec_id IN VARCHAR2
593 , p_component_item_id IN NUMBER
594 , p_organization_id IN NUMBER
595 , p_parent_item_id IN NUMBER
596 )
597 RETURN NUMBER
598 IS
599 l_comp_seq_id NUMBER := 0;
600 TYPE comp_rec_type IS REF CURSOR;
601 l_src_attrs comp_rec_type;
602 l_comp_id NUMBER;
603 l_op_seq_num NUMBER;
604 l_effec_date DATE;
605 l_from_unit_number VARCHAR2(100);
606 BEGIN
607 IF p_effec_control = 1
608 THEN
609 /*OPEN l_src_attrs FOR
610 SELECT
611 component_item_id,
612 new_operation_seq_num,
613 new_effectivity_date
614 FROM
615 bom_inventory_comps_interface
616 WHERE
617 batch_id = p_batch_id AND comp_source_system_reference = p_comp_rec_id;*
618 FETCH l_src_attrs INTO l_comp_id,l_op_seq_num,l_effec_date;
619 CLOSE l_src_attrs;*/
620 BEGIN
621
622 SELECT BCB.component_sequence_id
623 INTO l_comp_seq_id
624 FROM bom_components_b BCB,bom_inventory_comps_interface BICI
625 WHERE BICI.batch_id = p_batch_id
626 AND (BICI.comp_source_system_reference = p_comp_rec_id OR BICI.component_item_number = p_comp_rec_id)
627 AND (BICI.process_flag = 1 or BICI.process_flag = 5 )
628 AND BCB.bill_sequence_id = p_bill_seq_id
629 AND BCB.component_item_id = p_component_item_id
630 AND BCB.operation_seq_num = nvl(BICI.new_operation_seq_num,BICI.operation_seq_num)
631 AND BCB.effectivity_date = nvl(BICI.new_effectivity_date,BICI.effectivity_date)
632 AND (( pg_batch_options.CHANGE_NOTICE IS NULL AND BCB.implementation_date IS NOT NULL)
633 OR(pg_batch_options.CHANGE_NOTICE IS NOT NULL AND BCB.implementation_date IS NULL AND BCB.change_notice = pg_batch_options.CHANGE_NOTICE));
634
635
636 EXCEPTION
637 WHEN NO_DATA_FOUND THEN
638 NULL;
639 END;
640 ELSIF p_effec_control = 2 THEN
641 /*OPEN l_src_attrs FOR SELECT component_item_id,operation_seq_num,from_end_item_unit_number
642 FROM bom_inventory_comps_interface
643 WHERE batch_id = p_batch_id AND comp_source_system_reference = p_comp_rec_id;
644 FETCH l_src_attrs INTO l_comp_id,l_op_seq_num,l_from_unit_number;
645 CLOSE l_src_attrs;*/
646 BEGIN
647 SELECT BCB.component_sequence_id
648 INTO l_comp_seq_id
649 FROM bom_components_b BCB,bom_inventory_comps_interface BICI
650 WHERE BICI.batch_id = p_batch_id
651 AND (BICI.comp_source_system_reference = p_comp_rec_id OR BICI.component_item_number = p_comp_rec_id)
652 AND (BICI.process_flag = 1 or BICI.process_flag = 5 )
653 AND BCB.bill_sequence_id = p_bill_seq_id
654 AND BCB.component_item_id = p_component_item_id
655 AND BCB.operation_seq_num = nvl(BICI.new_operation_seq_num,BICI.operation_seq_num)
656 AND BCB.from_end_item_unit_number = nvl(BICI.new_from_end_item_unit_number,BICI.from_end_item_unit_number)
657 AND (( pg_batch_options.CHANGE_NOTICE IS NULL AND BCB.implementation_date IS NOT NULL)
658 OR(pg_batch_options.CHANGE_NOTICE IS NOT NULL AND BCB.implementation_date IS NULL AND BCB.change_notice = pg_batch_options.CHANGE_NOTICE));
659
660 EXCEPTION
661 WHEN NO_DATA_FOUND THEN
662 NULL;
663 END;
664 ELSIF p_effec_control = 4 THEN
665 BEGIN
666 SELECT BCB.component_sequence_id
667 INTO l_comp_seq_id
668 FROM bom_components_b BCB,bom_inventory_comps_interface BICI,Mtl_Item_Revisions MIR
669 WHERE BICI.batch_id = p_batch_id
670 AND (BICI.comp_source_system_reference = p_comp_rec_id OR BICI.component_item_number = p_comp_rec_id)
671 AND (BICI.process_flag = 1 or BICI.process_flag = 5 )
672 AND BCB.bill_sequence_id = p_bill_seq_id
673 AND BCB.component_item_id = p_component_item_id
674 AND nvl(BCB.operation_seq_num,1) = nvl(BICI.new_operation_seq_num,1)
675 AND MIR.inventory_item_id = p_parent_item_id
676 AND MIR.organization_id = p_organization_id
677 AND MIR.revision = BICI.from_end_item_rev_code
678 AND BCB.from_end_item_rev_id = MIR.Revision_Id
679 AND (( pg_batch_options.CHANGE_NOTICE IS NULL AND BCB.implementation_date IS NOT NULL)
680 OR(pg_batch_options.CHANGE_NOTICE IS NOT NULL AND BCB.implementation_date IS NULL AND BCB.change_notice = pg_batch_options.CHANGE_NOTICE));
681 EXCEPTION
682 WHEN NO_DATA_FOUND THEN
683 NULL;
684 END;
685 END IF;
686 RETURN l_comp_seq_id;
687 END CHECK_COMP_EXIST;
688
689 FUNCTION Item_Exist_In_Mtl_Intf
690 (
691 p_ss_reference IN VARCHAR2
692 , p_batch_id IN NUMBER
693 , p_org_code IN VARCHAR2
694 , p_item_number IN VARCHAR2
695 , p_ss_desc IN VARCHAR2
696 , p_item_desc IN VARCHAR2
697 , p_org_id IN NUMBER
698 )
699 RETURN BOOLEAN
700 IS
701 l_dummy VARCHAR2(20);
702 BEGIN
703 SELECT
704 'Exist'
705 INTO
706 l_dummy
707 FROM
708 mtl_system_items_interface MSII
709 WHERE
710 MSII.set_process_id = p_batch_id
711 AND ( (MSII.source_system_reference = p_ss_reference AND MSII.source_system_reference_desc = p_ss_desc )
712 OR(MSII.item_number = p_item_number AND MSII.description = p_item_desc)
713 )
714 AND (MSII.organization_code = p_org_code OR MSII.organization_id = p_org_id)
715 AND process_flag = 1;
716
717 IF l_dummy IS NOT NULL
718 THEN
719 RETURN TRUE;
720 END IF;
721
722 EXCEPTION
723 WHEN NO_DATA_FOUND THEN
724 RETURN FALSE;
725 END Item_Exist_In_Mtl_Intf;
726
727 /****************** Local Procedures Section Ends ******************/
728 /*
729 * The Method that willl be invoked by JCP
730 */
731
732 PROCEDURE Process_Structure_Data
733 (
734 p_batch_id IN NUMBER
735 )
736 IS
737 l_errbuff VARCHAR2(3000);
738 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
739 l_retCode VARCHAR2(1);
740 BEGIN
741 Retrieve_Batch_Options(p_batch_id => p_batch_id,
742 x_Mesg_Token_Tbl => l_Mesg_Token_Tbl,
743 x_error_code => l_retcode);
744
745 Process_Batch_Options(p_batch_id => p_batch_id);
746 Process_CM_Options(p_batch_id => p_batch_id);
747 /*
748 * As this API is called from BOMJCP we need to explicitly commit the data.
749 */
750 commit;
751 END Process_Structure_Data;
752
753 /*
754 * The Main Method that willl be invoked by all external programs
755 */
756 PROCEDURE Process_Structure_Data
757 ( p_batch_id IN NUMBER,
758 p_resultfmt_usage_id IN NUMBER,
759 p_user_id IN NUMBER,
760 p_conc_request_id IN NUMBER,
761 p_language_code IN VARCHAR2,
762 p_start_upload IN VARCHAR2,
763 x_errbuff IN OUT NOCOPY VARCHAR2,
764 x_retcode IN OUT NOCOPY VARCHAR2
765 )
766 IS
767 l_ret_code VARCHAR2(2);
768 l_err_buff VARCHAR2(1000);
769 l_message_list Error_Handler.Error_Tbl_Type;
770 l_request_id NUMBER := 0;
771 l_mesg_token_tbl Error_Handler.Mesg_Token_Tbl_Type;
772 l_from_jcp VARCHAR2(1) := 'N';
773 l_start_upload VARCHAR(1) := p_start_upload;
774 G_EXC_SEV_QUIT_OBJECT EXCEPTION;
775 BEGIN
776 -- We will call the xisting EGO_BOM_BULKLOAD_PVT_PKG for
777 -- data separation. As an initial test we are passing
778 -- concurrent request id as not NULL to invoke the jcp
779 --logMessage_forsnell('Called Method process_structure_data with flag '|| p_start_upload);
780 write_debug('Retrieving the batch options');
781 Write_Debug('Procedure is being called from EGO p_start_upload' || p_start_upload);
782
783 update_transaction_ids(p_batch_id);
784
785 Retrieve_Batch_Options
786 (
787 p_batch_id => p_batch_id
788 , x_Mesg_Token_Tbl => l_mesg_token_tbl
789 , x_error_code => l_ret_code
790 );
791
792 --logMessage_forsnell('done doing batch options' || 459);
793
794 IF l_ret_code <> 'S' THEN
795 RAISE G_EXC_SEV_QUIT_OBJECT;
796 END IF;
797
798 --J represents from JCP to avoid cyclic calls
799 IF (p_start_upload = 'J')
800 THEN
801 l_from_jcp := 'Y';
802 IF (pG_batch_options.IMPORT_ON_DATA_LOAD = 'Y')
803 THEN
804 l_start_upload := 'T';
805 END IF;
806 END IF;
807
808 --logMessage_forsnell('reaching the process structure data call pG_batch_options.SOURCE_SYSTEM_ID' || pG_batch_options.SOURCE_SYSTEM_ID);
809
810 /* only if rfusageid is not null, we should do data separation */
811 IF p_resultfmt_usage_id IS NOT NULL
812 THEN
813 BOM_BULKLOAD_PVT_PKG.PROCESS_BOM_INTERFACE_LINES
814 (
815 p_batch_id => p_batch_id,
816 p_resultfmt_usage_id => p_resultfmt_usage_id,
817 p_user_id => p_user_id,
818 p_conc_request_id => p_conc_request_id ,
819 p_language_code => p_language_code,
820 p_is_pdh_batch => pG_batch_options.PDH_BATCH,
821 x_errbuff => l_err_buff,
822 x_retcode => l_ret_code
823 );
824 END IF;
825
826 -- Update Matched Items
827
828 Write_Debug('Updating match data');
829
830 UPDATE_MATCH_DATA
831 (
832 p_batch_id => p_batch_id,
833 p_source_system_id => NULL,
834 x_Mesg_Token_Tbl => l_mesg_token_tbl,
835 x_Return_Status => x_retcode
836 );
837 IF (x_retcode = 'E') THEN
838 RAISE G_EXC_SEV_QUIT_OBJECT;
839 END IF;
840
841 --logMessage_forsnell(' Done with Separation' || pG_batch_options.PDH_BATCH);
842 Write_Debug('pG_batch_options.STRUCTURE_CONTENT--' || pG_batch_options.STRUCTURE_CONTENT);
843
844 IF NVL(pG_batch_options.STRUCTURE_CONTENT,'C') <> 'C' AND l_start_upload = 'T'
845 THEN
846 Write_Debug('CAlling the process_All_Comps_batch');
847 PROCESS_ALL_COMPS_BATCH
848 (
849 p_batch_id => p_batch_id
850 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
851 , x_Return_Status => x_retcode
852 );
853 Write_Debug('After calling process_all_comp--return_code--' || x_retcode);
854 END IF;
855
856 Write_Debug('Merging the duplicate Rows');
857 Merge_Duplicate_Rows
858 (
859 p_batch_id => p_batch_id,
860 x_Error_Mesg => x_errbuff,
861 x_Ret_Status => x_retcode
862 );
863
864 Write_Debug('After Merging rows ret_sts = ' || x_retcode);
865
866 IF (pG_batch_options.PDH_BATCH = 'Y') THEN
867 write_debug('Inside PDH');
868 Process_Batch_Options(p_batch_id => p_batch_id);
869 write_debug(' Done with Process_Batch_Options l_start_upload ' || l_start_upload || 'l_from_jcp ' || l_from_jcp);
870
871 IF (l_start_upload = 'T' AND l_from_jcp = 'N') THEN
872 write_debug('ready to lanuch jcp with p_batch_id ' || p_batch_id || ' and request id ' || l_request_id);
873 l_request_id := Fnd_Request.Submit_Request(
874 application => G_APP_SHORT_NAME,
875 program => 'BOMJCP',
876 sub_request => FALSE,
877 argument1 => p_conc_request_id,
878 argument2 => p_batch_id);
879 write_debug('the new request id ' || l_request_id);
880 END IF;
881 ELSE --If not PDH Batch
882 write_debug(' Reaching IMPORT_STRUCTURE_DATA for NON-PDH ');
883 IF (l_start_upload = 'T' and pG_batch_options.IMPORT_XREF_ONLY = 'Y') THEN
884 write_debug('The Process Returned because of Cross References Only');
885 return;
886 END IF;
887
888 write_debug(' Reaching IMPORT_STRUCTURE_DATA ');
889 IMPORT_STRUCTURE_DATA
890 (
891 p_batch_id => p_batch_id
892 , p_items_import_complete => l_start_upload
893 , p_callFromJCP => l_from_jcp
894 , p_request_id => p_conc_request_id
895 , x_error_message => l_err_buff
896 , x_return_code => l_ret_code
897 );
898 END IF;
899
900 x_retcode := l_ret_code;
901 x_errbuff := l_err_buff;
902 EXCEPTION
903 WHEN G_EXC_SEV_QUIT_OBJECT THEN
904 write_debug('Exception Occured');
905 x_retcode := 'E';
906 Error_Handler.Get_Message_List( x_message_list => l_message_list);
907 END Process_Structure_Data;
908
909 /****************** Resolve Cross References **************************
910 * Procedure : RESOLVE_XREFS_FOR_BATCH
911 * Purpose : This procedure will update the Bom Structure and Components
912 * Interface tables with the cross reference data obtained from
913 * Mtl_Cross_References.This API will update the Cross Referenced data
914 * for record in a batch which have matching entries in
915 * Mtl_Cross_References table.
916 * ??This should also insert customer xrefed rows
917 * Will return with success for Xreferences only
918 **********************************************************************/
919
920 PROCEDURE RESOLVE_XREFS_FOR_BATCH
921 (
922 p_batch_id IN NUMBER
923 ,x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
924 ,x_Return_Status IN OUT NOCOPY VARCHAR2
925 )
926 IS
927 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
928 l_Token_Tbl Error_Handler.Token_Tbl_Type;
929 l_err_text VARCHAR2(1000);
930
931 TYPE num_type IS TABLE OF NUMBER;
932 TYPE var_type IS TABLE OF VARCHAR2(1000);
933
934 l_item_id_table num_type;
935 l_org_id_table num_type;
936 l_ss_record_table var_type;
937 l_count NUMBER;
938 l_item_num_table var_type;
939
940 CURSOR Process_Header(l_batch_id IN NUMBER)
941 IS
942 SELECT MCR.inventory_item_id,MCR.organization_id,BBMI.source_system_reference,MSI.segment1
943 FROM bom_bill_of_mtls_interface BBMI,mtl_cross_references MCR,ego_import_batches_b EIBB,mtl_system_items MSI
944 WHERE BBMI.batch_id = l_batch_id
945 AND EIBB.batch_id = BBMI.batch_id
946 AND MCR.source_system_id = EIBB.source_system_id
947 AND MCR.cross_reference = BBMI.source_system_reference
948 AND MCR.cross_reference_type = 'SS_ITEM_XREF'
949 AND MSI.inventory_item_id = MCR.inventory_item_id
950 AND MSI.organization_id = MCR.organization_id
951 AND BBMI.assembly_item_id IS NULL
952 AND (BBMI.process_flag = 1 OR BBMI.process_flag = 5);
953
954 CURSOR Process_Comp(l_batch_id IN NUMBER)
955 IS
956 SELECT MCR.inventory_item_id,MCR.organization_id,BICI.comp_source_system_reference,MSI.segment1
957 FROM bom_inventory_comps_interface BICI,mtl_cross_references MCR,ego_import_batches_b EIBB,mtl_system_items MSI
958 WHERE BICI.batch_id = l_batch_id
959 AND EIBB.batch_id = BICI.batch_id
960 AND MCR.source_system_id = EIBB.source_system_id
961 AND MCR.cross_reference = BICI.comp_source_system_reference
962 AND MCR.cross_reference_type = 'SS_ITEM_XREF'
963 AND MSI.inventory_item_id = MCR.inventory_item_id
964 AND MSI.organization_id = MCR.organization_id
965 AND BICI.component_item_id IS NULL
966 AND (BICI.process_flag = 1 OR BICI.process_flag = 5);
967
968 CURSOR Process_Header_For_Comp
969 IS
970 SELECT MCR.inventory_item_id,MCR.organization_id,BICI.comp_source_system_reference,MSI.segment1
971 FROM bom_inventory_comps_interface BICI,mtl_cross_references MCR,ego_import_batches_b EIBB,mtl_system_items MSI
972 WHERE BICI.batch_id = p_batch_id
973 AND EIBB.batch_id = BICI.batch_id
974 AND MCR.source_system_id = EIBB.source_system_id
975 AND MCR.cross_reference = BICI.parent_source_system_reference
976 AND MCR.cross_reference_type = 'SS_ITEM_XREF'
977 AND MSI.inventory_item_id = MCR.inventory_item_id
978 AND MSI.organization_id = MCR.organization_id
979 AND BICI.assembly_item_id IS NULL
980 AND (BICI.process_flag = 1 OR BICI.process_flag = 5);
981
982 BEGIN
983
984 write_debug('In Resolve Xrefs ');
985
986 IF pG_batch_options.IMPORT_XREF_ONLY = 'Y' THEN
987 write_debug('The Process Returned because of Cross References Only');
988 x_return_status := 'S';
989 RETURN;
990 END IF;
991
992 OPEN Process_Header(p_batch_id);
993 FETCH Process_Header BULK COLLECT INTO l_item_id_table,l_org_id_table,l_ss_record_table,l_item_num_table;
994 CLOSE Process_Header;
995
996 l_count := l_ss_record_table.COUNT;
997 FOR i IN 1..l_count
998 LOOP
999 IF l_ss_record_table(i) IS NULL OR l_ss_record_table(i) = FND_API.G_MISS_CHAR
1000 THEN
1001 Error_Handler.Add_Error_Token
1002 (
1003 p_message_name => 'BOM_SOURCE_SYS_REF_INVALID'
1004 ,p_Mesg_token_Tbl => l_Mesg_Token_Tbl
1005 ,x_Mesg_token_Tbl => l_Mesg_Token_Tbl
1006 ,p_token_Tbl => l_Token_Tbl
1007 ,p_message_type => 'E'
1008 );
1009 x_return_status := FND_API.G_RET_STS_ERROR;
1010
1011 ELSE
1012 write_debug('Updating the header x-refs ');
1013
1014 UPDATE bom_bill_of_mtls_interface
1015 SET assembly_item_id = l_item_id_table(i),
1016 organization_id = l_org_id_table(i),
1017 item_number = l_item_num_table(i)
1018 WHERE batch_id = p_batch_id
1019 AND source_system_reference = l_ss_record_table(i)
1020 AND (process_flag = 1 OR process_flag = 5);
1021 END IF;
1022 END LOOP;
1023
1024 OPEN Process_Comp (p_batch_id);
1025 FETCH Process_Comp BULK COLLECT INTO l_item_id_table,l_org_id_table,l_ss_record_table,l_item_num_table;
1026 CLOSE Process_Comp;
1027
1028 l_count := l_ss_record_table.COUNT;
1029 FOR i IN 1..l_count
1030 LOOP
1031 IF l_ss_record_table(i) IS NULL OR l_ss_record_table(i) = FND_API.G_MISS_CHAR
1032 THEN
1033 Error_Handler.Add_Error_Token
1034 (
1035 p_message_name => 'BOM_SOURCE_SYS_REF_INVALID'
1036 ,p_Mesg_token_Tbl => l_Mesg_Token_Tbl
1037 ,x_Mesg_token_Tbl => l_Mesg_Token_Tbl
1038 ,p_token_Tbl => l_Token_Tbl
1039 ,p_message_type => 'E'
1040 );
1041 x_return_status := FND_API.G_RET_STS_ERROR;
1042 ELSE
1043 write_debug('Updating the component x-refs ');
1044
1045 UPDATE bom_inventory_comps_interface
1046 SET component_item_id = l_item_id_table(i),
1047 organization_id = l_org_id_table(i),
1048 component_item_number = l_item_num_table(i)
1049 WHERE batch_id = p_batch_id
1050 AND comp_source_system_reference = l_ss_record_table(i)
1051 AND ( process_flag = 1 OR process_flag = 5) ;
1052 END IF;
1053 END LOOP;
1054
1055 OPEN Process_Header_For_Comp;
1056 FETCH Process_Header_For_Comp BULK COLLECT INTO l_item_id_table,l_org_id_table,l_ss_record_table,l_item_num_table;
1057 CLOSE Process_Header_For_Comp;
1058
1059 l_count := l_ss_record_table.COUNT;
1060
1061 FOR i in 1..l_count LOOP
1062 UPDATE bom_inventory_comps_interface
1063 SET assembly_item_id = l_item_id_table(i),
1064 organization_id = l_org_id_table(i),
1065 assembly_item_number = l_item_num_table(i)
1066 WHERE batch_id = p_batch_id
1067 AND comp_source_system_reference = l_ss_record_table(i)
1068 AND ( process_flag = 1 OR process_flag = 5) ;
1069 END LOOP;
1070
1071 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
1072
1073 EXCEPTION
1074 WHEN OTHERS THEN
1075
1076 Write_Debug('Unexpected Error occured '|| SQLERRM);
1077
1078 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1079 THEN
1080 l_err_text := SUBSTR(SQLERRM, 1, 200);
1081 Error_Handler.Add_Error_Token
1082 (
1083 p_Message_Name => NULL
1084 , p_Message_Text => l_err_text
1085 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1086 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1087 );
1088 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1089 END IF;
1090 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1091
1092 END RESOLVE_XREFS_FOR_BATCH;
1093
1094 /* Update Match Data */
1095
1096 PROCEDURE UPDATE_MATCH_DATA
1097 (
1098 p_batch_id IN NUMBER
1099 , p_source_system_id IN NUMBER
1100 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1101 , x_Return_Status IN OUT NOCOPY VARCHAR2
1102 )
1103 IS
1104 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
1105 l_Token_Tbl Error_Handler.Token_Tbl_Type;
1106 l_err_text VARCHAR2(1000);
1107
1108 TYPE num_type IS TABLE OF NUMBER;
1109 TYPE var_type IS TABLE OF VARCHAR2(1000);
1110
1111 l_item_id_table num_type;
1112 l_org_id_table num_type;
1113 l_ss_record_table var_type;
1114 l_count NUMBER;
1115 l_item_num_table var_type;
1116 l_request_id NUMBER := nvl(FND_GLOBAL.conc_request_id,-1);
1117 CURSOR Process_Header
1118 IS
1119 SELECT MSII.inventory_item_id,MSII.organization_id,MSII.source_system_reference,MSII.item_number
1120 FROM bom_bill_of_mtls_interface BBMI,mtl_system_items_interface MSII
1121 WHERE BBMI.batch_id = p_batch_id
1122 AND (BBMI.process_flag = 1 OR BBMI.process_flag = 5)
1123 AND MSII.set_process_id = BBMI.batch_id
1124 AND MSII.process_flag IN (0,1,7)
1125 AND ((l_request_id <> -1 AND MSII.request_id = l_request_id) OR (l_request_id = -1 AND BBMI.request_id = MSII.request_id))
1126 AND (MSII.source_system_reference = BBMI.source_system_reference OR MSII.item_number = BBMI.item_number)
1127 AND (MSII.organization_code = BBMI.organization_code OR MSII.organization_id = BBMI.organization_id);
1128
1129 CURSOR Process_Comp
1130 IS
1131 SELECT MSII.inventory_item_id,MSII.organization_id,MSII.source_system_reference,MSII.item_number
1132 FROM bom_inventory_comps_interface BICI,mtl_system_items_interface MSII
1133 WHERE BICI.batch_id = p_batch_id
1134 AND (BICI.process_flag = 1 OR BICI.process_flag = 5)
1135 AND MSII.set_process_id = BICI.batch_id
1136 AND MSII.process_flag IN (0,1,7)
1137 AND ((l_request_id <> -1 AND MSII.request_id = l_request_id) OR (l_request_id = -1 AND BICI.request_id = MSII.request_id))
1138 AND (MSII.source_system_reference = BICI.comp_source_system_reference OR MSII.item_number = BICI.component_item_number)
1139 AND (MSII.organization_code = BICI.organization_code OR MSII.organization_id = BICI.organization_id);
1140
1141 Cursor Process_Header_For_Comp
1142 IS
1143 SELECT MSII.inventory_item_id,MSII.organization_id,MSII.source_system_reference,MSII.item_number
1144 FROM bom_inventory_comps_interface BICI,mtl_system_items_interface MSII
1145 WHERE BICI.batch_id = p_batch_id
1146 AND (BICI.process_flag = 1 OR BICI.process_flag = 5)
1147 AND MSII.set_process_id = BICI.batch_id
1148 AND MSII.process_flag IN (0,1,7)
1149 AND ((l_request_id <> -1 AND MSII.request_id = l_request_id) OR (l_request_id = -1 AND BICI.request_id = MSII.request_id))
1150 AND ( MSII.source_system_reference = BICI.parent_source_system_reference OR MSII.item_number = BICI.assembly_item_number )
1151 AND (MSII.organization_code = BICI.organization_code OR MSII.organization_id = BICI.organization_id);
1152
1153
1154 BEGIN
1155
1156 write_debug('In Update Match Data');
1157
1158 OPEN Process_Header;
1159 FETCH Process_Header BULK COLLECT INTO l_item_id_table,l_org_id_table,l_ss_record_table,l_item_num_table;
1160 CLOSE Process_Header;
1161
1162 l_count := l_ss_record_table.COUNT;
1163
1164 FOR i IN 1..l_count
1165 LOOP
1166 IF ( (l_ss_record_table(i) IS NULL AND l_item_num_table(i) IS NULL ) OR ( l_ss_record_table(i) = FND_API.G_MISS_CHAR AND l_item_num_table(i) = FND_API.G_MISS_CHAR) )
1167 THEN
1168 Error_Handler.Add_Error_Token
1169 (
1170 p_message_name => 'BOM_SOURCE_SYS_REF_INVALID'
1171 ,p_Mesg_token_Tbl => l_Mesg_Token_Tbl
1172 ,x_Mesg_token_Tbl => l_Mesg_Token_Tbl
1173 ,p_token_Tbl => l_Token_Tbl
1174 ,p_message_type => 'E'
1175 );
1176 x_return_status := FND_API.G_RET_STS_ERROR;
1177 ELSE
1178 write_debug('Updating the Header matches ');
1179
1180 UPDATE bom_bill_of_mtls_interface
1181 SET assembly_item_id = l_item_id_table(i),
1182 Organization_id = l_org_id_table(i),
1183 item_number = l_item_num_table(i),
1184 bill_sequence_id = null,
1185 transaction_type = 'SYNC'
1186 WHERE batch_id = p_batch_id
1187 AND (process_flag = 1 OR process_flag = 5)
1188 AND (source_system_reference = l_ss_record_table(i) OR item_number = l_item_num_table(i)) ;
1189 END IF;
1190 END LOOP;
1191 write_debug('After Updating Header Matches');
1192 OPEN Process_Comp;
1193 FETCH Process_Comp BULK COLLECT INTO l_item_id_table,l_org_id_table,l_ss_record_table,l_item_num_table;
1194 CLOSE Process_Comp;
1195 l_count := l_ss_record_table.COUNT;
1196
1197
1198 FOR i IN 1..l_count
1199 LOOP
1200 IF ( (l_ss_record_table(i) IS NULL AND l_item_num_table(i) IS NULL ) OR ( l_ss_record_table(i) = FND_API.G_MISS_CHAR AND l_item_num_table(i) = FND_API.G_MISS_CHAR) )
1201 THEN
1202 Error_Handler.Add_Error_Token
1203 (
1204 p_message_name => 'BOM_SOURCE_SYS_REF_INVALID'
1205 ,p_Mesg_token_Tbl => l_Mesg_Token_Tbl
1206 ,x_Mesg_token_Tbl => l_Mesg_Token_Tbl
1207 ,p_token_Tbl => l_Token_Tbl
1208 ,p_message_type => 'E'
1209 );
1210 x_return_status := FND_API.G_RET_STS_ERROR;
1211 ELSE
1212 write_debug('Updating the Component Matches');
1213 UPDATE bom_inventory_comps_interface
1214 SET component_item_id = l_item_id_table(i),
1215 Organization_id = l_org_id_table(i),
1216 component_item_number = l_item_num_table(i)
1217 WHERE batch_id = p_batch_id
1218 AND (process_flag = 1 OR process_flag = 5)
1219 AND (comp_source_system_reference = l_ss_record_table(i) OR component_item_number = l_item_num_table(i));
1220 END IF;
1221 END LOOP;
1222
1223 OPEN Process_Header_For_Comp;
1224 FETCH Process_Header_For_Comp BULK COLLECT INTO l_item_id_table,l_org_id_table,l_ss_record_table,l_item_num_table;
1225 CLOSE Process_Header_For_Comp;
1226
1227 l_count := l_ss_record_table.COUNT;
1228 FOR i in 1..l_count LOOP
1229 write_debug('Updating the Header matches in Component');
1230 UPDATE bom_inventory_comps_interface
1231 SET assembly_item_id = l_item_id_table(i),
1232 Organization_id = l_org_id_table(i),
1233 assembly_item_number = l_item_num_table(i)
1234 WHERE batch_id = p_batch_id
1235 and (process_flag = 1 OR process_flag = 5)
1236 AND ( parent_source_system_reference = l_ss_record_table(i) OR assembly_item_number = l_item_num_table(i)) ;
1237 END LOOP;
1238
1239 update_bill_info(p_batch_id => p_batch_id,
1240 x_Mesg_Token_Tbl => l_Mesg_Token_Tbl,
1241 x_Return_Status => x_return_status);
1242
1243 EXCEPTION
1244 WHEN OTHERS
1245 THEN
1246 Write_Debug('Unexpected Error occured..'|| SQLERRM);
1247 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1248 THEN
1249 l_err_text := SUBSTR(SQLERRM, 1, 200);
1250 Error_Handler.Add_Error_Token
1251 (
1252 p_Message_Name => NULL
1253 , p_Message_Text => l_err_text
1254 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1255 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1256 );
1257 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1258 END IF;
1259 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1260 END UPDATE_MATCH_DATA;
1261
1262 /* End Update Match Data */ --??DInu why two
1263
1264 PROCEDURE UPDATE_BILL_INFO
1265 (
1266 p_batch_id IN NUMBER
1267 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1268 , x_Return_Status IN OUT NOCOPY VARCHAR2
1269 )
1270 IS
1271
1272
1273 TYPE bom_comp_intf_type IS TABLE OF bom_inventory_comps_interface%ROWTYPE;
1274 TYPE bom_comp_type IS TABLE OF bom_components_b%ROWTYPE;
1275 TYPE num_type IS TABLE OF NUMBER;
1276 TYPE var_type IS TABLE OF VARCHAR2(1000);
1277
1278 l_err_text VARCHAR2(1000);
1279 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
1280
1281 l_comp_seq_id NUMBER;
1282 l_header_count NUMBER;
1283 l_comp_count NUMBER;
1284 l_comp_seq_count NUMBER;
1285 l_bill_seq_id NUMBER;
1286 l_effec_ctrl NUMBER;
1287 l_txn_table var_type;
1288 l_org_id NUMBER;
1289 l_header_rec_table var_type;
1290 l_str_name var_type;
1291 l_comp_table bom_comp_intf_type;
1292 l_comp_pdh_table bom_comp_type;
1293 l_item_id_table num_type;
1294 l_org_id_table num_type;
1295 l_not_exist BOOLEAN;
1296 l_exist_table num_type;
1297 l_str_type_id NUMBER;
1298 l_org_code_table var_type;
1299 l_item_name_table var_type;
1300 l_old_comp_seq_id NUMBER := NULL;
1301 l_comp_id NUMBER;
1302 l_wrong_comp BOOLEAN;
1303
1304 CURSOR Get_Header(l_batch_id IN NUMBER)
1305 IS
1306 SELECT BBMI.assembly_item_id,BBMI.organization_id,BBMI.alternate_bom_designator,BBMI.source_system_reference,UPPER(BBMI.transaction_type),BBMI.organization_code,BBMI.item_number
1307 FROM bom_bill_of_mtls_interface BBMI
1308 WHERE batch_id = l_batch_id
1309 AND process_flag NOT IN(3,7,-1);
1310
1311 CURSOR Process_Header(l_batch_id IN NUMBER,l_item_id IN NUMBER,l_org_id IN NUMBER,l_name IN VARCHAR2)
1312 IS
1313 SELECT BSB.bill_sequence_id,BSB.effectivity_control,BSB.organization_id
1314 FROM bom_bill_of_mtls_interface BBMI,
1315 bom_Structures_b BSB
1316 WHERE BBMI.batch_id = l_batch_id
1317 AND process_flag NOT IN(3,7,-1)
1318 AND BSB.assembly_item_id = l_item_id
1319 AND BSB.organization_id = l_org_id
1320 AND NVL(BSB.alternate_bom_designator,'Y') = NVL(l_name,'Y');
1321
1322 CURSOR Process_Comp(l_batch_id IN NUMBER,p_parent_reference IN VARCHAR2,l_parent_name IN VARCHAR2)
1323 IS
1324 SELECT *
1325 FROM bom_inventory_comps_interface BICI
1326 WHERE batch_id = l_batch_id
1327 AND process_flag NOT IN(3,7,-1)
1328 AND (parent_source_system_reference = p_parent_reference OR assembly_item_number = l_parent_name);
1329
1330 CURSOR Process_Unmatched_Comps(l_bill_seq_id IN NUMBER)
1331 IS
1332 SELECT *
1333 FROM Bom_Components_B BCB
1334 WHERE BCB.bill_sequence_id = l_bill_seq_id;
1335
1336 BEGIN
1337
1338
1339 write_debug('In Update Bill Info');
1340
1341 OPEN Get_Header(p_batch_id);
1342 FETCH Get_Header BULK COLLECT INTO l_item_id_table,l_org_id_table,l_str_name,l_header_rec_table,l_txn_table,l_org_code_table,l_item_name_table;
1343 CLOSE Get_Header;
1344
1345 l_header_count := l_header_rec_table.COUNT;
1346
1347 FOR i IN 1..l_header_count
1348 LOOP --Header Loop
1349
1350 write_debug('Updating the Bill for Header '|| l_header_rec_table(i));
1351
1352 l_bill_seq_id := NULL;
1353
1354 BEGIN
1355 IF l_org_id_table(i) IS NULL THEN
1356 SELECT organization_id
1357 INTO l_org_id_table(i)
1358 FROM mtl_parameters
1359 WHERE organization_code = l_org_code_table(i);
1360 END IF;
1361
1362
1363 IF l_item_id_table(i) IS NULL THEN
1364 SELECT inventory_item_id
1365 INTO l_item_id_table(i)
1366 FROM mtl_system_items_vl
1367 WHERE (concatenated_segments = l_header_rec_table(i) OR concatenated_segments = l_item_name_table(i))
1368 AND organization_id = l_org_id_table(i);
1369 END IF;
1370
1371 EXCEPTION WHEN NO_DATA_FOUND THEN
1372 NULL; --new item creation
1373 END;
1374
1375
1376 IF l_item_id_table(i) IS NOT NULL AND l_org_id_table(i) IS NOT NULL
1377 THEN
1378 OPEN Process_Header(p_batch_id,l_item_id_table(i),l_org_id_table(i),l_str_name(i));
1379 FETCH Process_Header INTO l_bill_seq_id,l_effec_ctrl,l_org_id;
1380 CLOSE Process_Header;
1381 END IF;
1382
1383
1384 OPEN Process_Comp(p_batch_id,l_header_rec_table(i),l_item_name_table(i));
1385 FETCH Process_Comp BULK COLLECT INTO l_comp_table;
1386 CLOSE Process_Comp;
1387
1388 l_comp_count := l_comp_table.COUNT;
1389
1390 IF (l_bill_seq_id IS NULL)
1391 THEN
1392 write_debug('Bill sequence id is null--Create header');
1393
1394 IF (l_txn_table(i) = 'SYNC' OR l_txn_table(i) = 'CREATE' OR l_txn_table(i) = 'UPDATE')
1395 THEN
1396 l_txn_table(i) := 'CREATE';
1397 END IF;
1398
1399 FOR j IN 1..l_comp_count
1400 LOOP
1401 IF (l_comp_table(j).transaction_type = 'SYNC' OR
1402 l_comp_table(j).transaction_type = 'CREATE' OR
1403 l_comp_table(j).transaction_type = 'UPDATE')
1404 THEN
1405 IF l_comp_table(j).component_sequence_id IS NULL THEN
1406 l_comp_table(j).transaction_type := 'CREATE';
1407 ELSE
1408 l_comp_table(j).transaction_type := 'UPDATE';
1409 END IF;
1410 END IF;
1411 END LOOP;
1412 ELSE
1413 write_debug('Bill sequence id is not null--Update header bill_seq_id ' || l_bill_seq_id);
1414 IF (l_txn_table(i) ='SYNC' OR l_txn_table(i) ='CREATE' OR l_txn_table(i) ='UPDATE')
1415 THEN
1416 l_txn_table(i) := 'UPDATE';
1417 END IF;
1418
1419 l_comp_count := l_comp_table.COUNT;
1420
1421 FOR j IN 1..l_comp_count
1422 LOOP
1423
1424 IF l_comp_table(j).comp_source_system_reference IS NULL THEN
1425 l_comp_table(j).comp_source_system_reference := l_comp_table(j).component_item_number;
1426 END IF;
1427
1428 l_comp_table(j).transaction_type := UPPER(l_comp_table(j).transaction_type);
1429
1430 l_comp_table(j).bill_sequence_id := l_bill_seq_id;
1431
1432
1433 IF l_comp_table(j).component_sequence_id IS NOT NULL THEN
1434 BEGIN
1435 SELECT component_item_id
1436 into l_comp_id
1437 from bom_components_b
1438 where component_sequence_id = l_comp_table(j).component_sequence_id;
1439
1440 IF l_comp_table(j).component_item_id = l_comp_id THEN
1441 l_wrong_comp := false;
1442 ELSE
1443 l_wrong_comp := true;
1444 END IF;
1445
1446 EXCEPTION WHEN NO_DATA_FOUND THEN
1447 l_wrong_comp := true;
1448 END;
1449 ELSE
1450 l_wrong_comp := true;
1451 END IF;
1452
1453 IF l_wrong_comp THEN
1454
1455 BEGIN
1456 IF l_comp_table(j).component_item_id IS NULL THEN
1457 SELECT inventory_item_id
1458 INTO l_comp_table(j).component_item_id
1459 FROM mtl_system_items_vl
1460 WHERE concatenated_segments = l_comp_table(j).component_item_number
1461 AND organization_id = l_org_id_table(i);
1462 END IF;
1463 EXCEPTION WHEN NO_DATA_FOUND THEN
1464 -- l_comp_table(j).component_item_id := null;
1465 NULL; -- new item creation
1466 END;
1467
1468 IF (l_comp_table(j).transaction_type = 'DELETE') THEN
1469 IF (l_comp_table(j).disable_date IS NULL) THEN
1470 l_comp_table(j).disable_date := sysdate;
1471 END IF;
1472 END IF;
1473
1474 l_comp_seq_id := CHECK_COMP_EXIST(l_bill_seq_id,
1475 l_effec_ctrl,
1476 p_batch_id,
1477 l_comp_table(j).comp_source_system_reference,
1478 l_comp_table(j).component_item_id,
1479 l_org_id_table(i),
1480 l_item_id_table(i)
1481 );
1482
1483
1484 IF(l_comp_seq_id <> 0)
1485 THEN
1486
1487 IF l_comp_table(j).process_flag = 5 THEN
1488 l_comp_table(j).old_component_sequence_id := l_comp_seq_id;
1489 END IF;
1490
1491 IF (l_comp_table(j).transaction_type = 'SYNC' OR l_comp_table(j).transaction_type = 'CREATE' OR l_comp_table(j).transaction_type = 'UPDATE') THEN
1492 l_comp_table(j).transaction_type := 'UPDATE';
1493 l_comp_table(j).component_sequence_id := l_comp_seq_id;
1494 ELSIF l_comp_table(j).transaction_type = 'UPDATE' THEN
1495 IF l_comp_table(j).component_sequence_id IS NULL THEN
1496 l_comp_table(j).component_sequence_id := l_comp_seq_id;
1497 END IF;
1498 END IF;
1499 IF (l_comp_table(j).transaction_type = 'DELETE') THEN
1500 IF (l_comp_table(j).component_sequence_id IS NULL) THEN
1501 l_comp_table(j).component_sequence_id := l_comp_seq_id;
1502 END IF;
1503 END IF;
1504 ELSE
1505 IF (l_comp_table(j).transaction_type = 'SYNC' OR l_comp_table(j).transaction_type = 'UPDATE' OR l_comp_table(j).transaction_type = 'CREATE') THEN
1506 l_comp_table(j).transaction_type := 'CREATE';
1507 l_comp_table(j).component_sequence_id := NULL;
1508 END IF;
1509 END IF;
1510 ELSE
1511 IF l_comp_table(j).process_flag = 5 THEN
1512 l_comp_table(j).old_component_sequence_id := l_comp_table(j).component_sequence_id;
1513 END IF;
1514 IF l_comp_table(j).transaction_type = 'SYNC' THEN
1515 l_comp_table(j).transaction_type := 'UPDATE';
1516 END IF;
1517 IF l_comp_table(j).transaction_type = 'DELETE' THEN
1518 IF l_comp_table(j).disable_date IS NULL THEN
1519 l_comp_table(j).disable_date := sysdate;
1520 END IF;
1521 END IF;
1522 END IF;
1523 END LOOP;
1524 END IF; /*bill_seq_id null IF */
1525
1526 l_comp_count := l_comp_table.COUNT;
1527
1528 FOR j IN 1..l_comp_count
1529 LOOP
1530 write_debug('updating comp -'|| l_comp_table(j).comp_source_system_reference);
1531 write_debug('with parent -'|| l_comp_table(j).parent_source_system_reference);
1532 UPDATE bom_inventory_comps_interface
1533 SET bill_sequence_id = l_comp_table(j).bill_sequence_id ,
1534 transaction_type = l_comp_table(j).transaction_type,
1535 component_sequence_id = l_comp_table(j).component_sequence_id,
1536 old_component_sequence_id = l_comp_table(j).old_component_sequence_id,
1537 disable_date = l_comp_table(j).disable_date,
1538 component_item_id = l_comp_table(j).component_item_id
1539 WHERE batch_id = l_comp_table(j).batch_id
1540 AND (process_flag = 1 or process_flag = 5)
1541 AND ( component_sequence_id = l_comp_table(j).component_sequence_id
1542 OR (/*component_sequence_id IS NULL
1543 AND*/(comp_source_system_reference = l_comp_table(j).comp_source_system_reference OR component_item_number = l_comp_table(j).component_item_number)
1544 AND (parent_source_system_reference = l_comp_table(j).parent_source_system_reference OR assembly_item_number = l_comp_table(j).assembly_item_number)));
1545
1546 -- IF l_comp_table(j).transaction_id IS NOT NULL THEN
1547 UPDATE bom_cmp_usr_attr_interface
1548 SET item_number = l_comp_table(j).component_item_number,
1549 assembly_item_number = l_comp_table(j).assembly_item_number,
1550 comp_source_system_reference = l_comp_table(j).comp_source_system_reference,
1551 parent_source_system_reference = l_comp_table(j).parent_source_system_reference,
1552 organization_id = l_org_id_table(i),
1553 attr_group_type = 'BOM_COMPONENTMGMT_GROUP' ,
1554 component_item_id = l_comp_table(j).component_item_id
1555 --process_status = 2
1556 WHERE batch_id = p_batch_id
1557 AND item_number = l_comp_table(j).component_item_number
1558 AND assembly_item_number = l_comp_table(j).assembly_item_number
1559 AND process_status NOT IN (3,4);
1560 -- AND transaction_id = l_comp_table(j).transaction_id;
1561 -- END IF;
1562 END LOOP;
1563
1564 UPDATE bom_bill_of_mtls_interface
1565 SET transaction_type = l_txn_table(i),
1566 Bill_sequence_id = l_bill_seq_id,
1567 assembly_item_id = l_item_id_table(i)
1568 WHERE batch_id = p_batch_id
1569 AND (source_system_reference = l_header_rec_table(i) OR item_number = l_item_name_table(i))
1570 AND (process_flag = 1 or process_flag = 5);
1571
1572 END LOOP; --End Header Loop
1573 x_Return_Status := 'S';
1574
1575 EXCEPTION
1576 WHEN OTHERS THEN
1577 Write_Debug('Unexpected Error occured' || SQLERRM);
1578 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1579 THEN
1580 l_err_text := SUBSTR(SQLERRM, 1, 200);
1581 Error_Handler.Add_Error_Token
1582 (
1583 p_Message_Name => NULL
1584 , p_Message_Text => l_err_text
1585 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1586 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1587 );
1588 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1589 END IF;
1590 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1591 END UPDATE_BILL_INFO;
1592
1593 /* End Update Bill Info */
1594
1595 /**
1596 * This procedure is the starting point for the existing open interface
1597 * tables being used to create batches.
1598 * Users will call this API once the data load for a batch is done in the
1599 * bom interface tables.
1600 *
1601 */
1602 PROCEDURE DATA_UPLOAD_COMPLETE
1603 (
1604 p_batch_id IN NUMBER
1605 , p_init_msg_list IN VARCHAR2
1606 , x_return_status IN OUT NOCOPY VARCHAR2
1607 , x_Error_Mesg IN OUT NOCOPY VARCHAR2
1608 , p_debug IN VARCHAR2
1609 , p_output_dir IN VARCHAR2
1610 , p_debug_filename IN VARCHAR2
1611 )
1612 IS
1613 G_EXC_SEV_QUIT_OBJECT EXCEPTION;
1614
1615 l_message_list Error_Handler.Error_Tbl_Type;
1616 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
1617 l_other_message VARCHAR2(50);
1618 l_Token_Tbl Error_Handler.Token_Tbl_Type;
1619 l_err_text VARCHAR2(2000);
1620 l_return_status VARCHAR2(1);
1621 l_debug_flag VARCHAR2(1) := p_debug;
1622 l_debug BOOLEAN := FALSE;
1623
1624 TYPE var_type IS TABLE OF VARCHAR2(50);
1625 TYPE num_type IS TABLE OF NUMBER;
1626
1627 TYPE bom_intf_header IS TABLE OF Bom_Bill_of_Mtls_Interface%ROWTYPE;
1628 TYPE bom_intf_comp IS TABLE OF Bom_Inventory_Comps_Interface%ROWTYPE;
1629 TYPE batch_options IS TABLE OF Ego_Import_Option_Sets%ROWTYPE;
1630
1631 l_header_table bom_intf_header;
1632 l_comp_table bom_intf_comp;
1633 l_header_count NUMBER;
1634 l_comp_count NUMBER;
1635 l_dummy VARCHAR2(10);
1636 l_user_name FND_USER.USER_NAME%TYPE := FND_GLOBAL.USER_NAME;
1637 l_user_id NUMBER;
1638 l_language VARCHAR2(100);
1639 l_resp_id NUMBER;
1640 l_request_id NUMBER;
1641 l_app_id NUMBER;
1642 l_batch_option_table batch_options;
1643
1644
1645 l_submit_failure_exc EXCEPTION;
1646
1647 l_ss_ref_table var_type;
1648 l_ss_desc_table var_type;
1649 l_cat_name_table var_type;
1650 l_cat_grp_table num_type;
1651 l_uom_table var_type;
1652 l_ss_id_table var_type;
1653 l_org_id_table num_type;
1654 l_org_code_table var_type;
1655 l_item_id_table num_type;
1656 l_item_number_table var_type;
1657 l_txn_type_table var_type;
1658 l_item_desc_table var_type;
1659
1660 l_str_type_id NUMBER;
1661 l_str_name VARCHAR2(100);
1662 l_effec_control VARCHAR2(100);
1663 l_process_flag NUMBER;
1664
1665 l_match_req_id NUMBER;
1666 l_import_req_id NUMBER;
1667
1668 CURSOR Get_Structure_Details
1669 (
1670 p_batch_id IN NUMBER
1671 )
1672 IS
1673 SELECT
1674 structure_type_id,
1675 structure_name,
1676 structure_effectivity_type
1677 FROM
1678 ego_import_option_sets
1679 WHERE
1680 batch_id = p_batch_id;
1681
1682 CURSOR Upload_Header
1683 (
1684 l_batch_id IN NUMBER
1685 )
1686 IS
1687 SELECT
1688 BBMI.SOURCE_SYSTEM_REFERENCE,
1689 BBMI.SOURCE_SYSTEM_REFERENCE_DESC,
1690 BBMI.CATALOG_CATEGORY_NAME,
1691 BBMI.ITEM_CATALOG_GROUP_ID,
1692 BBMI.PRIMARY_UNIT_OF_MEASURE,
1693 EIBB.SOURCE_SYSTEM_ID,
1694 BBMI.ORGANIZATION_ID,
1695 BBMI.ORGANIZATION_CODE,
1696 BBMI.ASSEMBLY_ITEM_ID,
1697 BBMI.ITEM_NUMBER,
1698 UPPER(BBMI.TRANSACTION_TYPE),
1699 BBMI.ITEM_DESCRIPTION
1700 FROM
1701 bom_bill_of_mtls_interface BBMI,
1702 ego_import_batches_b EIBB
1703 WHERE
1704 BBMI.batch_id = l_batch_id
1705 AND EIBB.batch_id = BBMI.batch_id
1706 AND BBMI.PROCESS_FLAG NOT IN (3,7,-1);
1707
1708 CURSOR Upload_Comp
1709 (
1710 l_batch_id IN NUMBER
1711 )
1712 IS
1713 SELECT
1714 BICI.comp_source_system_reference,
1715 BICI.COMP_SOURCE_SYSTEM_REFER_DESC,
1716 BICI.CATALOG_CATEGORY_NAME,
1717 BICI.ITEM_CATALOG_GROUP_ID,
1718 BICI.PRIMARY_UNIT_OF_MEASURE,
1719 EIBB.SOURCE_SYSTEM_ID,
1720 BICI.COMPONENT_ITEM_ID,
1721 BICI.COMPONENT_ITEM_NUMBER,
1722 BICI.ORGANIZATION_ID,
1723 BICI.ORGANIZATION_CODE,
1724 UPPER(BICI.TRANSACTION_TYPE),
1725 BICI.ITEM_DESCRIPTION
1726 FROM
1727 bom_inventory_comps_interface BICI,
1728 ego_import_batches_b EIBB
1729 WHERE
1730 BICI.batch_id = l_batch_id
1731 AND EIBB.batch_id = BICI.batch_id
1732 AND BICI.PROCESS_FLAG NOT IN (3,7,-1);
1733
1734 Cursor Get_Batch_Options
1735 (
1736 l_batch_id IN NUMBER
1737 )
1738 IS
1739 SELECT *
1740 FROM ego_import_option_sets
1741 WHERE batch_id = l_batch_id;
1742
1743 BEGIN
1744 IF p_init_msg_list = 'Y'
1745 THEN
1746 Error_Handler.Initialize();
1747 END IF;
1748
1749 IF l_debug_flag = 'Y'
1750 THEN
1751 IF trim(p_output_dir) IS NULL OR
1752 trim(p_output_dir) = ''
1753 THEN
1754 -- IF debug is Y THEN out dir must be
1755 -- specified
1756 Error_Handler.Add_Error_Token
1757 ( p_Message_text =>
1758 'Debug is set to Y so an output directory' ||
1759 ' must be specified. Debug will be turned' ||
1760 ' off since no directory is specified'
1761 , p_Mesg_Token_Tbl => l_Mesg_Token_tbl
1762 , x_Mesg_Token_Tbl => l_Mesg_Token_tbl
1763 , p_Token_Tbl => l_Token_Tbl
1764 );
1765 l_debug_flag := 'N';
1766 END IF;
1767
1768 IF trim(p_debug_filename) IS NULL OR
1769 trim(p_debug_filename) = ''
1770 THEN
1771 Error_Handler.Add_Error_Token
1772 ( p_Message_text =>
1773 'Debug is set to Y so an output filename' ||
1774 ' must be specified. Debug will be turned' ||
1775 ' off since no filename is specified'
1776 , p_Mesg_Token_Tbl => l_mesg_token_tbl
1777 , x_Mesg_Token_Tbl => l_mesg_token_tbl
1778 , p_Token_Tbl => l_token_tbl
1779 );
1780 l_debug_flag := 'N';
1781 END IF;
1782 Bom_Globals.Set_Debug(l_debug_flag);
1783
1784 IF Bom_Globals.Get_Debug = 'Y'
1785 THEN
1786 Error_Handler.Open_Debug_Session
1787 ( p_debug_filename => p_debug_filename
1788 , p_output_dir => p_output_dir
1789 , x_return_status => l_return_status
1790 , p_mesg_token_tbl => l_mesg_token_tbl
1791 , x_mesg_token_tbl => l_mesg_token_tbl
1792 );
1793
1794 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
1795 THEN
1796 l_debug_flag := 'N';
1797 END IF;
1798 END IF;
1799 END IF;
1800
1801 Bom_Globals.Set_Debug(l_debug_flag);
1802
1803 IF Error_Handler.get_debug <> 'Y' THEN
1804 l_debug := Init_Debug;
1805 ELSE
1806 l_debug := TRUE;
1807 END IF;
1808
1809 SELECT userenv('LANG')
1810 INTO l_language
1811 FROM dual;
1812
1813 l_user_id := FND_GLOBAL.USER_ID;
1814 l_resp_id := FND_GLOBAL.RESP_ID;
1815 l_app_id := FND_GLOBAL.RESP_APPL_ID;
1816
1817 IF (NVL(l_user_id,-1)=-1 OR NVL(l_resp_id,-1)=-1 OR NVL(l_app_id,-1)=-1)
1818 THEN
1819 Error_Handler.Add_Error_Token
1820 ( p_Message_Name => 'BOM_IMPORT_USER_INVALID'
1821 , p_Mesg_Token_Tbl => l_Mesg_Token_tbl
1822 , x_Mesg_Token_Tbl => l_Mesg_Token_tbl
1823 , p_Token_Tbl => l_token_tbl
1824 );
1825
1826 Error_Handler.Translate_And_Insert_Messages
1827 ( p_mesg_token_tbl => l_Mesg_Token_tbl
1828 , p_application_id => 'BOM'
1829 );
1830
1831 Error_Handler.Get_Message_List( x_message_list => l_message_list);
1832 x_Error_Mesg := l_message_list(1).Message_Text;
1833 x_return_status := FND_API.G_RET_STS_ERROR;
1834 RETURN;
1835 END IF;
1836
1837 Write_Debug('In Data Upld Complete Retrieving Batch Options');
1838
1839 Retrieve_Batch_Options( p_batch_id,l_mesg_token_tbl,l_return_status);
1840
1841 IF l_return_status <> 'S'
1842 THEN
1843 --x_Error_Mesg := l_err_text;
1844 RAISE G_EXC_SEV_QUIT_OBJECT;
1845 END IF;
1846
1847 Write_Debug('Resolving X-Refs');
1848
1849 RESOLVE_XREFS_FOR_BATCH
1850 (
1851 p_batch_id
1852 , l_Mesg_Token_Tbl
1853 , l_return_status
1854 );
1855 IF l_return_status <> 'S'
1856 THEN
1857 Error_Handler.Add_Error_Token
1858 (
1859 p_message_name => NULL
1860 , p_Mesg_Token_Tbl => l_mesg_token_tbl
1861 , x_Mesg_Token_Tbl => l_mesg_token_tbl
1862 , p_Token_Tbl => l_token_tbl
1863 );
1864 END IF;
1865
1866 Write_Debug('Uploading the Header');
1867
1868 IF pG_batch_options.IMPORT_XREF_ONLY = 'Y' THEN
1869 l_process_flag := 0;
1870 ELSE
1871 l_process_flag := 1;
1872 END If;
1873
1874
1875 OPEN Upload_Header(p_batch_id);
1876 FETCH
1877 Upload_Header
1878 BULK COLLECT INTO
1879 l_ss_ref_table,
1880 l_ss_desc_table,
1881 l_cat_name_table,
1882 l_cat_grp_table,
1883 l_uom_table,
1884 l_ss_id_table,
1885 l_org_id_table,
1886 l_org_code_table,
1887 l_item_id_table,
1888 l_item_number_table,
1889 l_txn_type_table,
1890 l_item_desc_table;
1891 CLOSE Upload_Header;
1892
1893 l_header_count := l_ss_ref_table.COUNT;
1894
1895 FOR i IN 1..l_header_count
1896 LOOP
1897 IF NOT Item_Exist_In_Mtl_Intf(l_ss_ref_table(i),p_batch_id,l_org_code_table(i),l_item_number_table(i),l_ss_desc_table(i),l_item_desc_table(i),l_org_id_table(i))
1898 THEN
1899 Write_Debug('Inserting into Mtl_Interface for Header');
1900 INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
1901 ( set_process_id
1902 , source_system_id
1903 , source_system_reference
1904 , SOURCE_SYSTEM_REFERENCE_DESC
1905 , item_catalog_group_id
1906 , primary_unit_of_measure
1907 , organization_id
1908 , organization_code
1909 , inventory_item_id
1910 , item_number
1911 , transaction_type
1912 , process_flag
1913 , description
1914 )
1915 VALUES
1916 (
1917 p_batch_id
1918 , l_ss_id_table(i)
1919 , l_ss_ref_table(i)
1920 , l_ss_desc_table(i)
1921 , l_cat_grp_table(i)
1922 , l_uom_table(i)
1923 , l_org_id_table(i)
1924 , l_org_code_table(i)
1925 , l_item_id_table(i)
1926 , l_item_number_table(i)
1927 , l_txn_type_table(i)
1928 , l_process_flag
1929 , l_item_desc_table(i)
1930 );
1931 END IF;
1932 END LOOP;
1933
1934 OPEN Upload_Comp(p_batch_id);
1935 FETCH
1936 Upload_Comp
1937 BULK COLLECT INTO
1938 l_ss_ref_table
1939 , l_ss_desc_table
1940 , l_cat_name_table
1941 , l_cat_grp_table
1942 , l_uom_table
1943 , l_ss_id_table
1944 , l_item_id_table
1945 , l_item_number_table
1946 , l_org_id_table
1947 , l_org_code_table
1948 , l_txn_type_table
1949 , l_item_desc_table;
1950 CLOSE Upload_Comp;
1951
1952 l_comp_count := l_ss_ref_table.COUNT;
1953
1954 FOR i IN 1..l_comp_count
1955 LOOP
1956 IF NOT Item_Exist_In_Mtl_Intf(l_ss_ref_table(i),p_batch_id,l_org_code_table(i),l_item_number_table(i),l_ss_desc_table(i),l_item_desc_table(i),l_org_id_table(i))
1957 THEN
1958 Write_Debug('Inserting into Mtl_Interface for Comps');
1959
1960 INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
1961 (
1962 set_process_id
1963 , source_system_id
1964 , source_system_reference
1965 , SOURCE_SYSTEM_REFERENCE_DESC
1966 , item_catalog_group_id
1967 , primary_unit_of_measure
1968 , organization_id
1969 , organization_code
1970 , inventory_item_id
1971 , item_number
1972 , transaction_type
1973 , process_flag
1974 , description
1975 )
1976 VALUES
1977 (
1978 p_batch_id
1979 , l_ss_id_table(i)
1980 , l_ss_ref_table(i)
1981 , l_ss_desc_table(i)
1982 , l_cat_grp_table(i)
1983 , l_uom_table(i)
1984 , l_org_id_table(i)
1985 , l_org_code_table(i)
1986 , l_item_id_table(i)
1987 , l_item_number_table(i)
1988 , l_txn_type_table(i)
1989 , l_process_flag
1990 , l_item_desc_table(i)
1991 );
1992
1993 END IF;
1994 END LOOP;
1995
1996
1997
1998 /* we need to trigger the EgoIJAVA in case of
1999 * import_on_data_load = Y or match_on_data_load = Y
2000 * as EGOIJAVA itself triggers the matching CP.
2001 */
2002 IF ( pG_batch_options.IMPORT_ON_DATA_LOAD = 'Y' OR pG_batch_options.MATCH_ON_DATA_LOAD = 'Y' ) THEN
2003 IF NOT FND_REQUEST.Set_Options
2004 ( implicit => 'WARNING'
2005 , protected => 'YES'
2006 )
2007 THEN
2008 RAISE l_submit_failure_exc;
2009 END IF;
2010 OPEN Get_Batch_Options(p_batch_id);
2011 FETCH Get_Batch_Options BULK COLLECT INTO l_batch_option_table;
2012 CLOSE Get_Batch_Options;
2013
2014 l_request_id := Fnd_Request.Submit_Request(
2015 application => 'EGO',
2016 program => 'EGOIJAVA',
2017 sub_request => FALSE,
2018 argument1 => null,-- result fmt
2019 argument2 => l_user_id,
2020 argument3 => l_language,-- lang
2021 argument4 => l_resp_id,-- Respo
2022 argument5 => l_app_id,-- App Id
2023 argument6 => 2,-- Run From - API
2024 argument7 => null,-- Create New Batch
2025 argument8 => p_batch_id,-- Batch Id
2026 argument9 => null,-- Batch Name
2027 argument10 => l_batch_option_table(1).import_on_data_load,-- Import on Data Load
2028 argument11 => l_batch_option_table(1).match_on_data_load,-- Match on Data Load
2029 argument12 => l_batch_option_table(1).add_all_to_change_flag,-- Use CO
2030 argument13 => l_batch_option_table(1).change_order_creation,-- Add/Create CO
2031 argument14 => l_batch_option_table(1).change_mgmt_type_code,-- CO category
2032 argument15 => l_batch_option_table(1).change_type_id,-- CO type
2033 argument16 => l_batch_option_table(1).change_notice,-- CO Name
2034 argument17 => l_batch_option_table(1).change_name,-- CO Number
2035 argument18 => l_batch_option_table(1).change_description-- CO Desc
2036 );
2037
2038 /**
2039 * Changes for bug 5395935
2040 * Calling the Ego API to update the request_id to the batch.
2041 * if Match On Data Load is Yes then the same request id will be passed in
2042 * p_match_request_id
2043 */
2044 IF l_request_id IS NOT NULL THEN
2045 IF ( nvl(pG_batch_options.IMPORT_ON_DATA_LOAD,'N') = 'Y') THEN
2046 l_import_req_id := l_request_id;
2047 END IF;
2048 IF ( nvl(pG_batch_options.MATCH_ON_DATA_LOAD,'N') = 'Y') THEN
2049 l_match_req_id := l_request_id;
2050 END IF;
2051 Ego_Import_Pvt.Update_Request_Id_To_Batch
2052 (p_import_request_id => l_import_req_id,
2053 p_match_request_id => l_match_req_id,
2054 p_batch_id => p_batch_id
2055 );
2056 END IF;
2057
2058 commit;
2059 --arudresh_debug('l_req_id--' || l_request_id);
2060 x_return_status := FND_API.G_RET_STS_SUCCESS;
2061 return;
2062 END IF;
2063
2064 EXCEPTION
2065 WHEN l_submit_failure_exc THEN
2066 write_debug('Got Exception while Submitting Conc Request');
2067 x_return_status := FND_API.G_RET_STS_ERROR;
2068 WHEN G_EXC_SEV_QUIT_OBJECT THEN
2069 write_debug('Got User Defined Exception ');
2070 x_return_status := FND_API.G_RET_STS_ERROR;
2071 WHEN OTHERS THEN
2072 write_debug('Got Other Exception');
2073 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2074
2075
2076
2077
2078 -- No need for this.This is necessary only if the user visits the UI.
2079 -- For that we have a call from the UI.
2080 /*
2081 UPDATE_BILL_INFO
2082 (
2083 p_batch_id => p_batch_id,
2084 x_Mesg_Token_Tbl => l_Mesg_Token_Tbl,
2085 x_Return_Status => l_return_status
2086 );
2087 IF l_return_status <> 'S'
2088 THEN
2089
2090 Error_Handler.Add_Error_Token
2091 (
2092 p_message_name => NULL
2093 , p_Mesg_Token_Tbl => l_mesg_token_tbl
2094 , x_Mesg_Token_Tbl => l_mesg_token_tbl
2095 , p_Token_Tbl => l_token_tbl
2096 );
2097 END IF;
2098
2099
2100 x_return_status := l_return_status;
2101 Error_Handler.Get_Message_List( x_message_list => l_message_list);
2102 x_Error_Mesg := l_message_list(1).Message_Text;
2103
2104 EXCEPTION
2105
2106
2107 Error_Handler.Add_Error_Token
2108 (
2109 p_message_name => NULL
2110 , p_Mesg_token_Tbl => l_Mesg_Token_Tbl
2111 , x_Mesg_token_Tbl => l_Mesg_Token_Tbl
2112 , p_token_Tbl => l_Token_Tbl
2113 , p_message_type => 'E'
2114 );
2115
2116 x_return_status := Error_Handler.G_STATUS_UNEXPECTED;
2117 Error_Handler.Get_Message_List( x_message_list => l_message_list);
2118 x_Error_Mesg := l_message_list(1).Message_Text;
2119
2120 IF Bom_Globals.Get_Debug = 'Y'
2121 THEN
2122 Error_Handler.Write_Debug('After getting exception for invalid batch id ');
2123 Error_Handler.Write_To_DebugFile;
2124 Error_Handler.Dump_Message_List;
2125 Error_Handler.Close_Debug_Session;
2126 END IF;
2127 */
2128
2129 END Data_Upload_Complete;
2130
2131 /* End Data Upload Complete - Duplicate?? */
2132
2133 FUNCTION BOM_GET_COMP_ATTR_DATA
2134 (
2135 p_batch_id NUMBER
2136 , p_ss_record_id VARCHAR2
2137 , p_comp_seq_id NUMBER
2138 , p_str_type_id NUMBER
2139 , p_effec_date DATE
2140 , p_op_seq_num NUMBER
2141 , p_item_id NUMBER
2142 , p_org_id NUMBER
2143 , p_intf_uniq_id NUMBER
2144 ) RETURN Bom_Attr_Diff_Table_Type
2145 IS
2146 TYPE tab_typ IS TABLE OF VARCHAR2(3200);
2147 TYPE num_type IS TABLE OF NUMBER;
2148
2149 l_dummy VARCHAR2(20);
2150 l_attr_sql VARCHAR2(10000);
2151 l_attr_sql1 VARCHAR2(10000);
2152 l_pdh_query VARCHAR2(1000);
2153 l_src_query VARCHAR2(1000);
2154 l_where_clause VARCHAR2(1000);
2155 l_attr_diff Bom_Attr_Diff_Table_Type := Bom_Attr_Diff_Table_Type();
2156 l_temp_table num_type;
2157 l_name_table tab_typ;
2158
2159 l_eff_date_intf DATE;
2160 l_new_eff_date_intf DATE;
2161 l_dis_date_intf DATE;
2162 l_from_num_intf VARCHAR2(100);
2163 l_new_from_num_intf VARCHAR2(100);
2164 l_to_unit_num_intf VARCHAR2(100);
2165 l_from_rev_intf VARCHAR2(50);
2166 l_to_item_rev_intf VARCHAR2(50);
2167
2168 l_eff_date_pdh DATE;
2169 l_dis_date_pdh DATE;
2170 l_from_num_pdh VARCHAR2(100);
2171 l_to_unit_num_pdh VARCHAR2(100);
2172 l_from_rev_pdh VARCHAR2(50);
2173 l_to_item_rev_pdh VARCHAR2(50);
2174 l_eff_sql VARCHAR2(1000);
2175
2176 attr_grp tab_typ;
2177 attr tab_typ;
2178 attr_name tab_typ;
2179 src_attr tab_typ;
2180 pdh_attr tab_typ;
2181 batch_identifier tab_typ;
2182
2183 l_count NUMBER;
2184 l_temp_count NUMBER;
2185 l_name_count NUMBER;
2186 l_attr_row Bom_Attribute_Row_Type := Bom_Attribute_Row_Type(1,1,1,1,1,1);
2187
2188 CURSOR Get_Attr_Details(p_str_type_id IN NUMBER,p_attr_grp_id IN NUMBER)
2189 IS
2190 SELECT BCEB.component_sequence_id
2191 FROM bom_components_ext_b BCEB
2192 WHERE BCEB.structure_type_id = p_str_type_id
2193 AND BCEB.attr_group_id = p_attr_grp_id;
2194
2195 CURSOR Get_Src_Attr(p_str_type_id IN NUMBER,p_attr_grp_id IN NUMBER,p_attr_grp_name IN VARCHAR2)
2196 IS
2197 SELECT BCUA.attr_group_int_name
2198 FROM bom_cmp_usr_attr_interface BCUA
2199 WHERE BCUA.batch_id = p_batch_id
2200 AND BCUA.structure_type_id = p_str_type_id
2201 AND (BCUA.attr_group_id = p_attr_grp_id OR BCUA.attr_group_int_name = p_attr_grp_name);
2202
2203 BEGIN
2204
2205 IF p_intf_uniq_id IS NOT NULL THEN
2206 SELECT effectivity_date,new_effectivity_date,disable_date,from_end_item_unit_number,new_from_end_item_unit_number,to_end_item_unit_number,from_end_item_rev_code,to_end_item_rev_code
2207 INTO l_eff_date_intf,l_new_eff_date_intf,l_dis_date_intf,l_from_num_intf ,l_new_from_num_intf,l_to_unit_num_intf,l_from_rev_intf ,l_to_item_rev_intf
2208 FROM bom_inventory_comps_interface
2209 WHERE batch_id = p_batch_id
2210 AND interface_table_unique_id = p_intf_uniq_id;
2211 END IF;
2212
2213 IF p_comp_seq_id IS NOT NULL THEN
2214 SELECT effectivity_date,disable_date,from_end_item_unit_number,to_end_item_unit_number,from_end_item_rev_id,to_end_item_rev_id
2215 INTO l_eff_date_pdh,l_dis_date_pdh,l_from_num_pdh,l_to_unit_num_pdh,l_from_rev_pdh,l_to_item_rev_pdh
2216 from bom_components_b
2217 where component_sequence_id = p_comp_seq_id;
2218 END IF;
2219
2220 l_eff_sql := ' SELECT ' ;
2221
2222 IF l_from_rev_intf IS NOT NULL THEN
2223 l_eff_sql := l_eff_sql || ' Bom_Globals.Retrieve_Message('|| '''' || 'BOM' || ''','||''''|| 'BOM_REVISION_EFF'||'''), '
2224 || ' Bom_Globals.Retrieve_Message('|| '''' || 'BOM' || ''','||''''|| 'BOM_FROM_END_ITEM_REV_LABEL'||'''), '
2225 || ' Bom_Globals.Retrieve_Message('|| '''' || 'BOM' || ''','||''''|| 'BOM_FROM_END_ITEM_REV_LABEL'||'''), '
2226 || p_batch_id || ', '
2227 || '''' || l_from_rev_intf || ''', ' ;
2228 IF l_from_rev_pdh IS NOT NULL THEN
2229 l_eff_sql := l_eff_sql || '''' || l_from_rev_pdh || '''';
2230 ELSE
2231 l_eff_sql := l_eff_sql || ' null ' ;
2232 END IF;
2233 l_eff_sql := l_eff_sql || ' FROM DUAL '
2234 || ' UNION ALL SELECT '
2235 || ' Bom_Globals.Retrieve_Message('|| '''' || 'BOM' || ''','||''''|| 'BOM_REVISION_EFF'||'''), '
2236 || ' Bom_Globals.Retrieve_Message('|| '''' || 'BOM' || ''','||''''|| 'BOM_TO_END_ITEM_REV_LABEL'||'''), '
2237 || ' Bom_Globals.Retrieve_Message('|| '''' || 'BOM' || ''','||''''|| 'BOM_TO_END_ITEM_REV_LABEL'||'''), '
2238 || p_batch_id || ', ' ;
2239 IF l_to_item_rev_intf IS NOT NULL THEN
2240 l_eff_sql := l_eff_sql || '''' || l_to_item_rev_intf || ''' , ';
2241 ELSE
2242 l_eff_sql := l_eff_sql || ' null , ' ;
2243 END IF;
2244
2245 IF l_to_item_rev_pdh IS NOT NULL THEN
2246 l_eff_sql := l_eff_sql || '''' || l_to_item_rev_pdh || '''';
2247 ELSE
2248 l_eff_sql := l_eff_sql || ' null ' ;
2249 END IF;
2250 l_eff_sql := l_eff_sql || ' FROM DUAL ';
2251 ELSIF (l_new_from_num_intf IS NOT NULL OR l_from_num_intf IS NOT NULL) THEN
2252 l_eff_sql := l_eff_sql || ' Bom_Globals.Retrieve_Message('|| '''' || 'BOM' || ''','||''''|| 'BOM_UNIT_EFF'||'''), '
2253 || ' Bom_Globals.Retrieve_Message('|| '''' || 'BOM' || ''','||''''|| 'BOM_EFF_FROM_NUMBER'||'''), '
2254 || ' Bom_Globals.Retrieve_Message('|| '''' || 'BOM' || ''','||''''|| 'BOM_EFF_FROM_NUMBER'||'''), '
2255 || p_batch_id || ', ';
2256 IF l_new_from_num_intf IS NOT NULL THEN
2257 l_eff_sql := l_eff_sql || '''' || l_new_from_num_intf || ''' , ' ;
2258 ELSIF l_from_num_intf IS NOT NULL THEN
2259 l_eff_sql := l_eff_sql || '''' || l_from_num_intf || ''' , ' ;
2260 ELSE
2261 l_eff_sql := l_eff_sql || ' null , ' ;
2262 END IF;
2263 IF l_from_num_pdh IS NOT NULL THEN
2264 l_eff_sql := l_eff_sql || '''' || l_from_num_pdh || '''';
2265 ELSE
2266 l_eff_sql := l_eff_sql || ' null ' ;
2267 END IF;
2268 l_eff_sql := l_eff_sql || ' FROM DUAL '
2269 || ' UNION ALL SELECT '
2270 || ' Bom_Globals.Retrieve_Message('|| '''' || 'BOM' || ''','||''''|| 'BOM_UNIT_EFF'||'''), '
2271 || ' Bom_Globals.Retrieve_Message('|| '''' || 'BOM' || ''','||''''|| 'BOM_EFF_TO_NUMBER'||'''), '
2272 || ' Bom_Globals.Retrieve_Message('|| '''' || 'BOM' || ''','||''''|| 'BOM_EFF_TO_NUMBER'||'''), '
2273 || p_batch_id || ', ';
2274 IF l_to_unit_num_intf IS NOT NULL THEN
2275 l_eff_sql := l_eff_sql || '''' || l_to_unit_num_intf || ''' , ';
2276 ELSE
2277 l_eff_sql := l_eff_sql || ' NULL , ' ;
2278 END IF;
2279 IF l_to_unit_num_pdh IS NOT NULL THEN
2280 l_eff_sql := l_eff_sql || '''' || l_to_unit_num_pdh || '''' ;
2281 ELSE
2282 l_eff_sql := l_eff_sql || ' NULL ';
2283 END IF;
2284 l_eff_sql := l_eff_sql || ' FROM DUAL ';
2285 ELSE
2286 l_eff_sql := l_eff_sql || ' Bom_Globals.Retrieve_Message('|| '''' || 'BOM' || ''','||''''|| 'BOM_EFF_DATE_CHOICE'||'''), '
2287 || ' Bom_Globals.Retrieve_Message('|| '''' || 'BOM' || ''','||''''|| 'BOM_EFFECTIVITY_DATE'||'''), '
2288 || ' Bom_Globals.Retrieve_Message('|| '''' || 'BOM' || ''','||''''|| 'BOM_EFFECTIVITY_DATE'||'''), '
2289 || p_batch_id || ', ';
2290 IF l_eff_date_intf IS NOT NULL THEN
2291 l_eff_sql := l_eff_sql || '''' || FND_DATE.DATE_TO_DISPLAYDT(l_eff_date_intf) || ''' , ' ;
2292 ELSIF l_new_eff_date_intf IS NOT NULL THEN
2293 l_eff_sql := l_eff_sql || '''' || FND_DATE.DATE_TO_DISPLAYDT(l_new_eff_date_intf) || ''' , ' ;
2294 ELSE
2295 l_eff_sql := l_eff_sql || ' NULL , ' ;
2296 END IF;
2297
2298 IF l_eff_date_pdh IS NOT NULL THEN
2299 l_eff_sql := l_eff_sql || '''' || FND_DATE.DATE_TO_DISPLAYDT(l_eff_date_pdh) || '''';
2300 ELSE
2301 l_eff_sql := l_eff_sql || ' NULL ' ;
2302 END IF;
2303 l_eff_sql := l_eff_sql || ' FROM DUAL '
2304 || ' UNION ALL SELECT '
2305 || ' Bom_Globals.Retrieve_Message('|| '''' || 'BOM' || ''','||''''|| 'BOM_EFF_DATE_CHOICE'||'''), '
2306 || ' Bom_Globals.Retrieve_Message('|| '''' || 'BOM' || ''','||''''|| 'BOM_DISABLE_DATE'||'''), '
2307 || ' Bom_Globals.Retrieve_Message('|| '''' || 'BOM' || ''','||''''|| 'BOM_DISABLE_DATE'||'''), '
2308 || p_batch_id || ', ' ;
2309 IF l_dis_date_intf IS NOT NULL THEN
2310 l_eff_sql := l_eff_sql || '''' || FND_DATE.DATE_TO_DISPLAYDT(l_dis_date_intf) || ''', ' ;
2311 ELSE
2312 l_eff_sql := l_eff_sql || ' NULL , ' ;
2313 END IF;
2314 IF l_dis_date_pdh IS NOT NULL THEN
2315 l_eff_sql := l_eff_sql || '''' || FND_DATE.DATE_TO_DISPLAYDT(l_dis_date_pdh) || '''';
2316 ELSE
2317 l_eff_sql := l_eff_sql || ' NULL ';
2318 END IF;
2319 l_eff_sql := l_eff_sql || ' FROM DUAL ';
2320
2321 END IF;
2322
2323 l_attr_sql := l_eff_sql || ' UNION ALL ';
2324
2325
2326 l_attr_sql := l_attr_sql || 'SELECT distinct(attr_group_disp_name), attr_display_name,attr_name , batch_id batch_identifier ,';--decode(attr.attr_name, ';
2327 l_attr_sql1 := 'SELECT grps.attr_group_disp_name, attrs.attr_display_name , attr_name , ' ;
2328 IF p_ss_record_id IS NULL THEN
2329 l_attr_sql1 := l_attr_sql1 || ' null batch_identifier , decode(attrs.database_column, ';
2330 ELSE
2331 l_attr_sql1 := l_attr_sql1 || ' batch_id batch_identifier , decode(attrs.database_column, ';
2332 END IF;
2333
2334 l_temp_count := 1;
2335 l_name_count := 1;
2336 FOR attr IN (SELECT * FROM bom_attrs_v)
2337 LOOP
2338 IF attr.attr_group_type = 'BOM_COMPONENTMGMT_GROUP'
2339 THEN
2340 OPEN Get_Src_Attr(p_str_type_id,attr.attr_group_id,attr.attr_group_name);
2341 FETCH Get_Src_Attr BULK COLLECT INTO l_name_table;
2342 CLOSE Get_Src_Attr;
2343
2344 IF l_name_table.COUNT > 0 THEN
2345
2346 IF l_name_count = 1 THEN
2347 l_attr_sql := l_attr_sql || 'decode(attr.attr_name, ';
2348 l_name_count := 2;
2349 END IF;
2350
2351 BEGIN
2352 SELECT 'Exist'
2353 INTO l_dummy
2354 FROM bom_cmp_usr_attr_interface BCUI
2355 WHERE (BCUI.comp_source_system_reference = p_ss_record_id OR BCUI.component_sequence_id = p_comp_seq_id)
2356 AND ( BCUI.attr_group_id = attr.attr_group_id OR BCUI.attr_group_int_name = attr.attr_group_name)
2357 AND BCUI.attr_int_name = attr.attr_name
2358 AND BCUI.batch_id = p_batch_id;
2359
2360 IF l_dummy IS NOT NULL
2361 THEN
2362
2363 l_attr_sql := l_attr_sql || '''' || attr.attr_name || ''',BCUA.attr_disp_value,';--(SELECT to_char(decode( ';
2364 /* IF SUBSTR(attr.database_column,1,LENGTH(attr.database_column)-10) = 'N' OR SUBSTR(attr.database_column,1,LENGTH(attr.database_column)-11) = 'N'
2365 THEN
2366 l_attr_sql := l_attr_sql || 'attr_value_num,null,attr_disp_value,attr_value_num)';
2367 END IF;
2368 IF SUBSTR(attr.database_column,1,LENGTH(attr.database_column)-10) = 'C' OR SUBSTR(attr.database_column,1,LENGTH(attr.database_column)-11) = 'C'
2369 THEN
2370 l_attr_sql := l_attr_sql || 'attr_value_str,null,attr_disp_value,attr_value_str)';
2371 END IF;
2372 IF SUBSTR(attr.database_column,1,LENGTH(attr.database_column)-10) = 'D' OR SUBSTR(attr.database_column,1,LENGTH(attr.database_column)-11) = 'D'
2373 THEN
2374 l_attr_sql := l_attr_sql || 'attr_value_date,null,attr_disp_value,attr_value_date)';
2375 END IF;*/
2376 /* l_attr_sql := l_attr_sql || ') from bom_cmp_usr_attr_interface where process_status <> -1 AND comp_source_system_reference = ' || ''''||
2377 p_ss_record_id ||''' and ( attr_group_id = ' ||
2378 attr.attr_group_id || ' or attr_group_int_name = '|| ''''||attr.attr_group_name || ''' ) and attr_int_name = ' || '''' || attr.attr_name || ''' ),';*/
2379 END IF;
2380
2381 EXCEPTION WHEN NO_DATA_FOUND THEN
2382 l_attr_sql := l_attr_sql || '''' || attr.attr_name || ''',(SELECT null from dual),';
2383 END;
2384
2385 END IF;--l_dummy not null
2386
2387 ELSE
2388 IF attr.attr_group_type = 'BOM_COMPONENT_BASE' THEN
2389 l_attr_sql1 := l_attr_sql1 || '''' || attr.database_column || ''', to_char(src_val.' || attr.database_column || ') ,';
2390 END IF;
2391 END IF;
2392 END LOOP;
2393 IF l_name_count = 1
2394 THEN
2395 l_attr_sql := l_attr_sql || ' null src_attr_value,';
2396 ELSE
2397 l_attr_sql := SUBSTR(l_attr_sql,1,LENGTH(l_attr_sql)-1) || ' ) src_attr_value, ';-- decode(attr.attr_name, ';
2398 END IF;
2399 l_attr_sql1 := SUBSTR(l_attr_sql1,1,LENGTH(l_attr_sql1)-1) || ' ) src_attr_value, decode(attrs.database_column, ';
2400
2401 l_temp_count := 1;
2402
2403 FOR attr IN (SELECT * FROM bom_attrs_v )
2404 LOOP
2405 IF attr.attr_group_type = 'BOM_COMPONENTMGMT_GROUP'
2406 THEN
2407 OPEN Get_Attr_Details(p_str_type_id,attr.attr_group_id);
2408 FETCH Get_Attr_Details BULK COLLECT INTO l_temp_table;
2409 CLOSE Get_Attr_Details;
2410
2411 IF l_temp_table.COUNT > 0
2412 THEN
2413
2414 IF l_temp_count = 1
2415 THEN
2416 l_attr_sql := l_attr_sql || ' decode(attr.attr_name, ';
2417 l_temp_count := 2;
2418 END IF;
2419
2420 BEGIN
2421 SELECT 'Exist'
2422 INTO l_dummy
2423 FROM bom_components_ext_b BCEB
2424 WHERE BCEB.component_sequence_id = p_comp_seq_id
2425 AND BCEB.attr_group_id = attr.attr_group_id;
2426
2427 IF l_dummy IS NOT NULL
2428 THEN
2429 l_attr_sql := l_attr_sql || '''' || attr.attr_name || ''' ,BCEB.' ||attr.database_column || ',';
2430 --(SELECT to_char(' || attr.database_column || ') FROM bom_components_ext_b where component_sequence_id = '|| p_comp_seq_id
2431 --|| ' AND attr_group_id = ' || attr.attr_group_id || '),';
2432 END IF;
2433
2434 EXCEPTION WHEN NO_DATA_FOUND THEN
2435 l_attr_sql := l_attr_sql || '''' || attr.attr_name || ''' ,(SELECT null from dual),';
2436 END;
2437
2438 END IF;--l_dummy
2439
2440 ELSE
2441 IF attr.attr_group_type = 'BOM_COMPONENT_BASE' THEN
2442 l_attr_sql1 := l_attr_sql1 || '''' || attr.database_column || ''' ,to_char(pdh_value.' || attr.database_column || '),';
2443 END IF;
2444 END IF;
2445 END LOOP;
2446
2447 IF l_temp_count = 1
2448 THEN
2449 l_attr_sql := l_attr_sql || ' null pdh_attr_value';
2450 ELSE
2451 l_attr_sql := SUBSTR(l_attr_sql,1,LENGTH(l_attr_sql)-1) || ' ) pdh_attr_value' ;
2452 END IF;
2453
2454 l_attr_sql1 := SUBSTR(l_attr_sql1,1,LENGTH(l_attr_sql1)-1) || ' ) pdh_attr_value' ;
2455 IF p_comp_seq_id IS NOT NULL
2456 THEN
2457 l_pdh_query := ' (SELECT * FROM bom_components_b WHERE component_sequence_id = :3 ) pdh_value' ;
2458 ELSE
2459 l_pdh_query := '(SELECT ';
2460 FOR attr IN (SELECT * FROM bom_attrs_v)
2461 LOOP
2462 IF attr.attr_group_type <> 'BOM_COMPONENTMGMT_GROUP'
2463 THEN
2464 IF attr.attr_group_type = 'BOM_COMPONENT_BASE' THEN
2465 l_pdh_query := l_pdh_query || ' null as ' || attr.database_column || ' ,';
2466 END IF;
2467 END IF;
2468 END LOOP;
2469 l_pdh_query := SUBSTR(l_pdh_query, 1, LENGTH(l_pdh_query)-1);
2470 l_pdh_query := l_pdh_query|| ' from dual ';
2471 l_pdh_query := l_pdh_query || ' ) pdh_value ';
2472 END IF;
2473
2474 IF p_ss_record_id IS NOT NULL
2475 THEN
2476 l_src_query := ' (SELECT * FROM bom_inventory_comps_interface WHERE batch_id = :1 ' ||
2477 ' AND ( (comp_source_system_reference = :2 OR component_item_number = ' || '''' || p_ss_record_id || ''' )' ||
2478 ' AND interface_table_unique_id = ' || p_intf_uniq_id ||
2479 ' AND organization_id = ' || p_org_id ||
2480 ' ) ) src_val ,';
2481 ELSE
2482 l_src_query := '(SELECT ';
2483 FOR attr IN (SELECT * FROM bom_attrs_v)
2484 LOOP
2485 IF attr.attr_group_type <> 'BOM_COMPONENTMGMT_GROUP'
2486 THEN
2487 IF attr.attr_group_type = 'BOM_COMPONENT_BASE' THEN
2488 l_src_query := l_src_query || ' null as ' || attr.database_column || ' ,';
2489 END IF;
2490 END IF;
2491 END LOOP;
2492 l_src_query := SUBSTR(l_src_query, 1, LENGTH(l_src_query)-1);
2493 l_src_query := l_src_query || ' from dual ) src_val, ';
2494 END IF;
2495 IF p_comp_seq_id IS NOT NULL THEN
2496 l_where_clause := ' WHERE ((BCEB.component_sequence_id = :9 AND BCEB.structure_type_id = :4 AND attr.attr_group_id = BCEB.ATTR_GROUP_ID) AND (';
2497 ELSE
2498 l_where_clause := 'WHERE ((BCEB.component_sequence_id = :9 AND BCEB.structure_type_id = :4 AND 1=2 ) OR (';
2499 END IF;
2500 l_where_clause := l_where_clause || ' BCUA.batch_id = :10 AND (BCUA.comp_source_system_reference = :11 OR BCUA.component_sequence_id = :12) '
2501 || ' AND BCUA.attr_int_name = attr.attr_name AND '
2502 || ' BCUA.structure_type_id = '|| p_str_type_id || ' AND BCUA.attr_disp_value IS NOT NULL AND attr.attr_group_type = ' || ''''
2503 || 'BOM_COMPONENTMGMT_GROUP' || ''' AND (BCUA.ATTR_GROUP_ID = '
2504 || ' attr.attr_group_id OR BCUA.attr_group_int_name = attr.attr_group_name )))';
2505
2506
2507 /* l_where_clause := ' WHERE (( BCEB.structure_type_id = :4 '
2508 || ' AND attr.attr_group_type = ' || ''''|| 'BOM_COMPONENTMGMT_GROUP'|| ''' AND attr.attr_group_id = BCEB.ATTR_GROUP_ID ) OR '
2509 || '(BCUA.batch_id = ' || p_batch_id ||' AND BCUA.comp_source_system_reference = ' || '''' || p_ss_record_id || ''' AND BCUA.attr_int_name = attr.attr_name AND '
2510 || ' BCUA.structure_type_id = '|| p_str_type_id || ' AND BCUA.attr_disp_value IS NOT NULL AND attr.attr_group_type = ' || ''''|| 'BOM_COMPONENTMGMT_GROUP' || ''' AND (BCUA.ATTR_GROUP_ID = '
2511 || ' attr.attr_group_id OR BCUA.attr_group_int_name = attr.attr_group_name )))';*/
2512
2513 --dinu_log_message(l_attr_sql);
2514 l_attr_sql := l_attr_sql || ' FROM bom_attrs_v attr, bom_components_ext_b BCEB,bom_cmp_usr_attr_interface BCUA '||l_where_clause ;
2515 --dinu_log_message(' FROM bom_attrs_v attr, bom_components_ext_b BCEB '||l_where_clause);
2516
2517 l_attr_sql := l_attr_sql || ' UNION ALL ' || l_attr_sql1
2518 || ' FROM (SELECT attr_group_name,attr_group_disp_name FROM ego_attr_groups_v WHERE attr_group_type = '||''''|| 'BOM_COMPONENT_BASE' || ''' AND application_id = 702 ORDER BY attr_group_name) grps,'
2519 || '(SELECT attr_name,attr_display_name,database_column,attr_group_name FROM ego_attrs_v WHERE attr_group_type = '|| ''''|| 'BOM_COMPONENT_BASE' || ''' AND application_id = 702 ORDER BY attr_group_name) attrs,'
2520 || l_src_query || l_pdh_query ||
2521 ' WHERE attrs.attr_group_name = grps.attr_group_name';
2522 --dinu_log_message(' UNION ALL ' );
2523 --dinu_log_message(l_attr_sql1 );
2524 --dinu_log_message(' FROM bom_attrs_v attr,');
2525 --dinu_log_message(l_src_query );
2526 --dinu_log_message(l_pdh_query );
2527 --dinu_log_message( ' WHERE attr.attr_group_type = '|| '''' || 'BOM_COMPONENT_BASE'||'''' );
2528 l_attr_sql := l_attr_sql || ' UNION ALL SELECT Bom_Globals.Retrieve_Message('|| '''' || 'BOM' || ''','||''''|| 'BOM_IMPORT_REF_DESGS'||'''),null,null, ' || p_batch_id || ' batch_identifier '
2529 ||',bom_import_pub.get_ref_desgs(:1,:2,:3,1,:5,:6,:7,:8),bom_import_pub.get_ref_desgs(:1,:2,:3,null,:5,:6,:7,:8) from dual' ;
2530
2531 /*dinu_log_message(' UNION ALL SELECT Bom_Globals.Retrieve_Message('|| '''' || 'BOM' || ''','||''''|| 'BOM_IMPORT_REF_DESGS'||'''),null,' || p_batch_id || ' batch_identifier ,';
2532 ||'bom_import_pub.get_ref_desgs(:1,:2,:3,1,:5,:6,:7,:8),');*/
2533 --dinu_log_message(' bom_import_pub.get_ref_desgs(:1,:2,:3,null,:5,:6,:7,:8) from dual' );
2534
2535
2536 l_dummy := NULL;
2537
2538 IF p_ss_record_id IS NOT NULL
2539 THEN
2540 IF p_comp_seq_id IS NOT NULL
2541 THEN
2542 EXECUTE IMMEDIATE l_attr_sql bulk collect INTO attr_grp,attr,attr_name,batch_identifier,src_attr,pdh_attr USING p_comp_seq_id,p_str_type_id,p_batch_id,
2543 p_ss_record_id,p_comp_seq_id,p_batch_id,
2544 p_ss_record_id,p_comp_seq_id,p_batch_id,p_ss_record_id,p_comp_seq_id,p_effec_date,p_op_seq_num,p_item_id,p_org_id,p_batch_id,p_ss_record_id,p_comp_seq_id,p_effec_date,p_op_seq_num,p_item_id,p_org_id;
2545 ELSE
2546 EXECUTE IMMEDIATE l_attr_sql bulk collect INTO attr_grp,attr,attr_name,batch_identifier,src_attr,pdh_attr USING p_comp_seq_id,p_str_type_id,p_batch_id,
2547 p_ss_record_id,p_comp_seq_id,p_batch_id,
2548 p_ss_record_id,p_batch_id,p_ss_record_id,l_dummy,p_effec_date,p_op_seq_num,p_item_id,p_org_id,p_batch_id,p_ss_record_id,l_dummy,p_effec_date,p_op_seq_num,p_item_id,p_org_id;
2549 END IF;
2550 ELSE
2551 EXECUTE IMMEDIATE l_attr_sql bulk collect INTO attr_grp,attr,attr_name,batch_identifier,src_attr,pdh_attr USING p_comp_seq_id,p_str_type_id,p_batch_id,p_ss_record_id,p_comp_seq_id,
2552 p_comp_seq_id,p_batch_id,p_ss_record_id,l_dummy,p_effec_date,p_op_seq_num,p_item_id,p_org_id,p_batch_id,p_ss_record_id,l_dummy,p_effec_date,p_op_seq_num,p_item_id,p_org_id;
2553
2554 /*EXECUTE IMMEDIATE l_attr_sql bulk collect INTO attr_grp,attr,batch_identifier,src_attr,pdh_attr USING p_str_type_id,
2555 p_batch_id,l_dummy,p_comp_seq_id,p_effec_date,p_op_seq_num,p_item_id,p_org_id,p_batch_id,l_dummy,p_comp_seq_id,p_effec_date,p_op_seq_num,p_item_id,p_org_id;*/
2556
2557 END IF;
2558
2559 l_count := attr_grp.COUNT;
2560
2561
2562 FOR i IN 1..l_count
2563 LOOP
2564 l_attr_diff.extend();
2565 l_attr_row.attr_grp_display_name := attr_grp(i);
2566 l_attr_row.attr_display_name := attr(i);
2567 l_attr_row.attr_name := attr_name(i);
2568 l_attr_row.batch_identifier := batch_identifier(i);
2569 l_attr_row.src_attr_value := src_attr(i);
2570 l_attr_row.pdh_attr_value := pdh_attr(i);
2571 l_attr_diff(i) := l_attr_row;
2572 END LOOP;
2573 RETURN l_attr_diff;
2574
2575 END BOM_GET_COMP_ATTR_DATA;
2576
2577
2578 /**
2579 * This procedure is used by the EGO team to notify that
2580 * matching of all the uploaded records is over and
2581 * further processing can be continued.
2582 */
2583 PROCEDURE Matching_Complete
2584 (
2585 p_batch_id IN NUMBER
2586 , x_return_status IN OUT NOCOPY VARCHAR2
2587 , x_Error_Mesg IN OUT NOCOPY VARCHAR2
2588 )
2589 IS
2590
2591 BEGIN
2592
2593 BOM_IMPORT_PUB.Matching_Complete
2594 (
2595 p_batch_id => p_batch_id
2596 , x_return_status => x_return_status
2597 , x_Error_Mesg => x_Error_Mesg
2598 , p_init_msg_list => 'N'
2599 , p_debug => 'N'
2600 , p_output_dir => NULL
2601 , p_debug_filename => NULL
2602 );
2603
2604 END Matching_Complete;
2605
2606
2607 /**
2608 * This procedure is used by the EGO team to notify that
2609 * matching of all the uploaded records is over and
2610 * further processing can be continued.
2611 */
2612 PROCEDURE Matching_Complete
2613 (
2614 p_batch_id IN NUMBER
2615 , p_init_msg_list IN VARCHAR2
2616 , x_return_status IN OUT NOCOPY VARCHAR2
2617 , x_Error_Mesg IN OUT NOCOPY VARCHAR2
2618 , p_debug IN VARCHAR2
2619 , p_output_dir IN VARCHAR2
2620 , p_debug_filename IN VARCHAR2
2621 )
2622 IS
2623 G_EXC_SEV_QUIT_OBJECT EXCEPTION;
2624
2625 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
2626 l_other_message VARCHAR2(50);
2627 l_Token_Tbl Error_Handler.Token_Tbl_Type;
2628 l_err_text VARCHAR2(2000);
2629 l_return_status VARCHAR2(1);
2630 l_Debug_flag VARCHAR2(1) := p_debug;
2631 l_source_system_id NUMBER;
2632 l_debug BOOLEAN := FALSE;
2633
2634 BEGIN
2635 IF p_init_msg_list = 'Y'
2636 THEN
2637 Error_Handler.Initialize();
2638 END IF;
2639
2640 IF l_debug_flag = 'Y'
2641 THEN
2642 IF trim(p_output_dir) IS NULL OR
2643 trim(p_output_dir) = ''
2644 THEN
2645 -- IF debug is Y THEN out dir must be
2646 -- specified
2647
2648 Error_Handler.Add_Error_Token
2649 ( p_Message_text =>
2650 'Debug is set to Y so an output directory' ||
2651 ' must be specified. Debug will be turned' ||
2652 ' off since no directory is specified'
2653 , p_Mesg_Token_Tbl => l_Mesg_Token_tbl
2654 , x_Mesg_Token_Tbl => l_Mesg_Token_tbl
2655 , p_Token_Tbl => l_Token_Tbl
2656 );
2657 l_debug_flag := 'N';
2658 END IF;
2659
2660 IF trim(p_debug_filename) IS NULL OR
2661 trim(p_debug_filename) = ''
2662 THEN
2663 Error_Handler.Add_Error_Token
2664 ( p_Message_text =>
2665 'Debug is set to Y so an output filename' ||
2666 ' must be specified. Debug will be turned' ||
2667 ' off since no filename is specified'
2668 , p_Mesg_Token_Tbl => l_mesg_token_tbl
2669 , x_Mesg_Token_Tbl => l_mesg_token_tbl
2670 , p_Token_Tbl => l_token_tbl
2671 );
2672 l_debug_flag := 'N';
2673 END IF;
2674 BOM_Globals.Set_Debug(l_debug_flag);
2675
2676 IF BOM_Globals.Get_Debug = 'Y'
2677 THEN
2678 Error_Handler.Open_Debug_Session
2679 ( p_debug_filename => p_debug_filename
2680 , p_output_dir => p_output_dir
2681 , x_return_status => l_return_status
2682 , p_mesg_token_tbl => l_mesg_token_tbl
2683 , x_mesg_token_tbl => l_mesg_token_tbl
2684 );
2685
2686 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
2687 THEN
2688 l_debug_flag := 'N';
2689 END IF;
2690 END IF;
2691 END IF;
2692
2693 Bom_Globals.Set_Debug(l_debug_flag);
2694
2695 /*IF NOT Does_Batch_Exist(p_batch_id)
2696 THEN
2697 l_other_message := 'BOM_BATCH_NOT_VALID';
2698 l_Token_Tbl(1).token_name := 'BATCH_ID';
2699 l_Token_Tbl(1).token_value := p_batch_id;
2700 RAISE G_EXC_SEV_QUIT_OBJECT;
2701 END IF;*/
2702
2703 IF Error_Handler.Get_Debug <> 'Y' THEN
2704 l_debug := Init_Debug();
2705 ELSE
2706 l_debug := TRUE;
2707 END IF;
2708
2709 Write_Debug('after validatng batch_ id in Matching Complete');
2710
2711 /* BEGIN
2712 SELECT
2713 source_system_id
2714 INTO
2715 l_source_system_id
2716 FROM
2717 ego_import_batches_b
2718 WHERE
2719 batch_id = p_batch_id;
2720
2721 EXCEPTION
2722 WHEN NO_DATA_FOUND
2723 THEN
2724 l_other_message := 'BOM_SOURCE_SYSTEM_INVALID';
2725 l_Token_Tbl(1).token_name := 'BATCH_ID';
2726 l_Token_Tbl(1).token_value := p_batch_id;
2727 RAISE G_EXC_SEV_QUIT_OBJECT;
2728 END;
2729 */
2730 Write_Debug('Calling Update Match Data');
2731
2732 UPDATE_MATCH_DATA
2733 (
2734 p_batch_id => p_batch_id,
2735 p_source_system_id => NULL,
2736 x_Mesg_Token_Tbl => l_Mesg_Token_Tbl,
2737 x_Return_Status => l_return_status
2738 );
2739 IF l_return_status <> 'S'
2740 THEN
2741 Error_Handler.Add_Error_Token
2742 (
2743 p_message_name => NULL
2744 , p_Mesg_Token_Tbl => l_mesg_token_tbl
2745 , x_Mesg_Token_Tbl => l_mesg_token_tbl
2746 , p_Token_Tbl => l_token_tbl
2747 );
2748
2749 END IF;
2750
2751 /*Write_Debug('after updating match data before update_bill_info');
2752
2753
2754 UPDATE_BILL_INFO
2755 (
2756 p_batch_id => p_batch_id,
2757 x_Mesg_Token_Tbl => l_Mesg_Token_Tbl,
2758 x_Return_Status => l_return_status
2759 );
2760
2761 IF l_return_status <> 'S'
2762 THEN
2763 Error_Handler.Add_Error_Token
2764 (
2765 p_message_name => NULL
2766 , p_Mesg_Token_Tbl => l_mesg_token_tbl
2767 , x_Mesg_Token_Tbl => l_mesg_token_tbl
2768 , p_Token_Tbl => l_token_tbl
2769 );
2770 END IF;
2771
2772 Write_Debug('after updating bill info');*/
2773
2774 x_Return_Status := l_Return_Status;
2775 -- As the Concurrent Manager is now using connection pooling adding
2776 -- the commit to explicitly commit the matching complete changes.
2777 COMMIT;
2778
2779 EXCEPTION
2780 WHEN G_EXC_SEV_QUIT_OBJECT THEN
2781
2782 Error_Handler.Add_Error_Token
2783 (
2784 p_message_name => NULL
2785 , p_Mesg_token_Tbl => l_Mesg_Token_Tbl
2786 , x_Mesg_token_Tbl => l_Mesg_Token_Tbl
2787 , p_token_Tbl => l_Token_Tbl
2788 , p_message_type => 'E'
2789 );
2790 x_return_status := Error_Handler.G_STATUS_UNEXPECTED;
2791 x_Error_Mesg := l_other_message;
2792
2793 IF Bom_Globals.Get_Debug = 'Y'
2794 THEN
2795 Error_Handler.Write_Debug('After getting exception for invalid batch id');
2796 Error_Handler.Write_To_DebugFile;
2797 Error_Handler.Dump_Message_List;
2798 Error_Handler.Close_Debug_Session;
2799 END IF;
2800
2801 END Matching_Complete;
2802
2803
2804
2805
2806
2807 FUNCTION get_G_MISS_NUM
2808 RETURN NUMBER
2809 IS
2810 BEGIN
2811 RETURN FND_API.G_MISS_NUM;
2812
2813 END get_G_MISS_NUM;
2814
2815 FUNCTION get_G_MISS_CHAR RETURN VARCHAR
2816 IS
2817 BEGIN
2818 RETURN FND_API.G_MISS_CHAR;
2819
2820 END get_G_MISS_CHAR;
2821
2822 FUNCTION get_G_MISS_DATE RETURN DATE
2823 IS
2824 BEGIN
2825 RETURN FND_API.G_MISS_DATE;
2826
2827 END get_G_MISS_DATE;
2828
2829 FUNCTION get_ref_desgs
2830 (
2831 p_batch_id IN NUMBER
2832 , p_comp_rec_id IN VARCHAR2
2833 , p_comp_seq_id IN NUMBER
2834 , p_mode IN NUMBER
2835 , p_effec_date IN DATE
2836 , p_op_seq_num IN NUMBER
2837 , p_item_id IN NUMBER
2838 , p_org_id IN NUMBER
2839 )RETURN VARCHAR2
2840 IS
2841 CURSOR c_src_ref_desg ( p_batch_id IN NUMBER,p_ss_ref IN VARCHAR2,p_comp_seq_id IN NUMBER)
2842 IS
2843 SELECT component_reference_designator
2844 FROM bom_ref_desgs_interface
2845 WHERE batch_id = p_batch_id
2846 AND ( ( (comp_source_system_reference = p_ss_ref OR component_item_number = p_ss_ref OR component_item_id = p_item_id )
2847 -- AND effectivity_date = p_effec_date
2848 -- AND operation_seq_num = p_op_seq_num
2849 AND organization_id = p_org_id
2850 )
2851 OR component_sequence_id = p_comp_seq_id
2852 )
2853 AND process_flag <> -1
2854 ORDER BY 1 DESC;
2855
2856 CURSOR c_pdh_ref_desg (p_comp_seq_id IN NUMBER)
2857 IS
2858 SELECT component_reference_designator
2859 FROM bom_reference_designators
2860 WHERE component_sequence_id = p_comp_seq_id
2861 ORDER BY 1 DESC;
2862
2863 l_ref_desg VARCHAR2(32000);
2864 BEGIN
2865 IF p_mode = 1
2866 THEN
2867 l_ref_desg := NULL;
2868 FOR c IN c_src_ref_desg(p_batch_id => p_batch_id,p_ss_ref =>p_comp_rec_id,p_comp_seq_id => p_comp_seq_id)
2869 LOOP
2870 l_ref_desg := c.component_reference_designator || ',' || l_ref_desg;
2871 END LOOP;
2872 IF (l_ref_desg IS NOT NULL) THEN
2873 l_ref_desg := SUBSTR(l_ref_desg, 1, LENGTH(l_ref_desg) - 1);
2874 END IF;
2875 RETURN l_ref_desg;
2876 ELSE
2877 l_ref_desg := NULL;
2878 FOR c IN c_pdh_ref_desg(p_comp_seq_id => p_comp_seq_id)
2879 LOOP
2880 l_ref_desg := c.component_reference_designator || ',' || l_ref_desg;
2881 END LOOP;
2882 IF (l_ref_desg IS NOT NULL) THEN
2883 l_ref_desg := SUBSTR(l_ref_desg, 1, LENGTH(l_ref_desg) - 1);
2884 END IF;
2885 RETURN l_ref_desg;
2886 END IF;
2887
2888 END get_ref_desgs;
2889
2890 PROCEDURE Update_User_Attr_Data
2891 (
2892 p_batch_id IN NUMBER
2893 , p_transaction_id IN NUMBER
2894 , p_comp_seq_id IN NUMBER
2895 , p_bill_seq_id IN NUMBER
2896 , p_call_Ext_Api IN VARCHAR2
2897 , p_parent_id IN NUMBER
2898 , p_org_id IN NUMBER
2899 , x_Return_Status IN OUT NOCOPY VARCHAR2
2900 , x_Error_Text IN OUT NOCOPY VARCHAR2
2901 )
2902 IS
2903
2904 l_comp_id NUMBER;
2905 l_org_id NUMBER;
2906 l_txn_type VARCHAR2(1000);
2907
2908 TYPE bom_cmp_usr_type IS TABLE OF bom_cmp_usr_attr_interface%ROWTYPE;
2909
2910 l_attr_table bom_cmp_usr_type;
2911 l_debug BOOLEAN := false;
2912 l_return_status VARCHAR2(1);
2913 l_err_text VARCHAR2(5000);
2914 l_count NUMBER;
2915 l_comp_seq_id NUMBER := p_comp_seq_id;
2916 l_err_code NUMBER;
2917 l_msg_count NUMBER;
2918 l_user_name FND_USER.USER_NAME%TYPE := FND_GLOBAL.USER_NAME;
2919 l_gz_party_id VARCHAR2(30);
2920 l_bill_seq_id NUMBER := p_bill_seq_id;
2921 l_comp_name VARCHAR2(100);
2922 l_parent_name VARCHAR2(100);
2923 l_target_sql VARCHAR2(1000);
2924 l_add_class VARCHAR2(1000);
2925 l_edit_prvlg VARCHAR2(20);
2926 l_par_edit_prvlg VARCHAR2(10);
2927 l_parent_id NUMBER;
2928
2929
2930
2931 BEGIN
2932
2933 -- l_debug := Init_Debug();
2934
2935 IF p_call_Ext_Api <> 'T' THEN
2936 -- write_debug('In Update User Attr');
2937
2938 IF l_comp_seq_id IS NULL THEN
2939
2940 SELECT component_sequence_id
2941 INTO l_comp_seq_id
2942 FROM bom_inventory_comps_interface
2943 WHERE batch_id = p_batch_id
2944 AND (process_flag = 1 OR process_flag = 5)
2945 AND transaction_id = p_transaction_id;
2946 END IF;
2947
2948 IF l_bill_seq_id IS NULL THEN
2949
2950 SELECT bill_sequence_id
2951 INTO l_bill_seq_id
2952 FROM bom_inventory_comps_interface
2953 WHERE batch_id = p_batch_id
2954 AND (process_flag = 1 OR process_flag = 5)
2955 AND transaction_id = p_transaction_id;
2956 END IF;
2957
2958 IF ( p_transaction_id IS NOT NULL OR p_comp_seq_id IS NOT NULL )THEN
2959
2960 SELECT component_item_number,assembly_item_number,component_item_id,organization_id,UPPER(transaction_type)
2961 INTO l_comp_name,l_parent_name,l_comp_id,l_org_id,l_txn_type
2962 FROM bom_inventory_comps_interface
2963 WHERE batch_id = p_batch_id
2964 AND (process_flag = 1 or process_flag = 5)
2965 AND (component_sequence_id = p_comp_seq_id OR transaction_id = p_transaction_id);
2966 END IF;
2967
2968
2969 /*
2970 * we need to update the pks here as the Insert_Default_Val_Rows ext api checks for these pks before inserting the default rows.
2971 * if we dont update the pks , then in case we have some rows for some attrs in the excel and if that attr has default values, ext
2972 * api will once again insert the default rows.Also we need to update the attr group id.
2973 */
2974
2975
2976 UPDATE bom_cmp_usr_attr_interface BCUA
2977 SET component_sequence_id = l_comp_seq_id,
2978 bill_sequence_id = l_bill_seq_id,
2979 process_status = 2,
2980 attr_group_id = (select attr_group_id from EGO_FND_DSC_FLX_CTX_EXT where application_id = 702 and DESCRIPTIVE_FLEXFIELD_NAME = 'BOM_COMPONENTMGMT_GROUP' and DESCRIPTIVE_FLEX_CONTEXT_CODE = BCUA.attr_group_int_name),
2981 attr_group_type = 'BOM_COMPONENTMGMT_GROUP'
2982 WHERE (BCUA.data_set_id = p_batch_id or BCUA.batch_id = p_batch_id )
2983 AND BCUA.process_status NOT in (3,4)
2984 AND ( (BCUA.component_sequence_id = l_comp_seq_id)
2985 OR (BCUA.component_sequence_id IS NULL
2986 AND BCUA.item_number = l_comp_name
2987 AND BCUA.assembly_item_number = l_parent_name
2988 AND BCUA.transaction_id = p_transaction_id)
2989 );
2990 /**
2991 * Only for new component creation use the attribute default logic.
2992 */
2993 IF l_txn_type = 'CREATE' THEN
2994
2995 /* The target sql should give the pk values,class code and data level values to Ext API.
2996 * We have them in this context and so no need to query them again
2997 */
2998
2999 l_target_sql := 'SELECT ' || l_comp_seq_id || ' component_sequence_id , ' || l_bill_seq_id ||
3000 ' bill_sequence_id, ' || pG_batch_options.structure_type_id ||
3001 ' structure_type_id , null DATA_LEVEL_COLUMN, '|| p_transaction_id || ' transaction_id FROM dual ';
3002
3003 l_add_class := 'SELECT bst.structure_type_id FROM BOM_STRUCTURE_TYPES_B bst START WITH bst.structure_type_id = ' || pG_batch_options.structure_type_id || ' CONNECT BY PRIOR bst.parent_structure_type_id = bst.structure_type_id ';
3004
3005
3006 EGO_USER_ATTRS_BULK_PVT.Insert_Default_Val_Rows (
3007 p_api_version => 1.0
3008 ,p_application_id => 702
3009 ,p_attr_group_type => 'BOM_COMPONENTMGMT_GROUP'
3010 ,p_object_name => 'BOM_COMPONENTS'
3011 ,p_interface_table_name => 'BOM_CMP_USR_ATTR_INTERFACE'
3012 ,p_data_set_id => p_batch_id
3013 ,p_target_entity_sql => l_target_sql
3014 ,p_additional_class_Code_query => l_add_class
3015 ,p_commit => 'T'
3016 ,x_return_status => l_return_status
3017 ,x_msg_data => l_err_text
3018 );
3019 END IF;
3020
3021 /*
3022 * Update comp_item_id and org_id.Otherwise ext bulkload will fail for privilege check.We check for
3023 * Edit item and View Item privileges.For this we need the comp ids and org ids.
3024 */
3025
3026 UPDATE bom_cmp_usr_attr_interface
3027 SET component_item_id = l_comp_id,
3028 organization_id = l_org_id,
3029 attr_group_type = 'BOM_COMPONENTMGMT_GROUP'
3030 WHERE (data_set_id = p_batch_id or batch_id = p_batch_id )
3031 AND ( (component_sequence_id = l_comp_seq_id)
3032 OR (component_sequence_id IS NULL
3033 AND item_number = l_comp_name
3034 AND assembly_item_number = l_parent_name
3035 AND transaction_id = p_transaction_id)
3036 );
3037
3038 ELSE
3039
3040 IF l_user_name IS NOT NULL THEN
3041 SELECT 'HZ_PARTY:'||TO_CHAR(PERSON_ID)
3042 INTO l_gz_party_id
3043 FROM ego_people_v
3044 WHERE USER_NAME = l_user_name;
3045 END IF;
3046
3047
3048 /* IF l_bill_seq_id IS NOT NULL THEN
3049 UPDATE bom_cmp_usr_attr_interface
3050 SET process_status = 2
3051 WHERE data_set_id = p_batch_id
3052 AND bill_sequence_id = l_bill_seq_id
3053 AND process_status = 0;
3054 END IF;*/
3055
3056 /*
3057 * When the parent item has edit item privilege , even if there's no edit privilege on the component
3058 * the user attributes should be processed.
3059 */
3060
3061 l_edit_prvlg := 'EGO_EDIT_ITEM';
3062 l_par_edit_prvlg := null;
3063
3064 /*IF l_bill_seq_id IS NOT NULL THEN
3065 SELECT assembly_item_id,organization_id
3066 INTO l_parent_id,l_org_id
3067 FROM bom_structures_b
3068 WHERE bill_sequence_id = l_bill_seq_id;*/
3069 IF p_parent_id IS NOT NULL AND p_org_id IS NOT NULL THEN
3070 l_par_edit_prvlg := EGO_DATA_SECURITY.CHECK_FUNCTION(1.0,'EGO_EDIT_ITEM','EGO_ITEM',p_parent_id,p_org_id,null,null,null,l_gz_party_id);
3071 END IF;
3072
3073 IF nvl(l_par_edit_prvlg,'F') = 'T' THEN
3074 l_edit_prvlg := null;
3075 END IF;
3076
3077 EGO_USER_ATTRS_BULK_PVT.Bulk_Load_User_Attrs_Data
3078 (
3079 p_api_version => 1.0
3080 , p_application_id => 702
3081 , p_attr_group_type => 'BOM_COMPONENTMGMT_GROUP'
3082 , p_object_name => 'BOM_COMPONENTS'
3083 , p_hz_party_id => l_gz_party_id
3084 , p_interface_table_name => 'BOM_CMP_USR_ATTR_INTERFACE'
3085 , p_data_set_id => p_batch_id
3086 , p_related_class_codes_query => 'SELECT bst.structure_type_id FROM BOM_STRUCTURE_TYPES_B bst START WITH bst.structure_type_id = UAI2.STRUCTURE_TYPE_ID CONNECT BY PRIOR bst.parent_structure_type_id = bst.structure_type_id '
3087 , p_init_fnd_msg_list => 'F'
3088 , p_log_errors => 'T'
3089 , p_add_errors_to_fnd_stack => 'T'
3090 , p_commit => 'T'
3091 , p_default_view_privilege => 'EGO_VIEW_ITEM'
3092 , p_default_edit_privilege => l_edit_prvlg
3093 , p_privilege_predicate_api_name => 'Bom_Import_Pub.Get_Item_Security_Predicate'
3094 , p_validate => true
3095 , p_do_dml => true
3096 , x_return_status => l_return_status
3097 , x_errorcode => l_err_code
3098 , x_msg_count => l_msg_count
3099 , x_msg_data => l_err_text
3100 );
3101
3102 -- If the bulkload is successfull then we need to update the process_status of
3103 -- interface rows to 4 , so that if again uploaded these rows are not processed.
3104 -- our processing cycle is for each header, so updating the process_status for processed headers
3105
3106 IF l_return_status = 'S' THEN
3107 UPDATE bom_cmp_usr_attr_interface
3108 SET process_status = 4
3109 WHERE ( data_set_id = p_batch_id or batch_id = p_batch_id)
3110 AND process_status = 2
3111 AND bill_sequence_id = l_bill_seq_id;
3112 END IF;
3113
3114 END IF;
3115 x_Return_Status := l_return_status;
3116 x_Error_Text := l_err_text;
3117
3118 EXCEPTION
3119 WHEN OTHERS THEN
3120 x_Return_Status := 'U';
3121 x_Error_Text := SUBSTR(SQLERRM, 1, 200);
3122
3123 END Update_User_Attr_Data;
3124
3125 /************************************************************************
3126 * Procedure: Data_Upload_Complete
3127 * Purpose : This method will be called by users after uploading batch data
3128 * in bom interface tables.
3129 * This will do the following steps
3130 * 1. Resolve the XREFs for existing cross references
3131 * 2. Call Item APIs to upload unmatched data
3132 * a. IF unmatched items are inserted Notify
3133 * Item Ego Data Upload Complete API
3134 * 3. Check Batch for Options - IF automated call import
3135 **************************************************************************/
3136
3137 PROCEDURE Data_Upload_Complete
3138 ( p_batch_id IN NUMBER
3139 , x_error_message OUT NOCOPY VARCHAR2
3140 , x_return_code OUT NOCOPY VARCHAR2
3141 )
3142 IS
3143 BEGIN
3144 BOM_IMPORT_PUB.DATA_UPLOAD_COMPLETE
3145 (
3146 p_batch_id => p_batch_id,
3147 x_Error_Mesg => x_error_message,
3148 p_init_msg_list => 'N',
3149 x_return_status => x_return_code,
3150 p_debug => 'N',
3151 p_output_dir => NULL,
3152 p_debug_filename => NULL
3153 );
3154
3155 END Data_Upload_Complete;
3156
3157 /************************************************************************
3158 * Procedure: IMPORT_STRUCTURE_DATA
3159 * Purpose : This method will be called by users after uploading batch data
3160 * in bom interface tables.
3161
3162 **************************************************************************/
3163 PROCEDURE IMPORT_STRUCTURE_DATA
3164 (
3165 p_batch_id IN NUMBER
3166 , p_items_import_complete IN VARCHAR2
3167 , p_callFromJCP IN VARCHAR2
3168 , p_request_id IN NUMBER
3169 , x_error_message OUT NOCOPY VARCHAR2
3170 , x_return_code OUT NOCOPY VARCHAR2
3171 )
3172 IS
3173 l_debug BOOLEAN := FALSE;
3174 l_request_id NUMBER;
3175 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
3176 BEGIN
3177
3178 --Update the Batch Record for reference
3179 IF Error_Handler.get_debug = 'Y' THEN
3180 l_debug := TRUE;
3181 ELSE
3182 l_debug := Init_Debug();
3183 END IF;
3184
3185 Retrieve_Batch_Options(p_batch_id => p_batch_id,
3186 x_Mesg_Token_Tbl => l_Mesg_Token_Tbl,
3187 x_error_code => x_return_code);
3188
3189 Write_Debug(' Calling the PRE_PROCESS_IMPORT_ROWS with p_batch_id ' || p_batch_id || ' item_complt ' ||p_items_import_complete );
3190
3191 PRE_PROCESS_IMPORT_ROWS
3192 ( p_batch_id => p_batch_id
3193 , x_error_message => x_error_message
3194 , p_items_import_complete => p_items_import_complete
3195 , x_return_code => x_return_code
3196 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3197 );
3198
3199 Write_Debug('After PRE_PROCESS_IMPORT_ROWS with ret code-'||x_return_code);
3200
3201 Write_Debug('p_callFromJCP -'|| p_callFromJCP);
3202
3203 IF (p_callFromJCP = 'Y') THEN
3204 RETURN;
3205 ELSE
3206 -- Call to launch the Java Concurrent Program - is this required??
3207 IF (p_request_id IS NOT NULL) THEN
3208 l_request_id := p_request_id;
3209 END IF;
3210 Write_Debug('Launching JCP p_items_import_complete='||p_items_import_complete);
3211
3212 IF (p_items_import_complete = 'T') THEN
3213
3214 l_request_id := Fnd_Request.Submit_Request(
3215 application => G_APP_SHORT_NAME,
3216 program => 'BOMJCP',
3217 sub_request => FALSE,
3218 argument1 => l_request_id,
3219 argument2 => p_batch_id);
3220 Write_Debug('Launched JCP with rqst id-'||l_request_id);
3221 END IF;
3222 END IF;
3223
3224
3225
3226 --IF nto we will launch the JCP
3227
3228 --(Once JCP is done - it will call our procedure for UA
3229 -- Propagate the failures or update the ids
3230 --JCP will actually kick off the User Attributes Stuff)
3231
3232 END IMPORT_STRUCTURE_DATA;
3233
3234
3235 /************************************************************************
3236 * Procedure: PRE_PROCESS_IMPORT_ROWS
3237 * Purpose : This method will be called by users after uploading batch data
3238 * in bom interface tables.
3239 Check Rows in MTL_INTERFACES WITH SAME BATCH ID
3240 AND PROCESS_FLAG 1
3241 IF (EXISTS) THEN
3242 NOTIFY ITEMS DATA LOAD.
3243 ELSE
3244 RUN XREF-MATCHES
3245 IF ANY UNMATCHED RECORDS THEN
3246 INSERT INTO ITEMS AND NOTIFY ITEMS DATALOAD
3247 ELSE
3248 CALL BOM JCP
3249 END IF;
3250 END IF;
3251 Update the bill information - Call Dinu's API
3252 UPdate IF change required to 5.
3253 *************************************************************************/
3254
3255 PROCEDURE PRE_PROCESS_IMPORT_ROWS
3256 (
3257 p_batch_id IN NUMBER
3258 , p_items_import_complete IN VARCHAR2
3259 , x_error_message OUT NOCOPY VARCHAR2
3260 , x_return_code OUT NOCOPY VARCHAR2
3261 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
3262 )
3263 IS
3264 l_item_infcrows_exists NUMBER;
3265 BEGIN
3266 --Already Updated the Batch Record for reference
3267 x_return_code := NULL;
3268
3269 Write_Debug('Inside pre_preocess_import starting process batch options');
3270
3271 Process_Batch_Options(p_batch_id => p_batch_id);
3272
3273 IF (pG_batch_options.PDH_BATCH = 'Y') THEN
3274 Write_Debug('Returning as this is a pdh batch');
3275 RETURN;
3276 END IF;
3277
3278 Write_Debug('p_items_import_complete = ' || p_items_import_complete);
3279
3280 IF (p_items_import_complete = 'T')
3281 THEN
3282 -- Update Cross References
3283 Write_Debug('Resolving X-REfs');
3284 RESOLVE_XREFS_FOR_BATCH
3285 (
3286 p_batch_id => p_batch_id
3287 ,x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
3288 ,x_Return_Status => x_return_code
3289 );
3290
3291 Write_Debug('After Resolving X-REfs with ret code = ' || x_return_code);
3292
3293
3294 IF (x_return_code = 'E') THEN
3295 RETURN;
3296 END IF;
3297 -- Propagate confirmation status
3298
3299 Write_Debug('Propagating Confirm Status');
3300
3301 PROPAGATE_CONFIRMATION_STATUS
3302 (
3303 p_batch_id => p_batch_id
3304 , x_error_message => x_error_message
3305 , x_return_code => x_return_code
3306 );
3307 IF (x_return_code = 'E') THEN
3308 --handle error
3309 RETURN;
3310 END IF;
3311 -- finally Call BOM JCP
3312 ELSE
3313 SELECT COUNT(*) INTO l_item_infcrows_exists FROM
3314 ( SELECT
3315 'X'
3316 FROM
3317 mtl_system_items_interface
3318 WHERE EXISTS
3319 (SELECT
3320 process_flag
3321 FROM
3322 mtl_system_items_interface
3323 WHERE
3324 set_process_id = p_batch_id
3325 AND process_flag = 1
3326 UNION ALL
3327 SELECT
3328 process_flag
3329 FROM
3330 mtl_item_revisions_interface
3331 WHERE
3332 set_process_id = p_batch_id
3333 AND process_flag = 1) ) QRSLT;
3334
3335 IF (l_item_infcrows_exists = 1) THEN
3336 --logMessage_forsnell('Call Item API?? - Verifying');
3337 NULL;
3338 ELSE
3339 -- Update Cross References
3340 RESOLVE_XREFS_FOR_BATCH
3341 (
3342 p_batch_id => p_batch_id
3343 ,x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
3344 ,x_Return_Status => x_return_code
3345 );
3346
3347 IF (pG_batch_options.IMPORT_XREF_ONLY <> 'Y')
3348 THEN
3349 --logMessage_forsnell('Import NON Xrefs also');
3350 --check IF dinu has one??
3351 Write_Debug('INSERT INTO ITEMS AND NOTIFY ITEMS DATALOAD AND NOTIFY ITEMS');
3352 /* IF ANY UNMATCHED RECORDS THEN INSERT INTO ITEMS AND NOTIFY ITEMS DATALOAD AND NOTIFY ITEMS */
3353 NULL;
3354 END IF; -- Import Xrefs Only
3355 END IF; -- IF item interface rows exist
3356 END IF; -- ELSE of item import complete
3357
3358 --commenting this out as matching complete already has this call
3359 /* Update_Bill_Info
3360 (
3361 p_batch_id => p_batch_id
3362 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
3363 , x_Return_Status => x_Return_code
3364 );*/
3365
3366 --Change Management Check
3367 Process_Batch_Options(p_batch_id => p_batch_id);
3368
3369 EXCEPTION
3370 WHEN OTHERS THEN
3371 --logMessage_forsnell('reching here p_organization_id' || SQLERRM);
3372 x_Return_code := 'E';
3373
3374 END PRE_PROCESS_IMPORT_ROWS;
3375
3376 /************************************************************************
3377 * Procedure: PROPAGATE_CONFIRMATION_STATUS
3378 * Purpose : This method will propagate the confirmation status for
3379 * the import rows from EGO tables to Structure Entities
3380 * Program Logic: For all Unconfirmed and Excluded Items do not process
3381 * their children For all uncofirmed children do not process
3382 * their Parent
3383 * The above is accomplished by setting the id columns to null
3384 *****************************************************************************/
3385 PROCEDURE PROPAGATE_CONFIRMATION_STATUS
3386 (
3387 p_batch_id IN NUMBER
3388 , x_error_message OUT NOCOPY VARCHAR2
3389 , x_return_code OUT NOCOPY VARCHAR2
3390 )
3391 IS
3392 /* Cursor to select confirm_status 'E' and 'U' rows for all Item Rows */
3393 CURSOR Item_Intf_NotReadyCr IS
3394 SELECT
3395 source_system_reference,
3396 inventory_item_id,
3397 organization_id,
3398 confirm_status,
3399 process_flag
3400 FROM
3401 mtl_system_items_interface
3402 WHERE
3403 set_process_id = p_batch_id
3404 AND confirm_status IN ('US','UM','UN','EX');
3405
3406 CURSOR Item_Intf_ReadyCr IS
3407 SELECT
3408 source_system_reference,
3409 inventory_item_id,
3410 organization_id,
3411 confirm_status,
3412 process_flag
3413 FROM
3414 mtl_system_items_interface
3415 WHERE
3416 set_process_id = p_batch_id
3417 AND confirm_status IN ('CC','CM','CN');
3418
3419 BEGIN
3420 x_return_code := NULL;
3421
3422
3423 /* we also need to propagate if a row got confirmed later from an
3424 unconfirmed or excluded state */
3425
3426 FOR iicr IN Item_Intf_ReadyCr
3427 LOOP --iicr cursor loop start
3428 -- Update Bill of materials for Unconfirmed and Excluded
3429 UPDATE
3430 bom_bill_of_mtls_interface
3431 SET
3432 process_flag = 1
3433 WHERE
3434 batch_id = p_batch_id
3435 AND source_system_reference = iicr.source_system_reference
3436 AND process_flag = 0;
3437
3438 -- Update Bill of materials for Unconfirmed Children
3439 UPDATE
3440 bom_bill_of_mtls_interface bmi
3441 SET
3442 process_flag = 1
3443 WHERE
3444 bmi.batch_id = p_batch_id
3445 AND bmi.process_flag = 0
3446 AND bmi.source_system_reference =
3447 ( SELECT DISTINCT
3448 bci.parent_source_system_reference
3449 FROM bom_inventory_comps_interface bci
3450 WHERE
3451 bci.batch_id = p_batch_id
3452 AND bci.comp_source_system_reference = iicr.source_system_reference
3453 AND iicr.confirm_status in ('CC','CM','CN'));
3454 -- Update Components for Unconfirmed and Excluded
3455 UPDATE
3456 bom_inventory_comps_interface
3457 SET
3458 process_flag = 1
3459 WHERE
3460 batch_id = p_batch_id
3461 and process_flag = 0
3462 AND ( comp_source_system_reference = iicr.source_system_reference
3463 OR parent_source_system_reference = iicr.source_system_reference);
3464 --Update other Entities here
3465 END LOOP; --iicr cursor loop end
3466
3467
3468 FOR iicr IN Item_Intf_NotReadyCr
3469 LOOP --iicr cursor loop start
3470 -- Update Bill of materials for Unconfirmed and Excluded
3471 UPDATE
3472 bom_bill_of_mtls_interface
3473 SET
3474 process_flag = 0
3475 WHERE
3476 batch_id = p_batch_id
3477 AND source_system_reference = iicr.source_system_reference
3478 AND process_flag = 1;
3479
3480 -- Update Bill of materials for Unconfirmed Children
3481 UPDATE
3482 bom_bill_of_mtls_interface bmi
3483 SET
3484 process_flag = 0
3485 WHERE
3486 bmi.batch_id = p_batch_id
3487 AND bmi.process_flag = 1
3488 AND bmi.source_system_reference =
3489 ( SELECT DISTINCT
3490 bci.parent_source_system_reference
3491 FROM bom_inventory_comps_interface bci
3492 WHERE
3493 bci.batch_id = p_batch_id
3494 AND bci.comp_source_system_reference = iicr.source_system_reference
3495 AND iicr.confirm_status in ('US','UM','UN'));
3496 -- Update Components for Unconfirmed and Excluded
3497 UPDATE
3498 bom_inventory_comps_interface
3499 SET
3500 process_flag = 0
3501 WHERE
3502 batch_id = p_batch_id
3503 and process_flag = 1
3504 AND ( comp_source_system_reference = iicr.source_system_reference
3505 OR parent_source_system_reference = iicr.source_system_reference);
3506 --Update other Entities here
3507 END LOOP; --iicr cursor loop end
3508
3509
3510 END PROPAGATE_CONFIRMATION_STATUS;
3511
3512 /**
3513 * Concurrent Program Replacement for BMCOIN
3514 * @param p_batch_id Batch Identifier for the batch being Imported
3515 * @param x_error_message Error Message
3516 * @param x_return_code Return code holding return status
3517 * @rep:scope public
3518 * @rep:lifecycle active
3519 * @rep:displayname Pre-Process Import Rows
3520 */
3521 PROCEDURE Import_Interface_Rows
3522 (
3523 x_err_buffer OUT NOCOPY VARCHAR2,
3524 x_return_code OUT NOCOPY VARCHAR2,
3525 p_organization_id IN NUMBER,
3526 p_all_organization IN VARCHAR2,
3527 p_import_routings IN VARCHAR2,
3528 p_import_bills IN VARCHAR2,
3529 p_delete_rows IN VARCHAR2,
3530 p_batch_id IN NUMBER
3531 )
3532 IS
3533 l_error_code VARCHAR2(1);
3534 l_error_message VARCHAR2(3000);
3535 l_return_code NUMBER;
3536 l_batch_metadata_exists NUMBER := NULL;
3537 l_conc_status BOOLEAN;
3538 BEGIN
3539 IF (p_batch_id IS NOT NULL) THEN
3540 SELECT batch_id INTO l_batch_metadata_exists FROM
3541 EGO_IMPORT_BATCHES_B
3542 WHERE BATCH_ID = p_batch_id;
3543 IF (l_batch_metadata_exists IS NOT NULL) THEN
3544 x_return_code := 'E';
3545 x_err_buffer := FND_MESSAGE.GET_STRING(G_APP_SHORT_NAME,'BOM_BATCH_EXIST');
3546 FND_FILE.put_line(FND_FILE.LOG, x_err_buffer);
3547 RETURN;
3548 END IF;
3549 END IF;
3550
3551 l_return_code := BOMPOPIF.bmopinp_open_interface_process(
3552 org_id => p_organization_id,
3553 all_org => p_all_organization,
3554 val_rtg_flag => p_import_routings,
3555 val_bom_flag => p_import_bills,
3556 pro_rtg_flag => p_import_routings,
3557 pro_bom_flag => p_import_bills,
3558 del_rec_flag => p_delete_rows,
3559 prog_appid => FND_GLOBAL.prog_appl_id,
3560 prog_id => FND_GLOBAL.conc_program_id,
3561 request_id => FND_GLOBAL.conc_request_id,
3562 user_id => FND_GLOBAL.login_id,
3563 login_id => FND_GLOBAL.login_id,
3564 p_batch_id => p_batch_id,
3565 err_text => x_err_buffer);
3566 --logMessage_forsnell('after import' || l_return_code );
3567
3568 --bug:5235742 Change the concurrent program completion status. Set warning, if
3569 --some of the entities errored out during delete.
3570 IF ( l_return_code = 0 ) THEN
3571 x_return_code := '0';
3572 Fnd_Message.Set_Name('INV','INV_STATUS_SUCCESS');
3573 x_err_buffer := Fnd_Message.Get;
3574 ELSIF ( l_return_code = 1 ) THEN
3575 x_return_code := '1';
3576 Fnd_Message.Set_Name('BOM','BOM_CONC_REQ_WARNING');
3577 x_err_buffer := Fnd_Message.Get;
3578 ELSE
3579 x_return_code := '2';
3580 END IF;
3581
3582 EXCEPTION
3583 WHEN NO_DATA_FOUND THEN
3584 l_return_code := BOMPOPIF.bmopinp_open_interface_process(
3585 org_id => p_organization_id,
3586 all_org => p_all_organization,
3587 val_rtg_flag => p_import_routings,
3588 val_bom_flag => p_import_bills,
3589 pro_rtg_flag => p_import_routings,
3590 pro_bom_flag => p_import_bills,
3591 del_rec_flag => p_delete_rows,
3592 prog_appid => FND_GLOBAL.prog_appl_id,
3593 prog_id => FND_GLOBAL.conc_program_id,
3594 request_id => FND_GLOBAL.conc_request_id,
3595 user_id => FND_GLOBAL.login_id,
3596 login_id => FND_GLOBAL.login_id,
3597 p_batch_id => p_batch_id,
3598 err_text => x_err_buffer);
3599
3600 IF ( l_return_code = 0 ) THEN
3601 x_return_code := '0';
3602 Fnd_Message.Set_Name('INV','INV_STATUS_SUCCESS');
3603 x_err_buffer := Fnd_Message.Get;
3604 ELSIF ( l_return_code = 1 ) THEN
3605 x_return_code := '1';
3606 Fnd_Message.Set_Name('BOM','BOM_CONC_REQ_WARNING');
3607 x_err_buffer := Fnd_Message.Get;
3608 ELSE
3609 x_return_code := '2';
3610 END IF;
3611
3612 END Import_Interface_Rows;
3613
3614 /**
3615 * This is the procedure for updating the Bill with item names
3616 * for a Pdh Batch Import.IF it is a Pdh Batch Import this
3617 * API will be called and this API will do the id to val
3618 * conversion IF needed.This will also populate the
3619 * source_system_reference with the Item Names or Component
3620 * names.This is for the Structure Import UI to show the
3621 * details of the batch even for a Pdh Batch Import which will
3622 * not have any source_system_reference.
3623 */
3624
3625 PROCEDURE Update_Bill_Val_Id
3626 (
3627 p_batch_id IN NUMBER
3628 , x_return_status IN OUT NOCOPY VARCHAR2
3629 , x_Error_Mesg IN OUT NOCOPY VARCHAR2
3630 )
3631 IS
3632
3633 G_EXC_SEV_QUIT_OBJECT EXCEPTION;
3634
3635 TYPE num_type IS TABLE OF NUMBER;
3636 TYPE var_type IS TABLE OF VARCHAR2(1000);
3637 TYPE date_type IS TABLE OF DATE;
3638
3639 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
3640 l_other_message VARCHAR2(50);
3641 l_Token_Tbl Error_Handler.Token_Tbl_Type;
3642 l_err_text VARCHAR2(2000);
3643 l_return_status VARCHAR2(1);
3644 l_message_list Error_Handler.Error_Tbl_Type;
3645 -- l_debug_flag VARCHAR2(1) := p_debug;
3646 l_debug BOOLEAN := FALSE;
3647
3648
3649 l_comp_item_id_table num_type;
3650 l_head_item_id_table num_type;
3651 l_org_id_table num_type;
3652 l_bill_seq_table num_type;
3653 l_op_seq_table num_type;
3654 l_head_name_table var_type;
3655 l_comp_name_table var_type;
3656 l_alt_desg_table var_type;
3657 l_org_code_table var_type;
3658 l_effectivity_table date_type;
3659 l_count NUMBER;
3660 l_comp_seq_table num_type;
3661
3662 l_request_id NUMBER;
3663
3664 CURSOR Get_Header(p_batch_id IN NUMBER)
3665 IS
3666 SELECT assembly_item_id,organization_id,bill_sequence_id,alternate_bom_designator,item_number,organization_code
3667 FROM bom_bill_of_mtls_interface
3668 WHERE batch_id = p_batch_id
3669 --AND (process_flag = 1 OR process_flag = 5)
3670 AND (assembly_item_id IS NOT NULL OR item_number IS NOT NULL);
3671
3672 CURSOR Get_Comps(p_batch_id IN NUMBER)
3673 IS
3674 SELECT BICI.component_item_id,BICI.organization_id,BICI.bill_sequence_id,BBMI.assembly_item_id,decode(BICI.operation_seq_num,null,
3675 BICI.new_operation_seq_num,BICI.operation_seq_num),decode(BICI.effectivity_date,null,BICI.new_effectivity_date,BICI.effectivity_date),
3676 BICI.component_item_number,BICI.assembly_item_number,BICI.organization_code,BICI.component_sequence_id
3677 FROM bom_inventory_comps_interface BICI,
3678 bom_bill_of_mtls_interface BBMI
3679 WHERE BBMI.batch_id = p_batch_id
3680 AND BICI.batch_id = BBMI.batch_id
3681 --AND (BBMI.process_flag = 1 OR BBMI.process_flag = 5)
3682 --AND (BICI.process_flag = 1 OR BICI.process_flag = 5)
3683 AND (BICI.bill_sequence_id = BBMI.bill_sequence_id OR BICI.assembly_item_id = BBMI.assembly_item_id OR BICI.assembly_item_number = BBMI.item_number)
3684 AND (BICI.component_item_id IS NOT NULL OR BICI.component_item_number IS NOT NULL);
3685
3686
3687 BEGIN
3688 update_transaction_ids(p_batch_id);
3689
3690 IF Error_Handler.Get_Debug <> 'Y' THEN
3691 l_debug := Init_Debug();
3692 ELSE
3693 l_debug := TRUE;
3694 END IF;
3695
3696 Write_Debug('Inside Upd_Bill_Val before Retr Batch Options');
3697
3698
3699 Retrieve_Batch_Options(p_batch_id,l_Mesg_Token_Tbl,l_return_status);
3700
3701 IF l_return_status <> 'S'
3702 THEN
3703 RAISE G_EXC_SEV_QUIT_OBJECT;
3704 END IF;
3705
3706 /*calling the OI util method for pre processing all entities*/
3707 --l_request_id := Bom_Open_Interface_Utl.Process_All_Entities(1,1, -1, -1, -1, -1,null,l_err_text,p_batch_id);
3708
3709 Write_Debug('Fetching the Header');
3710
3711
3712 OPEN Get_Header(p_batch_id);
3713 FETCH Get_Header BULK COLLECT INTO l_head_item_id_table,l_org_id_table,l_bill_seq_table,l_alt_desg_table,l_head_name_table,l_org_code_table;
3714 CLOSE Get_Header;
3715
3716 l_count := l_head_item_id_table.COUNT;
3717
3718 FOR i IN 1..l_count
3719 LOOP
3720
3721 BEGIN
3722
3723 IF l_org_id_table(i) IS NULL
3724 THEN
3725 SELECT organization_id
3726 INTO l_org_id_table(i)
3727 FROM mtl_parameters
3728 WHERE organization_code = l_org_code_table(i);
3729 END IF;
3730
3731 IF l_head_item_id_table(i) IS NULL
3732 THEN
3733 SELECT inventory_item_id
3734 INTO l_head_item_id_table(i)
3735 FROM mtl_system_items_kfv
3736 WHERE concatenated_segments = l_head_name_table(i)
3737 AND organization_id = l_org_id_table(i);
3738 END IF;
3739
3740 --do we need this....?
3741 IF l_head_name_table(i) IS NULL
3742 THEN
3743 SELECT concatenated_segments
3744 INTO l_head_name_table(i)
3745 FROM mtl_system_items_vl
3746 WHERE inventory_item_id = l_head_item_id_table(i)
3747 AND organization_id = l_org_id_table(i);
3748 END IF;
3749
3750 EXCEPTION WHEN NO_DATA_FOUND THEN
3751 --No data found is because its a new item creation.So dont do anything
3752 NULL;
3753
3754 END;
3755
3756 Write_Debug('Updating the Header with Ids');
3757
3758 IF pG_batch_options.PDH_BATCH = 'Y' THEN
3759 UPDATE bom_bill_of_mtls_interface
3760 SET source_system_reference = l_head_name_table(i),
3761 item_number = l_head_name_table(i),
3762 assembly_item_id = l_head_item_id_table(i),
3763 organization_id = l_org_id_table(i)
3764 WHERE ((assembly_item_id = l_head_item_id_table(i) AND organization_id = l_org_id_table(i)) OR (item_number = l_head_name_table(i) AND organization_code = l_org_code_table(i)))
3765 AND NVL(alternate_bom_designator,'Primary') = NVL(l_alt_desg_table(i),'Primary')
3766 --AND (process_flag = 1 OR process_flag = 5)
3767 AND batch_id = p_batch_id;
3768 ELSE
3769 UPDATE bom_bill_of_mtls_interface
3770 SET item_number = l_head_name_table(i),
3771 assembly_item_id = l_head_item_id_table(i),
3772 organization_id = l_org_id_table(i)
3773 WHERE ((assembly_item_id = l_head_item_id_table(i) AND organization_id = l_org_id_table(i)) OR (item_number = l_head_name_table(i) AND organization_code = l_org_code_table(i)))
3774 AND NVL(alternate_bom_designator,'Primary') = NVL(l_alt_desg_table(i),'Primary')
3775 AND (process_flag = 1 OR process_flag = 5)
3776 AND batch_id = p_batch_id;
3777 END IF;
3778
3779 END LOOP;--Loop for header ends here
3780
3781 Write_Debug('Fetching the Comps');
3782
3783 OPEN Get_Comps(p_batch_id);
3784 FETCH Get_Comps BULK COLLECT INTO l_comp_item_id_table,l_org_id_table,l_bill_seq_table,l_head_item_id_table,l_op_seq_table,l_effectivity_table,l_comp_name_table,l_head_name_table,l_org_code_table,l_comp_seq_table;
3785 CLOSE Get_Comps;
3786
3787 l_count := l_comp_item_id_table.COUNT;
3788
3789 FOR i IN 1..l_count
3790 LOOP
3791
3792 BEGIN
3793
3794 IF l_org_id_table(i) IS NULL
3795 THEN
3796 SELECT organization_id
3797 INTO l_org_id_table(i)
3798 FROM mtl_parameters
3799 WHERE organization_code = l_org_code_table(i);
3800 END IF;
3801
3802 IF l_comp_item_id_table(i) IS NULL
3803 THEN
3804 SELECT inventory_item_id
3805 INTO l_comp_item_id_table(i)
3806 FROM mtl_system_items_kfv
3807 WHERE concatenated_segments = l_comp_name_table(i)
3808 AND organization_id = l_org_id_table(i);
3809 END IF;
3810
3811 IF l_comp_name_table(i) IS NULL
3812 THEN
3813 SELECT concatenated_segments
3814 INTO l_comp_name_table(i)
3815 FROM mtl_system_items_vl
3816 WHERE inventory_item_id = l_comp_item_id_table(i)
3817 AND organization_id = l_org_id_table(i);
3818 END IF;
3819
3820 IF l_head_item_id_table(i) IS NULL
3821 THEN
3822 SELECT inventory_item_id
3823 INTO l_head_item_id_table(i)
3824 FROM mtl_system_items_kfv
3825 WHERE concatenated_segments = l_head_name_table(i)
3826 AND organization_id = l_org_id_table(i);
3827 END IF;
3828
3829 -- do we need this ....?
3830 IF l_head_name_table(i) IS NULL
3831 THEN
3832 SELECT concatenated_segments
3833 INTO l_head_name_table(i)
3834 FROM mtl_system_items_vl
3835 WHERE inventory_item_id = l_head_item_id_table(i)
3836 AND organization_id = l_org_id_table(i);
3837 END IF;
3838
3839 EXCEPTION WHEN NO_DATA_FOUND THEN
3840 --No data found is because its a new item creation.So dont do anything
3841 NULL;
3842 END;
3843
3844 Write_Debug('Updating the Comps-- ' ||l_comp_name_table(i) );
3845 Write_Debug('With Parent -- ' || l_head_name_table(i));
3846
3847 IF pG_batch_options.PDH_BATCH = 'Y' THEN
3848 UPDATE bom_inventory_comps_interface
3849 SET comp_source_system_reference = l_comp_name_table(i),
3850 parent_source_system_reference = l_head_name_table(i),
3851 component_item_number = l_comp_name_table(i),
3852 component_item_id = l_comp_item_id_table(i),
3853 assembly_item_number = l_head_name_table(i),
3854 assembly_item_id = l_head_item_id_table(i),
3855 organization_id = l_org_id_table(i)
3856 WHERE ((component_item_id = l_comp_item_id_table(i) AND organization_id = l_org_id_table(i)) OR (component_item_number = l_comp_name_table(i) AND organization_code = l_org_code_table(i)))
3857 AND (assembly_item_id = l_head_item_id_table(i) OR assembly_item_number = l_head_name_table(i))
3858 --AND (process_flag = 1 OR process_flag = 5)
3859 --AND new_operation_seq_num = l_op_seq_table(i)
3860 --AND new_effectivity_date = l_effectivity_table(i)
3861 AND batch_id = p_batch_id;
3862 ELSE
3863 UPDATE bom_inventory_comps_interface
3864 SET component_item_number = l_comp_name_table(i),
3865 component_item_id = l_comp_item_id_table(i),
3866 assembly_item_number = l_head_name_table(i),
3867 assembly_item_id = l_head_item_id_table(i),
3868 organization_id = l_org_id_table(i)
3869 WHERE ((component_item_id = l_comp_item_id_table(i) AND organization_id = l_org_id_table(i)) OR (component_item_number = l_comp_name_table(i) AND organization_code = l_org_code_table(i)))
3870 AND (assembly_item_id = l_head_item_id_table(i) OR assembly_item_number = l_head_name_table(i))
3871 AND (process_flag = 1 OR process_flag = 5)
3872 --AND new_operation_seq_num = l_op_seq_table(i)
3873 --AND new_effectivity_date = l_effectivity_table(i)
3874 AND batch_id = p_batch_id;
3875 END IF;
3876
3877 UPDATE bom_ref_desgs_interface
3878 SET component_sequence_id = l_comp_seq_table(i),
3879 component_item_id = l_comp_item_id_table(i),
3880 organization_id = l_org_id_table(i),
3881 assembly_item_id = l_head_item_id_table(i)
3882 WHERE batch_id = p_batch_id
3883 AND ((component_item_number = l_comp_name_table(i) AND organization_code = l_org_code_table(i) ) OR (component_item_id = l_comp_item_id_table(i) AND organization_id = l_org_id_table(i)))
3884 AND (assembly_item_number = l_head_name_table(i) OR assembly_item_id = l_head_item_id_table(i) )
3885 AND nvl(operation_seq_num,1) = nvl(l_op_seq_table(i),1)
3886 AND nvl(effectivity_date,sysdate) = nvl(l_effectivity_table(i),sysdate)
3887 AND component_sequence_id IS NULL;
3888
3889 END LOOP; --Loop for comps ends here.
3890
3891 /*
3892 * For a PDH batch IF the user comes to the UI THEN
3893 * we will not have the bill_seq_id and comp_seq_id
3894 * So calling the Update_Bill_Info so that when the
3895 * user comes to the UI we'll have all the data
3896 * to show.
3897 */
3898
3899 Write_Debug('Calling the Update Bill Info');
3900
3901 IF pG_batch_options.STRUCTURE_CONTENT <> 'A' THEN
3902
3903
3904 BOM_IMPORT_PUB.UPDATE_BILL_INFO
3905 (
3906 p_batch_id => p_batch_id
3907 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3908 , x_Return_Status => l_Return_Status
3909 );
3910 END IF;
3911
3912 x_Return_Status := l_Return_Status;
3913 IF x_Return_Status <> 'S' THEN
3914 Error_Handler.Get_Message_List( x_message_list => l_message_list);
3915 x_Error_Mesg := l_message_list(1).Message_Text;
3916 END IF;
3917
3918 EXCEPTION
3919 WHEN G_EXC_SEV_QUIT_OBJECT THEN
3920
3921 Error_Handler.Add_Error_Token
3922 (
3923 p_message_name => NULL
3924 , p_Mesg_token_Tbl => l_Mesg_Token_Tbl
3925 , x_Mesg_token_Tbl => l_Mesg_Token_Tbl
3926 , p_token_Tbl => l_Token_Tbl
3927 , p_message_type => 'E'
3928 );
3929
3930 x_return_status := Error_Handler.G_STATUS_UNEXPECTED;
3931 Error_Handler.Get_Message_List( x_message_list => l_message_list);
3932 x_Error_Mesg := l_message_list(1).Message_Text;
3933
3934 END Update_Bill_Val_Id;
3935
3936
3937
3938
3939 PROCEDURE Update_Confirmed_Items
3940 (
3941 p_batch_id IN NUMBER
3942 , p_ssRef_varray IN VARCHAR2_VARRAY
3943 , x_Error_Message IN OUT NOCOPY VARCHAR2
3944 , x_Return_Status IN OUT NOCOPY VARCHAR2
3945 )
3946 IS
3947 TYPE var_type IS TABLE OF VARCHAR2(1000);
3948 l_head_ref_table var_type;
3949 l_count NUMBER;
3950 l_item_id NUMBER;
3951
3952 BEGIN
3953
3954 IF p_ssRef_varray IS NOT NULL
3955 THEN
3956 l_count := p_ssRef_varray.FIRST;
3957 IF l_count >= 1
3958 THEN
3959 FOR i IN 1..l_count
3960 LOOP
3961 SELECT inventory_item_id
3962 INTO l_item_id
3963 FROM mtl_system_items_interface
3964 WHERE set_process_id = p_batch_id
3965 AND source_system_reference = p_ssRef_varray(i);
3966
3967 --Update the header
3968
3969 UPDATE bom_bill_of_mtls_interface
3970 SET assembly_item_id = l_item_id
3971 WHERE batch_id = p_batch_id
3972 AND source_system_reference = p_ssRef_varray(i);
3973
3974 -- Update the comps
3975
3976 UPDATE bom_inventory_comps_interface BICI
3977 SET component_item_id = l_item_id
3978 WHERE batch_id = p_batch_id
3979 AND comp_source_system_reference = p_ssRef_varray(i);
3980
3981 END LOOP;--varray loop ends here
3982 END IF;
3983 END IF;
3984
3985 END Update_Confirmed_Items;
3986
3987 /*
3988 * Function will check the internal value is PRIMARY_UI
3989 * value from BOM_GLOBALS and return the display name for
3990 * primary structure Name
3991 */
3992 FUNCTION Get_Primary_StructureName
3993 (p_struct_Internal_Name IN VARCHAR2)
3994 RETURN VARCHAR2
3995 IS
3996 l_primary_internal VARCHAR2(50) := BOM_GLOBALS.GET_PRIMARY_UI;
3997 l_primary_display VARCHAR2(50) := bom_globals.RETRIEVE_MESSAGE('BOM','BOM_PRIMARY');
3998 BEGIN
3999 IF ((p_struct_Internal_Name IS NULL) OR (p_struct_Internal_Name = l_primary_internal)) THEN
4000 RETURN l_primary_display;
4001 ELSE
4002 RETURN p_struct_Internal_Name;
4003 END IF;
4004 END Get_Primary_StructureName;
4005
4006 PROCEDURE Check_Change_Options
4007 (
4008 p_batch_id IN NUMBER,
4009 x_error_code IN OUT NOCOPY VARCHAR2,
4010 x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
4011 )
4012 IS
4013 TYPE num_type IS TABLE OF NUMBER;
4014 l_bill_seq_table num_type;
4015 l_change_policy VARCHAR2(500);
4016 l_change_flag VARCHAR2(1);
4017 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
4018 l_Token_Tbl Error_Handler.Token_Tbl_Type;
4019
4020 CURSOR Get_Bill_Seq_Id
4021 IS
4022 SELECT bill_sequence_id
4023 FROM bom_bill_of_mtls_interface
4024 WHERE batch_id = p_batch_id;
4025
4026 CURSOR Get_Change_Option
4027 IS
4028 SELECT add_all_to_change_flag
4029 FROM ego_import_option_sets
4030 WHERE batch_id = p_batch_id;
4031
4032 BEGIN
4033
4034 OPEN Get_Change_Option;
4035 FETCH Get_Change_Option INTO l_change_flag;
4036 CLOSE Get_Change_Option;
4037
4038 OPEN Get_Bill_Seq_Id;
4039 FETCH Get_Bill_Seq_Id BULK COLLECT INTO l_bill_seq_table;
4040 CLOSE Get_Bill_Seq_Id;
4041
4042 FOR i IN 1..l_bill_seq_table.COUNT LOOP
4043
4044 IF l_change_flag = 'N' OR l_change_flag IS NULL THEN
4045 l_change_policy := Bom_Globals.Get_Change_Policy_Val(l_bill_seq_table(i),NULL);
4046 IF l_change_policy = 'NOT_ALLOWED' THEN
4047 -- Thorw error
4048 Error_Handler.Add_Error_Token
4049 (
4050 p_message_name => 'BOM_CHANGES_NOT_ALLOWED'
4051 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4052 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4053 , p_Token_Tbl => l_Token_Tbl
4054 );
4055 x_error_code := 'E';
4056 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
4057 ELSIF l_change_policy = 'CHANGE_ORDER_REQUIRED' THEN
4058 l_change_flag := 'Y';
4059 END IF;
4060 IF l_change_flag = 'Y' THEN
4061 --Update the Header rows with process flag = 5
4062 UPDATE Bom_Bill_Of_Mtls_Interface
4063 SET process_flag = 5
4064 WHERE batch_id = p_batch_id
4065 AND bill_sequence_id = l_bill_seq_table(i);
4066
4067 -- Update the direct Component rows with process Flag = 5
4068 UPDATE Bom_Inventory_Comps_Interface
4069 SET Process_Flag = 5
4070 WHERE batch_id = p_batch_id
4071 AND bill_sequence_id = l_bill_seq_table(i);
4072 END IF;
4073 END IF;
4074 END LOOP;
4075 END Check_Change_Options;
4076
4077 PROCEDURE PROCESS_ALL_COMPS_BATCH
4078 (
4079 p_batch_id IN NUMBER
4080 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
4081 , x_Return_Status IN OUT NOCOPY VARCHAR2
4082 )
4083 IS
4084 TYPE bom_comp_intf_type IS TABLE OF bom_inventory_comps_interface%ROWTYPE;
4085 TYPE bom_comp_type IS TABLE OF bom_components_b%ROWTYPE;
4086 TYPE num_type IS TABLE OF NUMBER;
4087 TYPE var_type IS TABLE OF VARCHAR2(1000);
4088 l_err_text VARCHAR2(1000);
4089 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
4090 l_comp_seq_id NUMBER;
4091 l_header_count NUMBER;
4092 l_comp_count NUMBER;
4093 l_comp_seq_count NUMBER;
4094 l_bill_seq_id NUMBER;
4095 l_effec_ctrl NUMBER;
4096 l_txn_table var_type;
4097 l_org_id NUMBER;
4098 l_header_rec_table var_type;
4099 l_str_name var_type;
4100 l_comp_table bom_comp_intf_type;
4101 l_comp_pdh_table bom_comp_type;
4102 l_unmatch_comp bom_comp_type;
4103 l_item_id_table num_type;
4104 l_org_id_table num_type;
4105 l_not_exist BOOLEAN;
4106 l_exist_table num_type;
4107 l_str_type_id NUMBER;
4108 l_debug BOOLEAN := FALSE;
4109 l_org_code_table var_type;
4110 l_unmatch_count NUMBER;
4111 l_bill_sequence_id NUMBER;
4112 l_comp_match_found BOOLEAN;
4113 l_par_eff_date DATE;
4114 l_req_id_table num_type;
4115 l_bundle_id_table num_type;
4116
4117 CURSOR Get_Header(l_batch_id IN NUMBER)
4118 IS
4119 SELECT
4120 BBMI.assembly_item_id,BBMI.organization_id,BBMI.alternate_bom_designator,BBMI.source_system_reference,UPPER(BBMI.transaction_type),request_id,bundle_id
4121 FROM bom_bill_of_mtls_interface BBMI
4122 WHERE batch_id = l_batch_id
4123 AND process_flag NOT IN (3,7,-1,0);
4124
4125 CURSOR Process_Header(l_batch_id IN NUMBER,l_item_id IN NUMBER,l_org_id IN NUMBER,l_name IN VARCHAR2)
4126 IS
4127 SELECT
4128 BSB.bill_sequence_id,BSB.effectivity_control,BSB.organization_id
4129 FROM
4130 bom_bill_of_mtls_interface BBMI,
4131 bom_Structures_b BSB
4132 WHERE
4133 BBMI.batch_id = l_batch_id
4134 AND BBMI.process_flag NOT IN (3,7,-1,0)
4135 AND BSB.assembly_item_id = l_item_id
4136 AND BSB.organization_id = l_org_id
4137 AND NVL(BSB.alternate_bom_designator,'Y') = NVL(l_name,'Y');
4138
4139 CURSOR Process_Comp(l_batch_id IN NUMBER,p_parent_reference IN VARCHAR2)
4140 IS
4141 SELECT *
4142 FROM bom_inventory_comps_interface BICI
4143 WHERE batch_id = l_batch_id
4144 AND process_flag NOT IN(3,7,0,-1)
4145 AND parent_source_system_reference = p_parent_reference;
4146
4147
4148 CURSOR Process_Unmatched_Comps(l_bill_seq_id IN NUMBER)
4149 IS
4150 SELECT *
4151 FROM Bom_Components_B BCB
4152 WHERE BCB.bill_sequence_id = l_bill_seq_id;
4153
4154 BEGIN
4155
4156 --logMessage_forsnell(' Inside New method' || 2933);
4157
4158 IF Error_Handler.get_debug <> 'Y' THEN
4159 l_debug := Init_Debug;
4160 ELSE
4161 l_debug := TRUE;
4162 END IF;
4163
4164 write_debug('In Process_All_Comp_Batch');
4165 write_debug('Calling update_bill_val');
4166 Update_Bill_Val_Id
4167 (
4168 p_batch_id => p_batch_id
4169 , x_return_status => x_Return_Status
4170 , x_Error_Mesg => l_err_text
4171 );
4172
4173 write_debug('After updating bill val--ret_status--' || x_Return_Status);
4174 write_debug('After updating bill val --err_text--' || l_err_text);
4175
4176 OPEN Get_Header(p_batch_id);
4177 FETCH Get_Header BULK COLLECT INTO l_item_id_table,l_org_id_table,l_str_name,l_header_rec_table,l_txn_table,l_req_id_table,l_bundle_id_table;
4178 CLOSE Get_Header;
4179
4180 l_header_count := l_header_rec_table.COUNT;
4181
4182 FOR i IN 1..l_header_count
4183 LOOP --Header Loop
4184
4185 write_debug('Updating the Bill for Header '|| l_header_rec_table(i));
4186 l_bill_seq_id := NULL;
4187
4188 BEGIN
4189 IF l_org_id_table(i) IS NULL THEN
4190 SELECT organization_id
4191 INTO l_org_id_table(i)
4192 FROM mtl_parameters
4193 WHERE organization_code = l_org_code_table(i);
4194 END IF;
4195
4196
4197 IF l_item_id_table(i) IS NULL THEN
4198 SELECT inventory_item_id
4199 INTO l_item_id_table(i)
4200 FROM mtl_system_items_vl
4201 WHERE concatenated_segments = l_header_rec_table(i)
4202 AND organization_id = l_org_id_table(i);
4203 END IF;
4204
4205 EXCEPTION WHEN NO_DATA_FOUND THEN
4206 NULL; --new item creation
4207 END;
4208
4209 IF l_item_id_table(i) IS NOT NULL AND l_org_id_table(i) IS NOT NULL
4210 THEN
4211 OPEN Process_Header(p_batch_id,l_item_id_table(i),l_org_id_table(i),l_str_name(i));
4212 FETCH Process_Header INTO l_bill_seq_id,l_effec_ctrl,l_org_id;
4213 CLOSE Process_Header;
4214 END IF;
4215
4216 write_debug('pG_batch_options.PDH_BATCH--' || pG_batch_options.PDH_BATCH);
4217 write_debug('pG_batch_options.STRUCTURE_CONTENT--' || pG_batch_options.STRUCTURE_CONTENT);
4218 write_debug('bill sequence_id --' || l_bill_seq_id);
4219 write_debug('header rec --' || l_header_rec_table(i) );
4220
4221 OPEN Process_Comp(p_batch_id,l_header_rec_table(i));
4222 FETCH Process_Comp BULK COLLECT INTO l_comp_table;
4223 CLOSE Process_Comp;
4224
4225 l_comp_count := l_comp_table.COUNT;
4226
4227 write_debug('intf comp count--' || l_comp_count);
4228
4229 FOR j IN 1..l_comp_count
4230 LOOP
4231 l_comp_table(j).bill_sequence_id := l_bill_seq_id;
4232 IF l_comp_table(j).component_sequence_id IS NULL
4233 THEN
4234
4235 BEGIN
4236 IF l_comp_table(j).component_item_id IS NULL THEN
4237 SELECT inventory_item_id
4238 INTO l_comp_table(j).component_item_id
4239 FROM mtl_system_items_vl
4240 WHERE concatenated_segments = l_comp_table(j).comp_source_system_reference
4241 AND organization_id = l_org_id_table(i);
4242 END IF;
4243 EXCEPTION WHEN NO_DATA_FOUND THEN
4244 NULL; -- new item creation
4245 END;
4246
4247 l_comp_seq_id := CHECK_COMP_EXIST(l_bill_seq_id,
4248 l_effec_ctrl,
4249 p_batch_id,
4250 l_comp_table(j).comp_source_system_reference,
4251 l_comp_table(j).component_item_id,
4252 l_org_id_table(i),
4253 l_item_id_table(i)
4254 );
4255 write_debug('comp seq id after check_comp' || l_comp_seq_id);
4256 IF(l_comp_seq_id <> 0) THEN
4257 l_comp_table(j).component_sequence_id := l_comp_seq_id;
4258 END IF;
4259 END IF;
4260 END LOOP;
4261
4262 OPEN Process_Unmatched_Comps(l_bill_seq_id);
4263 FETCH Process_Unmatched_Comps BULK COLLECT INTO l_comp_pdh_table;
4264 CLOSE Process_Unmatched_Comps;
4265
4266 l_comp_seq_count := l_comp_pdh_table.COUNT;
4267
4268 write_debug('pdh cmp count--' || l_comp_seq_count );
4269 IF(l_comp_count >= l_comp_seq_count)
4270 THEN
4271
4272 FOR j IN 1..l_comp_count
4273 LOOP
4274 FOR k IN 1..l_comp_seq_count
4275 LOOP
4276 l_bill_sequence_id := Check_Header_Exists(l_comp_pdh_table(k).component_item_id,l_org_id_table(i),l_str_name(i));
4277 IF l_bill_sequence_id IS NOT NULL THEN
4278 IF NOT Header_Not_In_Intf(l_bill_sequence_id,l_comp_pdh_table(k).component_item_id,l_org_id_table(i),l_str_name(i),p_batch_id,l_req_id_table(i),l_bundle_id_table(i)) THEN
4279 OPEN Process_Unmatched_Comps(l_bill_sequence_id);
4280 FETCH Process_Unmatched_Comps BULK COLLECT INTO l_unmatch_comp;
4281 CLOSE Process_Unmatched_Comps;
4282
4283 l_unmatch_count := l_unmatch_comp.COUNT;
4284
4285 FOR m in 1..l_unmatch_count LOOP
4286 IF l_unmatch_comp(m).disable_date IS NULL OR (l_unmatch_comp(m).disable_date IS NOT NULL AND l_unmatch_comp(m).disable_date > sysdate) THEN
4287 INSERT INTO
4288 bom_inventory_comps_interface
4289 (
4290 component_item_id,
4291 organization_id,
4292 component_sequence_id,
4293 bill_sequence_id,
4294 parent_source_system_reference,
4295 batch_id,
4296 transaction_type,
4297 disable_date,
4298 process_flag,
4299 component_item_number,
4300 assembly_item_number,
4301 organization_code,
4302 alternate_bom_designator,
4303 assembly_item_id,
4304 transaction_id
4305 )
4306 VALUES
4307 (
4308 l_unmatch_comp(m).component_item_id,
4309 l_org_id_table(i),
4310 l_unmatch_comp(m).component_sequence_id,
4311 l_unmatch_comp(m).bill_sequence_id,
4312 (SELECT concatenated_segments FROM mtl_system_items_vl MSIVL
4313 WHERE MSIVL.inventory_item_id = l_comp_pdh_table(k).component_item_id
4314 AND organization_id = l_org_id_table(i)),
4315 p_batch_id,
4316 'DELETE',
4317 SYSDATE,
4318 1,
4319 (SELECT concatenated_segments FROM mtl_system_items_vl WHERE inventory_item_id = l_unmatch_comp(m).component_item_id AND organization_id = l_org_id_table(i)),
4320 (SELECT concatenated_segments FROM mtl_system_items_vl WHERE inventory_item_id = l_comp_pdh_table(k).component_item_id AND organization_id = l_org_id_table(i)),
4321 (SELECT organization_code FROM mtl_parameters WHERE organization_id = l_org_id_table(i)),
4322 l_str_name(i),
4323 l_comp_pdh_table(k).component_item_id,
4324 MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
4325 );
4326
4327 Disable_Refds
4328 (
4329 p_batch_id => p_batch_id,
4330 p_comp_seq_id => l_unmatch_comp(m).component_sequence_id,
4331 p_comp_id => l_unmatch_comp(m).component_item_id,
4332 p_parent_id => l_comp_pdh_table(k).component_item_id,
4333 p_eff_date => l_unmatch_comp(m).effectivity_date,
4334 p_op_seq_num => l_unmatch_comp(m).operation_seq_num,
4335 p_org_id => l_org_id_table(i)
4336 );
4337
4338 END IF; -- Disable Date Null
4339 END LOOP;
4340 END IF; -- Header Not In Intf
4341 END IF; -- Bill Seq Null
4342 IF (l_comp_table(j).component_item_id = l_comp_pdh_table(k).component_item_id
4343 AND l_comp_table(j).organization_id = l_org_id_table(i)
4344 AND l_comp_table(j).assembly_item_id = l_item_id_table(i))
4345 THEN
4346 l_comp_match_found := false;
4347
4348 IF l_comp_table(j).parent_revision_code IS NOT NULL THEN
4349 SELECT effectivity_date
4350 INTO l_par_eff_date
4351 from mtl_item_revisions
4352 WHERE inventory_item_id = l_comp_table(j).assembly_item_id
4353 AND organization_id = l_comp_table(j).organization_id
4354 AND revision = l_comp_table(j).parent_revision_code;
4355 END IF;
4356 /*
4357 If no effectivity info is given then match the component based on either parent rev or current eff date
4358 */
4359 IF pG_batch_options.STRUCTURE_EFFECTIVITY_TYPE = 1 THEN
4360 IF l_comp_table(j).effectivity_date IS NULL AND l_comp_table(j).new_effectivity_date IS NULL THEN -- when no eff info
4361 IF l_comp_table(j).parent_revision_code IS NOT NULL AND l_par_eff_date > sysdate THEN -- check for parent rev eff if provided
4362 IF (l_par_eff_date >= l_comp_pdh_table(k).effectivity_date AND (l_comp_pdh_table(k).disable_date IS NULL OR l_comp_pdh_table(k).disable_date >= l_par_eff_date ) ) THEN
4363 l_comp_match_found := true;
4364 END IF;
4365 ELSE -- if parent rev not provided then chek for current eff comp
4366 IF (l_comp_pdh_table(k).effectivity_date <= SYSDATE AND (l_comp_pdh_table(k).disable_date IS NULL OR l_comp_pdh_table(k).disable_date >= SYSDATE) ) THEN
4367 l_comp_match_found := true;
4368 END IF;
4369 END IF;
4370 ELSE --if eff info if given then use that
4371 IF (l_comp_pdh_table(k).effectivity_date = nvl(l_comp_table(j).new_effectivity_date,l_comp_table(j).effectivity_date)
4372 AND (l_comp_pdh_table(k).disable_date is NULL OR l_comp_pdh_table(k).disable_date > l_comp_pdh_table(k).effectivity_date) )
4373 THEN
4374 l_comp_match_found := true;
4375 END IF;
4376 END IF; -- eff info is null
4377 ELSIF (pG_batch_options.STRUCTURE_EFFECTIVITY_TYPE = 2 AND NVL(l_comp_pdh_table(k).to_end_item_unit_number,99999) >= NVL(l_comp_table(j).from_end_item_unit_number,99999)) THEN
4378 l_comp_match_found := true;
4379 ELSIF (pG_batch_options.STRUCTURE_EFFECTIVITY_TYPE = 4 AND NVL(l_comp_pdh_table(k).to_end_item_rev_id,99999) >= NVL(l_comp_table(j).from_end_item_rev_id,99999)) THEN
4380 l_comp_match_found := true;
4381 END IF; -- eff type is 1
4382
4383
4384 IF l_comp_match_found THEN
4385 l_comp_table(j).transaction_type := 'UPDATE';
4386 l_comp_pdh_table(k).bill_sequence_id := 0;
4387 IF nvl(l_comp_table(j).component_sequence_id,-1) <> l_comp_pdh_table(k).component_sequence_id THEN
4388 l_comp_table(j).component_sequence_id := l_comp_pdh_table(k).component_sequence_id;
4389 END IF;
4390 END IF;
4391
4392
4393 /*IF( ( pG_batch_options.STRUCTURE_EFFECTIVITY_TYPE = 1
4394 AND (l_comp_table(j).parent_revision_code IS NOT NULL AND l_comp_pdh_table(k).effectivity_date > l_par_eff_date AND
4395 NVL(l_comp_pdh_table(k).disable_date,NVL(l_comp_table(j).effectivity_date,NVL(l_comp_table(j).new_effectivity_date,sysdate))) >= NVL(l_comp_table(j).effectivity_date,NVL(l_comp_table(j).new_effectivity_date,sysdate))
4396 )
4397 OR
4398 ( pG_batch_options.STRUCTURE_EFFECTIVITY_TYPE = 2
4399 AND NVL(l_comp_pdh_table(k).to_end_item_unit_number,99999) >= NVL(l_comp_table(j).from_end_item_unit_number,99999)
4400 )
4401 OR
4402 ( pG_batch_options.STRUCTURE_EFFECTIVITY_TYPE = 4
4403 AND NVL(l_comp_pdh_table(k).to_end_item_rev_id,99999) >= NVL(l_comp_table(j).from_end_item_rev_id,99999)
4404 )
4405 )
4406 THEN
4407 l_comp_table(j).transaction_type := 'UPDATE';
4408 IF nvl(l_comp_table(j).component_sequence_id,-1) <> l_comp_pdh_table(k).component_sequence_id THEN
4409 l_comp_table(j).component_sequence_id := l_comp_pdh_table(k).component_sequence_id;
4410 END IF;
4411 END IF;*/
4412 ELSE
4413 IF l_comp_pdh_table(k).disable_date is NOT NULL AND l_comp_pdh_table(k).disable_date < sysdate THEN
4414 l_comp_pdh_table(k).bill_sequence_id := 0;
4415 END IF;
4416 END IF;
4417 END LOOP;
4418 END LOOP;
4419 write_debug('before inserting the delete rows first');
4420 FOR k IN 1..l_comp_seq_count
4421 LOOP
4422 IF l_comp_pdh_table(k).bill_sequence_id <> 0 THEN
4423 write_debug('inserting delete for comp --' ||l_comp_pdh_table(k).component_item_id );
4424 INSERT INTO
4425 bom_inventory_comps_interface
4426 (
4427 component_item_id,
4428 organization_id,
4429 component_sequence_id,
4430 bill_sequence_id,
4431 parent_source_system_reference,
4432 batch_id,
4433 transaction_type,
4434 disable_date,
4435 process_flag,
4436 component_item_number,
4437 assembly_item_number,
4438 organization_code,
4439 alternate_bom_designator,
4440 transaction_id
4441 )
4442 VALUES
4443 (
4444 l_comp_pdh_table(k).component_item_id,
4445 l_org_id_table(i),
4446 l_comp_pdh_table(k).component_sequence_id,
4447 l_bill_seq_id,
4448 l_header_rec_table(i),
4449 p_batch_id,
4450 'DELETE',
4451 SYSDATE,
4452 1,
4453 (SELECT concatenated_segments FROM mtl_system_items_vl WHERE inventory_item_id =l_comp_pdh_table(k).component_item_id AND organization_id = l_org_id_table(i)),
4454 (SELECT concatenated_segments FROM mtl_system_items_vl WHERE inventory_item_id = l_item_id_table(i) AND organization_id = l_org_id_table(i)),
4455 (SELECT organization_code FROM mtl_parameters WHERE organization_id = l_org_id_table(i)),
4456 l_str_name(i),
4457 MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
4458 );
4459 END IF;
4460 Disable_Refds
4461 (
4462 p_batch_id => p_batch_id,
4463 p_comp_seq_id => l_comp_pdh_table(k).component_sequence_id ,
4464 p_comp_id => l_comp_pdh_table(k).component_item_id,
4465 p_parent_id => l_item_id_table(i),
4466 p_eff_date => l_comp_pdh_table(k).effectivity_date,
4467 p_op_seq_num => l_comp_pdh_table(k).operation_seq_num,
4468 p_org_id => l_org_id_table(i)
4469 );
4470 END LOOP;
4471 ELSE
4472 FOR j IN 1..l_comp_seq_count
4473 LOOP
4474 l_not_exist := TRUE;
4475 l_bill_sequence_id := Check_Header_Exists(l_comp_pdh_table(j).component_item_id,l_org_id_table(i),l_str_name(i));
4476 IF l_bill_sequence_id IS NOT NULL THEN
4477 IF NOT Header_Not_In_Intf(l_bill_sequence_id,l_comp_pdh_table(j).component_item_id,l_org_id_table(i),l_str_name(i),p_batch_id,l_req_id_table(i),l_bundle_id_table(i)) THEN
4478 OPEN Process_Unmatched_Comps(l_bill_sequence_id);
4479 FETCH Process_Unmatched_Comps BULK COLLECT INTO l_unmatch_comp;
4480 CLOSE Process_Unmatched_Comps;
4481
4482 l_unmatch_count := l_unmatch_comp.COUNT;
4483
4484 FOR m in 1..l_unmatch_count LOOP
4485 IF l_unmatch_comp(m).disable_date IS NULL OR (l_unmatch_comp(m).disable_date IS NOT NULL AND l_unmatch_comp(m).disable_date > sysdate) THEN
4486 INSERT INTO
4487 bom_inventory_comps_interface
4488 (
4489 component_item_id,
4490 organization_id,
4491 component_sequence_id,
4492 bill_sequence_id,
4493 parent_source_system_reference,
4494 batch_id,
4495 transaction_type,
4496 disable_date,
4497 process_flag,
4498 component_item_number,
4499 assembly_item_number,
4500 organization_code,
4501 alternate_bom_designator,
4502 assembly_item_id,
4503 transaction_id
4504 )
4505 VALUES
4506 (
4507 l_unmatch_comp(m).component_item_id,
4508 l_org_id_table(i),
4509 l_unmatch_comp(m).component_sequence_id,
4510 l_unmatch_comp(m).bill_sequence_id,
4511 (SELECT concatenated_segments FROM mtl_system_items_vl MSIVL
4512 WHERE MSIVL.inventory_item_id = l_comp_pdh_table(j).component_item_id
4513 AND organization_id = l_org_id_table(i)),
4514 p_batch_id,
4515 'DELETE',
4516 SYSDATE,
4517 1,
4518 (SELECT concatenated_segments FROM mtl_system_items_vl WHERE inventory_item_id = l_unmatch_comp(m).component_item_id AND organization_id = l_org_id_table(i)),
4519 (SELECT concatenated_segments FROM mtl_system_items_vl WHERE inventory_item_id = l_comp_pdh_table(j).component_item_id AND organization_id = l_org_id_table(i)),
4520 (SELECT organization_code FROM mtl_parameters WHERE organization_id = l_org_id_table(i)),
4521 l_str_name(i),
4522 l_comp_pdh_table(j).component_item_id ,
4523 MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
4524 );
4525
4526 Disable_Refds
4527 (
4528 p_batch_id => p_batch_id,
4529 p_comp_seq_id => l_unmatch_comp(m).component_sequence_id,
4530 p_comp_id => l_unmatch_comp(m).component_item_id,
4531 p_parent_id => l_comp_pdh_table(j).component_item_id,
4532 p_eff_date => l_unmatch_comp(m).effectivity_date,
4533 p_op_seq_num => l_unmatch_comp(m).operation_seq_num,
4534 p_org_id => l_org_id_table(i)
4535 );
4536 END IF; -- Disable Date Null
4537 END LOOP;
4538 END IF; -- Header Not In Intf
4539 END IF; -- Bill Seq Null
4540 FOR k IN 1..l_comp_count
4541 LOOP
4542 IF (l_comp_table(k).component_item_id = l_comp_pdh_table(j).component_item_id
4543 AND l_comp_table(k).organization_id = l_org_id_table(i)
4544 AND l_comp_table(k).assembly_item_id = l_item_id_table(i) ) THEN
4545
4546 l_comp_match_found := false;
4547 IF l_comp_table(k).parent_revision_code IS NOT NULL THEN
4548 SELECT effectivity_date
4549 INTO l_par_eff_date
4550 from mtl_item_revisions
4551 WHERE inventory_item_id = l_comp_table(k).assembly_item_id
4552 AND organization_id = l_comp_table(k).organization_id
4553 AND revision = l_comp_table(k).parent_revision_code;
4554 END IF;
4555 /*
4556 If no effectivity info is given then match the component based on either parent rev or current eff date
4557 */
4558 IF pG_batch_options.STRUCTURE_EFFECTIVITY_TYPE = 1 THEN
4559 IF l_comp_table(k).effectivity_date IS NULL AND l_comp_table(k).new_effectivity_date IS NULL THEN -- when no eff info
4560 IF l_comp_table(k).parent_revision_code IS NOT NULL AND l_par_eff_date > sysdate THEN -- check for parent rev eff if provided
4561 IF (l_par_eff_date >= l_comp_pdh_table(j).effectivity_date AND (l_comp_pdh_table(j).disable_date IS NULL OR l_comp_pdh_table(j).disable_date >= l_par_eff_date ) ) THEN
4562 l_comp_match_found := true;
4563 l_not_exist := FALSE;
4564 END IF;
4565 ELSE -- if parent rev not provided then chek for current eff comp
4566 IF (l_comp_pdh_table(j).effectivity_date <= SYSDATE AND (l_comp_pdh_table(j).disable_date IS NULL OR l_comp_pdh_table(j).disable_date >= SYSDATE ))THEN
4567 l_comp_match_found := true;
4568 l_not_exist := FALSE;
4569 END IF;
4570 END IF;
4571 ELSE --if eff info if given then use that
4572 IF (l_comp_pdh_table(j).effectivity_date = nvl(l_comp_table(k).new_effectivity_date,l_comp_table(k).effectivity_date)
4573 AND (l_comp_pdh_table(j).disable_date is NULL OR l_comp_pdh_table(j).disable_date > l_comp_pdh_table(j).effectivity_date) )
4574 THEN
4575 l_comp_match_found := true;
4576 l_not_exist := FALSE;
4577 END IF;
4578 END IF; -- eff info is null
4579 ELSIF (pG_batch_options.STRUCTURE_EFFECTIVITY_TYPE = 2 AND NVL(l_comp_pdh_table(j).to_end_item_unit_number,99999) >= NVL(l_comp_table(k).from_end_item_unit_number,99999)) THEN
4580 l_comp_match_found := true;
4581 l_not_exist := FALSE;
4582 ELSIF (pG_batch_options.STRUCTURE_EFFECTIVITY_TYPE = 4 AND NVL(l_comp_pdh_table(j).to_end_item_rev_id,99999) >= NVL(l_comp_table(k).from_end_item_rev_id,99999)) THEN
4583 l_comp_match_found := true;
4584 l_not_exist := FALSE;
4585 END IF; -- eff type is 1
4586
4587
4588 IF l_comp_match_found THEN
4589 l_comp_table(k).transaction_type := 'UPDATE';
4590 IF nvl(l_comp_table(k).component_sequence_id,-1) <> l_comp_pdh_table(j).component_sequence_id THEN
4591 l_comp_table(k).component_sequence_id := l_comp_pdh_table(j).component_sequence_id;
4592 END IF;
4593 END IF;
4594
4595 /*IF( (pG_batch_options.STRUCTURE_EFFECTIVITY_TYPE = 1
4596 AND NVL(l_comp_pdh_table(j).disable_date,NVL(l_comp_table(k).effectivity_date,NVL(l_comp_table(k).new_effectivity_date,sysdate))) >= NVL(l_comp_table(k).effectivity_date,NVL(l_comp_table(k).new_effectivity_date,sysdate)))
4597 OR (pG_batch_options.STRUCTURE_EFFECTIVITY_TYPE = 2 AND NVL(l_comp_pdh_table(j).to_end_item_unit_number,99999) >= NVL(l_comp_table(k).from_end_item_unit_number,99999))
4598 OR (pG_batch_options.STRUCTURE_EFFECTIVITY_TYPE = 4 AND NVL(l_comp_pdh_table(j).to_end_item_rev_id,99999) >= NVL(l_comp_table(k).from_end_item_rev_id,99999))
4599 )
4600 THEN
4601 l_comp_table(k).transaction_type := 'UPDATE';
4602 IF nvl(l_comp_table(k).component_sequence_id,-1) <> l_comp_pdh_table(j).component_sequence_id THEN
4603 l_comp_table(k).component_sequence_id := l_comp_pdh_table(j).component_sequence_id;
4604 END IF;
4605 END IF;*/
4606 END IF;
4607 END LOOP;
4608 write_debug('before inserting the delete rows second');
4609 IF l_not_exist AND (l_comp_pdh_table(j).disable_date IS NULL OR (l_comp_pdh_table(j).disable_date IS NOT NULL AND l_comp_pdh_table(j).disable_date > sysdate)) THEN
4610 write_debug('inserting delete for comp --' ||l_comp_pdh_table(j).component_item_id );
4611 INSERT INTO
4612 bom_inventory_comps_interface
4613 (
4614 component_item_id,
4615 organization_id,
4616 component_sequence_id,
4617 bill_sequence_id,
4618 parent_source_system_reference,
4619 batch_id,
4620 transaction_type,
4621 disable_date,
4622 process_flag,
4623 component_item_number,
4624 assembly_item_number,
4625 organization_code,
4626 alternate_bom_designator,
4627 transaction_id
4628 )
4629 VALUES
4630 (
4631 l_comp_pdh_table(j).component_item_id,
4632 l_org_id_table(i),
4633 l_comp_pdh_table(j).component_sequence_id,
4634 l_bill_seq_id,
4635 l_header_rec_table(i),
4636 p_batch_id,
4637 'DELETE',
4638 SYSDATE,
4639 1,
4640 (SELECT concatenated_segments FROM mtl_system_items_vl WHERE inventory_item_id =l_comp_pdh_table(j).component_item_id AND organization_id = l_org_id_table(i)),
4641 (SELECT concatenated_segments FROM mtl_system_items_vl WHERE inventory_item_id = l_item_id_table(i) AND organization_id = l_org_id_table(i)),
4642 (SELECT organization_code FROM mtl_parameters WHERE organization_id = l_org_id_table(i)),
4643 l_str_name(i),
4644 MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
4645 );
4646 END IF;
4647 Disable_Refds
4648 (
4649 p_batch_id => p_batch_id,
4650 p_comp_seq_id => l_comp_pdh_table(j).component_sequence_id,
4651 p_comp_id => l_comp_pdh_table(j).component_item_id,
4652 p_parent_id => l_item_id_table(i),
4653 p_eff_date => l_comp_pdh_table(j).effectivity_date,
4654 p_op_seq_num => l_comp_pdh_table(j).operation_seq_num,
4655 p_org_id => l_org_id_table(i)
4656 );
4657 END LOOP;
4658 END IF;
4659
4660 l_comp_count := l_comp_table.COUNT;
4661 FOR i in 1..l_comp_count LOOP
4662 UPDATE bom_inventory_comps_interface
4663 SET component_sequence_id = l_comp_table(i).component_sequence_id,
4664 transaction_type = UPPER(l_comp_table(i).transaction_type)
4665 WHERE batch_id = p_batch_id
4666 AND (process_flag = 1 OR process_flag = 5)
4667 AND UPPER(transaction_type) <> 'DELETE'
4668 AND ( interface_table_unique_id = l_comp_table(i).interface_table_unique_id
4669 OR component_sequence_id = l_comp_table(i).component_sequence_id
4670 OR ( (component_item_id = l_comp_table(i).component_item_id OR component_item_number = l_comp_table(i).component_item_number)
4671 AND (organization_id = l_comp_table(i).organization_id OR organization_code = l_comp_table(i).organization_code)
4672 AND (assembly_item_id = l_comp_table(i).assembly_item_id OR assembly_item_number = l_comp_table(i).assembly_item_number)
4673 )
4674 );
4675 END LOOP;
4676
4677 END LOOP; --End Header Loop
4678 x_Return_Status := 'S';
4679 EXCEPTION
4680 WHEN OTHERS THEN
4681 Write_Debug('Unexpected Error occured' || SQLERRM);
4682 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4683 THEN
4684 l_err_text := SUBSTR(SQLERRM, 1, 200);
4685 Error_Handler.Add_Error_Token
4686 (
4687 p_Message_Name => NULL
4688 , p_Message_Text => l_err_text
4689 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4690 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
4691 );
4692 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
4693 END IF;
4694 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
4695 END PROCESS_ALL_COMPS_BATCH;
4696
4697 /**
4698 * This function is a public api that should be called by Ebusiness Suite Open
4699 * Interface Structure and Routings Import users for grouping of rows
4700 * Any other team using interface tables will use this method to get the batchId
4701 * sequence
4702 */
4703 FUNCTION Get_BatchId RETURN NUMBER
4704 IS
4705 L_NEXT_VALUE NUMBER;
4706 BEGIN
4707 SELECT
4708 MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
4709 INTO
4710 L_NEXT_VALUE
4711 FROM DUAL;
4712 RETURN L_NEXT_VALUE;
4713 EXCEPTION
4714 WHEN OTHERS THEN
4715 RETURN 0;
4716 END Get_BatchId;
4717
4718 /*
4719 * This API will delete all the records from all the
4720 * BOM interface tables for the given batch id.
4721 */
4722
4723 PROCEDURE Delete_Interface_Records
4724 (
4725 p_batch_id IN NUMBER
4726 , x_Error_Mesg IN OUT NOCOPY VARCHAR2
4727 , x_Ret_Code IN OUT NOCOPY VARCHAR2
4728 )
4729 IS
4730 l_debug BOOLEAN := FALSE;
4731 stmt_num NUMBER;
4732 BEGIN
4733
4734 stmt_num := 0;
4735 IF Error_Handler.get_debug <> 'Y' THEN
4736 l_debug := Init_Debug;
4737 ELSE
4738 l_debug := TRUE;
4739 END IF;
4740
4741 Write_Debug('Inside Delete Interface Records');
4742
4743 stmt_num := 1;
4744 Write_Debug('Deleting the header rows');
4745
4746 DELETE bom_bill_of_mtls_interface
4747 WHERE batch_id = p_batch_id;
4748
4749 stmt_num := 2;
4750 Write_Debug('Deleting the component rows');
4751
4752 Delete bom_inventory_comps_interface
4753 WHERE batch_id = p_batch_id;
4754
4755 stmt_num := 3;
4756 Write_Debug('Deleting the Ref Desgs interface');
4757
4758 DELETE bom_ref_desgs_interface
4759 WHERE batch_id = p_batch_id;
4760
4761 stmt_num := 4;
4762 Write_Debug('Deleting the Sub Comps Rows');
4763
4764 DELETE bom_sub_comps_interface
4765 WHERE batch_id = p_batch_id;
4766
4767 stmt_num := 5;
4768 Write_Debug('Deleting the component attr rows');
4769
4770 DELETE bom_cmp_usr_attr_interface
4771 WHERE (batch_id = p_batch_id or data_set_id = p_batch_id);
4772
4773 stmt_num := 6;
4774 Write_Debug('Deleting the Comp Operation Rows');
4775
4776 DELETE bom_component_ops_interface
4777 WHERE batch_id = p_batch_id;
4778
4779 stmt_num := 7;
4780 Write_Debug('Deleting the Network Operation Rows');
4781
4782 DELETE bom_op_networks_interface
4783 WHERE batch_id = p_batch_id;
4784
4785 stmt_num := 8;
4786 Write_Debug('Deleting the Operation Resources Rows');
4787
4788 DELETE bom_op_resources_interface
4789 WHERE batch_id = p_batch_id;
4790
4791 stmt_num := 9;
4792 Write_Debug('Deleting the Operation Routings Rows');
4793
4794 DELETE bom_op_routings_interface
4795 WHERE batch_id = p_batch_id;
4796
4797 stmt_num := 10;
4798 Write_Debug('Deleting the Operation Sequences Rows');
4799
4800 DELETE bom_op_sequences_interface
4801 WHERE batch_id = p_batch_id;
4802
4803 stmt_num := 11;
4804 Write_Debug('Deleting the Sub Operation Resources Rows');
4805
4806 DELETE bom_sub_op_resources_interface
4807 WHERE batch_id = p_batch_id;
4808
4809 x_Ret_Code := FND_API.G_RET_STS_SUCCESS;
4810
4811 EXCEPTION
4812 WHEN OTHERS THEN
4813 x_Error_Mesg := 'Delete Intf Rec (' || stmt_num || ') ' || SQLERRM;
4814 x_Ret_Code := FND_API.G_RET_STS_UNEXP_ERROR;
4815
4816 End Delete_Interface_Records;
4817
4818 /*
4819 * Procedure to merge the duplicate records
4820 */
4821 --Duplicate Records
4822 PROCEDURE Merge_Duplicate_Rows
4823 (
4824 p_batch_id IN NUMBER,
4825 x_Ret_Status IN OUT NOCOPY VARCHAR2,
4826 x_Error_Mesg IN OUT NOCOPY VARCHAR2
4827 )
4828 IS
4829 TYPE bom_comp_intf_type IS TABLE OF bom_inventory_comps_interface%ROWTYPE;
4830 l_comp_table bom_comp_intf_type;
4831 l_merge_comp bom_comp_intf_type;
4832 l_count NUMBER;
4833 l_merge_count NUMBER;
4834 l_temp_count NUMBER;
4835
4836
4837 CURSOR Get_Same_Comps
4838 (
4839 l_comp_seq IN NUMBER,
4840 l_comp_name IN VARCHAR2,
4841 l_par_name IN VARCHAR2,
4842 l_eff_date IN DATE,
4843 l_new_eff IN DATE,
4844 l_op_seq IN NUMBER,
4845 l_new_op_seq IN NUMBER,
4846 l_unit_num IN VARCHAR2,
4847 l_item_rev IN NUMBER,
4848 l_comp_ref IN VARCHAR2,
4849 l_par_ref IN VARCHAR2,
4850 l_txn_type IN VARCHAR2
4851 )
4852 IS
4853 SELECT *
4854 FROM bom_inventory_comps_interface
4855 WHERE batch_id = p_batch_id
4856 AND ( component_sequence_id = l_comp_seq
4857 OR (component_sequence_id is NULL
4858 AND ( (component_item_number = l_comp_name OR comp_source_system_reference = l_comp_ref)
4859 AND (assembly_item_number = l_par_name OR parent_source_system_reference = l_par_ref)
4860 AND (operation_seq_num = l_op_seq OR new_operation_seq_num = l_new_op_seq)
4861 AND (( effectivity_date = l_eff_date OR new_effectivity_date = l_new_eff)
4862 OR from_end_item_unit_number = l_unit_num
4863 OR from_end_item_rev_id = l_item_rev
4864 )
4865 )
4866 )
4867 )
4868 AND UPPER(transaction_type) = l_txn_type
4869 AND process_flag = 1
4870 ORDER by interface_table_unique_id DESC;
4871
4872 CURSOR Get_Comp
4873 IS
4874 SELECT *
4875 FROM bom_inventory_comps_interface
4876 WHERE batch_id = p_batch_id
4877 AND process_flag = 1;
4878
4879 BEGIN
4880
4881 Open Get_Comp;
4882 FETCH Get_Comp BULK COLLECT INTO l_comp_table;
4883 CLOSE Get_Comp;
4884
4885 l_count := l_comp_table.COUNT;
4886
4887 FOR i in 1..l_count
4888 LOOP
4889 IF l_comp_table(i).process_flag = 1 THEN
4890 OPEN Get_Same_Comps
4891 (
4892 l_comp_table(i).component_sequence_id,
4893 l_comp_table(i).component_item_number,
4894 l_comp_table(i).assembly_item_number,
4895 l_comp_table(i).effectivity_date,
4896 l_comp_table(i).new_effectivity_date,
4897 l_comp_table(i).operation_seq_num,
4898 l_comp_table(i).new_operation_seq_num,
4899 l_comp_table(i).from_end_item_unit_number,
4900 l_comp_table(i).from_end_item_rev_id,
4901 l_comp_table(i).comp_source_system_reference,
4902 l_comp_table(i).parent_source_system_reference,
4903 l_comp_table(i).transaction_type
4904 );
4905 FETCH Get_Same_Comps BULK COLLECT INTO l_merge_comp;
4906 CLOSE Get_Same_Comps;
4907
4908 l_merge_count := l_merge_comp.COUNT;
4909 IF l_merge_count > 1 THEN
4910
4911 FOR j in 2..l_merge_count
4912 LOOP
4913 IF l_merge_comp(1).operation_seq_num IS NULL THEN
4914 l_merge_comp(1).operation_seq_num := l_merge_comp(j).operation_seq_num;
4915 END IF;
4916 IF l_merge_comp(1).new_operation_seq_num IS NULL THEN
4917 l_merge_comp(1).new_operation_seq_num := l_merge_comp(j).new_operation_seq_num;
4918 END IF;
4919 IF l_merge_comp(1).basis_type IS NULL THEN
4920 l_merge_comp(1).basis_type := l_merge_comp(j).basis_type;
4921 END IF;
4922 IF l_merge_comp(1).component_quantity IS NULL THEN
4923 l_merge_comp(1).component_quantity := l_merge_comp(j).component_quantity;
4924 END IF;
4925 IF l_merge_comp(1).inverse_quantity IS NULL THEN
4926 l_merge_comp(1).inverse_quantity := l_merge_comp(j).inverse_quantity;
4927 END IF;
4928 IF l_merge_comp(1).component_yield_factor IS NULL THEN
4929 l_merge_comp(1).component_yield_factor := l_merge_comp(j).component_yield_factor;
4930 END IF;
4931 IF l_merge_comp(1).planning_factor IS NULL THEN
4932 l_merge_comp(1).planning_factor := l_merge_comp(j).planning_factor;
4933 END IF;
4934 IF l_merge_comp(1).quantity_related IS NULL THEN
4935 l_merge_comp(1).quantity_related := l_merge_comp(j).quantity_related;
4936 END IF;
4937 IF l_merge_comp(1).so_basis IS NULL THEN
4938 l_merge_comp(1).so_basis := l_merge_comp(j).so_basis;
4939 END IF;
4940 IF l_merge_comp(1).optional IS NULL THEN
4941 l_merge_comp(1).optional := l_merge_comp(j).optional;
4942 END IF;
4943 IF l_merge_comp(1).mutually_exclusive_options IS NULL THEN
4944 l_merge_comp(1).mutually_exclusive_options := l_merge_comp(j).mutually_exclusive_options;
4945 END IF;
4946 IF l_merge_comp(1).include_in_cost_rollup IS NULL THEN
4947 l_merge_comp(1).include_in_cost_rollup := l_merge_comp(j).include_in_cost_rollup;
4948 END IF;
4949 IF l_merge_comp(1).check_atp IS NULL THEN
4950 l_merge_comp(1).check_atp := l_merge_comp(j).check_atp;
4951 END IF;
4952 IF l_merge_comp(1).shipping_allowed IS NULL THEN
4953 l_merge_comp(1).shipping_allowed := l_merge_comp(j).shipping_allowed;
4954 END IF;
4955 IF l_merge_comp(1).required_to_ship IS NULL THEN
4956 l_merge_comp(1).required_to_ship := l_merge_comp(j).required_to_ship;
4957 END IF;
4958 IF l_merge_comp(1).required_for_revenue IS NULL THEN
4959 l_merge_comp(1).required_for_revenue := l_merge_comp(j).required_for_revenue;
4960 END IF;
4961 IF l_merge_comp(1).include_on_ship_docs IS NULL THEN
4962 l_merge_comp(1).include_on_ship_docs := l_merge_comp(j).include_on_ship_docs;
4963 END IF;
4964 IF l_merge_comp(1).low_quantity IS NULL THEN
4965 l_merge_comp(1).low_quantity := l_merge_comp(j).low_quantity;
4966 END IF;
4967 IF l_merge_comp(1).high_quantity IS NULL THEN
4968 l_merge_comp(1).high_quantity := l_merge_comp(j).high_quantity;
4969 END IF;
4970 IF l_merge_comp(1).acd_type IS NULL THEN
4971 l_merge_comp(1).acd_type := l_merge_comp(j).acd_type;
4972 END IF;
4973 IF l_merge_comp(1).wip_supply_type IS NULL THEN
4974 l_merge_comp(1).wip_supply_type := l_merge_comp(j).wip_supply_type;
4975 END IF;
4976 IF l_merge_comp(1).supply_subinventory IS NULL THEN
4977 l_merge_comp(1).supply_subinventory := l_merge_comp(j).supply_subinventory;
4978 END IF;
4979 IF l_merge_comp(1).supply_locator_id IS NULL THEN
4980 l_merge_comp(1).supply_locator_id := l_merge_comp(j).supply_locator_id;
4981 END IF;
4982 IF l_merge_comp(1).location_name IS NULL THEN
4983 l_merge_comp(1).location_name := l_merge_comp(j).location_name;
4984 END IF;
4985 IF l_merge_comp(1).bom_item_type IS NULL THEN
4986 l_merge_comp(1).bom_item_type := l_merge_comp(j).bom_item_type;
4987 END IF;
4988 IF l_merge_comp(1).operation_lead_time_percent IS NULL THEN
4989 l_merge_comp(1).operation_lead_time_percent := l_merge_comp(j).operation_lead_time_percent;
4990 END IF;
4991 IF l_merge_comp(1).cost_factor IS NULL THEN
4992 l_merge_comp(1).cost_factor := l_merge_comp(j).cost_factor;
4993 END IF;
4994 IF l_merge_comp(1).include_on_bill_docs IS NULL THEN
4995 l_merge_comp(1).include_on_bill_docs := l_merge_comp(j).include_on_bill_docs;
4996 END IF;
4997 IF l_merge_comp(1).pick_components IS NULL THEN
4998 l_merge_comp(1).pick_components := l_merge_comp(j).pick_components;
4999 END IF;
5000 IF l_merge_comp(1).original_system_reference IS NULL THEN
5001 l_merge_comp(1).original_system_reference := l_merge_comp(j).original_system_reference;
5002 END IF;
5003 IF l_merge_comp(1).enforce_int_requirements IS NULL THEN
5004 l_merge_comp(1).enforce_int_requirements := l_merge_comp(j).enforce_int_requirements;
5005 END IF;
5006 IF l_merge_comp(1).optional_on_model IS NULL THEN
5007 l_merge_comp(1).optional_on_model := l_merge_comp(j).optional_on_model;
5008 END IF;
5009 IF l_merge_comp(1).auto_request_material IS NULL THEN
5010 l_merge_comp(1).auto_request_material := l_merge_comp(j).auto_request_material;
5011 END IF;
5012 IF l_merge_comp(1).suggested_vendor_name IS NULL THEN
5013 l_merge_comp(1).suggested_vendor_name := l_merge_comp(j).suggested_vendor_name;
5014 END IF;
5015 IF l_merge_comp(1).unit_price IS NULL THEN
5016 l_merge_comp(1).unit_price := l_merge_comp(j).unit_price;
5017 END IF;
5018
5019 l_temp_count := l_comp_table.COUNT;
5020 FOR k in 1..l_temp_count
5021 LOOP
5022 IF l_comp_table(k).process_flag <> -1 THEN
5023 IF l_comp_table(k).interface_table_unique_id = l_merge_comp(j).interface_table_unique_id THEN
5024 l_comp_table(k).process_flag := -1;
5025 END IF;
5026 END IF;
5027 END LOOP;
5028
5029 l_merge_comp(j).process_flag := -1;
5030 END LOOP; -- merge table loop
5031
5032 UPDATE bom_inventory_comps_interface
5033 SET operation_seq_num = l_merge_comp(1).operation_seq_num,
5034 new_operation_seq_num = l_merge_comp(1).new_operation_seq_num,
5035 basis_type = l_merge_comp(1).basis_type,
5036 component_quantity = l_merge_comp(1).component_quantity,
5037 inverse_quantity = l_merge_comp(1).inverse_quantity,
5038 component_yield_factor = l_merge_comp(1).component_yield_factor,
5039 planning_factor = l_merge_comp(1).planning_factor,
5040 quantity_related = l_merge_comp(1).quantity_related,
5041 so_basis = l_merge_comp(1).so_basis,
5042 optional = l_merge_comp(1).optional,
5043 mutually_exclusive_options = l_merge_comp(1).mutually_exclusive_options,
5044 include_in_cost_rollup = l_merge_comp(1).include_in_cost_rollup,
5045 check_atp = l_merge_comp(1).check_atp,
5046 shipping_allowed = l_merge_comp(1).shipping_allowed,
5047 required_to_ship = l_merge_comp(1).required_to_ship,
5048 required_for_revenue = l_merge_comp(1).required_for_revenue,
5049 include_on_ship_docs = l_merge_comp(1).include_on_ship_docs,
5050 low_quantity = l_merge_comp(1).low_quantity,
5051 high_quantity = l_merge_comp(1).high_quantity,
5052 acd_type = l_merge_comp(1).acd_type ,
5053 wip_supply_type = l_merge_comp(1).wip_supply_type,
5054 supply_subinventory = l_merge_comp(1).supply_subinventory,
5055 supply_locator_id = l_merge_comp(1).supply_locator_id,
5056 location_name = l_merge_comp(1).location_name,
5057 bom_item_type = l_merge_comp(1).bom_item_type,
5058 operation_lead_time_percent = l_merge_comp(1).operation_lead_time_percent,
5059 cost_factor = l_merge_comp(1).cost_factor,
5060 include_on_bill_docs = l_merge_comp(1).include_on_bill_docs,
5061 pick_components = l_merge_comp(1).pick_components,
5062 original_system_reference = l_merge_comp(1).original_system_reference,
5063 enforce_int_requirements = l_merge_comp(1).enforce_int_requirements,
5064 optional_on_model = l_merge_comp(1).optional_on_model,
5065 auto_request_material = l_merge_comp(1).auto_request_material,
5066 suggested_vendor_name = l_merge_comp(1).suggested_vendor_name,
5067 unit_price = l_merge_comp(1).unit_price
5068 WHERE batch_id = p_batch_id
5069 AND interface_table_unique_id = l_merge_comp(1).interface_table_unique_id;
5070
5071 UPDATE bom_inventory_comps_interface
5072 SET process_flag = -1
5073 WHERE batch_id = p_batch_id
5074 AND ( component_sequence_id = l_merge_comp(1).component_sequence_id
5075 OR ( component_sequence_id IS NULL
5076 AND (component_item_number = l_merge_comp(1).component_item_number OR comp_source_system_reference = l_merge_comp(1).comp_source_system_reference)
5077 AND (assembly_item_number = l_merge_comp(1).assembly_item_number OR parent_source_system_reference = l_merge_comp(1).parent_source_system_reference)
5078 AND (operation_seq_num = l_merge_comp(1).operation_seq_num OR new_operation_seq_num = l_merge_comp(1).new_operation_seq_num)
5079 AND (( effectivity_date = l_merge_comp(1).effectivity_date OR new_effectivity_date = l_merge_comp(1).new_effectivity_date)
5080 OR from_end_item_unit_number = l_merge_comp(1).from_end_item_unit_number
5081 OR from_end_item_rev_id = l_merge_comp(1).from_end_item_rev_id
5082 )
5083 )
5084 )
5085 AND interface_table_unique_id <> l_merge_comp(1).interface_table_unique_id;
5086
5087 Merge_Ref_Desgs
5088 (
5089 p_batch_id => p_batch_id,
5090 p_comp_seq_id => l_merge_comp(1).component_sequence_id,
5091 p_comp_name => l_merge_comp(1).component_item_number,
5092 p_comp_ref => l_merge_comp(1).comp_source_system_reference,
5093 p_effec_date => l_merge_comp(1).effectivity_date,
5094 p_op_seq => l_merge_comp(1).operation_seq_num,
5095 p_new_effec_date => l_merge_comp(1).new_effectivity_date,
5096 p_new_op_seq => l_merge_comp(1).new_operation_seq_num,
5097 p_from_unit => l_merge_comp(1).from_end_item_unit_number,
5098 p_from_item_id => l_merge_comp(1).from_end_item_rev_id,
5099 p_parent_name => l_merge_comp(1).assembly_item_number,
5100 p_parent_ref => l_merge_comp(1).parent_source_system_reference,
5101 x_Ret_Status => x_Ret_Status,
5102 x_Error_Mesg => x_Error_Mesg
5103 );
5104
5105 Merge_User_Attrs
5106 (
5107 p_batch_id => p_batch_id,
5108 p_comp_seq => l_merge_comp(1).component_sequence_id,
5109 p_comp_name => l_merge_comp(1).component_item_number,
5110 p_comp_ref => l_merge_comp(1).comp_source_system_reference,
5111 p_txn_id => l_merge_comp(1).transaction_id,
5112 p_par_name => l_merge_comp(1).assembly_item_number,
5113 p_par_ref => l_merge_comp(1).parent_source_system_reference,
5114 p_org_id => l_merge_comp(1).organization_id,
5115 p_org_code => l_merge_comp(1).organization_code,
5116 x_Ret_Status => x_Ret_Status,
5117 x_Error_Mesg => x_Error_Mesg);
5118
5119 END IF; -- merge count >1
5120
5121 END IF; --comp_table.process_flag = 1
5122 END LOOP; -- comp_table loop
5123 x_Ret_Status := FND_API.G_RET_STS_SUCCESS;
5124 END Merge_Duplicate_Rows;
5125
5126 PROCEDURE Merge_Ref_Desgs
5127 (
5128 p_batch_id IN NUMBER,
5129 p_comp_seq_id IN NUMBER,
5130 p_comp_name IN VARCHAR2,
5131 p_comp_ref IN VARCHAR2,
5132 p_effec_date IN DATE,
5133 p_op_seq IN NUMBER,
5134 p_new_effec_date IN DATE,
5135 p_new_op_seq IN NUMBER,
5136 p_from_unit IN VARCHAR2,
5137 p_from_item_id IN NUMBER,
5138 p_parent_name IN VARCHAR2,
5139 p_parent_ref IN VARCHAR2,
5140 x_Ret_Status IN OUT NOCOPY VARCHAR2,
5141 x_Error_Mesg IN OUT NOCOPY VARCHAR2
5142 )
5143 IS
5144
5145 TYPE num_type IS TABLE OF NUMBER;
5146 TYPE var_type IS TABLE OF VARCHAR2(100);
5147
5148 l_max_unique_id num_type;
5149 l_comp_ref_des var_type;
5150 l_count NUMBER;
5151
5152 CURSOR Get_Ref_Desgs
5153 IS
5154 SELECT COMPONENT_REFERENCE_DESIGNATOR,MAX(interface_table_unique_id)
5155 FROM bom_ref_desgs_interface
5156 where batch_id = p_batch_id
5157 and process_flag = 1
5158 and ( component_sequence_id = p_comp_seq_id
5159 OR ( (component_item_number = p_comp_name OR comp_source_system_reference = p_comp_ref)
5160 and (nvl(operation_seq_num,1) = nvl(p_op_seq,1) OR nvl(operation_seq_num,1) = nvl(p_new_op_seq,1) )
5161 and (( nvl(effectivity_date,sysdate) = nvl(p_effec_date,sysdate) OR nvl(effectivity_date,sysdate) = nvl(p_new_effec_date,sysdate))
5162 or from_end_item_unit_number = p_from_unit
5163 --or from_end_item_rev_id = p_from_item_id
5164 )
5165 and (assembly_item_number = p_parent_name OR parent_source_system_reference = p_parent_ref)
5166 )
5167 )
5168 GROUP BY component_reference_designator;
5169
5170
5171 BEGIN
5172
5173 OPEN Get_Ref_Desgs;
5174 FETCH Get_Ref_Desgs BULK COLLECT INTO l_comp_ref_des,l_max_unique_id;
5175 CLOSE Get_Ref_Desgs;
5176
5177 l_count := l_max_unique_id.COUNT;
5178
5179 FOR i in 1..l_count
5180 LOOP
5181 UPDATE bom_ref_desgs_interface
5182 SET process_flag = -1
5183 WHERE batch_id = p_batch_id
5184 AND ( process_flag = 1 OR process_flag = 5)
5185 AND ( component_sequence_id = p_comp_seq_id
5186 OR ( (component_item_number = p_comp_name OR comp_source_system_reference = p_comp_ref)
5187 AND (nvl(operation_seq_num,1) = nvl(p_op_seq,1) OR nvl(operation_seq_num,1) = nvl(p_new_op_seq,1) )
5188 AND (( nvl(effectivity_date,sysdate) = nvl(p_effec_date,sysdate) OR nvl(effectivity_date,sysdate) = nvl(p_new_effec_date,sysdate))
5189 or from_end_item_unit_number = p_from_unit
5190 --or from_end_item_rev_id = p_from_item_id
5191 )
5192 AND (assembly_item_number = p_parent_name OR parent_source_system_reference = p_parent_ref)
5193 )
5194 )
5195 AND component_reference_designator = l_comp_ref_des(i)
5196 AND interface_table_unique_id <> l_max_unique_id(i);
5197 END LOOP;
5198 x_Ret_Status := FND_API.G_RET_STS_SUCCESS;
5199 END Merge_Ref_Desgs;
5200
5201 PROCEDURE Merge_User_Attrs
5202 (
5203 p_batch_id IN NUMBER,
5204 p_comp_seq IN NUMBER,
5205 p_comp_name IN VARCHAR2,
5206 p_comp_ref IN VARCHAR2,
5207 p_txn_id IN NUMBER,
5208 p_par_name IN VARCHAR2,
5209 p_par_ref IN VARCHAR2,
5210 p_org_id IN NUMBER,
5211 p_org_code IN VARCHAR2,
5212 x_Ret_Status IN OUT NOCOPY VARCHAR2,
5213 x_Error_Mesg IN OUT NOCOPY VARCHAR2
5214 )
5215 IS
5216
5217 TYPE bom_comp_attr_type IS TABLE OF bom_cmp_usr_attr_interface%ROWTYPE;
5218 l_attr_table bom_comp_attr_type;
5219 l_merge_table bom_comp_attr_type;
5220 l_count NUMBER;
5221 l_merge_count NUMBER;
5222 l_temp_count NUMBER;
5223 l_multi_row VARCHAR2(5);
5224
5225 CURSOR Get_User_Attrs
5226 IS
5227 SELECT *
5228 FROM bom_cmp_usr_attr_interface
5229 WHERE (data_set_id = p_batch_id OR batch_id = p_batch_id)
5230 AND ( component_sequence_id = p_comp_seq
5231 OR( (item_number = p_comp_name or comp_source_system_reference = p_comp_ref)
5232 AND (assembly_item_number = p_par_name OR parent_source_system_reference = p_par_ref)
5233 )
5234 )
5235 AND process_status = 0
5236 AND (organization_id = p_org_id OR organization_code = p_org_code);
5237 -- AND transaction_id = p_txn_id;
5238
5239 CURSOR Get_Same_Attrs
5240 (
5241 l_grp_int_name IN VARCHAR2,
5242 l_attr_int_name IN VARCHAR2,
5243 l_str_type_id IN NUMBER
5244 )
5245 IS
5246 SELECT *
5247 FROM bom_cmp_usr_attr_interface
5248 WHERE (data_set_id = p_batch_id OR batch_id = p_batch_id)
5249 AND ( component_sequence_id = p_comp_seq
5250 OR ( (item_number = p_comp_name or comp_source_system_reference = p_comp_ref)
5251 AND (assembly_item_number = p_par_name OR parent_source_system_reference = p_par_ref)
5252 )
5253 )
5254 --AND transaction_id = p_txn_id
5255 ANd process_status = 0
5256 AND (organization_id = p_org_id OR organization_code = p_org_code)
5257 AND attr_group_int_name = l_grp_int_name
5258 AND attr_int_name = l_attr_int_name
5259 AND structure_type_id = l_str_type_id
5260 ORDER BY interface_table_unique_id DESC;
5261
5262 BEGIN
5263 OPEN Get_User_Attrs;
5264 FETCH Get_User_Attrs BULK COLLECT INTO l_attr_table;
5265 CLOSE Get_User_Attrs;
5266
5267 l_count := l_attr_table.COUNT;
5268 FOR i in 1..l_count
5269 LOOP
5270 BEGIN
5271 SELECT multi_row_code
5272 INTO l_multi_row
5273 FROM ego_attr_groups_v
5274 WHERE attr_group_name = l_attr_table(i).ATTR_GROUP_INT_NAME
5275 AND attr_group_type = 'BOM_COMPONENTMGMT_GROUP';
5276
5277 EXCEPTION WHEN NO_DATA_FOUND THEN
5278 /*
5279 This error will be handled by the EXT API.
5280 All Attr Group related validations are handled by EXT API.
5281 */
5282 NULL;
5283 END;
5284
5285 IF nvl(l_multi_row,'N') <> 'Y' THEN
5286 IF l_attr_table(i).process_status = 0 THEN
5287 OPEN Get_Same_Attrs
5288 (
5289 l_attr_table(i).attr_group_int_name,
5290 l_attr_table(i).attr_int_name,
5291 l_attr_table(i).structure_type_id
5292 );
5293 FETCH Get_Same_Attrs BULK COLLECT INTO l_merge_table;
5294 CLOSE Get_Same_Attrs;
5295
5296 l_merge_count := l_merge_table.COUNT;
5297
5298 IF l_merge_count > 1 THEN
5299
5300 FOR j in 2..l_merge_count
5301 LOOP
5302 IF l_merge_table(1).attr_value_str IS NULL THEN
5303 l_merge_table(1).attr_value_str := l_merge_table(j).attr_value_str;
5304 END IF;
5305 IF l_merge_table(1).attr_value_num IS NULL THEN
5306 l_merge_table(1).attr_value_num := l_merge_table(j).attr_value_num;
5307 END IF;
5308 IF l_merge_table(1).attr_value_date IS NULL THEN
5309 l_merge_table(1).attr_value_date := l_merge_table(j).attr_value_date;
5310 END IF;
5311 IF l_merge_table(1).attr_disp_value IS NULL THEN
5312 l_merge_table(1).attr_disp_value := l_merge_table(j).attr_disp_value;
5313 END IF;
5314
5315 l_temp_count := l_attr_table.COUNT;
5316 FOR k in 1..l_temp_count
5317 LOOP
5318 IF l_attr_table(k).process_status <> -1 THEN
5319 IF
5320 l_attr_table(k).interface_table_unique_id = l_merge_table(j).interface_table_unique_id THEN
5321 l_attr_table(k).process_status := -1;
5322 END IF;
5323 END IF;
5324 END LOOP;
5325
5326 l_merge_table(j).process_status := -1;
5327 END LOOP; --same attrs loop
5328
5329 UPDATE bom_cmp_usr_attr_interface
5330 SET attr_value_str = l_merge_table(1).attr_value_str,
5331 attr_value_num = l_merge_table(1).attr_value_num,
5332 attr_value_date = l_merge_table(1).attr_value_date,
5333 attr_disp_value = l_merge_table(1).attr_disp_value
5334 WHERE (data_set_id = p_batch_id OR batch_id = p_batch_id)
5335 AND interface_table_unique_id = l_merge_table(1).interface_table_unique_id;
5336
5337 UPDATE bom_cmp_usr_attr_interface
5338 SET process_status = -1
5339 WHERE (batch_id = p_batch_id or data_set_id = p_batch_id)
5340 AND ( component_sequence_id = l_merge_table(1).component_sequence_id
5341 OR ( (item_number = l_merge_table(1).item_number or comp_source_system_reference = l_merge_table(1).comp_source_system_reference)
5342 AND (assembly_item_number = l_merge_table(1).assembly_item_number OR parent_source_system_reference = l_merge_table(1).parent_source_system_reference )
5343 )
5344 )
5345 --AND transaction_id = l_merge_table(1).transaction_id
5346 AND attr_group_int_name = l_merge_table(1).attr_group_int_name
5347 AND attr_int_name = l_merge_table(1).attr_int_name
5348 AND structure_type_id = l_merge_table(1).structure_type_id
5349 AND interface_table_unique_id <> l_merge_table(1).interface_table_unique_id;
5350
5351 END IF; -- merge count >1
5352
5353 END IF; -- process flag = 1
5354 END IF; -- Multi Row
5355 END LOOP; -- attrs loop
5356
5357 x_Ret_Status := FND_API.G_RET_STS_SUCCESS;
5358 END Merge_User_Attrs;
5359
5360 PROCEDURE Process_CM_Options(p_batch_id IN NUMBER)
5361 IS
5362 CURSOR Get_Header
5363 IS
5364 SELECT *
5365 FROM bom_bill_of_mtls_interface
5366 WHERE batch_id = p_batch_id
5367 AND process_flag = 1;
5368
5369 TYPE bom_header_type IS TABLE OF bom_bill_of_mtls_interface%ROWTYPE;
5370 l_header_table bom_header_type;
5371 l_str_chng_policy VARCHAR2(50);
5372 l_count NUMBER;
5373 l_rev_id NUMBER;
5374
5375 BEGIN
5376 --Update Change Required :Start
5377 IF ( pG_batch_options.CHANGE_ORDER_CREATION = 'N' OR
5378 pG_batch_options.CHANGE_ORDER_CREATION = 'E')
5379 THEN
5380 IF nvl(pG_batch_options.ADD_ALL_TO_CHANGE_FLAG,'N') = 'Y' THEN
5381 -- Only for header setting the process flag to 5 even for already
5382 -- process_flag = 7 records for bug 4686771
5383 UPDATE bom_bill_of_mtls_interface
5384 SET process_flag = 5
5385 --pending_from_ecn = nvl(pending_from_ecn,pG_batch_options.CHANGE_NOTICE) we need not do this
5386 WHERE batch_id = p_batch_id
5387 AND (process_flag = 1 OR process_flag = 7);
5388
5389 UPDATE bom_inventory_comps_interface
5390 SET process_flag = 5
5391 --change_notice = nvl(change_notice,pG_batch_options.CHANGE_NOTICE) we need not do this
5392 WHERE batch_id = p_batch_id
5393 AND process_flag = 1;
5394
5395 --Update other entities also
5396 UPDATE bom_ref_desgs_interface
5397 SET process_flag = 5
5398 WHERE batch_id = p_batch_id
5399 AND process_flag = 1;
5400
5401 UPDATE bom_sub_comps_interface
5402 SET process_flag = 5
5403 WHERE batch_id = p_batch_id
5404 AND process_flag = 1;
5405
5406 UPDATE bom_component_ops_interface
5407 SET process_flag = 5
5408 WHERE batch_id = p_batch_id
5409 AND process_flag = 1;
5410 ELSE
5411 OPEN Get_Header;
5412 FETCH Get_Header BULK COLLECT INTO l_header_table;
5413 CLOSE Get_Header;
5414
5415 l_count := l_header_table.COUNT;
5416 FOR i in 1..l_count LOOP
5417
5418 IF l_header_table(i).revision IS NOT NULL THEN
5419 SELECT mrb.revision_id
5420 INTO l_rev_id
5421 FROM mtl_item_revisions_b mrb
5422 WHERE mrb.inventory_item_id = l_header_table(i).assembly_item_id
5423 AND mrb.organization_id = l_header_table(i).organization_id
5424 AND mrb.revision = l_header_table(i).revision;
5425 END IF;
5426
5427 IF l_rev_id IS NOT NULL THEN
5428 l_str_chng_policy := BOM_GLOBALS.Get_Change_Policy_Val (l_header_table(i).assembly_item_id,
5429 l_header_table(i).organization_id,
5430 l_rev_id,
5431 null,
5432 l_header_table(i).structure_type_id);
5433 ELSE
5434 l_str_chng_policy := BOM_GLOBALS.Get_Change_Policy_Val (l_header_table(i).assembly_item_id,
5435 l_header_table(i).organization_id,
5436 NULL,
5437 SYSDATE,
5438 l_header_table(i).structure_type_id);
5439 END IF;
5440
5441 IF l_str_chng_policy = 'CHANGE_ORDER_REQUIRED' THEN
5442 UPDATE bom_bill_of_mtls_interface
5443 SET process_flag = 5
5444 WHERE batch_id = p_batch_id
5445 AND (process_flag = 1 OR process_flag = 7)
5446 AND interface_table_unique_id = l_header_table(i).interface_table_unique_id;
5447
5448
5449 UPDATE bom_inventory_comps_interface
5450 SET process_flag = 5
5451 WHERE batch_id = p_batch_id
5452 AND process_flag = 1
5453 AND ( bill_sequence_id = l_header_table(i).bill_sequence_id
5454 OR ( assembly_item_id = l_header_table(i).assembly_item_id
5455 AND organization_id = l_header_table(i).organization_id
5456 AND nvl(alternate_bom_designator,'Primary') = nvl(l_header_table(i).alternate_bom_designator,'Primary')
5457 )
5458 OR ( assembly_item_number = l_header_table(i).item_number
5459 AND organization_code = l_header_table(i).organization_code
5460 AND nvl(alternate_bom_designator,'Primary') = nvl(l_header_table(i).alternate_bom_designator,'Primary')
5461 )
5462 );
5463
5464 UPDATE bom_ref_desgs_interface
5465 SET process_flag = 5
5466 WHERE batch_id = p_batch_id
5467 AND process_flag = 1
5468 AND ( bill_sequence_id = l_header_table(i).bill_sequence_id
5469 OR ( assembly_item_id = l_header_table(i).assembly_item_id
5470 AND organization_id = l_header_table(i).organization_id
5471 AND nvl(alternate_bom_designator,'Primary') = nvl(l_header_table(i).alternate_bom_designator,'Primary')
5472 )
5473 OR ( assembly_item_number = l_header_table(i).item_number
5474 AND organization_code = l_header_table(i).organization_code
5475 AND nvl(alternate_bom_designator,'Primary') = nvl(l_header_table(i).alternate_bom_designator,'Primary')
5476 )
5477 );
5478
5479 UPDATE bom_sub_comps_interface
5480 SET process_flag = 5
5481 WHERE batch_id = p_batch_id
5482 AND process_flag = 1
5483 AND ( bill_sequence_id = l_header_table(i).bill_sequence_id
5484 OR ( assembly_item_id = l_header_table(i).assembly_item_id
5485 AND organization_id = l_header_table(i).organization_id
5486 AND nvl(alternate_bom_designator,'Primary') = nvl(l_header_table(i).alternate_bom_designator,'Primary')
5487 )
5488 OR ( assembly_item_number = l_header_table(i).item_number
5489 AND organization_code = l_header_table(i).organization_code
5490 AND nvl(alternate_bom_designator,'Primary') = nvl(l_header_table(i).alternate_bom_designator,'Primary')
5491 )
5492 );
5493
5494 UPDATE bom_component_ops_interface
5495 SET process_flag = 5
5496 WHERE batch_id = p_batch_id
5497 AND process_flag = 1
5498 AND ( bill_sequence_id = l_header_table(i).bill_sequence_id
5499 OR ( assembly_item_id = l_header_table(i).assembly_item_id
5500 AND organization_id = l_header_table(i).organization_id
5501 AND nvl(alternate_bom_designator,'Primary') = nvl(l_header_table(i).alternate_bom_designator,'Primary')
5502 )
5503 OR ( assembly_item_number = l_header_table(i).item_number
5504 AND organization_code = l_header_table(i).organization_code
5505 AND nvl(alternate_bom_designator,'Primary') = nvl(l_header_table(i).alternate_bom_designator,'Primary')
5506 )
5507 );
5508
5509 UPDATE bom_cmp_usr_attr_interface
5510 SET process_status = 5
5511 WHERE batch_id = p_batch_id
5512 AND process_status= 1
5513 AND ( bill_sequence_id = l_header_table(i).bill_sequence_id
5514 OR ( assembly_item_number = l_header_table(i).item_number
5515 AND organization_code = l_header_table(i).organization_code
5516 --AND nvl(alternate_bom_designator,'Primary') = nvl(l_header_table(i).alternate_bom_designator,'Primary')
5517 )
5518 );
5519
5520 END IF;
5521 END LOOP;
5522 --Update Change Required :End
5523 END IF;
5524 END IF;
5525 End Process_CM_Options;
5526
5527 PROCEDURE Get_Item_Security_Predicate
5528 (
5529 p_object_name IN VARCHAR2,
5530 p_party_id IN VARCHAR2,
5531 p_privilege_name IN VARCHAR2,
5532 p_table_alias IN VARCHAR2,
5533 x_security_predicate OUT NOCOPY VARCHAR2
5534 )
5535 IS
5536 l_temp_predicate VARCHAR2(2000);
5537 l_pk_column VARCHAR2(50);
5538 BEGIN
5539
5540 SELECT PK1_COLUMN_NAME
5541 INTO l_pk_column
5542 FROM fnd_objects
5543 WHERE obj_name = 'EGO_ITEM';
5544
5545 EGO_ITEM_USER_ATTRS_CP_PUB.Get_Item_Security_Predicate
5546 ( p_object_name => 'EGO_ITEM',
5547 p_party_id => p_party_id,
5548 p_privilege_name => p_privilege_name,
5549 p_table_alias => p_table_alias,
5550 x_security_predicate => x_security_predicate
5551 );
5552 l_temp_predicate := x_security_predicate;
5553 l_temp_predicate := replace(l_temp_predicate,'UAI2.'||l_pk_column,'UAI2.COMPONENT_ITEM_ID');
5554 x_security_predicate := l_temp_predicate;
5555
5556
5557 END Get_Item_Security_Predicate;
5558
5559 FUNCTION Get_Item_Matches
5560 (
5561 p_batch_id IN NUMBER,
5562 p_ss_ref IN VARCHAR2
5563 )
5564 RETURN VARCHAR2
5565 IS
5566 CURSOR Get_Matches IS
5567 SELECT match_id
5568 FROM ego_item_matches
5569 WHERE batch_id = p_batch_id
5570 AND source_system_reference = p_ss_ref;
5571
5572 TYPE num_type IS TABLE OF NUMBER;
5573 l_match_tab num_type;
5574 l_count NUMBER;
5575 l_ret_status VARCHAR2(3);
5576
5577 BEGIN
5578
5579 OPEN Get_Matches;
5580 FETCH Get_Matches BULK COLLECT INTO l_match_tab;
5581 CLOSE Get_Matches;
5582
5583 l_count := l_match_tab.COUNT;
5584 l_ret_status := 'N';
5585 IF l_count > 1 THEN
5586 l_ret_status := 'M';
5587 ELSIF l_count = 1 THEN
5588 l_ret_status := 'S';
5589 END IF;
5590 RETURN l_ret_status;
5591
5592 END Get_Item_Matches;
5593
5594
5595 END Bom_Import_Pub;