VRM_XLA_INV_ITEM_SRC

Details

Schema: FUSION
Object owner: VRM
Object type: VIEW

Columns

Name
INVENTORY_ITEM_NUMBER ITEM_CATEGORY_DESC INV_ITEM_GLOBAL_ATTRIBUTE1 INV_ITEM_GLOBAL_ATTRIBUTE10 INV_ITEM_GLOBAL_ATTRIBUTE2 INV_ITEM_GLOBAL_ATTRIBUTE3 INV_ITEM_GLOBAL_ATTRIBUTE4 INV_ITEM_GLOBAL_ATTRIBUTE5 INV_ITEM_GLOBAL_ATTRIBUTE6 INV_ITEM_GLOBAL_ATTRIBUTE7 INV_ITEM_GLOBAL_ATTRIBUTE8 INV_ITEM_GLOBAL_ATTRIBUTE9 INV_ITEM_GLOBAL_ATTR_CATEGORY INV_ITEM_INVENTORY_ITEM_ID INVENTORY_ITEM_TYPE INVENTORY_ITEM_SALES_ACCOUNT INVENTORY_WAREHOUSE_CODE INVENTORY_WAREHOUSE_NAME EVENT_ID LINE_NUMBER LEDGER_ID ITEM_SRL_NUM_CTRL_CODE ITEM_CATEGORY_CODE

Name

INVENTORY_ITEM_NUMBER

ITEM_CATEGORY_DESC

INV_ITEM_GLOBAL_ATTRIBUTE1

INV_ITEM_GLOBAL_ATTRIBUTE10

INV_ITEM_GLOBAL_ATTRIBUTE2

INV_ITEM_GLOBAL_ATTRIBUTE3

INV_ITEM_GLOBAL_ATTRIBUTE4

INV_ITEM_GLOBAL_ATTRIBUTE5

INV_ITEM_GLOBAL_ATTRIBUTE6

INV_ITEM_GLOBAL_ATTRIBUTE7

INV_ITEM_GLOBAL_ATTRIBUTE8

INV_ITEM_GLOBAL_ATTRIBUTE9

INV_ITEM_GLOBAL_ATTR_CATEGORY

INV_ITEM_INVENTORY_ITEM_ID

INVENTORY_ITEM_TYPE

INVENTORY_ITEM_SALES_ACCOUNT

INVENTORY_WAREHOUSE_CODE

INVENTORY_WAREHOUSE_NAME

EVENT_ID

LINE_NUMBER

LEDGER_ID

ITEM_SRL_NUM_CTRL_CODE

ITEM_CATEGORY_CODE

Query

