[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. |
1302: , poll.price_override
1303: , pol.project_id
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
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,
1624: hoi.organization_id as oem_organization_id,
1632: pha.segment1,
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
1708: FROM jmf_shikyu_allocations jsa
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(+)
1769: l_ordered_quantity NUMBER;
1770: l_primary_uom_qty NUMBER;
1771: l_primary_uom VARCHAR2(3);
1772: l_additional_supply VARCHAR2(1);
1773: l_po_uom PO_LINE_LOCATIONS_ALL.unit_meas_lookup_code%TYPE;
1774: l_po_quantity NUMBER;
1775: --l_shipment_id NUMBER;
1776: l_osa_shipment_id NUMBER;
1777: l_osa_component_id NUMBER;
1781: l_reference_num VARCHAR2(25);
1782: l_po_need_by_date DATE;
1783: l_po_header_num PO_HEADERS_ALL.SEGMENT1%TYPE;
1784: l_po_line_num PO_LINES.LINE_NUM%TYPE;
1785: l_po_shipment_num PO_LINE_LOCATIONS_ALL.SHIPMENT_NUM%TYPE;
1786: l_subcontracting_component
1787: NUMBER;
1788: l_replen_so_creation_failed
1789: VARCHAR2(1);
2026: , poll.quantity
2027: , l_primary_uom_qty
2028: , 0
2029: , 0
2030: FROM po_line_locations_all poll
2031: WHERE poll.line_location_id = l_line_location_id;
2032:
2033: ELSE
2034:
2209: , oem_organization_id NUMBER
2210: , tp_organization_id NUMBER
2211: , need_by_date DATE
2212: , quantity NUMBER
2213: , unit_of_measure PO_LINE_LOCATIONS_ALL.unit_meas_lookup_code%TYPE
2214: , uom_code MTL_UNITS_OF_MEASURE_VL.uom_code%TYPE
2215: , primary_uom_code MTL_UNITS_OF_MEASURE_VL.uom_code%TYPE
2216: , primary_uom_quantity NUMBER
2217: , project_id NUMBER
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
2257: -- unnecessarily bump up the sequence number for the ID of
2273: , jso.task_id
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'
2965: , jso.tp_organization_id
2966: FROM
2967: jmf_subcontract_orders jso,
2968: jmf_shikyu_components jsc,
2969: po_line_locations_all plla
2970: WHERE jso.subcontract_po_shipment_id = jsc.subcontract_po_shipment_id
2971: AND shikyu_component_price IS NULL
2972: AND plla.line_location_id = jso.subcontract_po_shipment_id
2973: AND plla.org_id = p_operating_unit;
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)
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:
3406:
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