View: CE_FC_AMTS_DISC_V

Product:	CE - Cash Management
Description:
Implementation/DBA Data:	APPS.CE_FC_AMTS_DISC_V

View Text

SELECT FCELL.FORECAST_ROW_ID
, FROW.ROW_NUMBER
, FROW.TRX_TYPE
, L6.MEANING
, FROW.DESCRIPTION
, FCELL.FORECAST_ID
, FCELL.BANK_ACCOUNT_ID
, NVL(BA.BANK_ACCOUNT_NAME
, L.MEANING)
, NVL(BA.BANK_ACCOUNT_NUM
, '')
, NVL(BA.CURRENCY_CODE
, 'OTHERS')
, DECODE(NVL(BA.BANK_ACCOUNT_NAME
, 'OTHERS')
, 'OTHERS'
, 1
, 0)
, NVL(FCELL.AMOUNT
, 0)
, FCELL.CURRENCY_CODE
, FCELL.TRX_DATE
, FCELL.REFERENCE_ID
, FCELL.ORG_ID
, NVL(XLE.NAME
, L2.MEANING)
, NVL(HZ.PARTY_NAME
, L2.MEANING)
, F.NAME
, F.FORECAST_CURRENCY
, F.FACTOR
, L7.MEANING
, FH.FORECAST_HEADER_ID
, FH.NAME
, FCOL.DEVELOPER_COLUMN_NUM
, DECODE(FCOL.DEVELOPER_COLUMN_NUM
, 0
, L3.MEANING
, DECODE(FCOL.DAYS_FROM
, FCOL.DAYS_TO
, TO_CHAR((F.START_DATE + FCOL.DAYS_FROM-1)
, 'DD-MON-RR')
, TO_CHAR((F.START_DATE + FCOL.DAYS_FROM-1)
, 'DD-MON-RR') || ' - ' || TO_CHAR((F.START_DATE + NVL(FCOL.DAYS_TO
, FCOL.DAYS_FROM)-1)
, 'DD-MON-RR')))
, DECODE(FROW.TRX_TYPE
, 'API'
, L4.MEANING
, 'APP'
, L4.MEANING
, 'APX'
, L4.MEANING
, 'OIO'
, L4.MEANING
, 'PAY'
, L4.MEANING
, 'POP'
, L4.MEANING
, 'POR'
, L4.MEANING
, 'PAT'
, L4.MEANING
, 'PAO'
, L4.MEANING
, 'UDO'
, L4.MEANING
, 'XTO'
, L4.MEANING
, L5.MEANING)
, FCOL.FORECAST_COLUMN_ID
, FCELL.TRX_AMOUNT
FROM CE_FORECAST_TRX_CELLS FCELL
, CE_FORECAST_COLUMNS FCOL
, CE_FORECAST_ROWS FROW
, CE_BANK_ACCOUNTS BA
, XLE_FIRSTPARTY_INFORMATION_V XLE
, HZ_PARTIES HZ
, CE_FORECASTS F
, CE_FORECAST_HEADERS FH
, CE_LOOKUPS L
, CE_LOOKUPS L2
, CE_LOOKUPS L3
, CE_LOOKUPS L4
, CE_LOOKUPS L5
, CE_LOOKUPS L6
, CE_LOOKUPS L7
WHERE FCELL.FORECAST_COLUMN_ID = FCOL.FORECAST_COLUMN_ID
AND FCELL.FORECAST_ROW_ID(+) = FROW.FORECAST_ROW_ID AND FCELL.BANK_ACCOUNT_ID = BA.BANK_ACCOUNT_ID(+) AND FCELL.ORG_ID = XLE.LEGAL_ENTITY_ID(+) AND XLE.PARTY_ID = HZ.PARTY_ID(+)
AND (FCELL.ORG_ID IS NULL OR EXISTS (SELECT NULL
FROM CE_SECURITY_PROFILES_V SEC
WHERE SEC.ORGANIZATION_ID = FCELL.ORG_ID
AND SEC.ORGANIZATION_TYPE = DECODE(FROW.TRX_TYPE
, 'PAY'
, 'BUSINESS_GROUP'
, 'XTI'
, 'LEGAL_ENTITY'
, 'XTO'
, 'LEGAL_ENTITY'
, 'XTR'
, 'LEGAL_ENTITY'
, 'OPERATING_UNIT')))
AND FCELL.FORECAST_ID = F.FORECAST_ID AND F.FORECAST_HEADER_ID = FH.FORECAST_HEADER_ID AND FH.FORECAST_HEADER_ID = FCOL.FORECAST_HEADER_ID AND L.LOOKUP_TYPE = 'FC_MISC' AND L.LOOKUP_CODE = 'OTHERS' AND L2.LOOKUP_TYPE = 'FC_MISC' AND L2.LOOKUP_CODE = 'OTHER' AND L3.LOOKUP_TYPE = 'FC_MISC' AND L3.LOOKUP_CODE = 'OVERDUE' AND L4.LOOKUP_TYPE = 'FC_MISC' AND L4.LOOKUP_CODE = 'OUTFLOW' AND L5.LOOKUP_TYPE = 'FC_MISC' AND L5.LOOKUP_CODE = 'INFLOW' AND L6.LOOKUP_TYPE = 'FORECAST_TRX_TYPE' AND L6.LOOKUP_CODE = FROW.TRX_TYPE AND L7.LOOKUP_TYPE = 'FC_FACTOR' AND L7.LOOKUP_CODE = F.FACTOR AND FH.AGING_TYPE = 'D' AND NVL(FCELL.INCLUDE_FLAG
, 'Y') = 'Y' AND FCELL.AMOUNT <> 0 UNION ALL SELECT FCELL.FORECAST_ROW_ID
, FROW.ROW_NUMBER
, FROW.TRX_TYPE
, L6.MEANING
, FROW.DESCRIPTION
, FCELL.FORECAST_ID
, FCELL.BANK_ACCOUNT_ID
, NVL(BA.BANK_ACCOUNT_NAME
, L.MEANING)
, NVL(BA.BANK_ACCOUNT_NUM
, '')
, NVL(BA.CURRENCY_CODE
, 'OTHERS')
, DECODE(NVL(BA.BANK_ACCOUNT_NAME
, 'OTHERS')
, 'OTHERS'
, 1
, 0)
, NVL(FCELL.AMOUNT
, 0)
, FCELL.CURRENCY_CODE
, FCELL.TRX_DATE
, FCELL.REFERENCE_ID
, FCELL.ORG_ID
, NVL(XLE.NAME
, L2.MEANING)
, NVL(HZ.PARTY_NAME
, L2.MEANING)
, F.NAME
, F.FORECAST_CURRENCY
, F.FACTOR
, L7.MEANING
, FH.FORECAST_HEADER_ID
, FH.NAME
, FCOL.DEVELOPER_COLUMN_NUM
, DECODE(FCOL.DEVELOPER_COLUMN_NUM
, 0
, L3.MEANING
, DECODE(GPS.PERIOD_NAME
, GPE.PERIOD_NAME
, GPS.PERIOD_NAME
, GPS.PERIOD_NAME || ' - ' || GPE.PERIOD_NAME))
, DECODE(FROW.TRX_TYPE
, 'API'
, L4.MEANING
, 'APP'
, L4.MEANING
, 'APX'
, L4.MEANING
, 'OIO'
, L4.MEANING
, 'PAY'
, L4.MEANING
, 'POP'
, L4.MEANING
, 'POR'
, L4.MEANING
, 'PAT'
, L4.MEANING
, 'PAO'
, L4.MEANING
, 'UDO'
, L4.MEANING
, 'XTO'
, L4.MEANING
, L5.MEANING)
, FCOL.FORECAST_COLUMN_ID
, FCELL.TRX_AMOUNT
FROM CE_FORECAST_TRX_CELLS FCELL
, CE_FORECAST_COLUMNS FCOL
, CE_FORECAST_ROWS FROW
, CE_BANK_ACCOUNTS BA
, XLE_FIRSTPARTY_INFORMATION_V XLE
, HZ_PARTIES HZ
, CE_FORECASTS F
, CE_FORECAST_HEADERS FH
, CE_LOOKUPS L
, CE_LOOKUPS L2
, CE_LOOKUPS L3
, CE_LOOKUPS L4
, CE_LOOKUPS L5
, CE_LOOKUPS L6
, CE_LOOKUPS L7
, GL_PERIODS GPS
, GL_PERIODS GPE
, GL_PERIODS GLP
, GL_PERIOD_TYPES GPT WHERE FCELL.FORECAST_COLUMN_ID = FCOL.FORECAST_COLUMN_ID
AND FCELL.FORECAST_ROW_ID(+) = FROW.FORECAST_ROW_ID AND FCELL.BANK_ACCOUNT_ID = BA.BANK_ACCOUNT_ID(+) AND FCELL.ORG_ID = XLE.LEGAL_ENTITY_ID(+)
AND XLE.PARTY_ID = HZ.PARTY_ID(+)
AND (FCELL.ORG_ID IS NULL OR EXISTS (SELECT NULL
FROM CE_SECURITY_PROFILES_V SEC
WHERE SEC.ORGANIZATION_ID = FCELL.ORG_ID
AND SEC.ORGANIZATION_TYPE = DECODE(FROW.TRX_TYPE
, 'PAY'
, 'BUSINESS_GROUP'
, 'XTI'
, 'LEGAL_ENTITY'
, 'XTO'
, 'LEGAL_ENTITY'
, 'XTR'
, 'LEGAL_ENTITY'
, 'OPERATING_UNIT')))
AND FCELL.FORECAST_ID = F.FORECAST_ID AND F.FORECAST_HEADER_ID = FH.FORECAST_HEADER_ID AND FH.FORECAST_HEADER_ID = FCOL.FORECAST_HEADER_ID AND L.LOOKUP_TYPE = 'FC_MISC' AND L.LOOKUP_CODE = 'OTHERS' AND L2.LOOKUP_TYPE = 'FC_MISC' AND L2.LOOKUP_CODE = 'OTHER' AND L3.LOOKUP_TYPE = 'FC_MISC' AND L3.LOOKUP_CODE = 'OVERDUE' AND L4.LOOKUP_TYPE = 'FC_MISC' AND L4.LOOKUP_CODE = 'OUTFLOW' AND L5.LOOKUP_TYPE = 'FC_MISC' AND L5.LOOKUP_CODE = 'INFLOW' AND L6.LOOKUP_TYPE = 'FORECAST_TRX_TYPE' AND L6.LOOKUP_CODE = FROW.TRX_TYPE AND L7.LOOKUP_TYPE = 'FC_FACTOR' AND L7.LOOKUP_CODE = F.FACTOR AND FH.AGING_TYPE = 'A'
AND GPS.PERIOD_NUM = DECODE (GREATEST(GLP.PERIOD_NUM + FCOL.DAYS_FROM -1
, GPT.NUMBER_PER_FISCAL_YEAR)
, GPT.NUMBER_PER_FISCAL_YEAR
, GLP.PERIOD_NUM + FCOL.DAYS_FROM -1
, MOD(GLP.PERIOD_NUM + FCOL.DAYS_FROM -1
, GPT.NUMBER_PER_FISCAL_YEAR))
AND GPE.PERIOD_NUM = DECODE (GREATEST(GLP.PERIOD_NUM + FCOL.DAYS_TO -1
, GPT.NUMBER_PER_FISCAL_YEAR)
, GPT.NUMBER_PER_FISCAL_YEAR
, GLP.PERIOD_NUM + FCOL.DAYS_TO -1
, MOD(GLP.PERIOD_NUM + FCOL.DAYS_TO -1
, GPT.NUMBER_PER_FISCAL_YEAR))
AND GPS.PERIOD_YEAR = DECODE(MOD (GLP.PERIOD_NUM + FCOL.DAYS_FROM-1
, GPT.NUMBER_PER_FISCAL_YEAR)
, 0
, GLP.PERIOD_YEAR + FLOOR((GLP.PERIOD_NUM + FCOL.DAYS_FROM-1)/GPT.NUMBER_PER_FISCAL_YEAR) -1
, GLP.PERIOD_YEAR + FLOOR((GLP.PERIOD_NUM + FCOL.DAYS_FROM-1)/GPT.NUMBER_PER_FISCAL_YEAR))
AND GPE.PERIOD_YEAR = DECODE(MOD (GLP.PERIOD_NUM + FCOL.DAYS_TO-1
, GPT.NUMBER_PER_FISCAL_YEAR)
, 0
, GLP.PERIOD_YEAR + FLOOR((GLP.PERIOD_NUM + FCOL.DAYS_TO-1)/GPT.NUMBER_PER_FISCAL_YEAR) -1
, GLP.PERIOD_YEAR + FLOOR((GLP.PERIOD_NUM + FCOL.DAYS_TO-1)/GPT.NUMBER_PER_FISCAL_YEAR)) AND GPT.PERIOD_TYPE = GLP.PERIOD_TYPE AND GPS.PERIOD_TYPE = GPE.PERIOD_TYPE
AND GPS.PERIOD_SET_NAME = GLP.PERIOD_SET_NAME AND GPE.PERIOD_SET_NAME = GLP.PERIOD_SET_NAME AND GLP.PERIOD_TYPE = GPS.PERIOD_TYPE AND GLP.PERIOD_SET_NAME = F.PERIOD_SET_NAME AND GLP.PERIOD_NAME = F.START_PERIOD AND NVL(FCELL.INCLUDE_FLAG
, 'Y') = 'Y' AND FCELL.AMOUNT <> 0

Columns