[Home] [Help]
PACKAGE BODY: APPS.PER_AE_WAGE_PROTECTION_PKG
Source
1 PACKAGE BODY per_ae_wage_protection_pkg as
2 /* $Header: peraewps.pkb 120.2 2010/08/31 06:56:33 bkeshary noship $ */
3 /*
4 ==============================================================================
5 MODIFICATION HISTORY
6
7 Name Date Version Bug Text
8 -------------- ----------- ------- ------- -----------------------------
9 BKeshary 27-May-2010 115.0 Initial Version
10 Bkeshary 11-Aug-2010 115.1 Modified for bug 10012575
11 Bkeshary 31-Aug-2010 115.2 Modified for bug 10012575
12 ==============================================================================
13 */
14
15 l_cnt_emps number :=0;
16 l_cnt_emp_paid number :=0;
17 l_amt_paid number :=0;
18 l_cnt_emp_not_paid number :=0;
19
20 function beforereport return boolean
21 is
22 begin
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_number_of_employees
34 (
35 p_month in varchar2,
36 p_year in varchar2,
37 p_employer_id in number,
38 p_business_group_id in per_all_assignments_f.business_group_id%type
39 ) return number
40 is
41
42 begin
43 SELECT count(distinct asg.assignment_id)
44 into l_cnt_emps
45 FROM per_all_assignments_f asg
46 ,pay_assignment_actions paa
47 ,pay_payroll_actions ppa
48 ,hr_soft_coding_keyflex hscl
49 ,per_periods_of_service pos
50 ,per_all_people_f ppf
51 WHERE asg.assignment_id = paa.assignment_id
52 AND paa.payroll_action_id = ppa.payroll_action_id
53 AND pos.period_of_service_id = asg.period_of_service_id
54 AND ppa.action_type in ('R','Q')
55 AND ppa.action_status = 'C'
56 AND paa.action_status = 'C'
57 AND trunc(ppa.date_earned,'MM') = trunc(last_day(to_date('01-' || p_month || '-' ||p_year,'DD-MM-YYYY')), 'MM')
58 AND trunc(last_day(to_date('01-' || p_month || '-' || p_year,'DD-MM-YYYY')),'MM') between asg.effective_start_date and asg.effective_end_date
59 AND hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
60 AND hscl.segment1 = to_char(p_employer_id)
61 AND ppf.person_id = asg.person_id
62 AND last_day(to_date('01-' || p_month || '-' ||p_year,'DD-MM-YYYY')) between ppf.effective_start_date and ppf.effective_end_date
63 AND ppf.per_information18 = (SELECT org_information1
64 FROM hr_organization_information
65 WHERE organization_id = p_business_group_id
66 AND org_information_context = 'AE_BG_DETAILS');
67
68 RETURN l_cnt_emps;
69 end get_number_of_employees;
70
71 FUNCTION get_number_of_emps_paid
72 (
73 p_month in varchar2,
74 p_year in varchar2,
75 p_employer_id in number,
76 p_business_group_id in per_all_assignments_f.business_group_id%type
77 ) return number
78 is
79 begin
80 select count(distinct asg.assignment_id), NVL(sum(ppv.value),0)
81 into l_cnt_emp_paid, l_amt_paid
82 from per_all_assignments_f asg,
83 pay_assignment_actions paa,
84 pay_payroll_actions ppa,
85 PAY_PRE_PAYMENTS_V ppv,
86 hr_soft_coding_keyflex hscl,
87 per_periods_of_service pos,
88 per_all_people_f ppf
89 where asg.assignment_id = paa.assignment_id
90 and paa.payroll_action_id = ppa.payroll_action_id
91 and pos.period_of_service_id = asg.period_of_service_id
92 and paa.action_status = 'C'
93 and ppa.action_type in ('P','U')
94 and ppa.action_status = 'C'
95 and trunc(ppa.date_earned,'MM') = trunc(last_day(to_date('01-' || p_month || '-' || p_year,'DD-MM-YYYY')), 'MM')
96 and trunc(last_day(to_date('01-' || p_month || '-' ||p_year,'DD-MM-YYYY')),'MM') between asg.effective_start_date and asg.effective_end_date
97 and hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
98 and hscl.segment1 = to_char(p_employer_id)
99 and ppf.person_id = asg.person_id
100 and last_day(to_date('01-' || p_month || '-' ||p_year,'DD-MM-YYYY')) between ppf.effective_start_date and ppf.effective_end_date
101 and ppf.per_information18 = (SELECT org_information1
102 FROM hr_organization_information
103 WHERE organization_id = p_business_group_id
104 AND org_information_context = 'AE_BG_DETAILS')
105 and paa.assignment_action_id = ppv.prepay_action_id
106 and ppv.business_group_id = p_business_group_id
107 and ppv.value > 0
108 and 'Paid' =ALL (select ppv1.status
109 from PAY_PRE_PAYMENTS_V ppv1
110 where ppv1.prepay_action_id = paa.assignment_action_id);
111 return l_cnt_emp_paid;
112 end get_number_of_emps_paid;
113
114
115 FUNCTION get_total_amt_paid RETURN NUMBER
116 IS
117 BEGIN
118 return l_amt_paid;
119 END get_total_amt_paid;
120
121 FUNCTION get_number_of_emps_not_paid RETURN NUMBER
122 is
123 begin
124 l_cnt_emp_not_paid := l_cnt_emps - l_cnt_emp_paid ;
125 return l_cnt_emp_not_paid;
126 END get_number_of_emps_not_paid;
127
128 FUNCTION get_month
129 (
130 p_month varchar2,
131 p_mon_number varchar2
132 ) return varchar2
133 is
134 l_month varchar2(10);
135 begin
136 if p_month = p_mon_number then
137 l_month := 'X';
138 else
139 l_month := NULL;
140 end if;
141 return l_month;
142 end get_month;
143
144 end per_ae_wage_protection_pkg; -- package body