[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)
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
AND 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
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.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))
BETWEEN TRUNC(var_start_date) AND TRUNC(cal2.calendar_date)
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)
/* 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
),
SYS_NO
) = 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(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)
-- 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 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_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)
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.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_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)
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.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 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= arg_instance_id;
'SELECT mar.A2M_DBLINK '
||' FROM MRP_AP_APPS_INSTANCES_ALL'||var_dblink||' mar'
||' WHERE mar.ALLOW_RELEASE_FLAG = 1 ';
SELECT DECODE( M2A_DBLINK, NULL, ' ', '@'||M2A_DBLINK),
APPS_VER
INTO var_dblink,
var_apps_ver
FROM MSC_APPS_INSTANCES
WHERE INSTANCE_ID= arg_instance_id;
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
INTO var_plan_type,
var_start_date,
var_demand_class,
lv_plan_name
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= arg_instance_id
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';
UPDATE msc_supplies
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),
/*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,
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
AND batch_id=msc_rel_plan_pub.g_batch_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_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_supplier_id = source_supplier_id,
implement_supplier_site_id = source_supplier_site_id,
implement_project_id = project_id,
implement_task_id = task_id,
implement_unit_number = unit_number,
implement_demand_class = NULL,
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
AND batch_id=MSC_REL_PLAN_PUB.g_batch_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_plans
SET release_reschedules=2
WHERE plan_id=arg_plan_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);
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);