APPS.INV_DCP_PVT SQL Statements

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

Line: 109

SELECT name from v$database;

Line: 239

  select SERIAL_NUMBER_CONTROL_CODE
  into   xfer_ser_num_code
  from mtl_system_items
  where organization_id = xfer_org
  and inventory_item_id = inv_item_id;

Line: 378

SELECT user_name, email_address
FROM fnd_user
WHERE user_id = p_user_id;

Line: 383

SELECT rtrim(ltrim(value)) from v$parameter
WHERE lower(name) = 'utl_file_dir';

Line: 387

SELECT name from v$database;

Line: 560

  g_dc_table.delete;

Line: 593

  SELECT *
  FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
  WHERE TRANSACTION_HEADER_ID=trx_hdr_id
  AND TRANSACTION_TEMP_ID = trx_temp_id;

Line: 691

   INV_LOG_UTIL.TRACE('LAST_UPDATED_BY:'||c1_rec.LAST_UPDATED_BY,l_module_name,'9');

Line: 692

   INV_LOG_UTIL.TRACE('LAST_UPDATE_DATE:'||c1_rec.LAST_UPDATE_DATE,l_module_name,'9');

Line: 693

   INV_LOG_UTIL.TRACE('LAST_UPDATE_LOGIN:'||c1_rec.LAST_UPDATE_LOGIN,l_module_name,'9');

Line: 746

   INV_LOG_UTIL.TRACE('PROGRAM_UPDATE_DATE:'||c1_rec.PROGRAM_UPDATE_DATE,l_module_name,'9');

Line: 777

   INV_LOG_UTIL.TRACE('SCHEDULE_UPDATE_CODE:'||c1_rec.SCHEDULE_UPDATE_CODE,l_module_name,'9');

Line: 880

Checks for Java TM at the End - Check if records have been deleted from MMTT, MTI and check Serial Status
                              - Dump data from MSNI, MTLI, MSNI, MTLT
***/
CURSOR C1 is
       SELECT TRANSACTION_TEMP_ID,TRANSACTION_TYPE_ID,
              TRANSACTION_SOURCE_TYPE_ID,TRANSACTION_ACTION_ID,
              ORGANIZATION_ID,TRANSFER_ORGANIZATION,INVENTORY_ITEM_ID
       FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
       WHERE TRANSACTION_HEADER_ID = p_trx_hdr_id
         AND PROCESS_FLAG = 'Y'
         AND ((TRANSACTION_ACTION_ID in (1,2,3,30,31,5)
	 AND inventory_item_id <> -1
         AND EXISTS (
           SELECT 'X'
           FROM MTL_SECONDARY_INVENTORIES MSI
           WHERE MSI.SECONDARY_INVENTORY_NAME = MMTT.SUBINVENTORY_CODE
           AND MSI.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
           AND MSI.QUANTITY_TRACKED = 2))
           OR (TRANSACTION_ACTION_ID = 21
             AND EXISTS (
              SELECT 'X'
                FROM MTL_SECONDARY_INVENTORIES MSI,
                MTL_SYSTEM_ITEMS ITM
                WHERE MSI.SECONDARY_INVENTORY_NAME = MMTT.SUBINVENTORY_CODE
                AND MSI.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
                AND ITM.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
                AND ITM.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
                AND ITM.ORGANIZATION_ID =  MSI.ORGANIZATION_ID
                AND ITM.INVENTORY_ASSET_FLAG = 'Y'
                AND MSI.ASSET_INVENTORY = 2)));

