1. Tables and Views for SCM
  2. CST_XLA_PUR_CATEGORIES_V

CST_XLA_PUR_CATEGORIES_V

Details

  • Schema: FUSION

  • Object owner: CST

  • Object type: VIEW

Columns

Name

INVENTORY_ITEM_ID

INV_ORG_ID

PUR_CATEGORY_ID

PUR_CATEGORY_CODE

PUR_CATEGORY_NAME

PUR_CATEGORY_DESC

PUR_CATEGORY_SEGMENT1

PUR_CATEGORY_SEGMENT2

PUR_CATEGORY_SEGMENT3

PUR_CATEGORY_SEGMENT4

PUR_CATEGORY_SEGMENT5

PUR_CATEGORY_SEGMENT6

PUR_CATEGORY_SEGMENT7

PUR_CATEGORY_SEGMENT8

PUR_CATEGORY_SEGMENT9

PUR_CATEGORY_SEGMENT10

PUR_CATEGORY_SEGMENT11

PUR_CATEGORY_SEGMENT12

PUR_CATEGORY_SEGMENT13

PUR_CATEGORY_SEGMENT14

PUR_CATEGORY_SEGMENT15

PUR_CATEGORY_SEGMENT16

PUR_CATEGORY_SEGMENT17

PUR_CATEGORY_SEGMENT18

PUR_CATEGORY_SEGMENT19

PUR_CATEGORY_SEGMENT20

PUR_CATEGORY_ATTR_CATEGORY

PUR_CATEGORY_ATTRIBUTE1

PUR_CATEGORY_ATTRIBUTE2

PUR_CATEGORY_ATTRIBUTE3

PUR_CATEGORY_ATTRIBUTE4

PUR_CATEGORY_ATTRIBUTE5

PUR_CATEGORY_ATTRIBUTE6

PUR_CATEGORY_ATTRIBUTE7

PUR_CATEGORY_ATTRIBUTE8

PUR_CATEGORY_ATTRIBUTE9

PUR_CATEGORY_ATTRIBUTE10

PUR_CATEGORY_ATTRIBUTE11

PUR_CATEGORY_ATTRIBUTE12

PUR_CATEGORY_ATTRIBUTE13

PUR_CATEGORY_ATTRIBUTE14

PUR_CATEGORY_ATTRIBUTE15

Query

SQL_Statement

SELECT

inventory_item_id,

inv_org_id,

pur_category_id,

pur_category_code,

pur_category_name,

pur_category_desc,

pur_category_segment1,

pur_category_segment2,

pur_category_segment3,

pur_category_segment4,

pur_category_segment5,

pur_category_segment6,

pur_category_segment7,

pur_category_segment8,

pur_category_segment9,

pur_category_segment10,

pur_category_segment11,

pur_category_segment12,

pur_category_segment13,

pur_category_segment14,

pur_category_segment15,

pur_category_segment16,

pur_category_segment17,

pur_category_segment18,

pur_category_segment19,

pur_category_segment20,

pur_category_attr_category,

pur_category_attribute1,

pur_category_attribute2,

pur_category_attribute3,

pur_category_attribute4,

pur_category_attribute5,

pur_category_attribute6,

pur_category_attribute7,

pur_category_attribute8,

pur_category_attribute9,

pur_category_attribute10,

pur_category_attribute11,

pur_category_attribute12,

pur_category_attribute13,

pur_category_attribute14,

pur_category_attribute15

FROM

(

SELECT

eic.inventory_item_id inventory_item_id,

eic.organization_id inv_org_id,

ec.category_id pur_category_id,

ec.category_code pur_category_code,

ec.category_name pur_category_name,

ec.description pur_category_desc,

ec.segment1 pur_category_segment1,

ec.segment2 pur_category_segment2,

ec.segment3 pur_category_segment3,

ec.segment4 pur_category_segment4,

ec.segment5 pur_category_segment5,

ec.segment6 pur_category_segment6,

ec.segment7 pur_category_segment7,

ec.segment8 pur_category_segment8,

ec.segment9 pur_category_segment9,

ec.segment10 pur_category_segment10,

ec.segment11 pur_category_segment11,

ec.segment12 pur_category_segment12,

ec.segment13 pur_category_segment13,

ec.segment14 pur_category_segment14,

ec.segment15 pur_category_segment15,

ec.segment16 pur_category_segment16,

ec.segment17 pur_category_segment17,

ec.segment18 pur_category_segment18,

ec.segment19 pur_category_segment19,

ec.segment20 pur_category_segment20,

ec.attribute_category pur_category_attr_category,

ec.attribute1 pur_category_attribute1,

ec.attribute2 pur_category_attribute2,

ec.attribute3 pur_category_attribute3,

ec.attribute4 pur_category_attribute4,

ec.attribute5 pur_category_attribute5,

ec.attribute6 pur_category_attribute6,

ec.attribute7 pur_category_attribute7,

ec.attribute8 pur_category_attribute8,

ec.attribute9 pur_category_attribute9,

ec.attribute10 pur_category_attribute10,

ec.attribute11 pur_category_attribute11,

ec.attribute12 pur_category_attribute12,

ec.attribute13 pur_category_attribute13,

ec.attribute14 pur_category_attribute14,

ec.attribute15 pur_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 = 2

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