[Home] [Help]
PACKAGE BODY: APPS.AP_DUPLICATE_VENDORS_PKG
Source
1 PACKAGE BODY AP_DUPLICATE_VENDORS_PKG as
2 /* $Header: apiduveb.pls 120.3 2004/10/28 00:01:41 pjena noship $ */
3
4 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
5
6 X_Entry_Id NUMBER DEFAULT NULL,
7 X_Vendor_Id NUMBER DEFAULT NULL,
8 X_Duplicate_Vendor_Id NUMBER DEFAULT NULL,
9 X_Vendor_Site_Id NUMBER DEFAULT NULL,
10 X_Duplicate_Vendor_Site_Id NUMBER DEFAULT NULL,
11 X_Number_Unpaid_Invoices NUMBER DEFAULT NULL,
12 X_Number_Paid_Invoices NUMBER DEFAULT NULL,
13 X_Number_Po_Headers_Changed NUMBER DEFAULT NULL,
14 X_Amount_Unpaid_Invoices NUMBER DEFAULT NULL,
15 X_Amount_Paid_Invoices NUMBER DEFAULT NULL,
16 X_Last_Update_Date DATE DEFAULT NULL,
17 X_Last_Updated_By NUMBER DEFAULT NULL,
18 X_Process_Flag VARCHAR2 DEFAULT NULL,
19 X_Process VARCHAR2 DEFAULT NULL,
20 X_Keep_Site_Flag VARCHAR2 DEFAULT NULL,
21 X_Paid_Invoices_Flag VARCHAR2 DEFAULT NULL,
22 X_Last_Update_Login NUMBER DEFAULT NULL,
23 X_Creation_Date DATE DEFAULT NULL,
24 X_Created_By NUMBER DEFAULT NULL,
25 X_Org_Id NUMBER,
26 X_calling_sequence IN VARCHAR2
27 ) IS
28 CURSOR C IS SELECT rowid FROM AP_DUPLICATE_VENDORS
29 WHERE ( (entry_id = X_Entry_Id)
30 or (entry_id is NULL and X_Entry_Id is NULL));
31
32 current_calling_sequence VARCHAR2(2000);
33 debug_info VARCHAR2(100);
34 BEGIN
35
36 -- Update the calling sequence
37 --
38 current_calling_sequence := 'AP_DUPLICATE_VENDORS_PKG.INSERT_ROW<-' ||
39 X_calling_sequence;
40
41 debug_info := 'Insert into AP_DUPLICATE_VENDORS';
42 INSERT INTO AP_DUPLICATE_VENDORS(
43
44 entry_id,
45 vendor_id,
46 duplicate_vendor_id,
47 vendor_site_id,
48 duplicate_vendor_site_id,
49 number_unpaid_invoices,
50 number_paid_invoices,
51 number_po_headers_changed,
52 amount_unpaid_invoices,
53 amount_paid_invoices,
54 last_update_date,
55 last_updated_by,
56 process_flag,
57 process,
58 keep_site_flag,
59 paid_invoices_flag,
60 last_update_login,
61 creation_date,
62 created_by,
63 org_id
64 ) VALUES (
65
66 X_Entry_Id,
67 X_Vendor_Id,
68 X_Duplicate_Vendor_Id,
69 X_Vendor_Site_Id,
70 X_Duplicate_Vendor_Site_Id,
71 X_Number_Unpaid_Invoices,
72 X_Number_Paid_Invoices,
73 X_Number_Po_Headers_Changed,
74 X_Amount_Unpaid_Invoices,
75 X_Amount_Paid_Invoices,
76 X_Last_Update_Date,
77 X_Last_Updated_By,
78 X_Process_Flag,
79 X_Process,
80 X_Keep_Site_Flag,
81 X_Paid_Invoices_Flag,
82 X_Last_Update_Login,
83 X_Creation_Date,
84 X_Created_By,
85 X_Org_Id
86 );
87
88 debug_info := 'Open cursor C';
89 OPEN C;
90 debug_info := 'Fetch cursor C';
91 FETCH C INTO X_Rowid;
92 if (C%NOTFOUND) then
93 debug_info := 'Close cursor C - DATA NOTFOUND';
94 CLOSE C;
95 Raise NO_DATA_FOUND;
96 end if;
97 debug_info := 'Close cursor C';
98 CLOSE C;
99
100 EXCEPTION
101 WHEN OTHERS THEN
102 IF (SQLCODE <> -20001) THEN
103 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
104 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
105 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
106 FND_MESSAGE.SET_TOKEN('PARAMETERS','ROWID = ' || X_Rowid ||
107 ', ENTRY_ID = ' || X_Entry_Id);
108 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
109 END IF;
110 APP_EXCEPTION.RAISE_EXCEPTION;
111
112 END Insert_Row;
113
114
115 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
116
117 X_Entry_Id NUMBER DEFAULT NULL,
118 X_Vendor_Id NUMBER DEFAULT NULL,
119 X_Duplicate_Vendor_Id NUMBER DEFAULT NULL,
120 X_Vendor_Site_Id NUMBER DEFAULT NULL,
121 X_Duplicate_Vendor_Site_Id NUMBER DEFAULT NULL,
122 X_Number_Unpaid_Invoices NUMBER DEFAULT NULL,
123 X_Number_Paid_Invoices NUMBER DEFAULT NULL,
124 X_Number_Po_Headers_Changed NUMBER DEFAULT NULL,
125 X_Amount_Unpaid_Invoices NUMBER DEFAULT NULL,
126 X_Amount_Paid_Invoices NUMBER DEFAULT NULL,
127 X_Process_Flag VARCHAR2 DEFAULT NULL,
128 X_Process VARCHAR2 DEFAULT NULL,
129 X_Keep_Site_Flag VARCHAR2 DEFAULT NULL,
130 X_Paid_Invoices_Flag VARCHAR2 DEFAULT NULL,
131 X_Org_Id NUMBER,
132 X_calling_sequence IN VARCHAR2
133 ) IS
134 CURSOR C IS
135 SELECT *
136 FROM AP_DUPLICATE_VENDORS
137 WHERE rowid = X_Rowid
138 FOR UPDATE of Entry_Id NOWAIT;
139 Recinfo C%ROWTYPE;
140
141 current_calling_sequence VARCHAR2(2000);
142 debug_info VARCHAR2(100);
143
144 BEGIN
145 -- Update the calling sequence
146 --
147 current_calling_sequence := 'AP_DUPLICATE_VENDORS_PKG.LOCK_ROW<-' ||
148 X_calling_sequence;
149
150 debug_info := 'Open cursor C';
151 OPEN C;
152 debug_info :=- 'Fetch cursor C';
153 FETCH C INTO Recinfo;
154 if (C%NOTFOUND) then
155 debug_info := 'Close cursor C - DATA NOT FOUND';
156 CLOSE C;
157 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
158 APP_EXCEPTION.Raise_Exception;
159 end if;
160 debug_info := 'Close cursor C';
161 CLOSE C;
162 if (
163
164 ( (Recinfo.entry_id = X_Entry_Id)
165 OR ( (Recinfo.entry_id IS NULL)
166 AND (X_Entry_Id IS NULL)))
167 AND ( (Recinfo.vendor_id = X_Vendor_Id)
168 OR ( (Recinfo.vendor_id IS NULL)
169 AND (X_Vendor_Id IS NULL)))
170 AND ( (Recinfo.duplicate_vendor_id = X_Duplicate_Vendor_Id)
171 OR ( (Recinfo.duplicate_vendor_id IS NULL)
172 AND (X_Duplicate_Vendor_Id IS NULL)))
173 AND ( (Recinfo.vendor_site_id = X_Vendor_Site_Id)
174 OR ( (Recinfo.vendor_site_id IS NULL)
175 AND (X_Vendor_Site_Id IS NULL)))
176 AND ( (Recinfo.duplicate_vendor_site_id = X_Duplicate_Vendor_Site_Id)
177 OR ( (Recinfo.duplicate_vendor_site_id IS NULL)
178 AND (X_Duplicate_Vendor_Site_Id IS NULL)))
179 AND ( (Recinfo.number_unpaid_invoices = X_Number_Unpaid_Invoices)
180 OR ( (Recinfo.number_unpaid_invoices IS NULL)
181 AND (X_Number_Unpaid_Invoices IS NULL)))
182 AND ( (Recinfo.number_paid_invoices = X_Number_Paid_Invoices)
183 OR ( (Recinfo.number_paid_invoices IS NULL)
184 AND (X_Number_Paid_Invoices IS NULL)))
185 AND ( (Recinfo.number_po_headers_changed = X_Number_Po_Headers_Changed)
186 OR ( (Recinfo.number_po_headers_changed IS NULL)
187 AND (X_Number_Po_Headers_Changed IS NULL)))
188 AND ( (Recinfo.amount_unpaid_invoices = X_Amount_Unpaid_Invoices)
189 OR ( (Recinfo.amount_unpaid_invoices IS NULL)
190 AND (X_Amount_Unpaid_Invoices IS NULL)))
191 AND ( (Recinfo.amount_paid_invoices = X_Amount_Paid_Invoices)
192 OR ( (Recinfo.amount_paid_invoices IS NULL)
193 AND (X_Amount_Paid_Invoices IS NULL)))
194 AND ( (Recinfo.process_flag = X_Process_Flag)
195 OR ( (Recinfo.process_flag IS NULL)
196 AND (X_Process_Flag IS NULL)))
197 AND ( (Recinfo.process = X_Process)
198 OR ( (Recinfo.process IS NULL)
199 AND (X_Process IS NULL)))
200 AND ( (Recinfo.keep_site_flag = X_Keep_Site_Flag)
201 OR ( (Recinfo.keep_site_flag IS NULL)
202 AND (X_Keep_Site_Flag IS NULL)))
203 AND ( (Recinfo.paid_invoices_flag = X_Paid_Invoices_Flag)
204 OR ( (Recinfo.paid_invoices_flag IS NULL)
205 AND (X_Paid_Invoices_Flag IS NULL)))
206 AND ( (Recinfo.org_id = X_Org_Id)
207 OR ( (Recinfo.org_id IS NULL)
208 AND (X_Org_Id IS NULL)))
209
210 ) then
211 return;
212 else
213 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
214 APP_EXCEPTION.Raise_Exception;
215 end if;
216
217 EXCEPTION
218 WHEN OTHERS THEN
219 IF (SQLCODE <> -20001) THEN
220 IF (SQLCODE = -54) THEN
221 FND_MESSAGE.SET_NAME('SQLAP','AP_RESOURCE_BUSY');
222 ELSE
223 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
224 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
225 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
226 FND_MESSAGE.SET_TOKEN('PARAMETERS','ROWID = ' || X_Rowid ||
227 ', ENTRY_ID = ' || X_Entry_Id);
228 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
229 END IF;
230 END IF;
231 APP_EXCEPTION.RAISE_EXCEPTION;
232
233 END Lock_Row;
234
235
236
237 PROCEDURE Update_Row(X_Rowid VARCHAR2,
238
239 X_Entry_Id NUMBER DEFAULT NULL,
240 X_Vendor_Id NUMBER DEFAULT NULL,
241 X_Duplicate_Vendor_Id NUMBER DEFAULT NULL,
242 X_Vendor_Site_Id NUMBER DEFAULT NULL,
243 X_Duplicate_Vendor_Site_Id NUMBER DEFAULT NULL,
244 X_Number_Unpaid_Invoices NUMBER DEFAULT NULL,
245 X_Number_Paid_Invoices NUMBER DEFAULT NULL,
246 X_Number_Po_Headers_Changed NUMBER DEFAULT NULL,
247 X_Amount_Unpaid_Invoices NUMBER DEFAULT NULL,
248 X_Amount_Paid_Invoices NUMBER DEFAULT NULL,
249 X_Last_Update_Date DATE DEFAULT NULL,
250 X_Last_Updated_By NUMBER DEFAULT NULL,
251 X_Process_Flag VARCHAR2 DEFAULT NULL,
252 X_Process VARCHAR2 DEFAULT NULL,
253 X_Keep_Site_Flag VARCHAR2 DEFAULT NULL,
254 X_Paid_Invoices_Flag VARCHAR2 DEFAULT NULL,
255 X_Last_Update_Login NUMBER DEFAULT NULL,
256 X_Org_Id NUMBER,
257 X_calling_sequence IN VARCHAR2
258
259 ) IS
260
261 current_calling_sequence VARCHAR2(2000);
262 debug_info VARCHAR2(100);
263
264 BEGIN
265
266 -- Update the calling sequence
267 --
268 current_calling_sequence := 'AP_DUPLICATE_VENDORS_PKG.UPDATE_ROW<-' ||
269 X_calling_sequence;
270
271 debug_info := 'Update AP_DUPLICATE_VENDORS';
272
273 UPDATE AP_DUPLICATE_VENDORS
274 SET
275 entry_id = X_Entry_Id,
276 vendor_id = X_Vendor_Id,
277 duplicate_vendor_id = X_Duplicate_Vendor_Id,
278 vendor_site_id = X_Vendor_Site_Id,
279 duplicate_vendor_site_id = X_Duplicate_Vendor_Site_Id,
280 number_unpaid_invoices = X_Number_Unpaid_Invoices,
281 number_paid_invoices = X_Number_Paid_Invoices,
282 number_po_headers_changed = X_Number_Po_Headers_Changed,
283 amount_unpaid_invoices = X_Amount_Unpaid_Invoices,
284 amount_paid_invoices = X_Amount_Paid_Invoices,
285 last_update_date = X_Last_Update_Date,
286 last_updated_by = X_Last_Updated_By,
287 process_flag = X_Process_Flag,
288 process = X_Process,
289 keep_site_flag = X_Keep_Site_Flag,
290 paid_invoices_flag = X_Paid_Invoices_Flag,
291 last_update_login = X_Last_Update_Login,
292 org_id = X_Org_Id
293 WHERE rowid = X_Rowid;
294
295 if (SQL%NOTFOUND) then
296 Raise NO_DATA_FOUND;
297 end if;
298
299 EXCEPTION
300 WHEN OTHERS THEN
301 IF (SQLCODE <> -20001) THEN
302 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
303 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
304 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
305 FND_MESSAGE.SET_TOKEN('PARAMETERS','ROWID = ' || X_Rowid ||
306 ', ENTRY_ID = ' || X_Entry_Id);
307 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
308 END IF;
309 APP_EXCEPTION.RAISE_EXCEPTION;
310
311 END Update_Row;
312
313 PROCEDURE Delete_Row(X_Rowid VARCHAR2,
314 X_calling_sequence IN VARCHAR2
315 ) IS
316
317 current_calling_sequence VARCHAR2(2000);
318 debug_info VARCHAR2(100);
319
320 BEGIN
321
322 -- Update the calling sequence
323 --
324 current_calling_sequence := 'AP_DUPLICATE_VENDORS_PKG.DELETE_ROW<-' ||
325 X_calling_sequence;
326
327 debug_info := 'Delete from AP_DUPLICATE_VENDORS';
328 DELETE FROM AP_DUPLICATE_VENDORS
329 WHERE rowid = X_Rowid;
330
331 if (SQL%NOTFOUND) then
332 Raise NO_DATA_FOUND;
333 end if;
334
335 EXCEPTION
336 WHEN OTHERS THEN
337 IF (SQLCODE <> -20001) THEN
338 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
339 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
340 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
341 FND_MESSAGE.SET_TOKEN('PARAMETERS','ROWID = ' || X_Rowid);
342 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
343 END IF;
344 APP_EXCEPTION.RAISE_EXCEPTION;
345
346 END Delete_Row;
347
348
349 END AP_DUPLICATE_VENDORS_PKG;