VIEW: APPS.CE_FC_AMTS_DISC_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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