[Home] [Help]
PACKAGE BODY: APPS.WSMPPCPD
Source
1 PACKAGE BODY WSMPPCPD AS
2 /* $Header: WSMPCPDB.pls 120.5 2006/09/14 05:23:10 sisankar noship $ */
3
4 g_org_id NUMBER := NULL;
5
6 /* ===========================================================================
7
8 PROCEDURE NAME: insert_bill
9
10 =========================================================================== */
11
12 PROCEDURE insert_bill (x_rec IN OUT NOCOPY bom_bill_of_mtls_interface%ROWTYPE,
13 x_assembly_item_name IN VARCHAR2 DEFAULT NULL,
14 x_organization_code IN VARCHAR2 DEFAULT NULL,
15 x_error_code IN OUT NOCOPY NUMBER,
16 x_error_msg IN OUT NOCOPY VARCHAR2) IS
17
18 x_progress VARCHAR2(3) := NULL;
19 e_insert_bill EXCEPTION;
20 x_process_flag NUMBER := 1;
21
22 BEGIN
23
24 x_progress := '010';
25
26 /* Verify that the required arguments are being passed in. */
27 IF ((x_assembly_item_name is NULL) OR
28 (x_organization_code is NULL) OR
29 (x_rec.assembly_type is NULL)) THEN
30 --(x_rec.bill_sequence_id is NULL)) THEN
31 raise e_insert_bill;
32 END IF;
33
34 x_progress := '020';
35
36 /******* begin delete ******
37 This insert is being commented out since we will use
38 the BOM Business Object API in 11.i.2 instead
39 of the Open Interface
40
41 INSERT INTO BOM_BILL_OF_MTLS_INTERFACE(
42 transaction_type,
43 assembly_item_id,
44 organization_id,
45 alternate_bom_designator,
46 common_assembly_item_id,
47 specific_assembly_comment,
48 attribute_category,
49 attribute1,
50 attribute2,
51 attribute3,
52 attribute4,
53 attribute5,
54 attribute6,
55 attribute7,
56 attribute8,
57 attribute9,
58 attribute10,
59 attribute11,
60 attribute12,
61 attribute13,
62 attribute14,
63 attribute15,
64 assembly_type,
65 common_bill_sequence_id,
66 bill_sequence_id,
67 revision,
68 common_organization_id,
69 process_flag,
70 organization_code,
71 common_org_code,
72 item_number,
73 common_item_number
74 ) VALUES (
75 'CREATE',
76 x_rec.assembly_item_id,
77 x_rec.organization_id,
78 x_rec.alternate_bom_designator,
79 x_rec.common_assembly_item_id,
80 x_rec.specific_assembly_comment,
81 x_rec.attribute_category,
82 x_rec.attribute1,
83 x_rec.attribute2,
84 x_rec.attribute3,
85 x_rec.attribute4,
86 x_rec.attribute5,
87 x_rec.attribute6,
88 x_rec.attribute7,
89 x_rec.attribute8,
90 x_rec.attribute9,
91 x_rec.attribute10,
92 x_rec.attribute11,
93 x_rec.attribute12,
94 x_rec.attribute13,
95 x_rec.attribute14,
96 x_rec.attribute15,
97 x_rec.assembly_type,
98 x_rec.common_bill_sequence_id,
99 x_rec.bill_sequence_id,
100 x_rec.revision,
101 x_rec.common_organization_id,
102 x_process_flag,
103 x_rec.organization_code,
104 x_rec.common_org_code,
105 x_rec.item_number,
106 x_rec.common_item_number);
107 ****** end delete ******/
108
109 -- begin add for wsm
110 g_bom_header_rec.Transaction_Type := BOM_Globals.G_OPR_CREATE;
111 g_bom_header_rec.Assembly_Item_Name := x_assembly_item_name;
112 g_bom_header_rec.Organization_Code := x_organization_code;
113 g_bom_header_rec.Alternate_Bom_Code := x_rec.Alternate_Bom_Designator;
114 g_bom_header_rec.Common_Assembly_Item_Name := null;
115 g_bom_header_rec.Common_Organization_Code := null;
116 g_bom_header_rec.Assembly_Comment := x_rec.specific_Assembly_Comment;
117 g_bom_header_rec.Assembly_Type := x_rec.Assembly_Type;
118 g_bom_header_rec.Attribute_category := x_rec.Attribute_category;
119 g_bom_header_rec.Attribute1 := x_rec.Attribute1;
120 g_bom_header_rec.Attribute2 := x_rec.Attribute2;
121 g_bom_header_rec.Attribute3 := x_rec.Attribute3;
122 g_bom_header_rec.Attribute4 := x_rec.Attribute4;
123 g_bom_header_rec.Attribute5 := x_rec.Attribute5;
124 g_bom_header_rec.Attribute6 := x_rec.Attribute6;
125 g_bom_header_rec.Attribute7 := x_rec.Attribute7;
126 g_bom_header_rec.Attribute8 := x_rec.Attribute8;
127 g_bom_header_rec.Attribute9 := x_rec.Attribute9;
128 g_bom_header_rec.Attribute10 := x_rec.Attribute10;
129 g_bom_header_rec.Attribute11 := x_rec.Attribute11;
130 g_bom_header_rec.Attribute12 := x_rec.Attribute12;
131 g_bom_header_rec.Attribute13 := x_rec.Attribute13;
132 g_bom_header_rec.Attribute14 := x_rec.Attribute14;
133 g_bom_header_rec.Attribute15 := x_rec.Attribute15;
134 -- end add for wsm
135
136 x_error_code := 0;
137
138 EXCEPTION
139 WHEN e_insert_bill THEN
140 x_error_code := 1;
141 -- x_error_msg := 'Insufficient arguments to WSMPPCPD.insert_bill';
142 fnd_message.set_name('WSM', 'WSM_INSUFFICIENT_ARGUMENTS');
143 fnd_message.set_token('OBJECT_NAME', 'WSMPPCPD.insert_bill');
144 x_error_msg := fnd_message.get;
145
146 WHEN OTHERS THEN
147 x_error_code := sqlcode;
148 x_error_msg := 'WSMPPCPD.insert_bill(' || x_progress || ')' || ' - ' || substr(sqlerrm, 1, 200);
149 END insert_bill;
150
151
152 /* ===========================================================================
153
154 PROCEDURE NAME: insert_component
155
156 =========================================================================== */
157
158 PROCEDURE insert_component (x_rec IN OUT NOCOPY bom_inventory_comps_interface%ROWTYPE,
159 x_component_name IN VARCHAR2 DEFAULT NULL,
160 x_organization_code IN VARCHAR2 DEFAULT NULL,
161 x_assembly_item_name IN VARCHAR2 DEFAULT NULL,
162 x_supply_locator IN VARCHAR2 DEFAULT NULL,
163 x_error_code IN OUT NOCOPY NUMBER,
164 x_error_msg IN OUT NOCOPY VARCHAR2) IS
165
166 x_progress VARCHAR2(3) := NULL;
167 e_insert_component EXCEPTION;
168 x_process_flag NUMBER := 1;
169 l_basis_type number; --LBM enh
170
171 BEGIN
172
173 x_progress := '010';
174
175 /* Verify that the required arguments are being passed in. */
176 IF ((x_component_name is NULL) OR
177 --(x_rec.component_sequence_id is NULL) OR
178 (x_organization_code is NULL) OR
179 (x_assembly_item_name is NULL) OR
180 (x_rec.operation_seq_num is NULL) OR
181 (x_rec.effectivity_date is NULL)) THEN
182 raise e_insert_component;
183 END IF;
184
185 x_progress := '020';
186 if x_rec.basis_type = 2 then --LBM enh
187 l_basis_type := 2;
188 else
189 l_basis_type := null;
190 end if; --LBM enh
191
192 /* This insert will be commented since we do not want to
193 insert into the interface table anymore. We will use
194 the bom bo api with 11.i.2
195
196 INSERT INTO BOM_INVENTORY_COMPS_INTERFACE(
197 transaction_type,
198 operation_seq_num,
199 component_item_id,
200 item_num,
201 component_quantity,
202 component_yield_factor,
203 component_remarks,
204 effectivity_date,
205 disable_date,
206 attribute_category,
207 attribute1,
208 attribute2,
209 attribute3,
210 attribute4,
211 attribute5,
212 attribute6,
213 attribute7,
214 attribute8,
215 attribute9,
216 attribute10,
217 attribute11,
218 attribute12,
219 attribute13,
220 attribute14,
221 attribute15,
222 planning_factor,
223 quantity_related,
224 so_basis,
225 optional,
226 mutually_exclusive_options,
227 include_in_cost_rollup,
228 check_atp,
229 required_to_ship,
230 required_for_revenue,
231 include_on_ship_docs,
232 low_quantity,
233 high_quantity,
234 component_sequence_id,
235 bill_sequence_id,
236 wip_supply_type,
237 supply_subinventory,
238 supply_locator_id,
239 operation_lead_time_percent,
240 assembly_item_id,
241 alternate_bom_designator,
242 organization_id,
243 organization_code,
244 component_item_number,
245 assembly_item_number,
246 location_name,
247 reference_designator,
248 substitute_comp_id,
249 substitute_comp_number,
250 process_flag
251 ) VALUES (
252 'CREATE',
253 x_rec.operation_seq_num,
254 x_rec.component_item_id,
255 x_rec.item_num,
256 x_rec.component_quantity,
257 x_rec.component_yield_factor,
258 x_rec.component_remarks,
259 x_rec.effectivity_date,
260 x_rec.disable_date,
261 x_rec.attribute_category,
262 x_rec.attribute1,
263 x_rec.attribute2,
264 x_rec.attribute3,
265 x_rec.attribute4,
266 x_rec.attribute5,
267 x_rec.attribute6,
268 x_rec.attribute7,
269 x_rec.attribute8,
270 x_rec.attribute9,
271 x_rec.attribute10,
272 x_rec.attribute11,
273 x_rec.attribute12,
274 x_rec.attribute13,
275 x_rec.attribute14,
276 x_rec.attribute15,
277 x_rec.planning_factor,
278 x_rec.quantity_related,
279 x_rec.so_basis,
280 x_rec.optional,
281 x_rec.mutually_exclusive_options,
282 x_rec.include_in_cost_rollup,
283 x_rec.check_atp,
284 x_rec.required_to_ship,
285 x_rec.required_for_revenue,
286 x_rec.include_on_ship_docs,
287 x_rec.low_quantity,
288 x_rec.high_quantity,
289 x_rec.component_sequence_id,
290 x_rec.bill_sequence_id,
291 x_rec.wip_supply_type,
292 x_rec.supply_subinventory,
293 x_rec.supply_locator_id,
294 x_rec.operation_lead_time_percent,
295 x_rec.assembly_item_id,
296 x_rec.alternate_bom_designator,
297 x_rec.organization_id,
298 x_rec.organization_code,
299 x_rec.component_item_number,
300 x_rec.assembly_item_number,
301 x_rec.location_name,
302 x_rec.reference_designator,
303 x_rec.substitute_comp_id,
304 x_rec.substitute_comp_number,
305 x_process_flag);
306 */
307 g_component_tbl(1).Transaction_Type := BOM_Globals.G_OPR_CREATE;
308 --start defaulting
309 g_component_tbl(1).item_sequence_number := NULL;
310 g_component_tbl(1).Quantity_Related := 2;
311 g_component_tbl(1).Check_Atp := NULL;
312 g_component_tbl(1).To_End_Item_Unit_Number := NULL;
313 g_component_tbl(1).So_Basis := 2;
314 g_component_tbl(1).Optional := 2;
315 g_component_tbl(1).Mutually_Exclusive := 2;
316 g_component_tbl(1).Shipping_Allowed := 2;
317 g_component_tbl(1).Required_To_Ship := 2;
318 g_component_tbl(1).Required_For_Revenue := 2;
319 g_component_tbl(1).Include_On_Ship_Docs := 2;
320 g_component_tbl(1).Minimum_Allowed_Quantity := NULL;
321 g_component_tbl(1).Maximum_Allowed_Quantity := NULL;
322 --end defaulting
323 g_component_tbl(1).Organization_Code := x_organization_code;
324 g_component_tbl(1).Assembly_Item_Name := x_assembly_item_name;
325 g_component_tbl(1).Start_Effective_Date := x_rec.effectivity_date;
326 g_component_tbl(1).Disable_Date := x_rec.Disable_Date;
327 g_component_tbl(1).Operation_Sequence_Number := x_rec.operation_seq_num;
328 g_component_tbl(1).Component_Item_Name := x_component_name;
329 g_component_tbl(1).Alternate_BOM_Code := x_rec.alternate_bom_designator;
330 g_component_tbl(1).Quantity_Per_Assembly := x_rec.component_quantity;
331 g_component_tbl(1).Planning_Percent := x_rec.planning_factor;
332 g_component_tbl(1).Projected_Yield := x_rec.component_yield_factor;
333 g_component_tbl(1).Include_In_Cost_Rollup := x_rec.include_in_cost_rollup;
334 g_component_tbl(1).Wip_Supply_Type := x_rec.wip_supply_type;
335 g_component_tbl(1).Supply_Subinventory := x_rec.Supply_Subinventory;
336 g_component_tbl(1).Location_Name := x_supply_locator;
337 g_component_tbl(1).Comments := x_rec.component_remarks;
338 g_component_tbl(1).Attribute_category := x_rec.Attribute_category;
339 g_component_tbl(1).Attribute1 := x_rec.Attribute1;
340 g_component_tbl(1).Attribute2 := x_rec.Attribute2;
341 g_component_tbl(1).Attribute3 := x_rec.Attribute3;
342 g_component_tbl(1).Attribute4 := x_rec.Attribute4;
343 g_component_tbl(1).Attribute5 := x_rec.Attribute5;
344 g_component_tbl(1).Attribute6 := x_rec.Attribute6;
345 g_component_tbl(1).Attribute7 := x_rec.Attribute7;
346 g_component_tbl(1).Attribute8 := x_rec.Attribute8;
347 g_component_tbl(1).Attribute9 := x_rec.Attribute9;
348 g_component_tbl(1).Attribute10 := x_rec.Attribute10;
349 g_component_tbl(1).Attribute11 := x_rec.Attribute11;
350 g_component_tbl(1).Attribute12 := x_rec.Attribute12;
351 g_component_tbl(1).Attribute13 := x_rec.Attribute13;
352 g_component_tbl(1).Attribute14 := x_rec.Attribute14;
353 g_component_tbl(1).Attribute15 := x_rec.Attribute15;
354 g_component_tbl(1).basis_type := l_basis_type; --LBM enh
355
356 x_error_code := 0;
357
358 EXCEPTION
359 WHEN e_insert_component THEN
360 x_error_code := 1;
361 --x_error_msg := 'Insufficient arguments to WSMPPCPD.insert_component';
362 fnd_message.set_name('WSM', 'WSM_INSUFFICIENT_ARGUMENTS');
363 fnd_message.set_token('OBJECT_NAME', 'WSMPPCPD.insert_component');
364 x_error_msg := fnd_message.get;
365
366 WHEN OTHERS THEN
367 x_error_code := sqlcode;
368 x_error_msg := 'WSMPPCPD.insert_component(' || x_progress || ')' || ' - ' || substr(sqlerrm, 1, 200);
369 END insert_component;
370
371
372 /* ===========================================================================
373
374 PROCEDURE NAME: insert_substitute_component
375
376 =========================================================================== */
377
378 PROCEDURE insert_substitute_component (
379 x_rec IN OUT NOCOPY bom_sub_comps_interface%ROWTYPE,
380 x_co_product_name IN VARCHAR2,
381 x_alternate_designator IN VARCHAR2,
382 x_component_name IN VARCHAR2,
383 x_comp_start_eff_date IN DATE,
384 x_org_code IN VARCHAR2,
385 x_error_code IN OUT NOCOPY NUMBER,
386 x_error_msg IN OUT NOCOPY VARCHAR2) IS
387
388 x_progress VARCHAR2(3) := NULL;
389 e_insert_substitute EXCEPTION;
390 e_proc_exception EXCEPTION;
391 x_process_flag NUMBER := 1;
392
393 BEGIN
394
395 x_progress := '010';
396
397 /* Verify that the required arguments are being passed in. */
398 IF ((x_rec.substitute_component_id is NULL) OR
399 (x_rec.substitute_item_quantity is NULL) OR
400 --(x_rec.component_sequence_id is NULL)) THEN -- not required now
401 (x_co_product_name is NULL) OR
402 (x_component_name is NULL) OR
403 (x_comp_start_eff_date is NULL) OR
404 (x_org_code is NULL)) THEN
405
406 raise e_insert_substitute;
407
408 END IF;
409
410 x_progress := '020';
411
412 /* Comment this out since we are using the BOM BO API in Release 11i.2
413 INSERT INTO BOM_SUB_COMPS_INTERFACE(
414 transaction_type,
415 substitute_component_id,
416 last_update_date,
417 last_updated_by,
418 creation_date,
419 created_by,
420 last_update_login,
421 substitute_item_quantity,
422 component_sequence_id,
423 acd_type,
424 change_notice,
425 attribute_category,
426 attribute1,
427 attribute2,
428 attribute3,
429 attribute4,
430 attribute5,
431 attribute6,
432 attribute7,
433 attribute8,
434 attribute9,
435 attribute10,
436 attribute11,
437 attribute12,
438 attribute13,
439 attribute14,
440 attribute15,
441 bill_sequence_id,
442 assembly_item_id,
443 alternate_bom_designator,
444 organization_id,
445 component_item_id,
446 operation_seq_num,
447 effectivity_date,
448 transaction_id,
449 process_flag,
450 organization_code,
451 substitute_comp_number,
452 component_item_number,
453 assembly_item_number
454 ) VALUES (
455 'CREATE',
456 x_rec.substitute_component_id,
457 sysdate,
458 fnd_global.user_id,
459 sysdate,
460 fnd_global.user_id,
461 fnd_global.login_id,
462 x_rec.substitute_item_quantity,
463 x_rec.component_sequence_id,
464 x_rec.acd_type,
465 x_rec.change_notice,
466 x_rec.attribute_category,
467 x_rec.attribute1,
468 x_rec.attribute2,
469 x_rec.attribute3,
470 x_rec.attribute4,
471 x_rec.attribute5,
472 x_rec.attribute6,
473 x_rec.attribute7,
474 x_rec.attribute8,
475 x_rec.attribute9,
476 x_rec.attribute10,
477 x_rec.attribute11,
478 x_rec.attribute12,
479 x_rec.attribute13,
480 x_rec.attribute14,
481 x_rec.attribute15,
482 x_rec.bill_sequence_id,
483 x_rec.assembly_item_id,
484 x_rec.alternate_bom_designator,
485 x_rec.organization_id,
486 x_rec.component_item_id,
487 x_rec.operation_seq_num,
488 x_rec.effectivity_date,
489 x_rec.transaction_id,
490 x_process_flag,
491 x_rec.organization_code,
492 x_rec.substitute_comp_number,
493 x_rec.component_item_number,
494 x_rec.assembly_item_number);
495 */
496
497 -- populate the bom bo api pl/sql table with substitute component data
498
499 g_subs_component_count := g_subs_component_count + 1;
500
501 g_subs_comp_tbl(g_subs_component_count).Transaction_Type
502 := BOM_Globals.G_OPR_CREATE;
503 g_subs_comp_tbl(g_subs_component_count).Organization_Code := x_org_code;
504 g_subs_comp_tbl(g_subs_component_count).Assembly_Item_Name
505 := x_co_product_name;
506 g_subs_comp_tbl(g_subs_component_count).Start_Effective_Date
507 := x_comp_start_eff_date;
508 g_subs_comp_tbl(g_subs_component_count).Operation_Sequence_Number := 1;
509 g_subs_comp_tbl(g_subs_component_count).Component_Item_Name
510 := x_component_name;
511 g_subs_comp_tbl(g_subs_component_count).Alternate_BOM_Code
512 := x_alternate_designator;
513 g_subs_comp_tbl(g_subs_component_count).Substitute_Component_Name
514 := WSMPCOGI.Get_Item_Name (
515 x_rec.substitute_component_id,
516 x_rec.organization_id,
517 x_error_code,
518 x_error_msg);
519 IF x_error_code <> 0 THEN
520 raise e_proc_exception;
521 END IF;
522
523 g_subs_comp_tbl(g_subs_component_count).Substitute_Item_Quantity
524 := x_rec.substitute_item_quantity;
525 g_subs_comp_tbl(g_subs_component_count).Attribute_category
526 := x_rec.attribute_category;
527 g_subs_comp_tbl(g_subs_component_count).Attribute1 := x_rec.attribute1;
528 g_subs_comp_tbl(g_subs_component_count).Attribute2 := x_rec.attribute2;
529 g_subs_comp_tbl(g_subs_component_count).Attribute3 := x_rec.attribute3;
530 g_subs_comp_tbl(g_subs_component_count).Attribute4 := x_rec.attribute4;
531 g_subs_comp_tbl(g_subs_component_count).Attribute5 := x_rec.attribute5;
532 g_subs_comp_tbl(g_subs_component_count).Attribute6 := x_rec.attribute6;
533 g_subs_comp_tbl(g_subs_component_count).Attribute7 := x_rec.attribute7;
534 g_subs_comp_tbl(g_subs_component_count).Attribute8 := x_rec.attribute8;
535 g_subs_comp_tbl(g_subs_component_count).Attribute9 := x_rec.attribute9;
536 g_subs_comp_tbl(g_subs_component_count).Attribute10 := x_rec.attribute10;
537 g_subs_comp_tbl(g_subs_component_count).Attribute11 := x_rec.attribute11;
538 g_subs_comp_tbl(g_subs_component_count).Attribute12 := x_rec.attribute12;
539 g_subs_comp_tbl(g_subs_component_count).Attribute13 := x_rec.attribute13;
540 g_subs_comp_tbl(g_subs_component_count).Attribute14 := x_rec.attribute14;
541 g_subs_comp_tbl(g_subs_component_count).Attribute15 := x_rec.attribute15;
542
543 x_error_code := 0;
544
545 EXCEPTION
546 WHEN e_insert_substitute THEN
547 x_error_code := 1;
548 --x_error_msg := 'Insufficient arguments to WSMPPCPD.insert_substitute_component';
549 fnd_message.set_name('WSM', 'WSM_INSUFFICIENT_ARGUMENTS');
550 fnd_message.set_token('OBJECT_NAME', 'WSMPPCPD.insert_substitute_component');
551 x_error_msg := fnd_message.get;
552
553 WHEN e_proc_exception THEN
554 x_error_msg := x_error_msg || ' - ' || 'WSMPPCPD.insert_substitute_component('||x_progress||')';
555
556 WHEN OTHERS THEN
557 x_error_code := sqlcode;
558 x_error_msg := 'WSMPPCPD.insert_substitute_component(' || x_progress || ')' || ' - ' || substr(sqlerrm, 1, 200);
559
560 END insert_substitute_component;
561
562
563 /* ===========================================================================
564 PROCEDURE NAME: insert_sub_comps
565 =========================================================================== */
566
567 PROCEDURE insert_sub_comps (x_co_product_group_id IN NUMBER,
568 x_co_product_name IN VARCHAR2,
569 x_alternate_designator IN VARCHAR2,
570 x_component_name IN VARCHAR2,
571 x_comp_start_eff_date IN DATE,
572 x_org_code IN VARCHAR2,
573 x_component_sequence_id IN NUMBER,
574 x_qty_multiplier IN NUMBER,
575 x_error_code IN OUT NOCOPY NUMBER,
576 x_error_msg IN OUT NOCOPY VARCHAR2) IS
577
578 x_progress VARCHAR2(3) := NULL;
579 i NUMBER;
580 e_proc_exception EXCEPTION;
581 x_rec bom_sub_comps_interface%ROWTYPE;
582
583 CURSOR S IS SELECT *
584 FROM wsm_co_prod_comp_substitutes
585 WHERE co_product_group_id = x_co_product_group_id;
586
587 BEGIN
588
589 x_progress := '010';
590 /*coprod enh p2*/
591 -- if ((g_subs_rec_set IS NULL) OR (g_subs_rec_set <> 'Y')) then
592 /*end coprod enh p2*/
593 -- Clean out the substitute component pl/sql table for the bom api
594 g_subs_comp_tbl.delete;
595 /*coprod enh p2*/
596 g_subs_component_count := 0;
597 /*end coprod enh p2*/
598
599 FOR S_rec IN S LOOP
600
601 x_rec.substitute_component_id := S_rec.substitute_component_id;
602 x_rec.substitute_item_quantity := S_rec.substitute_item_quantity;
603 x_rec.component_sequence_id := x_component_sequence_id;
604 x_rec.organization_id := g_org_id;
605 x_rec.attribute_category := S_rec.attribute_category;
606 x_rec.attribute1 := S_rec.attribute1;
607 x_rec.attribute2 := S_rec.attribute2;
608 x_rec.attribute3 := S_rec.attribute3;
609 x_rec.attribute4 := S_rec.attribute4;
610 x_rec.attribute5 := S_rec.attribute5;
611 x_rec.attribute6 := S_rec.attribute6;
612 x_rec.attribute7 := S_rec.attribute7;
613 x_rec.attribute8 := S_rec.attribute8;
614 x_rec.attribute9 := S_rec.attribute9;
615 x_rec.attribute10 := S_rec.attribute10;
616 x_rec.attribute11 := S_rec.attribute11;
617 x_rec.attribute12 := S_rec.attribute12;
618 x_rec.attribute13 := S_rec.attribute13;
619 x_rec.attribute14 := S_rec.attribute14;
620 x_rec.attribute15 := S_rec.attribute15;
621
622 WSMPPCPD.insert_substitute_component (x_rec,
623 x_co_product_name,
624 x_alternate_designator,
625 x_component_name,
626 x_comp_start_eff_date,
627 x_org_code,
628 x_error_code,
629 x_error_msg);
630 -- g_subs_rec_set := 'Y';
631
632 IF (x_error_code < 0) THEN
633 raise e_proc_exception;
634 ELSIF (x_error_code > 0) THEN
635 return;
636 END IF;
637
638 END LOOP;
639 -- elsif (g_subs_rec_set = 'Y') then
640 -- FOR i in 1..g_subs_component_count LOOP
641 -- g_subs_comp_tbl(g_subs_component_count).Assembly_Item_Name := x_co_product_name;
642 -- g_subs_comp_tbl(g_subs_component_count).Alternate_BOM_Code := x_alternate_designator;
643 -- END LOOP;
644 -- end if;
645 x_error_code := 0;
646
647 EXCEPTION
648 WHEN e_proc_exception THEN
649 x_error_msg := x_error_msg || ' - ' || 'WSMPPCPD.insert_sub_comps('||x_progress||')';
650
651 WHEN OTHERS THEN
652 x_error_code := sqlcode;
653 x_error_msg := 'WSMPPCPD.insert_sub_comps(' || x_progress || ')' || ' - ' || substr(sqlerrm, 1, 200);
654
655 END insert_sub_comps;
656
657 /* ===========================================================================
658
659 PROCEDURE NAME: process_bom_sub_comp
660
661 =========================================================================== */
662
663 /*Coprod enh p2 rewrote process_bom_sub_comp****************************************
664 pROCEDURE process_bom_sub_comp (x_co_product_group_id IN NUMBER,
665 X_substitute_component_id IN NUMBER,
666 X_substitute_comp_id_old IN NUMBER,
667 X_process_code IN NUMBER,
668 X_org_id IN NUMBER,
669 X_rowid IN OUT NOCOPY VARCHAR2,
670 x_last_update_login NUMBER,
671 x_last_updated_by NUMBER,
672 x_last_update_date DATE,
673 x_creation_date DATE,
674 x_created_by NUMBER,
675 x_substitute_item_quantity NUMBER,
676 x_attribute_category VARCHAR2,
677 x_attribute1 VARCHAR2,
678 x_attribute2 VARCHAR2,
679 x_attribute3 VARCHAR2,
680 x_attribute4 VARCHAR2,
681 x_attribute5 VARCHAR2,
682 x_attribute6 VARCHAR2,
683 x_attribute7 VARCHAR2,
684 x_attribute8 VARCHAR2,
685 x_attribute9 VARCHAR2,
686 x_attribute10 VARCHAR2,
687 x_attribute11 VARCHAR2,
688 x_attribute12 VARCHAR2,
689 x_attribute13 VARCHAR2,
690 x_attribute14 VARCHAR2,
691 x_attribute15 VARCHAR2,
692 x_error_code IN OUT NOCOPY NUMBER,
693 x_error_msg IN OUT NOCOPY VARCHAR2) IS
694
695 x_progress VARCHAR2(3) := NULL;
696 e_proc_exception EXCEPTION;
697 x_rec bom_sub_comps_interface%ROWTYPE;
698
699 e_comp_exception EXCEPTION;
700 e_check_unique_exception EXCEPTION;
701 e_check_common EXCEPTION;
702 e_sub_comp_not_exists EXCEPTION;
703 bom_dupl_comp_err EXCEPTION; -- abedajna
704
705 x_dummy NUMBER := NULL;
706 x_co_prod_exists NUMBER := NULL;
707 x_comp_exists NUMBER := NULL;
708 x_component_sequence_id NUMBER := NULL;
709 x_bill_sequence_id NUMBER := NULL;
710 x_sub_rowid VARCHAR2(30):= NULL;
711 x_sub_comp_record bom_substitute_components%ROWTYPE;
712 x_skip_sub_delete NUMBER := 0;
713
714 CURSOR S IS SELECT *
715 FROM wsm_co_prod_comp_substitutes
716 WHERE co_product_group_id = x_co_product_group_id;
717
718 CURSOR C (x_comp_seq_id NUMBER) IS SELECT rowid
719 FROM bom_substitute_components
720 WHERE component_sequence_id = x_comp_seq_id
721 AND substitute_component_id = x_substitute_comp_id_old
722 FOR UPDATE OF substitute_component_id NOWAIT;
723
724 CURSOR C_COPROD IS SELECT component_sequence_id,
725 bill_sequence_id
726 FROM wsm_co_products
727 WHERE co_product_group_id = x_co_product_group_id
728 And co_product_id is NOT NULL;
729
730 BEGIN
731
732 x_progress := '010';
733
734
735
736 IF (x_process_code = 1) THEN
737
738 BEGIN
739
740
741 -- modification begin for perf. tuning.. abedajna 10/12/00
742 SELECT 1
743 INTO x_co_prod_exists
744 FROM wsm_co_products
745 WHERE co_product_group_id = x_co_product_group_id
746 AND co_product_id IS NOT NULL;
747
748 EXCEPTION
749 WHEN NO_DATA_FOUND THEN
750 x_co_prod_exists := 0;
751
752 WHEN TOO_MANY_ROWS THEN
753 x_co_prod_exists := 1;
754 -- modification end for perf. tuning.. abedajna 10/12/00
755 END;
756
757 x_progress := '020';
758
759 IF (x_co_prod_exists = 0) THEN
760
761 WSMPCPCS.insert_row (
762 x_rowid,
763 x_co_product_group_id,
764 x_substitute_component_id,
765 x_last_update_login,
766 x_last_updated_by,
767 x_last_update_date,
768 x_creation_date,
769 x_created_by,
770 x_substitute_item_quantity,
771 x_attribute_category,
772 x_attribute1,
773 x_attribute2,
774 x_attribute3,
775 x_attribute4,
776 x_attribute5,
777 x_attribute6,
778 x_attribute7,
779 x_attribute8,
780 x_attribute9,
781 x_attribute10,
782 x_attribute11,
783 x_attribute12,
784 x_attribute13,
785 x_attribute14,
786 x_attribute15,
787 null,
788 null,
789 null,
790 null);
791
792 x_error_code := 0;
793 return;
794
795 END IF;
796 END IF;
797
798
799 x_progress := '030';
800
801 SELECT component_sequence_id,
802 bill_sequence_id
803 INTO x_component_sequence_id,
804 x_bill_sequence_id
805 FROM wsm_co_products
806 WHERE co_product_group_id = x_co_product_group_id
807 And co_product_id is NOT NULL
808 AND NVL(primary_flag, 'N') = 'Y';
809
810 x_progress := '040';
811
812 BEGIN
813
814 SELECT 1
815 INTO x_comp_exists
816 FROM bom_inventory_components
817 WHERE component_sequence_id = x_component_sequence_id;
818
819 EXCEPTION
820 WHEN NO_DATA_FOUND THEN
821 raise e_comp_exception;
822 END;
823
824
825 IF (x_process_code = 1) THEN
826
827 x_progress := '050';
828
829 BEGIN
830
831
832
833 -- modification begin for perf. tuning.. abedajna 10/12/00
834
835 x_dummy := 0;
836
837 SELECT 1
838 INTO x_dummy
839 FROM bom_substitute_components
840 WHERE nvl(acd_type, 1) = 1
841 AND substitute_component_id = x_substitute_component_id
842 AND component_sequence_id = x_component_sequence_id;
843
844 IF x_dummy <> 0 THEN
845 RAISE bom_dupl_comp_err;
846 END IF;
847
848 EXCEPTION
849
850 WHEN bom_dupl_comp_err THEN
851 fnd_message.set_name('BOM','BOM_DUPLICATE_SUB_COMP');
852 raise e_check_unique_exception;
853
854 WHEN NO_DATA_FOUND THEN
855 NULL;
856
857 WHEN TOO_MANY_ROWS THEN
858 fnd_message.set_name('BOM','BOM_DUPLICATE_SUB_COMP');
859 raise e_check_unique_exception;
860 -- modification end for perf. tuning.. abedajna 10/12/00
861
862 END;
863
864 x_progress := '060';
865
866 BEGIN
867
868 SELECT 1
869 INTO x_dummy
870 FROM bom_bill_of_materials bbom
871 WHERE bbom.common_bill_sequence_id = x_bill_sequence_id
872 AND bbom.organization_id <> x_org_id
873 AND NOT EXISTS (
874 SELECT null
875 FROM mtl_system_items msi
876 WHERE msi.organization_id = bbom.organization_id
877 AND msi.inventory_item_id = x_substitute_component_id
878 AND msi.bom_enabled_flag = 'Y'
879 AND ((bbom.assembly_type = 1
880 AND msi.eng_item_flag = 'N')
881 OR (bbom.assembly_type = 2)));
882
883 fnd_message.set_name('INV','INV_NOT_VALID');
884 fnd_message.set_token('ENTITY','Substitute item', TRUE);
885 raise e_check_common;
886
887 EXCEPTION
888 WHEN NO_DATA_FOUND THEN
889 null;
890 END;
891
892 x_progress := '070';
893
894 bom_sub_comps_pkg.insert_row(x_sub_rowid,
895 x_substitute_component_id,
896 x_last_update_date,
897 x_last_updated_by,
898 x_creation_date,
899 x_created_by,
900 x_last_update_login,
901 x_substitute_item_quantity,
902 x_component_sequence_id,
903 null,
904 null,
905 x_attribute_category,
906 x_attribute1,
907 x_attribute2,
908 x_attribute3,
909 x_attribute4,
910 x_attribute5,
911 x_attribute6,
912 x_attribute7,
913 x_attribute8,
914 x_attribute9,
915 x_attribute10,
916 x_attribute11,
917 x_attribute12,
918 x_attribute13,
919 x_attribute14,
920 x_attribute15);
921
922
923 WSMPCPCS.insert_row (x_rowid,
924 x_co_product_group_id,
925 x_substitute_component_id,
926 x_last_update_login,
927 x_last_updated_by,
928 x_last_update_date,
929 x_creation_date,
930 x_created_by,
931 x_substitute_item_quantity,
932 x_attribute_category,
933 x_attribute1,
934 x_attribute2,
935 x_attribute3,
936 x_attribute4,
937 x_attribute5,
938 x_attribute6,
939 x_attribute7,
940 x_attribute8,
941 x_attribute9,
942 x_attribute10,
943 x_attribute11,
944 x_attribute12,
945 x_attribute13,
946 x_attribute14,
947 x_attribute15,
948 null,
949 null,
950 null,
951 null);
952
953 x_error_code := 0;
954 return;
955
956 ELSIF (x_process_code = 2) THEN
957
958 IF (x_substitute_component_id <> x_substitute_comp_id_old) THEN
959
960
961 x_progress := '080';
962
963 BEGIN
964
965
966 x_dummy := 0;
967
968 SELECT 1
969 INTO x_dummy
970 FROM bom_substitute_components
971 WHERE nvl(acd_type, 1) = 1
972 AND substitute_component_id = x_substitute_component_id
973 AND component_sequence_id = x_component_sequence_id;
974
975
976 IF x_dummy <> 0 THEN
977 RAISE bom_dupl_comp_err;
978 END IF;
979
980 EXCEPTION
981
982 WHEN bom_dupl_comp_err THEN
983 fnd_message.set_name('BOM','BOM_DUPLICATE_SUB_COMP');
984 raise e_check_unique_exception;
985
986 WHEN NO_DATA_FOUND THEN
987 NULL;
988
989 WHEN TOO_MANY_ROWS THEN
990 fnd_message.set_name('BOM','BOM_DUPLICATE_SUB_COMP');
991 raise e_check_unique_exception;
992
993 -- modification end for perf. tuning.. abedajna 10/12/00
994 END;
995
996 x_progress := '090';
997
998 BEGIN
999
1000 SELECT 1
1001 INTO x_dummy
1002 FROM bom_bill_of_materials bbom
1003 WHERE bbom.common_bill_sequence_id = x_bill_sequence_id
1004 AND bbom.organization_id <> x_org_id
1005 AND NOT EXISTS
1006 (SELECT null
1007 FROM mtl_system_items msi
1008 WHERE msi.organization_id = bbom.organization_id
1009 AND msi.inventory_item_id = x_substitute_component_id
1010 AND msi.bom_enabled_flag = 'Y'
1011 AND ((bbom.assembly_type = 1
1012 AND msi.eng_item_flag = 'N')
1013 OR (bbom.assembly_type = 2)));
1014
1015 fnd_message.set_name('INV','INV_NOT_VALID');
1016 fnd_message.set_token('ENTITY','Substitute item', TRUE);
1017 raise e_check_common;
1018
1019 EXCEPTION
1020 WHEN NO_DATA_FOUND THEN
1021 null;
1022 END;
1023 END IF;
1024
1025
1026 OPEN C (x_component_sequence_id);
1027 FETCH C INTO x_sub_rowid;
1028 IF (C%NOTFOUND) THEN
1029 raise e_sub_comp_not_exists;
1030 END IF;
1031 CLOSE C;
1032
1033 x_progress := '100';
1034
1035 bom_sub_comps_pkg.update_row(x_sub_rowid,
1036 x_substitute_component_id,
1037 x_last_update_date,
1038 x_last_updated_by,
1039 x_last_update_login,
1040 x_substitute_item_quantity,
1041 x_component_sequence_id,
1042 null,
1043 null,
1044 x_attribute_category,
1045 x_attribute1,
1046 x_attribute2,
1047 x_attribute3,
1048 x_attribute4,
1049 x_attribute5,
1050 x_attribute6,
1051 x_attribute7,
1052 x_attribute8,
1053 x_attribute9,
1054 x_attribute10,
1055 x_attribute11,
1056 x_attribute12,
1057 x_attribute13,
1058 x_attribute14,
1059 x_attribute15);
1060
1061 x_progress := '110';
1062
1063 WSMPCPCS.update_row(X_rowid,
1064 x_co_product_group_id,
1065 x_substitute_component_id,
1066 x_last_update_login,
1067 x_last_updated_by,
1068 x_last_update_date,
1069 x_substitute_item_quantity,
1070 x_attribute_category,
1071 x_attribute1,
1072 x_attribute2,
1073 x_attribute3,
1074 x_attribute4,
1075 x_attribute5,
1076 x_attribute6,
1077 x_attribute7,
1078 x_attribute8,
1079 x_attribute9,
1080 x_attribute10,
1081 x_attribute11,
1082 x_attribute12,
1083 x_attribute13,
1084 x_attribute14,
1085 x_attribute15,
1086 null,
1087 null,
1088 null,
1089 null);
1090
1091 ELSIF (x_process_code = 3) THEN
1092
1093 x_progress := '120';
1094
1095 OPEN C (x_component_sequence_id);
1096 FETCH C INTO x_sub_rowid;
1097 IF (C%NOTFOUND) THEN
1098 x_skip_sub_delete := 1;
1099 END IF;
1100 CLOSE C;
1101
1102 IF (x_skip_sub_delete = 0) THEN
1103 bom_sub_comps_pkg.Delete_Row (x_sub_rowid);
1104 END IF;
1105
1106 WSMPCPCS.Delete_Row (x_rowid);
1107
1108 END IF;
1109
1110 x_error_code := 0;
1111
1112 EXCEPTION
1113 WHEN e_proc_exception THEN
1114 x_error_msg := x_error_msg || ' - ' || 'WSMPPCPD.insert_sub_comps('||x_progress||')';
1115
1116 WHEN e_comp_exception THEN
1117 x_error_code := 1;
1118 fnd_message.set_name('WSM','WSM_MISSING_BOM_COMP');
1119 x_error_msg := fnd_message.get;
1120
1121 WHEN e_check_unique_exception THEN
1122 x_error_code := 2;
1123 x_error_msg := fnd_message.get;
1124
1125 WHEN e_check_common THEN
1126 x_error_code := 3;
1127 x_error_msg := fnd_message.get;
1128
1129 WHEN e_sub_comp_not_exists THEN
1130 x_error_code := 4;
1131 fnd_message.set_name('WSM','WSM_MISSING_SUBS_COMP');
1132 x_error_msg := fnd_message.get;
1133
1134 WHEN app_exceptions.record_lock_exception THEN
1135 x_error_code := 5;
1136 fnd_message.set_name('WSM','WSM_SUBS_COMP_LOCK_ERR');
1137 x_error_msg := fnd_message.get;
1138
1139 WHEN OTHERS THEN
1140 x_error_code := sqlcode;
1141 x_error_msg := 'WSMPPCPD.process_bom_sub_comp(' || x_progress || ')' || ' - ' || substr(sqlerrm, 1, 200);
1142
1143 END process_bom_sub_comp;
1144 ***************************************************************************************************************************/
1145
1146
1147 PROCEDURE process_bom_sub_comp (x_co_product_group_id IN NUMBER,
1148 x_substitute_component_id IN NUMBER,
1149 x_substitute_comp_id_old IN NUMBER,
1150 x_process_code IN NUMBER,
1151 x_org_id IN NUMBER,
1152 x_rowid IN OUT NOCOPY VARCHAR2,
1153 x_last_update_login NUMBER,
1154 x_last_updated_by NUMBER,
1155 x_last_update_date DATE,
1156 x_creation_date DATE,
1157 x_created_by NUMBER,
1158 x_substitute_item_quantity NUMBER,
1159 x_attribute_category VARCHAR2,
1160 x_attribute1 VARCHAR2,
1161 x_attribute2 VARCHAR2,
1162 x_attribute3 VARCHAR2,
1163 x_attribute4 VARCHAR2,
1164 x_attribute5 VARCHAR2,
1165 x_attribute6 VARCHAR2,
1166 x_attribute7 VARCHAR2,
1167 x_attribute8 VARCHAR2,
1168 x_attribute9 VARCHAR2,
1169 x_attribute10 VARCHAR2,
1170 x_attribute11 VARCHAR2,
1171 x_attribute12 VARCHAR2,
1172 x_attribute13 VARCHAR2,
1173 x_attribute14 VARCHAR2,
1174 x_attribute15 VARCHAR2,
1175 x_basis_type NUMBER, --LBM enh
1176 x_error_code IN OUT NOCOPY NUMBER,
1177 x_error_msg IN OUT NOCOPY VARCHAR2) IS
1178
1179 x_progress VARCHAR2(3) := NULL;
1180 e_proc_exception EXCEPTION;
1181 x_rec bom_sub_comps_interface%ROWTYPE;
1182
1183 e_comp_exception EXCEPTION;
1184 e_check_unique_exception EXCEPTION;
1185 e_check_common EXCEPTION;
1186 e_sub_comp_not_exists EXCEPTION;
1187 bom_dupl_comp_err EXCEPTION; -- abedajna
1188
1189 x_dummy NUMBER := NULL;
1190 x_co_prod_exists NUMBER := NULL;
1191 x_comp_exists NUMBER := NULL;
1192 x_component_sequence_id NUMBER := NULL;
1193 x_bill_sequence_id NUMBER := NULL;
1194 x_sub_rowid VARCHAR2(30):= NULL;
1195 x_sub_comp_record bom_substitute_components%ROWTYPE;
1196 x_skip_sub_delete NUMBER := 0;
1197
1198 CURSOR S IS SELECT *
1199 FROM wsm_co_prod_comp_substitutes
1200 WHERE co_product_group_id = x_co_product_group_id;
1201
1202 CURSOR C (x_comp_seq_id NUMBER) IS SELECT rowid
1203 FROM bom_substitute_components
1204 WHERE component_sequence_id = x_comp_seq_id
1205 AND substitute_component_id = x_substitute_comp_id_old
1206 FOR UPDATE OF substitute_component_id NOWAIT;
1207
1208 CURSOR C_COPROD IS SELECT component_sequence_id,
1209 bill_sequence_id
1210 FROM wsm_co_products
1211 WHERE co_product_group_id = x_co_product_group_id
1212 And co_product_id is NOT NULL
1213 AND component_sequence_id IS NOT NULL;
1214
1215 BEGIN
1216
1217 x_progress := '010';
1218
1219 /* Verify if a co-product exists. */
1220
1221 IF (x_process_code = 1) THEN /* Insert */
1222
1223 BEGIN
1224
1225 -- modification begin for perf. tuning.. abedajna 10/12/00
1226 SELECT 1
1227 INTO x_co_prod_exists
1228 FROM wsm_co_products
1229 WHERE co_product_group_id = x_co_product_group_id
1230 AND co_product_id IS NOT NULL;
1231
1232 EXCEPTION
1233 WHEN NO_DATA_FOUND THEN
1234 x_co_prod_exists := 0;
1235
1236 WHEN TOO_MANY_ROWS THEN
1237 x_co_prod_exists := 1;
1238 -- modification end for perf. tuning.. abedajna 10/12/00
1239 END;
1240
1241 x_progress := '020';
1242
1243 IF (x_co_prod_exists = 0) THEN
1244
1245 /* Insert into wsm_co_prod_comp_substitutes. */
1246 WSMPCPCS.insert_row (
1247 x_rowid,
1248 x_co_product_group_id,
1249 x_substitute_component_id,
1250 x_last_update_login,
1251 x_last_updated_by,
1252 x_last_update_date,
1253 x_creation_date,
1254 x_created_by,
1255 x_substitute_item_quantity,
1256 x_attribute_category,
1257 x_attribute1,
1258 x_attribute2,
1259 x_attribute3,
1260 x_attribute4,
1261 x_attribute5,
1262 x_attribute6,
1263 x_attribute7,
1264 x_attribute8,
1265 x_attribute9,
1266 x_attribute10,
1267 x_attribute11,
1268 x_attribute12,
1269 x_attribute13,
1270 x_attribute14,
1271 x_attribute15,
1272 null,
1273 null,
1274 null,
1275 null,
1276 x_basis_type); --LBM enh
1277
1278 x_error_code := 0;
1279 return;
1280
1281 END IF;
1282 END IF;
1283
1284 /*
1285 * A bill most likely exists for this
1286 * co-product relationship. Changes will
1287 * have to be performed to both wsm_co_prod_comp_substitutes
1288 * as well as bom_component_substitutes.
1289 */
1290
1291 /*
1292 * Obtain the component information from the
1293 * primary co-product.
1294 */
1295 x_progress := '030';
1296
1297
1298
1299 /*coprod enh p2 introduced a loop that inserts the substitutes for all the coproducts*/
1300 FOR rec in C_COPROD LOOP
1301
1302 /* Verify that the component exists in BOM. */
1303
1304 x_progress := '040';
1305
1306
1307 BEGIN
1308
1309 SELECT 1
1310 INTO x_comp_exists
1311 FROM bom_inventory_components
1312 WHERE component_sequence_id = rec.component_sequence_id;
1313
1314 EXCEPTION
1315 WHEN NO_DATA_FOUND THEN
1316 raise e_comp_exception;
1317 END;
1318
1319 IF (x_process_code = 1) THEN /* Insert */
1320
1321 /* Check for uniqueness in bom_component_substitutes. */
1322
1323 x_progress := '050';
1324
1325 BEGIN
1326
1327 -- modification begin for perf. tuning.. abedajna 10/12/00
1328
1329 x_dummy := 0;
1330
1331 SELECT 1
1332 INTO x_dummy
1333 FROM bom_substitute_components
1334 WHERE nvl(acd_type, 1) = 1
1335 AND substitute_component_id = x_substitute_component_id
1336 AND component_sequence_id = rec.component_sequence_id;
1337
1338 IF x_dummy <> 0 THEN
1339 RAISE bom_dupl_comp_err;
1340 END IF;
1341
1342 EXCEPTION
1343
1344 WHEN bom_dupl_comp_err THEN
1345 fnd_message.set_name('BOM','BOM_DUPLICATE_SUB_COMP');
1346 raise e_check_unique_exception;
1347
1348 WHEN NO_DATA_FOUND THEN
1349 NULL;
1350
1351 WHEN TOO_MANY_ROWS THEN
1352 fnd_message.set_name('BOM','BOM_DUPLICATE_SUB_COMP');
1353 raise e_check_unique_exception;
1354 -- modification end for perf. tuning.. abedajna 10/12/00
1355
1356 END;
1357
1358 /* Perform the check commons processing. */
1359 x_progress := '060';
1360
1361 BEGIN
1362
1363 SELECT 1
1364 INTO x_dummy
1365 FROM bom_bill_of_materials bbom
1366 WHERE bbom.common_bill_sequence_id = rec.bill_sequence_id
1367 AND bbom.organization_id <> x_org_id
1368 AND NOT EXISTS (
1369 SELECT null
1370 FROM mtl_system_items msi
1371 WHERE msi.organization_id = bbom.organization_id
1372 AND msi.inventory_item_id = x_substitute_component_id
1373 AND msi.bom_enabled_flag = 'Y'
1374 AND ((bbom.assembly_type = 1
1375 AND msi.eng_item_flag = 'N')
1376 OR (bbom.assembly_type = 2)));
1377
1378 fnd_message.set_name('INV','INV_NOT_VALID');
1379 fnd_message.set_token('ENTITY','Substitute item', TRUE);
1380 raise e_check_common;
1381
1382 EXCEPTION
1383 WHEN NO_DATA_FOUND THEN
1384 null;
1385 END;
1386
1387 /*
1388 Insert into bom_substitute_components followed
1389 by an insert into wsm_co_prod_comp_substitutes.
1390 */
1391 x_progress := '070';
1392 --LBM enh : Per Vani Hymavathi of BOM dev, we do not need to pass basis_type for processing substitute components
1393 -- as Substitute components shall automaticaly inherit the basis type.
1394
1395 bom_sub_comps_pkg.insert_row(x_sub_rowid,
1396 x_substitute_component_id,
1397 x_last_update_date,
1398 x_last_updated_by,
1399 x_creation_date,
1400 x_created_by,
1401 x_last_update_login,
1402 x_substitute_item_quantity,
1403 rec.component_sequence_id,
1404 null,
1405 null,
1406 x_attribute_category,
1407 x_attribute1,
1408 x_attribute2,
1409 x_attribute3,
1410 x_attribute4,
1411 x_attribute5,
1412 x_attribute6,
1413 x_attribute7,
1414 x_attribute8,
1415 x_attribute9,
1416 x_attribute10,
1417 x_attribute11,
1418 x_attribute12,
1419 x_attribute13,
1420 x_attribute14,
1421 x_attribute15);
1422
1423 ELSIF (x_process_code = 2) THEN /* Update */
1424
1425 IF (x_substitute_component_id <> x_substitute_comp_id_old) THEN
1426
1427 /* Check that the new substitute component is unique. */
1428
1429 x_progress := '080';
1430
1431 BEGIN
1432
1433 x_dummy := 0;
1434
1435 SELECT 1
1436 INTO x_dummy
1437 FROM bom_substitute_components
1438 WHERE nvl(acd_type, 1) = 1
1439 AND substitute_component_id = x_substitute_component_id
1440 AND component_sequence_id =rec.component_sequence_id;
1441
1442
1443 IF x_dummy <> 0 THEN
1444 RAISE bom_dupl_comp_err;
1445 END IF;
1446
1447 EXCEPTION
1448
1449 WHEN bom_dupl_comp_err THEN
1450 fnd_message.set_name('BOM','BOM_DUPLICATE_SUB_COMP');
1451 raise e_check_unique_exception;
1452
1453 WHEN NO_DATA_FOUND THEN
1454 NULL;
1455
1456 WHEN TOO_MANY_ROWS THEN
1457 fnd_message.set_name('BOM','BOM_DUPLICATE_SUB_COMP');
1458 raise e_check_unique_exception;
1459
1460 -- modification end for perf. tuning.. abedajna 10/12/00
1461 END;
1462
1463 /* Perform the check commons processing.*/
1464 x_progress := '090';
1465
1466 BEGIN
1467
1468 SELECT 1
1469 INTO x_dummy
1470 FROM bom_bill_of_materials bbom
1471 WHERE bbom.common_bill_sequence_id = rec.bill_sequence_id
1472 AND bbom.organization_id <> x_org_id
1473 AND NOT EXISTS
1474 (SELECT null
1475 FROM mtl_system_items msi
1476 WHERE msi.organization_id = bbom.organization_id
1477 AND msi.inventory_item_id = x_substitute_component_id
1478 AND msi.bom_enabled_flag = 'Y'
1479 AND ((bbom.assembly_type = 1
1480 AND msi.eng_item_flag = 'N')
1481 OR (bbom.assembly_type = 2)));
1482
1483 fnd_message.set_name('INV','INV_NOT_VALID');
1484 fnd_message.set_token('ENTITY','Substitute item', TRUE);
1485 raise e_check_common;
1486
1487 EXCEPTION
1488 WHEN NO_DATA_FOUND THEN
1489 null;
1490 END;
1491 END IF;
1492
1493 /*
1494 * Lock record in bom_substitute_components
1495 * and perform the update.
1496 */
1497
1498 OPEN C (rec.component_sequence_id);
1499 FETCH C INTO x_sub_rowid;
1500 IF (C%NOTFOUND) THEN
1501 raise e_sub_comp_not_exists;
1502 END IF;
1503 CLOSE C;
1504
1505 x_progress := '100';
1506
1507 bom_sub_comps_pkg.update_row(x_sub_rowid,
1508 x_substitute_component_id,
1509 x_last_update_date,
1510 x_last_updated_by,
1511 x_last_update_login,
1512 x_substitute_item_quantity,
1513 rec.component_sequence_id,
1514 null,
1515 null,
1516 x_attribute_category,
1517 x_attribute1,
1518 x_attribute2,
1519 x_attribute3,
1520 x_attribute4,
1521 x_attribute5,
1522 x_attribute6,
1523 x_attribute7,
1524 x_attribute8,
1525 x_attribute9,
1526 x_attribute10,
1527 x_attribute11,
1528 x_attribute12,
1529 x_attribute13,
1530 x_attribute14,
1531 x_attribute15);
1532
1533
1534
1535 ELSIF (x_process_code = 3) THEN /* Delete */
1536
1537 /* Lock record in bom_substitute_components. */
1538 x_progress := '120';
1539
1540 OPEN C (rec.component_sequence_id);
1541 FETCH C INTO x_sub_rowid;
1542 IF (C%NOTFOUND) THEN
1543 x_skip_sub_delete := 1;
1544 END IF;
1545 CLOSE C;
1546
1547 IF (x_skip_sub_delete = 0) THEN
1548 bom_sub_comps_pkg.Delete_Row (x_sub_rowid);
1549 END IF;
1550
1551 -- WSMPCPCS.Delete_Row (x_rowid);
1552
1553 END IF;
1554 END LOOP;
1555
1556 IF (x_process_code=1) then
1557 WSMPCPCS.insert_row (x_rowid,
1558 x_co_product_group_id,
1559 x_substitute_component_id,
1560 x_last_update_login,
1561 x_last_updated_by,
1562 x_last_update_date,
1563 x_creation_date,
1564 x_created_by,
1565 x_substitute_item_quantity,
1566 x_attribute_category,
1567 x_attribute1,
1568 x_attribute2,
1569 x_attribute3,
1570 x_attribute4,
1571 x_attribute5,
1572 x_attribute6,
1573 x_attribute7,
1574 x_attribute8,
1575 x_attribute9,
1576 x_attribute10,
1577 x_attribute11,
1578 x_attribute12,
1579 x_attribute13,
1580 x_attribute14,
1581 x_attribute15,
1582 null,
1583 null,
1584 null,
1585 null,
1586 x_basis_type); --LBM enh
1587
1588 x_error_code := 0;
1589 return;
1590 ELSIF (x_process_code=2) then
1591 x_progress := '110';
1592
1593 WSMPCPCS.update_row(X_rowid,
1594 x_co_product_group_id,
1595 x_substitute_component_id,
1596 x_last_update_login,
1597 x_last_updated_by,
1598 x_last_update_date,
1599 x_substitute_item_quantity,
1600 x_attribute_category,
1601 x_attribute1,
1602 x_attribute2,
1603 x_attribute3,
1604 x_attribute4,
1605 x_attribute5,
1606 x_attribute6,
1607 x_attribute7,
1608 x_attribute8,
1609 x_attribute9,
1610 x_attribute10,
1611 x_attribute11,
1612 x_attribute12,
1613 x_attribute13,
1614 x_attribute14,
1615 x_attribute15,
1616 null,
1617 null,
1618 null,
1619 null,
1620 x_basis_type); --LBM enh
1621 ELSIF (x_process_code=3) then
1622 WSMPCPCS.Delete_Row (x_rowid);
1623 END IF;
1624 x_error_code := 0;
1625
1626 EXCEPTION
1627 WHEN e_proc_exception THEN
1628 x_error_msg := x_error_msg || ' - ' || 'WSMPPCPD.insert_sub_comps('||x_progress||')';
1629
1630 WHEN e_comp_exception THEN
1631 x_error_code := 1;
1632 fnd_message.set_name('WSM','WSM_MISSING_BOM_COMP');
1633 x_error_msg := fnd_message.get;
1634
1635 WHEN e_check_unique_exception THEN
1636 x_error_code := 2;
1637 x_error_msg := fnd_message.get;
1638
1639 WHEN e_check_common THEN
1640 x_error_code := 3;
1641 x_error_msg := fnd_message.get;
1642
1643 WHEN e_sub_comp_not_exists THEN
1644 x_error_code := 4;
1645 fnd_message.set_name('WSM','WSM_MISSING_SUBS_COMP');
1646 x_error_msg := fnd_message.get;
1647
1648 WHEN app_exceptions.record_lock_exception THEN
1649 x_error_code := 5;
1650 fnd_message.set_name('WSM','WSM_SUBS_COMP_LOCK_ERR');
1651 x_error_msg := fnd_message.get;
1652
1653 WHEN OTHERS THEN
1654 x_error_code := sqlcode;
1655 x_error_msg := 'WSMPPCPD.process_bom_sub_comp(' || x_progress || ')' || ' - ' || substr(sqlerrm, 1, 200);
1656
1657 END process_bom_sub_comp;
1658
1659
1660 /*===========================================================================
1661 PROCEDURE NAME: val_co_product_details
1662 This is a wrapper routine that in turn calls val_co_product and val_add_to_bill.
1663 Bug# 1418668. Split the validation portion from procedure process_co_product
1664 so that co_product form can call this procedure to validate before warning
1665 the user that he/she is about to change the BOM.
1666 ===========================================================================*/
1667
1668 PROCEDURE val_co_product_details(
1669 x_process_code IN NUMBER,
1670 x_rowid IN VARCHAR2 DEFAULT NULL,
1671 x_co_product_group_id IN NUMBER DEFAULT NULL,
1672 x_usage IN NUMBER DEFAULT NULL,
1673 x_co_product_id IN NUMBER DEFAULT NULL,
1674 x_org_id IN NUMBER DEFAULT NULL,
1675 x_primary_flag IN VARCHAR2 DEFAULT NULL,
1676 x_alternate_designator IN OUT NOCOPY VARCHAR2,
1677 x_bill_sequence_id IN OUT NOCOPY NUMBER,
1678 x_effectivity_date IN DATE DEFAULT NULL,
1679 x_disable_date IN DATE DEFAULT NULL,
1680 x_bill_insert IN OUT NOCOPY BOOLEAN,
1681 x_p_bill_insert IN OUT NOCOPY BOOLEAN,
1682 x_comp_insert IN OUT NOCOPY BOOLEAN,
1683 x_p_comp_insert IN OUT NOCOPY BOOLEAN,
1684 x_error_code IN OUT NOCOPY NUMBER,
1685 x_error_msg IN OUT NOCOPY VARCHAR2)
1686 IS
1687
1688 x_progress VARCHAR2(3) := NULL;
1689 e_proc_exception EXCEPTION;
1690
1691 x_quantity NUMBER := NULL;
1692 x_existing_bom NUMBER := NULL;
1693 x_bom_exists NUMBER := 0;
1694 x_comm_bill_seq_id NUMBER := NULL;
1695
1696 BEGIN
1697
1698 x_progress := '010';
1699 x_bill_insert := FALSE;
1700 x_p_bill_insert := FALSE;
1701 x_comp_insert := FALSE;
1702 x_p_comp_insert := FALSE;
1703
1704 IF (x_process_code IN (1,2)) THEN
1705 x_quantity := x_usage;
1706 END IF;
1707
1708 /* Cache org_id for use in this package. */
1709 g_org_id := x_org_id;
1710
1711 /* Bug# 1418668. Commented the following line as the form will call this
1712 validation routine only if x_process_code=1
1713 IF (x_process_code = 1) THEN */
1714
1715 /* Validate uniqueness of the co-product.. */
1716
1717 x_progress := '020';
1718
1719 WSMPVCPD.val_co_product (x_rowid,
1720 x_co_product_group_id,
1721 x_co_product_id,
1722 x_error_code,
1723 x_error_msg);
1724
1725 IF (x_error_code >= 2) THEN
1726 return;
1727 ELSIF (x_error_code <> 0) THEN
1728 raise e_proc_exception;
1729 END IF;
1730
1731 /*
1732 -- Verify if an alternate designator has been provided.
1733 -- If an alternate designator has been provided verify if there
1734 -- is a primary bill. If there is verify if the alternate bill
1735 -- already exists. If it does then verify whether you can
1736 -- add to the bill,if not return failure with a
1737 -- message. If the alternate bill does not exist create an
1738 -- alternate bill. If the primary does not exist, create the
1739 -- primary bill as well as the alternate bill. If an alternate
1740 -- designator has not been provided verify if there is a primary
1741 -- bill. If there is then verify whether you can add to the bill, if
1742 -- not, return failure with a message. If the primary bill does not exist
1743 -- create a primary bill. If this is a primary co-product insert
1744 -- a component for the bill.
1745 */
1746
1747 IF (x_alternate_designator is NULL) THEN
1748
1749 /* Verify if a primary bill exists. */
1750
1751 x_progress := '030';
1752
1753 BEGIN
1754
1755 SELECT bbom.bill_sequence_id,
1756 bbom.common_bill_sequence_id
1757 INTO x_existing_bom,
1758 x_comm_bill_seq_id
1759 FROM bom_bill_of_materials bbom
1760 WHERE bbom.assembly_item_id = x_co_product_id
1761 AND bbom.organization_id = x_org_id
1762 AND bbom.alternate_bom_designator is NULL;
1763
1764 EXCEPTION
1765 WHEN NO_DATA_FOUND THEN
1766 NULL;
1767 END;
1768
1769 IF (x_existing_bom is NOT NULL) THEN /* Primary BOM exists */
1770 x_bill_sequence_id := x_existing_bom;
1771 IF (x_primary_flag = 'Y') THEN
1772 WSMPVCPD.val_add_to_bill (x_co_product_group_id,
1773 x_org_id,
1774 x_co_product_id,
1775 x_comm_bill_seq_id,
1776 x_existing_bom,
1777 x_effectivity_date,
1778 x_disable_date,
1779 x_alternate_designator,
1780 x_error_code,
1781 x_error_msg);
1782
1783 IF (x_error_code > 0) THEN
1784 return;
1785 ELSIF (x_error_code <> 0) THEN
1786 raise e_proc_exception;
1787 END IF;
1788
1789 x_comp_insert := TRUE;
1790 END IF;
1791 ELSE /* Primary BOM does not exist. */
1792
1793 /* Create a Primary BOM */
1794
1795 x_alternate_designator := NULL;
1796 x_bill_insert := TRUE;
1797
1798 /*
1799 -- added by Bala.
1800 -- x_p_bill_insert := TRUE; -- Primary bill should be created.
1801 -- Later removed by raghu since we want x_bill_insert
1802 -- and not x_p_bill_insert. x_p_bill_insert is only
1803 -- for cases where we are trying to insert an alt
1804 -- bill and the primary bill does not exist.
1805 */
1806
1807
1808 IF (x_primary_flag = 'Y') THEN
1809 x_comp_insert := TRUE;
1810 ELSE
1811 x_comp_insert := FALSE;
1812 END IF;
1813 END IF;
1814
1815 ELSE
1816
1817 /* Verify if the specified alternate bill exists. */
1818
1819 BEGIN
1820
1821 x_progress := '060';
1822
1823 SELECT bbom.bill_sequence_id,
1824 bbom.common_bill_sequence_id
1825 INTO x_existing_bom,
1826 x_comm_bill_seq_id
1827 FROM bom_bill_of_materials bbom
1828 WHERE bbom.assembly_item_id = x_co_product_id
1829 AND bbom.organization_id = x_org_id
1830 AND bbom.alternate_bom_designator = x_alternate_designator;
1831
1832 EXCEPTION
1833 WHEN NO_DATA_FOUND THEN
1834 NULL;
1835 END;
1836
1837 IF (x_existing_bom is NOT NULL) THEN /* Alternate BOM exists */
1838 x_bill_sequence_id := x_existing_bom;
1839
1840 IF (x_primary_flag = 'Y') THEN
1841
1842 WSMPVCPD.val_add_to_bill (x_co_product_group_id,
1843 x_org_id,
1844 x_co_product_id,
1845 x_comm_bill_seq_id,
1846 x_existing_bom,
1847 x_effectivity_date,
1848 x_disable_date,
1849 x_alternate_designator,
1850 x_error_code,
1851 x_error_msg);
1852
1853 IF (x_error_code > 0) THEN
1854 return;
1855 ELSIF (x_error_code <> 0) THEN
1856 raise e_proc_exception;
1857 END IF;
1858
1859 x_comp_insert := TRUE;
1860
1861 END IF;
1862
1863 ELSE /* Alternate BOM does not exist. */
1864
1865 /* Verify if a primary bill exists. */
1866
1867 BEGIN
1868
1869 x_progress := '080';
1870
1871 -- commented out by abedajna on 10/12/00 for perf. tuning
1872 /*
1873 ** SELECT 1
1874 ** INTO x_bom_exists
1875 ** FROM sys.dual
1876 ** WHERE EXISTS (SELECT 1
1877 ** FROM bom_bill_of_materials bbom
1878 ** WHERE bbom.assembly_item_id = x_co_product_id
1879 ** AND bbom.organization_id = x_org_id
1880 ** AND bbom.alternate_bom_designator is NULL);
1881 ** EXCEPTION
1882 ** WHEN NO_DATA_FOUND THEN
1883 ** NULL;
1884 */
1885
1886 -- modification begin for perf. tuning.. abedajna 10/12/00
1887
1888 SELECT 1
1889 INTO x_bom_exists
1890 FROM bom_bill_of_materials bbom
1891 WHERE bbom.assembly_item_id = x_co_product_id
1892 AND bbom.organization_id = x_org_id
1893 AND bbom.alternate_bom_designator is NULL;
1894
1895 EXCEPTION
1896
1897 WHEN NO_DATA_FOUND THEN
1898 NULL;
1899
1900 WHEN TOO_MANY_ROWS THEN
1901 x_bom_exists := 1;
1902
1903 -- modification end for perf. tuning.. abedajna 10/12/00
1904
1905 END;
1906
1907 IF (x_bom_exists = 1) THEN
1908
1909 /* Create an alternate bill. */
1910
1911 x_bill_insert := TRUE;
1912
1913 IF (x_primary_flag = 'Y') THEN
1914 x_comp_insert := TRUE;
1915 ELSE
1916 x_comp_insert := FALSE;
1917 END IF;
1918
1919 ELSE
1920
1921 /* Create a primary bill followed by an alternate bill. */
1922
1923 x_progress := '090';
1924
1925 x_p_bill_insert := TRUE;
1926 x_bill_insert := TRUE;
1927 x_p_comp_insert := FALSE;
1928
1929 IF (x_primary_flag = 'Y') THEN
1930 x_comp_insert := TRUE;
1931 ELSE
1932 x_comp_insert := FALSE;
1933 END IF;
1934 END IF;
1935
1936 END IF;
1937
1938 END IF;
1939
1940 EXCEPTION
1941 WHEN e_proc_exception THEN
1942 x_error_msg := x_error_msg || ' - ' || 'WSMPPCPD.val_co_product_details('||x_progress||')'||' - '||substr(sqlerrm,1,200);
1943
1944 WHEN OTHERS THEN
1945 x_error_code := sqlcode;
1946 x_error_msg := 'WSMPPCPD.val_co_product_details(' || x_progress || ')' || ' - ' || substr(sqlerrm, 1, 200);
1947 END val_co_product_details;
1948
1949 /*===========================================================================
1950
1951 PROCEDURE NAME: process_co_product
1952 Bug# 1418668. Removed the validation portion from procedure process_co_product
1953 and created the procedure val_co_product_details so that co_product form can call
1954 this the procedure to validate before warning the user that he/she is about
1955 to change the BOM.
1956 ===========================================================================*/
1957
1958 PROCEDURE process_co_product(x_process_code IN NUMBER,
1959 x_rowid IN VARCHAR2 DEFAULT NULL,
1960 x_co_product_group_id IN NUMBER DEFAULT NULL,
1961 x_usage IN NUMBER DEFAULT NULL,
1962 x_duality_flag IN VARCHAR2 DEFAULT NULL,
1963 x_planning_factor IN NUMBER DEFAULT NULL,
1964 x_component_yield_factor IN NUMBER DEFAULT NULL,
1965 x_include_in_cost_rollup IN NUMBER DEFAULT NULL,
1966 x_wip_supply_type IN NUMBER DEFAULT NULL,
1967 x_supply_subinventory IN VARCHAR2 DEFAULT NULL,
1968 x_supply_locator_id IN NUMBER DEFAULT NULL,
1969 x_supply_locator IN VARCHAR2 DEFAULT NULL,
1970 x_component_remarks IN VARCHAR2 DEFAULT NULL,
1971 x_split IN NUMBER DEFAULT NULL,
1972 x_created_by IN NUMBER DEFAULT NULL,
1973 x_login_id IN NUMBER DEFAULT NULL,
1974 x_co_product_id IN NUMBER DEFAULT NULL,
1975 x_co_product_name IN VARCHAR2 DEFAULT NULL,
1976 x_revision IN VARCHAR2 DEFAULT NULL,
1977 x_org_id IN NUMBER DEFAULT NULL,
1978 x_org_code IN VARCHAR2 DEFAULT NULL,
1979 x_primary_flag IN VARCHAR2 DEFAULT NULL,
1980 x_alternate_designator IN OUT NOCOPY VARCHAR2,
1981 x_component_id IN NUMBER DEFAULT NULL,
1982 x_component_name IN VARCHAR2 DEFAULT NULL,
1983 x_bill_sequence_id IN OUT NOCOPY NUMBER,
1984 x_component_sequence_id IN OUT NOCOPY NUMBER,
1985 x_effectivity_date IN DATE DEFAULT NULL,
1986 x_disable_date IN DATE DEFAULT NULL,
1987 x_bill_insert IN BOOLEAN DEFAULT FALSE,
1988 x_p_bill_insert IN BOOLEAN DEFAULT FALSE,
1989 x_comp_insert IN BOOLEAN DEFAULT FALSE,
1990 x_p_comp_insert IN BOOLEAN DEFAULT FALSE,
1991 x_basis_type IN NUMBER , --LBM enh
1992 x_coprod_attribute_category VARCHAR2 DEFAULT NULL,
1993 x_coprod_attribute1 VARCHAR2 DEFAULT NULL,
1994 x_coprod_attribute2 VARCHAR2 DEFAULT NULL,
1995 x_coprod_attribute3 VARCHAR2 DEFAULT NULL,
1996 x_coprod_attribute4 VARCHAR2 DEFAULT NULL,
1997 x_coprod_attribute5 VARCHAR2 DEFAULT NULL,
1998 x_coprod_attribute6 VARCHAR2 DEFAULT NULL,
1999 x_coprod_attribute7 VARCHAR2 DEFAULT NULL,
2000 x_coprod_attribute8 VARCHAR2 DEFAULT NULL,
2001 x_coprod_attribute9 VARCHAR2 DEFAULT NULL,
2002 x_coprod_attribute10 VARCHAR2 DEFAULT NULL,
2003 x_coprod_attribute11 VARCHAR2 DEFAULT NULL,
2004 x_coprod_attribute12 VARCHAR2 DEFAULT NULL,
2005 x_coprod_attribute13 VARCHAR2 DEFAULT NULL,
2006 x_coprod_attribute14 VARCHAR2 DEFAULT NULL,
2007 x_coprod_attribute15 VARCHAR2 DEFAULT NULL,
2008 x_comp_attribute_category VARCHAR2 DEFAULT NULL,
2009 x_comp_attribute1 VARCHAR2 DEFAULT NULL,
2010 x_comp_attribute2 VARCHAR2 DEFAULT NULL,
2011 x_comp_attribute3 VARCHAR2 DEFAULT NULL,
2012 x_comp_attribute4 VARCHAR2 DEFAULT NULL,
2013 x_comp_attribute5 VARCHAR2 DEFAULT NULL,
2014 x_comp_attribute6 VARCHAR2 DEFAULT NULL,
2015 x_comp_attribute7 VARCHAR2 DEFAULT NULL,
2016 x_comp_attribute8 VARCHAR2 DEFAULT NULL,
2017 x_comp_attribute9 VARCHAR2 DEFAULT NULL,
2018 x_comp_attribute10 VARCHAR2 DEFAULT NULL,
2019 x_comp_attribute11 VARCHAR2 DEFAULT NULL,
2020 x_comp_attribute12 VARCHAR2 DEFAULT NULL,
2021 x_comp_attribute13 VARCHAR2 DEFAULT NULL,
2022 x_comp_attribute14 VARCHAR2 DEFAULT NULL,
2023 x_comp_attribute15 VARCHAR2 DEFAULT NULL,
2024 x_error_code IN OUT NOCOPY NUMBER,
2025 x_error_msg IN OUT NOCOPY VARCHAR2)
2026 IS
2027
2028 x_progress VARCHAR2(3) := NULL;
2029 e_proc_exception EXCEPTION;
2030 e_val_exception EXCEPTION;
2031 e_alt_val_exception EXCEPTION;
2032 e_no_bill_seq_exception EXCEPTION;
2033 e_no_comp_seq_exception EXCEPTION;
2034
2035 x_p_bill_sequence_id NUMBER := NULL;
2036 x_p_component_sequence_id NUMBER := NULL;
2037 x_quantity NUMBER := NULL;
2038 x_existing_bom NUMBER := NULL;
2039 x_bom_exists NUMBER := 0;
2040 x_alt_bom_exists NUMBER := 0;
2041 x_comm_bill_seq_id NUMBER := NULL;
2042 x_active_link NUMBER := NULL;
2043 x_dummy NUMBER := NULL;
2044 x_rec bom_bill_of_mtls_interface%ROWTYPE;
2045 x_rec_comp bom_inventory_comps_interface%ROWTYPE;
2046 l_err_text VARCHAR2(200);
2047 --bug 2987645
2048 l_effectivity_date DATE;
2049 --end bug 2987645
2050
2051 /* Bug# 1418668. Commented the cursor as it is not used any where
2052 CURSOR C (x_bill_seq_id NUMBER)IS
2053 SELECT 1
2054 FROM sys.dual
2055 WHERE EXISTS (SELECT 1
2056 FROM bom_inventory_components bic
2057 WHERE bic.bill_sequence_id = x_bill_seq_id
2058 AND (x_disable_date is NULL
2059 OR (trunc(x_disable_date) > trunc(bic.effectivity_date)))
2060 AND ((trunc(x_effectivity_date) < trunc(bic.disable_date))
2061 OR bic.disable_date is NULL)); */
2062 BEGIN
2063
2064 x_progress := '010';
2065
2066 IF (x_process_code IN (1,2)) THEN
2067 x_quantity := x_usage;
2068 END IF;
2069
2070 /* Cache org_id for use in this package. */
2071 g_org_id := x_org_id;
2072
2073 IF (x_process_code = 1) THEN
2074
2075 /* Obtain the sequence ids. */
2076 -- This part will be commented out since if we are using
2077 -- the bom bo api, the sequence ids cannot be passed in
2078 -- rather we should call these later to obtain the sequence
2079 -- ids that are created
2080
2081 /******************************
2082 IF (x_p_bill_insert) THEN
2083 WSMPCOGI.get_bill_comp_sequence (x_p_bill_sequence_id,
2084 x_error_code,
2085 x_error_msg);
2086 IF (x_error_code <> 0) THEN
2087 raise e_proc_exception;
2088 END IF;
2089 END IF;
2090
2091 IF (x_p_comp_insert) THEN
2092 WSMPCOGI.get_bill_comp_sequence (x_p_component_sequence_id,
2093 x_error_code,
2094 x_error_msg);
2095 IF (x_error_code <> 0) THEN
2096 raise e_proc_exception;
2097 END IF;
2098 END IF;
2099
2100 IF (x_bill_insert) THEN
2101 WSMPCOGI.get_bill_comp_sequence (x_bill_sequence_id,
2102 x_error_code,
2103 x_error_msg);
2104 IF (x_error_code <> 0) THEN
2105 raise e_proc_exception;
2106 END IF;
2107 END IF;
2108
2109 IF (x_comp_insert) THEN
2110 WSMPCOGI.get_bill_comp_sequence (x_component_sequence_id,
2111 x_error_code,
2112 x_error_msg);
2113 IF (x_error_code <> 0) THEN
2114 raise e_proc_exception;
2115 END IF;
2116 END IF;
2117 ***************************/
2118
2119 IF (x_p_bill_insert) THEN
2120 x_rec.assembly_item_id := x_co_product_id;
2121 x_rec.organization_id := x_org_id;
2122 x_rec.assembly_type := 1 /* Manufacturing */;
2123 x_rec.alternate_bom_designator := null;
2124 x_rec.revision := x_revision;
2125 x_rec.attribute_category := x_coprod_attribute_category;
2126 x_rec.attribute1 := x_coprod_attribute1;
2127 x_rec.attribute2 := x_coprod_attribute2;
2128 x_rec.attribute3 := x_coprod_attribute3;
2129 x_rec.attribute4 := x_coprod_attribute4;
2130 x_rec.attribute5 := x_coprod_attribute5;
2131 x_rec.attribute6 := x_coprod_attribute6;
2132 x_rec.attribute7 := x_coprod_attribute7;
2133 x_rec.attribute8 := x_coprod_attribute8;
2134 x_rec.attribute9 := x_coprod_attribute9;
2135 x_rec.attribute10 := x_coprod_attribute10;
2136 x_rec.attribute11 := x_coprod_attribute11;
2137 x_rec.attribute12 := x_coprod_attribute12;
2138 x_rec.attribute13 := x_coprod_attribute13;
2139 x_rec.attribute14 := x_coprod_attribute14;
2140 x_rec.attribute15 := x_coprod_attribute15;
2141
2142 x_progress := '100';
2143
2144 WSMPPCPD.insert_bill ( x_rec,
2145 x_co_product_name,
2146 x_org_code,
2147 x_error_code,
2148 x_error_msg);
2149
2150 IF (x_error_code <> 0) THEN
2151 raise e_proc_exception;
2152 END IF;
2153
2154 -- now go ahead and call the BOM Business Object API
2155 -- to insert this primary bill
2156
2157 WSMPPCPD.call_bom_bo_api (
2158 p_bom_header_rec => g_bom_header_rec,
2159 x_error_code => x_error_code,
2160 x_error_msg => x_error_msg );
2161
2162 IF x_error_code <> 0 THEN
2163 raise e_proc_exception;
2164 END IF;
2165
2166 x_progress := '105';
2167
2168 -- initialize parameters passed to the bom bo api
2169
2170 g_bom_header_rec := Bom_Bo_Pub.G_MISS_BOM_HEADER_REC;
2171 g_component_tbl.delete;
2172 g_component_tbl := Bom_Bo_Pub.G_MISS_BOM_COMPONENT_TBL;
2173 /*coprod enh p2*/
2174 --if ((g_subs_rec_set IS NULL) OR (g_subs_rec_set <> 'Y')) then
2175 g_subs_comp_tbl.delete;
2176 g_subs_comp_tbl := Bom_Bo_Pub.G_MISS_BOM_SUB_COMPONENT_TBL;
2177 -- end if;
2178 /*end coprod enh p2*/
2179
2180 END IF;
2181
2182
2183 /*
2184 if we are creating the bill with the alternate_designator as
2185 specified in the Co-Products form, then we are better off creating
2186 the Bill object in its entirety - ie, a Bill Header, Component and
2187 any substitute component. What we did in the previous insert was
2188 just creating a Primary Bill for the sake of being able to create
2189 the alternate bill (designator) that was specified in the co-products
2190 form. So in this case we will not call the bom bo api till the entire
2191 object has been prepared.
2192 */
2193
2194 IF (x_bill_insert) THEN
2195 x_rec.assembly_item_id := x_co_product_id;
2196 x_rec.organization_id := x_org_id;
2197 x_rec.assembly_type := 1 /* Manufacturing */;
2198 x_rec.alternate_bom_designator := x_alternate_designator;
2199 x_rec.revision := x_revision;
2200 x_rec.attribute_category := x_coprod_attribute_category;
2201 x_rec.attribute1 := x_coprod_attribute1;
2202 x_rec.attribute2 := x_coprod_attribute2;
2203 x_rec.attribute3 := x_coprod_attribute3;
2204 x_rec.attribute4 := x_coprod_attribute4;
2205 x_rec.attribute5 := x_coprod_attribute5;
2206 x_rec.attribute6 := x_coprod_attribute6;
2207 x_rec.attribute7 := x_coprod_attribute7;
2208 x_rec.attribute8 := x_coprod_attribute8;
2209 x_rec.attribute9 := x_coprod_attribute9;
2210 x_rec.attribute10 := x_coprod_attribute10;
2211 x_rec.attribute11 := x_coprod_attribute11;
2212 x_rec.attribute12 := x_coprod_attribute12;
2213 x_rec.attribute13 := x_coprod_attribute13;
2214 x_rec.attribute14 := x_coprod_attribute14;
2215 x_rec.attribute15 := x_coprod_attribute15;
2216
2217 x_progress := '110';
2218
2219 WSMPPCPD.insert_bill ( x_rec,
2220 x_co_product_name,
2221 x_org_code,
2222 x_error_code,
2223 x_error_msg);
2224
2225 IF (x_error_code <> 0) THEN
2226 raise e_proc_exception;
2227 END IF;
2228
2229 END IF; -- End of x_bill_insert
2230
2231 --bug 2987645
2232 IF x_effectivity_date < sysdate THEN
2233 l_effectivity_date := sysdate;
2234 ELSE
2235 l_effectivity_date := x_effectivity_date;
2236 END IF;
2237 --end bug 2987645
2238
2239 IF (x_comp_insert) THEN
2240
2241 x_rec_comp.alternate_bom_designator := x_alternate_designator;
2242 x_rec_comp.planning_factor := x_planning_factor;
2243 x_rec_comp.component_yield_factor := x_component_yield_factor;
2244 x_rec_comp.include_in_cost_rollup := x_include_in_cost_rollup;
2245 x_rec_comp.wip_supply_type := x_wip_supply_type;
2246 x_rec_comp.supply_subinventory := x_supply_subinventory;
2247 x_rec_comp.supply_locator_id := x_supply_locator_id;
2248 x_rec_comp.component_remarks := x_component_remarks;
2249 x_rec_comp.operation_seq_num := 1;
2250 x_rec_comp.component_item_id := x_component_id;
2251 x_rec_comp.component_quantity := x_quantity;
2252 --bug 2987645
2253 -- x_rec_comp.effectivity_date := x_effectivity_date;
2254 x_rec_comp.effectivity_date := l_effectivity_date;
2255 --end bug 2987645
2256 x_rec_comp.disable_date := x_disable_date;
2257 x_rec_comp.assembly_item_id := x_co_product_id;
2258 x_rec_comp.process_flag := 1;
2259 x_rec_comp.organization_id := x_org_id;
2260 x_rec_comp.basis_type := x_basis_type; --LBM enh
2261 x_rec_comp.attribute_category := x_comp_attribute_category;
2262 x_rec_comp.attribute1 := x_comp_attribute1;
2263 x_rec_comp.attribute2 := x_comp_attribute2;
2264 x_rec_comp.attribute3 := x_comp_attribute3;
2265 x_rec_comp.attribute4 := x_comp_attribute4;
2266 x_rec_comp.attribute5 := x_comp_attribute5;
2267 x_rec_comp.attribute6 := x_comp_attribute6;
2268 x_rec_comp.attribute7 := x_comp_attribute7;
2269 x_rec_comp.attribute8 := x_comp_attribute8;
2270 x_rec_comp.attribute9 := x_comp_attribute9;
2271 x_rec_comp.attribute10 := x_comp_attribute10;
2272 x_rec_comp.attribute11 := x_comp_attribute11;
2273 x_rec_comp.attribute12 := x_comp_attribute12;
2274 x_rec_comp.attribute13 := x_comp_attribute13;
2275 x_rec_comp.attribute14 := x_comp_attribute14;
2276 x_rec_comp.attribute15 := x_comp_attribute15;
2277
2278 x_progress := '120';
2279
2280
2281 WSMPPCPD.insert_component ( x_rec_comp,
2282 x_component_name,
2283 x_org_code,
2284 x_co_product_name,
2285 x_supply_locator,
2286 x_error_code,
2287 x_error_msg);
2288 IF (x_error_code <> 0) THEN
2289 raise e_proc_exception;
2290 END IF;
2291
2292 /* Insert substitutes. */
2293
2294 WSMPPCPD.insert_sub_comps (x_co_product_group_id,
2295 x_co_product_name,
2296 x_alternate_designator,
2297 x_component_name,
2298 --bug 2987645
2299 -- x_effectivity_date,
2300 l_effectivity_date,
2301 --end bug 2987645
2302 x_org_code,
2303 x_component_sequence_id,
2304 x_quantity,
2305 x_error_code,
2306 x_error_msg);
2307
2308 IF (x_error_code < 0) THEN
2309 raise e_proc_exception;
2310 ELSIF (x_error_code > 0) THEN
2311 return;
2312 END IF;
2313
2314 END IF; -- End of x_comp_insert
2315
2316 -- now go ahead and call the BOM Business Object API
2317 -- to insert the bill header, component and any substitute
2318 -- components
2319
2320 x_progress := '123';
2321
2322 -- Debug code insert by Bala.
2323 -- Start
2324
2325 If x_bill_insert AND (x_comp_insert <> TRUE) Then
2326
2327 WSMPPCPD.call_bom_bo_api (
2328 p_bom_header_rec => g_bom_header_rec,
2329 x_error_code => x_error_code,
2330 x_error_msg => x_error_msg );
2331
2332 End If;
2333
2334 IF x_error_code <> 0 THEN
2335 raise e_proc_exception;
2336 END IF;
2337
2338 x_progress := '124';
2339 -- If x_bill_insert AND x_comp_insert Then /* defensive check for bill */
2340 /*
2341 ** Line above commented out by Bala, July20th, 2000.
2342 **
2343 ** Bug# 1359564 - where there can be a situation where a component
2344 ** need to be created even if the bill header is not created (but
2345 ** might already be existing because of ALT_DESIGNATOR being created)
2346 **
2347 ** Scenario: No coprod defintion exist for coprodA as primary coprod.
2348 **
2349 ** coprod defintion: comp1-ALT1-coprodA.(alt designator is ALT1)
2350 ** This will create a primary BOM for coprodA with nocomponent.
2351 ** - ALTBILL for coprodA with ALT1 with component comp1.
2352 **
2353 ** Now if you again try to define a coproduct definiton as follows;
2354 ** comp2-NULL-coprodA (where the alternate designator is NULL).
2355 ** Now this will findout that the primary bill exists and hence
2356 ** will not create primary bill (x_bill_insert = FALSE).
2357 ** But still we need to create the component definiton and hence
2358 ** we need to check only for x_comp_insert = TRUE and not both.
2359 **
2360 ** - Bala BALAKUMAR, July 20th, 2000.
2361 */
2362
2363 If x_comp_insert Then /*Bug#1359654 fix */
2364
2365 WSMPPCPD.call_bom_bo_api (
2366 p_bom_header_rec => g_bom_header_rec,
2367 p_component_tbl => g_component_tbl,
2368 p_subs_comp_tbl => g_subs_comp_tbl,
2369 x_error_code => x_error_code,
2370 x_error_msg => x_error_msg );
2371
2372 End If;
2373
2374 IF x_error_code <> 0 THEN
2375 raise e_proc_exception;
2376 END IF;
2377
2378 -- End of Debug code test.
2379
2380
2381 -- initialize parameters passed to the bom bo api
2382 g_bom_header_rec := Bom_Bo_Pub.G_MISS_BOM_HEADER_REC;
2383 g_component_tbl.delete;
2384 g_component_tbl := Bom_Bo_Pub.G_MISS_BOM_COMPONENT_TBL;
2385 /*coprod enh p2*/
2386 --if ((g_subs_rec_set IS NULL) OR (g_subs_rec_set <> 'Y')) then
2387 g_subs_comp_tbl.delete;
2388 g_subs_comp_tbl := Bom_Bo_Pub.G_MISS_BOM_SUB_COMPONENT_TBL;
2389 --end if;
2390 /*end coprod enh p2*/
2391
2392 -- now call a private bom api to obtain the bill_sequence_id and
2393 -- the component_sequence_id
2394
2395 x_progress := '125';
2396
2397 x_bill_sequence_id := BOM_Val_To_Id.Bill_Sequence_Id (
2398 p_assembly_item_id => x_co_product_id,
2399 p_alternate_bom_code => x_alternate_designator,
2400 p_organization_id => x_org_id,
2401 x_err_text => l_err_text );
2402
2403 IF x_bill_sequence_id is NULL THEN
2404 -- x_error_msg := 'Unable to obtain Bill_Sequence_Id';
2405 fnd_message.set_name('WSM', 'WSM_NO_BILL_SEQ_ID');
2406 raise e_no_bill_seq_exception;
2407 END IF;
2408
2409 x_progress := '126';
2410
2411 -- Added By Bala.
2412 -- Need to be performed only when inserting a component.
2413
2414 If x_comp_insert Then
2415 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2416 DECLARE
2417 l_comp_eff_date DATE;
2418 BEGIN
2419 select bic.effectivity_date
2420 into l_comp_eff_date
2421 from bom_inventory_components bic,
2422 bom_bill_of_materials bom
2423 where bom.bill_sequence_id = x_bill_sequence_id
2424 and bic.bill_sequence_id = bom.common_bill_sequence_id
2425 and bic.component_item_id = x_component_id
2426 and bic.operation_seq_num = 1;
2427
2428 FND_LOG.STRING(FND_LOG.LEVEL_EVENT, 'wsm%',
2429 'component effectivity date = '||to_char(l_comp_eff_date, 'DD-MON-YYYY HH24:MI:SS')||
2430 ' x_component_id = '||x_component_id||
2431 ' l_effectivity_date = '||to_char(l_effectivity_date, 'DD-MON-YYYY HH24:MI:SS')||
2432 ' x_bill_sequence_id = '||x_bill_sequence_id);
2433 EXCEPTION
2434 WHEN no_data_found THEN
2435 FND_LOG.STRING(FND_LOG.LEVEL_EVENT, 'wsm%',
2436 'no_data_found ');
2437 WHEN too_many_rows THEN
2438 null;
2439 END;
2440 END IF;
2441 x_component_sequence_id := WSMPCOGI.Get_Component_Sequence_Id (
2442 p_component_item_id => x_component_id,
2443 p_operation_sequence_num => 1,
2444 --bug 2987645
2445 -- p_effectivity_date => x_effectivity_date,
2446 p_effectivity_date => l_effectivity_date,
2447 --end bug 2987645
2448 p_bill_sequence_id => x_bill_sequence_id,
2449 x_err_text => l_err_text );
2450
2451 IF x_component_sequence_id is NULL THEN
2452 -- x_error_msg := l_err_text ||'- Unable to obtain Component_Sequence_Id';
2453 /*
2454 x_error_msg := l_err_text ||
2455 'comp_id is '|| x_component_id ||
2456 '; opseqnum is 1' ||
2457 '; effectivity date is '|| x_effectivity_date ||
2458 '; from bill seq Id is '|| x_bill_sequence_id ||
2459 '; for co-product Id '|| x_co_product_id ||
2460 '; - Unable to obtain Component_Sequence_Id';
2461 raise e_proc_exception;
2462 */
2463
2464 fnd_message.set_name('WSM', 'WSM_NO_COMP_SEQ_ID');
2465 raise e_no_comp_seq_exception;
2466 END IF;
2467
2468 End If; -- End of getCompseqId if x_comp_insert is True.
2469
2470 /* Call BOM api to process interface. */
2471
2472 /*******
2473 This call will be commented out since we need to use the
2474 BOM Business Object API for 11.i.2 instead of the Open Interface
2475
2476 x_progress := '140';
2477
2478 x_error_code := bompopif.bmopinp_open_interface_process (org_id => x_org_id,
2479 all_org => 2,
2480 val_rtg_flag => 2,
2481 val_bom_flag => 1,
2482 pro_rtg_flag => 2,
2483 pro_bom_flag => 1,
2484 del_rec_flag => 1,
2485 prog_appid => -1,
2486 prog_id => -1,
2487 request_id => -1,
2488 user_id => x_created_by,
2489 login_id => x_login_id,
2490 err_text => x_error_msg);
2491
2492 IF (x_error_code <> 0) THEN
2493 raise e_proc_exception;
2494 END IF;
2495
2496 *******/
2497
2498 ELSIF (x_process_code = 2) THEN
2499
2500 /* For Update
2501 Bill attribute columns are not being updated
2502 based on discussion with B. Arvindh (02/09/98)
2503 Update component columns. */
2504
2505 x_progress := '150';
2506
2507 /* Lock corresponding component prior to update. */
2508
2509 WSMPPCPD.lock_component(x_component_sequence_id,
2510 x_error_code,
2511 x_error_msg);
2512
2513 IF (x_error_code > 0) THEN
2514 return;
2515 ELSIF (x_error_code < 0) THEN
2516 raise e_proc_exception;
2517 END IF;
2518
2519 /*
2520 ** This is the SQL we have to add the columns
2521 ** to update the component details on update mode.
2522 ** Bala
2523 */
2524
2525 UPDATE bom_inventory_components
2526 SET component_quantity = x_quantity,
2527 basis_type = decode(x_basis_type, 2, 2, null), --LBM enh
2528 disable_date = x_disable_date,
2529 effectivity_date = x_effectivity_date,
2530 attribute_category = x_comp_attribute_category,
2531 attribute1 = x_comp_attribute1,
2532 attribute2 = x_comp_attribute2,
2533 attribute3 = x_comp_attribute3,
2534 attribute4 = x_comp_attribute4,
2535 attribute5 = x_comp_attribute5,
2536 attribute6 = x_comp_attribute6,
2537 attribute7 = x_comp_attribute7,
2538 attribute8 = x_comp_attribute8,
2539 attribute9 = x_comp_attribute9,
2540 attribute10 = x_comp_attribute10,
2541 attribute11 = x_comp_attribute11,
2542 attribute12 = x_comp_attribute12,
2543 attribute13 = x_comp_attribute13,
2544 attribute14 = x_comp_attribute14,
2545 attribute15 = x_comp_attribute15
2546 WHERE common_component_sequence_id = x_component_sequence_id
2547 OR component_sequence_id = x_component_sequence_id;
2548 /* Modified where clause for bug 5519205.
2549 Use OR instead of nvl so that the query is performant. */
2550
2551 ELSIF (x_process_code = 3) THEN
2552
2553 /*
2554 -- For Deletes...
2555 -- Call routine to update the disable date
2556 -- for the component on the BOM for the co-product's.
2557 -- appropriate bill.
2558 */
2559
2560 /* Lock corresponding component prior to update. */
2561
2562 WSMPPCPD.lock_component (x_component_sequence_id,
2563 x_error_code,
2564 x_error_msg);
2565
2566 IF (x_error_code > 0) THEN
2567 return;
2568 ELSIF (x_error_code < 0) THEN
2569 raise e_proc_exception;
2570 END IF;
2571
2572 UPDATE bom_inventory_components
2573 SET disable_date = sysdate
2574 WHERE component_sequence_id = x_component_sequence_id;
2575
2576 END IF;
2577
2578 /******* Commenting this out since set_common_bill_new will not do the job
2579
2580 IF (x_process_code = 1) THEN
2581
2582 -- Call routine to update the common bill
2583 -- information.
2584
2585 x_progress := '160';
2586
2587 WSMPPCPD.set_common_bill (x_co_product_group_id,
2588 x_org_id,
2589 x_co_product_id,
2590 x_bill_sequence_id,
2591 x_component_sequence_id,
2592 x_primary_flag,
2593 x_error_code,
2594 x_error_msg);
2595 IF (x_error_code = 2) THEN
2596 return;
2597 ELSIF (x_error_code <> 0) THEN
2598 raise e_proc_exception;
2599 END IF;
2600 END IF;
2601 *******/
2602
2603 x_error_code := 0;
2604
2605 EXCEPTION
2606
2607 WHEN e_no_bill_seq_exception THEN
2608 x_error_code := -901;
2609 x_error_msg := fnd_message.get;
2610
2611 WHEN e_no_comp_seq_exception THEN
2612 x_error_code := -902;
2613 x_error_msg := fnd_message.get;
2614
2615 WHEN e_proc_exception THEN
2616 --x_error_code := -900; -- removed by raghu since we need the
2617 -- error code from call_bom_bo_api
2618 -- for error handling
2619 -- added by Bala.
2620 x_error_msg := x_error_msg || ' - ' || 'WSMPPCPD.process_co_product('||x_progress||')'||' - '||substr(sqlerrm,1,200);
2621
2622 WHEN OTHERS THEN
2623 x_error_code := sqlcode;
2624 x_error_msg := 'WSMPPCPD.process_co_product(' || x_progress || ')' || ' - ' || substr(sqlerrm, 1, 200);
2625
2626 END process_co_product;
2627
2628
2629 /*===========================================================================
2630
2631 PROCEDURE NAME: set_common_bill
2632
2633 ===========================================================================*/
2634
2635 PROCEDURE set_common_bill ( x_co_product_group_id IN NUMBER,
2636 x_org_id IN NUMBER,
2637 x_co_product_id IN NUMBER,
2638 x_bill_sequence_id IN NUMBER,
2639 x_component_sequence_id IN NUMBER,
2640 x_primary_flag IN VARCHAR2,
2641 x_error_code IN OUT NOCOPY NUMBER,
2642 x_error_msg IN OUT NOCOPY VARCHAR2) IS
2643
2644 x_progress VARCHAR2(3) := NULL;
2645 e_set_common_bill EXCEPTION;
2646 e_components_exist EXCEPTION;
2647 e_c_bill_exists EXCEPTION;
2648 e_proc_exception EXCEPTION;
2649
2650 x_c_org_id NUMBER := NULL;
2651 x_c_assembly_id NUMBER := NULL;
2652 x_c_bill_seq_id NUMBER := NULL;
2653 x_count_comp NUMBER := 0;
2654 x_current_comm_bill NUMBER := NULL;
2655
2656 CURSOR S IS
2657 SELECT bcp.bill_sequence_id
2658 FROM wsm_co_products bcp
2659 WHERE bcp.co_product_group_id = x_co_product_group_id
2660 AND bcp.bill_sequence_id <> x_bill_sequence_id
2661 AND bcp.co_product_id is NOT NULL;
2662
2663 BEGIN
2664
2665 x_progress := '010';
2666
2667 /*
2668 -- If this is the bill corresponding
2669 -- to the primary co-product then update
2670 -- the other co-product's bills to point
2671 -- to this bill. Since this processing is done
2672 -- prior to the insert, the bill information is
2673 -- obtained from the BOM tables.
2674 */
2675
2676 IF (nvl(x_primary_flag, 'N') = 'Y') THEN
2677
2678 x_c_bill_seq_id := x_bill_sequence_id;
2679 x_c_org_id := x_org_id;
2680 x_c_assembly_id := x_co_product_id;
2681
2682 FOR S_rec IN S LOOP
2683
2684 x_progress := '020';
2685
2686 /*
2687 -- Verify that there aren't any
2688 -- components for the bill. */
2689
2690 SELECT count (1)
2691 INTO x_count_comp
2692 FROM bom_inventory_components
2693 WHERE bill_sequence_id = S_rec.bill_sequence_id;
2694
2695 IF (x_count_comp = 1) THEN
2696 raise e_components_exist;
2697 END IF;
2698
2699 /* Verify that the component does not
2700 -- point to any bill other than the bill it is being
2701 -- updated to point to. */
2702
2703 BEGIN
2704
2705 x_progress := '030';
2706
2707 SELECT bbom.common_bill_sequence_id
2708 INTO x_current_comm_bill
2709 FROM bom_bill_of_materials bbom
2710 WHERE bbom.bill_sequence_id = S_rec.bill_sequence_id
2711 AND EXISTS (SELECT 1
2712 FROM wsm_co_products bcp
2713 WHERE bcp.bill_sequence_id = bbom.common_bill_sequence_id
2714 AND (bcp.disable_date is NULL
2715 OR bcp.disable_date > sysdate)
2716 AND bcp.co_product_group_id <> x_co_product_group_id);
2717
2718 EXCEPTION
2719 WHEN NO_DATA_FOUND THEN
2720 NULL;
2721 END;
2722
2723 IF ((x_current_comm_bill is NOT NULL) AND
2724 (x_current_comm_bill <> x_c_bill_seq_id)) THEN
2725 raise e_c_bill_exists;
2726 END IF;
2727
2728 x_progress := '040';
2729
2730 /* -- Lock corresponding bill prior to update. */
2731
2732 WSMPPCPD.lock_bill (S_rec.bill_sequence_id,
2733 x_error_code,
2734 x_error_msg);
2735
2736 IF (x_error_code > 0) THEN
2737 return;
2738 ELSIF (x_error_code < 0) THEN
2739 raise e_proc_exception;
2740 END IF;
2741
2742 UPDATE bom_bill_of_materials
2743 SET common_assembly_item_id = x_c_assembly_id,
2744 common_organization_id = x_c_org_id,
2745 common_bill_sequence_id = x_c_bill_seq_id
2746 WHERE bill_sequence_id = S_rec.bill_sequence_id;
2747
2748 END LOOP;
2749
2750 ELSE
2751
2752 /*
2753 -- Obtain the bill_sequence_id of the
2754 -- primary co-product and update the current
2755 -- bill.
2756 */
2757
2758 x_progress := '050';
2759
2760 BEGIN
2761
2762 SELECT bcp.bill_sequence_id,
2763 bcp.organization_id,
2764 bcp.co_product_id
2765 INTO x_c_bill_seq_id,
2766 x_c_org_id,
2767 x_c_assembly_id
2768 FROM wsm_co_products bcp
2769 WHERE bcp.co_product_group_id = x_co_product_group_id
2770 AND bcp.primary_flag = 'Y'
2771 AND rownum = 1;
2772
2773 EXCEPTION
2774 WHEN NO_DATA_FOUND THEN
2775 x_error_code := 0;
2776 return;
2777 END;
2778
2779 /*
2780 -- Verify that there aren't any
2781 -- components for the bill.
2782 */
2783
2784 x_progress := '060';
2785
2786 SELECT count(*)
2787 INTO x_count_comp
2788 FROM bom_inventory_components
2789 WHERE bill_sequence_id = x_bill_sequence_id;
2790
2791 IF (x_count_comp = 1) THEN
2792 raise e_components_exist;
2793 END IF;
2794
2795 /*
2796 -- Verify that the component does not
2797 -- point to any bill other than the bill it is being
2798 -- updated to point to.
2799 */
2800
2801 BEGIN
2802
2803 x_progress := '070';
2804
2805 SELECT bbom.common_bill_sequence_id
2806 INTO x_current_comm_bill
2807 FROM bom_bill_of_materials bbom
2808 WHERE bbom.bill_sequence_id = x_bill_sequence_id
2809 AND EXISTS (SELECT 1
2810 FROM wsm_co_products bcp
2811 WHERE bcp.bill_sequence_id = bbom.common_bill_sequence_id
2812 AND ( bcp.disable_date is NULL
2813 OR bcp.disable_date > sysdate)
2814 AND bcp.co_product_group_id <> x_co_product_group_id);
2815
2816 EXCEPTION
2817 WHEN NO_DATA_FOUND THEN
2818 NULL;
2819 END;
2820
2821 IF ((x_current_comm_bill is NOT NULL) AND
2822 (x_current_comm_bill <> x_c_bill_seq_id)) THEN
2823 raise e_c_bill_exists;
2824 END IF;
2825
2826 IF (x_current_comm_bill is NULL) THEN
2827 x_progress := '080';
2828
2829 /* -- Lock corresponding bill prior to update. */
2830
2831 WSMPPCPD.lock_bill (x_bill_sequence_id,
2832 x_error_code,
2833 x_error_msg);
2834
2835 IF (x_error_code > 0) THEN
2836 return;
2837 ELSIF (x_error_code < 0) THEN
2838 raise e_proc_exception;
2839 END IF;
2840
2841 UPDATE bom_bill_of_materials
2842 SET common_assembly_item_id = x_c_assembly_id,
2843 common_organization_id = x_c_org_id,
2844 common_bill_sequence_id = x_c_bill_seq_id
2845 WHERE bill_sequence_id = x_bill_sequence_id;
2846
2847 END IF;
2848 END IF;
2849
2850 x_error_code := 0;
2851
2852 EXCEPTION
2853 WHEN e_proc_exception THEN
2854 x_error_msg := x_error_msg || ' - ' || 'WSMPPCPD.set_common_bill('||x_progress||')';
2855
2856 WHEN e_c_bill_exists THEN
2857 x_error_code := 3;
2858 -- x_error_msg := 'Cannot update bill. It currently points to another common bill.';
2859 fnd_message.set_name('WSM', 'WSM_NO_BILL_UPDATE');
2860 x_error_msg := fnd_message.get;
2861
2862 WHEN e_components_exist THEN
2863 x_error_code := 2;
2864 -- x_error_msg := 'Components exist on this bill. Cannot set common bill.';
2865 fnd_message.set_name('WSM', 'WSM_BILL_COMPONENT_EXIST');
2866 x_error_msg := fnd_message.get;
2867
2868 WHEN e_set_common_bill THEN
2869 x_error_code := 1;
2870 -- x_error_msg := 'Insufficient arguments to WSMPPCPD.set_common_bill';
2871 fnd_message.set_name('WSM', 'WSM_INSUFFICIENT_ARGUMENTS');
2872 fnd_message.set_token('OBJECT_NAME', 'WSMPPCPD.set_common_bill');
2873 x_error_msg := fnd_message.get;
2874
2875 WHEN OTHERS THEN
2876 x_error_code := sqlcode;
2877 x_error_msg := 'WSMPPCPD.set_common_bill(' || x_progress || ')' || ' - ' || substr(sqlerrm, 1, 200);
2878 END set_common_bill;
2879
2880 /* ===========================================================================
2881
2882 PROCEDURE NAME: delete_component
2883
2884 =========================================================================== */
2885
2886 PROCEDURE delete_component(x_co_product_group_id IN NUMBER,
2887 x_rowid IN VARCHAR2,
2888 x_error_code IN OUT NOCOPY NUMBER,
2889 x_error_msg IN OUT NOCOPY VARCHAR2)
2890 IS
2891
2892 x_progress VARCHAR2(3) := NULL;
2893 e_proc_exception EXCEPTION;
2894 e_delete_component EXCEPTION;
2895
2896 CURSOR C IS
2897 SELECT rowid
2898 FROM wsm_co_prod_comp_substitutes
2899 WHERE co_product_group_id = x_co_product_group_id;
2900
2901 CURSOR S IS
2902 SELECT bcp.co_product_id
2903 FROM wsm_co_products bcp
2904 WHERE bcp.co_product_group_id = x_co_product_group_id
2905 AND bcp.co_product_id is NOT NULL;
2906
2907 BEGIN
2908
2909 x_progress := '010';
2910
2911 IF (x_co_product_group_id IS NULL) THEN
2912 raise e_delete_component;
2913 END IF;
2914
2915 /* -- Delete all the co-products. */
2916
2917 x_progress := '020';
2918
2919 FOR S_rec IN S LOOP
2920
2921 WSMPPCPD.delete_co_product (x_co_product_group_id,
2922 S_rec.co_product_id,
2923 x_error_code,
2924 x_error_msg);
2925
2926 IF (x_error_code = 5) THEN
2927 return;
2928 ELSIF (x_error_code <> 0) THEN
2929 raise e_proc_exception;
2930 END IF;
2931 END LOOP;
2932
2933 /* -- Delete all the substitutes. */
2934
2935 x_progress := '033';
2936
2937 FOR C_rec IN C LOOP
2938 WSMPCPCS.delete_row (C_rec.rowid);
2939 END LOOP;
2940
2941 x_progress := '040';
2942
2943 WSMPCPDS.delete_row (x_rowid);
2944
2945 x_error_code := 0;
2946
2947 EXCEPTION
2948 WHEN e_delete_component THEN
2949 x_error_code := 1;
2950 -- x_error_msg := 'Insufficient arguments to WSMPPCPD.delete_component';
2951 fnd_message.set_name('WSM', 'WSM_INSUFFICIENT_ARGUMENTS');
2952 fnd_message.set_token('OBJECT_NAME', 'WSMPPCPD.delete_component');
2953 x_error_msg := fnd_message.get;
2954
2955 WHEN e_proc_exception THEN
2956 x_error_msg := x_error_msg || ' - ' || 'WSMPPCPD.delete_component('||x_progress||')';
2957
2958 WHEN OTHERS THEN
2959 x_error_code := sqlcode;
2960 x_error_msg := 'WSMPPCPD.delete_component(' || x_progress || ')' || ' - ' || substr(sqlerrm, 1, 200);
2961 END delete_component;
2962
2963
2964 /* ===========================================================================
2965
2966 PROCEDURE NAME: delete_co_product
2967
2968 =========================================================================== */
2969
2970 PROCEDURE delete_co_product(x_co_product_group_id IN NUMBER,
2971 x_co_product_id IN NUMBER,
2972 x_error_code IN OUT NOCOPY NUMBER,
2973 x_error_msg IN OUT NOCOPY VARCHAR2)
2974 IS
2975
2976 x_progress VARCHAR2(3) := NULL;
2977 e_proc_exception EXCEPTION;
2978 e_delete_co_product EXCEPTION;
2979 x_component_sequence_id NUMBER := NULL;
2980 x_effectivity_date DATE;
2981 x_rowid VARCHAR2(20):= NULL;
2982 x_component_id NUMBER := NULL;
2983 x_alternate VARCHAR2(10):= NULL;
2984 x_bill_sequence_id NUMBER := NULL;
2985 x_primary_flag VARCHAR2(1) := 'N';
2986
2987 BEGIN
2988
2989 x_progress := '010';
2990
2991 IF ((x_co_product_group_id IS NULL) OR
2992 (x_co_product_id IS NULL)) THEN
2993 raise e_delete_co_product;
2994 END IF;
2995
2996 /* -- Obtain information from wsm_co_products. */
2997
2998 x_progress := '020';
2999 /******
3000 SELECT bcp.component_sequence_id,
3001 bcp.component_id,
3002 bcp.effectivity_date,
3003 bcp.rowid
3004 INTO x_component_sequence_id,
3005 x_component_id,
3006 x_effectivity_date,
3007 x_rowid
3008 FROM wsm_co_products bcp
3009 WHERE bcp.co_product_group_id = x_co_product_group_id
3010 AND bcp.co_product_id = x_co_product_id;
3011 ******/
3012
3013 SELECT bcp.component_id,
3014 bcp.effectivity_date,
3015 bcp.bill_sequence_id,
3016 bcp.primary_flag,
3017 bcp.rowid
3018 INTO x_component_id,
3019 x_effectivity_date,
3020 x_bill_sequence_id,
3021 x_primary_flag,
3022 x_rowid
3023 FROM wsm_co_products bcp
3024 WHERE bcp.co_product_group_id = x_co_product_group_id
3025 AND bcp.co_product_id = x_co_product_id;
3026
3027 /* -- Update bill. */
3028
3029 x_progress := '030';
3030
3031 /*******
3032 -- commented out by Bala on June 23rd, 2000.
3033
3034 WSMPPCPD.process_co_product (x_process_code => 3,
3035 x_bill_sequence_id => x_bill_sequence_id,
3036 x_component_sequence_id => x_component_sequence_id,
3037 x_alternate_designator => x_alternate,
3038 x_error_code => x_error_code,
3039 x_error_msg => x_error_msg);
3040
3041 IF (x_error_code = 5) THEN
3042 return;
3043 ELSIF (x_error_code <> 0) THEN
3044 raise e_proc_exception;
3045 END IF;
3046 *******/
3047
3048 /*
3049 ** Notes on Delete(ion) of a co-product.
3050 ** 1. WE DONOT ALLOW A PRIMARY CO-PRODUCT TO BE DELETED.
3051 ** 2. WE ALLOW SECONDARY CO-PRODUCTS DELETTION.
3052 ** 3. WE ALLOW THE WHOLE CO-PRODUCT DEFINTION TO BE DELETED.
3053 **
3054 ** Changes as per above;
3055 ** 1. No changes
3056 ** 2. We should delete the bom_header for the secondary co-product
3057 ** when we delete it from the co-product definition because in BOM
3058 ** Header level, there is nothing like a disable date. However, we
3059 ** should NOT UPDATE the BOM_INVENTORY_COMPONENTS as it belongs to
3060 ** the primary co-product bill.
3061 ** 3. When we delete the whole co-product definition, then we should
3062 ** delete all the co-product definition as well all the bom headers
3063 ** corresponding to the co-products and all the bom_inventory_components
3064 ** corresponding to the primary co-product' bill.
3065 ** - Bala BALAKUMAR, June 23rd, 2000.
3066 */
3067
3068 -- Commenting the following code out. Please refer to bug 2816426
3069
3070 /* ***************************************************************************
3071
3072 WSMPPCPD.lock_bill( x_bill_sequence_id => x_bill_sequence_id
3073 , x_error_code => x_error_code
3074 , x_error_msg => x_error_msg);
3075
3076 IF (x_error_code > 0) THEN
3077 return;
3078 ELSIF (x_error_code < 0) THEN
3079 raise e_proc_exception;
3080 END IF;
3081
3082 -- Now go ahead and delete the BOM Header.
3083
3084 x_progress := '031';
3085
3086 delete bom_bill_of_materials
3087 where bill_sequence_id = x_bill_sequence_id;
3088
3089
3090 ** If the coproduct is a primary co-product (as in deletion
3091 ** of the complete co-product group definition), then
3092 ** we need to delete the bom_inventory_components also.
3093
3094
3095 If NVL(x_primary_flag, 'N') = 'Y' Then
3096
3097 SELECT bcp.component_sequence_id
3098 INTO x_component_sequence_id
3099 FROM wsm_co_products bcp
3100 WHERE bcp.co_product_group_id = x_co_product_group_id
3101 AND bcp.co_product_id is not NULL
3102 And NVL(bcp.primary_flag, 'N') = 'Y';
3103
3104 x_progress := '032';
3105
3106 WSMPPCPD.lock_component(x_component_sequence_id,
3107 x_error_code,
3108 x_error_msg);
3109
3110 IF (x_error_code > 0) THEN
3111 return;
3112 ELSIF (x_error_code < 0) THEN
3113 raise e_proc_exception;
3114 END IF;
3115
3116 x_progress := '033';
3117
3118 delete bom_inventory_components
3119 Where bill_sequence_id = x_bill_sequence_id
3120 And component_sequence_id = x_component_sequence_id;
3121
3122
3123 ** Optionally, the component might have substitute
3124 ** components and reference designators. Hence, we put
3125 ** the following code in anonymous block so that
3126 ** when NO_DATA_FOUND exception is thrown, we don't
3127 ** have to do anything with it for this particular
3128 ** delete alone.
3129
3130
3131 BEGIN
3132 x_progress := '034';
3133
3134 delete bom_substitute_components
3135 Where component_sequence_id = x_component_sequence_id;
3136
3137 EXCEPTION
3138 WHEN NO_DATA_FOUND THEN
3139 Null;
3140 END;
3141
3142 BEGIN
3143 x_progress := '035';
3144 delete bom_reference_designators
3145 Where component_sequence_id = x_component_sequence_id;
3146
3147 EXCEPTION
3148 WHEN NO_DATA_FOUND THEN
3149 Null;
3150 END;
3151
3152 End If;
3153 -- End of code introduced by Bala Balakumar, June 23rd, 2000.
3154
3155 x_progress := '036';
3156
3157 WSMPCPSB.delete_substitutes (x_co_product_group_id,
3158 x_co_product_id);
3159
3160
3161 ************************************************************************ */
3162
3163 -- End commenting out code for bug 2816426
3164
3165 x_progress := '040';
3166
3167 WSMPCPDS.delete_row (x_rowid);
3168
3169 x_error_code := 0;
3170
3171 EXCEPTION
3172 WHEN e_delete_co_product THEN
3173 x_error_code := 1;
3174 -- x_error_msg := 'Insufficient arguments to WSMPPCPD.delete_co_product';
3175 fnd_message.set_name('WSM', 'WSM_INSUFFICIENT_ARGUMENTS');
3176 fnd_message.set_token('OBJECT_NAME', 'WSMPPCPD.delete_co_product');
3177 x_error_msg := fnd_message.get;
3178
3179 WHEN e_proc_exception THEN
3180 x_error_msg := x_error_msg || ' - ' || 'WSMPPCPD.delete_co_product('||x_progress||')';
3181
3182 WHEN OTHERS THEN
3183 x_error_code := sqlcode;
3184 x_error_msg := 'WSMPPCPD.delete_co_product(' || x_progress || ')' || ' - ' || substr(sqlerrm, 1, 200);
3185 END delete_co_product;
3186
3187
3188 /* ===========================================================================
3189
3190 PROCEDURE NAME: update_co_prod_details
3191
3192 =========================================================================== */
3193
3194 PROCEDURE update_co_prod_details(x_co_product_group_id IN NUMBER,
3195 x_effectivity_date IN DATE,
3196 x_disable_date IN DATE,
3197 x_usage_rate IN NUMBER,
3198 x_inv_usage IN NUMBER,
3199 x_duality_flag IN VARCHAR2,
3200 x_basis_type IN NUMBER, --LBM enh
3201 x_comp_attribute_category IN VARCHAR2,
3202 x_comp_attribute1 IN VARCHAR2,
3203 x_comp_attribute2 IN VARCHAR2,
3204 x_comp_attribute3 IN VARCHAR2,
3205 x_comp_attribute4 IN VARCHAR2,
3206 x_comp_attribute5 IN VARCHAR2,
3207 x_comp_attribute6 IN VARCHAR2,
3208 x_comp_attribute7 IN VARCHAR2,
3209 x_comp_attribute8 IN VARCHAR2,
3210 x_comp_attribute9 IN VARCHAR2,
3211 x_comp_attribute10 IN VARCHAR2,
3212 x_comp_attribute11 IN VARCHAR2,
3213 x_comp_attribute12 IN VARCHAR2,
3214 x_comp_attribute13 IN VARCHAR2,
3215 x_comp_attribute14 IN VARCHAR2,
3216 x_comp_attribute15 IN VARCHAR2,
3217 x_error_code IN OUT NOCOPY NUMBER,
3218 x_error_msg IN OUT NOCOPY VARCHAR2)
3219 IS
3220
3221 x_progress VARCHAR2(3) := NULL;
3222 e_proc_exception EXCEPTION;
3223 e_update_co_prod_details EXCEPTION;
3224 x_alternate VARCHAR2(10) := NULL;
3225 x_bill_sequence_id NUMBER := NULL;
3226 CURSOR S IS
3227 SELECT bcp.co_product_id,
3228 bcp.component_sequence_id,
3229 bcp.split
3230 FROM wsm_co_products bcp
3231 WHERE bcp.co_product_group_id = x_co_product_group_id
3232 AND bcp.co_product_id is NOT NULL;
3233
3234 BEGIN
3235
3236 x_progress := '010';
3237
3238 IF (x_co_product_group_id IS NULL) THEN
3239 raise e_update_co_prod_details;
3240 END IF;
3241
3242 --
3243 -- Update the corresponding bill.
3244 --
3245
3246 FOR S_rec IN S LOOP
3247
3248 x_progress := '020';
3249
3250 WSMPPCPD.process_co_product (x_process_code => 2,
3251 x_bill_sequence_id => x_bill_sequence_id,
3252 x_component_sequence_id => S_rec.component_sequence_id,
3253 x_alternate_designator => x_alternate,
3254 x_usage => x_usage_rate,
3255 x_split => S_rec.split,
3256 x_effectivity_date => x_effectivity_date,
3257 x_disable_date => x_disable_date,
3258 x_duality_flag => x_duality_flag,
3259 x_basis_type => x_basis_type, --LBM enh
3260 x_comp_attribute_category => x_comp_attribute_category,
3261 x_comp_attribute1 => x_comp_attribute1,
3262 x_comp_attribute2 => x_comp_attribute2,
3263 x_comp_attribute3 => x_comp_attribute3,
3264 x_comp_attribute4 => x_comp_attribute4,
3265 x_comp_attribute5 => x_comp_attribute5,
3266 x_comp_attribute6 => x_comp_attribute6,
3267 x_comp_attribute7 => x_comp_attribute7,
3268 x_comp_attribute8 => x_comp_attribute8,
3269 x_comp_attribute9 => x_comp_attribute9,
3270 x_comp_attribute10 => x_comp_attribute10,
3271 x_comp_attribute11 => x_comp_attribute11,
3272 x_comp_attribute12 => x_comp_attribute12,
3273 x_comp_attribute13 => x_comp_attribute13,
3274 x_comp_attribute14 => x_comp_attribute14,
3275 x_comp_attribute15 => x_comp_attribute15,
3276 x_error_code => x_error_code,
3277 x_error_msg => x_error_msg);
3278 IF (x_error_code < 0) THEN
3279 raise e_proc_exception;
3280 ELSIF (x_error_code <> 0) THEN
3281 return;
3282 END IF;
3283 END LOOP;
3284
3285 --
3286 -- On successful update of the bill update the
3287 -- co-products.
3288 --
3289 x_progress := '030';
3290
3291 UPDATE wsm_co_products
3292 SET effectivity_date = x_effectivity_date,
3293 disable_date = x_disable_date,
3294 usage_rate = x_usage_rate,
3295 duality_flag = x_duality_flag
3296 WHERE co_product_group_id = x_co_product_group_id;
3297
3298 x_error_code := 0;
3299
3300 EXCEPTION
3301 WHEN e_update_co_prod_details THEN
3302 x_error_code := 1;
3303 -- x_error_msg := 'Insufficient arguments to WSMPPCPD.update_co_prod_details';
3304 fnd_message.set_name('WSM', 'WSM_INSUFFICIENT_ARGUMENTS');
3305 fnd_message.set_token('OBJECT_NAME', 'WSMPPCPD.update_co_prod_details');
3306 x_error_msg := fnd_message.get;
3307
3308 WHEN e_proc_exception THEN
3309 x_error_msg := x_error_msg || ' - ' || 'WSMPPCPD.update_co_prod_details('||x_progress||')';
3310
3311 WHEN OTHERS THEN
3312 x_error_code := sqlcode;
3313 x_error_msg := 'WSMPPCPD.update_co_prod_details(' || x_progress || ')' || ' - ' || substr(sqlerrm, 1, 200);
3314 END update_co_prod_details;
3315
3316
3317 /*===========================================================================
3318
3319 FUNCTION NAME: lock_bill
3320
3321 ===========================================================================*/
3322
3323 PROCEDURE lock_bill (x_bill_sequence_id IN NUMBER,
3324 x_error_code IN OUT NOCOPY NUMBER,
3325 x_error_msg IN OUT NOCOPY VARCHAR2)
3326 IS
3327
3328 x_progress VARCHAR2(3) := '010';
3329
3330 CURSOR C IS SELECT *
3331 FROM bom_bill_of_materials
3332 WHERE bill_sequence_id = x_bill_sequence_id
3333 FOR UPDATE OF bill_sequence_id NOWAIT;
3334
3335 BEGIN
3336
3337 OPEN C;
3338 CLOSE C;
3339
3340 x_error_code := 0;
3341
3342 EXCEPTION
3343 WHEN app_exceptions.record_lock_exception THEN
3344 x_error_code := 1;
3345 fnd_message.set_name('WSM','WSM_ASSY_LOCK_ERR');
3346 x_error_msg := fnd_message.get;
3347
3348 WHEN OTHERS THEN
3349 x_error_code := sqlcode;
3350 x_error_msg := 'WSMPPCPD.lock_bill(' || x_progress || ')' || ' - ' || substr(sqlerrm, 1, 200);
3351
3352 END lock_bill;
3353
3354 /*===========================================================================
3355
3356 FUNCTION NAME: lock_component
3357
3358 ===========================================================================*/
3359
3360 PROCEDURE lock_component (x_component_sequence_id IN NUMBER,
3361 x_error_code IN OUT NOCOPY NUMBER,
3362 x_error_msg IN OUT NOCOPY VARCHAR2)
3363 IS
3364
3365 x_progress VARCHAR2(3) := '010';
3366
3367 CURSOR C IS SELECT *
3368 FROM bom_inventory_components
3369 WHERE bill_sequence_id = x_component_sequence_id
3370 FOR UPDATE OF component_sequence_id NOWAIT;
3371
3372 BEGIN
3373
3374 OPEN C;
3375 CLOSE C;
3376
3377 x_error_code := 0;
3378
3379 EXCEPTION
3380 WHEN app_exceptions.record_lock_exception THEN
3381 x_error_code := 1;
3382 fnd_message.set_name('WSM','WSM_COMP_LOCK_ERR');
3383 x_error_msg := fnd_message.get;
3384
3385 WHEN OTHERS THEN
3386 x_error_code := sqlcode;
3387 x_error_msg := 'WSMPPCPD.lock_component(' || x_progress || ')' || ' - ' || substr(sqlerrm, 1, 200);
3388
3389 END lock_component;
3390
3391 /*===========================================================================
3392
3393 PROCEDURE NAME: call_bom_bo_api
3394
3395 ===========================================================================*/
3396
3397 PROCEDURE call_bom_bo_api (
3398 p_bom_header_rec IN Bom_Bo_Pub.Bom_Head_Rec_Type :=
3399 Bom_Bo_Pub.G_MISS_BOM_HEADER_REC,
3400 p_component_tbl IN Bom_Bo_Pub.Bom_Comps_Tbl_Type :=
3401 Bom_Bo_Pub.G_MISS_BOM_COMPONENT_TBL,
3402 p_subs_comp_tbl IN Bom_Bo_Pub.Bom_Sub_Component_Tbl_Type :=
3403 Bom_Bo_Pub.G_MISS_BOM_SUB_COMPONENT_TBL,
3404 x_error_code IN OUT NOCOPY NUMBER,
3405 x_error_msg IN OUT NOCOPY VARCHAR2)
3406 IS
3407
3408 --define local variables
3409 l_bom_header_rec Bom_Bo_Pub.bom_Head_Rec_Type;
3410 l_bom_revision_tbl Bom_Bo_Pub.Bom_Revision_Tbl_Type;
3411 l_bom_component_tbl Bom_Bo_pub.Bom_Comps_Tbl_Type;
3412 l_bom_ref_designator_tbl Bom_Bo_Pub.Bom_Ref_Designator_Tbl_Type;
3413 l_bom_sub_component_tbl Bom_Bo_Pub.Bom_Sub_Component_Tbl_Type;
3414 l_return_status VARCHAR2(1);
3415 l_msg_count NUMBER;
3416 l_mesg_token_tbl Error_Handler.Mesg_Token_Tbl_Type;
3417
3418 e_null_param_exception EXCEPTION;
3419
3420 -- ST Bug fix 5081436
3421 l_full_path v$parameter.value%TYPE;
3422 l_new_full_path v$parameter.value%TYPE;
3423 l_file_dir v$parameter.value%TYPE;
3424 l_debug_flag VARCHAR2(1);
3425 l_mrp_debug VARCHAR2(1);
3426 fileHandler UTL_FILE.FILE_TYPE;
3427 -- ST Bug fix 5081436
3428
3429 BEGIN
3430
3431 --g_iteration_count := g_iteration_count + 1; -- for debug only
3432
3433 /*
3434 ** Introduced by Bala Balakumar to check for Assy Item Id
3435 ** and Org Id if null or not.
3436 **
3437 */
3438
3439 If p_bom_header_rec.assembly_item_name is NULL
3440 OR p_bom_header_rec.Organization_Code is NULL Then
3441
3442 raise e_null_param_exception;
3443 End If;
3444
3445 -- End of Debugging Script by Bala on June 21, 2000.
3446
3447 -- ST : Bug fix 5081436 start
3448 l_mrp_debug := fnd_profile.value('mrp_debug');
3449 l_debug_flag := 'N';
3450
3451 IF l_mrp_debug = 'Y' THEN
3452 -- Pass on a directory from utl_file_dir
3453 SELECT value
3454 INTO l_full_path
3455 FROM v$parameter
3456 WHERE name = 'utl_file_dir';
3457
3458 -- l_full_path contains a list of comma-separated directories
3459 WHILE(TRUE)
3460 LOOP
3461 -- get the first dir in the list
3462 SELECT trim(substr(l_full_path, 1, decode(instr(l_full_path,',')-1,
3463 -1, length(l_full_path),
3464 instr(l_full_path, ',')-1
3465 )
3466 )
3467 )
3468 INTO l_file_dir
3469 FROM dual;
3470
3471 -- check if the dir is valid
3472 BEGIN
3473 fileHandler := UTL_FILE.FOPEN(l_file_dir , 'wsmdbg.log', 'w');
3474 l_debug_flag := 'Y';
3475 EXCEPTION
3476 WHEN utl_file.invalid_path THEN
3477 l_debug_flag := 'N';
3478
3479 WHEN utl_file.invalid_operation THEN
3480 l_debug_flag := 'N';
3481 END;
3482
3483 IF l_debug_flag = 'Y' THEN -- got a valid directory
3484 EXIT;
3485 END IF;
3486
3487 -- earlier found dir was not a valid dir
3488 -- so remove that from the list, and get the new list */
3489 l_new_full_path := trim(substr(l_full_path, instr(l_full_path, ',')+1, length(l_full_path)));
3490
3491 -- if the new list has not changed, there are no more valid dirs left
3492 IF l_full_path = l_new_full_path THEN
3493 l_debug_flag := 'N';
3494 EXIT;
3495 END IF;
3496 l_full_path := l_new_full_path;
3497 END LOOP;
3498 END IF;
3499 -- ST : Bug fix 5081436 end
3500
3501 Bom_Bo_Pub.Process_Bom (
3502 p_init_msg_list => TRUE
3503 , p_bom_header_rec => p_bom_header_rec
3504 , p_bom_component_tbl => p_component_tbl
3505 , p_bom_sub_component_tbl => p_subs_comp_tbl
3506 , x_bom_header_rec => l_bom_header_rec
3507 , x_bom_revision_tbl => l_bom_revision_tbl
3508 , x_bom_component_tbl => l_bom_component_tbl
3509 , x_bom_ref_designator_tbl => l_bom_ref_designator_tbl
3510 , x_bom_sub_component_tbl => l_bom_sub_component_tbl
3511 , x_return_status => l_return_status
3512 , x_msg_count => l_msg_count
3513 , p_debug => l_debug_flag -- ST : Commenting for bug fix 'N'
3514 , p_output_dir => l_file_dir -- ST : Commenting for bug fix '/tmp'
3515 , p_debug_filename => 'wsmdbg.log' -- changed to wsmdbg.log from wsm.log to workaround GSCC error.
3516 );
3517
3518 IF l_return_status <> 'S' THEN
3519 x_error_code := -999;
3520 ELSE
3521 x_error_code := 0;
3522 END IF;
3523
3524 EXCEPTION
3525
3526 WHEN e_null_param_exception THEN
3527 x_error_code := -900;
3528 -- x_error_msg := 'Assembly Item Name or Org Code is NULL in header_rec';
3529 fnd_message.set_name('WSM', 'WSM_INSUFFICIENT_ARGUMENTS');
3530 fnd_message.set_token('OBJECT_NAME', 'WSMPPCPD.call_bom_bo_api');
3531 x_error_msg := fnd_message.get;
3532
3533 WHEN OTHERS THEN
3534 x_error_code := sqlcode;
3535 x_error_msg := 'WSMPPCPD.call_bom_bo_api - ' || substr(sqlerrm, 1, 200);
3536 END call_bom_bo_api;
3537
3538 /*===========================================================================
3539
3540 PROCEDURE NAME: set_common_bill_new
3541
3542 ===========================================================================*/
3543
3544 PROCEDURE set_common_bill_new (
3545 p_co_product_group_id IN NUMBER,
3546 p_organization_id IN NUMBER,
3547 p_organization_code IN VARCHAR2,
3548 p_alternate_designator IN VARCHAR2,
3549 x_error_code OUT NOCOPY NUMBER,
3550 x_error_msg OUT NOCOPY VARCHAR2 )
3551 IS
3552
3553 l_prim_co_prod_id NUMBER;
3554 l_prim_co_prod_name VARCHAR2(81); /* 81 as defined in BOM BO API */
3555 l_co_product_name VARCHAR2(81); /* 81 as defined in BOM BO API */
3556
3557 x_progress VARCHAR2(3) := NULL;
3558 e_primary_coprod_exception EXCEPTION;
3559
3560 /*
3561 ** This cursor ensures that the co-products being
3562 ** updated for the common bill sequence Id do exist
3563 ** in BOM as well and hence it is combined with the
3564 ** bom_bill_of_materials table.
3565 ** - Bala Balakumar, June 23rd, 2000.
3566 */
3567
3568 CURSOR C is
3569 SELECT co_product_id, wcp.alternate_designator
3570 FROM bom_bill_of_materials bbom,
3571 wsm_co_products wcp
3572 WHERE wcp.co_product_group_id = p_co_product_group_id
3573 AND wcp.co_product_id IS NOT NULL
3574 AND NVL(wcp.primary_flag, 'N') <> 'Y'
3575 AND bbom.assembly_item_id = wcp.co_product_id
3576 AND bbom.organization_id = p_organization_id
3577 /*coprod enh p2 .45*/
3578 --AND nvl(bbom.alternate_bom_designator, '$%&') = nvl(p_alternate_designator, '$%&')
3579 AND nvl(bbom.alternate_bom_designator, '$%&') = nvl(wcp.alternate_designator, '$%&')
3580 AND nvl(wcp.alternate_designator, '$%&') = (select nvl(wcp1.alternate_designator, '$%&')
3581 from wsm_co_products wcp1
3582 where wcp1.co_product_group_id=p_co_product_group_id
3583 and wcp1.primary_flag='Y')
3584 /*end coprod enh p2 .45*/
3585 AND bbom.common_bill_sequence_id = wcp.bill_sequence_id;
3586
3587 --commented out by Bala on June 24th, 2000.
3588 --AND bbom.bill_sequence_id = wcp.bill_sequence_id;
3589 -- Above line commented out and uncommented the check with common_bill_seq_id
3590 -- since if the common bill has been already set, the common_bill_seq_id and
3591 -- the bill_seq_id will not be the same and we want only those records which
3592 -- match the condition common_bill_seq_id = bill_seq_id - Raghu.
3593
3594 BEGIN
3595
3596 /* This procedure uses the BOM BO API to update the Bill headers
3597 of the Secondary Co-Products to set the Common Bill Reference. */
3598
3599 x_progress := '010';
3600
3601 -- get the primary co-product details
3602
3603 Begin
3604
3605 SELECT co_product_id
3606 INTO l_prim_co_prod_id
3607 FROM wsm_co_products
3608 WHERE co_product_group_id = p_co_product_group_id
3609 AND co_product_id IS NOT NULL
3610 AND nvl(primary_flag, 'N') = 'Y';
3611
3612
3613 EXCEPTION
3614 When TOO_MANY_ROWS Then
3615 fnd_message.set_name('WSM', 'WSM_NO_PRIMARY_COPRODUCT');
3616 x_error_code := sqlcode;
3617 /* Bug# 1790690. Added the following statement so that
3618 it raises the exception where there are more than
3619 one primary flag is checked */
3620 raise e_primary_coprod_exception;
3621
3622 When NO_DATA_FOUND Then
3623 fnd_message.set_name('WSM', 'WSM_NO_PRIMARY_COPRODUCT');
3624 x_error_code := sqlcode;
3625 /* Bug# 1790690. Added the following statement so that
3626 it raises the exception when the primary flag
3627 is not checked at all*/
3628 raise e_primary_coprod_exception;
3629 End;
3630
3631 x_progress := '020';
3632
3633 -- get the primary co_product_name
3634 If l_prim_co_prod_id is NOT NULL AND
3635 p_organization_id is NOT NULL Then
3636
3637 SELECT substr(concatenated_segments, 1, 80)
3638 INTO l_prim_co_prod_name
3639 FROM mtl_system_items_kfv
3640 WHERE inventory_item_id = l_prim_co_prod_id
3641 AND organization_id = p_organization_id;
3642
3643 Else
3644 fnd_message.set_name('WSM', 'WSM_NO_PRIMARY_COPRODUCT');
3645 raise e_primary_coprod_exception;
3646 End If;
3647
3648 x_progress := '030';
3649
3650 FOR c_rec in C LOOP
3651
3652 -- get the secondary co-product name
3653 SELECT substr(concatenated_segments, 1, 80)
3654 INTO l_co_product_name
3655 FROM mtl_system_items_kfv
3656 WHERE inventory_item_id = c_rec.co_product_id
3657 AND organization_id = p_organization_id;
3658
3659 x_progress := '035';
3660 -- prepare the bill header for the business object
3661
3662 g_bom_header_rec := Bom_Bo_Pub.G_MISS_BOM_HEADER_REC; /* initialize */
3663 -- now populate the header record before calling the API
3664 g_bom_header_rec.Transaction_Type := BOM_Globals.G_OPR_UPDATE;
3665 g_bom_header_rec.Assembly_Item_Name := l_co_product_name;
3666 g_bom_header_rec.Organization_Code := p_organization_code;
3667 /*coproduct enh p2 .45*/
3668 -- g_bom_header_rec.Alternate_Bom_Code := p_alternate_designator;
3669 g_bom_header_rec.Alternate_Bom_Code := c_rec.alternate_designator;
3670 /*end coproduct enh p2 .45*/
3671 g_bom_header_rec.Common_Assembly_Item_Name := l_prim_co_prod_name;
3672 g_bom_header_rec.Common_Organization_Code := p_organization_code;
3673 -- Added next line for resolution of bug 2682690
3674 g_bom_header_rec.Assembly_Type := 1;
3675
3676 -- now go ahead and call the BOM Business Object API
3677 -- to update this bill
3678
3679 WSMPPCPD.call_bom_bo_api (
3680 p_bom_header_rec => g_bom_header_rec,
3681 x_error_code => x_error_code,
3682 x_error_msg => x_error_msg );
3683
3684 IF x_error_code <> 0 THEN
3685 return;
3686 END IF;
3687
3688 END LOOP;
3689
3690 EXCEPTION
3691
3692 WHEN e_primary_coprod_exception Then
3693 x_error_code := -900;
3694 x_error_msg := fnd_message.get;
3695
3696 WHEN OTHERS THEN
3697 x_error_code := sqlcode;
3698 x_error_msg := 'WSMPPCPD.set_common_bill_new('||x_progress ||') ' || substr(sqlerrm, 1, 200);
3699
3700 END set_common_bill_new;
3701
3702 END WSMPPCPD;