[Home] [Help]
PACKAGE BODY: APPS.AP_WEB_SIGNING_LIMITS_PKG
Source
1 PACKAGE BODY AP_WEB_SIGNING_LIMITS_PKG as
2 /* $Header: apiwsltb.pls 120.3 2005/12/08 13:17:19 srinvenk ship $ */
3
4 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
5 X_Document_Type VARCHAR2,
6 X_Employee_Id NUMBER,
7 X_Cost_Center VARCHAR2,
8 X_Signing_Limit NUMBER,
9 X_Last_Update_Date DATE,
10 X_Last_Updated_By NUMBER,
11 X_Last_Update_Login NUMBER,
12 X_Creation_Date DATE,
13 X_Created_By NUMBER,
14 X_Org_Id NUMBER
15 ) IS
16 CURSOR C IS SELECT rowid FROM ap_web_signing_limits
17 WHERE X_Document_Type = Document_Type
18 AND X_Employee_Id = Employee_Id
19 AND X_Cost_Center = Cost_Center;
20
21 BEGIN
22
23 CHECK_UNIQUE(X_rowid, X_document_type, X_employee_id, X_cost_center,
24 'AP_WEB_SIGNING_LIMITS_PKG.Insert_Row');
25
26 INSERT INTO ap_web_signing_limits_all(
27 document_type,
28 employee_id,
29 cost_center,
30 signing_limit,
31 last_update_date,
32 last_updated_by,
33 last_update_login,
34 creation_date,
35 created_by,
36 org_id
37 ) VALUES (
38 X_Document_Type,
39 X_Employee_Id,
40 X_Cost_Center,
41 X_Signing_Limit,
42 X_Last_Update_Date,
43 X_Last_Updated_By,
44 X_Last_Update_Login,
45 X_Creation_Date,
46 X_Created_By,
47 X_Org_Id
48 );
49
50 OPEN C;
51 FETCH C INTO X_Rowid;
52 if (C%NOTFOUND) then
53 CLOSE C;
54 Raise NO_DATA_FOUND;
55 end if;
56 CLOSE C;
57
58 EXCEPTION
59 WHEN OTHERS THEN
60 IF (SQLCODE <> -20001) THEN
61 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
62 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
63 FND_MESSAGE.SET_TOKEN('PARAMENTERS','X_ROWID='||X_ROWID||
64 ',X_DOCUMENT_TYPE='||X_DOCUMENT_TYPE||
65 ',X_EMPLOYEE_ID='||X_EMPLOYEE_ID||
66 ',X_COST_CENTER='||X_COST_CENTER||
67 ',X_SIGNING_LIMIT='||X_SIGNING_LIMIT||
68 ',X_LAST_UPDATE_DATE='||X_LAST_UPDATE_DATE||
69 ',X_LAST_UPDATED_BY='||X_LAST_UPDATED_BY||
70 ',X_LAST_UPDATE_LOGIN='||X_LAST_UPDATE_LOGIN||
71 ',X_CREATION_DATE='||X_CREATION_DATE||
72 ',X_CREATED_BY='||X_CREATED_BY||
73 ',X_ORG_ID='||X_ORG_ID);
74 FND_MESSAGE.SET_TOKEN('DEBUG_INFO','INSERT_ROW HAS EXCEPTION');
75 END IF;
76 APP_EXCEPTION.RAISE_EXCEPTION;
77
78 END Insert_Row;
79
80
81 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
82 X_Document_Type VARCHAR2,
83 X_Employee_Id NUMBER,
84 X_Cost_Center VARCHAR2,
85 X_Signing_Limit NUMBER,
86 X_Org_Id NUMBER
87 ) IS
88 CURSOR C IS
89 SELECT *
90 FROM ap_web_signing_limits
91 WHERE rowid = X_Rowid
92 FOR UPDATE of employee_id NOWAIT;
93 Recinfo C%ROWTYPE;
94
95 BEGIN
96 OPEN C;
97 FETCH C INTO Recinfo;
98 if (C%NOTFOUND) then
99 CLOSE C;
100 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
101 APP_EXCEPTION.Raise_Exception;
102 end if;
103 CLOSE C;
104 if (
105 (Recinfo.document_type = X_Document_Type)
106 AND (Recinfo.employee_id = X_Employee_Id)
107 AND (Recinfo.cost_center = X_Cost_Center)
108 AND ( (Recinfo.signing_limit = X_Signing_Limit)
109 OR (X_Signing_Limit IS NULL))
110 AND ( (Recinfo.org_id = X_Org_Id)
111 OR (X_Org_Id IS NULL))
112 ) then
113 return;
114 else
115 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
116 APP_EXCEPTION.Raise_Exception;
117 end if;
118
119 EXCEPTION
120 WHEN OTHERS THEN
121 IF (SQLCODE <> -20001) THEN
122 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
123 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
124 FND_MESSAGE.SET_TOKEN('PARAMENTERS','X_ROWID='||X_ROWID||
125 ',X_DOCUMENT_TYPE='||X_DOCUMENT_TYPE||
126 ',X_EMPLOYEE_ID='||X_EMPLOYEE_ID||
127 ',X_COST_CENTER='||X_COST_CENTER||
128 ',X_SIGNING_LIMIT='||X_SIGNING_LIMIT||
129 ',X_ORG_ID='||X_ORG_ID);
130 FND_MESSAGE.SET_TOKEN('DEBUG_INFO','LOCK_ROW HAS EXCEPTION');
131 END IF;
132 APP_EXCEPTION.RAISE_EXCEPTION;
133 END Lock_Row;
134
135
136 PROCEDURE Update_Row(X_Rowid VARCHAR2,
137 X_Document_Type VARCHAR2,
138 X_Employee_Id NUMBER,
139 X_Cost_Center VARCHAR2,
140 X_Signing_Limit NUMBER,
141 X_Last_Update_Date DATE,
142 X_Last_Updated_By NUMBER,
143 X_Last_Update_Login NUMBER,
144 X_Org_Id NUMBER
145
146 ) IS
147 BEGIN
148 UPDATE ap_web_signing_limits
149 SET
150 document_type = X_Document_Type,
151 employee_id = X_Employee_Id,
152 cost_center = X_Cost_Center,
153 signing_limit = X_Signing_Limit,
154 last_update_date = X_Last_Update_Date,
155 last_updated_by = X_Last_Updated_By,
156 last_update_login = X_Last_Update_Login,
157 org_id = X_Org_Id
158 WHERE rowid = X_Rowid;
159
160 if (SQL%NOTFOUND) then
161 Raise NO_DATA_FOUND;
162 end if;
163
164 EXCEPTION
165 WHEN OTHERS THEN
166 IF (SQLCODE <> -20001) THEN
167 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
168 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
169 FND_MESSAGE.SET_TOKEN('PARAMENTERS','X_ROWID='||X_ROWID||
170 ',X_DOCUMENT_TYPE='||X_DOCUMENT_TYPE||
171 ',X_EMPLOYEE_ID='||X_EMPLOYEE_ID||
172 ',X_COST_CENTER='||X_COST_CENTER||
173 ',X_SIGNING_LIMIT='||X_SIGNING_LIMIT||
174 ',X_LAST_UPDATE_DATE='||X_LAST_UPDATE_DATE||
175 ',X_LAST_UPDATED_BY='||X_LAST_UPDATED_BY||
176 ',X_LAST_UPDATE_LOGIN='||X_LAST_UPDATE_LOGIN||
177 ',X_ORG_ID='||X_ORG_ID);
178 FND_MESSAGE.SET_TOKEN('DEBUG_INFO','UPDATE_ROW HAS EXCEPTION');
179 END IF;
180 APP_EXCEPTION.RAISE_EXCEPTION;
181 END Update_Row;
182
183 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
184 BEGIN
185 DELETE FROM ap_web_signing_limits
186 WHERE rowid = X_Rowid;
187
188 if (SQL%NOTFOUND) then
189 Raise NO_DATA_FOUND;
190 end if;
191
192 EXCEPTION
193 WHEN OTHERS THEN
194 IF (SQLCODE <> -20001) THEN
195 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
196 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
197 FND_MESSAGE.SET_TOKEN('PARAMENTERS','X_ROWID='||X_ROWID);
198 FND_MESSAGE.SET_TOKEN('DEBUG_INFO','DELETE_ROW HAS EXCEPTION');
199 END IF;
200 APP_EXCEPTION.RAISE_EXCEPTION;
201 END Delete_Row;
202
203 PROCEDURE CHECK_UNIQUE (X_Rowid VARCHAR2,
204 X_Document_Type VARCHAR2,
205 X_Employee_Id NUMBER,
206 X_Cost_Center VARCHAR2,
207 X_calling_sequence VARCHAR2) IS
208 dummy_a number := 0;
209 current_calling_sequence VARCHAR2(2000);
210 debug_info VARCHAR2(100);
211
212 begin
213
214 -- update the calling sequence
215 --
216 current_calling_sequence :=
217 'AP_WEB_SIGNING_LIMIT_PKG.CHECK_UNIQUE<-'||X_calling_sequence;
218
219 debug_info := 'Count for same document_type, employee_id, and cost_center';
220
221 select count(1)
222 into dummy_a
223 from AP_WEB_SIGNING_LIMITS
224 where document_type = X_document_type
225 and employee_id = X_employee_id
226 and cost_center = X_cost_center
227 and ((X_rowid is null) or (rowid <> X_rowid));
228
229 if (dummy_a >= 1) then
230 fnd_message.set_name('SQLAP', 'AP_ALL_DUPLICATE_VALUE');
231 app_exception.raise_exception;
232 end if;
233
234 EXCEPTION
235 when OTHERS then
236 if (SQLCODE <> -20001) then
237 fnd_message.set_name('SQLAP', 'AP_DEBUG');
238 fnd_message.set_token('ERROR', sqlerrm);
239 fnd_message.set_token('CALLING_SEQUENCE', current_calling_sequence);
240 fnd_message.set_token('PARAMETERS',
241 'X_Rowid = ' || X_Rowid
242 ||', X_Document_Type = ' || X_Document_Type
243 ||', X_Employee_Id = ' || X_Employee_Id
244 ||', X_Cost_Center = ' || X_Cost_Center );
245 fnd_message.set_token('DEBUG_INFO', debug_info);
246 end if;
247 app_exception.raise_exception;
248
249 end CHECK_UNIQUE;
250
251 END AP_WEB_SIGNING_LIMITS_PKG;