[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT stock_enabled_flag, restrict_subinventories_code
INTO var_stk,
var_restrict
FROM mtl_system_items
WHERE organization_id = arg_org_id
AND inventory_item_id = arg_item_id;
SELECT 'Y'
into var_stk
FROM mtl_item_sub_inventories
WHERE organization_id = arg_org_id
AND inventory_item_id = arg_item_id
AND secondary_inventory = arg_sub_inv;
SELECT misl.vendor_id,
misl.vendor_site_id,
misl.source_organization_id,
misl.allocation_percent,
NVL(misl.rank, 9999),
misl.sourcing_rule_id
FROM mrp_item_sourcing_levels_v misl
WHERE misl.source_type in (1,3)
and misl.inventory_item_id = arg_item_id
and misl.organization_id = arg_dest_organization_id
and misl.assignment_set_id = var_set_id
and arg_autosource_date between misl.effective_date and
NVL(disable_date, to_date(2634525, 'J'))
and PO_ASL_SV.check_asl_action('2_SOURCING',
misl.vendor_id, misl.vendor_site_id, arg_item_id, -1
, arg_dest_organization_id ) <> 0
AND nvl(nvl(p_source_organization_id,
misl.source_organization_id), -23453)
= nvl(misl.source_organization_id, -23453)
ORDER BY misl.sourcing_level ASC,
allocation_percent DESC, NVL(misl.rank, 9999) ASC;
select misi.source_organization_id,
misi.source_subinventory
into var_source_org,
var_source_sub
from mtl_item_sub_inventories misi,
org_organization_definitions ood,
financials_system_parameters fsp
where misi.organization_id = arg_dest_organization_id
and misi.inventory_item_id = arg_item_id
and misi.secondary_inventory = arg_dest_subinventory
and misi.organization_id = ood.organization_id
and ood.set_of_books_id = fsp.set_of_books_id
and ood.operating_unit = fsp.org_id -- bug 4968383
AND Nvl(arg_source_organization_id,
misi.source_organization_id) = misi.source_organization_id;
select msi.source_organization_id,
msi.source_subinventory
into var_source_org,
var_source_sub
from mtl_secondary_inventories msi,
org_organization_definitions ood,
financials_system_parameters fsp
where msi.organization_id = arg_dest_organization_id
and msi.secondary_inventory_name = arg_dest_subinventory
and msi.organization_id = ood.organization_id
and ood.set_of_books_id = fsp.set_of_books_id
and ood.operating_unit = fsp.org_id -- bug 4968383
AND Nvl(arg_source_organization_id,
msi.source_organization_id) = msi.source_organization_id;
SELECT msi.source_organization_id,
msi.source_subinventory
INTO var_source_org,
var_source_sub
FROM mtl_system_items msi,
org_organization_definitions ood,
financials_system_parameters fsp
WHERE msi.organization_id = arg_dest_organization_id
AND msi.inventory_item_id = arg_item_id
AND msi.organization_id = ood.organization_id
AND ood.operating_unit = fsp.org_id -- bug 4968383
AND ood.set_of_books_id = fsp.set_of_books_id;
SELECT mp.source_organization_id,
mp.source_subinventory
INTO var_source_org,
var_source_sub
FROM mtl_parameters mp,
org_organization_definitions ood,
financials_system_parameters fsp
WHERE mp.organization_id = arg_dest_organization_id
AND mp.organization_id = ood.organization_id
AND ood.operating_unit = fsp.org_id -- bug 4968383
AND ood.set_of_books_id = fsp.set_of_books_id;
SELECT site.vendor_site_code
INTO var_new_site_code
FROM
po_vendor_sites_all site,
po_vendors ven
WHERE NVL(ven.enabled_flag, 'N') = 'Y'
AND SYSDATE BETWEEN NVL(ven.start_date_active, SYSDATE -1)
AND NVL(ven.end_date_active, sysdate+1)
AND SYSDATE < NVL(site.inactive_date, SYSDATE + 1)
AND ven.vendor_id = site.vendor_id(+)
AND site.vendor_site_id(+) = var_site_id
AND ven.vendor_id = var_vendor_id ;
fnd_message.set_name('MRP', 'GEN-CANNOT SELECT');
fnd_message.set_token('SELECT', 'EC_SOURCE', TRUE);
fnd_message.set_name('MRP', 'GEN-NO ROWS SELECTED');
select dest_site.vendor_site_id
into arg_vendor_site_id
FROM org_organization_definitions oog,
po_vendor_sites_all dest_site,
po_vendors ven
WHERE NVL(ven.enabled_flag, 'N') = 'Y'
AND sysdate BETWEEN NVL(ven.start_date_active, sysdate -1)
AND NVL(ven.end_date_active, sysdate+1)
AND ven.vendor_id = arg_vendor_id
AND dest_site.vendor_id(+) = ven.vendor_id
AND dest_site.vendor_site_code(+) = var_vendor_site_code
AND nvl(dest_site.org_id,nvl(oog.operating_unit,-1)) =
nvl(oog.operating_unit,-1)
AND oog.organization_id = arg_dest_organization_id;
fnd_message.set_name('MRP', 'GEN-NO ROWS SELECTED');
SELECT distinct misl.sourcing_rule_name, ml.meaning
FROM
mfg_lookups ml,
mrp_item_sourcing_levels_v misl
WHERE misl.inventory_item_id = NVL(arg_item_id, misl.inventory_item_id)
and NVL(misl.category_id,-1) = NVL(arg_category_id, NVL(misl.category_id, -1))
and misl.vendor_id = arg_supplier_id
and NVL(misl.vendor_site_id,-1) = NVL(arg_supplier_site_id,-1)
and sysdate < NVL(misl.disable_date, sysdate + 1)
and ml.lookup_type = 'MRP_ASSIGNMENT_TYPE'
and ml.lookup_code = misl.assignment_type;
select to_date(arg_start_date,'J') - sysdate
into greater_than_plan_date
from dual;
select NVL(sum(PRIMARY_QUANTITY),0)
into x_total_alloc_qty
from mtl_material_transactions
where
inventory_item_id = arg_item_id
and ORGANIZATION_ID = arg_org_id
and transaction_action_id in (30, 31, 32 )
/* WIP scrap, Assembly compl , Assy Return qty is -ve */
and transaction_date between to_date(arg_start_date,'J')
and decode(arg_end_date,NULL_VALUE,transaction_date, to_date(arg_end_date,'J'));
delete from mrp_sourcing_history msh
where
msh.inventory_item_id = arg_item_id
and msh.organization_id = arg_org_id
and msh.sourcing_rule_id = arg_sourcing_rule_id
and NVL(msh.source_org_id,-1) = decode(arg_source_org, NULL_VALUE, NVL(msh.source_org_id,-1), arg_source_org)
and NVL(msh.vendor_id,-1) = decode(arg_vendor_id, NULL_VALUE, NVL(msh.vendor_id,-1), arg_vendor_id)
and NVL(msh.vendor_site_id,-1) = decode(arg_vendor_site_id, NULL_VALUE, NVL(msh.vendor_site_id,-1), arg_vendor_site_id);
insert into mrp_sourcing_history
(
inventory_item_id,
organization_id,
sourcing_rule_id,
source_org_id,
vendor_id,
vendor_site_id,
historical_allocation,
last_calculated_date,
last_updated_by,
last_update_date,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
values (
arg_item_id,
arg_org_id,
arg_sourcing_rule_id,
decode(arg_source_org, NULL_VALUE, NULL, arg_source_org),
decode(arg_vendor_id, NULL_VALUE, NULL, arg_vendor_id),
decode(arg_vendor_site_id,NULL_VALUE, NULL, arg_vendor_site_id),
x_total_alloc_qty,
sysdate,
1,
sysdate,
sysdate,
1,
NULL,
NULL,
NULL,
NULL,
NULL
);
SELECT NVL(SUM(NVL(jobs.quantity_completed,0)),0)
INTO x_job_alloc_qty
FROM wip_discrete_jobs jobs
WHERE jobs.primary_item_id = arg_item_id
AND jobs.organization_id = arg_org_id
AND DECODE(mps_consume_profile_value,
1, jobs.mps_scheduled_completion_date,
jobs.scheduled_completion_date) between
arg_start_date and NVL(arg_end_date,
Decode(mps_consume_profile_value,
1, jobs.mps_scheduled_completion_date,
jobs.scheduled_completion_date)
+1);
SELECT
Nvl(SUM(NVL(fs.quantity_completed,0)),0)
INTO x_fs_alloc_qty
FROM wip_flow_schedules fs
WHERE fs.primary_item_id = arg_item_id
AND fs.organization_id = arg_org_id
AND DECODE(mps_consume_profile_value,
1, fs.mps_scheduled_completion_date,
fs.scheduled_completion_date) between
arg_start_date and NVL(arg_end_date,
DECODE(mps_consume_profile_value,
1, fs.mps_scheduled_completion_date,
fs.scheduled_completion_date)+1);
SELECT
Nvl(SUM(NVL(rep.daily_production_rate * rep.processing_work_days,0)),0)
INTO x_rep_alloc_qty
FROM wip_repetitive_schedules rep,
wip_repetitive_items wri,
wip_entities we
WHERE
we.organization_id = arg_org_id
AND wri.wip_entity_id = we.wip_entity_id
AND we.primary_item_id = arg_item_id
AND wri.organization_id = we.organization_id
AND rep.wip_entity_id = we.wip_entity_id
AND rep.organization_id = wri.organization_id
AND rep.line_id = wri.line_id
AND rep.last_unit_completion_date between
arg_start_date and NVL(arg_end_date,rep.last_unit_completion_date+1);
SELECT NVL(SUM(
INV_CONVERT.inv_um_convert
(
arg_item_id,
6,
Nvl(rct.quantity,0),
NULL,
NULL,
rsl.unit_of_measure,
rsl.primary_unit_of_measure
)),0)
INTO x_total_alloc_qty
FROM rcv_shipment_lines rsl,
rcv_transactions rct
WHERE rct.shipment_line_id = rsl.shipment_line_id
AND rct.transaction_type = 'DELIVER'
AND rsl.from_organization_id = arg_source_org
AND rsl.to_organization_id = arg_org_id
AND rsl.item_id = arg_item_id
AND rct.transaction_date BETWEEN
arg_start_date AND NVL(arg_end_date,rct.transaction_date +1);
SELECT /*+ use_nl(rct,rsl, poh,pol)
INDEX(rct rcv_transactions_n15)
INDEX(rsl rcv_shipment_lines_u1)
INDEX(poh po_headers_u1)
INDEX(rol po_lines_u1) */
NVL(SUM(
INV_CONVERT.inv_um_convert
(
arg_item_id,
6,
Nvl(rct.quantity,0),
NULL,
NULL,
rsl.unit_of_measure,
rsl.primary_unit_of_measure
)),0)
INTO x_total_alloc_qty
FROM po_lines_all pol,
po_headers_all poh,
rcv_shipment_lines rsl,
rcv_transactions rct
WHERE rct.shipment_line_id = rsl.shipment_line_id
AND rct.transaction_type = 'DELIVER'
AND rsl.po_header_id = poh.po_header_id
AND rsl.po_line_id = pol.po_line_id
AND poh.vendor_id = arg_vendor_id
AND NVL(poh.vendor_site_id,-99) = NVL(arg_vendor_site_id,-99)
AND pol.item_id = arg_item_id
AND rct.transaction_date between arg_start_date AND
NVL(arg_end_date,rct.transaction_date +1);