DBA Data[Home] [Help]

APPS.INV_RCV_COMMON_APIS dependencies on PO_LINE_LOCATIONS

Line 1975: * Routing ID is defined at PO line-location level (po_line_locations_all)

1971:
1972: /*************************************************
1973: * Name: get_po_routing_id
1974: * This API returns routing id for a given PO header ID
1975: * Routing ID is defined at PO line-location level (po_line_locations_all)
1976: * We use the following rule to set headers routing ID
1977: * If there is one line detail needs inspection the entire PO needs inspection
1978: * elsif there is one line detail needs direct receiving the entire PO direct
1979: * else (all line detail are standard) the entire PO is standard

Line 2016: FROM po_line_locations poll, po_lines pol

2012: -- searching mechanism will not
2013: -- go to item/org level.
2014: SELECT poll.receiving_routing_id
2015: -- p_po_release_id is null and p_po_line_id is null
2016: FROM po_line_locations poll, po_lines pol
2017: WHERE pol.po_header_id = p_po_header_id
2018: AND poll.po_line_id = pol.po_line_id
2019: AND p_po_release_id is NULL
2020: AND p_po_line_id is null

Line 2031: FROM po_line_locations poll, po_lines pol

2027: AND poll.shipment_type IN('STANDARD', 'BLANKET', 'SCHEDULED')
2028: UNION ALL
2029: SELECT poll.receiving_routing_id
2030: -- p_po_release_id is null and p_po_line_id is not null
2031: FROM po_line_locations poll, po_lines pol
2032: WHERE poll.po_header_id = p_po_header_id
2033: AND poll.po_line_id = pol.po_line_id
2034: AND p_po_release_id is NULL
2035: AND (p_po_line_id is not null AND poll.po_line_id = p_po_line_id)

Line 2046: FROM po_line_locations poll, po_lines pol

2042: AND poll.shipment_type IN('STANDARD', 'BLANKET', 'SCHEDULED')
2043: UNION ALL
2044: SELECT poll.receiving_routing_id
2045: -- p_po_release_id is not null
2046: FROM po_line_locations poll, po_lines pol
2047: WHERE poll.po_header_id = p_po_header_id
2048: AND poll.po_line_id = pol.po_line_id
2049: AND (p_po_release_id is NOT NULL AND poll.po_release_id = p_po_release_id)
2050: AND (p_po_line_id is null or poll.po_line_id = p_po_line_id)

Line 2060: FROM po_line_locations poll, po_lines pol

2056: /*Fix for bug #4755862*/
2057: AND poll.shipment_type IN('STANDARD', 'BLANKET', 'SCHEDULED');
2058: /*
2059: SELECT poll.receiving_routing_id
2060: FROM po_line_locations poll, po_lines pol
2061: WHERE poll.po_header_id = p_po_header_id
2062: AND NVL(poll.po_release_id, -1) = NVL(p_po_release_id, NVL(poll.po_release_id, -1))
2063: AND NVL(poll.po_line_id, -1) = NVL(p_po_line_id, NVL(poll.po_line_id, -1))
2064: --AND pol.item_id = p_item_id

Line 2081: FROM PO_DISTRIBUTIONS POD, PO_LINES POL, PO_LINE_LOCATIONS POLL

2077:
2078: CURSOR pod_dest_context_cur IS
2079: SELECT DISTINCT NVL(POD.DESTINATION_TYPE_CODE,POD.DESTINATION_CONTEXT)
2080: -- p_po_release_id is null and p_po_line_id is null
2081: FROM PO_DISTRIBUTIONS POD, PO_LINES POL, PO_LINE_LOCATIONS POLL
2082: WHERE POL.PO_HEADER_ID = p_po_header_id
2083: AND POLL.PO_LINE_ID = POL.PO_LINE_ID
2084: AND POLL.SHIP_TO_ORGANIZATION_ID = NVL(p_organization_id, POLL.SHIP_TO_ORGANIZATION_ID) -- Bug 8242448
2085: AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID

Line 2099: FROM PO_DISTRIBUTIONS POD, PO_LINES POL, PO_LINE_LOCATIONS POLL

2095: AND POLL.SHIPMENT_TYPE IN ( 'STANDARD','BLANKET','SCHEDULED' )
2096: UNION ALL
2097: SELECT DISTINCT NVL(POD.DESTINATION_TYPE_CODE,POD.DESTINATION_CONTEXT)
2098: -- p_po_release_id is null and p_po_line_id is not null
2099: FROM PO_DISTRIBUTIONS POD, PO_LINES POL, PO_LINE_LOCATIONS POLL
2100: WHERE POLL.PO_HEADER_ID = p_po_header_id
2101: AND POLL.PO_LINE_ID = POL.PO_LINE_ID
2102: AND POLL.SHIP_TO_ORGANIZATION_ID = NVL(p_organization_id, POLL.SHIP_TO_ORGANIZATION_ID) -- Bug 8242448
2103: AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID

Line 2117: FROM PO_DISTRIBUTIONS POD, PO_LINES POL, PO_LINE_LOCATIONS POLL

2113: AND POLL.SHIPMENT_TYPE IN ( 'STANDARD','BLANKET','SCHEDULED' )
2114: UNION ALL
2115: SELECT DISTINCT NVL(POD.DESTINATION_TYPE_CODE,POD.DESTINATION_CONTEXT)
2116: -- p_po_release_id is not NULL
2117: FROM PO_DISTRIBUTIONS POD, PO_LINES POL, PO_LINE_LOCATIONS POLL
2118: WHERE POLL.PO_HEADER_ID = p_po_header_id
2119: AND POLL.PO_LINE_ID = POL.PO_LINE_ID
2120: AND POLL.SHIP_TO_ORGANIZATION_ID = NVL(p_organization_id, POLL.SHIP_TO_ORGANIZATION_ID) -- Bug 8242448
2121: AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID

Line 2134: -- distributions against po line locations whose closed_code not in status 'CLOSED','

2130:
2131: -- Bug 8242448 - Code changes start
2132:
2133: -- As part of bug # 7281141, the cursor 'pod_dest_context_cur' is modified to fetch the
2134: -- distributions against po line locations whose closed_code not in status 'CLOSED','
2135: -- CLOSED FOR RECEIVING', 'FINALLY CLOSED', for a wms org. But for a non-wms org we
2136: -- need to fetch the destination type code during deliver transaction also, in order to
2137: -- prompt the user to enter the deliver to location instead of subinventory and locator
2138: -- in case of expense destination. Hence added the following cursor which will retrieve

Line 2139: -- distributions against po line locations whose closed_code <> 'FINALLY CLOSED'.

2135: -- CLOSED FOR RECEIVING', 'FINALLY CLOSED', for a wms org. But for a non-wms org we
2136: -- need to fetch the destination type code during deliver transaction also, in order to
2137: -- prompt the user to enter the deliver to location instead of subinventory and locator
2138: -- in case of expense destination. Hence added the following cursor which will retrieve
2139: -- distributions against po line locations whose closed_code <> 'FINALLY CLOSED'.
2140:
2141: CURSOR pod_dest_context_inv_cur IS
2142: SELECT DISTINCT NVL(POD.DESTINATION_TYPE_CODE,POD.DESTINATION_CONTEXT)
2143: FROM PO_DISTRIBUTIONS POD, PO_LINES POL, PO_LINE_LOCATIONS POLL

Line 2143: FROM PO_DISTRIBUTIONS POD, PO_LINES POL, PO_LINE_LOCATIONS POLL

2139: -- distributions against po line locations whose closed_code <> 'FINALLY CLOSED'.
2140:
2141: CURSOR pod_dest_context_inv_cur IS
2142: SELECT DISTINCT NVL(POD.DESTINATION_TYPE_CODE,POD.DESTINATION_CONTEXT)
2143: FROM PO_DISTRIBUTIONS POD, PO_LINES POL, PO_LINE_LOCATIONS POLL
2144: WHERE POL.PO_HEADER_ID = p_po_header_id
2145: AND POLL.PO_LINE_ID = POL.PO_LINE_ID
2146: AND POLL.SHIP_TO_ORGANIZATION_ID = NVL(p_organization_id, POLL.SHIP_TO_ORGANIZATION_ID)
2147: AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID

Line 2158: FROM PO_DISTRIBUTIONS POD, PO_LINES POL, PO_LINE_LOCATIONS POLL

2154: AND NVL(POLL.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED'
2155: AND POLL.SHIPMENT_TYPE IN ( 'STANDARD','BLANKET','SCHEDULED' )
2156: UNION ALL
2157: SELECT DISTINCT NVL(POD.DESTINATION_TYPE_CODE,POD.DESTINATION_CONTEXT)
2158: FROM PO_DISTRIBUTIONS POD, PO_LINES POL, PO_LINE_LOCATIONS POLL
2159: WHERE POLL.PO_HEADER_ID = p_po_header_id
2160: AND POLL.PO_LINE_ID = POL.PO_LINE_ID
2161: AND POLL.SHIP_TO_ORGANIZATION_ID = NVL(p_organization_id, POLL.SHIP_TO_ORGANIZATION_ID)
2162: AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID

Line 2173: FROM PO_DISTRIBUTIONS POD, PO_LINES POL, PO_LINE_LOCATIONS POLL

2169: AND NVL(POLL.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED'
2170: AND POLL.SHIPMENT_TYPE IN ( 'STANDARD','BLANKET','SCHEDULED' )
2171: UNION ALL
2172: SELECT DISTINCT NVL(POD.DESTINATION_TYPE_CODE,POD.DESTINATION_CONTEXT)
2173: FROM PO_DISTRIBUTIONS POD, PO_LINES POL, PO_LINE_LOCATIONS POLL
2174: WHERE POLL.PO_HEADER_ID = p_po_header_id
2175: AND POLL.PO_LINE_ID = POL.PO_LINE_ID
2176: AND POLL.SHIP_TO_ORGANIZATION_ID = NVL(p_organization_id, POLL.SHIP_TO_ORGANIZATION_ID)
2177: AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID

Line 2191: FROM po_distributions pod, po_lines pol, po_line_locations poll

2187: -- Bug 8242448 - Code changes end
2188:
2189: /*
2190: SELECT DISTINCT Nvl(pod.destination_type_code,pod.destination_context)
2191: FROM po_distributions pod, po_lines pol, po_line_locations poll
2192: WHERE pod.po_header_id = p_po_header_id
2193: AND NVL(poll.po_release_id, -1) = NVL(p_po_release_id, NVL(poll.po_release_id, -1))
2194: AND NVL(poll.po_line_id, -1) = NVL(p_po_line_id, NVL(poll.po_line_id, -1))
2195: --AND pol.item_id = p_item_id

Line 4010: FROM po_line_locations_all poll, po_lines_all po

4006: AND pol.unit_meas_lookup_code IS NOT NULL
4007: AND pol.line_num = p_line_no
4008: AND pol.unit_meas_lookup_code = mum.unit_of_measure
4009: AND pol.po_line_id IN (SELECT poll.po_line_id
4010: FROM po_line_locations_all poll, po_lines_all po
4011: WHERE poll.po_header_id = po.po_header_id
4012: AND Nvl(poll.approved_flag,'N') = 'Y'
4013: AND Nvl(poll.cancel_flag,'N') = 'N'
4014: AND Nvl(poll.closed_code,'OPEN') NOT IN ('FINALLY CLOSED','CLOSED FOR RECEIVING')

Line 4047: FROM po_line_locations_all poll, po_lines_all po

4043: AND pol.unit_meas_lookup_code IS NOT NULL
4044: AND pol.unit_meas_lookup_code = mum.unit_of_measure
4045: AND pol.item_description = p_item_desc
4046: AND pol.po_line_id IN (SELECT poll.po_line_id
4047: FROM po_line_locations_all poll, po_lines_all po
4048: WHERE poll.po_header_id = po.po_header_id
4049: AND Nvl(poll.approved_flag,'N') = 'Y'
4050: AND Nvl(poll.cancel_flag,'N') = 'N'
4051: AND Nvl(poll.closed_code,'OPEN') NOT IN ('FINALLY CLOSED','CLOSED FOR RECEIVING')

Line 4085: FROM po_line_locations_all poll, po_lines_all po

4081: WHERE pol.po_header_id = p_po_header_id
4082: AND pol.unit_meas_lookup_code IS NOT NULL
4083: AND pol.item_id = p_item_id
4084: AND pol.po_line_id IN (SELECT poll.po_line_id
4085: FROM po_line_locations_all poll, po_lines_all po
4086: WHERE poll.po_header_id = po.po_header_id
4087: AND Nvl(poll.approved_flag,'N') = 'Y'
4088: AND Nvl(poll.cancel_flag,'N') = 'N'
4089: AND Nvl(poll.closed_code,'OPEN') NOT IN ('FINALLY CLOSED','CLOSED FOR RECEIVING')

Line 4114: FROM po_line_locations_all poll, po_lines_all po

4110: AND pol.unit_meas_lookup_code IS NOT NULL
4111: AND pol.item_id = p_item_id
4112: AND mum.UNIT_OF_MEASURE(+) = pol.UNIT_MEAS_LOOKUP_CODE
4113: AND pol.po_line_id IN (SELECT poll.po_line_id
4114: FROM po_line_locations_all poll, po_lines_all po
4115: WHERE poll.po_header_id = po.po_header_id
4116: AND Nvl(poll.approved_flag,'N') = 'Y'
4117: AND Nvl(poll.cancel_flag,'N') = 'N'
4118: AND Nvl(poll.closed_code,'OPEN') NOT IN ('FINALLY CLOSED','CLOSED FOR RECEIVING')

Line 4681: FROM po_distributions_all pda,po_lines_all pol, po_line_locations_all pll, hr_locations hl

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

Line 4714: FROM hr_locations hl, po_line_locations poll,po_lines pol

4710: -- bug 864331
4711: /* bug 14305407 modified the query to improve the performance */
4712: SELECT distinct( hl.location_code )
4713: INTO x_location_code
4714: FROM hr_locations hl, po_line_locations poll,po_lines pol
4715: WHERE hl.location_id = poll.ship_to_location_id
4716: AND poll.po_header_id = pol.po_header_id
4717: AND poll.po_line_id = pol.po_line_id
4718: AND poll.po_header_id = p_po_header_id

Line 4840: FROM po_line_locations poll,po_lines pol

4836: END IF;
4837:
4838: SELECT DISTINCT (pol.item_revision)
4839: INTO x_revision_code
4840: FROM po_line_locations poll,po_lines pol
4841: WHERE pol.po_header_id = p_po_header_id
4842: AND NVL(poll.po_line_id,-1) = NVL(p_po_line_id, NVL(poll.po_line_id, -1))
4843: AND NVL(pol.item_id,-1) = NVL(p_item_id,NVL(pol.item_id, -1))
4844: AND poll.ship_to_organization_id= p_organization_id