GMF Detailed Subledger

  SELECT  /*+ push_pred() no_expand */
                            sr.reference_no,
                            sr.header_id,
                            sr.event_id,
                            sr.legal_entity_id,
                            sr.ledger_id,
                            sr.operating_unit,
                            sr.base_currency,
                            sr.valuation_cost_type,
                            sr.valuation_cost_type_id,
                            sr.inventory_item_id,
                            sr.organization_id,
                            sr.item_revision,
                            sr.transaction_source_type_id,
                            sr.transaction_action_id,
                            sr.transaction_type_id,
                            sr.entity_code,
                            sr.event_class,
                            sr.event_type,
                            sr.transaction_id,
                            to_char(sr.transaction_date,'DD-MON-YYYY HH24:MI:SS') transaction_date,
                            sr.transaction_quantity,
                            sr.transaction_uom,
                            gmf_subled_rep_pkg.get_org_name(sr.organization_id)                                 org_name,
                            gmf_subled_rep_pkg.get_item_desc(sr.inventory_item_id,sr.organization_id)           item_desc,
                            gmf_subled_rep_pkg.get_entity_code_desc(sr.entity_code)                             entity_code_desc,
                            gmf_subled_rep_pkg.get_event_class_desc(sr.entity_code,sr.event_class)              event_class_desc,
                            gmf_subled_rep_pkg.get_event_type_desc(sr.entity_code,sr.event_class,sr.event_type) event_type_desc,
                            sr.cm_adjustment_type,
                            sr.cm_adjust_cost,
                            sr.cm_reason_code,
                            sr.cm_period_code,
                            sr.cm_calendar_code,
                            sr.cm_lot_number,
                            sr.in_subinventory_code,
							sr.in_transfer_subinventory, 
                            sr.pm_batch_number,
                            sr.pm_batch_status,
                            to_char(sr.pm_actual_start_date,'DD-MON-YYYY HH24:MI:SS') pm_actual_start_date,
                            to_char(sr.pm_actual_cmplt_date,'DD-MON-YYYY HH24:MI:SS') pm_actual_cmplt_date,
                            sr.pm_formula_no,
                            sr.pm_formula_vers,
                            sr.pm_routing_no,
                            sr.pm_routing_vers,
                            sr.pm_recipe_no,
                            sr.pm_recipe_version,
                            sr.pm_resources,
                            sr.po_purchase_number,
                            sr.po_supplier_name,
                            sr.po_receipt_number,
                            sr.po_receipt_line_number,
                            sr.om_order_number,
                            sr.om_customer_name,
                            sr.om_line_number,
                            sr.om_shipment_number
                    FROM gmf_subledger_rep_v sr,
                         gl_subr_sta gss
                    WHERE
                          sr.reference_no           = NVL(:P_REFERENCE_NO,sr.reference_no)   
                      AND sr.legal_entity_id        = :P_LEGAL_ENTITY_ID
                      AND sr.ledger_id              = :P_LEDGER_ID
                      AND sr.valuation_cost_type_id = :P_COST_TYPE_ID
                      AND sr.organization_id        = NVL(:P_ORGANIZATION_ID,sr.organization_id)
                      AND sr.reference_no           = gss.reference_no
                      AND gss.fiscal_year           = :P_FISCAL_YEAR
                      AND gss.period                = :P_PERIOD
                      AND sr.entity_code            = NVL(:P_ENTITY_CODE,sr.entity_code)
                      AND sr.event_class            = NVL(:P_EVENT_CLASS, sr.event_class)
                      AND sr.event_type             = DECODE((SELECT INSTR(:P_EVENT_TYPE,'_ALL') FROM DUAL),0,:P_EVENT_TYPE,sr.event_type)
                      AND sr.transaction_date  >= TO_DATE(NVL(:p_start_dt , TO_CHAR(sr.transaction_date, 'DD-MON-YYYY HH24:MI:SS')),'DD-MON-YYYY HH24:MI:SS' )
                      AND sr.transaction_date  <= TO_DATE(NVL(:p_end_dt , TO_CHAR(sr.transaction_date, 'DD-MON-YYYY HH24:MI:SS')),'DD-MON-YYYY HH24:MI:SS' )
                      AND ((NVL(:P_INCLUDE_ZERO_AMOUNT_LINES, 'N') = 'Y') 
                            OR EXISTS (SELECT 1 from gmf_xla_extract_lines el 
                                  WHERE el.header_id = sr.header_id
                                    AND ( ((NVL(:P_INCLUDE_ZERO_AMOUNT_LINES, 'N') = 'N') 
                                               AND (abs(el.base_amount) >= 0.005 OR abs(el.trans_amount) >= 0.005)))))
                      ORDER BY sr.entity_code, sr.organization_id,
                               DECODE(sr.entity_code,'INVENTORY', sr.TRANSACTION_ID,
                                                     'REVALUATION',sr.TRANSACTION_ID,
                                                     'ORDERMANAGEMENT',sr.OM_ORDER_NUMBER,sr.organization_id),
                               DECODE(sr.entity_code,'PRODUCTION', sr.PM_BATCH_NUMBER,
                                                     'PURCHASING', sr.PO_PURCHASE_NUMBER,sr.entity_code)