--Redundant noetix view noetix_sys.xxk_item_loc mtll1,noetix_sys.xxk_sys_item
--noetix_sys.invg0_inv_acl_map_base is replaced by haou and ood,gl
--
select
x.item$item_division division,
x.organization_code,
x.a$organization_name organisation_name,
x.item$item_brand brand,
x.item$sv$item item,
x.item_description,
x.item$sv$item||'|'||x.item_description "SKU",
x.a$subinventory,
x.locator$sv$stock_locator,
x.item$item_type,
x.item_type_a9_descr item_type,
null gl_account, --=VLOOKUP(K9,GL_Account,2,FALSE) not possible
x.lot_number lot,
x.lot_status,
x.dual_onhand_quantity, --sum to be defined in template
x.quantity qty, --sum to be defined in template
x.unit_cost_amount,
x.unit_cost_amount*x.quantity valuation, --sum to be defined in template
x.dual_onhand_uom uom
from
(
select
ood.organization_code,
ood.organization_name a$organization_name,
msib.segment1 item$sv$item,
msit.description item_description,
msib.inventory_item_id inventory_item_id,
msib.organization_id organization_id,
msit.long_description item_long_description,
moqd.revision item_revision,
moqd.subinventory_code a$subinventory,
gl.chart_of_accounts_id chart_of_accounts_id,
nvl(moqd.secondary_transaction_quantity,0) dual_onhand_quantity, --sum to be defined in template
nvl(moqd.primary_transaction_quantity,0) quantity, --SUM to be defined in template
muomv.unit_of_measure dual_onhand_uom,
msib.attribute23 item$arv_item_indicator,
msib.attribute1 item$allow_backorder,
msib.attribute2 item$b2b_item,
msib.attribute19 item$b2b_packsize,
msib.attribute20 item$b2b_packsize_uom,
msib.attribute17 item$incl_bo_s_in_planning,
msib.attribute15 item$invoice_uom,
msib.attribute22 item$item_brand,
msib.attribute21 item$item_division,
msib.attribute9 item$item_type,
msib.attribute18 item$legacy_item_code,
msib.attribute12 item$minimum_supply_percent,
msib.attribute5 item$no_of_sh_in_pa,
msib.attribute7 item$pa_gtin,
msib.attribute4 item$pa_pack_qty,
msib.attribute11 item$proof_date_spec_number,
msib.attribute16 item$sales_order_min_order_qty,
msib.attribute6 item$sh_gtin,
msib.attribute3 item$sh_pack_qty,
msib.attribute8 item$smallest_selling_uom,
msib.attribute13 item$trade_fees__y_n,
&category_columns
mil.description locator_description,
mil.segment1 locator$sv$stock_locator,
mil.inventory_location_id locator$inventory_location_id,
mil.organization_id locator$organization_id,
moqd.subinventory_code subinventory,
msi.description subinv_description,
msib.primary_unit_of_measure unit_of_measure,
msib.Unit_weight unit_weight,
--nvl(
--xxnao_opm_wrapper_pkg.get_standard_cost(
--msib.inventory_item_id,
--msib.organization_id,
--sysdate,
--gl.ledger_id
--),
--0
--)unit_cost_amount,
0 unit_cost_amount,
moqd.lot_number,
mln.expiration_date,
mmst1.status_code lot_status,
msi.description subinv_name,
ffv_it.description item_type_a9_descr,
trunc(mln.creation_date) lot_create_date,
msi.attribute1 subinv_usage_type,
------ Value Set-----XYINV_USAGE_TYPE
(
select ffvl.description
from
fnd_flex_value_sets ffvs,
fnd_flex_values_vl ffvl
where ffvs.flex_value_set_name = 'XYINV_USAGE_TYPE' and
ffvs.flex_value_set_id = ffvl.flex_value_set_id and
ffvl.flex_value = msi.attribute1
)subinv_usage_type_desc,
mln.c_attribute15 lab_notes,
mln.c_attribute10 lot_notes
--
from
mtl_units_of_measure_vl muomv,
mtl_item_locations mil,
mtl_system_items_tl msit,
mtl_system_items_b msib,
mtl_onhand_quantities_detail moqd,
mtl_lot_numbers mln,
mtl_material_statuses_tl mmst1,
mtl_secondary_inventories msi,
------ Value Set-----XYAI_ITEM_TYPE
(
select
ffvv.flex_value,
ffvv.flex_value_meaning,
ffvv.description
from
fnd_flex_values_vl ffvv,
fnd_flex_value_sets ffvs
where ffvs.flex_value_set_id=ffvv.flex_value_set_id and
ffvs.flex_value_set_name='XYAI INV Item Type'
) ffv_it,
org_organization_definitions ood,
hr_all_organization_units haou,
hr_organization_information hoi,
gl_ledgers gl
where 1=1 and
hoi.org_information_context= 'Accounting Information' and
hoi.organization_id=ood.organization_id and
haou.organization_id=to_number(hoi.org_information3) and -- this gets the operating unit id
gl.ledger_id=to_number(hoi.org_information1) and -- get the ledger_id
moqd.secondary_uom_code=muomv.uom_code(+) and
msib.organization_id=ood.organization_id and
moqd.organization_id=msi.organization_id and
moqd.subinventory_code=msi.secondary_inventory_name and
moqd.organization_id=msib.organization_id and
moqd.inventory_item_id=msib.inventory_item_id and
msit.inventory_item_id(+)=msib.inventory_item_id and
msit.organization_id(+)=msib.organization_id and
msit.language(+)=userenv('lang') and
moqd.locator_id=mil.inventory_location_id(+) and
moqd.organization_id=mil.organization_id(+) and
moqd.organization_id=mln.organization_id(+) and
moqd.inventory_item_id=mln.inventory_item_id(+) and
moqd.lot_number=mln.lot_number (+) and
nvl(mln.status_id,-99)=mmst1.status_id (+) and
mmst1.language(+)=userenv('lang') and
msib.attribute9=ffv_it.flex_value (+)
)x
where 2=2 |
