INV Transaction Upload

➜ Back to Library
Description
Categories: Enginatics, R12 only, Upload
Repository: Github
INV Transaction Upload
======================
This upload can be used to create Inventory Material Transactions.

The following upload modes are supported:

Create – Generates an empty upload file. Transaction details must be entered manually. The Create mode always opens a blank Excel template, regardless of any parameter settings.

Create, Update – Based on the select ...  INV Transaction Upload
======================
This upload can be used to create Inventory Material Transactions.

The following upload modes are supported:

Create – Generates an empty upload file. Transaction details must be entered manually. The Create mode always opens a blank Excel template, regardless of any parameter settings.

Create, Update – Based on the selected parameters, this mode can generate a pre-populated file with details such as item, primary unit of measure, and transaction date (defaulted to today’s date). It also displays the current on-hand quantity for each item, allowing users to transact against existing stock. Additionally, users can create new transactions by entering details manually

Note :
-- For "Account alias issue" transaction Type - Transaction Source needs to be selected for this Transaction Type, Don't select Account Alias.
-- For "Account alias receipt" transaction Type - Transaction Source needs to be selected for this Transaction Type, Don't select Account Alias.
-- For "Account issue" transaction Type - Account Alias needs to be selected for this Transaction Type, Don't select Transaction Source.
-- For "Account receipt" transaction Type - Account Alias needs to be selected for this Transaction Type, Don't select Transaction Source.   more 
select 
null action_,
null status_,
null message_,
null modified_columns_,
to_number(null) source_line_id,
to_number(null) source_header_id,
ood.organization_code organization,
msiv.concatenated_segments item,
msiv.description,
moqd.revision,
moqd.subinventory_code subinventory,
moqd.locator_code locator,
moqd.on_hand_quantity onhand_quantity,
msiv.primary_uom_code uom,
sysdate transaction_date,
to_char(null) transaction_type,
to_char(null) transaction_source,
to_number(null) quantity,
to_char(null) account_alias,
to_char(null) distribution_account,
to_char(null) gl_segment1,
to_char(null) gl_segment2,
to_char(null) gl_segment3,
to_char(null) gl_segment4,
to_char(null) gl_segment5,
to_char(null) gl_segment6,
to_char(null) gl_segment7,
to_char(null) gl_segment8,
to_char(null) gl_segment9,
to_char(null) gl_segment10,
to_char(null) gl_segment11,
to_char(null) gl_segment12,
to_char(null) gl_segment13,
to_char(null) gl_segment14,
to_char(null) gl_segment15,
to_char(null) lpn,
to_char(null) from_serial_number,
to_char(null) to_serial_number, 
to_char(null) transaction_reference,
to_char(null) reason,
to_number(null) transaction_cost,
to_char(null) lot_number,
to_date(null) lot_expiration_date,
to_number(null) lot_quantity,
to_char(null) to_org,
to_char(null) to_subinv,
to_char(null) to_locator,
to_char(null) location,
to_char(null) source_project,
to_char(null) source_task,
to_char(null) expenditure_type,
to_char(null) expenditure_org,
to_char(null) attribute_category,
to_char(null) inv_trx_attribute1,
to_char(null) inv_trx_attribute2,
to_char(null) inv_trx_attribute3,
to_char(null) inv_trx_attribute4,
to_char(null) inv_trx_attribute5,
to_char(null) inv_trx_attribute6,
to_char(null) inv_trx_attribute7,
to_char(null) inv_trx_attribute8,
to_char(null) inv_trx_attribute9,
to_char(null) inv_trx_attribute10,
to_char(null) inv_trx_attribute11,
to_char(null) inv_trx_attribute12,
to_char(null) inv_trx_attribute13,
to_char(null) inv_trx_attribute14,
to_char(null) inv_trx_attribute15,
0 upload_row
from 
org_organization_definitions ood,
mtl_system_items_vl msiv,
(
select 
moqd1.organization_id,
moqd1.inventory_item_id,
moqd1.subinventory_code,
moqd1.revision,
milk.concatenated_segments locator_code,
sum(primary_transaction_quantity) on_hand_quantity
from 
mtl_onhand_quantities_detail moqd1,
mtl_item_locations_kfv milk,
wms_license_plate_numbers wlpn
where 
moqd1.locator_id=milk.inventory_location_id(+) and
moqd1.lpn_id=wlpn.lpn_id(+)
group by 
moqd1.organization_id,
moqd1.inventory_item_id,
moqd1.subinventory_code,
moqd1.revision,
milk.concatenated_segments
) moqd
where
1=1 and
msiv.organization_id=ood.organization_id and
msiv.mtl_transactions_enabled_flag = 'Y' and 
msiv.organization_id=moqd.organization_id(+) and
msiv.inventory_item_id=moqd.inventory_item_id(+)
Parameter NameSQL textValidation
Upload Mode
:p_upload_mode like '%' || xxen_upload.action_update
LOV
Organization Code
ood.organization_code=:organization_code
LOV
Category Set
(msiv.organization_id, msiv.inventory_item_id) in
(
select
mic.organization_id,
mic.inventory_item_id
from
mtl_category_sets_v mcsv,
mtl_item_categories mic
where
mcsv.category_set_name=:p_category_set_name and
mcsv.category_set_id=mic.category_set_id
)
LOV
Category
(msiv.organization_id, msiv.inventory_item_id) in
(
select
mic.organization_id,
mic.inventory_item_id
from
mtl_category_sets_v mcsv,
mtl_categories_kfv mck,
mtl_item_categories mic
where
mck.concatenated_segments=:p_category and
mcsv.category_set_name=:p_category_set_name and
mcsv.category_set_id=mic.category_set_id and
mcsv.structure_id=mck.structure_id and
mck.category_id=mic.category_id
)
LOV
Item
msiv.concatenated_segments=:item
LOV
Item Like
msiv.concatenated_segments like :item_like
Char
Item Description
upper(msiv.description) like upper(:item_descr)
LOV
Item Type
msiv.item_type=xxen_util.lookup_code(:item_type,'ITEM_TYPE',3)
LOV
Item Status
msiv.inventory_item_status_code in (select mis.inventory_item_status_code from mtl_item_status mis where mis.inventory_item_status_code_tl=:item_status)
LOV
BOM Item Type
msiv.bom_item_type=:bom_item_type
LOV Oracle
Contract Item Type
msiv.contract_item_type_code=xxen_util.lookup_code(:contract_item_type,'OKB_CONTRACT_ITEM_TYPE',0)
LOV
Make or Buy
msiv.planning_make_buy_code=xxen_util.lookup_code(:make_or_buy,'MTL_PLANNING_MAKE_BUY',700)
LOV
Buyer
msiv.buyer_id=:buyer_id
LOV
Planner
msiv.planner_code=:planner_code
LOV
Inventory Planning Method
msiv.inventory_planning_code=xxen_util.lookup_code(:inventory_planning_method,'MTL_MATERIAL_PLANNING',700)
LOV
Cross Reference Type
msiv.inventory_item_id in (
select
mcrb.inventory_item_id
from
mtl_cross_references_b mcrb
where
mcrb.cross_reference_type=:cross_reference_type and
sysdate between nvl(mcrb.start_date_active,sysdate) and nvl(mcrb.end_date_active,sysdate)
)
LOV
Cross Reference
msiv.inventory_item_id in (
select
mcrb.inventory_item_id
from
mtl_cross_references_b mcrb
where
mcrb.cross_reference=:cross_reference and
mcrb.cross_reference_type=:cross_reference_type and
sysdate between nvl(mcrb.start_date_active,sysdate) and nvl(mcrb.end_date_active,sysdate)
)
LOV
Locator Control
msiv.location_control_code=xxen_util.lookup_code(:locator_control,'MTL_LOCATION_CONTROL',700)
LOV
Serial Number Control
msiv.serial_number_control_code=xxen_util.lookup_code(:serial_number_control,'MTL_SERIAL_NUMBER',700)
LOV
Lot Control
msiv.lot_control_code=xxen_util.lookup_code(:lot_control,'MTL_LOT_CONTROL',700)
LOV
Revision Control
msiv.revision_qty_control_code=xxen_util.lookup_code(:revision_control,'MTL_ENG_QUANTITY',700)
LOV
Download
Related Blitz Reports
INV Cycle count listing INV Onhand Quantities INV Cycle count entries and adjustments ECC Inventory Management, Inventory Trace Sales Orders INV Cycle counts pending approval INV Item Upload INV Items INV Cycle count open requests listing CAC Inventory Lot and Locator OPM Value (Period-End)
Blitz Report™