APPS.FTE_ACS_RULE_UTIL_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 390

  select nvl(fr.attribute_value_from_number, -1) from_region,
         nvl(tr.attribute_value_from_number, -1) to_region,
         attr.attribute_value_from_number, attr.attribute_value_to_number,
         attr.attribute_value_from, attr.attribute_value_to,
         attr.rule_attribute_id, attr.range_overlap_flag
    from fte_sel_rule_restrictions attr, fte_sel_rule_restrictions fr,
         fte_sel_rule_restrictions tr, fte_sel_rules rr
   where attr.group_id = x_group_id and attr.attribute_name = x_attr_name
     and fr.rule_id (+)= attr.rule_id
     and fr.attribute_name (+)= 'FROM_REGION_ID'
     and tr.rule_id (+)= attr.rule_id
     and tr.attribute_name (+)= 'TO_REGION_ID'
     and rr.rule_id = attr.rule_id
  order by from_region, to_region;

Line: 456

      UPDATE fte_sel_rule_restrictions
         SET range_overlap_flag = l_overlap_flag_tab(i)
       WHERE rule_attribute_id = l_rule_attribute_id_tab(i);

Line: 606

SELECT postal_code
FROM   wsh_locations
WHERE  wsh_location_id = p_location_id;

Line: 661

PROCEDURE INSERT_INTO_GTT(p_input_data	   IN	        FTE_ACS_PKG.fte_cs_entity_tab_type,
			  x_return_status  OUT NOCOPY  VARCHAR2)
IS
   l_cnt	       NUMBER;

Line: 666

   l_module_name       CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INSERT_INTO_GTT';

Line: 669

   l_insert_gtt_rec    FTE_CS_BULK_ENTITY_GTT;

Line: 679

  	      WSH_DEBUG_SV.logmsg(l_module_name,'Inserting data into GTT');

Line: 710

	 -- For Bulk insert we need to have record of tables instead of table of records.
	 --

	 i := 1;

Line: 717

		l_insert_gtt_rec.delivery_id_tab(i)			:=  p_input_data(itr).delivery_id;

Line: 718

		l_insert_gtt_rec.trip_id_tab(i)				:=  p_input_data(itr).trip_id;

Line: 719

		l_insert_gtt_rec.delivery_name_tab(i)			:=  p_input_data(itr).delivery_name;

Line: 720

		l_insert_gtt_rec.trip_name_tab(i)			:=  p_input_data(itr).trip_name;

Line: 721

		l_insert_gtt_rec.organization_id_tab(i)			:=  p_input_data(itr).organization_id;

Line: 722

		l_insert_gtt_rec.triporigin_internal_org_id_tab(i)	:=  p_input_data(itr).triporigin_internalorg_id;

Line: 723

		l_insert_gtt_rec.customer_id_tab(i)			:=  p_input_data(itr).customer_id;

Line: 724

		l_insert_gtt_rec.customer_site_id_tab(i)	       	:=  p_input_data(itr).customer_site_id;

Line: 725

		l_insert_gtt_rec.gross_weight_tab(i)			:=  p_input_data(itr).gross_weight;

Line: 726

		l_insert_gtt_rec.weight_uom_code_tab(i)	       		:=  p_input_data(itr).weight_uom_code;

Line: 727

		l_insert_gtt_rec.volume_tab(i)				:=  p_input_data(itr).volume;

Line: 728

		l_insert_gtt_rec.volume_uom_code_tab(i)	       		:=  p_input_data(itr).volume_uom_code;

Line: 729

		l_insert_gtt_rec.initial_pickup_loc_id_tab(i)		:=  p_input_data(itr).initial_pickup_loc_id;

Line: 730

		l_insert_gtt_rec.ultimate_dropoff_loc_id_tab(i)		:=  p_input_data(itr).ultimate_dropoff_loc_id;

Line: 731

		l_insert_gtt_rec.initial_pickup_date_tab(i)		:=  nvl(p_input_data(itr).initial_pickup_date,SYSDATE);

Line: 732

		l_insert_gtt_rec.ultimate_dropoff_date_tab(i)		:=  p_input_data(itr).ultimate_dropoff_date;

