[Home] [Help]
216: END IF;
217:
218: select md.using_assembly_demand_date
219: into var_date
220: from msc_demands md,
221: msc_full_pegging mfp
222: where mfp.pegging_id = arg_pegging_id
223: and mfp.plan_id=arg_plan_id
224: and md.demand_id = mfp.demand_id
548: select order_number
549: from msc_supplies
550: where disposition_id in
551: (select disposition_id
552: from msc_demands
553: where plan_id = arg_plan_id
554: and demand_id = arg_disp_id)
555: and sr_instance_id = arg_instance_id
556: and plan_id = arg_plan_id
561: select wip_entity_name
562: from msc_supplies
563: where disposition_id in
564: (select disposition_id
565: from msc_demands
566: where plan_id = arg_plan_id
567: and demand_id = arg_disp_id)
568: and sr_instance_id = arg_instance_id
569: and plan_id = arg_plan_id
572:
573: cursor designator_c is
574: select desig.designator
575: from msc_designators desig,
576: msc_demands mgr
577: where desig.designator_id = mgr.schedule_designator_id
578: and mgr.demand_id = arg_disp_id
579: and mgr.sr_instance_id = arg_instance_id
580: and mgr.plan_id = arg_plan_id
582:
583:
584: BEGIN
585: -- note : arg_disp_id contains the demand_id,
586: -- since plan_id, org, instance, disposition cannot be unique in msc_demands
587: --bug 2038727
588:
589: if ((arg_demand_type is NULL and arg_supply_type is NULL) or
590: arg_disp_id is null or
1076: order_number varchar2(240);
1077:
1078: cursor C4 is
1079: select sales_order_number
1080: from msc_demands
1081: where demand_id = arg_demand_id;
1082: BEGIN
1083: if arg_demand_id is null
1084: then return null;
1477: CURSOR excp_exist(p_plan_id number,
1478: p_demand_id number) IS
1479: select 1
1480: from msc_exception_details med,
1481: msc_demands md
1482: where med.plan_id = md.plan_id
1483: and med.organization_id = md.organization_id
1484: and med.sr_instance_id = md.sr_instance_id
1485: and med.inventory_item_id = md.inventory_item_id
1533: and mtp.partner_type= 3
1534: and ms.batch_id is null
1535: and
1536: not exists (select 1 from msc_full_pegging mfp,
1537: msc_demands md,
1538: msc_supplies ms1
1539: where mfp.sr_instance_id = ms.sr_instance_id and
1540: mfp.plan_id = ms.plan_id and
1541: mfp.transaction_id =ms.transaction_id and
1550:
1551: cursor duplicate_comp (p_plan_id NUMBER, p_trx_id NUMBER) is
1552: select count(*)
1553: from msc_supplies mr,
1554: msc_demands md
1555: where mr.plan_id = md.plan_id
1556: and mr.sr_instance_id = md.sr_instance_id
1557: and mr.transaction_id = md.disposition_id
1558: and mr.transaction_id = arg_trx_id
1777: -- Shikyu item
1778: RETURN l_action;
1779: END IF;
1780:
1781: if arg_source_table = 'MSC_DEMANDS' and
1782: arg_plan_id <> -1 and -- plan_id <> -1
1783: arg_order_type in (30,6,54) then -- sales order
1784: OPEN internal_sales(arg_plan_id, -- plan_id
1785: arg_base_item_id, -- disposition_id
1839: l_action := msc_get_name.lookup_meaning('MRP_ACTIONS',6);
1840: end if;
1841: end if; -- if arg_rescheduled_flag = 1
1842: end if; -- if v_temp = 2 then -- cancel
1843: end if; -- if arg_source_table = 'MSC_DEMANDS'
1844:
1845:
1846: END IF;
1847: return l_action;
2359: p_demand_id NUMBER) return number IS
2360:
2361: CURSOR dmd_cur IS
2362: SELECT order_priority
2363: FROM msc_demands
2364: WHERE plan_id = p_plan_id
2365: and demand_id = p_demand_id;
2366: l_priority number;
2367:
2475: l_group_id NUMBER;
2476:
2477: CURSOR grp_cur IS
2478: SELECT group_id
2479: FROM msc_demands
2480: WHERE plan_id = p_plan_id
2481: AND sr_instance_id = p_inst_id
2482: AND demand_id = p_demand_id;
2483:
2486: -- opening the expensive dmd_cur.
2487:
2488: CURSOR dmd_cur IS
2489: SELECT sum(using_requirement_quantity)
2490: FROM msc_demands
2491: WHERE plan_id = p_plan_id
2492: and sr_instance_id = p_inst_id
2493: and group_id = l_group_id;
2494:
2523: dem.forecast_set_id ),
2524: msc_get_name.scenario_designator(dem.forecast_set_id, dem.plan_id, dem.organization_id, dem.sr_instance_id)
2525: || decode(msc_get_name.designator(dem.schedule_designator_id,dem.forecast_set_id ), null, null, '/'||msc_get_name.designator(dem.schedule_designator_id,dem.forecast_set_id )))),
2526: msc_get_name.designator(dem.schedule_designator_id))) order_number
2527: from msc_demands dem, msc_system_items msi
2528: where dem.plan_id = p_plan_id
2529: and dem.sr_instance_id = p_inst_id
2530: and dem.demand_id = p_demand_id
2531: AND dem.plan_id = msi.plan_id
2699: forecast_set_id,plan_id,organization_id,sr_instance_id),
2700: msc_get_name.designator(schedule_designator_id)
2701: )
2702: )
2703: from msc_demands
2704: where plan_id = p_plan_id
2705: and demand_id = p_demand_id;
2706: BEGIN
2707: if p_plan_id is null or
3609:
3610: cursor c_dem (l_plan number, l_dem number) is
3611: select schedule_arrival_date, planned_arrival_date, promise_date, request_date
3612: ,using_assembly_demand_date, planned_ship_date, origination_type
3613: from msc_demands
3614: where plan_id = l_plan
3615: and demand_id = l_dem;
3616:
3617: l_sched_arrival date;
5021: END check_cfm;
5022:
5023: FUNCTION load_type ( p_plan_type IN NUMBER
5024: , p_plan_id IN NUMBER
5025: , p_source_table IN VARCHAR2 -- MSC_SUPPLIES or MSC_DEMANDS
5026: , p_transaction_id IN NUMBER -- or demand_id
5027: , p_organization_id IN NUMBER
5028: , p_sr_instance_id IN NUMBER -- not reqd, but can hit U1 index with this
5029: , p_order_type IN NUMBER
5040:
5041: -- IRproject
5042: cursor iso_exists IS
5043: select 1
5044: from msc_demands
5045: where disposition_id = p_transaction_id
5046: and plan_id = p_plan_id
5047: and origination_type = 30
5048: and organization_id = p_source_organization_id
5055: and order_type = 2
5056: and sr_instance_id = p_sr_instance_id
5057: and transaction_id in
5058: (select disposition_id
5059: from msc_demands
5060: where demand_id = p_transaction_id
5061: and plan_id = p_plan_id
5062: and organization_id = p_organization_id
5063: and sr_instance_id = p_sr_instance_id) ;
5199: END IF;
5200: END IF; -- IF p_plan_type in (8)
5201: --pabram.srp.release.changes.ends
5202:
5203: ELSE -- p_source_table = 'MSC_DEMANDS'
5204: IF p_order_type in (30) THEN
5205: -- v_load_type := 30;
5206: IF p_plan_type in( 1, 8) THEN -- ASCP and SRP
5207: open ir_exists;
5856: CURSOR excp_exist(p_plan_id number,
5857: p_demand_id number) IS
5858: select 1
5859: from msc_exception_details med,
5860: msc_demands md
5861: where med.plan_id = md.plan_id
5862: and med.organization_id = md.organization_id
5863: and med.sr_instance_id = md.sr_instance_id
5864: and med.inventory_item_id = md.inventory_item_id
6059: -- Shikyu item
6060: RETURN l_action;
6061: END IF;
6062:
6063: if arg_source_table = 'MSC_DEMANDS' and
6064: arg_plan_id <> -1 and -- plan_id <> -1
6065: arg_order_type in (30,6) then -- sales order
6066: OPEN internal_sales(arg_plan_id, -- plan_id
6067: arg_base_item_id, -- disposition_id
6123: l_action := 6;
6124: end if;
6125: end if; -- if arg_rescheduled_flag = 1
6126: end if; -- if v_temp = 2 then -- cancel
6127: end if; -- if arg_source_table = 'MSC_DEMANDS'
6128:
6129:
6130: END IF;
6131: return l_action;
6260: COMPONENTS.WIP_SUPPLY_TYPE
6261: FROM
6262: MSC_SUPPLIES SUPPLIES,
6263: MSC_FULL_PEGGING PEGGING,
6264: MSC_DEMANDS DEMANDS,
6265: MSC_BOM_COMPONENTS COMPONENTS,
6266: MSC_PLANS PLANS
6267: WHERE
6268: supplies.transaction_id=pegging.transaction_id and