View: MTL_FETCH_AGREEMENT_LINES

Product:	INV - Inventory
Description:
Implementation/DBA Data:	APPS.MTL_FETCH_AGREEMENT_LINES

View Text

SELECT CONTRACT_HEADER.CONTRACT_NUMBER
, CONTRACT_HEADER.ID CONTRACT_ID
, CONTRACT_HEADER.AUTHORING_ORG_ID
, OKI.CLE_ID SERVICE_AGREEMENT_LINE_ID
, MCP.CLIENT_ID
, MCP.CLIENT_CODE
, MCP.CLIENT_NUMBER
, CLIENT.PARTY_NAME CLIENT_NAME
, MSIB.CONCATENATED_SEGMENTS SERVICE_ITEM
, OKI.OBJECT1_ID2 SERVICE_ITEM_ORG_ID
, MSIB.INVENTORY_ITEM_ID
, MSIB.PRIMARY_UOM_CODE BILLING_UOM
, CONTRACT_LINES.START_DATE SERVICE_LINE_START_DATE
, CONTRACT_LINES.END_DATE SERVICE_LINE_END_DATE
, INV_3PL_BILLING_COUNTER_PVT.GET_TOP_COUNTER_DETAILS(CONTRACT_HEADER.ID
, CONTRACT_LINES.ID) COUNTER_ITEM_ID
, READING.NET_READING COUNTER_NET_READING
, READING.VALUE_TIMESTAMP LAST_COMPUTATION_DATE
, READING.COUNTER_READING
FROM MTL_SYSTEM_ITEMS_B_KFV MSIB
, OKC_K_ITEMS OKI
, OKC_K_HEADERS_ALL_B CONTRACT_HEADER
, OKC_K_LINES_B CONTRACT_LINES
, MTL_CLIENT_PARAMETERS MCP
, CSI_COUNTER_READINGS READING
, HZ_PARTIES CLIENT
, HZ_CUST_ACCOUNTS CUST_ACCOUNT
WHERE MSIB.INVENTORY_ITEM_ID = TO_NUMBER(OKI.OBJECT1_ID1)
AND MSIB.ORGANIZATION_ID = TO_NUMBER(OKI.OBJECT1_ID2)
AND OKI.DNZ_CHR_ID = CONTRACT_HEADER.ID
AND OKI.JTOT_OBJECT1_CODE = 'OKX_USAGE'
AND CONTRACT_LINES.CHR_ID = OKI.DNZ_CHR_ID
AND CONTRACT_LINES.ID = OKI.CLE_ID
AND MCP.CLIENT_ID = CONTRACT_LINES.CUST_ACCT_ID
AND CLIENT.PARTY_ID = CUST_ACCOUNT.PARTY_ID
AND CUST_ACCOUNT.CUST_ACCOUNT_ID = MCP.CLIENT_ID
AND READING.COUNTER_ID = INV_3PL_BILLING_COUNTER_PVT.GET_TOP_COUNTER_DETAILS(CONTRACT_HEADER.ID
, CONTRACT_LINES.ID)
AND READING.COUNTER_VALUE_ID = (SELECT MAX(COUNTER_VALUE_ID)
FROM CSI_COUNTER_READINGS
WHERE COUNTER_ID = INV_3PL_BILLING_COUNTER_PVT.GET_TOP_COUNTER_DETAILS(CONTRACT_HEADER.ID
, CONTRACT_LINES.ID)) UNION SELECT CONTRACT_HEADER.CONTRACT_NUMBER
, CONTRACT_HEADER.ID CONTRACT_ID
, CONTRACT_HEADER.AUTHORING_ORG_ID
, OKI.CLE_ID SERVICE_AGREEMENT_LINE_ID
, MCP.CLIENT_ID
, MCP.CLIENT_CODE
, MCP.CLIENT_NUMBER
, CLIENT.PARTY_NAME CLIENT_NAME
, MSIB.CONCATENATED_SEGMENTS SERVICE_ITEM
, OKI.OBJECT1_ID2 SERVICE_ITEM_ORG_ID
, MSIB.INVENTORY_ITEM_ID
, MSIB.PRIMARY_UOM_CODE BILLING_UOM
, CONTRACT_LINES.START_DATE SERVICE_LINE_START_DATE
, CONTRACT_LINES.END_DATE SERVICE_LINE_END_DATE
, INV_3PL_BILLING_COUNTER_PVT.GET_TOP_COUNTER_DETAILS(CONTRACT_HEADER.ID
, CONTRACT_LINES.ID) COUNTER_ITEM_ID
, NULL COUNTER_NET_READING
, NULL LAST_COMPUTATION_DATE
, NULL COUNTER_READING
FROM MTL_SYSTEM_ITEMS_B_KFV MSIB
, OKC_K_ITEMS OKI
, OKC_K_HEADERS_ALL_B CONTRACT_HEADER
, OKC_K_LINES_B CONTRACT_LINES
, MTL_CLIENT_PARAMETERS MCP
, HZ_PARTIES CLIENT
, HZ_CUST_ACCOUNTS CUST_ACCOUNT
WHERE MSIB.INVENTORY_ITEM_ID = TO_NUMBER(OKI.OBJECT1_ID1)
AND MSIB.ORGANIZATION_ID = TO_NUMBER(OKI.OBJECT1_ID2)
AND OKI.DNZ_CHR_ID = CONTRACT_HEADER.ID
AND OKI.JTOT_OBJECT1_CODE = 'OKX_USAGE'
AND CONTRACT_LINES.CHR_ID = OKI.DNZ_CHR_ID
AND CONTRACT_LINES.ID = OKI.CLE_ID
AND MCP.CLIENT_ID = CONTRACT_LINES.CUST_ACCT_ID
AND CLIENT.PARTY_ID = CUST_ACCOUNT.PARTY_ID
AND CUST_ACCOUNT.CUST_ACCOUNT_ID = MCP.CLIENT_ID
AND NOT EXISTS (SELECT 'Y'
FROM CSI_COUNTER_READINGS READING
WHERE READING.COUNTER_ID = INV_3PL_BILLING_COUNTER_PVT.GET_TOP_COUNTER_DETAILS(CONTRACT_HEADER.ID
, CONTRACT_LINES.ID))

Columns