Line: 733

		l_insert_gtt_rec.freight_terms_code_tab(i)		:=  p_input_data(itr).freight_terms_code;

Line: 734

		l_insert_gtt_rec.fob_code_tab(i)			:=  p_input_data(itr).fob_code;

Line: 735

		l_insert_gtt_rec.search_level_tab(i)			:=  p_input_data(itr).start_search_level;

Line: 736

		l_insert_gtt_rec.transit_time_tab(i)			:=  p_input_data(itr).transit_time;

Line: 746

            INSERT INTO FTE_SEL_SEARCH_ENTITIES_TMP(
			delivery_id,
			trip_id	,
			delivery_name,
			trip_name,
			organization_id,
			triporigin_internalorg_id,
			customer_id,
			customer_site_id,
			gross_weight,
			weight_uom_code,
			volume,
			volume_uom_code,
			initial_pickup_loc_id,
			ultimate_dropoff_loc_id,
			initial_pickup_date,
			ultimate_dropoff_date,
			freight_terms_code,
			fob_code,
			search_level,
			transit_time)
	    VALUES(
			l_insert_gtt_rec.delivery_id_tab(j),
			l_insert_gtt_rec.trip_id_tab(j),
			l_insert_gtt_rec.delivery_name_tab(j),
			l_insert_gtt_rec.trip_name_tab(j),
			l_insert_gtt_rec.organization_id_tab(j),
			l_insert_gtt_rec.triporigin_internal_org_id_tab(j),
			l_insert_gtt_rec.customer_id_tab(j),
			l_insert_gtt_rec.customer_site_id_tab(j),
			l_insert_gtt_rec.gross_weight_tab(j),
			l_insert_gtt_rec.weight_uom_code_tab(j),
			l_insert_gtt_rec.volume_tab(j),
			l_insert_gtt_rec.volume_uom_code_tab(j),
			l_insert_gtt_rec.initial_pickup_loc_id_tab(j),
			l_insert_gtt_rec.ultimate_dropoff_loc_id_tab(j),
			l_insert_gtt_rec.initial_pickup_date_tab(j),
			l_insert_gtt_rec.ultimate_dropoff_date_tab(j),
			l_insert_gtt_rec.freight_terms_code_tab(j),
			l_insert_gtt_rec.fob_code_tab(j),
			l_insert_gtt_rec.search_level_tab(j),
			l_insert_gtt_rec.transit_time_tab(j));

Line: 796

      WSH_UTIL_CORE.default_handler('FTE_ACS_RULE_UTIL_PKG.INSERT_INTO_GTT');

Line: 804

END insert_into_gtt;

Line: 961

select wcs.ship_method_code
from   wsh_org_carrier_services wocs,
       wsh_carrier_services     wcs
where  wcs.carrier_id         = p_carrier_id
and    wcs.service_level      = p_service_level
and    wcs.mode_of_transport  = p_mode_of_transport
and    wcs.enabled_flag       = 'Y'
and    wcs.carrier_service_id = wocs.carrier_service_id
and    wocs.organization_id   = p_organization_id
and    wocs.enabled_flag      = 'Y';

Line: 1078

SELECT  fsg.group_id,
	fsset.delivery_id,
	fsset.trip_id,
	fsset.gross_weight,
	fsset.weight_uom_code,
	fsset.volume,
	fsset.volume_uom_code,
	fsset.transit_time,
	fsset.initial_pickup_loc_id,
	fsset.ultimate_dropoff_loc_id,
	fsset.fob_code
--      We do not fetch start date and end date here as we are not going to cache this information.
FROM  	FTE_SEL_SEARCH_ENTITIES_TMP FSSET,
	FTE_SEL_GROUP_ASSIGNMENTS FSGA,
	FTE_SEL_GROUPS FSG
WHERE	fsga.customer_site_id  = fsset.customer_site_id
AND     fsga.group_id 	       = fsg.group_id
AND     fsg.object_id          = g_object_id
--AND     nvl(fsg.group_status_flag,'A')  NOT IN ('D','I')
AND     nvl(fsg.group_status_flag,'A') = g_flag_active
AND    (fsg.start_date < fsset.initial_pickup_date OR
        fsg.start_date is null)
