ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN E2B9823491A94076BC21FDB9B0DC9974Y select null action_, null status_, null message_, null modified_columns_, gabv.name batch, xxen_util.meaning(gabv.actual_flag,'GL_DSS_BALANCE_TYPE',101) balance_type, gabv.description batch_description, gabv.show_validation_status batch_status, gaf.name formula, gjc.user_je_category_name category, gaf.description formula_description, xxen_util.meaning(gaf.validation_status,'VALIDATION_STATUS',101) formula_status, xxen_util.meaning(gaf.conversion_method_code,'GL_CONVERSION_METHOD',101) entered_currency_allocation, gafl.transaction_currency currency, gdct.user_conversion_type conversion_type, xxen_util.yes(gaf.full_allocation_flag) full_cost_pool_allocation, decode(gafl.line_number,1,'A',2,'B',3,'C',4,'T',5,'O') line, gafl.amount, xxen_gl_upload.alloc_line_ledger(gafl.ledger_id,gafl.ledger_currency,gafl.line_number) ledger, gafl.ledger_action_code ledger_type, xxen_gl_upload.alloc_line_account(gafl.allocation_formula_id,gafl.line_number) account, gafl.segment_types_key segment_types, gafl.ledger_currency, xxen_util.meaning(gafl.currency_type,'GL_CURRENCY_TYPE',101) currency_type, gafl.entered_currency, coalesce(xxen_util.meaning(gafl.amount_type,'PTD_YTD',101),xxen_util.meaning(gafl.amount_type,'PATD_EOD',101)) amount_type, xxen_util.meaning(gafl.relative_period,'PERIOD CODE',101) relative_period, xxen_util.meaning(gafl.actual_flag,'GL_DSS_BALANCE_TYPE',101) line_balance_type, decode(gafl.actual_flag,'B',gbv.budget_name,'E',get.encumbrance_type) budget_or_encumbrance_type, null delete_batch, null delete_formula, null delete_line, gabv.allocation_batch_id, gaf.allocation_formula_id, gafl.line_number, null upload_row from gl_alloc_batches_v gabv, gl_alloc_formulas gaf, gl_je_categories gjc, gl_daily_conversion_types gdct, gl_alloc_formula_lines gafl, gl_budget_versions gbv, gl_encumbrance_types get where 1=1 and gabv.chart_of_accounts_id=(select gas.chart_of_accounts_id from gl_access_sets gas where gas.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID')) and gabv.view_privilege='Y' and gabv.allocation_batch_id=gaf.allocation_batch_id(+) and gaf.je_category_name=gjc.je_category_name(+) and gaf.currency_conversion_type=gdct.conversion_type(+) and gaf.allocation_formula_id=gafl.allocation_formula_id(+) and gafl.budget_version_id=gbv.budget_version_id(+) and gafl.encumbrance_type_id=get.encumbrance_type_id(+) Initial version: create, update and delete MassAllocation and MassBudget batches, formulas and lines as in the Define MassAllocations form, validating the formulas as the form does and submitting Validate MassAllocations. U A xxen_gl_upload.upload_mass_allocation US GL Mass Allocation Upload Creates, updates and deletes GL MassAllocation and MassBudget batches, formulas and formula lines - the Excel equivalent of the Define MassAllocations and Define MassBudgets forms. Each row is one formula line, with the batch and formula columns repeated on every line. The batch and formula values are taken from the first row of each batch and formula. Leave the formula columns blank to create or update a batch only. A formula has the lines A, B, C, Target (T) and Offset (O); a MassBudget formula needs A, B, C and T. Lines A, B and C take either an Amount or an account, the Target and Offset lines an account. Enter the Ledger, Ledger Type, Account and Segment Types as in the account flexfield of the form: the account segments and their types C (Constant), L (Looping) or S (Summing) are separated by the segment delimiter of the chart of accounts. The lines are validated as in the form. Each batch is saved as a whole: when one of its rows fails, none of its rows are saved. A saved change sets the batch to Not Validated and submits the Validate MassAllocations program, as the form does when it is closed. Set Delete Batch to Yes to delete a batch, Delete Formula to delete a formula with its lines, and Delete Line to delete the Offset line of a MassBudget formula. Batches are maintained in the chart of accounts of the GL access set of the responsibility. 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,U Y US Upload Mode 2 20 1=1 gabv.actual_flag=xxen_util.lookup_code(:balance_type,'GL_DSS_BALANCE_TYPE',101) LOV custom select flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='GL_DSS_BALANCE_TYPE' and flvv.view_application_id=101 and flvv.security_group_id=0 and flvv.enabled_flag='Y' and flvv.lookup_code in ('A','B','E') order by flvv.lookup_code US Balance Type Downloads the MassAllocation (Actual, Encumbrance) or MassBudget (Budget) batches only. 3 30 1=1 gabv.name=:batch LOV custom select gabv.name value, gabv.description from gl_alloc_batches_v gabv where gabv.chart_of_accounts_id=(select gas.chart_of_accounts_id from gl_access_sets gas where gas.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID')) and gabv.view_privilege='Y' and (:$flex$.balance_type is null or gabv.actual_flag=xxen_util.lookup_code(:$flex$.balance_type,'GL_DSS_BALANCE_TYPE',101)) order by gabv.name US Batch 4 40 1=1 gaf.name=:formula LOV custom select gaf.name value, gaf.description from gl_alloc_batches_v gabv, gl_alloc_formulas gaf where gabv.chart_of_accounts_id=(select gas.chart_of_accounts_id from gl_access_sets gas where gas.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID')) and gabv.view_privilege='Y' and (:$flex$.batch is null or gabv.name=:$flex$.batch) and gabv.allocation_batch_id=gaf.allocation_batch_id order by gaf.name US Formula DD5DB2A94F9F49C4BF054302AE1E5B26 Default All columns of the batches, formulas and formula lines. ENGINATICS 17 ACCOUNT 14 AMOUNT 22 AMOUNT_TYPE 2 BALANCE_TYPE Y 1 BATCH 3 BATCH_DESCRIPTION 4 BATCH_STATUS 25 BUDGET_OR_ENCUMBRANCE_TYPE 6 CATEGORY 11 CONVERSION_TYPE 10 CURRENCY 20 CURRENCY_TYPE 26 DELETE_BATCH 27 DELETE_FORMULA 28 DELETE_LINE 21 ENTERED_CURRENCY 9 ENTERED_CURRENCY_ALLOCATION 5 FORMULA 7 FORMULA_DESCRIPTION 8 FORMULA_STATUS 12 FULL_COST_POOL_ALLOCATION 15 LEDGER 19 LEDGER_CURRENCY 16 LEDGER_TYPE 13 LINE 24 LINE_BALANCE_TYPE 23 RELATIVE_PERIOD 18 SEGMENT_TYPES Upload Mode C,U S Site C2FE593B8D8C4026B39571F11CB24D98 New Formulas Empty sheet to create new batches and formulas, one row per formula line. ENGINATICS 15 ACCOUNT 12 AMOUNT 20 AMOUNT_TYPE 2 BALANCE_TYPE Y 1 BATCH 3 BATCH_DESCRIPTION 23 BUDGET_OR_ENCUMBRANCE_TYPE 5 CATEGORY 9 CONVERSION_TYPE 8 CURRENCY 18 CURRENCY_TYPE 19 ENTERED_CURRENCY 7 ENTERED_CURRENCY_ALLOCATION 4 FORMULA 6 FORMULA_DESCRIPTION 10 FULL_COST_POOL_ALLOCATION 13 LEDGER 17 LEDGER_CURRENCY 14 LEDGER_TYPE 11 LINE 22 LINE_BALANCE_TYPE 21 RELATIVE_PERIOD 16 SEGMENT_TYPES Upload Mode C Y S Site Balance Type Batch Formula DD5DB2A94F9F49C4BF054302AE1E5B26 1 ACTION_ Char varchar2 2 STATUS_ Char varchar2 3 MESSAGE_ Char varchar2 4 MODIFIED_COLUMNS_ Char varchar2 Y 5 BATCH LOV custom varchar2 Y select gabv.name value, gabv.description from gl_alloc_batches_v gabv where gabv.chart_of_accounts_id=(select gas.chart_of_accounts_id from gl_access_sets gas where gas.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID')) and gabv.view_privilege='Y' order by gabv.name MassAllocation or MassBudget batch name, unique within the chart of accounts of the GL access set of the responsibility. Enter a new name to create a batch. Y 6 BALANCE_TYPE LOV custom varchar2 select flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='GL_DSS_BALANCE_TYPE' and flvv.view_application_id=101 and flvv.security_group_id=0 and flvv.enabled_flag='Y' and flvv.lookup_code in ('A','B','E') order by flvv.lookup_code select xxen_util.meaning(nvl((select gab.actual_flag from gl_alloc_batches gab where gab.name=:$column$.batch and gab.chart_of_accounts_id=(select gas.chart_of_accounts_id from gl_access_sets gas where gas.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID'))),'A'),'GL_DSS_BALANCE_TYPE',101) from dual Actual or Encumbrance for a MassAllocation batch, Budget for a MassBudget batch. Cannot be changed for an existing batch. Y 7 BATCH_DESCRIPTION Char varchar2 select gab.description from gl_alloc_batches gab where gab.name=:$column$.batch and gab.chart_of_accounts_id=(select gas.chart_of_accounts_id from gl_access_sets gas where gas.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID')) 8 BATCH_STATUS Char varchar2 select xxen_util.meaning(gab.validation_status,'VALIDATION_STATUS',101) from gl_alloc_batches gab where gab.name=:$column$.batch and gab.chart_of_accounts_id=(select gas.chart_of_accounts_id from gl_access_sets gas where gas.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID')) Validation status of the batch. Saving a change sets it to Not Validated and submits the Validate MassAllocations program. Y 9 FORMULA Char varchar2 Formula name, unique within the batch. Leave blank to create or update the batch only. 10 CATEGORY LOV custom varchar2 select gjcv.user_je_category_name value, gjcv.description from gl_je_categories_v gjcv order by gjcv.user_je_category_name select gjc.user_je_category_name from gl_alloc_batches gab, gl_alloc_formulas gaf, gl_je_categories gjc where gab.name=:$column$.batch and gab.chart_of_accounts_id=(select gas.chart_of_accounts_id from gl_access_sets gas where gas.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID')) and gab.allocation_batch_id=gaf.allocation_batch_id and gaf.name=:$column$.formula and gaf.je_category_name=gjc.je_category_name Journal category of the generated journals. Required for a formula. 11 FORMULA_DESCRIPTION Char varchar2 select gaf.description from gl_alloc_batches gab, gl_alloc_formulas gaf where gab.name=:$column$.batch and gab.chart_of_accounts_id=(select gas.chart_of_accounts_id from gl_access_sets gas where gas.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID')) and gab.allocation_batch_id=gaf.allocation_batch_id and gaf.name=:$column$.formula 12 FORMULA_STATUS Char varchar2 select xxen_util.meaning(gaf.validation_status,'VALIDATION_STATUS',101) from gl_alloc_batches gab, gl_alloc_formulas gaf where gab.name=:$column$.batch and gab.chart_of_accounts_id=(select gas.chart_of_accounts_id from gl_access_sets gas where gas.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID')) and gab.allocation_batch_id=gaf.allocation_batch_id and gaf.name=:$column$.formula Y 13 ENTERED_CURRENCY_ALLOCATION LOV custom varchar2 select flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='GL_CONVERSION_METHOD' and flvv.view_application_id=101 and flvv.security_group_id=0 and flvv.enabled_flag='Y' and (flvv.lookup_code='CV' or xxen_util.lookup_code(:$column$.balance_type,'GL_DSS_BALANCE_TYPE',101)='A') order by flvv.lookup_code select xxen_util.meaning(nvl((select gaf.conversion_method_code from gl_alloc_batches gab, gl_alloc_formulas gaf where gab.name=:$column$.batch and gab.chart_of_accounts_id=(select gas.chart_of_accounts_id from gl_access_sets gas where gas.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID')) and gab.allocation_batch_id=gaf.allocation_batch_id and gaf.name=:$column$.formula),'CV'),'GL_CONVERSION_METHOD',101) from dual where :$column$.formula is not null Convert: allocate the converted amounts in the formula currency. Calculate: calculate the allocation in the entered currency, Actual batches only. 14 CURRENCY LOV custom varchar2 select fcav.currency_code value, fcav.name description from fnd_currencies_active_v fcav order by fcav.currency_code select nvl( (select gafl.transaction_currency from gl_alloc_batches gab, gl_alloc_formulas gaf, gl_alloc_formula_lines gafl where gab.name=:$column$.batch and gab.chart_of_accounts_id=(select gas.chart_of_accounts_id from gl_access_sets gas where gas.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID')) and gab.allocation_batch_id=gaf.allocation_batch_id and gaf.name=:$column$.formula and gaf.allocation_formula_id=gafl.allocation_formula_id and gafl.line_number=4), (select gl.currency_code from gl_access_sets gas, gl_ledgers gl where gas.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gas.default_ledger_id=gl.ledger_id) ) from dual where :$column$.formula is not null Transaction currency of the formula. 15 CONVERSION_TYPE LOV custom varchar2 select gdctv.user_conversion_type value, gdctv.description from gl_daily_conversion_types_v gdctv where gdctv.conversion_type not in ('User','EMU FIXED') and gdctv.use_privilege='Y' and xxen_util.lookup_code(:$column$.entered_currency_allocation,'GL_CONVERSION_METHOD',101)='CA' order by gdctv.user_conversion_type select gdct.user_conversion_type from gl_alloc_batches gab, gl_alloc_formulas gaf, gl_daily_conversion_types gdct where gab.name=:$column$.batch and gab.chart_of_accounts_id=(select gas.chart_of_accounts_id from gl_access_sets gas where gas.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID')) and gab.allocation_batch_id=gaf.allocation_batch_id and gaf.name=:$column$.formula and gaf.currency_conversion_type=gdct.conversion_type Required for the Calculate method. 16 FULL_COST_POOL_ALLOCATION LOV varchar2 Yes 8E2FF36EDEA679D2E0530100007F1FF2 select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual select xxen_util.yes(gaf.full_allocation_flag) from gl_alloc_batches gab, gl_alloc_formulas gaf where gab.name=:$column$.batch and gab.chart_of_accounts_id=(select gas.chart_of_accounts_id from gl_access_sets gas where gas.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID')) and gab.allocation_batch_id=gaf.allocation_batch_id and gaf.name=:$column$.formula 17 LINE LOV custom varchar2 select x.value, x.description from ( select 1 line_number, 'A' value, fnd_message.get_string('SQLGL','GL_ALLOC_LINE_A') description from dual union all select 2 line_number, 'B' value, fnd_message.get_string('SQLGL','GL_ALLOC_LINE_B') description from dual union all select 3 line_number, 'C' value, fnd_message.get_string('SQLGL','GL_ALLOC_LINE_C') description from dual union all select 4 line_number, 'T' value, fnd_message.get_string('SQLGL','GL_ALLOC_TARGET') description from dual union all select 5 line_number, 'O' value, fnd_message.get_string('SQLGL','GL_ALLOC_OFFSET') description from dual ) x order by x.line_number Formula line A, B, C, Target (T) or Offset (O), with the formula A * B / C. A formula always has all five lines, a MassBudget formula A, B, C and T and optionally O. 18 AMOUNT Number number A constant amount for line A, B or C instead of an account. 19 LEDGER LOV custom varchar2 select nvl(glr.target_ledger_short_name,gl.short_name) value, gl.name description from gl_ledgers gl, gl_ledger_relationships glr where gl.chart_of_accounts_id=(select gas.chart_of_accounts_id from gl_access_sets gas where gas.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID')) and (gl.ledger_category_code<>'NONE' or gl.object_type_code='S') and nvl(gl.complete_flag,'Y')='Y' and gl.ledger_id=glr.target_ledger_id(+) and gl.ledger_id=glr.source_ledger_id(+) and glr.application_id(+)=101 order by 1 Ledger or ledger set short name, the ledger segment of the account. Optional for Actual and Encumbrance balances. 20 LEDGER_TYPE LOV custom varchar2 select ffvv.flex_value value, ffvv.description from fnd_flex_value_sets ffvs, fnd_flex_values_vl ffvv where ffvs.flex_value_set_name=decode(:$column$.line,'T','GL_MA_TO_value_set','O','GL_MA_TO_value_set','GL_MA_ABC_value_set') and ffvs.flex_value_set_id=ffvv.flex_value_set_id and ffvv.enabled_flag='Y' order by ffvv.flex_value select 'C' from dual where :$column$.account is not null Segment type of the ledger segment: C (Constant) for a ledger, L (Looping) or S (Summing) for a ledger set. 21 ACCOUNT Char varchar2 Account segment values, separated by the segment delimiter of the chart of accounts. Parent values are allowed for Looping and Summing segments. The balancing segment can be left blank for Actual and Encumbrance balances. 22 SEGMENT_TYPES Char varchar2 select rpad('C',2*count(*)-1,max(fifst.concatenated_segment_delimiter)||'C') from gl_access_sets gas, fnd_id_flex_structures fifst, fnd_id_flex_segments fifs where gas.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and fifst.application_id=101 and fifst.id_flex_code='GL#' and fifst.id_flex_num=gas.chart_of_accounts_id and fifst.application_id=fifs.application_id and fifst.id_flex_code=fifs.id_flex_code and fifst.id_flex_num=fifs.id_flex_num and fifs.enabled_flag='Y' and :$column$.account is not null One segment type per account segment, separated by the segment delimiter: C (Constant), L (Looping) or S (Summing, lines A, B and C only). 23 LEDGER_CURRENCY LOV custom varchar2 select fcamv.currency_code value, fcamv.name description from fnd_currencies_active_monet_v fcamv order by fcamv.currency_code select gl_ledger_utils_pkg.get_default_ledger_currency(:$column$.ledger) from dual where nvl(:$column$.line,'A') in ('A','B','C') and :$column$.account is not null Lines A, B and C with an account: the currency of the ledger, or one of the ledger currencies of a ledger set. Required when no ledger is entered. 24 CURRENCY_TYPE LOV custom varchar2 select flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='GL_CURRENCY_TYPE' and flvv.view_application_id=101 and flvv.security_group_id=0 and flvv.enabled_flag='Y' order by flvv.lookup_code select xxen_util.meaning('T','GL_CURRENCY_TYPE',101) from dual where nvl(:$column$.line,'A') in ('A','B','C') and :$column$.account is not null 25 ENTERED_CURRENCY LOV custom varchar2 select fcav.currency_code value, fcav.name description from fnd_currencies_active_v fcav where xxen_util.lookup_code(:$column$.currency_type,'GL_CURRENCY_TYPE',101) in ('E','S') order by fcav.currency_code select decode(xxen_util.lookup_code(:$column$.currency_type,'GL_CURRENCY_TYPE',101),'E',:$column$.ledger_currency,'S','STAT') from dual Required for the Entered currency type, STAT for Statistical. 26 AMOUNT_TYPE LOV custom varchar2 select flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type in ('PTD_YTD','PATD_EOD') and flvv.view_application_id=101 and flvv.security_group_id=0 and flvv.enabled_flag='Y' order by decode(flvv.lookup_code,'PTD',1,'QTD',2,'YTD',3,'PJTD',4,'PATD',5,'QATD',6,'YATD',7,'EOD',8,9) select xxen_util.meaning('PTD','PTD_YTD',101) from dual where :$column$.account is not null Average balance amount types require an average balance ledger. Target and Offset lines always use PTD. 27 RELATIVE_PERIOD LOV custom varchar2 select flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='PERIOD CODE' and flvv.view_application_id=101 and flvv.security_group_id=0 and flvv.enabled_flag='Y' order by flvv.lookup_code select xxen_util.meaning('C','PERIOD CODE',101) from dual where :$column$.account is not null Target and Offset lines always use the current period. 28 LINE_BALANCE_TYPE LOV custom varchar2 select flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='GL_DSS_BALANCE_TYPE' and flvv.view_application_id=101 and flvv.security_group_id=0 and flvv.enabled_flag='Y' and flvv.lookup_code in ('A','B','E') order by flvv.lookup_code select :$column$.balance_type from dual where :$column$.account is not null Balance type of the line. Target and Offset lines use the balance type of the batch. 29 BUDGET_OR_ENCUMBRANCE_TYPE LOV custom varchar2 select gbcv.budget_name value, null description from gl_budgets_current_v gbcv where xxen_util.lookup_code(:$column$.line_balance_type,'GL_DSS_BALANCE_TYPE',101)='B' and gbcv.ledger_id=gl_ledger_utils_pkg.get_ledger_id_of_short_name(:$column$.ledger) union all select getcv.encumbrance_type value, null description from gl_encumbrance_types_current_v getcv where xxen_util.lookup_code(:$column$.line_balance_type,'GL_DSS_BALANCE_TYPE',101)='E' order by 1 Required for the Budget and Encumbrance balance types. 30 DELETE_BATCH LOV custom varchar2 select xxen_util.meaning('Y','YES_NO',0) value, null description from dual where :$column$.allocation_batch_id is not null Deletes the batch with all its formulas. Set it on any row of the batch. A batch used in an AutoAllocation set cannot be deleted. 31 DELETE_FORMULA LOV custom varchar2 select xxen_util.meaning('Y','YES_NO',0) value, null description from dual where :$column$.allocation_formula_id is not null Deletes the formula with all its lines. Set it on any row of the formula. 32 DELETE_LINE LOV custom varchar2 select xxen_util.meaning('Y','YES_NO',0) value, null description from dual where :$column$.allocation_formula_id is not null and :$column$.line='O' and xxen_util.lookup_code(:$column$.balance_type,'GL_DSS_BALANCE_TYPE',101)='B' Deletes the optional Offset line of a MassBudget formula. 33 ALLOCATION_BATCH_ID Number number Y 34 ALLOCATION_FORMULA_ID Number number Y 35 LINE_NUMBER Number number Y 36 UPLOAD_ROW Char varchar2 Y Error SQL Order By upload_row, batch, formula, line_number Success SQL select xu.action_, xu.status_, xu.message_, xu.modified_columns_, nvl(gabv.name,xu.batch) batch, nvl(xxen_util.meaning(gabv.actual_flag,'GL_DSS_BALANCE_TYPE',101),xu.balance_type) balance_type, nvl(gabv.description,xu.batch_description) batch_description, nvl(gabv.show_validation_status,xu.batch_status) batch_status, nvl(gaf.name,xu.formula) formula, nvl(gjc.user_je_category_name,xu.category) category, nvl(gaf.description,xu.formula_description) formula_description, nvl(xxen_util.meaning(gaf.validation_status,'VALIDATION_STATUS',101),xu.formula_status) formula_status, nvl(xxen_util.meaning(gaf.conversion_method_code,'GL_CONVERSION_METHOD',101),xu.entered_currency_allocation) entered_currency_allocation, nvl(gafl.transaction_currency,xu.currency) currency, nvl(gdct.user_conversion_type,xu.conversion_type) conversion_type, nvl(xxen_util.yes(gaf.full_allocation_flag),xu.full_cost_pool_allocation) full_cost_pool_allocation, nvl(decode(gafl.line_number,1,'A',2,'B',3,'C',4,'T',5,'O'),xu.line) line, nvl(gafl.amount,xu.amount) amount, nvl(xxen_gl_upload.alloc_line_ledger(gafl.ledger_id,gafl.ledger_currency,gafl.line_number),xu.ledger) ledger, nvl(gafl.ledger_action_code,xu.ledger_type) ledger_type, nvl(xxen_gl_upload.alloc_line_account(gafl.allocation_formula_id,gafl.line_number),xu.account) account, nvl(gafl.segment_types_key,xu.segment_types) segment_types, nvl(gafl.ledger_currency,xu.ledger_currency) ledger_currency, nvl(xxen_util.meaning(gafl.currency_type,'GL_CURRENCY_TYPE',101),xu.currency_type) currency_type, nvl(gafl.entered_currency,xu.entered_currency) entered_currency, coalesce(xxen_util.meaning(gafl.amount_type,'PTD_YTD',101),xxen_util.meaning(gafl.amount_type,'PATD_EOD',101),xu.amount_type) amount_type, nvl(xxen_util.meaning(gafl.relative_period,'PERIOD CODE',101),xu.relative_period) relative_period, nvl(xxen_util.meaning(gafl.actual_flag,'GL_DSS_BALANCE_TYPE',101),xu.line_balance_type) line_balance_type, nvl(decode(gafl.actual_flag,'B',gbv.budget_name,'E',get.encumbrance_type),xu.budget_or_encumbrance_type) budget_or_encumbrance_type, xu.delete_batch, xu.delete_formula, xu.delete_line, xu.allocation_batch_id, xu.allocation_formula_id, xu.line_number, xu.upload_row from xxen_gl_mass_allocation_2670_u xu, gl_alloc_batches_v gabv, gl_alloc_formulas gaf, gl_je_categories gjc, gl_daily_conversion_types gdct, gl_alloc_formula_lines gafl, gl_budget_versions gbv, gl_encumbrance_types get where xu.status_code_=xxen_upload.status_success and xu.allocation_batch_id=gabv.allocation_batch_id(+) and xu.allocation_formula_id=gaf.allocation_formula_id(+) and gaf.je_category_name=gjc.je_category_name(+) and gaf.currency_conversion_type=gdct.conversion_type(+) and xu.allocation_formula_id=gafl.allocation_formula_id(+) and xu.line_number=gafl.line_number(+) and gafl.budget_version_id=gbv.budget_version_id(+) and gafl.encumbrance_type_id=get.encumbrance_type_id(+)