CST_XLA_DOO_CATEGORIES

Details

Schema: FUSION
Object owner: CST
Object type: VIEW

Columns

Name
INVENTORY_ITEM_ID INV_ORG_ID DOO_CATEGORY_ID DOO_CATEGORY_CODE DOO_CATEGORY_NAME DOO_CATEGORY_DESC DOO_CATEGORY_SEGMENT1 DOO_CATEGORY_SEGMENT2 DOO_CATEGORY_SEGMENT3 DOO_CATEGORY_SEGMENT4 DOO_CATEGORY_SEGMENT5 DOO_CATEGORY_SEGMENT6 DOO_CATEGORY_SEGMENT7 DOO_CATEGORY_SEGMENT8 DOO_CATEGORY_SEGMENT9 DOO_CATEGORY_SEGMENT10 DOO_CATEGORY_SEGMENT11 DOO_CATEGORY_SEGMENT12 DOO_CATEGORY_SEGMENT13 DOO_CATEGORY_SEGMENT14 DOO_CATEGORY_SEGMENT15 DOO_CATEGORY_SEGMENT16 DOO_CATEGORY_SEGMENT17 DOO_CATEGORY_SEGMENT18 DOO_CATEGORY_SEGMENT19 DOO_CATEGORY_SEGMENT20 DOO_CATEGORY_ATTR_CATEGORY DOO_CATEGORY_ATTRIBUTE1 DOO_CATEGORY_ATTRIBUTE2 DOO_CATEGORY_ATTRIBUTE3 DOO_CATEGORY_ATTRIBUTE4 DOO_CATEGORY_ATTRIBUTE5 DOO_CATEGORY_ATTRIBUTE6 DOO_CATEGORY_ATTRIBUTE7 DOO_CATEGORY_ATTRIBUTE8 DOO_CATEGORY_ATTRIBUTE9 DOO_CATEGORY_ATTRIBUTE10 DOO_CATEGORY_ATTRIBUTE11 DOO_CATEGORY_ATTRIBUTE12 DOO_CATEGORY_ATTRIBUTE13 DOO_CATEGORY_ATTRIBUTE14 DOO_CATEGORY_ATTRIBUTE15

Name

INVENTORY_ITEM_ID

INV_ORG_ID

DOO_CATEGORY_ID

DOO_CATEGORY_CODE

DOO_CATEGORY_NAME

DOO_CATEGORY_DESC

DOO_CATEGORY_SEGMENT1

DOO_CATEGORY_SEGMENT2

DOO_CATEGORY_SEGMENT3

DOO_CATEGORY_SEGMENT4

DOO_CATEGORY_SEGMENT5

DOO_CATEGORY_SEGMENT6

DOO_CATEGORY_SEGMENT7

DOO_CATEGORY_SEGMENT8

DOO_CATEGORY_SEGMENT9

DOO_CATEGORY_SEGMENT10

DOO_CATEGORY_SEGMENT11

DOO_CATEGORY_SEGMENT12

DOO_CATEGORY_SEGMENT13

DOO_CATEGORY_SEGMENT14

DOO_CATEGORY_SEGMENT15

DOO_CATEGORY_SEGMENT16

DOO_CATEGORY_SEGMENT17

DOO_CATEGORY_SEGMENT18

DOO_CATEGORY_SEGMENT19

DOO_CATEGORY_SEGMENT20

DOO_CATEGORY_ATTR_CATEGORY

DOO_CATEGORY_ATTRIBUTE1

DOO_CATEGORY_ATTRIBUTE2

DOO_CATEGORY_ATTRIBUTE3

DOO_CATEGORY_ATTRIBUTE4

DOO_CATEGORY_ATTRIBUTE5

DOO_CATEGORY_ATTRIBUTE6

DOO_CATEGORY_ATTRIBUTE7

DOO_CATEGORY_ATTRIBUTE8

DOO_CATEGORY_ATTRIBUTE9

DOO_CATEGORY_ATTRIBUTE10

DOO_CATEGORY_ATTRIBUTE11

DOO_CATEGORY_ATTRIBUTE12

DOO_CATEGORY_ATTRIBUTE13

DOO_CATEGORY_ATTRIBUTE14

DOO_CATEGORY_ATTRIBUTE15

Query