Line: 913

       SELECT TRANSACTION_TEMP_ID,TRANSACTION_TYPE_ID,
              TRANSACTION_SOURCE_TYPE_ID,TRANSACTION_ACTION_ID,
              ORGANIZATION_ID,TRANSFER_ORGANIZATION,INVENTORY_ITEM_ID
       FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
       WHERE TRANSACTION_HEADER_ID = p_trx_hdr_id
         AND PROCESS_FLAG = 'Y'
         AND TRANSACTION_ACTION_ID in (2,5)
	 AND inventory_item_id <> -1
         AND EXISTS (
           SELECT 'X'
           FROM MTL_SECONDARY_INVENTORIES MSI,
           MTL_SYSTEM_ITEMS ITM
           WHERE MSI.SECONDARY_INVENTORY_NAME = MMTT.SUBINVENTORY_CODE
             AND MSI.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
             AND ITM.ORGANIZATION_ID = MSI.ORGANIZATION_ID
             AND ITM.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
             AND ITM.INVENTORY_ASSET_FLAG = 'Y'
             AND MSI.ASSET_INVENTORY = 2)
             AND EXISTS (
               SELECT 'X'
                 FROM MTL_SECONDARY_INVENTORIES MSI,
                 MTL_SYSTEM_ITEMS ITM
                 WHERE MSI.SECONDARY_INVENTORY_NAME = MMTT.TRANSFER_SUBINVENTORY
                   AND MSI.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
                   AND ITM.ORGANIZATION_ID = MSI.ORGANIZATION_ID
                   AND ITM.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
                   AND ITM.INVENTORY_ASSET_FLAG = 'Y'
                   AND MSI.ASSET_INVENTORY = 1);

Line: 943

       SELECT TRANSACTION_TEMP_ID,TRANSACTION_TYPE_ID,
              TRANSACTION_SOURCE_TYPE_ID,TRANSACTION_ACTION_ID,
              ORGANIZATION_ID,TRANSFER_ORGANIZATION,INVENTORY_ITEM_ID
       FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
       WHERE TRANSACTION_HEADER_ID = p_trx_hdr_id
         AND PROCESS_FLAG = 'Y'
         AND TRANSACTION_ACTION_ID = 3
	 AND inventory_item_id <> -1
         AND EXISTS (
           SELECT 'X'
           FROM MTL_SECONDARY_INVENTORIES MSI
           WHERE MSI.SECONDARY_INVENTORY_NAME = MMTT.SUBINVENTORY_CODE
             AND MSI.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
             AND MSI.ASSET_INVENTORY = 2)
             AND EXISTS (
               SELECT 'X'
               FROM MTL_SECONDARY_INVENTORIES MSI
               WHERE MSI.SECONDARY_INVENTORY_NAME = MMTT.TRANSFER_SUBINVENTORY
                AND MSI.ORGANIZATION_ID = MMTT.TRANSFER_ORGANIZATION
                AND MSI.ASSET_INVENTORY = 1);

Line: 965

       SELECT TRANSACTION_TEMP_ID,TRANSACTION_TYPE_ID,
              TRANSACTION_SOURCE_TYPE_ID,TRANSACTION_ACTION_ID,
              ORGANIZATION_ID,TRANSFER_ORGANIZATION,INVENTORY_ITEM_ID
       FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
       WHERE TRANSACTION_HEADER_ID = p_trx_hdr_id
         AND PROCESS_FLAG = 'Y'
	 AND inventory_item_id <> -1
         AND ((TRANSACTION_ACTION_ID in (1,2,3,30,31,5)
         AND EXISTS (
           SELECT 'X'
             FROM MTL_SECONDARY_INVENTORIES MSI
             WHERE MSI.SECONDARY_INVENTORY_NAME = MMTT.SUBINVENTORY_CODE
             AND MSI.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
             AND MSI.QUANTITY_TRACKED = 2)
            )) ;

