[Home] [Help]
PACKAGE BODY: APPS.WSMPCPDS
Source
1 PACKAGE BODY WSMPCPDS as
2 /* $Header: WSMCPRDB.pls 120.2 2005/09/09 07:09:56 abgangul noship $ */
3
4 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
5 X_co_product_group_id IN OUT NOCOPY NUMBER,
6 X_component_id NUMBER,
7 X_organization_id NUMBER,
8 X_Last_Update_Login NUMBER,
9 X_Last_Updated_By NUMBER,
10 X_Last_Update_Date DATE,
11 X_Creation_Date DATE,
12 X_Created_By NUMBER,
13 X_co_product_id NUMBER,
14 /*coprod enh p2 .45*/
15 X_alternate_designator VARCHAR2,
16 /*end coprod enh p2 .45*/
17 X_bill_sequence_id NUMBER,
18 X_component_sequence_id NUMBER,
19 X_split NUMBER,
20 X_effectivity_date DATE,
21 X_disable_date DATE,
22 X_primary_flag VARCHAR2,
23 X_revision VARCHAR2,
24 X_change_notice VARCHAR2,
25 X_implementation_date DATE,
26 X_usage_rate NUMBER,
27 X_duality_flag VARCHAR2,
28 X_planning_factor NUMBER,
29 X_component_yield_factor NUMBER,
30 X_include_in_cost_rollup NUMBER,
31 X_wip_supply_type NUMBER,
32 X_supply_subinventory VARCHAR2,
33 X_supply_locator_id NUMBER,
34 X_component_remarks VARCHAR2,
35 X_attribute_category VARCHAR2,
36 X_Attribute1 VARCHAR2,
37 X_Attribute2 VARCHAR2,
38 X_Attribute3 VARCHAR2,
39 X_Attribute4 VARCHAR2,
40 X_Attribute5 VARCHAR2,
41 X_Attribute6 VARCHAR2,
42 X_Attribute7 VARCHAR2,
43 X_Attribute8 VARCHAR2,
44 X_Attribute9 VARCHAR2,
45 X_Attribute10 VARCHAR2,
46 X_Attribute11 VARCHAR2,
47 X_Attribute12 VARCHAR2,
48 X_Attribute13 VARCHAR2,
49 X_Attribute14 VARCHAR2,
50 X_Attribute15 VARCHAR2,
51 X_basis_type NUMBER --LBM enh
52 ) IS
53 CURSOR C IS SELECT rowid FROM WSM_CO_PRODUCTS
54 WHERE component_id = X_component_id
55 AND organization_id = X_organization_id
56 AND co_product_group_id = X_co_product_group_id
57 AND ( co_product_id = x_co_product_id
58 OR ((co_product_id is NULL) AND
59 (x_co_product_id is NULL)));
60
61 --commented out by abedajna for perf. tuning
62 /* CURSOR S IS SELECT WSM_co_products_s.nextval FROM sys.dual;
63 **
64 ** BEGIN
65 ** if (X_co_product_group_id is NULL) then
66 ** OPEN S;
67 ** FETCH S INTO X_co_product_group_id;
68 ** CLOSE S;
69 ** end if;
70 */
71 l_basis_type number; --LBM enh
72 BEGIN
73
74 if X_basis_type = 2 then --LBM enh
75 l_basis_type := 2;
76 else
77 l_basis_type := null;
78 end if; --LBM enh
79
80 IF (x_co_product_id IS NOT NULL) THEN
81
82 INSERT INTO WSM_CO_PRODUCTS (
83 CO_PRODUCT_GROUP_ID,
84 COMPONENT_ID,
85 ORGANIZATION_ID,
86 CREATION_DATE,
87 CREATED_BY,
88 LAST_UPDATE_LOGIN,
89 LAST_UPDATED_BY,
90 LAST_UPDATE_DATE,
91 CO_PRODUCT_ID,
92 ALTERNATE_DESIGNATOR, --coprod enh p2
93 BILL_SEQUENCE_ID,
94 COMPONENT_SEQUENCE_ID,
95 SPLIT,
96 EFFECTIVITY_DATE,
97 DISABLE_DATE,
98 PRIMARY_FLAG,
99 REVISION,
100 CHANGE_NOTICE,
101 IMPLEMENTATION_DATE,
102 USAGE_RATE,
103 DUALITY_FLAG,
104 ATTRIBUTE_CATEGORY,
105 ATTRIBUTE1,
106 ATTRIBUTE2,
107 ATTRIBUTE3,
108 ATTRIBUTE4,
109 ATTRIBUTE5,
110 ATTRIBUTE6,
111 ATTRIBUTE7,
112 ATTRIBUTE8,
113 ATTRIBUTE9,
114 ATTRIBUTE10,
115 ATTRIBUTE11,
116 ATTRIBUTE12,
117 ATTRIBUTE13,
118 ATTRIBUTE14,
119 ATTRIBUTE15,
120 BASIS_TYPE ---LBM enh
121 ) VALUES (
122 -- abedajna X_co_product_group_id,
123 decode(X_co_product_group_id, NULL, WSM_co_products_s.nextval, X_co_product_group_id),
124 X_component_id,
125 X_organization_id,
126 X_creation_date,
127 X_created_by,
128 X_last_update_login,
129 X_last_updated_by,
130 X_last_update_date,
131 X_co_product_id,
132 X_alternate_designator, --coprod enh p2
133 X_bill_sequence_id,
134 X_component_sequence_id,
135 X_split,
136 X_effectivity_date,
137 X_disable_date,
138 X_primary_flag,
139 X_revision,
140 X_change_notice,
141 X_implementation_date,
142 X_usage_rate,
143 X_duality_flag,
144 X_attribute_category,
145 X_attribute1,
146 X_attribute2,
147 X_attribute3,
148 X_attribute4,
149 X_attribute5,
150 X_attribute6,
151 X_attribute7,
152 X_attribute8,
153 X_attribute9,
154 X_attribute10,
155 X_attribute11,
156 X_attribute12,
157 X_attribute13,
158 X_attribute14,
159 X_attribute15,
160 l_basis_type ---LBM enh
161 )
162 returning CO_PRODUCT_GROUP_ID into X_co_product_group_id;
163
164 ELSE
165
166 INSERT INTO WSM_CO_PRODUCTS (
167 CO_PRODUCT_GROUP_ID,
168 COMPONENT_ID,
169 ORGANIZATION_ID,
170 CREATION_DATE,
171 CREATED_BY,
172 LAST_UPDATE_LOGIN,
173 LAST_UPDATED_BY,
174 LAST_UPDATE_DATE,
175 CO_PRODUCT_ID,
176 ALTERNATE_DESIGNATOR, --coprod enh p2
177 BILL_SEQUENCE_ID,
178 COMPONENT_SEQUENCE_ID,
179 SPLIT,
180 EFFECTIVITY_DATE,
181 DISABLE_DATE,
182 PRIMARY_FLAG,
183 REVISION,
184 CHANGE_NOTICE,
185 IMPLEMENTATION_DATE,
186 USAGE_RATE,
187 DUALITY_FLAG,
188 PLANNING_FACTOR,
189 COMPONENT_YIELD_FACTOR,
190 INCLUDE_IN_COST_ROLLUP,
191 WIP_SUPPLY_TYPE,
192 SUPPLY_SUBINVENTORY,
193 SUPPLY_LOCATOR_ID,
194 COMPONENT_REMARKS,
195 COMP_ATTRIBUTE_CATEGORY,
196 COMP_ATTRIBUTE1,
197 COMP_ATTRIBUTE2,
198 COMP_ATTRIBUTE3,
199 COMP_ATTRIBUTE4,
200 COMP_ATTRIBUTE5,
201 COMP_ATTRIBUTE6,
202 COMP_ATTRIBUTE7,
203 COMP_ATTRIBUTE8,
204 COMP_ATTRIBUTE9,
205 COMP_ATTRIBUTE10,
206 COMP_ATTRIBUTE11,
207 COMP_ATTRIBUTE12,
208 COMP_ATTRIBUTE13,
209 COMP_ATTRIBUTE14,
210 COMP_ATTRIBUTE15,
211 BASIS_TYPE --LBM enh
212 ) VALUES (
213 -- abedajna X_co_product_group_id,
214 decode(X_co_product_group_id, NULL, WSM_co_products_s.nextval, X_co_product_group_id),
215 X_component_id,
216 X_organization_id,
217 X_creation_date,
218 X_created_by,
219 X_last_update_login,
220 X_last_updated_by,
221 X_last_update_date,
222 X_co_product_id,
223 X_alternate_designator, --coprod enh p2
224 X_bill_sequence_id,
225 X_component_sequence_id,
226 X_split,
227 X_effectivity_date,
228 X_disable_date,
229 X_primary_flag,
230 X_revision,
231 X_change_notice,
232 X_implementation_date,
233 X_usage_rate,
234 X_duality_flag,
235 X_planning_factor,
236 X_component_yield_factor,
237 X_include_in_cost_rollup,
238 X_wip_supply_type,
239 X_supply_subinventory,
240 X_supply_locator_id,
241 X_component_remarks,
242 X_attribute_category,
243 X_attribute1,
244 X_attribute2,
245 X_attribute3,
246 X_attribute4,
247 X_attribute5,
248 X_attribute6,
249 X_attribute7,
250 X_attribute8,
251 X_attribute9,
252 X_attribute10,
253 X_attribute11,
254 X_attribute12,
255 X_attribute13,
256 X_attribute14,
257 X_attribute15,
258 l_basis_type --LBM enh
259 )
260 returning CO_PRODUCT_GROUP_ID into X_co_product_group_id;
261
262 END IF;
263
264 OPEN C;
265 FETCH C INTO X_Rowid;
266 if (C%NOTFOUND) then
267 CLOSE C;
268 Raise NO_DATA_FOUND;
269 end if;
270 CLOSE C;
271
272 END Insert_Row;
273
274
275 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
276 X_co_product_group_id NUMBER,
277 X_component_id NUMBER,
278 X_organization_id NUMBER,
279 X_co_product_id NUMBER,
280 X_bill_sequence_id NUMBER,
281 X_component_sequence_id NUMBER,
282 X_split NUMBER,
283 X_effectivity_date DATE,
284 X_disable_date DATE,
285 X_primary_flag VARCHAR2,
286 X_revision VARCHAR2,
287 X_change_notice VARCHAR2,
288 X_implementation_date DATE,
289 X_usage_rate NUMBER,
290 X_duality_flag VARCHAR2,
291 X_planning_factor NUMBER,
292 X_component_yield_factor NUMBER,
293 X_include_in_cost_rollup NUMBER,
294 X_wip_supply_type NUMBER,
295 X_supply_subinventory VARCHAR2,
296 X_supply_locator_id NUMBER,
297 X_component_remarks VARCHAR2,
298 X_attribute_category VARCHAR2,
299 X_Attribute1 VARCHAR2,
300 X_Attribute2 VARCHAR2,
301 X_Attribute3 VARCHAR2,
302 X_Attribute4 VARCHAR2,
303 X_Attribute5 VARCHAR2,
304 X_Attribute6 VARCHAR2,
305 X_Attribute7 VARCHAR2,
306 X_Attribute8 VARCHAR2,
307 X_Attribute9 VARCHAR2,
308 X_Attribute10 VARCHAR2,
309 X_Attribute11 VARCHAR2,
310 X_Attribute12 VARCHAR2,
311 X_Attribute13 VARCHAR2,
312 X_Attribute14 VARCHAR2,
313 X_Attribute15 VARCHAR2,
314 X_Basis_type NUMBER ---LBM enh
315 ) IS
316 CURSOR C IS
317 SELECT *
318 FROM WSM_CO_PRODUCTS
319 WHERE rowid = X_Rowid
320 FOR UPDATE of component_id NOWAIT;
321 Recinfo C%ROWTYPE;
322 BEGIN
323 OPEN C;
324 FETCH C INTO Recinfo;
325 if (C%NOTFOUND) then
326 CLOSE C;
327 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
328 APP_EXCEPTION.Raise_Exception;
329 end if;
330 CLOSE C;
331
332 /*
333 This call is used to lock records in WSM_co_products
334 for both components as well as co-products.
335 While modifying a co-product relationship ensure
336 that the component record is locked before attempting
337 to modify the co-product records for that component.
338 */
339 if (X_co_product_id is NOT NULL) THEN
340 if (
341
342 (Recinfo.component_id = X_component_id)
343 AND (Recinfo.organization_id = X_organization_id)
344 AND (Recinfo.co_product_group_id = X_co_product_group_id)
345 AND ( (Recinfo.effectivity_date = X_effectivity_date)
346 OR ( (Recinfo.effectivity_date IS NULL)
347 AND (X_effectivity_date IS NULL))
348 OR (X_co_product_id is NOT NULL))
349 AND ( (Recinfo.co_product_id = X_co_product_id)
350 OR ( (Recinfo.co_product_id IS NULL)
351 AND (X_co_product_id IS NULL)))
352 AND ( (Recinfo.bill_sequence_id = X_bill_sequence_id)
353 OR ( (Recinfo.bill_sequence_id IS NULL)
354 AND (X_bill_sequence_id IS NULL)))
355 AND ( (Recinfo.component_sequence_id = X_component_sequence_id)
356 OR ( (Recinfo.component_sequence_id IS NULL)
357 AND (X_component_sequence_id IS NULL)))
358 AND ( (Recinfo.split = X_split)
359 OR ( (Recinfo.split IS NULL)
360 AND (X_split IS NULL)))
361 AND ( (Recinfo.disable_date = X_disable_date)
362 OR ( (Recinfo.disable_date IS NULL)
363 AND (X_disable_date IS NULL))
364 OR (X_co_product_id is NOT NULL))
365 AND ( (Recinfo.primary_flag = X_primary_flag)
366 OR ( (Recinfo.primary_flag IS NULL)
367 AND (X_primary_flag IS NULL)))
368 AND ( (Recinfo.revision = X_revision)
369 OR ( (Recinfo.revision IS NULL)
370 AND (X_revision IS NULL)))
371 AND ( (Recinfo.change_notice = X_change_notice)
372 OR ( (Recinfo.change_notice IS NULL)
373 AND (X_change_notice IS NULL)))
374 AND ( (Recinfo.implementation_date = X_implementation_date)
375 OR ( (Recinfo.implementation_date IS NULL)
376 AND (X_implementation_date IS NULL)))
377 AND ( (Recinfo.usage_rate = X_usage_rate)
378 OR ( (Recinfo.usage_rate IS NULL)
379 AND (X_usage_rate IS NULL))
380 OR (X_co_product_id is NOT NULL))
381 AND ( (Recinfo.duality_flag = X_duality_flag)
382 OR ( (Recinfo.duality_flag IS NULL)
383 AND (X_duality_flag IS NULL))
384 OR (X_co_product_id is NOT NULL))
385 AND ( (Recinfo.attribute_category = X_attribute_category)
386 OR ( (Recinfo.attribute_category IS NULL)
387 AND (X_attribute_category IS NULL)))
388 AND ( (Recinfo.attribute1 = X_Attribute1)
389 OR ( (Recinfo.attribute1 IS NULL)
390 AND (X_Attribute1 IS NULL)))
391 AND ( (Recinfo.attribute2 = X_Attribute2)
392 OR ( (Recinfo.attribute2 IS NULL)
393 AND (X_Attribute2 IS NULL)))
394 AND ( (Recinfo.attribute3 = X_Attribute3)
395 OR ( (Recinfo.attribute3 IS NULL)
396 AND (X_Attribute3 IS NULL)))
397 AND ( (Recinfo.attribute4 = X_Attribute4)
398 OR ( (Recinfo.attribute4 IS NULL)
399 AND (X_Attribute4 IS NULL)))
400 AND ( (Recinfo.attribute5 = X_Attribute5)
401 OR ( (Recinfo.attribute5 IS NULL)
402 AND (X_Attribute5 IS NULL)))
403 AND ( (Recinfo.attribute6 = X_Attribute6)
404 OR ( (Recinfo.attribute6 IS NULL)
405 AND (X_Attribute6 IS NULL)))
406 AND ( (Recinfo.attribute7 = X_Attribute7)
407 OR ( (Recinfo.attribute7 IS NULL)
408 AND (X_Attribute7 IS NULL)))
409 AND ( (Recinfo.attribute8 = X_Attribute8)
410 OR ( (Recinfo.attribute8 IS NULL)
411 AND (X_Attribute8 IS NULL)))
412 AND ( (Recinfo.attribute9 = X_Attribute9)
413 OR ( (Recinfo.attribute9 IS NULL)
414 AND (X_Attribute9 IS NULL)))
415 AND ( (Recinfo.attribute10 = X_Attribute10)
416 OR ( (Recinfo.attribute10 IS NULL)
417 AND (X_Attribute10 IS NULL)))
418 AND ( (Recinfo.attribute11 = X_Attribute11)
419 OR ( (Recinfo.attribute11 IS NULL)
420 AND (X_Attribute11 IS NULL)))
421 AND ( (Recinfo.attribute12 = X_Attribute12)
422 OR ( (Recinfo.attribute12 IS NULL)
423 AND (X_Attribute12 IS NULL)))
424 AND ( (Recinfo.attribute13 = X_Attribute13)
425 OR ( (Recinfo.attribute13 IS NULL)
426 AND (X_Attribute13 IS NULL)))
427 AND ( (Recinfo.attribute14 = X_Attribute14)
428 OR ( (Recinfo.attribute14 IS NULL)
429 AND (X_Attribute14 IS NULL)))
430 AND ( (Recinfo.attribute15 = X_Attribute15)
431 OR ( (Recinfo.attribute15 IS NULL)
432 AND (X_Attribute15 IS NULL)))
433 AND ( (Recinfo.basis_type = X_basis_type) -- LBM enh
434 OR ( (Recinfo.basis_type IS NULL) -- LBM enh
435 AND (X_basis_type IS NULL))) -- LBM enh
436
437 ) then
438 return;
439 else
440 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
441 APP_EXCEPTION.RAISE_EXCEPTION;
442 end if;
443
444 else
445 if (
446 (Recinfo.component_id = X_component_id)
447 AND (Recinfo.organization_id = X_organization_id)
448 AND (Recinfo.co_product_group_id = X_co_product_group_id)
449 AND ( (Recinfo.effectivity_date = X_effectivity_date)
450 OR ( (Recinfo.effectivity_date IS NULL)
451 AND (X_effectivity_date IS NULL))
452 OR (X_co_product_id is NOT NULL))
453 AND ( (Recinfo.co_product_id = X_co_product_id)
454 OR ( (Recinfo.co_product_id IS NULL)
455 AND (X_co_product_id IS NULL)))
456 AND ( (Recinfo.bill_sequence_id = X_bill_sequence_id)
457 OR ( (Recinfo.bill_sequence_id IS NULL)
458 AND (X_bill_sequence_id IS NULL)))
459 AND ( (Recinfo.component_sequence_id = X_component_sequence_id)
460 OR ( (Recinfo.component_sequence_id IS NULL)
461 AND (X_component_sequence_id IS NULL)))
462 AND ( (Recinfo.split = X_split)
463 OR ( (Recinfo.split IS NULL)
464 AND (X_split IS NULL)))
465 AND ( (Recinfo.disable_date = X_disable_date)
466 OR ( (Recinfo.disable_date IS NULL)
467 AND (X_disable_date IS NULL))
468 OR (X_co_product_id is NOT NULL))
469 AND ( (Recinfo.primary_flag = X_primary_flag)
470 OR ( (Recinfo.primary_flag IS NULL)
471 AND (X_primary_flag IS NULL)))
472 AND ( (Recinfo.revision = X_revision)
473 OR ( (Recinfo.revision IS NULL)
474 AND (X_revision IS NULL)))
475 AND ( (Recinfo.change_notice = X_change_notice)
476 OR ( (Recinfo.change_notice IS NULL)
477 AND (X_change_notice IS NULL)))
478 AND ( (Recinfo.implementation_date = X_implementation_date)
479 OR ( (Recinfo.implementation_date IS NULL)
480 AND (X_implementation_date IS NULL)))
481 AND ( (Recinfo.usage_rate = X_usage_rate)
482 OR ( (Recinfo.usage_rate IS NULL)
483 AND (X_usage_rate IS NULL))
484 OR (X_co_product_id is NOT NULL))
485 AND ( (Recinfo.duality_flag = X_duality_flag)
486 OR ( (Recinfo.duality_flag IS NULL)
487 AND (X_duality_flag IS NULL))
488 OR (X_co_product_id is NOT NULL))
489 AND (Recinfo.component_yield_factor = X_component_yield_factor)
490 AND (Recinfo.include_in_cost_rollup = X_include_in_cost_rollup)
491 AND (Recinfo.planning_factor = X_planning_factor)
492 AND ( (Recinfo.wip_supply_type = X_wip_supply_type)
493 OR ( (Recinfo.wip_supply_type IS NULL)
494 AND (X_wip_supply_type IS NULL)))
495 AND ( (Recinfo.supply_subinventory = X_supply_subinventory)
496 OR ( (Recinfo.supply_subinventory IS NULL)
497 AND (X_supply_subinventory IS NULL)))
498 AND ( (Recinfo.supply_locator_id = X_supply_locator_id)
499 OR ( (Recinfo.supply_locator_id IS NULL)
500 AND (X_supply_locator_id IS NULL)))
501 AND ( (Recinfo.component_remarks = X_component_remarks)
502 OR ( (Recinfo.component_remarks IS NULL)
503 AND (X_component_remarks IS NULL)))
504 AND ( (Recinfo.comp_attribute_category = X_attribute_category)
505 OR ( (Recinfo.comp_attribute_category IS NULL)
506 AND (X_attribute_category IS NULL)))
507 AND ( (Recinfo.comp_attribute1 = X_Attribute1)
508 OR ( (Recinfo.comp_attribute1 IS NULL)
509 AND (X_Attribute1 IS NULL)))
510 AND ( (Recinfo.comp_attribute2 = X_Attribute2)
511 OR ( (Recinfo.comp_attribute2 IS NULL)
512 AND (X_Attribute2 IS NULL)))
513 AND ( (Recinfo.comp_attribute3 = X_Attribute3)
514 OR ( (Recinfo.comp_attribute3 IS NULL)
515 AND (X_Attribute3 IS NULL)))
516 AND ( (Recinfo.comp_attribute4 = X_Attribute4)
517 OR ( (Recinfo.comp_attribute4 IS NULL)
518 AND (X_Attribute4 IS NULL)))
519 AND ( (Recinfo.comp_attribute5 = X_Attribute5)
520 OR ( (Recinfo.comp_attribute5 IS NULL)
521 AND (X_Attribute5 IS NULL)))
522 AND ( (Recinfo.comp_attribute6 = X_Attribute6)
523 OR ( (Recinfo.comp_attribute6 IS NULL)
524 AND (X_Attribute6 IS NULL)))
525 AND ( (Recinfo.comp_attribute7 = X_Attribute7)
526 OR ( (Recinfo.comp_attribute7 IS NULL)
527 AND (X_Attribute7 IS NULL)))
528 AND ( (Recinfo.comp_attribute8 = X_Attribute8)
529 OR ( (Recinfo.comp_attribute8 IS NULL)
530 AND (X_Attribute8 IS NULL)))
531 AND ( (Recinfo.comp_attribute9 = X_Attribute9)
532 OR ( (Recinfo.comp_attribute9 IS NULL)
533 AND (X_Attribute9 IS NULL)))
534 AND ( (Recinfo.comp_attribute10 = X_Attribute10)
535 OR ( (Recinfo.comp_attribute10 IS NULL)
536 AND (X_Attribute10 IS NULL)))
537 AND ( (Recinfo.comp_attribute11 = X_Attribute11)
538 OR ( (Recinfo.comp_attribute11 IS NULL)
539 AND (X_Attribute11 IS NULL)))
540 AND ( (Recinfo.comp_attribute12 = X_Attribute12)
541 OR ( (Recinfo.comp_attribute12 IS NULL)
542 AND (X_Attribute12 IS NULL)))
543 AND ( (Recinfo.comp_attribute13 = X_Attribute13)
544 OR ( (Recinfo.comp_attribute13 IS NULL)
545 AND (X_Attribute13 IS NULL)))
546 AND ( (Recinfo.comp_attribute14 = X_Attribute14)
547 OR ( (Recinfo.comp_attribute14 IS NULL)
548 AND (X_Attribute14 IS NULL)))
549 AND ( (Recinfo.comp_attribute15 = X_Attribute15)
550 OR ( (Recinfo.comp_attribute15 IS NULL)
551 AND (X_Attribute15 IS NULL)))
552 AND ( (Recinfo.basis_type = X_basis_type) -- LBM enh
553 OR ( (Recinfo.basis_type IS NULL) -- LBM enh
554 AND (X_basis_type IS NULL))) -- LBM enh
555
556
557 ) then
558 return;
559 else
560 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
561 APP_EXCEPTION.RAISE_EXCEPTION;
562 end if;
563 end if;
564
565 END Lock_Row;
566
567
568 PROCEDURE Update_Row(X_Rowid VARCHAR2,
569 X_co_product_group_id NUMBER,
570 X_component_id NUMBER,
571 X_organization_id NUMBER,
572 X_Last_Update_Login NUMBER,
573 X_last_update_date DATE,
574 X_last_updated_by NUMBER,
575 X_co_product_id NUMBER,
576 X_bill_sequence_id NUMBER,
577 X_component_sequence_id NUMBER,
578 X_split NUMBER,
579 X_effectivity_date DATE,
580 X_disable_date DATE,
581 X_primary_flag VARCHAR2,
582 X_revision VARCHAR2,
583 X_change_notice VARCHAR2,
584 X_implementation_date DATE,
585 X_usage_rate NUMBER,
586 X_duality_flag VARCHAR2,
587 X_planning_factor NUMBER,
588 X_component_yield_factor NUMBER,
589 X_include_in_cost_rollup NUMBER,
590 X_wip_supply_type NUMBER,
591 X_supply_subinventory VARCHAR2,
592 X_supply_locator_id NUMBER,
593 X_component_remarks VARCHAR2,
594 X_attribute_category VARCHAR2,
595 X_Attribute1 VARCHAR2,
596 X_Attribute2 VARCHAR2,
597 X_Attribute3 VARCHAR2,
598 X_Attribute4 VARCHAR2,
599 X_Attribute5 VARCHAR2,
600 X_Attribute6 VARCHAR2,
601 X_Attribute7 VARCHAR2,
602 X_Attribute8 VARCHAR2,
603 X_Attribute9 VARCHAR2,
604 X_Attribute10 VARCHAR2,
605 X_Attribute11 VARCHAR2,
606 X_Attribute12 VARCHAR2,
607 X_Attribute13 VARCHAR2,
608 X_Attribute14 VARCHAR2,
609 X_Attribute15 VARCHAR2,
610 X_basis_type NUMBER --LBM enh
611 ) IS
612
613 l_basis_type number; --LBM enh
614 BEGIN
615
616 if X_basis_type = 2 then --LBM enh
617 l_basis_type := 2;
618 else
619 l_basis_type := null;
620 end if; --LBM enh
621
622 IF (X_co_product_id is NOT NULL) THEN
623
624 UPDATE WSM_CO_PRODUCTS
625 SET
626 co_product_group_id = X_co_product_group_id,
627 component_id = X_component_id,
628 organization_id = X_organization_id,
629 last_update_login = X_last_update_login,
630 last_update_date = X_last_update_date,
631 last_updated_by = X_last_updated_by,
632 co_product_id = X_co_product_id,
633 bill_sequence_id = X_bill_sequence_id,
634 component_sequence_id = X_component_sequence_id,
635 split = X_split,
636 effectivity_date = X_effectivity_date,
637 disable_date = X_disable_date,
638 primary_flag = X_primary_flag,
639 revision = X_revision,
640 change_notice = X_change_notice,
641 implementation_date = X_implementation_date,
642 usage_rate = X_usage_rate,
643 duality_flag = X_duality_flag,
644 attribute_category = X_attribute_category,
645 attribute1 = X_attribute1,
646 attribute2 = X_attribute2,
647 attribute3 = X_attribute3,
648 attribute4 = X_attribute4,
649 attribute5 = X_attribute5,
650 attribute6 = X_attribute6,
651 attribute7 = X_attribute7,
652 attribute8 = X_attribute8,
653 attribute9 = X_attribute9,
654 attribute10 = X_attribute10,
655 attribute11 = X_attribute11,
656 attribute12 = X_attribute12,
657 attribute13 = X_attribute13,
658 attribute14 = X_attribute14,
659 attribute15 = X_attribute15,
660 basis_type = l_basis_type ---LBM enh
661 WHERE rowid = X_rowid;
662
663 ELSE
664
665 UPDATE WSM_CO_PRODUCTS
666 SET
667 co_product_group_id = X_co_product_group_id,
668 component_id = X_component_id,
669 organization_id = X_organization_id,
670 last_update_login = X_last_update_login,
671 last_update_date = X_last_update_date,
672 last_updated_by = X_last_updated_by,
673 co_product_id = X_co_product_id,
674 bill_sequence_id = X_bill_sequence_id,
675 component_sequence_id = X_component_sequence_id,
676 split = X_split,
677 effectivity_date = X_effectivity_date,
678 disable_date = X_disable_date,
679 primary_flag = X_primary_flag,
680 revision = X_revision,
681 change_notice = X_change_notice,
682 implementation_date = X_implementation_date,
683 usage_rate = X_usage_rate,
684 duality_flag = X_duality_flag,
685 planning_factor = X_planning_factor,
686 component_yield_factor = X_component_yield_factor,
687 include_in_cost_rollup = X_include_in_cost_rollup,
688 wip_supply_type = X_wip_supply_type,
689 supply_subinventory = X_supply_subinventory,
690 supply_locator_id = X_supply_locator_id,
691 component_remarks = X_component_remarks,
692 comp_attribute_category = X_attribute_category,
693 comp_attribute1 = X_attribute1,
694 comp_attribute2 = X_attribute2,
695 comp_attribute3 = X_attribute3,
696 comp_attribute4 = X_attribute4,
697 comp_attribute5 = X_attribute5,
698 comp_attribute6 = X_attribute6,
699 comp_attribute7 = X_attribute7,
700 comp_attribute8 = X_attribute8,
701 comp_attribute9 = X_attribute9,
702 comp_attribute10 = X_attribute10,
703 comp_attribute11 = X_attribute11,
704 comp_attribute12 = X_attribute12,
705 comp_attribute13 = X_attribute13,
706 comp_attribute14 = X_attribute14,
707 comp_attribute15 = X_attribute15,
708 basis_type = l_basis_type ---LBM enh
709 WHERE rowid = X_rowid;
710
711 END IF;
712
713 if (SQL%NOTFOUND) then
714 Raise NO_DATA_FOUND;
715 end if;
716
717 END Update_Row;
718
719
720
721 PROCEDURE Check_Unique(X_Rowid VARCHAR2,
722 X_component_id NUMBER,
723 X_organization_id NUMBER) IS
724
725 x1_dummy NUMBER; --abedajna
726 dummy NUMBER;
727 x_component VARCHAR2(820);
728
729 co_pdt_reltn_exst_error EXCEPTION; --abedajna
730
731 BEGIN
732
733
734 -- commented out by abedajna on 10/12/00 for perf. tuning
735
736 /* SELECT 1 INTO dummy
737 ** FROM DUAL
738 ** WHERE NOT EXISTS
739 ** ( SELECT 1
740 ** FROM WSM_co_products
741 ** WHERE component_id = X_component_id
742 ** AND organization_id = X_organization_id
743 ** AND ((X_Rowid IS NULL) OR (ROWID <> X_ROWID)));
744 **
745 ** EXCEPTION
746 ** WHEN NO_DATA_FOUND THEN
747 */
748 /* DEBUG - New Message */
749 /*
750 ** fnd_message.set_name ('BOM', 'CO_PRODUCT_RELATION_EXISTS');
751 */
752
753 -- modification begin for perf. tuning.. abedajna 10/12/00
754
755 x1_dummy := 0;
756
757 SELECT 1 INTO x1_dummy
758 FROM WSM_co_products
759 WHERE component_id = X_component_id
760 AND organization_id = X_organization_id
761 AND ((X_Rowid IS NULL) OR (ROWID <> X_ROWID));
762
763 IF x1_dummy <> 0 THEN
764 RAISE co_pdt_reltn_exst_error;
765 END IF;
766
767 EXCEPTION
768
769 WHEN co_pdt_reltn_exst_error THEN
770 fnd_message.set_name ('BOM', 'CO_PRODUCT_RELATION_EXISTS');
771
772
773 WHEN TOO_MANY_ROWS THEN
774 fnd_message.set_name ('BOM', 'CO_PRODUCT_RELATION_EXISTS');
775
776
777 WHEN NO_DATA_FOUND THEN
778 NULL;
779
780 -- modification end for perf. tuning.. abedajna 10/12/00
781
782
783 SELECT item_number
784 INTO x_component
785 FROM mtl_item_flexfields
786 WHERE inventory_item_id = x_component_id
787 AND organization_id = x_organization_id;
788
789 fnd_message.set_token ('COMPONENT', x_component);
790 app_exception.raise_exception;
791
792 END Check_Unique;
793
794
795 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
796 BEGIN
797
798 DELETE FROM WSM_CO_PRODUCTS
799 WHERE rowid = X_Rowid;
800
801 if (SQL%NOTFOUND) then
802 Raise NO_DATA_FOUND;
803 end if;
804 END Delete_Row;
805
806 END WSMPCPDS;