[Home] [Help]
241: --| 11/23/2006 vchu Bug fix for 5678387: Modified the |
242: --| queries that select |
243: --| UNIT_MEAS_LOOKUP_CODE from |
244: --| PO_LINE_LOCATIONS_ALL to select the |
245: --| same column from PO_LINES_ALL, if the |
246: --| UOM is not stamped on the line location,|
247: --| which seems to be the case for BPA |
248: --| Release Shipments. |
249: --| Also removed unnecessary logic that |
1304: , pol.task_id
1305: FROM
1306: po_line_locations_all poll
1307: , po_headers_all poh
1308: , po_lines_all pol
1309: , mtl_units_of_measure muom
1310: , po_releases_all por
1311: WHERE poll.po_header_id = poh.po_header_id
1312: AND poll.po_line_id = pol.po_line_id
1615: -- Selects the Replenishment POs with the supplier matching the OEM
1616: -- Organization and the Ship To Organization matching the TP
1617: -- Organization of some SHIKYU enabled shipping network.
1618:
1619: -- Bug 5678387: Modified the query to get the uom from PO_LINES_ALL
1620: -- if the uom is not stamped on the PO_LINE_LOCATIONS_ALL record
1621:
1622: CURSOR c_rep IS
1623: SELECT DISTINCT plla.line_location_id,
1633: pla.line_num,
1634: plla.shipment_num
1635: FROM hr_organization_information hoi,
1636: po_line_locations_all plla,
1637: po_lines_all pla,
1638: po_headers_all pha,
1639: mtl_interorg_parameters mip,
1640: mtl_system_items_b msib
1641: WHERE hoi.org_information_context = 'Customer/Supplier Association'
1709: , jmf_shikyu_components jsc
1710: , jmf_subcontract_orders jso
1711: , wip_requirement_operations wro
1712: , po_line_locations_all plla
1713: , po_lines_all pla
1714: , po_headers_all pha
1715: WHERE jso.subcontract_po_shipment_id = jsc.subcontract_po_shipment_id
1716: AND jsc.subcontract_po_shipment_id=jsa.subcontract_po_shipment_id(+)
1717: AND jsc.shikyu_component_id = jsa.shikyu_component_id(+)
2248: -- Bug 5500896: Added a where clause condition to filter out the
2249: -- subcontracting orders that do not belong to the operating unit
2250: -- for which the current run was executed in
2251:
2252: -- Bug 5678387: Modified the query to get the uom from PO_LINES_ALL
2253: -- if the uom is not stamped on the PO_LINE_LOCATIONS_ALL record,
2254: -- which seems to be the case for PO Release Shipments
2255: -- Also, removed bom_explosion_temp_s.nextval from the select statement
2256: -- since we do not insert into bom_explosion_temp, and this would
2274: , 'V'
2275: FROM
2276: jmf_subcontract_orders jso
2277: , po_line_locations_all poll
2278: , po_lines_all pla
2279: WHERE poll.line_location_id = jso.subcontract_po_shipment_id
2280: AND pla.po_line_id = poll.po_line_id
2281: AND jso.interlock_status = 'N'
2282: AND poll.org_id = p_operating_unit
3194: || l_osa_tbl(i).tp_organization_id
3195: );
3196: END IF;
3197:
3198: -- Bug 5678387: Modified the query to get the uom from PO_LINES_ALL
3199: -- if the uom is not stamped on the PO_LINE_LOCATIONS_ALL record,
3200: -- which seems to be the case for PO Release Shipments
3201:
3202: SELECT plla.quantity
3203: , NVL(plla.unit_meas_lookup_code, pla.unit_meas_lookup_code)
3204: INTO l_po_qty
3205: , l_po_uom
3206: FROM po_line_locations_all plla
3207: , po_lines_all pla
3208: WHERE plla.line_location_id = l_osa_tbl(i).subcontract_po_shipment_id
3209: AND plla.po_line_id = pla.po_line_id;
3210:
3211: l_primary_uom:= JMF_SHIKYU_UTIL.Get_Primary_UOM
3407: CURSOR c_proc_batch IS
3408: SELECT jso.subcontract_po_shipment_id
3409: FROM jmf_subcontract_orders jso
3410: , po_line_locations_all plla
3411: , po_lines_all pla
3412: , po_headers_all pha
3413: WHERE jso.interlock_status IN ('N', 'U')
3414: AND plla.line_location_id = jso.subcontract_po_shipment_id
3415: AND plla.po_line_id = pla.po_line_id