CST_XLA_INV_CATEGORIES

Details

Schema: FUSION
Object owner: CST
Object type: VIEW

Columns

Name
INVENTORY_ITEM_ID INV_ORG_ID INV_CATEGORY_ID INV_CATEGORY_CODE INV_CATEGORY_NAME INV_CATEGORY_DESC INV_CATEGORY_SEGMENT1 INV_CATEGORY_SEGMENT2 INV_CATEGORY_SEGMENT3 INV_CATEGORY_SEGMENT4 INV_CATEGORY_SEGMENT5 INV_CATEGORY_SEGMENT6 INV_CATEGORY_SEGMENT7 INV_CATEGORY_SEGMENT8 INV_CATEGORY_SEGMENT9 INV_CATEGORY_SEGMENT10 INV_CATEGORY_SEGMENT11 INV_CATEGORY_SEGMENT12 INV_CATEGORY_SEGMENT13 INV_CATEGORY_SEGMENT14 INV_CATEGORY_SEGMENT15 INV_CATEGORY_SEGMENT16 INV_CATEGORY_SEGMENT17 INV_CATEGORY_SEGMENT18 INV_CATEGORY_SEGMENT19 INV_CATEGORY_SEGMENT20 INV_CATEGORY_ATTR_CATEGORY INV_CATEGORY_ATTRIBUTE1 INV_CATEGORY_ATTRIBUTE2 INV_CATEGORY_ATTRIBUTE3 INV_CATEGORY_ATTRIBUTE4 INV_CATEGORY_ATTRIBUTE5 INV_CATEGORY_ATTRIBUTE6 INV_CATEGORY_ATTRIBUTE7 INV_CATEGORY_ATTRIBUTE8 INV_CATEGORY_ATTRIBUTE9 INV_CATEGORY_ATTRIBUTE10 INV_CATEGORY_ATTRIBUTE11 INV_CATEGORY_ATTRIBUTE12 INV_CATEGORY_ATTRIBUTE13 INV_CATEGORY_ATTRIBUTE14 INV_CATEGORY_ATTRIBUTE15

Name

INVENTORY_ITEM_ID

INV_ORG_ID

INV_CATEGORY_ID

INV_CATEGORY_CODE

INV_CATEGORY_NAME

INV_CATEGORY_DESC

INV_CATEGORY_SEGMENT1

INV_CATEGORY_SEGMENT2

INV_CATEGORY_SEGMENT3

INV_CATEGORY_SEGMENT4

INV_CATEGORY_SEGMENT5

INV_CATEGORY_SEGMENT6

INV_CATEGORY_SEGMENT7

INV_CATEGORY_SEGMENT8

INV_CATEGORY_SEGMENT9

INV_CATEGORY_SEGMENT10

INV_CATEGORY_SEGMENT11

INV_CATEGORY_SEGMENT12

INV_CATEGORY_SEGMENT13

INV_CATEGORY_SEGMENT14

INV_CATEGORY_SEGMENT15

INV_CATEGORY_SEGMENT16

INV_CATEGORY_SEGMENT17

INV_CATEGORY_SEGMENT18

INV_CATEGORY_SEGMENT19

INV_CATEGORY_SEGMENT20

INV_CATEGORY_ATTR_CATEGORY

INV_CATEGORY_ATTRIBUTE1

INV_CATEGORY_ATTRIBUTE2

INV_CATEGORY_ATTRIBUTE3

INV_CATEGORY_ATTRIBUTE4

INV_CATEGORY_ATTRIBUTE5

INV_CATEGORY_ATTRIBUTE6

INV_CATEGORY_ATTRIBUTE7

INV_CATEGORY_ATTRIBUTE8

INV_CATEGORY_ATTRIBUTE9

INV_CATEGORY_ATTRIBUTE10

INV_CATEGORY_ATTRIBUTE11

INV_CATEGORY_ATTRIBUTE12

INV_CATEGORY_ATTRIBUTE13

INV_CATEGORY_ATTRIBUTE14

INV_CATEGORY_ATTRIBUTE15

Query

