[Home] [Help]
PACKAGE BODY: APPS.PAY_IN_TRX_IDENTIFIERS
Source
1 PACKAGE BODY pay_in_trx_identifiers AS
2 /* $Header: pyintrx.pkb 120.1 2010/05/21 13:34:42 pmatamsr noship $ */
3
4 g_package CONSTANT VARCHAR2(100) := 'pay_in_trx_identifiers.';
5 g_debug BOOLEAN;
6
7
8 --------------------------------------------------------------------------
9 -- --
10 -- Name : GET_PARAMETERS --
11 -- Type : PROCEDURE --
12 -- Access : Public --
13 -- Description : This procedure determines the globals applicable --
14 -- through out the tenure of the process --
15 -- Parameters : --
16 -- IN : p_legislative_parameters VARCHAR2 --
17 -- IN : p_token_name VARCHAR2 --
18 -- --
19 -- --
20 -- OUT : N/A --
21 -- Change History : --
22 --------------------------------------------------------------------------
23 -- Rev# Date Userid Description --
24 --------------------------------------------------------------------------
25 -- 12.0 01-Jun-2009 rsaharay Initial Version --
26 --------------------------------------------------------------------------
27
28 FUNCTION get_parameters( p_legislative_parameters IN VARCHAR2,
29 p_token_name IN VARCHAR2 )
30 RETURN VARCHAR2
31 IS
32
33 l_token_value VARCHAR2(50);
34 l_procedure VARCHAR2(100);
35 l_message VARCHAR2(250);
36
37 BEGIN
38
39 g_debug := hr_utility.debug_enabled;
40 l_procedure := g_package || 'get_parameters';
41 pay_in_utils.set_location(g_debug,'Entering : '||l_procedure,10);
42
43
44 l_token_value := SUBSTR(p_legislative_parameters||' ',
45 INSTR(p_legislative_parameters||' ',p_token_name||'=')+(LENGTH(p_token_name||'=')),
46 INSTR(p_legislative_parameters||' ',' ',
47 INSTR(p_legislative_parameters||' ',p_token_name||'='))
48 - (INSTR(p_legislative_parameters||' ',p_token_name||'=')+LENGTH(p_token_name||'=')));
49
50 IF g_debug THEN
51 pay_in_utils.trace('p_legislative_parameters ',p_legislative_parameters);
52 pay_in_utils.trace('p_token_name ',p_token_name);
53 pay_in_utils.trace('l_token_value ',l_token_value);
54 END IF;
55
56 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
57 RETURN l_token_value ;
58 EXCEPTION
59 WHEN OTHERS THEN
60 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR',
61 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
62 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
63 RAISE ;
64 END get_parameters;
65
66
67 --------------------------------------------------------------------------
68 -- --
69 -- Name : BATCH_TRANSACTION_IDENTIFIERS --
70 -- Type : FUNCTION --
71 -- Access : Public --
72 -- Description : Function to identify the batch transaction --
73 -- identifiers --
74 -- --
75 -- Parameters : --
76 -- IN : p_effective_date DATE --
77 -- p_identifier_name VARCHAR2 --
78 -- p_payroll_action_id NUMBER --
79 -- p_payment_type_id NUMBER --
80 -- p_org_payment_method_id NUMBER --
81 -- p_personal_payment_method_id NUMBER --
82 -- p_assignment_action_id NUMBER --
83 -- p_pre_payment_id NUMBER --
84 -- p_delimiter_string VARCHAR2 --
85 -- --
86 -- OUT : N/A --
87 -- --
88 -- Change History : --
89 --------------------------------------------------------------------------
90 -- Rev# Date Userid Description --
91 --------------------------------------------------------------------------
92 -- 12.0 01-Jun-2009 rsaharay Initial Version --
93 --------------------------------------------------------------------------
94 FUNCTION batch_transaction_identifiers
95 (
96 p_effective_date DATE
97 , p_identifier_name VARCHAR2
98 , p_payroll_action_id NUMBER
99 , p_payment_type_id NUMBER
100 , p_org_payment_method_id NUMBER
101 , p_personal_payment_method_id NUMBER
102 , p_assignment_action_id NUMBER
103 , p_pre_payment_id NUMBER
104 , p_delimiter_string VARCHAR2
105 )
106 RETURN VARCHAR2
107 IS
108
109 l_emp_num per_people_f.employee_number%TYPE ;
110 l_emp_name1 per_people_f.full_name%TYPE ;
111 l_emp_name2 per_people_f.full_name%TYPE ;
112 l_emp_name3 per_people_f.full_name%TYPE ;
113 l_emp_bank hr_lookups.meaning%TYPE ;
114 l_emp_branch hr_lookups.meaning%TYPE ;
115 l_emp_acc_name hr_lookups.meaning%TYPE ;
116 l_emp_acc_num pay_external_accounts.segment1%TYPE ;
117 l_reg_comp pay_external_accounts.segment1%TYPE ;
118 l_leg_param pay_payroll_actions.legislative_parameters%TYPE ;
119 l_payment_date VARCHAR2(100);
120 l_transaction_group NUMBER(20) ;
121 l_request_id pay_payroll_actions.request_id%TYPE ;
122 l_reg_comp_name hr_organization_information.org_information4%TYPE ;
123 l_bank_acc_num pay_external_accounts.segment1%TYPE ;
124 l_procedure VARCHAR2(100);
125 l_message VARCHAR2(250);
126
127
128
129 CURSOR csr_emp_tran_details( l_payroll_action_id NUMBER
130 ,l_org_payment_method_id NUMBER
131 ,l_assignment_action_id NUMBER )
132 IS
133 SELECT per.employee_number emp_num
134 , SUBSTR(per.full_name,1,80) emp_name1
135 , SUBSTR(per.full_name,81,80) emp_name2
136 , SUBSTR(per.full_name,161,80) emp_name3
137 , hr_general.decode_lookup('IN_BANK',pea.segment3) emp_bank
138 , hr_general.decode_lookup('IN_BANK_BRANCH',pea.segment4) emp_branch
139 , hr_general.decode_lookup('IN_ACCOUNT_TYPE',pea.segment2) emp_acc_name
140 , pea.segment1 emp_acc_num
141 , ppa.legislative_parameters leg_param
142 , SUBSTR(lpad(paa.action_sequence,15,'0'),10) transaction_group
143 , ppa.request_id request_id
144 ,oea.segment1 bank_acc_num
145 FROM pay_org_payment_methods_f popm
146 , pay_external_accounts oea
147 , pay_personal_payment_methods_f pppm
148 , pay_external_accounts pea
149 , pay_pre_payments ppp
150 , pay_assignment_actions paa
151 , pay_payroll_actions ppa
152 , per_assignments_f asg
153 , per_people_f per
154 , hr_organization_units org
155 , pay_payment_types ppto
156 WHERE ppa.payroll_action_id = l_payroll_action_id
157 AND paa.assignment_action_id = l_assignment_action_id
158 AND ppp.pre_payment_id = paa.pre_payment_id
159 AND paa.payroll_action_id = ppa.payroll_action_id
160 AND ppa.business_group_id = popm.business_group_id
161 AND oea.external_account_id = popm.external_account_id
162 AND ppa.business_group_id = org.organization_id
163 AND popm.org_payment_method_id = ppp.org_payment_method_id
164 AND pea.external_account_id = pppm.external_account_id
165 AND pppm.personal_payment_method_id = ppp.personal_payment_method_id
166 AND paa.assignment_id = asg.assignment_id
167 AND asg.person_id = per.person_id
168 AND ppa.effective_date BETWEEN popm.effective_start_date AND popm.effective_end_date
169 AND ppa.effective_date BETWEEN pppm.effective_start_date AND pppm.effective_end_date
170 AND ppa.effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
171 AND ppa.effective_date BETWEEN per.effective_start_date AND per.effective_end_date
172 AND ppto.category = 'MT'
173 AND popm.payment_type_id = ppto.payment_type_id
174 AND popm.org_payment_method_id = l_org_payment_method_id;
175
176 CURSOR csr_reg_comp (l_reg_company NUMBER)
177 IS
178 SELECT hoi.org_information4 company_name
179 FROM hr_organization_information hoi
180 WHERE hoi.organization_id = l_reg_company
181 AND hoi.org_information_context = 'PER_IN_COMPANY_DF' ;
182
183
184 BEGIN
185
186 g_debug := hr_utility.debug_enabled;
187 l_procedure := g_package || 'batch_transaction_identifiers';
188 pay_in_utils.set_location(g_debug,'Entering : '||l_procedure,10);
189
190 IF g_debug THEN
191 pay_in_utils.trace('p_effective_date ',p_effective_date);
192 pay_in_utils.trace('p_identifier_name ',p_identifier_name);
193 pay_in_utils.trace('p_payroll_action_id ',p_payroll_action_id);
194 pay_in_utils.trace('p_payment_type_id ',p_payment_type_id);
195 pay_in_utils.trace('p_org_payment_method_id ',p_org_payment_method_id);
196 pay_in_utils.trace('p_personal_payment_method_id',p_personal_payment_method_id);
197 pay_in_utils.trace('p_assignment_action_id ',p_assignment_action_id);
198 pay_in_utils.trace('p_pre_payment_id ',p_pre_payment_id);
199 pay_in_utils.trace('p_delimiter_string ',p_delimiter_string);
200 END IF;
201
202
203 OPEN csr_emp_tran_details(p_payroll_action_id, p_org_payment_method_id, p_assignment_action_id);
204 FETCH csr_emp_tran_details INTO l_emp_num
205 ,l_emp_name1
206 ,l_emp_name2
207 ,l_emp_name3
208 ,l_emp_bank
209 ,l_emp_branch
210 ,l_emp_acc_name
211 ,l_emp_acc_num
212 ,l_leg_param
213 ,l_transaction_group
214 ,l_request_id
215 ,l_bank_acc_num;
216 CLOSE csr_emp_tran_details ;
217
218 pay_in_utils.set_location(g_debug,'In : '||l_procedure,20);
219
220 l_payment_date := get_parameters(l_leg_param,'PAYMENT_DATE_PARAM') ;
221 l_reg_comp := get_parameters(l_leg_param,'REGISTERED_EMPLOYER_PARAM') ;
222
223 OPEN csr_reg_comp(l_reg_comp);
224 FETCH csr_reg_comp INTO l_reg_comp_name;
225 CLOSE csr_reg_comp;
226
227 pay_in_utils.set_location(g_debug,'In : '||l_procedure,30);
228
229 IF g_debug THEN
230 pay_in_utils.trace('l_emp_num ',l_emp_num);
231 pay_in_utils.trace('l_emp_name1 ',l_emp_name1);
232 pay_in_utils.trace('l_emp_name2 ',l_emp_name2);
233 pay_in_utils.trace('l_emp_name3 ',l_emp_name3);
234 pay_in_utils.trace('l_emp_bank ',l_emp_bank);
235 pay_in_utils.trace('l_emp_branch ',l_emp_branch);
236 pay_in_utils.trace('l_emp_acc_name ',l_emp_acc_name);
237 pay_in_utils.trace('l_emp_acc_num ',l_emp_acc_num);
238 pay_in_utils.trace('l_leg_param ',l_leg_param);
239 pay_in_utils.trace('l_transaction_group',l_transaction_group);
240 pay_in_utils.trace('l_request_id ',l_request_id);
241 pay_in_utils.trace('l_bank_acc_num ',l_bank_acc_num);
242 pay_in_utils.trace('l_payment_date ',l_payment_date);
243 pay_in_utils.trace('l_reg_comp ',l_reg_comp);
244 pay_in_utils.trace('l_reg_comp_name ',l_reg_comp_name);
245 END IF;
246
247
248
249 pay_in_utils.set_location(g_debug,'In : '||l_procedure,40);
250
251 IF p_identifier_name = 'CONCATENATED_IDENTIFIERS' THEN
252 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,50);
253 RETURN l_reg_comp_name||','||l_bank_acc_num||','||l_payment_date;
254 ELSIF p_identifier_name = 'PAYEE_BANK_ACCOUNT_NAME' THEN
255 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,60);
256 RETURN l_emp_acc_name ;
257 ELSIF p_identifier_name = 'PAYEE_BANK_ACCOUNT_NUMBER' THEN
258 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,70);
259 RETURN l_emp_acc_num;
260 ELSIF p_identifier_name = 'PAYEE_BANK_BRANCH' THEN
261 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,80);
262 RETURN l_emp_branch;
263 ELSIF p_identifier_name = 'PAYEE_BANK_NAME' THEN
264 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,90);
265 RETURN l_emp_bank;
266 ELSIF p_identifier_name = 'TRANSACTION_DATE' THEN
267 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,100);
268 RETURN TO_CHAR(TO_DATE(l_payment_date),'yyyy/mon/dd');
269 ELSIF p_identifier_name = 'TRANSACTION_GROUP' THEN
270 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure,110);
271 RETURN p_payroll_action_id ;
272 END IF ;
273
274
275
276 EXCEPTION
277 WHEN OTHERS THEN
278 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR',
279 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
280 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 120);
281 RAISE ;
282 END batch_transaction_identifiers ;
283
284 END pay_in_trx_identifiers ;