1 PACKAGE BODY BOMPCOAN AS
2 /* $Header: BOMCOANB.pls 120.4.12020000.3 2012/07/20 07:08:02 nlingamp ship $ */
3 /*==========================================================================+
4 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
5 | All rights reserved. |
6 +===========================================================================+
7 | |
8 | File Name : BOMCOANB.pls |
9 | DESCRIPTION : |
10 | This file creates a packaged procedure that performs ECO |
11 | autonumbering. When passed a user id and an organization |
12 | id, it searches for a valid ECO autonumber prefix and next |
13 | available number, searching in the following order: |
14 | 1 - specific user, specific organization |
15 | 2 - specific user, across all organizations |
16 | 3 - specific organization, across all users |
17 | 4 - across all users and all organizations |
18 | INPUTS : P_USER_ID - user id |
19 | P_ORGANIZATION_ID - organization id |
20 | P_MODE - indicates whether or not to update next |
21 | available number in the ECO autonumber table |
22 | |
23 +==========================================================================*/
24
25
26
27 --Begin bug fix 9234014
28 PROCEDURE Check_Next_AutoNum(p_user_id IN NUMBER
29 , p_organization_id IN NUMBER
30 , p_change_notice IN VARCHAR2
31 , x_return_status IN OUT NOCOPY VARCHAR2)
32
33 IS
34 p_next_number ENG_AUTO_NUMBER_ECN.NEXT_AVAILABLE_NUMBER%TYPE;
35 p_prefix_temp VARCHAR2(12);
36 p_prefix_temp1 VARCHAR2(12);
37 p_greatest_num NUMBER;
38 l_return_status VARCHAR2(1);
39 p_temp_number NUMBER;
40 no_prefix_found EXCEPTION;
41
42 CURSOR PREFIX_CURSOR (c_user_id IN NUMBER,
43 c_org_id IN NUMBER,
44 c_prefix IN VARCHAR2) IS
45 SELECT
46 alpha_prefix,
47 next_available_number
48 FROM eng_auto_number_ecn
49 WHERE nvl(organization_id, c_org_id) = c_org_id
50 AND nvl(user_id, c_user_id) = c_user_id
51 AND alpha_prefix = c_prefix
52 AND change_type_id IS NULL
53 ORDER BY user_id, organization_id;
54
55
56 BEGIN
57
58
59
60 P_PREFIX_TEMP1 := RTRIM(P_CHANGE_NOTICE,'0123456789');
61
62 IF P_PREFIX_TEMP1 = P_CHANGE_NOTICE
63 THEN
64 RAISE no_prefix_found;
65 ELSE
66 OPEN PREFIX_CURSOR(P_USER_ID, P_ORGANIZATION_ID, P_PREFIX_TEMP1);
67
68 FETCH PREFIX_CURSOR INTO
69 p_prefix_temp,
70 p_next_number;
71
72 /* If no prefix is found, raise an exception and return to calling routine */
73
74 IF PREFIX_CURSOR%NOTFOUND
75 THEN RAISE no_prefix_found;
76 END IF;
77 CLOSE PREFIX_CURSOR;
78
79 END IF;
80
81 /* Find the chosen prefix and next number combination */
82 --p_temp_num is from change_notice
83 P_TEMP_NUMBER := GREATEST(TO_NUMBER(LTRIM(P_CHANGE_NOTICE,P_PREFIX_TEMP)))+1;
84
85 P_NEXT_NUMBER := GREATEST(P_NEXT_NUMBER, P_TEMP_NUMBER);
86
87 SELECT GREATEST(P_NEXT_NUMBER,
88 NVL(GREATEST(TO_NUMBER(LTRIM(CHANGE_NOTICE,P_PREFIX_TEMP))),0)+1)
89 INTO P_GREATEST_NUM
90 FROM ENG_ENGINEERING_CHANGES
91 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
92 AND RTRIM(CHANGE_NOTICE,'0123456789') = P_PREFIX_TEMP;
93
94 P_NEXT_NUMBER := GREATEST(P_GREATEST_NUM, P_NEXT_NUMBER);
95
96 /* Ensure that the length of the entire string is less than or equal to 10,
97 the maximum length for ECO name */
98
99 if LENGTHB(P_PREFIX_TEMP || P_NEXT_NUMBER) <= 10
100 then
101 l_return_status := 'S';
102 else
103 l_return_status := 'F';
104 end if;
105 x_return_status := l_return_status;
106
107
108 EXCEPTION
109 WHEN no_prefix_found THEN
110 l_return_status := 'S';
111 x_return_status := l_return_status;
112
113 WHEN no_data_found THEN
114 if LENGTHB(P_PREFIX_TEMP || P_NEXT_NUMBER) <= 10
115 then
116 l_return_status := 'S';
117 else
118 l_return_status := 'F';
119 end if;
120 x_return_status := l_return_status;
121 WHEN others THEN
122 l_return_status := 'S';
123 x_return_status := l_return_status;
124
125 END Check_Next_AutoNum;
126
127 --Eng bug fix 9234014
128
129
130 PROCEDURE BOM_ECO_AUTONUMBER
131 (P_USER_ID IN NUMBER,
132 P_ORGANIZATION_ID IN NUMBER,
133 P_MODE IN NUMBER,
134 P_PREFIX IN OUT NOCOPY VARCHAR2,
135 x_return_status IN OUT NOCOPY VARCHAR2)
136 IS
137
138 p_next_number ENG_AUTO_NUMBER_ECN.NEXT_AVAILABLE_NUMBER%TYPE;
139 p_prefix_temp VARCHAR2(12);
140 p_output_user_id NUMBER;
141 p_output_org_id NUMBER;
142 p_greatest_num NUMBER;
143 l_rowid VARCHAR2(102);
144 p_next_num_temp number; --added for bug 9234014
145
146 no_prefix_found EXCEPTION;
147 next_eco_invalid EXCEPTION; --added for bug 9234014
148 /* Added row_id to the cursor */
149 CURSOR PREFIX_CURSOR (c_user_id IN NUMBER,
150 c_org_id IN NUMBER) IS
151 SELECT
152 rowid ,
153 alpha_prefix,
154 next_available_number,
155 user_id,
156 organization_id
157 FROM eng_auto_number_ecn
158 WHERE nvl(organization_id, c_org_id) = c_org_id
159 AND nvl(user_id, c_user_id) = c_user_id
160 AND change_type_id IS NULL --* Added for bug #3959772
161 ORDER BY user_id, organization_id
162 FOR UPDATE;
163
164
165 BEGIN
166
167 /* P_MODE = 1 --> update ENG_AUTO_NUMBER_ECN to show next available
168 number
169 */
170
171 OPEN PREFIX_CURSOR(P_USER_ID, P_ORGANIZATION_ID);
172
173 FETCH PREFIX_CURSOR INTO
174 l_rowid ,
175 p_prefix_temp,
176 p_next_number,
177 p_output_user_id,
178 p_output_org_id;
179
180 /* If no prefix is found, raise an exception and return to calling routine */
181
182 IF PREFIX_CURSOR%NOTFOUND
183 THEN RAISE no_prefix_found;
184 END IF;
185
186 CLOSE PREFIX_CURSOR;
187
188 /* Ensure that chosen prefix and next number has not already been used */
189
190 SELECT GREATEST(P_NEXT_NUMBER,
191 NVL(MAX(TO_NUMBER(LTRIM(CHANGE_NOTICE,P_PREFIX_TEMP))),0)+1)
192 INTO P_GREATEST_NUM
193 FROM ENG_ENG_CHANGES_INTERFACE
194 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
195 AND RTRIM(CHANGE_NOTICE,'0123456789') = P_PREFIX_TEMP;
196
197 P_NEXT_NUMBER := GREATEST(P_GREATEST_NUM, P_NEXT_NUMBER);
198
199 SELECT GREATEST(P_NEXT_NUMBER,
200 NVL(MAX(TO_NUMBER(LTRIM(CHANGE_NOTICE,P_PREFIX_TEMP))),0)+1)
201 INTO P_GREATEST_NUM
202 FROM ENG_ENGINEERING_CHANGES
203 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
204 AND RTRIM(CHANGE_NOTICE,'0123456789') = P_PREFIX_TEMP;
205
206 P_NEXT_NUMBER := GREATEST(P_GREATEST_NUM, P_NEXT_NUMBER);
207
208 /* Ensure that the length of the entire string is less than or equal to 10,
209 the maximum length for ECO name */
210
211
212 if LENGTHB(P_PREFIX_TEMP || P_NEXT_NUMBER) <= 10
213 then
214 p_next_num_temp := P_NEXT_NUMBER + 1;
215 --check if the next available number would be greater than > 10 digits then fail this update and raise exception
216 --added for bug 9234014 (begin)
217 if LENGTHB(P_PREFIX_TEMP || p_next_num_temp) > 10 THEN
218 RAISE next_eco_invalid;
219 --added for bug 9234014 (end)
220 else
221
222 if P_MODE = 1 -- Only update the autonumber table if P_MODE is 1
223 then
224 --Added rowd_id ,as for case 4 ,the update statement updated all the records ,which is not desirable
225 UPDATE ENG_AUTO_NUMBER_ECN
226 SET NEXT_AVAILABLE_NUMBER = P_NEXT_NUMBER+1
227 WHERE NVL(ORGANIZATION_ID, -999) = NVL(P_OUTPUT_ORG_ID, -999)
228 AND NVL(USER_ID, -999) = NVL(P_OUTPUT_USER_ID, -999)
229 AND rowid =l_rowid;
230 end if;
231 end if;
232 P_PREFIX := P_PREFIX_TEMP || P_NEXT_NUMBER;
233 x_return_status := 'S'; --added for bug 9234014
234 else RAISE no_prefix_found;
235
236 end if;
237
238 EXCEPTION
239 WHEN no_prefix_found THEN --added for bug 9234014 (begin)
240 P_PREFIX := null;
241 x_return_status := 'F';
242
243 WHEN next_eco_invalid
244 THEN
245 P_PREFIX := P_PREFIX_TEMP || P_NEXT_NUMBER;
246 x_return_status := 'F';
247
248 WHEN others THEN
249 P_PREFIX := null;
250 x_return_status := 'F'; --added for bug 9234014 (end)
251
252 END BOM_ECO_AUTONUMBER;
253
254 END BOMPCOAN;
[Home] [Help]