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.

[Home] [Help]

View: AX_BALANCES_SRS_V

Product:	AX - Global Accounting Engine
Description:	Use to improve performance of queries of balances by reports
Implementation/DBA Data:	Not implemented in this database

View Text

SELECT 200 APPLICATION_ID
, GPS.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, AXB.CODE_COMBINATION_ID CODE_COMBINATION_ID
, GPS.PERIOD_NAME PERIOD_NAME
, GPS.PERIOD_YEAR PERIOD_YEAR
, GPS.PERIOD_NUM PERIOD_NUM
, GPS.EFFECTIVE_PERIOD_NUM EFFECTIVE_PERIOD_NUM
, PV.VENDOR_ID THIRD_PARTY_ID
, PV.VENDOR_NAME THIRD_PARTY_NAME
, SEGMENT1 THIRD_PARTY_NUMBER
, PVS.VENDOR_SITE_ID SUB_ID
, PVS.VENDOR_SITE_CODE SUB_NAME
, AXB.BALANCE_SEGMENT BALANCE_SEGMENT
, AXB.ACCOUNT_SEGMENT ACCOUNT_SEGMENT
, AXB.PERIOD_NET_DR PERIOD_NET_DR
, AXB.PERIOD_NET_CR PERIOD_NET_CR
, AXB.END_BALANCE_DR END_BALANCE_DR
, AXB.END_BALANCE_CR END_BALANCE_CR
FROM PO_VENDOR_SITES_ALL PVS
, PO_VENDORS PV
, AX_BALANCES AXB
, GL_PERIOD_STATUSES GPS
WHERE PVS.VENDOR_ID = PV.VENDOR_ID
AND GPS.APPLICATION_ID = 200
AND GPS.ADJUSTMENT_PERIOD_FLAG = 'N'
AND AXB.APPLICATION_ID = GPS.APPLICATION_ID
AND AXB.SET_OF_BOOKS_ID = GPS.SET_OF_BOOKS_ID
AND AXB.PERIOD_NAME = GPS.PERIOD_NAME
AND AXB.THIRD_PARTY_ID = PV.VENDOR_ID
AND AXB.SUB_ID = PVS.VENDOR_SITE_ID UNION /* 222 TPID <>-999 SUBID <>-999 */ SELECT 222 APPLICATION_ID
, GPS.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, AXB.CODE_COMBINATION_ID CODE_COMBINATION_ID
, GPS.PERIOD_NAME PERIOD_NAME
, GPS.PERIOD_YEAR PERIOD_YEAR
, GPS.PERIOD_NUM PERIOD_NUM
, GPS.EFFECTIVE_PERIOD_NUM EFFECTIVE_PERIOD_NUM
, RC.CUSTOMER_ID THIRD_PARTY_ID
, RC.CUSTOMER_NAME THIRD_PARTY_NAME
, RC.CUSTOMER_NUMBER THIRD_PARTY_NUMBER
, RSU.SITE_USE_ID SUB_ID
, RA.ADDRESS1 ||' '|| RA.ADDRESS2 ||' '|| RA.ADDRESS3 ||' '|| RA.ADDRESS4 ||' '|| RA.CITY ||' '|| NVL(RA.STATE
, RA.PROVINCE) ||' '|| RA.POSTAL_CODE SUB_NAME
, AXB.BALANCE_SEGMENT BALANCE_SEGMENT
, AXB.ACCOUNT_SEGMENT ACCOUNT_SEGMENT
, AXB.PERIOD_NET_DR PERIOD_NET_DR
, AXB.PERIOD_NET_CR PERIOD_NET_CR
, AXB.END_BALANCE_DR END_BALANCE_DR
, AXB.END_BALANCE_CR END_BALANCE_CR
FROM RA_ADDRESSES_ALL RA
, RA_SITE_USES_ALL RSU
, RA_CUSTOMERS RC
, AX_LOOKUPS ATP
, AX_LOOKUPS ATS
, AX_BALANCES AXB
, GL_PERIOD_STATUSES GPS
WHERE RC.CUSTOMER_ID = RA.CUSTOMER_ID
AND RSU.SITE_USE_CODE = 'BILL_TO'
AND RC.CUSTOMER_ID <> -999
AND RSU.SITE_USE_ID <> -999
AND AXB.THIRD_PARTY_ID <> -999
AND AXB.SUB_ID <> -999
AND RSU.ADDRESS_ID = RA.ADDRESS_ID
AND RC.CUSTOMER_NAME <> ATP.MEANING
AND RA.ADDRESS1 ||' '|| RA.ADDRESS2 ||' '|| RA.ADDRESS3 ||' '|| RA.ADDRESS4 ||' '|| RA.CITY ||' '|| NVL(RA.STATE
, RA.PROVINCE) ||' '|| RA.POSTAL_CODE <> ATS.MEANING
AND ATP.LOOKUP_CODE = 'CUSTOMER'
AND ATP.LOOKUP_TYPE = 'AX_3RD_PARTY_UNIDENTIFIED'
AND ATS.LOOKUP_CODE = 'CUSTOMER_SITE'
AND ATS.LOOKUP_TYPE = 'AX_3RD_PARTY_UNIDENTIFIED'
AND GPS.APPLICATION_ID = 222
AND AXB.SET_OF_BOOKS_ID = GPS.SET_OF_BOOKS_ID
AND GPS.ADJUSTMENT_PERIOD_FLAG = 'N'
AND AXB.APPLICATION_ID = GPS.APPLICATION_ID
AND AXB.PERIOD_NAME = GPS.PERIOD_NAME
AND AXB.THIRD_PARTY_ID = RC.CUSTOMER_ID
AND AXB.SUB_ID = RSU.SITE_USE_ID UNION /* 222 TPID <>-999 SUBID =-999 */ SELECT 222 APPLICATION_ID
, GPS.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, AXB.CODE_COMBINATION_ID CODE_COMBINATION_ID
, GPS.PERIOD_NAME PERIOD_NAME
, GPS.PERIOD_YEAR PERIOD_YEAR
, GPS.PERIOD_NUM PERIOD_NUM
, GPS.EFFECTIVE_PERIOD_NUM EFFECTIVE_PERIOD_NUM
, RC.CUSTOMER_ID THIRD_PARTY_ID
, RC.CUSTOMER_NAME THIRD_PARTY_NAME
, RC.CUSTOMER_NUMBER THIRD_PARTY_NUMBER
, -999 SUB_ID
, ATS.MEANING SUB_NAME
, AXB.BALANCE_SEGMENT BALANCE_SEGMENT
, AXB.ACCOUNT_SEGMENT ACCOUNT_SEGMENT
, AXB.PERIOD_NET_DR PERIOD_NET_DR
, AXB.PERIOD_NET_CR PERIOD_NET_CR
, AXB.END_BALANCE_DR END_BALANCE_DR
, AXB.END_BALANCE_CR END_BALANCE_CR
FROM RA_CUSTOMERS RC
, AX_LOOKUPS ATP
, AX_LOOKUPS ATS
, AX_BALANCES AXB
, GL_PERIOD_STATUSES GPS
WHERE RC.CUSTOMER_ID <> -999
AND AXB.THIRD_PARTY_ID <> -999
AND AXB.SUB_ID = -999
AND RC.CUSTOMER_NAME <> ATP.MEANING
AND ATP.LOOKUP_CODE = 'CUSTOMER'
AND ATP.LOOKUP_TYPE = 'AX_3RD_PARTY_UNIDENTIFIED'
AND ATS.LOOKUP_CODE = 'CUSTOMER_SITE'
AND ATS.LOOKUP_TYPE = 'AX_3RD_PARTY_UNIDENTIFIED'
AND GPS.APPLICATION_ID = 222
AND AXB.SET_OF_BOOKS_ID = GPS.SET_OF_BOOKS_ID
AND GPS.ADJUSTMENT_PERIOD_FLAG = 'N'
AND AXB.APPLICATION_ID = GPS.APPLICATION_ID
AND AXB.PERIOD_NAME = GPS.PERIOD_NAME
AND AXB.THIRD_PARTY_ID = RC.CUSTOMER_ID UNION /* 222 TPID = -999 SUBID =-999 */ SELECT 222 APPLICATION_ID
, GPS.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, AXB.CODE_COMBINATION_ID CODE_COMBINATION_ID
, GPS.PERIOD_NAME PERIOD_NAME
, GPS.PERIOD_YEAR PERIOD_YEAR
, GPS.PERIOD_NUM PERIOD_NUM
, GPS.EFFECTIVE_PERIOD_NUM EFFECTIVE_PERIOD_NUM
, -999 THIRD_PARTY_ID
, TP.MEANING THIRD_PARTY_NAME
, TP.MEANING THIRD_PARTY_NUMBER
, -999 SUB_ID
, SUB.MEANING SUB_NAME
, AXB.BALANCE_SEGMENT BALANCE_SEGMENT
, AXB.ACCOUNT_SEGMENT ACCOUNT_SEGMENT
, AXB.PERIOD_NET_DR PERIOD_NET_DR
, AXB.PERIOD_NET_CR PERIOD_NET_CR
, AXB.END_BALANCE_DR END_BALANCE_DR
, AXB.END_BALANCE_CR END_BALANCE_CR
FROM AX_BALANCES AXB
, GL_PERIOD_STATUSES GPS
, AX_LOOKUPS TP
, AX_LOOKUPS SUB
WHERE SUB.LOOKUP_TYPE = 'AX_3RD_PARTY_UNIDENTIFIED'
AND SUB.LOOKUP_CODE = 'CUSTOMER_SITE'
AND TP.LOOKUP_CODE = 'CUSTOMER'
AND TP.LOOKUP_TYPE = 'AX_3RD_PARTY_UNIDENTIFIED'
AND GPS.APPLICATION_ID = 222
AND GPS.ADJUSTMENT_PERIOD_FLAG = 'N'
AND AXB.APPLICATION_ID = GPS.APPLICATION_ID
AND AXB.SET_OF_BOOKS_ID = GPS.SET_OF_BOOKS_ID
AND AXB.PERIOD_NAME = GPS.PERIOD_NAME
AND AXB.THIRD_PARTY_ID = -999
AND AXB.SUB_ID = -999 UNION /* 401 CST TPID <> 0 SUBID <> 0 */ SELECT 401 APPLICATION_ID
, GPS.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, AXB.CODE_COMBINATION_ID CODE_COMBINATION_ID
, GPS.PERIOD_NAME PERIOD_NAME
, GPS.PERIOD_YEAR PERIOD_YEAR
, GPS.PERIOD_NUM PERIOD_NUM
, GPS.EFFECTIVE_PERIOD_NUM EFFECTIVE_PERIOD_NUM
, OD.ORGANIZATION_ID THIRD_PARTY_ID
, OD.ORGANIZATION_NAME THIRD_PARTY_NAME
, OD.ORGANIZATION_CODE THIRD_PARTY_NUMBER
, CST.COST_GROUP_ID SUB_ID
, CST.COST_GROUP SUB_NAME
, AXB.BALANCE_SEGMENT BALANCE_SEGMENT
, AXB.ACCOUNT_SEGMENT ACCOUNT_SEGMENT
, AXB.PERIOD_NET_DR PERIOD_NET_DR
, AXB.PERIOD_NET_CR PERIOD_NET_CR
, AXB.END_BALANCE_DR END_BALANCE_DR
, AXB.END_BALANCE_CR END_BALANCE_CR
FROM CST_COST_GROUPS CST
, MTL_PARAMETERS MTL
, ORG_ORGANIZATION_DEFINITIONS OD
, AX_LOOKUPS ATP
, AX_LOOKUPS ATS
, AX_BALANCES AXB
, GL_PERIOD_STATUSES GPS
WHERE OD.ORGANIZATION_ID = NVL(CST.ORGANIZATION_ID
, OD.ORGANIZATION_ID)
AND OD.ORGANIZATION_ID <> 0
AND CST.COST_GROUP_ID <> 0
AND GPS.APPLICATION_ID = 401
AND GPS.ADJUSTMENT_PERIOD_FLAG = 'N'
AND AXB.THIRD_PARTY_ID <> 0
AND AXB.SUB_ID <> 0
AND OD.ORGANIZATION_NAME <> ATP.MEANING
AND CST.COST_GROUP <> ATS.MEANING
AND ATP.LOOKUP_CODE = 'INVENTORY'
AND ATP.LOOKUP_TYPE = 'AX_3RD_PARTY_UNIDENTIFIED'
AND ATS.LOOKUP_CODE = 'INVENTORY_SITE'
AND ATS.LOOKUP_TYPE = 'AX_3RD_PARTY_UNIDENTIFIED'
AND AXB.APPLICATION_ID = GPS.APPLICATION_ID
AND AXB.SET_OF_BOOKS_ID = GPS.SET_OF_BOOKS_ID
AND AXB.PERIOD_NAME = GPS.PERIOD_NAME
AND AXB.THIRD_PARTY_ID = OD.ORGANIZATION_ID
AND MTL.ORGANIZATION_ID = OD.ORGANIZATION_ID
AND MTL.PRIMARY_COST_METHOD = 2
AND AXB.SUB_ID = CST.COST_GROUP_ID UNION /* 401 STD TPID <> 0 SUBID <> 0 */ SELECT 401 APPLICATION_ID
, GPS.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, AXB.CODE_COMBINATION_ID CODE_COMBINATION_ID
, GPS.PERIOD_NAME PERIOD_NAME
, GPS.PERIOD_YEAR PERIOD_YEAR
, GPS.PERIOD_NUM PERIOD_NUM
, GPS.EFFECTIVE_PERIOD_NUM EFFECTIVE_PERIOD_NUM
, AXB.THIRD_PARTY_ID THIRD_PARTY_ID
, OD.ORGANIZATION_NAME THIRD_PARTY_NAME
, OD.ORGANIZATION_CODE THIRD_PARTY_NUMBER
, AXB.SUB_ID SUB_ID
, ASI.SECONDARY_INVENTORY_NAME SUB_NAME
, AXB.BALANCE_SEGMENT BALANCE_SEGMENT
, AXB.ACCOUNT_SEGMENT ACCOUNT_SEGMENT
, AXB.PERIOD_NET_DR PERIOD_NET_DR
, AXB.PERIOD_NET_CR PERIOD_NET_CR
, AXB.END_BALANCE_DR END_BALANCE_DR
, AXB.END_BALANCE_CR END_BALANCE_CR
FROM AX_SECONDARY_INVENTORY ASI
, MTL_PARAMETERS MTL
, AX_LOOKUPS ATP
, AX_LOOKUPS ATS
, ORG_ORGANIZATION_DEFINITIONS OD
, AX_BALANCES AXB
, GL_PERIOD_STATUSES GPS
WHERE OD.ORGANIZATION_ID = ASI.ORGANIZATION_ID
AND GPS.APPLICATION_ID = 401
AND GPS.ADJUSTMENT_PERIOD_FLAG = 'N'
AND OD.ORGANIZATION_ID <> 0
AND ASI.SECONDARY_INVENTORY_ID <> 0
AND AXB.THIRD_PARTY_ID <> 0
AND AXB.SUB_ID <> 0
AND OD.ORGANIZATION_NAME <> ATP.MEANING
AND ASI.SECONDARY_INVENTORY_NAME <> ATS.MEANING
AND ATP.LOOKUP_CODE = 'INVENTORY'
AND ATP.LOOKUP_TYPE = 'AX_3RD_PARTY_UNIDENTIFIED'
AND ATS.LOOKUP_CODE = 'INVENTORY_SITE'
AND ATS.LOOKUP_TYPE = 'AX_3RD_PARTY_UNIDENTIFIED'
AND AXB.APPLICATION_ID = GPS.APPLICATION_ID
AND AXB.SET_OF_BOOKS_ID = GPS.SET_OF_BOOKS_ID
AND AXB.PERIOD_NAME = GPS.PERIOD_NAME
AND AXB.THIRD_PARTY_ID = ASI.ORGANIZATION_ID
AND MTL.ORGANIZATION_ID = OD.ORGANIZATION_ID
AND MTL.PRIMARY_COST_METHOD <> 2
AND AXB.SUB_ID = ASI.SECONDARY_INVENTORY_ID UNION /* 401 CST/STD TPID <> 0 SUBID = 0 */ SELECT 401 APPLICATION_ID
, GPS.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, AXB.CODE_COMBINATION_ID CODE_COMBINATION_ID
, GPS.PERIOD_NAME PERIOD_NAME
, GPS.PERIOD_YEAR PERIOD_YEAR
, GPS.PERIOD_NUM PERIOD_NUM
, GPS.EFFECTIVE_PERIOD_NUM EFFECTIVE_PERIOD_NUM
, AXB.THIRD_PARTY_ID THIRD_PARTY_ID
, OD.ORGANIZATION_NAME THIRD_PARTY_NAME
, OD.ORGANIZATION_CODE THIRD_PARTY_NUMBER
, 0 SUB_ID
, ATS.MEANING SUB_NAME
, AXB.BALANCE_SEGMENT BALANCE_SEGMENT
, AXB.ACCOUNT_SEGMENT ACCOUNT_SEGMENT
, AXB.PERIOD_NET_DR PERIOD_NET_DR
, AXB.PERIOD_NET_CR PERIOD_NET_CR
, AXB.END_BALANCE_DR END_BALANCE_DR
, AXB.END_BALANCE_CR END_BALANCE_CR
FROM ORG_ORGANIZATION_DEFINITIONS OD
, AX_LOOKUPS ATP
, AX_LOOKUPS ATS
, AX_BALANCES AXB
, GL_PERIOD_STATUSES GPS
WHERE OD.ORGANIZATION_ID = AXB.THIRD_PARTY_ID
AND GPS.APPLICATION_ID = 401
AND GPS.ADJUSTMENT_PERIOD_FLAG = 'N'
AND OD.ORGANIZATION_NAME <> ATP.MEANING
AND OD.ORGANIZATION_ID <> 0
AND AXB.THIRD_PARTY_ID <> 0
AND AXB.SUB_ID = 0
AND ATP.LOOKUP_CODE = 'INVENTORY'
AND ATP.LOOKUP_TYPE = 'AX_3RD_PARTY_UNIDENTIFIED'
AND ATS.LOOKUP_CODE = 'INVENTORY_SITE'
AND ATS.LOOKUP_TYPE = 'AX_3RD_PARTY_UNIDENTIFIED'
AND AXB.APPLICATION_ID = GPS.APPLICATION_ID
AND AXB.SET_OF_BOOKS_ID = GPS.SET_OF_BOOKS_ID
AND AXB.PERIOD_NAME = GPS.PERIOD_NAME UNION /* 401 CST/STD TPID = 0 SUBID = 0 */ SELECT 401 APPLICATION_ID
, GPS.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, AXB.CODE_COMBINATION_ID CODE_COMBINATION_ID
, GPS.PERIOD_NAME PERIOD_NAME
, GPS.PERIOD_YEAR PERIOD_YEAR
, GPS.PERIOD_NUM PERIOD_NUM
, GPS.EFFECTIVE_PERIOD_NUM EFFECTIVE_PERIOD_NUM
, 0 THIRD_PARTY_ID
, ATP.MEANING THIRD_PARTY_NAME
, ATP.MEANING THIRD_PARTY_NUMBER
, 0 SUB_ID
, ATS.MEANING SUB_NAME
, AXB.BALANCE_SEGMENT BALANCE_SEGMENT
, AXB.ACCOUNT_SEGMENT ACCOUNT_SEGMENT
, AXB.PERIOD_NET_DR PERIOD_NET_DR
, AXB.PERIOD_NET_CR PERIOD_NET_CR
, AXB.END_BALANCE_DR END_BALANCE_DR
, AXB.END_BALANCE_CR END_BALANCE_CR
FROM AX_LOOKUPS ATP
, AX_LOOKUPS ATS
, AX_BALANCES AXB
, GL_PERIOD_STATUSES GPS
WHERE GPS.APPLICATION_ID = 401
AND GPS.ADJUSTMENT_PERIOD_FLAG = 'N'
AND AXB.THIRD_PARTY_ID = 0
AND AXB.SUB_ID = 0
AND ATP.LOOKUP_CODE = 'INVENTORY'
AND ATP.LOOKUP_TYPE = 'AX_3RD_PARTY_UNIDENTIFIED'
AND ATS.LOOKUP_CODE = 'INVENTORY_SITE'
AND ATS.LOOKUP_TYPE = 'AX_3RD_PARTY_UNIDENTIFIED'
AND AXB.APPLICATION_ID = GPS.APPLICATION_ID
AND AXB.SET_OF_BOOKS_ID = GPS.SET_OF_BOOKS_ID
AND AXB.PERIOD_NAME = GPS.PERIOD_NAME

Columns

Name
APPLICATION_ID
SET_OF_BOOKS_ID
CODE_COMBINATION_ID
PERIOD_NAME
PERIOD_YEAR
PERIOD_NUM
EFFECTIVE_PERIOD_NUM
THIRD_PARTY_ID
THIRD_PARTY_NAME
THIRD_PARTY_NUMBER
SUB_ID
SUB_NAME
BALANCE_SEGMENT
ACCOUNT_SEGMENT
PERIOD_NET_DR
PERIOD_NET_CR
END_BALANCE_DR
END_BALANCE_CR