eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE: APPS.HXC_GENERIC_RETRIEVAL_PKG

Source


1 Package hxc_generic_retrieval_pkg AUTHID CURRENT_USER as
2 /* $Header: hxcgnret.pkh 120.8 2010/05/26 08:04:58 asrajago ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- |                     Private Global Definitions                           |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package  varchar2(33)	:= '  hxc_generic_retrieval_pkg.';  -- Global package name
9 
10 
11 -- global PL/SQL records and tables
12 
13 TYPE r_ret_criteria is RECORD (
14       location_id         per_all_assignments_f.location_id%TYPE,
15       payroll_id          per_all_assignments_f.payroll_id%TYPE,
16       organization_id     per_all_assignments_f.organization_id%TYPE,
17       gre_id              hr_soft_coding_keyflex.segment1%TYPE
18 );
19 
20 g_ret_criteria hxc_generic_retrieval_pkg.r_ret_criteria;
21 
22 
23 --Elp changes sonarasi 14-Mar-2003
24 TYPE r_app_set_id_string is RECORD (
25 app_set_id_string VARCHAR2(200)
26 );
27 
28 TYPE t_app_set_id_string IS TABLE OF r_app_set_id_string INDEX BY BINARY_INTEGER;
29 
30 g_app_set_id_string t_app_set_id_string;
31 --Elp changes sonarasi over
32 
33 TYPE r_field_mappings IS RECORD (
34 	bld_blk_info_type_id	hxc_bld_blk_info_types.bld_blk_info_type_id%TYPE
35 ,	field_name		hxc_mapping_components.field_name%TYPE
36 ,	attribute		hxc_mapping_components.segment%TYPE
37 ,	context			hxc_bld_blk_info_types.bld_blk_info_type%TYPE
38 ,	category		hxc_bld_blk_info_type_usages.building_block_category%TYPE );
39 --
40 TYPE t_field_mappings IS TABLE OF r_field_mappings INDEX BY BINARY_INTEGER;
41 
42 g_field_mappings_table t_field_mappings;
43 
44 -- building blocks
45 
46 TYPE r_building_blocks IS RECORD (
47 	bb_id			hxc_time_building_blocks.time_building_block_id%TYPE
48 ,	type			hxc_time_building_blocks.type%TYPE
49 ,	measure			hxc_time_building_blocks.measure%TYPE
50 ,	start_time		hxc_time_building_blocks.start_time%TYPE
51 ,	stop_time		hxc_time_building_blocks.stop_time%TYPE
52 ,	parent_bb_id 		hxc_time_building_blocks.parent_building_block_id%TYPE
53 ,	scope			hxc_time_building_blocks.scope%TYPE
54 ,	resource_id		hxc_time_building_blocks.resource_id%TYPE
55 ,	resource_type		hxc_time_building_blocks.resource_type%TYPE
56 ,	comment_text		hxc_time_building_blocks.comment_text%TYPE
57 ,	uom			hxc_time_building_blocks.unit_of_measure%TYPE
58 ,	ovn			hxc_time_building_blocks.object_version_number%TYPE
59 ,	changed			VARCHAR2(1)
60 ,	deleted			VARCHAR2(1)
61 ,	timecard_bb_id		hxc_time_building_blocks.time_building_block_id%TYPE
62 ,	timecard_ovn		hxc_time_building_blocks.object_version_number%TYPE );
63 
64 TYPE t_building_blocks IS TABLE OF r_building_blocks INDEX BY BINARY_INTEGER;
65 
66 t_day_bld_blks		t_building_blocks;
67 t_detail_bld_blks	t_building_blocks;
68 t_old_detail_bld_blks 	t_building_blocks;
69 t_old_day_bld_blks 	t_building_blocks;
70 
71 -- Bug 9494444
72 -- New type and variables for Recipient processing.
73 TYPE r_recipient_lines IS RECORD (
74 bb_id       NUMBER,
75 ovn         NUMBER,
76 rec_id      NUMBER,
77 batch_id    NUMBER);
78 
79 TYPE t_recipient_lines IS TABLE OF r_recipient_lines INDEX BY BINARY_INTEGER;
80 
81 t_detail_rec_lines t_recipient_lines;
82 t_old_detail_rec_lines t_recipient_lines;
83 
84 TYPE r_timecard_block IS RECORD (
85 	start_time		hxc_time_building_blocks.start_time%TYPE
86 ,	stop_time		hxc_time_building_blocks.stop_time%TYPE
87 ,	comment_text		hxc_time_building_blocks.comment_text%TYPE );
88 
89 TYPE t_timecard_blocks IS TABLE OF r_building_blocks INDEX BY BINARY_INTEGER;
90 
91 t_time_bld_blks t_timecard_blocks;
92 
93 -- attributes
94 
95 TYPE r_time_attributes IS RECORD (
96 	bb_id			hxc_time_building_blocks.time_building_block_id%TYPE
97 ,	field_name		hxc_mapping_components.field_name%TYPE
98 ,	value			hxc_time_attributes.attribute1%TYPE
99 ,	context			hxc_bld_blk_info_types.bld_blk_info_type%TYPE
100 ,	category		hxc_bld_blk_info_type_usages.building_block_category%TYPE );
101 
102 TYPE t_time_attribute IS TABLE OF r_time_attributes INDEX BY BINARY_INTEGER;
103 
104 t_attributes		t_time_attribute;
105 t_detail_attributes	t_time_attribute;
106 t_old_detail_attributes	t_time_attribute;
107 
108 TYPE r_all_building_blocks IS RECORD (
109 	time_bb_id			hxc_time_building_blocks.time_building_block_id%TYPE
110 ,	time_ovn			hxc_time_building_blocks.object_version_number%TYPE
111 ,	time_max_ovn			hxc_time_building_blocks.object_version_number%TYPE
112 ,	time_start_time			hxc_time_building_blocks.start_time%TYPE
113 ,	time_stop_time			hxc_time_building_blocks.stop_time%TYPE
114 ,	time_comment_text		hxc_time_building_blocks.comment_text%TYPE
115 ,	time_deleted                    hxc_time_building_blocks.comment_text%TYPE
116 ,	day_bb_id			hxc_time_building_blocks.time_building_block_id%TYPE
117 ,	day_start_time			hxc_time_building_blocks.start_time%TYPE
118 ,	day_stop_time			hxc_time_building_blocks.stop_time%TYPE
119 ,	day_ovn				hxc_time_building_blocks.object_version_number%TYPE
120 ,	day_max_ovn			hxc_time_building_blocks.object_version_number%TYPE
121 ,	detail_bb_id			hxc_time_building_blocks.time_building_block_id%TYPE
122 ,	detail_type			hxc_time_building_blocks.type%TYPE
123 ,	detail_measure			hxc_time_building_blocks.measure%TYPE
124 ,	detail_start_time		hxc_time_building_blocks.start_time%TYPE
125 ,	detail_stop_time		hxc_time_building_blocks.stop_time%TYPE
126 ,	detail_parent_bb_id 		hxc_time_building_blocks.parent_building_block_id%TYPE
127 ,	detail_scope			hxc_time_building_blocks.scope%TYPE
128 ,	detail_resource_id		hxc_time_building_blocks.resource_id%TYPE
129 ,	detail_resource_type		hxc_time_building_blocks.resource_type%TYPE
130 ,	detail_comment_text		hxc_time_building_blocks.comment_text%TYPE
131 ,	detail_ovn			hxc_time_building_blocks.object_version_number%TYPE
132 ,	detail_max_ovn			hxc_time_building_blocks.object_version_number%TYPE
133 ,	detail_deleted			VARCHAR2(1)
134 ,	detail_uom			hxc_time_building_blocks.unit_of_measure%TYPE
135 ,	detail_date_from		hxc_time_building_blocks.date_from%TYPE
136 ,	detail_date_to			hxc_time_building_blocks.date_to%TYPE
137 ,	detail_approval_status		hxc_time_building_blocks.approval_status%TYPE
138 ,	detail_approval_style_id	hxc_time_building_blocks.approval_style_id%TYPE
139 ,	detail_ta_id			hxc_time_attributes.time_attribute_id%TYPE
140 ,	detail_bld_blk_info_type_id	hxc_time_attributes.bld_blk_info_type_id%TYPE
141 ,	detail_attribute1			hxc_time_attributes.attribute1%TYPE
142 ,	detail_attribute2			hxc_time_attributes.attribute1%TYPE
143 ,	detail_attribute3			hxc_time_attributes.attribute1%TYPE
144 ,	detail_attribute4			hxc_time_attributes.attribute1%TYPE
145 ,	detail_attribute5			hxc_time_attributes.attribute1%TYPE
146 ,	detail_attribute6			hxc_time_attributes.attribute1%TYPE
147 ,	detail_attribute7			hxc_time_attributes.attribute1%TYPE
148 ,	detail_attribute8			hxc_time_attributes.attribute1%TYPE
149 ,	detail_attribute9			hxc_time_attributes.attribute1%TYPE
150 ,	detail_attribute10			hxc_time_attributes.attribute1%TYPE
151 ,	detail_attribute11			hxc_time_attributes.attribute1%TYPE
152 ,	detail_attribute12			hxc_time_attributes.attribute1%TYPE
153 ,	detail_attribute13			hxc_time_attributes.attribute1%TYPE
154 ,	detail_attribute14			hxc_time_attributes.attribute1%TYPE
155 ,	detail_attribute15			hxc_time_attributes.attribute1%TYPE
156 ,	detail_attribute16			hxc_time_attributes.attribute1%TYPE
157 ,	detail_attribute17			hxc_time_attributes.attribute1%TYPE
158 ,	detail_attribute18			hxc_time_attributes.attribute1%TYPE
159 ,	detail_attribute19			hxc_time_attributes.attribute1%TYPE
160 ,	detail_attribute20			hxc_time_attributes.attribute1%TYPE
161 ,	detail_attribute21			hxc_time_attributes.attribute1%TYPE
162 ,	detail_attribute22			hxc_time_attributes.attribute1%TYPE
163 ,	detail_attribute23			hxc_time_attributes.attribute1%TYPE
164 ,	detail_attribute24			hxc_time_attributes.attribute1%TYPE
165 ,	detail_attribute25			hxc_time_attributes.attribute1%TYPE
166 ,	detail_attribute26			hxc_time_attributes.attribute1%TYPE
167 ,	detail_attribute27			hxc_time_attributes.attribute1%TYPE
168 ,	detail_attribute28			hxc_time_attributes.attribute1%TYPE
169 ,	detail_attribute29			hxc_time_attributes.attribute1%TYPE
170 ,	detail_attribute30			hxc_time_attributes.attribute1%TYPE
171 ,	detail_attribute_category		hxc_time_attributes.attribute_category%TYPE );
172 
173 TYPE t_all_building_blocks IS TABLE OF r_all_building_blocks INDEX BY BINARY_INTEGER;
174 
175 t_bb	t_all_building_blocks;
176 
177 -- Transaction detail PL/SQL arrays
178 
179 TYPE t_time_building_block_id	IS TABLE OF hxc_transaction_details.time_building_block_id%TYPE INDEX BY BINARY_INTEGER;
180 TYPE t_time_building_block_ovn IS TABLE OF hxc_transaction_details.time_building_block_ovn%TYPE INDEX BY BINARY_INTEGER;
181 TYPE t_transaction_id		IS TABLE OF hxc_transaction_details.transaction_id%TYPE INDEX BY BINARY_INTEGER;
182 TYPE t_status			IS TABLE OF hxc_transaction_details.status%TYPE INDEX BY BINARY_INTEGER;
183 TYPE t_exception_description	IS TABLE OF hxc_transaction_details.exception_description%TYPE INDEX BY BINARY_INTEGER;
184 
185 t_tx_time_bb_id t_time_building_block_id;
186 t_tx_time_bb_ovn t_time_building_block_ovn;
187 t_tx_time_transaction_id t_transaction_id;
188 t_tx_time_status t_status;
189 t_tx_time_exception t_exception_description;
190 
191 t_tx_day_bb_id t_time_building_block_id;
192 t_tx_day_parent_id t_time_building_block_id;
193 t_tx_day_bb_ovn t_time_building_block_ovn;
194 t_tx_day_transaction_id t_transaction_id;
195 t_tx_day_status t_status;
196 t_tx_day_exception t_exception_description;
197 
198 t_tx_detail_bb_id t_time_building_block_id;
199 t_tx_detail_parent_id t_time_building_block_id;
200 t_tx_detail_bb_ovn t_time_building_block_ovn;
201 t_tx_detail_transaction_id t_transaction_id;
202 t_tx_detail_status t_status;
203 t_tx_detail_exception t_exception_description;
204 
205 t_tx_error_bb_id t_time_building_block_id;
206 t_tx_error_bb_ovn t_time_building_block_ovn;
207 t_tx_error_transaction_id t_transaction_id;
208 t_tx_error_status t_status;
209 t_tx_error_exception t_exception_description;
210 
211 g_transaction_id	hxc_transactions.transaction_id%TYPE;
212 g_retrieval_process_id	hxc_transactions.transaction_process_id%TYPE;
213 g_retrieval_tr_id	hxc_time_recipients.time_recipient_id%TYPE;
214 
215 g_lock_type varchar2(35);
216 
217 
218 -- new globals to support iterative calling
219 
220 
221 G_IN_LOOP            BOOLEAN := FALSE;
222 G_LAST_CHUNK         BOOLEAN := FALSE;
223 G_NO_TIMECARDS         BOOLEAN := TRUE;
224 G_OVERALL_NO_TIMECARDS BOOLEAN := TRUE;
225 G_CHUNK_NUMBER       NUMBER := 0;
226 G_TRANS_CODE         hxc_transactions.transaction_code%TYPE;
227 G_TRANS_PREFIX       varchar2(11);
228 
229 -- new globals for skipped records
230 
231 TYPE r_detail_skipped IS RECORD (resource_id hxc_time_building_blocks.resource_id%TYPE,
232 			timecard_id hxc_time_building_blocks.time_building_block_id%TYPE,
233 			timecard_ovn hxc_time_building_blocks.object_version_number%TYPE,
234 			bb_id hxc_time_building_blocks.time_building_block_id%TYPE,
235 			ovn hxc_time_building_blocks.object_version_number%TYPE,
236 			description VARCHAR2(80));
237 
238 TYPE t_detail_skipped IS TABLE OF r_detail_skipped INDEX BY BINARY_INTEGER;
239 g_detail_skipped t_detail_skipped;
240 
241 --Bug 8888911
242 -- Added this datatype.
243 TYPE NUMBERTABLE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
244 g_res_list  NUMBERTABLE;
245 -- Bug 9458888
246 g_temp_tc_list NUMBERTABLE;
247 
248 -- Bug 9494444
249 g_old_bb_ids   NUMBERTABLE;
250 
251 
252 TYPE VARCHARTAB IS TABLE OF VARCHAR2(150);
253 
254 -- Bug 9747820
255 -- New Global tables to process transferred_to.
256 g_timecards_array NUMBERTABLE;
257 g_tc_ovn_array    NUMBERTABLE;
258 
259 
260 
261 TYPE r_rtr_detail_blks IS RECORD (dummy VARCHAR2(1));
262 TYPE t_rtr_detail_blks IS TABLE OF r_rtr_detail_blks INDEX BY BINARY_INTEGER;
263 g_rtr_detail_blks t_rtr_detail_blks;
264 
265 -- Bug 9458888
266 -- Added to process skipped details
267       l_skipped_tc_id         VARCHARTAB;
268       l_skipped_bb_id         VARCHARTAB;
269       l_skipped_bb_ovn        VARCHARTAB;
270       l_skipped_desc          VARCHARTAB;
271       l_index                 NUMBER;
272 
273 
274 PROCEDURE execute_retrieval_process (
275 	p_process	in	hxc_retrieval_processes.name%TYPE
276 ,	p_transaction_code in	VARCHAR2
277 ,	p_start_date	in	DATE default null
278 ,	p_end_date	in	DATE default null
279 ,	p_incremental	in	VARCHAR2 default 'Y'
280 ,	p_rerun_flag	in	VARCHAR2 default 'N'
281 ,	p_where_clause	in	VARCHAR2
282 ,	p_scope		in	VARCHAR2 default 'DAY'
283 ,	p_clusive	in	VARCHAR2 default 'EX'
284 ,       p_unique_params in      VARCHAR2 default null
285 ,       p_since_date    in      VARCHAR2 default null
286 );
287 
288 Procedure Update_Transaction_Status ( p_process			hxc_retrieval_processes.name%TYPE
289 				,     p_status			hxc_transactions.status%TYPE
290 				,     p_exception_description   hxc_transactions.exception_description%TYPE
291 				,     p_rollback BOOLEAN DEFAULT FALSE );
292 
293 --Bug 8888911
294 --Added the new function to help easily write to
295 -- Conc process log and fnd_log_messages.
296 PROCEDURE put_log(p_text   IN VARCHAR2);
297 
298 -- Bug 9458888
299 -- Autonomous procedure used to update the status of timecards
300 
301 PROCEDURE update_rdb_status ( p_tc_list  NUMBERTABLE,
302                               p_from_status   VARCHAR2,
303                               p_to_status     VARCHAR2);
304 
305 -- Bug 9701936
306 -- Added this data type and procedure definition.
307 
308 TYPE NUMTABLE IS TABLE OF NUMBER;
309 
310 PROCEDURE update_rdb_status ( p_tc_list  NUMTABLE,
311                               p_from_status   VARCHAR2,
312                               p_to_status     VARCHAR2);
313 
314 
315 end hxc_generic_retrieval_pkg;