[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select curr_plan_type
from msc_plans
where plan_id = v_plan_id;
SELECT
mr.transaction_id,
mr.sr_instance_id,
mr.plan_id,
mr.organization_id,
mr.inventory_item_id,
nvl(mr.supplier_id,mt.modeled_supplier_id),
nvl(mr.supplier_site_id,mt.modeled_supplier_site_id),
nvl(mr.source_supplier_id,mt.modeled_supplier_id),
nvl(mr.source_supplier_site_id,mt.modeled_supplier_site_id),
mr.new_schedule_date,
mr.new_order_quantity,
mr.order_type,
decode(mr.order_type,
PLANNED_ORDER, decode(decode(mr.sr_instance_id,
mr.source_sr_instance_id, DECODE(mr.source_organization_id,mr.organization_id,MAKE,BUY),
BUY),
MAKE, decode(CFM_ROUTING_FLAG, 3,5,1),
BUY, 8),
PLANNED_NEW_BUY_ORDER, decode(decode(mr.sr_instance_id,
mr.source_sr_instance_id, DECODE(mr.source_organization_id,mr.organization_id,MAKE,BUY),
BUY),
MAKE, decode(CFM_ROUTING_FLAG, 3,5,1),
BUY, 8),
PO_REQUISITION, 16,
PURCHASE_ORDER, 20,
WIP_DISCRETE_JOB, decode(CFM_ROUTING_FLAG, 3,6,4)),
msi.uom_code,
mpl.employee_id,
decode(mr.sr_instance_id,mr.source_sr_instance_id,
DECODE(mr.source_organization_id,mr.organization_id,1,2),
2),
msi.sr_inventory_item_id,
nvl(mr.implement_project_id,mr.project_id),
mr.disposition_status_type
,cal2.calendar_date -- date also to print 10325294
FROM msc_calendar_dates cal1,
msc_calendar_dates cal2,
msc_planners mpl,
msc_trading_partners mparam,
msc_system_items msi,
msc_system_items rsi,
msc_supplies mr,
msc_plan_organizations_v mpo,
msc_plans mps,
msc_trading_partners mt
WHERE -- mpo.organization_id = arg_org_id
mpo.plan_id = arg_plan_id
-- AND mpo.sr_instance_id = arg_instance_id
AND mr.sr_instance_id = mpo.sr_instance_id
AND mr.organization_id = mpo.planned_organization
AND mr.plan_id = mpo.plan_id
AND mr.plan_id = mps.plan_id
--for bug#2881012
AND ( mr.order_type in (PLANNED_ORDER, PLANNED_NEW_BUY_ORDER)
OR ( mr.order_type IN (PURCHASE_ORDER,PO_REQUISITION,WIP_DISCRETE_JOB)
and ( mps.release_reschedules = SYS_YES or NVL(mps.INCLUDE_RESCHEDULES, SYS_NO) = SYS_YES )
and ( mr.disposition_status_type = CANCEL -- 2 is cancel
or ( mr.reschedule_flag = RESCHEDULE -- 2 => reschedule
and mr.new_schedule_date <> mr.old_schedule_date
and (nvl(mr.reschedule_days,0) <> 0) -- 8726490 , 9064626
)
)
)
)
AND (NVL(mr.schedule_compress_days, 0) = 0 OR
mr.schedule_compress_days <= v_comp_days_tol )
AND decode(nvl(mr.reschedule_days,0),0,mr.new_order_placement_date,decode(sign(trunc(mr.new_order_placement_date)-trunc(mr.old_order_placement_date)),1,mr.old_order_placement_date,-1,mr.new_order_placement_date))
<= TRUNC(cal2.calendar_date) --bug8351869
AND msi.organization_id = mr.organization_id
AND msi.sr_instance_id = mr.sr_instance_id
AND msi.inventory_item_id = mr.inventory_item_id
AND msi.plan_id = -1
AND msi.bom_item_type = 4
AND NVL(msi.release_time_fence_code, 5) NOT IN (5,6,7)
/* for bug#2881012 following is not for reschedules. Hence
reschedules skip these filters with mr.order_type <> PLANNED_ORDER*/
AND
( mr.order_type not in (PLANNED_ORDER,PLANNED_NEW_BUY_ORDER)
OR (
( msi.build_in_wip_flag = 1
AND msi.repetitive_type = 1 /* 1:NO, 2:YES */
AND decode(mr.sr_instance_id,mr.source_sr_instance_id,
DECODE(mr.source_organization_id, mr.organization_id, 1,2)
,2) = 1
)
OR ( msi.purchasing_enabled_flag = 1
AND decode(mr.sr_instance_id,mr.source_sr_instance_id,
DECODE(mr.source_organization_id, mr.organization_id, 1, 2)
,2) = 2
)
)
)
AND rsi.organization_id = mr.organization_id
AND rsi.plan_id = mr.plan_id
AND rsi.sr_instance_id = mr.sr_instance_id
AND rsi.inventory_item_id = mr.inventory_item_id
AND ( NVL(rsi.in_source_plan, 2) <> 1
OR
mr.transaction_id in (
select a.transaction_id from msc_supplies a, msc_supplies b,MSC_DESIGNATORS d, msc_system_items e
where a.schedule_designator_id is not null
and a.schedule_designator_id = b.schedule_designator_id
and a.organization_id = mr.organization_id
and a.organization_id = b.organization_id
and a.transaction_id= b.transaction_id
and a.sr_instance_id =b.sr_instance_id
and b.plan_id=-1
and a.inventory_item_id =e.inventory_item_id
and a.plan_id = e.plan_id
and a.sr_instance_id = e.sr_instance_id
and a.organization_id = e.organization_id
and NVL(e.in_source_plan, 2) =1
and a.plan_id=mr.plan_id
and b.SCHEDULE_ORIGINATION_TYPE =1
and a.schedule_designator_id = d.DESIGNATOR_ID
and d.designator not in (SELECT DISTINCT compile_designator
FROM MSC_PLANS
where sr_instance_id=a.sr_instance_id )
))
AND decode(mps.curr_plan_type,
SRP_PLAN,SYS_YES,
DRP_PLAN, SYS_YES,
MRP_PLAN, decode(lv_plan_release_profile,
SYS_YES, SYS_YES,
decode(rsi.mrp_planning_code,
MRP_PLANNED_ITEM, SYS_YES,
MPPMRP_PLANNED_ITEM, SYS_YES,
SYS_NO
)
),
MPS_PLAN, decode(lv_plan_release_profile,
SYS_YES, decode(rsi.mrp_planning_code,
MRP_PLANNED_ITEM, SYS_NO,
SYS_YES
),
decode(rsi.mrp_planning_code,
MPS_PLANNED_ITEM, SYS_YES,
MPPMPS_PLANNED_ITEM,SYS_YES,
SYS_NO
)
),
MPP_PLAN, decode(rsi.mrp_planning_code,
MPS_PLANNED_ITEM, SYS_NO,
MRP_PLANNED_ITEM, SYS_NO,
SYS_YES
),
RP_MRP_PLAN, decode(lv_plan_release_profile,
SYS_YES, SYS_YES,
decode(rsi.mrp_planning_code,
MRP_PLANNED_ITEM, SYS_YES,
MPPMRP_PLANNED_ITEM, SYS_YES,
SYS_NO
)
), --RP Auto Rel
RP_MPS_PLAN, decode(lv_plan_release_profile,
SYS_YES, decode(rsi.mrp_planning_code,
MRP_PLANNED_ITEM, SYS_NO,
SYS_YES
),
decode(rsi.mrp_planning_code,
MPS_PLANNED_ITEM, SYS_YES,
MPPMPS_PLANNED_ITEM,SYS_YES,
SYS_NO
)
), --RP Auto Rel
RP_MPP_PLAN, decode(rsi.mrp_planning_code,
MPS_PLANNED_ITEM, SYS_NO,
MRP_PLANNED_ITEM, SYS_NO,
SYS_YES
), SYS_NO --RP Auto Rel
) = SYS_YES
AND mpl.organization_id (+) = msi.organization_id
AND mpl.planner_code (+) = NVL(msi.planner_code, MAGIC_STRING)
AND mpl.sr_instance_id(+) = msi.sr_instance_id
AND mparam.sr_tp_id = mr.organization_id
AND mparam.sr_instance_id= mr.sr_instance_id
AND mparam.partner_type= 3
AND cal1.sr_instance_id= mr.sr_instance_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.sr_instance_id = mr.sr_instance_id
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(rsi.release_time_fence_code,
1, rsi.cumulative_total_lead_time,
2, rsi.cum_manufacturing_lead_time,
3, rsi.full_lead_time,
4, rsi.release_time_fence_days,
0), -- Selecting the rtf days from planned data instead of collected data 10325294
0)
-- bug fix for 2261963 to filter planned orders that have already been released --
AND NVL(mr.implemented_quantity, 0) + NVL(mr.quantity_in_process, 0)
< mr.new_order_quantity
AND mt.sr_instance_id(+) = mr.source_sr_instance_id
AND mt.sr_tp_id(+) = mr.source_organization_id
AND mt.partner_type(+) = 3
-- AND (mr.releasable = MSC_Rel_Plan_PUB.RELEASABLE or mr.releasable is null )
AND ( ( NVL(mps.auto_release_method,-1) IN (3,4) AND mr.releasable = 99) -- Query based autorel
OR
( NVL(mps.auto_release_method,-1) NOT IN (3,4) AND NVL(mr.releasable,0) =0 )-- other autorel
)
AND mr.batch_id is NULL
-- shikyu changes
AND not exists (select 1 from msc_system_items msi1 , msc_trading_partners mtp
where msi1.inventory_item_id = mr.inventory_item_id
and msi1.organization_id = mr.organization_id
and msi1.plan_id = mr.plan_id
AND msi1.sr_instance_id = mr.sr_instance_id
and nvl(msi1.release_time_fence_code,-1) = 7
and mtp.sr_tp_id = msi1.organization_id
and mtp.sr_instance_id = msi1.sr_instance_id
and mtp.partner_type=3
and (mtp.modeled_supplier_id is not null OR mtp.modeled_supplier_site_id is not null))
ORDER BY 2;
Select
ms.Transaction_id,
ms.order_type,
ms.sr_instance_id ,
ms.plan_id
from
msc_supplies ms,
msc_plans mps,
msc_system_items msi,
msc_trading_partners mtp,
msc_calendar_dates cal1,
msc_calendar_dates cal2
where
ms.plan_id =arg_plan_id
and ms.order_type in (PLANNED_IRO,PLANNED_ERO)
and ms.inventory_item_id = msi.inventory_item_id
and ms.sr_instance_id =msi.sr_instance_id
and msi.plan_id = arg_plan_id
and msi.bom_item_type = 4
and msi.release_time_fence_code NOT IN (5,6,7)
and ms.organization_id =msi.organization_id
and mtp.sr_tp_id = ms.organization_id
and mtp.sr_instance_id= ms.sr_instance_id
and mtp.partner_type= 3
and cal1.sr_instance_id = ms.sr_instance_id
and cal1.calendar_code = mtp.calendar_code
and cal1.exception_set_id = mtp.calendar_exception_set_id
and cal1.calendar_date = TRUNC(var_start_date)
and cal2.sr_instance_id = ms.sr_instance_id
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)
and nvl(ms.new_order_placement_date, ms.new_schedule_date) BETWEEN TRUNC(var_start_date)
and TRUNC(cal2.calendar_date)
and ms.plan_id = mps.plan_id
AND ( ( NVL(mps.auto_release_method,-1) IN (3,4) AND ms.releasable = 99) -- Query based autorel
OR
( NVL(mps.auto_release_method,-1) NOT IN (3,4) AND NVL(ms.releasable,0) =0 )-- other autorel
)
--and (ms.releasable =0 or ms.releasable is null)
and ms.batch_id is null;
Select
ms.Transaction_id,
ms.order_type,
ms.sr_instance_id ,
ms.plan_id
from
msc_supplies ms,
msc_plans mps,
msc_system_items msi,
msc_trading_partners mtp,
msc_calendar_dates cal1,
msc_calendar_dates cal2
where
ms.plan_id =arg_plan_id
and ms.order_type in (PLANNED_TRANSFER)
and ms.inventory_item_id = msi.inventory_item_id
and ms.sr_instance_id =msi.sr_instance_id
and msi.plan_id = arg_plan_id
and msi.bom_item_type = 4
and msi.release_time_fence_code NOT IN (5,6,7)
and ms.organization_id =msi.organization_id
and mtp.sr_tp_id = ms.organization_id
and mtp.sr_instance_id= ms.sr_instance_id
and mtp.partner_type= 3
and cal1.sr_instance_id = ms.sr_instance_id
and cal1.calendar_code = mtp.calendar_code
and cal1.exception_set_id = mtp.calendar_exception_set_id
and cal1.calendar_date = TRUNC(var_start_date)
and cal2.sr_instance_id = ms.sr_instance_id
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)
and nvl(ms.new_order_placement_date, ms.new_schedule_date) BETWEEN TRUNC(var_start_date)
and TRUNC(cal2.calendar_date)
and ms.plan_id = mps.plan_id
AND ( (NVL(mps.auto_release_method,-1) IN (3,4) AND ms.releasable = 99) -- Query based autorel
OR
(NVL(mps.auto_release_method,-1) NOT IN (3,4) AND NVL(ms.releasable,0) =0 )-- other autorel
)
--and (ms.releasable =0 or ms.releasable is null)
and ms.batch_id is null
and
not exists (select 1 from msc_full_pegging mfp,
msc_demands md,
msc_supplies ms1
where mfp.sr_instance_id = ms.sr_instance_id and
mfp.plan_id = ms.plan_id and
mfp.transaction_id =ms.transaction_id and
mfp.demand_id =md.demand_id and
mfp.sr_instance_id = md.sr_instance_id and
mfp.plan_id = md.plan_id and
md.origination_type =78 and
md.disposition_id = ms1.transaction_id and
md.sr_instance_id =ms1.sr_instance_id and
md.plan_id = ms1.plan_id and
ms1.order_type =79 and
rownum <2);
Select
ms.Transaction_id,
ms.order_type,
--decode(ms.order_type,)
ms.sr_instance_id ,
ms.plan_id
from
msc_supplies ms,
msc_plans mps,
msc_system_items msi,
msc_trading_partners mtp,
msc_calendar_dates cal1,
msc_calendar_dates cal2
where
ms.plan_id =arg_plan_id
and ms.order_type in (PLANNED_REP_WO,RESCHEDULE_EAM_CMRO) --79,70
and ms.inventory_item_id = msi.inventory_item_id
and ms.sr_instance_id =msi.sr_instance_id
and msi.plan_id = arg_plan_id
and msi.bom_item_type = 4
and msi.release_time_fence_code NOT IN (5,6,7)
and ms.organization_id =msi.organization_id
and mtp.sr_tp_id = ms.organization_id
and mtp.sr_instance_id= ms.sr_instance_id
and mtp.partner_type= 3
and cal1.sr_instance_id = ms.sr_instance_id
and cal1.calendar_code = mtp.calendar_code
and cal1.exception_set_id = mtp.calendar_exception_set_id
and cal1.calendar_date = TRUNC(var_start_date)
and cal2.sr_instance_id = ms.sr_instance_id
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)
and nvl(ms.new_order_placement_date, ms.new_schedule_date) BETWEEN TRUNC(var_start_date)
and TRUNC(cal2.calendar_date)
and ms.plan_id = mps.plan_id
AND ( ( NVL(mps.auto_release_method,-1) IN (3,4) AND ms.releasable = 99) -- Query based autorel
OR
( NVL(mps.auto_release_method,-1) NOT IN (3,4) AND NVL(ms.releasable,0) =0 )-- other autorel
)
--and (ms.releasable =0 or ms.releasable is null)
and ms.batch_id is null;
select organization_id,
sr_instance_id
into lv_arg_org_id_new,
lv_arg_instance_id_new
from msc_plans
where plan_id = arg_plan_id ;
select decode(FND_PROFILE.VALUE('MSC_DRP_RELEASE_FROM_MRP'),'Y',SYS_YES,SYS_NO)
into lv_plan_release_profile
from dual;
SELECT ALLOW_RELEASE_FLAG,
INSTANCE_CODE,
APPS_VER,
DECODE( M2A_DBLINK,
NULL, NULL_DBLINK,
'@'||M2A_DBLINK)
INTO var_allow_release,
var_instance_code,
var_apps_ver,
var_dblink
FROM MSC_APPS_INSTANCES
WHERE INSTANCE_ID= lv_arg_instance_id_new;
'SELECT mar.A2M_DBLINK '
||' FROM MRP_AP_APPS_INSTANCES_ALL'||var_dblink||' mar'
||' WHERE mar.ALLOW_RELEASE_FLAG = 1 ';
SELECT plan_type
INTO var_plan_type
FROM msc_plans
WHERE plan_id = arg_plan_id;
lv_sql_stmt_rp := ' SELECT nvl(profile_value,0)'
||' FROM msc_plan_profiles'
||' WHERE plan_id='|| arg_plan_id
||' AND profile_code=''MSC_AUTO_REL_COMP_TOLERANCE''';
SELECT DECODE( M2A_DBLINK, NULL, ' ', '@'||M2A_DBLINK),
APPS_VER
INTO var_dblink,
var_apps_ver
FROM MSC_APPS_INSTANCES
WHERE INSTANCE_ID= lv_arg_instance_id_new;
SELECT FND_GLOBAL.USER_NAME,
FND_GLOBAL.RESP_NAME,
FND_GLOBAL.APPLICATION_NAME
INTO var_user_name,
var_resp_name,
var_application_name
FROM dual;
'SELECT FND_GLOBAL.USER_ID,'
||' FND_PROFILE.VALUE'||var_dblink||'(''WIP_JOB_PREFIX''),'
||' FND_PROFILE.VALUE'||var_dblink||'(''MRP_LOAD_REQ_GROUP_BY'')'
||' FROM DUAL';
SELECT mp.curr_plan_type,
DECODE(UPPER(arg_use_start_date),
'Y', mp.plan_start_date, 'N', sysdate, sysdate),
sched.demand_class,
mp.compile_designator,
NVL(auto_release_method,0)
INTO var_plan_type,
var_start_date,
var_demand_class,
lv_plan_name,
lv_auto_release_method
FROM msc_plans mp,
msc_designators sched
WHERE sched.organization_id(+)= mp.organization_id
AND sched.designator(+) = mp.compile_designator
AND sched.sr_instance_id(+) = mp.sr_instance_id
AND mp.plan_id = arg_plan_id;
Execute immediate 'select mrp_workbench_query_s.nextval
FROM DUAL '
into MSC_Rel_Plan_PUB.g_batch_id;
UPDATE msc_supplies
SET batch_id = MSC_Rel_Plan_PUB.g_batch_id
WHERE transaction_id = var_transaction_id
AND sr_instance_id = var_sr_instance_id
AND plan_id = var_plan_id ;
SELECT DECODE(tps.sr_tp_site_id, -1, NULL,tps.sr_tp_site_id)
INTO var_location_id
FROM msc_trading_partner_sites tps
WHERE tps.sr_tp_id = var_org_id
AND tps.sr_instance_id= lv_arg_instance_id_new
AND tps.partner_type= 3;
'SELECT wp.default_discrete_class'
||' FROM wip_parameters'||var_dblink||' wp'
||' WHERE wp.organization_id = :var_org_id';
'SELECT wip_job_number_s.nextval'||var_dblink||' FROM dual';
select
mb.alternate_bom_designator
into var_alternate_bom
from msc_supplies ms,
msc_process_effectivity mpe,
msc_boms mb
where
ms.plan_id = var_plan_id -- Plan_id
and ms.order_type = 5 -- Order type for planned order
and ms.plan_id = mpe.plan_id
and ms.process_seq_id = mpe.process_sequence_id
and mpe.plan_id = mb.plan_id
and mpe.bill_sequence_id = mb.bill_sequence_id(+)
and mpe.sr_instance_id = mb.sr_instance_id
and ms.transaction_id= var_transaction_id;
select
mr.alternate_routing_designator
into var_alternate_routing
from msc_supplies ms,
msc_process_effectivity mpe,
msc_routings mr
where
ms.plan_id = var_plan_id -- Plan_id
and ms.order_type = 5 -- Order type for planned order
and ms.plan_id = mpe.plan_id
and ms.process_seq_id = mpe.process_sequence_id
and mpe.plan_id = mr.plan_id
and mpe.routing_sequence_id = mr.routing_sequence_id(+)
and mpe.sr_instance_id = mr.sr_instance_id
and ms.transaction_id= var_transaction_id;
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, -- Added for Ferring Enhancement - bug 14751383 / 14358365
implement_quantity = new_order_quantity,
implement_firm = DECODE(var_firm_jobs, 'Y', 1, 2),
/*for discrete job reschedules existing name is populated */
implement_job_name = decode(var_order_type,PLANNED_ORDER,var_job_prefix||to_char(var_wip_job_number)
,PLANNED_NEW_BUY_ORDER,var_job_prefix||to_char(var_wip_job_number),3,order_number),
/*implement status code must be set to cancel (7) in case of reschedules */
implement_status_code = DECODE(disposition_status_type, CANCEL,7, var_impl_status_code),
load_type = var_load_type,
/*this is 1 for reschedules */
reschedule_flag=decode(order_type,PLANNED_ORDER,reschedule_flag,PLANNED_NEW_BUY_ORDER,reschedule_flag,1),
/*for reschedules it is 1 */
implement_as = decode(order_type,PLANNED_ORDER,WIP_DISCRETE_JOB,PLANNED_NEW_BUY_ORDER,WIP_DISCRETE_JOB,1),
implement_wip_class_code = var_wip_class_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_unit_number = unit_number,
implement_demand_class = var_demand_class,
implement_alternate_bom = var_alternate_bom,
implement_alternate_routing= var_alternate_routing,
release_status = var_release_status,
last_updated_by = var_user_id
WHERE transaction_id = var_transaction_id
AND sr_instance_id = var_sr_instance_id
AND plan_id = var_plan_id;
-- the same plan. If, so, we need to update the load_type to 64
IF (var_order_type = PO_REQUISITION) THEN
BEGIN
var_sales_order_line_id := NULL;
SELECT sales_order_line_id
INTO var_sales_order_line_id
FROM msc_demands
WHERE
plan_id = var_plan_id
AND sr_instance_id = var_sr_instance_id
AND disposition_id = var_transaction_id
and origination_type = 30
and rownum = 1;
SELECT count(*)
INTO var_count
FROM msc_planners
WHERE employee_id = var_planner_employee_id
AND current_employee_flag= 1;
SELECT param.organization_code,
msi.item_name,
msi.planner_code
INTO var_org_code, var_item, var_planner_code
FROM msc_system_items msi,
msc_trading_partners param,
msc_supplies mr
WHERE mr.transaction_id = var_transaction_id
AND mr.sr_instance_id = var_sr_instance_id
AND mr.plan_id = var_plan_id
AND msi.organization_id = mr.organization_id
AND msi.inventory_item_id = mr.inventory_item_id
AND msi.sr_instance_id = mr.sr_instance_id
AND msi.plan_id = -1
AND param.sr_tp_id = mr.organization_id
AND param.sr_instance_id = mr.sr_instance_id
AND param.partner_type= 3;
UPDATE msc_supplies
SET old_order_quantity = new_order_quantity,
quantity_in_process = new_order_quantity,
implement_date = new_schedule_date,
/*implement quantity is 0 in case of cancels */
implement_quantity = decode(disposition_status_type, 2,0,new_order_quantity),
load_type = var_load_type,
/*this is 1 for reschedules */
reschedule_flag=decode(order_type,PLANNED_ORDER,reschedule_flag,PLANNED_NEW_BUY_ORDER,reschedule_flag,1),
/*for reschedules it is 1 */
implement_as = decode(order_type,PLANNED_ORDER,PO_REQUISITION,PLANNED_NEW_BUY_ORDER,PO_REQUISITION,1),
/*implement status code must be set to cancel (7) in case of reschedules */
implement_status_code = DECODE(disposition_status_type, CANCEL,7, null),
implement_firm = firm_planned_type,
implement_dock_date = new_dock_date,
implement_ship_date = new_ship_date, --9849059; checked in with RP auto release fix
last_updated_by = var_user_id
WHERE transaction_id = var_transaction_id
AND sr_instance_id = var_sr_instance_id
AND plan_id = var_plan_id;
MSC_SELECT_ALL_FOR_RELEASE_PUB.Update_Implement_Attrib(p_where_clause ,
NULL,
NULL,
NULL,
NULL,
NULL,
p_total_rows ,
p_succ_rows ,
p_error_rows
);
UPDATE msc_supplies
SET batch_id = MSC_Rel_Plan_PUB.g_batch_id,
load_type =decode(var_order_type,PLANNED_ERO,ERO_LOAD,IRO_LOAD)
WHERE transaction_id = var_transaction_id
AND sr_instance_id = var_sr_instance_id
AND plan_id = var_plan_id ;
MSC_SELECT_ALL_FOR_RELEASE_PUB.Update_Implement_Attrib(p_where_clause ,
NULL,
NULL,
NULL,
NULL,
NULL,
p_total_rows ,
p_succ_rows ,
p_error_rows
);
UPDATE msc_supplies
SET batch_id = MSC_Rel_Plan_PUB.g_batch_id,
load_type =TRANSFER_LOAD
WHERE transaction_id = var_transaction_id
AND sr_instance_id = var_sr_instance_id
AND plan_id = var_plan_id ;
UPDATE msc_supplies
SET batch_id = MSC_Rel_Plan_PUB.g_batch_id,
/* update rest of the implement columns here */
old_order_quantity = new_order_quantity,
quantity_in_process = new_order_quantity,
implement_date = new_schedule_date,
implement_quantity = new_order_quantity,
implement_firm = 1,
load_type = decode(var_order_type,PLANNED_REP_WO,CMRO_EAM_MASS_LOAD,RESCHEDULE_EAM_CMRO,CMRO_EAM_RESCHED),
reschedule_flag = DECODE(var_order_type,PLANNED_REP_WO,reschedule_flag,1),
implement_as = decode(var_order_type,PLANNED_REP_WO,CMRO_EAM_MASS_LOAD,RESCHEDULE_EAM_CMRO,CMRO_EAM_RESCHED),
implement_project_id = project_id,
implement_task_id = task_id,
implement_unit_number = unit_number,
release_status = var_release_status,
last_updated_by = var_user_id
WHERE transaction_id = var_transaction_id
AND sr_instance_id = var_sr_instance_id
AND plan_id = var_plan_id ;
/*????update other columns in msc_supplies if UI is not doing it....
wht abt the wip class code , job name etc.. who will populate this*/
END LOOP;
update msc_supplies
set releasable= null where releasable = 99 ;
UPDATE msc_plans
SET release_reschedules=2
WHERE plan_id=arg_plan_id
AND var_plan_type < RP_MRP_PLAN; -- For RP Plan keep the flag unchanged
select instance_code
into lv_instance_code
from msc_apps_instances
where instance_id= var_released_instance_id(i);
select instance_code
into lv_instance_code
from msc_apps_instances
where instance_id= var_released_instance(i);
SELECT mp.sr_instance_id,
mp.organization_id
INTO v_instance_id,
v_owning_org_id
FROM msc_plans mp
WHERE mp.plan_id = pPlan_id;
select instance_code
into lv_instance_code
from msc_apps_instances
where instance_id= var_released_instance_id(i);
select instance_code
into lv_instance_code
from msc_apps_instances
where instance_id= var_released_instance(i);
Cursor queries is SELECT Distinct Mpers.query_type||' - '||Mpq.Applied_to
FROM
MSC_PLAN_QUERIES mpq,
MSC_PQ_RESULTS mpr,
MSC_PERSONAL_QUERIES mpers
Where
mpers.query_type in (1,4,5,9)
AND mpq.plan_id = pPlan_id
AND mpr.plan_id=mpq.plan_id
AND mpq.query_id = mpers.query_id
AND mpr.query_id = mpq.query_id ;
Select
NVL(Mp.auto_release_method,-1) ,
NVL(Mp.include_reschedules,-1)
INTO
lv_auto_release_method,
lv_inc_rescheds
From MSC_PLANS mp
Where mp.plan_id = pPlan_id ;
Update MSC_SUPPLIES set releasable=9999 where transaction_id in
( select ms.transaction_id
from MSC_SUPPLIES ms,
MSC_PQ_RESULTS mpr,
MSC_PLAN_QUERIES mpq,
MSC_PERSONAL_QUERIES mpers
where mpr.plan_id = pPlan_id
and ms.plan_id = mpr.plan_id
and ms.sr_instance_id = mpr.sr_instance_id
and ms.inventory_item_id = mpr.inventory_item_id
and mpr.query_id =Mpq.Query_id
and mpers.query_type = 1
AND mpq.plan_id = pPlan_id
AND mpr.plan_id=mpq.plan_id
AND mpq.query_id = mpers.query_id
AND mpr.query_id = mpq.query_id
and Mpq.Applied_to = 1
) ;
Update MSC_SUPPLIES set releasable=9999 where transaction_id in
(Select ms.transaction_id
from MSC_SUPPLIES ms,
MSC_PQ_RESULTS mpr,
MSC_PLAN_QUERIES mpq,
MSC_PERSONAL_QUERIES mpers
where mpr.plan_id = pPlan_id
and ms.plan_id = mpr.plan_id
and ms.sr_instance_id = mpr.sr_instance_id
and ms.inventory_item_id = mpr.inventory_item_id
and ms.organization_id = mpr.organization_id
and mpr.query_id =Mpq.Query_id
and mpers.query_type = 1
AND mpq.plan_id = pPlan_id
AND mpr.plan_id=mpq.plan_id
AND mpq.query_id = mpers.query_id
AND mpr.query_id = mpq.query_id
and Mpq.Applied_to = 2
) ;
Update MSC_SUPPLIES set releasable=9999 where transaction_id in
(Select ms.transaction_id
from MSC_SUPPLIES ms,
MSC_PQ_RESULTS mpr,
MSC_ITEM_SUBSTITUTES mis
,MSC_PLAN_QUERIES mpq,
MSC_PERSONAL_QUERIES mpers
where mpr.plan_id = pPlan_id
and (mis.lower_item_id = mpr.inventory_item_id OR mis.higher_item_id = mpr.inventory_item_id)
and mis.sr_instance_id = mpr.sr_instance_id
and mis.plan_id = mpr.plan_id
and mis.relationship_type = 8 ----------Rel Type SuperSession
and ms.inventory_item_id in( mis.higher_item_id ,mis.Lower_item_id )
and ms.sr_instance_id = mis.sr_instance_id
and ms.plan_id = mis.plan_id
and mpr.query_id = Mpq.Query_id
and mpers.query_type = 1
AND mpq.plan_id = pPlan_id
AND mpr.plan_id=mpq.plan_id
AND mpq.query_id = mpers.query_id
AND mpr.query_id = mpq.query_id
and Mpq.Applied_to = 3
) ;
Update MSC_SUPPLIES set releasable=9999 where transaction_id in
(Select ms.transaction_id
from MSC_SUPPLIES ms,
MSC_PQ_RESULTS mpr,
MSC_ITEM_SUBSTITUTES mis
,MSC_PLAN_QUERIES mpq,
MSC_PERSONAL_QUERIES mpers
where mpr.plan_id = pPlan_id
and (mis.lower_item_id = mpr.inventory_item_id OR mis.higher_item_id = mpr.inventory_item_id)
and mis.sr_instance_id = mpr.sr_instance_id
and mis.plan_id = mpr.plan_id
and mis.relationship_type = 8 ---------------------------- Rel Type SuperSession
and ms.inventory_item_id in ( mis.higher_item_id ,mis.Lower_item_id )
and ms.organization_id = mpr.organization_id
and ms.sr_instance_id = mis.sr_instance_id
and ms.plan_id = mis.plan_id
and mpr.query_id =Mpq.Query_id
and mpers.query_type = 1
AND mpq.plan_id = pPlan_id
AND mpr.plan_id=mpq.plan_id
AND mpq.query_id = mpers.query_id
AND mpr.query_id = mpq.query_id
and Mpq.Applied_to = 4
) ;
Update MSC_SUPPLIES set releasable=9999 where transaction_id in
(Select ms.transaction_id
from MSC_SUPPLIES ms,
MSC_PQ_RESULTS mpr,
MSC_EXCEPTION_DETAILS med
,MSC_PLAN_QUERIES mpq,
MSC_PERSONAL_QUERIES mpers
where mpr.plan_id = pPlan_id
and mpr.query_id =Mpq.Query_id
and mpr.summary_data =2
and med.plan_id = mpr.plan_id
and med.exception_detail_id = mpr.exception_id
and med.sr_instance_id = mpr.sr_instance_id
and med.inventory_item_id <> -1
and med.TRANSACTION_ID IS NULL
and med.DEMAND_ID IS NULL
and ms.plan_id = med.plan_id
and ms.inventory_item_id = med.inventory_item_id
and ms.sr_instance_id = med.sr_instance_id
and mpers.query_type = 4
AND mpq.plan_id = pPlan_id
AND mpr.plan_id=mpq.plan_id
AND mpq.query_id = mpers.query_id
AND mpr.query_id = mpq.query_id
and Mpq.Applied_to = 1
) ;
Update MSC_SUPPLIES set releasable=9999 where transaction_id in
( Select ms.transaction_id
from MSC_SUPPLIES ms,
MSC_PQ_RESULTS mpr,
MSC_EXCEPTION_DETAILS med
,MSC_PLAN_QUERIES mpq,
MSC_PERSONAL_QUERIES mpers
where mpr.plan_id = pPlan_id
and mpr.query_id =Mpq.Query_id
and mpr.summary_data =2
and med.plan_id = mpr.plan_id
and med.exception_detail_id = mpr.exception_id
and med.sr_instance_id = mpr.sr_instance_id
and med.inventory_item_id <> 1
and med.TRANSACTION_ID IS NULL
and med.DEMAND_ID IS NULL
and ms.plan_id = med.plan_id
and ms.inventory_item_id = med.inventory_item_id
and ms.organization_id = med.organization_id
and ms.sr_instance_id = med.sr_instance_id
and mpers.query_type = 4
AND mpq.plan_id = pPlan_id
AND mpr.plan_id=mpq.plan_id
AND mpq.query_id = mpers.query_id
AND mpr.query_id = mpq.query_id
and Mpq.Applied_to = 2
) ;
Update MSC_SUPPLIES set releasable=9999 where transaction_id in
( Select ms.transaction_id
from MSC_SUPPLIES ms,
MSC_PQ_RESULTS mpr,
MSC_EXCEPTION_DETAILS med,
MSC_ITEM_SUBSTITUTES mis
,MSC_PLAN_QUERIES mpq,
MSC_PERSONAL_QUERIES mpers
where mpr.plan_id = pPlan_id
and mpr.query_id =Mpq.Query_id
and mpr.summary_data =2
and med.plan_id = mpr.plan_id
and med.exception_detail_id = mpr.exception_id
and med.sr_instance_id = mpr.sr_instance_id
and med.inventory_item_id <> 1
and med.TRANSACTION_ID IS NULL
and med.DEMAND_ID IS NULL
and ( mis.lower_item_id = med.inventory_item_id or mis.higher_item_id = med.inventory_item_id )
and mis.sr_instance_id = med.sr_instance_id
and mis.plan_id = med.plan_id
and mis.relationship_type = 8
and ms.plan_id = mis.plan_id
and ms.inventory_item_id in ( mis.higher_item_id , mis.lower_item_id)
and ms.sr_instance_id = mis.sr_instance_id
and mpers.query_type = 4
AND mpq.plan_id = pPlan_id
AND mpr.plan_id=mpq.plan_id
AND mpq.query_id = mpers.query_id
AND mpr.query_id = mpq.query_id
and Mpq.Applied_to = 3
);
Update MSC_SUPPLIES set releasable=9999 where transaction_id in
(Select ms.transaction_id
from MSC_SUPPLIES ms,
MSC_PQ_RESULTS mpr,
MSC_EXCEPTION_DETAILS med,
MSC_ITEM_SUBSTITUTES mis
,MSC_PLAN_QUERIES mpq,
MSC_PERSONAL_QUERIES mpers
where mpr.plan_id = pPlan_id
and mpr.query_id =Mpq.Query_id
and mpr.summary_data =2
and med.plan_id = mpr.plan_id
and med.exception_detail_id = mpr.exception_id
and med.sr_instance_id = mpr.sr_instance_id
and med.inventory_item_id <> 1
and med.TRANSACTION_ID IS NULL
and med.DEMAND_ID IS NULL
and ( mis.lower_item_id = med.inventory_item_id or mis.higher_item_id = med.inventory_item_id )
and mis.sr_instance_id = med.sr_instance_id
and mis.plan_id = med.plan_id
and mis.relationship_type = 8
and ms.plan_id = mis.plan_id
and ms.inventory_item_id in ( mis.higher_item_id , mis.lower_item_id)
and ms.organization_id = med.organization_id
and ms.sr_instance_id = mis.sr_instance_id
and mpers.query_type = 4
AND mpq.plan_id = pPlan_id
AND mpr.plan_id=mpq.plan_id
AND mpq.query_id = mpers.query_id
AND mpr.query_id = mpq.query_id
and Mpq.Applied_to = 4
) ;
Update MSC_SUPPLIES set releasable=9999 where transaction_id in
(Select ms.transaction_id
from MSC_SUPPLIES ms,
MSC_PQ_RESULTS mpr,
MSC_SUPPLIES ms2
,MSC_PLAN_QUERIES mpq,
MSC_PERSONAL_QUERIES mpers
where mpr.plan_id = pPlan_id
and mpr.SOURCE_TABLE = 'MSC_SUPPLIES'
and ms2.ROWID = mpr.row_id
and mpr.plan_id= ms2.plan_id
and ms.plan_id= ms2.plan_id
and ms.sr_instance_id = ms2.sr_instance_id
and ms.inventory_item_id = ms2.inventory_item_id
and mpers.query_type = 9
AND mpq.plan_id = pPlan_id
AND mpr.plan_id=mpq.plan_id
AND mpq.query_id = mpers.query_id
AND mpr.query_id = mpq.query_id
and Mpq.Applied_to = 1
union all
Select ms.transaction_id
from MSC_SUPPLIES ms,
MSC_PQ_RESULTS mpr,
MSC_DEMANDS md
,MSC_PLAN_QUERIES mpq,
MSC_PERSONAL_QUERIES mpers
where mpr.plan_id = pPlan_id
and mpr.SOURCE_TABLE = 'MSC_DEMANDS'
and md.ROWID = mpr.row_id
and mpr.plan_id= md.plan_id
and ms.plan_id= md.plan_id
and ms.sr_instance_id = md.sr_instance_id
and ms.inventory_item_id = md.inventory_item_id
and mpr.query_id =Mpq.Query_id
and mpers.query_type = 9
AND mpq.plan_id = pPlan_id
AND mpr.plan_id=mpq.plan_id
AND mpq.query_id = mpers.query_id
AND mpr.query_id = mpq.query_id
and Mpq.Applied_to = 1
) ;
Update MSC_SUPPLIES set releasable=9999 where transaction_id in
(Select ms.transaction_id
from MSC_SUPPLIES ms,
MSC_PQ_RESULTS mpr,
MSC_SUPPLIES ms2
,MSC_PLAN_QUERIES mpq,
MSC_PERSONAL_QUERIES mpers
where mpr.plan_id = pPlan_id
and mpr.SOURCE_TABLE = 'MSC_SUPPLIES'
and ms2.ROWID = mpr.row_id
and mpr.plan_id= ms2.plan_id
and mpr.sr_instance_id= ms2.sr_instance_id
and ms.plan_id= ms2.plan_id
and ms.sr_instance_id = ms2.sr_instance_id
and ms.inventory_item_id = ms2.inventory_item_id
and ms.organization_id = ms2.organization_id
and mpr.query_id =Mpq.Query_id
and mpers.query_type = 9
AND mpq.plan_id = pPlan_id
AND mpr.plan_id=mpq.plan_id
AND mpq.query_id = mpers.query_id
AND mpr.query_id = mpq.query_id
and Mpq.Applied_to = 2
union all
Select ms.transaction_id
from MSC_SUPPLIES ms,
MSC_PQ_RESULTS mpr,
MSC_DEMANDS md
,MSC_PLAN_QUERIES mpq,
MSC_PERSONAL_QUERIES mpers
where mpr.plan_id = pPlan_id
and mpr.SOURCE_TABLE = 'MSC_DEMANDS'
and md.ROWID = mpr.row_id
and mpr.plan_id= md.plan_id
and mpr.sr_instance_id= md.sr_instance_id
and ms.plan_id= md.plan_id
and ms.sr_instance_id = md.sr_instance_id
and ms.inventory_item_id = md.inventory_item_id
and ms.organization_id = md.organization_id
and mpr.query_id =Mpq.Query_id
and mpers.query_type = 9
AND mpq.plan_id = pPlan_id
AND mpr.plan_id=mpq.plan_id
AND mpq.query_id = mpers.query_id
AND mpr.query_id = mpq.query_id
and Mpq.Applied_to = 2
) ;
Update MSC_SUPPLIES set releasable=9999 where transaction_id in
(Select ms.transaction_id
from MSC_SUPPLIES ms,
MSC_PQ_RESULTS mpr,
MSC_SUPPLIES ms2,
MSC_ITEM_SUBSTITUTES mis
,MSC_PLAN_QUERIES mpq,
MSC_PERSONAL_QUERIES mpers
where mpr.plan_id = pPlan_id
and mpr.SOURCE_TABLE = 'MSC_SUPPLIES'
and ms2.ROWID = mpr.row_id
and mpr.plan_id= ms2.plan_id
and mpr.sr_instance_id= ms2.sr_instance_id
and (ms2.inventory_item_id = mis.lower_item_id OR ms2.inventory_item_id = mis.higher_item_id )
--and ms2.organization_id = mis.organization_id
and ms2.sr_instance_id = mis.sr_instance_id
and ms2.plan_id = mis.plan_id
and mis.relationship_type = 8 ---------------------------- Rel Type SuperSession
and ms.plan_id= mis.plan_id
and ms.sr_instance_id = mis.sr_instance_id
and ms.inventory_item_id in ( mis.higher_item_id , mis.Lower_item_id)
and mpr.query_id =Mpq.Query_id
and mpers.query_type = 9
AND mpq.plan_id = pPlan_id
AND mpr.plan_id=mpq.plan_id
AND mpq.query_id = mpers.query_id
AND mpr.query_id = mpq.query_id
and Mpq.Applied_to = 3
union all
Select ms.transaction_id
from MSC_SUPPLIES ms,
MSC_PQ_RESULTS mpr,
MSC_DEMANDS md,
MSC_ITEM_SUBSTITUTES mis
,MSC_PLAN_QUERIES mpq,
MSC_PERSONAL_QUERIES mpers
where mpr.plan_id = pPlan_id
and mpr.SOURCE_TABLE = 'MSC_DEMANDS'
and md.ROWID = mpr.row_id
and mpr.plan_id= md.plan_id
and mpr.sr_instance_id= md.sr_instance_id
and ( md.inventory_item_id = mis.lower_item_id OR md.inventory_item_id = mis.higher_item_id )
--and md.organization_id = mis.organization_id
and md.sr_instance_id = mis.sr_instance_id
and md.plan_id = mis.plan_id
and mis.relationship_type = 8 ---------------------------- Rel Type SuperSession
and ms.plan_id= mis.plan_id
and ms.sr_instance_id = mis.sr_instance_id
and ms.inventory_item_id in (mis.higher_item_id ,mis.lower_item_id )
and mpr.query_id =Mpq.Query_id
and mpers.query_type = 9
AND mpq.plan_id = pPlan_id
AND mpr.plan_id=mpq.plan_id
AND mpq.query_id = mpers.query_id
AND mpr.query_id = mpq.query_id
and Mpq.Applied_to = 3
) ;
Update MSC_SUPPLIES set releasable=9999 where transaction_id in
(Select ms.transaction_id
from MSC_SUPPLIES ms,
MSC_PQ_RESULTS mpr,
MSC_SUPPLIES ms2,
MSC_ITEM_SUBSTITUTES mis
,MSC_PLAN_QUERIES mpq,
MSC_PERSONAL_QUERIES mpers
where mpr.plan_id = pPlan_id
and mpr.SOURCE_TABLE = 'MSC_SUPPLIES'
and ms2.ROWID = mpr.row_id
and mpr.plan_id= ms2.plan_id
and mpr.sr_instance_id= ms2.sr_instance_id
and (ms2.inventory_item_id = mis.lower_item_id OR ms2.inventory_item_id = mis.higher_item_id )
--and ms2.organization_id = mis.organization_id
and ms2.sr_instance_id = mis.sr_instance_id
and ms2.plan_id = mis.plan_id
and mis.relationship_type = 8 ---------------------------- Rel Type SuperSession
and ms.plan_id= ms2.plan_id
and ms.sr_instance_id = ms2.sr_instance_id
and ms.inventory_item_id in (mis.higher_item_id ,mis.lower_item_id )
and ms.organization_id = ms2.organization_id
and mpr.query_id =Mpq.Query_id
and mpers.query_type = 9
AND mpq.plan_id = pPlan_id
AND mpr.plan_id=mpq.plan_id
AND mpq.query_id = mpers.query_id
AND mpr.query_id = mpq.query_id
and Mpq.Applied_to = 4
union all
Select ms.transaction_id
from MSC_SUPPLIES ms,
MSC_PQ_RESULTS mpr,
MSC_DEMANDS md,
MSC_ITEM_SUBSTITUTES mis
,MSC_PLAN_QUERIES mpq,
MSC_PERSONAL_QUERIES mpers
where mpr.plan_id = pPlan_id
and mpr.SOURCE_TABLE = 'MSC_DEMANDS'
and md.ROWID = mpr.row_id
and mpr.plan_id= md.plan_id
and mpr.sr_instance_id= md.sr_instance_id
and ( md.inventory_item_id = mis.lower_item_id OR md.inventory_item_id = mis.higher_item_id )
--and md.organization_id = mis.organization_id
and md.sr_instance_id = mis.sr_instance_id
and md.plan_id = mis.plan_id
and mis.relationship_type = 8 ---------------------------- Rel Type SuperSession
and ms.plan_id= md.plan_id
and ms.sr_instance_id = md.sr_instance_id
and ms.inventory_item_id in (mis.higher_item_id ,mis.lower_item_id )
and ms.organization_id = md.organization_id
and mpr.query_id =Mpq.Query_id
and mpers.query_type = 9
AND mpq.plan_id = pPlan_id
AND mpr.plan_id=mpq.plan_id
AND mpq.query_id = mpers.query_id
AND mpr.query_id = mpq.query_id
and Mpq.Applied_to = 4
) ;
Update MSC_SUPPLIES set releasable=9999 where transaction_id in
(Select ms.transaction_id
from MSC_SUPPLIES ms,
MSC_PQ_RESULTS mpr
,MSC_PLAN_QUERIES mpq,
MSC_PERSONAL_QUERIES mpers
where mpr.plan_id = pPlan_id
and mpr.SOURCE_TABLE = 'MSC_SUPPLIES'
and ms.ROWID = mpr.row_id
and mpr.plan_id= ms.plan_id
and mpr.sr_instance_id= ms.sr_instance_id
and mpr.query_id =Mpq.Query_id
and mpers.query_type = 9
AND mpq.plan_id = pPlan_id
AND mpr.plan_id=mpq.plan_id
AND mpq.query_id = mpers.query_id
AND mpr.query_id = mpq.query_id
and Mpq.Applied_to = 5
) ;
Update MSC_SUPPLIES set releasable=9999 where transaction_id in
(select ms.transaction_id
from MSC_SUPPLIES ms,
MSC_PQ_RESULTS mpr,
--MSC_SUPPLIES ms2
MSC_ITEM_SUPPLIERS ms2
,MSC_PLAN_QUERIES mpq,
MSC_PERSONAL_QUERIES mpers
where mpr.plan_id = pPlan_id
and ms2.plan_id=mpr.plan_id
and ms2.sr_instance_id = mpr.sr_instance_id
and ms2.inventory_item_id = mpr.inventory_item_id
and ms2.supplier_id = mpr.supplier_id
and NVL(ms2.supplier_site_id, -99999) = NVL( mpr.supplier_site_id,-99999)
and ms.plan_id = ms2.plan_id
and ms.sr_instance_id = ms2.sr_instance_id
and ms.inventory_item_id = ms2.inventory_item_id
and mpr.query_id =Mpq.Query_id
and mpers.query_type = 5
AND mpq.plan_id = pPlan_id
AND mpr.plan_id=mpq.plan_id
AND mpq.query_id = mpers.query_id
AND mpr.query_id = mpq.query_id
and Mpq.Applied_to = 1
) ;
Update MSC_SUPPLIES set releasable=9999 where transaction_id in
(select ms.transaction_id
from MSC_SUPPLIES ms,
MSC_PQ_RESULTS mpr,
--MSC_SUPPLIES ms2
MSC_ITEM_SUPPLIERS ms2
,MSC_PLAN_QUERIES mpq,
MSC_PERSONAL_QUERIES mpers
where mpr.plan_id = pPlan_id
and ms2.plan_id=mpr.plan_id
and ms2.sr_instance_id = mpr.sr_instance_id
and ms2.inventory_item_id = mpr.inventory_item_id
and ms2.organization_id = NVL(mpr.organization_id , ms2.organization_id)
and ms2.supplier_id = mpr.supplier_id
and NVL(ms2.supplier_site_id, -99999) = NVL( mpr.supplier_site_id,-99999)
and ms.plan_id = ms2.plan_id
and ms.sr_instance_id = ms2.sr_instance_id
and ms.inventory_item_id = ms2.inventory_item_id
and ms.organization_id = ms2.organization_id
and mpr.query_id =Mpq.Query_id
and mpers.query_type = 5
AND mpq.plan_id = pPlan_id
AND mpr.plan_id=mpq.plan_id
AND mpq.query_id = mpers.query_id
AND mpr.query_id = mpq.query_id
and Mpq.Applied_to = 2
);
Update MSC_SUPPLIES set releasable=9999 where transaction_id in
( select ms.transaction_id
from MSC_SUPPLIES ms,
MSC_PQ_RESULTS mpr,
MSC_ITEM_SUBSTITUTES mis,
--MSC_SUPPLIES ms2
MSC_ITEM_SUPPLIERS ms2
,MSC_PLAN_QUERIES mpq,
MSC_PERSONAL_QUERIES mpers
where mpr.plan_id = pPlan_id
and mpr.query_id =Mpq.Query_id
And ms2.plan_id = mpr.plan_id
And ms2.sr_instance_id = mpr.sr_instance_id
and ms2.inventory_item_id = mpr.inventory_item_id
and ms2.supplier_id = mpr.supplier_id
and NVL(ms2.supplier_site_id, -99999) = NVL( mpr.supplier_site_id,-99999)
and ( mis.lower_item_id = ms2.inventory_item_id OR mis.higher_item_id = ms2.inventory_item_id)
and mis.plan_id=ms2.plan_id
and mis.sr_instance_id = ms2.sr_instance_id
and mis.relationship_type = 8
and ms.sr_instance_id = mis.sr_instance_id
and ms.inventory_item_id in ( mis.higher_item_id , mis.lower_item_id)
and mpers.query_type = 5
AND mpq.plan_id = pPlan_id
AND mpr.plan_id=mpq.plan_id
AND mpq.query_id = mpers.query_id
AND mpr.query_id = mpq.query_id
and Mpq.Applied_to = 3
) ;
Update MSC_SUPPLIES set releasable=9999 where transaction_id in
(select ms.transaction_id
from MSC_SUPPLIES ms,
MSC_PQ_RESULTS mpr,
MSC_ITEM_SUBSTITUTES mis,
--MSC_SUPPLIES ms2
MSC_ITEM_SUPPLIERS ms2
,MSC_PLAN_QUERIES mpq,
MSC_PERSONAL_QUERIES mpers
where mpr.plan_id = pPlan_id
and mpr.query_id =Mpq.Query_id
And ms2.plan_id = mpr.plan_id
And ms2.sr_instance_id = mpr.sr_instance_id
and ms2.inventory_item_id = mpr.inventory_item_id
and ms2.organization_id = NVL(mpr.organization_id , ms2.organization_id)
and ms2.supplier_id = mpr.supplier_id
and NVL(ms2.supplier_site_id, -99999) = NVL( mpr.supplier_site_id,-99999)
and ( mis.lower_item_id = ms2.inventory_item_id OR mis.higher_item_id = ms2.inventory_item_id)
and mis.plan_id=ms2.plan_id
and mis.sr_instance_id = ms2.sr_instance_id
and mis.relationship_type = 8
and ms.sr_instance_id = mis.sr_instance_id
and ms.inventory_item_id in ( mis.higher_item_id , mis.lower_item_id)
and ms.organization_id = ms2.organization_id
and mpers.query_type = 5
AND mpq.plan_id = pPlan_id
AND mpr.plan_id=mpq.plan_id
AND mpq.query_id = mpers.query_id
AND mpr.query_id = mpq.query_id
and Mpq.Applied_to = 4
) ;
UPDATE MSC_SUPPLIES
set releasable = 99
where NVL(releasable,-1) = 9999
and plan_id = pPlan_id ;
UPDATE MSC_SUPPLIES
set releasable = 99
where NVL(releasable,-1) <> 9999
and plan_id = pPlan_id ;
UPDATE MSC_SUPPLIES
set releasable = NULL
where NVL(releasable,-1) = 9999
and plan_id = pPlan_id ;