[Home] [Help]
PACKAGE BODY: APPS.BOM_DELETE_GROUPS_API
Source
1 PACKAGE BODY Bom_Delete_Groups_Api AS
2 /* $Header: BOMPDELB.pls 120.9 2007/07/09 11:07:51 bbpatel ship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- BOMPDELB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package Bom_Delete_Groups_Api
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 02-SEP-02 Vani Hymavathi Initial Creation
21 ***************************************************************************/
22 /*****************************************************************
23 * FUNCTION : invoke_events
24 * Parameters IN : action_type ,org_id,inv_id ,alternate,st type id,
25 * bill_id ,comp_id,delete_type
26 * Parameters OUT: err_text
27 * return : 0 -success , other - SQL Exception
28 * Purpose : This function will invokde different Business Events
29 * depending on the parameters passed.
30 ******************************************************************/
31 FUNCTION invoke_events(p_action_type IN NUMBER,
32 p_org_id IN NUMBER,
33 p_assembly_id IN NUMBER,
34 p_alternate VARCHAR2,
35 p_item_name VARCHAR2,
36 p_description VARCHAR2,
37 p_bill_id IN NUMBER,
38 p_comp_id IN NUMBER,
39 p_delete_type IN NUMBER,
40 err_text OUT NOCOPY VARCHAR2) return NUMBER;
41 /*****************************************************************
42 * FUNCTION : write_log
43 * Parameters IN : alt_desg ,org_id,item_name ,comp_name ,
44 * eff_date,op_seq,delete_type.
45 * Parameters OUT: err_text
46 * return : 0 -success , other - SQL Exception
47 * Purpose : This function will write to conc-log
48 ******************************************************************/
49 FUNCTION write_log(alt_desg IN VARCHAR2,
50 org_name IN VARCHAR2,
51 item_name IN VARCHAR2,
52 comp_name IN VARCHAR2,
53 eff_date IN VARCHAR2,
54 op_seq IN NUMBER,
55 delete_type IN NUMBER,
56 err_text OUT NOCOPY VARCHAR2) RETURN NUMBER;
57
58 /*****************************************************************
59 * FUNCTION : substitute_tokens
60 * Parameters IN : token_list -TOKEN_RECORD, stmt -LONG
61 * Parameters OUT: stmt - LONG bind_list -BIND_TABLE, err_text
62 * return : 0 -success , other - SQL Exception
63 * Purpose : This function will replace '&' with ':'.
64 * and capture all the bind parameters used in the statement.
65 ******************************************************************/
66
67 FUNCTION substitute_tokens (token_list IN TOKEN_RECORD,
68 stmt IN OUT NOCOPY long,
69 bind_list OUT NOCOPY BIND_TABLE,
70 err_text OUT NOCOPY varchar2) return NUMBER;
71
72 /*****************************************************************
73 * FUNCTION : config_item_consolidate
74 * Parameters IN : inventory_item_id,organization_id,delete_entity_type
75 * Parameters OUT: err_text
76 * return : 0 -success ,2-error, other - SQL Exception
77 * Purpose : Configuration Item Purge - Consolidate Item
78 ******************************************************************/
79
80 FUNCTION config_item_consolidate( p_inventory_item_id IN NUMBER,
81 p_organization_id IN NUMBER,
82 p_delete_entity_type IN NUMBER,
83 err_text OUT NOCOPY VARCHAR2) return NUMBER;
84
85 /*****************************************************************
86 * FUNCTION : constraint_checker
87 * Parameters IN :token_list-TOKEN_RECORD, delete_seq_id, delete_entity_type
88 * Parameters OUT: err_text
89 * return : 0 -success ,2-error, other - SQL Exception
90 * Purpose : This function checks all the constraints from
91 * bom_delete_sql_statements table that are valid for given
92 * delete entity type
93 ******************************************************************/
94
95 FUNCTION constraint_checker( token_list IN TOKEN_RECORD,
96 delete_seq_id IN NUMBER,
97 delete_entity_type IN NUMBER,
98 err_text OUT NOCOPY varchar2) return NUMBER;
99
100 /*****************************************************************
101 * FUNCTION : extract_table_name
102 * Parameters IN :stmt-LONG
103 * return : Table name present in the statement
104 ******************************************************************/
105 FUNCTION extract_table_name ( stmt IN LONG, err_text OUT NOCOPY varchar2) return LONG;
106
107 /*****************************************************************
108 * FUNCTION : extract_table_name
109 * Parameters IN :stmt-LONG
110 * return : where clause of the statement
111 ******************************************************************/
112 FUNCTION extract_where (stmt IN LONG, err_text OUT NOCOPY varchar2) return LONG;
113
114 /*****************************************************************
115 * FUNCTION : archive_data
116 * Parameters IN :token_list-TOKEN_RECORD, archive table name,
117 * product table name , where clause.
118 * Parameters OUT: err_text
119 * return : 0 -success ,2-error, other - SQL Exception
120 * Purpose : archive the data from product table.
121 ******************************************************************/
122
123 FUNCTION archive_data(token_list IN Token_Record,
124 insert_table IN VARCHAR2,
125 table_name IN VARCHAR2,
126 where_clause IN VARCHAR2,
127 bind_list IN BIND_TABLE,
128 err_text OUT NOCOPY VARCHAR2 ) return NUMBER ;
129 /*****************************************************************
130 * FUNCTION : update_op_sequences
131 * Parameters IN :tdelete_entity_type ,routing_seq_id
132 * Parameters OUT: err_text
133 * return : 0 -success , other - SQL Exception
134 * Purpose : updates op sequences to 1 in BOM_INVENTORY_COMPONENTS
135 ******************************************************************/
136
137 FUNCTION update_op_sequences(delete_entity_type IN NUMBER,
138 routing_seq_id IN NUMBER,
139 op_seq_id IN NUMBER,
140 err_text OUT NOCOPY VARCHAR2 ) return NUMBER;
141
142
143 /*****************************************************************
144 * FUNCTION : execute_delete
145 * Parameters IN :token_list-TOKEN_RECORD, delete_entity_type
146 * archive_flag
147 * Parameters OUT: err_text, action_status(4-delete,3-error)
148 * return : 0 -success , other - SQL Exception
149 * Purpose : This function executes the delete statements from
150 * bom_delete_sql_statements table that are valid for given
151 * delete entity type
152 ******************************************************************/
153
154 FUNCTION execute_delete(delete_entity_type IN NUMBER,
155 token_list IN Token_Record,
156 archive_flag IN NUMBER,
157 action_status OUT NOCOPY NUMBER,
158 err_text OUT NOCOPY VARCHAR2) return NUMBER;
159
160 -- bug:5726408 Added support for executing UPDATE statement.
161 /*****************************************************************
162 * FUNCTION : execute_update
163 * Parameters IN : delete_entity_type Type of delete entity
164 * token_list Records of tokens to be substituted
165 * Parameters OUT: action_status 4-delete, 3-error
166 * err_text Error message in case of exception
167 * return : 0 -success , other - SQL Exception
168 * Purpose : This function executes the update statements from
169 * bom_delete_sql_statements table that are valid for given
170 * delete entity type
171 ******************************************************************/
172
173 FUNCTION execute_update (
174 delete_entity_type IN NUMBER,
175 token_list IN Token_Record,
176 action_status OUT NOCOPY NUMBER,
177 err_text OUT NOCOPY VARCHAR2) RETURN NUMBER;
178
179
180 /*****************************************************************
181 * FUNCTION : do_delete
182 * Parameters IN :delete group id ,delete type
183 * action_type (check or delete),archive_flag
184 * Parameters OUT: err_text
185 * return : 0 -success ,2-error, other - SQL Exception
186 * Purpose : This function checks all the constraints,
187 * arvhives the data based on the archive_flag option
188 * deletes the data based on action_type.
189 ******************************************************************/
190
191 FUNCTION do_delete(group_id IN NUMBER,
192 delete_type IN NUMBER,
193 action_type IN NUMBER,
194 archive_flag IN NUMBER,
195 err_text OUT NOCOPY VARCHAR2,
196 process_errored_rows IN VARCHAR2) return NUMBER;
197
198
199 ENTITY CONSTANT NUMBER :=1;
200 SUB_ENTITY CONSTANT NUMBER :=2;
201 ACT_DELETE CONSTANT NUMBER := 2;
202 ACT_CHECK CONSTANT NUMBER :=1;
203 FATAL_ERROR CONSTANT NUMBER :=-1000;
204 p_debug VARCHAR2(1);
205 user_id NUMBER := -1;
206 resp_id NUMBER := -1;
207 resp_appl_id NUMBER := -1;
208 req_id NUMBER := -1;
209 prog_id NUMBER := -1;
210 prog_appl_id NUMBER := -1;
211
212
213 /*****************************************************************
214 * Procedure : delete groups
215 * Parameters IN :
216 * delete_group_id,action_type,delete_type,archive
217 * Parameters OUT: ERRBUF, RETCODE
218 * Purpose : Main procedure for checking and deleting a delete group
219 ******************************************************************/
220
221 PROCEDURE delete_groups
222 (ERRBUF OUT NOCOPY VARCHAR2,
223 RETCODE OUT NOCOPY VARCHAR2,
224 delete_group_id IN NUMBER:= '0',
225 action_type IN NUMBER:= '1',
226 delete_type IN NUMBER:= '1',
227 archive IN NUMBER:='1',
228 process_errored_rows IN VARCHAR2
229 ) is
230
231 CONC_FAILURE EXCEPTION;
232 stmt_num NUMBER;
233
234 CURSOR delete_errors ( c_delete_group_id NUMBER )
235 IS
236 SELECT 1
237 FROM
238 BOM_DELETE_ENTITIES bdent,
239 BOM_DELETE_SUB_ENTITIES bdsubent
240 WHERE
241 bdent.DELETE_ENTITY_SEQUENCE_ID = bdsubent.DELETE_ENTITY_SEQUENCE_ID(+)
242 AND ( ( bdent.DELETE_STATUS_TYPE = 3 ) OR ( bdsubent.DELETE_STATUS_TYPE = 3 ) )
243 AND bdent.DELETE_GROUP_SEQUENCE_ID = c_delete_group_id;
244
245 BEGIN
246
247 stmt_num := 0;
248 IF FND_PROFILE.VALUE('MRP_DEBUG') = 'Y' then
249 p_debug := 'Y';
250 else
251 p_debug :='N';
252 end if;
253
254 stmt_num := 1;
255 user_id := nvl(FND_PROFILE.Value('USER_ID'),-1);
256 resp_id :=nvl(FND_PROFILE.value('RESP_ID'),-1);
257 resp_appl_id := nvl(FND_PROFILE.Value('RESP_APPL_ID'),-1);
258 req_id := FND_GLOBAL.CONC_REQUEST_ID;
259 prog_id := FND_GLOBAL.CONC_PROGRAM_ID;
260 prog_appl_id := FND_GLOBAL.PROG_APPL_ID;
261
262
263
264 stmt_num :=2;
265
266 /*
267 Added a call to Package BOM_DELETE_ENTITY.insert_common_bills
268 This inserts the common Bill entities for the current org, or all Orgs
269 or Org Hierarchy depending on the option chosen on the Delete Groups
270 Form
271 */
272 bom_delete_entity.insert_common_bills(delete_group_id,delete_type);
273
274
275
276 stmt_num :=3;
277
278 if (do_delete(delete_group_id, delete_type, action_type,
279 archive, ERRBUF, process_errored_rows) <> 0) then
280 /*
281 ** if delete returned failure, write error message to log file
282 ** rollback and return CONC_FAILURE
283 */
284 raise CONC_FAILURE;
285 end if;
286
287 stmt_num :=4;
288
289 /*
290 Added a call to Package BOM_DELETE_ENTITY.insert_original_bills
291 This inserts original Bill entities for the all Orgs
292 or Org Hierarchy depending on the option chosen on the Delete Groups Form
293
294 */
295 bom_delete_entity.insert_original_bills(delete_group_id,delete_type);
296
297
298 stmt_num :=5;
299
300 if (do_delete(delete_group_id, delete_type, action_type,
301 archive , ERRBUF, process_errored_rows) <> 0) then
302
303 /*
304 ** if delete returned failure, write error message to log file
305 ** rollback and return CONC_FAILURE
306 */
307 raise CONC_FAILURE;
308 end if;
309
310 COMMIT;
311
312 --bug:5235742 Change the concurrent program completion status. Set warning, if
313 --some of the entities errored out during delete.
314 FOR l_del_errors_rec IN delete_errors(delete_group_id)
315 LOOP
316 RETCODE := '1';
317 EXIT;
318 END LOOP;
319
320 IF ( RETCODE = '1' ) THEN
321 Fnd_Message.Set_Name('BOM','BOM_CONC_REQ_WARNING');
322 ERRBUF := Fnd_Message.Get;
323 ELSE
324 RETCODE := '0';
325 Fnd_Message.Set_Name('INV','INV_STATUS_SUCCESS');
326 ERRBUF := Fnd_Message.Get;
327 fnd_file.put_line( which => fnd_file.output, buff => ERRBUF);
328 END IF; -- end if ( RETCODE = '1' )
329
330 EXCEPTION
331 when CONC_FAILURE then
332 ROLLBACK;
333 fnd_file.put_line( which => fnd_file.output,
334 buff => ERRBUF);
335 RETCODE := '2';
336 WHEN others THEN
337 ERRBUF := ERRBUF||'Bom_Delete_Groups_Api '||stmt_num||' '||substrb(SQLERRM,1,500);
338 fnd_file.put_line( which => fnd_file.output,
339 buff => ERRBUF);
340 RETCODE := '2';
341
342 end;
343
344 /*****************************************************************
345 * FUNCTION : do_delete
346 * Parameters IN :delete group id ,delete type
347 * action_type (check or delete),archive_flag
348 * Parameters OUT: err_text
349 * return : 0 -success ,2-error, other - SQL Exception
350 * Purpose : This function checks all the constraints,
351 * arvhives the data based on the archive_flag option
352 * deletes the data based on action_type.
353 ******************************************************************/
354
355 FUNCTION do_delete(group_id IN NUMBER,
356 delete_type IN NUMBER,
357 action_type IN NUMBER,
358 archive_flag IN NUMBER,
359 err_text OUT NOCOPY varchar2,
360 process_errored_rows IN VARCHAR2) return NUMBER is
361
362
363 CURSOR entity_cursor (p_group_id NUMBER )is
364 SELECT /*+ ORDERED */ BDE.INVENTORY_ITEM_ID inventory_item_id,
365 BDE.ORGANIZATION_ID organization_id,
366 ALTERNATE_DESIGNATOR alternate_designator,
367 DELETE_ENTITY_TYPE delete_entity_type,
368 nvl(BILL_SEQUENCE_ID, -1) bill_seq_id,
369 nvl(ROUTING_SEQUENCE_ID, -1) routing_seq_id,
370 -1 component_seq_id,
371 -1 operation_seq_id,
372 DELETE_ENTITY_SEQUENCE_ID delete_entity_seq_id,
373 MP.ORGANIZATION_CODE organization_code,
374 BDE.ITEM_CONCAT_SEGMENTS item_name, --bug:6193035 Removed substrb
375 BDE.ITEM_DESCRIPTION description
376 FROM BOM_DELETE_ENTITIES BDE, MTL_PARAMETERS MP
377 WHERE DELETE_GROUP_SEQUENCE_ID = p_group_id
378 AND DELETE_STATUS_TYPE in (1,2, decode(process_errored_rows, 'Y', 3, 1))
379 AND BDE.ORGANIZATION_ID = MP.ORGANIZATION_ID
380 ORDER BY decode(MP.MASTER_ORGANIZATION_ID,
381 BDE.ORGANIZATION_ID, 2, 1),
382 decode(BDE.DELETE_ENTITY_TYPE,2,9999,BDE.DELETE_ENTITY_TYPE) DESC,
383 BDE.ALTERNATE_DESIGNATOR
384 FOR UPDATE OF ALTERNATE_DESIGNATOR;
385
386 CURSOR sub_entity_cursor(p_group_id NUMBER) is
387 SELECT /*+ ORDERED */ A.INVENTORY_ITEM_ID inventory_item_id,
388 A.ORGANIZATION_ID organization_id,
389 A.ALTERNATE_DESIGNATOR alternate_designator,
390 nvl(BILL_SEQUENCE_ID, -1) bill_seq_id,
391 nvl(ROUTING_SEQUENCE_ID, -1) routing_seq_id,
392 nvl(B.COMPONENT_SEQUENCE_ID, -1) component_seq_id,
393 nvl(B.OPERATION_SEQUENCE_ID, -1) operation_seq_id,
394 B.DELETE_ENTITY_SEQUENCE_ID delete_entity_seq_id,
395 B.component_item_id component_item_id,
396 MP.ORGANIZATION_CODE organization_code,
397 B.OPERATION_SEQ_NUM op_seq_num,
398 to_char(B.EFFECTIVITY_DATE, 'YYYY/MM/DD HH24:MI') effectivity_date,
399 A.ITEM_CONCAT_SEGMENTS item_name,
400 B.COMPONENT_CONCAT_SEGMENTS comp_name,
401 B.description description
402 FROM BOM_DELETE_ENTITIES A, BOM_DELETE_SUB_ENTITIES B,
403 MTL_PARAMETERS MP
404 WHERE A.DELETE_GROUP_SEQUENCE_ID = p_group_id
405 AND B.DELETE_STATUS_TYPE in (1,2,3)
406 AND A.DELETE_ENTITY_SEQUENCE_ID =
407 B.DELETE_ENTITY_SEQUENCE_ID
408 AND MP.ORGANIZATION_ID = A.ORGANIZATION_ID
409 FOR UPDATE OF B.OPERATION_SEQUENCE_ID;
410
411 cursor_type NUMBER;
412 curr_del_entity_type NUMBER;
413 current_seq_id NUMBER;
414 curr_comp_seq_id NUMBER;
415 curr_op_seq_id NUMBER;
416 chk_ret NUMBER;
417 action_status NUMBER;
418 token_list Token_Record;
419 delete_success NUMBER := 0;
420 stmt_num NUMBER :=0;
421
422 BEGIN
423
424 stmt_num := 1;
425 /*
426 ** retrieve all rows from DELETE_ENTITIES or DELETE_SUB_ENTITIES table
427 ** depending on delete
428 */
429 if (delete_type =1 OR delete_type=2 OR delete_type=3 OR delete_type=6 OR delete_type=7 ) THEN
430 cursor_type := ENTITY;
431 else
432 cursor_type := SUB_ENTITY;
433 end if;
434
435 stmt_num := 2;
436 if (cursor_type = ENTITY) THEN
437
438 FOR entity_record in entity_cursor(group_id) LOOP
439 SAVEPOINT start_process;
440
441 current_seq_id := entity_record.delete_entity_seq_id;
442 if(p_debug = 'Y')then
443 fnd_file.put_line (Which => FND_FILE.LOG,
444 buff => 'delete_entity_Seq_id = '|| to_char(current_seq_id));
445 end if;
446 curr_del_entity_type:= entity_record.delete_entity_type;
447 token_list.inventory_item_id := entity_record.inventory_item_id;
448 token_list.organization_id := entity_record.organization_id;
449 token_list.bill_sequence_id := entity_record.bill_seq_id;
450 token_list.routing_sequence_id := entity_record.routing_seq_id;
451 token_list.component_sequence_id := entity_record.component_seq_id;
452 token_list.operation_sequence_id := entity_record.operation_seq_id;
453 token_list.del_group_seq_id := group_id;
454 stmt_num := 3;
455 /*
456 delete all errors if any for this entity row since we are going to
457 rerun the delete on it
458 */
459
460 IF (curr_del_entity_type=1 OR curr_del_entity_type=2 OR curr_del_entity_type=3 )THEN
461 DELETE FROM BOM_DELETE_ERRORS
462 WHERE DELETE_ENTITY_SEQUENCE_ID = current_seq_id;
463 END IF;
464
465 stmt_num := 5;
466 chk_ret := constraint_checker(token_list, current_seq_id,
467 curr_del_entity_type, err_text );
468 stmt_num := 6;
469 if (chk_ret = 0) THEN
470
471 /*
472 ** perform delete here if action_type is delete
473 */
474 if (action_type = ACT_DELETE) then
475 stmt_num := 7;
476
477 if (execute_delete(entity_record.delete_entity_type,
478 token_list, archive_flag, action_status,
479 err_text ) <> 0 ) then
480
481 ROLLBACK TO SAVEPOINT start_process;
482 delete_success := 1;
483 else
484 if (action_status = 4) then
485 /*
486 ** write to log file for every lntity deleted
487 */
488 if ( write_log(
489 entity_record.alternate_designator,
490 entity_record.organization_code,
491 entity_record.item_name,
492 null,
493 null,
494 null,
495 entity_record.delete_entity_type,
496 err_text )<>0) then
497 return 2;
498 end if;
499 /*
500 ** invoke business events
501 */
502 if (invoke_events(
503 action_status,
504 entity_record.organization_id,
505 entity_record.inventory_item_id,
506 entity_record.alternate_designator,
507 entity_record.item_name,
508 entity_record.description,
509 null,
510 null,
511 entity_record.delete_entity_type,
512 err_text) <> 0) then
513 return 2;
514 end if;
515 end if;
516 end if;
517 else
518 action_status := 2; /* check ok */
519 end if;
520 elsif (chk_ret = 1) then
521 action_status := 3; /* error */
522 /*
523 ** invoke business events
524 */
525 if (invoke_events(
526 action_status,
527 entity_record.organization_id,
528 entity_record.inventory_item_id,
529 entity_record.alternate_designator,
530 entity_record.item_name,
531 entity_record.description,
532 null,
533 null,
534 entity_record.delete_entity_type,
535 err_text) <>0) then
536 return 2;
537 end if;
538 else
539 ROLLBACK TO SAVEPOINT start_process;
540 return 2;
541 end if;
542 /*
543 ** set the status
544 */
545 UPDATE BOM_DELETE_ENTITIES
546 SET DELETE_STATUS_TYPE = action_status,
547 DELETE_DATE = decode(action_status, 4,
548 sysdate, NULL),
549 LAST_UPDATE_DATE = sysdate,
550 LAST_UPDATED_BY = user_id,
551 REQUEST_ID = req_id,
552 PROGRAM_UPDATE_DATE = SYSDATE,
553 PROGRAM_ID = prog_id,
554 PROGRAM_APPLICATION_ID = prog_appl_id
555 WHERE DELETE_ENTITY_SEQUENCE_ID = current_seq_id;
556
557 END LOOP; /* for */
558
559 else
560 for sub_entity_record in sub_entity_cursor(group_id) LOOP
561 SAVEPOINT start_process;
562
563 current_seq_id := sub_entity_record.DELETE_ENTITY_SEQ_ID;
564 if(p_debug = 'Y')then
565 fnd_file.put_line (Which => FND_FILE.LOG,
566 buff => 'delete_entity_Seq_id = '|| to_char(current_seq_id));
567 end if;
568 curr_del_entity_type:= delete_type;
569 token_list.inventory_item_id := sub_entity_record.inventory_item_id;
570 token_list.organization_id := sub_entity_record.organization_id;
571 token_list.bill_sequence_id := sub_entity_record.bill_seq_id;
572 token_list.routing_sequence_id := sub_entity_record.routing_seq_id;
573 token_list.component_sequence_id := sub_entity_record.component_seq_id;
574 token_list.operation_sequence_id := sub_entity_record.operation_seq_id;
575 token_list.del_group_seq_id := group_id;
576 token_list.component_item_id := sub_entity_record.component_item_id; -- added for bug:5726408
577
578 /*
579 ** delete all errors if any for this entity row since we are going to
580 ** rerun the delete on it
581 */
582 if (curr_del_entity_type=4 ) then
583 curr_comp_seq_id := sub_entity_record.component_seq_id;
584 DELETE FROM BOM_DELETE_ERRORS
585 WHERE DELETE_ENTITY_SEQUENCE_ID = current_seq_id
586 AND COMPONENT_SEQUENCE_ID = curr_comp_seq_id;
587 elsif ( curr_del_entity_type= 5) then
588 curr_op_seq_id := sub_entity_record.operation_seq_id;
589 DELETE FROM BOM_DELETE_ERRORS
590 WHERE DELETE_ENTITY_SEQUENCE_ID = current_seq_id
591 AND OPERATION_SEQUENCE_ID = curr_op_seq_id;
592 end if;
593
594 chk_ret := constraint_checker(token_list, current_seq_id,
595 curr_del_entity_type, err_text );
596
597 if (chk_ret = 0) then
598 /*
599 ** perform delete here if action_type is delete
600 */
601 if (action_type = ACT_DELETE) then
602 if (execute_delete(curr_del_entity_type,
603 token_list, archive_flag, action_status,
604 err_text ) <>0) THEN
605 ROLLBACK TO SAVEPOINT start_process;
606 delete_success := 1;
607 else
608 if (action_status = 4) then
609 /*
610 ** write to log file for every entity deleted
611 */
612 if(write_log(
613 sub_entity_record.alternate_designator,
614 sub_entity_record.organization_code,
615 sub_entity_record.item_name,
616 sub_entity_record.comp_name,
617 sub_entity_record.effectivity_date,
618 sub_entity_record.op_seq_num,
619 delete_type,err_text)<>0)then
620 return 2;
621 end if;
622
623 /*
624 ** invoke business events
625 */
626 if(invoke_events(
627 action_status,
628 sub_entity_record.organization_id,
629 null,
630 null,
631 sub_entity_record.comp_name,
632 sub_entity_record.description,
633 sub_entity_record.bill_seq_id,
634 sub_entity_record.component_item_id,
635 delete_type, err_text)<>0)then
636 return 2;
637 end if;
638
639 end if;
640 end if;
641
642 /* bug:5726408 Execute UPDATE statement for entity */
643 IF ( execute_update( curr_del_entity_type,
644 token_list,
645 action_status,
646 err_text ) <> 0 )
647 THEN
648
649 ROLLBACK TO SAVEPOINT start_process;
650 delete_success := 1;
651
652 END IF;
653
654 else
655 action_status := 2; /* check ok */
656 end if;
657 elsif (chk_ret = 1) then
658 action_status := 3; /* error */
659 /*
660 ** invoke business events
661 */
662 if(invoke_events(
663 action_status,
664 sub_entity_record.organization_id,
665 null,
666 null,
667 sub_entity_record.comp_name,
668 sub_entity_record.description,
669 sub_entity_record.bill_seq_id,
670 sub_entity_record.component_item_id,
671 delete_type,err_text)<>0)then
672 return 2;
673 end if;
674
675 else
676 ROLLBACK TO SAVEPOINT start_process;
677 return 2;
678 end if;
679
680 /*
681 ** set the status
682 */
683
684 UPDATE BOM_DELETE_SUB_ENTITIES
685 SET DELETE_STATUS_TYPE = action_status,
686 DELETE_DATE = decode(action_status, 4,
687 sysdate, NULL),
688 LAST_UPDATE_DATE = sysdate,
689 LAST_UPDATED_BY = user_id,
690 REQUEST_ID = req_id,
691 PROGRAM_UPDATE_DATE = SYSDATE,
692 PROGRAM_ID = prog_id,
693 PROGRAM_APPLICATION_ID = prog_appl_id
694 WHERE DELETE_ENTITY_SEQUENCE_ID = current_seq_id
695 AND ((delete_type = 4
696 AND COMPONENT_SEQUENCE_ID =
697 curr_comp_seq_id)
698 OR
699 (curr_del_entity_type = 5
700 AND OPERATION_SEQUENCE_ID =
701 curr_op_seq_id));
702 END LOOP;
703 end if;
704
705 if (delete_success = 1) then
706 return 2;
707 end if;
708 if (entity_cursor % ISOPEN) then
709 close entity_cursor;
710 end if;
711 if (sub_entity_cursor %ISOPEN) then
712 close sub_entity_cursor;
713 end if;
714
715 return 0;
716
717 EXCEPTION
718 WHEN others THEN
719 err_text := err_text||'do_delete '||stmt_num||' '||substrb(SQLERRM,1,500);
720 RETURN SQLCODE;
721
722
723 END;
724
725
726 /*****************************************************************
727 * FUNCTION : constraint_checker
728 * Parameters IN :token_list-TOKEN_RECORD, delete_seq_id, delete_entity_type
729 * Parameters OUT: err_text
730 * return : 0 -success ,2-error, other - SQL Exception
731 * Purpose : This function checks all the constraints from
732 * bom_delete_sql_statements table that are valid for given
733 * delete entity type
734 ******************************************************************/
735
736 FUNCTION constraint_checker( token_list IN Token_Record,
737 delete_seq_id IN NUMBER,
738 delete_entity_type IN NUMBER,
739 err_text OUT NOCOPY varchar2) return NUMBER is
740
741 CURSOR constraint_cursor(p_delete_entity_type NUMBER) IS
742 SELECT SQL_STATEMENT_NAME stmt_name,
743 DELETE_ON_SUCCESS_FLAG delete_on_success_flag,
744 MESSAGE_NAME,
745 SQL_STATEMENT
746 FROM BOM_DELETE_SQL_STATEMENTS
747 WHERE SQL_STATEMENT_TYPE = 1
748 AND ACTIVE_FLAG = 1
749 AND DELETE_ENTITY_TYPE = p_delete_entity_type;
750
751 cur_rec constraint_cursor%ROWTYPE;
752 INVENTORY_ITEM_ID NUMBER;
753 ORGANIZATION_ID NUMBER;
754 COMPONENT_SEQUENCE_ID NUMBER;
755 OPERATION_SEQUENCE_ID NUMBER;
756 BILL_SEQUENCE_ID NUMBER;
757 ROUTING_SEQUENCE_ID NUMBER;
758 constraint_stmt LONG ;
759 first_time NUMBER := 0;
760 bind_list BIND_TABLE;
761 cnt NUMBER;
762 error_sequence_number NUMBER :=0;
763 stmt_num NUMBER := 0;
764 ret_code NUMBER :=0;
765 cursor_name INTEGER;
766 rows_processed INTEGER;
767
768 BEGIN
769
770
771 INVENTORY_ITEM_ID := token_list.inventory_item_id;
772 ORGANIZATION_ID := token_list.organization_id;
773 BILL_SEQUENCE_ID := token_list.bill_sequence_id;
774 ROUTING_SEQUENCE_ID := token_list.routing_sequence_id;
775 COMPONENT_SEQUENCE_ID := token_list.component_sequence_id;
776 OPERATION_SEQUENCE_ID := token_list.operation_sequence_id;
777
778 stmt_num :=1;
779 OPEN constraint_cursor(delete_entity_type);
780
781 LOOP
782 FETCH constraint_cursor INTO cur_rec ;
783 EXIT WHEN constraint_cursor%NOTFOUND;
784
785 stmt_num := 2;
786 /*
787 ** do the consolidation here for items and bill. Set a savepoint here
788 ** so that when constraint failure, then rollback consolidation
789 */
790 if ((delete_entity_type = 1 OR delete_entity_type = 2 OR
791 delete_entity_type = 3) AND first_time = 0)THEN
792
793 stmt_num := 3;
794 SAVEPOINT consolidate;
795 first_time := 1;
796
797 if (config_item_consolidate( inventory_item_id,organization_id,
798 delete_entity_type, err_text) <> 0 )THEN
799
800 return (FATAL_ERROR);
801 end if; /* if */
802 END IF; /* if */
803 /*
804 ** check to see if sql stmt was truncated, if so then allocate and
805 ** retrieve again
806 */
807 stmt_num := 4;
808 SELECT SQL_STATEMENT
809 INTO constraint_stmt
810 FROM BOM_DELETE_SQL_STATEMENTS
811 WHERE SQL_STATEMENT_NAME = cur_rec.stmt_name;
812
813 constraint_stmt := UPPER(constraint_stmt);
814 if(p_debug = 'Y')then
815 fnd_file.put_line (Which => FND_FILE.LOG,
816 buff => constraint_stmt);
817 end if;
818
819 /*
820 ** check to see if the first word in the statement is other than
821 ** select. In which case, this constraint should not be executed
822 */
823 stmt_num := 5;
824 if ( instr(constraint_stmt,'SELECT') =0) THEN
825 return(FATAL_ERROR);
826 end if;
827
828
829 stmt_num := 6;
830 if (substitute_tokens(token_list, constraint_stmt, bind_list ,err_text)<> 0) THEN
831 return 2;
832 else
833
834 if(p_debug = 'Y')then
835 fnd_file.put_line (Which => FND_FILE.LOG,
836 buff => constraint_stmt);
837 end if;
838 stmt_num := 7;
839 begin
840 cursor_name := dbms_sql.open_cursor;
841 DBMS_SQL.PARSE(cursor_name, constraint_stmt,dbms_sql.native);
842 dbms_sql.define_column(cursor_name, 1, cnt);
843 for i in 1..bind_list.COUNT loop
844
845 DBMS_SQL.BIND_VARIABLE(cursor_name, bind_list(i).bind_name, bind_list(i).bind_value);
846
847 end loop;
848 rows_processed := dbms_sql.execute_and_fetch(cursor_name);
849 dbms_sql.column_value(cursor_name, 1, cnt);
850 dbms_sql.close_cursor(cursor_name);
851 exception
852 when no_data_found then
853 cnt := 0;
854 dbms_sql.close_cursor(cursor_name);
855 when others then
856 if(p_debug = 'Y')then
857 fnd_file.put_line ( Which => FND_FILE.LOG,
858 buff => SUBSTRB(SQLERRM,1,500) );
859 end if;
860 DBMS_SQL.close_cursor(cursor_name);
861 end;
862
863 if(p_debug = 'Y')then
864 fnd_file.put_line (Which => FND_FILE.LOG,
865 buff => ' No of Rows = '||to_char(cnt));
866 end if;
867 if (cnt =0 AND cur_rec.delete_on_success_flag = 1) OR
868 (cnt <> 0 and cur_rec.delete_on_success_flag =2) then
869 error_sequence_number := error_sequence_number +1;
870 INSERT INTO BOM_DELETE_ERRORS (
871 DELETE_ENTITY_SEQUENCE_ID,
872 COMPONENT_SEQUENCE_ID,
873 OPERATION_SEQUENCE_ID,
874 ERROR_SEQUENCE_NUMBER,
875 SQL_STATEMENT_NAME,
876 LAST_UPDATE_DATE,
877 LAST_UPDATED_BY,
878 CREATION_DATE,
879 CREATED_BY)
880 VALUES (
881 delete_seq_id,
882 component_sequence_id,
883 operation_sequence_id,
884 error_sequence_number,
885 cur_rec.stmt_name,
886 SYSDATE,
887 user_id,
888 SYSDATE,
889 user_id);
890 ret_code := 1;
891 end if;
892 end if;
893 END LOOP;
894 if constraint_cursor%ISOPEN then
895 close constraint_cursor;
896 end if;
897
898 return ret_code;
899
900 EXCEPTION
901 WHEN others THEN
902 err_text := err_text||'const_checker '||stmt_num||' '||substrb(SQLERRM,1,500);
903 RETURN SQLCODE;
904
905 END;
906
907
908
909 /*****************************************************************
910 * FUNCTION : substitute_tokens
911 * Parameters IN : token_list -TOKEN_RECORD, stmt -LONG
912 * Parameters OUT: stmt - LONG bind_list -BIND_TABLE, err_text
913 * return : 0 -success , other - SQL Exception
914 * Purpose : This function will replace '&' with ':'.
915 * and capture all the bind parameters used in the statement.
916 ******************************************************************/
917 FUNCTION substitute_tokens ( token_list IN TOKEN_RECORD,
918 stmt IN OUT NOCOPY LONG,
919 bind_list OUT NOCOPY BIND_TABLE,
920 err_text OUT NOCOPY varchar2) return NUMBER is
921
922 i NUMBER :=1;
923 stmt_num NUMBER := 0;
924 BEGIN
925 stmt_num :=1;
926 stmt := UPPER(stmt);
927
928 stmt_num := 2;
929 stmt := replace(stmt,'&',':');
930
931 stmt_num := 3;
932 if( INSTR (stmt,':BILL_SEQUENCE_ID',1,1) <>0 ) then
933 bind_list(i).bind_name := 'BILL_SEQUENCE_ID';
934 bind_list(i).bind_value := token_list.bill_sequence_id;
935 i := i+1;
936 end if;
937 stmt_num := 4;
938 if (INSTR (stmt,':ROUTING_SEQUENCE_ID',1,1) <>0 ) then
939 bind_list(i).bind_name := 'ROUTING_SEQUENCE_ID';
940 bind_list(i).bind_value := token_list.routing_sequence_id;
941 i := i+1;
942 end if;
943 stmt_num := 5;
944 if (INSTR (stmt,':ITEM_ID',1,1) <>0) then
945 bind_list(i).bind_name := 'ITEM_ID';
946 bind_list(i).bind_value := token_list.inventory_item_id;
947 i := i+1;
948 end if;
949 stmt_num := 4;
950 if (INSTR (stmt,':COMPONENT_SEQUENCE_ID',1,1) <>0) then
951 bind_list(i).bind_name := 'COMPONENT_SEQUENCE_ID';
952 bind_list(i).bind_value := token_list.component_sequence_id;
953 i := i+1;
954 end if;
955 stmt_num := 5;
956 if (INSTR (stmt,':OPERATION_SEQUENCE_ID',1,1) <>0) then
957 bind_list(i).bind_name := 'OPERATION_SEQUENCE_ID';
958 bind_list(i).bind_value := token_list.operation_sequence_id;
959 i := i+1;
960 end if;
961 stmt_num := 6;
962 if (INSTR (stmt,':ORGANIZATION_ID',1,1) <>0) then
963 bind_list(i).bind_name := 'ORGANIZATION_ID';
964 bind_list(i).bind_value := token_list.organization_id;
965 i := i+1;
966 end if;
967
968 stmt_num := 7;
969 --bug:5546629 Added support for token DEL_GROUP_SEQ_ID
970 if (INSTR (stmt,':DEL_GROUP_SEQ_ID',1,1) <>0) then
971 bind_list(i).bind_name := 'DEL_GROUP_SEQ_ID';
972 bind_list(i).bind_value := token_list.del_group_seq_id;
973 i := i+1;
974 end if;
975
976 stmt_num := 8;
977 --bug:5546629 Added support for token COMP_ITEM_ID
978 if (INSTR (stmt,':COMPONENT_ITEM_ID',1,1) <>0) then
979 bind_list(i).bind_name := 'COMPONENT_ITEM_ID';
980 bind_list(i).bind_value := token_list.component_item_id;
981 i := i+1;
982 end if;
983
984 return 0;
985
986 EXCEPTION
987 WHEN others THEN
988 err_text := err_text||'sub_token '||stmt_num||' '||substrb(SQLERRM,1,500);
989 RETURN SQLCODE;
990
991 END;
992
993
994
995
996 /*****************************************************************
997 * FUNCTION : config_item_consolidate
998 * Parameters IN : inventory_item_id,organization_id,delete_entity_type
999 * Parameters OUT: err_text
1000 * return : 0 -success ,2-error, other - SQL Exception
1001 * Purpose : Configuration Item Purge - Consolidate Item
1002 ******************************************************************/
1003 FUNCTION config_item_consolidate( p_inventory_item_id IN NUMBER,
1004 p_organization_id IN NUMBER,
1005 p_delete_entity_type IN NUMBER,
1006 err_text OUT NOCOPY VARCHAR2) return NUMBER is
1007
1008 config_flag VARCHAR2(1) := ' ';
1009 delete_status VARCHAR2(10) := ' ';
1010 item_status VARCHAR2(10) := ' ';
1011 base_id NUMBER := 0;
1012 job_count NUMBER := 0;
1013 stmt_num NUMBER :=0;
1014
1015 BEGIN
1016 stmt_num := 1;
1017 if(p_debug = 'Y')then
1018 fnd_file.put_line (Which => FND_FILE.LOG,
1019 buff => 'inventory_item_id:'||to_char(p_inventory_item_id));
1020 fnd_file.put_line (Which => FND_FILE.LOG,
1021 buff => 'organization_id:'||to_char(p_organization_id));
1022
1023 fnd_file.put_line (Which => FND_FILE.LOG,
1024 buff => 'delete_entity_type:'||to_char(p_delete_entity_type));
1025 end if;
1026
1027 select MSI.AUTO_CREATED_CONFIG_FLAG,
1028 MSI.INVENTORY_ITEM_STATUS_CODE,
1029 MSI.BASE_ITEM_ID
1030 into config_flag,
1031 item_status,
1032 base_id
1033 from MTL_SYSTEM_ITEMS MSI
1034 where MSI.ORGANIZATION_ID = p_organization_id
1035 and MSI.INVENTORY_ITEM_ID = p_inventory_item_id;
1036
1037 stmt_num := 2;
1038 IF config_flag = 'Y' THEN
1039 BEGIN
1040 select BP.BOM_DELETE_STATUS_CODE
1041 into delete_status
1042 from BOM_PARAMETERS BP
1043 where BP.ORGANIZATION_ID = p_organization_id;
1044 stmt_num := 3;
1045
1046 IF item_status = delete_status THEN
1047 BEGIN
1048 IF base_id <> p_inventory_item_id and base_id IS NOT NULL THEN
1049 BEGIN
1050 job_count := 0;
1051 IF p_delete_entity_type in (2,3) THEN /* bill or routing */
1052 BEGIN
1053 stmt_num := 4;
1054 select count(*)
1055 into job_count
1056 from WIP_DISCRETE_JOBS WDJ
1057 where WDJ.ORGANIZATION_ID = p_organization_id
1058 and WDJ.PRIMARY_ITEM_ID = p_inventory_item_id
1059 and WDJ.STATUS_TYPE <> 12 /*Closed-no charges allowed*/
1060 and rownum = 1; /* get just the first one that */
1061 /* isn't closed */
1062 IF job_count = 0 then /* all were closed */
1063 BEGIN
1064 stmt_num := 5;
1065 update WIP_ENTITIES WE
1066 set WE.PRIMARY_ITEM_ID = base_id
1067 where WE.ORGANIZATION_ID =p_organization_id
1068 and WE.PRIMARY_ITEM_ID = p_inventory_item_id;
1069
1070 stmt_num := 6;
1071 update WIP_DISCRETE_JOBS WDJ
1072 set WDJ.PRIMARY_ITEM_ID = base_id,
1073 WDJ.ALTERNATE_BOM_DESIGNATOR = NULL,
1074 WDJ.ALTERNATE_ROUTING_DESIGNATOR = NULL
1075 where WDJ.ORGANIZATION_ID = p_organization_id
1076 and WDJ.PRIMARY_ITEM_ID = p_inventory_item_id;
1077
1078 stmt_num := 7;
1079 update WIP_MOVE_TRANSACTIONS WMT
1080 set WMT.PRIMARY_ITEM_ID = base_id
1081 where WMT.ORGANIZATION_ID = p_organization_id
1082 and WMT.PRIMARY_ITEM_ID = p_inventory_item_id;
1083
1084 stmt_num := 8;
1085 update WIP_MOVE_TXN_INTERFACE WMTI
1086 set WMTI.PRIMARY_ITEM_ID = base_id
1087 where WMTI.ORGANIZATION_ID = p_organization_id
1088 and WMTI.PRIMARY_ITEM_ID = p_inventory_item_id;
1089
1090 stmt_num := 9;
1091 update WIP_REQUIREMENT_OPERATIONS WRO
1092 set WRO.INVENTORY_ITEM_ID = base_id
1093 where WRO.ORGANIZATION_ID = p_organization_id
1094 and WRO.INVENTORY_ITEM_ID = p_inventory_item_id;
1095
1096 stmt_num := 10;
1097 update WIP_COST_TXN_INTERFACE WCTI
1098 set WCTI.PRIMARY_ITEM_ID = base_id
1099 where WCTI.ORGANIZATION_ID = p_organization_id
1100 and WCTI.PRIMARY_ITEM_ID = p_inventory_item_id;
1101
1102 stmt_num := 11;
1103 update WIP_TRANSACTIONS WT
1104 set WT.PRIMARY_ITEM_ID = base_id
1105 where WT.ORGANIZATION_ID = p_organization_id
1106 and WT.PRIMARY_ITEM_ID = p_inventory_item_id;
1107
1108 END;
1109 END IF; /* if job status is closed */
1110 END;
1111 END IF; /* if bill or routing type */
1112 IF p_delete_entity_type = 1 THEN /* item */
1113 BEGIN
1114 stmt_num := 12;
1115 update MTL_MATERIAL_TRANSACTIONS MT
1116 set MT.INVENTORY_ITEM_ID = base_id
1117 where MT.ORGANIZATION_ID = p_organization_id
1118 and MT.INVENTORY_ITEM_ID = p_inventory_item_id;
1119
1120 stmt_num := 13;
1121 update MTL_TRANSACTION_LOT_NUMBERS MTLN
1122 set MTLN.INVENTORY_ITEM_ID = base_id
1123 where MTLN.ORGANIZATION_ID = p_organization_id
1124 and MTLN.INVENTORY_ITEM_ID = p_inventory_item_id;
1125
1126 stmt_num := 14;
1127 update MTL_UNIT_TRANSACTIONS MUT
1128 set MUT.INVENTORY_ITEM_ID = base_id
1129 where MUT.ORGANIZATION_ID = p_organization_id
1130 and MUT.INVENTORY_ITEM_ID = p_inventory_item_id;
1131 stmt_num := 15;
1132
1133 update MTL_TRANSACTION_ACCOUNTS MTA
1134 set MTA.INVENTORY_ITEM_ID = base_id
1135 where MTA.ORGANIZATION_ID = p_organization_id
1136 and MTA.INVENTORY_ITEM_ID = p_inventory_item_id;
1137 END;
1138 END IF; /* if item type */
1139
1140 END;
1141 END IF; /* if base_id <> item_id */
1142 END;
1143 END IF; /* if status matches */
1144 END;
1145 END IF; /* If config item */
1146 return(0); /* Set status to OK */
1147
1148 EXCEPTION
1149 WHEN NO_DATA_FOUND THEN /* Don't try to consolidate, return true status */
1150 return (0);
1151 WHEN OTHERS THEN
1152 err_text := err_text||'config_item_consolidate'||stmt_num||' '||substrb(SQLERRM,1,500);
1153 RETURN SQLCODE;
1154 END;
1155
1156 /*****************************************************************
1157 * FUNCTION : execute_delete
1158 * Parameters IN :token_list-TOKEN_RECORD, delete_entity_type
1159 * archive_flag
1160 * Parameters OUT: err_text, action_status(4-delete,3-error)
1161 * return : 0 -success , other - SQL Exception
1162 * Purpose : This function executes the delete statements from
1163 * bom_delete_sql_statements table that are valid for given
1164 * delete entity type
1165 ******************************************************************/
1166 FUNCTION execute_delete(delete_entity_type IN NUMBER,
1167 token_list IN Token_Record,
1168 archive_flag IN NUMBER,
1169 action_status OUT NOCOPY NUMBER,
1170 err_text OUT NOCOPY VARCHAR2) return NUMBER is
1171
1172 CURSOR delete_cursor(p_delete_entity_type NUMBER) IS
1173 SELECT sql_statement stmt, ARCHIVE_TABLE_NAME,
1174 length(ARCHIVE_TABLE_NAME) archive_table_length,
1175 SQL_STATEMENT_NAME stmt_name
1176 FROM BOM_DELETE_SQL_STATEMENTS
1177 WHERE SQL_STATEMENT_TYPE = 2
1178 AND ACTIVE_FLAG = 1
1179 AND DELETE_ENTITY_TYPE = p_delete_entity_type
1180 ORDER BY SEQUENCE_NUMBER;
1181
1182 table_name VARCHAR2(80);
1183 where_stmt LONG;
1184 delete_stmt LONG;
1185 bind_list BIND_TABLE;
1186 stmt_num NUMBER :=0;
1187 cursor_name INTEGER;
1188 rows_processed INTEGER;
1189 BEGIN
1190 action_status := 3; -- initially set to Error.
1191 stmt_num := 1;
1192 for cur_rec in delete_cursor(delete_entity_type) LOOP
1193
1194 stmt_num := 2;
1195 SAVEPOINT consolidate;
1196
1197 /*
1198 ** check to see if sql stmt was truncated, if so then allocate and
1199 ** retrieve again
1200 */
1201
1202 stmt_num := 3;
1203
1204 delete_stmt := upper(cur_rec.stmt);
1205
1206 if(p_debug = 'Y')then
1207 fnd_file.put_line (Which => FND_FILE.LOG,
1208 buff => delete_stmt);
1209 end if;
1210 /*
1211 ** check to see if the first word in the statement is other than
1212 ** select. In which case, this constraint should not be executed
1213 */
1214 stmt_num := 4;
1215 if ( instr(delete_stmt,'DELETE') =0) THEN
1216 return(FATAL_ERROR);
1217
1218 end if;
1219
1220 stmt_num := 5;
1221
1222 if (substitute_tokens( token_list,delete_stmt,bind_list,err_text)<>0) THEN
1223 return 2;
1224 else
1225 if(p_debug = 'Y')then
1226 fnd_file.put_line (Which => FND_FILE.LOG,
1227 buff => delete_stmt);
1228 end if;
1229 if (cur_rec.archive_table_length > 0 AND archive_flag = 1) THEN
1230 stmt_num := 6;
1231 table_name := extract_table_name(delete_stmt,err_text);
1232 stmt_num := 7;
1233 where_stmt := extract_where(delete_stmt,err_text);
1234 stmt_num := 8;
1235 if (archive_data(token_list,cur_rec.archive_table_name,
1236 table_name, where_stmt,bind_list, err_text) <> 0) THEN
1237 stmt_num := 9;
1238 ROLLBACK TO SAVEPOINT start_process;
1239 return 1;
1240 end if;
1241 stmt_num := 1;
1242 end if;
1243 end if;
1244
1245
1246 cursor_name := dbms_sql.open_cursor;
1247 DBMS_SQL.PARSE(cursor_name, delete_stmt,dbms_sql.native);
1248 for i in 1..bind_list.COUNT loop
1249
1250
1251 DBMS_SQL.BIND_VARIABLE(cursor_name, bind_list(i).bind_name, bind_list(i).bind_value);
1252
1253 end loop;
1254 rows_processed:= DBMS_SQL.execute(cursor_name);
1255 dbms_sql.close_cursor(cursor_name);
1256 END LOOP;
1257 action_status := 4; -- deleted successfully
1258 return 0;
1259
1260 EXCEPTION
1261 WHEN others THEN
1262 err_text := err_text||'exec_delete '||stmt_num||' '||substrb(SQLERRM,1,500);
1263 ROLLBACK TO SAVEPOINT start_process;
1264 RETURN SQLCODE;
1265
1266
1267 end;
1268
1269 -- bug:5726408 Added support for executing UPDATE statement.
1270 /*****************************************************************
1271 * FUNCTION : execute_update
1272 * Parameters IN : delete_entity_type Type of delete entity
1273 * token_list Records of tokens to be substituted
1274 * Parameters OUT: action_status 4-delete, 3-error
1275 * err_text Error message in case of exception
1276 * return : 0 -success , other - SQL Exception
1277 * Purpose : This function executes the update statements from
1278 * bom_delete_sql_statements table that are valid for given
1279 * delete entity type
1280 ******************************************************************/
1281
1282 FUNCTION execute_update (
1283 delete_entity_type IN NUMBER,
1284 token_list IN Token_Record,
1285 action_status OUT NOCOPY NUMBER,
1286 err_text OUT NOCOPY VARCHAR2) RETURN NUMBER
1287 IS
1288
1289 CURSOR delete_cursor(p_delete_entity_type NUMBER)
1290 IS
1291 SELECT
1292 SQL_STATEMENT stmt,
1293 SQL_STATEMENT_NAME stmt_name
1294 FROM BOM_DELETE_SQL_STATEMENTS
1295 WHERE
1296 SQL_STATEMENT_TYPE = 3
1297 AND ACTIVE_FLAG = 1
1298 AND DELETE_ENTITY_TYPE = p_delete_entity_type
1299 ORDER BY SEQUENCE_NUMBER;
1300
1301 delete_stmt LONG;
1302 bind_list BIND_TABLE;
1303 stmt_num NUMBER := 0;
1304 cursor_name INTEGER;
1305 rows_processed INTEGER;
1306
1307 BEGIN
1308 action_status := 3; -- initially set to Error.
1309
1310 stmt_num := 1;
1311 FOR cur_rec IN delete_cursor ( delete_entity_type )
1312 LOOP
1313
1314 /* check to see if sql stmt was truncated, if so then allocate and
1315 * retrieve again */
1316
1317 stmt_num := 2;
1318 delete_stmt := UPPER(cur_rec.stmt);
1319
1320 IF ( p_debug = 'Y' )
1321 THEN
1322 FND_FILE.put_line ( Which => FND_FILE.LOG,
1323 buff => delete_stmt);
1324 END IF;
1325
1326 /* check to see if the first word in the statement is UPDATE. */
1327 stmt_num := 3;
1328 IF ( INSTR( delete_stmt, 'UPDATE' ) = 0 )
1329 THEN
1330 RETURN( FATAL_ERROR );
1331 END IF;
1332
1333 stmt_num := 4;
1334 IF ( substitute_tokens( token_list, delete_stmt, bind_list, err_text ) <> 0 )
1335 THEN
1336 RETURN 2;
1337 ELSE
1338 IF ( p_debug = 'Y' )
1339 THEN
1340 fnd_file.put_line ( Which => FND_FILE.LOG,
1341 buff => delete_stmt );
1342 END IF;
1343 END IF ;
1344
1345 cursor_name := DBMS_SQL.OPEN_CURSOR;
1346
1347 DBMS_SQL.PARSE( cursor_name, delete_stmt, DBMS_SQL.NATIVE );
1348
1349 FOR i IN 1 .. bind_list.COUNT
1350 LOOP
1351 DBMS_SQL.BIND_VARIABLE( cursor_name, bind_list(i).bind_name, bind_list(i).bind_value );
1352 END LOOP;
1353
1354 rows_processed:= DBMS_SQL.EXECUTE(cursor_name);
1355
1356 DBMS_SQL.CLOSE_CURSOR( cursor_name );
1357
1358 END LOOP; -- end FOR cur_rec IN delete_cursor
1359
1360 action_status := 4; -- deleted successfully
1361 RETURN 0;
1362
1363 EXCEPTION
1364 WHEN OTHERS THEN
1365 err_text := err_text||'exec_update '||stmt_num||' '||substrb(SQLERRM,1,500);
1366 RETURN SQLCODE;
1367
1368 END execute_update;
1369
1370
1371 /*****************************************************************
1372 * FUNCTION : update_op_sequences
1373 * Parameters IN :tdelete_entity_type ,routing_seq_id
1374 * Parameters OUT: err_text
1375 * return : 0 -success , other - SQL Exception
1376 * Purpose : updates op sequences to 1 in BOM_INVENTORY_COMPONENTS
1377 ******************************************************************/
1378
1379 FUNCTION update_op_sequences(delete_entity_type IN NUMBER,
1380 routing_seq_id IN NUMBER,
1381 op_seq_id IN NUMBER,
1382 err_text OUT NOCOPY varchar2) return NUMBER is
1383 stmt_num NUMBER := 0;
1384
1385 BEGIN
1386 if (delete_entity_type = 3) THEN/* routing delete */
1387 stmt_num := 1;
1388 UPDATE BOM_INVENTORY_COMPONENTS
1389 SET OPERATION_SEQ_NUM = 1
1390 WHERE BILL_SEQUENCE_ID = (SELECT BILL_SEQUENCE_ID
1391 FROM BOM_BILL_OF_MATERIALS BOM,
1392 BOM_OPERATIONAL_ROUTINGS BOR
1393 WHERE BOR.ROUTING_SEQUENCE_ID = routing_seq_id
1394 AND BOR.ORGANIZATION_ID = BOM.ORGANIZATION_ID
1395 AND BOR.ASSEMBLY_ITEM_ID = BOM.ASSEMBLY_ITEM_ID
1396 AND nvl(BOR.ALTERNATE_ROUTING_DESIGNATOR, 'NONE') =
1397 nvl(BOM.ALTERNATE_BOM_DESIGNATOR, 'NONE'));
1398 else /* operation delete */
1399 stmt_num := 2;
1400 UPDATE BOM_INVENTORY_COMPONENTS BIC
1401 SET OPERATION_SEQ_NUM = 1
1402 WHERE BILL_SEQUENCE_ID = (SELECT BILL_SEQUENCE_ID
1403 FROM BOM_BILL_OF_MATERIALS BOM,
1404 BOM_OPERATIONAL_ROUTINGS BOR
1405 WHERE BOR.ROUTING_SEQUENCE_ID = routing_seq_id
1406 AND BOR.ORGANIZATION_ID = BOM.ORGANIZATION_ID
1407 AND BOR.ASSEMBLY_ITEM_ID = BOM.ASSEMBLY_ITEM_ID
1408 AND nvl(BOR.ALTERNATE_ROUTING_DESIGNATOR, 'NONE') =
1409 nvl(BOM.ALTERNATE_BOM_DESIGNATOR, 'NONE'))
1410 AND OPERATION_SEQ_NUM = (SELECT OPERATION_SEQ_NUM
1411 FROM BOM_OPERATION_SEQUENCES
1412 WHERE OPERATION_SEQUENCE_ID = op_seq_id);
1413 end if;
1414
1415 return 0;
1416 EXCEPTION
1417 WHEN others THEN
1418 err_text := err_text||'update_op_sequences'||stmt_num||' '||substrb(SQLERRM,1,500);
1419 RETURN SQLCODE;
1420
1421 END;
1422
1423 /*****************************************************************
1424 * FUNCTION : archive_data
1425 * Parameters IN :token_list-TOKEN_RECORD, archive table name,
1426 * product table name , where clause.
1427 * Parameters OUT: err_text
1428 * return : 0 -success ,2-error, other - SQL Exception
1429 * Purpose : archive the data from product table.
1430 ******************************************************************/
1431
1432 FUNCTION archive_data( token_list IN Token_Record,
1433 insert_table IN varchar2,
1434 table_name IN varchar2,
1435 where_clause IN varchar2,
1436 bind_list IN BIND_TABLE,
1437 err_text OUT NOCOPY varchar2) return NUMBER is
1438
1439 l_schema VARCHAR2(30);
1440 l_status VARCHAR2(1);
1441 l_industry VARCHAR2(1);
1442 l_oracleUser VARCHAR2(30);
1443
1444 CURSOR col_list_cursor( prod_table VARCHAR2,
1445 schema_name VARCHAR2,
1446 oracle_user VARCHAR2) IS
1447 SELECT distinct ATC.COLUMN_NAME COLUMN_NAME
1448 FROM ALL_TAB_COLUMNS ATC,
1449 ALL_OBJECTS AO
1450 WHERE TABLE_NAME = trim(prod_table)
1451 AND ( ( AO.OBJECT_TYPE = 'TABLE' AND ATC.OWNER = schema_name )
1452 OR ( AO.OBJECT_TYPE = 'VIEW' AND ATC.OWNER = oracle_user ) )
1453 AND AO.OBJECT_NAME = trim(prod_table)
1454 AND AO.OWNER = ATC.OWNER
1455 ORDER BY COLUMN_NAME;
1456 insert_stmt LONG;
1457 update_stmt LONG;
1458 archive_table varchar2(80);
1459 prod_table varchar2(80);
1460 column_name VARCHAR2 (80);
1461 dummy NUMBER :=0;
1462 req_id NUMBER :=-1;
1463 prog_id NUMBER :=-1;
1464 stmt_num NUMBER:=0;
1465 cursor_name INTEGER;
1466 rows_processed INTEGER;
1467 l_app_short_name VARCHAR2(10);
1468
1469 BEGIN
1470 /*
1471 ** update the standard who columns before archiving the data. Need to
1472 ** do it this way, since if I try to update after archiving, then I don't
1473 ** know which rows were updated. If for some reason there is a failure
1474 ** then it rollsback the updates anyways
1475 */
1476
1477
1478 stmt_num := 0;
1479 req_id := nvl(FND_PROFILE.value('CONC_REQUEST_ID'),-1);
1480 prog_id := nvl(FND_PROFILE.value('CONC_PROGRAM_ID'),-1);
1481 resp_appl_id := nvl(FND_PROFILE.value('RESP_APPL_ID'),-1);
1482
1483 stmt_num := 1;
1484 update_stmt:= 'UPDATE' || table_name || ' SET REQUEST_ID = ' || req_id
1485 ||', PROGRAM_ID = ' || prog_id ||', PROGRAM_APPLICATION_ID =' || resp_appl_id
1486 ||', PROGRAM_UPDATE_DATE = sysdate ';
1487
1488 if(p_debug = 'Y')then
1489 fnd_file.put_line (Which => FND_FILE.LOG,
1490 buff => update_stmt);
1491 end if;
1492 update_stmt := update_stmt ||' WHERE '||where_clause;
1493 stmt_num := 2;
1494 cursor_name := dbms_sql.open_cursor;
1495 DBMS_SQL.PARSE(cursor_name, update_stmt,dbms_sql.native);
1496 for i in 1..bind_list.COUNT loop
1497 DBMS_SQL.BIND_VARIABLE(cursor_name, bind_list(i).bind_name, bind_list(i).bind_value);
1498 end loop;
1499 rows_processed:= DBMS_SQL.execute(cursor_name);
1500 dbms_sql.close_cursor(cursor_name);
1501 stmt_num := 3;
1502 archive_table := insert_table;
1503 prod_table := table_name;
1504 /*
1505 Begin
1506 SELECT 1
1507 INTO dummy
1508 FROM DUAL
1509 WHERE EXISTS (
1510 SELECT NULL
1511 FROM ALL_TAB_COLUMNS COL1
1512 WHERE TABLE_NAME = trim(prod_table)
1513 AND NOT EXISTS (
1514 SELECT NULL
1515 FROM ALL_TAB_COLUMNS COL2
1516 WHERE TABLE_NAME = trim(archive_table)
1517 AND COL2.COLUMN_NAME = COL1.COLUMN_NAME));
1518 exception
1519 when no_data_found then
1520 dummy :=0;
1521 when others then
1522 dummy :=2;
1523 end;
1524 stmt_num := 4;
1525 if (dummy <> 0) then
1526 -- archive table structure does not match production table structure
1527 fnd_message.set_name('BOM', 'BOM_ARCHIVE_TOO_OLD');
1528 err_text := fnd_message.get;
1529 return(2);
1530 end if;
1531 */
1532 SELECT
1533 ORACLE_USERNAME INTO l_oracleUser
1534 FROM
1535 FND_ORACLE_USERID
1536 WHERE
1537 READ_ONLY_FLAG = 'U';
1538
1539 --Bug No: 4248530. When the prod table is in some schema other than BOM then we need
1540 -- to get the schema name Ex: MTL_RTG_ITEM_REVISIONS.
1541 l_app_short_name := 'BOM';
1542 IF(INSTR(trim(prod_table), 'MTL',1,1) = 1) THEN
1543 l_app_short_name := 'INV';
1544 END IF;
1545
1546 IF NOT FND_INSTALLATION.GET_APP_INFO(l_app_short_name, l_status, l_industry, l_schema)
1547 THEN
1548 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1549 END IF;
1550
1551 IF (l_schema IS NULL OR l_oracleUser IS NULL)
1552 THEN
1553 stmt_num := 4;
1554 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1555 END IF;
1556
1557 stmt_num := 5;
1558 insert_stmt := insert_stmt || ' INSERT INTO '||insert_table || '( ';
1559
1560 IF (p_debug = 'Y') THEN
1561 fnd_file.put_line (Which => FND_FILE.LOG, buff => ('Schema Name:' || l_schema));
1562 fnd_file.put_line (Which => FND_FILE.LOG, buff => ('Oracle User:' || l_oracleUser));
1563 fnd_file.put_line (Which => FND_FILE.LOG, buff => ('Prod Table:' || prod_table));
1564 END IF;
1565
1566 for col_list in col_list_cursor(prod_table, l_schema, l_oracleUser) loop
1567 insert_stmt := insert_stmt || col_list.column_name || ' , ';
1568 END LOOP;
1569
1570 stmt_num := 6;
1571 insert_stmt:= trim (insert_stmt);
1572 insert_stmt := substr(insert_stmt,1,length(insert_stmt)-1 );
1573 insert_stmt := insert_stmt || ' ) SELECT ';
1574
1575 for col_list in col_list_cursor(prod_table, l_schema, l_oracleUser) loop
1576 insert_stmt := insert_stmt || col_list.column_name || ' , ';
1577 END LOOP;
1578
1579 insert_stmt := trim (insert_stmt);
1580 insert_stmt := substr(insert_stmt,1,length(insert_stmt)-1 );
1581 stmt_num := 7;
1582 insert_stmt := insert_stmt || ' FROM '|| prod_table || ' WHERE ' ||where_clause;
1583
1584 stmt_num := 8;
1585 if(p_debug = 'Y')then
1586 fnd_file.put_line (Which => FND_FILE.LOG,
1587 buff => insert_stmt);
1588 end if;
1589 cursor_name := dbms_sql.open_cursor;
1590 DBMS_SQL.PARSE(cursor_name, insert_stmt,dbms_sql.native);
1591 for i in 1..bind_list.COUNT loop
1592
1593 DBMS_SQL.BIND_VARIABLE(cursor_name, bind_list(i).bind_name, bind_list(i).bind_value);
1594
1595 end loop;
1596 rows_processed:= DBMS_SQL.execute(cursor_name);
1597 dbms_sql.close_cursor(cursor_name);
1598 return 0;
1599
1600 EXCEPTION
1601 WHEN others THEN
1602 err_text := err_text||'archive_data'||stmt_num||' '||substrb(SQLERRM,1,500);
1603 RETURN SQLCODE;
1604
1605 END;
1606
1607 /*****************************************************************
1608 * FUNCTION : extract_table_name
1609 * Parameters IN :stmt-LONG
1610 * return : Table name present in the statement
1611 ******************************************************************/
1612
1613 FUNCTION extract_table_name ( stmt IN LONG, err_text OUT NOCOPY VARCHAR2) return LONG is
1614 position1 NUMBER;
1615 position2 NUMBER;
1616 ret_value LONG;
1617 stmt_num NUMBER;
1618 BEGIN
1619 stmt_num :=1;
1620 position1 := instr (stmt, 'DELETE',1) +6;
1621 stmt_num := 2;
1622 position2 := instr(stmt,' WHERE',1);
1623 stmt_num :=3;
1624 ret_value := substr( stmt, position1, position2-position1);
1625 stmt_num :=4;
1626 ret_value := replace(ret_value,'FROM',' ');
1627 stmt_num := 5;
1628 return ret_value;
1629 EXCEPTION
1630 WHEN others THEN
1631 err_text := err_text||'extract_table_name'||stmt_num||' '||substrb(SQLERRM,1,500);
1632 RETURN SQLCODE;
1633 END;
1634 /*****************************************************************
1635 * FUNCTION : extract_table_name
1636 * Parameters IN :stmt-LONG
1637 * return : where clause of the statement
1638 ******************************************************************/
1639 FUNCTION extract_where (stmt IN LONG, err_text OUT NOCOPY VARCHAR2) return LONG is
1640 position1 NUMBER;
1641 stmt_num NUMBER;
1642
1643 BEGIN
1644 stmt_num :=1;
1645 position1 := instr(stmt,' WHERE',1);
1646 stmt_num :=2;
1647 return substr( stmt, position1+6);
1648 EXCEPTION
1649 WHEN others THEN
1650 err_text := err_text||'extract_where '||stmt_num||' '||substrb(SQLERRM,1,500);
1651 RETURN SQLCODE;
1652 END;
1653 /*****************************************************************
1654 * FUNCTION : write_log
1655 * Parameters IN : alt_desg ,org_id,item_name ,comp_name ,
1656 * eff_date,op_seq,delete_type.
1657 * Parameters OUT: err_text
1658 * return : 0 -success , other - SQL Exception
1659 * Purpose : This function will write to conc-log
1660 ******************************************************************/
1661 FUNCTION write_log(alt_desg IN VARCHAR2,
1662 org_name IN VARCHAR2,
1663 item_name IN VARCHAR2,
1664 comp_name IN VARCHAR2,
1665 eff_date IN VARCHAR2,
1666 op_seq IN NUMBER,
1667 delete_type IN NUMBER,
1668 err_text OUT NOCOPY VARCHAR2) return NUMBER is
1669 err_text1 varchar2(2000);
1670 err_text2 varchar2(2000);
1671 stmt_num NUMBER;
1672
1673 begin
1674 stmt_num := 1;
1675 if(delete_type = 1)then
1676 /* item delete */
1677 Fnd_Message.set_name('BOM', 'BOM_ITEM_DELETED');
1678 Fnd_Message.set_token('ORG', org_name);
1679 Fnd_Message.set_token('ITEM', item_name);
1680 err_text1 := Fnd_Message.get;
1681 elsif(delete_type =2 )then
1682
1683 /* bill delete */
1684 Fnd_Message.set_name('BOM', 'BOM_BILL_DELETED');
1685 Fnd_Message.set_token('ORG', org_name);
1686 Fnd_Message.set_token('ITEM', item_name);
1687 Fnd_Message.set_token('ALTERNATE', alt_desg);
1688 err_text1 := Fnd_Message.get;
1689 elsif(delete_type =3 )then
1690 /* routing delete */
1691 Fnd_Message.set_name('BOM', 'BOM_ROUTING_DELETED');
1692 Fnd_Message.set_token('ORG', org_name);
1693 Fnd_Message.set_token('ITEM', item_name);
1694 Fnd_Message.set_token('ALTERNATE', alt_desg);
1695 elsif(delete_type =4 )then
1696 /* component delete */
1697 Fnd_Message.set_name('BOM', 'BOM_COMPONENT_DELETED1');
1698 Fnd_Message.set_token('ORG', org_name);
1699 Fnd_Message.set_token('ITEM', item_name);
1700 Fnd_Message.set_token('ALTERNATE', alt_desg);
1701 err_text1 := Fnd_Message.get;
1702 Fnd_Message.set_name('BOM', 'BOM_COMPONENT_DELETED2');
1703 Fnd_Message.set_token('COMPONENT', comp_name);
1704 Fnd_Message.set_token('OP', op_seq);
1705 Fnd_Message.set_token('EFFDATE', eff_date);
1706 err_text2 := Fnd_Message.get;
1707 elsif(delete_type =5 )then
1708 /* operation delete */
1709 Fnd_Message.set_name('BOM', 'BOM_OPERATION_DELETED1');
1710 Fnd_Message.set_token('ORG', org_name);
1711 Fnd_Message.set_token('ITEM', item_name);
1712 Fnd_Message.set_token('ALTERNATE', alt_desg);
1713 err_text1 := Fnd_Message.get;
1714 Fnd_Message.set_name('BOM', 'BOM_OPERATION_DELETED2');
1715 Fnd_Message.set_token('COMPONENT', comp_name);
1716 Fnd_Message.set_token('OP', op_seq);
1717 Fnd_Message.set_token('EFFDATE', eff_date);
1718 err_text2 := Fnd_Message.get;
1719 end if;
1720 if err_text1 IS NOT NULL THEN
1721 fnd_file.put_line (Which => FND_FILE.LOG,
1722 buff => err_text1);
1723 else
1724
1725 if err_text2 is not null then
1726 fnd_file.put_line (Which => FND_FILE.LOG,
1727 buff => err_text2);
1728
1729 end if;
1730 end if;
1731 return 0;
1732 EXCEPTION
1733 WHEN others THEN
1734 err_text := err_text||'write_log'||stmt_num||' '||substrb(SQLERRM,1,500);
1735 RETURN SQLCODE;
1736
1737 end;
1738
1739 /*****************************************************************
1740 * FUNCTION : invoke_events
1741 * Parameters IN : ction_type ,org_id,inv_id ,alternate,structure type
1742 * bill_id, comp_id,delete_type
1743 * Parameters OUT: err_text
1744 * return : 0 -success ,other - SQL Exception
1745 * Purpose : This function will invokde different Business Events
1746 * depending on the parameters passed.
1747 ******************************************************************/
1748 FUNCTION invoke_events( p_action_type IN NUMBER,
1749 p_org_id IN NUMBER,
1750 p_assembly_id IN NUMBER,
1751 p_alternate IN VARCHAR2,
1752 p_item_name VARCHAR2,
1753 p_description VARCHAR2,
1754 p_bill_id IN NUMBER,
1755 p_comp_id IN NUMBER,
1756 p_delete_type IN NUMBER,
1757 err_text OUT NOCOPY VARCHAR2) return NUMBER is
1758 l_ret_status varchar2(1);
1759 l_org_code varchar2(30);
1760 l_master_org_flag varchar2(1);
1761 stmt_num NUMBER:=1;
1762 begin
1763
1764 if (p_action_type =4) then
1765
1766 if (p_delete_type = 1) then
1767
1768 /* Call IP api */ -- bug 4323967
1769
1770 IF (BOM_VALIDATE.Object_Exists(
1771 p_object_type => 'PACKAGE',
1772 p_object_name => 'ICX_CAT_POPULATE_MI_GRP') = 'Y') THEN
1773
1774 SELECT DECODE(master_organization_id, p_org_id, 'Y', 'N'), organization_code
1775 INTO l_master_org_flag, l_org_code
1776 FROM MTL_PARAMETERS
1777 WHERE organization_id = p_org_id;
1778
1779 stmt_num := 2;
1780
1781 EXECUTE IMMEDIATE
1782 ' BEGIN '||
1783 ' ICX_CAT_POPULATE_MI_GRP.populateItemChange ( '||
1784 ' P_API_VERSION => 1.0 '||
1785 ' ,P_COMMIT => FND_API.G_FALSE '||
1786 ' ,P_INIT_MSG_LIST => FND_API.G_FALSE '||
1787 ' ,P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL '||
1788 ' ,P_DML_TYPE => ''DELETE'' '||
1789 ' , P_INVENTORY_ITEM_ID =>:p_assembly_id '||
1790 ' , P_ITEM_NUMBER =>:p_item_name '||
1791 ' , P_ORGANIZATION_ID =>:p_org_id '||
1792 ' , P_ORGANIZATION_CODE =>:l_org_code '||
1793 ' , P_MASTER_ORG_FLAG =>:l_master_org_flag '||
1794 ' , P_ITEM_DESCRIPTION =>:p_description '||
1795 ' ,X_RETURN_STATUS => :l_ret_status ); '||
1796 ' END;'
1797 USING IN p_assembly_id, IN p_item_name, IN p_org_id, IN l_org_code, IN l_master_org_flag , IN p_description ,OUT l_ret_status;
1798
1799 END IF; --BOM_VALIDATE.Object_Exists for ICX_CAT_POPULATE_MI_GRP
1800
1801 --Now calling EGO code that will cancel any NIRs that exist for the deleted item.
1802 --Bug 5526375
1803 IF (BOM_VALIDATE.Object_Exists(
1804 p_object_type => 'PACKAGE',
1805 p_object_name => 'EGO_COMMON_PVT') = 'Y') THEN
1806
1807 IF (BOM_VALIDATE.Object_Exists(
1808 p_object_type => 'PACKAGE',
1809 p_object_name => 'ENG_NIR_UTIL_PKG') = 'Y') THEN
1810
1811 stmt_num := 3;
1812
1813 EXECUTE IMMEDIATE
1814 ' BEGIN '||
1815 ' EGO_COMMON_PVT.CANCEL_NIR_FOR_DELETE_ITEM( '||
1816 ' P_INVENTORY_ITEM_ID =>:p_assembly_id '||
1817 ' , P_ORGANIZATION_ID =>:p_org_id '||
1818 ' , P_ITEM_NUMBER =>:p_item_name ); '||
1819 ' END;'
1820 USING IN p_assembly_id, IN p_org_id, IN p_item_name;
1821
1822 END IF; --BOM_VALIDATE.Object_Exists for ENG_NIR_UTIL_PKG
1823
1824 END IF; --BOM_VALIDATE.Object_Exists for EGO_COMMON_PVT
1825
1826
1827 Bom_Business_Event_PKG.Raise_Item_Event
1828 ( p_Inventory_Item_Id => p_assembly_id
1829 ,p_Organization_Id => p_org_id
1830 ,p_item_name => p_item_name
1831 ,p_item_description => p_description
1832 ,p_Event_Name => Bom_Business_Event_PKG.G_ITEM_DEL_SUCCESS_EVENT);
1833
1834 elsif (p_delete_type =2 ) then
1835 Bom_Business_Event_PKG.Raise_Bill_Event
1836 ( p_pk1_value =>to_char( p_assembly_id)
1837 ,p_pk2_value => to_char(p_org_id)
1838 ,p_obj_name => null
1839 ,p_structure_name => p_alternate
1840 ,p_structure_comment => null
1841 ,p_organization_id => p_org_id
1842 ,p_Event_Name => Bom_Business_Event_PKG.G_STRUCTURE_DEL_SUCCESS_EVENT);
1843 elsif (p_delete_type =4) then
1844 Bom_Business_Event_PKG.Raise_Component_Event
1845 ( p_bill_sequence_id => p_bill_id
1846 ,p_pk1_value => to_char(p_comp_id)
1847 ,p_pk2_value => to_char(p_org_id)
1848 ,p_obj_name => null
1849 ,p_organization_id => p_org_id
1850 ,p_comp_item_name => p_item_name
1851 ,p_comp_description => p_description
1852 ,p_Event_Name => Bom_Business_Event_PKG.G_COMPONENT_DEL_SUCCESS_EVENT);
1853 end if; --if (p_delete_type = 1) then
1854
1855 elsif (p_action_type = 3) then
1856
1857 if (p_delete_type = 1) then
1858 Bom_Business_Event_PKG.Raise_Item_Event
1859 ( p_Inventory_Item_Id => p_assembly_id
1860 ,p_Organization_Id => p_org_id
1861 ,p_item_name => p_item_name
1862 ,p_item_description => p_description
1863 ,p_Event_Name => Bom_Business_Event_PKG.G_ITEM_DEL_ERROR_EVENT);
1864 elsif (p_delete_type =2 ) then
1865 Bom_Business_Event_PKG.Raise_Bill_Event
1866 ( p_pk1_value => to_char(p_assembly_id)
1867 ,p_pk2_value => to_char(p_org_id)
1868 ,p_obj_name => null
1869 ,p_structure_name => p_alternate
1870 ,p_structure_comment => null
1871 ,p_organization_id => p_org_id
1872 ,p_Event_Name => Bom_Business_Event_PKG.G_STRUCTURE_DEL_ERROR_EVENT);
1873 elsif (p_delete_type =4) then
1874 Bom_Business_Event_PKG.Raise_Component_Event
1875 ( p_bill_sequence_id => p_bill_id
1876 ,p_pk1_value => to_char(p_comp_id)
1877 ,p_pk2_value => to_char(p_org_id)
1878 ,p_obj_name => null
1879 ,p_organization_id => p_org_id
1880 ,p_comp_item_name => p_item_name
1881 ,p_comp_description => p_description
1882 ,p_Event_Name => Bom_Business_Event_PKG.G_COMPONENT_DEL_ERROR_EVENT);
1883 end if; --if (p_delete_type = 1) then
1884
1885 end if; --if (p_action_type =4) then
1886
1887 return 0;
1888
1889 EXCEPTION
1890 WHEN others THEN
1891 err_text := err_text||'invoke_events'||stmt_num||' '||substrb(SQLERRM,1,500);
1892 RETURN SQLCODE;
1893
1894 end;
1895
1896 PROCEDURE delete_groups
1897 (ERRBUF OUT NOCOPY VARCHAR2,
1898 RETCODE OUT NOCOPY VARCHAR2,
1899 delete_group_id IN NUMBER:= '0',
1900 action_type IN NUMBER:= '1',
1901 delete_type IN NUMBER:= '1',
1902 archive IN NUMBER:='1'
1903 )
1904 is
1905 begin
1906 delete_groups(
1907 ERRBUF => ERRBUF,
1908 RETCODE => RETCODE,
1909 delete_group_id => delete_group_id ,
1910 action_type => action_type,
1911 delete_type => delete_type,
1912 archive => archive,
1913 process_errored_rows => 'Y');
1914 end;
1915
1916
1917 end Bom_Delete_Groups_Api;