1 PACKAGE BODY PAY_PAYUSTIM_XMLP_PKG AS
2 /* $Header: PAYUSTIMB.pls 120.0 2007/12/28 06:48:02 srikrish noship $ */
3
4 function AfterPForm return boolean is
5 l_param varchar2(240);
6 L_FLAG VARCHAR2(5);
7 begin
8
9 declare
10 l_trace varchar2(30):='';
11 CURSOR p_trace IS select upper(parameter_value)
12 from pay_action_parameters
13 where parameter_name = 'TRACE';
14
15
16 begin
17
18 OPEN p_trace;
19 LOOP
20 FETCH p_trace INTO l_trace;
21 EXIT WHEN p_trace%NOTFOUND;
22
23 if l_trace = 'Y'
24 then
25 /*srw.do_sql('alter session set SQL_TRACE TRUE');*/--null;
26 EXECUTE IMMEDIATE 'alter session set SQL_TRACE TRUE';
27
28 end if;
29 END LOOP;
30 CLOSE p_trace;
31 exception when others then
32 null;
33
34 end;
35
36
37 begin
38
39 select tax_unit_id,
40 attribute2,
41 to_number(attribute3),
42 to_date(attribute4,'MM/DD/YYYY'),
43 to_date(attribute5,'MM/DD/YYYY')
44 into PACTID,
45 l_param,
46 p_business_group_id,
47 p_start_date,
48 p_end_date
49 from pay_us_rpt_totals
50 where organization_id = to_number(PPA_FINDER)
51 and attribute1 = 'TIAA-CREF'
52 and rownum=1;
53
54 p_consolidation_set_id := pqp_ustiaa_pkg.get_parameter('TRANSFER_CONC_SET',l_param);
55 p_payroll_id := pqp_ustiaa_pkg.get_parameter('TRANSFER_PAYROLL',l_param);
56 p_gre_id := pqp_ustiaa_pkg.get_parameter('TRANSFER_GRE',l_param);
57
58 pqp_tiaalod_pkg.Chk_Neg_Amt(p_payroll_action_id => PACTID);
59
60 exception when others then
61
62 /*srw.message(1,'Legislative parameter not found. ERROR');*/null;
63
64 delete from pay_us_rpt_totals where tax_unit_id = pactid;
65 commit;
66 raise;
67 end;
68
69 T_START_DATE := to_char(p_start_date,'DD-MON-YYYY');
70 T_END_DATE := to_char(p_end_date,'DD-MON-YYYY');
71
72
73 return (TRUE);
74 end;
75
76 function AfterReport return boolean is
77 l_flag varchar2(5);
78 begin
79
80 select attribute19
81 into
82 l_flag
83 from pay_us_rpt_totals
84 where tax_unit_id = pactid
85 and attribute1 = 'TIAA-CREF'
86 and rownum=1;
87
88 if l_flag ='Y' then
89 delete from pay_us_rpt_totals where tax_unit_id = pactid;
90 commit;
91 else
92
93 update pay_us_rpt_totals
94 set attribute19='Y'
95 where tax_unit_id = pactid
96 and attribute1 = 'TIAA-CREF';
97
101 return (TRUE);
98 commit;
99 end if;
100 --hr_standard.event('AFTER REPORT');
102 end;
103
104 function BeforeReport return boolean is
105 begin
106
107 begin
108 --hr_standard.event('BEFORE REPORT');
109 cp_business_group_name := hr_reports.get_business_group(p_business_group_id);
110
111 if p_payroll_id is not null then
112 select distinct payroll_name,'Payroll : '||payroll_name
113 into cp_payroll_name,cp_print_set_payroll_name
114 from pay_payrolls_f
115 where payroll_id = p_payroll_id
116 and effective_start_date <= p_start_date
117 and effective_end_date >= p_end_date;
118 end if;
119
120 if p_payroll_id is null and p_consolidation_set_id is not null then
121 select consolidation_set_name,'Consolidation Set : '||consolidation_set_name
122 into cp_consolidation_set_name,cp_print_set_payroll_name
123 from pay_consolidation_sets
124 where consolidation_set_id = p_consolidation_set_id;
125 end if;
126
127 if p_gre_id is not null then
128 select name
129 into cp_gre_name
130 from hr_organization_units
131 where organization_id = p_gre_id;
132 end if;
133
134
135 exception
136 when others then
137 /*srw.message(1,'Legislative parameter not found. ERROR');*/null;
138
139 delete from pay_us_rpt_totals where tax_unit_id = pactid;
140
141 raise;
142 end;
143 return (TRUE);
144 end;
145
146 function cf_1formula(assg_exp in varchar2) return char is
147 cursor err_msg is
148 select
149 distinct attribute15 err_message
150 from pay_us_rpt_totals purt
151 where tax_unit_id =pactid
152 and attribute1<>'TIAA-CREF'
153 and attribute14 is not null
154 and attribute5 = assg_exp;
155
156 l_err_msg1 varchar2(2000);
157 l_err_msg varchar2(2000);
158
159 begin
160 OPEN err_msg;
161 LOOP
162 FETCH err_msg into l_err_msg1;
163 EXIT WHEN err_msg%NOTFOUND;
164 IF l_err_msg is NULL THEN
165 l_err_msg:=l_err_msg1;
166 else
167
168 l_err_msg :=l_err_msg||','||l_err_msg1;
169 end if;
170 END LOOP;
171 CLOSE err_msg;
172
173 return(l_err_msg);
174
175
176
177
178
179
180
181 end;
182
183 function cf_ra_gra_plan_by_instformula(RA_GRA_PLAN_BY_INST1 in number) return number is
184 begin
185 RETURN(RA_GRA_PLAN_BY_INST1*100);
186 end;
187
188 function cf_ra_gra_plan_reductformula(RA_GRA_PLAN_REDUCT1 in number) return number is
189 begin
190 RETURN(RA_GRA_PLAN_REDUCT1*100);
191 end;
192
193 function cf_ra_plan_deductformula(RA_PLAN_DEDUCT1 in number) return number is
194 begin
195 RETURN(RA_PLAN_DEDUCT1*100);
196 end;
197
198 function cf_ra_addl_reductformula(RA_ADDL_REDUCT1 in number) return number is
199 begin
200 RETURN(RA_ADDL_REDUCT1*100);
201 end;
202
203 function cf_ra_addl_deductformula(RA_ADDL_DEDUCT1 in number) return number is
204 begin
205 RETURN(RA_ADDL_DEDUCT1*100);
206 end;
207
208 function cf_sra_gsra_reductformula(SRA_GSRA_REDUCT1 in number) return number is
209 begin
210 RETURN(SRA_GSRA_REDUCT1*100);
211 end;
212
213 --Functions to refer Oracle report placeholders--
214
215 Function CP_BUSINESS_GROUP_NAME_p return varchar2 is
216 Begin
217 return CP_BUSINESS_GROUP_NAME;
218 END;
219 Function CP_PAYROLL_NAME_p return varchar2 is
220 Begin
221 return CP_PAYROLL_NAME;
222 END;
223 Function CP_GRE_NAME_p return varchar2 is
224 Begin
225 return CP_GRE_NAME;
226 END;
232 Begin
227 Function CP_CONSOLIDATION_SET_NAME_p return varchar2 is
228 Begin
229 return CP_CONSOLIDATION_SET_NAME;
230 END;
231 Function CP_PRINT_SET_PAYROLL_NAME_p return varchar2 is
233 return CP_PRINT_SET_PAYROLL_NAME;
234 END;
235 END PAY_PAYUSTIM_XMLP_PKG ;
[Home] [Help]