28:
29: BEGIN
30:
31: /* -----------------------------------------------
32: Cleanup MRP_FLOW_DEMANDS data
33: -------------------------------------------------*/
34:
35: l_retval := FND_INSTALLATION.GET_APP_INFO('MRP', dummy1, dummy2, l_applsys_schema);
36:
35: l_retval := FND_INSTALLATION.GET_APP_INFO('MRP', dummy1, dummy2, l_applsys_schema);
36:
37: IF (i_RN = -1) THEN
38: -- complete refresh
39: sqls := 'TRUNCATE TABLE '||l_applsys_schema||'.MRP_FLOW_DEMANDS';
40: execute immediate sqls;
41: ELSE
42: DELETE FROM MRP_FLOW_DEMANDS
43: WHERE wip_entity_id IN ( SELECT wip_entity_id
38: -- complete refresh
39: sqls := 'TRUNCATE TABLE '||l_applsys_schema||'.MRP_FLOW_DEMANDS';
40: execute immediate sqls;
41: ELSE
42: DELETE FROM MRP_FLOW_DEMANDS
43: WHERE wip_entity_id IN ( SELECT wip_entity_id
44: FROM MRP_AD_FLOW_SCHDS
45: WHERE RN= i_RN);
46: END IF;
63: -- if jobs are affected by current bill changes
64: IF (g_wip_entity_id_arr.COUNT <> 0 ) THEN
65:
66: lv_temp_sql_stmt := ' DELETE /*+ parallel(mfd) */ '
67: ||' FROM MRP_FLOW_DEMANDS MFD '
68: ||' WHERE EXISTS (SELECT 1 '
69: ||' FROM MRP_SN_INV_COMPS MSIC '
70: ||' WHERE MSIC.RN = :i_RN'
71: ||' AND MSIC.BILL_SEQUENCE_ID = MFD.BILL_SEQUENCE_ID) ';
85: INTO lv_changed_job USING i_RN;
86:
87: IF (lv_changed_job > 0 ) THEN
88:
89: lv_temp_sql_stmt := ' DELETE FROM MRP_FLOW_DEMANDS MFD '
90: ||' WHERE EXISTS (SELECT 1 FROM MRP_SN_FLOW_SCHDS MFS '
91: ||' WHERE MFS.RN >= :i_RN '
92: ||' AND MFS.PLANNED_QUANTITY > MFS.QUANTITY_COMPLETED '
93: ||' AND MFS.WIP_ENTITY_ID = MFD.WIP_ENTITY_ID) ';
133:
134: CURSOR WIP_FLOW_JOBS_AFFECTED(i_RN_index IN NUMBER, i_bill_sequence_id IN NUMBER) IS
135: SELECT DISTINCT -- for performance we can remove distinct
136: wip_entity_id
137: FROM MRP_FLOW_DEMANDS
138: WHERE BILL_SEQUENCE_ID = i_bill_sequence_id;
139:
140: l_bill_sequence_id NUMBER;
141: l_wip_entity_id NUMBER;
408: BEGIN
409:
410: -- delete old demand
411: -- performance fix for Bug 3550414.
412: -- DELETE MRP_FLOW_DEMANDS
413: -- WHERE wip_entity_id = g_wip_entity_id;
414:
415:
416: -- insert demand for new bill
534: TYPE CurTyp IS REF CURSOR;
535: lv_ins CurTyp;
536: l_index NUMBER;
537: lv_cursor_stmt varchar2(5000);
538: lv_organization_id mrp_flow_demands.organization_id%TYPE;
539: lv_planned_quantity mrp_flow_demands.planned_quantity%TYPE;
540: lv_primary_item_id mrp_flow_demands.primary_item_id%TYPE;
541: lv_quantity_completed mrp_flow_demands.quantity_completed%TYPE;
542: lv_scheduled_comp_date mrp_flow_demands.scheduled_completion_date%TYPE;
535: lv_ins CurTyp;
536: l_index NUMBER;
537: lv_cursor_stmt varchar2(5000);
538: lv_organization_id mrp_flow_demands.organization_id%TYPE;
539: lv_planned_quantity mrp_flow_demands.planned_quantity%TYPE;
540: lv_primary_item_id mrp_flow_demands.primary_item_id%TYPE;
541: lv_quantity_completed mrp_flow_demands.quantity_completed%TYPE;
542: lv_scheduled_comp_date mrp_flow_demands.scheduled_completion_date%TYPE;
543: lv_scheduled_start_date mrp_flow_demands.scheduled_start_date%TYPE;
536: l_index NUMBER;
537: lv_cursor_stmt varchar2(5000);
538: lv_organization_id mrp_flow_demands.organization_id%TYPE;
539: lv_planned_quantity mrp_flow_demands.planned_quantity%TYPE;
540: lv_primary_item_id mrp_flow_demands.primary_item_id%TYPE;
541: lv_quantity_completed mrp_flow_demands.quantity_completed%TYPE;
542: lv_scheduled_comp_date mrp_flow_demands.scheduled_completion_date%TYPE;
543: lv_scheduled_start_date mrp_flow_demands.scheduled_start_date%TYPE;
544: lv_wip_entity_id mrp_flow_demands.wip_entity_id%TYPE;
537: lv_cursor_stmt varchar2(5000);
538: lv_organization_id mrp_flow_demands.organization_id%TYPE;
539: lv_planned_quantity mrp_flow_demands.planned_quantity%TYPE;
540: lv_primary_item_id mrp_flow_demands.primary_item_id%TYPE;
541: lv_quantity_completed mrp_flow_demands.quantity_completed%TYPE;
542: lv_scheduled_comp_date mrp_flow_demands.scheduled_completion_date%TYPE;
543: lv_scheduled_start_date mrp_flow_demands.scheduled_start_date%TYPE;
544: lv_wip_entity_id mrp_flow_demands.wip_entity_id%TYPE;
545: lv_plan_level mrp_flow_demands.plan_level%TYPE;
538: lv_organization_id mrp_flow_demands.organization_id%TYPE;
539: lv_planned_quantity mrp_flow_demands.planned_quantity%TYPE;
540: lv_primary_item_id mrp_flow_demands.primary_item_id%TYPE;
541: lv_quantity_completed mrp_flow_demands.quantity_completed%TYPE;
542: lv_scheduled_comp_date mrp_flow_demands.scheduled_completion_date%TYPE;
543: lv_scheduled_start_date mrp_flow_demands.scheduled_start_date%TYPE;
544: lv_wip_entity_id mrp_flow_demands.wip_entity_id%TYPE;
545: lv_plan_level mrp_flow_demands.plan_level%TYPE;
546: lv_wip_supply_type mrp_flow_demands.wip_supply_type%TYPE;
539: lv_planned_quantity mrp_flow_demands.planned_quantity%TYPE;
540: lv_primary_item_id mrp_flow_demands.primary_item_id%TYPE;
541: lv_quantity_completed mrp_flow_demands.quantity_completed%TYPE;
542: lv_scheduled_comp_date mrp_flow_demands.scheduled_completion_date%TYPE;
543: lv_scheduled_start_date mrp_flow_demands.scheduled_start_date%TYPE;
544: lv_wip_entity_id mrp_flow_demands.wip_entity_id%TYPE;
545: lv_plan_level mrp_flow_demands.plan_level%TYPE;
546: lv_wip_supply_type mrp_flow_demands.wip_supply_type%TYPE;
547: lv_bill_sequence_id mrp_flow_demands.bill_sequence_id%TYPE;
540: lv_primary_item_id mrp_flow_demands.primary_item_id%TYPE;
541: lv_quantity_completed mrp_flow_demands.quantity_completed%TYPE;
542: lv_scheduled_comp_date mrp_flow_demands.scheduled_completion_date%TYPE;
543: lv_scheduled_start_date mrp_flow_demands.scheduled_start_date%TYPE;
544: lv_wip_entity_id mrp_flow_demands.wip_entity_id%TYPE;
545: lv_plan_level mrp_flow_demands.plan_level%TYPE;
546: lv_wip_supply_type mrp_flow_demands.wip_supply_type%TYPE;
547: lv_bill_sequence_id mrp_flow_demands.bill_sequence_id%TYPE;
548: lv_RN mrp_flow_demands.rn%TYPE;
541: lv_quantity_completed mrp_flow_demands.quantity_completed%TYPE;
542: lv_scheduled_comp_date mrp_flow_demands.scheduled_completion_date%TYPE;
543: lv_scheduled_start_date mrp_flow_demands.scheduled_start_date%TYPE;
544: lv_wip_entity_id mrp_flow_demands.wip_entity_id%TYPE;
545: lv_plan_level mrp_flow_demands.plan_level%TYPE;
546: lv_wip_supply_type mrp_flow_demands.wip_supply_type%TYPE;
547: lv_bill_sequence_id mrp_flow_demands.bill_sequence_id%TYPE;
548: lv_RN mrp_flow_demands.rn%TYPE;
549:
542: lv_scheduled_comp_date mrp_flow_demands.scheduled_completion_date%TYPE;
543: lv_scheduled_start_date mrp_flow_demands.scheduled_start_date%TYPE;
544: lv_wip_entity_id mrp_flow_demands.wip_entity_id%TYPE;
545: lv_plan_level mrp_flow_demands.plan_level%TYPE;
546: lv_wip_supply_type mrp_flow_demands.wip_supply_type%TYPE;
547: lv_bill_sequence_id mrp_flow_demands.bill_sequence_id%TYPE;
548: lv_RN mrp_flow_demands.rn%TYPE;
549:
550: TYPE NumTblTyp IS TABLE OF NUMBER;
543: lv_scheduled_start_date mrp_flow_demands.scheduled_start_date%TYPE;
544: lv_wip_entity_id mrp_flow_demands.wip_entity_id%TYPE;
545: lv_plan_level mrp_flow_demands.plan_level%TYPE;
546: lv_wip_supply_type mrp_flow_demands.wip_supply_type%TYPE;
547: lv_bill_sequence_id mrp_flow_demands.bill_sequence_id%TYPE;
548: lv_RN mrp_flow_demands.rn%TYPE;
549:
550: TYPE NumTblTyp IS TABLE OF NUMBER;
551: TYPE DateTblTyp IS TABLE OF DATE;
544: lv_wip_entity_id mrp_flow_demands.wip_entity_id%TYPE;
545: lv_plan_level mrp_flow_demands.plan_level%TYPE;
546: lv_wip_supply_type mrp_flow_demands.wip_supply_type%TYPE;
547: lv_bill_sequence_id mrp_flow_demands.bill_sequence_id%TYPE;
548: lv_RN mrp_flow_demands.rn%TYPE;
549:
550: TYPE NumTblTyp IS TABLE OF NUMBER;
551: TYPE DateTblTyp IS TABLE OF DATE;
552:
656:
657: END LOOP;
658:
659: FORALL k IN 1..lv_organization_id_tab.count
660: INSERT INTO MRP_FLOW_DEMANDS(
661: organization_id,
662: planned_quantity,
663: primary_item_id,
664: quantity_completed,
691:
692:
693:
694: /* This is to be made a dynamic query for New Patching Strategy
695: INSERT INTO MRP_FLOW_DEMANDS(
696: organization_id,
697: planned_quantity,
698: primary_item_id,
699: quantity_completed,