with mpit_qry as
(
select /*+ INLINE */
-- Physical Inventory
mp.organization_code,
mpiv.physical_inventory_name,
mpiv.description,
mpiv.physical_inventory_date,
mpiv.snapshot_complete,
mpiv.freeze_date snapshot_date,
xxen_util.meaning(mpiv.adjustments_posted,'INV_YES_NO',3) adjustments_posted,
mpiv.last_adjustment_date,
mpiv.total_adjustment_value,
xxen_util.meaning(mpiv.approval_required,'INV_CC_APPROVAL',700) approval_required,
mpiv.approval_tolerance_pos quantity_tolerance_plus,
mpiv.approval_tolerance_neg quantity_tolerance_minus,
mpiv.cost_variance_pos value_tolerance_plus,
mpiv.cost_variance_neg value_tolerance_minus,
xxen_util.meaning(mpiv.all_subinventories_flag,'INV_YES_NO',3) all_subinventories,
(select listagg(mps.subinventory,',') within group (order by mps.subinventory)
from mtl_physical_subinventories mps
where mps.physical_inventory_id = mpiv.physical_inventory_id
and mps.organization_Id = mpiv.organization_id
) subinventories,
xxen_util.meaning(mpiv.exclude_zero_balance,'INV_YES_NO',3) exclude_zero_balance,
xxen_util.meaning(mpiv.exclude_negative_balance,'INV_YES_NO',3) exclude_negative_balance,
xxen_util.meaning(mpiv.dynamic_tag_entry_flag,'INV_YES_NO',3) dynamic_tag_entry_flag,
mpiv.next_tag_number,
mpiv.tag_number_increments,
mpiv.number_of_skus,
(select gcck.concatenated_segments
from gl_code_combinations_kfv gcck
where gcck.code_combination_id = mpiv.default_gl_adjust_account
) default_adjustment_account,
-- Tags
mpit.tag_number,
msiv.concatenated_segments item,
mpit.revision,
msiv.description item_description,
xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) item_type,
msiv.item_type item_type_code,
msiv.primary_uom_code item_primary_uom,
msiv.secondary_uom_code item_secondary_uom,
mck.concatenated_segments item_category,
mpit.subinventory,
milk.concatenated_segments locator,
mpit.lot_number,
mpit.lot_expiration_date,
mpit.serial_num serial_number,
inv_invarptp_xmlp_pkg.cf_parent_lpnformula(mpit.parent_lpn_id) parent_lpn,
inv_invarptp_xmlp_pkg.cf_outermost_lpnformula(mpit.outermost_lpn_id,mpit.parent_lpn_id) outermost_lpn,
--
mpit.tag_uom uom,
mpit.tag_quantity quantity,
mpit.tag_secondary_uom secondary_uom,
mpit.tag_secondary_quantity secondary_quantity,
(select papf.full_name
from per_all_people_f papf
where papf.person_id = mpit.counted_by_employee_id
and sysdate between papf.effective_start_date and papf.effective_end_date
) last_counted_by,
xxen_util.meaning(decode(mpit.void_flag,1,mpit.void_flag,null),'INV_YES_NO',3) void,
--
mpa.system_quantity,
mpa.secondary_system_qty secondary_system_quantity,
--
inv_invarptp_xmlp_pkg.cf_cost_groupformula(mpit.cost_group_id)cost_group_name,
--
mpit.attribute_category,
mpit.attribute1,
mpit.attribute2,
mpit.attribute3,
mpit.attribute4,
mpit.attribute5,
mpit.attribute6,
mpit.attribute7,
mpit.attribute8,
mpit.attribute9,
mpit.attribute10,
mpit.attribute11,
mpit.attribute12,
mpit.attribute13,
mpit.attribute14,
mpit.attribute15,
mpiv.physical_inventory_id,
mpit.tag_id,
mpit.last_update_date,
mpit.last_updated_by
from
mtl_physical_inventories_v mpiv,
mtl_physical_inventory_tags mpit,
mtl_physical_adjustments mpa,
mtl_parameters mp,
mtl_item_locations_kfv milk,
mtl_system_items_vl msiv,
mtl_item_categories mic,
mtl_categories_kfv mck
where
mpiv.physical_inventory_id = mpit.physical_inventory_id and
mpiv.organization_id = mpit.organization_id and
mpit.adjustment_id = mpa.adjustment_id (+) and
mpiv.organization_id = mp.organization_id and
mpit.organization_id = milk.organization_id (+) and
mpit.locator_id = milk.inventory_location_id (+) and
mpit.organization_id = msiv.organization_id (+) and
mpit.inventory_item_id = msiv.inventory_item_id (+) and
mpit.organization_id = mic.organization_id (+) and
mpit.inventory_item_id = mic.inventory_item_id (+) and
:p_category_set_id = mic.category_set_id (+) and
mic.category_id = mck.category_id (+) and
-- from INVADPPI (Physical Inventory Tag Counts) form
inv_material_status_grp.is_status_applicable( null, null,8,null,null,mpit.organization_id,mpit.inventory_item_id,mpit.subinventory,mpit.locator_id,mpit.lot_number,mpit.serial_num,'A') = 'Y' and
mpiv.adjustments_posted = 2 and
-- Exclude Serial Controlled Items in Physical Inventories defined for Multiple Serial Numbers Requests per Tag.
-- We are not handling this scenario as yet
&lp_multiple_sn_clause
--
not exists
(select
null
from
mtl_physical_adjustments mpa
where
mpit.organization_id = mpa.organization_id and
mpit.physical_inventory_id= mpa.physical_inventory_id and
mpit.adjustment_id = mpa.adjustment_id and
mpit.inventory_item_id = mpa.inventory_item_id and
nvl(mpa.approval_status,0) = 3 -- processed
)
)
--
-- Main Query Starts Here
--
select /*+ push_pred(mpit) */
null action_,
null status_,
null message_,
null request_id_,
null modified_columns_,
to_date(null) timestamp_,
null row_id,
:p_upload_mode upload_mode,
mpit.organization_code,
mpit.physical_inventory_name,
-- Tags
mpit.tag_number,
mpit.item,
mpit.revision,
mpit.item_description,
mpit.item_type,
mpit.item_category,
mpit.item_primary_uom,
mpit.item_secondary_uom,
mpit.subinventory,
mpit.locator,
mpit.lot_number,
mpit.lot_expiration_date,
mpit.serial_number,
mpit.parent_lpn,
mpit.outermost_lpn,
--
nvl(mpit.uom,mpit.item_primary_uom) uom,
mpit.quantity,
mpit.secondary_uom,
mpit.secondary_quantity,
mpit.last_counted_by,
:p_counter_emp_name counted_by,
mpit.void,
--
mpit.system_quantity,
mpit.secondary_system_quantity,
--
mpit.cost_group_name,
--
mpit.attribute_category,
mpit.attribute1,
mpit.attribute2,
mpit.attribute3,
mpit.attribute4,
mpit.attribute5,
mpit.attribute6,
mpit.attribute7,
mpit.attribute8,
mpit.attribute9,
mpit.attribute10,
mpit.attribute11,
mpit.attribute12,
mpit.attribute13,
mpit.attribute14,
mpit.attribute15,
-- ids
mpit.tag_id
from
mpit_qry mpit
where
mpit.organization_code = :p_organization_code and
mpit.physical_inventory_name = :p_physical_inv_name and
1=1 |
