[Home] [Help]
PACKAGE BODY: APPS.E_REV_ITEM_INT_PKG
Source
1 PACKAGE BODY E_REV_ITEM_INT_PKG as
2 /* $Header: bompirib.pls 120.3 2006/11/21 09:33:01 rnarveka ship $
3 |===========================================================================+
4 | Copyright (c) 1994 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +===========================================================================*/
8
9 PROCEDURE After_Delete(X_revised_item_sequence_id NUMBER) IS
10 BEGIN
11 DELETE FROM bom_inventory_comps_interface bici
12 WHERE revised_item_sequence_id = X_revised_item_sequence_id;
13
14 END After_Delete;
15
16 PROCEDURE Call_Mass_Change (
17 X_change_notice in varchar2, -- CHANGE_ORDER
18 X_org_id in NUMBER, -- ORGANIZATION_ID
19 X_model_item_access in NUMBER, -- MODEL_ITEM_ACCESS
20 X_planning_item_access in NUMBER, -- PLANNING_ITEM_ACCESS
21 X_std_item_access in NUMBER, -- STANDARD_ITEM_ACCESS
22 X_impl_code in NUMBER, -- IMPLEMENT
23 X_report_code in NUMBER, -- REPORT
24 X_delete_code in NUMBER, -- DELETE
25 X_req_id IN OUT NOCOPY NUMBER) IS
26 BEGIN
27 X_req_id := FND_REQUEST.Submit_Request(
28 'BOM', -- application
29 'BMCMUD', -- concurrent program
30 '', -- description
31 '', FALSE,
32 X_change_notice, -- CHANGE_ORDER
33 to_char(X_org_id), -- ORGANIZATION_ID
34 to_char(X_model_item_access), -- MODEL_ITEM_ACCESS
35 to_char(X_planning_item_access), -- PLANNING_ITEM_ACCESS
36 to_char(X_std_item_access), -- STANDARD_ITEM_ACCESS
37 to_char(X_impl_code), -- IMPLEMENT
38 to_char(X_report_code), -- REPORT
39 to_char(X_delete_code), -- DELETE
40 chr(0), '',
41 '', '', '', '', '', '', '', '', '', '', -- argument 11..20
42 '', '', '', '', '', '', '', '', '', '', -- argument 21..30
43 '', '', '', '', '', '', '', '', '', '', -- argument 31..40
44 '', '', '', '', '', '', '', '', '', '', -- argument 41..50
45 '', '', '', '', '', '', '', '', '', '', -- argument 53..60
46 '', '', '', '', '', '', '', '', '', '', -- argument 61..70
47 '', '', '', '', '', '', '', '', '', '', -- argument 71..80
48 '', '', '', '', '', '', '', '', '', '', -- argument 81..90
49 '', '', '', '', '', '', '', '', '', ''); -- argument 91..100
50 END Call_Mass_Change;
51
52 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
53 X_Change_Notice VARCHAR2,
54 X_Organization_Id NUMBER,
55 X_Last_Update_Date DATE,
56 X_Last_Updated_By NUMBER,
57 X_Creation_Date DATE,
58 X_Created_By NUMBER,
59 X_Last_Update_Login NUMBER,
60 X_Scheduled_Date DATE,
61 X_Mrp_Active NUMBER,
62 X_Update_Wip NUMBER,
63 X_Use_Up NUMBER,
64 X_Use_Up_Item_Id NUMBER,
65 X_Revised_Item_Sequence_Id NUMBER,
66 X_Alternate_Bom_Designator VARCHAR2,
67 X_Category_Set_Id NUMBER,
68 X_Structure_Id NUMBER,
69 X_Item_From VARCHAR2,
70 X_Item_To VARCHAR2,
71 X_Category_From VARCHAR2,
72 X_Category_To VARCHAR2,
73 X_Increment_Rev NUMBER,
74 X_Item_Type VARCHAR2,
75 X_Use_Up_Plan_Name VARCHAR2,
76 X_Alternate_Selection_Code NUMBER,
77 X_Base_Item_Id NUMBER,
78 X_Submit_Request BOOLEAN,
79 X_model_item_access NUMBER,
80 X_planning_item_access NUMBER,
81 X_std_item_access NUMBER,
82 X_impl_code NUMBER,
83 X_report_code NUMBER,
84 X_delete_code NUMBER,
85 X_From_End_Item_Unit_Number VARCHAR2,
86 X_req_id IN OUT NOCOPY NUMBER
87 ) IS
88
89 CURSOR C IS SELECT rowid FROM ENG_REVISED_ITEMS_INTERFACE
90 WHERE revised_item_sequence_id = X_Revised_Item_Sequence_Id;
91
92 BEGIN
93 INSERT INTO ENG_REVISED_ITEMS_INTERFACE(
94 change_notice,
95 organization_id,
96 last_update_date,
97 last_updated_by,
98 creation_date,
99 created_by,
100 last_update_login,
101 scheduled_date,
102 mrp_active,
103 update_wip,
104 use_up,
105 use_up_item_id,
106 revised_item_sequence_id,
107 alternate_bom_designator,
108 category_set_id,
109 structure_id,
110 item_from,
111 item_to,
112 category_from,
113 category_to,
114 increment_rev,
115 item_type,
116 use_up_plan_name,
117 alternate_selection_code,
118 base_item_id,
119 from_end_item_unit_number
120 ) VALUES (
121 X_Change_Notice,
122 X_Organization_Id,
123 X_Last_Update_Date,
124 X_Last_Updated_By,
125 X_Creation_Date,
126 X_Created_By,
127 X_Last_Update_Login,
128 /* decode(trunc(X_Scheduled_Date),
129 trunc(sysdate), sysdate,
130 X_Scheduled_Date), commented out to take proper system date bug5665084*/
131 X_Scheduled_Date,
132 X_Mrp_Active,
133 X_Update_Wip,
134 X_Use_Up,
135 X_Use_Up_Item_Id,
136 X_Revised_Item_Sequence_Id,
137 X_Alternate_Bom_Designator,
138 X_Category_Set_Id,
139 X_Structure_Id,
140 X_Item_From,
141 X_Item_To,
142 X_Category_From,
143 X_Category_To,
144 X_Increment_Rev,
145 X_Item_Type,
146 X_Use_Up_Plan_Name,
147 X_Alternate_Selection_Code,
148 X_Base_Item_Id,
149 X_From_End_Item_Unit_Number
150 );
151
152 OPEN C;
153 FETCH C INTO X_Rowid;
154 if (C%NOTFOUND) then
155 CLOSE C;
156 Raise NO_DATA_FOUND;
157 end if;
158 CLOSE C;
159
160 -- ERES BEGIN
161 -- If ERES is enabled, launch conc process from the client code
162 -- ELSE do it here
163 -- ============================================================
164 If NVL(FND_PROFILE.VALUE('EDR_ERES_ENABLED'),'N') = 'N' THEN
165 If X_Submit_Request then
166 Call_Mass_Change (
167 X_change_notice => X_Change_Notice,
168 X_org_id => X_Organization_Id,
169 X_model_item_access => X_model_item_access,
170 X_planning_item_access => X_planning_item_access,
171 X_std_item_access => X_std_item_access,
172 X_impl_code => X_impl_code,
173 X_report_code => X_report_code,
174 X_delete_code => X_delete_code,
175 X_req_id => X_req_id);
176 End If;
177 End If;
178 -- ERES END
179
180 END Insert_Row;
181
182
183 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
184 X_Change_Notice VARCHAR2,
185 X_Organization_Id NUMBER,
186 X_Scheduled_Date DATE,
187 X_Mrp_Active NUMBER,
188 X_Update_Wip NUMBER,
189 X_Use_Up NUMBER,
190 X_Use_Up_Item_Id NUMBER,
191 X_Revised_Item_Sequence_Id NUMBER,
192 X_Alternate_Bom_Designator VARCHAR2,
193 X_Category_Set_Id NUMBER,
194 X_Structure_Id NUMBER,
195 X_Item_From VARCHAR2,
196 X_Item_To VARCHAR2,
197 X_Category_From VARCHAR2,
198 X_Category_To VARCHAR2,
199 X_Increment_Rev NUMBER,
200 X_Item_Type VARCHAR2,
201 X_Use_Up_Plan_Name VARCHAR2,
202 X_Alternate_Selection_Code NUMBER,
203 X_Base_Item_Id NUMBER,
204 X_From_End_Item_Unit_Number VARCHAR2
205 ) IS
206 CURSOR C IS
207 SELECT *
208 FROM ENG_REVISED_ITEMS_INTERFACE
209 WHERE rowid = X_Rowid
210 FOR UPDATE of Revised_Item_Sequence_Id NOWAIT;
211 Recinfo C%ROWTYPE;
212
213
214 BEGIN
215 OPEN C;
216 FETCH C INTO Recinfo;
217 if (C%NOTFOUND) then
218 CLOSE C;
219 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
220 APP_EXCEPTION.Raise_Exception;
221 end if;
222 CLOSE C;
223 if (
224 ( (Recinfo.change_notice = X_Change_Notice)
225 OR ( (Recinfo.change_notice IS NULL)
226 AND (X_Change_Notice IS NULL)))
227 AND ( (Recinfo.organization_id = X_Organization_Id)
228 OR ( (Recinfo.organization_id IS NULL)
229 AND (X_Organization_Id IS NULL)))
230 AND ( (trunc(Recinfo.scheduled_date) = trunc(X_Scheduled_Date))
231 OR ( (Recinfo.scheduled_date IS NULL)
232 AND (X_Scheduled_Date IS NULL)))
233 AND ( (Recinfo.mrp_active = X_Mrp_Active)
234 OR ( (Recinfo.mrp_active IS NULL)
235 AND (X_Mrp_Active IS NULL)))
236 AND ( (Recinfo.update_wip = X_Update_Wip)
237 OR ( (Recinfo.update_wip IS NULL)
238 AND (X_Update_Wip IS NULL)))
239 AND ( (Recinfo.use_up = X_Use_Up)
240 OR ( (Recinfo.use_up IS NULL)
241 AND (X_Use_Up IS NULL)))
242 AND ( (Recinfo.use_up_item_id = X_Use_Up_Item_Id)
243 OR ( (Recinfo.use_up_item_id IS NULL)
244 AND (X_Use_Up_Item_Id IS NULL)))
245 AND ( (Recinfo.revised_item_sequence_id = X_Revised_Item_Sequence_Id)
246 OR ( (Recinfo.revised_item_sequence_id IS NULL)
247 AND (X_Revised_Item_Sequence_Id IS NULL)))
248 AND ( (Recinfo.alternate_bom_designator = X_Alternate_Bom_Designator)
249 OR ( (Recinfo.alternate_bom_designator IS NULL)
250 AND (X_Alternate_Bom_Designator IS NULL)))
251 AND ( (Recinfo.category_set_id = X_Category_Set_Id)
252 OR ( (Recinfo.category_set_id IS NULL)
253 AND (X_Category_Set_Id IS NULL)))
254 AND ( (Recinfo.structure_id = X_Structure_Id)
255 OR ( (Recinfo.structure_id IS NULL)
256 AND (X_Structure_Id IS NULL)))
257 AND ( (Recinfo.item_from = X_Item_From)
258 OR ( (Recinfo.item_from IS NULL)
259 AND (X_Item_From IS NULL)))
260 AND ( (Recinfo.item_to = X_Item_To)
261 OR ( (Recinfo.item_to IS NULL)
262 AND (X_Item_To IS NULL)))
263 AND ( (Recinfo.category_from = X_Category_From)
264 OR ( (Recinfo.category_from IS NULL)
265 AND (X_Category_From IS NULL)))
266 AND ( (Recinfo.category_to = X_Category_To)
267 OR ( (Recinfo.category_to IS NULL)
268 AND (X_Category_To IS NULL)))
269 AND ( (Recinfo.increment_rev = X_Increment_Rev)
270 OR ( (Recinfo.increment_rev IS NULL)
271 AND (X_Increment_Rev IS NULL)))
272 AND ( (Recinfo.item_type = X_Item_Type)
273 OR ( (Recinfo.item_type IS NULL)
274 AND (X_Item_Type IS NULL)))
275 AND ( (Recinfo.use_up_plan_name = X_Use_Up_Plan_Name)
276 OR ( (Recinfo.use_up_plan_name IS NULL)
277 AND (X_Use_Up_Plan_Name IS NULL)))
278 AND ( (Recinfo.alternate_selection_code = X_Alternate_Selection_Code)
279 OR ( (Recinfo.alternate_selection_code IS NULL)
280 AND (X_Alternate_Selection_Code IS NULL)))
281 AND ( (Recinfo.base_item_id = X_Base_Item_Id)
282 OR ( (Recinfo.base_item_id IS NULL)
283 AND (X_Base_Item_Id IS NULL)))
284 AND ( (Recinfo.from_end_item_unit_number = X_From_End_Item_Unit_Number)
285 OR ( (Recinfo.from_end_item_unit_number IS NULL)
286 AND (X_From_End_Item_Unit_Number IS NULL)))
287 ) then
288 return;
289 else
290 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
291 APP_EXCEPTION.Raise_Exception;
292 end if;
293 END Lock_Row;
294
295 PROCEDURE Update_Row(X_Rowid VARCHAR2,
296 X_Change_Notice VARCHAR2,
297 X_Organization_Id NUMBER,
298 X_Last_Update_Date DATE,
299 X_Last_Updated_By NUMBER,
300 X_Last_Update_Login NUMBER,
301 X_Scheduled_Date DATE,
302 X_Mrp_Active NUMBER,
303 X_Update_Wip NUMBER,
304 X_Use_Up NUMBER,
305 X_Use_Up_Item_Id NUMBER,
306 X_Revised_Item_Sequence_Id NUMBER,
307 X_Alternate_Bom_Designator VARCHAR2,
308 X_Category_Set_Id NUMBER,
309 X_Structure_Id NUMBER,
310 X_Item_From VARCHAR2,
311 X_Item_To VARCHAR2,
312 X_Category_From VARCHAR2,
313 X_Category_To VARCHAR2,
314 X_Increment_Rev NUMBER,
315 X_Item_Type VARCHAR2,
316 X_Use_Up_Plan_Name VARCHAR2,
317 X_Alternate_Selection_Code NUMBER,
318 X_Base_Item_Id NUMBER,
319 X_Submit_Request BOOLEAN,
320 X_model_item_access NUMBER,
321 X_planning_item_access NUMBER,
322 X_std_item_access NUMBER,
323 X_impl_code NUMBER,
324 X_report_code NUMBER,
325 X_delete_code NUMBER,
326 X_From_End_Item_Unit_Number VARCHAR2,
327 X_req_id IN OUT NOCOPY NUMBER
328 ) IS
329 BEGIN
330 UPDATE ENG_REVISED_ITEMS_INTERFACE
331 SET
332 change_notice = X_Change_Notice,
333 organization_id = X_Organization_Id,
334 last_update_date = X_Last_Update_Date,
335 last_updated_by = X_Last_Updated_By,
336 last_update_login = X_Last_Update_Login,
337 scheduled_date = X_Scheduled_Date, /*decode(trunc(X_Scheduled_Date),
338 trunc(sysdate), sysdate,
339 X_Scheduled_Date) commented out to take proper system date bug5665084*/
340 mrp_active = X_Mrp_Active,
341 update_wip = X_Update_Wip,
342 use_up = X_Use_Up,
343 use_up_item_id = X_Use_Up_Item_Id,
344 revised_item_sequence_id = X_Revised_Item_Sequence_Id,
345 alternate_bom_designator = X_Alternate_Bom_Designator,
346 category_set_id = X_Category_Set_Id,
347 structure_id = X_Structure_Id,
348 item_from = X_Item_From,
349 item_to = X_Item_To,
350 category_from = X_Category_From,
351 category_to = X_Category_To,
352 increment_rev = X_Increment_Rev,
353 item_type = X_Item_Type,
354 use_up_plan_name = X_Use_Up_Plan_Name,
355 alternate_selection_code = X_Alternate_Selection_Code,
356 base_item_id = X_Base_Item_Id,
357 from_end_item_unit_number = X_From_End_Item_Unit_Number
358 WHERE rowid = X_Rowid;
359
360 if (SQL%NOTFOUND) then
361 Raise NO_DATA_FOUND;
362 end if;
363
364 -- ERES BEGIN
365 -- If ERES is enabled, launch conc process from the client code
366 -- ELSE do it here
367 -- ============================================================
368 If NVL(FND_PROFILE.VALUE('EDR_ERES_ENABLED'),'N') = 'N' THEN
369 If X_Submit_Request then
370 Call_Mass_Change (
371 X_change_notice => X_Change_Notice,
372 X_org_id => X_Organization_Id,
373 X_model_item_access => X_model_item_access,
374 X_planning_item_access => X_planning_item_access,
375 X_std_item_access => X_std_item_access,
376 X_impl_code => X_impl_code,
377 X_report_code => X_report_code,
378 X_delete_code => X_delete_code,
379 X_req_id => X_req_id);
380 End If;
381 End If;
382 -- ERES END
383
384 END Update_Row;
385
386 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
387 X_Rev_Itm_Seq_Id NUMBER;
388 BEGIN
389
390 BEGIN
391 SELECT REVISED_ITEM_SEQUENCE_ID
392 INTO X_Rev_Itm_Seq_Id
393 FROM ENG_REVISED_ITEMS_INTERFACE
394 WHERE ROWID = X_Rowid;
395 EXCEPTION
396 WHEN NO_DATA_FOUND THEN
397 NULL;
398 END;
399
400 DELETE FROM ENG_REVISED_ITEMS_INTERFACE
401 WHERE rowid = X_Rowid;
402
403 if (SQL%NOTFOUND) then
404 Raise NO_DATA_FOUND;
405 end if;
406
407 -- delete from child table
408 E_Rev_Item_Int_Pkg.After_Delete(X_revised_item_sequence_id =>
409 X_Rev_Itm_Seq_id);
410
411 END Delete_Row;
412
413
414 END E_REV_ITEM_INT_PKG;