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