[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
,p_selection IN NUMBER
,p_cat_set_id IN NUMBER
,p_catg_struct_id IN NUMBER
,p_Catg_lo IN VARCHAR2
,p_catg_hi IN VARCHAR2
,p_item_lo IN VARCHAR2
,p_item_hi IN VARCHAR2
,p_planner_lo IN VARCHAR2
,p_planner_hi IN VARCHAR2
,p_buyer_lo IN VARCHAR2
,p_buyer_hi IN VARCHAR2
,p_sort IN VARCHAR2
,p_d_cutoff IN VARCHAR2
,p_d_cutoff_rel IN NUMBER
,p_s_cutoff IN VARCHAR2
,p_s_cutoff_rel IN NUMBER
,p_user_id IN NUMBER
,p_restock IN NUMBER
,p_handle_rep_item IN NUMBER
,p_dd_loc_id IN NUMBER
,p_net_unrsv IN NUMBER
,p_net_rsv IN NUMBER
,p_net_wip IN NUMBER
,p_include_po IN NUMBER
,p_include_wip IN NUMBER
,p_include_if IN NUMBER
,p_include_nonnet IN NUMBER
,p_lot_ctl IN NUMBER
,p_display_mode IN NUMBER
,p_show_desc IN NUMBER
,p_pur_revision IN NUMBER
,p_called_from IN VARCHAR2
) IS
l_api_version_number CONSTANT NUMBER := 1.0;
l_item_select VARCHAr2(800);
l_Cat_Select VARCHAR2(800);
SELECT employee_id
FROM fnd_user
WHERE user_id = l_user_id;
SELECT ITEM_SEGMENTS
, DESCRIPTION
, ERROR
, SORTEE
, MIN_QTY
, MAX_QTY
, ONHAND_QTY
, SUPPLY_QTY
, DEMAND_QTY
, TOT_AVAIL_QTY
, MIN_ORD_QTY
, MAX_ORD_QTY
, FIX_MULT
, REORD_QTY
FROM INV_MIN_MAX_TEMP;
SELECT c.description description,
c.repetitive_planning_flag repetitive_planned_item,
c.fixed_lead_time fixed_lead_time,
c.variable_lead_time variable_lead_time,
NVL(c.preprocessing_lead_time, 0) +
NVL(c.full_lead_time, 0) +
NVL(c.postprocessing_lead_time, 0) buying_lead_time,
c.primary_uom_code primary_uom,
p.ap_accrual_account accru_acct,
p.invoice_price_var_account ipv_acct,
NVL(c.encumbrance_account, p.encumbrance_account) budget_acct,
DECODE(c.inventory_asset_flag, 'Y', p.material_account,
NVL(c.expense_account, p.expense_account)) charge_acct,
NVL(c.source_type, p.source_type) src_type,
DECODE(c.source_type, NULL,
DECODE(p.source_type, NULL, NULL, p.source_organization_id),
c.source_organization_id) src_org,
DECODE(c.source_type, NULL,
DECODE(p.source_type, NULL, NULL, p.source_subinventory),
c.source_subinventory) src_subinv,
c.purchasing_enabled_flag purch_flag,
c.internal_order_enabled_flag order_flag,
c.mtl_transactions_enabled_flag transact_flag,
c.list_price_per_unit unit_price,
c.planning_make_buy_code mbf,
c.inventory_item_id item_id,
c.planner_code planner,
build_in_wip_flag build_in_wip,
pick_components_flag pick_components
FROM mtl_system_items_kfv c,
mtl_parameters p
WHERE c.concatenated_segments = p_item_Segments
AND c.organization_id = p.organization_id
AND p.organization_id = p_organization_id;
SELECT cpp.NODE_TYPE,cpp.ORGANIZATION_ID,cpp.SECONDARY_INVENTORY,cpp.CONDITION_TYPE,
cpp.planning_parameters_id,cpp.level_id,csin.parts_loop_id,csin.hierarchy_node_id,
csin.owner_resource_id, csin.owner_resource_type
FROM CSP_PLANNING_PARAMETERS cpp,csp_sec_inventories csin
WHERE LEVEL_ID LIKE p_level_id||'%'
and cpp.organization_id = csin.organization_id(+)
and cpp.secondary_inventory = csin.secondary_inventory_name(+);
select CSP_INV_LOC_ASSIGNMENT_ID from csp_inv_loc_assignments
where resource_id = p_resource_id and
resource_type = p_resource_type and
organization_id = p_organization_id and
SUBINVENTORY_CODE = p_subinventory_code and
(EFFECTIVE_DATE_END is null or trunc(EFFECTIVE_DATE_END) > trunc(sysdate));
SELECT ITEM_SEGMENTS,MIN_QTY,MAX_QTY,ONHAND_QTY,SUPPLY_QTY,DEMAND_QTY,
TOT_AVAIL_QTY,MIN_ORD_QTY,MAX_ORD_QTY,FIX_MULT,REORD_QTY
FROM INV_MIN_MAX_TEMP;
select distinct moq.inventory_item_id,
nvl(msib.max_minmax_quantity,0) max,
revision_qty_control_code
from mtl_onhand_quantities_detail moq,
mtl_system_items_b msib
where moq.organization_id = p_organization_id
and msib.organization_id = moq.organization_id
and msib.inventory_item_id = moq.inventory_item_id
and nvl(msib.INVENTORY_PLANNING_CODE,6) = 6;
select distinct moq.subinventory_code
from mtl_onhand_quantities moq,
csp_planning_parameters cpp
where moq.organization_id = p_organization_id
and moq.inventory_item_id = c_inventory_item_id
and cpp.organization_id = moq.organization_id
and cpp.secondary_inventory = moq.subinventory_code
and cpp.condition_type = 'G';
select msi.secondary_inventory_name
from mtl_secondary_inventories msi,
csp_planning_parameters cpp
where msi.organization_id = p_organization_id
and msi.secondary_inventory_name = nvl(p_subinventory_code,msi.secondary_inventory_name)
and cpp.organization_id = msi.organization_id
and cpp.secondary_inventory = msi.secondary_inventory_name
and cpp.condition_type = 'G';
select mosv.inventory_item_id,
nvl(misi.max_minmax_quantity,0) max,
msib.revision_qty_control_code
from mtl_onhand_sub_v mosv,
mtl_item_sub_inventories misi,
mtl_system_items_b msib
where mosv.organization_id = p_organization_id
and mosv.subinventory_code = c_subinventory_code
and misi.organization_id(+) = mosv.organization_id
and misi.inventory_item_id(+) = mosv.inventory_item_id
and misi.secondary_inventory(+) = mosv.subinventory_code
and msib.organization_id = mosv.organization_id
and msib.inventory_item_id = mosv.inventory_item_id
and nvl(misi.INVENTORY_PLANNING_CODE,6) = 6
/* Added to avoid duplicate rows of revision controled item */
group by mosv.inventory_item_id,
misi.max_minmax_quantity,
msib.revision_qty_control_code;
SELECT Sysdate INTO l_today FROM dual;
Select MEANING
into error_message
FROM MFG_LOOKUPS
WHERE LOOKUP_TYPE='INV_MMX_RPT_MSGS'
and LOOKUP_CODE = 4;
select operating_unit, substr(organization_name,1,30), operating_unit
into l_operating_unit, l_org_name, l_po_org_id
from org_organization_definitions
where organization_id = p_organization_id;
select nvl(req_encumbrance_flag, 'N')
into l_encum_flag
from financials_system_params_all
where nvl(org_id,-11)=nvl(l_operating_unit,-11);
select p.calendar_code, p.calendar_exception_set_id
into l_cal_code, l_exception_set_id
from mtl_parameters p
where p.organization_id = p_organization_id;
SELECT STRUCTURE_ID
into l_mcat_struct_id
FROM MTL_CATEGORY_SETS
WHERE CATEGORY_SET_ID = p_cat_set_id;
SELECT CSET.CATEGORY_SET_ID, CSET.STRUCTURE_ID
INTO l_category_set_id, l_mcat_struct_id
FROM MTL_CATEGORY_SETS CSET,
MTL_DEFAULT_CATEGORY_SETS DEF
WHERE DEF.CATEGORY_SET_ID = CSET.CATEGORY_SET_ID
AND DEF.FUNCTIONAL_AREA_ID = 1;
Build_item_cat_select(
p_Cat_Structure_id => l_mcat_struct_id,
x_item_select => l_item_Select,
x_cat_Select => l_cat_select);
select customer_id
into l_cust_id
from po_location_associations
where location_id = P_dd_loc_id;
select WIP_JOB_SCHEDULE_INTERFACE_S.nextval
into l_WIP_BATCH_ID
from dual;
p_item_select => l_item_select
, p_handle_rep_item => p_handle_rep_item
, p_pur_revision => nvl(fnd_profile.value('INV_PURCHASING_BY_REVISION'),2)
, p_cat_select => l_Cat_select
, p_cat_set_id => nvl(p_Cat_set_id,l_category_set_id)
, p_mcat_struct => l_mcat_struct_id
, p_level => l_level
, p_restock => 2
, p_include_nonnet => p_include_nonnet
, p_include_po => p_include_po
, p_include_wip => p_include_wip
, p_include_if => p_include_if
, p_net_rsv => p_net_rsv
, p_net_unrsv => p_net_unrsv
, p_net_wip => p_net_wip
, p_org_id => p_organization_id
, p_user_id => l_user_id
, p_employee_id => l_employee_id
, p_subinv => p_subinventory_code
, p_dd_loc_id => p_dd_loc_id
, p_wip_batch_id => l_wip_batch_id
, p_approval => to_number(nvl(FND_PROFILE.VALUE('INV_MINMAX_REORDER_APPROVED'),'1'))
, p_buyer_hi => p_buyer_hi
, p_buyer_lo => p_buyer_lo
, p_range_buyer => l_range_buyer
, p_cust_id => l_cust_id
, p_po_org_id => l_po_org_id
, p_range_sql => l_range_Sql
, p_sort => p_sort
, p_selection => 2 -- items above maximum quantity
, p_sysdate => l_today
, p_s_cutoff => l_s_cutoff
, p_d_cutoff => l_d_cutoff
, p_order_by => l_order_by
, p_encum_flag => l_encum_flag
, p_cal_code => l_cal_code
, p_exception_set_id => l_exception_set_id
, x_return_status => l_Return_status
, x_msg_data => l_msg_data);
SELECT msik.inventory_item_id
INTO l_item_id
FROM mtl_system_items_kfv msik
WHERE msik.concatenated_segments = inv_rec.item_segments
AND msik.organization_id = p_organization_id;
insert into csp_sup_dem_sub_temp(
inventory_item_id,
organization_id,
subinventory_code,
planning_parameters_id,
level_id,
parts_loop_id,
hierarchy_node_id,
excess_quantity)
values(
l_item_id,
p_organization_id,
p_subinventory_code,
rec.planning_parameters_id,
rec.level_id,
rec.parts_loop_id,
rec.hierarchy_node_id,
NVL(inv_rec.TOT_AVAIL_QTY,0) - NVL(inv_rec.SUPPLY_QTY,0) - NVL(inv_rec.MAX_QTY,0));
csp_excess_lists_pkg.Insert_Row(
px_EXCESS_LINE_ID => x_excess_line_id,
p_CREATED_BY => fnd_global.user_id,
p_CREATION_DATE => sysdate,
p_LAST_UPDATED_BY => fnd_global.user_id,
p_LAST_UPDATE_DATE => sysdate,
p_LAST_UPDATE_LOGIN => null,
p_ORGANIZATION_ID => p_organization_id,
p_SUBINVENTORY_CODE => p_subinventory_code,
p_CONDITION_CODE => 'G',
p_INVENTORY_ITEM_ID => l_item_id,
p_EXCESS_QUANTITY => NVL(inv_rec.TOT_AVAIL_QTY,0) - NVL(inv_rec.SUPPLY_QTY,0) - NVL(inv_rec.MAX_QTY,0),
p_EXCESS_STATUS => 'P',
p_REQUISITION_LINE_ID => null,
p_RETURNED_QUANTITY => null,
p_current_return_qty => null,
p_ATTRIBUTE_CATEGORY => null,
p_ATTRIBUTE1 => null,
p_ATTRIBUTE2 => null,
p_ATTRIBUTE3 => null,
p_ATTRIBUTE4 => null,
p_ATTRIBUTE5 => null,
p_ATTRIBUTE6 => null,
p_ATTRIBUTE7 => null,
p_ATTRIBUTE8 => null,
p_ATTRIBUTE9 => null,
p_ATTRIBUTE10 => null,
p_ATTRIBUTE11 => null,
p_ATTRIBUTE12 => null,
p_ATTRIBUTE13 => null,
p_ATTRIBUTE14 => null,
p_ATTRIBUTE15 => null);
insert into csp_sup_dem_sub_temp(
inventory_item_id,
organization_id,
subinventory_code,
planning_parameters_id,
level_id,
parts_loop_id,
hierarchy_node_id,
excess_quantity)
values(
coi.inventory_item_id,
p_organization_id,
null,
rec.planning_parameters_id,
rec.level_id,
rec.parts_loop_id,
rec.hierarchy_node_id,
l_excess);
csp_excess_lists_pkg.Insert_Row(
px_EXCESS_LINE_ID => x_excess_line_id,
p_CREATED_BY => fnd_global.user_id,
p_CREATION_DATE => sysdate,
p_LAST_UPDATED_BY => fnd_global.user_id,
p_LAST_UPDATE_DATE => sysdate,
p_LAST_UPDATE_LOGIN => null,
p_ORGANIZATION_ID => p_organization_id,
p_SUBINVENTORY_CODE => null,
p_CONDITION_CODE => 'G',
p_INVENTORY_ITEM_ID => coi.inventory_item_id,
p_EXCESS_QUANTITY => l_excess,
p_EXCESS_STATUS => 'P',
p_REQUISITION_LINE_ID => null,
p_RETURNED_QUANTITY => null,
p_current_return_qty => null,
p_ATTRIBUTE_CATEGORY => null,
p_ATTRIBUTE1 => null,
p_ATTRIBUTE2 => null,
p_ATTRIBUTE3 => null,
p_ATTRIBUTE4 => null,
p_ATTRIBUTE5 => null,
p_ATTRIBUTE6 => null,
p_ATTRIBUTE7 => null,
p_ATTRIBUTE8 => null,
p_ATTRIBUTE9 => null,
p_ATTRIBUTE10 => null,
p_ATTRIBUTE11 => null,
p_ATTRIBUTE12 => null,
p_ATTRIBUTE13 => null,
p_ATTRIBUTE14 => null,
p_ATTRIBUTE15 => null);
insert into csp_sup_dem_sub_temp(
inventory_item_id,
organization_id,
subinventory_code,
planning_parameters_id,
level_id,
parts_loop_id,
hierarchy_node_id,
excess_quantity)
values(
csin.inventory_item_id,
p_organization_id,
curs.secondary_inventory_name,
rec.planning_parameters_id,
rec.level_id,
rec.parts_loop_id,
rec.hierarchy_node_id,
l_excess);
csp_excess_lists_pkg.Insert_Row(
px_EXCESS_LINE_ID => x_excess_line_id,
p_CREATED_BY => fnd_global.user_id,
p_CREATION_DATE => sysdate,
p_LAST_UPDATED_BY => fnd_global.user_id,
p_LAST_UPDATE_DATE => sysdate,
p_LAST_UPDATE_LOGIN => null,
p_ORGANIZATION_ID => p_organization_id,
p_SUBINVENTORY_CODE => curs.secondary_inventory_name,
p_CONDITION_CODE => 'G',
p_INVENTORY_ITEM_ID => csin.inventory_item_id,
p_EXCESS_QUANTITY => l_excess,
p_EXCESS_STATUS => 'P',
p_REQUISITION_LINE_ID => null,
p_RETURNED_QUANTITY => null,
p_current_return_qty => null,
p_ATTRIBUTE_CATEGORY => null,
p_ATTRIBUTE1 => null,
p_ATTRIBUTE2 => null,
p_ATTRIBUTE3 => null,
p_ATTRIBUTE4 => null,
p_ATTRIBUTE5 => null,
p_ATTRIBUTE6 => null,
p_ATTRIBUTE7 => null,
p_ATTRIBUTE8 => null,
p_ATTRIBUTE9 => null,
p_ATTRIBUTE10 => null,
p_ATTRIBUTE11 => null,
p_ATTRIBUTE12 => null,
p_ATTRIBUTE13 => null,
p_ATTRIBUTE14 => null,
p_ATTRIBUTE15 => null);
update csp_excess_lists
set excess_status = 'O'
where excess_status = 'P';
Delete from INV_MIN_MAX_TEMP;
select cerb.excess_rule_id,
cerb.total_max_excess,
cerb.line_max_excess,
cerb.total_excess_value,
cerb.days_since_receipt,
cerb.top_excess_lines,
cerb.category_set_id,
cerb.category_id
from csp_excess_rules_b cerb
where excess_rule_id = p_excess_rule_id;
select sum(cel.excess_quantity * NVL(ITEM_COST,0))
from CST_ITEM_COSTS cic,
CST_COST_TYPES cct,
csp_excess_lists cel
where cic.ORGANIZATION_ID = cel.organization_id
and cic.inventory_item_id = cel.inventory_item_id
and cic.COST_TYPE_ID = cct.COST_TYPE_ID
and cct.COST_TYPE_ID = cct.DEFAULT_COST_TYPE_ID
and cel.excess_status = 'P';
select sum(msib.max_minmax_quantity * NVL(ITEM_COST,0))
from CST_ITEM_COSTS cic,
CST_COST_TYPES cct,
mtl_system_items_b msib
where msib.organization_id = p_organization_id
and cic.ORGANIZATION_ID = msib.organization_id
and cic.inventory_item_id = msib.inventory_item_id
and cic.COST_TYPE_ID = cct.COST_TYPE_ID
and cct.COST_TYPE_ID = cct.DEFAULT_COST_TYPE_ID
and msib.max_minmax_quantity > 0;
select sum(misi.max_minmax_quantity * nvl(cic.item_cost,0))
from cst_item_costs cic,
cst_cost_types cct,
mtl_item_sub_inventories misi
where misi.organization_id = p_organization_id
and misi.secondary_inventory = p_subinventory_code
and cic.organization_id = misi.organization_id
and cic.inventory_item_id = misi.inventory_item_id
and cic.cost_type_id = cct.cost_type_id
and cct.cost_type_id = cct.default_cost_type_id
and misi.max_minmax_quantity > 0;
select cel.excess_line_id,
cel.excess_quantity,
msib.max_minmax_quantity
from csp_excess_lists cel,
mtl_system_items_b msib
where cel.organization_id = p_organization_id
and cel.organization_id = msib.organization_id
and cel.inventory_item_id = msib.inventory_item_id
and cel.excess_status = 'P';
select cel.excess_line_id,
cel.excess_quantity,
misi.max_minmax_quantity
from csp_excess_lists cel,
mtl_item_sub_inventories misi
where cel.organization_id = p_organization_id
and cel.organization_id = misi.organization_id
and cel.subinventory_code = misi.secondary_inventory
and cel.inventory_item_id = misi.inventory_item_id
and cel.excess_status = 'P';
select mmt.transaction_date
from mtl_material_transactions mmt
where mmt.organization_id = p_organization_id
and mmt.inventory_item_id = p_inventory_item_id
and mmt.transaction_action_id in (2,3,12,27,31)
and mmt.transaction_quantity > 0
order by transaction_id desc;
select mmt.transaction_date
from mtl_material_transactions mmt
where mmt.organization_id = p_organization_id
and mmt.subinventory_code = p_subinventory_code
and mmt.inventory_item_id = p_inventory_item_id
and mmt.transaction_action_id in (2,3,12,27,31)
and mmt.transaction_quantity > 0
order by transaction_id desc;
select cel.excess_line_id,
cel.inventory_item_id
from csp_excess_lists cel
where cel.organization_id = p_organization_id
and cel.excess_status = 'P';
select cel.excess_line_id,
cel.excess_quantity * NVL(ITEM_COST,0) value
from CST_ITEM_COSTS cic,
CST_COST_TYPES cct,
csp_excess_lists cel
where cel.organization_id = p_organization_id
and cic.ORGANIZATION_ID = cel.organization_id
and cic.inventory_item_id = cel.inventory_item_id
and cic.COST_TYPE_ID = cct.COST_TYPE_ID
and cct.COST_TYPE_ID = cct.DEFAULT_COST_TYPE_ID
and cel.excess_status = 'P'
order by value desc;
delete from csp_excess_lists cel
where cel.excess_status = 'P'
and cel.inventory_item_id in
(select inventory_item_id
from mtl_item_categories
where category_set_id = br_rec.category_set_id
and category_id = nvl(br_rec.category_id,category_id)
and organization_id = cel.organization_id);
delete from csp_excess_lists
where excess_status = 'P';
delete from csp_excess_lists
where excess_line_id = olq.excess_line_id;
delete from csp_excess_lists
where excess_line_id = slq.excess_line_id;
delete from csp_excess_lists
where excess_line_id = el.excess_line_id;
delete from csp_excess_lists
where excess_line_id = el.excess_line_id;
delete from csp_excess_lists
where excess_line_id = elv.excess_line_id;
update csp_excess_lists
set excess_status = 'O'
where excess_line_id = elv.excess_line_id;
delete from csp_excess_lists
where excess_status = 'P';
update csp_excess_lists
set excess_status = 'O'
where excess_status = 'P';
select mosv.organization_id,
mosv.subinventory_code,
mosv.inventory_item_id,
total_qoh excess_quantity
from mtl_onhand_sub_v mosv,
csp_sec_inventories csin
where mosv.organization_id = p_organization_id
and csin.organization_id = mosv.organization_id
and csin.secondary_inventory_name = mosv.subinventory_code
and csin.condition_type = 'B'
and csin.secondary_inventory_name = nvl(p_subinventory_code,csin.secondary_inventory_name)
and total_qoh > 0;
insert into csp_sup_dem_sub_temp(
inventory_item_id,
organization_id,
subinventory_code,
planning_parameters_id,
level_id,
parts_loop_id,
hierarchy_node_id,
excess_quantity)
values(
d.inventory_item_id,
d.organization_id,
d.subinventory_code,
p_planning_parameters_id,
p_level_id,
p_parts_loop_id,
p_hierarchy_node_id,
d.excess_quantity);
csp_excess_lists_pkg.Insert_Row(
px_EXCESS_LINE_ID => x_excess_line_id,
p_CREATED_BY => fnd_global.user_id,
p_CREATION_DATE => sysdate,
p_LAST_UPDATED_BY => fnd_global.user_id,
p_LAST_UPDATE_DATE => sysdate,
p_LAST_UPDATE_LOGIN => null,
p_ORGANIZATION_ID => d.organization_id,
p_SUBINVENTORY_CODE => d.subinventory_code,
p_CONDITION_CODE => 'B',
p_INVENTORY_ITEM_ID => d.inventory_item_id,
p_EXCESS_QUANTITY => d.excess_quantity,
p_EXCESS_STATUS => 'O',
p_REQUISITION_LINE_ID => null,
p_RETURNED_QUANTITY => null,
p_current_return_qty => null,
p_ATTRIBUTE_CATEGORY => null,
p_ATTRIBUTE1 => null,
p_ATTRIBUTE2 => null,
p_ATTRIBUTE3 => null,
p_ATTRIBUTE4 => null,
p_ATTRIBUTE5 => null,
p_ATTRIBUTE6 => null,
p_ATTRIBUTE7 => null,
p_ATTRIBUTE8 => null,
p_ATTRIBUTE9 => null,
p_ATTRIBUTE10 => null,
p_ATTRIBUTE11 => null,
p_ATTRIBUTE12 => null,
p_ATTRIBUTE13 => null,
p_ATTRIBUTE14 => null,
p_ATTRIBUTE15 => null);
delete from csp_excess_lists
where organization_id = p_organization_id
and condition_code = nvl(p_condition_type,condition_code)
and excess_status = 'O';
delete from csp_excess_lists
where organization_id = p_organization_id
and subinventory_code = nvl(p_subinventory_code,subinventory_code)
and condition_code = nvl(p_condition_type,condition_code)
and excess_status = 'O';
PROCEDURE Build_Item_Cat_Select(p_Cat_structure_id IN NUMBER
,x_item_select OUT NOCOPY VARCHAR2
,x_cat_Select OUT NOCOPY VARCHAR2
) IS
l_flexfield_rec FND_FLEX_KEY_API.flexfield_type;
x_item_select := '('||l_mstk_Segs||')';
x_cat_select := '('||l_mcat_Segs||')';
select cpp.excess_rule_id
from csp_planning_parameters cpp
where cpp.organization_id = p_organization_id
and cpp.secondary_inventory = p_subinventory_code;
select cpp.excess_rule_id
from csp_planning_parameters cpp
where cpp.organization_id = p_organization_id
and cpp.secondary_inventory is null;
select revision
from mtl_item_revisions
where organization_id = p_organization_id
and inventory_item_id = p_inventory_item_id;
select sum(PRIMARY_UOM_QUANTITY- GREATEST(NVL(RESERVATION_QUANTITY,0),nvl(COMPLETED_QUANTITY,0)))
into qty
from mtl_demand
WHERE RESERVATION_TYPE = 1
AND p_net_unreserved = 1
AND parent_demand_id IS NULL
AND ORGANIZATION_ID = p_organization_id
and PRIMARY_UOM_QUANTITY > GREATEST(NVL(RESERVATION_QUANTITY,0),
nvl(COMPLETED_QUANTITY,0))
and INVENTORY_ITEM_ID = p_inventory_item_id
and REQUIREMENT_DATE <= sysdate + p_demand_cutoff
and demand_source_type not in (2,8,12)
and (p_planning_level = 1 or
SUBINVENTORY = p_subinventory_code) -- Included later for ORG Level
and (SUBINVENTORY is null or
p_planning_level = 2 or
EXISTS (SELECT 1
FROM MTL_SECONDARY_INVENTORIES S
WHERE S.ORGANIZATION_ID = p_organization_id
AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY
AND S.availability_type = DECODE(p_include_nonnet,
1,
S.availability_type,
1)));
select sum(PRIMARY_RESERVATION_QUANTITY)
into qty
from mtl_reservations
where p_net_reserved = 1
and ORGANIZATION_ID = p_organization_id
and INVENTORY_ITEM_ID = p_inventory_item_id
and REQUIREMENT_DATE <= sysdate + p_demand_cutoff
and demand_source_type_id not in (2,8,12)
and (p_planning_level = 1 or
SUBINVENTORY_CODE = p_subinventory_code) -- Included later for ORG Level
and (SUBINVENTORY_CODE is null or
p_planning_level = 2 or
EXISTS (SELECT 1
FROM MTL_SECONDARY_INVENTORIES S
WHERE S.ORGANIZATION_ID = p_organization_id
AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY_CODE
AND S.availability_type = DECODE(p_include_nonnet,
1,
S.availability_type,
1)));
select SUM(INV_DECIMALS_PUB.GET_PRIMARY_QUANTITY(SHIP_FROM_ORG_ID,
INVENTORY_ITEM_ID, ORDER_QUANTITY_UOM, Nvl(ordered_quantity,0)) -
get_shipped_qty(p_organization_id,p_inventory_item_id, ool.line_id))
into l_total_demand_qty
from oe_order_lines_all ool
where ship_from_org_id = p_organization_id
and open_flag = 'Y'
and INVENTORY_ITEM_ID = p_inventory_item_id
and schedule_ship_date <= sysdate + p_demand_cutoff
AND DECODE(OOL.SOURCE_DOCUMENT_TYPE_ID, 10, 8,DECODE(OOL.LINE_CATEGORY_CODE, 'ORDER',2,12)) IN (2,8,12)
and ((p_planning_level = 1 AND DECODE(OOL.SOURCE_DOCUMENT_TYPE_ID, 10, 8,DECODE(OOL.LINE_CATEGORY_CODE, 'ORDER',2,12)) <> 8 ) OR
SUBINVENTORY = p_subinventory_code) -- Included later for ORG Level
and (SUBINVENTORY is null or
p_planning_level = 2 or
EXISTS (SELECT 1
FROM MTL_SECONDARY_INVENTORIES S
WHERE S.ORGANIZATION_ID = p_organization_id
AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY
AND S.availability_type = DECODE(p_include_nonnet,
1,
S.availability_type,
1)));
select sum(PRIMARY_RESERVATION_QUANTITY)
into l_total_reserve_qty
from mtl_reservations
WHERE ORGANIZATION_ID = p_organization_id
and INVENTORY_ITEM_ID = p_inventory_item_id
and REQUIREMENT_DATE <= sysdate + p_demand_cutoff
and demand_source_type_id in (2,8,12)
and ((p_planning_level = 1 AND demand_source_type_id <> 8 ) OR
SUBINVENTORY_CODE = p_subinventory_code) -- Included later for ORG Level
and (SUBINVENTORY_CODE is null or
p_planning_level = 2 or
EXISTS (SELECT 1
FROM MTL_SECONDARY_INVENTORIES S
WHERE S.ORGANIZATION_ID = p_organization_id
AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY_CODE
AND S.availability_type = DECODE(p_include_nonnet,
1,
S.availability_type,
1)));
select SUM(INV_DECIMALS_PUB.GET_PRIMARY_QUANTITY(SHIP_FROM_ORG_ID,
INVENTORY_ITEM_ID, ORDER_QUANTITY_UOM, Nvl(ordered_quantity,0)) -
get_shipped_qty(p_organization_id,p_inventory_item_id, so.line_id))
into l_total_demand_qty
from oe_order_lines_all so,
po_requisition_headers_all poh,
po_requisition_lines_all pol
where so.ORIG_SYS_DOCUMENT_REF = poh.segment1
and poh.requisition_header_id = pol .requisition_header_id
and so.orig_sys_line_ref = pol.line_num
and ( pol.DESTINATION_ORGANIZATION_ID <> p_organization_id or
(pol.DESTINATION_ORGANIZATION_ID = p_organization_id and -- Added code Bug#1012179
pol.DESTINATION_TYPE_CODE = 'EXPENSE')
)
and so.ship_from_org_ID = p_organization_id
and so.open_flag = 'Y'
and so.INVENTORY_ITEM_ID = p_inventory_item_id
and schedule_ship_date <= sysdate + p_demand_cutoff
and DECODE(so.SOURCE_DOCUMENT_TYPE_ID, 10, 8,DECODE(so.LINE_CATEGORY_CODE, 'ORDER',2,12)) = 8
and (SUBINVENTORY is null or
EXISTS (SELECT 1
FROM MTL_SECONDARY_INVENTORIES S
WHERE S.ORGANIZATION_ID = p_organization_id
AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY
AND S.availability_type = DECODE(p_include_nonnet,
1,
S.availability_type,
1)));
select sum(PRIMARY_RESERVATION_QUANTITY)
into l_total_reserve_qty
from mtl_reservations md, oe_order_lines_all so,
po_req_distributions_all pod,
po_requisition_lines_all pol
where md.DEMAND_SOURCE_LINE_ID = so.LINE_ID
and so.ORIG_SYS_LINE_REF = pod.DISTRIBUTION_ID
and pod.REQUISITION_LINE_ID = pol.REQUISITION_LINE_ID
and (pol.DESTINATION_ORGANIZATION_ID <> p_organization_id or
(pol.DESTINATION_ORGANIZATION_ID = p_organization_id
and -- Added code Bug#1012179
pol.DESTINATION_TYPE_CODE = 'EXPENSE')
)
and ORGANIZATION_ID = p_organization_id
and md.INVENTORY_ITEM_ID = p_inventory_item_id
and REQUIREMENT_DATE <= sysdate + p_demand_cutoff
and demand_source_type_id = 8
and (SUBINVENTORY_CODE is null or
EXISTS (SELECT 1
FROM MTL_SECONDARY_INVENTORIES S
WHERE S.ORGANIZATION_ID = p_organization_id
AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY_CODE
AND S.availability_type = DECODE(p_include_nonnet,
1,
S.availability_type,
1)));
select sum(PRIMARY_UOM_QUANTITY - GREATEST(NVL(RESERVATION_QUANTITY,0),
nvl(COMPLETED_QUANTITY,0)))
into qty
from mtl_demand
where RESERVATION_TYPE = 3
and ORGANIZATION_ID = p_organization_id
and PRIMARY_UOM_QUANTITY > GREATEST(NVL(RESERVATION_QUANTITY,0),
nvl(COMPLETED_QUANTITY,0))
and INVENTORY_ITEM_ID = p_inventory_item_id
and REQUIREMENT_DATE <= sysdate + p_demand_cutoff
and p_net_reserved = 1
and p_planning_level = 1;
select sum(o.required_quantity - o.quantity_issued)
into qty
from wip_discrete_jobs d, wip_requirement_operations o
where o.wip_entity_id = d.wip_entity_id
and o.organization_id = d.organization_id
and d.organization_id = p_organization_id
and o.inventory_item_id = p_inventory_item_id
and o.date_required <= sysdate + p_demand_cutoff
and o.required_quantity > 0
and o.required_quantity > o.quantity_issued
and o.operation_seq_num > 0
and d.status_type in (1,3,4,6) -- Excluded 5 from selection Bug#1016495
and o.wip_supply_type <> 6;
select sum(o.required_quantity - o.quantity_issued)
into qty
from wip_repetitive_schedules r, wip_requirement_operations o
where o.wip_entity_id = r.wip_entity_id
and o.organization_id = r.organization_id
and r.organization_id = p_organization_id
and o.inventory_item_id = p_inventory_item_id
and o.date_required <= sysdate + p_demand_cutoff
and o.required_quantity > 0
and o.required_quantity > o.quantity_issued
and o.operation_seq_num > 0
and r.status_type in (1,3,4,6) -- Excluded 5 from selection Bug#1016495
and o.wip_supply_type <> 6;
/* SELECT sum(quantity - Nvl(quantity_delivered,0))
INTO qty
FROM mtl_txn_request_lines_v
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND line_status NOT IN (5,6)
AND transaction_action_id = 1
AND (p_planning_level = 1 or
from_subinventory_code = p_subinventory_code) -- Included later for ORG Level
AND ( from_subinventory_code is null or
p_planning_level = 2 or
EXISTS (SELECT 1
FROM MTL_SECONDARY_INVENTORIES S
WHERE S.ORGANIZATION_ID = p_organization_id
AND S.SECONDARY_INVENTORY_NAME = from_subinventory_code
AND S.availability_type = DECODE(p_include_nonnet,
1,S.availability_type,1)))
AND date_required <= sysdate + p_demand_cutoff;
SELECT SUM(MTRL.QUANTITY - NVL(MTRL.QUANTITY_DELIVERED,0))
INTO qty
FROM MTL_TXN_REQUEST_LINES MTRL,
MTL_TRANSACTION_TYPES MTT
WHERE MTT.TRANSACTION_TYPE_ID = MTRL.TRANSACTION_TYPE_ID
AND MTRL.ORGANIZATION_ID = p_organization_id
AND MTRL.INVENTORY_ITEM_ID = p_inventory_item_id
AND MTRL.LINE_STATUS NOT IN (5,6)
AND MTT.TRANSACTION_ACTION_ID = 1
AND (p_planning_level = 1 OR
MTRL.FROM_SUBINVENTORY_CODE = p_subinventory_code)
AND (MTRL.FROM_SUBINVENTORY_CODE IS NULL OR
p_planning_level = 2 OR
EXISTS (SELECT 1
FROM MTL_SECONDARY_INVENTORIES S
WHERE S.ORGANIZATION_ID = p_organization_id
AND S.SECONDARY_INVENTORY_NAME = MTRL.FROM_SUBINVENTORY_CODE
AND S.AVAILABILITY_TYPE = DECODE(p_include_nonnet,
1,S.AVAILABILITY_TYPE,1)))
AND MTRL.DATE_REQUIRED <= sysdate + p_demand_cutoff;
SELECT sum(quantity - Nvl(quantity_delivered,0))
INTO qty
FROM mtl_txn_request_lines_v
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND line_status NOT IN (5,6)
AND transaction_action_id IN (2,28)
AND p_planning_level = 2
AND from_subinventory_code = p_subinventory_code
AND date_required <= sysdate + p_demand_cutoff;
SELECT SUM(primary_quantity)
INTO l_shipped_qty
FROM mtl_material_transactions
WHERE transaction_action_id = 1
AND source_line_id = p_order_line_id
AND organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;