1 PACKAGE BODY PA_UTILS_SQNUM_PKG as
2 /* $Header: PAXGSQNB.pls 120.1.12010000.2 2008/09/27 09:28:01 bifernan ship $ */
3
4 /*----------------------------------------------------------+
5 | get_unique_proj_num : a procedure to get a unique number |
6 | for the automatic project number. |
7 | |
8 | unique_number : contains the returned unique number. |
9 | x_status : contains the returned status. |
10 | |
11 | x_status = 0 if it is successful. |
12 | = 1 if no data found. |
13 | = sqlcode otherwise |
14 | Bug fix : 438413 - Next number should actually be the |
15 | next number that is to be used, not the last|
16 | number used, as is currently stored in the |
17 | table. The next number will now be stored. |
18 | tsaifee 01/24/97 |
19 +----------------------------------------------------------*/
20 PROCEDURE get_unique_proj_num(x_table_name IN VARCHAR2,
21 user_id IN NUMBER,
22 unique_number IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
23 x_status IN OUT NOCOPY NUMBER) is --File.Sql.39 bug 4440895
24 uniqueid NUMBER;
25 -- Commented by Sachin. Bug 3517177
26 -- PL_DUMMY PA_UNIQUE_IDENTIFIER_CONTROL.NEXT_UNIQUE_IDENTIFIER%TYPE;
27 PRAGMA AUTONOMOUS_TRANSACTION; -- Added by Sachin. Bug 3517177
28
29 BEGIN
30
31 x_status := 0;
32
33 -- tsaifee 01/24/97 : First select the number from the table
34 -- then increment and update it.
35
36 -- LOCK TABLE PA_UNIQUE_IDENTIFIER_CONTROL IN SHARE UPDATE MODE;
37 /* Commented by sachin. Bug 3517177
38 SELECT NEXT_UNIQUE_IDENTIFIER
39 INTO PL_DUMMY
40 FROM PA_UNIQUE_IDENTIFIER_CONTROL
41 WHERE TABLE_NAME = x_table_name
42 AND NEXT_UNIQUE_IDENTIFIER IS NOT NULL
43 FOR UPDATE OF NEXT_UNIQUE_IDENTIFIER;
44 */
45 SELECT NEXT_UNIQUE_IDENTIFIER
46 INTO uniqueid
47 FROM PA_UNIQUE_IDENTIFIER_CONTROL
48 WHERE TABLE_NAME = x_table_name
49 AND NEXT_UNIQUE_IDENTIFIER IS NOT NULL
50 FOR UPDATE OF NEXT_UNIQUE_IDENTIFIER;
51
52 UPDATE PA_UNIQUE_IDENTIFIER_CONTROL
53 SET NEXT_UNIQUE_IDENTIFIER = NEXT_UNIQUE_IDENTIFIER + 1,
54 LAST_UPDATED_BY = user_id,
55 LAST_UPDATE_DATE = trunc(SYSDATE)
56 WHERE TABLE_NAME = x_table_name
57 AND NEXT_UNIQUE_IDENTIFIER IS NOT NULL;
58
59 unique_number := uniqueid;
60 Commit; -- Added by Sachin. Bug 3517177
61
62 EXCEPTION
63
64 when NO_DATA_FOUND then
65 x_status := 1;
66 rollback; --Added by Sachin. Bug 3517177
67
68 WHEN OTHERS then
69 x_status := SQLCODE;
70 rollback; --Added by Sachin. Bug 3517177
71
72 END get_unique_proj_num;
73
74 /*----------------------------------------------------------+
75 | get_unique_invoice_num : a procedure to get a unique |
76 | number for the automatic project number. |
77 | |
78 | unique_number : contains the returned unique number. |
79 | x_status : contains the returned status. |
80 | |
81 | x_status = 0 if it is successful. |
82 | = 1 if no data found. |
83 | = sqlcode otherwise |
84 | Bug fix : 438413 - Next number should actually be the |
85 | next number that is to be used, not the last|
86 | number used, as is currently stored in the |
87 | table. The next number will now be stored. |
88 | tsaifee 01/24/97 |
89 +----------------------------------------------------------*/
90 PROCEDURE get_unique_invoice_num(invoice_category IN VARCHAR2,
91 user_id IN NUMBER,
92 unique_number IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
93 x_status IN OUT NOCOPY NUMBER) is --File.Sql.39 bug 4440895
94 uniqueid NUMBER;
95 PL_DUMMY PA_IMPLEMENTATIONS.NEXT_AUTOMATIC_INVOICE_NUMBER%TYPE;
96
97 BEGIN
98
99 x_status := 0;
100
101 -- tsaifee 01/24/97 : First select the number from the table
102 -- then increment and update it.
103 -- Commented due to bug#634122 by Arindam
104
105 -- LOCK TABLE PA_IMPLEMENTATIONS IN SHARE UPDATE MODE;
106
107
108 SELECT decode(invoice_category, 'EXTERNAL-INVOICE',NEXT_AUTOMATIC_INVOICE_NUMBER,
109 CC_NEXT_AUTOMATIC_INVOICE_NUM)
110 INTO PL_DUMMY
111 FROM PA_IMPLEMENTATIONS
112 FOR UPDATE OF NEXT_AUTOMATIC_INVOICE_NUMBER,CC_NEXT_AUTOMATIC_INVOICE_NUM;
113
114 SELECT decode(invoice_category, 'EXTERNAL-INVOICE',NEXT_AUTOMATIC_INVOICE_NUMBER,
115 CC_NEXT_AUTOMATIC_INVOICE_NUM)
116 INTO uniqueid
117 FROM PA_IMPLEMENTATIONS;
118
119 UPDATE PA_IMPLEMENTATIONS
120 SET NEXT_AUTOMATIC_INVOICE_NUMBER = decode(invoice_category,
121 'EXTERNAL-INVOICE',NEXT_AUTOMATIC_INVOICE_NUMBER + 1,
122 NEXT_AUTOMATIC_INVOICE_NUMBER),
123 CC_NEXT_AUTOMATIC_INVOICE_NUM = decode(invoice_category,
124 'EXTERNAL-INVOICE',CC_NEXT_AUTOMATIC_INVOICE_NUM ,
125 CC_NEXT_AUTOMATIC_INVOICE_NUM+1),
126 LAST_UPDATED_BY = user_id,
127 LAST_UPDATE_DATE = trunc(SYSDATE);
128
129 unique_number := uniqueid;
130
131 EXCEPTION
132 /* Commented out for bug 1327836 as error is not handled in PAXVIACB.pls.If this
133 commnt is removed please handle the error at PAXVIACB.pls
134
135 when NO_DATA_FOUND then
136 x_status := 1;
137
138 WHEN OTHERS then
139 x_status := SQLCODE; */
140
141 /* Commented the above exception handling part and added the following
142 lines as part of fix for bug# 1327836*/
143
144 WHEN OTHERS then
145 raise;
146
147 END get_unique_invoice_num;
148
149 --Bug 7335526. Added code to revert the project number in
150 /*--------------------------------------------------------------+
151 | revert_unique_proj_num : A procedure to revert a unique |
152 | number for the automatic project number if project |
153 | creation errors out. |
154 | p_unique_number : contains the unique number which should |
155 | be reverted. |
156 +---------------------------------------------------------------*/
157 PROCEDURE revert_unique_proj_num(p_table_name IN VARCHAR2,
158 p_user_id IN NUMBER,
159 p_unique_number IN NUMBER) is
160
161 PRAGMA AUTONOMOUS_TRANSACTION; -- Added by Sachin. Bug 3517177
162
163 BEGIN
164
165 UPDATE PA_UNIQUE_IDENTIFIER_CONTROL
166 SET NEXT_UNIQUE_IDENTIFIER = p_unique_number,
167 LAST_UPDATED_BY = p_user_id,
168 LAST_UPDATE_DATE = trunc(SYSDATE)
169 WHERE TABLE_NAME = p_table_name
170 AND NEXT_UNIQUE_IDENTIFIER IS NOT NULL
171 AND NEXT_UNIQUE_IDENTIFIER = p_unique_number + 1 ;
172
173 Commit;
174
175
176 END;
177
178 END PA_UTILS_SQNUM_PKG;
[Home] [Help]