[Home] [Help]
361:
362: IF p_req_header_id IS NOT NULL THEN
363: SELECT 'N'
364: INTO l_lot_ser_flag
365: FROM po_requisition_headers prh, po_requisition_lines prl, rcv_shipment_lines rsl, mtl_system_items msi1, mtl_system_items msi2
366: WHERE prh.requisition_header_id = p_req_header_id
367: AND prl.requisition_header_id = prh.requisition_header_id
368: AND rsl.requisition_line_id = prl.requisition_line_id
369: AND rsl.item_id = msi1.inventory_item_id
2488: IF l_is_expense = 'N' THEN
2489: BEGIN
2490: SELECT 'Y'
2491: INTO l_is_expense
2492: FROM po_requisition_lines prl
2493: , rcv_shipment_lines rsl
2494: WHERE prl.requisition_line_id = rsl.requisition_line_id
2495: AND prl.destination_type_code = 'EXPENSE'
2496: AND rsl.shipment_header_id = p_shipment_header_id
5740: WHERE item_id = p_item_id
5741: AND to_organization_id = p_organization_id
5742: AND EXISTS(
5743: SELECT 1
5744: FROM po_requisition_lines prl, rcv_transactions rt, rcv_supply rs
5745: WHERE prl.requisition_header_id = p_requiition_header_id
5746: AND rsl.requisition_line_id = prl.requisition_line_id
5747: AND prl.item_id = p_item_id
5748: AND prl.source_type_code = 'INVENTORY'
5808: CONNECT BY parent_lpn_id = PRIOR lpn_id)
5809: AND wlc.parent_lpn_id = wln.lpn_id
5810: AND EXISTS(
5811: SELECT 1
5812: FROM po_requisition_lines_all prl, mtl_supply ms
5813: WHERE prl.requisition_header_id = p_requiition_header_id
5814: AND prl.requisition_header_id = ms.req_header_id
5815: AND prl.requisition_line_id = ms.req_line_id
5816: AND ms.supply_type_code = 'SHIPMENT'
5832: START WITH lpn_id = p_lpn_id
5833: CONNECT BY parent_lpn_id = PRIOR lpn_id)
5834: AND EXISTS(
5835: SELECT 1
5836: FROM po_requisition_lines_all prl, mtl_supply ms
5837: WHERE prl.requisition_header_id = p_requiition_header_id
5838: AND prl.requisition_header_id = ms.req_header_id
5839: AND prl.requisition_line_id = ms.req_line_id
5840: AND ms.supply_type_code = 'SHIPMENT'
5873: WHERE item_id = p_item_id
5874: AND to_organization_id = p_organization_id
5875: AND EXISTS(
5876: SELECT 1
5877: FROM po_requisition_lines prl
5878: WHERE prl.requisition_header_id = p_requiition_header_id
5879: AND rsl.requisition_line_id = prl.requisition_line_id
5880: AND prl.item_id = p_item_id);
5881: EXCEPTION
5903: WHERE item_id = p_item_id
5904: AND to_organization_id = p_organization_id
5905: AND EXISTS(
5906: SELECT 1
5907: FROM po_requisition_lines prl, rcv_transactions rt, rcv_supply rs
5908: WHERE prl.requisition_header_id = p_requiition_header_id
5909: AND rsl.requisition_line_id = prl.requisition_line_id
5910: AND prl.item_id = p_item_id
5911: AND prl.source_type_code = 'INVENTORY'
6224: GROUP BY project_id, task_id;
6225: ELSIF p_document_type = 'REQ' THEN
6226: SELECT COUNT(COUNT(*))
6227: INTO l_project_tasks_count
6228: FROM po_req_distributions_all pod, po_requisition_lines_all pol
6229: WHERE pol.requisition_header_id = p_req_header_id
6230: AND pod.requisition_line_id = pol.requisition_line_id
6231: AND pod.project_id IS NOT NULL
6232: AND (p_item_id IS NULL OR pol.item_id = p_item_id)
6237: GROUP BY project_id, task_id;
6238:
6239: SELECT COUNT(*)
6240: INTO l_distributions_count
6241: FROM po_req_distributions_all pod, po_requisition_lines_all pol
6242: WHERE pol.requisition_header_id = p_req_header_id
6243: AND pod.requisition_line_id = pol.requisition_line_id
6244: AND (p_item_id IS NULL OR pol.item_id = p_item_id)
6245: AND ( (p_item_rev IS NULL OR pol.item_revision IS NULL) OR
6257: END IF;
6258:
6259: SELECT COUNT(COUNT(*))
6260: INTO l_project_tasks_count
6261: FROM po_req_distributions_all pod, po_requisition_lines_all pol
6262: WHERE pol.requisition_header_id = p_req_header_id
6263: AND pod.requisition_line_id = pol.requisition_line_id
6264: AND pod.project_id IS NOT NULL
6265: AND (p_item_id IS NULL OR pol.item_id = p_item_id)
6274:
6275:
6276: SELECT COUNT(*)
6277: INTO l_distributions_count
6278: FROM po_req_distributions_all pod, po_requisition_lines_all pol
6279: WHERE pol.requisition_header_id = p_req_header_id
6280: AND pod.requisition_line_id = pol.requisition_line_id
6281: AND (p_item_id IS NULL OR pol.item_id = p_item_id)
6282: AND ( (p_item_rev IS NULL OR pol.item_revision IS NULL) OR
6423: AND to_organization_id = p_to_org_id
6424: AND shipment_header_id = p_ship_head_id
6425: AND EXISTS(
6426: SELECT 1
6427: FROM po_requisition_lines prl
6428: WHERE prl.requisition_header_id = p_requisition_id
6429: AND rsl.requisition_line_id = prl.requisition_line_id
6430: AND prl.item_id = p_item_id);
6431:
6654: AND to_organization_id = p_to_org_id
6655: AND shipment_header_id = p_ship_head_id
6656: AND EXISTS(
6657: SELECT 1
6658: FROM po_requisition_lines prl
6659: WHERE prl.requisition_header_id = p_requisition_id
6660: AND rsl.requisition_line_id = prl.requisition_line_id
6661: AND prl.item_id = p_item_id);
6662:
6771: ELSIF p_document_type = 'REQ' THEN
6772: BEGIN
6773: SELECT pod.task_id
6774: INTO l_task_id
6775: FROM po_req_distributions_all pod, po_requisition_lines_all pol
6776: WHERE pol.requisition_header_id = p_req_header_id
6777: AND pod.requisition_line_id = pol.requisition_line_id
6778: AND pod.project_id = p_project_id
6779: AND(p_item_id IS NULL
7522: IF p_req_line_id IS NOT NULL THEN
7523:
7524: Select org_id
7525: into l_operating_unit_id
7526: from po_requisition_lines_all
7527: where requisition_line_id = p_req_line_id;
7528:
7529: END IF;
7530:
7580: END IF;
7581: BEGIN
7582: SELECT count(DISTINCT rsl.shipment_header_id)
7583: INTO x_open_shipments
7584: FROM rcv_shipment_lines rsl, po_requisition_lines prl
7585: WHERE to_organization_id = p_organization_id
7586: AND nvl(rsl.shipment_line_status_code, ' ') <> 'FULLY RECEIVED'
7587: AND prl.requisition_header_id = p_requisition_header_id
7588: AND rsl.requisition_line_id = prl.requisition_line_id;