View: XNC_CSI_SUBSCRIPTION_PO_V

View Text

SELECT UNIQUE HP.PARTY_ID PARTY_ID
, HCA.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, HCA.ACCOUNT_NUMBER ACCOUNT_NUMBER
, MRIA.INVENTORY_ITEM_ID
, OL.INVENTORY_ITEM_ID RELATED_ITEM_ID
, MSI.DESCRIPTION RELATED_ITEM_DESC
, LTRIM(RTRIM(MSI.PADDED_CONCATENATED_SEGMENTS)) RELATED_ITEM_NAME
, CSTTS.TRANSACTION_SYSTEM_ID SYSTEM_ID
, CSTTS.SYSTEM_NAME SUBSCRIPTION
, OL.HEADER_ID OL_HEADER_ID
, OL.LINE_ID OL_LINE_ID
, OL.ORG_ID ORG_ID
, MSI.ORGANIZATION_ID
FROM CSI_T_TRANSACTION_LINES A
, CSI_T_TXN_LINE_DETAILS B
, OE_ORDER_LINES_ALL OL
, MTL_SYSTEM_ITEMS_KFV MSI
, MTL_RELATED_ITEMS_ALL_V MRIA
, XNC_ITEM_ACTION_PARS_V XIAPV
, CSI_T_TXN_SYSTEMS CSTTS
, HZ_CUST_ACCOUNTS HCA
, HZ_PARTIES HP
WHERE OL.LINE_ID = A.SOURCE_TRANSACTION_ID
AND B.TRANSACTION_LINE_ID = A.TRANSACTION_LINE_ID
AND OL.OPEN_FLAG = 'Y'
AND OL.LINE_TYPE_ID <> FND_PROFILE.VALUE('XNC_REMOVE_ORDER_LINE_TYPE')
AND MSI.INVENTORY_ITEM_ID = OL.INVENTORY_ITEM_ID
AND MRIA.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MRIA.RELATED_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MRIA.RELATIONSHIP_TYPE_ID = 6
AND B.TRANSACTION_SYSTEM_ID = CSTTS.TRANSACTION_SYSTEM_ID
AND HCA.CUST_ACCOUNT_ID = OL.SOLD_TO_ORG_ID
AND CSTTS.CUSTOMER_ID = OL.SOLD_TO_ORG_ID
AND XIAPV.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND XIAPV.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND XIAPV.SUB_ACTION_TYPE = 'SUBSCRIPTION'
AND XIAPV.SUBSCRIPTION_FLAG = 'Y'
AND CSTTS.CUSTOMER_ID = HCA.CUST_ACCOUNT_ID(+)
AND HP.PARTY_ID = HCA.PARTY_ID

Columns