[Home] [Help]
11124: where PRL.deliver_to_location_id = HRL.location_id
11125: and nvl(HRL.ship_to_location_id, HRL.location_id) = x_ship_to_location_id
11126: UNION ALL
11127: select 'x'
11128: from HZ_LOCATIONS HZ
11129: where PRL.deliver_to_location_id = HZ.location_id
11130: and HZ.location_id = x_ship_to_location_id)
11131: )
11132: AND( nvl(l_shipto_prf,'Y') = 'N' -- Bug 3201308
12346: ** interface table matches the shipment you are trying to create.
12347: */
12348: /* Bug # 2224446, Added outer join on mtl_system_items */
12349: /* Bug: 2348161.Changed the below SQL and removed the reference to the tables
12350: HZ_LOCATIONS and HR_LOCATIONS and also the corresponding where
12351: clause. Instead added a subquery to check for the location_id
12352: to improve the performance
12353: */
12354: /* Bug 2466578. Changed the UNION to UNION ALL in the sub query to improve the
12405: where PRL.deliver_to_location_id = HRL.location_id
12406: and nvl(HRL.ship_to_location_id, HRL.location_id) = x_ship_to_location_id
12407: UNION ALL
12408: select 'x'
12409: from HZ_LOCATIONS HZ
12410: where PRL.deliver_to_location_id = HZ.location_id
12411: and HZ.location_id = x_ship_to_location_id)
12412: AND PRL.destination_organization_id = x_destination_org_id
12413: AND DECODE(PRL.destination_type_code,
12474: ** See if a record that has just been inserted into the
12475: ** interface table matches the shipment you are trying to create.
12476: */
12477: /* Bug: 2348161.Changed the below SQL and removed the reference to the tables
12478: HZ_LOCATIONS and HR_LOCATIONS and also the corresponding where
12479: clause. Instead added a subquery to check for the location_id
12480: to improve the performance
12481: */
12482: SELECT PLI.shipment_num
12525: where PRL.deliver_to_location_id = HRL.location_id
12526: and nvl(HRL.ship_to_location_id, HRL.location_id) = x_ship_to_location_id
12527: UNION ALL
12528: select 'x'
12529: from HZ_LOCATIONS HZ
12530: where PRL.deliver_to_location_id = HZ.location_id
12531: and HZ.location_id = x_ship_to_location_id)
12532: -- start of 1548597
12533: AND
12593: end;
12594: IF x_check_doc_sub_type='BID' then
12595:
12596: /* Bug: 2348161.Changed the below SQL and removed the reference to the tables
12597: HZ_LOCATIONS and HR_LOCATIONS and also the corresponding where
12598: clause. Instead added a subquery to check for the location_id
12599: to improve the performance
12600: */
12601:
12652: where PRL.deliver_to_location_id = HRL.location_id
12653: and nvl(HRL.ship_to_location_id, HRL.location_id) = x_ship_to_location_id
12654: UNION ALL
12655: select 'x'
12656: from HZ_LOCATIONS HZ
12657: where PRL.deliver_to_location_id = HZ.location_id
12658: and HZ.location_id = x_ship_to_location_id)
12659: AND ROWNUM = 1
12660: ORDER BY shipment_num;
13513: BEGIN
13514:
13515: SELECT hzl.location_id
13516: INTO l_ship_to_location_id
13517: FROM hz_locations hzl
13518: WHERE hzl.location_id = p_deliver_to_loc_id;
13519:
13520: l_found := TRUE;
13521:
13521:
13522: EXCEPTION
13523: WHEN NO_DATA_FOUND THEN
13524: IF (PO_LOG.d_stmt) THEN
13525: PO_LOG.stmt(d_module, d_progress, 'No data found in hz_locations.');
13526: END IF;
13527: END;
13528:
13529: END IF; -- if not l_found