MSC_AP_PRICE_LISTS

Details

Schema: FUSION
Object owner: MSC
Object type: VIEW

Columns

Name
PRICE_LIST_NAME PRICE_LIST_DESCRIPTION ITEM_NAME ORG_LEVEL_NAME ORG_MEMBER_NAME PRICE PRICE_LIST_START_DATE PRICE_LIST_END_DATE ITEM_PRICE_START_DATE ITEM_PRICE_END_DATE PRICE_CURRENCY PRICE_UOM

Name

PRICE_LIST_NAME

PRICE_LIST_DESCRIPTION

ITEM_NAME

ORG_LEVEL_NAME

ORG_MEMBER_NAME

PRICE

PRICE_LIST_START_DATE

PRICE_LIST_END_DATE

ITEM_PRICE_START_DATE

ITEM_PRICE_END_DATE

PRICE_CURRENCY

PRICE_UOM

Query

SQL_Statement
SELECT PL_B.NAME PRICE_LIST_NAME, PL_B.DESCRIPTION PRICE_LIST_DESCRIPTION, MIN(ITM.ITEM_NUMBER) ITEM_NAME, 'BUSINESS UNIT' ORG_LEVEL_NAME, BU.BU_NAME ORG_MEMBER_NAME, NEWTAB.PRICE PRICE, PL_B.START_DATE PRICE_LIST_START_DATE, PL_B.END_DATE PRICE_LIST_END_DATE, PL_CHARGE.START_DATE ITEM_PRICE_START_DATE, PL_CHARGE.END_DATE ITEM_PRICE_END_DATE, PL_B.CURRENCY_CODE PRICE_CURRENCY, PL_ITEMS.PRICING_UOM_CODE PRICE_UOM FROM QP_PRICE_LISTS_VL PL_B, QP_PRICE_LIST_CHARGES PL_CHARGE, QP_PRICE_LIST_ITEMS PL_ITEMS, FUN_BU_USAGES_V BU, MSC_AP_EGP_SYSTEM_ITEMS_V ITM, (SELECT MAX(B.CHARGE_LINE_NUMBER) CHARGE_LINE_NUMBER, A.PRICE, A.PARENT_ENTITY_ID FROM (SELECT MAX(BASE_PRICE) PRICE, PARENT_ENTITY_ID FROM QP_PRICE_LIST_CHARGES WHERE SYSDATE BETWEEN START_DATE AND NVL( END_DATE, SYSDATE + 1 ) AND PRICE_PERIODICITY_CODE IS NULL AND BASE_PRICE > 0 GROUP BY PARENT_ENTITY_ID ) A, QP_PRICE_LIST_CHARGES B WHERE SYSDATE BETWEEN B.START_DATE AND NVL( B.END_DATE, SYSDATE + 1 ) AND B.PRICE_PERIODICITY_CODE IS NULL AND B.BASE_PRICE > 0 AND A.PRICE = B.BASE_PRICE AND A.PARENT_ENTITY_ID = B.PARENT_ENTITY_ID GROUP BY A.PRICE, A.PARENT_ENTITY_ID ) NEWTAB WHERE PL_B.PRICE_LIST_ID = PL_CHARGE.PRICE_LIST_ID AND PL_B.PRICE_LIST_ID = PL_ITEMS.PRICE_LIST_ID AND PL_B.LINE_TYPE_CODE = 'ORA_BUY' AND PL_CHARGE.PRICE_LIST_ID = PL_ITEMS.PRICE_LIST_ID AND PL_CHARGE.BASE_PRICE = NEWTAB.PRICE AND PL_CHARGE.BASE_PRICE > 0 AND PL_CHARGE.PARENT_ENTITY_ID = NEWTAB.PARENT_ENTITY_ID AND PL_CHARGE.PARENT_ENTITY_TYPE_CODE = 'PRICE_LIST_ITEM' AND PL_CHARGE.CHARGE_LINE_NUMBER = NEWTAB.CHARGE_LINE_NUMBER AND PL_CHARGE.PRICE_PERIODICITY_CODE IS NULL AND PL_ITEMS.PRICE_LIST_ITEM_ID = PL_CHARGE.PARENT_ENTITY_ID AND PL_ITEMS.LINE_TYPE_CODE = 'ORA_BUY' AND PL_ITEMS.PRIMARY_PRICING_UOM_FLAG = 'Y' AND PL_ITEMS.ITEM_LEVEL_CODE = 'ITEM' AND BU.BUSINESS_UNIT_ID = PL_B.ORG_ID AND ITM.INVENTORY_ITEM_ID = PL_ITEMS.ITEM_ID AND USAGE_UOM_CODE IS NULL AND SYSDATE BETWEEN PL_CHARGE.START_DATE AND NVL( PL_CHARGE.END_DATE, SYSDATE + 1 ) GROUP BY PL_B.NAME, PL_B.DESCRIPTION, BU.BU_NAME, NEWTAB.PRICE, PL_B.START_DATE, PL_B.END_DATE, PL_CHARGE.START_DATE, PL_CHARGE.END_DATE, PL_B.CURRENCY_CODE, PL_ITEMS.PRICING_UOM_CODE, ITM.ITEM_NUMBER

