Query for Secondary Price list for customer account
The pricing engine uses secondary price lists when it cannot determine the price for an item using the price list assigned to an order. Primary and secondary price lists have the same currency. If the item you are ordering is not in the primary price list, the pricing engine looks for the price on any attached secondary price list. If the item is found, the pricing engine uses the highest precedence secondary price list (the secondary price list with the lowest value in the precedence field).
Here in this query,we have joins for customer,price list,sales order and GL tables.
SELECT DISTINCT HCSUA.PRICE_LIST_ID,
SECONDARY_PRICE_LIST.NAME PRICE_LIST,
HCAA.ACCOUNT_NUMBER,
HCAA.ACCOUNT_NAME,
HCSUA.SITE_USE_CODE,
HCSUA.LOCATION,
LOC.ADDRESS1,
LOC.ADDRESS2,
LOC.ADDRESS3,
LOC.ADDRESS4,
LOC.CITY,
LOC.POSTAL_CODE,
LOC.STATE,
ABC.FLEX_VALUE "PRODUCT",
ABC.DESCRIPTION "PRODUCT DESCRIPTION",
PARTY_SITE.PARTY_SITE_NUMBER,
SECONDARY_PRICE_LIST.PRECEDENCE,
QLH2.CURRENCY_CODE CURRENCY,
QLH2.ATTRIBUTE1 PL_TYPE,
QLL2.PRODUCT_ATTR_VAL_DISP ITEM_NUMBER,
(SELECT REPLACE(DESCRIPTION, CHR(13), '')
FROM APPS.MTL_SYSTEM_ITEMS
WHERE SEGMENT1 = QLL2.PRODUCT_ATTR_VAL_DISP
AND ROWNUM = 1) ITEM_DESCRIPTION,
QLL2.OPERAND PRICE,
QLL2.PRODUCT_UOM_CODE UOM,
QLL2.ATTRIBUTE1 FREE_DAYS,
QLL2.ATTRIBUTE2 PROGRESSIVE_DAYS,
QLL2.ATTRIBUTE3 CYLINDER_GROUP_CODE,
QLL2.ATTRIBUTE4 MIN_CHARGE,
QLL2.ATTRIBUTE5 MAX_CHARGE,
QLL2.ATTRIBUTE6 EXEMPT
FROM APPS.HZ_CUST_ACCOUNTS_ALL HCAA,
APPS.HZ_CUST_ACCT_SITES_ALL HCASA,
APPS.HZ_CUST_SITE_USES_ALL HCSUA,
APPS.HZ_LOCATIONS LOC,
APPS.HZ_PARTY_SITES PARTY_SITE,
APPS.HZ_PARTIES HP,
APPS.QP_LIST_HEADERS QLH,
APPS.QP_LIST_LINES_V QLL,
APPS.QP_LIST_HEADERS QLH2,
APPS.QP_LIST_LINES_V QLL2,
APPS.MTL_SYSTEM_ITEMS_B MSIB,
--APPS.OE_ORDER_LINES_ALL OOLA,
APPS.GL_CODE_COMBINATIONS GCC,
--APPS.MTL_ITEM_CATEGORIES_V MICAT,
APPS.FND_FLEX_VALUES_VL ABC,
(SELECT NAME, PRECEDENCE, PARENT_PRICE_LIST_ID, LIST_HEADER_ID
FROM APPS.QP_SECONDARY_PRICE_LISTS_V) SECONDARY_PRICE_LIST
WHERE HCAA.CUST_ACCOUNT_ID = HCASA.CUST_ACCOUNT_ID
-- AND HCAA.CUST_ACCOUNT_ID = HCP.CUST_ACCOUNT_ID
AND HCASA.CUST_ACCT_SITE_ID = HCSUA.CUST_ACCT_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND HCASA.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND HP.PARTY_ID = PARTY_SITE.PARTY_ID
AND HCSUA.PRICE_LIST_ID = QLH.LIST_HEADER_ID
AND QLH.LIST_HEADER_ID = HCSUA.PRICE_LIST_ID
AND QLH.LIST_HEADER_ID = QLL.LIST_HEADER_ID(+)
AND SECONDARY_PRICE_LIST.PARENT_PRICE_LIST_ID(+) = HCSUA.PRICE_LIST_ID
AND MSIB.ORGANIZATION_ID = P_ORGANIZATION_ID
AND GCC.CODE_COMBINATION_ID = MSIB.COST_OF_SALES_ACCOUNT
AND ABC.FLEX_VALUE_SET_ID = P_FLEXFIELD_ID
AND GCC.SEGMENT7 = ABC.FLEX_VALUE
AND TO_CHAR(MSIB.INVENTORY_ITEM_ID) = QLL.PRODUCT_ID
AND TO_CHAR(MSIB.INVENTORY_ITEM_ID) = QLL2.PRODUCT_ID
AND QLH2.LIST_HEADER_ID(+) = SECONDARY_PRICE_LIST.LIST_HEADER_ID
AND QLH2.LIST_HEADER_ID = QLL2.LIST_HEADER_ID(+)
AND NVL(QLL2.END_DATE_ACTIVE, SYSDATE + 1) > SYSDATE
AND NVL(QLH2.END_DATE_ACTIVE, SYSDATE + 1) > SYSDATE
--AND QLH2.ATTRIBUTE1 NOT IN ('G', 'L', 'N')
AND HCAA.ACCOUNT_NUMBER = P_CUSTOMER_ACCOUNT_NUMBER
