APPS.INV_SUB_CG_UTIL SQL Statements

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

Line: 6

** Function:    validate_cg_update
** Description: Checks if cost group can be updated
** Output:
**      x_return_status
**              return status indicating success, error, unexpected error
**      x_msg_count
**              number of messages in message list
**      x_msg_data
**              if the number of messages in message list is 1, contains
**              message text
** Input:
**      p_cost_group_id
**	       cost group for which the check has to be made
**
** Returns:
**      TRUE if cost group can be updated, else FALSE
**
**      Please use return value to determine if cost group can be updated or not.
**      Do not use x_return_status for this purpose as
**      . x_return_status could be success and yet cost group not be updated
**      . x_return_status is set to error when an error(such as SQL error)
**        occurs.
** --------------------------------------------------------------------------
*/
g_pkg_name CONSTANT VARCHAR2(30) := 'INV_SUB_CG_UTIL';

Line: 32

function validate_cg_update (
  x_return_status               OUT NOCOPY VARCHAR2
, x_msg_count                   OUT NOCOPY NUMBER
, x_msg_data                    OUT NOCOPY VARCHAR2
, p_cost_group_id               IN  NUMBER) return boolean
  IS

     /*


      --  cursor moq_cursor (v_cost_group_id number)
      --  is
      --  select count(*)
      --  from MTL_ONHAND_QUANTITIES_DETAIL moq
      --	where moq.cost_group_id = v_cost_group_id;

Line: 60

        select count(*)
        from mtl_material_transactions_temp mmtt
	where mmtt.cost_group_id          = v_cost_group_id;

Line: 182

              , 'validate_cg_update'
              );

Line: 206

end validate_cg_update;

Line: 210

** Function:    validate_cg_delete
** Description: Checks if cost group can be delete
** Output:
**      x_return_status
**              return status indicating success, error, unexpected error
**      x_msg_count
**              number of messages in message list
**      x_msg_data
**              if the number of messages in message list is 1, contains
**              message text
** Input:
**      p_cost_group_id
**	       cost group for which the check has to be made
**
** Returns:
**      TRUE if cost group can be deleted, else FALSE
**
**      Please use return value to determine if cost group can be deleted or not.
**      Do not use x_return_status for this purpose as
**      . x_return_status could be success and yet cost group not be deleted
**      . x_return_status is set to error when an error(such as SQL error)
**        occurs.
** --------------------------------------------------------------------------
*/

function validate_cg_delete (
  x_return_status               OUT NOCOPY VARCHAR2
, x_msg_count                   OUT NOCOPY NUMBER
, x_msg_data                    OUT NOCOPY VARCHAR2
, p_cost_group_id               IN  NUMBER
, p_organization_id             IN NUMBER) return boolean
is

   l_return_status     varchar2(1) := fnd_api.g_ret_sts_success;

Line: 245

   l_cannot_delete     VARCHAR2(1) := 'N';

Line: 257

	 SELECT 'Y' INTO l_cannot_delete FROM dual
	   WHERE
	   exists
	   (SELECT organization_id
	    FROM mtl_parameters mp
	    WHERE mp.default_cost_group_id = p_cost_group_id);

Line: 265

	    l_cannot_delete := 'N';

Line: 268

      IF l_cannot_delete = 'N' THEN
	 BEGIN
	    SELECT 'Y' INTO l_cannot_delete FROM dual
	      WHERE
	      exists
	      (SELECT organization_id
	       FROM mtl_secondary_inventories msi
	       WHERE msi.default_cost_group_id = p_cost_group_id);

Line: 278

	       l_cannot_delete := 'N';

Line: 282

      IF l_cannot_delete = 'N' THEN
         BEGIN
	    SELECT 'Y' INTO l_cannot_delete FROM dual
	      WHERE
	      exists
	      (SELECT organization_id
	       FROM mtl_material_transactions_temp mmtt
	       WHERE
	       (mmtt.cost_group_id  = p_cost_group_id
		OR mmtt.transfer_cost_group_id = p_cost_group_id));

Line: 294

	       l_cannot_delete := 'N';

Line: 298

      IF l_cannot_delete = 'N' THEN
         BEGIN
	    SELECT 'Y' INTO l_cannot_delete FROM dual
	      WHERE
	      exists
	      (SELECT organization_id
	       FROM MTL_ONHAND_QUANTITIES_DETAIL moq
	       WHERE moq.cost_group_id = p_cost_group_id);

Line: 308

	       l_cannot_delete := 'N';

