[Home] [Help]
139: --| POs won't be created. |
140: --| 05/09/2006 vchu Modified the INSERT INTO |
141: --| JMF_SUBCONTRACT_ORDERS_TEMP statement |
142: --| to populate the need_by_date as the |
143: --| promised_date from po_line_locations_all|
144: --| table, if need_by_date was null. |
145: --| 05/12/2006 vchu Bug fix for 5212199: Added where clause |
146: --| condition to c_alloc to select only the |
147: --| not fully allocated Subcontracting |
240: --| seriously hurt the performance. |
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. |
1355: , poll.price_override
1356: , pol.project_id
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
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,
1793: hoi.organization_id as oem_organization_id,
1801: pha.segment1,
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
1881: FROM jmf_shikyu_allocations jsa
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(+)
1944: l_ordered_quantity NUMBER;
1945: l_primary_uom_qty NUMBER;
1946: l_primary_uom VARCHAR2(3);
1947: l_additional_supply VARCHAR2(1);
1948: l_po_uom PO_LINE_LOCATIONS_ALL.unit_meas_lookup_code%TYPE;
1949: l_po_quantity NUMBER;
1950: --l_shipment_id NUMBER;
1951: l_osa_shipment_id NUMBER;
1952: l_osa_component_id NUMBER;
1956: l_reference_num VARCHAR2(25);
1957: l_po_need_by_date DATE;
1958: l_po_header_num PO_HEADERS_ALL.SEGMENT1%TYPE;
1959: l_po_line_num PO_LINES.LINE_NUM%TYPE;
1960: l_po_shipment_num PO_LINE_LOCATIONS_ALL.SHIPMENT_NUM%TYPE;
1961: l_subcontracting_component
1962: NUMBER;
1963: l_replen_so_creation_failed
1964: VARCHAR2(1);
2240: , poll.quantity
2241: , l_primary_uom_qty
2242: , 0
2243: , 0
2244: FROM po_line_locations_all poll
2245: WHERE poll.line_location_id = l_line_location_id;
2246:
2247: ELSE
2248:
2448: , oem_organization_id NUMBER
2449: , tp_organization_id NUMBER
2450: , need_by_date DATE
2451: , quantity NUMBER
2452: , unit_of_measure PO_LINE_LOCATIONS_ALL.unit_meas_lookup_code%TYPE
2453: , uom_code MTL_UNITS_OF_MEASURE_VL.uom_code%TYPE
2454: , primary_uom_code MTL_UNITS_OF_MEASURE_VL.uom_code%TYPE
2455: , primary_uom_quantity NUMBER
2456: , project_id NUMBER
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
2496: -- unnecessarily bump up the sequence number for the ID of
2512: , jso.task_id
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'
3237: , jso.tp_organization_id
3238: FROM
3239: jmf_subcontract_orders jso,
3240: jmf_shikyu_components jsc,
3241: po_line_locations_all plla
3242: WHERE jso.subcontract_po_shipment_id = jsc.subcontract_po_shipment_id
3243: AND shikyu_component_price IS NULL
3244: AND plla.line_location_id = jso.subcontract_po_shipment_id
3245: AND plla.org_id = p_operating_unit;
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)
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:
3705:
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