APPS.BOMPCOAN SQL Statements

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

Line: 20

|                 P_MODE - indicates whether or not to update next          |
|                          available number in the ECO autonumber table     |
|                                                                           |
+==========================================================================*/
PROCEDURE BOM_ECO_AUTONUMBER
   (P_USER_ID 			IN	NUMBER,
    P_ORGANIZATION_ID		IN	NUMBER,
    P_MODE 			IN      NUMBER,
    P_PREFIX			IN OUT NOCOPY  VARCHAR2)
IS

    p_next_number       ENG_AUTO_NUMBER_ECN.NEXT_AVAILABLE_NUMBER%TYPE;

Line: 42

                 SELECT
		          rowid ,
			  alpha_prefix,
                          next_available_number,
                          user_id,
                          organization_id
                 FROM     eng_auto_number_ecn
                 WHERE    nvl(organization_id, c_org_id) = c_org_id
                 AND      nvl(user_id, c_user_id) = c_user_id
		 AND      change_type_id IS NULL --* Added for bug #3959772
                 ORDER BY user_id, organization_id
		 FOR UPDATE;

Line: 58

/* P_MODE = 1 --> update ENG_AUTO_NUMBER_ECN to show next available
                  number
*/

OPEN PREFIX_CURSOR(P_USER_ID, P_ORGANIZATION_ID);

Line: 81

SELECT GREATEST(P_NEXT_NUMBER,
       NVL(MAX(TO_NUMBER(LTRIM(CHANGE_NOTICE,P_PREFIX_TEMP))),0)+1)
INTO   P_GREATEST_NUM
FROM   ENG_ENG_CHANGES_INTERFACE
WHERE  ORGANIZATION_ID = P_ORGANIZATION_ID
AND    RTRIM(CHANGE_NOTICE,'0123456789') = P_PREFIX_TEMP;

Line: 90

SELECT GREATEST(P_NEXT_NUMBER,
       NVL(MAX(TO_NUMBER(LTRIM(CHANGE_NOTICE,P_PREFIX_TEMP))),0)+1)
INTO   P_GREATEST_NUM
FROM   ENG_ENGINEERING_CHANGES
WHERE  ORGANIZATION_ID = P_ORGANIZATION_ID
AND    RTRIM(CHANGE_NOTICE,'0123456789') = P_PREFIX_TEMP;

Line: 105

   if P_MODE = 1   -- Only update the autonumber table if P_MODE is 1
   then
   --Added rowd_id ,as for case 4 ,the update statement updated all the records ,which is not desirable
   UPDATE ENG_AUTO_NUMBER_ECN
   SET NEXT_AVAILABLE_NUMBER = P_NEXT_NUMBER+1
   WHERE NVL(ORGANIZATION_ID, -999) = NVL(P_OUTPUT_ORG_ID, -999)
   AND   NVL(USER_ID, -999) = NVL(P_OUTPUT_USER_ID, -999)
   AND   rowid =l_rowid;