[Home] [Help]
PACKAGE BODY: APPS.PO_LINE_LOCATIONS_SV6
Source
1 PACKAGE BODY PO_LINE_LOCATIONS_SV6 AS
2 /* $Header: POXPIISB.pls 120.2.12000000.1 2007/01/16 23:03:58 appldev ship $ */
3
4 /*================================================================
5
6 PROCEDURE NAME: insert_po_line_locations()
7
8 ==================================================================*/
9 PROCEDURE insert_po_line_locations(
10 x_line_location_id IN NUMBER,
11 x_last_update_date IN DATE,
12 x_last_updated_by IN NUMBER,
13 x_po_header_id IN NUMBER,
14 x_po_line_id IN NUMBER,
15 x_last_update_login IN NUMBER,
16 x_creation_date IN DATE,
17 x_created_by IN NUMBER,
18 x_quantity IN NUMBER,
19 x_quantity_received IN NUMBER,
20 x_quantity_accepted IN NUMBER,
21 x_quantity_rejected IN NUMBER,
22 x_quantity_billed IN NUMBER,
23 x_quantity_cancelled IN NUMBER,
24 x_unit_meas_lookup_code IN VARCHAR2,
25 x_po_release_id IN NUMBER,
26 x_ship_to_location_id IN NUMBER,
27 x_ship_via_lookup_code IN VARCHAR2,
28 x_need_by_date IN DATE,
29 x_promised_date IN DATE,
30 x_last_accept_date IN DATE,
31 x_price_override IN NUMBER,
32 x_encumbered_flag IN VARCHAR2,
33 x_encumbered_date IN DATE,
34 x_fob_lookup_code IN VARCHAR2,
35 x_freight_terms_lookup_code IN VARCHAR2,
36 x_taxable_flag IN VARCHAR2,
37 x_tax_code_id IN NUMBER,
38 x_from_header_id IN NUMBER,
39 x_from_line_id IN NUMBER,
40 x_from_line_location_id IN NUMBER,
41 x_start_date IN DATE,
42 x_end_date IN DATE,
43 x_lead_time IN NUMBER,
44 x_lead_time_unit IN VARCHAR2,
45 x_price_discount IN NUMBER,
46 x_terms_id IN NUMBER,
47 x_approved_flag IN VARCHAR2,
48 x_closed_flag IN VARCHAR2,
49 x_cancel_flag IN VARCHAR2,
50 x_cancelled_by IN NUMBER,
51 x_cancel_date IN DATE,
52 x_cancel_reason IN VARCHAR2,
53 x_firm_status_lookup_code IN VARCHAR2,
54 x_attribute_category IN VARCHAR2,
55 x_attribute1 IN VARCHAR2,
56 x_attribute2 IN VARCHAR2,
57 x_attribute3 IN VARCHAR2,
58 x_attribute4 IN VARCHAR2,
59 x_attribute5 IN VARCHAR2,
60 x_attribute6 IN VARCHAR2,
61 x_attribute7 IN VARCHAR2,
62 x_attribute8 IN VARCHAR2,
63 x_attribute9 IN VARCHAR2,
64 x_attribute10 IN VARCHAR2,
65 x_attribute11 IN VARCHAR2,
66 x_attribute12 IN VARCHAR2,
67 x_attribute13 IN VARCHAR2,
68 x_attribute14 IN VARCHAR2,
69 x_attribute15 IN VARCHAR2,
70 x_inspection_required_flag IN VARCHAR2,
71 x_receipt_required_flag IN VARCHAR2,
72 x_qty_rcv_tolerance IN NUMBER,
73 x_qty_rcv_exception_code IN VARCHAR2,
74 x_enforce_ship_to_loc_code IN VARCHAR2,
75 x_allow_sub_receipts_flag IN VARCHAR2,
76 x_days_early_receipt_allowed IN VARCHAR2,
77 x_days_late_receipt_allowed IN VARCHAR2,
78 x_receipt_days_exception_code IN VARCHAR2,
79 x_invoice_close_tolerance IN NUMBER,
80 x_receive_close_tolerance IN NUMBER,
81 x_ship_to_organization_id IN NUMBER,
82 x_shipment_num IN NUMBER,
83 x_source_shipment_id IN NUMBER,
84 x_shipment_type IN VARCHAR2,
85 x_closed_code IN VARCHAR2,
86 x_request_id IN NUMBER,
87 x_program_application_id IN NUMBER,
88 x_program_id IN NUMBER,
89 x_program_update_date IN DATE,
90 x_ussgl_transaction_code IN VARCHAR2,
91 x_government_context IN VARCHAR2,
92 x_receiving_routing_id IN NUMBER,
93 x_accrue_on_receipt_flag IN VARCHAR2,
94 x_closed_reason IN VARCHAR2,
95 x_closed_date IN DATE,
96 x_closed_by IN NUMBER,
97 x_org_id IN NUMBER,
98 p_transaction_flow_header_id IN NUMBER, --< Shared Proc FPJ >
99 --<SERVICES FPJ START>
100 p_amount IN NUMBER,
101 p_order_type_lookup_code IN VARCHAR2,
102 p_purchase_basis IN VARCHAR2,
103 --<SERVICES FPJ END>
104 x_match_option IN VARCHAR2 DEFAULT NULL,
105 X_note_to_receiver IN VARCHAR2 default null --togeorge 09/28/2000
106 )
107
108 IS
109 x_progress varchar2(3) := null;
110 l_unit_price number;
111 l_price_discount number;
112 l_type_lookup_code po_headers_all.type_lookup_code%TYPE; /* <TIMEPHASED FPI> */
113 l_price_override number := null; /* <TIMEPHASED FPI> */
114
115 BEGIN
116 x_progress := '010';
117
118 /* Bug: 550424. gtummala. 10/03/97.
119 * The org_id was not being populated from the
120 * default value (from client info) in the table because we
121 * are passing in a value here, which could be null. If
122 * we don't pass in the value org_id will get populated
123 */
124
125 -- Calculate the price_discount - Bug: 758866
126 begin
127 select unit_price into l_unit_price
128 from po_lines
129 where po_header_id = x_po_header_id
130 and po_line_id = x_po_line_id;
131
132 l_price_discount := ROUND(((l_unit_price - x_price_override)/l_unit_price) * 100, 2);
133
134 exception
135 when others then
136 NULL;
137 end;
138
139 x_progress := '020';
140
141 /* <TIMEPHASED FPI START> */
142 /* Defaulting the price discount and pricebreak price for Blanket Agreement, if absent */
143 select type_lookup_code
144 into l_type_lookup_code
145 from po_headers_all
146 where po_header_id = x_po_header_id;
147
148 if (l_type_lookup_code = 'BLANKET') then
149 if (x_price_override is not null) then
150 l_price_discount := ROUND(((l_unit_price - x_price_override)/l_unit_price) * 100, 2);
151 elsif (x_price_override is null AND x_price_discount is not null) then
152 l_price_override := ROUND((((100 - x_price_discount)/100) * l_unit_price), 2);
153 end if;
154 end if;
155 /* <TIMEPHASED FPI END> */
156
157 if (l_price_override is not null) then
158 INSERT INTO po_line_locations
159 (
160 line_location_id,
161 last_update_date,
162 last_updated_by,
163 po_header_id,
164 po_line_id,
165 last_update_login,
166 creation_date,
167 created_by,
168 quantity,
169 quantity_received,
170 quantity_accepted,
171 quantity_rejected,
172 quantity_billed,
173 quantity_cancelled,
174 unit_meas_lookup_code,
175 po_release_id,
176 ship_to_location_id,
177 ship_via_lookup_code,
178 need_by_date,
179 promised_date,
180 last_accept_date,
181 price_override,
182 encumbered_flag,
183 encumbered_date,
184 fob_lookup_code,
185 freight_terms_lookup_code,
186 taxable_flag,
187 tax_code_id,
188 from_header_id,
189 from_line_id,
190 from_line_location_id,
191 start_date,
192 end_date,
193 lead_time,
194 lead_time_unit,
195 price_discount,
196 terms_id,
197 approved_flag,
198 closed_flag,
199 cancel_flag,
200 cancelled_by,
201 cancel_date,
202 cancel_reason,
203 firm_status_lookup_code,
204 attribute_category,
205 attribute1,
206 attribute2,
207 attribute3,
208 attribute4,
209 attribute5,
210 attribute6,
211 attribute7,
212 attribute8,
213 attribute9,
214 attribute10,
215 attribute11,
216 attribute12,
217 attribute13,
218 attribute14,
219 attribute15,
220 inspection_required_flag,
221 receipt_required_flag,
222 qty_rcv_tolerance,
223 qty_rcv_exception_code,
224 enforce_ship_to_location_code,
225 allow_substitute_receipts_flag,
226 days_early_receipt_allowed,
227 days_late_receipt_allowed,
228 receipt_days_exception_code,
229 invoice_close_tolerance,
230 receive_close_tolerance,
231 ship_to_organization_id,
232 shipment_num,
233 source_shipment_id,
234 shipment_type,
235 closed_code,
236 request_id,
237 program_application_id,
238 program_id,
239 program_update_date,
240 government_context,
241 receiving_routing_id,
242 accrue_on_receipt_flag,
243 closed_reason,
244 closed_date,
245 closed_by,
246 match_option, --frkhan
247 note_to_receiver, --togeorge 09/28/2000
248 transaction_flow_header_id, --< Shared Proc FPJ >
249 --<SERVICES FPJ START>
250 amount,
251 amount_received,
252 amount_cancelled,
253 amount_billed,
254 --<SERVICES FPJ END>
255 org_id -- <R12 MOAC>
256 )
257 VALUES
258 (
259 x_line_location_id,
260 x_last_update_date,
261 x_last_updated_by,
262 x_po_header_id,
263 x_po_line_id,
264 x_last_update_login,
265 x_creation_date,
266 x_created_by,
267 x_quantity,
268 -- Bug: 1693472 Add the NVL
269 nvl(x_quantity_received,0),
270 nvl(x_quantity_accepted,0),
271 nvl(x_quantity_rejected,0),
272 nvl(x_quantity_billed,0),
273 nvl(x_quantity_cancelled,0),
274 x_unit_meas_lookup_code,
275 x_po_release_id,
276 x_ship_to_location_id,
277 x_ship_via_lookup_code,
278 x_need_by_date,
279 x_promised_date,
280 x_last_accept_date,
281 l_price_override, /* <TIMEPHASED FPI> */
282 x_encumbered_flag,
283 x_encumbered_date,
284 x_fob_lookup_code,
285 x_freight_terms_lookup_code,
286 x_taxable_flag,
287 x_tax_code_id,
288 x_from_header_id,
289 x_from_line_id,
290 x_from_line_location_id,
291 x_start_date,
292 x_end_date,
293 x_lead_time,
294 x_lead_time_unit,
295 l_price_discount,
296 x_terms_id,
297 x_approved_flag,
298 x_closed_flag,
299 x_cancel_flag,
300 x_cancelled_by,
301 x_cancel_date,
302 x_cancel_reason,
303 x_firm_status_lookup_code,
304 x_attribute_category,
305 x_attribute1,
306 x_attribute2,
307 x_attribute3,
308 x_attribute4,
309 x_attribute5,
310 x_attribute6,
311 x_attribute7,
312 x_attribute8,
313 x_attribute9,
314 x_attribute10,
315 x_attribute11,
316 x_attribute12,
317 x_attribute13,
318 x_attribute14,
319 x_attribute15,
320 x_inspection_required_flag,
321 x_receipt_required_flag,
322 x_qty_rcv_tolerance,
323 x_qty_rcv_exception_code,
324 x_enforce_ship_to_loc_code,
325 x_allow_sub_receipts_flag,
326 x_days_early_receipt_allowed,
327 x_days_late_receipt_allowed,
328 x_receipt_days_exception_code,
329 x_invoice_close_tolerance,
330 x_receive_close_tolerance,
331 x_ship_to_organization_id,
332 x_shipment_num,
333 x_source_shipment_id,
334 x_shipment_type,
335 x_closed_code,
336 x_request_id,
337 x_program_application_id,
338 x_program_id,
339 x_program_update_date,
340 x_government_context,
341 x_receiving_routing_id,
342 x_accrue_on_receipt_flag,
343 x_closed_reason,
344 x_closed_date,
345 x_closed_by,
346 x_match_option, --frkhan
347 X_note_to_receiver, --togeorge 09/28/2000
348 p_transaction_flow_header_id, --< Shared Proc FPJ >
349 --<SERVICES FPJ START>
350 p_amount,
351 0,
352 0,
353 0,
354 --<SERVICES FPJ END>
355 X_org_id -- <R12 MOAC>
356 );
357
358 /* <TIMEPHASED FPI START> */
359 else
360 INSERT INTO po_line_locations
361 (
362 line_location_id,
363 last_update_date,
364 last_updated_by,
365 po_header_id,
366 po_line_id,
367 last_update_login,
371 quantity_received,
368 creation_date,
369 created_by,
370 quantity,
372 quantity_accepted,
373 quantity_rejected,
374 quantity_billed,
375 quantity_cancelled,
376 unit_meas_lookup_code,
377 po_release_id,
378 ship_to_location_id,
379 ship_via_lookup_code,
380 need_by_date,
381 promised_date,
382 last_accept_date,
383 price_override,
384 encumbered_flag,
385 encumbered_date,
386 fob_lookup_code,
387 freight_terms_lookup_code,
388 taxable_flag,
389 tax_code_id,
390 from_header_id,
391 from_line_id,
392 from_line_location_id,
393 start_date,
394 end_date,
395 lead_time,
396 lead_time_unit,
397 price_discount,
398 terms_id,
399 approved_flag,
400 closed_flag,
401 cancel_flag,
402 cancelled_by,
403 cancel_date,
404 cancel_reason,
405 firm_status_lookup_code,
406 attribute_category,
407 attribute1,
408 attribute2,
409 attribute3,
410 attribute4,
411 attribute5,
412 attribute6,
413 attribute7,
414 attribute8,
415 attribute9,
416 attribute10,
417 attribute11,
418 attribute12,
419 attribute13,
420 attribute14,
421 attribute15,
422 inspection_required_flag,
423 receipt_required_flag,
424 qty_rcv_tolerance,
425 qty_rcv_exception_code,
426 enforce_ship_to_location_code,
427 allow_substitute_receipts_flag,
428 days_early_receipt_allowed,
429 days_late_receipt_allowed,
430 receipt_days_exception_code,
431 invoice_close_tolerance,
432 receive_close_tolerance,
433 ship_to_organization_id,
434 shipment_num,
435 source_shipment_id,
436 shipment_type,
437 closed_code,
438 request_id,
439 program_application_id,
440 program_id,
441 program_update_date,
442 government_context,
443 receiving_routing_id,
444 accrue_on_receipt_flag,
445 closed_reason,
446 closed_date,
447 closed_by,
448 match_option, --frkhan
449 note_to_receiver, --togeorge 09/28/2000
450 transaction_flow_header_id, --< Shared Proc FPJ >
451 --<SERVICES FPJ START>
452 amount,
453 amount_received,
454 amount_cancelled,
455 amount_billed,
456 --<SERVICES FPJ END>
457 org_id -- <R12 MOAC>
458 )
459 VALUES
460 (
461 x_line_location_id,
462 x_last_update_date,
463 x_last_updated_by,
464 x_po_header_id,
465 x_po_line_id,
466 x_last_update_login,
467 x_creation_date,
468 x_created_by,
469 x_quantity,
470 -- Bug: 1693472 Add the NVL
471 nvl(x_quantity_received,0),
472 nvl(x_quantity_accepted,0),
473 nvl(x_quantity_rejected,0),
474 nvl(x_quantity_billed,0),
475 nvl(x_quantity_cancelled,0),
476 x_unit_meas_lookup_code,
477 x_po_release_id,
478 x_ship_to_location_id,
479 x_ship_via_lookup_code,
480 x_need_by_date,
481 x_promised_date,
482 x_last_accept_date,
483 x_price_override,
484 x_encumbered_flag,
485 x_encumbered_date,
486 x_fob_lookup_code,
487 x_freight_terms_lookup_code,
488 x_taxable_flag,
489 x_tax_code_id,
490 x_from_header_id,
491 x_from_line_id,
492 x_from_line_location_id,
493 x_start_date,
494 x_end_date,
495 x_lead_time,
496 x_lead_time_unit,
497 l_price_discount,
498 x_terms_id,
499 x_approved_flag,
500 x_closed_flag,
501 x_cancel_flag,
502 x_cancelled_by,
503 x_cancel_date,
504 x_cancel_reason,
505 x_firm_status_lookup_code,
506 x_attribute_category,
507 x_attribute1,
508 x_attribute2,
509 x_attribute3,
510 x_attribute4,
511 x_attribute5,
512 x_attribute6,
513 x_attribute7,
514 x_attribute8,
515 x_attribute9,
516 x_attribute10,
517 x_attribute11,
518 x_attribute12,
519 x_attribute13,
520 x_attribute14,
521 x_attribute15,
522 x_inspection_required_flag,
523 x_receipt_required_flag,
524 x_qty_rcv_tolerance,
525 x_qty_rcv_exception_code,
526 x_enforce_ship_to_loc_code,
527 x_allow_sub_receipts_flag,
528 x_days_early_receipt_allowed,
529 x_days_late_receipt_allowed,
530 x_receipt_days_exception_code,
531 x_invoice_close_tolerance,
532 x_receive_close_tolerance,
533 x_ship_to_organization_id,
534 x_shipment_num,
535 x_source_shipment_id,
536 x_shipment_type,
537 x_closed_code,
538 x_request_id,
539 x_program_application_id,
540 x_program_id,
541 x_program_update_date,
542 x_government_context,
543 x_receiving_routing_id,
544 x_accrue_on_receipt_flag,
545 x_closed_reason,
546 x_closed_date,
547 x_closed_by,
548 x_match_option, --frkhan
549 X_note_to_receiver, --togeorge 09/28/2000
550 p_transaction_flow_header_id, --< Shared Proc FPJ >
551 --<SERVICES FPJ START>
552 p_amount,
553 0,
554 0,
555 0,
556 --<SERVICES FPJ END>
557 X_org_id -- <R12 MOAC>
558 );
559
560 end if;
561 /* <TIMEPHASED FPI END> */
562
563 EXCEPTION
564 WHEN others THEN
565 po_message_s.sql_error('insert_po_line_locations',
566 x_progress, sqlcode);
567 raise;
568 END insert_po_line_locations;
569
570 END PO_LINE_LOCATIONS_SV6;