[Home] [Help]
4677: BEGIN
4678: /* bug 14305407 modified the query to improve the performance */
4679: SELECT distinct(hl.location_code)
4680: INTO x_location_code
4681: FROM po_distributions_all pda,po_lines_all pol, po_line_locations_all pll, hr_locations hl
4682: WHERE pda.po_header_id = pll.po_header_id
4683: AND pda.line_location_id = pll.line_location_id
4684: AND pda.po_line_id = pll.po_line_id
4685: AND pda.po_header_id = pol.po_header_id
6700:
6701: IF p_document_type = 'PO' THEN
6702: SELECT COUNT(COUNT(*)) -- get the no of project/tasks combinations
6703: INTO l_project_tasks_count
6704: FROM po_distributions_all pod, po_lines_all pol
6705: WHERE pod.po_header_id = p_po_header_id
6706: AND pod.po_line_id = NVL(p_po_line_id, pod.po_line_id)
6707: AND pod.project_id IS NOT NULL
6708: AND pod.po_header_id = pol.po_header_id
6715: GROUP BY pod.project_id, pod.task_id;
6716:
6717: SELECT COUNT(*)
6718: INTO l_distributions_count
6719: FROM po_distributions_all pod, po_lines_all pol
6720: WHERE pod.po_header_id = p_po_header_id
6721: AND pod.po_line_id = NVL(p_po_line_id, pod.po_line_id)
6722: AND pod.po_header_id = pol.po_header_id
6723: AND pod.po_line_id = pol.po_line_id
6729:
6730: ELSIF p_document_type = 'ASN' THEN
6731: SELECT COUNT(COUNT(*))
6732: INTO l_project_tasks_count
6733: FROM po_distributions_all pod, rcv_shipment_lines rsl
6734: WHERE pod.po_header_id = rsl.po_header_id
6735: AND rsl.shipment_header_id = p_shipment_header_id
6736: AND (p_item_id IS NULL OR rsl.item_id = p_item_id)
6737: AND ( (p_item_rev IS NULL OR rsl.item_revision IS NULL) OR
6742: GROUP BY project_id, task_id;
6743:
6744: SELECT COUNT(COUNT(*))
6745: INTO l_distributions_count
6746: FROM po_distributions_all pod, rcv_shipment_lines rsl
6747: WHERE pod.po_header_id = rsl.po_header_id
6748: AND rsl.po_line_id = pod.po_line_id(+)
6749: AND rsl.po_line_location_id = pod.line_location_id(+)
6750: AND rsl.shipment_header_id = p_shipment_header_id
6847: ELSIF p_document_type = 'RECEIPT' THEN
6848: IF p_po_header_id IS NOT NULL THEN
6849: SELECT COUNT(COUNT(*)) -- get the no of project/tasks combinations
6850: INTO l_project_tasks_count
6851: FROM po_distributions_all pod, po_lines_all pol
6852: WHERE pod.po_header_id = p_po_header_id
6853: AND pod.project_id IS NOT NULL
6854: AND pod.po_header_id = pol.po_header_id
6855: AND pod.po_line_id = pol.po_line_id
6861: GROUP BY pod.project_id, pod.task_id;
6862:
6863: SELECT COUNT(*)
6864: INTO l_distributions_count
6865: FROM po_distributions_all pod, po_lines_all pol
6866: WHERE pod.po_header_id = p_po_header_id
6867: AND pod.po_header_id = pol.po_header_id
6868: AND pod.po_line_id = pol.po_line_id
6869: AND (p_item_id IS NULL OR pol.item_id = p_item_id)
7256: IF p_document_type = 'PO' THEN
7257: BEGIN
7258: SELECT pod.task_id
7259: INTO l_task_id
7260: FROM po_distributions_all pod, po_lines_all pol
7261: WHERE pod.po_header_id = p_po_header_id
7262: AND pod.po_line_id = NVL(p_po_line_id, pod.po_line_id)
7263: AND pod.project_id = p_project_id
7264: AND pod.po_header_id = pol.po_header_id
7282: ELSIF p_document_type = 'ASN' THEN
7283: BEGIN
7284: SELECT pod.task_id
7285: INTO l_task_id
7286: FROM po_distributions_all pod, rcv_shipment_lines rsl
7287: WHERE pod.po_header_id = rsl.po_header_id
7288: AND rsl.po_line_id = pod.po_line_id(+)
7289: AND rsl.po_line_location_id = pod.line_location_id(+)
7290: AND rsl.shipment_header_id = p_shipment_header_id
7342: IF p_po_header_id IS NOT NULL THEN
7343: BEGIN
7344: SELECT pod.task_id
7345: INTO l_task_id
7346: FROM po_distributions_all pod, po_lines_all pol
7347: WHERE pod.po_header_id = p_po_header_id
7348: AND pod.project_id = p_project_id
7349: AND pod.po_header_id = pol.po_header_id
7350: AND pod.po_line_id = pol.po_line_id