APPS.INV_COMINGLING_UTILS SQL Statements

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

Line: 116

      SELECT 'Y' INTO l_serial_item
	FROM dual
	WHERE
	exists
	(--select count(*)
	 --into l_serial_count
	 SELECT inventory_item_id
	 from mtl_system_items
	 where organization_id = p_organization_id
	 and inventory_item_id = p_inventory_item_id
	 and serial_number_control_code NOT IN (1, 6)); --serial controlled items

Line: 142

	 SELECT 'Y' INTO l_moq_exist
	   FROM dual
	   WHERE
	   exists
	   (SELECT organization_id
	    --BUG 2921882
	    --Changing the count(*) to existence for performance improvement
	    --select
	    --count(*)
	    --into l_moq_count
	    from mtl_onhand_quantities_detail
	    where organization_id = p_organization_id
	    AND inventory_item_id  = p_inventory_item_id
	    AND (revision = p_revision
		 OR revision is null and p_revision is null)
	    AND (lot_number = p_lot_number
		 OR lot_number is null and p_lot_number is null)
	    AND subinventory_code = p_subinventory_code
	    AND  (locator_id = p_locator_id
		  OR locator_id is null and  p_locator_id is null)
	    AND cost_group_id is not null
	    AND cost_group_id <> p_cost_group_id
	    AND containerized_flag = 2 --  (loose material)
	    );

Line: 200

	 SELECT 'Y' INTO
	   l_mmtt_receipts_exist FROM dual
	   WHERE
	   exists
	   (SELECT organization_id
	    --BUG 2921882
	    --Changing the count(*) to existence for performance improvement
	    --select
	    --count(*)
	    --into l_mmtt_receipts_count
	    from mtl_material_transactions_temp
	    where organization_id = p_organization_id
	    AND inventory_item_id = p_inventory_item_id
	    AND (revision = p_revision
		 OR revision is null and p_revision is null)
	    AND lot_number is null
	    AND subinventory_code = p_subinventory_code
	    AND (locator_id = p_locator_id
		 OR locator_id is null and p_locator_id is null)
	    AND cost_group_id is not null
	    AND cost_group_id <> p_cost_group_id
	    AND transaction_action_id not in (inv_globals.G_Action_Issue,
					      inv_globals.G_Action_Subxfr,
					      inv_globals.G_Action_Orgxfr,
					      inv_globals.G_Action_IntransitShipment,
					      inv_globals.G_Action_Stgxfr,
					      inv_globals.G_Action_DeliveryAdj,
					      inv_globals.G_Action_AssyReturn,
					      inv_globals.G_Action_NegCompReturn)
		 AND posting_flag = 'Y');

Line: 239

	    SELECT 'Y' INTO l_mmtt_receipts_exist
	      FROM dual
	      WHERE
	      exists
	      (SELECT mmtt.organization_id --Bug 4496965
	       --BUG 2921882
	       --Changing the count(*) to existence for performance improvement
	       --select
	       --count(*)
	       --into l_mmtt_receipts_count
	       from mtl_material_transactions_temp mmtt,
	       mtl_transaction_lots_temp mtlt
	       where mmtt.organization_id = p_organization_id
	       AND mmtt.inventory_item_id = p_inventory_item_id
	       AND (mmtt.revision = p_revision
		    OR mmtt.revision is null and p_revision is null)
	       AND (mtlt.lot_number = p_lot_number
		    and mtlt.transaction_temp_id = mmtt.transaction_temp_id)
	       AND mmtt.subinventory_code = p_subinventory_code
	       AND (mmtt.locator_id = p_locator_id
		    OR mmtt.locator_id is null and  p_locator_id is null)
	       AND mmtt.cost_group_id is not null
	       AND mmtt.cost_group_id <> p_cost_group_id
	       AND transaction_action_id not in (inv_globals.G_Action_Issue,
						 inv_globals.G_Action_Subxfr,
						 inv_globals.G_Action_Orgxfr,
						 inv_globals.G_Action_IntransitShipment,
						 inv_globals.G_Action_Stgxfr,
						 inv_globals.G_Action_DeliveryAdj,
						 inv_globals.G_Action_AssyReturn,
						 inv_globals.G_Action_NegCompReturn)
			 AND mmtt.posting_flag = 'Y');

