ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN CDAC629F0CBF7EC3E0530100007FBA2AY select gl.name ledger ,gjh.period_name ,gjs.user_je_source_name ,gjc.user_je_category_name ,gir.gl_sl_link_table ,gir.gl_sl_link_id ,xah.application_id xah_appl_id ,xah.gl_transfer_status_code ,xah.accounting_entry_status_code ,xe.event_type_code ,xe.event_id ,xte.application_id xte_appl_id ,xte.entity_code ,xte.source_id_int_1 ,xal.accounting_class_code ,xdl.accounting_line_code ,xdl.accounting_line_type_code ,xdl.line_definition_code ,xdl.event_class_code ,xdl.event_type_code dist_event_type_code ,xdl.source_distribution_type ,xdl.source_distribution_id_num_1 -- 707 - Cost Management ,wt.transaction_id wip_transaction_id ,we.wip_entity_name wip_entity_name ,wt.project_id wip_project_id --,wta.wip_sub_ledger_id wip_sub_ledger_id --,wt2.transaction_id wta_wip_transaction_id --,we2.wip_entity_name wta_wip_entity_name --,wt2.project_id wta_project_id ,rrsl.rcv_sub_ledger_id rrsl_rcv_sub_ledger_id ,rrsl.rcv_transaction_id rrsl_rcv_transaction_id ,rt.transaction_id rrsl_transaction_id ,rt.wip_entity_id rrsl_wip_entity_id ,rt.wip_line_id rrsl_wip_line_id ,rt.wip_repetitive_schedule_id rrsl_wip_repetitive_sched_id ,rt.wip_operation_seq_num rrsl_wip_operation_seq_num ,rt.wip_resource_seq_num rrsl_wip_resource_seq_num ,rt.project_id rrsl_project_id ,we3.wip_entity_name rrsl_wip_entity_name -- 275 - Projects ,ppa.segment1 pa_project ,peia.expenditure_type pa_expenditure_type -- 222 AR ,rcta.customer_trx_id ar_customer_trx_id ,rcta.interface_header_context ar_interface_header_context ,rcta.interface_header_attribute1 ar_interface_header_att1 ,rbsa.name ar_batch_source -- 555 OPM ,gxeh.header_id gxeh_header_id ,gxeh.reference_no gxeh_reference_no ,gxeh.event_id gxeh_event_id ,gxeh.entity_code gxeh_entity_code ,gxeh.event_class_code gxeh_event_class_code ,gxeh.event_type_code gxeh_event_type_code ,gxeh.transaction_id gxeh_transaction_id ,gxel.line_id gxel_line_id ,gxel.event_id gxel_event_id ,grat.accounting_txn_id grat_accounting_txn_id ,grat.rcv_transaction_id grat_rcv_transaction_id ,rt2.transaction_id grat_transaction_id ,rt2.wip_entity_id grat_wip_entity_id ,rt2.wip_line_id grat_wip_line_id ,rt2.wip_repetitive_schedule_id grat_wip_repetitive_sched_id ,rt2.wip_operation_seq_num grat_wip_operation_seq_num ,rt2.wip_resource_seq_num grat_wip_resource_seq_num ,rt2.project_id grat_project_id ,we4.wip_entity_name grat_wip_entity_name ,mmt.project_id grat_mmt_project_id -- 140 Assets ,case when xte.application_id = 140 and xte.entity_code = 'TRANSACTIONS' then (select fab.asset_number from fa_additions_b fab,fa_transaction_headers fth where fth.asset_id=fab.asset_id and fth.transaction_header_id=xte.source_id_int_1) when xte.application_id = 140 and xte.entity_code = 'DEPRECIATION' then (select fab.asset_number from fa_additions_b fab where fab.asset_id=xte.source_id_int_1) end asset_number ,case when xte.application_id = 140 and xte.entity_code = 'TRANSACTIONS' then (select fab.asset_number from fa_additions_b fab,fa_transaction_headers fth where fth.asset_id=fab.asset_id and fth.transaction_header_id=xte.source_id_int_1) when xte.application_id = 140 and xte.entity_code = 'DEPRECIATION' then (select fab.asset_number from fa_additions_b fab, fa_deprn_detail fdd where fab.asset_id=fdd.asset_id and fdd.asset_id=xte.source_id_int_1 and fdd.period_counter=xte.source_id_int_2 and fdd.deprn_run_id=xte.source_id_int_3 and rownum=1) end asset_number2 from gl_ledgers gl , gl_je_sources gjs , gl_je_categories gjc , gl_je_headers gjh , gl_je_lines gjl , gl_import_references gir , gl_code_combinations gcc , xla.xla_ae_lines xal , xla.xla_ae_headers xah , xla.xla_events xe , xla.xla_transaction_entities xte , xla.xla_distribution_links xdl -- 707 - Cost Management , wip_transactions wt , wip_entities we --, wip_transaction_accounts wta --, wip_transactions wt2 --, wip_entities we2 , rcv_receiving_sub_ledger rrsl , rcv_transactions rt , wip_entities we3 -- 275 - Projects , pa_expenditure_items_all peia , pa_projects_all ppa -- 222 AR , ar_adjustments_all aaa , ra_customer_trx_all rcta , ra_batch_sources_all rbsa -- 555 OPM , gmf_xla_extract_lines gxel , gmf_xla_extract_headers gxeh , gmf_rcv_accounting_txns grat , rcv_transactions rt2 , wip_entities we4 , mtl_material_transactions mmt where gjs.je_source_name = gjh.je_source and gjc.je_category_name = gjh.je_category and gjl.je_header_id = gjh.je_header_id and gcc.code_combination_id = gjl.code_combination_id -- and gir.je_header_id (+) = gjl.je_header_id and gir.je_line_num (+) = gjl.je_line_num -- and xal.gl_sl_link_id (+) = gir.gl_sl_link_id and xal.gl_sl_link_table (+) = gir.gl_sl_link_table and xah.ae_header_id (+) = xal.ae_header_id and xah.application_id (+) = xal.application_id and xe.application_id (+) = xah.application_id and xe.event_id (+) = xah.event_id and xte.application_id (+) = xah.application_id and xte.entity_id (+) = xah.entity_id and xdl.ae_header_id (+) = xal.ae_header_id and xdl.ae_line_num (+) = xal.ae_line_num -- 707 - Cost Management and wt.transaction_id (+) = case when xte.application_id = 707 then xte.source_id_int_1 end and we.wip_entity_id (+) = wt.wip_entity_id --and wta.wip_sub_ledger_id (+) = case when xdl.application_id = 707 and xdl.source_distribution_type = 'WIP_TRANSACTION_ACCOUNTS' then xdl.source_distribution_id_num_1 end --and wt2.transaction_id (+) = wta.transaction_id --and we2.wip_entity_id = wt2.wip_entity_id and rrsl.rcv_sub_ledger_id (+) = case when xdl.application_id = 707 and xdl.source_distribution_type = 'RCV_RECEIVING_SUB_LEDGER' then xdl.source_distribution_id_num_1 end and rt.transaction_id (+) = rrsl.rcv_transaction_id and we3.wip_entity_id (+) = rt.wip_entity_id -- 275 - Projects and peia.expenditure_item_id (+) = case when xte.application_id = 275 and xte.entity_code = 'EXPENDITURES' then xte.source_id_int_1 end and ppa.project_id (+) = case when xte.application_id = 275 then case xte.entity_code when 'REVENUE' then xte.source_id_int_1 when 'EXPENDITURES' then peia.project_id end end -- 222 AR and aaa.adjustment_id (+) = case when xte.application_id = 222 and xte.entity_code = 'ADJUSTMENTS' then xte.source_id_int_1 end and rcta.customer_trx_id (+) = case when xte.application_id = 222 then case xte.entity_code when 'TRANSACTIONS' then xte.source_id_int_1 when 'BILLS_RECEIVABLE' then xte.source_id_int_1 when 'ADJUSTMENTS' then aaa.customer_trx_id end end and rbsa.batch_source_id (+) = rcta.batch_source_id and rbsa.org_id (+) = rcta.org_id -- 555 OPM and gxel.line_id (+) = case when xdl.application_id = 555 then xdl.source_distribution_id_num_1 end and gxeh.header_id (+) = gxel.header_id and grat.accounting_txn_id (+) = gxeh.transaction_id and rt2.transaction_id (+) = grat.rcv_transaction_id and we4.wip_entity_id (+) = rt2.wip_entity_id and mmt.transaction_id (+) = gxeh.transaction_id -- and gjh.status = 'P' and gjh.actual_flag = 'A' and 1=1 US GL SLA Drillback Diagnostic GL SLA Drillback Diagnostic 1=1 1 10 1=1 gl.name=:ledger LOV GL Ledger 8E2FF36EDEB879D2E0530100007F1FF2 Y 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 nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+)) 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 select x.* from ( select gl.name from gl_ledgers gl where gl.ledger_id in (select gasna.ledger_id from gl_access_set_norm_assign gasna where gasna.access_set_id=nvl(fnd_profile.value('GL_ACCESS_SET_ID'),-1)) order by decode(gl.ledger_category_code,'PRIMARY',1,'SECONDARY',2,'ALC',3,'NONE',4), gl.name ) x where rownum=1 Y US Ledger 2 20 1=1 gjh.period_name=:period_name LOV GL Period (past) 8E2FF36EDED479D2E0530100007F1FF2 Y select distinct gp.period_name value, ( select 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_period_statuses gps where gp.period_name=gps.period_name and gps.ledger_id=(select gl.ledger_id from gl_ledgers gl where gl.name=:$flex$.ledger) and gps.application_id=101 ) description, min(gp.start_date) over (partition by gp.period_name) min_start_date from gl_periods gp where gp.start_date<=sysdate+400 and (gp.period_set_name,gp.period_type) in ( select gl.period_set_name, gl.accounted_period_type from gl_ledgers gl where gl.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+)) and (:$flex$.ledger is null or xxen_util.contains(:$flex$.ledger,gl.name)='Y') and (:$flex$.operating_unit is null or gl.ledger_id in (select hou.set_of_books_id from hr_operating_units hou where xxen_util.contains(:$flex$.operating_unit,hou.name)='Y')) and (:$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 gl.chart_of_accounts_id in (select fifsv.id_flex_num from fnd_id_flex_structures_vl fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y')) ) order by min_start_date desc, gp.period_name select distinct max(gp.period_name) keep (dense_rank last order by gp.start_date,gp.period_year,gp.period_num) over () period_name from gl.gl_periods gp where gp.start_date<=sysdate and (gp.period_set_name,gp.period_type) in ( select gl.period_set_name, gl.accounted_period_type from gl_ledgers gl where :$flex$.period_from is null and :$flex$.batch is null and :$flex$.journal is null and (:$flex$.ledger is null or xxen_util.contains(:$flex$.ledger,gl.name)='Y') and (:$flex$.chart_of_accounts is null or gl.chart_of_accounts_id in (select fifsv.id_flex_num from fnd_id_flex_structures_vl fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y')) ) Y US Period 3 30 1=1 gjh.je_source in (select gjsv.je_source_name from gl_je_sources_vl gjsv where gjsv.user_je_source_name=:user_je_source_name) LOV GL Journal Source 8E2FF36EDF2379D2E0530100007F1FF2 Y select gjsv.user_je_source_name value, gjsv.description from gl_je_sources_vl gjsv order by gjsv.user_je_source_name Y US Journal Source 4 40 1=1 gjh.je_category in (select gjcv.je_category_name from gl_je_categories_vl gjcv where gjcv.user_je_category_name=:journal_category) LOV GL Journal Category 8E2FF36EDF2479D2E0530100007F1FF2 Y select gjcv.user_je_category_name value, gjcv.description from gl_je_categories_vl gjcv order by gjcv.user_je_category_name US Journal Category