1 PACKAGE BODY PO_SHIPMENTS_SV1 as
2 /* $Header: POXPOS1B.pls 120.4 2006/06/16 11:30:50 nipagarw noship $*/
3 /*===========================================================================
4
5 PROCEDURE NAME: get_shipment_num
6
7 ===========================================================================*/
8 PROCEDURE get_shipment_num
9 (X_po_release_id IN NUMBER,
10 X_po_line_id IN NUMBER,
11 X_shipment_num IN OUT NOCOPY NUMBER) IS
12
13 X_progress varchar2(3) := '';
14
15 BEGIN
16
17 -- If this is a release, pass in the release number.
18 -- If this is not a release, pass in the po_line_id
19 IF (X_po_release_id is NOT NULL) THEN
20 SELECT max(PLL.shipment_num) + 1
21 INTO X_shipment_num
22 FROM po_line_locations PLL
23 WHERE PLL.po_release_id = X_po_release_id;
24
25 ELSE
26 SELECT max(PLL.shipment_num) + 1
27 INTO X_shipment_num
28 FROM po_line_locations PLL
29 WHERE PLL.po_line_id = X_po_line_id
30 AND PLL.shipment_type in ('STANDARD', 'PLANNED', 'PRICE BREAK');
31
32 END IF;
33
34 --
35 -- If a shipment number is not selected, this is
36 -- the first shipment and a shipment number of
37 -- one should be returned.
38 --
39 IF (X_shipment_num is null) THEN
40 X_shipment_num := 1;
41 END IF;
42
43
44
45 EXCEPTION
46 when others then
47 po_message_s.sql_error('get_shipment_num', X_progress, sqlcode);
48 raise;
49
50 END get_shipment_num;
51
52 /*===========================================================================
53
54 PROCEDURE NAME: get_planned_ship_info
55
56 ===========================================================================*/
57 PROCEDURE get_planned_ship_info
58 (X_source_shipment_id IN NUMBER,
59 X_set_of_books_id IN NUMBER,
60 X_ship_to_location_code IN OUT NOCOPY VARCHAR2,
61 X_ship_to_location_id IN OUT NOCOPY NUMBER,
62 X_ship_to_org_code IN OUT NOCOPY VARCHAR2,
63 X_ship_to_organization_id IN OUT NOCOPY NUMBER,
64 X_quantity IN OUT NOCOPY NUMBER,
65 X_price_override IN OUT NOCOPY NUMBER,
66 X_promised_date IN OUT NOCOPY DATE,
67 X_need_by_date IN OUT NOCOPY DATE,
68 X_taxable_flag IN OUT NOCOPY VARCHAR2,
69 X_tax_name IN OUT NOCOPY VARCHAR2,
70 X_enforce_ship_to_location IN OUT NOCOPY VARCHAR2,
71 X_allow_substitute_receipts IN OUT NOCOPY VARCHAR2,
72 X_receiving_routing_id IN OUT NOCOPY NUMBER ,
73 X_qty_rcv_tolerance IN OUT NOCOPY NUMBER ,
74 X_qty_rcv_exception_code IN OUT NOCOPY VARCHAR2 ,
75 X_days_early_receipt_allowed IN OUT NOCOPY NUMBER ,
76 X_last_accept_date IN OUT NOCOPY DATE,
77 X_days_late_receipt_allowed IN OUT NOCOPY NUMBER ,
78 X_receipt_days_exception_code IN OUT NOCOPY VARCHAR2 ,
79 X_invoice_close_tolerance IN OUT NOCOPY NUMBER,
80 X_receive_close_tolerance IN OUT NOCOPY NUMBER,
81 X_accrue_on_receipt_flag IN OUT NOCOPY VARCHAR2,
82 X_receipt_required_flag IN OUT NOCOPY VARCHAR2,
83 X_inspection_required_flag IN OUT NOCOPY VARCHAR2) IS
84
85 X_progress varchar2(3) := '';
86
87 -- These two variables are required for the calls to get
88 -- location name and get ship to org code.
89 -- They are out parameters that are not used by this routine
90 X_inv_org_id number ;
91 X_ship_to_org_name varchar2(240);
92
93 -- Bug 4963855. Removed references to tax columns. The calling procedure
94 -- PO_SHIPMENTS_SV5.val_source_ship_num is not called from anywhere
95 CURSOR C is
96 SELECT PLL.ship_to_location_id,
97 PLL.ship_to_organization_id,
98 PLL.quantity,
99 PLL.price_override,
100 PLL.promised_date,
101 PLL.need_by_date,
102 PLL.enforce_ship_to_location_code,
103 PLL.allow_substitute_receipts_flag,
104 PLL.receiving_routing_id ,
105 PLL.qty_rcv_tolerance ,
106 PLL.qty_rcv_exception_code ,
107 PLL.days_early_receipt_allowed ,
108 PLL.last_accept_date,
109 PLL.days_late_receipt_allowed,
110 PLL.receipt_days_exception_code ,
111 PLL.invoice_close_tolerance,
112 PLL.receive_close_tolerance,
113 PLL.accrue_on_receipt_flag,
114 PLL.receipt_required_flag,
115 PLL.inspection_required_flag
116 FROM PO_LINE_LOCATIONS PLL
117 WHERE PLL.line_location_id = X_source_shipment_id;
118
119 BEGIN
120
121 IF (X_source_shipment_id is not null) THEN
122 X_progress := '010';
123 OPEN C;
124 X_progress := '020';
125
126 FETCH C into X_ship_to_location_id,
127 X_ship_to_organization_id,
128 X_quantity,
129 X_price_override,
130 X_promised_date,
131 X_need_by_date,
132 X_enforce_ship_to_location ,
133 X_allow_substitute_receipts ,
134 X_receiving_routing_id ,
135 X_qty_rcv_tolerance ,
136 X_qty_rcv_exception_code ,
137 X_days_early_receipt_allowed ,
138 X_last_accept_date,
139 X_days_late_receipt_allowed ,
140 X_receipt_days_exception_code ,
141 X_invoice_close_tolerance,
142 X_receive_close_tolerance,
143 X_accrue_on_receipt_flag,
144 X_receipt_required_flag,
145 X_inspection_required_flag;
146
147 CLOSE C;
148
149
150 ELSE
151 X_progress := '030';
152 po_message_s.sql_error('get_planned_ship_info', X_progress,
153 sqlcode);
154
155 END IF;
156
157 -- Get the location code based on location id.
158 IF X_ship_to_location_id is not null then
159 po_locations_s.get_loc_attributes(X_ship_to_location_id,
160 X_ship_to_location_code,
161 X_inv_org_id);
162 ELSE
163 X_ship_to_location_code := NULL;
164
165 END IF;
166
167 -- get the ship to org code based on ship to org
168 IF X_ship_to_organization_id is not null then
169 po_orgs_sv.get_org_info(X_ship_to_organization_id,
170 X_set_of_books_id,
171 X_ship_to_org_code ,
172 X_ship_to_org_name);
173 ELSE
174 X_ship_to_org_code := NULL;
175
176 END IF;
177
178
179 EXCEPTION
180 when others then
181 po_message_s.sql_error('get_planned_ship_info', X_progress, sqlcode);
182 raise;
183
184 END get_planned_ship_info;
185
186
187 /*===========================================================================
188
189 FUNCTION NAME: get_sched_released_qty
190
191 ===========================================================================*/
192
193 FUNCTION get_sched_released_qty
194 (X_source_id IN NUMBER,
195 X_entity_level IN VARCHAR2,
196 X_shipment_type IN VARCHAR2) RETURN NUMBER IS
197
198 X_progress VARCHAR2(3) := '000';
199 X_quantity_released NUMBER := '';
200 X_planned_shipment_id NUMBER := '';
201
202 BEGIN
203 X_progress := '010';
204
205 /*
206 ** Note always pass in the source shipment id or the source
207 ** line id.
208 ** Get the quantity released for the scheduled shipments
209 ** When modifying this entity type:
210 ** Planned PO Shipment = Where line_location_id = source_id
211 ** Planned PO Line = Where po_line_id = source_id
212 */
213 IF (X_entity_level = 'LINE') THEN
214
215 X_progress := '020';
216
217 SELECT sum(PLL.quantity - nvl(PLL.quantity_cancelled,0))
218 INTO X_quantity_released
219 FROM po_line_locations PLL
220 WHERE PLL.po_line_id = X_source_id
221 AND PLL.shipment_type = 'SCHEDULED' ;
222
223 ELSIF (X_entity_level = 'SHIPMENT') THEN /* Entity level is SHIPMENT */
224
225 /*
226 ** Get the quantity released for all releases against the
227 ** planned shipment
228 */
229 X_progress := '030';
230
231 SELECT sum(PLL.quantity - nvl(PLL.quantity_cancelled,0))
232 INTO X_quantity_released
233 FROM po_line_locations PLL
234 WHERE PLL.source_shipment_id = X_source_id
235 AND PLL.shipment_type = 'SCHEDULED' ;
236
237 -- Bug 3840143: Added qty released calculation for distributions
238 ELSIF (X_entity_level = 'DISTRIBUTION') THEN /* Entity level is DISTRIBUTION */
239
240 /*
241 ** Get the quantity released for all releases against the
242 ** planned distributions
243 */
244 X_progress := '040';
245
246 SELECT nvl(sum(nvl(POD.quantity_ordered,0)- nvl(POD.quantity_cancelled,0)),0)
247 INTO X_quantity_released
248 FROM po_distributions POD
249 WHERE POD.source_distribution_id = X_source_id ;
250
251 END IF;
252
253 RETURN(X_quantity_released);
254
255 EXCEPTION
256 when others then
257 po_message_s.sql_error('get_sched_released_qty', X_progress, sqlcode);
258 raise;
259 END get_sched_released_qty;
260
261
262 /*===========================================================================
263
264 FUNCTION NAME: val_sched_released_qty
265
266 DEBUG. review this code.
267 ===========================================================================*/
268 FUNCTION val_sched_released_qty
269 (X_entity_level IN VARCHAR2,
270 X_line_id IN NUMBER,
271 X_line_location_id IN NUMBER,
272 X_shipment_type IN VARCHAR2,
273 X_quantity_ordered IN NUMBER,
274 X_source_shipment_id IN NUMBER ) RETURN BOOLEAN IS
275
276 X_progress VARCHAR2(3) := '';
277 X_quantity_ordered_new NUMBER := '';
278 X_quantity_released NUMBER := '';
279 X_planned_shipment_id NUMBER;
280
281 BEGIN
282
283 /*
284 ** We should get the current quantity ordered for the
285 ** planned purchase order shipment.
286 ** We should also get total quantity released against the
287 ** planned purchase order shipment.
288 */
289
290
291 IF (X_entity_level = 'SHIPMENT') THEN
292
293 -- This is called when modiyfing a scheduled shipment quantity.
294 -- DEBUG. do we ever call this for schedule releases or only
295 --- when modifying a planned po? Messages are wrong for a release.
296 IF (X_shipment_type = 'SCHEDULED') THEN
297
298 X_progress := '010';
299
300 -- get quantity ordered on planned shipment.
301 X_quantity_ordered_new := po_shipments_sv3.get_planned_qty_ordered(X_source_shipment_id,
302 'PLANNED');
303
304 --dbms_output.put_line ('Qty ord (not null)= '||X_quantity_ordered_new);
305
306 -- get the quantity released to date against the planned
307 -- purchase order shipment that is associated with
308 -- the release shipment that we are modifying.
309 X_quantity_released := po_shipments_sv1.get_sched_released_qty
310 (X_source_shipment_id,
311 'SHIPMENT',
312 'SCHEDULED');
313
314 -- This is called when modifying a planned purchase order shipment.
315 ELSIF (X_shipment_type = 'PLANNED') THEN
316 X_quantity_ordered_new := X_quantity_ordered;
317
318 -- get the quantity released to date against the planned
319 -- purchase order shipment that we are currently modifying.
320 X_quantity_released := po_shipments_sv1.get_sched_released_qty
321 (X_line_location_id,
322 'SHIPMENT',
323 'SCHEDULED');
324
325 END IF;
326
327 ELSE /* Entity Level = Line */
328
329 -- this is called when modifying a planned purchase order line.
330 X_quantity_ordered_new := X_quantity_ordered;
331
332 -- get the quantity released to date against the planned
333 -- purchase order line.
334 X_quantity_released := po_shipments_sv1.get_sched_released_qty
335 (X_line_id,
336 'LINE',
337 X_shipment_type);
338
339 --dbms_output.put_line ('Qty released(line)= '||X_quantity_released);
340
341 END IF;
342
343
344 /*
345 ** If the quantity ordered is less than the quantity
346 ** released, we need to display a message to the user
347 ** that they cannot reduce the quantity ordered to
348 ** less than what has already be released. The message
349 ** is displayed on the client side.
350 */
351 IF (X_quantity_ordered_new > X_quantity_released OR
352 X_quantity_released is NULL) THEN
353 --dbms_output.put_line ('Returned TRUE');
354 RETURN(TRUE);
355
356 ELSIF (X_quantity_ordered_new = X_quantity_released) THEN
357 --dbms_output.put_line ('Returned FALSE');
358 po_message_s.app_error('PO_PO_ALL_SHIP_RELEASED');
359
360 RETURN(FALSE);
361
362 ELSE
363 /* If the Quantity Ordered is less than what has been
364 ** already released display an appropriate message */
365 po_message_s.app_error('PO_PO_QTY_EXCEEDS_UNREL');
366 RETURN(FALSE);
367
368 END IF;
369
370
371 EXCEPTION
372 when others then
373 --dbms_output.put_line('In VAL exception');
374 po_message_s.sql_error('val_sched_released_qty', X_progress, sqlcode);
375 raise;
376 END val_sched_released_qty;
377
378 END PO_SHIPMENTS_SV1;
[Home] [Help]