Line: 298

		   SELECT 'Y' INTO l_mmtt_transfers_exist
		     FROM dual
		     WHERE
		     exists
		     (SELECT organization_id
		      --BUG 2921882
		      --Changing the count(*) to existence for performance improvement
		      --select
		      --count(*)
		      --into l_mmtt_transfers_count
		      from mtl_material_transactions_temp
		      where decode(transaction_action_id, inv_globals.G_Action_Orgxfr, transfer_organization, organization_id)= p_organization_id
		      and inventory_item_id = p_inventory_item_id
		      and (revision = p_revision
			   OR revision is null and p_revision is null)
		      AND lot_number is null
		      AND transfer_subinventory = p_subinventory_code
		      AND transfer_to_location IS null
		      AND transfer_cost_group_id is not null
		      AND transfer_cost_group_id <> p_cost_group_id
		      AND transaction_action_id in (inv_globals.G_Action_Subxfr,
						    inv_globals.G_Action_Orgxfr,
						    inv_globals.G_Action_Stgxfr)
		      AND posting_flag = 'Y');

Line: 328

		      SELECT 'Y' INTO l_mmtt_transfers_exist
			FROM dual
			WHERE
			exists
			(SELECT organization_id
			 --BUG 2921882
			 --Changing the count(*) to existence for performance improvement
			 --select
			 --count(*)
			 --into l_mmtt_transfers_count
			 from mtl_material_transactions_temp
			 where decode(transaction_action_id, inv_globals.G_Action_Orgxfr, transfer_organization, organization_id)= p_organization_id
			 and inventory_item_id = p_inventory_item_id
			 and (revision = p_revision
			      OR revision is null and p_revision is null)
			 AND lot_number is null
			 AND transfer_subinventory = p_subinventory_code
			 AND transfer_to_location = p_locator_id
			 AND transfer_cost_group_id is not null
			 AND transfer_cost_group_id <> p_cost_group_id
			 AND transaction_action_id in (inv_globals.G_Action_Subxfr,
						       inv_globals.G_Action_Orgxfr,
						       inv_globals.G_Action_Stgxfr)
			 AND posting_flag = 'Y');

Line: 362

			    SELECT 'Y' INTO l_mmtt_transfers_exist
			      FROM dual
			      WHERE
			      exists
			      (SELECT mmtt.organization_id --Bug 4496965
			       --BUG 2921882
			       --Changing the count(*) to existence for performance improvement
			       --select
			       --count(*)
			       --into l_mmtt_transfers_count
			       from mtl_material_transactions_temp mmtt,
			       mtl_transaction_lots_temp mtlt
			       where decode(transaction_action_id, inv_globals.G_Action_Orgxfr, transfer_organization, mmtt.organization_id)= p_organization_id
			       AND mmtt.inventory_item_id = p_inventory_item_id
			       AND (mmtt.revision = p_revision
				    OR mmtt.revision is null and p_revision is null)
			       AND (mtlt.lot_number = p_lot_number
				    AND mtlt.transaction_temp_id = mmtt.transaction_temp_id)
			       AND mmtt.transfer_subinventory = p_subinventory_code
			       AND mmtt.transfer_to_location IS null
			       AND mmtt.transfer_cost_group_id is not null
			       AND mmtt.transfer_cost_group_id <> p_cost_group_id
			       AND transaction_action_id in (inv_globals.G_Action_Subxfr,
							       inv_globals.G_Action_Orgxfr,
							     inv_globals.G_Action_Stgxfr)
				 AND posting_flag = 'Y');

Line: 394

				  SELECT 'Y' INTO l_mmtt_transfers_exist
				    FROM dual
				    WHERE
				    exists
				    (SELECT mmtt.organization_id --Bug 4496965
				     --BUG 2921882
				     --Changing the count(*) to existence for performance improvement
				     --select
				     --count(*)
				     --into l_mmtt_transfers_count
				     from mtl_material_transactions_temp mmtt,
				     mtl_transaction_lots_temp mtlt
				     where decode(transaction_action_id, inv_globals.G_Action_Orgxfr, transfer_organization, mmtt.organization_id)= p_organization_id
				     AND mmtt.inventory_item_id = p_inventory_item_id
				     AND (mmtt.revision = p_revision
					  OR mmtt.revision is null and p_revision is null)
				     AND (mtlt.lot_number = p_lot_number
					  AND mtlt.transaction_temp_id = mmtt.transaction_temp_id)
				     AND mmtt.transfer_subinventory = p_subinventory_code
				     AND mmtt.transfer_to_location = p_locator_id
				     AND mmtt.transfer_cost_group_id is not null
				     AND mmtt.transfer_cost_group_id <> p_cost_group_id
				     AND transaction_action_id in (inv_globals.G_Action_Subxfr,
								   inv_globals.G_Action_Orgxfr,
								   inv_globals.G_Action_Stgxfr)
				       AND posting_flag = 'Y');

