[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
2630: IF l_is_expense = 'N' THEN
2631: BEGIN
2632: SELECT 'Y'
2633: INTO l_is_expense
2634: FROM po_requisition_lines prl
2635: , rcv_shipment_lines rsl
2636: WHERE prl.requisition_line_id = rsl.requisition_line_id
2637: AND prl.destination_type_code = 'EXPENSE'
2638: AND rsl.shipment_header_id = p_shipment_header_id
6272: WHERE item_id = p_item_id
6273: AND to_organization_id = p_organization_id
6274: AND EXISTS(
6275: SELECT 1
6276: FROM po_requisition_lines prl, rcv_transactions rt, rcv_supply rs
6277: WHERE prl.requisition_header_id = p_requiition_header_id
6278: AND rsl.requisition_line_id = prl.requisition_line_id
6279: AND prl.item_id = p_item_id
6280: AND prl.source_type_code = 'INVENTORY'
6340: CONNECT BY parent_lpn_id = PRIOR lpn_id)
6341: AND wlc.parent_lpn_id = wln.lpn_id
6342: AND EXISTS(
6343: SELECT 1
6344: FROM po_requisition_lines_all prl, mtl_supply ms
6345: WHERE prl.requisition_header_id = p_requiition_header_id
6346: AND prl.requisition_header_id = ms.req_header_id
6347: AND prl.requisition_line_id = ms.req_line_id
6348: AND ms.supply_type_code = 'SHIPMENT'
6364: START WITH lpn_id = p_lpn_id
6365: CONNECT BY parent_lpn_id = PRIOR lpn_id)
6366: AND EXISTS(
6367: SELECT 1
6368: FROM po_requisition_lines_all prl, mtl_supply ms
6369: WHERE prl.requisition_header_id = p_requiition_header_id
6370: AND prl.requisition_header_id = ms.req_header_id
6371: AND prl.requisition_line_id = ms.req_line_id
6372: AND ms.supply_type_code = 'SHIPMENT'
6405: WHERE item_id = p_item_id
6406: AND to_organization_id = p_organization_id
6407: AND EXISTS(
6408: SELECT 1
6409: FROM po_requisition_lines prl
6410: WHERE prl.requisition_header_id = p_requiition_header_id
6411: AND rsl.requisition_line_id = prl.requisition_line_id
6412: AND prl.item_id = p_item_id);
6413: EXCEPTION
6435: WHERE item_id = p_item_id
6436: AND to_organization_id = p_organization_id
6437: AND EXISTS(
6438: SELECT 1
6439: FROM po_requisition_lines prl, rcv_transactions rt, rcv_supply rs
6440: WHERE prl.requisition_header_id = p_requiition_header_id
6441: AND rsl.requisition_line_id = prl.requisition_line_id
6442: AND prl.item_id = p_item_id
6443: AND prl.source_type_code = 'INVENTORY'
6756: GROUP BY project_id, task_id;
6757: ELSIF p_document_type = 'REQ' THEN
6758: SELECT COUNT(COUNT(*))
6759: INTO l_project_tasks_count
6760: FROM po_req_distributions_all pod, po_requisition_lines_all pol
6761: WHERE pol.requisition_header_id = p_req_header_id
6762: AND pod.requisition_line_id = pol.requisition_line_id
6763: AND pod.project_id IS NOT NULL
6764: AND (p_item_id IS NULL OR pol.item_id = p_item_id)
6769: GROUP BY project_id, task_id;
6770:
6771: SELECT COUNT(*)
6772: INTO l_distributions_count
6773: FROM po_req_distributions_all pod, po_requisition_lines_all pol
6774: WHERE pol.requisition_header_id = p_req_header_id
6775: AND pod.requisition_line_id = pol.requisition_line_id
6776: AND (p_item_id IS NULL OR pol.item_id = p_item_id)
6777: AND ( (p_item_rev IS NULL OR pol.item_revision IS NULL) OR
6789: END IF;
6790:
6791: SELECT COUNT(COUNT(*))
6792: INTO l_project_tasks_count
6793: FROM po_req_distributions_all pod, po_requisition_lines_all pol
6794: WHERE pol.requisition_header_id = p_req_header_id
6795: AND pod.requisition_line_id = pol.requisition_line_id
6796: AND pod.project_id IS NOT NULL
6797: AND (p_item_id IS NULL OR pol.item_id = p_item_id)
6806:
6807:
6808: SELECT COUNT(*)
6809: INTO l_distributions_count
6810: FROM po_req_distributions_all pod, po_requisition_lines_all pol
6811: WHERE pol.requisition_header_id = p_req_header_id
6812: AND pod.requisition_line_id = pol.requisition_line_id
6813: AND (p_item_id IS NULL OR pol.item_id = p_item_id)
6814: AND ( (p_item_rev IS NULL OR pol.item_revision IS NULL) OR
6955: AND to_organization_id = p_to_org_id
6956: AND shipment_header_id = p_ship_head_id
6957: AND EXISTS(
6958: SELECT 1
6959: FROM po_requisition_lines prl
6960: WHERE prl.requisition_header_id = p_requisition_id
6961: AND rsl.requisition_line_id = prl.requisition_line_id
6962: AND prl.item_id = p_item_id);
6963:
7186: AND to_organization_id = p_to_org_id
7187: AND shipment_header_id = p_ship_head_id
7188: AND EXISTS(
7189: SELECT 1
7190: FROM po_requisition_lines prl
7191: WHERE prl.requisition_header_id = p_requisition_id
7192: AND rsl.requisition_line_id = prl.requisition_line_id
7193: AND prl.item_id = p_item_id);
7194:
7303: ELSIF p_document_type = 'REQ' THEN
7304: BEGIN
7305: SELECT pod.task_id
7306: INTO l_task_id
7307: FROM po_req_distributions_all pod, po_requisition_lines_all pol
7308: WHERE pol.requisition_header_id = p_req_header_id
7309: AND pod.requisition_line_id = pol.requisition_line_id
7310: AND pod.project_id = p_project_id
7311: AND(p_item_id IS NULL
8054: IF p_req_line_id IS NOT NULL THEN
8055:
8056: Select org_id
8057: into l_operating_unit_id
8058: from po_requisition_lines_all
8059: where requisition_line_id = p_req_line_id;
8060:
8061: END IF;
8062:
8112: END IF;
8113: BEGIN
8114: SELECT count(DISTINCT rsl.shipment_header_id)
8115: INTO x_open_shipments
8116: FROM rcv_shipment_lines rsl, po_requisition_lines prl
8117: WHERE to_organization_id = p_organization_id
8118: AND nvl(rsl.shipment_line_status_code, ' ') <> 'FULLY RECEIVED'
8119: AND prl.requisition_header_id = p_requisition_header_id
8120: AND rsl.requisition_line_id = prl.requisition_line_id;