[Home] [Help]
1824: NULL AS secondary_uom_code,
1825: MIN(pod.project_id) AS project_id,
1826: MIN(pod.task_id) AS task_id,
1827: NULL AS lpn_id
1828: FROM rcv_shipment_headers rsh, rcv_shipment_lines rsl, po_lines_all pol, po_line_types plt,
1829: po_line_locations_all poll, po_distributions_all pod, mtl_units_of_measure muom
1830: WHERE rsh.shipment_num IS NOT NULL
1831: AND rsh.receipt_source_code = 'VENDOR'
1832: AND rsh.asn_type in ('ASN','ASBN')
1880: MIN(prd.project_id) AS project_id,
1881: MIN(prd.task_id) AS task_id,
1882: NULL AS lpn_id
1883: FROM po_requisition_headers_all prh, po_requisition_lines_all prl,
1884: rcv_shipment_lines rsl, rcv_shipment_headers rsh, po_req_distributions_all prd,
1885: mtl_units_of_measure muom
1886: WHERE prh.requisition_header_id = prl.requisition_header_id
1887: AND prd.requisition_line_id = prl.requisition_line_id
1888: AND prh.authorization_status = 'APPROVED'
1932: AND prl.source_type_code = 'INVENTORY'
1933: AND prl.destination_organization_id = l_organization_id
1934: AND prl.item_id = l_inventory_item_id
1935: AND NOT EXISTS (SELECT 'Ship Confirmed'
1936: FROM rcv_shipment_lines rsl
1937: WHERE rsl.requisition_line_id = prl.requisition_line_id
1938: AND rsl.routing_header_id > 0
1939: AND rsl.shipment_line_status_code <> 'CANCELLED'
1940: AND rsl.to_organization_id = l_organization_id
1966: NULL AS secondary_uom_code,
1967: NULL AS project_id,
1968: NULL AS task_id,
1969: NULL AS lpn_id
1970: FROM rcv_shipment_headers rsh, rcv_shipment_lines rsl, mtl_units_of_measure muom
1971: WHERE rsh.shipment_num IS NOT NULL
1972: AND rsh.shipment_header_id = rsl.shipment_header_id
1973: AND rsh.receipt_source_code = 'INVENTORY'
1974: AND EXISTS (SELECT 'Available Supply'
2233:
2234: -- Cursor to lock the ASN supply line record.
2235: CURSOR lock_asn_record(p_uom_code VARCHAR2, p_project_id NUMBER, p_task_id NUMBER) IS
2236: SELECT muom.uom_code, pod.project_id, pod.task_id
2237: FROM rcv_shipment_lines rsl, po_line_locations_all poll, mtl_units_of_measure muom,
2238: (SELECT po_header_id, po_line_id, line_location_id,
2239: MIN(project_id) AS project_id, MIN(task_id) AS task_id
2240: FROM po_distributions_all
2241: WHERE po_header_id = l_supply_header_id
2264: -- For UOM integrity, if we want to match the UOM from the supply to the reservation,
2265: -- just use the UOM at the requisition line level.
2266: CURSOR lock_intreq_record(p_uom_code VARCHAR2, p_project_id NUMBER, p_task_id NUMBER) IS
2267: SELECT muom_prl.uom_code, prd.project_id, prd.task_id
2268: FROM po_requisition_lines_all prl, rcv_shipment_lines rsl, mtl_units_of_measure muom_prl,
2269: (SELECT requisition_line_id, MIN(project_id) AS project_id, MIN(task_id) AS task_id
2270: FROM po_req_distributions_all
2271: WHERE requisition_line_id = l_supply_line_id
2272: GROUP BY requisition_line_id
2286:
2287: -- Cursor to lock the In Transit Shipment supply line record.
2288: CURSOR lock_intship_record(p_uom_code VARCHAR2, p_project_id NUMBER, p_task_id NUMBER) IS
2289: SELECT muom.uom_code, NULL AS project_id, NULL AS task_id
2290: FROM rcv_shipment_lines rsl, mtl_units_of_measure muom
2291: WHERE rsl.shipment_header_id = l_supply_header_id
2292: AND rsl.shipment_line_id = l_supply_line_id
2293: AND rsl.unit_of_measure = muom.unit_of_measure
2294: AND (p_uom_code IS NULL OR muom.uom_code = p_uom_code)
10006: NVL(wts.planned_arrival_date,
10007: NVL(NVL(rsh.expected_receipt_date, rsh.shipped_date),
10008: NVL(poll.promised_date, poll.need_by_date))) AS expected_receipt_date
10009: FROM po_line_locations_all poll, wsh_delivery_details wdd,
10010: rcv_shipment_headers rsh, rcv_shipment_lines rsl,
10011: wsh_delivery_assignments_v wda, wsh_new_deliveries wnd, wsh_delivery_legs wdl,
10012: wsh_trip_stops wts, wsh_trips wt, wsh_carrier_services wcs_wnd,
10013: wsh_carrier_services wcs_wdd, wsh_carriers wc_poll, wsh_carriers wc_rsh
10014: WHERE rsl.po_header_id = p_source_header_id
10070: wcs_prl.carrier_id))))) AS carrier_id,
10071: NVL(wts.planned_arrival_date,
10072: NVL(NVL(rsh.expected_receipt_date, rsh.shipped_date),
10073: prl.need_by_date)) AS expected_receipt_date
10074: FROM po_requisition_lines_all prl, rcv_shipment_lines rsl, rcv_shipment_headers rsh,
10075: oe_order_lines_all ool, wsh_delivery_details wdd,
10076: wsh_delivery_assignments_v wda, wsh_new_deliveries wnd, wsh_delivery_legs wdl,
10077: wsh_trip_stops wts, wsh_trips wt, wsh_carrier_services wcs_wnd,
10078: wsh_carrier_services wcs_wdd, wsh_carrier_services wcs_ool,
10121: wc_rsh.carrier_id as carrier_id,
10122: NVL(rsh.expected_receipt_date,
10123: NVL(rsh.shipped_date + NVL(mism.intransit_time, 0),
10124: rsh.shipped_date)) AS expected_receipt_date
10125: FROM rcv_shipment_lines rsl, rcv_shipment_headers rsh,
10126: wsh_carriers wc_rsh, mtl_interorg_ship_methods mism
10127: WHERE rsl.shipment_header_id = p_source_header_id
10128: AND rsl.shipment_line_id = p_source_line_id
10129: AND rsl.shipment_header_id = rsh.shipment_header_id