[Home] [Help]
363: rec.daily_rate))) new_quantity,
364: SUM(NVL(rec.old_order_quantity,0)) old_quantity,
365: sum(0) dos,
366: 0 cost
367: FROM msc_form_query list,
368: msc_trading_partners param,
369: msc_system_items msi,
370: msc_supplies rec,
371: msc_calendar_dates dates
529: null)) ,1) ,1)) new_quantity,
530: 0 old_quantity,
531: 0 dos,
532: 0 cost
533: FROM msc_form_query list,
534: msc_trading_partners param,
535: msc_demands mgr,
536: msc_calendar_dates dates
537: WHERE (arg_res_level = 1
656: DECODE(SUM (NVL(mgr.using_requirement_quantity, 1)) ,0 ,1 ,
657: SUM (NVL(mgr.using_requirement_quantity, 1))) old_quantity,
658: 0 dos,
659: 0 cost
660: FROM msc_form_query list,
661: msc_trading_partners param,
662: msc_demands mgr,
663: msc_calendar_dates dates
664: WHERE (arg_res_level = 1
709: avail.quantity_available new_quantity,
710: 0 old_quantity,
711: 0 dos,
712: 0 cost
713: FROM msc_form_query list,
714: msc_available_to_promise avail
715: WHERE avail.schedule_date < last_date
716: AND avail.organization_id = list.number2
717: AND avail.plan_id = list.number4
730: safety.organization_id old_quantity,
731: sum(safety.achieved_days_of_supply) dos,
732: sum(safety.safety_stock_quantity * item.standard_cost) cost
733: FROM msc_safety_stocks safety,
734: msc_form_query list ,
735: msc_system_items item
736: WHERE safety.period_start_date <= last_date
737: AND safety.organization_id = list.number2
738: AND safety.sr_instance_id = list.number3
768: sum(safety.TOTAL_UNPOOLED_SAFETY_STOCK) old_quantity,
769: sum(safety.target_days_of_supply) dos,
770: sum(safety.TARGET_SAFETY_STOCK * item.standard_cost) cost
771: FROM msc_safety_stocks safety,
772: msc_form_query list ,
773: msc_system_items item
774: WHERE safety.period_start_date <= last_date
775: AND safety.organization_id = list.number2
776: AND safety.sr_instance_id = list.number3
805: sum(0) old_quantity,
806: sum(safety.user_defined_dos) dos,
807: sum(safety.USER_DEFINED_SAFETY_STOCKS * item.standard_cost) cost
808: FROM msc_safety_stocks safety,
809: msc_form_query list,
810: msc_system_items item
811: WHERE safety.period_start_date <= last_date
812: AND safety.organization_id = list.number2
813: AND safety.sr_instance_id = list.number3
842: sum(safety.SUP_LTVAR_SS_PERCENT) old_quantity,
843: sum(safety.TRANSIT_LTVAR_SS_PERCENT) dos,
844: sum(safety.DEMAND_VAR_SS_PERCENT) cost
845: FROM msc_safety_stocks safety,
846: msc_form_query list,
847: msc_system_items item
848: WHERE safety.period_start_date <= last_date
849: AND safety.organization_id = list.number2
850: AND safety.sr_instance_id = list.number3
878: min(0) old_quantity,
879: min(lvl.min_quantity_dos) dos,
880: 0
881: FROM msc_inventory_levels lvl,
882: msc_form_query list
883: WHERE lvl.inventory_date <= last_date
884: AND lvl.organization_id = list.number2
885: AND lvl.sr_instance_id = list.number3
886: AND lvl.plan_id = list.number4
906: max(0) old_quantity,
907: max(lvl.max_quantity_dos) dos,
908: 0
909: FROM msc_inventory_levels lvl,
910: msc_form_query list
911: WHERE lvl.inventory_date<= last_date
912: AND lvl.organization_id = list.number2
913: AND lvl.sr_instance_id = list.number3
914: AND lvl.plan_id = list.number4
934: 0 old_quantity,
935: 0 dos,
936: 0
937: FROM msc_analysis_aggregate lvl,
938: msc_form_query list,
939: msc_plan_buckets mpb
940: WHERE lvl.record_type = 3
941: AND lvl.period_type = 1
942: AND lvl.plan_id = list.number4
968: 0 old_quantity,
969: 0 dos,
970: 0
971: FROM msc_analysis_aggregate lvl,
972: msc_form_query list,
973: msc_plan_buckets mpb
974: WHERE lvl.record_type = 3
975: AND lvl.period_type = 1
976: AND lvl.plan_id = list.number4
1001: 0,
1002: 0,
1003: 0,
1004: 0
1005: FROM msc_form_query list
1006: WHERE list.query_id = item_list_id
1007: ORDER BY
1008: 1, 2, 6, 4;
1009:
1223:
1224: cursor check_atp is
1225: SELECT msi.calculate_atp
1226: FROM msc_system_items msi,
1227: msc_form_query mfq
1228: WHERE msi.inventory_item_id = mfq.number1
1229: AND msi.organization_id = mfq.number2
1230: AND msi.plan_id = arg_plan_id
1231: AND msi.sr_instance_id = mfq.number3
2743: end if; -- if p_pf =-2
2744:
2745: -- dbms_output.put_line(' before inserting into temp table ');
2746:
2747: sql_stmt1 := 'INSERT INTO msc_form_query ( '||
2748: 'query_id, '||
2749: 'last_update_date, '||
2750: 'last_updated_by, '||
2751: 'creation_date, '||
3025: l_filter varchar2(255);
3026:
3027: CURSOR TRANS_C IS
3028: SELECT number1
3029: FROM msc_form_query
3030: WHERE query_id = l_query_id;
3031:
3032: CURSOR plan_start_c IS
3033: SELECT curr_start_date,
3049: l_dock_date_prof number := nvl(FND_PROFILE.Value('MSC_PO_DOCK_DATE_CALC_PREF'),1);
3050:
3051: cursor item_record is
3052: select distinct number2,number1
3053: from msc_form_query
3054: where query_id = p_item_query_id
3055: and number5 = p_item_id
3056: and number6 = p_org_id
3057: and number3 = p_inst_id ;
3059: v_item_id number;
3060:
3061: cursor item_supplier_record is
3062: select distinct number1,number8
3063: from msc_form_query
3064: where query_id = p_item_query_id;
3065:
3066:
3067: req_item_id NUMBER;
3082: l_isHp := true;
3083: end if;
3084:
3085:
3086: SELECT msc_form_query_s.nextval
3087: INTO l_query_id
3088: FROM dual;
3089:
3090: --< bug5449978--if plan is drp or unconstrained ascp or any plan where enforce_supplier capacity constraints is off, fallback to msc_supplies
3105: l_optimized_plan := 1;
3106: ELSE
3107: l_optimized_plan := 0;
3108: END IF;
3109: sql_stmt := 'INSERT INTO msc_form_query ( '||
3110: 'query_id, '||
3111: 'last_update_date, '||
3112: 'last_updated_by, '||
3113: 'creation_date, '||
3336: CLOSE item_supplier_record;
3337:
3338:
3339: ELSE -- an item
3340: -- need to get the real item_id, org_id from msc_form_query
3341:
3342: sql_stmt_1 := sql_stmt;
3343:
3344: open item_record;