APPS.OE_ITORD_PUB SQL Statements

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

Line: 31

					OE_ITORD_PUB.insert_rules(p_Item_Orderability_Import_Tbl(i));

Line: 163

	IF p_Item_Orderability_Import_Rec.last_updated_by  is NULL then
	     fnd_message.set_name('ONT','OE_ATTRIBUTE_REQUIRED');

Line: 165

             fnd_message.set_token('ATTRIBUTE','LAST_UPDATED_BY');

Line: 171

	IF p_Item_Orderability_Import_Rec.last_update_date  is NULL then
	     fnd_message.set_name('ONT','OE_ATTRIBUTE_REQUIRED');

Line: 173

             fnd_message.set_token('ATTRIBUTE','LAST_UPDATE_DATE');

Line: 207

			     SELECT 'Y' into l_exists
			     FROM HR_OPERATING_UNITS
			     where organization_id = p_Item_Orderability_Import_Rec.org_id
			     and rownum = 1;

Line: 276

	     select 'Y' into l_exists
	     from fnd_user where user_id = p_Item_Orderability_Import_Rec.created_by
	     and end_date is NULL;

Line: 292

IF p_Item_Orderability_Import_Rec.last_updated_by  is NULL then
	     begin
	     select 'Y' into l_exists
	     from fnd_user where user_id =  p_Item_Orderability_Import_Rec.last_updated_by
	     and end_date is NULL;

Line: 301

	        fnd_message.set_token('ATTRIBUTE','LAST_UPDATED_BY');

Line: 350

		    SELECT 'Y'
		    INTO l_exists
		    FROM hz_parties party,
			hz_cust_accounts acct
		    WHERE acct.party_id = party.party_id
		    AND   acct.status = 'A'
		    AND acct.cust_account_id = p_Item_Orderability_Import_Rec.customer_id ;

Line: 395

		         SELECT 'Y'
			 INTO l_exists
			 FROM hz_cust_profile_classes cpc
			 WHERE profile_class_id = p_Item_Orderability_Import_Rec.customer_class_id ;

Line: 440

		   SELECT 'Y'
		   INTO l_exists
		   FROM ar_lookups
		   WHERE lookup_type = 'CUSTOMER_CATEGORY'
		    AND lookup_code = p_Item_Orderability_Import_Rec.customer_category_code ;

Line: 479

		    SELECT 'Y'
		    INTO l_exists
		    FROM wsh_regions_v
		   WHERE region_id = p_Item_Orderability_Import_Rec.region_id;

Line: 517

		   SELECT 'Y'
		   INTO l_exists
		   FROM oe_order_types_v
		   WHERE order_type_id = p_Item_Orderability_Import_Rec.order_type_id ;

Line: 556

		   SELECT  'Y'
		   INTO l_exists
		   FROM hz_cust_site_uses_all site
		   WHERE site.site_use_code = 'SHIP_TO'
		    AND site.site_use_id= p_Item_Orderability_Import_Rec.SHIP_TO_LOCATION_ID;

Line: 595

		   SELECT 'Y'
		   INTO l_exists
		   FROM oe_lookups
		  WHERE lookup_type = 'SALES_CHANNEL'
		    AND lookup_code =p_Item_Orderability_Import_Rec.sales_channel_code;

Line: 636

		  SELECT 'Y'
		  INTO l_exists
		  FROM ra_salesreps
		  WHERE salesrep_id = p_Item_Orderability_Import_Rec.SALES_PERSON_ID;

Line: 676

		    SELECT 'Y'
		    INTO l_exists
		    FROM hz_parties party,
			hz_cust_accounts acct
		    WHERE acct.party_id = party.party_id
		    AND acct.cust_account_id = p_Item_Orderability_Import_Rec.end_customer_id ;

Line: 717

	    SELECT 'Y'
            INTO l_exists
            FROM hz_cust_site_uses_all site
            WHERE site.site_use_code = 'BILL_TO'
            AND site.site_use_id= p_Item_Orderability_Import_Rec.BILL_TO_LOCATION_ID ;

Line: 759

	   SELECT 'Y'
           INTO l_exists
           FROM hz_cust_site_uses_all site
           WHERE site.site_use_code = 'DELIVER_TO'
           AND site.site_use_id= p_Item_Orderability_Import_Rec.DELIVER_TO_LOCATION_ID;

Line: 802

		  select 'Y' into l_exists
		  from mtl_system_items_b
		  where inventory_item_id = p_Item_Orderability_Import_Rec.inventory_item_id
		  and organization_id = oe_sys_parameters.value('MASTER_ORGANIZATION_ID',p_Item_Orderability_Import_Rec.org_id );

Line: 832

				select 'Y' into l_exists
				from mtl_categories
				where category_id = p_Item_Orderability_Import_Rec.item_category_id ;

