[Home] [Help]
PACKAGE BODY: APPS.BOMDELEX
Source
1 PACKAGE BODY BOMDELEX AS
2 /* $Header: BOMDEXPB.pls 120.1 2005/06/21 04:44:18 appldev ship $ */
3
4 /*==========================================================================+
5 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
6 | All rights reserved. |
7 +===========================================================================+
8 | |
9 | File Name : BOMDELEB.pls |
10 | DESCRIPTION : This file is a packaged body for deleting
11 | records from bom_explosions table where the rexplode flag
12 | is set to 1
13 | Parameters: 1 - top bill sequence id , 2-explosion type
14 | error_code error code
15 | error_msg error message
16 |History :
17 |23-JUN-03 Sangeetha CREATED
18 +==========================================================================*/
19 PROCEDURE DELETE_BOM_EXPLOSIONS(
20 ERRBUF IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
21 RETCODE IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
22 top_bill_seq_id IN Number ,
23 expl_type IN Varchar2
24 ) IS
25 conc_status BOOLEAN ;
26 Current_Error_Code Varchar2(20) := NULL;
27 INVALID_ARGUMENT_LIST Exception;
28 Cursor Get_Delete_Rows IS
29 Select top_bill_sequence_id, bill_sequence_id,
30 explosion_type, sort_order
31 From bom_explosions
32 where top_bill_sequence_id = top_bill_seq_id
33 and explosion_type = expl_type
34 and rexplode_flag = 1 ;
35
36 BEGIN
37 /* Print the list of parameters */
38 FND_FILE.PUT_LINE(FND_FILE.LOG,'******************************************') ;
39 FND_FILE.PUT_LINE( FND_FILE.LOG,'Top_Bill_sequence_id='||to_char(top_bill_seq_id));
40 FND_FILE.PUT_LINE( FND_FILE.LOG,'Explosion Type='||expl_type);
41 FND_FILE.PUT_LINE(FND_FILE.LOG,'******************************************') ;
42
43 /* Make sure the right set of parameter are passed */
44 IF (top_bill_seq_id is NULL) Or (expl_type IS NULL) THEN
45 raise invalid_argument_list ;
46 END IF ;
47
48
49 For Delete_Rows in Get_Delete_Rows
50 Loop
51 Loop
52 Delete from bom_explosions BE
53 Where
54 BE.top_bill_sequence_id = DELETE_ROWS.top_bill_sequence_id
55 And BE.EXPLOSION_TYPE =
56 DELETE_ROWS.explosion_type
57 AND (BE.SORT_ORDER like DELETE_ROWS.sort_order||'%'
58 AND BE.SORT_ORDER <> DELETE_ROWS.sort_order)
59 AND ROWNUM < 1000 ;
60 EXIT WHEN (SQL%ROWCOUNT = 0) ;
61 COMMIT;
62 End Loop;
63 END LOOP ;
64
65 UPDATE BOM_EXPLOSIONS
66 SET REQUEST_ID = NULL
67 WHERE TOP_BILL_SEQUENCE_ID = top_bill_seq_id
68 AND EXPLOSION_TYPE = expl_type
69 AND SORT_ORDER = Bom_Common_Definitions.G_Bom_Init_SortCode;
70
71 Commit ;
72
73 RETCODE := 0 ;
74 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',Current_Error_Code);
75
76 EXCEPTION
77 WHEN invalid_argument_list THEN
78 UPDATE BOM_EXPLOSIONS
79 SET REQUEST_ID = NULL
80 WHERE TOP_BILL_SEQUENCE_ID = top_bill_seq_id
81 AND EXPLOSION_TYPE = expl_type
82 AND SORT_ORDER = Bom_Common_Definitions.G_Bom_Init_SortCode;
83 commit;
84 FND_FILE.PUT_LINE(FND_FILE.LOG,'Either Top assembly Item Id or Explosion type is not specified') ;
85 RETCODE := 2;
86 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
87 When Others Then
88 Rollback;
89 UPDATE BOM_EXPLOSIONS
90 SET REQUEST_ID = NULL
91 WHERE TOP_BILL_SEQUENCE_ID = top_bill_seq_id
92 AND EXPLOSION_TYPE = expl_type
93 AND SORT_ORDER = Bom_Common_Definitions.G_Bom_Init_SortCode;
94 commit;
95
96 FND_FILE.PUT_LINE(FND_FILE.LOG,'The concurrent request did not complete
97 successfully');
98 FND_FILE.PUT_LINE(fnd_file.log,'Others exception raised');
99 FND_FILE.PUT_LINE(FND_FILE.LOG,'Others : '||SQLCODE || ':'||SQLERRM) ;
100 RETCODE := 2;
101 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
102
103 END DELETE_BOM_EXPLOSIONS;
104
105 PROCEDURE GET_TOP_BILL(Item_Id IN Number,
106 Org_Id IN Number,
107 Alt_Bom_Desg IN VARCHAR2,
108 Return_Status IN OUT NOCOPY /* file.sql.39 change */ Varchar2 ,
109 Err_Buf IN OUT NOCOPY /* file.sql.39 change */ Varchar2) IS
110
111 Cursor get_top(Bill_Seq_Id Number)
112 IS
113 Select top_bill_sequence_id, explosion_type, sort_order
114 from bom_explosions BE
115 where BE.COMP_COMMON_BILL_SEQ_ID = Bill_Seq_Id
116 and BE.rexplode_flag = 1
117 and BE.explosion_type in ('INCLUDED','OPTIONAL','ALL');
118
119 Cursor bom_expl(top_bill_id Number, expl_type VARCHAR2)
120 IS
121 SELECT Request_id
122 FROM Bom_explosions
123 WHERE top_bill_sequence_id = top_bill_id
124 AND explosion_type = expl_type
125 AND sort_order = Bom_Common_Definitions.G_Bom_Init_SortCode;
126
127 X_Req_Id Number := 0;
128 Req_Id Number := 0;
129 Bill_Id Number := 0;
133 Begin
130 l_delete_bom_expl Number := 2;
131 expl_row_cnt Number := 0;
132
134
135 l_delete_bom_expl := fnd_profile.value('BOM:DELETE_BOM_EXPLOSIONS');
136
137 If l_delete_bom_expl = 1 then
138 SELECT bill_sequence_id
139 INTO Bill_Id
140 FROM bom_bill_of_materials
141 WHERE Assembly_Item_Id = Item_Id
142 AND nvl(Alternate_Bom_Designator,'NONE')=nvl(Alt_Bom_Desg,'NONE')
143 AND Organization_Id = Org_Id;
144
145 For C1 in get_top(Bill_Id)
146 Loop
147 expl_row_cnt := 0;
148 Begin
149 SELECT count(*)
150 into expl_row_cnt
151 FROM bom_explosions
152 WHERE top_bill_sequence_id = C1.top_bill_sequence_id
153 AND explosion_type = C1.explosion_type
154 AND sort_order like C1.sort_order||'%'
155 AND sort_order <> C1.sort_order;
156 END;
157 If (expl_row_cnt > 0) then
158 For cr in bom_expl(C1.top_bill_sequence_id,C1.explosion_type)
159 Loop
160 If (cr.Request_Id IS NULL ) then
161 X_req_id := fnd_request.submit_request(
162 application => 'BOM',
163 program => 'BOMDELEX',
164 description => NULL,
168 argument2 => C1.explosion_type);
165 start_time => NULL,
166 sub_request => FALSE,
167 argument1 => C1.top_bill_sequence_id,
169 If (X_req_id <> 0) then
170 Update bom_explosions
171 set REQUEST_ID = X_req_id
172 where top_bill_sequence_id = C1.top_bill_sequence_id
173 and explosion_type = C1.explosion_type
174 and SORT_ORDER = Bom_Common_Definitions.G_Bom_Init_SortCode;
175 Commit;
176 Return_Status := 'S';
177 Else
178 Return_Status := 'E';
179 End if;
180 End If;
181 End Loop;
182 X_req_Id := 0;
183 End If;
184 End Loop;
185 End if;
186 Exception
187 WHEN NO_DATA_FOUND THEN
188 Err_Buf := SQLERRM;
189 Return_Status := 'E';
190 WHEN OTHERS THEN
191 Err_buf := SQLERRM;
192 Return_Status := 'E';
193 End GET_TOP_BILL;
194
195 END BOMDELEX ;