[Home] [Help]
67: -- kabalakr Wed Oct 29 23:19:22 PST 2003.
68:
69: CURSOR int_txn (grp_id NUMBER, txn_id NUMBER) IS
70: SELECT max(interface_transaction_id)
71: FROM rcv_transactions_interface
72: WHERE group_id = grp_id
73: AND parent_transaction_id = txn_id;
74:
75: l_rti_int_txn_id NUMBER;
77: -- Bug 8806035.ntungare
78: -- Added this cursor and variable for copying the supplier lot number information.
79: CURSOR vend_lot_num (txn_id NUMBER) IS
80: SELECT vendor_lot_num
81: FROM rcv_transactions
82: WHERE transaction_id = txn_id;
83:
84: l_vendor_lot_num VARCHAR2(30) := NULL;
85:
689: p_reinsp_flag out NOCOPY varchar2) is
690:
691: cursor txn_type (x_txn_id in number) is
692: select transaction_type
693: from rcv_transactions
694: where transaction_id = x_txn_id;
695:
696: begin
697:
851: p_rcv_txn_id NUMBER;
852: CURSOR shp_hdr_rcv_txns(x_lpn_id NUMBER, x_shipment_line_id NUMBER) IS
853: SELECT rt.shipment_header_id shipment_header_id,
854: rt.transaction_id transaction_id
855: FROM rcv_transactions rt,
856: rcv_supply rs
857: WHERE rs.lpn_id = x_lpn_id AND
858: rt.transaction_id=rs.supply_source_id AND
859: rt.transaction_type='RECEIVE' AND
919: --
920: if p_rma_id is not null and p_rma_id > 0 then
921: shls_query :=
922: ' select distinct rs.shipment_line_id ' ||
923: ' from rcv_supply rs, rcv_transactions rt, rcv_shipment_headers rsh ' ||
924: ' where rsh.receipt_source_code = ''CUSTOMER'' ' ||
925: ' and rs.oe_order_header_id = :1 ' ||
926: ' and rs.to_organization_id = :2 ' ||
927: ' and rs.item_id = :3 ' ||
942: --
943: elsif p_int_ship_id is not null and p_int_ship_id > 0 then
944: shls_query :=
945: ' select distinct rs.shipment_line_id ' ||
946: ' from rcv_supply rs, rcv_transactions rt, rcv_shipment_headers rsh ' ||
947: ' where rsh.receipt_source_code <> ''VENDOR'' ' ||
948: ' and rs.shipment_header_id = :1 ' ||
949: ' and rs.to_organization_id = :2 ' ||
950: ' and rs.item_id = :3 ' ||
966: elsif p_receipt_num is not null then
967: shls_query :=
968: ' select distinct rsl.shipment_line_id ' ||
969: ' from rcv_supply rs, ' ||
970: ' rcv_transactions rt, ' ||
971: ' rcv_shipment_headers rsh, ' ||
972: ' rcv_shipment_lines rsl ' ||
973: ' where rsh.receipt_num = :1 ' ||
974: ' and rsh.shipment_header_id = rs.shipment_header_id ' ||
991: -- because inspection_status_code of rt remains at not_inspected' even for skipped
992: -- shipment lines.pdube Fri May 15 02:20:44 PDT 2009
993: /*shls_query :=
994: ' SELECT distinct rs.shipment_line_id ' ||
995: ' FROM rcv_supply rs, rcv_transactions rt, po_headers ph ' ||
996: ' WHERE rs.rcv_transaction_id = rt.transaction_id ' ||
997: ' AND rs.po_header_id = ph.po_header_id ' ||
998: ' AND ph.segment1 = :1 ' ||
999: ' AND rs.to_organization_id = :2 ' ||
1005: -- bug 9652549 CLM changes
1006: --
1007: shls_query :=
1008: ' SELECT distinct rs.shipment_line_id ' ||
1009: ' FROM rcv_supply rs, rcv_transactions rt,qa_skiplot_rcv_results qsrr , PO_HEADERS_TRX_V ph '||
1010: ' WHERE rs.rcv_transaction_id = rt.transaction_id ' ||
1011: ' AND rs.po_header_id = ph.po_header_id ' ||
1012: ' AND ph.segment1 = :1 ' ||
1013: ' AND rs.to_organization_id = :2 ' ||
1052: p_rej_qty out NOCOPY number) is
1053:
1054: cursor get_coll_id (x_txn_id in number) is
1055: select qa_collection_id
1056: from rcv_transactions
1057: where transaction_id = x_txn_id;
1058:
1059: coll_id number;
1060: result varchar2(100);
1456: transaction_type
1457: INTO l_txn_id,
1458: temp_parent_txn_id,
1459: temp_txn_type
1460: FROM RCV_TRANSACTIONS
1461: WHERE SHIPMENT_LINE_ID = p_shipment_line_id
1462: AND SHIPMENT_HEADER_ID = p_shipment_header_id
1463: AND TRANSACTION_ID = p_rcv_txn_id;
1464:
1464:
1465: IF temp_txn_type = 'TRANSFER' THEN
1466: SELECT INTERFACE_TRANSACTION_ID into l_txn_id
1467: FROM (SELECT INTERFACE_TRANSACTION_ID , transaction_type
1468: FROM RCV_TRANSACTIONS
1469: START WITH transaction_id = temp_parent_txn_id
1470: CONNECT BY PRIOR parent_transaction_id = transaction_id)
1471: WHERE transaction_type = 'RECEIVE';
1472: END IF;
1482: --
1483: ELSE
1484: BEGIN
1485: SELECT INTERFACE_TRANSACTION_ID into l_txn_id
1486: FROM RCV_TRANSACTIONS
1487: WHERE SHIPMENT_LINE_ID = p_shipment_line_id;
1488: EXCEPTION WHEN TOO_MANY_ROWS THEN
1489: NULL;
1490: END;
1769: -- Bug 8806035.ntungare
1770: -- Added this cursor and variable for copying the supplier lot number information.
1771: CURSOR vend_lot_num (txn_id NUMBER) IS
1772: SELECT vendor_lot_num
1773: FROM rcv_transactions
1774: WHERE transaction_id = txn_id;
1775: l_vendor_lot_num VARCHAR2(30) := NULL;
1776:
1777: