1 PACKAGE BODY pay_pqh_rbc
2 /* $Header: pypqhrbc.pkb 120.0.12010000.1 2008/07/27 23:26:32 appldev ship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_pqh_rbc
21
22 Description : delivery of eventy qulaifier for pqh rate by
23 criteria , for retro notif
24
25 Uses :
26
27 Change List
28 -----------
29 Date Name Vers Bug No Description
30 ---- ---- ---- ------ -----------
31 06-Nov-2006 Tbattoo 110.0 Created.
32 03-Jan-2008 AYegappa 115.6 Corrected determine_rbc_rate call
33 *******************************************************************/
34 AS
35
36
37
38 FUNCTION RBC_event_qualifier return varchar2
39
40 is
41
42 cursor get_pact_de is
43 select ppa.date_earned
44 from pay_payroll_actions ppa,
45 pay_element_entries_f pee,
46 pay_assignment_actions paa
47 where paa.assignment_id=pay_interpreter_pkg.g_asg_id
48 and paa.payroll_action_id=ppa.payroll_action_id
49 and ppa.action_type in ('R','Q')
50 and ppa.date_earned >=pay_interpreter_pkg.g_effective_date
51 and ppa.date_earned between pee.effective_start_date and pee.effective_end_date
52 and pee.element_entry_id=pay_interpreter_pkg.g_ee_id
53 order by ppa.date_earned desc;
54
55 cursor get_bg is
56 select distinct business_group_id
57 from pay_element_entries_f pee,
58 per_all_assignments_f paf
59 where pee.element_entry_id=pay_interpreter_pkg.g_ee_id
60 and pee.assignment_id=paf.assignment_id
61 and pay_interpreter_pkg.g_effective_date between
62 pee.effective_start_date and pee.effective_end_date
63 and pay_interpreter_pkg.g_effective_date between
64 paf.effective_start_date and paf.effective_end_date;
65
66 cursor rate_effected is
67 select 'Y'
68 from dual
69 where exists (
70 select 'Y'
71 from pay_element_entries_f pee
72 ,pqh_criteria_rate_elements pcre
73 ,pqh_rate_matrix_rates_f prmr
74 where pee.element_entry_id=pay_interpreter_pkg.g_ee_id
75 and pcre.element_type_id=pee.element_type_id
76 and pcre.criteria_rate_defn_id=prmr.criteria_rate_defn_id
77 and prmr.rate_matrix_rate_id=pay_interpreter_pkg.g_object_key
78 and pee.effective_end_date >= prmr.effective_start_date
79 and pee.effective_start_date <= prmr.effective_end_date)
80 or exists (
81 Select 'Y'
82 from pay_element_entries_f pee,
83 pqh_criteria_rate_elements pcre,
84 pqh_criteria_rate_factors pcrf,
85 pqh_rate_matrix_rates_f prmr
86 where pee.element_entry_id=pay_interpreter_pkg.g_ee_id
87 and pcre.element_type_id=pee.element_type_id
88 and pcre.criteria_rate_defn_id = pcrf.criteria_rate_defn_id
89 and pcrf.parent_criteria_rate_defn_id = prmr.criteria_rate_defn_id
90 and prmr.rate_matrix_rate_id = pay_interpreter_pkg.g_object_key
91 and pee.effective_end_date >= prmr.effective_start_date
92 and pee.effective_start_date <= prmr.effective_end_date);
93
94 l_exists varchar2(1);
95 l_rate_factors pqh_rbc_rate_retrieval.g_rbc_factor_tbl;
96 l_rate_factor_cnt number;
97 l_min_rate number;
98 l_max_rate number;
99 l_rate number;
100 l_mid_rate number;
101 l_bus_grp number;
102 l_date_earned date;
103
104 begin
105
106
107 open rate_effected;
108 fetch rate_effected into l_exists;
109
110 if rate_effected%notfound then
111 close rate_effected;
112 return 'N';
113 else
114 --if does exist then call rbc api
115 close rate_effected;
116 open get_bg;
117 fetch get_bg into l_bus_grp;
118
119 if get_bg%notfound then
120 close get_bg;
121 return 'N';
122 else
123 close get_bg;
124
125 open get_pact_de;
126 loop
127 fetch get_pact_de into l_date_earned;
128
129 if get_pact_de%notfound then
130 close get_pact_de;
131 return 'N';
132 end if;
133
134 /* Added parameter names for bug 6695937 */
135 pqh_rbc_rate_retrieval.determine_rbc_rate(
136 p_element_entry_id => pay_interpreter_pkg.g_ee_id,
137 p_business_group_id => l_bus_grp,
138 p_effective_date => l_date_earned,
139 p_rate_factors => l_rate_factors,
140 p_rate_factor_cnt => l_rate_factor_cnt,
141 p_min_rate => l_min_rate,
142 p_mid_rate => l_mid_rate,
143 p_max_rate => l_max_rate,
144 p_rate => l_rate);
145
146 for i in 1..l_rate_factor_cnt loop
147 if (l_rate_factors(i).rate_matrix_rate_id=pay_interpreter_pkg.g_object_key)
148 then
149 close get_pact_de;
150 return 'Y';
151 end if;
152 end loop;
153 end loop;
154 close get_pact_de;
155 return 'N';
156 end if;
157 end if;
158
159 end;
160
161 end pay_pqh_rbc;
[Home] [Help]