[Home] [Help]
PACKAGE BODY: APPS.PO_REQ_DIST_SV
Source
1 PACKAGE BODY po_req_dist_sv AS
2 /* $Header: POXRQD1B.pls 120.8 2010/02/28 14:03:14 ssreekum ship $ */
3 /*===========================================================================
4
5 PROCEDURE NAME: check_unique()
6
7 ===========================================================================*/
8
9 FUNCTION check_unique(x_row_id VARCHAR2, x_distribution_num NUMBER,
10 x_requisition_line_id NUMBER) RETURN BOOLEAN IS
11
12 dummy varchar2(40);
13 x_progress VARCHAR2(3) := NULL;
14
15 BEGIN
16 x_progress := '001';
17
18 select 'that line number exists'
19 into dummy
20 from po_req_distributions
21 where requisition_line_id = x_requisition_line_id
22 and distribution_num = x_distribution_num
23 AND (rowid <> x_row_id
24 OR x_row_id IS NULL);
25
26 RETURN(FALSE); -- there is a row with that distribution_num already
27
28 EXCEPTION
29 WHEN NO_DATA_FOUND THEN
30 RETURN(TRUE);
31 WHEN OTHERS THEN
32 -- dbms_output.put_line('In Exception');
33 po_message_s.sql_error('check_unique', x_progress, sqlcode);
34 RAISE;
35 END check_unique;
36
37
38 /*===========================================================================
39
40 PROCEDURE NAME: check_unique_insert()
41
42 ===========================================================================*/
43
44 PROCEDURE check_unique_insert(x_row_id IN OUT NOCOPY VARCHAR2, x_distribution_num NUMBER,
45 x_requisition_line_id NUMBER) IS
46
47 dummy varchar2(40);
48 x_progress VARCHAR2(3) := NULL;
49
50 BEGIN
51 x_progress := '001';
52
53 select '1'
54 into dummy
55 from dual
56 where not exists (
57 select 'that line number exists'
58 from po_req_distributions
59 where requisition_line_id = x_requisition_line_id
60 and distribution_num = x_distribution_num
61 AND (rowid <> x_row_id
62 OR x_row_id IS NULL));
63
64
65 EXCEPTION
66 WHEN NO_DATA_FOUND THEN
67 po_message_s.app_error('PO_RQ_LINE_NUM_ALREADY_EXISTS');
68 RAISE;
69 WHEN OTHERS THEN
70 -- dbms_output.put_line('In Exception');
71 po_message_s.sql_error('check_unique_insert', x_progress, sqlcode);
72 RAISE;
73 END check_unique_insert;
74
75
76 /*===========================================================================
77
78 FUNCTION NAME: get_max_dist_num()
79
80 ===========================================================================*/
81
82 FUNCTION get_max_dist_num(x_requisition_line_id NUMBER) RETURN NUMBER IS
83
84 x_progress VARCHAR2(3) := NULL;
85 max_dist_num NUMBER;
86
87 cursor C1 is
88 select nvl(max(distribution_num),0)
89 from po_req_distributions
90 where requisition_line_id = x_requisition_line_id;
91
92 BEGIN
93 x_progress := '001';
94 open C1;
95 fetch C1 into max_dist_num;
96 RETURN(max_dist_num);
97 close C1;
98
99 EXCEPTION
100 WHEN OTHERS THEN
101 -- dbms_output.put_line('In Exception');
102 po_message_s.sql_error('get_max_dist_num', x_progress, sqlcode);
103 RAISE;
104 END get_max_dist_num;
105
106
107 /*===========================================================================
108
109 PROCEDURE NAME: select_summary()
110
111 ===========================================================================*/
112
113 PROCEDURE select_summary(x_requisition_line_id IN OUT NOCOPY NUMBER,
114 x_total IN OUT NOCOPY NUMBER) IS
115
116 x_progress VARCHAR2(3) := NULL;
117
118 BEGIN
119 select nvl(sum(req_line_quantity),0)
120 into x_total
121 from po_req_distributions
122 where requisition_line_id = x_requisition_line_id;
123
124
125 EXCEPTION
126 WHEN OTHERS THEN
127 -- dbms_output.put_line('In Exception');
128 po_message_s.sql_error('select_summary', x_progress, sqlcode);
129 RAISE;
130 END select_summary;
131
132
133
134 /*===========================================================================
135
136 PROCEDURE NAME: update_reqs_distributions
137
138 ===========================================================================*/
139
140 PROCEDURE update_reqs_distributions
141 (X_req_header_id IN NUMBER,
142 X_req_line_id IN NUMBER,
143 X_req_control_action IN VARCHAR2,
144 X_req_action_date IN DATE,
145 X_req_control_error_rc IN OUT NOCOPY VARCHAR2) IS
146
147 X_progress VARCHAR2(3) := NULL;
148 X_gl_cancelled_date PO_REQ_DISTRIBUTIONS.gl_cancelled_date%TYPE := NULL;
149 X_gl_closed_date PO_REQ_DISTRIBUTIONS.gl_closed_date%TYPE := NULL;
150
151 BEGIN
152
153 -- dbms_output.put_line('Enter update_reqs_distributions');
154
155 X_req_control_error_rc := '';
156 IF SubStr(X_req_control_action,1,6) = 'CANCEL' THEN
157 IF X_req_action_date is NOT NULL THEN
158 X_gl_cancelled_date := X_req_action_date;
159 ELSE
160 X_gl_cancelled_date := sysdate;
161 END IF;
162 ELSIF X_req_control_action = 'FINALLY CLOSE' THEN
163 IF X_req_action_date is NOT NULL THEN
164 X_gl_closed_date := X_req_action_date;
165 ELSE
166 X_gl_closed_date := sysdate;
167 END IF;
168 END IF;
169
170 IF X_req_header_id is NOT NULL OR
171 X_req_line_id is NOT NULL THEN
172
173 /* The following SQL statement is optimized to update either
174 ** 1. all document lines - if header_id is passed or,
175 ** 2. one document line - if both header_id and line_id are passed.
176 */
177 X_progress := '010';
178 UPDATE PO_REQ_DISTRIBUTIONS
179 SET gl_cancelled_date = nvl(X_gl_cancelled_date, gl_cancelled_date),
180 gl_closed_date = nvl(X_gl_closed_date, gl_closed_date)
181 WHERE requisition_line_id IN
182 (SELECT requisition_line_id
183 FROM po_requisition_lines PORL
184 WHERE PORL.requisition_header_id = X_req_header_id
185 AND PORL.requisition_line_id =
186 nvl(X_req_line_id, PORL.requisition_line_id));
187 ELSE
188 /* DEBUG - show error message */
189 X_req_control_error_rc := 'Y';
190 X_progress := '015';
191 po_message_s.sql_error('update_reqs_distributions', X_progress, sqlcode);
192 END IF;
193
194
195 -- dbms_output.put_line('Exit update_reqs_distributions');
196
197 EXCEPTION
198
199 WHEN NO_DATA_FOUND THEN
200 X_req_control_error_rc := 'Y';
201 po_message_s.sql_error('update_reqs_distributions', X_progress, sqlcode);
202 RAISE;
203 WHEN OTHERS THEN
204 po_message_s.sql_error('update_reqs_distributions', X_progress, sqlcode);
205 RAISE;
206
207 END update_reqs_distributions;
208
209
210
211 /*===========================================================================
212
213 PROCEDURE NAME: val_create_dist()
214
215 ===========================================================================*/
216 /*
217 PROCEDURE val_create_dist() IS
218
219 x_progress VARCHAR2(3) := NULL;
220
221 BEGIN
222
223
224 EXCEPTION
225 WHEN OTHERS THEN
226 po_message.set_name('val_create_dist', x_progress, sqlcode);
227 RAISE;
228
229 END val_create_dist;
230 */
231
232 /*===========================================================================
233
234 PROCEDURE NAME: create_dist_for_modify
235
236 ===========================================================================*/
237
238 PROCEDURE create_dist_for_modify(x_new_req_line_id IN NUMBER,
239 x_orig_req_line_id IN NUMBER,
240 x_new_line_quantity IN NUMBER) IS
241
242 x_progress VARCHAR2(3) := NULL;
243
244 x_distribution_id NUMBER := NULL;
245 x_new_dist_quantity NUMBER;
246 x_orig_line_quantity NUMBER;
247 x_total_dist_qty NUMBER;
248
249 dist_rec po_req_distributions%rowtype;
250 x_rowid VARCHAR2(30);
251
252 -- JFMIP, support for Req Modify when encumbrance is enabled START
253 l_base_currency PO_HEADERS_ALL.currency_code%TYPE;
254 /*Bug4421065 TCA Impact: Removed the obsolete column ap_tax_rounding_rule in po_vendors_sites_all table */
255
256 l_prorated_rec_tax PO_REQ_DISTRIBUTIONS.recoverable_tax%type;
257 l_prorated_nonrec_tax PO_REQ_DISTRIBUTIONS.nonrecoverable_tax%type;
258 -- JFMIP, support for Req Modify when encumbrance is enabled END
259
260 CURSOR C IS
261 SELECT distribution_id
262 FROM po_req_distributions
263 WHERE requisition_line_id = x_orig_req_line_id;
264
265 BEGIN
266
267 /*
268 ** Obtain the line quantity from the original
269 ** line.
270 */
271
272 x_progress := '010';
273
274 SELECT quantity
275 INTO x_orig_line_quantity
276 FROM po_requisition_lines
277 WHERE requisition_line_id = x_orig_req_line_id;
278
279 -- dbms_output.put_line ('Original Line quantity: ' ||
280 -- bug1555260 to_char(x_orig_line_quantity));
281
282 -- JFMIP, support for Req Modify when encumbrance is enabled START
283 l_base_currency := PO_CORE_S2.get_base_currency;
284
285
286 -- JFMIP, support for Req Modify when encumbrance is enabled END
287
288 /*
289 ** Open cursor to loop through all the distributions
290 ** of the original requisition line and create new
291 ** distributions for the new requisition line.
292 */
293
294 FOR CREC IN C LOOP
295
296 x_progress := '020';
297
298 SELECT *
299 INTO dist_rec
300 FROM po_req_distributions
301 WHERE distribution_id = CREC.distribution_id;
302
303
304 /*
305 ** Compute prorated quantity for the new distributions.
306 */
307
308 x_new_dist_quantity := round(((dist_rec.req_line_quantity/
309 x_orig_line_quantity)* x_new_line_quantity),
310 -- JFMIP
311 -- 5);
312 13);
313
314 -- JFMIP, support for Req Modify when encumbrance is enabled START
315 --Prorate and round the tax
316 /*Bug4421065:<R12 eTax Integration> Assigned null values to the below variables since the tax engine will take care of calculating the tax */
317 l_prorated_rec_tax:= null;
318
319 l_prorated_nonrec_tax:= null;
320
321 -- JFMIP, support for Req Modify when encumbrance is enabled END
322
323 /* Clear old distribution id */
324
325 x_distribution_id := NULL;
326
327 -- dbms_output.put_line ('Distribution quantity: ' ||
328 --bug1555260 x_new_dist_quantity);
329
330 x_progress := '030';
331
332 po_req_distributions_pkg1.insert_row (x_rowid,
333 x_distribution_id,
334 dist_rec.last_update_date,
335 dist_rec.last_updated_by,
336 x_new_req_line_id,
337 dist_rec.set_of_books_id,
338 dist_rec.code_combination_id,
339 x_new_dist_quantity,
340 NULL,-- req_line_amount -- <SERVICES FPJ>
341 NULL,-- req_line_currency_amount-- <SERVICES FPJ>
342 dist_rec.last_update_login,
343 dist_rec.creation_date,
344 dist_rec.created_by,
345 dist_rec.encumbered_flag,
346 dist_rec.gl_encumbered_date,
347 dist_rec.gl_encumbered_period_name,
348 dist_rec.gl_cancelled_date,
349 dist_rec.failed_funds_lookup_code,
350 -- JFMIP, support for Req Modify when encumbrance is enabled
351 -- dist_rec.encumbered_amount,
352 0,
353 dist_rec.budget_account_id,
354 dist_rec.accrual_account_id,
355 dist_rec.variance_account_id,
356 dist_rec.prevent_encumbrance_flag,
357 dist_rec.attribute_category,
358 dist_rec.attribute1,
359 dist_rec.attribute2,
360 dist_rec.attribute3,
361 dist_rec.attribute4,
362 dist_rec.attribute5,
363 dist_rec.attribute6,
364 dist_rec.attribute7,
365 dist_rec.attribute8,
366 dist_rec.attribute9,
367 dist_rec.attribute10,
368 dist_rec.attribute11,
369 dist_rec.attribute12,
370 dist_rec.attribute13,
371 dist_rec.attribute14,
372 dist_rec.attribute15,
373 NULL, --<R12 SLA>
374 dist_rec.government_context,
375 dist_rec.project_id,
376 dist_rec.task_id,
377 dist_rec.expenditure_type,
378 dist_rec.project_accounting_context,
379 dist_rec.expenditure_organization_id,
380 dist_rec.gl_closed_date,
381 dist_rec.Distribution_Id, /* <<CLM Partial Funding Code Changes>> */ --dist_rec.source_req_distribution_id,
382 dist_rec.distribution_num,
383 dist_rec.project_related_flag,
384 dist_rec.expenditure_item_date,
385 dist_rec.end_item_unit_number,
386 dist_rec.recovery_rate,
387 -- JFMIP START
388 -- dist_rec.recoverable_tax,
389 -- dist_rec.nonrecoverable_tax,
390 l_prorated_rec_tax,
391 l_prorated_nonrec_tax,
392 -- JFMIP END
393 dist_rec.tax_recovery_override_flag,
394 -- <R12 MOAC Start> bug4548700 added the following to forward contract details to
395 -- newly splitted lines.
396 dist_rec.award_id, --null
397 dist_rec.oke_contract_line_id, --null
398 dist_rec.oke_contract_deliverable_id, --null
399 dist_rec.org_id, -- <R12 MOAC End>
400 dist_rec.Partial_funded_flag /* <<CLM Partial Funding Code Changes>> */
401 );
402
403
404 END LOOP;
405
406 /*
407 ** Obtain the difference in the requisition line
408 ** quantity and the sum of the distributions quantity.
409 ** Add the difference to the first distribution of the
410 ** new line.
411 */
412
413 x_progress := '040';
414
415 SELECT sum (req_line_quantity)
416 INTO x_total_dist_qty
417 FROM po_req_distributions
418 WHERE requisition_line_id = x_new_req_line_id;
419
420 IF (x_total_dist_qty = x_new_line_quantity) THEN
421 return;
422
423 ELSE
424
425 x_progress := '050';
426
427 UPDATE po_req_distributions
428 SET req_line_quantity = req_line_quantity + (x_new_line_quantity -
429 x_total_dist_qty)
430 WHERE distribution_id = (SELECT min(distribution_id)
431 FROM po_req_distributions
432 WHERE requisition_line_id = x_new_req_line_id);
433
434 END IF;
435
436
437 EXCEPTION
438 WHEN OTHERS THEN
439 po_message_s.sql_error('create_dist_for_modify', x_progress, sqlcode);
440 raise;
441
442 END create_dist_for_modify;
443
444 END po_req_dist_sv;