1 PACKAGE BODY PO_SHIPMENTS_SV10 as
2 /* $Header: POXPOSAB.pls 120.3 2006/07/27 23:47:08 dreddy noship $*/
3
4 /*===========================================================================
5
6 FUNCTION NAME: val_approval_status
7
8 ===========================================================================*/
9 FUNCTION val_approval_status
10 (X_shipment_id IN NUMBER,
11 X_shipment_type IN VARCHAR2,
12 X_quantity IN NUMBER,
13 X_amount IN NUMBER,
14 X_ship_to_location_id IN NUMBER,
15 X_promised_date IN DATE,
16 X_need_by_date IN DATE,
17 X_shipment_num IN NUMBER,
18 X_last_accept_date IN DATE,
19 X_taxable_flag IN VARCHAR2,
20 X_ship_to_organization_id IN NUMBER,
21 X_price_discount IN NUMBER,
22 X_price_override IN NUMBER,
23 X_tax_code_id IN NUMBER,
24 p_start_date IN DATE, /* <TIMEPHASED FPI> */
25 p_end_date IN DATE, /* <TIMEPHASED FPI> */
26 p_days_early_receipt_allowed IN NUMBER) -- <INBOUND LOGISTICS FPJ>
27 RETURN NUMBER IS
28
29 X_need_to_approve NUMBER := NULL;
30 X_progress VARCHAR2(3) := '';
31
32 X_temp_quantity NUMBER;
33 X_temp_amount NUMBER;
34 X_temp_ship_to_location_id NUMBER;
35 X_temp_promised_date DATE;
36 X_temp_need_by_date DATE;
37 X_temp_shipment_num NUMBER;
38 X_temp_last_accept_date DATE;
39 X_temp_price_discount NUMBER;
40 X_temp_price_override NUMBER;
41 X_temp_ship_to_organization_id NUMBER;
42 X_temp_taxable_flag VARCHAR2(1);
43 l_temp_start_date DATE; /* <TIMEPHASED FPI> */
44 l_temp_end_date DATE; /* <TIMEPHASED FPI> */
45 l_days_early_receipt_allowed NUMBER; -- <INBOUND LOGISTICS FPJ>
46
47 BEGIN
48
49 IF (X_shipment_type = 'STANDARD' OR X_shipment_type = 'PLANNED' OR
50 X_shipment_type = 'SCHEDULED' OR X_shipment_type = 'BLANKET' OR
51 X_shipment_type = 'PRICE BREAK' ) THEN
52
53 X_progress := '010';
54
55 /* <TIMEPHASED FPI> */
56 /*
57 Getting the start_date and end_date values to be used in determining
58 whether the document requires reapproval.
59 */
60 SELECT PLL.quantity,
61 PLL.amount, -- Bug 5409088
62 PLL.ship_to_location_id,
63 PLL.promised_date,
64 PLL.need_by_date,
65 PLL.shipment_num,
66 PLL.last_accept_date,
67 PLL.price_discount,
68 PLL.price_override,
69 PLL.ship_to_organization_id,
70 PLL.taxable_flag,
71 PLL.start_date, /* <TIMEPHASED FPI> */
72 PLL.end_date, /* <TIMEPHASED FPI> */
73 PLL.days_early_receipt_allowed --<INBOUND LOGISTICS FPJ>
74 INTO X_temp_quantity,
75 X_temp_amount, -- Bug 5409088
76 X_temp_ship_to_location_id,
77 X_temp_promised_date,
78 X_temp_need_by_date,
79 X_temp_shipment_num,
80 X_temp_last_accept_date,
81 X_temp_price_discount,
82 X_temp_price_override,
83 X_temp_ship_to_organization_id,
84 X_temp_taxable_flag,
85 l_temp_start_date, /* <TIMEPHASED FPI> */
86 l_temp_end_date, /* <TIMEPHASED FPI> */
87 l_days_early_receipt_allowed --<INBOUND LOGISTICS FPJ>
88 FROM PO_LINE_LOCATIONS PLL
89 WHERE PLL.line_location_id = X_shipment_id;
90
91 -- Bug 5409088 : Added amount check for service lines
92 if ((X_temp_quantity <> X_quantity )
93 OR (X_temp_quantity is NULL
94 AND
95 X_quantity is NOT NULL)
96 OR (X_temp_quantity is NOT NULL
97 AND
98 X_quantity is NULL)
99 OR (X_temp_amount <> X_amount )
100 OR (X_temp_amount is NULL
101 AND
102 X_amount is NOT NULL)
103 OR (X_temp_amount is NOT NULL
104 AND
105 X_amount is NULL)
106 OR (X_temp_ship_to_location_id <> X_ship_to_location_id)
107 OR (X_temp_ship_to_location_id is NULL
108 AND
109 X_ship_to_location_id IS NOT NULL)
110 OR (X_temp_ship_to_location_id IS NOT NULL
111 AND
112 X_ship_to_location_id IS NULL)
113 OR (X_temp_promised_date <> X_promised_date)
114 OR (X_temp_promised_date IS NULL
115 AND
116 X_promised_date IS NOT NULL)
117 OR (X_temp_promised_date IS NOT NULL
118 AND
119 X_promised_date IS NULL)
120 OR (X_temp_need_by_date <> X_need_by_date)
121 OR (X_temp_need_by_date IS NULL
122 AND
123 X_need_by_date IS NOT NULL)
124 OR (X_temp_need_by_date IS NOT NULL
125 AND
126 X_need_by_date IS NULL)
127 OR (X_temp_shipment_num <> X_shipment_num)
128 OR (X_temp_shipment_num IS NULL
129 AND
130 X_shipment_num IS NOT NULL)
131 OR (X_temp_shipment_num IS NOT NULL
132 AND
133 X_shipment_num IS NULL)
134 /* <TIMEPHASED FPI START> */
135 /*
136 If the passed in dates are different that the values in the
137 database, the document needs to go through reapproval
138 */
139 OR (l_temp_start_date <> p_start_date)
140 OR (l_temp_start_date is null AND p_start_date is not null)
141 OR (l_temp_start_date is not null AND p_start_date is null)
142 OR (l_temp_end_date <> p_end_date)
143 OR (l_temp_end_date is null AND p_end_date is not null)
144 OR (l_temp_end_date is not null AND p_end_date is null)
145 /* <TIMEPHASED FPI END> */
146 -- <INBOUND LOGISTICS FPJ START>
147 OR (l_days_early_receipt_allowed <> p_days_early_receipt_allowed)
148 OR (l_days_early_receipt_allowed IS NULL AND p_days_early_receipt_allowed IS NOT NULL)
149 OR (l_days_early_receipt_allowed IS NOT NULL AND p_days_early_receipt_allowed IS NULL)
150 -- <INBOUND LOGISTICS FPJ END>
151 ) then
152
153 /* Unapprove Both the doc and the shipment */
154
155 X_need_to_approve := 2;
156
157 end if;
158
159 if ( (X_temp_last_accept_date <> X_last_accept_date)
160
161 OR (X_temp_last_accept_date IS NULL
162 AND
163 X_last_accept_date IS NOT NULL)
164 OR (X_temp_last_accept_date IS NOT NULL
165 AND
166 X_last_accept_date IS NULL)
167 OR (X_temp_price_discount <> X_price_discount)
168 OR (X_temp_price_discount IS NULL
169 AND
170 X_price_discount IS NOT NULL)
171 OR (X_temp_price_discount IS NOT NULL
172 AND
173 X_price_discount IS NULL)
174 OR (X_temp_price_override <> X_price_override)
175 OR (X_temp_price_override IS NULL
176 AND
177 X_temp_price_override IS NOT NULL)
178 OR (X_temp_price_override IS NOT NULL
179 AND
180 X_price_override IS NULL)
181 OR (X_temp_ship_to_organization_id <> X_ship_to_organization_id)
182 OR (X_temp_ship_to_organization_id IS NULL
183 AND
184 X_ship_to_organization_id IS NOT NULL)
185 OR (X_temp_ship_to_organization_id IS NOT NULL
186 AND
187 X_ship_to_organization_id IS NULL)) then
188
189 /* Unapprove Both the doc and the shipment */
190
191 X_need_to_approve := 2;
192
193 end if;
194
195 --<Bug 5185802> Taxable flag field has been removed from the UI,
196 --removed the condition to unapprove document on change of taxable
197 --flag. x_need_to_approve := 1 does not exist anymore
198
199 if X_need_to_approve is NULL then
200
201 /* Do not Unapprove */
202
203 X_need_to_approve := 0;
204
205 end if;
206
207 end if; /* Shipment TYpe Check */
208
209
210 /*
211 IF ((X_need_to_approve = 1) or
212 (X_need_to_approve = 2)) THEN
213 dbms_output.put_line('status changed = Y');
214
215 ELSE
216 dbms_output.put_line('status changed = N');
217
218 END IF;
219 */
220 return(X_need_to_approve);
221
222 EXCEPTION
223 WHEN NO_DATA_FOUND THEN
224 -- dbms_output.put_line('No data found');
225 return(0);
226 WHEN OTHERS THEN
227 -- dbms_output.put_line('In UPDATE exception');
228 po_message_s.sql_error('val_approval_status', X_progress, sqlcode);
229 raise;
230 END val_approval_status;
231 /*===========================================================================
232
233 FUNCTION NAME: get_rcv_routing_name
234
235 ===========================================================================*/
236 FUNCTION get_rcv_routing_name
237 (X_rcv_routing_id IN NUMBER)
238 return varchar2 is
239 X_rcv_routing_name varchar2(80);
240 X_progress varchar2(3) := null;
241 begin
242 X_progress := '010';
243 select routing_name
244 into X_rcv_routing_name
245 from rcv_routing_headers
246 where routing_header_id = X_rcv_routing_id;
247 return(X_rcv_routing_name);
248 exception
249 WHEN NO_DATA_FOUND THEN
250 -- dbms_output.put_line('No data found');
251 return(null);
252 when others then
253 -- dbms_output.put_line('In get_rcv_routing_name');
254 po_message_s.sql_error('get_rcv_routing_name', X_progress, sqlcode);
255 raise;
256 END get_rcv_routing_name;
257
258
259 /*===========================================================================
260
261 PROCEDURE NAME: get_original_date()
262
263 ===========================================================================*/
264 procedure get_shipment_post_query_info(X_line_location_id IN NUMBER,
265 X_shipment_type IN VARCHAR2,
266 X_item_id IN NUMBER,
267 X_ship_to_org_id IN NUMBER,
268 X_total IN OUT NOCOPY NUMBER,
269 X_total_rtot_db IN OUT NOCOPY NUMBER,
270 X_original_Date IN OUT NOCOPY DATE,
271 X_item_status IN OUT NOCOPY VARCHAR2,
272 x_project_references_enabled IN OUT NOCOPY NUMBER,
273 x_project_control_level IN OUT NOCOPY NUMBER) IS
274
275 X_Progress varchar2(3) := '';
276
277 BEGIN
278
279 X_Progress := '010';
280
281 --
282 -- Get the total from distributions
283 --
284
285 X_total := 0;
286 X_total_rtot_db := 0;
287
288 IF X_shipment_type NOT IN ('BLANKET','SCHEDULED') THEN
289 if X_line_location_id is not null then
290 select nvl(sum(quantity_ordered),0),nvl(sum(quantity_ordered),0)
291 into X_total, X_total_rtot_db
292 from po_distributions pod
293 where pod.line_location_id = X_line_location_id;
294 else
295 X_total := 0;
296 X_total_rtot_db := 0;
297 end if;
298 END IF;
299
300 --
301 -- Get the original commitment date
302 --
303 po_shipments_sv7.get_original_date (X_line_location_id,
304 X_original_date);
305
306 --
307 -- Get the Item_status.
308 --
309 po_items_sv2.get_item_status (X_item_id,
310 X_ship_to_org_id,
311 x_item_status );
312
313 po_core_s4.get_mtl_parameters(X_ship_to_org_id, NULL,
314 x_project_references_enabled,
315 x_project_control_level);
316
317 EXCEPTION
318
319 WHEN no_data_found then
320 null;
321 WHEN others then
322 po_message_s.sql_error('get_shipment_post_query_info',X_Progress,sqlcode);
323 raise;
324
325 END get_shipment_post_query_info;
326
327 /*==============================================================================
328 Procedure get_price_update_flag
329 Description: Decides whether shipment price should be updatable or not.
330 ==============================================================================*/
331 procedure get_price_update_flag(X_line_location_id IN NUMBER,
332 X_expense_accrual_code in varchar2,
333 X_quantity_received in number,
334 X_quantity_billed in number,
335 X_prevent_price_update_flag in out NOCOPY varchar2) is
336 -- <FPJ Retroactive Price START>
337 l_retroactive_update VARCHAR2(30) := 'NEVER';
338 l_archive_mode_rel PO_DOCUMENT_TYPES.archive_external_revision_code%TYPE;
339 l_encumbrance_on VARCHAR2(1);
340 l_current_org_id NUMBER;
341 -- Bug 3231062
342 l_retro_prj_allowed VARCHAR2(1);
343 -- <FPJ Retroactive Price END>
344
345 begin
346
347
348 -- <FPJ Retroactive Price START>
349 l_current_org_id := PO_GA_PVT.get_current_org;
350 l_retroactive_update := PO_RETROACTIVE_PRICING_PVT.Get_Retro_Mode;
351 -- Bug 3565522 : get the archive mode
352 l_archive_mode_rel := PO_DOCUMENT_ARCHIVE_PVT.get_archive_mode(
353 p_doc_type => 'RELEASE',
354 p_doc_subtype => 'BLANKET');
355 IF PO_CORE_S.is_encumbrance_on(p_doc_type => 'RELEASE',
356 p_org_id => l_current_org_id)
357 THEN
358 l_encumbrance_on := 'Y';
359 ELSE
360 l_encumbrance_on := 'N';
361 END IF;
362 -- Bug 3231062
363 l_retro_prj_allowed := PO_RETROACTIVE_PRICING_PVT.Is_Retro_Project_Allowed(
364 p_std_po_price_change => 'N',
365 p_po_line_id => NULL,
366 p_po_line_loc_id => X_line_location_id);
367
368 -- <FPJ Retroactive Price END>
369
370 /* Bug #1916593, uncommented the below sql which will allow the
371 price updation on a Release */
372 SELECT 'Y'
373 INTO x_prevent_price_update_flag
374 FROM DUAL
375 WHERE EXISTS
376 (SELECT 'prevent price udpate'
377 FROM po_distributions
378 WHERE line_location_id = x_line_location_id
379 AND
380 (
381 (
382 (destination_type_code in ('INVENTORY','SHOP FLOOR')
383 OR (destination_type_code='EXPENSE'
384 AND (x_expense_accrual_code = 'RECEIPT'
385 OR X_quantity_billed > 0)))
386 AND (x_quantity_received > 0 OR
387 x_quantity_billed > 0)
388 -- <FPJ Retroactive Price START>
389 AND (l_retroactive_update <> 'ALL_RELEASES' OR
390 (l_retroactive_update = 'ALL_RELEASES' AND
391 l_encumbrance_on = 'Y' ) OR -- Bug 3573266
392 (l_retroactive_update = 'ALL_RELEASES' AND
393 l_archive_mode_rel <> 'APPROVE') OR -- Bug 3565522
394 (l_retroactive_update = 'ALL_RELEASES' AND
395 l_retro_prj_allowed = 'N')) ) -- Bug 3231062
396 -- <FPJ Retroactive Price END>
397 --<Encumbrance FPJ>
398 OR (encumbered_flag = 'Y')
399 )
400 );
401
402 EXCEPTION
403 WHEN NO_DATA_FOUND then null;
404 WHEN OTHERS then raise;
405
406 end get_price_update_flag;
407
408
409 END PO_SHIPMENTS_SV10;
[Home] [Help]