AR_CM_INVENTORY_L

Details

Schema: FUSION
Object owner: AR
Object type: VIEW

Columns

Name
CM_LINE_ITEM_GLOBAL_ATTR1 CM_LINE_ITEM_GLOBAL_ATTR10 CM_LINE_ITEM_GLOBAL_ATTR2 CM_LINE_ITEM_GLOBAL_ATTR3 CM_LINE_ITEM_GLOBAL_ATTR4 CM_LINE_ITEM_GLOBAL_ATTR5 CM_LINE_ITEM_GLOBAL_ATTR6 CM_LINE_ITEM_GLOBAL_ATTR7 CM_LINE_ITEM_GLOBAL_ATTR8 CM_LINE_ITEM_GLOBAL_ATTR9 CM_LINE_ITEM_GLOBAL_ATTR_CAT CM_LINE_ITEM_ID CM_LINE_ITEM_TYPE CM_LINE_ITEM_SALES_ACCOUNT CM_LINE_ITEM_SRL_NM_CTRL_CODE CM_LINE_ITEM_CAT_ID EVENT_ID LINE_NUMBER LANGUAGE LEDGER_ID CM_LINE_WAREHOUSE_CODE CM_LINE_WAREHOUSE_NAME CM_LINE_ITEM_CAT_CODE

Name

CM_LINE_ITEM_GLOBAL_ATTR1

CM_LINE_ITEM_GLOBAL_ATTR10

CM_LINE_ITEM_GLOBAL_ATTR2

CM_LINE_ITEM_GLOBAL_ATTR3

CM_LINE_ITEM_GLOBAL_ATTR4

CM_LINE_ITEM_GLOBAL_ATTR5

CM_LINE_ITEM_GLOBAL_ATTR6

CM_LINE_ITEM_GLOBAL_ATTR7

CM_LINE_ITEM_GLOBAL_ATTR8

CM_LINE_ITEM_GLOBAL_ATTR9

CM_LINE_ITEM_GLOBAL_ATTR_CAT

CM_LINE_ITEM_ID

CM_LINE_ITEM_TYPE

CM_LINE_ITEM_SALES_ACCOUNT

CM_LINE_ITEM_SRL_NM_CTRL_CODE

CM_LINE_ITEM_CAT_ID

EVENT_ID

LINE_NUMBER

LANGUAGE

LEDGER_ID

CM_LINE_WAREHOUSE_CODE

CM_LINE_WAREHOUSE_NAME

CM_LINE_ITEM_CAT_CODE

Query

