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