AP Employee Listing - draft

Description
Categories: BI Publisher, Financials, Procurement
Application: Payables
Source: Employee Listing (XML) - Not Supported: Reserved For Future Use
Short Name: APXEMELR_XML
DB package: AP_APXEMELR_XMLP_PKG
            SELECT  null C_A_FLEXDATA,
                    substr(e.employee_num,1,30) C_A_EMP_NUM,
                    substr(nvl(e.last_name,'No Last Name')||', '||
                    nvl(e.first_name,'No First Name')||' '||
                    substr(nvl(e.middle_name,' '),1,1),1,20) C_A_EMPLOYEE_NAME,
                    e.inactive_date C_A_INACTIVE,
                    nvl(e.supervisor_id,-1) C_A_SUP_ID,
                    nvl(e.location_id,-1) C_A_LOC_ID,
                    nvl(e.default_code_combination_id,0) C_A_CODE_COMBINATION,
                    e.employee_id C_A_EMP_ID,
                    substr(nvl(s.last_name,'No Last Name')||','||
                    nvl(s.first_name,'No First Name')||' '||
                    substr(nvl(s.middle_names,' '),1,1),1,20) C_A_SUPERVISOR,
                    s.employee_number C_A_SUP_NUM,
                    substr(l.location_code,1,10) C_A_LOCATION_CODE,
                fnd_flex_xml_publisher_apis.process_kff_combination_1('c_a_flexfield', 'SQLGL', 'GL#', g.CHART_OF_ACCOUNTS_ID, NULL, g.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_A_FLEXFIELD
            FROM    per_employees_x e, per_all_people_f s, hr_locations_all l,
                    gl_code_combinations g, financials_system_parameters fsp
            WHERE  (e.supervisor_id = s.person_id(+))
            AND    (e.location_id = l.location_id(+))
            AND    (e.default_code_combination_id = g.code_combination_id(+))
            AND     g.chart_of_accounts_id(+) = :c_chart_of_accounts_id
            AND    (:p_sup_chose_m = 'All' OR
                    :p_sup_chose_m = s.person_id)
            AND    (:p_loc_chose_m = 'All' OR UPPER(:p_loc_chose_m) =
                                            UPPER(l.location_code))
            AND  trunc (sysdate) between s.effective_start_date (+) and s.effective_end_date (+)
            AND  nvl (e.inactive_date, to_date('31/12/4712', 'DD/MM/YYYY')) >= trunc (sysdate)
            AND e.business_group_id = fsp.business_group_id
            ORDER BY
             DECODE(SUBSTR(:p_out_type,1,4), 'Numb', e.employee_num, 'Last',
                    upper(e.last_name), 'Supe', upper(s.last_name), 'Loca',
                    l.location_code, e.employee_num),
             DECODE(SUBSTR(:p_out_type,1,4), 'Numb', upper(e.last_name), 'Last',
                    e.first_name, 'Supe',s.first_name,'Loca',
                    upper(e.last_name), upper(e.last_name)),
             DECODE(SUBSTR(:p_out_type,1,4), 'Numb', upper(e.first_name), 'Last',
                     null, 'Supe', upper(e.last_name), 'Loca', e.first_name,
                                                  e.first_name)
Parameter Name SQL text Validation
Ledger
 
Location (skip for All)
 
LOV Oracle
Supervisor Name (skip for All)
 
LOV Oracle
Include Active/Inactive?
 
LOV Oracle
Sort Employees By
 
LOV Oracle