[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NVL(mwdo.orders_default_job_status, UNRELEASED_NO_CHARGES),
NVL(mwdo.job_class_code, var_wip_class_code),
NVL(mwdo.orders_firm_jobs, 'N')
FROM mrp_workbench_display_options mwdo,
fnd_user fu
WHERE fu.employee_id = var_planner_employee_id
AND fu.start_date <= sysdate
AND NVL(fu.end_date, sysdate) >= sysdate
AND mwdo.user_id = fu.user_id;
SELECT mr.rowid, mr.organization_id, mr.inventory_item_id,
mr.new_schedule_date, mr.new_order_quantity,
msi.primary_uom_code,
decode(msi.planner_code,NULL,mplm.employee_id,mpl.employee_id),
DECODE(mr.source_organization_id, mr.organization_id, MAKE, BUY) ,
nvl(mr.implement_project_id,mr.project_id)
FROM bom_calendar_dates cal1,
bom_calendar_dates cal2,
mtl_planners mplm,
mtl_planners mpl,
mtl_parameters mparam,
mtl_system_items master_msi,
mtl_system_items msi,
mrp_system_items rsi,
mrp_recommendations mr,
mrp_plan_organizations_v mpo
WHERE mpo.organization_id = arg_org_id
AND mpo.compile_designator = arg_plan_name
AND mr.organization_id = mpo.planned_organization
AND mr.compile_designator = mpo.compile_designator
AND mr.order_type = PLANNED_ORDER
AND NVL(mr.schedule_compression_days, 0) = 0
AND mr.new_order_placement_date BETWEEN TRUNC(var_start_date)
AND cal2.calendar_date
AND msi.organization_id = mr.organization_id
AND msi.inventory_item_id = mr.inventory_item_id
AND msi.bom_item_type = STANDARD_ITEM
AND NVL(msi.release_time_fence_code, NO_AUTO_RELEASE) NOT IN
(NO_AUTO_RELEASE, NO_KANBAN_RELEASE)
AND ((msi.build_in_wip_flag = 'Y'
AND NVL(msi.repetitive_planning_flag, 'N') = 'N'
AND DECODE(mr.source_organization_id, mr.organization_id, MAKE,
BUY) = MAKE)
OR (msi.purchasing_enabled_flag = 'Y'
AND DECODE(mr.source_organization_id, mr.organization_id, MAKE,
BUY) = BUY))
AND NOT EXISTS ( SELECT 1 FROM bom_operational_routings
WHERE assembly_item_id = mr.inventory_item_id
AND organization_id = mr.organization_id
AND nvl(alternate_routing_designator,'-23453') =
nvl(mr.alternate_routing_designator,'-23453')
AND cfm_routing_flag = 1)
AND rsi.organization_id = mr.organization_id
AND rsi.compile_designator = mr.compile_designator
AND rsi.inventory_item_id = mr.inventory_item_id
AND NVL(rsi.in_source_plan, SYS_NO) <> SYS_YES
AND master_msi.organization_id = mparam.master_organization_id
AND master_msi.inventory_item_id = msi.inventory_item_id
AND mpl.organization_id (+) = msi.organization_id
AND mpl.planner_code (+) = NVL(msi.planner_code, MAGIC_STRING)
AND mplm.organization_id (+)= master_msi.organization_id
AND mplm.planner_code (+)= NVL(master_msi.planner_code, MAGIC_STRING)
AND mparam.organization_id = mr.organization_id
AND cal1.calendar_code = mparam.calendar_code
AND cal1.exception_set_id = mparam.calendar_exception_set_id
AND cal1.calendar_date = TRUNC(var_start_date)
AND cal2.calendar_code = cal1.calendar_code
AND cal2.exception_set_id = cal1.exception_set_id
AND cal2.seq_num = cal1.next_seq_num +
NVL(DECODE(msi.release_time_fence_code,
1, msi.cumulative_total_lead_time,
2, msi.cum_manufacturing_lead_time,
3, msi.full_lead_time,
4, msi.release_time_fence_days,
0),
0)
ORDER BY 2;
SELECT mp.curr_plan_type,
wip_job_schedule_interface_s.nextval,
mrp_workbench_query_s.nextval,
DECODE(UPPER(arg_use_start_date),
'Y', mp.plan_start_date, 'N', sysdate, sysdate),
sched.demand_class
INTO var_plan_type,
var_wip_group_id, var_po_batch_number,
var_start_date, var_demand_class
FROM mrp_plans mp,
mrp_schedule_designators sched
WHERE sched.organization_id (+)= mp.organization_id
AND sched.schedule_designator (+)= mp.compile_designator
AND mp.organization_id = arg_org_id
AND mp.compile_designator = arg_plan_name;
SELECT wp.default_discrete_class
INTO var_wip_class_code
FROM wip_parameters wp
WHERE wp.organization_id = var_org_id;
SELECT loc.location_id
INTO var_location_id
FROM hr_locations loc,
hr_organization_units unit
WHERE unit.organization_id = var_org_id
AND unit.location_id = loc.location_id;
SELECT wp.default_discrete_class
INTO var_wip_class_code
FROM wip_parameters wp
WHERE wp.organization_id = var_org_id;
UPDATE mrp_recommendations
SET old_order_quantity = new_order_quantity,
quantity_in_process = new_order_quantity,
implement_date = new_schedule_date,
implement_quantity = new_order_quantity,
implement_firm = DECODE(var_firm_jobs,
'Y', 1,
2),
implement_job_name = var_job_prefix||to_char(wip_job_number_s.nextval),
implement_status_code = var_impl_status_code,
implement_wip_class_code = NVL(var_wip_class_code,var_job_prefix),
implement_source_org_id = NULL,
implement_vendor_id = NULL,
implement_vendor_site_id = NULL,
implement_project_id = project_id,
implement_task_id = task_id,
implement_demand_class = var_demand_class,
load_type = WIP_DIS_MASS_LOAD,
implement_as = WIP_DISCRETE_JOB
WHERE rowid = var_rowid;
SELECT count(*)
INTO var_count
FROM hr_employees_current_v emp
WHERE emp.employee_id = NVL(var_planner_employee_id, NULL_VALUE);
SELECT param.organization_code, msik.concatenated_segments,
msik.planner_code
INTO var_org_code, var_item, var_planner_code
FROM mtl_system_items_kfv msik,
mtl_parameters param,
mrp_recommendations mr
WHERE mr.rowid = var_rowid
AND msik.organization_id = mr.organization_id
AND msik.inventory_item_id = mr.inventory_item_id
AND param.organization_id = mr.organization_id;
UPDATE mrp_recommendations
SET old_order_quantity = new_order_quantity,
quantity_in_process = new_order_quantity,
implement_date = new_schedule_date,
implement_quantity = new_order_quantity,
implement_firm = firm_planned_type,
implement_dock_date = new_dock_date,
implement_employee_id = var_planner_employee_id,
implement_uom_code = var_primary_uom_code,
implement_location_id = var_location_id,
implement_source_org_id = source_organization_id,
implement_vendor_id = source_vendor_id,
implement_vendor_site_id = source_vendor_site_id,
implement_project_id = project_id,
implement_task_id = task_id,
implement_demand_class = NULL,
load_type = PO_MASS_LOAD,
implement_as = PO_REQUISITION
WHERE rowid = var_rowid;