[Home] [Help]
PACKAGE BODY: APPS.ENG_BOM_RTG_TRANSFER_PKG
Source
1 PACKAGE BODY ENG_BOM_RTG_TRANSFER_PKG AS
2 /* $Header: ENGPTRFB.pls 120.4 2006/03/06 08:13:20 prgopala noship $ */
3
4 -- +--------------------------- RAISE_ERROR ----------------------------------+
5
6 -- NAME
7 -- RAISE_ERROR
8
9 -- DESCRIPTION
10 -- Raise generic error message. For sql error failures, places the SQLERRM
11 -- error on the message stack
12
13 -- REQUIRES
14 -- func_name: function name
15 -- stmt_num : statement number
16
17 -- OUTPUT
18
19 -- NOTES
20
21 -- +--------------------------------------------------------------------------+
22
23 PROCEDURE RAISE_ERROR (
24 func_name VARCHAR2,
25 stmt_num NUMBER,
26 message_name VARCHAR2,
27 token VARCHAR2
28 )
29 IS
30 err_text VARCHAR2(1000);
31 BEGIN
32 ROLLBACK;
33 err_text := func_name || '(' || stmt_num || ') ' || token;
34 IF (message_name IS NOT NULL) THEN
35 FND_MESSAGE.SET_NAME('ENG', message_name);
36 FND_MESSAGE.SET_TOKEN('ENTITY', err_text);
37 END IF;
38 APP_EXCEPTION.RAISE_EXCEPTION;
39 END RAISE_ERROR;
40
41 -- +-------------------------- ENG_BOM_RTG_TRANSFER --------------------------+
42
43 -- NAME
44 -- ENG_BOM_RTG_TRANSFER
45
46 -- DESCRIPTION
47 -- Transfer engineering data from engineering to manufacturing
48
49 -- REQUIRES
50 -- org_id: organization id
51 -- eng_item_id
52 -- mfg_item_id
53 -- transfer_option:
54 -- 1. all rows
55 -- 2. current only
56 -- 3. current and pending
57 -- designator_option
58 -- 1. all
59 -- 2. primary only
60 -- 3. specific only
61 -- alt_bom_designator
62 -- alt_rtg_designator
63 -- effectivity_date
64 -- lastloginid not used internally just kept to support already existing usage
65 -- bom_rev_starting
66 -- rtg_rev_starting
67 -- ecn_name
68 -- item_code
69 -- 1. transfer yes
70 -- 2. transfer no
71 -- bom_code
72 -- 1. transfer yes
73 -- 2. transfer no
74 -- rtg_code
75 -- 1. transfer yes
76 -- 2. transfer no
77 -- mfg_description
78 -- segment1
79 -- segment2
80 -- segment3
81 -- segment4
82 -- segment5
83 -- segment6
84 -- segment7
85 -- segment8
86 -- segment9
87 -- segment10
88 -- segment11
89 -- segment12
90 -- segment13
91 -- segment14
92 -- segment15
93 -- segment16
94 -- segment17
95 -- segment18
96 -- segment19
97 -- segment20
98 -- implemented_only
99 -- 1. yes
100 -- 2. no
101 -- commit Introduced for BUG 3196478 OCT 2003
102 -- TRUE or FALSE
103
104 -- OUTPUT
105
106 -- RETURNS
107
108 -- NOTES
109
110 -- +--------------------------------------------------------------------------+
111
112 -- BUG 3196478
113 -- Introduce parameter X_commit to give added control over commit handling
114 PROCEDURE ENG_BOM_RTG_TRANSFER
115 (
116 X_org_id IN NUMBER,
117 X_eng_item_id IN NUMBER,
118 X_mfg_item_id IN NUMBER,
119 X_transfer_option IN NUMBER DEFAULT 2,
120 X_designator_option IN NUMBER DEFAULT 1,
121 X_alt_bom_designator IN VARCHAR2,
122 X_alt_rtg_designator IN VARCHAR2,
123 X_effectivity_date IN DATE,
124 X_last_login_id IN NUMBER DEFAULT -1,
125 X_bom_rev_starting IN VARCHAR2,
126 X_rtg_rev_starting IN VARCHAR2,
127 X_ecn_name IN VARCHAR2,
128 X_item_code IN NUMBER DEFAULT 1,
129 X_bom_code IN NUMBER DEFAULT 1,
130 X_rtg_code IN NUMBER DEFAULT 1,
131 X_mfg_description IN VARCHAR2,
132 X_segment1 IN VARCHAR2,
133 X_segment2 IN VARCHAR2,
134 X_segment3 IN VARCHAR2,
135 X_segment4 IN VARCHAR2,
136 X_segment5 IN VARCHAR2,
137 X_segment6 IN VARCHAR2,
138 X_segment7 IN VARCHAR2,
139 X_segment8 IN VARCHAR2,
140 X_segment9 IN VARCHAR2,
141 X_segment10 IN VARCHAR2,
142 X_segment11 IN VARCHAR2,
143 X_segment12 IN VARCHAR2,
144 X_segment13 IN VARCHAR2,
145 X_segment14 IN VARCHAR2,
146 X_segment15 IN VARCHAR2,
147 X_segment16 IN VARCHAR2,
148 X_segment17 IN VARCHAR2,
149 X_segment18 IN VARCHAR2,
150 X_segment19 IN VARCHAR2,
151 X_segment20 IN VARCHAR2,
152 X_implemented_only IN NUMBER DEFAULT 2,
153 X_unit_number IN VARCHAR2 DEFAULT NULL,
154 X_commit IN BOOLEAN DEFAULT TRUE
155 )
156 IS
157 X_identical NUMBER; -- eng_item and mfg_item are the same
158 -- 1 = yes, 2 = no
159 X_end EXCEPTION;
160 l_return_status VARCHAR2(1); -- ERES
161 l_msg_count NUMBER; -- ERES
162 l_msg_data VARCHAR2(2000); -- ERES
163
164 -- Bug#3196367.
165 l_Common_Org_Id NUMBER;
166 l_Common_Assembly_Item_Id NUMBER;
167 l_Common_Bill_Sequence_Id NUMBER;
168 -- l_Common_Alternate BOM_ALTERNATE_DESIGNATORS.ALTERNATE_DESIGNATOR_CODE%TYPE;
169 -- Common Item Cursor to fetch the distinct items
170 CURSOR common_item_csr IS
171 -- Get DISTINCT items. This is only item transfer
172 SELECT DISTINCT NVL(COMMON_ASSEMBLY_ITEM_ID,ASSEMBLY_ITEM_ID) COMMON_ASSEMBLY_ITEM_ID,
173 NVL(COMMON_ORGANIZATION_ID,ORGANIZATION_ID) COMMON_ORG_ID
174 FROM BOM_BILL_OF_MATERIALS BOM
175 WHERE BOM.ORGANIZATION_ID = X_org_id
176 AND BOM.ASSEMBLY_ITEM_ID = X_eng_item_id
177 AND nvl(bom.effectivity_control, 1) <> 4 -- Bug 4210718
178 AND ((X_designator_option = 2 AND
179 BOM.ALTERNATE_BOM_DESIGNATOR IS NULL)
180 OR (X_designator_option = 3 AND
181 BOM.ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator)
182 OR (X_designator_option = 1));
183 CURSOR other_reference_item_csr IS
184 -- Get DISTINCT items. This is only item transfer Added
185 --AND RBOM.COMMON_BILL_SEQUENCE_ID = NVL(CBOM.COMMON_BILL_SEQUENCE_ID,CBOM.BILL_SEQUENCE_ID)
186 --for performance issue.
187 SELECT DISTINCT RBOM.ASSEMBLY_ITEM_ID ASSEMBLY_ITEM_ID, RBOM.ORGANIZATION_ID ORG_ID
188 FROM BOM_BILL_OF_MATERIALS RBOM, MTL_SYSTEM_ITEMS_B MST, BOM_BILL_OF_MATERIALS CBOM
189 WHERE CBOM.ORGANIZATION_ID = X_org_id
190 AND CBOM.ASSEMBLY_ITEM_ID = X_eng_item_id
191 AND ((X_designator_option = 2 AND
192 CBOM.ALTERNATE_BOM_DESIGNATOR IS NULL)
193 OR (X_designator_option = 3 AND
194 CBOM.ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator)
195 OR (X_designator_option = 1))
196 AND RBOM.COMMON_ASSEMBLY_ITEM_ID = NVL(CBOM.COMMON_ASSEMBLY_ITEM_ID,CBOM.ASSEMBLY_ITEM_ID)
197 AND RBOM.COMMON_ORGANIZATION_ID = NVL(CBOM.COMMON_ORGANIZATION_ID,CBOM.ORGANIZATION_ID)
198 AND RBOM.COMMON_BILL_SEQUENCE_ID = NVL(CBOM.COMMON_BILL_SEQUENCE_ID,CBOM.BILL_SEQUENCE_ID)
199 AND NVL(RBOM.ALTERNATE_BOM_DESIGNATOR,'PRIMARY') = NVL(CBOM.ALTERNATE_BOM_DESIGNATOR,'PRIMARY')
200 AND MST.INVENTORY_ITEM_ID = RBOM.ASSEMBLY_ITEM_ID
201 AND MST.ORGANIZATION_ID = RBOM.ORGANIZATION_ID
202 AND nvl(rbom.effectivity_control, 1) <> 4 -- Bug 4210718
203 AND MST.ENG_ITEM_FLAG = 'Y'; -- Fetch only engineering items. Because it could have been just transferred.
204 -- Cursor will fetch either the source bill or the bill which is not commoned.
205 CURSOR common_bill_csr IS
206 SELECT NVL(COMMON_ASSEMBLY_ITEM_ID,ASSEMBLY_ITEM_ID) COMMON_ASSEMBLY_ITEM_ID, NVL(COMMON_ORGANIZATION_ID,ORGANIZATION_ID) COMMON_ORG_ID,
207 SOURCE_BILL_SEQUENCE_ID, ALTERNATE_BOM_DESIGNATOR
208 FROM BOM_BILL_OF_MATERIALS BOM
209 WHERE BOM.ORGANIZATION_ID = X_org_id
210 AND BOM.ASSEMBLY_ITEM_ID = X_eng_item_id
211 AND ((X_designator_option = 2 AND
212 BOM.ALTERNATE_BOM_DESIGNATOR IS NULL)
213 OR (X_designator_option = 3 AND
214 BOM.ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator)
215 OR (X_designator_option = 1));
216 -- Cursor to fetch all the reference bill if the bill to be transfered is common or reference bill.
217 /* CURSOR reference_bill_csr(cp_Common_Org_Id NUMBER, cp_Common_Assembly_Item_Id NUMBER) IS
218 SELECT ASSEMBLY_ITEM_ID ASSEMBLY_ITEM_ID, ORGANIZATION_ID ORG_ID
219 FROM BOM_BILL_OF_MATERIALS BOM
220 WHERE BOM.COMMON_ORGANIZATION_ID = cp_Common_Org_Id
221 AND BOM.COMMON_ASSEMBLY_ITEM_ID = cp_Common_Assembly_Item_Id -- Fix for bug 3519193. Changed the cursor parameter name
222 AND ((X_designator_option = 2 AND
223 BOM.ALTERNATE_BOM_DESIGNATOR IS NULL)
224 OR (X_designator_option = 3 AND
225 BOM.ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator)
226 OR (X_designator_option = 1))
227 AND BOM.BILL_SEQUENCE_ID <> BOM.COMMON_BILL_SEQUENCE_ID; -- Make sure source not fetched
228 */
229 CURSOR other_reference_bill_csr( cp_Source_Bill_Sequence_Id NUMBER, cp_Assembly_Item_Id NUMBER) IS
230 SELECT ASSEMBLY_ITEM_ID ASSEMBLY_ITEM_ID, ORGANIZATION_ID ORG_ID
231 FROM BOM_BILL_OF_MATERIALS BOM
232 WHERE BOM.Source_BILL_SEQUENCE_ID = cp_Source_Bill_Sequence_Id
233 AND BOM.BILL_SEQUENCE_ID <> BOM.Source_BILL_SEQUENCE_ID; -- Make sure source not fetched
234 -- End Bug#3196367.
235 BEGIN
236
237 -- if somehow a row is passed with all transfer options = no, return.
238 IF ( X_item_code = 2 and X_bom_code = 2 and X_rtg_code = 2) THEN
239 RAISE X_end; -- clean exit from package
240 END IF;
241
242 IF ( X_eng_item_id = X_mfg_item_id ) THEN -- no name changes
243 X_identical := 1;
244 END IF;
245
246 -- call ITEM_TRANSFER function when item is engineering item.
247
248 IF ( X_item_code = 1 ) THEN
249 -- Bug#3503220
250 -- First Transfer the Item from which the transfer is being called.
251 ENG_ITEM_PKG.ITEM_TRANSFER(X_org_id => X_org_id,
252 X_eng_item_id => X_eng_item_id,
253 X_mfg_item_id => X_mfg_item_id,
254 x_last_login_id => -1,
255 X_mfg_description => X_mfg_description,
256 X_ecn_name => X_ecn_name,
257 X_bom_rev_starting => X_bom_rev_starting,
258 X_segment1 => X_segment1,
259 X_segment2 => X_segment2,
260 X_segment3 => X_segment3,
261 X_segment4 => X_segment4,
262 X_segment5 => X_segment5,
263 X_segment6 => X_segment6,
264 X_segment7 => X_segment7,
265 X_segment8 => X_segment8,
266 X_segment9 => X_segment9,
267 X_segment10 => X_segment10,
268 X_segment11 => X_segment11,
269 X_segment12 => X_segment12,
270 X_segment13 => X_segment13,
271 X_segment14 => X_segment14,
272 X_segment15 => X_segment15,
273 X_segment16 => X_segment16,
274 X_segment17 => X_segment17,
275 X_segment18 => X_segment18,
276 X_segment19 => X_segment19,
277 X_segment20 => X_segment20);
278 -- Bug#3196367
279 -- After transferring the item from which the transfer is called transfer the items for
280 -- the common bills which it refers. This should happen only when it is a bill transfer.
281 IF ( X_bom_code = 1 AND X_eng_item_id = X_mfg_item_id) THEN
282 FOR common_rec IN common_item_csr LOOP
283 l_Common_Org_Id := common_rec.COMMON_ORG_ID;
284 l_Common_Assembly_Item_Id := common_rec.COMMON_ASSEMBLY_ITEM_ID;
285 -- Call transfer only if Common Item and X_eng_item_id are different
286 -- Because the X_eng_item_id is already transferred in the beginning.
287 IF ( l_Common_Assembly_Item_Id <> X_eng_item_id ) THEN
288 ENG_ITEM_PKG.ITEM_TRANSFER(X_org_id => l_Common_Org_Id,
289 X_eng_item_id => l_Common_Assembly_Item_Id,
290 X_mfg_item_id => l_Common_Assembly_Item_Id,
291 x_last_login_id => -1,
292 X_mfg_description => X_mfg_description,
293 X_ecn_name => X_ecn_name,
294 X_bom_rev_starting => X_bom_rev_starting,
295 X_segment1 => X_segment1,
296 X_segment2 => X_segment2,
297 X_segment3 => X_segment3,
298 X_segment4 => X_segment4,
299 X_segment5 => X_segment5,
300 X_segment6 => X_segment6,
301 X_segment7 => X_segment7,
302 X_segment8 => X_segment8,
303 X_segment9 => X_segment9,
304 X_segment10 => X_segment10,
305 X_segment11 => X_segment11,
306 X_segment12 => X_segment12,
307 X_segment13 => X_segment13,
308 X_segment14 => X_segment14,
309 X_segment15 => X_segment15,
310 X_segment16 => X_segment16,
311 X_segment17 => X_segment17,
312 X_segment18 => X_segment18,
313 X_segment19 => X_segment19,
314 X_segment20 => X_segment20);
315 END IF;
316 -- Transfer the Items which are referencing the common bill but it should not be the item from
317 -- which the transfer is being called. X_eng_item_id takes care of the above check.
318 FOR other_reference_rec IN other_reference_item_csr LOOP
319 ENG_ITEM_PKG.ITEM_TRANSFER(X_org_id => other_reference_rec.ORG_ID,
320 X_eng_item_id => other_reference_rec.ASSEMBLY_ITEM_ID,
321 X_mfg_item_id => other_reference_rec.ASSEMBLY_ITEM_ID,
322 x_last_login_id => -1,
323 X_mfg_description => X_mfg_description,
324 X_ecn_name => X_ecn_name,
325 X_bom_rev_starting => X_bom_rev_starting,
326 X_segment1 => X_segment1,
327 X_segment2 => X_segment2,
328 X_segment3 => X_segment3,
329 X_segment4 => X_segment4,
330 X_segment5 => X_segment5,
331 X_segment6 => X_segment6,
332 X_segment7 => X_segment7,
333 X_segment8 => X_segment8,
334 X_segment9 => X_segment9,
335 X_segment10 => X_segment10,
336 X_segment11 => X_segment11,
337 X_segment12 => X_segment12,
338 X_segment13 => X_segment13,
339 X_segment14 => X_segment14,
340 X_segment15 => X_segment15,
341 X_segment16 => X_segment16,
342 X_segment17 => X_segment17,
343 X_segment18 => X_segment18,
344 X_segment19 => X_segment19,
345 X_segment20 => X_segment20);
346 END LOOP; -- Loop ends for referencing item transfer
347 END LOOP; -- Loop ends for common item transfer
348 -- Fetch all the reference bills and transfer the corresponding items.
349 END IF; -- FOR IF ( X_bom_code = 1 ) -- Bug#3196367
350
351 -- ERES BEGIN
352 -- ==========
353 IF NVL(x_identical,2) = 2 THEN
354 -- determine eRecord_ID of parent eRecord
355 -- if ERES is not in use, this might be NULL
356 -- =========================================
357 QA_EDR_STANDARD.Get_Erecord_ID
358 ( p_api_version => 1.0
359 , p_init_msg_list => FND_API.G_TRUE
360 , x_return_status => l_return_status
361 , x_msg_count => l_msg_count
362 , x_msg_data => l_msg_data
363 , p_event_name => 'oracle.apps.eng.copyToManufacturing'
364 , p_event_key => to_char(X_eng_item_id)||'-'||to_char(X_org_id)||'-'||to_char(X_mfg_item_id)
365 , x_erecord_id => G_PARENT_ERECORD_ID
366 );
367 -- invoke eRecord logging
368 -- ======================
369 IF G_PARENT_ERECORD_ID is NOT NULL THEN
370 -- Retrieve the organization code needed as part of the user key
371 -- =============================================================
372 select organization_code into G_ORG_CODE
373 from mtl_parameters
374 where organization_id = X_org_id;
375
376 -- BUG 3503220 - Ensure item name reflects new copy
377 -- ================================================
378 select concatenated_segments into G_ITEM_NAME
379 from mtl_system_items_kfv
380 where inventory_item_id = X_mfg_item_id AND organization_id = X_org_id;
381
382
383 ENG_BOM_RTG_TRANSFER_PKG.Process_Erecord
384 ( p_event_name =>'oracle.apps.inv.itemCreate'
385 , p_event_key =>to_char(X_org_id)||'-'||to_char(X_mfg_item_id)
386 , p_user_key =>G_ORG_CODE||'-'||G_ITEM_NAME
387 , p_parent_event_key => to_char(X_eng_item_id)||'-'||to_char(X_org_id)||'-'||to_char(X_mfg_item_id)
388 );
389 END IF;
390 END IF;
391 -- ERES END
392 -- ========
393 END IF;
394
395 -- at this point, all items have been transferred to manufacturing, and
396 -- all revisions for bill/item have been set by ITEM_TRANSFER function.
397 --
398 -- if items are already manufacturing items when passed from the form,
399 -- then need to check the revision. if new rev means user wants to put
400 -- in new revision.
401
402 IF ( X_rtg_code = 1 ) THEN
403
404 IF ( X_rtg_rev_starting IS NOT null ) THEN
405
406 ENG_COPY_TABLE_ROWS_PKG.C_MTL_RTG_ITEM_REVISIONS(X_inventory_item_id => X_mfg_item_id,
407 X_organization_id => X_org_id,
408 X_process_revision => X_rtg_rev_starting,
409 X_last_update_date => SYSDATE,
410 X_last_updated_by => to_number(Fnd_Profile.Value('USER_ID')),
411 X_creation_date => SYSDATE,
412 X_created_by => to_number(Fnd_Profile.Value('USER_ID')),
413 X_last_update_login => to_number(Fnd_Profile.Value('LOGIN_ID')),
414 X_effectivity_date => SYSDATE,
415 X_change_notice => X_ecn_name,
416 X_implementation_date => SYSDATE);
417
418 END IF;
419
420 IF ( X_identical = 1 ) THEN
421
422 -- change routing type to 1 (mfg)
423
424 ENG_ROUTING_PKG.ROUTING_UPDATE(X_org_id => X_org_id,
425 X_eng_item_id => X_eng_item_id,
426 X_designator_option => X_designator_option,
427 X_transfer_option => X_transfer_option,
428 X_alt_rtg_designator => X_alt_rtg_designator,
429 X_effectivity_date => X_effectivity_date);
430 ELSE
431
432 ENG_ROUTING_PKG.ROUTING_TRANSFER(X_org_id => X_org_id,
433 X_eng_item_id => X_eng_item_id,
434 X_mfg_item_id => X_mfg_item_id,
435 X_designator_option => X_designator_option,
436 X_transfer_option => X_transfer_option,
437 X_alt_rtg_designator => X_alt_rtg_designator,
438 X_effectivity_date => X_effectivity_date,
439 x_last_login_id => -1,
440 X_ecn_name => X_ecn_name);
441
442 END IF; -- end of IF ( X_identical = 1 ) THEN
443
444 END IF; -- end of IF ( X_rtg_code = 1 ) THEN
445
446 IF ( X_bom_code = 1 ) THEN
447
448 IF ( X_bom_rev_starting IS NOT null AND X_item_code = 2 ) THEN
449
450 ENG_COPY_TABLE_ROWS_PKG.C_MTL_ITEM_REVISIONS(X_inventory_item_id => X_mfg_item_id,
451 X_organization_id => X_org_id,
452 X_revision => X_bom_rev_starting,
453 X_last_update_date => SYSDATE,
454 X_last_updated_by => to_number(Fnd_Profile.Value('USER_ID')),
455 X_creation_date => SYSDATE,
456 X_created_by => to_number(Fnd_Profile.Value('USER_ID')),
457 X_last_update_login => to_number(Fnd_Profile.Value('LOGIN_ID')),
458 X_effectivity_date => SYSDATE,
459 X_change_notice => X_ecn_name,
460 X_implementation_date => SYSDATE);
461
462 END IF;
463
464 IF ( X_identical = 1 ) THEN
465
466 -- change assembly type to 1 (mfg)
467 -- Bug#3196367 Starts.
468 -- Always transfer the bill from which the transfer is called.
469 -- If it is a common bill although component transfer is called it transfers 0 components
470 -- if the bill which is calling references some other bill.
471 ENG_BOM_PKG.BOM_UPDATE(X_org_id => X_org_id,
472 X_eng_item_id => X_eng_item_id,
473 X_designator_option => X_designator_option,
474 X_transfer_option => X_transfer_option,
475 X_alt_bom_designator => X_alt_bom_designator,
476 X_effectivity_date => X_effectivity_date,
477 X_implemented_only => X_implemented_only,
478 X_unit_number => X_unit_number);
479 ENG_ITEM_PKG.COMPONENT_TRANSFER(X_org_id => X_org_id,
480 X_eng_item_id => X_eng_item_id,
481 X_designator_option => X_designator_option,
482 X_alt_bom_designator => X_alt_bom_designator);
483
484 ENG_ITEM_PKG.SET_OP_SEQ(X_org_id => X_org_id,
485 X_item_id => X_eng_item_id,
486 X_designator_option => X_designator_option,
487 X_alt_bom_designator => X_alt_bom_designator);
488 FOR common_rec IN common_bill_csr LOOP
489 IF ( common_rec.COMMON_ASSEMBLY_ITEM_ID <> X_eng_item_id) THEN
490 -- If Primary needs to be transferred then Designator Option is 2
491 IF ( common_rec.ALTERNATE_BOM_DESIGNATOR IS NULL ) THEN
492 ENG_BOM_PKG.BOM_UPDATE(X_org_id => common_rec.COMMON_ORG_ID,
493 X_eng_item_id => common_rec.COMMON_ASSEMBLY_ITEM_ID,
494 -- For common bill always transfer particular alternate only.
495 X_designator_option => 2,
496 X_transfer_option => X_transfer_option,
497 -- To transfer particular alternate pass alternate from the cursor
498 X_alt_bom_designator => common_rec.ALTERNATE_BOM_DESIGNATOR,
499 X_effectivity_date => X_effectivity_date,
500 X_implemented_only => X_implemented_only,
501 X_unit_number => X_unit_number);
502 ENG_ITEM_PKG.COMPONENT_TRANSFER(X_org_id => common_rec.COMMON_ORG_ID,
503 X_eng_item_id => common_rec.COMMON_ASSEMBLY_ITEM_ID,
504 -- For common bill always transfer particular alternate only.
505 X_designator_option => 2,
506 -- To transfer particular alternate pass alternate from the cursor
507 X_alt_bom_designator => common_rec.ALTERNATE_BOM_DESIGNATOR);
508
509 ENG_ITEM_PKG.SET_OP_SEQ(X_org_id => common_rec.COMMON_ORG_ID,
510 X_item_id => common_rec.COMMON_ASSEMBLY_ITEM_ID,
511 -- For common bill always transfer particular alternate only.
512 X_designator_option => 2,
513 -- To transfer particular alternate pass alternate from the cursor
514 X_alt_bom_designator => common_rec.ALTERNATE_BOM_DESIGNATOR);
515 ELSE
516 -- If Specific Alternate needs to be transferred then Designator Option is 3
517 ENG_BOM_PKG.BOM_UPDATE(X_org_id => common_rec.COMMON_ORG_ID,
518 X_eng_item_id => common_rec.COMMON_ASSEMBLY_ITEM_ID,
519 -- For common bill always transfer particular alternate only.
520 X_designator_option => 3,
521 X_transfer_option => X_transfer_option,
522 -- To transfer particular alternate pass alternate from the cursor
523 X_alt_bom_designator => common_rec.ALTERNATE_BOM_DESIGNATOR,
524 X_effectivity_date => X_effectivity_date,
525 X_implemented_only => X_implemented_only,
526 X_unit_number => X_unit_number);
527 ENG_ITEM_PKG.COMPONENT_TRANSFER(X_org_id => common_rec.COMMON_ORG_ID,
528 X_eng_item_id => common_rec.COMMON_ASSEMBLY_ITEM_ID,
529 -- For common bill always transfer particular alternate only.
530 X_designator_option => 3,
531 -- To transfer particular alternate pass alternate from the cursor
532 X_alt_bom_designator => common_rec.ALTERNATE_BOM_DESIGNATOR);
533
534 ENG_ITEM_PKG.SET_OP_SEQ(X_org_id => common_rec.COMMON_ORG_ID,
535 X_item_id => common_rec.COMMON_ASSEMBLY_ITEM_ID,
536 -- For common bill always transfer particular alternate only.
537 X_designator_option => 3,
538 -- To transfer particular alternate pass alternate from the cursor
539 X_alt_bom_designator => common_rec.ALTERNATE_BOM_DESIGNATOR);
540
541 END IF;
542 END IF;
543 -- If there are references then just update the reference bills alone. Because already source
544 -- have been copied.
545 FOR other_reference_rec IN other_reference_bill_csr(common_rec.Source_BILL_SEQUENCE_ID, X_eng_item_id) LOOP
546 -- For reference bills just transfer the bill.
547 -- Components and Operation Sequences are transferred as part of common bill.
548 -- If Primary needs to be transferred then Designator Option is 2
549 IF ( common_rec.ALTERNATE_BOM_DESIGNATOR IS NULL ) THEN
550 ENG_BOM_PKG.BOM_UPDATE(X_org_id => other_reference_rec.ORG_ID,
551 X_eng_item_id => other_reference_rec.ASSEMBLY_ITEM_ID,
552 -- For reference bill always transfer particular alternate only.
553 X_designator_option => 2,
554 X_transfer_option => X_transfer_option,
555 -- To transfer particular alternate pass alternate from the cursor
556 X_alt_bom_designator => common_rec.ALTERNATE_BOM_DESIGNATOR,
557 X_effectivity_date => X_effectivity_date,
558 X_implemented_only => X_implemented_only,
559 X_unit_number => X_unit_number);
560 -- R12: Added To handle editable common bills
561 ENG_ITEM_PKG.COMPONENT_TRANSFER(X_org_id => other_reference_rec.ORG_ID,
562 X_eng_item_id => other_reference_rec.ASSEMBLY_ITEM_ID,
563 X_designator_option => 2,
564 X_alt_bom_designator => common_rec.ALTERNATE_BOM_DESIGNATOR);
565
566 ENG_ITEM_PKG.SET_OP_SEQ(X_org_id => other_reference_rec.ORG_ID,
567 X_item_id => other_reference_rec.ASSEMBLY_ITEM_ID,
568 X_designator_option => 2,
569 X_alt_bom_designator => common_rec.ALTERNATE_BOM_DESIGNATOR);
570 ELSE
571 -- If Specific Alternate needs to be transferred then Designator Option is 3
572 ENG_BOM_PKG.BOM_UPDATE(X_org_id => other_reference_rec.ORG_ID,
573 X_eng_item_id => other_reference_rec.ASSEMBLY_ITEM_ID,
574 -- For reference bill always transfer particular alternate only.
575 X_designator_option => 3,
576 X_transfer_option => X_transfer_option,
577 -- To transfer particular alternate pass alternate from the cursor
578 X_alt_bom_designator => common_rec.ALTERNATE_BOM_DESIGNATOR,
579 X_effectivity_date => X_effectivity_date,
580 X_implemented_only => X_implemented_only,
581 X_unit_number => X_unit_number);
582 -- R12: Added To handle editable common bills
583 ENG_ITEM_PKG.COMPONENT_TRANSFER(X_org_id => other_reference_rec.ORG_ID,
584 X_eng_item_id => other_reference_rec.ASSEMBLY_ITEM_ID,
585 X_designator_option => 3,
586 X_alt_bom_designator => common_rec.ALTERNATE_BOM_DESIGNATOR);
587
588 ENG_ITEM_PKG.SET_OP_SEQ(X_org_id => other_reference_rec.ORG_ID,
589 X_item_id => other_reference_rec.ASSEMBLY_ITEM_ID,
590 X_designator_option => 3,
591 X_alt_bom_designator => common_rec.ALTERNATE_BOM_DESIGNATOR);
592 END IF;
593
594 END LOOP; -- Loop ends for reference bills for a particular common bill.
595 END LOOP; -- Loop ends for Common Bill.
596 -- Bug#3196367 Ends
597 ELSE
598 ENG_BOM_PKG.BOM_TRANSFER(X_org_id => X_org_id,
599 X_eng_item_id => X_eng_item_id,
600 X_mfg_item_id => X_mfg_item_id,
601 x_last_login_id => -1,
602 X_designator_option => X_designator_option,
603 X_transfer_option => X_transfer_option,
604 X_alt_bom_designator => X_alt_bom_designator,
605 X_effectivity_date => X_effectivity_date,
606 X_ecn_name => X_ecn_name,
607 X_unit_number => X_unit_number);
608
609 /*
610 ENG_ITEM_PKG.COMPONENT_TRANSFER(X_org_id => X_org_id,
611 X_eng_item_id => X_eng_item_id,
612 X_designator_option => X_designator_option,
613 X_alt_bom_designator => X_alt_bom_designator);
614 */
615
616 ENG_ITEM_PKG.SET_OP_SEQ(X_org_id => X_org_id,
617 X_item_id => X_mfg_item_id,
618 X_designator_option => X_designator_option,
619 X_alt_bom_designator => X_alt_bom_designator);
620
621 END IF; -- end of IF ( X_identical = 1 ) THEN
622
623 END IF; -- end of IF ( X_bom_code = 1 ) THEN
624
625 -- BUG 3196478
626 -- Introduce parameter to control commit handling.
627 -- Default is TRUE
628 IF X_commit
629 THEN
630 COMMIT;
631 END IF;
632 -- BUG 3196478 END
633
634 EXCEPTION
635 WHEN X_end THEN
636 null;
637
638 END ENG_BOM_RTG_TRANSFER;
639
640 ----------------------------- Procedure ---------------------------------
641 --
642 -- NAME
643 -- Process_Erecord
644 -- DESCRIPTION
645 -- Log an electronic record for a child event associated to a parent event
646 -- of 'oracle.apps.eng.copyToManufacturing';
647 -- REQUIRES
648 -- p_event_name Child event name
649 -- p_event_key Child event key
650 -- p_user_key Child event user key
651 -- p_parent_event_key Parent event key
652 -- MODIFIES
653 -- Adds row to edr_psig_documents
654 -- RETURNS
655 --
656 -- NOTES
657 -- kxhunt 15/OCT/2003
658 -- Update call to RAISE_ERES_EVENT following denormalization
659 -- of input params.
660
661 PROCEDURE Process_Erecord( p_event_name IN VARCHAR2
662 , p_event_key IN VARCHAR2
663 , p_user_key IN VARCHAR2
664 , p_parent_event_key IN VARCHAR2)
665 IS
666 l_event QA_EDR_STANDARD.ERES_EVENT_REC_TYPE;
667 l_children QA_EDR_STANDARD.ERECORD_ID_TBL_TYPE;
668 l_return_status VARCHAR2(1);
669 l_msg_count NUMBER;
670 l_msg_index NUMBER;
671 l_msg_data VARCHAR2(2000);
672 l_event_status VARCHAR2(20);
673 l_trans_status VARCHAR2(30);
674 l_overall_status VARCHAR2(20);
675 l_erecord_id NUMBER;
676 l_statement_num NUMBER;
677 l_ackn_by VARCHAR2(80);
678 i pls_integer;
679
680 BEGIN
681
682 l_statement_num := 100;
683 /* BUG 3237159
684 Ensure labelling is consistent across the application
685 =====================================================*/
686 IF p_event_name = 'oracle.apps.inv.itemCreate' THEN
687 FND_MESSAGE.SET_NAME('INV', 'INV_ERES_ORG_ITEM_KEY_LABEL');
688 ELSIF p_event_name = 'oracle.apps.bom.billCreate' THEN
689 FND_MESSAGE.SET_NAME('BOM', 'BOM_ERES_BILL_USER_KEY');
690 ELSIF p_event_name = 'oracle.apps.bom.routingCreate' THEN
691 FND_MESSAGE.SET_NAME('BOM', 'BOM_ERES_ROUTING_USER_KEY');
692 END IF;
693
694
695 -- This is a child event. Set up the payload
696 -- ==========================================
697
698 l_event.param_name_1 := 'DEFERRED';
699 l_event.param_value_1 := 'Y';
700
701 l_event.param_name_2 := 'POST_OPERATION_API';
702 l_event.param_value_2 := NULL;
703
704 l_event.param_name_3 := 'PSIG_USER_KEY_LABEL';
705 l_event.param_value_3 := FND_MESSAGE.GET;
706
707 l_event.param_name_4 := 'PSIG_USER_KEY_VALUE';
708 l_event.param_value_4 := p_user_key;
709 l_event.param_name_5 := 'PSIG_TRANSACTION_AUDIT_ID';
710 l_event.param_value_5 := '-1';
711
712 l_event.param_name_6 := '#WF_SOURCE_APPLICATION_TYPE';
713 l_event.param_value_6 := 'DB';
714
715 l_event.param_name_7 := '#WF_SIGN_REQUESTER';
716 l_event.param_value_7 := FND_GLOBAL.USER_NAME;
717
718 --associate the parent
719 --====================
720 l_event.param_name_10 := 'PARENT_EVENT_NAME';
721 l_event.param_value_10 := 'oracle.apps.eng.copyToManufacturing';
722
723 l_event.param_name_11 := 'PARENT_EVENT_KEY';
724 l_event.param_value_11 := p_parent_event_key;
725
726 l_event.param_name_12 := 'PARENT_ERECORD_ID';
727 l_event.param_value_12 := TO_CHAR(G_PARENT_ERECORD_ID);
728
729 --Load up the EVENT
730 --=================
731 l_event.event_name := p_event_name;
732 l_event.event_key := p_event_key;
733
734 --Raise the event
735 --===============
736 l_statement_num := 200;
737 QA_EDR_STANDARD.Raise_ERES_Event
738 (p_api_version => 1.0
739 ,p_init_msg_list =>FND_API.G_TRUE
740 ,p_validation_level =>FND_API.G_VALID_LEVEL_FULL
741 ,x_return_status =>l_return_status
742 ,x_msg_count =>l_msg_count
743 ,x_msg_data =>l_msg_data
744 ,p_child_erecords =>l_children
745 ,x_event =>l_event
746 );
747
748 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
749 APP_EXCEPTION.RAISE_EXCEPTION;
750 END IF;
751
752 l_erecord_id := l_event.erecord_id;
753 l_event_status := l_event.event_status;
754
755 --Acknowledge
756 --===========
757 -- BUG 3201850 - incorporate status of NOACTION
758 IF l_event_status in ('COMPLETE', 'PENDING', 'NOACTION', 'SUCCESS') THEN
759 l_trans_status := 'SUCCESS';
760 ELSE
761 l_trans_status := 'ERROR';
762 END IF;
763
764 FND_MESSAGE.Set_Name('ENG','ENG_COPY_TITLE');
765 l_ackn_by := FND_MESSAGE.Get;
766
767 IF l_erecord_id is not null THEN
768 l_statement_num := 200;
769 QA_EDR_STANDARD.Send_Ackn
770 (p_api_version => 1.0
771 ,p_init_msg_list => FND_API.G_TRUE
772 ,x_return_status => l_return_status
773 ,x_msg_count => l_msg_count
774 ,x_msg_data => l_msg_data
775 ,p_event_name => p_event_name
776 ,p_event_key => p_event_key
777 ,p_erecord_id => l_erecord_id
778 ,p_trans_status => l_trans_status
779 ,p_ackn_by => l_ackn_by
780 ,p_ackn_note => NULL
781 ,p_autonomous_commit => FND_API.G_FALSE
782 );
783 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
784 APP_EXCEPTION.RAISE_EXCEPTION;
785 END IF;
786 END IF;
787
788 EXCEPTION
789 WHEN OTHERS THEN
790 ENG_BOM_RTG_TRANSFER_PKG.RAISE_ERROR(func_name => 'ENG_BOM_RTG_TRANSFER_PKG.Process_Erecord',
791 stmt_num => l_statement_num,
792 message_name => 'ENG_ENUBRT_ERROR',
793 token => SQLERRM);
794
795 END Process_Erecord;
796
797 END ENG_BOM_RTG_TRANSFER_PKG;