[Home] [Help]
PACKAGE BODY: APPS.MSD_BOM_COLLECTIONS
Source
1 PACKAGE BODY MSD_BOM_COLLECTIONS AS
2 /* $Header: msdbmclb.pls 120.2 2006/03/02 00:51:32 brampall noship $ */
3 /* Declare Private Procedures */
4
5 /* Bom Item Types */
6 C_MODEL Constant NUMBER := 1;
7 C_OPTION_CLASS Constant NUMBER := 2;
8 C_PLANNING Constant NUMBER := 3;
9 C_STANDARD Constant NUMBER := 4;
10 C_PRODUCT_FAMILY Constant NUMBER := 5;
11
12 /* ATO Forecast Control Options */
13 C_CONSUME Constant NUMBER := 1;
14 C_CONSUME_DERIVE Constant NUMBER := 2;
15 C_NONE Constant NUMBER := 3;
16
17 /* MRP Planning Codes */
18 C_NO_PLANNING Constant NUMBER := 6;
19
20 /* Initialized Variables */
21 C_INIT_VARCHAR2 Constant VARCHAR2(255) := 'init';
22 C_INIT_DATE Constant DATE := sysdate;
23 C_INIT_NUMBER Constant NUMBER := 0;
24
25 /* Switch for Debugging */
26 C_DEBUG Constant BOOLEAN := FALSE;
27
28 /* Temp Variables */
29 tmp1 varchar2(100);
30 tmp2 number;
31 tmp3 number;
32
33 /* Determines Bom Type for a given Item
34 * Given the following :
35 *
36 * (1) Instance, (2) Source Org Pk, (3) Source Inventory Item Id
37 *
38 * Returns: 1 - Model
39 * 2 - Option Class
40 * 3 - Standard
41 * 4 - Planning
42 * 5 - Product Family
43 *
44 */
45
46 Procedure get_bom_item_type(
47 p_answer in out nocopy number,
48 P_instance in varchar2,
49 p_sr_org_pk in varchar2,
50 p_sr_inventory_item_pk in varchar2);
51
52
53 /* Determines all Assemblies using this Item
54 *
55 * Given the following :
56 *
57 * (1) Instance, (2) Source Org Pk, (3) Source Inventory Item Id
58 *
59 * Returns: Assemblies appended to p_parents.
60 *
61 */
62 procedure get_all_parents (
63 p_parents in out nocopy parents,
64 p_instance in varchar2,
65 p_sr_org_pk in varchar2,
66 p_asmb_ascp_pk in varchar2,
67 p_planning_factor in number,
68 p_quantity_per in number);
69
70 procedure debug_line ( p_msg in varchar2);
71
72 /* Option Class Parents Stack*/
73 oc_parents parents;
74
75 /* Model Parents Stack*/
76 mo_parents parents;
77
78 /* Public Procedures */
79
80 procedure collect_bom_data (
81 errbuf in out nocopy varchar2,
82 retcode in out nocopy varchar2,
83 p_instance_id in number) IS
84
85 p_bom_item_type number := 0;
86 p_first_parent parent_type;
87 x_sr_level_pk varchar2(255);
88 i number := 0;
89
90 /* Create the Collections for looping */
91 L_INSTANCE varchar2List;
92 L_ORG_SR_PKS varchar2List;
93 L_ASSEMBLY_ASCP_PKS numberList;
94 L_COMPONENT_SR_PKS varchar2List;
95 L_EFFECTIVE_DATES dateList;
96 L_DISABLE_DATES dateList;
97 L_QUANTITY_PER numberList;
98 L_PLANNING_FACTOR numberList;
99 L_BILL_SEQUENCE_ID numberList;
100 L_OPTIONAL_FLAG numberList;
101
102 /* Create the Collections needed for Bulk Insert */
103 C_INSTANCE varchar2List := varchar2List(C_INIT_VARCHAR2);
104 C_ORG_PKS varchar2List := varchar2List(C_INIT_VARCHAR2);
105 C_ORG_SR_PKS varchar2List := varchar2List(C_INIT_VARCHAR2);
106 C_ASSEMBLY_PKS varchar2List := varchar2List(C_INIT_VARCHAR2);
107 C_ASSEMBLY_SR_PKS varchar2List := varchar2List(C_INIT_VARCHAR2);
108 C_COMPONENT_PKS varchar2List := varchar2List(C_INIT_VARCHAR2);
109 C_COMPONENT_SR_PKS varchar2List := varchar2List(C_INIT_VARCHAR2);
110 C_EFFECTIVE_DATES dateList := dateList(C_INIT_DATE);
111 C_DISABLE_DATES dateList := dateList(C_INIT_DATE);
112 C_QUANTITY_PER numberList := numberList(C_INIT_NUMBER);
113 C_PLANNING_FACTOR numberList := numberList(C_INIT_NUMBER);
114 C_OPTIONAL_FLAG numberList := numberList(C_INIT_NUMBER);
115
116 CURSOR C1 Is
117 SELECT DISTINCT
118 mb.sr_instance_id,
119 mb.organization_id,
120 mbc.using_assembly_id,
121 ascp_comp.sr_inventory_item_id,
122 mbc.effectivity_date,
123 mbc.disable_date,
124 decode(mbc.usage_quantity/decode(mbc.usage_quantity,
125 null,1,
126 0,1,
127 abs(mbc.usage_quantity)),
128 1, (mbc.usage_quantity * mbc.Component_Yield_Factor),
129 (mbc.usage_quantity / mbc.Component_Yield_Factor))*msd_common_utilities.uom_conv(ascp_comp.uom_code,ascp_comp.inventory_item_id) usage_quantity,
130 mbc.planning_factor,
131 mb.bill_sequence_id,
132 mbc.optional_component
133 FROM msc_bom_components mbc,
134 msc_boms mb,
135 msc_system_items assemble,
136 msc_system_items ascp_comp
137 WHERE mbc.plan_id = -1
138 AND mb.plan_id = mbc.plan_id
139 AND ascp_comp.plan_id = mbc.plan_id
140 AND mb.organization_id = mbc.organization_id
141 AND mb.alternate_bom_designator is null
142 AND mb.bill_sequence_id = mbc.bill_sequence_id
143 AND assemble.sr_instance_id = mbc.sr_instance_id
144 AND assemble.plan_id = mbc.plan_id
145 AND assemble.inventory_item_id = mbc.using_assembly_id
146 AND assemble.organization_id = mbc.organization_id
147 AND (assemble.mrp_planning_code <> 6 or -- Exclude non plan ATO, but include PTO
148 (assemble.mrp_planning_code = 6 and assemble.pick_components_flag = 'Y'))
149 AND assemble.ato_forecast_control <> 3
150 AND (assemble.bom_item_type <> 4 or -- exclude Standard bom, but include Kit
151 (assemble.bom_item_type = 4 and assemble.pick_components_flag = 'Y'))
152 AND mbc.inventory_item_id = ascp_comp.inventory_item_id
153 AND mbc.organization_id = ascp_comp.organization_id
154 AND mbc.sr_instance_id = ascp_comp.sr_instance_id
155 AND ascp_comp.ato_forecast_control = C_CONSUME_DERIVE
156 AND ascp_comp.bom_item_type in (C_MODEL,C_STANDARD)
157 AND ( ascp_comp.mrp_planning_code <> C_NO_PLANNING or
158 (ascp_comp.mrp_planning_code = C_NO_PLANNING and ascp_comp.pick_components_flag = 'Y'))
159 AND mbc.sr_instance_id = mb.sr_instance_id; -- Support PTO as component
160
161
162 cursor c2(p_instance in number,
163 p_org_id in number,
164 p_item_id in number) is
165 select sr_inventory_item_id
166 from msc_system_items
167 where sr_instance_id = p_instance
168 and plan_id = -1
169 and organization_id = p_org_id
170 and inventory_item_id = p_item_id;
171
172
173 compLastIndex number;
174 numInsert number := 1;
175 numMo number := 1;
176 icount number:= 0;
177
178 BEGIN
179
180 retcode := '0';
181
182 if (C_DEBUG) then
183 debug_line('Checking Profile Option.');
184 end if;
185
186 if (fnd_profile.value('MSD_PLANNING_PERCENTAGE') <> 3) then
187 if (C_DEBUG) then
188 debug_line('This program only runs for Planning Percentage option 3.');
189 end if;
190 return;
191 end if;
192
193 if (C_DEBUG) then
194 debug_line('Deleting Old Bom Data.');
195 end if;
196
197 Delete from msd_bom_components
198 where instance = p_instance_id;
199
200 if (C_DEBUG) then
201 debug_line('Selecting Valid Components from Msc');
202 end if;
203
204 Open c1;
205 FETCH c1 bulk collect into
206 L_INSTANCE,
207 L_ORG_SR_PKS,
208 L_ASSEMBLY_ASCP_PKS,
209 L_COMPONENT_SR_PKS,
210 L_EFFECTIVE_DATES,
211 L_DISABLE_DATES,
212 L_QUANTITY_PER,
213 L_PLANNING_FACTOR,
214 L_BILL_SEQUENCE_ID,
215 L_OPTIONAL_FLAG;
216
217
218 -- Bug 4266827. If no rows exist then do not continue.
219
220 if c1%ROWCOUNT = 0 then
221 close c1;
222 return;
223 end if;
224
225 Close c1;
226
227 debug_line('Begin looping through all components.');
228
229 For j in L_COMPONENT_SR_PKS.first..L_COMPONENT_SR_PKS.last loop
230
231 mo_parents.delete;
232
233 if (C_DEBUG) then
234 debug_line('Row: ' || icount);
235 debug_line(' Instance: ' || icount);
236 debug_line(' Organization Sr Pk: ' || L_ORG_SR_PKS(j));
237 debug_line(' Assembly Pk: ' || L_ASSEMBLY_ASCP_PKS(j));
238 debug_line(' Component SrPk: ' || L_COMPONENT_SR_PKS(j));
239 debug_line(' Effective Date: ' || L_EFFECTIVE_DATES(j));
240 debug_line(' Bill Sequence Id: ' || L_BILL_SEQUENCE_ID(j));
241 debug_line(' Optional Flag : ' || L_OPTIONAL_FLAG(j));
242 end if;
243
244 if (icount = 700) then
245 -- exit;
246 null;
247 end if;
248
249 icount := icount + 1;
250
251 get_bom_item_type(
252 p_answer => p_bom_item_type,
253 p_instance => L_INSTANCE(j),
254 p_sr_org_pk => L_ORG_SR_PKS(j),
255 p_sr_inventory_item_pk => L_ASSEMBLY_ASCP_PKS(j));
256
257 oc_parents(1).item_id := 0;
258 oc_parents(1).planning_factor := 0;
259 oc_parents(1).quantity_per := 0;
260
261 mo_parents(1).item_id := 0;
262 mo_parents(1).planning_factor := 0;
263 mo_parents(1).quantity_per := 0;
264
265 if (p_bom_item_type = C_OPTION_CLASS) then
266
267 get_all_parents(
268 p_parents => oc_parents,
269 P_instance => L_INSTANCE(j),
270 p_sr_org_pk => L_ORG_SR_PKS(j),
271 p_asmb_ascp_pk => L_ASSEMBLY_ASCP_PKS(j),
272 p_planning_factor => L_PLANNING_FACTOR(j),
273 p_quantity_per => L_QUANTITY_PER(j)
274 );
275
276 while oc_parents.count > 0 loop
277 p_bom_item_type := 0;
278
279 compLastIndex := oc_parents.last;
280
281 if compLastIndex is null then
282 if (C_DEBUG) then
283 debug_line('compLastIndex is null in method bom_collections');
284 end if;
285 end if;
286
287 get_bom_item_type(
288 p_answer => p_bom_item_type,
289 p_instance => L_INSTANCE(j),
290 p_sr_org_pk => L_ORG_SR_PKS(j),
291 p_sr_inventory_item_pk => oc_parents(compLastIndex).item_id);
292
293 if (p_bom_item_type = C_OPTION_CLASS) then
294
295 tmp1 := oc_parents(compLastIndex).item_id;
296 tmp2 := oc_parents(compLastIndex).planning_factor;
297 tmp3 := oc_parents(compLastIndex).quantity_per;
298
299 oc_parents.delete(compLastIndex);
300
301 get_all_parents(
302 p_parents => oc_parents,
303 p_instance => L_INSTANCE(j),
304 p_sr_org_pk => L_ORG_SR_PKS(j),
305 p_asmb_ascp_pk => tmp1,
306 p_planning_factor => tmp2,
307 p_quantity_per => tmp3
308 );
309
310 elsif (p_bom_item_type = C_MODEL) then
311 mo_parents(numMo).item_id := oc_parents(compLastIndex).item_id;
312 mo_parents(numMo).planning_factor := oc_parents(compLastIndex).planning_factor;
313 mo_parents(numMo).quantity_per := oc_parents(compLastIndex).quantity_per;
314 numMo := numMo + 1;
315 oc_parents.delete(compLastIndex);
316 else
317 oc_parents.delete(compLastIndex);
318 end if;
319
320 end loop;
321
322 i := mo_parents.FIRST; -- get subscript of first element
323 WHILE i IS NOT NULL LOOP
324
325 if (numInsert > C_INSTANCE.LAST) then
326 C_INSTANCE.extend;
327 C_ORG_SR_PKS.extend;
328 C_ASSEMBLY_SR_PKS.extend;
329 C_COMPONENT_SR_PKS.extend;
330 C_EFFECTIVE_DATES.extend;
331 C_DISABLE_DATES.extend;
332 C_QUANTITY_PER.extend;
333 C_PLANNING_FACTOR.extend;
334 C_OPTIONAL_FLAG.extend;
335 end if;
336
337
338 open c2(L_INSTANCE(j), to_number(L_ORG_SR_PKS(j)), mo_parents(i).item_id);
339 fetch c2 into x_sr_level_pk;
340 close c2;
341
342 if (x_sr_level_pk is not null and mo_parents(i).quantity_per <> 0 and mo_parents(i).planning_factor <> 0) then
343 if (numInsert > C_INSTANCE.LAST) then
344 C_INSTANCE.extend;
345 C_ORG_SR_PKS.extend;
346 C_ASSEMBLY_SR_PKS.extend;
347 C_COMPONENT_SR_PKS.extend;
348 C_EFFECTIVE_DATES.extend;
349 C_DISABLE_DATES.extend;
350 C_QUANTITY_PER.extend;
351 C_PLANNING_FACTOR.extend;
352 C_OPTIONAL_FLAG.extend;
353 end if;
354
355 C_INSTANCE(numInsert) := L_INSTANCE(j);
356 C_ORG_SR_PKS(numInsert) := L_ORG_SR_PKS(j);
357 C_ASSEMBLY_SR_PKS(numInsert) := x_sr_level_pk;
358 C_COMPONENT_SR_PKS(numInsert) := L_COMPONENT_SR_PKS(j);
359 C_EFFECTIVE_DATES(numInsert) := L_EFFECTIVE_DATES(j);
360 C_DISABLE_DATES(numInsert) := L_DISABLE_DATES(j);
361 C_QUANTITY_PER(numInsert) := mo_parents(i).quantity_per;
362 C_PLANNING_FACTOR(numInsert) := mo_parents(i).planning_factor;
363 C_OPTIONAL_FLAG(numInsert) := L_OPTIONAL_FLAG(j);
364
365 numInsert := numInsert + 1;
366 end if;
367
368 i := mo_parents.NEXT(i); -- get subscript of next element
369
370 END LOOP;
371
372
373
374 elsif (p_bom_item_type = C_MODEL or
375 p_bom_item_type = C_STANDARD ) then /* To bring PTO Kit */
376
377 open c2(L_INSTANCE(j), to_number(L_ORG_SR_PKS(j)), L_ASSEMBLY_ASCP_PKS(j));
378 fetch c2 into x_sr_level_pk;
379 close c2;
380
381 if (x_sr_level_pk is not null) then
382 if (numInsert > C_INSTANCE.LAST) then
383 C_INSTANCE.extend;
384 C_ORG_SR_PKS.extend;
385 C_ASSEMBLY_SR_PKS.extend;
386 C_COMPONENT_SR_PKS.extend;
387 C_EFFECTIVE_DATES.extend;
388 C_DISABLE_DATES.extend;
389 C_QUANTITY_PER.extend;
390 C_PLANNING_FACTOR.extend;
391 C_OPTIONAL_FLAG.extend;
392 end if;
393
394 C_INSTANCE(numInsert) := L_INSTANCE(j);
395 C_ORG_SR_PKS(numInsert) := L_ORG_SR_PKS(j);
396 C_ASSEMBLY_SR_PKS(numInsert) := x_sr_level_pk;
397 C_COMPONENT_SR_PKS(numInsert) := L_COMPONENT_SR_PKS(j);
398 C_EFFECTIVE_DATES(numInsert) := L_EFFECTIVE_DATES(j);
399 C_DISABLE_DATES(numInsert) := L_DISABLE_DATES(j);
400 C_QUANTITY_PER(numInsert) := L_QUANTITY_PER(j);
401 C_PLANNING_FACTOR(numInsert) := L_PLANNING_FACTOR(j);
402 C_OPTIONAL_FLAG(numInsert) := L_OPTIONAL_FLAG(j);
403
404 numInsert := numInsert + 1;
405 end if;
406 end if;
407
408 end loop;
409
410
411 if (C_INSTANCE(1) = C_INIT_VARCHAR2) then
412 if (C_DEBUG) then
413 debug_line('There is no data to insert.');
414 end if;
415 else
416 -- INSERT THE DATA
417 -- start with index 2, index 1 contains the initial characters
418 FORALL k in 2..C_INSTANCE.last
419 INSERT INTO msd_bom_components (
420 instance,
421 sr_organization_pk,
422 sr_assembly_pk,
423 sr_component_pk,
424 effectivity_date,
425 disable_date,
426 quantity_per,
427 planning_factor,
428 creation_date,
429 created_by,
430 last_update_date,
431 last_updated_by,
432 last_update_login,
433 optional_flag)
434 VALUES (
435 C_INSTANCE(k),
436 C_ORG_SR_PKS(k),
437 C_ASSEMBLY_SR_PKS(k),
438 C_COMPONENT_SR_PKS(k),
439 C_EFFECTIVE_DATES(k),
440 C_DISABLE_DATES(k),
441 C_QUANTITY_PER(k),
442 C_PLANNING_FACTOR(k),
443 sysdate,
444 fnd_global.user_id,
445 sysdate,
446 fnd_global.user_id,
447 fnd_global.user_id,
448 C_OPTIONAL_FLAG(k) );
449
450 if (C_DEBUG) then
451 debug_line('The number of rows inserted is : ' || numInsert);
452 end if;
453 end if;
454
455 Exception
456 when others then
457 if (C_DEBUG) then
458 debug_line(sqlerrm);
459 end if;
460 retcode :='-1';
461 END collect_bom_data;
462
463
464 Procedure get_bom_item_type(
465 p_answer in out nocopy number,
466 P_instance in varchar2,
467 p_sr_org_pk in varchar2,
468 p_sr_inventory_item_pk in varchar2) is
469
470 CURSOR C1 IS
471 SELECT bom_item_type
472 FROM msc_system_items
473 WHERE sr_instance_id = p_instance
474 AND organization_id = p_sr_org_pk
475 AND inventory_item_id = p_sr_inventory_item_pk
476 AND plan_id = -1;
477
478 Begin
479 Open c1;
480 Fetch c1 into p_answer;
481
482 If c1%NOTFOUND then
483 p_answer := 0;
484 end if;
485 Close c1;
486
487 End get_bom_item_type;
488
489 /* Finds all assemblies using a component. This procedure is called when
490 * a component is selected and its parent is an option class. When this
491 * occurs, the option class's nearest model needs to be found. Therefore,
492 * the components grandparents which are the assemblies parents are
493 * placed in a stack for further inspection.
494 *
495 * Model A
496 * |
497 * |--- Option Class A
498 * | |
499 * | |--- Option Class A'
500 * | |
501 * | |--- Component A
502 * |
503 * Model B
504 * |
505 * |--- Option Class A
506 * | |
507 * | |--- Option Class A'
508 * | |
509 * | |--- Component A
510 * Model C
511 * |
512 * |--- Option Class A'
513 * |
514 * |--- Component A
515 *
516 *
517 * In this case, Component A needs to find Model A, B, C. Option Class A' is an
518 * option class, but is used in several places. The procedure will search
519 * the components using Depth First Search (DFS) and append possible parents
520 * to the argument parameter.
521 *
522 *
523 * Parameters : 1. p_parents - vector containing all of the assemblies.
524 * 2. p_instance - source location
525 * 3. p_sr_org_pk - Organization source primary key.
526 * 4. p_asmb_ascp_pk - The assembly whose parents we are looking for.
527 * 5. p_planning_factor - Planning factor from this assembly
528 */
529
530 Procedure get_all_parents(
531 P_parents in out nocopy parents,
532 P_instance in varchar2,
533 p_sr_org_pk in varchar2,
534 p_asmb_ascp_pk in varchar2,
535 p_planning_factor in number,
536 p_quantity_per in number) is
537
538 endPos number := p_parents.last;
539
540 cursor c1 is
541 select using_assembly_id,
542 planning_factor,
543 decode(mbc.usage_quantity/decode(mbc.usage_quantity,
544 null,1,
545 0,1,
546 abs(mbc.usage_quantity)),
547 1, (mbc.usage_quantity * mbc.Component_Yield_Factor),
548 (mbc.usage_quantity / mbc.Component_Yield_Factor))*msd_sr_util.uom_conv(msi.uom_code,msi.sr_inventory_item_id) usage_quantity
549 from msc_bom_components mbc,
550 msc_system_items msi
551 where mbc.plan_id = -1
552 and msi.plan_id = -1
553 and msi.organization_id = mbc.organization_id
554 and msi.sr_instance_id = p_instance
555 and mbc.organization_id = p_sr_org_pk
556 and mbc.sr_instance_id = p_instance
557 and mbc.inventory_item_id = msi.inventory_item_id
558 and msi.inventory_item_id = p_asmb_ascp_pk
559 and (mbc.optional_component = 1 or msi.ato_forecast_control in (C_CONSUME ,C_CONSUME_DERIVE));
560
561 Begin
562
563 for c_token in c1 loop
564
565 endPos := endPos + 1;
566
567 if endPos is null then
568 if (C_DEBUG) then
569 debug_line('endPos is null in method get_all_parent');
570 end if;
571 end if;
572 p_parents(endPos).item_id := c_token.using_assembly_id;
573 p_parents(endPos).planning_factor := (p_planning_factor * c_token.planning_factor) / 100;
574 p_parents(endPos).quantity_per := p_quantity_per * c_token.usage_quantity;
575
576
577 end loop;
578 end get_all_parents;
579
580 procedure debug_line ( p_msg in varchar2) is
581 begin
582 -- dbms_output.put_line(p_msg);
583 fnd_file.put_line(fnd_file.log, p_msg);
584 end debug_line;
585
586 END MSD_BOM_COLLECTIONS;