[Home] [Help]
PACKAGE BODY: APPS.PO_NEGOTIATIONS4_PVT
Source
1 PACKAGE BODY PO_NEGOTIATIONS4_PVT AS
2 /* $Header: POXVNG4B.pls 120.23.12020000.3 2013/02/10 13:21:13 vegajula ship $ */
3
4 TYPE auction_header_id_tbl_type is TABLE OF
5 po_req_split_lines_gt.auction_header_id%type INDEX BY BINARY_INTEGER;
6 TYPE bid_number_tbl_type is TABLE OF
7 po_req_split_lines_gt.bid_number%type INDEX BY BINARY_INTEGER;
8 TYPE bid_line_number_tbl_type is TABLE OF
9 po_req_split_lines_gt.bid_line_number%type INDEX BY BINARY_INTEGER;
10 TYPE requisition_header_id_tbl_type is TABLE OF
11 po_req_split_lines_gt.requisition_header_id%type INDEX BY BINARY_INTEGER;
12 TYPE requisition_line_id_tbl_type is TABLE OF
13 po_req_split_lines_gt.requisition_line_id%type INDEX BY BINARY_INTEGER;
14 TYPE allocated_qty_tbl_type is TABLE OF
15 po_req_split_lines_gt.allocated_qty%type INDEX BY BINARY_INTEGER;
16 TYPE new_req_line_id_tbl_type is TABLE OF
17 po_req_split_lines_gt.new_req_line_id%type INDEX BY BINARY_INTEGER;
18 TYPE new_line_num_tbl_type is TABLE OF
19 po_req_split_lines_gt.new_line_num%type INDEX BY BINARY_INTEGER;
20 TYPE totallc_req_line_qty_tbl_type is TABLE OF
21 po_req_split_lines_gt.total_alloc_req_line_qty%type INDEX BY BINARY_INTEGER;
22 TYPE requisition_line_qty_tbl_type is TABLE OF
23 po_req_split_lines_gt.requisition_line_qty%type INDEX BY BINARY_INTEGER;
24 TYPE min_bid_number_tbl_type is TABLE OF
25 po_req_split_lines_gt.min_bid_number%type INDEX BY BINARY_INTEGER;
26 TYPE record_status_tbl_type is TABLE OF
27 po_req_split_lines_gt.record_status%type INDEX BY BINARY_INTEGER;
28 TYPE row_id_tbl_type is TABLE OF rowid INDEX BY BINARY_INTEGER;
29 TYPE min_dist_id_tbl_type is TABLE OF
30 po_req_distributions.distribution_id%type INDEX BY BINARY_INTEGER;
31 TYPE org_id_tbl_type is TABLE OF
32 po_headers.org_id%type INDEX BY BINARY_INTEGER;
33 TYPE round_tax_tbl_type is TABLE OF
34 po_req_distributions.recoverable_tax%type INDEX BY BINARY_INTEGER;
35 -- Bug 4723367 START
36 TYPE encumbrance_flag_tbl_type is TABLE OF
37 financials_system_parameters.req_encumbrance_flag%type
38 INDEX BY BINARY_INTEGER;
39 -- Bug 4723367 END
40
41 G_PKG_NAME CONSTANT varchar2(30) := 'PO_NEGOTIATIONS4_PVT';
42 G_MODULE_PREFIX CONSTANT VARCHAR2(60) := 'po.plsql.' || G_PKG_NAME || '.';
43 G_FND_DEBUG VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
44 G_FND_DEBUG_LEVEL VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_LEVEL'),'0');
45
46 PROCEDURE Print_Global_Table(p_module IN VARCHAR2) IS
47 requisition_header_id_dbg_tbl requisition_header_id_tbl_type;
48 requisition_line_id_dbg_tbl requisition_line_id_tbl_type;
49 bid_number_dbg_tbl bid_number_tbl_type;
50 bid_line_number_dbg_tbl bid_line_number_tbl_type;
51 allocated_qty_dbg_tbl allocated_qty_tbl_type;
52 requisition_line_qty_dbg_tbl requisition_line_qty_tbl_type;
53 auction_header_id_dbg_tbl auction_header_id_tbl_type;
54 new_req_line_id_dbg_tbl new_req_line_id_tbl_type;
55 new_line_num_dbg_tbl new_line_num_tbl_type;
56 totalloc_req_line_qty_dbg_tbl totallc_req_line_qty_tbl_type;
57 min_bid_number_dbg_tbl min_bid_number_tbl_type;
58 record_status_dbg_tbl record_status_tbl_type;
59
60
61 BEGIN
62
63 SELECT prs.requisition_header_id,
64 prs.requisition_line_id,
65 prs.auction_header_id,
66 prs.bid_number,
67 prs.bid_line_number,
68 prs.allocated_qty,
69 prs.requisition_line_qty,
70 prs.new_req_line_id,
71 prs.new_line_num,
72 prs.total_alloc_req_line_qty,
73 prs.min_bid_number,
74 prs.record_status
75 BULK COLLECT INTO
76 requisition_header_id_dbg_tbl,
77 requisition_line_id_dbg_tbl,
78 auction_header_id_dbg_tbl,
79 bid_number_dbg_tbl,
80 bid_line_number_dbg_tbl,
81 allocated_qty_dbg_tbl,
82 requisition_line_qty_dbg_tbl,
83 new_req_line_id_dbg_tbl,
84 new_line_num_dbg_tbl,
85 totalloc_req_line_qty_dbg_tbl,
86 min_bid_number_dbg_tbl,
87 record_status_dbg_tbl
88 FROM po_req_split_lines_gt prs
89 ORDER BY prs.requisition_header_id,prs.requisition_line_id;
90
91 FOR l_dbg_index in 1.. requisition_line_id_dbg_tbl.COUNT loop
92 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
93 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, p_module,
94 'auction_header_id:'||to_char(auction_header_id_dbg_tbl(l_dbg_index))||','
95 ||'bid_number:'||to_char(bid_number_dbg_tbl(l_dbg_index))||','
96 ||'bid_line_number:'||to_char(bid_line_number_dbg_tbl(l_dbg_index))||','
97 ||'requisition_header_id:'||to_char(requisition_header_id_dbg_tbl(l_dbg_index))||','
98 ||'requisition_line_id:'||to_char(requisition_line_id_dbg_tbl(l_dbg_index))||','
99 ||'allocated_qty:'||to_char(allocated_qty_dbg_tbl(l_dbg_index))||','
100 ||'requisition_line_qty:'||to_char(requisition_line_qty_dbg_tbl(l_dbg_index))||','
101 ||'new_req_line_id:'||to_char(new_req_line_id_dbg_tbl(l_dbg_index))||','
102 ||'new_line_num:'||to_char(new_line_num_dbg_tbl(l_dbg_index))||','
103 ||'total_alloc_req_line_qty:'||to_char(totalloc_req_line_qty_dbg_tbl(l_dbg_index))||','
104 ||'min_bid_number:'||to_char(min_bid_number_dbg_tbl(l_dbg_index))||','
105 ||'record_status:'||record_status_dbg_tbl(l_dbg_index));
106 END IF;
107 END LOOP;
108 END;
109
110 /**
111 * Private Procedure: Split_RequisitionLines
112 * Requires: API message list has been initialized if p_init_msg_list is false.
113 * Modifies: Inserts new req lines and their distributions, For parent
114 * req lines, update requisition_lines table to modified_by_agent_flag='Y'.
115 * Also sets prevent encumbrace flag to 'Y' in the po_req_distributions table.
116 * Effects: This api split the requisition lines, if needed, depending on the
117 * allocation done by the sourcing user. This api uses a global temp. table
118 * to massage the input given by sourcing and inserts records into
119 * po_requisition_lines_all and po_req_distributions_all table. This api also
120 * handles the encumbrace effect of splitting requisition lines. This api would
121 * be called from ORacle sourcing workflow.
122 *
123 * Returns:
124 * x_return_status - FND_API.G_RET_STS_SUCCESS if action succeeds
125 * FND_API.G_RET_STS_ERROR if action fails
126 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
127 * x_msg_count returns count of messages in the stack.
128 * x_msg_data returns message only if 1 message.
129 *
130 * Possible values for PO_REQ_SPLIT_LINES_GT.record_status:
131 * 'S' - Split;
132 * 'N' - New Line(for the remainder which will go back to pool);
133 * 'C' - Cancelled, finally closed, withdrawn, and Req lines with 0 quantity;
134 * 'E' - Equal Allocation;
135 * 'I' - No Allocation;
136 * 'T' - Line type with value basis 'RATE' and 'FIXED PRICE'
137 *
138 */
139
140 PROCEDURE Split_RequisitionLines
141 ( p_api_version IN NUMBER ,
142 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE ,
143 p_commit IN VARCHAR2 :=FND_API.G_FALSE ,
144 x_return_status OUT NOCOPY VARCHAR2 ,
145 x_msg_count OUT NOCOPY NUMBER ,
146 x_msg_data OUT NOCOPY VARCHAR2 ,
147 p_auction_header_id IN NUMBER
148 )
149 IS
150
151 l_api_name CONSTANT varchar2(30) := 'SPLIT_REQUISITIONLINES';
152 l_log_head CONSTANT VARCHAR2(100) := G_MODULE_PREFIX||l_api_name;
153 l_api_version CONSTANT NUMBER := 1.0;
154
155 l_module VARCHAR2(100);
156 l_progress VARCHAR2(3);
157
158 requisition_line_id_tbl requisition_line_id_tbl_type;
159 min_bid_number_tbl min_bid_number_tbl_type;
160 total_alloc_req_line_qty_tbl totallc_req_line_qty_tbl_type;
161
162
163
164 --declare the result tables.
165 auction_header_id_rslt_tbl auction_header_id_tbl_type;
166 bid_number_rslt_tbl bid_number_tbl_type;
167 bid_line_number_rslt_tbl bid_line_number_tbl_type;
168 requisition_header_id_rslt_tbl requisition_header_id_tbl_type;
169 requisition_line_id_rslt_tbl requisition_line_id_tbl_type;
170 allocated_qty_rslt_tbl allocated_qty_tbl_type;
171 new_req_line_id_rslt_tbl new_req_line_id_tbl_type;
172 new_line_num_rslt_tbl new_line_num_tbl_type;
173 totallc_req_line_qty_rslt_tbl totallc_req_line_qty_tbl_type;
174 requisition_line_qty_rslt_tbl requisition_line_qty_tbl_type;
175 min_bid_number_rslt_tbl min_bid_number_tbl_type;
176 record_status_rslt_tbl record_status_tbl_type;
177 encumbrance_flag_rslt_tbl encumbrance_flag_tbl_type; -- Bug 4723367
178
179 --define table type variables for requisition line num calculation.
180 requisition_header_id_lnm_tbl requisition_header_id_tbl_type;
181 requisition_line_id_lnm_tbl requisition_line_id_tbl_type;
182 bid_number_lnm_tbl bid_number_tbl_type;
183 bid_line_number_lnm_tbl bid_line_number_tbl_type;
184 new_line_num_lnm_tbl new_line_num_tbl_type;
185 row_id_lnm_tbl row_id_tbl_type;
186
187 --define table type variables for distribution qty rounding.
188 req_line_id_round_tbl new_line_num_tbl_type;
189 min_dist_id_round_tbl min_dist_id_tbl_type;
190 sum_req_line_qty_round_tbl allocated_qty_tbl_type;
191 req_line_qty_round_tbl requisition_line_qty_tbl_type;
192
193 l_return_status VARCHAR2(1);
194 l_msg_count NUMBER;
195 l_msg_data VARCHAR2(2000);
196
197 -- SQL What:This cursor Locks the requisition lines the api is going to process
198 -- SQL Why :This locking ensures that the records are not touched by any other
199 -- transactions.Opening the cursor keeps the records locked till the
200 -- transaction control happens.
201 CURSOR LockReqLines_Cursor IS
202 SELECT prl.requisition_line_id,quantity
203 FROM po_requisition_lines_all prl, --<Sourcing 11.5.10+>
204 po_req_split_lines_gt prs
205 WHERE prl.requisition_line_id = prs.requisition_line_id
206 FOR UPDATE OF prl.quantity NOWAIT;
207
208 old_requisition_header_id po_req_split_lines_gt.requisition_header_id%type:=0;
209 l_serial_num number;
210 l_line_num_index number;
211 l_req_encumbrance_flag financials_system_parameters.req_encumbrance_flag%type;
212
213 -- <FPI JFMIP Req Split>
214 l_online_report_id PO_ONLINE_REPORT_TEXT.online_report_id%TYPE;
215
216 --<Sourcing 11.5.10+> Define variables needed for context switching
217 l_old_line_requesting_ou_id PO_REQUISITION_LINES_ALL.org_id%TYPE;
218 l_line_requesting_ou_id PO_REQUISITION_LINES_ALL.org_id%TYPE;
219 l_current_ou_id PO_REQUISITION_LINES_ALL.org_id%TYPE;
220 l_org_context_changed VARCHAR2(1) := 'N';
221
222 -- bug 5249299 <variable addition START>
223 l_project_id po_req_distributions_all.project_id%TYPE;
224 l_task_id po_req_distributions_all.task_id%TYPE;
225 l_award_id po_req_distributions_all.award_id%TYPE;
226 l_expenditure_type po_req_distributions_all.expenditure_type%TYPE;
227 l_expenditure_item_date po_req_distributions_all.expenditure_item_date%TYPE;
228 l_distribution_id po_req_distributions_all.distribution_id%TYPE;
229 l_award_set_id po_req_distributions_all.award_id%TYPE;
230 l_status VARCHAR2(1);
231
232 CURSOR l_req_dist_proj_csr(l_req_line_id number) IS
233 SELECT distribution_id,
234 project_id,
235 task_id,
236 award_id,
237 expenditure_type,
238 expenditure_item_date
239 FROM po_req_distributions_all
240 WHERE requisition_line_id = l_req_line_id;
241
242 l_req_dist_proj_rec l_req_dist_proj_csr%ROWTYPE;
243 -- bug 5249299 <variable addition END>
244
245 --<R12 eTax Integration> Cursor to find requisition_header_id's of all
246 -- requisition lines being processed
247 CURSOR req_header_id_csr IS
248 SELECT DISTINCT prs.requisition_header_id
249 FROM po_req_split_lines_gt prs;
250
251 l_recrt_req_bal VARCHAR2(1); -- <Bug 6962281>
252
253 BEGIN
254
255 l_progress :='000';
256 l_module := G_MODULE_PREFIX||l_api_name||'.'||'000'||'.';
257 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
258 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Entering');
259 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module,
260 'Entering ' || G_PKG_NAME || '.' || l_api_name);
261 END IF;
262
263 SAVEPOINT Split_RequisitionLines_PVT;
264
265 l_progress :='010';
266 l_module := G_MODULE_PREFIX||l_api_name||'.'||'010'||'.';
267 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
268 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
269 'Compatible_API_Call ');
270 END IF;
271
272 IF NOT FND_API.Compatible_API_Call
273 (
274 l_api_version,
275 p_api_version,
276 l_api_name,
277 G_PKG_NAME
278 )
279 THEN
280 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
281 END IF;
282
283
284 l_progress :='020';
285 l_module := G_MODULE_PREFIX||l_api_name||'.'||'020'||'.';
286 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
287 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
288 'OPEN LockReqLines_Cursor ');
289 END IF;
290 -- Lock the requisition lines the api is going to process
291 OPEN LockReqLines_Cursor;
292 NULL;
293 CLOSE LockReqLines_Cursor;
294
295
296
297 -- SQL What:update the temp table with requisition line quantity and
298 -- mark the lines which have been cancelled
299 -- SQL Why :Requsition line quantity is required later in the process.
300 -- especially to help bulk processing.
301 BEGIN
302
303 l_progress :='030';
304 l_module := G_MODULE_PREFIX||l_api_name||'.'||'030'||'.';
305 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
306 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
307 'before update po_req_split_lines_gt for quantity and
308 status ');
309 END IF;
310
311 --bug# 2729465 mark finally closed lines also with a status 'C'
312 --Code impact is minimal when all these lines to be discarded are marked
313 --with a single status.
314 --Also removed an unnecessary join using requisition_header_id
315 UPDATE po_req_split_lines_gt prs
316 SET (prs.requisition_line_qty,
317 prs.record_status)=
318 (SELECT quantity,
319 --decode(cancel_flag,'Y','C',null)
320 decode(cancel_flag,'Y','C',decode(closed_code,'FINALLY CLOSED',
321 'C',NULL))
322 FROM po_requisition_lines_all prl --<Sourcing 11.5.10+>
323 WHERE prl.requisition_line_id=prs.requisition_line_id
324 );
325
326 l_progress :='031';
327 l_module := G_MODULE_PREFIX||l_api_name||'.'||'031'||'.';
328 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
329 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
330 'after update po_req_split_lines_gt for quantity and
331 status; updated '||sql%rowcount||' rows');
332 END IF;
333
334
335 EXCEPTION
336
337 WHEN OTHERS THEN
338 po_message_s.sql_error('Exception of Split_requisitionLines()',
339 l_progress , sqlcode);
340 FND_MSG_PUB.Add;
341 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
342 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_module,
343 'Exception: update po_req_split_lines_gt for
344 quantity and status ');
345 END IF;
346 RAISE;
347
348 END;
349
350 --do the dump of input values if the log level is statement.
351 l_progress :='032';
352 l_module := G_MODULE_PREFIX||l_api_name||'.'||'032'||'.';
353 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL=FND_LOG.LEVEL_STATEMENT THEN
354 Print_Global_Table(l_module);
355 END IF;
356
357 --<BEGIN bug# 2729465> withdrawn lines also with a status 'C'
358 --Code impact is minimal when all these lines to be discarded are marked
359 --with a single status.
360 BEGIN
361
362 l_progress :='035';
363 l_module := G_MODULE_PREFIX||l_api_name||'.'||'035'||'.';
364 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
365 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
366 'before update po_req_split_lines_gt for withdrawn lines');
367 END IF;
368
369 -- SQL What:update the withdrawn lines in the temp table as 'C'
370 -- SQL Why :These lines are not processed.
371 UPDATE po_req_split_lines_gt prs
372 SET prs.record_status='C'
373 WHERE NOT EXISTS
374 (SELECT requisition_line_id
375 FROM po_requisition_lines_all prl --<Sourcing 11.5.10+>
376 WHERE prl.requisition_line_id= prs.requisition_line_id
377 );
378
379 l_progress :='036';
380 l_module := G_MODULE_PREFIX||l_api_name||'.'||'036'||'.';
381 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
382 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
383 'after update po_req_split_lines_gt for withdrawn lines;
384 updated '||sql%rowcount||' rows');
385 END IF;
386
387 EXCEPTION
388
389 WHEN OTHERS THEN
390 po_message_s.sql_error('Exception of Split_requisitionLines()',
391 l_progress , sqlcode);
392 FND_MSG_PUB.Add;
393 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
394 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_module,
395 'Exception: update po_req_split_lines_gt for
396 withdrawn lines ');
397 END IF;
398 RAISE;
399
400 END;
401 --<END bug# 2729465>
402
403
404 -- <SERVICES FPJ START> Mark all Services Lines with a status of 'T'.
405 -- ( These lines are bypassed during the split, but will still be considered
406 -- during the bid association. )
407 --
408 BEGIN
409
410 l_progress :='037';
411 l_module := G_MODULE_PREFIX||l_api_name||'.'||'037'||'.';
412 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
413 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
414 'before update po_req_split_lines_gt for Services lines');
415 END IF;
416
417 -- SQL What: Update Services Lines in the Global Temp Table as 'T'.
418 -- SQL Why : Services Lines are bypassed during the splitting.
419 --
420 UPDATE po_req_split_lines_gt PRS
421 SET ( PRS.record_status
422 , PRS.new_req_line_id ) = ( SELECT 'T'
423 , PRL.requisition_line_id
424 FROM po_requisition_lines_all PRL
425 , po_line_types_b PLT
426 WHERE PRL.requisition_line_id = PRS.requisition_line_id
427 AND PRL.line_type_id = PLT.line_type_id
428 AND PLT.order_type_lookup_code IN ('RATE','FIXED PRICE')
429 )
430 -- Bug 3345861: without the following WHERE clause, lines with record_status 'C'
431 -- will be overwritten
432 WHERE nvl(PRS.record_status, 'NOVAL') <> 'C';
433
434 l_progress :='038';
435 l_module := G_MODULE_PREFIX||l_api_name||'.'||'038'||'.';
436 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
437 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
438 'after update po_req_split_lines_gt for Services lines;
439 updated '||sql%rowcount||' rows');
440 END IF;
441
442 EXCEPTION
443
444 WHEN OTHERS THEN
445 po_message_s.sql_error('Exception of Split_requisitionLines()', l_progress , sqlcode);
446 FND_MSG_PUB.Add;
447 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
448 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_module, 'Exception: update po_req_split_lines_gt for Services lines');
449 END IF;
450 RAISE;
451
452 END;
453 -- <SERVICES FPJ END>
454
455
456 -- <CLM CHANGES START - DEMO FLOW> Mark all CLM Lines with a status of 'T'.
457 -- ( These lines are bypassed during the split, but will still be considered
458 -- during the bid association. )
459 --
460 BEGIN
461
462 l_progress :='039';
463 l_module := G_MODULE_PREFIX||l_api_name||'.'||'039'||'.';
464 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
465 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
466 'before update po_req_split_lines_gt for CLM lines');
467 END IF;
468
469 -- SQL What: Update Services Lines in the Global Temp Table as 'T'.
470 -- SQL Why : Services Lines are bypassed during the splitting.
471 --
472 UPDATE po_req_split_lines_gt PRS
473 SET ( PRS.record_status
474 , PRS.new_req_line_id ) = ( SELECT 'T'
475 , PRL.requisition_line_id
476 FROM po_requisition_lines_all PRL
477 , po_requisition_headers_all PRH
478 WHERE PRL.requisition_line_id = PRS.requisition_line_id
479 AND PRH.requisition_header_id = PRS.requisition_header_id
480 AND Nvl(PRH.FEDERAL_FLAG,'N') = 'Y'
481 )
482 WHERE nvl(PRS.record_status, 'NOVAL') <> 'C'
483 AND EXISTS (SELECT 'Y'
484 FROM po_requisition_headers_all PRH1
485 where PRH1.requisition_header_id = PRS.requisition_header_id
486 AND Nvl(PRH1.FEDERAL_FLAG,'N') = 'Y');
487
488 l_progress :='039';
489 l_module := G_MODULE_PREFIX||l_api_name||'.'||'039'||'.';
490 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
491 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
492 'after update po_req_split_lines_gt for CLM lines;
493 updated '||sql%rowcount||' rows');
494 END IF;
495
496 EXCEPTION
497
498 WHEN OTHERS THEN
499 po_message_s.sql_error('Exception of Split_requisitionLines()', l_progress , sqlcode);
500 FND_MSG_PUB.Add;
501 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
502 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_module, 'Exception: update po_req_split_lines_gt for CLM lines');
503 END IF;
504 RAISE;
505
506 END;
507 -- <CLM CHANGES END - DEMO FLOW>
508
509
510 BEGIN
511
512 l_progress :='040';
513 l_module := G_MODULE_PREFIX||l_api_name||'.'||'040'||'.';
514 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
515 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
516 'before select sum of allocated_qty and min of bid_number
517 ');
518 END IF;
519
520 -- SQL What: Select to populate the temp table with total allocated qty for
521 -- a requisition line and the minimum bid number
522 -- ( Do not include Services Lines ). -- <SERVICES FPJ>
523 -- SQL Why : This is required later in the process.
524 -- especially to help bulk processing.
525 --Bug#2728152 added nvl around sum(allocated_qty)
526 SELECT requisition_line_id,nvl(sum(allocated_qty),0),min(bid_number)
527 BULK COLLECT INTO
528 requisition_line_id_tbl,
529 total_alloc_req_line_qty_tbl,
530 min_bid_number_tbl
531 FROM po_req_split_lines_gt
532 WHERE nvl(record_status,'NOVAL') NOT IN ('C','T') -- <SERVICES FPJ>
533 GROUP BY requisition_line_id;
534
535 l_progress :='041';
536 l_module := G_MODULE_PREFIX||l_api_name||'.'||'041'||'.';
537 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
538 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
539 'after select sum of allocated_qty and min of bid_number;
540 selected '||requisition_line_id_tbl.COUNT||' rows');
541 END IF;
542
543 EXCEPTION
544
545 WHEN OTHERS THEN
546 po_message_s.sql_error('Exception of Split_requisitionLines()',
547 l_progress , sqlcode);
548 FND_MSG_PUB.Add;
549 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
550 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_module,
551 'Exception: select sum of allocated_qty and min of
552 bid_number ');
553 END IF;
554
555 RAISE;
556
557 END;
558
559
560 BEGIN
561
562 l_progress :='050';
563 l_module := G_MODULE_PREFIX||l_api_name||'.'||'050'||'.';
564 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
565 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
566 'before updating po_req_split_lines_gt with sum of
567 allocated_qty and min of bid_number etc..
568 ');
569 END IF;
570
571 -- SQL What:Populate the temp table with total allocated qty for a
572 -- requisition line and the minimum bid number
573 -- SQL Why :This is required later in the process.
574 -- especially to help bulk processing.
575
576 FORALL qty_rollup_index in 1.. requisition_line_id_tbl.COUNT
577 UPDATE po_req_split_lines_gt
578 SET min_bid_number= min_bid_number_tbl(qty_rollup_index),
579 total_alloc_req_line_qty=
580 total_alloc_req_line_qty_tbl(qty_rollup_index),
581 record_status =decode(nvl(allocated_qty,0),requisition_line_qty,
582 'E',0,'I',
583 -- Bug 3345861: Do not split lines where requisition_line_qty is 0
584 -- Assign 'C' as the record_status to such lines
585 decode(requisition_line_qty, 0, 'C', 'S')),
586 new_req_line_id=decode(nvl(allocated_qty,0),requisition_line_qty,
587 requisition_line_id,0,null,po_requisition_lines_s.nextval)
588 WHERE requisition_line_id = requisition_line_id_tbl(qty_rollup_index);
589
590 l_progress :='051';
591 l_module := G_MODULE_PREFIX||l_api_name||'.'||'050'||'.';
592 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
593 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
594 'after updating po_req_split_lines_gt with sum of
595 allocated_qty and min of bid_number etc..; updated '
596 || sql%rowcount ||' rows');
597 END IF;
598
599
600 EXCEPTION
601
602 WHEN OTHERS THEN
603 po_message_s.sql_error('Exception of Split_requisitionLines()',
604 l_progress , sqlcode);
605 FND_MSG_PUB.Add;
606 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
607 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_module,
608 'Exception: updating po_req_split_lines_gt with sum of
609 allocated_qty and min of bid_number etc.. ');
610 END IF;
611 RAISE;
612
613 END;
614
615
616 BEGIN
617
618 l_progress :='060';
619 l_module := G_MODULE_PREFIX||l_api_name||'.'||'060'||'.';
620 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
621 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
622 'before inserting remainder req lines into
623 po_req_split_lines_gt ');
624 END IF;
625
626 -- <Bug 6962281>
627 -- Get the value of profile PO_RECRT_POST_AWRD_AMT_REQ_BAL, i.e.
628 -- "PO: Recreate Post Award Amount Based Req Line Balance"
629 FND_PROFILE.get('PO_RECRT_POST_AWRD_AMT_REQ_BAL', l_recrt_req_bal);
630
631 -- SQL What: Make entry for the new remainder req lines in the temp table
632 -- ( which are not consumed or Services Lines )
633 -- SQL Why : These rows stand for the remainder req lines to be created
634 -- in the po_requisition_lines_all table.
635 -- <Bug 6962281>
636 -- For Amount based lines, new req line should be created only if profile
637 -- PO_RECRT_POST_AWRD_AMT_REQ_BAL value is Y.
638 -- Added join conditions with tables po_requisition_lines_all and
639 -- po_line_types_b.
640 INSERT INTO po_req_split_lines_gt
641 ( auction_header_id,
642 bid_number,
643 bid_line_number,
644 requisition_header_id,
645 requisition_line_id,
646 allocated_qty,
647 new_req_line_id,
648 total_alloc_req_line_qty,
649 requisition_line_qty,
650 min_bid_number,
651 record_status
652 )
653 SELECT prsl.auction_header_id,
654 NULL,
655 prsl.bid_line_number,
656 prsl.requisition_header_id,
657 prsl.requisition_line_id,
658 (prsl.requisition_line_qty - prsl.total_alloc_req_line_qty),
659 po_requisition_lines_s.nextval,
660 NULL,
661 prsl.requisition_line_qty,
662 NULL,
663 'N'
664 FROM po_req_split_lines_gt prsl,
665 po_requisition_lines_all prl,
666 po_line_types_b plt
667 WHERE prsl.total_alloc_req_line_qty < prsl.requisition_line_qty
668 AND prsl.record_status NOT IN ('I','T') -- <SERVICES FPJ>
669 AND prsl.bid_number = prsl.min_bid_number
670 AND prsl.requisition_line_id = prl.requisition_line_id
671 AND prl.line_type_id = plt.line_type_id
672 AND DECODE(plt.order_type_lookup_code,
673 'AMOUNT', l_recrt_req_bal,
674 'Y') = 'Y';
675
676 l_progress :='061';
677 l_module := G_MODULE_PREFIX||l_api_name||'.'||'060'||'.';
678 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
679 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
680 'after inserting remainder req lines into
681 po_req_split_lines_gt inserted '||sql%rowcount||' rows');
682 END IF;
683
684 EXCEPTION
685
686 WHEN OTHERS THEN
687 po_message_s.sql_error('Exception of Split_requisitionLines()',
688 l_progress , sqlcode);
689 FND_MSG_PUB.Add;
690 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
691 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_module,
692 'Exception: inserting remainder req lines into
693 po_req_split_lines_gt ');
694 END IF;
695 RAISE;
696 END;
697
698
699 BEGIN
700
701 l_progress :='070';
702 l_module := G_MODULE_PREFIX||l_api_name||'.'||'070'||'.';
703 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
704 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
705 'before stamping bid info for equal allocation ');
706 END IF;
707
708 -- SQL What:update the po_requisition_lines_all table to stamp bid info for
709 -- lines with Equal allocation (include Services Lines)
710 -- SQL Why :We need this update only for equal award. For other cases
711 -- we pass in the bid info through the insert statement.
712 UPDATE po_requisition_lines_all prl --<Sourcing 11.5.10+>
713 SET (bid_number,
714 bid_line_number)=
715 (SELECT prs.bid_number,
716 prs.bid_line_number
717 FROM po_req_split_lines_gt prs
718 WHERE prl.requisition_line_id=prs.requisition_line_id
719 AND prs.record_status IN ('E','T') -- <SERVICES FPJ>
720 )
721 WHERE prl.requisition_line_id in
722 (SELECT prs1.requisition_line_id
723 FROM po_req_split_lines_gt prs1
724 WHERE prs1.record_status IN ('E','T') ); -- <SERVICES FPJ>
725
726 l_progress :='071';
727 l_module := G_MODULE_PREFIX||l_api_name||'.'||'071'||'.';
728 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
729 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
730 'before stamping bid info for equal allocation updated'
731 || sql%rowcount||' requisition lines ');
732 END IF;
733
734 EXCEPTION
735
736 WHEN OTHERS THEN
737 po_message_s.sql_error('Exception of Split_requisitionLines()',
738 l_progress , sqlcode);
739 FND_MSG_PUB.Add;
740 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
741 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_module,
742 'Exception: stamping bid info for equal allocation
743 ');
744 END IF;
745 RAISE;
746
747 END;
748
749
750 BEGIN
751
752 l_progress :='080';
753 l_module := G_MODULE_PREFIX||l_api_name||'.'||'080'||'.';
754 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
755 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
756 'before updating po_req_split_lines_gt with max linenum');
757 END IF;
758
759 --SQL What:update the temp table with the max line number for each
760 -- requisition_header_id
761 --SQL Why :This is required to calculate the line numbers when creating
762 -- the new requisition lines
763 UPDATE po_req_split_lines_gt prs
764 SET prs.new_line_num=
765 (SELECT max(prl.line_num)
766 FROM po_requisition_lines_all prl --<Sourcing 11.5.10+>
767 WHERE prl.requisition_header_id=prs.requisition_header_id)
768 WHERE prs.record_status in ('S','N');
769
770 l_progress :='081';
771 l_module := G_MODULE_PREFIX||l_api_name||'.'||'081'||'.';
772 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
773 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
774 'after updating po_req_split_lines_gt with max linenum;
775 Updated '|| sql%rowcount||' rows ');
776 END IF;
777
778 EXCEPTION
779
780 WHEN OTHERS THEN
781 po_message_s.sql_error('Exception of Split_requisitionLines()',
782 l_progress , sqlcode);
783 FND_MSG_PUB.Add;
784 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
785 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_module,
786 'Exception: updating po_req_split_lines_gt with max
787 line num');
788 END IF;
789 RAISE;
790
791 END;
792
793
794 BEGIN
795
796 l_progress :='090';
797 l_module := G_MODULE_PREFIX||l_api_name||'.'||'090'||'.';
798 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
799 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
800 'before selecting rowid and new_line_num to memory ');
801 END IF;
802
803 -- SQL What:Add incremental numbers to the new_line_num resetting at
804 -- requisition_header_id level
805 -- SQL Why :This is required to calculate the line numbers when creating
806 -- the new requisition lines. We already have max(line_num) in
807 -- new_line_num column. Add incremental numbers to make the
808 -- new_line_num unique.
809 -- We could not achieve this through a single update as more
810 -- than one new requisition lines could have the same
811 -- requisition_line_id as its parent.
812 SELECT prs.requisition_header_id,
813 prs.requisition_line_id,
814 prs.bid_number,
815 prs.bid_line_number,
816 prs.new_line_num,
817 prs.rowid
818 BULK COLLECT INTO
819 requisition_header_id_lnm_tbl,
820 requisition_line_id_lnm_tbl,
821 bid_number_lnm_tbl,
822 bid_line_number_lnm_tbl,
823 new_line_num_lnm_tbl,
824 row_id_lnm_tbl
825 FROM po_req_split_lines_gt prs
826 WHERE prs.record_status in ('S','N')
827 ORDER BY prs.requisition_header_id,prs.requisition_line_id;
828
829 l_progress :='091';
830 l_module := G_MODULE_PREFIX||l_api_name||'.'||'091'||'.';
831 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
832 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
833 'after selecting rowid and new_line_num to memory;
834 selected '||row_id_lnm_tbl.COUNT||' rows');
835 END IF;
836
837 EXCEPTION
838 WHEN OTHERS THEN
839
840 po_message_s.sql_error('Exception of Split_requisitionLines()',
841 l_progress , sqlcode);
842 FND_MSG_PUB.Add;
843 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
844 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_module,
845 'Exception: selecting rowid and new_line_num to memory '
846 );
847 END IF;
848 RAISE;
849
850 END;
851
852
853 BEGIN
854
855 l_progress :='100';
856 l_module := G_MODULE_PREFIX||l_api_name||'.'||'100'||'.';
857 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
858 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
859 'before loop: Assign incremental numbers to line num ');
860 END IF;
861
862 FOR l_line_num_index in 1.. requisition_line_id_lnm_tbl.COUNT
863 LOOP
864
865 IF requisition_header_id_lnm_tbl(l_line_num_index)
866 <>old_requisition_header_id
867 THEN
868 l_serial_num :=1;
869 ELSE
870 l_serial_num:=l_serial_num+1;
871 END IF;
872
873 new_line_num_lnm_tbl(l_line_num_index)
874 :=new_line_num_lnm_tbl(l_line_num_index)+l_serial_num;
875
876 l_progress :='105';
877 l_module := G_MODULE_PREFIX||l_api_name||'.'||'105'||'.';
878 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
879 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
880 'Inside loop: Requisition_header_id = '
881 ||requisition_header_id_lnm_tbl(l_line_num_index)
882 ||'Requisition_line_id = '
883 ||requisition_line_id_lnm_tbl(l_line_num_index)
884 ||'New line num = '
885 ||new_line_num_lnm_tbl(l_line_num_index));
886 END IF;
887
888 old_requisition_header_id:=
889 requisition_header_id_lnm_tbl(l_line_num_index);
890
891 END LOOP;
892
893 l_progress :='109';
894 l_module := G_MODULE_PREFIX||l_api_name||'.'||'109'||'.';
895 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
896 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
897 'After loop: Assign incremental numbers to line num ');
898 END IF;
899
900 EXCEPTION
901 WHEN OTHERS THEN
902
903 po_message_s.sql_error('Exception of Split_requisitionLines()',
904 l_progress , sqlcode);
905 FND_MSG_PUB.Add;
906 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
907 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_module,
908 'Exception: Assign incremental numbers to line num ');
909 END IF;
910 RAISE;
911 END;
912
913
914 BEGIN
915
916 l_progress :='110';
917 l_module := G_MODULE_PREFIX||l_api_name||'.'||'110'||'.';
918 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
919 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
920 'Before: update po_req_split_lines_gt toset new_line_num');
921 END IF;
922
923 FORALL l_line_num_upd_index in 1.. requisition_line_id_lnm_tbl.COUNT
924 UPDATE po_req_split_lines_gt
925 SET new_line_num=new_line_num_lnm_tbl(l_line_num_upd_index)
926 WHERE rowid=row_id_lnm_tbl(l_line_num_upd_index);
927 l_progress :='111';
928 l_module := G_MODULE_PREFIX||l_api_name||'.'||'110'||'.';
929 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
930 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
931 'After: update po_req_split_lines_gt toset new_line_num;
932 Updated '||sql%rowcount||' rows');
933 END IF;
934
935 EXCEPTION
936 WHEN OTHERS THEN
937
938 po_message_s.sql_error('Exception of Split_requisitionLines()',
939 l_progress , sqlcode);
940 FND_MSG_PUB.Add;
941 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
942 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_module,
943 'Exception: update po_req_split_lines_gt to set
944 new_line_num');
945 END IF;
946 RAISE;
947
948 END;
949
950
951 -- SQL What:Bulk collect all the requisition lines which are eligible
952 -- to be split,which are record_status in ('S','N').
953 -- SQL Why :proceed only if there is at least one requisition line to
954 -- be split and it also helped to merged multiple DMLs.
955 BEGIN
956
957 l_progress :='120';
958 l_module := G_MODULE_PREFIX||l_api_name||'.'||'120'||'.';
959 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
960 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
961 'Before: Collect all the req lines which are eligible to split ');
962 Print_Global_Table(l_module);
963 END IF;
964
965 SELECT prs.auction_header_id,
966 prs.bid_number,
967 prs.bid_line_number,
968 prs.requisition_header_id,
969 prs.requisition_line_id,
970 prs.allocated_qty,
971 prs.new_req_line_id,
972 prs.new_line_num,
973 prs.total_alloc_req_line_qty,
974 prs.requisition_line_qty,
975 prs.min_bid_number,
976 prs.record_status,
977 NVL(fsp.req_encumbrance_flag, 'N') -- Bug 4723367
978 BULK COLLECT INTO
979 auction_header_id_rslt_tbl,
980 bid_number_rslt_tbl,
981 bid_line_number_rslt_tbl,
982 requisition_header_id_rslt_tbl,
983 requisition_line_id_rslt_tbl,
984 allocated_qty_rslt_tbl,
985 new_req_line_id_rslt_tbl,
986 new_line_num_rslt_tbl,
987 totallc_req_line_qty_rslt_tbl,
988 requisition_line_qty_rslt_tbl,
989 min_bid_number_rslt_tbl,
990 record_status_rslt_tbl,
991 encumbrance_flag_rslt_tbl -- Bug 4723367
992 FROM po_req_split_lines_gt prs,
993 po_requisition_lines_all prl, --<Sourcing 11.5.10+>
994 financials_system_parameters fsp -- Bug 4723367
995 -- Bug 5467617: Removed the joins to PO_VENDORS and PO_VENDOR_SITES_ALL
996 -- These are not required anymore because they we used to fetch the
997 -- rounding rule from the site level. Now the tax rounding is done by
998 -- recalculating the tax at the end of this flow.
999 WHERE record_status in ('S','N')
1000 AND prs.requisition_line_id = prl.requisition_line_id
1001 AND nvl(prl.org_id, -99) = nvl(fsp.org_id, -99); -- Bug 4723367
1002
1003 l_progress :='121';
1004 l_module := G_MODULE_PREFIX||l_api_name||'.'||'121'||'.';
1005 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1006 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
1007 'After: Collect all the req lines which are eligible to ' ||
1008 'split; Selected '||requisition_line_id_rslt_tbl.COUNT
1009 ||' rows');
1010 END IF;
1011
1012 EXCEPTION
1013 WHEN OTHERS THEN
1014
1015 po_message_s.sql_error('Exception of Split_requisitionLines()',
1016 l_progress , sqlcode);
1017 FND_MSG_PUB.Add;
1018 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1019 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_module,
1020 'Exception: Collect all the req lines which are eligible to split ');
1021 END IF;
1022 RAISE;
1023
1024 END;
1025
1026
1027 IF requisition_line_id_rslt_tbl.COUNT >= 1 THEN
1028 --Create new requisition lines
1029 -- <SERVICES FPJ>
1030 -- Added order_type_lookup_code, purchase_basis and matching_basis
1031 BEGIN
1032
1033 l_progress :='130';
1034 l_module := G_MODULE_PREFIX||l_api_name||'.'||'130'||'.';
1035 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1036 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
1037 'Before: Create all the new requisition lines ');
1038 END IF;
1039
1040 FORALL l_insert_line_index IN
1041 1.. requisition_line_id_rslt_tbl.COUNT
1042 INSERT INTO po_requisition_lines_all --<Sourcing 11.5.10+>
1043 (
1044 requisition_line_id,
1045 requisition_header_id,
1046 line_num,
1047 line_type_id,
1048 category_id,
1049 item_description,
1050 unit_meas_lookup_code,
1051 unit_price,
1052 quantity,
1053 deliver_to_location_id,
1054 to_person_id,
1055 last_update_date,
1056 last_updated_by,
1057 source_type_code,
1058 last_update_login,
1059 creation_date,
1060 created_by,
1061 item_id,
1062 item_revision,
1063 quantity_delivered,
1064 suggested_buyer_id,
1065 encumbered_flag,
1066 rfq_required_flag,
1067 need_by_date,
1068 line_location_id,
1069 modified_by_agent_flag,
1070 parent_req_line_id,
1071 justification,
1072 note_to_agent,
1073 note_to_receiver,
1074 purchasing_agent_id,
1075 document_type_code,
1076 blanket_po_header_id,
1077 blanket_po_line_num,
1078 currency_code,
1079 rate_type,
1080 rate_date,
1081 rate,
1082 currency_unit_price,
1083 suggested_vendor_name,
1084 suggested_vendor_location,
1085 suggested_vendor_contact,
1086 suggested_vendor_phone,
1087 suggested_vendor_product_code,
1088 un_number_id,
1089 hazard_class_id,
1090 must_use_sugg_vendor_flag,
1091 reference_num,
1092 on_rfq_flag,
1093 urgent_flag,
1094 cancel_flag,
1095 source_organization_id,
1096 source_subinventory,
1097 destination_type_code,
1098 destination_organization_id,
1099 destination_subinventory,
1100 quantity_cancelled,
1101 cancel_date,
1102 cancel_reason,
1103 closed_code,
1104 agent_return_note,
1105 changed_after_research_flag,
1106 vendor_id,
1107 vendor_site_id,
1108 vendor_contact_id,
1109 research_agent_id,
1110 on_line_flag,
1111 wip_entity_id,
1112 wip_line_id,
1113 wip_repetitive_schedule_id,
1114 wip_operation_seq_num,
1115 wip_resource_seq_num,
1116 attribute_category,
1117 destination_context,
1118 inventory_source_context,
1119 vendor_source_context,
1120 attribute1,
1121 attribute2,
1122 attribute3,
1123 attribute4,
1124 attribute5,
1125 attribute6,
1126 attribute7,
1127 attribute8,
1128 attribute9,
1129 attribute10,
1130 attribute11,
1131 attribute12,
1132 attribute13,
1133 attribute14,
1134 attribute15,
1135 bom_resource_id,
1136 government_context,
1137 closed_reason,
1138 closed_date,
1139 transaction_reason_code,
1140 quantity_received,
1141 tax_code_id,
1142 tax_user_override_flag,
1143 oke_contract_header_id,
1144 oke_contract_version_id,
1145 secondary_unit_of_measure,
1146 secondary_quantity,
1147 preferred_grade,
1148 secondary_quantity_received,
1149 secondary_quantity_cancelled,
1150 auction_header_id,
1151 auction_display_number,
1152 auction_line_number,
1153 reqs_in_pool_flag,
1154 vmi_flag,
1155 bid_number,
1156 bid_line_number,
1157 order_type_lookup_code,
1158 purchase_basis,
1159 matching_basis,
1160 org_id, --<Sourcing 11.5.10+>
1161 tax_attribute_update_code --<R12 eTax Integration>
1162 )
1163 SELECT new_req_line_id_rslt_tbl(l_insert_line_index),
1164 prl.requisition_header_id,
1165 new_line_num_rslt_tbl(l_insert_line_index),
1166 prl.line_type_id,
1167 prl.category_id,
1168 prl.item_description,
1169 prl.unit_meas_lookup_code,
1170 prl.unit_price,
1171 allocated_qty_rslt_tbl(l_insert_line_index),
1172 prl.deliver_to_location_id,
1173 prl.to_person_id,
1174 prl.last_update_date,
1175 prl.last_updated_by,
1176 prl.source_type_code,
1177 prl.last_update_login,
1178 prl.creation_date,
1179 prl.created_by,
1180 prl.item_id,
1181 prl.item_revision,
1182 prl.quantity_delivered,
1183 prl.suggested_buyer_id,
1184 prl.encumbered_flag,
1185 prl.rfq_required_flag,
1186 prl.need_by_date,
1187 prl.line_location_id,
1188 prl.modified_by_agent_flag,
1189 -- Bug 13387472: parent_req_line_id of the new req line created should be requisition line id of the
1190 -- original req line. It was wrongly assigned as prl.parent_req_line_id.
1191 prl.requisition_line_id, --prl.parent_req_line_id
1192 prl.justification,
1193 prl.note_to_agent,
1194 prl.note_to_receiver,
1195 prl.purchasing_agent_id,
1196 prl.document_type_code,
1197 prl.blanket_po_header_id,
1198 prl.blanket_po_line_num,
1199 prl.currency_code,
1200 prl.rate_type,
1201 prl.rate_date,
1202 prl.rate,
1203 prl.currency_unit_price,
1204 prl.suggested_vendor_name,
1205 prl.suggested_vendor_location,
1206 prl.suggested_vendor_contact,
1207 prl.suggested_vendor_phone,
1208 prl.suggested_vendor_product_code,
1209 prl.un_number_id,
1210 prl.hazard_class_id,
1211 prl.must_use_sugg_vendor_flag,
1212 prl.reference_num,
1213 prl.on_rfq_flag,
1214 prl.urgent_flag,
1215 prl.cancel_flag,
1216 prl.source_organization_id,
1217 prl.source_subinventory,
1218 prl.destination_type_code,
1219 prl.destination_organization_id,
1220 prl.destination_subinventory,
1221 prl.quantity_cancelled,
1222 prl.cancel_date,
1223 prl.cancel_reason,
1224 prl.closed_code,
1225 prl.agent_return_note,
1226 prl.changed_after_research_flag,
1227 prl.vendor_id,
1228 prl.vendor_site_id,
1229 prl.vendor_contact_id,
1230 prl.research_agent_id,
1231 prl.on_line_flag,
1232 prl.wip_entity_id,
1233 prl.wip_line_id,
1234 prl.wip_repetitive_schedule_id,
1235 prl.wip_operation_seq_num,
1236 prl.wip_resource_seq_num,
1237 prl.attribute_category,
1238 prl.destination_context,
1239 prl.inventory_source_context,
1240 prl.vendor_source_context,
1241 prl.attribute1,
1242 prl.attribute2,
1243 prl.attribute3,
1244 prl.attribute4,
1245 prl.attribute5,
1246 prl.attribute6,
1247 prl.attribute7,
1248 prl.attribute8,
1249 prl.attribute9,
1250 prl.attribute10,
1251 prl.attribute11,
1252 prl.attribute12,
1253 prl.attribute13,
1254 prl.attribute14,
1255 prl.attribute15,
1256 prl.bom_resource_id,
1257 prl.government_context,
1258 prl.closed_reason,
1259 prl.closed_date,
1260 prl.transaction_reason_code,
1261 prl.quantity_received,
1262 prl.tax_code_id,
1263 prl.tax_user_override_flag,
1264 prl.oke_contract_header_id,
1265 prl.oke_contract_version_id,
1266 prl.secondary_unit_of_measure,
1267 prl.secondary_quantity,
1268 prl.preferred_grade,
1269 prl.secondary_quantity_received,
1270 prl.secondary_quantity_cancelled,
1271 prl.auction_header_id,
1272 prl.auction_display_number,
1273 prl.auction_line_number,
1274 'Y', --new reqs are placed back in pool after splitting
1275 prl.vmi_flag,
1276 bid_number_rslt_tbl(l_insert_line_index),
1277 decode(record_status_rslt_tbl(l_insert_line_index),'N',NULL,
1278 bid_line_number_rslt_tbl(l_insert_line_index)),
1279 prl.order_type_lookup_code,
1280 prl.purchase_basis,
1281 prl.matching_basis,
1282 prl.org_id, --<Sourcing 11.5.10+>
1283 'CREATE' --<R12 eTax Integration>
1284 FROM po_requisition_lines_all prl --<Sourcing 11.5.10+>
1285 WHERE prl.requisition_line_id=
1286 requisition_line_id_rslt_tbl(l_insert_line_index);
1287
1288 l_progress :='131';
1289 l_module := G_MODULE_PREFIX||l_api_name||'.'||'131'||'.';
1290 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1291 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
1292 'Before: Create all the new requisition lines; Inserted '
1293 ||sql%rowcount||' requisition lines');
1294 END IF;
1295
1296 EXCEPTION
1297 WHEN OTHERS THEN
1298
1299 po_message_s.sql_error
1300 ('Exception of Split_requisitionLines()', l_progress ,
1301 sqlcode);
1302 FND_MSG_PUB.Add;
1303 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1304 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED, l_module,
1305 'Exception: Create all the new requisition lines ');
1306 END IF;
1307 RAISE;
1308
1309 END;
1310
1311
1312 -- SQL What:Mark all the parent requisition lines which are split
1313 -- with modified_by_agent_flag setting 'Y'. Eligible lines
1314 -- are the ones with the record status 'S'
1315 -- SQL Why :This indicates that this requisition lines have been
1316 -- modified by the buyer and no longer available for any
1317 -- operations.
1318 BEGIN
1319
1320 l_progress :='140';
1321 l_module := G_MODULE_PREFIX||l_api_name||'.'||'140'||'.';
1322 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1323 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
1324 'Before: Mark parent req lines as modified by agent');
1325 END IF;
1326
1327 -- <REQINPOOL>: added update of reqs_in_pool_flag and of
1328 -- WHO columns.
1329 FORALL l_mod_buyer_index in 1.. requisition_line_id_rslt_tbl.COUNT
1330 UPDATE po_requisition_lines_all --<Sourcing 11.5.10+>
1331 SET modified_by_agent_flag = 'Y',
1332 reqs_in_pool_flag = NULL, --<REQINPOOL>
1333 last_update_date = SYSDATE,
1334 last_updated_by = FND_GLOBAL.USER_ID,
1335 last_update_login = FND_GLOBAL.LOGIN_ID
1336 WHERE requisition_line_id =
1337 requisition_line_id_rslt_tbl(l_mod_buyer_index)
1338 AND record_status_rslt_tbl(l_mod_buyer_index)='S';
1339
1340 l_progress :='141';
1341 l_module := G_MODULE_PREFIX||l_api_name||'.'||'141'||'.';
1342 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1343 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
1344 'After: Mark parent req lines as modified by agent;Updated'
1345 ||sql%rowcount||' requisition lines ');
1346 END IF;
1347
1348 EXCEPTION
1349 WHEN OTHERS THEN
1350
1351 po_message_s.sql_error
1352 ('Exception of Split_requisitionLines()', l_progress ,
1353 sqlcode);
1354 FND_MSG_PUB.Add;
1355 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1356 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
1357 'Exception: Mark parent req lines as modified by agent');
1358 END IF;
1359 RAISE;
1360
1361 END;
1362
1363 --<Bug 2752584 mbhargav START>
1364 --
1365 -- SQL What: Doing two bulk statements. One to delete from
1366 -- mtl_supply entries for old req lines. Second for
1367 -- inserting into mtl_supply newly created lines
1368 -- SQL Why : To take care of update to MTL_SUPPLY tables in case of
1369 -- req-split
1370 --<CLM INTG - PLANNING>
1371 -- Mark exclude_from_planning as 'Y' for CLM Documents
1372 --<CLM INTG - PLANNING>
1373 BEGIN
1374
1375 l_progress :='145';
1376 l_module := G_MODULE_PREFIX||l_api_name||'.'||'145'||'.';
1377 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1378 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
1379 'Before: Update MTL_SUPPLY');
1380 END IF;
1381
1382 FORALL l_update_mtl_supply_index in 1.. new_req_line_id_rslt_tbl.COUNT
1383 -- Insert New Supply for each new Line created by the Reqsplit
1384 INSERT INTO mtl_supply(supply_type_code,
1385 supply_source_id,
1386 last_updated_by,
1387 last_update_date,
1388 last_update_login,
1389 created_by,
1390 creation_date,
1391 req_header_id,
1392 req_line_id,
1393 item_id,
1394 item_revision,
1395 quantity,
1396 unit_of_measure,
1397 receipt_date,
1398 need_by_date,
1399 destination_type_code,
1400 location_id,
1401 from_organization_id,
1402 from_subinventory,
1403 to_organization_id,
1404 to_subinventory,
1405 change_flag,
1406 to_org_primary_quantity,
1407 change_type,
1408 to_org_primary_uom,
1409 expected_delivery_date,
1410 exclude_from_planning --<CLM INTG - PLANNING>
1411 )
1412 SELECT 'REQ',
1413 prl.requisition_line_id,
1414 prl.last_updated_by,
1415 prl.last_update_date,
1416 prl.last_update_login,
1417 prl.created_by,
1418 prl.creation_date,
1419 prl.requisition_header_id,
1420 prl.requisition_line_id,
1421 prl.item_id,
1422 prl.item_revision,
1423 prl.quantity - (nvl(prl.quantity_cancelled, 0) +
1424 nvl(prl.quantity_delivered, 0)),
1425 prl.unit_meas_lookup_code,
1426 prl.need_by_date,
1427 prl.need_by_date,
1428 prl.destination_type_code,
1429 prl.deliver_to_location_id,
1430 prl.source_organization_id,
1431 prl.source_subinventory,
1432 prl.destination_organization_id,
1433 prl.destination_subinventory,
1434 null,
1435 prl.quantity - (nvl(prl.quantity_cancelled, 0) +
1436 nvl(prl.quantity_delivered, 0)),
1437 null,
1438 prl.unit_meas_lookup_code,
1439 decode(prl.item_id, null, null, prl.need_by_date + nvl(msi.postprocessing_lead_time,0)),
1440 DECODE(PO_CLM_INTG_GRP.IS_CLM_DOCUMENT('REQUISITION',prl.Requisition_header_id),'Y','Y','N',NULL,NULL) --<CLM INTG - PLANNING>
1441 FROM po_requisition_lines_all prl, --<Sourcing 11.5.10+>
1442 mtl_system_items msi
1443 WHERE prl.requisition_line_id =
1444 new_req_line_id_rslt_tbl(l_update_mtl_supply_index)
1445 AND prl.destination_organization_id = msi.organization_id(+)
1446 AND prl.item_id = msi.inventory_item_id(+)
1447 AND EXISTS
1448 (select 'Supply Exists'
1449 from mtl_supply
1450 where supply_type_code = 'REQ'
1451 AND supply_source_id =
1452 requisition_line_id_rslt_tbl(l_update_mtl_supply_index));
1453
1454 --Delete the entry in mtl_supply for original req line
1455 FORALL l_delete_mtl_supply_index in
1456 1.. requisition_line_id_rslt_tbl.COUNT
1457 DELETE FROM mtl_supply
1458 WHERE supply_type_code = 'REQ'
1459 AND supply_source_id =
1460 requisition_line_id_rslt_tbl(l_delete_mtl_supply_index);
1461
1462 l_progress :='146';
1463 l_module := G_MODULE_PREFIX||l_api_name||'.'||'146'||'.';
1464 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1465 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
1466 'After: Update MTL_SUPPLY;Updated'
1467 ||sql%rowcount||' requisition lines ');
1468 END IF;
1469
1470 EXCEPTION
1471 WHEN OTHERS THEN
1472
1473 po_message_s.sql_error
1474 ('Exception of Split_requisitionLines()', l_progress ,
1475 sqlcode);
1476 FND_MSG_PUB.Add;
1477 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1478 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
1479 'Exception: Update MTL_SUPPLY');
1480 END IF;
1481 RAISE;
1482
1483 END;
1484 --<Bug 2752584 mbhargav END>
1485
1486
1487 l_progress :='150';
1488 l_module := G_MODULE_PREFIX||l_api_name||'.'||'150'||'.';
1489 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1490 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
1491 'Before: Copy all the attachments');
1492 END IF;
1493
1494
1495 FOR l_copy_attach_index in 1.. requisition_line_id_rslt_tbl.COUNT
1496 LOOP
1497 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1498 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
1499 'requisition_line_id_rslt_tbl='
1500 ||to_char(requisition_line_id_rslt_tbl(l_copy_attach_index))
1501 ||' to- new_req_line_id_rslt_tbl='
1502 ||to_char(new_req_line_id_rslt_tbl(l_copy_attach_index)));
1503 END IF;
1504
1505 fnd_attached_documents2_pkg.copy_attachments
1506 (X_from_entity_name => 'REQ_LINES' ,
1507 X_from_pk1_value=>
1508 requisition_line_id_rslt_tbl(l_copy_attach_index),
1509 X_from_pk2_value => NULL ,
1510 X_from_pk3_value => NULL ,
1511 X_from_pk4_value => NULL ,
1512 X_from_pk5_value => NULL,
1513 X_to_entity_name => 'REQ_LINES' ,
1514 X_to_pk1_value =>new_req_line_id_rslt_tbl(l_copy_attach_index),
1515 X_to_pk2_value => NULL ,
1516 X_to_pk3_value => NULL ,
1517 X_to_pk4_value => NULL ,
1518 X_to_pk5_value => NULL ,
1519 X_created_by => NULL ,
1520 X_last_update_login => NULL ,
1521 X_program_application_id => NULL ,
1522 X_program_id => NULL ,
1523 X_request_id => NULL ,
1524 X_automatically_added_flag=> NULL
1525 );
1526
1527 END LOOP;
1528
1529 l_progress :='159';
1530 l_module := G_MODULE_PREFIX||l_api_name||'.'||'159'||'.';
1531 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1532 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
1533 'After: Copy all the attachments');
1534 END IF;
1535
1536 -- Retrieve the current operating unit from the environment
1537 l_current_ou_id := PO_GA_PVT.get_current_org;
1538
1539 -- Take care of single-org instance
1540 IF l_current_ou_id IS NULL THEN
1541 l_current_ou_id := -99;
1542 END IF;
1543
1544 -- Initialize l_old_line_requesting_ou_id to be the same as
1545 -- the current ou id
1546 l_old_line_requesting_ou_id := l_current_ou_id;
1547 --<Sourcing 11.5.10+ End>
1548
1549
1550 BEGIN
1551
1552 l_progress :='160';
1553 l_module := G_MODULE_PREFIX||l_api_name||'.'||'160'||'.';
1554 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1555 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
1556 'Before: Create distributions lines For all the ' ||
1557 'newly created requisition lines');
1558 END IF;
1559 --<Sourcing 11.5.10+ Start>
1560 FOR l_create_dist_index
1561 IN 1.. requisition_line_id_rslt_tbl.COUNT
1562 LOOP
1563 -- Set org context to the ou where the current requisition line
1564 -- is raised, because AP's tax rounding API needs to be called
1565 -- in the Requesting OU's org context
1566
1567 -- SQL What: Retrieves the value of org_id from the current
1568 -- requisition line
1569 -- SQL Why: Need to set org context to the OU where the
1570 -- requisition line is raised
1571 SELECT nvl(org_id, -99)
1572 INTO l_line_requesting_ou_id
1573 FROM po_requisition_lines_all
1574 WHERE requisition_line_id = requisition_line_id_rslt_tbl(l_create_dist_index);
1575
1576 IF l_line_requesting_ou_id <> l_old_line_requesting_ou_id THEN
1577 PO_MOAC_UTILS_PVT.set_org_context(l_line_requesting_ou_id) ; -- <R12 MOAC>
1578 l_org_context_changed := 'Y';
1579 l_old_line_requesting_ou_id := l_line_requesting_ou_id;
1580 END IF;
1581
1582 INSERT INTO po_req_distributions_all --<Sourcing 11.5.10+>
1583 (DISTRIBUTION_ID ,
1584 LAST_UPDATE_DATE ,
1585 LAST_UPDATED_BY ,
1586 REQUISITION_LINE_ID ,
1587 SET_OF_BOOKS_ID ,
1588 CODE_COMBINATION_ID ,
1589 REQ_LINE_QUANTITY ,
1590 LAST_UPDATE_LOGIN ,
1591 CREATION_DATE ,
1592 CREATED_BY ,
1593 ENCUMBERED_FLAG ,
1594 GL_ENCUMBERED_DATE ,
1595 GL_ENCUMBERED_PERIOD_NAME ,
1596 GL_CANCELLED_DATE ,
1597 FAILED_FUNDS_LOOKUP_CODE ,
1598 ENCUMBERED_AMOUNT ,
1599 BUDGET_ACCOUNT_ID ,
1600 ACCRUAL_ACCOUNT_ID ,
1601 ORG_ID ,
1602 VARIANCE_ACCOUNT_ID ,
1603 PREVENT_ENCUMBRANCE_FLAG ,
1604 ATTRIBUTE_CATEGORY ,
1605 ATTRIBUTE1 ,
1606 ATTRIBUTE2 ,
1607 ATTRIBUTE3 ,
1608 ATTRIBUTE4 ,
1609 ATTRIBUTE5 ,
1610 ATTRIBUTE6 ,
1611 ATTRIBUTE7 ,
1612 ATTRIBUTE8 ,
1613 ATTRIBUTE9 ,
1614 ATTRIBUTE10 ,
1615 ATTRIBUTE11 ,
1616 ATTRIBUTE12 ,
1617 ATTRIBUTE13 ,
1618 ATTRIBUTE14 ,
1619 ATTRIBUTE15 ,
1620 GOVERNMENT_CONTEXT ,
1621 REQUEST_ID ,
1622 PROGRAM_APPLICATION_ID ,
1623 PROGRAM_ID ,
1624 PROGRAM_UPDATE_DATE ,
1625 PROJECT_ID ,
1626 TASK_ID ,
1627 EXPENDITURE_TYPE ,
1628 PROJECT_ACCOUNTING_CONTEXT ,
1629 EXPENDITURE_ORGANIZATION_ID ,
1630 GL_CLOSED_DATE ,
1631 SOURCE_REQ_DISTRIBUTION_ID ,
1632 DISTRIBUTION_NUM ,
1633 PROJECT_RELATED_FLAG ,
1634 EXPENDITURE_ITEM_DATE ,
1635 ALLOCATION_TYPE ,
1636 ALLOCATION_VALUE ,
1637 END_ITEM_UNIT_NUMBER ,
1638 RECOVERABLE_TAX ,
1639 NONRECOVERABLE_TAX ,
1640 RECOVERY_RATE ,
1641 TAX_RECOVERY_OVERRIDE_FLAG ,
1642 AWARD_ID ,
1643 OKE_CONTRACT_LINE_ID ,
1644 OKE_CONTRACT_DELIVERABLE_ID
1645 )
1646 SELECT po_req_distributions_s.nextval,
1647 LAST_UPDATE_DATE ,
1648 LAST_UPDATED_BY ,
1649 new_req_line_id_rslt_tbl(l_create_dist_index),
1650 SET_OF_BOOKS_ID ,
1651 CODE_COMBINATION_ID ,
1652 round(((req_line_quantity/requisition_line_qty_rslt_tbl(l_create_dist_index))* allocated_qty_rslt_tbl(l_create_dist_index)),13),
1653 --enter req form, dist screen uses 13 places to round.
1654 --suggested by PM.
1655 LAST_UPDATE_LOGIN ,
1656 CREATION_DATE ,
1657 CREATED_BY ,
1658 --purposely encumbered flag is copied from the parent dist
1659 --line instead of assigning a null values. This is required
1660 --as there is no parent dist id on the distributions table.
1661 ENCUMBERED_FLAG ,
1662 GL_ENCUMBERED_DATE ,
1663 GL_ENCUMBERED_PERIOD_NAME ,
1664 GL_CANCELLED_DATE ,
1665 FAILED_FUNDS_LOOKUP_CODE ,
1666 --bug#2728152, the new lines should have 0 encumbered amt.
1667 --as encumbrance api is looking at this value.
1668 --ENCUMBERED_AMOUNT ,
1669 0,
1670 BUDGET_ACCOUNT_ID ,
1671 ACCRUAL_ACCOUNT_ID ,
1672 ORG_ID ,
1673 VARIANCE_ACCOUNT_ID ,
1674 PREVENT_ENCUMBRANCE_FLAG ,
1675 ATTRIBUTE_CATEGORY ,
1676 ATTRIBUTE1 ,
1677 ATTRIBUTE2 ,
1678 ATTRIBUTE3 ,
1679 ATTRIBUTE4 ,
1680 ATTRIBUTE5 ,
1681 ATTRIBUTE6 ,
1682 ATTRIBUTE7 ,
1683 ATTRIBUTE8 ,
1684 ATTRIBUTE9 ,
1685 ATTRIBUTE10 ,
1686 ATTRIBUTE11 ,
1687 ATTRIBUTE12 ,
1688 ATTRIBUTE13 ,
1689 ATTRIBUTE14 ,
1690 ATTRIBUTE15 ,
1691 GOVERNMENT_CONTEXT ,
1692 REQUEST_ID ,
1693 PROGRAM_APPLICATION_ID ,
1694 PROGRAM_ID ,
1695 PROGRAM_UPDATE_DATE ,
1696 PROJECT_ID ,
1697 TASK_ID ,
1698 EXPENDITURE_TYPE ,
1699 PROJECT_ACCOUNTING_CONTEXT ,
1700 EXPENDITURE_ORGANIZATION_ID ,
1701 GL_CLOSED_DATE ,
1702 SOURCE_REQ_DISTRIBUTION_ID ,
1703 DISTRIBUTION_NUM ,
1704 PROJECT_RELATED_FLAG ,
1705 EXPENDITURE_ITEM_DATE ,
1706 ALLOCATION_TYPE ,
1707 ALLOCATION_VALUE ,
1708 END_ITEM_UNIT_NUMBER ,
1709 --<R12 eTax Integration> recoverable and nonrecoverable
1710 -- tax is recalculated instead of being prorated
1711 null,
1712 null,
1713 RECOVERY_RATE ,
1714 TAX_RECOVERY_OVERRIDE_FLAG ,
1715 AWARD_ID ,
1716 OKE_CONTRACT_LINE_ID ,
1717 OKE_CONTRACT_DELIVERABLE_ID
1718 FROM po_req_distributions_all --<Sourcing 11.5.10+>
1719 WHERE requisition_line_id=
1720 requisition_line_id_rslt_tbl(l_create_dist_index);
1721
1722 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1723 PO_DEBUG.debug_stmt(l_log_head,l_progress,
1724 'Created '||sql%rowcount||' distributions lines, requisition_line_id= '|| new_req_line_id_rslt_tbl(l_create_dist_index) );
1725 END IF;
1726 -- bug 5249299 start: Need to call GMS api to maintain the adls data since these are
1727 -- new distributions.
1728 FOR l_req_dist_proj_rec IN
1729 l_req_dist_proj_csr(requisition_line_id_rslt_tbl(l_create_dist_index))
1730 LOOP
1731
1732 l_distribution_id := l_req_dist_proj_rec.distribution_id;
1733 l_project_id := l_req_dist_proj_rec.project_id;
1734 l_task_id := l_req_dist_proj_rec.task_id;
1735 l_award_id := l_req_dist_proj_rec.award_id ;
1736 l_expenditure_type := l_req_dist_proj_rec.expenditure_type ;
1737 l_expenditure_item_date := l_req_dist_proj_rec.expenditure_item_date;
1738
1739 l_progress :='159';
1740 l_module := G_MODULE_PREFIX||l_api_name||'.'||'159'||'.';
1741 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1742 PO_DEBUG.debug_stmt(l_log_head,l_progress,
1743 'calling GMS_POR_API.when_insert_line :'||
1744 'l_distribution_id '||l_distribution_id||
1745 'l_project_id '||l_project_id||
1746 'l_task_id '||l_task_id ||
1747 'l_award_id '||l_award_id ||
1748 'l_expenditure_type '||l_expenditure_type ||
1749 'l_expenditure_item_date '||
1750 to_char(l_expenditure_item_date,'DD-MON-YYYY'));
1751 END IF;
1752
1753 IF (l_award_id is not null) THEN
1754
1755 l_award_id := GMS_POR_API.get_award_id(
1756 X_award_set_id => l_award_id,
1757 X_award_number => NULL,
1758 X_req_distribution_id => NULL);
1759
1760 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1761 PO_DEBUG.debug_stmt(l_log_head,l_progress,
1762 'After calling GMS_POR_API.get_award_id :'||
1763 'l_award_id'||l_award_id);
1764 END IF;
1765
1766 GMS_POR_API.when_insert_line (
1767 X_distribution_id => l_distribution_id,
1768 X_project_id => l_project_id,
1769 X_task_id => l_task_id,
1770 X_award_id => l_award_id,
1771 X_expenditure_type => l_expenditure_type,
1772 X_expenditure_item_date=> l_expenditure_item_date,
1773 X_award_set_id => l_award_set_id, --OUT
1774 X_status => l_status) ;
1775
1776
1777 l_progress :='160';
1778 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1779 PO_DEBUG.debug_stmt(l_log_head,l_progress,
1780 'After when insert line : Out values '||
1781 'l_award_set_id '||l_award_set_id ||
1782 'l_status '||l_status );
1783 END IF;
1784
1785
1786 END IF; -- if (l_award_id is NOT NULL)
1787
1788 END LOOP; -- req_dist_proj_cursor
1789 -- bug 5249299 end
1790
1791 END LOOP; --<Sourcing 11.5.10+>
1792
1793 l_progress :='161';
1794 l_module := G_MODULE_PREFIX||l_api_name||'.'||'161'||'.';
1795 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1796 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
1797 'After: Create distributions lines for all the
1798 newly created requisition lines; Created '
1799 ||sql%rowcount||' distributions lines ' );
1800 END IF;
1801 EXCEPTION
1802 WHEN OTHERS THEN
1803
1804 po_message_s.sql_error
1805 ('Exception of Split_requisitionLines()', l_progress ,
1806 sqlcode);
1807 FND_MSG_PUB.Add;
1808 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1809 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
1810 'Excption: Create distributions lines for all the
1811 newly created requisition lines');
1812 END IF;
1813
1814 -- Switch the org context back to the current OU if it has been changed
1815 IF (l_org_context_changed = 'Y') THEN
1816 PO_MOAC_UTILS_PVT.set_org_context(l_current_ou_id) ; -- <R12 MOAC>
1817 l_org_context_changed := 'N';
1818 END IF;
1819
1820 RAISE;
1821
1822 END;
1823
1824 -- Switch the org context back to the current OU if it has been changed
1825 IF (l_org_context_changed = 'Y') THEN
1826 PO_MOAC_UTILS_PVT.set_org_context(l_current_ou_id) ; -- <R12 MOAC>
1827 l_org_context_changed := 'N';
1828 END IF;
1829 --<Sourcing 11.5.10+ End>
1830
1831 --<R12 eTax Integration> Calculate recoverable and nonrecoverable
1832 -- tax amounts
1833 FOR i IN req_header_id_csr LOOP
1834 PO_TAX_INTERFACE_PVT.calculate_tax_requisition(
1835 p_requisition_header_id => i.requisition_header_id,
1836 p_calling_program => 'PO_NEGOTIATIONS',
1837 x_return_status => l_return_status
1838 );
1839 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1840 FND_MESSAGE.set_name('PO','PO_PDOI_TAX_CALCULATION_ERR');
1841 FND_MSG_PUB.add;
1842
1843 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1844 RAISE FND_API.g_exc_error;
1845 ELSE
1846 RAISE FND_API.g_exc_unexpected_error;
1847 END IF;
1848 END IF;
1849 END LOOP;
1850
1851 --take care of rounding here...
1852 BEGIN
1853
1854 l_progress :='170';
1855 l_module := G_MODULE_PREFIX||l_api_name||'.'||'170'||'.';
1856 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1857 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
1858 'Before: Collect the info to take care of rounding ');
1859 END IF;
1860
1861 --sql what: Select min distribution id, sum of req line quantity
1862 -- from dist lines and max allocated qty frm temp table.
1863 --sql why : This information is required for the update to take
1864 -- care of the rounding issue which is the next step.
1865 SELECT prd.requisition_line_id,
1866 MIN(prd.distribution_id),
1867 SUM(prd.req_line_quantity),
1868 MAX(prs.allocated_qty) --this would be always one record.
1869 BULK COLLECT INTO
1870 req_line_id_round_tbl,
1871 min_dist_id_round_tbl,
1872 sum_req_line_qty_round_tbl,
1873 req_line_qty_round_tbl
1874 FROM po_req_distributions_all prd, --<Sourcing 11.5.10+>
1875 po_req_split_lines_gt prs
1876 WHERE prd.requisition_line_id = prs.new_req_line_id
1877 AND prs.record_status in ('S','N')
1878 GROUP BY prd.requisition_line_id;
1879
1880 l_progress :='171';
1881 l_module := G_MODULE_PREFIX||l_api_name||'.'||'171'||'.';
1882 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1883 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
1884 'After: Collect the info to take care of rounding ');
1885 END IF;
1886
1887 EXCEPTION
1888 WHEN OTHERS THEN
1889
1890 po_message_s.sql_error
1891 ('Exception of Split_requisitionLines()', l_progress ,
1892 sqlcode);
1893 FND_MSG_PUB.Add;
1894 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1895 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
1896 'Exception: Collect the info to take care of rounding ');
1897 END IF;
1898 RAISE;
1899
1900 END;
1901
1902 BEGIN
1903
1904 l_progress :='180';
1905 l_module := G_MODULE_PREFIX||l_api_name||'.'||'180'||'.';
1906 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1907 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
1908 'Before: update to take care of rounding ');
1909 END IF;
1910
1911 --sql what: Update one of the distributions(which has min dist_id)
1912 -- with the excess/less of the sum of quantities of all
1913 -- the distribution lines.
1914 --sql why : To take care of rounding issue.
1915 FORALL l_qty_rounding_index in 1.. req_line_id_round_tbl.COUNT
1916 UPDATE po_req_distributions_all --<Sourcing 11.5.10+>
1917 SET req_line_quantity = req_line_quantity+
1918 (req_line_qty_round_tbl(l_qty_rounding_index)-
1919 sum_req_line_qty_round_tbl(l_qty_rounding_index))
1920 WHERE distribution_id=
1921 min_dist_id_round_tbl(l_qty_rounding_index);
1922
1923 l_progress :='181';
1924 l_module := G_MODULE_PREFIX||l_api_name||'.'||'181'||'.';
1925 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1926 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
1927 'After: update to take care of rounding; Updated '
1928 ||sql%rowcount||' rows');
1929 END IF;
1930
1931 EXCEPTION
1932 WHEN OTHERS THEN
1933 po_message_s.sql_error
1934 ('Exception of Split_requisitionLines()', l_progress ,
1935 sqlcode);
1936 FND_MSG_PUB.Add;
1937 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1938 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
1939 'Exception: update to take care of rounding ');
1940 END IF;
1941 RAISE;
1942 END;
1943
1944 -- JFMIP, support for Req Modify when encumbrance is enabled START
1945 l_progress :='195';
1946 l_module := G_MODULE_PREFIX||l_api_name||'.'||'195'||'.';
1947 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1948 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
1949 'Before: Calling HANDLE_TAX_ADJUSTMENTS; Status is '||
1950 l_return_status);
1951 END IF;
1952
1953 handle_tax_adjustments
1954 ( p_api_version =>1.0, -- Bug 4029136
1955 p_commit =>p_commit, -- Bug 3152161
1956 x_return_status =>l_return_status,
1957 x_msg_count =>l_msg_count,
1958 x_msg_data =>l_msg_data
1959 );
1960
1961 l_progress :='196';
1962 l_module := G_MODULE_PREFIX||l_api_name||'.'||'196'||'.';
1963 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1964 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
1965 'After: Calling HANDLE_TAX_ADJUSTMENTS; Status is
1966 '||l_return_status);
1967 END IF;
1968
1969 IF (l_return_status = FND_API.g_ret_sts_error) THEN
1970 RAISE FND_API.g_exc_error;
1971 ELSIF (l_return_status = FND_API.g_ret_sts_unexp_error) THEN
1972 RAISE FND_API.g_exc_unexpected_error;
1973 END IF;
1974 -- JFMIP, support for Req Modify when encumbrance is enabled END
1975
1976 -- Bug 4723367 START
1977 -- Added encumbrance table which stores the encumbrance flag for
1978 -- each requesting OU. The following code loops through the table
1979 -- and sets the encumbrance flag to 'Y' if any of the requesting
1980 -- OUs have encumbrance flag of 'Y'. Otherwise, it remains 'N'.
1981
1982 --Determine whether encumbrance is enabled
1983 l_progress :='190';
1984 l_module := G_MODULE_PREFIX||l_api_name||'.'||'190'||'.';
1985 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT)
1986 THEN
1987 FND_LOG.string(
1988 FND_LOG.LEVEL_STATEMENT,
1989 l_module,
1990 'Before: select req_encumbrance_flag '
1991 );
1992 END IF;
1993
1994 -- Default encumbrance flag to 'N'
1995 l_req_encumbrance_flag := 'N';
1996
1997 -- If any of the requesting OUs have encumbrance flag of 'Y', set
1998 -- encumbrance flag to 'Y' and exit the loop
1999 FOR l_index in 1..encumbrance_flag_rslt_tbl.COUNT
2000 LOOP
2001 IF (encumbrance_flag_rslt_tbl(l_index) = 'Y')
2002 THEN
2003 l_req_encumbrance_flag := 'Y';
2004 EXIT;
2005 END IF;
2006 END LOOP;
2007
2008 l_progress :='191';
2009 l_module := G_MODULE_PREFIX||l_api_name||'.'||'191'||'.';
2010 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT)
2011 THEN
2012 FND_LOG.string(
2013 FND_LOG.LEVEL_STATEMENT,
2014 l_module,
2015 'After: select req_encumbrance_flag '
2016 );
2017 END IF;
2018 -- Bug 4723367 END
2019
2020 --Select all the distribution lines which are to be reserved and
2021 --unreserved into a plsql table
2022 IF l_req_encumbrance_flag = 'Y' THEN
2023 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2024 -- <FPI JFMIP Req Split START>
2025 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2026 'Before: Calling handle_funds_reversal');
2027 END IF;
2028
2029 handle_funds_reversal
2030 ( p_api_version =>1.0, -- Bug 4029136
2031 p_commit =>p_commit, -- Bug 3152161
2032 x_return_status =>l_return_status,
2033 x_msg_count =>l_msg_count,
2034 x_msg_data =>l_msg_data,
2035 x_online_report_id =>l_online_report_id
2036 );
2037
2038 l_progress :='211';
2039 l_module := G_MODULE_PREFIX||l_api_name||'.'||'211'||'.';
2040 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2041 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2042 'After: Calling encumbrance api; Status is '||l_return_status);
2043 END IF;
2044 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2045 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2046 'After: Calling handle_funds_reversal; Status is
2047 '||l_return_status);
2048 END IF;
2049
2050
2051 IF (l_return_status = FND_API.g_ret_sts_error) THEN
2052 RAISE FND_API.g_exc_error;
2053 ELSIF (l_return_status = FND_API.g_ret_sts_unexp_error) THEN
2054 RAISE FND_API.g_exc_unexpected_error;
2055 END IF;
2056 -- <FPI JFMIP Req Split END>
2057
2058 END IF; -- if l_encumbrance_flag..
2059
2060 END IF; -- requisition_line_id_rslt_tbl.COUNT>1
2061
2062 IF FND_API.To_Boolean( p_commit ) THEN
2063 COMMIT WORK;
2064 END IF;
2065
2066 -- Standard call to get message count and if count is 1,
2067 -- get message info.
2068 FND_MSG_PUB.Count_And_Get
2069 ( p_count => x_msg_count ,
2070 p_data => x_msg_data
2071 );
2072
2073 l_progress :='230';
2074 l_module := G_MODULE_PREFIX||l_api_name||'.'||'230'||'.';
2075 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2076 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2077 'Before: Setting success status to x_return_status');
2078 END IF;
2079 x_return_status := FND_API.G_RET_STS_SUCCESS;
2080
2081 EXCEPTION
2082 WHEN FND_API.g_exc_unexpected_error THEN
2083 ROLLBACK TO Split_RequisitionLines_PVT;
2084 x_msg_data := FND_MSG_PUB.GET();
2085 x_return_status := FND_API.g_ret_sts_unexp_error;
2086 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
2087 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION, l_module,
2088 'Exception: UnexpectedError '||x_msg_data||sqlerrm);
2089 END IF;
2090
2091 WHEN FND_API.g_exc_error THEN
2092 ROLLBACK TO Split_RequisitionLines_PVT;
2093 x_return_status := FND_API.g_ret_sts_error;
2094 FND_MSG_PUB.Count_And_Get
2095 ( p_count => x_msg_count ,
2096 p_data => x_msg_data
2097 );
2098 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
2099 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION, l_module,
2100 'Exception: ExpectedError '||x_msg_data||sqlerrm);
2101 END IF;
2102
2103 WHEN OTHERS THEN
2104 BEGIN
2105
2106 -- Log a debug message, add the error the the API message list.
2107 PO_DEBUG.handle_unexp_error(g_pkg_name,l_api_name,l_progress);
2108
2109 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2110
2111 FND_MSG_PUB.Count_And_Get(
2112 p_count => x_msg_count
2113 , p_data => x_msg_data
2114 );
2115
2116 ROLLBACK TO Split_RequisitionLines_PVT;
2117
2118 EXCEPTION
2119 WHEN OTHERS THEN
2120 NULL;
2121 END;
2122
2123 END Split_RequisitionLines;
2124
2125
2126 /**
2127 * Private Procedure: Consume_ReqDemandYesNo
2128 * Requires: Std. workflow input parameters
2129 * Modifies: NA.
2130 *
2131 * Effects: This procedure checks whether sourcing user wanted to consume the
2132 * req demand by looking up the consume_Req_demand_flag from po_headers
2133 *
2134 * Returns: std. workflow out parameters
2135 */
2136 PROCEDURE Consume_ReqDemandYesNo
2137 ( itemtype IN VARCHAR2 ,
2138 itemkey IN VARCHAR2 ,
2139 actid IN NUMBER ,
2140 funcmode IN VARCHAR2 ,
2141 resultout OUT NOCOPY VARCHAR2
2142 )
2143 IS
2144
2145 l_orgid number;
2146 l_create_sr_asl varchar2(2);
2147 x_progress varchar2(300);
2148
2149 l_doc_string varchar2(200);
2150 l_preparer_user_name varchar2(100);
2151 l_document_id po_headers.po_header_id%type;
2152 l_document_type po_document_types_all.document_type_code%type;
2153 l_document_subtype po_document_types_all.document_subtype%type;
2154 l_consume_req_demand_flag po_headers.consume_req_demand_flag%type := NULL;
2155
2156 l_resp_id number;
2157 l_user_id number;
2158 l_appl_id number;
2159
2160 --bug2829163
2161 l_po_revision po_headers.revision_num%type;
2162
2163 BEGIN
2164 x_progress := 'PO_NEGOTIATIONS4_PVT.Consume_ReqDemandYesNo: 01';
2165 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
2166
2167 -- Do nothing in cancel or timeout mode
2168 if (funcmode <> wf_engine.eng_run) then
2169 resultout := wf_engine.eng_null;
2170 return;
2171 end if;
2172
2173
2174 l_document_id := PO_WF_UTIL_PKG.GetItemAttrText
2175 ( itemtype => itemtype,
2176 itemkey => itemkey,
2177 aname => 'DOCUMENT_ID'
2178 );
2179 l_document_type := PO_WF_UTIL_PKG.GetItemAttrText
2180 ( itemtype => itemtype,
2181 itemkey => itemkey,
2182 aname => 'DOCUMENT_TYPE'
2183 );
2184 l_document_subtype := PO_WF_UTIL_PKG.GetItemAttrText
2185 ( itemtype =>itemtype,
2186 itemkey => itemkey,
2187 aname => 'DOCUMENT_SUBTYPE'
2188 );
2189
2190 IF l_document_type = 'PA' AND l_document_subtype = 'BLANKET' THEN
2191
2192 --bug2829163
2193 SELECT NVL(REVISION_NUM,0)
2194 INTO l_po_revision
2195 FROM po_headers
2196 WHERE PO_HEADER_ID = l_document_id;
2197
2198 END IF;
2199
2200 --bug2829163 added the check l_po_revision=0
2201 IF l_document_type = 'PA' AND l_document_subtype = 'BLANKET'
2202 AND l_po_revision=0
2203 THEN
2204 -- SQL What:Select consume req demang flag
2205 -- SQL Why :If sourcing buyer wanted to consume the req demand then
2206 -- place the sourcing info on the requisition. So this
2207 -- information is set in the workflow.
2208 SELECT NVL(consume_req_demand_flag,'N')
2209 INTO l_consume_req_demand_flag
2210 FROM po_headers
2211 WHERE po_header_id = l_document_id;
2212
2213 END IF;
2214
2215 resultout := wf_engine.eng_completed || ':' || nvl(l_consume_req_demand_flag, 'N');
2216
2217 x_progress := 'PO_NEGOTIATIONS4_PVT.Consume_ReqDemandYesNo: 02. Result= '
2218 || l_consume_req_demand_flag;
2219 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
2220
2221 EXCEPTION
2222 WHEN OTHERS THEN
2223 l_consume_req_demand_flag := 'N';
2224 resultout := wf_engine.eng_completed || ':' || l_consume_req_demand_flag;
2225 END Consume_ReqDemandYesNo;
2226
2227
2228 /**
2229 * Private Procedure: Place_SourcingInfoOnReq
2230 * Requires: Std. workflow input parameters
2231 * Modifies: See Effects.
2232 * Effects: This procedure updates
2233 * suggested_vendor_name, suggested_vendor_location,
2234 * document_type_code, blanket_po_header_id and blanket_po_line_num on
2235 * po_requisition_lines if the user wanted to consume the req demand based
2236 * on the bid and auction information on the blanket which is undergoing
2237 * approval.
2238 * Returns: std. workflow out parameters
2239 */
2240 procedure Place_SourcingInfoOnReq
2241 ( itemtype IN VARCHAR2 ,
2242 itemkey IN VARCHAR2 ,
2243 actid IN NUMBER ,
2244 funcmode IN VARCHAR2 ,
2245 resultout OUT NOCOPY VARCHAR2
2246 ) IS
2247
2248 l_orgid number;
2249 l_create_sr_asl varchar2(2);
2250 x_progress varchar2(300);
2251
2252 l_doc_string varchar2(200);
2253 l_preparer_user_name varchar2(100);
2254 l_document_id po_headers.po_header_id%type;
2255 l_document_type PO_DOCUMENT_TYPES_ALL.DOCUMENT_TYPE_CODE%TYPE;
2256 l_document_subtype PO_DOCUMENT_TYPES_ALL.DOCUMENT_SUBTYPE%TYPE;
2257 l_vendor_id PO_VENDORS.VENDOR_ID%TYPE;
2258 l_vendor_name PO_VENDORS.VENDOR_NAME%TYPE;
2259 l_vendor_site_id PO_VENDOR_SITES.VENDOR_SITE_ID%TYPE;
2260 l_vendor_site_code PO_VENDOR_SITES.VENDOR_SITE_CODE%TYPE;
2261
2262 l_resp_id number;
2263 l_user_id number;
2264 l_appl_id number;
2265
2266 -- SQL What:Bring all the lines for the blanket we have approved.
2267 -- SQL Why :Requires later down in the process to update all the
2268 -- req lines with the matching auction and bid information for
2269 -- each of these blanket lines.
2270 CURSOR document_lines_cursor is
2271 SELECT line_num,auction_header_id,auction_line_number,
2272 bid_number,bid_line_number
2273 FROM po_lines
2274 WHERE po_header_id=l_document_id;
2275
2276 BEGIN
2277
2278 x_progress := 'PO_NEGOTIATIONS4_PVT.Place_SourcingInfoOnReq: 01';
2279 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
2280
2281 -- Do nothing in cancel or timeout mode
2282 if (funcmode <> wf_engine.eng_run) then
2283
2284 resultout := wf_engine.eng_null;
2285 return;
2286
2287 end if;
2288
2289 /* Bug# 2846210
2290 ** Desc: Setting application context as this wf api will be executed
2291 ** after the background engine is run.
2292 */
2293
2294 -- Context Setting revamp
2295 -- PO_REQAPPROVAL_INIT1.Set_doc_mgr_context(itemtype, itemkey);
2296
2297
2298 l_document_id := PO_WF_UTIL_PKG.GetItemAttrText
2299 ( itemtype => itemtype,
2300 itemkey => itemkey,
2301 aname => 'DOCUMENT_ID'
2302 );
2303 l_document_type := PO_WF_UTIL_PKG.GetItemAttrText
2304 ( itemtype => itemtype,
2305 itemkey => itemkey,
2306 aname => 'DOCUMENT_TYPE'
2307 );
2308 l_document_subtype := PO_WF_UTIL_PKG.GetItemAttrText
2309 ( itemtype =>itemtype,
2310 itemkey => itemkey,
2311 aname => 'DOCUMENT_SUBTYPE'
2312 );
2313
2314 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'l_document_id'
2315 ||to_char(l_document_id)||'l_document_type='||l_document_type
2316 ||'l_vendor_id='||to_char(l_vendor_id)||'l_supplier='
2317 ||l_vendor_name||'l_vendor_site_id='
2318 ||to_char(l_vendor_site_id));
2319
2320 BEGIN
2321
2322 -- SQL What:Select vendor information
2323 -- SQL Why :These are not already available from WF. hence the select
2324 SELECT poh.vendor_id,
2325 pov.vendor_name,
2326 poh.vendor_site_id,
2327 povs.vendor_site_code
2328 INTO l_vendor_id,l_vendor_name,l_vendor_site_id,l_vendor_site_code
2329 FROM po_headers poh ,po_vendors pov,po_vendor_sites povs
2330 WHERE po_header_id = l_document_id
2331 AND poh.vendor_id=pov.vendor_id
2332 AND poh.vendor_site_id=povs.vendor_site_id;
2333
2334 EXCEPTION
2335 WHEN OTHERS THEN
2336 x_progress:='PO_NEGOTIATIONS4_PVT.Place_SourcingInfoOnReq: 02.';
2337 RAISE;
2338 END;
2339
2340
2341 FOR l_document_lines_index in document_lines_cursor
2342 LOOP
2343
2344 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,
2345 'before placing info on req- l_document_id='
2346 ||to_char(l_document_id)||'blanket_po_line_num='
2347 ||to_char(l_document_lines_index.line_num)
2348 ||'auction_header_id='
2349 ||to_char(l_document_lines_index.auction_header_id)
2350 ||'bid_line_number='
2351 ||to_char(l_document_lines_index.bid_line_number)
2352 ||'bid_number='||to_char(l_document_lines_index.bid_number));
2353
2354 -- SQL What:Place the sourcing information on the AVAILABLE backing reqs.
2355 -- SQL Why :Next step is to launch the create doc wf to create a release
2356 -- based on this blanket which we just approved. Here we
2357 -- require this sourcing information.
2358 UPDATE po_requisition_lines
2359 SET suggested_vendor_name=l_vendor_name,
2360 suggested_vendor_location = l_vendor_site_code,
2361 document_type_code=l_document_subtype,
2362 blanket_po_header_id= l_document_id,
2363 blanket_po_line_num=l_document_lines_index.line_num
2364 -- suggested_vendor_id = l_vendor_id,
2365 -- suggested_vendor_site_id=l_vendor_site_id
2366 WHERE auction_header_id = l_document_lines_index.auction_header_id
2367 AND bid_line_number=l_document_lines_index.bid_line_number
2368 AND bid_number =l_document_lines_index.bid_number--placed on anotherneg
2369 AND line_location_id is null --placed on another po doc
2370 AND nvl(cancel_flag,'N')= 'N' --Cancelled
2371 AND nvl(closed_code,'OPEN') <> 'FINALLY CLOSED' --finally closed
2372 AND nvl(modified_by_agent_flag,'N') <> 'Y'; --buyer modified the req.
2373
2374 END LOOP;
2375
2376
2377 resultout:=wf_engine.eng_completed || ':' || 'Y';
2378
2379 x_progress:='PO_NEGOTIATIONS4_PVT.Place_SourcingInfoOnReq: 02. Result= '||'Y';
2380 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress||sql%rowcount);
2381
2382 EXCEPTION
2383 WHEN OTHERS THEN
2384 resultout := wf_engine.eng_completed || ':' || 'N';
2385 END Place_SourcingInfoOnReq;
2386
2387
2388 /**
2389 * Private Procedure: Launch_CreateDocWF
2390 * Requires: Std. workflow input parameters
2391 * Modifies: NA.
2392 * Effects: This procedure Launches create document workflow to automatically
2393 * create a release based on the blaket the po approval workflow just
2394 * approved.
2395 * Returns: std. workflow out parameters
2396 */
2397 PROCEDURE Launch_CreateDocWF
2398 ( ItemType IN VARCHAR2 ,
2399 ItemKey IN VARCHAR2 ,
2400 actid IN NUMBER ,
2401 funcmode IN VARCHAR2 ,
2402 resultout OUT NOCOPY VARCHAR2
2403 ) IS
2404
2405 x_progress varchar2(200);
2406
2407 l_ItemType varchar2(8);
2408 l_ItemKey varchar2(80);
2409 l_workflow_process varchar2(30);
2410 l_dummy varchar2(38);
2411 l_orgid number;
2412 l_interface_source varchar2(30);
2413 l_document_id po_headers.po_header_id%type;
2414
2415 l_user_id number;
2416 l_resp_id number;
2417 l_appl_id number;
2418
2419 cursor C1 is
2420 select WF_CREATEDOC_ITEMTYPE,WF_CREATEDOC_PROCESS
2421 from po_document_types
2422 where DOCUMENT_TYPE_CODE= 'REQUISITION'
2423 and DOCUMENT_SUBTYPE = 'PURCHASE';
2424
2425 BEGIN
2426
2427 /*Bug 7517077, assigning value to the resultout parameter, since the next activity gets driven based on this*/
2428 if (funcmode <> wf_engine.eng_run) then
2429 resultout := wf_engine.eng_null;
2430 return;
2431 end if;
2432
2433 -- Get the org context
2434 l_orgid := PO_WF_UTIL_PKG.GetItemAttrNumber
2435 (itemtype => itemtype,
2436 itemkey => itemkey,
2437 aname => 'ORG_ID');
2438 l_user_id := PO_WF_UTIL_PKG.GetItemAttrNumber
2439 (itemtype => itemtype,
2440 itemkey => itemkey,
2441 aname => 'USER_ID');
2442 l_resp_id := PO_WF_UTIL_PKG.GetItemAttrNumber
2443 (itemtype => itemtype,
2444 itemkey => itemkey,
2445 aname => 'RESPONSIBILITY_ID');
2446 l_appl_id := PO_WF_UTIL_PKG.GetItemAttrNumber
2447 (itemtype => itemtype,
2448 itemkey => itemkey,
2449 aname => 'APPLICATION_ID');
2450 l_document_id := PO_WF_UTIL_PKG.GetItemAttrNumber
2451 (itemtype => itemtype,
2452 itemkey => itemkey,
2453 aname => 'DOCUMENT_ID');
2454
2455 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'l_orgid='||to_char(l_orgid)||'l_user_id='||to_char(l_user_id)||'l_resp_id='||to_char(l_resp_id)||'l_appl_id='||to_char(l_appl_id)||'l_document_id='||to_char(l_document_id));
2456
2457 /* Since the call may be started from background engine (new seesion),
2458 * need to ensure the fnd context is correct
2459 */
2460
2461 if (l_user_id is not null and
2462 l_resp_id is not null and
2463 l_appl_id is not null )then
2464
2465 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'apps_init');
2466 fnd_global.APPS_INITIALIZE(l_user_id, l_resp_id, l_appl_id);
2467 end if;
2468
2469 IF l_orgid is NOT NULL THEN
2470 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'setting org');
2471 PO_MOAC_UTILS_PVT.set_org_context(l_orgid) ; -- <R12 MOAC>
2472 END IF;
2473
2474 x_progress := 'PO_NEGOTIATIONS4_PVT.Launch_CreateDocWF:01';
2475 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
2476
2477 /* Create the ItemKey: Use the PO workflow sequence */
2478 select to_char(PO_WF_ITEMKEY_S.nextval) into l_dummy from sys.dual;
2479
2480 OPEN C1;
2481 FETCH C1 into l_ItemType, l_workflow_process;
2482
2483 IF C1%NOTFOUND THEN
2484 close C1;
2485 raise NO_DATA_FOUND;
2486 END IF;
2487
2488 CLOSE C1;
2489
2490 l_ItemKey := to_char(l_document_id) || '-' || l_dummy;
2491
2492 x_progress := 'PO_NEGOTIATIONS4_PVT.Launch_CreateDocWF:02 ItemType=' ||
2493 l_ItemType || ' ItemKey=' || l_ItemKey;
2494 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
2495
2496 x_progress := '30: Launch_CreateDocWF: Called with following parameters:' ||
2497 'ItemType = ' || l_ItemType || '/ ' ||
2498 'ItemKey = ' || l_ItemKey || '/ ' ||
2499 'workflow_process = ' || l_workflow_process;
2500
2501 po_wf_debug_pkg.insert_debug(Itemtype,Itemkey,x_progress);
2502
2503 IF ( l_ItemType is NOT NULL ) AND
2504 ( l_ItemKey is NOT NULL) THEN
2505 wf_engine.CreateProcess
2506 (itemtype => l_itemtype,
2507 itemkey => l_itemkey,
2508 process => l_workflow_process );
2509
2510 x_progress:= '40: Launch_CreateDocWF: Just after CreateProcess';
2511 po_wf_debug_pkg.insert_debug(Itemtype,Itemkey,x_progress);
2512
2513 PO_WF_UTIL_PKG.SetItemAttrNumber
2514 (itemtype => l_itemtype,
2515 itemkey => l_itemkey,
2516 aname => 'ORG_ID',
2517 avalue => l_orgid);
2518
2519 PO_WF_UTIL_PKG.SetItemAttrNumber
2520 (itemtype => l_itemtype,
2521 itemkey => l_itemkey,
2522 aname => 'CONSUME_REQ_DEMAND_DOC_ID',
2523 avalue => l_DOCUMENT_ID);
2524
2525 /* Kick off the process */
2526
2527 x_progress := '40: Launch_CreateDocWF: Kicking off StartProcess ';
2528 po_wf_debug_pkg.insert_debug(Itemtype,Itemkey,x_progress);
2529
2530 wf_engine.StartProcess(itemtype => l_itemtype,
2531 itemkey => l_itemkey );
2532 END IF;
2533 /*Bug 7517077, assigning value 'ACTIVITY PERFORMED' to the resultout parameter, since the next activity gets driven based on this*/
2534 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
2535 EXCEPTION
2536 WHEN OTHERS THEN
2537 wf_core.context('PO_NEGOTIATIONS4_PVT','Launch_CreateDocWF',
2538 x_progress);
2539 raise;
2540 END Launch_CreateDocWF;
2541
2542
2543 -- <FPI JFMIP Req Split START>
2544 -------------------------------------------------------------------------------
2545 --Start of Comments
2546 --Name: handle_funds_reversal
2547 --Pre-reqs:
2548 -- None.
2549 --Modifies:
2550 -- PO_REQ_DISTRIBUTIONS
2551 --Locks:
2552 -- None.
2553 --Function:
2554 -- Make a call to encumbrance api to reverse the funds reservation
2555 -- for the parent and reserve the funds for the children in force mode.
2556 --Parameters:
2557 --OUT:
2558 --x_online_report_id
2559 -- Specify the online report ID
2560 --Testing:
2561 -- None.
2562 --End of Comments
2563 -------------------------------------------------------------------------------
2564 PROCEDURE handle_funds_reversal
2565 ( p_api_version IN NUMBER,
2566 p_commit IN VARCHAR2,
2567 x_return_status OUT NOCOPY VARCHAR2,
2568 x_msg_count OUT NOCOPY NUMBER,
2569 x_msg_data OUT NOCOPY VARCHAR2
2570 )
2571 IS
2572 l_online_report_id PO_ONLINE_REPORT_TEXT.online_report_id%TYPE;
2573 BEGIN
2574 handle_funds_reversal(p_api_version => p_api_version,
2575 p_commit => p_commit,
2576 x_return_status => x_return_status,
2577 x_msg_count => x_msg_count,
2578 x_msg_data => x_msg_data,
2579 x_online_report_id => l_online_report_id);
2580 END handle_funds_reversal;
2581
2582 PROCEDURE handle_funds_reversal
2583 ( p_api_version IN NUMBER,
2584 p_commit IN VARCHAR2,
2585 x_return_status OUT NOCOPY VARCHAR2,
2586 x_msg_count OUT NOCOPY NUMBER,
2587 x_msg_data OUT NOCOPY VARCHAR2,
2588 x_online_report_id OUT NOCOPY NUMBER
2589 )
2590 IS
2591
2592 l_api_name CONSTANT varchar2(30) := 'HANDLE_FUNDS_REVERSAL';
2593 l_api_version CONSTANT NUMBER := 1.0;
2594
2595 l_module VARCHAR2(100);
2596 l_progress VARCHAR2(3);
2597
2598 --define object type variable for calling encumbrance api.
2599 l_before_dist_ids_tbl po_tbl_number;
2600 l_after_dist_ids_tbl po_tbl_number;
2601 l_po_return_code VARCHAR2(20);
2602 l_req_org_id po_tbl_number; --Bug 5666854
2603 l_orig_org_id number; --Bug 5666854
2604 l_org_context_changed VARCHAR2(1) := 'N';--Bug 5666854
2605
2606 BEGIN
2607
2608 l_progress :='000';
2609 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2610 l_orig_org_id := PO_MOAC_UTILS_PVT.GET_CURRENT_ORG_ID;
2611 IF G_FND_DEBUG = 'Y' THEN
2612 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2613 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module,
2614 'Entering ' || G_PKG_NAME || '.' || l_api_name);
2615 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module,
2616 ' current org = ' ||l_orig_org_id);
2617 END IF;
2618 END IF;
2619
2620 SAVEPOINT HANDLE_FUNDS_REVERSAL_PVT;
2621
2622 l_progress :='010';
2623 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2624 IF G_FND_DEBUG = 'Y' THEN
2625 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2626 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2627 'Compatible_API_Call ');
2628 END IF;
2629 END IF;
2630
2631 IF NOT FND_API.Compatible_API_Call
2632 (
2633 l_api_version,
2634 p_api_version,
2635 l_api_name,
2636 G_PKG_NAME
2637 )
2638 THEN
2639 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2640 END IF;
2641
2642 --do the dump of input values if the log level is statement.
2643 l_progress :='015';
2644 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2645 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL=FND_LOG.LEVEL_STATEMENT THEN
2646 Print_Global_Table(l_module);
2647 END IF;
2648
2649 l_progress :='020';
2650
2651 --Bug 5666854 START
2652 -- Set org context to the Org where the current requisition line
2653 -- is raised, because handle_funds_reversal needs to operate
2654 -- in the Requesting OU's org context
2655
2656 -- SQL What: Find out the distinct OrgIds to which the requisitions
2657 -- belong to
2658 -- SQL Why: Need to set org context to the OU where the
2659 -- requisition line is raised
2660 SELECT distinct org_id bulk collect
2661 INTO l_req_org_id
2662 FROM po_requisition_headers_all
2663 WHERE requisition_header_id IN
2664 (SELECT DISTINCT requisition_header_id
2665 FROM po_req_split_lines_gt);
2666
2667 IF G_FND_DEBUG = 'Y' THEN
2668 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2669 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module,
2670 'Number of distinct orgs to be considered = '||l_req_org_id.count);
2671 END IF;
2672 END IF;
2673 for l_index IN 1..l_req_org_id.count
2674 LOOP
2675 --Set Org Context to the Requisition OrgId
2676 --Bug 10131290.Added (l_orig_org_id is null) to the IF condition
2677 IF (l_orig_org_id is null) OR (l_req_org_id(l_index) <> l_orig_org_id) THEN
2678 PO_MOAC_UTILS_PVT.set_org_context(l_req_org_id(l_index)) ;
2679 l_org_context_changed := 'Y';
2680 END IF;
2681 IF G_FND_DEBUG = 'Y' THEN
2682 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2683 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module,
2684 'Current org id = '||l_req_org_id(l_index));
2685 END IF;
2686 END IF;
2687 BEGIN
2688 --Select all the distribution lines which are to be reserved and
2689 --unreserved into a plsql table
2690
2691 l_progress :='030';
2692 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2693 IF G_FND_DEBUG = 'Y' THEN
2694 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2695 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2696 'Before: select BEFORE req distributions for encumb. adjustmnets');
2697 END IF;
2698 END IF;
2699
2700 SELECT prd.distribution_id
2701 BULK COLLECT INTO
2702 l_before_dist_ids_tbl
2703 FROM po_req_distributions prd,
2704 po_req_split_lines_gt prs
2705 WHERE prd.requisition_line_id = prs.requisition_line_id
2706 AND (prs.bid_number = prs.min_bid_number
2707 OR
2708 -- when called from autocreate req modify bid number
2709 -- would be null
2710 prs.bid_number IS NULL
2711 )
2712 AND prs.record_status = 'S'
2713 AND nvl(prd.prevent_encumbrance_flag,'N') <> 'Y'
2714 AND nvl(prd.encumbered_flag,'N') ='Y';
2715
2716 l_progress :='040';
2717 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2718
2719 IF G_FND_DEBUG = 'Y' THEN
2720 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2721 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2722 'l_before_dist_ids_tbl, count= '||l_before_dist_ids_tbl.count);
2723 END IF;
2724 FOR l_log_index IN 1.. l_before_dist_ids_tbl.COUNT
2725 LOOP
2726 -- Bug 4618614: Workaround GSCC error for checking logging statement.
2727 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2728 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2729 'l_before_dist_ids_tbl('||to_char(l_log_index)||')='
2730 ||l_before_dist_ids_tbl(l_log_index));
2731 END IF;
2732 END LOOP;
2733 END IF; --if g_fnd_debug='Y'
2734
2735 l_progress :='050';
2736 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2737 IF G_FND_DEBUG = 'Y' THEN
2738 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2739 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2740 'Before: select AFTER req distributions for encumb. adjustmnets');
2741 END IF;
2742 END IF;
2743
2744 SELECT prd.distribution_id
2745 BULK COLLECT INTO
2746 l_after_dist_ids_tbl
2747 FROM po_req_distributions prd,
2748 po_req_split_lines_gt prs
2749 WHERE prd.requisition_line_id = prs.new_req_line_id
2750 AND prs.record_status in ('S','N')
2751 AND nvl(prd.prevent_encumbrance_flag,'N') <> 'Y'
2752 AND nvl(prd.encumbered_flag,'N') ='Y';
2753
2754 l_progress :='060';
2755 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2756 IF G_FND_DEBUG = 'Y' THEN
2757 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2758 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2759 'l_after_dist_ids_tbl, count= '||l_after_dist_ids_tbl.count);
2760 END IF;
2761 FOR l_log_index IN 1.. l_after_dist_ids_tbl.COUNT
2762 LOOP
2763 -- Bug 4618614: Workaround GSCC error for checking logging statement.
2764 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2765 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2766 'l_after_dist_ids_tbl('||to_char(l_log_index)||')='
2767 ||l_after_dist_ids_tbl(l_log_index));
2768 END IF;
2769 END LOOP;
2770 END IF; --if g_fnd_debug='Y'
2771
2772 EXCEPTION
2773 WHEN OTHERS THEN
2774 po_message_s.sql_error
2775 ('Exception of HANDLE_FUNDS_REVERSAL()', l_progress ,
2776 sqlcode);
2777 FND_MSG_PUB.Add;
2778 IF G_FND_DEBUG = 'Y' THEN
2779 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
2780 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION, l_module,
2781 'Exception: select req distributions for encumb. adjustmnets');
2782 END IF;
2783 END IF;
2784 RAISE;
2785 END;
2786
2787 --call the encumbrance api
2788 l_progress :='070';
2789 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2790 IF G_FND_DEBUG = 'Y' THEN
2791 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2792 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2793 'Before: Calling encumbrance api');
2794 END IF;
2795 END IF;
2796
2797 PO_DOCUMENT_FUNDS_PVT.do_req_split(
2798 x_return_status => x_return_status
2799 , p_before_dist_ids_tbl => l_before_dist_ids_tbl
2800 , p_after_dist_ids_tbl => l_after_dist_ids_tbl
2801 , p_employee_id => NULL
2802 , p_override_funds => PO_DOCUMENT_FUNDS_PVT.g_parameter_USE_PROFILE
2803 , p_override_date => SYSDATE
2804 , x_po_return_code => l_po_return_code
2805 , x_online_report_id => x_online_report_id
2806 );
2807
2808 l_progress :='080';
2809 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2810
2811 IF G_FND_DEBUG = 'Y' THEN
2812 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2813 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2814 'After: Calling encumbrance api; Status is '||x_return_status);
2815 END IF;
2816 END IF;
2817
2818 IF (x_return_status = FND_API.g_ret_sts_error) OR
2819 (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
2820
2821 IF G_FND_DEBUG = 'Y' THEN
2822 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
2823 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION, l_module,
2824 'online_report_id = '||to_char(x_online_report_id)
2825 ||', po_return_code = '||l_po_return_code);
2826 END IF;
2827 END IF;
2828
2829
2830 IF (x_return_status = FND_API.g_ret_sts_error) THEN
2831 RAISE FND_API.g_exc_error;
2832 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
2833 RAISE FND_API.g_exc_unexpected_error;
2834 END IF;
2835 END IF; /*IF (x_return_status = FND_API.g_ret_sts_error)*/
2836 END LOOP;
2837
2838 -- Switch the org context back to the current OU if it has been changed
2839 IF (l_org_context_changed = 'Y') THEN
2840 PO_MOAC_UTILS_PVT.set_org_context(l_orig_org_id);
2841 l_org_context_changed := 'N';
2842 END IF;
2843 --Bug 5666854 END
2844
2845 --bug 3537764: removed code to update the prevent enc flag
2846 --this is handled within the encumbrance call now
2847 --(in po_document_funds_pvt.do_req_split)
2848
2849
2850 IF FND_API.To_Boolean( p_commit ) THEN
2851 COMMIT WORK;
2852 END IF;
2853
2854 -- Standard call to get message count and if count is 1,
2855 -- get message info.
2856 FND_MSG_PUB.Count_And_Get
2857 ( p_count => x_msg_count ,
2858 p_data => x_msg_data
2859 );
2860
2861 l_progress :='200';
2862 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2863 IF G_FND_DEBUG = 'Y' THEN
2864 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2865 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2866 'Before: Setting success status to x_return_status');
2867 END IF;
2868 END IF;
2869 x_return_status := FND_API.G_RET_STS_SUCCESS;
2870
2871 EXCEPTION
2872 WHEN FND_API.g_exc_unexpected_error THEN
2873 ROLLBACK TO HANDLE_FUNDS_REVERSAL_PVT;
2874 x_msg_data := FND_MSG_PUB.GET();
2875 x_return_status := FND_API.g_ret_sts_unexp_error;
2876 IF G_FND_DEBUG = 'Y' THEN
2877 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
2878 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION, l_module,
2879 'Exception: UnexpectedError '||x_msg_data||sqlerrm);
2880 END IF;
2881 END IF;
2882 WHEN FND_API.g_exc_error THEN
2883 ROLLBACK TO HANDLE_FUNDS_REVERSAL_PVT;
2884 x_return_status := FND_API.g_ret_sts_error;
2885 FND_MSG_PUB.Count_And_Get
2886 ( p_count => x_msg_count ,
2887 p_data => x_msg_data
2888 );
2889 IF G_FND_DEBUG = 'Y' THEN
2890 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
2891 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION, l_module,
2892 'Exception: ExpectedError '||x_msg_data||sqlerrm);
2893 END IF;
2894 END IF;
2895
2896 WHEN OTHERS THEN
2897 ROLLBACK TO HANDLE_FUNDS_REVERSAL_PVT;
2898 --x_msg_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST);
2899 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2900
2901 FND_MSG_PUB.Count_And_Get
2902 ( p_count => x_msg_count ,
2903 p_data => x_msg_data
2904 );
2905 IF G_FND_DEBUG = 'Y' THEN
2906 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
2907 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION, l_module,
2908 'Exception: UnExpectedError '||x_msg_data||sqlerrm);
2909 END IF;
2910 END IF;
2911
2912 END handle_funds_reversal;
2913
2914
2915 -------------------------------------------------------------------------------
2916 --Start of Comments
2917 --Name: handle_tax_adjustments
2918 --Pre-reqs:
2919 -- None.
2920 --Modifies:
2921 -- PO_REQ_DISTRIBUTIONS
2922 --Locks:
2923 -- None.
2924 --Function:
2925 -- Handle tax adjustments.
2926 --Parameters:
2927 -- None.
2928 --Testing:
2929 -- None.
2930 --End of Comments
2931 -------------------------------------------------------------------------------
2932 PROCEDURE handle_tax_adjustments
2933 ( p_api_version IN NUMBER,
2934 p_commit IN VARCHAR2,
2935 x_return_status OUT NOCOPY VARCHAR2,
2936 x_msg_count OUT NOCOPY NUMBER,
2937 x_msg_data OUT NOCOPY VARCHAR2
2938 )
2939 IS
2940
2941 l_api_name CONSTANT varchar2(30) := 'HANDLE_TAX_ADJUSTMENTS';
2942 l_api_version CONSTANT NUMBER := 1.0;
2943
2944 l_module VARCHAR2(100);
2945 l_progress VARCHAR2(3);
2946
2947 sum_new_line_r_tax_tbl round_tax_tbl_type;
2948 sum_new_line_nr_tax_tbl round_tax_tbl_type;
2949 min_dist_id_tax_tbl min_dist_id_tbl_type;
2950 req_line_id_tax_tbl requisition_line_id_tbl_type;
2951 sum_orig_line_r_tax_tbl round_tax_tbl_type;
2952 sum_orig_line_nr_tax_tbl round_tax_tbl_type;
2953
2954 BEGIN
2955
2956 l_progress :='000';
2957 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2958 IF G_FND_DEBUG = 'Y' THEN
2959 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2960 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, l_module,
2961 'Entering ' || G_PKG_NAME || '.' || l_api_name);
2962 END IF;
2963 END IF;
2964
2965 SAVEPOINT HANDLE_TAX_ADJUSTMENTS_PVT;
2966
2967 l_progress :='010';
2968 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
2969 IF G_FND_DEBUG = 'Y' THEN
2970 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2971 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
2972 'Compatible_API_Call ');
2973 END IF;
2974 END IF;
2975
2976 IF NOT FND_API.Compatible_API_Call
2977 (
2978 l_api_version,
2979 p_api_version,
2980 l_api_name,
2981 G_PKG_NAME
2982 )
2983 THEN
2984 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2985 END IF;
2986
2987 --do the dump of input values if the log level is statement.
2988 l_progress :='015';
2989 l_module := G_MODULE_PREFIX||l_api_name||'.'||'015'||'.';
2990 IF G_FND_DEBUG = 'Y' AND G_FND_DEBUG_LEVEL=FND_LOG.LEVEL_STATEMENT THEN
2991 Print_Global_Table(l_module);
2992 END IF;
2993
2994 l_progress :='020';
2995
2996 BEGIN
2997
2998 l_progress :='030';
2999 l_module := G_MODULE_PREFIX||l_api_name||'.'||'030'||'.';
3000 IF G_FND_DEBUG = 'Y' THEN
3001 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3002 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3003 'Before: Collect the info to take care of TAX rounding ');
3004 END IF;
3005 END IF;
3006
3007 --The tax info collection for new req lines, this will be needed
3008 --for update of tax later
3009 SELECT SUM(prd.recoverable_tax),
3010 SUM(prd.nonrecoverable_tax),
3011 MIN(prd.distribution_id)
3012 BULK COLLECT INTO
3013 sum_new_line_r_tax_tbl,
3014 sum_new_line_nr_tax_tbl,
3015 min_dist_id_tax_tbl
3016 FROM po_req_distributions prd, po_req_split_lines_gt prs
3017 WHERE prd.requisition_line_id = prs.new_req_line_id
3018 AND prs.record_status in ('S','N')
3019 GROUP BY prs.requisition_line_id,
3020 prd.code_combination_id;
3021
3022 IF G_FND_DEBUG = 'Y' THEN
3023 FOR i in 1..sum_new_line_r_tax_tbl.COUNT LOOP
3024 -- Bug 4618614: Workaround GSCC error for checking logging statement.
3025 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3026
3027 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3028 'sum_new_line_r_tax_tbl('||i||'): '||sum_new_line_r_tax_tbl(i)||','||
3029 'sum_new_line_nr_tax_tbl('||i||'): '||sum_new_line_nr_tax_tbl(i)||','||
3030 'min_dist_id_tax_tbl('||i||'): '||min_dist_id_tax_tbl(i));
3031 END IF;
3032 END LOOP;
3033 END IF;
3034
3035 l_progress :='040';
3036 --The tax info collection for original req lines, this will be needed
3037 --for update of tax later
3038 SELECT prs.requisition_line_id,
3039 SUM(prd.recoverable_tax),
3040 SUM(prd.nonrecoverable_tax)
3041 BULK COLLECT INTO
3042 req_line_id_tax_tbl,
3043 sum_orig_line_r_tax_tbl,
3044 sum_orig_line_nr_tax_tbl
3045 FROM po_req_distributions prd, po_req_split_lines_gt prs
3046 WHERE prd.requisition_line_id = prs.requisition_line_id
3047 AND (prs.bid_number = prs.min_bid_number OR
3048 -- when called from autocreate req modify bid number would be null
3049 prs.bid_number IS NULL)
3050 AND prs.record_status = 'S'
3051 GROUP BY prs.requisition_line_id,
3052 prd.code_combination_id;
3053
3054 l_progress :='050';
3055 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3056
3057 IF G_FND_DEBUG = 'Y' THEN
3058 FOR i in 1..req_line_id_tax_tbl.COUNT LOOP
3059 -- Bug 4618614: Workaround GSCC error for checking logging statement.
3060 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3061
3062 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3063 'req_line_id_tax_tbl('||i||'): '||req_line_id_tax_tbl(i)||','||
3064 'sum_orig_line_r_tax_tbl('||i||'): '||sum_orig_line_r_tax_tbl(i)||','||
3065 'sum_orig_line_nr_tax_tbl('||i||'): '||sum_orig_line_nr_tax_tbl(i));
3066 END IF;
3067 END LOOP;
3068 END IF;
3069
3070 IF G_FND_DEBUG = 'Y' THEN
3071 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3072 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3073 'After: Collect the info to take care of TAX rounding ');
3074 END IF;
3075 END IF;
3076
3077 EXCEPTION
3078 WHEN OTHERS THEN
3079 po_message_s.sql_error
3080 ('Exception of HANDLE_TAX_ADJUSTMENTS()', l_progress ,
3081 sqlcode);
3082 FND_MSG_PUB.Add;
3083 IF G_FND_DEBUG = 'Y' THEN
3084 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3085 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3086 'Exception: Collect the info to take care of TAX rounding ');
3087 END IF;
3088 END IF;
3089 RAISE;
3090 END;
3091
3092
3093 BEGIN
3094
3095 l_progress :='100';
3096 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3097 IF G_FND_DEBUG = 'Y' THEN
3098 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3099 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3100 'Before: update to take care of TAX rounding ');
3101 END IF;
3102 END IF;
3103
3104 --sql what: Update one of the distributions(which has min dist_id)
3105 -- with the excess/less of the sum of RECOVERABLE and
3106 -- NONRECOVERABLE TAX of all
3107 -- the distribution lines.
3108 --sql why : To take care of TAX rounding issue.
3109 FORALL l_tax_rounding_index in 1.. req_line_id_tax_tbl.COUNT
3110 UPDATE PO_REQ_DISTRIBUTIONS
3111 SET recoverable_tax = recoverable_tax+
3112 (sum_orig_line_r_tax_tbl(l_tax_rounding_index) -
3113 sum_new_line_r_tax_tbl(l_tax_rounding_index)),
3114 nonrecoverable_tax = nonrecoverable_tax+
3115 (sum_orig_line_nr_tax_tbl(l_tax_rounding_index) -
3116 sum_new_line_nr_tax_tbl(l_tax_rounding_index))
3117 WHERE distribution_id=
3118 min_dist_id_tax_tbl(l_tax_rounding_index);
3119
3120 l_progress :='110';
3121 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3122 IF G_FND_DEBUG = 'Y' THEN
3123 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3124 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3125 'After: update to take care of TAX rounding; Updated '
3126 ||sql%rowcount||' rows');
3127 END IF;
3128 END IF;
3129
3130 EXCEPTION
3131 WHEN OTHERS THEN
3132 po_message_s.sql_error
3133 ('Exception of HANDLE_TAX_ADJUSTMENTS()', l_progress ,
3134 sqlcode);
3135 FND_MSG_PUB.Add;
3136 IF G_FND_DEBUG = 'Y' THEN
3137 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
3138 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION, l_module,
3139 'Exception: update to take care of TAX rounding ');
3140 END IF;
3141 END IF;
3142 RAISE;
3143 END;
3144
3145 IF G_FND_DEBUG = 'Y' THEN
3146 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3147 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3148 'Before Commit');
3149 END IF;
3150 END IF;
3151
3152 IF FND_API.To_Boolean( p_commit ) THEN
3153 COMMIT WORK;
3154 END IF;
3155
3156 IF G_FND_DEBUG = 'Y' THEN
3157 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3158 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3159 'After Commit');
3160 END IF;
3161 END IF;
3162
3163 -- Standard call to get message count and if count is 1,
3164 -- get message info.
3165 FND_MSG_PUB.Count_And_Get
3166 ( p_count => x_msg_count ,
3167 p_data => x_msg_data
3168 );
3169
3170 l_progress :='200';
3171 l_module := G_MODULE_PREFIX||l_api_name||'.'||l_progress||'.';
3172 IF G_FND_DEBUG = 'Y' THEN
3173 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3174 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_module,
3175 'Before: Setting success status to x_return_status');
3176 END IF;
3177 END IF;
3178 x_return_status := FND_API.G_RET_STS_SUCCESS;
3179
3180
3181 EXCEPTION
3182 WHEN FND_API.g_exc_unexpected_error THEN
3183 ROLLBACK TO HANDLE_TAX_ADJUSTMENTS_PVT;
3184 x_msg_data := FND_MSG_PUB.GET();
3185 x_return_status := FND_API.g_ret_sts_unexp_error;
3186 IF G_FND_DEBUG = 'Y' THEN
3187 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
3188 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION, l_module,
3189 'Exception: UnexpectedError '||x_msg_data||sqlerrm);
3190 END IF;
3191 END IF;
3192
3193 WHEN FND_API.g_exc_error THEN
3194 ROLLBACK TO HANDLE_TAX_ADJUSTMENTS_PVT;
3195 x_return_status := FND_API.g_ret_sts_error;
3196 FND_MSG_PUB.Count_And_Get
3197 ( p_count => x_msg_count ,
3198 p_data => x_msg_data
3199 );
3200 IF G_FND_DEBUG = 'Y' THEN
3201 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
3202 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION, l_module,
3203 'Exception: ExpectedError '||x_msg_data||sqlerrm);
3204 END IF;
3205 END IF;
3206
3207 WHEN OTHERS THEN
3208 ROLLBACK TO HANDLE_TAX_ADJUSTMENTS_PVT;
3209 --x_msg_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST);
3210 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3211
3212 FND_MSG_PUB.Count_And_Get
3213 ( p_count => x_msg_count ,
3214 p_data => x_msg_data
3215 );
3216 IF G_FND_DEBUG = 'Y' THEN
3217 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
3218 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION, l_module,
3219 'Exception: UnExpectedError '||x_msg_data||sqlerrm);
3220 END IF;
3221 END IF;
3222
3223 END handle_tax_adjustments;
3224
3225 -- <FPI JFMIP Req Split END>
3226
3227 END PO_NEGOTIATIONS4_PVT;