1 PACKAGE BODY PO_SHIPMENTS_SV2 as
2 /* $Header: POXPOS2B.pls 115.8 2002/11/23 02:43:03 sbull ship $*/
3
4 -- Read the profile option that enables/disables the debug log
5 g_po_pdoi_write_to_file VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_PDOI_WRITE_TO_FILE'),'N');
6
7 /*===========================================================================
8
9 FUNCTION NAME: get_number_shipments
10
11 ===========================================================================*/
12 FUNCTION get_number_shipments
13 (X_po_line_id IN NUMBER,
14 X_shipment_type IN VARCHAR2) RETURN NUMBER IS
15
16 X_progress VARCHAR2(3) := '';
17 X_number_shipments NUMBER := '';
18
19 BEGIN
20
21 X_progress := '010';
22
23 SELECT count(PLL.line_location_id)
24 INTO X_number_shipments
25 FROM po_line_locations PLL
26 WHERE PLL.po_line_id = X_po_line_id
27 AND PLL.shipment_type = X_shipment_type;
28
29 IF (g_po_pdoi_write_to_file = 'Y') THEN
30 po_debug.put_line ('Number Shipments = '||X_number_shipments);
31 END IF;
32
33 RETURN(X_number_shipments);
34
35 EXCEPTION
36 when others then
37 IF (g_po_pdoi_write_to_file = 'Y') THEN
38 po_debug.put_line('In exception');
39 END IF;
40 po_message_s.sql_error('get_number_shipments', X_progress, sqlcode);
41 raise;
42 END get_number_shipments;
43
44 /*===========================================================================
45
46 FUNCTION NAME: val_release_shipments
47
48 ===========================================================================*/
49 FUNCTION val_release_shipments
50 (X_po_line_id IN NUMBER,
51 X_shipment_type IN VARCHAR2) RETURN BOOLEAN IS
52
53 X_progress VARCHAR2(3) := '';
54 X_number_shipments NUMBER := '';
55
56 BEGIN
57
58 X_progress := '010';
59
60 /*
61 ** To get the number of shipments for a planned line,
62 ** pass in a type of SCHEDULED.
63 ** To get the number of shipments for a blanket line,
64 ** pass in a type of BLANKET.
65 */
66 X_number_shipments := get_number_shipments(X_po_line_id,
67 X_shipment_type);
68
69 If (X_number_shipments is null OR
70 X_number_shipments = 0) THEN
71 IF (g_po_pdoi_write_to_file = 'Y') THEN
72 po_debug.put_line ('Returned FALSE - No Shipments');
73 END IF;
74 return(FALSE);
75 ELSE
76 IF (g_po_pdoi_write_to_file = 'Y') THEN
77 po_debug.put_line ('Returned TRUE - Shipments');
78 END IF;
79 return(TRUE);
80 END IF;
81
82 EXCEPTION
83 when others then
84 IF (g_po_pdoi_write_to_file = 'Y') THEN
85 po_debug.put_line('In exception');
86 END IF;
87 po_message_s.sql_error('val_release_shipments', X_progress, sqlcode);
88 raise;
89 END val_release_shipments;
90
91
92
93 /*===========================================================================
94
95 PROCEDURE NAME: get_shipment_status
96
97 ===========================================================================*/
98 PROCEDURE get_shipment_status
99 (X_po_line_id IN NUMBER,
100 X_shipment_type IN VARCHAR2,
101 X_line_location_id IN NUMBER,
102 X_approved_flag IN OUT NOCOPY VARCHAR2,
103 X_encumbered_flag IN OUT NOCOPY VARCHAR2,
104 X_closed_code IN OUT NOCOPY VARCHAR2,
105 X_cancelled_flag IN OUT NOCOPY VARCHAR2) IS
106
107 X_progress VARCHAR2(3) := '';
108
109 CURSOR C is
110 SELECT nvl(PLL.approved_flag,'N'),
111 nvl(PLL.encumbered_flag,'N'),
112 nvl(PLL.closed_code,'OPEN'),
113 nvl(PLL.cancel_flag,'N')
114 FROM po_line_locations PLL
115 WHERE PLL.line_location_id = X_line_location_id
116 AND PLL.shipment_type = X_shipment_type;
117
118 BEGIN
119
120 IF (g_po_pdoi_write_to_file = 'Y') THEN
121 po_debug.put_line('Before open cursor');
122 END IF;
123
124 if (X_line_location_id is not null) then
125 if (X_shipment_type is not null) then
126
127 X_progress := '010';
128 OPEN C;
129 X_progress := '020';
130
131 FETCH C into X_approved_flag,
132 X_encumbered_flag,
133 X_closed_code,
134 X_cancelled_flag;
135
136 CLOSE C;
137
138 IF (g_po_pdoi_write_to_file = 'Y') THEN
139 po_debug.put_line('Line Location Id : '|| X_line_location_id);
140 po_debug.put_line('Approved Flag = '||X_approved_flag);
141 po_debug.put_line('Encumbered Flag = '||X_encumbered_flag);
142 po_debug.put_line('Closed Code = '||X_closed_code);
143 po_debug.put_line('Canclled_flag = '||X_cancelled_flag);
144 END IF;
145
146 else
147 X_progress := '025';
148 po_message_s.sql_error('get_shipment_status', X_progress, sqlcode);
149
150 end if;
151
152 else
153 X_progress := '030';
154 po_message_s.sql_error('get_shipment_status', X_progress, sqlcode);
155
156 end if;
157
158 EXCEPTION
159 when others then
160 IF (g_po_pdoi_write_to_file = 'Y') THEN
161 po_debug.put_line('In exception');
162 END IF;
163 po_message_s.sql_error('get_shipment_status', X_progress, sqlcode);
164 raise;
165 END get_shipment_status;
166
167
168
169
170 /*===========================================================================
171
172 FUNCTION NAME: update_shipment_qty
173
174 ===========================================================================*/
175 FUNCTION update_shipment_qty
176 (X_line_location_id IN NUMBER,
177 X_shipment_type IN VARCHAR2,
178 X_line_quantity IN NUMBER) RETURN BOOLEAN IS
179
180 X_progress VARCHAR2(3) := '';
181 X_login_id NUMBER := '';
182 X_last_updated_by NUMBER := '';
183
184 BEGIN
185
186 X_progress := '010';
187
188 IF (g_po_pdoi_write_to_file = 'Y') THEN
189 po_debug.put_line('Before get who information');
190 END IF;
191 /*
192 ** Get the who information
193 */
194 X_login_id := fnd_global.login_id;
195 X_last_updated_by := fnd_global.user_id;
196
197 IF (g_po_pdoi_write_to_file = 'Y') THEN
198 po_debug.put_line('Before update statement');
199 END IF;
200 /*
201 ** Update the purchase order shipment quantity to the lines qty.
202 */
203 UPDATE PO_LINE_LOCATIONS
204 SET quantity = X_line_quantity,
205 approved_flag = decode(approved_flag, 'N', 'N', 'R'),
206 last_update_date = sysdate,
207 last_updated_by = X_last_updated_by,
208 last_update_login = X_login_id
209 WHERE line_location_id = X_line_location_id
210 AND shipment_type = X_shipment_type;
211
212 RETURN(TRUE);
213
214 EXCEPTION
215 WHEN NO_DATA_FOUND THEN
216 null;
217 WHEN OTHERS THEN
218 IF (g_po_pdoi_write_to_file = 'Y') THEN
219 po_debug.put_line('In UPDATE exception');
220 END IF;
221 po_message_s.sql_error('update_shipment_qty', X_progress, sqlcode);
222 raise;
223 END update_shipment_qty;
224
225
226 /*===========================================================================
227
228 FUNCTION NAME: val_ship_qty
229
230 Note: you should only call this routine if your
231 shipment type is standard or planned.
232 This way you prevent a server call.
233 I am checking it in the server side just
234 in case it is called.
235 Issues: Should you be able to update the quantity
236 on a shipment if is finally closed?
237 DEBUG.
238
239 ===========================================================================*/
240 FUNCTION val_ship_qty
241 (X_po_line_id IN NUMBER,
242 X_shipment_type IN VARCHAR2,
243 X_line_quantity IN NUMBER) RETURN BOOLEAN IS
244
245 X_progress VARCHAR2(3) := '';
246 X_approved_flag VARCHAR2(1) := '';
247 X_encumbered_flag VARCHAR2(1) := '';
248 X_closed_code VARCHAR2(25) := '';
249 X_cancelled_flag VARCHAR2(1) := '';
250 X_line_location_id NUMBER := '';
251 X_number_shipments NUMBER := '';
252
253
254 BEGIN
255
256 /*
257 ** If this is a standard or planned purchase order, continue
258 ** with checks. Otherwise, we should not update the price.
259 */
260 IF (X_shipment_type = 'STANDARD' OR X_shipment_type = 'PLANNED') THEN
261 null;
262 ELSE
263 return(FALSE);
264 END IF;
265
266 /*
267 ** Get the number of shipments associated with the purchase order line.
268 */
269 X_number_shipments := po_shipments_sv2.get_number_shipments(X_po_line_id,
270 X_shipment_type);
271
272 IF (g_po_pdoi_write_to_file = 'Y') THEN
273 po_debug.put_line('X_number_shipments = '||X_number_shipments);
274 END IF;
275
276 /*
277 ** If the number of shipment is 1, then continue. Otherwise,
278 ** we should not update the quantity on the shipment.
279 */
280 IF (X_number_shipments = 1) THEN
281
282 X_line_location_id := po_shipments_sv3.get_line_location_id(X_po_line_id,
283 X_shipment_type);
284
285 IF (g_po_pdoi_write_to_file = 'Y') THEN
286 po_debug.put_line('X_line_location_id = '||X_line_location_id);
287 END IF;
288
289 /*
290 ** Get the line_location_id and status of the single shipment.
291 */
292 po_shipments_sv2.get_shipment_status (X_po_line_id,
293 X_shipment_type,
294 X_line_location_id,
295 X_approved_flag,
296 X_encumbered_flag,
297 X_closed_code,
298 X_cancelled_flag);
299
300 IF (g_po_pdoi_write_to_file = 'Y') THEN
301 po_debug.put_line('X_encumbered_flag = '||X_encumbered_flag);
302 po_debug.put_line('X_cancelled_flag = '||X_cancelled_flag);
303 END IF;
304
305 /*
306 ** Allow the quantity to be updated if the shipment is
307 ** not encumbered, cancelled or finally closed..
308 */
309 IF ( (X_encumbered_flag = 'N') AND
310 (X_cancelled_flag = 'N') AND
311 (X_closed_code <> 'FINALLY CLOSED') ) THEN
312
313 IF po_shipments_sv2.update_shipment_qty (X_line_location_id,
314 X_shipment_type,
315 X_line_quantity) THEN
316 RETURN(TRUE);
317 ELSE
318 RETURN(FALSE);
319 END IF;
320
321 ELSE /* The Shipment is encumbered/cancelled/finally closed. */
322 RETURN(FALSE);
323 END IF;
324
325 ELSE /* Number of Shipments != 1 */
326 RETURN(FALSE);
327 END IF;
328
329 EXCEPTION
330 when others then
331 IF (g_po_pdoi_write_to_file = 'Y') THEN
332 po_debug.put_line('In exception');
333 END IF;
334 po_message_s.sql_error('val_ship_qty', X_progress, sqlcode);
335 raise;
336 END val_ship_qty;
337
338
339
340 /*===========================================================================
341
342 FUNCTION NAME: val_ship_price
343
344 Note: you should only call this routine for standard
345 and planned purchase orders.
346 ===========================================================================*/
347 FUNCTION val_ship_price
348 (X_po_line_id IN NUMBER,
349 X_shipment_type IN VARCHAR2,
350 X_unit_price IN NUMBER ) RETURN BOOLEAN IS
351
352 X_progress VARCHAR2(3) := '';
353
354 BEGIN
355
356 /*
357 ** If this is a standard or planned purchase order, continue
358 ** with checks. Otherwise, we should not update the price.
359 */
360 IF (X_shipment_type = 'STANDARD' OR X_shipment_type = 'PLANNED') THEN
361 null;
362 IF (g_po_pdoi_write_to_file = 'Y') THEN
363 po_debug.put_line('It is STANDARD or PLANNED');
364 END IF;
365 ELSE
366 IF (g_po_pdoi_write_to_file = 'Y') THEN
367 po_debug.put_line('It is not STANDARD or PLANNED');
368 END IF;
369 return(FALSE);
370 END IF;
371
372 IF (g_po_pdoi_write_to_file = 'Y') THEN
373 po_debug.put_line('Before If');
374 END IF;
375 IF po_shipments_sv2.update_shipment_price (X_po_line_id,
376 X_shipment_type,
377 X_unit_price) THEN
378 IF (g_po_pdoi_write_to_file = 'Y') THEN
379 po_debug.put_line('Returned TRUE');
380 END IF;
381 return(TRUE);
382 ELSE
383 IF (g_po_pdoi_write_to_file = 'Y') THEN
384 po_debug.put_line('Returned FALSE');
385 END IF;
386 return(FALSE);
387 END IF;
388 IF (g_po_pdoi_write_to_file = 'Y') THEN
389 po_debug.put_line('After If');
390 END IF;
391
392
393 EXCEPTION
394 when others then
395 IF (g_po_pdoi_write_to_file = 'Y') THEN
396 po_debug.put_line('In VAL exception');
397 END IF;
398 po_message_s.sql_error('val_ship_price', X_progress, sqlcode);
399 raise;
400 END val_ship_price;
401
402
403 /*===========================================================================
404
405 FUNCTION NAME: update_shipment_price
406
407 DEBUG: Should we allow you to update the price
408 on a finally closed shipment?
409
410 ===========================================================================*/
411 FUNCTION update_shipment_price
412 (X_po_line_id IN NUMBER,
413 X_shipment_type IN VARCHAR2,
414 X_unit_price IN NUMBER) RETURN BOOLEAN IS
415
416 X_progress VARCHAR2(3) := '';
417 X_login_id NUMBER := '';
418 X_last_updated_by NUMBER := '';
419
420 BEGIN
421
422 X_progress := '010';
423
424 IF (g_po_pdoi_write_to_file = 'Y') THEN
425 po_debug.put_line('Before get who information');
426 END IF;
427 /*
428 ** Get the who information
429 */
430 X_login_id := fnd_global.login_id;
431 X_last_updated_by := fnd_global.user_id;
432
433 IF (g_po_pdoi_write_to_file = 'Y') THEN
434 po_debug.put_line('Before update statement');
435 END IF;
436 /*
437 ** Update the purchase order shipment price to the lines unit
438 ** price for all shipments that are not cancelled.
439 */
440 UPDATE PO_LINE_LOCATIONS
441 SET price_override = X_unit_price,
442 approved_flag = decode(approved_flag, 'N', 'N', 'R'),
443 last_update_date = sysdate,
444 last_updated_by = X_last_updated_by,
445 last_update_login = X_login_id
446 WHERE po_line_id = X_po_line_id
447 AND nvl(cancel_flag,'N') = 'N'
448 AND nvl(closed_code,'OPEN') <> 'FINALLY CLOSED'
449 AND shipment_type in ('STANDARD','PLANNED');
450
451 RETURN(TRUE);
452
453 EXCEPTION
454 WHEN NO_DATA_FOUND THEN
455 null;
456 WHEN OTHERS THEN
457 IF (g_po_pdoi_write_to_file = 'Y') THEN
458 po_debug.put_line('In UPDATE exception');
459 END IF;
460 po_message_s.sql_error('update_shipment_price', X_progress, sqlcode);
461 raise;
462 END update_shipment_price;
463
464 /*===========================================================================
465 --togeorge 05/18/2001
466 --Bug# 1712919
467 PROCEDURE NAME: get_drop_ship_cust_locations
468 On enter po and release forms ship to location code
469 is required column. Since hz_locations does not
470 store location_code and corresponding location
471 code is null in hr_locations table, when a drop
472 ship PO/Rel is queried in the form the user wont
473 be allowed to save the records. So this procedure
474 gets the concatenated address1 and city from
475 hz_locations table for this specific condition.
476 Called from POXPOPOS.pld(post_query)
477
478 ===========================================================================*/
479 PROCEDURE get_drop_ship_cust_locations
480 (x_ship_to_location_id IN NUMBER,
481 x_ship_to_location_code IN OUT NOCOPY VARCHAR2) IS
482
483 X_progress VARCHAR2(3) := '';
484
485 BEGIN
486 X_progress := '010';
487
488 --Bug# 1852364
489 --togeorge 07/27/2001
490 --Changed hr_locations to hz_locations in the following query as hr team
491 --is going to remove union to hz_locations from hr_locations view.
492 --SELECT nvl(location_code,substr(rtrim(address_line_1)||'-'||rtrim(town_or_city),1,20)) ship_to_location_code
493 /* Bug 2313980 fixed. replaced below 'substr' with 'substrb' to take care of
494 multibyte (like japanese) scenarios.
495 */
496 SELECT (substrb(rtrim(address1)||'-'||rtrim(city),1,20)) ship_to_location_code
497 INTO x_ship_to_location_code
498 FROM hz_locations
499 WHERE location_id = x_ship_to_location_id;
500
501 EXCEPTION
502 WHEN OTHERS THEN
503 null;
504 END get_drop_ship_cust_locations;
505
506 END PO_SHIPMENTS_SV2;
[Home] [Help]