[Home] [Help]
PACKAGE BODY: APPS.PO_MODIFY_REQUISITION_PVT
Source
1 PACKAGE BODY PO_MODIFY_REQUISITION_PVT AS
2 /* $Header: PO_MODIFY_REQUISITION_PVT.plb 120.12.12020000.2 2013/02/10 20:39:45 vegajula ship $ */
3
4 -- Logging global constants
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PO_MODIFY_REQUISITION_PVT';
6 D_PACKAGE_BASE CONSTANT VARCHAR2(100) := PO_LOG.get_package_base(G_PKG_NAME);
7
8 --Other constants used in code
9 g_REQ_LINES_ENTITY CONSTANT VARCHAR2(20) := 'REQ_LINES';
10 g_EXPLODE_REQ_ACTION CONSTANT VARCHAR2(20) :='Explode_Req';
11 g_CALLING_PROGRAM_SPLIT CONSTANT VARCHAR2(20) :='SPLIT';
12 g_CALLING_PROGRAM_CATALOG CONSTANT VARCHAR2(20) :='CATALOG';
13 g_TAX_ATTRIBUTE_CREATE CONSTANT VARCHAR2(20) :='CREATE';
14 /**
15 Function GET_UDA_TEMPLATE_ID
16 Returns UDA_TEMPLATE_ID
17 for the given REQUISITION_LINE_ID.
18 */
19 FUNCTION GET_UDA_TEMPLATE_ID(REQ_LINE_ID IN NUMBER
20 ) RETURN NUMBER
21 IS
22 l_uda_template NUMBER;
23 BEGIN
24 SELECT UDA_TEMPLATE_ID
25 into l_uda_template
26 FROM PO_REQUISITION_LINES_ALL
27 WHERE REQUISITION_LINE_ID=REQ_LINE_ID;
28 RETURN l_uda_template;
29 END GET_UDA_TEMPLATE_ID;
30
31 /**
32 Function GET_ATTR_GROUP_NAME
33 returns ATTR_GROUP_NAME
34 when ATTRIBUTE_CATEGORY ='PRICING'
35 Algorithm:
36 For the given REQUISITION_LINE_ID
37 the ATTR_GROUP_NAME is computed for 'PRICING' ATTRIBUTE_CATEGORY
38 */
39 FUNCTION GET_ATTR_GROUP_NAME(P_REQ_LINE_ID IN NUMBER
40 ) RETURN VARCHAR2
41 IS
42 L_ATTR_GROUP_NAME VARCHAR2(30);
43 BEGIN
44
45 SELECT AGS.ATTR_GROUP_NAME
46 INTO L_ATTR_GROUP_NAME
47 FROM PO_REQ_LINES_EXT_B prleb,
48 po_uda_ag_template_usages usages,
49 ego_attr_groups_v ags,
50 po_requisition_lines_all prla
51 WHERE prleb.requisition_line_id = p_req_line_id
52 AND prleb.attr_group_id = ags.attr_group_id
53 AND prleb.attr_group_id = usages.attribute_group_id
54 AND usages.attribute_category = 'PRICING'
55 AND prla.requisition_line_id = p_req_line_id
56 AND usages.template_id = prla.uda_template_id
57 AND usages.attribute1 ='IDC_NA'
58 AND usages.attribute2= prla.contract_type;
59 RETURN L_ATTR_GROUP_NAME;
60 EXCEPTION
61 WHEN No_Data_Found THEN
62 L_ATTR_GROUP_NAME := NULL;
63
64 RETURN L_ATTR_GROUP_NAME;
65 END GET_ATTR_GROUP_NAME;
66
67
68 /**
69 * Private Procedure: split_requisition_lines
70 * Requires: API message list has been initialized if p_init_msg_list is
71 * false.
72 * Modifies: Inserts new req lines and their distributions, For parent .
73 * req lines, update requisition_lines table to modified_by_agent_flag = 'Y' .
74 * Also sets prevent encumbrace flag to 'Y' in the po_req_distributions table.
75 * Effects: This api split the requisition lines, into two lines with specified quantity.
76 * This api uses a global temp.table to process the input given by autocreate(HTML) and
77 * inserts records into po_requisition_lines_all and po_req_distributions_all table.
78 * This api also handles the encumbrace effect of splitting requisition lines. This
79 * api would be called from Autocreate HTML.
80 *
81 * Returns:
82 * x_return_status - FND_API.G_RET_STS_SUCCESS if action succeeds
83 * FND_API.G_RET_STS_ERROR if action fails
84 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
85 * x_msg_count returns count of messages in the stack.
86 * x_msg_data returns message only if 1 message.
87 * Algorithm:
88 * 1. Get the requisition line id of the req line that needs
89 * to be split
90 * 2. Retrieve the quantity on the given line and split using
91 * split function.
92 * 3. Calculate the maximum line number of the lines that
93 * belong to the given requisition.
94 * 4. Using a for loop insert two records into the po_requisition_lines_all
95 * table and provide the correct line number by incrementing
96 * max line number by one in each iteration.
97 * 5. Update the split req line and set the modified flag
98 * and purchasing agent flag.
99 * 6. Copy the attachments from the parent line on to the
100 * new lines.
101 * 7. Handle tax adjustments for the new lines
102 * 8. Handle encumbrance funds results for the new and old
103 * lines.
104 *
105 */
106
107
108
109 PROCEDURE split_requisition_lines(p_api_version IN NUMBER,
110 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
111 p_commit IN VARCHAR2 := FND_API.G_FALSE,
112 x_return_status OUT NOCOPY VARCHAR2,
113 x_msg_count OUT NOCOPY NUMBER,
114 x_msg_data OUT NOCOPY VARCHAR2,
115 p_req_line_id IN NUMBER,
116 p_num_of_new_lines IN NUMBER,
117 p_quantity_tbl IN PO_TBL_NUMBER,
118 p_agent_id IN NUMBER,
119 p_calling_program IN VARCHAR2,
120 p_handle_tax_diff_if_enc IN VARCHAR2,
121 x_new_line_ids_tbl OUT NOCOPY PO_TBL_NUMBER,
122 x_error_msg_tbl OUT NOCOPY PO_TBL_VARCHAR2000
123 ) IS
124
125 l_module CONSTANT VARCHAR2(100) := 'split_requisition_lines';
126 d_module_base CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(d_PACKAGE_BASE,l_module);
127
128 l_api_version CONSTANT NUMBER := 1.0;
129 d_progress NUMBER;
130 l_old_org_id NUMBER;
131 BEGIN
132 --CREATE A SAVE POINT ON ENTERING THIS PROCEDURE
133 SAVEPOINT split_requisition_lines_PVT;
134
135 d_progress := 10;
136
137 IF PO_LOG.d_event THEN
138 PO_LOG.event(d_module_base,d_progress,'Starting Requisition Split ');
139 END IF;
140 --Initialize the error messages table
141 x_error_msg_tbl :=po_tbl_varchar2000();
142 IF NOT FND_API.Compatible_API_Call(l_api_version,
143 p_api_version,
144 l_module,
145 G_PKG_NAME)
146 THEN
147 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
148 END IF;
149
150 --Get the value of current org id. We would set the
151 --org context back to this value before leaving the
152 --program.
153 l_old_org_id := PO_MOAC_UTILS_PVT.get_current_org_id;
154
155 IF PO_LOG.d_stmt THEN
156 PO_LOG.stmt(d_module_base,d_progress,'l_old_org_id ', l_old_org_id);
157 PO_LOG.stmt(d_module_base,d_progress,'Retrieved the value of current orgId');
158 END IF;
159
160 d_progress := 20;
161
162 create_requisition_lines(p_api_version => p_api_version,
163 p_init_msg_list => p_init_msg_list,
164 p_commit => p_commit,
165 x_return_status => x_return_status,
166 x_msg_count => x_msg_count,
167 x_msg_data => x_msg_data,
168 p_req_line_id => p_req_line_id,
169 p_num_of_new_lines => p_num_of_new_lines,
170 p_quantity_tbl => p_quantity_tbl,
171 p_agent_id => p_agent_id,
172 p_calling_program => p_calling_program,
173 x_new_line_ids_tbl => x_new_line_ids_tbl,
174 x_error_msg_tbl => x_error_msg_tbl);
175
176 IF PO_LOG.d_stmt THEN
177 PO_LOG.stmt(d_module_base,d_progress,'x_return_status',x_return_status);
178 END IF;
179
180 IF (x_return_status = FND_API.g_ret_sts_error)
181 THEN
182 RAISE FND_API.g_exc_error;
183 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error)
184 THEN
185 RAISE FND_API.g_exc_unexpected_error;
186 END IF; --x_return_status
187
188 d_progress := 30;
189
190 post_modify_requisition_lines(p_api_version => p_api_version,
191 p_init_msg_list => p_init_msg_list,
192 p_commit => p_commit,
193 x_return_status => x_return_status,
194 x_msg_count => x_msg_count,
195 x_msg_data => x_msg_data,
196 p_req_line_id => p_req_line_id,
197 p_handle_tax_diff_if_enc => p_handle_tax_diff_if_enc,
198 p_new_line_ids_tbl => x_new_line_ids_tbl,
199 x_error_msg_tbl => x_error_msg_tbl);
200
201
202 d_progress:=40;
203
204 IF PO_LOG.d_stmt THEN
205 PO_LOG.stmt(d_module_base,d_progress,'x_return_status',x_return_status);
206 END IF;
207
208 IF (x_return_status = FND_API.g_ret_sts_error)
209 THEN
210 RAISE FND_API.g_exc_error;
211 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error)
212 THEN
213 RAISE FND_API.g_exc_unexpected_error;
214 END IF; --x_return_status
215
216 IF PO_LOG.d_stmt THEN
217 PO_LOG.stmt(d_module_base,d_progress,'Reset the org context to old value');
218 END IF;
219
220 --Set the org context back to the original org context
221 po_moac_utils_pvt.set_org_context(l_old_org_id);
222
223 IF PO_LOG.d_stmt THEN
224 PO_LOG.stmt(d_module_base,d_progress,'l_old_org_id',l_old_org_id);
225 END IF;
226
227 d_progress:=50;
228 x_return_status := FND_API.g_ret_sts_success;
229 IF (PO_LOG.d_proc)
230 THEN
231 PO_LOG.proc_end(d_module_base,'x_return_status', x_return_status);
232 PO_LOG.proc_end(d_module_base,'x_msg_count', x_msg_count );
233 PO_LOG.proc_end(d_module_base,'x_msg_data', x_msg_data );
234 END IF;
235 EXCEPTION
236 WHEN FND_API.g_exc_error THEN
237 ROLLBACK TO split_requisition_lines_PVT;
238 x_return_status := FND_API.g_ret_sts_error;
239 po_moac_utils_pvt.set_org_context(l_old_org_id);
240 IF (PO_LOG.d_exc)
241 THEN
242 PO_LOG.exc(d_module_base,d_progress, SQLCODE || SQLERRM);
243 PO_LOG.proc_end(d_module_base,'x_return_status', x_return_status);
244 PO_LOG.proc_end(d_module_base,'x_msg_count', x_msg_count );
245 PO_LOG.proc_end(d_module_base,'x_msg_data', x_msg_data );
246 END IF;
247 WHEN FND_API.g_exc_unexpected_error THEN
248 ROLLBACK TO split_requisition_lines_PVT;
249 po_moac_utils_pvt.set_org_context(l_old_org_id);
250
251 po_message_s.sql_error(g_pkg_name, l_module, d_progress, SQLCODE, SQLERRM);
252 FND_MSG_PUB.Add;
253 FND_MESSAGE.set_encoded(encoded_message =>FND_MSG_PUB.GET());
254 x_msg_data := FND_MESSAGE.get;
255 x_error_msg_tbl.extend(1);
256 x_error_msg_tbl(x_error_msg_tbl.count) := x_msg_data;
257 x_return_status := FND_API.g_ret_sts_error;
258
259 IF (PO_LOG.d_exc)
260 THEN
261 PO_LOG.exc(d_module_base,d_progress, SQLCODE || SQLERRM);
262 PO_LOG.proc_end(d_module_base,'x_return_status', x_return_status);
263 PO_LOG.proc_end(d_module_base,'x_msg_count', x_msg_count );
264 PO_LOG.proc_end(d_module_base,'x_msg_data', x_msg_data );
265 END IF;
266 WHEN OTHERS THEN
267 ROLLBACK TO split_requisition_lines_PVT;
268 po_moac_utils_pvt.set_org_context(l_old_org_id);
269
270 BEGIN
271 -- Log a debug message, add the error the the API message list.
272 po_message_s.sql_error(g_pkg_name, l_module, d_progress, SQLCODE, SQLERRM);
273 FND_MSG_PUB.Add;
274 FND_MESSAGE.set_encoded(encoded_message =>FND_MSG_PUB.GET());
275 x_msg_data := FND_MESSAGE.get;
276 x_error_msg_tbl.extend(1);
277 x_error_msg_tbl(x_error_msg_tbl.count) := x_msg_data;
278
279 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
280 IF (PO_LOG.d_exc)
281 THEN
282 PO_LOG.exc(d_module_base,d_progress, SQLCODE || SQLERRM);
283 PO_LOG.proc_end(d_module_base,'x_return_status', x_return_status);
284 PO_LOG.proc_end(d_module_base,'x_msg_count', x_msg_count );
285 PO_LOG.proc_end(d_module_base,'x_msg_data', x_msg_data );
286 END IF;
287
288 EXCEPTION
289 WHEN OTHERS THEN
290 po_moac_utils_pvt.set_org_context(l_old_org_id);
291 IF (PO_LOG.d_exc)
292 THEN
293 PO_LOG.exc(d_module_base,d_progress, SQLCODE || SQLERRM);
294 PO_LOG.proc_end(d_module_base,'x_return_status', x_return_status);
295 PO_LOG.proc_end(d_module_base,'x_msg_count', x_msg_count );
296 PO_LOG.proc_end(d_module_base,'x_msg_data', x_msg_data );
297 END IF;
298 RAISE;
299 END;
300 END split_requisition_lines;
301
302 PROCEDURE create_requisition_lines(p_api_version IN NUMBER,
303 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
304 p_commit IN VARCHAR2 := FND_API.G_FALSE,
305 x_return_status OUT NOCOPY VARCHAR2,
306 x_msg_count OUT NOCOPY NUMBER,
307 x_msg_data OUT NOCOPY VARCHAR2,
308 p_req_line_id IN NUMBER,
309 p_num_of_new_lines IN NUMBER,
310 p_quantity_tbl IN PO_TBL_NUMBER,
311 p_agent_id IN NUMBER,
312 p_calling_program IN VARCHAR2,
313 x_new_line_ids_tbl OUT NOCOPY PO_TBL_NUMBER,
314 x_error_msg_tbl OUT NOCOPY PO_TBL_VARCHAR2000
315 ) IS
316
317 l_module CONSTANT VARCHAR2(100) := 'create_requisition_lines';
318 d_module_base CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(d_PACKAGE_BASE, l_module);
319 l_api_version CONSTANT NUMBER := 1.0;
320 d_progress NUMBER;
321 --declare the result tables.
322 new_req_line_id_rslt_tbl PO_TBL_NUMBER;
323 -- SQL What:This cursor Locks the requisition lines the api is going to
324 -- process
325 -- SQL Why :This locking ensures that the records are not touched by
326 -- any other transactions.Opening the cursor keeps the records
327 -- locked till the transaction control happens.
328 CURSOR lock_req_lines_cs IS
329 SELECT prl_1.REQUISITION_LINE_ID,
330 prl_1.ORG_ID,
331 prl_1.REQUISITION_HEADER_ID,
332 prl_1.GROUP_LINE_ID,--add
333 prl_1.CLM_INFO_FLAG,--add
334 PRHA.FEDERAL_FLAG,
335 prl_2.LINE_NUM_DISPLAY
336 FROM po_requisition_lines_all prl_1,
337 po_requisition_lines_all prl_2,
338 Po_Requisition_Headers_All prha
339 WHERE prl_1.requisition_line_id = p_req_line_id
340 AND prha.Requisition_Header_Id = prl_1.Requisition_Header_Id
341 AND prl_2.Requisition_Line_Id = NVL(prl_1.GROUP_LINE_ID,p_req_line_id)
342 FOR UPDATE OF prl_1.quantity NOWAIT;
343
344 l_serial_num NUMBER;
345 l_line_num_index NUMBER;
346 l_max_line_num NUMBER;
347 l_current_org_id NUMBER;
348 l_requisition_line_id NUMBER;
349 l_old_org_id NUMBER;
350 l_new_requisition_line_Id NUMBER; --7835635
351 -- CLM_SPLIT
352 -- l_temp_req_header_id having the req_header_id
353 -- l_next_clin_number having the next clin numbers
354 l_temp_req_header_id NUMBER;
355 l_next_clin_number VARCHAR2(100);
356 l_group_id_temp NUMBER;
357 l_clm_info_flag VARCHAR2(1);
358 l_parent_clin_number VARCHAR2(100);
359 l_number_of_slins NUMBER;
360 l_is_federal_flag VARCHAR2(1);
361 l_num_of_new_lines NUMBER;
362 l_next_slin_number VARCHAR2(100);
363 TYPE l_slin_array_type IS VARRAY(6) OF VARCHAR2(20) ;
364 l_slin_array l_slin_array_type;
365 l_slin_array_tbl Po_Tbl_Number;
366 l_req_line_id_temp NUMBER;
367 l_line_num_index_passed NUMBER;
368 l_display_number VARCHAR2(100);
369 l_quantity NUMBER;
370 l_grp_id NUMBER;
371 l_slin_num_index NUMBER;
372 l_slin_number_temp VARCHAR2(100);
373 l_new_clin_number NUMBER;
374 l_uda_template NUMBER;
375 l_ags_table PO_TBL_VARCHAR30;
376 l_attr_group_name PO_TBL_VARCHAR2000;
377 l_temp_num NUMBER;
378 x_return_status_uda VARCHAR2(100);
379 x_msg_count_uda NUMBER;
380 x_msg_data_uda VARCHAR2(100);
381
382 --end
383 BEGIN
384 --CREATE A SAVE POINT ON ENTERING THIS PROCEDURE
385 SAVEPOINT create_requisition_lines_PVT;
386
387 d_progress := 10;
388
389 IF PO_LOG.d_event THEN
390 PO_LOG.event(d_module_base,d_progress,'Starting Requisition Split ');
391 END IF;
392 --Initialize the error messages table
393 x_error_msg_tbl :=po_tbl_varchar2000();
394 IF NOT FND_API.Compatible_API_Call(l_api_version,
395 p_api_version,
396 l_module,
397 G_PKG_NAME)
398 THEN
399 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
400 END IF;
401
402 IF (PO_LOG.d_proc)
403 THEN
404 PO_LOG.proc_begin(d_module_base);
405 PO_LOG.proc_begin(d_module_base,'p_api_version', p_api_version );
406 PO_LOG.proc_begin(d_module_base,'p_init_msg_list', p_init_msg_list);
407 PO_LOG.proc_begin(d_module_base,'p_commit', p_commit );
408 PO_LOG.proc_begin(d_module_base,'p_req_line_id', p_req_line_id );
409 PO_LOG.proc_begin(d_module_base,'p_quantity_tbl', p_quantity_tbl );
410 PO_LOG.proc_begin(d_module_base,'p_agent_id', p_agent_id );
411 END IF;
412
413 IF PO_LOG.d_stmt THEN
414 PO_LOG.stmt(d_module_base,d_progress,'Attempting to lock the requisition line ');
415 END IF;
416
417 -- Lock the requisition lines the api is going to process
418 -- Retrieve the value of requisition org id so that the org
419 -- context can be set to this value.
420
421 OPEN lock_req_lines_cs;
422
423 FETCH lock_req_lines_cs
424 INTO L_REQUISITION_LINE_ID,
425 L_CURRENT_ORG_ID,
426 l_temp_req_header_id,
427 l_group_id_temp,-- add
428 l_clm_info_flag,--add
429 l_is_federal_flag,
430 l_parent_clin_number;--used only in priced slin
431
432 CLOSE lock_req_lines_cs;
433
434 IF PO_LOG.d_stmt THEN
435 PO_LOG.stmt(d_module_base,d_progress,'Locked the requisition Line Successfully ');
436 END IF;
437
438
439 --***
440 d_progress := 20;
441 --Set the org context to the org id of the parent req line.
442 po_moac_utils_pvt.set_org_context(l_current_org_id);
443
444 IF PO_LOG.d_stmt THEN
445 PO_LOG.stmt(d_module_base,d_progress,'l_current_org_id',l_current_org_id);
446 PO_LOG.stmt(d_module_base,d_progress,'Set the org context to the organization in which Requisition was raised.');
447 END IF;
448
449 d_progress := 30;
450
451 IF PO_LOG.d_stmt THEN
452 PO_LOG.stmt(d_module_base,d_progress,'Calculating value of max line number for the given requisition');
453 END IF;
454 --SQL What:Retrieve the max line number for the given requisition
455 -- to which the requisition line belongs
456 --SQL Why :This is required to calculate the line numbers when creating
457 -- the new requisition lines
458
459 SELECT MAX(prl.line_num)
460 INTO l_max_line_num
461 FROM po_requisition_lines_all prl
462 WHERE prl.requisition_header_id =
463 (SELECT requisition_header_id
464 FROM po_requisition_lines_all
465 WHERE requisition_line_id = p_req_Line_id);
466
467 IF PO_LOG.d_stmt THEN
468 PO_LOG.stmt(d_module_base,d_progress,'l_max_line_num',l_max_line_num);
469 END IF;
470
471 d_progress := 40;
472
473 -- Call the function to split the requisition line
474 x_new_line_ids_tbl := po_tbl_number(p_quantity_tbl.count);
475
476 --***CLM_SPLIT
477 l_new_clin_number:=0;
478 l_slin_array :=l_slin_array_type();
479 --<>L_Uda_Template_Array:= Po_Tbl_Number();
480 L_Ags_Table:= Po_Tbl_Varchar30();
481 L_Attr_Group_Name:= Po_Tbl_Varchar2000();
482 l_temp_num:=1;
483
484 -- Retrieve federalFlag and copy it to l_is_federal_flag
485
486 -- SELECT Federal_Flag
487 -- INTO l_is_federal_flag
488 -- FROM Po_Requisition_Headers_All
489 -- WHERE Requisition_Header_Id=L_Temp_Req_Header_Id;
490
491 --if it is a CLM document
492 IF l_is_federal_flag ='Y' THEN
493
494 -- if its a priced SLIN retrieve the CLINNUM of the corresponding CLIN
495 -- IF l_group_id_temp is not NULL
496 -- then
497 -- SELECT Line_Num_Display
498 -- INTO l_parent_clin_number
499 -- FROM po_requisition_lines_all prl
500 -- WHERE Prl.Requisition_Line_Id = L_Group_Id_Temp;
501 -- END IF;
502
503 -- retrieve the number of SLins associated with Requisition Line
504 SELECT COUNT(*) INTO l_number_of_slins
505 FROM po_requisition_lines_all
506 WHERE Group_Line_Id=L_Requisition_Line_Id AND Requisition_Header_Id=L_Temp_Req_Header_Id;
507
508 -- for Standalone Clins and Priced Slins l_number_of_slins=0
509 -- for Priced CLins with Info Slins l_number_of_slins!=0
510 IF l_number_of_slins=0
511 THEN
512 l_num_of_new_lines:=p_num_of_new_lines;
513 ELSE
514 l_num_of_new_lines:=P_Num_Of_New_Lines*(l_number_of_slins+1);
515 END IF;
516
517 --UDA copy for PRICING shouldnt be done
518 --<>l_uda_template_array.EXTEND;
519 --get the template id of the line and copy it to l_uda_template_array
520 l_uda_template :=GET_UDA_TEMPLATE_ID(p_req_line_id);
521 -- copy the attribute_group_name of PRICING for the given line and template_id to L_ATTR_GROUP_NAME
522 l_attr_group_name.EXTEND;
523 l_attr_group_name(1):=GET_ATTR_GROUP_NAME(p_req_line_id);
524
525 -- if the given line is Priced Clin having INfo Slins
526 IF l_number_of_slins<>0 THEN
527 -- copy the req_line_id's of the info slins associated to the given CLIN
528 SELECT Requisition_Line_Id BULK COLLECT
529 INTO l_slin_array_TBL
530 FROM po_requisition_lines_all
531 WHERE Group_Line_Id=L_Requisition_Line_Id AND requisition_header_id=l_temp_req_header_id;
532
533 -- copy the req_line_id of associated Info SLins into l_slin_array
534 -- copy the attribute_group_name of PRICING for the associated Info Slins and template_id to L_ATTR_GROUP_NAME
535 FOR l_temp_num IN 1..l_number_of_slins
536 LOOP
537 l_slin_array.EXTEND;
538 l_slin_array(l_temp_num):=l_slin_array_TBL(l_temp_num);
539 --l_uda_template_array.EXTEND;
540 --l_uda_template_array(l_temp_num+1):=l_uda_template_array(1);
541 l_attr_group_name.EXTEND;
542 l_attr_group_name(l_temp_num+1):=GET_ATTR_GROUP_NAME(l_slin_array(l_temp_num) );
543 END LOOP;
544 END IF;
545 ELSE-- if its a non-CLM document
546 l_num_of_new_lines:=p_num_of_new_lines;
547 END IF;
548 l_temp_num:=1;
549 --***end******
550
551 FOR l_line_num_index IN 1 .. l_num_of_new_lines
552 LOOP
553 --ascertain the serial number for each req line created
554 --This would be added to max line number to determine the
555 --line number for the line being created.
556
557 IF l_line_num_index = 1
558 THEN
559 l_serial_num := 1;
560 ELSE
561 l_serial_num := l_serial_num + 1;
562 END IF;
563 --x_new_line_ids_tbl(l_line_num_index):=l_serial_num;
564 --Insert appropriate data into requisition lines all
565
566 IF PO_LOG.d_stmt THEN
567 PO_LOG.stmt(d_module_base,d_progress,'Inserting a new row');
568 PO_LOG.stmt(d_module_base,d_progress,'l_serial_num',l_serial_num);
569 END IF;
570
571
572 --7835635
573
574
575 --***CLM_SPLIT ****************
576 -- copy the parent_req_line_id into L_REQUISITION_LINE_ID
577 L_REQUISITION_LINE_ID:=P_REQ_LINE_ID;
578
579 -- if clm document
580 IF l_is_federal_flag ='Y' THEN
581
582 -- if standalone clin or Priced CLin or Priced Slin
583 -- l_new_clin_number was initialised to 0.
584 IF l_line_num_index=1 or l_line_num_index=l_new_clin_number*(l_number_of_slins+1)+1
585 THEN
586
587 IF l_line_num_index = 1
588 THEN
589 -- copy L_REQUISITION_LINE_ID into l_req_line_id_temp and compute the l_next_clin_number
590 l_req_line_id_temp:=l_requisition_line_id;
591 l_next_clin_number := PON_CLO_RENUMBER_PKG.NEXT_CLIN_NUM_WRAPPER(l_temp_req_header_id, 'ICX');
592 l_next_slin_number:=NULL;
593 -- copy the quantity of the new line into l_quantity
594 l_quantity:=p_quantity_tbl(1);
595 ELSE
596 --if l_number_of_slins=0 and if (sum of the quantities in the new lines is equal to the quantity
597 --of the parent line) or if Liquidated
598 --then line_num_index=2 since l_new_clin_number=1.
599 --if l_number_of_slins=0 and sum of the quantities in the new lines is Less than the quantity
600 --in the parent line and if Create new Line was considered then
601 --for l_new_clin_number=1 line_num_index=2 and
602 --for l_new_clin_number=2 line_num_index=3.
603 l_req_line_id_temp:=l_requisition_line_id;
604 l_next_clin_number := PON_CLO_RENUMBER_PKG.INCREMENT_CLIN_NUMBER(l_next_clin_number);
605 l_next_slin_number:=NULL;
606 l_quantity:=p_quantity_tbl(l_new_clin_number+1);
607 END IF;
608 -- copy the l_group_id_temp to l_grp_id and l_next_clin_number to l_display_number.
609 l_grp_id:=l_group_id_temp;
610 l_display_number:=l_next_clin_number;
611
612 -- Priced Slin
613
614 IF l_group_id_temp IS NOT NULL AND l_number_of_slins=0
615 THEN
616 IF l_line_num_index = 1
617 THEN
618 l_req_line_id_temp:=l_requisition_line_id;
619 l_next_slin_number := PON_CLO_RENUMBER_PKG.GETNEXTSLINNUMBER('ICX',l_temp_req_header_id,l_parent_clin_number,l_clm_info_flag );
620 l_quantity:=P_QUANTITY_TBL(1);
621 l_slin_number_temp:=l_next_slin_number;
622 ELSE
623 l_req_line_id_temp:=l_requisition_line_id;
624 l_next_slin_number := PON_CLO_RENUMBER_PKG.INCREMENT_PRICED_SLIN_NUMBER(l_slin_number_temp);
625 l_slin_number_temp:=l_next_slin_number;
626 l_quantity:=P_QUANTITY_TBL(l_new_clin_number+1);
627 END IF;
628 l_display_number:=l_next_slin_number;
629 END IF;
630 --increment l_new_clin_number
631 l_new_clin_number:=l_new_clin_number+1;
632 l_grp_id:=l_group_id_temp;
633
634 ELSE
635 --Info Slins
636 --for the 1st new Clin's associated SLins
637 IF L_Line_Num_Index<(l_number_of_slins+1)+1
638 THEN
639 -- copy the req_line_id of the infoslin into l_req_line_id_temp.
640 l_req_line_id_temp:=l_slin_array(l_line_num_index-1);
641 IF l_line_num_index=2
642 THEN
643
644 l_next_slin_number :=PON_CLO_RENUMBER_PKG.FIRST_INFO_SLIN_NUMBER(l_next_clin_number);
645 -- copy the req_line_id of the clin to the l_grp_id of the Info SLins
646 l_grp_id:=l_new_requisition_line_Id;
647 ELSE
648 l_next_slin_number := PON_CLO_RENUMBER_PKG.INCREMENT_INFO_SLIN_NUMBER(l_next_slin_number);
649 END IF;
650 -- for the 2nd new Clin's associated SLins
651 ELSif l_line_num_index >(l_number_of_slins+1)+1 and l_line_num_index<2*(l_number_of_slins+1)+1 then
652
653 l_req_line_id_temp:=l_slin_array(l_line_num_index-(l_number_of_slins+1)-1);
654 IF l_line_num_index=(l_number_of_slins+1)+2
655 THEN
656
657 l_next_slin_number :=PON_CLO_RENUMBER_PKG.FIRST_INFO_SLIN_NUMBER(l_next_clin_number);
658 l_grp_id:=l_new_requisition_line_Id;
659 ELSE
660 l_next_slin_number := PON_CLO_RENUMBER_PKG.INCREMENT_INFO_SLIN_NUMBER(l_next_slin_number);
661 END IF;
662 -- when create new line is selected
663 ELSif l_line_num_index >2*(l_number_of_slins+1)+1 and l_line_num_index<3*(l_number_of_slins+1)+1 then
664 l_req_line_id_temp:=l_slin_array(l_line_num_index-2*(l_number_of_slins+1)-1);
665 IF l_line_num_index=2*(l_number_of_slins+1)+2
666 THEN
667
668 l_next_slin_number :=PON_CLO_RENUMBER_PKG.FIRST_INFO_SLIN_NUMBER(l_next_clin_number);
669 l_grp_id:=l_new_requisition_line_Id;
670 ELSE
671 l_next_slin_number := PON_CLO_RENUMBER_PKG.INCREMENT_INFO_SLIN_NUMBER(l_next_slin_number);
672 END IF;
673
674 END IF;
675 -- quantity of the info slins is null.
676 l_quantity:=NULL;
677 l_display_number:=l_next_slin_number;
678
679 END IF;
680 ELSE
681 -- Non-Clm
682 l_display_number:=NULL;
683 l_grp_id:=NULL;
684 l_quantity:=P_QUANTITY_TBL(l_new_clin_number+1);
685 l_new_clin_number:=l_new_clin_number+1;
686 l_req_line_id_temp:=P_REQ_LINE_ID;
687 l_number_of_slins:=0;
688 END IF;
689
690 --**End************************
691
692 SELECT po_requisition_lines_s.NEXTVAL into l_new_requisition_line_Id FROM dual;
693 INSERT INTO po_requisition_lines_all
694 (requisition_line_id,
695 requisition_header_id,
696 line_num,
697 line_type_id,
698 category_id,
699 item_description,
700 unit_meas_lookup_code,
701 unit_price,
702 quantity,
703 deliver_to_location_id,
704 to_person_id,
705 last_update_date,
706 last_updated_by,
707 source_type_code,
708 last_update_login,
709 creation_date,
710 created_by,
711 item_id,
712 item_revision,
713 quantity_delivered,
714 suggested_buyer_id,
715 encumbered_flag,
716 rfq_required_flag,
717 need_by_date,
718 line_location_id,
719 modified_by_agent_flag,
720 parent_req_line_id,
721 justification,
722 note_to_agent,
723 note_to_receiver,
724 purchasing_agent_id,
725 document_type_code,
726 blanket_po_header_id,
727 blanket_po_line_num,
728 currency_code,
729 rate_type,
730 rate_date,
731 rate,
732 currency_unit_price,
733 suggested_vendor_name,
734 suggested_vendor_location,
735 suggested_vendor_contact,
736 suggested_vendor_phone,
737 suggested_vendor_product_code,
738 un_number_id,
739 hazard_class_id,
740 must_use_sugg_vendor_flag,
741 reference_num,
742 on_rfq_flag,
743 urgent_flag,
744 cancel_flag,
745 source_organization_id,
746 source_subinventory,
747 destination_type_code,
748 destination_organization_id,
749 destination_subinventory,
750 quantity_cancelled,
751 cancel_date,
752 cancel_reason,
753 closed_code,
754 agent_return_note,
755 changed_after_research_flag,
756 vendor_id,
757 vendor_site_id,
758 vendor_contact_id,
759 research_agent_id,
760 on_line_flag,
761 wip_entity_id,
762 wip_line_id,
763 wip_repetitive_schedule_id,
764 wip_operation_seq_num,
765 wip_resource_seq_num,
766 attribute_category,
767 destination_context,
768 inventory_source_context,
769 vendor_source_context,
770 attribute1,
771 attribute2,
772 attribute3,
773 attribute4,
774 attribute5,
775 attribute6,
776 attribute7,
777 attribute8,
778 attribute9,
779 attribute10,
780 attribute11,
781 attribute12,
782 attribute13,
783 attribute14,
784 attribute15,
785 bom_resource_id,
786 ussgl_transaction_code,
787 government_context,
788 closed_reason,
789 closed_date,
790 transaction_reason_code,
791 quantity_received,
792 tax_code_id,
793 tax_user_override_flag,
794 oke_contract_header_id,
795 oke_contract_version_id,
796 secondary_unit_of_measure,
797 secondary_quantity,
798 preferred_grade,
799 secondary_quantity_received,
800 secondary_quantity_cancelled,
801 auction_header_id,
802 auction_display_number,
803 auction_line_number,
804 reqs_in_pool_flag,
805 vmi_flag,
806 bid_number,
807 bid_line_number,
808 order_type_lookup_code,
809 purchase_basis,
810 matching_basis,
811 org_id,
812 catalog_type,
813 catalog_source,
814 item_source_id, --Added for bug 9092341
815 manufacturer_id,
816 manufacturer_name,
817 manufacturer_part_number,
818 requester_email,
819 requester_fax,
820 requester_phone,
821 unspsc_code,
822 other_category_code,
823 supplier_duns,
824 tax_status_indicator,
825 pcard_flag,
826 new_supplier_flag,
827 AUTO_RECEIVE_FLAG,
828 TAX_ATTRIBUTE_UPDATE_CODE,
829 LINE_NUM_DISPLAY,--start addition for split_clm
830 CONFORMED_LINE_ID,
831 AMENDMENT_TYPE,
832 AMENDMENT_STATUS,
833 GROUP_LINE_ID,
834 CLM_INFO_FLAG,
835 CLM_OPTION_INDICATOR,
836 CLM_BASE_LINE_NUM,
837 CLM_OPTION_NUM,
838 CLM_OPTION_FROM_DATE,
839 CLM_OPTION_TO_DATE,
840 CLM_FUNDED_FLAG,
841 UDA_TEMPLATE_ID,
842 CONTRACT_TYPE,
843 COST_CONSTRAINT,
844 AMENDMENT_RESPONSE_REASON,
845 CLM_PERIOD_PERF_START_DATE,
846 CLM_PERIOD_PERF_END_DATE,
847 CLM_EXTENDED_ITEM_DESCRIPTION,
848 CLM_OPTION_EXERCISED,
849 ASSIGNMENT_NUMBER,
850 PO_DRAFT_ID--end addition
851 )
852 (SELECT l_new_requisition_line_Id,
853 prl.requisition_header_id,
854 (l_serial_num + l_max_line_num),
855 prl.line_type_id,
856 prl.category_id,
857 prl.item_description,
858 prl.unit_meas_lookup_code,
859 prl.unit_price,
860 l_quantity,--clm_split
861 prl.deliver_to_location_id,
862 prl.to_person_id,
863 SYSDATE, -- Modified for bug 9092341
864 FND_GLOBAL.USER_ID,
865 prl.source_type_code,
866 prl.last_update_login,
867 SYSDATE, -- Modified for bug 9092341
868 FND_GLOBAL.USER_ID,
869 prl.item_id,
870 prl.item_revision,
871 prl.quantity_delivered,
872 prl.suggested_buyer_id,
873 prl.encumbered_flag,
874 prl.rfq_required_flag,
875 prl.need_by_date,
876 prl.line_location_id,
877 NULL,
878 p_req_line_id,
879 prl.justification,
880 prl.note_to_agent,
881 prl.note_to_receiver,
882 prl.purchasing_agent_id,
883 prl.document_type_code,
884 prl.blanket_po_header_id,
885 prl.blanket_po_line_num,
886 prl.currency_code,
887 prl.rate_type,
888 prl.rate_date,
889 prl.rate,
890 prl.currency_unit_price,
891 prl.suggested_vendor_name,
892 prl.suggested_vendor_location,
893 prl.suggested_vendor_contact,
894 prl.suggested_vendor_phone,
895 prl.suggested_vendor_product_code,
896 decode(p_calling_program,g_CALLING_PROGRAM_SPLIT,
897 prl.un_number_id,null),
898 prl.hazard_class_id,
899 prl.must_use_sugg_vendor_flag,
900 prl.reference_num,
901 prl.on_rfq_flag,
902 prl.urgent_flag,
903 prl.cancel_flag,
904 prl.source_organization_id,
905 prl.source_subinventory,
906 prl.destination_type_code,
907 prl.destination_organization_id,
908 prl.destination_subinventory,
909 prl.quantity_cancelled,
910 prl.cancel_date,
911 prl.cancel_reason,
912 prl.closed_code,
913 prl.agent_return_note,
914 prl.changed_after_research_flag,
915 prl.vendor_id,
916 prl.vendor_site_id,
917 prl.vendor_contact_id,
918 prl.research_agent_id,
919 prl.on_line_flag,
920 prl.wip_entity_id,
921 prl.wip_line_id,
922 prl.wip_repetitive_schedule_id,
923 prl.wip_operation_seq_num,
924 prl.wip_resource_seq_num,
925 prl.attribute_category,
926 prl.destination_context,
927 prl.inventory_source_context,
928 prl.vendor_source_context,
929 prl.attribute1,
930 prl.attribute2,
931 prl.attribute3,
932 prl.attribute4,
933 prl.attribute5,
934 prl.attribute6,
935 prl.attribute7,
936 prl.attribute8,
937 prl.attribute9,
938 prl.attribute10,
939 prl.attribute11,
940 prl.attribute12,
941 prl.attribute13,
942 prl.attribute14,
943 prl.attribute15,
944 prl.bom_resource_id,
945 prl.ussgl_transaction_code,
946 prl.government_context,
947 prl.closed_reason,
948 prl.closed_date,
949 prl.transaction_reason_code,
950 prl.quantity_received,
951 prl.tax_code_id,
952 prl.tax_user_override_flag,
953 prl.oke_contract_header_id,
954 prl.oke_contract_version_id,
955 decode(p_calling_program,g_CALLING_PROGRAM_SPLIT,
956 prl.secondary_unit_of_measure,null),
957 prl.secondary_quantity,
958 prl.preferred_grade,
959 prl.secondary_quantity_received,
960 prl.secondary_quantity_cancelled,
961 prl.auction_header_id,
962 prl.auction_display_number,
963 prl.auction_line_number,
964 'Y', --new reqs are placed back in pool after splitting
965 prl.vmi_flag,
966 prl.bid_number,
967 prl.bid_line_number,
968 prl.order_type_lookup_code,
969 prl.purchase_basis,
970 prl.matching_basis,
971 prl.org_id,
972 prl.catalog_type,
973 prl.catalog_source,
974 prl.item_source_id, -- Added for bug 9092341
975 prl.manufacturer_id,
976 prl.manufacturer_name,
977 prl.manufacturer_part_number,
978 prl.requester_email,
979 prl.requester_fax,
980 prl.requester_phone,
981 prl.unspsc_code,
982 prl.other_category_code,
983 prl.supplier_duns,
984 prl.tax_status_indicator,
985 prl.pcard_flag,
986 prl.new_supplier_flag,
987 PRL.AUTO_RECEIVE_FLAG,
988 G_TAX_ATTRIBUTE_CREATE,
989 l_display_number,-- added for clm_split
990 PRL.CONFORMED_LINE_ID,
991 PRL.AMENDMENT_TYPE,
992 PRL.AMENDMENT_STATUS,
993 l_grp_id,-- clm_split
994 PRL.CLM_INFO_FLAG,
995 PRL.CLM_OPTION_INDICATOR,
996 PRL.CLM_BASE_LINE_NUM,
997 PRL.CLM_OPTION_NUM,
998 PRL.CLM_OPTION_FROM_DATE,
999 PRL.CLM_OPTION_TO_DATE,
1000 PRL.CLM_FUNDED_FLAG,
1001 PRL.UDA_TEMPLATE_ID,
1002 PRL.CONTRACT_TYPE,
1003 PRL.COST_CONSTRAINT,
1004 PRL.AMENDMENT_RESPONSE_REASON,
1005 PRL.CLM_PERIOD_PERF_START_DATE,
1006 PRL.CLM_PERIOD_PERF_END_DATE,
1007 PRL.CLM_EXTENDED_ITEM_DESCRIPTION,
1008 PRL.CLM_OPTION_EXERCISED,
1009 PRL.ASSIGNMENT_NUMBER,
1010 PRL.PO_DRAFT_ID -- end of addition for clm_split
1011
1012 FROM po_requisition_lines_all prl
1013 WHERE prl.requisition_line_id = l_req_line_id_temp);
1014
1015 --7835635
1016
1017
1018
1019 INSERT INTO por_item_attribute_values(
1020 item_type,
1021 requisition_header_id,
1022 requisition_line_id,
1023 attribute1,
1024 attribute2,
1025 attribute3,
1026 attribute4,
1027 attribute5,
1028 attribute6,
1029 attribute7,
1030 attribute8,
1031 attribute9,
1032 attribute10,
1033 attribute11,
1034 attribute12,
1035 attribute13,
1036 attribute14,
1037 attribute15,
1038 org_id,
1039 created_by,
1040 creation_date,
1041 last_updated_by,
1042 last_update_date,
1043 last_update_login)
1044 SELECT piav.item_type,
1045 piav.requisition_header_id,
1046 l_new_requisition_line_Id ,
1047 piav.attribute1,
1048 piav.attribute2,
1049 piav.attribute3,
1050 piav.attribute4,
1051 piav.attribute5,
1052 piav.attribute6,
1053 piav.attribute7,
1054 piav.attribute8,
1055 piav.attribute9,
1056 piav.attribute10,
1057 piav.attribute11,
1058 piav.attribute12,
1059 piav.attribute13,
1060 piav.attribute14,
1061 piav.attribute15,
1062 piav.org_id,
1063 piav.created_by,
1064 piav.creation_date,
1065 piav.last_updated_by,
1066 piav.last_update_date,
1067 piav.last_update_login
1068 FROM por_item_attribute_values piav
1069 WHERE piav.requisition_line_id = l_req_line_id_temp;
1070 --7835635
1071
1072 --<CLM-SPLIT> uda copy for clm document.
1073 IF l_is_federal_flag ='Y' THEN
1074 -- temp_num was intialised to 1.
1075 l_ags_table := po_tbl_varchar30(l_attr_group_name(l_temp_num));
1076 PO_UDA_DATA_UTIL.COPY_USER_ATTRS(
1077 x_template_id => l_uda_template,
1078 x_from_pk_col_value_pairs => ego_col_name_value_pair_array
1079 (ego_col_name_value_pair_obj( 'REQUISITION_LINE_ID' , l_req_line_id_temp)),
1080 x_to_pk_col_value_pairs => ego_col_name_value_pair_array
1081 (ego_col_name_value_pair_obj( 'REQUISITION_LINE_ID' ,l_new_requisition_line_Id )),
1082 x_copy_attribute_groups => 'EXCLUSIVE',
1083 x_attribute_group_table => l_ags_table,
1084 x_return_status =>x_return_status_uda,
1085 x_msg_count =>x_msg_count_uda,
1086 x_msg_data =>x_msg_data_uda
1087 );
1088 -- if temp_num=1 then it corresponds to the CLIN
1089 -- if temp_num=2 it correponds to the 1st Info Slin
1090 -- IF temp_num>(l_number_of_slins) temp_num is 1 and the cycle is repeated
1091 --this is done to ensure the uda attribute of CLIN is copied to the corresponding CLin
1092 -- uda attribute of INFO SLIN is copied to the corresponding SLins
1093 IF l_temp_num>(l_number_of_slins)
1094 THEN
1095 l_temp_num:=1;
1096 ELSE
1097 l_temp_num:=l_temp_num+1;
1098 END IF;
1099 end if;
1100
1101 END LOOP;
1102
1103 d_progress := 50;
1104
1105 IF PO_LOG.d_stmt THEN
1106 PO_LOG.stmt(d_module_base,d_progress,'Updating the old requisition line to mark it as modified and to push it out of pool');
1107 END IF;
1108
1109 -- SQL What:Mark all the parent requisition lines which are split
1110 -- with modified_by_agent_flag setting 'Y'.
1111 -- SQL Why :This indicates that this requisition lines have been
1112 -- modified by the buyer and no longer available for any
1113 -- operations.
1114 -- Also implemented the following rules for Catalog Integration.
1115
1116 -- If the original requisition line has a bid and/or negotiation
1117 -- reference, the bid and/or negotiation reference from the
1118 -- original requisition line should be dropped.
1119
1120 -- If the original requisition line has a value for On RFQ flag
1121 -- the On RFQ flag from the original requisition line should be
1122 -- dropped.
1123
1124 -- add P_REQ_LINE_ID to l_slin_array
1125 l_slin_array.EXTEND;
1126 l_slin_array(l_number_of_slins+1):=p_req_line_id;
1127
1128 -- update po_requisition_lines_all for the associated Line.. andassociated Info Slins if any.
1129 FORALL l_slin_num_index IN 1 .. l_number_of_slins+1
1130 UPDATE po_requisition_lines_all
1131 SET modified_by_agent_flag = 'Y',
1132 purchasing_agent_id = p_agent_id,
1133 reqs_in_pool_flag = NULL, --<REQINPOOL>
1134 on_rfq_flag = decode(p_calling_program,
1135 g_CALLING_PROGRAM_CATALOG,
1136 null,
1137 on_rfq_flag),
1138 bid_number = decode(p_calling_program,
1139 g_calling_program_catalog,
1140 null,
1141 bid_number),
1142 bid_line_number = decode(p_calling_program,
1143 g_calling_program_catalog,
1144 null,
1145 bid_line_number),
1146 auction_header_id = decode(p_calling_program,
1147 g_calling_program_catalog,
1148 null,
1149 auction_header_id),
1150 auction_display_number = decode(p_calling_program,
1151 g_calling_program_catalog,
1152 null,
1153 auction_display_number),
1154 auction_line_number = decode(p_calling_program,
1155 g_calling_program_catalog,
1156 null,
1157 auction_line_number),
1158 last_update_date = SYSDATE,
1159 last_updated_by = FND_GLOBAL.USER_ID,
1160 last_update_login = FND_GLOBAL.LOGIN_ID
1161 WHERE requisition_line_id = l_slin_array(l_slin_num_index);--p_req_line_id
1162
1163 --Collect all req line id's in a new table.
1164 --get the ids of the new lines created. This would be returned back as
1165 --an out parameter
1166 --Performance fix for bug 4930487
1167 SELECT PRL1.requisition_line_id
1168 BULK COLLECT
1169 INTO new_req_line_id_rslt_tbl
1170 FROM PO_REQUISITION_LINES_ALL PRL1, PO_REQUISITION_LINES_ALL PRL2
1171 WHERE PRL1.requisition_header_id = PRL2.requisition_header_id
1172 AND PRL1.parent_req_line_id = p_req_line_id
1173 AND PRL2.requisition_line_id = p_req_line_id;
1174
1175 IF PO_LOG.d_stmt THEN
1176 FOR i in 1..new_req_line_id_rslt_tbl.count LOOP
1177 PO_LOG.stmt(d_module_base,d_progress,'new_req_line_id_rslt_tbl('||i||')',new_req_line_id_rslt_tbl(i));
1178 END LOOP;
1179 END IF;
1180 x_new_line_ids_tbl := new_req_line_id_rslt_tbl;
1181
1182 d_progress := 60;
1183
1184 IF FND_API.To_Boolean(p_commit)
1185 THEN
1186 IF PO_LOG.d_event THEN
1187 PO_LOG.event(d_module_base,d_progress,'Commiting work');
1188 END IF;
1189 COMMIT WORK;
1190 END IF; --FND_API
1191
1192 d_progress := 70;
1193 x_return_status := FND_API.g_ret_sts_success;
1194
1195 IF PO_LOG.d_stmt THEN
1196 PO_LOG.stmt(d_module_base,d_progress,'l_old_org_id',l_old_org_id);
1197 END IF;
1198
1199 IF (PO_LOG.d_proc)
1200 THEN
1201 PO_LOG.proc_end(d_module_base,'x_return_status', x_return_status);
1202 PO_LOG.proc_end(d_module_base,'x_msg_count', x_msg_count );
1203 PO_LOG.proc_end(d_module_base,'x_msg_data', x_msg_data );
1204 END IF;
1205 EXCEPTION
1206 WHEN OTHERS THEN
1207 ROLLBACK TO create_requisition_lines_PVT;
1208 BEGIN
1209 -- Log a debug message, add the error the the API message list.
1210 po_message_s.sql_error(g_pkg_name, l_module, d_progress, SQLCODE, SQLERRM);
1211 FND_MSG_PUB.Add;
1212 FND_MESSAGE.set_encoded(encoded_message =>FND_MSG_PUB.GET());
1213 x_msg_data := FND_MESSAGE.get;
1214 x_error_msg_tbl.extend(1);
1215 x_error_msg_tbl(x_error_msg_tbl.count) := x_msg_data;
1216 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1217 IF (PO_LOG.d_exc)
1218 THEN
1219 PO_LOG.exc(d_module_base,d_progress, SQLCODE || SQLERRM);
1220 PO_LOG.proc_end(d_module_base,'x_return_status', x_return_status);
1221 PO_LOG.proc_end(d_module_base,'x_msg_count', x_msg_count );
1222 PO_LOG.proc_end(d_module_base,'x_msg_data', x_msg_data );
1223 END IF;
1224
1225 EXCEPTION
1226 WHEN OTHERS THEN
1227 po_moac_utils_pvt.set_org_context(l_old_org_id);
1228 IF (PO_LOG.d_exc)
1229 THEN
1230 PO_LOG.exc(d_module_base,d_progress, SQLCODE || SQLERRM);
1231 PO_LOG.proc_end(d_module_base,'x_return_status', x_return_status);
1232 PO_LOG.proc_end(d_module_base,'x_msg_count', x_msg_count );
1233 PO_LOG.proc_end(d_module_base,'x_msg_data', x_msg_data );
1234 END IF;
1235 RAISE;
1236 END;
1237 END create_requisition_lines;
1238
1239 -------------------------------------------------------------------------------
1240
1241 PROCEDURE post_modify_requisition_lines(p_api_version IN NUMBER,
1242 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1243 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1244 x_return_status OUT NOCOPY VARCHAR2,
1245 x_msg_count OUT NOCOPY NUMBER,
1246 x_msg_data OUT NOCOPY VARCHAR2,
1247 p_req_line_id IN NUMBER,
1248 p_handle_tax_diff_if_enc IN VARCHAR2,
1249 p_new_line_ids_tbl IN PO_TBL_NUMBER,
1250 x_error_msg_tbl OUT NOCOPY PO_TBL_VARCHAR2000) IS
1251
1252 l_module CONSTANT VARCHAR2(100) := 'post_modify_requisition_lines';
1253 d_module_base CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(d_PACKAGE_BASE, l_module);
1254 l_api_version CONSTANT NUMBER := 1.0;
1255 d_progress NUMBER;
1256 --declare the result tables.
1257 l_return_status VARCHAR2(1);
1258 l_req_encumbrance_flag financials_system_parameters.req_encumbrance_flag%TYPE;
1259 l_online_report_id PO_ONLINE_REPORT_TEXT.online_report_id%TYPE;
1260 l_success BOOLEAN;
1261 l_requisition_header_id NUMBER;
1262 l_req_encumbered_flag VARCHAR2(5);
1263 l_quantity_table PO_TBL_NUMBER;
1264 l_tax_message FND_NEW_MESSAGES.message_text%type := NULL;
1265 l_message_text FND_NEW_MESSAGES.message_text%type := NULL;
1266 l_Req_Header_Id NUMBER; /* <<CLM Partial Funding Code Changes>> */
1267 BEGIN
1268 --CREATE A SAVE POINT ON ENTERING THIS PROCEDURE
1269 SAVEPOINT post_requisition_lines_PVT;
1270
1271 d_progress := 10;
1272
1273 IF PO_LOG.d_event THEN
1274 PO_LOG.event(d_module_base,d_progress,'Post Modify Requisition ');
1275 END IF;
1276
1277 --Initialize the error messages table
1278 x_error_msg_tbl :=po_tbl_varchar2000();
1279
1280
1281 IF NOT FND_API.Compatible_API_Call(l_api_version,
1282 p_api_version,
1283 l_module,
1284 G_PKG_NAME)
1285 THEN
1286 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1287 END IF;
1288
1289 IF (PO_LOG.d_proc)
1290 THEN
1291 PO_LOG.proc_begin(d_module_base);
1292 PO_LOG.proc_begin(d_module_base,'p_api_version', p_api_version );
1293 PO_LOG.proc_begin(d_module_base,'p_init_msg_list', p_init_msg_list);
1294 PO_LOG.proc_begin(d_module_base,'p_commit', p_commit );
1295 PO_LOG.proc_begin(d_module_base,'p_req_line_id', p_req_line_id );
1296 END IF;
1297
1298 --update the supply for existing requisition line and create
1299 --supply for the new lines
1300
1301 d_progress := 20;
1302
1303 IF PO_LOG.d_stmt THEN
1304 PO_LOG.stmt(d_module_base,d_progress,'Updating the requisition supply');
1305 END IF;
1306
1307 l_success := po_supply.po_req_supply(NULL,
1308 p_req_line_id,
1309 NULL,
1310 g_EXPLODE_REQ_ACTION,
1311 NULL,
1312 NULL,
1313 NULL);
1314
1315 --copy the attachments from the parent line onto the new req
1316 --lines created. Also create the distributions for each of the new
1317 --lines
1318
1319 d_progress := 40;
1320
1321 -- Performance fix for bug 4930487
1322 SELECT PRL1.quantity
1323 BULK COLLECT
1324 INTO l_quantity_table
1325 FROM po_requisition_lines_all PRL1, po_requisition_lines_all PRL2
1326 WHERE PRL1.requisition_header_id = PRL2.requisition_header_id
1327 AND PRL1.parent_req_line_id = p_req_line_id
1328 AND PRL2.requisition_line_id = p_req_line_id;
1329
1330 FOR l_req_line_index IN 1 .. P_new_line_ids_tbl.COUNT
1331 LOOP
1332 IF PO_LOG.d_stmt THEN
1333 PO_LOG.stmt(d_module_base,d_progress,'l_req_line_index',l_req_line_index);
1334 PO_LOG.stmt(d_module_base,d_progress,'Coping attachments from old line to newly created lines');
1335 END IF;
1336
1337 fnd_attached_documents2_pkg.copy_attachments(
1338 X_from_entity_name => g_REQ_LINES_ENTITY,
1339 X_from_pk1_value => p_req_line_id,
1340 X_from_pk2_value => NULL,
1341 X_from_pk3_value => NULL,
1342 X_from_pk4_value => NULL,
1343 X_from_pk5_value => NULL,
1344 X_to_entity_name => g_REQ_LINES_ENTITY,
1345 X_to_pk1_value => p_new_line_ids_tbl(l_req_line_index),
1346 X_to_pk2_value => NULL,
1347 X_to_pk3_value => NULL,
1348 X_to_pk4_value => NULL,
1349 X_to_pk5_value => NULL,
1350 X_created_by => NULL,
1351 X_last_update_login => NULL,
1352 X_program_application_id => NULL,
1353 X_program_id => NULL,
1354 X_request_id => NULL,
1355 X_automatically_added_flag => NULL);
1356
1357 IF PO_LOG.d_stmt THEN
1358 PO_LOG.stmt(d_module_base,d_progress,'Creating a new distribution for ',p_new_line_ids_tbl(l_req_line_index));
1359 END IF;
1360 d_progress := 50;
1361 --Create req distributions for the newly created line
1362 PO_REQ_DIST_SV.create_dist_for_modify(
1363 p_new_line_ids_tbl(l_req_line_index),
1364 p_req_line_id,
1365 l_quantity_table(l_req_line_index));
1366
1367 IF PO_LOG.d_stmt THEN
1368 PO_LOG.stmt(d_module_base,d_progress,'Succesfully created a new distribution for',p_new_line_ids_tbl(l_req_line_index));
1369 END IF;
1370 END LOOP;
1371
1372 d_progress := 60;
1373 --Retrieve the value of l_requisition_header_id first
1374 select requisition_header_id
1375 into l_requisition_header_id
1376 from po_requisition_lines_all
1377 where requisition_line_id = p_req_line_id;
1378
1379 IF PO_LOG.d_stmt THEN
1380 PO_LOG.stmt(d_module_base,d_progress,'l_requisition_header_id',l_requisition_header_id);
1381 END IF;
1382 --Calculate the tax for the entire document again. The recoverable and non recoverable
1383 --tax fields need to be updated appropriately.
1384 PO_TAX_INTERFACE_PVT.calculate_tax_requisition(
1385 p_requisition_header_id => l_requisition_header_id,
1386 p_calling_program => g_CALLING_PROGRAM_SPLIT,
1387 x_return_status => l_return_status);
1388
1389 --<Bug#4765982 Start>
1390 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1391 l_tax_message := FND_MESSAGE.get_string('PO','PO_TAX_CALCULATION')||' : ' ;
1392
1393 FOR i IN 1..po_tax_interface_pvt.G_TAX_ERRORS_TBL.MESSAGE_TEXT.COUNT LOOP
1394 l_message_text := l_tax_message || po_tax_interface_pvt.G_TAX_ERRORS_TBL.message_text(i);
1395 FND_MESSAGE.set_name('PO','PO_CUSTOM_MSG');
1396 FND_MESSAGE.set_token('TRANSLATED_TOKEN',l_message_text);
1397 FND_MSG_PUB.Add;
1398 END LOOP;
1399
1400 RAISE FND_API.G_EXC_ERROR;
1401
1402 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1403 FND_MESSAGE.set_name('PO','PO_PDOI_TAX_CALCULATION_ERR');
1404 FND_MSG_PUB.Add;
1405 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1406 END IF;--l_return_status
1407 --<Bug#4765982 End>
1408
1409
1410 --Check if the current org has req encumbrance enabled.
1411 --If it is enabled then handle funds reversal for the parent
1412 --line and encumber the newly created line
1413 d_progress := 70;
1414 IF PO_LOG.d_stmt THEN
1415 PO_LOG.stmt(d_module_base,d_progress,'Checking if requisition encumbrance is switched on');
1416 END IF;
1417
1418 SELECT nvl(req_encumbrance_flag, 'N')
1419 INTO l_req_encumbrance_flag
1420 FROM financials_system_parameters;
1421
1422 IF PO_LOG.d_stmt THEN
1423 PO_LOG.stmt(d_module_base,d_progress,'l_req_encumbrance_flag',l_req_encumbrance_flag);
1424 END IF;
1425
1426 --Select all the distribution lines which are to be reserved and
1427 --unreserved into a plsql table
1428 IF l_req_encumbrance_flag = 'Y'
1429 THEN
1430 begin
1431 SELECT ENCUMBERED_FLAG,
1432 REQUISITION_HEADER_ID /* <<CLM Partial Funding Code Changes>> */
1433 INTO l_req_encumbered_flag,
1434 l_Req_Header_Id /* <<CLM Partial Funding Code Changes>> */
1435 FROM PO_REQUISITION_LINES_ALL
1436 WHERE requisition_line_id = p_req_line_id;
1437 exception
1438 when others then
1439 l_req_encumbered_flag :='N';
1440 end;
1441
1442 if(l_req_encumbered_flag = 'Y')THEN
1443 /* <<CLM Partial Funding Code Changes>> */
1444 d_progress := 75;
1445 IF PO_LOG.d_stmt THEN
1446 PO_LOG.stmt(d_module_base,d_progress,'CLM Calculations');
1447 END IF;
1448 IF Po_Partial_Funding_Pkg.is_clm_document(p_doc_type =>'REQ',
1449 p_doc_level_id => l_Req_Header_Id) = 'Y' THEN
1450 -- Call an api to update funded value and other details on Req Distributions
1451 FOR I IN 1 .. P_new_line_ids_tbl.COUNT
1452 LOOP
1453 po_partial_funding_pkg.Updt_Funds_Split(x_return_status => l_return_status,
1454 p_new_Req_line_id => P_new_line_ids_tbl(i));
1455 IF PO_LOG.d_stmt THEN
1456 PO_LOG.stmt(d_module_base,d_progress,'l_return_status'||l_return_status);
1457 END IF;
1458
1459 IF (l_return_status = FND_API.g_ret_sts_error)
1460 THEN
1461 RAISE FND_API.g_exc_error;
1462 ELSIF (l_return_status = FND_API.g_ret_sts_unexp_error)
1463 THEN
1464 RAISE FND_API.g_exc_unexpected_error;
1465 END IF; --l_return_status
1466
1467 END LOOP;
1468 END IF;
1469
1470 /* <<CLM Partial Funding Code Changes>> */
1471 d_progress := 80;
1472 IF PO_LOG.d_stmt THEN
1473 PO_LOG.stmt(d_module_base,d_progress,'Calling funds reversal');
1474 END IF;
1475 --Pass the distribution ids of the newly created lines to the
1476 --encumbrance api to reserve funds
1477 call_funds_reversal(p_api_version => 1.0,
1478 p_commit => p_commit,
1479 x_return_status => l_return_status,
1480 x_msg_count => x_msg_count,
1481 x_msg_data => x_msg_data,
1482 p_req_line_id => p_req_line_id,
1483 p_handle_tax_flag => p_handle_tax_diff_if_enc,
1484 x_online_report_id => l_online_report_id);
1485
1486 IF PO_LOG.d_stmt THEN
1487 PO_LOG.stmt(d_module_base,d_progress,'l_return_status'||l_return_status);
1488 END IF;
1489
1490 IF (l_return_status = FND_API.g_ret_sts_error)
1491 THEN
1492 RAISE FND_API.g_exc_error;
1493 ELSIF (l_return_status = FND_API.g_ret_sts_unexp_error)
1494 THEN
1495 RAISE FND_API.g_exc_unexpected_error;
1496 END IF; --l_return_status
1497 END IF;
1498 END IF; --l_req_encumbrance_flag
1499
1500 d_progress := 90;
1501
1502 IF FND_API.To_Boolean(p_commit)
1503 THEN
1504 IF PO_LOG.d_event THEN
1505 PO_LOG.event(d_module_base,d_progress,'Commiting work');
1506 END IF;
1507 COMMIT WORK;
1508 END IF; --FND_API
1509
1510 x_return_status := FND_API.g_ret_sts_success;
1511
1512 IF (PO_LOG.d_proc)
1513 THEN
1514 PO_LOG.proc_end(d_module_base,'x_return_status', x_return_status);
1515 PO_LOG.proc_end(d_module_base,'x_msg_count', x_msg_count );
1516 PO_LOG.proc_end(d_module_base,'x_msg_data', x_msg_data );
1517 END IF;
1518 EXCEPTION
1519 WHEN FND_API.g_exc_unexpected_error THEN
1520 ROLLBACK TO post_requisition_lines_PVT;
1521 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1522 p_data => x_msg_data);
1523 IF(x_msg_count>0)THEN
1524 NULL;
1525 ELSE
1526 po_message_s.sql_error(g_pkg_name, l_module, d_progress, SQLCODE, SQLERRM);
1527 FND_MSG_PUB.Add;
1528 END IF;
1529
1530 FND_MESSAGE.set_encoded(encoded_message =>FND_MSG_PUB.GET());
1531 x_msg_data := FND_MESSAGE.get;
1532 x_error_msg_tbl.extend(1);
1533 x_error_msg_tbl(x_error_msg_tbl.COUNT) := x_msg_data;
1534
1535 x_return_status := FND_API.g_ret_sts_unexp_error;
1536
1537 IF (PO_LOG.d_exc)
1538 THEN
1539 PO_LOG.exc(d_module_base,d_progress, SQLCODE || SQLERRM);
1540 PO_LOG.proc_end(d_module_base,'x_return_status', x_return_status);
1541 PO_LOG.proc_end(d_module_base,'x_msg_count', x_msg_count );
1542 PO_LOG.proc_end(d_module_base,'x_msg_data', x_msg_data );
1543 END IF;
1544 WHEN FND_API.g_exc_error THEN
1545 ROLLBACK TO post_requisition_lines_PVT;
1546
1547 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1548 p_data => x_msg_data);
1549
1550 IF l_online_report_id IS NOT NULL THEN
1551 PO_Document_Control_PVT.add_online_report_msgs
1552 (
1553 p_api_version => 1.0
1554 ,p_init_msg_list => FND_API.G_FALSE
1555 ,x_return_status => x_return_status
1556 ,p_online_report_id => l_online_report_id);
1557 END IF;
1558
1559 FOR i IN 1..FND_MSG_PUB.count_msg loop
1560 FND_MESSAGE.set_encoded(encoded_message =>
1561 FND_MSG_PUB.get(p_msg_index => i));
1562 x_error_msg_tbl.extend(1);
1563 x_error_msg_tbl(i) := FND_MESSAGE.get;
1564 END LOOP;
1565 --<bug#5523323 START>
1566 --Set the return status at the end so that the call to add
1567 --online report msgs onto the stack doesn't override the value set
1568 --by the exception handler.
1569 x_return_status := FND_API.g_ret_sts_error;
1570 --<bug#5523323 END>
1571 IF (PO_LOG.d_exc)
1572 THEN
1573 PO_LOG.exc(d_module_base,d_progress, SQLCODE || SQLERRM);
1574 PO_LOG.proc_end(d_module_base,'x_return_status', x_return_status);
1575 PO_LOG.proc_end(d_module_base,'x_msg_count', x_msg_count );
1576 PO_LOG.proc_end(d_module_base,'x_msg_data', x_msg_data );
1577 END IF;
1578 WHEN OTHERS THEN
1579 ROLLBACK TO post_requisition_lines_PVT;
1580 BEGIN
1581 -- Log a debug message, add the error the the API message list.
1582 po_message_s.sql_error(g_pkg_name, l_module, d_progress, SQLCODE, SQLERRM);
1583 FND_MSG_PUB.Add;
1584 FND_MESSAGE.set_encoded(encoded_message =>FND_MSG_PUB.GET());
1585 x_msg_data := FND_MESSAGE.get;
1586 x_error_msg_tbl.extend(1);
1587 x_error_msg_tbl(1) := x_msg_data;
1588 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1589 IF (PO_LOG.d_exc)
1590 THEN
1591 PO_LOG.exc(d_module_base,d_progress, SQLCODE || SQLERRM);
1592 PO_LOG.proc_end(d_module_base,'x_return_status', x_return_status);
1593 PO_LOG.proc_end(d_module_base,'x_msg_count', x_msg_count );
1594 PO_LOG.proc_end(d_module_base,'x_msg_data', x_msg_data );
1595 END IF;
1596 EXCEPTION
1597 WHEN OTHERS THEN
1598 IF (PO_LOG.d_exc)
1599 THEN
1600 PO_LOG.exc(d_module_base,d_progress, SQLCODE || SQLERRM);
1601 PO_LOG.proc_end(d_module_base,'x_return_status', x_return_status);
1602 PO_LOG.proc_end(d_module_base,'x_msg_count', x_msg_count );
1603 PO_LOG.proc_end(d_module_base,'x_msg_data', x_msg_data );
1604 END IF;
1605 RAISE;
1606 END;
1607 END post_modify_requisition_lines;
1608
1609 -------------------------------------------------------------------------------
1610
1611 -------------------------------------------------------------------------------
1612
1613 PROCEDURE call_funds_reversal(p_api_version IN NUMBER,
1614 p_commit IN VARCHAR2,
1615 x_return_status OUT NOCOPY VARCHAR2,
1616 x_msg_count OUT NOCOPY NUMBER,
1617 x_msg_data OUT NOCOPY VARCHAR2,
1618 p_req_line_id IN NUMBER,
1619 p_handle_tax_flag IN VARCHAR2,
1620 x_online_report_id OUT NOCOPY NUMBER) IS
1621 l_module CONSTANT VARCHAR2(30) := 'CALL_FUNDS_REVERSAL';
1622 l_api_version CONSTANT NUMBER := 1.0;
1623 d_progress NUMBER;
1624 d_module_base CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(d_PACKAGE_BASE,
1625 l_module);
1626 --define object type variable for calling encumbrance api.
1627 l_po_return_code VARCHAR2(20);
1628
1629 BEGIN
1630
1631 d_progress := 10;
1632
1633 SAVEPOINT CALL_FUNDS_REVERSAL_PVT;
1634
1635 IF (PO_LOG.d_event) THEN
1636 PO_LOG.event(d_module_base,d_progress,'Starting calculate CALL_FUNDS_REVERSAL');
1637 END IF;
1638
1639 IF (PO_LOG.d_proc) THEN
1640 PO_LOG.proc_begin(d_module_base, 'p_api_version', p_api_version);
1641 PO_LOG.proc_begin(d_module_base, 'p_commit', p_commit);
1642 PO_LOG.proc_begin(d_module_base, 'p_req_line_id', p_req_line_id);
1643 END IF;
1644
1645 d_progress := 20;
1646 IF NOT FND_API.Compatible_API_Call(l_api_version,
1647 p_api_version,
1648 l_module,
1649 G_PKG_NAME) THEN
1650 IF (PO_LOG.d_event) THEN
1651 PO_LOG.event(d_module_base, d_progress, 'Api versions incompatible. Throwing an exception');
1652 END IF;
1653 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1654 END IF;
1655
1656 d_progress := 30;
1657
1658 IF PO_LOG.d_stmt THEN
1659 PO_LOG.stmt(d_module_base,d_progress,'Inserting values into po_req_split_lines_gt');
1660 END IF;
1661
1662 -- Performance fix for bug 4930487
1663 -- Prevented FTS on po_requisition_lines_all by adding an exists clause
1664 INSERT INTO po_req_split_lines_gt
1665 (requisition_header_id,
1666 requisition_line_id,
1667 allocated_qty,
1668 new_req_line_id,
1669 record_status)
1670 SELECT prl.requisition_header_id,
1671 p_req_line_id,
1672 prl.quantity,
1673 DECODE(prl.requisition_line_id,
1674 p_req_line_id,
1675 NULL,
1676 prl.requisition_line_id),
1677 DECODE(prl.requisition_line_id, p_req_line_id, 'S', 'N')
1678 FROM po_requisition_lines prl
1679 WHERE (prl.requisition_line_id = p_req_line_id OR
1680 prl.parent_req_line_id = p_req_line_id)
1681 AND EXISTS(
1682 SELECT requisition_header_id
1683 FROM po_requisition_lines_all PRL1
1684 WHERE PRL1.requisition_header_id = prl.requisition_header_id
1685 AND PRL1.requisition_line_id = p_req_line_id);
1686
1687
1688 IF SQL%ROWCOUNT < 1 THEN
1689 IF PO_LOG.d_stmt THEN
1690 PO_LOG.stmt(d_module_base,d_progress,'No rows inserted into PO_REQ_SPLIT_LINES_GT');
1691 END IF;
1692
1693 po_message_s.sql_error('No rows inserted into PO_REQ_SPLIT_LINES_GT', d_progress, sqlcode);
1694 FND_MSG_PUB.add;
1695 RAISE FND_API.g_exc_unexpected_error;
1696 END IF;
1697
1698 d_progress := 40;
1699 IF PO_LOG.d_stmt THEN
1700 PO_LOG.stmt(d_module_base,d_progress,'Handle Tax Adjustments');
1701 END IF;
1702
1703 IF (p_handle_tax_flag = 'Y') THEN
1704 PO_NEGOTIATIONS4_PVT.handle_tax_adjustments(p_api_version => 1.0,
1705 p_commit => 'F',
1706 x_return_status => x_return_status,
1707 x_msg_count => x_msg_count,
1708 x_msg_data => x_msg_data);
1709 IF (x_return_status <> 'S') THEN
1710 IF PO_LOG.d_stmt THEN
1711 PO_LOG.stmt(d_module_base,d_progress,'x_return_status',x_return_status);
1712 END IF;
1713 RAISE FND_API.g_exc_unexpected_error;
1714 END IF;
1715 END IF; /*IF (p_handle_tax_flag = 'Y')*/
1716
1717 d_progress := 50;
1718
1719 PO_NEGOTIATIONS4_PVT.handle_funds_reversal(p_api_version => 1.0,
1720 p_commit => 'F',
1721 x_return_status => x_return_status,
1722 x_msg_count => x_msg_count,
1723 x_msg_data => x_msg_data,
1724 x_online_report_id => x_online_report_id);
1725
1726 IF PO_LOG.d_stmt THEN
1727 PO_LOG.stmt(d_module_base, d_progress,'x_return_status',x_return_status);
1728 PO_LOG.stmt(d_module_base, d_progress,'l_po_return_code', l_po_return_code);
1729 PO_LOG.stmt(d_module_base, d_progress,'x_online_report_id', x_online_report_id);
1730 END IF;
1731
1732 IF (x_return_status = FND_API.g_ret_sts_error) THEN
1733 RAISE FND_API.g_exc_error;
1734 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
1735 RAISE FND_API.g_exc_unexpected_error;
1736 END IF;
1737
1738 d_progress := 60;
1739
1740 IF FND_API.To_Boolean(p_commit) THEN
1741 IF PO_LOG.d_event THEN
1742 PO_LOG.event(d_module_base, d_progress, 'Commiting work');
1743 END IF;
1744 COMMIT WORK;
1745 END IF;
1746
1747 -- Standard call to get message count and if count is 1,
1748 -- get message info.
1749 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1750 x_return_status := FND_API.G_RET_STS_SUCCESS;
1751
1752 d_progress := 70;
1753
1754 IF (PO_LOG.d_proc) THEN
1755 PO_LOG.proc_end(d_module_base, 'x_return_status', x_return_status);
1756 PO_LOG.proc_end(d_module_base, 'x_msg_count', x_msg_count);
1757 PO_LOG.proc_end(d_module_base, 'x_msg_data', x_msg_data);
1758 PO_LOG.proc_end(d_module_base,'x_online_report_id',x_online_report_id);
1759 END IF;
1760
1761 EXCEPTION
1762
1763 WHEN FND_API.g_exc_unexpected_error THEN
1764
1765 ROLLBACK TO CALL_FUNDS_REVERSAL_PVT;
1766
1767 x_msg_data := FND_MSG_PUB.GET();
1768 x_return_status := FND_API.g_ret_sts_unexp_error;
1769
1770 IF (PO_LOG.d_exc) THEN
1771 PO_LOG.exc(d_module_base, d_progress, SQLCODE || SQLERRM);
1772 PO_LOG.proc_end(d_module_base, 'x_return_status', x_return_status);
1773 PO_LOG.proc_end(d_module_base, 'x_msg_count', x_msg_count);
1774 PO_LOG.proc_end(d_module_base, 'x_msg_data', x_msg_data);
1775 PO_LOG.proc_end(d_module_base,'x_online_report_id',x_online_report_id);
1776 END IF;
1777
1778 WHEN FND_API.g_exc_error THEN
1779
1780 ROLLBACK TO CALL_FUNDS_REVERSAL_PVT;
1781
1782 x_return_status := FND_API.g_ret_sts_error;
1783
1784 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1785 p_data => x_msg_data);
1786
1787 IF (PO_LOG.d_exc) THEN
1788 PO_LOG.exc(d_module_base, d_progress, SQLCODE || SQLERRM);
1789 PO_LOG.proc_end(d_module_base, 'x_return_status', x_return_status);
1790 PO_LOG.proc_end(d_module_base, 'x_msg_count', x_msg_count);
1791 PO_LOG.proc_end(d_module_base, 'x_msg_data', x_msg_data);
1792 PO_LOG.proc_end(d_module_base,'x_online_report_id', x_online_report_id);
1793 END IF;
1794
1795 WHEN OTHERS THEN
1796
1797 po_message_s.sql_error(g_pkg_name, l_module, d_progress, SQLCODE, SQLERRM);
1798 FND_MSG_PUB.Add;
1799 FND_MESSAGE.set_encoded(encoded_message =>FND_MSG_PUB.GET());
1800
1801 ROLLBACK TO CALL_FUNDS_REVERSAL_PVT;
1802 x_msg_data := FND_MESSAGE.get;
1803 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1804
1805 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1806 p_data => x_msg_data);
1807
1808 IF (PO_LOG.d_exc) THEN
1809 PO_LOG.exc(d_module_base, d_progress, SQLCODE || SQLERRM);
1810 PO_LOG.proc_end(d_module_base, 'x_return_status', x_return_status);
1811 PO_LOG.proc_end(d_module_base, 'x_msg_count', x_msg_count);
1812 PO_LOG.proc_end(d_module_base, 'x_msg_data', x_msg_data);
1813 PO_LOG.proc_end(d_module_base, 'x_online_report_id', x_online_report_id);
1814
1815 END IF;
1816
1817 END call_funds_reversal;
1818 -------------------------------------------------------------------------------
1819 END PO_MODIFY_REQUISITION_PVT;