[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT distinct pl.user_name
FROM msc_planners pl,
msc_system_items sys
WHERE sys.plan_id = p_plan_id
AND sys.organization_id = p_organization_id
AND sys.sr_instance_id = p_instance_id
AND sys.inventory_item_id = p_inventory_item_id
AND pl.organization_id = sys.organization_id
AND pl.sr_instance_id = sys.sr_instance_id
AND pl.planner_code = sys.planner_code;
SELECT exp.exception_detail_id,
exp.organization_id,
exp.sr_instance_id,
exp.inventory_item_id,
exp.exception_type,
mtp.organization_code,
mi.item_name,mi.description,
ml.meaning,
nvl(decode(exp.exception_type,
17, msc_get_name.project(
exp.number1,
exp.organization_id,
exp.plan_id,
exp.sr_instance_id),
18, msc_get_name.project(
exp.number1,
exp.organization_id,
exp.plan_id,
exp.sr_instance_id),
msc_get_name.project(
sup.project_id,
sup.organization_id,
sup.plan_id,
sup.sr_instance_id)), 'N/A'),
nvl(decode(exp.exception_type,
19, msc_get_name.project(
md2.project_id,
md2.organization_id,
md2.plan_id,
md2.sr_instance_id), null), 'N/A'),
nvl(decode(exp.exception_type,
17, msc_get_name.task(
exp.number2,
exp.number1,
exp.organization_id,
exp.plan_id,
exp.sr_instance_id),
18, msc_get_name.task(
exp.number2,
exp.number1,
exp.organization_id,
exp.plan_id,
exp.sr_instance_id),
msc_get_name.task(
sup.task_id,
sup.project_id,
sup.organization_id,
sup.plan_id,
sup.sr_instance_id)), 'N/A'),
nvl(decode(exp.exception_type,
19, msc_get_name.task(
md2.task_id,
md2.project_id,
md2.organization_id,
md2.plan_id,
md2.sr_instance_id), null), 'N/A'),
decode(exp.exception_type, 17, char1, 18, char1,
sup.planning_group),
DECODE(exp.EXCEPTION_TYPE, 2,NULL, 3, NULL, 6, NULL, 7, NULL,
10, sup.new_schedule_date, 18, NULL, 20, NULL,
27, md.using_assembly_demand_date,28, NULL,37, NULL,
24, md.using_assembly_demand_date,
25, md.using_assembly_demand_date,
26, md.using_assembly_demand_date,
49,msc_exp_wf.substitute_supply_date(exp.plan_id,exp.sr_instance_id,exp.supplier_id),
exp.DATE1),
DECODE(EXP.EXCEPTION_TYPE,1,EXP.DATE1, 2, EXP.DATE1, 3, EXP.DATE1,
6, EXP.DATE1, 7,EXP.DATE1, 17, EXP.DATE1, 18, EXP.DATE1,
20, EXP.DATE1, 28, EXP.DATE1, 37, EXP.DATE1,15, md2.using_assembly_demand_date,16, md2.using_assembly_demand_date,49,msc_exp_wf.demand_order_date(exp.plan_id,exp.sr_instance_id,exp.supplier_id),
NULL),
DECODE(exp.EXCEPTION_TYPE, 15, msc_exp_wf.demand_order_date(exp.plan_id,exp.sr_instance_id,exp.demand_id),16,msc_exp_wf.demand_order_date(exp.plan_id,exp.sr_instance_id,exp.demand_id),exp.date2),
DECODE(exp.EXCEPTION_TYPE, 9, sup.SCHEDULE_COMPRESS_DAYS,NULL),
decode(exp.exception_type,
37,nvl(exp.number4,0)-nvl(exp.quantity,0),
28,MSC_EXP_WF.SupplierCapacity(p_plan_id,exp.exception_detail_id),
49,msc_get_name.demand_quantity(exp.plan_id,exp.sr_instance_id,
exp.supplier_id),
exp.quantity),
decode(exp.exception_type, 12, sup.lot_number),
decode(exp.exception_type, 1, NULL, 2, NULL, 3, NULL,
9, NVL(sup.order_number,exp.number1),12, NULL,
13, md.order_number,
14, NVL(md.order_number,msc_get_name.designator(
md.schedule_designator_id)),
16, nvl(sup.order_number,sup.transaction_id),
17, NULL, 18, NULL , 20, NULL,
24, md.order_number, 25, md.order_number,
26, msc_get_name.designator(md.schedule_designator_id),
27, msc_get_name.designator(md.schedule_designator_id),
28, NULL, 37, NULL, 70,md.order_number,
sup.order_number),
sup_ml.meaning,
msc_get_name.item_name(
decode(exp.exception_type,
15, md2.inventory_item_id, 16, md2.inventory_item_id,
19, md2.inventory_item_id, 24, md.inventory_item_id,
25, md.inventory_item_id, 26, md.inventory_item_id,
27, md.inventory_item_id, 49, exp.number1,null),
null,null,null),
msc_get_name.item_desc(
decode(exp.exception_type,
15, md2.inventory_item_id, 16, md2.inventory_item_id,
19, md2.inventory_item_id, 24, md.inventory_item_id,
25, md.inventory_item_id, 26, md.inventory_item_id,
27, md.inventory_item_id, 49, exp.number1,null),
exp.organization_id,p_plan_id,exp.sr_instance_id),
decode(exp.exception_type,
15,md2.order_number, 19,md2.order_number,
24,md.order_number,25,md.order_number,
26,msc_get_name.designator(md.schedule_designator_id),
27,msc_get_name.designator(md.schedule_designator_id),
49,msc_get_name.demand_order_number(exp.plan_id,
exp.sr_instance_id, exp.supplier_id),
NULL) ,
null,
decode(exp.exception_type, 49,
msc_get_name.org_code(exp.number2, exp.sr_instance_id),
15,msc_get_name.org_code(md2.organization_id, md2.sr_instance_id),
16,msc_get_name.org_code(md2.organization_id, md2.sr_instance_id),null),
decode(exp.exception_type, 28, exp.quantity, 37, exp.quantity,
49, exp.quantity,
15, md.using_requirement_quantity,
16, md.using_requirement_quantity,
17, md.using_requirement_quantity,
18, md.using_requirement_quantity,
2, md.using_requirement_quantity,
3, msc_get_name.demand_quantity(exp.plan_id,exp.sr_instance_id,md2.demand_id),null),
decode(exp.exception_type, 12, null, 13, null, 14, null,
17, null, 18, null, 24, null, 25, null, 26, null,
27, null, 28, null, 37, exp.number2, exp.number1),
decode(exp.exception_type, 49, msc_exp_wf.demand_order_type(exp.plan_id,exp.sr_instance_id,exp.supplier_id),sup.order_type),
decode(exp.exception_type, 37, exp.number4,null),
exp.number1,
exp.number2
FROM msc_supplies sup,
msc_full_pegging mfp,
msc_demands md2,
msc_demands md,
msc_exception_details exp,
msc_system_items mi,
msc_trading_partners mtp,
mfg_lookups ml,
mfg_lookups sup_ml
WHERE exp.exception_type in
(1,2,3,6,7,8,9,10,12,13,14,15,16,17,18,19,20,24,25,26,27,28,37,49,70)
AND exp.plan_id = p_plan_id
AND exp.exception_detail_id = p_exception_id
AND sup.plan_id (+) = exp.plan_id
AND sup.transaction_id (+) = exp.number1
and sup.sr_instance_id(+) = exp.sr_instance_id
AND mfp.plan_id (+) = exp.plan_id
AND mfp.pegging_id (+) = exp.number2
and mfp.sr_instance_id(+) = exp.sr_instance_id
AND md2.plan_id (+) = mfp.plan_id
AND md2.demand_id (+) = mfp.demand_id
and md2.sr_instance_id(+) = mfp.sr_instance_id
AND md.plan_id (+) = exp.plan_id
AND md.demand_id (+) = exp.number1
and md.sr_instance_id(+) = exp.sr_instance_id
AND mi.inventory_item_id = exp.inventory_item_id
AND mi.organization_id = exp.organization_id
AND mi.plan_id = exp.plan_id
AND mi.sr_instance_id = exp.sr_instance_id
AND mtp.sr_tp_id = exp.organization_id
AND mtp.sr_instance_id = exp.sr_instance_id
AND mtp.partner_type = 3
and ml.lookup_type = 'MRP_EXCEPTION_CODE_TYPE'
and ml.lookup_code = exp.exception_type
and sup_ml.lookup_type(+) = 'MRP_ORDER_TYPE'
and sup_ml.lookup_code(+) = sup.order_type;
SELECT exp.exception_detail_id,
exp.organization_id,
exp.sr_instance_id,
exp.inventory_item_id,
exp.exception_type,
mtp.organization_code,
mi.item_name,mi.description,
ml.meaning,
nvl(decode(exp.exception_type,
17, msc_get_name.project(
exp.number1,
exp.organization_id,
exp.plan_id,
exp.sr_instance_id),
18, msc_get_name.project(
exp.number1,
exp.organization_id,
exp.plan_id,
exp.sr_instance_id),
msc_get_name.project(
sup.project_id,
sup.organization_id,
sup.plan_id,
sup.sr_instance_id)), 'N/A'),
nvl(decode(exp.exception_type,
19, msc_get_name.project(
md2.project_id,
md2.organization_id,
md2.plan_id,
md2.sr_instance_id), null), 'N/A'),
nvl(decode(exp.exception_type,
17, msc_get_name.task(
exp.number2,
exp.number1,
exp.organization_id,
exp.plan_id,
exp.sr_instance_id),
18, msc_get_name.task(
exp.number2,
exp.number1,
exp.organization_id,
exp.plan_id,
exp.sr_instance_id),
msc_get_name.task(
sup.task_id,
sup.project_id,
sup.organization_id,
sup.plan_id,
sup.sr_instance_id)), 'N/A'),
nvl(decode(exp.exception_type,
19, msc_get_name.task(
md2.task_id,
md2.project_id,
md2.organization_id,
md2.plan_id,
md2.sr_instance_id), null), 'N/A'),
decode(exp.exception_type, 17, char1, 18, char1,
sup.planning_group),
DECODE(exp.EXCEPTION_TYPE, 2,NULL, 3, NULL, 6, NULL, 7, NULL,
10, sup.new_schedule_date, 18, NULL, 20, NULL,
27, md.using_assembly_demand_date,28, NULL,37, NULL,
24, md.using_assembly_demand_date,
25, md.using_assembly_demand_date,
26, md.using_assembly_demand_date,
49,msc_exp_wf.substitute_supply_date(exp.plan_id,exp.sr_instance_id,exp.supplier_id),
exp.DATE1),
DECODE(EXP.EXCEPTION_TYPE,1,EXP.DATE1, 2, EXP.DATE1, 3, EXP.DATE1,
6, EXP.DATE1, 7,EXP.DATE1, 17, EXP.DATE1, 18, EXP.DATE1,
20, EXP.DATE1, 28, EXP.DATE1, 37, EXP.DATE1,
15, md2.using_assembly_demand_date,16, md2.using_assembly_demand_date,49,msc_exp_wf.demand_order_date(exp.plan_id,exp.sr_instance_id,exp.supplier_id),
NULL),
DECODE(EXP.EXCEPTION_TYPE,15,msc_exp_wf.demand_order_date(exp.plan_id,exp.sr_instance_id,exp.demand_id),16,msc_exp_wf.demand_order_date(exp.plan_id,exp.sr_instance_id,exp.demand_id),exp.date2),
DECODE(exp.EXCEPTION_TYPE, 9, sup.SCHEDULE_COMPRESS_DAYS,NULL),
decode(exp.exception_type,
37,nvl(exp.number4,0)-nvl(exp.quantity,0),
28,MSC_EXP_WF.SupplierCapacity(p_plan_id,exp.exception_detail_id),
49,msc_get_name.demand_quantity(exp.plan_id,exp.sr_instance_id,
exp.supplier_id),
exp.quantity),
decode(exp.exception_type, 12, sup.lot_number),
decode(exp.exception_type, 1, NULL, 2, NULL, 3, NULL,
9, NVL(sup.order_number,exp.number1),12, NULL,
13, md.order_number,
14, NVL(md.order_number,msc_get_name.designator(
md.schedule_designator_id)),
16, nvl(sup.order_number,sup.transaction_id),
17, NULL, 18, NULL , 20, NULL,
24, md.order_number, 25, md.order_number,
26, msc_get_name.designator(md.schedule_designator_id),
27, msc_get_name.designator(md.schedule_designator_id),
28, NULL, 37, NULL, 70,md.order_number, sup.order_number),
sup_ml.meaning,
msc_get_name.item_name(
decode(exp.exception_type,
15, md2.inventory_item_id, 16, md2.inventory_item_id,
19, md2.inventory_item_id, 24, md.inventory_item_id,
25, md.inventory_item_id, 26, md.inventory_item_id,
27, md.inventory_item_id, 49, exp.number1, null),
null,null,null),
msc_get_name.item_desc(
decode(exp.exception_type,
15, md2.inventory_item_id, 16, md2.inventory_item_id,
19, md2.inventory_item_id, 24, md.inventory_item_id,
25, md.inventory_item_id, 26, md.inventory_item_id,
27, md.inventory_item_id, 49, exp.number1, null),
exp.organization_id,p_plan_id,exp.sr_instance_id),
decode(exp.exception_type,
15,md2.order_number, 19,md2.order_number,
24,md.order_number,25,md.order_number,
26,msc_get_name.designator(md.schedule_designator_id),
27,msc_get_name.designator(md.schedule_designator_id),
49,msc_get_name.demand_order_number(exp.plan_id,
exp.sr_instance_id, exp.supplier_id),
NULL) ,
null,
decode(exp.exception_type, 49,
msc_get_name.org_code(exp.number2, exp.sr_instance_id),
15,msc_get_name.org_code(md2.organization_id, md2.sr_instance_id),
16,msc_get_name.org_code(md2.organization_id, md2.sr_instance_id),null),
decode(exp.exception_type, 28, exp.quantity, 37, exp.quantity,
49, exp.quantity,
15, md.using_requirement_quantity,
16, md.using_requirement_quantity,
17, md.using_requirement_quantity,
18, md.using_requirement_quantity,
2, md.using_requirement_quantity,
3, msc_get_name.demand_quantity(exp.plan_id,exp.sr_instance_id,md2.demand_id),null),
decode(exp.exception_type, 12, null, 13, null, 14, null,
17, null, 18, null, 24, null, 25, null, 26, null,
27, null, 28, null, 37, exp.number2, exp.number1),
decode(exp.exception_type, 49, msc_exp_wf.demand_order_type(exp.plan_id,exp.sr_instance_id,exp.supplier_id),sup.order_type),
decode(exp.exception_type, 37, exp.number4,null),
exp.number1,
exp.number2
FROM msc_supplies sup,
msc_full_pegging mfp,
msc_demands md2,
msc_demands md,
msc_exception_details exp,
msc_system_items mi,
msc_trading_partners mtp,
mfg_lookups ml,
mfg_lookups sup_ml
WHERE exp.exception_type in
(1,2,3,6,7,8,9,10,12,13,14,15,16,17,18,19,20,24,25,26,27,28,37,49,70)
AND exp.plan_id = p_plan_id
AND exp.exception_detail_id in (SELECT number1
FROM msc_form_query
WHERE query_id = p_query_id)
AND sup.plan_id (+) = exp.plan_id
AND sup.transaction_id (+) = exp.number1
and sup.sr_instance_id(+) = exp.sr_instance_id
AND mfp.plan_id (+) = exp.plan_id
AND mfp.pegging_id (+) = exp.number2
and mfp.sr_instance_id(+) = exp.sr_instance_id
AND md2.plan_id (+) = mfp.plan_id
AND md2.demand_id (+) = mfp.demand_id
and md2.sr_instance_id(+) = mfp.sr_instance_id
AND md.plan_id (+) = exp.plan_id
AND md.demand_id (+) = exp.number1
and md.sr_instance_id(+) = exp.sr_instance_id
AND mi.inventory_item_id = exp.inventory_item_id
AND mi.organization_id = exp.organization_id
AND mi.plan_id = exp.plan_id
AND mi.sr_instance_id = exp.sr_instance_id
AND mtp.sr_tp_id = exp.organization_id
AND mtp.sr_instance_id = exp.sr_instance_id
AND mtp.partner_type = 3
and ml.lookup_type = 'MRP_EXCEPTION_CODE_TYPE'
and ml.lookup_code = exp.exception_type
and sup_ml.lookup_type(+) = 'MRP_ORDER_TYPE'
and sup_ml.lookup_code(+) = sup.order_type;
SELECT exp.exception_detail_id,
exp.organization_id,
exp.sr_instance_id,
exp.inventory_item_id,
exp.exception_type,
mtp.organization_code,
mi.item_name,mi.description,
ml.meaning,
nvl(decode(exp.exception_type,
17, msc_get_name.project(
exp.number1,
exp.organization_id,
exp.plan_id,
exp.sr_instance_id),
18, msc_get_name.project(
exp.number1,
exp.organization_id,
exp.plan_id,
exp.sr_instance_id),
msc_get_name.project(
sup.project_id,
sup.organization_id,
sup.plan_id,
sup.sr_instance_id)), 'N/A'),
nvl(decode(exp.exception_type,
19, msc_get_name.project(
md2.project_id,
md2.organization_id,
md2.plan_id,
md2.sr_instance_id), null), 'N/A'),
nvl(decode(exp.exception_type,
17, msc_get_name.task(
exp.number2,
exp.number1,
exp.organization_id,
exp.plan_id,
exp.sr_instance_id),
18, msc_get_name.task(
exp.number2,
exp.number1,
exp.organization_id,
exp.plan_id,
exp.sr_instance_id),
msc_get_name.task(
sup.task_id,
sup.project_id,
sup.organization_id,
sup.plan_id,
sup.sr_instance_id)), 'N/A'),
nvl(decode(exp.exception_type,
19, msc_get_name.task(
md2.task_id,
md2.project_id,
md2.organization_id,
md2.plan_id,
md2.sr_instance_id), null), 'N/A'),
decode(exp.exception_type, 17, char1, 18, char1,
sup.planning_group),
DECODE(exp.EXCEPTION_TYPE, 2,NULL, 3, NULL, 6, NULL, 7, NULL,
10, sup.new_schedule_date, 18, NULL, 20, NULL,
27, md.using_assembly_demand_date,28, NULL,37, NULL,
24, md.using_assembly_demand_date,
25, md.using_assembly_demand_date,
26, md.using_assembly_demand_date,
49,msc_exp_wf.substitute_supply_date(exp.plan_id,exp.sr_instance_id,exp.supplier_id),
exp.DATE1),
DECODE(EXP.EXCEPTION_TYPE,1,EXP.DATE1, 2, EXP.DATE1, 3, EXP.DATE1,
6, EXP.DATE1, 7,EXP.DATE1, 17, EXP.DATE1, 18, EXP.DATE1,
20, EXP.DATE1, 28, EXP.DATE1, 37, EXP.DATE1,
15, md2.using_assembly_demand_date,16, md2.using_assembly_demand_date,49,msc_exp_wf.demand_order_date(exp.plan_id,exp.sr_instance_id,exp.supplier_id),
NULL),
DECODE(EXP.EXCEPTION_TYPE,15,msc_exp_wf.demand_order_date(exp.plan_id,exp.sr_instance_id,exp.demand_id),16,msc_exp_wf.demand_order_date(exp.plan_id,exp.sr_instance_id,exp.demand_id),exp.date2),
DECODE(exp.EXCEPTION_TYPE, 9, sup.SCHEDULE_COMPRESS_DAYS,NULL),
decode(exp.exception_type,
37,nvl(exp.number4,0)-nvl(exp.quantity,0),
28,MSC_EXP_WF.SupplierCapacity(p_plan_id,exp.exception_detail_id),
49,msc_get_name.demand_quantity(exp.plan_id,exp.sr_instance_id,
exp.supplier_id),
exp.quantity),
decode(exp.exception_type, 12, sup.lot_number),
decode(exp.exception_type, 1, NULL, 2, NULL, 3, NULL,
9, NVL(sup.order_number,exp.number1),12, NULL,
13, md.order_number,
14, NVL(md.order_number,msc_get_name.designator(
md.schedule_designator_id)),
16, nvl(sup.order_number,sup.transaction_id),
17, NULL, 18, NULL , 20, NULL,
24, md.order_number, 25, md.order_number,
26, msc_get_name.designator(md.schedule_designator_id),
27, msc_get_name.designator(md.schedule_designator_id),
28, NULL, 37, NULL, 70,md.order_number, sup.order_number),
sup_ml.meaning,
msc_get_name.item_name(
decode(exp.exception_type,
15, md2.inventory_item_id, 16, md2.inventory_item_id,
19, md2.inventory_item_id, 24, md.inventory_item_id,
25, md.inventory_item_id, 26, md.inventory_item_id,
27, md.inventory_item_id, 49, exp.number1,null),
null,null,null),
msc_get_name.item_desc(
decode(exp.exception_type,
15, md2.inventory_item_id, 16, md2.inventory_item_id,
19, md2.inventory_item_id, 24, md.inventory_item_id,
25, md.inventory_item_id, 26, md.inventory_item_id,
27, md.inventory_item_id, 49, exp.number1,null),
exp.organization_id,p_plan_id,exp.sr_instance_id),
decode(exp.exception_type,
15,md2.order_number, 19,md2.order_number,
24,md.order_number,25,md.order_number,
26,msc_get_name.designator(md.schedule_designator_id),
27,msc_get_name.designator(md.schedule_designator_id),
49,msc_get_name.demand_order_number(exp.plan_id,
exp.sr_instance_id, exp.supplier_id),
NULL) ,
null,
decode(exp.exception_type, 49,
msc_get_name.org_code(exp.number2, exp.sr_instance_id),
15,msc_get_name.org_code(md2.organization_id, md2.sr_instance_id),
16,msc_get_name.org_code(md2.organization_id, md2.sr_instance_id),null),
decode(exp.exception_type, 28, exp.quantity, 37, exp.quantity,
49, exp.quantity,
15, md.using_requirement_quantity,
16, md.using_requirement_quantity,
17, md.using_requirement_quantity,
18, md.using_requirement_quantity,
2, md.using_requirement_quantity,
3, msc_get_name.demand_quantity(exp.plan_id,exp.sr_instance_id,md2.demand_id),null),
decode(exp.exception_type, 12, null, 13, null, 14, null,
17, null, 18, null, 24, null, 25, null, 26, null,
27, null, 28, null, 37, exp.number2, exp.number1),
decode(exp.exception_type, 49, msc_exp_wf.demand_order_type(exp.plan_id,exp.sr_instance_id,exp.supplier_id),sup.order_type),
decode(exp.exception_type, 37, exp.number4,null),
exp.number1,
exp.number2
FROM msc_supplies sup,
msc_full_pegging mfp,
msc_demands md2,
msc_demands md,
msc_exception_details exp,
msc_system_items mi,
msc_trading_partners mtp,
mfg_lookups ml,
mfg_lookups sup_ml
WHERE exp.exception_type in
(1,2,3,6,7,8,9,10,12,13,14,15,16,17,18,19,20,24,25,26,27,28,37,49,70)
AND exp.plan_id = p_plan_id
AND sup.plan_id (+) = exp.plan_id
AND sup.transaction_id (+) = exp.number1
and sup.sr_instance_id(+) = exp.sr_instance_id
AND mfp.plan_id (+) = exp.plan_id
AND mfp.pegging_id (+) = exp.number2
and mfp.sr_instance_id(+) = exp.sr_instance_id
AND md2.plan_id (+) = mfp.plan_id
AND md2.demand_id (+) = mfp.demand_id
and md2.sr_instance_id(+) = mfp.sr_instance_id
AND md.plan_id (+) = exp.plan_id
AND md.demand_id (+) = exp.number1
and md.sr_instance_id(+) = exp.sr_instance_id
AND mi.inventory_item_id = exp.inventory_item_id
AND mi.organization_id = exp.organization_id
AND mi.plan_id = exp.plan_id
AND mi.sr_instance_id = exp.sr_instance_id
AND mtp.sr_tp_id = exp.organization_id
AND mtp.sr_instance_id = exp.sr_instance_id
AND mtp.partner_type = 3
and ml.lookup_type = 'MRP_EXCEPTION_CODE_TYPE'
and ml.lookup_code = exp.exception_type
and sup_ml.lookup_type(+) = 'MRP_ORDER_TYPE'
and sup_ml.lookup_code(+) = sup.order_type;
SELECT vend.partner_id,
vend.partner_name
FROM msc_trading_partners vend,
msc_exception_details exp,
msc_supplies ms
WHERE vend.partner_id = nvl(exp.supplier_id, ms.supplier_id)
AND vend.partner_type = 1
AND ms.plan_id (+) = exp.plan_id
AND ms.transaction_id (+) = exp.number1
AND exp.exception_detail_id = p_exception_id
AND exp.plan_id = p_plan_id;
SELECT vend.partner_id,
vend.partner_name,
site.partner_site_id,
site.tp_site_code
FROM msc_trading_partners vend,
msc_trading_partner_sites site,
msc_exception_details exp,
msc_supplies ms
WHERE site.partner_site_id = nvl(exp.supplier_site_id,ms.supplier_site_id)
AND vend.partner_id = nvl(exp.supplier_id, ms.supplier_id)
AND vend.partner_type = 1
AND ms.plan_id (+) = exp.plan_id
AND ms.transaction_id (+) = exp.number1
AND exp.exception_detail_id = p_exception_id
AND exp.plan_id = p_plan_id;
SELECT vend.partner_id,
vend.partner_name
FROM msc_trading_partners vend,
msc_demands rec,
msc_exception_details exp
WHERE vend.sr_tp_id = rec.customer_id
AND vend.sr_instance_id = rec.sr_instance_id
AND vend.partner_type = 2
AND rec.demand_id = exp.number1
AND rec.plan_id = exp.plan_id
AND exp.exception_detail_id = p_exception_id
AND exp.plan_id = p_plan_id;
SELECT decode(M2A_dblink,null,' ','@'||M2A_dblink),
decode(A2M_dblink,null,' ','@'||A2M_dblink)
FROM msc_apps_instances
WHERE instance_id = sr_instance_id;
l_org_selection number;
DeleteActivities(p_plan_id);
msc_util.msc_debug('deleted entries');
select nvl(PREPROCESSING_LEAD_TIME,0),nvl(FIXED_LEAD_TIME,0),nvl(POSTPROCESSING_LEAD_TIME,0)
from msc_system_items msi
where msi.sr_instance_id = instance_id
and msi.plan_id = p_plan_id
and msi.organization_id = lv_organization_id
and msi.inventory_item_id = lv_inventory_item_id;
select md.schedule_ship_date old_ship_date,
md.schedule_arrival_date old_arrival_date,
md.dmd_satisfied_date new_ship_date,
md.planned_arrival_date new_arrival_date,
md.request_date request_arrival_date,
md.request_ship_date,
md.promise_date promise_arrival_date,
md.promise_ship_date,
md.shipping_method_code new_ship_method,
md.orig_shipping_method_code old_ship_method,
md.orig_intransit_lead_time old_lead_time,
md.intransit_lead_time new_lead_time,
msc_get_name.customer_site(md.customer_site_id) customer_site,
msc_get_name.org_code(md.original_org_id,
md.original_inst_id) org_code,
msc_get_name.org_code(md.organization_id,md.sr_instance_id) to_org,
md.demand_id,
md.latest_acceptable_date,
msc_get_name.lookup_meaning('SYS_YES_NO',md.atp_override_flag) atp_override_flag
from msc_demands md,
msc_exception_details med
where med.plan_id = md.plan_id
and med.number1 = md.demand_id
and med.sr_instance_id = md.sr_instance_id
and med.plan_id = p_plan_id
and med.exception_detail_id = l_exception_id;
select compile_designator
into compile_designator
from msc_plans
where plan_id = p_plan_id;
select
nvl(sum(new_order_quantity),0)
into
lv_ava_quantity
from
msc_supplies
where
organization_id=lv_organization_id
and inventory_item_id=lv_inventory_item_id
and plan_id=p_plan_id
and sr_instance_id=instance_id
and nvl(disposition_status_type,-99)<>2
and new_order_quantity > 0
and to_date(new_schedule_date,'DD-MM-YY') <= to_date(to_char(from_date),'DD-MM-YY');
select
nvl(sum(new_order_quantity*(-1)),0)
into
lv_ava_quantity_temp
from
msc_supplies
where
organization_id=lv_organization_id
and inventory_item_id=lv_inventory_item_id
and plan_id=p_plan_id
and sr_instance_id=instance_id
and nvl(disposition_status_type,-99)<>2
and new_order_quantity < 0
and to_date(new_schedule_date,'DD-MM-YY') <= to_date(to_char(from_date),'DD-MM-YY');
select
nvl(sum(using_requirement_quantity),0)
into
lv_req_quantity
from
msc_demands
where
organization_id=lv_organization_id
and inventory_item_id=lv_inventory_item_id
and plan_id=p_plan_id
and sr_instance_id=instance_id
and to_date(using_assembly_demand_date,'DD-MM-YY') <= to_date(to_char(from_date),'DD-MM-YY');
select
nvl(sum(new_order_quantity),0)
into
lv_ava_quantity
from
msc_supplies
where
organization_id=lv_organization_id
and inventory_item_id=lv_inventory_item_id
and plan_id=p_plan_id
and sr_instance_id=instance_id
and project_id=sup_project_id
and nvl(task_id,-99)=nvl(sup_task_id,-99)
and nvl(disposition_status_type,-99)<>2
and new_order_quantity > 0
and to_date(new_schedule_date,'DD-MM-YY') <= to_date(to_char(from_date),'DD-MM-YY');
select
nvl(sum(new_order_quantity*(-1)),0)
into
lv_ava_quantity_temp
from
msc_supplies
where
organization_id=lv_organization_id
and inventory_item_id=lv_inventory_item_id
and plan_id=p_plan_id
and sr_instance_id=instance_id
and project_id=sup_project_id
and nvl(task_id,-99)=nvl(sup_task_id,-99)
and nvl(disposition_status_type,-99)<>2
and new_order_quantity < 0
and to_date(new_schedule_date,'DD-MM-YY') <= to_date(to_char(from_date),'DD-MM-YY');
select
nvl(sum(using_requirement_quantity),0)
into
lv_req_quantity
from
msc_demands
where
organization_id=lv_organization_id
and inventory_item_id=lv_inventory_item_id
and plan_id=p_plan_id
and sr_instance_id=instance_id
and project_id=sup_project_id
and nvl(task_id,-99)=nvl(sup_task_id,-99)
and to_date(using_assembly_demand_date,'DD-MM-YY') <= to_date(to_char(from_date),'DD-MM-YY');
PROCEDURE SelectPlanner( itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2 ) is
l_sr_status varchar2(10) :=
wf_engine.GetItemAttrText( itemtype => itemtype,
itemkey => itemkey,
aname => 'SR_RESULT');
msc_util.msc_debug('In the planner select logic');
wf_core.context('MSC_EXP_WF', 'SelectPlanner', itemtype, itemkey, actid, funcmode);
END SelectPlanner;
UPDATE MSC_SUPPLIES
SET old_order_quantity = new_order_quantity,
quantity_in_process = new_order_quantity,
implement_date = new_schedule_date,
implement_dock_date = new_dock_date,
implement_quantity =
decode(l_exception_type, 8, 0, new_order_quantity),
implement_status_code =
decode(order_type, 3,
decode(l_exception_type, 8, 7, implement_status_code),
implement_status_code),
implement_source_org_id = NULL,
implement_supplier_id = NULL,
implement_supplier_site_id = NULL,
implement_project_id = project_id,
implement_task_id = task_id,
implement_demand_class = demand_class,
load_type =
decode(order_type, 3,
decode(nvl(cfm_routing_flag,0), 3, 6, 4),
2, 16, 1, 20, NULL),
last_update_date = sysdate,
last_updated_by = l_user_id
WHERE transaction_id = l_transaction_id
AND plan_id = l_plan_id
returning organization_id, sr_instance_id, order_type into
l_org_id, l_sr_instance_id, l_order_type;
PROCEDURE DeleteActivities( arg_plan_id in number) IS
TYPE DelExpType is REF CURSOR;
delete_activities_c DelExpType;
SELECT DEcode(m2a_dblink, null, ' ', '@' || m2a_dblink)
FROM msc_apps_instances
WHERE instance_id in (select sr_instance_id
from msc_plan_organizations
where plan_id = p_plan_id)
UNION
select ' '
from dual;
'begin mrp_msc_exp_wf.deleteActivities'||l_db_link||
'(:arg_plan_id); end;';
sql_stmt := ' SELECT item_key, item_type ' ||
' FROM wf_items' || l_db_link ||
' WHERE item_type in (''MSCEXPWF'',''MRPEXWFS'') '||
' AND item_key like '''|| to_char(arg_plan_id) || '-%''';
OPEN delete_activities_c for sql_stmt;
FETCH DELETE_ACTIVITIES_C INTO l_item_key, l_item_type;
EXIT WHEN DELETE_ACTIVITIES_C%NOTFOUND;
update
wf_notifications
set end_date = sysdate
where group_id in
(select notification_id
from wf_item_activity_statuses
where item_type = l_item_type
and item_key = l_item_key
union
select notification_id
from wf_item_activity_statuses_h
where item_type = l_item_type
and item_key = l_item_key);
update wf_items
set end_date = sysdate
where item_type = l_item_type
and item_key = l_item_key;
update wf_item_activity_statuses
set end_date = sysdate
where item_type = l_item_type
and item_key = l_item_key;
update wf_item_activity_statuses_h
set end_date = sysdate
where item_type = l_item_type
and item_key = l_item_key;
CLOSE delete_activities_c;
msc_util.msc_debug('Error in delete activities:'|| to_char(sqlcode) || ':'
|| substr(sqlerrm,1,100));
END DeleteActivities;
SELECT DISTINCT convd.conversion_rate/convs.conversion_rate
FROM msc_uom_conversions convs,msc_uom_conversions convd,
msc_system_items msi, msc_item_suppliers sup
WHERE sup.supplier_id = partner_id
AND sup.supplier_site_id = partner_site_id
AND sup.inventory_item_id = item_id
AND sup.organization_id = org_id
AND sup.sr_instance_id = inst_id
AND sup.plan_id = arg_plan_id
AND sup.using_organization_id = -1
AND msi.plan_id = arg_plan_id
AND msi.inventory_item_id = item_id
AND msi.organization_id = org_id
AND msi.sr_instance_id = inst_id
AND convs.sr_instance_id = inst_id
AND convs.inventory_item_id = 0
AND convs.uom_code = msi.uom_code
AND convd.sr_instance_id = inst_id
AND convd.inventory_item_id = 0
AND convd.uom_code = sup.purchasing_unit_of_measure;
SELECT DISTINCT capacity,from_date,NVL(to_date,from_date)
FROM msc_supplier_capacities
WHERE plan_id = arg_plan_id
AND supplier_id = partner_id
AND supplier_site_id = partner_site_id
AND inventory_item_id = item_id
AND organization_id = p_org_id
AND sr_instance_id = inst_id
AND from_date <= NVL(p_to_date,p_from_date)
AND to_date > p_from_date;
SELECT DISTINCT from_date,NVL(to_date,from_date),
NVL(utilization_rate,quantity),supplier_id,
supplier_site_id, inventory_item_id, organization_id, sr_instance_id
INTO p_from_date,p_to_date,p_percent,p_partner_id,p_site_id,p_item_id,
p_org_id,p_inst_id
FROM msc_exception_details_v
WHERE plan_id = arg_plan_id
AND exception_id = arg_exception_id;
SELECT DISTINCT fence_days, tolerance_percentage
BULK COLLECT INTO suptol.fence,suptol.tolerance
FROM msc_supplier_flex_fences
WHERE plan_id = arg_plan_id
AND inventory_item_id = p_item_id
AND organization_id = p_org_id
AND sr_instance_id = p_inst_id
AND supplier_id = p_partner_id
AND supplier_site_id = p_site_id
ORDER BY fence_days;
PROCEDURE SelectSrUsers(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2) is
CURSOR BUYER_C(p_inventory_item_id in number,
p_plan_id in number,
p_org_id in number,
p_instance in number) IS
SELECT cont.name
FROM msc_partner_contacts cont, msc_system_items sys
WHERE sys.inventory_item_id = p_inventory_item_id
AND sys.organization_id = p_org_id
AND sys.sr_instance_id = p_instance
AND sys.plan_id = p_plan_id
AND cont.partner_id = sys.buyer_id
AND cont.sr_instance_id = sys.sr_instance_id
AND cont.partner_type = 4;
SELECT name
FROM msc_partner_contacts cont,
msc_exception_details exp,
msc_supplies ms
WHERE exp.exception_detail_id = p_exception_id
AND exp.plan_id = p_plan_id
AND cont.partner_site_id = nvl(exp.supplier_site_id,ms.supplier_site_id)
AND cont.partner_type = 1
AND cont.sr_instance_id = nvl(exp.sr_instance_id,ms.sr_instance_id)
AND ms.plan_id(+) = exp.plan_id
AND ms.transaction_id(+) = exp.number1;
SELECT so.salesrep_id
FROM msc_sales_orders so,
msc_demands mgr,
msc_exception_details exp
WHERE so.sales_order_number = mgr.order_number
AND so.sr_instance_id = mgr.sr_instance_id
AND mgr.plan_id = exp.plan_id
AND mgr.demand_id = exp.number1
AND exp.exception_detail_id = p_exception_id
AND exp.plan_id = p_plan_id;
SELECT so.salesrep_id
FROM msc_sales_orders so,
msc_demands mgr
WHERE so.sales_order_number = mgr.order_number
AND so.sr_instance_id = mgr.sr_instance_id
AND mgr.plan_id = p_plan_id
AND mgr.demand_id = p_demand_id;
SELECT name
FROM msc_partner_contacts cont, msc_demands mgr,
msc_exception_details exp
WHERE exp.exception_detail_id = p_exception_id
AND exp.plan_id = p_plan_id
AND cont.partner_id = mgr.customer_id
AND cont.partner_site_id = mgr.customer_site_id
AND cont.partner_type = 2
and cont.sr_instance_id = mgr.sr_instance_id
AND mgr.demand_id = exp.number1
AND mgr.plan_id = exp.plan_id;
SELECT name
FROM msc_partner_contacts cont, msc_demands mgr
where cont.partner_id = mgr.customer_id
AND cont.partner_site_id = mgr.customer_site_id
AND cont.partner_type = 2
and cont.sr_instance_id = mgr.sr_instance_id
AND mgr.demand_id = p_demand_id
AND mgr.plan_id = p_plan_id;
SELECT proj.manager_contact
FROM msc_projects proj
where proj.project_number = p_project_number
and proj.organization_id = l_org_id
and proj.sr_instance_id = l_instance_id
AND proj.plan_id = -1;
SELECT NVL(tasks.manager_contact,proj.manager_contact)
FROM msc_projects proj, msc_project_tasks tasks
WHERE tasks.task_number = p_task_number
AND proj.project_id = tasks.project_id
AND proj.project_number = p_project_number
and proj.organization_id = tasks.organization_id
and proj.sr_instance_id = tasks.sr_instance_id
AND proj.plan_id = tasks.plan_id
and proj.organization_id = l_org_id
and proj.sr_instance_id = l_instance_id
AND proj.plan_id = -1;
END SelectSrUsers;
select 1
from wf_item_activity_statuses
where item_type = p_item_type
and item_key = p_item_key
and activity_status = 'DEFERRED';
SELECT origination_type
FROM msc_demands
WHERE plan_id = p_plan_id
and sr_instance_id = p_inst_id
and demand_id = p_demand_id;
SELECT using_assembly_demand_date
FROM msc_demands
WHERE plan_id = p_plan_id
and sr_instance_id = p_inst_id
and demand_id = p_demand_id;
SELECT DMD_SATISFIED_DATE
FROM msc_demands
WHERE plan_id = p_plan_id
and sr_instance_id = p_inst_id
and demand_id = p_demand_id;