AND    (fsg.end_date   > fsset.initial_pickup_date OR
        fsg.end_date is null)
AND     fsset.rule_id IS NULL
AND     fsset.search_level <> 'O'
ORDER BY fsg.group_id;

Line: 1108

SELECT  fsg.group_id,
	fsset.delivery_id,
	fsset.trip_id,
	fsset.gross_weight,
	fsset.weight_uom_code,
	fsset.volume,
	fsset.volume_uom_code,
	fsset.transit_time,
	fsset.initial_pickup_loc_id,
	fsset.ultimate_dropoff_loc_id,
	fsset.fob_code
--      We do not fetch start date and end date here as we are not going to cache this information.
FROM  	FTE_SEL_SEARCH_ENTITIES_TMP FSSET,
	FTE_SEL_GROUP_ASSIGNMENTS FSGA,
	FTE_SEL_GROUPS FSG
WHERE	fsga.customer_id      = fsset.customer_id
AND     fsga.group_id         = fsg.group_id
AND     fsg.object_id         = g_object_id
--AND     nvl(fsg.group_status_flag,'A')  NOT IN ('D','I')
AND     nvl(fsg.group_status_flag,'A') = g_flag_active
AND    (fsg.start_date < fsset.initial_pickup_date OR
        fsg.start_date is null)
AND    (fsg.end_date   > fsset.initial_pickup_date OR
        fsg.end_date is null)
AND	fsset.rule_id IS NULL
AND     fsset.search_level <>'O'
ORDER BY fsg.group_id;

Line: 1138

SELECT  fsg.group_id,
	fsset.delivery_id,
	fsset.trip_id,
	fsset.gross_weight,
	fsset.weight_uom_code,
	fsset.volume,
	fsset.volume_uom_code,
	fsset.transit_time,
	fsset.initial_pickup_loc_id,
	fsset.ultimate_dropoff_loc_id,
	fsset.fob_code
--      We do not fetch start date and end date here as we are not going to cache this information.
FROM    FTE_SEL_SEARCH_ENTITIES_TMP FSSET,
	FTE_SEL_GROUP_ASSIGNMENTS FSGA,
	FTE_SEL_GROUPS FSG
WHERE   fsga.organization_id   = fsset.organization_id
AND     fsga.group_id          = fsg.group_id
AND     fsg.object_id          = g_object_id
--AND     nvl(fsg.group_status_flag,'A')  NOT IN ('D','I')
AND     nvl(fsg.group_status_flag,'A') = g_flag_active
AND    (fsg.start_date < fsset.initial_pickup_date OR
        fsg.start_date is null)
AND    (fsg.end_date   > fsset.initial_pickup_date OR
        fsg.end_date is null)
AND	fsset.rule_id IS NULL
ORDER BY fsg.group_id;

Line: 1170

SELECT  fsg.group_id,
	fsset.delivery_id,
	fsset.trip_id,
	fsset.gross_weight,
	fsset.weight_uom_code,
	fsset.volume,
	fsset.volume_uom_code,
	fsset.transit_time,
	fsset.initial_pickup_loc_id,
	fsset.ultimate_dropoff_loc_id,
	fsset.fob_code
--      We do not fetch start date and end date here as we are not going to cache this information.
FROM    FTE_SEL_SEARCH_ENTITIES_TMP FSSET,
	FTE_SEL_GROUP_ASSIGNMENTS FSGA,
	FTE_SEL_GROUPS FSG
WHERE   fsga.organization_id   = fsset.triporigin_internalorg_id
AND     fsga.group_id          = fsg.group_id
AND     fsg.object_id          = g_object_id
--AND     nvl(fsg.group_status_flag,'A')  NOT IN ('D','I')
AND     nvl(fsg.group_status_flag,'A') = g_flag_active
AND    (fsg.start_date < fsset.initial_pickup_date OR
        fsg.start_date is null)
AND    (fsg.end_date   > fsset.initial_pickup_date OR
        fsg.end_date is null)
AND	fsset.rule_id IS NULL
ORDER BY fsg.group_id;

Line: 1201

