[Home] [Help]
PACKAGE BODY: APPS.JL_ZZ_AR_TX_LGL_MSG_PKG
Source
1 PACKAGE BODY JL_ZZ_AR_TX_LGL_MSG_PKG AS
2 /* $Header: jlzztlmb.pls 120.2 2003/03/03 19:36:18 opedrega ship $ */
3
4 PROCEDURE Insert_Row
5 (X_rowid IN OUT NOCOPY VARCHAR2,
6 X_rule_id NUMBER,
7 X_rule_data_id NUMBER,
8 X_exception_code VARCHAR2,
9 X_message_id NUMBER,
10 X_inventory_item_flag VARCHAR2,
11 X_inventory_organization_id NUMBER,
12 X_start_date_active DATE,
13 X_end_date_active DATE,
14 X_org_id NUMBER,
15 X_last_update_date DATE,
16 X_last_updated_by NUMBER,
17 X_last_update_login NUMBER,
18 X_creation_date DATE,
19 X_created_by NUMBER,
20 X_calling_sequence IN VARCHAR2) IS
21
22 CURSOR C IS
23 SELECT rowid
24 FROM jl_zz_ar_tx_lgl_msg
25 WHERE rule_id = X_rule_id
26 AND rule_data_id = X_rule_data_id
27 AND exception_code = X_exception_code
28 AND org_id = X_org_id;
29
30 current_calling_sequence VARCHAR2(2000);
31 debug_info VARCHAR2(100);
32
33 BEGIN
34 -- Update the calling sequence
35 --
36 current_calling_sequence := 'jl_zz_ar_tx_lgl_msg_PKG.INSERT_ROW<-' ||
37 X_calling_sequence;
38
39 debug_info := 'Insert INTO jl_zz_ar_tx_lgl_msg ';
40 INSERT INTO jl_zz_ar_tx_lgl_msg(rule_id,
41 rule_data_id,
42 exception_code,
43 message_id,
44 inventory_item_flag,
45 inventory_organization_id,
46 start_date_active,
47 end_date_active,
48 org_id,
49 last_update_date,
50 last_updated_by,
51 last_update_login,
52 creation_date,
53 created_by)
54 VALUES (X_rule_id,
55 X_rule_data_id,
56 X_exception_code,
57 X_message_id,
58 X_inventory_item_flag,
59 X_inventory_organization_id,
60 X_start_date_active,
61 X_end_date_active,
62 X_org_id,
63 X_last_update_date,
64 X_last_updated_by,
65 X_last_update_login,
66 X_creation_date,
67 X_created_by);
68
69 debug_info := 'Open CURSOR C';
70 OPEN C;
71 debug_info := 'Fetch CURSOR C';
72 FETCH C INTO X_rowid;
73 IF (C%NOTFOUND) then
74 debug_info := 'Close CURSOR C - DATA NOTFOUND';
75 CLOSE C;
76 Raise NO_DATA_FOUND;
77 END IF;
78 debug_info := 'Close CURSOR C';
79 CLOSE C;
80
81 EXCEPTION
82 WHEN OTHERS THEN
83 IF (SQLCODE <> -20001) THEN
84 FND_MESSAGE.SET_NAME('AR','AR_DEBUG');
85 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
86 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
87 FND_MESSAGE.SET_TOKEN('PARAMETERS',
88 'org_id = ' || X_org_id ||
89 'rule_id = ' || X_rule_id ||
90 'rule_data_id = ' || X_rule_data_id );
91 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
92 END IF;
93 APP_EXCEPTION.RAISE_EXCEPTION;
94
95 END Insert_Row;
96
97 PROCEDURE Lock_Row
98 (X_rowid VARCHAR2,
99 X_rule_id NUMBER,
100 X_rule_data_id NUMBER,
101 X_exception_code VARCHAR2,
102 X_message_id NUMBER,
103 X_inventory_item_flag VARCHAR2,
104 X_inventory_organization_id NUMBER,
105 X_start_date_active DATE,
106 X_end_date_active DATE,
107 X_org_id NUMBER,
108 X_last_update_date DATE,
109 X_last_updated_by NUMBER,
110 X_last_update_login NUMBER,
111 X_creation_date DATE,
112 X_created_by NUMBER,
113 X_calling_sequence IN VARCHAR2) IS
114
115 CURSOR C IS
116 SELECT RULE_ID,
117 RULE_DATA_ID,
118 EXCEPTION_CODE,
119 MESSAGE_ID,
120 INVENTORY_ITEM_FLAG,
121 INVENTORY_ORGANIZATION_ID,
122 START_DATE_ACTIVE,
123 END_DATE_ACTIVE,
124 ORG_ID,
125 LAST_UPDATE_DATE,
126 LAST_UPDATED_BY,
127 CREATION_DATE,
128 CREATED_BY,
129 LAST_UPDATE_LOGIN
130 FROM JL_ZZ_AR_TX_LGL_MSG
131 WHERE rule_id = X_rule_id
132 AND rule_data_id = X_rule_data_id
133 AND exception_code = X_exception_code
134 AND org_id = X_org_id
135 FOR UPDATE of rule_id,
136 exception_code,
137 rule_data_id,
138 org_id
139 NOWAIT;
140
141 Recinfo C%ROWTYPE;
142
143 current_calling_sequence VARCHAR2(2000);
144 debug_info VARCHAR2(100);
145
146 BEGIN
147 -- Update the calling sequence
148 --
149 current_calling_sequence := 'jl_zz_ar_tx_lgl_msg_PKG.LOCK_ROW<-' ||
150 X_calling_sequence;
151
152 debug_info := 'Open CURSOR C';
153 OPEN C;
154 debug_info := 'Fetch CURSOR C';
155 FETCH C INTO Recinfo;
156 IF (C%NOTFOUND) THEN
157 debug_info := 'Close CURSOR C - DATA NOTFOUND';
158 CLOSE C;
159 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
160 APP_EXCEPTION.Raise_Exception;
161 END IF;
162
163 debug_info := 'Close CURSOR C';
164 CLOSE C;
165 IF ((Recinfo.rule_id = X_rule_id) AND
166 (Recinfo.rule_data_id = X_rule_data_id) AND
167 (Recinfo.message_id = X_message_id) AND
168 (Recinfo.exception_code = X_exception_code) AND
169 (Recinfo.inventory_item_flag = X_inventory_item_flag) AND
170 (Recinfo.last_updated_by = X_last_updated_by) AND
171 (Recinfo.last_update_date = X_last_update_date) AND
172 ((Recinfo.inventory_organization_id = X_inventory_organization_id) OR
173 ((X_inventory_organization_id IS NULL) AND
174 (Recinfo.inventory_organization_id IS NULL))) AND
175 ((Recinfo.start_date_active = X_start_date_active) OR
176 ((X_start_date_active IS NULL) AND
177 (Recinfo.start_date_active IS NULL))) AND
178 ((Recinfo.end_date_active = X_end_date_active) OR
179 ((X_end_date_active IS NULL) AND
180 (Recinfo.end_date_active IS NULL))) AND
181 ((Recinfo.org_id = X_org_id) OR
182 ((X_org_id IS NULL) AND
183 (Recinfo.org_id IS NULL))) AND
184 ((Recinfo.last_update_login = X_last_update_login) OR
185 ((X_last_update_login IS NULL) AND
186 (Recinfo.last_update_login IS NULL))) AND
187 ((Recinfo.creation_date = X_creation_date) OR
188 ((X_creation_date IS NULL) AND
189 (Recinfo.creation_date IS NULL))) AND
190 ((Recinfo.created_by = X_created_by) OR
191 ((X_created_by IS NULL) AND
192 (Recinfo.created_by IS NULL)))) THEN
193 return;
194 ELSE
195 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
196 APP_EXCEPTION.Raise_Exception;
197 END IF;
198
199 EXCEPTION
200 WHEN OTHERS THEN
201 IF (SQLCODE <> -20001) THEN
202 IF (SQLCODE = -54) THEN
203 FND_MESSAGE.SET_NAME('AR','AR_RESOURCE_BUSY');
204 ELSE
205 FND_MESSAGE.SET_NAME('AR','AR_DEBUG');
206 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
207 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
208 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
209 FND_MESSAGE.SET_TOKEN('PARAMETERS',
210 'rule_id = ' || X_rule_id ||
211 'rule_data_id = ' || X_rule_data_id );
212 END IF;
213 END IF;
214 APP_EXCEPTION.RAISE_EXCEPTION;
215 END Lock_Row;
216
217 PROCEDURE Update_Row
218 (X_rowid VARCHAR2,
219 X_rule_id NUMBER,
220 X_rule_data_id NUMBER,
221 X_exception_code VARCHAR2,
222 X_message_id NUMBER,
223 X_inventory_item_flag VARCHAR2,
224 X_inventory_organization_id NUMBER,
225 X_start_date_active DATE,
226 X_end_date_active DATE,
227 X_org_id NUMBER,
228 X_last_update_date DATE,
229 X_last_updated_by NUMBER,
230 X_last_update_login NUMBER,
231 X_creation_date DATE,
232 X_created_by NUMBER,
233 X_calling_sequence IN VARCHAR2) IS
234
235 BEGIN
236 UPDATE JL_ZZ_AR_TX_LGL_MSG
237 SET rule_id = X_rule_id,
238 rule_data_id = X_rule_data_id,
239 exception_code = X_exception_code,
240 message_id = X_message_id,
241 inventory_item_flag = X_inventory_item_flag,
242 inventory_organization_id = X_inventory_organization_id,
243 start_date_active = X_start_date_active,
244 end_date_active = X_end_date_active,
245 org_id = X_org_id,
246 last_update_date = X_last_update_date,
247 last_updated_by = X_last_updated_by,
248 last_update_login = X_last_update_login,
249 creation_date = X_creation_date,
250 created_by = X_created_by
251 WHERE rowid = X_rowid;
252
253 IF (SQL%NOTFOUND) THEN
254 raise NO_DATA_FOUND;
255 END IF;
256 END Update_Row;
257
258 PROCEDURE Delete_Row
259 (X_rowid VARCHAR2) IS
260
261 BEGIN
262 DELETE
263 FROM JL_ZZ_AR_TX_LGL_MSG
264 WHERE rowid = X_rowid;
265
266 IF (SQL%NOTFOUND) THEN
267 raise NO_DATA_FOUND;
268 END IF;
269 END Delete_Row;
270
271 PROCEDURE Check_Unique
272 (X_rowid VARCHAR2,
273 X_rule_id NUMBER,
274 X_rule_data_id NUMBER,
275 X_exception_code VARCHAR2,
276 X_org_id NUMBER) IS
277
278 dummy NUMBER;
279
280 BEGIN
281 SELECT count(1)
282 INTO dummy
283 FROM jl_zz_ar_tx_lgl_msg
284 WHERE rule_id = X_rule_id
285 AND rule_data_id = X_rule_data_id
286 AND exception_code = X_exception_code
287 AND org_id = X_org_id
288 AND ((X_rowid IS NULL) OR (rowid <> X_rowid));
289
290 IF (dummy >= 1) THEN
291 FND_MESSAGE.SET_NAME ('JL', 'JL_ZZ_AR_TX_ALREADY_EXISTS');
292 FND_MESSAGE.SET_TOKEN('ENTITY', 'Legal Message');
293 APP_EXCEPTION.RAISE_EXCEPTION;
294 END IF;
295 END Check_Unique;
296
297 END jl_zz_ar_tx_lgl_msg_pkg;