[Home] [Help]
PACKAGE BODY: APPS.MRP_MANAGER_PK
Source
1 PACKAGE BODY mrp_manager_pk AS
2 /* $Header: MRPPPMGB.pls 120.6.12020000.2 2013/02/27 04:34:41 lsindhur ship $ */
3
4 -- Global constant for package name
5 g_om_installed VARCHAR2(3) := NULL;
6
7
8 -- ********************** mds_explode_in_process ******************************
9 PROCEDURE mds_explode_in_process (arg_in_process_id OUT NOCOPY NUMBER,
10 arg_request_id IN NUMBER,
11 arg_user_id IN NUMBER) IS
12 var_batch_id NUMBER;
13 BEGIN
14 SELECT mrp_form_query_s.nextval
15 INTO var_batch_id
16 FROM dual;
17
18 var_watch_id := mrp_print_pk.start_watch(
19 'GEN-inserting',
20 arg_request_id,
21 arg_user_id,
22 'ENTITY',
23 'E_ITEMS',
24 'Y',
25 'TABLE',
26 'mrp_form_query(1:'||to_char(var_batch_id)||')',
27 'N');
28
29 --
30 -- Insert the following into MRP_FORM_QUERY:
31 -- 1. Items in MRP_RELIEF_INTERFACE
32 -- 2. Product family of items in (1)
33 -- 3. Components of config items in (1)
34 -- 4. Other items that are in the same product family as items in (1)
35 --
36 INSERT INTO mrp_form_query (
37 query_id,
38 number1,
39 number2,
40 number3,
41 last_update_date,
42 last_updated_by,
43 creation_date,
44 created_by)
45 SELECT var_batch_id,
46 upd.inventory_item_id,
47 upd.organization_id,
48 1,
49 SYSDATE,
50 -1,
51 SYSDATE,
52 -1
53 FROM mrp_relief_interface upd
54 WHERE upd.relief_type = 1
55 AND upd.request_id IS NOT NULL
56 AND upd.error_message IS NULL
57 AND upd.process_status = 3
58 UNION
59 SELECT /*+ ordered use_nl(upd1, item) */
60 var_batch_id, /* product family */
61 item.product_family_item_id,
62 upd1.organization_id,
63 1,
64 SYSDATE,
65 -1,
66 SYSDATE,
67 -1
68 FROM mrp_relief_interface upd1,
69 mtl_system_items item
70 WHERE item.organization_id = upd1.organization_id
71 AND item.inventory_item_id = upd1.inventory_item_id
72 AND upd1.relief_type = 1
73 AND upd1.request_id IS NOT NULL
74 AND upd1.error_message IS NULL
75 AND upd1.process_status = 3
76 UNION
77 SELECT /*+ ordered use_nl(upd2,bom_item, bom, comp, comp_item) */
78 var_batch_id,
79 comp.component_item_id, /* config item's component */
80 bom.organization_id,
81 1,
82 SYSDATE,
83 -1,
84 SYSDATE,
85 -1
86 FROM mrp_relief_interface upd2,
87 mtl_system_items bom_item,
88 bom_bill_of_materials bom,
89 bom_inventory_components comp,
90 mtl_system_items comp_item
91 WHERE NVL(comp_item.ato_forecast_control, ATO_NONE) <> ATO_NONE
92 AND comp_item.inventory_item_id = comp.component_item_id
93 AND comp_item.organization_id = bom.organization_id
94 AND comp.bill_sequence_id = bom.common_bill_sequence_id
95 AND bom.alternate_bom_designator IS NULL
96 AND bom.organization_id = bom_item.organization_id
97 AND bom.assembly_item_id = bom_item.inventory_item_id
98 AND bom_item.base_item_id IS NOT NULL
99 AND bom_item.organization_id = upd2.organization_id
100 AND bom_item.inventory_item_id = upd2.inventory_item_id
101 AND upd2.relief_type = 1
102 AND upd2.request_id IS NOT NULL
103 AND upd2.error_message IS NULL
104 AND upd2.process_status = 3
105 UNION
106 SELECT /*+ ordered use_nl(upd3, item1, item2)
107 index (item2 mtl_system_items_b_n7) */
108 var_batch_id,
109 item2.inventory_item_id, /* other items that belong to */
110 item2.organization_id, /* the same product family */
111 1,
112 SYSDATE,
113 -1,
114 SYSDATE,
115 -1
116 FROM mrp_relief_interface upd3,
117 mtl_system_items item1,
118 mtl_system_items item2
119 WHERE item2.product_family_item_id = item1.product_family_item_id
120 AND item2.organization_id = item1.organization_id
121 AND item2.inventory_item_id <> item1.inventory_item_id
122 AND item1.organization_id = upd3.organization_id
123 AND item1.inventory_item_id = upd3.inventory_item_id
124 AND upd3.relief_type = 1
125 AND upd3.request_id IS NOT NULL
126 AND upd3.error_message IS NULL
127 AND upd3.process_status = 3;
128
129 mrp_print_pk.stop_watch(arg_request_id,
130 var_watch_id,
131 SQL%ROWCOUNT);
132
133 var_watch_id := mrp_print_pk.start_watch(
134 'GEN-inserting',
135 arg_request_id,
136 arg_user_id,
137 'ENTITY',
138 'E_ITEMS',
139 'Y',
140 'TABLE',
141 'mrp_form_query(2:'||to_char(var_batch_id)||')',
142 'N');
143
144 --
145 -- Insert the config items of those items inserted above
146 --
147 INSERT INTO mrp_form_query (
148 query_id,
149 number1,
150 number2,
151 number3,
152 last_update_date,
153 last_updated_by,
154 creation_date,
155 created_by)
156 SELECT DISTINCT
157 var_batch_id,
158 bom.assembly_item_id,
159 bom.organization_id,
160 2,
161 SYSDATE,
162 -1,
163 SYSDATE,
164 -1
165 FROM mtl_system_items bom_item,
166 bom_bill_of_materials bom,
167 bom_inventory_components comp,
168 mtl_system_items comp_item,
169 mrp_form_query query
170 WHERE bom_item.base_item_id IS NOT NULL
171 AND bom_item.organization_id = bom.organization_id
172 AND bom_item.inventory_item_id = bom.assembly_item_id
173 AND bom.alternate_bom_designator IS NULL
174 AND bom.organization_id = comp_item.organization_id
175 AND bom.common_bill_sequence_id = comp.bill_sequence_id
176 AND comp.component_item_id = comp_item.inventory_item_id
177 AND NVL(comp_item.ato_forecast_control, ATO_NONE) <> ATO_NONE
178 AND comp_item.organization_id = query.number2
179 AND comp_item.inventory_item_id = query.number1
180 AND query.query_id = var_batch_id;
181
182 mrp_print_pk.stop_watch(arg_request_id,
183 var_watch_id,
184 SQL%ROWCOUNT);
185
186 arg_in_process_id := var_batch_id;
187 END mds_explode_in_process;
188
189
190 -- ********************** explode_in_process ********************************
191 PROCEDURE explode_in_process (arg_in_process_id OUT NOCOPY NUMBER,
192 arg_request_id IN NUMBER,
193 arg_user_id IN NUMBER) IS
194 var_batch_id NUMBER;
195 var_batch_size NUMBER;
196 var_expl NUMBER;
197 BEGIN
198 SELECT mrp_form_query_s.nextval
199 INTO var_batch_id
200 FROM dual;
201
202 var_watch_id := mrp_print_pk.start_watch(
203 'GEN-inserting',
204 arg_request_id,
205 arg_user_id,
206 'ENTITY',
207 'E_ITEMS',
208 'Y',
209 'TABLE',
210 'mrp_form_query(1:'||to_char(var_batch_id)||')',
211 'N');
212
213 var_expl := NVL(TO_NUMBER(FND_PROFILE.VALUE('MRP_FC_EXPLOSION')), SYS_YES);
214 var_batch_size := NVL(TO_NUMBER(FND_PROFILE.VALUE(
215 'MRP_SCHED_MGR_BATCH_SIZE')), SYS_YES);
216
217 --
218 -- Insert the following into MRP_FORM_QUERY:
219 -- 1. Items in MRP_SALES_ORDER_UPDATES
220 -- 2. Product family of items in (1)
221 -- 3. Components of items in (1)
222 -- 4. Other items that are in the same product family as items in (1)
223 --
224 INSERT INTO mrp_form_query (
225 query_id,
226 number1,
227 number2,
228 number3,
229 last_update_date,
230 last_updated_by,
231 creation_date,
232 created_by)
233 SELECT /*+ INDEX (upd MRP_SALES_ORDER_UPDATES_N4) */
234 var_batch_id,
235 upd.inventory_item_id,
236 upd.organization_id,
237 -1,
238 SYSDATE,
239 -1,
240 SYSDATE,
241 -1
242 FROM mrp_sales_order_updates upd
243 WHERE upd.process_status = 3
244 UNION
245 SELECT /*+ INDEX (upd1 MRP_SALES_ORDER_UPDATES_N4)
246 ORDERED
247 USE_NL (upd1, item) */
248 var_batch_id, /* product family */
249 item.product_family_item_id,
250 upd1.organization_id,
251 1,
252 SYSDATE,
253 -1,
254 SYSDATE,
255 -1
256 FROM mrp_sales_order_updates upd1,
257 mtl_system_items item
258 WHERE item.organization_id = upd1.organization_id
259 AND item.inventory_item_id = upd1.inventory_item_id
260 AND upd1.request_id IS NOT NULL
261 AND upd1.error_message IS NULL
262 AND upd1.process_status = 3
263 UNION
264 SELECT /*+ INDEX (upd2 MRP_SALES_ORDER_UPDATES_N4)
265 ORDERED
266 USE_NL (upd2, bom_item,bom,comp,comp_item) */
267 var_batch_id,
268 comp.component_item_id, /* items's children */
269 bom.organization_id,
270 1,
271 SYSDATE,
272 -1,
273 SYSDATE,
274 -1
275 FROM mrp_sales_order_updates upd2,
276 mtl_system_items bom_item,
277 bom_bill_of_materials bom,
278 bom_inventory_components comp,
279 mtl_system_items comp_item
280 WHERE (((bom_item.bom_item_type = ITEM_TYPE_MODEL
281 --
282 -- This is the logic we have added to skip explosion of
283 -- option classes if the profile is set to SYS_NO.
284 --
285 OR (bom_item.bom_item_type = ITEM_TYPE_OPTION_CLASS
286 AND var_expl = SYS_YES))
287 AND comp.optional = SYS_NO
288 AND comp_item.bom_item_type = ITEM_TYPE_STANDARD)
289 OR (bom_item.base_item_id IS NOT NULL))
290 AND NVL(comp_item.ato_forecast_control, ATO_NONE) <> ATO_NONE
291 AND comp_item.inventory_item_id = comp.component_item_id
292 AND comp_item.organization_id = bom.organization_id
293 AND comp.bill_sequence_id = bom.common_bill_sequence_id
294 AND bom.alternate_bom_designator IS NULL
295 AND bom.organization_id = bom_item.organization_id
296 AND bom.assembly_item_id = bom_item.inventory_item_id
297 AND bom_item.pick_components_flag = 'N'
298 AND bom_item.organization_id = upd2.organization_id
299 AND bom_item.inventory_item_id = upd2.inventory_item_id
300 AND upd2.request_id IS NOT NULL
301 AND upd2.error_message IS NULL
302 AND upd2.process_status = 3
303 UNION
304 SELECT /*+ INDEX (upd3 MRP_SALES_ORDER_UPDATES_N4)
305 INDEX (item2 MTL_SYSTEM_ITEMS_B_N7)
306 ORDERED
307 USE_NL (upd3, item1,item2) */
308 var_batch_id,
309 item2.inventory_item_id, /* other items that belong to */
310 item2.organization_id, /* the same product family */
311 1,
312 SYSDATE,
313 -1,
314 SYSDATE,
315 -1
316 FROM mrp_sales_order_updates upd3,
317 mtl_system_items item1,
318 mtl_system_items item2
319 WHERE item2.product_family_item_id = item1.product_family_item_id
320 AND item2.organization_id = item1.organization_id
321 AND item2.inventory_item_id <> item1.inventory_item_id
322 AND item1.organization_id = upd3.organization_id
323 AND item1.inventory_item_id = upd3.inventory_item_id
324 AND upd3.request_id IS NOT NULL
325 AND upd3.error_message IS NULL
326 AND upd3.process_status = 3;
327
328 mrp_print_pk.stop_watch(arg_request_id,
329 var_watch_id,
330 SQL%ROWCOUNT);
331
332 var_watch_id := mrp_print_pk.start_watch(
333 'GEN-inserting',
334 arg_request_id,
335 arg_user_id,
336 'ENTITY',
337 'E_ITEMS',
338 'Y',
339 'TABLE',
340 'mrp_form_query(2:'||to_char(var_batch_id)||')',
341 'N');
342
343 --
344 -- Insert the following into MRP_FORM_QUERY:
345 -- 1. Model and Option classes of mandatory standard items
346 -- which are inserted above
347 -- 2. Config items of those items inserted above
348 --
349 INSERT INTO mrp_form_query (
350 query_id,
351 number1,
352 number2,
353 number3,
354 last_update_date,
355 last_updated_by,
356 creation_date,
357 created_by)
358 SELECT /*+ INDEX (query MRP_FORM_QUERY_N1)
359 ORDERED
360 USE_NL (query, comp_item, comp, bom, bom_item) */
361 DISTINCT
362 var_batch_id,
363 bom.assembly_item_id,
364 bom.organization_id,
365 -1,
366 SYSDATE,
367 -1,
368 SYSDATE,
369 -1
370 FROM mrp_form_query query,
371 mtl_system_items comp_item,
372 bom_inventory_components comp,
373 bom_bill_of_materials bom,
374 mtl_system_items bom_item
375 WHERE (((bom_item.bom_item_type = ITEM_TYPE_MODEL
376 --
377 -- This is the logic we have added to skip explosion of
378 -- option classes if the profile is set to SYS_NO.
379 --
380 OR (bom_item.bom_item_type = ITEM_TYPE_OPTION_CLASS
381 AND var_expl = SYS_YES))
382 AND comp.optional = SYS_NO
383 AND comp_item.bom_item_type = ITEM_TYPE_STANDARD)
384 OR (bom_item.base_item_id IS NOT NULL))
385 AND bom_item.pick_components_flag = 'N'
386 AND bom_item.organization_id = bom.organization_id
387 AND bom_item.inventory_item_id = bom.assembly_item_id
388 AND bom.alternate_bom_designator IS NULL
389 AND bom.organization_id = comp_item.organization_id
390 AND bom.common_bill_sequence_id = comp.bill_sequence_id
391 AND comp.component_item_id = comp_item.inventory_item_id
392 AND NVL(comp_item.ato_forecast_control, ATO_NONE) <> ATO_NONE
393 AND comp_item.organization_id = query.number2
394 AND comp_item.inventory_item_id = query.number1
395 AND query.query_id = var_batch_id;
396
397 mrp_print_pk.stop_watch(arg_request_id,
398 var_watch_id,
399 SQL%ROWCOUNT);
400
401 var_watch_id := mrp_print_pk.start_watch(
402 'GEN-inserting',
403 arg_request_id,
404 arg_user_id,
405 'ENTITY',
406 'E_ITEMS',
407 'Y',
408 'TABLE',
409 'mrp_form_query(3:'||to_char(var_batch_id)||')',
410 'N');
411 --
412 -- Inserting the records to process
413 --
414 INSERT INTO mrp_form_query (
415 query_id,
416 number1,
417 number2,
418 number3,
419 last_update_date,
420 last_updated_by,
421 creation_date,
422 created_by)
423 SELECT /*+ index(upd2 mrp_sales_order_updates_n4) */
424 var_batch_id,
425 -1,
426 inventory_item_id,
427 -1,
428 SYSDATE,
429 -1,
430 SYSDATE,
431 -1
432 FROM mrp_sales_order_updates upd2
433 WHERE (upd2.new_schedule_date <>
434 NVL(upd2.old_schedule_date,
435 upd2.new_schedule_date + 1)
436 OR upd2.new_schedule_quantity <>
437 NVL(upd2.old_schedule_quantity,
438 upd2.new_schedule_quantity+1)
439 OR upd2.current_customer_id <>
440 NVL(upd2.previous_customer_id,
441 upd2.current_customer_id + 1)
442 OR upd2.current_bill_id <>
443 NVL(upd2.previous_bill_id,
444 upd2.current_bill_id + 1)
445 OR upd2.current_ship_id <>
446 NVL(upd2.previous_ship_id,
447 upd2.current_ship_id + 1)
448 OR nvl(upd2.current_available_to_mrp,'N') <>
449 NVL(upd2.previous_available_to_mrp,
450 'N')
451 OR nvl(upd2.current_demand_class,'734jkhJK24') <>
452 NVL(upd2.previous_demand_class,
453 '734jkhJK24'))
454 AND upd2.process_status = 2
455 AND upd2.error_message IS NULL
456 AND upd2.request_id IS NULL
457 AND rownum <= var_batch_size
458 AND NOT EXISTS
459 (SELECT 'x'
460 FROM mrp_form_query
461 WHERE query_id = var_batch_id
462 AND number1 = upd2.inventory_item_id);
463
464 mrp_print_pk.stop_watch(arg_request_id,
465 var_watch_id,
466 SQL%ROWCOUNT);
467
468
469
470 arg_in_process_id := var_batch_id;
471 END explode_in_process;
472 -- ********************** compute_sales_order_changes *************************
473 PROCEDURE compute_sales_order_changes(arg_request_id IN NUMBER,
474 arg_user_id IN NUMBER) IS
475
476 var_break_loop INTEGER := SYS_NO;
477 var_old_so_cutoff_days NUMBER;
478 var_first_time varchar2(5);
479 pvalue boolean;
480 new_org_rec_count NUMBER;
481
482 rows_updated NUMBER := 0;
483 var_dem_rowid ROWID;
484 var_dem_inventory_item_id NUMBER;
485 var_dem_demand_id NUMBER;
486 var_dem_organization_id NUMBER;
487 var_dem_user_line_num VARCHAR2(30);
488 var_dem_requirement_date DATE;
489 var_dem_primary_uom_quantity NUMBER;
490 var_dem_customer_id NUMBER;
491 var_dem_ship_to_site_use_id NUMBER;
492 var_dem_bill_to_site_use_id NUMBER;
493 var_dem_available_to_mrp VARCHAR2(1);
494 var_dem_demand_class VARCHAR2(30);
495 var_dem_completed_quantity NUMBER;
496 var_dem_ordered_item NUMBER;
497 var_dem_source_header_id NUMBER;
498 var_upd_rowid ROWID;
499
500 TYPE line_id_table is TABLE of NUMBER
501 INDEX BY BINARY_INTEGER;
502 line_id_arr line_id_table;
503 var_org_id NUMBER;
504 var_cal_code VARCHAR2(30);
505 prev_cal_code VARCHAR2(30);
506 var_except_set_id NUMBER;
507 prev_except_set_id NUMBER;
508 var_min_cal_date DATE;
509 var_max_cal_date DATE;
510 counter NUMBER := 0;
511 counter1 NUMBER := 0;
512 var_demand_type NUMBER;
513 var_ato_line_id NUMBER;
514 var_dem_header_id NUMBER;
515 var_dem_demand_type NUMBER;
516 records_in_process NUMBER := 0;
517 config_lines_counter NUMBER;
518
519 var_debug BOOLEAN := FALSE;
520 insert_count NUMBER;
521 update_count NUMBER;
522
523 to_insert NUMBER;
524 to_update NUMBER;
525
526 var_line_id NUMBER;
527 busy EXCEPTION;
528
529 TYPE ato_model_config_rec IS RECORD
530 (
531 ato_line_id NUMBER,
532 config_line_id NUMBER
533 );
534
535 TYPE ato_model_config_tbl IS TABLE OF ato_model_config_rec
536 INDEX BY BINARY_INTEGER;
537
538 ato_model_config_arr ato_model_config_tbl;
539
540 max_ato_model_config NUMBER := 0;
541 found_config_item NUMBER; -- Indicates if the configured item is
542 -- present in the local array
543 -- ato_model_config_arr.
544 config_line_id NUMBER;
545 config_item_exists NUMBER;
546 line_ids_except_config NUMBER;
547
548 PRAGMA EXCEPTION_INIT(busy, -54);
549
550 CURSOR MTL_DEMAND_CUR_FIRST IS
551 SELECT
552 dem.inventory_item_id,
553 dem.line_id,
554 dem.SHIP_FROM_ORG_ID ,
555 dem.line_number ,
556 decode(nvl(dem.mfg_lead_time,0),
557 0, dem.SCHEDULE_SHIP_DATE,
558 decode(dem.line_id,
559 dem.ato_line_id, dem.SCHEDULE_SHIP_DATE,
560 MRP_CALENDAR.DATE_OFFSET (dem.ship_from_org_id,
561 1,
562 dem.schedule_ship_date,
563 -1*(dem.mfg_lead_time)))),
564 DECODE(dem.ORDERED_QUANTITY,
565 NULL, 0,
566 INV_DECIMALS_PUB.GET_PRIMARY_QUANTITY(
567 dem.SHIP_FROM_ORG_ID,
568 dem.INVENTORY_ITEM_ID,
569 dem.ORDER_QUANTITY_UOM,
570 dem.ORDERED_QUANTITY)),
571 dem.SOLD_TO_ORG_ID,
572 dem.SHIP_TO_ORG_ID,
573 dem.INVOICE_TO_ORG_ID,
574 NVL(visible_demand_flag,'N'),
575 dem.demand_class_code,
576 DECODE(dem.SHIPPED_QUANTITY,
577 NULL, 0,
578 INV_DECIMALS_PUB.GET_PRIMARY_QUANTITY(
579 dem.SHIP_FROM_ORG_ID,
580 dem.INVENTORY_ITEM_ID,
581 dem.ORDER_QUANTITY_UOM,
582 dem.SHIPPED_QUANTITY)),
583 DECODE(DECODE(dem.ITEM_TYPE_CODE,
584 'CLASS',2,
585 'CONFIG',4,
586 'MODEL',1,
587 'OPTION' ,3,
588 'STANDARD',6,
589 -1),
590 1, dem.inventory_item_id, NULL),
591 inv_salesorder.get_salesorder_for_oeheader(dem.HEADER_ID),
592 DECODE(dem.ITEM_TYPE_CODE,
593 'CLASS',2,
594 'CONFIG',4,
595 'MODEL',1,
596 'OPTION' ,3,
597 'STANDARD',6,
598 -1),
599 dem.ato_line_id,
600 upd.rowid
601 FROM
602 oe_order_lines_all dem,
603 mrp_sales_order_updates upd,
604 mtl_parameters param
605 WHERE NVL(upd.process_status, -1) <> 3
606 AND upd.sales_order_id(+) = dem.line_id
607 AND param.calendar_code IS NOT NULL
608 AND param.calendar_exception_set_id IS NOT NULL
609 AND param.organization_id = decode(dem.cancelled_flag,
610 'Y', upd.organization_id,
611 dem.ship_from_org_id)
612 AND dem.SOLD_TO_ORG_ID IS NOT NULL
613 AND dem.SHIP_TO_ORG_ID IS NOT NULL
614 AND dem.INVOICE_TO_ORG_ID IS NOT NULL
615 AND ((dem.SCHEDULE_SHIP_DATE is NULL
616 -- AND dem.cancelled_flag = 'Y'
617 )
618 OR (dem.SCHEDULE_SHIP_DATE IS NOT NULL
619 and dem.SCHEDULE_SHIP_DATE >=
620 (SYSDATE - var_old_so_cutoff_days)))
621 -- BUG 2848262, Need to compare
622 -- the value current_ cols in upd with
623 -- the corresponding values in dem.
624 AND (NOT EXISTS
625 (SELECT NULL
626 FROM mrp_sales_order_updates updates
627 WHERE updates.sales_order_id = dem.line_id
628 AND (
629 decode(nvl(dem.mfg_lead_time,0),
630 0,updates.new_schedule_date,
631 decode(dem.line_id,
632 dem.ato_line_id, dem.SCHEDULE_SHIP_DATE,
633 mrp_calendar.date_offset(updates.organization_id,
634 1,
635 updates.new_schedule_date,
636 dem.mfg_lead_time)))
637 = dem.SCHEDULE_SHIP_DATE
638 OR
639 -- (dem.cancelled_flag = 'Y'
640 (NVL(dem.visible_demand_flag,'N') = 'N'
641 and updates.current_available_to_mrp = 'N'))
642 AND updates.new_schedule_quantity =
643 DECODE(dem.ORDERED_QUANTITY,
644 NULL, 0,
645 INV_DECIMALS_PUB.GET_PRIMARY_QUANTITY(
646 dem.SHIP_FROM_ORG_ID,
647 dem.INVENTORY_ITEM_ID,
648 dem.ORDER_QUANTITY_UOM,
649 dem.ORDERED_QUANTITY))
650 AND updates.current_customer_id = dem.SOLD_TO_ORG_ID
651 AND updates.current_ship_id = dem.SHIP_TO_ORG_ID
652 AND updates.current_bill_id = dem.INVOICE_TO_ORG_ID
653 AND NVL(updates.current_demand_class, 'A') =
654 NVL(dem.demand_class_code, 'A')
655 AND updates.process_status <> 3))
656 AND (NOT EXISTS
657 (SELECT NULL
658 FROM Msc_FORM_QUERY query
659 WHERE query.query_id = dem.line_id))
660 AND rownum <= UPDATE_BATCH_SIZE
661 -- BUG 2848262, Either record
662 AND (dem.visible_demand_flag = 'Y' -- can be inserted
663 OR upd.rowid is NOT NULL) -- OR can be updated
664 AND DECODE(dem.SOURCE_DOCUMENT_TYPE_ID, 10, 8,
665 DECODE(dem.LINE_CATEGORY_CODE, 'ORDER',2,12))
666 IN (MTL_SALES_ORDER, MTL_INT_SALES_ORDER);
667
668
669 CURSOR MTL_DEMAND_CUR_NEXT IS
670 SELECT /*+ ORDERED USE_NL(v, dem,upd,param) */
671 dem.inventory_item_id,
672 dem.line_id,
673 dem.SHIP_FROM_ORG_ID ,
674 dem.line_number ,
675 decode(nvl(dem.mfg_lead_time,0),
676 0, dem.SCHEDULE_SHIP_DATE,
677 decode(dem.line_id,
678 dem.ato_line_id, dem.SCHEDULE_SHIP_DATE,
679 MRP_CALENDAR.DATE_OFFSET (dem.ship_from_org_id,
680 1,
681 dem.schedule_ship_date,
682 -1*(dem.mfg_lead_time)))),
683 DECODE(dem.ORDERED_QUANTITY,
684 NULL, 0,
685 INV_DECIMALS_PUB.GET_PRIMARY_QUANTITY(
686 dem.SHIP_FROM_ORG_ID,
687 dem.INVENTORY_ITEM_ID,
688 dem.ORDER_QUANTITY_UOM,
689 dem.ORDERED_QUANTITY)),
690 dem.SOLD_TO_ORG_ID,
691 dem.SHIP_TO_ORG_ID,
692 dem.INVOICE_TO_ORG_ID,
693 NVL(visible_demand_flag,'N'),
694 dem.demand_class_code,
695 DECODE(dem.SHIPPED_QUANTITY,
696 NULL, 0,
697 INV_DECIMALS_PUB.GET_PRIMARY_QUANTITY(
698 dem.SHIP_FROM_ORG_ID,
699 dem.INVENTORY_ITEM_ID,
700 dem.ORDER_QUANTITY_UOM,
701 dem.SHIPPED_QUANTITY)),
702 DECODE(DECODE(dem.ITEM_TYPE_CODE,
703 'CLASS',2,
704 'CONFIG',4,
705 'MODEL',1,
706 'OPTION' ,3,
707 'STANDARD',6,
708 -1),
709 1, dem.inventory_item_id, NULL),
710 inv_salesorder.get_salesorder_for_oeheader(dem.HEADER_ID),
711 DECODE(dem.ITEM_TYPE_CODE,
712 'CLASS',2,
713 'CONFIG',4,
714 'MODEL',1,
715 'OPTION' ,3,
716 'STANDARD',6,
717 -1),
718 dem.ato_line_id,
719 upd.rowid
720 FROM
721 (SELECT DISTINCT line_id
722 FROM MRP_SO_LINES_TEMP
723 WHERE process_status = 3
724 AND request_id = arg_request_id) V,
725 oe_order_lines_all dem,
726 mrp_sales_order_updates upd,
727 mtl_parameters param
728 WHERE NVL(upd.process_status, -1) <> 3
729 AND upd.sales_order_id(+) = dem.line_id
730 AND param.calendar_code IS NOT NULL
731 AND param.calendar_exception_set_id IS NOT NULL
732 AND param.organization_id = decode(dem.cancelled_flag,
733 'Y', upd.organization_id,
734 dem.ship_from_org_id)
735 AND dem.SOLD_TO_ORG_ID IS NOT NULL
736 AND dem.SHIP_TO_ORG_ID IS NOT NULL
737 AND dem.INVOICE_TO_ORG_ID IS NOT NULL
738 AND (NOT EXISTS
739 (SELECT NULL
740 FROM Msc_FORM_QUERY query
741 WHERE query.query_id = dem.line_id))
742 AND DECODE(dem.SOURCE_DOCUMENT_TYPE_ID, 10, 8,
743 DECODE(dem.LINE_CATEGORY_CODE, 'ORDER',2,12))
744 IN (MTL_SALES_ORDER, MTL_INT_SALES_ORDER)
745 AND dem.line_id = V.line_id
746 ORDER BY dem.line_id;
747 /* Bug 1997355
748 * This Order By clause ensures that the
749 * Configured line is retrieved after
750 * the model/option class/option
751 * records
752 */
753
754
755 CURSOR MTL_DEMAND_CUR1 IS
756 SELECT demand.rowid
757 FROM mtl_demand_omoe demand
758 WHERE ((EXISTS
759 (SELECT NULL
760 FROM mrp_sales_order_updates updates
761 WHERE updates.sales_order_id = demand.demand_id
762 AND updates.old_schedule_date = demand.requirement_date
763 AND updates.old_schedule_quantity =
764 demand.primary_uom_quantity
765 AND updates.previous_customer_id = demand.customer_id
766 AND updates.previous_ship_id = demand.ship_to_site_use_id
767 AND updates.previous_bill_id = demand.bill_to_site_use_id
768 AND updates.previous_available_to_mrp =
769 DECODE(demand.available_to_mrp, SYS_YES, 'Y', 'N')
770 AND NVL(updates.current_demand_class, 'A') =
771 NVL(demand.demand_class, 'A')
772 AND updates.process_status <> 3))
773 OR demand.demand_source_type NOT IN
774 (MTL_SALES_ORDER, MTL_INT_SALES_ORDER)
775 OR demand.parent_demand_id IS NOT NULL
776 OR demand.customer_id IS NULL
777 OR demand.ship_to_site_use_id IS NULL
778 OR demand.bill_to_site_use_id IS NULL
779 OR demand.available_to_mrp IS NULL)
780 AND demand.updated_flag = SYS_YES
781 AND rownum <= UPDATE_BATCH_SIZE
782 FOR UPDATE of demand.updated_flag NOWAIT;
783
784 CURSOR calendar is
785 SELECT DISTINCT calendar_code,
786 calendar_exception_set_id,
787 param.organization_id
788 from mtl_parameters param,
789 mrp_sales_order_updates mrp
790 where param.organization_id = mrp.organization_id
791 and mrp.process_status = 1
792 order by calendar_code, calendar_exception_set_id;
793
794 CURSOR cur_model_opt is
795 SELECT line_id
796 FROM oe_order_lines_all
797 WHERE ato_line_id = var_ato_line_id
798 AND line_id <> var_dem_demand_id
799 AND item_type_code <> 'CONFIG'
800 AND header_id = var_dem_header_id;
801
802 /* Bug 1997355.
803 * Changing oe_order_lines to
804 * oe_order_lines_ALL in the cursor
805 * cur_header.
806 */
807
808 /* Bug 2504542.
809 * There could be multiple config lines
810 * for same ato_line_id.
811 * This happens in case an ATO model is
812 * below a PTO model and the total
813 * quantity is not shipped at once.
814 */
815
816 CURSOR config_lines is
817 SELECT line_id
818 FROM oe_order_lines_all
819 WHERE
820 ato_line_id = var_ato_line_id
821 AND item_type_code = 'CONFIG';
822
823 CURSOR cur_header is
824 SELECT header_id from oe_order_lines_all
825 where line_id = var_dem_demand_id;
826
827 /* Bug 2848262
828 * Introduced a new cursor -
829 * so_lines_temp.
830 * This selects and locks records from the
831 * table mrp_so_lines_temp that
832 * can be processed at once.
833 * With this cursor we can remove
834 * the costly exclusive lock statement
835 * on msou.
836 */
837
838 CURSOR so_lines_temp is
839 SELECT mslt.line_id
840 FROM mrp_so_lines_temp mslt
841 WHERE
842 mslt.process_status = 2
843 AND mslt.request_id is NULL
844 AND rownum <= UPDATE_BATCH_SIZE
845 AND NOT EXISTS
846 (SELECT 1
847 FROM mrp_sales_order_updates upd
848 WHERE
849 upd.sales_order_id = mslt.line_id
850 AND upd.process_status = 3)
851 AND NOT EXISTS
852 (SELECT 1
853 FROM mrp_so_lines_temp mslt1
854 WHERE
855 mslt.line_id = mslt1.line_id
856 AND mslt1.process_status = 3)
857 FOR UPDATE OF process_status NOWAIT;
858
859 BEGIN
860
861 var_old_so_cutoff_days := NVL(TO_NUMBER(
862 FND_PROFILE.VALUE('MRP_OLD_SO_CUTOFF_DAYS')), 99999);
863
864 /* -- bug16239252
865 if ( g_om_installed IS NULL ) then
866 g_om_installed := oe_install.get_active_product ;
867 end if;
868
869
870
871 IF g_om_installed = 'OE' THEN
872
873 LOOP
874 rows_updated := 0;
875
876 var_watch_id := mrp_print_pk.start_watch('GEN-updated',
877 arg_request_id,
878 arg_user_id,
879 'ENTITY',
880 'mtl_demand(1)',
881 'N');
882
883
884 /*------------------------------------------------------+
885 | |
886 | Set updated flag to SYS_NO if none of the attributes |
887 | that affect sales order consumption have changed and |
888 | the sales order is already in MRP_SALES_ORDER_UPDATES |
889 | |
890 +------------------------------------------------------
891 LOOP
892 BEGIN
893 OPEN mtl_demand_cur1;
894 EXIT;
895 EXCEPTION
896 WHEN busy THEN
897 NULL;
898 dbms_lock.sleep(5);
899 END;
900 END LOOP;
901
902 LOOP
903 FETCH mtl_demand_cur1 INTO
904 var_dem_rowid;
905 EXIT WHEN mtl_demand_cur1%NOTFOUND;
906
907 UPDATE mtl_demand demand
908 SET demand.updated_flag = SYS_NO
909 WHERE rowid = var_dem_rowid;
910 rows_updated := rows_updated + SQL%ROWCOUNT;
911
912 END LOOP;
913
914 CLOSE mtl_demand_cur1;
915
916 mrp_print_pk.stop_watch(arg_request_id,
917 var_watch_id,
918 rows_updated);
919
920 COMMIT;
921
922 IF rows_updated < UPDATE_BATCH_SIZE THEN
923 EXIT;
924 END IF;
925
926 END LOOP;
927
928 /* ------------------------------------------------------+
929 | If Installed product is OE, then RETURN !!! |
930 + ------------------------------------------------------
931 return;
932
933 END IF;*/
934
935 -- Determine whether this is the first time the planning manager is run.
936
937 /*2285868*/
938
939 var_first_time :=nvl(FND_PROFILE.VALUE('MRP_PLNG_MGR_FIRST_TIME'),'Y');
940
941 var_debug := FND_PROFILE.VALUE('MRP_DEBUG') = 'Y';
942 /*
943 mrp_print_pk.mrprint('First time : '|| var_first_time,
944 arg_request_id, arg_user_id);
945
946 */
947
948 LOOP
949
950 rows_updated := 0;
951
952 IF (var_first_time = 'N') Then
953
954 /*
955 var_watch_id := mrp_print_pk.start_watch('GEN-updated',
956 arg_request_id,
957 arg_user_id,
958 'ENTITY',
959 'mrp_so_lines_temp',
960 'N');
961
962 */
963 -- Update the interface table
964 -- mrp_so_lines_temp.
965 -- Keep looping until the records in mslt
966 -- can be locked.
967
968 LOOP
969 BEGIN
970 OPEN so_lines_temp;
971 EXIT;
972 EXCEPTION
973 WHEN busy THEN
974 NULL;
975 dbms_lock.sleep(5);
976 END;
977 END LOOP;
978
979
980
981 FETCH so_lines_temp BULK COLLECT INTO
982 line_id_arr;
983
984
985 IF (line_id_arr.COUNT > 0) THEN
986
987 FORALL i in line_id_arr.FIRST..line_id_arr.LAST
988 UPDATE mrp_so_lines_temp
989 SET process_status = 3,
990 request_id = arg_request_id,
991 last_update_login = arg_user_id,
992 last_update_date = sysdate
993 WHERE
994 line_id = line_id_arr(i)
995 and process_status = 2
996 and request_id IS NULL;
997
998 rows_updated := rows_updated + SQL%ROWCOUNT;
999
1000 END IF;
1001 CLOSE so_lines_temp;
1002
1003 line_id_arr.DELETE;
1004 /*
1005 mrp_print_pk.stop_watch(arg_request_id,
1006 var_watch_id,
1007 rows_updated);
1008 */
1009
1010 COMMIT;
1011
1012 IF (rows_updated = 0) THEN
1013
1014 EXIT; -- To take care of the last iteration
1015 -- in the case when NOT first time.
1016 -- Execute the code outside of the
1017 -- main loop.
1018 END IF;
1019
1020 IF (rows_updated < UPDATE_BATCH_SIZE) THEN
1021 var_break_loop := SYS_YES;
1022 END IF;
1023
1024 ELSE
1025 -- This is the first time the planning
1026 -- manager is running after creation of
1027 -- the profile option OR it has been
1028 -- reset to 'Yes'
1029 DELETE FROM mrp_so_lines_temp;
1030
1031 -- BUG 2848262
1032 -- Lock the table msou only if
1033 -- the planning manager is being
1034 -- run for the first time.
1035
1036
1037 /*
1038 var_watch_id := mrp_print_pk.start_watch('GEN-LOCK TABLE',
1039 arg_request_id,
1040 arg_user_id,
1041 'TABLE',
1042 'mrp_sales_order_updates',
1043 'N',
1044 'DATE',
1045 to_char(sysdate,'dd-mon hh24:mi:ss'),
1046 'Y');
1047
1048 */
1049
1050 LOCK TABLE mrp_sales_order_updates IN SHARE ROW EXCLUSIVE MODE;
1051
1052
1053 /*
1054 mrp_print_pk.stop_watch(arg_request_id,
1055 var_watch_id,
1056 rows_updated);
1057 */
1058
1059 END IF;
1060
1061
1062 /*
1063 var_watch_id := mrp_print_pk.start_watch('GEN-SELECTING',
1064 arg_request_id,
1065 arg_user_id,
1066 'ENTITY',
1067 'mrp_sales_order_updates',
1068 'N');
1069 */
1070
1071 LOOP
1072 BEGIN
1073 IF (var_first_time = 'Y') THEN
1074 OPEN mtl_demand_cur_first;
1075 ELSE
1076 OPEN mtl_demand_cur_next;
1077 END IF;
1078 EXIT;
1079 EXCEPTION
1080 WHEN busy THEN
1081 NULL;
1082 dbms_lock.sleep(5);
1083 END;
1084 END LOOP;
1085
1086 /*----------------------------------------------+
1087 | |
1088 | Set to IN_PROCESS all rows that are for sales |
1089 | orders and if the rows are not in MRP_SALES |
1090 | ORDER_UPDATES or are not IN_PROCESS |
1091 | |
1092 +----------------------------------------------*/
1093 counter := 0;
1094 counter1 := 0;
1095
1096 insert_count := 0;
1097 update_count := 0;
1098 to_insert := 0;
1099 to_update := 0;
1100
1101 LOOP
1102
1103 IF (var_first_time = 'Y') THEN
1104 FETCH mtl_demand_cur_first INTO
1105 var_dem_inventory_item_id,
1106 var_dem_demand_id,
1107 var_dem_organization_id,
1108 var_dem_user_line_num,
1109 var_dem_requirement_date,
1110 var_dem_primary_uom_quantity,
1111 var_dem_customer_id,
1112 var_dem_ship_to_site_use_id,
1113 var_dem_bill_to_site_use_id,
1114 var_dem_available_to_mrp,
1115 var_dem_demand_class,
1116 var_dem_completed_quantity,
1117 var_dem_ordered_item,
1118 var_dem_source_header_id,
1119 var_dem_demand_type,
1120 var_ato_line_id,
1121 var_upd_rowid;
1122
1123 EXIT WHEN mtl_demand_cur_first%NOTFOUND;
1124 ELSE
1125 FETCH mtl_demand_cur_next INTO
1126 var_dem_inventory_item_id,
1127 var_dem_demand_id,
1128 var_dem_organization_id,
1129 var_dem_user_line_num,
1130 var_dem_requirement_date,
1131 var_dem_primary_uom_quantity,
1132 var_dem_customer_id,
1133 var_dem_ship_to_site_use_id,
1134 var_dem_bill_to_site_use_id,
1135 var_dem_available_to_mrp,
1136 var_dem_demand_class,
1137 var_dem_completed_quantity,
1138 var_dem_ordered_item,
1139 var_dem_source_header_id,
1140 var_dem_demand_type,
1141 var_ato_line_id,
1142 var_upd_rowid;
1143
1144 EXIT WHEN mtl_demand_cur_next%NOTFOUND;
1145 END IF;
1146
1147
1148 /* Bug 1997355
1149 * Need to check for the case when rescheduling a
1150 * an ATO Model without delinking from the configured
1151 * item that has been created already.
1152 */
1153
1154 /* Bug 2504542.
1155 * Need to do the following, only if the planning
1156 * manager is NOT running for the first time.
1157 */
1158
1159 IF ((var_ato_line_id IS NOT NULL) AND
1160 (var_dem_demand_type <> 4) AND
1161 (var_first_time = 'N')) THEN
1162
1163 found_config_item := SYS_NO;
1164 config_lines_counter := 0;
1165
1166 FOR k IN 1..max_ato_model_config LOOP
1167 IF (ato_model_config_arr(k).ato_line_id = var_ato_line_id) THEN
1168 found_config_item := SYS_YES;
1169 config_line_id := ato_model_config_arr(k).config_line_id;
1170 config_lines_counter := k;
1171 EXIT;
1172 END IF;
1173 END LOOP;
1174
1175 IF (found_config_item = SYS_NO) THEN
1176 /* Need to check if the configured item is created.
1177 */
1178 OPEN config_lines;
1179 LOOP
1180
1181 FETCH config_lines INTO config_line_id;
1182 EXIT WHEN config_lines%NOTFOUND;
1183
1184 config_lines_counter := config_lines_counter + 1;
1185 max_ato_model_config := max_ato_model_config +1;
1186 ato_model_config_arr(max_ato_model_config).ato_line_id :=
1187 var_ato_line_id;
1188 ato_model_config_arr(max_ato_model_config).config_line_id :=
1189 config_line_id;
1190
1191 /* Config Item exists. Check if it is a part
1192 * of the request set.
1193 * We need to make sure that the config item
1194 * Line is processed.
1195 *
1196 * Because of the order in which we are
1197 * fetching records from the cursor
1198 * mtl_demand_cur_next, we execute this piece
1199 * of code only for the ATO model.
1200 */
1201
1202 BEGIN
1203
1204 SELECT /*+ INDEX (t mrp_so_lines_n2) */ 1
1205 INTO config_item_exists
1206 FROM mrp_so_lines_temp t
1207 WHERE process_status = 3
1208 AND line_id = config_line_id
1209 AND request_id = arg_request_id
1210 AND ROWNUM = 1;
1211
1212 EXCEPTION WHEN NO_DATA_FOUND THEN
1213 config_item_exists := 0;
1214 END;
1215
1216 IF (config_item_exists = 0) THEN
1217
1218 INSERT INTO mrp_so_lines_temp
1219 (
1220 LAST_UPDATED_BY ,
1221 LAST_UPDATE_DATE,
1222 CREATION_DATE,
1223 CREATED_BY,
1224 LAST_UPDATE_LOGIN,
1225 line_id,
1226 process_status
1227 )
1228 VALUES
1229 (
1230 arg_user_id,
1231 SYSDATE,
1232 SYSDATE,
1233 arg_user_id,
1234 arg_user_id,
1235 config_line_id,
1236 2 -- To Be Processed.
1237 ) ;
1238 END IF;
1239
1240 END LOOP;
1241 CLOSE config_lines;
1242
1243 /* If a configured line has not been created,
1244 * then we need to insert -1 as the
1245 * configured_line id in the array
1246 * ato_model_config_arr.
1247 */
1248 IF (config_lines_counter = 0 ) THEN
1249 config_line_id := -1;
1250 max_ato_model_config := max_ato_model_config +1;
1251 ato_model_config_arr(max_ato_model_config).ato_line_id :=
1252 var_ato_line_id;
1253 ato_model_config_arr(max_ato_model_config).config_line_id :=
1254 config_line_id;
1255
1256 END IF;
1257
1258 END IF;
1259 IF (config_line_id <> -1) THEN
1260 /* We should insert/update 0 for the
1261 * new_schedule_quantity in the table
1262 * mrp_sales_order_updates where the line
1263 * being processed is of model/option class
1264 * or for the option item
1265 */
1266 var_dem_primary_uom_quantity := 0;
1267
1268 END IF;
1269
1270
1271 END IF;
1272
1273 IF var_upd_rowid IS NOT NULL THEN
1274
1275 /* 2463192 - Removed the check on completed_quantity since this does not
1276 affect forecast consumption. */
1277
1278 to_update := to_update + 1;
1279
1280 UPDATE mrp_sales_order_updates upd
1281 SET
1282 last_update_date = SYSDATE,
1283 last_updated_by = arg_user_id,
1284 last_update_login = -1,
1285 process_status = 1,
1286 inventory_item_id = var_dem_inventory_item_id,
1287 sales_order_id = var_dem_demand_id,
1288 organization_id = nvl(var_dem_organization_id,
1289 organization_id),
1290 line_num = var_dem_user_line_num,
1291 new_schedule_date = nvl(var_dem_requirement_date,
1292 new_schedule_date),
1293 new_schedule_quantity = var_dem_primary_uom_quantity,
1294 current_customer_id = var_dem_customer_id,
1295 current_ship_id = var_dem_ship_to_site_use_id,
1296 current_bill_id = var_dem_bill_to_site_use_id,
1297 current_available_to_mrp= var_dem_available_to_mrp,
1298 current_demand_class = var_dem_demand_class,
1299 completed_quantity = var_dem_completed_quantity,
1300 request_id = NULL,
1301 error_message = NULL
1302 WHERE upd.rowid = var_upd_rowid
1303 AND ((new_schedule_date <> var_dem_requirement_date)
1304 OR (new_schedule_quantity <> var_dem_primary_uom_quantity)
1305 OR (current_customer_id <> var_dem_customer_id)
1306 OR (current_ship_id <> var_dem_ship_to_site_use_id)
1307 OR (current_bill_id <> var_dem_bill_to_site_use_id)
1308 OR (current_available_to_mrp <> var_dem_available_to_mrp)
1309 OR (NVL(current_demand_class, '734jkhJK24') <>
1310 NVL(var_dem_demand_class, '734jkhJK24')))
1311 AND inventory_item_id = var_dem_inventory_item_id
1312 AND organization_id = Nvl(var_dem_organization_id,
1313 organization_id);
1314
1315 --
1316 -- update the old values so that this row is not picked up again by
1317 -- the mtl_Demand_cur loop
1318
1319 update_count := update_count + sql%rowcount;
1320 if (sql%rowcount = 0) then
1321
1322 -- Changes for the bug 2296197.
1323 -- Need to check if the Shipping Warehouse (organization_id)
1324 -- for the sales order line has been changed.
1325
1326 IF (var_dem_organization_id IS NOT NULL) THEN
1327 -- Check if a record for the new organization_id
1328 -- exists in the table already.
1329
1330 SELECT COUNT(*)
1331 INTO new_org_rec_count
1332 FROM mrp_sales_order_updates
1333 WHERE
1334 sales_order_id = var_dem_demand_id
1335 AND inventory_item_id = var_dem_inventory_item_id
1336 AND organization_id = var_dem_organization_id;
1337
1338 IF (new_org_rec_count = 0) THEN
1339 -- There is no record for the new org,
1340 -- Need to insert a record for the new org.
1341
1342 IF var_dem_available_to_mrp = 'Y' THEN
1343
1344 INSERT INTO mrp_sales_order_updates
1345 (update_seq_num ,
1346 last_update_date,
1347 last_updated_by,
1348 creation_date,
1349 created_by,
1350 last_update_login,
1351 process_status,
1352 inventory_item_id,
1353 sales_order_id,
1354 organization_id,
1355 line_num,
1356 new_schedule_date,
1357 old_schedule_date,
1358 new_schedule_quantity,
1359 old_schedule_quantity,
1360 current_customer_id,
1361 previous_customer_id,
1362 current_ship_id,
1363 previous_ship_id,
1364 current_bill_id,
1365 previous_bill_id,
1366 current_territory_id,
1367 previous_territory_id,
1368 current_available_to_mrp,
1369 previous_available_to_mrp,
1370 current_demand_class,
1371 previous_demand_class,
1372 ordered_item_id,
1373 completed_quantity)
1374 VALUES
1375 (mrp_sales_order_updates_s.nextval,
1376 SYSDATE,
1377 arg_user_id,
1378 SYSDATE,
1379 arg_user_id,
1380 -1,
1381 1,
1382 var_dem_inventory_item_id,
1383 var_dem_demand_id,
1384 var_dem_organization_id,
1385 var_dem_user_line_num,
1386 var_dem_requirement_date,
1387 NULL,
1388 var_dem_primary_uom_quantity,
1389 NULL,
1390 var_dem_customer_id,
1391 NULL,
1392 var_dem_ship_to_site_use_id,
1393 NULL,
1394 var_dem_bill_to_site_use_id,
1395 NULL,
1396 var_dem_source_header_id,
1397 var_dem_source_header_id,
1398 var_dem_available_to_mrp,
1399 NULL,
1400 var_dem_demand_class,
1401 NULL,
1402 var_dem_ordered_item,
1403 var_dem_completed_quantity);
1404
1405 insert_count := insert_count + sql%rowcount;
1406 END IF;
1407
1408 END IF;
1409
1410 -- Need to update the record for the existing
1411 -- Org(s) to 0.
1412
1413 UPDATE mrp_sales_order_updates
1414 SET
1415 last_update_date = SYSDATE,
1416 last_updated_by = arg_user_id,
1417 last_update_login = -1,
1418 process_status = 1,
1419 new_schedule_quantity = 0,
1420 request_id = NULL,
1421 error_message = NULL
1422 WHERE
1423 sales_order_id = var_dem_demand_id
1424 AND ( inventory_item_id <> var_dem_inventory_item_id
1425 OR organization_id <> var_dem_organization_id)
1426 AND new_schedule_quantity <> 0;
1427
1428
1429 update_count := update_count + sql%rowcount;
1430 END IF;
1431 -- Commented out the following update statement
1432 -- for the bug 2296197
1433
1434 --update mrp_sales_order_updates
1435 -- set old_schedule_date=new_schedule_date,
1436 -- old_schedule_quantity=new_schedule_quantity,
1437 -- previous_customer_id = current_customer_id,
1438 -- previous_ship_id = current_ship_id,
1439 -- previous_bill_id = current_bill_id
1440 -- where rowid = var_upd_rowid;
1441
1442 end if;
1443
1444
1445 ELSIF var_dem_available_to_mrp = 'Y' THEN
1446 INSERT INTO mrp_sales_order_updates
1447 (update_seq_num ,
1448 last_update_date,
1449 last_updated_by,
1450 creation_date,
1451 created_by,
1452 last_update_login,
1453 process_status,
1454 inventory_item_id,
1455 sales_order_id,
1456 organization_id,
1457 line_num,
1458 new_schedule_date,
1459 old_schedule_date,
1460 new_schedule_quantity,
1461 old_schedule_quantity,
1462 current_customer_id,
1463 previous_customer_id,
1464 current_ship_id,
1465 previous_ship_id,
1466 current_bill_id,
1467 previous_bill_id,
1468 current_territory_id,
1469 previous_territory_id,
1470 current_available_to_mrp,
1471 previous_available_to_mrp,
1472 current_demand_class,
1473 previous_demand_class,
1474 ordered_item_id,
1475 completed_quantity)
1476 VALUES
1477 (mrp_sales_order_updates_s.nextval,
1478 SYSDATE,
1479 arg_user_id,
1480 SYSDATE,
1481 arg_user_id,
1482 -1,
1483 1,
1484 var_dem_inventory_item_id,
1485 var_dem_demand_id,
1486 var_dem_organization_id,
1487 var_dem_user_line_num,
1488 var_dem_requirement_date,
1489 NULL,
1490 var_dem_primary_uom_quantity,
1491 NULL,
1492 var_dem_customer_id,
1493 NULL,
1494 var_dem_ship_to_site_use_id,
1495 NULL,
1496 var_dem_bill_to_site_use_id,
1497 NULL,
1498 var_dem_source_header_id,
1499 var_dem_source_header_id,
1500 var_dem_available_to_mrp,
1501 NULL,
1502 var_dem_demand_class,
1503 NULL,
1504 var_dem_ordered_item,
1505 var_dem_completed_quantity);
1506
1507
1508 insert_count := insert_count + sql%rowcount;
1509 END IF;
1510
1511 /*
1512 * change the quantitites for model,options to 0 since
1513 * config item is being created
1514 */
1515 if(var_dem_demand_type = 4) then
1516 /* Scope for further optimization.
1517 * If the ATO model and Configured item are
1518 * in the same set of records being processed
1519 * then this update of mrp_sales_order_updates
1520 * is redundant as we have already inserted/
1521 * updated the new_schedule_quantity to 0
1522 */
1523
1524 open cur_header;
1525 fetch cur_header into var_dem_header_id;
1526 close cur_header;
1527
1528 OPEN CUR_MODEL_OPT;
1529
1530 LOOP
1531 FETCH cur_model_opt INTO line_ids_except_config;
1532 EXIT WHEN CUR_MODEL_OPT%NOTFOUND;
1533
1534 /* 2463192 - update the record only if new_schedule_quantity <> 0 */
1535
1536 UPDATE mrp_sales_order_updates upd
1537 SET
1538 last_update_date = SYSDATE,
1539 last_updated_by = arg_user_id,
1540 last_update_login = -1,
1541 process_status = 1,
1542 new_schedule_quantity = 0,
1543 current_available_to_mrp = 'N',
1544 request_id = NULL,
1545 error_message = NULL
1546 WHERE upd.sales_order_id = line_ids_except_config
1547 and upd.new_schedule_quantity <> 0;
1548
1549 update_count := update_count + sql%rowcount;
1550 END LOOP;
1551
1552 CLOSE CUR_MODEL_OPT;
1553
1554 end if;
1555
1556 /*if ( g_om_installed IS NULL ) then
1557 g_om_installed := oe_install.get_active_product ;
1558 end if;
1559
1560 IF g_om_installed = 'OE' THEN
1561
1562 UPDATE mtl_demand
1563 SET updated_flag = SYS_NO
1564 WHERE rowid = var_dem_rowid;
1565
1566 END IF;*/ --bug16239252
1567
1568 /* For Processing a batch of records at a time */
1569 IF (var_first_time = 'N') THEN
1570 counter1 := counter1 + 1;
1571 line_id_arr(counter1) := var_dem_demand_id;
1572 END IF;
1573
1574 END LOOP;
1575
1576 IF (var_first_time = 'Y') THEN
1577 counter := counter + mtl_demand_cur_first%ROWCOUNT;
1578 CLOSE mtl_demand_cur_first;
1579
1580 IF counter < UPDATE_BATCH_SIZE THEN
1581 var_break_loop := SYS_YES;
1582 END IF;
1583 ELSE
1584 CLOSE mtl_demand_cur_next;
1585 END IF;
1586
1587
1588 IF (var_first_time = 'N') THEN
1589 IF (var_break_loop = SYS_NO) THEN
1590 -- Update the temp table mrp_so_lines_temp
1591 -- set the line ids that have been processed
1592 -- already to process status 5.
1593 -- For the batch of rows processed.
1594 FORALL i IN 1..counter1
1595 UPDATE /*+ INDEX (t mrp_so_lines_n2) */ mrp_so_lines_temp t
1596 SET process_status = 5,
1597 last_update_login = arg_user_id,
1598 last_update_date = sysdate
1599 WHERE
1600 process_status = 3
1601 AND request_id = arg_request_id
1602 AND line_id = line_id_arr(i);
1603 END IF;
1604 END IF;
1605
1606
1607 /*
1608
1609 mrp_print_pk.stop_watch(arg_request_id,
1610 var_watch_id,
1611 counter);
1612
1613 IF var_debug THEN
1614
1615 mrp_print_pk.mrprint('Rows to update : '|| to_char(to_update),
1616 arg_request_id, arg_user_id);
1617
1618
1619 var_watch_id := mrp_print_pk.start_watch('GEN-INSERTED ROWS',
1620 arg_request_id,
1621 arg_user_id,
1622 'NUMBER',
1623 to_char(insert_count),
1624 'N',
1625 'TABLE',
1626 'mrp_sales_order_updates',
1627 'N');
1628
1629 mrp_print_pk.stop_watch(arg_request_id,
1630 var_watch_id);
1631
1632 var_watch_id := mrp_print_pk.start_watch('GEN-updated',
1633 arg_request_id,
1634 arg_user_id,
1635 'ENTITY',
1636 'mrp_sales_order_updates',
1637 'N');
1638
1639 mrp_print_pk.stop_watch(arg_request_id,
1640 var_watch_id,
1641 update_count);
1642
1643 END IF;
1644
1645 */
1646 /*------------------------------------------------------------+
1647 | Update sales order dates to the last valid workday for |
1648 | sales orders that lie outside the calendar date |
1649 +------------------------------------------------------------*/
1650 OPEN calendar;
1651 prev_cal_code := 'aggd4885-23453';
1652 prev_except_set_id := '-23453';
1653
1654
1655 LOOP
1656 FETCH calendar into var_cal_code,
1657 var_except_set_id,
1658 var_org_id;
1659
1660 EXIT WHEN calendar%NOTFOUND;
1661
1662 IF prev_cal_code <> var_cal_code OR var_except_set_id <>
1663 prev_except_set_id
1664 THEN
1665
1666
1667 SELECT min(calendar_date), max(calendar_date)
1668 INTO var_min_cal_date,
1669 var_max_cal_date
1670 FROM bom_calendar_dates
1671 WHERE calendar_code = var_cal_code
1672 AND exception_set_id = var_except_set_id;
1673
1674 prev_cal_code := var_cal_code;
1675 prev_except_set_id := var_except_set_id;
1676 END IF;
1677
1678 /*
1679 IF var_debug THEN
1680
1681 mrp_print_pk.mrprint('Processing Calendar: '|| var_cal_code,
1682 arg_request_id, arg_user_id);
1683 mrp_print_pk.mrprint('Min Date : '||
1684 to_char(var_min_cal_date,'DD-MON-RR'),
1685 arg_request_id, arg_user_id);
1686 mrp_print_pk.mrprint('Max Date : '||
1687 to_char(var_max_cal_date,'DD-MON-RR'),
1688 arg_request_id, arg_user_id);
1689
1690 END IF;
1691 */
1692 insert into msc_form_query
1693 (query_id ,
1694 last_update_date,
1695 last_updated_by,
1696 creation_date,
1697 created_by,
1698 last_update_login)
1699 select sales_order_id ,
1700 SYSDATE,
1701 arg_user_id,
1702 SYSDATE,
1703 arg_user_id,
1704 -1
1705 from mrp_sales_order_updates
1706 where organization_id = var_org_id
1707 and process_status = 1
1708 and (new_schedule_date < var_min_cal_date
1709 or new_schedule_date > var_max_cal_date);
1710
1711 /*
1712 IF var_debug THEN
1713
1714 insert_count := sql%rowcount;
1715 var_watch_id := mrp_print_pk.start_watch('GEN-INSERTED ROWS',
1716 arg_request_id,
1717 arg_user_id,
1718 'NUMBER',
1719 to_char(insert_count),
1720 'N',
1721 'TABLE',
1722 'msc_form_query',
1723 'N');
1724
1725 mrp_print_pk.stop_watch(arg_request_id,
1726 var_watch_id);
1727
1728 END IF;
1729 */
1730 /*
1731 END LOOP;
1732 */
1733 /*
1734 IF var_debug THEN
1735
1736
1737 mrp_print_pk.mrprint('Processed '|| to_char(calendar%rowcount)
1738 ||' orgs',
1739 arg_request_id, arg_user_id);
1740
1741 END IF;
1742 */
1743
1744 /*
1745 CLOSE calendar;
1746
1747 */
1748 /*
1749 IF var_debug THEN
1750
1751
1752 var_watch_id := mrp_print_pk.start_watch('GEN-updated',
1753 arg_request_id,
1754 arg_user_id,
1755 'ENTITY',
1756 'mrp_sales_order_updates',
1757 'N');
1758 END IF;
1759 */
1760 UPDATE mrp_sales_order_updates upd
1761 SET old_schedule_date = GREATEST(var_min_cal_date,
1762 LEAST(var_max_cal_date, old_schedule_date)),
1763 new_schedule_date = GREATEST(var_min_cal_date,
1764 LEAST(var_max_cal_date, new_schedule_date)),
1765 process_status = 2
1766 WHERE process_status = 1
1767 AND organization_id = var_org_id ;
1768
1769 /*
1770 IF var_debug THEN
1771
1772
1773 mrp_print_pk.stop_watch(arg_request_id,
1774 var_watch_id,
1775 SQL%ROWCOUNT);
1776
1777 END IF;
1778 */
1779 END LOOP;
1780
1781 CLOSE calendar;
1782
1783 COMMIT;
1784
1785 IF var_break_loop = SYS_YES THEN
1786 EXIT;
1787 END IF;
1788
1789 END LOOP;
1790
1791 IF (var_first_time = 'N') Then
1792 -- Update the interface table
1793 -- mrp_so_lines_temp.
1794 -- For the last batch of rows.
1795 FORALL i IN 1..counter1
1796 UPDATE /*+ INDEX (t mrp_so_lines_n2) */ mrp_so_lines_temp t
1797 SET process_status = 5,
1798 last_update_login = arg_user_id,
1799 last_update_date = sysdate
1800 WHERE
1801 process_status = 3
1802 AND request_id = arg_request_id
1803 AND line_id = line_id_arr(i);
1804
1805 /* Bug 1997355.
1806 * Need to handle a case when a sales order line is
1807 * deleted OR a configured item is delinked from it's
1808 * model item.
1809 * In this case, there will be some records in the table
1810 * mrp_so_lines_temp, with the process_status as 3
1811 * but their corresponding record is not found in the
1812 * table oe_order_lines_all as they have been deleted.
1813 */
1814 UPDATE mrp_sales_order_updates upd
1815 SET
1816 last_update_date = SYSDATE,
1817 last_updated_by = arg_user_id,
1818 last_update_login = -1,
1819 process_status = 2,
1820 new_schedule_quantity = 0,
1821 current_available_to_mrp = 'N', -- BUG 3445569
1822 request_id = NULL,
1823 error_message = NULL
1824 WHERE
1825 sales_order_id IN
1826 (SELECT line_id
1827 FROM mrp_so_lines_temp
1828 WHERE
1829 process_status = 3
1830 AND request_id = arg_request_id) ;
1831
1832 /* Now Update these lines in the table
1833 * mrp_so_lines_temp to processed.
1834 */
1835
1836 UPDATE mrp_so_lines_temp
1837 SET process_status = 5,
1838 last_update_login = arg_user_id,
1839 last_update_date = sysdate
1840 WHERE
1841 process_status = 3
1842 AND request_id = arg_request_id ;
1843
1844
1845 ELSE
1846 -- Set the profile option MRP Planning Manager
1847 -- First Time to No.
1848 pvalue := fnd_profile.save('MRP_PLNG_MGR_FIRST_TIME', 'N', 'SITE');
1849 END IF;
1850 COMMIT;
1851
1852 END compute_sales_order_changes;
1853
1854 -- ********************** update_sales_orders *************************
1855 PROCEDURE update_sales_orders(arg_request_id IN NUMBER,
1856 arg_user_id IN NUMBER) IS
1857
1858 CURSOR lock_mtl_demand_cur IS
1859 SELECT demand.rowid,
1860 updates1.new_schedule_quantity,
1861 updates1.new_schedule_date
1862 FROM mtl_demand demand,
1863 mrp_sales_order_updates updates1
1864 WHERE updates1.sales_order_id = demand.demand_id
1865 AND updates1.request_id = arg_request_id
1866 AND updates1.process_status = 3
1867 AND updates1.error_message IS NULL
1868 FOR UPDATE OF demand.mrp_date NOWAIT;
1869
1870 var_rowid ROWID;
1871 var_date DATE;
1872 var_quantity NUMBER;
1873 rows_updated NUMBER := 0;
1874
1875 busy EXCEPTION;
1876
1877 PRAGMA EXCEPTION_INIT(busy, -54);
1878
1879 BEGIN
1880
1881 /*if ( g_om_installed IS NULL ) then
1882 g_om_installed := oe_install.get_active_product ;
1883 end if;
1884
1885 IF g_om_installed = 'OE' THEN
1886
1887 var_watch_id := mrp_print_pk.start_watch('GEN-updated',
1888 arg_request_id,
1889 arg_user_id,
1890 'ENTITY',
1891 'mtl_demand',
1892 'N');
1893 LOOP
1894 BEGIN
1895 OPEN lock_mtl_demand_cur;
1896 EXIT;
1897 EXCEPTION
1898 WHEN busy THEN
1899 NULL;
1900 dbms_lock.sleep(5);
1901 END;
1902 END LOOP;
1903
1904 LOOP
1905 FETCH lock_mtl_demand_cur INTO
1906 var_rowid,
1907 var_quantity,
1908 var_date;
1909 EXIT WHEN lock_mtl_demand_cur%NOTFOUND;
1910
1911 UPDATE mtl_demand demand
1912 SET demand.mrp_date = var_date,
1913 demand.mrp_quantity = var_quantity
1914 WHERE rowid = var_rowid;
1915
1916 rows_updated := rows_updated + SQL%ROWCOUNT;
1917
1918 END LOOP;
1919
1920 CLOSE lock_mtl_demand_cur;
1921
1922 mrp_print_pk.stop_watch(arg_request_id,
1923 var_watch_id,
1924 rows_updated);
1925
1926 END IF;*/
1927
1928 var_watch_id := mrp_print_pk.start_watch('GEN-updated',
1929 arg_request_id,
1930 arg_user_id,
1931 'ENTITY',
1932 'mrp_sales_order_updates',
1933 'N');
1934 UPDATE mrp_sales_order_updates
1935 SET old_schedule_date = new_schedule_date,
1936 old_schedule_quantity = new_schedule_quantity,
1937 previous_customer_id = current_customer_id,
1938 previous_ship_id = current_ship_id,
1939 previous_bill_id = current_bill_id,
1940 previous_demand_class = current_demand_class,
1941 previous_territory_id = current_territory_id,
1942 previous_available_to_mrp = current_available_to_mrp,
1943 process_status = 5
1944 WHERE request_id = arg_request_id
1945 AND process_status = 3
1946 AND error_message IS NULL;
1947
1948 mrp_print_pk.stop_watch(arg_request_id,
1949 var_watch_id,
1950 SQL%ROWCOUNT);
1951
1952 END update_sales_orders;
1953
1954 -- ********************** create_forecast_items *************************
1955 PROCEDURE create_forecast_items(
1956 arg_request_id IN NUMBER,
1957 arg_user_id IN NUMBER,
1958 arg_desig IN VARCHAR2) IS
1959 BEGIN
1960
1961 var_watch_id := mrp_print_pk.start_watch(
1962 'GEN-inserting',
1963 arg_request_id,
1964 arg_user_id,
1965 'ENTITY',
1966 'E_ITEMS',
1967 'Y',
1968 'TABLE',
1969 'mrp_forecast_items',
1970 'N');
1971
1972 INSERT INTO mrp_forecast_items
1973 (
1974 inventory_item_id,
1975 organization_id,
1976 forecast_designator,
1977 last_update_date,
1978 last_updated_by,
1979 creation_date,
1980 created_by,
1981 last_update_login
1982 )
1983 SELECT /*+ index (dates MRP_FORECAST_DATES_N1) */
1984 DISTINCT inventory_item_id,
1985 organization_id,
1986 forecast_designator,
1987 SYSDATE,
1988 1,
1989 SYSDATE,
1990 1,
1991 -1
1992 FROM mrp_forecast_dates dates
1993 WHERE NOT EXISTS
1994 (SELECT NULL
1995 FROM mrp_forecast_items items
1996 WHERE items.organization_id = dates.organization_id
1997 AND items.forecast_designator = dates.forecast_designator
1998 AND items.inventory_item_id = dates.inventory_item_id);
1999
2000 mrp_print_pk.stop_watch(arg_request_id,
2001 var_watch_id,
2002 SQL%ROWCOUNT);
2003
2004 END create_forecast_items;
2005
2006 -- ********************** update_forecast_desc_flex *************************
2007 PROCEDURE update_forecast_desc_flex(arg_row_count IN OUT NOCOPY NUMBER) IS
2008 BEGIN
2009
2010 /*------------------------------------------------------+
2011 | Copy comments, desc flex, project, and line reference |
2012 | For line reference, copy only if for the same org |
2013 +------------------------------------------------------*/
2014
2015 UPDATE mrp_forecast_dates dates1
2016 SET (ddf_context, attribute_category,
2017 attribute1, attribute2, attribute3,
2018 attribute4, attribute5, attribute6,
2019 attribute7, attribute8, attribute9,
2020 attribute10, attribute11, attribute12,
2021 attribute13, attribute14, attribute15,
2022 comments, line_id, project_id, task_id)
2023 = (SELECT dates2.ddf_context, dates2.attribute_category,
2024 dates2.attribute1, dates2.attribute2, dates2.attribute3,
2025 dates2.attribute4, dates2.attribute5, dates2.attribute6,
2026 dates2.attribute7, dates2.attribute8, dates2.attribute9,
2027 dates2.attribute10, dates2.attribute11,
2028 dates2.attribute12, dates2.attribute13,
2029 dates2.attribute14, dates2.attribute15,
2030 dates2.comments,
2031 DECODE(dates2.organization_id,
2032 dates1.organization_id, dates2.line_id, NULL),
2033 DECODE(mtl.project_reference_enabled,
2034 1, dates2.project_id,
2035 NULL),
2036 DECODE(mtl.project_reference_enabled,
2037 1, DECODE(project_control_level,
2038 2, dates2.task_id,
2039 NULL),
2040 NULL)
2041 FROM mtl_parameters mtl,
2042 mrp_forecast_dates dates2
2043 WHERE dates2.transaction_id = dates1.old_transaction_id
2044 AND dates1.organization_id = mtl.organization_id)
2045 WHERE dates1.old_transaction_id >= 0;
2046
2047 arg_row_count := SQL%ROWCOUNT;
2048
2049 UPDATE mrp_forecast_dates
2050 SET to_update = null
2051 WHERE old_transaction_id >= 0;
2052
2053 END update_forecast_desc_flex;
2054
2055 -- ********************** update_schedule_desc_flex *************************
2056 PROCEDURE update_schedule_desc_flex(arg_row_count IN OUT NOCOPY NUMBER,
2057 arg_schedule_count IN NUMBER,
2058 arg_forecast_count IN NUMBER,
2059 arg_so_count IN NUMBER,
2060 arg_interorg_count IN NUMBER) IS
2061 BEGIN
2062
2063
2064 /*--------------------------------------------------------------+
2065 | BUG # 2639914 |
2066 | Execute the Update statement based on the value of the new |
2067 | input parameters. |
2068 | Execute the update only if the corresponding counter > 0 |
2069 +-------------------------------------------------------------*/
2070
2071 arg_row_count := 0;
2072
2073 if (nvl(arg_forecast_count,1) > 0) then
2074
2075 /*-----------------------------------------------------------------------+
2076 | Copy only comments, project, and line reference for fcst to sched load |
2077 | For line reference, copy only if for the same org |
2078 | Do not copy end_item_unit_number because it is not stored on forecast |
2079 +-----------------------------------------------------------------------*/
2080
2081 UPDATE mrp_schedule_dates dates
2082 SET (schedule_comments, line_id, project_id, task_id)
2083 = (SELECT fc_dates.comments,
2084 DECODE(fc_dates.organization_id,
2085 dates.organization_id, fc_dates.line_id, NULL),
2086 DECODE(mtl.project_reference_enabled,
2087 1, fc_dates.project_id,
2088 NULL),
2089 DECODE(mtl.project_reference_enabled,
2090 1, DECODE(mtl.project_control_level,
2091 2, fc_dates.task_id,
2092 NULL),
2093 NULL)
2094 FROM mtl_parameters mtl,
2095 mrp_forecast_dates fc_dates
2096 WHERE fc_dates.transaction_id = dates.old_transaction_id
2097 AND dates.organization_id = mtl.organization_id)
2098 WHERE dates.old_transaction_id >= 0
2099 AND (dates.schedule_origination_type = 2
2100 OR (dates.schedule_origination_type = 8
2101 AND dates.source_forecast_designator is not NULL));
2102
2103 arg_row_count := arg_row_count + SQL%ROWCOUNT;
2104
2105 end if;
2106
2107 if (nvl(arg_so_count,1) > 0) then
2108
2109
2110 /*-------------------------------------------------------------+
2111 | copy only project reference for sales order to schedule load |
2112 | copy end_item_unit_number |
2113 +-------------------------------------------------------------*/
2114
2115 UPDATE mrp_schedule_dates dates
2116 SET (project_id, task_id, end_item_unit_number)
2117 = (SELECT DECODE(mtl.project_reference_enabled,
2118 1, mrp_manager_pk.get_project_id(dates.reservation_id),
2119 NULL),
2120 DECODE(mtl.project_reference_enabled,
2121 1, DECODE(mtl.project_control_level,
2122 2, mrp_manager_pk.get_task_id(dates.reservation_id),
2123 NULL),
2124 NULL),
2125 mrp_manager_pk.get_unit_number(dates.reservation_id)
2126 FROM mtl_parameters mtl
2127 WHERE dates.organization_id = mtl.organization_id)
2128 WHERE dates.old_transaction_id >= 0
2129 AND (dates.schedule_origination_type = 3
2130 OR (dates.schedule_origination_type = 8
2131 AND dates.source_sales_order_id is not NULL));
2132
2133 arg_row_count := arg_row_count + SQL%ROWCOUNT;
2134
2135 end if;
2136
2137
2138 if (nvl(arg_schedule_count,1) > 0) then
2139
2140
2141 /*-----------------------------------------------------------------------+
2142 | Copy comments, desc flex, project, line reference, and |
2143 | end_item_unit_number for sched to sched load |
2144 | Desc flex: only MDS -> MDS or MPS -> MPS |
2145 | Line reference: copy only if for the same org |
2146 +-----------------------------------------------------------------------*/
2147
2148 UPDATE mrp_schedule_dates dates1
2149 SET (ddf_context, attribute_category,
2150 attribute1, attribute2, attribute3,
2151 attribute4, attribute5, attribute6,
2152 attribute7, attribute8, attribute9,
2153 attribute10, attribute11, attribute12,
2154 attribute13, attribute14, attribute15,
2155 schedule_comments, line_id, project_id, task_id,
2156 end_item_unit_number)
2157 = (SELECT
2158 DECODE(sched2.schedule_type,
2159 sched1.schedule_type, dates2.ddf_context, NULL),
2160 DECODE(sched2.schedule_type,
2161 sched1.schedule_type, dates2.attribute_category, NULL),
2162 DECODE(sched2.schedule_type,
2163 sched1.schedule_type, dates2.attribute1, NULL),
2164 DECODE(sched2.schedule_type,
2165 sched1.schedule_type, dates2.attribute2, NULL),
2166 DECODE(sched2.schedule_type,
2167 sched1.schedule_type, dates2.attribute3, NULL),
2168 DECODE(sched2.schedule_type,
2169 sched1.schedule_type, dates2.attribute4, NULL),
2170 DECODE(sched2.schedule_type,
2171 sched1.schedule_type, dates2.attribute5, NULL),
2172 DECODE(sched2.schedule_type,
2173 sched1.schedule_type, dates2.attribute6, NULL),
2174 DECODE(sched2.schedule_type,
2175 sched1.schedule_type, dates2.attribute7, NULL),
2176 DECODE(sched2.schedule_type,
2177 sched1.schedule_type, dates2.attribute8, NULL),
2178 DECODE(sched2.schedule_type,
2179 sched1.schedule_type, dates2.attribute9, NULL),
2180 DECODE(sched2.schedule_type,
2181 sched1.schedule_type, dates2.attribute10, NULL),
2182 DECODE(sched2.schedule_type,
2183 sched1.schedule_type, dates2.attribute11, NULL),
2184 DECODE(sched2.schedule_type,
2185 sched1.schedule_type, dates2.attribute12, NULL),
2186 DECODE(sched2.schedule_type,
2187 sched1.schedule_type, dates2.attribute13, NULL),
2188 DECODE(sched2.schedule_type,
2189 sched1.schedule_type, dates2.attribute14, NULL),
2190 DECODE(sched2.schedule_type,
2191 sched1.schedule_type, dates2.attribute15, NULL),
2192 dates2.schedule_comments,
2193 DECODE(sched2.organization_id,
2194 sched1.organization_id, dates2.line_id, NULL),
2195 DECODE(mtl.project_reference_enabled,
2196 1, dates2.project_id, NULL),
2197 DECODE(mtl.project_reference_enabled,
2198 1, DECODE(mtl.project_control_level,
2199 2, dates2.task_id, NULL),
2200 NULL),
2201 dates2.end_item_unit_number
2202 FROM mtl_parameters mtl,
2203 mrp_schedule_designators sched1,
2204 mrp_schedule_designators sched2,
2205 mrp_schedule_dates dates2
2206 WHERE dates2.mps_transaction_id = dates1.old_transaction_id
2207 AND dates2.schedule_level = dates1.schedule_level
2208 AND sched1.organization_id = dates1.organization_id
2209 AND sched1.schedule_designator = dates1.schedule_designator
2210 AND sched2.organization_id = dates2.organization_id
2211 AND sched2.schedule_designator = dates2.schedule_designator
2212 AND mtl.organization_id = dates1.organization_id)
2213 WHERE dates1.old_transaction_id >= 0
2214 AND (dates1.schedule_origination_type = 4
2215 OR (dates1.schedule_origination_type = 8
2216 AND dates1.source_schedule_designator is not NULL));
2217
2218 arg_row_count := arg_row_count + SQL%ROWCOUNT;
2219
2220 end if;
2221
2222 if (nvl(arg_interorg_count,1) > 0) then
2223
2224
2225 /*------------------------------------------------------------------------+
2226 | Copy only project reference for interorg planned order to schedule load |
2227 | Do not copy line reference since they're always for different orgs |
2228 | copy end_item_unit_number |
2229 +------------------------------------------------------------------------*/
2230
2231 UPDATE mrp_schedule_dates dates
2232 SET (project_id, task_id, end_item_unit_number)
2233 = (SELECT DECODE(mtl.project_reference_enabled,
2234 1, recom.project_id,
2235 NULL),
2236 DECODE(mtl.project_reference_enabled,
2237 1, DECODE(mtl.project_control_level,
2238 2, recom.task_id,
2239 NULL),
2240 NULL),
2241 NVL(recom.implement_end_item_unit_number,
2242 recom.end_item_unit_number)
2243 FROM mtl_parameters mtl,
2244 mrp_recommendations recom
2245 WHERE recom.transaction_id = dates.old_transaction_id
2246 AND dates.organization_id = mtl.organization_id)
2247 WHERE dates.old_transaction_id >= 0
2248 AND dates.schedule_origination_type = 11;
2249
2250 arg_row_count := arg_row_count + SQL%ROWCOUNT;
2251
2252 end if;
2253
2254 UPDATE mrp_schedule_dates
2255 SET to_update = null
2256 WHERE old_transaction_id >= 0;
2257
2258 EXCEPTION
2259
2260 WHEN NO_DATA_FOUND THEN
2261 NULL;
2262 END update_schedule_desc_flex;
2263
2264 -- *************** get_customer_name ************
2265 -- Procedure returns customer name given customer id
2266
2267 FUNCTION get_customer_name(
2268 p_customer_id IN NUMBER)
2269 RETURN VARCHAR2 IS
2270
2271 v_customer_name VARCHAR2(50);
2272
2273 BEGIN
2274
2275 SELECT part.party_name
2276 INTO v_customer_name
2277 FROM HZ_PARTIES part,
2278 HZ_CUST_ACCOUNTS cust
2279 WHERE cust.cust_account_id = p_customer_id
2280 AND part.party_id = cust.party_id ;
2281
2282 RETURN v_customer_name;
2283
2284 END get_customer_name;
2285
2286 -- *************** get_ship_address ************
2287 -- Procedure returns ship address given ship id
2288
2289 FUNCTION get_ship_address(
2290 p_ship_id IN NUMBER)
2291 RETURN VARCHAR2 IS
2292
2293 v_ship_address VARCHAR2(240);
2294
2295 BEGIN
2296
2297 SELECT address1
2298 INTO v_ship_address
2299 FROM RA_CUSTOMER_SHIP_VIEW
2300 where ship_id(+) = p_ship_id;
2301
2302 RETURN v_ship_address;
2303
2304 END get_ship_address;
2305
2306 -- *************** get_bill_address ************
2307 -- Procedure returns bill address given bill id
2308
2309 FUNCTION get_bill_address(
2310 p_bill_id IN NUMBER)
2311 RETURN VARCHAR2 IS
2312
2313 v_bill_address VARCHAR2(240);
2314
2315 BEGIN
2316
2317 SELECT address1
2318 INTO v_bill_address
2319 FROM RA_CUSTOMER_BILL_VIEW
2320 where bill_id(+) = p_bill_id;
2321
2322 RETURN v_bill_address;
2323
2324 END get_bill_address;
2325
2326 FUNCTION get_project_id(
2327 p_demand_id IN NUMBER)
2328 RETURN NUMBER IS
2329
2330 v_project_id NUMBER;
2331
2332 BEGIN
2333
2334 SELECT project_id
2335 INTO v_project_id
2336 FROM oe_order_lines_all
2337 WHERE line_id = p_demand_id
2338 AND visible_demand_flag = 'Y';
2339
2340 RETURN v_project_id;
2341
2342 END get_project_id;
2343
2344 FUNCTION get_task_id(
2345 p_demand_id IN NUMBER)
2346 RETURN NUMBER IS
2347
2348 v_task_id NUMBER;
2349
2350 BEGIN
2351
2352 SELECT task_id
2353 INTO v_task_id
2354 FROM oe_order_lines_all
2355 WHERE line_id = p_demand_id
2356 AND visible_demand_flag = 'Y';
2357
2358 RETURN v_task_id;
2359
2360 END get_task_id;
2361
2362 FUNCTION get_unit_number(
2363 p_demand_id IN NUMBER)
2364 RETURN VARCHAR2 IS
2365
2366 v_unit_number VARCHAR2(30);
2367
2368 BEGIN
2369
2370 /* if ( g_om_installed IS NULL ) then
2371 g_om_installed := oe_install.get_active_product ;
2372 end if;
2373
2374 1835326 - SVAIDYAN : Uncomment for OM. We need to retrieve the unit
2375 numbers for OM also.
2376
2377
2378 IF g_om_installed = 'OE' THEN
2379
2380 SELECT NVL(sl.end_item_unit_number, slp.end_item_unit_number)
2381 INTO v_unit_number
2382 FROM so_lines_all sl,
2383 so_lines_all slp,
2384 mtl_demand_omoe dem
2385 WHERE slp.line_id(+) = nvl(sl.parent_line_id,sl.line_id)
2386 AND to_number(dem.demand_source_line) = sl.line_id(+)
2387 AND dem.demand_source_type in (2,8)
2388 AND dem.demand_id = p_demand_id;
2389
2390 ELSE
2391
2392 /* 1835326 - SCHAUDHA : Removed the join to mtl_demand_omoe as the
2393 query fetched multiple rows for a sales
2394 order line that is reserved against
2395 multiple lots.
2396 */
2397 SELECT slp.end_item_unit_number
2398 INTO v_unit_number
2399 FROM oe_order_lines_all sl,
2400 oe_order_lines_all slp
2401 WHERE slp.line_id = nvl(sl.top_model_line_id,sl.line_id)
2402 AND sl.line_id = p_demand_id;
2403
2404 -- END IF;
2405
2406 RETURN v_unit_number;
2407
2408 END get_unit_number;
2409
2410 END; -- package