[Home] [Help]
1799: NULL AS secondary_uom_code,
1800: MIN(pod.project_id) AS project_id,
1801: MIN(pod.task_id) AS task_id,
1802: NULL AS lpn_id
1803: FROM rcv_shipment_headers rsh, rcv_shipment_lines rsl, po_lines_all pol, po_line_types plt,
1804: po_line_locations_all poll, po_distributions_all pod, mtl_units_of_measure muom
1805: WHERE rsh.shipment_num IS NOT NULL
1806: AND rsh.receipt_source_code = 'VENDOR'
1807: AND rsh.asn_type in ('ASN','ASBN')
1855: MIN(prd.project_id) AS project_id,
1856: MIN(prd.task_id) AS task_id,
1857: NULL AS lpn_id
1858: FROM po_requisition_headers_all prh, po_requisition_lines_all prl,
1859: rcv_shipment_lines rsl, rcv_shipment_headers rsh, po_req_distributions_all prd,
1860: mtl_units_of_measure muom
1861: WHERE prh.requisition_header_id = prl.requisition_header_id
1862: AND prd.requisition_line_id = prl.requisition_line_id
1863: AND prh.authorization_status = 'APPROVED'
1907: AND prl.source_type_code = 'INVENTORY'
1908: AND prl.destination_organization_id = l_organization_id
1909: AND prl.item_id = l_inventory_item_id
1910: AND NOT EXISTS (SELECT 'Ship Confirmed'
1911: FROM rcv_shipment_lines rsl
1912: WHERE rsl.requisition_line_id = prl.requisition_line_id
1913: AND rsl.routing_header_id > 0
1914: AND rsl.shipment_line_status_code <> 'CANCELLED'
1915: AND rsl.to_organization_id = l_organization_id
1941: NULL AS secondary_uom_code,
1942: NULL AS project_id,
1943: NULL AS task_id,
1944: NULL AS lpn_id
1945: FROM rcv_shipment_headers rsh, rcv_shipment_lines rsl, mtl_units_of_measure muom
1946: WHERE rsh.shipment_num IS NOT NULL
1947: AND rsh.shipment_header_id = rsl.shipment_header_id
1948: AND rsh.receipt_source_code = 'INVENTORY'
1949: AND EXISTS (SELECT 'Available Supply'
2208:
2209: -- Cursor to lock the ASN supply line record.
2210: CURSOR lock_asn_record(p_uom_code VARCHAR2, p_project_id NUMBER, p_task_id NUMBER) IS
2211: SELECT muom.uom_code, pod.project_id, pod.task_id
2212: FROM rcv_shipment_lines rsl, po_line_locations_all poll, mtl_units_of_measure muom,
2213: (SELECT po_header_id, po_line_id, line_location_id,
2214: MIN(project_id) AS project_id, MIN(task_id) AS task_id
2215: FROM po_distributions_all
2216: WHERE po_header_id = l_supply_header_id
2239: -- For UOM integrity, if we want to match the UOM from the supply to the reservation,
2240: -- just use the UOM at the requisition line level.
2241: CURSOR lock_intreq_record(p_uom_code VARCHAR2, p_project_id NUMBER, p_task_id NUMBER) IS
2242: SELECT muom_prl.uom_code, prd.project_id, prd.task_id
2243: FROM po_requisition_lines_all prl, rcv_shipment_lines rsl, mtl_units_of_measure muom_prl,
2244: (SELECT requisition_line_id, MIN(project_id) AS project_id, MIN(task_id) AS task_id
2245: FROM po_req_distributions_all
2246: WHERE requisition_line_id = l_supply_line_id
2247: GROUP BY requisition_line_id
2261:
2262: -- Cursor to lock the In Transit Shipment supply line record.
2263: CURSOR lock_intship_record(p_uom_code VARCHAR2, p_project_id NUMBER, p_task_id NUMBER) IS
2264: SELECT muom.uom_code, NULL AS project_id, NULL AS task_id
2265: FROM rcv_shipment_lines rsl, mtl_units_of_measure muom
2266: WHERE rsl.shipment_header_id = l_supply_header_id
2267: AND rsl.shipment_line_id = l_supply_line_id
2268: AND rsl.unit_of_measure = muom.unit_of_measure
2269: AND (p_uom_code IS NULL OR muom.uom_code = p_uom_code)
9811: NVL(wts.planned_arrival_date,
9812: NVL(NVL(rsh.expected_receipt_date, rsh.shipped_date),
9813: NVL(poll.promised_date, poll.need_by_date))) AS expected_receipt_date
9814: FROM po_line_locations_all poll, wsh_delivery_details wdd,
9815: rcv_shipment_headers rsh, rcv_shipment_lines rsl,
9816: wsh_delivery_assignments_v wda, wsh_new_deliveries wnd, wsh_delivery_legs wdl,
9817: wsh_trip_stops wts, wsh_trips wt, wsh_carrier_services wcs_wnd,
9818: wsh_carrier_services wcs_wdd, wsh_carriers wc_poll, wsh_carriers wc_rsh
9819: WHERE rsl.po_header_id = p_source_header_id
9875: wcs_prl.carrier_id))))) AS carrier_id,
9876: NVL(wts.planned_arrival_date,
9877: NVL(NVL(rsh.expected_receipt_date, rsh.shipped_date),
9878: prl.need_by_date)) AS expected_receipt_date
9879: FROM po_requisition_lines_all prl, rcv_shipment_lines rsl, rcv_shipment_headers rsh,
9880: oe_order_lines_all ool, wsh_delivery_details wdd,
9881: wsh_delivery_assignments_v wda, wsh_new_deliveries wnd, wsh_delivery_legs wdl,
9882: wsh_trip_stops wts, wsh_trips wt, wsh_carrier_services wcs_wnd,
9883: wsh_carrier_services wcs_wdd, wsh_carrier_services wcs_ool,
9926: wc_rsh.carrier_id as carrier_id,
9927: NVL(rsh.expected_receipt_date,
9928: NVL(rsh.shipped_date + NVL(mism.intransit_time, 0),
9929: rsh.shipped_date)) AS expected_receipt_date
9930: FROM rcv_shipment_lines rsl, rcv_shipment_headers rsh,
9931: wsh_carriers wc_rsh, mtl_interorg_ship_methods mism
9932: WHERE rsl.shipment_header_id = p_source_header_id
9933: AND rsl.shipment_line_id = p_source_line_id
9934: AND rsl.shipment_header_id = rsh.shipment_header_id