1 PACKAGE BODY pay_us_payment_pkg AS
2 /* $Header: pyuspymt.pkb 120.0 2005/05/29 09:52:43 appldev noship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 2005 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_us_payment_pkg
21
22 Description : This package contains the function get_trx_date to
23 fetch the payment date.
24
25 Change List
26 -----------
27 Date Name Vers Bug No Description
28 ----------- ---------- ------ ------- -------------------------
29 27-APR-2005 rsethupa 115.0 Created
30
31 *****************************************************************
32
33 ****************************************************************************
34 Function Name: get_trx_date
35 Description: Returns the Payment Date for a Check Payroll Action
36 ***************************************************************************/
37 FUNCTION get_trx_date (
38 p_business_group_id pay_payroll_actions.business_group_id%TYPE,
39 p_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE,
40 p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE
41 DEFAULT NULL,
42 p_payroll_id pay_payroll_actions.payroll_id%TYPE
43 DEFAULT NULL,
44 p_consolidation_set_id pay_payroll_actions.consolidation_set_id%TYPE
45 DEFAULT NULL,
46 p_org_payment_method_id pay_payroll_actions.org_payment_method_id%TYPE
47 DEFAULT NULL,
48 p_effective_date pay_payroll_actions.effective_date%TYPE
49 DEFAULT NULL,
50 p_date_earned pay_payroll_actions.date_earned%TYPE
51 DEFAULT NULL,
52 p_override_date pay_payroll_actions.overriding_dd_date%TYPE
53 DEFAULT NULL,
54 p_pre_payment_id pay_pre_payments.pre_payment_id%TYPE
55 DEFAULT NULL,
56 p_payment_date pay_payroll_actions.effective_date%TYPE
57 DEFAULT NULL
58 )
59 RETURN DATE
60 IS
61 /**************************************************************************
62 Cursor Name: c_get_override_date
63 Description: Fetches the value of the overriding_dd_date column for the
64 the Check Writer payroll Action. More number of parameters
65 are passed as input to improve performance
66 *************************************************************************/
67 CURSOR c_get_override_date (
68 cp_business_group_id IN NUMBER,
69 cp_payroll_action_id IN NUMBER,
70 cp_payroll_id IN NUMBER,
71 cp_consolidation_set_id IN NUMBER,
72 cp_org_payment_method_id IN NUMBER,
73 cp_effective_date IN DATE,
74 cp_date_earned IN DATE
75 )
76 IS
77 SELECT overriding_dd_date
78 FROM pay_payroll_actions
79 WHERE business_group_id = cp_business_group_id
80 AND payroll_action_id = cp_payroll_action_id
81 AND payroll_id = NVL (cp_payroll_id, payroll_id)
82 AND consolidation_set_id =
83 NVL (cp_consolidation_set_id, consolidation_set_id)
84 AND org_payment_method_id =
85 NVL (cp_org_payment_method_id, org_payment_method_id)
86 AND effective_date = NVL (cp_effective_date, effective_date)
87 AND date_earned = NVL (cp_date_earned, date_earned);
88
89 /**************************************************************************
90 Cursor Name: c_get_pymt_effective_date
91 Description: Takes as INPUT the payroll_action_id of Check Writer and
92 other optional parameters. By checking for ASSACT interlocks
93 it fetches the effective_date of the prepayment action
94 locked by the Check Writer action
95 *************************************************************************/
96 CURSOR c_get_pymt_effective_date (
97 cp_business_group_id IN NUMBER,
98 cp_payroll_action_id IN NUMBER,
99 cp_assignment_action_id IN NUMBER,
100 cp_payroll_id IN NUMBER,
101 cp_consolidation_set_id IN NUMBER,
102 cp_org_payment_method_id IN NUMBER,
103 cp_effective_date IN DATE,
104 cp_date_earned IN DATE,
105 cp_pre_payment_id IN NUMBER
106 )
107 IS
108 SELECT NVL (ppa_chk.overriding_dd_date, ppa_pre.effective_date)
109 FROM pay_payroll_actions ppa_chk,
110 pay_assignment_actions paa_chk,
111 pay_payroll_actions ppa_pre,
112 pay_assignment_actions paa_pre,
113 pay_action_interlocks pai,
114 pay_pre_payments ppp
115 WHERE ppa_chk.payroll_action_id = cp_payroll_action_id
116 AND ppa_chk.business_group_id = cp_business_group_id
117 AND ppa_chk.payroll_id = NVL (cp_payroll_id, ppa_chk.payroll_id)
118 AND ppa_chk.consolidation_set_id =
119 NVL (cp_consolidation_set_id, ppa_chk.consolidation_set_id)
120 AND ppa_chk.org_payment_method_id =
121 NVL (cp_org_payment_method_id, ppa_chk.org_payment_method_id)
122 AND ppa_chk.effective_date =
123 NVL (cp_effective_date, ppa_chk.effective_date)
124 AND ppa_chk.date_earned = NVL (cp_date_earned, ppa_chk.date_earned)
125 AND ppa_chk.action_type = 'H'
126 AND ppa_chk.payroll_action_id = paa_chk.payroll_action_id
127 AND paa_chk.action_status = 'C'
128 AND paa_chk.assignment_action_id = pai.locking_action_id
129 AND pai.locked_action_id = paa_pre.assignment_action_id
130 AND paa_pre.payroll_action_id = ppa_pre.payroll_action_id
131 AND paa_pre.action_status = 'C'
132 AND ppa_pre.action_type IN ('P', 'U')
133 AND paa_pre.assignment_action_id = ppp.assignment_action_id
134 AND ppp.pre_payment_id = NVL (cp_pre_payment_id, ppp.pre_payment_id)
135 AND ppp.pre_payment_id = paa_chk.pre_payment_id
136 AND ppp.org_payment_method_id = ppa_chk.org_payment_method_id;
137
138 /**************************************************************************
139 Cursor Name: c_get_pymt_date_with_prepay_id
140 Description: Takes as INPUT the pre_payment_id and fetches effective_date
141 of the prepayment action. This cursor is opened only if the
142 the overriding_dd_date column for the Check Writer action is
143 NULL and a pre_payment_id is passed
144 *************************************************************************/
145 CURSOR c_get_pymt_date_with_prepay_id (cp_pre_payment_id IN NUMBER)
146 IS
147 SELECT ppa.effective_date
148 FROM pay_pre_payments ppp,
149 pay_assignment_actions paa,
150 pay_payroll_actions ppa
151 WHERE ppp.pre_payment_id = cp_pre_payment_id
152 AND ppp.assignment_action_id = paa.assignment_action_id
153 AND paa.payroll_action_id = ppa.payroll_action_id
154 AND ppa.action_type IN ('P', 'U');
155
156 l_override_date pay_payroll_actions.overriding_dd_date%TYPE;
157 l_table_count NUMBER;
158 BEGIN
159 -- hr_utility.trace_on (NULL, 'PYMT');
160 hr_utility.set_location ('pay_us_payment_pkg.get_trx_date', 10);
161 hr_utility.TRACE ('p_business_group_id: ' || p_business_group_id);
162 hr_utility.TRACE ('p_payroll_action_id: ' || p_payroll_action_id);
163 hr_utility.TRACE ('p_assignment_action_id: ' || p_assignment_action_id);
164 hr_utility.TRACE ('p_payroll_id: ' || p_payroll_id);
165 hr_utility.TRACE ('p_consolidation_set_id: ' || p_consolidation_set_id);
166 hr_utility.TRACE ('p_org_payment_method_id: ' || p_org_payment_method_id);
167 hr_utility.TRACE ('p_effective_date: ' || p_effective_date);
168 hr_utility.TRACE ('p_date_earned: ' || p_date_earned);
169 hr_utility.TRACE ('p_override_date: ' || p_override_date);
170 hr_utility.TRACE ('p_pre_payment_id: ' || p_pre_payment_id);
171 hr_utility.TRACE ('p_payment_date: ' || p_payment_date);
172
173 IF p_override_date IS NOT NULL
174 THEN
175 l_override_date := p_override_date;
176 ELSE
177 /* Check if the override_date is already cached for a payroll_Action_id */
178 IF ltr_override_date_table.COUNT > 0
179 THEN
180 FOR j IN
181 ltr_override_date_table.FIRST .. ltr_override_date_table.LAST
182 LOOP
183 IF ltr_override_date_table (j).payroll_action_id =
184 p_payroll_action_id
185 THEN
186 l_override_date := ltr_override_date_table (j).override_date;
187 hr_utility.set_location ('pay_us_payment_pkg.get_trx_date',
188 20
189 );
190 EXIT;
191 END IF;
192 END LOOP;
193 ELSIF ltr_override_date_table.COUNT = 0
194 THEN
195 /* Check if OVERRIDING_DD_DATE has a value for the Check Writer Action */
196 OPEN c_get_override_date (p_business_group_id,
197 p_payroll_action_id,
198 p_payroll_id,
199 p_consolidation_set_id,
200 p_org_payment_method_id,
201 p_effective_date,
202 p_date_earned
203 );
204
205 FETCH c_get_override_date
206 INTO l_override_date;
207
208 CLOSE c_get_override_date;
209
210 hr_utility.set_location ('pay_us_payment_pkg.get_trx_date', 30);
211 l_table_count := ltr_override_date_table.COUNT;
212 ltr_override_date_table (l_table_count).payroll_action_id :=
213 p_payroll_action_id;
214 ltr_override_date_table (l_table_count).override_date :=
215 l_override_date;
216 END IF; -- check for caching
217
218 IF l_override_date IS NULL
219 THEN
220 IF p_payment_date IS NOT NULL
221 THEN
222 l_override_date := p_payment_date;
223 hr_utility.set_location ('pay_us_payment_pkg.get_trx_date', 40);
224 ELSIF p_pre_payment_id IS NOT NULL
225 THEN
226 /* Just fetch prepayment action's effective_date with pre_payment_id.
227 This improves performance by not checking for interlocks */
228 OPEN c_get_pymt_date_with_prepay_id (p_pre_payment_id);
229
230 FETCH c_get_pymt_date_with_prepay_id
231 INTO l_override_date;
232
233 CLOSE c_get_pymt_date_with_prepay_id;
234
235 hr_utility.set_location ('pay_us_payment_pkg.get_trx_date', 50);
236 ELSE
237 /* With the help of interlocks, get prepayment effective_date */
238 OPEN c_get_pymt_effective_date (p_business_group_id,
239 p_payroll_action_id,
240 p_assignment_action_id,
241 p_payroll_id,
242 p_consolidation_set_id,
243 p_org_payment_method_id,
244 p_effective_date,
245 p_date_earned,
246 p_pre_payment_id
247 );
248
249 FETCH c_get_pymt_effective_date
250 INTO l_override_date;
251
252 CLOSE c_get_pymt_effective_date;
253
254 hr_utility.set_location ('pay_us_payment_pkg.get_trx_date', 60);
255 END IF; -- if p_payment_date is not null
256 END IF; -- if l_override_date is null
257 END IF;
258
259 -- if p_override_date is not null
260 RETURN l_override_date;
261 END;
262 END pay_us_payment_pkg;
[Home] [Help]