1 PACKAGE BODY PO_OM_INTEGRATION_GRP AS
2 /* $Header: POXGOMIB.pls 120.5 2008/01/04 12:34:15 ppadilam ship $*/
3
4 --CONSTANTS
5
6 G_PKG_NAME CONSTANT varchar2(30) := 'PO_OM_INTEGRATION_GRP';
7
8 c_log_head CONSTANT VARCHAR2(50) := 'po.plsql.'|| G_PKG_NAME || '.';
9
10 -- Read the profile option that enables/disables the debug log
11 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
12
13 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
14 g_debug_unexp CONSTANT BOOLEAN := PO_DEBUG.is_debug_unexp_on;
15
16 -- START Forward declarations for package private procedures:
17 PROCEDURE call_po_change_api (
18 p_chg IN OUT NOCOPY PO_CHANGES_REC_TYPE,
19 x_return_status OUT NOCOPY VARCHAR2
20 );
21 -- END Forward declarations for package private procedures
22
23 -------------------------------------------------------------------------------
24 --Start of Comments
25 --Name: update_req_po
26 --Pre-reqs:
27 -- None.
28 --Modifies:
29 -- None.
30 --Locks:
31 -- None.
32 --Function:
33 -- Validate and Update Requisition and Purchase Order/Release. This procedure
34 -- is called by OM to synchroize key attribute values on Sales Order with the
35 -- corresponding Drop Ship Purchasing Documents.
36 --Parameters:
37 --IN:
38 --p_api_version
39 -- Specifies API version.
40 --p_req_header_id
41 -- Specifies Requisition Header ID.
42 -- The Req HeaderId/LineId identify the drop ship requisition line of
43 -- the backing sales order line
44 --p_req_line_id
45 -- Specifies Requisition Line ID.
46 -- The Req HeaderId/LineId identify the drop ship requisition line of
47 -- the backing sales order line
48 --p_po_header_id := NULL
49 -- Specifies Purchase Order Header ID.
50 -- The PO HeaderId/LineId/LineLocationId identify the drop ship PO Shipment of
51 -- the backing sales order line
52 --p_po_release_id := NULL
53 -- Specifies Purchase Order Release ID.
54 -- The PO ReleaseId/LineId/LineLocationId identify the drop ship
55 -- Release Shipment of the backing sales order line
56 --p_po_line_id := NULL
57 -- Specifies Purchase Order Line ID.
58 -- The PO LineId/LineLocationId together with a PO HeaderId or ReleaseId
59 -- identify the drop ship PO/Release Shipment of the backing sales order line
60 --p_po_line_location_id := NULL
61 -- Specifies Purchase Order Shipment ID.
62 -- The PO LineId/LineLocationId together with a PO HeaderId or ReleaseId
63 -- identify the drop ship PO/Release Shipment of the backing sales order line
64 --p_quantity := NULL
65 -- The new quantity value to update on Requisition Line and PO/Release Shipment
66 -- When quantity changes on SO Line, both p_quantity and p_secondary_quantity
67 -- should be passed in with latest values on SO line.
68 --p_secondary_quantity := NULL
69 -- The new secondary quantity value to update on Requisition Line
70 -- and PO/Release Shipment. This should be passed only when quantity changes
71 -- on SO. This should NOT be passed if sec qty changes but no change to qty
72 --p_need_by_date := NULL
73 -- The new need by date value to update on Requisition Line and
74 -- PO/Release Shipment. This field is called Schedule Date on SO Line.
75 --p_ship_to_location_id := NULL
76 -- The new Ship To Location to update on Requisition Line and PO/Release Shipment
77 --p_sales_order_update_date := NULL
78 -- The new sales order update date to update on PO/Release Shipment.
79 -- When any of the referenced data elements on SO change, this pararameter is
80 -- sent in with value of SYSDATE.
81 --OUT:
82 --x_return_status
83 -- Indicates API return status as 'S', 'E' or 'U'.
84 --x_msg_count
85 -- Number of Error messages
86 --x_msg_data
87 -- Error messages body
88 --Notes:
89 -- Requisition and/or PO can be in a different Operating Unit from the current one
90 --Testing:
91 -- Call the API when only Requisition Exist, PO/Release Exist
92 -- and for all the combinations of attributes.
93 --End of Comments
94 -------------------------------------------------------------------------------
95
96 PROCEDURE update_req_po
97 (
98 p_api_version IN NUMBER,
99 p_req_header_id IN PO_TBL_NUMBER,
100 p_req_line_id IN PO_TBL_NUMBER,
101 p_po_header_id IN PO_TBL_NUMBER := NULL,
102 p_po_release_id IN PO_TBL_NUMBER := NULL,
103 p_po_line_id IN PO_TBL_NUMBER := NULL,
104 p_po_line_location_id IN PO_TBL_NUMBER := NULL,
105 p_quantity IN PO_TBL_NUMBER := NULL,
106 p_secondary_quantity IN PO_TBL_NUMBER := NULL,
107 p_need_by_date IN PO_TBL_DATE := NULL,
108 p_ship_to_location_id IN PO_TBL_NUMBER := NULL,
109 p_sales_order_update_date IN PO_TBL_DATE := NULL,
110 p_preferred_grade IN PO_TBL_VARCHAR240 := NULL, --<INVCONV R12>
111 x_return_status OUT NOCOPY VARCHAR2,
112 x_msg_count OUT NOCOPY NUMBER,
113 x_msg_data OUT NOCOPY VARCHAR2
114 )
115 IS
116 l_api_name CONSTANT VARCHAR(30) := 'UPDATE_REQ_PO';
117 l_api_version CONSTANT NUMBER := 1.0;
118 l_progress VARCHAR2(3) := '000';
119 l_log_head CONSTANT VARCHAR2(100) := c_log_head || '.' || l_api_name;
120
121 -- Bug 3292895 START
122 TYPE indexed_tbl_number IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
123
124 l_processed_pos INDEXED_TBL_NUMBER;
125 l_processed_releases INDEXED_TBL_NUMBER;
126 l_processed_reqs INDEXED_TBL_NUMBER;
127
128 l_cur_po_header_id PO_HEADERS_ALL.po_header_id%TYPE;
129 l_cur_po_release_id PO_RELEASES_ALL.po_release_id%TYPE;
130 l_cur_req_header_id PO_REQUISITION_HEADERS_ALL.requisition_header_id%TYPE;
131 l_start_index NUMBER;
132
133 l_po_changes PO_CHANGES_REC_TYPE;
134 l_errors PO_API_ERRORS_REC_TYPE;
135 l_req_changes PO_REQ_CHANGES_REC_TYPE;
136 -- Bug 3292895 END
137
138 l_quantity_ordered PO_TBL_NUMBER := p_quantity;
139
140 l_original_org_id NUMBER := PO_MOAC_UTILS_PVT.get_current_org_id ; -- <R12 MOAC> added
141 l_document_org_id PO_HEADERS.ORG_ID%TYPE;
142 l_count NUMBER;
143
144 --Bug# 4640038 Start, Added the following variable
145
146 l_po_request_unit_of_measure PO_LINES_ALL.UNIT_MEAS_LOOKUP_CODE%TYPE;
147 l_po_request_secondary_uom PO_LINES_ALL.SECONDARY_UOM%TYPE;
148 l_so_request_unit_of_measure mtl_units_of_measure.UNIT_OF_MEASURE_TL%TYPE;
149 l_so_request_secondary_uom mtl_units_of_measure.UNIT_OF_MEASURE_TL%TYPE;
150 l_uom_conversion_rate NUMBER := 1;
151 l_suom_conversion_rate NUMBER := 1;
152 l_secondary_quantity_ordered PO_TBL_NUMBER := p_secondary_quantity;
153 l_item_id po_lines.item_id%TYPE;
154
155 --Bug# 4640038 End
156
157 -- Bug 3639067
158 l_drop_ship_flag PO_LINE_LOCATIONS_ALL.drop_ship_flag%TYPE;
159
160 BEGIN
161
162 IF g_fnd_debug = 'Y' THEN
163 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
164 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, c_log_head || '.'||l_api_name||'.'
165 || l_progress, 'Entering Procedure '||l_api_name);
166 END IF;
167 END IF;
168
169 -- Standard call to check for call compatibility
170 l_progress := '010';
171 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
172 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
173 END IF;
174
175 SAVEPOINT PO_OM_GRP_UPDATE_REQ_PO_SP;
176
177 -- Bug 3292895 START
178 -- Rewrote the grouping logic for PO shipments / requistion lines so that
179 -- it does not use nested tables, which are not supported in Oracle 8i.
180
181 -- Group the changes to various PO Shipments by po_header_id/po_release_id
182 -- and call the PO Change API for each PO/release.
183 l_progress := '020';
184
185 LOOP
186 l_cur_po_header_id := NULL;
187 l_cur_po_release_id := NULL;
188
189 -- Find the next PO or release to process.
190 l_progress := '030';
191 FOR i IN 1..p_po_header_id.count LOOP
192
193 IF (p_po_release_id(i) IS NOT NULL)
194 AND (NOT l_processed_releases.EXISTS(p_po_release_id(i))) THEN
195 -- We found a release that has not been processed yet.
196 l_cur_po_release_id := p_po_release_id(i);
197 l_processed_releases(l_cur_po_release_id) := 1; -- Mark it as processed.
198 -- Bug 3691067. For a release, mark its BPA also as processed because
199 -- changes to the Sales Order should only be reflected in the Release
200 -- not its corresponding Blanket
201 l_processed_pos(p_po_header_id(i)) := 1;
202 l_start_index := i;
203 EXIT;
204 END IF;
205
206 IF (p_po_header_id(i) IS NOT NULL)
207 AND (NOT l_processed_pos.EXISTS(p_po_header_id(i))) THEN
208 -- We found a PO that has not been processed yet.
209 l_cur_po_header_id := p_po_header_id(i);
210 l_processed_pos(l_cur_po_header_id) := 1; -- Mark it as processed.
211 l_start_index := i;
212 EXIT;
213 END IF;
214
215 END LOOP; -- 1..p_po_header_id.count
216
217 -- Exit the loop once all POs/releases have been processed.
218 EXIT WHEN ((l_cur_po_header_id IS NULL) AND (l_cur_po_release_id IS NULL));
219
220 -- Create a change object for the PO/release.
221 l_progress := '040';
222 l_po_changes := PO_CHANGES_REC_TYPE.create_object (
223 p_po_header_id => l_cur_po_header_id,
224 p_po_release_id => l_cur_po_release_id );
225
226 -- Add the shipment changes for this PO/release to the change object.
227 l_progress := '050';
228 FOR i IN l_start_index..p_po_header_id.count LOOP
229 IF ((l_cur_po_header_id IS NOT NULL)
230 AND (l_cur_po_header_id = p_po_header_id(i))) OR
231 ((l_cur_po_release_id IS NOT NULL)
232 AND (l_cur_po_release_id = p_po_release_id(i))) THEN
233
234 -- Bug 3639067 START
235 -- Only synchronize if the shipment is flagged as drop ship.
236 -- (Pre-11.5.10 drop shipments may still have drop_ship_flag of NULL
237 -- if the upgrade script poxupgds.sql found discrepancies between the
238 -- sales order, requisition, and PO. In this case, we should not
239 -- automatically synchronize SO changes to the req/PO.)
240 SELECT drop_ship_flag
241 INTO l_drop_ship_flag
242 FROM po_line_locations_all
243 WHERE line_location_id = p_po_line_location_id(i);
244
245 IF (l_drop_ship_flag = 'Y') THEN
246
247 --Bug 3256289: Pass ReqLine's UOM, SecondaryUOM to Change PO API as request UOMs
248 -- Bug# 4640038, Now getting the uom from
249 -- Drop ship sales order line and convering the quantity
250 -- to the PO UOM.
251
252 Begin
253
254 l_progress := '052';
255
256 -- Bug# 4640038, Get the uom and secondary uom of sales order line
257 --bug5606683 changed the following sql select clause to get the UOM instead of
258 -- their translated value.
259 SELECT puom.unit_of_measure,
260 suom.unit_of_measure
261 INTO l_so_request_unit_of_measure,
262 l_so_request_secondary_uom
263 FROM oe_order_lines_all ol,
264 oe_drop_ship_sources ds,
265 mtl_units_of_measure puom,
266 mtl_units_of_measure suom
267 WHERE ol.line_id=ds.line_id
268 AND ds.line_location_id= p_po_line_location_id(i)
269 and ol.order_quantity_uom= puom.uom_code
270 and ol.ordered_quantity_uom2=suom.uom_code(+);
271
272 Exception
273
274 When OTHERS then
275
276 IF (g_debug_stmt) THEN
277 PO_DEBUG.debug_stmt (
278 p_log_head => l_log_head,
279 p_token => l_progress,
280 p_message => 'Exception while retrieving drop ship row :' || p_po_line_location_id(i) );
281 END IF;
282
283 End;
284
285
286 IF (g_debug_stmt) THEN
287 PO_DEBUG.debug_stmt (
288 p_log_head => l_log_head,
289 p_token => l_progress,
290 p_message => 'Sales Order uom :' ||l_so_request_unit_of_measure||', Secondary UOM :'||l_so_request_secondary_uom);
291 END IF;
292
293 l_progress := '054';
294
295 -- Bug# 4640038, Get the uom and secondary uom of PO line
296
297 Select item_id ,
298 unit_meas_lookup_code ,
299 secondary_uom
300 INTO l_item_id,
301 l_po_request_unit_of_measure,
302 l_po_request_secondary_uom
303 from po_lines_all
304 WHERE po_line_id = p_po_line_id(i);
305
306 IF (g_debug_stmt) THEN
307 PO_DEBUG.debug_stmt (
308 p_log_head => l_log_head,
309 p_token => l_progress,
310 p_message => 'PO uom :' ||l_po_request_unit_of_measure||', Secondary UOM :'||l_po_request_secondary_uom);
311
312 END IF;
313
314 -- Bug# 4640038, Get the conversion rate of uom from SO to PO
315 l_progress := '056';
316 IF l_po_request_unit_of_measure IS NOT NULL AND
317 l_so_request_unit_of_measure IS NOT NULL THEN
318 IF l_po_request_unit_of_measure <> l_so_request_unit_of_measure then
319 l_uom_conversion_rate := nvl(po_uom_s.po_uom_convert(
320 l_so_request_unit_of_measure,
321 l_po_request_unit_of_measure,
322 l_item_id),1);
323 IF (g_debug_stmt) THEN
324 PO_DEBUG.debug_stmt (
325 p_log_head => l_log_head,
326 p_token => l_progress,
327 p_message => 'PO UOM conversion rate :' ||to_char(l_uom_conversion_rate));
328 END IF;
329 END IF;
330 END IF;
331
332 -- Bug# 4640038, Get the conversion rate of secondary uom from SO to PO
333 l_progress := '058';
334 IF l_po_request_secondary_uom is not NULL AND
335 l_so_request_secondary_uom is not NULL THEN
336 IF l_po_request_secondary_uom <> l_so_request_secondary_uom THEN
337 l_suom_conversion_rate := nvl(po_uom_s.po_uom_convert(
338 l_so_request_secondary_uom,
339 l_po_request_secondary_uom,
340 l_item_id),1);
341 IF (g_debug_stmt) THEN
342 PO_DEBUG.debug_stmt (
343 p_log_head => l_log_head,
344 p_token => l_progress,
345 p_message => 'PO Secondary UOM conversion rate :' ||to_char(l_suom_conversion_rate));
346 END IF;
347 END IF;
348 END IF;
349
350 /*
351
352 Bug# 4640038, Commented out this code
353 The Requisition could be in a different uom that the sales order
354 When changing the quantity iof the SO we need to pass the quantity
355 converted to the PO UOM as the PO ans SO can be in different UOM.
356 We will convert the SO quantity to the PO Quantity and pass it
357 to the change order api.
358
359
360 SELECT unit_meas_lookup_code, secondary_unit_of_measure
361 INTO l_request_unit_of_measure, l_request_secondary_uom
362 FROM po_requisition_lines_all
363 WHERE requisition_line_id = p_req_line_id(i);
364
365 */
366
367 --Bug 3239540: OM passes p_quantity as the new open, undelivered
368 --quantity. We need to add the quantity delivered:
369 -- new quantity ordered = new undelivered quantity + quantity delivered
370 -- Bug# 4640038 Added the conversion rate calculation below
371
372 IF (p_quantity(i) IS NULL) THEN
373 l_quantity_ordered(i) := NULL;
374
375 ELSE
376
377 SELECT round((p_quantity(i) * l_uom_conversion_rate),5) + nvl(sum(nvl(quantity_delivered, 0)), 0)
378 INTO l_quantity_ordered(i)
379 FROM po_distributions_all
380 WHERE line_location_id = p_po_line_location_id(i);
381
382 END IF;
383
384 -- Bug# 4640038 Added the conversion rate calculation below
385
386 IF (p_secondary_quantity(i) IS NULL) THEN
387 l_secondary_quantity_ordered(i) := NULL;
388 ELSE
389
390 SELECT round((p_secondary_quantity(i) * l_suom_conversion_rate),5) + NVL(SUM(NVL(secondary_quantity_received,0)),0)
391 INTO l_secondary_quantity_ordered(i)
392 FROM po_line_locations_all
393 WHERE line_location_id = p_po_line_location_id(i);
394 END IF;
395
396
397 -- Add this shipment change to the PO change object.
398 l_po_changes.shipment_changes.add_change (
399 p_po_line_location_id => p_po_line_location_id(i),
400 p_quantity => l_quantity_ordered(i),
401 p_request_unit_of_measure => l_po_request_unit_of_measure,
402 p_secondary_quantity => p_secondary_quantity(i),
403 p_request_secondary_uom => l_po_request_secondary_uom,
404 p_need_by_date => p_need_by_date(i),
405 p_ship_to_location_id => p_ship_to_location_id(i),
406 p_preferred_grade => p_preferred_grade(i), --<INVCONV R12>
407 p_sales_order_update_date => p_sales_order_update_date(i) );
408
409 -- Bug 3639067 START
410 ELSE -- l_drop_ship_flag is not Y
411 IF (g_debug_stmt) THEN
412 PO_DEBUG.debug_stmt (
413 p_log_head => l_log_head,
414 p_token => l_progress,
415 p_message => 'Skip this PO shipment - drop_ship_flag is not Y: '
416 || p_po_line_location_id(i) );
417 END IF;
418 END IF; -- l_drop_ship_flag
419 -- Bug 3639067 END
420
421 END IF;
422 END LOOP; -- l_start_index..p_po_header_id.count
423
424 -- Update the PO/Release by calling the PO Change API.
425 -- Bug 3248723 START
426 l_progress := '060';
427 IF (l_po_changes.shipment_changes.get_count > 0) THEN -- Bug 3639067
428 call_po_change_api ( p_chg => l_po_changes,
429 x_return_status => x_return_status );
430 END IF; -- Bug 3639067
431 -- Bug 3248723 END
432
433 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
434 RAISE FND_API.g_exc_error;
435 ELSIF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
436 RAISE FND_API.g_exc_unexpected_error;
437 END IF;
438
439 END LOOP;
440
441 -- Group the changes to various requisition lines by requisition_header_id
442 -- and call the Req Change API for each requisition.
443 l_progress := '200';
444
445 LOOP
446 l_cur_req_header_id := NULL;
447
448 -- Find the next requisition to process.
449 l_progress := '210';
450 FOR i IN 1..p_req_header_id.count LOOP
451
452 IF (p_req_header_id(i) IS NOT NULL)
453 AND (NOT l_processed_reqs.EXISTS(p_req_header_id(i))) THEN
454 -- We found a requisition that has not been processed yet.
455 l_cur_req_header_id := p_req_header_id(i);
456 l_processed_reqs(l_cur_req_header_id) := 1; -- Mark it as processed.
457 l_start_index := i;
458 EXIT;
459 END IF;
460
461 END LOOP; -- 1..p_req_header_id.count
462
463 -- Exit the loop once all requisitions have been processed.
464 EXIT WHEN (l_cur_req_header_id IS NULL);
465
466 -- Create a change object for the requisition.
467 l_progress := '220';
468 -- Bug 3331194 Set unit_price, etc. to PO_TBL_NUMBER() instead of NULL.
469 l_req_changes := PO_REQ_CHANGES_REC_TYPE (
470 req_header_id => l_cur_req_header_id,
471 line_changes =>
472 PO_REQ_LINES_REC_TYPE (
473 req_line_id => PO_TBL_NUMBER(),
474 unit_price => PO_TBL_NUMBER(),
475 currency_unit_price => PO_TBL_NUMBER(),
476 quantity => PO_TBL_NUMBER(),
477 secondary_quantity => PO_TBL_NUMBER(),
478 -- preferred_grade => PO_TBL_VARCHAR240(), --<INVCONV R12>
479 need_by_date => PO_TBL_DATE(),
480 deliver_to_location_id => PO_TBL_NUMBER(),
481 assignment_start_date => PO_TBL_DATE(),
482 assignment_end_date => PO_TBL_DATE(),
483 amount => PO_TBL_NUMBER() ),
484 distribution_changes => null );
485
486 -- Add the line changes for this requisition to the change object.
487 l_progress := '230';
488 FOR i IN l_start_index..p_req_header_id.count LOOP
489 IF (l_cur_req_header_id = p_req_header_id(i)) THEN
490
491 -- Bug 3639067 START
492 -- Only synchronize if the line is flagged as drop ship.
493 SELECT drop_ship_flag
494 INTO l_drop_ship_flag
495 FROM po_requisition_lines_all
496 WHERE requisition_line_id = p_req_line_id(i);
497
498 IF (l_drop_ship_flag = 'Y') THEN
499 -- Bug 3639067 END
500
501 l_req_changes.line_changes.req_line_id.extend(1);
502 l_count := l_req_changes.line_changes.req_line_id.count;
503 l_req_changes.line_changes.req_line_id(l_count) := p_req_line_id(i);
504
505 l_req_changes.line_changes.unit_price.extend(1);
506 l_req_changes.line_changes.currency_unit_price.extend(1);
507
508 l_req_changes.line_changes.quantity.extend(1);
509 l_req_changes.line_changes.quantity(l_count) := l_quantity_ordered(i);
510
511 l_req_changes.line_changes.secondary_quantity.extend(1);
512 l_req_changes.line_changes.secondary_quantity(l_count)
513 := p_secondary_quantity(i);
514
515 l_req_changes.line_changes.need_by_date.extend(1);
516 l_req_changes.line_changes.need_by_date(l_count)
517 := p_need_by_date(i);
518
519 l_req_changes.line_changes.deliver_to_location_id.extend(1);
520 l_req_changes.line_changes.deliver_to_location_id(l_count)
521 := p_ship_to_location_id(i);
522
523 l_req_changes.line_changes.assignment_start_date.extend(1);
524 l_req_changes.line_changes.assignment_end_date.extend(1);
525 l_req_changes.line_changes.amount.extend(1);
526
527 -- Bug 3639067 START
528 ELSE -- l_drop_ship_flag is not Y
529 IF (g_debug_stmt) THEN
530 PO_DEBUG.debug_stmt (
531 p_log_head => l_log_head,
532 p_token => l_progress,
533 p_message => 'Skip this req line - drop_ship_flag is not Y: '
534 || p_req_line_id(i) );
535 END IF;
536 END IF; -- l_drop_ship_flag
537 -- Bug 3639067 END
538
539 END IF;
540 END LOOP; -- l_start_index..p_req_header_id.count
541
542 -- Update the Requisition by calling the Update Requisition API.
543 l_progress := '240';
544
545 IF (l_req_changes.line_changes.req_line_id.count > 0) THEN -- Bug 3639067
546
547 -- Retrieve the document's operating unit.
548 SELECT org_id
549 INTO l_document_org_id
550 FROM po_requisition_headers_all
551 WHERE requisition_header_id = l_cur_req_header_id;
552
553 -- Set Org Context to that of Document
554 PO_MOAC_UTILS_PVT.set_org_context(l_document_org_id) ; -- <R12 MOAC>
555
556 l_progress := '250';
557 PO_REQ_DOCUMENT_UPDATE_GRP.update_requisition (
558 p_api_version => 1.0,
559 p_req_changes => l_req_changes,
560 p_update_source => 'OM',
561 x_return_status => x_return_status,
562 x_msg_count => x_msg_count,
563 x_msg_data => x_msg_data );
564
565 IF (x_return_status = FND_API.g_ret_sts_error) THEN
566 RAISE FND_API.g_exc_error;
567 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
568 RAISE FND_API.g_exc_unexpected_error;
569 END IF;
570
571 END IF; -- l_req_changes... - Bug 3639067
572
573 END LOOP;
574 -- Bug 3292895 END
575
576 l_progress := '900';
577 -- Set the org context back to the original operating unit.
578 PO_MOAC_UTILS_PVT.set_org_context(l_original_org_id) ; -- <R12 MOAC>
579
580 x_return_status := FND_API.G_RET_STS_SUCCESS;
581
582 EXCEPTION
583 WHEN FND_API.G_EXC_ERROR THEN
584 ROLLBACK TO PO_OM_GRP_UPDATE_REQ_PO_SP;
585 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
586 x_return_status := FND_API.G_RET_STS_ERROR;
587 -- Set the org context back to the original operating unit.
588 PO_MOAC_UTILS_PVT.set_org_context(l_original_org_id) ; -- <R12 MOAC>
589 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
590 ROLLBACK TO PO_OM_GRP_UPDATE_REQ_PO_SP;
591 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
592 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
593 -- Set the org context back to the original operating unit.
594 PO_MOAC_UTILS_PVT.set_org_context(l_original_org_id) ; -- <R12 MOAC>
595 WHEN OTHERS THEN
596 ROLLBACK TO PO_OM_GRP_UPDATE_REQ_PO_SP;
597 PO_DEBUG.handle_unexp_error ( p_pkg_name => g_pkg_name,
598 p_proc_name => l_api_name,
599 p_progress => l_progress );
600 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
601 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
602 -- Set the org context back to the original operating unit.
603 PO_MOAC_UTILS_PVT.set_org_context(l_original_org_id) ; -- <R12 MOAC>
604
605 END update_req_po;
606
607 -------------------------------------------------------------------------------
608 --Start of Comments
609 --Name: cancel_req_po
610 --Pre-reqs:
611 -- None.
612 --Modifies:
613 -- None.
614 --Locks:
615 -- None.
616 --Function:
617 -- Validate and Cancel Requisition and Purchase Order/Release. This procedure
618 -- is called by OM when a Drop Ship SO Line is cancelled or deleted.
619 --Parameters:
620 --IN:
621 --p_api_version
622 -- Specifies API version.
623 --p_req_header_id
624 -- Specifies Requisition Header ID.
625 -- The Req HeaderId/LineId identify the drop ship requisition line of
626 -- the backing sales order line
627 --p_req_line_id
628 -- Specifies Requisition Line ID.
629 -- The Req HeaderId/LineId identify the drop ship requisition line of
630 -- the backing sales order line
631 --p_po_header_id := NULL
632 -- Specifies Purchase Order Header ID.
633 -- The PO HeaderId/LineId/LineLocationId identify the drop ship PO Shipment of
634 -- the backing sales order line
635 --p_po_release_id := NULL
636 -- Specifies Purchase Order Header ID.
637 -- The PO ReleaseId/LineId/LineLocationId identify the drop ship
638 -- Release Shipment of the backing sales order line
639 --p_po_line_id := NULL
640 -- Specifies Purchase Order Line ID.
641 -- The PO LineId/LineLocationId together with a PO HeaderId or ReleaseId
642 -- identify the drop ship PO/Release Shipment of the backing sales order line
643 --p_po_line_location_id := NULL
644 -- Specifies Purchase Order Shipment ID.
645 -- The PO LineId/LineLocationId together with a PO HeaderId or ReleaseId
646 -- identify the drop ship PO/Release Shipment of the backing sales order line
647 --OUT:
648 --x_return_status
649 -- Indicates API return status as 'S', 'E' or 'U'.
650 --x_msg_count
651 -- Number of Error messages
652 --x_msg_data
653 -- Error messages body
654 --Notes:
655 -- Requisition and/or PO can be in a different Operating Unit from the current one
656 --Testing:
657 -- Call the API when only Requisition Exist, and when PO/Release also Exist
658 --End of Comments
659 -------------------------------------------------------------------------------
660
661 PROCEDURE cancel_req_po
662 (
663 p_api_version IN NUMBER,
664 p_req_header_id IN PO_TBL_NUMBER,
665 p_req_line_id IN PO_TBL_NUMBER,
666 p_po_header_id IN PO_TBL_NUMBER := NULL,
667 p_po_release_id IN PO_TBL_NUMBER := NULL,
668 p_po_line_id IN PO_TBL_NUMBER := NULL,
669 p_po_line_location_id IN PO_TBL_NUMBER := NULL,
670 x_return_status OUT NOCOPY VARCHAR2,
671 x_msg_count OUT NOCOPY NUMBER,
672 x_msg_data OUT NOCOPY VARCHAR2
673 )
674 IS
675 l_api_name CONSTANT VARCHAR(30) := 'CANCEL_REQ_PO';
676 l_api_version CONSTANT NUMBER := 1.0;
677 l_progress VARCHAR2(3) := '000';
678 l_log_head CONSTANT VARCHAR2(100) := c_log_head || '.' || l_api_name;
679
680 l_doc_type PO_DOCUMENT_TYPES.DOCUMENT_TYPE_CODE%TYPE;
681 l_doc_subtype PO_DOCUMENT_TYPES.DOCUMENT_SUBTYPE%TYPE;
682
683 -- Bug 3248723 START
684 l_original_org_id NUMBER := PO_MOAC_UTILS_PVT.get_current_org_id ; -- <R12 MOAC>
685 l_approved_date PO_LINE_LOCATIONS_ALL.approved_date%TYPE;
686 l_chg PO_CHANGES_REC_TYPE;
687 -- Bug 3248723 END
688 l_document_org_id PO_HEADERS.ORG_ID%TYPE; -- Bug 3362534
689
690 -- Bug 3639067 START
691 l_drop_ship_flag PO_LINE_LOCATIONS_ALL.drop_ship_flag%TYPE;
692 l_session_gt_key PO_SESSION_GT.key%TYPE;
693 l_req_header_id PO_TBL_NUMBER;
694 l_req_line_id PO_TBL_NUMBER;
695 -- Bug 3639067 END
696
697 BEGIN
698
699 IF g_fnd_debug = 'Y' THEN
700 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
701 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, c_log_head || '.'||l_api_name||'.'
702 || l_progress, 'Entering Procedure '||l_api_name);
703 END IF;
704 END IF;
705
706 -- Standard call to check for call compatibility
707 l_progress := '010';
708 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
709 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
710 END IF;
711
712 -- Cancel PO/Release Shipments
713 l_progress := '020';
714 FOR i IN 1..p_po_header_id.count LOOP
715
716 IF p_po_header_id(i) IS NOT NULL OR p_po_release_id(i) IS NOT NULL THEN
717 --If PO is NOT null, that means PO is created, not just req
718
719 IF p_po_release_id(i) IS NOT NULL THEN
720 SELECT RELEASE_TYPE,
721 org_id -- Bug 3362534
722 INTO l_doc_subtype,
723 l_document_org_id
724 from PO_RELEASES_ALL
725 WHERE po_release_id = p_po_release_id(i);
726 l_doc_type := 'RELEASE';
727 ELSE
728 SELECT TYPE_LOOKUP_CODE,
729 org_id -- Bug 3362534
730 INTO l_doc_subtype,
731 l_document_org_id
732 from PO_HEADERS_ALL
733 WHERE po_header_id = p_po_header_id(i);
734 l_doc_type := 'PO';
735 END IF;
736
737 -- Bug 3248723 START
738 -- If the shipment has not been approved before, call the PO Change API
739 -- to delete it. Otherwise, call the PO Control API to cancel it.
740
741 l_progress := '025';
742 SELECT approved_date,
743 drop_ship_flag -- Bug 3639067
744 INTO l_approved_date,
745 l_drop_ship_flag
746 FROM po_line_locations_all
747 WHERE line_location_id = p_po_line_location_id(i);
748
749 -- Bug 3639067 START
750 -- Only synchronize if the shipment is flagged as drop ship.
751 IF (l_drop_ship_flag = 'Y') THEN
752 -- Bug 3639067 END
753
754 IF (l_approved_date IS NULL) THEN -- Delete the shipment.
755 l_progress := '030';
756 -- Create a change object with a request to delete the shipment.
757 l_chg := PO_CHANGES_REC_TYPE.create_object (
758 p_po_header_id => p_po_header_id(i),
759 p_po_release_id => p_po_release_id(i) );
760 l_chg.shipment_changes.add_change (
761 p_po_line_location_id => p_po_line_location_id(i),
762 p_delete_record => PO_DOCUMENT_UPDATE_GRP.G_PARAMETER_YES );
763
764 -- Call the PO Change API to delete the shipment.
765 call_po_change_api ( p_chg => l_chg,
766 x_return_status => x_return_status );
767 ELSE -- Cancel the shipment.
768 -- Bug 3248723 END
769 l_progress := '035';
770
771 -- Bug 3362534 Set the org context to the operating unit of the document.
772 PO_MOAC_UTILS_PVT.set_org_context(l_document_org_id) ; -- <R12 MOAC>
773
774 PO_Document_Control_GRP.control_document
775 (p_api_version => 1.0,
776 p_init_msg_list => FND_API.G_FALSE,
777 p_commit => FND_API.G_FALSE,
778 x_return_status => x_return_status,
779 p_doc_type => l_doc_type,
780 p_doc_subtype => l_doc_subtype,
781 p_doc_id => p_po_header_id(i),
782 p_doc_num => null,
783 p_release_id => p_po_release_id(i),
784 p_release_num => null,
785 p_doc_line_id => p_po_line_id(i),
786 p_doc_line_num => null,
787 p_doc_line_loc_id => p_po_line_location_id(i),
788 p_doc_shipment_num => null,
789 p_source => 'OM',
790 p_action => 'CANCEL',
791 p_action_date => SYSDATE,
792 p_cancel_reason => 'Order Management',
793 p_cancel_reqs_flag => 'N',
794 p_print_flag => 'N',
795 p_note_to_vendor => null);
796 END IF; -- l_approved_date
797
798 IF (x_return_status = FND_API.g_ret_sts_error) THEN
799 RAISE FND_API.g_exc_error;
800 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
801 RAISE FND_API.g_exc_unexpected_error;
802 END IF;
803
804 -- Bug 3639067 START
805 ELSE -- l_drop_ship_flag is not Y
806 IF (g_debug_stmt) THEN
807 PO_DEBUG.debug_stmt (
808 p_log_head => l_log_head,
809 p_token => l_progress,
810 p_message => 'Skip this PO shipment - drop_ship_flag is not Y: '
811 || p_po_line_location_id(i) );
812 END IF;
813 END IF; -- l_drop_ship_flag
814 -- Bug 3639067 END
815
816 END IF; --End of IF p_po_header_id(i) IS NOT NULL ...
817
818 END LOOP;
819
820 -- Cancel Req Lines
821 l_progress := '040';
822
823 -- Bug 3639067 START
824 -- Only synchronize the lines that are flagged as drop ship.
825 -- To do this, we filter p_req_line_id, p_req_header_id to construct
826 -- new tables l_req_line_id, l_req_header_id that contain only the req lines
827 -- with drop_ship_flag = 'Y'.
828
829 -- SQL What: Retrieve a key to identify our records in PO_SESSION_GT.
830 SELECT PO_SESSION_GT_S.nextval
831 INTO l_session_gt_key
832 FROM dual;
833
834 ----------------------------------------------------------------
835 -- PO_SESSION_GT column mapping
836 --
837 -- num1 requisition_line_id
838 -- num2 requisition_header_id
839 ----------------------------------------------------------------
840
841 -- SQL What: Insert into PO_SESSION_GT the IDs of the req lines that have
842 -- drop_ship_flag = 'Y'
843 FORALL i IN 1..p_req_line_id.COUNT
844 INSERT INTO po_session_gt
845 (key, num1, num2)
846 SELECT l_session_gt_key, PRL.requisition_line_id, PRL.requisition_header_id
847 FROM po_requisition_lines_all PRL
848 WHERE PRL.requisition_line_id = p_req_line_id(i)
849 AND PRL.drop_ship_flag = 'Y';
850
851 -- SQL What: Select the req line and header IDs from PO_SESSION_GT into
852 -- the PL/SQL tables l_req_line_id and l_req_header_id.
853 SELECT GT.num1, GT.num2
854 BULK COLLECT INTO l_req_line_id, l_req_header_id
855 FROM po_session_gt GT
856 WHERE key = l_session_gt_key;
857
858 IF (g_debug_stmt) THEN
859 PO_DEBUG.debug_var (
860 p_log_head => l_log_head,
861 p_progress => l_progress,
862 p_name => 'l_req_header_id',
863 p_value => l_req_header_id
864 );
865
866 PO_DEBUG.debug_var (
867 p_log_head => l_log_head,
868 p_progress => l_progress,
869 p_name => 'l_req_line_id',
870 p_value => l_req_line_id
871 );
872
873 IF (p_req_line_id.COUNT > l_req_line_id.COUNT) THEN
874 PO_DEBUG.debug_stmt (
875 p_log_head => l_log_head,
876 p_token => l_progress,
877 p_message => 'Skipped '||(p_req_line_id.COUNT - l_req_line_id.COUNT)
878 ||' req lines whose drop_ship_flag is not Y' );
879 END IF;
880 END IF;
881
882 IF (l_req_header_id.COUNT > 0) THEN
883 -- Bug 3639067 END
884
885 PO_REQ_DOCUMENT_CANCEL_GRP.cancel_requisition
886 (p_api_version => 1.0,
887 p_req_header_id => l_req_header_id, -- Bug 3639067
888 p_req_line_id => l_req_line_id, -- Bug 3639067
889 p_cancel_date => SYSDATE,
890 p_cancel_reason => 'Order Management',
891 p_source => 'OM',
892 x_return_status => x_return_status,
893 x_msg_count => x_msg_count,
894 x_msg_data => x_msg_data);
895
896 IF (x_return_status = FND_API.g_ret_sts_error) THEN
897 RAISE FND_API.g_exc_error;
898 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
899 RAISE FND_API.g_exc_unexpected_error;
900 END IF;
901
902 END IF; -- l_req_header_id - Bug 3639067
903
904 l_progress := '050';
905
906 x_return_status := FND_API.G_RET_STS_SUCCESS;
907
908 -- Set the org context back to the original operating unit.
909 PO_MOAC_UTILS_PVT.set_org_context(l_original_org_id) ; -- <R12 MOAC>
910
911 EXCEPTION
912 WHEN FND_API.G_EXC_ERROR THEN
913 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
914 x_return_status := FND_API.G_RET_STS_ERROR;
915 -- Set the org context back to the original operating unit.
916 PO_MOAC_UTILS_PVT.set_org_context(l_original_org_id) ; -- <R12 MOAC>
917 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
918 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
919 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
920 -- Set the org context back to the original operating unit.
921 PO_MOAC_UTILS_PVT.set_org_context(l_original_org_id) ; -- <R12 MOAC>
922 WHEN OTHERS THEN
923 PO_DEBUG.handle_unexp_error ( p_pkg_name => g_pkg_name,
924 p_proc_name => l_api_name,
925 p_progress => l_progress );
926 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
927 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
928 -- Set the org context back to the original operating unit.
929 PO_MOAC_UTILS_PVT.set_org_context(l_original_org_id) ; -- <R12 MOAC>
930
931 END cancel_req_po;
932
933 -- Bug 3248723 START
934 -------------------------------------------------------------------------------
935 --Start of Comments
936 --Name: call_po_change_api
937 --Pre-reqs:
938 -- None.
939 --Modifies:
940 -- the org context
941 --Locks:
942 -- None.
943 --Function:
944 -- Switches the org context to the document's operating unit and calls the
945 -- PO Change API to apply the requested changes.
946 --Parameters:
947 --IN:
948 --p_chg
949 -- Object with the requested changes
950 --OUT:
951 --x_return_status
952 -- Indicates procedure return status.
953 --End of Comments
954 -------------------------------------------------------------------------------
955 PROCEDURE call_po_change_api (
956 p_chg IN OUT NOCOPY PO_CHANGES_REC_TYPE,
957 x_return_status OUT NOCOPY VARCHAR2
958 ) IS
959 l_proc_name CONSTANT VARCHAR2(30) := 'call_po_change_api';
960
961 l_progress VARCHAR2(3) := '000';
962 l_authorization_status PO_HEADERS.AUTHORIZATION_STATUS%TYPE;
963 l_document_org_id PO_HEADERS.ORG_ID%TYPE;
964 l_run_submission_checks VARCHAR2(1);
965 l_launch_approvals_flag VARCHAR2(1);
966 l_errors PO_API_ERRORS_REC_TYPE;
967 BEGIN
968 -- Retrieve the document's operating unit.
969 IF p_chg.po_release_id IS NOT NULL THEN
970 SELECT org_id, authorization_status
971 INTO l_document_org_id, l_authorization_status
972 FROM po_releases_all
973 WHERE po_release_id = p_chg.po_release_id;
974 ELSE -- PO
975 SELECT org_id, authorization_status
976 INTO l_document_org_id, l_authorization_status
977 FROM po_headers_all
978 WHERE po_header_id = p_chg.po_header_id;
979 END IF;
980
981 l_progress := '010';
982
983 -- Set Org Context to that of Document
984 PO_MOAC_UTILS_PVT.set_org_context(l_document_org_id) ; -- <R12 MOAC>
985
986 -- If the PO was in Approved status before the change, then perform
987 -- submission checks and launch the PO Approval workflow to re-approve
988 -- the PO after making the changes.
989 IF l_authorization_status = 'APPROVED' THEN
990 l_run_submission_checks := FND_API.G_TRUE;
991 l_launch_approvals_flag := FND_API.G_TRUE;
992 ELSE
993 l_run_submission_checks := FND_API.G_FALSE;
994 l_launch_approvals_flag := FND_API.G_FALSE;
995 END IF;
996
997 l_progress := '020';
998 PO_DOCUMENT_UPDATE_GRP.update_document (
999 p_api_version => 1.0,
1000 p_init_msg_list => FND_API.G_FALSE,
1001 x_return_status => x_return_status,
1002 p_changes => p_chg,
1003 p_run_submission_checks => l_run_submission_checks,
1004 p_launch_approvals_flag => l_launch_approvals_flag,
1005 p_buyer_id => null,
1006 p_update_source => PO_DOCUMENT_UPDATE_GRP.G_UPDATE_SOURCE_OM,
1007 p_override_date => null,
1008 x_api_errors => l_errors,
1009 p_approval_background_flag => PO_DOCUMENT_UPDATE_GRP.G_PARAMETER_NO
1010 );
1011
1012 l_progress := '030';
1013 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1014 -- If non success status, transfer l_errors to FND Message Stack
1015 FOR j IN 1..l_errors.message_text.count LOOP
1016 FND_MESSAGE.set_name('PO', 'PO_WRAPPER_MESSAGE');
1017 FND_MESSAGE.set_token('MESSAGE', l_errors.message_text(j));
1018 FND_MSG_PUB.Add;
1019 END LOOP;
1020 END IF;
1021
1022 EXCEPTION
1023 WHEN OTHERS THEN
1024 PO_DEBUG.handle_unexp_error ( p_pkg_name => g_pkg_name,
1025 p_proc_name => l_proc_name,
1026 p_progress => l_progress );
1027 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1028 END call_po_change_api;
1029 -- Bug 3248723 END
1030
1031 -- <DOC PURGE FPJ START>
1032
1033 -------------------------------------------------------------------------------
1034 --Start of Comments
1035 --Name: purge
1036 --Pre-reqs:
1037 -- None.
1038 --Modifies:
1039 -- drop_ship_flag
1040 --Locks:
1041 -- None.
1042 --Function:
1043 -- Perform necessary action on PO side when a Sales Order is purged.
1044 -- If the purged SO is a drop ship SO, the drop ship flag at PO shipment
1045 -- needs to be updated to NULL.
1046 --Parameters:
1047 --IN:
1048 --p_api_version
1049 -- Specifies API version.
1050 --p_init_msg_list
1051 -- Determines whether the message stacked can be initialized within the API
1052 --p_commit
1053 -- Determines whether the API will commit
1054 --p_entity
1055 -- Types of ids that are passing in. For now it has to be 'PO_LINE_LOCATIONS'
1056 --p_entity_id_tbl
1057 -- A table containing ids of the records to be updated. For now they must
1058 -- be drop shipments on a PO.
1059 --OUT:
1060 --IN OUT:
1061 --x_return_status
1062 -- Indicates API return status as 'S', 'E' or 'U'.
1063 --x_msg_count
1064 -- Number of Error messages
1065 --x_msg_data
1066 -- Error messages body
1067 --Notes:
1068 --Testing:
1069 --End of Comments
1070 -------------------------------------------------------------------------------
1071
1072 PROCEDURE Purge
1073 ( p_api_version IN NUMBER
1074 ,p_init_msg_list IN VARCHAR2
1075 ,p_commit IN VARCHAR2
1076 ,x_return_status OUT NOCOPY VARCHAR2
1077 ,x_msg_count OUT NOCOPY NUMBER
1078 ,x_msg_data OUT NOCOPY VARCHAR2
1079 ,p_entity IN VARCHAR2
1080 ,p_entity_id_tbl IN PO_TBL_NUMBER
1081 )
1082 IS
1083 l_api_name VARCHAR2(50) := 'Purge';
1084 l_api_version NUMBER := 1.0;
1085 l_progress VARCHAR2(3);
1086 l_log_head CONSTANT VARCHAR2(100) := c_log_head || '.' || l_api_name;
1087
1088 BEGIN
1089
1090 l_progress := '000';
1091
1092 x_return_status := FND_API.G_RET_STS_SUCCESS;
1093
1094 IF (g_fnd_debug = 'Y') THEN
1095 PO_DEBUG.debug_begin
1096 ( p_log_head => l_log_head
1097 );
1098 END IF;
1099
1100 IF (FND_API.to_boolean(p_init_msg_list)) THEN
1101 FND_MSG_PUB.initialize;
1102 END IF;
1103
1104 IF (NOT FND_API.Compatible_API_Call
1105 ( p_current_version_number => l_api_version,
1106 p_caller_version_number => p_api_version,
1107 p_api_name => l_api_name,
1108 p_pkg_name => g_pkg_name
1109 )
1110 ) THEN
1111
1112 l_progress := '010';
1113 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1114 END IF;
1115
1116 IF (p_entity_id_tbl IS NULL) THEN
1117 l_progress := '020';
1118
1119 IF (g_fnd_debug = 'Y') THEN
1120 PO_DEBUG.debug_stmt
1121 ( p_log_head => l_log_head,
1122 p_token => l_progress,
1123 p_message => 'entity_id_tbl is empty. quitting'
1124 );
1125 END IF;
1126
1127 RETURN;
1128 END IF;
1129
1130 IF (p_entity = 'PO_LINE_LOCATIONS') THEN
1131 l_progress := '030';
1132
1133 IF (g_fnd_debug = 'Y') THEN
1134 PO_DEBUG.debug_stmt
1135 ( p_log_head => l_log_head,
1136 p_token => l_progress,
1137 p_message => 'p_entity_id_tbl.COUNT=' ||
1138 p_entity_id_tbl.COUNT
1139 );
1140 END IF;
1141
1142 --SQL What: Set drop ship flag on the PO shipment to NULL when
1143 -- the assocaited SO is purged
1144 --SQL Why: The association between SO and PO will be removed once
1145 -- the SO is purged.
1146
1147 FORALL i IN 1..p_entity_id_tbl.COUNT
1148 UPDATE po_line_locations_all PLL
1149 SET drop_ship_flag = ''
1150 WHERE PLL.line_location_id = p_entity_id_tbl(i);
1151
1152 IF (g_fnd_debug = 'Y') THEN
1153 PO_DEBUG.debug_stmt
1154 ( p_log_head => l_log_head,
1155 p_token => l_progress,
1156 p_message => '# of updated rows: ' || SQL%ROWCOUNT
1157 );
1158 END IF;
1159
1160 ELSE
1161 l_progress := '050';
1162
1163 IF (g_fnd_debug = 'Y') THEN
1164 PO_DEBUG.debug_stmt
1165 ( p_log_head => l_log_head,
1166 p_token => l_progress,
1167 p_message => 'Unknown entity: ' || p_entity
1168 );
1169 END IF;
1170
1171 FND_MSG_PUB.add_exc_msg
1172 ( p_pkg_name => g_pkg_name,
1173 p_procedure_name => l_api_name || '.' || l_progress,
1174 p_error_text => 'Unknown entity: ' || p_entity
1175 );
1176
1177 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1178
1179 END IF;
1180
1181 l_progress := '060';
1182
1183 IF (FND_API.to_boolean(p_commit)) THEN
1184
1185 l_progress := '070';
1186 COMMIT;
1187
1188 END IF;
1189
1190 IF (g_fnd_debug = 'Y') THEN
1191 PO_DEBUG.debug_end
1192 ( p_log_head => l_log_head
1193 );
1194 END IF;
1195
1196 EXCEPTION
1197 WHEN OTHERS THEN
1198 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1199 PO_DEBUG.handle_unexp_error ( p_pkg_name => g_pkg_name,
1200 p_proc_name => l_api_name,
1201 p_progress => l_progress );
1202 FND_MSG_PUB.count_and_get
1203 ( p_encoded => 'F',
1204 p_count => x_msg_count,
1205 p_data => x_msg_data
1206 );
1207
1208 END Purge;
1209
1210
1211 -------------------------------------------------------------------------------
1212 --Start of Comments
1213 --Name: validate_purge
1214 --Pre-reqs:
1215 -- None.
1216 --Modifies:
1217 -- None
1218 --Locks:
1219 -- None.
1220 --Function:
1221 -- Given the drop shipment id of a PO, this procedure determines whether the
1222 -- corresponing SO is purgable. This is called before a Sales Order is about
1223 -- to be purged
1224 --Parameters:
1225 --IN:
1226 --p_api_version
1227 -- Specifies API version.
1228 --p_init_msg_list
1229 -- Determines whether the message stacked can be initialized within the API
1230 --p_commit
1231 -- Determines whether the API will commit
1232 --p_entity
1233 -- Types of ids that are passing in. For now it has to be 'PO_LINE_LOCATIONS'
1234 --p_entity_id_tbl
1235 -- A table containing ids of the records to checked. For now they must
1236 -- be the PO shipments, with the corrsponding SO line that are about to be
1237 -- purged
1238 --IN OUT:
1239 --OUT:
1240 --x_return_status
1241 -- Indicates API return status as 'S', 'E' or 'U'.
1242 --x_msg_count
1243 -- Number of Error messages
1244 --x_msg_data
1245 -- Error messages body
1246 --x_purge_allowed_tbl
1247 -- Returns 'Y' if the corresponding shipment in p_entity_id_tbl is in a state
1248 -- where the corresponding SO is allowed to be purged. (and 'N' otherwise)
1249 -- The number of entries returned in this structure should be the same as
1250 -- that in p_entity_id_tbl. This API will always re-initialize this parameter
1251 --Notes:
1252 --Testing:
1253 --End of Comments
1254 -------------------------------------------------------------------------------
1255
1256 PROCEDURE Validate_Purge
1257 ( p_api_version IN NUMBER
1258 ,p_init_msg_list IN VARCHAR2
1259 ,p_commit IN VARCHAR2
1260 ,x_return_status OUT NOCOPY VARCHAR2
1261 ,x_msg_count OUT NOCOPY NUMBER
1262 ,x_msg_data OUT NOCOPY VARCHAR2
1263 ,p_entity IN VARCHAR2
1264 ,p_entity_id_tbl IN PO_TBL_NUMBER
1265 ,x_purge_allowed_tbl OUT NOCOPY PO_TBL_VARCHAR1
1266 )
1267 IS
1268 l_api_name VARCHAR2(50) := 'Validate_Purge';
1269 l_api_version NUMBER := 1.0;
1270 l_progress VARCHAR2(3);
1271 l_log_head CONSTANT VARCHAR2(100) := c_log_head || '.' || l_api_name;
1272
1273 l_seq_id NUMBER;
1274
1275 l_order_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
1276 BEGIN
1277 l_progress := '000';
1278
1279 x_return_status := FND_API.G_RET_STS_SUCCESS;
1280
1281 IF (g_fnd_debug = 'Y') THEN
1282 PO_DEBUG.debug_begin
1283 ( p_log_head => l_log_head
1284 );
1285 END IF;
1286
1287 IF (FND_API.to_boolean(p_init_msg_list)) THEN
1288 FND_MSG_PUB.initialize;
1289 END IF;
1290
1291 IF (NOT FND_API.Compatible_API_Call
1292 ( p_current_version_number => l_api_version,
1293 p_caller_version_number => p_api_version,
1294 p_api_name => l_api_name,
1295 p_pkg_name => g_pkg_name
1296 )
1297 ) THEN
1298
1299 l_progress := '010';
1300 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1301 END IF;
1302
1303 IF (p_entity_id_tbl IS NULL) THEN
1304 l_progress := '020';
1305
1306 IF (g_fnd_debug = 'Y') THEN
1307 PO_DEBUG.debug_stmt
1308 ( p_log_head => l_log_head,
1309 p_token => l_progress,
1310 p_message => 'entity_id_tbl is empty. quitting'
1311 );
1312 END IF;
1313
1314 RETURN;
1315 END IF;
1316
1317 l_order_tbl.extend(p_entity_id_tbl.COUNT);
1318
1319 FOR i IN 1..l_order_tbl.COUNT LOOP
1320 l_order_tbl(i) := i;
1321 END LOOP;
1322
1323 IF (g_fnd_debug = 'Y') THEN
1324 PO_DEBUG.debug_stmt
1325 ( p_log_head => l_log_head,
1326 p_token => l_progress,
1327 p_message => 'l_order_tbl.count= ' || l_order_tbl.COUNT
1328 );
1329 END IF;
1330
1331 l_progress := '030';
1332
1333 SELECT PO_SESSION_GT_S.nextval
1334 INTO l_seq_id
1335 FROM DUAL;
1336
1337 IF (g_fnd_debug = 'Y') THEN
1338 PO_DEBUG.debug_stmt
1339 ( p_log_head => l_log_head,
1340 p_token => l_progress,
1341 p_message => 'seq_id = ' || l_seq_id
1342 );
1343 END IF;
1344
1345 FORALL i IN 1..p_entity_id_tbl.COUNT
1346 INSERT INTO po_session_gt
1347 ( key,
1348 num1,
1349 num2 )
1350 VALUES
1351 ( l_seq_id,
1352 p_entity_id_tbl(i),
1353 l_order_tbl(i) );
1354
1355 l_progress := '040';
1356
1357 IF (p_entity = 'PO_LINE_LOCATIONS') THEN
1358 l_progress := '050';
1359
1360 --SQL WHAT: Determine whether a SO can be purged or not by
1361 -- Checking the following things on the associated PO:
1362 -- Does PO still exist? - If No, then allow purge
1363 -- IS PO finally closed? - If Yes, then allow purge
1364 -- Is PO Cancelled? - If Yes, then allow purge
1365 -- Otherwise, disallow purge
1366 --SQL WHY: SO should not be purged if the associated PO is not
1367 -- in an unmodifiable stage
1368
1369 SELECT DECODE (PH.po_header_id,
1370 NULL, 'Y', -- po is deleted
1371 DECODE (PH.closed_code,
1372 'FINALLY CLOSED', 'Y', -- po is finally closed
1373 DECODE (PH.cancel_flag,
1374 'Y', 'Y', -- po is cancelled
1375 'N')))
1376 BULK COLLECT INTO x_purge_allowed_tbl
1377 FROM po_session_gt PSG,
1378 po_headers_all PH,
1379 po_line_locations_all PLL
1380 WHERE PSG.key = l_seq_id
1381 AND PSG.num1 = PLL.line_location_id (+)
1382 AND PLL.po_header_id = PH.po_header_id (+)
1383 ORDER BY PSG.num2 asc;
1384
1385 IF (g_fnd_debug = 'Y') THEN
1386 PO_DEBUG.debug_stmt
1387 ( p_log_head => l_log_head,
1388 p_token => l_progress,
1389 p_message => 'x_purge_allowed.COUNT = ' ||
1390 x_purge_allowed_tbl.COUNT
1391 );
1392 END IF;
1393
1394 ELSE
1395 l_progress := '060';
1396
1397 IF (g_fnd_debug = 'Y') THEN
1398 PO_DEBUG.debug_stmt
1399 ( p_log_head => l_log_head,
1400 p_token => l_progress,
1401 p_message => 'Unknown entity: ' || p_entity
1402 );
1403 END IF;
1404
1405 FND_MSG_PUB.add_exc_msg
1406 ( p_pkg_name => g_pkg_name,
1407 p_procedure_name => l_api_name || '.' || l_progress,
1408 p_error_text => 'Unknown entity: ' || p_entity
1409 );
1410
1411 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1412
1413 END IF;
1414
1415 l_progress := '070';
1416 DELETE FROM po_session_gt WHERE key = l_seq_id;
1417
1418 IF (FND_API.to_boolean(p_commit)) THEN
1419
1420 l_progress := '080';
1421 COMMIT;
1422
1423 END IF;
1424
1425 IF (g_fnd_debug = 'Y') THEN
1426 PO_DEBUG.debug_end
1427 ( p_log_head => l_log_head
1428 );
1429 END IF;
1430
1431
1432 EXCEPTION
1433 WHEN OTHERS THEN
1434 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1435 PO_DEBUG.handle_unexp_error ( p_pkg_name => g_pkg_name,
1436 p_proc_name => l_api_name,
1437 p_progress => l_progress );
1438 FND_MSG_PUB.count_and_get
1439 ( p_encoded => 'F',
1440 p_count => x_msg_count,
1441 p_data => x_msg_data
1442 );
1443
1444 END Validate_Purge;
1445
1446 -- <DOC PURGE FPJ END>
1447
1448 -------------------------------------------------------------------------------
1449 --Start of Comments
1450 --Name: get_drop_ship_details
1451 --Pre-reqs:
1452 -- none
1453 --Modifies:
1454 -- None.
1455 --Locks:
1456 -- None.
1457 --Procedure:
1458 -- Returns drop ship info for a given shipment
1459 --Parameters:
1460 --IN:
1461 --p_api_version
1462 -- Initial API version : Expected value is 1.0
1463 --p_line_location_id
1464 -- shipment identifier
1465 --OUT:
1466 --x_order_line_info_rec
1467 -- Record containing the order line info
1468 --x_msg_data
1469 -- Message returned by the API
1470 --x_msg_count
1471 -- Number of error messages
1472 --x_ret_status
1473 -- (a) FND_API.G_RET_STS_SUCCESS - 'S' if successful
1474 -- (b) FND_API.G_RET_STS_ERROR - 'E' if known error occurs
1475 -- (c) FND_API.G_RET_STS_UNEXP_ERROR - 'U' if unexpected error occurs
1476 --Testing:
1477 -- None.
1478 --End of Comments
1479 -------------------------------------------------------------------------------
1480 PROCEDURE get_drop_ship_details(p_api_version IN NUMBER,
1481 p_line_location_id IN NUMBER,
1482 x_customer_name OUT NOCOPY VARCHAR2,
1483 x_customer_contact OUT NOCOPY VARCHAR2,
1484 x_shipping_method OUT NOCOPY VARCHAR2,
1485 x_shipping_instructions OUT NOCOPY VARCHAR2,
1486 x_packing_instructions OUT NOCOPY VARCHAR2,
1487 x_so_num OUT NOCOPY VARCHAR2,
1488 x_so_line_num OUT NOCOPY VARCHAR2,
1489 x_so_status OUT NOCOPY VARCHAR2,
1490 x_ordered_qty OUT NOCOPY NUMBER,
1491 x_shipped_qty OUT NOCOPY NUMBER,
1492 x_customer_po_number OUT NOCOPY VARCHAR2,
1493 x_customer_po_line_num OUT NOCOPY VARCHAR2,
1494 x_customer_po_shipment_num OUT NOCOPY VARCHAR2,
1495 x_customer_item_desc OUT NOCOPY VARCHAR2,
1496 x_msg_data OUT NOCOPY VARCHAR2,
1497 x_msg_count OUT NOCOPY NUMBER,
1498 x_return_status OUT NOCOPY VARCHAR2 ) IS
1499
1500 l_api_name CONSTANT VARCHAR2(30) := 'get_drop_ship_details';
1501 l_api_version CONSTANT NUMBER := 1.0;
1502 l_order_line_info_rec OE_DROP_SHIP_GRP.Order_Line_Info_Rec_Type;
1503
1504 BEGIN
1505 -- Initialise the return status
1506 x_return_status := FND_API.G_RET_STS_SUCCESS;
1507
1508 -- check for API version
1509 IF ( NOT FND_API.compatible_api_call(l_api_version,p_api_version,l_api_name,G_PKG_NAME) )
1510 THEN
1511 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1512 return;
1513 END IF;
1514
1515 -- Call the OM API to get the drop ship info
1516 OE_DROP_SHIP_GRP.get_order_line_info(
1517 P_API_VERSION => 1.0,
1518 P_PO_HEADER_ID => null,
1519 P_PO_LINE_ID => null,
1520 P_PO_LINE_LOCATION_ID => p_line_location_id,
1521 P_PO_RELEASE_ID => null,
1522 P_MODE => 2 ,
1523 X_ORDER_LINE_INFO_REC => l_order_line_info_rec,
1524 X_MSG_DATA => x_msg_data,
1525 X_MSG_COUNT => x_msg_count,
1526 X_RETURN_STATUS => x_return_status );
1527
1528 IF X_RETURN_STATUS = FND_API.G_RET_STS_SUCCESS THEN
1529 x_customer_name := l_order_line_info_rec.SHIP_TO_CUSTOMER_NAME;
1530 x_customer_contact := l_order_line_info_rec.SHIP_TO_CONTACT_NAME;
1531 x_shipping_method := l_order_line_info_rec.SHIPPING_METHOD;
1532 x_shipping_instructions := l_order_line_info_rec.SHIPPING_INSTRUCTIONS;
1533 x_packing_instructions := l_order_line_info_rec.PACKING_INSTRUCTIONS;
1534 x_so_num := l_order_line_info_rec.SALES_ORDER_NUMBER;
1535 x_so_line_num := l_order_line_info_rec.SALES_ORDER_LINE_NUMBER;
1536 x_so_status := l_order_line_info_rec.SALES_ORDER_LINE_STATUS;
1537 x_ordered_qty := l_order_line_info_rec.SALES_ORDER_LINE_ORDERED_QTY;
1538 x_shipped_qty := l_order_line_info_rec.SALES_ORDER_LINE_SHIPPED_QTY;
1539 x_customer_item_desc := l_order_line_info_rec.CUSTOMER_PRODUCT_DESCRIPTION;
1540 x_customer_po_number := l_order_line_info_rec.CUSTOMER_PO_NUMBER;
1541 x_customer_po_line_num := l_order_line_info_rec.CUSTOMER_PO_LINE_NUMBER;
1542 x_customer_po_shipment_num := l_order_line_info_rec.CUSTOMER_PO_SHIPMENT_NUMBER;
1543 END IF;
1544
1545 EXCEPTION
1546 When Others then
1547 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1548 END;
1549
1550 END PO_OM_INTEGRATION_GRP;
[Home] [Help]