[Home] [Help]
PACKAGE BODY: APPS.POA_SAVINGS_NP
Source
1 PACKAGE BODY poa_savings_np AS
2 /* $Header: poasvp3b.pls 120.3 2006/02/13 01:27:01 sdiwakar noship $ */
3
4 /*
5 NAME
6 populate_npcontract -
7 DESCRIPTION
8 main function for populating poa_bis_savings fact table
9 for Oracle Purchasing with non-contract and potential
10 contract purchases information
11 */
12
13
14 --
15 l_from_currency_code VARCHAR2(150) := NULL;
16 l_to_currency_code VARCHAR2(150) := NULL;
17 l_curr_conv_rate_date DATE;
18 l_curr_conv_rate_type VARCHAR2(150);
19 l_currency_conv_rate NUMBER;
20
21 PROCEDURE populate_npcontract (p_start_date IN DATE,
22 p_end_date IN DATE,
23 p_start_time IN DATE,
24 p_batch_no IN NUMBER)
25 IS
26
27 v_item_id NUMBER;
28 v_item_revision VARCHAR2(3) := NULL;
29 v_category_id NUMBER;
30 v_quantity NUMBER;
31 v_unit_meas_lookup_code VARCHAR2(25) := NULL;
32 v_creation_date DATE;
33 v_currency_code VARCHAR2(15) := NULL;
34 v_po_distribution_id NUMBER;
35 v_ship_to_location_id NUMBER;
36 v_price_override NUMBER;
37 v_need_by_date DATE;
38 v_org_id NUMBER;
39
40
41 v_matching_agreement BINARY_INTEGER := 0;
42
43 v_lowest_possible_price NUMBER;
44 v_buf VARCHAR2(240) := NULL;
45 v_count NUMBER := 0;
46
47 x_iteration BINARY_INTEGER := 0;
48 x_progress VARCHAR2(3) := NULL;
49
50 e_error EXCEPTION;
51 l_start_time DATE;
52 l_end_time DATE;
53 v_ship_to_organization_id po_line_locations_all.ship_to_organization_id%type;
54 v_ship_to_ou NUMBER;
55 v_rate_date DATE;
56 v_edw_global_rate_type edw_local_system_parameters.rate_type%type;
57 v_edw_global_currency_code EDW_LOCAL_SYSTEM_PARAMETERS.warehouse_currency_code%type;
58 --
59 /* cursor to look at all standard POs which non-one-time-items that exist
60 * in blankets. We still need to check whether all the criteria
61 * for the item match those stated in the blankets.
62 */
63 CURSOR C_STD_PO (c_batch_no NUMBER) IS
64 SELECT /*+ cardinality (inc, 1) */ plc.item_id
65 , plc.item_revision
66 , plc.category_id
67 , psc.quantity
68 , plc.unit_meas_lookup_code
69 , pod.creation_date
70 , phc.currency_code
71 , pod.po_distribution_id
72 , psc.ship_to_location_id
73 , psc.price_override
74 , psc.need_by_date
75 , phc.org_id
76 , psc.ship_to_organization_id
77 , to_number(hro.org_information3) org_information3
78 , nvl(pod.rate_date,pod.creation_date) rate_date
79 , phc.rate_type
80 FROM poa_edw_po_dist_inc inc,
81 po_distributions_all pod
82 , po_line_locations_all psc
83 , po_lines_all plc
84 , po_doc_style_headers style
85 , po_headers_all phc
86 , po_headers_all ga
87 , hr_organization_information hro
88 WHERE inc.primary_key = pod.PO_DISTRIBUTION_ID
89 and phc.po_header_id = plc.po_header_id
90 and plc.po_line_id = psc.po_line_id
91 and psc.line_location_id = pod.line_location_id
92 and psc.shipment_type = 'STANDARD'
93 and phc.style_id = style.style_id
94 and nvl(style.progress_payment_flag,'N') = 'N'
95 and psc.approved_flag = 'Y'
96 and plc.contract_id is null
97 and plc.from_header_id = ga.po_header_id(+)
98 AND Nvl(ga.global_agreement_flag, 'N') = 'N'
99 and plc.item_id is not null
100 and pod.creation_date is not null
101 and inc.batch_id = c_batch_no
102 and to_number(hro.organization_id) = psc.ship_to_organization_id
103 and hro.org_information_context = 'Accounting Information'
104 and nvl(pod.distribution_type,'-99') <> 'AGREEMENT'
105 and exists (SELECT 'blanket item'
106 FROM po_lines_all pl
107 , po_headers_all ph
108 WHERE ph.type_lookup_code = 'BLANKET'
109 and ph.po_header_id = pl.po_header_id
110 and nvl(pl.unit_meas_lookup_code,
111 nvl(plc.unit_meas_lookup_code, '-1'))
112 = nvl(plc.unit_meas_lookup_code, '-1')
113 and pod.creation_date between
114 nvl(ph.start_date, pod.creation_date)
115 and nvl(ph.end_date, pod.creation_date)
116 and trunc(pod.creation_date) <= nvl(pl.expiration_date, pod.creation_date)
117 and pl.item_id = plc.item_id
118 and nvl(pl.item_revision, nvl(plc.item_revision, '-1'))
119 = nvl(plc.item_revision, '-1')
120 and (
121 (nvl(ph.global_agreement_flag,'N') = 'N'
122 and ph.org_id = to_number(hro.org_information3)
123 )
124 or
125 (ph.global_agreement_flag = 'Y'
126 and exists
127 (select 'enabled'
128 from po_ga_org_assignments poga
129 where poga.po_header_id = ph.po_header_id
130 and poga.enabled_flag = 'Y'
131 and ((poga.purchasing_org_id in
132 (select /*+ leading(tfh) */ tfh.start_org_id
133 from mtl_transaction_flow_headers tfh,
134 financials_system_params_all fsp1,
135 financials_system_params_all fsp2
136 where pod.creation_date between nvl(tfh.start_date,pod.creation_date)
137 and nvl(tfh.end_date,pod.creation_date)
138 and tfh.flow_type = 2
139 and fsp1.org_id = tfh.start_org_id
140 and fsp1.purch_encumbrance_flag = 'N'
141 and fsp2.org_id = tfh.end_org_id
142 and fsp2.purch_encumbrance_flag = 'N'
143 and (
144 (tfh.qualifier_code is null) or
145 (tfh.qualifier_code = 1 and tfh.qualifier_value_id = plc.category_id)
146 )
147 and tfh.end_org_id = to_number(hro.org_information3)
148 and (
149 (tfh.organization_id = psc.ship_to_organization_id) or
150 (tfh.organization_id is null)
151 )
152 )
153 )
154 or poga.purchasing_org_id = to_number(hro.org_information3)
155 )
156 )
157 )
158 )
159 );
160
161 BEGIN
162
163 POA_LOG.debug_line('Populate_noncontract: entered');
164 POA_LOG.debug_line(' ');
165
166
167 /* Delete from poa_bis_savings all rows which will is approved
168 * and was modified in the date range specified.
169 * These rows will be reinserted with the new modified information.
170 */
171
172
173 x_progress := '015';
174
175 DELETE FROM poa_bis_savings poa
176 WHERE distribution_transaction_id IN
177 (SELECT primary_key FROM poa_edw_po_dist_inc WHERE batch_id = p_batch_no);
178
179 /* Insert rows for POs created for one-time items in which no blankets
180 * exists (non-contracts)
181 */
182
183 x_progress := '020';
184 INSERT INTO poa_bis_savings
185 ( purchase_amount
186 , contract_amount
187 , non_contract_amount
188 , pot_contract_amount
189 , potential_saving
190 , total_purchase_qty
191 , distribution_transaction_id
192 , document_type_code
193 , purchase_creation_date
194 , item_id
195 , item_description
196 , category_id
197 , supplier_site_id
198 , supplier_id
199 , requestor_id
200 , ship_to_location_id
201 , ship_to_organization_id
202 , operating_unit_id
203 , buyer_id
204 , project_id
205 , task_id
206 , currency_code
207 , rate_type
208 , rate_date
209 , cost_center_id
210 , account_id
211 , company_id
212 , rate
213 , approved_date
214 , Currency_Conv_Rate
215 , created_by
216 , creation_date
217 , last_updated_by
218 , last_update_date
219 , last_update_login
220 , request_id
221 , program_application_id
222 , program_id
223 , program_update_date)
224 (SELECT /*+ cardinality (inc, 1) */
225 decode(psc.consigned_flag ,'Y'
226 ,null
227 ,decode(psc.matching_basis,'AMOUNT'
228 ,decode(psc.closed_code,'FINALLY_CLOSED'
229 ,decode(sign(nvl(pod.amount_delivered,0) -nvl(pod.amount_billed,0)) ,1
230 ,nvl(pod.amount_delivered,0)
231 ,nvl(pod.amount_billed,0)
232 ) *nvl(pod.rate,1)
233 ,(nvl(pod.amount_ordered,0) - nvl(pod.amount_cancelled,0)) * nvl(pod.rate,1)
234 )
235 ,decode(psc.closed_code,'FINALLY_CLOSED'
236 ,decode(sign(nvl(pod.quantity_delivered,0) -nvl(pod.quantity_billed,0)) ,1
237 ,nvl(pod.quantity_delivered,0)
238 ,nvl(pod.quantity_billed,0)
239 ) * nvl(psc.price_override,0) * nvl(pod.rate,1)
240 ,(nvl(pod.quantity_ordered,0) - nvl(pod.quantity_cancelled,0)) * nvl(psc.price_override,0) * nvl(pod.rate,1)
241 )
242 )
243 )
244 , Decode(psc.consigned_flag, 'Y', NULL, 0)
245 , decode(psc.consigned_flag
246 ,'Y'
247 ,null
248 ,decode(psc.matching_basis
249 ,'AMOUNT'
250 ,(decode(psc.closed_code
251 ,'FINALLY_CLOSED'
252 ,(decode(sign(nvl(pod.amount_delivered,0)
253 -nvl(pod.amount_billed,0))
254 ,1
255 ,nvl(pod.amount_delivered,0)
256 ,nvl(pod.amount_billed,0))) * nvl(pod.rate,1)
257 ,(nvl(pod.amount_ordered,0)
258 -nvl(pod.amount_cancelled,0))
259 *nvl(pod.rate,1)))
260 ,(decode(psc.closed_code
261 ,'FINALLY_CLOSED'
262 ,(decode(sign(nvl(pod.quantity_delivered,0)
263 -nvl(pod.quantity_billed,0))
264 ,1
265 ,nvl(pod.quantity_delivered,0)
266 ,nvl(pod.quantity_billed,0)))
267 *nvl(psc.price_override,0)
268 *nvl(pod.rate,1)
269 ,(nvl(pod.quantity_ordered,0)
270 -nvl(pod.quantity_cancelled,0))
271 *nvl(psc.price_override,0)
272 *nvl(pod.rate,1)))
273 )
274 )
275 , Decode(psc.consigned_flag, 'Y', NULL, 0)
276 , Decode(psc.consigned_flag, 'Y', NULL, 0)
277 , decode(psc.consigned_flag
278 ,'Y'
279 ,null
280 ,decode(psc.value_basis
281 ,'QUANTITY'
282 ,pod.quantity_ordered
283 ,null)
284 )
285 , pod.po_distribution_id
286 , phc.type_lookup_code
287 , pod.creation_date
288 , plc.item_id
289 , plc.item_description
290 , plc.category_id
291 , phc.vendor_site_id
292 , phc.vendor_id
293 , pod.deliver_to_person_id
294 , psc.ship_to_location_id
295 , psc.ship_to_organization_id
296 , psc.org_id
297 , phc.agent_id
298 , pod.project_id
299 , pod.task_id
300 , gl.currency_code
301 , phc.rate_type
302 , nvl(phc.rate_date, pod.creation_date)
303 , pod.code_combination_id
304 , NULL
305 , NULL
306 , pod.rate
307 , NVL(POA_OLTP_GENERIC_PKG.get_approved_date_poh(pod.creation_date, phc.po_header_id),
308 phc.approved_date)
309 , POA_CURRENCY_PKG.get_global_currency_rate (phc.rate_type,
310 decode(phc.rate_type, 'User', gl.currency_code,
311 NVL(phc.currency_code, gl.currency_code)),
312 NVL(pod.rate_date, pod.creation_date), phc.rate)
313 , fnd_global.user_id
314 , p_start_time
315 , fnd_global.user_id
316 , p_start_time
317 , fnd_global.login_id
318 , fnd_global.conc_request_id
319 , fnd_global.prog_appl_id
320 , fnd_global.conc_program_id
321 , p_start_time
322 FROM poa_edw_po_dist_inc inc,
323 gl_sets_of_books gl
324 , po_distributions_all pod
325 , po_doc_style_headers style
326 , po_line_locations_all psc
327 , po_lines_all plc
328 , po_headers_all phc
329 , po_headers_all ga
330 WHERE inc.primary_key = pod.PO_DISTRIBUTION_ID
331 and phc.po_header_id = plc.po_header_id
332 and plc.po_line_id = psc.po_line_id
333 and psc.line_location_id = pod.line_location_id
334 and psc.shipment_type = 'STANDARD'
335 and phc.style_id = style.style_id
336 and nvl(style.progress_payment_flag,'N') = 'N'
337 AND plc.from_header_id = ga.po_header_id(+)
338 AND Nvl(ga.global_agreement_flag, 'N') = 'N'
339 and psc.approved_flag = 'Y'
340 and nvl(pod.distribution_type,'-99') <> 'AGREEMENT'
341 and plc.contract_id is null
342 and gl.set_of_books_id = pod.set_of_books_id
343 and plc.item_id is null
344 and pod.creation_date is not null
345 and inc.batch_id = p_batch_no);
346
347 x_progress := '025';
348
349 /* Insert rows for POs created for non-one-time items in which no blankets
350 * exists (non-contracts). These are considered leakage. So, for
351 * each of these rows, we still need to calculate their potential
352 * savings.
353 */
354
355 INSERT INTO poa_bis_savings
356 ( purchase_amount
357 , contract_amount
358 , non_contract_amount
359 , pot_contract_amount
360 , potential_saving
361 , total_purchase_qty
362 , distribution_transaction_id
363 , document_type_code
364 , purchase_creation_date
365 , item_id
366 , item_description
367 , category_id
368 , supplier_site_id
369 , supplier_id
370 , requestor_id
371 , ship_to_location_id
372 , ship_to_organization_id
373 , operating_unit_id
374 , buyer_id
375 , project_id
376 , task_id
377 , currency_code
378 , rate_type
379 , rate_date
380 , cost_center_id
381 , account_id
382 , company_id
383 , rate
384 , approved_date
385 , Currency_Conv_Rate
386 , created_by
387 , creation_date
388 , last_updated_by
389 , last_update_date
390 , last_update_login
391 , request_id
392 , program_application_id
393 , program_id
394 , program_update_date)
395 (SELECT /*+ cardinality(inc, 1) */ decode(psc.consigned_flag
396 ,'Y'
397 ,null
398 ,decode(psc.matching_basis
399 ,'AMOUNT'
400 ,(decode(psc.closed_code
401 ,'FINALLY_CLOSED'
402 ,(decode(sign(nvl(pod.AMOUNT_delivered,0)
403 -nvl(pod.AMOUNT_billed,0))
404 ,1
405 ,nvl(pod.AMOUNT_delivered,0)
406 ,nvl(pod.AMOUNT_billed,0))) * nvl(pod.rate,1)
407 ,(nvl(pod.amount_ordered,0)
408 -nvl(pod.amount_cancelled,0))
409 * nvl(pod.rate,1)))
410 ,(decode(psc.closed_code
411 ,'FINALLY_CLOSED'
412 ,(decode(sign(nvl(pod.quantity_delivered,0)
413 -nvl(pod.quantity_billed,0))
414 ,1
415 ,nvl(pod.quantity_delivered,0)
416 ,nvl(pod.quantity_billed,0)))
417 *nvl(psc.price_override,0) * nvl(pod.rate,1)
418 ,(nvl(pod.quantity_ordered,0)
419 -nvl(pod.quantity_cancelled,0))
420 *nvl(psc.price_override,0)
421 *nvl(pod.rate,1)))
422 )
423 )
424 , Decode(psc.consigned_flag, 'Y', NULL, 0)
425 , decode(psc.consigned_flag
426 ,'Y'
427 ,null
428 ,decode(psc.matching_basis
429 ,'AMOUNT'
430 ,(decode(psc.closed_code
431 ,'FINALLY_CLOSED'
432 ,(decode(sign(nvl(pod.AMOUNT_delivered,0)
433 -nvl(pod.AMOUNT_billed,0))
434 ,1
435 ,nvl(pod.AMOUNT_delivered,0)
436 ,nvl(pod.AMOUNT_billed,0))) * nvl(pod.rate,1)
437 ,(nvl(pod.AMOUNT_ordered,0)
438 -nvl(pod.AMOUNT_cancelled,0))
439 *nvl(pod.rate,1)))
440 ,(decode(psc.closed_code
441 ,'FINALLY_CLOSED'
442 ,(decode(sign(nvl(pod.quantity_delivered,0)
443 -nvl(pod.quantity_billed,0))
444 ,1
445 ,nvl(pod.quantity_delivered,0)
446 ,nvl(pod.quantity_billed,0)))
447 *nvl(psc.price_override,0)
448 *nvl(pod.rate,1)
449 ,(nvl(pod.quantity_ordered,0)
450 -nvl(pod.quantity_cancelled,0))
451 *nvl(psc.price_override,0)
452 *nvl(pod.rate,1)))
453 )
454 )
455 , Decode(psc.consigned_flag, 'Y', NULL, 0)
456 , Decode(psc.consigned_flag, 'Y', NULL, 0)
457 , decode(psc.consigned_flag
458 ,'Y'
459 ,null
460 ,decode(psc.value_basis
461 ,'QUANTITY'
462 ,pod.quantity_ordered
463 ,null
464 )
465 )
466 , pod.po_distribution_id
467 , phc.type_lookup_code
468 , pod.creation_date
469 , plc.item_id
470 , plc.item_description
471 , plc.category_id
472 , phc.vendor_site_id
473 , phc.vendor_id
474 , pod.deliver_to_person_id
475 , psc.ship_to_location_id
476 , psc.ship_to_organization_id
477 , psc.org_id
478 , phc.agent_id
479 , pod.project_id
480 , pod.task_id
481 , gl.currency_code
482 , phc.rate_type
483 , nvl(phc.rate_date, pod.creation_date)
484 , pod.code_combination_id
485 , NULL
486 , NULL
487 , pod.rate
488 , NVL(POA_OLTP_GENERIC_PKG.get_approved_date_poh(pod.creation_date, phc.po_header_id),
489 phc.approved_date)
490 , POA_CURRENCY_PKG.get_global_currency_rate (phc.rate_type,
491 decode(phc.rate_type, 'User', gl.currency_code,
492 NVL(phc.currency_code,gl.currency_code)),
493 NVL(pod.rate_date, pod.creation_date), phc.rate)
494 , fnd_global.user_id
495 , p_start_time
496 , fnd_global.user_id
497 , p_start_time
498 , fnd_global.login_id
499 , fnd_global.conc_request_id
500 , fnd_global.prog_appl_id
501 , fnd_global.conc_program_id
502 , p_start_time
503 FROM poa_edw_po_dist_inc inc,
504 gl_sets_of_books gl
505 , po_distributions_all pod
506 , po_line_locations_all psc
507 , po_lines_all plc
508 , po_headers_all phc
509 , po_headers_all ga
510 , po_doc_style_headers style
511 , hr_organization_information hro
512 WHERE inc.primary_key = pod.PO_DISTRIBUTION_ID
513 and phc.po_header_id = plc.po_header_id
514 and plc.po_line_id = psc.po_line_id
515 and psc.line_location_id = pod.line_location_id
516 and psc.shipment_type = 'STANDARD'
517 and phc.style_id = style.style_id
518 and nvl(style.progress_payment_flag,'N') = 'N'
519 and psc.approved_flag = 'Y'
520 and plc.contract_id is NULL
521 AND plc.from_header_id = ga.po_header_id(+)
522 AND Nvl(ga.global_agreement_flag, 'N') = 'N'
523 and gl.set_of_books_id = pod.set_of_books_id
524 and plc.item_id is not null
525 and pod.creation_date is not null
526 and inc.batch_id = p_batch_no
527 and to_number(hro.organization_id) = psc.ship_to_organization_id
528 and hro.org_information_context = 'Accounting Information'
529 and nvl(pod.distribution_type,'-99') <> 'AGREEMENT'
530 and not exists (SELECT 'blanket item'
531 FROM po_lines_all pl
532 , po_headers_all ph
533 WHERE ph.type_lookup_code = 'BLANKET'
534 and ph.po_header_id = pl.po_header_id
535 and nvl(pl.unit_meas_lookup_code,
536 nvl(plc.unit_meas_lookup_code, '-1'))
537 = nvl(plc.unit_meas_lookup_code, '-1')
538 and pod.creation_date between
539 nvl(ph.start_date, pod.creation_date)
540 and nvl(ph.end_date, pod.creation_date)
541 and trunc(pod.creation_date) <= nvl(pl.expiration_date, pod.creation_date)
542 and pl.item_id = plc.item_id
543 and nvl(pl.item_revision, nvl(plc.item_revision, '-1'))
544 = nvl(plc.item_revision, '-1')
545 and (
546 (nvl(ph.global_agreement_flag,'N') = 'N'
547 and ph.org_id = to_number(hro.org_information3)
548 )
549 or
550 (ph.global_agreement_flag = 'Y'
551 and exists
552 (select 'enabled'
553 from po_ga_org_assignments poga
554 where poga.po_header_id = ph.po_header_id
555 and poga.enabled_flag = 'Y'
556 and ((poga.purchasing_org_id in
557 (select tfh.start_org_id
558 from mtl_transaction_flow_headers tfh,
559 financials_system_params_all fsp1,
560 financials_system_params_all fsp2
561 where pod.creation_date between nvl(tfh.start_date,pod.creation_date)
562 and nvl(tfh.end_date,pod.creation_date)
563 and tfh.flow_type = 2
564 and fsp1.org_id = tfh.start_org_id
565 and fsp1.purch_encumbrance_flag = 'N'
566 and fsp2.org_id = tfh.end_org_id
567 and fsp2.purch_encumbrance_flag = 'N'
568 and (
569 (tfh.qualifier_code is null) or
570 (tfh.qualifier_code = 1 and tfh.qualifier_value_id = plc.category_id)
571 )
572 and tfh.end_org_id = to_number(hro.org_information3)
573 and (
574 (tfh.organization_id = psc.ship_to_organization_id) or
575 (tfh.organization_id is null)
576 )
577 )
578 )
579 or poga.purchasing_org_id = to_number(hro.org_information3)
580 )
581 )
582 )
583 )
584 )
585 );
586
587
588 /* Go through all the rows which we considered as leakages to
589 * calculate their potential savings
590 */
591
592 POA_LOG.debug_line('Opening cursor c_std_po');
593 POA_LOG.debug_line(' ');
594
595 select sysdate into l_start_time from dual;
596 begin
597 select warehouse_currency_code,
598 rate_type
599 into
600 v_edw_global_currency_code,
601 v_edw_global_rate_type
602 from edw_local_system_parameters;
603 if( v_edw_global_currency_code is null
604 or v_edw_global_rate_type is null
605 )
606 then
607 v_edw_global_currency_code := 'USD';
608 v_edw_global_rate_type := 'Corporate';
609 end if;
610 exception
611 when others then
612 v_edw_global_currency_code := 'USD';
613 v_edw_global_rate_type := 'Corporate';
614 end;
615 FOR v_cursor_inst IN c_std_po(p_batch_no) LOOP
616
617 x_iteration := x_iteration + 1;
618
619 v_item_id := v_cursor_inst.item_id;
620 v_item_revision := v_cursor_inst.item_revision;
621 v_category_id := v_cursor_inst.category_id;
622 v_quantity := v_cursor_inst.quantity;
623 v_unit_meas_lookup_code := v_cursor_inst.unit_meas_lookup_code;
624 v_creation_date := v_cursor_inst.creation_date;
625 v_currency_code := v_cursor_inst.currency_code;
626 v_po_distribution_id := v_cursor_inst.po_distribution_id;
627 v_ship_to_location_id := v_cursor_inst.ship_to_location_id;
628 v_price_override := v_cursor_inst.price_override;
629 v_need_by_date := v_cursor_inst.need_by_date;
630 v_org_id := v_cursor_inst.org_id;
631 v_ship_to_organization_id := v_cursor_inst.ship_to_organization_id;
632 v_ship_to_ou := v_cursor_inst.org_information3;
633 v_rate_date := v_cursor_inst.rate_date;
634 --v_rate_type := v_cursor_inst.rate_type;
635
636 v_lowest_possible_price := poa_savings_sav.get_lowest_possible_price(
637 v_creation_date,
638 v_quantity,
639 v_unit_meas_lookup_code,
640 v_currency_code,
641 v_item_id,
642 v_item_revision,
643 v_category_id,
644 v_ship_to_location_id,
645 v_need_by_date,
646 v_org_id,
647 v_ship_to_organization_id,
648 v_ship_to_ou,
649 v_rate_date,
650 v_edw_global_rate_type,
651 v_edw_global_currency_code);
652
653 v_lowest_possible_price := v_lowest_possible_price
654 * get_currency_conv_rate(v_edw_global_currency_code,
655 v_currency_code,
656 v_rate_date,
657 v_edw_global_rate_type);
658 -- POA_LOG.debug_line('Lowest price determined using: creation_date=' ||
659 -- v_creation_date || ', quantity=' ||
660 -- v_quantity || ', unit of measure=' ||
661 -- v_unit_meas_lookup_code || ', currency=' ||
662 -- v_currency_code || ', item id=' ||
663 -- v_item_id || ', item revision=' ||
664 -- v_item_revision || ', category id=' ||
665 -- v_category_id || ', ship to location_id=' ||
666 -- v_ship_to_location_id);
667 -- POA_LOG.debug_line(' ');
668
669 poa_savings_np.insert_npcontract(v_po_distribution_id,
670 v_lowest_possible_price, p_start_time);
671
672 -- POA_LOG.debug_line('Inserting np contract for distribution_id=' ||
673 -- v_po_distribution_id || ', lowest possible price=' ||
674 -- v_lowest_possible_price);
675 -- POA_LOG.debug_line(' ');
676
677 END LOOP;
678
679 select sysdate into l_end_time from dual;
680 POA_LOG.put_line('Populate_npcontract: non one-time items with open blankets in batch '||p_batch_no || ' are ' || x_iteration);
681
682 POA_LOG.put_line('Populate_npcontract: time to calculate lowest price: '||
683 poa_log.duration(l_end_time - l_start_time) || ', start time: ' ||
684 to_char(l_start_time, 'MM/DD/YYYY HH24:MI:SS') || ', end time: ' ||
685 to_char(l_end_time, 'MM/DD/YYYY HH24:MI:SS'));
686
687 POA_LOG.debug_line('Populate_npcontract exit');
688 --
689 EXCEPTION
690 WHEN others THEN
691 v_buf := 'Non Contract function: ' || sqlcode || ': ' || sqlerrm || ': ' || x_progress;
692 ROLLBACK;
693
694 POA_LOG.put_line(v_buf);
695 POA_LOG.put_line(' ');
696
697 RAISE;
698 --
699 END populate_npcontract;
700
701 /*
702 NAME
703 insert_npcontract -
704 DESCRIPTION
705
706 */
707 --
708 PROCEDURE insert_npcontract (p_po_distribution_id IN NUMBER,
709 p_lowest_price IN NUMBER,
710 p_start_time IN DATE)
711 IS
712 --
713
714 v_npcontract_purchase_amount NUMBER;
715 v_npcontract_purchase_amount2 NUMBER;
716 v_potential_savings NUMBER;
717 v_quantity_ordered NUMBER;
718 v_po_distribution_id NUMBER;
719 v_type_lookup_code VARCHAR2(25) := NULL;
720 v_creation_date DATE;
721
722 v_item_id NUMBER;
723 v_item_description VARCHAR2(240) := NULL;
724 v_category_id NUMBER;
725 v_vendor_site_id NUMBER;
726 v_vendor_id NUMBER;
727 v_deliver_to_person_id NUMBER;
728 v_ship_to_location_id NUMBER;
729 v_ship_to_organization_id NUMBER;
730 v_org_id NUMBER;
731 v_agent_id NUMBER;
732 v_project_id NUMBER;
733 v_task_id NUMBER;
734 v_rate_type VARCHAR2(30) := NULL;
735 v_rate_date DATE;
736 v_rowcount BINARY_INTEGER := 0;
737 v_currency_code VARCHAR2(15) := NULL;
738 v_approved_date DATE;
739 v_rate NUMBER;
740 v_Currency_Conv_Rate NUMBER;
741 v_cost_center_id NUMBER;
742
743 v_buf VARCHAR2(240) := NULL;
744 x_progress VARCHAR2(3) := NULL;
745
746
747 BEGIN
748
749 POA_LOG.debug_line('Insert_npcontract: entered');
750 x_progress := '030';
751
752 SELECT decode(psc.consigned_flag, 'Y', null, decode(psc.closed_code, 'FINALLY_CLOSED',
753 (decode(sign(nvl(pod.quantity_delivered,0)
754 - nvl(pod.quantity_billed,0)),
755 1, nvl(pod.quantity_delivered,0), nvl(pod.quantity_billed,0)))
756 * nvl(psc.price_override,0) * nvl(pod.rate, 1),
757 (nvl(pod.quantity_ordered,0) - nvl(pod.quantity_cancelled,0))
758 * nvl(psc.price_override,0) * nvl(pod.rate,1)))
759 , decode(psc.consigned_flag, 'Y', null, decode(psc.closed_code, 'FINALLY_CLOSED',
760 (decode(sign(nvl(pod.quantity_delivered,0)
761 - nvl(pod.quantity_billed,0)),
762 1, nvl(pod.quantity_delivered,0), nvl(pod.quantity_billed,0)))
763 * (nvl(psc.price_override,0)-(decode(nvl(p_lowest_price,0),0,
764 nvl(psc.price_override,0),
765 nvl(p_lowest_price,0))))
766 * nvl(pod.rate,1),
767 (nvl(pod.quantity_ordered,0) - nvl(pod.quantity_cancelled,0))
768 * (nvl(psc.price_override,0)-(decode(nvl(p_lowest_price,0), 0,
769 nvl(psc.price_override,0),
770 nvl(p_lowest_price,0))))
771 * nvl(pod.rate,1)))
772 , decode(psc.consigned_flag, 'Y', null, pod.quantity_ordered)
773 , pod.po_distribution_id
774 , phc.type_lookup_code
775 , pod.creation_date
776 , plc.item_id
777 , plc.item_description
778 , plc.category_id
779 , phc.vendor_site_id
780 , phc.vendor_id
781 , pod.deliver_to_person_id
782 , psc.ship_to_location_id
783 , psc.ship_to_organization_id
784 , psc.org_id
785 , phc.agent_id
786 , pod.project_id
787 , pod.task_id
788 , gl.currency_code
789 , phc.rate_type
790 , nvl(phc.rate_date, pod.creation_date)
791 , pod.code_combination_id
792 , pod.rate
793 , NVL(POA_OLTP_GENERIC_PKG.get_approved_date_poh(pod.creation_date, phc.po_header_id),
794 phc.approved_date)
795 , POA_CURRENCY_PKG.get_global_currency_rate (phc.rate_type,
796 decode(phc.rate_type, 'User', gl.currency_code,
797 NVL(phc.currency_code,gl.currency_code)),
798 NVL(pod.rate_date, pod.creation_date), phc.rate)
799 INTO v_npcontract_purchase_amount,
800 v_potential_savings,
801 v_quantity_ordered,
802 v_po_distribution_id,
803 v_type_lookup_code,
804 v_creation_date,
805 v_item_id,
806 v_item_description,
807 v_category_id,
808 v_vendor_site_id,
809 v_vendor_id,
810 v_deliver_to_person_id,
811 v_ship_to_location_id,
812 v_ship_to_organization_id,
813 v_org_id,
814 v_agent_id,
815 v_project_id,
816 v_task_id,
817 v_currency_code,
818 v_rate_type,
819 v_rate_date,
820 v_cost_center_id,
821 v_rate,
822 v_approved_date,
823 v_Currency_Conv_Rate
824 FROM gl_sets_of_books gl
825 , po_distributions_all pod
826 , po_line_locations_all psc
827 , po_lines_all plc
828 , po_headers_all phc
829 WHERE pod.po_distribution_id = p_po_distribution_id
830 and pod.line_location_id = psc.line_location_id
831 and psc.po_line_id = plc.po_line_id
832 and plc.po_header_id = phc.po_header_id
833 and gl.set_of_books_id = pod.set_of_books_id
834 and nvl(pod.distribution_type,'-99') <> 'AGREEMENT';
835
836
837 x_progress := '040';
838
839 SELECT count(*) INTO v_rowcount FROM poa_bis_savings
840 WHERE distribution_transaction_id = v_po_distribution_id;
841
842 --If a row already exists, then delete it and reinsert the
843 --row with the updated information
844
845 IF (v_rowcount > 0) THEN
846 -- POA_LOG.debug_line(' v_rowcount is: ' || v_rowcount);
847
848 x_progress := '050';
849 DELETE FROM poa_bis_savings
850 WHERE distribution_transaction_id = v_po_distribution_id;
851 END IF;
852
853 x_progress := '060';
854
855 if (v_npcontract_purchase_amount IS NOT NULL) then
856 v_npcontract_purchase_amount2 := 0;
857 else
858 v_npcontract_purchase_amount2 := NULL;
859 end if;
860
861 INSERT INTO poa_bis_savings
862 ( purchase_amount
863 , contract_amount
864 , non_contract_amount
865 , pot_contract_amount
866 , potential_saving
867 , total_purchase_qty
868 , distribution_transaction_id
869 , document_type_code
870 , purchase_creation_date
871 , item_id
872 , item_description
873 , category_id
874 , supplier_site_id
875 , supplier_id
876 , requestor_id
877 , ship_to_location_id
878 , ship_to_organization_id
879 , operating_unit_id
880 , buyer_id
881 , project_id
882 , task_id
883 , currency_code
884 , rate_type
885 , rate_date
886 , cost_center_id
887 , account_id
888 , company_id
889 , rate
890 , approved_date
891 , Currency_Conv_Rate
892 , created_by
893 , creation_date
894 , last_updated_by
895 , last_update_date
896 , last_update_login
897 , request_id
898 , program_application_id
899 , program_id
900 , program_update_date)
901 VALUES
902 (v_npcontract_purchase_amount
903 ,v_npcontract_purchase_amount2
904 ,v_npcontract_purchase_amount2
905 ,v_npcontract_purchase_amount
906 ,v_potential_savings
907 ,v_quantity_ordered
908 ,v_po_distribution_id
909 ,v_type_lookup_code
910 ,v_creation_date
911 ,v_item_id
912 ,v_item_description
913 ,v_category_id
914 ,v_vendor_site_id
915 ,v_vendor_id
916 ,v_deliver_to_person_id
917 ,v_ship_to_location_id
918 ,v_ship_to_organization_id
919 ,v_org_id
920 ,v_agent_id
921 ,v_project_id
922 ,v_task_id
923 ,v_currency_code
924 ,v_rate_type
925 ,v_rate_date
926 ,v_cost_center_id
927 ,NULL
928 ,NULL
929 ,v_rate
930 ,v_approved_date
931 ,v_Currency_Conv_Rate
932 ,fnd_global.user_id
933 ,p_start_time
934 ,fnd_global.user_id
935 ,p_start_time
936 ,fnd_global.login_id
937 ,fnd_global.conc_request_id
938 ,fnd_global.prog_appl_id
939 ,fnd_global.conc_program_id
940 ,p_start_time);
941
942
943 --
944 EXCEPTION
945 WHEN others THEN
946 v_buf := 'Insert non contract function: ' || sqlcode || ': ' || sqlerrm || ': ' || x_progress;
947
948 ROLLBACK;
949 POA_LOG.put_line(v_buf);
950 POA_LOG.put_line(' ');
951
952 RAISE;
953 END insert_npcontract;
954
955 FUNCTION get_currency_conv_rate (p_from_currency_code po_headers_all.currency_code%type,
956 p_to_currency_code VARCHAR2,
957 p_rate_date DATE,
958 p_rate_type edw_local_system_parameters.rate_type%type) RETURN NUMBER
959 IS
960 BEGIN
961
962 if(p_from_currency_code = l_from_currency_code
963 and p_to_currency_code = l_to_currency_code
964 and p_rate_date = l_curr_conv_rate_date
965 and p_rate_type = l_curr_conv_rate_type)
966 then
967 return l_currency_conv_rate;
968 else
969 l_from_currency_code := p_from_currency_code;
970 l_to_currency_code := p_to_currency_code;
971 l_curr_conv_rate_date := p_rate_date;
972 l_curr_conv_rate_type := p_rate_type;
973 l_currency_conv_rate := gl_currency_api.get_rate_sql(l_from_currency_code,
974 l_to_currency_code,
975 l_curr_conv_rate_date,
976 l_curr_conv_rate_type
977 );
978 return l_currency_conv_rate;
979 end if;
980
981 END get_currency_conv_rate;
982
983 END poa_savings_np;