[Home] [Help]
PACKAGE BODY: APPS.MRP_MANAGER_PK
Source
1 PACKAGE BODY mrp_manager_pk AS
2 /* $Header: MRPPPMGB.pls 120.3.12000000.4 2007/10/25 19:33:29 schaudha 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 if ( g_om_installed IS NULL ) then
862 g_om_installed := oe_install.get_active_product ;
863 end if;
864 var_old_so_cutoff_days := NVL(TO_NUMBER(
865 FND_PROFILE.VALUE('MRP_OLD_SO_CUTOFF_DAYS')), 99999);
866
867 IF g_om_installed = 'OE' THEN
868
869 LOOP
870 rows_updated := 0;
871 /*
872 var_watch_id := mrp_print_pk.start_watch('GEN-updated',
873 arg_request_id,
874 arg_user_id,
875 'ENTITY',
876 'mtl_demand(1)',
877 'N');
878
879 */
880 /*------------------------------------------------------+
881 | |
882 | Set updated flag to SYS_NO if none of the attributes |
883 | that affect sales order consumption have changed and |
884 | the sales order is already in MRP_SALES_ORDER_UPDATES |
885 | |
886 +------------------------------------------------------*/
887 LOOP
888 BEGIN
889 OPEN mtl_demand_cur1;
890 EXIT;
891 EXCEPTION
892 WHEN busy THEN
893 NULL;
894 dbms_lock.sleep(5);
895 END;
896 END LOOP;
897
898 LOOP
899 FETCH mtl_demand_cur1 INTO
900 var_dem_rowid;
901 EXIT WHEN mtl_demand_cur1%NOTFOUND;
902
903 UPDATE mtl_demand demand
904 SET demand.updated_flag = SYS_NO
905 WHERE rowid = var_dem_rowid;
906 rows_updated := rows_updated + SQL%ROWCOUNT;
907
908 END LOOP;
909
910 CLOSE mtl_demand_cur1;
911 /*
912 mrp_print_pk.stop_watch(arg_request_id,
913 var_watch_id,
914 rows_updated);
915 */
916 COMMIT;
917
918 IF rows_updated < UPDATE_BATCH_SIZE THEN
919 EXIT;
920 END IF;
921
922 END LOOP;
923
924 /* ------------------------------------------------------+
925 | If Installed product is OE, then RETURN !!! |
926 + ------------------------------------------------------*/
927 return;
928
929 END IF;
930
931 -- Determine whether this is the first time the planning manager is run.
932
933 /*2285868*/
934
935 var_first_time :=nvl(FND_PROFILE.VALUE('MRP_PLNG_MGR_FIRST_TIME'),'Y');
936
937 var_debug := FND_PROFILE.VALUE('MRP_DEBUG') = 'Y';
938 /*
939 mrp_print_pk.mrprint('First time : '|| var_first_time,
940 arg_request_id, arg_user_id);
941
942 */
943
944 LOOP
945
946 rows_updated := 0;
947
948 IF (var_first_time = 'N') Then
949
950 /*
951 var_watch_id := mrp_print_pk.start_watch('GEN-updated',
952 arg_request_id,
953 arg_user_id,
954 'ENTITY',
955 'mrp_so_lines_temp',
956 'N');
957
958 */
959 -- Update the interface table
960 -- mrp_so_lines_temp.
961 -- Keep looping until the records in mslt
962 -- can be locked.
963
964 LOOP
965 BEGIN
966 OPEN so_lines_temp;
967 EXIT;
968 EXCEPTION
969 WHEN busy THEN
970 NULL;
971 dbms_lock.sleep(5);
972 END;
973 END LOOP;
974
975
976
977 FETCH so_lines_temp BULK COLLECT INTO
978 line_id_arr;
979
980
981 IF (line_id_arr.COUNT > 0) THEN
982
983 FORALL i in line_id_arr.FIRST..line_id_arr.LAST
984 UPDATE mrp_so_lines_temp
985 SET process_status = 3,
986 request_id = arg_request_id,
987 last_update_login = arg_user_id,
988 last_update_date = sysdate
989 WHERE
990 line_id = line_id_arr(i)
991 and process_status = 2
992 and request_id IS NULL;
993
994 rows_updated := rows_updated + SQL%ROWCOUNT;
995
996 END IF;
997 CLOSE so_lines_temp;
998
999 line_id_arr.DELETE;
1000 /*
1001 mrp_print_pk.stop_watch(arg_request_id,
1002 var_watch_id,
1003 rows_updated);
1004 */
1005
1006 COMMIT;
1007
1008 IF (rows_updated = 0) THEN
1009
1010 EXIT; -- To take care of the last iteration
1011 -- in the case when NOT first time.
1012 -- Execute the code outside of the
1013 -- main loop.
1014 END IF;
1015
1016 IF (rows_updated < UPDATE_BATCH_SIZE) THEN
1017 var_break_loop := SYS_YES;
1018 END IF;
1019
1020 ELSE
1021 -- This is the first time the planning
1022 -- manager is running after creation of
1023 -- the profile option OR it has been
1024 -- reset to 'Yes'
1025 DELETE FROM mrp_so_lines_temp;
1026
1027 -- BUG 2848262
1028 -- Lock the table msou only if
1029 -- the planning manager is being
1030 -- run for the first time.
1031
1032
1033 /*
1034 var_watch_id := mrp_print_pk.start_watch('GEN-LOCK TABLE',
1035 arg_request_id,
1036 arg_user_id,
1037 'TABLE',
1038 'mrp_sales_order_updates',
1039 'N',
1040 'DATE',
1041 to_char(sysdate,'dd-mon hh24:mi:ss'),
1042 'Y');
1043
1044 */
1045
1046 LOCK TABLE mrp_sales_order_updates IN SHARE ROW EXCLUSIVE MODE;
1047
1048
1049 /*
1050 mrp_print_pk.stop_watch(arg_request_id,
1051 var_watch_id,
1052 rows_updated);
1053 */
1054
1055 END IF;
1056
1057
1058 /*
1059 var_watch_id := mrp_print_pk.start_watch('GEN-SELECTING',
1060 arg_request_id,
1061 arg_user_id,
1062 'ENTITY',
1063 'mrp_sales_order_updates',
1064 'N');
1065 */
1066
1067 LOOP
1068 BEGIN
1069 IF (var_first_time = 'Y') THEN
1070 OPEN mtl_demand_cur_first;
1071 ELSE
1072 OPEN mtl_demand_cur_next;
1073 END IF;
1074 EXIT;
1075 EXCEPTION
1076 WHEN busy THEN
1077 NULL;
1078 dbms_lock.sleep(5);
1079 END;
1080 END LOOP;
1081
1082 /*----------------------------------------------+
1083 | |
1084 | Set to IN_PROCESS all rows that are for sales |
1085 | orders and if the rows are not in MRP_SALES |
1086 | ORDER_UPDATES or are not IN_PROCESS |
1087 | |
1088 +----------------------------------------------*/
1089 counter := 0;
1090 counter1 := 0;
1091
1092 insert_count := 0;
1093 update_count := 0;
1094 to_insert := 0;
1095 to_update := 0;
1096
1097 LOOP
1098
1099 IF (var_first_time = 'Y') THEN
1100 FETCH mtl_demand_cur_first INTO
1101 var_dem_inventory_item_id,
1102 var_dem_demand_id,
1103 var_dem_organization_id,
1104 var_dem_user_line_num,
1105 var_dem_requirement_date,
1106 var_dem_primary_uom_quantity,
1107 var_dem_customer_id,
1108 var_dem_ship_to_site_use_id,
1109 var_dem_bill_to_site_use_id,
1110 var_dem_available_to_mrp,
1111 var_dem_demand_class,
1112 var_dem_completed_quantity,
1113 var_dem_ordered_item,
1114 var_dem_source_header_id,
1115 var_dem_demand_type,
1116 var_ato_line_id,
1117 var_upd_rowid;
1118
1119 EXIT WHEN mtl_demand_cur_first%NOTFOUND;
1120 ELSE
1121 FETCH mtl_demand_cur_next INTO
1122 var_dem_inventory_item_id,
1123 var_dem_demand_id,
1124 var_dem_organization_id,
1125 var_dem_user_line_num,
1126 var_dem_requirement_date,
1127 var_dem_primary_uom_quantity,
1128 var_dem_customer_id,
1129 var_dem_ship_to_site_use_id,
1130 var_dem_bill_to_site_use_id,
1131 var_dem_available_to_mrp,
1132 var_dem_demand_class,
1133 var_dem_completed_quantity,
1134 var_dem_ordered_item,
1135 var_dem_source_header_id,
1136 var_dem_demand_type,
1137 var_ato_line_id,
1138 var_upd_rowid;
1139
1140 EXIT WHEN mtl_demand_cur_next%NOTFOUND;
1141 END IF;
1142
1143
1144 /* Bug 1997355
1145 * Need to check for the case when rescheduling a
1146 * an ATO Model without delinking from the configured
1147 * item that has been created already.
1148 */
1149
1150 /* Bug 2504542.
1151 * Need to do the following, only if the planning
1152 * manager is NOT running for the first time.
1153 */
1154
1155 IF ((var_ato_line_id IS NOT NULL) AND
1156 (var_dem_demand_type <> 4) AND
1157 (var_first_time = 'N')) THEN
1158
1159 found_config_item := SYS_NO;
1160 config_lines_counter := 0;
1161
1162 FOR k IN 1..max_ato_model_config LOOP
1163 IF (ato_model_config_arr(k).ato_line_id = var_ato_line_id) THEN
1164 found_config_item := SYS_YES;
1165 config_line_id := ato_model_config_arr(k).config_line_id;
1166 config_lines_counter := k;
1167 EXIT;
1168 END IF;
1169 END LOOP;
1170
1171 IF (found_config_item = SYS_NO) THEN
1172 /* Need to check if the configured item is created.
1173 */
1174 OPEN config_lines;
1175 LOOP
1176
1177 FETCH config_lines INTO config_line_id;
1178 EXIT WHEN config_lines%NOTFOUND;
1179
1180 config_lines_counter := config_lines_counter + 1;
1181 max_ato_model_config := max_ato_model_config +1;
1182 ato_model_config_arr(max_ato_model_config).ato_line_id :=
1183 var_ato_line_id;
1184 ato_model_config_arr(max_ato_model_config).config_line_id :=
1185 config_line_id;
1186
1187 /* Config Item exists. Check if it is a part
1188 * of the request set.
1189 * We need to make sure that the config item
1190 * Line is processed.
1191 *
1192 * Because of the order in which we are
1193 * fetching records from the cursor
1194 * mtl_demand_cur_next, we execute this piece
1195 * of code only for the ATO model.
1196 */
1197
1198 BEGIN
1199
1200 SELECT /*+ INDEX (t mrp_so_lines_n2) */ 1
1201 INTO config_item_exists
1202 FROM mrp_so_lines_temp t
1203 WHERE process_status = 3
1204 AND line_id = config_line_id
1205 AND request_id = arg_request_id
1206 AND ROWNUM = 1;
1207
1208 EXCEPTION WHEN NO_DATA_FOUND THEN
1209 config_item_exists := 0;
1210 END;
1211
1212 IF (config_item_exists = 0) THEN
1213
1214 INSERT INTO mrp_so_lines_temp
1215 (
1216 LAST_UPDATED_BY ,
1217 LAST_UPDATE_DATE,
1218 CREATION_DATE,
1219 CREATED_BY,
1220 LAST_UPDATE_LOGIN,
1221 line_id,
1222 process_status
1223 )
1224 VALUES
1225 (
1226 arg_user_id,
1227 SYSDATE,
1228 SYSDATE,
1229 arg_user_id,
1230 arg_user_id,
1231 config_line_id,
1232 2 -- To Be Processed.
1233 ) ;
1234 END IF;
1235
1236 END LOOP;
1237 CLOSE config_lines;
1238
1239 /* If a configured line has not been created,
1240 * then we need to insert -1 as the
1241 * configured_line id in the array
1242 * ato_model_config_arr.
1243 */
1244 IF (config_lines_counter = 0 ) THEN
1245 config_line_id := -1;
1246 max_ato_model_config := max_ato_model_config +1;
1247 ato_model_config_arr(max_ato_model_config).ato_line_id :=
1248 var_ato_line_id;
1249 ato_model_config_arr(max_ato_model_config).config_line_id :=
1250 config_line_id;
1251
1252 END IF;
1253
1254 END IF;
1255 IF (config_line_id <> -1) THEN
1256 /* We should insert/update 0 for the
1257 * new_schedule_quantity in the table
1258 * mrp_sales_order_updates where the line
1259 * being processed is of model/option class
1260 * or for the option item
1261 */
1262 var_dem_primary_uom_quantity := 0;
1263
1264 END IF;
1265
1266
1267 END IF;
1268
1269 IF var_upd_rowid IS NOT NULL THEN
1270
1271 /* 2463192 - Removed the check on completed_quantity since this does not
1272 affect forecast consumption. */
1273
1274 to_update := to_update + 1;
1275
1276 UPDATE mrp_sales_order_updates upd
1277 SET
1278 last_update_date = SYSDATE,
1279 last_updated_by = arg_user_id,
1280 last_update_login = -1,
1281 process_status = 1,
1282 inventory_item_id = var_dem_inventory_item_id,
1283 sales_order_id = var_dem_demand_id,
1284 organization_id = nvl(var_dem_organization_id,
1285 organization_id),
1286 line_num = var_dem_user_line_num,
1287 new_schedule_date = nvl(var_dem_requirement_date,
1288 new_schedule_date),
1289 new_schedule_quantity = var_dem_primary_uom_quantity,
1290 current_customer_id = var_dem_customer_id,
1291 current_ship_id = var_dem_ship_to_site_use_id,
1292 current_bill_id = var_dem_bill_to_site_use_id,
1293 current_available_to_mrp= var_dem_available_to_mrp,
1294 current_demand_class = var_dem_demand_class,
1295 completed_quantity = var_dem_completed_quantity,
1296 request_id = NULL,
1297 error_message = NULL
1298 WHERE upd.rowid = var_upd_rowid
1299 AND ((new_schedule_date <> var_dem_requirement_date)
1300 OR (new_schedule_quantity <> var_dem_primary_uom_quantity)
1301 OR (current_customer_id <> var_dem_customer_id)
1302 OR (current_ship_id <> var_dem_ship_to_site_use_id)
1303 OR (current_bill_id <> var_dem_bill_to_site_use_id)
1304 OR (current_available_to_mrp <> var_dem_available_to_mrp)
1305 OR (NVL(current_demand_class, '734jkhJK24') <>
1306 NVL(var_dem_demand_class, '734jkhJK24')))
1307 AND inventory_item_id = var_dem_inventory_item_id
1308 AND organization_id = Nvl(var_dem_organization_id,
1309 organization_id);
1310
1311 --
1312 -- update the old values so that this row is not picked up again by
1313 -- the mtl_Demand_cur loop
1314
1315 update_count := update_count + sql%rowcount;
1316 if (sql%rowcount = 0) then
1317
1318 -- Changes for the bug 2296197.
1319 -- Need to check if the Shipping Warehouse (organization_id)
1320 -- for the sales order line has been changed.
1321
1322 IF (var_dem_organization_id IS NOT NULL) THEN
1323 -- Check if a record for the new organization_id
1324 -- exists in the table already.
1325
1326 SELECT COUNT(*)
1327 INTO new_org_rec_count
1328 FROM mrp_sales_order_updates
1329 WHERE
1330 sales_order_id = var_dem_demand_id
1331 AND inventory_item_id = var_dem_inventory_item_id
1332 AND organization_id = var_dem_organization_id;
1333
1334 IF (new_org_rec_count = 0) THEN
1335 -- There is no record for the new org,
1336 -- Need to insert a record for the new org.
1337
1338 IF var_dem_available_to_mrp = 'Y' THEN
1339
1340 INSERT INTO mrp_sales_order_updates
1341 (update_seq_num ,
1342 last_update_date,
1343 last_updated_by,
1344 creation_date,
1345 created_by,
1346 last_update_login,
1347 process_status,
1348 inventory_item_id,
1349 sales_order_id,
1350 organization_id,
1351 line_num,
1352 new_schedule_date,
1353 old_schedule_date,
1354 new_schedule_quantity,
1355 old_schedule_quantity,
1356 current_customer_id,
1357 previous_customer_id,
1358 current_ship_id,
1359 previous_ship_id,
1360 current_bill_id,
1361 previous_bill_id,
1362 current_territory_id,
1363 previous_territory_id,
1364 current_available_to_mrp,
1365 previous_available_to_mrp,
1366 current_demand_class,
1367 previous_demand_class,
1368 ordered_item_id,
1369 completed_quantity)
1370 VALUES
1371 (mrp_sales_order_updates_s.nextval,
1372 SYSDATE,
1373 arg_user_id,
1374 SYSDATE,
1375 arg_user_id,
1376 -1,
1377 1,
1378 var_dem_inventory_item_id,
1379 var_dem_demand_id,
1380 var_dem_organization_id,
1381 var_dem_user_line_num,
1382 var_dem_requirement_date,
1383 NULL,
1384 var_dem_primary_uom_quantity,
1385 NULL,
1386 var_dem_customer_id,
1387 NULL,
1388 var_dem_ship_to_site_use_id,
1389 NULL,
1390 var_dem_bill_to_site_use_id,
1391 NULL,
1392 var_dem_source_header_id,
1393 var_dem_source_header_id,
1394 var_dem_available_to_mrp,
1395 NULL,
1396 var_dem_demand_class,
1397 NULL,
1398 var_dem_ordered_item,
1399 var_dem_completed_quantity);
1400
1401 insert_count := insert_count + sql%rowcount;
1402 END IF;
1403
1404 END IF;
1405
1406 -- Need to update the record for the existing
1407 -- Org(s) to 0.
1408
1409 UPDATE mrp_sales_order_updates
1410 SET
1411 last_update_date = SYSDATE,
1412 last_updated_by = arg_user_id,
1413 last_update_login = -1,
1414 process_status = 1,
1415 new_schedule_quantity = 0,
1416 request_id = NULL,
1417 error_message = NULL
1418 WHERE
1419 sales_order_id = var_dem_demand_id
1420 AND ( inventory_item_id <> var_dem_inventory_item_id
1421 OR organization_id <> var_dem_organization_id)
1422 AND new_schedule_quantity <> 0;
1423
1424
1425 update_count := update_count + sql%rowcount;
1426 END IF;
1427 -- Commented out the following update statement
1428 -- for the bug 2296197
1429
1430 --update mrp_sales_order_updates
1431 -- set old_schedule_date=new_schedule_date,
1432 -- old_schedule_quantity=new_schedule_quantity,
1433 -- previous_customer_id = current_customer_id,
1434 -- previous_ship_id = current_ship_id,
1435 -- previous_bill_id = current_bill_id
1436 -- where rowid = var_upd_rowid;
1437
1438 end if;
1439
1440
1441 ELSIF var_dem_available_to_mrp = 'Y' THEN
1442 INSERT INTO mrp_sales_order_updates
1443 (update_seq_num ,
1444 last_update_date,
1445 last_updated_by,
1446 creation_date,
1447 created_by,
1448 last_update_login,
1449 process_status,
1450 inventory_item_id,
1451 sales_order_id,
1452 organization_id,
1453 line_num,
1454 new_schedule_date,
1455 old_schedule_date,
1456 new_schedule_quantity,
1457 old_schedule_quantity,
1458 current_customer_id,
1459 previous_customer_id,
1460 current_ship_id,
1461 previous_ship_id,
1462 current_bill_id,
1463 previous_bill_id,
1464 current_territory_id,
1465 previous_territory_id,
1466 current_available_to_mrp,
1467 previous_available_to_mrp,
1468 current_demand_class,
1469 previous_demand_class,
1470 ordered_item_id,
1471 completed_quantity)
1472 VALUES
1473 (mrp_sales_order_updates_s.nextval,
1474 SYSDATE,
1475 arg_user_id,
1476 SYSDATE,
1477 arg_user_id,
1478 -1,
1479 1,
1480 var_dem_inventory_item_id,
1481 var_dem_demand_id,
1482 var_dem_organization_id,
1483 var_dem_user_line_num,
1484 var_dem_requirement_date,
1485 NULL,
1486 var_dem_primary_uom_quantity,
1487 NULL,
1488 var_dem_customer_id,
1489 NULL,
1490 var_dem_ship_to_site_use_id,
1491 NULL,
1492 var_dem_bill_to_site_use_id,
1493 NULL,
1494 var_dem_source_header_id,
1495 var_dem_source_header_id,
1496 var_dem_available_to_mrp,
1497 NULL,
1498 var_dem_demand_class,
1499 NULL,
1500 var_dem_ordered_item,
1501 var_dem_completed_quantity);
1502
1503
1504 insert_count := insert_count + sql%rowcount;
1505 END IF;
1506
1507 /*
1508 * change the quantitites for model,options to 0 since
1509 * config item is being created
1510 */
1511 if(var_dem_demand_type = 4) then
1512 /* Scope for further optimization.
1513 * If the ATO model and Configured item are
1514 * in the same set of records being processed
1515 * then this update of mrp_sales_order_updates
1516 * is redundant as we have already inserted/
1517 * updated the new_schedule_quantity to 0
1518 */
1519
1520 open cur_header;
1521 fetch cur_header into var_dem_header_id;
1522 close cur_header;
1523
1524 OPEN CUR_MODEL_OPT;
1525
1526 LOOP
1527 FETCH cur_model_opt INTO line_ids_except_config;
1528 EXIT WHEN CUR_MODEL_OPT%NOTFOUND;
1529
1530 /* 2463192 - update the record only if new_schedule_quantity <> 0 */
1531
1532 UPDATE mrp_sales_order_updates upd
1533 SET
1534 last_update_date = SYSDATE,
1535 last_updated_by = arg_user_id,
1536 last_update_login = -1,
1537 process_status = 1,
1538 new_schedule_quantity = 0,
1539 current_available_to_mrp = 'N',
1540 request_id = NULL,
1541 error_message = NULL
1542 WHERE upd.sales_order_id = line_ids_except_config
1543 and upd.new_schedule_quantity <> 0;
1544
1545 update_count := update_count + sql%rowcount;
1546 END LOOP;
1547
1548 CLOSE CUR_MODEL_OPT;
1549
1550 end if;
1551
1552 if ( g_om_installed IS NULL ) then
1553 g_om_installed := oe_install.get_active_product ;
1554 end if;
1555
1556 IF g_om_installed = 'OE' THEN
1557
1558 UPDATE mtl_demand
1559 SET updated_flag = SYS_NO
1560 WHERE rowid = var_dem_rowid;
1561
1562 END IF;
1563 /* For Processing a batch of records at a time */
1564 IF (var_first_time = 'N') THEN
1565 counter1 := counter1 + 1;
1566 line_id_arr(counter1) := var_dem_demand_id;
1567 END IF;
1568
1569 END LOOP;
1570
1571 IF (var_first_time = 'Y') THEN
1572 counter := counter + mtl_demand_cur_first%ROWCOUNT;
1573 CLOSE mtl_demand_cur_first;
1574
1575 IF counter < UPDATE_BATCH_SIZE THEN
1576 var_break_loop := SYS_YES;
1577 END IF;
1578 ELSE
1579 CLOSE mtl_demand_cur_next;
1580 END IF;
1581
1582
1583 IF (var_first_time = 'N') THEN
1584 IF (var_break_loop = SYS_NO) THEN
1585 -- Update the temp table mrp_so_lines_temp
1586 -- set the line ids that have been processed
1587 -- already to process status 5.
1588 -- For the batch of rows processed.
1589 FORALL i IN 1..counter1
1590 UPDATE /*+ INDEX (t mrp_so_lines_n2) */ mrp_so_lines_temp t
1591 SET process_status = 5,
1592 last_update_login = arg_user_id,
1593 last_update_date = sysdate
1594 WHERE
1595 process_status = 3
1596 AND request_id = arg_request_id
1597 AND line_id = line_id_arr(i);
1598 END IF;
1599 END IF;
1600
1601
1602 /*
1603
1604 mrp_print_pk.stop_watch(arg_request_id,
1605 var_watch_id,
1606 counter);
1607
1608 IF var_debug THEN
1609
1610 mrp_print_pk.mrprint('Rows to update : '|| to_char(to_update),
1611 arg_request_id, arg_user_id);
1612
1613
1614 var_watch_id := mrp_print_pk.start_watch('GEN-INSERTED ROWS',
1615 arg_request_id,
1616 arg_user_id,
1617 'NUMBER',
1618 to_char(insert_count),
1619 'N',
1620 'TABLE',
1621 'mrp_sales_order_updates',
1622 'N');
1623
1624 mrp_print_pk.stop_watch(arg_request_id,
1625 var_watch_id);
1626
1627 var_watch_id := mrp_print_pk.start_watch('GEN-updated',
1628 arg_request_id,
1629 arg_user_id,
1630 'ENTITY',
1631 'mrp_sales_order_updates',
1632 'N');
1633
1634 mrp_print_pk.stop_watch(arg_request_id,
1635 var_watch_id,
1636 update_count);
1637
1638 END IF;
1639
1640 */
1641 /*------------------------------------------------------------+
1642 | Update sales order dates to the last valid workday for |
1643 | sales orders that lie outside the calendar date |
1644 +------------------------------------------------------------*/
1645 OPEN calendar;
1646 prev_cal_code := 'aggd4885-23453';
1647 prev_except_set_id := '-23453';
1648
1649
1650 LOOP
1651 FETCH calendar into var_cal_code,
1652 var_except_set_id,
1653 var_org_id;
1654
1655 EXIT WHEN calendar%NOTFOUND;
1656
1657 IF prev_cal_code <> var_cal_code OR var_except_set_id <>
1658 prev_except_set_id
1659 THEN
1660
1661
1662 SELECT min(calendar_date), max(calendar_date)
1663 INTO var_min_cal_date,
1664 var_max_cal_date
1665 FROM bom_calendar_dates
1666 WHERE calendar_code = var_cal_code
1667 AND exception_set_id = var_except_set_id;
1668
1669 prev_cal_code := var_cal_code;
1670 prev_except_set_id := var_except_set_id;
1671 END IF;
1672
1673 /*
1674 IF var_debug THEN
1675
1676 mrp_print_pk.mrprint('Processing Calendar: '|| var_cal_code,
1677 arg_request_id, arg_user_id);
1678 mrp_print_pk.mrprint('Min Date : '||
1679 to_char(var_min_cal_date,'DD-MON-RR'),
1680 arg_request_id, arg_user_id);
1681 mrp_print_pk.mrprint('Max Date : '||
1682 to_char(var_max_cal_date,'DD-MON-RR'),
1683 arg_request_id, arg_user_id);
1684
1685 END IF;
1686 */
1687 insert into msc_form_query
1688 (query_id ,
1689 last_update_date,
1690 last_updated_by,
1691 creation_date,
1692 created_by,
1693 last_update_login)
1694 select sales_order_id ,
1695 SYSDATE,
1696 arg_user_id,
1697 SYSDATE,
1698 arg_user_id,
1699 -1
1700 from mrp_sales_order_updates
1701 where organization_id = var_org_id
1702 and process_status = 1
1703 and (new_schedule_date < var_min_cal_date
1704 or new_schedule_date > var_max_cal_date);
1705
1706 /*
1707 IF var_debug THEN
1708
1709 insert_count := sql%rowcount;
1710 var_watch_id := mrp_print_pk.start_watch('GEN-INSERTED ROWS',
1711 arg_request_id,
1712 arg_user_id,
1713 'NUMBER',
1714 to_char(insert_count),
1715 'N',
1716 'TABLE',
1717 'msc_form_query',
1718 'N');
1719
1720 mrp_print_pk.stop_watch(arg_request_id,
1721 var_watch_id);
1722
1723 END IF;
1724 */
1725 /*
1726 END LOOP;
1727 */
1728 /*
1729 IF var_debug THEN
1730
1731
1732 mrp_print_pk.mrprint('Processed '|| to_char(calendar%rowcount)
1733 ||' orgs',
1734 arg_request_id, arg_user_id);
1735
1736 END IF;
1737 */
1738
1739 /*
1740 CLOSE calendar;
1741
1742 */
1743 /*
1744 IF var_debug THEN
1745
1746
1747 var_watch_id := mrp_print_pk.start_watch('GEN-updated',
1748 arg_request_id,
1749 arg_user_id,
1750 'ENTITY',
1751 'mrp_sales_order_updates',
1752 'N');
1753 END IF;
1754 */
1755 UPDATE mrp_sales_order_updates upd
1756 SET old_schedule_date = GREATEST(var_min_cal_date,
1757 LEAST(var_max_cal_date, old_schedule_date)),
1758 new_schedule_date = GREATEST(var_min_cal_date,
1759 LEAST(var_max_cal_date, new_schedule_date)),
1760 process_status = 2
1761 WHERE process_status = 1
1762 AND organization_id = var_org_id ;
1763
1764 /*
1765 IF var_debug THEN
1766
1767
1768 mrp_print_pk.stop_watch(arg_request_id,
1769 var_watch_id,
1770 SQL%ROWCOUNT);
1771
1772 END IF;
1773 */
1774 END LOOP;
1775
1776 CLOSE calendar;
1777
1778 COMMIT;
1779
1780 IF var_break_loop = SYS_YES THEN
1781 EXIT;
1782 END IF;
1783
1784 END LOOP;
1785
1786 IF (var_first_time = 'N') Then
1787 -- Update the interface table
1788 -- mrp_so_lines_temp.
1789 -- For the last batch of rows.
1790 FORALL i IN 1..counter1
1791 UPDATE /*+ INDEX (t mrp_so_lines_n2) */ mrp_so_lines_temp t
1792 SET process_status = 5,
1793 last_update_login = arg_user_id,
1794 last_update_date = sysdate
1795 WHERE
1796 process_status = 3
1797 AND request_id = arg_request_id
1798 AND line_id = line_id_arr(i);
1799
1800 /* Bug 1997355.
1801 * Need to handle a case when a sales order line is
1802 * deleted OR a configured item is delinked from it's
1803 * model item.
1804 * In this case, there will be some records in the table
1805 * mrp_so_lines_temp, with the process_status as 3
1806 * but their corresponding record is not found in the
1807 * table oe_order_lines_all as they have been deleted.
1808 */
1809 UPDATE mrp_sales_order_updates upd
1810 SET
1811 last_update_date = SYSDATE,
1812 last_updated_by = arg_user_id,
1813 last_update_login = -1,
1814 process_status = 2,
1815 new_schedule_quantity = 0,
1816 current_available_to_mrp = 'N', -- BUG 3445569
1817 request_id = NULL,
1818 error_message = NULL
1819 WHERE
1820 sales_order_id IN
1821 (SELECT line_id
1822 FROM mrp_so_lines_temp
1823 WHERE
1824 process_status = 3
1825 AND request_id = arg_request_id) ;
1826
1827 /* Now Update these lines in the table
1828 * mrp_so_lines_temp to processed.
1829 */
1830
1831 UPDATE mrp_so_lines_temp
1832 SET process_status = 5,
1833 last_update_login = arg_user_id,
1834 last_update_date = sysdate
1835 WHERE
1836 process_status = 3
1837 AND request_id = arg_request_id ;
1838
1839
1840 ELSE
1841 -- Set the profile option MRP Planning Manager
1842 -- First Time to No.
1843 pvalue := fnd_profile.save('MRP_PLNG_MGR_FIRST_TIME', 'N', 'SITE');
1844 END IF;
1845 COMMIT;
1846
1847 END compute_sales_order_changes;
1848
1849 -- ********************** update_sales_orders *************************
1850 PROCEDURE update_sales_orders(arg_request_id IN NUMBER,
1851 arg_user_id IN NUMBER) IS
1852
1853 CURSOR lock_mtl_demand_cur IS
1854 SELECT demand.rowid,
1855 updates1.new_schedule_quantity,
1856 updates1.new_schedule_date
1857 FROM mtl_demand demand,
1858 mrp_sales_order_updates updates1
1859 WHERE updates1.sales_order_id = demand.demand_id
1860 AND updates1.request_id = arg_request_id
1861 AND updates1.process_status = 3
1862 AND updates1.error_message IS NULL
1863 FOR UPDATE OF demand.mrp_date NOWAIT;
1864
1865 var_rowid ROWID;
1866 var_date DATE;
1867 var_quantity NUMBER;
1868 rows_updated NUMBER := 0;
1869
1870 busy EXCEPTION;
1871
1872 PRAGMA EXCEPTION_INIT(busy, -54);
1873
1874 BEGIN
1875
1876 if ( g_om_installed IS NULL ) then
1877 g_om_installed := oe_install.get_active_product ;
1878 end if;
1879
1880 IF g_om_installed = 'OE' THEN
1881
1882 var_watch_id := mrp_print_pk.start_watch('GEN-updated',
1883 arg_request_id,
1884 arg_user_id,
1885 'ENTITY',
1886 'mtl_demand',
1887 'N');
1888 LOOP
1889 BEGIN
1890 OPEN lock_mtl_demand_cur;
1891 EXIT;
1892 EXCEPTION
1893 WHEN busy THEN
1894 NULL;
1895 dbms_lock.sleep(5);
1896 END;
1897 END LOOP;
1898
1899 LOOP
1900 FETCH lock_mtl_demand_cur INTO
1901 var_rowid,
1902 var_quantity,
1903 var_date;
1904 EXIT WHEN lock_mtl_demand_cur%NOTFOUND;
1905
1906 UPDATE mtl_demand demand
1907 SET demand.mrp_date = var_date,
1908 demand.mrp_quantity = var_quantity
1909 WHERE rowid = var_rowid;
1910
1911 rows_updated := rows_updated + SQL%ROWCOUNT;
1912
1913 END LOOP;
1914
1915 CLOSE lock_mtl_demand_cur;
1916
1917 mrp_print_pk.stop_watch(arg_request_id,
1918 var_watch_id,
1919 rows_updated);
1920
1921 END IF;
1922
1923 var_watch_id := mrp_print_pk.start_watch('GEN-updated',
1924 arg_request_id,
1925 arg_user_id,
1926 'ENTITY',
1927 'mrp_sales_order_updates',
1928 'N');
1929 UPDATE mrp_sales_order_updates
1930 SET old_schedule_date = new_schedule_date,
1931 old_schedule_quantity = new_schedule_quantity,
1932 previous_customer_id = current_customer_id,
1933 previous_ship_id = current_ship_id,
1934 previous_bill_id = current_bill_id,
1935 previous_demand_class = current_demand_class,
1936 previous_territory_id = current_territory_id,
1937 previous_available_to_mrp = current_available_to_mrp,
1938 process_status = 5
1939 WHERE request_id = arg_request_id
1940 AND process_status = 3
1941 AND error_message IS NULL;
1942
1943 mrp_print_pk.stop_watch(arg_request_id,
1944 var_watch_id,
1945 SQL%ROWCOUNT);
1946
1947 END update_sales_orders;
1948
1949 -- ********************** create_forecast_items *************************
1950 PROCEDURE create_forecast_items(
1951 arg_request_id IN NUMBER,
1952 arg_user_id IN NUMBER,
1953 arg_desig IN VARCHAR2) IS
1954 BEGIN
1955
1956 var_watch_id := mrp_print_pk.start_watch(
1957 'GEN-inserting',
1958 arg_request_id,
1959 arg_user_id,
1960 'ENTITY',
1961 'E_ITEMS',
1962 'Y',
1963 'TABLE',
1964 'mrp_forecast_items',
1965 'N');
1966
1967 INSERT INTO mrp_forecast_items
1968 (
1969 inventory_item_id,
1970 organization_id,
1971 forecast_designator,
1972 last_update_date,
1973 last_updated_by,
1974 creation_date,
1975 created_by,
1976 last_update_login
1977 )
1978 SELECT /*+ index (dates MRP_FORECAST_DATES_N1) */
1979 DISTINCT inventory_item_id,
1980 organization_id,
1981 forecast_designator,
1982 SYSDATE,
1983 1,
1984 SYSDATE,
1985 1,
1986 -1
1987 FROM mrp_forecast_dates dates
1988 WHERE NOT EXISTS
1989 (SELECT NULL
1990 FROM mrp_forecast_items items
1991 WHERE items.organization_id = dates.organization_id
1992 AND items.forecast_designator = dates.forecast_designator
1993 AND items.inventory_item_id = dates.inventory_item_id);
1994
1995 mrp_print_pk.stop_watch(arg_request_id,
1996 var_watch_id,
1997 SQL%ROWCOUNT);
1998
1999 END create_forecast_items;
2000
2001 -- ********************** update_forecast_desc_flex *************************
2002 PROCEDURE update_forecast_desc_flex(arg_row_count IN OUT NOCOPY NUMBER) IS
2003 BEGIN
2004
2005 /*------------------------------------------------------+
2006 | Copy comments, desc flex, project, and line reference |
2007 | For line reference, copy only if for the same org |
2008 +------------------------------------------------------*/
2009
2010 UPDATE mrp_forecast_dates dates1
2011 SET (ddf_context, attribute_category,
2012 attribute1, attribute2, attribute3,
2013 attribute4, attribute5, attribute6,
2014 attribute7, attribute8, attribute9,
2015 attribute10, attribute11, attribute12,
2016 attribute13, attribute14, attribute15,
2017 comments, line_id, project_id, task_id)
2018 = (SELECT dates2.ddf_context, dates2.attribute_category,
2019 dates2.attribute1, dates2.attribute2, dates2.attribute3,
2020 dates2.attribute4, dates2.attribute5, dates2.attribute6,
2021 dates2.attribute7, dates2.attribute8, dates2.attribute9,
2022 dates2.attribute10, dates2.attribute11,
2023 dates2.attribute12, dates2.attribute13,
2024 dates2.attribute14, dates2.attribute15,
2025 dates2.comments,
2026 DECODE(dates2.organization_id,
2027 dates1.organization_id, dates2.line_id, NULL),
2028 DECODE(mtl.project_reference_enabled,
2029 1, dates2.project_id,
2030 NULL),
2031 DECODE(mtl.project_reference_enabled,
2032 1, DECODE(project_control_level,
2033 2, dates2.task_id,
2034 NULL),
2035 NULL)
2036 FROM mtl_parameters mtl,
2037 mrp_forecast_dates dates2
2038 WHERE dates2.transaction_id = dates1.old_transaction_id
2039 AND dates1.organization_id = mtl.organization_id)
2040 WHERE dates1.old_transaction_id >= 0;
2041
2042 arg_row_count := SQL%ROWCOUNT;
2043
2044 UPDATE mrp_forecast_dates
2045 SET to_update = null
2046 WHERE old_transaction_id >= 0;
2047
2048 END update_forecast_desc_flex;
2049
2050 -- ********************** update_schedule_desc_flex *************************
2051 PROCEDURE update_schedule_desc_flex(arg_row_count IN OUT NOCOPY NUMBER,
2052 arg_schedule_count IN NUMBER,
2053 arg_forecast_count IN NUMBER,
2054 arg_so_count IN NUMBER,
2055 arg_interorg_count IN NUMBER) IS
2056 BEGIN
2057
2058
2059 /*--------------------------------------------------------------+
2060 | BUG # 2639914 |
2061 | Execute the Update statement based on the value of the new |
2062 | input parameters. |
2063 | Execute the update only if the corresponding counter > 0 |
2064 +-------------------------------------------------------------*/
2065
2066 arg_row_count := 0;
2067
2068 if (nvl(arg_forecast_count,1) > 0) then
2069
2070 /*-----------------------------------------------------------------------+
2071 | Copy only comments, project, and line reference for fcst to sched load |
2072 | For line reference, copy only if for the same org |
2073 | Do not copy end_item_unit_number because it is not stored on forecast |
2074 +-----------------------------------------------------------------------*/
2075
2076 UPDATE mrp_schedule_dates dates
2077 SET (schedule_comments, line_id, project_id, task_id)
2078 = (SELECT fc_dates.comments,
2079 DECODE(fc_dates.organization_id,
2080 dates.organization_id, fc_dates.line_id, NULL),
2081 DECODE(mtl.project_reference_enabled,
2082 1, fc_dates.project_id,
2083 NULL),
2084 DECODE(mtl.project_reference_enabled,
2085 1, DECODE(mtl.project_control_level,
2086 2, fc_dates.task_id,
2087 NULL),
2088 NULL)
2089 FROM mtl_parameters mtl,
2090 mrp_forecast_dates fc_dates
2091 WHERE fc_dates.transaction_id = dates.old_transaction_id
2092 AND dates.organization_id = mtl.organization_id)
2093 WHERE dates.old_transaction_id >= 0
2094 AND (dates.schedule_origination_type = 2
2095 OR (dates.schedule_origination_type = 8
2096 AND dates.source_forecast_designator is not NULL));
2097
2098 arg_row_count := arg_row_count + SQL%ROWCOUNT;
2099
2100 end if;
2101
2102 if (nvl(arg_so_count,1) > 0) then
2103
2104
2105 /*-------------------------------------------------------------+
2106 | copy only project reference for sales order to schedule load |
2107 | copy end_item_unit_number |
2108 +-------------------------------------------------------------*/
2109
2110 UPDATE mrp_schedule_dates dates
2111 SET (project_id, task_id, end_item_unit_number)
2112 = (SELECT DECODE(mtl.project_reference_enabled,
2113 1, mrp_manager_pk.get_project_id(dates.reservation_id),
2114 NULL),
2115 DECODE(mtl.project_reference_enabled,
2116 1, DECODE(mtl.project_control_level,
2117 2, mrp_manager_pk.get_task_id(dates.reservation_id),
2118 NULL),
2119 NULL),
2120 mrp_manager_pk.get_unit_number(dates.reservation_id)
2121 FROM mtl_parameters mtl
2122 WHERE dates.organization_id = mtl.organization_id)
2123 WHERE dates.old_transaction_id >= 0
2124 AND (dates.schedule_origination_type = 3
2125 OR (dates.schedule_origination_type = 8
2126 AND dates.source_sales_order_id is not NULL));
2127
2128 arg_row_count := arg_row_count + SQL%ROWCOUNT;
2129
2130 end if;
2131
2132
2133 if (nvl(arg_schedule_count,1) > 0) then
2134
2135
2136 /*-----------------------------------------------------------------------+
2137 | Copy comments, desc flex, project, line reference, and |
2138 | end_item_unit_number for sched to sched load |
2139 | Desc flex: only MDS -> MDS or MPS -> MPS |
2140 | Line reference: copy only if for the same org |
2141 +-----------------------------------------------------------------------*/
2142
2143 UPDATE mrp_schedule_dates dates1
2144 SET (ddf_context, attribute_category,
2145 attribute1, attribute2, attribute3,
2146 attribute4, attribute5, attribute6,
2147 attribute7, attribute8, attribute9,
2148 attribute10, attribute11, attribute12,
2149 attribute13, attribute14, attribute15,
2150 schedule_comments, line_id, project_id, task_id,
2151 end_item_unit_number)
2152 = (SELECT
2153 DECODE(sched2.schedule_type,
2154 sched1.schedule_type, dates2.ddf_context, NULL),
2155 DECODE(sched2.schedule_type,
2156 sched1.schedule_type, dates2.attribute_category, NULL),
2157 DECODE(sched2.schedule_type,
2158 sched1.schedule_type, dates2.attribute1, NULL),
2159 DECODE(sched2.schedule_type,
2160 sched1.schedule_type, dates2.attribute2, NULL),
2161 DECODE(sched2.schedule_type,
2162 sched1.schedule_type, dates2.attribute3, NULL),
2163 DECODE(sched2.schedule_type,
2164 sched1.schedule_type, dates2.attribute4, NULL),
2165 DECODE(sched2.schedule_type,
2166 sched1.schedule_type, dates2.attribute5, NULL),
2167 DECODE(sched2.schedule_type,
2168 sched1.schedule_type, dates2.attribute6, NULL),
2169 DECODE(sched2.schedule_type,
2170 sched1.schedule_type, dates2.attribute7, NULL),
2171 DECODE(sched2.schedule_type,
2172 sched1.schedule_type, dates2.attribute8, NULL),
2173 DECODE(sched2.schedule_type,
2174 sched1.schedule_type, dates2.attribute9, NULL),
2175 DECODE(sched2.schedule_type,
2176 sched1.schedule_type, dates2.attribute10, NULL),
2177 DECODE(sched2.schedule_type,
2178 sched1.schedule_type, dates2.attribute11, NULL),
2179 DECODE(sched2.schedule_type,
2180 sched1.schedule_type, dates2.attribute12, NULL),
2181 DECODE(sched2.schedule_type,
2182 sched1.schedule_type, dates2.attribute13, NULL),
2183 DECODE(sched2.schedule_type,
2184 sched1.schedule_type, dates2.attribute14, NULL),
2185 DECODE(sched2.schedule_type,
2186 sched1.schedule_type, dates2.attribute15, NULL),
2187 dates2.schedule_comments,
2188 DECODE(sched2.organization_id,
2189 sched1.organization_id, dates2.line_id, NULL),
2190 DECODE(mtl.project_reference_enabled,
2191 1, dates2.project_id, NULL),
2192 DECODE(mtl.project_reference_enabled,
2193 1, DECODE(mtl.project_control_level,
2194 2, dates2.task_id, NULL),
2195 NULL),
2196 dates2.end_item_unit_number
2197 FROM mtl_parameters mtl,
2198 mrp_schedule_designators sched1,
2199 mrp_schedule_designators sched2,
2200 mrp_schedule_dates dates2
2201 WHERE dates2.mps_transaction_id = dates1.old_transaction_id
2202 AND dates2.schedule_level = dates1.schedule_level
2203 AND sched1.organization_id = dates1.organization_id
2204 AND sched1.schedule_designator = dates1.schedule_designator
2205 AND sched2.organization_id = dates2.organization_id
2206 AND sched2.schedule_designator = dates2.schedule_designator
2207 AND mtl.organization_id = dates1.organization_id)
2208 WHERE dates1.old_transaction_id >= 0
2209 AND (dates1.schedule_origination_type = 4
2210 OR (dates1.schedule_origination_type = 8
2211 AND dates1.source_schedule_designator is not NULL));
2212
2213 arg_row_count := arg_row_count + SQL%ROWCOUNT;
2214
2215 end if;
2216
2217 if (nvl(arg_interorg_count,1) > 0) then
2218
2219
2220 /*------------------------------------------------------------------------+
2221 | Copy only project reference for interorg planned order to schedule load |
2222 | Do not copy line reference since they're always for different orgs |
2223 | copy end_item_unit_number |
2224 +------------------------------------------------------------------------*/
2225
2226 UPDATE mrp_schedule_dates dates
2227 SET (project_id, task_id, end_item_unit_number)
2228 = (SELECT DECODE(mtl.project_reference_enabled,
2229 1, recom.project_id,
2230 NULL),
2231 DECODE(mtl.project_reference_enabled,
2232 1, DECODE(mtl.project_control_level,
2233 2, recom.task_id,
2234 NULL),
2235 NULL),
2236 NVL(recom.implement_end_item_unit_number,
2237 recom.end_item_unit_number)
2238 FROM mtl_parameters mtl,
2239 mrp_recommendations recom
2240 WHERE recom.transaction_id = dates.old_transaction_id
2241 AND dates.organization_id = mtl.organization_id)
2242 WHERE dates.old_transaction_id >= 0
2243 AND dates.schedule_origination_type = 11;
2244
2245 arg_row_count := arg_row_count + SQL%ROWCOUNT;
2246
2247 end if;
2248
2249 UPDATE mrp_schedule_dates
2250 SET to_update = null
2251 WHERE old_transaction_id >= 0;
2252
2253 EXCEPTION
2254
2255 WHEN NO_DATA_FOUND THEN
2256 NULL;
2257 END update_schedule_desc_flex;
2258
2259 -- *************** get_customer_name ************
2260 -- Procedure returns customer name given customer id
2261
2262 FUNCTION get_customer_name(
2263 p_customer_id IN NUMBER)
2264 RETURN VARCHAR2 IS
2265
2266 v_customer_name VARCHAR2(50);
2267
2268 BEGIN
2269
2270 SELECT part.party_name
2271 INTO v_customer_name
2272 FROM HZ_PARTIES part,
2273 HZ_CUST_ACCOUNTS cust
2274 WHERE cust.cust_account_id = p_customer_id
2275 AND part.party_id = cust.party_id ;
2276
2277 RETURN v_customer_name;
2278
2279 END get_customer_name;
2280
2281 -- *************** get_ship_address ************
2282 -- Procedure returns ship address given ship id
2283
2284 FUNCTION get_ship_address(
2285 p_ship_id IN NUMBER)
2286 RETURN VARCHAR2 IS
2287
2288 v_ship_address VARCHAR2(240);
2289
2290 BEGIN
2291
2292 SELECT address1
2293 INTO v_ship_address
2294 FROM RA_CUSTOMER_SHIP_VIEW
2295 where ship_id(+) = p_ship_id;
2296
2297 RETURN v_ship_address;
2298
2299 END get_ship_address;
2300
2301 -- *************** get_bill_address ************
2302 -- Procedure returns bill address given bill id
2303
2304 FUNCTION get_bill_address(
2305 p_bill_id IN NUMBER)
2306 RETURN VARCHAR2 IS
2307
2308 v_bill_address VARCHAR2(240);
2309
2310 BEGIN
2311
2312 SELECT address1
2313 INTO v_bill_address
2314 FROM RA_CUSTOMER_BILL_VIEW
2315 where bill_id(+) = p_bill_id;
2316
2317 RETURN v_bill_address;
2318
2319 END get_bill_address;
2320
2321 FUNCTION get_project_id(
2322 p_demand_id IN NUMBER)
2323 RETURN NUMBER IS
2324
2325 v_project_id NUMBER;
2326
2327 BEGIN
2328
2329 SELECT project_id
2330 INTO v_project_id
2331 FROM oe_order_lines_all
2332 WHERE line_id = p_demand_id
2333 AND visible_demand_flag = 'Y';
2334
2335 RETURN v_project_id;
2336
2337 END get_project_id;
2338
2339 FUNCTION get_task_id(
2340 p_demand_id IN NUMBER)
2341 RETURN NUMBER IS
2342
2343 v_task_id NUMBER;
2344
2345 BEGIN
2346
2347 SELECT task_id
2348 INTO v_task_id
2349 FROM oe_order_lines_all
2350 WHERE line_id = p_demand_id
2351 AND visible_demand_flag = 'Y';
2352
2353 RETURN v_task_id;
2354
2355 END get_task_id;
2356
2357 FUNCTION get_unit_number(
2358 p_demand_id IN NUMBER)
2359 RETURN VARCHAR2 IS
2360
2361 v_unit_number VARCHAR2(30);
2362
2363 BEGIN
2364
2365 if ( g_om_installed IS NULL ) then
2366 g_om_installed := oe_install.get_active_product ;
2367 end if;
2368
2369 /* 1835326 - SVAIDYAN : Uncomment for OM. We need to retrieve the unit
2370 numbers for OM also.
2371 */
2372
2373 IF g_om_installed = 'OE' THEN
2374
2375 SELECT NVL(sl.end_item_unit_number, slp.end_item_unit_number)
2376 INTO v_unit_number
2377 FROM so_lines_all sl,
2378 so_lines_all slp,
2379 mtl_demand_omoe dem
2380 WHERE slp.line_id(+) = nvl(sl.parent_line_id,sl.line_id)
2381 AND to_number(dem.demand_source_line) = sl.line_id(+)
2382 AND dem.demand_source_type in (2,8)
2383 AND dem.demand_id = p_demand_id;
2384
2385 ELSE
2386
2387 /* 1835326 - SCHAUDHA : Removed the join to mtl_demand_omoe as the
2388 query fetched multiple rows for a sales
2389 order line that is reserved against
2390 multiple lots.
2391 */
2392 SELECT slp.end_item_unit_number
2393 INTO v_unit_number
2394 FROM oe_order_lines_all sl,
2395 oe_order_lines_all slp
2396 WHERE slp.line_id = nvl(sl.top_model_line_id,sl.line_id)
2397 AND sl.line_id = p_demand_id;
2398
2399 END IF;
2400
2401 RETURN v_unit_number;
2402
2403 END get_unit_number;
2404
2405 END; -- package