SQL_Statement
SELECT inventory_item_id, inv_org_id, inv_category_id, inv_category_code, inv_category_name, inv_category_desc, inv_category_segment1, inv_category_segment2, inv_category_segment3, inv_category_segment4, inv_category_segment5, inv_category_segment6, inv_category_segment7, inv_category_segment8, inv_category_segment9, inv_category_segment10, inv_category_segment11, inv_category_segment12, inv_category_segment13, inv_category_segment14, inv_category_segment15, inv_category_segment16, inv_category_segment17, inv_category_segment18, inv_category_segment19, inv_category_segment20, inv_category_attr_category, inv_category_attribute1, inv_category_attribute2, inv_category_attribute3, inv_category_attribute4, inv_category_attribute5, inv_category_attribute6, inv_category_attribute7, inv_category_attribute8, inv_category_attribute9, inv_category_attribute10, inv_category_attribute11, inv_category_attribute12, inv_category_attribute13, inv_category_attribute14, inv_category_attribute15 FROM ( SELECT eic.inventory_item_id inventory_item_id, eic.organization_id inv_org_id, ec.category_id inv_category_id, ec.category_code inv_category_code, ec.category_name inv_category_name, ec.description inv_category_desc, ec.segment1 inv_category_segment1, ec.segment2 inv_category_segment2, ec.segment3 inv_category_segment3, ec.segment4 inv_category_segment4, ec.segment5 inv_category_segment5, ec.segment6 inv_category_segment6, ec.segment7 inv_category_segment7, ec.segment8 inv_category_segment8, ec.segment9 inv_category_segment9, ec.segment10 inv_category_segment10, ec.segment11 inv_category_segment11, ec.segment12 inv_category_segment12, ec.segment13 inv_category_segment13, ec.segment14 inv_category_segment14, ec.segment15 inv_category_segment15, ec.segment16 inv_category_segment16, ec.segment17 inv_category_segment17, ec.segment18 inv_category_segment18, ec.segment19 inv_category_segment19, ec.segment20 inv_category_segment20, ec.attribute_category inv_category_attr_category, ec.attribute1 inv_category_attribute1, ec.attribute2 inv_category_attribute2, ec.attribute3 inv_category_attribute3, ec.attribute4 inv_category_attribute4, ec.attribute5 inv_category_attribute5, ec.attribute6 inv_category_attribute6, ec.attribute7 inv_category_attribute7, ec.attribute8 inv_category_attribute8, ec.attribute9 inv_category_attribute9, ec.attribute10 inv_category_attribute10, ec.attribute11 inv_category_attribute11, ec.attribute12 inv_category_attribute12, ec.attribute13 inv_category_attribute13, ec.attribute14 inv_category_attribute14, ec.attribute15 inv_category_attribute15, ROW_NUMBER() OVER(PARTITION BY eic.inventory_item_id, eic.organization_id, eic.category_set_id ORDER BY eic.item_category_assignment_id DESC) rank FROM egp_categories_vl ec, egp_item_categories eic, egp_default_category_sets edcs, egp_category_sets_b ecs WHERE edcs.functional_area_id = 1 AND edcs.category_set_id = eic.category_set_id AND edcs.category_set_id = ecs.category_set_id AND ec.category_id = eic.category_id ) WHERE rank = 1

SQL_Statement

SELECT

inventory_item_id,

inv_org_id,

inv_category_id,

inv_category_code,

inv_category_name,

inv_category_desc,

inv_category_segment1,

inv_category_segment2,

inv_category_segment3,

inv_category_segment4,

inv_category_segment5,

inv_category_segment6,

inv_category_segment7,

inv_category_segment8,

inv_category_segment9,

inv_category_segment10,

inv_category_segment11,

inv_category_segment12,

inv_category_segment13,

inv_category_segment14,

inv_category_segment15,

inv_category_segment16,

inv_category_segment17,

inv_category_segment18,

inv_category_segment19,

inv_category_segment20,

inv_category_attr_category,

inv_category_attribute1,

inv_category_attribute2,

inv_category_attribute3,

inv_category_attribute4,

inv_category_attribute5,

inv_category_attribute6,

inv_category_attribute7,

inv_category_attribute8,

inv_category_attribute9,

inv_category_attribute10,

inv_category_attribute11,

inv_category_attribute12,

inv_category_attribute13,

inv_category_attribute14,

inv_category_attribute15

FROM

(

SELECT

eic.inventory_item_id inventory_item_id,

eic.organization_id inv_org_id,

ec.category_id inv_category_id,

ec.category_code inv_category_code,

ec.category_name inv_category_name,

ec.description inv_category_desc,

ec.segment1 inv_category_segment1,

ec.segment2 inv_category_segment2,

ec.segment3 inv_category_segment3,

ec.segment4 inv_category_segment4,

ec.segment5 inv_category_segment5,

ec.segment6 inv_category_segment6,

ec.segment7 inv_category_segment7,

ec.segment8 inv_category_segment8,

ec.segment9 inv_category_segment9,

ec.segment10 inv_category_segment10,

ec.segment11 inv_category_segment11,

ec.segment12 inv_category_segment12,

ec.segment13 inv_category_segment13,

ec.segment14 inv_category_segment14,

ec.segment15 inv_category_segment15,

ec.segment16 inv_category_segment16,

ec.segment17 inv_category_segment17,

ec.segment18 inv_category_segment18,

ec.segment19 inv_category_segment19,

ec.segment20 inv_category_segment20,

ec.attribute_category inv_category_attr_category,

ec.attribute1 inv_category_attribute1,

ec.attribute2 inv_category_attribute2,

ec.attribute3 inv_category_attribute3,

ec.attribute4 inv_category_attribute4,

ec.attribute5 inv_category_attribute5,

ec.attribute6 inv_category_attribute6,

ec.attribute7 inv_category_attribute7,

ec.attribute8 inv_category_attribute8,

ec.attribute9 inv_category_attribute9,

ec.attribute10 inv_category_attribute10,

ec.attribute11 inv_category_attribute11,

ec.attribute12 inv_category_attribute12,

ec.attribute13 inv_category_attribute13,

ec.attribute14 inv_category_attribute14,

ec.attribute15 inv_category_attribute15,

ROW_NUMBER()

OVER(PARTITION BY eic.inventory_item_id, eic.organization_id, eic.category_set_id

ORDER BY eic.item_category_assignment_id DESC) rank

FROM

egp_categories_vl ec,

egp_item_categories eic,

egp_default_category_sets edcs,

egp_category_sets_b ecs

WHERE

edcs.functional_area_id = 1

AND edcs.category_set_id = eic.category_set_id

AND edcs.category_set_id = ecs.category_set_id

AND ec.category_id = eic.category_id

)

WHERE

rank = 1