[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT clcrb.include_sales_orders
, clcrb.include_move_orders
, clcrb.include_work_orders
, clcrb.include_purchase_orders
, clcrb.include_requisitions
, clcrb.include_interorg_transfers
, clcrb.include_onhand_good
, clcrb.include_onhand_bad
, clcrb.include_intransit_move_orders
, clcrb.tolerance_percent
FROM csp_loop_calc_rules_b clcrb
, csp_parts_loops_b cplb
WHERE cplb.parts_loop_id = p_parts_loop_id
AND cplb.calculation_rule_id = clcrb.calculation_rule_id;
SELECT decode(l_include_intransit_mo,'Y',nvl(ccsds.intransit_move_orders,0),0)
+ decode(l_include_interorg_transfers,'Y',nvl(ccsds.open_interorg_transf_in,0)-nvl(ccsds.open_interorg_transf_out,0),0)
+ decode(l_include_sales_orders,'Y',nvl(ccsds.open_sales_orders,0),0)
+ decode(l_include_move_orders,'Y',nvl(ccsds.open_move_orders_in,0)-nvl(ccsds.open_move_orders_out,0),0)
+ decode(l_include_requisitions,'Y',nvl(ccsds.open_requisitions,0),0)
+ decode(l_include_purchase_orders,'Y',nvl(ccsds.open_purchase_orders,0),0)
+ decode(l_include_work_orders,'Y',nvl(ccsds.open_work_orders,0),0)
+ decode(l_include_onhand_good,'Y',nvl(ccsds.onhand_good,0),0)
+ decode(l_include_onhand_bad,'Y',nvl(ccsds.onhand_bad,0),0) quantity_level
, ccsds.onhand_good
, cmsi.total_loop_min_good_quantity
, cmsi.total_loop_quantity
FROM csp_mstrstck_lists_itms cmsi
, csp_curr_sup_dem_sums ccsds
WHERE parts_loop_id = p_parts_loop_id
AND subinventory_code is null
AND ccsds.parts_loop_id = cmsi.parts_loops_id
AND ccsds.inventory_item_id = cmsi.inventory_item_id
AND ccsds.inventory_item_id = nvl(p_inventory_item_id,ccsds.inventory_item_id);*/
SELECT decode(l_include_intransit_mo,'Y',nvl(ccsds.intransit_move_orders,0),0)
+ decode(l_include_interorg_transfers,'Y',nvl(ccsds.open_interorg_transf_in,0)-nvl(ccsds.open_interorg_transf_out,0),0)
+ decode(l_include_sales_orders,'Y',nvl(ccsds.open_sales_orders,0),0)
+ decode(l_include_move_orders,'Y',nvl(ccsds.open_move_orders_in,0)-nvl(ccsds.open_move_orders_out,0),0)
+ decode(l_include_requisitions,'Y',nvl(ccsds.open_requisitions,0),0)
+ decode(l_include_purchase_orders,'Y',nvl(ccsds.open_purchase_orders,0),0)
+ decode(l_include_work_orders,'Y',nvl(ccsds.open_work_orders,0),0)
+ decode(l_include_onhand_good,'Y',nvl(ccsds.onhand_good,0),0)
+ decode(l_include_onhand_bad,'Y',nvl(ccsds.onhand_bad,0),0) quantity_level
, nvl(ccsds.onhand_good,0)
, nvl(cmsi.total_loop_min_good_quantity,0)
, nvl(cmsi.total_loop_quantity,0)
FROM csp_mstrstck_lists_itms cmsi
, csp_curr_sup_dem_sums ccsds
WHERE cmsi.parts_loops_id = p_parts_loop_id
AND ccsds.parts_loop_id(+) = cmsi.parts_loops_id
AND ccsds.subinventory_code(+) is null
AND cmsi.inventory_item_id = p_inventory_item_id
AND ccsds.inventory_item_id(+) = cmsi.inventory_item_id;
SELECT decode(l_include_intransit_mo,'Y',nvl(ccsds.intransit_move_orders,0),0)
+ decode(l_include_interorg_transfers,'Y',nvl(ccsds.interorg_transf_in,0)-nvl(ccsds.interorg_transf_out,0),0)
+ decode(l_include_sales_orders,'Y',nvl(ccsds.sales_orders,0),0)
+ decode(l_include_move_orders,'Y',nvl(ccsds.move_orders_in,0)-nvl(ccsds.move_orders_out,0),0)
+ decode(l_include_requisitions,'Y',nvl(ccsds.requisitions,0),0)
+ decode(l_include_purchase_orders,'Y',nvl(ccsds.purchase_orders,0),0)
+ decode(l_include_work_orders,'Y',nvl(ccsds.work_orders,0),0)
+ decode(l_include_onhand_good,'Y',nvl(ccsds.onhand_good,0),0)
+ decode(l_include_onhand_bad,'Y',nvl(ccsds.onhand_bad,0),0) quantity_level
, nvl(ccsds.onhand_good,0)
, nvl(cmsi.total_loop_min_good_quantity,0)
, nvl(cmsi.total_loop_quantity,0)
FROM csp_mstrstck_lists_itms cmsi
, CSP_SUP_DEM_PL_MV ccsds
WHERE cmsi.parts_loops_id = p_parts_loop_id
AND ccsds.parts_loop_id(+) = cmsi.parts_loops_id
AND cmsi.inventory_item_id = p_inventory_item_id
AND ccsds.inventory_item_id(+) = cmsi.inventory_item_id;
SELECT cplb.parts_loop_id
, cplb.planner_code
, cplb.organization_id
, clcrb.include_sales_orders
, clcrb.include_move_orders
, clcrb.include_work_orders
, clcrb.include_purchase_orders
, clcrb.include_requisitions
, clcrb.include_interorg_transfers
, clcrb.include_onhand_good
, clcrb.include_onhand_bad
, clcrb.include_intransit_move_orders
, clcrb.tolerance_percent
FROM csp_loop_calc_rules_b clcrb
, csp_parts_loops_b cplb
WHERE cplb.calculation_rule_id = clcrb.calculation_rule_id;
SELECT ccsds.inventory_item_id
FROM csp_curr_sup_dem_sums ccsds
WHERE parts_loop_id = c_parts_loop_id
AND subinventory_code is null;*/
SELECT INVENTORY_ITEM_ID inventory_item_id
FROM CSP_MSTRSTCK_LISTS_ITMS
WHERE PARTS_LOOPS_ID = c_parts_loop_id;
SELECT planner_code
FROM csp_mstrstck_lists_itms
WHERE parts_loops_id = c_parts_loop_id
AND inventory_item_id = c_inventory_item_id;
csp_notifications_pkg.insert_row(
px_notification_id => l_notification_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_planner_code => l_planner_code,
p_parts_loop_id => cr.parts_loop_id,
p_organization_id => cr.organization_id,
p_inventory_item_id => curs.inventory_item_id,
p_notification_date => sysdate,
p_reason => 'A',
p_status => '1',
p_quantity => l_quantity_level - l_total_loop_quantity,
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);
csp_notifications_pkg.insert_row(
px_notification_id => l_notification_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_planner_code => l_planner_code,
p_parts_loop_id => cr.parts_loop_id,
p_organization_id => cr.organization_id,
p_inventory_item_id => curs.inventory_item_id,
p_notification_date => sysdate,
p_reason => 'B',
p_status => '1',
p_quantity => l_total_loop_quantity - l_quantity_level,
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);
csp_notifications_pkg.insert_row(
px_notification_id => l_notification_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_planner_code => l_planner_code,
p_parts_loop_id => cr.parts_loop_id,
p_organization_id => cr.organization_id,
p_inventory_item_id => curs.inventory_item_id,
p_notification_date => sysdate,
p_reason => 'N',
p_status => '1',
p_quantity => l_not_enough_good_parts,
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);