SQL_Statement
SELECT /+ INDEX(l ar_xla_lines_extract_n1) leading(edcs l ITEMCATS) / mtlvl.global_attribute1 cm_line_item_global_attr1 , mtlvl.global_attribute10 cm_line_item_global_attr10 , mtlvl.global_attribute2 cm_line_item_global_attr2 , mtlvl.global_attribute3 cm_line_item_global_attr3 , mtlvl.global_attribute4 cm_line_item_global_attr4 , mtlvl.global_attribute5 cm_line_item_global_attr5 , mtlvl.global_attribute6 cm_line_item_global_attr6 , mtlvl.global_attribute7 cm_line_item_global_attr7 , mtlvl.global_attribute8 cm_line_item_global_attr8 , mtlvl.global_attribute9 cm_line_item_global_attr9 , mtlvl.global_attribute_category cm_line_item_global_attr_cat , mtlvl.inventory_item_id cm_line_item_id , mtlvl.item_type cm_line_item_type , mtlvl.sales_account cm_line_item_sales_account , mtlvl.serial_number_control_code cm_line_item_srl_nm_ctrl_code , mtlicf.category_id cm_line_item_cat_id , l.event_id event_id , l.line_number line_number , l.language language , l.ledger_id ledger_id , o.organization_code cm_line_warehouse_code , o.organization_name cm_line_warehouse_name , mtlcbf.category_code cm_line_item_cat_code FROM EGP_SYSTEM_ITEMS_VL mtlvl , (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_B mtlcbf , EGP_CATEGORY_SET_VALID_CATS mtlcsf , INV_ORGANIZATION_DEFINITIONS_V o , ar_xla_lines_extract l, egp_Default_category_sets edcs WHERE mtlvl.organization_id = NVL(l.warehouse_id, l.so_organization_id) AND mtlvl.inventory_item_id = l.inventory_item_id AND l.inventory_item_id = mtlicf.inventory_item_id (+) AND l.level_flag = 'L' AND mtlicf.organization_id (+) = NVL(l.warehouse_id, l.so_organization_id) AND mtlicf.category_set_id = edcs.category_set_id (+) AND edcs.functional_area_id = 1 AND mtlcbf.category_id (+) = mtlicf.category_id AND mtlcsf.category_id (+) = mtlicf.category_id AND mtlcsf.category_set_id (+) = mtlicf.category_set_id AND o.organization_id = NVL(l.warehouse_id, l.so_organization_id) AND l.event_type_code IN ('CM_CREATE','CM_UPDATE') GROUP BY mtlvl.inventory_item_id , mtlvl.global_attribute1 , mtlvl.global_attribute10 , mtlvl.global_attribute2 , mtlvl.global_attribute3 , mtlvl.global_attribute4 , mtlvl.global_attribute5 , mtlvl.global_attribute6 , mtlvl.global_attribute7 , mtlvl.global_attribute8 , mtlvl.global_attribute9 , mtlvl.global_attribute_category , mtlvl.inventory_item_id , mtlvl.item_type , mtlvl.sales_account , mtlvl.serial_number_control_code , mtlicf.category_id , l.event_id , l.line_number , l.language , l.ledger_id , o.organization_code , o.organization_name , mtlcbf.category_code

SQL_Statement

SELECT

/*+ INDEX(l ar_xla_lines_extract_n1) leading(edcs l ITEMCATS) */

mtlvl.global_attribute1 cm_line_item_global_attr1 ,

mtlvl.global_attribute10 cm_line_item_global_attr10 ,

mtlvl.global_attribute2 cm_line_item_global_attr2 ,

mtlvl.global_attribute3 cm_line_item_global_attr3 ,

mtlvl.global_attribute4 cm_line_item_global_attr4 ,

mtlvl.global_attribute5 cm_line_item_global_attr5 ,

mtlvl.global_attribute6 cm_line_item_global_attr6 ,

mtlvl.global_attribute7 cm_line_item_global_attr7 ,

mtlvl.global_attribute8 cm_line_item_global_attr8 ,

mtlvl.global_attribute9 cm_line_item_global_attr9 ,

mtlvl.global_attribute_category cm_line_item_global_attr_cat ,

mtlvl.inventory_item_id cm_line_item_id ,

mtlvl.item_type cm_line_item_type ,

mtlvl.sales_account cm_line_item_sales_account ,

mtlvl.serial_number_control_code cm_line_item_srl_nm_ctrl_code ,

mtlicf.category_id cm_line_item_cat_id ,

l.event_id event_id ,

l.line_number line_number ,

l.language language ,

l.ledger_id ledger_id ,

o.organization_code cm_line_warehouse_code ,

o.organization_name cm_line_warehouse_name ,

mtlcbf.category_code cm_line_item_cat_code

FROM EGP_SYSTEM_ITEMS_VL mtlvl ,

(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_B mtlcbf ,

EGP_CATEGORY_SET_VALID_CATS mtlcsf ,

INV_ORGANIZATION_DEFINITIONS_V o ,

ar_xla_lines_extract l,

egp_Default_category_sets edcs

WHERE mtlvl.organization_id = NVL(l.warehouse_id, l.so_organization_id)

AND mtlvl.inventory_item_id = l.inventory_item_id

AND l.inventory_item_id = mtlicf.inventory_item_id (+)

AND l.level_flag = 'L'

AND mtlicf.organization_id (+) = NVL(l.warehouse_id, l.so_organization_id)

AND mtlicf.category_set_id = edcs.category_set_id (+)

AND edcs.functional_area_id = 1

AND mtlcbf.category_id (+) = mtlicf.category_id

AND mtlcsf.category_id (+) = mtlicf.category_id

AND mtlcsf.category_set_id (+) = mtlicf.category_set_id

AND o.organization_id = NVL(l.warehouse_id, l.so_organization_id)

AND l.event_type_code IN ('CM_CREATE','CM_UPDATE')

GROUP BY mtlvl.inventory_item_id ,

mtlvl.global_attribute1 ,

mtlvl.global_attribute10 ,

mtlvl.global_attribute2 ,

mtlvl.global_attribute3 ,

mtlvl.global_attribute4 ,

mtlvl.global_attribute5 ,

mtlvl.global_attribute6 ,

mtlvl.global_attribute7 ,

mtlvl.global_attribute8 ,

mtlvl.global_attribute9 ,

mtlvl.global_attribute_category ,

mtlvl.inventory_item_id ,

mtlvl.item_type ,

mtlvl.sales_account ,

mtlvl.serial_number_control_code ,

mtlicf.category_id ,

l.event_id ,

l.line_number ,

l.language ,

l.ledger_id ,

o.organization_code ,

o.organization_name ,

mtlcbf.category_code