[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select alternate_routing_designator
into alt_rtg
from msc_routings b, msc_process_effectivity p
where p.plan_id = b.plan_id
and p.sr_instance_id= b.sr_instance_id
and p.routing_sequence_id = b.routing_sequence_id
and p.process_sequence_id = p_proc_seq_id
and p.plan_id = p_plan_id
and p.sr_instance_id = p_sr_instance_id
;
select alternate_bom_designator
into alt_bom
from msc_boms b, msc_process_effectivity p
where p.plan_id = b.plan_id
and p.sr_instance_id= p.sr_instance_id
and p.bill_sequence_id = b.bill_sequence_id
and p.process_sequence_id = p_proc_seq_id
and p.plan_id = p_plan_id
and p.sr_instance_id = p_sr_instance_id
;
PROCEDURE Update_Implement_Attrib(p_where_clause IN VARCHAR2,
p_employee_id IN NUMBER,
p_demand_class IN VARCHAR2,
p_def_job_class IN VARCHAR2,
p_def_firm_jobs IN VARCHAR2,
p_include_so IN VARCHAR2,
p_total_rows OUT NOCOPY NUMBER,
p_succ_rows OUT NOCOPY NUMBER,
p_error_rows OUT NOCOPY NUMBER
) IS
p_sql_stmt VARCHAR2(32767);
select curr_plan_type
from msc_plans
where plan_id = p_plan_id;
SELECT mp.employee_id
FROM msc_planners mp
WHERE mp.planner_code = p_planner_code
AND mp.organization_id = p_org_id
AND mp.sr_instance_id = p_inst_id
AND mp.current_employee_flag = 1;
select mtps.sr_tp_site_id
from msc_trading_partners mtp,
msc_trading_partner_sites mtps
where mtp.sr_tp_id = p_org_id
AND mtp.sr_instance_id = p_inst_id
AND mtp.partner_type =3
AND mtps.partner_id = mtp.partner_id;
p_valid := MSC_SELECT_ALL_FOR_RELEASE_PUB.child_supplies_onhand(
p_plan_id, p_transaction_id);
p_impl_as := msc_select_all_for_release_pub.get_implement_as(
p_order_type,
p_org_id,
p_source_org_id,
p_supplier_id,
p_planning_make_buy_code,
p_build_in_wip_flag,
p_purchasing_enabled_flag);
/* update employee_id to be the employee_id for the corresponding
planner_code in msc_system_items */
OPEN empl_C;
PROCEDURE update_success_supplies IS
CURSOR sr_item IS
SELECT msi.sr_inventory_item_id
FROM msc_system_items msi
WHERE msi.plan_id=p_plan_id
AND msi.organization_id = p_org_id
AND msi.sr_instance_id = p_inst_id
and msi.inventory_item_id = p_item_id;
update msc_supplies mr
set implement_wip_class_code =
nvl(p_wip_class_code, p_item_wip_class),
implement_status_code =
nvl(implement_status_code, p_def_job_status),
implement_demand_class =
nvl(mr.implement_demand_class, p_demand_class),
implement_job_name =
nvl(mr.implement_job_name, p_job_name),
implement_firm = nvl(mr.implement_firm,
DECODE(p_def_firm_jobs, 'Y', 1, mr.firm_planned_type)),
implement_alternate_routing = nvl(implement_alternate_routing,
get_alternate_rtg(mr.plan_id,mr.sr_instance_id,mr.process_seq_id)),
implement_alternate_bom = nvl(implement_alternate_bom,
get_alternate_bom(mr.plan_id,mr.sr_instance_id,mr.process_seq_id))
where transaction_id = p_transaction_id
and plan_id = p_plan_id;
update msc_supplies mr
SET implement_date = nvl(mr.implement_date,
decode(trunc(GREATEST(NVL(mr.firm_date,mr.new_schedule_date),
TRUNC(SYSDATE))),
trunc(mr.new_schedule_date),
mr.new_schedule_date,
msc_calendar.next_work_day(
mr.organization_id,
mr.sr_instance_id,
1,
greatest(nvl( mr.firm_date, mr.new_schedule_date ),
trunc(sysdate))
)
)
),
implement_quantity = nvl(implement_quantity,
DECODE(mr.disposition_status_type, 2,
decode(mr.order_type,3,
decode(nvl(mr.implemented_quantity,0),0,
mr.new_order_quantity, mr.implemented_quantity),
0),
GREATEST(NVL(mr.firm_quantity, mr.new_order_quantity)
- NVL(mr.quantity_in_process, 0)
- NVL(mr.implemented_quantity, 0), 0))),
release_status = 1,
implement_as = p_impl_as,
release_errors = null,
implement_employee_id = p_empl_id,
implement_location_id = p_loc_id,
implement_supplier_id = nvl(mr.implement_supplier_id,
DECODE(p_impl_as,
2, nvl(mr.implement_supplier_id, mr.source_supplier_id),
mr.implement_supplier_id)),
implement_supplier_site_id = DECODE(p_impl_as, 2,
nvl(mr.implement_supplier_site_id, mr.source_supplier_site_id),
mr.implement_supplier_site_id),
implement_source_org_id = DECODE(p_impl_as, 2,
DECODE(mr.source_organization_id, mr.organization_id, NULL,
mr.source_organization_id),
5, nvl(mr.source_organization_id, mr.organization_id), NULL),
implement_sr_instance_id = DECODE(p_impl_as, 2,
DECODE(mr.source_sr_instance_id, mr.sr_instance_id, NULL,
mr.source_sr_instance_id),
5, nvl(mr.source_sr_instance_id, mr.sr_instance_id), NULL),
reschedule_flag = DECODE(mr.order_type, 5, 2, 51, 2, 1),
implement_unit_number = decode(p_effectivity_control,2,
nvl(implement_unit_number,unit_number), null),
load_type = p_load_type,
status = 0,
applied = 2,
last_updated_by = fnd_global.user_id,
implement_status_code =
-- bug 4410222, For cancelled discrete jobs, set implement_status_code to 7
decode(order_type, 3,
decode(disposition_status_type,2, 7,
implement_status_code),
implement_status_code),
implement_dock_date = nvl(implement_dock_date,
decode(order_type, 2, new_dock_date ,
53, new_dock_date ,
implement_dock_date))
where transaction_id = p_transaction_id
and plan_id = p_plan_id;
update msc_supplies mr
set quantity_in_process =
DECODE(mr.number1,
-9999, mr.quantity_in_process,
GREATEST(0,
NVL(mr.quantity_in_process, 0) +
NVL(mr.implement_quantity, 0) -
NVL(mr.number1,0))),
number1 = DECODE(mr.order_type,
5, mr.implement_quantity,
51, mr.implement_quantity,
mr.number1),
implement_project_id =
nvl(mr.implement_project_id,mr.project_id),
implement_task_id =
nvl(mr.implement_task_id,mr.task_id),
implement_ship_date = nvl(implement_ship_date,
decode(p_load_type, 32, new_ship_date, -- internal req
256, new_ship_date, -- internal repair
implement_ship_date)),
implement_dock_date = nvl(implement_dock_date,
decode(p_load_type, 32, new_dock_date ,
256, new_ship_date, -- internal repair
implement_dock_date)),
implement_firm = nvl(implement_firm,
decode(p_load_type, 32, firm_planned_type,
256, firm_planned_type, -- internal repair
implement_firm))
where transaction_id = p_transaction_id
and plan_id = p_plan_id;
update msc_supplies msrs
SET implement_date = nvl(msrs.implement_date,
msrs.last_unit_completion_date),
implement_daily_rate = nvl(msrs.implement_daily_rate, msrs.daily_rate),
implement_quantity = nvl(msrs.implement_daily_rate, msrs.daily_rate),
implement_demand_class = nvl(msrs.implement_demand_class,
p_demand_class),
implement_line_id = nvl(msrs.implement_line_id, msrs.line_id),
implement_processing_days = nvl(msrs.implement_processing_days,
msc_calendar.days_between(msrs.organization_id,
msrs.sr_instance_id,
1,
msrs.last_unit_completion_date,
nvl(msrs.first_unit_completion_date,
msrs.new_schedule_date)
) +1),
load_type = p_load_type,
release_errors = null,
release_status = 1,
implement_as =4,
status = 0,
applied = 2,
last_updated_by = fnd_global.user_id
where transaction_id = p_transaction_id
and plan_id = p_plan_id;
END update_success_supplies;
Procedure update_sup_rel_error IS
BEGIN
update msc_supplies
SET implement_as = NULL,
implement_quantity = NULL,
implement_date = NULL,
release_status = 2,
release_errors = p_rel_error
where transaction_id = p_transaction_id
and plan_id = p_plan_id;
END update_sup_rel_error;
Procedure update_dmd_rel_error IS
BEGIN
update msc_demands
set release_errors = p_rel_error,
implement_org_id = null,
implement_instance_id = null,
implement_date = NULL,
implement_ship_date = NULL,
implement_arrival_date = NULL,
implement_earliest_date = NULL,
implement_firm = NULL,
reschedule_flag = NULL,
load_type = null,
release_status = 2
where plan_id = p_plan_id
and demand_id = p_transaction_id
and sr_instance_id = p_inst_id;
END update_dmd_rel_error;
PROCEDURE update_dmd_success IS
p_impl_date date;
msc_rel_wf.update_so_dates(p_plan_id, p_transaction_id, p_inst_id,
p_impl_date, v_ship_date, v_arrival_date,
v_earliest_date);
update msc_demands
set implement_org_id = organization_id,
implement_instance_id = sr_instance_id,
implement_date = nvl(implement_date,planned_ship_date),
implement_ship_date = v_ship_date,
implement_arrival_date = v_arrival_date,
implement_earliest_date = v_earliest_date,
implement_firm = nvl(implement_firm, org_firm_flag),
load_type = 30,
reschedule_flag = 1,
release_status = 1,
status = 0,
applied =2,
last_updated_by = fnd_global.user_id,
release_errors = NULL
where plan_id = p_plan_id
and demand_id = p_transaction_id
and sr_instance_id = p_inst_id;
END update_dmd_success;
SELECT instance_id,null
BULK COLLECT INTO g_instance_id,g_job_prefix
FROM msc_apps_instances;
'select ' ||
'plan_id, ' ||
'transaction_id, ' ||
'action, ' ||
'cfm_routing_flag, ' ||
'bom_item_type, ' ||
'release_time_fence_code, ' ||
'in_source_plan, ' ||
'inventory_item_id, ' ||
'build_in_wip_flag, ' ||
'order_type, ' ||
'source_organization_id, ' ||
'organization_id, ' ||
'purchasing_enabled_flag, ' ||
'source_vendor_id, ' ||
'planning_make_buy_code, ' ||
'build_in_wip_flag, '||
-- 'effectivity_control, ' ||
'planner_code, ' ||
'sr_instance_id, ' ||
'new_due_date, ' ||
'project_id, ' ||
'task_id, ' ||
'unit_number, ' ||
'lots_exist, '||
'quantity_rate '||
' from '||msc_get_name.get_order_view(p_plan_type, p_plan_id) ||
' where ' || p_where_clause ||
' and order_type IN (1, 2, 3, 5, 13, 51, 53, 76,77,78)'||
' and source_table = ''MSC_SUPPLIES''' ||
' and nvl(release_time_fence_code,-1) <> 7' ||
-- Shikyu items should not be processed
' and nvl(release_status,2) = 2';
update_sup_rel_error;
update_success_supplies;
'SELECT ' ||
'plan_id, ' ||
'transaction_id, ' ||
'sr_instance_id ' ||
'from msc_orders_v mo' ||
' where ' || p_where_clause ||
' and nvl(release_status,2) = 2' ||
' and order_type = 30'||
' and source_table = ''MSC_DEMANDS'''||
' and exists (select 1 from msc_exception_details med ' ||
' where med.plan_id = mo.plan_id ' ||
' and med.exception_type = 70 ' ||
' and med.organization_id = mo.organization_id '||
' and med.sr_instance_id = mo.sr_instance_id '||
' and med.inventory_item_id = mo.inventory_item_id '||
' and med.number1 = mo.transaction_id) ';
update_dmd_rel_error;
update_dmd_success;
END Update_Implement_Attrib;
SELECT 1
from msc_full_pegging mfp1,
msc_full_pegging mfp2,
msc_supplies ms
where mfp1.plan_id = p_plan_id
and mfp1.transaction_id = p_transaction_id
and mfp2.plan_id = mfp1.plan_id
and mfp2.prev_pegging_id = mfp1.pegging_id
and ms.plan_id = mfp2.plan_id
and ms.transaction_id = mfp2.transaction_id
and ms.sr_instance_id = mfp2.sr_instance_id
and (ms.order_type in (1,2,8,11,12) or -- purchased/transferred supply
(ms.order_type = 5 and -- not make planned order
nvl(ms.source_organization_id,-1) <> ms.organization_id));