[Home] [Help]
PACKAGE BODY: APPS.RCV_HXT_GRP
Source
1 PACKAGE BODY RCV_HXT_GRP AS
2 /* $Header: RCVGHXTB.pls 120.17 2008/04/27 08:56:05 adbharga noship $ */
3
4 -- record for all timecard attributes interesting to Purchasing
5 TYPE TimecardAttributesRec IS RECORD
6 ( timecard_bb_id HXC_TIME_BUILDING_BLOCKS.time_building_block_id%TYPE
7 , timecard_bb_ovn HXC_TIME_BUILDING_BLOCKS.object_version_number%TYPE
8 , timecard_start_time HXC_TIME_BUILDING_BLOCKS.start_time%TYPE
9 , timecard_stop_time HXC_TIME_BUILDING_BLOCKS.stop_time%TYPE
10 , timecard_approval_status HXC_TIMECARD_SUMMARY.approval_status%TYPE
11 , timecard_approval_date HXC_TIME_BUILDING_BLOCKS.date_from%TYPE
12 , timecard_submission_date HXC_TIMECARD_SUMMARY.submission_date%TYPE
13 , timecard_comment HXC_TIME_BUILDING_BLOCKS.comment_text%TYPE
14 , day_bb_id HXC_TIME_BUILDING_BLOCKS.time_building_block_id%TYPE
15 , day_start_time HXC_TIME_BUILDING_BLOCKS.start_time%TYPE
16 , detail_bb_id HXC_TIME_BUILDING_BLOCKS.time_building_block_id%TYPE
17 , detail_bb_ovn HXC_TIME_BUILDING_BLOCKS.object_version_number%TYPE
18 , detail_type HXC_TIME_BUILDING_BLOCKS.type%TYPE
19 , detail_measure HXC_TIME_BUILDING_BLOCKS.measure%TYPE
20 , detail_start_time HXC_TIME_BUILDING_BLOCKS.start_time%TYPE
21 , detail_stop_time HXC_TIME_BUILDING_BLOCKS.stop_time%TYPE
22 , detail_uom HXC_TIME_BUILDING_BLOCKS.unit_of_measure%TYPE
23 , detail_changed VARCHAR2(30)
24 , detail_new VARCHAR2(30)
25 , detail_deleted VARCHAR2(30)
26 , detail_date_from HXC_TIME_BUILDING_BLOCKS.date_from%TYPE
27 , detail_date_to HXC_TIME_BUILDING_BLOCKS.date_to%TYPE
28 , resource_id HXC_TIME_BUILDING_BLOCKS.resource_id%TYPE
29 , po_number PO_HEADERS_ALL.segment1%TYPE
30 , po_header_id PO_HEADERS_ALL.po_header_id%TYPE
31 , po_line PO_LINES_ALL.line_num%TYPE
32 , po_line_id PO_LINES_ALL.po_line_id%TYPE
33 , po_line_location_id PO_LINE_LOCATIONS_ALL.line_location_id%TYPE
34 , po_distribution_id PO_DISTRIBUTIONS_ALL.po_distribution_id%TYPE
35 , project_id PO_DISTRIBUTIONS_ALL.project_id%TYPE
36 , task_id PO_DISTRIBUTIONS_ALL.task_id%TYPE
37 , po_price_type PO_TEMP_LABOR_RATES_V.asg_rate_type%TYPE
38 , po_price_type_display PO_TEMP_LABOR_RATES_V.price_type_dsp%TYPE
39 , po_billable_amount PO_LINES_ALL.amount%TYPE
40 , po_receipt_date RCV_TRANSACTIONS.transaction_date%TYPE
41 , lpn_group_id RCV_TRANSACTIONS.lpn_group_id%TYPE
42
43 -- save the transaction type so we know how to check for success
44 , transaction_type VARCHAR2(240)
45
46 -- we need to reference two rti rows for corrections
47 , receive_rti_id RCV_TRANSACTIONS_INTERFACE.interface_transaction_id%TYPE
48 , deliver_rti_id RCV_TRANSACTIONS_INTERFACE.interface_transaction_id%TYPE
49
50 -- we need to reference four rti rows for delete+insert
51 , delete_receive_rti_id RCV_TRANSACTIONS_INTERFACE.interface_transaction_id%TYPE
52 , delete_deliver_rti_id RCV_TRANSACTIONS_INTERFACE.interface_transaction_id%TYPE
53
54 -- parent txns exist when we have received against this po line before
55 , parent_receive_txn_id RCV_TRANSACTIONS.transaction_id%TYPE
56 , parent_deliver_txn_id RCV_TRANSACTIONS.transaction_id%TYPE
57
58 -- org_id of the PO/CWK
59 , org_id PO_HEADERS_ALL.org_id%TYPE
60
61 -- save the purchasing category attribute_id to attach to error messages
62 , time_attribute_id HXC_TIME_ATTRIBUTES.time_attribute_id%TYPE
63
64 -- transient variable to save the validation status
65 , validation_status VARCHAR2(30)
66
67 -- is this the old version of a changed block
68 , old_block VARCHAR2(1)
69 );
70
71 -- table to store all attributes for a particular block
72 TYPE TimecardAttributesTbl IS TABLE OF TimecardAttributesRec INDEX BY BINARY_INTEGER;
73
74 -- temp tables for ROI data
75 -- We will create one rhi row per PO per timecard
76 -- and one rti row per PO line per timecard
77 TYPE rhi_table IS TABLE OF RCV_HEADERS_INTERFACE%ROWTYPE INDEX BY BINARY_INTEGER;
78 TYPE rti_table IS TABLE OF RCV_TRANSACTIONS_INTERFACE%ROWTYPE INDEX BY BINARY_INTEGER;
79
80 -- store results of the above in a hash for quick lookup
81 TYPE rti_status_table IS TABLE OF BINARY_INTEGER INDEX BY BINARY_INTEGER;
82
83 -- cache records for use in caches
84
85 TYPE po_header_cr IS RECORD
86 ( po_header_id PO_HEADERS_ALL.po_header_id%TYPE
87 , segment1 PO_HEADERS_ALL.segment1%TYPE
88 , user_hold_flag PO_HEADERS_ALL.user_hold_flag%TYPE
89 , org_id PO_HEADERS_ALL.org_id%TYPE
90 , vendor_id PO_HEADERS_ALL.vendor_id%TYPE
91 , vendor_site_id PO_HEADERS_ALL.vendor_site_id%TYPE
92 );
93
94 -- Combine PO line/shipment info into a single record because
95 -- there is a 1-1 mapping between line and shipment for
96 -- Rate-Based Temp Labor
97 TYPE po_line_cr IS RECORD
98 ( po_line_id PO_LINES_ALL.po_line_id%TYPE
99 , po_header_id PO_LINES_ALL.po_header_id%TYPE
100 , line_num PO_LINES_ALL.line_num%TYPE
101 , unit_price PO_LINES_ALL.unit_price%TYPE
102 , matching_basis PO_LINES_ALL.matching_basis%TYPE
103 , purchase_basis PO_LINES_ALL.purchase_basis%TYPE
104 , order_type_lookup_code PO_LINES_ALL.order_type_lookup_code%TYPE
105 , start_date PO_LINES_ALL.start_date%TYPE
106 , expiration_date PO_LINES_ALL.expiration_date%TYPE
107 , job_id PO_LINES_ALL.job_id%TYPE
108 , line_location_id PO_LINE_LOCATIONS_ALL.line_location_id%TYPE
109 , approved_flag PO_LINE_LOCATIONS_ALL.approved_flag%TYPE
110 , cancel_flag PO_LINE_LOCATIONS_ALL.cancel_flag%TYPE
111 , closed_code PO_LINE_LOCATIONS_ALL.closed_code%TYPE
112 , qty_rcv_exception_code PO_LINE_LOCATIONS_ALL.qty_rcv_exception_code%TYPE
113 , tolerable_amount PO_LINE_LOCATIONS_ALL.amount%TYPE
114 , timecard_amount PO_LINE_LOCATIONS_ALL.amount%TYPE
115 , ship_to_organization_id PO_LINE_LOCATIONS_ALL.ship_to_organization_id%TYPE
116 , ship_to_location_id PO_LINE_LOCATIONS_ALL.ship_to_location_id%TYPE
117 , time_attribute_id HXC_TIME_ATTRIBUTES.time_attribute_id%TYPE
118 );
119
120 TYPE po_distribution_cr IS RECORD
121 ( po_distribution_id PO_DISTRIBUTIONS_ALL.po_distribution_id%TYPE
122 , project_id PO_DISTRIBUTIONS_ALL.project_id%TYPE
123 , task_id PO_DISTRIBUTIONS_ALL.task_id%TYPE
124 );
125
126 TYPE fnd_lookups_cr IS RECORD
127 ( lookup_code FND_LOOKUPS.lookup_code%TYPE
128 , meaning FND_LOOKUPS.meaning%TYPE
129 );
130
131 TYPE price_differentials_cr IS RECORD
132 ( entity_id PO_PRICE_DIFFERENTIALS.entity_id%TYPE
133 , price_type PO_PRICE_DIFFERENTIALS.price_type%TYPE
134 , enabled_flag PO_PRICE_DIFFERENTIALS.enabled_flag%TYPE
135 , multiplier PO_PRICE_DIFFERENTIALS.multiplier%TYPE
136 , price PO_LINES_ALL.unit_price%TYPE
137 );
138
139 -- The PO information is new to 11.5.10 so do not introduce
140 -- compile-time dependency on those fields
141 TYPE per_all_assignments_cr IS RECORD
142 ( person_id PER_ALL_ASSIGNMENTS_F.person_id%TYPE
143 , po_line_id PO_LINES_ALL.po_line_id%TYPE
144 , effective_start_date PER_ALL_ASSIGNMENTS_F.effective_start_date%TYPE
145 , effective_end_date PER_ALL_ASSIGNMENTS_F.effective_end_date%TYPE
146 );
147
148 TYPE rcv_transactions_cr IS RECORD
149 ( receive_transaction_id RCV_TRANSACTIONS.transaction_id%TYPE
150 , deliver_transaction_id RCV_TRANSACTIONS.transaction_id%TYPE
151 , po_line_id RCV_TRANSACTIONS.po_line_id%TYPE
152 , po_distribution_id PO_DISTRIBUTIONS_ALL.po_distribution_id%TYPE
153 , timecard_id RCV_TRANSACTIONS.timecard_id%TYPE
154 , timecard_ovn RCV_TRANSACTIONS.timecard_ovn%TYPE
155 );
156
157 -- cache results of expensive OTL APIs and SQLs
158 TYPE build_block_cache IS TABLE OF HXC_USER_TYPE_DEFINITION_GRP.building_block_info INDEX BY BINARY_INTEGER;
159 TYPE build_attribute_cache IS TABLE OF HXC_USER_TYPE_DEFINITION_GRP.attribute_info INDEX BY BINARY_INTEGER;
160 TYPE po_header_cache IS TABLE OF po_header_cr INDEX BY BINARY_INTEGER;
161 TYPE po_line_cache IS TABLE OF po_line_cr INDEX BY BINARY_INTEGER;
162 TYPE po_distribution_cache IS TABLE OF po_distribution_cr INDEX BY BINARY_INTEGER;
163 TYPE price_type_lookup_cache IS TABLE OF fnd_lookups_cr INDEX BY BINARY_INTEGER;
164 TYPE price_differentials_cache IS TABLE OF price_differentials_cr INDEX BY BINARY_INTEGER;
165 TYPE assignments_cache IS TABLE OF per_all_assignments_cr INDEX BY BINARY_INTEGER;
166 TYPE rcv_transactions_cache IS TABLE OF rcv_transactions_cr INDEX BY BINARY_INTEGER;
167
168 -- package globals
169 G_PKG_NAME CONSTANT VARCHAR2(30) := 'RCV_HXT_GRP';
170 G_LOG_MODULE CONSTANT VARCHAR2(40) := 'po.plsql.' || G_PKG_NAME;
171 G_CONC_LOG VARCHAR2(32767);
172 -- bug 5976883 : Have changed the fnd logging logic according to PO standards
173 -- Now at all places we are using the module.package.procedure convention.
174 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
175 g_debug_unexp CONSTANT BOOLEAN := PO_DEBUG.is_debug_unexp_on;
176 -- global counters for summary reporting
177 g_retrieved_details NUMBER := 0;
178 g_successful_details NUMBER := 0;
179 g_failed_details NUMBER := 0;
180 g_req_id NUMBER := 0;
181 g_group_id NUMBER := 0;
182 g_txn_status hxc_transactions.status%TYPE;
183 g_txn_msg hxc_transactions.exception_description%TYPE;
184 g_overall_status hxc_transactions.status%TYPE;
185
186 -- caches
187 g_build_block_cache build_block_cache;
188 g_build_attribute_cache build_attribute_cache;
189 g_po_header_cache po_header_cache;
190 g_po_line_cache po_line_cache;
191 g_po_distribution_cache po_distribution_cache;
192 g_price_type_lookup_cache price_type_lookup_cache;
193 g_price_differentials_cache price_differentials_cache;
194 g_assignments_cache assignments_cache;
195 g_rcv_transactions_cache rcv_transactions_cache;
196
197 -- performance info
198 g_retrieval_start DATE;
199 g_retrieval_stop DATE;
200 g_retrieval_time NUMBER;
201 g_generic_start DATE;
202 g_generic_stop DATE;
203 g_generic_time NUMBER;
204 g_receiving_start DATE;
205 g_receiving_stop DATE;
206 g_receiving_time NUMBER;
207 g_update_start DATE;
208 g_update_stop DATE;
209 g_validate_start DATE;
210 g_validate_stop DATE;
211
212 g_build_block_calls NUMBER;
213 g_build_attribute_calls NUMBER;
214 g_po_header_calls NUMBER;
215 g_po_line_calls NUMBER;
216 g_po_distribution_calls NUMBER;
217 g_price_type_lookup_calls NUMBER;
218 g_price_differentials_calls NUMBER;
219 g_assignments_calls NUMBER;
220 g_rcv_transactions_calls NUMBER;
221
222 g_build_block_misses NUMBER;
223 g_build_attribute_misses NUMBER;
224 g_po_header_misses NUMBER;
225 g_po_line_misses NUMBER;
226 g_po_distribution_misses NUMBER;
227 g_price_type_lookup_misses NUMBER;
228 g_price_differentials_misses NUMBER;
229 g_assignments_misses NUMBER;
230 g_rcv_transactions_misses NUMBER;
231
232 g_error_raised_flag NUMBER := 0;--Bug:5559915
233 /** Bug:5559915
234 * Above variable is introduced to prevent logging of same
235 * error message for each entries in the Time Card.
236 * g_error_raised_flag = 0 error message is not logged
237 * g_error_raised_flag = 1 error message is logged
238 */
239
240 -- cursor for retrieving successful receiving transactions
241 CURSOR new_rt_rows( v_group_id VARCHAR2 ) IS
242 SELECT po_line_id
243 , timecard_id
244 , interface_transaction_id
245 FROM rcv_transactions
246 WHERE group_id = v_group_id;
247
248 ISP_STORE_TIMECARD_FAILED EXCEPTION;
249 ISP_RECONCILE_ACTIONS_FAILED EXCEPTION;
250 RETRIEVAL_FAILED EXCEPTION;
251 DEBUGGING_BREAKPOINT EXCEPTION;
252 DERIVE_DISTRIBUTION_ID_FAILED EXCEPTION;
253 DERIVE_JOB_ID_FAILED EXCEPTION;
254 DERIVE_ROI_VALUES_FAILED EXCEPTION;
255 TIMECARD_NOT_APPROVED EXCEPTION;
256
257 -- Private support procedures
258
259 -- wrapper for RCV_HXT_GRP.string
260 -- Bug 5976883 : Rewrote string debug function according to PO standards
261 PROCEDURE string
262 ( log_level IN number
263 , module IN varchar2
264 , message IN varchar2
265 ) IS
266 l_debug_on BOOLEAN;
267 l_progress VARCHAR2(3) := '000';
268 BEGIN
269 IF NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N') = 'Y' THEN
270 l_debug_on := TRUE;
271 END IF;
272 -- add to fnd_log_messages
273 -- asn_debug.put_line(module||': '||message,log_level);
274 if (log_level = FND_LOG.LEVEL_STATEMENT and g_debug_stmt ) then
275 po_debug.debug_stmt(module,l_progress,message);
276
277 elsif (log_level = FND_LOG.LEVEL_UNEXPECTED and g_debug_unexp ) then
278 po_debug.debug_unexp(module,l_progress,message);
279
280 elsif (l_debug_on and log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
281 FND_LOG.string(
282 log_level
283 , module||'.'||l_progress
284 , message
285 );
286 end if;
287
288 BEGIN
289 FND_FILE.put_line(FND_FILE.log, message);
290 EXCEPTION
291 WHEN FND_FILE.UTL_FILE_ERROR THEN
292 NULL;
293 END;
294 END string;
295 -- bug 5976883 : This will help us debug attribute records.
296 PROCEDURE debug_TimecardAttributesRec
297 ( p_log_head IN varchar2
298 , p_attributes IN TimecardAttributesRec
299 , l_new_old IN varchar2 DEFAULT NULL
300 ) IS
301 l_progress varchar2(3):= '000';
302 l_api_name CONSTANT varchar2(30) := 'debug_TimecardAttributesRec';
303 l_log_head CONSTANT VARCHAR2(100) := G_LOG_MODULE || l_api_name;
304 BEGIN
305 if g_debug_stmt then
306 PO_DEBUG.debug_var(l_log_head,l_progress,l_new_old ||'-timecard_bb_id ' , p_attributes.timecard_bb_id);
307 PO_DEBUG.debug_var(l_log_head,l_progress,l_new_old ||'-timecard_bb_ovn ' , p_attributes.timecard_bb_ovn);
308 PO_DEBUG.debug_var(l_log_head,l_progress,l_new_old ||'-timecard_start_time ' , p_attributes.timecard_start_time);
309 PO_DEBUG.debug_var(l_log_head,l_progress,l_new_old ||'-timecard_stop_time ' , p_attributes.timecard_stop_time);
310 PO_DEBUG.debug_var(l_log_head,l_progress,l_new_old ||'-timecard_approval_status ', p_attributes.timecard_approval_status);
311 PO_DEBUG.debug_var(l_log_head,l_progress,l_new_old ||'-timecard_approval_date ' , p_attributes.timecard_approval_date);
312 PO_DEBUG.debug_var(l_log_head,l_progress,l_new_old ||'-timecard_submission_date ', p_attributes.timecard_submission_date);
313 PO_DEBUG.debug_var(l_log_head,l_progress,l_new_old ||'-timecard_comment ' , p_attributes.timecard_comment);
314 PO_DEBUG.debug_var(l_log_head,l_progress,l_new_old ||'-day_bb_id ' , p_attributes.day_bb_id);
315 PO_DEBUG.debug_var(l_log_head,l_progress,l_new_old ||'-day_start_time ' , p_attributes.day_start_time);
316 PO_DEBUG.debug_var(l_log_head,l_progress,l_new_old ||'-detail_bb_id ' , p_attributes.detail_bb_id);
317 PO_DEBUG.debug_var(l_log_head,l_progress,l_new_old ||'-detail_bb_ovn ' , p_attributes.detail_bb_ovn);
318 PO_DEBUG.debug_var(l_log_head,l_progress,l_new_old ||'-detail_type ' , p_attributes.detail_type);
319 PO_DEBUG.debug_var(l_log_head,l_progress,l_new_old ||'-detail_measure ' , p_attributes.detail_measure);
320 PO_DEBUG.debug_var(l_log_head,l_progress,l_new_old ||'-detail_start_time ' , p_attributes.detail_start_time);
321 PO_DEBUG.debug_var(l_log_head,l_progress,l_new_old ||'-detail_stop_time ' , p_attributes.detail_stop_time);
322 PO_DEBUG.debug_var(l_log_head,l_progress,l_new_old ||'-detail_uom ' , p_attributes.detail_uom);
323 PO_DEBUG.debug_var(l_log_head,l_progress,l_new_old ||'-detail_changed ' , p_attributes.detail_changed);
324 PO_DEBUG.debug_var(l_log_head,l_progress,l_new_old ||'-detail_new ' , p_attributes.detail_new);
325 PO_DEBUG.debug_var(l_log_head,l_progress,l_new_old ||'-detail_deleted ' , p_attributes.detail_deleted);
326 PO_DEBUG.debug_var(l_log_head,l_progress,l_new_old ||'-detail_date_from ' , p_attributes.detail_date_from);
327 PO_DEBUG.debug_var(l_log_head,l_progress,l_new_old ||'-detail_date_to ' , p_attributes.detail_date_to);
328 PO_DEBUG.debug_var(l_log_head,l_progress,l_new_old ||'-resource_id ' , p_attributes.resource_id);
329 PO_DEBUG.debug_var(l_log_head,l_progress,l_new_old ||'-po_number ' , p_attributes.po_number);
330 PO_DEBUG.debug_var(l_log_head,l_progress,l_new_old ||'-po_header_id ' , p_attributes.po_header_id);
331 PO_DEBUG.debug_var(l_log_head,l_progress,l_new_old ||'-po_line ' , p_attributes.po_line);
332 PO_DEBUG.debug_var(l_log_head,l_progress,l_new_old ||'-po_line_id ' , p_attributes.po_line_id);
333 PO_DEBUG.debug_var(l_log_head,l_progress,l_new_old ||'-po_line_location_id ' , p_attributes.po_line_location_id);
334 PO_DEBUG.debug_var(l_log_head,l_progress,l_new_old ||'-po_distribution_id ' , p_attributes.po_distribution_id);
335 PO_DEBUG.debug_var(l_log_head,l_progress,l_new_old ||'-project_id ' , p_attributes.project_id);
336 PO_DEBUG.debug_var(l_log_head,l_progress,l_new_old ||'-task_id ' , p_attributes.task_id);
337 PO_DEBUG.debug_var(l_log_head,l_progress,l_new_old ||'-po_price_type ' , p_attributes.po_price_type);
338 PO_DEBUG.debug_var(l_log_head,l_progress,l_new_old ||'-po_price_type_display ' , p_attributes.po_price_type_display);
339 PO_DEBUG.debug_var(l_log_head,l_progress,l_new_old ||'-po_billable_amount ' , p_attributes.po_billable_amount);
340 PO_DEBUG.debug_var(l_log_head,l_progress,l_new_old ||'-po_receipt_date ' , p_attributes.po_receipt_date);
341 PO_DEBUG.debug_var(l_log_head,l_progress,l_new_old ||'-lpn_group_id ' , p_attributes.lpn_group_id);
342 end if;
343
344 END debug_TimecardAttributesRec;
345
346
347 -- bug 5976883 <Debug END>
348
349 -- bug 5928019 : Need to set the rhi rows to error status if none of teh
350 -- associated rti rows are in PENDING status. We will call this just
351 -- before inserting the data into RHI AND RTI
352
353 procedure set_rhi_table_status (p_rhi_rows IN OUT NOCOPY rhi_table,
354 p_rti_rows IN OUT NOCOPY rti_table,
355 p_processable_rows_exist IN OUT NOCOPY VARCHAR2) IS
356
357 l_transaction_id NUMBER;
358 l_status VARCHAR2(15);
359 type l_index_table is table of VARCHAR2(10) index by binary_integer;
360 l_temp l_index_table;
361 l_api_name CONSTANT varchar2(30) := 'set_rhi_table_status';
362 l_log_head CONSTANT VARCHAR2(100) := G_LOG_MODULE || '.'||l_api_name;
363 l_progress VARCHAR2(3) := '000';
364 BEGIN
365 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
366 , module => l_log_head
367 , message => 'Begin set_rhi_table_status'
368 );
369 -- bug 6000903 <start>
370 /* New logic
371
372 The structure:
373 1. There can be rti rows corresponding to rhi rows (new receipts) or there can be
374 rti rows without rhi rows (corrections).
375 2. These rows will have the header_interface_id AS null.
376 3. The rti records have the correct value of the processign status code, however
377 we need to set the processing_status_code of the rhi records and also need to determine
378 that finally if there are any net processable rows
379
380
381 The Algo:
382 scratch pad l_temp, which has a cell for each header_interface_id in the rhi table
383 *loop through the rti table
384 *If an errored record is found check its header interface_id
385 *if header_interface_id is present => the record belongs to a rhi record
386 *mark the scratch pad's cell for this header_interface_id as ERROR if it is not already
387 pending
388 *This is done so that we record header_interface as pending even if one associated
389 rti record exists with a pending status.
390 *if rti processing_status_code is ERROR and no rhi id is present, do nothing
391 *if rti processing status code is not ERROR, and rhi id is present mark the
392 scratch pad's cell for that rhi id as PENDING, to indicate a processable record
393 has been found. Also set the processable_rows_exist to Y.
394 *similarly if rti ps code is NOT error and rhi id is NULL processable rows exist
395
396 Finally check the scratch pad, all header_ids that have ERROR in their
397 respective cells will be marked as ERROR */
398 -- bug 6391432
399 -- Found that after having one failed record with RTI ID = NULL. The whole
400 -- batch was failing in retrival process. This was due to an unhanded exception
401 -- [NO DATA FOUND] was raised in the set_rhi_table_status.
402 -- l_temp : Place holder plsql table indexed by header_interface_id. There
403 -- are senario's that this table wont have value for some header_interface_id
404 -- So when we looping through p_rti_rows there for some header_interface_id
405 -- l_temp(p_rti_rows(i).header_interface_id) will not have any value. Which will
406 -- throw a NO DATA FOUND exception.
407 -- Added logic to overcome this problem. by using .exists() function.
408
409 p_processable_rows_exist := 'N';
410 for i in 1..p_rti_rows.count loop
411 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE,module =>
412 l_log_head,message => 'p_rti_rows(i).processing_status_code :'||p_rti_rows(i).processing_status_code);
413 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE,module =>
414 l_log_head,message => 'p_rti_rows(i).header_interface_id :'||p_rti_rows(i).header_interface_id);
415
416 if ((p_rti_rows(i).processing_status_code = 'ERROR') AND
417 (p_rti_rows(i).header_interface_id is not null)) THEN
418 if l_temp.exists(p_rti_rows(i).header_interface_id) THEN
419 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE,module => l_log_head,message =>
420 'Setting ERROR l_temp(p_rti_rows(i).header_interface_id) :'||l_temp(p_rti_rows(i).header_interface_id));
421 if nvl(l_temp(p_rti_rows(i).header_interface_id),'ERROR') <> 'PENDING' then
422 l_temp(p_rti_rows(i).header_interface_id) := 'ERROR';
423 end if;
424 ELSE
425 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE,module => l_log_head,message => 'Setting ERROR l_temp header_interface_id ');
426 l_temp(p_rti_rows(i).header_interface_id) := 'ERROR';
427 end if;
428
429 -- do nothing for condition rti_processing_status_code = ERROR AND
430 -- rti_header_interface_id NULL
431 elsif ((p_rti_rows(i).processing_status_code <> 'ERROR') AND
432 (p_rti_rows(i).header_interface_id is not null)) then
433 l_temp(p_rti_rows(i).header_interface_id) := 'PENDING';
434 RCV_HXT_GRP.string( log_level =>
435 FND_LOG.LEVEL_PROCEDURE,module => l_log_head,message => 'Setting Pending l_temp');
436 p_processable_rows_exist := 'Y';
437
438 elsif ((p_rti_rows(i).processing_status_code <> 'ERROR') AND
439 (p_rti_rows(i).header_interface_id is null)) then
440 p_processable_rows_exist := 'Y';
441 end if;
442 end loop;
443
444 if p_rhi_rows.count > 0 then
445 for i in 1..p_rhi_rows.count loop
446 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE,module => l_log_head,message =>
447 'Loop 2 p_rhi_rows.header_interface_id :'||p_rhi_rows(i).header_interface_id);
448 if l_temp.exists(p_rhi_rows(i).header_interface_id) THEN
449 if l_temp(p_rhi_rows(i).header_interface_id) = 'ERROR' THEN
450 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE,module => l_log_head,message =>
451 'Loop 2 l_temp :'||l_temp(p_rhi_rows(i).header_interface_id));
452 p_rhi_rows(i).processing_status_code := 'ERROR';
453 end if;
454 end if;
455 end loop;
456 end if;
457 EXCEPTION
458 WHEN OTHERS THEN
459 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
460 , module => l_log_head
461 , message => 'Unexpected exception in set_rhi_table_status ' || SQLERRM
462 );
463 RAISE;
464 END set_rhi_table_status;
465
466 PROCEDURE initialize_cache_statistics IS
467 BEGIN
468 -- We want to maintain the stats throughout the session
469 -- so do not reset to zero if they are non-null
470 IF g_build_block_calls IS NULL THEN
471 g_build_block_calls := 0;
472 g_build_attribute_calls := 0;
473 g_po_header_calls := 0;
474 g_po_line_calls := 0;
475 g_po_distribution_calls := 0;
476 g_price_type_lookup_calls := 0;
477 g_price_differentials_calls := 0;
478 g_assignments_calls := 0;
479 g_rcv_transactions_calls := 0;
480
481 g_build_block_misses := 0;
482 g_build_attribute_misses := 0;
483 g_po_header_misses := 0;
484 g_po_line_misses := 0;
485 g_po_distribution_misses := 0;
486 g_price_type_lookup_misses := 0;
487 g_price_differentials_misses := 0;
488 g_assignments_misses := 0;
489 g_rcv_transactions_misses := 0;
490 END IF;
491 END initialize_cache_statistics;
492
493 PROCEDURE initialize_timing_statistics IS
494 BEGIN
495 -- We want to maintain the stats throughout the session
496 -- so do not reset to zero if they are non-null
497 IF g_retrieval_time IS NULL THEN
498 g_retrieval_time := 0;
499 g_generic_time := 0;
500 g_receiving_time := 0;
501 END IF;
502 END initialize_timing_statistics;
503
504 -- This procedure is called by the update process
505 -- to reset state of the caches for each timecard
506 -- submission without actually clearing the caches
507 PROCEDURE initialize_caches IS
508 l_po_line_id PO_LINES_ALL.po_line_id%TYPE;
509 BEGIN
510 l_po_line_id := g_po_line_cache.FIRST;
511 WHILE l_po_line_id IS NOT NULL LOOP
512 -- Reset the amounts in case the user tries
513 -- to submit a timecard more than once
514 g_po_line_cache(l_po_line_id).timecard_amount := 0;
515 l_po_line_id := g_po_line_cache.NEXT(l_po_line_id);
516 END LOOP;
517 END initialize_caches;
518
519 FUNCTION get_rhi_idx
520 ( p_attributes IN TimecardAttributesRec
521 , p_rhi_rows IN rhi_table
522 , p_rti_rows IN rti_table
523 ) RETURN NUMBER IS
524 l_rhi_id RCV_HEADERS_INTERFACE.header_interface_id%TYPE;
525 BEGIN
526 -- find a transaction of the matching header to get the header_interface_id
527 FOR i IN 1..p_rti_rows.COUNT LOOP
528 IF p_rti_rows(i).po_header_id = p_attributes.po_header_id AND
529 p_rti_rows(i).timecard_id = p_attributes.timecard_bb_id THEN
530 l_rhi_id := p_rti_rows(i).header_interface_id;
531 EXIT;
532 END IF;
533 END LOOP;
534
535 -- use the header_interface_id to find the index in rhi_rows
536 IF l_rhi_id IS NOT NULL THEN
537 FOR i IN 1..p_rhi_rows.COUNT LOOP
538 IF p_rhi_rows(i).header_interface_id = l_rhi_id THEN
539 RETURN i;
540 END IF;
541 END LOOP;
542 END IF;
543
544 -- index not found
545 RETURN NULL;
546 END get_rhi_idx;
547
548 FUNCTION get_rti_idx
549 ( p_attributes IN TimecardAttributesRec
550 , p_rti_rows IN rti_table
551 ) RETURN RCV_TRANSACTIONS_INTERFACE.interface_transaction_id%TYPE IS
552 BEGIN
553 -- projects case placed in separate loop so we only test once
554 IF p_attributes.project_id IS NOT NULL AND
555 p_attributes.task_id IS NOT NULL THEN
556 FOR i IN 1..p_rti_rows.COUNT LOOP
557 IF p_rti_rows(i).po_distribution_id = p_attributes.po_distribution_id AND
558 p_rti_rows(i).timecard_id = p_attributes.timecard_bb_id THEN
559 RETURN i;
560 END IF;
561 END LOOP;
562
563 -- non-projects case
564 ELSE
565 FOR i IN 1..p_rti_rows.COUNT LOOP
566 IF p_rti_rows(i).po_line_id = p_attributes.po_line_id AND
567 p_rti_rows(i).timecard_id = p_attributes.timecard_bb_id THEN
568 RETURN i;
569 END IF;
570 END LOOP;
571 END IF;
572
573 -- index not found
574 RETURN NULL;
575 END get_rti_idx;
576
577 FUNCTION get_group_id
578 ( p_rti_rows IN rti_table
579 ) RETURN RCV_TRANSACTIONS_INTERFACE.group_id%TYPE IS
580 BEGIN
581 IF g_group_id = 0 THEN
582 SELECT RCV_INTERFACE_GROUPS_S.NEXTVAL
583 INTO g_group_id
584 FROM dual;
585 END IF;
586
587 RETURN g_group_id;
588 END get_group_id;
589
590 FUNCTION get_po_header
591 ( p_po_header_id IN PO_HEADERS_ALL.po_header_id%TYPE
592 ) RETURN po_header_cr IS
593 BEGIN
594 g_po_header_calls := g_po_header_calls + 1;
595
596 IF NOT g_po_header_cache.EXISTS(p_po_header_id) THEN
597 g_po_header_misses := g_po_header_misses + 1;
598
599 SELECT poh.po_header_id
600 , poh.segment1
601 , NVL (poh.user_hold_flag, 'N')
602 , poh.org_id
603 , poh.vendor_id
604 , poh.vendor_site_id
605 INTO g_po_header_cache(p_po_header_id).po_header_id
606 , g_po_header_cache(p_po_header_id).segment1
607 , g_po_header_cache(p_po_header_id).user_hold_flag
608 , g_po_header_cache(p_po_header_id).org_id
609 , g_po_header_cache(p_po_header_id).vendor_id
610 , g_po_header_cache(p_po_header_id).vendor_site_id
611 FROM po_headers_all poh
612 WHERE poh.po_header_id = p_po_header_id;
613 END IF;
614
615 RETURN g_po_header_cache(p_po_header_id);
616 END get_po_header;
617
618 FUNCTION get_po_line
619 ( p_po_line_id IN PO_LINES_ALL.po_line_id%TYPE
620 ) RETURN po_line_cr IS
621 BEGIN
622 g_po_line_calls := g_po_line_calls + 1;
623
624 IF NOT g_po_line_cache.EXISTS(p_po_line_id) THEN
625 g_po_line_misses := g_po_line_misses + 1;
626
627 SELECT pol.po_line_id
628 , pol.po_header_id
629 , pol.line_num
630 , pol.unit_price
631 , pol.matching_basis
632 , pol.purchase_basis
633 , pol.order_type_lookup_code
634 , NVL (pol.start_date, HR_GENERAL.start_of_time)
635 , NVL (pol.expiration_date, HR_GENERAL.end_of_time)
636 , pol.job_id
637 , poll.line_location_id
638 , NVL (poll.approved_flag, 'N')
639 , NVL (poll.cancel_flag, 'N')
640 , NVL (poll.closed_code, 'OPEN')
641 , NVL (poll.qty_rcv_exception_code, 'NONE')
642 , poll.amount + ( poll.amount * NVL (poll.qty_rcv_tolerance, 0) / 100 )
643 , 0
644 , poll.ship_to_organization_id
645 , poll.ship_to_location_id
646 INTO g_po_line_cache(p_po_line_id).po_line_id
647 , g_po_line_cache(p_po_line_id).po_header_id
648 , g_po_line_cache(p_po_line_id).line_num
649 , g_po_line_cache(p_po_line_id).unit_price
650 , g_po_line_cache(p_po_line_id).matching_basis
651 , g_po_line_cache(p_po_line_id).purchase_basis
652 , g_po_line_cache(p_po_line_id).order_type_lookup_code
653 , g_po_line_cache(p_po_line_id).start_date
654 , g_po_line_cache(p_po_line_id).expiration_date
655 , g_po_line_cache(p_po_line_id).job_id
656 , g_po_line_cache(p_po_line_id).line_location_id
657 , g_po_line_cache(p_po_line_id).approved_flag
658 , g_po_line_cache(p_po_line_id).cancel_flag
659 , g_po_line_cache(p_po_line_id).closed_code
660 , g_po_line_cache(p_po_line_id).qty_rcv_exception_code
661 , g_po_line_cache(p_po_line_id).tolerable_amount
662 , g_po_line_cache(p_po_line_id).timecard_amount
663 , g_po_line_cache(p_po_line_id).ship_to_organization_id
664 , g_po_line_cache(p_po_line_id).ship_to_location_id
665 FROM po_lines_all pol
666 , po_line_locations_all poll
667 WHERE pol.po_line_id = p_po_line_id
668 AND poll.po_line_id = pol.po_line_id;
669 END IF;
670
671 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
672 , module => G_LOG_MODULE
673 , message => 'after po_line_id ' ||g_po_line_cache(p_po_line_id).po_line_id||'**'
674 ||'po_header_id '||g_po_line_cache(p_po_line_id).po_header_id||'**'
675 ||'line_num '||g_po_line_cache(p_po_line_id).line_num||'**'
676 ||'unit_price '||g_po_line_cache(p_po_line_id).unit_price||'**'
677 ||'matching_basis '||g_po_line_cache(p_po_line_id).matching_basis||'**'
678 ||'purchase_basis '||g_po_line_cache(p_po_line_id).purchase_basis||'**'
679 ||'order_type_lookup_code '||g_po_line_cache(p_po_line_id).order_type_lookup_code||'**'
680 ||'start_date '||g_po_line_cache(p_po_line_id).start_date||'**'
681 ||'expiration_date '||g_po_line_cache(p_po_line_id).expiration_date||'**'
682 ||'job_id '||g_po_line_cache(p_po_line_id).job_id||'**'
683 ||'line_location_id '||g_po_line_cache(p_po_line_id).line_location_id||'**'
684 ||'approved_flag '||g_po_line_cache(p_po_line_id).approved_flag||'**'
685 ||'cancel_flag '||g_po_line_cache(p_po_line_id).cancel_flag||'**'
686 ||'closed_code '||g_po_line_cache(p_po_line_id).closed_code||'**'
687 ||'qty_rcv_exception_code '||g_po_line_cache(p_po_line_id).qty_rcv_exception_code||'**'
688 ||'tolerable_amount '||g_po_line_cache(p_po_line_id).tolerable_amount||'**'
689 ||'timecard_amount '||g_po_line_cache(p_po_line_id).timecard_amount||'**'
690 ||'ship_to_organization_id '||g_po_line_cache(p_po_line_id).ship_to_organization_id||'**'
691 ||'ship_to_location_id '||g_po_line_cache(p_po_line_id).ship_to_location_id
692 );
693
694 RETURN g_po_line_cache(p_po_line_id);
695 END get_po_line;
696
697 FUNCTION get_po_distribution
698 ( p_po_line_id IN PO_LINES_ALL.po_line_id%TYPE
699 , p_project_id IN PO_DISTRIBUTIONS_ALL.project_id%TYPE
700 , p_task_id IN PO_DISTRIBUTIONS_ALL.task_id%TYPE
701 ) RETURN po_distribution_cr IS
702 l_api_name CONSTANT varchar2(30) := 'get_po_distribution';
703 l_log_head CONSTANT VARCHAR2(100) := G_LOG_MODULE || '.'||l_api_name;
704 BEGIN
705 g_po_distribution_calls := g_po_distribution_calls + 1;
706
707 IF p_project_id IS NULL OR
708 p_task_id IS NULL THEN
709 RETURN NULL;
710 END IF;
711
712 IF NOT g_po_distribution_cache.EXISTS(p_po_line_id) OR
713 g_po_distribution_cache(p_po_line_id).project_id <> p_project_id OR
714 g_po_distribution_cache(p_po_line_id).task_id <> p_task_id THEN
715 g_po_distribution_misses := g_po_distribution_misses + 1;
716
717 g_po_distribution_cache(p_po_line_id).project_id := p_project_id;
718 g_po_distribution_cache(p_po_line_id).task_id := p_task_id;
719
720 -- allocate all the amount to the first distribution that matches
721 SELECT MIN(pod.po_distribution_id)
722 INTO g_po_distribution_cache(p_po_line_id).po_distribution_id
723 FROM po_distributions_all pod
724 WHERE pod.po_line_id = p_po_line_id
725 AND pod.project_id = p_project_id
726 AND pod.task_id = p_task_id;
727
728 -- < Service Procurement ER Start>
729 -- Get the first distribution from Purchase order which has a Dummp Project Associated.
730 -- This is used in the case when user select a Project on the Timecard which doesn't matches
731 -- with the projects in Purchase Order which was selected on Time card.
732 IF g_po_distribution_cache(p_po_line_id).po_distribution_id IS NULL THEN
733 SELECT MIN(psp.po_distribution_id)
734 INTO g_po_distribution_cache(p_po_line_id).po_distribution_id
735 FROM PO_SP_VAL_V psp
736 WHERE psp.po_line_id = p_po_line_id
737 AND psp.project_id IS NOT NULL
738 AND psp.task_id IS NOT NULL
739 AND psp.VALIDATE_PROJECT_FLAG = 'Y';
740 END IF;
741 -- < Service Procurement ER Ends>
742 END IF;
743
744 RETURN g_po_distribution_cache(p_po_line_id);
745 EXCEPTION
746 WHEN OTHERS THEN
747 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
748 , module => l_log_head
749 , message => 'Unexpected exception deriving po_distribution_id (po_line_id=' || p_po_line_id || ', project_id=' || p_project_id || ', task_id=' || p_task_id || '): ' || SQLERRM
750 );
751 RAISE DERIVE_DISTRIBUTION_ID_FAILED;
752 END get_po_distribution;
753
754 FUNCTION get_price_type_lookup
755 ( p_price_type IN PO_PRICE_DIFFERENTIALS.price_type%TYPE
756 ) RETURN fnd_lookups_cr IS
757 l_cache_index BINARY_INTEGER;
758 BEGIN
759 g_price_type_lookup_calls := g_price_type_lookup_calls + 1;
760
761 -- Since 8i does not have support VARCHAR index we need to loop through the table
762 -- which is acceptable for this case because we don't expect many differentials in
763 -- the same timecard
764 -- Most recently added price type is most likely to get looked up so search backwards
765 l_cache_index := g_price_type_lookup_cache.LAST;
766 WHILE l_cache_index IS NOT NULL LOOP
767 IF g_price_type_lookup_cache(l_cache_index).lookup_code = p_price_type THEN
768 EXIT;
769 END IF;
770 l_cache_index := g_price_type_lookup_cache.PRIOR(l_cache_index);
771 END LOOP;
772
773 IF l_cache_index IS NULL THEN
774 g_price_type_lookup_misses := g_price_type_lookup_misses + 1;
775
776 l_cache_index := NVL(g_price_type_lookup_cache.LAST, 0) + 1;
777
778 SELECT p_price_type
779 , meaning
780 INTO g_price_type_lookup_cache(l_cache_index).lookup_code
781 , g_price_type_lookup_cache(l_cache_index).meaning
782 FROM fnd_lookups
783 WHERE lookup_type = 'PRICE DIFFERENTIALS'
784 AND lookup_code = p_price_type;
785 ELSIF l_cache_index <> g_price_type_lookup_cache.LAST THEN
786 -- maintain LRU
787 g_price_type_lookup_cache(g_price_type_lookup_cache.LAST + 1) := g_price_type_lookup_cache(l_cache_index);
788 g_price_type_lookup_cache.DELETE(l_cache_index);
789 l_cache_index := g_price_type_lookup_cache.LAST;
790 END IF;
791
792 RETURN g_price_type_lookup_cache(l_cache_index);
793 END get_price_type_lookup;
794
795 FUNCTION get_price_differentials
796 ( p_po_line_id IN PO_LINES_ALL.po_line_id%TYPE
797 , p_price_type IN PO_PRICE_DIFFERENTIALS.price_type%TYPE
798 ) RETURN price_differentials_cr IS
799 l_cache_index BINARY_INTEGER;
800 BEGIN
801 g_price_differentials_calls := g_price_differentials_calls + 1;
802
803 -- shortcut for standard rate
804 IF p_price_type = 'STANDARD' THEN
805 DECLARE
806 l_standard_rate price_differentials_cr;
807 BEGIN
808 l_standard_rate.entity_id := p_po_line_id;
809 l_standard_rate.price_type := p_price_type;
810 l_standard_rate.enabled_flag := 'Y';
811 l_standard_rate.multiplier := 1.0;
812 l_standard_rate.price := get_po_line(p_po_line_id).unit_price;
813
814 RETURN l_standard_rate;
815 END;
816 END IF;
817
818 l_cache_index := g_price_differentials_cache.LAST;
819 WHILE l_cache_index IS NOT NULL LOOP
820 IF g_price_differentials_cache(l_cache_index).entity_id = p_po_line_id AND
821 g_price_differentials_cache(l_cache_index).price_type = p_price_type
822 THEN
823 EXIT;
824 END IF;
825 l_cache_index := g_price_differentials_cache.PRIOR(l_cache_index);
826 END LOOP;
827
828 IF l_cache_index IS NULL THEN
829 g_price_differentials_misses := g_price_differentials_misses + 1;
830
831 l_cache_index := NVL(g_price_differentials_cache.LAST, 0) + 1;
832
833 SELECT entity_id
834 , price_type
835 , enabled_flag
836 , multiplier
837 INTO g_price_differentials_cache(l_cache_index).entity_id
838 , g_price_differentials_cache(l_cache_index).price_type
839 , g_price_differentials_cache(l_cache_index).enabled_flag
840 , g_price_differentials_cache(l_cache_index).multiplier
841 FROM po_price_differentials
842 WHERE entity_type = 'PO LINE'
843 AND entity_id = p_po_line_id
844 AND price_type = p_price_type;
845
846 g_price_differentials_cache(l_cache_index).price := get_po_line(p_po_line_id).unit_price * g_price_differentials_cache(l_cache_index).multiplier;
847 ELSIF l_cache_index <> g_price_differentials_cache.LAST THEN
848 -- maintain LRU
849 g_price_differentials_cache(g_price_differentials_cache.LAST + 1) := g_price_differentials_cache(l_cache_index);
850 g_price_differentials_cache.DELETE(l_cache_index);
851 l_cache_index := g_price_differentials_cache.LAST;
852 END IF;
853
854 RETURN g_price_differentials_cache(l_cache_index);
855 END get_price_differentials;
856
857 -- Gets assignment for this PO/person effective on a particular date
858 -- Throws a NO DATA FOUND if no such assignment exists
859 FUNCTION get_assignment
860 ( p_po_line_id IN PO_LINES_ALL.po_line_id%TYPE
861 , p_person_id IN PER_ALL_ASSIGNMENTS_F.person_id%TYPE
862 , p_effective_date IN DATE
863 ) RETURN per_all_assignments_cr IS
864 l_sql VARCHAR2(1500);
865 BEGIN
866 g_assignments_calls := g_assignments_calls + 1;
867
868 IF NOT g_assignments_cache.EXISTS(p_po_line_id) OR
869 g_assignments_cache(p_po_line_id).person_id <> p_person_id OR
870 p_effective_date NOT BETWEEN
871 g_assignments_cache(p_po_line_id).effective_start_date AND
872 g_assignments_cache(p_po_line_id).effective_end_date
873 THEN
874 g_assignments_misses := g_assignments_misses + 1;
875
876 -- < Service Procurement ER Start>
877 -- look for assignment from the new PO CWK Association table
878 -- along with with the Assignments in HRMS.
879
880 -- The po info in this table is new in 11.5.10
881 -- so we use dynamic sql to avoid compile-time
882 -- dependencies to the new fields
883 l_sql :=' SELECT effective_start_date , effective_end_date
884 FROM per_all_assignments_f paaf
885 WHERE paaf.po_line_id = :po_line_id
886 AND paaf.person_id = :person_id
887 AND Trunc(:effective_date)
888 BETWEEN Trunc(paaf.effective_start_date)
889 AND Trunc(paaf.effective_end_date)
890 UNION
891 SELECT effective_start_date , effective_end_date
892 FROM per_all_assignments_f paaf,
893 po_cwk_associations pca,
894 po_headers_all ph,
895 po_lines_all pl
896 WHERE pca.po_line_id = :po_line_id
897 AND pca.cwk_person_id = :person_id
898 AND pca.po_line_id = pl.po_line_id
899 AND pca.po_header_id = ph.po_header_id
900 AND pca.cwk_person_id = paaf.person_id
901 AND paaf.job_id = pl.job_id
902 AND ph.vendor_id = paaf.vendor_id
903 AND ph.vendor_site_id = paaf.vendor_site_id
904 AND Trunc(:effective_date)
905 BETWEEN Trunc(paaf.effective_start_date)
906 AND Trunc(paaf.effective_end_date) ';
907
908
909 EXECUTE IMMEDIATE l_sql
910 INTO g_assignments_cache(p_po_line_id).effective_start_date
911 , g_assignments_cache(p_po_line_id).effective_end_date
912 USING p_po_line_id
913 , p_person_id
914 , p_effective_date
915 , p_po_line_id
916 , p_person_id
917 , p_effective_date;
918 -- < Service Procurement ER Ends >
919 END IF;
920
921 RETURN g_assignments_cache(p_po_line_id);
922 END get_assignment;
923
924 FUNCTION get_rcv_transaction
925 ( p_timecard_bb_id IN HXC_TIME_BUILDING_BLOCKS.time_building_block_id%TYPE
926 , p_po_line_id IN PO_LINES_ALL.po_line_id%TYPE
927 ) RETURN rcv_transactions_cr IS
928 l_api_name CONSTANT varchar2(30) := 'get_rcv_transaction';
929 l_log_head CONSTANT VARCHAR2(100) := G_LOG_MODULE || '.'||l_api_name;
930 BEGIN
931 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
932 , module => l_log_head
933 , message => 'Finding Parent Transcations , TimeCard ID: ' || p_timecard_bb_id || ' Po line Id: ' || p_po_line_id
934 );
935 g_rcv_transactions_calls := g_rcv_transactions_calls + 1;
936
937 IF NOT g_rcv_transactions_cache.EXISTS(p_timecard_bb_id) OR
938 g_rcv_transactions_cache(p_timecard_bb_id).po_line_id <> p_po_line_id THEN
939 g_rcv_transactions_misses := g_rcv_transactions_misses + 1;
940
941 g_rcv_transactions_cache(p_timecard_bb_id).po_line_id := p_po_line_id;
942 --Bug 5217532 START
943 --Break the old SQL into 2 different SQL to avoid Merge Join Catesian
944 BEGIN
945 SELECT receive.transaction_id
946 INTO g_rcv_transactions_cache(p_timecard_bb_id).receive_transaction_id
947 FROM rcv_transactions receive
948 WHERE receive.timecard_id = p_timecard_bb_id
949 AND receive.po_line_id = p_po_line_id
950 AND receive.transaction_type = 'RECEIVE';
951 EXCEPTION
952 WHEN NO_DATA_FOUND THEN
953 g_rcv_transactions_cache(p_timecard_bb_id).receive_transaction_id := NULL;
954 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
955 , module => l_log_head
956 , message => 'Unable to find Parent Transcations ,TimeCard ID: ' || p_timecard_bb_id || ' po_line_id: ' || p_po_line_id
957 );
958
959 END;
960
961 BEGIN
962 SELECT deliver.transaction_id
963 INTO g_rcv_transactions_cache(p_timecard_bb_id).deliver_transaction_id
964 FROM rcv_transactions deliver
965 WHERE deliver.timecard_id = p_timecard_bb_id
966 AND deliver.po_line_id = p_po_line_id
967 AND deliver.transaction_type = 'DELIVER';
968 EXCEPTION
969 WHEN NO_DATA_FOUND THEN
970 g_rcv_transactions_cache(p_timecard_bb_id).deliver_transaction_id := NULL;
971 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
972 , module => l_log_head
973 , message => 'Unable to find Parent Transcations ,TimeCard ID: ' || p_timecard_bb_id || ' po_line_id: ' || p_po_line_id
974 );
975
976 END;
977 --Bug 5217532 END
978 END IF;
979
980 RETURN g_rcv_transactions_cache(p_timecard_bb_id);
981 END get_rcv_transaction;
982
983 FUNCTION get_rcv_transaction
984 ( p_timecard_bb_id IN HXC_TIME_BUILDING_BLOCKS.time_building_block_id%TYPE
985 , p_po_line_id IN PO_LINES_ALL.po_line_id%TYPE
986 , p_po_distribution_id IN PO_DISTRIBUTIONS_ALL.po_distribution_id%TYPE
987 ) RETURN rcv_transactions_cr IS
988 l_api_name CONSTANT varchar2(30) := 'get_rcv_transaction';
989 l_log_head CONSTANT VARCHAR2(100) := G_LOG_MODULE || '.'||l_api_name;
990 BEGIN
991 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
992 , module => l_log_head
993 , message => 'Finding Parent Transcations , TimeCard ID: ' || p_timecard_bb_id || ' po_line_id: ' || p_po_line_id || ' po_distribution_id: ' || p_po_distribution_id
994 );
995 IF p_po_distribution_id IS NULL THEN
996 RETURN get_rcv_transaction( p_timecard_bb_id, p_po_line_id );
997 END IF;
998
999 g_rcv_transactions_calls := g_rcv_transactions_calls + 1;
1000
1001 IF NOT g_rcv_transactions_cache.EXISTS(p_timecard_bb_id) OR
1002 g_rcv_transactions_cache(p_timecard_bb_id).po_distribution_id <> p_po_distribution_id THEN
1003 g_rcv_transactions_misses := g_rcv_transactions_misses + 1;
1004
1005 g_rcv_transactions_cache(p_timecard_bb_id).po_distribution_id := p_po_distribution_id;
1006 --Bug 5217532 START
1007 --Break the old SQL into 2 different SQL to avoid Merge Join Catesian
1008 BEGIN
1009 SELECT receive.transaction_id
1010 INTO g_rcv_transactions_cache(p_timecard_bb_id).receive_transaction_id
1011 FROM rcv_transactions receive
1012 WHERE receive.timecard_id = p_timecard_bb_id
1013 AND receive.po_distribution_id = p_po_distribution_id
1014 AND receive.transaction_type = 'RECEIVE';
1015 EXCEPTION
1016 WHEN NO_DATA_FOUND THEN
1017 g_rcv_transactions_cache(p_timecard_bb_id).receive_transaction_id := NULL;
1018 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
1019 , module => l_log_head
1020 , message => 'Unable to find Parent Transcations ,TimeCard ID: ' || p_timecard_bb_id || ' po_line_id: ' || p_po_line_id || ' po_distribution_id: ' || p_po_distribution_id
1021 );
1022 END;
1023
1024 BEGIN
1025 SELECT deliver.transaction_id
1026 INTO g_rcv_transactions_cache(p_timecard_bb_id).deliver_transaction_id
1027 FROM rcv_transactions deliver
1028 WHERE deliver.timecard_id = p_timecard_bb_id
1029 AND deliver.po_distribution_id = p_po_distribution_id
1030 AND deliver.transaction_type = 'DELIVER';
1031 EXCEPTION
1032 WHEN NO_DATA_FOUND THEN
1033 g_rcv_transactions_cache(p_timecard_bb_id).deliver_transaction_id := NULL;
1034 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
1035 , module => l_log_head
1036 , message => 'Unable to find Parent Transcations ,TimeCard ID: ' || p_timecard_bb_id || ' po_line_id: ' || p_po_line_id || ' po_distribution_id: ' || p_po_distribution_id
1037 );
1038 END;
1039 --Bug 5217532 END
1040 END IF;
1041
1042 RETURN g_rcv_transactions_cache(p_timecard_bb_id);
1043 END get_rcv_transaction;
1044
1045 -- cached wrapper for build_block
1046 FUNCTION build_block
1047 ( p_bb_id IN HXC_TIME_BUILDING_BLOCKS.time_building_block_id%TYPE
1048 , p_bb_ovn IN HXC_TIME_BUILDING_BLOCKS.object_version_number%TYPE
1049 ) RETURN HXC_USER_TYPE_DEFINITION_GRP.building_block_info IS
1050 BEGIN
1051 g_build_block_calls := g_build_block_calls + 1;
1052
1053 IF NOT g_build_block_cache.EXISTS(p_bb_id) OR
1054 g_build_block_cache(p_bb_id).object_version_number <> p_bb_ovn
1055 THEN
1056 g_build_block_misses := g_build_block_misses + 1;
1057 g_build_block_cache(p_bb_id) := HXC_INTEGRATION_LAYER_V1_GRP.build_block(p_bb_id, p_bb_ovn);
1058 END IF;
1059
1060 RETURN g_build_block_cache(p_bb_id);
1061 END build_block;
1062
1063 -- Cached wrapper for build_attribute
1064 -- Returns the first record in the table returned by OTL
1065 -- since 8i does not support table of tables
1066 FUNCTION build_attribute
1067 ( p_bb_id IN HXC_TIME_BUILDING_BLOCKS.time_building_block_id%TYPE
1068 , p_bb_ovn IN HXC_TIME_BUILDING_BLOCKS.object_version_number%TYPE
1069 , p_attribute_category IN HXC_TIME_ATTRIBUTES.attribute_category%TYPE
1070 ) RETURN HXC_USER_TYPE_DEFINITION_GRP.attribute_info IS
1071 BEGIN
1072 g_build_attribute_calls := g_build_attribute_calls + 1;
1073
1074 IF NOT g_build_attribute_cache.EXISTS(p_bb_id) OR
1075 g_build_attribute_cache(p_bb_id).object_version_number <> p_bb_ovn OR
1076 g_build_attribute_cache(p_bb_id).attribute_category <> p_attribute_category
1077 THEN
1078 g_build_attribute_misses := g_build_attribute_misses + 1;
1079 g_build_attribute_cache(p_bb_id) := HXC_INTEGRATION_LAYER_V1_GRP.build_attribute( p_bb_id, p_bb_ovn, p_attribute_category )(1);
1080 END IF;
1081
1082 RETURN g_build_attribute_cache(p_bb_id);
1083 END build_attribute;
1084
1085 -- Procedure to skip over related attributes and old blocks
1086 -- Used when skipping over detail blocks
1087 PROCEDURE skip_block
1088 ( p_blocks IN OUT NOCOPY HXC_USER_TYPE_DEFINITION_GRP.t_building_blocks
1089 , p_blk_idx IN OUT NOCOPY BINARY_INTEGER
1090 , p_old_blocks IN OUT NOCOPY HXC_USER_TYPE_DEFINITION_GRP.t_building_blocks
1091 , p_old_blk_idx IN OUT NOCOPY BINARY_INTEGER
1092 , p_attributes IN OUT NOCOPY HXC_USER_TYPE_DEFINITION_GRP.t_time_attribute
1093 , p_att_idx IN OUT NOCOPY BINARY_INTEGER
1094 , p_old_attributes IN OUT NOCOPY HXC_USER_TYPE_DEFINITION_GRP.t_time_attribute
1095 , p_old_att_idx IN OUT NOCOPY BINARY_INTEGER
1096 ) IS
1097 BEGIN
1098 -- skip the attributes for this block
1099 WHILE p_att_idx <= p_attributes.LAST AND p_attributes(p_att_idx).bb_id = p_blocks(p_blk_idx).bb_id LOOP
1100 p_att_idx := p_att_idx + 1;
1101 END LOOP;
1102
1103 -- skip the old block for this block
1104 IF p_blocks(p_blk_idx).changed = 'Y' THEN
1105 -- skip the old attributes as well
1106 WHILE p_old_att_idx <= p_old_attributes.LAST AND p_old_attributes(p_old_att_idx).bb_id = p_old_blocks(p_old_blk_idx).bb_id LOOP
1107 p_old_att_idx := p_old_att_idx + 1;
1108 END LOOP;
1109
1110 p_old_blk_idx := p_old_blk_idx + 1;
1111 END IF;
1112
1113 p_blk_idx := p_blk_idx + 1;
1114 END skip_block;
1115
1116 PROCEDURE Set_Attribute
1117 ( p_attributes IN OUT NOCOPY TimecardAttributesRec
1118 , p_attribute_name IN HXC_MAPPING_COMPONENTS.field_name%TYPE
1119 , p_attribute_value IN HXC_TIME_ATTRIBUTES.attribute1%TYPE
1120 , p_attribute_id IN HXC_TIME_ATTRIBUTES.time_attribute_id%TYPE DEFAULT NULL
1121 ) IS
1122 l_attribute_name HXC_MAPPING_COMPONENTS.field_name%TYPE;
1123 l_api_name CONSTANT varchar2(30) := 'Set_Attribute';
1124 l_log_head CONSTANT VARCHAR2(100) := G_LOG_MODULE || '.'||l_api_name;
1125 BEGIN
1126 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
1127 , module => l_log_head
1128 , message => 'Setting attribute ' || p_attribute_name || ' with value ' || p_attribute_value || ' (p_attribute_id=' || p_attribute_id || ')'
1129 );
1130
1131 l_attribute_name := upper(p_attribute_name);
1132
1133 IF l_attribute_name = 'ORG_ID' THEN
1134 p_attributes.org_id := p_attribute_value;
1135 ELSIF l_attribute_name = 'PO NUMBER' THEN
1136 p_attributes.po_number := p_attribute_value;
1137 ELSIF l_attribute_name = 'PO HEADER ID' THEN
1138 p_attributes.po_header_id := p_attribute_value;
1139 ELSIF l_attribute_name = 'PO LINE NUMBER' THEN
1140 p_attributes.po_line := p_attribute_value;
1141 ELSIF l_attribute_name = 'PO LINE ID' THEN
1142 p_attributes.po_line_id := p_attribute_value;
1143 p_attributes.time_attribute_id := p_attribute_id; -- can be captured with any purchasing attribute
1144 ELSIF l_attribute_name = 'PO PRICE TYPE' THEN
1145 p_attributes.po_price_type := p_attribute_value;
1146 ELSIF l_attribute_name = 'PO PRICE TYPE DISPLAY' THEN
1147 p_attributes.po_price_type_display := p_attribute_value;
1148 ELSIF l_attribute_name = 'PO BILLABLE AMOUNT' THEN
1149 p_attributes.po_billable_amount := p_attribute_value;
1150 ELSIF l_attribute_name = 'PO RECEIPT DATE' THEN
1151 p_attributes.po_receipt_date := FND_DATE.Canonical_to_Date(p_attribute_value);
1152 ELSIF l_attribute_name = 'PROJECT_ID' THEN
1153 p_attributes.project_id := p_attribute_value;
1154 ELSIF l_attribute_name = 'TASK_ID' THEN
1155 p_attributes.task_id := p_attribute_value;
1156 END IF;
1157 END Set_Attribute;
1158
1159 -- This procedure is called during update and validate. In these processes,
1160 -- the attributes are in totally random order, so we use the timecard_id
1161 -- as an index into the attributes table.
1162 PROCEDURE Sort_Attributes
1163 ( p_all_attributes IN OUT NOCOPY TimecardAttributesTbl
1164 , p_raw_attributes IN HXC_USER_TYPE_DEFINITION_GRP.app_attributes_info
1165 ) IS
1166 l_bb_id HXC_TIME_BUILDING_BLOCKS.time_building_block_id%TYPE;
1167 l_api_name CONSTANT varchar2(30) := 'Sort_Attributes';
1168 l_log_head CONSTANT VARCHAR2(100) := G_LOG_MODULE || '.'||l_api_name;
1169 BEGIN
1170 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
1171 , module => l_log_head
1172 , message => 'Begin Sort_Attributes'
1173 );
1174
1175 -- loop through all the attributes to sort them out
1176 FOR att_idx IN 1..p_raw_attributes.COUNT LOOP
1177 l_bb_id := p_raw_attributes(att_idx).building_block_id;
1178
1179 IF NOT p_all_attributes.EXISTS(l_bb_id) THEN
1180 p_all_attributes(l_bb_id).detail_bb_id := l_bb_id;
1181 END IF;
1182
1183 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
1184 , module => l_log_head
1185 , message => 'Capturing attribute_id=' || p_raw_attributes(att_idx).time_attribute_id || ', attribute_name=' || p_raw_attributes(att_idx).attribute_name
1186 );
1187
1188 Set_Attribute( p_all_attributes(l_bb_id)
1189 , p_raw_attributes(att_idx).attribute_name
1190 , p_raw_attributes(att_idx).attribute_value
1191 , p_raw_attributes(att_idx).time_attribute_id
1192 );
1193 END LOOP;
1194
1195 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
1196 , module => l_log_head
1197 , message => 'End Sort_Attributes'
1198 );
1199 END Sort_Attributes;
1200
1201 PROCEDURE Update_Attributes
1202 ( p_attributes IN OUT NOCOPY TimecardAttributesRec
1203 , p_messages IN OUT NOCOPY HXC_USER_TYPE_DEFINITION_GRP.message_table
1204 ) IS
1205 l_api_name CONSTANT varchar2(30) := 'Update_Attributes';
1206 l_log_head CONSTANT VARCHAR2(100) := G_LOG_MODULE || '.'||l_api_name;
1207 po_update_flag VARCHAR2(1) :='N'; --bug 6998132
1208 BEGIN
1209 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
1210 , module => l_log_head
1211 , message => 'Begin Update_Attributes'
1212 );
1213
1214 -- Derive id's
1215
1216 -- The layout only deposits id values, so we must always derive the display
1217 -- values from the id values, and null the outdated display value if
1218 -- the id is null.
1219 --bug 6998132 start
1220
1221 IF p_attributes.po_number IS NOT NULL THEN
1222
1223 BEGIN
1224 SELECT 'Y'
1225 INTO po_update_flag
1226 FROM dual
1227 WHERE EXISTS (SELECT segment1
1228 FROM po_headers_all
1229 WHERE segment1=p_attributes.po_number
1230 AND org_id=hxc_timecard_properties.setup_mo_global_params(fnd_global.employee_id)
1231 AND Nvl(closed_code,'OPEN') <> 'FINALLY CLOSED'
1232 AND Nvl(user_hold_flag,'N') <> 'Y'
1233 );
1234 EXCEPTION
1235 WHEN No_Data_Found THEN
1236 HXC_INTEGRATION_LAYER_V1_GRP.add_error_to_table( p_message_table => p_messages
1237 , p_message_name => 'HXC_RET_UNEXPECTED_ERROR'
1238 , p_message_token => 'ERR&' || 'Existing PO in uneditable state -- Finally Closed/On Hold '
1239 , p_message_level => HXC_USER_TYPE_DEFINITION_GRP.c_error
1240 , p_message_field => 'PO Header Id'
1241 , p_application_short_name => 'HXC'
1242 , p_timecard_bb_id => NULL
1243 , p_time_attribute_id => p_attributes.time_attribute_id
1244 );
1245 END;
1246
1247 END IF;
1248
1249 -- bug 6998132 end
1250
1251 -- derive po_number
1252 IF p_attributes.po_header_id IS NULL THEN
1253 p_attributes.po_number := NULL;
1254 ELSE
1255 BEGIN
1256 p_attributes.po_number := get_po_header(p_attributes.po_header_id).segment1;
1257 EXCEPTION
1258 WHEN OTHERS THEN
1259 -- unexpected exception deriving po header id
1260 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
1261 , module => l_log_head
1262 , message => 'Unexpected exception deriving PO Header Id (PO Number=' || p_attributes.po_number || '): ' || SQLERRM
1263 );
1264 END;
1265 END IF;
1266
1267 -- derive po_line
1268 IF p_attributes.po_line_id IS NULL THEN
1269 p_attributes.po_line := NULL;
1270 ELSE
1271 BEGIN
1272 p_attributes.po_line := get_po_line(p_attributes.po_line_id).line_num;
1273 EXCEPTION
1274 WHEN OTHERS THEN
1275 -- unexpected exception deriving po line id
1276 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
1277 , module => l_log_head
1278 , message => 'Unexpected exception deriving PO Line Number (PO Header Id=' || p_attributes.po_header_id || ', PO Line Id=' || p_attributes.po_line_id || '): ' || SQLERRM
1279 );
1280 END;
1281 END IF;
1282
1283 -- derive price_type_display
1284 -- not cached because 8i doesn't support non-integer indexing
1285 -- should cache when moving to 9i
1286 IF p_attributes.po_price_type IS NULL THEN
1287 p_attributes.po_price_type_display := NULL;
1288 ELSE
1289 BEGIN
1290 p_attributes.po_price_type_display := get_price_type_lookup(p_attributes.po_price_type).meaning;
1291 EXCEPTION
1292 WHEN OTHERS THEN
1293 -- unexpected exception deriving price type
1294 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
1295 , module => l_log_head
1296 , message => 'Unexpected exception deriving PO Price Type Display (PO Price Type=' || p_attributes.po_price_type || '): ' || SQLERRM
1297 );
1298 END;
1299 END IF;
1300
1301 -- calculated fields
1302
1303 -- PO Billable Amount
1304 IF p_attributes.detail_measure IS NOT NULL AND
1305 p_attributes.po_line_id IS NOT NULL AND
1306 p_attributes.po_price_type IS NOT NULL
1307 THEN
1308 BEGIN
1309 p_attributes.po_billable_amount := p_attributes.detail_measure * get_price_differentials(p_attributes.po_line_id, p_attributes.po_price_type).price;
1310 EXCEPTION
1311 WHEN OTHERS THEN
1312 -- unexpected exception deriving PO Billable Amount
1313 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
1314 , module => l_log_head
1315 , message => 'Unexpected exception deriving PO Billable Amount (PO Price Type=' || p_attributes.po_price_type || ', PO Line Id=' || p_attributes.po_line_id || '): ' || SQLERRM
1316 );
1317 END;
1318 END IF;
1319
1320 -- PO Receipt Date
1321 -- This field is no longer set during deposit, because the correct transaction date
1322 -- can be determined much more easily at retrieval time, when it is clear whether
1323 -- the receiving transaction type is RECEIVE or CORRECT
1324 p_attributes.po_receipt_date := SYSDATE;
1325
1326 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
1327 , module => l_log_head
1328 , message => 'End Update_Attributes'
1329 );
1330 END Update_Attributes;
1331
1332 PROCEDURE Validate_Attributes
1333 ( p_attributes IN OUT NOCOPY TimecardAttributesRec
1334 , p_messages IN OUT NOCOPY HXC_USER_TYPE_DEFINITION_GRP.message_table
1335 ) IS
1336 l_api_name CONSTANT varchar2(30) := 'Validate_Attributes';
1337 l_log_head CONSTANT VARCHAR2(100) := G_LOG_MODULE || '.'||l_api_name;
1338 l_count NUMBER;
1339
1340 WRONG_BB_TYPE EXCEPTION;
1341 WRONG_BB_UOM EXCEPTION;
1342 INCOMPLETE_PO_INFO EXCEPTION;
1343 NO_PO_INFO EXCEPTION;
1344 NO_PO_LINE_INFO EXCEPTION;
1345 NO_AMT_INFO EXCEPTION;
1346 NO_RATE_INFO EXCEPTION;
1347 NEGATIVE_HOURS EXCEPTION;
1348 INVALID_PO EXCEPTION;
1349 INVALID_PO_EDIT EXCEPTION;
1350 INVALID_PO_LINE EXCEPTION;
1351 INVALID_PO_LINE_EDIT EXCEPTION;
1352 INVALID_RATE_TYPE EXCEPTION;
1353 INVALID_ASSIGNMENT EXCEPTION;
1354 BB_DATE_OUT_OF_ASG_PERIOD EXCEPTION;
1355 BB_DATE_OUT_OF_PO_PERIOD EXCEPTION;
1356 BEGIN
1357 -- initialize the validation status to error so we can short-circuit
1358 -- the procedure in case of error
1359 p_attributes.validation_status := 'ERROR';
1360
1361 -- validate that the timecard is the correct type
1362 IF p_attributes.detail_type <> 'MEASURE' THEN
1363 RAISE WRONG_BB_TYPE;
1364 END IF;
1365
1366 IF p_attributes.detail_uom <> 'HOURS' THEN
1367 RAISE WRONG_BB_UOM;
1368 END IF;
1369
1370 IF p_attributes.detail_measure < 0 THEN
1371 RAISE NEGATIVE_HOURS;
1372 END IF;
1373
1374 -- validate that all relevant attributes are populated
1375 IF p_attributes.po_number IS NULL AND
1376 p_attributes.po_header_id IS NULL AND
1377 p_attributes.po_line IS NULL AND
1378 p_attributes.po_line_id IS NULL AND
1379 p_attributes.po_price_type_display IS NULL AND
1380 p_attributes.po_price_type IS NULL THEN
1381 -- User didn't enter any attribute
1382 -- This is a special case where OTL does not have a Java object to attach
1383 -- an inline message to the attribute, so we attach a special message to
1384 -- every detail block in the row
1385 RAISE INCOMPLETE_PO_INFO;
1386 END IF;
1387
1388 IF p_attributes.po_number IS NULL OR
1389 p_attributes.po_header_id IS NULL THEN
1390 RAISE NO_PO_INFO;
1391 END IF;
1392
1393 IF p_attributes.po_line IS NULL OR
1394 p_attributes.po_line_id IS NULL THEN
1395 RAISE NO_PO_LINE_INFO;
1396 END IF;
1397
1398 IF p_attributes.po_price_type_display IS NULL OR
1399 p_attributes.po_price_type IS NULL THEN
1400 RAISE NO_RATE_INFO;
1401 END IF;
1402
1403 IF p_attributes.po_billable_amount IS NULL THEN
1404 RAISE NO_AMT_INFO;
1405 END IF;
1406
1407 -- we don't check for receipt date because it will be calculated during retrieval
1408
1409 -- validate that the PO is a valid, open PO
1410 DECLARE
1411 -- PO statuses
1412 l_include_closed_po fnd_profile_option_values.profile_option_value%TYPE;
1413
1414 -- PO dates
1415 pol_start_date DATE;
1416 pol_end_date DATE;
1417 BEGIN
1418 -- Capture all the flags so we can print log them when the PO is invalid, to aid debugging
1419 -- We don't need to check the flags at Shipment level because there is only going to be 1
1420 -- shipment for the line, so the status will bubble up to the line level.
1421 l_include_closed_po := NVL (FND_PROFILE.value('RCV_CLOSED_PO_DEFAULT_OPTION'), 'N');
1422
1423 /*bug 6902391 Changing to single org as in 11.5.10*/
1424
1425 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
1426 , module => l_log_head
1427 , message => 'TimeCard day_start_time ' || p_attributes.day_start_time || 'Timecard resource_id = '||p_attributes.resource_id
1428 );
1429
1430 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
1431 , module => l_log_head
1432 , message => 'hr_organization_api.get_operating_unit = ' || hxc_timecard_properties.setup_mo_global_params(fnd_global.employee_id)
1433 );
1434
1435
1436 IF get_po_header(p_attributes.po_header_id).user_hold_flag <> 'N' OR
1437 get_po_header(p_attributes.po_header_id).org_id <> hxc_timecard_properties.setup_mo_global_params(fnd_global.employee_id)
1438 -- Condition removed as not required. After R12 MOAC. User can use Purchase
1439 -- Order Created in other Operating Unit.
1440 -- get_po_header(p_attributes.po_header_id).org_id <> FND_GLOBAL.org_id
1441 THEN
1442 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_EXCEPTION
1443 , module => l_log_head
1444 , message => 'PO is invalid: ' || '*'
1445 || get_po_header(p_attributes.po_header_id).user_hold_flag || '*'
1446 );
1447
1448 IF p_attributes.old_block = 'Y' THEN
1449 RAISE INVALID_PO_EDIT;
1450 ELSE
1451 RAISE INVALID_PO;
1452 END IF;
1453 END IF;
1454
1455 IF get_po_line(p_attributes.po_line_id).matching_basis <> 'AMOUNT' OR
1456 get_po_line(p_attributes.po_line_id).purchase_basis <> 'TEMP LABOR' OR
1457 get_po_line(p_attributes.po_line_id).order_type_lookup_code <> 'RATE' OR
1458 get_po_line(p_attributes.po_line_id).approved_flag <> 'Y' OR
1459 get_po_line(p_attributes.po_line_id).cancel_flag <> 'N' OR
1460 get_po_line(p_attributes.po_line_id).closed_code = 'FINALLY CLOSED' OR
1461 (l_include_closed_po <> 'Y' AND get_po_line(p_attributes.po_line_id).closed_code IN ('CLOSED','CLOSED FOR RECEIVING'))
1462 THEN
1463 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_EXCEPTION
1464 , module => l_log_head
1465 , message => 'Line is invalid: ' || '*'
1466 || get_po_line(p_attributes.po_line_id).matching_basis || '*'
1467 || get_po_line(p_attributes.po_line_id).purchase_basis || '*'
1468 || get_po_line(p_attributes.po_line_id).order_type_lookup_code || '*'
1469 || get_po_line(p_attributes.po_line_id).approved_flag || '*'
1470 || get_po_line(p_attributes.po_line_id).cancel_flag || '*'
1471 || get_po_line(p_attributes.po_line_id).closed_code || '*'
1472 || l_include_closed_po || '*'
1473 );
1474
1475 IF p_attributes.old_block = 'Y' THEN
1476 RAISE INVALID_PO_LINE_EDIT;
1477 ELSE
1478 RAISE INVALID_PO_LINE;
1479 END IF;
1480 END IF;
1481
1482 IF p_attributes.old_block = 'N' AND
1483 NOT p_attributes.day_start_time BETWEEN get_po_line(p_attributes.po_line_id).start_date AND get_po_line(p_attributes.po_line_id).expiration_date
1484 THEN
1485 RAISE BB_DATE_OUT_OF_PO_PERIOD;
1486 END IF;
1487 EXCEPTION
1488 WHEN INVALID_PO THEN
1489 -- invalid PO information
1490 /** Bug:5559915
1491 * Call to this procedure Validate_Attributes is done in loop from
1492 * Validate_Timecard() for each Time card entry of same Time card.
1493 * PO and PO line of the Time card entries will be same. No need to
1494 * log the same PO and PO line error message again for every
1495 * Time card entry in the Time Card.
1496 * So, before logging the PO and PO line related error message
1497 * checking whether error message is already logged or not.
1498 */
1499 IF g_error_raised_flag = 0 THEN--Bug:5559915
1500 HXC_INTEGRATION_LAYER_V1_GRP.add_error_to_table( p_message_table => p_messages
1501 , p_message_name => 'RCV_OTL_INVALID_VALUE'
1502 , p_message_token => 'INVALID_VALUE&' || 'PO'
1503 , p_message_level => HXC_USER_TYPE_DEFINITION_GRP.c_error
1504 , p_message_field => 'PO Header Id'
1505 , p_application_short_name => 'PO'
1506 , p_timecard_bb_id => NULL
1507 , p_time_attribute_id => p_attributes.time_attribute_id
1508 );
1509 g_error_raised_flag := 1;
1510 END IF;
1511 RETURN;
1512 WHEN INVALID_PO_EDIT THEN
1513 -- invalid PO information on edit timecard
1514 IF g_error_raised_flag = 0 THEN--Bug:5559915
1515 HXC_INTEGRATION_LAYER_V1_GRP.add_error_to_table( p_message_table => p_messages
1516 , p_message_name => 'RCV_OTL_UPDATE_INVALID_PO'
1517 , p_message_token => NULL
1518 , p_message_level => HXC_USER_TYPE_DEFINITION_GRP.c_error
1519 , p_message_field => 'PO Header Id'
1520 , p_application_short_name => 'PO'
1521 , p_timecard_bb_id => NULL
1522 , p_time_attribute_id => p_attributes.time_attribute_id
1523 );
1524 g_error_raised_flag := 1;
1525 END IF;
1526 RETURN;
1527 WHEN INVALID_PO_LINE THEN
1528 -- invalid PO information
1529 IF g_error_raised_flag = 0 THEN--Bug:5559915
1530 HXC_INTEGRATION_LAYER_V1_GRP.add_error_to_table( p_message_table => p_messages
1531 , p_message_name => 'RCV_OTL_UPDATE_INVALID_PO'
1532 , p_message_token => NULL
1533 , p_message_level => HXC_USER_TYPE_DEFINITION_GRP.c_error
1534 , p_message_field => 'PO Line Id'
1535 , p_application_short_name => 'PO'
1536 , p_timecard_bb_id => NULL
1537 , p_time_attribute_id => p_attributes.time_attribute_id
1538 );
1539 g_error_raised_flag := 1;
1540 END IF;
1541 RETURN;
1542 WHEN INVALID_PO_LINE_EDIT THEN
1543 -- invalid PO information on edit timecard
1544 IF g_error_raised_flag = 0 THEN--Bug:5559915
1545 HXC_INTEGRATION_LAYER_V1_GRP.add_error_to_table( p_message_table => p_messages
1546 , p_message_name => 'RCV_OTL_UPDATE_INVALID_PO'
1547 , p_message_token => NULL
1548 , p_message_level => HXC_USER_TYPE_DEFINITION_GRP.c_error
1549 , p_message_field => 'PO Line Id'
1550 , p_application_short_name => 'PO'
1551 , p_timecard_bb_id => NULL
1552 , p_time_attribute_id => p_attributes.time_attribute_id
1553 );
1554 g_error_raised_flag := 1;
1555 END IF;
1556 RETURN;
1557 WHEN BB_DATE_OUT_OF_PO_PERIOD THEN
1558 -- tried to record time outside of PO period
1559 HXC_INTEGRATION_LAYER_V1_GRP.add_error_to_table( p_message_table => p_messages
1560 , p_message_name => 'RCV_OTL_OUT_OF_PO_PER'
1561 , p_message_token => 'BB_DATE&' || p_attributes.day_start_time || '&' || 'PARAMS&' || 'PO Start Date=' || pol_start_date || ', PO Expiration Date=' || pol_end_date
1562 , p_message_level => HXC_USER_TYPE_DEFINITION_GRP.c_error
1563 , p_message_field => 'PO Header Id'
1564 , p_application_short_name => 'PO'
1565 , p_timecard_bb_id => NULL
1566 , p_time_attribute_id => p_attributes.time_attribute_id
1567 );
1568 WHEN OTHERS THEN
1569 -- exception while trying to validate PO information
1570 HXC_INTEGRATION_LAYER_V1_GRP.add_error_to_table( p_message_table => p_messages
1571 , p_message_name => 'HXC_RET_UNEXPECTED_ERROR'
1572 , p_message_token => 'ERR&' || 'validating PO information: ' || SQLERRM
1573 , p_message_level => HXC_USER_TYPE_DEFINITION_GRP.c_error
1574 , p_message_field => 'PO Header Id'
1575 , p_application_short_name => 'HXC'
1576 , p_timecard_bb_id => NULL
1577 , p_time_attribute_id => p_attributes.time_attribute_id
1578 );
1579
1580 -- cannot proceed without valid PO info
1581 RETURN;
1582 END;
1583
1584 -- validate that the Rate Type is valid for this PO line
1585 BEGIN
1586 IF p_attributes.old_block = 'N' AND
1587 p_attributes.po_price_type <> 'STANDARD'
1588 THEN
1589 IF get_price_differentials(p_attributes.po_line_id, p_attributes.po_price_type).enabled_flag <> 'Y' THEN
1590 RAISE INVALID_RATE_TYPE;
1591 END IF;
1592 END IF;
1593 EXCEPTION
1594 WHEN NO_DATA_FOUND THEN
1595 -- the price differential does not even exist in the table
1596 RAISE INVALID_RATE_TYPE;
1597 WHEN INVALID_RATE_TYPE THEN
1598 -- the price differential exists but is not enabled
1599 RAISE;
1600 WHEN OTHERS THEN
1601 HXC_INTEGRATION_LAYER_V1_GRP.add_error_to_table( p_message_table => p_messages
1602 , p_message_name => 'HXC_RET_UNEXPECTED_ERROR'
1603 , p_message_token => 'ERR&' || 'validating PO Price Type information: ' || SQLERRM
1604 , p_message_level => HXC_USER_TYPE_DEFINITION_GRP.c_error
1605 , p_message_field => 'PO Price Type'
1606 , p_application_short_name => 'HXC'
1607 , p_timecard_bb_id => NULL
1608 , p_time_attribute_id => p_attributes.time_attribute_id
1609 );
1610 RETURN;
1611 END;
1612
1613 -- validate that work is done within assignment period
1614 DECLARE
1615 l_assignment per_all_assignments_cr;
1616 BEGIN
1617 l_assignment := get_assignment( p_attributes.po_line_id
1618 , p_attributes.resource_id
1619 , p_attributes.day_start_time
1620 );
1621 EXCEPTION
1622 WHEN NO_DATA_FOUND THEN
1623 -- tried to record time outside of assignment period
1624 HXC_INTEGRATION_LAYER_V1_GRP.add_error_to_table( p_message_table => p_messages
1625 , p_message_name => 'RCV_OTL_OUT_OF_ASG_PER'
1626 , p_message_token => 'BB_DATE&' || p_attributes.day_start_time || '&' || 'PARAMS&' || ' '
1627 , p_message_level => HXC_USER_TYPE_DEFINITION_GRP.c_error
1628 , p_message_field => 'PO Header Id'
1629 , p_application_short_name => 'PO'
1630 , p_timecard_bb_id => NULL
1631 , p_time_attribute_id => p_attributes.time_attribute_id
1632 );
1633 WHEN OTHERS THEN
1634 -- exception while trying to validate assignment period
1635 HXC_INTEGRATION_LAYER_V1_GRP.add_error_to_table( p_message_table => p_messages
1636 , p_message_name => 'HXC_RET_UNEXPECTED_ERROR'
1637 , p_message_token => 'ERR&' || 'validating against assignment period: ' || SQLERRM
1638 , p_message_level => HXC_USER_TYPE_DEFINITION_GRP.c_error
1639 , p_message_field => 'PO Header Id'
1640 , p_application_short_name => 'HXC'
1641 , p_timecard_bb_id => NULL
1642 , p_time_attribute_id => p_attributes.time_attribute_id
1643 );
1644 RETURN;
1645 END;
1646
1647 -- maintain the po line amounts table
1648 BEGIN
1649 -- The check_mappingvalue_sum function that we use to calculate
1650 -- total timecarded amount only counts the active block
1651 -- and we set the parameters so it only counts SUBMITTED blocks
1652 -- So we only count an old block if it is SUBMITTED and active
1653 IF p_attributes.old_block = 'N' OR
1654 ( p_attributes.timecard_approval_status = 'SUBMITTED' AND
1655 p_attributes.detail_date_to = HR_GENERAL.end_of_time )
1656 THEN
1657 -- assumption is that this entry exists in the cache
1658 -- be careful if moving this block of code
1659 g_po_line_cache(p_attributes.po_line_id).timecard_amount := get_po_line(p_attributes.po_line_id).timecard_amount + p_attributes.po_billable_amount;
1660 g_po_line_cache(p_attributes.po_line_id).time_attribute_id := NVL(get_po_line(p_attributes.po_line_id).time_attribute_id, p_attributes.time_attribute_id);
1661
1662 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_EXCEPTION
1663 , module => l_log_head
1664 , message => 'Amount for PO Line Id ' || p_attributes.po_line_id || ' updated to ' || get_po_line(p_attributes.po_line_id).timecard_amount
1665 );
1666 END IF;
1667 EXCEPTION
1668 WHEN OTHERS THEN
1669 -- exception while updating PO Line Amount
1670 HXC_INTEGRATION_LAYER_V1_GRP.add_error_to_table( p_message_table => p_messages
1671 , p_message_name => 'HXC_RET_UNEXPECTED_ERROR'
1672 , p_message_token => 'ERR&' || 'updating PO Line Amounts Table: ' || SQLERRM
1673 , p_message_level => HXC_USER_TYPE_DEFINITION_GRP.c_error
1674 , p_message_field => NULL
1675 , p_application_short_name => 'HXC'
1676 , p_timecard_bb_id => p_attributes.detail_bb_id
1677 , p_timecard_bb_ovn => p_attributes.detail_bb_ovn
1678 , p_time_attribute_id => NULL
1679 );
1680 END;
1681
1682 -- everything went through fine so set the status to success
1683 p_attributes.validation_status := 'SUCCESS';
1684 EXCEPTION
1685 WHEN WRONG_BB_TYPE THEN
1686 HXC_INTEGRATION_LAYER_V1_GRP.add_error_to_table( p_message_table => p_messages
1687 , p_message_name => 'RCV_OTL_WRONG_BB_TYPE'
1688 , p_message_token => NULL
1689 , p_message_level => HXC_USER_TYPE_DEFINITION_GRP.c_error
1690 , p_message_field => NULL
1691 , p_application_short_name => 'PO'
1692 , p_timecard_bb_id => p_attributes.detail_bb_id
1693 , p_timecard_bb_ovn => p_attributes.detail_bb_ovn
1694 , p_time_attribute_id => NULL
1695 );
1696 WHEN WRONG_BB_UOM THEN
1697 HXC_INTEGRATION_LAYER_V1_GRP.add_error_to_table( p_message_table => p_messages
1698 , p_message_name => 'RCV_OTL_WRONG_BB_UOM'
1699 , p_message_token => NULL
1700 , p_message_level => HXC_USER_TYPE_DEFINITION_GRP.c_error
1701 , p_message_field => NULL
1702 , p_application_short_name => 'PO'
1703 , p_timecard_bb_id => p_attributes.detail_bb_id
1704 , p_timecard_bb_ovn => p_attributes.detail_bb_ovn
1705 , p_time_attribute_id => NULL
1706 );
1707 WHEN NEGATIVE_HOURS THEN
1708 HXC_INTEGRATION_LAYER_V1_GRP.add_error_to_table( p_message_table => p_messages
1709 , p_message_name => 'RCV_OTL_NEGATIVE_HOURS'
1710 , p_message_token => NULL
1711 , p_message_level => HXC_USER_TYPE_DEFINITION_GRP.c_error
1712 , p_message_field => NULL
1713 , p_application_short_name => 'PO'
1714 , p_timecard_bb_id => p_attributes.detail_bb_id
1715 , p_timecard_bb_ovn => p_attributes.detail_bb_ovn
1716 , p_time_attribute_id => NULL
1717 );
1718 WHEN INCOMPLETE_PO_INFO THEN
1719 HXC_INTEGRATION_LAYER_V1_GRP.add_error_to_table( p_message_table => p_messages
1720 , p_message_name => 'RCV_OTL_INCOMPLETE_PO'
1721 , p_message_token => NULL
1722 , p_message_level => HXC_USER_TYPE_DEFINITION_GRP.c_error
1723 , p_message_field => NULL
1724 , p_application_short_name => 'PO'
1725 , p_timecard_bb_id => p_attributes.detail_bb_id
1726 , p_timecard_bb_ovn => p_attributes.detail_bb_ovn
1727 , p_time_attribute_id => NULL
1728 );
1729 WHEN NO_PO_INFO THEN
1730 HXC_INTEGRATION_LAYER_V1_GRP.add_error_to_table( p_message_table => p_messages
1731 , p_message_name => 'RCV_OTL_INVALID_VALUE'
1732 , p_message_token => 'INVALID_VALUE&' || 'PO'
1733 , p_message_level => HXC_USER_TYPE_DEFINITION_GRP.c_error
1734 , p_message_field => 'PO Header Id'
1735 , p_application_short_name => 'PO'
1736 , p_timecard_bb_id => NULL
1737 , p_time_attribute_id => p_attributes.time_attribute_id
1738 );
1739 WHEN NO_PO_LINE_INFO THEN
1740 HXC_INTEGRATION_LAYER_V1_GRP.add_error_to_table( p_message_table => p_messages
1741 , p_message_name => 'RCV_OTL_INVALID_VALUE'
1742 , p_message_token => 'INVALID_VALUE&' || 'Line'
1743 , p_message_level => HXC_USER_TYPE_DEFINITION_GRP.c_error
1744 , p_message_field => 'PO Line Id'
1745 , p_application_short_name => 'PO'
1746 , p_timecard_bb_id => NULL
1747 , p_time_attribute_id => p_attributes.time_attribute_id
1748 );
1749 WHEN NO_RATE_INFO THEN
1750 HXC_INTEGRATION_LAYER_V1_GRP.add_error_to_table( p_message_table => p_messages
1751 , p_message_name => 'RCV_OTL_INVALID_VALUE'
1752 , p_message_token => 'INVALID_VALUE&' || 'Type'
1753 , p_message_level => HXC_USER_TYPE_DEFINITION_GRP.c_error
1754 , p_message_field => 'PO Price Type'
1755 , p_application_short_name => 'PO'
1756 , p_timecard_bb_id => NULL
1757 , p_time_attribute_id => p_attributes.time_attribute_id
1758 );
1759 WHEN NO_AMT_INFO THEN
1760 HXC_INTEGRATION_LAYER_V1_GRP.add_error_to_table( p_message_table => p_messages
1761 , p_message_name => 'RCV_OTL_NO_AMT'
1762 , p_message_token => NULL
1763 , p_message_level => HXC_USER_TYPE_DEFINITION_GRP.c_error
1764 , p_message_field => NULL
1765 , p_application_short_name => 'PO'
1766 , p_timecard_bb_id => p_attributes.detail_bb_id
1767 , p_timecard_bb_ovn => p_attributes.detail_bb_ovn
1768 , p_time_attribute_id => NULL
1769 );
1770 WHEN INVALID_RATE_TYPE THEN
1771 HXC_INTEGRATION_LAYER_V1_GRP.add_error_to_table( p_message_table => p_messages
1772 , p_message_name => 'RCV_OTL_INVALID_VALUE'
1773 , p_message_token => 'INVALID_VALUE&' || 'Type'
1774 , p_message_level => HXC_USER_TYPE_DEFINITION_GRP.c_error
1775 , p_message_field => 'PO Price Type'
1776 , p_application_short_name => 'PO'
1777 , p_timecard_bb_id => NULL
1778 , p_time_attribute_id => p_attributes.time_attribute_id
1779 );
1780 END Validate_Attributes;
1781
1782 PROCEDURE Validate_Amount_Tolerances
1783 ( p_messages IN OUT NOCOPY HXC_USER_TYPE_DEFINITION_GRP.message_table
1784 ) IS
1785 l_return_status VARCHAR2(1);
1786 l_po_line_id BINARY_INTEGER;
1787 l_timecard_amount_sum PO_LINES_ALL.amount%TYPE;
1788 l_tolerable_amount PO_LINES_ALL.amount%TYPE;
1789 l_qty_rcv_exception_code PO_LINE_LOCATIONS_ALL.qty_rcv_exception_code%TYPE;
1790
1791 GET_TIMECARD_AMOUNT_FAILED EXCEPTION;
1792 l_api_name CONSTANT varchar2(30) := 'Validate_Amount_Tolerances';
1793 l_log_head CONSTANT VARCHAR2(100) := G_LOG_MODULE || '.'||l_api_name;
1794 BEGIN
1795 l_po_line_id := g_po_line_cache.FIRST;
1796 WHILE l_po_line_id IS NOT NULL LOOP
1797 BEGIN
1798 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
1799 , module => l_log_head
1800 , message => 'Validating amount tolerance for PO Line Id=' || l_po_line_id || ' (Billable Amount=' || get_po_line(l_po_line_id).timecard_amount || ')'
1801 );
1802
1803 -- query the tolerance exception code first so we can move on if there is no check
1804 -- do not consider the received amounts because that is already included in the timecard amount below
1805 l_qty_rcv_exception_code := get_po_line(l_po_line_id).qty_rcv_exception_code;
1806 l_tolerable_amount := get_po_line(l_po_line_id).tolerable_amount;
1807
1808 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
1809 , module => l_log_head
1810 , message => 'Fetched Tolerable Amount=' || l_tolerable_amount || ', Receiving Tolerance Exception Code=' || l_qty_rcv_exception_code || ' (PO Line Id=' || l_po_line_id || ')'
1811 );
1812
1813 IF l_qty_rcv_exception_code IN ('WARNING', 'REJECT') THEN
1814 -- consider the amounts already accounted for in submitted timecards
1815 l_timecard_amount_sum := HXC_INTEGRATION_LAYER_V1_GRP.get_mappingvalue_sum
1816 ( p_bld_blk_info_type => 'PURCHASING'
1817 , p_field_name1 => 'PO Billable Amount'
1818 , p_field_name2 => 'PO Line Id'
1819 , p_field_value2 => l_po_line_id
1820 , p_status => 'SUBMITTED'
1821 , p_resource_id => FND_GLOBAL.employee_id
1822 );
1823
1824 -- get_mappingvalue_sum returns null when no timecard matches the conditions
1825 IF l_timecard_amount_sum IS NULL THEN
1826 l_timecard_amount_sum := 0;
1827 END IF;
1828
1829 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
1830 , module => l_log_head
1831 , message => 'Fetched Timecard Amount Sum=' || l_timecard_amount_sum || ' (PO Line Id=' || l_po_line_id || ')'
1832 );
1833
1834 -- finally check if the tolerance will be broken
1835 IF get_po_line(l_po_line_id).timecard_amount + l_timecard_amount_sum > l_tolerable_amount THEN
1836 IF l_qty_rcv_exception_code = 'WARNING' THEN
1837 HXC_INTEGRATION_LAYER_V1_GRP.add_error_to_table( p_message_table => p_messages
1838 , p_message_name => 'RCV_OTL_WARN_TOLERANCE'
1839 , p_message_token => NULL
1840 , p_message_level => HXC_USER_TYPE_DEFINITION_GRP.c_warning
1841 , p_message_field => 'PO Header Id'
1842 , p_application_short_name => 'PO'
1843 , p_timecard_bb_id => NULL
1844 , p_time_attribute_id => get_po_line(l_po_line_id).time_attribute_id
1845 );
1846 ELSIF l_qty_rcv_exception_code = 'REJECT' THEN
1847 HXC_INTEGRATION_LAYER_V1_GRP.add_error_to_table( p_message_table => p_messages
1848 , p_message_name => 'RCV_OTL_EXCEED_TOLERANCE'
1849 , p_message_token => NULL
1850 , p_message_level => HXC_USER_TYPE_DEFINITION_GRP.c_error
1851 , p_message_field => 'PO Header Id'
1852 , p_application_short_name => 'PO'
1853 , p_timecard_bb_id => NULL
1854 , p_time_attribute_id => get_po_line(l_po_line_id).time_attribute_id
1855 );
1856 END IF;
1857 END IF;
1858 END IF;
1859
1860 l_po_line_id := g_po_line_cache.NEXT(l_po_line_id);
1861 EXCEPTION
1862 WHEN GET_TIMECARD_AMOUNT_FAILED THEN
1863 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_EXCEPTION
1864 , module => l_log_head
1865 , message => 'PO_HXC_INTERFACE_PVT.get_timecard_amount returned error'
1866 );
1867
1868 HXC_INTEGRATION_LAYER_V1_GRP.add_error_to_table( p_message_table => p_messages
1869 , p_message_name => 'HXC_RET_UNEXPECTED_ERROR'
1870 , p_message_token => 'ERR&' || 'calling get_timecard_amount'
1871 , p_message_level => HXC_USER_TYPE_DEFINITION_GRP.c_error
1872 , p_message_field => 'PO Header Id'
1873 , p_application_short_name => 'HXC'
1874 , p_timecard_bb_id => NULL
1875 , p_time_attribute_id => get_po_line(l_po_line_id).time_attribute_id
1876 );
1877
1878 l_po_line_id := g_po_line_cache.NEXT(l_po_line_id);
1879 END;
1880 END LOOP;
1881 EXCEPTION
1882 WHEN OTHERS THEN
1883 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
1884 , module => l_log_head
1885 , message => 'Unexpected exception validating amount tolerances: ' || SQLERRM
1886 );
1887
1888 HXC_INTEGRATION_LAYER_V1_GRP.add_error_to_table( p_message_table => p_messages
1889 , p_message_name => 'HXC_RET_UNEXPECTED_ERROR'
1890 , p_message_token => 'ERR&' || 'validating amount tolerances: ' || SQLERRM
1891 , p_message_level => HXC_USER_TYPE_DEFINITION_GRP.c_error
1892 , p_message_field => NULL
1893 , p_application_short_name => 'HXC'
1894 , p_timecard_bb_id => NULL
1895 , p_time_attribute_id => NULL
1896 , p_message_extent => HXC_USER_TYPE_DEFINITION_GRP.c_blk_children_extent
1897 );
1898 END Validate_Amount_Tolerances;
1899
1900 PROCEDURE Derive_Common_RTI_Values( p_rti_row IN OUT NOCOPY RCV_TRANSACTIONS_INTERFACE%ROWTYPE
1901 , p_attributes IN TimecardAttributesRec
1902 ) IS
1903 l_api_name CONSTANT varchar2(30) := 'Derive_Common_RTI_Values';
1904 l_log_head CONSTANT VARCHAR2(100) := G_LOG_MODULE || '.'||l_api_name;
1905 BEGIN
1906 -- interface_transaction_id
1907 SELECT RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL
1908 INTO p_rti_row.interface_transaction_id
1909 FROM dual;
1910
1911 -- job_id
1912 BEGIN
1913 p_rti_row.job_id := get_po_line(p_attributes.po_line_id).job_id;
1914 EXCEPTION
1915 WHEN OTHERS THEN
1916 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
1917 , module => l_log_head
1918 , message => 'Unexpected exception deriving job_id (po_line_id=' || p_attributes.po_line_id || '): ' || SQLERRM
1919 );
1920 RAISE DERIVE_JOB_ID_FAILED;
1921 END;
1922
1923 -- WHO columns
1924 p_rti_row.last_update_date := SYSDATE;
1925 p_rti_row.last_updated_by := FND_GLOBAL.USER_ID;
1926 p_rti_row.creation_date := p_rti_row.last_update_date;
1927 p_rti_row.created_by := p_rti_row.last_updated_by;
1928
1929 -- hardcoded values
1930 p_rti_row.expected_receipt_date := SYSDATE;
1931 p_rti_row.processing_mode_code := 'BATCH';
1932 p_rti_row.processing_status_code := 'PENDING';
1933 p_rti_row.transaction_status_code := 'PENDING';
1934 p_rti_row.receipt_source_code := 'VENDOR';
1935 p_rti_row.source_document_code := 'PO';
1936 p_rti_row.validation_flag := 'Y';
1937
1938 -- atomic processing
1939 p_rti_row.lpn_group_id := p_attributes.lpn_group_id;
1940
1941 -- PO information
1942 p_rti_row.po_header_id := p_attributes.po_header_id;
1943 p_rti_row.po_line_id := p_attributes.po_line_id;
1944 p_rti_row.po_line_location_id := p_attributes.po_line_location_id;
1945 p_rti_row.po_distribution_id := p_attributes.po_distribution_id;
1946
1947 -- timecard info
1948 p_rti_row.timecard_id := p_attributes.timecard_bb_id;
1949 p_rti_row.timecard_ovn := p_attributes.timecard_bb_ovn;
1950
1951 -- projects info
1952 p_rti_row.project_id := p_attributes.project_id;
1953 p_rti_row.task_id := p_attributes.task_id;
1954
1955 -- employee_id
1956 p_rti_row.employee_id := p_attributes.resource_id;
1957 END Derive_Common_RTI_Values;
1958
1959 PROCEDURE Derive_Receive_Values
1960 ( p_rhi_rows IN OUT NOCOPY rhi_table
1961 , p_rti_rows IN OUT NOCOPY rti_table
1962 , p_attributes IN OUT NOCOPY TimecardAttributesRec
1963 ) IS
1964 l_rhi_row RCV_HEADERS_INTERFACE%ROWTYPE;
1965 l_rti_row RCV_TRANSACTIONS_INTERFACE%ROWTYPE;
1966 l_rhi_row_idx BINARY_INTEGER;
1967 l_rti_row_idx BINARY_INTEGER;
1968 l_api_name CONSTANT varchar2(30) := 'Derive_Receive_Values';
1969 l_log_head CONSTANT VARCHAR2(100) := G_LOG_MODULE || '.'||l_api_name;
1970 BEGIN
1971 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
1972 , module => l_log_head
1973 , message => 'Begin Derive_Receive_Values'
1974 );
1975
1976 -- check for existing rhi row
1977 l_rhi_row_idx := get_rhi_idx( p_attributes, p_rhi_rows, p_rti_rows );
1978 IF l_rhi_row_idx IS NOT NULL THEN
1979 -- found a match
1980 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
1981 , module => l_log_head
1982 , message => 'Using existing RHI row'
1983 );
1984
1985 l_rhi_row := p_rhi_rows(l_rhi_row_idx);
1986 ELSE -- found existing rhi row
1987 l_rhi_row_idx := p_rhi_rows.COUNT + 1;
1988
1989 -- header_id
1990 SELECT RCV_HEADERS_INTERFACE_S.NEXTVAL
1991 INTO l_rhi_row.header_interface_id
1992 FROM dual;
1993
1994 -- group_id
1995 l_rhi_row.group_id := get_group_id( p_rti_rows );
1996
1997 -- employee_id
1998 l_rhi_row.employee_id := p_attributes.resource_id;
1999
2000 -- WHO columns
2001 l_rhi_row.last_update_date := SYSDATE;
2002 l_rhi_row.last_updated_by := FND_GLOBAL.USER_ID;
2003 l_rhi_row.creation_date := l_rhi_row.last_update_date;
2004 l_rhi_row.created_by := l_rhi_row.last_updated_by;
2005
2006 -- hardcoded values
2007 l_rhi_row.expected_receipt_date := SYSDATE;
2008 l_rhi_row.processing_status_code := 'PENDING';
2009 l_rhi_row.receipt_source_code := 'VENDOR';
2010 l_rhi_row.transaction_type := 'NEW';
2011 l_rhi_row.auto_transact_code := 'DELIVER';
2012 l_rhi_row.validation_flag := 'Y';
2013
2014 -- PO derived values
2015 l_rhi_row.vendor_id := get_po_header(p_attributes.po_header_id).vendor_id;
2016 l_rhi_row.vendor_site_id := get_po_header(p_attributes.po_header_id).vendor_site_id;
2017 l_rhi_row.ship_to_organization_id := get_po_line(p_attributes.po_line_id).ship_to_organization_id;
2018 l_rhi_row.location_id := get_po_line(p_attributes.po_line_id).ship_to_location_id;
2019 END IF; -- found existing rhi row
2020
2021 -- check for existing rti row
2022 l_rti_row_idx := get_rti_idx( p_attributes, p_rti_rows );
2023 IF l_rti_row_idx IS NOT NULL THEN
2024 -- found a match
2025 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2026 , module => l_log_head
2027 , message => 'Using existing RTI row'
2028 );
2029
2030 -- make a local working copy
2031 l_rti_row := p_rti_rows(l_rti_row_idx);
2032
2033 -- txn date = max(start_time) only if user did not specify any override value
2034 IF p_attributes.po_receipt_date IS NULL AND
2035 trunc(p_attributes.detail_start_time) > l_rti_row.transaction_date
2036 THEN
2037 l_rti_row.transaction_date := trunc(p_attributes.detail_start_time);
2038
2039 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2040 , module => l_log_head
2041 , message => 'Updated transaction_date=' || l_rti_row.transaction_date
2042 );
2043 END IF;
2044
2045 -- update the amount
2046 l_rti_row.amount := l_rti_row.amount + p_attributes.po_billable_amount;
2047
2048 -- use processing_status_code to encode whether to insert to db
2049 -- BUG6343206
2050 -- Reverting the changes done for BUG3550333 [115.69]
2051 -- We are allowing the zero amount receipts to be created as of now.
2052 -- Whnever a new Reciept is created then let it go to RTP if the amount come to 0.
2053 -- Also not updating the RTI to PENDING , as the RTI we found will have the status
2054 -- code as 'PENDING' as we do that in ELSE PART in Derive_Common_RTI_Values.
2055 /*
2056 IF l_rti_row.amount = 0 THEN
2057 l_rhi_row.processing_status_code := 'SUCCESS';
2058 l_rti_row.processing_status_code := 'SUCCESS';
2059 ELSE
2060 l_rhi_row.processing_status_code := 'PENDING';
2061 l_rti_row.processing_status_code := 'PENDING';
2062 END IF;
2063 */
2064
2065 -- associate the rti rows to the building block
2066 p_attributes.receive_rti_id := l_rti_row.interface_transaction_id;
2067
2068 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2069 , module => l_log_head
2070 , message => 'Set receive_rti_id=' || p_attributes.receive_rti_id
2071 );
2072 ELSE -- found existing rti row
2073 l_rti_row_idx := p_rti_rows.COUNT + 1;
2074
2075 -- group_id
2076 l_rti_row.group_id := l_rhi_row.group_id;
2077
2078 -- header_interface_id
2079 l_rti_row.header_interface_id := l_rhi_row.header_interface_id;
2080
2081 -- common derivations
2082 Derive_Common_RTI_Values( l_rti_row
2083 , p_attributes
2084 );
2085
2086 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2087 , module => l_log_head
2088 , message => 'Set po_distribution_id=' || l_rti_row.po_distribution_id
2089 );
2090
2091 -- transaction type
2092 l_rti_row.transaction_type := 'RECEIVE';
2093 l_rti_row.auto_transact_code := 'DELIVER';
2094
2095 -- initialize receipt date
2096 IF p_attributes.po_receipt_date IS NOT NULL THEN
2097 l_rti_row.transaction_date := p_attributes.po_receipt_date;
2098 ELSE
2099 l_rti_row.transaction_date := trunc(p_attributes.detail_start_time);
2100 END IF;
2101
2102 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2103 , module => l_log_head
2104 , message => 'Initialized transaction_date=' || l_rti_row.transaction_date
2105 );
2106
2107 -- amount received
2108 l_rti_row.amount := p_attributes.po_billable_amount;
2109
2110 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2111 , module => l_log_head
2112 , message => 'Set amount=' || l_rti_row.amount || ' from ' || p_attributes.po_billable_amount
2113 );
2114
2115 -- use processing_status_code to encode whether to insert to db
2116 -- BUG6343206
2117 -- Reverting the changes done for BUG3550333 [115.69]
2118 -- We are allowing the zero amount receipts to be created as of now.
2119 -- Whnever a new Reciept is created then let it go at the First time
2120 -- Even if the amount come to 0.
2121 /*
2122 IF l_rti_row.amount = 0 THEN
2123 l_rhi_row.processing_status_code := 'SUCCESS';
2124 l_rti_row.processing_status_code := 'SUCCESS';
2125 END IF;
2126 */
2127
2128 -- associate the rti rows to the building block
2129 p_attributes.receive_rti_id := l_rti_row.interface_transaction_id;
2130
2131 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2132 , module => l_log_head
2133 , message => 'Set receive_rti_id=' || p_attributes.receive_rti_id
2134 );
2135 END IF; -- found existing rti row
2136
2137 -- copy data back to the main tables
2138 p_rhi_rows(l_rhi_row_idx) := l_rhi_row;
2139 p_rti_rows(l_rti_row_idx) := l_rti_row;
2140
2141 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
2142 , module => l_log_head
2143 , message => 'End Derive_Receive_Values'
2144 );
2145 END Derive_Receive_Values;
2146
2147 PROCEDURE Derive_Correction_Values
2148 ( p_rti_rows IN OUT NOCOPY rti_table
2149 , p_attributes IN OUT NOCOPY TimecardAttributesRec
2150 , p_old_attributes IN OUT NOCOPY TimecardAttributesRec
2151 ) IS
2152 l_correction_amount po_lines_all.amount%TYPE;
2153 l_old_correction_amount po_lines_all.amount%TYPE;
2154 l_swap rcv_transactions_interface.interface_transaction_id%TYPE; /*Bug 6031665*/
2155 -- we need one correction for each parent transaction
2156 l_rcv_rti_row RCV_TRANSACTIONS_INTERFACE%ROWTYPE;
2157 l_del_rti_row RCV_TRANSACTIONS_INTERFACE%ROWTYPE;
2158 l_rti_row_idx BINARY_INTEGER;
2159 l_api_name CONSTANT varchar2(30) := 'Derive_Correction_Values';
2160 l_log_head CONSTANT VARCHAR2(100) := G_LOG_MODULE || '.'||l_api_name;
2161 BEGIN
2162 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
2163 , module => l_log_head
2164 , message => 'Begin Derive_Correction_Values'
2165 );
2166
2167 -- determine the correction amount
2168 l_correction_amount := p_attributes.po_billable_amount
2169 - p_old_attributes.po_billable_amount;
2170
2171 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2172 , module => l_log_head
2173 , message => 'Derived correction amount=' || l_correction_amount
2174 );
2175
2176 -- check if we already have this correction so we can just add the amount to it
2177 l_rti_row_idx := get_rti_idx( p_attributes, p_rti_rows );
2178 IF l_rti_row_idx IS NOT NULL THEN
2179 -- found a match
2180 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2181 , module => l_log_head
2182 , message => 'Using existing RTI rows'
2183 );
2184
2185 -- save the existing correction amount for calculation and identifying txn types
2186 l_old_correction_amount := p_rti_rows(l_rti_row_idx).amount;
2187 l_correction_amount := l_correction_amount + l_old_correction_amount;
2188
2189 -- since this is a correction, both this row and the next are relevant
2190 p_rti_rows(l_rti_row_idx).amount := l_correction_amount;
2191 p_rti_rows(l_rti_row_idx+1).amount := l_correction_amount;
2192
2193 -- bug 6031665:
2194 /* Bug 6867607
2195 sign(l_correction_amount)A--sign(l_old_correction_amount)B--swap--A+B<1 and A<>B
2196
2197 -------------------------- ----------------------------- ---- -------------
2198 0 0 N False
2199 0 +1 N False
2200 0 -1 Y True
2201 +1 0 N False
2202 +1 +1 N False
2203 +1 -1 Y True
2204 -1 0 Y True
2205 -1 +1 Y True
2206 -1 -1 N False
2207 */
2208
2209 if ((sign(l_correction_amount) <> sign(l_old_correction_amount))
2210 AND (sign(l_correction_amount)+sign(l_old_correction_amount)<1)) then
2211 -- This means that one is a positive correction and the other is a negative correction.
2212 -- If this happens we need to swap the order of the correction records, because for
2213 -- negative correction the correction record for deliver xaction should go first and
2214 -- for positive correction the correction record for receipt xaction should go first.
2215 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2216 , module => l_log_head
2217 , message => 'sign(l_correction_amount)=' ||sign(l_correction_amount) || ',sign(l_old_correction_amount) ' || sign(l_old_correction_amount)||',hence swap'
2218 );
2219 l_swap:= p_rti_rows(l_rti_row_idx).interface_transaction_id;
2220 p_rti_rows(l_rti_row_idx).interface_transaction_id:=
2221 p_rti_rows(l_rti_row_idx+1).interface_transaction_id;
2222 p_rti_rows(l_rti_row_idx+1).interface_transaction_id:= l_swap;
2223 end if;
2224 -- use processing_status_code to encode whether to insert to db
2225 -- BUG6343206
2226 -- Reverting the changes done for BUG3550333 [115.69, 115.78]
2227 -- We are allowing the zero amount receipts to be created as of now.
2228 -- Whenever a Correcting amount come as Zero we need it to go the RTP .
2229 -- Updating the RTI to PENDING , as the RTI we found might have a differnt status.
2230 /*
2231 IF l_correction_amount = 0 THEN
2232 p_rti_rows(l_rti_row_idx).processing_status_code := 'SUCCESS';
2233 p_rti_rows(l_rti_row_idx+1).processing_status_code := 'SUCCESS';
2234 ELSE
2235 p_rti_rows(l_rti_row_idx).processing_status_code := 'PENDING';
2236 p_rti_rows(l_rti_row_idx+1).processing_status_code := 'PENDING';
2237 END IF;
2238 */
2239
2240 -- associate the rti rows to the building block
2241 -- bug 6031665 : instead of the old_correction amount check the correction amount.
2242 -- IF l_old_correction_amount > 0 THEN
2243 /* Bug 6867607*/
2244 IF l_correction_amount >= 0 THEN
2245 IF p_rti_rows(l_rti_row_idx).interface_transaction_id < p_rti_rows(l_rti_row_idx+1).interface_transaction_id then
2246 p_attributes.receive_rti_id := p_rti_rows(l_rti_row_idx).interface_transaction_id;
2247 p_attributes.deliver_rti_id := p_rti_rows(l_rti_row_idx+1).interface_transaction_id;
2248 ELSE
2249 p_attributes.receive_rti_id := p_rti_rows(l_rti_row_idx+1).interface_transaction_id;
2250 p_attributes.deliver_rti_id := p_rti_rows(l_rti_row_idx).interface_transaction_id;
2251 END IF;
2252 ELSE
2253 IF p_rti_rows(l_rti_row_idx).interface_transaction_id > p_rti_rows(l_rti_row_idx+1).interface_transaction_id then
2254 p_attributes.receive_rti_id := p_rti_rows(l_rti_row_idx).interface_transaction_id;
2255 p_attributes.deliver_rti_id := p_rti_rows(l_rti_row_idx+1).interface_transaction_id;
2256 ELSE
2257 p_attributes.receive_rti_id := p_rti_rows(l_rti_row_idx+1).interface_transaction_id;
2258 p_attributes.deliver_rti_id := p_rti_rows(l_rti_row_idx).interface_transaction_id;
2259 END IF;
2260 END IF;
2261
2262 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2263 , module => l_log_head
2264 , message => 'Set receive_rti_id=' || p_attributes.receive_rti_id || ', deliver_rti_id=' || p_attributes.deliver_rti_id
2265 );
2266 ELSE -- found existing rti rows
2267 -- did not find any match, let's create a new transaction
2268 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2269 , module => l_log_head
2270 , message => 'Creating new RTI rows'
2271 );
2272
2273 -- group_id
2274 l_rcv_rti_row.group_id := get_group_id( p_rti_rows );
2275
2276 -- common derivations
2277 Derive_Common_RTI_Values( l_rcv_rti_row
2278 , p_attributes
2279 );
2280
2281 -- transaction type
2282 l_rcv_rti_row.transaction_type := 'CORRECT';
2283
2284 -- initialize receipt date
2285 IF p_attributes.po_receipt_date IS NOT NULL THEN
2286 l_rcv_rti_row.transaction_date := p_attributes.po_receipt_date;
2287 ELSE
2288 l_rcv_rti_row.transaction_date := trunc(SYSDATE);
2289 END IF;
2290
2291 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2292 , module => l_log_head
2293 , message => 'Initialized transaction_date=' || l_rcv_rti_row.transaction_date
2294 );
2295
2296 -- amount to be corrected
2297 l_rcv_rti_row.amount := l_correction_amount;
2298
2299 -- use processing_status_code to encode whether to insert to db
2300 -- BUG6343206
2301 -- Reverting the changes done for BUG3550333 [115.69]
2302 -- We are allowing the zero amount receipts to be created as of now.
2303 -- Whnever a Correcting amount come as Zero we need it to go the RTP .
2304 -- Updating the RTI to PENDING , as the RTI we found might have a differnt status.
2305 /*
2306 IF l_correction_amount = 0 THEN
2307 l_rcv_rti_row.processing_status_code := 'SUCCESS';
2308 END IF;
2309 */
2310 -- duplicate the rti row to get the other one
2311 l_del_rti_row := l_rcv_rti_row;
2312
2313 -- parent transaction id
2314 l_rcv_rti_row.parent_transaction_id := p_attributes.parent_receive_txn_id;
2315 l_del_rti_row.parent_transaction_id := p_attributes.parent_deliver_txn_id;
2316
2317 IF l_correction_amount < 0 THEN
2318 -- for negative corrections, we correct the deliver first
2319
2320 -- get the next rti id for the receive transaction
2321 SELECT RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL
2322 INTO l_rcv_rti_row.interface_transaction_id
2323 FROM dual;
2324
2325 -- insert into rti table
2326 p_rti_rows(p_rti_rows.COUNT + 1) := l_del_rti_row;
2327 p_rti_rows(p_rti_rows.COUNT + 1) := l_rcv_rti_row;
2328 ELSE
2329 -- for positive corrections, we correct the receive first
2330
2331 -- get the next rti id for the deliver transaction
2332 SELECT RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL
2333 INTO l_del_rti_row.interface_transaction_id
2334 FROM dual;
2335
2336 -- insert into rti table
2337 p_rti_rows(p_rti_rows.COUNT + 1) := l_rcv_rti_row;
2338 p_rti_rows(p_rti_rows.COUNT + 1) := l_del_rti_row;
2339 END IF;
2340
2341 -- associate the rti rows to the building block
2342 p_attributes.receive_rti_id := l_rcv_rti_row.interface_transaction_id;
2343 p_attributes.deliver_rti_id := l_del_rti_row.interface_transaction_id;
2344
2345 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2346 , module => l_log_head
2347 , message => 'Set receive_rti_id=' || p_attributes.receive_rti_id || ', deliver_rti_id=' || p_attributes.deliver_rti_id
2348 );
2349 END IF; -- found existing rti rows
2350
2351 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
2352 , module => l_log_head
2353 , message => 'End Derive_Correction_Values'
2354 );
2355 END Derive_Correction_Values;
2356 --BUG6343206
2357 -- Added a new parameter for receipt date when calling delete on blocks
2358 -- such that we can have the request Transaction date or the system date
2359 -- insteed of using transaction date from OLD records.
2360 PROCEDURE Derive_Delete_Values
2361 ( p_rti_rows IN OUT NOCOPY rti_table
2362 , p_attributes IN OUT NOCOPY TimecardAttributesRec
2363 , receipt_date IN DATE
2364 ) IS
2365 l_new_attributes TimecardAttributesRec;
2366 l_api_name CONSTANT varchar2(30) := 'Derive_Delete_Values';
2367 l_log_head CONSTANT VARCHAR2(100) := G_LOG_MODULE || '.'||l_api_name;
2368 BEGIN
2369 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
2370 , module => l_log_head
2371 , message => 'Begin Derive_Delete_Values'
2372 );
2373
2374 -- delete is the same as correction except the new amount is 0
2375 l_new_attributes := p_attributes;
2376 l_new_attributes.detail_measure := 0;
2377 l_new_attributes.po_billable_amount := 0;
2378 -- BUG6343206
2379 -- We are stamping the request Transaction date or the system date
2380 -- insteed of using transaction date from OLD records. Which has
2381 -- been passed.
2382
2383 l_new_attributes.po_receipt_date := receipt_date;
2384
2385 Derive_Correction_Values( p_rti_rows
2386 , l_new_attributes
2387 , p_attributes
2388 );
2389
2390 -- derive_correction_values only sets the rti-bb relationship in the new attributes
2391 p_attributes.receive_rti_id := l_new_attributes.receive_rti_id;
2392 p_attributes.deliver_rti_id := l_new_attributes.deliver_rti_id;
2393
2394 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
2395 , module => l_log_head
2396 , message => 'End Derive_Delete_Values'
2397 );
2398 END Derive_Delete_Values;
2399
2400 -- Procedure to derive the ROI values for a new detail block
2401 PROCEDURE Derive_New_Block_Values
2402 ( p_rhi_rows IN OUT NOCOPY rhi_table
2403 , p_rti_rows IN OUT NOCOPY rti_table
2404 , p_attributes IN OUT NOCOPY TimecardAttributesRec
2405 ) IS
2406 l_api_name CONSTANT varchar2(30) := 'Derive_New_Block_Values';
2407 l_log_head CONSTANT VARCHAR2(100) := G_LOG_MODULE || '.'||l_api_name;
2408 BEGIN
2409 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
2410 , module => l_log_head
2411 , message => 'Begin Derive_New_Block_Values'
2412 );
2413
2414 -- if we have received against this po line before
2415 -- we will correct that receipt
2416 IF p_attributes.parent_receive_txn_id <> 0 THEN
2417 DECLARE
2418 l_old_attributes TimecardAttributesRec := p_attributes;
2419 BEGIN
2420 -- new block to be added to old receipt
2421 -- we don't have the old attributes so we make them up
2422 l_old_attributes.detail_measure := 0;
2423 l_old_attributes.po_billable_amount := 0;
2424
2425 Derive_Correction_Values( p_rti_rows
2426 , p_attributes
2427 , l_old_attributes
2428 );
2429
2430 -- save the transaction type
2431 p_attributes.transaction_type := 'CORRECT';
2432 END;
2433 ELSE -- received before
2434 Derive_Receive_Values( p_rhi_rows
2435 , p_rti_rows
2436 , p_attributes
2437 );
2438
2439 -- save the transaction type
2440 p_attributes.transaction_type := 'RECEIVE';
2441 END IF;
2442
2443 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
2444 , module => l_log_head
2445 , message => 'End Derive_New_Block_Values'
2446 );
2447 END Derive_New_Block_Values;
2448
2449
2450 PROCEDURE Derive_Interface_Values
2451 ( p_attributes IN OUT NOCOPY TimecardAttributesRec
2452 , p_old_attributes IN OUT NOCOPY TimecardAttributesRec
2453 , p_rhi_rows IN OUT NOCOPY rhi_table
2454 , p_rti_rows IN OUT NOCOPY rti_table
2455 ) IS
2456 l_api_name CONSTANT varchar2(30) := 'Derive_Interface_Values';
2457 l_log_head CONSTANT VARCHAR2(100) := G_LOG_MODULE || '.'||l_api_name;
2458 l_progress VARCHAR2(3) := '000';
2459 BEGIN
2460 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
2461 , module => l_log_head
2462 , message => 'Begin Derive_Interface_Values'
2463 );
2464
2465 -- use lpn_group_id to make sure that the receiving transactions are atomic for this block
2466 BEGIN
2467 SELECT rcv_interface_groups_s.NEXTVAL
2468 INTO p_attributes.lpn_group_id
2469 FROM dual;
2470
2471 p_old_attributes.lpn_group_id := p_attributes.lpn_group_id;
2472 EXCEPTION
2473 WHEN OTHERS THEN
2474 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
2475 , module => l_log_head
2476 , message => 'Unexpected exception setting lpn_group_id: ' || SQLERRM
2477 );
2478 RAISE DERIVE_ROI_VALUES_FAILED;
2479 END;
2480 IF g_debug_stmt THEN
2481 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Printing p_attributes');
2482 RCV_HXT_GRP.debug_TimecardAttributesRec(l_log_head, p_attributes,'NEW');
2483 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Printing p_old_attributes');
2484 RCV_HXT_GRP.debug_TimecardAttributesRec(l_log_head, p_old_attributes,'OLD');
2485 end if;
2486 -- find the parent transactions, assume the old block has the same parents
2487 BEGIN
2488 p_attributes.parent_receive_txn_id := get_rcv_transaction(p_attributes.timecard_bb_id, p_attributes.po_line_id, p_attributes.po_distribution_id).receive_transaction_id;
2489 p_attributes.parent_deliver_txn_id := get_rcv_transaction(p_attributes.timecard_bb_id, p_attributes.po_line_id, p_attributes.po_distribution_id).deliver_transaction_id;
2490
2491 p_old_attributes.parent_receive_txn_id := p_attributes.parent_receive_txn_id;
2492 p_old_attributes.parent_deliver_txn_id := p_attributes.parent_deliver_txn_id;
2493 EXCEPTION
2494 WHEN OTHERS THEN
2495 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
2496 , module => l_log_head
2497 , message => 'Unexpected exception querying for parent transactions in Derive_Interface_Values (po_line_id=' || p_attributes.po_line_id || ', timecard_bb_id=' || p_attributes.timecard_bb_id || '): ' || SQLERRM
2498 );
2499 RAISE DERIVE_ROI_VALUES_FAILED;
2500 END;
2501
2502 -- check if the block was updated
2503 IF p_attributes.detail_changed = 'Y' THEN
2504 -- BUG# 6798505/6631524
2505 -- User can Change Project Information on the time card along with the Line Information
2506 -- Which will lead to change in Distribution Id. If the Distribution ID changes then
2507 -- follow the same Step as we do in Line Change.
2508 IF ((p_attributes.po_line_id <> p_old_attributes.po_line_id )
2509 OR (p_attributes.po_distribution_id is not null and p_attributes.po_distribution_id <> p_old_attributes.po_distribution_id )) THEN
2510
2511 -- if the user changed po information we need to correct
2512 -- the old receipt to zero and either create a new receipt
2513 -- or correct an existing one
2514 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
2515 , module => l_log_head
2516 , message => 'Correction : Either PO Line OR Project/Task information changed '
2517 );
2518 -- in this case the old block does not have the same parents
2519 -- so we need to derive it
2520 BEGIN
2521 -- These calls will flush the records cached by the new timecard, but we
2522 -- do not expect the user to change the PO on a timecard very often
2523 -- BUG# 6798505/6631524
2524 -- User can Change Project Information on the time card along with the Line Information
2525 -- Which will lead to change in Distribution Id. We need to look for Old Attribute
2526 -- Distribution Id.
2527 p_old_attributes.parent_receive_txn_id := get_rcv_transaction(p_old_attributes.timecard_bb_id, p_old_attributes.po_line_id, p_old_attributes.po_distribution_id).receive_transaction_id;
2528 p_old_attributes.parent_deliver_txn_id := get_rcv_transaction(p_old_attributes.timecard_bb_id, p_old_attributes.po_line_id, p_old_attributes.po_distribution_id).deliver_transaction_id;
2529 EXCEPTION
2530 WHEN OTHERS THEN
2531 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
2532 , module => l_log_head
2533 , message => 'Unexpected exception querying for parent transactions for the old block in Derive_Interface_Values ('
2534 || 'po_line_id=' || p_old_attributes.po_line_id
2535 || ', timecard_bb_id=' || p_old_attributes.timecard_bb_id
2536 || '): ' || SQLERRM
2537 );
2538 RAISE DERIVE_ROI_VALUES_FAILED;
2539 END;
2540
2541 -- BUG6343206
2542 -- Added a new parameter for receipt date when calling delete on blocks
2543 -- such that we can have the request Transaction date or the system date
2544 -- insteed of using transaction date from OLD records.
2545 Derive_Delete_Values( p_rti_rows
2546 , p_old_attributes
2547 , p_attributes.po_receipt_date
2548 );
2549
2550 -- capture the rti ids for the delete
2551 p_attributes.delete_receive_rti_id := p_old_attributes.receive_rti_id;
2552 p_attributes.delete_deliver_rti_id := p_old_attributes.deliver_rti_id;
2553
2554 -- either create a new receipt or correct an existing one
2555 -- against the new po information
2556 Derive_New_Block_Values( p_rhi_rows
2557 , p_rti_rows
2558 , p_attributes
2559 );
2560
2561 -- save the transaction type
2562 p_attributes.transaction_type := 'DELETE ' || p_attributes.transaction_type;
2563
2564 ELSE -- po line changed
2565 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
2566 , module => l_log_head
2567 , message => 'Correction : Either Rate type or the hours worked changed '
2568 );
2569 -- if the user only changed the rate type or the hours worked
2570 -- then we just need to correct the receipt
2571 IF p_attributes.detail_deleted = 'Y' THEN
2572 -- BUG6343206
2573 -- Added a new parameter for receipt date when calling delete on blocks
2574 -- such that we can have the request Transaction date or the system date
2575 -- insteed of using transaction date from OLD records.
2576 Derive_Delete_Values( p_rti_rows
2577 , p_old_attributes
2578 , p_attributes.po_receipt_date
2579 );
2580
2581 -- capture the rti ids for the delete
2582 p_attributes.delete_receive_rti_id := p_old_attributes.receive_rti_id;
2583 p_attributes.delete_deliver_rti_id := p_old_attributes.deliver_rti_id;
2584
2585 -- save the transaction type
2586 p_attributes.transaction_type := 'DELETE';
2587 ELSE
2588 Derive_Correction_Values( p_rti_rows
2589 , p_attributes
2590 , p_old_attributes
2591 );
2592
2593 -- save the transaction type
2594 p_attributes.transaction_type := 'CORRECT';
2595 END IF;
2596 END IF; -- po line changed
2597 ELSE -- detail_changed
2598 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
2599 , module => l_log_head
2600 , message => 'Receive : Block is newly created.'
2601 );
2602 Derive_New_Block_Values( p_rhi_rows
2603 , p_rti_rows
2604 , p_attributes
2605 );
2606 END IF; -- detail_changed
2607
2608 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
2609 , module => l_log_head
2610 , message => 'Done deriving ROI values. Calling iSP to store timecard detail information...'
2611 );
2612
2613 -- iSP Integration - store timecard information in iSP table
2614 DECLARE
2615 l_return_status VARCHAR2(240);
2616 l_msg_data VARCHAR2(2000);
2617 l_action VARCHAR2(240);
2618
2619 l_rhi_idx BINARY_INTEGER;
2620 l_rti_idx BINARY_INTEGER;
2621 l_rhi_row rcv_headers_interface%ROWTYPE;
2622 l_rti_row rcv_transactions_interface%ROWTYPE;
2623 BEGIN
2624 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2625 , module => l_log_head
2626 , message => 'Finding ROI rows: p_rhi_rows.COUNT=' || p_rhi_rows.COUNT || ' p_rti_rows.COUNT=' || p_rti_rows.COUNT || ' p_attributes.po_distribution_id=' || p_attributes.po_distribution_id
2627 );
2628
2629 l_rhi_idx := get_rhi_idx(p_attributes, p_rhi_rows, p_rti_rows);
2630 IF l_rhi_idx IS NOT NULL THEN
2631 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2632 , module => l_log_head
2633 , message => 'rhi_idx=' || l_rhi_idx
2634 );
2635 l_rhi_row := p_rhi_rows(l_rhi_idx);
2636 END IF;
2637
2638 l_rti_idx := get_rti_idx(p_attributes, p_rti_rows);
2639 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2640 , module => l_log_head
2641 , message => 'rti_idx=' || l_rti_idx
2642 );
2643 l_rti_row := p_rti_rows(l_rti_idx);
2644
2645 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2646 , module => l_log_head
2647 , message => 'Setting p_action for store_timecard_details'
2648 );
2649
2650 IF p_attributes.detail_changed = 'Y' THEN
2651 IF p_attributes.detail_deleted = 'Y' THEN
2652 l_action := 'DELETE';
2653 ELSE
2654 l_action := 'UPDATE';
2655 END IF;
2656 ELSE
2657 l_action := 'INSERT';
2658 END IF;
2659
2660 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2661 , module => l_log_head
2662 , message => 'p_action=' || l_action
2663 );
2664
2665
2666 PO_STORE_TIMECARD_PKG_GRP.store_timecard_details (
2667 p_api_version => 1.0
2668 , x_return_status => l_return_status
2669 , x_msg_data => l_msg_data
2670 , p_vendor_id => l_rhi_row.vendor_id
2671 , p_vendor_site_id => l_rhi_row.vendor_site_id
2672 , p_vendor_contact_id => NULL
2673 , p_po_num => p_attributes.po_number
2674 , p_po_line_number => p_attributes.po_line
2675 , p_org_id => p_attributes.org_id
2676 , p_project_id => p_attributes.project_id
2677 , p_task_id => p_attributes.task_id
2678 , p_tc_id => p_attributes.timecard_bb_id
2679 , p_tc_day_id => p_attributes.day_bb_id
2680 , p_tc_detail_id => p_attributes.detail_bb_id
2681 , p_tc_uom => p_attributes.detail_uom
2682 , p_tc_start_date => p_attributes.timecard_start_time
2683 , p_tc_end_date => p_attributes.timecard_stop_time
2684 , p_tc_entry_date => p_attributes.detail_start_time
2685 , p_tc_time_received => p_attributes.detail_measure
2686 , p_tc_approval_status => p_attributes.timecard_approval_status
2687 , p_tc_approval_date => p_attributes.timecard_approval_date
2688 , p_tc_submission_date => p_attributes.timecard_submission_date
2689 , p_contingent_worker_id => p_attributes.resource_id
2690 , p_tc_comment_text => p_attributes.timecard_comment
2691 , p_line_rate_type => p_attributes.po_price_type
2692 , p_line_rate => 0
2693 , p_action => l_action
2694 , p_interface_transaction_id => l_rti_row.interface_transaction_id
2695 );
2696
2697 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2698 , module => l_log_head
2699 , message => 'After store_timecard_details'
2700 );
2701
2702 IF l_return_status <> FND_API.g_ret_sts_success THEN
2703 RAISE ISP_STORE_TIMECARD_FAILED;
2704 END IF;
2705 EXCEPTION
2706 WHEN ISP_STORE_TIMECARD_FAILED THEN
2707 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_EXCEPTION
2708 , module => l_log_head
2709 , message => 'iSP store_timecard_details failed: x_return_status=' || l_return_status || ', x_msg_data=' || l_msg_data
2710 );
2711 RAISE DERIVE_ROI_VALUES_FAILED;
2712 WHEN OTHERS THEN
2713 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_EXCEPTION
2714 , module => l_log_head
2715 , message => 'Exception trying to call iSP store_timecard_details: x_return_status=' || l_return_status || ', x_msg_data=' || l_msg_data || ', sqlerrm=' || sqlerrm
2716 );
2717 RAISE DERIVE_ROI_VALUES_FAILED;
2718 END; -- end of isp integration
2719
2720 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
2721 , module => l_log_head
2722 , message => 'End Derive_Interface_Values'
2723 );
2724 EXCEPTION
2725 WHEN DERIVE_DISTRIBUTION_ID_FAILED THEN
2726 RAISE DERIVE_ROI_VALUES_FAILED;
2727 WHEN DERIVE_JOB_ID_FAILED THEN
2728 RAISE DERIVE_ROI_VALUES_FAILED;
2729 END Derive_Interface_Values;
2730
2731 PROCEDURE Add_Where_Clause
2732 ( p_where_clause IN OUT NOCOPY VARCHAR2
2733 , p_new_condition IN VARCHAR2
2734 ) IS
2735 BEGIN
2736 IF p_where_clause IS NOT NULL THEN
2737 p_where_clause := p_where_clause || ' AND ' || p_new_condition;
2738 ELSE
2739 p_where_clause := p_new_condition;
2740 END IF;
2741 END Add_Where_Clause;
2742
2743 -- bug6395858
2744 -- we are using p_rhi_rows and p_rti_row for tracking any errors
2745 -- which might occur while populating the data in Interface Table.
2746
2747 PROCEDURE Insert_Interface_Values
2748 ( p_rhi_rows IN OUT NOCOPY rhi_table
2749 , p_rti_rows IN OUT NOCOPY rti_table
2750 -- Bug6343206
2751 -- Reverting the changes done for BUG3550333 [115.69]
2752 -- We are allowing the zero amount receipts to be created as of now.
2753 -- There will be no entry going in as SUCCESS. so we need no track
2754 -- those block by l_rti_status.
2755 -- , p_rti_status IN OUT NOCOPY rti_status_table
2756 ) IS
2757
2758 --added for bugfix 5609476
2759 CURSOR c_get_currency_code(v_po_header_id NUMBER) IS
2760 SELECT currency_code
2761 FROM po_headers
2762 where po_header_id = v_po_header_id;
2763
2764 -- for 8i compatibility, we can only do BULK INSERT using an array for each column
2765 TYPE header_interface_id_tbl IS TABLE OF RCV_HEADERS_INTERFACE.header_interface_id%TYPE INDEX BY BINARY_INTEGER;
2766 TYPE group_id_tbl IS TABLE OF RCV_HEADERS_INTERFACE.group_id%TYPE INDEX BY BINARY_INTEGER;
2767 TYPE processing_status_code_tbl IS TABLE OF RCV_HEADERS_INTERFACE.processing_status_code%TYPE INDEX BY BINARY_INTEGER;
2768 TYPE receipt_source_code_tbl IS TABLE OF RCV_HEADERS_INTERFACE.receipt_source_code%TYPE INDEX BY BINARY_INTEGER;
2769 TYPE transaction_type_tbl IS TABLE OF RCV_HEADERS_INTERFACE.transaction_type%TYPE INDEX BY BINARY_INTEGER;
2770 TYPE auto_transact_code_tbl IS TABLE OF RCV_HEADERS_INTERFACE.auto_transact_code%TYPE INDEX BY BINARY_INTEGER;
2771 TYPE last_update_date_tbl IS TABLE OF RCV_HEADERS_INTERFACE.last_update_date%TYPE INDEX BY BINARY_INTEGER;
2772 TYPE last_updated_by_tbl IS TABLE OF RCV_HEADERS_INTERFACE.last_updated_by%TYPE INDEX BY BINARY_INTEGER;
2773 TYPE creation_date_tbl IS TABLE OF RCV_HEADERS_INTERFACE.creation_date%TYPE INDEX BY BINARY_INTEGER;
2774 TYPE created_by_tbl IS TABLE OF RCV_HEADERS_INTERFACE.created_by%TYPE INDEX BY BINARY_INTEGER;
2775 TYPE vendor_id_tbl IS TABLE OF RCV_HEADERS_INTERFACE.vendor_id%TYPE INDEX BY BINARY_INTEGER;
2776 TYPE vendor_site_id_tbl IS TABLE OF RCV_HEADERS_INTERFACE.vendor_site_id%TYPE INDEX BY BINARY_INTEGER;
2777 TYPE ship_to_organization_id_tbl IS TABLE OF RCV_HEADERS_INTERFACE.ship_to_organization_id%TYPE INDEX BY BINARY_INTEGER;
2778 TYPE location_id_tbl IS TABLE OF RCV_HEADERS_INTERFACE.location_id%TYPE INDEX BY BINARY_INTEGER;
2779 TYPE expected_receipt_date_tbl IS TABLE OF RCV_HEADERS_INTERFACE.expected_receipt_date%TYPE INDEX BY BINARY_INTEGER;
2780 TYPE employee_id_tbl IS TABLE OF RCV_HEADERS_INTERFACE.employee_id%TYPE INDEX BY BINARY_INTEGER;
2781 TYPE validation_flag_tbl IS TABLE OF RCV_HEADERS_INTERFACE.validation_flag%TYPE INDEX BY BINARY_INTEGER;
2782
2783 TYPE interface_transaction_id_tbl IS TABLE OF RCV_TRANSACTIONS_INTERFACE.interface_transaction_id%TYPE INDEX BY BINARY_INTEGER;
2784 TYPE lpn_group_id_tbl IS TABLE OF RCV_TRANSACTIONS_INTERFACE.lpn_group_id%TYPE INDEX BY BINARY_INTEGER;
2785 TYPE transaction_date_tbl IS TABLE OF RCV_TRANSACTIONS_INTERFACE.transaction_date%TYPE INDEX BY BINARY_INTEGER;
2786 TYPE processing_mode_code_tbl IS TABLE OF RCV_TRANSACTIONS_INTERFACE.processing_mode_code%TYPE INDEX BY BINARY_INTEGER;
2787 TYPE transaction_status_code_tbl IS TABLE OF RCV_TRANSACTIONS_INTERFACE.transaction_status_code%TYPE INDEX BY BINARY_INTEGER;
2788 TYPE source_document_code_tbl IS TABLE OF RCV_TRANSACTIONS_INTERFACE.source_document_code%TYPE INDEX BY BINARY_INTEGER;
2789 TYPE parent_transaction_id_tbl IS TABLE OF RCV_TRANSACTIONS_INTERFACE.parent_transaction_id%TYPE INDEX BY BINARY_INTEGER;
2790 TYPE po_header_id_tbl IS TABLE OF RCV_TRANSACTIONS_INTERFACE.po_header_id%TYPE INDEX BY BINARY_INTEGER;
2791 TYPE po_line_id_tbl IS TABLE OF RCV_TRANSACTIONS_INTERFACE.po_line_id%TYPE INDEX BY BINARY_INTEGER;
2792 TYPE po_line_location_id_tbl IS TABLE OF RCV_TRANSACTIONS_INTERFACE.po_line_location_id%TYPE INDEX BY BINARY_INTEGER;
2793 TYPE po_distribution_id_tbl IS TABLE OF RCV_TRANSACTIONS_INTERFACE.po_distribution_id%TYPE INDEX BY BINARY_INTEGER;
2794 TYPE project_id_tbl IS TABLE OF RCV_TRANSACTIONS_INTERFACE.project_id%TYPE INDEX BY BINARY_INTEGER;
2795 TYPE task_id_tbl IS TABLE OF RCV_TRANSACTIONS_INTERFACE.task_id%TYPE INDEX BY BINARY_INTEGER;
2796 TYPE amount_tbl IS TABLE OF RCV_TRANSACTIONS_INTERFACE.amount%TYPE INDEX BY BINARY_INTEGER;
2797 TYPE job_id_tbl IS TABLE OF RCV_TRANSACTIONS_INTERFACE.job_id%TYPE INDEX BY BINARY_INTEGER;
2798 TYPE timecard_id_tbl IS TABLE OF RCV_TRANSACTIONS_INTERFACE.timecard_id%TYPE INDEX BY BINARY_INTEGER;
2799 TYPE timecard_ovn_tbl IS TABLE OF RCV_TRANSACTIONS_INTERFACE.timecard_ovn%TYPE INDEX BY BINARY_INTEGER;
2800 --Bug6395858
2801 -- A temp table l_rhi_stat is taken to mark those timecards for
2802 -- which RHI got in error while processing and corrosponding RTI
2803 -- has to maked error.
2804 TYPE l_index_table IS TABLE OF VARCHAR2(10) INDEX BY binary_integer;
2805 l_rhi_stat l_index_table;
2806 rhi_header_interface_id header_interface_id_tbl;
2807 rhi_group_id group_id_tbl;
2808 rhi_processing_status_code processing_status_code_tbl;
2809 rhi_receipt_source_code receipt_source_code_tbl;
2810 rhi_transaction_type transaction_type_tbl;
2811 rhi_auto_transact_code auto_transact_code_tbl;
2812 rhi_last_update_date last_update_date_tbl;
2813 rhi_last_updated_by last_updated_by_tbl;
2814 rhi_creation_date creation_date_tbl;
2815 rhi_created_by created_by_tbl;
2816 rhi_vendor_id vendor_id_tbl;
2817 rhi_vendor_site_id vendor_site_id_tbl;
2818 rhi_ship_to_organization_id ship_to_organization_id_tbl;
2819 rhi_location_id location_id_tbl;
2820 rhi_expected_receipt_date expected_receipt_date_tbl;
2821 rhi_employee_id employee_id_tbl;
2822 rhi_validation_flag validation_flag_tbl;
2823
2824 rti_interface_transaction_id interface_transaction_id_tbl;
2825 rti_header_interface_id header_interface_id_tbl;
2826 rti_group_id group_id_tbl;
2827 rti_lpn_group_id lpn_group_id_tbl;
2828 rti_last_update_date last_update_date_tbl;
2829 rti_last_updated_by last_updated_by_tbl;
2830 rti_creation_date creation_date_tbl;
2831 rti_created_by created_by_tbl;
2832 rti_transaction_type transaction_type_tbl;
2833 rti_transaction_date transaction_date_tbl;
2834 rti_processing_status_code processing_status_code_tbl;
2835 rti_processing_mode_code processing_mode_code_tbl;
2836 rti_transaction_status_code transaction_status_code_tbl;
2837 rti_employee_id employee_id_tbl;
2838 rti_auto_transact_code auto_transact_code_tbl;
2839 rti_receipt_source_code receipt_source_code_tbl;
2840 rti_source_document_code source_document_code_tbl;
2841 rti_parent_transaction_id parent_transaction_id_tbl;
2842 rti_po_header_id po_header_id_tbl;
2843 rti_po_line_id po_line_id_tbl;
2844 rti_po_line_location_id po_line_location_id_tbl;
2845 rti_po_distribution_id po_distribution_id_tbl;
2846 rti_project_id project_id_tbl;
2847 rti_task_id task_id_tbl;
2848 rti_expected_receipt_date expected_receipt_date_tbl;
2849 rti_validation_flag validation_flag_tbl;
2850 rti_amount amount_tbl;
2851 rti_job_id job_id_tbl;
2852 rti_timecard_id timecard_id_tbl;
2853 rti_timecard_ovn timecard_ovn_tbl;
2854
2855 row_idx BINARY_INTEGER;
2856 l_currency_code VARCHAR2(3); --bugfix 5609476
2857 l_api_name CONSTANT varchar2(30) := 'Insert_Interface_Values';
2858 l_log_head CONSTANT VARCHAR2(100) := G_LOG_MODULE || '.'||l_api_name;
2859 BEGIN
2860 -- save new ROI data to the database
2861 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
2862 , module => l_log_head
2863 , message => 'Begin Insert_Interface_Values'
2864 );
2865
2866 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2867 , module => l_log_head
2868 , message => 'RHI rows: ' || p_rhi_rows.COUNT || ' RTI rows: '
2869 || p_rti_rows.COUNT
2870 );
2871
2872 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2873 , module => l_log_head
2874 , message => 'Coping the RHI Records to Local PL/SQL tables'
2875 );
2876 -- transfer data from table of records to tables of each column
2877 FOR i IN 1..p_rhi_rows.COUNT LOOP
2878 IF p_rhi_rows(i).processing_status_code = 'PENDING' THEN
2879 BEGIN
2880 row_idx := rhi_header_interface_id.COUNT + 1;
2881 rhi_header_interface_id(row_idx) := p_rhi_rows(i).header_interface_id;
2882 rhi_group_id(row_idx) := p_rhi_rows(i).group_id;
2883 rhi_processing_status_code(row_idx) := p_rhi_rows(i).processing_status_code;
2884 rhi_receipt_source_code(row_idx) := p_rhi_rows(i).receipt_source_code;
2885 rhi_transaction_type(row_idx) := p_rhi_rows(i).transaction_type;
2886 rhi_auto_transact_code(row_idx) := p_rhi_rows(i).auto_transact_code;
2887 rhi_last_update_date(row_idx) := p_rhi_rows(i).last_update_date;
2888 rhi_last_updated_by(row_idx) := p_rhi_rows(i).last_updated_by;
2889 rhi_creation_date(row_idx) := p_rhi_rows(i).creation_date;
2890 rhi_created_by(row_idx) := p_rhi_rows(i).created_by;
2891 rhi_vendor_id(row_idx) := p_rhi_rows(i).vendor_id;
2892 rhi_vendor_site_id(row_idx) := p_rhi_rows(i).vendor_site_id;
2893 rhi_ship_to_organization_id(row_idx) := p_rhi_rows(i).ship_to_organization_id;
2894 rhi_location_id(row_idx) := p_rhi_rows(i).location_id;
2895 rhi_expected_receipt_date(row_idx) := p_rhi_rows(i).expected_receipt_date;
2896 rhi_employee_id(row_idx) := p_rhi_rows(i).employee_id;
2897 rhi_validation_flag(row_idx) := p_rhi_rows(i).validation_flag;
2898 EXCEPTION
2899 WHEN OTHERS THEN
2900 -- Bug6395858
2901 -- Marking the RHI as error. as it went into exception
2902 -- Also temp table is marked for rhi rows to make RTI
2903 -- also in error.
2904 p_rhi_rows(i).processing_status_code := 'ERROR';
2905 l_rhi_stat(p_rhi_rows(i).header_interface_id) := 'ERROR';
2906 row_idx := row_idx - 1;
2907 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2908 , module => l_log_head
2909 , message => 'Exception while populating RHI plsql table ' ||
2910 p_rhi_rows(i).header_interface_id
2911 || ' Error '||SQLERRM
2912 );
2913 END;
2914 END IF;
2915 END LOOP;
2916
2917 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2918 , module => l_log_head
2919 , message => 'Inserting ' || rhi_header_interface_id.COUNT || ' rows into RHI'
2920 );
2921
2922 -- insert into db from arrays
2923 FORALL i IN 1..rhi_header_interface_id.COUNT
2924 INSERT INTO rcv_headers_interface( header_interface_id
2925 , group_id
2926 , processing_status_code
2927 , receipt_source_code
2928 , transaction_type
2929 , auto_transact_code
2930 , last_update_date
2931 , last_updated_by
2932 , creation_date
2933 , created_by
2934 , vendor_id
2935 , vendor_site_id
2936 , ship_to_organization_id
2937 , location_id
2938 , expected_receipt_date
2939 , employee_id
2940 , validation_flag
2941 ) VALUES ( rhi_header_interface_id(i)
2942 , rhi_group_id(i)
2943 , rhi_processing_status_code(i)
2944 , rhi_receipt_source_code(i)
2945 , rhi_transaction_type(i)
2946 , rhi_auto_transact_code(i)
2947 , rhi_last_update_date(i)
2948 , rhi_last_updated_by(i)
2949 , rhi_creation_date(i)
2950 , rhi_created_by(i)
2951 , rhi_vendor_id(i)
2952 , rhi_vendor_site_id(i)
2953 , rhi_ship_to_organization_id(i)
2954 , rhi_location_id(i)
2955 , rhi_expected_receipt_date(i)
2956 , rhi_employee_id(i)
2957 , rhi_validation_flag(i)
2958 );
2959
2960 -- transfer data from table of records to tables of each column
2961
2962 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2963 , module => l_log_head
2964 , message => 'Inserted ' || rhi_header_interface_id.COUNT || ' rows into RHI'
2965 );
2966
2967 -- bug 6031665
2968 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
2969 , module => l_log_head
2970 , message => 'Copying the RTI Records to Local PL/SQL tables'
2971 );
2972 FOR i IN 1..p_rti_rows.COUNT LOOP
2973 --Bug6395858
2974 -- Checking every RTI against the temp table whether the
2975 -- associated RHI is the Error the mark that records as error.
2976
2977 IF l_rhi_stat.EXISTS(p_rti_rows(i).header_interface_id) THEN
2978 p_rti_rows(i).processing_status_code := 'ERROR';
2979 END IF;
2980 IF p_rti_rows(i).processing_status_code = 'PENDING' THEN
2981 BEGIN
2982 row_idx := rti_interface_transaction_id.COUNT + 1;
2983 rti_interface_transaction_id(row_idx) := p_rti_rows(i).interface_transaction_id;
2984 rti_header_interface_id(row_idx) := p_rti_rows(i).header_interface_id;
2985 rti_group_id(row_idx) := p_rti_rows(i).group_id;
2986 rti_lpn_group_id(row_idx) := p_rti_rows(i).lpn_group_id;
2987 rti_last_update_date(row_idx) := p_rti_rows(i).last_update_date;
2988 rti_last_updated_by(row_idx) := p_rti_rows(i).last_updated_by;
2989 rti_creation_date(row_idx) := p_rti_rows(i).creation_date;
2990 rti_created_by(row_idx) := p_rti_rows(i).created_by;
2991 rti_transaction_type(row_idx) := p_rti_rows(i).transaction_type;
2992 rti_transaction_date(row_idx) := p_rti_rows(i).transaction_date;
2993 rti_processing_status_code(row_idx) := p_rti_rows(i).processing_status_code;
2994 rti_processing_mode_code(row_idx) := p_rti_rows(i).processing_mode_code;
2995 rti_transaction_status_code(row_idx) := p_rti_rows(i).transaction_status_code;
2996 rti_employee_id(row_idx) := p_rti_rows(i).employee_id;
2997 rti_auto_transact_code(row_idx) := p_rti_rows(i).auto_transact_code;
2998 rti_receipt_source_code(row_idx) := p_rti_rows(i).receipt_source_code;
2999 rti_source_document_code(row_idx) := p_rti_rows(i).source_document_code;
3000 rti_parent_transaction_id(row_idx) := p_rti_rows(i).parent_transaction_id;
3001 rti_po_header_id(row_idx) := p_rti_rows(i).po_header_id;
3002 rti_po_line_id(row_idx) := p_rti_rows(i).po_line_id;
3003 rti_po_line_location_id(row_idx) := p_rti_rows(i).po_line_location_id;
3004 rti_po_distribution_id(row_idx) := p_rti_rows(i).po_distribution_id;
3005 rti_project_id(row_idx) := p_rti_rows(i).project_id;
3006 rti_task_id(row_idx) := p_rti_rows(i).task_id;
3007 rti_expected_receipt_date(row_idx) := p_rti_rows(i).expected_receipt_date;
3008 rti_validation_flag(row_idx) := p_rti_rows(i).validation_flag;
3009 --bugfix 5609476 {
3010 OPEN c_get_currency_code(p_rti_rows(i).po_header_id);
3011 FETCH c_get_currency_code INTO l_currency_code;
3012 CLOSE c_get_currency_code;
3013
3014 --call AP API to get the correct rounded-off amount
3015 rti_amount(row_idx) := ap_utilities_pkg.ap_round_currency(p_rti_rows(i).amount,l_currency_code);
3016 --bugfix 5609476 }
3017 rti_job_id(row_idx) := p_rti_rows(i).job_id;
3018 rti_timecard_id(row_idx) := p_rti_rows(i).timecard_id;
3019 rti_timecard_ovn(row_idx) := p_rti_rows(i).timecard_ovn;
3020 EXCEPTION
3021 WHEN OTHERS THEN
3022 -- Bug6395858
3023 -- Now we are marking RTI as error if any exception is raised while populating the tables
3024 -- from RTI record.
3025
3026 row_idx := row_idx - 1;
3027 p_rti_rows(i).processing_status_code := 'ERROR';
3028 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3029 , module => l_log_head
3030 , message => 'Exception while populating RTI plsql table. Interface transaction id :'
3031 || p_rti_rows(i).interface_transaction_id ||
3032 ' Time card id : '||p_rti_rows(i).timecard_id|| ' Error '||SQLERRM
3033 );
3034 END;
3035 -- Bug6343206
3036 -- We are allowing the zero amount receipts to be created as of now.
3037 -- There wont be any entry comming as a fake SUCCESS.
3038
3039 /*ELSIF p_rti_rows(i).processing_status_code = 'SUCCESS' THEN
3040 -- Marking the record as fake success
3041 -- Bug6395858
3042 -- If Records dont have interface transaction id, then we need to mark this error
3043 -- as there is no other option at present to propogate fake success from here back
3044 -- to the OTL.
3045 IF (p_rti_rows(i).interface_transaction_id IS NOT NULL) THEN
3046 p_rti_status(p_rti_rows(i).interface_transaction_id) := 1;
3047 ELSE
3048 -- No Need to decrease the Counter here as we didnt increased.
3049 p_rti_rows(i).processing_status_code := 'ERROR';
3050 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3051 , module => l_log_head
3052 , message => 'Exception while propogating fake success. Interface transaction id is NULL:'
3053 ||' Time card id : '||p_rti_rows(i).timecard_id|| ' Error '||SQLERRM
3054 );
3055 END IF;*/
3056 END IF;
3057 END LOOP;
3058 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3059 , module => l_log_head
3060 , message => 'Inserting '|| rti_interface_transaction_id.COUNT || ' rows into RHI'
3061 );
3062
3063 -- insert into db from arrays
3064 FORALL i IN 1..rti_interface_transaction_id.COUNT
3065 INSERT INTO rcv_transactions_interface( interface_transaction_id
3066 , header_interface_id
3067 , group_id
3068 , lpn_group_id
3069 , last_update_date
3070 , last_updated_by
3071 , creation_date
3072 , created_by
3073 , transaction_type
3074 , transaction_date
3075 , processing_status_code
3076 , processing_mode_code
3077 , transaction_status_code
3078 , employee_id
3079 , auto_transact_code
3080 , receipt_source_code
3081 , source_document_code
3082 , parent_transaction_id
3083 , po_header_id
3084 , po_line_id
3085 , po_line_location_id
3086 , po_distribution_id
3087 , project_id
3088 , task_id
3089 , expected_receipt_date
3090 , validation_flag
3091 , amount
3092 , job_id
3093 , timecard_id
3094 , timecard_ovn
3095 ) VALUES ( rti_interface_transaction_id(i)
3096 , rti_header_interface_id(i)
3097 , rti_group_id(i)
3098 , rti_lpn_group_id(i)
3099 , rti_last_update_date(i)
3100 , rti_last_updated_by(i)
3101 , rti_creation_date(i)
3102 , rti_created_by(i)
3103 , rti_transaction_type(i)
3104 , rti_transaction_date(i)
3105 , rti_processing_status_code(i)
3106 , rti_processing_mode_code(i)
3107 , rti_transaction_status_code(i)
3108 , rti_employee_id(i)
3109 , rti_auto_transact_code(i)
3110 , rti_receipt_source_code(i)
3111 , rti_source_document_code(i)
3112 , rti_parent_transaction_id(i)
3113 , rti_po_header_id(i)
3114 , rti_po_line_id(i)
3115 , rti_po_line_location_id(i)
3116 , rti_po_distribution_id(i)
3117 , rti_project_id(i)
3118 , rti_task_id(i)
3119 , rti_expected_receipt_date(i)
3120 , rti_validation_flag(i)
3121 , rti_amount(i)
3122 , rti_job_id(i)
3123 , rti_timecard_id(i)
3124 , rti_timecard_ovn(i)
3125 );
3126
3127 COMMIT;
3128
3129 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3130 , module => l_log_head
3131 , message => 'Inserted ' || rti_interface_transaction_id.COUNT || ' rows into RTI'
3132 );
3133
3134 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
3135 , module => l_log_head
3136 , message => 'End Insert_Interface_Values'
3137 );
3138 END Insert_Interface_Values;
3139
3140 PROCEDURE Capture_Timecard_Info
3141 ( p_block IN HXC_USER_TYPE_DEFINITION_GRP.r_building_blocks
3142 , p_src_attributes IN HXC_USER_TYPE_DEFINITION_GRP.t_time_attribute
3143 , p_att_idx IN OUT NOCOPY BINARY_INTEGER
3144 , p_dst_attributes IN OUT NOCOPY TimecardAttributesRec
3145 ) IS
3146 l_api_name CONSTANT varchar2(30) := 'Capture_Timecard_Info';
3147 l_log_head CONSTANT VARCHAR2(100) := G_LOG_MODULE || '.'||l_api_name;
3148 l_progress varchar2(3) := '000';
3149 BEGIN
3150 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
3151 , module => l_log_head
3152 , message => 'Begin Capture_Timecard_Info'
3153 );
3154
3155 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3156 , module => l_log_head
3157 , message => 'Capturing info from block record for detail bb_id=' || p_block.bb_id || ' ovn=' || p_block.ovn
3158 );
3159
3160 -- add the building block info
3161 p_dst_attributes.detail_bb_id := p_block.bb_id;
3162 p_dst_attributes.detail_bb_ovn := p_block.ovn;
3163 p_dst_attributes.detail_changed := p_block.changed;
3164 p_dst_attributes.detail_deleted := p_block.deleted;
3165 p_dst_attributes.detail_uom := p_block.uom;
3166 p_dst_attributes.detail_start_time := p_block.start_time;
3167 p_dst_attributes.detail_stop_time := p_block.stop_time;
3168 p_dst_attributes.detail_measure := p_block.measure;
3169 p_dst_attributes.resource_id := p_block.resource_id;
3170 p_dst_attributes.day_bb_id := p_block.parent_bb_id;
3171 p_dst_attributes.timecard_bb_id := p_block.timecard_bb_id;
3172 p_dst_attributes.timecard_bb_ovn := p_block.timecard_ovn;
3173 p_dst_attributes.timecard_comment := p_block.comment_text;
3174
3175 -- add timecard info
3176 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3177 , module => l_log_head
3178 , message => 'Deriving timecard info for timecard bb_id=' || p_dst_attributes.timecard_bb_id || ' ovn=' || p_dst_attributes.timecard_bb_ovn
3179 );
3180
3181 -- certain timecard information is not provided so we need to query them up
3182 DECLARE
3183 l_timecard_block HXC_USER_TYPE_DEFINITION_GRP.building_block_info;
3184 BEGIN
3185 l_timecard_block := RCV_HXT_GRP.build_block( p_dst_attributes.timecard_bb_id
3186 , p_dst_attributes.timecard_bb_ovn
3187 );
3188 p_dst_attributes.timecard_start_time := l_timecard_block.start_time;
3189 p_dst_attributes.timecard_stop_time := l_timecard_block.stop_time;
3190
3191 -- these functions are cached so we do not have to cache the results
3192 p_dst_attributes.timecard_approval_date := HXC_INTEGRATION_LAYER_V1_GRP.get_timecard_approval_date( p_timecard_id => p_dst_attributes.timecard_bb_id );
3193 p_dst_attributes.timecard_submission_date := HXC_INTEGRATION_LAYER_V1_GRP.get_timecard_submission_date( p_timecard_id => p_dst_attributes.timecard_bb_id );
3194 p_dst_attributes.timecard_approval_status := HXC_INTEGRATION_LAYER_V1_GRP.get_timecard_approval_status( p_timecard_id => p_dst_attributes.timecard_bb_id );
3195 IF g_debug_stmt THEN
3196 PO_DEBUG.debug_var(l_log_head,l_progress,'timecard_approval_date',
3197 p_dst_attributes.timecard_approval_date);
3198 PO_DEBUG.debug_var(l_log_head,l_progress,'timecard_submission_date',
3199 p_dst_attributes.timecard_submission_date);
3200 PO_DEBUG.debug_var(l_log_head,l_progress,'timecard_approval_status',
3201 p_dst_attributes.timecard_approval_status);
3202 END IF;
3203 END;
3204
3205 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3206 , module => l_log_head
3207 , message => 'Capturing info from attribute records for detail bb_id=' || p_dst_attributes.detail_bb_id || ' ovn=' || p_dst_attributes.detail_bb_ovn
3208 );
3209
3210 -- store the attributes for this building block in a record
3211 WHILE p_att_idx <= p_src_attributes.COUNT AND p_src_attributes(p_att_idx).bb_id = p_dst_attributes.detail_bb_id LOOP
3212 Set_Attribute( p_dst_attributes
3213 , p_src_attributes(p_att_idx).field_name
3214 , p_src_attributes(p_att_idx).value
3215 );
3216 p_att_idx := p_att_idx + 1;
3217 END LOOP;
3218
3219 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
3220 , module => l_log_head
3221 , message => 'End Capture_Timecard_Info'
3222 );
3223 END Capture_Timecard_Info;
3224
3225 PROCEDURE Capture_Old_Timecard_Info
3226 ( p_block IN HXC_USER_TYPE_DEFINITION_GRP.r_building_blocks
3227 , p_src_attributes IN HXC_USER_TYPE_DEFINITION_GRP.t_time_attribute
3228 , p_att_idx IN OUT NOCOPY BINARY_INTEGER
3229 , p_dst_attributes IN OUT NOCOPY TimecardAttributesRec
3230 ) IS
3231 l_api_name CONSTANT varchar2(30) := 'Capture_Old_Timecard_Info';
3232 l_log_head CONSTANT VARCHAR2(100) := G_LOG_MODULE || '.'||l_api_name;
3233 BEGIN
3234 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
3235 , module => l_log_head
3236 , message => 'Begin Capture_Old_Timecard_Info'
3237 );
3238
3239 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3240 , module => l_log_head
3241 , message => 'Capturing info from block record for old detail bb_id=' || p_block.bb_id || ' ovn=' || p_block.ovn
3242 );
3243
3244 -- mark this as an old block
3245 p_dst_attributes.old_block := 'Y';
3246
3247 -- capture relevant info provided in the block
3248 p_dst_attributes.detail_bb_id := p_block.bb_id;
3249 p_dst_attributes.detail_bb_ovn := p_block.ovn;
3250 p_dst_attributes.detail_changed := p_block.changed;
3251 p_dst_attributes.detail_deleted := p_block.deleted;
3252 p_dst_attributes.detail_uom := p_block.uom;
3253 p_dst_attributes.detail_start_time := p_block.start_time;
3254 p_dst_attributes.detail_stop_time := p_block.stop_time;
3255 p_dst_attributes.detail_measure := p_block.measure;
3256 p_dst_attributes.resource_id := p_block.resource_id;
3257
3258 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3259 , module => l_log_head
3260 , message => 'Deriving day/timecard info for old detail bb_id=' || p_dst_attributes.detail_bb_id || ' ovn=' || p_dst_attributes.detail_bb_ovn
3261 );
3262
3263 -- derive day/timecard info for old block
3264 DECLARE
3265 l_detail_block HXC_USER_TYPE_DEFINITION_GRP.building_block_info;
3266 l_day_block HXC_USER_TYPE_DEFINITION_GRP.building_block_info;
3267 BEGIN
3268 l_detail_block := RCV_HXT_GRP.build_block( p_dst_attributes.detail_bb_id
3269 , p_dst_attributes.detail_bb_ovn
3270 );
3271 l_day_block := RCV_HXT_GRP.build_block( l_detail_block.parent_building_block_id
3272 , l_detail_block.parent_building_block_ovn
3273 );
3274
3275 p_dst_attributes.day_bb_id := l_day_block.time_building_block_id;
3276 p_dst_attributes.timecard_bb_id := l_day_block.parent_building_block_id;
3277 p_dst_attributes.timecard_bb_ovn := l_day_block.parent_building_block_ovn;
3278
3279 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3280 , module => l_log_head
3281 , message => 'Derived for old detail block: day_bb_id=' || p_dst_attributes.day_bb_id || ', timecard_bb_id=' || p_dst_attributes.timecard_bb_id || ', timecard_bb_ovn=' || p_dst_attributes.timecard_bb_ovn
3282 );
3283 END;
3284
3285 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3286 , module => l_log_head
3287 , message => 'Capturing info from attribute records for old detail bb_id=' || p_dst_attributes.detail_bb_id || ' ovn=' || p_dst_attributes.detail_bb_ovn
3288 );
3289
3290 -- capture the attributes from the old attributes table
3291 WHILE p_att_idx <= p_src_attributes.COUNT AND p_src_attributes(p_att_idx).bb_id = p_dst_attributes.detail_bb_id LOOP
3292 Set_Attribute( p_dst_attributes
3293 , p_src_attributes(p_att_idx).field_name
3294 , p_src_attributes(p_att_idx).value
3295 );
3296
3297 p_att_idx := p_att_idx + 1;
3298 END LOOP;
3299
3300 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
3301 , module => l_log_head
3302 , message => 'End Capture_Old_Timecard_Info'
3303 );
3304 END Capture_Old_Timecard_Info;
3305
3306 PROCEDURE Query_Timecard_Info
3307 ( p_bb_id IN HXC_TIME_BUILDING_BLOCKS.time_building_block_id%TYPE
3308 , p_bb_ovn IN HXC_TIME_BUILDING_BLOCKS.object_version_number%TYPE
3309 , p_attributes_rec IN OUT NOCOPY TimecardAttributesRec
3310 ) IS
3311 l_api_name CONSTANT varchar2(30) := 'Query_Timecard_Info';
3312 l_log_head CONSTANT VARCHAR2(100) := G_LOG_MODULE || '.'||l_api_name;
3313 l_block HXC_USER_TYPE_DEFINITION_GRP.building_block_info;
3314 l_attributes HXC_USER_TYPE_DEFINITION_GRP.attribute_info;
3315 BEGIN
3316 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3317 , module => l_log_head
3318 , message => 'Querying bb_id=' || p_bb_id || ' bb_ovn=' || p_bb_ovn
3319 );
3320
3321 -- query timecard block
3322 l_block := RCV_HXT_GRP.build_block( p_bb_id
3323 , p_bb_ovn
3324 );
3325
3326 p_attributes_rec.detail_bb_id := l_block.time_building_block_id;
3327 p_attributes_rec.detail_bb_ovn := l_block.object_version_number;
3328 p_attributes_rec.detail_type := l_block.type;
3329 p_attributes_rec.detail_measure := l_block.measure;
3330 p_attributes_rec.detail_uom := l_block.unit_of_measure;
3331 p_attributes_rec.resource_id := l_block.resource_id;
3332 p_attributes_rec.timecard_approval_status := l_block.approval_status;
3333 p_attributes_rec.detail_date_from := l_block.date_from;
3334 p_attributes_rec.detail_date_to := l_block.date_to;
3335
3336 -- manually pull up attributes
3337 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3338 , module => l_log_head
3339 , message => 'Querying timecard attributes'
3340 );
3341
3342 l_attributes := RCV_HXT_GRP.build_attribute( p_bb_id
3343 , p_bb_ovn
3344 , 'PURCHASING'
3345 );
3346 p_attributes_rec.po_number := l_attributes.attribute1;
3347 p_attributes_rec.po_line_id := l_attributes.attribute2;
3348 p_attributes_rec.po_price_type := l_attributes.attribute3;
3349 p_attributes_rec.po_billable_amount := l_attributes.attribute4;
3350 p_attributes_rec.po_receipt_date := FND_DATE.canonical_to_date(l_attributes.attribute5);
3351 p_attributes_rec.po_line := l_attributes.attribute6;
3352 p_attributes_rec.po_price_type_display := l_attributes.attribute7;
3353 p_attributes_rec.po_header_id := l_attributes.attribute8;
3354 EXCEPTION
3355 WHEN OTHERS THEN
3356 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
3357 , module => l_log_head
3358 , message => 'Unexpected exception in Query_Timecard_Info (bb_id=' || p_bb_id || ', bb_ovn=' || p_bb_ovn || '): ' || SQLERRM
3359 );
3360 RAISE;
3361 END Query_Timecard_Info;
3362
3363 PROCEDURE Fail_ROI_Rows
3364 ( failed_timecard_id IN HXC_TIME_BUILDING_BLOCKS.time_building_block_id%TYPE
3365 , p_rti_rows IN OUT NOCOPY rti_table
3366 ) IS
3367 BEGIN
3368 -- we have to search the entire table because we cannot keep the rows in order of timecard_id
3369 FOR i IN 1..p_rti_rows.COUNT LOOP
3370 IF p_rti_rows(i).timecard_id = failed_timecard_id THEN
3371 p_rti_rows(i).processing_status_code := 'ERROR';
3372 p_rti_rows(i).transaction_status_code := 'ERROR';
3373 END IF;
3374 END LOOP;
3375 END Fail_ROI_Rows;
3376
3377 PROCEDURE Retrieve_Timecards_Body
3378 ( p_blocks IN OUT NOCOPY HXC_USER_TYPE_DEFINITION_GRP.t_building_blocks
3379 , p_old_blocks IN OUT NOCOPY HXC_USER_TYPE_DEFINITION_GRP.t_building_blocks
3380 , p_attributes IN OUT NOCOPY HXC_USER_TYPE_DEFINITION_GRP.t_time_attribute
3381 , p_old_attributes IN OUT NOCOPY HXC_USER_TYPE_DEFINITION_GRP.t_time_attribute
3382 , p_receipt_date IN DATE
3383 ) IS
3384 l_processable_rows_exist varchar2(1); --Bug6000903
3385 l_rt_row new_rt_rows%ROWTYPE;
3386 l_rti_status rti_status_table;
3387 l_attributes TimecardAttributesRec;
3388 l_old_attributes TimecardAttributesRec;
3389 l_all_attributes TimecardAttributesTbl;
3390 l_rhi_rows rhi_table;
3391 l_rti_rows rti_table;
3392
3393 blk_idx BINARY_INTEGER;
3394 old_blk_idx BINARY_INTEGER;
3395 att_idx BINARY_INTEGER;
3396 old_att_idx BINARY_INTEGER;
3397
3398 last_blk_idx BINARY_INTEGER;
3399 last_old_blk_idx BINARY_INTEGER;
3400 last_att_idx BINARY_INTEGER;
3401 last_old_att_idx BINARY_INTEGER;
3402
3403 failed_timecard_id HXC_TIME_BUILDING_BLOCKS.time_building_block_id%TYPE;
3404 -- Bug6357273
3405 -- This is added to Propagate the errored Detail block id on those we will get
3406 -- Skipped by this failed Block
3407 failed_detail_block_id HXC_TIME_BUILDING_BLOCKS.time_building_block_id%TYPE;
3408
3409 TRANSACTION_FAILED EXCEPTION;
3410 l_api_name CONSTANT varchar2(30) := 'Retrieve_Timecards_Body';
3411 l_log_head CONSTANT VARCHAR2(100) := G_LOG_MODULE || '.'||l_api_name;
3412 --Bug6357273
3413 -- This temp variable is been added to loop through records when a detail block fails
3414 -- and get the Details blocks for the same timecards which are processed and make them
3415 -- with errors.
3416 l_exp_idx BINARY_INTEGER;
3417 BEGIN
3418 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
3419 , module => l_log_head
3420 , message => 'Begin Retrieve_Timecards_Body'
3421 );
3422 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3423 , module => l_log_head
3424 , message => 'last_blk_idx=' || last_blk_idx || ' last_old_blk_idx=' || last_old_blk_idx || ' last_att_idx=' || last_att_idx || ' last_old att_idx=' || last_old_att_idx
3425 );
3426
3427 -- initialize indexes
3428 blk_idx := 1;
3429 old_blk_idx := 1;
3430 att_idx := 1;
3431 old_att_idx := 1;
3432
3433 last_blk_idx := p_blocks.COUNT;
3434 last_old_blk_idx := p_old_blocks.COUNT;
3435 last_att_idx := p_attributes.COUNT;
3436 last_old_att_idx := p_old_attributes.COUNT;
3437
3438 -- cleanup iSP table by calling reconcile_actions
3439 DECLARE
3440 l_return_status VARCHAR2(100);
3441 l_msg_data VARCHAR2(2000);
3442 BEGIN
3443 -- Making Stages which are been cleared in Retrieval Program which we will help to
3444 -- track the Transaction Status. One Transaction is comman to all the timecards
3445 -- which are been worked upon.
3446 --
3447 -- Need to Verified this message is stored in local variable
3448 -- which might not get into OTL in Retrieval Program Crash...
3449 g_txn_msg := 'Stage 01 - ISP Reconcile Action is been called for First Time';
3450 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3451 , module => l_log_head
3452 , message => 'Calling iSP to reconcile actions'
3453 );
3454
3455 PO_STORE_TIMECARD_PKG_GRP.reconcile_actions( p_api_version => 1.0
3456 , x_return_status => l_return_status
3457 , x_msg_data => l_msg_data
3458 );
3459
3460 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3461 , module => l_log_head
3462 , message => 'Done with iSP reconcile actions'
3463 );
3464
3465 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3466 RAISE ISP_RECONCILE_ACTIONS_FAILED;
3467 END IF;
3468 EXCEPTION
3469 WHEN ISP_RECONCILE_ACTIONS_FAILED THEN
3470 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_EXCEPTION
3471 ,module=>l_log_head
3472 , message => 'iSP reconcile actions failed: x_return_status=' || l_return_status || ' x_msg_data=' || l_msg_data
3473 );
3474 RAISE;
3475 WHEN OTHERS THEN
3476 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
3477 , module => l_log_head
3478 , message => 'Unexpected exception while calling iSP reconcile actions: x_return_status=' || l_return_status || ' x_msg_data=' || l_msg_data || ' sqlerrm=' || SQLERRM
3479 );
3480 RAISE ISP_RECONCILE_ACTIONS_FAILED;
3481 END;
3482
3483 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3484 , module => l_log_head
3485 , message => 'Starting main loop through detail building blocks...'
3486 );
3487
3488 -- loop through the detail building blocks
3489 g_txn_msg := 'Stage 02 - Start Processing the Blocks one by one before inserting in ROI';
3490 WHILE blk_idx <= last_blk_idx LOOP
3491 BEGIN
3492 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3493 , module => l_log_head
3494 , message => '|------------------ blk_idx = ' || blk_idx || ' ------------------|'
3495 );
3496 --Bug6357273
3497 -- We are trying to keep track of every block which is getting processes while the program runs
3498 -- such that we can populate this message to OTL, In case of failure we will be to track the
3499 -- Flow for that specific Detail Block.
3500
3501 --Bug6357273
3502 /* Making every record initially as error, Because when OTL passes the Data to Global PL SQL
3503 they mark the records as IN PROGRESS, then by any changes if the Retrieval Program crashes
3504 without Completing then this records left in IN PROGRESS only which latter on are not Picked
3505 for any futher Retrieval.
3506 Also will be overwritting this status, when we get success for the Blocks. This will make the
3507 excpetion DETAIL_NOT_PROCESSED unused as there will no records which will have t_tx_detail_status
3508 as NULL.
3509 This will insure that even if we encounter unexpected error then blocks will not remain as
3510 IN PROGRESS. */
3511 HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_status(blk_idx) := 'ERROR';
3512 HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(blk_idx) := 'PO: Start Processing the Block';
3513
3514
3515 -- skip unapproved timecards
3516 WHILE blk_idx <= last_blk_idx AND
3517 HXC_INTEGRATION_LAYER_V1_GRP.get_timecard_approval_status( p_blocks(blk_idx).bb_id ) <> 'APPROVED'
3518 LOOP
3519 HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_status(blk_idx) := 'ERRORS';
3520 HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(blk_idx) := 'Timecard not approved';
3521
3522 skip_block( p_blocks
3523 , blk_idx
3524 , p_old_blocks
3525 , old_blk_idx
3526 , p_attributes
3527 , att_idx
3528 , p_old_attributes
3529 , old_att_idx
3530 );
3531 END LOOP;
3532
3533 -- check that we have not skipped everything left
3534 EXIT WHEN blk_idx > last_blk_idx;
3535
3536 -- clear the attribute map
3537 l_attributes := NULL;
3538 l_old_attributes := NULL;
3539 --Bug6357273
3540 HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(blk_idx) := 'PO-20- Capture_Timecard_Info - Start';
3541
3542 Capture_Timecard_Info( p_block => p_blocks(blk_idx)
3543 , p_src_attributes => p_attributes
3544 , p_att_idx => att_idx
3545 , p_dst_attributes => l_attributes
3546 );
3547 --Bug6357273
3548 HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(blk_idx) := 'PO-30- Capture_Timecard_Info - End';
3549
3550 -- add old block info for changed blocks
3551 IF p_blocks(blk_idx).changed = 'Y' THEN
3552 --Bug6357273
3553 HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(blk_idx) := 'PO-40- Capture_Old_Timecard_Info - Start';
3554 Capture_Old_Timecard_Info( p_block => p_old_blocks(old_blk_idx)
3555 , p_src_attributes => p_old_attributes
3556 , p_att_idx => old_att_idx
3557 , p_dst_attributes => l_old_attributes
3558 );
3559
3560 --Bug6357273
3561 HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(blk_idx) := 'PO-50- Capture_Old_Timecard_Info - End';
3562
3563 -- maintain the old block index
3564 old_blk_idx := old_blk_idx + 1;
3565 END IF;
3566 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3567 , module => l_log_head
3568 , message => 'fill the po_distribution_id and line_location_id for NEW attributes'
3569 );
3570 --Bug6357273
3571 HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(blk_idx) := 'PO-60- Get the PO Line - Start';
3572 -- add PO shipment and distribution information
3573 l_attributes.po_line_location_id := get_po_line(l_attributes.po_line_id).line_location_id;
3574 HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(blk_idx) := 'PO-70- Get the PO Distribution for the line : '||
3575 l_attributes.po_line_id ||
3576 ' Project : '||l_attributes.project_id||
3577 ' Task : '||l_attributes.task_id||
3578 ' - Start';
3579
3580 l_attributes.po_distribution_id := get_po_distribution( l_attributes.po_line_id
3581 , l_attributes.project_id
3582 , l_attributes.task_id
3583 ).po_distribution_id;
3584 HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(blk_idx) := 'PO-80- Get the PO Distribution End';
3585 -- bug 5976883
3586 -- need to add the shipment and distribution information to the old attributes block also
3587 -- This is required since in the derive_delete_values we build the rti based on the old_attributes
3588 -- only and the rti does not contain the dist, shipment_id values.
3589 -- Hence get_rti_idx fails in case of a po-projects OTL setup, since in this case we use distribution_id
3590 -- to get the rti_idx, before calling PO_STORE_TIMECARD_PKG_GRP.store_timecard_details
3591 IF p_blocks(blk_idx).changed = 'Y' THEN
3592 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3593 , module => l_log_head
3594 , message => 'fill the po_distribution_id and line_location_id for old attributes'
3595 );
3596 HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(blk_idx) := 'PO-90- Get the PO line for Old block - Start';
3597 l_old_attributes.po_line_location_id := get_po_line(l_old_attributes.po_line_id).line_location_id;
3598 HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(blk_idx) := 'PO-100- Get the PO Distribution for the Old line : '||
3599 l_old_attributes.po_line_id ||
3600 ' Project : '||l_old_attributes.project_id||
3601 ' Task : '||l_old_attributes.task_id||
3602 ' - Start';
3603 l_old_attributes.po_distribution_id := get_po_distribution( l_old_attributes.po_line_id
3604 , l_old_attributes.project_id
3605 , l_old_attributes.task_id
3606 ).po_distribution_id;
3607 HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(blk_idx) := 'PO-110- Get the Old PO Distribution End';
3608
3609 END IF;
3610
3611 -- set the receipt date
3612 l_attributes.po_receipt_date := p_receipt_date;
3613
3614 -- derive roi field values from timecard attributes
3615 HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(blk_idx) := 'PO-120- Calculate the RTI values - Start';
3616 Derive_Interface_Values(l_attributes, l_old_attributes, l_rhi_rows, l_rti_rows);
3617 HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(blk_idx) := 'PO-130- Calculate the RTI values - End';
3618 -- add attributes to big table for use later
3619 l_all_attributes(l_attributes.detail_bb_id) := l_attributes;
3620
3621 -- advance the block loop counter
3622 blk_idx := blk_idx + 1;
3623 EXCEPTION
3624 WHEN OTHERS THEN
3625 --Bug6357273
3626 -- save the exception description before we lose it and upending the message with the
3627 -- Status message for that block.
3628 HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_status(blk_idx) := 'ERRORS';
3629 HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(blk_idx) := HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(blk_idx)
3630 || 'Unexpected exception while processing results from Generic Retrieval: ' || SQLERRM;
3631 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
3632 , module => l_log_head
3633 , message =>HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(blk_idx)
3634 );
3635
3636 -- When a detail fails, we need to fail the entire timecard.
3637 -- The detail blocks are ordered by day, and timecard, so we only need to search adjacent detail blocks
3638 failed_timecard_id := p_blocks(blk_idx).timecard_bb_id;
3639 -- Bug6357273
3640 -- This is added to Propagate the errored Detail block id on those we will get
3641 -- Skipped by this failed Block
3642 failed_detail_block_id := p_blocks(blk_idx).bb_id;
3643
3644 -- Those that came before can be aborted by setting the ROI status codes to ERROR
3645
3646
3647 Fail_ROI_Rows( failed_timecard_id, l_rti_rows );
3648 --Bug6357273
3649 --Even the previous time card blocks which are processed so far need to be skipped and stamped with appropriate error message
3650 l_exp_idx := blk_idx - 1;
3651 WHILE l_exp_idx >= 1 AND p_blocks(l_exp_idx).timecard_bb_id = failed_timecard_id LOOP
3652 HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_status(l_exp_idx) := 'ERRORS';
3653 HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(l_exp_idx) := 'Skipped because detail block '||failed_detail_block_id||' in the same timecard failed';
3654 l_exp_idx := l_exp_idx -1;
3655 END LOOP;
3656
3657 -- Those that will come after can be aborted by simply skipping over them
3658 blk_idx := blk_idx + 1;
3659 WHILE blk_idx <= last_blk_idx AND p_blocks(blk_idx).timecard_bb_id = failed_timecard_id LOOP
3660 -- set the error description while we still know what happened
3661 HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_status(blk_idx) := 'ERRORS';
3662 HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(blk_idx) := 'Skipped because detail block '||failed_detail_block_id||' in the same timecard failed';
3663
3664 skip_block( p_blocks
3665 , blk_idx
3666 , p_old_blocks
3667 , old_blk_idx
3668 , p_attributes
3669 , att_idx
3670 , p_old_attributes
3671 , old_att_idx
3672 );
3673 END LOOP;
3674 END;
3675 END LOOP;
3676
3677 -- bug 6000903: check if any processable rows exists. Launch RTP only if
3678 -- there are processable rows
3679 l_processable_rows_exist := 'N';
3680 g_txn_msg := 'Stage 03 - Done with Capture TimeCard Block Process, going to insert valid one to Receiving interface table';
3681 set_rhi_table_status(l_rhi_rows, l_rti_rows,l_processable_rows_exist);
3682
3683 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3684 , module => l_log_head
3685 , message => 'l_processable_rows_exist' || l_processable_rows_exist
3686 );
3687
3688 if l_processable_rows_exist ='Y' THEN
3689 --IF l_rti_rows.COUNT > 0 THEN
3690 -- Bug6343206
3691 -- Reverting the changes done for BUG3550333 [115.69]
3692 -- We are allowing the zero amount receipts to be created as of now.
3693 -- There will be no entry going in as SUCCESS. so we need no track
3694 -- those block by l_rti_status.
3695 -- insert the ROI rows into the database
3696 -- Insert_Interface_Values(l_rhi_rows, l_rti_rows, l_rti_status);
3697
3698 Insert_Interface_Values(l_rhi_rows, l_rti_rows);
3699
3700 -- call the receiving transaction processor
3701 DECLARE
3702 l_phase VARCHAR2(240);
3703 l_status VARCHAR2(240);
3704 l_dev_phase VARCHAR2(240);
3705 l_dev_status VARCHAR2(240);
3706 l_message VARCHAR2(240);
3707 l_success BOOLEAN;
3708
3709 l_return_code NUMBER;
3710 l_timeout NUMBER := 300;
3711 l_outcome VARCHAR2(240);
3712
3713 RVCTP_FAILED EXCEPTION;
3714 BEGIN
3715 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3716 , module => l_log_head
3717 , message => 'Calling Receiving Transaction Processor with group_id=' || g_group_id
3718 );
3719
3720 g_receiving_start := SYSDATE;
3721
3722 g_req_id := FND_REQUEST.SUBMIT_REQUEST( application => 'PO'
3723 , program => 'RVCTP'
3724 , description => 'Receiving Transaction Processor called by Retrieve Time from OTL'
3725 , argument1 => 'BATCH'
3726 , argument2 => g_group_id
3727 );
3728
3729 IF (g_req_id = 0) THEN
3730 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_EXCEPTION
3731 , module => l_log_head
3732 , message => 'Concurrent request submission failed'
3733 );
3734
3735 RAISE RVCTP_FAILED;
3736 ELSE
3737 COMMIT;
3738 END IF;
3739
3740 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3741 , module => l_log_head
3742 , message => 'Request ID for RVCTP: ' || TO_CHAR(g_req_id)
3743 );
3744
3745 l_success := FND_CONCURRENT.WAIT_FOR_REQUEST( request_id => g_req_id
3746 , interval => 15
3747 , max_wait => 0
3748 , phase => l_phase
3749 , status => l_status
3750 , dev_phase => l_dev_phase
3751 , dev_status => l_dev_status
3752 , message => l_message
3753 );
3754
3755 g_receiving_stop := SYSDATE;
3756 g_receiving_time := g_receiving_time + ( g_receiving_stop - g_receiving_start );
3757
3758 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3759 , module => l_log_head
3760 , message => 'RVCTP done: ' || '*' || l_phase || '*' || l_status || '*' || l_dev_phase || '*' || l_dev_status || '*' || l_message || '*'
3761 );
3762
3763 IF NOT l_success THEN
3764 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_EXCEPTION
3765 , module => l_log_head
3766 , message => 'Receiving Transaction Processor returned false.'
3767 );
3768
3769 RAISE RVCTP_FAILED;
3770 END IF;
3771 EXCEPTION
3772 WHEN RVCTP_FAILED THEN
3773 G_CONC_LOG := G_CONC_LOG || FND_MESSAGE.get_string('PO', 'RCV_OTL_RCVTP_FAIL')
3774 || FND_GLOBAL.local_chr(10) || FND_GLOBAL.local_chr(10);
3775
3776 ROLLBACK;
3777
3778 --
3779 UPDATE rcv_transactions_interface
3780 SET transaction_status_code = 'ERROR'
3781 WHERE group_id = g_group_id
3782 AND transaction_status_code = 'RUNNING';
3783
3784 COMMIT;
3785
3786 WHEN OTHERS THEN
3787 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_EXCEPTION
3788 , module => l_log_head
3789 , message => 'Exception trying to run Receiving Transaction Processor: ' || SQLERRM
3790 );
3791 RAISE TRANSACTION_FAILED;
3792 END;
3793 END IF;
3794
3795 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3796 , module => l_log_head
3797 , message => 'Finding successful receiving transactions'
3798 );
3799 g_txn_msg := 'Stage 04 - RTP is Sucess, Looping through RCV Transaction for Getting Success Records.';
3800
3801 -- get the successful transactions we just created
3802 FOR l_rt_row IN new_rt_rows( l_rti_rows(1).group_id ) LOOP
3803 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3804 , module => l_log_head
3805 , message => 'Successful RTI id ' || l_rt_row.interface_transaction_id
3806 );
3807 l_rti_status(l_rt_row.interface_transaction_id) := 1;
3808 END LOOP;
3809
3810 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3811 , module => l_log_head
3812 , message => 'Setting Detail Statuses'
3813 );
3814
3815 -- update detail statuses
3816 FOR blk_idx IN 1..last_blk_idx LOOP
3817 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3818 , module => l_log_head
3819 , message => 'Setting Detail Status for blk_idx=' || blk_idx || ' bb_id=' || p_blocks(blk_idx).bb_id
3820 );
3821
3822 DECLARE
3823 l_bb_id HXC_TIME_BUILDING_BLOCKS.time_building_block_id%TYPE;
3824 l_transaction_type VARCHAR2(100);
3825 l_receive_rti_id RCV_TRANSACTIONS_INTERFACE.interface_transaction_id%TYPE;
3826 l_deliver_rti_id RCV_TRANSACTIONS_INTERFACE.interface_transaction_id%TYPE;
3827 l_delete_receive_rti_id RCV_TRANSACTIONS_INTERFACE.interface_transaction_id%TYPE;
3828 l_delete_deliver_rti_id RCV_TRANSACTIONS_INTERFACE.interface_transaction_id%TYPE;
3829 l_message VARCHAR2(1000);
3830 DETAIL_NOT_PROCESSED EXCEPTION;
3831 BEGIN
3832 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3833 , module => l_log_head
3834 , message => 'Finding RTI ids'
3835 );
3836
3837 l_bb_id := p_blocks(blk_idx).bb_id;
3838
3839 IF NOT l_all_attributes.EXISTS(l_bb_id) THEN
3840 RAISE DETAIL_NOT_PROCESSED;
3841 END IF;
3842
3843 l_transaction_type := l_all_attributes(l_bb_id).transaction_type;
3844 l_receive_rti_id := l_all_attributes(l_bb_id).receive_rti_id;
3845 l_deliver_rti_id := l_all_attributes(l_bb_id).deliver_rti_id;
3846 l_delete_receive_rti_id := l_all_attributes(l_bb_id).delete_receive_rti_id;
3847 l_delete_deliver_rti_id := l_all_attributes(l_bb_id).delete_deliver_rti_id;
3848
3849 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3850 , module => l_log_head
3851 , message => 'Found bb_id=' || l_bb_id
3852 || ' transaction_type=' || l_transaction_type
3853 || ' receive_rti_id=' || l_receive_rti_id
3854 || ' deliver_rti_id=' || l_deliver_rti_id
3855 || ' delete_receive_rti_id=' || l_delete_receive_rti_id
3856 || ' delete_deliver_rti_id=' || l_delete_deliver_rti_id
3857 );
3858
3859 IF (l_transaction_type = 'RECEIVE'
3860 AND l_rti_status.EXISTS(l_receive_rti_id))
3861 OR (l_transaction_type = 'CORRECT'
3862 AND l_rti_status.EXISTS(l_receive_rti_id)
3863 AND l_rti_status.EXISTS(l_deliver_rti_id))
3864 OR (l_transaction_type = 'DELETE'
3865 AND l_rti_status.EXISTS(l_delete_receive_rti_id)
3866 AND l_rti_status.EXISTS(l_delete_deliver_rti_id))
3867 OR (l_transaction_type = 'DELETE RECEIVE'
3868 AND l_rti_status.EXISTS(l_receive_rti_id)
3869 AND l_rti_status.EXISTS(l_delete_receive_rti_id)
3870 AND l_rti_status.EXISTS(l_delete_deliver_rti_id))
3871 OR (l_transaction_type = 'DELETE CORRECT'
3872 AND l_rti_status.EXISTS(l_receive_rti_id)
3873 AND l_rti_status.EXISTS(l_deliver_rti_id)
3874 AND l_rti_status.EXISTS(l_delete_receive_rti_id)
3875 AND l_rti_status.EXISTS(l_delete_deliver_rti_id)) THEN
3876
3877 HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_status(blk_idx) := 'SUCCESS';
3878 g_successful_details := g_successful_details + 1;
3879
3880 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3881 , module => l_log_head
3882 , message => 'Detail success'
3883 );
3884
3885 ELSE
3886 HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_status(blk_idx) := 'ERRORS';
3887 -- BUG6357273
3888 -- Propogating RTP errors back to the OTL.
3889 -- Logic : We are looping through all the records from rcv_transaction_interface
3890 -- for the Transaction Line id which is not there in the l_rti_status table which
3891 -- is means this records have failed and can have RTP Error Message.
3892 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3893 , module => l_log_head
3894 , message => 'Stamping RTP errors to the Timecards '
3895 );
3896
3897 FOR rec IN (SELECT error_message_name ||' : '|| error_message msg
3898 FROM po_interface_errors
3899 WHERE interface_line_id IN (l_receive_rti_id,l_deliver_rti_id,
3900 l_delete_receive_rti_id,l_delete_deliver_rti_id)
3901 AND table_name = 'RCV_TRANSACTIONS_INTERFACE') LOOP
3902
3903 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3904 , module => l_log_head
3905 , message => 'RTP errors : ' || rec.msg
3906 );
3907
3908 l_message := l_message || rec.msg;
3909 END LOOP;
3910
3911 IF HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(blk_idx) IS NULL THEN
3912 HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(blk_idx) := 'Receipt not created : '||l_message;
3913 ELSE
3914 HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(blk_idx) := HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(blk_idx) || ' Receipt not created : '||l_message;
3915 END IF;
3916 g_failed_details := g_failed_details + 1;
3917
3918 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3919 , module => l_log_head
3920 , message => 'Detail error '||HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(blk_idx)
3921 );
3922 END IF;
3923 EXCEPTION
3924 WHEN DETAIL_NOT_PROCESSED THEN
3925 -- only set generic message if not already set when ignoring block
3926 IF HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_status(blk_idx) IS NULL THEN
3927 HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_status(blk_idx) := 'ERRORS';
3928 HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(blk_idx) := 'Detail block not processed';
3929 ELSE
3930 HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(blk_idx) := HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(blk_idx)
3931 || ' Detail block not processed';
3932 END IF;
3933 g_failed_details := g_failed_details + 1;
3934
3935 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3936 , module => l_log_head
3937 , message => 'Detail block not processed'
3938 );
3939
3940 WHEN OTHERS THEN
3941 HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_status(blk_idx) := 'ERRORS';
3942 IF HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(blk_idx) IS NULL THEN
3943 HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(blk_idx) := 'Unexpected exception while checking receipt
3944 for detail block: ' || SQLERRM;
3945 ELSE
3946 HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception(blk_idx) := HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_exception
3947 (blk_idx) || 'Exception while checking receipt for detail block: ' || SQLERRM;
3948 END IF;
3949 g_failed_details := g_failed_details + 1;
3950
3951 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
3952 , module => l_log_head
3953 , message => 'Unexpected exception while checking receipt for detail block: ' || SQLERRM
3954 );
3955 END;
3956 END LOOP;
3957 g_txn_msg := 'Stage 05 - Processed the Records for success/ERROR Transaction. Going for ISP reconcile Action' ;
3958 DECLARE
3959 l_return_status VARCHAR2(100);
3960 l_msg_data VARCHAR2(2000);
3961
3962 ISP_RECONCILE_ACTIONS_FAILED EXCEPTION;
3963 BEGIN
3964 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3965 , module => l_log_head
3966 , message => 'Calling iSP to reconcile actions'
3967 );
3968
3969 PO_STORE_TIMECARD_PKG_GRP.reconcile_actions( p_api_version => 1.0
3970 , x_return_status => l_return_status
3971 , x_msg_data => l_msg_data
3972 );
3973
3974 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3975 , module => l_log_head
3976 , message => 'Done with iSP reconcile actions'
3977 );
3978
3979 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3980 RAISE ISP_RECONCILE_ACTIONS_FAILED;
3981 END IF;
3982 EXCEPTION
3983 WHEN ISP_RECONCILE_ACTIONS_FAILED THEN
3984 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_EXCEPTION
3985 , module => l_log_head
3986 , message => 'iSP reconcile actions failed: x_return_status=' || l_return_status || ' x_msg_data=' || l_msg_data
3987 );
3988 WHEN OTHERS THEN
3989 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
3990 , module => l_log_head
3991 , message => 'Unexpected exception while calling iSP reconcile actions: x_return_status=' || l_return_status || ' x_msg_data=' || l_msg_data || ' sqlerrm=' || SQLERRM
3992 );
3993 END;
3994
3995 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
3996 , module => l_log_head
3997 , message => 'Setting Day/Timecard Statuses'
3998 );
3999
4000 -- update status for day and timecard building blocks
4001 HXC_INTEGRATION_LAYER_V1_GRP.set_parent_statuses;
4002
4003 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
4004 , module => l_log_head
4005 , message => 'Setting Transaction Status'
4006 );
4007
4008 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
4009 , module => l_log_head
4010 , message => 'RT status count = ' || l_rti_status.COUNT
4011 );
4012
4013 -- if every block was in error, the transaction failed
4014 IF l_rti_status.COUNT = 0 THEN
4015 g_txn_status := 'ERRORS';
4016 g_overall_status := 'ERRORS';
4017 g_txn_msg := g_txn_msg || 'No Receiving Transaction created';
4018 ELSE
4019 g_txn_status := 'SUCCESS';
4020 g_txn_msg := 'Receiving Transactions created';
4021 END IF;
4022
4023 HXC_INTEGRATION_LAYER_V1_GRP.update_transaction_status (
4024 p_process => 'Purchasing Retrieval Process'
4025 , p_status => g_txn_status
4026 , p_exception_description => g_txn_msg
4027 );
4028
4029 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
4030 , module => l_log_head
4031 , message => 'Retrieval Transaction ' || g_txn_status || ': ' || g_txn_msg
4032 );
4033
4034 COMMIT;
4035
4036 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
4037 , module => l_log_head
4038 , message => 'Transaction committed'
4039 );
4040
4041 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
4042 , module => l_log_head
4043 , message => 'End Retrieve_Timecards_Body'
4044 );
4045 EXCEPTION
4046 WHEN DEBUGGING_BREAKPOINT THEN
4047 -- Bug6357273.
4048 -- In the case also we need to propogate message back to OTL
4049 -- Which will help OTL to debug by the records got failed
4050 ROLLBACK;
4051 HXC_INTEGRATION_LAYER_V1_GRP.set_parent_statuses;
4052 HXC_INTEGRATION_LAYER_V1_GRP.update_transaction_status (
4053 p_process => 'Purchasing Retrieval Process'
4054 , p_status => 'ERRORS'
4055 , p_exception_description => g_txn_msg || 'Hit Breakpoint. Ending process in error, because we are still debugging.'
4056 );
4057 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_EXCEPTION
4058 , module => l_log_head
4059 , message => g_txn_msg || 'Hit Breakpoint. Ending process in error, because we are still debugging.'
4060 );
4061 COMMIT;
4062 RAISE RETRIEVAL_FAILED;
4063 WHEN ISP_RECONCILE_ACTIONS_FAILED THEN
4064 -- Bug6357273.
4065 -- In the case also we need to propogate message back to OTL
4066 -- Which will help OTL to debug by the records got failed
4067 ROLLBACK;
4068 HXC_INTEGRATION_LAYER_V1_GRP.set_parent_statuses;
4069 -- this is only raised in the first call to reconcile actions. the later one is not fatal.
4070 HXC_INTEGRATION_LAYER_V1_GRP.update_transaction_status (
4071 p_process => 'Purchasing Retrieval Process'
4072 , p_status => 'ERRORS'
4073 , p_exception_description => g_txn_msg || 'Error calling Reconcile_Actions, please see FND_LOG_MESSAGES for details'
4074 );
4075 COMMIT;
4076 RAISE RETRIEVAL_FAILED;
4077 WHEN ISP_STORE_TIMECARD_FAILED THEN
4078 -- Bug6357273.
4079 -- In the case also we need to propogate message back to OTL
4080 -- Which will help OTL to debug by the records got failed
4081 ROLLBACK;
4082 HXC_INTEGRATION_LAYER_V1_GRP.set_parent_statuses;
4083 HXC_INTEGRATION_LAYER_V1_GRP.update_transaction_status (
4084 p_process => 'Purchasing Retrieval Process'
4085 , p_status => 'ERRORS'
4086 , p_exception_description => g_txn_msg || 'Error in Retrieve_Timecards_Body, please see FND_LOG_MESSAGES for details'
4087 );
4088 COMMIT;
4089 RAISE RETRIEVAL_FAILED;
4090 WHEN TRANSACTION_FAILED THEN
4091 -- Bug6357273.
4092 -- In the case also we need to propogate message back to OTL
4093 -- Which will help OTL to debug by the records got failed
4094 ROLLBACK;
4095 HXC_INTEGRATION_LAYER_V1_GRP.set_parent_statuses;
4096 HXC_INTEGRATION_LAYER_V1_GRP.update_transaction_status (
4097 p_process => 'Purchasing Retrieval Process'
4098 , p_status => 'ERRORS'
4099 , p_exception_description => 'Error in Retrieve_Timecards_Body, please see FND_LOG_MESSAGES for details'
4100 );
4101 COMMIT;
4102 RAISE RETRIEVAL_FAILED;
4103 WHEN OTHERS THEN
4104 -- Bug6357273.
4105 -- In the case also we need to propogate message back to OTL
4106 -- Which will help OTL to debug by the records got failed
4107 ROLLBACK;
4108 HXC_INTEGRATION_LAYER_V1_GRP.set_parent_statuses;
4109
4110
4111 HXC_INTEGRATION_LAYER_V1_GRP.update_transaction_status (
4112 p_process => 'Purchasing Retrieval Process'
4113 , p_status => 'ERRORS'
4114 , p_exception_description => SUBSTR(g_txn_msg || 'Unexpected exception in Retrieve_Timecards_Body: ' || SQLERRM, 1, 2000)
4115 );
4116 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
4117 , module => l_log_head
4118 , message => SUBSTR(g_txn_msg || 'Unexpected exception in Retrieve_Timecards_Body: ' || SQLERRM, 1, 2000)
4119 );
4120 COMMIT;
4121 RAISE RETRIEVAL_FAILED;
4122 END Retrieve_Timecards_Body;
4123
4124 -- Public callbacks
4125 FUNCTION Purchasing_Retrieval_Process RETURN VARCHAR2 IS
4126 l_retrieval_process HXC_TIME_RECIPIENTS.application_retrieval_function%TYPE;
4127 BEGIN
4128 l_retrieval_process := 'Purchasing Retrieval Process';
4129 RETURN l_retrieval_process;
4130 END Purchasing_Retrieval_Process;
4131
4132 -- errors/warnings cannot be raised during update
4133 PROCEDURE Update_Timecard( p_operation IN VARCHAR2 )
4134 IS
4135 l_bb_id HXC_TIME_BUILDING_BLOCKS.time_building_block_id%TYPE;
4136
4137 l_all_attributes TimecardAttributesTbl;
4138
4139 l_blocks HXC_USER_TYPE_DEFINITION_GRP.timecard_info;
4140 l_attributes HXC_USER_TYPE_DEFINITION_GRP.app_attributes_info;
4141 l_messages HXC_USER_TYPE_DEFINITION_GRP.message_table;
4142 l_api_name CONSTANT varchar2(30) := 'Update_Timecard';
4143 l_log_head CONSTANT VARCHAR2(100) := G_LOG_MODULE || '.'||l_api_name;
4144 BEGIN
4145 g_update_start := SYSDATE;
4146 initialize_cache_statistics;
4147 initialize_caches;
4148
4149 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
4150 , module => l_log_head
4151 , message => 'Begin Update_Timecard'
4152 );
4153
4154 -- get time information
4155 HXC_INTEGRATION_LAYER_V1_GRP.get_app_hook_params(
4156 p_building_blocks => l_blocks,
4157 p_app_attributes => l_attributes,
4158 p_messages => l_messages);
4159
4160 -- sort the attributes by bb_id
4161 Sort_Attributes( p_all_attributes => l_all_attributes
4162 , p_raw_attributes => l_attributes
4163 );
4164
4165 -- loop through the detail blocks to process them with the attributes
4166 FOR blk_idx IN 1..l_blocks.COUNT LOOP
4167 IF l_blocks(blk_idx).scope = 'DETAIL' THEN
4168 l_bb_id := l_blocks(blk_idx).time_building_block_id;
4169
4170 -- add some block properties
4171 l_all_attributes(l_bb_id).detail_measure := l_blocks(blk_idx).measure;
4172
4173 -- modify the attributes as necessary
4174 Update_Attributes(l_all_attributes(l_bb_id), l_messages);
4175 END IF;
4176 END LOOP;
4177
4178 -- go through the attributes again to save the data
4179 FOR att_idx IN 1..l_attributes.COUNT LOOP
4180 l_bb_id := l_attributes(att_idx).building_block_id;
4181
4182 IF l_attributes(att_idx).attribute_name = 'PO Number' THEN
4183 l_attributes(att_idx).attribute_value := l_all_attributes(l_bb_id).po_number;
4184 ELSIF l_attributes(att_idx).attribute_name = 'PO Header Id' THEN
4185 l_attributes(att_idx).attribute_value := l_all_attributes(l_bb_id).po_header_id;
4186 ELSIF l_attributes(att_idx).attribute_name = 'PO Line Number' THEN
4187 l_attributes(att_idx).attribute_value := l_all_attributes(l_bb_id).po_line;
4188 ELSIF l_attributes(att_idx).attribute_name = 'PO Line Id' THEN
4189 l_attributes(att_idx).attribute_value := l_all_attributes(l_bb_id).po_line_id;
4190 ELSIF l_attributes(att_idx).attribute_name = 'PO Price Type' THEN
4191 l_attributes(att_idx).attribute_value := l_all_attributes(l_bb_id).po_price_type;
4192 ELSIF l_attributes(att_idx).attribute_name = 'PO Price Type Display' THEN
4193 l_attributes(att_idx).attribute_value := l_all_attributes(l_bb_id).po_price_type_display;
4194 ELSIF l_attributes(att_idx).attribute_name = 'PO Billable Amount' THEN
4195 l_attributes(att_idx).attribute_value := l_all_attributes(l_bb_id).po_billable_amount;
4196 ELSIF l_attributes(att_idx).attribute_name = 'PO Receipt Date' THEN
4197 l_attributes(att_idx).attribute_value := FND_DATE.date_to_canonical(l_all_attributes(l_bb_id).po_receipt_date);
4198 END IF;
4199 END LOOP;
4200
4201 -- set time information
4202 HXC_INTEGRATION_LAYER_V1_GRP.set_app_hook_params(
4203 p_building_blocks => l_blocks,
4204 p_app_attributes => l_attributes,
4205 p_messages => l_messages);
4206
4207 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
4208 , module => l_log_head
4209 , message => 'End Update_Timecard'
4210 );
4211
4212 g_update_stop := SYSDATE;
4213 END Update_Timecard;
4214
4215 PROCEDURE Validate_Timecard( p_operation IN VARCHAR2 )
4216 IS
4217 l_all_attributes TimecardAttributesTbl;
4218 l_old_attributes TimecardAttributesTbl;
4219 l_attributes_rec TimecardAttributesRec;
4220 l_old_attributes_rec TimecardAttributesRec;
4221 l_bb_id HXC_TIME_BUILDING_BLOCKS.time_building_block_id%TYPE;
4222
4223 l_blocks HXC_USER_TYPE_DEFINITION_GRP.timecard_info;
4224 l_attributes HXC_USER_TYPE_DEFINITION_GRP.app_attributes_info;
4225 l_messages HXC_USER_TYPE_DEFINITION_GRP.message_table;
4226 l_api_name CONSTANT varchar2(30) := 'Validate_Timecard';
4227 l_log_head CONSTANT VARCHAR2(100) := G_LOG_MODULE || '.'||l_api_name;
4228 BEGIN
4229 g_validate_start := SYSDATE;
4230 initialize_cache_statistics;
4231
4232 /* Bug 5401262: Procedure Validate_Amount_Tolerances() validates all rows in po
4233 cache. We need to clear cache to prevent false errors/warnings */
4234 g_po_line_cache.delete;
4235
4236 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
4237 , module => l_log_head
4238 , message => 'Begin Validate_Timecard'
4239 );
4240
4241 -- get time information
4242 HXC_INTEGRATION_LAYER_V1_GRP.get_app_hook_params(
4243 p_building_blocks => l_blocks,
4244 p_app_attributes => l_attributes,
4245 p_messages => l_messages);
4246
4247 Sort_Attributes( p_all_attributes => l_all_attributes
4248 , p_raw_attributes => l_attributes
4249 );
4250
4251 -- loop through the blocks to capture the block properties in the attribute map
4252 FOR blk_idx IN 1..l_blocks.COUNT LOOP
4253 l_bb_id := l_blocks(blk_idx).time_building_block_id;
4254
4255 -- make a local working copy
4256 l_attributes_rec := l_all_attributes(l_bb_id);
4257
4258 -- add some relevant bb info as attributes
4259 IF l_blocks(blk_idx).scope = 'DETAIL' THEN
4260 l_attributes_rec.detail_bb_id := l_bb_id;
4261 l_attributes_rec.detail_bb_ovn := l_blocks(blk_idx).object_version_number;
4262 l_attributes_rec.detail_type := l_blocks(blk_idx).type;
4263 l_attributes_rec.detail_changed := l_blocks(blk_idx).changed;
4264 l_attributes_rec.detail_new := l_blocks(blk_idx).new;
4265 l_attributes_rec.detail_measure := l_blocks(blk_idx).measure;
4266 l_attributes_rec.detail_uom := l_blocks(blk_idx).unit_of_measure;
4267 l_attributes_rec.resource_id := l_blocks(blk_idx).resource_id;
4268 l_attributes_rec.old_block := 'N';
4269
4270 -- emulate the deleted attribute
4271 IF l_blocks(blk_idx).date_to <> HR_GENERAL.end_of_time THEN
4272 l_attributes_rec.detail_deleted := 'Y';
4273 ELSE
4274 l_attributes_rec.detail_deleted := 'N';
4275 END IF;
4276
4277 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
4278 , module => l_log_head
4279 , message => 'Detail block flags *'
4280 || l_attributes_rec.detail_changed || '*'
4281 || l_attributes_rec.detail_new || '*'
4282 || l_attributes_rec.detail_deleted || '*'
4283 );
4284
4285 -- capture old block/attr info if relevant
4286 IF l_attributes_rec.detail_new = 'N' THEN
4287 Query_Timecard_Info( l_attributes_rec.detail_bb_id
4288 , l_attributes_rec.detail_bb_ovn
4289 , l_old_attributes_rec
4290 );
4291
4292 -- we only care about the most recent SUBMITTED block if it exists
4293 WHILE l_old_attributes_rec.detail_bb_ovn > 1 AND
4294 l_old_attributes_rec.timecard_approval_status <> 'SUBMITTED' LOOP
4295 Query_Timecard_Info( l_old_attributes_rec.detail_bb_id
4296 , l_old_attributes_rec.detail_bb_ovn - 1
4297 , l_old_attributes_rec
4298 );
4299 END LOOP;
4300
4301 -- mark this as an old block since Query_Timecard_Info does not know that
4302 l_old_attributes_rec.old_block := 'Y';
4303
4304 -- negate the amount to subtract from the po line amount later
4305 l_old_attributes_rec.po_billable_amount := 0 - l_old_attributes_rec.po_billable_amount;
4306
4307 l_old_attributes(l_bb_id) := l_old_attributes_rec;
4308 END IF;
4309 ELSIF l_blocks(blk_idx).scope = 'DAY' THEN
4310 l_attributes_rec.day_bb_id := l_bb_id;
4311 l_attributes_rec.day_start_time := l_blocks(blk_idx).start_time;
4312 END IF;
4313
4314 -- save the changes back in the main repository
4315 l_all_attributes(l_bb_id) := l_attributes_rec;
4316 END LOOP;
4317
4318 -- loop through the blocks again to perform validations
4319 /** Bug:5559915
4320 * Before looping into the each time card entry of a Time card, set the
4321 * g_error_raised_flag to 0 and after the loop reset it to 0.
4322 * When Validate_Attributes() is called inside the FOR loop, we have to log
4323 * PO and PO line related error message only once and not for each
4324 * Time card entry. Validate_Attributes() may set the g_error_raised_flag
4325 * to 1, so we have to reset after the FOR loop.
4326 */
4327 g_error_raised_flag := 0;--Bug:5559915
4328 FOR blk_idx IN 1..l_blocks.COUNT LOOP
4329 l_bb_id := l_blocks(blk_idx).time_building_block_id;
4330
4331 IF l_blocks(blk_idx).scope = 'DETAIL' THEN
4332 -- capture the parent information
4333 l_all_attributes(l_bb_id).day_start_time := l_all_attributes(l_blocks(blk_idx).parent_building_block_id).day_start_time;
4334 IF l_old_attributes.EXISTS(l_bb_id) THEN
4335 l_old_attributes(l_bb_id).day_start_time := l_all_attributes(l_bb_id).day_start_time;
4336 END IF;
4337
4338 -- validate the old attributes if relevant
4339 IF l_all_attributes(l_bb_id).detail_new = 'N' AND
4340 l_all_attributes(l_bb_id).detail_changed = 'Y' AND
4341 l_old_attributes(l_bb_id).timecard_approval_status = 'SUBMITTED' THEN
4342 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
4343 , module => G_LOG_MODULE
4344 , message => 'Validating old block'
4345 );
4346 Validate_Attributes(l_old_attributes(l_bb_id), l_messages);
4347 ELSE
4348 -- ignore blocks that have not been submitted
4349 -- and blocks that have not changed
4350 l_old_attributes(l_bb_id).validation_status := 'SKIPPED';
4351 END IF;
4352
4353 -- validate the new attributes if block is not deleted and old block is good/irrelevant
4354 IF l_all_attributes(l_bb_id).detail_new = 'Y' OR
4355 ( l_all_attributes(l_bb_id).detail_changed = 'Y' AND
4356 l_all_attributes(l_bb_id).detail_deleted = 'N' AND
4357 l_old_attributes(l_bb_id).validation_status IN ('SUCCESS','SKIPPED')
4358 )
4359 THEN
4360 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
4361 , module => l_log_head
4362 , message => 'Validating new block'
4363 );
4364 Validate_Attributes(l_all_attributes(l_bb_id), l_messages);
4365 END IF;
4366
4367 /* Bug 5394967.
4368 * When user deletes, the flags detail_changed will be 'N', detail_new will be 'Y'
4369 * detail_deleted will be 'Y'. We do not handle this case. We did not call the
4370 * validate_attributes and because of this, we were deleting the timecards even
4371 * if it is in a state where it should not be deleted. Added the following
4372 * code to call the procedure that will validate the timecard before deleting it.
4373 */
4374
4375 IF l_all_attributes(l_bb_id).detail_new = 'N' AND
4376 ( l_all_attributes(l_bb_id).detail_changed = 'N' AND
4377 l_all_attributes(l_bb_id).detail_deleted = 'Y'
4378 )
4379 THEN
4380 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
4381 , module => l_log_head
4382 , message => 'Validating block that is deleted '||l_all_attributes(l_bb_id).old_block
4383 );
4384 Validate_Attributes(l_all_attributes(l_bb_id), l_messages);
4385 END IF;
4386 END IF;
4387 END LOOP;
4388 g_error_raised_flag := 0;--Bug:5559915
4389
4390 -- validate the amount tolerances
4391 Validate_Amount_Tolerances(l_messages);
4392
4393 -- set time information
4394 HXC_INTEGRATION_LAYER_V1_GRP.set_app_hook_params(
4395 p_building_blocks => l_blocks,
4396 p_app_attributes => l_attributes,
4397 p_messages => l_messages);
4398
4399 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
4400 , module => l_log_head
4401 , message => 'End Validate_Timecard'
4402 );
4403
4404 g_validate_stop := SYSDATE;
4405
4406 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
4407 , module => l_log_head
4408 , message => 'Cache hit rates: ' || FND_GLOBAL.local_chr(10)
4409 || ' build_block: ' || (g_build_block_calls - g_build_block_misses) || '/' || g_build_block_calls || FND_GLOBAL.local_chr(10)
4410 || ' build_attribute: ' || (g_build_attribute_calls - g_build_attribute_misses) || '/' || g_build_attribute_calls || FND_GLOBAL.local_chr(10)
4411 || ' po_header: ' || (g_po_header_calls - g_po_header_misses || '/' || g_po_header_calls) || FND_GLOBAL.local_chr(10)
4412 || ' po_line: ' || (g_po_line_calls - g_po_line_misses || '/' || g_po_line_calls) || FND_GLOBAL.local_chr(10)
4413 || ' po_distribution: ' || (g_po_distribution_calls - g_po_distribution_misses || '/' || g_po_distribution_calls) || FND_GLOBAL.local_chr(10)
4414 || ' price_type_lookup: ' || (g_price_type_lookup_calls - g_price_type_lookup_misses) || '/' || g_price_type_lookup_calls || FND_GLOBAL.local_chr(10)
4415 || ' price_differentials: ' || (g_price_differentials_calls - g_price_differentials_misses) || '/' || g_price_differentials_calls || FND_GLOBAL.local_chr(10)
4416 || ' assignments: ' || (g_assignments_calls - g_assignments_misses) || '/' || g_assignments_calls || FND_GLOBAL.local_chr(10)
4417 || ' rcv_transactions: ' || (g_rcv_transactions_calls - g_rcv_transactions_misses) || '/' || g_rcv_transactions_calls || FND_GLOBAL.local_chr(10)
4418 || FND_GLOBAL.local_chr(10)
4419 || 'Running times: ' || FND_GLOBAL.local_chr(10)
4420 || ' Update: ' || TO_CHAR((g_update_stop - g_update_start) * 8640000, '99,999.90') || ' ms' || FND_GLOBAL.local_chr(10)
4421 || ' Validate: ' || TO_CHAR((g_validate_stop - g_validate_start) * 8640000, '99,999.90') || ' ms' || FND_GLOBAL.local_chr(10)
4422 );
4423 EXCEPTION
4424 WHEN OTHERS THEN
4425 HXC_INTEGRATION_LAYER_V1_GRP.add_error_to_table( p_message_table => l_messages
4426 , p_message_name => 'HXC_RET_UNEXPECTED_ERROR'
4427
4428
4429 , p_message_token => 'ERR&' || 'validating timecard: ' || SQLERRM
4430 , p_message_level => HXC_USER_TYPE_DEFINITION_GRP.c_error
4431 , p_message_field => NULL
4432 , p_application_short_name => 'HXC'
4433 , p_timecard_bb_id => NULL
4434 , p_time_attribute_id => NULL
4435 , p_message_extent => HXC_USER_TYPE_DEFINITION_GRP.c_blk_children_extent
4436 );
4437
4438 END Validate_Timecard;
4439
4440 PROCEDURE Validate_Block
4441 ( p_effective_date IN DATE
4442 , p_type IN VARCHAR2
4443 , p_measure IN NUMBER
4444 , p_unit_of_measure IN VARCHAR2
4445 , p_start_time IN DATE
4446 , p_stop_time IN DATE
4447 , p_parent_building_block_id IN NUMBER
4448 , p_parent_building_block_ovn IN NUMBER
4449 , p_scope IN VARCHAR2
4450 , p_approval_style_id IN NUMBER
4451 , p_approval_status IN VARCHAR2
4452 , p_resource_id IN NUMBER
4453 , p_resource_type IN VARCHAR2
4454 , p_comment_text IN VARCHAR2
4455 )
4456 IS
4457 BEGIN
4458 null;
4459 END Validate_Block;
4460
4461 --
4462 -- This is the wrapper around the actual retrieval.
4463 -- The retrieval needs to access global tables in HXC_GENERIC_RETRIEVAL
4464 -- and the code becomes unreadable with the long references to the tables.
4465 --
4466 -- However, assigning the tables to local tables could be a big performance
4467 -- impact for big retrievals, so we write a wrapper that passes the global
4468 -- tables as NOCOPY parameters.
4469 --
4470 -- This way, the code can reference the local parameters, yet the tables
4471 -- are not copied.
4472 --
4473 PROCEDURE Retrieve_Timecards
4474 ( errbuf OUT NOCOPY VARCHAR2
4475 , retcode OUT NOCOPY VARCHAR2
4476 , p_vendor_id IN NUMBER
4477 , p_start_date IN VARCHAR2
4478 , p_end_date IN VARCHAR2
4479 , p_receipt_date IN VARCHAR2
4480 ) IS
4481 l_start_date DATE;
4482 l_end_date DATE;
4483 l_receipt_date DATE;
4484 --#Bug 6798505/6631524
4485 l_where_clause VARCHAR2(1000);
4486 l_more_timecards BOOLEAN := TRUE;
4487
4488 GENERIC_RETRIEVAL_FAILED EXCEPTION;
4489 SUCCESS_SHORT_CIRCUIT EXCEPTION;
4490 l_api_name CONSTANT varchar2(30) := 'Retrieve_Timecards';
4491 l_log_head CONSTANT VARCHAR2(100) := G_LOG_MODULE || '.'||l_api_name;
4492 BEGIN
4493 -- initialize
4494 g_retrieval_start := SYSDATE;
4495 g_overall_status := 'SUCCESS';
4496 initialize_cache_statistics;
4497 initialize_timing_statistics;
4498
4499 G_CONC_LOG := '';
4500
4501 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
4502 , module => l_log_head
4503 , message => 'Begin Retrieve_Timecards'
4504 );
4505
4506 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
4507 , module => l_log_head
4508 , message => 'Parameters: p_vendor_id=' || p_vendor_id
4509 || ', p_start_date=' || p_start_date
4510 || ', p_end_date=' || p_end_date
4511 || ', p_receipt_date=' || p_receipt_date
4512 );
4513
4514 -- convert the date parameters
4515 l_start_date := FND_DATE.Canonical_To_Date(p_start_date);
4516 l_end_date := FND_DATE.Canonical_To_Date(p_end_date);
4517 l_receipt_date := FND_DATE.Canonical_To_Date(p_receipt_date);
4518
4519 /* Bug 5713531 .Change made in where clause as po_headers and po_lines */
4520 -- add supplier name condition
4521 -- bug 6031665 : corrected the syntax error. Previously the in condition was
4522 -- in ("RATE", "FIXED PRICE") instead of in (''RATE'', ''FIXED PRICE'')
4523 IF p_vendor_id IS NOT NULL THEN
4524 Add_Where_Clause( p_where_clause => l_where_clause
4525 , p_new_condition => '[PO Line Id]{ IN (SELECT TO_CHAR(pol.po_line_id)
4526 FROM po_headers poh, po_lines pol
4527 WHERE poh.po_header_id = pol.po_header_id
4528 AND pol.order_type_lookup_code in (''RATE'',''FIXED PRICE'') and poh.vendor_id = '
4529 || p_vendor_id || ')}');
4530 /* Else clause also added to impose OU specific behaviour */
4531 ELSE
4532 Add_Where_Clause( p_where_clause => l_where_clause
4533 , p_new_condition => '[PO Line Id]{ IN (SELECT TO_CHAR(pol.po_line_id)
4534 FROM po_lines pol
4535 WHERE pol.order_type_lookup_code in (''RATE'',''FIXED PRICE''))}');
4536 END IF;
4537
4538 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
4539 , module => l_log_head
4540 , message => 'Calling Generic Retrieval with p_where_clause: '
4541 || l_where_clause
4542 );
4543
4544 -- loop through the batches
4545 WHILE l_more_timecards LOOP
4546 -- call the generic retrieval package to populate global tables
4547 BEGIN
4548 g_generic_start := SYSDATE;
4549
4550 HXC_INTEGRATION_LAYER_V1_GRP.Execute_Retrieval_Process(
4551 P_Process => 'Purchasing Retrieval Process',
4552 P_Transaction_code => NULL,
4553 P_Start_Date => l_start_date,
4554 P_End_Date => l_end_date,
4555 P_Incremental => 'Y',
4556 P_Rerun_Flag => 'N',
4557 P_Where_Clause => l_where_clause,
4558 P_Scope => 'DAY',
4559 P_Clusive => 'EX');
4560
4561 g_generic_stop := SYSDATE;
4562 g_generic_time := g_generic_time + (g_generic_stop - g_generic_start);
4563 EXCEPTION
4564 WHEN OTHERS THEN
4565 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_EXCEPTION
4566 , module => l_log_head
4567 , message => 'Generic Retrieval failed: ' || SQLERRM
4568 );
4569 IF SQLERRM like 'ORA-20001: HXC_0013_GNRET_NO_BLD_BLKS%' OR
4570 SQLERRM like 'ORA-20001: HXC_0012_GNRET_NO_TIMECARDS%' THEN
4571 G_CONC_LOG := G_CONC_LOG || FND_MESSAGE.get_string('PO', 'RCV_OTL_GNRET_NO_TIMECARDS')
4572 || FND_GLOBAL.local_chr(10) || FND_GLOBAL.local_chr(10);
4573 RAISE SUCCESS_SHORT_CIRCUIT;
4574 ELSIF SQLERRM like 'ORA-20001: HXC_0017_GNRET_PROCESS_RUNNING%' THEN
4575 G_CONC_LOG := G_CONC_LOG || FND_MESSAGE.get_string('PO', 'RCV_OTL_GNRET_PROCESS_RUNNING')
4576 || FND_GLOBAL.local_chr(10) || FND_GLOBAL.local_chr(10);
4577 END IF;
4578
4579 RAISE GENERIC_RETRIEVAL_FAILED;
4580 END;
4581
4582 g_retrieved_details := g_retrieved_details + HXC_USER_TYPE_DEFINITION_GRP.t_detail_bld_blks.COUNT;
4583
4584 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
4585 , module => l_log_head
4586 , message => 'Returned from Generic Retrieval with '
4587 || HXC_USER_TYPE_DEFINITION_GRP.t_detail_bld_blks.COUNT
4588 || ' detail blocks'
4589 );
4590
4591 -- are there any more timecard blocks to process?
4592 IF HXC_USER_TYPE_DEFINITION_GRP.t_detail_bld_blks.COUNT > 0 THEN
4593 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
4594 , module => l_log_head
4595 , message => 'Calling Retrieval Body...'
4596 );
4597
4598 -- call the body of the retrieval
4599 -- Bug6357273
4600 -- We are calling Generic retrieval in the loop now, so there can be multiple batches in
4601 -- Retrieval program. SO now even if one batch get failed due to some error, other batches
4602 -- will executed and the summery report will be printed.
4603 BEGIN
4604 Retrieve_Timecards_Body( p_blocks => HXC_USER_TYPE_DEFINITION_GRP.t_detail_bld_blks
4605 , p_old_blocks => HXC_USER_TYPE_DEFINITION_GRP.t_old_detail_bld_blks
4606 , p_attributes => HXC_USER_TYPE_DEFINITION_GRP.t_detail_attributes
4607 , p_old_attributes => HXC_USER_TYPE_DEFINITION_GRP.t_old_detail_attributes
4608 , p_receipt_date => l_receipt_date
4609 );
4610 EXCEPTION
4611 WHEN OTHERS THEN
4612 RCV_HXT_GRP.string ( log_level => FND_LOG.LEVEL_UNEXPECTED , module => l_log_head , message => 'Retrieve_Timecards_Body failed. Error:'|| sqlerrm );
4613 END;
4614
4615 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
4616 , module => l_log_head
4617 , message => 'Returned from Retrieval Body'
4618 );
4619 ELSE
4620 l_more_timecards := FALSE;
4621
4622 HXC_INTEGRATION_LAYER_V1_GRP.update_transaction_status (
4623 p_process => 'Purchasing Retrieval Process'
4624 , p_status => 'SUCCESS'
4625 , p_exception_description => 'No more rows to process'
4626 );
4627 END IF;
4628 END LOOP;
4629
4630 g_retrieval_stop := SYSDATE;
4631 g_retrieval_time := g_retrieval_stop - g_retrieval_start;
4632
4633 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
4634 , module => l_log_head
4635 , message => 'End Retrieve_Timecards'
4636 );
4637
4638 IF g_failed_details > 0 THEN
4639 G_CONC_LOG := G_CONC_LOG || FND_MESSAGE.get_string('PO', 'RCV_OTL_RCVTP_ERROR')
4640 || FND_GLOBAL.local_chr(10) || FND_GLOBAL.local_chr(10);
4641 END IF;
4642
4643 -- generate summary report
4644 G_CONC_LOG := G_CONC_LOG || 'Summary information: ' || FND_GLOBAL.local_chr(10)
4645 || ' Detail blocks retrieved: ' || g_retrieved_details || FND_GLOBAL.local_chr(10)
4646 || ' Detail blocks successful: ' || g_successful_details || FND_GLOBAL.local_chr(10)
4647 || ' Detail blocks failed: ' || g_failed_details || FND_GLOBAL.local_chr(10)
4648 || ' Receiving Transaction Processor request id: ' || g_req_id || FND_GLOBAL.local_chr(10)
4649 || ' Receiving Transaction Processor group id: ' || g_group_id || FND_GLOBAL.local_chr(10)
4650 || ' Retrieval status: ' || g_overall_status || FND_GLOBAL.local_chr(10)
4651 || FND_GLOBAL.local_chr(10)
4652 || 'Cache hit rates: ' || FND_GLOBAL.local_chr(10)
4653 || ' build_block: ' || (g_build_block_calls - g_build_block_misses) || '/' || g_build_block_calls || FND_GLOBAL.local_chr(10)
4654 || ' build_attribute: ' || (g_build_attribute_calls - g_build_attribute_misses) || '/' || g_build_attribute_calls || FND_GLOBAL.local_chr(10)
4655 || ' po_header: ' || (g_po_header_calls - g_po_header_misses || '/' || g_po_header_calls) || FND_GLOBAL.local_chr(10)
4656 || ' po_line: ' || (g_po_line_calls - g_po_line_misses || '/' || g_po_line_calls) || FND_GLOBAL.local_chr(10)
4657 || ' po_distribution: ' || (g_po_distribution_calls - g_po_distribution_misses || '/' || g_po_distribution_calls) || FND_GLOBAL.local_chr(10)
4658 || ' price_type_lookup: ' || (g_price_type_lookup_calls - g_price_type_lookup_misses) || '/' || g_price_type_lookup_calls || FND_GLOBAL.local_chr(10)
4659 || ' price_differentials: ' || (g_price_differentials_calls - g_price_differentials_misses) || '/' || g_price_differentials_calls || FND_GLOBAL.local_chr(10)
4660 || ' assignments: ' || (g_assignments_calls - g_assignments_misses) || '/' || g_assignments_calls || FND_GLOBAL.local_chr(10)
4661 || ' rcv_transactions: ' || (g_rcv_transactions_calls - g_rcv_transactions_misses) || '/' || g_rcv_transactions_calls || FND_GLOBAL.local_chr(10)
4662 || FND_GLOBAL.local_chr(10)
4663 || 'Running times: ' || FND_GLOBAL.local_chr(10)
4664 || ' Generic Retrieval: ' || TO_CHAR((g_generic_time) * 8640000, '99,999.90') || ' ms' || FND_GLOBAL.local_chr(10)
4665 || ' Receiving Transaction Processor: ' || TO_CHAR((g_receiving_time) * 8640000, '99,999.90') || ' ms' || FND_GLOBAL.local_chr(10)
4666 || ' Retrieval: '
4667 || TO_CHAR(((g_retrieval_time) - (g_generic_time) - (g_receiving_time)) * 8640000, '99,999.90')
4668 || ' ms' || FND_GLOBAL.local_chr(10);
4669
4670 -- send output to concurrent log
4671 errbuf := G_CONC_LOG;
4672 IF g_failed_details > 0 THEN
4673 retcode := 1;
4674 ELSE
4675 retcode := 0;
4676 END IF;
4677
4678 EXCEPTION
4679 WHEN SUCCESS_SHORT_CIRCUIT THEN
4680 errbuf := G_CONC_LOG;
4681 retcode := 0;
4682 WHEN GENERIC_RETRIEVAL_FAILED THEN
4683 errbuf := G_CONC_LOG;
4684 retcode := 2;
4685 WHEN OTHERS THEN
4686 RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_UNEXPECTED
4687 , module => l_log_head
4688 , message => 'Unexpected exception in RCV_HXT_GRP.Retrieve_Timecards: ' || SQLERRM
4689 );
4690 errbuf := G_CONC_LOG;
4691 retcode := 2;
4692 END;
4693
4694 END RCV_HXT_GRP;
4695