SQL_Statement
SELECT /+ INDEX(l VRM_XLA_IFRS15_EXTRACT_GT_N1) / mtlbf.ITEM_NUMBER INVENTORY_ITEM_NUMBER, mtlcbf.DESCRIPTION ITEM_CATEGORY_DESC, mtlbf.GLOBAL_ATTRIBUTE1 INV_ITEM_GLOBAL_ATTRIBUTE1 , mtlbf.GLOBAL_ATTRIBUTE10 INV_ITEM_GLOBAL_ATTRIBUTE10 , mtlbf.GLOBAL_ATTRIBUTE2 INV_ITEM_GLOBAL_ATTRIBUTE2 , mtlbf.GLOBAL_ATTRIBUTE3 INV_ITEM_GLOBAL_ATTRIBUTE3 , mtlbf.GLOBAL_ATTRIBUTE4 INV_ITEM_GLOBAL_ATTRIBUTE4 , mtlbf.GLOBAL_ATTRIBUTE5 INV_ITEM_GLOBAL_ATTRIBUTE5 , mtlbf.GLOBAL_ATTRIBUTE6 INV_ITEM_GLOBAL_ATTRIBUTE6 , mtlbf.GLOBAL_ATTRIBUTE7 INV_ITEM_GLOBAL_ATTRIBUTE7 , mtlbf.GLOBAL_ATTRIBUTE8 INV_ITEM_GLOBAL_ATTRIBUTE8 , mtlbf.GLOBAL_ATTRIBUTE9 INV_ITEM_GLOBAL_ATTRIBUTE9 , mtlbf.GLOBAL_ATTRIBUTE_CATEGORY INV_ITEM_GLOBAL_ATTR_CATEGORY , mtlbf.INVENTORY_ITEM_ID INV_ITEM_INVENTORY_ITEM_ID , mtlbf.ITEM_TYPE INVENTORY_ITEM_TYPE, mtlbf.SALES_ACCOUNT INVENTORY_ITEM_SALES_ACCOUNT, o.ORGANIZATION_CODE INVENTORY_WAREHOUSE_CODE, o.ORGANIZATION_NAME INVENTORY_WAREHOUSE_NAME, l.EVENT_ID EVENT_ID, l.LINE_NUMBER LINE_NUMBER, l.LEDGER_ID LEDGER_ID, mtlbf.serial_number_control_code ITEM_SRL_NUM_CTRL_CODE , mtlcbf.category_code ITEM_CATEGORY_CODE FROM EGP_SYSTEM_ITEMS_VL mtlbf , RCS_ORG_DEFINITIONS_LW_V o , (SELECT ITEMCATS.inventory_item_id INVENTORY_ITEM_ID, ORG.organization_id organization_id, ITEMCATS.category_set_id category_set_id, ITEMCATS.category_id category_id FROM egp_item_cat_assignments ITEMCATS, egp_category_sets_b CATSETS, inv_org_parameters ORG WHERE CATSETS.category_set_id = ITEMCATS.category_set_id AND EXISTS ( SELECT /+ NO_UNNEST / 1 FROM egp_item_org_associations ITEMS WHERE ORG.organization_id = ITEMS.organization_id AND ITEMS.inventory_item_id = ITEMCATS.inventory_item_id ) AND ( ( CATSETS.control_level = 1 AND ITEMCATS.organization_id = ORG.master_organization_id ) OR ( CATSETS.control_level = 2 AND ITEMCATS.organization_id = ORG.item_definition_org_id ) ) ) mtlicf, EGP_CATEGORIES_VL mtlcbf, VRM_XLA_IFRS15_EXTRACT_GT l, EGP_DEFAULT_CATEGORY_SETS edcs, GL_LEDGERS gll WHERE l.LEVEL_CODE = 'L' AND mtlbf.INVENTORY_ITEM_ID = l.INVENTORY_ITEM_ID AND mtlbf.ORGANIZATION_ID = l.INVENTORY_ORG_ID AND o.ORGANIZATION_ID = l.INVENTORY_ORG_ID AND gll.ledger_id = l.ledger_id AND mtlicf.organization_id (+) = l.INVENTORY_ORG_ID AND mtlicf.INVENTORY_ITEM_ID (+)= l.INVENTORY_ITEM_ID AND mtlicf.category_set_id = edcs.category_set_id (+) AND edcs.functional_area_id = 1 AND mtlcbf.category_id (+) = mtlicf.category_id

SQL_Statement

SELECT /*+ INDEX(l VRM_XLA_IFRS15_EXTRACT_GT_N1) */

mtlbf.ITEM_NUMBER INVENTORY_ITEM_NUMBER,

mtlcbf.DESCRIPTION ITEM_CATEGORY_DESC,

mtlbf.GLOBAL_ATTRIBUTE1 INV_ITEM_GLOBAL_ATTRIBUTE1 ,

mtlbf.GLOBAL_ATTRIBUTE10 INV_ITEM_GLOBAL_ATTRIBUTE10 ,

mtlbf.GLOBAL_ATTRIBUTE2 INV_ITEM_GLOBAL_ATTRIBUTE2 ,

