[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Build_item_cat_select(p_Cat_structure_id IN NUMBER
,x_item_select OUT NOCOPY VARCHAR2
,x_cat_Select OUT NOCOPY VARCHAR2
);
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 notification_id,
inventory_item_id,
notification_type,
quantity,
need_date
FROM csp_notifications
WHERE organization_id = p_organization_id;
SELECT inventory_item_id
FROM mtl_related_items_view
WHERE relationship_type_id = 18
AND related_item_id = p_item_id;
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_category_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_repitem
, p_pur_revision => nvl(fnd_profile.value('INV_PURCHASING_BY_REVISION'),2)
, p_cat_select => l_Cat_select
, p_cat_set_id => p_Category_set_id
, p_mcat_struct => l_mcat_struct_id
, p_level => 1 -- always run at organization level
, p_restock => l_Restock
, p_include_nonnet => p_include_nonnet_sub
, p_include_po => p_include_po
, p_include_wip => p_include_wip
, p_include_if => p_include_iface_sup
, 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 => null
, 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 => 1 -- items under minimum 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 count(inventory_item_id)
INTO l_count
FROM csp_notifications
WHERE organization_id = p_organization_id
AND inventory_item_id = l_item_attr_rec.item_id
AND nvl(suppress_end_date, sysdate) >= sysdate;
SELECT c1.calendar_date
INTO l_need_by_date
FROM bom_calendar_dates c1,
bom_calendar_dates c
WHERE c1.calendar_code = c.calendar_code
AND c1.exception_set_id = c.exception_set_id
AND c1.seq_num = c.next_seq_num + CEIL(l_item_attr_rec.buying_lead_time)
AND c.calendar_code = l_cal_code
AND c.exception_set_id = l_exception_set_id
AND c.calendar_date = trunc(sysdate);
csp_notifications_pkg.insert_row(
px_notification_id => l_notification_id,
p_created_by => l_user_id,
p_creation_date => sysdate,
p_last_updated_by => l_user_id,
p_last_update_date => sysdate,
p_last_update_login => l_login_id,
p_planner_code => l_item_attr_rec.planner,
p_parts_loop_id => null,
p_organization_id => p_organization_id,
p_inventory_item_id => l_item_attr_rec.item_id,
p_notification_date => sysdate,
p_reason => 'N',
p_status => '1',
p_quantity => l_index.reord_qty,
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,
p_need_date => l_need_by_date,
p_suppress_end_date => null,
p_notification_type => 'IO');
csp_notifications_pkg.insert_row(
px_notification_id => l_notification_id,
p_created_by => l_user_id,
p_creation_date => sysdate,
p_last_updated_by => l_user_id,
p_last_update_date => sysdate,
p_last_update_login => l_login_id,
p_planner_code => l_item_attr_rec.planner,
p_parts_loop_id => null,
p_organization_id => p_organization_id,
p_inventory_item_id => l_item_attr_rec.item_id,
p_notification_date => sysdate,
p_reason => 'N',
p_status => '1',
p_quantity => l_index.reord_qty,
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,
p_need_date => l_need_by_date,
p_suppress_end_date => null,
p_notification_type => 'PO');
csp_notifications_pkg.insert_row(
px_notification_id => l_notification_id,
p_created_by => l_user_id,
p_creation_date => sysdate,
p_last_updated_by => l_user_id,
p_last_update_date => sysdate,
p_last_update_login => l_login_id,
p_planner_code => l_item_attr_rec.planner,
p_parts_loop_id => null,
p_organization_id => p_organization_id,
p_inventory_item_id => l_item_attr_rec.item_id,
p_notification_date => sysdate,
p_reason => 'N',
p_status => '1',
p_quantity => l_index.reord_qty,
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,
p_need_date => null, --l_need_by_date,
p_suppress_end_date => null,
p_notification_type => 'NS');
SELECT c1.calendar_date
INTO l_need_by_date
FROM bom_calendar_dates c1,
bom_calendar_dates c
WHERE c1.calendar_code = c.calendar_code
AND c1.exception_set_id = c.exception_set_id
AND c1.seq_num = (c.next_seq_num + CEIL(l_lead_time))
AND c.calendar_code = l_cal_code
AND c.exception_set_id = l_exception_set_id
AND c.calendar_date = trunc(sysdate);
csp_notifications_pkg.insert_row(
px_notification_id => l_notification_id,
p_created_by => l_user_id,
p_creation_date => sysdate,
p_last_updated_by => l_user_id,
p_last_update_date => sysdate,
p_last_update_login => l_login_id,
p_planner_code => l_item_attr_rec.planner,
p_parts_loop_id => null,
p_organization_id => p_organization_id,
p_inventory_item_id => l_item_attr_rec.item_id,
p_notification_date => sysdate,
p_reason => 'N',
p_status => '1',
p_quantity => l_index.reord_qty,
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,
p_need_date => l_need_by_date,
p_suppress_end_date => null,
p_notification_type => 'WIP');
delete from INV_MIN_MAX_TEMP;
p_item_select => l_item_select
, p_handle_rep_item => p_repitem
, p_pur_revision => nvl(fnd_profile.value('INV_PURCHASING_BY_REVISION'),2)
, p_cat_select => l_Cat_select
, p_cat_set_id => p_Category_set_id
, p_mcat_struct => l_mcat_struct_id
, p_level => 1 -- always run at organization level
, p_restock => 2
, p_include_nonnet => p_include_nonnet_sub
, p_include_po => p_include_po
, p_include_wip => p_include_wip
, p_include_if => p_include_iface_sup
, 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 => null
, 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 + 10000)
, 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.planner_code,
msik.inventory_item_id
INTO l_planner,
l_item_id
FROM mtl_system_items_kfv msik
WHERE msik.concatenated_segments = mrc.item_segments
AND msik.organization_id = p_organization_id;
SELECT count(inventory_item_id)
INTO l_count
FROM csp_notifications
WHERE organization_id = p_organization_id
AND inventory_item_id = l_item_id
AND nvl(suppress_end_date, sysdate) >= sysdate;
SELECT sum(pol.quantity)
INTO l_supply_qty
FROM po_requisition_headers_all poh,
po_requisition_lines_all pol
WHERE poh.authorization_status = 'INCOMPLETE'
AND pol.requisition_header_id = poh.requisition_header_id
AND pol.destination_type_code = 'INVENTORY'
AND pol.item_id = l_item_id
AND pol.destination_organization_id = p_organization_id;
select edq_multiple,
minimum_Value
into l_Edq_multiple,
l_min_Value
from csp_planning_parameters
where organization_id = p_organization_id
and secondary_inventory is null;
SELECT cic.item_cost
INTO l_item_cost
FROM cst_item_costs cic,
mtl_parameters mp
WHERE cic.inventory_item_id = l_item_id
AND cic.organization_id = mp.organization_id
AND cic.cost_type_id = mp.primary_cost_method
AND mp.organization_id = p_organization_id;
csp_notifications_pkg.insert_row(
px_notification_id => l_notification_id,
p_created_by => l_user_id,
p_creation_date => sysdate,
p_last_updated_by => l_user_id,
p_last_update_date => sysdate,
p_last_update_login => l_login_id,
p_planner_code => l_planner,
p_parts_loop_id => null,
p_organization_id => p_organization_id,
p_inventory_item_id => l_item_id,
p_notification_date => sysdate,
p_reason => 'N',
p_status => '1',
p_quantity => l_EOO_qty,
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,
p_need_date => null,
p_suppress_end_date => null,
p_notification_type => 'EOO');
DELETE FROM INV_MIN_MAX_TEMP;
select 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
into l_fixed_lt,
l_Variable_lt,
l_buying_lt
from mtl_system_items c
where c.inventory_item_id = onc.inventory_item_id
and organization_id = p_organization_id;
SELECT c1.calendar_date
INTO l_est_date
FROM bom_calendar_dates c1,
bom_calendar_dates c
WHERE c1.calendar_code = c.calendar_code
AND c1.exception_set_id = c.exception_set_id
AND c1.seq_num = c.prior_seq_num - CEIL(l_buying_lt)
AND c.calendar_code = l_cal_code
AND c.exception_set_id = l_exception_set_id
AND c.calendar_date = trunc(onc.need_date);
SELECT c1.calendar_date
INTO l_est_date
FROM bom_calendar_dates c1,
bom_calendar_dates c
WHERE c1.calendar_code = c.calendar_code
AND c1.exception_set_id = c.exception_set_id
AND c1.seq_num = (c.prior_seq_num - CEIL(l_lead_time))
AND c.calendar_code = l_cal_code
AND c.exception_set_id = l_exception_set_id
AND c.calendar_date = trunc(onc.need_date);
SELECT related_item_id
INTO l_related_item
FROM mtl_related_items_view
WHERE relationship_type_id = 18
AND inventory_item_id = onc.inventory_item_id;
SELECT tracking_signal
FROM csp_usage_headers
WHERE organization_id = p_organization_id
AND inventory_item_id = onc.inventory_item_id
AND header_Data_type = 4;
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.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.inventory_item_id = p_item_id
AND c.organization_id = p.organization_id
AND p.organization_id = p_organization_id;
SELECT notification_rule_id
INTO l_business_rule_id
FROM csp_planning_parameters
WHERE organization_id = p_organization_id
AND node_type = 'ORGANIZATION_WH';
SELECT IO_Excess_Value
,IO_Repair_Value
,IO_Recommend_Value
,IO_Tracking_Signal_Max
,IO_Tracking_Signal_Min
,REQ_Excess_Value
,REQ_Repair_Value
,REQ_Recommend_Value
,REQ_Tracking_Signal_Max
,REQ_Tracking_Signal_Min
,WIP_Order_Excess_Value
,WIP_Order_Repair_Value
,WIP_Order_Recommend_Value
,WIP_Order_Tracking_Signal_Max
,WIP_Order_Tracking_Signal_Min
INTO l_business_rule_rec
FROM csp_notification_rules_vl
WHERE notification_rule_id = l_business_rule_id;
SELECT cic.item_cost
INTO l_item_cost
FROM cst_item_costs cic,
mtl_parameters mp
WHERE cic.inventory_item_id = onc.inventory_item_id
AND cic.organization_id = mp.organization_id
AND cic.cost_type_id = mp.primary_cost_method
AND mp.organization_id = p_organization_id;
SELECT nvl(SUM(DECODE(cnd.source_type, 'EXCESS', cnd.available_quantity, null)), 0) AS Excess_Qty,
nvl(SUM(DECODE(cnd.source_type, 'REPAIR', cnd.available_quantity, null)), 0) AS Repair_Qty
INTO l_total_excess, l_total_repair
FROM csp_notification_details cnd
WHERE notification_id = onc.notification_id;
update csp_notifications
set status = 5
where notification_id = onc.notification_id;
DELETE FROM csp_notification_Details
WHERE notification_id = onc.notification_id;
update csp_notifications
set status = 5
where notification_id = onc.notification_id;
DELETE FROM csp_notification_Details
WHERE notification_id = onc.notification_id;
update csp_notifications
set status = 5
where notification_id = onc.notification_id;
DELETE FROM csp_notification_Details
WHERE notification_id = onc.notification_id;
select 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
into l_parts_rec.source_org_id,
l_parts_rec.source_subinv
from mtl_system_items c,
mtl_parameters p
where c.inventory_item_id = l_parts_rec.inventory_item_id
and c.organization_id = p.organization_id
and p.organization_id = p_organization_id;
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 MAX(revision_qty_control_code)
INTO item_rev_ctl
FROM mtl_system_items msi
WHERE msi.organization_id = orgn_id
AND msi.inventory_item_id = item_id;
SELECT MAX(revision)
INTO item_rev
FROM mtl_item_revisions mir
WHERE inventory_item_id = item_id
AND organization_id = orgn_id
AND effectivity_date < SYSDATE
AND effectivity_date =
(
SELECT MAX(effectivity_date)
FROM mtl_item_revisions mir1
WHERE mir1.inventory_item_id = mir.inventory_item_id
AND mir1.organization_id = mir.organization_id
AND effectivity_date < SYSDATE
);
INSERT INTO po_requisitions_interface_all(
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
ITEM_DESCRIPTION,
CREATION_DATE,
CREATED_BY,
PREPARER_ID,
INTERFACE_SOURCE_CODE,
REQUISITION_TYPE,
AUTHORIZATION_STATUS,
SOURCE_TYPE_CODE,
SOURCE_ORGANIZATION_ID,
SOURCE_SUBINVENTORY,
DESTINATION_ORGANIZATION_ID,
DESTINATION_SUBINVENTORY,
DELIVER_TO_REQUESTOR_ID,
DESTINATION_TYPE_CODE,
UOM_CODE,
DELIVER_TO_LOCATION_ID,
ITEM_ID,
ITEM_REVISION,
QUANTITY,
NEED_BY_DATE,
GL_DATE,
CHARGE_ACCOUNT_ID,
ACCRUAL_ACCOUNT_ID,
VARIANCE_ACCOUNT_ID,
BUDGET_ACCOUNT_ID,
AUTOSOURCE_FLAG,
ORG_ID)
VALUES (
sysdate,
user_id,
description,
sysdate,
user_id,
employee_id,
'INV',
DECODE(src_type, 1, 'INTERNAL', 'PURCHASE'),
DECODE(APPROVAL,1,'INCOMPLETE',2,'APPROVED'),
DECODE(src_type, 1, 'INVENTORY', 'VENDOR'),
src_org,
src_subinv,
organization_id,
subinv,
employee_id,
'INVENTORY',
uom,
location_id,
item_id,
DECODE(item_rev,'@@@',NULL,item_rev),
qty,
trunc(nb_time),
SYSDATE,
charge_acct,
accru_acct,
ipv_acct,
budget_acct,
'P',
po_org_id);
SELECT meaning
INTO x_ret_mesg
FROM mfg_lookups
WHERE lookup_type = 'INV_MMX_RPT_MSGS'
AND lookup_code = 1;
INSERT INTO WIP_JOB_SCHEDULE_INTERFACE(
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
GROUP_ID,
PROCESS_PHASE,
PROCESS_STATUS,
ORGANIZATION_ID,
LOAD_TYPE,
LAST_UNIT_COMPLETION_DATE,
PRIMARY_ITEM_ID,
START_QUANTITY,
STATUS_TYPE)
VALUES(
sysd,
user_id,
sysd,
user_id,
WIP_ID,
2,
1,
organization_id,
1,
nb_time,
item_id,
qty,
DECODE(approval,1,1,2,3));
SELECT meaning
INTO x_ret_mesg
FROM mfg_lookups
WHERE lookup_type = 'INV_MMX_RPT_MSGS'
AND lookup_code = 2;
l_item_select VARCHAR2(800);
l_cat_select VARCHAR2(800);
select misl.source_organization_id
from MRP_ITEM_SOURCING_LEVELS_V misl, csp_planning_parameters cpp
where cpp.organization_id = p_organization_id
and misl.organization_id = cpp.organization_id
and misl.assignment_set_id =cpp.usable_assignment_set_id
and inventory_item_id = p_item_rec.inventory_item_id
and SOURCE_TYPE = 1
and sourcing_level = (select min(sourcing_level) from MRP_ITEM_SOURCING_LEVELS_V
where organization_id = p_organization_id
and assignment_set_id = cpp.usable_assignment_set_id
and inventory_item_id = p_item_rec.inventory_item_id
and sourcing_level not in (2,9));
SELECT secondary_inventory_name
FROM mtl_secondary_inventories
WHERE organization_id = p_orgn_id
AND availability_type = 1;
(SELECT secondary_inventory_name
FROM csp_sec_inventories
WHERE condition_type = 'B'
AND organization_id = p_orgn_id);
SELECT secondary_inventory_name
FROM csp_sec_inventories
WHERE organization_id = p_orgn_id
AND condition_type = 'G';
SELECT employee_id
FROM fnd_user
WHERE user_id = l_user_id;
SELECT STRUCTURE_ID
into l_mcat_struct_id
FROM MTL_CATEGORY_SETS
WHERE CATEGORY_SET_ID = p_item_rec.category_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(l_mcat_struct_id,
l_item_Select,
l_cat_select);
SELECT concatenated_segments
INTO l_item
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_item_rec.inventory_item_id;
SELECT organization_type,
condition_type
INTO l_organization_type,
l_condition_type
FROM csp_planning_parameters
WHERE organization_id = esc.source_organization_id
AND secondary_inventory IS NULL;
SELECT inventory_planning_code
INTO l_item_minmax_flag
FROM mtl_system_items
where organization_id = esc.source_organization_id
and inventory_item_id = p_item_rec.inventory_item_id;
p_item_select => l_item_select
, p_handle_rep_item => 2
, p_pur_revision => nvl(fnd_profile.value('INV_PURCHASING_BY_REVISION'),2)
, p_cat_select => l_Cat_select
, p_cat_set_id => nvl(p_item_rec.Category_set_id, l_category_set_id)
, p_mcat_struct => l_mcat_struct_id
, p_level => 1 -- run at orgn level
, p_restock => 2 -- no restock
, p_include_nonnet => 2 -- do not include non nettable subinv
, p_include_po => nvl(p_item_rec.include_po, 1)
, p_include_wip => nvl(p_item_rec.include_wip, 1)
, p_include_if => nvl(p_item_rec.include_iface_sup, 1)
, p_net_rsv => nvl(p_item_rec.net_rsv, 1)
, p_net_unrsv => nvl(p_item_rec.net_unrsv, 1)
, p_net_wip => nvl(p_item_rec.net_wip, 1)
, p_org_id => esc.source_organization_id
, p_user_id => l_user_id
, p_employee_id => l_employee_id
, p_subinv => null
, p_dd_loc_id => p_item_rec.dd_loc_id
, p_wip_batch_id => null --l_wip_batch_id
, p_approval => to_number(nvl(FND_PROFILE.VALUE('INV_MINMAX_REORDER_APPROVED'),'1'))
, p_buyer_hi => null --p_buyer_hi
, p_buyer_lo => null --p_buyer_lo
, p_range_buyer => null --l_range_buyer
, p_cust_id => null --l_cust_id
, p_po_org_id => null --l_po_org_id
, p_range_sql => l_range_Sql
, p_sort => 1 --p_sort
, p_selection => 2 -- items above maximum quantity
, p_sysdate => sysdate
, p_s_cutoff => nvl(p_item_rec.s_cutoff, sysdate)
, p_d_cutoff => nvl(p_item_rec.d_cutoff, sysdate)
, p_order_by => l_order_by
, p_encum_flag => null --l_encum_flag
, p_cal_code => null --l_cal_code
, p_exception_set_id => null --l_exception_set_id
, x_return_status => l_Return_status
, x_msg_data => l_msg_data);
SELECT (tot_avail_qty - max_qty) excess_qty
INTO l_excess_qty
FROM INV_MIN_MAX_TEMP
WHERE item_Segments = l_item;
DELETE FROM INV_MIN_MAX_TEMP;
SELECT inventory_planning_code
INTO l_sub_minmax_flag
FROM mtl_item_sub_inventories
WHERE organization_id = esc.source_organization_id
AND secondary_inventory = rsc.secondary_inventory_name
AND inventory_item_id = p_item_rec.inventory_item_id;
p_item_select => l_item_select
, p_handle_rep_item => 2
, p_pur_revision => nvl(fnd_profile.value('INV_PURCHASING_BY_REVISION'),2)
, p_cat_select => l_Cat_select
, p_cat_set_id => nvl(p_item_rec.Category_set_id, l_category_set_id)
, p_mcat_struct => l_mcat_struct_id
, p_level => 2 -- run at organization level
, p_restock => 2 -- no restock
, p_include_nonnet => 1 -- include non nettable subinv
, p_include_po => nvl(p_item_rec.include_po, 1)
, p_include_wip => nvl(p_item_rec.include_wip, 1)
, p_include_if => nvl(p_item_rec.include_iface_sup, 1)
, p_net_rsv => nvl(p_item_rec.net_rsv, 1)
, p_net_unrsv => nvl(p_item_rec.net_unrsv, 1)
, p_net_wip => nvl(p_item_rec.net_wip, 1)
, p_org_id => esc.source_organization_id
, p_user_id => l_user_id
, p_employee_id => l_employee_id
, p_subinv => rsc.secondary_inventory_name
, p_dd_loc_id => p_item_rec.dd_loc_id
, p_wip_batch_id => null --l_wip_batch_id
, p_approval => to_number(nvl(FND_PROFILE.VALUE('INV_MINMAX_REORDER_APPROVED'),'1'))
, p_buyer_hi => null --p_buyer_hi
, p_buyer_lo => null --p_buyer_lo
, p_range_buyer => null --l_range_buyer
, p_cust_id => null --l_cust_id
, p_po_org_id => null --l_po_org_id
, p_range_sql => l_range_Sql
, p_sort => 1 --p_sort
, p_selection => 2 -- items above maximum quantity
, p_sysdate => sysdate
, p_s_cutoff => nvl(p_item_rec.s_cutoff, sysdate)
, p_d_cutoff => nvl(p_item_rec.d_cutoff, sysdate)
, p_order_by => l_order_by
, p_encum_flag => null --l_encum_flag
, p_cal_code => null --l_cal_code
, p_exception_set_id => null --l_exception_set_id
, x_return_status => l_Return_status
, x_msg_data => l_msg_data);
SELECT (tot_avail_qty - max_qty) excess_qty
INTO l_excess_qty
FROM INV_MIN_MAX_TEMP
WHERE item_Segments = l_item;
DELETE FROM INV_MIN_MAX_TEMP;
select misl.source_organization_id
from MRP_ITEM_SOURCING_LEVELS_V misl, csp_planning_parameters cpp
where cpp.organization_id = p_organization_id
and misl.organization_id = cpp.organization_id
and misl.assignment_set_id =cpp.defective_assignment_set_id
and inventory_item_id = p_inventory_item_id
and SOURCE_TYPE = 1
and sourcing_level = (select min(sourcing_level) from MRP_ITEM_SOURCING_LEVELS_V
where organization_id = p_organization_id
and assignment_set_id = cpp.defective_assignment_set_id
and inventory_item_id = p_inventory_item_id
and sourcing_level not in (2,9));
select misl.source_type, misl.source_organization_id
from MRP_ITEM_SOURCING_LEVELS_V misl, csp_planning_parameters cpp
where cpp.organization_id = p_organization_id
and misl.organization_id = cpp.organization_id
and misl.assignment_set_id =cpp.repair_assignment_set_id
and inventory_item_id = p_inventory_item_id
and SOURCE_TYPE in (1,3)
and sourcing_level = (select min(sourcing_level) from MRP_ITEM_SOURCING_LEVELS_V
where organization_id = p_organization_id
and assignment_set_id = cpp.repair_assignment_set_id
and inventory_item_id = p_inventory_item_id
and sourcing_level not in (2,9))
order by misl.rank;
SELECT secondary_inventory_name
FROM mtl_secondary_inventories
WHERE organization_id = p_orgn_id
AND secondary_inventory_name NOT IN
(SELECT secondary_inventory_name
FROM csp_sec_inventories
WHERE condition_type = 'G'
AND organization_id = p_orgn_id);
SELECT secondary_inventory_name
FROM csp_sec_inventories
WHERE organization_id = p_orgn_id
AND condition_type = 'B';
select serv_req_enabled_code
into l_Serviceable
from mtl_system_items
where inventory_item_id = p_inventory_item_id
and organization_id = rsc.source_organization_id;
SELECT organization_type,
nvl(condition_type, 'G')
INTO l_organization_type,
l_condition_type
FROM csp_planning_parameters
WHERE organization_id = rep.source_organization_id
AND secondary_inventory IS NULL;
DELETE FROM csp_notification_Details
WHERE notification_id in
(SELECT notification_id
FROM csp_notifications
WHERE trunc(nvl(suppress_end_date, sysdate)) <= trunc(sysdate)
AND organization_id = p_organization_id);
DELETE FROM csp_notifications
WHERE trunc(nvl(suppress_end_date, sysdate)) <= trunc(sysdate)
AND organization_id = p_organization_id;
CSP_Notification_Details_PKG.Insert_Row(
px_NOTIFICATION_DETAIL_ID => l_notif_detail_id
,p_NOTIFICATION_ID => p_notification_id
,p_INVENTORY_ITEM_ID => p_parts_rec.inventory_item_id
,p_AVAILABLE_QUANTITY => p_parts_rec.quantity
,p_ORDER_BY_DATE => nvl(p_order_by_dt,sysdate)
,p_SOURCE_TYPE => p_source_type
,p_SOURCE_ORGANIZATION_ID => p_parts_rec.source_org_id
,p_SOURCE_SUBINVENTORY => p_parts_rec.source_subinv
,p_CREATED_BY => nvl(fnd_global.user_id, 0)
,p_CREATION_DATE => sysdate
,p_LAST_UPDATED_BY => nvl(fnd_global.user_id, 0)
,p_LAST_UPDATE_DATE => sysdate
,p_LAST_UPDATE_LOGIN => nvl(fnd_global.login_id, -1)
,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
,p_REPAIR_SUPPLIER_ID => p_parts_rec.repair_supplier_id
,p_ORDER_NUMBER => NULL
);