[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);
v_excess_part.LAST_UPDATED_BY := fnd_global.user_id;
v_excess_part.LAST_UPDATE_DATE := sysdate;
update CSP_SEC_INVENTORIES
set last_excess_run_date = sysdate
where organization_id = p_organization_id
and secondary_inventory_name = nvl(p_subinventory_code, secondary_inventory_name);
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);
v_excess_part.LAST_UPDATED_BY := fnd_global.user_id;
v_excess_part.LAST_UPDATE_DATE := sysdate;
update CSP_SEC_INVENTORIES
set last_excess_run_date = sysdate
where 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(
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);
v_excess_part.LAST_UPDATED_BY := fnd_global.user_id;
v_excess_part.LAST_UPDATE_DATE := sysdate;
update CSP_SEC_INVENTORIES
set last_excess_run_date = sysdate
where organization_id = p_organization_id
and secondary_inventory_name = nvl(curs.secondary_inventory_name, secondary_inventory_name);
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
and mmt.transaction_date > sysdate - br_rec.days_since_receipt;
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
and mmt.transaction_date > sysdate - br_rec.days_since_receipt;
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);
v_excess_part.LAST_UPDATED_BY := fnd_global.user_id;
v_excess_part.LAST_UPDATE_DATE := sysdate;
update CSP_SEC_INVENTORIES
set last_excess_run_date = sysdate
where organization_id = p_organization_id
and secondary_inventory_name = nvl(p_subinventory_code, secondary_inventory_name);
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;
p_is_insert_record IN VARCHAR2 default 'Y'
) IS
CURSOR c_get_return_info (cv_ORGANIZATION_ID NUMBER,
cv_SUBINVENTORY_CODE VARCHAR2) IS
select
CSI.RETURN_ORGANIZATION_ID,
CSI.RETURN_SUBINVENTORY_NAME
from
CSP_SEC_INVENTORIES CSI
where
CSI.SECONDARY_INVENTORY_NAME = cv_SUBINVENTORY_CODE
and CSI.ORGANIZATION_ID = cv_ORGANIZATION_ID;
SELECT dest_org_id,
dest_subinv
FROM csp_return_routing_rules
WHERE rule_id = v_return_rule_id;
'p_is_insert_record = ' || p_is_insert_record);
if nvl(p_is_insert_record, 'N') = 'Y' then
if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
'csp.plsql.CSP_EXCESS_PARTS_PVT.populate_excess_list',
'Calling csp_excess_lists_pkg.Insert_Row for i = ' || i);
csp_excess_lists_pkg.Insert_Row(
px_EXCESS_LINE_ID => x_excess_line_id,
p_CREATED_BY => v_excess_part.CREATED_BY,
p_CREATION_DATE => v_excess_part.CREATION_DATE,
p_LAST_UPDATED_BY => v_excess_part.LAST_UPDATED_BY,
p_LAST_UPDATE_DATE => v_excess_part.LAST_UPDATE_DATE,
p_LAST_UPDATE_LOGIN => v_excess_part.LAST_UPDATE_LOGIN,
p_ORGANIZATION_ID => v_excess_part.ORGANIZATION_ID,
p_SUBINVENTORY_CODE => v_excess_part.SUBINVENTORY_CODE,
p_CONDITION_CODE => v_excess_part.CONDITION_CODE,
p_INVENTORY_ITEM_ID => v_excess_part.INVENTORY_ITEM_ID,
p_EXCESS_QUANTITY => v_excess_part.EXCESS_QUANTITY,
p_EXCESS_STATUS => v_excess_part.EXCESS_STATUS,
p_RETURN_ORG_ID => v_excess_part.RETURN_ORGANIZATION_ID,
p_RETURN_SUB_INV => v_excess_part.RETURN_SUBINVENTORY_NAME,
p_REQUISITION_LINE_ID => v_excess_part.REQUISITION_LINE_ID,
p_RETURNED_QUANTITY => v_excess_part.RETURNED_QUANTITY,
p_current_return_qty => v_excess_part.CURRENT_RETURN_QTY,
p_ATTRIBUTE_CATEGORY => v_excess_part.ATTRIBUTE_CATEGORY,
p_ATTRIBUTE1 => v_excess_part.ATTRIBUTE1,
p_ATTRIBUTE2 => v_excess_part.ATTRIBUTE2,
p_ATTRIBUTE3 => v_excess_part.ATTRIBUTE3,
p_ATTRIBUTE4 => v_excess_part.ATTRIBUTE4,
p_ATTRIBUTE5 => v_excess_part.ATTRIBUTE5,
p_ATTRIBUTE6 => v_excess_part.ATTRIBUTE6,
p_ATTRIBUTE7 => v_excess_part.ATTRIBUTE7,
p_ATTRIBUTE8 => v_excess_part.ATTRIBUTE8,
p_ATTRIBUTE9 => v_excess_part.ATTRIBUTE9,
p_ATTRIBUTE10 => v_excess_part.ATTRIBUTE10,
p_ATTRIBUTE11 => v_excess_part.ATTRIBUTE11,
p_ATTRIBUTE12 => v_excess_part.ATTRIBUTE12,
p_ATTRIBUTE13 => v_excess_part.ATTRIBUTE13,
p_ATTRIBUTE14 => v_excess_part.ATTRIBUTE14,
p_ATTRIBUTE15 => v_excess_part.ATTRIBUTE15);
select * from (
select distinct
(decode(r.source_org_id, v_source_org_id, 1, 0)+
decode(r.source_subinv, v_source_subinv, 2, 0)+
decode(r.return_type, v_ret_trans_type, 4, 0)+
decode(r.inv_cat_set_id, mtl.category_set_id, 8, 0)+
decode(r.inv_cat_id, mtl.category_id, 16, 0)+
decode(r.inv_item_id, v_item_id, 32, 0)) as weigth,
r.rule_id
from
(
select rule_id from csp_return_routing_rules where source_org_id = v_source_org_id
and nvl(source_type, 'I') = 'I' and enabled = 'Y'
and sysdate between nvl(start_active_date, sysdate)
and nvl(end_active_date, sysdate + 1)
union
select rule_id from csp_return_routing_rules where source_subinv = v_source_subinv
and nvl(source_type, 'I') = 'I' and enabled = 'Y'
and sysdate between nvl(start_active_date, sysdate)
and nvl(end_active_date, sysdate + 1)
union
select rule_id from csp_return_routing_rules where return_type = v_ret_trans_type
and nvl(source_type, 'I') = 'I' and enabled = 'Y'
and sysdate between nvl(start_active_date, sysdate)
and nvl(end_active_date, sysdate + 1)
union
select rule_id from csp_return_routing_rules
where inv_cat_set_id in (select distinct category_set_id
from MTL_ITEM_CATEGORIES
where inventory_item_id = v_item_id
and organization_id = v_source_org_id)
and nvl(source_type, 'I') = 'I' and enabled = 'Y'
and sysdate between nvl(start_active_date, sysdate)
and nvl(end_active_date, sysdate + 1)
union
select rule_id from csp_return_routing_rules
where inv_cat_id in (select distinct category_id
from MTL_ITEM_CATEGORIES
where inventory_item_id = v_item_id
and organization_id = v_source_org_id)
and nvl(source_type, 'I') = 'I' and enabled = 'Y'
and sysdate between nvl(start_active_date, sysdate)
and nvl(end_active_date, sysdate + 1)
union
select rule_id from csp_return_routing_rules where inv_item_id = v_item_id
and nvl(source_type, 'I') = 'I' and enabled = 'Y'
and sysdate between nvl(start_active_date, sysdate)
and nvl(end_active_date, sysdate + 1)
union
select rule_id from csp_return_routing_rules
where nvl(source_type, 'I') = 'I' and enabled = 'Y'
and sysdate between nvl(start_active_date, sysdate)
and nvl(end_active_date, sysdate + 1)
and decode(source_org_id, 0, null, source_org_id) is null
and source_subinv is null
and return_type is null
and decode(inv_cat_set_id, 0, null, inv_cat_set_id) is null
and decode(inv_cat_id, 0, null, inv_cat_id) is null
and decode(inv_item_id, 0, null, inv_item_id) is null
) prob,
csp_return_routing_rules r,
MTL_ITEM_CATEGORIES mtl
where r.rule_id = prob.rule_id
and mtl.inventory_item_id = v_item_id
and mtl.organization_id = v_source_org_id
and nvl(decode(r.source_org_id, 0, null, r.source_org_id), v_source_org_id) = v_source_org_id
and nvl(r.source_subinv, nvl(v_source_subinv, 'NULL')) = nvl(v_source_subinv, 'NULL')
and nvl(r.return_type, v_ret_trans_type) = v_ret_trans_type
and nvl(decode(r.inv_cat_set_id, 0, null, r.inv_cat_set_id), mtl.category_set_id) = mtl.category_set_id
and nvl(decode(r.inv_cat_id, 0, null, r.inv_cat_id), mtl.category_id) = mtl.category_id
and nvl(decode(r.inv_item_id, 0, null, r.inv_item_id), v_item_id) = v_item_id
order by 1 desc
) intable where rownum = 1;
select * from (
select distinct
(decode(r.source_terr_id, v_source_terr_id, 1, 0)+
decode(r.return_type, v_ret_trans_type, 4, 0)+
decode(r.inv_cat_set_id, mtl.category_set_id, 8, 0)+
decode(r.inv_cat_id, mtl.category_id, 16, 0)+
decode(r.inv_item_id, v_item_id, 32, 0)) as weigth,
r.rule_id
from
(
select rule_id from csp_return_routing_rules where source_terr_id = v_source_terr_id
and nvl(source_type, v_source_terr_type) = v_source_terr_type and enabled = 'Y'
and sysdate between nvl(start_active_date, sysdate)
and nvl(end_active_date, sysdate + 1)
union
select rule_id from csp_return_routing_rules where return_type = v_ret_trans_type
and nvl(source_type, v_source_terr_type) = v_source_terr_type and enabled = 'Y'
and sysdate between nvl(start_active_date, sysdate)
and nvl(end_active_date, sysdate + 1)
union
select rule_id from csp_return_routing_rules
where inv_cat_set_id in (select distinct category_set_id
from MTL_ITEM_CATEGORIES
where inventory_item_id = v_item_id
and organization_id = cs_std.get_item_valdn_orgzn_id)
and nvl(source_type, v_source_terr_type) = v_source_terr_type and enabled = 'Y'
and sysdate between nvl(start_active_date, sysdate)
and nvl(end_active_date, sysdate + 1)
union
select rule_id from csp_return_routing_rules
where inv_cat_id in (select distinct category_id
from MTL_ITEM_CATEGORIES
where inventory_item_id = v_item_id
and organization_id = cs_std.get_item_valdn_orgzn_id)
and nvl(source_type, v_source_terr_type) = v_source_terr_type and enabled = 'Y'
and sysdate between nvl(start_active_date, sysdate)
and nvl(end_active_date, sysdate + 1)
union
select rule_id from csp_return_routing_rules where inv_item_id = v_item_id
and nvl(source_type, v_source_terr_type) = v_source_terr_type and enabled = 'Y'
and sysdate between nvl(start_active_date, sysdate)
and nvl(end_active_date, sysdate + 1)
union
select rule_id from csp_return_routing_rules
where nvl(source_type, v_source_terr_type) = v_source_terr_type and enabled = 'Y'
and sysdate between nvl(start_active_date, sysdate)
and nvl(end_active_date, sysdate + 1)
and decode(source_terr_id, 0, null, source_terr_id) is null
and return_type is null
and decode(inv_cat_set_id, 0, null, inv_cat_set_id) is null
and decode(inv_cat_id, 0, null, inv_cat_id) is null
and decode(inv_item_id, 0, null, inv_item_id) is null
) prob,
csp_return_routing_rules r,
MTL_ITEM_CATEGORIES mtl
where r.rule_id = prob.rule_id
and mtl.inventory_item_id = v_item_id
and mtl.organization_id = cs_std.get_item_valdn_orgzn_id
and nvl(decode(r.source_terr_id, 0, null, r.source_terr_id), v_source_terr_id) = v_source_terr_id
and nvl(r.return_type, v_ret_trans_type) = v_ret_trans_type
and nvl(decode(r.inv_cat_set_id, 0, null, r.inv_cat_set_id), mtl.category_set_id) = mtl.category_set_id
and nvl(decode(r.inv_cat_id, 0, null, r.inv_cat_id), mtl.category_id) = mtl.category_id
and nvl(decode(r.inv_item_id, 0, null, r.inv_item_id), v_item_id) = v_item_id
order by 1 desc
) intable where rownum = 1;
select
nvl(stocking_site_type, 'MANNED')
from csp_planning_parameters
where organization_id = v_org_id
and nvl(secondary_inventory, 'NULL') = nvl(v_subinv, 'NULL');
SELECT HLOC.CITY,
HLOC.POSTAL_CODE,
HLOC.STATE,
HLOC.PROVINCE,
HLOC.COUNTY,
HLOC.COUNTRY
FROM csp_sec_inventories CINV,
HZ_LOCATIONS HLOC,
csp_rs_cust_relations rcr,
hz_cust_acct_sites_All cas,
hz_cust_site_uses_all csu,
hz_party_sites ps
WHERE CINV.ORGANIZATION_ID = v_org_id
AND CINV.secondary_inventory_name = v_subinv
AND CINV.owner_resource_id = rcr.RESOURCE_ID
AND CINV.owner_resource_type = rcr.RESOURCE_TYPE
AND rcr.customer_id = cas.cust_account_id
AND cas.cust_acct_site_id = csu.cust_acct_site_id
AND csu.site_use_code = 'SHIP_TO'
AND csu.PRIMARY_FLAG = 'Y'
AND csu.STATUS = 'A'
AND cas.status = 'A'
AND cas.party_site_id = ps.party_site_id
AND HLOC.LOCATION_ID = ps.location_id
AND rownum = 1;
select
c.hz_location_id,
h.city,
h.postal_code,
h.state,
h.province,
h.county,
h.country
from
csp_planning_parameters c,
hz_locations h
where c.organization_id = v_org_id
and nvl(c.secondary_inventory, 'NULL') = nvl(v_subinv, 'NULL')
and c.hz_location_id = h.location_id;
select
hrloc.town_or_city as city,
hrloc.postal_code as postal_code,
null as state,
null as province,
null as county,
hrloc.country as country
from
MTL_SECONDARY_INVENTORIES sub,
hr_all_organization_units org,
hr_locations_all hrloc
where org.organization_id = v_org_id
and org.organization_id = sub.organization_id(+)
and sub.secondary_inventory_name(+) = v_subinv
and nvl(sub.location_id, org.location_id) = hrloc.location_id;
select
tall.terr_id
from
jtf_terr_all tall,
jtf_terr_results_gt_mt tmt,
JTF_TERR_TYPES_ALL tty
where
tall.terr_id = tmt.terr_id
and tty.application_short_name = 'CSP'
and tty.org_id = tall.org_id
and tall.territory_type_id = tty.terr_type_id
and tty.enabled_flag = 'Y'
and tall.enabled_flag = 'Y'
and tall.start_date_active <= sysdate
and nvl(tall.end_date_active, sysdate+1) > sysdate
order by tmt.absolute_rank desc;
select tall.terr_id
from jtf_terr_all tall,
jtf_terr_results_gt_mt tmt,
jtf_terr_types_all tty
where tall.terr_id = tmt.terr_id
and tty.application_short_name = 'CSP'
and tty.org_id = tall.org_id
and tall.territory_type_id = tty.terr_type_id
and tty.enabled_flag = 'Y'
and tall.enabled_flag='Y'
and tall.start_date_active <=sysdate
and nvl(tall.end_date_active,sysdate+1) >= sysdate
order by tmt.absolute_rank desc;
select hl.city,hl.postal_code,hl.state,hl.province,hl.county,hl.country
from hz_locations hl
where hl.location_id = p_hz_location_id;
select crrr.dest_org_id,
crrr.dest_subinv,
cpp.hz_location_id,
hoa.location_id,
ood.operating_unit
from csp_return_routing_rules crrr,
hr_organization_units hoa,
csp_planning_parameters cpp,
org_organization_definitions ood
where crrr.rule_id = p_return_rule_id
and crrr.dest_subinv is null
and crrr.dest_org_id = hoa.organization_id
and ood.organization_id = hoa.organization_id
and cpp.organization_id (+) = crrr.dest_org_id
and cpp.secondary_inventory (+) = crrr.dest_subinv
union
select crrr.dest_org_id,
crrr.dest_subinv,
cpp.hz_location_id,
msi.location_id,
ood.operating_unit
from csp_return_routing_rules crrr,
mtl_secondary_inventories msi,
csp_planning_parameters cpp,
org_organization_definitions ood
where crrr.rule_id = p_return_rule_id
and crrr.dest_org_id = msi.organization_id
and crrr.dest_subinv = msi.secondary_inventory_name
and ood.organization_id = msi.organization_id
and cpp.organization_id (+) = crrr.dest_org_id
and cpp.secondary_inventory (+) = crrr.dest_subinv
and crrr.dest_subinv is not null;