1 PACKAGE BODY rcv_normalize_data_pkg AS
2 /* $Header: RCVNRMDB.pls 120.15.12010000.2 2008/10/14 20:06:26 vthevark ship $*/
3
4 g_fail_if_one_line_fails VARCHAR2(1) := nvl(fnd_profile.VALUE('RCV_FAIL_IF_LINE_FAILS'),'N'); /* lcm changes */
5
6 PROCEDURE handle_error(
7 p_rti_row IN OUT NOCOPY rcv_transactions_interface%ROWTYPE
8 ) IS
9 BEGIN
10 p_rti_row.processing_status_code := 'ERROR';
11 p_rti_row.processing_request_id := g_request_id;
12
13 IF (g_fail_all = 'Y') THEN
14 /* Update statement called in update_rti_error
15 UPDATE rcv_transactions_interface
16 SET processing_status_code = 'ERROR',
17 processing_request_id = g_request_id
18 WHERE header_interface_id = p_rti_row.header_interface_id
19 AND processing_status_code IN('PENDING', 'RUNNING')
20 AND transaction_status_code = 'PENDING';
21 */
22 /*BEGIN BUG: 5598140*/
23 RCV_ROI_PREPROCESSOR.update_rti_error(p_group_id => p_rti_row.group_id,
24 p_interface_id => NULL,
25 p_header_interface_id => p_rti_row.header_interface_id,
26 p_lpn_group_id => NULL
27 );
28 ELSIF (p_rti_row.lpn_group_id is not null) THEN
29 RCV_ROI_PREPROCESSOR.update_rti_error(p_group_id => p_rti_row.group_id,
30 p_interface_id => NULL,
31 p_header_interface_id => NULL,
32 p_lpn_group_id => p_rti_row.lpn_group_id
33 );
34
35 ELSE
36 RCV_ROI_PREPROCESSOR.update_rti_error(p_group_id => p_rti_row.group_id,
37 p_interface_id => p_rti_row.interface_transaction_id,
38 p_header_interface_id => NULL,
39 p_lpn_group_id => NULL
40 );
41 /*END BUG: 5598140*/
42 END IF;
43
44 rcv_table_functions.update_rti_row(p_rti_row);
45
46 IF (g_multiple_groups = FALSE) THEN
47 COMMIT; --We don't want to erase what we've done so far
48 --RAISE rcv_error_pkg.e_fatal_error;
49 END IF;
50 END handle_error;
51
52 PROCEDURE check_orphan_rhi(
53 p_rhi_row IN OUT NOCOPY rcv_headers_interface%ROWTYPE
54 ) IS
55 x_rti_count NUMBER;
56 proc_code rcv_headers_interface.processing_status_code%TYPE;
57 BEGIN
58 SELECT count(*)
59 INTO x_rti_count
60 FROM rcv_transactions_interface
61 WHERE header_interface_id = p_rhi_row.header_interface_id
62 AND processing_status_code in ('PENDING','RUNNING')
63 AND transaction_status_code = 'PENDING';
64
65 asn_debug.put_line('count of running rtis'|| x_rti_count);
66
67 IF x_rti_count = 0 THEN -- error out orphan rhi
68 asn_debug.put_line('Erroring out orphan RHI: ' ||
69 p_rhi_row.header_interface_id);
70
71 p_rhi_row.processing_status_code := 'ERROR';
72 p_rhi_row.processing_request_id := g_request_id;
73
74 rcv_table_functions.update_rhi_row(p_rhi_row);
75 COMMIT;
76 END IF;
77 EXCEPTION
78 WHEN OTHERS THEN
79 NULL;
80 END check_orphan_rhi;
81
82 PROCEDURE prepare_pending_rhi(
83 p_rhi_row IN OUT NOCOPY rcv_headers_interface%ROWTYPE
84 ) IS
85 x_org_id NUMBER;
86 BEGIN --update block
87 BEGIN --exception handling block
88 rcv_default_pkg.default_header(p_rhi_row);
89 EXCEPTION
90 WHEN OTHERS THEN
91 p_rhi_row.processing_status_code := 'ERROR';
92 p_rhi_row.processing_request_id := g_request_id;
93
94 UPDATE rcv_transactions_interface
95 SET processing_status_code = 'ERROR',
96 processing_request_id = g_request_id
97 WHERE header_interface_id = p_rhi_row.header_interface_id
98 AND processing_status_code IN('PENDING', 'RUNNING')
99 AND transaction_status_code = 'PENDING';
100 END; --exception handling block
101
102 rcv_table_functions.update_rhi_row(p_rhi_row);
103 END prepare_pending_rhi;
104
105 PROCEDURE prepare_pending_rti(
106 p_rti_row IN OUT NOCOPY rcv_transactions_interface%ROWTYPE
107 ) IS
108 BEGIN --this is the update block
109 BEGIN --this is the exception catching block
110 IF (p_rti_row.GROUP_ID IS NULL) THEN
111 p_rti_row.GROUP_ID := g_group_id;
112 END IF;
113
114 IF (p_rti_row.validation_flag = 'Y') THEN
115 rcv_default_pkg.default_transaction(p_rti_row);
116 END IF;
117 EXCEPTION
118 WHEN OTHERS THEN
119 handle_error(p_rti_row);
120 END; --exception handling block
121
122 rcv_table_functions.update_rti_row(p_rti_row);
123 END prepare_pending_rti;
124
125 PROCEDURE process_orphan_rti(
126 p_rti_row IN OUT NOCOPY rcv_transactions_interface%ROWTYPE
127 ) IS
128 BEGIN
129 rcv_error_pkg.set_error_message('RCV_IS_ORPHAN');
130 rcv_error_pkg.log_interface_error('PARENT_INTERFACE_TXN_ID');
131 EXCEPTION
132 WHEN OTHERS THEN
133 p_rti_row.processing_status_code := 'ERROR';
134 p_rti_row.processing_request_id := g_request_id;
135 rcv_table_functions.update_rti_row(p_rti_row);
136 END;
137
138 PROCEDURE process_row(
139 p_rti_row IN OUT NOCOPY rcv_transactions_interface%ROWTYPE
140 ) IS
141 x_rhi_row rcv_headers_interface%ROWTYPE;
142
143 -- Following 4 variables are added for BugFix 5078706
144 l_parent_transaction_type rcv_transactions.transaction_type%type;
145 l_parent_transaction_id rcv_transactions.transaction_id%type;
146 l_true boolean;
147 l_match_count number;
148
149 /* lcm changes */
150 l_lpn_group_rti_count number := 0;
151
152 BEGIN
153 l_true := TRUE; -- BugFix 5078706
154
155 BEGIN
156 asn_debug.put_line('Processing row INTERFACE_TRANSACTION_ID = ' || p_rti_row.interface_transaction_id);
157
158 -- Following code is added for BugFix 5078706
159 BEGIN
160 select transaction_id,
161 transaction_type
162 into l_parent_transaction_id,
163 l_parent_transaction_type
164 from rcv_transactions
165 where transaction_type = 'UNORDERED'
166 connect by prior
167 parent_transaction_id = transaction_id
168 start with transaction_id = p_rti_row.parent_transaction_id;
169
170 begin
171 select count(1)
172 into l_match_count
173 from rcv_transactions
174 where transaction_type = 'MATCH'
175 and parent_transaction_id = l_parent_transaction_id;
176 exception
177 when others
178 then
179 l_match_count := 0;
180 end;
181
182 IF (l_parent_transaction_type = 'UNORDERED' and l_match_count = 0)
183 THEN
184 l_true := FALSE;
185 ELSE
186 l_true := TRUE;
187 END IF;
188
189 EXCEPTION
190 WHEN OTHERS
191 THEN
192 l_true := TRUE;
193 END;
194 -- End of code for BugFix 5078706
195
196 /*
197 ** Bug#4641243 - Operating Unit information will not be available for
198 ** Unordered Receipts
199 */
200
201 IF (p_rti_row.transaction_type <> 'UNORDERED' AND p_rti_row.source_document_code <> 'INVENTORY') THEN -- bug 5147243
202 IF (l_true = TRUE) -- BugFix 5078706
203 THEN
204 IF (NVL(mo_global.check_access(p_rti_row.org_id),'N') = 'N') THEN
205 rcv_error_pkg.set_error_message('RCV_NOT_IN_ORG');
206 rcv_error_pkg.set_token('ORG_ID', p_rti_row.org_id);
207 rcv_error_pkg.set_token('OU', mo_global.get_current_org_id);
208 rcv_error_pkg.log_interface_error('ORG_ID');
209 END IF;
210 END IF; -- BugFix 5078706
211 END IF;
212
213 /* Bugfix : 5354379 : The defaulting should not be called from here since the parent row will be in
214 status "RUNNING" at this instance and which will therefore cause the rcv_default_pkg.default_from_parent
215 to raise an error thru rcv_default_pkg.default_rti_from_rti.
216 IF (p_rti_row.parent_interface_txn_id IS NOT NULL) THEN
217 rcv_default_pkg.default_from_parent(p_rti_row); -- we want to pull in any new information about the row
218 END IF;
219 */
220 EXCEPTION
221 WHEN OTHERS THEN
222 handle_error(p_rti_row);
223 END;
224
225 /* EXECUTE PROCESSOR on row */
226 IF (p_rti_row.processing_status_code <> 'ERROR') THEN
227
228 /* lcm chages : Start */
229 x_rhi_row := rcv_table_functions.get_rhi_row_from_id(p_rti_row.header_interface_id);
230
231 IF ( rcv_table_functions.is_lcm_org (p_rti_row.to_organization_id) = 'Y'
232 AND rcv_table_functions.is_pre_rcv_org (p_rti_row.to_organization_id) = 'N') Then
233 --
234 IF (p_rti_row.source_document_code = 'PO'
235 AND (p_rti_row.transaction_type = 'RECEIVE'
236 OR (p_rti_row.transaction_type = 'SHIP'
237 AND p_rti_row.auto_transact_code in ('RECEIVE','DELIVER')))
238 AND rcv_table_functions.is_lcm_shipment (p_rti_row.po_line_location_id) = 'Y'
239 AND p_rti_row.lcm_shipment_line_id is null) THEN
240 --
241 asn_debug.put_line('Setting current row to status LC_PENDING');
242 p_rti_row.processing_status_code := 'LC_PENDING';
243 --
244 ELSE
245 /* If a non-lcm line and lcm line are tied to the same lpn_group_id, we
246 need to set the non-lcm line to 'WLC_PENDING' as these should be processed together. */
247 IF (p_rti_row.lpn_group_id IS NOT NULL) THEN
248 select count(1)
249 into l_lpn_group_rti_count
250 from rcv_transactions_interface rti
251 where rti.group_id = p_rti_row.group_id
252 and rti.lpn_group_id is not null
253 and rti.lpn_group_id = p_rti_row.lpn_group_id
254 and rti.source_document_code = 'PO'
255 and exists (select 'LCM Shipment' from po_line_locations_all pll
256 where pll.line_location_id = rti.po_line_location_id
257 and lcm_flag = 'Y')
258 and (rti.lcm_shipment_line_id is null or rti.unit_landed_cost is null);
259 --
260 asn_debug.put_line('LPN Group check : l_lpn_group_rti_count = ' ||l_lpn_group_rti_count, null,14);
261 --
262 END IF;
263 --
264 IF (x_rhi_row.asn_type = 'ASN'
265 AND g_fail_if_one_line_fails = 'Y'
266 AND l_lpn_group_rti_count = 0) THEN
267 select count(1)
268 into l_lpn_group_rti_count
269 from rcv_transactions_interface rti
270 where rti.group_id = x_rhi_row.group_id
271 and rti.header_interface_id = x_rhi_row.header_interface_id
272 and rti.source_document_code = 'PO'
273 and exists (select 'LCM Shipment' from po_line_locations_all pll
274 where pll.line_location_id = rti.po_line_location_id
275 and lcm_flag = 'Y')
276 and (rti.lcm_shipment_line_id is null or rti.unit_landed_cost is null);
277 --
278 asn_debug.put_line('ASN check : l_lpn_group_rti_count = ' ||l_lpn_group_rti_count, null,14);
279 --
280 END IF;
281 IF (l_lpn_group_rti_count > 0) THEN
282 asn_debug.put_line('Setting current row to status WLC_PENDING');
283 p_rti_row.processing_status_code := 'WLC_PENDING';
284 p_rti_row.processing_request_id := g_request_id;
285 IF (x_rhi_row.processing_status_code = 'ERROR') THEN
286 asn_debug.put_line('Setting header row status HEADER_INTERFACE_ID=' || x_rhi_row.header_interface_id);
287 x_rhi_row.processing_status_code := 'PENDING';
288 x_rhi_row.processing_request_id := g_request_id;
289 rcv_table_functions.update_rhi_row(x_rhi_row);
290 END IF;
291 ELSE
292 asn_debug.put_line('Setting current row to status RUNNING');
293 p_rti_row.processing_status_code := 'RUNNING';
294 p_rti_row.processing_request_id := g_request_id;
295 IF (x_rhi_row.processing_status_code IN ('PENDING', 'ERROR')) THEN
296 asn_debug.put_line('Setting header row status HEADER_INTERFACE_ID=' || x_rhi_row.header_interface_id);
297 x_rhi_row.processing_status_code := 'RUNNING';
298 x_rhi_row.processing_request_id := g_request_id;
299 rcv_table_functions.update_rhi_row(x_rhi_row);
300 END IF;
301 END IF;
302 --
303 END IF;
304 --
305 ELSE
306 asn_debug.put_line('Setting current row to status RUNNING');
307 p_rti_row.processing_status_code := 'RUNNING';
308 p_rti_row.processing_request_id := g_request_id;
309
310 IF (x_rhi_row.processing_status_code IN('PENDING', 'ERROR')) THEN
311 asn_debug.put_line('Setting header row status HEADER_INTERFACE_ID=' || x_rhi_row.header_interface_id);
312 x_rhi_row.processing_status_code := 'RUNNING';
313 x_rhi_row.processing_request_id := g_request_id;
314 rcv_table_functions.update_rhi_row(x_rhi_row);
315 END IF;
316 END IF;
317 /* lcm chages : End */
318
319 END IF;
320
321 rcv_table_functions.update_rti_row(p_rti_row);
322 END;
323
324 PROCEDURE explode_all_lpn IS
325 l_ret_status VARCHAR2(20);
326 l_msg_count NUMBER;
327 l_msg_data VARCHAR2(100);
328 BEGIN
329 asn_debug.put_line('in explode_all_lpn');
330
331 UPDATE rcv_transactions_interface
332 SET processing_status_code = 'RUNNING',
333 processing_request_id = g_request_id
334 WHERE processing_status_code = 'PENDING'
335 AND ( processing_request_id = g_request_id
336 OR processing_request_id IS NULL)
337 AND ( mo_global.check_access(org_id) = 'Y'
338 OR org_id IS NULL)
339 AND ( lpn_group_id IS NOT NULL
340 OR lpn_id IS NOT NULL
341 OR license_plate_number IS NOT NULL
342 OR interface_transaction_id IN(SELECT interface_transaction_id
343 FROM wms_lpn_contents_interface));
344
345 inv_rcv_integration_apis.explode_lpn(1.0,
346 fnd_api.g_true,
347 l_ret_status,
348 l_msg_count,
349 l_msg_data,
350 NULL,
351 g_request_id
352 );
353
354 UPDATE rcv_transactions_interface
355 SET processing_status_code = 'PENDING'
356 WHERE processing_status_code = 'RUNNING'
357 AND processing_request_id = g_request_id;
358
359 asn_debug.put_line('finished explode_all_lpn');
360 EXCEPTION
361 WHEN OTHERS THEN
362 asn_debug.put_line('encountered an error in explode_all_lpn');
363 END explode_all_lpn;
364
365 PROCEDURE explode_lpn IS
366 l_ret_status VARCHAR2(20);
367 l_msg_count NUMBER;
368 l_msg_data VARCHAR2(100);
369 BEGIN
370 asn_debug.put_line('in explode_lpn');
371
372 UPDATE rcv_transactions_interface
373 SET processing_status_code = 'RUNNING',
374 processing_request_id = g_request_id,
375 GROUP_ID = g_group_id
376 WHERE processing_status_code = 'PENDING'
377 AND ( processing_request_id = g_request_id
378 OR processing_request_id IS NULL)
379 AND GROUP_ID = g_group_id
380 AND ( lpn_group_id IS NOT NULL
381 OR lpn_id IS NOT NULL
382 OR license_plate_number IS NOT NULL
383 OR interface_transaction_id IN(SELECT interface_transaction_id
384 FROM wms_lpn_contents_interface));
385
386 inv_rcv_integration_apis.explode_lpn(1.0,
387 fnd_api.g_true,
388 l_ret_status,
389 l_msg_count,
390 l_msg_data,
391 g_group_id,
392 g_request_id
393 );
394
395 UPDATE rcv_transactions_interface
396 SET processing_status_code = 'PENDING'
397 WHERE processing_status_code = 'RUNNING'
398 AND processing_request_id = g_request_id
399 AND GROUP_ID = g_group_id;
400
401 asn_debug.put_line('finished explode_lpn');
402 EXCEPTION
403 WHEN OTHERS THEN
404 asn_debug.put_line('encountered an error in explode_lpn');
405 END explode_lpn;
406
407 PROCEDURE process_pending_rows(
408 p_processing_mode IN VARCHAR2,
409 p_group_id IN NUMBER,
410 p_request_id IN NUMBER,
411 p_org_id IN NUMBER
412 ) IS
413 CURSOR c_get_group_id IS
414 SELECT rcv_interface_groups_s.NEXTVAL
415 FROM DUAL;
416
417 CURSOR c_get_all_pending_rhi_row IS
418 SELECT *
419 FROM rcv_headers_interface
420 WHERE processing_status_code = 'PENDING'
421 AND ( mo_global.check_access(org_id) = 'Y'
422 OR org_id IS NULL);
423
424 CURSOR c_get_pending_rhi_row IS
425 SELECT *
426 FROM rcv_headers_interface
427 WHERE processing_status_code = 'PENDING'
428 AND GROUP_ID = g_group_id;
429
430 /* the order is important, which is the reason for the connect by string */
431 /* this ensures defaulting from a parent row that has already been populated */
432 /* however, because of this we will not collect 'orphan' rows - we need to
433 /* error out the orphans */
434 /* WDK - ADD INDEX ON PARENT_INTERFACE_TXN_ID */
435 CURSOR c_get_all_pending_rti_row IS
436 SELECT *
437 FROM rcv_transactions_interface
438 WHERE processing_status_code = 'PENDING'
439 AND processing_mode_code = g_processing_mode -- Bug 6311798
440 AND ( mo_global.check_access(org_id) = 'Y'
441 OR org_id IS NULL)
442 CONNECT BY PRIOR interface_transaction_id = parent_interface_txn_id
443 START WITH parent_interface_txn_id IS NULL
444 ORDER BY GROUP_ID;
445
446 CURSOR c_get_pending_rti_row IS
447 SELECT *
448 FROM rcv_transactions_interface
449 WHERE processing_status_code = 'PENDING'
450 AND processing_mode_code = g_processing_mode -- Bug 6311798
451 AND GROUP_ID = g_group_id
452 CONNECT BY PRIOR interface_transaction_id = parent_interface_txn_id
453 START WITH parent_interface_txn_id IS NULL;
454
455 /* because we needed to include parent/child order, we introduce the possibility */
456 /* of not processing 'orphan' children. these children need to be errored out */
457
458 /** Bug: 5473673
459 * There is possibility to have more than 1 orphan record exist in
460 * rcv_transactions_interface table. In that case inner query will
461 * multiple rows, but we are having the condition "=" in
462 * the clause "START WITH". So, we have to replace the contition
463 * "=" with "IN"
464 */
465
466 CURSOR c_get_all_orphan_rti_row IS
467 SELECT *
468 FROM rcv_transactions_interface
469 WHERE processing_status_code = 'PENDING'
470 AND processing_mode_code = g_processing_mode -- Bug 6311798
471 AND ( mo_global.check_access(org_id) = 'Y'
472 OR org_id IS NULL)
473 CONNECT BY PRIOR interface_transaction_id = parent_interface_txn_id
474 START WITH interface_transaction_id IN (SELECT interface_transaction_id --Bug:5473673
475 FROM rcv_transactions_interface
476 WHERE parent_interface_txn_id NOT IN(SELECT interface_transaction_id
477 FROM rcv_transactions_interface));
478
479 CURSOR c_get_orphan_rti_row IS
480 SELECT *
481 FROM rcv_transactions_interface
482 WHERE processing_status_code = 'PENDING'
483 AND processing_mode_code = g_processing_mode -- Bug 6311798
484 AND GROUP_ID = g_group_id
485 CONNECT BY PRIOR interface_transaction_id = parent_interface_txn_id
486 START WITH interface_transaction_id IN (SELECT interface_transaction_id --Bug: 5473673
487 FROM rcv_transactions_interface
488 WHERE parent_interface_txn_id NOT IN(SELECT interface_transaction_id
489 FROM rcv_transactions_interface));
490
491 BEGIN
492 IF p_processing_mode = 'BATCH' THEN
493 IF p_group_id IS NULL
494 OR p_group_id = 0 THEN
495 g_multiple_groups := TRUE;
496 ELSE
497 g_group_id := p_group_id;
498 g_multiple_groups := FALSE;
499 END IF;
500 ELSE --p_processing = 'ONLINE' or 'IMMEDIATE'
501 g_multiple_groups := FALSE;
502 END IF;
503
504 g_group_id := NVL(p_group_id, 0);
505 g_request_id := NVL(p_request_id, 0);
506 g_processing_mode := p_processing_mode; -- Bug 6311798
507 rcv_table_functions.g_default_org_id := p_org_id;
508
509 IF g_group_id = 0 THEN
510 OPEN c_get_group_id;
511 FETCH c_get_group_id INTO g_group_id;
512 CLOSE c_get_group_id;
513 END IF;
514
515 asn_debug.put_line('Process Pending Rows: p_processing_mode=' || p_processing_mode || ' g_group_id=' || g_group_id || ' g_request_id=' || g_request_id || ' p_org_id=' || p_org_id);
516
517 /* we cannot use bulk collects because we are using the tablespace as working memory */
518 IF (g_multiple_groups = TRUE) THEN
519 asn_debug.put_line('Processing multiple groups...');
520 explode_all_lpn;
521
522 FOR x_rhi_row IN c_get_all_pending_rhi_row LOOP
523 prepare_pending_rhi(x_rhi_row);
524 END LOOP;
525
526 FOR x_rti_row IN c_get_all_pending_rti_row LOOP
527 prepare_pending_rti(x_rti_row);
528 END LOOP;
529
530 FOR x_rti_row IN c_get_all_orphan_rti_row LOOP
531 process_orphan_rti(x_rti_row);
532 END LOOP;
533 ELSE
534 asn_debug.put_line('Processing single group...');
535 explode_lpn;
536
537 FOR x_rhi_row IN c_get_pending_rhi_row LOOP
538 prepare_pending_rhi(x_rhi_row);
539 END LOOP;
540
541 FOR x_rti_row IN c_get_pending_rti_row LOOP
542 prepare_pending_rti(x_rti_row);
543 END LOOP;
544
545 FOR x_rti_row IN c_get_orphan_rti_row LOOP
546 process_orphan_rti(x_rti_row);
547 END LOOP;
548 END IF;
549
550 /* we now rerun the pending filter which will work this time because org_id has been populated */
551 IF (g_multiple_groups = TRUE) THEN
552 FOR x_rti_row IN c_get_all_pending_rti_row LOOP
553 process_row(x_rti_row);
554 END LOOP;
555 ELSE
556 FOR x_rti_row IN c_get_pending_rti_row LOOP
557 process_row(x_rti_row);
558 END LOOP;
559 END IF;
560
561 /* rhi could be without any pending rti at this point. */
562 asn_debug.put_line('Check orphan RHI');
563 IF (g_multiple_groups = TRUE) THEN
564 FOR x_rhi_row IN c_get_all_pending_rhi_row LOOP
565 check_orphan_rhi(x_rhi_row);
566 END LOOP;
567 ELSE
568 FOR x_rhi_row IN c_get_pending_rhi_row LOOP
569 check_orphan_rhi(x_rhi_row);
570 END LOOP;
571 END IF;
572
573 /* lcm changes */
574 asn_debug.put_line('Calling RCV_LCM_WEB_SERVICE.Get_Landed_Cost');
575 RCV_LCM_WEB_SERVICE.Get_Landed_Cost (p_group_id, p_processing_mode);
576
577 END process_pending_rows;
578 END rcv_normalize_data_pkg;
[Home] [Help]