[Home] [Help]
PACKAGE BODY: APPS.PAY_ZA_EFT_RECONCILIATION
Source
1 PACKAGE BODY PAY_ZA_EFT_RECONCILIATION AS
2 -- /* $Header: pyzaeftrecn.pkb 120.0.12010000.1 2009/07/28 09:29:02 dchindar noship $ */
3 --
4
5 FUNCTION get_eft_recon_data (p_effective_date DATE,
6 p_identifier_name VARCHAR2,
7 p_payroll_action_id NUMBER,
8 p_payment_type_id NUMBER,
9 p_org_payment_method_id NUMBER,
10 p_personal_payment_method_id NUMBER,
11 p_assignment_action_id NUMBER,
12 p_pre_payment_id NUMBER,
13 p_delimiter_string VARCHAR2)
14 RETURN VARCHAR2
15 IS
16
17 CURSOR c_get_trx_date
18 IS
19 Select to_char(nvl(ppa.overriding_dd_date, nvl(ptp.default_dd_date, ptp.end_date)), 'YYYY/MM/DD')
20 from pay_assignment_actions paa,
21 pay_pre_payments ppp,
22 pay_assignment_actions paa2,
23 pay_payroll_actions ppa,
24 per_time_periods ptp
25 where paa.assignment_action_id = p_assignment_action_id
26 and paa.PRE_PAYMENT_ID = ppp.PRE_PAYMENT_ID
27 and paa2.assignment_action_id = ( select max(locked_action_id)
28 from pay_action_interlocks pai
29 where pai.locking_action_id = ppp.assignment_action_id
30 )
31 and paa2.payroll_action_id = ppa.payroll_action_id
32 and ppa.time_period_id = ptp.time_period_id;
33
34
35 CURSOR get_acb_code (p_effective_date date) IS
36 select SEGMENT2
37 from pay_payrolls_f PPF,
38 PAY_PAYROLL_ACTIONS PPA,
39 hr_soft_coding_keyflex SOK
40 where PPA.payroll_action_id = p_payroll_action_id
41 and PPA.payroll_id = PPF.payroll_id
42 and PPF.SOFT_CODING_KEYFLEX_ID = SOK.SOFT_CODING_KEYFLEX_ID
43 and p_effective_date between ppf.EFFECTIVE_START_DATE and ppf.EFFECTIVE_END_DATE;
44
45
46 CURSOR org_payment_method_acc_no(p_effective_date date) IS
47 select SEGMENT3
48 from PAY_ORG_PAYMENT_METHODS_F OPM,
49 PAY_EXTERNAL_ACCOUNTS PEA
50 where OPM.ORG_PAYMENT_METHOD_ID = p_org_payment_method_id
51 and OPM.EXTERNAL_ACCOUNT_ID = PEA.EXTERNAL_ACCOUNT_ID
52 and p_effective_date between OPM.EFFECTIVE_START_DATE and OPM.EFFECTIVE_END_DATE;
53
54
55 cursor acb_effective_date IS
56 select ppa.effective_date
57 from pay_assignment_actions paa,
58 pay_pre_payments ppp,
59 pay_assignment_actions paa2,
60 pay_payroll_actions ppa
61 where paa.assignment_action_id = p_assignment_action_id
62 and paa.PRE_PAYMENT_ID = ppp.PRE_PAYMENT_ID
63 and paa2.assignment_action_id = ( select max(locked_action_id)
64 from pay_action_interlocks pai
65 where pai.locking_action_id = ppp.assignment_action_id
66 )
67 and paa2.payroll_action_id = ppa.payroll_action_id;
68
69 CURSOR inst_code_and_type IS
70 select substr(hoi.org_information1, 1, 80),
71 substr(hoi.org_information2, 1, 80)
72 from pay_payroll_actions ppa,
73 hr_organization_information hoi,
74 hr_organization_units hou
75 where ppa.payroll_action_id = p_payroll_action_id
76 and hou.organization_id = ppa.business_group_id
77 and hoi.organization_id = hou.organization_id
78 and hoi.org_information_context = 'ZA_ACB_INFORMATION';
79
80
81
82 l_usr_fnc_name VARCHAR2(100) := NULL;
83 l_business_grp_id NUMBER;
84 l_acb_code VARCHAR2(30);
85 l_gen_number VARCHAR2(30);
86 l_acc_number VARCHAR2(30);
87 l_return_value varchar2(30);
88 l_effective_date date;
89 l_inst_code VARCHAR2(30);
90 l_acb_user_type VARCHAR2(30);
91
92
93 BEGIN
94
95 SELECT BUSINESS_GROUP_ID
96 INTO l_business_grp_id
97 FROM pay_payroll_actions
98 WHERE PAYROLL_ACTION_ID = p_payroll_action_id;
99
100
101 Select hruserdt.get_table_value(l_business_grp_id,
102 'ZA_EFT_RECONC_FUNC',
103 'RECONCILIATION',
104 'FUNCTION NAME',
105 p_effective_date)
106 Into l_usr_fnc_name
107 From dual;
108
109 IF l_usr_fnc_name IS NOT NULL then
110
111 EXECUTE IMMEDIATE 'select '||l_usr_fnc_name||'(:1,:2,:3,:4,:5,:6,:7,:8,:9) from dual'
112 INTO l_return_value
113 USING p_effective_date ,
114 p_identifier_name,
115 p_payroll_action_id,
116 p_payment_type_id,
117 p_org_payment_method_id,
118 p_personal_payment_method_id,
119 p_assignment_action_id,
120 p_pre_payment_id,
121 p_delimiter_string ;
122
123 IF UPPER(p_identifier_name) = 'TRANSACTION_DATE' THEN
124 begin
125 l_return_value := to_char(to_date(l_return_value, 'YYYY/MM/DD'), 'YYYY/MM/DD');
126 EXCEPTION
127 WHEN others THEN
128 raise_application_error(-20001, 'Transaction Date must be in YYYY/MM/DD format.');
129 END;
130 END IF;
131
132
133 ELSE
134
135 IF UPPER(p_identifier_name) = 'TRANSACTION_DATE' THEN
136
137 OPEN c_get_trx_date;
138 FETCH c_get_trx_date INTO l_return_value;
139 CLOSE c_get_trx_date;
140
141 ELSIF UPPER(p_identifier_name) = 'TRANSACTION_GROUP' THEN
142
143 l_return_value := p_payroll_action_id;
144
145 ELSIF UPPER(p_identifier_name) = 'CONCATENATED_IDENTIFIERS' THEN
146
147 OPEN acb_effective_date;
148 FETCH acb_effective_date INTO l_effective_date;
149 CLOSE acb_effective_date;
150
151 OPEN get_acb_code(l_effective_date);
152 FETCH get_acb_code INTO l_acb_code;
153 CLOSE get_acb_code;
154
155 OPEN inst_code_and_type;
156 FETCH inst_code_and_type INTO l_inst_code, l_acb_user_type;
157 CLOSE inst_code_and_type;
158
159 IF l_acb_user_type = 'S' THEN
160 SELECT GEN_NUMBER INTO l_gen_number
161 FROM PAY_ZA_ACB_USER_GEN_NOS
162 WHERE PAYROLL_ACTION_ID = p_payroll_action_id
163 AND USER_CODE = l_inst_code;
164 else
165 SELECT GEN_NUMBER INTO l_gen_number
166 FROM PAY_ZA_ACB_USER_GEN_NOS
167 WHERE PAYROLL_ACTION_ID = p_payroll_action_id
168 AND USER_CODE = l_acb_code;
169 END IF;
170
171
172 OPEN org_payment_method_acc_no(l_effective_date);
173 FETCH org_payment_method_acc_no INTO l_acc_number;
174 CLOSE org_payment_method_acc_no;
175
176 l_return_value := l_gen_number||p_delimiter_string||l_acb_code||p_delimiter_string||l_acc_number||p_delimiter_string||'ACB';
177
178 END IF;
179 END IF;
180
181 RETURN l_return_value;
182 EXCEPTION
183 WHEN OTHERS THEN
184 RAISE;
185 END get_eft_recon_data;
186
187 END PAY_ZA_EFT_RECONCILIATION;
188