[Home] [Help]
PACKAGE BODY: APPS.BOM_DELETE_GROUPS_API
Source
1 PACKAGE BODY Bom_Delete_Groups_Api AS
2 /* $Header: BOMPDELB.pls 120.12.12020000.4 2012/11/17 12:14:27 ntungare 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_DEL_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 /* Added for bug 13077632 */
559 UPDATE BOM_DELETE_ENTITIES
560 SET REQUEST_ID = req_id
561 WHERE DELETE_GROUP_SEQUENCE_ID = group_id
562 AND DELETE_STATUS_TYPE = 5;
563
564 else
565 for sub_entity_record in sub_entity_cursor(group_id) LOOP
566 SAVEPOINT start_process;
567
568 current_seq_id := sub_entity_record.DELETE_ENTITY_SEQ_ID;
569 if(p_debug = 'Y')then
570 fnd_file.put_line (Which => FND_FILE.LOG,
571 buff => 'delete_entity_Seq_id = '|| to_char(current_seq_id));
572 end if;
573 curr_del_entity_type:= delete_type;
574 token_list.inventory_item_id := sub_entity_record.inventory_item_id;
575 token_list.organization_id := sub_entity_record.organization_id;
576 token_list.bill_sequence_id := sub_entity_record.bill_seq_id;
577 token_list.routing_sequence_id := sub_entity_record.routing_seq_id;
578 token_list.component_sequence_id := sub_entity_record.component_seq_id;
579 token_list.operation_sequence_id := sub_entity_record.operation_seq_id;
580 token_list.del_group_seq_id := group_id;
581 token_list.component_item_id := sub_entity_record.component_item_id; -- added for bug:5726408
582
583 /*
584 ** delete all errors if any for this entity row since we are going to
585 ** rerun the delete on it
586 */
587 if (curr_del_entity_type=4 ) then
588 curr_comp_seq_id := sub_entity_record.component_seq_id;
589 DELETE FROM BOM_DELETE_ERRORS
590 WHERE DELETE_ENTITY_SEQUENCE_ID = current_seq_id
591 AND COMPONENT_SEQUENCE_ID = curr_comp_seq_id;
592 elsif ( curr_del_entity_type= 5) then
593 curr_op_seq_id := sub_entity_record.operation_seq_id;
594 DELETE FROM BOM_DELETE_ERRORS
595 WHERE DELETE_ENTITY_SEQUENCE_ID = current_seq_id
596 AND OPERATION_SEQUENCE_ID = curr_op_seq_id;
597 end if;
598
599 chk_ret := constraint_checker(token_list, current_seq_id,
600 curr_del_entity_type, err_text );
601
602 if (chk_ret = 0) then
603 /*
604 ** perform delete here if action_type is delete
605 */
606 if (action_type = ACT_DELETE) then
607 if (execute_delete(curr_del_entity_type,
608 token_list, archive_flag, action_status,
609 err_text ) <>0) THEN
610 ROLLBACK TO SAVEPOINT start_process;
611 delete_success := 1;
612 else
613 if (action_status = 4) then
614 /*
615 ** write to log file for every entity deleted
616 */
617 if(write_log(
618 sub_entity_record.alternate_designator,
619 sub_entity_record.organization_code,
620 sub_entity_record.item_name,
621 sub_entity_record.comp_name,
622 sub_entity_record.effectivity_date,
623 sub_entity_record.op_seq_num,
624 delete_type,err_text)<>0)then
625 return 2;
626 end if;
627
628 /*
629 ** invoke business events
630 */
631 if(invoke_events(
632 action_status,
633 sub_entity_record.organization_id,
634 null,
635 null,
636 sub_entity_record.comp_name,
637 sub_entity_record.description,
638 sub_entity_record.bill_seq_id,
639 sub_entity_record.component_item_id,
640 delete_type, err_text)<>0)then
641 return 2;
642 end if;
643
644 end if;
645 end if;
646
647 /* bug:5726408 Execute UPDATE statement for entity */
648 IF ( execute_update( curr_del_entity_type,
649 token_list,
650 action_status,
651 err_text ) <> 0 )
652 THEN
653
654 ROLLBACK TO SAVEPOINT start_process;
655 delete_success := 1;
656
657 END IF;
658
659 else
660 action_status := 2; /* check ok */
661 end if;
662 elsif (chk_ret = 1) then
663 action_status := 3; /* error */
664 /*
665 ** invoke business events
666 */
667 if(invoke_events(
668 action_status,
669 sub_entity_record.organization_id,
670 null,
671 null,
672 sub_entity_record.comp_name,
673 sub_entity_record.description,
674 sub_entity_record.bill_seq_id,
675 sub_entity_record.component_item_id,
676 delete_type,err_text)<>0)then
677 return 2;
678 end if;
679
680 else
681 ROLLBACK TO SAVEPOINT start_process;
682 return 2;
683 end if;
684
685 /*
686 ** set the status
687 */
688
689 UPDATE BOM_DELETE_SUB_ENTITIES
690 SET DELETE_STATUS_TYPE = action_status,
691 DELETE_DATE = decode(action_status, 4,
692 sysdate, NULL),
693 LAST_UPDATE_DATE = sysdate,
694 LAST_UPDATED_BY = user_id,
695 REQUEST_ID = req_id,
696 PROGRAM_UPDATE_DATE = SYSDATE,
697 PROGRAM_ID = prog_id,
698 PROGRAM_APPLICATION_ID = prog_appl_id
699 WHERE DELETE_ENTITY_SEQUENCE_ID = current_seq_id
700 AND ((delete_type = 4
701 AND COMPONENT_SEQUENCE_ID =
702 curr_comp_seq_id)
703 OR
704 (curr_del_entity_type = 5
705 AND OPERATION_SEQUENCE_ID =
706 curr_op_seq_id));
707 END LOOP;
708 end if;
709
710 if (delete_success = 1) then
711 return 2;
712 end if;
713 if (entity_cursor % ISOPEN) then
714 close entity_cursor;
715 end if;
716 if (sub_entity_cursor %ISOPEN) then
717 close sub_entity_cursor;
718 end if;
719
720 return 0;
721
722 EXCEPTION
723 WHEN others THEN
724 err_text := err_text||'do_delete '||stmt_num||' '||substrb(SQLERRM,1,500);
725 RETURN SQLCODE;
726
727
728 END;
729
730
731 /*****************************************************************
732 * FUNCTION : constraint_checker
733 * Parameters IN :token_list-TOKEN_RECORD, delete_seq_id, delete_entity_type
734 * Parameters OUT: err_text
735 * return : 0 -success ,2-error, other - SQL Exception
736 * Purpose : This function checks all the constraints from
737 * bom_delete_sql_statements table that are valid for given
738 * delete entity type
739 ******************************************************************/
740
741 FUNCTION constraint_checker( token_list IN Token_Record,
742 delete_seq_id IN NUMBER,
743 delete_entity_type IN NUMBER,
744 err_text OUT NOCOPY varchar2) return NUMBER is
745
746 CURSOR constraint_cursor(p_delete_entity_type NUMBER) IS
747 SELECT SQL_STATEMENT_NAME stmt_name,
748 DELETE_ON_SUCCESS_FLAG delete_on_success_flag,
749 MESSAGE_NAME,
750 SQL_STATEMENT
751 FROM BOM_DELETE_SQL_STATEMENTS
752 WHERE SQL_STATEMENT_TYPE = 1
753 AND ACTIVE_FLAG = 1
754 AND DELETE_ENTITY_TYPE = p_delete_entity_type;
755
756 cur_rec constraint_cursor%ROWTYPE;
757 INVENTORY_ITEM_ID NUMBER;
758 ORGANIZATION_ID NUMBER;
759 COMPONENT_SEQUENCE_ID NUMBER;
760 OPERATION_SEQUENCE_ID NUMBER;
761 BILL_SEQUENCE_ID NUMBER;
762 ROUTING_SEQUENCE_ID NUMBER;
763 constraint_stmt CLOB ; --modified from LONG to CLOB as part of 13401082
764 first_time NUMBER := 0;
765 bind_list BIND_TABLE;
766 cnt NUMBER;
767 error_sequence_number NUMBER :=0;
768 stmt_num NUMBER := 0;
769 ret_code NUMBER :=0;
770 cursor_name INTEGER;
771 rows_processed INTEGER;
772
773 BEGIN
774
775
776 INVENTORY_ITEM_ID := token_list.inventory_item_id;
777 ORGANIZATION_ID := token_list.organization_id;
778 BILL_SEQUENCE_ID := token_list.bill_sequence_id;
779 ROUTING_SEQUENCE_ID := token_list.routing_sequence_id;
780 COMPONENT_SEQUENCE_ID := token_list.component_sequence_id;
781 OPERATION_SEQUENCE_ID := token_list.operation_sequence_id;
782
783 stmt_num :=1;
784 OPEN constraint_cursor(delete_entity_type);
785
786 LOOP
787 FETCH constraint_cursor INTO cur_rec ;
788 EXIT WHEN constraint_cursor%NOTFOUND;
789
790 stmt_num := 2;
791 /*
792 ** do the consolidation here for items and bill. Set a savepoint here
793 ** so that when constraint failure, then rollback consolidation
794 */
795 if ((delete_entity_type = 1 OR delete_entity_type = 2 OR
796 delete_entity_type = 3) AND first_time = 0)THEN
797
798 stmt_num := 3;
799 SAVEPOINT consolidate;
800 first_time := 1;
801
802 if (config_item_consolidate( inventory_item_id,organization_id,
803 delete_entity_type, err_text) <> 0 )THEN
804
805 return (FATAL_ERROR);
806 end if; /* if */
807 END IF; /* if */
808 /*
809 ** check to see if sql stmt was truncated, if so then allocate and
810 ** retrieve again
811 */
812 stmt_num := 4;
813 SELECT SQL_STATEMENT
814 INTO constraint_stmt
815 FROM BOM_DELETE_SQL_STATEMENTS
816 WHERE SQL_STATEMENT_NAME = cur_rec.stmt_name;
817
818 constraint_stmt := UPPER(constraint_stmt);
819 if(p_debug = 'Y')then
820 fnd_file.put_line (Which => FND_FILE.LOG,
821 buff => constraint_stmt);
822 end if;
823
824 /*
825 ** check to see if the first word in the statement is other than
826 ** select. In which case, this constraint should not be executed
827 */
828 stmt_num := 5;
829 if ( instr(constraint_stmt,'SELECT') =0) THEN
830 return(FATAL_ERROR);
831 end if;
832
833
834 stmt_num := 6;
835 if (substitute_tokens(token_list, constraint_stmt, bind_list ,err_text)<> 0) THEN
836 return 2;
837 else
838
839 if(p_debug = 'Y')then
840 fnd_file.put_line (Which => FND_FILE.LOG,
841 buff => constraint_stmt);
842 end if;
843 stmt_num := 7;
844 begin
845 cursor_name := dbms_sql.open_cursor;
846 DBMS_SQL.PARSE(cursor_name, constraint_stmt,dbms_sql.native);
847 dbms_sql.define_column(cursor_name, 1, cnt);
848 for i in 1..bind_list.COUNT loop
849
850 DBMS_SQL.BIND_VARIABLE(cursor_name, bind_list(i).bind_name, bind_list(i).bind_value);
851
852 end loop;
853 rows_processed := dbms_sql.execute_and_fetch(cursor_name);
854 dbms_sql.column_value(cursor_name, 1, cnt);
855 dbms_sql.close_cursor(cursor_name);
856 exception
857 when no_data_found then
858 cnt := 0;
859 dbms_sql.close_cursor(cursor_name);
860 when others then
861 if(p_debug = 'Y')then
862 fnd_file.put_line ( Which => FND_FILE.LOG,
863 buff => SUBSTRB(SQLERRM,1,500) );
864 end if;
865 DBMS_SQL.close_cursor(cursor_name);
866 end;
867
868 if(p_debug = 'Y')then
869 fnd_file.put_line (Which => FND_FILE.LOG,
870 buff => ' No of Rows = '||to_char(cnt));
871 end if;
872 if (cnt =0 AND cur_rec.delete_on_success_flag = 1) OR
873 (cnt <> 0 and cur_rec.delete_on_success_flag =2) then
874 error_sequence_number := error_sequence_number +1;
875 INSERT INTO BOM_DELETE_ERRORS (
876 DELETE_ENTITY_SEQUENCE_ID,
877 COMPONENT_SEQUENCE_ID,
878 OPERATION_SEQUENCE_ID,
879 ERROR_SEQUENCE_NUMBER,
880 SQL_STATEMENT_NAME,
881 LAST_UPDATE_DATE,
882 LAST_UPDATED_BY,
883 CREATION_DATE,
884 CREATED_BY)
885 VALUES (
886 delete_seq_id,
887 component_sequence_id,
888 operation_sequence_id,
889 error_sequence_number,
890 cur_rec.stmt_name,
891 SYSDATE,
892 user_id,
893 SYSDATE,
894 user_id);
895 ret_code := 1;
896 end if;
897 end if;
898 END LOOP;
899 if constraint_cursor%ISOPEN then
900 close constraint_cursor;
901 end if;
902
903 return ret_code;
904
905 EXCEPTION
906 WHEN others THEN
907 err_text := err_text||'const_checker '||stmt_num||' '||substrb(SQLERRM,1,500);
908 RETURN SQLCODE;
909
910 END;
911
912
913
914 /*****************************************************************
915 * FUNCTION : substitute_tokens
916 * Parameters IN : token_list -TOKEN_RECORD, stmt -LONG
917 * Parameters OUT: stmt - LONG bind_list -BIND_TABLE, err_text
918 * return : 0 -success , other - SQL Exception
919 * Purpose : This function will replace '&' with ':'.
920 * and capture all the bind parameters used in the statement.
921 ******************************************************************/
922 FUNCTION substitute_tokens ( token_list IN TOKEN_RECORD,
923 stmt IN OUT NOCOPY LONG,
924 bind_list OUT NOCOPY BIND_TABLE,
925 err_text OUT NOCOPY varchar2) return NUMBER is
926
927 i NUMBER :=1;
928 stmt_num NUMBER := 0;
929 BEGIN
930 stmt_num :=1;
931 stmt := UPPER(stmt);
932
933 stmt_num := 2;
934 stmt := replace(stmt,'&',':');
935
936 stmt_num := 3;
937 if( INSTR (stmt,':BILL_SEQUENCE_ID',1,1) <>0 ) then
938 bind_list(i).bind_name := 'BILL_SEQUENCE_ID';
939 bind_list(i).bind_value := token_list.bill_sequence_id;
940 i := i+1;
941 end if;
942 stmt_num := 4;
943 if (INSTR (stmt,':ROUTING_SEQUENCE_ID',1,1) <>0 ) then
944 bind_list(i).bind_name := 'ROUTING_SEQUENCE_ID';
945 bind_list(i).bind_value := token_list.routing_sequence_id;
946 i := i+1;
947 end if;
948 stmt_num := 5;
949 if (INSTR (stmt,':ITEM_ID',1,1) <>0) then
950 bind_list(i).bind_name := 'ITEM_ID';
951 bind_list(i).bind_value := token_list.inventory_item_id;
952 i := i+1;
953 end if;
954 stmt_num := 4;
955 if (INSTR (stmt,':COMPONENT_SEQUENCE_ID',1,1) <>0) then
956 bind_list(i).bind_name := 'COMPONENT_SEQUENCE_ID';
957 bind_list(i).bind_value := token_list.component_sequence_id;
958 i := i+1;
959 end if;
960 stmt_num := 5;
961 if (INSTR (stmt,':OPERATION_SEQUENCE_ID',1,1) <>0) then
962 bind_list(i).bind_name := 'OPERATION_SEQUENCE_ID';
963 bind_list(i).bind_value := token_list.operation_sequence_id;
964 i := i+1;
965 end if;
966 stmt_num := 6;
967 if (INSTR (stmt,':ORGANIZATION_ID',1,1) <>0) then
968 bind_list(i).bind_name := 'ORGANIZATION_ID';
969 bind_list(i).bind_value := token_list.organization_id;
970 i := i+1;
971 end if;
972
973 stmt_num := 7;
974 --bug:5546629 Added support for token DEL_GROUP_SEQ_ID
975 if (INSTR (stmt,':DEL_GROUP_SEQ_ID',1,1) <>0) then
976 bind_list(i).bind_name := 'DEL_GROUP_SEQ_ID';
977 bind_list(i).bind_value := token_list.del_group_seq_id;
978 i := i+1;
979 end if;
980
981 stmt_num := 8;
982 --bug:5546629 Added support for token COMP_ITEM_ID
983 if (INSTR (stmt,':COMPONENT_ITEM_ID',1,1) <>0) then
984 bind_list(i).bind_name := 'COMPONENT_ITEM_ID';
985 bind_list(i).bind_value := token_list.component_item_id;
986 i := i+1;
987 end if;
988
989 return 0;
990
991 EXCEPTION
992 WHEN others THEN
993 err_text := err_text||'sub_token '||stmt_num||' '||substrb(SQLERRM,1,500);
994 RETURN SQLCODE;
995
996 END;
997
998
999
1000
1001 /*****************************************************************
1002 * FUNCTION : config_item_consolidate
1003 * Parameters IN : inventory_item_id,organization_id,delete_entity_type
1004 * Parameters OUT: err_text
1005 * return : 0 -success ,2-error, other - SQL Exception
1006 * Purpose : Configuration Item Purge - Consolidate Item
1007 ******************************************************************/
1008 FUNCTION config_item_consolidate( p_inventory_item_id IN NUMBER,
1009 p_organization_id IN NUMBER,
1010 p_delete_entity_type IN NUMBER,
1011 err_text OUT NOCOPY VARCHAR2) return NUMBER is
1012
1013 config_flag VARCHAR2(1) := ' ';
1014 delete_status VARCHAR2(10) := ' ';
1015 item_status VARCHAR2(10) := ' ';
1016 base_id NUMBER := 0;
1017 job_count NUMBER := 0;
1018 stmt_num NUMBER :=0;
1019
1020 BEGIN
1021 stmt_num := 1;
1022 if(p_debug = 'Y')then
1023 fnd_file.put_line (Which => FND_FILE.LOG,
1024 buff => 'inventory_item_id:'||to_char(p_inventory_item_id));
1025 fnd_file.put_line (Which => FND_FILE.LOG,
1026 buff => 'organization_id:'||to_char(p_organization_id));
1027
1028 fnd_file.put_line (Which => FND_FILE.LOG,
1029 buff => 'delete_entity_type:'||to_char(p_delete_entity_type));
1030 end if;
1031
1032 select MSI.AUTO_CREATED_CONFIG_FLAG,
1033 MSI.INVENTORY_ITEM_STATUS_CODE,
1034 MSI.BASE_ITEM_ID
1035 into config_flag,
1036 item_status,
1037 base_id
1038 from MTL_SYSTEM_ITEMS MSI
1039 where MSI.ORGANIZATION_ID = p_organization_id
1040 and MSI.INVENTORY_ITEM_ID = p_inventory_item_id;
1041
1042 stmt_num := 2;
1043 IF config_flag = 'Y' THEN
1044 BEGIN
1045 select BP.BOM_DELETE_STATUS_CODE
1046 into delete_status
1047 from BOM_PARAMETERS BP
1048 where BP.ORGANIZATION_ID = p_organization_id;
1049 stmt_num := 3;
1050
1051 IF item_status = delete_status THEN
1052 BEGIN
1053 IF base_id <> p_inventory_item_id and base_id IS NOT NULL THEN
1054 BEGIN
1055 job_count := 0;
1056 IF p_delete_entity_type in (2,3) THEN /* bill or routing */
1057 BEGIN
1058 stmt_num := 4;
1059 select count(*)
1060 into job_count
1061 from WIP_DISCRETE_JOBS WDJ
1062 where WDJ.ORGANIZATION_ID = p_organization_id
1063 and WDJ.PRIMARY_ITEM_ID = p_inventory_item_id
1064 and WDJ.STATUS_TYPE <> 12 /*Closed-no charges allowed*/
1065 and rownum = 1; /* get just the first one that */
1066 /* isn't closed */
1067 IF job_count = 0 then /* all were closed */
1068 BEGIN
1069 stmt_num := 5;
1070 update WIP_ENTITIES WE
1071 set WE.PRIMARY_ITEM_ID = base_id
1072 where WE.ORGANIZATION_ID =p_organization_id
1073 and WE.PRIMARY_ITEM_ID = p_inventory_item_id;
1074
1075 stmt_num := 6;
1076 update WIP_DISCRETE_JOBS WDJ
1077 set WDJ.PRIMARY_ITEM_ID = base_id,
1078 WDJ.ALTERNATE_BOM_DESIGNATOR = NULL,
1079 WDJ.ALTERNATE_ROUTING_DESIGNATOR = NULL
1080 where WDJ.ORGANIZATION_ID = p_organization_id
1081 and WDJ.PRIMARY_ITEM_ID = p_inventory_item_id;
1082
1083 stmt_num := 7;
1084 update WIP_MOVE_TRANSACTIONS WMT
1085 set WMT.PRIMARY_ITEM_ID = base_id
1086 where WMT.ORGANIZATION_ID = p_organization_id
1087 and WMT.PRIMARY_ITEM_ID = p_inventory_item_id;
1088
1089 stmt_num := 8;
1090 update WIP_MOVE_TXN_INTERFACE WMTI
1091 set WMTI.PRIMARY_ITEM_ID = base_id
1092 where WMTI.ORGANIZATION_ID = p_organization_id
1093 and WMTI.PRIMARY_ITEM_ID = p_inventory_item_id;
1094
1095 stmt_num := 9;
1096 update WIP_REQUIREMENT_OPERATIONS WRO
1097 set WRO.INVENTORY_ITEM_ID = base_id
1098 where WRO.ORGANIZATION_ID = p_organization_id
1099 and WRO.INVENTORY_ITEM_ID = p_inventory_item_id;
1100
1101 stmt_num := 10;
1102 update WIP_COST_TXN_INTERFACE WCTI
1103 set WCTI.PRIMARY_ITEM_ID = base_id
1104 where WCTI.ORGANIZATION_ID = p_organization_id
1105 and WCTI.PRIMARY_ITEM_ID = p_inventory_item_id;
1106
1107 stmt_num := 11;
1108 update WIP_TRANSACTIONS WT
1109 set WT.PRIMARY_ITEM_ID = base_id
1110 where WT.ORGANIZATION_ID = p_organization_id
1111 and WT.PRIMARY_ITEM_ID = p_inventory_item_id;
1112
1113 END;
1114 END IF; /* if job status is closed */
1115 END;
1116 END IF; /* if bill or routing type */
1117 IF p_delete_entity_type = 1 THEN /* item */
1118 BEGIN
1119 stmt_num := 12;
1120 update MTL_MATERIAL_TRANSACTIONS MT
1121 set MT.INVENTORY_ITEM_ID = base_id
1122 where MT.ORGANIZATION_ID = p_organization_id
1123 and MT.INVENTORY_ITEM_ID = p_inventory_item_id;
1124
1125 stmt_num := 13;
1126 update MTL_TRANSACTION_LOT_NUMBERS MTLN
1127 set MTLN.INVENTORY_ITEM_ID = base_id
1128 where MTLN.ORGANIZATION_ID = p_organization_id
1129 and MTLN.INVENTORY_ITEM_ID = p_inventory_item_id;
1130
1131 stmt_num := 14;
1132 update MTL_UNIT_TRANSACTIONS MUT
1133 set MUT.INVENTORY_ITEM_ID = base_id
1134 where MUT.ORGANIZATION_ID = p_organization_id
1135 and MUT.INVENTORY_ITEM_ID = p_inventory_item_id;
1136 stmt_num := 15;
1137
1138 update MTL_TRANSACTION_ACCOUNTS MTA
1139 set MTA.INVENTORY_ITEM_ID = base_id
1140 where MTA.ORGANIZATION_ID = p_organization_id
1141 and MTA.INVENTORY_ITEM_ID = p_inventory_item_id;
1142 END;
1143 END IF; /* if item type */
1144
1145 END;
1146 END IF; /* if base_id <> item_id */
1147 END;
1148 END IF; /* if status matches */
1149 END;
1150 END IF; /* If config item */
1151 return(0); /* Set status to OK */
1152
1153 EXCEPTION
1154 WHEN NO_DATA_FOUND THEN /* Don't try to consolidate, return true status */
1155 return (0);
1156 WHEN OTHERS THEN
1157 err_text := err_text||'config_item_consolidate'||stmt_num||' '||substrb(SQLERRM,1,500);
1158 RETURN SQLCODE;
1159 END;
1160
1161 /*****************************************************************
1162 * FUNCTION : execute_delete
1163 * Parameters IN :token_list-TOKEN_RECORD, delete_entity_type
1164 * archive_flag
1165 * Parameters OUT: err_text, action_status(4-delete,3-error)
1166 * return : 0 -success , other - SQL Exception
1167 * Purpose : This function executes the delete statements from
1168 * bom_delete_sql_statements table that are valid for given
1169 * delete entity type
1170 ******************************************************************/
1171 FUNCTION execute_delete(delete_entity_type IN NUMBER,
1172 token_list IN Token_Record,
1173 archive_flag IN NUMBER,
1174 action_status OUT NOCOPY NUMBER,
1175 err_text OUT NOCOPY VARCHAR2) return NUMBER is
1176
1177 CURSOR delete_cursor(p_delete_entity_type NUMBER) IS
1178 SELECT sql_statement stmt, ARCHIVE_TABLE_NAME,
1179 length(ARCHIVE_TABLE_NAME) archive_table_length,
1180 SQL_STATEMENT_NAME stmt_name
1181 FROM BOM_DELETE_SQL_STATEMENTS
1182 WHERE SQL_STATEMENT_TYPE = 2
1183 AND ACTIVE_FLAG = 1
1184 AND DELETE_ENTITY_TYPE = p_delete_entity_type
1185 ORDER BY SEQUENCE_NUMBER;
1186
1187 table_name VARCHAR2(80);
1188 where_stmt LONG;
1189 delete_stmt LONG;
1190 bind_list BIND_TABLE;
1191 stmt_num NUMBER :=0;
1192 cursor_name INTEGER;
1193 rows_processed INTEGER;
1194 BEGIN
1195 action_status := 3; -- initially set to Error.
1196 stmt_num := 1;
1197 for cur_rec in delete_cursor(delete_entity_type) LOOP
1198
1199 stmt_num := 2;
1200 SAVEPOINT consolidate;
1201
1202 /*
1203 ** check to see if sql stmt was truncated, if so then allocate and
1204 ** retrieve again
1205 */
1206
1207 stmt_num := 3;
1208
1209 delete_stmt := upper(cur_rec.stmt);
1210
1211 if(p_debug = 'Y')then
1212 fnd_file.put_line (Which => FND_FILE.LOG,
1213 buff => delete_stmt);
1214 end if;
1215 /*
1216 ** check to see if the first word in the statement is other than
1217 ** select. In which case, this constraint should not be executed
1218 */
1219 stmt_num := 4;
1220 if ( instr(delete_stmt,'DELETE') =0) THEN
1221 return(FATAL_ERROR);
1222
1223 end if;
1224
1225 stmt_num := 5;
1226
1227 if (substitute_tokens( token_list,delete_stmt,bind_list,err_text)<>0) THEN
1228 return 2;
1229 else
1230 if(p_debug = 'Y')then
1231 fnd_file.put_line (Which => FND_FILE.LOG,
1232 buff => delete_stmt);
1233 end if;
1234 if (cur_rec.archive_table_length > 0 AND archive_flag = 1) THEN
1235 stmt_num := 6;
1236 table_name := extract_table_name(delete_stmt,err_text);
1237 stmt_num := 7;
1238 where_stmt := extract_where(delete_stmt,err_text);
1239 stmt_num := 8;
1240 if (archive_data(token_list,cur_rec.archive_table_name,
1241 table_name, where_stmt,bind_list, err_text) <> 0) THEN
1242 stmt_num := 9;
1243 ROLLBACK TO SAVEPOINT start_process;
1244 return 1;
1245 end if;
1246 stmt_num := 1;
1247 end if;
1248 end if;
1249
1250
1251 cursor_name := dbms_sql.open_cursor;
1252 DBMS_SQL.PARSE(cursor_name, delete_stmt,dbms_sql.native);
1253 for i in 1..bind_list.COUNT loop
1254
1255
1256 DBMS_SQL.BIND_VARIABLE(cursor_name, bind_list(i).bind_name, bind_list(i).bind_value);
1257
1258 end loop;
1259 rows_processed:= DBMS_SQL.execute(cursor_name);
1260 dbms_sql.close_cursor(cursor_name);
1261 END LOOP;
1262 action_status := 4; -- deleted successfully
1263 return 0;
1264
1265 EXCEPTION
1266 WHEN others THEN
1267 err_text := err_text||'exec_delete '||stmt_num||' '||substrb(SQLERRM,1,500);
1268 ROLLBACK TO SAVEPOINT start_process;
1269 RETURN SQLCODE;
1270
1271
1272 end;
1273
1274 -- bug:5726408 Added support for executing UPDATE statement.
1275 /*****************************************************************
1276 * FUNCTION : execute_update
1277 * Parameters IN : delete_entity_type Type of delete entity
1278 * token_list Records of tokens to be substituted
1279 * Parameters OUT: action_status 4-delete, 3-error
1280 * err_text Error message in case of exception
1281 * return : 0 -success , other - SQL Exception
1282 * Purpose : This function executes the update statements from
1283 * bom_delete_sql_statements table that are valid for given
1284 * delete entity type
1285 ******************************************************************/
1286
1287 FUNCTION execute_update (
1288 delete_entity_type IN NUMBER,
1289 token_list IN Token_Record,
1290 action_status OUT NOCOPY NUMBER,
1291 err_text OUT NOCOPY VARCHAR2) RETURN NUMBER
1292 IS
1293
1294 CURSOR delete_cursor(p_delete_entity_type NUMBER)
1295 IS
1296 SELECT
1297 SQL_STATEMENT stmt,
1298 SQL_STATEMENT_NAME stmt_name
1299 FROM BOM_DELETE_SQL_STATEMENTS
1300 WHERE
1301 SQL_STATEMENT_TYPE = 3
1302 AND ACTIVE_FLAG = 1
1303 AND DELETE_ENTITY_TYPE = p_delete_entity_type
1304 ORDER BY SEQUENCE_NUMBER;
1305
1306 delete_stmt LONG;
1307 bind_list BIND_TABLE;
1308 stmt_num NUMBER := 0;
1309 cursor_name INTEGER;
1310 rows_processed INTEGER;
1311
1312 BEGIN
1313 action_status := 3; -- initially set to Error.
1314
1315 stmt_num := 1;
1316 FOR cur_rec IN delete_cursor ( delete_entity_type )
1317 LOOP
1318
1319 /* check to see if sql stmt was truncated, if so then allocate and
1320 * retrieve again */
1321
1322 stmt_num := 2;
1323 delete_stmt := UPPER(cur_rec.stmt);
1324
1325 IF ( p_debug = 'Y' )
1326 THEN
1327 FND_FILE.put_line ( Which => FND_FILE.LOG,
1328 buff => delete_stmt);
1329 END IF;
1330
1331 /* check to see if the first word in the statement is UPDATE. */
1332 stmt_num := 3;
1333 IF ( INSTR( delete_stmt, 'UPDATE' ) = 0 )
1334 THEN
1335 RETURN( FATAL_ERROR );
1336 END IF;
1337
1338 stmt_num := 4;
1339 IF ( substitute_tokens( token_list, delete_stmt, bind_list, err_text ) <> 0 )
1340 THEN
1341 RETURN 2;
1342 ELSE
1343 IF ( p_debug = 'Y' )
1344 THEN
1345 fnd_file.put_line ( Which => FND_FILE.LOG,
1346 buff => delete_stmt );
1347 END IF;
1348 END IF ;
1349
1350 cursor_name := DBMS_SQL.OPEN_CURSOR;
1351
1352 DBMS_SQL.PARSE( cursor_name, delete_stmt, DBMS_SQL.NATIVE );
1353
1354 FOR i IN 1 .. bind_list.COUNT
1355 LOOP
1356 DBMS_SQL.BIND_VARIABLE( cursor_name, bind_list(i).bind_name, bind_list(i).bind_value );
1357 END LOOP;
1358
1359 rows_processed:= DBMS_SQL.EXECUTE(cursor_name);
1360
1361 DBMS_SQL.CLOSE_CURSOR( cursor_name );
1362
1363 END LOOP; -- end FOR cur_rec IN delete_cursor
1364
1365 action_status := 4; -- deleted successfully
1366 RETURN 0;
1367
1368 EXCEPTION
1369 WHEN OTHERS THEN
1370 err_text := err_text||'exec_update '||stmt_num||' '||substrb(SQLERRM,1,500);
1371 RETURN SQLCODE;
1372
1373 END execute_update;
1374
1375
1376 /*****************************************************************
1377 * FUNCTION : update_op_sequences
1378 * Parameters IN :tdelete_entity_type ,routing_seq_id
1379 * Parameters OUT: err_text
1380 * return : 0 -success , other - SQL Exception
1381 * Purpose : updates op sequences to 1 in BOM_INVENTORY_COMPONENTS
1382 ******************************************************************/
1383
1384 FUNCTION update_op_sequences(delete_entity_type IN NUMBER,
1385 routing_seq_id IN NUMBER,
1386 op_seq_id IN NUMBER,
1387 err_text OUT NOCOPY varchar2) return NUMBER is
1388 stmt_num NUMBER := 0;
1389
1390 BEGIN
1391 if (delete_entity_type = 3) THEN/* routing delete */
1392 stmt_num := 1;
1393 UPDATE BOM_INVENTORY_COMPONENTS
1394 SET OPERATION_SEQ_NUM = 1
1395 WHERE BILL_SEQUENCE_ID = (SELECT BILL_SEQUENCE_ID
1396 FROM BOM_BILL_OF_MATERIALS BOM,
1397 BOM_OPERATIONAL_ROUTINGS BOR
1398 WHERE BOR.ROUTING_SEQUENCE_ID = routing_seq_id
1399 AND BOR.ORGANIZATION_ID = BOM.ORGANIZATION_ID
1400 AND BOR.ASSEMBLY_ITEM_ID = BOM.ASSEMBLY_ITEM_ID
1401 AND nvl(BOR.ALTERNATE_ROUTING_DESIGNATOR, 'NONE') =
1402 nvl(BOM.ALTERNATE_BOM_DESIGNATOR, 'NONE'));
1403 else /* operation delete */
1404 stmt_num := 2;
1405 UPDATE BOM_INVENTORY_COMPONENTS BIC
1406 SET OPERATION_SEQ_NUM = 1
1407 WHERE BILL_SEQUENCE_ID = (SELECT BILL_SEQUENCE_ID
1408 FROM BOM_BILL_OF_MATERIALS BOM,
1409 BOM_OPERATIONAL_ROUTINGS BOR
1410 WHERE BOR.ROUTING_SEQUENCE_ID = routing_seq_id
1411 AND BOR.ORGANIZATION_ID = BOM.ORGANIZATION_ID
1412 AND BOR.ASSEMBLY_ITEM_ID = BOM.ASSEMBLY_ITEM_ID
1413 AND nvl(BOR.ALTERNATE_ROUTING_DESIGNATOR, 'NONE') =
1414 nvl(BOM.ALTERNATE_BOM_DESIGNATOR, 'NONE'))
1415 AND OPERATION_SEQ_NUM = (SELECT OPERATION_SEQ_NUM
1416 FROM BOM_OPERATION_SEQUENCES
1417 WHERE OPERATION_SEQUENCE_ID = op_seq_id);
1418 end if;
1419
1420 return 0;
1421 EXCEPTION
1422 WHEN others THEN
1423 err_text := err_text||'update_op_sequences'||stmt_num||' '||substrb(SQLERRM,1,500);
1424 RETURN SQLCODE;
1425
1426 END;
1427
1428 /*****************************************************************
1429 * FUNCTION : archive_data
1430 * Parameters IN :token_list-TOKEN_RECORD, archive table name,
1431 * product table name , where clause.
1432 * Parameters OUT: err_text
1433 * return : 0 -success ,2-error, other - SQL Exception
1434 * Purpose : archive the data from product table.
1435 ******************************************************************/
1436
1437 FUNCTION archive_data( token_list IN Token_Record,
1438 insert_table IN varchar2,
1439 table_name IN varchar2,
1440 where_clause IN varchar2,
1441 bind_list IN BIND_TABLE,
1442 err_text OUT NOCOPY varchar2) return NUMBER is
1443
1444 l_schema VARCHAR2(30);
1445 l_status VARCHAR2(1);
1446 l_industry VARCHAR2(1);
1447 l_oracleUser VARCHAR2(30);
1448
1449 -- bug 15835553
1450 /*
1451 CURSOR col_list_cursor( prod_table VARCHAR2,
1452 schema_name VARCHAR2,
1453 oracle_user VARCHAR2) IS
1454 SELECT distinct ATC.COLUMN_NAME COLUMN_NAME
1455 FROM ALL_TAB_COLUMNS ATC,
1456 ALL_OBJECTS AO
1457 WHERE TABLE_NAME = trim(prod_table)
1458 AND ( ( AO.OBJECT_TYPE = 'TABLE' AND ATC.OWNER = schema_name )
1459 OR ( AO.OBJECT_TYPE = 'VIEW' AND ATC.OWNER = oracle_user ) )
1460 AND AO.OBJECT_NAME = trim(prod_table)
1461 AND AO.OWNER = ATC.OWNER
1462 ORDER BY COLUMN_NAME;
1463 */
1464 CURSOR col_list_cursor( prod_table VARCHAR2,
1465 schema_name VARCHAR2,
1466 oracle_user VARCHAR2) IS
1467 SELECT DISTINCT ATC.COLUMN_NAME COLUMN_NAME
1468 FROM
1469 (SELECT col.column_name,
1470 col.owner,
1471 syn.synonym_name AS table_name
1472 FROM user_synonyms syn,
1473 dba_tab_columns col
1474 WHERE col.owner = syn.table_owner
1475 AND col.table_name = syn.table_name
1476 AND syn.synonym_name = trim(prod_table)
1477 ) ATC,
1478 ALL_OBJECTS AO
1479 WHERE ( ( AO.OBJECT_TYPE = 'TABLE' AND ATC.OWNER = schema_name )
1480 OR ( AO.OBJECT_TYPE = 'VIEW' AND ATC.OWNER = oracle_user )
1481 )
1482 AND AO.OBJECT_NAME = ATC.table_name
1483 AND AO.OWNER = ATC.OWNER
1484 -- for cases wherein the object is a view then it wont be prsent in
1485 -- user_synonyms when run from APPS schema. eg. BIC. So doing a UNION ALL
1486 -- with following SQL
1487 UNION ALL
1488 SELECT DISTINCT ATC.COLUMN_NAME COLUMN_NAME
1489 FROM DBA_TAB_COLUMNS ATC,
1490 ALL_OBJECTS AO
1491 WHERE AO.OBJECT_TYPE = 'VIEW'
1492 AND ATC.OWNER = oracle_user
1493 AND AO.OBJECT_NAME = ATC.table_name
1494 AND AO.OWNER = ATC.OWNER
1495 AND AO.object_name = trim(prod_table)
1496 AND NOT EXISTS (SELECT 1 FROM user_synonyms WHERE synonym_name = trim(prod_table))
1497 ORDER BY 1;
1498
1499 insert_stmt LONG;
1500 update_stmt LONG;
1501 archive_table varchar2(80);
1502 prod_table varchar2(80);
1503 column_name VARCHAR2 (80);
1504 dummy NUMBER :=0;
1505 req_id NUMBER :=-1;
1506 prog_id NUMBER :=-1;
1507 stmt_num NUMBER:=0;
1508 cursor_name INTEGER;
1509 rows_processed INTEGER;
1510 l_app_short_name VARCHAR2(10);
1511
1512 BEGIN
1513 /*
1514 ** update the standard who columns before archiving the data. Need to
1515 ** do it this way, since if I try to update after archiving, then I don't
1516 ** know which rows were updated. If for some reason there is a failure
1517 ** then it rollsback the updates anyways
1518 */
1519
1520
1521 stmt_num := 0;
1522 req_id := nvl(FND_PROFILE.value('CONC_REQUEST_ID'),-1);
1523 prog_id := nvl(FND_PROFILE.value('CONC_PROGRAM_ID'),-1);
1524 resp_appl_id := nvl(FND_PROFILE.value('RESP_APPL_ID'),-1);
1525
1526 stmt_num := 1;
1527 update_stmt:= 'UPDATE' || table_name || ' SET REQUEST_ID = ' || req_id
1528 ||', PROGRAM_ID = ' || prog_id ||', PROGRAM_APPLICATION_ID =' || resp_appl_id
1529 ||', PROGRAM_UPDATE_DATE = sysdate ';
1530
1531 if(p_debug = 'Y')then
1532 fnd_file.put_line (Which => FND_FILE.LOG,
1533 buff => update_stmt);
1534 end if;
1535 update_stmt := update_stmt ||' WHERE '||where_clause;
1536 stmt_num := 2;
1537 cursor_name := dbms_sql.open_cursor;
1538 DBMS_SQL.PARSE(cursor_name, update_stmt,dbms_sql.native);
1539 for i in 1..bind_list.COUNT loop
1540 DBMS_SQL.BIND_VARIABLE(cursor_name, bind_list(i).bind_name, bind_list(i).bind_value);
1541 end loop;
1542 rows_processed:= DBMS_SQL.execute(cursor_name);
1543 dbms_sql.close_cursor(cursor_name);
1544 stmt_num := 3;
1545 archive_table := insert_table;
1546 prod_table := table_name;
1547 /*
1548 Begin
1549 SELECT 1
1550 INTO dummy
1551 FROM DUAL
1552 WHERE EXISTS (
1553 SELECT NULL
1554 FROM ALL_TAB_COLUMNS COL1
1555 WHERE TABLE_NAME = trim(prod_table)
1556 AND NOT EXISTS (
1557 SELECT NULL
1558 FROM ALL_TAB_COLUMNS COL2
1559 WHERE TABLE_NAME = trim(archive_table)
1560 AND COL2.COLUMN_NAME = COL1.COLUMN_NAME));
1561 exception
1562 when no_data_found then
1563 dummy :=0;
1564 when others then
1565 dummy :=2;
1566 end;
1567 stmt_num := 4;
1568 if (dummy <> 0) then
1569 -- archive table structure does not match production table structure
1570 fnd_message.set_name('BOM', 'BOM_ARCHIVE_TOO_OLD');
1571 err_text := fnd_message.get;
1572 return(2);
1573 end if;
1574 */
1575 SELECT
1576 ORACLE_USERNAME INTO l_oracleUser
1577 FROM
1578 FND_ORACLE_USERID
1579 WHERE
1580 READ_ONLY_FLAG = 'U';
1581
1582 --Bug No: 4248530. When the prod table is in some schema other than BOM then we need
1583 -- to get the schema name Ex: MTL_RTG_ITEM_REVISIONS.
1584 l_app_short_name := 'BOM';
1585 IF(INSTR(trim(prod_table), 'MTL',1,1) = 1) THEN
1586 l_app_short_name := 'INV';
1587 END IF;
1588
1589 IF NOT FND_INSTALLATION.GET_APP_INFO(l_app_short_name, l_status, l_industry, l_schema)
1590 THEN
1591 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1592 END IF;
1593
1594 IF (l_schema IS NULL OR l_oracleUser IS NULL)
1595 THEN
1596 stmt_num := 4;
1597 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1598 END IF;
1599
1600 stmt_num := 5;
1601 insert_stmt := insert_stmt || ' INSERT INTO '||insert_table || '( ';
1602
1603 IF (p_debug = 'Y') THEN
1604 fnd_file.put_line (Which => FND_FILE.LOG, buff => ('Schema Name:' || l_schema));
1605 fnd_file.put_line (Which => FND_FILE.LOG, buff => ('Oracle User:' || l_oracleUser));
1606 fnd_file.put_line (Which => FND_FILE.LOG, buff => ('Prod Table:' || prod_table));
1607 END IF;
1608
1609 for col_list in col_list_cursor(prod_table, l_schema, l_oracleUser) loop
1610 insert_stmt := insert_stmt || col_list.column_name || ' , ';
1611 END LOOP;
1612
1613 stmt_num := 6;
1614 insert_stmt:= trim (insert_stmt);
1615 insert_stmt := substr(insert_stmt,1,length(insert_stmt)-1 );
1616 insert_stmt := insert_stmt || ' ) SELECT ';
1617
1618 for col_list in col_list_cursor(prod_table, l_schema, l_oracleUser) loop
1619 insert_stmt := insert_stmt || col_list.column_name || ' , ';
1620 END LOOP;
1621
1622 insert_stmt := trim (insert_stmt);
1623 insert_stmt := substr(insert_stmt,1,length(insert_stmt)-1 );
1624 stmt_num := 7;
1625 insert_stmt := insert_stmt || ' FROM '|| prod_table || ' WHERE ' ||where_clause;
1626
1627 stmt_num := 8;
1628 if(p_debug = 'Y')then
1629 fnd_file.put_line (Which => FND_FILE.LOG,
1630 buff => insert_stmt);
1631 end if;
1632 cursor_name := dbms_sql.open_cursor;
1633 DBMS_SQL.PARSE(cursor_name, insert_stmt,dbms_sql.native);
1634 for i in 1..bind_list.COUNT loop
1635
1636 DBMS_SQL.BIND_VARIABLE(cursor_name, bind_list(i).bind_name, bind_list(i).bind_value);
1637
1638 end loop;
1639 rows_processed:= DBMS_SQL.execute(cursor_name);
1640 dbms_sql.close_cursor(cursor_name);
1641 return 0;
1642
1643 EXCEPTION
1644 WHEN others THEN
1645 err_text := err_text||'archive_data'||stmt_num||' '||substrb(SQLERRM,1,500);
1646 RETURN SQLCODE;
1647
1648 END;
1649
1650 /*****************************************************************
1651 * FUNCTION : extract_table_name
1652 * Parameters IN :stmt-LONG
1653 * return : Table name present in the statement
1654 ******************************************************************/
1655
1656 FUNCTION extract_table_name ( stmt IN LONG, err_text OUT NOCOPY VARCHAR2) return LONG is
1657 position1 NUMBER;
1658 position2 NUMBER;
1659 ret_value LONG;
1660 stmt_num NUMBER;
1661 BEGIN
1662 stmt_num :=1;
1663 position1 := instr (stmt, 'DELETE',1) +6;
1664 stmt_num := 2;
1665 position2 := instr(stmt,' WHERE',1);
1666 stmt_num :=3;
1667 ret_value := substr( stmt, position1, position2-position1);
1668 stmt_num :=4;
1669 ret_value := replace(ret_value,'FROM',' ');
1670 stmt_num := 5;
1671 return ret_value;
1672 EXCEPTION
1673 WHEN others THEN
1674 err_text := err_text||'extract_table_name'||stmt_num||' '||substrb(SQLERRM,1,500);
1675 RETURN SQLCODE;
1676 END;
1677 /*****************************************************************
1678 * FUNCTION : extract_table_name
1679 * Parameters IN :stmt-LONG
1680 * return : where clause of the statement
1681 ******************************************************************/
1682 FUNCTION extract_where (stmt IN LONG, err_text OUT NOCOPY VARCHAR2) return LONG is
1683 position1 NUMBER;
1684 stmt_num NUMBER;
1685
1686 BEGIN
1687 stmt_num :=1;
1688 position1 := instr(stmt,' WHERE',1);
1689 stmt_num :=2;
1690 return substr( stmt, position1+6);
1691 EXCEPTION
1692 WHEN others THEN
1693 err_text := err_text||'extract_where '||stmt_num||' '||substrb(SQLERRM,1,500);
1694 RETURN SQLCODE;
1695 END;
1696 /*****************************************************************
1697 * FUNCTION : write_log
1698 * Parameters IN : alt_desg ,org_id,item_name ,comp_name ,
1699 * eff_date,op_seq,delete_type.
1700 * Parameters OUT: err_text
1701 * return : 0 -success , other - SQL Exception
1702 * Purpose : This function will write to conc-log
1703 ******************************************************************/
1704 FUNCTION write_log(alt_desg IN VARCHAR2,
1705 org_name IN VARCHAR2,
1706 item_name IN VARCHAR2,
1707 comp_name IN VARCHAR2,
1708 eff_date IN VARCHAR2,
1709 op_seq IN NUMBER,
1710 delete_type IN NUMBER,
1711 err_text OUT NOCOPY VARCHAR2) return NUMBER is
1712 err_text1 varchar2(2000);
1713 err_text2 varchar2(2000);
1714 stmt_num NUMBER;
1715
1716 begin
1717 stmt_num := 1;
1718 if(delete_type = 1)then
1719 /* item delete */
1720 Fnd_Message.set_name('BOM', 'BOM_ITEM_DELETED');
1721 Fnd_Message.set_token('ORG', org_name);
1722 Fnd_Message.set_token('ITEM', item_name);
1723 err_text1 := Fnd_Message.get;
1724 elsif(delete_type =2 )then
1725
1726 /* bill delete */
1727 Fnd_Message.set_name('BOM', 'BOM_BILL_DELETED');
1728 Fnd_Message.set_token('ORG', org_name);
1729 Fnd_Message.set_token('ITEM', item_name);
1730 Fnd_Message.set_token('ALTERNATE', alt_desg);
1731 err_text1 := Fnd_Message.get;
1732 elsif(delete_type =3 )then
1733 /* routing delete */
1734 Fnd_Message.set_name('BOM', 'BOM_ROUTING_DELETED');
1735 Fnd_Message.set_token('ORG', org_name);
1736 Fnd_Message.set_token('ITEM', item_name);
1737 Fnd_Message.set_token('ALTERNATE', alt_desg);
1738 elsif(delete_type =4 )then
1739 /* component delete */
1740 Fnd_Message.set_name('BOM', 'BOM_COMPONENT_DELETED1');
1741 Fnd_Message.set_token('ORG', org_name);
1742 Fnd_Message.set_token('ITEM', item_name);
1743 Fnd_Message.set_token('ALTERNATE', alt_desg);
1744 err_text1 := Fnd_Message.get;
1745 Fnd_Message.set_name('BOM', 'BOM_COMPONENT_DELETED2');
1746 Fnd_Message.set_token('COMPONENT', comp_name);
1747 Fnd_Message.set_token('OP', op_seq);
1748 Fnd_Message.set_token('EFFDATE', eff_date);
1749 err_text2 := Fnd_Message.get;
1750 elsif(delete_type =5 )then
1751 /* operation delete */
1752 Fnd_Message.set_name('BOM', 'BOM_OPERATION_DELETED1');
1753 Fnd_Message.set_token('ORG', org_name);
1754 Fnd_Message.set_token('ITEM', item_name);
1755 Fnd_Message.set_token('ALTERNATE', alt_desg);
1756 err_text1 := Fnd_Message.get;
1757 Fnd_Message.set_name('BOM', 'BOM_OPERATION_DELETED2');
1758 Fnd_Message.set_token('COMPONENT', comp_name);
1759 Fnd_Message.set_token('OP', op_seq);
1760 Fnd_Message.set_token('EFFDATE', eff_date);
1761 err_text2 := Fnd_Message.get;
1762 end if;
1763 if err_text1 IS NOT NULL THEN
1764 fnd_file.put_line (Which => FND_FILE.LOG,
1765 buff => err_text1);
1766 else
1767
1768 if err_text2 is not null then
1769 fnd_file.put_line (Which => FND_FILE.LOG,
1770 buff => err_text2);
1771
1772 end if;
1773 end if;
1774 return 0;
1775 EXCEPTION
1776 WHEN others THEN
1777 err_text := err_text||'write_log'||stmt_num||' '||substrb(SQLERRM,1,500);
1778 RETURN SQLCODE;
1779
1780 end;
1781
1782 /*****************************************************************
1783 * FUNCTION : invoke_events
1784 * Parameters IN : ction_type ,org_id,inv_id ,alternate,structure type
1785 * bill_id, comp_id,delete_type
1786 * Parameters OUT: err_text
1787 * return : 0 -success ,other - SQL Exception
1788 * Purpose : This function will invokde different Business Events
1789 * depending on the parameters passed.
1790 ******************************************************************/
1791 FUNCTION invoke_events( p_action_type IN NUMBER,
1792 p_org_id IN NUMBER,
1793 p_assembly_id IN NUMBER,
1794 p_alternate IN VARCHAR2,
1795 p_item_name VARCHAR2,
1796 p_description VARCHAR2,
1797 p_bill_id IN NUMBER,
1798 p_comp_id IN NUMBER,
1799 p_delete_type IN NUMBER,
1800 err_text OUT NOCOPY VARCHAR2) return NUMBER is
1801 l_ret_status varchar2(1);
1802 l_org_code varchar2(30);
1803 l_master_org_flag varchar2(1);
1804 stmt_num NUMBER:=1;
1805 begin
1806
1807 if (p_action_type =4) then
1808
1809 if (p_delete_type = 1) then
1810
1811 /* Call IP api */ -- bug 4323967
1812
1813 IF (BOM_VALIDATE.Object_Exists(
1814 p_object_type => 'PACKAGE',
1815 p_object_name => 'ICX_CAT_POPULATE_MI_GRP') = 'Y') THEN
1816
1817 SELECT DECODE(master_organization_id, p_org_id, 'Y', 'N'), organization_code
1818 INTO l_master_org_flag, l_org_code
1819 FROM MTL_PARAMETERS
1820 WHERE organization_id = p_org_id;
1821
1822 stmt_num := 2;
1823
1824 EXECUTE IMMEDIATE
1825 ' BEGIN '||
1826 ' ICX_CAT_POPULATE_MI_GRP.populateItemChange ( '||
1827 ' P_API_VERSION => 1.0 '||
1828 ' ,P_COMMIT => FND_API.G_FALSE '||
1829 ' ,P_INIT_MSG_LIST => FND_API.G_FALSE '||
1830 ' ,P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL '||
1831 ' ,P_DML_TYPE => ''DELETE'' '||
1832 ' , P_INVENTORY_ITEM_ID =>:p_assembly_id '||
1833 ' , P_ITEM_NUMBER =>:p_item_name '||
1834 ' , P_ORGANIZATION_ID =>:p_org_id '||
1835 ' , P_ORGANIZATION_CODE =>:l_org_code '||
1836 ' , P_MASTER_ORG_FLAG =>:l_master_org_flag '||
1837 ' , P_ITEM_DESCRIPTION =>:p_description '||
1838 ' ,X_RETURN_STATUS => :l_ret_status ); '||
1839 ' END;'
1840 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;
1841
1842 END IF; --BOM_VALIDATE.Object_Exists for ICX_CAT_POPULATE_MI_GRP
1843
1844 --Now calling EGO code that will cancel any NIRs that exist for the deleted item.
1845 --Bug 5526375
1846 IF (BOM_VALIDATE.Object_Exists(
1847 p_object_type => 'PACKAGE',
1848 p_object_name => 'EGO_COMMON_PVT') = 'Y') THEN
1849
1850 IF (BOM_VALIDATE.Object_Exists(
1851 p_object_type => 'PACKAGE',
1852 p_object_name => 'ENG_NIR_UTIL_PKG') = 'Y') THEN
1853
1854 stmt_num := 3;
1855
1856 EXECUTE IMMEDIATE
1857 ' BEGIN '||
1858 ' EGO_COMMON_PVT.CANCEL_NIR_FOR_DELETE_ITEM( '||
1859 ' P_INVENTORY_ITEM_ID =>:p_assembly_id '||
1860 ' , P_ORGANIZATION_ID =>:p_org_id '||
1861 ' , P_ITEM_NUMBER =>:p_item_name ); '||
1862 ' END;'
1863 USING IN p_assembly_id, IN p_org_id, IN p_item_name;
1864
1865 END IF; --BOM_VALIDATE.Object_Exists for ENG_NIR_UTIL_PKG
1866
1867 END IF; --BOM_VALIDATE.Object_Exists for EGO_COMMON_PVT
1868
1869
1870 Bom_Business_Event_PKG.Raise_Item_Event
1871 ( p_Inventory_Item_Id => p_assembly_id
1872 ,p_Organization_Id => p_org_id
1873 ,p_item_name => p_item_name
1874 ,p_item_description => p_description
1875 ,p_Event_Name => Bom_Business_Event_PKG.G_ITEM_DEL_SUCCESS_EVENT);
1876
1877 elsif (p_delete_type =2 ) then
1878 Bom_Business_Event_PKG.Raise_Bill_Event
1879 ( p_pk1_value =>to_char( p_assembly_id)
1880 ,p_pk2_value => to_char(p_org_id)
1881 ,p_obj_name => null
1882 ,p_structure_name => p_alternate
1883 ,p_structure_comment => null
1884 ,p_organization_id => p_org_id
1885 ,p_Event_Name => Bom_Business_Event_PKG.G_STRUCTURE_DEL_SUCCESS_EVENT);
1886 elsif (p_delete_type =4) then
1887 Bom_Business_Event_PKG.Raise_Component_Event
1888 ( p_bill_sequence_id => p_bill_id
1889 ,p_pk1_value => to_char(p_comp_id)
1890 ,p_pk2_value => to_char(p_org_id)
1891 ,p_obj_name => null
1892 ,p_organization_id => p_org_id
1893 ,p_comp_item_name => p_item_name
1894 ,p_comp_description => p_description
1895 ,p_Event_Name => Bom_Business_Event_PKG.G_COMPONENT_DEL_SUCCESS_EVENT);
1896 end if; --if (p_delete_type = 1) then
1897
1898 elsif (p_action_type = 3) then
1899
1900 if (p_delete_type = 1) then
1901 Bom_Business_Event_PKG.Raise_Item_Event
1902 ( p_Inventory_Item_Id => p_assembly_id
1903 ,p_Organization_Id => p_org_id
1904 ,p_item_name => p_item_name
1905 ,p_item_description => p_description
1906 ,p_Event_Name => Bom_Business_Event_PKG.G_ITEM_DEL_ERROR_EVENT);
1907 elsif (p_delete_type =2 ) then
1908 Bom_Business_Event_PKG.Raise_Bill_Event
1909 ( p_pk1_value => to_char(p_assembly_id)
1910 ,p_pk2_value => to_char(p_org_id)
1911 ,p_obj_name => null
1912 ,p_structure_name => p_alternate
1913 ,p_structure_comment => null
1914 ,p_organization_id => p_org_id
1915 ,p_Event_Name => Bom_Business_Event_PKG.G_STRUCTURE_DEL_ERROR_EVENT);
1916 elsif (p_delete_type =4) then
1917 Bom_Business_Event_PKG.Raise_Component_Event
1918 ( p_bill_sequence_id => p_bill_id
1919 ,p_pk1_value => to_char(p_comp_id)
1920 ,p_pk2_value => to_char(p_org_id)
1921 ,p_obj_name => null
1922 ,p_organization_id => p_org_id
1923 ,p_comp_item_name => p_item_name
1924 ,p_comp_description => p_description
1925 ,p_Event_Name => Bom_Business_Event_PKG.G_COMPONENT_DEL_ERROR_EVENT);
1926 end if; --if (p_delete_type = 1) then
1927
1928 end if; --if (p_action_type =4) then
1929
1930 return 0;
1931
1932 EXCEPTION
1933 WHEN others THEN
1934 err_text := err_text||'invoke_events'||stmt_num||' '||substrb(SQLERRM,1,500);
1935 RETURN SQLCODE;
1936
1937 end;
1938
1939 PROCEDURE delete_groups
1940 (ERRBUF OUT NOCOPY VARCHAR2,
1941 RETCODE OUT NOCOPY VARCHAR2,
1942 delete_group_id IN NUMBER:= '0',
1943 action_type IN NUMBER:= '1',
1944 delete_type IN NUMBER:= '1',
1945 archive IN NUMBER:='1'
1946 )
1947 is
1948 begin
1949 delete_groups(
1950 ERRBUF => ERRBUF,
1951 RETCODE => RETCODE,
1952 delete_group_id => delete_group_id ,
1953 action_type => action_type,
1954 delete_type => delete_type,
1955 archive => archive,
1956 process_errored_rows => 'Y');
1957 end;
1958
1959
1960 end Bom_Delete_Groups_Api;