SELECT  fsg.group_id,
	fsset.delivery_id,
	fsset.trip_id,
	fsset.gross_weight,
	fsset.weight_uom_code,
	fsset.volume,
	fsset.volume_uom_code,
	fsset.transit_time,
	fsset.initial_pickup_loc_id,
	fsset.ultimate_dropoff_loc_id,
	fsset.fob_code
--      We do not fetch start date and end date here as we are not going to cache this information.
FROM  	FTE_SEL_SEARCH_ENTITIES_TMP FSSET,
 	FTE_SEL_GROUPS FSG,
    FTE_SEL_GROUP_ASSIGNMENTS assign
WHERE   --assigned_flag = 'E'
--AND
fsg.object_id =  g_object_id
--AND     nvl(fsg.group_status_flag,'A')  NOT IN ('D','I')
AND     nvl(fsg.group_status_flag,'A') = g_flag_active
AND    (fsg.start_date < fsset.initial_pickup_date OR
         fsg.start_date is null)
AND    (fsg.end_date   > fsset.initial_pickup_date OR
         fsg.end_date is null)
AND	fsset.rule_id IS NULL
and fsg.group_id = assign.group_id
and assign.customer_id is null and assign.CUSTOMER_SITE_ID is null and assign.ORGANIZATION_ID is null

ORDER BY fsg.group_id;

Line: 1237

SELECT fsg.group_id,
       fsg.start_date,
       fsg.end_date
FROM   fte_sel_group_assignments fsga,
       fte_sel_groups            fsg
WHERE  fsga.customer_site_id  = p_customer_site_id
AND    fsga.group_id          = fsg.group_id
AND    fsg.object_id          = g_object_id
--AND     nvl(fsg.group_status_flag,'A')  NOT IN ('D','I')
AND    nvl(fsg.group_status_flag,'A') = g_flag_active
AND    (fsg.start_date < p_ship_date OR
        fsg.start_date is null)
AND    (fsg.end_date   > p_ship_date OR
        fsg.end_date is null);

Line: 1257

SELECT fsg.group_id,
       fsg.start_date,
       fsg.end_date
FROM   fte_sel_group_assignments fsga,
       fte_sel_groups fsg
WHERE  fsga.customer_id       = p_customer_id
AND    fsga.group_id          = fsg.group_id
AND    fsg.object_id          = g_object_id
--AND     nvl(fsg.group_status_flag,'A')  NOT IN ('D','I')
AND    nvl(fsg.group_status_flag,'A') = g_flag_active
AND   (fsg.start_date < p_ship_date OR
       fsg.start_date is null)
AND   (fsg.end_date   > p_ship_date OR
       fsg.end_date is null);

Line: 1277

SELECT fsg.group_id,
       fsg.start_date,
       fsg.end_date
FROM   fte_sel_group_assignments fsga,
       fte_sel_groups            fsg
WHERE  fsga.organization_id    = p_org_id
AND    fsga.group_id           = fsg.group_id
AND    fsg.object_id           = g_object_id
--AND     nvl(fsg.group_status_flag,'A')  NOT IN ('D','I')
AND    nvl(fsg.group_status_flag,'A') = g_flag_active
AND    (fsg.start_date < p_ship_date OR
        fsg.start_date is null)
AND    (fsg.end_date   > p_ship_date OR
        fsg.end_date is null);

Line: 1296

SELECT fsg.group_id,
       fsg.start_date,
       fsg.end_date
FROM   fte_sel_groups fsg,
        FTE_SEL_GROUP_ASSIGNMENTS assign
WHERE  --assigned_flag          = 'E'
--AND
fsg.object_id          = g_object_id
--AND     nvl(fsg.group_status_flag,'A')  NOT IN ('D','I')
AND    nvl(fsg.group_status_flag,'A') = g_flag_active
AND    (fsg.start_date < p_ship_date OR
        fsg.start_date is null)
AND    (fsg.end_date   > p_ship_date OR
        fsg.end_date is null)
 and fsg.group_id = assign.group_id
and assign.customer_id is null and assign.CUSTOMER_SITE_ID is null and assign.ORGANIZATION_ID is null;