[Home] [Help]
56: , 'Vendor'
57: , poh.note_to_receiver
58: , to_char(poh.org_id) --
59: FROM rcv_supply ms
60: , rcv_transactions rt
61: , po_headers_all poh
62: , po_lines_all pol
63: , po_line_types plt
64: , mtl_parameters mp
82: AND msik.mtl_transactions_enabled_flag = 'N'))))
83: AND poh.segment1 LIKE (p_po_number)
84: AND (exists
85: (SELECT 1
86: FROM rcv_transactions rt1
87: WHERE rt1.transaction_id = rt.transaction_id
88: AND rt1.inspection_status_code <> 'NOT INSPECTED'
89: AND rt1.routing_header_id = 2)
90: OR rt.routing_header_id <> 2
106: poh.Note_To_Receiver ,
107: to_char(poh.org_id) --
108:
109: FROM rcv_Supply ms ,
110: rcv_Transactions rt,
111: po_Headers_trx_v poh , -- CLM, bug 9403291
112: mtl_Parameters mp
113: WHERE poh.po_Header_Id = ms.po_Header_Id
114: AND ms.Quantity > 0
149: ))))
150: AND poh.Segment1 LIKE (p_po_number||l_append)
151: AND (EXISTS
152: (SELECT 1
153: FROM rcv_Transactions rt1
154: WHERE rt1.Transaction_Id = rt.Transaction_Id
155: AND rt1.Inspection_Status_Code <> 'NOT INSPECTED'
156: AND rt1.RoutIng_Header_Id = 2
157: )
253: ELSIF p_mobile_form = 'INSPECT' THEN
254: OPEN x_po_num_lov FOR
255: /*Bug: 4951739
256: Modified query: Referencing base table instead of
257: RCV_TRANSACTIONS_V
258: */
259: SELECT DISTINCT poh.segment1 ,
260: poh.po_header_id ,
261: poh.type_lookup_code ,
267: 'Vendor' ,
268: poh.note_to_receiver ,
269: to_char(poh.org_id)
270: FROM RCV_SUPPLY RSUP,
271: RCV_TRANSACTIONS RT,
272: PO_LOOKUP_CODES PLC,
273: PO_LINES_trx_v pol, -- CLM project, bug 9403291
274: PO_LINE_LOCATIONS_trx_v PLL, -- CLM project, bug 9403291
275: PO_HEADERS_trx_v POH, -- CLM project, bug 9403291
611: FROM
612: rcv_shipment_headers rsh,
613: rcv_shipment_lines rsl,
614: rcv_supply ms,
615: rcv_transactions rt,
616: org_organization_definitions ood
617: WHERE rsh.shipment_header_id = ms.shipment_header_id
618: AND ms.to_organization_id = p_organization_id
619: AND rt.organization_id = p_organization_id
624: AND rsl.shipment_header_id = rsh.shipment_header_id
625: AND Nvl(ms.quantity,0) > 0
626: AND (exists
627: (SELECT 1
628: FROM rcv_transactions rt1
629: WHERE rt1.transaction_id = rt.transaction_id
630: AND rt1.inspection_status_code <> 'NOT INSPECTED'
631: AND rt1.routing_header_id = 2)
632: OR rt.routing_header_id <> 2
700: ELSIF p_mobile_form = 'INSPECT' THEN
701: OPEN x_shipment_num_lov FOR
702: /*Bug: 4951739
703: Modified query: Referencing base table instead of
704: RCV_TRANSACTIONS_V
705: */
706: SELECT DISTINCT rsh.shipment_num,
707: rsh.shipment_header_id,
708: rsh.shipped_date,
715: rsh.waybill_airbill_num,
716: rsh.freight_carrier_code
717: FROM RCV_SUPPLY RSUP,
718: RCV_SHIPMENT_LINES RSL,
719: RCV_TRANSACTIONS RT,
720: RCV_SHIPMENT_HEADERS RSH,
721: PO_LOOKUP_CODES PLC,
722: PO_LINE_LOCATIONS_ALL PLL,
723: HR_ALL_ORGANIZATION_UNITS_TL OOD
787: FROM
788: po_req_headers_trx_v prh, -- CLM project, bug 9403291
789: rcv_supply ms,
790: po_req_lines_trx_v prl, -- CLM project, bug 9403291
791: rcv_transactions rt
792: WHERE
793: prh.requisition_header_id = ms.req_header_id
794: AND prl.requisition_header_id = prh.requisition_header_id
795: AND prl.destination_organization_id = p_organization_id
804: -- Added the following line to avoid a full table scan
805: AND rt.requisition_line_id = prl.requisition_line_id
806: AND (exists
807: (SELECT 1
808: FROM rcv_transactions rt1
809: WHERE rt1.transaction_id = rt.transaction_id
810: AND rt1.inspection_status_code <> 'NOT INSPECTED'
811: AND rt1.routing_header_id = 2)
812: OR rt.routing_header_id <> 2
864: AND exists
865: (SELECT 1
866: FROM rcv_shipment_lines rsl,
867: rcv_shipment_headers rsh,
868: rcv_transactions_v rtv
869: WHERE rsl.requisition_line_id = prl.requisition_line_id
870: AND rsh.shipment_header_id = rsl.shipment_header_id
871: AND rtv.shipment_header_id = rsh.shipment_header_id
872: AND rtv.shipment_line_id = rsl.shipment_line_id
952: , rt.oe_order_header_id
953: , rt.po_release_id --bug6594996 add PO release header ID associated with the receip
954: , ms.from_organization_id -- bug #6917248
955: FROM rcv_supply ms
956: , rcv_transactions rt
957: , rcv_shipment_headers rsh
958: , rcv_shipment_lines rsl
959: , mtl_parameters mp
960: WHERE rsh.shipment_header_id = ms.shipment_header_id
987: AND rt.transaction_type IN ('ACCEPT','MATCH','RECEIVE',
988: 'REJECT','RETURN TO RECEIVING','TRANSFER')
989: AND (exists
990: (SELECT 1
991: FROM rcv_transactions rt1
992: WHERE rt1.transaction_id = rt.transaction_id
993: AND rt1.inspection_status_code <> 'NOT INSPECTED'
994: AND rt1.routing_header_id = 2)
995: OR rt.routing_header_id <> 2
1098: begin
1099: OPEN x_getRcptNumLOV FOR
1100: /*Bug: 4951739
1101: Modified query: Referencing base table instead of
1102: RCV_TRANSACTIONS_V
1103: */
1104: SELECT DISTINCT rsh.receipt_num ,
1105: null ,
1106: rsup.shipment_header_id ,
1109: null
1110: , null -- bug # 6917248
1111: , rsup.from_organization_id -- bug # 6917248
1112: FROM RCV_SUPPLY RSUP,
1113: RCV_TRANSACTIONS RT,
1114: RCV_SHIPMENT_HEADERS RSH,
1115: RCV_SHIPMENT_LINES RSL,
1116: PO_LOOKUP_CODES PLC,
1117: PO_LINE_LOCATIONS_ALL PLL,
1180: OTT_ALL.ORDER_CATEGORY_CODE ORDER_TYPE_CODE, --OLT.ORDER_CATEGORY_CODE ORDER_TYPE_CODE,
1181: oec.customer_id,
1182: oec.name customer_name,
1183: oec.customer_number
1184: FROM rcv_transactions rt,
1185: rcv_supply ms,
1186: oe_order_headers_all oeh,
1187: oe_order_lines_all oel,
1188: -- oe_line_types_v oet,
1207: -- AND oel.line_type_id = oet.line_type_id
1208: AND oeh.sold_to_org_id = oec.customer_id
1209: AND (exists
1210: (SELECT 1
1211: FROM rcv_transactions rt1
1212: WHERE rt1.transaction_id = rt.transaction_id
1213: AND rt1.inspection_status_code <> 'NOT INSPECTED'
1214: AND rt1.routing_header_id = 2)
1215: OR rt.routing_header_id <> 2
1285: -- oet.ORDER_CATEGORY_CODE ,
1286: oec.customer_id,
1287: oec.name customer_name,
1288: oec.customer_number
1289: FROM rcv_transactions_v rtv,
1290: oe_order_headers_all oeh,
1291: OE_TRANSACTION_TYPES_TL OTT_TL,
1292: OE_TRANSACTION_TYPES_ALL OTT_ALL,
1293: oe_order_lines_all oel,