1 PACKAGE BODY per_za_ee_differential_pkg as
2 /* $Header: perzaeid.pkb 120.0.12010000.1 2009/12/08 06:30:12 rbabla noship $ */
3 /*
4 ==============================================================================
5 MODIFICATION HISTORY
6
7 Name Date Version Bug Text
8 -------------- ----------- ------- ------- -----------------------------
9 R Babla 24-Nov-2009 115.0 9112237 Initial Version
10 ==============================================================================
11 */
12
13 function beforereport return boolean
14 is
15 begin
16 -- hr_utility.trace_on(NULL,'PERZAEID');
17 hr_utility.set_location('Entered beforereport',10);
18 hr_utility.set_location('P_REPORT_DATE:'||P_REPORT_DATE,10);
19 hr_utility.set_location('P_BUSINESS_GROUP_ID:'||P_BUSINESS_GROUP_ID,10);
20 hr_utility.set_location('P_LEGAL_ENTITY_ID:'||P_LEGAL_ENTITY_ID,10);
21 hr_utility.set_location('P_SALARY_METHOD:'||P_SALARY_METHOD,10);
22 per_za_employment_equity_pkg.init_g_cat_lev_new_table(P_REPORT_DATE,P_BUSINESS_GROUP_ID,P_LEGAL_ENTITY_ID,P_SALARY_METHOD);
23 return TRUE;
24 end;
25
26 function afterreport return boolean
27 is
28 begin
29 --reset_tables;
30 return TRUE;
31 end;
32
33 function get_seta_classification
34 (
35 p_business_group_id in per_all_assignments_f.business_group_id%type,
36 p_legal_entity_id in per_assignment_extra_info.aei_information7%type default null
37 ) return varchar2
38 is
39 cursor seta_class (p_org_id in number) is
40 Select org_information4 Seta_Classification
41 From hr_organization_information hoi
42 Where hoi.organization_id = p_org_id
43 And hoi.org_information_context = 'ZA_NQF_SETA_INFO';
44 begin
45 for seta_rec in seta_class (p_legal_entity_id)
46 loop
47 return (seta_rec.Seta_Classification);
48 end loop;
49
50 for seta_rec1 in seta_class (p_business_group_id)
51 loop
52 return(seta_rec1.Seta_Classification);
53 end loop;
54
55 return (null);
56 end get_seta_classification;
57
58
59 FUNCTION get_total(p_employment_type IN VARCHAR2
60 ,p_emp_type varchar2
61 ,p_report_id varchar2
62 ,p_legal_entity_id NUMBER) RETURN NUMBER
63 IS
64 l_cnt number:=0;
65 BEGIN
66 if p_report_id not in ('EDF','EDFI') then
67 select nvl(sum(decode(p_emp_type,'FA',FA,'FC',FC,'FI',FI,'FW',FW,'MA',MA,'MC',MC,'MI',MI,'MW',MW)),0)
68 into l_cnt
69 from per_za_employment_equity
70 where legal_entity_id = p_legal_entity_id
71 and report_id = p_report_id
72 and employment_type=p_employment_type;
73 elsif p_emp_type = 'FF' then
74 select nvl(sum(FA)+sum(FC)+sum(FI)+sum(FW),0)
75 into l_cnt
76 from per_za_employment_equity
77 where legal_entity_id = p_legal_entity_id
78 and report_id = p_report_id
79 and employment_type=p_employment_type;
80 elsif p_emp_type = 'MF' then
81 select nvl(sum(MA)+sum(MC)+sum(MI)+sum(MW),0)
82 into l_cnt
83 from per_za_employment_equity
84 where legal_entity_id = p_legal_entity_id
85 and report_id = p_report_id
86 and employment_type=p_employment_type;
87 end if;
88 return l_cnt;
89 exception
90 when no_data_found then
91 return 0;
92 END GET_TOTAL;
93
94
95 FUNCTION get_row_total(p_employment_type IN VARCHAR2
96 ,p_inc_num varchar2
97 ,p_legal_entity_id NUMBER) RETURN NUMBER
98 IS
99 l_cnt number;
100 begin
101 if p_inc_num = 'NUM' then
102 --Calculate the count of employees
103 select nvl(SUM(TOTAL),0)
104 into l_cnt
105 from per_za_employment_equity
106 where legal_entity_id = p_legal_entity_id
107 and report_id in ('ED','EDF')
108 and employment_type=p_employment_type;
109 else
110 --Calculate the sum of employee income
111 select nvl(SUM(TOTAL),0)
112 into l_cnt
113 from per_za_employment_equity
114 where legal_entity_id = p_legal_entity_id
115 and report_id in ('EDI','EDFI')
116 and employment_type=p_employment_type;
117 end if;
118 RETURN l_cnt;
119 end get_row_total;
120
121
122 end per_za_ee_differential_pkg; -- package body
[Home] [Help]