[Home] [Help]
PACKAGE BODY: APPS.PO_DELREC_PVT
Source
1 PACKAGE BODY PO_DELREC_PVT AS
2 /* $Header: POXVDRDB.pls 120.6.12010000.2 2008/08/11 12:48:27 bisdas ship $ */
3 c_log_head CONSTANT VARCHAR2(30) := 'po.plsql.PO_DELREC_PVT.';
4 x_progress VARCHAR2(4) := NULL;
5
6 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
7 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
8 g_debug_unexp CONSTANT BOOLEAN := PO_DEBUG.is_debug_unexp_on;
9
10 PROCEDURE get_approved_po
11 (
12 p_api_version IN NUMBER,
13 x_return_status IN OUT NOCOPY VARCHAR2,
14 p_header_id IN NUMBER,
15 p_line_id IN NUMBER,
16 p_line_location_id IN NUMBER,
17 x_fte_rec IN OUT NOCOPY OE_WSH_BULK_GRP.Line_Rec_Type
18 );
19
20 PROCEDURE get_cancelled_po
21 (
22 p_api_version IN NUMBER,
23 x_return_status IN OUT NOCOPY VARCHAR2,
24 p_header_id IN NUMBER,
25 p_line_id IN NUMBER,
26 p_line_location_id IN NUMBER,
27 x_fte_rec IN OUT NOCOPY OE_WSH_BULK_GRP.Line_Rec_Type
28 );
29
30 PROCEDURE get_opened_po
31 (
32 p_api_version IN NUMBER,
33 x_return_status IN OUT NOCOPY VARCHAR2,
34 p_header_id IN NUMBER,
35 p_line_id IN NUMBER,
36 p_line_location_id IN NUMBER,
37 x_fte_rec IN OUT NOCOPY OE_WSH_BULK_GRP.Line_Rec_Type
38 );
39
40 PROCEDURE get_closed_po
41 (
42 p_api_version IN NUMBER,
43 x_return_status IN OUT NOCOPY VARCHAR2,
44 p_header_id IN NUMBER,
45 p_line_id IN NUMBER,
46 p_line_location_id IN NUMBER,
47 x_fte_rec IN OUT NOCOPY OE_WSH_BULK_GRP.Line_Rec_Type
48 );
49
50 PROCEDURE get_finally_closed_po
51 (
52 p_api_version IN NUMBER,
53 x_return_status IN OUT NOCOPY VARCHAR2,
54 p_header_id IN NUMBER,
55 p_line_id IN NUMBER,
56 p_line_location_id IN NUMBER,
57 x_fte_rec IN OUT NOCOPY OE_WSH_BULK_GRP.Line_Rec_Type
58 );
59
60 PROCEDURE get_approved_release
61 (
62 p_api_version IN NUMBER,
63 x_return_status IN OUT NOCOPY VARCHAR2,
64 p_header_id IN NUMBER,
65 p_line_location_id IN NUMBER,
66 x_fte_rec IN OUT NOCOPY OE_WSH_BULK_GRP.Line_Rec_Type
67 );
68
69 PROCEDURE get_cancelled_release
70 (
71 p_api_version IN NUMBER,
72 x_return_status IN OUT NOCOPY VARCHAR2,
73 p_header_id IN NUMBER,
74 p_line_location_id IN NUMBER,
75 x_fte_rec IN OUT NOCOPY OE_WSH_BULK_GRP.Line_Rec_Type
76 );
77
78
79 PROCEDURE get_opened_release
80 (
81 p_api_version IN NUMBER,
82 x_return_status IN OUT NOCOPY VARCHAR2,
83 p_header_id IN NUMBER,
84 p_line_location_id IN NUMBER,
85 x_fte_rec IN OUT NOCOPY OE_WSH_BULK_GRP.Line_Rec_Type
86 );
87
88 PROCEDURE get_closed_release
89 (
90 p_api_version IN NUMBER,
91 x_return_status IN OUT NOCOPY VARCHAR2,
92 p_header_id IN NUMBER,
93 p_line_location_id IN NUMBER,
94 x_fte_rec IN OUT NOCOPY OE_WSH_BULK_GRP.Line_Rec_Type
95 );
96
97 PROCEDURE get_finally_closed_release
98 (
99 p_api_version IN NUMBER,
100 x_return_status IN OUT NOCOPY VARCHAR2,
101 p_header_id IN NUMBER,
102 p_line_location_id IN NUMBER,
103 x_fte_rec IN OUT NOCOPY OE_WSH_BULK_GRP.Line_Rec_Type
104 );
105
106 PROCEDURE make_rcv_call
107 (
108 p_api_version IN NUMBER,
109 p_action_rec IN OUT NOCOPY WSH_BULK_TYPES_GRP.action_parameters_rectype,
110 x_return_status IN OUT NOCOPY VARCHAR2,
111 p_action IN VARCHAR2,
112 p_header_id IN NUMBER,
113 p_line_id IN NUMBER,
114 p_line_location_id IN NUMBER,
115 x_fte_rec IN OUT NOCOPY OE_WSH_BULK_GRP.Line_Rec_Type );
116
117 -- Bug 3581992 START
118 PROCEDURE debug_fte_rec
119 (
120 p_fte_rec IN OE_WSH_BULK_GRP.Line_Rec_Type
121 );
122 -- Bug 3581992 END
123
124 -------------------------------------------------------------------------------
125 --Start of Comments
126 --Name: create_update_delrec
127 --Pre-reqs:
128 -- None.
129 --Modifies:
130 -- l_fte_rec
131 --Locks:
132 -- None.
133 --Function:
134 -- Call FTE's API to create delivery record for Standard Purchase Order
135 -- and Blanket Release
136 --Parameters:
137 --IN:
138 --p_api_version
139 -- Specifies API version.
140 --p_action
141 -- Specifies doc control action.
142 --p_doc_type
143 -- Differentiates between the doc being a PO or Release.
144 --p_doc_subtype
145 -- Specifies Standard PO or Blanket Release.
146 --p_doc_id
147 -- Corresponding to po_header_id or po_release_id.
148 --p_line_id
149 -- Corresponding to po_line_id
150 --p_line_location_id
151 -- Corresponding to po_line_location_id
152 --IN OUT:
153 --x_return_status
154 -- Indicates API return status as 'S', 'E' or 'U'.
155 --x_msg_count
156 -- Error messages number.
157 --x_msg_data
158 -- Error messages body.
159 --Testing:
160 -- Need to integrate FTE to implement the testing.
161 --End of Comments
162 -------------------------------------------------------------------------------
163
164
165 PROCEDURE create_update_delrec
166 (
167 p_api_version IN NUMBER,
168 x_return_status IN OUT NOCOPY VARCHAR2,
169 x_msg_count IN OUT NOCOPY NUMBER,
170 x_msg_data IN OUT NOCOPY VARCHAR2,
171 p_action IN VARCHAR2,
172 p_doc_type IN VARCHAR2,
173 p_doc_subtype IN VARCHAR2,
174 p_doc_id IN NUMBER,
175 p_line_id IN NUMBER,
176 p_line_location_id IN NUMBER
177 )
178 IS
179 l_api_name CONSTANT VARCHAR2(100) := 'create_update_delrec';
180 l_api_version CONSTANT NUMBER := 1.0;
181 l_shipping_control PO_HEADERS_ALL.shipping_control%TYPE;
182
183 -- define record of tables for bulk processing
184 l_action_rec WSH_BULK_TYPES_GRP.action_parameters_rectype;
185 l_fte_rec OE_WSH_BULK_GRP.Line_Rec_Type;
186 l_fte_out_rec WSH_BULK_TYPES_GRP.Bulk_process_out_rec_type;
187
188 BEGIN
189 -- Standard Start of API savepoint
190 SAVEPOINT create_update_delrec;
191
192 -- Initialize API return status to success
193 x_return_status := FND_API.G_RET_STS_SUCCESS;
194
195
196 x_progress:='000';
197 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
198 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name ||'.begin', 'Check API Call Compatibility');
199 END IF;
200 -- Standard call to check for call compatibility.
201 IF NOT FND_API.Compatible_API_Call
202 (
203 p_current_version_number => l_api_version,
204 p_caller_version_number => p_api_version,
205 p_api_name => l_api_name,
206 p_pkg_name => 'PO_DELREC_PVT'
207 )
208 THEN
209 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
210 END IF;
211
212 --<R12 OTM INTEGRATION START>: Call OTM if installed. Otherwise, execute
213 --old FTE integration
214 IF (PO_OTM_INTEGRATION_PVT.is_otm_installed()) THEN
215
216 x_progress := '005';
217
218 PO_OTM_INTEGRATION_PVT.handle_doc_update(
219 p_doc_type => p_doc_type
220 , p_doc_id => p_doc_id
221 , p_action => p_action
222 , p_line_id => p_line_id
223 , p_line_loc_id => p_line_location_id);
224
225 x_progress := '006';
226
227 ELSIF (WSH_UTIL_CORE.fte_is_installed() = 'Y') THEN
228
229 x_progress := '008';
230
231 l_action_rec.Caller := 'PO';
232 l_action_rec.Phase := '';
233
234 x_progress:='010';
235 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
236 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name ||'.begin', 'Check action ' || p_action);
237 END IF;
238 IF (p_action IN ('APPROVE_DOCUMENT', 'APPROVE', 'APPROVE AND RESERVE')) THEN
239 l_action_rec.action_code := 'APPROVE_PO';
240 ELSIF (p_action = 'CANCEL') THEN
241 l_action_rec.action_code := 'CANCEL_PO';
242 ELSIF (p_action IN ('OPEN', 'RECEIVE OPEN')) THEN
243 l_action_rec.action_code := 'REOPEN_PO';
244 ELSIF (p_action = 'CLOSE') THEN
245 l_action_rec.action_code := 'CLOSE_PO';
246 ELSIF (p_action = 'FINALLY CLOSE') THEN
247 l_action_rec.action_code := 'FINAL_CLOSE';
248 ELSIF (p_action = 'RECEIVE CLOSE') THEN
249 l_action_rec.action_code := 'CLOSE_PO_FOR_RECEIVING';
250 -- Receiving Codes
251 ELSIF (p_action = 'ASN') THEN
252 l_action_rec.action_code := 'ASN';
253 ELSIF (p_action = 'CANCEL_ASN') THEN
254 l_action_rec.action_code := 'CANCEL_ASN';
255 ELSIF (p_action = 'RECEIPT') THEN
256 l_action_rec.action_code := 'RECEIPT';
257 ELSIF (p_action = 'MATCH') THEN
258 l_action_rec.action_code := 'MATCH';
259 ELSIF (p_action = 'RECEIPT_CORRECTION') THEN
260 l_action_rec.action_code := 'RECEIPT_CORRECTION';
261 ELSIF (p_action = 'CORRECT') THEN
262 l_action_rec.action_code := 'RECEIPT_CORRECTION';
263 ELSIF (p_action = 'RTV') THEN
264 l_action_rec.action_code := 'RTV';
265 ELSIF (p_action = 'RTV_CORRECTION') THEN
266 l_action_rec.action_code := 'RTV_CORRECTION';
267 ELSIF (p_action = 'RECEIPT_ADD') THEN
268 l_action_rec.action_code := 'RECEIPT_ADD';
269 ELSIF (p_action = 'RECEIPT_HEADER_UPD') THEN
270 l_action_rec.action_code := 'RECEIPT_HEADER_UPD';
271 ELSE
272 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
273 -- <OTM INTEGRATION FPJ>: changed exception to log and early
274 -- return. Previously, calls to create_update_delrec were
275 -- filtered for the action, but OTM responds to a different
276 -- set of actions, so the filters have been removed.
277 IF(FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
278 FND_LOG.string(FND_LOG.level_statement, c_log_head || l_api_name,
279 'Unrecognized command for FTE: ' || p_action ||
280 ', returning.');
281 END IF;
282 RETURN;
283 END IF;
284
285
286 -- The following is for query data to create delivery record for:
287 -- Standard PO, Standard PO referencing GA or Contract
288 x_progress:='015';
289 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
290 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name ||'.begin', 'Check doc type is ' || p_doc_type || ' doc subtype is ' || p_doc_subtype || '
291 doc id is ' || p_doc_id || ' line id is ' || p_line_id || ' line location id is ' || p_line_location_id );
292 END IF;
293
294 /* Bug 7232644: Checking of the value of the shipping control before calling the
295 procedure make_rcv_call.*/
296
297 IF ( p_doc_type = 'PO' AND p_doc_subtype = 'STANDARD' ) THEN
298 SELECT shipping_control
299 INTO l_shipping_control
300 FROM PO_HEADERS_ALL
301 WHERE po_header_id = p_doc_id;
302 ELSIF ( p_doc_type = 'RELEASE' AND p_doc_subtype = 'BLANKET') THEN
303 SELECT shipping_control
304 INTO l_shipping_control
305 FROM PO_RELEASES_ALL
306 WHERE po_release_id = p_doc_id;
307 ELSE
308 l_shipping_control := NULL;
309 END IF;
310
311 --Bug#5009715 We should only call FTE API when Shipping Control is SUPPLIER or BUYER
312 IF (nvl(l_shipping_control,'NONE') = 'NONE') THEN
313 IF g_fnd_debug = 'Y' THEN
314 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
315 FND_LOG.string(
316 log_level => FND_LOG.LEVEL_STATEMENT,
317 module => c_log_head || l_api_name || '.begin',
318 message => 'FTE API is not called because shipping control is set to NULL or NONE'
319 );
320 END IF;
321 END IF;
322 RETURN;
323 END IF;
324
325
326 IF ( p_doc_type = 'RCV') THEN
327 l_action_rec.Entity := 'PO';
328 make_rcv_call(
329 p_api_version => l_api_version,
330 p_action_rec => l_action_rec,
331 x_return_status => x_return_status,
332 p_action => p_action,
333 p_header_id => p_doc_id,
334 p_line_id => p_line_id,
335 p_line_location_id => p_line_location_id,
336 x_fte_rec => l_fte_rec
337 );
338
339 END IF;
340
341
342
343 IF ( p_doc_type = 'PO' AND p_doc_subtype = 'STANDARD' ) THEN
344 l_action_rec.Entity := 'PO';
345 IF (l_action_rec.action_code = 'APPROVE_PO') THEN
346 get_approved_po
347 (
348 p_api_version => l_api_version,
349 x_return_status => x_return_status,
350 p_header_id => p_doc_id,
351 p_line_id => p_line_id,
352 p_line_location_id => p_line_location_id,
353 x_fte_rec => l_fte_rec
354 );
355 ELSIF (l_action_rec.action_code = 'CANCEL_PO') THEN
356 get_cancelled_po
357 (
358 p_api_version => l_api_version,
359 x_return_status => x_return_status,
360 p_header_id => p_doc_id,
361 p_line_id => p_line_id,
362 p_line_location_id => p_line_location_id,
363 x_fte_rec => l_fte_rec
364 );
365 ELSIF (l_action_rec.action_code = 'REOPEN_PO') THEN
366 get_opened_po
367 (
368 p_api_version => l_api_version,
369 x_return_status => x_return_status,
370 p_header_id => p_doc_id,
371 p_line_id => p_line_id,
372 p_line_location_id => p_line_location_id,
373 x_fte_rec => l_fte_rec
374 );
375 ELSIF (l_action_rec.action_code IN ('CLOSE_PO', 'CLOSE_PO_FOR_RECEIVING')) THEN
376 get_closed_po
377 (
378 p_api_version => l_api_version,
379 x_return_status => x_return_status,
380 p_header_id => p_doc_id,
381 p_line_id => p_line_id,
382 p_line_location_id => p_line_location_id,
383 x_fte_rec => l_fte_rec
384 );
385 ELSIF (l_action_rec.action_code = 'FINAL_CLOSE') THEN
386 get_finally_closed_po
387 (
388 p_api_version => l_api_version,
389 x_return_status => x_return_status,
390 p_header_id => p_doc_id,
391 p_line_id => p_line_id,
392 p_line_location_id => p_line_location_id,
393 x_fte_rec => l_fte_rec
394 );
395 ELSE
396 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
397 END IF; -- IF (l_action_rec.action_code = 'APPROVE_PO')
398
399 x_progress:='020';
400 -- Bug 3581992 START
401 IF (g_debug_stmt) THEN
402 debug_fte_rec(l_fte_rec); -- Log the contents of l_fte_rec.
403 END IF;
404 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
405 -- Bug 3581992 END
406
407 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name ||'.begin', 'Call WSH_BULK_PROCESS_GRP.create_update_delivery_details');
408 END IF;
409
410 -- Bug 3602512 START
411 -- Do not call the FTE API if we do not have any records in l_fte_rec.
412 -- ex. This happens when we approve a PO that requires signature,
413 -- because the shipments will still have approved_flag = N until
414 -- the PO is signed.
415 IF (l_fte_rec.po_shipment_line_id.count > 0) THEN
416
417 WSH_BULK_PROCESS_GRP.create_update_delivery_details
418 (
419 p_api_version_number => l_api_version,
420 p_init_msg_list => FND_API.G_FALSE,
421 p_commit => FND_API.G_FALSE,
422 p_action_prms => l_action_rec,
423 p_line_rec => l_fte_rec,
424 x_Out_Rec => l_fte_out_rec,
425 x_return_status => x_return_status,
426 x_msg_count => x_msg_count,
427 x_msg_data => x_msg_data
428 );
429
430 ELSE -- l_fte_rec.po_shipment_line_id.count = 0
431 IF (g_debug_stmt) THEN
432 PO_DEBUG.debug_stmt ( p_log_head => c_log_head||l_api_name,
433 p_token => NULL,
434 p_message =>
435 'l_fte_rec has no records, so do not call the FTE API.' );
436 END IF;
437 END IF; -- l_fte_rec
438 -- Bug 3602512 END
439
440 -- The following is for query data to create delivery record for:
441 -- Blanket Release
442
443 ELSIF ( p_doc_type = 'RELEASE' AND p_doc_subtype = 'BLANKET') THEN
444 l_action_rec.Entity := 'RELEASE';
445
446 IF (l_action_rec.action_code = 'APPROVE_PO') THEN
447 get_approved_release
448 (
449 p_api_version => l_api_version,
450 x_return_status => x_return_status,
451 p_header_id => p_doc_id,
452 p_line_location_id => p_line_location_id,
453 x_fte_rec => l_fte_rec
454 );
455 ELSIF (l_action_rec.action_code = 'CANCEL_PO') THEN
456 get_cancelled_release
457 (
458 p_api_version => l_api_version,
459 x_return_status => x_return_status,
460 p_header_id => p_doc_id,
461 p_line_location_id => p_line_location_id,
462 x_fte_rec => l_fte_rec
463 );
464 ELSIF (l_action_rec.action_code = 'REOPEN_PO') THEN
465 get_opened_release
466 (
467 p_api_version => l_api_version,
468 x_return_status => x_return_status,
469 p_header_id => p_doc_id,
470 p_line_location_id => p_line_location_id,
471 x_fte_rec => l_fte_rec
472 );
473 ELSIF (l_action_rec.action_code IN ('CLOSE_PO', 'CLOSE_PO_FOR_RECEIVING')) THEN
474 get_closed_release
475 (
476 p_api_version => l_api_version,
477 x_return_status => x_return_status,
478 p_header_id => p_doc_id,
479 p_line_location_id => p_line_location_id,
480 x_fte_rec => l_fte_rec
481 );
482 ELSIF (l_action_rec.action_code = 'FINAL_CLOSE') THEN
483 get_finally_closed_release
484 (
485 p_api_version => l_api_version,
486 x_return_status => x_return_status,
487 p_header_id => p_doc_id,
488 p_line_location_id => p_line_location_id,
489 x_fte_rec => l_fte_rec
490 );
491 ELSE
492 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
493 END IF; -- IF (l_action_rec.action_code = 'APPROVE_PO')
494
495 X_progress:='020';
496 -- Bug 3581992 START
497 IF (g_debug_stmt) THEN
498 debug_fte_rec(l_fte_rec); -- Log the contents of l_fte_rec.
499 END IF;
500 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
501 -- Bug 3581992 END
502
503 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,c_log_head || l_api_name ||'.begin', 'Call WSH_BULK_PROCESS_GRP.create_update_delivery_details');
504 END IF;
505
506 -- Bug 3602512 START
507 -- Do not call the FTE API if we do not have any records in l_fte_rec.
508 IF (l_fte_rec.po_shipment_line_id.count > 0) THEN
509
510 WSH_BULK_PROCESS_GRP.create_update_delivery_details
511 (
512 p_api_version_number => l_api_version,
513 p_init_msg_list => FND_API.G_FALSE,
514 p_commit => FND_API.G_FALSE,
515 p_action_prms => l_action_rec,
516 p_line_rec => l_fte_rec,
517 x_Out_Rec => l_fte_out_rec,
518 x_return_status => x_return_status,
519 x_msg_count => x_msg_count,
520 x_msg_data => x_msg_data
521 );
522
523 ELSE -- l_fte_rec.po_shipment_line_id.count = 0
524 IF (g_debug_stmt) THEN
525 PO_DEBUG.debug_stmt ( p_log_head => c_log_head||l_api_name,
526 p_token => NULL,
527 p_message =>
528 'l_fte_rec has no records, so do not call the FTE API.' );
529 END IF;
530 END IF; -- l_fte_rec
531 -- Bug 3602512 END
532
533 END IF; -- IF ( p_doc_type = 'PO' AND p_doc_subtype = 'STANDARD' )
534
535 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
536 FOR i IN 1..x_msg_count LOOP
537 x_msg_data := SUBSTR(x_msg_data || FND_MSG_PUB.Get(p_msg_index=>i, p_encoded =>'F' ), 1, 2000);
538 END LOOP;
539 RAISE FND_API.G_EXC_ERROR;
540 END IF;
541 END IF; --IF (PO_OTM_INTEGRATION_PVT.is_otm_installed()) THEN
542 --<R12 OTM INTEGRATION END>
543
544 EXCEPTION
545 WHEN FND_API.G_EXC_ERROR THEN
546 ROLLBACK TO create_update_delrec;
547 x_return_status := FND_API.G_RET_STS_ERROR;
548 IF g_fnd_debug = 'Y' THEN
549 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
550 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,c_log_head || l_api_name ||'.EXCEPTION', 'Exception :'||x_progress||x_msg_data);
551 END IF;
552 END IF;
553 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
554 ROLLBACK TO create_update_delrec;
555 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
556 IF g_fnd_debug = 'Y' THEN
557 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
558 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,c_log_head || l_api_name ||'.EXCEPTION', 'Exception :'||x_progress||sqlcode);
559 END IF;
560 END IF;
561 WHEN OTHERS THEN
562 ROLLBACK TO create_update_delrec;
563 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
564 IF g_fnd_debug = 'Y' THEN
565 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) THEN
566 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,c_log_head || l_api_name ||'.EXCEPTION', 'Exception :'||x_progress||sqlcode);
567 END IF;
568 END IF;
569 END;
570
571 /*
572 PROCEDURE test_rcv_call
573 IS
574 l_action_rec WSH_BULK_TYPES_GRP.action_parameters_rectype;
575 l_return_status VARCHAR2(255);
576 l_fte_rec OE_WSH_BULK_GRP.Line_Rec_Type;
577 BEGIN
578 l_action_rec.action_code := 'RECEIPT';
579 l_action_rec.Caller := 'PO';
580 l_action_rec.Phase := '';
581 l_action_rec.Entity := 'PO';
582 make_rcv_call(
583 1.0,
584 l_action_rec,
585 l_return_status,
586 'RECEIPT',
587 29431,
588 NULL,
589 NULL,
590 l_fte_rec);
591
592 END;
593 */
594
595
596 PROCEDURE make_rcv_call
597 (
598 p_api_version IN NUMBER,
599 p_action_rec IN OUT NOCOPY WSH_BULK_TYPES_GRP.action_parameters_rectype,
600 x_return_status IN OUT NOCOPY VARCHAR2,
601 p_action IN VARCHAR2,
602 p_header_id IN NUMBER,
603 p_line_id IN NUMBER,
604 p_line_location_id IN NUMBER,
605 x_fte_rec IN OUT NOCOPY OE_WSH_BULK_GRP.Line_Rec_Type
606 )
607 IS
608 l_api_name VARCHAR2(25) := 'make_rcv_call';
609 l_fte_out_rec WSH_BULK_TYPES_GRP.Bulk_process_out_rec_type;
610 l_msg_count NUMBER;
611 l_msg_data VARCHAR2(2000);
612 l_transaction_type VARCHAR2(25);
613 l_transaction_id NUMBER;
614
615 BEGIN
616 IF (p_action IN ('RECEIPT_CORRECTION', 'RTV_CORRECTION'))
617 THEN
618 l_transaction_type := 'CORRECT';
619
620 -- this works because RTV is called inline
621 SELECT transaction_id
622 INTO l_transaction_id
623 FROM rcv_fte_transaction_lines
624 WHERE header_id = p_header_id
625 AND line_id = p_line_id
626 AND action = p_action
627 AND reported_flag IN ( 'N', 'U')
628 AND rownum = 1;
629
630 ELSIF
631 (p_action = 'RECEIPT')
632 THEN
633 l_transaction_type := 'RECEIVE';
634 ELSIF
635 (p_action = 'RTV')
636 THEN
637 l_transaction_type := 'RETURN TO VENDOR';
638
639 -- this works because RTV is called inline
640 SELECT transaction_id
641 INTO l_transaction_id
642 FROM rcv_fte_transaction_lines
643 WHERE header_id = p_header_id
644 AND line_id = p_line_id
645 AND action = p_action
646 AND reported_flag IN ( 'N', 'U')
647 AND rownum = 1;
648 ELSIF
649 (p_action IN ('ASN', 'CANCEL_ASN', 'RECEIPT_ADD', 'RECEIPT_HEADER_UPD'))
650 THEN l_transaction_type := 'RECEIVE';
651 ELSIF
652 (p_action = 'MATCH')
653 THEN
654 l_transaction_type := 'MATCH';
655 END IF;
656 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
657 FND_LOG.string (FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name || '.begin', 'In make_rcv_call');
658 END IF;
659 -- INSERT INTO ben_test VALUES ('GOT HERE', SYSDATE);
660 IF (p_line_id IS NULL) THEN
661 -- IF (1 = 1) THEN
662 SELECT
663 'PO',
664 poh.po_header_id,
665 pol.po_line_id,
666 rsl.po_release_id,
667 poh.vendor_id,
668 poh.vendor_site_id,
669 pol.item_id,
670 pol.item_description,
671 pol.hazard_class_id,
672 pll.country_of_origin_code,
673 pll.ship_to_location_id,
674 poh.user_hold_flag,
675 pll.qty_rcv_tolerance,
676 pll.receive_close_tolerance,
677 pll.quantity_shipped,
678 rt.subinventory,
679 pol.item_revision,
680 rt.locator_id,
681 pll.need_by_date,
682 pll.promised_date,
683 orf.party_id,
684 poh.freight_terms_lookup_code,
685 poh.fob_lookup_code,
686 pol.vendor_product_num,
687 msi.unit_weight,
688 msi.weight_uom_code,
689 msi.unit_volume,
690 msi.volume_uom_code,
691 rsh.ship_to_org_id,
692 poh.segment1,
693 DECODE (poh.type_lookup_code,
694 'STANDARD', 1,
695 'BLANKET', 2,
696 1),
697 fl.meaning, -- begin
698 pll.quantity,
699 puom.uom_code,
700 pll.quantity_cancelled,
701 rsh.waybill_airbill_num,
702 nvl(rsl.packing_slip, rsh.packing_slip),
703 poh.org_id,
704 pol.line_num,
705 rsh.gross_weight, -- end
706 rsh.gross_weight_uom_code,
707 rsh.net_weight,
708 rsh.net_weight_uom_code,
709 rsh.tar_weight,
710 rsh.tar_weight_uom_code,
711 pll.price_override,
712 poh.currency_code,
713 pol.qc_grade,
714 pll.secondary_quantity,
715 suom.uom_code, --Bug 5130410 FP: BUG 5137236.secondary_unit_of_measure,
716 pll.secondary_quantity,
717 rsl.secondary_quantity_shipped,
718 pll.secondary_quantity_cancelled,
719 suom.uom_code, --Bug 5130410 FP: BUG 5137236.secondary_unit_of_measure,
720
721 rsl.asn_lpn_id, -- used to NVL with rt, they don't care about rt
722 DECODE(p_action, 'ASN', rsl.quantity_shipped,
723 'RECEIPT', distquery.squant,
724 'MATCH', distquery.squant,
725 NVL(rt.quantity,0)),
726 NVL(rtuom.uom_code, muom.uom_code),
727 rt.secondary_quantity,
728 NVL(rtsuom.uom_code, suom.uom_code),
729 rsl.po_line_location_id,
730 pll.shipment_num,
731 por.release_num,
732 pll.days_early_receipt_allowed,
733 pll.days_late_receipt_allowed,
734 poh.shipping_control,
735 pll.drop_ship_flag,
736 rsh.shipment_header_id,
737 rsh.shipment_num,
738 rsh.receipt_num,
739 rsh.shipped_date,
740 DECODE(p_action, 'ASN', rsh.expected_receipt_date, NVL(rt.transaction_date, SYSDATE)),
741 rsh.bill_of_lading,
742 rsh.num_of_containers,
743 rsl.container_num,
744 rsl.truck_num,
745 rsl.shipment_line_id,
746 pll.qty_rcv_exception_code,
747 DECODE(p_action, 'RECEIPT', distquery.maxtrans, 'MATCH', distquery.maxtrans, rt.transaction_id),
748 rsl.shipment_line_id,
749 rsh.shipment_header_id,
750 pll.closed_flag,
751 pll.cancel_flag,
752 pll.closed_code,
753 DECODE (PLT.outside_operation_flag, 'Y', 'GB_OSP', 'GB'),
754 pll.receipt_days_exception_code,
755 pll.enforce_ship_to_location_code,
756 poh.revision_num,
757 por.revision_num,
758 pll.last_update_date,
759 rsl.ship_to_location_id,
760 rsl.item_id,
761 rsl.item_description,
762 rt.country_of_origin_code,
763 rsl.item_revision,
764 orf2.party_id,
765 rsh.freight_terms,
766 rsl.vendor_item_num,
767 rt.qc_grade,
768 rsh.asn_type
769 BULK COLLECT INTO
770 x_fte_rec.source_code,
771 x_fte_rec.header_id,
772 x_fte_rec.line_id,
773 x_fte_rec.source_blanket_reference_id,
774 x_fte_rec.vendor_id,
775 x_fte_rec.ship_from_site_id,
776 x_fte_rec.inventory_item_id,
777 x_fte_rec.item_description,
778 x_fte_rec.hazard_class_id,
779 x_fte_rec.country_of_origin,
780 x_fte_rec.ship_to_location_id,
781 x_fte_rec.hold_code,
782 x_fte_rec.ship_tolerance_above,
783 x_fte_rec.ship_tolerance_below,
784 x_fte_rec.shipped_quantity,
785 x_fte_rec.subinventory,
786 x_fte_rec.revision,
787 x_fte_rec.locator_id,
788 x_fte_rec.request_date,
789 x_fte_rec.schedule_ship_date,
790 x_fte_rec.carrier_id,
791 x_fte_rec.freight_terms_code,
792 x_fte_rec.fob_point_code,
793 x_fte_rec.supplier_item_num,
794 x_fte_rec.net_weight,
795 x_fte_rec.weight_uom_code,
796 x_fte_rec.volume,
797 x_fte_rec.volume_uom_code,
798 x_fte_rec.organization_id,
799 x_fte_rec.source_header_number,
800 x_fte_rec.source_header_type_id, -- fix
801 x_fte_rec.source_header_type_name, -- begin
802 x_fte_rec.ordered_quantity,
803 x_fte_rec.order_quantity_uom,
804 x_fte_rec.cancelled_quantity,
805 x_fte_rec.tracking_number,
806 x_fte_rec.packing_slip_number,
807 x_fte_rec.org_id,
808 x_fte_rec.source_line_number, -- end
809 x_fte_rec.rcv_gross_weight,
810 x_fte_rec.rcv_gross_weight_uom_code,
811 x_fte_rec.rcv_net_weight,
812 x_fte_rec.rcv_net_weight_uom_code,
813 x_fte_rec.rcv_tare_weight,
814 x_fte_rec.rcv_tare_weight_uom_code,
815 x_fte_rec.unit_list_price,
816 x_fte_rec.currency_code,
817 x_fte_rec.preferred_grade,
818 x_fte_rec.ordered_quantity2,
819 x_fte_rec.ordered_quantity_uom2,
820 x_fte_rec.requested_quantity2,
821 x_fte_rec.shipped_quantity2,
822 x_fte_rec.cancelled_quantity2,
823 x_fte_rec.requested_quantity_uom2,
824 x_fte_rec.lpn_id,
825 x_fte_rec.received_quantity,
826 x_fte_rec.received_quantity_uom,
827 x_fte_rec.received_quantity2,
828 x_fte_rec.received_quantity2_uom,
829 x_fte_rec.po_shipment_line_id,
830 x_fte_rec.po_shipment_line_number,
831 x_fte_rec.source_blanket_reference_num,
832 x_fte_rec.days_early_receipt_allowed,
833 x_fte_rec.days_late_receipt_allowed,
834 x_fte_rec.shipping_control,
835 x_fte_rec.drop_ship_flag,
836 x_fte_rec.shipment_header_id,
837 x_fte_rec.shipment_num,
838 x_fte_rec.receipt_num,
839 x_fte_rec.shipped_date,
840 x_fte_rec.expected_receipt_date,
841 x_fte_rec.bill_of_lading,
842 x_fte_rec.num_of_containers,
843 x_fte_rec.container_num,
844 x_fte_rec.truck_num,
845 x_fte_rec.shipment_line_id,
846 x_fte_rec.qty_rcv_exception_code,
847 x_fte_rec.rcv_transaction_id,
848 x_fte_rec.rcv_parent_shipment_line_id,
849 x_fte_rec.rcv_parent_shipment_header_id,
850 x_fte_rec.closed_flag,
851 x_fte_rec.cancelled_flag,
852 x_fte_rec.closed_code,
853 x_fte_rec.source_line_type_code,
854 x_fte_rec.receipt_days_exception_code,
855 x_fte_rec.enforce_ship_to_location_code,
856 x_fte_rec.po_revision,
857 x_fte_rec.release_revision,
858 x_fte_rec.shipping_details_updated_on,
859 x_fte_rec.rcv_ship_to_location_id,
860 x_fte_rec.rcv_inventory_item_id,
861 x_fte_rec.rcv_item_description,
862 x_fte_rec.rcv_country_of_origin,
863 x_fte_rec.rcv_revision,
864 x_fte_rec.rcv_carrier_id,
865 x_fte_rec.rcv_freight_terms_code,
866 x_fte_rec.rcv_supplier_item_num,
867 x_fte_rec.rcv_preferred_grade,
868 x_fte_rec.asn_type
869 FROM
870 po_headers_all poh,
871 po_lines_all pol,
872 po_line_locations_all pll,
873 rcv_transactions rt,
874 mtl_system_items msi,
875 fnd_lookup_values fl,
876 po_releases_all por,
877 mtl_units_of_measure muom,
878 mtl_units_of_measure suom,
879 mtl_units_of_measure puom,
880 mtl_units_of_measure rtuom,
881 mtl_units_of_measure rtsuom,
882 rcv_shipment_headers rsh,
883 rcv_shipment_lines rsl,
884 org_freight orf,
885 org_freight orf2,
886 po_line_types_b plt,
887 (
888 SELECT shipment_header_id,
889 shipment_line_id,
890 sum(quantity) squant,
891 max(transaction_id) maxtrans
892 FROM rcv_transactions
893 WHERE shipment_header_id = p_header_id
894 AND transaction_type IN ('RECEIVE', 'MATCH')
895 AND p_action IN ('RECEIPT', 'MATCH')
896 GROUP BY shipment_line_id,
897 shipment_header_id
898 UNION ALL
899 SELECT shipment_header_id,
900 shipment_line_id,
901 0 squant,
902 0 maxtrans
903 FROM rcv_shipment_lines
904 WHERE shipment_header_id = p_header_id
905 AND p_action NOT IN ('RECEIPT', 'MATCH')
906 ) distquery
907 WHERE
908 rsl.shipment_header_id = p_header_id
909 AND rsl.shipment_header_id = rsh.shipment_header_id
910 AND rsl.shipment_header_id = distquery.shipment_header_id
911 AND rsl.shipment_line_id = distquery.shipment_line_id
912 AND (distquery.maxtrans = rt.transaction_id OR
913 distquery.maxtrans = 0)
914 AND rsl.po_header_id = poh.po_header_id
915 AND rsl.po_line_id = pol.po_line_id (+)
916 AND pol.line_type_id = plt.line_type_id
917 AND plt.order_type_lookup_code = 'QUANTITY' --bugfix 5525510
918 AND rsl.po_line_location_id = pll.line_location_id (+)
919 AND rsl.shipment_line_id = rt.shipment_line_id (+)
920 AND (rt.transaction_type = l_transaction_type
921 OR rt.transaction_type IS NULL)
922 AND (rt.transaction_id IN
923 (
924 SELECT transaction_id
925 FROM rcv_fte_transaction_lines
926 WHERE header_id = p_header_id
927 AND action = p_action
928 AND reported_flag IN ( 'N', 'U')
929 )
930 OR rt.transaction_id IN
931 (
932 SELECT max(transaction_id)
933 FROM rcv_transactions
934 WHERE shipment_header_id = p_header_id
935 AND transaction_type = 'RECEIVE'
936 AND p_action = 'RECEIPT_HEADER_UPD'
937 )
938 OR p_action NOT IN ('RTV', 'RECEIPT_ADD', 'RECEIPT', 'RECEIPT_HEADER_UPD', 'MATCH')
939 )
940 AND rsl.po_release_id = por.po_release_id (+)
941 AND fl.lookup_code = poh.type_lookup_code
942 AND fl.lookup_type = 'PO TYPE'
943 AND fl.language = USERENV('LANG')
944 AND rsl.item_id = msi.inventory_item_id (+)
945 AND rsl.to_organization_id = msi.organization_id (+)
946 AND rsl.unit_of_measure = muom.unit_of_measure (+)
947 AND rsl.secondary_unit_of_measure = suom.unit_of_measure (+)
948 AND rt.unit_of_measure = rtuom.unit_of_measure (+)
949 AND rt.secondary_unit_of_measure = rtsuom.unit_of_measure (+)
950 AND pol.unit_meas_lookup_code = puom.unit_of_measure (+)
951 AND pll.ship_via_lookup_code = orf.freight_code (+)
952 AND pll.ship_to_organization_id = orf.organization_id (+)
953 AND (orf.language = USERENV('LANG') OR orf.language IS NULL)
954 AND rsh.freight_carrier_code = orf2.freight_code (+)
955 AND rsh.ship_to_org_id = orf2.organization_id (+)
956 AND (orf2.language = USERENV('LANG') OR orf2.language IS NULL);
957 ELSE
958 -- SAME QUERY, but with a line id constraint in the where clause
959 -- This is messy to read, but performs much better than dynamic sql,
960 -- Or an NVL in the WHERE clause.
961 SELECT
962 'PO',
963 poh.po_header_id,
964 pol.po_line_id,
965 rsl.po_release_id,
966 poh.vendor_id,
967 poh.vendor_site_id,
968 pol.item_id,
969 pol.item_description,
970 pol.hazard_class_id,
971 pll.country_of_origin_code,
972 pll.ship_to_location_id,
973 poh.user_hold_flag,
974 pll.qty_rcv_tolerance,
975 pll.receive_close_tolerance,
976 pll.quantity_shipped,
977 rt.subinventory,
978 pol.item_revision,
979 rt.locator_id,
980 pll.need_by_date,
981 pll.promised_date,
982 orf.party_id,
983 poh.freight_terms_lookup_code,
984 poh.fob_lookup_code,
985 pol.vendor_product_num,
986 msi.unit_weight,
987 msi.weight_uom_code,
988 msi.unit_volume,
989 msi.volume_uom_code,
990 rsh.ship_to_org_id,
991 poh.segment1,
992 DECODE( poh.type_lookup_code,
993 'STANDARD', 1,
994 'BLANKET', 2,
995 1),
996 fl.meaning,
997 pll.quantity,
998 puom.uom_code,
999 pll.quantity_cancelled,
1000 rsh.waybill_airbill_num,
1001 nvl(rsl.packing_slip, rsh.packing_slip),
1002 poh.org_id,
1003 pol.line_num,
1004 rsh.gross_weight,
1005 rsh.gross_weight_uom_code,
1006 rsh.net_weight,
1007 rsh.net_weight_uom_code,
1008 rsh.tar_weight,
1009 rsh.tar_weight_uom_code,
1010 pll.price_override,
1011 poh.currency_code,
1012 pol.qc_grade,
1013 pll.secondary_quantity,
1014 suom.uom_code, --Bug 5130410 FP: BUG 5137236.secondary_unit_of_measure,
1015 pll.secondary_quantity,
1016 rsl.secondary_quantity_shipped,
1017 pll.secondary_quantity_cancelled,
1018 suom.uom_code, --Bug 5130410 FP: BUG 5137236.secondary_unit_of_measure,
1019 rsl.asn_lpn_id, -- fte doesn't care about rt.lpn_id
1020 DECODE(p_action, 'ASN', rsl.quantity_shipped, NVL(rt.quantity,0)),
1021 NVL(rtuom.uom_code, muom.uom_code),
1022 rt.secondary_quantity,
1023 NVL(rtsuom.uom_code, suom.uom_code),
1024 rsl.po_line_location_id,
1025 pll.shipment_num,
1026 por.release_num,
1027 pll.days_early_receipt_allowed,
1028 pll.days_late_receipt_allowed,
1029 poh.shipping_control,
1030 pll.drop_ship_flag,
1031 rsh.shipment_header_id,
1032 rsh.shipment_num,
1033 rsh.receipt_num,
1034 rsh.shipped_date,
1035 DECODE(p_action, 'ASN', rsh.expected_receipt_date, NVL(rt.transaction_date, SYSDATE)),
1036 rsh.bill_of_lading,
1037 rsh.num_of_containers,
1038 rsl.container_num,
1039 rsl.truck_num,
1040 rsl.shipment_line_id,
1041 pll.qty_rcv_exception_code,
1042 rt.transaction_id,
1043 rsl.shipment_line_id,
1044 rsh.shipment_header_id,
1045 pll.closed_flag,
1046 pll.cancel_flag,
1047 pll.closed_code,
1048 DECODE(PLT.outside_operation_flag, 'Y', 'GB_OSP', 'GB'),
1049 pll.receipt_days_exception_code,
1050 pll.enforce_ship_to_location_code,
1051 poh.revision_num,
1052 por.revision_num,
1053 pll.last_update_date,
1054 rsl.ship_to_location_id,
1055 rsl.item_id,
1056 rsl.item_description,
1057 rt.country_of_origin_code,
1058 rsl.item_revision,
1059 orf2.party_id,
1060 rsh.freight_terms,
1061 rsl.vendor_item_num,
1062 rt.qc_grade,
1063 rsh.asn_type
1064 BULK COLLECT INTO
1065 x_fte_rec.source_code,
1066 x_fte_rec.header_id,
1067 x_fte_rec.line_id,
1068 x_fte_rec.source_blanket_reference_id,
1069 x_fte_rec.vendor_id,
1070 x_fte_rec.ship_from_site_id,
1071 x_fte_rec.inventory_item_id,
1072 x_fte_rec.item_description,
1073 x_fte_rec.hazard_class_id,
1074 x_fte_rec.country_of_origin,
1075 x_fte_rec.ship_to_location_id,
1076 x_fte_rec.hold_code,
1077 x_fte_rec.ship_tolerance_above,
1078 x_fte_rec.ship_tolerance_below,
1079 x_fte_rec.shipped_quantity,
1080 x_fte_rec.subinventory,
1081 x_fte_rec.revision,
1082 x_fte_rec.locator_id,
1083 x_fte_rec.request_date,
1084 x_fte_rec.schedule_ship_date,
1085 x_fte_rec.carrier_id,
1086 x_fte_rec.freight_terms_code,
1087 x_fte_rec.fob_point_code,
1088 x_fte_rec.supplier_item_num,
1089 x_fte_rec.net_weight,
1090 x_fte_rec.weight_uom_code,
1091 x_fte_rec.volume,
1092 x_fte_rec.volume_uom_code,
1093 x_fte_rec.organization_id,
1094 x_fte_rec.source_header_number,
1095 x_fte_rec.source_header_type_id,
1096 x_fte_rec.source_header_type_name,
1097 x_fte_rec.ordered_quantity,
1098 x_fte_rec.order_quantity_uom,
1099 x_fte_rec.cancelled_quantity,
1100 x_fte_rec.tracking_number,
1101 x_fte_rec.packing_slip_number,
1102 x_fte_rec.org_id,
1103 x_fte_rec.source_line_number,
1104 x_fte_rec.rcv_gross_weight,
1105 x_fte_rec.rcv_gross_weight_uom_code,
1106 x_fte_rec.rcv_net_weight,
1107 x_fte_rec.rcv_net_weight_uom_code,
1108 x_fte_rec.rcv_tare_weight,
1109 x_fte_rec.rcv_tare_weight_uom_code,
1110 x_fte_rec.unit_list_price,
1111 x_fte_rec.currency_code,
1112 x_fte_rec.preferred_grade,
1113 x_fte_rec.ordered_quantity2,
1114 x_fte_rec.ordered_quantity_uom2,
1115 x_fte_rec.requested_quantity2,
1116 x_fte_rec.shipped_quantity2,
1117 x_fte_rec.cancelled_quantity2,
1118 x_fte_rec.requested_quantity_uom2,
1119 x_fte_rec.lpn_id,
1120 x_fte_rec.received_quantity,
1121 x_fte_rec.received_quantity_uom,
1122 x_fte_rec.received_quantity2,
1123 x_fte_rec.received_quantity2_uom,
1124 x_fte_rec.po_shipment_line_id,
1125 x_fte_rec.po_shipment_line_number,
1126 x_fte_rec.source_blanket_reference_num,
1127 x_fte_rec.days_early_receipt_allowed,
1128 x_fte_rec.days_late_receipt_allowed,
1129 x_fte_rec.shipping_control,
1130 x_fte_rec.drop_ship_flag,
1131 x_fte_rec.shipment_header_id,
1132 x_fte_rec.shipment_num,
1133 x_fte_rec.receipt_num,
1134 x_fte_rec.shipped_date,
1135 x_fte_rec.expected_receipt_date,
1136 x_fte_rec.bill_of_lading,
1137 x_fte_rec.num_of_containers,
1138 x_fte_rec.container_num,
1139 x_fte_rec.truck_num,
1140 x_fte_rec.shipment_line_id,
1141 x_fte_rec.qty_rcv_exception_code,
1142 x_fte_rec.rcv_transaction_id,
1143 x_fte_rec.rcv_parent_shipment_line_id,
1144 x_fte_rec.rcv_parent_shipment_header_id,
1145 x_fte_rec.closed_flag,
1146 x_fte_rec.cancelled_flag,
1147 x_fte_rec.closed_code,
1148 x_fte_rec.source_line_type_code,
1149 x_fte_rec.receipt_days_exception_code,
1150 x_fte_rec.enforce_ship_to_location_code,
1151 x_fte_rec.po_revision,
1152 x_fte_rec.release_revision,
1153 x_fte_rec.shipping_details_updated_on,
1154 x_fte_rec.rcv_ship_to_location_id,
1155 x_fte_rec.rcv_inventory_item_id,
1156 x_fte_rec.rcv_item_description,
1157 x_fte_rec.rcv_country_of_origin,
1158 x_fte_rec.rcv_revision,
1159 x_fte_rec.rcv_carrier_id,
1160 x_fte_rec.rcv_freight_terms_code,
1161 x_fte_rec.rcv_supplier_item_num,
1162 x_fte_rec.rcv_preferred_grade,
1163 x_fte_rec.asn_type
1164 FROM
1165 po_headers_all poh,
1166 po_lines_all pol,
1167 po_line_locations_all pll,
1168 rcv_transactions rt,
1169 mtl_system_items msi,
1170 fnd_lookup_values fl,
1171 po_releases_all por,
1172 mtl_units_of_measure muom,
1173 mtl_units_of_measure suom,
1174 mtl_units_of_measure puom,
1175 mtl_units_of_measure rtuom,
1176 mtl_units_of_measure rtsuom,
1177 rcv_shipment_headers rsh,
1178 rcv_shipment_lines rsl,
1179 org_freight orf,
1180 org_freight orf2,
1181 po_line_types_b plt
1182 WHERE
1183 rsl.shipment_header_id = p_header_id
1184 AND rsl.shipment_line_id = p_line_id
1185 AND rsl.shipment_header_id = rsh.shipment_header_id
1186 AND rsl.po_header_id = poh.po_header_id
1187 AND rsl.po_line_id = pol.po_line_id (+)
1188 AND pol.line_type_id = plt.line_type_id
1189 AND plt.order_type_lookup_code = 'QUANTITY' --bugfix 5525510
1190 AND rsl.po_line_location_id = pll.line_location_id (+)
1191 AND rsl.shipment_line_id = rt.shipment_line_id (+)
1192 AND (rt.transaction_type = l_transaction_type
1193 OR rt.transaction_type IS NULL)
1194 AND (rt.transaction_id = l_transaction_id
1195 OR l_transaction_id IS NULL)
1196 AND rsl.po_release_id = por.po_release_id (+)
1197 AND fl.lookup_code = poh.type_lookup_code
1198 AND fl.lookup_type = 'PO TYPE'
1199 AND fl.language = USERENV('LANG')
1200 AND rsl.item_id = msi.inventory_item_id (+)
1201 AND rsl.to_organization_id = msi.organization_id (+)
1202 AND rsl.unit_of_measure = muom.unit_of_measure (+)
1203 AND rt.unit_of_measure = rtuom.unit_of_measure (+)
1204 AND rt.secondary_unit_of_measure = rtsuom.unit_of_measure(+)
1205 AND rsl.secondary_unit_of_measure = suom.unit_of_measure (+)
1206 AND pol.unit_meas_lookup_code = puom.unit_of_measure (+)
1207 AND pll.ship_via_lookup_code = orf.freight_code (+)
1208 AND pll.ship_to_organization_id = orf.organization_id (+)
1209 AND (orf.language = USERENV('LANG') OR orf.language IS NULL)
1210 AND rsh.freight_carrier_code = orf2.freight_code (+)
1211 AND rsh.ship_to_org_id = orf2.organization_id (+)
1212 AND (orf2.language = USERENV('LANG') OR orf2.language IS NULL);
1213
1214 END IF;
1215 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1216 FND_LOG.string (FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name || '.begin', 'After Queries, rows= ' || x_fte_rec.source_code.LAST);
1217 END IF;
1218 -- INSERT INTO ben_test VALUES ('GOT HERE TOO', SYSDATE);
1219 p_action_rec.ship_from_location_id := rcv_table_functions.get_rsh_row_from_id(p_header_id).ship_from_location_id;
1220
1221 WSH_BULK_PROCESS_GRP.create_update_delivery_details
1222 (
1223 p_api_version_number => p_api_version,
1224 p_init_msg_list => FND_API.G_FALSE,
1225 p_commit => FND_API.G_FALSE,
1226 p_action_prms => p_action_rec,
1227 p_line_rec => x_fte_rec,
1228 x_Out_Rec => l_fte_out_rec,
1229 x_return_status => x_return_status,
1230 x_msg_count => l_msg_count,
1231 x_msg_data => l_msg_data
1232 );
1233 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1234 FND_LOG.string (FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name || '.begin', 'RS: ' || x_return_status || ' x_msg_data: ' || l_msg_data);
1235 END IF;
1236 --INSERT INTO ben_test VALUES ('RS: ' || x_return_status || ' x_msg_data: ' || l_msg_data, SYSDATE);
1237 EXCEPTION
1238 WHEN NO_DATA_FOUND THEN
1239 NULL;
1240 WHEN OTHERS THEN
1241 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1242 l_msg_data := 'Errm ' || sqlerrm;
1243 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1244 FND_LOG.string (FND_LOG.LEVEL_STATEMENT, c_log_head || l_api_name || '.begin', l_msg_data);
1245 END IF;
1246 -- INSERT INTO ben_test VALUES (l_msg_data, SYSDATE);
1247 RAISE;
1248
1249 END make_rcv_call;
1250
1251
1252 -------------------------------------------------------------------------------
1253 --Start of Comments
1254 --Name: get_approved_po
1255 --Pre-reqs:
1256 -- None.
1257 --Modifies:
1258 -- None.
1259 --Locks:
1260 -- None.
1261 --Function:
1262 -- Get data for delivery record from Approved Standard Purchase Order
1263 --Parameters:
1264 --IN:
1265 --p_api_version
1266 -- Specifies API version.
1267 --p_header_id
1268 -- Corresponding to po_header_id
1269 --p_line_id
1270 -- Corresponding to po_line_id
1271 --p_line_location_id
1272 -- Corresponding to po_line_location_id
1273 --IN OUT:
1274 --x_return_status
1275 -- Indicates API return status as 'S', 'E' or 'U'.
1276 --Testing:
1277 -- Need to integrate FTE to implement the testing.
1278 --End of Comments
1279 -------------------------------------------------------------------------------
1280
1281
1282 PROCEDURE get_approved_po
1283 (
1284 p_api_version IN NUMBER,
1285 x_return_status IN OUT NOCOPY VARCHAR2,
1286 p_header_id IN NUMBER,
1287 p_line_id IN NUMBER,
1288 p_line_location_id IN NUMBER,
1289 x_fte_rec IN OUT NOCOPY OE_WSH_BULK_GRP.Line_Rec_Type
1290 )
1291 IS
1292 l_api_name CONSTANT VARCHAR2(100) := 'get_approved_po';
1293 l_api_version CONSTANT NUMBER := 1.0;
1294
1295 BEGIN
1296 -- Standard call to check for call compatibility.
1297 IF NOT FND_API.Compatible_API_Call
1298 (
1299 p_current_version_number => l_api_version,
1300 p_caller_version_number => p_api_version,
1301 p_api_name => l_api_name,
1302 p_pkg_name => 'PO_DELREC_PVT'
1303 )
1304 THEN
1305 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1306 END IF;
1307
1308 -- The following is for query data for delivery record from:
1309 -- Approved Standard PO, Standard PO referencing GA or Contract
1310
1311 --SQL What: Querying data from Approved Standard PO of quantity
1312 --SQL based items.
1313 --SQL Where: MSI.inventory_item_id (+) = POL.item_id
1314 --SQL NVL(MSI.organization_id, POLL.ship_to_organization_id)
1315 --SQL = POLL.ship_to_organization_id
1316 --SQL To get record for one-time item
1317 --SQL Why: Same as SQL What
1318 SELECT 'PO', -- source code
1319 POH.po_header_id,
1320 POH.vendor_id,
1321 POH.vendor_site_id,
1322 POH.user_hold_flag,
1323 POH.freight_terms_lookup_code,
1324 POH.fob_lookup_code,
1325 POH.segment1,
1326 1, -- stands for 'PO'
1327 PDT.type_name,
1328 POH.org_id,
1329 POH.currency_code,
1330 POH.shipping_control,
1331 POH.revision_num,
1332 POL.po_line_id,
1333 POL.item_id,
1334 POL.item_description,
1335 POL.hazard_class_id,
1336 POL.item_revision,
1337 POL.vendor_product_num,
1338 POL.line_num,
1339 DECODE(PLT.outside_operation_flag, 'Y', 'GB_OSP', 'GB'),
1340 POLL.line_location_id,
1341 POLL.country_of_origin_code,
1342 POLL.ship_to_location_id,
1343 POLL.qty_rcv_tolerance,
1344 POLL.receive_close_tolerance,
1345 POLL.quantity_shipped,
1346 POLL.need_by_date,
1347 POLL.promised_date,
1348 POLL.ship_to_organization_id,
1349 POLL.quantity,
1350 MUOM.uom_code,
1351 POLL.quantity_cancelled,
1352 POLL.price_override,
1353 POLL.preferred_grade,
1354 POLL.secondary_quantity,
1355 MUOM1.uom_code,
1356 POLL.secondary_quantity,
1357 POLL.secondary_quantity_cancelled,
1358 MUOM1.uom_code,
1359 POLL.shipment_num,
1360 POLL.days_early_receipt_allowed,
1361 POLL.days_late_receipt_allowed,
1362 POLL.drop_ship_flag,
1363 POLL.qty_rcv_exception_code,
1364 POLL.closed_flag,
1365 POLL.closed_code,
1366 POLL.cancel_flag,
1367 POLL.receipt_days_exception_code,
1368 POLL.enforce_ship_to_location_code,
1369 POLL.last_update_date,
1370 FRT.party_id,
1371 MSI.unit_weight,
1372 MSI.weight_uom_code,
1373 MSI.unit_volume,
1374 MSI.volume_uom_code
1375 BULK COLLECT INTO
1376 x_fte_rec.source_code, -- Header
1377 x_fte_rec.header_id,
1378 x_fte_rec.vendor_id,
1379 x_fte_rec.ship_from_site_id,
1380 x_fte_rec.hold_code,
1381 x_fte_rec.freight_terms_code,
1382 x_fte_rec.fob_point_code,
1383 x_fte_rec.source_header_number,
1384 x_fte_rec.source_header_type_id,
1385 x_fte_rec.source_header_type_name,
1386 x_fte_rec.org_id,
1387 x_fte_rec.currency_code,
1388 x_fte_rec.shipping_control,
1389 x_fte_rec.po_revision,
1390 x_fte_rec.line_id, -- Line
1391 x_fte_rec.inventory_item_id,
1392 x_fte_rec.item_description,
1393 x_fte_rec.hazard_class_id,
1394 x_fte_rec.revision,
1395 x_fte_rec.supplier_item_num,
1396 x_fte_rec.source_line_number,
1397 x_fte_rec.source_line_type_code,
1398 x_fte_rec.po_shipment_line_id, -- Shipment
1399 x_fte_rec.country_of_origin,
1400 x_fte_rec.ship_to_location_id,
1401 x_fte_rec.ship_tolerance_above,
1402 x_fte_rec.ship_tolerance_below,
1403 x_fte_rec.shipped_quantity,
1404 x_fte_rec.request_date,
1405 x_fte_rec.schedule_ship_date,
1406 x_fte_rec.organization_id,
1407 x_fte_rec.ordered_quantity,
1408 x_fte_rec.order_quantity_uom,
1409 x_fte_rec.cancelled_quantity,
1410 x_fte_rec.unit_list_price,
1411 x_fte_rec.preferred_grade,
1412 x_fte_rec.ordered_quantity2,
1413 x_fte_rec.ordered_quantity_uom2,
1414 x_fte_rec.requested_quantity2,
1415 x_fte_rec.cancelled_quantity2,
1416 x_fte_rec.requested_quantity_uom2,
1417 x_fte_rec.po_shipment_line_number,
1418 x_fte_rec.days_early_receipt_allowed,
1419 x_fte_rec.days_late_receipt_allowed,
1420 x_fte_rec.drop_ship_flag,
1421 x_fte_rec.qty_rcv_exception_code,
1422 x_fte_rec.closed_flag,
1423 x_fte_rec.closed_code,
1424 x_fte_rec.cancelled_flag,
1425 x_fte_rec.receipt_days_exception_code,
1426 x_fte_rec.enforce_ship_to_location_code,
1427 x_fte_rec.shipping_details_updated_on,
1428 x_fte_rec.carrier_id, -- Others
1429 x_fte_rec.net_weight,
1430 x_fte_rec.weight_uom_code,
1431 x_fte_rec.volume,
1432 x_fte_rec.volume_uom_code
1433 FROM PO_HEADERS POH,
1434 PO_LINES POL,
1435 PO_LINE_LOCATIONS POLL,
1436 PO_LINE_TYPES_B PLT,
1437 ORG_FREIGHT_TL FRT,
1438 MTL_SYSTEM_ITEMS_B MSI,
1439 PO_DOCUMENT_TYPES_VL PDT,
1440 MTL_UNITS_OF_MEASURE MUOM,
1441 MTL_UNITS_OF_MEASURE MUOM1
1442 WHERE POH.po_header_id = p_header_id
1443 AND PDT.document_type_code = 'PO'
1444 AND PDT.document_subtype = POH.type_lookup_code
1445 AND POL.po_header_id = POH.po_header_id
1446 AND POLL.po_line_id = POL.po_line_id
1447 AND POL.po_line_id = NVL(p_line_id, POL.po_line_id)
1448 AND POLL.line_location_id
1449 = NVL(p_line_location_id, POLL.line_location_id)
1450 AND POL.line_type_id = PLT.line_type_id
1451 AND PLT.order_type_lookup_code = 'QUANTITY'
1452 AND FRT.freight_code (+) = POH.ship_via_lookup_code
1453 AND FRT.language (+) = USERENV('LANG')
1454 AND NVL(FRT.organization_id, POLL.ship_to_organization_id)
1455 = POLL.ship_to_organization_id
1456 AND MSI.inventory_item_id (+) = POL.item_id
1457 AND NVL(MSI.organization_id, POLL.ship_to_organization_id)
1458 = POLL.ship_to_organization_id
1459 AND MUOM.unit_of_measure(+) = POL.unit_meas_lookup_code
1460 AND MUOM1.unit_of_measure(+) = POLL.secondary_unit_of_measure
1461 AND NVL(POLL.approved_flag, 'N') = 'Y'
1462 AND NVL(POLL.cancel_flag, 'N') <> 'Y'; -- Bug 3581992
1463
1464 EXCEPTION
1465 WHEN NO_DATA_FOUND THEN
1466 NULL;
1467 WHEN OTHERS THEN
1468 x_return_status := FND_API.G_RET_STS_ERROR;
1469 END;
1470
1471
1472
1473
1474 -------------------------------------------------------------------------------
1475 --Start of Comments
1476 --Name: get_cancelled_po
1477 --Pre-reqs:
1478 -- None.
1479 --Modifies:
1480 -- None.
1481 --Locks:
1482 -- None.
1483 --Function:
1484 -- Get data for delivery record from Cancelled Standard Purchase Order
1485 --Parameters:
1486 --IN:
1487 --p_api_version
1488 -- Specifies API version.
1489 --p_header_id
1490 -- Corresponding to po_header_id
1491 --p_line_id
1492 -- Corresponding to po_line_id
1493 --p_line_location_id
1494 -- Corresponding to po_line_location_id
1495 --IN OUT:
1496 --x_return_status
1497 -- Indicates API return status as 'S', 'E' or 'U'.
1498 --Testing:
1499 -- Need to integrate FTE to implement the testing.
1500 --End of Comments
1501 -------------------------------------------------------------------------------
1502
1503
1504 PROCEDURE get_cancelled_po
1505 (
1506 p_api_version IN NUMBER,
1507 x_return_status IN OUT NOCOPY VARCHAR2,
1508 p_header_id IN NUMBER,
1509 p_line_id IN NUMBER,
1510 p_line_location_id IN NUMBER,
1511 x_fte_rec IN OUT NOCOPY OE_WSH_BULK_GRP.Line_Rec_Type
1512 )
1513 IS
1514 l_api_name CONSTANT VARCHAR2(100) := 'get_cancelled_po';
1515 l_api_version CONSTANT NUMBER := 1.0;
1516
1517 BEGIN
1518 -- Standard call to check for call compatibility.
1519 IF NOT FND_API.Compatible_API_Call
1520 (
1521 p_current_version_number => l_api_version,
1522 p_caller_version_number => p_api_version,
1523 p_api_name => l_api_name,
1524 p_pkg_name => 'PO_DELREC_PVT'
1525 )
1526 THEN
1527 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1528 END IF;
1529
1530 -- The following is for query data for delivery record from:
1531 -- Cancelled Standard PO, Standard PO referencing GA or Contract
1532
1533 --SQL What: Querying data from Cancelled Standard PO of quantity
1534 --SQL based items.
1535 --SQL Where: MSI.inventory_item_id (+) = POL.item_id
1536 --SQL NVL(MSI.organization_id, POLL.ship_to_organization_id)
1537 --SQL = POLL.ship_to_organization_id
1538 --SQL To get record for one-time item
1539 --SQL Why: Same as SQL What
1540 SELECT 'PO', -- source code
1541 POH.po_header_id,
1542 POH.vendor_id,
1543 POH.vendor_site_id,
1544 POH.user_hold_flag,
1545 POH.freight_terms_lookup_code,
1546 POH.fob_lookup_code,
1547 POH.segment1,
1548 1, -- stands for 'PO'
1549 PDT.type_name,
1550 POH.org_id,
1551 POH.currency_code,
1552 POH.shipping_control,
1553 POH.revision_num,
1554 POL.po_line_id,
1555 POL.item_id,
1556 POL.item_description,
1557 POL.hazard_class_id,
1558 POL.item_revision,
1559 POL.vendor_product_num,
1560 POL.line_num,
1561 DECODE(PLT.outside_operation_flag, 'Y', 'GB_OSP', 'GB'),
1562 POLL.line_location_id,
1563 POLL.country_of_origin_code,
1564 POLL.ship_to_location_id,
1565 POLL.qty_rcv_tolerance,
1566 POLL.receive_close_tolerance,
1567 POLL.quantity_shipped,
1568 POLL.need_by_date,
1569 POLL.promised_date,
1570 POLL.ship_to_organization_id,
1571 POLL.quantity,
1572 MUOM.uom_code,
1573 POLL.quantity_cancelled,
1574 POLL.price_override,
1575 POLL.preferred_grade,
1576 POLL.secondary_quantity,
1577 MUOM1.uom_code,
1578 POLL.secondary_quantity,
1579 POLL.secondary_quantity_cancelled,
1580 MUOM1.uom_code,
1581 POLL.shipment_num,
1582 POLL.days_early_receipt_allowed,
1583 POLL.days_late_receipt_allowed,
1584 POLL.drop_ship_flag,
1585 POLL.qty_rcv_exception_code,
1586 POLL.closed_flag,
1587 POLL.closed_code,
1588 POLL.cancel_flag,
1589 POLL.receipt_days_exception_code,
1590 POLL.enforce_ship_to_location_code,
1591 POLL.last_update_date,
1592 FRT.party_id,
1593 MSI.unit_weight,
1594 MSI.weight_uom_code,
1595 MSI.unit_volume,
1596 MSI.volume_uom_code
1597 BULK COLLECT INTO
1598 x_fte_rec.source_code, -- Header
1599 x_fte_rec.header_id,
1600 x_fte_rec.vendor_id,
1601 x_fte_rec.ship_from_site_id,
1602 x_fte_rec.hold_code,
1603 x_fte_rec.freight_terms_code,
1604 x_fte_rec.fob_point_code,
1605 x_fte_rec.source_header_number,
1606 x_fte_rec.source_header_type_id,
1607 x_fte_rec.source_header_type_name,
1608 x_fte_rec.org_id,
1609 x_fte_rec.currency_code,
1610 x_fte_rec.shipping_control,
1611 x_fte_rec.po_revision,
1612 x_fte_rec.line_id, -- Line
1613 x_fte_rec.inventory_item_id,
1614 x_fte_rec.item_description,
1615 x_fte_rec.hazard_class_id,
1616 x_fte_rec.revision,
1617 x_fte_rec.supplier_item_num,
1618 x_fte_rec.source_line_number,
1619 x_fte_rec.source_line_type_code,
1620 x_fte_rec.po_shipment_line_id, -- Shipment
1621 x_fte_rec.country_of_origin,
1622 x_fte_rec.ship_to_location_id,
1623 x_fte_rec.ship_tolerance_above,
1624 x_fte_rec.ship_tolerance_below,
1625 x_fte_rec.shipped_quantity,
1626 x_fte_rec.request_date,
1627 x_fte_rec.schedule_ship_date,
1628 x_fte_rec.organization_id,
1629 x_fte_rec.ordered_quantity,
1630 x_fte_rec.order_quantity_uom,
1631 x_fte_rec.cancelled_quantity,
1632 x_fte_rec.unit_list_price,
1633 x_fte_rec.preferred_grade,
1634 x_fte_rec.ordered_quantity2,
1635 x_fte_rec.ordered_quantity_uom2,
1636 x_fte_rec.requested_quantity2,
1637 x_fte_rec.cancelled_quantity2,
1638 x_fte_rec.requested_quantity_uom2,
1639 x_fte_rec.po_shipment_line_number,
1640 x_fte_rec.days_early_receipt_allowed,
1641 x_fte_rec.days_late_receipt_allowed,
1642 x_fte_rec.drop_ship_flag,
1643 x_fte_rec.qty_rcv_exception_code,
1644 x_fte_rec.closed_flag,
1645 x_fte_rec.closed_code,
1646 x_fte_rec.cancelled_flag,
1647 x_fte_rec.receipt_days_exception_code,
1648 x_fte_rec.enforce_ship_to_location_code,
1649 x_fte_rec.shipping_details_updated_on,
1650 x_fte_rec.carrier_id, -- Others
1651 x_fte_rec.net_weight,
1652 x_fte_rec.weight_uom_code,
1653 x_fte_rec.volume,
1654 x_fte_rec.volume_uom_code
1655 FROM PO_HEADERS POH,
1656 PO_LINES POL,
1657 PO_LINE_LOCATIONS POLL,
1658 PO_LINE_TYPES_B PLT,
1659 ORG_FREIGHT_TL FRT,
1660 MTL_SYSTEM_ITEMS_B MSI,
1661 PO_DOCUMENT_TYPES_VL PDT,
1662 MTL_UNITS_OF_MEASURE MUOM,
1663 MTL_UNITS_OF_MEASURE MUOM1
1664 WHERE POH.po_header_id = p_header_id
1665 AND PDT.document_type_code = 'PO'
1666 AND PDT.document_subtype = POH.type_lookup_code
1667 AND POL.po_header_id = POH.po_header_id
1668 AND POLL.po_line_id = POL.po_line_id
1669 AND POL.po_line_id = NVL(p_line_id, POL.po_line_id)
1670 AND POLL.line_location_id
1671 = NVL(p_line_location_id, POLL.line_location_id)
1672 AND POL.line_type_id = PLT.line_type_id
1673 AND PLT.order_type_lookup_code = 'QUANTITY'
1674 AND FRT.freight_code (+) = POH.ship_via_lookup_code
1675 AND FRT.language (+) = USERENV('LANG')
1676 AND NVL(FRT.organization_id, POLL.ship_to_organization_id)
1677 = POLL.ship_to_organization_id
1678 AND MSI.inventory_item_id (+) = POL.item_id
1679 AND NVL(MSI.organization_id, POLL.ship_to_organization_id)
1680 = POLL.ship_to_organization_id
1681 AND MUOM.unit_of_measure(+) = POL.unit_meas_lookup_code
1682 AND MUOM1.unit_of_measure(+) = POLL.secondary_unit_of_measure
1683 AND NVL(POLL.cancel_flag, 'N') = 'Y';
1684
1685 EXCEPTION
1686 WHEN NO_DATA_FOUND THEN
1687 NULL;
1688 WHEN OTHERS THEN
1689 x_return_status := FND_API.G_RET_STS_ERROR;
1690 END;
1691
1692
1693
1694
1695 -------------------------------------------------------------------------------
1696 --Start of Comments
1697 --Name: get_opened_po
1698 --Pre-reqs:
1699 -- None.
1700 --Modifies:
1701 -- None.
1702 --Locks:
1703 -- None.
1704 --Function:
1705 -- Get data for delivery record from Opened Standard Purchase Order
1706 --Parameters:
1707 --IN:
1708 --p_api_version
1709 -- Specifies API version.
1710 --p_header_id
1711 -- Corresponding to po_header_id
1712 --p_line_id
1713 -- Corresponding to po_line_id
1714 --p_line_location_id
1715 -- Corresponding to po_line_location_id
1716 --IN OUT:
1717 --x_return_status
1718 -- Indicates API return status as 'S', 'E' or 'U'.
1719 --Testing:
1720 -- Need to integrate FTE to implement the testing.
1721 --End of Comments
1722 -------------------------------------------------------------------------------
1723
1724
1725 PROCEDURE get_opened_po
1726 (
1727 p_api_version IN NUMBER,
1728 x_return_status IN OUT NOCOPY VARCHAR2,
1729 p_header_id IN NUMBER,
1730 p_line_id IN NUMBER,
1731 p_line_location_id IN NUMBER,
1732 x_fte_rec IN OUT NOCOPY OE_WSH_BULK_GRP.Line_Rec_Type
1733 )
1734 IS
1735 l_api_name CONSTANT VARCHAR2(100) := 'get_opened_po';
1736 l_api_version CONSTANT NUMBER := 1.0;
1737
1738 BEGIN
1739 -- Standard call to check for call compatibility.
1740 IF NOT FND_API.Compatible_API_Call
1741 (
1742 p_current_version_number => l_api_version,
1743 p_caller_version_number => p_api_version,
1744 p_api_name => l_api_name,
1745 p_pkg_name => 'PO_DELREC_PVT'
1746 )
1747 THEN
1748 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1749 END IF;
1750
1751 -- The following is for query data for delivery record from:
1752 -- Opened Standard PO, Standard PO referencing GA or Contract
1753
1754 --SQL What: Querying data from Opened Standard PO of quantity
1755 --SQL based items.
1756 --SQL Where: MSI.inventory_item_id (+) = POL.item_id
1757 --SQL NVL(MSI.organization_id, POLL.ship_to_organization_id)
1758 --SQL = POLL.ship_to_organization_id
1759 --SQL To get record for one-time item
1760 --SQL Why: Same as SQL What
1761 SELECT 'PO', -- source code
1762 POH.po_header_id,
1763 POH.vendor_id,
1764 POH.vendor_site_id,
1765 POH.user_hold_flag,
1766 POH.freight_terms_lookup_code,
1767 POH.fob_lookup_code,
1768 POH.segment1,
1769 1, -- stands for 'PO'
1770 PDT.type_name,
1771 POH.org_id,
1772 POH.currency_code,
1773 POH.shipping_control,
1774 POH.revision_num,
1775 POL.po_line_id,
1776 POL.item_id,
1777 POL.item_description,
1778 POL.hazard_class_id,
1779 POL.item_revision,
1780 POL.vendor_product_num,
1781 POL.line_num,
1782 DECODE(PLT.outside_operation_flag, 'Y', 'GB_OSP', 'GB'),
1783 POLL.line_location_id,
1784 POLL.country_of_origin_code,
1785 POLL.ship_to_location_id,
1786 POLL.qty_rcv_tolerance,
1787 POLL.receive_close_tolerance,
1788 POLL.quantity_shipped,
1789 POLL.need_by_date,
1790 POLL.promised_date,
1791 POLL.ship_to_organization_id,
1792 POLL.quantity,
1793 MUOM.uom_code,
1794 POLL.quantity_cancelled,
1795 POLL.price_override,
1796 POLL.preferred_grade,
1797 POLL.secondary_quantity,
1798 MUOM1.uom_code,
1799 POLL.secondary_quantity,
1800 POLL.secondary_quantity_cancelled,
1801 MUOM1.uom_code,
1802 POLL.shipment_num,
1803 POLL.days_early_receipt_allowed,
1804 POLL.days_late_receipt_allowed,
1805 POLL.drop_ship_flag,
1806 POLL.qty_rcv_exception_code,
1807 POLL.closed_flag,
1808 POLL.closed_code,
1809 POLL.cancel_flag,
1810 POLL.receipt_days_exception_code,
1811 POLL.enforce_ship_to_location_code,
1812 POLL.last_update_date,
1813 FRT.party_id,
1814 MSI.unit_weight,
1815 MSI.weight_uom_code,
1816 MSI.unit_volume,
1817 MSI.volume_uom_code
1818 BULK COLLECT INTO
1819 x_fte_rec.source_code, -- Header
1820 x_fte_rec.header_id,
1821 x_fte_rec.vendor_id,
1822 x_fte_rec.ship_from_site_id,
1823 x_fte_rec.hold_code,
1824 x_fte_rec.freight_terms_code,
1825 x_fte_rec.fob_point_code,
1826 x_fte_rec.source_header_number,
1827 x_fte_rec.source_header_type_id,
1828 x_fte_rec.source_header_type_name,
1829 x_fte_rec.org_id,
1830 x_fte_rec.currency_code,
1831 x_fte_rec.shipping_control,
1832 x_fte_rec.po_revision,
1833 x_fte_rec.line_id, -- Line
1834 x_fte_rec.inventory_item_id,
1835 x_fte_rec.item_description,
1836 x_fte_rec.hazard_class_id,
1837 x_fte_rec.revision,
1838 x_fte_rec.supplier_item_num,
1839 x_fte_rec.source_line_number,
1840 x_fte_rec.source_line_type_code,
1841 x_fte_rec.po_shipment_line_id, -- Shipment
1842 x_fte_rec.country_of_origin,
1843 x_fte_rec.ship_to_location_id,
1844 x_fte_rec.ship_tolerance_above,
1845 x_fte_rec.ship_tolerance_below,
1846 x_fte_rec.shipped_quantity,
1847 x_fte_rec.request_date,
1848 x_fte_rec.schedule_ship_date,
1849 x_fte_rec.organization_id,
1850 x_fte_rec.ordered_quantity,
1851 x_fte_rec.order_quantity_uom,
1852 x_fte_rec.cancelled_quantity,
1853 x_fte_rec.unit_list_price,
1854 x_fte_rec.preferred_grade,
1855 x_fte_rec.ordered_quantity2,
1856 x_fte_rec.ordered_quantity_uom2,
1857 x_fte_rec.requested_quantity2,
1858 x_fte_rec.cancelled_quantity2,
1859 x_fte_rec.requested_quantity_uom2,
1860 x_fte_rec.po_shipment_line_number,
1861 x_fte_rec.days_early_receipt_allowed,
1862 x_fte_rec.days_late_receipt_allowed,
1863 x_fte_rec.drop_ship_flag,
1864 x_fte_rec.qty_rcv_exception_code,
1865 x_fte_rec.closed_flag,
1866 x_fte_rec.closed_code,
1867 x_fte_rec.cancelled_flag,
1868 x_fte_rec.receipt_days_exception_code,
1869 x_fte_rec.enforce_ship_to_location_code,
1870 x_fte_rec.shipping_details_updated_on,
1871 x_fte_rec.carrier_id, -- Others
1872 x_fte_rec.net_weight,
1873 x_fte_rec.weight_uom_code,
1874 x_fte_rec.volume,
1875 x_fte_rec.volume_uom_code
1876 FROM PO_HEADERS POH,
1877 PO_LINES POL,
1878 PO_LINE_LOCATIONS POLL,
1879 PO_LINE_TYPES_B PLT,
1880 ORG_FREIGHT_TL FRT,
1881 MTL_SYSTEM_ITEMS_B MSI,
1882 PO_DOCUMENT_TYPES_VL PDT,
1883 MTL_UNITS_OF_MEASURE MUOM,
1884 MTL_UNITS_OF_MEASURE MUOM1
1885 WHERE POH.po_header_id = p_header_id
1886 AND PDT.document_type_code = 'PO'
1887 AND PDT.document_subtype = POH.type_lookup_code
1888 AND POL.po_header_id = POH.po_header_id
1889 AND POLL.po_line_id = POL.po_line_id
1890 AND POL.po_line_id = NVL(p_line_id, POL.po_line_id)
1891 AND POLL.line_location_id
1892 = NVL(p_line_location_id, POLL.line_location_id)
1893 AND POL.line_type_id = PLT.line_type_id
1894 AND PLT.order_type_lookup_code = 'QUANTITY'
1895 AND FRT.freight_code (+) = POH.ship_via_lookup_code
1896 AND FRT.language (+) = USERENV('LANG')
1897 AND NVL(FRT.organization_id, POLL.ship_to_organization_id)
1898 = POLL.ship_to_organization_id
1899 AND MSI.inventory_item_id (+) = POL.item_id
1900 AND NVL(MSI.organization_id, POLL.ship_to_organization_id)
1901 = POLL.ship_to_organization_id
1902 AND MUOM.unit_of_measure(+) = POL.unit_meas_lookup_code
1903 AND MUOM1.unit_of_measure(+) = POLL.secondary_unit_of_measure
1904 AND NVL(POLL.closed_code, 'OPEN') = 'OPEN'
1905 AND NVL(POLL.cancel_flag, 'N') <> 'Y'; -- Bug 3581992
1906
1907 EXCEPTION
1908 WHEN NO_DATA_FOUND THEN
1909 NULL;
1910 WHEN OTHERS THEN
1911 x_return_status := FND_API.G_RET_STS_ERROR;
1912 END;
1913
1914
1915
1916
1917 -------------------------------------------------------------------------------
1918 --Start of Comments
1919 --Name: get_closed_po
1920 --Pre-reqs:
1921 -- None.
1922 --Modifies:
1923 -- None.
1924 --Locks:
1925 -- None.
1926 --Function:
1927 -- Get data for delivery record from Closed Standard Purchase Order
1928 --Parameters:
1929 --IN:
1930 --p_api_version
1931 -- Specifies API version.
1932 --p_header_id
1933 -- Corresponding to po_header_id
1934 --p_line_id
1935 -- Corresponding to po_line_id
1936 --p_line_location_id
1937 -- Corresponding to po_line_location_id
1938 --IN OUT:
1939 --x_return_status
1940 -- Indicates API return status as 'S', 'E' or 'U'.
1941 --Testing:
1942 -- Need to integrate FTE to implement the testing.
1943 --End of Comments
1944 -------------------------------------------------------------------------------
1945
1946
1947 PROCEDURE get_closed_po
1948 (
1949 p_api_version IN NUMBER,
1950 x_return_status IN OUT NOCOPY VARCHAR2,
1951 p_header_id IN NUMBER,
1952 p_line_id IN NUMBER,
1953 p_line_location_id IN NUMBER,
1954 x_fte_rec IN OUT NOCOPY OE_WSH_BULK_GRP.Line_Rec_Type
1955 )
1956 IS
1957 l_api_name CONSTANT VARCHAR2(100) := 'get_closed_po';
1958 l_api_version CONSTANT NUMBER := 1.0;
1959
1960 BEGIN
1961 -- Standard call to check for call compatibility.
1962 IF NOT FND_API.Compatible_API_Call
1963 (
1964 p_current_version_number => l_api_version,
1965 p_caller_version_number => p_api_version,
1966 p_api_name => l_api_name,
1967 p_pkg_name => 'PO_DELREC_PVT'
1968 )
1969 THEN
1970 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1971 END IF;
1972
1973 -- The following is for query data for delivery record from:
1974 -- Closed Standard PO, Standard PO referencing GA or Contract
1975
1976 --SQL What: Querying data from Closed Standard PO of quantity
1977 --SQL based items.
1978 --SQL Where: MSI.inventory_item_id (+) = POL.item_id
1979 --SQL NVL(MSI.organization_id, POLL.ship_to_organization_id)
1980 --SQL = POLL.ship_to_organization_id
1981 --SQL To get record for one-time item
1982 --SQL Why: Same as SQL What
1983 SELECT 'PO', -- source code
1984 POH.po_header_id,
1985 POH.vendor_id,
1986 POH.vendor_site_id,
1987 POH.user_hold_flag,
1988 POH.freight_terms_lookup_code,
1989 POH.fob_lookup_code,
1990 POH.segment1,
1991 1, -- stands for 'PO'
1992 PDT.type_name,
1993 POH.org_id,
1994 POH.currency_code,
1995 POH.shipping_control,
1996 POH.revision_num,
1997 POL.po_line_id,
1998 POL.item_id,
1999 POL.item_description,
2000 POL.hazard_class_id,
2001 POL.item_revision,
2002 POL.vendor_product_num,
2003 POL.line_num,
2004 DECODE(PLT.outside_operation_flag, 'Y', 'GB_OSP', 'GB'),
2005 POLL.line_location_id,
2006 POLL.country_of_origin_code,
2007 POLL.ship_to_location_id,
2008 POLL.qty_rcv_tolerance,
2009 POLL.receive_close_tolerance,
2010 POLL.quantity_shipped,
2011 POLL.need_by_date,
2012 POLL.promised_date,
2013 POLL.ship_to_organization_id,
2014 POLL.quantity,
2015 MUOM.uom_code,
2016 POLL.quantity_cancelled,
2017 POLL.price_override,
2018 POLL.preferred_grade,
2019 POLL.secondary_quantity,
2020 MUOM1.uom_code,
2021 POLL.secondary_quantity,
2022 POLL.secondary_quantity_cancelled,
2023 MUOM1.uom_code,
2024 POLL.shipment_num,
2025 POLL.days_early_receipt_allowed,
2026 POLL.days_late_receipt_allowed,
2027 POLL.drop_ship_flag,
2028 POLL.qty_rcv_exception_code,
2029 POLL.closed_flag,
2030 POLL.closed_code,
2031 POLL.cancel_flag,
2032 POLL.receipt_days_exception_code,
2033 POLL.enforce_ship_to_location_code,
2034 POLL.last_update_date,
2035 FRT.party_id,
2036 MSI.unit_weight,
2037 MSI.weight_uom_code,
2038 MSI.unit_volume,
2039 MSI.volume_uom_code
2040 BULK COLLECT INTO
2041 x_fte_rec.source_code, -- Header
2042 x_fte_rec.header_id,
2043 x_fte_rec.vendor_id,
2044 x_fte_rec.ship_from_site_id,
2045 x_fte_rec.hold_code,
2046 x_fte_rec.freight_terms_code,
2047 x_fte_rec.fob_point_code,
2048 x_fte_rec.source_header_number,
2049 x_fte_rec.source_header_type_id,
2050 x_fte_rec.source_header_type_name,
2051 x_fte_rec.org_id,
2052 x_fte_rec.currency_code,
2053 x_fte_rec.shipping_control,
2054 x_fte_rec.po_revision,
2055 x_fte_rec.line_id, -- Line
2056 x_fte_rec.inventory_item_id,
2057 x_fte_rec.item_description,
2058 x_fte_rec.hazard_class_id,
2059 x_fte_rec.revision,
2060 x_fte_rec.supplier_item_num,
2061 x_fte_rec.source_line_number,
2062 x_fte_rec.source_line_type_code,
2063 x_fte_rec.po_shipment_line_id, -- Shipment
2064 x_fte_rec.country_of_origin,
2065 x_fte_rec.ship_to_location_id,
2066 x_fte_rec.ship_tolerance_above,
2067 x_fte_rec.ship_tolerance_below,
2068 x_fte_rec.shipped_quantity,
2069 x_fte_rec.request_date,
2070 x_fte_rec.schedule_ship_date,
2071 x_fte_rec.organization_id,
2072 x_fte_rec.ordered_quantity,
2073 x_fte_rec.order_quantity_uom,
2074 x_fte_rec.cancelled_quantity,
2075 x_fte_rec.unit_list_price,
2076 x_fte_rec.preferred_grade,
2077 x_fte_rec.ordered_quantity2,
2078 x_fte_rec.ordered_quantity_uom2,
2079 x_fte_rec.requested_quantity2,
2080 x_fte_rec.cancelled_quantity2,
2081 x_fte_rec.requested_quantity_uom2,
2082 x_fte_rec.po_shipment_line_number,
2083 x_fte_rec.days_early_receipt_allowed,
2084 x_fte_rec.days_late_receipt_allowed,
2085 x_fte_rec.drop_ship_flag,
2086 x_fte_rec.qty_rcv_exception_code,
2087 x_fte_rec.closed_flag,
2088 x_fte_rec.closed_code,
2089 x_fte_rec.cancelled_flag,
2090 x_fte_rec.receipt_days_exception_code,
2091 x_fte_rec.enforce_ship_to_location_code,
2092 x_fte_rec.shipping_details_updated_on,
2093 x_fte_rec.carrier_id, -- Others
2094 x_fte_rec.net_weight,
2095 x_fte_rec.weight_uom_code,
2096 x_fte_rec.volume,
2097 x_fte_rec.volume_uom_code
2098 FROM PO_HEADERS POH,
2099 PO_LINES POL,
2100 PO_LINE_LOCATIONS POLL,
2101 PO_LINE_TYPES_B PLT,
2102 ORG_FREIGHT_TL FRT,
2103 MTL_SYSTEM_ITEMS_B MSI,
2104 PO_DOCUMENT_TYPES_VL PDT,
2105 MTL_UNITS_OF_MEASURE MUOM,
2106 MTL_UNITS_OF_MEASURE MUOM1
2107 WHERE POH.po_header_id = p_header_id
2108 AND PDT.document_type_code = 'PO'
2109 AND PDT.document_subtype = POH.type_lookup_code
2110 AND POL.po_header_id = POH.po_header_id
2111 AND POLL.po_line_id = POL.po_line_id
2112 AND POL.po_line_id = NVL(p_line_id, POL.po_line_id)
2113 AND POLL.line_location_id
2114 = NVL(p_line_location_id, POLL.line_location_id)
2115 AND POL.line_type_id = PLT.line_type_id
2116 AND PLT.order_type_lookup_code = 'QUANTITY'
2117 AND FRT.freight_code (+) = POH.ship_via_lookup_code
2118 AND FRT.language (+) = USERENV('LANG')
2119 AND NVL(FRT.organization_id, POLL.ship_to_organization_id)
2120 = POLL.ship_to_organization_id
2121 AND MSI.inventory_item_id (+) = POL.item_id
2122 AND NVL(MSI.organization_id, POLL.ship_to_organization_id)
2123 = POLL.ship_to_organization_id
2124 AND MUOM.unit_of_measure(+) = POL.unit_meas_lookup_code
2125 AND MUOM1.unit_of_measure(+) = POLL.secondary_unit_of_measure
2126 AND NVL(POLL.closed_code, 'OPEN') IN ('CLOSED', 'CLOSED FOR RECEIVING')
2127 AND NVL(POLL.cancel_flag, 'N') <> 'Y'; -- Bug 3581992
2128
2129 EXCEPTION
2130 WHEN NO_DATA_FOUND THEN
2131 NULL;
2132 WHEN OTHERS THEN
2133 x_return_status := FND_API.G_RET_STS_ERROR;
2134 END;
2135
2136
2137
2138
2139 -------------------------------------------------------------------------------
2140 --Start of Comments
2141 --Name: get_finally_closed_po
2142 --Pre-reqs:
2143 -- None.
2144 --Modifies:
2145 -- None.
2146 --Locks:
2147 -- None.
2148 --Function:
2149 -- Get data for delivery record from Finally Closed Standard Purchase Order
2150 --Parameters:
2151 --IN:
2152 --p_api_version
2153 -- Specifies API version.
2154 --p_header_id
2155 -- Corresponding to po_header_id
2156 --p_line_id
2157 -- Corresponding to po_line_id
2158 --p_line_location_id
2159 -- Corresponding to po_line_location_id
2160 --IN OUT:
2161 --x_return_status
2162 -- Indicates API return status as 'S', 'E' or 'U'.
2163 --Testing:
2164 -- Need to integrate FTE to implement the testing.
2165 --End of Comments
2166 -------------------------------------------------------------------------------
2167
2168
2169 PROCEDURE get_finally_closed_po
2170 (
2171 p_api_version IN NUMBER,
2172 x_return_status IN OUT NOCOPY VARCHAR2,
2173 p_header_id IN NUMBER,
2174 p_line_id IN NUMBER,
2175 p_line_location_id IN NUMBER,
2176 x_fte_rec IN OUT NOCOPY OE_WSH_BULK_GRP.Line_Rec_Type
2177 )
2178 IS
2179 l_api_name CONSTANT VARCHAR2(100) := 'get_finally_closed_po';
2180 l_api_version CONSTANT NUMBER := 1.0;
2181
2182 BEGIN
2183 -- Standard call to check for call compatibility.
2184 IF NOT FND_API.Compatible_API_Call
2185 (
2186 p_current_version_number => l_api_version,
2187 p_caller_version_number => p_api_version,
2188 p_api_name => l_api_name,
2189 p_pkg_name => 'PO_DELREC_PVT'
2190 )
2191 THEN
2192 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2193 END IF;
2194
2195 -- The following is for query data for delivery record from:
2196 -- Finally Closed Standard PO, Standard PO referencing GA or Contract
2197
2198 --SQL What: Querying data from Finally Closed Standard PO of
2199 --SQL quantity based items.
2200 --SQL Where: MSI.inventory_item_id (+) = POL.item_id
2201 --SQL NVL(MSI.organization_id, POLL.ship_to_organization_id)
2202 --SQL = POLL.ship_to_organization_id
2203 --SQL To get record for one-time item
2204 --SQL Why: Same as SQL What
2205 SELECT 'PO', -- source code
2206 POH.po_header_id,
2207 POH.vendor_id,
2208 POH.vendor_site_id,
2209 POH.user_hold_flag,
2210 POH.freight_terms_lookup_code,
2211 POH.fob_lookup_code,
2212 POH.segment1,
2213 1, -- stands for 'PO'
2214 PDT.type_name,
2215 POH.org_id,
2216 POH.currency_code,
2217 POH.shipping_control,
2218 POH.revision_num,
2219 POL.po_line_id,
2220 POL.item_id,
2221 POL.item_description,
2222 POL.hazard_class_id,
2223 POL.item_revision,
2224 POL.vendor_product_num,
2225 POL.line_num,
2226 DECODE(PLT.outside_operation_flag, 'Y', 'GB_OSP', 'GB'),
2227 POLL.line_location_id,
2228 POLL.country_of_origin_code,
2229 POLL.ship_to_location_id,
2230 POLL.qty_rcv_tolerance,
2231 POLL.receive_close_tolerance,
2232 POLL.quantity_shipped,
2233 POLL.need_by_date,
2234 POLL.promised_date,
2235 POLL.ship_to_organization_id,
2236 POLL.quantity,
2237 MUOM.uom_code,
2238 POLL.quantity_cancelled,
2239 POLL.price_override,
2240 POLL.preferred_grade,
2241 POLL.secondary_quantity,
2242 MUOM1.uom_code,
2243 POLL.secondary_quantity,
2244 POLL.secondary_quantity_cancelled,
2245 MUOM1.uom_code,
2246 POLL.shipment_num,
2247 POLL.days_early_receipt_allowed,
2248 POLL.days_late_receipt_allowed,
2249 POLL.drop_ship_flag,
2250 POLL.qty_rcv_exception_code,
2251 POLL.closed_flag,
2252 POLL.closed_code,
2253 POLL.cancel_flag,
2254 POLL.receipt_days_exception_code,
2255 POLL.enforce_ship_to_location_code,
2256 POLL.last_update_date,
2257 FRT.party_id,
2258 MSI.unit_weight,
2259 MSI.weight_uom_code,
2260 MSI.unit_volume,
2261 MSI.volume_uom_code
2262 BULK COLLECT INTO
2263 x_fte_rec.source_code, -- Header
2264 x_fte_rec.header_id,
2265 x_fte_rec.vendor_id,
2266 x_fte_rec.ship_from_site_id,
2267 x_fte_rec.hold_code,
2268 x_fte_rec.freight_terms_code,
2269 x_fte_rec.fob_point_code,
2270 x_fte_rec.source_header_number,
2271 x_fte_rec.source_header_type_id,
2272 x_fte_rec.source_header_type_name,
2273 x_fte_rec.org_id,
2274 x_fte_rec.currency_code,
2275 x_fte_rec.shipping_control,
2276 x_fte_rec.po_revision,
2277 x_fte_rec.line_id, -- Line
2278 x_fte_rec.inventory_item_id,
2279 x_fte_rec.item_description,
2280 x_fte_rec.hazard_class_id,
2281 x_fte_rec.revision,
2282 x_fte_rec.supplier_item_num,
2283 x_fte_rec.source_line_number,
2284 x_fte_rec.source_line_type_code,
2285 x_fte_rec.po_shipment_line_id, -- Shipment
2286 x_fte_rec.country_of_origin,
2287 x_fte_rec.ship_to_location_id,
2288 x_fte_rec.ship_tolerance_above,
2289 x_fte_rec.ship_tolerance_below,
2290 x_fte_rec.shipped_quantity,
2291 x_fte_rec.request_date,
2292 x_fte_rec.schedule_ship_date,
2293 x_fte_rec.organization_id,
2294 x_fte_rec.ordered_quantity,
2295 x_fte_rec.order_quantity_uom,
2296 x_fte_rec.cancelled_quantity,
2297 x_fte_rec.unit_list_price,
2298 x_fte_rec.preferred_grade,
2299 x_fte_rec.ordered_quantity2,
2300 x_fte_rec.ordered_quantity_uom2,
2301 x_fte_rec.requested_quantity2,
2302 x_fte_rec.cancelled_quantity2,
2303 x_fte_rec.requested_quantity_uom2,
2304 x_fte_rec.po_shipment_line_number,
2305 x_fte_rec.days_early_receipt_allowed,
2306 x_fte_rec.days_late_receipt_allowed,
2307 x_fte_rec.drop_ship_flag,
2308 x_fte_rec.qty_rcv_exception_code,
2309 x_fte_rec.closed_flag,
2310 x_fte_rec.closed_code,
2311 x_fte_rec.cancelled_flag,
2312 x_fte_rec.receipt_days_exception_code,
2313 x_fte_rec.enforce_ship_to_location_code,
2314 x_fte_rec.shipping_details_updated_on,
2315 x_fte_rec.carrier_id, -- Others
2316 x_fte_rec.net_weight,
2317 x_fte_rec.weight_uom_code,
2318 x_fte_rec.volume,
2319 x_fte_rec.volume_uom_code
2320 FROM PO_HEADERS POH,
2321 PO_LINES POL,
2322 PO_LINE_LOCATIONS POLL,
2323 PO_LINE_TYPES_B PLT,
2324 ORG_FREIGHT_TL FRT,
2325 MTL_SYSTEM_ITEMS_B MSI,
2326 PO_DOCUMENT_TYPES_VL PDT,
2327 MTL_UNITS_OF_MEASURE MUOM,
2328 MTL_UNITS_OF_MEASURE MUOM1
2329 WHERE POH.po_header_id = p_header_id
2330 AND PDT.document_type_code = 'PO'
2331 AND PDT.document_subtype = POH.type_lookup_code
2332 AND POL.po_header_id = POH.po_header_id
2333 AND POLL.po_line_id = POL.po_line_id
2334 AND POL.po_line_id = NVL(p_line_id, POL.po_line_id)
2335 AND POLL.line_location_id
2336 = NVL(p_line_location_id, POLL.line_location_id)
2337 AND POL.line_type_id = PLT.line_type_id
2338 AND PLT.order_type_lookup_code = 'QUANTITY'
2339 AND FRT.freight_code (+) = POH.ship_via_lookup_code
2340 AND FRT.language (+) = USERENV('LANG')
2341 AND NVL(FRT.organization_id, POLL.ship_to_organization_id)
2342 = POLL.ship_to_organization_id
2343 AND MSI.inventory_item_id (+) = POL.item_id
2344 AND NVL(MSI.organization_id, POLL.ship_to_organization_id)
2345 = POLL.ship_to_organization_id
2346 AND MUOM.unit_of_measure(+) = POL.unit_meas_lookup_code
2347 AND MUOM1.unit_of_measure(+) = POLL.secondary_unit_of_measure
2348 AND NVL(POLL.closed_code, 'OPEN') = 'FINALLY CLOSED';
2349
2350 EXCEPTION
2351 WHEN NO_DATA_FOUND THEN
2352 NULL;
2353 WHEN OTHERS THEN
2354 x_return_status := FND_API.G_RET_STS_ERROR;
2355 END;
2356
2357
2358
2359
2360 -------------------------------------------------------------------------------
2361 --Start of Comments
2362 --Name: get_approved_release
2363 --Pre-reqs:
2364 -- None.
2365 --Modifies:
2366 -- None.
2367 --Locks:
2368 -- None.
2369 --Function:
2370 -- Get data for delivery record from Approved Blanket Release
2371 --Parameters:
2372 --IN:
2373 --p_api_version
2374 -- Specifies API version.
2375 --p_header_id
2376 -- Corresponding to po_release_id
2377 --p_line_location_id
2378 -- Corresponding to po_line_location_id
2379 --IN OUT:
2380 --x_return_status
2381 -- Indicates API return status as 'S', 'E' or 'U'.
2382 --Testing:
2383 -- Need to integrate FTE to implement the testing.
2384 --End of Comments
2385 -------------------------------------------------------------------------------
2386
2387
2388 PROCEDURE get_approved_release
2389 (
2390 p_api_version IN NUMBER,
2391 x_return_status IN OUT NOCOPY VARCHAR2,
2392 p_header_id IN NUMBER,
2393 p_line_location_id IN NUMBER,
2394 x_fte_rec IN OUT NOCOPY OE_WSH_BULK_GRP.Line_Rec_Type
2395 )
2396 IS
2397 l_api_name CONSTANT VARCHAR2(100) := 'get_approved_release';
2398 l_api_version CONSTANT NUMBER := 1.0;
2399
2400 BEGIN
2401 -- Standard call to check for call compatibility.
2402 IF NOT FND_API.Compatible_API_Call
2403 (
2404 p_current_version_number => l_api_version,
2405 p_caller_version_number => p_api_version,
2406 p_api_name => l_api_name,
2407 p_pkg_name => 'PO_DELREC_PVT'
2408 )
2409 THEN
2410 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2411 END IF;
2412
2413 -- The following is for query data for delivery record from:
2414 -- Approved Blanket Release
2415
2416 --SQL What: Querying data from Approved Blanket Releases of quantity
2417 --SQL based items.
2418 --SQL Where: MSI.inventory_item_id (+) = POL.item_id
2419 --SQL NVL(MSI.organization_id, POLL.ship_to_organization_id)
2420 --SQL = POLL.ship_to_organization_id
2421 --SQL To get record for one-time item
2422 --SQL Why: Same as SQL What
2423 SELECT POR.po_release_id,
2424 POR.release_num,
2425 POR.shipping_control,
2426 POR.revision_num,
2427 'PO', -- source code
2428 POH.po_header_id,
2429 POH.vendor_id,
2430 POH.vendor_site_id,
2431 POH.user_hold_flag,
2432 POH.freight_terms_lookup_code,
2433 POH.fob_lookup_code,
2434 POH.segment1,
2435 2, -- stands for 'RELEASE'
2436 PDT.type_name,
2437 POH.org_id,
2438 POH.currency_code,
2439 POH.revision_num,
2440 POL.po_line_id,
2441 POL.item_id,
2442 POL.item_description,
2443 POL.hazard_class_id,
2444 POL.item_revision,
2445 POL.vendor_product_num,
2446 POL.line_num,
2447 DECODE(PLT.outside_operation_flag, 'Y', 'GB_OSP', 'GB'),
2448 POLL.line_location_id,
2449 POLL.country_of_origin_code,
2450 POLL.ship_to_location_id,
2451 POLL.qty_rcv_tolerance,
2452 POLL.receive_close_tolerance,
2453 POLL.quantity_shipped,
2454 POLL.need_by_date,
2455 POLL.promised_date,
2456 POLL.ship_to_organization_id,
2457 POLL.quantity,
2458 MUOM.uom_code,
2459 POLL.quantity_cancelled,
2460 POLL.price_override,
2461 POLL.preferred_grade,
2462 POLL.secondary_quantity,
2463 MUOM1.uom_code,
2464 POLL.secondary_quantity,
2465 POLL.secondary_quantity_cancelled,
2466 MUOM1.uom_code,
2467 POLL.shipment_num,
2468 POLL.days_early_receipt_allowed,
2469 POLL.days_late_receipt_allowed,
2470 POLL.drop_ship_flag,
2471 POLL.qty_rcv_exception_code,
2472 POLL.closed_flag,
2473 POLL.closed_code,
2474 POLL.cancel_flag,
2475 POLL.receipt_days_exception_code,
2476 POLL.enforce_ship_to_location_code,
2477 POLL.last_update_date,
2478 FRT.party_id,
2479 MSI.unit_weight,
2480 MSI.weight_uom_code,
2481 MSI.unit_volume,
2482 MSI.volume_uom_code
2483 BULK COLLECT INTO
2484 x_fte_rec.source_blanket_reference_id, -- Release Header
2485 x_fte_rec.source_blanket_reference_num,
2486 x_fte_rec.shipping_control,
2487 x_fte_rec.release_revision,
2488 x_fte_rec.source_code,
2489 x_fte_rec.header_id, -- PO Header
2490 x_fte_rec.vendor_id,
2491 x_fte_rec.ship_from_site_id,
2492 x_fte_rec.hold_code,
2493 x_fte_rec.freight_terms_code,
2494 x_fte_rec.fob_point_code,
2495 x_fte_rec.source_header_number,
2496 x_fte_rec.source_header_type_id,
2497 x_fte_rec.source_header_type_name,
2498 x_fte_rec.org_id,
2499 x_fte_rec.currency_code,
2500 x_fte_rec.po_revision,
2501 x_fte_rec.line_id, -- Line
2502 x_fte_rec.inventory_item_id,
2503 x_fte_rec.item_description,
2504 x_fte_rec.hazard_class_id,
2505 x_fte_rec.revision,
2506 x_fte_rec.supplier_item_num,
2507 x_fte_rec.source_line_number,
2508 x_fte_rec.source_line_type_code,
2509 x_fte_rec.po_shipment_line_id, -- Shipment
2510 x_fte_rec.country_of_origin,
2511 x_fte_rec.ship_to_location_id,
2512 x_fte_rec.ship_tolerance_above,
2513 x_fte_rec.ship_tolerance_below,
2514 x_fte_rec.shipped_quantity,
2515 x_fte_rec.request_date,
2516 x_fte_rec.schedule_ship_date,
2517 x_fte_rec.organization_id,
2518 x_fte_rec.ordered_quantity,
2519 x_fte_rec.order_quantity_uom,
2520 x_fte_rec.cancelled_quantity,
2521 x_fte_rec.unit_list_price,
2522 x_fte_rec.preferred_grade,
2523 x_fte_rec.ordered_quantity2,
2524 x_fte_rec.ordered_quantity_uom2,
2525 x_fte_rec.requested_quantity2,
2526 x_fte_rec.cancelled_quantity2,
2527 x_fte_rec.requested_quantity_uom2,
2528 x_fte_rec.po_shipment_line_number,
2529 x_fte_rec.days_early_receipt_allowed,
2530 x_fte_rec.days_late_receipt_allowed,
2531 x_fte_rec.drop_ship_flag,
2532 x_fte_rec.qty_rcv_exception_code,
2533 x_fte_rec.closed_flag,
2534 x_fte_rec.closed_code,
2535 x_fte_rec.cancelled_flag,
2536 x_fte_rec.receipt_days_exception_code,
2537 x_fte_rec.enforce_ship_to_location_code,
2538 x_fte_rec.shipping_details_updated_on,
2539 x_fte_rec.carrier_id, -- Others
2540 x_fte_rec.net_weight,
2541 x_fte_rec.weight_uom_code,
2542 x_fte_rec.volume,
2543 x_fte_rec.volume_uom_code
2544 FROM PO_RELEASES POR,
2545 PO_HEADERS POH,
2546 PO_LINES POL,
2547 PO_LINE_LOCATIONS POLL,
2548 PO_LINE_TYPES_B PLT,
2549 ORG_FREIGHT_TL FRT,
2550 MTL_SYSTEM_ITEMS_B MSI,
2551 PO_DOCUMENT_TYPES_VL PDT,
2552 MTL_UNITS_OF_MEASURE MUOM,
2553 MTL_UNITS_OF_MEASURE MUOM1
2554 WHERE POR.po_release_id = p_header_id
2555 AND POH.po_header_id = POR.po_header_id
2556 AND POL.po_header_id = POH.po_header_id
2557 AND POLL.po_line_id = POL.po_line_id
2558 AND POLL.po_release_id = POR.po_release_id
2559 AND PDT.document_type_code = 'PA'
2560 AND PDT.document_subtype = POR.release_type
2561 AND POLL.line_location_id
2562 = NVL(p_line_location_id, POLL.line_location_id)
2563 AND POL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
2564 AND PLT.ORDER_TYPE_LOOKUP_CODE = 'QUANTITY'
2565 AND FRT.freight_code (+) = POH.ship_via_lookup_code
2566 AND FRT.language (+) = USERENV('LANG')
2567 AND NVL(FRT.organization_id, POLL.ship_to_organization_id)
2568 = POLL.ship_to_organization_id
2569 AND MSI.inventory_item_id (+) = POL.item_id
2570 AND NVL(MSI.organization_id, POLL.ship_to_organization_id)
2571 = POLL.ship_to_organization_id
2572 AND MUOM.unit_of_measure(+) = POL.unit_meas_lookup_code
2573 AND MUOM1.unit_of_measure(+) = POLL.secondary_unit_of_measure
2574 AND NVL(POLL.approved_flag, 'N') = 'Y'
2575 AND NVL(POLL.cancel_flag, 'N') <> 'Y'; -- Bug 3581992
2576
2577 EXCEPTION
2578 WHEN NO_DATA_FOUND THEN
2579 NULL;
2580 WHEN OTHERS THEN
2581 x_return_status := FND_API.G_RET_STS_ERROR;
2582 END;
2583
2584
2585
2586
2587
2588 -------------------------------------------------------------------------------
2589 --Start of Comments
2590 --Name: get_cancelled_release
2591 --Pre-reqs:
2592 -- None.
2593 --Modifies:
2594 -- None.
2595 --Locks:
2596 -- None.
2597 --Function:
2598 -- Get data for delivery record from Cacelled Blanket Release
2599 --Parameters:
2600 --IN:
2601 --p_api_version
2602 -- Specifies API version.
2603 --p_header_id
2604 -- Corresponding to po_release_id
2605 --p_line_location_id
2606 -- Corresponding to po_line_location_id
2607 --IN OUT:
2608 --x_return_status
2609 -- Indicates API return status as 'S', 'E' or 'U'.
2610 --Testing:
2611 -- Need to integrate FTE to implement the testing.
2612 --End of Comments
2613 -------------------------------------------------------------------------------
2614
2615
2616 PROCEDURE get_cancelled_release
2617 (
2618 p_api_version IN NUMBER,
2619 x_return_status IN OUT NOCOPY VARCHAR2,
2620 p_header_id IN NUMBER,
2621 p_line_location_id IN NUMBER,
2622 x_fte_rec IN OUT NOCOPY OE_WSH_BULK_GRP.Line_Rec_Type
2623 )
2624 IS
2625 l_api_name CONSTANT VARCHAR2(100) := 'get_cancelled_release';
2626 l_api_version CONSTANT NUMBER := 1.0;
2627
2628 BEGIN
2629 -- Standard call to check for call compatibility.
2630 IF NOT FND_API.Compatible_API_Call
2631 (
2632 p_current_version_number => l_api_version,
2633 p_caller_version_number => p_api_version,
2634 p_api_name => l_api_name,
2635 p_pkg_name => 'PO_DELREC_PVT'
2636 )
2637 THEN
2638 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2639 END IF;
2640
2641 -- The following is for query data for delivery record from:
2642 -- Cancelled Blanket Release
2643
2644 --SQL What: Querying data from Cancelled Blanket Releases of quantity
2645 --SQL based items.
2646 --SQL Where: MSI.inventory_item_id (+) = POL.item_id
2647 --SQL NVL(MSI.organization_id, POLL.ship_to_organization_id)
2648 --SQL = POLL.ship_to_organization_id
2649 --SQL To get record for one-time item
2650 --SQL Why: Same as SQL What
2651 SELECT POR.po_release_id,
2652 POR.release_num,
2653 POR.shipping_control,
2654 POR.revision_num,
2655 'PO', -- source code
2656 POH.po_header_id,
2657 POH.vendor_id,
2658 POH.vendor_site_id,
2659 POH.user_hold_flag,
2660 POH.freight_terms_lookup_code,
2661 POH.fob_lookup_code,
2662 POH.segment1,
2663 2, -- stands for 'RELEASE'
2664 PDT.type_name,
2665 POH.org_id,
2666 POH.currency_code,
2667 POH.revision_num,
2668 POL.po_line_id,
2669 POL.item_id,
2670 POL.item_description,
2671 POL.hazard_class_id,
2672 POL.item_revision,
2673 POL.vendor_product_num,
2674 POL.line_num,
2675 DECODE(PLT.outside_operation_flag, 'Y', 'GB_OSP', 'GB'),
2676 POLL.line_location_id,
2677 POLL.country_of_origin_code,
2678 POLL.ship_to_location_id,
2679 POLL.qty_rcv_tolerance,
2680 POLL.receive_close_tolerance,
2681 POLL.quantity_shipped,
2682 POLL.need_by_date,
2683 POLL.promised_date,
2684 POLL.ship_to_organization_id,
2685 POLL.quantity,
2686 MUOM.uom_code,
2687 POLL.quantity_cancelled,
2688 POLL.price_override,
2689 POLL.preferred_grade,
2690 POLL.secondary_quantity,
2691 MUOM1.uom_code,
2692 POLL.secondary_quantity,
2693 POLL.secondary_quantity_cancelled,
2694 MUOM1.uom_code,
2695 POLL.shipment_num,
2696 POLL.days_early_receipt_allowed,
2697 POLL.days_late_receipt_allowed,
2698 POLL.drop_ship_flag,
2699 POLL.qty_rcv_exception_code,
2700 POLL.closed_flag,
2701 POLL.closed_code,
2702 POLL.cancel_flag,
2703 POLL.receipt_days_exception_code,
2704 POLL.enforce_ship_to_location_code,
2705 POLL.last_update_date,
2706 FRT.party_id,
2707 MSI.unit_weight,
2708 MSI.weight_uom_code,
2709 MSI.unit_volume,
2710 MSI.volume_uom_code
2711 BULK COLLECT INTO
2712 x_fte_rec.source_blanket_reference_id, -- Release Header
2713 x_fte_rec.source_blanket_reference_num,
2714 x_fte_rec.shipping_control,
2715 x_fte_rec.release_revision,
2716 x_fte_rec.source_code,
2717 x_fte_rec.header_id, -- PO Header
2718 x_fte_rec.vendor_id,
2719 x_fte_rec.ship_from_site_id,
2720 x_fte_rec.hold_code,
2721 x_fte_rec.freight_terms_code,
2722 x_fte_rec.fob_point_code,
2723 x_fte_rec.source_header_number,
2724 x_fte_rec.source_header_type_id,
2725 x_fte_rec.source_header_type_name,
2726 x_fte_rec.org_id,
2727 x_fte_rec.currency_code,
2728 x_fte_rec.po_revision,
2729 x_fte_rec.line_id, -- Line
2730 x_fte_rec.inventory_item_id,
2731 x_fte_rec.item_description,
2732 x_fte_rec.hazard_class_id,
2733 x_fte_rec.revision,
2734 x_fte_rec.supplier_item_num,
2735 x_fte_rec.source_line_number,
2736 x_fte_rec.source_line_type_code,
2737 x_fte_rec.po_shipment_line_id, -- Shipment
2738 x_fte_rec.country_of_origin,
2739 x_fte_rec.ship_to_location_id,
2740 x_fte_rec.ship_tolerance_above,
2741 x_fte_rec.ship_tolerance_below,
2742 x_fte_rec.shipped_quantity,
2743 x_fte_rec.request_date,
2744 x_fte_rec.schedule_ship_date,
2745 x_fte_rec.organization_id,
2746 x_fte_rec.ordered_quantity,
2747 x_fte_rec.order_quantity_uom,
2748 x_fte_rec.cancelled_quantity,
2749 x_fte_rec.unit_list_price,
2750 x_fte_rec.preferred_grade,
2751 x_fte_rec.ordered_quantity2,
2752 x_fte_rec.ordered_quantity_uom2,
2753 x_fte_rec.requested_quantity2,
2754 x_fte_rec.cancelled_quantity2,
2755 x_fte_rec.requested_quantity_uom2,
2756 x_fte_rec.po_shipment_line_number,
2757 x_fte_rec.days_early_receipt_allowed,
2758 x_fte_rec.days_late_receipt_allowed,
2759 x_fte_rec.drop_ship_flag,
2760 x_fte_rec.qty_rcv_exception_code,
2761 x_fte_rec.closed_flag,
2762 x_fte_rec.closed_code,
2763 x_fte_rec.cancelled_flag,
2764 x_fte_rec.receipt_days_exception_code,
2765 x_fte_rec.enforce_ship_to_location_code,
2766 x_fte_rec.shipping_details_updated_on,
2767 x_fte_rec.carrier_id, -- Others
2768 x_fte_rec.net_weight,
2769 x_fte_rec.weight_uom_code,
2770 x_fte_rec.volume,
2771 x_fte_rec.volume_uom_code
2772 FROM PO_RELEASES POR,
2773 PO_HEADERS POH,
2774 PO_LINES POL,
2775 PO_LINE_LOCATIONS POLL,
2776 PO_LINE_TYPES_B PLT,
2777 ORG_FREIGHT_TL FRT,
2778 MTL_SYSTEM_ITEMS_B MSI,
2779 PO_DOCUMENT_TYPES_VL PDT,
2780 MTL_UNITS_OF_MEASURE MUOM,
2781 MTL_UNITS_OF_MEASURE MUOM1
2782 WHERE POR.po_release_id = p_header_id
2783 AND POH.po_header_id = POR.po_header_id
2784 AND POL.po_header_id = POH.po_header_id
2785 AND POLL.po_line_id = POL.po_line_id
2786 AND POLL.po_release_id = POR.po_release_id
2787 AND PDT.document_type_code = 'PA'
2788 AND PDT.document_subtype = POR.release_type
2789 AND POLL.line_location_id
2790 = NVL(p_line_location_id, POLL.line_location_id)
2791 AND POL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
2792 AND PLT.ORDER_TYPE_LOOKUP_CODE = 'QUANTITY'
2793 AND FRT.freight_code (+) = POH.ship_via_lookup_code
2794 AND FRT.language (+) = USERENV('LANG')
2795 AND NVL(FRT.organization_id, POLL.ship_to_organization_id)
2796 = POLL.ship_to_organization_id
2797 AND MSI.inventory_item_id (+) = POL.item_id
2798 AND NVL(MSI.organization_id, POLL.ship_to_organization_id)
2799 = POLL.ship_to_organization_id
2800 AND MUOM.unit_of_measure(+) = POL.unit_meas_lookup_code
2801 AND MUOM1.unit_of_measure(+) = POLL.secondary_unit_of_measure
2802 AND NVL(POLL.cancel_flag, 'N') = 'Y';
2803
2804 EXCEPTION
2805 WHEN NO_DATA_FOUND THEN
2806 NULL;
2807 WHEN OTHERS THEN
2808 x_return_status := FND_API.G_RET_STS_ERROR;
2809 END;
2810
2811
2812
2813
2814 -------------------------------------------------------------------------------
2815 --Start of Comments
2816 --Name: get_opened_release
2817 --Pre-reqs:
2818 -- None.
2819 --Modifies:
2820 -- None.
2821 --Locks:
2822 -- None.
2823 --Function:
2824 -- Get data for delivery record from Opened Blanket Release
2825 --Parameters:
2826 --IN:
2827 --p_api_version
2828 -- Specifies API version.
2829 --p_header_id
2830 -- Corresponding to po_release_id
2831 --p_line_location_id
2832 -- Corresponding to po_line_location_id
2833 --IN OUT:
2834 --x_return_status
2835 -- Indicates API return status as 'S', 'E' or 'U'.
2836 --Testing:
2837 -- Need to integrate FTE to implement the testing.
2838 --End of Comments
2839 -------------------------------------------------------------------------------
2840
2841
2842 PROCEDURE get_opened_release
2843 (
2844 p_api_version IN NUMBER,
2845 x_return_status IN OUT NOCOPY VARCHAR2,
2846 p_header_id IN NUMBER,
2847 p_line_location_id IN NUMBER,
2848 x_fte_rec IN OUT NOCOPY OE_WSH_BULK_GRP.Line_Rec_Type
2849 )
2850 IS
2851 l_api_name CONSTANT VARCHAR2(100) := 'get_opened_release';
2852 l_api_version CONSTANT NUMBER := 1.0;
2853
2854 BEGIN
2855 -- Standard call to check for call compatibility.
2856 IF NOT FND_API.Compatible_API_Call
2857 (
2858 p_current_version_number => l_api_version,
2859 p_caller_version_number => p_api_version,
2860 p_api_name => l_api_name,
2861 p_pkg_name => 'PO_DELREC_PVT'
2862 )
2863 THEN
2864 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2865 END IF;
2866
2867 -- The following is for query data for delivery record from:
2868 -- Opened Blanket Release
2869
2870 --SQL What: Querying data from Opened Blanket Releases of quantity
2871 --SQL based items.
2872 --SQL Where: MSI.inventory_item_id (+) = POL.item_id
2873 --SQL NVL(MSI.organization_id, POLL.ship_to_organization_id)
2874 --SQL = POLL.ship_to_organization_id
2875 --SQL To get record for one-time item
2876 --SQL Why: Same as SQL What
2877 SELECT POR.po_release_id,
2878 POR.release_num,
2879 POR.shipping_control,
2880 POR.revision_num,
2881 'PO', -- source code
2882 POH.po_header_id,
2883 POH.vendor_id,
2884 POH.vendor_site_id,
2885 POH.user_hold_flag,
2886 POH.freight_terms_lookup_code,
2887 POH.fob_lookup_code,
2888 POH.segment1,
2889 2, -- stands for 'RELEASE'
2890 PDT.type_name,
2891 POH.org_id,
2892 POH.currency_code,
2893 POH.revision_num,
2894 POL.po_line_id,
2895 POL.item_id,
2896 POL.item_description,
2897 POL.hazard_class_id,
2898 POL.item_revision,
2899 POL.vendor_product_num,
2900 POL.line_num,
2901 DECODE(PLT.outside_operation_flag, 'Y', 'GB_OSP', 'GB'),
2902 POLL.line_location_id,
2903 POLL.country_of_origin_code,
2904 POLL.ship_to_location_id,
2905 POLL.qty_rcv_tolerance,
2906 POLL.receive_close_tolerance,
2907 POLL.quantity_shipped,
2908 POLL.need_by_date,
2909 POLL.promised_date,
2910 POLL.ship_to_organization_id,
2911 POLL.quantity,
2912 MUOM.uom_code,
2913 POLL.quantity_cancelled,
2914 POLL.price_override,
2915 POLL.preferred_grade,
2916 POLL.secondary_quantity,
2917 MUOM1.uom_code,
2918 POLL.secondary_quantity,
2919 POLL.secondary_quantity_cancelled,
2920 MUOM1.uom_code,
2921 POLL.shipment_num,
2922 POLL.days_early_receipt_allowed,
2923 POLL.days_late_receipt_allowed,
2924 POLL.drop_ship_flag,
2925 POLL.qty_rcv_exception_code,
2926 POLL.closed_flag,
2927 POLL.closed_code,
2928 POLL.cancel_flag,
2929 POLL.receipt_days_exception_code,
2930 POLL.enforce_ship_to_location_code,
2931 POLL.last_update_date,
2932 FRT.party_id,
2933 MSI.unit_weight,
2934 MSI.weight_uom_code,
2935 MSI.unit_volume,
2936 MSI.volume_uom_code
2937 BULK COLLECT INTO
2938 x_fte_rec.source_blanket_reference_id, -- Release Header
2939 x_fte_rec.source_blanket_reference_num,
2940 x_fte_rec.shipping_control,
2941 x_fte_rec.release_revision,
2942 x_fte_rec.source_code,
2943 x_fte_rec.header_id, -- PO Header
2944 x_fte_rec.vendor_id,
2945 x_fte_rec.ship_from_site_id,
2946 x_fte_rec.hold_code,
2947 x_fte_rec.freight_terms_code,
2948 x_fte_rec.fob_point_code,
2949 x_fte_rec.source_header_number,
2950 x_fte_rec.source_header_type_id,
2951 x_fte_rec.source_header_type_name,
2952 x_fte_rec.org_id,
2953 x_fte_rec.currency_code,
2954 x_fte_rec.po_revision,
2955 x_fte_rec.line_id, -- Line
2956 x_fte_rec.inventory_item_id,
2957 x_fte_rec.item_description,
2958 x_fte_rec.hazard_class_id,
2959 x_fte_rec.revision,
2960 x_fte_rec.supplier_item_num,
2961 x_fte_rec.source_line_number,
2962 x_fte_rec.source_line_type_code,
2963 x_fte_rec.po_shipment_line_id, -- Shipment
2964 x_fte_rec.country_of_origin,
2965 x_fte_rec.ship_to_location_id,
2966 x_fte_rec.ship_tolerance_above,
2967 x_fte_rec.ship_tolerance_below,
2968 x_fte_rec.shipped_quantity,
2969 x_fte_rec.request_date,
2970 x_fte_rec.schedule_ship_date,
2971 x_fte_rec.organization_id,
2972 x_fte_rec.ordered_quantity,
2973 x_fte_rec.order_quantity_uom,
2974 x_fte_rec.cancelled_quantity,
2975 x_fte_rec.unit_list_price,
2976 x_fte_rec.preferred_grade,
2977 x_fte_rec.ordered_quantity2,
2978 x_fte_rec.ordered_quantity_uom2,
2979 x_fte_rec.requested_quantity2,
2980 x_fte_rec.cancelled_quantity2,
2981 x_fte_rec.requested_quantity_uom2,
2982 x_fte_rec.po_shipment_line_number,
2983 x_fte_rec.days_early_receipt_allowed,
2984 x_fte_rec.days_late_receipt_allowed,
2985 x_fte_rec.drop_ship_flag,
2986 x_fte_rec.qty_rcv_exception_code,
2987 x_fte_rec.closed_flag,
2988 x_fte_rec.closed_code,
2989 x_fte_rec.cancelled_flag,
2990 x_fte_rec.receipt_days_exception_code,
2991 x_fte_rec.enforce_ship_to_location_code,
2992 x_fte_rec.shipping_details_updated_on,
2993 x_fte_rec.carrier_id, -- Others
2994 x_fte_rec.net_weight,
2995 x_fte_rec.weight_uom_code,
2996 x_fte_rec.volume,
2997 x_fte_rec.volume_uom_code
2998 FROM PO_RELEASES POR,
2999 PO_HEADERS POH,
3000 PO_LINES POL,
3001 PO_LINE_LOCATIONS POLL,
3002 PO_LINE_TYPES_B PLT,
3003 ORG_FREIGHT_TL FRT,
3004 MTL_SYSTEM_ITEMS_B MSI,
3005 PO_DOCUMENT_TYPES_VL PDT,
3006 MTL_UNITS_OF_MEASURE MUOM,
3007 MTL_UNITS_OF_MEASURE MUOM1
3008 WHERE POR.po_release_id = p_header_id
3009 AND POH.po_header_id = POR.po_header_id
3010 AND POL.po_header_id = POH.po_header_id
3011 AND POLL.po_line_id = POL.po_line_id
3012 AND POLL.po_release_id = POR.po_release_id
3013 AND PDT.document_type_code = 'PA'
3014 AND PDT.document_subtype = POR.release_type
3015 AND POLL.line_location_id
3016 = NVL(p_line_location_id, POLL.line_location_id)
3017 AND POL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
3018 AND PLT.ORDER_TYPE_LOOKUP_CODE = 'QUANTITY'
3019 AND FRT.freight_code (+) = POH.ship_via_lookup_code
3020 AND FRT.language (+) = USERENV('LANG')
3021 AND NVL(FRT.organization_id, POLL.ship_to_organization_id)
3022 = POLL.ship_to_organization_id
3023 AND MSI.inventory_item_id (+) = POL.item_id
3024 AND NVL(MSI.organization_id, POLL.ship_to_organization_id)
3025 = POLL.ship_to_organization_id
3026 AND MUOM.unit_of_measure(+) = POL.unit_meas_lookup_code
3027 AND MUOM1.unit_of_measure(+) = POLL.secondary_unit_of_measure
3028 AND NVL(POLL.closed_code, 'OPEN') = 'OPEN'
3029 AND NVL(POLL.cancel_flag, 'N') <> 'Y'; -- Bug 3581992
3030
3031 EXCEPTION
3032 WHEN NO_DATA_FOUND THEN
3033 NULL;
3034 WHEN OTHERS THEN
3035 x_return_status := FND_API.G_RET_STS_ERROR;
3036 END;
3037
3038
3039
3040
3041 -------------------------------------------------------------------------------
3042 --Start of Comments
3043 --Name: get_closed_release
3044 --Pre-reqs:
3045 -- None.
3046 --Modifies:
3047 -- None.
3048 --Locks:
3049 -- None.
3050 --Function:
3051 -- Get data for delivery record from Closed Blanket Release
3052 --Parameters:
3053 --IN:
3054 --p_api_version
3055 -- Specifies API version.
3056 --p_header_id
3057 -- Corresponding to po_release_id
3058 --p_line_location_id
3059 -- Corresponding to po_line_location_id
3060 --IN OUT:
3061 --x_return_status
3062 -- Indicates API return status as 'S', 'E' or 'U'.
3063 --Testing:
3064 -- Need to integrate FTE to implement the testing.
3065 --End of Comments
3066 -------------------------------------------------------------------------------
3067
3068
3069 PROCEDURE get_closed_release
3070 (
3071 p_api_version IN NUMBER,
3072 x_return_status IN OUT NOCOPY VARCHAR2,
3073 p_header_id IN NUMBER,
3074 p_line_location_id IN NUMBER,
3075 x_fte_rec IN OUT NOCOPY OE_WSH_BULK_GRP.Line_Rec_Type
3076 )
3077 IS
3078 l_api_name CONSTANT VARCHAR2(100) := 'get_closed_release';
3079 l_api_version CONSTANT NUMBER := 1.0;
3080
3081 BEGIN
3082 -- Standard call to check for call compatibility.
3083 IF NOT FND_API.Compatible_API_Call
3084 (
3085 p_current_version_number => l_api_version,
3086 p_caller_version_number => p_api_version,
3087 p_api_name => l_api_name,
3088 p_pkg_name => 'PO_DELREC_PVT'
3089 )
3090 THEN
3091 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3092 END IF;
3093
3094 -- The following is for query data for delivery record from:
3095 -- Closed Blanket Release
3096
3097 --SQL What: Querying data from Closed Blanket Releases of quantity
3098 --SQL based items.
3099 --SQL Where: MSI.inventory_item_id (+) = POL.item_id
3100 --SQL NVL(MSI.organization_id, POLL.ship_to_organization_id)
3101 --SQL = POLL.ship_to_organization_id
3102 --SQL To get record for one-time item
3103 --SQL Why: Same as SQL What
3104 SELECT POR.po_release_id,
3105 POR.release_num,
3106 POR.shipping_control,
3107 POR.revision_num,
3108 'PO', -- source code
3109 POH.po_header_id,
3110 POH.vendor_id,
3111 POH.vendor_site_id,
3112 POH.user_hold_flag,
3113 POH.freight_terms_lookup_code,
3114 POH.fob_lookup_code,
3115 POH.segment1,
3116 2, -- stands for 'RELEASE'
3117 PDT.type_name,
3118 POH.org_id,
3119 POH.currency_code,
3120 POH.revision_num,
3121 POL.po_line_id,
3122 POL.item_id,
3123 POL.item_description,
3124 POL.hazard_class_id,
3125 POL.item_revision,
3126 POL.vendor_product_num,
3127 POL.line_num,
3128 DECODE(PLT.outside_operation_flag, 'Y', 'GB_OSP', 'GB'),
3129 POLL.line_location_id,
3130 POLL.country_of_origin_code,
3131 POLL.ship_to_location_id,
3132 POLL.qty_rcv_tolerance,
3133 POLL.receive_close_tolerance,
3134 POLL.quantity_shipped,
3135 POLL.need_by_date,
3136 POLL.promised_date,
3137 POLL.ship_to_organization_id,
3138 POLL.quantity,
3139 MUOM.uom_code,
3140 POLL.quantity_cancelled,
3141 POLL.price_override,
3142 POLL.preferred_grade,
3143 POLL.secondary_quantity,
3144 MUOM1.uom_code,
3145 POLL.secondary_quantity,
3146 POLL.secondary_quantity_cancelled,
3147 MUOM1.uom_code,
3148 POLL.shipment_num,
3149 POLL.days_early_receipt_allowed,
3150 POLL.days_late_receipt_allowed,
3151 POLL.drop_ship_flag,
3152 POLL.qty_rcv_exception_code,
3153 POLL.closed_flag,
3154 POLL.closed_code,
3155 POLL.cancel_flag,
3156 POLL.receipt_days_exception_code,
3157 POLL.enforce_ship_to_location_code,
3158 POLL.last_update_date,
3159 FRT.party_id,
3160 MSI.unit_weight,
3161 MSI.weight_uom_code,
3162 MSI.unit_volume,
3163 MSI.volume_uom_code
3164 BULK COLLECT INTO
3165 x_fte_rec.source_blanket_reference_id, -- Release Header
3166 x_fte_rec.source_blanket_reference_num,
3167 x_fte_rec.shipping_control,
3168 x_fte_rec.release_revision,
3169 x_fte_rec.source_code,
3170 x_fte_rec.header_id, -- PO Header
3171 x_fte_rec.vendor_id,
3172 x_fte_rec.ship_from_site_id,
3173 x_fte_rec.hold_code,
3174 x_fte_rec.freight_terms_code,
3175 x_fte_rec.fob_point_code,
3176 x_fte_rec.source_header_number,
3177 x_fte_rec.source_header_type_id,
3178 x_fte_rec.source_header_type_name,
3179 x_fte_rec.org_id,
3180 x_fte_rec.currency_code,
3181 x_fte_rec.po_revision,
3182 x_fte_rec.line_id, -- Line
3183 x_fte_rec.inventory_item_id,
3184 x_fte_rec.item_description,
3185 x_fte_rec.hazard_class_id,
3186 x_fte_rec.revision,
3187 x_fte_rec.supplier_item_num,
3188 x_fte_rec.source_line_number,
3189 x_fte_rec.source_line_type_code,
3190 x_fte_rec.po_shipment_line_id, -- Shipment
3191 x_fte_rec.country_of_origin,
3192 x_fte_rec.ship_to_location_id,
3193 x_fte_rec.ship_tolerance_above,
3194 x_fte_rec.ship_tolerance_below,
3195 x_fte_rec.shipped_quantity,
3196 x_fte_rec.request_date,
3197 x_fte_rec.schedule_ship_date,
3198 x_fte_rec.organization_id,
3199 x_fte_rec.ordered_quantity,
3200 x_fte_rec.order_quantity_uom,
3201 x_fte_rec.cancelled_quantity,
3202 x_fte_rec.unit_list_price,
3203 x_fte_rec.preferred_grade,
3204 x_fte_rec.ordered_quantity2,
3205 x_fte_rec.ordered_quantity_uom2,
3206 x_fte_rec.requested_quantity2,
3207 x_fte_rec.cancelled_quantity2,
3208 x_fte_rec.requested_quantity_uom2,
3209 x_fte_rec.po_shipment_line_number,
3210 x_fte_rec.days_early_receipt_allowed,
3211 x_fte_rec.days_late_receipt_allowed,
3212 x_fte_rec.drop_ship_flag,
3213 x_fte_rec.qty_rcv_exception_code,
3214 x_fte_rec.closed_flag,
3215 x_fte_rec.closed_code,
3216 x_fte_rec.cancelled_flag,
3217 x_fte_rec.receipt_days_exception_code,
3218 x_fte_rec.enforce_ship_to_location_code,
3219 x_fte_rec.shipping_details_updated_on,
3220 x_fte_rec.carrier_id, -- Others
3221 x_fte_rec.net_weight,
3222 x_fte_rec.weight_uom_code,
3223 x_fte_rec.volume,
3224 x_fte_rec.volume_uom_code
3225 FROM PO_RELEASES POR,
3226 PO_HEADERS POH,
3227 PO_LINES POL,
3228 PO_LINE_LOCATIONS POLL,
3229 PO_LINE_TYPES_B PLT,
3230 ORG_FREIGHT_TL FRT,
3231 MTL_SYSTEM_ITEMS_B MSI,
3232 PO_DOCUMENT_TYPES_VL PDT,
3233 MTL_UNITS_OF_MEASURE MUOM,
3234 MTL_UNITS_OF_MEASURE MUOM1
3235 WHERE POR.po_release_id = p_header_id
3236 AND POH.po_header_id = POR.po_header_id
3237 AND POL.po_header_id = POH.po_header_id
3238 AND POLL.po_line_id = POL.po_line_id
3239 AND POLL.po_release_id = POR.po_release_id
3240 AND PDT.document_type_code = 'PA'
3241 AND PDT.document_subtype = POR.release_type
3242 AND POLL.line_location_id
3243 = NVL(p_line_location_id, POLL.line_location_id)
3244 AND POL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
3245 AND PLT.ORDER_TYPE_LOOKUP_CODE = 'QUANTITY'
3246 AND FRT.freight_code (+) = POH.ship_via_lookup_code
3247 AND FRT.language (+) = USERENV('LANG')
3248 AND NVL(FRT.organization_id, POLL.ship_to_organization_id)
3249 = POLL.ship_to_organization_id
3250 AND MSI.inventory_item_id (+) = POL.item_id
3251 AND NVL(MSI.organization_id, POLL.ship_to_organization_id)
3252 = POLL.ship_to_organization_id
3253 AND MUOM.unit_of_measure(+) = POL.unit_meas_lookup_code
3254 AND MUOM1.unit_of_measure(+) = POLL.secondary_unit_of_measure
3255 AND NVL(POLL.closed_code, 'OPEN') IN ('CLOSED', 'CLOSED FOR RECEIVING')
3256 AND NVL(POLL.cancel_flag, 'N') <> 'Y'; -- Bug 3581992
3257
3258 EXCEPTION
3259 WHEN NO_DATA_FOUND THEN
3260 NULL;
3261 WHEN OTHERS THEN
3262 x_return_status := FND_API.G_RET_STS_ERROR;
3263 END;
3264
3265
3266
3267
3268 -------------------------------------------------------------------------------
3269 --Start of Comments
3270 --Name: get_finally_closed_release
3271 --Pre-reqs:
3272 -- None.
3273 --Modifies:
3274 -- None.
3275 --Locks:
3276 -- None.
3277 --Function:
3278 -- Get data for delivery record from Finally Closed Blanket Release
3279 --Parameters:
3280 --IN:
3281 --p_api_version
3282 -- Specifies API version.
3283 --p_header_id
3284 -- Corresponding to po_release_id
3285 --p_line_location_id
3286 -- Corresponding to po_line_location_id
3287 --IN OUT:
3288 --x_return_status
3289 -- Indicates API return status as 'S', 'E' or 'U'.
3290 --Testing:
3291 -- Need to integrate FTE to implement the testing.
3292 --End of Comments
3293 -------------------------------------------------------------------------------
3294
3295
3296
3297
3298 PROCEDURE get_finally_closed_release
3299 (
3300 p_api_version IN NUMBER,
3301 x_return_status IN OUT NOCOPY VARCHAR2,
3302 p_header_id IN NUMBER,
3303 p_line_location_id IN NUMBER,
3304 x_fte_rec IN OUT NOCOPY OE_WSH_BULK_GRP.Line_Rec_Type
3305 )
3306 IS
3307 l_api_name CONSTANT VARCHAR2(100) := 'get_finally_closed_release';
3308 l_api_version CONSTANT NUMBER := 1.0;
3309
3310 BEGIN
3311 -- Standard call to check for call compatibility.
3312 IF NOT FND_API.Compatible_API_Call
3313 (
3314 p_current_version_number => l_api_version,
3315 p_caller_version_number => p_api_version,
3316 p_api_name => l_api_name,
3317 p_pkg_name => 'PO_DELREC_PVT'
3318 )
3319 THEN
3320 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3321 END IF;
3322
3323 -- The following is for query data for delivery record from:
3324 --Finally Closed Blanket Release
3325
3326 --SQL What: Querying data from Finally Closed Blanket Releases of quantity
3327 --SQL based items.
3328 --SQL Where: MSI.inventory_item_id (+) = POL.item_id
3329 --SQL NVL(MSI.organization_id, POLL.ship_to_organization_id)
3330 --SQL = POLL.ship_to_organization_id
3331 --SQL To get record for one-time item
3332 --SQL Why: Same as SQL What
3333 SELECT POR.po_release_id,
3334 POR.release_num,
3335 POR.shipping_control,
3336 POR.revision_num,
3337 'PO', -- source code
3338 POH.po_header_id,
3339 POH.vendor_id,
3340 POH.vendor_site_id,
3341 POH.user_hold_flag,
3342 POH.freight_terms_lookup_code,
3343 POH.fob_lookup_code,
3344 POH.segment1,
3345 2, -- stands for 'RELEASE'
3346 PDT.type_name,
3347 POH.org_id,
3348 POH.currency_code,
3349 POH.revision_num,
3350 POL.po_line_id,
3351 POL.item_id,
3352 POL.item_description,
3353 POL.hazard_class_id,
3354 POL.item_revision,
3355 POL.vendor_product_num,
3356 POL.line_num,
3357 DECODE(PLT.outside_operation_flag, 'Y', 'GB_OSP', 'GB'),
3358 POLL.line_location_id,
3359 POLL.country_of_origin_code,
3360 POLL.ship_to_location_id,
3361 POLL.qty_rcv_tolerance,
3362 POLL.receive_close_tolerance,
3363 POLL.quantity_shipped,
3364 POLL.need_by_date,
3365 POLL.promised_date,
3366 POLL.ship_to_organization_id,
3367 POLL.quantity,
3368 MUOM.uom_code,
3369 POLL.quantity_cancelled,
3370 POLL.price_override,
3371 POLL.preferred_grade,
3372 POLL.secondary_quantity,
3373 MUOM1.uom_code,
3374 POLL.secondary_quantity,
3375 POLL.secondary_quantity_cancelled,
3376 MUOM1.uom_code,
3377 POLL.shipment_num,
3378 POLL.days_early_receipt_allowed,
3379 POLL.days_late_receipt_allowed,
3380 POLL.drop_ship_flag,
3381 POLL.qty_rcv_exception_code,
3382 POLL.closed_flag,
3383 POLL.closed_code,
3384 POLL.cancel_flag,
3385 POLL.receipt_days_exception_code,
3386 POLL.enforce_ship_to_location_code,
3387 POLL.last_update_date,
3388 FRT.party_id,
3389 MSI.unit_weight,
3390 MSI.weight_uom_code,
3391 MSI.unit_volume,
3392 MSI.volume_uom_code
3393 BULK COLLECT INTO
3394 x_fte_rec.source_blanket_reference_id, -- Release Header
3395 x_fte_rec.source_blanket_reference_num,
3396 x_fte_rec.shipping_control,
3397 x_fte_rec.release_revision,
3398 x_fte_rec.source_code,
3399 x_fte_rec.header_id, -- PO Header
3400 x_fte_rec.vendor_id,
3401 x_fte_rec.ship_from_site_id,
3402 x_fte_rec.hold_code,
3403 x_fte_rec.freight_terms_code,
3404 x_fte_rec.fob_point_code,
3405 x_fte_rec.source_header_number,
3406 x_fte_rec.source_header_type_id,
3407 x_fte_rec.source_header_type_name,
3408 x_fte_rec.org_id,
3409 x_fte_rec.currency_code,
3410 x_fte_rec.po_revision,
3411 x_fte_rec.line_id, -- Line
3412 x_fte_rec.inventory_item_id,
3413 x_fte_rec.item_description,
3414 x_fte_rec.hazard_class_id,
3415 x_fte_rec.revision,
3416 x_fte_rec.supplier_item_num,
3417 x_fte_rec.source_line_number,
3418 x_fte_rec.source_line_type_code,
3419 x_fte_rec.po_shipment_line_id, -- Shipment
3420 x_fte_rec.country_of_origin,
3421 x_fte_rec.ship_to_location_id,
3422 x_fte_rec.ship_tolerance_above,
3423 x_fte_rec.ship_tolerance_below,
3424 x_fte_rec.shipped_quantity,
3425 x_fte_rec.request_date,
3426 x_fte_rec.schedule_ship_date,
3427 x_fte_rec.organization_id,
3428 x_fte_rec.ordered_quantity,
3429 x_fte_rec.order_quantity_uom,
3430 x_fte_rec.cancelled_quantity,
3431 x_fte_rec.unit_list_price,
3432 x_fte_rec.preferred_grade,
3433 x_fte_rec.ordered_quantity2,
3434 x_fte_rec.ordered_quantity_uom2,
3435 x_fte_rec.requested_quantity2,
3436 x_fte_rec.cancelled_quantity2,
3437 x_fte_rec.requested_quantity_uom2,
3438 x_fte_rec.po_shipment_line_number,
3439 x_fte_rec.days_early_receipt_allowed,
3440 x_fte_rec.days_late_receipt_allowed,
3441 x_fte_rec.drop_ship_flag,
3442 x_fte_rec.qty_rcv_exception_code,
3443 x_fte_rec.closed_flag,
3444 x_fte_rec.closed_code,
3445 x_fte_rec.cancelled_flag,
3446 x_fte_rec.receipt_days_exception_code,
3447 x_fte_rec.enforce_ship_to_location_code,
3448 x_fte_rec.shipping_details_updated_on,
3449 x_fte_rec.carrier_id, -- Others
3450 x_fte_rec.net_weight,
3451 x_fte_rec.weight_uom_code,
3452 x_fte_rec.volume,
3453 x_fte_rec.volume_uom_code
3454 FROM PO_RELEASES POR,
3455 PO_HEADERS POH,
3456 PO_LINES POL,
3457 PO_LINE_LOCATIONS POLL,
3458 PO_LINE_TYPES_B PLT,
3459 ORG_FREIGHT_TL FRT,
3460 MTL_SYSTEM_ITEMS_B MSI,
3461 PO_DOCUMENT_TYPES_VL PDT,
3462 MTL_UNITS_OF_MEASURE MUOM,
3463 MTL_UNITS_OF_MEASURE MUOM1
3464 WHERE POR.po_release_id = p_header_id
3465 AND POH.po_header_id = POR.po_header_id
3466 AND POL.po_header_id = POH.po_header_id
3467 AND POLL.po_line_id = POL.po_line_id
3468 AND POLL.po_release_id = POR.po_release_id
3469 AND PDT.document_type_code = 'PA'
3470 AND PDT.document_subtype = POR.release_type
3471 AND POLL.line_location_id
3472 = NVL(p_line_location_id, POLL.line_location_id)
3473 AND POL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
3474 AND PLT.ORDER_TYPE_LOOKUP_CODE = 'QUANTITY'
3475 AND FRT.freight_code (+) = POH.ship_via_lookup_code
3476 AND FRT.language (+) = USERENV('LANG')
3477 AND NVL(FRT.organization_id, POLL.ship_to_organization_id)
3478 = POLL.ship_to_organization_id
3479 AND MSI.inventory_item_id (+) = POL.item_id
3480 AND NVL(MSI.organization_id, POLL.ship_to_organization_id)
3481 = POLL.ship_to_organization_id
3482 AND MUOM.unit_of_measure(+) = POL.unit_meas_lookup_code
3483 AND MUOM1.unit_of_measure(+) = POLL.secondary_unit_of_measure
3484 AND NVL(POLL.closed_code, 'OPEN') = 'FINALLY CLOSED';
3485
3486 EXCEPTION
3487 WHEN NO_DATA_FOUND THEN
3488 NULL;
3489 WHEN OTHERS THEN
3490 x_return_status := FND_API.G_RET_STS_ERROR;
3491 END;
3492
3493 -- Bug 3581992 START
3494 -------------------------------------------------------------------------------
3495 --Start of Comments
3496 --Name: debug_fte_rec
3497 --Pre-reqs:
3498 -- None.
3499 --Modifies:
3500 -- FND log
3501 --Locks:
3502 -- None.
3503 --Function:
3504 -- Prints out some attributes of the FTE record to the FND log for debugging
3505 -- purposes.
3506 --Parameters:
3507 --IN:
3508 --p_fte_rec
3509 -- record that we will pass to the FTE API
3510 --End of Comments
3511 -------------------------------------------------------------------------------
3512 PROCEDURE debug_fte_rec
3513 (
3514 p_fte_rec IN OE_WSH_BULK_GRP.Line_Rec_Type
3515 ) IS
3516 l_api_name CONSTANT VARCHAR2(30) := 'debug_fte_rec';
3517 BEGIN
3518 IF (g_debug_stmt) AND (p_fte_rec.po_shipment_line_id IS NOT NULL) THEN
3519 PO_DEBUG.debug_begin ( c_log_head||l_api_name );
3520
3521 FOR i IN 1..p_fte_rec.po_shipment_line_id.COUNT LOOP
3522 PO_DEBUG.debug_var (
3523 p_log_head => c_log_head||l_api_name,
3524 p_progress => '000',
3525 p_name => 'p_fte_rec.po_shipment_line_id('||i||')',
3526 p_value => p_fte_rec.po_shipment_line_id(i)
3527 );
3528 END LOOP;
3529 END IF;
3530 EXCEPTION
3531 WHEN OTHERS THEN
3532 null; -- ignore errors
3533 END;
3534 -- Bug 3581992 END
3535
3536 END PO_DELREC_PVT;