SQL_Statement
SELECT inventory_item_id, inv_org_id, doo_category_id, doo_category_code, doo_category_name, doo_category_desc, doo_category_segment1, doo_category_segment2, doo_category_segment3, doo_category_segment4, doo_category_segment5, doo_category_segment6, doo_category_segment7, doo_category_segment8, doo_category_segment9, doo_category_segment10, doo_category_segment11, doo_category_segment12, doo_category_segment13, doo_category_segment14, doo_category_segment15, doo_category_segment16, doo_category_segment17, doo_category_segment18, doo_category_segment19, doo_category_segment20, doo_category_attr_category, doo_category_attribute1, doo_category_attribute2, doo_category_attribute3, doo_category_attribute4, doo_category_attribute5, doo_category_attribute6, doo_category_attribute7, doo_category_attribute8, doo_category_attribute9, doo_category_attribute10, doo_category_attribute11, doo_category_attribute12, doo_category_attribute13, doo_category_attribute14, doo_category_attribute15 FROM ( SELECT eic.inventory_item_id inventory_item_id, eic.organization_id inv_org_id, ec.category_id doo_category_id, ec.category_code doo_category_code, ec.category_name doo_category_name, ec.description doo_category_desc, ec.segment1 doo_category_segment1, ec.segment2 doo_category_segment2, ec.segment3 doo_category_segment3, ec.segment4 doo_category_segment4, ec.segment5 doo_category_segment5, ec.segment6 doo_category_segment6, ec.segment7 doo_category_segment7, ec.segment8 doo_category_segment8, ec.segment9 doo_category_segment9, ec.segment10 doo_category_segment10, ec.segment11 doo_category_segment11, ec.segment12 doo_category_segment12, ec.segment13 doo_category_segment13, ec.segment14 doo_category_segment14, ec.segment15 doo_category_segment15, ec.segment16 doo_category_segment16, ec.segment17 doo_category_segment17, ec.segment18 doo_category_segment18, ec.segment19 doo_category_segment19, ec.segment20 doo_category_segment20, ec.attribute_category doo_category_attr_category, ec.attribute1 doo_category_attribute1, ec.attribute2 doo_category_attribute2, ec.attribute3 doo_category_attribute3, ec.attribute4 doo_category_attribute4, ec.attribute5 doo_category_attribute5, ec.attribute6 doo_category_attribute6, ec.attribute7 doo_category_attribute7, ec.attribute8 doo_category_attribute8, ec.attribute9 doo_category_attribute9, ec.attribute10 doo_category_attribute10, ec.attribute11 doo_category_attribute11, ec.attribute12 doo_category_attribute12, ec.attribute13 doo_category_attribute13, ec.attribute14 doo_category_attribute14, ec.attribute15 doo_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 = 24 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,

doo_category_id,

doo_category_code,

doo_category_name,

doo_category_desc,

doo_category_segment1,

doo_category_segment2,

doo_category_segment3,

doo_category_segment4,

doo_category_segment5,

doo_category_segment6,

doo_category_segment7,

doo_category_segment8,

doo_category_segment9,

doo_category_segment10,

doo_category_segment11,

doo_category_segment12,

doo_category_segment13,

doo_category_segment14,

doo_category_segment15,

doo_category_segment16,

doo_category_segment17,

doo_category_segment18,

doo_category_segment19,

doo_category_segment20,

doo_category_attr_category,

doo_category_attribute1,

doo_category_attribute2,

doo_category_attribute3,

doo_category_attribute4,

doo_category_attribute5,

doo_category_attribute6,

doo_category_attribute7,

doo_category_attribute8,

doo_category_attribute9,

doo_category_attribute10,

doo_category_attribute11,

doo_category_attribute12,

doo_category_attribute13,

doo_category_attribute14,

doo_category_attribute15

FROM

(

SELECT

eic.inventory_item_id inventory_item_id,

eic.organization_id inv_org_id,

ec.category_id doo_category_id,

ec.category_code doo_category_code,

ec.category_name doo_category_name,

ec.description doo_category_desc,

ec.segment1 doo_category_segment1,

ec.segment2 doo_category_segment2,

ec.segment3 doo_category_segment3,

ec.segment4 doo_category_segment4,

ec.segment5 doo_category_segment5,

ec.segment6 doo_category_segment6,

ec.segment7 doo_category_segment7,

ec.segment8 doo_category_segment8,

ec.segment9 doo_category_segment9,

ec.segment10 doo_category_segment10,

ec.segment11 doo_category_segment11,

ec.segment12 doo_category_segment12,

ec.segment13 doo_category_segment13,

ec.segment14 doo_category_segment14,

ec.segment15 doo_category_segment15,

ec.segment16 doo_category_segment16,

ec.segment17 doo_category_segment17,

ec.segment18 doo_category_segment18,

ec.segment19 doo_category_segment19,

ec.segment20 doo_category_segment20,

ec.attribute_category doo_category_attr_category,

ec.attribute1 doo_category_attribute1,

ec.attribute2 doo_category_attribute2,

ec.attribute3 doo_category_attribute3,

ec.attribute4 doo_category_attribute4,

ec.attribute5 doo_category_attribute5,

ec.attribute6 doo_category_attribute6,

ec.attribute7 doo_category_attribute7,

ec.attribute8 doo_category_attribute8,

ec.attribute9 doo_category_attribute9,

ec.attribute10 doo_category_attribute10,

ec.attribute11 doo_category_attribute11,

ec.attribute12 doo_category_attribute12,

ec.attribute13 doo_category_attribute13,

ec.attribute14 doo_category_attribute14,

ec.attribute15 doo_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 = 24

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