[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT s.transaction_id, s.sr_instance_id
FROM msc_supplies s,
msc_plan_organizations_v orgs
where s.release_errors is NULL
AND s.po_line_id is not null
AND s.plan_id = orgs.plan_id
and s.load_type = PURCHASE_ORDER_RESCHEDULE
and s.order_type = PURCHASE_ORDER
AND s.organization_id = orgs.planned_organization
AND s.sr_instance_id = orgs.sr_instance_id
AND orgs.organization_id = arg_owning_org_id
AND orgs.owning_sr_instance = arg_owning_instance
AND orgs.plan_id = arg_plan_id
AND orgs.planned_organization = decode(arg_org_id,
arg_owning_org_id, orgs.planned_organization,
arg_org_id)
AND orgs.sr_instance_id = decode(arg_instance,
arg_owning_instance, orgs.sr_instance_id,
arg_instance);
SELECT s.transaction_id, s.sr_instance_id
FROM msc_supplies s,
msc_plan_organizations_v orgs
where s.release_errors is NULL
AND s.plan_id = orgs.plan_id
and s.load_type = PURCHASE_REQ_RESCHEDULE
and s.order_type = PURCHASE_REQ
AND s.po_line_id IS NOT NULL
AND s.organization_id = orgs.planned_organization
AND s.sr_instance_id = orgs.sr_instance_id
AND orgs.organization_id = arg_owning_org_id
AND orgs.owning_sr_instance = arg_owning_instance
AND orgs.plan_id = arg_plan_id
AND orgs.planned_organization = decode(arg_org_id,
arg_owning_org_id, orgs.planned_organization,
arg_org_id)
AND orgs.sr_instance_id = decode(arg_instance,
arg_owning_instance, orgs.sr_instance_id,
arg_instance);
SELECT distinct mai.instance_id,
decode(mai.m2a_dblink, null,' ','@'||m2a_dblink),
mai.instance_code
FROM msc_apps_instances mai,
msc_plan_organizations_v orgs
where orgs.organization_id = arg_owning_org_id
AND orgs.owning_sr_instance = arg_owning_instance
AND orgs.plan_id = arg_plan_id
AND orgs.planned_organization = decode(arg_org_id,
arg_owning_org_id, orgs.planned_organization,
arg_org_id)
AND orgs.sr_instance_id = decode(arg_instance,
arg_owning_instance, orgs.sr_instance_id,
arg_instance)
AND orgs.sr_instance_id = mai.instance_id;
SELECT distinct load_type
from msc_supplies
where plan_id = arg_plan_id
and sr_instance_id = v_instance_id
and load_type in (WIP_DIS_MASS_LOAD,WIP_REP_MASS_LOAD,
LOT_BASED_JOB_LOAD,LOT_BASED_JOB_RESCHEDULE,
WIP_DIS_MASS_RESCHEDULE,PURCHASE_REQ_MASS_LOAD,
EAM_DIS_MASS_RESCHEDULE);
msc_util.msc_debug('start workflow for batch update');
SELECT decode(mai.m2a_dblink, null,' ','@'||m2a_dblink),
mai.instance_code
FROM msc_apps_instances mai
WHERE mai.instance_id = p_instance_id;
deleteActivities(p_item_key);
deleteActivities(p_item_key,p_dblink);
select mp.compile_designator,
msi.item_name,
msc_get_name.org_code(ms.organization_id,ms.sr_instance_id),
msc_get_name.supplier(ms.supplier_id),
DECODE(ms.order_type,5,to_char(ms.transaction_id),ms.order_number),
msc_get_name.lookup_meaning('MRP_ORDER_TYPE',ms.order_type),
ms.new_schedule_date,
ms.implement_date,
ms.new_order_quantity,
msi.buyer_name
from msc_plans mp,
msc_system_items msi,
msc_supplies ms
where ms.plan_id = p_plan_id
and ms.transaction_id = p_transaction_id
and mp.plan_id = ms.plan_id
and msi.plan_id = ms.plan_id
and msi.organization_id = ms.organization_id
and msi.sr_instance_id = ms.sr_instance_id
and msi.inventory_item_id = ms.inventory_item_id
;
PROCEDURE Select_buyer_supplier( itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2 ) is
p_plan_id number;
select mpc.name
from msc_supplies ms,
msc_system_items msi,
msc_partner_contacts mpc
where ms.plan_id = p_plan_id
and ms.transaction_id = p_transaction_id
and msi.plan_id = ms.plan_id
and msi.organization_id = ms.organization_id
and msi.sr_instance_id = ms.sr_instance_id
and msi.inventory_item_id = ms.inventory_item_id
and msi.sr_instance_id = mpc.sr_instance_id
and msi.buyer_id = mpc.partner_id
and mpc.partner_type =4;
select mpc.name
from msc_supplies ms,
msc_partner_contacts mpc
where ms.plan_id = p_plan_id
and ms.transaction_id = p_transaction_id
and ms.sr_instance_id = mpc.sr_instance_id
and ms.supplier_id = mpc.partner_id
and mpc.partner_type =1;
select mp.compile_designator,
msc_get_name.item_name(ms.inventory_item_id, null,null,null),
msc_get_name.org_code(ms.organization_id,ms.sr_instance_id),
DECODE(ms.order_type,5,to_char(ms.transaction_id),ms.order_number),
msc_get_name.lookup_meaning('MRP_ORDER_TYPE',ms.order_type),
ms.new_schedule_date,
ms.implement_date,
ms.new_order_quantity
from msc_plans mp,
msc_supplies ms
where ms.plan_id = p_plan_id
and ms.transaction_id = p_transaction_id
and mp.plan_id = ms.plan_id
;
'select mrp_form_query_s.nextval'||p_dblink||
' from dual';
END select_buyer_supplier;
PROCEDURE DeleteActivities( p_item_key varchar2,
p_dblink varchar2 default null) IS
TYPE DelExpType is REF CURSOR;
delete_activities_c DelExpType;
sql_stmt := ' SELECT item_key ' ||
' FROM wf_items' || p_dblink ||
' WHERE item_type = :item_type' ||
' AND item_key like '''|| p_item_key || '%''';
OPEN delete_activities_c for sql_stmt USING g_item_type;
FETCH DELETE_ACTIVITIES_C INTO l_item_key;
EXIT WHEN DELETE_ACTIVITIES_C%NOTFOUND;
'update wf_notifications' || p_dblink ||
' set end_date = sysdate - 450' ||
' where group_id in ' ||
' (select notification_id' ||
' from wf_item_activity_statuses' ||p_dblink ||
' where item_type = :item_type' ||
' and item_key = :l_item_key' ||
' union' ||
' select notification_id' ||
' from wf_item_activity_statuses_h' || p_dblink ||
' where item_type = :item_type' ||
' and item_key = :l_item_key)';
' update wf_items' || p_dblink ||
' set end_date = sysdate - 450' ||
' where item_type = :item_type'||
' and item_key = :l_item_key';
' update wf_item_activity_statuses'|| p_dblink ||
' set end_date = sysdate - 450' ||
' where item_type = :item_type'||
' and item_key = :l_item_key';
' update wf_item_activity_statuses_h'|| p_dblink ||
' set end_date = sysdate - 450' ||
' where item_type = :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;
UPDATE MSC_SUPPLIES
SET implement_date = NULL,
release_status = NULL,
load_type = NULL
WHERE transaction_id= p_transaction_id
AND plan_id= p_plan_id;
select mtp.calendar_code
from msc_trading_partners mtp
where mtp.sr_tp_id = p_organization_id
and mtp.sr_instance_id = p_sr_instance_id
and mtp.partner_type = 3;
select distinct mp.sr_instance_id,
decode(mai.M2A_dblink,null,' ','@'||mai.M2A_dblink),
nvl(mai.A2M_dblink, '-1'),
mai.instance_code
from msc_plan_organizations mp,
msc_apps_instances mai
where plan_id = arg_plan_id
and mp.sr_instance_id = mai.instance_id
and mai.instance_type <> 3 -- xml fix
and nvl(mai.apps_ver,1) <> 1; -- not back port to 107 yet
select distinct mp.sr_instance_id
from msc_plan_organizations mp,
msc_apps_instances mai
where plan_id = arg_plan_id
and mp.sr_instance_id = mai.instance_id
and mai.instance_type = 3; -- xml fix
select plan_type
from msc_plans a
where
plan_id = p_plan_id;
SELECT s.sr_instance_id,
--trunc(nvl(s.promised_date,s.need_by_date)) +p_timestamp old_need_by_date,
-- bug 8979681
--trunc(nvl(s.promised_date,s.original_need_by_date)) ,
nvl(s.promised_date,s.old_need_by_date) ,--bug#13615378
trunc(min(get_dock_date(s.sr_instance_id,
s.receiving_calendar,
s.intransit_calendar,
NVL(s.implement_date,s.new_schedule_date),
NVL(msi.postprocessing_lead_time,0),
arg_plan_id, s.organization_id )))+ p_timestamp new_need_by_date,
s.disposition_id po_header_id,
s.po_line_id po_line_id,
s.order_number po_number,
min(s.implement_quantity) qty,
s.po_line_location_id shipment_id,
s.po_distribution_id distribution_id,
nvl(s.implement_uom_code,msi.uom_code) uom,
mp.operating_unit operating_unit,
s.disposition_status_type action
BULK COLLECT INTO
p_po_instance_id,
p_old_need_by_date,
p_new_need_by_date,
p_po_header_id,
p_po_line_id,
p_po_number,
p_po_quantity,
p_shipment_id,
p_distribution_id,
p_uom_code,
p_operating_unit,
p_action
FROM msc_apps_instances mai, -- xml fix
msc_system_items msi,
msc_trading_partners mp,
msc_supplies s
WHERE msi.inventory_item_id = s.inventory_item_id
AND msi.plan_id = s.plan_id
AND msi.organization_id = s.organization_id
and msi.sr_instance_id = s.sr_instance_id
AND mp.sr_tp_id = msi.organization_id
AND mp.sr_instance_id = msi.sr_instance_id
AND mp.partner_type= 3
and mai.instance_id = s.sr_instance_id -- xml fix
and mai.instance_type <> 3 -- xml fix- only for non legacy
AND s.plan_id = arg_plan_id
AND s.release_errors is NULL
and s.load_type = 20
and s.order_type = 1
and s.last_updated_by = decode(p_release_by_user,'Y', p_user_id,
s.last_updated_by)
group by s.sr_instance_id,
--trunc(nvl(s.promised_date,s.original_need_by_date)) ,
nvl(s.promised_date,s.old_need_by_date) ,
s.disposition_id,
s.po_line_id, s.order_number, s.disposition_status_type,
s.po_line_location_id, po_distribution_id,
nvl(s.implement_uom_code,msi.uom_code), mp.operating_unit;
SELECT s.sr_instance_id,
nvl(s.promised_date,s.need_by_date) old_need_by_date,
min(get_dock_date(s.sr_instance_id,
s.receiving_calendar,
s.intransit_calendar,
NVL(s.implement_date,s.new_schedule_date),
NVL(msi.postprocessing_lead_time,0),
arg_plan_id, s.organization_id)) new_need_by_date,
s.disposition_id po_header_id,
s.po_line_id po_line_id,
s.order_number po_number,
min(s.implement_quantity) qty,
s.po_line_location_id shipment_id,
s.po_distribution_id distribution_id,
nvl(s.implement_uom_code,msi.uom_code) uom,
mp.operating_unit operating_unit,
s.disposition_status_type action
BULK COLLECT INTO
p_po_instance_id,
p_old_need_by_date,
p_new_need_by_date,
p_po_header_id,
p_po_line_id,
p_po_number,
p_po_quantity,
p_shipment_id,
p_distribution_id,
p_uom_code,
p_operating_unit,
p_action
FROM msc_apps_instances mai, -- xml fix
msc_system_items msi,
msc_trading_partners mp,
msc_supplies s
WHERE msi.inventory_item_id = s.inventory_item_id
AND msi.plan_id = s.plan_id
AND msi.organization_id = s.organization_id
and msi.sr_instance_id = s.sr_instance_id
AND mp.sr_tp_id = msi.organization_id
AND mp.sr_instance_id = msi.sr_instance_id
AND mp.partner_type= 3
and mai.instance_id = s.sr_instance_id -- xml fix
and mai.instance_type <> 3 -- xml fix- only for non legacy
AND s.plan_id = arg_plan_id
AND s.release_errors is NULL
and s.load_type = 20
and s.order_type = 1
and s.last_updated_by = decode(p_release_by_user,'Y', p_user_id,
s.last_updated_by)
group by s.sr_instance_id,nvl(s.promised_date,s.need_by_date),
s.disposition_id,
s.po_line_id, s.order_number, s.disposition_status_type,
s.po_line_location_id, po_distribution_id,
nvl(s.implement_uom_code,msi.uom_code), mp.operating_unit;
select msc_form_query_s.nextval
into v_batch_id
from dual;
insert into msc_purchase_order_interface
(last_update_date,
last_updated_by,
creation_date,
created_by,
batch_id,
sr_instance_id,
old_need_by_date,
new_need_by_date,
po_header_id,
po_line_id,
po_number,
po_quantity,
action,
po_line_location_id,
po_distribution_id,
uom,
operating_unit)
values
(sysdate,
p_user_id,
sysdate,
p_user_id,
v_batch_id,
p_po_instance_id(a),
p_old_need_by_date(a),
p_new_need_by_date(a),
p_po_header_id(a),
p_po_line_id(a),
p_po_number(a),
p_po_quantity(a),
p_action(a),
p_shipment_id(a),
p_distribution_id(a),
p_uom_code(a),
p_operating_unit(a));
SELECT s.sr_instance_id,
s.transaction_id, -- xml fix
nvl(s.implement_uom_code,msi.uom_code), -- xml fix
trunc(nvl(s.promised_date,s.need_by_date))+p_timestamp old_need_by_date,
trunc(get_dock_date(s.sr_instance_id,
s.receiving_calendar,
s.intransit_calendar,
NVL(s.implement_date,
s.new_schedule_date),
NVL(msi.postprocessing_lead_time, 0),
arg_plan_id, s.organization_id))+p_timestamp new_need_by_date,
s.disposition_id po_header_id,
s.po_line_id po_line_id,
s.order_number po_number,
min(s.implement_quantity) qty,
s.plan_id
BULK COLLECT INTO
p_po_instance_id,
p_source_line_id, -- xml fix
p_uom_code, -- xml fix
p_old_need_by_date,
p_new_need_by_date,
p_po_header_id,
p_po_line_id,
p_po_number,
p_po_quantity,
p_plan_id
FROM msc_apps_instances mai,
msc_system_items msi,
msc_supplies s
WHERE msi.inventory_item_id = s.inventory_item_id
AND msi.plan_id = s.plan_id
AND msi.organization_id = s.organization_id
and msi.sr_instance_id = s.sr_instance_id
and mai.instance_id = s.sr_instance_id -- xml fix
and mai.instance_type = 3 -- xml fix
AND s.plan_id = arg_plan_id
AND s.release_errors is NULL
and s.load_type = 20
and s.order_type = 1
and s.last_updated_by = decode(p_release_by_user,'Y', p_user_id,
s.last_updated_by)
group by s.sr_instance_id, s.transaction_id,
nvl(s.implement_uom_code,msi.uom_code),
trunc(nvl(s.promised_date,s.need_by_date))+p_timestamp,
trunc(get_dock_date(s.sr_instance_id,
s.receiving_calendar,
s.intransit_calendar,
NVL(s.implement_date,
s.new_schedule_date),
NVL(msi.postprocessing_lead_time, 0),
arg_plan_id, s.organization_id))+p_timestamp,
s.disposition_id,s.po_line_id, s.order_number,s.plan_id;
SELECT s.sr_instance_id,
s.transaction_id, -- xml fix
nvl(s.implement_uom_code,msi.uom_code), -- xml fix
nvl(s.promised_date,s.need_by_date) old_need_by_date,
get_dock_date(s.sr_instance_id,
s.receiving_calendar,
s.intransit_calendar,
NVL(s.implement_date,
s.new_schedule_date),
NVL(msi.postprocessing_lead_time, 0),
arg_plan_id, s.organization_id) new_need_by_date,
s.disposition_id po_header_id,
s.po_line_id po_line_id,
s.order_number po_number,
min(s.implement_quantity) qty,
s.plan_id
BULK COLLECT INTO
p_po_instance_id,
p_source_line_id, -- xml fix
p_uom_code, -- xml fix
p_old_need_by_date,
p_new_need_by_date,
p_po_header_id,
p_po_line_id,
p_po_number,
p_po_quantity,
p_plan_id
FROM msc_apps_instances mai,
msc_system_items msi,
msc_supplies s
WHERE msi.inventory_item_id = s.inventory_item_id
AND msi.plan_id = s.plan_id
AND msi.organization_id = s.organization_id
and msi.sr_instance_id = s.sr_instance_id
and mai.instance_id = s.sr_instance_id -- xml fix
and mai.instance_type = 3 -- xml fix
AND s.plan_id = arg_plan_id
AND s.release_errors is NULL
and s.load_type = 20
and s.order_type = 1
and s.last_updated_by = decode(p_release_by_user,'Y', p_user_id,
s.last_updated_by)
group by s.sr_instance_id, s.transaction_id,
nvl(s.implement_uom_code,msi.uom_code),
nvl(s.promised_date,s.need_by_date),
get_dock_date(s.sr_instance_id,
s.receiving_calendar,
s.intransit_calendar,
NVL(s.implement_date,
s.new_schedule_date),
NVL(msi.postprocessing_lead_time, 0),
arg_plan_id, s.organization_id),
s.disposition_id,s.po_line_id, s.order_number,s.plan_id;
INSERT INTO msc_po_reschedule_interface
(process_id,
quantity,
need_by_date,
line_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
purchase_order_id,
po_number,
source_line_id,
uom,
SR_INSTANCE_ID,
plan_id)
VALUES (
NULL,
p_po_quantity(a),
p_new_need_by_date(a),
p_po_line_id(a),
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
p_po_header_id(a),
p_po_number(a),
p_source_line_id(a),
p_uom_code(a),
p_po_instance_id(a),
p_plan_id(a));
select count(*)
into v_temp
from msc_po_reschedule_interface
where sr_instance_id = cur.sr_instance_id;
select count(*)
into v_temp2
from msc_supplies
where plan_id = arg_plan_id
anD release_errors is NULL
and load_type = 20
and order_type = 1
and sr_instance_id = cur.sr_instance_id
and last_updated_by = decode(p_release_by_user,'Y', p_user_id,
last_updated_by);
select count(*)
into v_temp
from msc_purchase_order_interface
where sr_instance_id = p_instance_id
and batch_id = v_batch_id;
select count(*)
into v_temp2
from msc_supplies
where plan_id = arg_plan_id
AND release_errors is NULL
and load_type = 20
and order_type = 1
and sr_instance_id = p_instance_id
and last_updated_by = decode(p_release_by_user,'Y', p_user_id,
last_updated_by);
/* for auto-release we do not want applied and status to be updated */
SELECT release_reschedules
INTO v_autorelease
FROM msc_plans
WHERE plan_id=arg_plan_id;
UPDATE MSC_SUPPLIES
SET implement_date = NULL,
release_status = decode(sign(p_plan_type-100),1,
decode(release_status,11,21,
12,22,
13,23),
NULL),
load_type = NULL,
applied = decode(v_autorelease,1,applied,2),
status = decode(v_autorelease,1,status,0)
WHERE plan_id= arg_plan_id
and release_errors is NULL
and load_type = 20
and order_type =1
and last_updated_by = decode(p_release_by_user,'Y', p_user_id,
last_updated_by);
select distinct mp.sr_instance_id,
decode(mai.M2A_dblink,null,' ','@'||mai.M2A_dblink),
decode(mai.A2M_dblink,null,' ','@'||mai.A2M_dblink)
from msc_plan_organizations mp,
msc_apps_instances mai
where plan_id = arg_plan_id
and mp.sr_instance_id = mai.instance_id
-- and mai.instance_type <> 3 -- xml fix
and nvl(mai.apps_ver,1) <> 1; -- not back port to 107 yet
select sr_inventory_item_id
from msc_system_items msi
where msi.plan_id = arg_plan_id
and msi.organization_id =l_org_id
and msi.sr_instance_id = l_inst_id
and msi.inventory_item_id = l_item_id;
select plan_type
from msc_plans a
where
plan_id = p_plan_id;
SELECT md.sr_instance_id,
NVL(md.sales_order_line_id,0),
md.implement_org_id,
md.implement_instance_id,
trunc(md.implement_earliest_date) + p_timestamp, -- Earliest ship date
trunc(md.implement_ship_date)+p_timestamp,
trunc(nvl(md.implement_arrival_date,md.schedule_ship_date)) +p_timestamp,
mtp.operating_unit,
md.demand_id,
md.ship_method,
NVL(md.intransit_lead_time,0),
nvl(md.implement_firm,2),
NVL(nvl(md.prev_subst_org,md.original_org_id),
md.organization_id),
trunc(md.schedule_arrival_date) + p_timestamp,
trunc(md.schedule_ship_date)+p_timestamp,
md.orig_shipping_method_code,
NVL(md.orig_intransit_lead_time,0),
md.order_number,
decode(md.customer_id, null, 100, to_number(null)),
decode(md.customer_id, null,
nvl(md.quantity_by_due_date,
md.using_requirement_quantity),
md.using_requirement_quantity),
nvl(nvl(md.prev_subst_item,md.original_item_id),
md.inventory_item_id),
md.inventory_item_id,
md.organization_id
BULK COLLECT INTO
p_instance_id,
p_so_line_id,
p_so_org_id,
p_so_instance_id,
p_earliest_ship_date,
p_ship_date,
p_arrival_date,
p_operating_unit,
p_demand_id,
p_ship_method,
p_lead_time,
p_implement_firm,
p_orig_org_id,
p_orig_arrival_date,
p_orig_ship_date,
p_orig_ship_method,
p_orig_lead_time,
p_order_number,
p_source_type,
p_qty,
p_original_item_id,
p_substitute_item_id,
p_org_id
FROM msc_demands md,
msc_trading_partners mtp
WHERE md.plan_id = arg_plan_id
AND md.release_errors is NULL
and md.load_type = 30
and md.origination_type = 30
and md.last_updated_by = decode(p_release_by_user,'Y', p_user_id,
md.last_updated_by)
AND mtp.sr_tp_id = md.organization_id
AND mtp.sr_instance_id = md.sr_instance_id
AND mtp.partner_type= 3
order by mtp.operating_unit, md.order_number, md.arrival_set_id,md.ship_set_id;
SELECT md.sr_instance_id,
NVL(md.sales_order_line_id,0),
md.implement_org_id,
md.implement_instance_id,
md.implement_earliest_date, -- Earliest ship date
md.implement_ship_date,
nvl(md.implement_arrival_date,md.schedule_ship_date),
mtp.operating_unit,
md.demand_id,
md.ship_method,
NVL(md.intransit_lead_time,0),
nvl(md.implement_firm,2),
NVL(nvl(md.prev_subst_org,md.original_org_id),
md.organization_id),
md.schedule_arrival_date,
md.schedule_ship_date,
md.orig_shipping_method_code,
NVL(md.orig_intransit_lead_time,0),
md.order_number,
decode(md.customer_id, null, 100, to_number(null)),
decode(md.customer_id, null,
nvl(md.quantity_by_due_date,
md.using_requirement_quantity),
md.using_requirement_quantity),
nvl(nvl(md.prev_subst_item,md.original_item_id),
md.inventory_item_id),
md.inventory_item_id,
md.organization_id
BULK COLLECT INTO
p_instance_id,
p_so_line_id,
p_so_org_id,
p_so_instance_id,
p_earliest_ship_date,
p_ship_date,
p_arrival_date,
p_operating_unit,
p_demand_id,
p_ship_method,
p_lead_time,
p_implement_firm,
p_orig_org_id,
p_orig_arrival_date,
p_orig_ship_date,
p_orig_ship_method,
p_orig_lead_time,
p_order_number,
p_source_type,
p_qty,
p_original_item_id,
p_substitute_item_id,
p_org_id
FROM msc_demands md,
msc_trading_partners mtp
WHERE md.plan_id = arg_plan_id
AND md.release_errors is NULL
and md.load_type = 30
and md.origination_type = 30
and md.last_updated_by = decode(p_release_by_user,'Y', p_user_id,
md.last_updated_by)
AND mtp.sr_tp_id = md.organization_id
AND mtp.sr_instance_id = md.sr_instance_id
AND mtp.partner_type= 3
order by mtp.operating_unit, md.order_number, md.arrival_set_id,md.ship_set_id;
select msc_form_query_s.nextval
into v_batch_id
from dual;
insert into msc_sales_order_interface
(last_update_date,
last_updated_by,
creation_date,
created_by,
batch_id,
sr_instance_id,
line_id,
operating_unit,
header_id,
org_id,
schedule_ship_date,
schedule_arrival_date,
earliest_ship_date,
delivery_lead_time,
ship_method,
orig_org_id,
orig_schedule_ship_date,
orig_schedule_arrival_date,
orig_lead_time,
orig_ship_method,
quantity,
firm_flag,
source_type,
order_number,
demand_id,
plan_id,
orig_item_id,
inventory_item_id)
values
(sysdate,
p_user_id,
sysdate,
p_user_id,
v_batch_id,
p_instance_id(a),
p_so_line_id(a),
p_operating_unit(a),
null, -- so_header_id: we don't hv this in destination
p_so_org_id(a),
p_ship_date(a),
p_arrival_date(a),
p_earliest_ship_date(a),
p_lead_time(a),
p_ship_method(a),
p_orig_org_id(a),
p_orig_ship_date(a),
p_orig_arrival_date(a),
p_orig_lead_time(a),
p_orig_ship_method(a),
p_qty(a),
p_implement_firm(a),
p_source_type(a),
p_order_number(a),
p_demand_id(a),
arg_plan_id,
p_original_item_id(a),
p_substitute_item_id(a));
select count(*)
into v_temp
from msc_sales_order_interface
where sr_instance_id = p_inst_id
and batch_id = v_batch_id;
lv_sql_stmt:= 'select meaning from fnd_lookups'||p_dblink||
' where lookup_type = :p_type '||
' and lookup_code = :p_code ';
UPDATE msc_sales_order_interface
set source_type = null,
quantity = null
where source_type =100 -- customer_id is null
and order_number not like '%'||crm_char
and sr_instance_id = p_inst_id
and batch_id = v_batch_id;
select count(*)
into v_temp2
from msc_demands
where plan_id = arg_plan_id
anD release_errors is NULL
and load_type = 30
and sr_instance_id = p_inst_id
and last_updated_by = decode(p_release_by_user,'Y', p_user_id,
last_updated_by);
UPDATE MSC_DEMANDS
SET /* implement_date = NULL,
implement_ship_date = NULL,
implement_earliest_date = NULL,
implement_arrival_date = NULL,
implement_org_id = NULL,
implement_instance_id = NULL,
implement_firm = null, */
release_status = decode(sign(p_plan_type-100),1,
decode(release_status,11,21,
12,22,
13,23),
NULL),
--- release_status = NULL,
load_type = NULL,
applied = 2,
status =0
WHERE plan_id= arg_plan_id
and release_errors is NULL
and last_updated_by = decode(p_release_by_user,'Y', p_user_id,
last_updated_by)
and load_type = 30;
select decode(M2A_dblink,null,' ','@'||M2A_dblink)
from msc_apps_instances
where instance_id = p_instance_id;
sql_stmt := 'SELECT wip_job_number_s.nextval'||db_link||' from dual';
select decode(M2A_dblink,null,' ','@'||M2A_dblink),
apps_ver
from msc_apps_instances
where instance_id = p_instance_id;
SELECT APPLICATION_ID
INTO l_application_id
FROM FND_APPLICATION_VL
WHERE APPLICATION_SHORT_NAME = 'MSC'
and rownum =1 ;
select decode(M2A_dblink,null,' ','@'||M2A_dblink),apps_ver
from msc_apps_instances
where instance_id = p_instance_id;
sql_stmt := 'SELECT DEFAULT_DISCRETE_CLASS
FROM WIP_PARAMETERS'||db_link||
' WHERE ORGANIZATION_ID = :p_org_id';
select decode(M2A_dblink,null,' ','@'||M2A_dblink)
from msc_apps_instances
where instance_id = p_instance_id;
sql_stmt := 'SELECT 1 from dual'||db_link;
sql_stmt := 'SELECT 1 from dual'||db_link;
deleteActivities(p_item_key);
deleteActivities(p_item_key,p_dblink);
p_process := 'BATCH_UPDATE';
update msc_supplies
SET implement_demand_class = NULL,
implement_date = NULL,
implement_quantity = NULL,
implement_firm = NULL,
implement_wip_class_code = NULL,
implement_job_name = NULL,
implement_status_code = NULL,
implement_location_id = NULL,
implement_source_org_id = NULL,
implement_supplier_id = NULL,
implement_supplier_site_id = NULL,
implement_project_id = NULL,
implement_task_id = NULL,
release_status = NULL,
load_type = NULL,
implement_as = NULL,
implement_unit_number = NULL,
implement_schedule_group_id = NULL,
implement_build_sequence = NULL,
implement_line_id = NULL,
implement_alternate_bom = NULL,
implement_alternate_routing = NULL
WHERE organization_id IN
(select planned_organization
from msc_plan_organizations_v
where organization_id = p_owning_org
and owning_sr_instance = p_owning_instance
and plan_id = p_plan_id
AND planned_organization = decode(p_org_id,
p_owning_org, planned_organization,
p_org_id)
AND sr_instance_id = p_instance_id )
AND sr_instance_id= p_instance_id
AND plan_id = p_plan_id
AND release_errors IS NULL
AND load_type = p_load_type;
Procedure insert_temp_table(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2 ) IS
p_plan_id number;
select apps_ver
from msc_apps_instances
where instance_id = p_instance_id;
msc_util.msc_debug('insert temp table now');
msc_util.msc_debug('# of rows updated:'|| p_count);
msc_util.msc_debug('no rows are inserted');
END insert_temp_table;
DELETE msc_wip_job_schedule_interface
WHERE sr_instance_id= p_instance;
DELETE MSC_WIP_JOB_DTLS_INTERFACE
WHERE sr_instance_id= p_instance;
DELETE msc_wip_job_schedule_interface
WHERE sr_instance_id= p_instance;
DELETE MSC_WIP_JOB_DTLS_INTERFACE
WHERE sr_instance_id= p_instance;
DELETE MSC_PO_REQUISITIONS_INTERFACE
WHERE sr_instance_id= p_instance;
select msc_get_name.org_code(s.organization_id,s.sr_instance_id),
msc_get_name.supplier(s.supplier_id),
msc_get_name.supplier_site(s.supplier_site_id),
msc_get_name.lookup_meaning('MRP_ORDER_TYPE',s.order_type),
DECODE(s.order_type,5,to_char(s.transaction_id),s.order_number),
s.new_schedule_date,
s.new_order_quantity,
mp.compile_designator,
msi.item_name,
msi.buyer_name,
decode(s.order_type, 1, s.disposition_id, null),
s.po_line_id,
s.implement_quantity,
cal2.calendar_date
from msc_supplies s,
msc_system_items msi,
msc_plans mp,
msc_calendar_dates cal1,
msc_calendar_dates cal2,
msc_trading_partners mtp
where s.plan_id = p_plan_id
and s.transaction_id =p_transaction_id
and s.plan_id = mp.plan_id
and s.plan_id = msi.plan_id
and s.organization_id = msi.organization_id
and s.sr_instance_id = msi.sr_instance_id
and s.inventory_item_id = msi.inventory_item_id
and cal1.sr_instance_id = mtp.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(NVL(s.implement_date,s.new_schedule_date))
AND cal2.sr_instance_id = cal1.sr_instance_id
AND cal2.calendar_code = cal1.calendar_code
AND cal2.exception_set_id = cal1.exception_set_id
AND cal2.seq_num = GREATEST(1,NVL(cal1.seq_num, cal1.prior_seq_num) -
NVL(msi.postprocessing_lead_time, 0))
AND mtp.sr_tp_id = msi.organization_id
AND mtp.sr_instance_id = msi.sr_instance_id
AND mtp.partner_type= 3;
SELECT new_dock_date
FROM msc_supplies
WHERE plan_id = -1
AND transaction_id = p_transaction_id;
'insert into mrp_form_query'||p_dblink||
' (query_id,'||
' last_update_date,'||
' last_updated_by,'||
' creation_date,'||
' created_by,'||
' char1,'||
' char2,'||
' char3,'||
' char4,'||
' char5,'||
' char6,'||
' char7,'||
' char8,'||
' date1,'||
' date2,'||
' date3,'||
' number1,'||
' number2,'||
' number3,'||
' number4)'||
' VALUES('||
' :p_query_id,'||
' sysdate,'||
' -1,'||
' sysdate,'||
' -1,'||
' :l_org_code,'||
' :l_supplier,'||
' :l_supplier_site,'||
' :l_order_type,'||
' :l_order,'||
' :l_plan_name,'||
' :l_item_name,'||
' :l_buyer,'||
' :l_new_due_date,'||
' :l_new_need_by_date,'||
' :l_old_need_by_date,'||
' :l_qty,'||
' :l_impl_qty,'||
' :l_po_header_id,'||
' :l_po_line_id)';
select user_id
into l_user_id
from fnd_user
where user_name = p_user_name;
SELECT APPLICATION_ID
INTO l_application_id
FROM FND_APPLICATION_VL
WHERE APPLICATION_SHORT_NAME = 'MSC'
and rownum =1 ;
SELECT responsibility_id
INTO l_resp_id
FROM FND_responsibility_vl
where application_Id = l_application_id
and rownum =1 ;
SELECT APPLICATION_ID
INTO l_application_id
FROM FND_APPLICATION_VL
WHERE APPLICATION_SHORT_NAME = 'MRP'
and rownum = 1;
SELECT responsibility_id
INTO l_resp_id
FROM FND_responsibility_vl
where application_Id = l_application_id
and rownum =1 ;
select application_short_name
from fnd_application_vl
where application_name = p_appl_name;
SELECT DECODE( M2A_DBLINK, NULL, ' ', '@'||M2A_DBLINK),
DECODE( M2A_DBLINK, NULL, ' ', M2A_DBLINK)
INTO lv_dblink, lv_dblink2
FROM msc_apps_instances
WHERE instance_id = p_instance_id;
SELECT FND_GLOBAL.USER_NAME,
FND_GLOBAL.RESP_NAME,
FND_GLOBAL.APPLICATION_NAME
INTO lv_user_name,
lv_resp_name,
lv_application_name
FROM dual;
lv_sql_stmt:= 'SELECT mrp_rel_wf.get_profile_value'||lv_dblink||'('||
':1, :2, :3, :4) from dual';
PROCEDURE update_so_dates(p_plan_id number, p_demand_id number,
p_inst_id number, p_implement_date date,
p_ship_date out nocopy date,
p_arrival_date out nocopy date,
p_earliest_date out nocopy date) IS
TYPE NumArr IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
select order_number,
organization_id org_id,
sr_instance_id inst_id,
origination_type demand_type,
ship_set_id, arrival_set_id,
dmd_satisfied_date earliest_ship_date,
nvl(planned_ship_date,dmd_satisfied_date) planned_ship_date,
planned_arrival_date,
nvl(p_implement_date,nvl(firm_date, nvl(planned_ship_date,
dmd_satisfied_date))) ship_date,
order_date_type_code order_type,
decode(order_date_type_code, 1, request_ship_date,
request_date) request_date,
decode(order_date_type_code, 1, schedule_ship_date,
schedule_arrival_date) schedule_date,
intransit_lead_time lead_time,
inventory_item_id,
ship_method,
customer_id,
customer_site_id
from msc_demands
where plan_id = p_plan_id
and demand_id = p_demand_id
and sr_instance_id = p_inst_id;
select
md.demand_id,
md.dmd_satisfied_date, --earliest ship date,
decode(md.demand_id, p_demand_id,
nvl(p_implement_date, --plan ship date
nvl(firm_date, nvl(md.planned_ship_date,
md.dmd_satisfied_date))),
nvl(implement_date,
nvl(firm_date, nvl(md.planned_ship_date,
md.dmd_satisfied_date)))),
md.request_ship_date,
md.schedule_ship_date,
nvl(md.planned_ship_date,md.dmd_satisfied_date),
md.planned_arrival_date,
md.intransit_lead_time
from msc_demands md,
msc_system_items msi
where md.plan_id = p_plan_id
and md.ship_set_id = p_ship_set_id
and msi.plan_id = md.plan_id
and msi.organization_id = md.organization_id
and msi.sr_instance_id = md.sr_instance_id
and msi.inventory_item_id = md.inventory_item_id
and nvl(msi.bom_item_type,4) <> 5; -- not a product family
select
md.demand_id,
md.dmd_satisfied_date, --earliest ship date
decode(md.demand_id, p_demand_id, --plan ship date
nvl(p_implement_date,
nvl(firm_date, nvl(md.planned_ship_date,
md.dmd_satisfied_date))),
nvl(implement_date,
nvl(firm_date, nvl(md.planned_ship_date,
md.dmd_satisfied_date)))),
md.request_date,
md.schedule_arrival_date,
md.intransit_lead_time,
md.organization_id,
md.sr_instance_id,
nvl(md.planned_ship_date,md.dmd_satisfied_date),
md.planned_arrival_date
from msc_demands md,
msc_system_items msi
where md.plan_id = p_plan_id
and md.arrival_set_id = p_arrival_set_id
and msi.plan_id = md.plan_id
and msi.organization_id = md.organization_id
and msi.sr_instance_id = md.sr_instance_id
and msi.inventory_item_id = md.inventory_item_id
and nvl(msi.bom_item_type,4) <> 5; -- not a product family
select plan_type
from msc_plans a
where
plan_id = p_plan_id;
update msc_demands
set implement_ship_date = p_ship_date, -- sche ship date
implement_date = nvl(implement_date, nvl(firm_date,
nvl(planned_ship_date,dmd_satisfied_date))),
implement_earliest_date = p_earliest_date, -- earliest ship date
implement_arrival_date = v_planned_arrival_date(a),
implement_org_id = organization_id,
implement_instance_id = sr_instance_id,
implement_firm = nvl(implement_firm, org_firm_flag),
load_type = 30,
reschedule_flag = 1,
release_status = decode(sign(p_plan_type-100),1,13,1),
--- for rp, 13= mark for release
status = 0,
applied =2,
last_updated_by = fnd_global.user_id
where plan_id = p_plan_id
and demand_id = v_set_demand_id(a);
update msc_demands
set implement_arrival_date = p_arrival_date,
implement_earliest_date = v_earliest_date(a),
implement_ship_date = v_ship_date(a), -- sche ship date
implement_date = nvl(implement_date, nvl(firm_date,
nvl(planned_ship_date,dmd_satisfied_date))),
implement_org_id = organization_id,
implement_instance_id = sr_instance_id,
implement_firm = nvl(implement_firm, org_firm_flag),
load_type = 30,
reschedule_flag = 1,
release_status = decode(sign(p_plan_type-100),1,13,1),
status = 0,
applied =2,
last_updated_by = fnd_global.user_id
where plan_id = p_plan_id
and demand_id = v_set_demand_id(a);
update msc_demands
set implement_earliest_date = p_earliest_date
where plan_id = p_plan_id
and demand_id = p_demand_id;
END update_so_dates;
select ship_set_id, arrival_set_id
from msc_demands
where plan_id = p_plan_id
and demand_id = p_demand_id
and sr_instance_id = p_instance_id;
UPDATE MSC_DEMANDS
SET implement_date = NULL,
implement_ship_date = NULL,
implement_earliest_date = NULL,
implement_arrival_date = NULL,
implement_org_id = NULL,
implement_instance_id = NULL,
implement_firm = null,
release_status = NULL,
reschedule_flag = null,
load_type = NULL,
applied = 2,
status =0
WHERE plan_id= p_plan_id
AND origination_type = 30
AND ship_set_id = p_ship_set_id
AND demand_id <> p_demand_id;
UPDATE MSC_DEMANDS
SET implement_date = NULL,
implement_ship_date = NULL,
implement_earliest_date = NULL,
implement_arrival_date = NULL,
implement_org_id = NULL,
implement_instance_id = NULL,
implement_firm = null,
reschedule_flag = null,
release_status = NULL,
load_type = NULL,
applied = 2,
status =0
WHERE plan_id= p_plan_id
AND origination_type = 30
AND arrival_set_id = p_arrival_set_id
AND demand_id <> p_demand_id;
select order_number,
organization_id org_id,
origination_type demand_type,
ship_set_id, arrival_set_id,
decode(nvl(prev_subst_item,0),
inventory_item_id, 0,0,0,1) subst_item
from msc_demands
where plan_id = p_plan_id
and demand_id = p_demand_id
and sr_instance_id = p_inst_id;
select 1
from msc_demands
where plan_id = p_plan_id
and order_number = p_order_number
and origination_type = p_order_type
and organization_id <> p_org_id
and using_requirement_quantity <> 0;
select 1
from msc_demands
where plan_id = p_plan_id
and ship_set_id = p_ship_set_id
and organization_id <> p_org_id ;
select
decode(nvl(original_item_id,0), inventory_item_id, 0,0,0,1)
from msc_demands
where plan_id = p_plan_id
and ship_set_id = p_ship_set_id;
select
decode(nvl(original_item_id,0), inventory_item_id, 0,0,0,1)
from msc_demands
where plan_id = p_plan_id
and arrival_set_id = p_arrival_set_id;
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 msi.item_name,
md.order_number,
msi.description item_desc,
msc_get_name.customer(md.customer_id) customer_name,
msc_get_name.customer_site(md.customer_site_id) customer_site,
msc_get_name.org_code(nvl(md.prev_subst_org, md.original_org_id),
md.original_inst_id) org_code,
msc_get_name.org_code(md.organization_id,md.sr_instance_id) to_org,
msoi.schedule_ship_date new_ship_date,
md.schedule_ship_date old_ship_date,
msoi.schedule_arrival_date new_arrival_date,
md.schedule_arrival_date old_arrival_date,
msoi.ship_method new_ship_method,
md.orig_shipping_method_code old_ship_method,
md.orig_intransit_lead_time old_lead_time,
msoi.delivery_lead_time new_lead_time,
msoi.earliest_ship_date earliest_ship_date,
msoi.return_status,
md.demand_id,
md.inventory_item_id,
md.organization_id,
md.sr_instance_id,
md.plan_id,
mp.compile_designator plan_name,
msoi.line_number line_number,
msc_get_name.lookup_meaning('SYS_YES_NO',msoi.return_status) atp_override_flag,
msc_get_name.item_name(nvl(md.prev_subst_item,md.original_item_id),
null,null,null)
orig_item_name,
msc_get_name.item_desc(nvl(md.prev_subst_item,md.original_item_id),
nvl(md.prev_subst_org, md.original_org_id),
md.plan_id,md.original_inst_id)
orig_item_desc
from msc_system_items msi,
msc_plans mp,
msc_demands md,
msc_sales_order_interface msoi
where msoi.batch_id = p_batch_id
and msoi.sr_instance_id = p_instance_id
and msoi.plan_id = md.plan_id
and msoi.demand_id = md.demand_id
and msoi.return_status is not null
and msi.plan_id = md.plan_id
and msi.organization_id = md.organization_id
and msi.sr_instance_id = md.sr_instance_id
and msi.inventory_item_id = md.inventory_item_id
and mp.plan_id = msoi.plan_id;
select to_char(mrp_form_query_s.nextval)
into item_key
from dual;