Line: 439

			SELECT 'Y' INTO l_lpn_contents_exist
			  FROM dual
			  WHERE
			  exists
			  (SELECT organization_id
			   --BUG 2921882
			   --Changing the count(*) to existence for performance improvement
			   --select
			   --count(*)
			   --into l_lpn_contents_count
			   from wms_lpn_contents
			   where organization_id = p_organization_id
			   AND inventory_item_id  = p_inventory_item_id
			   AND (revision = p_revision
				OR revision is null and p_revision is null)
			   AND (lot_number = p_lot_number
				OR lot_number is null and p_lot_number is null)
			   AND cost_group_id is not null
			   AND cost_group_id <> p_cost_group_id
			   AND parent_lpn_id = p_lpn_id);

Line: 480

			     SELECT 'Y' INTO l_mmtt_lpn_receipts_exist
			       FROM dual
			       WHERE
			       exists
			       (SELECT organization_id
				--BUG 2921882
				--Changing the count(*) to existence for performance improvement
				--select
				--count(*)
				--into l_mmtt_lpn_receipts_count
				from mtl_material_transactions_temp
				where organization_id = p_organization_id
				AND inventory_item_id = p_inventory_item_id
				AND (revision = p_revision
				     OR revision is null and p_revision is null)
				AND lot_number is null
				and subinventory_code = p_subinventory_code
				and (locator_id = p_locator_id
				     OR locator_id is null and  p_locator_id is null)
				AND cost_group_id is not null
				AND cost_group_id <> p_cost_group_id
				AND posting_flag = 'Y'
				AND transfer_lpn_id is not null
				AND transfer_lpn_id = p_lpn_id);

Line: 511

				   SELECT 'Y' INTO l_mmtt_lpn_receipts_exist
				     FROM dual
				     WHERE
				     exists
				     (SELECT mmtt.organization_id --Bug 4496965
				      --BUG 2921882
				      --Changing the count(*) to existence for performance improvement
				      --select
				      --count(*)
				      --into
				      --l_mmtt_lpn_receipts_count
				      from mtl_material_transactions_temp mmtt,
				      mtl_transaction_lots_temp mtlt
				      where mmtt.organization_id = p_organization_id
				      AND mmtt.inventory_item_id = p_inventory_item_id
				      AND (mmtt.revision = p_revision
					   OR mmtt.revision is null and p_revision is null)
				      AND (mtlt.lot_number = p_lot_number
					   AND mtlt.transaction_temp_id = mmtt.transaction_temp_id)
				      AND mmtt.subinventory_code = p_subinventory_code
				      AND (mmtt.locator_id = p_locator_id
					   OR mmtt.locator_id is null and p_locator_id is null)
				      AND mmtt.cost_group_id is not null
				      AND mmtt.cost_group_id <> p_cost_group_id
				      AND mmtt.posting_flag = 'Y'
				      AND mmtt.transfer_lpn_id is not null
				      AND mmtt.transfer_lpn_id = p_lpn_id);

Line: 592

	SELECT * FROM
	  mtl_material_transactions_temp
	  WHERE
	  transaction_temp_id = p_transaction_temp_id;

Line: 642

	SELECT
	  mtlt.lot_number lot
	  FROM
	  mtl_transaction_lots_temp mtlt
	  WHERE mtlt.transaction_temp_id = p_mmtt_rec.transaction_temp_id;

Line: 683

      SELECT 'Y' INTO l_serials_exist
	FROM dual
	WHERE exists
	( SELECT 1
	  FROM mtl_serial_numbers_temp
	  WHERE transaction_temp_id = p_mmtt_rec.transaction_temp_id);

Line: 692

	    SELECT 'Y' INTO l_serials_exist
	      FROM dual
	      WHERE exists
	      (SELECT msnt.transaction_temp_id
	       FROM
	       mtl_serial_numbers_temp msnt,
	       mtl_transaction_lots_temp mtlt
	       WHERE mtlt.transaction_temp_id = p_mmtt_rec.transaction_temp_id
	       AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id);

Line: 756

	    SELECT
	      Nvl(lpn_controlled_flag,2)
	      INTO
	      l_lpn_controlled_flag
	      FROM
	      mtl_secondary_inventories
	      WHERE
	      secondary_inventory_name = l_comingle_sub
	      AND organization_id = l_comingle_org;