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