4 procedure insert_row (x_rowid in out NOCOPY VARCHAR2 ,
1 package body gms_encumbrance_items_pkg as
2 /* $Header: GMSTITMB.pls 120.1 2007/02/06 09:47:11 rshaik ship $ */
3
5 x_encumbrance_item_id in out NOCOPY NUMBER,
6 x_last_update_date in DATE,
7 x_last_updated_by in NUMBER,
8 x_creation_date in DATE,
9 x_created_by in NUMBER,
10 x_encumbrance_id in NUMBER,
11 x_task_id in NUMBER,
12 x_encumbrance_item_date in DATE,
13 x_encumbrance_type in VARCHAR2,
14 x_enc_distributed_flag in VARCHAR2,
15 x_amount in NUMBER DEFAULT NULL,
16 x_override_to_organization_id in NUMBER DEFAULT NULL,
17 x_adjusted_encumbrance_item_id in NUMBER DEFAULT NULL,
18 x_net_zero_adjustment_flag in VARCHAR2 DEFAULT NULL,
19 x_transferred_from_enc_item_id in NUMBER DEFAULT NULL,
20 x_last_update_login in NUMBER DEFAULT NULL,
21 x_request_id in NUMBER DEFAULT NULL,
22 x_attribute_category in VARCHAR2 DEFAULT NULL,
23 x_attribute1 in VARCHAR2 DEFAULT NULL,
24 x_attribute2 in VARCHAR2 DEFAULT NULL,
25 x_attribute3 in VARCHAR2 DEFAULT NULL,
26 x_attribute4 in VARCHAR2 DEFAULT NULL,
27 x_attribute5 in VARCHAR2 DEFAULT NULL,
28 x_attribute6 in VARCHAR2 DEFAULT NULL,
29 x_attribute7 in VARCHAR2 DEFAULT NULL,
30 x_attribute8 in VARCHAR2 DEFAULT NULL,
31 x_attribute9 in VARCHAR2 DEFAULT NULL,
32 x_attribute10 in VARCHAR2 DEFAULT NULL,
33 x_orig_transaction_reference in VARCHAR2 DEFAULT NULL,
34 x_transaction_source in VARCHAR2 DEFAULT NULL,
35 x_project_id in NUMBER DEFAULT NULL,
36 x_source_encumbrance_item_id in NUMBER DEFAULT NULL,
37 x_job_id in NUMBER DEFAULT NULL,
38 x_system_linkage_function in VARCHAR2,
39 x_denom_currency_code in VARCHAR2 DEFAULT NULL,
40 x_denom_raw_amount in NUMBER DEFAULT NULL,
41 x_acct_exchange_rounding_limit in NUMBER DEFAULT NULL,
42 x_acct_currency_code in VARCHAR2 DEFAULT NULL,
43 x_acct_rate_date in DATE DEFAULT NULL,
44 x_acct_rate_type in VARCHAR2 DEFAULT NULL,
45 x_acct_exchange_rate in NUMBER DEFAULT NULL,
46 x_acct_raw_cost in NUMBER DEFAULT NULL,
47 x_project_currency_code in VARCHAR2 DEFAULT NULL,
48 x_project_rate_date in DATE DEFAULT NULL,
49 x_project_rate_type in VARCHAR2 DEFAULT NULL,
50 x_project_exchange_rate in NUMBER DEFAULT NULL,
51 x_encumbrance_comment in VARCHAR2 DEFAULT NULL,
52 x_org_id in NUMBER ,
53 x_denom_tp_currency_code in VARCHAR2 DEFAULT NULL,
54 x_denom_transfer_price in NUMBER DEFAULT NULL,
55 x_person_id in NUMBER DEFAULT NULL,
56 x_incurred_by_person_id in NUMBER DEFAULT NULL,
57 x_ind_compiled_set_id in NUMBER DEFAULT NULL,
58 x_pa_date in DATE DEFAULT NULL,
59 x_gl_date in DATE DEFAULT NULL,
60 x_line_num in NUMBER DEFAULT 1,
61 x_burden_sum_dest_run_id in NUMBER DEFAULT NULL,
62 x_burden_sum_source_run_id in NUMBER DEFAULT NULL) IS
63
64
65 cursor return_rowid is select rowid from gms_encumbrance_items
66 where encumbrance_item_id = x_encumbrance_item_id;
67 cursor get_itemid is select gms_encumbrance_items_s.nextval from sys.dual;
68
69 status NUMBER;
70 l_project_id number; --Bug 5726575
71 BEGIN
75 fetch get_itemid into x_encumbrance_item_id;
72
73 if (x_encumbrance_item_id is null) then
74 open get_itemid;
76 close get_itemid;
77 end if;
78
79 --Bug 5726575
80 if x_project_id is null then
81 select project_id
82 into l_project_id
83 from pa_tasks
84 where task_id = x_task_id;
85 end if;
86
87 -- if amt is negative, need to update reversed original
88
89 insert into gms_encumbrance_items (encumbrance_item_id,
90 last_update_date,
91 last_updated_by,
92 creation_date,
93 created_by,
94 encumbrance_id,
95 task_id,
96 encumbrance_item_date,
97 encumbrance_type,
98 enc_distributed_flag,
99 amount,
100 override_to_organization_id,
101 adjusted_encumbrance_item_id,
102 net_zero_adjustment_flag,
103 transferred_from_enc_item_id,
104 last_update_login,
105 attribute_category,
106 attribute1,
107 attribute2,
108 attribute3,
109 attribute4,
110 attribute5,
111 attribute6,
112 attribute7,
113 attribute8,
114 attribute9,
115 attribute10,
116 orig_transaction_reference,
117 transaction_source,
118 project_id,
119 source_encumbrance_item_id,
120 job_id,
121 system_linkage_function,
122 denom_currency_code,
123 denom_raw_amount,
124 acct_exchange_rounding_limit,
125 acct_currency_code,
126 acct_rate_date,
127 acct_rate_type,
128 acct_exchange_rate,
129 acct_raw_cost,
130 project_currency_code,
131 project_rate_date,
132 project_rate_type,
133 project_exchange_rate,
134 denom_tp_currency_code,
135 denom_transfer_price,
136 encumbrance_comment,
137 person_id,
138 incurred_by_person_id,
139 ind_compiled_set_id,
140 pa_date,
141 gl_date,
142 line_num,
143 burden_sum_dest_run_id,
144 burden_sum_source_run_id ,
145 org_id )
146 values (x_encumbrance_item_id,
147 x_last_update_date,
148 x_last_updated_by,
149 x_creation_date,
150 x_created_by,
151 x_encumbrance_id,
152 x_task_id,
153 x_encumbrance_item_date,
154 x_encumbrance_type,
155 x_enc_distributed_flag,
156 x_amount,
157 x_override_to_organization_id,
158 x_adjusted_encumbrance_item_id,
159 x_net_zero_adjustment_flag,
160 x_transferred_from_enc_item_id,
161 x_last_update_login,
162 x_attribute_category,
163 x_attribute1,
164 x_attribute2,
165 x_attribute3,
166 x_attribute4,
167 x_attribute5,
168 x_attribute6,
169 x_attribute7,
170 x_attribute8,
171 x_attribute9,
172 x_attribute10,
173 x_orig_transaction_reference,
174 x_transaction_source,
175 nvl(x_project_id, l_project_id),--Bug 5726575 x_project_id,
176 x_source_encumbrance_item_id,
177 x_job_id,
178 x_system_linkage_function,
179 x_denom_currency_code,
180 x_denom_raw_amount,
181 x_acct_exchange_rounding_limit,
182 x_acct_currency_code,
183 x_acct_rate_date,
184 x_acct_rate_type,
185 x_acct_exchange_rate,
186 x_acct_raw_cost,
187 x_project_currency_code,
188 x_project_rate_date,
189 x_project_rate_type,
190 x_project_exchange_rate,
191 x_denom_tp_currency_code,
192 x_denom_transfer_price,
193 x_encumbrance_comment,
194 x_person_id,
195 x_incurred_by_person_id,
196 x_ind_compiled_set_id,
197 x_pa_date,
198 x_gl_date,
199 x_line_num,
200 x_burden_sum_dest_run_id,
201 x_burden_sum_source_run_id,
202 x_org_id );
203
204 open return_rowid;
205 fetch return_rowid into x_rowid;
206 if (return_rowid%notfound) then
207 raise NO_DATA_FOUND; -- should we return something else?
208 end if;
209 close return_rowid;
210
211 -- this assumes the neg amount has already been validated, and
212 -- is matched. unmatched occurs in adjustments
216 update gms_encumbrance_items
213 -- if (((x_quantity < 0) or (x_burden_cost < 0)) and
214 if ((x_amount < 0) and
215 (x_net_zero_adjustment_flag = 'Y')) then
217 set net_zero_adjustment_flag = 'Y'
218 where encumbrance_item_id = x_adjusted_encumbrance_item_id;
219
220 -- Date : 17-JUN-99
221 --
222 -- Earlier the value for parameter encumbrance id was NULL. This
223 -- resulted in the reversing related items getting creating in a
224 -- different encumbrance id which is different from that of the
225 -- source item. Changed the NULL value to the current encumbrance
226 -- id.
227 --
228 /*
229 pa_adjustments.ReverseRelatedItems(x_adjusted_encumbrance_item_id,
230 x_encumbrance_id,
231 'PAXTREPE',
232 X_created_by,
233 X_last_update_login,
234 status );
235 */
236 --
237 --
238 end if;
239 END insert_row;
240 -- =====================================================================================
241
242 procedure update_row (x_rowid in VARCHAR2,
243 x_encumbrance_item_id in NUMBER,
244 x_last_update_date in DATE,
245 x_last_updated_by in NUMBER,
246 x_encumbrance_id in NUMBER,
247 x_task_id in NUMBER,
248 x_encumbrance_item_date in DATE,
249 x_encumbrance_type in VARCHAR2,
250 x_enc_distributed_flag in VARCHAR2,
251 x_amount in NUMBER,
252 x_override_to_organization_id in NUMBER,
253 x_adjusted_encumbrance_item_id in NUMBER,
254 x_net_zero_adjustment_flag in VARCHAR2,
255 x_transferred_from_enc_item_id in NUMBER,
256 x_last_update_login in NUMBER,
257 x_attribute_category in VARCHAR2,
258 x_attribute1 in VARCHAR2,
259 x_attribute2 in VARCHAR2,
260 x_attribute3 in VARCHAR2,
261 x_attribute4 in VARCHAR2,
262 x_attribute5 in VARCHAR2,
263 x_attribute6 in VARCHAR2,
264 x_attribute7 in VARCHAR2,
265 x_attribute8 in VARCHAR2,
266 x_attribute9 in VARCHAR2,
267 x_attribute10 in VARCHAR2,
268 x_orig_transaction_reference in VARCHAR2,
269 x_transaction_source in VARCHAR2,
270 x_project_id in NUMBER,
271 x_source_encumbrance_item_id in NUMBER,
272 x_job_id in NUMBER,
273 x_system_linkage_function in VARCHAR2,
274 x_denom_currency_code in VARCHAR2,
275 x_denom_raw_amount in NUMBER,
276 x_acct_exchange_rounding_limit in NUMBER,
277 x_acct_currency_code in VARCHAR2,
278 x_acct_rate_date in DATE,
279 x_acct_rate_type in VARCHAR2,
280 x_acct_exchange_rate in NUMBER,
281 x_acct_raw_cost in NUMBER,
282 x_project_currency_code in VARCHAR2,
283 x_project_rate_date in DATE,
284 x_project_rate_type in VARCHAR2,
285 x_project_exchange_rate in NUMBER,
286 x_encumbrance_comment in VARCHAR2,
287 x_pa_date in DATE,
288 x_gl_date in DATE ) IS
289 /*
290 x_denom_tp_currency_code in VARCHAR2,
291 x_denom_transfer_price in NUMBER ,
292 x_person_id in NUMBER,
293 x_incurred_by_person_id in NUMBER,
294 x_ind_compiled_set_id in NUMBER,
295 x_line_num in NUMBER,
296 x_burden_sum_dest_run_id in NUMBER,
297 x_burden_sum_source_run_id in NUMBER) IS
298 */
299
300 action VARCHAR2(30);
301 outcome VARCHAR2(100);
302 num_processed NUMBER;
303 num_rejected NUMBER;
304 status NUMBER;
305 l_project_id number; --Bug 5726575
306
307 BEGIN
308 -- need to check status, force user to use adjust if necessary
309
310 --Bug 5693864
311 if x_project_id is null then
312 select project_id
313 into l_project_id
314 from pa_tasks
315 where task_id = x_task_id;
316 end if;
317
318 update gms_encumbrance_items
319 set encumbrance_item_id = x_encumbrance_item_id,
320 last_update_date = x_last_update_date,
321 last_updated_by = x_last_updated_by,
322 encumbrance_id = x_encumbrance_id,
323 task_id = x_task_id,
324 encumbrance_item_date = x_encumbrance_item_date,
325 encumbrance_type = x_encumbrance_type,
326 enc_distributed_flag = x_enc_distributed_flag,
327 amount = x_amount,
328 override_to_organization_id = x_override_to_organization_id,
329 adjusted_encumbrance_item_id = x_adjusted_encumbrance_item_id,
330 net_zero_adjustment_flag = x_net_zero_adjustment_flag,
331 transferred_from_enc_item_id = x_transferred_from_enc_item_id,
332 last_update_login = x_last_update_login,
336 attribute3 = x_attribute3,
333 attribute_category = x_attribute_category,
334 attribute1 = x_attribute1,
335 attribute2 = x_attribute2,
337 attribute4 = x_attribute4,
338 attribute5 = x_attribute5,
339 attribute6 = x_attribute6,
340 attribute7 = x_attribute7,
341 attribute8 = x_attribute8,
342 attribute9 = x_attribute9,
343 attribute10 = x_attribute10,
344 orig_transaction_reference = x_orig_transaction_reference,
345 transaction_source = x_transaction_source,
346 project_id = nvl(x_project_id, l_project_id), --Bug 5693864 x_project_id,
347 source_encumbrance_item_id = x_source_encumbrance_item_id,
348 job_id = x_job_id,
349 system_linkage_function = x_system_linkage_function,
350 denom_currency_code = x_denom_currency_code,
351 denom_raw_amount = x_denom_raw_amount,
352 acct_exchange_rounding_limit = x_acct_exchange_rounding_limit,
353 acct_currency_code = x_acct_currency_code,
354 acct_rate_date = x_acct_rate_date,
355 acct_rate_type = x_acct_rate_type,
356 acct_exchange_rate = x_acct_exchange_rate,
357 acct_raw_cost = x_acct_raw_cost,
358 encumbrance_comment = x_encumbrance_comment ,
359 pa_date = x_pa_date,
360 gl_date = x_gl_date
361 /*
362 project_currency_code = x_project_currency_code,
363 project_rate_date = x_project_rate_date,
364 project_rate_type = x_project_rate_type,
365 project_exchange_rate = x_project_exchange_rate
366 */
367 where rowid = x_rowid;
368
369 -- this assumes the neg amount has already been validated, and
370 -- is matched. unmatched occurs in adjustments
371 -- if (((x_quantity < 0) or (x_burden_cost < 0)) and
372 if ((x_amount < 0) and
373 (x_net_zero_adjustment_flag = 'Y')) then
374 update gms_encumbrance_items
375 set net_zero_adjustment_flag = 'Y'
376 where encumbrance_item_id = x_adjusted_encumbrance_item_id;
377
378 -- Date : 17-JUN-99
379 --
380 -- Earlier the value for parameter encumbrance id was NULL. This
381 -- resulted in the reversing related items getting creating in a
382 -- different encumbrance id which is different from that of the
383 -- source item. Changed the NULL value to the current encumbrance
384 -- id.
385 --
386 pa_adjustments.ReverseRelatedItems(x_adjusted_encumbrance_item_id,
387 x_encumbrance_id,
388 'PAXTREPE',
389 X_last_updated_by,
390 X_last_update_login,
391 status );
392 --
393 --
394 end if;
395
396 END update_row;
397
398 -- =========================================================================================
399 -- Given the encumbrance_item_id, delete the row.
400 -- If deletion of an reversing item occurs, make sure to reset the
401 -- net_zero_adjustment_flag in the reversed item.
402
403 procedure delete_row (x_encumbrance_item_id in NUMBER) is
404
405 cursor check_reversing is
406 select adjusted_encumbrance_item_id from gms_encumbrance_items
407 where encumbrance_item_id = x_encumbrance_item_id;
408
409 cursor check_source is
410 select encumbrance_item_id, adjusted_encumbrance_item_id
411 from gms_encumbrance_items
412 where source_encumbrance_item_id = x_encumbrance_item_id;
413
414 rev_item check_reversing%rowtype;
415 source_item check_source%rowtype;
416
417 BEGIN
418
419 -- reset the adjustment flag.
420 open check_reversing;
421 fetch check_reversing into rev_item;
422 if (rev_item.adjusted_encumbrance_item_id is not null) then
423 update gms_encumbrance_items
424 set net_zero_adjustment_flag = 'N'
425 where encumbrance_item_id = rev_item.adjusted_encumbrance_item_id;
426
427 open check_source ;
428 --
429 -- Previously the following section which deals with related items was
430 -- done based on the assumption that there can exist only one related
431 -- item. So not suprisingly bug# 912209 was logged which states that
432 -- only one of the related item was getting deleted when the source
433 -- item was deleted. Now the deletion of related items sections is
434 -- called in a loop for each of the related items.
435 --
436 LOOP
437 fetch check_source into source_item ;
438 if check_source%notfound then exit ;
439 end if;
440 fetch check_source into source_item ;
441 if (source_item.adjusted_encumbrance_item_id is not null) then
442 update gms_encumbrance_items
443 set net_zero_adjustment_flag = 'N'
444 where encumbrance_item_id = source_item.adjusted_encumbrance_item_id ;
445
446 delete from gms_encumbrance_items
447 where encumbrance_item_id = source_item.encumbrance_item_id;
448 end if ;
449 END LOOP;
450 --
451 -- End section
452 --
453 close check_source ;
454
455 end if;
456
457 delete from gms_encumbrance_items
458 where encumbrance_item_id = x_encumbrance_item_id;
459
460
461 END delete_row;
462
463 -- =======================================================================================================
464 procedure delete_row (x_rowid in VARCHAR2) is
465
466 cursor get_itemid is select encumbrance_item_id from gms_encumbrance_items
467 where rowid = x_rowid;
468 x_encumbrance_item_id NUMBER;
469
470 BEGIN
471 open get_itemid;
472 fetch get_itemid into x_encumbrance_item_id;
473
474 delete_row (x_encumbrance_item_id);
475
476 END delete_row;
477
478
479
480 procedure lock_row (x_rowid in VARCHAR2) is
481 BEGIN
482 null;
483 END lock_row;
484
485 END gms_encumbrance_items_pkg;
[Home] [Help]