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