[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT user_name
INTO G_USER_NAME
FROM fnd_user
WHERE user_id = p_user_id;
SELECT NVL(fnd_profile.value('INV_DEBUG_TRACE'),'2')
INTO l_inv_debug
FROM dual;
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_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_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
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY VARCHAR2
) IS
TYPE c_items_curtype IS REF CURSOR;
' SELECT ' || p_item_select || ' 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,
pick_components_flag pick_components
FROM mtl_categories b,
mtl_item_categories a,
mtl_system_items 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 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 ( ' || p_range_sql || ' ) ';
' SELECT ' || p_item_select || ' 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,
pick_components_flag
FROM mtl_categories b,
mtl_item_categories a,
mtl_system_items 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 || ' ) ';
' SELECT ' || p_item_select || ',
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,
pick_components_flag
FROM mtl_categories b,
mtl_item_categories a,
mtl_system_items 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 || ' ) ';
' SELECT ' || p_item_select || ',
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,
pick_components_flag
FROM mtl_categories b,
mtl_item_categories a,
mtl_system_items 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 ||
', p_handle_rep_item: ' || to_char(p_handle_rep_item) ||
', p_pur_revision: ' || to_char(p_pur_revision) ||
', p_cat_select: ' || p_cat_select ||
', p_cat_set_id: ' || to_char(p_cat_set_id) ||
', p_mcat_struct: ' || to_char(p_mcat_struct) ||
', p_level: ' || to_char(p_level) ||
', p_restock: ' || to_char(p_restock) ||
', p_include_nonnet: ' || to_char(p_include_nonnet) ||
', p_include_po: ' || to_char(p_include_po) ||
', p_include_wip: ' || to_char(p_include_wip) ||
', p_include_if: ' || to_char(p_include_if)
, 'run_min_max_plan'
, 5);
', p_selection: ' || to_char(p_selection) ||
', p_sysdate: ' || to_char(p_sysdate, 'DD-MON-YYYY HH24:MI:SS') ||
', p_s_cutoff: ' || to_char(p_s_cutoff, 'DD-MON-YYYY HH24:MI:SS') ||
', p_d_cutoff: ' || to_char(p_d_cutoff, 'DD-MON-YYYY HH24:MI:SS') ||
', p_order_by: ' || p_order_by ||
', p_encum_flag: ' || p_encum_flag ||
', p_cal_code: ' || p_cal_code ||
', p_exception_set_id: ' || to_char(p_exception_set_id)
, 'run_min_max_plan'
, 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 := get_item_segments(p_org_id, item_rec.item_id);
INSERT INTO INV_MIN_MAX_TEMP (
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)
VALUES ( l_item_segments
, item_rec.description
, l_stat
, l_sortee
, 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_system_items_kfv
WHERE organization_id = p_org_id
AND inventory_item_id = p_item_id;
SELECT concatenated_segments
FROM mtl_categories_kfv
WHERE category_id = p_category_id
AND structure_id = p_struct_id;
SELECT SUM(moqd.transaction_quantity)
INTO l_moq_qty1
FROM mtl_onhand_quantities_detail moqd
WHERE moqd.organization_id = p_org_id
AND moqd.inventory_item_id = p_item_id
AND EXISTS (select 'x' from mtl_secondary_inventories msi
WHERE msi.organization_id = moqd.organization_id and
msi.secondary_inventory_name = moqd.subinventory_code
AND msi.availability_type = decode(p_include_nonnet,1,msi.availability_type,1))
AND nvl(moqd.planning_tp_type,2) = 2;
SELECT SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
Sign(mmtt.primary_quantity)) * Abs( mmtt.primary_quantity ))
INTO l_mmtt_qty1
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_org_id
AND mmtt.inventory_item_id = p_item_id
AND mmtt.posting_flag = 'Y'
AND mmtt.subinventory_code IS NOT NULL
AND Nvl(mmtt.transaction_status,0) <> 2
AND mmtt.transaction_action_id NOT IN (24,30)
AND EXISTS (select 'x' from mtl_secondary_inventories msi
WHERE msi.organization_id = mmtt.organization_id
AND msi.secondary_inventory_name = mmtt.subinventory_code
AND msi.availability_type = decode(p_include_nonnet,1,msi.availability_type,1))
AND nvl(mmtt.planning_tp_type,2) = 2;
SELECT SUM(Abs(mmtt.primary_quantity))
INTO l_mmtt_qty2
FROM mtl_material_transactions_temp mmtt
WHERE decode(mmtt.transaction_action_id,3,
mmtt.transfer_organization,mmtt.organization_id) = p_org_id
AND mmtt.inventory_item_id = p_item_id
AND mmtt.posting_flag = 'Y'
AND Nvl(mmtt.transaction_status,0) <> 2
AND mmtt.transaction_action_id in (2,28,3)
AND ((mmtt.transfer_subinventory IS NULL) OR
(mmtt.transfer_subinventory IS NOT NULL
AND EXISTS (select 'x' from mtl_secondary_inventories msi
WHERE msi.organization_id = decode(mmtt.transaction_action_id,
3, mmtt.transfer_organization,mmtt.organization_id)
AND msi.secondary_inventory_name = mmtt.transfer_subinventory
AND msi.availability_type = decode(p_include_nonnet,1,msi.availability_type,1))))
AND nvl(mmtt.planning_tp_type,2) = 2;
SELECT SUM(moqd.transaction_quantity)
INTO l_moq_qty1
FROM mtl_onhand_quantities_detail moqd
WHERE moqd.organization_id = p_org_id
AND moqd.inventory_item_id = p_item_id
--AND moqd.planning_tp_type = 2
AND moqd.subinventory_code = p_subinv;
SELECT SUM(Decode(mmtt.transaction_action_id, 1, -1, 2, -1, 28, -1, 3, -1,
Sign(mmtt.primary_quantity)) * Abs( mmtt.primary_quantity ))
INTO l_mmtt_qty1
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_org_id
AND mmtt.inventory_item_id = p_item_id
AND mmtt.subinventory_code = p_subinv
--AND mmtt.planning_tp_type = 2
AND mmtt.posting_flag = 'Y'
AND mmtt.subinventory_code IS NOT NULL
AND Nvl(mmtt.transaction_status,0) <> 2
AND mmtt.transaction_action_id NOT IN (24,30);
SELECT SUM(Abs(mmtt.primary_quantity))
INTO l_mmtt_qty2
FROM mtl_material_transactions_temp mmtt
WHERE decode(mmtt.transaction_action_id,3,
mmtt.transfer_organization,mmtt.organization_id) = p_org_id
AND mmtt.inventory_item_id = p_item_id
AND mmtt.transfer_subinventory = p_subinv
--AND mmtt.planning_tp_type = 2
AND mmtt.posting_flag = 'Y'
AND Nvl(mmtt.transaction_status,0) <> 2
AND mmtt.transaction_action_id in (2,28,3);
' SELECT NVL(sum(to_org_primary_quantity), 0)
FROM mtl_supply sup, bom_calendar_dates c1, bom_calendar_dates c
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 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_postp_lead_time))
AND c.calendar_code = :l_cal_code
AND c.exception_set_id = :l_except_id
AND c.calendar_date = trunc(sup.need_by_date)
AND trunc(c1.calendar_date) <= :l_s_cutoff
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) ';
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 NOT IN (5,6)
AND mtrl.date_required <= p_s_cutoff;
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 trunc(scheduled_completion_date) <= p_s_cutoff
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 NVL(SUM(quantity),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 <= p_s_cutoff
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
--Bug 2647862
AND trunc(scheduled_completion_date) <= p_s_cutoff
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
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)));
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
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)));
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
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
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)));
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')
)
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
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,
po_requisition_lines_all pol
where md.DEMAND_SOURCE_LINE_ID = so.LINE_ID
and so.ORIG_SYS_LINE_REF = to_char(pod.DISTRIBUTION_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')
)
and ORGANIZATION_ID = p_org_id
and md.INVENTORY_ITEM_ID = p_item_id
and REQUIREMENT_DATE <= p_d_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_org_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_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(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
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.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
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(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 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(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 NOT IN (5,6)
AND mtrl.date_required <= p_d_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
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
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)));
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 = 2
AND mtrl.line_status NOT IN (5,6);
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 + 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 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 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
);
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, 'INCOMPLETE', 2,'APPROVED'),
DECODE(p_src_type, 1, 'INVENTORY', 'VENDOR'),
p_src_org,
p_src_subinv,
p_organization_id,
p_subinv,
p_employee_id,
'INVENTORY',
p_uom,
p_location_id,
p_item_id,
DECODE(l_item_revision,'@@@',NULL,l_item_revision),
p_qty,
trunc(p_nb_time),
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;
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,
LAST_UNIT_COMPLETION_DATE,
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_nb_time,
p_item_id,
p_qty,
DECODE(p_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;