VIEW: APPS.QPR_SR_PC_HIER_V

Source

View Text - Preformatted

SELECT distinct msiv.inventory_item_id, msiv.concatenated_segments, msiv_tl.description , null, null, null, null, decode( cat.mult_item_cat_assign_flag, 'Y', qpr_sr_util.get_null_pk, decode( cat.category_id, null, qpr_sr_util.get_null_pk, cat.category_id)), decode( cat.mult_item_cat_assign_flag, 'Y', qpr_sr_util.get_null_desc, decode( cat.category_id, null, qpr_sr_util.get_null_desc, cat.concatenated_segments)) , null, null, null, null, null, qpr_sr_util.get_all_prd_pk, qpr_sr_util.get_all_prd_desc, null, null, null, null, null, qpr_sr_util.get_max_date( qpr_sr_util.get_max_date(trunc(msiv.last_update_date, 'dd'), trunc(msiv_tl.last_update_date, 'dd')), trunc(cat.last_update_date, 'dd')) FROM mtl_system_items_kfv msiv, mtl_system_items_tl msiv_tl, (select micv.inventory_item_id, micv.organization_id, mcs.mult_item_cat_assign_flag, micv.category_id, mc.concatenated_segments, micv.category_set_id, qpr_sr_util.get_max_date( qpr_sr_util.get_max_date(trunc(micv.last_update_date, 'dd'), trunc(mc.last_update_date, 'dd')) , trunc(mcs.last_update_date, 'dd')) last_update_date from mtl_item_categories micv, mtl_categories_b_kfv mc, mtl_category_sets_b mcs where to_char(mcs.category_set_id) = nvl(fnd_profile.value('QPR_CATEGORY_SET_NAME'), mcs.category_set_id) and micv.category_set_id = mcs.category_set_id and mc.category_id = micv.category_id ) cat WHERE msiv.inventory_item_id = msiv_tl.inventory_item_id AND msiv.organization_id = msiv_tl.organization_id and msiv.organization_id = (fnd_profile.value('QPR_MASTER_ORG')) AND msiv_tl.LANGUAGE = userenv('LANG') AND msiv.customer_order_flag = 'Y' AND msiv.inventory_item_id = cat.inventory_item_id(+) AND msiv.organization_id = cat.organization_id(+)

View Text - HTML Formatted

SELECT DISTINCT MSIV.INVENTORY_ITEM_ID
, MSIV.CONCATENATED_SEGMENTS
, MSIV_TL.DESCRIPTION
, NULL
, NULL
, NULL
, NULL
, DECODE( CAT.MULT_ITEM_CAT_ASSIGN_FLAG
, 'Y'
, QPR_SR_UTIL.GET_NULL_PK
, DECODE( CAT.CATEGORY_ID
, NULL
, QPR_SR_UTIL.GET_NULL_PK
, CAT.CATEGORY_ID))
, DECODE( CAT.MULT_ITEM_CAT_ASSIGN_FLAG
, 'Y'
, QPR_SR_UTIL.GET_NULL_DESC
, DECODE( CAT.CATEGORY_ID
, NULL
, QPR_SR_UTIL.GET_NULL_DESC
, CAT.CONCATENATED_SEGMENTS))
, NULL
, NULL
, NULL
, NULL
, NULL
, QPR_SR_UTIL.GET_ALL_PRD_PK
, QPR_SR_UTIL.GET_ALL_PRD_DESC
, NULL
, NULL
, NULL
, NULL
, NULL
, QPR_SR_UTIL.GET_MAX_DATE( QPR_SR_UTIL.GET_MAX_DATE(TRUNC(MSIV.LAST_UPDATE_DATE
, 'DD')
, TRUNC(MSIV_TL.LAST_UPDATE_DATE
, 'DD'))
, TRUNC(CAT.LAST_UPDATE_DATE
, 'DD'))
FROM MTL_SYSTEM_ITEMS_KFV MSIV
, MTL_SYSTEM_ITEMS_TL MSIV_TL
, (SELECT MICV.INVENTORY_ITEM_ID
, MICV.ORGANIZATION_ID
, MCS.MULT_ITEM_CAT_ASSIGN_FLAG
, MICV.CATEGORY_ID
, MC.CONCATENATED_SEGMENTS
, MICV.CATEGORY_SET_ID
, QPR_SR_UTIL.GET_MAX_DATE( QPR_SR_UTIL.GET_MAX_DATE(TRUNC(MICV.LAST_UPDATE_DATE
, 'DD')
, TRUNC(MC.LAST_UPDATE_DATE
, 'DD'))
, TRUNC(MCS.LAST_UPDATE_DATE
, 'DD')) LAST_UPDATE_DATE
FROM MTL_ITEM_CATEGORIES MICV
, MTL_CATEGORIES_B_KFV MC
, MTL_CATEGORY_SETS_B MCS
WHERE TO_CHAR(MCS.CATEGORY_SET_ID) = NVL(FND_PROFILE.VALUE('QPR_CATEGORY_SET_NAME')
, MCS.CATEGORY_SET_ID)
AND MICV.CATEGORY_SET_ID = MCS.CATEGORY_SET_ID
AND MC.CATEGORY_ID = MICV.CATEGORY_ID ) CAT
WHERE MSIV.INVENTORY_ITEM_ID = MSIV_TL.INVENTORY_ITEM_ID
AND MSIV.ORGANIZATION_ID = MSIV_TL.ORGANIZATION_ID
AND MSIV.ORGANIZATION_ID = (FND_PROFILE.VALUE('QPR_MASTER_ORG'))
AND MSIV_TL.LANGUAGE = USERENV('LANG')
AND MSIV.CUSTOMER_ORDER_FLAG = 'Y'
AND MSIV.INVENTORY_ITEM_ID = CAT.INVENTORY_ITEM_ID(+)
AND MSIV.ORGANIZATION_ID = CAT.ORGANIZATION_ID(+)