ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 4D7541E4C44D23BFE0630100007FEA98Y select null action_, null status_, null message_, null modified_columns_, gl.name ledger_name, iddt.dossier_name dossier_type, iddt.amount_type, idth.trx_number transaction_number, idth.dossier_transaction_name, idth.description, idth.trx_status status, idth.parent_trx_number parent_transaction_number, idts.budget_name source_budget, idts.period_name source_period, gcck_s.concatenated_segments source_account, idts.budget_amount source_budget_amount, idts.funds_available source_funds_available, idts.new_balance source_new_balance, idtd.budget_name dest_budget, idtd.period_name dest_period, gcck_d.concatenated_segments dest_account, idtd.budget_amount dest_budget_amount, idtd.funds_available dest_funds_available, idtd.new_balance dest_new_balance, to_number(null) upload_row from igi_dos_trx_headers idth, igi_dos_doc_types iddt, gl_ledgers gl, igi_dos_trx_sources idts, igi_dos_trx_dest idtd, gl_code_combinations_kfv gcck_s, gl_code_combinations_kfv gcck_d where 1=1 and idth.dossier_id=iddt.dossier_id and idth.sob_id=gl.ledger_id and idth.trx_id=idts.trx_id and idts.source_trx_id=idtd.source_trx_id and idth.trx_id=idtd.trx_id and idts.code_combination_id=gcck_s.code_combination_id(+) and idtd.code_combination_id=gcck_d.code_combination_id(+) Upload Row column added to keep the uploaded row order in the result; Ledger Name uses the shared GL Ledger list. U A xxen_igi_upload.upload_dossier xxen_igi_upload.validate_row( p_ledger_name=>:$column$.Ledger_Name, p_dossier_name=>:$column$.Dossier_Type, p_source_budget=>:$column$.Source_Budget, p_source_period=>:$column$.Source_Period, p_source_account=>:$column$.Source_Account, p_dest_budget=>:$column$.Dest_Budget, p_dest_period=>:$column$.Dest_Period, p_dest_account=>:$column$.Dest_Account, p_dest_budget_amount=>:$column$.Dest_Budget_Amount ) US IGI Dossier Maintenance Upload Creates dossier transactions (budget transfers) of Oracle Public Sector Financials International (IGI) through the IGI Dossier table handlers, with status In Process. Check funds, reserve funds and approve them in the Dossier Maintenance window. Rows with the same Dossier Transaction Name, dossier type and ledger form one transaction: the first row creates the header and the source budget line, and every row adds a destination budget line. A row whose transaction already exists adds a destination line to it; the header and existing lines are not updated. The source budget amount is the total of the destination amounts and must not exceed the funds available on the source account. Funds available are the budget less encumbrance and actual balances, for the amount type of the dossier type (PTD, QTD, YTD or PJTD). The destination new balance is its funds available plus the transferred amount. Enginatics R12 only Upload 1=1 1 10 1=1 :p_upload_mode like '%' || xxen_upload.action_update LOV Blitz Upload Mode E811F942597A2075E0530100007FF4F2 select xxen_upload.action_create id, xxen_upload.action_meaning(xxen_upload.action_create) value, null description from dual union all select xxen_upload.action_create||','||xxen_upload.action_update id, xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) value, null description from dual C Y US Upload Mode 2 20 1=1 gl.name=:ledger LOV GL Ledger 8E2FF36EDEB879D2E0530100007F1FF2 select gl.name value, fifsv.id_flex_structure_name||': '||decode(gl.ledger_category_code,'NONE',xxen_util.meaning(gl.object_type_code,'LEDGERS',101),xxen_util.meaning(gl.ledger_category_code,'GL_ASF_LEDGER_CATEGORY',101))||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where (:$flex$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y')) and (:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') and gl.ledger_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='LEDGER') and gl.chart_of_accounts_id=fifsv.id_flex_num and fifsv.id_flex_code='GL#' and fifsv.application_id=101 order by fifsv.id_flex_structure_name, decode(gl.ledger_category_code,'PRIMARY',1,'SECONDARY',2,'ALC',3,'NONE',4), gl.name xxen_util.default_ledger Y US Ledger 3 30 1=1 idth.trx_number=:transaction_number LOV custom select idth.trx_number value, iddt.dossier_name||': '||idth.dossier_transaction_name||' ('||idth.trx_status||')' description from igi_dos_trx_headers idth, igi_dos_doc_types iddt where idth.dossier_id=iddt.dossier_id and :$flex$.upload_mode='C,U' order by idth.trx_number desc US Transaction Number 405BB6159D0D4473BD4F1D680444C348 Default All columns. ENGINATICS 3 AMOUNT_TYPE 6 DESCRIPTION 17 DEST_ACCOUNT 15 DEST_BUDGET 18 DEST_BUDGET_AMOUNT 19 DEST_FUNDS_AVAILABLE 20 DEST_NEW_BALANCE 16 DEST_PERIOD 5 DOSSIER_TRANSACTION_NAME Y 2 DOSSIER_TYPE 1 LEDGER_NAME 8 PARENT_TRANSACTION_NUMBER 11 SOURCE_ACCOUNT 9 SOURCE_BUDGET 12 SOURCE_BUDGET_AMOUNT 13 SOURCE_FUNDS_AVAILABLE 14 SOURCE_NEW_BALANCE 10 SOURCE_PERIOD 7 STATUS 4 TRANSACTION_NUMBER S Site 405BB6159D0D4473BD4F1D680444C348 1 ACTION_ Char varchar2 2 STATUS_ Char varchar2 3 MESSAGE_ Char varchar2 4 MODIFIED_COLUMNS_ Char varchar2 Y 5 LEDGER_NAME LOV varchar2 30 GL Ledger 8E2FF36EDEB879D2E0530100007F1FF2 select gl.name value, fifsv.id_flex_structure_name||': '||decode(gl.ledger_category_code,'NONE',xxen_util.meaning(gl.object_type_code,'LEDGERS',101),xxen_util.meaning(gl.ledger_category_code,'GL_ASF_LEDGER_CATEGORY',101))||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where (:$flex$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y')) and (:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') and gl.ledger_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='LEDGER') and gl.chart_of_accounts_id=fifsv.id_flex_num and fifsv.id_flex_code='GL#' and fifsv.application_id=101 order by fifsv.id_flex_structure_name, decode(gl.ledger_category_code,'PRIMARY',1,'SECONDARY',2,'ALC',3,'NONE',4), gl.name Y Y 6 DOSSIER_TYPE LOV custom varchar2 60 Y select iddt.dossier_name value, iddt.dossier_description description from igi_dos_doc_types iddt where (:$column$.ledger_name is null or iddt.sob_id in (select gl.ledger_id from gl_ledgers gl where gl.name=:$column$.ledger_name)) order by iddt.dossier_name Y 7 AMOUNT_TYPE Char varchar2 5 select iddt.amount_type from igi_dos_doc_types iddt where iddt.dossier_name=:$column$.Dossier_Type Y 8 TRANSACTION_NUMBER Char varchar2 40 Y 9 DOSSIER_TRANSACTION_NAME Char varchar2 80 Y 10 DESCRIPTION Char varchar2 80 Y 11 STATUS Char varchar2 30 coalesce((select idth.trx_status from igi_dos_trx_headers idth where idth.trx_number=:$column$.Transaction_Number),'In Process') Y 12 PARENT_TRANSACTION_NUMBER Char varchar2 40 13 SOURCE_BUDGET LOV custom varchar2 25 select gbv.budget_name value, gbv.description from gl_budgets_v gbv where gbv.status in ('C','O') and (:$column$.Ledger_Name is null or gbv.ledger_name=:$column$.Ledger_Name) order by gbv.budget_name Y 14 SOURCE_PERIOD LOV custom varchar2 30 select gps.period_name value, xxen_util.meaning(gps.closing_status,'CLOSING_STATUS',101)||': '||fnd_date.date_to_displaydate(gps.start_date)||' - '||fnd_date.date_to_displaydate(gps.end_date) description from gl_ledgers gl, gl_period_statuses gps where gl.name=:$column$.Ledger_Name and gps.ledger_id=gl.ledger_id and gps.application_id=101 and gps.closing_status in ('O','F') order by gps.start_date desc Y 15 SOURCE_ACCOUNT LOV custom varchar2 311 select gcck.concatenated_segments value, gcck.description from gl_code_combinations_kfv gcck, igi_dos_doc_types iddt where iddt.dossier_name=:$column$.Dossier_Type and gcck.chart_of_accounts_id=iddt.coa_id and gcck.enabled_flag='Y' and gcck.summary_flag='N' order by gcck.concatenated_segments Y 16 SOURCE_BUDGET_AMOUNT Number number Y 17 SOURCE_FUNDS_AVAILABLE Number number xxen_igi_upload.source_funds_available(:$column$.Ledger_Name,:$column$.Dossier_Type,:$column$.Source_Budget,:$column$.Source_Period,:$column$.Source_Account) Y 18 SOURCE_NEW_BALANCE Number number xxen_igi_upload.source_funds_available(:$column$.Ledger_Name,:$column$.Dossier_Type,:$column$.Source_Budget,:$column$.Source_Period,:$column$.Source_Account)-:$column$.Source_Budget_Amount Y 19 DEST_BUDGET LOV custom varchar2 80 select gbv.budget_name value, gbv.description from gl_budgets_v gbv where gbv.status in ('C','O') and (:$column$.Ledger_Name is null or gbv.ledger_name=:$column$.Ledger_Name) order by gbv.budget_name Y 20 DEST_PERIOD LOV custom varchar2 30 select gps.period_name value, xxen_util.meaning(gps.closing_status,'CLOSING_STATUS',101)||': '||fnd_date.date_to_displaydate(gps.start_date)||' - '||fnd_date.date_to_displaydate(gps.end_date) description from gl_ledgers gl, gl_period_statuses gps where gl.name=:$column$.Ledger_Name and gps.ledger_id=gl.ledger_id and gps.application_id=101 and gps.closing_status in ('O','F') order by gps.start_date desc Y 21 DEST_ACCOUNT LOV custom varchar2 311 select gcck.concatenated_segments value, gcck.description from gl_code_combinations_kfv gcck, igi_dos_doc_types iddt where iddt.dossier_name=:$column$.Dossier_Type and gcck.chart_of_accounts_id=iddt.coa_id and gcck.enabled_flag='Y' and gcck.summary_flag='N' order by gcck.concatenated_segments Y 22 DEST_BUDGET_AMOUNT Number number Y 23 DEST_FUNDS_AVAILABLE Number number xxen_igi_upload.dest_funds_available(:$column$.Ledger_Name,:$column$.Dossier_Type,:$column$.Dest_Budget,:$column$.Dest_Period,:$column$.Dest_Account) Y 24 DEST_NEW_BALANCE Number number xxen_igi_upload.dest_funds_available(:$column$.Ledger_Name,:$column$.Dossier_Type,:$column$.Dest_Budget,:$column$.Dest_Period,:$column$.Dest_Account)+:$column$.Dest_Budget_Amount Y 25 UPLOAD_ROW Number number Y U 1 p_ledger_name in Char LEDGER_NAME U 2 p_dossier_name in Char DOSSIER_TYPE U 3 p_transaction_number in Char TRANSACTION_NUMBER U 4 p_transaction_name in Char DOSSIER_TRANSACTION_NAME U 5 p_description in Char DESCRIPTION U 6 p_parent_transaction_number in Char PARENT_TRANSACTION_NUMBER U 7 p_source_budget in Char SOURCE_BUDGET U 8 p_source_period in Char SOURCE_PERIOD U 9 p_source_account in Char SOURCE_ACCOUNT U 10 p_dest_budget in Char DEST_BUDGET U 11 p_dest_period in Char DEST_PERIOD U 12 p_dest_account in Char DEST_ACCOUNT U 13 p_dest_budget_amount in Number DEST_BUDGET_AMOUNT U 14 p_query_transaction_number in Char Y U 15 status_ out Char STATUS_CODE_ U 16 message_ out Char MESSAGE_ Error SQL Order By upload_row, ledger_name, dossier_type, transaction_number, source_account, dest_account Success SQL select xu.action_, xu.status_, xu.message_, xu.modified_columns_, xu.ledger_name, xu.dossier_type, xu.amount_type, xu.transaction_number, xu.dossier_transaction_name, xu.description, xu.status, xu.parent_transaction_number, xu.source_budget, xu.source_period, gcck_s.concatenated_segments source_account, xu.source_budget_amount, xu.source_funds_available, xu.source_new_balance, xu.dest_budget, xu.dest_period, gcck_d.concatenated_segments dest_account, xu.dest_budget_amount, xu.dest_funds_available, xu.dest_new_balance, xu.upload_row from ( select xu.action_, xu.status_, xu.message_, xu.modified_columns_, gl.name ledger_name, iddt.dossier_name dossier_type, iddt.amount_type, idth.trx_number transaction_number, idth.dossier_transaction_name, idth.description, idth.trx_status status, idth.parent_trx_number parent_transaction_number, idts.budget_name source_budget, idts.period_name source_period, idts.budget_amount source_budget_amount, idts.funds_available source_funds_available, idts.new_balance source_new_balance, idtd.budget_name dest_budget, idtd.period_name dest_period, idtd.budget_amount dest_budget_amount, idtd.funds_available dest_funds_available, idtd.new_balance dest_new_balance, xu.source_account, xu.dest_account, idts.code_combination_id source_code_combination_id, idtd.code_combination_id dest_code_combination_id, xu.upload_row from xxen_igi_dossier_mainte_8049_u xu, igi_dos_trx_headers idth, igi_dos_doc_types iddt, gl_ledgers gl, igi_dos_trx_sources idts, igi_dos_trx_dest idtd where xu.status_code_=xxen_upload.status_success and xu.ledger_name=gl.name and xu.dossier_type=iddt.dossier_name and xu.transaction_number=idth.trx_number and idth.dossier_id=iddt.dossier_id and idth.sob_id=gl.ledger_id and idth.trx_id=idts.trx_id and idts.source_trx_id=idtd.source_trx_id and idth.trx_id=idtd.trx_id ) xu, --before 12c an outer joined table may reference at most one other table, so the driving tables are wrapped gl_code_combinations_kfv gcck_s, gl_code_combinations_kfv gcck_d where xu.source_account=gcck_s.concatenated_segments(+) and xu.source_code_combination_id=gcck_s.code_combination_id(+) and xu.dest_account=gcck_d.concatenated_segments(+) and xu.dest_code_combination_id=gcck_d.code_combination_id(+)