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