[Home] [Help]
PACKAGE BODY: APPS.PO_RELGEN_PKG
Source
1 PACKAGE BODY PO_RELGEN_PKG AS
2 /* $Header: porelgeb.pls 120.30.12020000.4 2013/04/05 08:34:17 jemishra ship $ */
3
4 --<ENCUMBRANCE FPJ>
5 g_dest_type_code_SHOP_FLOOR CONSTANT
6 PO_DISTRIBUTIONS_ALL.destination_type_code%TYPE
7 := 'SHOP FLOOR'
8 ;
9
10 -- Bug 2701147 START
11 -- Constants for constructing error messages:
12
13 -- This is used as a delimiter in constructing the error msgs
14 g_delim CONSTANT VARCHAR2(1) := ' ';
15
16 g_reqmsg CONSTANT VARCHAR2(75) := substr(FND_MESSAGE.GET_STRING('PO', 'PO_REQ_TYPE'),1,25);
17
18 --Contains message 'Line#'
19 g_linemsg CONSTANT VARCHAR2(75) := substr(FND_MESSAGE.GET_STRING('PO', 'PO_ZMVOR_LINE'), 1,25);
20
21 --Contains message 'Shipment#'
22 g_shipmsg CONSTANT VARCHAR2(75) := substr(FND_MESSAGE.GET_STRING('PO', 'PO_ZMVOR_SHIPMENT'), 1,25);
23
24 --Contains message 'Distribution#'
25 g_distmsg CONSTANT VARCHAR2(75) := substr(FND_MESSAGE.GET_STRING('PO', 'PO_ZMVOR_DISTRIBUTION'), 1,25);
26
27 g_pkg_name CONSTANT VARCHAR2(30) := 'PO_RELGEN_PKG';
28 c_log_head CONSTANT VARCHAR2(50) := 'po.plsql.'|| G_PKG_NAME || '.';
29
30 -- Read the profile option that enables/disables the debug log
31 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
32
33 g_debug_stmt BOOLEAN := PO_DEBUG.is_debug_stmt_on;
34
35 -- <INVCONV R12>
36 g_chktype_TRACKING_QTY_IND CONSTANT
37 MTL_SYSTEM_ITEMS_B.TRACKING_QUANTITY_IND%TYPE
38 := 'PS';
39
40 g_bpamsg CONSTANT VARCHAR2(75) := substr(FND_MESSAGE.GET_STRING('PO', 'PO_BLANKET_PO'),1,26) ;--12553671
41
42 -- Private procedure:
43 PROCEDURE preapproval_checks( p_po_header_id IN NUMBER,
44 p_req_num IN VARCHAR2,
45 p_req_line_num IN NUMBER,
46 x_check_status OUT NOCOPY VARCHAR2);
47 -- Bug 2701147 END
48
49 PROCEDURE create_award_distribution; --<GRANTS FPJ>
50
51 --<Encumbrance FPJ>
52 PROCEDURE CREATE_RELEASE_DISTRIBUTION(
53 req_line IN requisition_lines_cursor%rowtype
54 , p_req_enc_flag IN VARCHAR2
55 );
56
57
58 PROCEDURE CREATE_RELEASES
59 IS
60 x_old_po_header_id number := 0;
61 po_req_lines requisition_lines_cursor%rowtype;
62 old_po_req_line requisition_lines_cursor%rowtype;
63 x_old_doc_generation_method po_autosource_documents.doc_generation_method%type;
64 x_kanban_return_status VARCHAR2(10) := '';
65 x_return_code VARCHAR2(25);
66 x_tax_status VARCHAR2(10);
67 l_encode varchar2(2000);
68 x_error_msg varchar2(2000);
69 x_no_convert_flag varchar2(1);
70 x_uom_convert varchar2(2) := fnd_profile.value('PO_REQ_BPA_UOM_CONVERT');
71 x_req_num varchar2(20);
72 x_req_line_num number;
73 /* Supplier PCard FPH */
74 x_old_pcard_id number;
75
76 l_check_status VARCHAR2(1); -- Bug 2701147
77 l_api_name CONSTANT varchar2(40) := 'CREATE_RELEASES';
78
79 --bug2880298 start
80
81 l_req_enc_flag financials_system_parameters.req_encumbrance_flag%TYPE;
82 l_enf_vendor_hold_flag po_system_parameters.enforce_vendor_hold_flag%TYPE;
83
84 -- bug2880298 end
85
86 -- <FPJ Refactor Archiving API>
87 l_return_status varchar2(1) ;
88 l_msg_count NUMBER := 0;
89 l_msg_data VARCHAR2(2000);
90 --<R12 eTax Integration Start>
91 l_tax_return_status VARCHAR2(1);
92 --<R12 eTax Integration End>
93
94 l_progress VARCHAR2(3) := '000'; -- Bug 3570793
95 BEGIN
96
97 --bug2880298
98 -- get necessary information from fsp and posp and pass them to the
99 -- cursor requisition_lines_cursor1 rather than getting the values
100 -- from the cursor to improve performance and avoid catesian joins
101 -- in a large query.
102
103 SELECT FSP.INVENTORY_ORGANIZATION_ID,
104 POSP.EXPENSE_ACCRUAL_CODE,
105 NVL(FSP.req_encumbrance_flag, 'N'), --bug2880298
106 NVL(POSP.enforce_vendor_hold_flag, 'N') --bug2880298
107 INTO x_inventory_org_id,
108 x_expense_accrual_code,
109 l_req_enc_flag, --bug2880298
110 l_enf_vendor_hold_flag --bug2880298
111 FROM FINANCIALS_SYSTEM_PARAMETERS FSP,
112 PO_SYSTEM_PARAMETERS POSP;
113
114 IF SQL%NOTFOUND THEN
115 RAISE NO_DATA_FOUND;
116 END IF;
117
118 l_progress := '010';
119
120 BEGIN -- Added for Bug #2206125, the following code was always
121 -- returning the period name irrespective of PO encumbered flag
122 -- so adding a condition from FSP to check if encumbrance in On.
123
124 SELECT GPS.PERIOD_NAME
125 INTO x_period_name
126 FROM GL_PERIOD_STATUSES GPS,
127 FINANCIALS_SYSTEM_PARAMETERS FSP
128 WHERE GPS.APPLICATION_ID = 101
129 AND GPS.SET_OF_BOOKS_ID = FSP.SET_OF_BOOKS_ID
130 AND GPS.ADJUSTMENT_PERIOD_FLAG = 'N'
131 AND TRUNC(SYSDATE) BETWEEN TRUNC(GPS.START_DATE)
132 AND TRUNC(GPS.END_DATE)
133 AND NVL(FSP.PURCH_ENCUMBRANCE_FLAG, 'N') = 'Y'; -- Bug #2206125
134
135 IF SQL%NOTFOUND THEN
136 RAISE NO_DATA_FOUND;
137 END IF;
138
139 EXCEPTION -- Bug #2206125
140 WHEN NO_DATA_FOUND THEN
141 x_period_name := '';
142 END; -- Bug #2206125
143
144 -- dbms_output.put_line ('Before Open Cursor1');
145
146 -- bug2880298
147 -- added two parameters for the following cursor
148
149 l_progress := '020';
150
151 open requisition_lines_cursor1 (l_req_enc_flag,
152 l_enf_vendor_hold_flag);
153
154 -- dbms_output.put_line ('After Open Cursor1');
155
156 loop
157 l_progress := '030';
158
159 fetch requisition_lines_cursor1 into po_req_lines;
160 exit when requisition_lines_cursor1%notfound;
161
162 /* Enh 1660036 : If the req uom and po uom are different and the
163 convert uom profile is not set to yes we set the no convert flag to Y
164 so that release creation does not happen for this req.*/
165
166 x_no_convert_flag := 'N';
167
168 select segment1 into x_req_num
169 from po_requisition_headers
170 where requisition_header_id = po_req_lines.requisition_header_id;
171
172 select line_num into x_req_line_num
173 from po_requisition_lines
174 where requisition_line_id = po_req_lines.requisition_line_id;
175
176 IF ( po_req_lines.req_uom <> po_req_lines.po_uom ) AND
177 nvl(x_uom_convert,'Y') <> 'Y' THEN
178
179 /* if the profile is unset we take it as yes because we want
180 the create releases to behave as earlier */
181
182 fnd_message.set_name ('PO', 'PO_REQ_BPA_CONVERT_UOM');
183 fnd_message.set_token('REQ', x_req_num);
184 fnd_message.set_token('LINE', x_req_line_num);
185 x_error_msg := fnd_message.get;
186 fnd_file.put_line(fnd_file.log,x_error_msg);
187 x_no_convert_flag := 'Y';
188
189 END IF;
190
191 l_progress := '040';
192
193 /* Enh 1660036 : proceed with the rest of the release creation only if
194 the no convert flag has not been set */
195 IF x_no_convert_flag = 'N' THEN
196 IF (x_old_po_header_id <> po_req_lines.blanket_po_header_id OR
197 x_old_doc_generation_method <> po_req_lines.doc_generation_method OR
198 /* Supplier PCard FPH */
199 nvl(x_old_pcard_id,0) <> nvl(po_req_lines.pcard_id,0))
200 THEN
201
202 IF(x_old_po_header_id <> 0) THEN
203 --<R12 eTax Integration Start>
204 l_tax_return_status := NULL;
205 po_tax_interface_pvt.calculate_tax(x_return_status => l_tax_return_status,
206 p_po_header_id => NULL,
207 p_po_release_id => x_po_release_id,
208 p_calling_program =>'PORELGEB');
209 l_progress := '042';
210
211 IF l_tax_return_status <> FND_API.G_RET_STS_SUCCESS THEN
212 IF (g_debug_stmt) THEN
213 PO_DEBUG.debug_stmt (
214 p_log_head => c_log_head||l_api_name,
215 p_token => '',
216 p_message => 'Error in tax calcualtion'
217 ||' po_release_id: '||x_po_release_id
218 );
219 END IF;
220 FOR i IN 1..po_tax_interface_pvt.g_tax_errors_tbl.message_text.COUNT LOOP
221 fnd_file.put_line(FND_FILE.LOG, po_tax_interface_pvt.g_tax_errors_tbl.message_text(i) );
222 END LOOP;
223 END IF;
224 l_progress := '043';
225
226 --<R12 eTax Integration End>
227
228 IF (x_authorization_status = 'APPROVED') THEN
229
230 --<R12 eTax Integration Start>
231 IF l_tax_return_status <> FND_API.G_RET_STS_SUCCESS THEN
232 --
233 -- ECO Bug 4643026
234 -- Update Release with status INCOMPLETE
235 -- as the tax calculation has failed
236 --
237
238 /*Bug 7609663: Update the approved_flag in po_releses_all and po_line_locations_all */
239
240 UPDATE po_releases_all por
241 SET por.authorization_status = 'INCOMPLETE',
242 por.approved_flag = 'N'
243 WHERE por.po_release_id = x_po_release_id;
244
245 UPDATE po_line_locations_all plla
246 SET plla.approved_flag = 'N'
247 WHERE plla.po_release_id = x_po_release_id;
248
249 /* Bug 7609663: end */
250
251 x_authorization_status := 'INCOMPLETE';
252 ELSE
253 --<R12 eTax Integration End>
254 l_progress := '050';
255
256 -- Support for Kanban Execution
257 /* Bug# 2485087, Moved kanban execution here where all code and
258 logic related to after creating the releases was there. With the
259 earlier Code we were having problems with treating the Release
260 creation and Kanban updation as one transaction and if the next
261 Releases needed to be Rolled back we also lost the Kanban Card
262 Updation for the previous Release. */
263
264 PO_KANBAN_SV.Update_Card_Status ('IN_PROCESS',
265 'RELEASE',
266 x_po_release_id,
267 x_kanban_return_status);
268
269 -- <FPJ Refactor Archiving API START>
270 PO_DOCUMENT_ARCHIVE_GRP.Archive_PO(
271 p_api_version => 1.0,
272 p_document_id => x_po_release_id,
273 p_document_type => 'RELEASE',
274 p_document_subtype => 'BLANKET', -- Not really needed
275 p_process => 'APPROVE',
276 x_return_status => l_return_status,
277 x_msg_count => l_msg_count,
278 x_msg_data => l_msg_data);
279
280 IF (l_return_status <> 'S') THEN
281 APP_EXCEPTION.Raise_Exception;
282 END IF;
283
284 -- <FPJ Refactor Archiving API END>
285
286 WRAPUP(old_po_req_line);
287
288 l_progress := '060';
289 -- Bug 3570793 START
290 -- Moved the call here so that it happens for every approved
291 -- release that is created.
292 PO_RESERVATION_MAINTAIN_SV.maintain_reservation (
293 p_header_id => x_po_release_id,
294 p_line_id => 0,
295 p_line_location_id => 0,
296 p_distribution_id => 0,
297 p_action => 'Approve_Blanket_Release_Supply',
298 p_recreate_demand_flag => 'N',
299 p_called_from_reqimport => 'N',
300 x_return_status => l_return_status
301 );
302 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
303 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
304 END IF;
305 -- Bug 3570793 END
306
307 l_progress := '070';
308 -- <INBOUND LOGISTICS FPJ START>
309 create_deliveryrecord(p_release_id => x_po_release_id);
310 -- <INBOUND LOGISTICS FPJ END>
311
312 l_progress := '080';
313 -- Bug 2701147 START
314 -- Retrieve the req number and req line number for the
315 -- previous release created.
316 select segment1 into x_req_num
317 from po_requisition_headers
318 where requisition_header_id = old_po_req_line.requisition_header_id;
319
320
321 select line_num into x_req_line_num
322 from po_requisition_lines
323 where requisition_line_id = old_po_req_line.requisition_line_id;
324
325
326
327 -- If the release fails any of the pre-approval checks,
328 -- rollback its creation, but proceed with the creation of
329 -- the next release.
330
331 preapproval_checks(x_old_po_header_id,
332 x_req_num,x_req_line_num,
333 l_check_status);
334
335 IF (l_check_status = FND_API.G_RET_STS_ERROR) THEN
336 -- Rollback the creation of the release.
337 ROLLBACK TO PORELGEN_1;
338 -- Bug 3570793 START
339 IF (g_debug_stmt) THEN
340 PO_DEBUG.debug_stmt (
341 p_log_head => c_log_head||l_api_name,
342 p_token => '',
343 p_message => 'Preapproval checks failed; rollback'
344 ||' po_release_id: '||x_po_release_id
345 );
346 END IF;
347 -- Bug 3570793 END
348 --<BUG 7685164 Added as part of LCM ER START>
349 -- Assigning check status as unexpected error to make sure that if the release
350 -- fails in LCM submission checks then we should create the release with incomplete status.
351 ELSIF (l_check_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
352
353 --<BUG 7721295 Need to revert the approved date and LCM flag if the
354 -- LCM submission check fails>
355 UPDATE po_releases_all por
356 SET por.authorization_status = 'INCOMPLETE',
357 por.approved_flag = 'N',
358 por.approved_date = NULL
359 WHERE por.po_release_id = x_po_release_id;
360
361 UPDATE po_line_locations_all plla
362 SET plla.approved_flag = 'N',
363 plla.approved_date = NULL,
364 plla.lcm_flag = NULL
365 WHERE plla.po_release_id = x_po_release_id
366 AND plla.lcm_flag = 'Y';
367
368 UPDATE po_distributions_all pda
369 SET pda.lcm_flag = NULL
370 WHERE pda.line_location_id = x_line_location_id
371 AND pda.lcm_flag = 'Y';
372
373 IF (g_debug_stmt) THEN
374 PO_DEBUG.debug_stmt (p_log_head => c_log_head||l_api_name,
375 p_token => '',
376 p_message => 'Preapproval checks for LCM features failed'
377 ||' po_release_id: '||x_po_release_id);
378 END IF;
379 --<BUG 7685164 END>
380 END IF;
381 -- Bug 2701147 END
382 END IF; -- tax calculation failed for approved release
383 ELSE -- authorization_status <> 'APPROVED'
384 WRAPUP(old_po_req_line);
385 END IF; -- authorization_status <> 'APPROVED'
386 END IF; -- old_header_id <> 0
387
388 SAVEPOINT PORELGEN_1;
389 CREATE_RELEASE_HEADER (po_req_lines);
390
391 END IF; --old_header_id <> rec.header_id
392
393 l_progress := '100';
394
395 CREATE_RELEASE_SHIPMENT (po_req_lines);
396 CREATE_RELEASE_DISTRIBUTION (po_req_lines,l_req_enc_flag);
397
398 --<BUG 7721295 Added as part of the LCM ER>
399 PO_DOCUMENT_CHECKS_PVT.set_lcm_flag(x_line_location_id,'AFTER',l_return_status);
400
401
402 /* ecso 11/18/97 R11 OE drop ship call back */
403 OE_DROP_SHIP (po_req_lines);
404
405 --Moved Kanban Execution to the Top
406
407 l_progress := '110';
408
409 /*
410 Bug 5973123
411 When a req is autocreated to PO, the correct supply manipulation order is,
412 Req Creation
413 Req Deletion
414 PO Creation
415
416 The CLOSE PO API does not delete the backing requisition supply. It just
417 creates the PO supply if it does not exist. This causes supply manipulation
418 order to go out of sync. Hence now moving the supply creation code before
419 the close PO API. The MAINTAIN_SUPPLY will delete the req supply first and
420 create the PO Supply. Later the Close PO API, would delete the supply if
421 required. Thus the supply manipulation order is maintained.
422 */
423
424 MAINTAIN_SUPPLY (po_req_lines);
425
426 l_progress := '115';
427
428 /* Bug 724170 GMudgal 12/02/98.
429 ** Call the po_actions.close_po plsql routine to set the closed
430 ** code at the shipment level and consequently rollup to the lines
431 ** and headers. This was done because, in case the matching for the
432 ** release is set to 2-way and the receipt close tolerance is 100% then
433 ** the release shipment should be closed for receiving when it is
434 ** created. This will enable AP to close the shipment (finally close
435 ** in case of final match) when an invoice is matched. The rolled up
436 ** state for lines and header will be CLOSED only for both match and
437 ** final match.
438 */
439 IF (NOT(PO_ACTIONS.Close_PO(x_po_release_id,
440 'RELEASE',
441 'BLANKET',
442 po_req_lines.blanket_po_line_id,
443 x_line_location_id,
444 'UPDATE_CLOSE_STATE',
445 NULL, -- p_reason
446 'PO', -- p_calling_mode
447 'N',
448 x_return_code,
449 'Y'))) THEN
450 APP_EXCEPTION.Raise_Exception;
451 END IF ;
452
453 /* 858071 - SVAIDYAN : Call maintain_supply after po_actions.close_po so
454 that supply is created correctly.
455 */
456
457 l_progress := '120';
458 /* Bug 5973123 MAINTAIN_SUPPLY (po_req_lines); */
459
460 x_old_po_header_id := po_req_lines.blanket_po_header_id;
461 x_old_doc_generation_method := po_req_lines.doc_generation_method;
462 /* Supplier PCard FPH */
463 x_old_pcard_id := po_req_lines.pcard_id;
464 old_po_req_line := po_req_lines;
465
466 END IF;
467
468 end loop;
469
470
471 -- bug 589727
472 -- Bug# 2485087, Moved kanban execution from here and did
473 -- it alone with Archive logic below
474
475 IF (x_old_po_header_id <> 0)
476 THEN
477 -- sjadhav, added tax call
478 --<R12 eTax Integration Start>
479 l_tax_return_status := NULL;
480 po_tax_interface_pvt.calculate_tax(x_return_status => l_tax_return_status,
481 p_po_header_id => NULL,
482 p_po_release_id => x_po_release_id,
483 p_calling_program =>'PORELGEB');
484 l_progress := '042';
485 IF l_tax_return_status <> FND_API.G_RET_STS_SUCCESS THEN
486 IF (g_debug_stmt) THEN
487 PO_DEBUG.debug_stmt (
488 p_log_head => c_log_head||l_api_name,
489 p_token => '',
490 p_message => 'Error in tax calcualtion'
491 ||' po_release_id: '||x_po_release_id
492 );
493 END IF;
494 FOR i IN 1..po_tax_interface_pvt.g_tax_errors_tbl.message_text.COUNT LOOP
495 fnd_file.put_line(FND_FILE.LOG, po_tax_interface_pvt.g_tax_errors_tbl.message_text(i) );
496 END LOOP;
497 END IF;
498 l_progress := '043';
499
500 --<R12 eTax Integration End>
501
502 IF(x_authorization_status = 'APPROVED') THEN
503 l_progress := '200';
504 --<R12 eTax Integration Start>
505 IF l_tax_return_status <> FND_API.G_RET_STS_SUCCESS THEN
506 -- sjadhav,
507 -- ECO Bug 4643026
508 -- Update Release with status INCOMPLETE
509 -- as the tax calculation has failed
510 --
511 /*Bug 7609663: Update the approved_flag in po_releses_all and po_line_locations_all */
512
513 UPDATE po_releases_all por
514 SET por.authorization_status = 'INCOMPLETE',
515 por.approved_flag = 'N'
516 WHERE por.po_release_id = x_po_release_id;
517
518 UPDATE po_line_locations_all plla
519 SET plla.approved_flag = 'N'
520 WHERE plla.po_release_id = x_po_release_id;
521
522 /* Bug 7609663: end */
523 ELSE
524 --<R12 eTax Integration End>
525
526 --Moved Kanban Execution here
527 --Bug# 2485087, Updating Kanban for Last Release Created.
528 PO_KANBAN_SV.Update_Card_Status ('IN_PROCESS',
529 'RELEASE',
530 x_po_release_id,
531 x_kanban_return_status);
532 -- <FPJ Refactor Archiving API START>
533 PO_DOCUMENT_ARCHIVE_GRP.Archive_PO(p_api_version => 1.0,
534 p_document_id => x_po_release_id,
535 p_document_type => 'RELEASE',
536 p_document_subtype => 'BLANKET', -- Not really needed
537 p_process => 'APPROVE',
538 x_return_status => l_return_status,
539 x_msg_count => l_msg_count,
540 x_msg_data => l_msg_data);
541
542 IF (l_return_status <> 'S') THEN
543 APP_EXCEPTION.Raise_Exception;
544 END IF;
545
546 -- PO_RELGEN_PKG1.ARCHIVE_RELEASE(x_po_release_id);
547 -- <FPJ Refactor Archiving API END>
548
549 WRAPUP(old_po_req_line);
550
551 l_progress := '210';
552 -- Bug 3570793 START
553 -- Moved the call here so that it happens for every approved
554 -- release that is created.
555 PO_RESERVATION_MAINTAIN_SV.maintain_reservation (
556 p_header_id => x_po_release_id,
557 p_line_id => 0,
558 p_line_location_id => 0,
559 p_distribution_id => 0,
560 p_action => 'Approve_Blanket_Release_Supply',
561 p_recreate_demand_flag => 'N',
562 p_called_from_reqimport => 'N',
563 x_return_status => l_return_status
564 );
565 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
566 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
567 END IF;
568 -- Bug 3570793 END
569
570 l_progress := '220';
571 -- <INBOUND LOGISTICS FPJ START>
572 create_deliveryrecord(p_release_id => x_po_release_id);
573 -- <INBOUND LOGISTICS FPJ END>
574
575 l_progress := '230';
576 -- Bug 2701147 START
577 select segment1 into x_req_num
578 from po_requisition_headers
579 where requisition_header_id = old_po_req_line.requisition_header_id;
580
581
582 select line_num into x_req_line_num
583 from po_requisition_lines
584 where requisition_line_id = old_po_req_line.requisition_line_id;
585
586
587 -- If the release fails any of the pre-approval checks, rollback
588 -- its creation.
589 preapproval_checks(x_old_po_header_id,x_req_num,x_req_line_num,
590 l_check_status);
591
592 IF (l_check_status = FND_API.G_RET_STS_ERROR) THEN
593 -- Rollback the creation of the release.
594 ROLLBACK TO PORELGEN_1;
595 -- Bug 3570793 START
596 IF (g_debug_stmt) THEN
597 PO_DEBUG.debug_stmt (
598 p_log_head => c_log_head||l_api_name,
599 p_token => '',
600 p_message => 'Preapproval checks failed; rollback'
601 ||' po_release_id: '||x_po_release_id
602 );
603 END IF;
604 -- Bug 3570793 END
605 --<BUG 7685164 Added as part of LCM ER START>
606 -- Assigning check status as unexpected error to make sure that if the release
607 -- fails in LCM submission checks then we should create the release with incomplete status.
608 ELSIF (l_check_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
609
610 --<BUG 7721295 Need to revert the approved date and LCM flag if the
611 -- LCM submission check fails>
612 UPDATE po_releases_all por
613 SET por.authorization_status = 'INCOMPLETE',
614 por.approved_flag = 'N',
615 por.approved_date = NULL
616 WHERE por.po_release_id = x_po_release_id;
617
618 UPDATE po_line_locations_all plla
619 SET plla.approved_flag = 'N',
620 plla.approved_date = NULL,
621 plla.lcm_flag = NULL
622 WHERE plla.po_release_id = x_po_release_id
623 AND plla.lcm_flag = 'Y';
624
625 UPDATE po_distributions_all pda
626 SET pda.lcm_flag = NULL
627 WHERE pda.line_location_id = x_line_location_id
628 AND pda.lcm_flag = 'Y';
629
630 IF (g_debug_stmt) THEN
631 PO_DEBUG.debug_stmt (p_log_head => c_log_head||l_api_name,
632 p_token => '',
633 p_message => 'Preapproval checks for LCM features failed'
634 ||' po_release_id: '||x_po_release_id);
635 END IF;
636 --<BUG 7685164 END>
637 END IF;
638 -- Bug 2701147 END
639 END IF; -- tax calculation failed for approved release
640 ELSE -- authorization_status <> 'APPROVED'
641 WRAPUP(old_po_req_line);
642 END IF; -- authorization_status <> 'APPROVED'
643 END IF; -- old_header_id <> 0
644
645 l_progress := '140';
646 PO_RELGEN_PKG1.MRP_SUPPLY;
647 /*
648 Bug # 1995964 KPERIASA
649 Description : Spares Management Project. Support for reservation
650 within purchasing. Modified porelgeb.pls to add a call to
651 PO_RESERVATION_MAINTAIN_SV.maintain_reservation
652 */
653 -- Bug 3570793 Moved the maintain_reservation call to inside the loop,
654 -- so that it is called for each approved release that is created.
655
656 close requisition_lines_cursor1;
657
658 commit;
659
660 return;
661
662 EXCEPTION
663 -- Bug 3570793 START
664 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
665 -- Write the errors on the message list to the concurrent program log.
666 PO_DEBUG.write_msg_list_to_file (
667 p_log_head => c_log_head || l_api_name,
668 p_progress => l_progress
669 );
670 raise_application_error(-20001,sqlerrm||'---'||msgbuf);
671 -- Bug 3570793 END
672
673 WHEN OTHERS THEN
674 -- dbms_output.put_line ('In exception');
675 -- Bug 2701147 START
676 FND_FILE.put_line(FND_FILE.LOG,
677 c_log_head || l_api_name || '.' || l_progress
678 || ' exception; SQL code: ' || sqlcode);
679 -- Bug 2701147 END
680 raise_application_error(-20001,sqlerrm||'---'||msgbuf);
681 END CREATE_RELEASES;
682
683
684
685
686 /* ============================================================================
687 NAME: CREATE_RELEASE_HEADER
688 DESC: Create a new release header
689 ARGS: IN : req_line IN requisition_lines_cursor%rowtype
690 ALGR: Determine authorization status of release to be created
691 Create release header
692
693 ===========================================================================*/
694
695 PROCEDURE CREATE_RELEASE_HEADER(req_line IN requisition_lines_cursor%rowtype)
696 IS
697 l_api_name CONSTANT VARCHAR2(30) := 'create_release_header';
698
699 x_release_num number := 0;
700 x_purch_encumbrance_flag varchar2(1) := 'N';
701 x_pay_on_code varchar2(25) := '';
702 x_shipping_control varchar2(30) := ''; /* Bug 6454219 */
703 x_acceptance_required_flag po_system_parameters.acceptance_required_flag%TYPE; /*Bug7668178*/
704
705 BEGIN
706
707 SELECT NVL(PURCH_ENCUMBRANCE_FLAG,'N')
708 INTO x_purch_encumbrance_flag
709 FROM FINANCIALS_SYSTEM_PARAMETERS;
710
711 /*Bug7668178: Get the default Acceptance Required Flag value from PO System Parameters */
712
713 SELECT Decode(acceptance_required_flag,'D','Y','Y','Y','S','Y','N')
714 INTO x_acceptance_required_flag
715 FROM po_system_parameters;
716
717
718 IF (req_line.doc_generation_method = 'CREATE_AND_APPROVE' AND
719 x_purch_encumbrance_flag = 'N')
720 THEN
721 x_authorization_status := 'APPROVED';
722 x_acceptance_required_flag := NULL; /*Bug7668178: Set Acceptance Required Flag to NULL when generation method is Automatic Release */
723 ELSE
724 x_authorization_status := 'INCOMPLETE';
725 END IF;
726
727 SELECT PO_RELEASES_S.NEXTVAL
728 INTO x_po_release_id
729 FROM SYS.DUAL;
730
731 /* Bug 1834138. pchintal. Code for calculating the shipment number.
732 This was done as a part of improving the performance of the create
733 releases process.
734 */
735
736 IF (Gpo_release_id_prev <> x_po_release_id) then
737 Gpo_release_id_prev := x_po_release_id;
738 Gship_num_prev := 0;
739 END IF;
740
741 SELECT NVL(MAX(RELEASE_NUM) +1,1)
742 INTO x_release_num
743 FROM PO_RELEASES
744 WHERE PO_HEADER_ID = req_line.blanket_po_header_id;
745
746 /* Bug 6454219 Added code to fetch shipping_control from the BPA.
747 Same would be inserted into po_releases_all table */
748 SELECT PAY_ON_CODE, SHIPPING_CONTROL
749 INTO x_pay_on_code, x_shipping_control
750 FROM PO_HEADERS
751 WHERE PO_HEADER_ID = req_line.blanket_po_header_id;
752
753 -- Bug 3570793 START
754 IF (g_debug_stmt) THEN
755 PO_DEBUG.debug_stmt (
756 p_log_head => c_log_head||l_api_name,
757 p_token => '',
758 p_message => 'Create release header;'
759 ||' req_line_id: '||req_line.requisition_line_id
760 ||', po_release_id: '||x_po_release_id
761 );
762 END IF;
763 -- Bug 3570793 END
764
765 INSERT INTO PO_RELEASES
766 (PO_RELEASE_ID,
767 LAST_UPDATE_DATE,
768 LAST_UPDATED_BY,
769 PO_HEADER_ID,
770 RELEASE_NUM,
771 PCARD_ID, --Supplier Pcard FPH
772 AGENT_ID,
773 RELEASE_DATE,
774 CREATION_DATE,
775 CREATED_BY,
776 LAST_UPDATE_LOGIN,
777 REVISION_NUM,
778 APPROVED_FLAG,
779 APPROVED_DATE,
780 AUTHORIZATION_STATUS,
781 PRINT_COUNT,
782 CANCEL_FLAG,
783 RELEASE_TYPE,
784 PAY_ON_CODE,
785 GOVERNMENT_CONTEXT,
786 DOCUMENT_CREATION_METHOD, -- PO DBI FPJ
787 ORG_ID, -- <R12 MOAC>
788 tax_attribute_update_code, --< eTax Integration R12>
789 SHIPPING_CONTROL, /* Bug 6454219 */
790 ACCEPTANCE_REQUIRED_FLAG -- Bug 7668178
791 )
792 VALUES (x_po_release_id, -- :po_release_id
793 sysdate,
794 req_line.last_updated_by, -- :cpo_last_updated_by
795 req_line.blanket_po_header_id, -- :po_header_id
796 x_release_num, -- :release_num
797 req_line.pcard_id, -- :pcard_id Supplier Pcard FPH
798 req_line.agent_id, -- :agent_id
799 SYSDATE, -- <Action Date TZ FPJ>
800 sysdate,
801 req_line.last_updated_by, -- :cpo_last_updated_by
802 req_line.last_update_login, -- :last_update_login,
803 0,
804 DECODE(x_authorization_status,
805 'APPROVED','Y','N'), -- 'N'
806 DECODE(x_authorization_status,
807 'APPROVED', sysdate, NULL), -- approved date
808 x_authorization_status, -- :'INCOMPLETE'
809 0,
810 'N',
811 'BLANKET',
812 x_pay_on_code,
813 null, -- :government_context
814 -- Bug 3648268 Use lookup code instead of hardcoded value
815 'CREATE_RELEASES', -- Document Creation Method PO DBI FPJ
816 req_line.org_id, -- <R12 MOAC>
817 'CREATE', --<eTax integration R12>
818 x_shipping_control, /* Bug 6454219 */
819 x_acceptance_required_flag -- Bug7668178
820 );
821
822 EXCEPTION
823
824 WHEN OTHERS THEN
825 raise_application_error(-20001,sqlerrm||'---'||msgbuf);
826
827 END CREATE_RELEASE_HEADER;
828
829
830 /* ============================================================================
831 NAME: CREATE_RELEASE_SHIPMENT
832 DESC: Create a new release shipment
833 ARGS: IN : req_line IN requisition_lines_cursor%rowtype
834 ALGR: Determine ship-to-location and tax information
835 Create new release shipment
836 Associate req line to the shipment created
837
838 ===========================================================================*/
839
840 PROCEDURE CREATE_RELEASE_SHIPMENT(req_line IN requisition_lines_cursor%rowtype)
841 IS
842 l_api_name CONSTANT VARCHAR2(30) := 'create_release_shipment';
843
844 x_ship_to_location_id number := 0;
845 x_taxable_flag po_system_parameters.taxable_flag%type;
846 x_shipment_num number := 0;
847 rcv_controls rcv_control_type;
848 x_conversion_rate number := 1;
849 x_best_price number :=0;
850 x_ext_precision number :=5;
851
852 --<INVCONV R12 START>
853 x_secondary_unit_of_measure mtl_units_of_measure.unit_of_measure%type;
854 x_secondary_quantity number;
855 x_secondary_uom_code mtl_units_of_measure.uom_code%type;
856 --<INVCONV R12 END>
857
858 x_invoice_match_option varchar2(25); -- bgu, Dec. 11, 98
859 --frkhan 1/12/99
860 x_country_of_origin_code varchar2(2);
861
862 /* <TIMEPHASED FPI START> */
863 l_quantity po_requisition_lines_all.quantity%TYPE := null;
864 l_price_break_type varchar2(1) := null;
865 l_po_line_id po_lines_all.po_line_id%TYPE := null;
866 l_cumulative_flag boolean;
867 l_dest_org_id po_requisition_lines_all.destination_organization_id%TYPE := null;
868 l_need_by_date po_requisition_lines_all.need_by_date%TYPE := null;
869 /* <TIMEPHASED FPI END> */
870
871 --<Bug# 3293109 START>
872 l_promised_date DATE := NULL;
873 l_po_promised_def_prf VARCHAR2(1) := fnd_profile.value('PO_NEED_BY_PROMISE_DEFAULTING');
874 --<Bug# 3293109 END>
875
876 l_outsourced_assembly PO_LINE_LOCATIONS_ALL.outsourced_assembly%TYPE;
877 l_value_basis PO_LINES_ALL.order_type_lookup_code%TYPE; --Bug 4896950
878 l_matching_basis PO_LINES_ALL.matching_basis%TYPE; --Bug 4896950
879 l_unit_meas_lookup_code PO_LINES_ALL.unit_meas_lookup_code%TYPE; --Bug 4896950
880
881 --<BUG 7685164 START>
882 l_return_status VARCHAR2(1);
883 l_msg_count NUMBER;
884 l_msg_data VARCHAR2(2000);
885 l_msg_buf VARCHAR2(2000);
886 l_progress VARCHAR2(3) := '001';
887 --<BUG 7685164 END>
888
889 BEGIN
890
891 --<BUG 7685164 START>
892 l_return_status := FND_API.G_RET_STS_SUCCESS;
893 IF (g_fnd_debug = 'Y') THEN
894 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
895 FND_LOG.string (
896 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
897 MODULE => c_log_head || '.'||l_api_name||'.' || l_progress,
898 MESSAGE => 'Start create release shipments'
899 );
900 END IF;
901 END IF;
902 --<BUG 7685164 END>
903
904 SELECT PO_LINE_LOCATIONS_S.NEXTVAL
905 INTO x_line_location_id
906 FROM SYS.DUAL;
907
908 /* Bug 1834138. pchintal. Commented the below SQL and added new Code
909 for calculating the shipment number. This was done as a part of
910 improving the performance of the create releases process.
911
912 SELECT NVL(MAX(SHIPMENT_NUM) +1,1)
913 INTO x_shipment_num
914 FROM PO_LINE_LOCATIONS
915 WHERE PO_RELEASE_ID = x_po_release_id;
916 */
917
918 Gship_num_prev :=Gship_num_prev+1;
919 x_shipment_num := Gship_num_prev;
920
921 /* pchintal Code changes end for bug 1834138 */
922
923 /* Bug 1942696 */
924
925 begin
926
927 SELECT NVL(SHIP_TO_LOCATION_ID,LOCATION_ID)
928 INTO x_ship_to_location_id
929 FROM HR_LOCATIONS
930 WHERE LOCATION_ID = req_line.deliver_to_location_id;
931
932 exception
933
934 when no_data_found then
935
936 /* Check validity against HZ_LOCATIONS */
937
938 begin
939 select location_id
940 into x_ship_to_location_id
941 FROM HZ_LOCATIONS
942 where location_id = req_line.deliver_to_location_id;
943
944 exception
945 when no_data_found then
946
947 null;
948 end;
949
950 end;
951
952 --<BUG 7685164 START>
953 l_progress := '002';
954 IF (g_fnd_debug = 'Y') THEN
955 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
956 FND_LOG.string (
957 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
958 MODULE => c_log_head || '.'||l_api_name||'.' || l_progress,
959 MESSAGE => x_ship_to_location_id
960 );
961 END IF;
962 END IF;
963 --<BUG 7685164 END>
964
965 GET_RCV_CONTROLS(req_line,rcv_controls);
966
967 /** bgu, Dec. 11, 98
968 * Default match option form vendor site, vendor, down to
969 * financial system parameters
970 */
971 GET_INVOICE_MATCH_OPTION(req_line, x_invoice_match_option);
972
973
974 l_quantity := req_line.quantity; /* <TIMEPHASED FPI> */
975
976 IF ( req_line.req_uom <> req_line.po_uom ) THEN
977 /*Bug1635257
978 The po_uom_convert function was returning a very high precision
979 after converting units while creating PO from requisitions
980 in autocreate. Thus the value being returned by the function
981 call was rounded off to 5 digits after decimal to prevent
982 such high prcision to be generated.
983 */
984 /* bug 1921133
985 If we round the conversion rate as done in the above bugfix there will
986 be inaccuracies in the final quantity. so we need to round the quantity
987 after muliplying with the rate intead of the rate itself */
988
989 /* <TIMEPHASED FPI START> */
990
991 -- bug2763177
992 -- should not comment out derivation of x_conversion_rate as it is
993 -- used for quantity conversion
994
995 x_conversion_rate := po_uom_s.po_uom_convert(req_line.req_uom,
996 req_line.po_uom,
997 req_line.item_id);
998
999 /* Bug 2758378 - The parameters for uom_convert was in the wrong order
1000 which resulted in Create Releases program to complete with error
1001 exiting with status 1. Interchanged the req_line.item_id and the
1002 req_line.po_uom parameters */
1003
1004 po_uom_s.uom_convert(req_line.quantity,
1005 req_line.req_uom,
1006 req_line.item_id,
1007 req_line.po_uom,
1008 l_quantity);
1009 /* <TIMEPHASED FPI END> */
1010
1011 END IF;
1012
1013 --<INVCONV R12 START>
1014 --Ensure secondary qty/UOM populated if appropriate
1015 x_secondary_unit_of_measure := req_line.secondary_unit_of_measure;
1016 x_secondary_quantity := req_line.secondary_quantity;
1017
1018 IF x_secondary_quantity is NULL and req_line.item_id is NOT NULL THEN
1019 PO_UOM_S.get_secondary_uom( req_line.item_id,
1020 req_line.destination_organization_id,
1021 x_secondary_uom_code,
1022 x_secondary_unit_of_measure);
1023
1024 IF x_secondary_unit_of_measure is NOT NULL THEN
1025 po_uom_s.uom_convert(req_line.quantity,
1026 req_line.req_uom,
1027 req_line.item_id,
1028 x_secondary_unit_of_measure,
1029 x_secondary_quantity);
1030 ELSE
1031 x_secondary_quantity := NULL;
1032 x_secondary_unit_of_measure := NULL;
1033 END IF;
1034 END IF;
1035 --<INVCONV R12 END>
1036
1037 /* <TIMEPHASED FPI START> */
1038 l_po_line_id := req_line.blanket_po_line_id;
1039 l_dest_org_id := req_line.destination_organization_id;
1040 l_need_by_date := req_line.need_by_date;
1041
1042 BEGIN
1043 --Bug 4896950: added value/matching basis, uom to select below
1044 select decode(price_break_lookup_code, 'CUMULATIVE', 'Y', 'N'),
1045 order_type_lookup_code,
1046 matching_basis,
1047 unit_meas_lookup_code
1048 into l_price_break_type,
1049 l_value_basis,
1050 l_matching_basis,
1051 l_unit_meas_lookup_code
1052 from po_lines_all
1053 where po_line_id = l_po_line_id;
1054 EXCEPTION
1055 when others then
1056 null;
1057 END;
1058
1059 if (l_price_break_type = 'Y') then
1060 l_cumulative_flag := TRUE;
1061 else
1062 l_cumulative_flag := FALSE;
1063 end if;
1064
1065 x_best_price := po_sourcing2_sv.get_break_price(l_quantity,
1066 l_dest_org_id,
1067 x_ship_to_location_id,
1068 l_po_line_id,
1069 l_cumulative_flag,
1070 l_need_by_date,
1071 p_req_line_price => req_line.unit_price);--bug 8845486: Passing parameter
1072 --to the argument p_req_line_price);
1073 -- Bug 12844276
1074
1075 /* Commented off for TIMEPHASED
1076 x_best_price := GET_BEST_PRICE(req_line,
1077 x_conversion_rate,
1078 x_ship_to_location_id);
1079 */
1080
1081 /* <TIMEPHASED FPI END> */
1082
1083 --frkhan 1/12/99 get default country of origin code
1084 po_coo_s.get_default_country_of_origin(
1085 req_line.item_id,
1086 req_line.destination_organization_id,
1087 req_line.vendor_id,
1088 req_line.vendor_site_id,
1089 x_country_of_origin_code);
1090
1091 SELECT FC.EXTENDED_PRECISION
1092 INTO x_ext_precision
1093 FROM PO_HEADERS POH, FND_CURRENCIES FC
1094 WHERE POH.PO_HEADER_ID = req_line.blanket_po_header_id
1095 AND POH.CURRENCY_CODE = FC.CURRENCY_CODE;
1096
1097 --<Bug# 3293109 START>
1098 if nvl(l_po_promised_def_prf, 'N') = 'Y' then
1099 l_promised_date := req_line.need_by_date;
1100 end if;
1101 --<Bug# 3293109 END>
1102
1103 -- bug4865023 START
1104 l_outsourced_assembly :=
1105 PO_CORE_S.get_outsourced_assembly
1106 ( p_item_id => req_line.item_id,
1107 p_ship_to_org_id => req_line.destination_organization_id
1108 );
1109 -- bug4865023 END
1110
1111 /* Bug 2654838 : If UOM in Blanket and Purchase requisition are same
1112 then we should not convert the quantity. */
1113
1114 -- Bug 3570793 START
1115 IF (g_debug_stmt) THEN
1116 PO_DEBUG.debug_stmt (
1117 p_log_head => c_log_head||l_api_name,
1118 p_token => '',
1119 p_message => 'Create release shipment;'
1120 ||' req_line_id: '||req_line.requisition_line_id
1121 ||', po_release_id: '||x_po_release_id
1122 ||', line_location_id: '||x_line_location_id
1123 );
1124 END IF;
1125 -- Bug 3570793 END
1126
1127 INSERT INTO PO_LINE_LOCATIONS(
1128 LINE_LOCATION_ID,
1129 LAST_UPDATE_DATE,
1130 LAST_UPDATED_BY,
1131 PO_HEADER_ID,
1132 CREATION_DATE,
1133 CREATED_BY,
1134 LAST_UPDATE_LOGIN,
1135 PO_LINE_ID,
1136 QUANTITY,
1137 QUANTITY_RECEIVED,
1138 QUANTITY_ACCEPTED,
1139 QUANTITY_REJECTED,
1140 QUANTITY_BILLED,
1141 QUANTITY_CANCELLED,
1142 SHIP_TO_LOCATION_ID,
1143 NEED_BY_DATE,
1144 PROMISED_DATE,
1145 --togeorge 09/28/2000
1146 --added note to receiver
1147 note_to_receiver,
1148 APPROVED_FLAG,
1149 APPROVED_DATE,
1150 PO_RELEASE_ID,
1151 CANCEL_FLAG,
1152 CLOSED_CODE,
1153 PRICE_OVERRIDE,
1154 ENCUMBERED_FLAG,
1155 SHIPMENT_TYPE,
1156 SHIPMENT_NUM,
1157 INSPECTION_REQUIRED_FLAG,
1158 RECEIPT_REQUIRED_FLAG,
1159 GOVERNMENT_CONTEXT,
1160 DAYS_EARLY_RECEIPT_ALLOWED,
1161 DAYS_LATE_RECEIPT_ALLOWED,
1162 ENFORCE_SHIP_TO_LOCATION_CODE,
1163 SHIP_TO_ORGANIZATION_ID,
1164 INVOICE_CLOSE_TOLERANCE,
1165 RECEIVE_CLOSE_TOLERANCE,
1166 ACCRUE_ON_RECEIPT_FLAG,
1167 RECEIVING_ROUTING_ID,
1168 QTY_RCV_TOLERANCE,
1169 ALLOW_SUBSTITUTE_RECEIPTS_FLAG,
1170 QTY_RCV_EXCEPTION_CODE,
1171 RECEIPT_DAYS_EXCEPTION_CODE,
1172 MATCH_OPTION, -- bgu, Dec. 11, 98
1173 COUNTRY_OF_ORIGIN_CODE, --frkhan 1/12/99
1174 SECONDARY_UNIT_OF_MEASURE,
1175 SECONDARY_QUANTITY,
1176 PREFERRED_GRADE,
1177 SECONDARY_QUANTITY_RECEIVED,
1178 SECONDARY_QUANTITY_ACCEPTED,
1179 SECONDARY_QUANTITY_REJECTED,
1180 SECONDARY_QUANTITY_CANCELLED,
1181 VMI_FLAG, -- VMI FPH
1182 DROP_SHIP_FLAG, -- <DropShip FPJ>
1183 ORG_ID, -- <R12 MOAC>
1184 tax_attribute_update_code, --<eTax Integration R12>
1185 outsourced_assembly, -- bug 4865023
1186 value_basis, --bug 4896950
1187 matching_basis, --bug 4896950
1188 unit_meas_lookup_code --bug 4896950
1189 )
1190 VALUES (x_line_location_id, -- :line_location_id
1191 sysdate,
1192 req_line.last_updated_by, -- :cpo_last_updated_by
1193 req_line.blanket_po_header_id, -- :po_header_id
1194 sysdate,
1195 req_line.last_updated_by, -- :cpo_last_updated_by
1196 req_line.last_update_login, -- :last_update_login
1197 req_line.blanket_po_line_id, -- :po_line_id
1198 decode(x_conversion_rate,1,req_line.quantity,round(req_line.quantity * x_conversion_rate,5)),--:quantity
1199 0,
1200 0,
1201 0,
1202 0,
1203 0,
1204 x_ship_to_location_id, -- :ship_to_location_id,
1205 req_line.need_by_date, -- :need_by_date
1206 l_promised_date, --<Bug# 3293109>
1207 --togeorge 09/28/2000
1208 --added note to receiver
1209 req_line.note_to_receiver,
1210 DECODE(x_authorization_status,
1211 'APPROVED','Y','N'), -- 'N'
1212 --Bug #1057095 insert sysdate only
1213 --if the shipment is approved
1214 DECODE(x_authorization_status,
1215 'APPROVED', sysdate, NULL), -- approved date
1216 x_po_release_id, -- :po_release_id,
1217 'N',
1218 'OPEN',
1219 x_best_price, /* 9168321 fix ROUND(x_best_price,x_ext_precision), */
1220 -- :best_price
1221 'N',
1222 'BLANKET', -- :shipment_type,
1223 x_shipment_num, -- :shipment_num,
1224 rcv_controls.inspection_required_flag,
1225 -- :inspection_required_flag,
1226 rcv_controls.receipt_required_flag,
1227 --:receipt_rqd_flag,
1228 null, -- :government_context,
1229 rcv_controls.days_early_receipt_allowed,
1230 -- :days_early_receipt,
1231 rcv_controls.days_late_receipt_allowed,
1232 -- :days_late_receipt,
1233 rcv_controls.enforce_ship_to_location,
1234 -- :enforce_ship_to_location,
1235 req_line.destination_organization_id, -- :dest_org
1236 rcv_controls.invoice_close_tolerance,
1237 -- :invoice_close_tolerance,
1238 rcv_controls.receipt_close_tolerance,
1239 -- :receive_close_tolerance,
1240 DECODE(req_line.destination_type_code, --:dst_code
1241 'EXPENSE',
1242 DECODE(rcv_controls.receipt_required_flag,
1243 -- :receipt_required_flag
1244 'N', 'N',
1245 DECODE(x_expense_accrual_code,
1246 -- :expense_accrual_code
1247 'PERIOD END', 'N', 'Y')),
1248 'Y'),
1249 rcv_controls.receiving_routing_id,
1250 rcv_controls.qty_rcv_tolerance,
1251 rcv_controls.allow_substitute_receipts_flag,
1252 rcv_controls.qty_rcv_exception_code,
1253 rcv_controls.receipt_days_exception_code,
1254 x_invoice_match_option, --bgu, Dec. 11, 98
1255 X_COUNTRY_OF_ORIGIN_CODE, --frkhan 1/12/99
1256 --<INVCONV R12 START>
1257 x_secondary_unit_of_measure,
1258 x_secondary_quantity,
1259 req_line.preferred_grade,
1260 DECODE(x_secondary_unit_of_measure,NULL,NULL,0),
1261 DECODE(x_secondary_unit_of_measure,NULL,NULL,0),
1262 DECODE(x_secondary_unit_of_measure,NULL,NULL,0),
1263 DECODE(x_secondary_unit_of_measure,NULL,NULL,0),
1264 --<INVCONV R12 END>
1265 req_line.vmi_flag, -- VMI FPH
1266 req_line.drop_ship_flag, -- <DropShip FPJ>
1267 req_line.org_id, -- <R12 MOAC>
1268 'CREATE', --<eTax integration R12>
1269 l_outsourced_assembly, -- bug 4865023
1270 l_value_basis, --bug 4896950
1271 l_matching_basis, --bug 4896950
1272 l_unit_meas_lookup_code --bug 4896950
1273 );
1274
1275 UPDATE PO_REQUISITION_LINES
1276 SET line_location_id = x_line_location_id,
1277 reqs_in_pool_flag = NULL, -- <REQINPOOL>
1278 last_update_date = SYSDATE
1279 WHERE requisition_line_id = req_line.requisition_line_id;
1280
1281 /* bug 1921133
1282 If we round the conversion rate as there will be inaccuracies in the
1283 final quantity. so we need to round the quantity
1284 after muliplying with the rate intead of the rate itself */
1285
1286 /* bug 2994264 : need to round off the quantity as indicated in the above fix.
1287 The above fix indicates the rounding, but due to some reason,
1288 it was missed, hence doing it as a part of this fix. */
1289 /* Bug# 3104460 - Do not update PO_LINES.QUANTITY with released amount
1290 UPDATE SQL deleted */
1291
1292 /* Bug 947709
1293 ** Adding code to copy attachments from Requisition
1294 ** to Release.
1295 */
1296
1297 -- Calling API to copy attachments from Requisition Lines to
1298 -- Release Shipments
1299 fnd_attached_documents2_pkg.copy_attachments('REQ_LINES',
1300 req_line.requisition_line_id,
1301 '',
1302 '',
1303 '',
1304 '',
1305 'PO_SHIPMENTS',
1306 x_line_location_id,
1307 '',
1308 '',
1309 '',
1310 '',
1311 req_line.last_updated_by,
1312 req_line.last_update_login,
1313 '',
1314 '',
1315 '');
1316
1317 -- Calling API to copy Requisiton Header Attachments to
1318 -- Release Shipments.
1319
1320 fnd_attached_documents2_pkg.copy_attachments('REQ_HEADERS',
1321 req_line.requisition_header_id,
1322 '',
1323 '',
1324 '',
1325 '',
1326 'PO_SHIPMENTS',
1327 x_line_location_id,
1328 '',
1329 '',
1330 '',
1331 '',
1332 req_line.last_updated_by,
1333 req_line.last_update_login,
1334 '',
1335 '',
1336 '');
1337
1338 /* Bug #947709 */
1339 --<BUG 7721295 Moved the call after creation of headers,shipments and distributions>
1340 /*<BUG 7685164 Added as part of LCM ER>
1341 l_progress := '003';
1342
1343 PO_DOCUMENT_CHECKS_PVT.set_lcm_flag(x_line_location_id,'AFTER',l_return_status);*/
1344
1345 EXCEPTION
1346 --<BUG 7685164 Exception part added as part of LCM ER>
1347 WHEN OTHERS THEN
1348 FND_FILE.put_line(FND_FILE.LOG,
1349 c_log_head || l_api_name || ' exception; location: '
1350 || l_progress || ' SQL code: ' || sqlcode);
1351 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1352 FND_LOG.string(FND_LOG.level_unexpected, c_log_head ||
1353 l_api_name || '.others_exception', 'EXCEPTION: Location is '
1354 || l_progress || ' SQL CODE is '||sqlcode);
1355 END IF;
1356 raise_application_error(-20001,sqlerrm||'---'||msgbuf);
1357 END CREATE_RELEASE_SHIPMENT;
1358
1359
1360 /* ============================================================================
1361 NAME: CREATE_RELEASE_DISTRIBUTIONS
1362 DESC: Create new release dsitributions
1363 ARGS: IN : req_line IN requisition_lines_cursor%rowtype
1364 ALGR: Create new release distributions
1365
1366 ===========================================================================*/
1367
1368 PROCEDURE CREATE_RELEASE_DISTRIBUTION(
1369 req_line IN requisition_lines_cursor%rowtype
1370 , p_req_enc_flag IN VARCHAR2
1371 )
1372 IS
1373 l_api_name CONSTANT VARCHAR2(30) := 'create_release_distribution';
1374
1375 x_conversion_rate number := 1;
1376 x_shipment_quantity number := 0;
1377 x_dist_quantity number := 0;
1378 x_qty_difference number := 0;
1379
1380 x_tax_code_id ap_tax_codes_all.tax_id%type;
1381
1382
1383 BEGIN
1384
1385 IF ( req_line.req_uom <> req_line.po_uom ) THEN
1386 /*Bug1635257
1387 The po_uom_convert function was returning a very high precision
1388 after converting units while creating PO from requisitions
1389 in autocreate. Thus the value being returned by the function
1390 call was rounded off to 5 digits after decimal to prevent
1391 such high prcision to be generated.
1392 */
1393 x_conversion_rate := round(po_uom_s.po_uom_convert(req_line.req_uom,
1394 req_line.po_uom,
1395 req_line.item_id),5);
1396 END IF;
1397
1398 /* Bug 2654838 : If UOM in Blanket and Purchase requisition are same
1399 then we should not convert the quantity. */
1400
1401 BEGIN --<GRANTS FPJ>
1402 -- Bug 3570793 START
1403 IF (g_debug_stmt) THEN
1404 PO_DEBUG.debug_stmt (
1405 p_log_head => c_log_head||l_api_name,
1406 p_token => '',
1407 p_message => 'Create release distributions;'
1408 ||' req_line_id: '||req_line.requisition_line_id
1409 ||', po_release_id: '||x_po_release_id
1410 ||', line_location_id: '||x_line_location_id
1411 );
1412 END IF;
1413 -- Bug 3570793 END
1414
1415 INSERT INTO PO_DISTRIBUTIONS
1416 (PO_DISTRIBUTION_ID,
1417 LAST_UPDATE_DATE,
1418 LAST_UPDATED_BY,
1419 PO_HEADER_ID,
1420 CREATION_DATE,
1421 CREATED_BY,
1422 LAST_UPDATE_LOGIN,
1423 PO_LINE_ID,
1424 LINE_LOCATION_ID,
1425 PO_RELEASE_ID,
1426 REQ_DISTRIBUTION_ID,
1427 SET_OF_BOOKS_ID,
1428 CODE_COMBINATION_ID,
1429 DELIVER_TO_LOCATION_ID,
1430 DELIVER_TO_PERSON_ID,
1431 QUANTITY_ORDERED,
1432 QUANTITY_DELIVERED,
1433 QUANTITY_BILLED,
1434 QUANTITY_CANCELLED,
1435 RATE_DATE,
1436 RATE,
1437 ACCRUED_FLAG,
1438 ENCUMBERED_FLAG,
1439 GL_ENCUMBERED_DATE,
1440 GL_ENCUMBERED_PERIOD_NAME,
1441 DISTRIBUTION_NUM,
1442 DESTINATION_TYPE_CODE,
1443 DESTINATION_ORGANIZATION_ID,
1444 DESTINATION_SUBINVENTORY,
1445 BUDGET_ACCOUNT_ID,
1446 ACCRUAL_ACCOUNT_ID,
1447 VARIANCE_ACCOUNT_ID,
1448 WIP_ENTITY_ID,
1449 WIP_LINE_ID,
1450 WIP_REPETITIVE_SCHEDULE_ID,
1451 WIP_OPERATION_SEQ_NUM,
1452 WIP_RESOURCE_SEQ_NUM,
1453 BOM_RESOURCE_ID,
1454 GOVERNMENT_CONTEXT,
1455 PREVENT_ENCUMBRANCE_FLAG,
1456 PROJECT_ID,
1457 TASK_ID,
1458 AWARD_ID, -- OGM_0.0
1459 EXPENDITURE_TYPE,
1460 PROJECT_ACCOUNTING_CONTEXT,
1461 DESTINATION_CONTEXT,
1462 EXPENDITURE_ORGANIZATION_ID,
1463 EXPENDITURE_ITEM_DATE,
1464 ACCRUE_ON_RECEIPT_FLAG,
1465 KANBAN_CARD_ID,
1466 TAX_RECOVERY_OVERRIDE_FLAG, --<eTax Integration R12>
1467 RECOVERY_RATE,
1468 --togeorge 10/05/2000
1469 --added oke columns
1470 oke_contract_line_id,
1471 oke_contract_deliverable_id,
1472 --spangulu 09/16/2003
1473 --added distribution_type for encumb. rewrite
1474 distribution_type,
1475 Org_Id -- <R12 MOAC>
1476 )
1477 SELECT PO_DISTRIBUTIONS_S.NEXTVAL,
1478 sysdate,
1479 req_line.last_updated_by, -- :cpo_last_updated_by
1480 req_line.blanket_po_header_id, --:po_header_id
1481 sysdate,
1482 req_line.last_updated_by, -- :cpo_last_updated_by,
1483 req_line.last_update_login, -- :last_update_login,
1484 req_line.blanket_po_line_id, -- :po_line_id,
1485 x_line_location_id, -- :line_location_id
1486 x_po_release_id, -- :po_release_id
1487 PRD.DISTRIBUTION_ID,
1488 PRD.SET_OF_BOOKS_ID,
1489 PRD.CODE_COMBINATION_ID,
1490 req_line.deliver_to_location_id, --:deliver_to_loc_id
1491 req_line.deliver_to_person_id, --:deliver_to_per_id
1492 decode(x_conversion_rate,1,prd.req_line_quantity,round(prd.req_line_quantity * x_conversion_rate,5)),
1493 -- :div by rate????
1494 0,
1495 0,
1496 0,
1497 ph.rate_date,
1498 ph.rate,
1499 'N',
1500 'N'
1501 --<Encumbrance FPJ>
1502 -- If Req encumbrance is on, copy the Req period.
1503 -- Otherwise, if PO enc is on and SYSDATE is open
1504 -- (x_period_name tries to tell us this, but is buggy)
1505 -- then use SYSDATE. Otherwise, NULL.
1506
1507 -- gl_encumbered_date =
1508 , DECODE( p_req_enc_flag
1509 , 'Y', PRD.gl_encumbered_date
1510 , DECODE( x_period_name
1511 , '', TO_DATE(NULL)
1512 , TRUNC(SYSDATE)
1513 )
1514 )
1515 -- gl_encumbered_period_name =
1516 , DECODE( p_req_enc_flag
1517 , 'Y', PRD.gl_encumbered_period_name
1518 , x_period_name
1519 )
1520 , PRD.DISTRIBUTION_NUM, -- (:distribution_num + ROWNUM),
1521 PRL.DESTINATION_TYPE_CODE,
1522 PRL.DESTINATION_ORGANIZATION_ID,
1523 PRL.DESTINATION_SUBINVENTORY,
1524 PRD.BUDGET_ACCOUNT_ID,
1525 PRD.ACCRUAL_ACCOUNT_ID,
1526 PRD.VARIANCE_ACCOUNT_ID,
1527 PRL.WIP_ENTITY_ID,
1528 PRL.WIP_LINE_ID,
1529 PRL.WIP_REPETITIVE_SCHEDULE_ID,
1530 PRL.WIP_OPERATION_SEQ_NUM,
1531 PRL.WIP_RESOURCE_SEQ_NUM,
1532 PRL.BOM_RESOURCE_ID,
1533 PH.GOVERNMENT_CONTEXT
1534 --<ENCUMBRANCE FPJ>
1535 -- prevent_encumbrance_flag =
1536 /*, DECODE( PRL.destination_type_code
1537 , g_dest_type_code_SHOP_FLOOR, 'Y'
1538 , 'N'
1539 ) */
1540 , DECODE( PRL.destination_type_code
1541 , g_dest_type_code_SHOP_FLOOR
1542 , decode((select entity_type
1543 from wip_entities
1544 where wip_entity_id= PRL.wip_entity_id),6, 'N', 'Y')
1545 , 'N'
1546 ) /* Encumbrance Project - To enable encumbrance for destination type Shop Floor and WIP entity type EAM */
1547 , PRD.PROJECT_ID,
1548 PRD.TASK_ID,
1549 PRD.AWARD_ID, -- OGM_0.0 Change
1550 PRD.EXPENDITURE_TYPE,
1551 PRD.PROJECT_ACCOUNTING_CONTEXT,
1552 PRL.DESTINATION_CONTEXT,
1553 PRD.EXPENDITURE_ORGANIZATION_ID,
1554 PRD.EXPENDITURE_ITEM_DATE,
1555 PLL.ACCRUE_ON_RECEIPT_FLAG,
1556 PRL.KANBAN_CARD_ID,
1557 nvl(PRD.TAX_RECOVERY_OVERRIDE_FLAG, 'N'),
1558 decode(PRD.TAX_RECOVERY_OVERRIDE_FLAG, 'Y', PRD.RECOVERY_RATE, null),--<R12 eTax Integration>
1559 --togeorge 10/05/2000
1560 --added oke columns
1561 PRD.oke_contract_line_id,
1562 PRD.oke_contract_deliverable_id,
1563 --spangulu 09/16/2003
1564 --added distribution_type for encumb. rewrite
1565 PLL.shipment_type,
1566 PH.Org_Id -- <R12 MOAC>
1567 FROM PO_REQ_DISTRIBUTIONS PRD,
1568 PO_REQUISITION_LINES PRL,
1569 PO_HEADERS PH,
1570 PO_LINE_LOCATIONS PLL
1571 WHERE PRD.REQUISITION_LINE_ID = req_line.requisition_line_id
1572 AND PRL.REQUISITION_LINE_ID = req_line.requisition_line_id
1573 AND PH.PO_HEADER_ID = req_line.blanket_po_header_id
1574 AND PLL.LINE_LOCATION_ID = x_line_location_id;
1575
1576 --<GRANTS FPJ START>
1577 create_award_distribution;
1578 EXCEPTION
1579 WHEN OTHERS THEN
1580 ROLLBACK TO PORELGEN_1;
1581 RAISE;
1582 END;
1583 --<GRANTS FPJ END>
1584
1585 SELECT POLL.quantity
1586 INTO x_shipment_quantity
1587 FROM PO_LINE_LOCATIONS POLL,
1588 PO_REQUISITION_LINES PORL
1589 WHERE POLL.LINE_LOCATION_ID = PORL.LINE_LOCATION_ID
1590 AND PORL.REQUISITION_LINE_ID
1591 = req_line.requisition_line_id;
1592
1593 SELECT SUM(POD.QUANTITY_ORDERED)
1594 INTO x_dist_quantity
1595 FROM PO_LINE_LOCATIONS POLL,
1596 PO_REQUISITION_LINES PORL,
1597 PO_DISTRIBUTIONS POD
1598 WHERE POLL.LINE_LOCATION_ID = PORL.LINE_LOCATION_ID
1599 AND PORL.REQUISITION_LINE_ID
1600 = req_line.requisition_line_id
1601 AND POD.LINE_LOCATION_ID = POLL.LINE_LOCATION_ID;
1602
1603 x_qty_difference := x_shipment_quantity-x_dist_quantity;
1604
1605 IF (x_qty_difference <> 0) THEN
1606 UPDATE PO_DISTRIBUTIONS POD
1607 SET POD.QUANTITY_ORDERED
1608 = POD.QUANTITY_ORDERED + x_qty_difference
1609 WHERE POD.PO_DISTRIBUTION_ID =
1610 (SELECT POD2.PO_DISTRIBUTION_ID
1611 FROM PO_DISTRIBUTIONS POD2,
1612 PO_LINE_LOCATIONS POLL,
1613 PO_REQUISITION_LINES PORL
1614 WHERE POD2.LINE_LOCATION_ID
1615 = POLL.LINE_LOCATION_ID
1616 AND POLL.LINE_LOCATION_ID
1617 = PORL.LINE_LOCATION_ID
1618 AND PORL.REQUISITION_LINE_ID
1619 = req_line.requisition_line_id
1620 AND POD2.distribution_num=1);
1621 END IF;
1622
1623
1624 EXCEPTION
1625 WHEN OTHERS THEN
1626 raise_application_error(-20001,sqlerrm||'---'||msgbuf);
1627
1628 END CREATE_RELEASE_DISTRIBUTION;
1629
1630
1631 /* ============================================================================
1632 NAME: GET_RCV_CONTROLS
1633 DESC: Get receiving controls
1634 ARGS: IN : req_line IN requisition_lines_cursor%rowtype
1635 IN OUT: rcv_controls IN OUT rcv_control_type
1636 ALGR: Get all the receiving controls required
1637
1638 ===========================================================================*/
1639
1640 PROCEDURE GET_RCV_CONTROLS(req_line IN requisition_lines_cursor%rowtype,
1641 rcv_controls IN OUT NOCOPY rcv_control_type)
1642 IS
1643 BEGIN
1644 IF (req_line.item_id is not NULL) THEN
1645 select nvl(rcv_controls.inspection_required_flag,
1646 msi.INSPECTION_REQUIRED_FLAG),
1647 nvl(rcv_controls.days_early_receipt_allowed,
1648 msi.DAYS_EARLY_RECEIPT_ALLOWED),
1649 nvl(rcv_controls.days_late_receipt_allowed,
1650 msi.DAYS_LATE_RECEIPT_ALLOWED),
1651 nvl(rcv_controls.enforce_ship_to_location,
1652 msi.ENFORCE_SHIP_TO_LOCATION_CODE),
1653 nvl(rcv_controls.invoice_close_tolerance,
1654 msi.INVOICE_CLOSE_TOLERANCE),
1655 nvl(rcv_controls.receipt_close_tolerance,
1656 msi.RECEIVE_CLOSE_TOLERANCE),
1657 nvl(rcv_controls.receiving_routing_id,
1658 msi.RECEIVING_ROUTING_ID),
1659 nvl(rcv_controls.qty_rcv_tolerance,
1660 msi.QTY_RCV_TOLERANCE),
1661 nvl(rcv_controls.allow_substitute_receipts_flag,
1662 msi.ALLOW_SUBSTITUTE_RECEIPTS_FLAG),
1663 nvl(rcv_controls.qty_rcv_exception_code,
1664 msi.QTY_RCV_EXCEPTION_CODE),
1665 nvl(rcv_controls.receipt_required_flag,
1666 msi.RECEIPT_REQUIRED_FLAG),
1667 nvl(rcv_controls.receipt_days_exception_code,
1668 msi.RECEIPT_DAYS_EXCEPTION_CODE)
1669 into rcv_controls.inspection_required_flag,
1670 rcv_controls.days_early_receipt_allowed,
1671 rcv_controls.days_late_receipt_allowed,
1672 rcv_controls.enforce_ship_to_location,
1673 rcv_controls.invoice_close_tolerance,
1674 rcv_controls.receipt_close_tolerance,
1675 rcv_controls.receiving_routing_id,
1676 rcv_controls.qty_rcv_tolerance,
1677 rcv_controls.allow_substitute_receipts_flag,
1678 rcv_controls.qty_rcv_exception_code,
1679 rcv_controls.receipt_required_flag,
1680 rcv_controls.receipt_days_exception_code
1681 from mtl_system_items msi
1682 where msi.inventory_item_id = req_line.item_id
1683 and msi.organization_id = req_line.destination_organization_id;
1684 END IF;
1685
1686 -- Bug: 1322342 Select inspection required flag and receipt required flag
1687 -- also if destination org returns null
1688
1689 IF (rcv_controls.receipt_close_tolerance is null) OR
1690 (rcv_controls.invoice_close_tolerance is null) OR
1691 (rcv_controls.receipt_required_flag is null) OR
1692 (rcv_controls.inspection_required_flag is null) THEN
1693
1694 BEGIN
1695 select nvl(rcv_controls.receipt_close_tolerance,
1696 receive_close_tolerance),
1697 nvl(rcv_controls.invoice_close_tolerance,
1698 invoice_close_tolerance),
1699 nvl(rcv_controls.receipt_required_flag,
1700 receipt_required_flag),
1701 nvl(rcv_controls.inspection_required_flag,
1702 inspection_required_flag)
1703 into rcv_controls.receipt_close_tolerance,
1704 rcv_controls.invoice_close_tolerance,
1705 rcv_controls.receipt_required_flag,
1706 rcv_controls.inspection_required_flag
1707 from mtl_system_items
1708 where organization_id = x_inventory_org_id
1709 and inventory_item_id = req_line.item_id;
1710 EXCEPTION
1711 WHEN NO_DATA_FOUND THEN
1712 NULL;
1713 END;
1714 END IF;
1715
1716 IF (req_line.blanket_po_line_id is not NULL) THEN
1717 select nvl(rcv_controls.receipt_required_flag,plt.receiving_flag)
1718 into rcv_controls.receipt_required_flag
1719 from po_lines pol,
1720 po_line_types plt
1721 where pol.po_line_id = req_line.blanket_po_line_id
1722 and pol.line_type_id = plt.line_type_id;
1723 END IF;
1724
1725 --Begin fix4388305(forward fix4379167)
1726 IF (req_line.blanket_po_line_id is not NULL AND
1727 rcv_controls.receipt_close_tolerance is null) THEN
1728
1729 select nvl(rcv_controls.receipt_close_tolerance, plt.receive_close_tolerance)
1730 into rcv_controls.receipt_close_tolerance
1731 from po_lines pol,
1732 po_line_types plt
1733 where pol.po_line_id = req_line.blanket_po_line_id
1734 and pol.line_type_id = plt.line_type_id;
1735 END IF;
1736 -- End fix4388305(forward fix4379167)
1737
1738 IF (req_line.vendor_id is not NULL) THEN
1739 select nvl(rcv_controls.inspection_required_flag,
1740 pov.INSPECTION_REQUIRED_FLAG),
1741 nvl(rcv_controls.days_early_receipt_allowed,
1742 pov.DAYS_EARLY_RECEIPT_ALLOWED),
1743 nvl(rcv_controls.days_late_receipt_allowed,
1744 pov.DAYS_LATE_RECEIPT_ALLOWED),
1745 nvl(rcv_controls.enforce_ship_to_location,
1746 pov.ENFORCE_SHIP_TO_LOCATION_CODE),
1747 nvl(rcv_controls.receiving_routing_id,
1748 pov.RECEIVING_ROUTING_ID),
1749 nvl(rcv_controls.qty_rcv_tolerance,
1750 pov.QTY_RCV_TOLERANCE),
1751 nvl(rcv_controls.allow_substitute_receipts_flag,
1752 pov.ALLOW_SUBSTITUTE_RECEIPTS_FLAG),
1753 nvl(rcv_controls.qty_rcv_exception_code,
1754 pov.QTY_RCV_EXCEPTION_CODE),
1755 nvl(rcv_controls.receipt_required_flag,
1756 pov.RECEIPT_REQUIRED_FLAG),
1757 nvl(rcv_controls.receipt_days_exception_code,
1758 pov.RECEIPT_DAYS_EXCEPTION_CODE)
1759 into rcv_controls.inspection_required_flag,
1760 rcv_controls.days_early_receipt_allowed,
1761 rcv_controls.days_late_receipt_allowed,
1762 rcv_controls.enforce_ship_to_location,
1763 rcv_controls.receiving_routing_id,
1764 rcv_controls.qty_rcv_tolerance,
1765 rcv_controls.allow_substitute_receipts_flag,
1766 rcv_controls.qty_rcv_exception_code,
1767 rcv_controls.receipt_required_flag,
1768 rcv_controls.receipt_days_exception_code
1769 from po_vendors pov
1770 where pov.vendor_id = req_line.vendor_id;
1771 END IF;
1772 select nvl(rcv_controls.days_early_receipt_allowed,
1773 rp.DAYS_EARLY_RECEIPT_ALLOWED),
1774 nvl(rcv_controls.days_late_receipt_allowed,
1775 rp.DAYS_LATE_RECEIPT_ALLOWED),
1776 nvl(rcv_controls.enforce_ship_to_location,
1777 rp.ENFORCE_SHIP_TO_LOCATION_CODE),
1778 nvl(rcv_controls.receiving_routing_id,
1779 rp.RECEIVING_ROUTING_ID),
1780 nvl(rcv_controls.qty_rcv_tolerance,
1781 rp.QTY_RCV_TOLERANCE),
1782 nvl(rcv_controls.allow_substitute_receipts_flag,
1783 rp.ALLOW_SUBSTITUTE_RECEIPTS_FLAG),
1784 nvl(rcv_controls.qty_rcv_exception_code,
1785 rp.QTY_RCV_EXCEPTION_CODE),
1786 nvl(rcv_controls.receipt_days_exception_code,
1787 rp.RECEIPT_DAYS_EXCEPTION_CODE)
1788 into rcv_controls.days_early_receipt_allowed,
1789 rcv_controls.days_late_receipt_allowed,
1790 rcv_controls.enforce_ship_to_location,
1791 rcv_controls.receiving_routing_id,
1792 rcv_controls.qty_rcv_tolerance,
1793 rcv_controls.allow_substitute_receipts_flag,
1794 rcv_controls.qty_rcv_exception_code,
1795 rcv_controls.receipt_days_exception_code
1796 from rcv_parameters rp
1797 where rp.organization_id = req_line.destination_organization_id;
1798 select nvl(rcv_controls.inspection_required_flag,
1799 posp.INSPECTION_REQUIRED_FLAG),
1800 nvl(rcv_controls.receipt_required_flag,
1801 posp.RECEIVING_FLAG),
1802 nvl(rcv_controls.invoice_close_tolerance,
1803 posp.INVOICE_CLOSE_TOLERANCE),
1804 nvl(rcv_controls.receipt_close_tolerance,
1805 posp.RECEIVE_CLOSE_TOLERANCE)
1806 into rcv_controls.inspection_required_flag,
1807 rcv_controls.receipt_required_flag,
1808 rcv_controls.invoice_close_tolerance,
1809 rcv_controls.receipt_close_tolerance
1810 from po_system_parameters posp;
1811
1812 IF (rcv_controls.inspection_required_flag is NULL) THEN
1813 rcv_controls.inspection_required_flag := 'N';
1814 END IF;
1815
1816 -- begin bug 3330748
1817 IF (req_line.drop_ship_flag = 'Y') THEN
1818 rcv_controls.inspection_required_flag := 'N';
1819 END IF;
1820 -- begin bug 3330748
1821
1822
1823 IF (rcv_controls.receipt_required_flag is NULL) THEN
1824 rcv_controls.receipt_required_flag := 'N';
1825 END IF;
1826
1827 IF (rcv_controls.days_early_receipt_allowed is NULL) THEN
1828 rcv_controls.days_early_receipt_allowed := 0;
1829 END IF;
1830
1831 IF (rcv_controls.days_late_receipt_allowed is NULL) THEN
1832 rcv_controls.days_late_receipt_allowed := 0;
1833 END IF;
1834
1835 /* Bug# 2206626 - Replaced 'N' with 'NONE' */
1836 IF (rcv_controls.enforce_ship_to_location is NULL) THEN
1837 rcv_controls.enforce_ship_to_location := 'NONE';
1838 END IF;
1839
1840 IF (rcv_controls.invoice_close_tolerance is NULL) THEN
1841 rcv_controls.invoice_close_tolerance := '0';
1842 END IF;
1843
1844 IF (rcv_controls.receipt_close_tolerance is NULL) THEN
1845 rcv_controls.receipt_close_tolerance := '0';
1846 END IF;
1847
1848
1849 --bug3211753
1850 --For drop shipments, receipt routing is always 3 (direct delivery)
1851 IF (req_line.drop_ship_flag = 'Y') THEN
1852 rcv_controls.receiving_routing_id := 3;
1853 -- <<Start of Bug Fix::14762318>>
1854 -- For four way matching, receipt routing is always Inspection Required(2)
1855 ELSIF (rcv_controls.inspection_required_flag = 'Y'
1856 AND rcv_controls.receipt_required_flag = 'Y')
1857 THEN
1858 rcv_controls.receiving_routing_id := 2; --Inspection Required
1859 ELSE
1860 NULL;
1861 -- <<End of Bug Fix::14762318>>
1862 END IF;
1863
1864 EXCEPTION
1865 WHEN OTHERS THEN
1866 raise_application_error(-20001,sqlerrm||'---'||msgbuf);
1867
1868 END GET_RCV_CONTROLS;
1869
1870 /* ==========================================================================
1871 NAME: GET_INVOICE_MATCH_OPTION
1872 DESC: Default match option from vendor site, vendor, down to financial
1873 system defaults
1874 ARGS: IN : req_line IN requisition_lines_cursor%rowtype
1875 OUT: invoice_match_option
1876 AUTHOR: bgu, Dec. 10, 98
1877 =========================================================================*/
1878
1879 PROCEDURE GET_INVOICE_MATCH_OPTION(req_line IN requisition_lines_cursor%rowtype,
1880 x_invoice_match_option OUT NOCOPY varchar2)
1881 IS
1882 BEGIN
1883
1884 x_invoice_match_option := NULL;
1885
1886 if (req_line.vendor_site_id is not null) then
1887 /* Retrieve Invoice Match Option from Vendor site*/
1888 SELECT match_option
1889 INTO x_invoice_match_option
1890 FROM po_vendor_sites
1891 WHERE vendor_site_id = req_line.vendor_site_id;
1892 end if;
1893
1894 if(x_invoice_match_option is NULL) then
1895 /* Retrieve Invoice Match Option from Vendor */
1896 if (req_line.vendor_id is not null) then
1897 SELECT match_option
1898 INTO x_invoice_match_option
1899 FROM po_vendors
1900 WHERE vendor_id = req_line.vendor_id;
1901 end if;
1902 end if;
1903
1904 if(x_invoice_match_option is NULL) then
1905 /* Retrieve Invoice Match Option from Financial System Parameters */
1906 SELECT fsp.match_option
1907 INTO x_invoice_match_option
1908 FROM financials_system_parameters fsp;
1909 end if;
1910
1911 EXCEPTION
1912 WHEN OTHERS THEN
1913 raise_application_error(-20001,sqlerrm||'---'||msgbuf);
1914
1915 END GET_INVOICE_MATCH_OPTION;
1916
1917
1918 /* ============================================================================
1919 NAME: MAINTAIN_SUPPLY
1920 DESC: Maintain the supply view
1921 ARGS: IN : req_line IN requisition_lines_cursor%rowtype
1922 ALGR: If the release created is approved, delete req supply and create
1923 Po supply
1924
1925 ===========================================================================*/
1926
1927 PROCEDURE MAINTAIN_SUPPLY(req_line IN requisition_lines_cursor%rowtype)
1928 IS
1929 BEGIN
1930
1931 if (x_authorization_status = 'APPROVED') THEN
1932
1933 DELETE FROM MTL_SUPPLY
1934 WHERE SUPPLY_TYPE_CODE = 'REQ'
1935 AND SUPPLY_SOURCE_ID = req_line.requisition_line_id;
1936
1937 INSERT INTO MTL_SUPPLY
1938 (supply_type_code,
1939 supply_source_id,
1940 last_updated_by,
1941 last_update_date,
1942 last_update_login,
1943 created_by,
1944 creation_date,
1945 po_header_id,
1946 po_release_id,
1947 po_line_id,
1948 po_line_location_id,
1949 po_distribution_id,
1950 item_id,
1951 item_revision,
1952 quantity,
1953 unit_of_measure,
1954 receipt_date,
1955 need_by_date,
1956 destination_type_code,
1957 location_id,
1958 to_organization_id,
1959 to_subinventory,
1960 change_flag)
1961 select 'PO',
1962 pod.po_distribution_id,
1963 pod.last_updated_by,
1964 pod.last_update_date,
1965 pod.last_update_login,
1966 pod.created_by,
1967 pod.creation_date,
1968 pod.po_header_id,
1969 x_po_release_id, -- :po_release_id
1970 pod.po_line_id,
1971 pod.line_location_id,
1972 pod.po_distribution_id,
1973 pol.item_id,
1974 pol.item_revision,
1975 pod.quantity_ordered,
1976 pol.unit_meas_lookup_code,
1977 nvl(poll.promised_date,poll.need_by_date),
1978 poll.need_by_date,
1979 pod.destination_type_code,
1980 pod.deliver_to_location_id,
1981 pod.destination_organization_id,
1982 pod.destination_subinventory,
1983 'Y'
1984 from po_distributions pod,
1985 po_line_locations poll,
1986 po_lines pol
1987 where poll.line_location_id = x_line_location_id
1988 and nvl(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
1989 and nvl(poll.closed_code, 'OPEN') <> 'CLOSED'
1990 and nvl(poll.closed_code, 'OPEN') <> 'CLOSED FOR RECEIVING'
1991 and nvl(poll.cancel_flag, 'N') = 'N'
1992 and pod.line_location_id = poll.line_location_id
1993 and pol.po_line_id = pod.po_line_id
1994 and nvl(poll.approved_flag, 'Y') = 'Y'
1995 and not exists
1996 (select 'Supply Exists'
1997 from mtl_supply ms1
1998 where ms1.supply_type_code = 'PO'
1999 and ms1.supply_source_id = pod.po_distribution_id);
2000
2001 END IF;
2002
2003 EXCEPTION
2004 WHEN OTHERS THEN
2005 raise_application_error(-20001,sqlerrm||'---'||msgbuf);
2006
2007 END MAINTAIN_SUPPLY;
2008
2009 /* ============================================================================
2010 NAME: WRAPUP
2011 DESC: insert into the notifications table and the action history table
2012 ARGS: IN : req_line IN requisition_lines_cursor%rowtype
2013 ALGR: If the release is not approved, insert appropriate rows into
2014 PO_NOTIFICATIONS
2015 else insert appropriate rows into PO_ACTION_HISTORY
2016
2017 ===========================================================================*/
2018
2019 PROCEDURE WRAPUP(req_line IN requisition_lines_cursor%rowtype)
2020 IS
2021 BEGIN
2022
2023 IF (x_authorization_status <> 'APPROVED') THEN
2024
2025 /* obsolete in R11
2026 INSERT INTO PO_NOTIFICATIONS
2027 (EMPLOYEE_ID,
2028 OBJECT_TYPE_LOOKUP_CODE,
2029 OBJECT_ID,
2030 OBJECT_CREATION_DATE,
2031 LAST_UPDATE_DATE,
2032 LAST_UPDATED_BY,
2033 LAST_UPDATE_LOGIN,
2034 CREATION_DATE,
2035 CREATED_BY,
2036 ACTION_LOOKUP_CODE)
2037 SELECT AGENT_ID,
2038 'RELEASE',
2039 PO_RELEASE_ID,
2040 TRUNC(CREATION_DATE),
2041 sysdate,
2042 req_line.last_updated_by,
2043 req_line.last_update_login,
2044 sysdate,
2045 req_line.last_updated_by,
2046 DECODE(HOLD_FLAG,
2047 'Y','ON_HOLD',
2048 DECODE(APPROVED_FLAG,
2049 'R','REQUIRES_REAPPROVAL',
2050 'F','FAILED_APPROVAL',
2051 'NEVER_APPROVED'))
2052 FROM PO_RELEASES
2053 WHERE NVL(CANCEL_FLAG,'N') = 'N'
2054 AND NVL(APPROVED_FLAG,'N') <> 'Y'
2055 AND PO_RELEASE_ID = x_po_release_id; */
2056 null;
2057 ELSE
2058
2059 INSERT into PO_ACTION_HISTORY
2060 (object_id,
2061 object_type_code,
2062 object_sub_type_code,
2063 sequence_num,
2064 last_update_date,
2065 last_updated_by,
2066 creation_date,
2067 created_by,
2068 action_code,
2069 action_date,
2070 employee_id,
2071 note,
2072 object_revision_num,
2073 last_update_login,
2074 request_id,
2075 program_application_id,
2076 program_id,
2077 program_update_date,
2078 approval_path_id,
2079 offline_code)
2080 VALUES
2081 (x_po_release_id,
2082 'RELEASE',
2083 'BLANKET',
2084 1, -- Bug 13579433
2085 sysdate,
2086 req_line.last_updated_by,
2087 sysdate,
2088 req_line.last_updated_by,
2089 'SUBMIT',
2090 sysdate,
2091 req_line.agent_id,
2092 'AUTO RELEASE',
2093 0,
2094 req_line.last_update_login,
2095 0,
2096 0,
2097 0,
2098 '',
2099 null,
2100 null);
2101
2102 INSERT into PO_ACTION_HISTORY
2103 (object_id,
2104 object_type_code,
2105 object_sub_type_code,
2106 sequence_num,
2107 last_update_date,
2108 last_updated_by,
2109 creation_date,
2110 created_by,
2111 action_code,
2112 action_date,
2113 employee_id,
2114 note,
2115 object_revision_num,
2116 last_update_login,
2117 request_id,
2118 program_application_id,
2119 program_id,
2120 program_update_date,
2121 approval_path_id,
2122 offline_code)
2123 VALUES
2124 (x_po_release_id,
2125 'RELEASE',
2126 'BLANKET',
2127 2, -- Bug 13579433
2128 sysdate,
2129 req_line.last_updated_by,
2130 sysdate,
2131 req_line.last_updated_by,
2132 'APPROVE',
2133 sysdate,
2134 req_line.agent_id,
2135 'AUTO RELEASE',
2136 0,
2137 req_line.last_update_login,
2138 0,
2139 0,
2140 0,
2141 '',
2142 null,
2143 null);
2144
2145 END IF;
2146
2147 EXCEPTION
2148 WHEN OTHERS THEN
2149 raise_application_error(-20001,sqlerrm||'---'||msgbuf);
2150
2151 END WRAPUP;
2152
2153 /* ============================================================================
2154 NAME: GET_BEST_PRICE
2155 DESC: Get the best price for the sourced requisition line
2156 ARGS: req_line IN requisition_lines_cursor%rowtype
2157 x_conversion_rate IN number
2158 x_ship_to_location_id IN number
2159 RETURNS: best_price number
2160 ALGR: determine whether the price break is cumulative or not
2161 get the best price based on the price break type
2162
2163 ===========================================================================*/
2164
2165 FUNCTION GET_BEST_PRICE(req_line IN requisition_lines_cursor%rowtype,
2166 x_conversion_rate IN number,
2167 x_ship_to_location_id IN number)
2168 return number
2169 IS
2170 x_price_break_type varchar2(20) := '';
2171 x_best_price number := 0;
2172 x_price_break_quantity number :=0;
2173 x_po_line_price number := 0;
2174 BEGIN
2175
2176 /* Get the price break type CUMULATIVE or NON CUMULATIVE */
2177
2178 SELECT PRICE_BREAK_LOOKUP_CODE
2179 INTO x_price_break_type
2180 FROM PO_LINES
2181 WHERE PO_LINE_ID = req_line.blanket_po_line_id;
2182
2183 /* Get the price break quantity based on the price break type */
2184 /* This is done by calculating the released amount if required */
2185 /* and adding the current quantity to it */
2186
2187 IF (x_price_break_type = 'CUMULATIVE') THEN
2188
2189 -- Bug 521788, lpo, 03/26/98
2190 -- Ported SVAIDYAN's fix to r11.
2191 -- If there are no releases yet, then the sum would give a null value
2192 -- Hence, added a nvl for it.
2193
2194 SELECT nvl(SUM(QUANTITY - nvl(QUANTITY_CANCELLED, 0)), 0)
2195 INTO x_price_break_quantity
2196 FROM PO_LINE_LOCATIONS
2197 WHERE PO_LINE_ID = req_line.blanket_po_line_id
2198 AND SHIPMENT_TYPE <> 'PRICE BREAK';
2199
2200 -- End of fix. Bug 521788, lpo, 03/26/98
2201
2202 END IF;
2203
2204 IF (x_price_break_type = 'NON CUMULATIVE') THEN
2205 x_price_break_quantity := 0;
2206 END IF;
2207
2208 /* bug 1921133
2209 If we round the conversion rate as there will be inaccuracies in the
2210 final quantity. so we need to round the quantity
2211 after muliplying with the rate intead of the rate itself */
2212
2213 /* Bug 2654838 : If UOM in Blanket and Purchase requisition are same
2214 then we should not convert the quantity. */
2215
2216 if(x_conversion_rate=1) then
2217 x_price_break_quantity := x_price_break_quantity + req_line.quantity;
2218 else
2219 x_price_break_quantity := x_price_break_quantity
2220 + round(req_line.quantity * x_conversion_rate,5);
2221 end if;
2222
2223 /* Get the blanket line price */
2224
2225 SELECT UNIT_PRICE
2226 INTO x_po_line_price
2227 FROM PO_LINES
2228 WHERE PO_LINE_ID = req_line.blanket_po_line_id;
2229
2230 /* Determine the final price for the item shipment */
2231
2232 SELECT LEAST(NVL(MIN(PRICE_OVERRIDE), x_po_line_price), x_po_line_price)
2233 INTO x_best_price
2234 FROM PO_LINE_LOCATIONS
2235 WHERE SHIPMENT_TYPE = 'PRICE BREAK'
2236 AND PO_LINE_ID = req_line.blanket_po_line_id
2237 AND QUANTITY <= x_price_break_quantity
2238 AND (SHIP_TO_LOCATION_ID = NVL(x_ship_to_location_id,
2239 SHIP_TO_LOCATION_ID)
2240 OR
2241 SHIP_TO_LOCATION_ID IS NULL)
2242 AND (SHIP_TO_ORGANIZATION_ID
2243 = NVL(req_line.destination_organization_id,
2244 SHIP_TO_ORGANIZATION_ID)
2245 OR
2246 SHIP_TO_ORGANIZATION_ID IS NULL);
2247
2248 RETURN(x_best_price);
2249
2250 EXCEPTION
2251 WHEN OTHERS THEN
2252 raise_application_error(-20001,sqlerrm||'---'||msgbuf);
2253
2254 END GET_BEST_PRICE;
2255
2256
2257 /* ============================================================================
2258 NAME: CHECK_AMOUNT_LIMITS
2259 DESC: Perform document level amount limit checks
2260 ARGS: IN : x_old_po_header_id IN NUMBER
2261 ALGR: Get the amount of the current release
2262 Determine the total amount released against blanket if this
2263 release were to be included
2264 Get blanket maximum amount limit
2265 Get blanket minimum amount limit
2266 The total including this release must be >= than the
2267 minimum and <= the maximum
2268
2269 ===========================================================================*/
2270
2271 PROCEDURE CHECK_AMOUNT_LIMITS(x_old_po_header_id IN NUMBER,x_req_num IN varchar2,x_req_line_num IN NUMBER,
2272 x_check_status OUT NOCOPY VARCHAR2 -- Bug 2701147
2273 )
2274 IS
2275 release_amount number := 0;
2276 total_release_amount number := 0;
2277 min_release_amount number := 0;
2278 max_release_amount number :=0;
2279 x_error_msg varchar2(2000);
2280 x_po_num varchar2(20);
2281 BEGIN
2282 x_check_status := FND_API.G_RET_STS_SUCCESS; -- Bug 2701147
2283
2284 SELECT nvl(sum(poll.quantity * poll.price_override),0)
2285 INTO release_amount
2286 FROM po_line_locations poll
2287 WHERE poll.po_release_id = x_po_release_id
2288 AND poll.shipment_type = 'BLANKET';
2289
2290 SELECT nvl(sum(poll.quantity * poll.price_override),0)
2291 INTO total_release_amount
2292 FROM po_line_locations poll,
2293 po_releases por,
2294 po_headers poh
2295 WHERE poh.po_header_id = x_old_po_header_id
2296 AND poll.po_header_id = poh.po_header_id
2297 AND poll.shipment_type = 'BLANKET'
2298 AND poll.po_release_id = por.po_release_id
2299 AND (nvl(por.approved_flag,'N') = 'Y'
2300 OR por.po_release_id = x_po_release_id)
2301 AND nvl(poll.cancel_flag,'N') = 'N';
2302
2303 SELECT nvl(poh.min_release_amount,0)
2304 INTO min_release_amount
2305 FROM po_headers poh
2306 WHERE po_header_id = x_old_po_header_id;
2307
2308 SELECT nvl(poh.amount_limit,-1),segment1
2309 INTO max_release_amount,x_po_num
2310 FROM po_headers poh
2311 WHERE po_header_id = x_old_po_header_id;
2312
2313
2314 IF (release_amount < min_release_amount)
2315 THEN
2316 -- Bug 2701147 START
2317 -- Write an error message to the log file.
2318 x_error_msg := FND_MESSAGE.GET_STRING('PO',
2319 'PO_SUB_REL_AMT_LESS_MINREL_AMT');
2320 FND_FILE.put_line(FND_FILE.LOG,
2321 substr(g_reqmsg||g_delim||x_req_num||g_delim||g_linemsg||g_delim||
2322 x_req_line_num||g_delim||
2323 x_error_msg,1,240));
2324 x_check_status := FND_API.G_RET_STS_ERROR;
2325 -- Bug 2701147 END
2326 END IF;
2327
2328 IF (max_release_amount <> -1 AND total_release_amount > max_release_amount)
2329 THEN
2330 /* Bug#2057344 Added the below piece of code to write the message
2331 PO_REQ_REL_AMT_GRT_LIMIT_AMT to the log file when the Total Release amount is
2332 greater than the Amount in BPO */
2333 fnd_message.set_name ('PO', 'PO_REQ_REL_AMT_GRT_LIMIT_AMT');
2334 fnd_message.set_token('REQ_NUM', x_req_num);
2335 fnd_message.set_token('LINE_NUM', x_req_line_num);
2336 fnd_message.set_token('PO_NUM',x_po_num);
2337 x_error_msg := fnd_message.get;
2338 fnd_file.put_line(fnd_file.log,x_error_msg);
2339 x_check_status := FND_API.G_RET_STS_ERROR; -- Bug 2701147
2340 END IF;
2341
2342 EXCEPTION
2343 WHEN OTHERS THEN
2344 raise_application_error(-20001,sqlerrm||'---'||msgbuf);
2345
2346 END CHECK_AMOUNT_LIMITS;
2347
2348
2349 /* ============================================================================
2350 NAME: OE_DROP_SHIP
2351 DESC: OE drop ship call back
2352 update the so_drop_ship_sources table with PO info
2353 ARGS: IN : req_line IN requisition_lines_cursor%rowtype
2354 ===========================================================================*/
2355 PROCEDURE OE_DROP_SHIP(req_line IN requisition_lines_cursor%rowtype)
2356 IS
2357 x_p_api_version number:='';
2358 x_p_return_status varchar2(1):='';
2359 x_p_msg_count number:='';
2360 x_p_msg_data varchar2(2000):='';
2361 x_p_req_header_id NUMBER:='';
2362 x_p_req_line_id NUMBER:='';
2363 x_p_po_header_id number:='';
2364 x_p_po_line_id number:='';
2365 x_p_line_location_id number:='';
2366 x_p_po_release_id number:='';
2367 BEGIN
2368
2369 x_p_api_version := 1.0;
2370 x_p_req_line_id := req_line.requisition_line_id;
2371 x_p_po_header_id := req_line.blanket_po_header_id;
2372 x_p_po_line_id := req_line.blanket_po_line_id;
2373 x_p_line_location_id:= x_line_location_id; -- global variable
2374 x_p_po_release_id := x_po_release_id; -- global variable
2375
2376 BEGIN
2377 SELECT requisition_header_id
2378 INTO x_p_req_header_id
2379 FROM po_requisition_lines
2380 WHERE requisition_line_id = req_line.requisition_line_id;
2381 EXCEPTION
2382 WHEN NO_DATA_FOUND THEN raise;
2383 WHEN OTHERS THEN raise;
2384 END;
2385
2386 oe_drop_ship_grp.update_po_info(x_p_api_version,
2387 x_p_return_status,
2388 x_p_msg_count,
2389 x_p_msg_data,
2390 x_p_req_header_id,
2391 x_p_req_line_id,
2392 x_p_po_header_id,
2393 x_p_po_line_id,
2394 x_p_line_location_id,
2395 x_p_po_release_id
2396 );
2397 EXCEPTION
2398 WHEN OTHERS THEN
2399 raise_application_error(-20001,sqlerrm||'---'||msgbuf);
2400
2401 END OE_DROP_SHIP;
2402
2403 --- Bug 2701147 START
2404 /**
2405 * Private Procedure: get_req_info_from_po_dist
2406 * Modifies: none
2407 * Effects: Returns the requisition number and requisition line number
2408 * used to create the given release distribution.
2409 **/
2410 PROCEDURE get_req_info_from_po_dist (p_po_distribution_id IN NUMBER,
2411 x_req_num OUT NOCOPY
2412 PO_REQUISITION_HEADERS.segment1%TYPE,
2413 x_req_line_num OUT NOCOPY NUMBER)
2414 IS
2415 BEGIN
2416 SELECT prh.segment1, prl.line_num
2417 INTO x_req_num, x_req_line_num
2418 FROM po_distributions pod,
2419 po_req_distributions prd,
2420 po_requisition_lines prl,
2421 po_requisition_headers prh
2422 WHERE pod.po_distribution_id = p_po_distribution_id
2423 AND pod.req_distribution_id = prd.distribution_id
2424 AND prl.requisition_line_id = prd.requisition_line_id
2425 AND prh.requisition_header_id = prl.requisition_header_id;
2426 EXCEPTION
2427 WHEN NO_DATA_FOUND THEN
2428 x_req_num := NULL;
2429 x_req_line_num := NULL;
2430 END get_req_info_from_po_dist;
2431
2432 /**
2433 * Private Procedure: get_req_info_from_po_dist
2434 * Modifies: none
2435 * Effects: Returns the requisition number and requisition line number
2436 * used to create the given release shipment.
2437 * Note that Create Releases only creates one distribution per shipment.
2438 **/
2439 PROCEDURE get_req_info_from_po_shipment (p_line_location_id IN NUMBER,
2440 x_req_num OUT NOCOPY
2441 PO_REQUISITION_HEADERS.segment1%TYPE,
2442 x_req_line_num OUT NOCOPY NUMBER)
2443 IS
2444 l_po_distribution_id NUMBER;
2445 BEGIN
2446 SELECT min(pod.po_distribution_id)
2447 INTO l_po_distribution_id
2448 FROM po_distributions pod
2449 WHERE pod.line_location_id = p_line_location_id;
2450
2451 get_req_info_from_po_dist (l_po_distribution_id,
2452 x_req_num, x_req_line_num);
2453 EXCEPTION
2454 WHEN NO_DATA_FOUND THEN
2455 x_req_num := NULL;
2456 x_req_line_num := NULL;
2457 END get_req_info_from_po_shipment;
2458
2459 /**
2460 * Private Procedure: CHECK_REL_REQPRICE
2461 * Note: This procedure is adapted from the Document Submission Checks API
2462 * (PO_DOCUMENT_CHECKS_PVT.check_po_rel_reqprice).
2463 * Modifies: Inserts error msgs in the concurrent program log.
2464 * Effects: This procedure checks that the release shipment price is
2465 * within the tolerance of the requisition line.
2466 * Returns:
2467 * x_check_status: FND_API.G_RET_STS_SUCCESS if release passes all
2468 * the tolerance checks
2469 * FND_API.G_RET_STS_ERROR if at least one check fails
2470 */
2471 PROCEDURE check_rel_reqprice(x_check_status OUT NOCOPY VARCHAR2) IS
2472
2473 l_textline po_online_report_text.text_line%TYPE := NULL;
2474 l_api_name CONSTANT varchar2(40) := 'CHECK_REL_REQPRICE';
2475 l_progress VARCHAR2(3);
2476
2477 l_enforce_price_tolerance po_system_parameters.enforce_price_change_allowance%TYPE;
2478 l_enforce_price_amount po_system_parameters.enforce_price_change_amount%TYPE;
2479 l_amount_tolerance po_system_parameters.price_change_amount%TYPE;
2480
2481 TYPE unit_of_measure IS TABLE of PO_LINES.unit_meas_lookup_code%TYPE;
2482 TYPE NumTab IS TABLE of NUMBER;
2483 l_ship_price_in_base_curr NumTab;
2484 l_ship_unit_of_measure unit_of_measure;
2485 l_ship_num NumTab;
2486 l_line_num NumTab;
2487 l_quantity NumTab;
2488 l_item_id NumTab;
2489 l_line_location_id NumTab;
2490
2491 --For Req Cursor
2492 l_req_unit_of_measure unit_of_measure;
2493 l_req_line_unit_price NumTab;
2494 l_po_req_line_num NumTab;
2495 l_po_req_ship_num NumTab;
2496 l_po_req_quantity NumTab;
2497
2498 l_ship_price_ext_precn NUMBER;
2499 l_shipment_to_req_rate NUMBER := 0;
2500 l_price_tolerance_allowed NUMBER := 0;
2501
2502 l_req_num PO_REQUISITION_HEADERS.segment1%TYPE;
2503 L_req_line_num NUMBER;
2504
2505 /*
2506 ** Setup the Release select cursor
2507 ** Select shipment price and convert it to base currency.
2508 ** this is done by taking the distribution rate and applying
2509 ** it evenly over all distributions. Additionally get the
2510 ** shipment unit of measure, quantity, and item_id to be
2511 ** passed to the UomC function. Get the shipment_num and
2512 ** line_num to be passed to the pooinsingle function.
2513 */
2514 CURSOR rel_shipment_cursor (p_document_id NUMBER) IS
2515 SELECT nvl(max(POLL.price_override) *
2516 sum(decode(plt.order_type_lookup_code,'AMOUNT',1,nvl(POD.rate,1))*
2517 (POD.quantity_ordered -
2518 nvl(POD.quantity_cancelled, 0))) /
2519 (max(POLL.quantity) -
2520 nvl(max(POLL.quantity_cancelled),0)), -1) Price,
2521 POL.unit_meas_lookup_code uom,
2522 nvl(POLL.shipment_num,0) ship_num,
2523 nvl(POL.line_num,0) line_num,
2524 nvl(POLL.quantity,0) quantity,
2525 nvl(POL.item_id,0) item_id,
2526 nvl( POLL.line_location_id,0) line_loc_id
2527 FROM PO_LINE_LOCATIONS POLL,
2528 PO_LINE_TYPES PLT,
2529 PO_LINES POL,
2530 PO_DISTRIBUTIONS POD
2531 WHERE POLL.po_line_id = POL.po_line_id
2532 AND POLL.line_location_id = POD.line_location_id
2533 AND POLL.po_release_id = p_document_id
2534 AND POL.line_type_id = PLT.line_type_id
2535 AND nvl(POLL.cancel_flag,'N') <> 'Y'
2536 AND nvl(POLL.closed_code,'OPEN') <> 'FINALLY CLOSED'
2537 GROUP BY POL.unit_meas_lookup_code, nvl(POLL.shipment_num,0),
2538 nvl(POL.line_num,0), nvl(POLL.quantity,0),
2539 nvl(POL.item_id,0), POLL.price_override,
2540 nvl(POLL.line_location_id,0);
2541
2542 CURSOR req_price_tol_cursor(p_line_location_id NUMBER) IS
2543 SELECT min(PRL.unit_price),
2544 PRL.unit_meas_lookup_code,
2545 min(POL.line_num),
2546 min(POLL.shipment_num)
2547 FROM PO_REQUISITION_LINES PRL,
2548 PO_LINE_LOCATIONS POLL,
2549 PO_LINES POL
2550 WHERE PRL.line_location_id = POLL.line_location_id
2551 AND POLL.line_location_id = p_line_location_id
2552 AND PRL.unit_price >= 0
2553 AND POLL.po_line_id = POL.po_line_id
2554 GROUP BY PRL.unit_meas_LOOKUP_code;
2555
2556 CURSOR req_price_amt_cursor(p_line_location_id NUMBER) IS
2557 SELECT min(PRL.unit_price),
2558 PRL.unit_meas_lookup_code,
2559 sum(PD.quantity_ordered),
2560 min(POL.line_num),
2561 min(POLL.shipment_num)
2562 FROM PO_REQUISITION_LINES PRL,
2563 PO_LINE_LOCATIONS POLL,
2564 PO_LINES POL,
2565 PO_DISTRIBUTIONS PD,
2566 PO_REQ_DISTRIBUTIONS PRD
2567 WHERE POLL.line_location_id = p_line_location_id
2568 AND POLL.po_line_id = POL.po_line_id
2569 AND PRL.unit_price >= 0
2570 AND POLL.line_location_id = PD.line_location_id
2571 AND PD.req_distribution_id = PRD.distribution_id
2572 AND PRD.requisition_line_id = PRL.requisition_line_id
2573 GROUP BY PRL.requisition_line_id, PRL.unit_meas_lookup_code;
2574
2575 BEGIN
2576 x_check_status := FND_API.G_RET_STS_SUCCESS;
2577
2578 l_progress := '000';
2579 IF g_fnd_debug = 'Y' THEN
2580 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2581 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || '.'||l_api_name||'.'
2582 || l_progress,'PO REQ: Price, Amount Toleance check');
2583 END IF;
2584 END IF;
2585
2586 --check if this check is enforced
2587 SELECT nvl(enforce_price_change_allowance, 'N'),
2588 nvl(enforce_price_change_amount, 'N'),
2589 nvl(price_change_amount, -1)
2590 INTO l_enforce_price_tolerance,
2591 l_enforce_price_amount,
2592 l_amount_tolerance
2593 FROM po_system_parameters;
2594
2595 l_progress := '001';
2596 IF g_fnd_debug = 'Y' THEN
2597 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2598 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || '.'||l_api_name||'.'
2599 || l_progress,'Is price tol check enforced '||l_enforce_price_tolerance
2600 || ' Is price amount check enforced ' || l_enforce_price_amount);
2601 END IF;
2602 END IF;
2603
2604 --if we are not enforcing the price tolerance checks then return success
2605 IF l_enforce_price_tolerance = 'N' AND l_enforce_price_amount = 'N' THEN
2606 RETURN;
2607 END IF;
2608
2609 l_progress := '002';
2610 OPEN rel_shipment_cursor(x_po_release_id);
2611
2612 FETCH rel_shipment_cursor BULK COLLECT INTO
2613 l_ship_price_in_base_curr,
2614 l_ship_unit_of_measure,
2615 l_ship_num,
2616 l_line_num,
2617 l_quantity,
2618 l_item_id,
2619 l_line_location_id;
2620
2621 CLOSE rel_shipment_cursor;
2622
2623 l_progress := '004';
2624 FOR shipment_line IN 1..l_line_location_id.COUNT LOOP
2625
2626 --Bug 1991546
2627 --Obtain extended precision which is used for rounding while
2628 --checking for tolerance
2629 BEGIN
2630 SELECT round(l_ship_price_in_base_curr(shipment_line),nvl(FND.extended_precision,5))
2631 INTO l_ship_price_ext_precn
2632 FROM FND_CURRENCIES FND, PO_HEADERS POH,
2633 PO_LINE_LOCATIONS POLL
2634 WHERE POH.po_header_id = POLL.po_header_id
2635 AND POH.currency_code = FND.currency_code
2636 AND POLL.line_location_id = l_line_location_id(shipment_line);
2637 EXCEPTION
2638 WHEN NO_DATA_FOUND THEN
2639 l_ship_price_ext_precn := l_ship_price_in_base_curr(shipment_line);
2640 WHEN OTHERS THEN
2641 RAISE;
2642 END;
2643
2644 l_progress := '005';
2645 --Do price tolerance check
2646 IF l_enforce_price_tolerance = 'Y' THEN
2647
2648 IF g_fnd_debug = 'Y' THEN
2649 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2650 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || '.'||l_api_name||'.'
2651 || l_progress,'Doing Price Tolerance check');
2652 END IF;
2653 END IF;
2654 OPEN req_price_tol_cursor(l_line_location_id(shipment_line));
2655
2656 FETCH req_price_tol_cursor BULK COLLECT INTO
2657 l_req_line_unit_price,
2658 l_req_unit_of_measure,
2659 l_po_req_line_num,
2660 l_po_req_ship_num;
2661
2662 CLOSE req_price_tol_cursor;
2663
2664 l_progress := '006';
2665 FOR req_line IN 1..l_req_line_unit_price.COUNT LOOP
2666
2667 /*
2668 ** If a row was returned then the PO or Release is associated
2669 ** with a requisition and you should continue with the logic.
2670 ** If a row was not returned. It does not mean that an error
2671 ** occurred, it meas that the submission check does not apply
2672 ** to this document.
2673 */
2674
2675 --Call function that returns the shipment price
2676 --converted to the correct UOM.
2677 po_uom_s.po_uom_conversion(
2678 l_ship_unit_of_measure(shipment_line),
2679 l_req_unit_of_measure(req_line),
2680 l_item_id(shipment_line),
2681 l_shipment_to_req_rate);
2682
2683 IF l_shipment_to_req_rate = 0.0 THEN
2684 l_shipment_to_req_rate :=1.0;
2685 END IF;
2686 l_progress := '007';
2687 --Get the tolerance allowed. This is the tolerance
2688 --allowed between the requisition price and
2689 --shipment price.
2690 -- bug 432746.
2691 SELECT NVL(MSI.price_tolerance_percent/100,
2692 NVL(POSP.price_change_allowance/100,-1))
2693 INTO l_price_tolerance_allowed
2694 FROM MTL_SYSTEM_ITEMS MSI,
2695 PO_SYSTEM_PARAMETERS POSP,
2696 FINANCIALS_SYSTEM_PARAMETERS FSP
2697 WHERE msi.inventory_item_id(+) = l_item_id(shipment_line)
2698 AND MSI.organization_id(+) = FSP.inventory_organization_id;
2699
2700 l_progress := '008';
2701 IF l_price_tolerance_allowed <> -1 AND
2702 l_req_line_unit_price(req_line) <> 0 THEN
2703
2704 /*
2705 ** Check to see if the rate returned from the function
2706 ** multiplied by the shipment price in base currency and
2707 ** then divided by the requisition price is less then
2708 ** the tolerance. If not, call the function to
2709 ** insert into the Online Report Text Table.
2710 **
2711 ** The following formula will cost precision erro when the
2712 ** increase equals to the tolerance.
2713 ** Patched as part of bug 432746.
2714 **
2715 **if ((((ship_price_in_base_curr * rate) /
2716 ** req_line_unit_price[i]) -1) <= tolerance)
2717 */
2718
2719 /* Bug 638073
2720 the formula for tolerance check should be
2721 ship_price_in_base_curr/ req_line_unit_pric e[i] *rate
2722 since rate is the conversion from shipment uom to req uom
2723 */
2724
2725 /* svaidyan 09/10/98 726568 Modified the price tolerance
2726 to check against tolerance + 1.000001. This is because,
2727 the reqs sourced to a blanket store the unit price rounded
2728 to 5 decimal places and hence we compare only upto the 5th
2729 decimal place.
2730 */
2731 IF (((l_ship_price_ext_precn) /
2732 (l_req_line_unit_price(req_line) *
2733 l_shipment_to_req_rate ))
2734 > (l_price_tolerance_allowed + 1.000001))
2735 THEN
2736 l_progress := '009';
2737 --Report the price tolerance error
2738 l_textline := FND_MESSAGE.GET_STRING('PO',
2739 'PO_SUB_REQ_PRICE_TOL_EXCEED');
2740 get_req_info_from_po_shipment(
2741 l_line_location_id(shipment_line),
2742 l_req_num, l_req_line_num);
2743 FND_FILE.put_line(FND_FILE.LOG,
2744 substr(g_reqmsg||g_delim||
2745 l_req_num||g_delim||g_linemsg||g_delim||
2746 l_req_line_num||g_delim||
2747 l_textline,1,240));
2748 x_check_status := FND_API.G_RET_STS_ERROR;
2749
2750 END IF; --check for tolerance
2751
2752 END IF; --check l_price_tolerance_allowed
2753
2754 END LOOP; --req line
2755
2756 END IF; --price tolerance check
2757
2758 l_progress := '010';
2759
2760 --Do price 'not to exceed' amount check
2761 IF l_enforce_price_amount = 'Y' THEN
2762 IF g_fnd_debug = 'Y' THEN
2763 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2764 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || '.'||l_api_name||'.'
2765 || l_progress,'Doing Not to exceed amt check');
2766 END IF;
2767 END IF;
2768
2769 OPEN req_price_amt_cursor(l_line_location_id(shipment_line));
2770
2771 FETCH req_price_amt_cursor BULK COLLECT INTO
2772 l_req_line_unit_price,
2773 l_req_unit_of_measure,
2774 l_po_req_quantity,
2775 l_po_req_line_num,
2776 l_po_req_ship_num;
2777
2778 CLOSE req_price_amt_cursor;
2779
2780 l_progress := '011';
2781 FOR req_line IN 1..l_req_line_unit_price.COUNT LOOP
2782
2783 /*
2784 ** If a row was returned then the PO or Release is associated
2785 ** with a requisition and you should continue with the logic.
2786 ** If a row was not returned. It does not mean that an error
2787 ** occurred, it meas that the submission check does not apply
2788 ** to this document.
2789 */
2790
2791 --Call function that returns the shipment price
2792 --converted to the correct UOM.
2793 po_uom_s.po_uom_conversion(
2794 l_ship_unit_of_measure(shipment_line),
2795 l_req_unit_of_measure(req_line),
2796 l_item_id(shipment_line),
2797 l_shipment_to_req_rate);
2798
2799 IF l_shipment_to_req_rate = 0.0 THEN
2800 l_shipment_to_req_rate :=1.0;
2801 END IF;
2802
2803
2804 IF l_amount_tolerance >= 0 AND
2805 l_req_line_unit_price(req_line) <> 0 THEN
2806
2807 --do the amount check
2808 --makes sure the requisition amount and
2809 --PO amount for each shipment line is within the value
2810 --defined in the column PRICE_CHANGE_AMOUNT of table
2811 --PO_SYSTEM_PARAMETERS.
2812 IF ((l_ship_price_ext_precn -
2813 (l_req_line_unit_price(req_line) *
2814 l_shipment_to_req_rate))
2815 * l_po_req_quantity(req_line)
2816 > l_amount_tolerance)
2817 THEN
2818 l_progress := '012';
2819 --Report the price amount exceeded error
2820 l_textline :=
2821 FND_MESSAGE.GET_STRING('PO', 'PO_SUB_REQ_AMT_TOL_EXCEED');
2822 get_req_info_from_po_shipment(
2823 l_line_location_id(shipment_line),
2824 l_req_num, l_req_line_num);
2825 FND_FILE.put_line(FND_FILE.LOG,
2826 substr(g_reqmsg||g_delim||
2827 l_req_num||g_delim||g_linemsg||g_delim||
2828 l_req_line_num||g_delim||
2829 l_textline,1,240));
2830 x_check_status := FND_API.G_RET_STS_ERROR;
2831
2832 END IF; --amount check
2833
2834 END IF; --check l_amount_tolerance_allowed
2835
2836 END LOOP; --req line
2837
2838 END IF; --not to exceed amount check
2839
2840 END LOOP; --for shipment_line
2841
2842 EXCEPTION
2843 WHEN OTHERS THEN
2844 FND_FILE.put_line(FND_FILE.LOG,
2845 c_log_head || l_api_name || ' exception; location: '
2846 || l_progress || ' SQL code: ' || sqlcode);
2847 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
2848 FND_LOG.string(FND_LOG.level_unexpected, c_log_head ||
2849 l_api_name || '.others_exception', 'EXCEPTION: Location is '
2850 || l_progress || ' SQL CODE is '||sqlcode);
2851 END IF;
2852 raise_application_error(-20001,sqlerrm||'---'||msgbuf);
2853 END CHECK_REL_REQPRICE;
2854
2855 /**
2856 * Private Procedure: preapproval_checks
2857 * Modifies: Writes error messages to the concurrent program log.
2858 * Effects: Performs checks that must be successful for an approved release
2859 * to be created.
2860 * Returns:
2861 * x_check_status: FND_API.G_RET_STS_SUCCESS if release passes all
2862 * pre-approval checks
2863 * FND_API.G_RET_STS_ERROR if at least one check fails
2864 **/
2865 PROCEDURE preapproval_checks( p_po_header_id IN NUMBER,
2866 p_req_num IN VARCHAR2,
2867 p_req_line_num IN NUMBER,
2868 x_check_status OUT NOCOPY VARCHAR2)
2869 IS
2870 l_api_name CONSTANT varchar2(30) := 'PREAPPROVAL_CHECKS';
2871
2872 TYPE ErrorMessagesTab is TABLE of PO_ONLINE_REPORT_TEXT.text_line%TYPE
2873 INDEX by BINARY_INTEGER;
2874 l_error_messages ErrorMessagesTab;
2875 l_textline PO_ONLINE_REPORT_TEXT.text_line%TYPE;
2876
2877 TYPE NumTab is TABLE of NUMBER INDEX by BINARY_INTEGER;
2878 l_line_location_id NumTab;
2879 l_dist_id NumTab;
2880
2881 l_req_num PO_REQUISITION_HEADERS.segment1%TYPE;
2882 L_req_line_num NUMBER;
2883
2884 l_check_status VARCHAR2(1);
2885 l_progress VARCHAR2(3) := '001';
2886
2887 -- <JFMIP Vendor Registration FPJ Start>
2888 -- If the profile option 'Enable Transaction Code' is set to Yes, then
2889 -- it is a federal instance, and we need to check vendor site registration
2890 -- status when necessary
2891 l_federal_instance VARCHAR2(1);
2892 l_vendor_id PO_HEADERS.vendor_id%TYPE;
2893 l_vendor_site_id PO_HEADERS.vendor_site_id%TYPE;
2894 l_valid_registration BOOLEAN := FALSE;
2895 -- <JFMIP Vendor Registration FPJ End>
2896 l_sob_id FINANCIALS_SYSTEM_PARAMS_ALL.set_of_books_id%TYPE;
2897 l_purch_enc_flag FINANCIALS_SYSTEM_PARAMS_ALL.purch_encumbrance_flag%TYPE;
2898 BEGIN
2899
2900 x_check_status := FND_API.G_RET_STS_SUCCESS;
2901 --<R12 SLA START>
2902 l_federal_instance := PO_CORE_S.Check_Federal_Instance(
2903 PO_MOAC_UTILS_PVT.Get_Current_Org_Id);
2904 --<R12 SLA END>
2905 IF g_fnd_debug = 'Y' THEN
2906 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2907 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || '.'||l_api_name||'.'
2908 || l_progress,'Perform pre-approval checks on the release');
2909 END IF;
2910 END IF;
2911
2912 -- First check the document level amount limits.
2913 check_amount_limits(p_po_header_id, p_req_num, p_req_line_num,l_check_status);
2914 IF (l_check_status = FND_API.G_RET_STS_ERROR) THEN
2915 x_check_status := FND_API.G_RET_STS_ERROR;
2916 END IF;
2917
2918 -- The following checks were adapted from the Document Submission
2919 -- Checks API (PO_DOCUMENT_CHECKS_PVT.check_releases):
2920
2921 ----------------------------------------------
2922
2923 l_progress := '012';
2924 IF g_fnd_debug = 'Y' THEN
2925 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2926 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || '.'||l_api_name||'.'
2927 || l_progress,'REL 12: Amount released check for min release amt');
2928 END IF;
2929 END IF;
2930
2931 -- Check 12: The Amount being released for all shipments for a particular line
2932 -- must be greater than the min release amount specified in agreement line
2933 -- PO_SUB_REL_SHIPAMT_LESS_MINREL
2934
2935 l_textline := FND_MESSAGE.GET_STRING('PO','PO_SUB_REL_SHIPAMT_LESS_MINREL');
2936
2937 --Bug 12553671 Start
2938 /*SELECT substr(g_reqmsg||g_delim||p_req_num||g_delim||
2939 g_linemsg||g_delim||p_req_line_num||
2940 g_delim||l_textline||g_delim||POL.min_release_amount,1,240)*/
2941 SELECT substr(g_bpamsg||g_delim||POH.segment1||g_delim||
2942 g_linemsg||g_delim||POL.line_num||
2943 g_delim||l_textline||g_delim||POL.min_release_amount,1,240)
2944 --Bug 12553671 end
2945 BULK COLLECT INTO l_error_messages
2946 FROM PO_LINES POL,PO_RELEASES POR,PO_LINE_LOCATIONS PLL
2947 , PO_HEADERS POH --Bug 12553671, Added table
2948 WHERE POH.po_header_id = POL.po_header_id --Bug 12553671
2949 AND PLL.po_release_id = POR.po_release_id
2950 AND PLL.po_release_id = x_po_release_id
2951 AND POL.po_line_id = PLL.po_line_id
2952 AND POL.min_release_amount is not null
2953 AND POL.min_release_amount >
2954 --Bug 10403684 start. Sync the following portion code with the submition check in PO_DOCUMENT_CHECKS_PVT.check_releases()
2955 ( SELECT decode ( sum ( decode ( PLL2.quantity /*Bug 5028960 pol.quantity */
2956 , NULL , PLL2.amount - nvl(PLL2.amount_cancelled,0)
2957 , PLL2.quantity - nvl(PLL2.quantity_cancelled,0)
2958 )
2959 )
2960 , 0 , POL.min_release_amount
2961 , sum ( decode ( PLL2.quantity /*Bug 5028960 pol.quantity */
2962 , NULL , PLL2.amount - nvl(PLL2.amount_cancelled,0)
2963 , ( ( PLL2.quantity - nvl(PLL2.quantity_cancelled,0) )
2964 * PLL2.price_override
2965 )
2966 )
2967 )
2968 )
2969 FROM PO_LINE_LOCATIONS PLL2
2970 WHERE PLL2.po_line_id = POL.po_line_id
2971 AND PLL2.po_release_id = POR.po_release_id
2972 AND PLL2.shipment_type in ('BLANKET', 'SCHEDULED')
2973 )
2974 --Bug 10403684 end
2975 /* (SELECT
2976 decode(sum(nvl(PLL2.quantity,0)-nvl(PLL2.quantity_cancelled,0)),
2977 0,POL.min_release_amount,
2978 sum((nvl(PLL2.quantity,0)-nvl(PLL2.quantity_cancelled,0))
2979 *PLL2.price_override))
2980 FROM PO_LINE_LOCATIONS PLL2
2981 WHERE PLL2.po_line_id = POL.po_line_id
2982 AND PLL2.po_release_id = POR.po_release_id
2983 AND PLL2.shipment_type in ('BLANKET', 'SCHEDULED'))*/
2984 GROUP BY POH.segment1, POL.line_num,POL.min_release_amount; --12553671
2985
2986 FOR i IN 1..l_error_messages.COUNT LOOP
2987 FND_FILE.put_line(FND_FILE.LOG, l_error_messages(i));
2988 END LOOP;
2989 IF l_error_messages.COUNT > 0 THEN
2990 x_check_status := FND_API.G_RET_STS_ERROR;
2991 END IF;
2992
2993 ----------------------------------------------
2994
2995 l_progress := '013';
2996 IF g_fnd_debug = 'Y' THEN
2997 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2998 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || '.'||l_api_name||'.'
2999 || l_progress,'REL 13: GL date check ');
3000 END IF;
3001 END IF;
3002
3003 -- Check 13: The Release GL date should be within an open purchasing period
3004 -- PO_SUB_REL_INVALID_GL_DATE
3005
3006 -- bug 4963886
3007 -- The query had the check for purchasing encumbrance. Moved it
3008 -- out into an if block so the query is only conditionally executed
3009 -- Also removed the need for fsp by introducing the bind variable
3010 -- for set_of_books_id
3011
3012 SELECT NVL(purch_encumbrance_flag,'N'), set_of_books_id
3013 INTO l_purch_enc_flag, l_sob_id
3014 FROM FINANCIALS_SYSTEM_PARAMETERS;
3015
3016 IF l_purch_enc_flag = 'Y' THEN
3017
3018 -- bug 3296181
3019 -- Changed the message name.
3020 l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_PDOI_INVALID_GL_ENC_PER');
3021 SELECT substr(l_textline,1,240),
3022 POD.po_distribution_id
3023 BULK COLLECT INTO l_error_messages, l_dist_id
3024 FROM PO_DISTRIBUTIONS POD, PO_LINE_LOCATIONS PLL, PO_LINES POL
3025 WHERE POD.line_location_id = PLL.line_location_id
3026 AND PLL.po_release_id = x_po_release_id
3027 AND POL.po_line_id = PLL.po_line_id
3028 AND nvl(POD.encumbered_flag,'N') = 'N'
3029 AND nvl(PLL.cancel_flag,'N') = 'N'
3030 AND nvl(PLL.closed_code,'OPEN') <> 'FINALLY CLOSED'
3031 AND not exists
3032 (SELECT 'find if the GL date is not within Open period'
3033 from GL_PERIOD_STATUSES PS1, GL_PERIOD_STATUSES PS2,
3034 GL_SETS_OF_BOOKS GSOB
3035 WHERE PS1.application_id = 101
3036 AND PS1.set_of_books_id = l_sob_id
3037 AND PS1.closing_status IN ('O','F')
3038 AND trunc(nvl(POD.GL_ENCUMBERED_DATE,PS1.start_date))
3039 BETWEEN trunc(PS1.start_date) AND trunc(PS1.end_date)
3040 AND PS1.period_year <= GSOB.latest_encumbrance_year
3041 AND PS1.period_name = PS2.period_name
3042 AND PS2.application_id = 201
3043 AND PS2.closing_status = 'O'
3044 AND PS2.set_of_books_id = l_sob_id
3045 AND GSOB.set_of_books_id = l_sob_id);
3046
3047 FOR i IN 1..l_error_messages.COUNT LOOP
3048 get_req_info_from_po_dist(l_dist_id(i),
3049 l_req_num, l_req_line_num);
3050 FND_FILE.put_line(FND_FILE.LOG,
3051 g_reqmsg||g_delim||l_req_num||g_delim||
3052 g_linemsg||g_delim||l_req_line_num||g_delim||
3053 l_error_messages(i));
3054 END LOOP;
3055 IF l_error_messages.COUNT > 0 THEN
3056 x_check_status := FND_API.G_RET_STS_ERROR;
3057 END IF;
3058
3059 END IF; -- l_purch_enc_flag check
3060
3061 ----------------------------------------------
3062
3063 l_progress := '014';
3064 IF g_fnd_debug = 'Y' THEN
3065 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3066 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || '.'||l_api_name||'.'
3067 || l_progress,'Rel 14: UOM Interclass conversions check');
3068 END IF;
3069 END IF;
3070
3071 -- Check 14: Invalid Interclass conversions between UOMs should not be allowed
3072 -- PO_SUB_UOM_CLASS_CONVERSION, PO_SUB_REL_INVALID_CLASS_CONV
3073 -- Message inserted is:
3074 --'Following Interclass UOM conversion is not defined or
3075 -- is disabled <UOM1> <UOM2>'
3076 -- Bug #1630662
3077 l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_UOM_CLASS_CONVERSION');
3078 SELECT substr(l_textline||g_delim||
3079 MTL1.uom_class||' , '||MTL2.uom_class,1,240),
3080 POLL.line_location_id
3081 BULK COLLECT INTO l_error_messages, l_line_location_id
3082 FROM MTL_UOM_CLASS_CONVERSIONS MOU, PO_LINE_LOCATIONS POLL,
3083 PO_LINES POL, MTL_UOM_CLASSES_TL MTL1,
3084 MTL_UOM_CLASSES_TL MTL2
3085 WHERE MOU.inventory_item_id = POL.item_id
3086 AND (NVL(MOU.disable_date, TRUNC(SYSDATE)) + 1) < TRUNC(SYSDATE)
3087 AND POL.po_line_id = POLL.po_line_id
3088 AND POLL.po_release_id = x_po_release_id
3089 AND MOU.from_uom_class = MTL1.uom_class
3090 AND MOU.to_uom_class = MTL2.uom_class
3091 AND EXISTS
3092 (SELECT 'uom conversion exists'
3093 FROM MTL_UNITS_OF_MEASURE MUM
3094 WHERE POL.unit_meas_lookup_code = MUM.unit_of_measure
3095 AND MOU.to_uom_class = MUM.uom_class);
3096
3097 FOR i IN 1..l_error_messages.COUNT LOOP
3098 get_req_info_from_po_shipment(l_line_location_id(i),
3099 l_req_num, l_req_line_num);
3100 FND_FILE.put_line(FND_FILE.LOG,
3101 g_reqmsg||g_delim||l_req_num||g_delim||
3102 g_linemsg||g_delim||l_req_line_num||g_delim||
3103 l_error_messages(i));
3104 FND_FILE.put_line(FND_FILE.LOG, l_error_messages(i));
3105 END LOOP;
3106 IF l_error_messages.COUNT > 0 THEN
3107 x_check_status := FND_API.G_RET_STS_ERROR;
3108 END IF;
3109
3110 --------------------------------------------------
3111
3112 l_progress := '015';
3113 IF g_fnd_debug = 'Y' THEN
3114 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3115 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || '.'||l_api_name||'.'
3116 || l_progress,'REL 15: Item restricted check ');
3117 END IF;
3118 END IF;
3119
3120 -- Check 15: If an item is restricted then the Purchase Order Vendor
3121 -- must be listed in the Approved Suppliers List table and must be approved.
3122 -- PO_SUB_ITEM_NOT_APPROVED_REL
3123 -- Bug# 2461828
3124 l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_ITEM_NOT_APPROVED_REL');
3125 SELECT substr(g_reqmsg||g_delim||p_req_num||g_delim||
3126 g_linemsg||g_delim||p_req_line_num||
3127 g_delim||l_textline,1,240)
3128 BULK COLLECT INTO l_error_messages
3129 FROM MTL_SYSTEM_ITEMS MSI, PO_LINE_LOCATIONS PLL,
3130 PO_RELEASES POR,PO_LINES POL, PO_HEADERS POH,
3131 FINANCIALS_SYSTEM_PARAMETERS FSP
3132 WHERE POR.po_release_id = x_po_release_id
3133 AND POR.po_header_id = POH.po_header_id
3134 AND POR.po_header_id = POL.po_header_id
3135 AND POL.po_line_id = PLL.po_line_id
3136 AND POR.po_release_id = PLL.po_release_id
3137 AND MSI.organization_id = PLL.SHIP_TO_ORGANIZATION_id
3138 AND MSI.inventory_item_id = POL.item_id
3139 AND POL.item_id is not null
3140 AND nvl(PLL.closed_code,'OPEN') <> 'FINALLY CLOSED'
3141 AND nvl(POL.cancel_flag,'N') = 'N'
3142 AND nvl(PLL.cancel_flag,'N') = 'N'
3143 AND nvl(MSI.must_use_approved_vendor_flag,'N') = 'Y'
3144 AND not exists
3145 (SELECT sum(decode(ASR.allow_action_flag, 'Y', 1, -100))
3146 FROM PO_APPROVED_SUPPLIER_LIS_VAL_V ASL, PO_ASL_STATUS_RULES ASR
3147 WHERE ASL.using_organization_id in (PLL.ship_to_organization_id, -1)
3148 AND ASL.vendor_id = POH.vendor_id
3149 AND nvl(ASL.vendor_site_id, POH.vendor_site_id) = POH.vendor_site_id
3150 AND ASL.item_id = POL.item_id
3151 AND ASL.asl_status_id = ASR.status_id
3152 AND ASR.business_rule = '1_PO_APPROVAL'
3153 HAVING sum(decode(ASR.allow_action_flag, 'Y', 1, -100)) > 0
3154 UNION ALL
3155 SELECT sum(decode(ASR.allow_action_flag, 'Y', 1, -100))
3156 FROM PO_APPROVED_SUPPLIER_LIS_VAL_V ASL, PO_ASL_STATUS_RULES ASR
3157 WHERE ASL.using_organization_id in (PLL.ship_to_organization_id , -1)
3158 AND ASL.vendor_id = POH.vendor_id
3159 AND nvl(ASL.vendor_site_id, POH.vendor_site_id) = POH.vendor_site_id
3160 AND ASL.item_id is NULL
3161 AND not exists
3162 (SELECT ASL1.ASL_ID
3163 FROM PO_APPROVED_SUPPLIER_LIS_VAL_V ASL1
3164 WHERE ASL1.ITEM_ID = POL.item_id
3165 AND ASL1.using_organization_id in (PLL.ship_to_organization_id, -1))
3166 AND ASL.category_id in
3167 (SELECT MIC.category_id
3168 FROM MTL_ITEM_CATEGORIES MIC
3169 WHERE MIC.inventory_item_id = POL.item_id
3170 AND MIC.organization_id = PLL.ship_to_organization_id)
3171 AND ASL.asl_status_id = ASR.status_id
3172 AND ASR.business_rule = '1_PO_APPROVAL'
3173 HAVING sum(decode(ASR.allow_action_flag, 'Y', 1, -100)) > 0);
3174
3175 FOR i IN 1..l_error_messages.COUNT LOOP
3176 FND_FILE.put_line(FND_FILE.LOG, l_error_messages(i));
3177 END LOOP;
3178 IF l_error_messages.COUNT > 0 THEN
3179 x_check_status := FND_API.G_RET_STS_ERROR;
3180 END IF;
3181
3182 ---------------------------------------------
3183
3184 l_progress := '016';
3185 IF g_fnd_debug = 'Y' THEN
3186 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3187 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || '.'||l_api_name||'.'
3188 || l_progress,'REL 16: ASL Debarred check ');
3189 END IF;
3190 END IF;
3191
3192 -- Check 16: Determine if an item is restricted. If it is restricted the
3193 -- Purchase Order Vendor must be listed in the Approved Suppliers
3194 -- List table and must be approved for release to get approved.
3195 -- Bug 839743
3196 -- PO_SUB_ITEM_ASL_DEBARRED_REL
3197
3198 l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_ITEM_ASL_DEBARRED_REL');
3199 SELECT substr(g_reqmsg||g_delim||p_req_num||g_delim||
3200 g_linemsg||g_delim||p_req_line_num||
3201 g_delim||l_textline,1,240)
3202 BULK COLLECT INTO l_error_messages
3203 FROM MTL_SYSTEM_ITEMS MSI, PO_LINE_LOCATIONS PLL,
3204 PO_RELEASES POR,PO_LINES POL, PO_HEADERS POH,
3205 FINANCIALS_SYSTEM_PARAMETERS FSP
3206 WHERE POR.po_release_id = x_po_release_id
3207 AND POR.po_header_id = POH.po_header_id
3208 AND POR.po_header_id = POL.po_header_id
3209 AND POL.po_line_id = PLL.po_line_id
3210 AND POR.po_release_id = PLL.po_release_id
3211 AND MSI.organization_id = PLL.ship_to_organization_id
3212 AND MSI.inventory_item_id = POL.item_id
3213 AND POL.item_id is not null
3214 AND nvl(PLL.closed_code,'OPEN') <> 'FINALLY CLOSED'
3215 AND nvl(POL.cancel_flag,'N') = 'N'
3216 AND nvl(PLL.cancel_flag,'N') = 'N'
3217 AND nvl(MSI.must_use_approved_vendor_flag,'N') = 'Y'
3218 AND exists
3219 (SELECT sum(decode(ASR.allow_action_flag, 'Y', 1, -100))
3220 FROM PO_APPROVED_SUPPLIER_LIS_VAL_V ASL, PO_ASL_STATUS_RULES ASR
3221 WHERE ASL.using_organization_id in (PLL.ship_to_organization_id, -1)
3222 AND ASL.vendor_id = POH.vendor_id
3223 AND nvl(ASL.vendor_site_id, POH.vendor_site_id) = POH.vendor_site_id
3224 AND ASL.item_id = POL.item_id
3225 AND ASL.asl_status_id = ASR.status_id
3226 AND ASR.business_rule = '1_PO_APPROVAL'
3227 HAVING sum(decode(ASR.allow_action_flag, 'Y', 1, -100)) < 0
3228 UNION ALL
3229 SELECT sum(decode(ASR.allow_action_flag, 'Y', 1, -100))
3230 FROM PO_APPROVED_SUPPLIER_LIS_VAL_V ASL, PO_ASL_STATUS_RULES ASR
3231 WHERE ASL.using_organization_id in (PLL.ship_to_organization_id , -1)
3232 AND ASL.vendor_id = POH.vendor_id
3233 AND nvl(ASL.vendor_site_id, POH.vendor_site_id) = POH.vendor_site_id
3234 AND ASL.item_id is NULL
3235 AND ASL.category_id in
3236 (SELECT MIC.category_id
3237 FROM MTL_ITEM_CATEGORIES MIC
3238 WHERE MIC.inventory_item_id = POL.item_id
3239 AND MIC.organization_id = PLL.ship_to_organization_id)
3240 AND ASL.asl_status_id = ASR.status_id
3241 AND ASR.business_rule = '1_PO_APPROVAL'
3242 HAVING sum(decode(ASR.allow_action_flag, 'Y', 1, -100)) < 0);
3243
3244 FOR i IN 1..l_error_messages.COUNT LOOP
3245 FND_FILE.put_line(FND_FILE.LOG, l_error_messages(i));
3246 END LOOP;
3247 IF l_error_messages.COUNT > 0 THEN
3248 x_check_status := FND_API.G_RET_STS_ERROR;
3249 END IF;
3250
3251 ---------------------------------------------------------
3252
3253 -- Check that the release shipment price is within the tolerance
3254 -- of the requisition line.
3255 check_rel_reqprice(l_check_status);
3256 IF (l_check_status = FND_API.G_RET_STS_ERROR) THEN
3257 x_check_status := FND_API.G_RET_STS_ERROR;
3258 END IF;
3259
3260 ----------------------------------------------------------
3261 --<JFMIP Vendor Registration FPJ Start>
3262 -- Check if vendor site has a valid Central Contractor Registration(CCR)
3263 l_progress := '017';
3264
3265 IF g_fnd_debug = 'Y' THEN
3266 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3267 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || '.'||l_api_name
3268 ||'.'|| l_progress,'Vendor site registration check ');
3269 END IF;
3270 END IF;
3271
3272 -- No need to check vendor site registration if it's not a federal instance
3273 IF l_federal_instance = 'Y' THEN
3274
3275 -- SQL What: retrieves vendor and vendor site id from blanket header
3276 -- SQL Why: need to check vendor site registration status below
3277 BEGIN
3278 SELECT vendor_id, vendor_site_id
3279 INTO l_vendor_id, l_vendor_site_id
3280 FROM po_headers_all
3281 WHERE po_header_id = p_po_header_id;
3282 EXCEPTION
3283 WHEN OTHERS THEN
3284 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3285 END;
3286
3287 -- Call PO_FV_INTEGRATION_PVT.val_vendor_site_ccr_regis to check the
3288 -- Central Contractor Registration (CCR) status of the vendor site
3289 IF (l_vendor_id IS NOT NULL) AND (l_vendor_site_id IS NOT NULL) THEN
3290 l_valid_registration := PO_FV_INTEGRATION_PVT.val_vendor_site_ccr_regis(
3291 p_vendor_id => l_vendor_id,
3292 p_vendor_site_id => l_vendor_site_id);
3293
3294 IF NOT l_valid_registration THEN
3295 l_textline := FND_MESSAGE.get_string('PO', 'PO_VENDOR_SITE_CCR_INVALID');
3296 FND_FILE.put_line(FND_FILE.LOG, substr(g_reqmsg||g_delim
3297 ||p_req_num||g_delim||g_linemsg||g_delim
3298 ||p_req_line_num||g_delim||l_textline,1,240));
3299 x_check_status := FND_API.G_RET_STS_ERROR;
3300 END IF; -- l_valid_registration check
3301 END IF; -- l_vendor_id and l_vendor_site_id check
3302 END IF; -- l_federal_instance check
3303 --<JFMIP Vendor Registration FPJ End>
3304
3305 --<BUG 7685164 Added following submission checks as part of LCM ER>
3306 l_progress := '018';
3307 IF g_fnd_debug = 'Y' THEN
3308 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3309 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || '.'||l_api_name||'.'
3310 || l_progress,'LCM enabled release shipment should have invoice match option as receipt');
3311 END IF;
3312 END IF;
3313 l_textline := FND_MESSAGE.GET_STRING('PO','PO_SUB_REL_SHIP_INV_MATCH_NE_R');
3314
3315 SELECT substr (g_shipmsg||g_delim||PLL.shipment_num||g_delim||l_textline,1,240)
3316 BULK COLLECT INTO l_error_messages
3317 FROM PO_RELEASES_ALL POR,
3318 PO_LINE_LOCATIONS_ALL PLL
3319 WHERE POR.po_release_id = PLL.po_release_id
3320 AND POR.po_release_id = x_po_release_id
3321 AND Nvl(PLL.LCM_FLAG,'N') = 'Y'
3322 AND Nvl(PLL.match_option,'P') <> 'R';
3323
3324 FOR i IN 1..l_error_messages.COUNT LOOP
3325 FND_FILE.put_line(FND_FILE.LOG, l_error_messages(i));
3326 END LOOP;
3327 IF l_error_messages.COUNT > 0 THEN
3328 x_check_status := FND_API.G_RET_STS_UNEXP_ERROR;
3329 END IF;
3330
3331 l_progress := '019';
3332 IF g_fnd_debug = 'Y' THEN
3333 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3334 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || '.'||l_api_name||'.'
3335 || l_progress,'LCM enabled release distribution should have destination type as Inventory');
3336 END IF;
3337 END IF;
3338
3339 l_textline := FND_MESSAGE.GET_STRING('PO','PO_SUB_REL_DIST_DEST_TYPE_NE_I');
3340
3341 SELECT substr (g_shipmsg||g_delim||PLL.shipment_num||g_delim||g_distmsg||g_delim||
3342 POD.distribution_num||g_delim||l_textline, 1,240)
3343 BULK COLLECT INTO l_error_messages
3344 FROM PO_RELEASES_GT POR,
3345 PO_LINE_LOCATIONS_GT PLL,
3346 PO_DISTRIBUTIONS_GT POD
3347 WHERE POR.po_release_id = POD.po_release_id
3348 AND POD.line_location_id = PLL.line_location_id
3349 AND POR.po_release_id = x_po_release_id
3350 AND Nvl(POD.LCM_FLAG,'N') = 'Y'
3351 AND POD.DESTINATION_TYPE_CODE <> 'INVENTORY';
3352
3353 FOR i IN 1..l_error_messages.COUNT LOOP
3354 FND_FILE.put_line(FND_FILE.LOG, l_error_messages(i));
3355 END LOOP;
3356 IF l_error_messages.COUNT > 0 THEN
3357 x_check_status := FND_API.G_RET_STS_UNEXP_ERROR;
3358 END IF;
3359
3360 EXCEPTION
3361
3362 WHEN OTHERS THEN
3363 FND_FILE.put_line(FND_FILE.LOG,
3364 c_log_head || l_api_name || ' exception; location: '
3365 || l_progress || ' SQL code: ' || sqlcode);
3366 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3367 FND_LOG.string(FND_LOG.level_unexpected, c_log_head ||
3368 l_api_name || '.others_exception', 'EXCEPTION: Location is '
3369 || l_progress || ' SQL CODE is '||sqlcode);
3370 END IF;
3371 raise_application_error(-20001,sqlerrm||'---'||msgbuf);
3372
3373 END PREAPPROVAL_CHECKS;
3374 --- Bug 2701147 END
3375
3376 -- <INBOUND LOGISITCS PFJ START>
3377 -------------------------------------------------------------------------------
3378 --Start of Comments
3379 --Name: create_deliveryrecord
3380 --Pre-reqs:
3381 -- None.
3382 --Modifies:
3383 -- l_fte_rec
3384 --Locks:
3385 -- None.
3386 --Function:
3387 -- Call FTE's API to create delivery record for Approved Blanket Release
3388 --Parameters:
3389 --IN:
3390 --p_release_id
3391 -- Corresponding to po_release_id
3392 --Testing:
3393 -- Pass in po_release_id for an approved release.
3394 --End of Comments
3395 -------------------------------------------------------------------------------
3396
3397 PROCEDURE create_deliveryrecord(p_release_id IN NUMBER)
3398 IS
3399 l_api_name CONSTANT varchar2(30) := 'CREATE_DELIVERYRECORD';
3400
3401 l_return_status VARCHAR2(1);
3402 l_msg_count NUMBER;
3403 l_msg_data VARCHAR2(2000);
3404 l_msg_buf VARCHAR2(2000);
3405
3406 l_progress VARCHAR2(3) := '001';
3407
3408 BEGIN
3409 l_return_status := FND_API.G_RET_STS_SUCCESS;
3410
3411 IF (g_fnd_debug = 'Y') THEN
3412 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3413 FND_LOG.string (
3414 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
3415 MODULE => c_log_head || '.'||l_api_name||'.' || l_progress,
3416 MESSAGE => 'Start create delivery record for approved Blanket Release'
3417 );
3418 END IF;
3419 END IF;
3420
3421 PO_DELREC_PVT.create_update_delrec
3422 (
3423 p_api_version => 1.0,
3424 x_return_status => l_return_status,
3425 x_msg_count => l_msg_count,
3426 x_msg_data => l_msg_data,
3427 p_action => 'APPROVE',
3428 p_doc_type => 'RELEASE',
3429 p_doc_subtype => 'BLANKET',
3430 p_doc_id => p_release_id,
3431 p_line_id => NULL,
3432 p_line_location_id => NULL
3433 );
3434
3435 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3436 RAISE FND_API.G_EXC_ERROR;
3437 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3438 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3439 ELSE
3440 NULL;
3441 END IF;
3442
3443 EXCEPTION
3444 WHEN FND_API.G_EXC_ERROR THEN
3445 ROLLBACK TO PORELGEN_1;
3446 -- Bug 3570793 Write the message list to the concurrent program log.
3447 PO_DEBUG.write_msg_list_to_file (
3448 p_log_head => c_log_head || l_api_name,
3449 p_progress => l_progress
3450 );
3451
3452 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3453 ROLLBACK TO PORELGEN_1;
3454 -- Bug 3570793 Write the message list to the concurrent program log.
3455 PO_DEBUG.write_msg_list_to_file (
3456 p_log_head => c_log_head || l_api_name,
3457 p_progress => l_progress
3458 );
3459
3460 WHEN OTHERS THEN
3461 ROLLBACK TO PORELGEN_1;
3462
3463 IF FND_MSG_PUB.check_msg_level(
3464 p_message_level => FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
3465 FND_MSG_PUB.add_exc_msg(
3466 p_pkg_name => G_PKG_NAME,
3467 p_procedure_name => l_api_name
3468 );
3469 END IF;
3470
3471 IF (g_fnd_debug = 'Y') THEN
3472 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3473 FND_LOG.string(
3474 LOG_LEVEL => FND_LOG.level_unexpected,
3475 MODULE => c_log_head ||'.'||l_api_name||'.others_exception',
3476 MESSAGE => FND_MSG_PUB.get(p_msg_index => FND_MSG_PUB.G_LAST, p_encoded => 'F')
3477 );
3478 END IF;
3479 END IF;
3480
3481 END create_deliveryrecord;
3482 -- <INBOUND LOGISITCS PFJ END>
3483
3484 --<GRANTS FPJ START>
3485
3486 ----------------------------------------------------------------------------
3487 --Start of Comments
3488 --Name: create_award_distribution
3489 --Pre-reqs:
3490 -- None
3491 --Modifies:
3492 -- PO_DISTRIBUTIONS
3493 -- GMS_AWARD_DISTRIBUTIONS
3494 --Locks:
3495 -- None
3496 --Function:
3497 -- Calls Grants Accounting API to create new award distributions lines
3498 -- when a requisition with distributions that reference awards is
3499 -- processed into a release through the Create Releases concurent request.
3500 --Parameters:
3501 -- None
3502 --Returns:
3503 -- None
3504 --Testing:
3505 -- None
3506 --End of Comments
3507 ----------------------------------------------------------------------------
3508
3509 PROCEDURE create_award_distribution IS
3510
3511 l_api_name CONSTANT VARCHAR(30) := 'CREATE_AWARD_DISTRIBUTION';
3512 l_msg_count NUMBER;
3513 l_msg_data VARCHAR2(2000);
3514 l_return_status VARCHAR2(1);
3515 l_gms_po_interface_obj gms_po_interface_type;
3516 l_progress VARCHAR2(3);
3517 l_msg_buf VARCHAR2(2000);
3518
3519 BEGIN
3520
3521 l_progress := '001';
3522
3523 --SQL WHAT: Select the columns that Grants needs from the
3524 -- po_distributions table for this release where
3525 -- award_id is referenced.
3526 --SQL WHY : Need to call GMS API to update award distribution
3527 -- lines table.
3528
3529 SELECT
3530 po_distribution_id,
3531 distribution_num,
3532 project_id,
3533 task_id,
3534 award_id,
3535 NULL
3536 BULK COLLECT INTO
3537 l_gms_po_interface_obj.distribution_id,
3538 l_gms_po_interface_obj.distribution_num,
3539 l_gms_po_interface_obj.project_id,
3540 l_gms_po_interface_obj.task_id,
3541 l_gms_po_interface_obj.award_set_id_in,
3542 l_gms_po_interface_obj.award_set_id_out
3543 FROM PO_DISTRIBUTIONS
3544 WHERE line_location_id = x_line_location_id AND
3545 award_id IS NOT NULL;
3546
3547 IF SQL%NOTFOUND THEN
3548 RETURN;
3549 END IF;
3550
3551 l_progress := '002';
3552
3553 --Call GMS API to update award distribution lines table
3554
3555 PO_GMS_INTEGRATION_PVT.maintain_adl (
3556 p_api_version => 1.0,
3557 x_return_status => l_return_status,
3558 x_msg_count => l_msg_count,
3559 x_msg_data => l_msg_data,
3560 p_caller => 'CREATE_RELEASE',
3561 x_po_gms_interface_obj => l_gms_po_interface_obj);
3562
3563 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3564 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3565 END IF;
3566
3567 l_progress := '003';
3568
3569 --Update po_distributions table with the new award_id's
3570
3571 FORALL i IN 1..l_gms_po_interface_obj.distribution_id.COUNT
3572 UPDATE po_distributions
3573 SET award_id = l_gms_po_interface_obj.award_set_id_out(i)
3574 WHERE po_distribution_id = l_gms_po_interface_obj.distribution_id(i);
3575
3576 EXCEPTION
3577
3578 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3579
3580 -- Bug 3570793 Write the message list to the concurrent program log.
3581 PO_DEBUG.write_msg_list_to_file (
3582 p_log_head => c_log_head || l_api_name,
3583 p_progress => l_progress
3584 );
3585
3586 RAISE;
3587
3588 WHEN OTHERS THEN
3589
3590 FND_FILE.put_line(FND_FILE.LOG,
3591 c_log_head || l_api_name || ' exception; location: '
3592 || l_progress || ' SQL code: ' || sqlcode);
3593
3594 IF (g_fnd_debug = 'Y') THEN
3595 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3596 FND_LOG.string(
3597 LOG_LEVEL => FND_LOG.level_unexpected,
3598 MODULE => c_log_head||l_api_name||'.error_exception',
3599 MESSAGE => 'EXCEPTION ' || l_progress || ': SQL CODE is '||sqlcode
3600 );
3601 END IF;
3602 END IF;
3603
3604 RAISE;
3605
3606 END create_award_distribution;
3607
3608 --<GRANTS FPJ END>
3609
3610 /*bug12602301 starts: Performance issue.
3611 in order to determine the one particular entry from
3612 the asl table the earlier cursor had many queries within itself
3613 i.e subqueries. As a part of the fix all the conditions of the subqueries have been
3614 incorporated in a single sql.This sql is in the function get_asl_id which retruns the asl_id
3615 The precedence for picking up an asl entry is
3616 1. Item level asl.
3617 2. Category ASl
3618 For each of the above
3619 Local ASL will be preferred over global asl
3620 Valid Vendor site will be preferred than vendor_site being null
3621 */
3622 FUNCTION get_asl_id( p_item_id IN po_requisition_lines_all.item_id%TYPE,
3623 p_category_id IN po_requisition_lines_all.category_id%TYPE,
3624 p_destination_organization_id IN po_requisition_lines_all.destination_organization_id%TYPE,
3625 p_vendor_id IN po_headers_all.vendor_id%TYPE,
3626 p_vendor_site_id IN po_headers_all.vendor_site_id%TYPE )
3627 return number
3628 as
3629
3630 l_progress VARCHAR2(3) := '000';
3631 l_api_name CONSTANT VARCHAR2(30) := 'get_asl_id';
3632
3633 l_aslid NUMBER;
3634 begin
3635
3636 IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >
3637 PO_DEBUG.debug_begin(p_log_head => c_log_head||l_api_name);
3638 END IF;
3639
3640 l_progress := '010';
3641 IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >
3642 PO_DEBUG.debug_stmt(p_log_head => c_log_head||l_api_name,
3643 p_token => l_progress,
3644 p_message => 'Get_asl_id : item_id : '||p_item_id||
3645 'category_id : '||p_category_id||
3646 'destination_organization_id : '||p_destination_organization_id||
3647 'vendor_id : '||p_vendor_id||
3648 'vendor_site_id : '||p_vendor_site_id);
3649 END IF;
3650
3651 l_progress := '020';
3652
3653 SELECT asl_id
3654 INTO l_aslid
3655 FROM (SELECT asl_id
3656 FROM (SELECT paa2.asl_id,Nvl(paa2.item_id, -1) item_id,
3657 Nvl(paa2.category_id, -1) category_id,
3658 Decode(item_id, NULL, '2-CATEGORY',
3659 '1-ITEM') item_cat,
3660 paa2.using_organization_id,
3661 paa2.vendor_id,
3662 Nvl(paa2.vendor_site_id, -1) vendor_site_id
3663 FROM po_asl_attributes_val_v paa2
3664 WHERE p_item_id IS NOT NULL
3665 AND paa2.item_id = p_item_id
3666 AND paa2.vendor_id = p_vendor_id
3667 AND paa2.using_organization_id IN
3668 ( -1, p_destination_organization_id
3669 )
3670 AND (paa2.vendor_site_id IS NULL
3671 OR
3672 paa2.vendor_site_id = Nvl(p_vendor_site_id, -1)
3673 )
3674 and paa2.release_generation_method in ('CREATE','CREATE_AND_APPROVE')
3675 UNION
3676 SELECT paa3.asl_id,Nvl(paa3.item_id, -1) item_id,
3677 Nvl(paa3.category_id, -1) category_id,
3678 Decode(item_id, NULL, '2-CATEGORY',
3679 '1-ITEM') item_cat,
3680 paa3.using_organization_id,
3681 paa3.vendor_id vendor_id,
3682 Nvl(paa3.vendor_site_id, -1) vendor_site_id
3683 FROM po_asl_attributes_val_v paa3
3684 WHERE p_item_id IS NULL
3685 AND paa3.category_id = p_category_id
3686 AND paa3.vendor_id = p_vendor_id
3687 AND paa3.using_organization_id IN
3688 ( -1, p_destination_organization_id
3689 )
3690 AND (paa3.vendor_site_id IS NULL
3691 OR
3692 paa3.vendor_site_id = Nvl(p_vendor_site_id, -1)
3693 )
3694 and paa3.release_generation_method in ('CREATE','CREATE_AND_APPROVE') )
3695 ORDER BY ITEM_CAT ASC,Nvl(item_id, -1),
3696 Nvl(category_id, -1),
3697 using_organization_id DESC,
3698 Nvl(vendor_site_id, -1))
3699 WHERE rownum < 2 ;
3700
3701 l_progress := '030';
3702 IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >
3703 PO_DEBUG.debug_stmt(p_log_head => c_log_head||l_api_name,
3704 p_token => l_progress,
3705 p_message => 'Get_asl_id : l_aslid : '||l_aslid);
3706 END IF;
3707
3708
3709 return l_aslid;
3710
3711
3712 EXCEPTION WHEN NO_DATA_FOUND THEN
3713 l_aslid := -1;
3714 l_progress := '040';
3715 IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >
3716 PO_DEBUG.debug_stmt(p_log_head => c_log_head||l_api_name,
3717 p_token => l_progress,
3718 p_message => 'Get_asl_id in exception block : l_aslid : '||l_aslid);
3719 END IF;
3720 RETURN l_aslid;
3721
3722 end;
3723 /*bug 12602301 ends*/
3724 END PO_RELGEN_PKG;