[Home] [Help]
PACKAGE BODY: APPS.ENG_BOM_PKG
Source
1 PACKAGE BODY ENG_BOM_PKG AS
2 /* $Header: ENGPBTRB.pls 120.6 2006/03/06 08:06:33 prgopala noship $ */
3
4 /***************************************************************************
5 * Function : Get_GTIN_Structure_Type_Id
6 * Returns : StructureTypeId of 'Packaging Hierarchy' / NULL
7 * Parameters IN : None
8 * Parameters OUT: None
9 * Purpose : To get the structure type id of 'Packaging Hierarchy' if available
10 *****************************************************************************/
11 FUNCTION Get_GTIN_Structure_Type_Id RETURN NUMBER
12 IS
13 l_GTIN_Id NUMBER;
14 BEGIN
15 SELECT Structure_Type_Id
16 INTO l_GTIN_Id
17 FROM bom_structure_types_vl
18 WHERE Structure_Type_Name ='Packaging Hierarchy';
19
20 RETURN l_GTIN_Id;
21
22 EXCEPTION
23 WHEN NO_DATA_FOUND THEN
24 Return NULL;
25 END Get_GTIN_Structure_Type_Id;
26
27 -- +------------------------------ BOM_UPDATE --------------------------------+
28 -- NAME
29 -- BOM_UPDATE
30
31 -- DESCRIPTION
32 -- Update Bills: Flip assembly_type to 1 (manufacturing)
33
34 -- REQUIRES
35 -- org_id: organization id
36 -- eng_item_id: bill that requires assembly_type to bee set to 1
37 -- designator_option
38 -- 1. all
39 -- 2. primary only
40 -- 3. specific only
41 -- alt_bom_designator
42
43 -- OUTPUT
44
45 -- NOTES
46
47 -- +--------------------------------------------------------------------------+
48
49 PROCEDURE BOM_UPDATE
50 (
51 X_org_id IN NUMBER,
52 X_eng_item_id IN NUMBER,
53 X_designator_option IN NUMBER,
54 X_transfer_option IN NUMBER,
55 X_alt_bom_designator IN VARCHAR2,
56 X_effectivity_date IN DATE,
57 X_implemented_only IN NUMBER,
58 X_unit_number IN VARCHAR2 DEFAULT NULL
59 )
60 IS
61 X_stmt_num NUMBER;
62 X_GTIN_ST_TYPE_ID NUMBER;
63 BEGIN
64
65 X_stmt_num := 400;
66
67 X_GTIN_ST_TYPE_ID := Get_GTIN_Structure_Type_Id;
68
69 UPDATE BOM_BILL_OF_MATERIALS
70 SET ASSEMBLY_TYPE = 1,
71 LAST_UPDATE_DATE = sysdate,
72 LAST_UPDATED_BY = to_number(fnd_profile.value('USER_ID'))
73 WHERE ORGANIZATION_ID = X_org_id
74 AND ASSEMBLY_ITEM_ID = X_eng_item_id
75 AND ((X_designator_option = 2 AND
76 ALTERNATE_BOM_DESIGNATOR IS NULL)
77 OR
78 (X_designator_option = 3 AND
79 ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator)
80 OR
81 X_designator_option = 1)
82 AND nvl(effectivity_control , 1) <> 4 -- Bug 4210718
83 AND (X_GTIN_ST_TYPE_ID IS NULL or STRUCTURE_TYPE_ID <> X_GTIN_ST_TYPE_ID);
84
85 X_stmt_num := 401;
86 If (PJM_UNIT_EFF.Enabled = 'Y' AND
87 PJM_UNIT_EFF.Unit_Effective_Item(
88 X_Item_ID => X_eng_item_id,
89 X_Organization_ID => X_org_id) = 'Y')
90 THEN
91 DELETE FROM BOM_INVENTORY_COMPONENTS BIC
92 WHERE ((X_implemented_only = 1 AND BIC.IMPLEMENTATION_DATE IS NULL)
93 OR (X_transfer_option = 2 AND
94 (BIC.FROM_END_ITEM_UNIT_NUMBER > X_unit_number
95 OR (BIC.FROM_END_ITEM_UNIT_NUMBER < X_unit_number
96 AND NVL(BIC.TO_END_ITEM_UNIT_NUMBER, X_unit_number)
97 < X_unit_number)))
98 OR (X_transfer_option = 3 AND
99 NVL(BIC.TO_END_ITEM_UNIT_NUMBER, X_unit_number) < X_unit_number))
100 AND BIC.BILL_SEQUENCE_ID in (SELECT BOM_T.BILL_SEQUENCE_ID
101 FROM BOM_BILL_OF_MATERIALS BOM_T
102 WHERE ORGANIZATION_ID = X_org_id
103 AND ASSEMBLY_ITEM_ID = X_eng_item_id
104 AND ASSEMBLY_TYPE = 1
105 AND ((X_designator_option = 2 AND ALTERNATE_BOM_DESIGNATOR IS NULL)
106 OR (X_designator_option = 3 AND ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator)
107 OR (X_designator_option = 1))
108 AND (X_GTIN_ST_TYPE_ID IS NULL or STRUCTURE_TYPE_ID <> X_GTIN_ST_TYPE_ID)
109 AND nvl(BOM_T.effectivity_control , 1) <> 4 -- Bug 4210718
110 );
111 ELSE
112 DELETE FROM BOM_INVENTORY_COMPONENTS BIC
113 WHERE ((X_implemented_only = 1 AND BIC.IMPLEMENTATION_DATE IS NULL)
114 OR (X_transfer_option = 2 AND
115 (BIC.EFFECTIVITY_DATE > X_effectivity_date
116 OR NVL(BIC.DISABLE_DATE, X_effectivity_date + 1)
117 <= X_effectivity_date))
118 OR (X_transfer_option = 3 AND NVL(BIC.DISABLE_DATE, X_effectivity_date +
119 1) <= X_effectivity_date))
120 AND BIC.BILL_SEQUENCE_ID in (SELECT BOM_T.BILL_SEQUENCE_ID
121 FROM BOM_BILL_OF_MATERIALS BOM_T
122 WHERE ORGANIZATION_ID = X_org_id
123 AND ASSEMBLY_ITEM_ID = X_eng_item_id
124 AND ASSEMBLY_TYPE = 1
125 AND ((X_designator_option = 2 AND ALTERNATE_BOM_DESIGNATOR IS NULL)
126 OR (X_designator_option = 3 AND ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator)
127 OR (X_designator_option = 1))
128 AND (X_GTIN_ST_TYPE_ID IS NULL or STRUCTURE_TYPE_ID <> X_GTIN_ST_TYPE_ID)
129 AND nvl(BOM_T.effectivity_control , 1) <> 4 -- Bug 4210718
130 );
131 END IF;
132 EXCEPTION
133 WHEN OTHERS THEN
134 ENG_BOM_RTG_TRANSFER_PKG.RAISE_ERROR(func_name => 'BOM_UPDATE',
135 stmt_num => X_stmt_num,
136 message_name => 'ENG_ENUBRT_ERROR',
137 token => SQLERRM);
138
139 END BOM_UPDATE;
140
141 -- +--------------------------- BOM_TRANSFER -----------------------------+
142 -- NAME
143 -- BOM_TRANSFER
144
145 -- DESCRIPTION
146 -- Transfer Bills
147
148 -- REQUIRES
149 -- org_id: organization id
150 -- eng_item_id
151 -- mfg_item_id
152 -- designator_option
153 -- 1. all
154 -- 2. primary only
155 -- 3. specific only
156 -- transfer option
157 -- 1. all rows
158 -- 2. current only
159 -- 3. current and pending
160 -- alt_bom_designator
161 -- effectivity_date
162 -- last_login_id not used internally
163 -- ecn_name
164
165 -- OUTPUT
166
167 -- NOTES
168
169 -- +--------------------------------------------------------------------------+
170
171 PROCEDURE BOM_TRANSFER
172 (
173 X_org_id IN NUMBER,
174 X_eng_item_id IN NUMBER,
175 X_mfg_item_id IN NUMBER,
176 X_designator_option IN NUMBER,
177 X_transfer_option IN NUMBER,
178 X_alt_bom_designator IN VARCHAR2,
179 X_effectivity_date IN DATE,
180 X_last_login_id IN NUMBER,
181 X_ecn_name IN VARCHAR2,
182 X_unit_number IN VARCHAR2 DEFAULT NULL
183 )
184 IS
185 X_stmt_num NUMBER;
186 X_from_bill_sequence_id NUMBER;
187 X_GTIN_ST_TYPE_ID NUMBER;
188 FLAG NUMBER;
189 CURSOR BOM_CURSOR IS
190 SELECT BILL_SEQUENCE_ID, ALTERNATE_BOM_DESIGNATOR
191 FROM BOM_BILL_OF_MATERIALS
192 WHERE ORGANIZATION_ID = X_org_id
193 AND ASSEMBLY_ITEM_ID = X_mfg_item_id
194 AND nvl(effectivity_control, 1) <> 4 -- Bug 4210718
195 AND Source_BILL_SEQUENCE_ID = BILL_SEQUENCE_ID; --R12
196
197 -- BUG 3503220
198 CURSOR BOM_COPIES IS
199 SELECT BILL_SEQUENCE_ID, ALTERNATE_BOM_DESIGNATOR, assembly_item_id, organization_id, obj_name
200 FROM BOM_BILL_OF_MATERIALS BOM_T
201 WHERE ORGANIZATION_ID = X_org_id
202 AND ASSEMBLY_ITEM_ID = X_mfg_item_id
203 AND nvl(BOM_T.effectivity_control, 1) <> 4 -- Bug 4210718
204 AND ((X_designator_option = 2 AND
205 BOM_T.ALTERNATE_BOM_DESIGNATOR IS NULL)
206 OR
207 (X_designator_option = 3 AND
208 BOM_T.ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator)
209 OR
210 (X_designator_option = 1));
211
212 BEGIN
213
214 --- BOM_BILL_OF_MATERIALS
215
216 X_stmt_num := 700;
217
218
219 BEGIN
220
221 X_GTIN_ST_TYPE_ID := Get_GTIN_Structure_Type_Id;
222
223 -- Bug 3503263 While Specific Alternate of a Bill is being copied then Primary bill of Target Item is creaed.
224 IF (x_eng_item_id<>x_mfg_item_id)and (x_designator_option = 3) then
225 FLAG := 1;
226 ELSE
227 FLAG:= 0;
228 END IF;
229 -- Bug 3523263 Bug 4240131 inserted effectivity_control in table.
230 INSERT INTO BOM_BILL_OF_MATERIALS(
231 ASSEMBLY_ITEM_ID,
232 ORGANIZATION_ID,
233 ALTERNATE_BOM_DESIGNATOR,
234 LAST_UPDATE_DATE,
235 LAST_UPDATED_BY,
236 CREATION_DATE,
237 CREATED_BY,
238 LAST_UPDATE_LOGIN,
239 COMMON_ASSEMBLY_ITEM_ID,
240 SPECIFIC_ASSEMBLY_COMMENT,
241 PENDING_FROM_ECN,
242 ATTRIBUTE_CATEGORY,
243 ATTRIBUTE1,
244 ATTRIBUTE2,
245 ATTRIBUTE3,
246 ATTRIBUTE4,
247 ATTRIBUTE5,
248 ATTRIBUTE6,
249 ATTRIBUTE7,
250 ATTRIBUTE8,
251 ATTRIBUTE9,
252 ATTRIBUTE10,
253 ATTRIBUTE11,
254 ATTRIBUTE12,
255 ATTRIBUTE13,
256 ATTRIBUTE14,
257 ATTRIBUTE15,
258 ASSEMBLY_TYPE,
259 BILL_SEQUENCE_ID,
260 COMMON_BILL_SEQUENCE_ID,
261 REQUEST_ID,
262 PROGRAM_APPLICATION_ID,
263 PROGRAM_ID,
264 PROGRAM_UPDATE_DATE,
265 COMMON_ORGANIZATION_ID,
266 NEXT_EXPLODE_DATE,
267 EFFECTIVITY_CONTROL,
268 source_bill_sequence_id, --R12
269 pk1_value, --Bug 4707618
270 pk2_value) --Bug 4707618
271 SELECT
272 X_mfg_item_id,
273 ORGANIZATION_ID,
274 BOM_T.ALTERNATE_BOM_DESIGNATOR,
275 SYSDATE,
276 to_number(Fnd_Profile.Value('USER_ID')),
277 SYSDATE,
278 to_number(Fnd_Profile.Value('USER_ID')),
279 to_number(Fnd_Profile.Value('LOGIN_ID')),
280 BOM_T.COMMON_ASSEMBLY_ITEM_ID,
281 BOM_T.SPECIFIC_ASSEMBLY_COMMENT,
282 BOM_T.PENDING_FROM_ECN,
283 BOM_T.ATTRIBUTE_CATEGORY,
284 BOM_T.ATTRIBUTE1,
285 BOM_T.ATTRIBUTE2,
286 BOM_T.ATTRIBUTE3,
287 BOM_T.ATTRIBUTE4,
288 BOM_T.ATTRIBUTE5,
289 BOM_T.ATTRIBUTE6,
290 BOM_T.ATTRIBUTE7,
291 BOM_T.ATTRIBUTE8,
292 BOM_T.ATTRIBUTE9,
293 BOM_T.ATTRIBUTE10,
294 BOM_T.ATTRIBUTE11,
295 BOM_T.ATTRIBUTE12,
296 BOM_T.ATTRIBUTE13,
297 BOM_T.ATTRIBUTE14,
298 BOM_T.ATTRIBUTE15,
299 1,
300 BOM_INVENTORY_COMPONENTS_S.NEXTVAL,
301 DECODE(BOM_T.COMMON_BILL_SEQUENCE_ID,BOM_T.BILL_SEQUENCE_ID,BOM_INVENTORY_COMPONENTS_S.CURRVAL,BOM_T.COMMON_BILL_SEQUENCE_ID),
302 BOM_T.REQUEST_ID,
303 BOM_T.PROGRAM_APPLICATION_ID,
304 BOM_T.PROGRAM_ID,
305 BOM_T.PROGRAM_UPDATE_DATE,
306 BOM_T.COMMON_ORGANIZATION_ID,
307 BOM_T.NEXT_EXPLODE_DATE,
308 BOM_T.EFFECTIVITY_CONTROL,
309 DECODE(BOM_T.COMMON_BILL_SEQUENCE_ID,BOM_T.BILL_SEQUENCE_ID,BOM_INVENTORY_COMPONENTS_S.CURRVAL,BOM_T.COMMON_BILL_SEQUENCE_ID),
310 X_mfg_item_id,
311 ORGANIZATION_ID
312 FROM BOM_BILL_OF_MATERIALS BOM_T
313 WHERE ORGANIZATION_ID = X_org_id
314 AND ASSEMBLY_ITEM_ID = X_eng_item_id
315 AND ((X_designator_option = 2 AND
316 BOM_T.ALTERNATE_BOM_DESIGNATOR IS NULL)
317 OR
318 (X_designator_option = 3 AND
319 BOM_T.ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator)
320 OR
321 (X_designator_option = 1))
322 AND nvl(BOM_T.effectivity_control , 1) <> 4 -- Bug 4210718
323 AND (X_GTIN_ST_TYPE_ID IS NULL or BOM_T.STRUCTURE_TYPE_ID <> X_GTIN_ST_TYPE_ID);
324
325
326 EXCEPTION
327 WHEN OTHERS THEN
328 ENG_BOM_RTG_TRANSFER_PKG.RAISE_ERROR(func_name => 'BOM_TRANSFER',
329 stmt_num => X_stmt_num,
330 message_name => 'ENG_ENUBRT_ERROR',
331 token => SQLERRM);
332 END;
333 -- bug 3780577 : odaboval moved the ERES call after the LOOP
334 -- in order to see the inventory components:
335 /* THIS ERES CALL IS NOT MOVED TO A PLACE BELOW.
336 -- ERES BEGIN
337 -- If there is a parent eRecord, log a child record to accompany it
338 -- ================================================================
339 IF ENG_BOM_RTG_TRANSFER_PKG.G_PARENT_ERECORD_ID is NOT NULL THEN
340 FOR BILL IN BOM_COPIES LOOP -- BUG 3503220
341 -- Log an erecord for each new bill inserted above -- BUG 3503220
342
343 ENG_BOM_RTG_TRANSFER_PKG.Process_Erecord
344 ( p_event_name =>'oracle.apps.bom.billCreate'
345 , p_event_key =>to_char(BILL.bill_sequence_id)
346 , p_user_key =>ENG_BOM_RTG_TRANSFER_PKG.G_ITEM_NAME
347 ||'-'||ENG_BOM_RTG_TRANSFER_PKG.G_ORG_CODE||'-'||BILL.alternate_bom_designator
348 , p_parent_event_key =>to_char(X_eng_item_id)||'-'||to_char(X_org_id)
349 ||'-'||to_char(X_mfg_item_id)
350 );
351 END LOOP;
352 END IF;
353 NOT USED ANYMORE. PLEASE SEE CALL BELOW. */
354 -- ERES END
355 -- ========
356
357 FOR BOM1 IN BOM_CURSOR LOOP
358
359 X_stmt_num := 701;
360 BEGIN
361 SELECT BILL_SEQUENCE_ID
362 INTO X_from_bill_sequence_id
363 FROM BOM_BILL_OF_MATERIALS
364 WHERE ORGANIZATION_ID = X_org_id
365 AND ASSEMBLY_ITEM_ID = X_eng_item_id
366 AND NVL(ALTERNATE_BOM_DESIGNATOR,'NONE') = NVL(BOM1.ALTERNATE_BOM_DESIGNATOR,'NONE');
367 EXCEPTION
368 WHEN OTHERS THEN
369 ENG_BOM_RTG_TRANSFER_PKG.RAISE_ERROR(func_name => 'BOM_TRANSFER',
370 stmt_num => X_stmt_num,
371 message_name => 'ENG_ENUBRT_ERROR',
372 token => SQLERRM);
373 END;
374
375 ENG_ITEM_PKG.COMPONENT_TRANSFER(X_org_id => X_org_id,
376 X_eng_item_id => X_eng_item_id,
377 X_designator_option => X_designator_option,
378 X_alt_bom_designator => X_alt_bom_designator);
379
380 BOM_COPY_BILL.COPY_BILL(from_sequence_id => X_from_bill_sequence_id,
381 to_sequence_id => BOM1.BILL_SEQUENCE_ID,
382 from_org_id => X_org_id,
383 to_org_id => X_org_id,
384 display_option => X_transfer_option,
385 user_id => to_number(Fnd_Profile.Value('USER_ID')),
386 to_item_id => X_mfg_item_id,
387 direction => 4,
388 to_alternate => BOM1.ALTERNATE_BOM_DESIGNATOR,
389 rev_date => X_effectivity_date,
390 e_change_notice => X_ecn_name,
391 rev_item_seq_id => NULL,
392 bill_or_eco => 1,
393 eco_eff_date => X_effectivity_date,
394 unit_number => X_unit_number,
395 from_item_id =>X_eng_item_id);
396
397 END LOOP;
398 IF FLAG=1 THEN -- 3503263
399 UPDATE BOM_BILL_OF_MATERIALS BOM1
400 SET BOM1.ALTERNATE_BOM_DESIGNATOR =NULL
401 WHERE ORGANIZATION_ID = X_org_id
402 AND ASSEMBLY_ITEM_ID = X_mfg_item_id
403 AND (X_designator_option = 3 AND
404 BOM1.ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator);
405 END IF;
406
407 -- Bug 4584490: Changes for bom business events support
408 BEGIN
409 FOR BILL IN BOM_COPIES
410 LOOP
411 Bom_Business_Event_PKG.Raise_Bill_Event(
412 p_pk1_value => Bill.assembly_item_id
413 , p_pk2_value => Bill.organization_id
414 , p_obj_name => Bill.obj_name
415 , p_structure_name => Bill.alternate_bom_designator
416 , p_organization_id => Bill.organization_id
417 , p_structure_comment => NULL
418 , p_Event_Name => Bom_Business_Event_PKG.G_STRUCTURE_CREATION_EVENT
419 );
420 END LOOP;
421 EXCEPTION
422 WHEN OTHERS THEN
423 null;
424 -- nothing is required to be done, process continues
425 END;
426 -- End Changes for bug 4584490
427 -- bug 3780577 : odaboval moved the ERES BillCreate here
428 -- in order to get the routing revisions.
429 -- ERES BEGIN
430 -- If there is a parent eRecord, log a child record to accompany it
431 -- ================================================================
432 IF ENG_BOM_RTG_TRANSFER_PKG.G_PARENT_ERECORD_ID is NOT NULL THEN
433 FOR BILL IN BOM_COPIES LOOP -- BUG 3503220
434 -- Log an erecord for each new bill inserted above -- BUG 3503220
435
436 ENG_BOM_RTG_TRANSFER_PKG.Process_Erecord
437 ( p_event_name =>'oracle.apps.bom.billCreate'
438 , p_event_key =>to_char(BILL.bill_sequence_id)
439 , p_user_key =>ENG_BOM_RTG_TRANSFER_PKG.G_ITEM_NAME
440 ||'-'||ENG_BOM_RTG_TRANSFER_PKG.G_ORG_CODE||'-'||BILL.alternate_bom_designator
441 , p_parent_event_key =>to_char(X_eng_item_id)||'-'||to_char(X_org_id)
442 ||'-'||to_char(X_mfg_item_id)
443 );
444 END LOOP;
445 END IF;
446 -- ERES END
447 -- ========
448 END BOM_TRANSFER;
449
450 END ENG_BOM_PKG;