GMF Detailed Subledger

Description
Categories: BI Publisher, Manufacturing
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 NameSQL textValidation
Include Zero Amount lines Flag
 
LOV Oracle
Event Type
 
LOV Oracle
Event Class
 
LOV Oracle
Entity Code
 
LOV Oracle
End Date
 
DateTime
Start Date
 
DateTime
Organization Code
 
LOV Oracle
Period
 
LOV Oracle
Fiscal Year
 
LOV Oracle
Cost Type
 
LOV Oracle
Ledger
 
LOV Oracle
Legal Entity
 
LOV Oracle
Reference No
 
LOV Oracle