CST_XLA_CST_CATEGORIES

Details

Schema: FUSION
Object owner: CST
Object type: VIEW

Columns

Name
INVENTORY_ITEM_ID INV_ORG_ID CST_CATEGORY_ID CST_CATEGORY_CODE CST_CATEGORY_NAME CST_CATEGORY_DESC CST_CATEGORY_SEGMENT1 CST_CATEGORY_SEGMENT2 CST_CATEGORY_SEGMENT3 CST_CATEGORY_SEGMENT4 CST_CATEGORY_SEGMENT5 CST_CATEGORY_SEGMENT6 CST_CATEGORY_SEGMENT7 CST_CATEGORY_SEGMENT8 CST_CATEGORY_SEGMENT9 CST_CATEGORY_SEGMENT10 CST_CATEGORY_SEGMENT11 CST_CATEGORY_SEGMENT12 CST_CATEGORY_SEGMENT13 CST_CATEGORY_SEGMENT14 CST_CATEGORY_SEGMENT15 CST_CATEGORY_SEGMENT16 CST_CATEGORY_SEGMENT17 CST_CATEGORY_SEGMENT18 CST_CATEGORY_SEGMENT19 CST_CATEGORY_SEGMENT20 CST_CATEGORY_ATTR_CATEGORY CST_CATEGORY_ATTRIBUTE1 CST_CATEGORY_ATTRIBUTE2 CST_CATEGORY_ATTRIBUTE3 CST_CATEGORY_ATTRIBUTE4 CST_CATEGORY_ATTRIBUTE5 CST_CATEGORY_ATTRIBUTE6 CST_CATEGORY_ATTRIBUTE7 CST_CATEGORY_ATTRIBUTE8 CST_CATEGORY_ATTRIBUTE9 CST_CATEGORY_ATTRIBUTE10 CST_CATEGORY_ATTRIBUTE11 CST_CATEGORY_ATTRIBUTE12 CST_CATEGORY_ATTRIBUTE13 CST_CATEGORY_ATTRIBUTE14 CST_CATEGORY_ATTRIBUTE15

Name

INVENTORY_ITEM_ID

INV_ORG_ID

CST_CATEGORY_ID

CST_CATEGORY_CODE

CST_CATEGORY_NAME

CST_CATEGORY_DESC

CST_CATEGORY_SEGMENT1

CST_CATEGORY_SEGMENT2

CST_CATEGORY_SEGMENT3

CST_CATEGORY_SEGMENT4

CST_CATEGORY_SEGMENT5

CST_CATEGORY_SEGMENT6

CST_CATEGORY_SEGMENT7

CST_CATEGORY_SEGMENT8

CST_CATEGORY_SEGMENT9

CST_CATEGORY_SEGMENT10

CST_CATEGORY_SEGMENT11

CST_CATEGORY_SEGMENT12

CST_CATEGORY_SEGMENT13

CST_CATEGORY_SEGMENT14

CST_CATEGORY_SEGMENT15

CST_CATEGORY_SEGMENT16

CST_CATEGORY_SEGMENT17

CST_CATEGORY_SEGMENT18

CST_CATEGORY_SEGMENT19

CST_CATEGORY_SEGMENT20

CST_CATEGORY_ATTR_CATEGORY

CST_CATEGORY_ATTRIBUTE1

CST_CATEGORY_ATTRIBUTE2

CST_CATEGORY_ATTRIBUTE3

CST_CATEGORY_ATTRIBUTE4

CST_CATEGORY_ATTRIBUTE5

CST_CATEGORY_ATTRIBUTE6

CST_CATEGORY_ATTRIBUTE7

CST_CATEGORY_ATTRIBUTE8

CST_CATEGORY_ATTRIBUTE9

CST_CATEGORY_ATTRIBUTE10

CST_CATEGORY_ATTRIBUTE11

CST_CATEGORY_ATTRIBUTE12

CST_CATEGORY_ATTRIBUTE13

CST_CATEGORY_ATTRIBUTE14

CST_CATEGORY_ATTRIBUTE15

Query

