[Home] [Help]
PACKAGE BODY: APPS.MSD_DEM_COLLECT_BOM_DATA
Source
1 PACKAGE BODY MSD_DEM_COLLECT_BOM_DATA AS
2 /* $Header: msddemcbdb.pls 120.3 2009/09/23 10:50:05 sjagathe noship $ */
3
4
5 /*** CONSTANTS ***/
6
7 /* Bom Item Types */
8 C_MODEL CONSTANT NUMBER := 1;
9 C_OPTION_CLASS CONSTANT NUMBER := 2;
10 C_PLANNING CONSTANT NUMBER := 3;
11 C_STANDARD CONSTANT NUMBER := 4;
12 C_PRODUCT_FAMILY CONSTANT NUMBER := 5;
13
14 /* ATO Forecast Control Options */
15 C_CONSUME CONSTANT NUMBER := 1;
16 C_CONSUME_DERIVE CONSTANT NUMBER := 2;
17 C_NONE CONSTANT NUMBER := 3;
18
19 /* MRP Planning Codes */
20 C_NO_PLANNING CONSTANT NUMBER := 6;
21
22 /* Initialized Variables */
23 C_INIT_VARCHAR2 CONSTANT VARCHAR2(255) := 'init';
24 C_INIT_DATE CONSTANT DATE := sysdate;
25 C_INIT_NUMBER CONSTANT NUMBER := 0;
26
27 /*** CONSTANTS ***/
28
29
30 /* Temp Variables */
31 tmp1 VARCHAR2(100);
32 tmp2 NUMBER;
33 tmp3 NUMBER;
34 tmp4 DATE;
35
36 /* Option Class Parents Stack*/
37 oc_parents PARENTS;
38
39 /* Model Parents Stack*/
40 mo_parents PARENTS;
41
42
43 /*** PRIVATE PROCEDURES ***
44 *
45 * get_bom_item_type
46 * get_all_parents
47 * debug_line
48 *
49 *** PRIVATE PROCEDURES ***/
50
51
52
53
54 /* Determines Bom Type for a given Item
55 * Given the following :
56 *
57 * (1) Instance, (2) Source Org Pk, (3) Source Inventory Item Id
58 *
59 * Returns: 1 - Model
60 * 2 - Option Class
61 * 3 - Standard
62 * 4 - Planning
63 * 5 - Product Family
64 *
65 */
66 PROCEDURE GET_BOM_ITEM_TYPE(
67 p_answer IN OUT NOCOPY NUMBER,
68 p_instance IN VARCHAR2,
69 p_sr_org_pk IN VARCHAR2,
70 p_sr_inventory_item_pk IN VARCHAR2)
71 IS
72 CURSOR c1 IS
73 SELECT bom_item_type
74 FROM msc_system_items
75 WHERE sr_instance_id = p_instance
76 AND organization_id = p_sr_org_pk
77 AND inventory_item_id = p_sr_inventory_item_pk
78 AND plan_id = -1;
79 BEGIN
80
81 OPEN c1;
82 FETCH c1 INTO p_answer;
83
84 IF (c1%NOTFOUND)
85 THEN
86 p_answer := 0;
87 END IF;
88 CLOSE c1;
89
90 END GET_BOM_ITEM_TYPE;
91
92
93
94
95 /* Finds all assemblies using a component. This procedure is called when
96 * a component is selected and its parent is an option class. When this
97 * occurs, the option class's nearest model needs to be found. Therefore,
98 * the components grandparents which are the assemblies parents are
99 * placed in a stack for further inspection.
100 *
101 * Model A
102 * |
103 * |--- Option Class A
104 * | |
105 * | |--- Option Class A'
106 * | |
107 * | |--- Component A
108 * |
109 * Model B
110 * |
111 * |--- Option Class A
112 * | |
113 * | |--- Option Class A'
114 * | |
115 * | |--- Component A
116 * Model C
117 * |
118 * |--- Option Class A'
119 * |
120 * |--- Component A
121 *
122 *
123 * In this case, Component A needs to find Model A, B, C. Option Class A' is an
124 * option class, but is used in several places. The procedure will search
125 * the components using Depth First Search (DFS) and append possible parents
126 * to the argument parameter.
127 *
128 *
129 * Parameters : 1. p_parents - vector containing all of the assemblies.
130 * 2. p_instance - source location
131 * 3. p_sr_org_pk - Organization source primary key.
132 * 4. p_asmb_ascp_pk - The assembly whose parents we are looking for.
133 * 5. p_planning_factor - Planning factor from this assembly
134 * 6. p_disable_date - Disable date for assembly and its component
135 */
136 PROCEDURE GET_ALL_PARENTS (
137 p_parents IN OUT NOCOPY PARENTS,
138 p_instance IN VARCHAR2,
139 p_sr_org_pk IN VARCHAR2,
140 p_asmb_ascp_pk IN VARCHAR2,
141 p_planning_factor IN NUMBER,
142 p_quantity_per IN NUMBER,
143 p_disable_date IN DATE)
144 IS
145 endPos NUMBER := p_parents.last;
146
147 CURSOR c1 IS
148 SELECT using_assembly_id,
149 planning_factor,
150 decode(mbc.usage_quantity/decode(mbc.usage_quantity,
151 null,1,
152 0,1,
153 abs(mbc.usage_quantity)),
154 1,
155 (mbc.usage_quantity * mbc.Component_Yield_Factor),
156 (mbc.usage_quantity / mbc.Component_Yield_Factor))
157 * msd_dem_common_utilities.uom_conv(msi.sr_instance_id, msi.uom_code,msi.sr_inventory_item_id) usage_quantity,
158 mbc.disable_date disable_date
159 FROM msc_system_items msi,
160 msc_bom_components mbc
161 WHERE msi.plan_id = -1
162 AND msi.sr_instance_id = p_instance
163 AND msi.organization_id = p_sr_org_pk
164 AND msi.inventory_item_id = p_asmb_ascp_pk
165 AND mbc.plan_id = -1
166 AND mbc.sr_instance_id = p_instance
167 AND mbc.organization_id = msi.organization_id
168 AND mbc.inventory_item_id = msi.inventory_item_id
169 AND ( mbc.optional_component = 1
170 OR msi.ato_forecast_control IN (C_CONSUME ,C_CONSUME_DERIVE));
171
172 BEGIN
173
174 FOR c_token IN c1
175 LOOP
176
177 endPos := endPos + 1;
178
179 IF (endPos IS NULL)
180 THEN
181 msd_dem_common_utilities.log_debug ('msd_dem_collect_bom_data.get_all_parents - endPos is null');
182 END IF;
183
184 p_parents(endPos).item_id := c_token.using_assembly_id;
185 p_parents(endPos).planning_factor := (p_planning_factor * c_token.planning_factor) / 100;
186 p_parents(endPos).quantity_per := p_quantity_per * c_token.usage_quantity;
187
188 IF (c_token.disable_date IS NULL)
189 THEN
190 p_parents(endPos).disable_date := p_disable_date;
191 ELSIF (p_disable_date IS NULL)
192 THEN
193 p_parents(endPos).disable_date := c_token.disable_date;
194 ELSE
195 p_parents(endPos).disable_date := LEAST (c_token.disable_date, p_disable_date);
196 END IF;
197
198 END LOOP;
199
200 END GET_ALL_PARENTS;
201
202
203
204
205 /*** PUBLIC PROCEDURES ***
206 *
207 * COLLECT_BOM_DATA
208 *
209 *** PUBLIC PROCEDURES ***/
210
211
212
213
214 /*
215 *
216 */
217 PROCEDURE COLLECT_BOM_DATA (
218 errbuf OUT NOCOPY VARCHAR2,
219 retcode OUT NOCOPY VARCHAR2,
220 p_sr_instance_id IN NUMBER )
221 IS
222
223 p_bom_item_type NUMBER := 0;
224 p_first_parent PARENT_TYPE;
225 x_sr_level_pk VARCHAR2(255);
226 i NUMBER := 0;
227 compLastIndex NUMBER;
228 numInsert NUMBER := 1;
229 numMo NUMBER := 1;
230 icount NUMBER := 0;
231 x_master_org NUMBER := NULL;
232
233 x_errbuf VARCHAR2(2000) := NULL;
234 x_retcode VARCHAR2(255) := NULL;
235
236 /* Create the Collections for looping */
237 L_INSTANCE VARCHAR2LIST;
238 L_ORG_SR_PKS VARCHAR2LIST;
239 L_ASSEMBLY_ASCP_PKS NUMBERLIST;
240 L_COMPONENT_SR_PKS VARCHAR2LIST;
241 L_EFFECTIVE_DATES DATELIST;
242 L_DISABLE_DATES DATELIST;
243 L_QUANTITY_PER NUMBERLIST;
244 L_PLANNING_FACTOR NUMBERLIST;
245 L_BILL_SEQUENCE_ID NUMBERLIST;
246 L_OPTIONAL_FLAG NUMBERLIST;
247
248 /* Create the Collections needed for Bulk Insert */
249 C_INSTANCE VARCHAR2LIST := VARCHAR2LIST (C_INIT_VARCHAR2);
250 C_ORG_PKS VARCHAR2LIST := VARCHAR2LIST (C_INIT_VARCHAR2);
251 C_ORG_SR_PKS VARCHAR2LIST := VARCHAR2LIST (C_INIT_VARCHAR2);
252 C_ASSEMBLY_PKS VARCHAR2LIST := VARCHAR2LIST (C_INIT_VARCHAR2);
253 C_ASSEMBLY_SR_PKS VARCHAR2LIST := VARCHAR2LIST (C_INIT_VARCHAR2);
254 C_COMPONENT_PKS VARCHAR2LIST := VARCHAR2LIST (C_INIT_VARCHAR2);
255 C_COMPONENT_SR_PKS VARCHAR2LIST := VARCHAR2LIST (C_INIT_VARCHAR2);
256 C_EFFECTIVE_DATES DATELIST := DATELIST (C_INIT_DATE);
257 C_DISABLE_DATES DATELIST := DATELIST (C_INIT_DATE);
258 C_QUANTITY_PER NUMBERLIST := NUMBERLIST (C_INIT_NUMBER);
259 C_PLANNING_FACTOR NUMBERLIST := NUMBERLIST (C_INIT_NUMBER);
260 C_OPTIONAL_FLAG NUMBERLIST := NUMBERLIST (C_INIT_NUMBER);
261
262 /* Cursor to get all the components and their parents */
263 CURSOR c1 (p_master_org IN NUMBER)
264 IS
265 SELECT DISTINCT
266 mb.sr_instance_id,
267 mb.organization_id,
268 mbc.using_assembly_id,
269 ascp_comp.sr_inventory_item_id,
270 mbc.effectivity_date,
271 mbc.disable_date,
272 decode(mbc.usage_quantity/decode(mbc.usage_quantity,
273 null,1,
274 0,1,
275 abs(mbc.usage_quantity)),
276 1,
277 (mbc.usage_quantity * mbc.Component_Yield_Factor),
278 (mbc.usage_quantity / mbc.Component_Yield_Factor))
279 * msd_dem_common_utilities.uom_conv(ascp_comp.sr_instance_id, ascp_comp.uom_code, ascp_comp.inventory_item_id) usage_quantity,
280 mbc.planning_factor,
281 mb.bill_sequence_id,
282 mbc.optional_component
283 FROM msc_boms mb,
284 msc_bom_components mbc,
285 msc_system_items assemble,
286 msc_system_items ascp_comp
287 WHERE mb.plan_id = -1
288 AND mb.sr_instance_id = p_sr_instance_id
289 AND decode (p_master_org, null, mb.organization_id, p_master_org) = mb.organization_id
290 AND mb.alternate_bom_designator is null
291 AND mbc.bill_sequence_id = mb.bill_sequence_id
292 AND mbc.plan_id = mb.plan_id
293 AND mbc.sr_instance_id = mb.sr_instance_id
294 AND mbc.organization_id = mb.organization_id
295 AND assemble.sr_instance_id = mbc.sr_instance_id
296 AND assemble.plan_id = mbc.plan_id
297 AND assemble.inventory_item_id = mbc.using_assembly_id
298 AND assemble.organization_id = mbc.organization_id
299 AND ( assemble.mrp_planning_code <> 6 -- Exclude non plan ATO, but include PTO
300 OR
301 ( assemble.mrp_planning_code = 6
302 AND assemble.pick_components_flag = 'Y'))
303 AND assemble.ato_forecast_control <> 3
304 AND ( assemble.bom_item_type <> 4 -- exclude Standard bom, but include Kit
305 OR
306 ( assemble.bom_item_type = 4
307 AND assemble.pick_components_flag = 'Y'))
308 AND ascp_comp.plan_id = mbc.plan_id
309 AND ascp_comp.inventory_item_id = mbc.inventory_item_id
310 AND ascp_comp.organization_id = mbc.organization_id
311 AND ascp_comp.sr_instance_id = mbc.sr_instance_id
312 AND ascp_comp.ato_forecast_control = C_CONSUME_DERIVE
313 AND ascp_comp.bom_item_type in (C_MODEL,C_STANDARD)
314 AND ( ascp_comp.mrp_planning_code <> C_NO_PLANNING
315 OR ( ascp_comp.mrp_planning_code = C_NO_PLANNING
316 AND ascp_comp.pick_components_flag = 'Y')); -- Support PTO as component
317
318
319 CURSOR c2(p_instance IN NUMBER,
320 p_org_id IN NUMBER,
321 p_item_id IN NUMBER) IS
322 SELECT sr_inventory_item_id
323 FROM msc_system_items
324 WHERE sr_instance_id = p_instance
325 AND plan_id = -1
326 AND organization_id = p_org_id
327 AND inventory_item_id = p_item_id;
328
329
330 CURSOR c3 IS
331 SELECT sr_instance_id,
332 sr_organization_id,
333 sr_assembly_item_id,
334 sr_component_item_id,
335 min(effectivity_date) effectivity_date,
336 max(nvl(disable_date, to_date('01-01-4000', 'DD-MM-YYYY'))) disable_date,
337 sum(quantity_per) quantity_per,
338 (sum(quantity_per) * 100)/(decode (sum (decode (planning_factor, 0, null, quantity_per * 100 /planning_factor)),
339 0,1,null,1,
340 sum (decode (planning_factor, 0, null, quantity_per * 100 /planning_factor)))) planning_factor,
341 min(optional_flag) optional_flag
342 FROM
343 msd_dem_bom_components
344 GROUP BY
345 sr_instance_id,
346 sr_organization_id,
347 sr_assembly_item_id,
348 sr_component_item_id
349 HAVING count(*) > 1;
350
351
352 BEGIN
353
354 msd_dem_common_utilities.log_debug ('Entering: msd_dem_collect_bom_data.collect_bom_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
355
356 retcode := 0;
357 /* Log the parameters */
358 msd_dem_common_utilities.log_debug (' Instance ID - ' || to_char(p_sr_instance_id));
359
360 /* The procedure should only execute if profile MSD_DEM: Include Dependent Demand is set to yes. */
361 IF (fnd_profile.value('MSD_DEM_INCLUDE_DEPENDENT_DEMAND') = 2)
362 THEN
363 msd_dem_common_utilities.log_message ('In msd_dem_collect_bom_data.collect_bom_data - '
364 || 'Profile MSD_DEM: Include Dependent Demand is set to No. '
365 || 'Hence no action taken. Exiting Normally.');
366 retcode := 0;
367 RETURN;
368 END IF;
369
370 /* The procedure should only execute if profile MSD_DEM: Calculate Planning Percentage
371 * is set to Collect Consume and Derive Options Only. */
372 IF (fnd_profile.value('MSD_DEM_PLANNING_PERCENTAGE') <> 2)
373 THEN
374 msd_dem_common_utilities.log_message ('In msd_dem_collect_bom_data.collect_bom_data - '
375 || 'Profile MSD_DEM: Calculate Planning Percentage is not set to Collect Options Only. '
376 || 'Hence no action taken. Exiting Normally.');
377 retcode := 0;
378 RETURN;
379 END IF;
380
381 msd_dem_common_utilities.log_message ('Truncating table MSD_DEM_BOM_COMPONENTS');
382 msd_dem_query_utilities.truncate_table (
383 errbuf,
384 retcode,
385 'MSD_DEM_BOM_COMPONENTS',
386 2,
387 1);
388 IF (retcode = -1)
389 THEN
390 msd_dem_common_utilities.log_message ('Error(1) in msd_dem_collect_bom_data.collect_bom_data - '
391 || 'Error in call to msd_dem_query_utilities.truncate_table');
392 msd_dem_common_utilities.log_message(errbuf);
393 RETURN;
394 END IF;
395
396 IF (to_number(fnd_profile.value('MSD_DEM_EXPLODE_DEMAND_METHOD')) = 2)
397 THEN
398 x_master_org := msd_dem_common_utilities.get_parameter_value (p_sr_instance_id, 'MSD_DEM_MASTER_ORG');
399 END IF;
400
401 msd_dem_common_utilities.log_debug ('Select Valid Components from MSC');
402 OPEN c1 (x_master_org);
403 FETCH c1 BULK COLLECT INTO
404 L_INSTANCE,
405 L_ORG_SR_PKS,
406 L_ASSEMBLY_ASCP_PKS,
407 L_COMPONENT_SR_PKS,
408 L_EFFECTIVE_DATES,
409 L_DISABLE_DATES,
410 L_QUANTITY_PER,
411 L_PLANNING_FACTOR,
412 L_BILL_SEQUENCE_ID,
413 L_OPTIONAL_FLAG;
414
415 IF (c1%ROWCOUNT = 0)
416 THEN
417 CLOSE c1;
418 msd_dem_common_utilities.log_message ('Error(2) in msd_dem_collect_bom_data.collect_bom_data - '
419 || 'No rows found in c1 cursor');
420 retcode := -1;
421 RETURN;
422 END IF;
423
424 CLOSE c1;
425
426 msd_dem_common_utilities.log_debug ('Begin looping through all components.');
427
428 FOR j IN L_COMPONENT_SR_PKS.FIRST..L_COMPONENT_SR_PKS.LAST
429 LOOP
430
431 mo_parents.delete;
432
433 msd_dem_common_utilities.log_debug('Row: ' || icount);
434 msd_dem_common_utilities.log_debug(' Instance: ' || icount);
435 msd_dem_common_utilities.log_debug(' Organization Sr Pk: ' || L_ORG_SR_PKS(j));
436 msd_dem_common_utilities.log_debug(' Assembly Pk: ' || L_ASSEMBLY_ASCP_PKS(j));
437 msd_dem_common_utilities.log_debug(' Component SrPk: ' || L_COMPONENT_SR_PKS(j));
438 msd_dem_common_utilities.log_debug(' Effective Date: ' || L_EFFECTIVE_DATES(j));
439 msd_dem_common_utilities.log_debug(' Bill Sequence Id: ' || L_BILL_SEQUENCE_ID(j));
440 msd_dem_common_utilities.log_debug(' Optional Flag : ' || L_OPTIONAL_FLAG(j));
441
442 icount := icount + 1;
443
444 get_bom_item_type(
445 p_answer => p_bom_item_type,
446 p_instance => L_INSTANCE(j),
447 p_sr_org_pk => L_ORG_SR_PKS(j),
448 p_sr_inventory_item_pk => L_ASSEMBLY_ASCP_PKS(j));
449
450 oc_parents(1).item_id := 0;
451 oc_parents(1).planning_factor := 0;
452 oc_parents(1).quantity_per := 0;
453 oc_parents(1).disable_date := NULL;
454
455 mo_parents(1).item_id := 0;
456 mo_parents(1).planning_factor := 0;
457 mo_parents(1).quantity_per := 0;
458 mo_parents(1).disable_date := NULL;
459
460 IF (p_bom_item_type = C_OPTION_CLASS)
461 THEN
462 get_all_parents(
463 p_parents => oc_parents,
464 P_instance => L_INSTANCE(j),
465 p_sr_org_pk => L_ORG_SR_PKS(j),
466 p_asmb_ascp_pk => L_ASSEMBLY_ASCP_PKS(j),
467 p_planning_factor => L_PLANNING_FACTOR(j),
468 p_quantity_per => L_QUANTITY_PER(j),
469 p_disable_date => L_DISABLE_DATES(j) );
470
471 WHILE (oc_parents.count > 0) -- Start of While Loop1
472 LOOP
473
474 p_bom_item_type := 0;
475 compLastIndex := oc_parents.last;
476
477 IF compLastIndex IS NULL
478 THEN
479 msd_dem_common_utilities.log_debug('compLastIndex is null in method bom_collections');
480 END IF;
481
482 get_bom_item_type(
483 p_answer => p_bom_item_type,
484 p_instance => L_INSTANCE(j),
485 p_sr_org_pk => L_ORG_SR_PKS(j),
486 p_sr_inventory_item_pk => oc_parents(compLastIndex).item_id);
487
488 IF (p_bom_item_type = C_OPTION_CLASS)
489 THEN
490
491 tmp1 := oc_parents(compLastIndex).item_id;
492 tmp2 := oc_parents(compLastIndex).planning_factor;
493 tmp3 := oc_parents(compLastIndex).quantity_per;
494 tmp4 := oc_parents(compLastIndex).disable_date;
495
496 oc_parents.delete(compLastIndex);
497
498 get_all_parents(
499 p_parents => oc_parents,
500 p_instance => L_INSTANCE(j),
501 p_sr_org_pk => L_ORG_SR_PKS(j),
502 p_asmb_ascp_pk => tmp1,
503 p_planning_factor => tmp2,
504 p_quantity_per => tmp3,
505 p_disable_date => tmp4 );
506
507 ELSIF (p_bom_item_type = C_MODEL)
508 THEN
509 mo_parents(numMo).item_id := oc_parents(compLastIndex).item_id;
510 mo_parents(numMo).planning_factor := oc_parents(compLastIndex).planning_factor;
511 mo_parents(numMo).quantity_per := oc_parents(compLastIndex).quantity_per;
512 mo_parents(numMo).disable_date := oc_parents(compLastIndex).disable_date;
513 numMo := numMo + 1;
514 oc_parents.delete(compLastIndex);
515 ELSE
516 oc_parents.delete(compLastIndex);
517 END IF;
518
519 END LOOP; -- End of While Loop1
520
521 i := mo_parents.FIRST; -- get subscript of first element
522 WHILE (i IS NOT NULL) -- Start of While Loop2
523 LOOP
524
525 IF (numInsert > C_INSTANCE.LAST)
526 THEN
527 C_INSTANCE.extend;
528 C_ORG_SR_PKS.extend;
529 C_ASSEMBLY_SR_PKS.extend;
530 C_COMPONENT_SR_PKS.extend;
531 C_EFFECTIVE_DATES.extend;
532 C_DISABLE_DATES.extend;
533 C_QUANTITY_PER.extend;
534 C_PLANNING_FACTOR.extend;
535 C_OPTIONAL_FLAG.extend;
536 END IF;
537
538 OPEN c2(L_INSTANCE(j), to_number(L_ORG_SR_PKS(j)), mo_parents(i).item_id);
539 FETCH c2 INTO x_sr_level_pk;
540 CLOSE c2;
541
542 IF ( x_sr_level_pk IS NOT NULL
543 AND mo_parents(i).quantity_per <> 0
544 AND mo_parents(i).planning_factor <> 0)
545 THEN
546
547 IF (numInsert > C_INSTANCE.LAST)
548 THEN
549 C_INSTANCE.extend;
550 C_ORG_SR_PKS.extend;
551 C_ASSEMBLY_SR_PKS.extend;
552 C_COMPONENT_SR_PKS.extend;
553 C_EFFECTIVE_DATES.extend;
554 C_DISABLE_DATES.extend;
555 C_QUANTITY_PER.extend;
556 C_PLANNING_FACTOR.extend;
557 C_OPTIONAL_FLAG.extend;
558 END IF;
559
560 C_INSTANCE(numInsert) := L_INSTANCE(j);
561 C_ORG_SR_PKS(numInsert) := L_ORG_SR_PKS(j);
562 C_ASSEMBLY_SR_PKS(numInsert) := x_sr_level_pk;
563 C_COMPONENT_SR_PKS(numInsert) := L_COMPONENT_SR_PKS(j);
564 C_EFFECTIVE_DATES(numInsert) := L_EFFECTIVE_DATES(j);
565 C_DISABLE_DATES(numInsert) := mo_parents(i).disable_date;
566 C_QUANTITY_PER(numInsert) := mo_parents(i).quantity_per;
567 C_PLANNING_FACTOR(numInsert) := mo_parents(i).planning_factor;
568 C_OPTIONAL_FLAG(numInsert) := L_OPTIONAL_FLAG(j);
569
570 numInsert := numInsert + 1;
571
572 END IF;
573
574 i := mo_parents.NEXT(i); -- get subscript of next element
575
576 END LOOP; -- End of While Loop2
577
578 ELSIF ( p_bom_item_type = C_MODEL
579 OR p_bom_item_type = C_STANDARD ) /* To bring PTO Kit */
580 THEN
581
582 OPEN c2(L_INSTANCE(j), to_number(L_ORG_SR_PKS(j)), L_ASSEMBLY_ASCP_PKS(j));
583 FETCH c2 INTO x_sr_level_pk;
584 CLOSE c2;
585
586 IF (x_sr_level_pk IS NOT NULL)
587 THEN
588
589 IF (numInsert > C_INSTANCE.LAST)
590 THEN
591 C_INSTANCE.extend;
592 C_ORG_SR_PKS.extend;
593 C_ASSEMBLY_SR_PKS.extend;
594 C_COMPONENT_SR_PKS.extend;
595 C_EFFECTIVE_DATES.extend;
596 C_DISABLE_DATES.extend;
597 C_QUANTITY_PER.extend;
598 C_PLANNING_FACTOR.extend;
599 C_OPTIONAL_FLAG.extend;
600 END IF;
601
602 C_INSTANCE(numInsert) := L_INSTANCE(j);
603 C_ORG_SR_PKS(numInsert) := L_ORG_SR_PKS(j);
604 C_ASSEMBLY_SR_PKS(numInsert) := x_sr_level_pk;
605 C_COMPONENT_SR_PKS(numInsert) := L_COMPONENT_SR_PKS(j);
606 C_EFFECTIVE_DATES(numInsert) := L_EFFECTIVE_DATES(j);
607 C_DISABLE_DATES(numInsert) := L_DISABLE_DATES(j);
608 C_QUANTITY_PER(numInsert) := L_QUANTITY_PER(j);
609 C_PLANNING_FACTOR(numInsert) := L_PLANNING_FACTOR(j);
610 C_OPTIONAL_FLAG(numInsert) := L_OPTIONAL_FLAG(j);
611
612 numInsert := numInsert + 1;
613
614 END IF;
615
616 END IF;
617
618 END LOOP;
619
620
621 IF (C_INSTANCE(1) = C_INIT_VARCHAR2)
622 THEN
623 msd_dem_common_utilities.log_debug ('There is no data to insert - ' || to_char(C_INSTANCE.LAST));
624 ELSE
625
626 -- INSERT THE DATA
627 FORALL k IN 1..C_INSTANCE.LAST
628 INSERT INTO MSD_DEM_BOM_COMPONENTS (
629 sr_instance_id,
630 sr_organization_id,
631 sr_assembly_item_id,
632 sr_component_item_id,
633 effectivity_date,
634 disable_date,
635 quantity_per,
636 planning_factor,
637 creation_date,
638 created_by,
639 last_update_date,
640 last_updated_by,
641 last_update_login,
642 optional_flag)
643 VALUES (
644 C_INSTANCE(k),
645 C_ORG_SR_PKS(k),
646 C_ASSEMBLY_SR_PKS(k),
647 C_COMPONENT_SR_PKS(k),
648 C_EFFECTIVE_DATES(k),
649 C_DISABLE_DATES(k),
650 C_QUANTITY_PER(k),
651 C_PLANNING_FACTOR(k),
652 sysdate,
653 fnd_global.user_id,
654 sysdate,
655 fnd_global.user_id,
656 fnd_global.user_id,
657 C_OPTIONAL_FLAG(k) );
658
659 msd_dem_common_utilities.log_debug ('The number of rows inserted is : ' || to_char(numInsert - 1));
660
661
662 -- Remove the duplicates
663 i := 0;
664 FOR c_token IN c3
665 LOOP
666
667 i := i + 1;
668 msd_dem_common_utilities.log_debug (to_char(i) || '. Duplicate - Instance/Organization/Parent/Child - '
669 || to_char(c_token.sr_instance_id) || '/'
670 || to_char(c_token.sr_organization_id) || '/'
671 || to_char(c_token.sr_assembly_item_id) || '/'
672 || to_char(c_token.sr_component_item_id));
673
674 DELETE FROM MSD_DEM_BOM_COMPONENTS
675 WHERE sr_instance_id = c_token.sr_instance_id
676 AND sr_organization_id = c_token.sr_organization_id
677 AND sr_assembly_item_id = c_token.sr_assembly_item_id
678 AND sr_component_item_id = c_token.sr_component_item_id;
679
680 msd_dem_common_utilities.log_debug ('Number of rows deleted : ' || to_char(SQL%ROWCOUNT));
681
682 INSERT INTO MSD_DEM_BOM_COMPONENTS (
683 sr_instance_id,
684 sr_organization_id,
685 sr_assembly_item_id,
686 sr_component_item_id,
687 effectivity_date,
688 disable_date,
689 quantity_per,
690 planning_factor,
691 creation_date,
692 created_by,
693 last_update_date,
694 last_updated_by,
695 last_update_login,
696 optional_flag)
697 VALUES (
698 c_token.sr_instance_id,
699 c_token.sr_organization_id,
700 c_token.sr_assembly_item_id,
701 c_token.sr_component_item_id,
702 c_token.effectivity_date,
703 decode (c_token.disable_date, to_date('01-01-4000', 'DD-MM-YYYY'), null, c_token.disable_date),
704 c_token.quantity_per,
705 c_token.planning_factor,
706 sysdate,
707 fnd_global.user_id,
708 sysdate,
709 fnd_global.user_id,
710 fnd_global.user_id,
711 c_token.optional_flag);
712
713 msd_dem_common_utilities.log_debug ('Number of rows inserted : ' || to_char(SQL%ROWCOUNT));
714
715 END LOOP;
716
717 COMMIT;
718
719 -- Analyze the table
720 msd_dem_collect_history_data.analyze_table(x_errbuf, x_retcode, 'MSD_DEM_BOM_COMPONENTS');
721
722 END IF;
723
724 msd_dem_common_utilities.log_debug ('Exiting: msd_dem_collect_bom_data.collect_bom_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
725
726 retcode := 0;
727
728 EXCEPTION
729 WHEN OTHERS THEN
730 errbuf := substr(SQLERRM,1,150);
731 retcode := -1;
732
733 msd_dem_common_utilities.log_message ('Exception(1): msd_dem_collect_bom_data.collect_bom_data - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
734 msd_dem_common_utilities.log_message (errbuf);
735 RETURN;
736
737 END COLLECT_BOM_DATA;
738
739
740 END MSD_DEM_COLLECT_BOM_DATA;