[Home] [Help]
PACKAGE BODY: APPS.JE_IT_EXEMPT_LETTERS_PKG
Source
1 PACKAGE BODY JE_IT_EXEMPT_LETTERS_PKG as
2 /* $Header: jeitexlb.pls 120.6 2006/05/31 05:44:38 samalhot ship $ */
3
4 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
5 X_Legal_Entity_Id NUMBER,
6 X_Set_of_Books_Id NUMBER,
7 X_Vendor_Id NUMBER,
8 X_Effective_From DATE,
9 X_Effective_To DATE,
10 X_Year NUMBER,
11 X_Exemption_Letter_Id NUMBER,
12 X_Print_Flag VARCHAR2,
13 X_Issue_Flag VARCHAR2,
14 X_Custom_Flag VARCHAR2,
15 X_Letter_Type VARCHAR2,
16 X_Limit_Amount NUMBER,
17 X_Clause_Ref VARCHAR2,
18 X_Issue_Date DATE,
19 X_Last_Update_Date DATE,
20 X_Last_Updated_By NUMBER,
21 X_Creation_Date DATE,
22 X_Created_By NUMBER
23 ) IS
24 CURSOR C IS SELECT rowid FROM JE_IT_EXEMPT_LETTERS
25 WHERE vendor_id = X_Vendor_Id
26 AND effective_from = X_Effective_From
27 AND effective_to = X_Effective_To;
28
29 CURSOR c_alc_ledger (p_ledger_id IN NUMBER) IS
30 SELECT ledger_id, currency_code
31 FROM gl_alc_ledger_rships_v
32 WHERE primary_ledger_id = p_ledger_id
33 AND application_id = 222
34 AND org_id = MO_GLOBAL.get_current_org_id
35 -- AND nvl(org_id,-99) = NVL(NVL(rtrim(substr(userenv('CLIENT_INFO'),1,10)),-99),-99)
36 AND relationship_enabled_flag = 'Y';
37
38 BEGIN
39 declare
40 exchange_rate NUMBER;
41 c_ledger_id NUMBER;
42 begin
43 INSERT INTO JE_IT_EXEMPT_LETTERS(
44 legal_entity_id,
45 set_of_books_id,
46 vendor_id,
47 effective_from,
48 effective_to,
49 year,
50 exemption_letter_id,
51 print_flag,
52 issue_flag,
53 custom_flag,
54 letter_type,
55 limit_amount,
56 clause_ref,
57 issue_date,
58 last_update_date,
59 last_updated_by,
60 creation_date,
61 created_by
62 ) VALUES (
63 X_Legal_Entity_Id,
64 X_Set_of_Books_Id,
65 X_Vendor_Id,
66 X_Effective_From,
67 X_Effective_To,
68 X_Year,
69 X_Exemption_Letter_Id,
70 X_Print_Flag,
71 X_Issue_Flag,
72 X_Custom_Flag,
73 X_Letter_Type,
74 X_Limit_Amount,
75 X_Clause_Ref,
76 X_Issue_Date,
77 X_Last_Update_Date,
78 X_Last_Updated_By,
79 X_Creation_Date,
80 X_Created_By
81 );
82 -- We also need to insert a row for each additional ledger
83 -- Loop for each Additional Ledger
84 for rledger_rec in c_alc_ledger(c_ledger_id)
85 loop
86
87 -- get exchange rate for the additional ledger currency
88 exchange_rate := gl_currency_api.get_rate_sql (rledger_rec.ledger_id,
89 rledger_rec.currency_code,NULL,NULL);
90
91
92 INSERT INTO JE_IT_EXEMPT_LETTERS(
93 legal_entity_id,
94 set_of_books_id,
95 vendor_id,
96 effective_from,
97 effective_to,
98 year,
99 exemption_letter_id,
100 print_flag,
101 issue_flag,
102 custom_flag,
103 letter_type,
104 limit_amount,
105 clause_ref,
106 issue_date,
107 last_update_date,
108 last_updated_by,
109 creation_date,
110 created_by
111 ) VALUES (
112 X_Legal_Entity_Id,
113 X_Set_of_Books_Id,
114 X_Vendor_Id,
115 X_Effective_From,
116 X_Effective_To,
117 X_Year,
118 X_Exemption_Letter_Id,
119 X_Print_Flag,
120 X_Issue_Flag,
121 X_Custom_Flag,
122 X_Letter_Type,
123 X_Limit_Amount/exchange_rate,
124 X_Clause_Ref,
125 X_Issue_Date,
126 X_Last_Update_Date,
127 X_Last_Updated_By,
128 X_Creation_Date,
129 X_Created_By
130 );
131 end loop;
132 OPEN C;
133 FETCH C INTO X_Rowid;
134 if (C%NOTFOUND) then
135 CLOSE C;
136 Raise NO_DATA_FOUND;
137 end if;
138 CLOSE C;
139 end;
140 END Insert_Row;
141
142
143 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
144 X_Legal_Entity_Id NUMBER,
145 X_Set_of_Books_Id NUMBER,
146 X_Vendor_Id NUMBER,
147 X_Effective_From DATE,
148 X_Effective_To DATE,
149 X_Year NUMBER,
150 X_Exemption_Letter_Id NUMBER,
151 X_Print_Flag VARCHAR2,
152 X_Issue_Flag VARCHAR2,
153 X_Custom_Flag VARCHAR2,
154 X_Letter_Type VARCHAR2,
155 X_Limit_Amount NUMBER,
156 X_Clause_Ref VARCHAR2,
157 X_Issue_Date DATE,
158 X_Last_Update_Date DATE,
159 X_Last_Updated_By NUMBER,
160 X_Creation_Date DATE,
161 X_Created_By NUMBER
162 ) IS
163 CURSOR C IS
164 SELECT *
165 FROM JE_IT_EXEMPT_LETTERS
166 WHERE rowid = X_Rowid
167 FOR UPDATE of Vendor_Id NOWAIT;
168 Recinfo C%ROWTYPE;
169
170
171
172 BEGIN
173
174 OPEN C;
175 FETCH C INTO Recinfo;
176 if (C%NOTFOUND) then
177 CLOSE C;
178 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
179 APP_EXCEPTION.Raise_Exception;
180 end if;
181 CLOSE C;
182 if ( (Recinfo.legal_entity_id = X_Legal_Entity_Id)
183 AND (Recinfo.set_of_books_id = X_Set_of_Books_Id)
184 AND (Recinfo.vendor_id = X_Vendor_Id)
185 AND (Recinfo.effective_from = X_Effective_From)
186 AND (Recinfo.effective_to = X_Effective_To)
187 AND (Recinfo.year = X_Year)
188 AND (Recinfo.exemption_letter_id = X_Exemption_Letter_Id)
189 AND (Recinfo.print_flag = X_Print_Flag)
190 AND (Recinfo.issue_flag = X_Issue_Flag)
191 AND (Recinfo.custom_flag = X_Custom_Flag)
192 AND (Recinfo.letter_type = X_Letter_Type)
193 AND (Recinfo.limit_amount = X_Limit_Amount)
194 AND (Recinfo.clause_ref = X_Clause_Ref)
195 AND ( (trunc(Recinfo.issue_date) = X_Issue_Date)
196 OR ( (Recinfo.issue_date IS NULL)
197 AND (X_Issue_Date IS NULL)))
198 ) then
199
200 return;
201 else
202 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
203 APP_EXCEPTION.Raise_Exception;
204 end if;
205 END Lock_Row;
206
207
208 PROCEDURE Update_Row(X_Rowid VARCHAR2,
209 X_Legal_Entity_Id NUMBER,
210 X_Set_of_Books_Id NUMBER,
211 X_Vendor_Id NUMBER,
212 X_Effective_From DATE,
213 X_Effective_To DATE,
214 X_Year NUMBER,
215 X_Exemption_Letter_Id NUMBER,
216 X_Print_Flag VARCHAR2,
217 X_Issue_Flag VARCHAR2,
218 X_Custom_Flag VARCHAR2,
219 X_Letter_Type VARCHAR2,
220 X_Limit_Amount NUMBER,
221 X_Clause_Ref VARCHAR2,
222 X_Issue_Date DATE,
223 X_Last_Update_Date DATE,
224 X_Last_Updated_By NUMBER,
225 X_Creation_Date DATE,
226 X_Created_By NUMBER
227 ) IS
228 CURSOR c_alc_ledger (p_ledger_id in number) is
229 SELECT ledger_id, currency_code
230 FROM gl_alc_ledger_rships_v
231 WHERE primary_ledger_id = p_ledger_id
232 AND application_id = 222
233 AND org_id = MO_GLOBAL.get_current_org_id
234 -- AND NVL(org_id,-99) = NVL(NVL(rtrim(substr(userenv('CLIENT_INFO'),1,10)),-99), -99)
235 AND relationship_enabled_flag = 'Y' ;
236
237 BEGIN
238 declare
239 update_exemption_letter_id JE_IT_EXEMPT_LETTERS.exemption_letter_id%TYPE;
240 update_vendor_id JE_IT_EXEMPT_LETTERS.vendor_id%TYPE;
241 exchange_rate NUMBER;
242 c_ledger_id NUMBER;
243 begin
244 SELECT vendor_id,exemption_letter_id
245 INTO update_vendor_id, update_exemption_letter_id
246 FROM je_it_exempt_letters
247 WHERE rowid =x_rowid;
248
249 UPDATE JE_IT_EXEMPT_LETTERS
250 SET
251 legal_entity_id = X_Legal_Entity_Id,
252 set_of_books_id = X_Set_of_Books_Id,
253 vendor_id = X_Vendor_Id,
254 effective_from = X_Effective_From,
255 effective_to = X_Effective_To,
256 year = X_Year,
257 exemption_letter_id = X_Exemption_Letter_Id,
258 print_flag = X_Print_Flag,
259 issue_flag = X_Issue_Flag,
260 custom_flag = X_Custom_Flag,
261 letter_type = X_Letter_Type,
262 limit_amount = X_Limit_Amount,
263 clause_ref = X_Clause_Ref,
264 issue_date = X_Issue_Date,
265 last_update_date = X_Last_Update_Date,
266 last_updated_by = X_Last_Updated_By,
267 creation_date = X_Creation_Date,
268 created_by = X_Created_By
269 WHERE rowid = X_Rowid;
270
271 -- We also need to update a row for each additional ledger.
272
273 -- Loop for each Additional ledger
274 For rledger_rec IN c_alc_ledger(c_ledger_id)
275 loop
276 -- get exchange rate for the additional ledger currency
277 exchange_rate :=gl_currency_api.get_rate_sql(rledger_rec.ledger_id,
278 rledger_rec.currency_code,NULL,NULL);
279
280 UPDATE JE_IT_EXEMPT_LETTERS
281 SET
282 legal_entity_id = X_Legal_Entity_Id,
283 set_of_books_id = rledger_rec.ledger_id,
284 vendor_id = X_Vendor_Id,
285 effective_from = X_Effective_From,
286 effective_to = X_Effective_To,
287 year = X_Year,
288 exemption_letter_id = X_Exemption_Letter_Id,
289 print_flag = X_Print_Flag,
290 issue_flag = X_Issue_Flag,
291 custom_flag = X_Custom_Flag,
292 letter_type = X_Letter_Type,
293 limit_amount = X_Limit_Amount/exchange_rate,
294 clause_ref = X_Clause_Ref,
295 issue_date = X_Issue_Date,
296 last_update_date = X_Last_Update_Date,
297 last_updated_by = X_Last_Updated_By,
298 creation_date = X_Creation_Date,
299 created_by = X_Created_By
300 WHERE set_of_books_id = rledger_rec.ledger_id
301 AND vendor_id= update_vendor_id
302 AND exemption_letter_id = update_exemption_letter_id;
303 end loop;
304
305 if (SQL%NOTFOUND) then
306 Raise NO_DATA_FOUND;
307 end if;
308 end;
309 END Update_Row;
310
311
312 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
313
314 CURSOR c_alc_ledger (p_ledger_id in number) is
315 SELECT ledger_id, currency_code
316 FROM gl_alc_ledger_rships_v
317 WHERE primary_ledger_id = p_ledger_id
318 AND application_id = 222
319 AND org_id = MO_GLOBAL.get_current_org_id
320 -- AND NVL(org_id,-99) = NVL(NVL(rtrim(substr(userenv('CLIENT_INFO'),1,10)),-99), -99)
321 AND relationship_enabled_flag = 'Y' ;
322
323
324 BEGIN
325 declare
326 delete_exemption_letter_id JE_IT_EXEMPT_LETTERS.exemption_letter_id%TYPE;
327 delete_vendor_id JE_IT_EXEMPT_LETTERS.vendor_id%TYPE;
328 exchange_rate NUMBER;
329 c_ledger_id NUMBER;
330 begin
331 SELECT vendor_id,exemption_letter_id
332 INTO delete_vendor_id, delete_exemption_letter_id
333 FROM je_it_exempt_letters
334 WHERE rowid = x_rowid;
335
336
337 DELETE FROM JE_IT_EXEMPT_LETTERS
338 WHERE rowid = X_Rowid;
339
340 FOR rledger_rec IN c_alc_ledger(c_ledger_id)
341 loop
342
343 DELETE FROM JE_IT_EXEMPT_LETTERS
344 WHERE set_of_books_id = rledger_rec.ledger_id
345 AND vendor_id = delete_vendor_id
346 AND exemption_letter_id = delete_exemption_letter_id;
347
348 end loop;
349 if (SQL%NOTFOUND) then
350 Raise NO_DATA_FOUND;
351 end if;
352 end;
353 END Delete_Row;
354
355
356 END JE_IT_EXEMPT_LETTERS_PKG;