GMF GL Expense Allocation Detail

Description
Categories: BI Publisher, Manufacturing
Application: Process Manufacturing Financials
Source: GL Expense Allocation Detail Report (XML)
Short Name: CMR11USR_XML
DB package: GMF_CMR11USR_XMLP_PKG
SELECT 
    'Allocations' dtl_ind, 
    m.alloc_code alloc_code, 
    m.alloc_desc alloc_desc , 
    i.account_key_type acct_type, 
    i.line_no line_no, 
    i.account_id, 
    i.amount amount, 
    d.percent_allocation percent_alloc, 
    d.allocated_expense_amt alloc_mp_amt, 
    t.item_number item_no, 
    t.description item_desc, 
    b.balance_type balance_type, 
    b.bas_ytd_ptd bas_ytd, 
    mp.organization_code organization_code, 
    gmf_utilities_grp.get_organization_name(mp.organization_id) organization_name,
    c.cost_cmpntcls_code cost_cmpt_code, 
    c.cost_cmpntcls_desc cost_cmpt_desc, 
    b.analysis_code analysis_code, 
    a.cost_analysis_desc analysis_desc, 
    typ.description ytd_ptd_desc, 
    typ1.description balance_desc, 
    b.alloc_method mthd, 
	GMF_CMR11USR_XMLP_PKG.account_codeformula(i.account_id) account_code, 
	GMF_CMR11USR_XMLP_PKG.account_descformula(i.account_id) account_desc
FROM gl_aloc_inp i, 
    gl_aloc_mst m, 
    gl_aloc_dtl d, 
    gl_aloc_bas b, 
    gmf_period_statuses gps,
    mtl_item_flexfields t, 
    mtl_parameters mp, 
    cm_cmpt_mst c, 
    cm_alys_mst a , 
    gem_lookups typ, 
    gem_lookups typ1,
    gmd_org_access_vw oa
WHERE 
    m.legal_entity_id = :legal_entity_id1 	
    --m.legal_entity_id = :legal_entity_id 	
    AND i.account_key_type = '0' 
    AND i.calendar_code = :cost_calendar
    --AND i.period_code = :period_code
	AND i.period_code = :period_code1
    AND i.alloc_id = m.alloc_id 
    AND d.period_id = gps.period_id
    AND gps.calendar_code = i.calendar_code
    AND gps.period_code = i.period_code	
    AND d.alloc_id = i.alloc_id 
    AND d.line_no = i.line_no 
    AND b.alloc_id = i.alloc_id 
    AND b.line_no = i.line_no 
    AND b.inventory_item_id = t.inventory_item_id 
    AND b.organization_id = t.organization_id 
    AND b.organization_id = mp.organization_id 
    AND b.cmpntcls_id = c.cost_cmpntcls_id 
    AND b.analysis_code = a.cost_analysis_code 
    AND b.bas_ytd_ptd = to_number(typ.lookup_code(+)) 
    AND b.balance_type =to_number(typ1.lookup_code(+)) 
    AND typ.lookup_type(+) = 'BAL_YTD_PTD' 
    AND typ1.lookup_type(+) = 'BALANCE_TYPE' 
    AND b.organization_id = oa.organization_id 	
&AllocationCP 
ORDER BY 2 asc, 4 desc,5 asc
Parameter Name SQL text Validation
Non Block Sql
 
LOV Oracle
SY ALL
 
Currency Code
 
User Id
 
Number
To Allocation Code
 
LOV Oracle
From Allocation Code
 
LOV Oracle
Period Code
 
LOV Oracle
CALENDAR_CODE
 
LOV Oracle
Legal Entity
 
LOV Oracle
Ask a question