SQL_Statement
SELECT inventory_item_id, inv_org_id, cst_category_id, cst_category_code, cst_category_name, cst_category_desc, cst_category_segment1, cst_category_segment2, cst_category_segment3, cst_category_segment4, cst_category_segment5, cst_category_segment6, cst_category_segment7, cst_category_segment8, cst_category_segment9, cst_category_segment10, cst_category_segment11, cst_category_segment12, cst_category_segment13, cst_category_segment14, cst_category_segment15, cst_category_segment16, cst_category_segment17, cst_category_segment18, cst_category_segment19, cst_category_segment20, cst_category_attr_category, cst_category_attribute1, cst_category_attribute2, cst_category_attribute3, cst_category_attribute4, cst_category_attribute5, cst_category_attribute6, cst_category_attribute7, cst_category_attribute8, cst_category_attribute9, cst_category_attribute10, cst_category_attribute11, cst_category_attribute12, cst_category_attribute13, cst_category_attribute14, cst_category_attribute15 FROM ( SELECT eic.inventory_item_id inventory_item_id, eic.organization_id inv_org_id, ec.category_id cst_category_id, ec.category_code cst_category_code, ec.category_name cst_category_name, ec.description cst_category_desc, ec.segment1 cst_category_segment1, ec.segment2 cst_category_segment2, ec.segment3 cst_category_segment3, ec.segment4 cst_category_segment4, ec.segment5 cst_category_segment5, ec.segment6 cst_category_segment6, ec.segment7 cst_category_segment7, ec.segment8 cst_category_segment8, ec.segment9 cst_category_segment9, ec.segment10 cst_category_segment10, ec.segment11 cst_category_segment11, ec.segment12 cst_category_segment12, ec.segment13 cst_category_segment13, ec.segment14 cst_category_segment14, ec.segment15 cst_category_segment15, ec.segment16 cst_category_segment16, ec.segment17 cst_category_segment17, ec.segment18 cst_category_segment18, ec.segment19 cst_category_segment19, ec.segment20 cst_category_segment20, ec.attribute_category cst_category_attr_category, ec.attribute1 cst_category_attribute1, ec.attribute2 cst_category_attribute2, ec.attribute3 cst_category_attribute3, ec.attribute4 cst_category_attribute4, ec.attribute5 cst_category_attribute5, ec.attribute6 cst_category_attribute6, ec.attribute7 cst_category_attribute7, ec.attribute8 cst_category_attribute8, ec.attribute9 cst_category_attribute9, ec.attribute10 cst_category_attribute10, ec.attribute11 cst_category_attribute11, ec.attribute12 cst_category_attribute12, ec.attribute13 cst_category_attribute13, ec.attribute14 cst_category_attribute14, ec.attribute15 cst_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 = 5 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,

cst_category_id,

cst_category_code,

cst_category_name,

cst_category_desc,

cst_category_segment1,

cst_category_segment2,

cst_category_segment3,

cst_category_segment4,

cst_category_segment5,

cst_category_segment6,

cst_category_segment7,

cst_category_segment8,

cst_category_segment9,

cst_category_segment10,

cst_category_segment11,

cst_category_segment12,

cst_category_segment13,

cst_category_segment14,

cst_category_segment15,

cst_category_segment16,

cst_category_segment17,

cst_category_segment18,

cst_category_segment19,

cst_category_segment20,

cst_category_attr_category,

cst_category_attribute1,

cst_category_attribute2,

cst_category_attribute3,

cst_category_attribute4,

cst_category_attribute5,

cst_category_attribute6,

cst_category_attribute7,

cst_category_attribute8,

cst_category_attribute9,

cst_category_attribute10,

cst_category_attribute11,

cst_category_attribute12,

cst_category_attribute13,

cst_category_attribute14,

cst_category_attribute15

FROM

(

SELECT

eic.inventory_item_id inventory_item_id,

eic.organization_id inv_org_id,

ec.category_id cst_category_id,

ec.category_code cst_category_code,

ec.category_name cst_category_name,

ec.description cst_category_desc,

ec.segment1 cst_category_segment1,

ec.segment2 cst_category_segment2,

ec.segment3 cst_category_segment3,

ec.segment4 cst_category_segment4,

ec.segment5 cst_category_segment5,

ec.segment6 cst_category_segment6,

ec.segment7 cst_category_segment7,

ec.segment8 cst_category_segment8,

ec.segment9 cst_category_segment9,

ec.segment10 cst_category_segment10,

ec.segment11 cst_category_segment11,

ec.segment12 cst_category_segment12,

ec.segment13 cst_category_segment13,

ec.segment14 cst_category_segment14,

ec.segment15 cst_category_segment15,

ec.segment16 cst_category_segment16,

ec.segment17 cst_category_segment17,

ec.segment18 cst_category_segment18,

ec.segment19 cst_category_segment19,

ec.segment20 cst_category_segment20,

ec.attribute_category cst_category_attr_category,

ec.attribute1 cst_category_attribute1,

ec.attribute2 cst_category_attribute2,

ec.attribute3 cst_category_attribute3,

ec.attribute4 cst_category_attribute4,

ec.attribute5 cst_category_attribute5,

ec.attribute6 cst_category_attribute6,

ec.attribute7 cst_category_attribute7,

ec.attribute8 cst_category_attribute8,

ec.attribute9 cst_category_attribute9,

ec.attribute10 cst_category_attribute10,

ec.attribute11 cst_category_attribute11,

ec.attribute12 cst_category_attribute12,

ec.attribute13 cst_category_attribute13,

ec.attribute14 cst_category_attribute14,

ec.attribute15 cst_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 = 5

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