ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D46FA651A9E05362FB09050512Y SELECT ATA, AID, (CASE WHEN CANCELLATION_YEAR IS NULL OR NGCF = 'Y' THEN BPOA WHEN PERIOD_YEAR < CANCELLATION_YEAR THEN BPOA WHEN PERIOD_YEAR > CANCELLATION_YEAR AND NGCF = 'N' THEN NULL WHEN PERIOD_YEAR = CANCELLATION_YEAR AND PERIOD_NUM <= CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'N' THEN BPOA WHEN PERIOD_YEAR = CANCELLATION_YEAR AND PERIOD_NUM > CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'Y' AND NGCF = 'N' THEN NULL ELSE NULL END) BPOA, (CASE WHEN CANCELLATION_YEAR IS NULL OR NGCF = 'Y' THEN EPOA WHEN PERIOD_YEAR < CANCELLATION_YEAR THEN EPOA WHEN PERIOD_YEAR > CANCELLATION_YEAR AND NGCF = 'N' THEN NULL WHEN PERIOD_YEAR = CANCELLATION_YEAR AND PERIOD_NUM <= CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'N' THEN EPOA WHEN PERIOD_YEAR = CANCELLATION_YEAR AND PERIOD_NUM > CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'Y' AND NGCF = 'N' THEN NULL ELSE NULL END) EPOA, (CASE WHEN CANCELLATION_YEAR IS NULL OR NGCF = 'Y' THEN AV WHEN PERIOD_YEAR < CANCELLATION_YEAR THEN AV WHEN PERIOD_YEAR > CANCELLATION_YEAR AND NGCF = 'N' THEN 'C' WHEN PERIOD_YEAR = CANCELLATION_YEAR AND PERIOD_NUM <= CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'N' THEN AV WHEN PERIOD_YEAR = CANCELLATION_YEAR AND PERIOD_NUM > CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'Y' AND NGCF = 'N' THEN 'C' ELSE 'C' END) AV, PERIOD_YEAR, PERIOD_NUM, CANCELLATION_YEAR, CANCEL_NUM, ADJUSTMENT_PERIOD_FLAG, MAIN, SUB, EVENT, USSGL_ACCT, PERIOD, SUM(DEBIT), SUM(CREDIT), POSTED_DATE, GL_DATE, ATC, DRC, APC, ACB, PRC, FNF, TPA, TPM, NBC, CSC ,BEA ,BSC ,ENC ,CNC ,BII ,PYA ,CCY ,PCI ,DOCUMENT_NUMBER ,DOCUMENT_SOURCE ,DOCUMENT_CATEGORY ,CONCATENATED_SEGMENTS ,RTC ,NGCF FROM (SELECT DEPT_TRANSFER ATA ,DEPARTMENT_ID AID ,ESTABLISHED_FISCAL_YR BPOA ,END_YEAR_AVAIL EPOA ,AVAILABILITY_TYPE_CODE AV ,TP.PERIOD_YEAR ,TP.PERIOD_NUM ,(SELECT period_year FROM gl_period_statuses WHERE CANCELLATION_DATE BETWEEN start_date AND end_date AND set_of_books_id = :P_LEDGER_ID AND application_id = 101 AND adjustment_period_flag = 'N') CANCELLATION_YEAR ,(SELECT NVL((SELECT max(b.period_num) FROM gl_period_statuses_v b WHERE b.set_of_books_id = :P_LEDGER_ID AND b.application_id = 101 AND cancellation_date BETWEEN b.start_date AND b.end_date AND b.period_num<(SELECT max(a.period_num) FROM gl_period_statuses_v a WHERE a.set_of_books_id =:P_LEDGER_ID AND a.application_id = 101 AND cancellation_date BETWEEN a.start_date and a.end_date)), (SELECT c.period_num FROM gl_period_statuses_v c WHERE CANCELLATION_DATE BETWEEN c.start_date AND c.end_date AND c.set_of_books_id = :P_LEDGER_ID AND c.application_id = 101 AND c.adjustment_period_flag= 'N')) from DUAL) CANCEL_NUM, (SELECT DECODE(tp.period_num,( SELECT max(b.period_num) FROM gl_period_statuses_v b WHERE b.set_of_books_id = :P_LEDGER_ID AND b.application_id = 101 AND b.period_year=tp.period_year AND adjustment_period_flag = 'Y'),'Y','N') FROM dual) ADJUSTMENT_PERIOD_FLAG ,FUND_GROUP_CODE MAIN ,TAFS_SUB_ACCT SUB ,SLA_HDR_EVENT_ID EVENT ,USSGL_ACCOUNT USSGL_ACCT ,PERIOD_NAME PERIOD ,DEBIT DEBIT ,CREDIT CREDIT ,GL_POSTED_DATE POSTED_DATE ,GL_DATE GL_DATE ,AUTHORITY_TYPE_CODE ATC ,DIRECT_OR_REIMB_CODE DRC ,APPOR_CAT_CODE APC ,APPOR_CAT_B_CODE ACB ,PROGRAM_REPT_CODE PRC ,FED_NON_FED FNF ,TRADING_PARTNER_AGENCY_ID TPA ,TRADING_PARTNER_MAIN_ACCOUNT TPM ,NEW_BAL_CODE NBC ,CUR_SUBSEQUENT_CODE CSC ,BEA_CAT_CODE BEA ,BORR_SRC_CODE BSC ,EXCH_NON_EXCH ENC ,CUST_NON_CUST CNC ,BUDGET_IMPACT_IND BII ,PYA_CODE PYA ,CREDIT_COHORT_YR CCY ,PROGRAM_COST_IND PCI ,DOCUMENT_NUMBER ,DOCUMENT_SOURCE ,DOCUMENT_CATEGORY ,CONCATENATED_SEGMENTS ,REDUCTION_TYPE_CODE RTC ,TB.NO_GTAS_CAN_TAS_FLAG NGCF FROM FV_GTAS_TRX_V TB, ( SELECT period_year, period_num, adjustment_period_flag FROM gl_period_statuses WHERE set_of_books_id = :P_LEDGER_ID AND application_id = 101 AND period_name = :P_TO_PERIOD) TP WHERE TB.SET_OF_BOOKS_ID = :P_LEDGER_ID) GROUP BY ATA, AID, (CASE WHEN CANCELLATION_YEAR IS NULL OR NGCF = 'Y' THEN BPOA WHEN PERIOD_YEAR < CANCELLATION_YEAR THEN BPOA WHEN PERIOD_YEAR > CANCELLATION_YEAR AND NGCF = 'N' THEN NULL WHEN PERIOD_YEAR = CANCELLATION_YEAR AND PERIOD_NUM <= CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'N' THEN BPOA WHEN PERIOD_YEAR = CANCELLATION_YEAR AND PERIOD_NUM > CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'Y' AND NGCF = 'N' THEN NULL ELSE NULL END) , (CASE WHEN CANCELLATION_YEAR IS NULL OR NGCF = 'Y' THEN EPOA WHEN PERIOD_YEAR < CANCELLATION_YEAR THEN EPOA WHEN PERIOD_YEAR > CANCELLATION_YEAR AND NGCF = 'N' THEN NULL WHEN PERIOD_YEAR = CANCELLATION_YEAR AND PERIOD_NUM <= CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'N' THEN EPOA WHEN PERIOD_YEAR = CANCELLATION_YEAR AND PERIOD_NUM > CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'Y' AND NGCF = 'N' THEN NULL ELSE NULL END), (CASE WHEN CANCELLATION_YEAR IS NULL OR NGCF = 'Y' THEN AV WHEN PERIOD_YEAR < CANCELLATION_YEAR THEN AV WHEN PERIOD_YEAR > CANCELLATION_YEAR AND NGCF = 'N' THEN 'C' WHEN PERIOD_YEAR = CANCELLATION_YEAR AND PERIOD_NUM <= CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'N' THEN AV WHEN PERIOD_YEAR = CANCELLATION_YEAR AND PERIOD_NUM > CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'Y' AND NGCF = 'N' THEN 'C' ELSE 'C' END), PERIOD_YEAR, PERIOD_NUM, CANCELLATION_YEAR, CANCEL_NUM, ADJUSTMENT_PERIOD_FLAG, MAIN, SUB, EVENT, USSGL_ACCT, PERIOD, POSTED_DATE, GL_DATE, ATC, DRC, APC, ACB, PRC, FNF, TPA, TPM, NBC, CSC ,BEA ,BSC ,ENC ,CNC ,BII ,PYA ,CCY ,PCI ,DOCUMENT_NUMBER ,DOCUMENT_SOURCE ,DOCUMENT_CATEGORY ,CONCATENATED_SEGMENTS ,RTC ,NGCF ORDER BY ATA,AID,BPOA, EPOA,AV,PERIOD_YEAR, PERIOD_NUM, CANCELLATION_YEAR, CANCEL_NUM, ADJUSTMENT_PERIOD_FLAG,MAIN,SUB,DOCUMENT_SOURCE,DOCUMENT_CATEGORY,DOCUMENT_NUMBER,USSGL_ACCT FV FVGTASTR US FV GTAS Transaction Register Imported from BI Publisher Application: Federal Financials Source: GTAS Transaction Register Report Short Name: FVGTASTR DB package: FV_GTAS_TRX_REGISTER BI Publisher :p_aid_high :p_aid_low :p_chart_of_accounts_id :p_flex_high :p_flex_low :p_from_gl_posted_date :p_from_period :p_journal_category :p_journal_source :p_ledger_id :p_main_acct_high :p_main_acct_low :p_to_gl_posted_date :p_to_period 1 -10 :p_chart_of_accounts_id LOV Oracle FV_SRS_COA_BY_LEDGER Y select chart_of_accounts_id id, chart_of_accounts_id value, null description from gl_access_sets where access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') order by value,description SELECT CHART_OF_ACCOUNTS_ID FROM GL_LEDGERS WHERE LEDGER_ID = :$flex$.ledger_name Y US Chart of Accounts ID Chart of Accounts ID 2 10 :p_ledger_id LOV Oracle FV_SRS_LEDGER_NAME Y select l.ledger_id id, l.name value, l.description description from gl_access_set_ledgers ac,gl_ledgers_public_v l where ac.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and l.ledger_id=ac.ledger_id and l.ledger_category_code='PRIMARY' order by name SELECT name FROM gl_ledgers WHERE ledger_id = gl_access_set_security_pkg.get_default_ledger_id(fnd_profile.value('GL_ACCESS_SET_ID'),'F') Y US Ledger Name Ledger Name 3 20 :p_flex_low Char US Flexfield Low Flexfield Low 4 30 :p_flex_high Char US Flexfield High Flexfield High 5 40 :p_from_period LOV Oracle FV_FACTS_TRANS_REG_FROM_PERIOD Y select period_name id, period_name value, null description from gl_period_statuses where application_id=101 and ledger_id=:$flex$.fv_srs_ledger_name order by period_year desc,period_num desc Y US Start Period Start Period 6 50 :p_to_period LOV Oracle FV_FACTS_TRANS_REG_TO_PERIOD Y select period_name id, period_name value, null description from gl_period_statuses where application_id=101 and ledger_id=:$flex$.fv_srs_ledger_name and start_date>=(select start_date from gl_period_statuses where application_id=101 and ledger_id=:$flex$.fv_srs_ledger_name and period_name=:$flex$.fv_facts_trans_reg_from_period) and period_year=(select period_year from gl_period_statuses where application_id=101 and ledger_id=:$flex$.fv_srs_ledger_name and period_name=:$flex$.fv_facts_trans_reg_from_period) order by period_year desc,period_num desc SELECT period_name FROM gl_period_statuses WHERE period_name = :$flex$.start_period AND set_of_books_id = fnd_profile.value('GL_SET_OF_BKS_ID') AND application_id = 101 Y US End Period End Period 7 60 :p_from_gl_posted_date Date US Posted Date From GL From Posted Date 8 70 :p_to_gl_posted_date Date US Posted Date To GL To Posted Date 9 80 :p_journal_source LOV Oracle FV_FACTS_TRANS_REG_SOURCE Y select je_source_name id, user_je_source_name value, null description from gl_je_sources order by user_je_source_name US Journal Source Journal Source 10 90 :p_journal_category LOV Oracle FV_FACTS_TRANS_REG_CATEGORY Y select je_category_name id, user_je_category_name value, null description from gl_je_categories order by user_je_category_name US Journal Category Journal Category 11 100 :p_aid_low LOV Oracle FV_GTAS_AID_LOW Y select laid id, aid value, null description from (select distinct(treasury_dept_code) aid,treasury_dept_code laid,set_of_books_id from fv_facts_federal_accounts) where set_of_books_id=:$flex$.fv_srs_ledger_name order by aid US Agency ID Low Agency ID Low 12 110 :p_aid_high LOV Oracle FV_GTAS_AID_HIGH Y select laid id, aid value, null description from (select distinct(treasury_dept_code) aid,treasury_dept_code laid,set_of_books_id from fv_facts_federal_accounts) where set_of_books_id=:$flex$.fv_srs_ledger_name and aid>=:$flex$.fv_gtas_aid_low order by aid US Agency ID High Agency ID High 13 120 :p_main_acct_low LOV Oracle FV_GTAS_MAIN_ACCT_LOW Y select lmain id, main value, null description from (select distinct(treasury_acct_code) main,treasury_acct_code lmain,set_of_books_id from fv_facts_federal_accounts) where set_of_books_id=:$flex$.fv_srs_ledger_name order by main US Main Account Low Main Account Low 14 130 :p_main_acct_high LOV Oracle FV_GTAS_MAIN_ACCT_HIGH Y select lmain id, main value, null description from (select distinct(treasury_acct_code) main,treasury_acct_code lmain,set_of_books_id from fv_facts_federal_accounts) where set_of_books_id=:$flex$.fv_srs_ledger_name and main>=:$flex$.fv_gtas_main_acct_low order by main US Main Account High Main Account High