Line: 981

       SELECT TRANSACTION_TEMP_ID,TRANSACTION_TYPE_ID,
              TRANSACTION_SOURCE_TYPE_ID,TRANSACTION_ACTION_ID,
              ORGANIZATION_ID,TRANSFER_ORGANIZATION,INVENTORY_ITEM_ID
       FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
       WHERE TRANSACTION_HEADER_ID = p_trx_hdr_id
       AND PROCESS_FLAG = 'Y'
       AND LOCATOR_ID IS NOT NULL
       AND transaction_action_id not in (24,30)
       AND inventory_item_id <> -1
       AND EXISTS (
           SELECT 'x'
   	   FROM MTL_PARAMETERS P,MTL_SECONDARY_INVENTORIES S,MTL_SYSTEM_ITEMS I
	   WHERE I.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
	   AND S.SECONDARY_INVENTORY_NAME = MMTT.SUBINVENTORY_CODE
	   AND P.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
	   AND I.ORGANIZATION_ID = S.ORGANIZATION_ID
	   AND P.ORGANIZATION_ID = S.ORGANIZATION_ID
	   AND P.ORGANIZATION_ID = I.ORGANIZATION_ID
	   AND (decode(P.STOCK_LOCATOR_CONTROL_CODE,4, decode(S.LOCATOR_TYPE,5,I.LOCATION_CONTROL_CODE, S.LOCATOR_TYPE) ,P.STOCK_LOCATOR_CONTROL_CODE) <> 1 ))
       AND NOT EXISTS (
           SELECT NULL
           FROM MTL_ITEM_LOCATIONS MIL
           WHERE MIL.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
           AND MIL.SUBINVENTORY_CODE = MMTT.SUBINVENTORY_CODE
           AND MIL.INVENTORY_LOCATION_ID = MMTT.LOCATOR_ID);

Line: 1008

       SELECT TRANSACTION_TEMP_ID,TRANSACTION_TYPE_ID,
              TRANSACTION_SOURCE_TYPE_ID,TRANSACTION_ACTION_ID,
              ORGANIZATION_ID,TRANSFER_ORGANIZATION,INVENTORY_ITEM_ID
       FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
       WHERE TRANSACTION_HEADER_ID = p_trx_hdr_id
       AND PROCESS_FLAG = 'Y'
       AND LOCATOR_ID IS NULL
       AND inventory_item_id <> -1
       AND transaction_action_id not in (24,30)
       AND EXISTS (
           SELECT 'x'
   	   FROM MTL_PARAMETERS P,MTL_SECONDARY_INVENTORIES S,MTL_SYSTEM_ITEMS I
	   WHERE I.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
	   AND S.SECONDARY_INVENTORY_NAME = MMTT.SUBINVENTORY_CODE
	   AND P.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
	   AND I.ORGANIZATION_ID = S.ORGANIZATION_ID
	   AND P.ORGANIZATION_ID = S.ORGANIZATION_ID
	   AND P.ORGANIZATION_ID = I.ORGANIZATION_ID
	   AND (decode(P.STOCK_LOCATOR_CONTROL_CODE,4, decode(S.LOCATOR_TYPE,5,I.LOCATION_CONTROL_CODE, S.LOCATOR_TYPE) ,P.STOCK_LOCATOR_CONTROL_CODE) <> 1 ))
      UNION ALL
       SELECT TRANSACTION_TEMP_ID,TRANSACTION_TYPE_ID,
              TRANSACTION_SOURCE_TYPE_ID,TRANSACTION_ACTION_ID,
              ORGANIZATION_ID,TRANSFER_ORGANIZATION,INVENTORY_ITEM_ID
       FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
       WHERE TRANSACTION_HEADER_ID = p_trx_hdr_id
       AND PROCESS_FLAG = 'Y'
       AND LOCATOR_ID IS NOT NULL
       AND transaction_action_id not in (24,30)
       AND inventory_item_id <> -1
       AND EXISTS (
           SELECT 'x'
   	   FROM MTL_PARAMETERS P,MTL_SECONDARY_INVENTORIES S,MTL_SYSTEM_ITEMS I
	   WHERE I.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
	   AND S.SECONDARY_INVENTORY_NAME = MMTT.SUBINVENTORY_CODE
	   AND P.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
	   AND I.ORGANIZATION_ID = S.ORGANIZATION_ID
	   AND P.ORGANIZATION_ID = S.ORGANIZATION_ID
	   AND P.ORGANIZATION_ID = I.ORGANIZATION_ID
	   AND (decode(P.STOCK_LOCATOR_CONTROL_CODE,4, decode(S.LOCATOR_TYPE,5,I.LOCATION_CONTROL_CODE, S.LOCATOR_TYPE) ,P.STOCK_LOCATOR_CONTROL_CODE) =1));

