[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.7 2006/11/13 05:52:04 asista noship $ */
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 * Private Procedure: split_requisition_lines
16 * Requires: API message list has been initialized if p_init_msg_list is
17 * false.
18 * Modifies: Inserts new req lines and their distributions, For parent .
19 * req lines, update requisition_lines table to modified_by_agent_flag = 'Y' .
20 * Also sets prevent encumbrace flag to 'Y' in the po_req_distributions table.
21 * Effects: This api split the requisition lines, into two lines with specified quantity.
22 * This api uses a global temp.table to process the input given by autocreate(HTML) and
23 * inserts records into po_requisition_lines_all and po_req_distributions_all table.
24 * This api also handles the encumbrace effect of splitting requisition lines. This
25 * api would be called from Autocreate HTML.
26 *
27 * Returns:
28 * x_return_status - FND_API.G_RET_STS_SUCCESS if action succeeds
29 * FND_API.G_RET_STS_ERROR if action fails
30 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
31 * x_msg_count returns count of messages in the stack.
32 * x_msg_data returns message only if 1 message.
33 * Algorithm:
34 * 1. Get the requisition line id of the req line that needs
35 * to be split
36 * 2. Retrieve the quantity on the given line and split using
37 * split function.
38 * 3. Calculate the maximum line number of the lines that
39 * belong to the given requisition.
40 * 4. Using a for loop insert two records into the po_requisition_lines_all
41 * table and provide the correct line number by incrementing
42 * max line number by one in each iteration.
43 * 5. Update the split req line and set the modified flag
44 * and purchasing agent flag.
45 * 6. Copy the attachments from the parent line on to the
46 * new lines.
47 * 7. Handle tax adjustments for the new lines
48 * 8. Handle encumbrance funds results for the new and old
49 * lines.
50 *
51 */
52
53 PROCEDURE split_requisition_lines(p_api_version IN NUMBER,
54 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
55 p_commit IN VARCHAR2 := FND_API.G_FALSE,
56 x_return_status OUT NOCOPY VARCHAR2,
57 x_msg_count OUT NOCOPY NUMBER,
58 x_msg_data OUT NOCOPY VARCHAR2,
59 p_req_line_id IN NUMBER,
60 p_num_of_new_lines IN NUMBER,
61 p_quantity_tbl IN PO_TBL_NUMBER,
62 p_agent_id IN NUMBER,
63 p_calling_program IN VARCHAR2,
64 p_handle_tax_diff_if_enc IN VARCHAR2,
65 x_new_line_ids_tbl OUT NOCOPY PO_TBL_NUMBER,
66 x_error_msg_tbl OUT NOCOPY PO_TBL_VARCHAR2000
67 ) IS
68
69 l_module CONSTANT VARCHAR2(100) := 'split_requisition_lines';
70 d_module_base CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(d_PACKAGE_BASE,l_module);
71
72 l_api_version CONSTANT NUMBER := 1.0;
73 d_progress NUMBER;
74 l_old_org_id NUMBER;
75 BEGIN
76 --CREATE A SAVE POINT ON ENTERING THIS PROCEDURE
77 SAVEPOINT split_requisition_lines_PVT;
78
79 d_progress := 10;
80
81 IF PO_LOG.d_event THEN
82 PO_LOG.event(d_module_base,d_progress,'Starting Requisition Split ');
83 END IF;
84 --Initialize the error messages table
85 x_error_msg_tbl :=po_tbl_varchar2000();
86 IF NOT FND_API.Compatible_API_Call(l_api_version,
87 p_api_version,
88 l_module,
89 G_PKG_NAME)
90 THEN
91 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
92 END IF;
93
94 --Get the value of current org id. We would set the
95 --org context back to this value before leaving the
96 --program.
97 l_old_org_id := PO_MOAC_UTILS_PVT.get_current_org_id;
98
99 IF PO_LOG.d_stmt THEN
100 PO_LOG.stmt(d_module_base,d_progress,'l_old_org_id ', l_old_org_id);
101 PO_LOG.stmt(d_module_base,d_progress,'Retrieved the value of current orgId');
102 END IF;
103
104 d_progress := 20;
105
106 create_requisition_lines(p_api_version => p_api_version,
107 p_init_msg_list => p_init_msg_list,
108 p_commit => p_commit,
109 x_return_status => x_return_status,
110 x_msg_count => x_msg_count,
111 x_msg_data => x_msg_data,
112 p_req_line_id => p_req_line_id,
113 p_num_of_new_lines => p_num_of_new_lines,
114 p_quantity_tbl => p_quantity_tbl,
115 p_agent_id => p_agent_id,
116 p_calling_program => p_calling_program,
117 x_new_line_ids_tbl => x_new_line_ids_tbl,
118 x_error_msg_tbl => x_error_msg_tbl);
119
120 IF PO_LOG.d_stmt THEN
121 PO_LOG.stmt(d_module_base,d_progress,'x_return_status',x_return_status);
122 END IF;
123
124 IF (x_return_status = FND_API.g_ret_sts_error)
125 THEN
126 RAISE FND_API.g_exc_error;
127 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error)
128 THEN
129 RAISE FND_API.g_exc_unexpected_error;
130 END IF; --x_return_status
131
132 d_progress := 30;
133
134 post_modify_requisition_lines(p_api_version => p_api_version,
135 p_init_msg_list => p_init_msg_list,
136 p_commit => p_commit,
137 x_return_status => x_return_status,
138 x_msg_count => x_msg_count,
139 x_msg_data => x_msg_data,
140 p_req_line_id => p_req_line_id,
141 p_handle_tax_diff_if_enc => p_handle_tax_diff_if_enc,
142 p_new_line_ids_tbl => x_new_line_ids_tbl,
143 x_error_msg_tbl => x_error_msg_tbl);
144
145
146 d_progress:=40;
147
148 IF PO_LOG.d_stmt THEN
149 PO_LOG.stmt(d_module_base,d_progress,'x_return_status',x_return_status);
150 END IF;
151
152 IF (x_return_status = FND_API.g_ret_sts_error)
153 THEN
154 RAISE FND_API.g_exc_error;
155 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error)
156 THEN
157 RAISE FND_API.g_exc_unexpected_error;
158 END IF; --x_return_status
159
160 IF PO_LOG.d_stmt THEN
161 PO_LOG.stmt(d_module_base,d_progress,'Reset the org context to old value');
162 END IF;
163
164 --Set the org context back to the original org context
165 po_moac_utils_pvt.set_org_context(l_old_org_id);
166
167 IF PO_LOG.d_stmt THEN
168 PO_LOG.stmt(d_module_base,d_progress,'l_old_org_id',l_old_org_id);
169 END IF;
170
171 d_progress:=50;
172 x_return_status := FND_API.g_ret_sts_success;
173 IF (PO_LOG.d_proc)
174 THEN
175 PO_LOG.proc_end(d_module_base,'x_return_status', x_return_status);
176 PO_LOG.proc_end(d_module_base,'x_msg_count', x_msg_count );
177 PO_LOG.proc_end(d_module_base,'x_msg_data', x_msg_data );
178 END IF;
179 EXCEPTION
180 WHEN FND_API.g_exc_error THEN
181 ROLLBACK TO split_requisition_lines_PVT;
182 x_return_status := FND_API.g_ret_sts_error;
183 po_moac_utils_pvt.set_org_context(l_old_org_id);
184 IF (PO_LOG.d_exc)
185 THEN
186 PO_LOG.exc(d_module_base,d_progress, SQLCODE || SQLERRM);
187 PO_LOG.proc_end(d_module_base,'x_return_status', x_return_status);
188 PO_LOG.proc_end(d_module_base,'x_msg_count', x_msg_count );
189 PO_LOG.proc_end(d_module_base,'x_msg_data', x_msg_data );
190 END IF;
191 WHEN FND_API.g_exc_unexpected_error THEN
192 ROLLBACK TO split_requisition_lines_PVT;
193 po_moac_utils_pvt.set_org_context(l_old_org_id);
194
195 po_message_s.sql_error(g_pkg_name, l_module, d_progress, SQLCODE, SQLERRM);
196 FND_MSG_PUB.Add;
197 FND_MESSAGE.set_encoded(encoded_message =>FND_MSG_PUB.GET());
198 x_msg_data := FND_MESSAGE.get;
199 x_error_msg_tbl.extend(1);
200 x_error_msg_tbl(x_error_msg_tbl.count) := x_msg_data;
201 x_return_status := FND_API.g_ret_sts_error;
202
203 IF (PO_LOG.d_exc)
204 THEN
205 PO_LOG.exc(d_module_base,d_progress, SQLCODE || SQLERRM);
206 PO_LOG.proc_end(d_module_base,'x_return_status', x_return_status);
207 PO_LOG.proc_end(d_module_base,'x_msg_count', x_msg_count );
208 PO_LOG.proc_end(d_module_base,'x_msg_data', x_msg_data );
209 END IF;
210 WHEN OTHERS THEN
211 ROLLBACK TO split_requisition_lines_PVT;
212 po_moac_utils_pvt.set_org_context(l_old_org_id);
213
214 BEGIN
215 -- Log a debug message, add the error the the API message list.
216 po_message_s.sql_error(g_pkg_name, l_module, d_progress, SQLCODE, SQLERRM);
217 FND_MSG_PUB.Add;
218 FND_MESSAGE.set_encoded(encoded_message =>FND_MSG_PUB.GET());
219 x_msg_data := FND_MESSAGE.get;
220 x_error_msg_tbl.extend(1);
221 x_error_msg_tbl(x_error_msg_tbl.count) := x_msg_data;
222
223 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
224 IF (PO_LOG.d_exc)
225 THEN
226 PO_LOG.exc(d_module_base,d_progress, SQLCODE || SQLERRM);
227 PO_LOG.proc_end(d_module_base,'x_return_status', x_return_status);
228 PO_LOG.proc_end(d_module_base,'x_msg_count', x_msg_count );
229 PO_LOG.proc_end(d_module_base,'x_msg_data', x_msg_data );
230 END IF;
231
232 EXCEPTION
233 WHEN OTHERS THEN
234 po_moac_utils_pvt.set_org_context(l_old_org_id);
235 IF (PO_LOG.d_exc)
236 THEN
237 PO_LOG.exc(d_module_base,d_progress, SQLCODE || SQLERRM);
238 PO_LOG.proc_end(d_module_base,'x_return_status', x_return_status);
239 PO_LOG.proc_end(d_module_base,'x_msg_count', x_msg_count );
240 PO_LOG.proc_end(d_module_base,'x_msg_data', x_msg_data );
241 END IF;
242 RAISE;
243 END;
244 END split_requisition_lines;
245
246 PROCEDURE create_requisition_lines(p_api_version IN NUMBER,
247 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
248 p_commit IN VARCHAR2 := FND_API.G_FALSE,
249 x_return_status OUT NOCOPY VARCHAR2,
250 x_msg_count OUT NOCOPY NUMBER,
251 x_msg_data OUT NOCOPY VARCHAR2,
252 p_req_line_id IN NUMBER,
253 p_num_of_new_lines IN NUMBER,
254 p_quantity_tbl IN PO_TBL_NUMBER,
255 p_agent_id IN NUMBER,
256 p_calling_program IN VARCHAR2,
257 x_new_line_ids_tbl OUT NOCOPY PO_TBL_NUMBER,
258 x_error_msg_tbl OUT NOCOPY PO_TBL_VARCHAR2000
259 ) IS
260
261 l_module CONSTANT VARCHAR2(100) := 'create_requisition_lines';
262 d_module_base CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(d_PACKAGE_BASE, l_module);
263 l_api_version CONSTANT NUMBER := 1.0;
264 d_progress NUMBER;
265 --declare the result tables.
266 new_req_line_id_rslt_tbl PO_TBL_NUMBER;
267 -- SQL What:This cursor Locks the requisition lines the api is going to
268 -- process
269 -- SQL Why :This locking ensures that the records are not touched by
270 -- any other transactions.Opening the cursor keeps the records
271 -- locked till the transaction control happens.
272 CURSOR lock_req_lines_cs IS
273 SELECT prl.requisition_line_id,
274 prl.org_id
275 FROM po_requisition_lines_all prl
276 WHERE prl.requisition_line_id = p_req_line_id
277 FOR UPDATE OF prl.quantity NOWAIT;
278
279 l_serial_num NUMBER;
280 l_line_num_index NUMBER;
281 l_max_line_num NUMBER;
282 l_current_org_id NUMBER;
283 l_requisition_line_id NUMBER;
284 l_old_org_id NUMBER;
285 BEGIN
286 --CREATE A SAVE POINT ON ENTERING THIS PROCEDURE
287 SAVEPOINT create_requisition_lines_PVT;
288
289 d_progress := 10;
290
291 IF PO_LOG.d_event THEN
292 PO_LOG.event(d_module_base,d_progress,'Starting Requisition Split ');
293 END IF;
294 --Initialize the error messages table
295 x_error_msg_tbl :=po_tbl_varchar2000();
296 IF NOT FND_API.Compatible_API_Call(l_api_version,
297 p_api_version,
301 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
298 l_module,
299 G_PKG_NAME)
300 THEN
302 END IF;
303
304 IF (PO_LOG.d_proc)
305 THEN
306 PO_LOG.proc_begin(d_module_base);
307 PO_LOG.proc_begin(d_module_base,'p_api_version', p_api_version );
308 PO_LOG.proc_begin(d_module_base,'p_init_msg_list', p_init_msg_list);
309 PO_LOG.proc_begin(d_module_base,'p_commit', p_commit );
310 PO_LOG.proc_begin(d_module_base,'p_req_line_id', p_req_line_id );
311 PO_LOG.proc_begin(d_module_base,'p_quantity_tbl', p_quantity_tbl );
312 PO_LOG.proc_begin(d_module_base,'p_agent_id', p_agent_id );
313 END IF;
314
315 IF PO_LOG.d_stmt THEN
316 PO_LOG.stmt(d_module_base,d_progress,'Attempting to lock the requisition line ');
317 END IF;
318
319 -- Lock the requisition lines the api is going to process
320 -- Retrieve the value of requisition org id so that the org
321 -- context can be set to this value.
322
323 OPEN lock_req_lines_cs;
324
325 FETCH lock_req_lines_cs
326 INTO l_requisition_line_id,
327 l_current_org_id;
328
329 CLOSE lock_req_lines_cs;
330
331 IF PO_LOG.d_stmt THEN
332 PO_LOG.stmt(d_module_base,d_progress,'Locked the requisition Line Successfully ');
333 END IF;
334
335 d_progress := 20;
336 --Set the org context to the org id of the parent req line.
337 po_moac_utils_pvt.set_org_context(l_current_org_id);
338
339 IF PO_LOG.d_stmt THEN
340 PO_LOG.stmt(d_module_base,d_progress,'l_current_org_id',l_current_org_id);
341 PO_LOG.stmt(d_module_base,d_progress,'Set the org context to the organization in which Requisition was raised.');
342 END IF;
343
344 d_progress := 30;
345
346 IF PO_LOG.d_stmt THEN
347 PO_LOG.stmt(d_module_base,d_progress,'Calculating value of max line number for the given requisition');
348 END IF;
349 --SQL What:Retrieve the max line number for the given requisition
350 -- to which the requisition line belongs
351 --SQL Why :This is required to calculate the line numbers when creating
352 -- the new requisition lines
353
354 SELECT MAX(prl.line_num)
355 INTO l_max_line_num
356 FROM po_requisition_lines_all prl
357 WHERE prl.requisition_header_id =
358 (SELECT requisition_header_id
359 FROM po_requisition_lines_all
360 WHERE requisition_line_id = p_req_Line_id);
361
362 IF PO_LOG.d_stmt THEN
363 PO_LOG.stmt(d_module_base,d_progress,'l_max_line_num',l_max_line_num);
364 END IF;
365
366 d_progress := 40;
367
368 -- Call the function to split the requisition line
369 x_new_line_ids_tbl := po_tbl_number(p_quantity_tbl.count);
370
371 FOR l_line_num_index IN 1 .. p_num_of_new_lines
372 LOOP
373 --ascertain the serial number for each req line created
374 --This would be added to max line number to determine the
375 --line number for the line being created.
376
377 IF l_line_num_index = 1
378 THEN
379 l_serial_num := 1;
380 ELSE
381 l_serial_num := l_serial_num + 1;
382 END IF;
383 --x_new_line_ids_tbl(l_line_num_index):=l_serial_num;
384 --Insert appropriate data into requisition lines all
385
386 IF PO_LOG.d_stmt THEN
387 PO_LOG.stmt(d_module_base,d_progress,'Inserting a new row');
388 PO_LOG.stmt(d_module_base,d_progress,'l_serial_num',l_serial_num);
389 END IF;
390
391
392 INSERT INTO po_requisition_lines_all
393 (requisition_line_id,
394 requisition_header_id,
395 line_num,
396 line_type_id,
397 category_id,
398 item_description,
399 unit_meas_lookup_code,
400 unit_price,
401 quantity,
402 deliver_to_location_id,
403 to_person_id,
404 last_update_date,
405 last_updated_by,
406 source_type_code,
407 last_update_login,
408 creation_date,
409 created_by,
410 item_id,
411 item_revision,
412 quantity_delivered,
413 suggested_buyer_id,
414 encumbered_flag,
415 rfq_required_flag,
416 need_by_date,
417 line_location_id,
418 modified_by_agent_flag,
419 parent_req_line_id,
420 justification,
421 note_to_agent,
422 note_to_receiver,
423 purchasing_agent_id,
424 document_type_code,
425 blanket_po_header_id,
426 blanket_po_line_num,
427 currency_code,
428 rate_type,
429 rate_date,
430 rate,
431 currency_unit_price,
432 suggested_vendor_name,
433 suggested_vendor_location,
434 suggested_vendor_contact,
435 suggested_vendor_phone,
439 must_use_sugg_vendor_flag,
436 suggested_vendor_product_code,
437 un_number_id,
438 hazard_class_id,
440 reference_num,
441 on_rfq_flag,
442 urgent_flag,
443 cancel_flag,
444 source_organization_id,
445 source_subinventory,
446 destination_type_code,
447 destination_organization_id,
448 destination_subinventory,
449 quantity_cancelled,
450 cancel_date,
451 cancel_reason,
452 closed_code,
453 agent_return_note,
454 changed_after_research_flag,
455 vendor_id,
456 vendor_site_id,
457 vendor_contact_id,
458 research_agent_id,
459 on_line_flag,
460 wip_entity_id,
461 wip_line_id,
462 wip_repetitive_schedule_id,
463 wip_operation_seq_num,
464 wip_resource_seq_num,
465 attribute_category,
466 destination_context,
467 inventory_source_context,
468 vendor_source_context,
469 attribute1,
470 attribute2,
471 attribute3,
472 attribute4,
473 attribute5,
474 attribute6,
475 attribute7,
476 attribute8,
477 attribute9,
478 attribute10,
479 attribute11,
480 attribute12,
481 attribute13,
482 attribute14,
483 attribute15,
484 bom_resource_id,
485 ussgl_transaction_code,
486 government_context,
487 closed_reason,
488 closed_date,
489 transaction_reason_code,
490 quantity_received,
491 tax_code_id,
492 tax_user_override_flag,
493 oke_contract_header_id,
494 oke_contract_version_id,
495 secondary_unit_of_measure,
496 secondary_quantity,
497 preferred_grade,
498 secondary_quantity_received,
499 secondary_quantity_cancelled,
500 auction_header_id,
501 auction_display_number,
502 auction_line_number,
503 reqs_in_pool_flag,
504 vmi_flag,
505 bid_number,
506 bid_line_number,
507 order_type_lookup_code,
508 purchase_basis,
509 matching_basis,
510 org_id,
511 catalog_type,
512 catalog_source,
513 manufacturer_id,
514 manufacturer_name,
515 manufacturer_part_number,
516 requester_email,
517 requester_fax,
518 requester_phone,
519 unspsc_code,
520 other_category_code,
521 supplier_duns,
522 tax_status_indicator,
523 pcard_flag,
524 new_supplier_flag,
525 auto_receive_flag,
526 tax_attribute_update_code)
527 (SELECT po_requisition_lines_s.NEXTVAL,
528 prl.requisition_header_id,
529 (l_serial_num + l_max_line_num),
530 prl.line_type_id,
531 prl.category_id,
532 prl.item_description,
533 prl.unit_meas_lookup_code,
534 prl.unit_price,
535 p_quantity_tbl(l_line_num_index),
536 prl.deliver_to_location_id,
537 prl.to_person_id,
538 prl.last_update_date,
539 prl.last_updated_by,
540 prl.source_type_code,
541 prl.last_update_login,
542 prl.creation_date,
543 prl.created_by,
544 prl.item_id,
545 prl.item_revision,
546 prl.quantity_delivered,
547 prl.suggested_buyer_id,
548 prl.encumbered_flag,
549 prl.rfq_required_flag,
550 prl.need_by_date,
551 prl.line_location_id,
552 NULL,
553 p_req_line_id,
554 prl.justification,
555 prl.note_to_agent,
556 prl.note_to_receiver,
557 prl.purchasing_agent_id,
558 prl.document_type_code,
559 prl.blanket_po_header_id,
560 prl.blanket_po_line_num,
561 prl.currency_code,
562 prl.rate_type,
563 prl.rate_date,
564 prl.rate,
565 prl.currency_unit_price,
566 prl.suggested_vendor_name,
567 prl.suggested_vendor_location,
568 prl.suggested_vendor_contact,
569 prl.suggested_vendor_phone,
570 prl.suggested_vendor_product_code,
574 prl.must_use_sugg_vendor_flag,
571 decode(p_calling_program,g_CALLING_PROGRAM_SPLIT,
572 prl.un_number_id,null),
573 prl.hazard_class_id,
575 prl.reference_num,
576 prl.on_rfq_flag,
577 prl.urgent_flag,
578 prl.cancel_flag,
579 prl.source_organization_id,
580 prl.source_subinventory,
581 prl.destination_type_code,
582 prl.destination_organization_id,
583 prl.destination_subinventory,
584 prl.quantity_cancelled,
585 prl.cancel_date,
586 prl.cancel_reason,
587 prl.closed_code,
588 prl.agent_return_note,
589 prl.changed_after_research_flag,
590 prl.vendor_id,
591 prl.vendor_site_id,
592 prl.vendor_contact_id,
593 prl.research_agent_id,
594 prl.on_line_flag,
595 prl.wip_entity_id,
596 prl.wip_line_id,
597 prl.wip_repetitive_schedule_id,
598 prl.wip_operation_seq_num,
599 prl.wip_resource_seq_num,
600 prl.attribute_category,
601 prl.destination_context,
602 prl.inventory_source_context,
603 prl.vendor_source_context,
604 prl.attribute1,
605 prl.attribute2,
606 prl.attribute3,
607 prl.attribute4,
608 prl.attribute5,
609 prl.attribute6,
610 prl.attribute7,
611 prl.attribute8,
612 prl.attribute9,
613 prl.attribute10,
614 prl.attribute11,
615 prl.attribute12,
616 prl.attribute13,
617 prl.attribute14,
618 prl.attribute15,
619 prl.bom_resource_id,
620 prl.ussgl_transaction_code,
621 prl.government_context,
622 prl.closed_reason,
623 prl.closed_date,
624 prl.transaction_reason_code,
625 prl.quantity_received,
626 prl.tax_code_id,
627 prl.tax_user_override_flag,
628 prl.oke_contract_header_id,
629 prl.oke_contract_version_id,
630 decode(p_calling_program,g_CALLING_PROGRAM_SPLIT,
631 prl.secondary_unit_of_measure,null),
632 prl.secondary_quantity,
633 prl.preferred_grade,
634 prl.secondary_quantity_received,
635 prl.secondary_quantity_cancelled,
636 prl.auction_header_id,
637 prl.auction_display_number,
638 prl.auction_line_number,
639 'Y', --new reqs are placed back in pool after splitting
640 prl.vmi_flag,
641 prl.bid_number,
642 prl.bid_line_number,
643 prl.order_type_lookup_code,
644 prl.purchase_basis,
645 prl.matching_basis,
646 prl.org_id,
647 prl.catalog_type,
648 prl.catalog_source,
649 prl.manufacturer_id,
650 prl.manufacturer_name,
651 prl.manufacturer_part_number,
652 prl.requester_email,
653 prl.requester_fax,
654 prl.requester_phone,
655 prl.unspsc_code,
656 prl.other_category_code,
657 prl.supplier_duns,
658 prl.tax_status_indicator,
659 prl.pcard_flag,
660 prl.new_supplier_flag,
661 prl.auto_receive_flag,
662 g_TAX_ATTRIBUTE_CREATE
663 FROM po_requisition_lines_all prl
664 WHERE prl.requisition_line_id = p_req_line_id);
665
666 END LOOP;
667
668 d_progress := 50;
669
670 IF PO_LOG.d_stmt THEN
671 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');
672 END IF;
673
674 -- SQL What:Mark all the parent requisition lines which are split
675 -- with modified_by_agent_flag setting 'Y'.
676 -- SQL Why :This indicates that this requisition lines have been
677 -- modified by the buyer and no longer available for any
678 -- operations.
679 -- Also implemented the following rules for Catalog Integration.
680
681 -- If the original requisition line has a bid and/or negotiation
682 -- reference, the bid and/or negotiation reference from the
683 -- original requisition line should be dropped.
684
685 -- If the original requisition line has a value for On RFQ flag
686 -- the On RFQ flag from the original requisition line should be
687 -- dropped.
688
689 UPDATE po_requisition_lines_all
690 SET modified_by_agent_flag = 'Y',
694 g_CALLING_PROGRAM_CATALOG,
691 purchasing_agent_id = p_agent_id,
692 reqs_in_pool_flag = NULL, --<REQINPOOL>
693 on_rfq_flag = decode(p_calling_program,
695 null,
696 on_rfq_flag),
697 bid_number = decode(p_calling_program,
698 g_calling_program_catalog,
699 null,
700 bid_number),
701 bid_line_number = decode(p_calling_program,
702 g_calling_program_catalog,
703 null,
704 bid_line_number),
705 auction_header_id = decode(p_calling_program,
706 g_calling_program_catalog,
707 null,
708 auction_header_id),
709 auction_display_number = decode(p_calling_program,
710 g_calling_program_catalog,
711 null,
712 auction_display_number),
713 auction_line_number = decode(p_calling_program,
714 g_calling_program_catalog,
715 null,
716 auction_line_number),
717 last_update_date = SYSDATE,
718 last_updated_by = FND_GLOBAL.USER_ID,
719 last_update_login = FND_GLOBAL.LOGIN_ID
720 WHERE requisition_line_id = p_req_line_id;
721
722 --Collect all req line id's in a new table.
723 --get the ids of the new lines created. This would be returned back as
724 --an out parameter
725 --Performance fix for bug 4930487
726 SELECT PRL1.requisition_line_id
727 BULK COLLECT
728 INTO new_req_line_id_rslt_tbl
729 FROM PO_REQUISITION_LINES_ALL PRL1, PO_REQUISITION_LINES_ALL PRL2
730 WHERE PRL1.requisition_header_id = PRL2.requisition_header_id
731 AND PRL1.parent_req_line_id = p_req_line_id
732 AND PRL2.requisition_line_id = p_req_line_id;
733
734 IF PO_LOG.d_stmt THEN
735 FOR i in 1..new_req_line_id_rslt_tbl.count LOOP
736 PO_LOG.stmt(d_module_base,d_progress,'new_req_line_id_rslt_tbl('||i||')',new_req_line_id_rslt_tbl(i));
737 END LOOP;
738 END IF;
739 x_new_line_ids_tbl := new_req_line_id_rslt_tbl;
740
741 d_progress := 60;
742
743 IF FND_API.To_Boolean(p_commit)
744 THEN
745 IF PO_LOG.d_event THEN
746 PO_LOG.event(d_module_base,d_progress,'Commiting work');
747 END IF;
748 COMMIT WORK;
749 END IF; --FND_API
750
751 d_progress := 70;
752 x_return_status := FND_API.g_ret_sts_success;
753
754 IF PO_LOG.d_stmt THEN
755 PO_LOG.stmt(d_module_base,d_progress,'l_old_org_id',l_old_org_id);
756 END IF;
757
758 IF (PO_LOG.d_proc)
759 THEN
760 PO_LOG.proc_end(d_module_base,'x_return_status', x_return_status);
761 PO_LOG.proc_end(d_module_base,'x_msg_count', x_msg_count );
762 PO_LOG.proc_end(d_module_base,'x_msg_data', x_msg_data );
763 END IF;
764 EXCEPTION
765 WHEN OTHERS THEN
766 ROLLBACK TO create_requisition_lines_PVT;
767 BEGIN
768 -- Log a debug message, add the error the the API message list.
769 po_message_s.sql_error(g_pkg_name, l_module, d_progress, SQLCODE, SQLERRM);
770 FND_MSG_PUB.Add;
771 FND_MESSAGE.set_encoded(encoded_message =>FND_MSG_PUB.GET());
772 x_msg_data := FND_MESSAGE.get;
773 x_error_msg_tbl.extend(1);
774 x_error_msg_tbl(x_error_msg_tbl.count) := x_msg_data;
775 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
776 IF (PO_LOG.d_exc)
777 THEN
778 PO_LOG.exc(d_module_base,d_progress, SQLCODE || SQLERRM);
779 PO_LOG.proc_end(d_module_base,'x_return_status', x_return_status);
780 PO_LOG.proc_end(d_module_base,'x_msg_count', x_msg_count );
781 PO_LOG.proc_end(d_module_base,'x_msg_data', x_msg_data );
782 END IF;
783
784 EXCEPTION
785 WHEN OTHERS THEN
786 po_moac_utils_pvt.set_org_context(l_old_org_id);
787 IF (PO_LOG.d_exc)
788 THEN
789 PO_LOG.exc(d_module_base,d_progress, SQLCODE || SQLERRM);
790 PO_LOG.proc_end(d_module_base,'x_return_status', x_return_status);
791 PO_LOG.proc_end(d_module_base,'x_msg_count', x_msg_count );
792 PO_LOG.proc_end(d_module_base,'x_msg_data', x_msg_data );
793 END IF;
794 RAISE;
795 END;
796 END create_requisition_lines;
797
798 -------------------------------------------------------------------------------
799
800 PROCEDURE post_modify_requisition_lines(p_api_version IN NUMBER,
801 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
802 p_commit IN VARCHAR2 := FND_API.G_FALSE,
806 p_req_line_id IN NUMBER,
803 x_return_status OUT NOCOPY VARCHAR2,
804 x_msg_count OUT NOCOPY NUMBER,
805 x_msg_data OUT NOCOPY VARCHAR2,
807 p_handle_tax_diff_if_enc IN VARCHAR2,
808 p_new_line_ids_tbl IN PO_TBL_NUMBER,
809 x_error_msg_tbl OUT NOCOPY PO_TBL_VARCHAR2000) IS
810
811 l_module CONSTANT VARCHAR2(100) := 'post_modify_requisition_lines';
812 d_module_base CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(d_PACKAGE_BASE, l_module);
813 l_api_version CONSTANT NUMBER := 1.0;
814 d_progress NUMBER;
815 --declare the result tables.
816 l_return_status VARCHAR2(1);
817 l_req_encumbrance_flag financials_system_parameters.req_encumbrance_flag%TYPE;
818 l_online_report_id PO_ONLINE_REPORT_TEXT.online_report_id%TYPE;
819 l_success BOOLEAN;
820 l_requisition_header_id NUMBER;
821 l_req_encumbered_flag VARCHAR2(5);
822 l_quantity_table PO_TBL_NUMBER;
823 l_tax_message FND_NEW_MESSAGES.message_text%type := NULL;
824 l_message_text FND_NEW_MESSAGES.message_text%type := NULL;
825 BEGIN
826 --CREATE A SAVE POINT ON ENTERING THIS PROCEDURE
827 SAVEPOINT post_requisition_lines_PVT;
828
829 d_progress := 10;
830
831 IF PO_LOG.d_event THEN
832 PO_LOG.event(d_module_base,d_progress,'Post Modify Requisition ');
833 END IF;
834
835 --Initialize the error messages table
836 x_error_msg_tbl :=po_tbl_varchar2000();
837
838 IF NOT FND_API.Compatible_API_Call(l_api_version,
839 p_api_version,
840 l_module,
841 G_PKG_NAME)
842 THEN
843 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
844 END IF;
845
846 IF (PO_LOG.d_proc)
847 THEN
848 PO_LOG.proc_begin(d_module_base);
849 PO_LOG.proc_begin(d_module_base,'p_api_version', p_api_version );
850 PO_LOG.proc_begin(d_module_base,'p_init_msg_list', p_init_msg_list);
851 PO_LOG.proc_begin(d_module_base,'p_commit', p_commit );
852 PO_LOG.proc_begin(d_module_base,'p_req_line_id', p_req_line_id );
853 END IF;
854
855 --update the supply for existing requisition line and create
856 --supply for the new lines
857
858 d_progress := 20;
859
860 IF PO_LOG.d_stmt THEN
861 PO_LOG.stmt(d_module_base,d_progress,'Updating the requisition supply');
862 END IF;
863
864 l_success := po_supply.po_req_supply(NULL,
865 p_req_line_id,
866 NULL,
867 g_EXPLODE_REQ_ACTION,
868 NULL,
869 NULL,
870 NULL);
871
872 --copy the attachments from the parent line onto the new req
873 --lines created. Also create the distributions for each of the new
874 --lines
875
876 d_progress := 40;
877
878 -- Performance fix for bug 4930487
879 SELECT PRL1.quantity
880 BULK COLLECT
881 INTO l_quantity_table
882 FROM po_requisition_lines_all PRL1, po_requisition_lines_all PRL2
883 WHERE PRL1.requisition_header_id = PRL2.requisition_header_id
884 AND PRL1.parent_req_line_id = p_req_line_id
885 AND PRL2.requisition_line_id = p_req_line_id;
886
887 FOR l_req_line_index IN 1 .. P_new_line_ids_tbl.COUNT
888 LOOP
889 IF PO_LOG.d_stmt THEN
890 PO_LOG.stmt(d_module_base,d_progress,'l_req_line_index',l_req_line_index);
891 PO_LOG.stmt(d_module_base,d_progress,'Coping attachments from old line to newly created lines');
892 END IF;
893
894 fnd_attached_documents2_pkg.copy_attachments(
895 X_from_entity_name => g_REQ_LINES_ENTITY,
896 X_from_pk1_value => p_req_line_id,
897 X_from_pk2_value => NULL,
898 X_from_pk3_value => NULL,
899 X_from_pk4_value => NULL,
900 X_from_pk5_value => NULL,
901 X_to_entity_name => g_REQ_LINES_ENTITY,
902 X_to_pk1_value => p_new_line_ids_tbl(l_req_line_index),
903 X_to_pk2_value => NULL,
904 X_to_pk3_value => NULL,
905 X_to_pk4_value => NULL,
906 X_to_pk5_value => NULL,
907 X_created_by => NULL,
908 X_last_update_login => NULL,
909 X_program_application_id => NULL,
910 X_program_id => NULL,
911 X_request_id => NULL,
912 X_automatically_added_flag => NULL);
913
914 IF PO_LOG.d_stmt THEN
915 PO_LOG.stmt(d_module_base,d_progress,'Creating a new distribution for ',p_new_line_ids_tbl(l_req_line_index));
916 END IF;
917 d_progress := 50;
918 --Create req distributions for the newly created line
919 PO_REQ_DIST_SV.create_dist_for_modify(
923
920 p_new_line_ids_tbl(l_req_line_index),
921 p_req_line_id,
922 l_quantity_table(l_req_line_index));
924 IF PO_LOG.d_stmt THEN
925 PO_LOG.stmt(d_module_base,d_progress,'Succesfully created a new distribution for',p_new_line_ids_tbl(l_req_line_index));
926 END IF;
927 END LOOP;
928
929 d_progress := 60;
930 --Retrieve the value of l_requisition_header_id first
931 select requisition_header_id
932 into l_requisition_header_id
933 from po_requisition_lines_all
934 where requisition_line_id = p_req_line_id;
935
936 IF PO_LOG.d_stmt THEN
937 PO_LOG.stmt(d_module_base,d_progress,'l_requisition_header_id',l_requisition_header_id);
938 END IF;
939 --Calculate the tax for the entire document again. The recoverable and non recoverable
940 --tax fields need to be updated appropriately.
941 PO_TAX_INTERFACE_PVT.calculate_tax_requisition(
942 p_requisition_header_id => l_requisition_header_id,
943 p_calling_program => g_CALLING_PROGRAM_SPLIT,
944 x_return_status => l_return_status);
945
946 --<Bug#4765982 Start>
947 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
948 l_tax_message := FND_MESSAGE.get_string('PO','PO_TAX_CALCULATION')||' : ' ;
949
950 FOR i IN 1..po_tax_interface_pvt.G_TAX_ERRORS_TBL.MESSAGE_TEXT.COUNT LOOP
951 l_message_text := l_tax_message || po_tax_interface_pvt.G_TAX_ERRORS_TBL.message_text(i);
952 FND_MESSAGE.set_name('PO','PO_CUSTOM_MSG');
953 FND_MESSAGE.set_token('TRANSLATED_TOKEN',l_message_text);
954 FND_MSG_PUB.Add;
955 END LOOP;
956
957 RAISE FND_API.G_EXC_ERROR;
958
959 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
960 FND_MESSAGE.set_name('PO','PO_PDOI_TAX_CALCULATION_ERR');
961 FND_MSG_PUB.Add;
962 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
963 END IF;--l_return_status
964 --<Bug#4765982 End>
965
966
967 --Check if the current org has req encumbrance enabled.
968 --If it is enabled then handle funds reversal for the parent
969 --line and encumber the newly created line
970 d_progress := 70;
971 IF PO_LOG.d_stmt THEN
972 PO_LOG.stmt(d_module_base,d_progress,'Checking if requisition encumbrance is switched on');
973 END IF;
974
975 SELECT nvl(req_encumbrance_flag, 'N')
976 INTO l_req_encumbrance_flag
977 FROM financials_system_parameters;
978
979 IF PO_LOG.d_stmt THEN
980 PO_LOG.stmt(d_module_base,d_progress,'l_req_encumbrance_flag',l_req_encumbrance_flag);
981 END IF;
982
983 --Select all the distribution lines which are to be reserved and
984 --unreserved into a plsql table
985 IF l_req_encumbrance_flag = 'Y'
986 THEN
987 begin
988 SELECT ENCUMBERED_FLAG
989 INTO l_req_encumbered_flag
990 FROM PO_REQUISITION_LINES_ALL
991 WHERE requisition_line_id = p_req_line_id;
992 exception
993 when others then
994 l_req_encumbered_flag :='N';
995 end;
996
997 if(l_req_encumbered_flag = 'Y')THEN
998 d_progress := 80;
999 IF PO_LOG.d_stmt THEN
1000 PO_LOG.stmt(d_module_base,d_progress,'Calling funds reversal');
1001 END IF;
1002 --Pass the distribution ids of the newly created lines to the
1003 --encumbrance api to reserve funds
1004 call_funds_reversal(p_api_version => 1.0,
1005 p_commit => p_commit,
1006 x_return_status => l_return_status,
1007 x_msg_count => x_msg_count,
1008 x_msg_data => x_msg_data,
1009 p_req_line_id => p_req_line_id,
1010 p_handle_tax_flag => p_handle_tax_diff_if_enc,
1011 x_online_report_id => l_online_report_id);
1012
1013 IF PO_LOG.d_stmt THEN
1014 PO_LOG.stmt(d_module_base,d_progress,'l_return_status'||l_return_status);
1015 END IF;
1016
1017 IF (l_return_status = FND_API.g_ret_sts_error)
1018 THEN
1019 RAISE FND_API.g_exc_error;
1020 ELSIF (l_return_status = FND_API.g_ret_sts_unexp_error)
1021 THEN
1022 RAISE FND_API.g_exc_unexpected_error;
1023 END IF; --l_return_status
1024 END IF;
1025 END IF; --l_req_encumbrance_flag
1026
1027 d_progress := 90;
1028
1029 IF FND_API.To_Boolean(p_commit)
1030 THEN
1031 IF PO_LOG.d_event THEN
1032 PO_LOG.event(d_module_base,d_progress,'Commiting work');
1033 END IF;
1034 COMMIT WORK;
1035 END IF; --FND_API
1036
1037 x_return_status := FND_API.g_ret_sts_success;
1038
1039 IF (PO_LOG.d_proc)
1040 THEN
1041 PO_LOG.proc_end(d_module_base,'x_return_status', x_return_status);
1042 PO_LOG.proc_end(d_module_base,'x_msg_count', x_msg_count );
1043 PO_LOG.proc_end(d_module_base,'x_msg_data', x_msg_data );
1047 ROLLBACK TO post_requisition_lines_PVT;
1044 END IF;
1045 EXCEPTION
1046 WHEN FND_API.g_exc_unexpected_error THEN
1048 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1049 p_data => x_msg_data);
1050 IF(x_msg_count>0)THEN
1051 NULL;
1052 ELSE
1053 po_message_s.sql_error(g_pkg_name, l_module, d_progress, SQLCODE, SQLERRM);
1054 FND_MSG_PUB.Add;
1055 END IF;
1056
1057 FND_MESSAGE.set_encoded(encoded_message =>FND_MSG_PUB.GET());
1058 x_msg_data := FND_MESSAGE.get;
1059 x_error_msg_tbl.extend(1);
1060 x_error_msg_tbl(x_error_msg_tbl.COUNT) := x_msg_data;
1061
1062 x_return_status := FND_API.g_ret_sts_unexp_error;
1063
1064 IF (PO_LOG.d_exc)
1065 THEN
1066 PO_LOG.exc(d_module_base,d_progress, SQLCODE || SQLERRM);
1067 PO_LOG.proc_end(d_module_base,'x_return_status', x_return_status);
1068 PO_LOG.proc_end(d_module_base,'x_msg_count', x_msg_count );
1069 PO_LOG.proc_end(d_module_base,'x_msg_data', x_msg_data );
1070 END IF;
1071 WHEN FND_API.g_exc_error THEN
1072 ROLLBACK TO post_requisition_lines_PVT;
1073
1074 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1075 p_data => x_msg_data);
1076
1077 IF l_online_report_id IS NOT NULL THEN
1078 PO_Document_Control_PVT.add_online_report_msgs
1079 (
1080 p_api_version => 1.0
1081 ,p_init_msg_list => FND_API.G_FALSE
1082 ,x_return_status => x_return_status
1083 ,p_online_report_id => l_online_report_id);
1084 END IF;
1085
1086 FOR i IN 1..FND_MSG_PUB.count_msg loop
1087 FND_MESSAGE.set_encoded(encoded_message =>
1088 FND_MSG_PUB.get(p_msg_index => i));
1089 x_error_msg_tbl.extend(1);
1090 x_error_msg_tbl(i) := FND_MESSAGE.get;
1091 END LOOP;
1092 --<bug#5523323 START>
1093 --Set the return status at the end so that the call to add
1094 --online report msgs onto the stack doesn't override the value set
1095 --by the exception handler.
1096 x_return_status := FND_API.g_ret_sts_error;
1097 --<bug#5523323 END>
1098 IF (PO_LOG.d_exc)
1099 THEN
1100 PO_LOG.exc(d_module_base,d_progress, SQLCODE || SQLERRM);
1101 PO_LOG.proc_end(d_module_base,'x_return_status', x_return_status);
1102 PO_LOG.proc_end(d_module_base,'x_msg_count', x_msg_count );
1103 PO_LOG.proc_end(d_module_base,'x_msg_data', x_msg_data );
1104 END IF;
1105 WHEN OTHERS THEN
1106 ROLLBACK TO post_requisition_lines_PVT;
1107 BEGIN
1108 -- Log a debug message, add the error the the API message list.
1109 po_message_s.sql_error(g_pkg_name, l_module, d_progress, SQLCODE, SQLERRM);
1110 FND_MSG_PUB.Add;
1111 FND_MESSAGE.set_encoded(encoded_message =>FND_MSG_PUB.GET());
1112 x_msg_data := FND_MESSAGE.get;
1113 x_error_msg_tbl.extend(1);
1114 x_error_msg_tbl(1) := x_msg_data;
1115 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1116 IF (PO_LOG.d_exc)
1117 THEN
1118 PO_LOG.exc(d_module_base,d_progress, SQLCODE || SQLERRM);
1119 PO_LOG.proc_end(d_module_base,'x_return_status', x_return_status);
1120 PO_LOG.proc_end(d_module_base,'x_msg_count', x_msg_count );
1121 PO_LOG.proc_end(d_module_base,'x_msg_data', x_msg_data );
1122 END IF;
1123 EXCEPTION
1124 WHEN OTHERS THEN
1125 IF (PO_LOG.d_exc)
1126 THEN
1127 PO_LOG.exc(d_module_base,d_progress, SQLCODE || SQLERRM);
1128 PO_LOG.proc_end(d_module_base,'x_return_status', x_return_status);
1129 PO_LOG.proc_end(d_module_base,'x_msg_count', x_msg_count );
1130 PO_LOG.proc_end(d_module_base,'x_msg_data', x_msg_data );
1131 END IF;
1132 RAISE;
1133 END;
1134 END post_modify_requisition_lines;
1135
1136 -------------------------------------------------------------------------------
1137
1138 -------------------------------------------------------------------------------
1139
1140 PROCEDURE call_funds_reversal(p_api_version IN NUMBER,
1141 p_commit IN VARCHAR2,
1142 x_return_status OUT NOCOPY VARCHAR2,
1143 x_msg_count OUT NOCOPY NUMBER,
1144 x_msg_data OUT NOCOPY VARCHAR2,
1145 p_req_line_id IN NUMBER,
1146 p_handle_tax_flag IN VARCHAR2,
1147 x_online_report_id OUT NOCOPY NUMBER) IS
1148 l_module CONSTANT VARCHAR2(30) := 'CALL_FUNDS_REVERSAL';
1149 l_api_version CONSTANT NUMBER := 1.0;
1150 d_progress NUMBER;
1151 d_module_base CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(d_PACKAGE_BASE,
1152 l_module);
1153 --define object type variable for calling encumbrance api.
1154 l_po_return_code VARCHAR2(20);
1155
1156 BEGIN
1157
1158 d_progress := 10;
1159
1163 PO_LOG.event(d_module_base,d_progress,'Starting calculate CALL_FUNDS_REVERSAL');
1160 SAVEPOINT CALL_FUNDS_REVERSAL_PVT;
1161
1162 IF (PO_LOG.d_event) THEN
1164 END IF;
1165
1166 IF (PO_LOG.d_proc) THEN
1167 PO_LOG.proc_begin(d_module_base, 'p_api_version', p_api_version);
1168 PO_LOG.proc_begin(d_module_base, 'p_commit', p_commit);
1169 PO_LOG.proc_begin(d_module_base, 'p_req_line_id', p_req_line_id);
1170 END IF;
1171
1172 d_progress := 20;
1173 IF NOT FND_API.Compatible_API_Call(l_api_version,
1174 p_api_version,
1175 l_module,
1176 G_PKG_NAME) THEN
1177 IF (PO_LOG.d_event) THEN
1178 PO_LOG.event(d_module_base, d_progress, 'Api versions incompatible. Throwing an exception');
1179 END IF;
1180 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1181 END IF;
1182
1183 d_progress := 30;
1184
1185 IF PO_LOG.d_stmt THEN
1186 PO_LOG.stmt(d_module_base,d_progress,'Inserting values into po_req_split_lines_gt');
1187 END IF;
1188
1189 -- Performance fix for bug 4930487
1190 -- Prevented FTS on po_requisition_lines_all by adding an exists clause
1191 INSERT INTO po_req_split_lines_gt
1192 (requisition_header_id,
1193 requisition_line_id,
1194 allocated_qty,
1195 new_req_line_id,
1196 record_status)
1197 SELECT prl.requisition_header_id,
1198 p_req_line_id,
1199 prl.quantity,
1200 DECODE(prl.requisition_line_id,
1201 p_req_line_id,
1202 NULL,
1203 prl.requisition_line_id),
1204 DECODE(prl.requisition_line_id, p_req_line_id, 'S', 'N')
1205 FROM po_requisition_lines prl
1206 WHERE (prl.requisition_line_id = p_req_line_id OR
1207 prl.parent_req_line_id = p_req_line_id)
1208 AND EXISTS(
1209 SELECT requisition_header_id
1210 FROM po_requisition_lines_all PRL1
1211 WHERE PRL1.requisition_header_id = prl.requisition_header_id
1212 AND PRL1.requisition_line_id = p_req_line_id);
1213
1214
1215 IF SQL%ROWCOUNT < 1 THEN
1216 IF PO_LOG.d_stmt THEN
1217 PO_LOG.stmt(d_module_base,d_progress,'No rows inserted into PO_REQ_SPLIT_LINES_GT');
1218 END IF;
1219
1220 po_message_s.sql_error('No rows inserted into PO_REQ_SPLIT_LINES_GT', d_progress, sqlcode);
1221 FND_MSG_PUB.add;
1222 RAISE FND_API.g_exc_unexpected_error;
1223 END IF;
1224
1225 d_progress := 40;
1226 IF PO_LOG.d_stmt THEN
1227 PO_LOG.stmt(d_module_base,d_progress,'Handle Tax Adjustments');
1228 END IF;
1229
1230 IF (p_handle_tax_flag = 'Y') THEN
1231 PO_NEGOTIATIONS4_PVT.handle_tax_adjustments(p_api_version => 1.0,
1232 p_commit => 'F',
1233 x_return_status => x_return_status,
1234 x_msg_count => x_msg_count,
1235 x_msg_data => x_msg_data);
1236 IF (x_return_status <> 'S') THEN
1237 IF PO_LOG.d_stmt THEN
1238 PO_LOG.stmt(d_module_base,d_progress,'x_return_status',x_return_status);
1239 END IF;
1240 RAISE FND_API.g_exc_unexpected_error;
1241 END IF;
1242 END IF; /*IF (p_handle_tax_flag = 'Y')*/
1243
1244 d_progress := 50;
1245
1246 PO_NEGOTIATIONS4_PVT.handle_funds_reversal(p_api_version => 1.0,
1247 p_commit => 'F',
1248 x_return_status => x_return_status,
1249 x_msg_count => x_msg_count,
1250 x_msg_data => x_msg_data,
1251 x_online_report_id => x_online_report_id);
1252
1253 IF PO_LOG.d_stmt THEN
1254 PO_LOG.stmt(d_module_base, d_progress,'x_return_status',x_return_status);
1255 PO_LOG.stmt(d_module_base, d_progress,'l_po_return_code', l_po_return_code);
1256 PO_LOG.stmt(d_module_base, d_progress,'x_online_report_id', x_online_report_id);
1257 END IF;
1258
1259 IF (x_return_status = FND_API.g_ret_sts_error) THEN
1260 RAISE FND_API.g_exc_error;
1261 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
1262 RAISE FND_API.g_exc_unexpected_error;
1263 END IF;
1264
1265 d_progress := 60;
1266
1267 IF FND_API.To_Boolean(p_commit) THEN
1268 IF PO_LOG.d_event THEN
1269 PO_LOG.event(d_module_base, d_progress, 'Commiting work');
1270 END IF;
1271 COMMIT WORK;
1272 END IF;
1273
1274 -- Standard call to get message count and if count is 1,
1275 -- get message info.
1276 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1277 x_return_status := FND_API.G_RET_STS_SUCCESS;
1278
1279 d_progress := 70;
1280
1281 IF (PO_LOG.d_proc) THEN
1282 PO_LOG.proc_end(d_module_base, 'x_return_status', x_return_status);
1283 PO_LOG.proc_end(d_module_base, 'x_msg_count', x_msg_count);
1284 PO_LOG.proc_end(d_module_base, 'x_msg_data', x_msg_data);
1285 PO_LOG.proc_end(d_module_base,'x_online_report_id',x_online_report_id);
1286 END IF;
1287
1288 EXCEPTION
1289
1290 WHEN FND_API.g_exc_unexpected_error THEN
1291
1292 ROLLBACK TO CALL_FUNDS_REVERSAL_PVT;
1293
1294 x_msg_data := FND_MSG_PUB.GET();
1295 x_return_status := FND_API.g_ret_sts_unexp_error;
1296
1297 IF (PO_LOG.d_exc) THEN
1298 PO_LOG.exc(d_module_base, d_progress, SQLCODE || SQLERRM);
1299 PO_LOG.proc_end(d_module_base, 'x_return_status', x_return_status);
1300 PO_LOG.proc_end(d_module_base, 'x_msg_count', x_msg_count);
1301 PO_LOG.proc_end(d_module_base, 'x_msg_data', x_msg_data);
1302 PO_LOG.proc_end(d_module_base,'x_online_report_id',x_online_report_id);
1303 END IF;
1304
1305 WHEN FND_API.g_exc_error THEN
1306
1307 ROLLBACK TO CALL_FUNDS_REVERSAL_PVT;
1308
1309 x_return_status := FND_API.g_ret_sts_error;
1310
1311 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1312 p_data => x_msg_data);
1313
1314 IF (PO_LOG.d_exc) THEN
1315 PO_LOG.exc(d_module_base, d_progress, SQLCODE || SQLERRM);
1316 PO_LOG.proc_end(d_module_base, 'x_return_status', x_return_status);
1317 PO_LOG.proc_end(d_module_base, 'x_msg_count', x_msg_count);
1318 PO_LOG.proc_end(d_module_base, 'x_msg_data', x_msg_data);
1319 PO_LOG.proc_end(d_module_base,'x_online_report_id', x_online_report_id);
1320 END IF;
1321
1322 WHEN OTHERS THEN
1323
1324 po_message_s.sql_error(g_pkg_name, l_module, d_progress, SQLCODE, SQLERRM);
1325 FND_MSG_PUB.Add;
1326 FND_MESSAGE.set_encoded(encoded_message =>FND_MSG_PUB.GET());
1327
1328 ROLLBACK TO CALL_FUNDS_REVERSAL_PVT;
1329 x_msg_data := FND_MESSAGE.get;
1330 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1331
1332 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1333 p_data => x_msg_data);
1334
1335 IF (PO_LOG.d_exc) THEN
1336 PO_LOG.exc(d_module_base, d_progress, SQLCODE || SQLERRM);
1337 PO_LOG.proc_end(d_module_base, 'x_return_status', x_return_status);
1338 PO_LOG.proc_end(d_module_base, 'x_msg_count', x_msg_count);
1339 PO_LOG.proc_end(d_module_base, 'x_msg_data', x_msg_data);
1340 PO_LOG.proc_end(d_module_base, 'x_online_report_id', x_online_report_id);
1341
1342 END IF;
1343
1344 END call_funds_reversal;
1345 -------------------------------------------------------------------------------
1346 END PO_MODIFY_REQUISITION_PVT;