Line: 312

      IF l_cannot_delete = 'N' THEN
	 BEGIN
	    SELECT 'Y' INTO l_cannot_delete FROM dual
	      WHERE
	      exists
	      (SELECT organization_id
	       FROM mtl_material_transactions mmt
	       WHERE
	       (mmt.cost_group_id          = p_cost_group_id
		OR mmt.transfer_cost_group_id = p_cost_group_id));

Line: 324

	       l_cannot_delete := 'N';

Line: 329

	    SELECT 'Y' INTO l_cannot_delete FROM dual
	      WHERE
	      exists
	      (SELECT organization_id
	       FROM mtl_parameters mp
	       WHERE mp.default_cost_group_id = p_cost_group_id
	       AND mp.organization_id = p_organization_id);

Line: 338

	       l_cannot_delete := 'N';

Line: 341

	 IF l_cannot_delete = 'N' THEN
            BEGIN
	       SELECT 'Y' INTO l_cannot_delete FROM dual
		 WHERE
		 exists
		 (select organization_id
		  from mtl_secondary_inventories msi
		  where msi.default_cost_group_id = p_cost_group_id
		  AND msi.organization_id = p_organization_id);

Line: 352

		  l_cannot_delete := 'N';

Line: 356

	 IF l_cannot_delete = 'N' THEN
            BEGIN
	       SELECT 'Y' INTO l_cannot_delete FROM dual
		 WHERE
		 exists
		 (SELECT organization_id
		  FROM mtl_material_transactions_temp mmtt
		  WHERE
		  mmtt.organization_id = p_organization_id AND
		  (mmtt.cost_group_id  = p_cost_group_id
		   OR mmtt.transfer_cost_group_id = p_cost_group_id));

Line: 369

		  l_cannot_delete := 'N';

Line: 373

	 IF l_cannot_delete = 'N' THEN
            BEGIN
	       SELECT 'Y' INTO l_cannot_delete FROM dual
		 WHERE
		 exists
		 (SELECT organization_id
		  FROM MTL_ONHAND_QUANTITIES_DETAIL moq
		  WHERE moq.cost_group_id = p_cost_group_id
		  AND moq.organization_id = p_organization_id);

Line: 384

	    l_cannot_delete := 'N';      END;

Line: 387

	 IF l_cannot_delete = 'N' THEN
            BEGIN
	       SELECT 'Y' INTO l_cannot_delete FROM dual
		 WHERE
		 exists
		 (SELECT organization_id
		  FROM mtl_material_transactions mmt
		  WHERE mmt.organization_id = p_organization_id AND
		  (mmt.cost_group_id          = p_cost_group_id
		   OR mmt.transfer_cost_group_id = p_cost_group_id));

Line: 399

		  l_cannot_delete := 'N';

Line: 404

   IF l_cannot_delete = 'Y' THEN
      RETURN FALSE;

Line: 440

              , 'validate_cg_update'
              );

Line: 450

end validate_cg_delete;

Line: 454

** Procedure:   update_sub_accounts
** Description: Updates a given subinventory with a given cost group's accounts
** Output:
**      x_return_status
**              return status indicating success, error, unexpected error
**      x_msg_count
**              number of messages in message list
**      x_msg_data
**              if the number of messages in message list is 1, contains
**              message text
** Input:
**      p_cost_group_id
**             	cost group whose accounts have to be used to update subinventory
**      p_organization_id
**	       	organization to which the to be subinventory belongs
**      p_subinventory
**		subinventory whose accounts have to be synchronized with those
**		of cost group
**
** Returns:
**	none
** --------------------------------------------------------------------------
*/

procedure update_sub_accounts (
  x_return_status               OUT NOCOPY VARCHAR2
, x_msg_count                   OUT NOCOPY NUMBER
, x_msg_data                    OUT NOCOPY VARCHAR2
, p_cost_group_id               IN  NUMBER
, p_organization_id             IN  NUMBER
, p_subinventory                IN  VARCHAR2)
is
    l_material_account			number;

Line: 545

    update mtl_secondary_inventories
    set
      material_account  	 =  l_material_account
    , material_overhead_account  =  l_material_overhead_account
    , resource_account           =  l_resource_account
    , overhead_account           =  l_overhead_account
    , outside_processing_account =  l_outside_processing_account
    , expense_account            =  l_expense_account
    , encumbrance_account        =  l_encumbrance_account
    where organization_id          = p_organization_id
    and   secondary_inventory_name = p_subinventory;

Line: 588

              , 'update_sub_accounts'
              );

Line: 598

end update_sub_accounts;

Line: 602

