[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 |
1357: , pol.task_id
1358: FROM
1359: po_line_locations_all poll
1360: , po_headers_all poh
1361: , po_lines_all pol
1362: , mtl_units_of_measure muom
1363: , po_releases_all por
1364: WHERE poll.po_header_id = poh.po_header_id
1365: AND poll.po_line_id = pol.po_line_id
1784: -- Selects the Replenishment POs with the supplier matching the OEM
1785: -- Organization and the Ship To Organization matching the TP
1786: -- Organization of some SHIKYU enabled shipping network.
1787:
1788: -- Bug 5678387: Modified the query to get the uom from PO_LINES_ALL
1789: -- if the uom is not stamped on the PO_LINE_LOCATIONS_ALL record
1790:
1791: CURSOR c_rep IS
1792: SELECT DISTINCT plla.line_location_id,
1802: pla.line_num,
1803: plla.shipment_num
1804: FROM hr_organization_information hoi,
1805: po_line_locations_all plla,
1806: po_lines_all pla,
1807: po_headers_all pha,
1808: mtl_interorg_parameters mip,
1809: mtl_system_items_b msib
1810: WHERE hoi.org_information_context = 'Customer/Supplier Association'
1882: , jmf_shikyu_components jsc
1883: , jmf_subcontract_orders jso
1884: , wip_requirement_operations wro
1885: , po_line_locations_all plla
1886: , po_lines_all pla
1887: , po_headers_all pha
1888: WHERE jso.subcontract_po_shipment_id = jsc.subcontract_po_shipment_id
1889: AND jsc.subcontract_po_shipment_id=jsa.subcontract_po_shipment_id(+)
1890: AND jsc.shikyu_component_id = jsa.shikyu_component_id(+)
2487: -- Bug 5500896: Added a where clause condition to filter out the
2488: -- subcontracting orders that do not belong to the operating unit
2489: -- for which the current run was executed in
2490:
2491: -- Bug 5678387: Modified the query to get the uom from PO_LINES_ALL
2492: -- if the uom is not stamped on the PO_LINE_LOCATIONS_ALL record,
2493: -- which seems to be the case for PO Release Shipments
2494: -- Also, removed bom_explosion_temp_s.nextval from the select statement
2495: -- since we do not insert into bom_explosion_temp, and this would
2513: , 'V'
2514: FROM
2515: jmf_subcontract_orders jso
2516: , po_line_locations_all poll
2517: , po_lines_all pla
2518: WHERE poll.line_location_id = jso.subcontract_po_shipment_id
2519: AND pla.po_line_id = poll.po_line_id
2520: AND jso.interlock_status = 'N'
2521: AND poll.org_id = p_operating_unit
3471: || l_osa_tbl(i).tp_organization_id
3472: );
3473: END IF;
3474:
3475: -- Bug 5678387: Modified the query to get the uom from PO_LINES_ALL
3476: -- if the uom is not stamped on the PO_LINE_LOCATIONS_ALL record,
3477: -- which seems to be the case for PO Release Shipments
3478:
3479: SELECT plla.quantity
3480: , NVL(plla.unit_meas_lookup_code, pla.unit_meas_lookup_code)
3481: INTO l_po_qty
3482: , l_po_uom
3483: FROM po_line_locations_all plla
3484: , po_lines_all pla
3485: WHERE plla.line_location_id = l_osa_tbl(i).subcontract_po_shipment_id
3486: AND plla.po_line_id = pla.po_line_id;
3487:
3488: l_primary_uom:= JMF_SHIKYU_UTIL.Get_Primary_UOM
3706: CURSOR c_proc_batch IS
3707: SELECT jso.subcontract_po_shipment_id
3708: FROM jmf_subcontract_orders jso
3709: , po_line_locations_all plla
3710: , po_lines_all pla
3711: , po_headers_all pha
3712: WHERE jso.interlock_status IN ('N', 'U')
3713: AND plla.line_location_id = jso.subcontract_po_shipment_id
3714: AND plla.po_line_id = pla.po_line_id