ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 4A908EAABEEB6078E0630100007FAF58 select gsrv.reference_no, xep.name legal_entity, gl.name ledger, gsrv.base_currency, gsrv.valuation_cost_type cost_type, gss.fiscal_year, gss.period, (select glp.period_name from gl_periods glp where glp.period_set_name=gl.period_set_name and glp.period_type=gl.accounted_period_type and glp.period_year=gss.fiscal_year and glp.period_num=gss.period) period_name, mp.organization_code, haou.name organization, (select xetv.name from xla_entity_types_vl xetv where xetv.application_id=555 and xetv.entity_code=gsrv.entity_code) entity_type, (select xecv.name from xla_event_classes_vl xecv where xecv.application_id=555 and xecv.entity_code=gsrv.entity_code and xecv.event_class_code=gsrv.event_class) event_class, (select xetyv.name from xla_event_types_vl xetyv where xetyv.application_id=555 and xetyv.entity_code=gsrv.entity_code and xetyv.event_class_code=gsrv.event_class and xetyv.event_type_code=gsrv.event_type) event_type, gsrv.transaction_id, gsrv.transaction_date, msibk.concatenated_segments item, msibk.description item_description, gsrv.item_revision, gsrv.transaction_quantity, gsrv.transaction_uom, gxel.journal_line_type, gxel.component_cost, decode(sign(gxel.base_amount),1,gxel.base_amount,0,0) base_dr_amount, decode(sign(gxel.base_amount),-1,abs(gxel.base_amount),0,0) base_cr_amount, decode(sign(gxel.trans_amount),1,gxel.trans_amount,0,0) trans_dr_amount, decode(sign(gxel.trans_amount),-1,abs(gxel.trans_amount),0,0) trans_cr_amount, gxel.entered_amount, gxel.accounted_amount, gcck.concatenated_segments account, gl_flexfields_pkg.get_concat_description(gcck.chart_of_accounts_id,gcck.code_combination_id,'N') account_description, gsrv.in_subinventory_code subinventory, gsrv.in_transfer_subinventory transfer_subinventory, gsrv.pm_batch_number batch_number, gsrv.pm_batch_status batch_status, gsrv.pm_actual_start_date batch_actual_start_date, gsrv.pm_actual_cmplt_date batch_actual_completion_date, gsrv.pm_formula_no formula, nullif(gsrv.pm_formula_vers,-1) formula_version, gsrv.pm_routing_no routing, nullif(gsrv.pm_routing_vers,-1) routing_version, gsrv.pm_recipe_no recipe, nullif(gsrv.pm_recipe_version,-1) recipe_version, gsrv.pm_resources resources, gsrv.po_purchase_number purchase_order, gsrv.po_supplier_name supplier, gsrv.po_receipt_number receipt_number, nullif(gsrv.po_receipt_line_number,-1) receipt_line_number, nullif(gsrv.om_order_number,-1) order_number, gsrv.om_customer_name customer, nullif(gsrv.om_line_number,-1) om_line_number, gsrv.om_shipment_number shipment_number, gsrv.cm_adjustment_type, gsrv.cm_adjust_cost, gsrv.cm_reason_code, gsrv.cm_period_code, gsrv.cm_calendar_code, gsrv.cm_lot_number from gmf_subledger_rep_v gsrv, gl_subr_sta gss, gmf_xla_extract_lines gxel, xla_distribution_links xdl, xla_ae_lines xal, gl_code_combinations_kfv gcck, xle_entity_profiles xep, gl_ledgers gl, mtl_parameters mp, hr_all_organization_units haou, mtl_system_items_b_kfv msibk where gsrv.reference_no=gss.reference_no and gsrv.header_id=gxel.header_id and gsrv.event_id=xdl.event_id(+) and xdl.application_id(+)=555 and gsrv.entity_code=xdl.source_distribution_type(+) and gxel.line_id=xdl.source_distribution_id_num_1(+) and xdl.ae_header_id=xal.ae_header_id(+) and xdl.ae_line_num=xal.ae_line_num(+) and xal.ledger_id(+)=gsrv.ledger_id and xal.code_combination_id=gcck.code_combination_id(+) and gsrv.legal_entity_id=xep.legal_entity_id(+) and gsrv.ledger_id=gl.ledger_id(+) and gsrv.organization_id=mp.organization_id(+) and gsrv.organization_id=haou.organization_id(+) and gsrv.inventory_item_id=msibk.inventory_item_id(+) and gsrv.organization_id=msibk.organization_id(+) and (:include_zero_amount_lines='Y' or abs(gxel.base_amount)>=0.005 or abs(gxel.trans_amount)>=0.005) and 1=1 order by gsrv.entity_code, mp.organization_code, gsrv.transaction_id, gxel.line_number, gxel.cost_cmpntcls_id, gxel.cost_analysis_code Flattened SQL to include extract lines, account details (via XLA distribution links). Replaced gmf_subled_rep_pkg function calls with inline joins/subqueries. Based on 12.2 data template GMFDSUR.xml 120.15.12020000.5. Y GMF US OPM Detailed Subledger OPM Financials Detailed Subledger Report showing transaction headers, extract lines with debit/credit amounts, and GL account distributions. Based on Oracle standard report GMFDSUR (Detailed Subledger Report). Enginatics 1=1 :include_zero_amount_lines 1 10 1=1 gsrv.reference_no=:reference_no LOV custom Y select gxeh.reference_no id, gxeh.reference_no value, null description from gmf_xla_extract_headers gxeh where gxeh.header_id=(select min(b.header_id) from gmf_xla_extract_headers b where b.reference_no=gxeh.reference_no) order by gxeh.reference_no desc US Reference No Subledger accounting reference number 2 20 1=1 gsrv.legal_entity_id=:legal_entity_id LOV custom Y select gfp.legal_entity_id id, gle.legal_entity_name value, null description from gmf_legal_entities gle, gmf_fiscal_policies gfp where gle.legal_entity_id=gfp.legal_entity_id order by gle.legal_entity_name select gss.legal_entity_name value, null meaning from gl_subr_sta gss where gss.reference_no=:$flex$.Reference_No Y US Legal Entity Legal entity 3 30 1=1 gsrv.ledger_id=:ledger_id LOV custom Y select gl.ledger_id id, gl.name value, null description from gmf_ledger_valuation_methods glvm, gl_ledgers gl where glvm.legal_entity_id=:$flex$.Legal_Entity and glvm.ledger_id=gl.ledger_id group by gl.ledger_id, gl.name order by gl.name select gl.name value, null meaning from gl_subr_sta gss, gl_ledgers gl where gss.ledger_id=gl.ledger_id and gss.reference_no=:$flex$.Reference_No Y US Ledger Ledger 4 40 1=1 gsrv.valuation_cost_type_id=:cost_type_id LOV custom Y select cmm.cost_type_id id, cmm.cost_mthd_code value, null description from gmf_ledger_valuation_methods glvm, cm_mthd_mst cmm where glvm.legal_entity_id=:$flex$.Legal_Entity and glvm.ledger_id=:$flex$.Ledger and glvm.cost_type_id=cmm.cost_type_id order by cmm.cost_mthd_code select gss.cost_mthd_code value, null meaning from gl_subr_sta gss where gss.reference_no=:$flex$.Reference_No Y US Cost Type Valuation cost type 5 50 1=1 gss.fiscal_year=:fiscal_year LOV custom Y select distinct glp.period_year id, glp.period_year value, null description from gl_periods glp, gl_ledgers gl where gl.ledger_id=:$flex$.Ledger and gl.period_set_name=glp.period_set_name and gl.accounted_period_type=glp.period_type order by glp.period_year desc select gss.fiscal_year value, null meaning from gl_subr_sta gss where gss.reference_no=:$flex$.Reference_No Y US Fiscal Year Fiscal year 6 60 1=1 gss.period=:period LOV custom Y select glp.period_num id, glp.period_num value, glp.period_name description from gl_periods glp, gl_period_statuses gps, gl_ledgers gl where gl.ledger_id=:$flex$.Ledger and gl.period_set_name=glp.period_set_name and gl.accounted_period_type=glp.period_type and glp.period_year=:$flex$.Fiscal_Year and glp.period_num=gps.period_num and glp.period_year=gps.period_year and gps.set_of_books_id=gl.ledger_id and gps.application_id=101 order by glp.period_num select gss.period value, null meaning from gl_subr_sta gss where gss.reference_no=:$flex$.Reference_No Y US Period Accounting period 7 70 1=1 gsrv.organization_id=:organization_id LOV custom Y select god.organization_id id, god.organization_code value, god.organization_name description from gmf_organization_definitions god where god.legal_entity_id=:$flex$.Legal_Entity and god.ledger_id=:$flex$.Ledger order by god.organization_code US Organization Inventory organization 8 80 1=1 gsrv.transaction_date>=:start_date DateTime select fnd_date.date_to_chardt(gss.period_start_date) value, null meaning from gl_subr_sta gss where gss.reference_no=:$flex$.Reference_No US Start Date Transaction start date 9 90 1=1 gsrv.transaction_date<=:end_date DateTime select fnd_date.date_to_chardt(gss.period_end_date) value, null meaning from gl_subr_sta gss where gss.reference_no=:$flex$.Reference_No US End Date Transaction end date 10 100 1=1 gsrv.entity_code=:entity_code LOV custom Y select xetv.entity_code id, xetv.name value, null description from xla_entity_types_vl xetv where xetv.application_id=555 and xetv.entity_code not in ('MANUAL','THIRD_PARTY_MERGE') and xetv.enabled_flag='Y' order by xetv.entity_code US Entity Code SLA entity code 11 110 1=1 gsrv.event_class=:event_class_code LOV custom Y select xecv.event_class_code id, xecv.name value, null description from xla_entity_types_vl xetv, xla_event_classes_vl xecv where xetv.application_id=555 and xetv.entity_code=:$flex$.Entity_Code and xecv.application_id=555 and xetv.entity_code=xecv.entity_code and xecv.enabled_flag='Y' order by xecv.name US Event Class SLA event class 12 120 1=1 gsrv.event_type=:event_type_code LOV custom Y select xetyv.event_type_code id, xetyv.name value, null description from xla_entity_types_vl xetv, xla_event_classes_vl xecv, xla_event_types_vl xetyv where xetv.application_id=555 and xetv.entity_code=:$flex$.Entity_Code and xecv.application_id=555 and xecv.entity_code=xetv.entity_code and xecv.event_class_code=:$flex$.Event_Class and xetyv.application_id=555 and xetyv.entity_code=xetv.entity_code and xetyv.event_class_code=xecv.event_class_code and xetyv.enabled_flag='Y' order by xetyv.name US Event Type SLA event type 13 130 :include_zero_amount_lines LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description N US Include Zero Amount Lines Include lines with zero amounts :$flex$.Entity_Code Entity Code Event Class :$flex$.Entity_Code Entity Code Event Type :$flex$.Event_Class Event Class Event Type :$flex$.Fiscal_Year Fiscal Year Period :$flex$.Ledger Ledger Cost Type :$flex$.Ledger Ledger Fiscal Year :$flex$.Ledger Ledger Organization :$flex$.Ledger Ledger Period :$flex$.Legal_Entity Legal Entity Cost Type :$flex$.Legal_Entity Legal Entity Ledger :$flex$.Legal_Entity Legal Entity Organization :$flex$.Reference_No Reference No Cost Type :$flex$.Reference_No Reference No End Date :$flex$.Reference_No Reference No Fiscal Year :$flex$.Reference_No Reference No Ledger :$flex$.Reference_No Reference No Legal Entity :$flex$.Reference_No Reference No Period :$flex$.Reference_No Reference No Start Date