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     |
|                                                                           |
+==========================================================================*/



--Begin  bug fix 9234014
PROCEDURE Check_Next_AutoNum(p_user_id  IN NUMBER
   , p_organization_id  IN NUMBER
   , p_change_notice IN VARCHAR2
   , x_return_status IN OUT NOCOPY VARCHAR2)

IS
    p_next_number       ENG_AUTO_NUMBER_ECN.NEXT_AVAILABLE_NUMBER%TYPE;

Line: 45

                 SELECT
                          alpha_prefix,
                          next_available_number
                 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      alpha_prefix = c_prefix
		             AND      change_type_id IS NULL
                 ORDER BY user_id, organization_id;

Line: 87

SELECT GREATEST(P_NEXT_NUMBER,
       NVL(GREATEST(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: 151

                 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: 167

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

OPEN PREFIX_CURSOR(P_USER_ID, P_ORGANIZATION_ID);

Line: 190

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: 199

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: 222

   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;