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