[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select category_id
from mtl_item_categories
where inventory_item_id = p_item_id
and organization_id = p_org_id;
SELECT sum(decode(ASR.allow_action_flag, 'Y', 1, -100)) INTO x_asl_status
FROM PO_APPROVED_SUPPLIER_LIS_VAL_V ASL, PO_ASL_STATUS_RULES ASR
WHERE ASL.using_organization_id IN (nvl(x_ship_to_org,-1), -1)
AND ASL.vendor_id = x_vendor_id
AND nvl(ASL.vendor_site_id, nvl(x_vendor_site_id,-1)) = nvl(x_vendor_site_id,-1)
AND ( (ASL.item_id = x_item_id) OR
(ASL.category_id = x_category_id) OR
(ASL.category_id in (SELECT MIC.category_id
FROM MTL_ITEM_CATEGORIES MIC
WHERE MIC.inventory_item_id = x_item_id
AND MIC.organization_id = x_ship_to_org)))
AND ASL.asl_status_id = ASR.status_id
AND ASR.business_rule = x_action;
SELECT sum(decode(ASR.allow_action_flag, 'Y', 1, -100)) INTO x_asl_status
FROM PO_APPROVED_SUPPLIER_LIS_VAL_V ASL, PO_ASL_STATUS_RULES_V ASR
WHERE ASL.using_organization_id IN (nvl(x_ship_to_org,-1), -1)
AND ASL.vendor_id = x_vendor_id
AND nvl(ASL.vendor_site_id, nvl(x_vendor_site_id,-1)) = nvl(x_vendor_site_id,-1)
AND ASL.item_id = x_item_id
AND ASL.asl_status_id = ASR.status_id
AND ASR.business_rule = x_action;
SELECT sum(decode(ASR.allow_action_flag, 'Y', 1, -100)) INTO x_asl_status
FROM PO_APPROVED_SUPPLIER_LIS_VAL_V ASL, PO_ASL_STATUS_RULES_V ASR
WHERE ASL.using_organization_id IN (nvl(x_ship_to_org,-1), -1)
AND ASL.vendor_id = x_vendor_id
AND nvl(ASL.vendor_site_id, nvl(x_vendor_site_id,-1)) = nvl(x_vendor_site_id,-1)
AND ASL.category_id = x_category_id
AND ASL.asl_status_id = ASR.status_id
AND ASR.business_rule = x_action;
SELECT sum(decode(ASR.allow_action_flag, 'Y', 1, -100)) INTO x_asl_status
FROM PO_APPROVED_SUPPLIER_LIS_VAL_V ASL, PO_ASL_STATUS_RULES_V ASR
WHERE ASL.using_organization_id IN (nvl(x_ship_to_org,-1), -1)
AND ASL.vendor_id = x_vendor_id
AND nvl(ASL.vendor_site_id, nvl(x_vendor_site_id,-1)) = nvl(x_vendor_site_id,-1)
AND ASL.category_id in (SELECT MIC.category_id
FROM MTL_ITEM_CATEGORIES MIC
WHERE MIC.inventory_item_id = x_item_id
AND MIC.organization_id = x_ship_to_org)
AND ASL.asl_status_id = ASR.status_id
AND ASR.business_rule = x_action;
SELECT sum(decode(ASR.allow_action_flag, 'Y', 1, -100)) INTO x_asl_s
FROM PO_APPROVED_SUPPLIER_LIS_VAL_V ASL, PO_ASL_STATUS_RULES_V ASR
WHERE ASL.using_organization_id IN (nvl(x_ship_to_org,-1), -1)
AND ASL.vendor_id = x_vendor_id
AND nvl(ASL.vendor_site_id, nvl(x_vendor_site_id,-1)) = nvl(x_vendor_site_id,-1)
AND ASL.category_id = v_category.category_id
AND ASL.asl_status_id = ASR.status_id
AND ASR.business_rule = x_action;
procedure update_vendor_status(x_organization_id in number,
x_vendor_id in number,
x_status in varchar2,
x_vendor_site_id in number default null,
x_item_id in number default null,
x_global_asl_update in varchar2 ,
x_org_id in number default null,
x_return_code in out NOCOPY varchar2) is
x_status_id number;
l_api_name CONSTANT VARCHAR2(30) := 'update_vendor_status';
select status_id
into x_status_id
from po_asl_statuses
where status = x_status;
if NVL(x_global_asl_update,'N') = 'N' then
l_progress := '010';
update po_approved_supplier_list pasl
set pasl.asl_status_id = x_status_id
where pasl.using_organization_id = x_organization_id
and pasl.vendor_id = x_vendor_id
and pasl.vendor_site_id = NVL(x_vendor_site_id,pasl.vendor_site_id)
and pasl.item_id = NVL(x_item_id,pasl.item_id)
and exists (select null from po_vendor_sites_all pvsa
where NVL(pvsa.org_id, -99) =
NVL(x_org_id, NVL(pvsa.org_id, -99))
and pvsa.vendor_site_id = pasl.vendor_site_id)
RETURNING PASL.asl_id --
BULK COLLECT INTO l_asl_id_tbl; --
update po_approved_supplier_list pasl
set pasl.asl_status_id = x_status_id
where pasl.using_organization_id in (x_organization_id,-1)
and pasl.vendor_id = x_vendor_id
and pasl.vendor_site_id = NVL(x_vendor_site_id,pasl.vendor_site_id)
and pasl.item_id = NVL(x_item_id,pasl.item_id)
and exists (select null from po_vendor_sites_all pvsa
where NVL(pvsa.org_id, -99) =
NVL(x_org_id, NVL(pvsa.org_id, -99))
and pvsa.vendor_site_id = pasl.vendor_site_id)
RETURNING PASL.asl_id --
BULK COLLECT INTO l_asl_id_tbl; --
p_action => PO_ASL_SV.G_EVENT_UPDATE,
p_calling_from => 'PO_ASL_SV.udpate_vendor_status',
p_ackn_note => NULL,
p_autonomous_commit => FND_API.G_FALSE
);
end update_vendor_status;
SELECT count(1)
INTO dummy
FROM chv_cum_periods
WHERE organization_id = x_current_form_org
AND sysdate between cum_period_start_date and
cum_period_end_date;
SELECT past.status_id,
past.status,
plc.lookup_code,
plc.displayed_field,
fsp.inventory_organization_id
INTO x_default_status_id,
x_default_status,
x_default_business_code,
x_default_business,
x_po_item_master_org_id
FROM po_asl_statuses past,
po_lookup_codes plc,
financials_system_parameters fsp
WHERE past.asl_default_flag = 'Y'
AND plc.lookup_type = 'ASL_VENDOR_BUSINESS_TYPE'
AND plc.lookup_code = 'DIRECT';
SELECT count(1)
INTO x_dummy_count
FROM po_approved_supplier_list pasl
WHERE pasl.manufacturer_id = x_manufacturer_id
AND pasl.using_organization_id = x_using_organization_id
AND pasl.item_id = x_item_id;
SELECT count(1)
INTO x_dummy_count
FROM po_approved_supplier_list pasl
WHERE pasl.manufacturer_id = x_manufacturer_id
AND pasl.using_organization_id = x_using_organization_id
AND pasl.category_id = x_category_id;
SELECT count(1)
INTO x_dummy_count
FROM po_approved_supplier_list pasl
WHERE pasl.vendor_id = x_vendor_id
AND ((pasl.vendor_site_id is null AND x_vendor_site_id is null) OR
(pasl.vendor_site_id = x_vendor_site_id))
AND pasl.using_organization_id = x_using_organization_id
AND pasl.item_id = x_item_id;
SELECT count(1)
INTO x_dummy_count
FROM po_approved_supplier_list pasl
WHERE pasl.vendor_id = x_vendor_id
AND ((pasl.vendor_site_id is null AND x_vendor_site_id is null) OR
(pasl.vendor_site_id = x_vendor_site_id))
AND pasl.using_organization_id = x_using_organization_id
AND pasl.category_id = x_category_id;
SELECT count(1)
INTO x_dummy_count_local_attr
FROM po_approved_supplier_list pasl,po_asl_attributes paa
WHERE pasl.vendor_id = x_vendor_id
AND ((pasl.vendor_site_id is null AND x_vendor_site_id is null) OR
(pasl.vendor_site_id = x_vendor_site_id))
AND pasl.using_organization_id = -1
AND paa.using_organization_id = x_using_organization_id
AND pasl.asl_id = paa.asl_id
AND ((pasl.item_id is null AND x_item_id is null) OR
(pasl.item_id = x_item_id))
AND ((pasl.category_id is null AND x_category_id is null) OR
(pasl.category_id = x_category_id));
IF (p_action = G_EVENT_INSERT) THEN
l_progress := '010';
ELSIF (p_action = G_EVENT_UPDATE) THEN
l_progress := '020';
l_event.event_name := 'oracle.apps.po.asl.update';
SELECT PASL.using_organization_id,
MP.organization_code,
PASL.item_id,
DECODE (PASL.vendor_business_type,
'MANUFACTURER', MM.manufacturer_name,
PV.vendor_name),
PVS.vendor_site_code,
MC.concatenated_segments,
MSI.concatenated_segments
INTO l_using_org_id,
l_organization,
l_item_id,
l_vendor_name,
l_vendor_site_code,
l_category_name,
l_item_name
FROM po_approved_supplier_list PASL,
mtl_parameters MP,
mtl_manufacturers MM,
po_vendors PV,
po_vendor_sites_all PVS,
mtl_system_items_kfv MSI,
mtl_categories_kfv MC
WHERE PASL.asl_id = p_asl_id
AND PASL.using_organization_id = MP.organization_id (+)
AND PASL.manufacturer_id = MM.manufacturer_id (+)
AND PASL.vendor_id = PV.vendor_id (+)
AND PASL.vendor_site_id = PVS.vendor_site_id (+)
AND PASL.item_id = MSI.inventory_item_id (+)
AND PASL.owning_organization_id = NVL(MSI.organization_id,
PASL.owning_organization_id)
AND PASL.category_id = MC.category_id (+);
g_asl_activities.delete;