VIEW: APPS.FV_ALC_BUSINESS_ACTIVITY_V

Source

View Text - Preformatted

SELECT DISTINCT fou.set_of_books_id, cba.agency_location_code, fv.fv_alc_addresses_id, fv.business_activity_code, alc_gwa_category_id, gwa.gwa_reporter_category_code, gwa.effective_start_date, gwa.effective_end_date, gp.period_name FROM ce_bank_accounts cba, ce_bank_acct_uses_all cbau, fv_operating_units_all fou, fv_alc_addresses_all fv, fv_alc_gwa_categories gwa, gl_ledgers_public_v glp, gl_periods gp WHERE cba.agency_location_code IS NOT NULL AND cba.bank_account_id = cbau.bank_account_id AND fou.organization_id = cbau.org_id AND cba.account_classification = 'INTERNAL' AND cba.agency_location_code = fv.agency_location_code(+) AND fv.fv_alc_addresses_id= gwa.fv_alc_addresses_id(+) AND glp.period_set_name=gp.period_set_name AND glp.ledger_id = fou.set_of_books_id AND gp.adjustment_period_flag = 'N' AND (((gp.start_date BETWEEN gwa.effective_start_date AND NVL(gwa.effective_end_date, gp.start_date)) AND (gp.end_date BETWEEN gwa.effective_start_date AND NVL(gwa.effective_end_date, GP.end_date)) AND gwa.effective_start_date IS NOT NULL)) UNION SELECT distinct fou.set_of_books_id, cba.agency_location_code, fv.fv_alc_addresses_id , fv.business_activity_code, NULL, NULL, NULL, NULL, NULL FROM ce_bank_accounts cba, ce_bank_acct_uses_all cbau, fv_operating_units_all fou, fv_alc_addresses_all fv, fv_alc_gwa_categories gwa WHERE cba.agency_location_code IS NOT NULL AND cba.bank_account_id = cbau.bank_account_id AND fou.organization_id = cbau.org_id AND cba.account_classification = 'INTERNAL' AND cba.agency_location_code = fv.agency_location_code(+) AND fv.fv_alc_addresses_id= gwa.fv_alc_addresses_id(+) AND gwa.effective_start_date IS NULL UNION SELECT -1 set_of_books_id, 'ALL', -1, NULL, NULL, NULL, NULL, NULL, NULL FROM dual

View Text - HTML Formatted

SELECT DISTINCT FOU.SET_OF_BOOKS_ID
, CBA.AGENCY_LOCATION_CODE
, FV.FV_ALC_ADDRESSES_ID
, FV.BUSINESS_ACTIVITY_CODE
, ALC_GWA_CATEGORY_ID
, GWA.GWA_REPORTER_CATEGORY_CODE
, GWA.EFFECTIVE_START_DATE
, GWA.EFFECTIVE_END_DATE
, GP.PERIOD_NAME
FROM CE_BANK_ACCOUNTS CBA
, CE_BANK_ACCT_USES_ALL CBAU
, FV_OPERATING_UNITS_ALL FOU
, FV_ALC_ADDRESSES_ALL FV
, FV_ALC_GWA_CATEGORIES GWA
, GL_LEDGERS_PUBLIC_V GLP
, GL_PERIODS GP
WHERE CBA.AGENCY_LOCATION_CODE IS NOT NULL
AND CBA.BANK_ACCOUNT_ID = CBAU.BANK_ACCOUNT_ID
AND FOU.ORGANIZATION_ID = CBAU.ORG_ID
AND CBA.ACCOUNT_CLASSIFICATION = 'INTERNAL'
AND CBA.AGENCY_LOCATION_CODE = FV.AGENCY_LOCATION_CODE(+)
AND FV.FV_ALC_ADDRESSES_ID= GWA.FV_ALC_ADDRESSES_ID(+)
AND GLP.PERIOD_SET_NAME=GP.PERIOD_SET_NAME
AND GLP.LEDGER_ID = FOU.SET_OF_BOOKS_ID
AND GP.ADJUSTMENT_PERIOD_FLAG = 'N'
AND (((GP.START_DATE BETWEEN GWA.EFFECTIVE_START_DATE
AND NVL(GWA.EFFECTIVE_END_DATE
, GP.START_DATE))
AND (GP.END_DATE BETWEEN GWA.EFFECTIVE_START_DATE
AND NVL(GWA.EFFECTIVE_END_DATE
, GP.END_DATE))
AND GWA.EFFECTIVE_START_DATE IS NOT NULL)) UNION SELECT DISTINCT FOU.SET_OF_BOOKS_ID
, CBA.AGENCY_LOCATION_CODE
, FV.FV_ALC_ADDRESSES_ID
, FV.BUSINESS_ACTIVITY_CODE
, NULL
, NULL
, NULL
, NULL
, NULL
FROM CE_BANK_ACCOUNTS CBA
, CE_BANK_ACCT_USES_ALL CBAU
, FV_OPERATING_UNITS_ALL FOU
, FV_ALC_ADDRESSES_ALL FV
, FV_ALC_GWA_CATEGORIES GWA
WHERE CBA.AGENCY_LOCATION_CODE IS NOT NULL
AND CBA.BANK_ACCOUNT_ID = CBAU.BANK_ACCOUNT_ID
AND FOU.ORGANIZATION_ID = CBAU.ORG_ID
AND CBA.ACCOUNT_CLASSIFICATION = 'INTERNAL'
AND CBA.AGENCY_LOCATION_CODE = FV.AGENCY_LOCATION_CODE(+)
AND FV.FV_ALC_ADDRESSES_ID= GWA.FV_ALC_ADDRESSES_ID(+)
AND GWA.EFFECTIVE_START_DATE IS NULL UNION SELECT -1 SET_OF_BOOKS_ID
, 'ALL'
, -1
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
FROM DUAL