GMF Detailed Subledger

Description
Categories: BI Publisher
Columns: Reference No, Header Id, Event Id, Legal Entity Id, Ledger Id, Operating Unit, Base Currency, Valuation Cost Type, Valuation Cost Type Id, Inventory Item Id ...
Application: Process Manufacturing Financials
Source: Detailed Subledger Report
Short Name: GMFDSUR
DB package:
  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)
Parameter Name SQL text Validation
Reference No
 
LOV Oracle
Legal Entity
 
LOV Oracle
Ledger
 
LOV Oracle
Cost Type
 
LOV Oracle
Fiscal Year
 
LOV Oracle
Period
 
LOV Oracle
Organization Code
 
LOV Oracle
Start Date
 
DateTime
End Date
 
DateTime
Entity Code
 
LOV Oracle
Event Class
 
LOV Oracle
Event Type
 
LOV Oracle
Include Zero Amount lines Flag
 
LOV Oracle