mtlbf.GLOBAL_ATTRIBUTE3 INV_ITEM_GLOBAL_ATTRIBUTE3 ,

mtlbf.GLOBAL_ATTRIBUTE4 INV_ITEM_GLOBAL_ATTRIBUTE4 ,

mtlbf.GLOBAL_ATTRIBUTE5 INV_ITEM_GLOBAL_ATTRIBUTE5 ,

mtlbf.GLOBAL_ATTRIBUTE6 INV_ITEM_GLOBAL_ATTRIBUTE6 ,

mtlbf.GLOBAL_ATTRIBUTE7 INV_ITEM_GLOBAL_ATTRIBUTE7 ,

mtlbf.GLOBAL_ATTRIBUTE8 INV_ITEM_GLOBAL_ATTRIBUTE8 ,

mtlbf.GLOBAL_ATTRIBUTE9 INV_ITEM_GLOBAL_ATTRIBUTE9 ,

mtlbf.GLOBAL_ATTRIBUTE_CATEGORY INV_ITEM_GLOBAL_ATTR_CATEGORY ,

mtlbf.INVENTORY_ITEM_ID INV_ITEM_INVENTORY_ITEM_ID ,

mtlbf.ITEM_TYPE INVENTORY_ITEM_TYPE,

mtlbf.SALES_ACCOUNT INVENTORY_ITEM_SALES_ACCOUNT,

o.ORGANIZATION_CODE INVENTORY_WAREHOUSE_CODE,

o.ORGANIZATION_NAME INVENTORY_WAREHOUSE_NAME,

l.EVENT_ID EVENT_ID,

l.LINE_NUMBER LINE_NUMBER,

l.LEDGER_ID LEDGER_ID,

mtlbf.serial_number_control_code ITEM_SRL_NUM_CTRL_CODE ,

mtlcbf.category_code ITEM_CATEGORY_CODE

FROM EGP_SYSTEM_ITEMS_VL mtlbf ,

RCS_ORG_DEFINITIONS_LW_V o ,

(SELECT ITEMCATS.inventory_item_id INVENTORY_ITEM_ID,

ORG.organization_id organization_id,

ITEMCATS.category_set_id category_set_id,

ITEMCATS.category_id category_id

FROM egp_item_cat_assignments ITEMCATS,

egp_category_sets_b CATSETS,

inv_org_parameters ORG

WHERE CATSETS.category_set_id = ITEMCATS.category_set_id

AND EXISTS ( SELECT /*+ NO_UNNEST */ 1

FROM egp_item_org_associations ITEMS

WHERE ORG.organization_id = ITEMS.organization_id

AND ITEMS.inventory_item_id = ITEMCATS.inventory_item_id )

AND ( ( CATSETS.control_level = 1 AND ITEMCATS.organization_id = ORG.master_organization_id )

OR ( CATSETS.control_level = 2 AND ITEMCATS.organization_id = ORG.item_definition_org_id ) )

) mtlicf,

EGP_CATEGORIES_VL mtlcbf,

VRM_XLA_IFRS15_EXTRACT_GT l,

EGP_DEFAULT_CATEGORY_SETS edcs,

GL_LEDGERS gll

WHERE l.LEVEL_CODE = 'L'

AND mtlbf.INVENTORY_ITEM_ID = l.INVENTORY_ITEM_ID

AND mtlbf.ORGANIZATION_ID = l.INVENTORY_ORG_ID

AND o.ORGANIZATION_ID = l.INVENTORY_ORG_ID

AND gll.ledger_id = l.ledger_id

AND mtlicf.organization_id (+) = l.INVENTORY_ORG_ID

AND mtlicf.INVENTORY_ITEM_ID (+)= l.INVENTORY_ITEM_ID

AND mtlicf.category_set_id = edcs.category_set_id (+)

AND edcs.functional_area_id = 1

AND mtlcbf.category_id (+) = mtlicf.category_id