The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT party_name
INTO l_party_name
FROM hz_parties
WHERE party_id = p_party_id;
SELECT category_name
INTO l_category_code
FROM msc_item_categories mic,
msc_trading_partners tp,
msc_trading_partner_maps map
WHERE map.company_key = l_org_id
and map.map_type = 2
and map.tp_key = tp.partner_id
and tp.sr_tp_id = mic.organization_id
and mic.sr_instance_id = tp.sr_instance_id
and mic.inventory_item_id = p_inventory_item_id
and mic.category_set_id = FND_PROFILE.VALUE('MSCX_CP_HZ_CATEGORY_SET');
SELECT buyer_name into l_buyer_code
FROM msc_system_items msi,
msc_trading_partners tp,
msc_trading_partner_maps map
WHERE map.company_key = l_org_id
and map.map_type = 2
and map.tp_key = tp.partner_id
and tp.sr_tp_id = msi.organization_id
and msi.sr_instance_id = tp.sr_instance_id
and msi.inventory_item_id = p_inventory_item_id
and msi.plan_id = -1;
/* select max(tpx.xref_ext_value)
into l_xref_party_name
from ect_xref_dtl tpx
where tpx.xref_int_value = p_xref_party_id
and tpx.party_id = p_party_id
and tpx.direction = 'IN'
and tpx.xref_category_id = 9 ;
select uom_class
into l_uom_class
from msc_units_of_measure
where uom_code = p_uom_code
and rownum = 1;
select uom_class
into l_dest_uom_class
from msc_units_of_measure
where uom_code = p_dest_uom_code
and rownum = 1;
select muc1.conversion_rate/muc2.conversion_rate
INTO
p_conv_rate
FROM
msc_uom_conversions muc1,
msc_uom_conversions muc2
where muc1.inventory_item_id = 0
and muc2.inventory_item_id = 0
and muc1.uom_class = muc2.uom_class
and muc1.uom_class = l_uom_class
and muc1.uom_code = p_uom_code
and muc2.uom_code = p_dest_uom_code
and rownum = 1;
select muc.conversion_rate
INTO
p_conv_rate
FROM
msc_uom_conversions_view muc
where muc.inventory_item_id = p_inventory_item_id
and muc.primary_uom_code = p_uom_code
and muc.uom_code = p_dest_uom_code
and rownum = 1;
select muc.conversion_rate
INTO
p_conv_rate
FROM
msc_uom_conversions_view muc
where muc.inventory_item_id = p_inventory_item_id
and muc.primary_uom_code = p_dest_uom_code
and muc.uom_code = p_uom_code
and rownum = 1;
FUNCTION UPDATE_SHIP_RCPT_DATES (
p_customer_id IN NUMBER,
p_customer_site_id IN NUMBER,
p_supplier_id IN NUMBER,
p_supplier_site_id IN NUMBER,
p_order_type IN NUMBER,
p_item_id IN NUMBER,
p_ship_date IN DATE,
p_rcpt_date IN DATE) RETURN DATE IS
l_org_id NUMBER NULL;
SELECT tp_key
INTO l_tp_org_partner_id
FROM msc_trading_partner_maps map
WHERE map.map_type = 2
and map.company_key = l_org_id;
SELECT sr_tp_id,
sr_instance_id
INTO l_sr_tp_id,
l_sr_instance_id
FROM msc_trading_partners
WHERE partner_id = l_tp_org_partner_id;
SELECT tpl.sr_tp_id INTO
l_tp_customer_id
FROM
msc_tp_id_lid tpl,
msc_trading_partner_maps map,
msc_company_relationships rels
WHERE tpl.tp_id = map.tp_key
and tpl.partner_type = 2
and tpl.sr_company_id = -1
and tpl.sr_instance_id = l_sr_instance_id
and map.map_type = 1
and map.company_key = rels.relationship_id
and rels.object_id = l_customer_id
and rels.subject_id = l_company_id
and rels.relationship_type = 1;
SELECT tps.sr_tp_site_id into
l_tp_customer_site_id
FROM
msc_tp_site_id_lid tps,
msc_trading_partner_maps map
WHERE tps.sr_company_id = -1
and tps.tp_site_id = map.tp_key
and tps.sr_instance_id = l_sr_instance_id
and tps.partner_type = 2
and map.map_type = 3
and map.company_key = l_customer_site_id;
SELECT tps.sr_tp_site_id into
l_tp_customer_site_id
FROM
msc_tp_site_id_lid tps,
msc_trading_partner_sites tp_sites,
msc_trading_partner_maps map
WHERE tps.sr_company_id = -1
and tps.tp_site_id = tp_sites.partner_site_id
and tps.sr_instance_id = l_sr_instance_id
and tps.partner_type = 2
and tp_sites.partner_site_id = map.tp_key
and tp_sites.tp_site_code = 'SHIP_TO'
and map.map_type = 3
and map.company_key = l_customer_site_id;
select mrp_atp_schedule_temp_s.nextval
into l_session_id
from dual;
SELECT tp_key
INTO l_tp_org_partner_id
FROM msc_trading_partner_maps map
WHERE map.map_type = 2
and map.company_key = l_org_id;
SELECT sr_tp_id,
sr_instance_id
INTO l_sr_tp_id,
l_sr_instance_id
FROM msc_trading_partners
WHERE partner_id = l_tp_org_partner_id;
SELECT map.tp_key INTO
l_tp_supplier_id
FROM
msc_trading_partner_maps map,
msc_company_relationships rels
WHERE map.map_type = 1
and map.company_key = rels.relationship_id
and rels.object_id = l_supplier_id
and rels.subject_id = l_company_id
and rels.relationship_type = 2;
SELECT map.tp_key INTO
l_tp_supplier_site_id
FROM
msc_trading_partner_maps map
WHERE map.map_type = 3
and map.company_key = l_supplier_site_id;
select mrp_atp_schedule_temp_s.nextval
into l_session_id
from dual;
END UPDATE_SHIP_RCPT_DATES;
select to_number(to_char(sysdate, 'j'))
INTO jul_ship_date
FROM dual;
** It is built on top of update_ship_rcpt_dates fn. It passes
** in a ship date and gets back a recipt date. The difference
** between the 2 is returned as lead time.
*/
BEGIN
rcpt_date := UPDATE_SHIP_RCPT_DATES (
p_customer_id,
p_customer_site_id,
p_publisher_id,
p_publisher_site_id,
14, /* Hard Coded to Sales Order */
NULL,
ship_date,
NULL);
select to_number(to_char(rcpt_date, 'j'))
INTO jul_rcpt_date FROM dual;
select meaning
into l_order_type_desc
from mfg_lookups
where lookup_type = p_lookup_type
and lookup_code = p_order_type_code;
SELECT tp.sr_tp_id,tp.sr_instance_id
INTO aps_partner_id, aps_sr_instance_id
FROM msc_trading_partner_maps map,
msc_trading_partners tp
WHERE map.map_type = G_ORGANIZATION_MAPPING
AND map.company_key = p_sce_company_site_id
AND map.tp_key = tp.partner_id;
SELECT map.tp_key
INTO aps_partner_id
FROM msc_trading_partner_maps map,
msc_company_relationships cr
WHERE map.map_type = G_COMPANY_MAPPING
AND cr.object_id = p_sce_company_id
AND map.company_key = cr.relationship_id
AND cr.relationship_type = p_relationship_type
AND cr.subject_id = OEM_COMPANY_ID;
SELECT map.tp_key
INTO aps_partner_site_id
FROM msc_trading_partner_maps map
WHERE map.map_type = G_COMPANY_SITE_MAPPING
AND map.company_key = p_sce_company_site_id;
SELECT tp_sites.partner_site_id
INTO aps_partner_site_id
FROM msc_trading_partner_sites tp_sites,
msc_trading_partner_maps map
WHERE tp_sites.partner_site_id = map.tp_key
and tp_sites.tp_site_code = 'SHIP_TO'
and tp_sites.partner_type = 2
and map.map_type = G_COMPANY_SITE_MAPPING
and map.company_key = p_sce_company_site_id;
select nvl(ca1.CALENDAR_CODE, ca.CALENDAR_CODE)
into lv_calendar_code
from msc_calendar_assignments ca,
msc_calendar_assignments ca1
where ca.sr_instance_id = aps_sr_instance_id
and ca.CALENDAR_TYPE = 'RECEIVING'
and ca.partner_type = 2
and ca.partner_id = aps_cust_partner_id
and ca.ORGANIZATION_ID is null
and ca.ASSOCIATION_TYPE = G_CUSTOMER
and ca1.sr_instance_id(+) = ca.sr_instance_id
and ca1.CALENDAR_TYPE(+) = ca.CALENDAR_TYPE
and ca1.partner_type(+) = ca.partner_type
and ca1.ORGANIZATION_ID is null
and ca1.ASSOCIATION_TYPE(+) = G_CUSTOMER_SITE
and ca1.partner_id(+) = ca.partner_id
and ca1.partner_site_id(+) = aps_cust_partner_site_id;
select nvl(ca.CALENDAR_CODE, tp.calendar_code)
into lv_calendar_code
from msc_trading_partners tp,
msc_calendar_assignments ca
where tp.sr_instance_id = aps_sr_instance_id
and tp.sr_tp_id = aps_org_partner_id
and tp.partner_type = 3
and ca.sr_instance_id(+) = tp.sr_instance_id
and ca.ORGANIZATION_ID(+) = tp.sr_tp_id
and ca.partner_type(+) = tp.partner_type
and ca.CALENDAR_TYPE(+) = 'SHIPPING'
and ca.ASSOCIATION_TYPE(+) = G_ORGANIZATION;
select nvl(ca.CALENDAR_CODE, tp.calendar_code)
into lv_calendar_code
from msc_calendar_assignments ca,
msc_trading_partners tp
where tp.sr_instance_id = aps_sr_instance_id
and tp.sr_tp_id = aps_org_partner_id
and tp.partner_type = 3
and ca.sr_instance_id(+) = tp.sr_instance_id
and ca.ORGANIZATION_ID(+) = tp.sr_tp_id
and ca.partner_type(+) = tp.partner_type
and ca.CALENDAR_TYPE(+) = 'RECEIVING'
and ca.ASSOCIATION_TYPE(+) = G_ORGANIZATION;
select nvl(ca1.CALENDAR_CODE, ca.CALENDAR_CODE),
nvl(ca1.sr_instance_id,ca.sr_instance_id)
into lv_calendar_code,
aps_sr_instance_id
from msc_calendar_assignments ca,
msc_calendar_assignments ca1
where ca.CALENDAR_TYPE = 'RECEIVING'
and ca.partner_type = 2
and ca.partner_id = aps_cust_partner_id
and ca.ORGANIZATION_ID is null
and ca.ASSOCIATION_TYPE = G_CUSTOMER
and ca1.sr_instance_id(+) = ca.sr_instance_id
and ca1.CALENDAR_TYPE(+) = ca.CALENDAR_TYPE
and ca1.partner_type(+) = ca.partner_type
and ca1.ORGANIZATION_ID is null
and ca1.ASSOCIATION_TYPE(+) = G_CUSTOMER_SITE
and ca1.partner_id(+) = ca.partner_id
and ca1.partner_site_id(+) = aps_cust_partner_site_id;
select nvl(ca1.CALENDAR_CODE, ca.CALENDAR_CODE)
into lv_calendar_code
from msc_calendar_assignments ca,
msc_calendar_assignments ca1
where ca.sr_instance_id = aps_sr_instance_id
and ca.CALENDAR_TYPE = 'RECEIVING'
and ca.partner_type = 2
and ca.partner_id = aps_cust_partner_id
and ca.ORGANIZATION_ID is null
and ca.ASSOCIATION_TYPE = G_CUSTOMER
and ca1.sr_instance_id(+) = ca.sr_instance_id
and ca1.CALENDAR_TYPE(+) = ca.CALENDAR_TYPE
and ca1.partner_type(+) = ca.partner_type
and ca1.ORGANIZATION_ID is null
and ca1.ASSOCIATION_TYPE(+) = G_CUSTOMER_SITE
and ca1.partner_id(+) = ca.partner_id
and ca1.partner_site_id(+) = aps_cust_partner_site_id;
select nvl(ca.CALENDAR_CODE, tp.calendar_code)
into lv_calendar_code
from msc_trading_partners tp,
msc_calendar_assignments ca
where tp.sr_instance_id = aps_sr_instance_id
and tp.sr_tp_id = aps_org_partner_id
and tp.partner_type = 3
and ca.sr_instance_id(+) = tp.sr_instance_id
and ca.ORGANIZATION_ID(+) = tp.sr_tp_id
and ca.partner_type(+) = tp.partner_type
and ca.CALENDAR_TYPE(+) = 'SHIPPING'
and ca.ASSOCIATION_TYPE(+) = G_ORGANIZATION;
select sr_instance_id
into p_sr_instance_id
from msc_calendar_dates
where calendar_code = p_calendar_code
and rownum = 1;
SELECT decode(upper(mtps.shipping_control), 'BUYER', 2,1)
from msc_trading_partner_maps mtpm,
msc_trading_partner_sites mtps
where mtpm.company_key = a_site_id
and mtpm.map_type = G_COMPANY_SITE
and mtpm.tp_key = mtps.partner_site_id
and mtps.partner_type = a_partner_type;
SELECT decode(upper(mtps.shipping_control), 'BUYER', 2,1)
from msc_trading_partner_maps mtpm,
msc_trading_partner_sites mtps
where mtpm.company_key = a_site_id
and mtpm.map_type = G_COMPANY_SITE
and mtpm.tp_key = mtps.partner_site_id
and mtps.partner_type = a_partner_type
AND mtps.tp_site_code = 'SHIP_TO'
;
select mcs.company_id, mcs.company_site_id
INTO l_customer_id, l_customer_site_id
from msc_companies mc,
msc_company_sites mcs
where mc.company_id = mcs.company_id
and upper(mc.company_name) = upper(p_customer_name)
and upper(mcs.company_site_name) = upper(p_customer_site_name);
select mcs.company_id, mcs.company_site_id
INTO l_supplier_id, l_supplier_site_id
from msc_companies mc,
msc_company_sites mcs
where mc.company_id = mcs.company_id
and upper(mc.company_name) = upper(p_supplier_name)
and upper(mcs.company_site_name) = upper(p_supplier_site_name);
SELECT distinct buyer_name into l_buyer_code
FROM msc_system_items msi
WHERE
msi.organization_id=p_organization_id
and msi.sr_instance_id = p_sr_instance_id
and msi.inventory_item_id = p_inventory_item_id
and msi.plan_id = -1;