1 PACKAGE BODY BOMPCOAN AS
2 /* $Header: BOMCOANB.pls 120.2 2005/09/28 04:46:11 earumuga 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 PROCEDURE BOM_ECO_AUTONUMBER
25 (P_USER_ID IN NUMBER,
26 P_ORGANIZATION_ID IN NUMBER,
27 P_MODE IN NUMBER,
28 P_PREFIX IN OUT NOCOPY VARCHAR2)
29 IS
30
31 p_next_number ENG_AUTO_NUMBER_ECN.NEXT_AVAILABLE_NUMBER%TYPE;
32 p_prefix_temp VARCHAR2(12);
33 p_output_user_id NUMBER;
34 p_output_org_id NUMBER;
35 p_greatest_num NUMBER;
36 l_rowid VARCHAR2(102);
37
38 no_prefix_found EXCEPTION;
39 /* Added row_id to the cursor */
40 CURSOR PREFIX_CURSOR (c_user_id IN NUMBER,
41 c_org_id IN NUMBER) IS
42 SELECT
43 rowid ,
44 alpha_prefix,
45 next_available_number,
46 user_id,
47 organization_id
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 change_type_id IS NULL --* Added for bug #3959772
52 ORDER BY user_id, organization_id
53 FOR UPDATE;
54
55
56 BEGIN
57
58 /* P_MODE = 1 --> update ENG_AUTO_NUMBER_ECN to show next available
59 number
60 */
61
62 OPEN PREFIX_CURSOR(P_USER_ID, P_ORGANIZATION_ID);
63
64 FETCH PREFIX_CURSOR INTO
65 l_rowid ,
66 p_prefix_temp,
67 p_next_number,
68 p_output_user_id,
69 p_output_org_id;
70
71 /* If no prefix is found, raise an exception and return to calling routine */
72
73 IF PREFIX_CURSOR%NOTFOUND
74 THEN RAISE no_prefix_found;
75 END IF;
76
77 CLOSE PREFIX_CURSOR;
78
79 /* Ensure that chosen prefix and next number has not already been used */
80
81 SELECT GREATEST(P_NEXT_NUMBER,
82 NVL(MAX(TO_NUMBER(LTRIM(CHANGE_NOTICE,P_PREFIX_TEMP))),0)+1)
83 INTO P_GREATEST_NUM
84 FROM ENG_ENG_CHANGES_INTERFACE
85 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
86 AND RTRIM(CHANGE_NOTICE,'0123456789') = P_PREFIX_TEMP;
87
88 P_NEXT_NUMBER := GREATEST(P_GREATEST_NUM, P_NEXT_NUMBER);
89
90 SELECT GREATEST(P_NEXT_NUMBER,
91 NVL(MAX(TO_NUMBER(LTRIM(CHANGE_NOTICE,P_PREFIX_TEMP))),0)+1)
92 INTO P_GREATEST_NUM
93 FROM ENG_ENGINEERING_CHANGES
94 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
95 AND RTRIM(CHANGE_NOTICE,'0123456789') = P_PREFIX_TEMP;
96
97 P_NEXT_NUMBER := GREATEST(P_GREATEST_NUM, P_NEXT_NUMBER);
98
99 /* Ensure that the length of the entire string is less than or equal to 10,
100 the maximum length for ECO name */
101
102
103 if LENGTHB(P_PREFIX_TEMP || P_NEXT_NUMBER) <= 10
104 then
105 if P_MODE = 1 -- Only update the autonumber table if P_MODE is 1
106 then
107 --Added rowd_id ,as for case 4 ,the update statement updated all the records ,which is not desirable
108 UPDATE ENG_AUTO_NUMBER_ECN
109 SET NEXT_AVAILABLE_NUMBER = P_NEXT_NUMBER+1
110 WHERE NVL(ORGANIZATION_ID, -999) = NVL(P_OUTPUT_ORG_ID, -999)
111 AND NVL(USER_ID, -999) = NVL(P_OUTPUT_USER_ID, -999)
112 AND rowid =l_rowid;
113
114
115 end if;
116
117 P_PREFIX := P_PREFIX_TEMP || P_NEXT_NUMBER;
118
119 else RAISE no_prefix_found;
120
121 end if;
122
123 EXCEPTION
124 WHEN no_prefix_found THEN null;
125 WHEN others THEN null;
126
127 END BOM_ECO_AUTONUMBER;
128
129 END BOMPCOAN;
[Home] [Help]