[Home] [Help]
11761: where PRL.deliver_to_location_id = HRL.location_id
11762: and nvl(HRL.ship_to_location_id, HRL.location_id) = x_ship_to_location_id
11763: UNION ALL
11764: select 'x'
11765: from HZ_LOCATIONS HZ
11766: where PRL.deliver_to_location_id = HZ.location_id
11767: and HZ.location_id = x_ship_to_location_id)
11768: )
11769: AND( nvl(l_shipto_prf,'Y') = 'N' -- Bug 3201308
13068: ** interface table matches the shipment you are trying to create.
13069: */
13070: /* Bug # 2224446, Added outer join on mtl_system_items */
13071: /* Bug: 2348161.Changed the below SQL and removed the reference to the tables
13072: HZ_LOCATIONS and HR_LOCATIONS and also the corresponding where
13073: clause. Instead added a subquery to check for the location_id
13074: to improve the performance
13075: */
13076: /* Bug 2466578. Changed the UNION to UNION ALL in the sub query to improve the
13127: where PRL.deliver_to_location_id = HRL.location_id
13128: and nvl(HRL.ship_to_location_id, HRL.location_id) = x_ship_to_location_id
13129: UNION ALL
13130: select 'x'
13131: from HZ_LOCATIONS HZ
13132: where PRL.deliver_to_location_id = HZ.location_id
13133: and HZ.location_id = x_ship_to_location_id)
13134: AND PRL.destination_organization_id = x_destination_org_id
13135: AND DECODE(PRL.destination_type_code,
13196: ** See if a record that has just been inserted into the
13197: ** interface table matches the shipment you are trying to create.
13198: */
13199: /* Bug: 2348161.Changed the below SQL and removed the reference to the tables
13200: HZ_LOCATIONS and HR_LOCATIONS and also the corresponding where
13201: clause. Instead added a subquery to check for the location_id
13202: to improve the performance
13203: */
13204: SELECT PLI.shipment_num
13247: where PRL.deliver_to_location_id = HRL.location_id
13248: and nvl(HRL.ship_to_location_id, HRL.location_id) = x_ship_to_location_id
13249: UNION ALL
13250: select 'x'
13251: from HZ_LOCATIONS HZ
13252: where PRL.deliver_to_location_id = HZ.location_id
13253: and HZ.location_id = x_ship_to_location_id)
13254: -- start of 1548597
13255: AND
13315: end;
13316: IF x_check_doc_sub_type='BID' then
13317:
13318: /* Bug: 2348161.Changed the below SQL and removed the reference to the tables
13319: HZ_LOCATIONS and HR_LOCATIONS and also the corresponding where
13320: clause. Instead added a subquery to check for the location_id
13321: to improve the performance
13322: */
13323:
13374: where PRL.deliver_to_location_id = HRL.location_id
13375: and nvl(HRL.ship_to_location_id, HRL.location_id) = x_ship_to_location_id
13376: UNION ALL
13377: select 'x'
13378: from HZ_LOCATIONS HZ
13379: where PRL.deliver_to_location_id = HZ.location_id
13380: and HZ.location_id = x_ship_to_location_id)
13381: AND ROWNUM = 1
13382: ORDER BY shipment_num;
14419: BEGIN
14420:
14421: SELECT hzl.location_id
14422: INTO l_ship_to_location_id
14423: FROM hz_locations hzl
14424: WHERE hzl.location_id = p_deliver_to_loc_id;
14425:
14426: l_found := TRUE;
14427:
14427:
14428: EXCEPTION
14429: WHEN NO_DATA_FOUND THEN
14430: IF (PO_LOG.d_stmt) THEN
14431: PO_LOG.stmt(d_module, d_progress, 'No data found in hz_locations.');
14432: END IF;
14433: END;
14434:
14435: END IF; -- if not l_found