SQL_Statement

SELECT PL_B.NAME PRICE_LIST_NAME,

PL_B.DESCRIPTION PRICE_LIST_DESCRIPTION,

MIN(ITM.ITEM_NUMBER) ITEM_NAME,

'BUSINESS UNIT' ORG_LEVEL_NAME,

BU.BU_NAME ORG_MEMBER_NAME,

NEWTAB.PRICE PRICE,

PL_B.START_DATE PRICE_LIST_START_DATE,

PL_B.END_DATE PRICE_LIST_END_DATE,

PL_CHARGE.START_DATE ITEM_PRICE_START_DATE,

PL_CHARGE.END_DATE ITEM_PRICE_END_DATE,

PL_B.CURRENCY_CODE PRICE_CURRENCY,

PL_ITEMS.PRICING_UOM_CODE PRICE_UOM

FROM QP_PRICE_LISTS_VL PL_B,

QP_PRICE_LIST_CHARGES PL_CHARGE,

QP_PRICE_LIST_ITEMS PL_ITEMS,

FUN_BU_USAGES_V BU,

MSC_AP_EGP_SYSTEM_ITEMS_V ITM,

(SELECT MAX(B.CHARGE_LINE_NUMBER) CHARGE_LINE_NUMBER,

A.PRICE,

A.PARENT_ENTITY_ID

FROM

(SELECT MAX(BASE_PRICE) PRICE,

PARENT_ENTITY_ID

FROM QP_PRICE_LIST_CHARGES

WHERE SYSDATE BETWEEN START_DATE AND NVL( END_DATE, SYSDATE + 1 )

AND PRICE_PERIODICITY_CODE IS NULL

AND BASE_PRICE > 0

GROUP BY PARENT_ENTITY_ID

) A,

QP_PRICE_LIST_CHARGES B

WHERE SYSDATE BETWEEN B.START_DATE AND NVL( B.END_DATE, SYSDATE + 1 )

AND B.PRICE_PERIODICITY_CODE IS NULL

AND B.BASE_PRICE > 0

AND A.PRICE = B.BASE_PRICE

AND A.PARENT_ENTITY_ID = B.PARENT_ENTITY_ID

GROUP BY A.PRICE,

A.PARENT_ENTITY_ID

) NEWTAB

WHERE PL_B.PRICE_LIST_ID = PL_CHARGE.PRICE_LIST_ID

AND PL_B.PRICE_LIST_ID = PL_ITEMS.PRICE_LIST_ID

AND PL_B.LINE_TYPE_CODE = 'ORA_BUY'

AND PL_CHARGE.PRICE_LIST_ID = PL_ITEMS.PRICE_LIST_ID

AND PL_CHARGE.BASE_PRICE = NEWTAB.PRICE

AND PL_CHARGE.BASE_PRICE > 0

AND PL_CHARGE.PARENT_ENTITY_ID = NEWTAB.PARENT_ENTITY_ID

AND PL_CHARGE.PARENT_ENTITY_TYPE_CODE = 'PRICE_LIST_ITEM'

AND PL_CHARGE.CHARGE_LINE_NUMBER = NEWTAB.CHARGE_LINE_NUMBER

AND PL_CHARGE.PRICE_PERIODICITY_CODE IS NULL

AND PL_ITEMS.PRICE_LIST_ITEM_ID = PL_CHARGE.PARENT_ENTITY_ID

AND PL_ITEMS.LINE_TYPE_CODE = 'ORA_BUY'

AND PL_ITEMS.PRIMARY_PRICING_UOM_FLAG = 'Y'

AND PL_ITEMS.ITEM_LEVEL_CODE = 'ITEM'

AND BU.BUSINESS_UNIT_ID = PL_B.ORG_ID

AND ITM.INVENTORY_ITEM_ID = PL_ITEMS.ITEM_ID

AND USAGE_UOM_CODE IS NULL

AND SYSDATE BETWEEN PL_CHARGE.START_DATE AND NVL( PL_CHARGE.END_DATE, SYSDATE + 1 )

GROUP BY PL_B.NAME,

PL_B.DESCRIPTION,

BU.BU_NAME,

NEWTAB.PRICE,

PL_B.START_DATE,

PL_B.END_DATE,

PL_CHARGE.START_DATE,

PL_CHARGE.END_DATE,

PL_B.CURRENCY_CODE,

PL_ITEMS.PRICING_UOM_CODE,

ITM.ITEM_NUMBER