[Home] [Help]
PACKAGE BODY: APPS.PAY_PAYUSNAC_XMLP_PKG
Source
1 PACKAGE BODY PAY_PAYUSNAC_XMLP_PKG AS
2 /* $Header: PAYUSNACB.pls 120.0 2007/12/28 06:46:42 srikrish noship $ */
3
4 function BeforeReport return boolean is
5
6 l_p_value varchar2(10);
7 cid number;
8
9 begin
10
11 begin
12
13 select parameter_value into l_p_value
14 from pay_action_parameters
15 where parameter_name = 'TRACE';
16
17 exception when others then
18
19 l_p_value := 'N';
20
21 end;
22
23 if l_p_value = 'Y' then
24
25 /*srw.do_sql('alter session set SQL_TRACE TRUE');*/null;
26
27
28 end if;
29 select consolidation_set_id into cid
30 from pay_payroll_actions
31 where payroll_action_id = p_payroll_action_id;
32 c_consolidation_set_id := cid;
33
34 begin
35
36 --hr_standard.event('BEFORE REPORT');
37 null; c_business_group_name :=
38 hr_reports.get_business_group(p_business_group_id);
39 /*srw.message('1','Business Group ID
40 '||c_business_group_name);*/null;
41
42 /*srw.message('2','Consolidation Set Id '||to_char(c_consolidation_set_id));*/null;
43
44 /*srw.message('3','Payroll Action ID '||to_char(p_payroll_action_id));*/null;
45
46 end; return (TRUE);
47 end;
48
49 function get_address(loc_id in number) return varchar2 is
50 f_address varchar2(300) := null;
51
52 address_line_1 hr_locations_all.address_line_1%TYPE;
53 address_line_2 hr_locations_all.address_line_2%TYPE;
54 address_line_3 hr_locations_all.address_line_3%TYPE;
55 location_code hr_locations_all.location_code%type;
56 town_or_city hr_locations_all.town_or_city%TYPE;
57 postal_code hr_locations_all.postal_code%TYPE;
58 region_2 hr_locations_all.region_2%type;
59
60 cursor get_loc_rec is
61 select address_line_1,
62 address_line_2,
63 address_line_3,
64 town_or_city,
65 region_2,
66 postal_code,
67 location_code
68 from hr_locations_all
69 where location_id = loc_id;
70 begin
71 open get_loc_rec;
72 fetch get_loc_rec into address_line_1,
73 address_line_2,
74 address_line_3,
75 town_or_city,
76 region_2,
77 postal_code,
78 location_code;
79 if get_loc_rec%found
80 then
81 if address_line_1 is not null
82 then
83 f_address := rpad(address_line_1,45,' ');
84 end if;
85 if address_line_2 is not null
86 then
87 f_address := f_address ||
88 rpad(address_line_2,45,' ');
89 end if;
90 if address_line_3 is not null
91 then
92 f_address := f_address ||
93 rpad(address_line_3,45,' ');
94 end if;
95 f_address := f_address ||location_code ||' ';
96 if region_2 is not null
97 then
98 f_address := f_address ||region_2 ||' ' ||
99 postal_code;
100 end if;
101 close get_loc_rec;
102 return f_address;
103 end if;
104 end;
105
106 function calc_pnot(amount in number) return number is
107 f_prenot number;
108 begin
109 if amount = 0
110 then
111 f_prenot := 1;
112 else
113 f_prenot := 0;
114 end if;
115 return f_prenot;
116 end;
117
118 function get_cid return number is
119 cid number;
120 begin
121 select consolidation_set_id into cid
122 from pay_payroll_actions
123 where payroll_action_id = p_payroll_action_id;
124 c_consolidation_set_id := cid;
125 return cid;
126 end;
127
128 function get_pay_act_name
129 return varchar2 is
130 ret varchar2(150);
131 begin
132 select distinct to_char(a.payroll_action_id) ||
133 '-'||
134 to_char(effective_date,'DD-MON-YYYY')||
135 '-'||
136 b.CONSOLIDATION_SET_NAME ||
137 decode(c.PAYROLL_NAME,null,null,'-') ||
138 c.PAYROLL_NAME
139 into ret
140 from pay_payroll_actions a,
141 pay_consolidation_sets b,
142 pay_payrolls_f c
143 where a.CONSOLIDATION_SET_ID = b.CONSOLIDATION_SET_ID
144 and a.PAYROLL_ID = c.PAYROLL_ID (+)
145 and a.ACTION_TYPE = 'M'
146 and a.ACTION_STATUS = 'C'
147 and a.payroll_action_id = p_payroll_action_id;
148 c_payroll_action_name := ret;
149 return ret;
150 end;
151
152 function cf_bal_nachaformula(c_tot_amt in number) return number is
153 v_flag PAY_ORG_PAYMENT_METHODS_F.pmeth_information6%TYPE;
154 v_return NUMBER(12,2) := 0.00;
155 begin
156 begin
157 SELECT nvl(p1.pmeth_information6,'Y')
158 INTO v_flag
159 FROM pay_org_payment_methods_f p1,
160 pay_payroll_actions p2
161 WHERE p1.business_group_id = P_BUSINESS_GROUP_ID
162 AND p2.payroll_action_id = P_PAYROLL_ACTION_ID
163 AND p1.business_group_id = p2.business_group_id
164 AND p1.org_payment_method_id = p2.org_payment_method_id
165 AND p1.payment_type_id = p2.payment_type_id;
166 exception
167 WHEN others THEN
168 v_flag := 'Y';
169 end;
170 IF v_flag = 'Y' THEN
171 v_return := c_tot_amt;
172 ELSE
173 v_return := 0.00;
174 END IF;
175 RETURN v_return;
176 end;
177
178 function AfterReport return boolean is
179 begin
180 --hr_standard.event('AFTER REPORT');
181 return (TRUE);
182 end;
183
184 --Functions to refer Oracle report placeholders--
185
186 Function C_BUSINESS_GROUP_NAME_p return varchar2 is
187 Begin
188 return C_BUSINESS_GROUP_NAME;
189 END;
190 Function C_PAYROLL_ACTION_NAME_p return varchar2 is
191 Begin
192 return C_PAYROLL_ACTION_NAME;
193 END;
194 Function C_CONSOLIDATION_SET_ID_p return number is
195 Begin
196 return C_CONSOLIDATION_SET_ID;
197 END;
198 END PAY_PAYUSNAC_XMLP_PKG ;