Line: 889

				 SELECT 'Y'
				 INTO l_exists
				 FROM mtl_item_categories ic,
				    mtl_default_category_sets cs,
				    oe_item_orderability oei
				 WHERE ic.category_set_id=cs.category_set_id
				 AND cs.functional_area_id = 7
				 AND ic.organization_id = oe_sys_parameters.Value('MASTER_ORGANIZATION_ID',p_Item_Orderability_Import_Rec.org_id)
				 AND ic.inventory_item_id = p_Item_Orderability_Import_Rec.inventory_item_id
				 AND oei.enable_flag='Y'
				 AND ic.category_id = oei.item_category_id
				 and org_id = p_Item_Orderability_Import_Rec.org_id
			        AND rownum = 1;

Line: 917

			sql_stmt :=    ' select count(1) '
			             || ' from oe_item_orderability io_hdr , oe_item_orderability_rules io_rules '
				     || ' where io_hdr.orderability_id = io_rules.orderability_id '
				     || ' and io_hdr.enable_flag = ''Y'' '
				     || ' and io_rules.enable_flag=''Y'' '
				     || ' and io_hdr.org_id = '|| p_Item_Orderability_Import_Rec.org_id
				     || ' and io_hdr.inventory_item_id = ' || p_Item_Orderability_Import_Rec.inventory_item_id
				     || ' and io_rules.rule_level = ' ||''''|| p_Item_Orderability_Import_Rec.rule_level ||''''
				     || ' and io_rules.'||l_rule_level_coulmn
				     || ' = ' ;

Line: 954

			       SELECT 'Y'
			       into l_exists
			       FROM mtl_item_categories ic,
				 mtl_default_category_sets cs,
				 oe_item_orderability oei
				WHERE ic.category_set_id=cs.category_set_id
				 AND cs.functional_area_id = 7
				 AND ic.organization_id=oe_sys_parameters.Value('MASTER_ORGANIZATION_ID',p_Item_Orderability_Import_Rec.org_id)
				 AND ic.inventory_item_id = oei.inventory_item_id
				 AND oei.enable_flag='Y'
				 AND ic.category_id = p_Item_Orderability_Import_Rec.ITEM_CATEGORY_ID
				 and org_id = p_Item_Orderability_Import_Rec.org_id
				 AND rownum = 1;

Line: 982

		sql_stmt :=    ' select count(1) '
			             || ' from oe_item_orderability io_hdr , oe_item_orderability_rules io_rules '
				     || ' where io_hdr.orderability_id = io_rules.orderability_id '
				     || ' and io_hdr.enable_flag = ''Y'' '
				     || ' and io_rules.enable_flag=''Y'' '
				     || ' and io_hdr.org_id = '|| p_Item_Orderability_Import_Rec.org_id
				     || ' and io_hdr.item_category_id = ' || p_Item_Orderability_Import_Rec.item_category_id
				     || ' and io_rules.rule_level = ' ||''''|| p_Item_Orderability_Import_Rec.rule_level ||''''
				     || ' and io_rules.'||l_rule_level_coulmn
				     || ' = ' ;

Line: 1091

Procedure insert_rules( p_Item_Orderability_Import_Rec IN OUT NOCOPY  OE_ITORD_PUB.Item_Orderability_Import_Rec ) IS

l_item_orderability_rec OE_ITORD_UTIL.Item_Orderability_Rec;

Line: 1103

	oe_debug_pub.add('Entering OE_ITORD_PUB.insert_rules');

Line: 1112

				select orderability_id into l_orderability_id
				from oe_item_orderability
				where  enable_flag = 'Y'
				and org_id = p_Item_Orderability_Import_Rec.org_id
				and   item_level = p_Item_Orderability_Import_Rec.ITEM_LEVEL
				and  ( inventory_item_id = p_Item_Orderability_Import_Rec.inventory_item_id
				       -- OR item_category_id  = p_Item_Orderability_Import_Rec.inventory_item_id );  -- Commented for bug 9779867

Line: 1129

				SELECT OE_ITEM_ORDERABILITY_S.nextval
				INTO l_orderability_id
			        FROM dual;

Line: 1142

			        l_item_orderability_rec.last_updated_by     := p_Item_Orderability_Import_Rec.last_updated_by;

Line: 1143

			        l_item_orderability_rec.last_update_date    := p_Item_Orderability_Import_Rec.last_update_date;

Line: 1146

				OE_ITORD_UTIL.INSERT_ROW (l_item_orderability_rec,l_status);

Line: 1166

			      l_item_orderability_rules_rec.LAST_UPDATED_BY        := p_Item_Orderability_Import_Rec.last_updated_by;

Line: 1167

			      l_item_orderability_rules_rec.LAST_UPDATE_DATE       := p_Item_Orderability_Import_Rec.last_update_date;

Line: 1190

			OE_ITORD_UTIL.Insert_Row( l_item_orderability_rules_rec
                              , l_status
                              , l_rowid
                              );

Line: 1197

				 select orderability_id into l_orderability_id
				 from oe_item_orderability_rules where rowid = l_rowid ;

Line: 1208

	oe_debug_pub.add('Leaving OE_ITORD_PUB.insert_rules');

Line: 1218

End insert_rules;