[Home] [Help]
PACKAGE BODY: APPS.PAY_US_ARCHIVE_UTIL
Source
1 PACKAGE BODY PAY_US_ARCHIVE_UTIL AS
2 /* $Header: payusarchiveutil.pkb 120.0 2005/05/29 11:52:17 appldev noship $ */
3
4 /*
5 +=====================================================================+
6 | Copyright (c) 1997 Orcale Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +=====================================================================+
10 Name : payusarchiveutil.pkb
11 Description : This package contains utilities to fetch archived values.
12
13 Change List
14 -----------
15
16 Version Date Author Bug No. Description of Change
17 -------+---------+---------------+---------+--------------------------
18 115.0 20-AUG-04 rsethupa 3393493 Created
19 115.1 25-AUG-04 sodhingr Changed the logic which caches
20 user_entity_id to always check
21 if the user_entity_id is NULL
22 if the user_entity_id is NULL
23 115.2 01-SEP-04 sodhingr Added the debug messages and
24 changed the data type of lv_return_value
25 to ff_archive_item.value%type to avoid
26 the pl/sql numeric error when getting
27 the archive_date
28 115.3 08-NOV-04 rsethupa 3180532 Added function get_ff_archive_value
29 115.4 10-NOV-04 meshah removed function
30 get_ff_archive_value and
31 moved it to
32 pay_us_reporting_utils_pkg
33 for extract reasons.
34 115.5 23-NOV-04 rsethupa 3180532 Changed cursor
35 c_get_value_with_jc
36 to check only for the first 2
37 characters of jurisdiction code
38 ----------------------------------------------------------------------
39 */
40
41 /*********************************************************************
42 Name : get_archive_value
43
44 Description : gets the archived value for a particular Action ID
45 (Assignment Action or Payroll Action). Jurisdiction
46 code is optional.
47
48 ********************************************************************/
49 FUNCTION get_archive_value (
50 p_action_id NUMBER,
51 p_user_entity_name VARCHAR2,
52 p_tax_unit_id NUMBER,
53 p_jurisdiction_code VARCHAR2 DEFAULT NULL
54 )
55 RETURN VARCHAR2
56 IS
57 CURSOR c_get_user_entity_name (cp_user_entity_name VARCHAR2)
58 IS
59 SELECT user_entity_id
60 FROM ff_user_entities
61 WHERE user_entity_name = cp_user_entity_name;
62
63 CURSOR c_get_value (
64 cp_user_entity_id NUMBER,
65 cp_action_id NUMBER,
66 cp_tax_unit_id NUMBER,
67 cp_tax_context_id NUMBER
68 )
69 IS
70 SELECT fai.VALUE
71 FROM ff_archive_items fai, ff_archive_item_contexts fic
72 WHERE fai.context1 = cp_action_id
73 AND fai.user_entity_id = cp_user_entity_id
74 AND fai.archive_item_id = fic.archive_item_id
75 AND fic.context_id = cp_tax_context_id
76 AND fic.CONTEXT = cp_tax_unit_id;
77
78 /* The following cursor is just like the c_state_item cursor in package
79 pyusw2pg.pkb */
80 CURSOR c_get_value_with_jc (
81 cp_user_entity_id NUMBER,
82 cp_action_id NUMBER,
83 cp_tax_unit_id NUMBER,
84 cp_jurisdiction_code VARCHAR2
85 )
86 IS
87 SELECT fai.VALUE
88 FROM ff_archive_item_contexts faic2,
89 ff_archive_item_contexts faic1,
90 ff_contexts fc2,
91 ff_contexts fc1,
92 ff_archive_items fai
93 WHERE fai.user_entity_id = cp_user_entity_id
94 AND fai.context1 = cp_action_id
95 AND fc2.context_name = 'TAX_UNIT_ID'
96 AND fc1.context_name = 'JURISDICTION_CODE'
97 AND fai.archive_item_id = faic2.archive_item_id
98 AND faic2.context_id = fc2.context_id
99 AND faic2.CONTEXT = cp_tax_unit_id
100 AND fai.archive_item_id = faic1.archive_item_id
101 AND faic1.context_id = fc1.context_id
102 AND substr(faic1.CONTEXT,1,2) = substr(cp_jurisdiction_code,1,2);
103
104 lv_tax_context_id NUMBER;
105 lv_table_count NUMBER;
106 lv_user_entity_id ff_user_entities.user_entity_id%TYPE;
107 lv_user_entity_name ff_user_entities.user_entity_name%TYPE;
108 lv_return_value ff_archive_items.value%TYPE;
109 BEGIN
110 lv_tax_context_id := -1;
111 lv_table_count := 0;
112 lv_user_entity_id := NULL;
113 lv_tax_context_id := hr_us_w2_rep.get_context_id ('TAX_UNIT_ID');
114 lv_return_value := NULL;
115
116 IF ltr_user_entity_table.COUNT > 0
117 THEN
118 hr_utility.trace('User Entity table count > 0 '||ltr_user_entity_table.COUNT);
119 FOR j IN ltr_user_entity_table.FIRST .. ltr_user_entity_table.LAST
120 LOOP
121 IF ltr_user_entity_table (j).user_entity_name =
122 p_user_entity_name
123 THEN
124 lv_user_entity_id := ltr_user_entity_table (j).user_entity_id;
125 EXIT;
126 END IF;
127 END LOOP;
128 /*Always check if the user_entity_id is NULL to get the user_entity_id if the it's not
129 cached
130 ELSIF ltr_user_entity_table.COUNT = 0 OR lv_user_entity_id = NULL
131 */
132 END IF;
133
134 hr_utility.trace('User Entity table count '|| ltr_user_entity_table.COUNT);
135 hr_utility.trace(' lv_user_entity_id '|| lv_user_entity_id);
136
137 IF ltr_user_entity_table.COUNT = 0 OR lv_user_entity_id IS NULL THEN
138 OPEN c_get_user_entity_name (p_user_entity_name);
139
140 FETCH c_get_user_entity_name
141 INTO lv_user_entity_id;
142
143 CLOSE c_get_user_entity_name;
144
145 lv_table_count := ltr_user_entity_table.COUNT;
146 ltr_user_entity_table (lv_table_count).user_entity_name :=
147 p_user_entity_name;
148 ltr_user_entity_table (lv_table_count).user_entity_id :=
149 lv_user_entity_id;
150 END IF;
151
152 hr_utility.trace(' lv_user_entity_id '|| lv_user_entity_id);
153 hr_utility.trace(' p_action_id '|| p_action_id);
154 hr_utility.trace(' p_tax_unit_id '|| p_tax_unit_id);
155 hr_utility.trace(' lv_tax_context_id '|| lv_tax_context_id);
156
157 IF p_jurisdiction_code IS NULL
158 THEN
159 hr_utility.trace(' Jurisdiction is NULL');
160 hr_utility.trace(' Jurisdiction is NULL, lv_user_entity_id '|| lv_user_entity_id);
161
162 OPEN c_get_value (lv_user_entity_id,
163 p_action_id,
164 p_tax_unit_id,
165 lv_tax_context_id
166 );
167
168 FETCH c_get_value
169 INTO lv_return_value;
170
171 hr_utility.trace(' lv_return_value '|| lv_return_value);
172
173 CLOSE c_get_value;
174 ELSE
175 hr_utility.trace(' Jurisdiction is Not NULL, p_jurisdiction_code '||p_jurisdiction_code);
176 OPEN c_get_value_with_jc (lv_user_entity_id,
177 p_action_id,
178 p_tax_unit_id,
179 p_jurisdiction_code
180 );
181
182 FETCH c_get_value_with_jc
183 INTO lv_return_value;
184
185 CLOSE c_get_value_with_jc;
186 END IF;
187
188 RETURN lv_return_value;
189 END get_archive_value;
190
191 END pay_us_archive_util;
192