View: AXFV_ACCOUNT_BALANCES

View Text

SELECT 200 APPLICATION_ID
, GPS.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, PV.VENDOR_ID TRADING_PARTNER_ID
, PVS.VENDOR_SITE_ID TRADING_PARTNER_SITE_ID
, AXB.CODE_COMBINATION_ID ACCOUNT_ID
, GPS.PERIOD_NAME PERIOD_NAME
, APP.APPLICATION_NAME APPLICATION_NAME
, SOB.NAME SET_OF_BOOKS_NAME
, PV.VENDOR_NAME TRADING_PARTNER_NAME
, PVS.VENDOR_SITE_CODE TRADING_PARTNER_SITE_NAME
, '_KF:SQLGL:GL#:GCC' "_KF:ACCOUNT"
, AXB.ACCOUNT_SEGMENT ACCOUNT_SEGMENT
, AXB.BALANCE_SEGMENT BALANCING_SEGMENT
, (AXB.END_BALANCE_DR - AXB.PERIOD_NET_DR - AXB.END_BALANCE_CR + AXB.PERIOD_NET_CR) BEGINNING_BALANCE
, (AXB.END_BALANCE_DR - AXB.PERIOD_NET_DR) DEBIT_BEGINNING_BALANCE
, (AXB.END_BALANCE_CR - AXB.PERIOD_NET_CR) CREDIR_BEGINNING_BALANCE
, (AXB.PERIOD_NET_DR - AXB.PERIOD_NET_CR) TRANSACTION
, AXB.PERIOD_NET_DR DEBIT_TRANSACTION
, AXB.PERIOD_NET_CR CREDIT_TRANSACTION
, (AXB.END_BALANCE_DR - AXB.END_BALANCE_CR) ENDING_BALANCE
, AXB.END_BALANCE_DR DEBIT_ENDING_BALANCE
, AXB.END_BALANCE_CR CREDIT_ENDING_BALANCE
, GPS.EFFECTIVE_PERIOD_NUM PERIOD_NAME_SORT_NUMBER
, AXB.HISTORIC_BALANCE HISTORIC_BALANCES
, AXB.CREATED_BY CREATED_BY
, AXB.CREATION_DATE CREATION_DATE
, AXB.LAST_UPDATED_BY LAST_UPDATED_BY
, AXB.LAST_UPDATE_DATE LAST_UPDATE_DATE
FROM GL_SETS_OF_BOOKS SOB
, FND_APPLICATION_VL APP
, 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
AND APP.APPLICATION_ID = AXB.APPLICATION_ID
AND SOB.SET_OF_BOOKS_ID = AXB.SET_OF_BOOKS_ID UNION /* 222 TPID <>-999 SUBID <>-999 */ SELECT 222 APPLICATION_ID
, GPS.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, RC.CUSTOMER_ID THIRD_PARTY_ID
, RSU.SITE_USE_ID SUB_ID
, AXB.CODE_COMBINATION_ID CODE_COMBINATION_ID
, GPS.PERIOD_NAME PERIOD_NAME
, APP.APPLICATION_NAME APPLICATION_NAME
, SOB.NAME NAME
, RC.CUSTOMER_NAME THIRD_PARTY_NAME
, RA.ADDRESS1 ||' '|| RA.ADDRESS2 ||' '|| RA.ADDRESS3 ||' '|| RA.ADDRESS4 ||' '|| RA.CITY ||' '|| NVL(RA.STATE
, RA.PROVINCE) ||' '|| RA.POSTAL_CODE SUB_NAME
, '_KF:SQLGL:GL#:GCC' "_KF:SQLGL:GL#:GCC"
, AXB.ACCOUNT_SEGMENT ACCOUNT_SEGMENT
, AXB.BALANCE_SEGMENT BALANCE_SEGMENT
, (AXB.END_BALANCE_DR - AXB.PERIOD_NET_DR - AXB.END_BALANCE_CR + AXB.PERIOD_NET_CR)
, (AXB.END_BALANCE_DR - AXB.PERIOD_NET_DR)
, (AXB.END_BALANCE_CR - AXB.PERIOD_NET_CR)
, (AXB.PERIOD_NET_DR - AXB.PERIOD_NET_CR)
, AXB.PERIOD_NET_DR PERIOD_NET_DR
, AXB.PERIOD_NET_CR PERIOD_NET_CR
, (AXB.END_BALANCE_DR - AXB.END_BALANCE_CR)
, AXB.END_BALANCE_DR
, AXB.END_BALANCE_CR
, GPS.EFFECTIVE_PERIOD_NUM EFFECTIVE_PERIOD_NUM
, AXB.HISTORIC_BALANCE
, AXB.CREATED_BY
, AXB.CREATION_DATE
, AXB.LAST_UPDATED_BY
, AXB.LAST_UPDATE_DATE
FROM GL_SETS_OF_BOOKS SOB
, FND_APPLICATION_VL APP
, 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
AND APP.APPLICATION_ID = AXB.APPLICATION_ID
AND SOB.SET_OF_BOOKS_ID = AXB.SET_OF_BOOKS_ID UNION /* 222 TPID <>-999 SUBID =-999 */ SELECT 222 APPLICATION_ID
, GPS.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, RC.CUSTOMER_ID THIRD_PARTY_ID
, -999 SUB_ID
, AXB.CODE_COMBINATION_ID CODE_COMBINATION_ID
, GPS.PERIOD_NAME PERIOD_NAME
, APP.APPLICATION_NAME APPLICATION_NAME
, SOB.NAME NAME
, RC.CUSTOMER_NAME THIRD_PARTY_NAME
, ATS.MEANING SUB_NAME
, '_KF:SQLGL:GL#:GCC' "_KF:SQLGL:GL#:GCC"
, AXB.ACCOUNT_SEGMENT ACCOUNT_SEGMENT
, AXB.BALANCE_SEGMENT BALANCE_SEGMENT
, (AXB.END_BALANCE_DR - AXB.PERIOD_NET_DR - AXB.END_BALANCE_CR + AXB.PERIOD_NET_CR)
, (AXB.END_BALANCE_DR - AXB.PERIOD_NET_DR)
, (AXB.END_BALANCE_CR - AXB.PERIOD_NET_CR)
, (AXB.PERIOD_NET_DR - AXB.PERIOD_NET_CR)
, AXB.PERIOD_NET_DR PERIOD_NET_DR
, AXB.PERIOD_NET_CR PERIOD_NET_CR
, (AXB.END_BALANCE_DR - AXB.END_BALANCE_CR)
, AXB.END_BALANCE_DR
, AXB.END_BALANCE_CR
, GPS.EFFECTIVE_PERIOD_NUM EFFECTIVE_PERIOD_NUM
, AXB.HISTORIC_BALANCE
, AXB.CREATED_BY
, AXB.CREATION_DATE
, AXB.LAST_UPDATED_BY
, AXB.LAST_UPDATE_DATE
FROM GL_SETS_OF_BOOKS SOB
, FND_APPLICATION_VL APP
, 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
AND APP.APPLICATION_ID = AXB.APPLICATION_ID
AND SOB.SET_OF_BOOKS_ID = AXB.SET_OF_BOOKS_ID UNION /* 222 TPID = -999 SUBID =-999 */ SELECT 222 APPLICATION_ID
, GPS.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, -999 THIRD_PARTY_ID
, -999 SUB_ID
, AXB.CODE_COMBINATION_ID CODE_COMBINATION_ID
, GPS.PERIOD_NAME PERIOD_NAME
, APP.APPLICATION_NAME APPLICATION_NAME
, SOB.NAME NAME
, TP.MEANING THIRD_PARTY_NAME
, SUB.MEANING SUB_NAME
, '_KF:SQLGL:GL#:GCC' "_KF:SQLGL:GL#:GCC"
, AXB.ACCOUNT_SEGMENT ACCOUNT_SEGMENT
, AXB.BALANCE_SEGMENT BALANCE_SEGMENT
, (AXB.END_BALANCE_DR - AXB.PERIOD_NET_DR - AXB.END_BALANCE_CR + AXB.PERIOD_NET_CR)
, (AXB.END_BALANCE_DR - AXB.PERIOD_NET_DR)
, (AXB.END_BALANCE_CR - AXB.PERIOD_NET_CR)
, (AXB.PERIOD_NET_DR - AXB.PERIOD_NET_CR)
, AXB.PERIOD_NET_DR PERIOD_NET_DR
, AXB.PERIOD_NET_CR PERIOD_NET_CR
, (AXB.END_BALANCE_DR - AXB.END_BALANCE_CR)
, AXB.END_BALANCE_DR
, AXB.END_BALANCE_CR
, GPS.EFFECTIVE_PERIOD_NUM EFFECTIVE_PERIOD_NUM
, AXB.HISTORIC_BALANCE
, AXB.CREATED_BY
, AXB.CREATION_DATE
, AXB.LAST_UPDATED_BY
, AXB.LAST_UPDATE_DATE
FROM GL_SETS_OF_BOOKS SOB
, FND_APPLICATION_VL APP
, 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
AND APP.APPLICATION_ID = AXB.APPLICATION_ID
AND SOB.SET_OF_BOOKS_ID = AXB.SET_OF_BOOKS_ID UNION /* 401 CST TPID <> 0 SUBID <> 0 */ SELECT 401 APPLICATION_ID
, GPS.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, OD.ORGANIZATION_ID THIRD_PARTY_ID
, CST.COST_GROUP_ID SUB_ID
, AXB.CODE_COMBINATION_ID CODE_COMBINATION_ID
, GPS.PERIOD_NAME PERIOD_NAME
, APP.APPLICATION_NAME APPLICATION_NAME
, SOB.NAME NAME
, OD.ORGANIZATION_NAME THIRD_PARTY_NAME
, CST.COST_GROUP SUB_NAME
, '_KF:SQLGL:GL#:GCC' "_KF:SQLGL:GL#:GCC"
, AXB.ACCOUNT_SEGMENT ACCOUNT_SEGMENT
, AXB.BALANCE_SEGMENT BALANCE_SEGMENT
, (AXB.END_BALANCE_DR - AXB.PERIOD_NET_DR - AXB.END_BALANCE_CR + AXB.PERIOD_NET_CR)
, (AXB.END_BALANCE_DR - AXB.PERIOD_NET_DR)
, (AXB.END_BALANCE_CR - AXB.PERIOD_NET_CR)
, (AXB.PERIOD_NET_DR - AXB.PERIOD_NET_CR)
, AXB.PERIOD_NET_DR PERIOD_NET_DR
, AXB.PERIOD_NET_CR PERIOD_NET_CR
, (AXB.END_BALANCE_DR - AXB.END_BALANCE_CR)
, AXB.END_BALANCE_DR
, AXB.END_BALANCE_CR
, GPS.EFFECTIVE_PERIOD_NUM EFFECTIVE_PERIOD_NUM
, AXB.HISTORIC_BALANCE
, AXB.CREATED_BY
, AXB.CREATION_DATE
, AXB.LAST_UPDATED_BY
, AXB.LAST_UPDATE_DATE
FROM GL_SETS_OF_BOOKS SOB
, FND_APPLICATION_VL APP
, 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
AND APP.APPLICATION_ID = AXB.APPLICATION_ID
AND SOB.SET_OF_BOOKS_ID = AXB.SET_OF_BOOKS_ID UNION /* 401 STD TPID <> 0 SUBID <> 0 */ SELECT 401 APPLICATION_ID
, GPS.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, OD.ORGANIZATION_ID THIRD_PARTY_ID
, AXB.SUB_ID SUB_ID
, AXB.CODE_COMBINATION_ID CODE_COMBINATION_ID
, GPS.PERIOD_NAME PERIOD_NAME
, APP.APPLICATION_NAME APPLICATION_NAME
, SOB.NAME NAME
, OD.ORGANIZATION_NAME THIRD_PARTY_NAME
, ASI.SECONDARY_INVENTORY_NAME SUB_NAME
, '_KF:SQLGL:GL#:GCC' "_KF:SQLGL:GL#:GCC"
, AXB.ACCOUNT_SEGMENT ACCOUNT_SEGMENT
, AXB.BALANCE_SEGMENT BALANCE_SEGMENT
, (AXB.END_BALANCE_DR - AXB.PERIOD_NET_DR - AXB.END_BALANCE_CR + AXB.PERIOD_NET_CR)
, (AXB.END_BALANCE_DR - AXB.PERIOD_NET_DR)
, (AXB.END_BALANCE_CR - AXB.PERIOD_NET_CR)
, (AXB.PERIOD_NET_DR - AXB.PERIOD_NET_CR)
, AXB.PERIOD_NET_DR PERIOD_NET_DR
, AXB.PERIOD_NET_CR PERIOD_NET_CR
, (AXB.END_BALANCE_DR - AXB.END_BALANCE_CR)
, AXB.END_BALANCE_DR
, AXB.END_BALANCE_CR
, GPS.EFFECTIVE_PERIOD_NUM EFFECTIVE_PERIOD_NUM
, AXB.HISTORIC_BALANCE
, AXB.CREATED_BY
, AXB.CREATION_DATE
, AXB.LAST_UPDATED_BY
, AXB.LAST_UPDATE_DATE
FROM GL_SETS_OF_BOOKS SOB
, FND_APPLICATION_VL APP
, 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
AND APP.APPLICATION_ID = AXB.APPLICATION_ID
AND SOB.SET_OF_BOOKS_ID = AXB.SET_OF_BOOKS_ID UNION /* 401 CST/STD TPID <> 0 SUBID = 0 */ SELECT 401 APPLICATION_ID
, GPS.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, AXB.THIRD_PARTY_ID THIRD_PARTY_ID
, 0 SUB_ID
, AXB.CODE_COMBINATION_ID CODE_COMBINATION_ID
, GPS.PERIOD_NAME PERIOD_NAME
, APP.APPLICATION_NAME APPLICATION_NAME
, SOB.NAME NAME
, OD.ORGANIZATION_NAME THIRD_PARTY_NAME
, ATS.MEANING SUB_NAME
, '_KF:SQLGL:GL#:GCC' "_KF:SQLGL:GL#:GCC"
, AXB.ACCOUNT_SEGMENT ACCOUNT_SEGMENT
, AXB.BALANCE_SEGMENT BALANCE_SEGMENT
, (AXB.END_BALANCE_DR - AXB.PERIOD_NET_DR - AXB.END_BALANCE_CR + AXB.PERIOD_NET_CR)
, (AXB.END_BALANCE_DR - AXB.PERIOD_NET_DR)
, (AXB.END_BALANCE_CR - AXB.PERIOD_NET_CR)
, (AXB.PERIOD_NET_DR - AXB.PERIOD_NET_CR)
, AXB.PERIOD_NET_DR PERIOD_NET_DR
, AXB.PERIOD_NET_CR PERIOD_NET_CR
, (AXB.END_BALANCE_DR - AXB.END_BALANCE_CR)
, AXB.END_BALANCE_DR
, AXB.END_BALANCE_CR
, GPS.EFFECTIVE_PERIOD_NUM EFFECTIVE_PERIOD_NUM
, AXB.HISTORIC_BALANCE
, AXB.CREATED_BY
, AXB.CREATION_DATE
, AXB.LAST_UPDATED_BY
, AXB.LAST_UPDATE_DATE
FROM GL_SETS_OF_BOOKS SOB
, FND_APPLICATION_VL APP
, 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
AND APP.APPLICATION_ID = AXB.APPLICATION_ID
AND SOB.SET_OF_BOOKS_ID = AXB.SET_OF_BOOKS_ID UNION /* 401 CST/STD TPID = 0 SUBID = 0 */ SELECT 401 APPLICATION_ID
, GPS.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, 0 THIRD_PARTY_ID
, 0 SUB_ID
, AXB.CODE_COMBINATION_ID CODE_COMBINATION_ID
, GPS.PERIOD_NAME PERIOD_NAME
, APP.APPLICATION_NAME APPLICATION_NAME
, SOB.NAME NAME
, ATP.MEANING THIRD_PARTY_NAME
, ATS.MEANING SUB_NAME
, '_KF:SQLGL:GL#:GCC' "_KF:SQLGL:GL#:GCC"
, AXB.ACCOUNT_SEGMENT ACCOUNT_SEGMENT
, AXB.BALANCE_SEGMENT BALANCE_SEGMENT
, (AXB.END_BALANCE_DR - AXB.PERIOD_NET_DR - AXB.END_BALANCE_CR + AXB.PERIOD_NET_CR)
, (AXB.END_BALANCE_DR - AXB.PERIOD_NET_DR)
, (AXB.END_BALANCE_CR - AXB.PERIOD_NET_CR)
, (AXB.PERIOD_NET_DR - AXB.PERIOD_NET_CR)
, AXB.PERIOD_NET_DR PERIOD_NET_DR
, AXB.PERIOD_NET_CR PERIOD_NET_CR
, (AXB.END_BALANCE_DR - AXB.END_BALANCE_CR)
, AXB.END_BALANCE_DR
, AXB.END_BALANCE_CR
, GPS.EFFECTIVE_PERIOD_NUM EFFECTIVE_PERIOD_NUM
, AXB.HISTORIC_BALANCE
, AXB.CREATED_BY
, AXB.CREATION_DATE
, AXB.LAST_UPDATED_BY
, AXB.LAST_UPDATE_DATE
FROM GL_SETS_OF_BOOKS SOB
, FND_APPLICATION_VL APP
, 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
AND APP.APPLICATION_ID = AXB.APPLICATION_ID
AND SOB.SET_OF_BOOKS_ID = AXB.SET_OF_BOOKS_ID WITH READ ONLY

Columns