eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.INV_ITEM_SUB_DEFAULT_PKG SQL Statements

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

Line: 3


 PROCEDURE INSERT_UPD_ITEM_SUB_DEFAULTS (
     x_return_status         OUT NOCOPY VARCHAR2
   , x_msg_count             OUT NOCOPY NUMBER
   , x_msg_data              OUT NOCOPY VARCHAR2
   , p_organization_id       IN  NUMBER
   , p_inventory_item_id     IN  NUMBER
   , p_subinventory_code     IN  VARCHAR2
   , p_default_type          IN  NUMBER
   , p_creation_date         IN  DATE
   , p_created_by            IN  NUMBER
   , p_last_update_date      IN  DATE
   , p_last_updated_by       IN  NUMBER
   , p_process_code          IN  VARCHAR2
   , p_commit                IN  VARCHAR2 ) IS

   l_chk_rec_exists NUMBER;

Line: 24


         INV_TRX_UTIL_PUB.TRACE('Beginning of the program .The input parameters are :','INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);

Line: 25


         INV_TRX_UTIL_PUB.TRACE('p_organization_id :'||p_organization_id,'INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);

Line: 26


         INV_TRX_UTIL_PUB.TRACE('p_inventory_item_id :'||p_inventory_item_id,'INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);

Line: 27


         INV_TRX_UTIL_PUB.TRACE('p_subinventory_code :'||p_subinventory_code,'INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);

Line: 28


         INV_TRX_UTIL_PUB.TRACE('p_default_type :'||p_default_type,'INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);

Line: 29


         INV_TRX_UTIL_PUB.TRACE('p_creation_date :'||p_creation_date,'INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);

Line: 30


         INV_TRX_UTIL_PUB.TRACE('p_created_by :'||p_created_by,'INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);

Line: 31


         INV_TRX_UTIL_PUB.TRACE('p_last_update_date :'||p_last_update_date,'INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);

Line: 32


         INV_TRX_UTIL_PUB.TRACE('p_last_updated_by :'||p_last_updated_by,'INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);

Line: 33


         INV_TRX_UTIL_PUB.TRACE('p_process_code :'||p_process_code,'INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);

Line: 34


         INV_TRX_UTIL_PUB.TRACE('p_commit :'||p_commit,'INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);

Line: 46


         p_last_update_date IS NULL OR
         p_process_code IS NULL OR
         p_last_updated_by IS NULL )
         THEN
         IF (l_debug = 1) THEN
            INV_TRX_UTIL_PUB.TRACE('One or more input parameter/s is/are  null :','INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);

Line: 62


            INV_TRX_UTIL_PUB.TRACE('p_default_type is not in 1, 2,3 ','INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);

Line: 70


      IF p_process_code NOT IN ('INSERT','UPDATE','SYNC') THEN
      IF (l_debug = 1) THEN
         INV_TRX_UTIL_PUB.TRACE('Invalid value for p_process_code','INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);

Line: 77


         /*Invalid value for the PROCESS CODE.The value should be either insert update or sync*/
      END IF;

Line: 82


	 SELECT 'X' INTO l_subinv_code
	   FROM mtl_secondary_inventories
	  WHERE secondary_inventory_name = p_subinventory_code
	    AND nvl(disable_date,sysdate+1) > sysdate
	    AND organization_id = p_organization_id;

Line: 95


      IF  UPPER(p_process_code) = 'INSERT' THEN
         IF (l_debug = 1) THEN
            INV_TRX_UTIL_PUB.TRACE('p_process_code IS Insert ','INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);

Line: 99


            INSERT
            INTO mtl_item_sub_defaults (INVENTORY_ITEM_ID
                                        ,ORGANIZATION_ID
                                        ,SUBINVENTORY_CODE
                                        ,DEFAULT_TYPE
                                        ,LAST_UPDATE_DATE
                                        ,LAST_UPDATED_BY
                                        ,CREATION_DATE
                                        ,CREATED_BY
                                        )
            VALUES (p_inventory_item_id
                    ,p_organization_id
                    ,p_subinventory_code
                    ,p_default_type
                    ,p_last_update_date
                    ,p_last_updated_by
                    ,p_creation_date
                    ,p_created_by
                    );

Line: 120


                  INV_TRX_UTIL_PUB.TRACE('Record inserted successfully ','INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);

Line: 123


      ELSIF UPPER(p_process_code) = 'UPDATE' THEN
            IF (l_debug = 1) THEN
               INV_TRX_UTIL_PUB.TRACE('p_process_code IS Update ','INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);

Line: 130


         delete from mtl_item_sub_defaults
	 where inventory_item_id = p_inventory_item_id
	 and organization_id = p_organization_id
	 and default_type = p_default_type;

Line: 135


            UPDATE mtl_item_sub_defaults
            SET  subinventory_code = p_subinventory_code
               , LAST_UPDATE_DATE  = p_last_update_date
               , LAST_UPDATED_BY   = p_last_updated_by
               , CREATION_DATE     = p_creation_date
               , CREATED_BY        = p_created_by
            WHERE inventory_item_id = p_inventory_item_id
             AND organization_id = p_organization_id
             AND default_type    = p_default_type;

Line: 146


             INV_TRX_UTIL_PUB.TRACE('Record Updated successfully','INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);

Line: 153


        Inventory_item_id,Organization_id,Default_type.If it exists, Update the
        record with the new info provided. If the record does not exists, insert
        a new record into MTL_ITEM_SUB_DEFAULTS
       */
           IF (l_debug = 1) THEN
              INV_TRX_UTIL_PUB.TRACE('p_process_code IS Sync ','INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);

Line: 161


                 SELECT 1
                 INTO  l_chk_rec_exists
                 FROM mtl_item_sub_defaults
                 WHERE inventory_item_id = p_inventory_item_id
                 AND organization_id = p_organization_id
                 AND default_type    = p_default_type;

Line: 170


         delete from mtl_item_sub_defaults
	 where inventory_item_id = p_inventory_item_id
	 and organization_id = p_organization_id
	 and default_type = p_default_type;

Line: 175


                 UPDATE mtl_item_sub_defaults
                    SET  subinventory_code = p_subinventory_code
                    , LAST_UPDATE_DATE  = p_last_update_date
                    , LAST_UPDATED_BY   = p_last_updated_by
                    , CREATION_DATE     = p_creation_date
                    , CREATED_BY        = p_created_by
                 WHERE inventory_item_id = p_inventory_item_id
                    AND organization_id = p_organization_id
                    AND default_type    = p_default_type;

Line: 186


                       INV_TRX_UTIL_PUB.TRACE('Record Updated successfully','INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);

Line: 194


                     INV_TRX_UTIL_PUB.TRACE('In no data found. Record does not exists in mtl_item_sub_defaults.Insert record.','INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);

Line: 196


                  INSERT INTO mtl_item_sub_defaults (INVENTORY_ITEM_ID
                                        ,ORGANIZATION_ID
                                        ,SUBINVENTORY_CODE
                                        ,DEFAULT_TYPE
                                        ,LAST_UPDATE_DATE
                                        ,LAST_UPDATED_BY
                                        ,CREATION_DATE
                                        ,CREATED_BY
                                        )
                  VALUES (p_inventory_item_id
                    ,p_organization_id
                    ,p_subinventory_code
                    ,p_default_type
                    ,p_last_update_date
                    ,p_last_updated_by
                    ,p_creation_date
                    ,p_created_by
                    );

Line: 216


                        INV_TRX_UTIL_PUB.TRACE('Record inserted successfully ','INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);

Line: 224


            INV_TRX_UTIL_PUB.TRACE('p_commit is true. Hence commiting the transaction ','INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);

Line: 229


         INV_TRX_UTIL_PUB.TRACE('Program completed successfully','INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);

Line: 235


            INV_TRX_UTIL_PUB.TRACE('In fnd_api.g_exc_error :'||SQLERRM,'INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);

Line: 243


            INV_TRX_UTIL_PUB.TRACE('In FND_API.G_EXC_UNEXPECTED_ERROR :'||SQLERRM,'INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);

Line: 251


            INV_TRX_UTIL_PUB.TRACE('In when others :'||SQLERRM,'INSERT_UPDATE_ITEM_SUB_DEFAULTS',9);

Line: 256


   END INSERT_UPD_ITEM_SUB_DEFAULTS;