[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE run_min_max_plan ( p_item_select IN VARCHAR2
, p_handle_rep_item IN NUMBER
, p_pur_revision IN NUMBER
, p_cat_select IN VARCHAR2
, p_cat_set_id IN NUMBER
, p_mcat_struct IN NUMBER
, p_level IN NUMBER
, p_restock IN NUMBER
, p_include_nonnet IN NUMBER
, p_include_po IN NUMBER
, p_include_mo IN NUMBER DEFAULT 1
, p_include_wip IN NUMBER
, p_include_if IN NUMBER
, p_net_rsv IN NUMBER
, p_net_unrsv IN NUMBER
, p_net_wip IN NUMBER
, p_org_id IN NUMBER
, p_user_id IN NUMBER
, p_employee_id IN NUMBER
, p_subinv IN VARCHAR2
, p_dd_loc_id IN NUMBER
, p_wip_batch_id IN NUMBER
, p_approval IN NUMBER
, p_buyer_hi IN VARCHAR2
, p_buyer_lo IN VARCHAR2
, p_range_buyer IN VARCHAR2
, p_cust_id IN NUMBER
, p_cust_site_id IN NUMBER
, p_po_org_id IN NUMBER
, p_range_sql IN VARCHAR2
, p_sort IN VARCHAR2
, p_selection IN NUMBER
, p_sysdate IN DATE
, p_s_cutoff IN DATE
, p_d_cutoff IN DATE
, p_order_by IN VARCHAR2
, p_encum_flag IN VARCHAR2
, p_cal_code IN VARCHAR2
, p_exception_set_id IN NUMBER
, p_gen_report IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY VARCHAR2
, p_osfm_batch_id IN NUMBER DEFAULT NULL /* Added for Bug 6807835 */
) IS
TYPE c_items_curtype IS REF CURSOR;
/*bug3146742,changed p_item_select to concatenated segmentsto pick the segments with delimiters and table mtl_sytem_items to view mtl_sytem_items_v inorder to get the concatenated segments column*/
/* bug no 6009682 added parallel hints */
sql_stmt1 VARCHAR2(8000) :=
' SELECT /*+ parallel(b) parallel(a) */
c.concatenated_segments item,
c.description description,
c.fixed_lot_multiplier fix_mult,
c.min_minmax_quantity min_qty,
c.max_minmax_quantity max_qty,
c.minimum_order_quantity min_ord_qty,
c.maximum_order_quantity max_ord_qty,
c.fixed_lead_time,
c.variable_lead_time,
NVL(c.postprocessing_lead_time, 0) postprocessing_lead_time,
NVL(c.preprocessing_lead_time, 0) +
NVL(c.full_lead_time, 0) buying_lead_time,
c.planner_code planner,
NULL buyer,
' || p_cat_select || ' category,
b.category_id category_id,
c.inventory_item_id item_id,
c.lot_control_code lot_ctl,
c.repetitive_planning_flag repetitive_planned_item,
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,
build_in_wip_flag build_in_wip,
/* nsinghi MIN-MAX INVCONV start */
NVL(p.process_enabled_flag,''N'') process_enabled,
NVL(c.recipe_enabled_flag,''N'') recipe_enabled,
NVL(c.process_execution_enabled_flag,''N'') execution_enabled,
/* nsinghi MIN-MAX INVCONV end */
pick_components_flag pick_components
FROM mtl_categories b,
mtl_item_categories a,
mtl_system_items_vl c,
mtl_parameters p
WHERE b.category_id = a.category_id
AND b.structure_id = :mcat_struct_id
AND c.inventory_item_flag = ''Y''
AND p.organization_id = :org_id
AND a.organization_id = c.organization_id
AND a.organization_id = :org_id /* bug no 6009682 */
AND c.inventory_planning_code = 2
AND a.category_set_id = :cat_set_id
AND a.inventory_item_id = c.inventory_item_id
AND ( ' || p_range_sql || ' ) ';
/*bug3146742,changed p_item_select to concatenated segmentsto pick the segments with delimiters and table mtl_sytem_items to view mtl_sytem_items_v inorder to get the concatenated segments column*/
sql_stmt2 VARCHAR2(8000) :=
' SELECT c.concatenated_segments item,
c.description description,
s.fixed_lot_multiple fix_mult,
s.min_minmax_quantity min_qty,
s.max_minmax_quantity max_qty,
s.minimum_order_quantity min_ord_qty,
s.maximum_order_quantity max_ord_qty,
c.fixed_lead_time,
c.variable_lead_time,
NVL(c.postprocessing_lead_time, 0) postprocessing_lead_time,
NVL(s.preprocessing_lead_time,
NVL(m.preprocessing_lead_time,
NVL(c.preprocessing_lead_time, 0))) +
NVL(s.processing_lead_time,
NVL(m.processing_lead_time,
NVL(c.full_lead_time, 0))) buying_lead_time,
c.planner_code planner,
NULL,
' || p_cat_select || ',
b.category_id category_id,
c.inventory_item_id item_id,
c.lot_control_code,
c.repetitive_planning_flag repetitive_planned_item,
c.primary_uom_code,
p.ap_accrual_account,
p.invoice_price_var_account,
NVL(s.encumbrance_account,
NVL(m.encumbrance_account,
NVL(c.encumbrance_account, p.encumbrance_account))),
DECODE(c.inventory_asset_flag,
''Y'', m.material_account,
NVL(m.expense_account,
NVL(c.expense_account, p.expense_account))),
NVL(s.source_type,
NVL(m.source_type,
NVL(c.source_type, p.source_type))),
DECODE(s.source_type,
NULL, DECODE(m.source_type,
NULL, DECODE(c.source_type,
NULL, DECODE(p.source_type,
NULL, NULL,
p.source_organization_id),
c.source_organization_id),
m.source_organization_id),
s.source_organization_id),
DECODE(s.source_type,
NULL, DECODE(m.source_type,
NULL, DECODE(c.source_type,
NULL, DECODE(p.source_type,
NULL, NULL,
p.source_subinventory),
c.source_subinventory),
m.source_subinventory),
s.source_subinventory),
c.purchasing_enabled_flag,
c.internal_order_enabled_flag,
c.mtl_transactions_enabled_flag,
c.list_price_per_unit,
c.planning_make_buy_code,
build_in_wip_flag,
/* nsinghi MIN-MAX INVCONV start */
NVL(p.process_enabled_flag,''N'') process_enabled,
NVL(c.recipe_enabled_flag,''N'') recipe_enabled,
NVL(c.process_execution_enabled_flag,''N'') execution_enabled,
/* nsinghi MIN-MAX INVCONV end */
pick_components_flag
FROM mtl_categories b,
mtl_item_categories a,
mtl_system_items_vl c,
mtl_parameters p,
mtl_secondary_inventories m,
mtl_item_sub_inventories s
WHERE b.category_id = a.category_id
AND b.structure_id = :mcat_struct_id
AND c.inventory_item_flag = ''Y''
AND p.organization_id = :org_id
AND a.organization_id = c.organization_id
AND c.organization_id = :org_id
AND c.inventory_item_id = s.inventory_item_id
AND a.category_set_id = :cat_set_id
AND a.inventory_item_id = s.inventory_item_id
AND s.organization_id = :org_id
AND s.inventory_planning_code = 2
AND s.secondary_inventory = :sub
AND m.organization_id = :org_id
AND m.secondary_inventory_name = :sub
AND ( ' || p_range_sql || ' ) ';
/*bug3146742,changed p_item_select to concatenated segmentsto pick the segments with delimiters and table mtl_sytem_items to view mtl_sytem_items_v inorder to get the concatenated segments column*/
sql_stmt3 VARCHAR2(8000) :=
' SELECT c.concatenated_segments,
c.description,
c.fixed_lot_multiplier,
c.min_minmax_quantity,
c.max_minmax_quantity,
c.minimum_order_quantity,
c.maximum_order_quantity,
c.fixed_lead_time,
c.variable_lead_time,
NVL(c.postprocessing_lead_time, 0) postprocessing_lead_time,
NVL(c.preprocessing_lead_time, 0) +
NVL(c.full_lead_time, 0) buying_lead_time,
c.planner_code planner,
SUBSTR(v.full_name, 1, 10),
' || p_cat_select || ',
b.category_id category_id,
c.inventory_item_id,
c.lot_control_code,
c.repetitive_planning_flag repetitive_planned_item,
c.primary_uom_code,
p.ap_accrual_account,
p.invoice_price_var_account,
NVL(c.encumbrance_account, p.encumbrance_account),
decode(c.inventory_asset_flag,
''Y'', p.material_account,
NVL(c.expense_account, p.expense_account)),
NVL(c.source_type, p.source_type),
decode(c.source_type, NULL, decode(p.source_type, NULL, NULL,
p.source_organization_id), c.source_organization_id),
decode(c.source_type, NULL, decode(p.source_type, NULL, NULL,
p.source_subinventory), c.source_subinventory),
c.purchasing_enabled_flag,
c.internal_order_enabled_flag,
c.mtl_transactions_enabled_flag,
c.list_price_per_unit,
c.planning_make_buy_code,
build_in_wip_flag,
/* nsinghi MIN-MAX INVCONV start */
NVL(p.process_enabled_flag,''N'') process_enabled,
NVL(c.recipe_enabled_flag,''N'') recipe_enabled,
NVL(c.process_execution_enabled_flag,''N'') execution_enabled,
/* nsinghi MIN-MAX INVCONV end */
pick_components_flag
FROM mtl_categories b,
mtl_item_categories a,
mtl_system_items_vl c,
mtl_parameters p,
per_all_people_f v
WHERE b.category_id = a.category_id
AND b.structure_id = :mcat_struct_id
AND c.inventory_item_flag = ''Y''
AND p.organization_id = :org_id
AND a.organization_id = c.organization_id
AND c.organization_id = :org_id
AND c.inventory_planning_code = 2
AND a.category_set_id = :cat_set_id
AND a.inventory_item_id = c.inventory_item_id
AND v.person_id (+) = c.buyer_id
AND (
(:l_sysdate between v.effective_start_date and v.effective_end_date)
OR
(v.effective_start_date IS NULL AND v.effective_end_date IS NULL)
)
AND ( ' || p_range_sql || ' )
AND ( ' || p_range_buyer || ' ) ';
/*bug3146742,changed p_item_select to concatenated segmentsto pick the segments with delimiters and table mtl_sytem_items to view mtl_sytem_items_v inorder to get the concatenated segments column*/
sql_stmt4 VARCHAR2(8000) :=
' SELECT c.concatenated_segments,
c.description,
s.fixed_lot_multiple,
s.min_minmax_quantity,
s.max_minmax_quantity,
s.minimum_order_quantity,
s.maximum_order_quantity,
c.fixed_lead_time,
c.variable_lead_time,
NVL(c.postprocessing_lead_time, 0) postprocessing_lead_time,
NVL(s.preprocessing_lead_time,
NVL(m.preprocessing_lead_time,
NVL(c.preprocessing_lead_time, 0))) +
NVL(s.processing_lead_time,
NVL(m.processing_lead_time,
NVL(c.full_lead_time, 0))) buying_lead_time,
c.planner_code planner,
SUBSTR(v.full_name, 1, 10),
' || p_cat_select || ',
b.category_id category_id,
c.inventory_item_id,
c.lot_control_code,
c.repetitive_planning_flag repetitive_planned_item,
c.primary_uom_code,
p.ap_accrual_account,
p.invoice_price_var_account,
NVL(s.encumbrance_account,
NVL(m.encumbrance_account,
NVL(c.encumbrance_account, p.encumbrance_account))),
DECODE(c.inventory_asset_flag,
''Y'', m.material_account,
NVL(m.expense_account,
NVL(c.expense_account, p.expense_account))),
NVL(s.source_type,
NVL(m.source_type,
NVL(c.source_type, p.source_type))),
DECODE(s.source_type,
NULL, DECODE(m.source_type,
NULL, DECODE(c.source_type,
NULL, DECODE(p.source_type,
NULL, NULL,
p.source_organization_id),
c.source_organization_id),
m.source_organization_id),
s.source_organization_id),
DECODE(s.source_type,
NULL, DECODE(m.source_type,
NULL, DECODE(c.source_type,
NULL, DECODE(p.source_type,
NULL, NULL,
p.source_subinventory),
c.source_subinventory),
m.source_subinventory),
s.source_subinventory),
c.purchasing_enabled_flag,
c.internal_order_enabled_flag,
c.mtl_transactions_enabled_flag,
c.list_price_per_unit,
c.planning_make_buy_code,
build_in_wip_flag,
/* nsinghi MIN-MAX INVCONV start */
NVL(p.process_enabled_flag,''N'') process_enabled,
NVL(c.recipe_enabled_flag,''N'') recipe_enabled,
NVL(c.process_execution_enabled_flag,''N'') execution_enabled,
/* nsinghi MIN-MAX INVCONV end */
pick_components_flag
FROM mtl_categories b,
mtl_item_categories a,
mtl_system_items_vl c,
mtl_parameters p,
mtl_secondary_inventories m,
mtl_item_sub_inventories s,
per_all_people_f v
WHERE b.category_id = a.category_id
AND b.structure_id = :mcat_struct_id
AND c.inventory_item_flag = ''Y''
AND p.organization_id = :org_id
AND a.organization_id = c.organization_id
AND c.organization_id = :org_id
AND c.inventory_item_id = s.inventory_item_id
AND a.category_set_id = :cat_set_id
AND a.inventory_item_id = s.inventory_item_id
AND s.organization_id = :org_id
AND s.inventory_planning_code = 2
AND s.secondary_inventory = :sub
AND m.organization_id = :org_id
AND m.secondary_inventory_name = :sub
AND v.person_id (+) = c.buyer_id
AND (
(:l_sysdate between v.effective_start_date and v.effective_end_date)
OR
(v.effective_start_date IS NULL AND v.effective_end_date IS NULL)
)
AND ( ' || p_range_sql || ' )
AND ( ' || p_range_buyer || ' ) ' ;
' p_item_select: ' || p_item_select || fnd_global.local_chr(10) ||
', p_handle_rep_item: ' || to_char(p_handle_rep_item) || fnd_global.local_chr(10) ||
', p_pur_revision: ' || to_char(p_pur_revision) || fnd_global.local_chr(10) ||
', p_cat_select: ' || p_cat_select || fnd_global.local_chr(10) ||
', p_cat_set_id: ' || to_char(p_cat_set_id) || fnd_global.local_chr(10) ||
', p_mcat_struct: ' || to_char(p_mcat_struct) || fnd_global.local_chr(10) ||
', p_level: ' || to_char(p_level) || fnd_global.local_chr(10) ||
', p_restock: ' || to_char(p_restock) || fnd_global.local_chr(10) ||
', p_include_nonnet: ' || to_char(p_include_nonnet) || fnd_global.local_chr(10) ||
', p_include_po: ' || to_char(p_include_po) || fnd_global.local_chr(10) ||
', p_include_mo: ' || to_char(p_include_mo) || fnd_global.local_chr(10) ||
', p_include_wip: ' || to_char(p_include_wip) || fnd_global.local_chr(10) ||
', p_include_if: ' || to_char(p_include_if) || fnd_global.local_chr(10)
, l_proc_name
, 5
);
', p_selection: ' || to_char(p_selection) || fnd_global.local_chr(10) ||
', p_sysdate: ' || to_char(p_sysdate, 'DD-MON-YYYY HH24:MI:SS') ||
fnd_global.local_chr(10) ||
', p_s_cutoff: ' || to_char(p_s_cutoff, 'DD-MON-YYYY HH24:MI:SS') ||
fnd_global.local_chr(10) ||
', p_d_cutoff: ' || to_char(p_d_cutoff, 'DD-MON-YYYY HH24:MI:SS') ||
fnd_global.local_chr(10) ||
', p_order_by: ' || p_order_by || fnd_global.local_chr(10) ||
', p_encum_flag: ' || p_encum_flag || fnd_global.local_chr(10) ||
', p_cal_code: ' || p_cal_code || fnd_global.local_chr(10) ||
', p_exception_set_id: ' || to_char(p_exception_set_id)|| fnd_global.local_chr(10) ||
', p_gen_report: ' || p_gen_report || fnd_global.local_chr(10) ||
', p_osfm_batch_id: ' || p_osfm_batch_id || fnd_global.local_chr(10)
, l_proc_name
, 5
);
SELECT NVL(fnd_profile.value('PO_VMI_ENABLED'),'N')
INTO l_vmi_enabled
FROM dual;
IF (p_selection = 1 AND l_tot_avail_qty < NVL(item_rec.min_qty, 0))
OR
(p_selection = 2 AND l_tot_avail_qty > NVL(item_rec.max_qty, 0))
OR
(p_selection = 3)
THEN
--
--
l_item_segments := SUBSTR(item_rec.item,1,800);
INSERT INTO INV_MIN_MAX_TEMP (
ITEM_SEGMENTS
, DESCRIPTION
, ERROR
, SORTEE
, SUBINVENTORY_CODE
, MIN_QTY
, MAX_QTY
, ONHAND_QTY
, SUPPLY_QTY
, DEMAND_QTY
, TOT_AVAIL_QTY
, MIN_ORD_QTY
, MAX_ORD_QTY
, FIX_MULT
, REORD_QTY)
VALUES ( l_item_segments
, item_rec.description
, l_stat
, l_sortee
, p_subinv
, item_rec.min_qty
, item_rec.max_qty
, l_onhand_qty
, l_supply_qty
, l_demand_qty
, l_tot_avail_qty
, item_rec.min_ord_qty
, item_rec.max_ord_qty
, item_rec.fix_mult
, l_reord_qty);
SELECT concatenated_segments
FROM mtl_categories_kfv
WHERE category_id = p_category_id
AND structure_id = p_struct_id;
' SELECT NVL(sum(to_org_primary_quantity), 0)
FROM mtl_supply sup
, bom_calendar_dates c
, bom_calendar_dates c1
WHERE sup.supply_type_code IN (''PO'',''REQ'',''SHIPMENT'',''RECEIVING'')
AND sup.destination_type_code = ''INVENTORY''
AND sup.to_organization_id = :l_org_id
AND sup.item_id = :l_item_id
AND c.calendar_code = :l_cal_code
AND c.exception_set_id = :l_except_id
AND c.calendar_date = trunc(decode(sup.supply_type_code, ''SHIPMENT'', sup.receipt_date, ''RECEIVING'', sup.receipt_date,nvl(sup.need_by_date, sup.receipt_date)))
AND c1.calendar_code = c.calendar_code
AND c1.exception_set_id = c.exception_set_id
AND c1.seq_num = (c.next_seq_num + trunc(:l_postproc_lead_time))
AND c1.calendar_date <= :l_s_cutoff + 0.99999 /* bug no 6009682 */
AND (NVL(sup.from_organization_id,-1) <> :l_org_id
OR (sup.from_organization_id = :l_org_id
AND ((:l_include_nonnet = 2
AND
EXISTS (SELECT ''x''
FROM mtl_secondary_inventories sub1
WHERE sub1.organization_id = sup.from_organization_id
AND sub1.secondary_inventory_name = sup.from_subinventory
AND sub1.availability_type <> 1
)
)
OR :l_level = 2
)
)
)
AND (sup.to_subinventory IS NULL
OR
(EXISTS (SELECT ''x''
FROM mtl_secondary_inventories sub2
WHERE sub2.secondary_inventory_name = sup.to_subinventory
AND sub2.organization_id = sup.to_organization_id
AND sub2.availability_type = decode(:l_include_nonnet,
1,sub2.availability_type,
1)
)
)
OR :l_level = 2
)
AND (:l_level = 1 OR to_subinventory = :l_subinv)
-- Bug 5041763 Not considering supply from drop ship orders
AND NOT EXISTS (SELECT ''X'' FROM OE_DROP_SHIP_SOURCES ODSS
WHERE DECODE(sup.PO_HEADER_ID, NULL, sup.REQ_LINE_ID, sup.PO_LINE_LOCATION_ID) =
DECODE(sup.PO_HEADER_ID,NULL, ODSS.REQUISITION_LINE_ID, ODSS.LINE_LOCATION_ID)) ';
OR EXISTS (SELECT ''x''
FROM po_line_locations_all lilo
WHERE lilo.line_location_id = sup.po_line_location_id
AND NVL(lilo.vmi_flag,''N'') = ''N''
)
)
AND (sup.req_line_id IS NULL
OR EXISTS (SELECT ''x''
FROM po_requisition_lines_all prl
WHERE prl.requisition_line_id = sup.req_line_id
AND NVL(prl.vmi_flag,''N'') = ''N''
)
)';
/* SELECT NVL(sum(mtrl.quantity - NVL(mtrl.quantity_delivered,0)),0)
INTO l_qty
FROM mtl_transaction_types mtt,
mtl_txn_request_lines mtrl
WHERE mtt.transaction_action_id IN (2,28)
AND mtt.transaction_type_id = mtrl.transaction_type_id
AND mtrl.organization_id = p_org_id
AND mtrl.inventory_item_id = p_item_id
AND mtrl.to_subinventory_code = p_subinv
AND mtrl.line_status NOT IN (5,6)
AND mtrl.date_required <= p_s_cutoff;*/
SELECT NVL(SUM(inv_decimals_pub.get_primary_quantity( p_org_id
,p_item_id
, mtrl.uom_code
, mtrl.quantity - NVL(mtrl.quantity_delivered,0))
),0)
INTO l_qty
FROM mtl_transaction_types mtt,
mtl_txn_request_lines mtrl
WHERE mtt.transaction_action_id IN (2,28)
AND mtt.transaction_type_id = mtrl.transaction_type_id
AND mtrl.organization_id = p_org_id
AND mtrl.inventory_item_id = p_item_id
AND mtrl.to_subinventory_code = p_subinv
AND mtrl.line_status IN (3,7) --Changed for Bug 5330189: 3 = Approved 7 = Pre-Approved
AND mtrl.date_required <= p_s_cutoff + 0.99999; /* bug no 6009682 */
SELECT
SUM ( NVL((NVL(d.wip_plan_qty, d.plan_qty) - d.actual_qty), 0) *
(original_primary_qty/original_qty))
INTO l_qty
FROM gme_material_details d
, gme_batch_header h
WHERE h.batch_type IN (0,10)
AND h.batch_status IN (1,2)
AND h.batch_id = d.batch_id
AND d.inventory_item_id = p_item_id
AND d.organization_id = p_org_id
AND NVL(d.original_qty, 0) <> 0
AND d.material_requirement_date <= p_s_cutoff
AND d.line_type > 0;
SELECT sum(NVL(start_quantity,0)
- NVL(quantity_completed,0)
- NVL(quantity_scrapped,0))
INTO l_qty
FROM wip_discrete_jobs
WHERE organization_id = p_org_id
AND primary_item_id = p_item_id
AND job_type in (1,3)
AND status_type IN (1,3,4,6)
--Bug 2647862
AND scheduled_completion_date <= p_s_cutoff + 0.99999 /* bug no 6009682 */
AND (NVL(start_quantity,0) - NVL(quantity_completed,0)
- NVL(quantity_scrapped,0)) > 0;
SELECT SUM(daily_production_rate *
GREATEST(0, LEAST(processing_work_days,
p_s_cutoff - first_unit_completion_date
)
)
- quantity_completed)
INTO l_qty
FROM wip_repetitive_schedules wrs,
wip_repetitive_items wri
WHERE wrs.organization_id = p_org_id
AND wrs.status_type IN (1,3,4,6)
AND wri.organization_id = p_org_id
AND wri.primary_item_id = p_item_id
AND wri.wip_entity_id = wrs.wip_entity_id
AND wri.line_id = wrs.line_id
AND (daily_production_rate *
GREATEST(0, LEAST(processing_work_days,
p_s_cutoff - first_unit_completion_date
)
)
- quantity_completed) > 0;
SELECT uom_code
INTO l_puom
FROM mtl_system_items_vl msiv , mtl_units_of_measure_vl muom
WHERE msiv.inventory_item_id = p_item_id
AND msiv.organization_id = p_org_id
AND muom.unit_of_measure = msiv.primary_unit_of_measure;
SELECT NVL(SUM(DECODE(NVL(uom_code,get_item_uom_code(unit_of_measure)),
l_puom,quantity,
INV_CONVERT.INV_UM_CONVERT(p_item_id,null,quantity,NVL(uom_code,get_item_uom_code(unit_of_measure)),l_puom,null,null)
)),0)
INTO l_qty
FROM po_requisitions_interface_all
WHERE destination_organization_id = p_org_id
AND item_id = p_item_id
AND p_include_po = 1
AND (p_level = 1 or destination_subinventory = p_subinv)
AND need_by_date <= (trunc(p_s_cutoff) + 1 - (1/(24*60*60)))
AND NVL(process_flag,'@@@') <> 'ERROR'
AND (NVL(source_organization_id,-1) <> p_org_id OR
(source_organization_id = p_org_id AND
(( p_include_nonnet = 2 AND
EXISTS (SELECT 'x'
FROM mtl_secondary_inventories sub1
WHERE sub1.organization_id = source_organization_id
AND sub1.secondary_inventory_name = source_subinventory
AND sub1.availability_type <> 1)) OR
p_level = 2)
))
AND (destination_subinventory IS NULL OR
EXISTS (SELECT 1
FROM mtl_secondary_inventories sub2
WHERE secondary_inventory_name = destination_subinventory
AND destination_subinventory = NVL(p_subinv,
destination_subinventory)
AND sub2.organization_id = p_org_id
AND sub2.availability_type = decode(p_include_nonnet,
1,sub2.availability_type,1)) OR
p_level = 2);
SELECT SUM(NVL(planned_quantity,0)
- NVL(quantity_completed,0))
INTO l_qty
FROM wip_flow_schedules
WHERE organization_id = p_org_id
AND primary_item_id = p_item_id
AND status = 1
AND scheduled_flag = 1 -- Bug 3151797
--Bug 2647862
AND scheduled_completion_date <= p_s_cutoff + 0.99999 /* bug no 6009682 */
AND (NVL(planned_quantity,0)
- NVL(quantity_completed,0)) > 0;
select sum(PRIMARY_UOM_QUANTITY - GREATEST(NVL(RESERVATION_QUANTITY,0),
NVL(COMPLETED_QUANTITY,0)))
into qty
from mtl_demand
WHERE RESERVATION_TYPE = 1
AND parent_demand_id IS NULL
AND ORGANIZATION_ID = p_org_id
and PRIMARY_UOM_QUANTITY > GREATEST(NVL(RESERVATION_QUANTITY,0),
NVL(COMPLETED_QUANTITY,0))
and INVENTORY_ITEM_ID = p_item_id
and REQUIREMENT_DATE <= p_d_cutoff + 0.99999 /* bug no 6009682 */
and demand_source_type not in (2,8,12)
and (p_level = 1 or
SUBINVENTORY = p_subinv) -- Included later for ORG Level
and (SUBINVENTORY is null or
p_level = 2 or
EXISTS (SELECT 1
FROM MTL_SECONDARY_INVENTORIES S
WHERE S.ORGANIZATION_ID = p_org_id
AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY
AND S.availability_type = DECODE(p_include_nonnet,
1,
S.availability_type,
1)))
/* nsinghi MIN-MAX INVCONV start */
AND (locator_id IS NULL OR
p_level = 2 OR
EXISTS (SELECT 1 FROM mtl_item_locations mil
WHERE mil.organization_id = p_org_id
AND mil.inventory_location_id = locator_id
AND mil.subinventory_code = NVL(subinventory, mil.subinventory_code)
AND mil.availability_type = decode(p_include_nonnet,1,mil.availability_type,1)))
AND (lot_number IS NULL OR
p_level = 2 OR
EXISTS (SELECT 1 FROM mtl_lot_numbers mln
WHERE mln.organization_id = p_org_id
AND mln.lot_number = lot_number
AND mln.inventory_item_id = p_item_id
AND mln.availability_type = decode(p_include_nonnet,1,mln.availability_type,1)));
select sum(PRIMARY_RESERVATION_QUANTITY)
into qty
from mtl_reservations
where ORGANIZATION_ID = p_org_id
and INVENTORY_ITEM_ID = p_item_id
and REQUIREMENT_DATE <= p_d_cutoff + 0.99999 /* bug no 6009682 */
and demand_source_type_id not in (2,8,12)
and (p_level = 1 or
SUBINVENTORY_CODE = p_subinv) -- Included later for ORG Level
and (SUBINVENTORY_CODE is null or
p_level = 2 or
EXISTS (SELECT 1
FROM MTL_SECONDARY_INVENTORIES S
WHERE S.ORGANIZATION_ID = p_org_id
AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY_CODE
AND S.availability_type = DECODE(p_include_nonnet,
1,
S.availability_type,
1)))
/* nsinghi MIN-MAX INVCONV start */
AND (locator_id IS NULL OR
p_level = 2 OR
EXISTS (SELECT 1 FROM mtl_item_locations mil
WHERE mil.organization_id = p_org_id
AND mil.inventory_location_id = locator_id
AND mil.subinventory_code = NVL(subinventory_code, mil.subinventory_code)
AND mil.availability_type = decode(p_include_nonnet,1,mil.availability_type,1)))
AND (lot_number IS NULL OR
p_level = 2 OR
EXISTS (SELECT 1 FROM mtl_lot_numbers mln
WHERE mln.organization_id = p_org_id
AND mln.lot_number = lot_number
AND mln.inventory_item_id = p_item_id
AND mln.availability_type = decode(p_include_nonnet,1,mln.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_org_id, p_item_id, ool.line_id)),
SUM(DECODE(p_level,
2, get_staged_qty( p_org_id
, p_subinv
, p_item_id
, ool.line_id
, p_include_nonnet),
0)
),
SUM(DECODE(p_level,
2, get_pick_released_qty( p_org_id
, p_subinv
, p_item_id
, ool.line_id),
0)
)
into l_total_demand_qty, l_staged_qty, l_pick_released_qty
from oe_order_lines_all ool
where ship_from_org_id = p_org_id
and open_flag = 'Y'
AND visible_demand_flag = 'Y'
AND shipped_quantity is null
and INVENTORY_ITEM_ID = p_item_id
and schedule_ship_date <= p_d_cutoff + 0.99999 /* bug no 6009682 */
AND DECODE( OOL.SOURCE_DOCUMENT_TYPE_ID
, 10, 8
, DECODE(OOL.LINE_CATEGORY_CODE, 'ORDER',2,12)) IN (2,8,12)
and ((p_level = 1
AND DECODE( OOL.SOURCE_DOCUMENT_TYPE_ID
, 10, 8
, DECODE(OOL.LINE_CATEGORY_CODE, 'ORDER',2,12)) <> 8)
OR SUBINVENTORY = p_subinv) -- Included later for ORG Level
and (SUBINVENTORY is null or
p_level = 2 or
EXISTS (SELECT 1
FROM MTL_SECONDARY_INVENTORIES S
WHERE S.ORGANIZATION_ID = p_org_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_org_id
and INVENTORY_ITEM_ID = p_item_id
and REQUIREMENT_DATE <= p_d_cutoff + 0.99999 /* bug no 6009682 */
and demand_source_type_id in (2,8,12)
and ((p_level = 1 AND demand_source_type_id <> 8) OR
SUBINVENTORY_CODE = p_subinv) -- Included later for ORG Level
and (SUBINVENTORY_CODE is null or
p_level = 2 or
EXISTS (SELECT 1
FROM MTL_SECONDARY_INVENTORIES S
WHERE S.ORGANIZATION_ID = p_org_id
AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY_CODE
AND S.availability_type = DECODE(p_include_nonnet,
1,
S.availability_type,
1)))
/* nsinghi MIN-MAX INVCONV start */
AND (locator_id IS NULL OR
p_level = 2 OR
EXISTS (SELECT 1 FROM mtl_item_locations mil
WHERE mil.organization_id = p_org_id
AND mil.inventory_location_id = locator_id
AND mil.subinventory_code = NVL(subinventory_code, mil.subinventory_code)
AND mil.availability_type = decode(p_include_nonnet,1,mil.availability_type,1)))
AND (lot_number IS NULL OR
p_level = 2 OR
EXISTS (SELECT 1 FROM mtl_lot_numbers mln
WHERE mln.organization_id = p_org_id
AND mln.lot_number = lot_number
AND mln.inventory_item_id = p_item_id
AND mln.availability_type = decode(p_include_nonnet,1,mln.availability_type,1)))
-- Bug 5041763 excluding drop ship demand
and NOT EXISTS (SELECT 1
FROM OE_DROP_SHIP_SOURCES ODSS
WHERE ODSS.LINE_ID = DEMAND_SOURCE_LINE_ID);
select sum(mr.PRIMARY_RESERVATION_QUANTITY) into l_sub_reserve_qty
from mtl_reservations mr, oe_order_lines_all ool
where mr.organization_id = p_org_id
AND mr.inventory_item_id = p_item_id
AND mr.demand_source_line_id = ool.line_id
AND mr.demand_source_type_id in (2,8,12)
AND ool.subinventory is NULL
AND ool.open_flag = 'Y'
AND ool.visible_demand_flag = 'Y'
AND ool.shipped_quantity is null
AND mr.REQUIREMENT_DATE <= p_d_cutoff + 0.99999 /* bug no 6009682 */
AND mr.subinventory_code IS NOT NULL
AND mr.subinventory_code = p_subinv;
SELECT NVL(SUM(primary_quantity),0)
INTO l_allocated_qty
FROM mtl_material_transactions_temp mmtt
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id
AND subinventory_code = p_subinv
AND transfer_subinventory <> p_subinv
AND NVL(transaction_status, 1) = 2
AND transaction_source_type_id in (2,8)
AND not exists (SELECT 1 from mtl_reservations
WHERE reservation_id = mmtt.reservation_id
AND nvl(subinventory_code, '@@@') = p_subinv)
AND exists (SELECT 1 from mtl_txn_request_lines
WHERE line_id = mmtt.move_order_line_id
AND from_subinventory_code is null
AND line_status NOT IN (5,6)
AND date_required <= p_d_cutoff + 0.99999); /* bug no 6009682 */
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_org_id, p_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.SOURCE_DOCUMENT_ID = pol.requisition_header_id
-- and poh.requisition_header_id = pol.requisition_header_id
and so.source_document_line_id = pol.requisition_line_id
and (pol.DESTINATION_ORGANIZATION_ID <> p_org_id or
(pol.DESTINATION_ORGANIZATION_ID = p_org_id and -- Added code Bug#1012179
( pol.DESTINATION_TYPE_CODE = 'EXPENSE' OR --Bug#3619239 started
-- Bug 3619239 The functionality is added so that demand from Internal Sales Requisitions are taken
-- into consideration if Destination Type is Inventory and Destination Subinventory is Non Quantity Tracked
( pol.DESTINATION_TYPE_CODE = 'INVENTORY'
AND pol.DESTINATION_SUBINVENTORY IS NOT NULL
AND EXISTS (select 1 from
MTL_SECONDARY_INVENTORIES
where SECONDARY_INVENTORY_NAME = pol.DESTINATION_SUBINVENTORY
and ORGANIZATION_ID = pol.DESTINATION_ORGANIZATION_ID
and QUANTITY_TRACKED = 2)
)
)-- Bug#3619239 ended
)
)
and so.ship_from_org_ID = p_org_id
and so.open_flag = 'Y'
AND so.visible_demand_flag = 'Y'
AND shipped_quantity is null
and so.INVENTORY_ITEM_ID = p_item_id
and schedule_ship_date <= p_d_cutoff + 0.99999 /* bug no 6009682 */
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_org_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, Bug 5934651
po_requisition_lines_all pol
where md.DEMAND_SOURCE_LINE_ID = so.LINE_ID
-- and to_number(so.ORIG_SYS_LINE_REF) = pod.DISTRIBUTION_ID --Bug#2883172
and so.SOURCE_DOCUMENT_ID = pol.requisition_header_id -- Bug 5934651
and so.source_document_line_id = pol.requisition_line_id
-- and pod.REQUISITION_LINE_ID = pol.REQUISITION_LINE_ID
and (pol.DESTINATION_ORGANIZATION_ID <> p_org_id or
(pol.DESTINATION_ORGANIZATION_ID = p_org_id
and -- Added code Bug#1012179
( pol.DESTINATION_TYPE_CODE = 'EXPENSE' OR -- Bug#3619239 started
-- Bug 3619239 The functionality is added so that demand from Internal Sales Requisitions are taken
-- into consideration if Destination Type is Inventory and Destination Subinventory is Non Quantity Tracked
( pol.DESTINATION_TYPE_CODE = 'INVENTORY'
AND pol.DESTINATION_SUBINVENTORY IS NOT NULL
AND EXISTS (select 1 from
MTL_SECONDARY_INVENTORIES
where SECONDARY_INVENTORY_NAME = pol.DESTINATION_SUBINVENTORY
and ORGANIZATION_ID = pol.DESTINATION_ORGANIZATION_ID
and QUANTITY_TRACKED = 2)
)
)-- Bug#3619239 ended
)
)
and ORGANIZATION_ID = p_org_id
and md.INVENTORY_ITEM_ID = p_item_id
and REQUIREMENT_DATE <= p_d_cutoff + 0.99999 /* bug no 6009682 */
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_org_id
AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY_CODE
AND S.availability_type = DECODE(p_include_nonnet,
1,
S.availability_type,
1)))
/* nsinghi MIN-MAX INVCONV start */
AND (md.locator_id IS NULL OR
p_level = 2 OR
EXISTS (SELECT 1 FROM mtl_item_locations mil
WHERE mil.organization_id = p_org_id
AND mil.inventory_location_id = md.locator_id
AND mil.subinventory_code = NVL(md.subinventory_code, mil.subinventory_code)
AND mil.availability_type = decode(p_include_nonnet,1,mil.availability_type,1)))
AND (md.lot_number IS NULL OR
p_level = 2 OR
EXISTS (SELECT 1 FROM mtl_lot_numbers mln
WHERE mln.organization_id = p_org_id
AND mln.lot_number = md.lot_number
AND mln.inventory_item_id = p_item_id
AND mln.availability_type = decode(p_include_nonnet,1,mln.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_org_id
and PRIMARY_UOM_QUANTITY >
GREATEST(NVL(RESERVATION_QUANTITY,0), NVL(COMPLETED_QUANTITY,0))
and INVENTORY_ITEM_ID = p_item_id
and REQUIREMENT_DATE <= p_d_cutoff
and p_net_rsv = 1;
SELECT
SUM (INV_DECIMALS_PUB.GET_PRIMARY_QUANTITY( p_org_id
, p_item_id
, d.dtl_um
, NVL(NVL(d.wip_plan_qty, d.plan_qty) - d.actual_qty, 0))-
NVL(mtr.primary_reservation_quantity,0))
INTO qty
FROM gme_material_details d
, gme_batch_header h
, mtl_reservations mtr
WHERE h.batch_type IN (0,10)
AND h.batch_status IN (1,2)
AND h.batch_id = d.batch_id
AND d.line_type = -1
-- AND NVL(d.original_qty, 0) <> 0 --commented as part of bug 8434499
AND d.organization_id = p_org_id
AND d.inventory_item_id = p_item_id
AND d.batch_id = mtr.demand_source_header_id (+)
AND d.material_detail_id = mtr.demand_source_line_id (+)
AND d.inventory_item_id = mtr.inventory_item_id (+)
AND d.organization_id = mtr.organization_id (+)
AND (INV_DECIMALS_PUB.GET_PRIMARY_QUANTITY( p_org_id
, p_item_id
, d.dtl_um
, NVL(NVL(d.wip_plan_qty, d.plan_qty) - d.actual_qty, 0))-
NVL(mtr.primary_reservation_quantity,0)) > 0
AND NVL(mtr.demand_source_type_id, 5) = 5
AND d.material_requirement_date <= p_d_cutoff
AND (mtr.subinventory_code IS NULL OR
EXISTS (SELECT 1
FROM mtl_secondary_inventories s
WHERE s.organization_id = p_org_id
AND s.secondary_inventory_name = mtr.subinventory_code
AND s.availability_type = DECODE(p_include_nonnet,1,s.availability_type,1)))
AND (mtr.locator_id IS NULL OR
EXISTS (SELECT 1 FROM mtl_item_locations mil
WHERE mil.organization_id = p_org_id
AND mil.inventory_location_id = mtr.locator_id
AND mil.subinventory_code = NVL(mtr.subinventory_code, mil.subinventory_code)
AND mil.availability_type = DECODE(p_include_nonnet,1,mil.availability_type,1)))
AND (mtr.lot_number IS NULL OR
EXISTS (SELECT 1 FROM mtl_lot_numbers mln
WHERE mln.organization_id = p_org_id
AND mln.lot_number = mtr.lot_number
AND mln.inventory_item_id = p_item_id
AND mln.availability_type = DECODE(p_include_nonnet,1,mln.availability_type,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_org_id
and o.inventory_item_id = p_item_id
and o.date_required <= p_d_cutoff + 0.99999 /* bug no 6009682 */
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 not in (5,6); -- Included 5 from the selection Bug#4488415
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.repetitive_schedule_id = r.repetitive_schedule_id
and o.organization_id = r.organization_id
and r.organization_id = p_org_id
and o.inventory_item_id = p_item_id
and o.date_required <= p_d_cutoff + 0.99999 /* bug no 6009682 */
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 not in (5,6); -- Included 5 from the selection Bug#4488415
/*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.TRANSACTION_TYPE_ID <> INV_Globals.G_TYPE_XFER_ORDER_WIP_ISSUE
AND MTRL.ORGANIZATION_ID = p_org_id
AND MTRL.INVENTORY_ITEM_ID = p_item_id
AND MTRL.LINE_STATUS NOT IN (5,6)
AND MTT.TRANSACTION_ACTION_ID = 1
AND (p_level = 1 OR
MTRL.FROM_SUBINVENTORY_CODE = p_subinv)
AND (MTRL.FROM_SUBINVENTORY_CODE IS NULL OR
p_level = 2 OR
EXISTS (SELECT 1
FROM MTL_SECONDARY_INVENTORIES S
WHERE S.ORGANIZATION_ID = p_org_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 <= p_d_cutoff;*/
SELECT NVL(SUM(inv_decimals_pub.get_primary_quantity( p_org_id
,p_item_id
, mtrl.uom_code
, mtrl.quantity - NVL(mtrl.quantity_delivered,0))
),0)
INTO qty
FROM MTL_TXN_REQUEST_LINES MTRL,
MTL_TRANSACTION_TYPES MTT
WHERE MTT.TRANSACTION_TYPE_ID = MTRL.TRANSACTION_TYPE_ID
AND MTRL.TRANSACTION_TYPE_ID <> INV_Globals.G_TYPE_XFER_ORDER_WIP_ISSUE
AND MTRL.ORGANIZATION_ID = p_org_id
AND MTRL.INVENTORY_ITEM_ID = p_item_id
AND MTRL.LINE_STATUS IN (3,7)--Changed for Bug 5330189: 3 = Approved 7 = Pre-Approved
AND MTT.TRANSACTION_ACTION_ID = 1
AND (p_level = 1 OR
MTRL.FROM_SUBINVENTORY_CODE = p_subinv)
AND (MTRL.FROM_SUBINVENTORY_CODE IS NULL OR
p_level = 2 OR
EXISTS (SELECT 1
FROM MTL_SECONDARY_INVENTORIES S
WHERE S.ORGANIZATION_ID = p_org_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 <= p_d_cutoff + 0.99999 /* bug no 6009682 */
/* nsinghi MIN-MAX INVCONV start */
AND (mtrl.from_locator_id IS NULL OR
p_level = 2 OR
EXISTS (SELECT 1 FROM mtl_item_locations mil
WHERE mil.organization_id = p_org_id
AND mil.inventory_location_id = mtrl.from_locator_id
AND mil.subinventory_code = NVL(mtrl.from_subinventory_code, mil.subinventory_code)
AND mil.availability_type = decode(p_include_nonnet,1,mil.availability_type,1)))
AND (mtrl.lot_number IS NULL OR
p_level = 2 OR
EXISTS (SELECT 1 FROM mtl_lot_numbers mln
WHERE mln.organization_id = p_org_id
AND mln.lot_number = mtrl.lot_number
AND mln.inventory_item_id = p_item_id
AND mln.availability_type = decode(p_include_nonnet,1,mln.availability_type,1)));
/*SELECT NVL(sum(mtrl.quantity - NVL(mtrl.quantity_delivered,0)),0)
INTO qty
FROM mtl_transaction_types mtt,
mtl_txn_request_lines mtrl
WHERE mtt.transaction_action_id IN (2,28)
AND mtt.transaction_type_id = mtrl.transaction_type_id
AND mtrl.organization_id = p_org_id
AND mtrl.inventory_item_id = p_item_id
AND mtrl.from_subinventory_code = p_subinv
AND mtrl.line_status NOT IN (5,6)
AND mtrl.date_required <= p_d_cutoff;*/
SELECT NVL(SUM(inv_decimals_pub.get_primary_quantity( p_org_id
,p_item_id
,mtrl.uom_code
, mtrl.quantity - NVL(mtrl.quantity_delivered,0))
),0)
INTO qty
FROM mtl_transaction_types mtt,
mtl_txn_request_lines mtrl
WHERE mtt.transaction_action_id IN (2,28)
AND mtt.transaction_type_id = mtrl.transaction_type_id
AND mtrl.organization_id = p_org_id
AND mtrl.inventory_item_id = p_item_id
AND mtrl.from_subinventory_code = p_subinv
AND MTRL.LINE_STATUS IN (3,7) --Changed for Bug 5330189: 3 = Approved 7 = Pre-Approved
AND mtrl.date_required <= p_d_cutoff + 0.99999; /* bug no 6009682 */
select sum(PRIMARY_RESERVATION_QUANTITY)
into qty
from mtl_reservations
WHERE ORGANIZATION_ID = p_org_id
and INVENTORY_ITEM_ID = p_item_id
and demand_source_type_id = 2
and supply_source_type_id = 13
and REQUIREMENT_DATE <= p_d_cutoff + 0.99999 /* bug no 6009682 */
and ((p_level = 1 ) OR
SUBINVENTORY_CODE = p_subinv)
and ( SUBINVENTORY_CODE is null or
p_level = 2 or
EXISTS (SELECT 1
FROM MTL_SECONDARY_INVENTORIES S
WHERE S.ORGANIZATION_ID = p_org_id
AND S.SECONDARY_INVENTORY_NAME = SUBINVENTORY_CODE
AND S.availability_type = DECODE(p_include_nonnet,
1,
S.availability_type,
1)))
and EXISTS (SELECT 1
FROM OE_DROP_SHIP_SOURCES ODSS
WHERE ODSS.LINE_ID = DEMAND_SOURCE_LINE_ID);
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
AND transaction_source_type_id in (2,8);
SELECT NVL(SUM(primary_reservation_quantity),0)
INTO l_staged_qty
FROM mtl_reservations
WHERE organization_id = p_org_id
AND inventory_item_id = p_item_id
AND demand_source_line_id = p_order_line_id
AND demand_source_type_id IN (2,8,12)
AND NVL(staged_flag, 'X') = 'Y'
AND subinventory_code IS NOT NULL
AND subinventory_code <> p_subinv; -- Bug 4313204
SELECT NVL(sum(mtrl.quantity - NVL(mtrl.quantity_delivered,0)),0)
INTO l_pick_released_qty
FROM mtl_txn_request_headers mtrh,
mtl_txn_request_lines mtrl
WHERE mtrh.move_order_type = 3
AND mtrh.header_id = mtrl.header_id
AND mtrl.organization_id = p_org_id
AND mtrl.inventory_item_id = p_item_id
AND mtrl.from_subinventory_code = p_subinv
AND mtrl.txn_source_line_id = p_order_line_id
AND mtrl.transaction_source_type_id in (2,8)
AND mtrl.line_status NOT IN (5,6);
SELECT meaning
INTO l_error_message
FROM mfg_lookups
WHERE lookup_type = 'INV_MMX_RPT_MSGS'
AND lookup_code = 7;
SELECT meaning
INTO l_error_message
FROM mfg_lookups
WHERE lookup_type = 'INV_MMX_RPT_MSGS'
AND lookup_code = 6;
SELECT p.calendar_code, p.calendar_exception_set_id
INTO l_so_cal_code, l_so_exception_set_id
FROM mtl_parameters p
WHERE p.organization_id = l_src_org;
SELECT c1.calendar_date
INTO x_scheduled_ship_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
AND c.calendar_code = l_so_cal_code
AND c.exception_set_id = l_so_exception_set_id
AND c.calendar_date = trunc(p_sourcing_date);
SELECT LOCATION_ID
INTO l_from_location_id
FROM MTL_SECONDARY_INVENTORIES
WHERE SECONDARY_INVENTORY_NAME = l_src_subinv
AND ORGANIZATION_ID = l_src_org ;
SELECT LOC.LOCATION_ID
INTO l_from_location_id
FROM HR_ORGANIZATION_UNITS ORG,HR_LOCATIONS LOC
WHERE ORG.ORGANIZATION_ID = l_src_org
AND ORG.LOCATION_ID = LOC.LOCATION_ID;
SELECT MRP_ATP_SCHEDULE_TEMP_S.NEXTVAL
INTO l_session_id
FROM SYS.DUAL;
SELECT NVL(process_enabled_flag,'N') INTO l_process_enabled
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT NVL(recipe_enabled_flag, 'N'), NVL(process_execution_enabled_flag, 'N')
INTO l_recipe_enabled, l_execution_enabled
FROM mtl_system_items
WHERE organization_id = p_organization_id
AND inventory_item_id = p_item_id;
select nvl(location_id,0) into l_sub_loc_id from mtl_secondary_inventories
where secondary_inventory_name = p_subinv and organization_id = p_organization_id;
SELECT asset_inventory,expense_account INTO l_asset_flag, l_exp_acct FROM mtl_secondary_inventories
WHERE secondary_inventory_name = p_subinv and organization_id = p_organization_id;
SELECT c1.calendar_date
INTO l_sourcing_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 + trunc(p_buying_lead_time))
AND c.calendar_code = p_cal_code
AND c.exception_set_id = p_except_id
AND c.calendar_date = trunc(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)
AND c.calendar_code = p_cal_code
AND c.exception_set_id = p_except_id
AND c.calendar_date = trunc(l_need_by_date);
l_trolin_tbl(l_order_count).last_updated_by := p_user_id;
l_trolin_tbl(l_order_count).last_update_date := sysdate;
l_trolin_tbl(l_order_count).last_update_login := p_user_id;
fnd_msg_pub.delete_msg(i);
SELECT meaning
INTO x_ret_mesg
FROM mfg_lookups
WHERE lookup_type = 'INV_MMX_RPT_MSGS'
AND lookup_code = 1;
SELECT meaning
INTO x_ret_mesg
FROM mfg_lookups
WHERE lookup_type = 'INV_MMX_RPT_MSGS'
AND lookup_code = 5;
SELECT revision_qty_control_code
INTO l_item_rev_ctl
FROM mtl_system_items msi
WHERE msi.organization_id = l_orgn_id
AND msi.inventory_item_id = p_item_id;
SELECT MAX(revision)
INTO l_item_revision
FROM mtl_item_revisions mir
WHERE inventory_item_id = p_item_id
AND organization_id = l_orgn_id
AND effectivity_date < SYSDATE
AND implementation_date is not null /* Added for Bug 7110794 */
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 implementation_date is not null /* Added for Bug 7110794 */
AND effectivity_date < SYSDATE
);
select uom_code
into l_unit_of_issue
from mtl_system_items_vl msiv , mtl_units_of_measure_vl muom
where msiv.inventory_item_id = p_item_id
and msiv.organization_id = p_organization_id
and muom.unit_of_measure = NVL(msiv.unit_of_issue,msiv.primary_unit_of_measure);
print_debug('Inserting into PO_REQUISITIONS_INTERFACE_ALL', 're_po', 9);
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,
p_user_id,
p_description,
sysdate,
p_user_id,
p_employee_id,
'INV',
DECODE(p_src_type, 1, 'INTERNAL', 'PURCHASE'),
DECODE(p_approval, 1, 'APPROVED','INCOMPLETE'),
DECODE(p_src_type, 1, 'INVENTORY', 'VENDOR'),
p_src_org,
p_src_subinv,
p_organization_id,
p_subinv,
p_employee_id,
'INVENTORY',
DECODE(l_check_uom,1,l_unit_of_issue,p_uom), -- Bug 3894347
p_location_id,
p_item_id,
DECODE(l_item_revision,'@@@',NULL,l_item_revision),
DECODE(l_check_uom,1,l_qty_conv,p_qty), -- Bug 3894347
(trunc(p_nb_time) + 1 - (1/(24*60*60))),
SYSDATE,
p_charge_acct,
p_accru_acct,
p_ipv_acct,
p_budget_acct,
'P',
p_po_org_id);
SELECT meaning
INTO x_ret_mesg
FROM mfg_lookups
WHERE lookup_type = 'INV_MMX_RPT_MSGS'
AND lookup_code = 1;
select wsm_lot_sm_ifc_header_s.nextval
into l_header_id
from dual;
/*Bug 15837698 Moved this part just before inserting into wsm_lot_job_interface*/
/* select FND_Profile.value('WIP_JOB_PREFIX')||wip_job_number_s.nextval
INTO l_job_name
from dual;
select nvl(cfm_routing_flag,0) into l_cfm_flag
from BOM_OPERATIONAL_ROUTINGS
where assembly_item_id = p_item_id
AND organization_id = p_organization_id
AND alternate_routing_designator is NULL;
SELECT 'Y' INTO l_is_lot_control
FROM dual
WHERE exists
(SELECT 1 FROM mtl_system_items
WHERE organization_id = p_organization_id
AND inventory_item_id = p_item_id
AND lot_control_code = 2);
SELECT WSM_LOT_JOB_INTERFACE_S.NEXTVAL
INTO l_osfm_batch_id
FROM SYS.DUAL;
before inserting data into WSM_LOT_JOB_INTERFACE */
select FND_Profile.value('WIP_JOB_PREFIX')||wip_job_number_s.nextval
INTO l_job_name
from dual;
INSERT INTO WSM_LOT_JOB_INTERFACE (
mode_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
group_id,
source_line_id,
organization_id,
load_type,
status_type,
primary_item_id,
job_name,
start_Quantity,
process_Status,
first_unit_start_date,
last_unit_completion_date,
scheduling_method,
completion_subinventory,
completion_locator_id,
class_code,
description,
bom_revision_date,
routing_revision_date,
header_id)
VALUES (
1,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
l_osfm_batch_id,
Decode(l_mode_flag, 1,null,l_header_id),
p_organization_id,
5, --job creation
3, --1:unreleased, 3: released
p_item_id,
l_job_name,
p_qty,
1,
l_first_unit_start_date,
l_last_unit_completion_date,
l_scheduling_method,
null,
null,
'',
null,
'',
'',
l_header_id);
print_debug('Inserting into WIP_JOB_SCHEDULE_INTERFACE', 're_wip', 9);
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,
FIRST_UNIT_START_DATE , -- LAST_UNIT_COMPLETION_DATE, -- added by bug 13709690
PRIMARY_ITEM_ID,
START_QUANTITY,
STATUS_TYPE)
VALUES(
p_sysd,
p_user_id,
p_sysd,
p_user_id,
p_wip_id,
2,
1,
p_organization_id,
1,
p_sysd , -- p_nb_time, -- added by bug 13709690
p_item_id,
p_qty,
DECODE(p_approval,1,3,1));
print_debug('Inserting into WIP_JOB_SCHEDULE_INTERFACE', 're_wip', 9);
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,
FIRST_UNIT_START_DATE , -- LAST_UNIT_COMPLETION_DATE, -- added by bug 13709690
PRIMARY_ITEM_ID,
START_QUANTITY,
STATUS_TYPE)
VALUES(
p_sysd,
p_user_id,
p_sysd,
p_user_id,
p_wip_id,
2,
1,
p_organization_id,
1,
p_sysd , -- p_nb_time, -- added by bug 13709690
p_item_id,
p_qty,
DECODE(p_approval,1,3,1));
SELECT meaning
INTO x_ret_mesg
FROM mfg_lookups
WHERE lookup_type = 'INV_MMX_RPT_MSGS'
AND lookup_code = 2;
l_gme_batch_header.update_inventory_ind := 'Y' ;
SELECT meaning
INTO x_ret_mesg
FROM mfg_lookups
WHERE lookup_type = 'INV_MMX_RPT_MSGS'
AND lookup_code = 2;
SELECT SUM(quantity) FROM wms_loaded_quantities_v
WHERE inventory_item_id = p_item_id
AND subinventory_code = nvl(p_subinv , subinventory_code )
AND organization_id = p_org_id;
SELECT uom_code
INTO l_uom_code
FROM mtl_units_of_measure_vl
WHERE unit_of_measure = p_uom_name;