Line: 1049

       SELECT TRANSACTION_TEMP_ID,TRANSACTION_TYPE_ID,
              TRANSACTION_SOURCE_TYPE_ID,TRANSACTION_ACTION_ID,
              ORGANIZATION_ID,TRANSFER_ORGANIZATION,INVENTORY_ITEM_ID
       FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
       WHERE TRANSACTION_HEADER_ID = p_trx_hdr_id
       AND PROCESS_FLAG = 'Y'
       AND LOCATOR_ID IS NOT NULL
       AND transaction_action_id not in (24,30)
       AND inventory_item_id <> -1
       AND NOT EXISTS (
           SELECT NULL
           FROM MTL_SECONDARY_LOCATORS MSL,
                MTL_SYSTEM_ITEMS MSI
           WHERE MSI.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
             AND MSI.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
             AND MSI.RESTRICT_LOCATORS_CODE = 1
             AND MSL.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
             AND MSL.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
             AND MSL.ORGANIZATION_ID = MSI.ORGANIZATION_ID
             AND MSL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
             AND MSL.SUBINVENTORY_CODE = MMTT.SUBINVENTORY_CODE
             AND MSL.SECONDARY_LOCATOR = MMTT.LOCATOR_ID
           UNION
           SELECT NULL
             FROM MTL_SYSTEM_ITEMS ITM
            WHERE ITM.RESTRICT_LOCATORS_CODE = 2
              AND ITM.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
              AND ITM.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID);

Line: 1079

       SELECT TRANSACTION_TEMP_ID,TRANSACTION_TYPE_ID,
              TRANSACTION_SOURCE_TYPE_ID,TRANSACTION_ACTION_ID,
              ORGANIZATION_ID,TRANSFER_ORGANIZATION,INVENTORY_ITEM_ID
       FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
       WHERE TRANSACTION_HEADER_ID = p_trx_hdr_id
       AND PROCESS_FLAG = 'Y'
       AND TRANSACTION_ACTION_ID IN (2,3,5)
       AND TRANSFER_TO_LOCATION IS NOT NULL
       AND inventory_item_id <> -1
       AND NOT EXISTS (
           SELECT NULL
           FROM MTL_ITEM_LOCATIONS MIL
           WHERE MIL.ORGANIZATION_ID = decode(MMTT.TRANSACTION_ACTION_ID,3,
                 MMTT.TRANSFER_ORGANIZATION,MMTT.ORGANIZATION_ID)
             AND MIL.SUBINVENTORY_CODE = MMTT.TRANSFER_SUBINVENTORY
             AND MIL.INVENTORY_LOCATION_ID = MMTT.TRANSFER_TO_LOCATION
             AND TRUNC(MMTT.TRANSACTION_DATE) <= NVL(MIL.DISABLE_DATE,
                                                    MMTT.TRANSACTION_DATE + 1));

