GMF GL Expense Allocation Definition

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