GMF GL Expense Allocation Definition

Description
Categories: BI Publisher, Manufacturing
Application: Process Manufacturing Financials
Source: GL Expense Allocation Definition Report (XML)
Short Name: CMR10USR_XML
DB package: GMF_CMR10USR_XMLP_PKG
SELECT 
    'Allocation Definition' dtl_ind, 
    m.alloc_code alloc_code, 
    m.alloc_desc alloc_desc, 
    b.line_no line_no, 
    b.organization_id, 
    gmf_utilities_grp.get_organization_code(b.organization_id) organization_code,
    gmf_utilities_grp.get_organization_name(b.organization_id) organization_name,
    i.item_number item_no, 
    i.description item_desc, 
    b.basis_account_id,
    b.balance_type bal_type, 
    b.bas_ytd_ptd bas_ytd, 
    b.fixed_percent percent, 	
    c.cost_cmpntcls_code cmpt_code, 
    c.cost_cmpntcls_desc cmpt_desc, 
    b.analysis_code anly_code, 
    a.cost_analysis_desc anyl_desc, 
    typ.description ytd_ptd_desc, 
    typ1.description balance_desc, 
	GMF_CMR10USR_XMLP_PKG.basis_account_codeformula(b.basis_account_id) basis_account_code, 
	GMF_CMR10USR_XMLP_PKG.basis_account_descformula(b.basis_account_id) basis_account_desc
FROM gl_aloc_mst m, 
    gl_aloc_bas b, 
    cm_cmpt_mst c, 
    cm_alys_mst a, 
    gem_lookups typ, 
    gem_lookups typ1, 
    mtl_item_flexfields i,
    gmd_org_access_vw oa
WHERE m.legal_entity_id = :LEGAL_ENTITY_ID
    AND b.alloc_id = m.alloc_id 
    AND b.inventory_item_id = i.inventory_item_id 
    AND b.organization_id = i.organization_id     
    AND b.cmpntcls_id = c.cost_cmpntcls_id 
    AND b.analysis_code = a.cost_analysis_code 
    AND b.delete_mark = 0 
    AND c.delete_mark = 0 
    AND a.delete_mark = 0 
    AND nvl(b.bas_ytd_ptd,0) = decode(b.bas_ytd_ptd,null,0,to_number(typ.lookup_code)) 
    AND typ.lookup_type (+) = decode(b.bas_ytd_ptd,null,null,'BAL_YTD_PTD') 
    AND nvl(b.balance_type,0) = decode(b.balance_type,null,0,to_number(typ1.lookup_code)) 
    AND typ1.lookup_type(+) = decode(b.balance_type,null,null,'BALANCE_TYPE' ) 
    AND oa.organization_id = b.organization_id
    &AllocationCP 
ORDER BY alloc_code, dtl_ind, line_no asc
Parameter Name SQL text Validation
Non Block Sql
 
LOV Oracle
SY ALL
 
User Id
 
Number
To Allocation Code
 
LOV Oracle
From Allocation Code
 
LOV Oracle
Legal Entity
 
LOV Oracle