PER Employment Equity Income Differential (PDF)

Description
Categories: BI Publisher, Human Resources
Application: Human Resources
Source: Employment Equity Income Differential (PDF)
Short Name: PERZAEID_XML
DB package: per_za_ee_differential_pkg
select :p_report_date                                         report_date,
       :p_business_group_id                                   business_group_id,
	   haou.name                                              legal_entity,
       nvl(:p_legal_entity_id,haou.organization_id)           legal_entity_id,
       hoi.org_information5                           co_registration_no,
       hoi.org_information3                           sars_registration_no,
       hoi.org_information6                           uif_no,
       hoi1.ORG_INFORMATION3                           industry_sector,
       decode(hoi1.ORG_INFORMATION5, 'Y', 'Yes', 'No') organ_of_state,
       decode(hoi1.ORG_INFORMATION4, 'Y', 'YES', 'NO') voluntary_compliance,
       hoi.ORG_INFORMATION1                           Trade_name,
       hoi.ORG_INFORMATION11                          DTI_Registration_Name,
       hoi.ORG_INFORMATION2                           Comp_reg_No,
       hoi.ORG_INFORMATION3                           Tax_Ref_No,
       hoi.ORG_INFORMATION6                           UIF_No,
       hoi1.ORG_INFORMATION1                          EE_Ref_No,
       decode (hoi1.ORG_INFORMATION2,null,null,'7','Educational Institution',
           (Select hlb.meaning from hr_lookups hlb
           Where hlb.lookup_type = 'ZA_EE_BUSINESS_TYPE'
           and hlb.lookup_code = hoi1.org_information2
           and hlb.application_id = '800'))                          business_type,
       decode(hoi1.ORG_INFORMATION14,null,'No','Yes')  grp_holding_cmpny_yes_no,
	   hoi1.ORG_INFORMATION14                          grp_holding_cmpny,
       hoi2.ORG_INFORMATION1                          Contact_Tele_No,
       hoi2.ORG_INFORMATION2                          Contact_Fax_No,
       hoi2.ORG_INFORMATION3                          Contact_Email_Address,
       hoi2.ORG_INFORMATION4                              post_address_1,
       hoi2.ORG_INFORMATION5                              post_address_2,
       hoi2.ORG_INFORMATION6                              post_address_3,
       hoi2.ORG_INFORMATION8                              post_town_or_city,
       hoi2.ORG_INFORMATION7                              post_postal_code,
        decode( hoi2.ORG_INFORMATION9, null, null, (
              select meaning from hr_lookups
              Where lookup_type = 'ZA_PROVINCE' and
              lookup_code = hoi2.ORG_INFORMATION9 and
              application_id = '800'))                                      post_postal_province,
       hoi2.ORG_INFORMATION10                             Physical_address_1,
       hoi2.ORG_INFORMATION11                             Physical_address_2,
       hoi2.ORG_INFORMATION12                             Physical_address_3,
       hoi2.ORG_INFORMATION14                             Physical_town_or_city,
       hoi2.ORG_INFORMATION13                             Physical_postal_code,
               decode(hoi2.ORG_INFORMATION15,null,null,(
                          Select meaning from hr_lookups 
                           Where lookup_type = 'ZA_PROVINCE' and
                           lookup_code = hoi2.ORG_INFORMATION15
                          and application_id = '800')  )                 Physical_province,
       hoi1.ORG_INFORMATION6                              CEO_Name_Surname,
       hoi1.ORG_INFORMATION7                              CEO_Tele_no,
       hoi1.ORG_INFORMATION8                              CEO_Fax_No,
       hoi1.ORG_INFORMATION9                              CEO_email_add,
       hoi1.ORG_INFORMATION10                             EMgr_Name_Surname,
       hoi1.ORG_INFORMATION11                             EMgr_Tele_no,
       hoi1.ORG_INFORMATION12                             EMgr_Fax_No,
       hoi1.ORG_INFORMATION13                             EMgr_email_add,
	   per_za_ee_differential_pkg.get_seta_classification(:P_BUSINESS_GROUP_ID,:P_LEGAL_ENTITY_ID) seta_class,
	   :P_SUBMISSION_DATE   submission_date,
	   per_za_ee_differential_pkg.get_total('Permanent','FA','ED',haou.organization_id) FA_TOT_P,
	   per_za_ee_differential_pkg.get_total('Permanent','FC','ED',haou.organization_id) FC_TOT_P,
	   per_za_ee_differential_pkg.get_total('Permanent','FW','ED',haou.organization_id) FW_TOT_P,
	   per_za_ee_differential_pkg.get_total('Permanent','FI','ED',haou.organization_id) FI_TOT_P,
	   per_za_ee_differential_pkg.get_total('Permanent','MA','ED',haou.organization_id) MA_TOT_P,
	   per_za_ee_differential_pkg.get_total('Permanent','MC','ED',haou.organization_id) MC_TOT_P,
	   per_za_ee_differential_pkg.get_total('Permanent','MW','ED',haou.organization_id) MW_TOT_P,
	   per_za_ee_differential_pkg.get_total('Permanent','MI','ED',haou.organization_id) MI_TOT_P,
	   per_za_ee_differential_pkg.get_total('Permanent','FA','EDI',haou.organization_id) FA_TOT_P_INC,
	   per_za_ee_differential_pkg.get_total('Permanent','FC','EDI',haou.organization_id) FC_TOT_P_INC,
	   per_za_ee_differential_pkg.get_total('Permanent','FW','EDI',haou.organization_id) FW_TOT_P_INC,
	   per_za_ee_differential_pkg.get_total('Permanent','FI','EDI',haou.organization_id) FI_TOT_P_INC,
	   per_za_ee_differential_pkg.get_total('Permanent','MA','EDI',haou.organization_id) MA_TOT_P_INC,
	   per_za_ee_differential_pkg.get_total('Permanent','MC','EDI',haou.organization_id) MC_TOT_P_INC,
	   per_za_ee_differential_pkg.get_total('Permanent','MW','EDI',haou.organization_id) MW_TOT_P_INC,
	   per_za_ee_differential_pkg.get_total('Permanent','MI','EDI',haou.organization_id) MI_TOT_P_INC,
	   per_za_ee_differential_pkg.get_total('Permanent','MF','EDF',haou.organization_id) MF_TOT_P,
	   per_za_ee_differential_pkg.get_total('Permanent','FF','EDF',haou.organization_id) FF_TOT_P,
	   per_za_ee_differential_pkg.get_total('Permanent','MF','EDFI',haou.organization_id) MF_TOT_P_INC,
	   per_za_ee_differential_pkg.get_total('Permanent','FF','EDFI',haou.organization_id) FF_TOT_P_INC,
	   per_za_ee_differential_pkg.get_row_total('Permanent','NUM',haou.organization_id) TOT_P,
	   per_za_ee_differential_pkg.get_row_total('Permanent','INC',haou.organization_id) TOT_P_INC,
	   per_za_ee_differential_pkg.get_total('Non-Permanent','FA','ED',haou.organization_id) FA_TOT_NP,
	   per_za_ee_differential_pkg.get_total('Non-Permanent','FC','ED',haou.organization_id) FC_TOT_NP,
	   per_za_ee_differential_pkg.get_total('Non-Permanent','FW','ED',haou.organization_id) FW_TOT_NP,
	   per_za_ee_differential_pkg.get_total('Non-Permanent','FI','ED',haou.organization_id) FI_TOT_NP,
	   per_za_ee_differential_pkg.get_total('Non-Permanent','MA','ED',haou.organization_id) MA_TOT_NP,
	   per_za_ee_differential_pkg.get_total('Non-Permanent','MC','ED',haou.organization_id) MC_TOT_NP,
	   per_za_ee_differential_pkg.get_total('Non-Permanent','MW','ED',haou.organization_id) MW_TOT_NP,
	   per_za_ee_differential_pkg.get_total('Non-Permanent','MI','ED',haou.organization_id) MI_TOT_NP,
	   per_za_ee_differential_pkg.get_total('Non-Permanent','FA','EDI',haou.organization_id) FA_TOT_NP_INC,
	   per_za_ee_differential_pkg.get_total('Non-Permanent','FC','EDI',haou.organization_id) FC_TOT_NP_INC,
	   per_za_ee_differential_pkg.get_total('Non-Permanent','FW','EDI',haou.organization_id) FW_TOT_NP_INC,
	   per_za_ee_differential_pkg.get_total('Non-Permanent','FI','EDI',haou.organization_id) FI_TOT_NP_INC,
	   per_za_ee_differential_pkg.get_total('Non-Permanent','MA','EDI',haou.organization_id) MA_TOT_NP_INC,
	   per_za_ee_differential_pkg.get_total('Non-Permanent','MC','EDI',haou.organization_id) MC_TOT_NP_INC,
	   per_za_ee_differential_pkg.get_total('Non-Permanent','MW','EDI',haou.organization_id) MW_TOT_NP_INC,
	   per_za_ee_differential_pkg.get_total('Non-Permanent','MI','EDI',haou.organization_id) MI_TOT_NP_INC,
	   per_za_ee_differential_pkg.get_total('Non-Permanent','MF','EDF',haou.organization_id) MF_TOT_NP,
	   per_za_ee_differential_pkg.get_total('Non-Permanent','FF','EDF',haou.organization_id) FF_TOT_NP,
	   per_za_ee_differential_pkg.get_total('Non-Permanent','MF','EDFI',haou.organization_id) MF_TOT_NP_INC,
	   per_za_ee_differential_pkg.get_total('Non-Permanent','FF','EDFI',haou.organization_id) FF_TOT_NP_INC,
	   per_za_ee_differential_pkg.get_row_total('Non-Permanent','NUM',haou.organization_id) TOT_NP,
	   per_za_ee_differential_pkg.get_row_total('Non-Permanent','INC',haou.organization_id) TOT_NP_INC
from   hr_organization_information hoi,
       hr_all_organization_units   haou,
       hr_organization_information hoi1, 
       hr_organization_information hoi2 
where  haou.business_group_id = :P_BUSINESS_GROUP_ID
and    haou.organization_id = nvl(:P_LEGAL_ENTITY_ID,haou.organization_id)
and    haou.organization_id = hoi.organization_id
AND    hoi1.org_information_context(+) = 'ZA_EMPLOYMENT_EQUITY_INFO'
AND    hoi2.org_information_context(+) = 'ZA_LEGAL_ENTITY_CONTACT_INFO'
AND    hoi1.organization_id(+) = haou.organization_id
AND    hoi2.organization_id(+) = haou.organization_id
and    hoi.org_information_context = 'ZA_LEGAL_ENTITY'
order  by 3
Parameter Name SQL text Validation
Business Group ID
 
Number
Salary Calculation Method
 
LOV Oracle
Report Submission Date
 
Date
Legal Entity
 
LOV Oracle
Report Date
 
Date