[Home] [Help]
PACKAGE BODY: APPS.PO_SHIPMENTS_SV3
Source
4 /*===========================================================================
1 PACKAGE BODY PO_SHIPMENTS_SV3 as
2 /* $Header: POXPOS3B.pls 120.4 2005/07/01 03:06:19 manram noship $*/
3
5
6 FUNCTION NAME: get_line_location_id
7
8 ===========================================================================*/
9 FUNCTION get_line_location_id
10 (X_po_line_id IN NUMBER,
11 X_shipment_type IN VARCHAR2) RETURN NUMBER IS
12
13 X_progress VARCHAR2(3) := '';
14 X_line_location_id NUMBER := '';
15
16 BEGIN
17
18 X_progress := '010';
19
20 -- Note that this routine is only called if there is only one
21 -- shipment.
22 SELECT line_location_id
23 INTO X_line_location_id
24 FROM po_line_locations PLL
25 WHERE PLL.po_line_id = X_po_line_id
26 AND PLL.shipment_type = X_shipment_type;
27
28 -- togeorge 10/26/2000 commented out due to the problems while arcs in.
29 --dbms_output.put_line ('Line Location Id = '||X_line_location_id);
30
31 RETURN(X_line_location_id);
32
33 EXCEPTION
34 when others then
35 -- togeorge 10/26/2000 commented out due to the problems while arcs in.
36 -- dbms_output.put_line('In exception');
37 po_message_s.sql_error('get_line_location_id', X_progress, sqlcode);
38 raise;
39 END get_line_location_id;
40
41 /*===========================================================================
42
43 PROCEDURE NAME: get_po_line_location_id()
44
45 ===========================================================================*/
46
47 PROCEDURE get_po_line_location_id
51
48 (X_po_line_location_id_record IN OUT NOCOPY rcv_shipment_line_sv.po_line_location_id_rtype) IS
49
50 BEGIN
52 SELECT max(line_location_id) into x_po_line_location_id_record.po_line_location_id
53 from po_lines_all pol, --<R12.MOAC>
54 po_line_locations poll
55 where pol.po_line_id = poll.po_line_id
56 and NVL(X_po_line_location_id_record.item_id, pol.item_id) = pol.item_id
57 and NVL(X_po_line_location_id_record.po_line_id, pol.po_line_id) = pol.po_line_id
58 and pol.po_header_id = X_po_line_location_id_record.po_header_id
59 order by nvl(poll.promised_date, poll.need_by_date);
60
61 if (x_po_line_location_id_record.po_line_location_id is null) then
62 x_po_line_location_id_record.error_record.error_status := 'F';
63 x_po_line_location_id_record.error_record.error_message := 'RCV_ITEM_PO_LINE_LOCATION_ID';
64 end if;
65
66 exception
67 when others then
68 x_po_line_location_id_record.error_record.error_status := 'U';
69
70 END get_po_line_location_id;
71
72 /*===========================================================================
73
74 FUNCTION NAME: get_planned_qty_ordered
75
76 ===========================================================================*/
77
78 -- DEBUG. Not currently being called from source line number.
79 -- This is a bug.
80 FUNCTION get_planned_qty_ordered
81 (X_po_line_id IN NUMBER,
82 X_shipment_type IN VARCHAR2) RETURN NUMBER IS
83
84 /* DEBUG Why pass X_shipment_type if this is always going to be
85 ** PLANNED? */
86
87 X_progress VARCHAR2(3) := '';
88 X_quantity_ordered NUMBER := '';
89 X_planned_shipment_id NUMBER := '';
90
91 BEGIN
92 X_progress := '010';
93
94 -- Get the sum of the quantity of all shipments.
95 SELECT sum(PLL.quantity - nvl(PLL.quantity_cancelled,0))
96 INTO X_quantity_ordered
97 FROM po_line_locations PLL
98 WHERE PLL.po_line_id = X_po_line_id
99 AND PLL.shipment_type = 'PLANNED' ;
100
101 -- togeorge 10/26/2000 commented out due to the problems while arcs in.
102 -- dbms_output.put_line ('Quantity Ordered (ord - can) = '||X_quantity_ordered);
103
104 RETURN(X_quantity_ordered);
105
106 EXCEPTION
107 when others then
108 -- togeorge 10/26/2000 commented out due to the problems while arcs in.
109 -- dbms_output.put_line('In exception');
110 po_message_s.sql_error('get_planned_qty_ordered', X_progress, sqlcode);
111 raise;
112 END get_planned_qty_ordered;
113
114 /*===========================================================================
115
116 PROCEDURE NAME: insert_rel_shipment
117
118 ===========================================================================*/
119 FUNCTION insert_rel_shipment
120 (X_Rowid IN OUT NOCOPY VARCHAR2,
121 X_Line_Location_Id IN OUT NOCOPY NUMBER,
122 X_Last_Update_Date DATE,
123 X_Last_Updated_By NUMBER,
124 X_Po_Header_Id NUMBER,
125 X_Po_Line_Id NUMBER,
126 X_Last_Update_Login NUMBER,
127 X_Creation_Date DATE,
128 X_Created_By NUMBER,
129 X_Quantity NUMBER,
130 X_Quantity_Received NUMBER,
131 X_Quantity_Accepted NUMBER,
132 X_Quantity_Rejected NUMBER,
133 X_Quantity_Billed NUMBER,
134 X_Quantity_Cancelled NUMBER,
135 X_Unit_Meas_Lookup_Code VARCHAR2,
136 X_Po_Release_Id NUMBER,
137 X_Ship_To_Location_Id NUMBER,
138 X_Ship_Via_Lookup_Code VARCHAR2,
139 X_Need_By_Date DATE,
140 X_Promised_Date DATE,
141 X_Last_Accept_Date DATE,
142 X_Price_Override NUMBER,
143 X_Encumbered_Flag VARCHAR2,
144 X_Encumbered_Date DATE,
145 X_Fob_Lookup_Code VARCHAR2,
146 X_Freight_Terms_Lookup_Code VARCHAR2,
147 X_Taxable_Flag VARCHAR2,
148 X_Tax_Code_Id NUMBER,
149 X_Tax_User_Override_Flag VARCHAR2,
150 X_Calculate_Tax_Flag VARCHAR2,
151 X_From_Header_Id NUMBER,
152 X_From_Line_Id NUMBER,
153 X_From_Line_Location_Id NUMBER,
154 X_Start_Date DATE,
155 X_End_Date DATE,
156 X_Lead_Time NUMBER,
157 X_Lead_Time_Unit VARCHAR2,
158 X_Price_Discount NUMBER,
159 X_Terms_Id NUMBER,
160 X_Approved_Flag VARCHAR2,
161 X_Approved_Date DATE,
162 X_Closed_Flag VARCHAR2,
163 X_Cancel_Flag VARCHAR2,
167 X_Firm_Status_Lookup_Code VARCHAR2,
164 X_Cancelled_By NUMBER,
165 X_Cancel_Date DATE,
166 X_Cancel_Reason VARCHAR2,
168 X_Attribute_Category VARCHAR2,
169 X_Attribute1 VARCHAR2,
170 X_Attribute2 VARCHAR2,
171 X_Attribute3 VARCHAR2,
172 X_Attribute4 VARCHAR2,
173 X_Attribute5 VARCHAR2,
174 X_Attribute6 VARCHAR2,
175 X_Attribute7 VARCHAR2,
176 X_Attribute8 VARCHAR2,
177 X_Attribute9 VARCHAR2,
178 X_Attribute10 VARCHAR2,
179 X_Attribute11 VARCHAR2,
180 X_Attribute12 VARCHAR2,
181 X_Attribute13 VARCHAR2,
182 X_Attribute14 VARCHAR2,
183 X_Attribute15 VARCHAR2,
184 X_Inspection_Required_Flag VARCHAR2,
185 X_Receipt_Required_Flag VARCHAR2,
186 X_Qty_Rcv_Tolerance NUMBER,
187 X_Qty_Rcv_Exception_Code VARCHAR2,
188 X_Enforce_Ship_To_Location VARCHAR2,
189 X_Allow_Substitute_Receipts VARCHAR2,
190 X_Days_Early_Receipt_Allowed NUMBER,
191 X_Days_Late_Receipt_Allowed NUMBER,
192 X_Receipt_Days_Exception_Code VARCHAR2,
193 X_Invoice_Close_Tolerance NUMBER,
194 X_Receive_Close_Tolerance NUMBER,
195 X_Ship_To_Organization_Id NUMBER,
196 X_Shipment_Num NUMBER,
197 X_Source_Shipment_Id NUMBER,
198 X_Shipment_Type VARCHAR2,
199 X_Closed_Code VARCHAR2,
200 X_Ussgl_Transaction_Code VARCHAR2,
201 X_Government_Context VARCHAR2,
202 X_Receiving_Routing_Id NUMBER,
203 X_Accrue_On_Receipt_Flag VARCHAR2,
204 X_Closed_Reason VARCHAR2,
205 X_Closed_Date DATE,
206 X_Closed_By NUMBER,
207 X_Global_Attribute_Category VARCHAR2,
208 X_Global_Attribute1 VARCHAR2,
209 X_Global_Attribute2 VARCHAR2,
210 X_Global_Attribute3 VARCHAR2,
211 X_Global_Attribute4 VARCHAR2,
212 X_Global_Attribute5 VARCHAR2,
213 X_Global_Attribute6 VARCHAR2,
214 X_Global_Attribute7 VARCHAR2,
215 X_Global_Attribute8 VARCHAR2,
216 X_Global_Attribute9 VARCHAR2,
217 X_Global_Attribute10 VARCHAR2,
218 X_Global_Attribute11 VARCHAR2,
219 X_Global_Attribute12 VARCHAR2,
220 X_Global_Attribute13 VARCHAR2,
221 X_Global_Attribute14 VARCHAR2,
222 X_Global_Attribute15 VARCHAR2,
223 X_Global_Attribute16 VARCHAR2,
224 X_Global_Attribute17 VARCHAR2,
225 X_Global_Attribute18 VARCHAR2,
226 X_Global_Attribute19 VARCHAR2,
227 X_Global_Attribute20 VARCHAR2,
228 X_Invoice_Match_Option VARCHAR2, --bgu, Dec. 7, 98
229 X_Country_of_Origin_Code VARCHAR2,
230 --togeorge 10/12/2000
231 --Bug# 1433282
232 --added note to receiver
233 X_note_to_receiver VARCHAR2,
234 -- Mahesh Chandak(GML) Add 7 process related fields.Bug# 1548597
235 -- start of 1548597
236 X_Secondary_Unit_Of_Measure VARCHAR2 default null,
237 X_Secondary_Quantity NUMBER default null ,
238 X_Preferred_Grade VARCHAR2 default null,
239 X_Secondary_Quantity_Received NUMBER default null,
240 X_Secondary_Quantity_Accepted NUMBER default null,
241 X_Secondary_Quantity_Rejected NUMBER default null,
242 X_Secondary_Quantity_Cancelled NUMBER default null,
243 -- end of 1548597
244 X_amount NUMBER default null, -- <SERVICES FPJ>
245 p_manual_price_change_flag VARCHAR2 default null, -- <Manual Price Override FPJ>
246 p_org_id IN NUMBER -- <R12.MOAC>
247 ,p_outsourced_assembly IN NUMBER default 2
248 ) RETURN BOOLEAN IS
249
250 X_progress VARCHAR2(3) := '';
251 X_Val_Qty_Released_True BOOLEAN;
252 X_Entity_Level VARCHAR2(25) := 'SHIPMENT';
253 X_orig_quantity NUMBER;
254
260 BEGIN
255 -- <Complex Work R12 Start>
256 l_value_basis PO_LINES_ALL.order_type_lookup_code%TYPE;
257 l_matching_basis PO_LINES_ALL.matching_basis%TYPE;
258 -- <Complex Work R12 End>
259
261
262 -- verify that the shipment number is unique.
263 -- Otherwise, display a message to the user and
264 -- abort insert_row.
265
266 X_progress := '005';
267 po_line_locations_pkg_s3.check_unique(
268 X_rowid,
269 X_shipment_num,
270 X_po_line_id,
271 X_po_release_id,
272 X_shipment_type);
273
274 X_progress := '010';
275
276 X_orig_quantity := 0; /* This is always zero for new releases
277 that get created . You would have to
278 fetch from the db for update cases alone */
279
280 -- Check if you have released more than is available on the
281 -- planned purchase order. If you have released more than
282 -- is available,the routine being called will display a message
283 -- to the user and prevent the user from continuing.
284 po_shipments_sv7.check_available_quantity(X_Source_Shipment_Id,
285 X_orig_quantity,
286 X_quantity);
287
288 -- Get the line_location_id for this release shipment.
289 X_progress := '020';
290
291 SELECT po_line_locations_s.nextval
292 INTO X_line_location_id
293 FROM sys.dual;
294
295 -- <Complex Work R12 Start>
296 -- Get value_basis and matching_basis from line
297 X_progress := '025';
298
299 SELECT pol.order_type_lookup_code, pol.matching_basis
300 INTO l_value_basis, l_matching_basis
301 FROM po_lines_all pol
302 WHERE pol.po_line_id = X_Po_Line_Id;
303
304 X_progress := '030';
305 -- <Complex Work R12 End>
306
307
308 -- Call the insert row routine with all parameters.
309 po_line_locations_pkg_s0.insert_row(
310 X_Rowid,
311 X_Line_Location_Id,
312 X_Last_Update_Date,
313 X_Last_Updated_By,
314 X_Po_Header_Id,
315 X_Po_Line_Id,
316 X_Last_Update_Login,
317 X_Creation_Date,
318 X_Created_By,
319 X_Quantity,
320 X_Quantity_Received,
321 X_Quantity_Accepted,
322 X_Quantity_Rejected,
323 X_Quantity_Billed,
324 X_Quantity_Cancelled,
325 X_Unit_Meas_Lookup_Code,
326 X_Po_Release_Id,
327 X_Ship_To_Location_Id,
328 X_Ship_Via_Lookup_Code,
329 X_Need_By_Date,
330 X_Promised_Date,
331 X_Last_Accept_Date,
332 X_Price_Override,
333 X_Encumbered_Flag,
334 X_Encumbered_Date,
335 X_Fob_Lookup_Code,
336 X_Freight_Terms_Lookup_Code,
337 X_Taxable_Flag,
338 NULL, --<R12 eTax Integration>
339 NULL, --<R12 eTax Integration>
340 X_Calculate_Tax_Flag,
341 X_From_Header_Id,
342 X_From_Line_Id,
343 X_From_Line_Location_Id,
344 X_Start_Date,
345 X_End_Date,
346 X_Lead_Time,
347 X_Lead_Time_Unit,
348 X_Price_Discount,
349 X_Terms_Id,
350 X_Approved_Flag,
351 X_Approved_Date,
352 X_Closed_Flag,
353 X_Cancel_Flag,
354 X_Cancelled_By,
355 X_Cancel_Date,
356 X_Cancel_Reason,
357 X_Firm_Status_Lookup_Code,
358 X_Attribute_Category,
359 X_Attribute1,
360 X_Attribute2,
361 X_Attribute3,
362 X_Attribute4,
363 X_Attribute5,
364 X_Attribute6,
368 X_Attribute10,
365 X_Attribute7,
366 X_Attribute8,
367 X_Attribute9,
369 X_Attribute11,
370 X_Attribute12,
371 X_Attribute13,
372 X_Attribute14,
373 X_Attribute15,
374 X_Inspection_Required_Flag,
375 X_Receipt_Required_Flag,
376 X_Qty_Rcv_Tolerance,
377 X_Qty_Rcv_Exception_Code,
378 X_Enforce_Ship_To_Location,
379 X_Allow_Substitute_Receipts,
380 X_Days_Early_Receipt_Allowed,
381 X_Days_Late_Receipt_Allowed,
382 X_Receipt_Days_Exception_Code,
383 X_Invoice_Close_Tolerance,
384 X_Receive_Close_Tolerance,
385 X_Ship_To_Organization_Id,
386 X_Shipment_Num,
387 X_Source_Shipment_Id,
388 X_Shipment_Type,
389 X_Closed_Code,
390 NULL, --<R12 SLA>
391 X_Government_Context,
392 X_Receiving_Routing_Id,
393 X_Accrue_On_Receipt_Flag,
394 X_Closed_Reason,
395 X_Closed_Date,
396 X_Closed_By,
397 X_Global_Attribute_Category,
398 X_Global_Attribute1,
399 X_Global_Attribute2,
400 X_Global_Attribute3,
401 X_Global_Attribute4,
402 X_Global_Attribute5,
403 X_Global_Attribute6,
404 X_Global_Attribute7,
405 X_Global_Attribute8,
406 X_Global_Attribute9,
407 X_Global_Attribute10,
408 X_Global_Attribute11,
409 X_Global_Attribute12,
410 X_Global_Attribute13,
411 X_Global_Attribute14,
412 X_Global_Attribute15,
413 X_Global_Attribute16,
414 X_Global_Attribute17,
415 X_Global_Attribute18,
416 X_Global_Attribute19,
417 X_Global_Attribute20,
418 X_Country_of_Origin_Code,
419 X_Invoice_Match_Option, --bgu, Dec. 7, 98
420 l_value_basis, -- <Complex Work R12>
421 l_matching_basis, -- <Complex Work R12>
422 --togeorge 10/12/2000
423 --Bug# 1433282
424 --added note to receiver
425 X_note_to_receiver,
426 --Start of Bug# 1548597.
427 X_Secondary_Unit_Of_Measure,
428 X_Secondary_Quantity,
429 X_Preferred_Grade,
430 X_Secondary_Quantity_Received,
431 X_Secondary_Quantity_Accepted,
432 X_Secondary_Quantity_Rejected,
433 X_Secondary_Quantity_Cancelled,
434 -- end of Bug# 1548597
435 null, -- <SERVICES FPJ>
436 X_amount, -- <SERVICES FPJ>
437 NULL, --transaction_flow_header_id
438 p_manual_price_change_flag, -- <Manual Price Override FPJ>
439 p_org_id -- <R12.MOAC>
440 ,p_outsourced_assembly --<SHIKYU R12>
441 );
442
443 /*
444 ** Call the routine to update release header approval status
445 */
446 UPDATE po_releases
447 SET approved_flag = 'R',
448 authorization_status = 'REQUIRES REAPPROVAL'
449 WHERE po_release_id = X_po_release_id
450 AND nvl(approved_flag,'N') not in ('N', 'R');
451
452 /*
453 ** Call the routine to attempt to update the quantity on
454 ** the purchase order line for blanket agreement releaeses.
455 */
456 IF X_shipment_type = 'BLANKET' THEN
457
458 /* Bug 482679
459 * problem with null quantity; replaced with following call.
460 */
461 po_lines_sv.update_released_quantity('INSERT',
462 'BLANKET',
463 x_po_line_id,
464 x_orig_quantity,
465 x_quantity);
466 END IF;
467
468 return(TRUE);
469
470 EXCEPTION
471 WHEN OTHERS THEN
472 -- togeorge 10/26/2000 commented out due to the problems while arcs in.
473 -- dbms_output.put_line('In exception');
474 po_message_s.sql_error('insert_rel_shipment', X_progress, sqlcode);
475 raise;
476 END insert_rel_shipment;
477
478
479 END PO_SHIPMENTS_SV3;