eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.MTL_FETCH_AGREEMENT_LINES

Source

View Text - Preformatted


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))

View Text - HTML Formatted

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))