Line: 1099

       SELECT TRANSACTION_TEMP_ID,TRANSACTION_TYPE_ID,
              TRANSACTION_SOURCE_TYPE_ID,TRANSACTION_ACTION_ID,
              ORGANIZATION_ID,TRANSFER_ORGANIZATION,INVENTORY_ITEM_ID
       FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
       WHERE TRANSACTION_HEADER_ID = p_trx_hdr_id
       AND PROCESS_FLAG = 'Y'
       AND TRANSACTION_ACTION_ID in (2,21,3,5)
       AND TRANSFER_TO_LOCATION IS NOT NULL
       AND inventory_item_id <> -1
       AND NOT EXISTS (
           SELECT NULL
           FROM MTL_SECONDARY_LOCATORS MSL,
                MTL_SYSTEM_ITEMS MSI
           WHERE MSI.ORGANIZATION_ID = decode(MMTT.TRANSACTION_ACTION_ID,2,
                MMTT.ORGANIZATION_ID,5,MMTT.ORGANIZATION_ID, MMTT.TRANSFER_ORGANIZATION)
             AND MSI.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
             AND MSI.RESTRICT_LOCATORS_CODE = 1
             AND MSL.ORGANIZATION_ID = decode(MMTT.TRANSACTION_ACTION_ID,2,
                MMTT.ORGANIZATION_ID,5,MMTT.ORGANIZATION_ID, MMTT.TRANSFER_ORGANIZATION)
             AND MSL.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
             AND MSL.ORGANIZATION_ID = MSI.ORGANIZATION_ID
             AND MSL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
             AND MSL.SUBINVENTORY_CODE = MMTT.TRANSFER_SUBINVENTORY
             AND MSL.SECONDARY_LOCATOR = MMTT.TRANSFER_TO_LOCATION
           UNION
           SELECT NULL
           FROM MTL_SYSTEM_ITEMS MSI
           WHERE MSI.ORGANIZATION_ID = decode(MMTT.TRANSACTION_ACTION_ID,2,
                MMTT.ORGANIZATION_ID,5,MMTT.ORGANIZATION_ID,MMTT.TRANSFER_ORGANIZATION)
             AND MSI.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
             AND MSI.RESTRICT_LOCATORS_CODE = 2);

Line: 1132

       SELECT TRANSACTION_TEMP_ID,TRANSACTION_TYPE_ID,
              TRANSACTION_SOURCE_TYPE_ID,TRANSACTION_ACTION_ID,
              ORGANIZATION_ID,TRANSFER_ORGANIZATION,INVENTORY_ITEM_ID
       FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
       WHERE TRANSACTION_HEADER_ID = p_trx_hdr_id
       AND PROCESS_FLAG = 'Y'
       AND TRANSACTION_ACTION_ID NOT IN (24,33,34,30)
       AND inventory_item_id <> -1
       AND NOT EXISTS (
           SELECT NULL
             FROM MTL_ITEM_REVISIONS MIR,
                  MTL_SYSTEM_ITEMS MSI
            WHERE MSI.REVISION_QTY_CONTROL_CODE = 2
              AND MIR.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
              AND MIR.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
              AND MSI.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
              AND MSI.ORGANIZATION_ID = MMTT.ORGANIZATION_ID
              AND MIR.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
              AND MIR.ORGANIZATION_ID = MSI.ORGANIZATION_ID
              AND MIR.REVISION = MMTT.REVISION
             UNION
              SELECT NULL
                FROM MTL_SYSTEM_ITEMS ITM
               WHERE ITM.REVISION_QTY_CONTROL_CODE = 1
                 AND MMTT.REVISION IS NULL
                 AND ITM.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
                 AND ITM.ORGANIZATION_ID = MMTT.ORGANIZATION_ID);

Line: 1162

       SELECT TRANSACTION_TEMP_ID,TRANSACTION_TYPE_ID,
              TRANSACTION_SOURCE_TYPE_ID,TRANSACTION_ACTION_ID,
              ORGANIZATION_ID,TRANSFER_ORGANIZATION,INVENTORY_ITEM_ID
       FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
       WHERE TRANSACTION_HEADER_ID = p_trx_hdr_id
       AND PROCESS_FLAG = 'Y'
       AND TRANSACTION_ACTION_ID = 3
       AND inventory_item_id <> -1
       AND NOT EXISTS (
            SELECT NULL
             FROM MTL_ITEM_REVISIONS MIR,
                  MTL_SYSTEM_ITEMS MSI
            WHERE MSI.REVISION_QTY_CONTROL_CODE = 2
              AND MIR.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
              AND MIR.ORGANIZATION_ID = MMTT.TRANSFER_ORGANIZATION
              AND MIR.REVISION = MMTT.REVISION
              AND MSI.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
              AND MSI.ORGANIZATION_ID = MMTT.TRANSFER_ORGANIZATION
              AND MIR.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
              AND MIR.ORGANIZATION_ID = MSI.ORGANIZATION_ID
            UNION
              SELECT NULL
              FROM MTL_SYSTEM_ITEMS ITM
              WHERE ITM.REVISION_QTY_CONTROL_CODE = 1
              AND ITM.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
              AND ITM.ORGANIZATION_ID = MMTT.TRANSFER_ORGANIZATION);

