[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 1
from MSC_EXCEPTION_PREFERENCES ep,
fnd_user u
where ep.user_id = u.user_id
and u.user_name = p_user
and exception_type_lookup_code = p_excep_type
and rank > 0;
l_select_flag number; --- Bug # 6242764
fetch check_user into l_select_flag;
select ex.exception_detail_id,
ex.sr_instance_id,
ex.company_id,
ex.company_name,
ex.company_site_id,
ex.company_site_name,
ex.inventory_item_id,
ex.item_name,
ex.item_description,
ex.exception_group,
ex.exception_type,
ex.exception_type_name,
ex.supplier_id,
ex.supplier_name,
ex.supplier_site_id,
ex.supplier_site_name,
ex.trading_partner_item_name,
ex.customer_id,
ex.customer_name,
ex.customer_site_id,
ex.customer_site_name,
ex.trading_partner_item_name,
ex.number3, --based item qty
ex.transaction_id1, --base item trx id
ex.transaction_id2, --pegged item trx id
ex.number1, --total supply/total intransit
ex.number2, --total demand/total onhand
ex.threshold, --lead time/itm min/itm max/threshold
ex.lead_time,
ex.item_min_qty,
ex.item_max_qty,
ex.date1, --based item actual dt
ex.date2, --pegged item actual dt
ex.date3, --today's dt (sysdate)
ex.date4,
ex.date5,
ex.exception_basis,
ex.order_creation_date1, --based item creation dt
ex.order_creation_date2, --pegged item creation date
ex.order_number,
ex.release_number,
ex.line_number,
ex.end_order_number,
ex.end_order_rel_number,
ex.end_order_line_number
from msc_x_exception_details ex
where ex.plan_id = -1
and ex.exception_group in (1,2,3,4,5,6,7,8,9,10) --exclude user define exceptions
and ex.version = 'CURRENT'; --indicate the current run of the netting engine
SELECT distinct pl.user_name
FROM msc_system_items msi,
msc_company_sites s,
msc_trading_partner_maps map,
msc_trading_partners part,
msc_planners pl
WHERE s.company_id = 1
AND s.company_site_id = p_company_site_id
AND map.map_type = 2
AND map.company_key = s.company_site_id
AND part.partner_id = map.tp_key
AND msi.organization_id = part.sr_tp_id
AND msi.sr_instance_id = part.sr_instance_id
AND msi.plan_id = -1
AND msi.inventory_item_id = p_item_id
AND pl.sr_instance_id = part.sr_instance_id
AND pl.planner_code = msi.planner_code
AND pl.organization_id = part.sr_tp_id;
SELECT distinct con.name
FROM msc_trading_partner_maps map,
msc_trading_partner_maps map1,
msc_company_sites site,
msc_partner_contacts con
WHERE map.map_type = 3
AND map.company_key = site.company_site_id
AND map.tp_key = con.partner_site_id
AND map1.company_key =p_oem_site_id
AND map1.map_type= 2
AND site.company_id = p_company_id
AND site.company_site_id = p_company_site_id
AND exists ( select 1 from msc_system_items msi, msc_trading_partners mt
where msi.plan_id = -1 --- Bug # 6242764
and msi.sr_instance_id = con.sr_instance_id
and msi.inventory_item_id = p_inventory_item_id
and mt.partner_id = map1.tp_key
and mt.sr_tp_id = msi.organization_id
and mt.partner_type=3); -- Bug #6242828
SELECT
distinct con.name
FROM
msc_trading_partner_maps map,
msc_company_sites site,
msc_partner_contacts con
WHERE map.map_type = 3
AND map.company_key = site.company_site_id
AND map.tp_key = con.partner_site_id
AND site.company_id = p_company_id
AND site.company_site_id = p_company_site_id; -- Bug #6242828
SELECT distinct con.name
FROM msc_trading_partner_maps map,
msc_trading_partner_maps map1,
msc_company_relationships rel,
msc_companies c,
msc_partner_contacts con
WHERE map.map_type = 1 --company
AND map.company_key = rel.relationship_id
AND rel.relationship_type = 2 --supplier
AND rel.object_id = c.company_id
AND map.tp_key = con.partner_id
AND map1.company_key =p_oem_id
AND map1.map_type= 2
AND con.partner_type = 1 --suplier
AND c.company_id = p_company_id
AND exists ( select 1 from msc_system_items msi,msc_trading_partners mt
where msi.plan_id = -1 --- Bug # 6242764
and msi.sr_instance_id = con.sr_instance_id
and msi.inventory_item_id = p_inventory_item_id
and mt.partner_id = map1.tp_key
and mt.sr_tp_id = msi.organization_id
and mt.partner_type=3)
UNION
SELECT distinct con.name
FROM msc_trading_partner_maps map,
msc_trading_partner_maps map1,
msc_company_relationships rel,
msc_companies c,
msc_partner_contacts con
WHERE map.map_type = 1 --company
AND map.company_key = rel.relationship_id
AND rel.relationship_type = 1 --customer
AND rel.object_id = c.company_id
AND con.partner_id = map.tp_key
AND map1.company_key =p_oem_id
AND map1.map_type= 2
AND con.partner_type = 2 --customer
AND c.company_id = p_company_id
AND exists ( select 1 from msc_system_items msi, msc_trading_partners mt
where msi.plan_id = -1 --- Bug # 6242764
and msi.sr_instance_id = con.sr_instance_id
and msi.inventory_item_id = p_inventory_item_id
and mt.partner_id = map1.tp_key
and mt.sr_tp_id = msi.organization_id
and mt.partner_type=3); -- Bug #6242828
SELECT distinct con.name
FROM msc_trading_partner_maps map,
msc_company_relationships rel,
msc_companies c,
msc_partner_contacts con
WHERE map.map_type = 1 --company
AND map.company_key = rel.relationship_id
AND rel.relationship_type = 2 --supplier
AND rel.object_id = c.company_id
AND map.tp_key =con.partner_id
AND con.partner_type = 1 --suplier
AND c.company_id = p_company_id
UNION
SELECT distinct con.name
FROM msc_trading_partner_maps map,
msc_company_relationships rel,
msc_companies c,
msc_partner_contacts con
WHERE map.map_type = 1 --company
AND map.company_key = rel.relationship_id
AND rel.relationship_type = 1 --customer
AND rel.object_id = c.company_id
AND con.partner_id = map.tp_key
AND con.partner_type = 2 --customer
AND c.company_id = p_company_id; -- Bug #6242828
l_inserted_record number;
l_inserted_record := 0;
SELECT substr(display_name,instr(display_name,',')+ 1) || ' ' ||
substr(display_name,1, instr(display_name,',') -1)
INTO l_real_name
FROM wf_users
WHERE name = l_user_performer;
l_inserted_record := l_inserted_record + 1;
SELECT 1 into l_exist from dual
WHERE exists (SELECT 1
FROM msc_trading_partner_maps map,
msc_trading_partner_maps map1,
msc_company_sites site,
msc_partner_contacts con
WHERE map.map_type = 3
AND map.company_key = site.company_site_id
AND map.tp_key = con.partner_site_id
AND map1.company_key =decode(t_supplier_id(j),1,t_supplier_site_id(j),t_customer_site_id(j))
AND map1.map_type= 2
AND site.company_id = t_company_id(j)
AND site.company_site_id = t_company_site_id(j)
AND exists ( select 1 from msc_system_items msi, msc_trading_partners mt
where msi.plan_id = -1 --- Bug # 6242764
and msi.sr_instance_id = con.sr_instance_id
and msi.inventory_item_id = t_item_id(j)
and mt.partner_id = map1.tp_key
and mt.sr_tp_id = msi.organization_id
and mt.partner_type=3));
SELECT 1 into l_exist
FROM dual
WHERE exists (SELECT 1
FROM msc_trading_partner_maps map,
msc_company_sites site,
msc_partner_contacts con
WHERE map.map_type = 3
AND map.company_key = site.company_site_id
AND map.tp_key = con.partner_site_id
AND site.company_id = t_company_id(j)
AND site.company_site_id = t_company_site_id(j)); -- Bug #6242828
select decode(t_supplier_id(j),1,t_supplier_site_id(j),t_customer_site_id(j))
into l_oem_site_id
from dual;
SELECT substr(display_name,instr(display_name,',')+ 1) || ' ' ||
substr(display_name,1, instr(display_name,',') -1)
INTO l_real_name
FROM wf_users
WHERE name = l_user_performer;
l_inserted_record := l_inserted_record + 1;
SELECT 0 into l_independent
FROM dual
WHERE exists (SELECT 1
FROM msc_trading_partner_maps map,
msc_trading_partner_maps map1,
msc_company_relationships rel,
msc_companies c,
msc_partner_contacts con
WHERE map.map_type = 1 --company
AND map.company_key = rel.relationship_id
AND (rel.relationship_type = 2 --supplier
OR rel.relationship_type = 1) --customer
AND rel.object_id = c.company_id
AND map.tp_key = con.partner_id
AND map1.company_key =decode(t_supplier_id(j),1,t_supplier_id(j),t_customer_id(j))
AND map1.map_type= 2
AND (con.partner_type = 1 --suplier
OR con.partner_type = 2) --customer
AND c.company_id = t_company_id(j)
AND exists ( SELECT 1
FROM msc_system_items msi,
msc_trading_partners mt
WHERE msi.plan_id = -1 --- Bug # 6242764
AND msi.sr_instance_id = con.sr_instance_id
AND msi.inventory_item_id = t_item_id(j)
AND mt.partner_id = map1.tp_key
AND mt.sr_tp_id = msi.organization_id
AND mt.partner_type=3) ); -- Bug #6242828
SELECT 1 into l_independent
FROM dual
WHERE EXISTS (SELECT 1
FROM msc_trading_partner_maps map,
msc_company_relationships rel,
msc_companies c,
msc_partner_contacts con
WHERE map.map_type = 1 --company
AND map.company_key = rel.relationship_id
AND (rel.relationship_type = 2 --supplier
OR rel.relationship_type = 1) --customer
AND rel.object_id = c.company_id
AND map.tp_key =con.partner_id
AND c.company_id = t_company_id(j));
select decode(t_supplier_id(j),1,t_supplier_site_id(j),t_customer_site_id(j))
into l_oem_id
from dual;
SELECT substr(display_name,instr(display_name,',')+ 1) || ' ' ||
substr(display_name,1, instr(display_name,',') -1)
INTO l_real_name
FROM wf_users
WHERE name = l_user_performer;
l_inserted_record := l_inserted_record + 1;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Total WF notifications inserted: ' || l_inserted_record);
update msc_x_exception_details
set version = null, last_update_login = null
where plan_id = -1
and version = 'CURRENT'
and exception_group in (1,2,3,4,5,6,7,8,9,10);
SELECT distinct sd.publisher_id,
sd.publisher_name,
sd.publisher_site_id,
sd.publisher_site_name,
sd.supplier_id,
sd.supplier_name,
sd.supplier_site_id,
sd.supplier_site_name,
sd.customer_id,
sd.customer_name,
sd.customer_site_id,
sd.customer_site_name
FROM msc_sup_dem_entries sd
WHERE sd.publisher_order_type = p_publish_program_type and
sd.plan_id = -1 and
sd.publisher_id = 1 and
exists (select cs.company_site_id
from msc_plan_organizations o,
msc_company_sites cs,
msc_trading_partner_maps m,
msc_trading_partners p
where o.plan_id = p_plan_id
AND O.ORGANIZATION_ID = NVL(p_org_id , O.ORGANIZATION_ID)
AND O.SR_INSTANCE_ID = NVL(p_sr_instance_id , O.SR_INSTANCE_ID)
AND P.SR_TP_ID = O.ORGANIZATION_ID
AND P.SR_INSTANCE_ID = O.SR_INSTANCE_ID and
p.partner_type = 3 and
m.tp_key = p.partner_id and
m.map_type = 2 and
cs.company_site_id = m.company_key and
cs.company_id = 1
and sd.publisher_site_id =cs.company_site_id and rownum=1) and
exists (select c.company_id
from msc_companies c,
msc_trading_partner_maps m,
msc_company_relationships r
where m.tp_key = nvl(p_supplier_id, m.tp_key) and
m.map_type = 1 and
r.relationship_id = m.company_key and
r.subject_id = 1 and
r.relationship_type = 2 and
c.company_id = r.object_id and
sd.supplier_id =c.company_id and rownum=1) and
exists (select s.company_site_id
from msc_company_sites s,
msc_trading_partner_maps m
where m.tp_key = nvl(p_supplier_site_id, m.tp_key) and
m.map_type = 3 and
s.company_site_id = m.company_key and
s.company_id = sd.supplier_id and
sd.supplier_site_id=s.company_site_id and rownum=1) and
exists (select nvl(i.base_item_id,i.inventory_item_id)
from msc_system_items i,
msc_plan_organizations o
where o.plan_id = p_plan_id and
i.plan_id = o.plan_id
AND O.ORGANIZATION_ID = NVL(p_org_id , O.ORGANIZATION_ID)
AND O.SR_INSTANCE_ID =NVL(p_sr_instance_id , O.SR_INSTANCE_ID)
AND I.ORGANIZATION_ID = O.ORGANIZATION_ID
AND I.SR_INSTANCE_ID = O.SR_INSTANCE_ID and
NVL(i.planner_code,'-99') = NVL(p_planner_code,
NVL(i.planner_code,'-99')) and
NVL(i.abc_class_name,'-99') = NVL(p_abc_class,
NVL(i.abc_class_name,'-99')) and
i.inventory_item_id = nvl(p_item_id, i.inventory_item_id)
and NVL(sd.base_item_id, sd.inventory_item_id) = nvl(i.base_item_id,i.inventory_item_id)
and rownum=1) and
NVL(sd.planner_code,'-99') = nvl(p_planner_code, NVL(sd.planner_code, '-99')) and
NVL(sd.planning_group,'-99') = nvl(p_planning_gp, NVL(sd.planning_group, '-99')) and
NVL(sd.project_number,'-99') = nvl(p_project_id, NVL(sd.project_number, '-99')) and
NVL(sd.task_number, '-99') = nvl(p_task_id, NVL(sd.task_number, '-99')) and
sd.designator = p_designator and
sd.version = p_version and
key_date between nvl(p_horizon_start, sysdate - 36500) and
nvl(p_horizon_end, sysdate + 36500);
SELECT distinct sd.inventory_item_id,
sd.item_name,
sd.item_description
FROM msc_sup_dem_entries sd
WHERE sd.publisher_order_type = 2 and
sd.plan_id = -1 and
sd.publisher_id = p_company_id and
sd.publisher_site_id = p_company_site_id and
sd.supplier_id = p_tp_company_id and
sd.supplier_site_id = p_tp_company_site_id and
sd.designator = p_designator and
sd.version = p_version and
sd.inventory_item_id = nvl(p_item_id, sd.inventory_item_id) and
key_date between nvl(p_horizon_start, sysdate - 36500) and
nvl(p_horizon_end, sysdate + 36500);
SELECT distinct sd.publisher_id,
sd.publisher_name,
sd.publisher_site_id,
sd.publisher_site_name,
sd.supplier_id,
sd.supplier_name,
sd.supplier_site_id,
sd.supplier_site_name,
sd.customer_id,
sd.customer_name,
sd.customer_site_id,
sd.customer_site_name
FROM msc_sup_dem_entries sd
WHERE sd.publisher_order_type = p_publish_program_type and
sd.plan_id = -1 and
sd.publisher_id = 1 and
sd.publisher_site_id IN (select cs.company_site_id
from msc_plan_organizations o,
msc_company_sites cs,
msc_trading_partner_maps m,
msc_trading_partners p
where o.plan_id = p_plan_id and
p.sr_tp_id = nvl(p_org_id, o.organization_id) and
p.sr_instance_id = nvl(p_sr_instance_id,
o.sr_instance_id) and
p.partner_type = 3 and
m.tp_key = p.partner_id and
m.map_type = 2 and
cs.company_site_id = m.company_key and
cs.company_id = 1) and
sd.customer_id IN (select distinct c.company_id
from msc_companies c,
msc_trading_partner_maps m,
msc_company_relationships r
where m.tp_key = nvl(p_customer_id, m.tp_key) and
m.map_type = 1 and
r.relationship_id = m.company_key and
r.subject_id = 1 and
r.relationship_type = 1 and
c.company_id = r.object_id) and
sd.customer_site_id IN (select s.company_site_id
from msc_company_sites s,
msc_trading_partner_maps m
where m.tp_key = nvl(p_customer_site_id, m.tp_key) and
m.map_type = 3 and
s.company_site_id = m.company_key and
s.company_id = sd.customer_id) and
NVL(sd.base_item_id, sd.inventory_item_id) IN (select nvl(i.base_item_id,i.inventory_item_id)
from msc_system_items i,
msc_plan_organizations o
where o.plan_id = p_plan_id and
i.plan_id = o.plan_id and
i.organization_id = nvl(p_org_id,
o.organization_id) and
i.sr_instance_id = nvl(p_sr_instance_id,
o.sr_instance_id) and
NVL(i.planner_code,'-99') = NVL(p_planner_code,
NVL(i.planner_code,'-99')) and
NVL(i.abc_class_name,'-99') = NVL(p_abc_class,
NVL(i.abc_class_name,'-99')) and
i.inventory_item_id = nvl(p_item_id, i.inventory_item_id)) and
NVL(sd.planner_code,'-99') = nvl(p_planner_code, NVL(sd.planner_code, '-99')) and
NVL(sd.planning_group,'-99') = nvl(p_planning_gp, NVL(sd.planning_group, '-99')) and
NVL(sd.project_number,'-99') = nvl(p_project_id, NVL(sd.project_number, '-99')) and
NVL(sd.task_number, '-99') = nvl(p_task_id, NVL(sd.task_number, '-99')) and
sd.designator = p_designator and
sd.version = p_version and
key_date between nvl(p_horizon_start, sysdate - 36500) and
nvl(p_horizon_end, sysdate + 36500);
SELECT distinct sd.inventory_item_id,
sd.item_name,
sd.item_description
FROM msc_sup_dem_entries sd
WHERE sd.publisher_order_type = 3 and
sd.plan_id = -1 and
sd.publisher_id = p_company_id and
sd.publisher_site_id = p_company_site_id and
sd.customer_id = p_tp_company_id and
sd.customer_site_id = p_tp_company_site_id and
sd.designator = p_designator and
sd.version = p_version and
sd.inventory_item_id = nvl(p_item_id, sd.inventory_item_id) and
key_date between nvl(p_horizon_start, sysdate - 36500) and
nvl(p_horizon_end, sysdate + 36500);
SELECT distinct con.name
FROM msc_trading_partner_maps map,
msc_company_sites site,
msc_partner_contacts con
WHERE map.map_type = 3
AND map.company_key = site.company_site_id
AND map.tp_key = con.partner_site_id
AND site.company_id = p_company_id
AND site.company_site_id = p_company_site_id;
SELECT distinct con.name
FROM msc_trading_partner_maps map,
msc_company_sites site,
msc_partner_contacts con,
msc_trading_partner_sites tps
WHERE map.map_type = 3
AND map.company_key = site.company_site_id
AND map.tp_key = tps.partner_site_id
AND tps.partner_site_id = con.partner_site_id
AND tps.sr_instance_id = con.sr_instance_id
AND site.company_id = p_company_id
AND site.company_site_id = p_company_site_id;
select msc_cl_refresh_s.nextval into l_next_number from dual;
SELECT substr(display_name,instr(display_name,',')+ 1) || ' ' ||
substr(display_name,1, instr(display_name,',') -1)
INTO l_real_name
FROM wf_users
WHERE name = l_user_performer;
SELECT substr(display_name,instr(display_name,',')+ 1) || ' ' ||
substr(display_name,1, instr(display_name,',') -1)
INTO l_real_name
FROM wf_users
WHERE name = l_user_performer;