** Procedure:   update_org_accounts
** Description: Updates a given organization with a given cost group's accounts
** Output:
**      x_return_status
**              return status indicating success, error, unexpected error
**      x_msg_count
**              number of messages in message list
**      x_msg_data
**              if the number of messages in message list is 1, contains
**              message text
** Input:
**      p_cost_group_id
**             	cost group whose accounts have to be used to update organization
**      p_organization_id
**		organization whose accounts have to be synchronized with those
**		of cost group
**
** Returns:
**	none
** --------------------------------------------------------------------------
*/

procedure update_org_accounts (
  x_return_status               OUT NOCOPY VARCHAR2
, x_msg_count                   OUT NOCOPY NUMBER
, x_msg_data                    OUT NOCOPY VARCHAR2
, p_cost_group_id               IN  NUMBER
, p_organization_id             IN  NUMBER)
is
    l_material_account			number;

Line: 689

    update mtl_parameters
    set
      material_account  	 =  l_material_account
    , material_overhead_account  =  l_material_overhead_account
    , resource_account           =  l_resource_account
    , overhead_account           =  l_overhead_account
    , outside_processing_account =  l_outside_processing_account
    , expense_account            =  l_expense_account
    , encumbrance_account        =  l_encumbrance_account
    where organization_id          = p_organization_id;

Line: 731

              , 'update_org_accounts'
              );

Line: 741

end update_org_accounts;

Line: 781

	select organization_id, secondary_inventory_name
	from mtl_secondary_inventories
	where default_cost_group_id = p_cost_group_id;

Line: 875

	select organization_id
	from mtl_parameters
	where default_cost_group_id = p_cost_group_id;

Line: 964

	select nvl(default_cost_group_id,0)
	into l_cost_group_id
	from mtl_parameters
	where organization_id = p_organization_id;

Line: 1049

	select nvl(default_cost_group_id,0)
	into l_cost_group_id
	from mtl_secondary_inventories
	where organization_id          = p_organization_id
        and   secondary_inventory_name = p_subinventory;

Line: 1101

** Procedure:   find_update_subs_accounts
** Description: For a given cost group, all subinventories that have it as a
**		default cost group are found and their accounts are
**		synchronized with those of the cost group
** Output:
**      x_return_status
**              return status indicating success, error, unexpected error
**      x_msg_count
**              number of messages in message list
**      x_msg_data
**              if the number of messages in message list is 1, contains
**              message text
** Input:
**      p_cost_group_id
**              cost group whose accounts will be used to synchronize with
**              accounts of subinventories that have this cost group as
**		the default cost group
** Returns:
**	none
** --------------------------------------------------------------------------
*/

procedure find_update_subs_accounts(
  x_return_status               OUT NOCOPY VARCHAR2
, x_msg_count                   OUT NOCOPY NUMBER
, x_msg_data                    OUT NOCOPY VARCHAR2
, p_cost_group_id               IN  NUMBER)
is
    l_return_status     varchar2(1) := fnd_api.g_ret_sts_success;

Line: 1166

		inv_sub_cg_util.update_sub_accounts (
		  x_return_status   => l_return_status
		, x_msg_count       => l_msg_count
                , x_msg_data        => l_msg_data
                , p_cost_group_id   => p_cost_group_id
                , p_organization_id => l_sub_tbl(i).organization_id
                , p_subinventory    => l_sub_tbl(i).subinventory);

Line: 1214

              , 'find_update_subs_accounts'
            );

Line: 1224

end find_update_subs_accounts;

Line: 1228

** Procedure:   find_update_orgs_accounts
** Description: For a given cost group, all organziations that have it as a
**              default cost group are found and their accounts are
**              synchronized with those of the cost group
** Output:
**      x_return_status
**              return status indicating success, error, unexpected error
**      x_msg_count
**              number of messages in message list
**      x_msg_data
**              if the number of messages in message list is 1, contains
**              message text
** Input:
**      p_cost_group_id
**              cost group whose accounts will be used to synchronize with
**              accounts of organziations that have this cost group as
**              the default cost group
** Returns:
**      none
** --------------------------------------------------------------------------
*/
procedure find_update_orgs_accounts(
  x_return_status               OUT NOCOPY VARCHAR2
, x_msg_count                   OUT NOCOPY NUMBER
, x_msg_data                    OUT NOCOPY VARCHAR2
, p_cost_group_id               IN  NUMBER)
is
    l_return_status     varchar2(1) := fnd_api.g_ret_sts_success;

Line: 1292

		inv_sub_cg_util.update_org_accounts (
		  x_return_status   => l_return_status
		, x_msg_count       => l_msg_count
                , x_msg_data        => l_msg_data
                , p_cost_group_id   => p_cost_group_id
                , p_organization_id => l_org_tbl(i).organization_id);

Line: 1339

              , 'find_update_orgs_accounts'
            );

Line: 1349

end find_update_orgs_accounts;