Line: 1190

         SELECT organization_code
 	 FROM  org_organization_definitions
	 WHERE organization_id = p_org_id;

Line: 1195

  	SELECT TRANSACTION_TYPE_NAME
	FROM mtl_transaction_types
	WHERE TRANSACTION_TYPE_ID=p_trx_type_id;

Line: 1200

  	SELECT transaction_source_type_name
	FROM mtl_txn_source_types
	WHERE transaction_source_type_id=p_source_type_id;

Line: 1205

  	SELECT meaning
	FROM mfg_lookups
	WHERE lookup_code=p_action_id
	AND   lookup_type='MTL_TRANSACTION_ACTION';

Line: 1211

	SELECT concatenated_segments
      	FROM   mtl_system_items_kfv
      	WHERE  inventory_item_id = p_item_id
	AND organization_id = p_org_id;

Line: 1220

       	SELECT a.TRANSACTION_TEMP_ID,a.TRANSACTION_TYPE_ID,a.TRANSACTIOn_QUANTITY,
               a.TRANSACTION_SOURCE_TYPE_ID,a.TRANSACTION_ACTION_ID,
               a.ORGANIZATION_ID,a.TRANSFER_ORGANIZATION,a.INVENTORY_ITEM_ID,
	       A.SUBINVENTORY_CODE,A.LOCATOR_ID,A.REVISION,A.LOT_NUMBER,
	       A.TRANSFER_SUBINVENTORY,a.TRANSFER_TO_LOCATION,
	       b.CURRENT_SUBINVENTORY_CODE,b.CURRENT_LOCATOR_ID,b.REVISION current_revision,
	       b.CURRENT_STATUS,b.SERIAL_NUMBER,b.current_organization_id,d.SERIAL_NUMBER_CONTROL_CODE
       	FROM MTL_MATERIAL_TRANSACTIONS_TEMP a,MTL_SERIAL_NUMBERS b,
             MTL_SERIAL_NUMBERS_TEMP c,MTL_SYSTEM_ITEMS d
       	WHERE A.TRANSACTION_TEMP_ID = C.TRANSACTION_TEMP_ID
	AND   LPAD(B.SERIAL_NUMBER,30) >= LPAD(C.FM_SERIAL_NUMBER,30)
	AND   LPAD(NVL(B.SERIAL_NUMBER,'-99'),30) <= LPAD(NVL(C.TO_SERIAL_NUMBER,'-99'),30)
	AND   A.ORGANIZATION_ID = nvl(B.CURRENT_ORGANIZATION_ID,A.ORGANIZATION_ID)
	AND   A.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
	AND   A.TRANSACTION_HEADER_ID=P_TRX_HDR_ID
	AND   A.TRANSACTION_TEMP_ID=nvl(P_TRX_TEMP_ID,A.TRANSACTION_TEMP_ID)
        AND   A.ORGANIZATION_ID = d.ORGANIZATION_ID
        AND   A.INVENTORY_ITEM_ID = d.INVENTORY_ITEM_ID
       	AND   A.PROCESS_FLAG = 'Y'
        AND   A.INVENTORY_ITEM_ID <> -1
        AND   ((d.SERIAL_NUMBER_CONTROL_CODE =6 and ((a.TRANSACTION_ACTION_ID =1 and a.transaction_source_type_id=2) or (a.transaction_action_id in (3,21) and a.transaction_source_type_id=8)))
              OR d.SERIAL_NUMBER_CONTROL_CODE in (2,5));

Line: 1267

 SELECT FND_PROFILE.VALUE('INV:EXPENSE_TO_ASSET_TRANSFER')
 INTO l_profile
 FROM dual;