[Home] [Help]
PACKAGE BODY: APPS.POR_RCV_TRANSACTION_SV
Source
1 PACKAGE BODY POR_RCV_TRANSACTION_SV AS
2 /* $Header: PORRCVTB.pls 120.7.12020000.4 2013/03/01 03:17:35 shindeng ship $*/
3
4 /******************************************************************
5 ** Function : insert_transaction_interface
6 ** Description : This is a function called from Java layer
7 ** currently used by return items and correction on the web.
8 ******************************************************************/
9 procedure insert_row(p_transaction_date in date,
10 p_parent_transaction_id in number,
11 p_group_id in number,
12 p_txn_qty in number,
13 p_txn_uom in varchar2,
14 p_primary_qty in number,
15 p_primary_uom in varchar2,
16 p_transaction_type in varchar2,
17 p_Receiving_Location_Id in number,
18 p_Return_Reason_Id in number,
19 p_subinventory in varchar2,
20 p_RMA_Reference in varchar2,
21 p_employee_id in number,
22 p_Comments in varchar2,
23 x_parent_interface_txn_id IN OUT NOCOPY number);
24
25 PROCEDURE insert_interface_errors ( rcv_trx IN OUT NOCOPY rcv_transactions_interface%ROWTYPE,
26 X_column_name IN VARCHAR2,
27 X_err_message IN VARCHAR2);
28
29 function get_rtv_id(p_transaction_id in number) return number;
30
31 function insert_transaction_interface(
32 p_Transaction_Type in varchar2,
33 p_caller in varchar2,
34 p_Parent_Transaction_Id in number,
35 p_Quantity in number, -- if correction, pass +/- qty.
36 p_Group_Id in number,
37 p_Group_Id2 in number,
38 p_Transaction_Date in date default sysdate,
39 p_Unit_Of_Measure in varchar2 default null,
40 p_Return_Reason_Id in number default null,
41 p_RMA_reference in varchar2 default null,
42 p_Subinventory in varchar2 default null,
43 p_Receiving_Location_Id in number default null,
44 p_Comments in varchar2 default null) return number is
45
46 x_user_id number;
47 x_employee_id number;
48
49 x_item_id number;
50 x_txn_uom varchar2(25) := p_Unit_Of_Measure;
51 x_primary_uom varchar2(25);
52 x_txn_qty number := p_Quantity;
53 x_primary_qty number;
54
55 x_parent_type varchar2(25);
56 x_grandparent_type varchar2(25);
57 x_grandparent_id number;
58 x_txn_org_id number;
59 x_user_org_id number;
60 x_parent_interface_txn_id number := NULL;
61
62 begin
63
64 /* Get info from parent transaction and Org ID*/
65
66 SELECT RL.PRIMARY_UNIT_OF_MEASURE,
67 RL.ITEM_ID,
68 RT.TRANSACTION_TYPE,
69 NVL(OH.ORG_ID, PH.ORG_ID)
70 INTO X_PRIMARY_UOM,
71 X_ITEM_ID,
72 X_PARENT_TYPE,
73 X_TXN_ORG_ID
74 FROM RCV_TRANSACTIONS RT,
75 RCV_SHIPMENT_LINES RL,
76 PO_HEADERS_ALL PH,
77 OE_ORDER_HEADERS_ALL OH
78 WHERE RT.TRANSACTION_ID = P_PARENT_TRANSACTION_ID
79 AND RT.SHIPMENT_LINE_ID = RL.SHIPMENT_LINE_ID
80 AND RT.PO_HEADER_ID = PH.PO_HEADER_ID(+)
81 AND RT.OE_ORDER_HEADER_ID = OH.HEADER_ID(+);
82
83 x_user_org_id := MO_GLOBAL.get_current_org_id;
84
85 if (x_txn_org_id <> MO_GLOBAL.get_current_org_id) then
86 mo_global.set_policy_context(p_access_mode => 'S',
87 p_org_id => x_txn_org_id);
88 end if;
89
90 x_user_id := fnd_global.user_id;
91
92 BEGIN
93 SELECT HR.EMPLOYEE_ID
94 INTO x_employee_id
95 FROM FND_USER FND, HR_EMPLOYEES_CURRENT_V HR
96 WHERE FND.USER_ID = x_user_id
97 AND FND.EMPLOYEE_ID = HR.EMPLOYEE_ID
98 AND ROWNUM = 1;
99 EXCEPTION
100 WHEN others THEN
101 x_employee_id := 0;
102 END;
103
104 /* DEBUG Need to convert received qty and uom into ordered qty and uom
105 Find out how to get the uom_class... see if it is */
106 /*
107 ** If you're receiving a one-time item then go get the primary
108 ** unit of measure based on the unit of measure class that is
109 ** assigned to the base transaction unit of measure.
110 */
111
112
113 /** DEBUG : Can we just call this routine if the receipt uom
114 ** is different from primary_uom? **/
115
116 if (X_txn_uom <> X_primary_uom) then
117 PO_UOM_S.UOM_CONVERT (x_txn_qty,
118 x_txn_uom,
119 x_item_id,
120 x_primary_uom,
121 x_primary_qty);
122 else
123 X_primary_qty := X_txn_qty;
124 end if;
125
126 if p_transaction_type = 'RETURN TO VENDOR' then
127
128 insert_row(p_transaction_date ,
129 p_parent_transaction_id,
130 p_group_id ,
131 x_txn_qty ,
132 x_txn_uom ,
133 x_primary_qty ,
134 x_primary_uom ,
135 p_transaction_type ,
136 p_Receiving_Location_Id,
137 p_Return_Reason_Id ,
138 p_subinventory ,
139 p_RMA_Reference ,
140 x_employee_id ,
141 p_Comments ,
142 x_parent_interface_txn_id);
143
144 elsif p_transaction_type = 'CORRECT' and
145 x_parent_type = 'DELIVER' and
146 x_txn_qty > 0 then
147
148 -- grand parent is the receive transaction
149 select rt2.transaction_type,
150 rt2.transaction_id
151 into x_grandparent_type,
152 x_grandparent_id
153 from rcv_transactions rt1,
154 rcv_transactions rt2
155 where rt1.transaction_id = p_parent_transaction_id
156 and rt2.transaction_id = rt1.parent_transaction_id;
157
158 -- correct receive first if qty is +'ve
159 insert_row(p_transaction_date ,
160 x_grandparent_id ,
161 p_group_id ,-- Bug12529647, use the same group_id rather than 2 different ids.
162 x_txn_qty ,
163 x_txn_uom ,
164 x_primary_qty ,
165 x_primary_uom ,
166 p_transaction_type ,
167 p_Receiving_Location_Id,
168 p_Return_Reason_Id ,
169 p_subinventory ,
170 p_RMA_Reference ,
171 x_employee_id ,
172 p_Comments ,
173 x_parent_interface_txn_id);
174
175 insert_row(p_transaction_date ,
176 p_parent_transaction_id,
177 p_group_id ,-- Bug12529647, use the same group_id rather than 2 different ids.
178 x_txn_qty ,
179 x_txn_uom ,
180 x_primary_qty ,
181 x_primary_uom ,
182 p_transaction_type ,
183 p_Receiving_Location_Id,
184 p_Return_Reason_Id ,
185 p_subinventory ,
186 p_RMA_Reference ,
187 x_employee_id ,
188 p_Comments ,
189 x_parent_interface_txn_id);
190
191 elsif p_transaction_type = 'CORRECT' and
192 x_parent_type = 'DELIVER' and
193 x_txn_qty < 0 then
194
195 -- grand parent is the receive transaction
196 select rt2.transaction_type,
197 rt2.transaction_id
198 into x_grandparent_type,
199 x_grandparent_id
200 from rcv_transactions rt1,
201 rcv_transactions rt2
202 where rt1.transaction_id = p_parent_transaction_id
203 and rt2.transaction_id = rt1.parent_transaction_id;
204
205 -- correct deliver first if qty is -'ve
206 insert_row(p_transaction_date ,
207 p_parent_transaction_id,
208 p_group_id ,-- Bug12529647, use the same group_id rather than 2 different ids.
209 x_txn_qty ,
210 x_txn_uom ,
211 x_primary_qty ,
212 x_primary_uom ,
213 p_transaction_type ,
214 p_Receiving_Location_Id,
215 p_Return_Reason_Id ,
216 p_subinventory ,
217 p_RMA_Reference ,
218 x_employee_id ,
219 p_Comments ,
220 x_parent_interface_txn_id);
221
222 insert_row(p_transaction_date ,
223 x_grandparent_id ,
224 p_group_id ,-- Bug12529647, use the same group_id rather than 2 different ids.
225 x_txn_qty ,
226 x_txn_uom ,
227 x_primary_qty ,
228 x_primary_uom ,
229 p_transaction_type ,
230 p_Receiving_Location_Id,
231 p_Return_Reason_Id ,
232 p_subinventory ,
233 p_RMA_Reference ,
234 x_employee_id ,
235 p_Comments ,
236 x_parent_interface_txn_id);
237
238 elsif p_transaction_type = 'CORRECT' and
239 x_parent_type = 'RETURN TO RECEIVING' and
240 x_txn_qty < 0 then
241
242 -- grand parent is the rtv transaction
243 x_grandparent_id := get_rtv_id(p_parent_transaction_id);
244
245 -- correct rtv first where qty is -'ve
246 insert_row(p_transaction_date ,
247 x_grandparent_id ,
248 p_group_id ,
249 x_txn_qty ,
250 x_txn_uom ,
251 x_primary_qty ,
252 x_primary_uom ,
253 p_transaction_type ,
254 p_Receiving_Location_Id,
255 p_Return_Reason_Id ,
256 p_subinventory ,
257 p_RMA_Reference ,
258 x_employee_id ,
259 p_Comments ,
260 x_parent_interface_txn_id);
261
262 insert_row(p_transaction_date ,
263 p_parent_transaction_id,
264 p_group_id2 ,
265 x_txn_qty ,
266 x_txn_uom ,
267 x_primary_qty ,
268 x_primary_uom ,
269 p_transaction_type ,
270 p_Receiving_Location_Id,
271 p_Return_Reason_Id ,
272 p_subinventory ,
273 p_RMA_Reference ,
274 x_employee_id ,
275 p_Comments ,
276 x_parent_interface_txn_id);
277
278 elsif p_transaction_type = 'CORRECT' and
279 x_parent_type = 'RETURN TO RECEIVING' and
280 x_txn_qty > 0 then
281
282 -- grand parent is the rtv transaction
283 x_grandparent_id := get_rtv_id(p_parent_transaction_id);
284
285 -- correct rtr first where qty is +'ve
286 insert_row(p_transaction_date ,
287 p_parent_transaction_id,
288 p_group_id ,
289 x_txn_qty ,
290 x_txn_uom ,
291 x_primary_qty ,
292 x_primary_uom ,
293 p_transaction_type ,
294 p_Receiving_Location_Id,
295 p_Return_Reason_Id ,
296 p_subinventory ,
297 p_RMA_Reference ,
298 x_employee_id ,
299 p_Comments ,
300 x_parent_interface_txn_id);
301
302 insert_row(p_transaction_date ,
303 x_grandparent_id ,
304 p_group_id2 ,
305 x_txn_qty ,
306 x_txn_uom ,
307 x_primary_qty ,
308 x_primary_uom ,
309 p_transaction_type ,
310 p_Receiving_Location_Id,
311 p_Return_Reason_Id ,
312 p_subinventory ,
313 p_RMA_Reference ,
314 x_employee_id ,
315 p_Comments ,
316 x_parent_interface_txn_id);
317 end if;
318
319 if (MO_GLOBAL.get_current_org_id <> x_user_org_id) then
320 mo_global.set_policy_context(p_access_mode => 'S',
321 p_org_id => x_user_org_id);
322 end if;
323
324 return 0;
325
326 exception
327 when others THEN
328 if (MO_GLOBAL.get_current_org_id <> x_user_org_id) then
329 mo_global.set_policy_context(p_access_mode => 'S',
330 p_org_id => x_user_org_id);
331 end if;
332 ERROR_STACK.PUSHMESSAGE( substr(SQLERRM,12,512),'ICX');
333 return 1;
334
335 end insert_transaction_interface;
336
337 /*************************************************************
338 ** Function : Process_Transactions
339 ** Description : This is a procedure that validates
340 ** the transactions and call_txn_processor.
341 **************************************************************/
342
343 function process_transactions(p_group_id in number,
344 p_group_id2 in number,
345 p_caller in varchar2)
346 return number is
347
348 CURSOR rcv_get_interface_rows IS
349 SELECT *
350 FROM rcv_transactions_interface
351 WHERE group_id = p_group_id OR group_id = p_group_id2
352 ORDER BY interface_transaction_id;
353
354 rcv_trx rcv_transactions_interface%ROWTYPE;
355 x_return number;
356 valid_wip_info NUMBER :=0;
357 X_column_name VARCHAR2(30);
358 X_err_message VARCHAR2(240);
359
360 BEGIN
361
362 /*
363 ** if this is a shop floor destination then make sure that the job
364 ** information is still valid
365 */
366
367 OPEN rcv_get_interface_rows;
368 LOOP
369 FETCH rcv_get_interface_rows INTO
370 rcv_trx;
371 EXIT WHEN rcv_get_interface_rows%NOTFOUND;
372
373 IF (rcv_trx.destination_type_code = 'SHOP FLOOR') THEN
374 valid_wip_info := rcv_transactions_sv.val_wip_info (
375 rcv_trx.to_organization_id,
376 rcv_trx.wip_entity_id,
377 rcv_trx.wip_operation_seq_num,
378 rcv_trx.wip_resource_seq_num,
379 rcv_trx.wip_line_id,
380 rcv_trx.wip_repetitive_schedule_id,
381 rcv_trx.po_line_id);
382
383 IF (valid_wip_info <> 0) THEN
384
385 if valid_wip_info = 10 then
386
387 X_column_name := 'TO_ORGANIZATION_ID';
388 X_err_message := 'RCV_DEST_ORG_NA';
389
390 elsif valid_wip_info = 20 then
391
392 X_column_name := 'WIP_ENTITY_ID';
393 X_err_message := 'RCV_WIP_ENTITY_ID_NA';
394
395 elsif valid_wip_info = 30 then
396
397 X_column_name := 'WIP_OP_SEQ_NUM';
398 X_err_message := 'RCV_WIP_OP_SEQ_NUM_NA';
399
400 elsif valid_wip_info = 40 then
401
402 X_column_name := 'WIP_RES_SEQ_NUM';
403 X_err_message := 'RCV_WIP_RES_SEQ_NUM_NA';
404
405 elsif valid_wip_info = 50 then
406
407 X_column_name := 'WIP_REPETITIVE_SCHEDULE_ID';
408 X_err_message := 'RCV_WIP_REP_SCH_JOB_NOT_OPEN';
409
410 elsif valid_wip_info = 60 then
411
412 X_column_name := '_WIP_ENTITY_ID';
413 X_err_message := 'RCV_WIP_JOB_NOT_OPEN';
414
415 end if;
416
417 insert_interface_errors(rcv_trx,
418 X_column_name,
419 X_err_message);
420
421
422 EXIT;
423 END IF;
424
425 END IF;
426 END LOOP;
427
428 /* If job information is valid proceed to returns */
429
430 IF (valid_wip_info = 0) THEN
431
432 x_return := por_rcv_ord_SV.call_txn_processor(p_group_id, p_caller);
433
434 if x_return = 0 then
435
436 x_return := por_rcv_ord_SV.call_txn_processor(p_group_id2, p_caller);
437
438 end if;
439
440 return x_return;
441
442 ELSE
443 return 98; /* error code for invalid job information */
444 END IF;
445
446 end process_transactions;
447
448
449 function get_net_delivered_qty(p_txn_id in number) return number is
450
451 X_progress VARCHAR2(3) := '000';
452
453 X_delivered_quantity NUMBER := 0;
454
455 v_txn_id NUMBER := 0;
456 v_quantity NUMBER := 0;
457 v_transaction_type VARCHAR2(25) := '';
458 v_parent_id NUMBER := 0;
459 v_parent_type VARCHAR2(25) := '';
460
461 CURSOR c_txn_history (c_transaction_id NUMBER) IS
462 SELECT
463 transaction_id,
464 nvl(quantity, amount),
465 transaction_type,
466 parent_transaction_id
467 FROM
468 rcv_transactions
469 START WITH transaction_id = c_transaction_id
470 CONNECT BY parent_transaction_id = PRIOR transaction_id;
471
472 begin
473
474 OPEN c_txn_history(p_txn_id);
475
476 X_progress := '003';
477
478 --asn_debug.put_line('TXN HISTOR');
479 LOOP
480 FETCH c_txn_history INTO v_txn_id,
481 v_quantity,
482 v_transaction_type,
483 v_parent_id;
484
485 EXIT WHEN c_txn_history%NOTFOUND;
486
487 X_progress := '004';
488 -- asn_debug.put_line('TRANSACTION TYPE ' || v_transaction_type);
489 -- asn_debug.put_line('QUANTITY ' || v_quantity);
490
491 IF v_transaction_type = 'DELIVER' THEN
492
493 X_delivered_quantity := X_delivered_quantity + v_quantity;
494
495 ELSIF v_transaction_type = 'RETURN TO RECEIVING' THEN
496
497 X_delivered_quantity := X_delivered_quantity - v_quantity;
498
499 ELSIF v_transaction_type = 'CORRECT' THEN
500
501 /* The correction function is based on parent transaction type */
502
503 SELECT
504 transaction_type
505 INTO
506 v_parent_type
507 FROM
508 rcv_transactions
509 WHERE
510 transaction_id = v_parent_id;
511
512 IF v_parent_type = 'DELIVER' THEN
513
514 X_delivered_quantity := X_delivered_quantity + v_quantity;
515
516 ELSIF v_parent_type = 'RETURN TO RECEIVING' THEN
517
518 X_delivered_quantity := X_delivered_quantity - v_quantity;
519
520 END IF;
521 END IF;
522
523 END LOOP;
524
525 CLOSE c_txn_history;
526
527 return X_delivered_quantity;
528
529 end get_net_delivered_qty;
530
531 function get_net_returned_qty(p_txn_id in number) return number is
532
533 X_progress VARCHAR2(3) := '000';
534
535 X_returned_quantity NUMBER := 0;
536
537 v_txn_id NUMBER := 0;
538 v_quantity NUMBER := 0;
539 v_transaction_type VARCHAR2(25) := '';
540 v_parent_id NUMBER := 0;
541 v_parent_type VARCHAR2(25) := '';
542
543 CURSOR c_txn_history (c_transaction_id NUMBER) IS
544 SELECT
545 transaction_id,
546 quantity,
547 transaction_type,
548 parent_transaction_id
549 FROM
550 rcv_transactions
551 START WITH transaction_id = c_transaction_id
552 CONNECT BY parent_transaction_id = PRIOR transaction_id;
553
554 begin
555
556 OPEN c_txn_history(p_txn_id);
557
558 X_progress := '003';
559
560 LOOP
561 FETCH c_txn_history INTO v_txn_id,
562 v_quantity,
563 v_transaction_type,
564 v_parent_id;
565
566 EXIT WHEN c_txn_history%NOTFOUND;
567
568 X_progress := '004';
569
570 IF v_transaction_type in ('RETURN TO RECEIVING', 'RETURN TO VENDOR') THEN
571
572 X_returned_quantity := X_returned_quantity + v_quantity;
573
574 ELSIF v_transaction_type = 'CORRECT' THEN
575
576 /* The correction function is based on parent transaction type */
577
578 SELECT
579 transaction_type
580 INTO
581 v_parent_type
582 FROM
583 rcv_transactions
584 WHERE
585 transaction_id = v_parent_id;
586
587 IF v_parent_type in ('RETURN TO RECEIVING', 'RETURN TO VENDOR') THEN
588
589 X_returned_quantity := X_returned_quantity + v_quantity;
590
591 END IF;
592 END IF;
593
594 END LOOP;
595
596 CLOSE c_txn_history;
597
598 return X_returned_quantity;
599
600 end get_net_returned_qty;
601
602 procedure insert_row(p_transaction_date in date,
603 p_parent_transaction_id in number,
604 p_group_id in number,
605 p_txn_qty in number,
606 p_txn_uom in varchar2,
607 p_primary_qty in number,
608 p_primary_uom in varchar2,
609 p_transaction_type in varchar2,
610 p_Receiving_Location_Id in number,
611 p_Return_Reason_Id in number,
612 p_subinventory in varchar2,
613 p_RMA_Reference in varchar2,
614 p_employee_id in number,
615 p_Comments in varchar2,
616 x_parent_interface_txn_id in out nocopy number) IS
617
618 x_create_debit_memo_flag varchar2(1) := null;
619 x_from_subinventory varchar2(240) := null;
620 x_from_locator_id number := null;
621 x_interface_id number :=null;
622 x_uom_code VARCHAR2(5) := NULL;
623
624 begin
625 -- for amount based lines x_uom_code is populated as currency code in RT hence NO_DATA_FOUND arises.
626 BEGIN
627 select uom_code
628 into x_uom_code
629 from mtl_units_of_measure
630 where unit_of_measure = p_txn_uom;
631
632 EXCEPTION
633 WHEN NO_DATA_FOUND THEN
634 x_uom_code := NULL;
635
636 END ;
637
638 if p_transaction_type = 'RETURN TO VENDOR' then
639 begin
640 if fnd_profile.Value('POR_ENABLE_DEBIT_MEMO') = 'Y' then
641 select povs.create_debit_memo_flag
642 into x_create_debit_memo_flag
643 from po_vendor_sites povs, rcv_transactions rt
644 where povs.vendor_site_id = rt.vendor_site_id
645 and rt.transaction_id = p_parent_transaction_id;
646 else
647 x_create_debit_memo_flag := 'N';
648 end if;
649 exception
650 when others then
651 x_create_debit_memo_flag := 'N';
652 end;
653 end if;
654
655 if p_transaction_type = 'RETURN TO VENDOR' or
656 p_txn_qty < 0
657 then
658 select rt.subinventory, rt.locator_id
659 into x_from_subinventory, x_from_locator_id
660 from rcv_transactions rt
661 where rt.transaction_id = p_parent_transaction_id;
662 else
663 select rt.from_subinventory, rt.from_locator_id
664 into x_from_subinventory, x_from_locator_id
665 from rcv_transactions rt
666 where rt.transaction_id = p_parent_transaction_id;
667 end if;
668
669 -- Setting validation flag to Y for ROI
670 Select RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL
671 INTO x_interface_id
672 FROM DUAL;
673
674 insert into RCV_TRANSACTIONS_INTERFACE
675 ( receipt_source_code,
676 interface_transaction_id,
677 group_id,
678 org_id,
679 last_update_date,
680 last_updated_by,
681 created_by,
682 creation_date,
683 last_update_login,
684 source_document_code,
685 destination_type_code,
686 transaction_date,
687 quantity,
688 unit_of_measure,
689 amount,
690 shipment_header_id,
691 shipment_line_id,
692 substitute_unordered_code,
693 employee_id,
694 parent_transaction_id,
695 inspection_status_code,
696 inspection_quality_code,
697 po_header_id,
698 po_release_id,
699 po_line_id,
700 po_line_location_id,
701 po_distribution_id,
702 po_revision_num,
703 po_unit_price,
704 currency_code,
705 currency_conversion_rate,
706 currency_conversion_type,
707 currency_conversion_date,
708 requisition_line_id,
709 routing_header_id,
710 routing_step_id,
711 comments,
712 attribute_category,
713 attribute1,
714 attribute2,
715 attribute3,
716 attribute4,
717 attribute5,
718 attribute6,
719 attribute7,
720 attribute8,
721 attribute9,
722 attribute10,
723 attribute11,
724 attribute12,
725 attribute13,
726 attribute14,
727 attribute15,
728 transaction_type,
729 location_id,
730 processing_status_code,
731 processing_mode_code,
732 transaction_status_code,
733 category_id,
734 vendor_lot_num,
735 reason_id,
736 primary_quantity,
737 primary_unit_of_measure,
738 item_id,
739 item_revision,
740 to_organization_id,
741 deliver_to_location_id,
742 destination_context,
743 vendor_id,
744 deliver_to_person_id,
745 subinventory,
746 from_subinventory,
747 locator_id,
748 from_locator_id,
749 wip_entity_id,
750 wip_line_id,
751 wip_repetitive_schedule_id,
752 wip_operation_seq_num,
753 wip_resource_seq_num,
754 bom_resource_id,
755 from_organization_id,
756 receipt_exception_flag,
757 department_code,
758 item_description,
759 movement_id,
760 use_mtl_lot,
761 use_mtl_serial,
762 RMA_REFERENCE,
763 ussgl_transaction_code,
764 government_context,
765 vendor_site_id,
766 create_debit_memo_flag,
767 job_id,
768 matching_basis, parent_interface_txn_id,uom_code)
769 select
770 rh.receipt_source_code,
771 x_interface_id,
772 p_group_id,
773 MO_GLOBAL.get_current_org_id,
774 SYSDATE,
775 fnd_global.user_id,
776 fnd_global.user_id,
777 SYSDATE,
778 fnd_global.user_id,
779 rt.source_document_code,
780 rt.destination_type_code,
781 p_transaction_date,
782 decode(pol.matching_basis,'AMOUNT',null,p_txn_qty),
783 nvl(p_txn_uom, rt.unit_of_measure),
784 decode(pol.matching_basis,'AMOUNT',p_txn_qty,null),
785 rt.shipment_header_id,
786 rt.shipment_line_id,
787 rt.substitute_unordered_code,
788 p_employee_id,
789 p_parent_transaction_id,
790 rt.inspection_status_code,
791 rt.inspection_quality_code,
792 rt.po_header_id,
793 rt.po_release_id,
794 rt.po_line_id,
795 rt.po_line_location_id,
796 rt.po_distribution_id,
797 rt.po_revision_num,
798 rt.po_unit_price,
799 rt.currency_code,
800 rt.currency_conversion_rate,
801 rt.currency_conversion_type,
802 rt.currency_conversion_date,
803 rt.requisition_line_id,
804 rt.routing_header_id,
805 rt.routing_step_id,
806 p_Comments,
807 rt.attribute_category,
808 rt.attribute1,
809 rt.attribute2,
810 rt.attribute3,
811 rt.attribute4,
812 rt.attribute5,
813 rt.attribute6,
814 rt.attribute7,
815 rt.attribute8,
816 rt.attribute9,
817 rt.attribute10,
818 rt.attribute11,
819 rt.attribute12,
820 rt.attribute13,
821 rt.attribute14,
822 rt.attribute15,
823 p_transaction_type,
824 nvl(p_Receiving_Location_Id, rt.location_id),
825 'PENDING',
826 'ONLINE',
827 'PENDING',
828 rl.category_id,
829 rt.vendor_lot_num,
830 nvl(p_Return_Reason_Id, rt.reason_id),
831 p_primary_qty,
832 p_primary_uom,
833 rl.item_id,
834 rl.item_revision,
835 rl.to_organization_id,
836 rt.deliver_to_location_id,
837 rt.destination_context,
838 rt.vendor_id,
839 rt.deliver_to_person_id,
840 nvl(p_subinventory, rt.subinventory),
841 x_from_subinventory,
842 rt.locator_id,
843 x_from_locator_id,
844 rt.wip_entity_id,
845 rt.wip_line_id,
846 rt.wip_repetitive_schedule_id,
847 rt.wip_operation_seq_num,
848 rt.wip_resource_seq_num,
849 rt.bom_resource_id,
850 rt.organization_id,
851 rt.receipt_exception_flag,
852 rt.department_code,
853 rl.item_description,
854 null,
855 msi.lot_control_code,
856 msi.SERIAL_NUMBER_CONTROL_CODE,
857 p_RMA_Reference,
858 NULL,
859 NULL,
860 rt.vendor_site_id,
861 x_create_debit_memo_flag,
862 rt.job_id,
863 pol.matching_basis,
864 x_parent_interface_txn_id,
865 nvl(x_uom_code,rt.uom_code)
866 from rcv_transactions rt,
867 rcv_shipment_lines rl,
868 rcv_shipment_headers rh,
869 mtl_system_items msi,
870 po_Lines_all pol
871 where transaction_id = p_parent_transaction_id
872 and rt.shipment_line_id = rl.shipment_line_id
873 and rl.shipment_header_id = rh.shipment_header_id
874 and MSI.INVENTORY_ITEM_ID(+) = RL.ITEM_ID
875 and NVL(MSI.ORGANIZATION_ID, RT.ORGANIZATION_ID) = RT.ORGANIZATION_ID
876 and pol.po_line_id(+) = rt.po_line_id;
877
878 x_parent_interface_txn_id := x_interface_id;
879
880 end insert_row;
881
882 function get_rtv_id(p_transaction_id in number) return number is
883
884 x_deliver_id number;
885 x_receive_id number;
886 x_rtv_id number;
887
888 begin
889
890 -- Get the receive and deliver txn first
891
892 select rt2.transaction_id,
893 rt2.parent_transaction_id
894 into x_deliver_id,
895 x_receive_id
896 from rcv_transactions rt1,
897 rcv_transactions rt2
898 where rt1.parent_transaction_id = rt2.transaction_id
899 and rt1.transaction_id = p_transaction_id;
900
901 -- Get the RTV transaction
902
903 select min(transaction_id)
904 into x_rtv_id
905 from rcv_transactions
906 where parent_transaction_id = x_receive_id
907 and transaction_type = 'RETURN TO VENDOR'
908 and get_net_returned_qty(transaction_id) = get_net_returned_qty(p_transaction_id);
909
910
911 return x_rtv_id;
912
913 end get_rtv_id;
914
915 function GET_SHIPMENT_NUM(p_order_type_code in varchar2,
916 p_key_id in number)
917
918 return varchar2 is
919
920 CURSOR c_req_shipment (c_req_line_id NUMBER) IS
921 SELECT
922 rsh.shipment_num, rsh.shipment_header_id
923 FROM
924 rcv_shipment_headers rsh,
925 rcv_shipment_lines rsl
926 WHERE
927 rsh.shipment_header_id = rsl.shipment_header_id and
928 rsl.requisition_line_id = c_req_line_id;
929
930 CURSOR c_po_shipment (c_line_location_id NUMBER) IS
931 SELECT
932 rsh.shipment_num, rsh.shipment_header_id
933 FROM
934 rcv_shipment_headers rsh,
935 rcv_shipment_lines rsl
936 WHERE
937 rsh.shipment_header_id = rsl.shipment_header_id and
938 rsl.po_line_location_id = c_line_location_id and
939 rsh.asn_type is not null;
940
941 x_shipment_num varchar2(30);
942 x_counter number;
943 x_shipment_header_id number;
944 x_old_shipment_header_id number;
945
946 begin
947 x_counter :=0;
948 x_old_shipment_header_id := 0;
949 x_shipment_header_id := 0;
950
951 if (p_order_type_code = 'REQ') then
952 OPEN c_req_shipment(p_key_id);
953
954 LOOP
955 FETCH c_req_shipment INTO x_shipment_num, x_shipment_header_id;
956 EXIT WHEN c_req_shipment %NOTFOUND;
957 if(x_counter = 0) then
958 x_old_shipment_header_id := x_shipment_header_id;
959 x_counter := x_counter + 1;
960 elsif (x_old_shipment_header_id <> x_shipment_header_id) then
961 x_shipment_num := fnd_message.get_string('ICX', 'ICX_POR_MULTIPLE');
962 exit;
963 end if;
964
965 END LOOP;
966
967 close c_req_shipment;
968 else
969 OPEN c_po_shipment(p_key_id);
970
971 LOOP
972 FETCH c_po_shipment INTO x_shipment_num, x_shipment_header_id;
973 EXIT WHEN c_po_shipment %NOTFOUND;
974 if(x_counter = 0) then
975 x_old_shipment_header_id := x_shipment_header_id;
976 x_counter := x_counter + 1;
977 elsif (x_old_shipment_header_id <> x_shipment_header_id) then
978 x_shipment_num := fnd_message.get_string('ICX', 'ICX_POR_MULTIPLE');
979 exit;
980 end if;
981
982 END LOOP;
983
984 close c_po_shipment;
985 end if;
986 return x_shipment_num;
987 end GET_SHIPMENT_NUM;
988
989 /*===========================================================================
990
991 PROCEDURE NAME: Insert_Interface_Errors
992
993 ===========================================================================*/
994
995 /*
996 ** Insert into PO_INTERFACE_ERRORS table
997 */
998
999
1000 PROCEDURE insert_interface_errors ( rcv_trx IN OUT NOCOPY rcv_transactions_interface%ROWTYPE,
1001 X_column_name IN VARCHAR2,
1002 X_err_message IN VARCHAR2) as
1003
1004 X_progress VARCHAR2(3) := '000';
1005
1006 begin
1007
1008 X_progress := '050';
1009
1010 INSERT INTO po_interface_errors (interface_type,
1011 interface_transaction_id,
1012 column_name,
1013 error_message_name,
1014 batch_id,
1015 creation_date,
1016 created_by,
1017 last_update_date,
1018 last_updated_by,
1019 last_update_login,
1020 request_id,
1021 program_application_id,
1022 program_id,
1023 program_update_date)
1024 VALUES ( rcv_trx.transaction_type,
1025 rcv_trx.interface_transaction_id,
1026 X_column_name,
1027 X_err_message,
1028 rcv_trx.group_id,
1029 rcv_trx.creation_date,
1030 rcv_trx.created_by,
1031 rcv_trx.last_update_date,
1032 rcv_trx.last_updated_by,
1033 rcv_trx.last_update_login,
1034 rcv_trx.request_id,
1035 rcv_trx.program_application_id,
1036 rcv_trx.program_id,
1037 rcv_trx.program_update_date);
1038
1039 commit;
1040
1041 exception
1042 when others then
1043 po_message_s.sql_error('insert_interface_errors', x_progress, sqlcode);
1044 raise;
1045
1046 end insert_interface_errors;
1047
1048 end POR_RCV_TRANSACTION_SV;