[Home] [Help]
PACKAGE BODY: APPS.POA_DBI_PO_DIST_F_C
Source
1 PACKAGE BODY POA_DBI_PO_DIST_F_C AS
2 /* $Header: poadbipodfrefb.pls 120.23.12000000.2 2007/08/13 10:01:47 kalakshm ship $ */
3 g_init boolean := false;
4
5 /* PUBLIC PROCEDURE */
6 PROCEDURE initial_load (errbuf OUT NOCOPY VARCHAR2,
7 retcode OUT NOCOPY NUMBER)
8 IS
9 l_poa_schema VARCHAR2(30);
10 l_status VARCHAR2(30);
11 l_industry VARCHAR2(30);
12
13 l_stmt VARCHAR2(4000);
14 BEGIN
15 IF (FND_INSTALLATION.GET_APP_INFO('POA', l_status, l_industry, l_poa_schema)) THEN
16 l_stmt := 'TRUNCATE TABLE ' || l_poa_schema || '.POA_DBI_POD_F';
17 EXECUTE IMMEDIATE l_stmt;
18
19 l_stmt := 'TRUNCATE TABLE ' || l_poa_schema || '.POA_DBI_POD_INC';
20 EXECUTE IMMEDIATE l_stmt;
21
22 l_stmt := 'TRUNCATE TABLE ' || l_poa_schema || '.POA_DBI_POD_RATES';
23 EXECUTE IMMEDIATE l_stmt;
24
25 l_stmt := 'TRUNCATE TABLE ' || l_poa_schema || '.POA_DBI_NEG_PO_RATES';
26 EXECUTE IMMEDIATE l_stmt;
27
28 l_stmt := 'TRUNCATE TABLE ' || l_poa_schema || '.POA_DBI_NEG_DETAILS';
29 EXECUTE IMMEDIATE l_stmt;
30
31 l_stmt := 'TRUNCATE TABLE ' || l_poa_schema || '.POA_DBI_POD_MATCH_TEMP';
32 EXECUTE IMMEDIATE l_stmt;
33
34 l_stmt := 'TRUNCATE TABLE ' || l_poa_schema || '.POA_DBI_POD_LOWEST_ALL_TEMP';
35 EXECUTE IMMEDIATE l_stmt;
36
37 l_stmt := 'TRUNCATE TABLE ' || l_poa_schema || '.POA_DBI_POD_LOWEST_PRICE_TEMP';
38 EXECUTE IMMEDIATE l_stmt;
39
40 g_init := true;
41 populate_po_dist_facts (errbuf, retcode);
42 END IF;
43
44 EXCEPTION
45 WHEN OTHERS THEN
46 Errbuf:= Sqlerrm;
47 Retcode:=sqlcode;
48
49 ROLLBACK;
50 POA_LOG.debug_line('initial_load' || Sqlerrm || sqlcode || sysdate);
51 RAISE_APPLICATION_ERROR(-20000,'Stack Dump Follows =>', true);
52
53 END initial_load;
54
55
56
57 /* PUBLIC PROCEDURE */
58 PROCEDURE populate_po_dist_facts (errbuf OUT NOCOPY VARCHAR2,
59 retcode OUT NOCOPY NUMBER)
60 IS
61 l_no_batch NUMBER;
62 l_go_ahead BOOLEAN := false;
63 l_count NUMBER := 0;
64
65 l_poa_schema VARCHAR2(30);
66 l_status VARCHAR2(30);
67 l_industry VARCHAR2(30);
68 l_sec_cur_yn number;
69 l_stmt varchar2(4000);
70 l_start_date VARCHAR2(22);
71 l_end_date varchar2(22);
72 l_glob_date VARCHAR2(22);
73 /*
74 fnd_date.initialize('YYYY/MM/DD', 'YYYY/MM/DD HH24:MI:SS');
75 l_from_date := fnd_date.displayDT_to_date(p_from_date);
76 l_to_date := fnd_date.displayDT_to_date(p_to_date);
77 */
78 l_ret number;
79 l_batch_size NUMBER;
80 l_start_time DATE;
81 l_login number;
82 l_user number;
83 l_dop NUMBER := 1;
84 d_start_date DATE;
85 d_end_date DATE;
86 d_glob_date DATE;
87 l_rate_type VARCHAR2(30);
88 l_srate_type varchar2(30);
89 l_global_cur_code gl_sets_of_books.currency_code%type;
90 l_sglobal_cur_code gl_sets_of_books.currency_code%type;
91 BEGIN
92 Errbuf :=NULL;
93 Retcode:=0;
94 l_global_cur_code := bis_common_parameters.get_currency_code;
95 l_sglobal_cur_code := bis_common_parameters.get_secondary_currency_code;
96 l_srate_type := bis_common_parameters.get_secondary_rate_type;
97 l_batch_size := bis_common_parameters.get_batch_size(10);
98 l_rate_type := bis_common_parameters.get_rate_type;
99 if(poa_currency_pkg.display_secondary_currency_yn)
100 then
101 l_sec_cur_yn := 1;
102 else
103 l_sec_cur_yn := 0;
104 end if;
105
106 DBMS_APPLICATION_INFO.SET_MODULE(module_name => 'DBI POD COLLECT', action_name => 'start');
107 l_dop := bis_common_parameters.get_degree_of_parallelism;
108 -- default DOP to profile in EDW_PARALLEL_SRC if 2nd param is not passed
109 l_go_ahead := bis_collection_utilities.setup('POAPODIST');
110 if (g_init) then
111 execute immediate 'alter session set hash_area_size=104857600';
112 execute immediate 'alter session set sort_area_size=104857600';
113 -- execute immediate 'alter session disable parallel dml' ;
114 end if;
115 IF (NOT l_go_ahead) THEN
116 errbuf := fnd_message.get;
117 RAISE_APPLICATION_ERROR (-20000, 'Error in SETUP: ' || errbuf);
118 END IF;
119 bis_collection_utilities.g_debug := FALSE;
120
121
122 IF(g_init) THEN
123 l_start_date := To_char(bis_common_parameters.get_global_start_date
124 , 'YYYY/MM/DD HH24:MI:SS');
125 d_start_date := bis_common_parameters.get_global_start_date;
126 ELSE
127 l_start_date := To_char(fnd_date.displaydt_to_date(bis_collection_utilities.get_last_refresh_period('POAPODIST')) - 0.004,'YYYY/MM/DD HH24:MI:SS');
128 /* note that if there is not a success record in the log, we should get global start date as l_start_date */
129 d_start_date := fnd_date.displaydt_to_date(bis_collection_utilities.get_last_refresh_period('POAPODIST')) - 0.004;
130 END IF;
131
132
133 l_end_date := To_char(Sysdate, 'YYYY/MM/DD HH24:MI:SS');
134 d_end_date := Sysdate;
135
136 bis_collection_utilities.log( 'The collection range is from '||
137 l_start_date ||' to '|| l_end_date, 0);
138
139
140 IF (l_batch_size IS NULL) THEN
141 l_batch_size := 10000;
142 END if;
143
144 bis_collection_utilities.log('Truncate INC table: '|| 'Sysdate=' ||To_char(Sysdate, 'DD/MM/YYYY HH24:MI:SS'), 0);
145 IF (NOT(FND_INSTALLATION.GET_APP_INFO('POA', l_status, l_industry, l_poa_schema))) THEN
146 bis_collection_utilities.log('Error getting app info '|| 'Sysdate=' ||To_char(Sysdate, 'DD/MM/YYYY HH24:MI:SS'), 0);
147 RAISE_APPLICATION_ERROR (-20000, 'Error in GET_APP_INFO: ' || errbuf);
148 END IF;
149 l_stmt := 'TRUNCATE TABLE ' || l_poa_schema || '.POA_DBI_POD_INC';
150 EXECUTE IMMEDIATE l_stmt;
151 l_stmt := 'TRUNCATE TABLE ' || l_poa_schema || '.POA_DBI_POD_RATES';
152 EXECUTE IMMEDIATE l_stmt;
153 l_stmt := 'TRUNCATE TABLE ' || l_poa_schema || '.POA_DBI_NEG_PO_RATES';
154 EXECUTE IMMEDIATE l_stmt;
155 l_stmt := 'TRUNCATE TABLE ' || l_poa_schema || '.POA_DBI_NEG_DETAILS';
156 EXECUTE IMMEDIATE l_stmt;
157
158 DBMS_APPLICATION_INFO.SET_ACTION('inc');
159 bis_collection_utilities.log('Populate INC table '|| 'Sysdate=' ||To_char(Sysdate, 'DD/MM/YYYY HH24:MI:SS'), 0);
160 l_glob_date := To_char(bis_common_parameters.get_global_start_date, 'YYYY/MM/DD HH24:MI:SS');
161 d_glob_date := bis_common_parameters.get_global_start_date;
162
163 if(g_init) then
164 insert /*+ append parallel(poa_dbi_pod_inc) */ into poa_dbi_pod_inc
165 ( primary_key,
166 global_cur_conv_rate,
167 batch_id,
168 func_cur_code,
169 txn_cur_code,
170 rate_date
171 )
172 ( select
173 po_distribution_id primary_key,
174 null global_cur_conv_rate,
175 1 batch_id,
176 func_cur_code,
177 txn_cur_code,
178 rate_date
179 from
180 (
181 (
182 select /*+ parallel(pol) parallel(pll) parallel(poh) parallel(pod) parallel(poa_gl)
183 NO_MERGE USE_HASH(pol) use_hash(pll) use_hash(poh) use_hash(pod) use_hash(poa_gl)*/
184 pod.po_distribution_id,
185 poa_gl.currency_code func_cur_code,
186 poh.currency_code txn_cur_code,
187 trunc(nvl(pod.rate_date, pod.creation_date)) rate_date
188 from
189 po_lines_all pol,
190 po_line_locations_all pll,
191 po_headers_all poh,
192 po_distributions_all pod,
193 gl_sets_of_books poa_gl
194 where pod.line_location_id = pll.line_location_id
195 and pod.po_line_id = pol.po_line_id
196 and pod.po_header_id = poh.po_header_id
197 and pll.shipment_type in ('STANDARD','PREPAYMENT')
198 and pll.approved_flag = 'Y'
199 and nvl(pod.distribution_type,'-99') <> 'AGREEMENT'
200 and poa_gl.set_of_books_id = pod.set_of_books_id
201 and pod.creation_date >= d_glob_date
202 and ( pol.last_update_date between d_start_date and d_end_date or
203 pll.last_update_date between d_start_date and d_end_date or
204 poh.last_update_date between d_start_date and d_end_date or
205 pod.last_update_date between d_start_date and d_end_date )
206 )
207 union all
208 (
209 select /*+ parallel(pol) parallel(pll) parallel(poh) parallel(por) parallel(pod) parallel(poa_gl)
210 NO_MERGE USE_HASH(pol) use_hash(pll) use_hash(poh) use_hash(por) use_hash(pod) use_hash(poa_gl) */
211 pod.po_distribution_id,
212 poa_gl.currency_code func_cur_code,
213 poh.currency_code txn_cur_code,
214 trunc(nvl(pod.rate_date, pod.creation_date)) rate_date
215 from
216 po_lines_all pol,
217 po_line_locations_all pll,
218 po_headers_all poh,
219 po_releases_all por,
220 po_distributions_all pod,
221 gl_sets_of_books poa_gl
222 where pod.line_location_id = pll.line_location_id
223 and pod.po_release_id = por.po_release_id
224 and pod.po_line_id = pol.po_line_id
225 and pod.po_header_id = poh.po_header_id
226 and pll.shipment_type in ('BLANKET', 'SCHEDULED')
227 and pll.approved_flag = 'Y'
228 and nvl(pod.distribution_type,'-99') <> 'AGREEMENT'
229 and poa_gl.set_of_books_id = pod.set_of_books_id
230 and pod.creation_date >= d_glob_date
231 and ( pol.last_update_date between d_start_date and d_end_date or
232 pll.last_update_date between d_start_date and d_end_date or
233 poh.last_update_date between d_start_date and d_end_date or
234 pod.last_update_date between d_start_date and d_end_date or
235 por.last_update_date between d_start_date and d_end_date)
236 )
237 )
238 );
239
240 else -- not initial load
241
242 insert /*+ append */ into
243 poa_dbi_pod_inc
244 (
245 primary_key,
246 global_cur_conv_rate,
247 batch_id,
248 func_cur_code,
249 txn_cur_code,
250 rate_date
251 )
252 ( select
253 primary_key,
254 null global_cur_conv_rate,
255 ceil(rownum/l_batch_size) batch_id,
256 func_cur_code,
257 txn_cur_code,
258 rate_date
259 from
260 (
261 (
262 select /*+ cardinality(pol, 1)*/
263 pod.po_distribution_id primary_key,
264 poa_gl.currency_code func_cur_code,
265 poh.currency_code txn_cur_code,
266 trunc(nvl(pod.rate_date, pod.creation_date)) rate_date
267 from
268 po_lines_all pol,
269 po_line_locations_all pll,
270 po_headers_all poh,
271 po_distributions_all pod,
272 gl_sets_of_books poa_gl
273 where pod.line_location_id = pll.line_location_id
274 and pod.po_line_id = pol.po_line_id
275 and pod.po_header_id = poh.po_header_id
276 and pll.shipment_type in ('STANDARD','PREPAYMENT')
277 and pll.approved_flag = 'Y'
278 and nvl(pod.distribution_type,'-99') <> 'AGREEMENT'
279 and poa_gl.set_of_books_id = pod.set_of_books_id
280 and pod.creation_date >= d_glob_date
281 and pol.last_update_date between d_start_date and d_end_date
282 UNION
283 select /*+ cardinality(pll, 1)*/
284 pod.po_distribution_id primary_key,
285 poa_gl.currency_code func_cur_code,
286 poh.currency_code txn_cur_code,
287 trunc(nvl(pod.rate_date, pod.creation_date)) rate_date
288 from
289 po_line_locations_all pll,
290 po_headers_all poh,
291 po_distributions_all pod,
292 gl_sets_of_books poa_gl
293 where pod.line_location_id = pll.line_location_id
294 and pod.po_header_id = poh.po_header_id
295 and pll.shipment_type in ('STANDARD','PREPAYMENT')
296 and pll.approved_flag = 'Y'
297 and nvl(pod.distribution_type,'-99') <> 'AGREEMENT'
298 and poa_gl.set_of_books_id = pod.set_of_books_id
299 and pod.creation_date >= d_glob_date
300 and pll.last_update_date between d_start_date and d_end_date
301 UNION
302 select /*+ cardinality(poh, 1)*/
303 pod.po_distribution_id primary_key,
304 poa_gl.currency_code func_cur_code,
305 poh.currency_code txn_cur_code,
306 trunc(nvl(pod.rate_date, pod.creation_date)) rate_date
307 from
308 po_line_locations_all pll,
309 po_headers_all poh,
310 po_distributions_all pod,
311 gl_sets_of_books poa_gl
312 where pod.line_location_id = pll.line_location_id
313 and pod.po_header_id = poh.po_header_id
314 and pll.shipment_type in ('STANDARD','PREPAYMENT')
315 and pll.approved_flag = 'Y'
316 and nvl(pod.distribution_type,'-99') <> 'AGREEMENT'
317 and poa_gl.set_of_books_id = pod.set_of_books_id
318 and pod.creation_date >= d_glob_date
319 and poh.last_update_date between d_start_date and d_end_date
320 UNION
321 select /*+ cardinality(pod, 1)*/
322 pod.po_distribution_id primary_key,
323 poa_gl.currency_code func_cur_code,
324 poh.currency_code txn_cur_code,
325 trunc(nvl(pod.rate_date, pod.creation_date)) rate_date
326 from
327 po_line_locations_all pll,
328 po_headers_all poh,
329 po_distributions_all pod,
330 gl_sets_of_books poa_gl
331 where pod.line_location_id = pll.line_location_id
332 and pod.po_header_id = poh.po_header_id
333 and pll.shipment_type in ('STANDARD','PREPAYMENT')
334 and pll.approved_flag = 'Y'
335 and nvl(pod.distribution_type,'-99') <> 'AGREEMENT'
336 and poa_gl.set_of_books_id = pod.set_of_books_id
337 and pod.creation_date >= d_glob_date
338 and pod.last_update_date between d_start_date and d_end_date
339 )
340 union all
341 (
342 select /*+ cardinality(pol, 1)*/
343 pod.po_distribution_id primary_key,
344 poa_gl.currency_code func_cur_code,
345 poh.currency_code txn_cur_code,
346 trunc(nvl(pod.rate_date, pod.creation_date)) rate_date
347 from
348 po_lines_all pol,
349 po_line_locations_all pll,
350 po_headers_all poh,
351 po_distributions_all pod,
352 gl_sets_of_books poa_gl
353 where pod.line_location_id = pll.line_location_id
354 and pod.po_line_id = pol.po_line_id
355 and pod.po_header_id = poh.po_header_id
356 and pll.shipment_type in ('BLANKET', 'SCHEDULED')
357 and pll.approved_flag = 'Y'
358 and nvl(pod.distribution_type,'-99') <> 'AGREEMENT'
359 and poa_gl.set_of_books_id = pod.set_of_books_id
360 and pod.creation_date >= d_glob_date
361 and pol.last_update_date between d_start_date and d_end_date
362 UNION
363 select /*+ cardinality(pll, 1)*/
364 pod.po_distribution_id primary_key,
365 poa_gl.currency_code func_cur_code,
366 poh.currency_code txn_cur_code,
367 trunc(nvl(pod.rate_date, pod.creation_date)) rate_date
368 from
369 po_line_locations_all pll,
370 po_headers_all poh,
371 po_distributions_all pod,
372 gl_sets_of_books poa_gl
373 where pod.line_location_id = pll.line_location_id
374 and pod.po_header_id = poh.po_header_id
375 and pll.shipment_type in ('BLANKET', 'SCHEDULED')
376 and pll.approved_flag = 'Y'
377 and nvl(pod.distribution_type,'-99') <> 'AGREEMENT'
378 and poa_gl.set_of_books_id = pod.set_of_books_id
379 and pod.creation_date >= d_glob_date
380 and pll.last_update_date between d_start_date and d_end_date
381 UNION
382 select /*+ cardinality(poh, 1)*/
383 pod.po_distribution_id primary_key,
384 poa_gl.currency_code func_cur_code,
385 poh.currency_code txn_cur_code,
386 trunc(nvl(pod.rate_date, pod.creation_date)) rate_date
387 from
388 po_line_locations_all pll,
389 po_headers_all poh,
390 po_distributions_all pod,
391 gl_sets_of_books poa_gl
392 where pod.line_location_id = pll.line_location_id
393 and pod.po_header_id = poh.po_header_id
394 and pll.shipment_type in ('BLANKET', 'SCHEDULED')
395 and pll.approved_flag = 'Y'
396 and nvl(pod.distribution_type,'-99') <> 'AGREEMENT'
397 and poa_gl.set_of_books_id = pod.set_of_books_id
398 and pod.creation_date >= d_glob_date
399 and poh.last_update_date between d_start_date and d_end_date
400 UNION
401 select /*+ cardinality(pod, 1)*/
402 pod.po_distribution_id primary_key,
403 poa_gl.currency_code func_cur_code,
404 poh.currency_code txn_cur_code,
405 trunc(nvl(pod.rate_date, pod.creation_date)) rate_date
406 from
407 po_line_locations_all pll,
408 po_headers_all poh,
409 po_distributions_all pod,
410 gl_sets_of_books poa_gl
411 where pod.line_location_id = pll.line_location_id
412 and pod.po_header_id = poh.po_header_id
413 and pll.shipment_type in ('BLANKET', 'SCHEDULED')
414 and pll.approved_flag = 'Y'
415 and nvl(pod.distribution_type,'-99') <> 'AGREEMENT'
416 and poa_gl.set_of_books_id = pod.set_of_books_id
417 and pod.creation_date >= d_glob_date
418 and pod.last_update_date between d_start_date and d_end_date
419 UNION
420 select /*+ cardinality(por, 1)*/
421 pod.po_distribution_id primary_key,
422 poa_gl.currency_code func_cur_code,
423 poh.currency_code txn_cur_code,
424 trunc(nvl(pod.rate_date, pod.creation_date)) rate_date
425 from
426 po_line_locations_all pll,
427 po_headers_all poh,
428 po_releases_all por,
429 po_distributions_all pod,
430 gl_sets_of_books poa_gl
431 where pod.line_location_id = pll.line_location_id
432 and pod.po_release_id = por.po_release_id
433 and pod.po_header_id = poh.po_header_id
434 and pll.shipment_type in ('BLANKET', 'SCHEDULED')
435 and pll.approved_flag = 'Y'
436 and nvl(pod.distribution_type,'-99') <> 'AGREEMENT'
437 and poa_gl.set_of_books_id = pod.set_of_books_id
438 and pod.creation_date >= d_glob_date
439 and por.last_update_date between d_start_date and d_end_date
440 )
441 )
442 );
443
444 end if;
445
446 COMMIT;
447
448 DBMS_APPLICATION_INFO.SET_ACTION('stats incremental');
449
450 fnd_stats.gather_table_stats(OWNNAME => l_poa_schema, TABNAME => 'POA_DBI_POD_INC');
451
452 insert /*+ APPEND */ into poa_dbi_pod_rates
453 (
454 txn_cur_code,
455 func_cur_code,
456 rate_date,
457 global_cur_conv_rate,
458 sglobal_cur_conv_rate
459 )
460 select
461 txn_cur_code,
462 func_cur_code,
463 rate_date,
464 poa_currency_pkg.get_dbi_global_rate(
465 l_rate_type,
466 func_cur_code,
467 rate_date,
468 txn_cur_code
469 ) global_cur_conv_rate,
470 ( case when l_sec_cur_yn = 0
471 then null
472 else poa_currency_pkg.get_dbi_sglobal_rate(
473 l_srate_type,
474 func_cur_code,
475 rate_date,
476 txn_cur_code
477 )
478 end
479 ) sglobal_cur_conv_rate
480 from
481 (
482 select distinct
483 txn_cur_code,
484 func_cur_code,
485 rate_date
486 from poa_dbi_pod_inc
487 order by func_cur_code, rate_date
488 );
489
490 COMMIT;
491
492 if(g_init) then
493 insert /*+ APPEND PARALLEL*/ into poa_dbi_neg_po_rates
494 (
495 txn_cur_code,
496 func_cur_code,
497 rate_date,
498 global_cur_conv_rate,
499 sglobal_cur_conv_rate
500 )
501 select
502 txn_cur_code,
503 func_cur_code,
504 rate_date,
505 poa_currency_pkg.get_dbi_global_rate(
506 l_rate_type,
507 func_cur_code,
508 rate_date,
509 txn_cur_code
510 ) global_cur_conv_rate,
511 ( case when l_sec_cur_yn = 0
512 then null
513 else poa_currency_pkg.get_dbi_sglobal_rate(
514 l_srate_type,
515 func_cur_code,
516 rate_date,
517 txn_cur_code
518 )
519 end
520 ) sglobal_cur_conv_rate
521 from
522 (
523 select /*+ parallel(pod) parallel(pol) parallel(ponh) parallel(ponbh) */
524 distinct ponh.currency_code txn_cur_code,
525 pgl.currency_code func_cur_code,
526 nvl(trunc(ponh.rate_date), trunc(ponh.creation_date)) rate_date
527 from poa_dbi_pod_inc inc,
528 po_distributions_all pod,
529 po_lines_all pol,
530 pon_bid_headers ponbh,
531 pon_auction_headers_all ponh,
532 financials_system_params_all pfsp,
533 gl_sets_of_books pgl
534 where pod.po_distribution_id = inc.primary_key
535 and pod.po_line_id = pol.po_line_id
536 and pol.auction_header_id = ponbh.auction_header_id
537 and ponbh.auction_header_id = ponh.auction_header_id
538 and ponh.contract_type = 'STANDARD'
539 and ponh.org_id = pfsp.org_id
540 and pfsp.set_of_books_id = pgl.set_of_books_id
541 and ponh.creation_date >= d_glob_date
542 union
543 select /*+ parallel(pod) parallel(pol) parallel(ponh) parallel(ponbh) */
544 distinct ponh.currency_code txn_cur_code,
545 pgl.currency_code func_cur_code,
546 nvl(trunc(ponh.rate_date), trunc(ponh.creation_date)) rate_date
547 from poa_dbi_pod_inc inc,
548 po_distributions_all pod,
549 po_lines_all pol,
550 pon_bid_headers ponbh,
551 pon_auction_headers_all ponh,
552 financials_system_params_all pfsp,
553 gl_sets_of_books pgl
554 where pod.po_distribution_id = inc.primary_key
555 and pod.po_line_id = pol.po_line_id
556 and pol.contract_id = ponbh.po_header_id
557 and ponbh.auction_header_id = ponh.auction_header_id
558 and ponh.contract_type = 'CONTRACT'
559 and ponh.org_id = pfsp.org_id
560 and pfsp.set_of_books_id = pgl.set_of_books_id
561 and ponh.creation_date >= d_glob_date
562 union
563 select /*+ parallel(pod) parallel(pol) parallel(ponh) parallel(ponbh) */
564 distinct ponh.currency_code txn_cur_code,
565 pgl.currency_code func_cur_code,
566 nvl(trunc(ponh.rate_date), trunc(ponh.creation_date)) rate_date
567 from
568 poa_dbi_pod_inc inc,
569 po_distributions_all pod,
570 po_lines_all pol,
571 pon_bid_headers ponbh,
572 pon_auction_headers_all ponh,
573 financials_system_params_all pfsp,
574 gl_sets_of_books pgl
575 where pod.po_distribution_id = inc.primary_key
576 and pod.po_line_id = pol.po_line_id
577 and pol.from_header_id = ponbh.po_header_id
578 and ponbh.auction_header_id = ponh.auction_header_id
579 and ponh.org_id = pfsp.org_id
580 and pfsp.set_of_books_id = pgl.set_of_books_id
581 and ponh.creation_date >= d_glob_date
582 order by func_cur_code,
583 rate_date );
584
585 else --not initial load
586 insert /*+ APPEND */ into poa_dbi_neg_po_rates
587 (
588 txn_cur_code,
589 func_cur_code,
590 rate_date,
591 global_cur_conv_rate,
592 sglobal_cur_conv_rate
593 )
594 select
595 txn_cur_code,
596 func_cur_code,
597 rate_date,
598 poa_currency_pkg.get_dbi_global_rate(
599 l_rate_type,
600 func_cur_code,
601 rate_date,
602 txn_cur_code
603 ) global_cur_conv_rate,
604 ( case when l_sec_cur_yn = 0
605 then null
606 else poa_currency_pkg.get_dbi_sglobal_rate(
607 l_srate_type,
608 func_cur_code,
609 rate_date,
610 txn_cur_code
611 )
612 end
613 ) sglobal_cur_conv_rate
614 from
615 (
616 select /*+ leading(inc,pod) cardinality(inc,1) */
617 distinct ponh.currency_code txn_cur_code,
618 pgl.currency_code func_cur_code,
619 nvl(trunc(ponh.rate_date), trunc(ponh.creation_date)) rate_date
620 from poa_dbi_pod_inc inc,
621 po_distributions_all pod,
622 po_lines_all pol,
623 pon_bid_headers ponbh,
624 pon_auction_headers_all ponh,
625 financials_system_params_all pfsp,
626 gl_sets_of_books pgl
627 where pod.po_distribution_id = inc.primary_key
628 and pod.po_line_id = pol.po_line_id
629 and pol.auction_header_id = ponbh.auction_header_id
630 and ponbh.auction_header_id = ponh.auction_header_id
631 and ponh.contract_type = 'STANDARD'
632 and ponh.org_id = pfsp.org_id
633 and pfsp.set_of_books_id = pgl.set_of_books_id
634 and ponh.creation_date >= d_glob_date
635 union
636 select /*+ leading(inc,pod) cardinality(inc,100) */
637 distinct ponh.currency_code txn_cur_code,
638 pgl.currency_code func_cur_code,
639 nvl(trunc(ponh.rate_date), trunc(ponh.creation_date)) rate_date
640 from poa_dbi_pod_inc inc,
641 po_distributions_all pod,
642 po_lines_all pol,
643 pon_bid_headers ponbh,
644 pon_auction_headers_all ponh,
645 financials_system_params_all pfsp,
646 gl_sets_of_books pgl
647 where pod.po_distribution_id = inc.primary_key
648 and pod.po_line_id = pol.po_line_id
649 and pol.contract_id = ponbh.po_header_id
650 and ponbh.auction_header_id = ponh.auction_header_id
651 and ponh.contract_type = 'CONTRACT'
652 and ponh.org_id = pfsp.org_id
653 and pfsp.set_of_books_id = pgl.set_of_books_id
654 and ponh.creation_date >= d_glob_date
655 union
656 select /*+ leading(inc,pod) cardinality(inc,1) */
657 distinct ponh.currency_code txn_cur_code,
658 pgl.currency_code func_cur_code,
659 nvl(trunc(ponh.rate_date), trunc(ponh.creation_date)) rate_date
660 from
661 poa_dbi_pod_inc inc,
662 po_distributions_all pod,
663 po_lines_all pol,
664 pon_bid_headers ponbh,
665 pon_auction_headers_all ponh,
666 financials_system_params_all pfsp,
667 gl_sets_of_books pgl
668 where pod.po_distribution_id = inc.primary_key
669 and pod.po_line_id = pol.po_line_id
670 and pol.from_header_id = ponbh.po_header_id
671 and ponbh.auction_header_id = ponh.auction_header_id
672 and ponh.org_id = pfsp.org_id
673 and pfsp.set_of_books_id = pgl.set_of_books_id
674 and ponh.creation_date >= d_glob_date
675 order by func_cur_code,
676 rate_date );
677
678 end if;
679 COMMIT;
680 -- Gather statistics for poa_dbi_neg_po_rates
681 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_poa_schema, TABNAME => 'POA_DBI_NEG_PO_RATES') ;
682
683 --- Create a Negotiations Table that stores all relevant data pertaining to negotiations ---
684 if(g_init) then
685 insert /*+ APPEND */ into poa_dbi_neg_details
686 (
687 po_distribution_id,
688 auction_header_id,
689 auction_line_number,
690 bid_number,
691 bid_line_number,
692 negotiation_creator_id,
693 doctype_id,
694 neg_current_price,
695 neg_func_cur_code,
696 neg_func_cur_conv_rate,
697 neg_global_cur_conv_rate,
698 neg_sglobal_cur_conv_rate,
699 neg_transaction_uom,
700 neg_base_uom,
701 neg_base_uom_conv_rate
702 )
703 ( select /*+ USE_HASH(inc) */
704 pod.po_distribution_id,
705 pol.auction_header_id,
706 pol.auction_line_number,
707 pol.bid_number,
708 pol.bid_line_number,
709 hz.person_identifier negotiation_creator_id,
710 ponh.doctype_id,
711 ponip.current_price neg_current_price,
712 neg_rates.func_cur_code neg_func_cur_code,
713 nvl(ponh.rate,1) neg_func_cur_conv_rate,
714 neg_rates.global_cur_conv_rate neg_global_cur_conv_rate,
715 neg_rates.sglobal_cur_conv_rate neg_sglobal_cur_conv_rate,
716 uom.unit_of_measure neg_transaction_uom,
717 decode(ponip.item_id, null, decode(pll.value_basis, 'AMOUNT',uom.unit_of_measure, nvl(pll.unit_meas_lookup_code, pol.unit_meas_lookup_code)), pitem.primary_unit_of_measure) neg_base_uom,
718 decode(
719 ponip.item_id,
720 null,
721 decode(pll.value_basis,'AMOUNT',1,
722 decode(uom.unit_of_measure,nvl(pll.unit_meas_lookup_code, pol.unit_meas_lookup_code), 1,
723 poa_dbi_uom_pkg.convert_neg_to_po_uom(uom.unit_of_measure, nvl(pll.unit_meas_lookup_code, pol.unit_meas_lookup_code))))
724 ,
725 decode(uom.unit_of_measure,
726 pitem.primary_unit_of_measure, 1,
727 poa_dbi_uom_pkg.convert_to_item_base_uom(
728 ponip.item_id,
729 ppar.master_organization_id,
730 uom.unit_of_measure,
731 pitem.primary_uom_code
732 )
733 )
734 ) neg_base_uom_conv_rate
735 from
736 poa_dbi_pod_inc inc,
737 po_distributions_all pod,
738 po_line_locations_all pll,
739 po_lines_all pol,
740 pon_bid_item_prices ponbip,
741 pon_bid_headers ponbh,
742 pon_auction_item_prices_all ponip,
743 pon_auction_headers_all ponh,
744 poa_dbi_neg_po_rates neg_rates,
745 financials_system_params_all pfsp,
746 gl_sets_of_books pgl,
747 mtl_system_items pitem,
748 mtl_units_of_measure uom,
749 mtl_parameters ppar,
750 hz_parties hz
751 where
752 pod.po_distribution_id = inc.primary_key
753 and pod.line_location_id = pll.line_location_id
754 and pll.po_line_id = pol.po_line_id
755 and pol.from_header_id is null
756 and pol.auction_header_id = ponbip.auction_header_id
757 and pol.bid_number = ponbip.bid_number
758 and pol.auction_line_number = ponbip.auction_line_number
759 and pol.bid_line_number = ponbip.line_number
760 and ponbip.auction_header_id = ponbh.auction_header_id
761 and ponbh.auction_header_id = ponip.auction_header_id
762 and ponip.auction_header_id = ponh.auction_header_id
763 and ponbh.bid_number = ponbip.bid_number
764 and ponbip.auction_line_number = ponip.line_number
765 and ponh.org_id = pfsp.org_id
766 and ponbh.contract_type in ('STANDARD','BLANKET')
767 and pfsp.set_of_books_id = pgl.set_of_books_id
768 and pfsp.inventory_organization_id = ppar.organization_id
769 and ponip.item_id = pitem.inventory_item_id(+)
770 and ppar.master_organization_id = nvl(pitem.organization_id, ppar.master_organization_id)
771 and ponip.uom_code = uom.uom_code(+)
772 and ponh.trading_partner_contact_id = hz.party_id
773 and neg_rates.rate_date = nvl(trunc(ponh.rate_date), trunc(ponh.creation_date))
774 and neg_rates.txn_cur_code = ponh.currency_code
775 and neg_rates.func_cur_code = pgl.currency_code
776 and ponh.creation_date >= d_glob_date
777 UNION
778 select /*+ USE_HASH(inc) */
779 pod.po_distribution_id,
780 pol_orig.auction_header_id,
781 pol_orig.auction_line_number,
782 pol_orig.bid_number,
783 pol_orig.bid_line_number,
784 hz.person_identifier negotiation_creator_id,
785 ponh.doctype_id,
786 ponip.current_price neg_current_price,
787 neg_rates.func_cur_code neg_func_cur_code,
788 nvl(ponh.rate,1) neg_func_cur_conv_rate,
789 neg_rates.global_cur_conv_rate neg_global_cur_conv_rate,
790 neg_rates.sglobal_cur_conv_rate neg_sglobal_cur_conv_rate,
791 uom.unit_of_measure neg_transaction_uom,
792 decode(ponip.item_id, null, decode(pll.value_basis, 'AMOUNT',uom.unit_of_measure, nvl(pll.unit_meas_lookup_code, pol.unit_meas_lookup_code)), pitem.primary_unit_of_measure) neg_base_uom,
793 decode(
794 ponip.item_id,
795 null,
796 decode(pll.value_basis,'AMOUNT',1,
797 decode(uom.unit_of_measure,nvl(pll.unit_meas_lookup_code, pol.unit_meas_lookup_code), 1,
798 poa_dbi_uom_pkg.convert_neg_to_po_uom(uom.unit_of_measure, nvl(pll.unit_meas_lookup_code, pol.unit_meas_lookup_code))))
799 ,
800 decode(uom.unit_of_measure,
801 pitem.primary_unit_of_measure, 1,
802 poa_dbi_uom_pkg.convert_to_item_base_uom(
803 ponip.item_id,
804 ppar.master_organization_id,
805 uom.unit_of_measure,
806 pitem.primary_uom_code
807 )
808 )
809 ) neg_base_uom_conv_rate
810 from
811 poa_dbi_pod_inc inc,
812 po_distributions_all pod,
813 po_line_locations_all pll,
814 po_lines_all pol,
815 po_headers_all poh_orig,
816 po_lines_all pol_orig,
817 pon_bid_item_prices ponbip,
818 pon_bid_headers ponbh,
819 pon_auction_item_prices_all ponip,
820 pon_auction_headers_all ponh,
821 poa_dbi_neg_po_rates neg_rates,
822 financials_system_params_all pfsp,
823 gl_sets_of_books pgl,
824 mtl_system_items pitem,
825 mtl_units_of_measure uom,
826 mtl_parameters ppar,
827 hz_parties hz
828 where
829 pod.po_distribution_id = inc.primary_key
830 and pod.line_location_id = pll.line_location_id
831 and pll.po_line_id = pol.po_line_id
832 and pol.from_header_id = poh_orig.po_header_id
833 and pol.from_line_id = pol_orig.po_line_id
834 and pol_orig.po_header_id = poh_orig.po_header_id
835 and pol_orig.po_header_id = poh_orig.po_header_id
836 and pol_orig.bid_number = ponbh.bid_number
837 and pol_orig.bid_line_number = ponbip.line_number
838 and pol_orig.auction_header_id = ponh.auction_header_id
839 and pol_orig.auction_line_number = ponip.line_number
840 and ponbip.auction_header_id = ponbh.auction_header_id
841 and ponbh.auction_header_id = ponip.auction_header_id
842 and ponip.auction_header_id = ponh.auction_header_id
843 and ponbh.bid_number = ponbip.bid_number
844 and ponbip.auction_line_number = ponip.line_number
845 and ponh.org_id = pfsp.org_id
846 and ponbh.contract_type = 'BLANKET'
847 and pfsp.set_of_books_id = pgl.set_of_books_id
848 and pfsp.inventory_organization_id = ppar.organization_id
849 and ponip.item_id = pitem.inventory_item_id(+)
850 and ppar.master_organization_id = nvl(pitem.organization_id, ppar.master_organization_id)
851 and ponip.uom_code = uom.uom_code(+)
852 and ponh.trading_partner_contact_id = hz.party_id
853 and neg_rates.rate_date = nvl(trunc(ponh.rate_date), trunc(ponh.creation_date))
854 and neg_rates.txn_cur_code = ponh.currency_code
855 and neg_rates.func_cur_code = pgl.currency_code
856 and ponh.creation_date >= d_glob_date
857 UNION
858 select /*+ USE_HASH(inc) */
859 pod.po_distribution_id,
860 ponbip.auction_header_id,
861 ponbip.auction_line_number,
862 ponbip.bid_number,
863 ponbip.line_number bid_line_number,
864 hz.person_identifier negotiation_creator_id,
865 ponh.doctype_id,
866 null neg_current_price,
867 neg_rates.func_cur_code neg_func_cur_code,
868 nvl(ponh.rate,1) neg_func_cur_conv_rate,
869 neg_rates.global_cur_conv_rate neg_global_cur_conv_rate,
870 neg_rates.sglobal_cur_conv_rate neg_sglobal_cur_conv_rate,
871 uom.unit_of_measure neg_transaction_uom,
872 decode(ponip.item_id, null, decode(pll.value_basis, 'AMOUNT',uom.unit_of_measure, nvl(pll.unit_meas_lookup_code, pol.unit_meas_lookup_code)), pitem.primary_unit_of_measure) neg_base_uom,
873 decode(
874 ponip.item_id,
875 null,
876 decode(pll.value_basis,'AMOUNT',1,
877 decode(uom.unit_of_measure,nvl(pll.unit_meas_lookup_code, pol.unit_meas_lookup_code), 1,
878 poa_dbi_uom_pkg.convert_neg_to_po_uom(uom.unit_of_measure, nvl(pll.unit_meas_lookup_code, pol.unit_meas_lookup_code))))
879 ,
880 decode(uom.unit_of_measure,
881 pitem.primary_unit_of_measure, 1,
882 poa_dbi_uom_pkg.convert_to_item_base_uom(
883 ponip.item_id,
884 ppar.master_organization_id,
885 uom.unit_of_measure,
886 pitem.primary_uom_code
887 )
888 )
889 ) neg_base_uom_conv_rate
890 from
891 poa_dbi_pod_inc inc,
892 po_distributions_all pod,
893 po_line_locations_all pll,
894 po_lines_all pol,
895 po_headers_all poh_orig,
896 pon_bid_item_prices ponbip,
897 pon_bid_headers ponbh,
898 pon_auction_item_prices_all ponip,
899 pon_auction_headers_all ponh,
900 poa_dbi_neg_po_rates neg_rates,
901 financials_system_params_all pfsp,
902 gl_sets_of_books pgl,
903 mtl_system_items pitem,
904 mtl_units_of_measure uom,
905 mtl_parameters ppar,
906 hz_parties hz
907 where
908 pod.po_distribution_id = inc.primary_key
909 and pod.line_location_id = pll.line_location_id
910 and pll.po_line_id = pol.po_line_id
911 and pol.contract_id = poh_orig.po_header_id
912 and ponbh.po_header_id = poh_orig.po_header_id
913 and ponbh.contract_type = 'CONTRACT'
914 and ponbip.auction_header_id = ponbh.auction_header_id
915 and ponbh.auction_header_id = ponip.auction_header_id
916 and ponip.auction_header_id = ponh.auction_header_id
917 and ponbh.bid_number = ponbip.bid_number
918 and ponbip.auction_line_number = ponip.line_number
919 and ponh.org_id = pfsp.org_id
920 and pfsp.set_of_books_id = pgl.set_of_books_id
921 and pfsp.inventory_organization_id = ppar.organization_id
922 and ponip.item_id = pitem.inventory_item_id(+)
923 and ppar.master_organization_id = nvl(pitem.organization_id, ppar.master_organization_id)
924 and ponip.uom_code = uom.uom_code(+)
925 and ponh.trading_partner_contact_id = hz.party_id
926 and neg_rates.rate_date = nvl(trunc(ponh.rate_date), trunc(ponh.creation_date))
927 and neg_rates.txn_cur_code = ponh.currency_code
928 and neg_rates.func_cur_code = pgl.currency_code
929 and ponh.creation_date >= d_glob_date );
930 else --not initial load, change the hints for faster performance
931 insert /*+ APPEND */ into poa_dbi_neg_details
932 (
933 po_distribution_id,
934 auction_header_id,
935 auction_line_number,
936 bid_number,
937 bid_line_number,
938 negotiation_creator_id,
939 doctype_id,
940 neg_current_price,
941 neg_func_cur_code,
942 neg_func_cur_conv_rate,
943 neg_global_cur_conv_rate,
944 neg_sglobal_cur_conv_rate,
945 neg_transaction_uom,
946 neg_base_uom,
947 neg_base_uom_conv_rate
948 )
949 ( select /*+ leading(inc,pod) cardinality(inc,1) */
950 pod.po_distribution_id,
951 pol.auction_header_id,
952 pol.auction_line_number,
953 pol.bid_number,
954 pol.bid_line_number,
955 hz.person_identifier negotiation_creator_id,
956 ponh.doctype_id,
957 ponip.current_price neg_current_price,
958 neg_rates.func_cur_code neg_func_cur_code,
959 nvl(ponh.rate,1) neg_func_cur_conv_rate,
960 neg_rates.global_cur_conv_rate neg_global_cur_conv_rate,
961 neg_rates.sglobal_cur_conv_rate neg_sglobal_cur_conv_rate,
962 uom.unit_of_measure neg_transaction_uom,
963 decode(ponip.item_id, null, decode(pll.value_basis, 'AMOUNT',uom.unit_of_measure, nvl(pll.unit_meas_lookup_code, pol.unit_meas_lookup_code)), pitem.primary_unit_of_measure) neg_base_uom,
964 decode(
965 ponip.item_id,
966 null,
967 decode(pll.value_basis,'AMOUNT',1,
968 decode(uom.unit_of_measure,nvl(pll.unit_meas_lookup_code, pol.unit_meas_lookup_code), 1,
969 poa_dbi_uom_pkg.convert_neg_to_po_uom(uom.unit_of_measure, nvl(pll.unit_meas_lookup_code, pol.unit_meas_lookup_code))))
970 ,
971 decode(uom.unit_of_measure,
972 pitem.primary_unit_of_measure, 1,
973 poa_dbi_uom_pkg.convert_to_item_base_uom(
974 ponip.item_id,
975 ppar.master_organization_id,
976 uom.unit_of_measure,
977 pitem.primary_uom_code
978 )
979 )
980 ) neg_base_uom_conv_rate
981 from
982 poa_dbi_pod_inc inc,
983 po_distributions_all pod,
984 po_line_locations_all pll,
985 po_lines_all pol,
986 pon_bid_item_prices ponbip,
987 pon_bid_headers ponbh,
988 pon_auction_item_prices_all ponip,
989 pon_auction_headers_all ponh,
990 poa_dbi_neg_po_rates neg_rates,
991 financials_system_params_all pfsp,
992 gl_sets_of_books pgl,
993 mtl_system_items pitem,
994 mtl_units_of_measure uom,
995 mtl_parameters ppar,
996 hz_parties hz
997 where
998 pod.po_distribution_id = inc.primary_key
999 and pod.line_location_id = pll.line_location_id
1000 and pll.po_line_id = pol.po_line_id
1001 and pol.from_header_id is null
1002 and pol.auction_header_id = ponbip.auction_header_id
1003 and pol.bid_number = ponbip.bid_number
1004 and pol.auction_line_number = ponbip.auction_line_number
1005 and pol.bid_line_number = ponbip.line_number
1006 and ponbip.auction_header_id = ponbh.auction_header_id
1007 and ponbh.auction_header_id = ponip.auction_header_id
1008 and ponip.auction_header_id = ponh.auction_header_id
1009 and ponbh.bid_number = ponbip.bid_number
1010 and ponbip.auction_line_number = ponip.line_number
1011 and ponh.org_id = pfsp.org_id
1012 and ponbh.contract_type in ('STANDARD','BLANKET')
1013 and pfsp.set_of_books_id = pgl.set_of_books_id
1014 and pfsp.inventory_organization_id = ppar.organization_id
1015 and ponip.item_id = pitem.inventory_item_id(+)
1016 and ppar.master_organization_id = nvl(pitem.organization_id, ppar.master_organization_id)
1017 and ponip.uom_code = uom.uom_code(+)
1018 and ponh.trading_partner_contact_id = hz.party_id
1019 and neg_rates.rate_date = nvl(trunc(ponh.rate_date), trunc(ponh.creation_date))
1020 and neg_rates.txn_cur_code = ponh.currency_code
1021 and neg_rates.func_cur_code = pgl.currency_code
1022 and ponh.creation_date >= d_glob_date
1023 UNION
1024 select /*+ leading(inc,pod) cardinality(inc,1) */
1025 pod.po_distribution_id,
1026 pol_orig.auction_header_id,
1027 pol_orig.auction_line_number,
1028 pol_orig.bid_number,
1029 pol_orig.bid_line_number,
1030 hz.person_identifier negotiation_creator_id,
1031 ponh.doctype_id,
1032 ponip.current_price neg_current_price,
1033 neg_rates.func_cur_code neg_func_cur_code,
1034 nvl(ponh.rate,1) neg_func_cur_conv_rate,
1035 neg_rates.global_cur_conv_rate neg_global_cur_conv_rate,
1036 neg_rates.sglobal_cur_conv_rate neg_sglobal_cur_conv_rate,
1037 uom.unit_of_measure neg_transaction_uom,
1038 decode(ponip.item_id, null, decode(pll.value_basis, 'AMOUNT',uom.unit_of_measure, nvl(pll.unit_meas_lookup_code, pol.unit_meas_lookup_code)), pitem.primary_unit_of_measure) neg_base_uom,
1039 decode(
1040 ponip.item_id,
1041 null,
1042 decode(pll.value_basis,'AMOUNT',1,
1043 decode(uom.unit_of_measure,nvl(pll.unit_meas_lookup_code, pol.unit_meas_lookup_code), 1,
1044 poa_dbi_uom_pkg.convert_neg_to_po_uom(uom.unit_of_measure, nvl(pll.unit_meas_lookup_code, pol.unit_meas_lookup_code))))
1045 ,
1046 decode(uom.unit_of_measure,
1047 pitem.primary_unit_of_measure, 1,
1048 poa_dbi_uom_pkg.convert_to_item_base_uom(
1049 ponip.item_id,
1050 ppar.master_organization_id,
1051 uom.unit_of_measure,
1052 pitem.primary_uom_code
1053 )
1054 )
1055 ) neg_base_uom_conv_rate
1056 from
1057 poa_dbi_pod_inc inc,
1058 po_distributions_all pod,
1059 po_line_locations_all pll,
1060 po_lines_all pol,
1061 po_headers_all poh_orig,
1062 po_lines_all pol_orig,
1063 pon_bid_item_prices ponbip,
1064 pon_bid_headers ponbh,
1065 pon_auction_item_prices_all ponip,
1066 pon_auction_headers_all ponh,
1067 poa_dbi_neg_po_rates neg_rates,
1068 financials_system_params_all pfsp,
1069 gl_sets_of_books pgl,
1070 mtl_system_items pitem,
1071 mtl_units_of_measure uom,
1072 mtl_parameters ppar,
1073 hz_parties hz
1074 where
1075 pod.po_distribution_id = inc.primary_key
1076 and pod.line_location_id = pll.line_location_id
1077 and pll.po_line_id = pol.po_line_id
1078 and pol.from_header_id = poh_orig.po_header_id
1079 and pol.from_line_id = pol_orig.po_line_id
1080 and pol_orig.po_header_id = poh_orig.po_header_id
1081 and pol_orig.po_header_id = poh_orig.po_header_id
1082 and pol_orig.bid_number = ponbh.bid_number
1083 and pol_orig.bid_line_number = ponbip.line_number
1084 and pol_orig.auction_header_id = ponh.auction_header_id
1085 and pol_orig.auction_line_number = ponip.line_number
1086 and ponbip.auction_header_id = ponbh.auction_header_id
1087 and ponbh.auction_header_id = ponip.auction_header_id
1088 and ponip.auction_header_id = ponh.auction_header_id
1089 and ponbh.bid_number = ponbip.bid_number
1090 and ponbip.auction_line_number = ponip.line_number
1091 and ponh.org_id = pfsp.org_id
1092 and ponbh.contract_type = 'BLANKET'
1093 and pfsp.set_of_books_id = pgl.set_of_books_id
1094 and pfsp.inventory_organization_id = ppar.organization_id
1095 and ponip.item_id = pitem.inventory_item_id(+)
1096 and ppar.master_organization_id = nvl(pitem.organization_id, ppar.master_organization_id)
1097 and ponip.uom_code = uom.uom_code(+)
1098 and ponh.trading_partner_contact_id = hz.party_id
1099 and neg_rates.rate_date = nvl(trunc(ponh.rate_date), trunc(ponh.creation_date))
1100 and neg_rates.txn_cur_code = ponh.currency_code
1101 and neg_rates.func_cur_code = pgl.currency_code
1102 and ponh.creation_date >= d_glob_date
1103 UNION
1104 select /*+ leading(inc,pod) cardinality(inc,1) */
1105 pod.po_distribution_id,
1106 ponbip.auction_header_id,
1107 ponbip.auction_line_number,
1108 ponbip.bid_number,
1109 ponbip.line_number bid_line_number,
1110 hz.person_identifier negotiation_creator_id,
1111 ponh.doctype_id,
1112 null neg_current_price,
1113 neg_rates.func_cur_code neg_func_cur_code,
1114 nvl(ponh.rate,1) neg_func_cur_conv_rate,
1115 neg_rates.global_cur_conv_rate neg_global_cur_conv_rate,
1116 neg_rates.sglobal_cur_conv_rate neg_sglobal_cur_conv_rate,
1117 uom.unit_of_measure neg_transaction_uom,
1118 decode(ponip.item_id, null, decode(pll.value_basis, 'AMOUNT',uom.unit_of_measure, nvl(pll.unit_meas_lookup_code, pol.unit_meas_lookup_code)), pitem.primary_unit_of_measure) neg_base_uom,
1119 decode(
1120 ponip.item_id,
1121 null,
1122 decode(pll.value_basis,'AMOUNT',1,
1123 decode(uom.unit_of_measure,nvl(pll.unit_meas_lookup_code, pol.unit_meas_lookup_code), 1,
1124 poa_dbi_uom_pkg.convert_neg_to_po_uom(uom.unit_of_measure, nvl(pll.unit_meas_lookup_code, pol.unit_meas_lookup_code))))
1125 ,
1126 decode(uom.unit_of_measure,
1127 pitem.primary_unit_of_measure, 1,
1128 poa_dbi_uom_pkg.convert_to_item_base_uom(
1129 ponip.item_id,
1130 ppar.master_organization_id,
1131 uom.unit_of_measure,
1132 pitem.primary_uom_code
1133 )
1134 )
1135 ) neg_base_uom_conv_rate
1136 from
1137 poa_dbi_pod_inc inc,
1138 po_distributions_all pod,
1139 po_line_locations_all pll,
1140 po_lines_all pol,
1141 po_headers_all poh_orig,
1142 pon_bid_item_prices ponbip,
1143 pon_bid_headers ponbh,
1144 pon_auction_item_prices_all ponip,
1145 pon_auction_headers_all ponh,
1146 poa_dbi_neg_po_rates neg_rates,
1147 financials_system_params_all pfsp,
1148 gl_sets_of_books pgl,
1149 mtl_system_items pitem,
1150 mtl_units_of_measure uom,
1151 mtl_parameters ppar,
1152 hz_parties hz
1153 where
1154 pod.po_distribution_id = inc.primary_key
1155 and pod.line_location_id = pll.line_location_id
1156 and pll.po_line_id = pol.po_line_id
1157 and pol.contract_id = poh_orig.po_header_id
1158 and ponbh.po_header_id = poh_orig.po_header_id
1159 and ponbh.contract_type = 'CONTRACT'
1160 and ponbip.auction_header_id = ponbh.auction_header_id
1161 and ponbh.auction_header_id = ponip.auction_header_id
1162 and ponip.auction_header_id = ponh.auction_header_id
1163 and ponbh.bid_number = ponbip.bid_number
1164 and ponbip.auction_line_number = ponip.line_number
1165 and ponh.org_id = pfsp.org_id
1166 and pfsp.set_of_books_id = pgl.set_of_books_id
1167 and pfsp.inventory_organization_id = ppar.organization_id
1168 and ponip.item_id = pitem.inventory_item_id(+)
1169 and ppar.master_organization_id = nvl(pitem.organization_id, ppar.master_organization_id)
1170 and ponip.uom_code = uom.uom_code(+)
1171 and ponh.trading_partner_contact_id = hz.party_id
1172 and neg_rates.rate_date = nvl(trunc(ponh.rate_date), trunc(ponh.creation_date))
1173 and neg_rates.txn_cur_code = ponh.currency_code
1174 and neg_rates.func_cur_code = pgl.currency_code
1175 and ponh.creation_date >= d_glob_date );
1176
1177 end if;
1178
1179 COMMIT;
1180
1181
1182 DBMS_APPLICATION_INFO.SET_ACTION('stats rates');
1183
1184 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_poa_schema,
1185 TABNAME => 'POA_DBI_POD_RATES') ;
1186 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_poa_schema,
1187 TABNAME => 'POA_DBI_NEG_DETAILS') ;
1188
1189 bis_collection_utilities.log('Populate base table: '|| 'Sysdate=' ||To_char(Sysdate, 'DD/MM/YYYY HH24:MI:SS'), 0);
1190
1191 select max(batch_id), COUNT(1) into l_no_batch, l_count from poa_dbi_pod_inc;
1192 bis_collection_utilities.log('Identified '|| l_count ||' changed records. Batch size='|| l_batch_size || '. # of Batches=' || l_no_batch
1193 || '. Sysdate=' ||To_char(Sysdate, 'DD/MM/YYYY HH24:MI:SS'), 0);
1194
1195 /* missing currency handling */
1196
1197 IF (poa_currency_pkg.g_missing_cur) THEN
1198 poa_currency_pkg.g_missing_cur := false;
1199 errbuf := 'There are missing currencies\n';
1200 RAISE_APPLICATION_ERROR (-20000, 'Error in INC table collection: ' || errbuf);
1201 END IF;
1202
1203 /*
1204 IF (l_rate = -1) THEN
1205 bis_collection_utilities.log('There are missing currencies '|| 'Sysdate=' ||To_char(Sysdate, 'DD/MM/YYYY HH24:MI:SS'), 0);
1206 RAISE_APPLICATION_ERROR (-20000, 'Error in INC table collection: ' || errbuf);
1207 ELSIF (l_rate = -2) THEN
1208 bis_collection_utilities.log('There are invalid currencies '|| 'Sysdate=' ||To_char(Sysdate, 'DD/MM/YYYY HH24:MI:SS'), 0);
1209 RAISE_APPLICATION_ERROR (-20000, 'Error in INC table collection: ' || errbuf);
1210 END IF;
1211 */
1212
1213 l_start_time := sysdate;
1214 l_login := fnd_global.login_id;
1215 l_user := fnd_global.user_id;
1216 DBMS_APPLICATION_INFO.SET_ACTION('collect');
1217
1218 if (l_no_batch is NOT NULL) then
1219 IF (g_init) THEN
1220 bis_collection_utilities.log('Initial Load - populate match table. '|| 'Sysdate=' ||To_char(Sysdate, 'DD/MM/YYYY HH24:MI:SS'), 0);
1221
1222 INSERT /*+ APPEND PARALLEL(poa_dbi_pod_match_temp) */ INTO
1223 poa_dbi_pod_match_temp
1224 ( po_distribution_id,
1225 creation_date,
1226 quantity,
1227 unit_meas_lookup_code,
1228 currency_code,
1229 item_id,
1230 ship_to_location_id,
1231 org_id,
1232 need_by_date,
1233 func_cur_code,
1234 rate_date,
1235 ship_to_ou_id,
1236 category_id,
1237 ship_to_organization_id
1238 )
1239 SELECT /*+ PARALLEL(inc) PARALLEL(pod) PARALLEL(pll) PARALLEL(pol)
1240 PARALLEL(poh) use_hash(pod) use_hash(pll) use_hash(pol)
1241 use_hash(poh) use_hash(match) */
1242 pod.po_distribution_id,
1243 pod.creation_date,
1244 pll.quantity,
1245 nvl(pll.unit_meas_lookup_code,pol.unit_meas_lookup_code),
1246 poh.currency_code,
1247 pol.item_id,
1248 pll.ship_to_location_id,
1249 poh.org_id,
1250 pll.need_by_date,
1251 inc.func_cur_code,
1252 nvl(pod.rate_date, pod.creation_date),
1253 match.ship_to_ou_id,
1254 pol.category_id,
1255 pll.ship_to_organization_id
1256 FROM
1257 poa_dbi_pod_inc inc,
1258 po_distributions_all pod,
1259 po_line_locations_all pll,
1260 po_lines_all pol,
1261 po_headers_all poh,
1262 po_doc_style_headers style,
1263 ( SELECT /*+ PARALLEL(pod) PARALLEL(psc) PARALLEL(plc) PARALLEL(inc)
1264 PARALLEL(ga) no_merge use_hash(pod) use_hash(psc)
1265 use_hash(plc) use_hash(v1) use_hash(ga) use_hash(pgoa)
1266 use_hash(hro) */
1267 distinct
1268 pod.po_distribution_id,
1269 hro.ship_to_ou_id
1270 FROM
1271 po_distributions_all pod,
1272 po_line_locations_all psc,
1273 po_lines_all plc,
1274 poa_dbi_pod_inc inc,
1275 po_headers_all ga,
1276 (select /*+ no_merge */ to_number(hro.org_information3) ship_to_ou_id,organization_id
1277 from hr_organization_information hro where
1278 hro.org_information_context='Accounting Information') hro,
1279 ( SELECT /*+ PARALLEL(pl) PARALLEL(ph) no_merge use_hash(ph, pl) */
1280 pl.item_id,
1281 ph.start_date,
1282 ph.end_date,
1283 pl.expiration_date,
1284 ph.org_id,
1285 ph.global_agreement_flag,
1286 ph.po_header_id,
1287 pl.creation_date
1288 FROM
1289 po_lines_all pl,
1290 po_headers_all ph
1291 WHERE ph.type_lookup_code = 'BLANKET'
1292 and pl.price_break_lookup_code is not null
1293 AND ph.approved_flag IN ('Y', 'R')
1294 and ph.po_header_id = pl.po_header_id
1295 and nvl(ph.cancel_flag, 'N') = 'N'
1296 and nvl(pl.cancel_flag, 'N') = 'N'
1297 ) v1,
1298 ( select /*+ no_merge parallel(pgoa) */
1299 distinct po_header_id, purchasing_org_id
1300 from po_ga_org_assignments pgoa
1301 where enabled_flag = 'Y'
1302 ) pgoa
1303 WHERE plc.po_line_id = psc.po_line_id
1304 and psc.line_location_id = pod.line_location_id
1305 and psc.shipment_type = 'STANDARD'
1306 and plc.from_header_id = ga.po_header_id(+)
1307 and nvl(ga.global_agreement_flag, 'N') = 'N'
1308 and psc.approved_flag = 'Y'
1309 and plc.item_id is not null
1310 and pod.creation_date is not null
1311 and nvl(pod.distribution_type,'-99') <> 'AGREEMENT'
1312 and v1.item_id = plc.item_id
1313 AND inc.primary_key = pod.po_distribution_id
1314 and v1.po_header_id = pgoa.po_header_id (+)
1315 and to_number(hro.organization_id) = psc.ship_to_organization_id
1316 and (
1317 ( pgoa.purchasing_org_id in
1318 ( select /*+ ordered no_merge parallel(tfh) parallel(fsp1) parallel(fsp2) use_hash(tfh) use_hash(fsp1) use_hash(fsp2) */ tfh.start_org_id
1319 from
1320 mtl_procuring_txn_flow_hdrs_v tfh,
1321 financials_system_params_all fsp1,
1322 financials_system_params_all fsp2
1323 where pod.creation_date between nvl(tfh.start_date, pod.creation_date) and nvl(tfh.end_date, pod.creation_date)
1324 and fsp1.org_id = tfh.start_org_id
1325 and fsp1.purch_encumbrance_flag = 'N'
1326 and fsp2.org_id = tfh.end_org_id
1327 and fsp2.purch_encumbrance_flag = 'N'
1328 and tfh.end_org_id = hro.ship_to_ou_id
1329 and ((tfh.qualifier_code is null) or (tfh.qualifier_code = 1 and tfh.qualifier_value_id = plc.category_id))
1330 and ((tfh.organization_id is null) or (tfh.organization_id = psc.ship_to_organization_id))
1331 )
1332 )
1333 or (nvl(pgoa.purchasing_org_id,hro.ship_to_ou_id) = hro.ship_to_ou_id )
1334 )
1335 and (
1336 ( v1.org_id = hro.ship_to_ou_id
1337 and nvl(v1.global_agreement_flag, 'N') = 'N'
1338 )
1339 or
1340 ( v1.global_agreement_flag = 'Y'
1341 and pgoa.purchasing_org_id is not null
1342 )
1343 )
1344 and Trunc(pod.creation_date) between nvl(v1.start_date, Trunc(pod.creation_date))
1345 and nvl(v1.end_date, pod.creation_date)
1346 and pod.creation_date >= v1.creation_date
1347 and Trunc(pod.creation_date) <= nvl(v1.expiration_date, pod.creation_date)
1348 ) match
1349 WHERE inc.primary_key = pod.po_distribution_id
1350 AND poh.po_header_id = pol.po_header_id
1351 and pol.po_line_id = pll.po_line_id
1352 and pll.line_location_id = pod.line_location_id
1353 and poh.style_id = style.style_id
1354 and nvl(style.progress_payment_flag,'N') = 'N'
1355 and pll.approved_flag = 'Y'
1356 and nvl(pod.distribution_type,'-99') <> 'AGREEMENT'
1357 and pod.creation_date is not NULL
1358 and inc.primary_key = match.po_distribution_id;
1359
1360 COMMIT;
1361
1362 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_poa_schema,
1363 TABNAME => 'POA_DBI_POD_MATCH_TEMP') ;
1364
1365 bis_collection_utilities.log('Initial Load - populate lowest price table. '|| 'Sysdate=' ||To_char(Sysdate, 'DD/MM/YYYY HH24:MI:SS'), 0);
1366
1367 INSERT /*+ APPEND PARALLEL(t) */ INTO poa_dbi_pod_lowest_all_temp t(po_distribution_id, po_header_id, shipto_price, generic_price, unit_price)
1368 WITH bb AS (
1369 select /*+ PARALLEL(b) PARALLEL(ptmp) PARALLEL(std) PARALLEL(poa_gl) PARALLEL(fsp) no_merge leading(ptmp) use_hash(l, b, pgoa, std) */
1370 distinct ptmp.po_distribution_id,ptmp.creation_date,ptmp.ship_to_location_id, ptmp.item_id, ptmp.unit_meas_lookup_code,
1371 b.po_header_id,b.amount_limit,b.min_release_amount b_min_release_amount, b.global_agreement_flag, b.vendor_id,
1372 l.po_line_id, l.item_id b_item_id, l.unit_meas_lookup_code b_unit_meas_lookup_code, l.cancel_flag, l.expiration_date, l.price_break_lookup_code, l.unit_price, l.min_release_amount bl_min_release_amount,
1373 l.creation_date bl_creation_date,
1374 nvl(std.po_line_id, l.po_line_id) std_id,
1375 poa_gl.currency_code bl_func_cur_code, b.rate bl_rate
1376 from po_headers_all b,
1377 poa_dbi_pod_match_temp ptmp,
1378 po_lines_all std,
1379 (select /*+ PARALLEL(bl) no_merge */ bl.po_header_id, bl.item_id, bl.unit_meas_lookup_code,bl.expiration_date, bl.po_line_id, bl.min_release_amount, bl.unit_price, bl.cancel_flag, bl.price_break_lookup_code, bl.creation_date
1380 from po_lines_all bl
1381 where bl.price_break_lookup_code is not null
1382 and nvl(bl.cancel_flag, 'N') = 'N'
1383 ) l,
1384 (select /*+ PARALLEL(pgoa) no_merge */ distinct po_header_id, purchasing_org_id
1385 from po_ga_org_assignments pgoa
1386 where enabled_flag = 'Y') pgoa,
1387 financials_system_params_all fsp, -- to get the functional currency of the blanket agreement
1388 gl_sets_of_books poa_gl
1389 where l.po_header_id = b.po_header_id
1390 and l.item_id = ptmp.item_id
1391 and l.unit_meas_lookup_code = ptmp.unit_meas_lookup_code
1392 and Trunc(ptmp.creation_date) <= nvl(l.expiration_date, ptmp.creation_date)
1393 and ptmp.creation_date >= l.creation_date
1394 and b.type_lookup_code = 'BLANKET'
1395 and b.approved_flag in ('Y','R')
1396 and nvl(b.cancel_flag, 'N') = 'N'
1397 and b.po_header_id = pgoa.po_header_id (+)
1398 and b.org_id = fsp.org_id
1399 and fsp.set_of_books_id = poa_gl.set_of_books_id
1400 and ((pgoa.purchasing_org_id in
1401 (select /*+ ordered PARALLEL(tfh) PARALLEL(fsp1) PARALLEL(fsp2) no_merge use_hash(tfh) use_hash(fsp1) use_hash(fsp2) */ tfh.start_org_id
1402 from mtl_procuring_txn_flow_hdrs_v tfh,
1403 financials_system_params_all fsp1,
1404 financials_system_params_all fsp2
1405 where ptmp.creation_date between nvl(tfh.start_date, ptmp.creation_date) and nvl(tfh.end_date, ptmp.creation_date)
1406 and fsp1.org_id = tfh.start_org_id
1407 and fsp1.purch_encumbrance_flag = 'N'
1408 and fsp2.org_id = tfh.end_org_id
1409 and fsp2.purch_encumbrance_flag = 'N'
1410 and tfh.end_org_id = ptmp.ship_to_ou_id
1411 and ((tfh.qualifier_code is null) or (tfh.qualifier_code = 1 and tfh.qualifier_value_id = ptmp.category_id))
1412 and ((tfh.organization_id is null) or (tfh.organization_id = ptmp.ship_to_organization_id))
1413 )
1414 )
1415 or (nvl(pgoa.purchasing_org_id, ptmp.ship_to_ou_id) = ptmp.ship_to_ou_id))
1416 and ((ptmp.ship_to_ou_id = b.org_id and nvl(b.global_agreement_flag, 'N') = 'N')
1417 or
1418 (b.global_agreement_flag = 'Y' and pgoa.purchasing_org_id is not null))
1419 and Trunc(ptmp.creation_date) between nvl(b.start_date, Trunc(ptmp.creation_date)) AND nvl(b.end_date, ptmp.creation_date)
1420 and l.po_line_id = std.from_line_id (+)
1421 )
1422 select po_distribution_id,
1423 po_header_id,
1424 min(price1 * cur_conversion_rate) keep (dense_rank first order by nvl2(price1, nvl(quantity, 0), null) desc nulls last, trunc(creation_date) desc) price1,
1425 min(price2 * cur_conversion_rate) keep (dense_rank first order by nvl2(price1, nvl(quantity, 0), null) desc nulls last, trunc(creation_date) desc) price2,
1426 min(unit_price * cur_conversion_rate) unit_price
1427 from (
1428 select /*+ PARALLEL(blanket) PARALLEL(bblanket) PARALLEL(ptmp3) PARALLEL(pb) ORDERED use_hash(blanket bblanket ptmp3 pb) */
1429 ptmp3.po_distribution_id,
1430 ptmp3.quantity dist_quantity,
1431 blanket.po_line_id,
1432 blanket.line_all_qty,
1433 pb.line_location_id,
1434 blanket.line_qty,
1435 pb.price_override,
1436 unit_price,
1437 pb.ship_to_location_id,
1438 price_break_lookup_code,
1439 blanket.amount_limit,
1440 blanket.b_min,
1441 bl_min,
1442 pb.quantity,
1443 blanket.b_item_id,
1444 pb.creation_date,
1445 blanket.po_header_id,
1446 blanket.vendor_id
1447 , nvl(blanket.bl_rate, 1) * decode(blanket.bl_func_cur_code, ptmp3.func_cur_code, 1, poa_ga_util_pkg.get_ga_conversion_rate(blanket.bl_func_cur_code, ptmp3.func_cur_code, ptmp3.rate_date)) cur_conversion_rate
1448 -- convert to blanket functional currency and then to standard PO functional currency
1449 ,(case when pb.ship_to_location_id = ptmp3.ship_to_location_id
1450 and ( pb.quantity is null or ( price_break_lookup_code = 'NON CUMULATIVE' and ptmp3.quantity >= pb.quantity)
1451 or (price_break_lookup_code = 'CUMULATIVE' and ptmp3.quantity + blanket.line_qty >= pb.quantity))
1452 then pb.price_override else null end) price1
1453 ,(case when pb.line_location_id is not null and pb.ship_to_location_id is null
1454 and (pb.quantity is null or (price_break_lookup_code = 'NON CUMULATIVE' and ptmp3.quantity >= pb.quantity)
1455 or (price_break_lookup_code = 'CUMULATIVE' and ptmp3.quantity + blanket.line_all_qty >= pb.quantity))
1456 then pb.price_override else null end) price2
1457 from
1458 (
1459 select /*+ PARALLEL(rll) PARALLEL(rd) no_merge leading(b) use_hash(rll, rd) */
1460 b.po_distribution_id, b.creation_date, b.po_header_id, b.vendor_id, b.amount_limit, b.b_min_release_amount b_min, b.global_agreement_flag,
1461 b.po_line_id, b.b_item_id, b.b_unit_meas_lookup_code,
1462 b.cancel_flag, b.expiration_date, b.price_break_lookup_code, b.unit_price, b.bl_min_release_amount bl_min, b.bl_creation_date, b.bl_func_cur_code, b.bl_rate
1463 ,sum(case when rll.approved_flag='Y' and rll.ship_to_location_id = b.ship_to_location_id
1464 and rd.creation_date < b.creation_date
1465 then
1466 nvl(rd.quantity_ordered,0)-nvl(rd.quantity_cancelled,0) else 0 END) line_qty
1467 ,sum(case when rll.approved_flag='Y' and rd.creation_date < b.creation_date then
1468 nvl(rd.quantity_ordered,0)-nvl(rd.quantity_cancelled,0) else 0 end) line_all_qty
1469 from bb b
1470 ,po_line_locations_all rll
1471 ,po_distributions_all rd
1472 where b.std_id = rll.po_line_id(+)
1473 and rll.line_location_id = rd.line_location_id(+)
1474 and nvl(rd.distribution_type,'-99') <> 'AGREEMENT'
1475 and (rll.shipment_type is null or rll.shipment_type in ('BLANKET', 'STANDARD', 'PRICE BREAK'))
1476 GROUP by b.po_distribution_id, b.po_line_id, b.po_header_id, b.creation_date, b.vendor_id, b.global_agreement_flag,
1477 b.b_item_id, b.bl_min_release_amount, b.b_unit_meas_lookup_code, b.cancel_flag,
1478 b.expiration_date, b.price_break_lookup_code, b.unit_price,b.amount_limit, b.b_min_release_amount, b.bl_creation_date, b.bl_func_cur_code, b.bl_rate
1479 ) blanket,
1480 (
1481 select /*+ PARALLEL(rll) PARALLEL(rd) no_merge leading(b) use_hash(rll, rd) */
1482 b.po_distribution_id, b.po_header_id,
1483 sum(case when rd.creation_date < b.creation_date then
1484 nvl(rll.price_override,0) * (nvl(rd.quantity_ordered,0)-nvl(rd.quantity_cancelled,0)) else 0 END) blanket_amt
1485 from bb b
1486 ,po_line_locations_all rll
1487 ,po_distributions_all rd
1488 where b.std_id = rll.po_line_id(+)
1489 and rll.line_location_id = rd.line_location_id(+)
1490 and nvl(rd.distribution_type,'-99') <> 'AGREEMENT'
1491 and (rll.shipment_type is null or rll.shipment_type in ('BLANKET', 'STANDARD', 'PRICE BREAK'))
1492 GROUP by b.po_distribution_id, b.po_header_id
1493 ) bblanket,
1494 poa_dbi_pod_match_temp ptmp3,
1495 po_line_locations_all pb
1496 where blanket.po_distribution_id = ptmp3.po_distribution_id
1497 and blanket.po_distribution_id = bblanket.po_distribution_id
1498 and blanket.po_header_id = bblanket.po_header_id
1499 and blanket.b_item_id = ptmp3.item_id
1500 and blanket.b_unit_meas_lookup_code = ptmp3.unit_meas_lookup_code
1501 and nvl(blanket.cancel_flag, 'N') = 'N'
1502 and Trunc(blanket.creation_date) <= nvl(blanket.expiration_date, blanket.creation_date)
1503 and blanket.creation_date >= blanket.bl_creation_date
1504 and pb.po_line_id(+) = blanket.po_line_id
1505 and pb.shipment_type(+) = 'PRICE BREAK'
1506 and nvl(pb.cancel_flag, 'N') = 'N'
1507 and trunc(nvl(ptmp3.need_by_date, ptmp3.creation_date)) between
1508 trunc(nvl(pb.start_date, nvl(pb.creation_date, nvl(ptmp3.need_by_date, ptmp3.creation_date)))) and
1509 nvl(pb.end_date, nvl(ptmp3.need_by_date, ptmp3.creation_date))
1510 and ptmp3.quantity * nvl(pb.price_override,unit_price) >= nvl(blanket.bl_min,0)
1511 and ptmp3.quantity * nvl(pb.price_override,unit_price) >= nvl(blanket.b_min,0)
1512 and (blanket.amount_limit is null or ptmp3.quantity * nvl(pb.price_override,unit_price) + bblanket.blanket_amt
1513 <= blanket.amount_limit)
1514 )
1515 group by po_distribution_id ,po_line_id, po_header_id;
1516
1517 COMMIT;
1518
1519 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_poa_schema,
1520 TABNAME => 'POA_DBI_POD_LOWEST_ALL_TEMP') ;
1521
1522 insert /*+ APPEND PARALLEL(t) */ into poa_dbi_pod_lowest_price_temp t(po_distribution_id,
1523 lowest_price,
1524 potential_contract_id)
1525 select po_distribution_id,
1526 coalesce(min(shipto_price), min(generic_price), min(unit_price)) lowest_price,
1527 coalesce(min(nvl2(shipto_price, po_header_id, null)) keep (dense_rank first order by shipto_price nulls last) ,
1528 min(nvl2(generic_price, po_header_id, null)) keep (dense_rank first order by generic_price nulls last) ,
1529 min(po_header_id) keep (dense_rank first order by unit_price nulls last) ) potential_contract_id from
1530 poa_dbi_pod_lowest_all_temp
1531 group by po_distribution_id;
1532
1533 COMMIT;
1534
1535 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_poa_schema,
1536 TABNAME => 'POA_DBI_POD_LOWEST_PRICE_TEMP') ;
1537
1538 bis_collection_utilities.log('Initial Load - using one batch approach, populate base fact. '|| 'Sysdate=' ||To_char(Sysdate, 'DD/MM/YYYY HH24:MI:SS'), 0);
1539 INSERT /*+ APPEND PARALLEL(t) */ INTO
1540 poa_dbi_pod_f t
1541 (
1542 t.po_distribution_id,
1543 t.po_header_id,
1544 t.po_line_id,
1545 t.po_release_id,
1546 t.creation_operating_unit_id,
1547 t.ship_to_org_id,
1548 t.approved_date,
1549 t.distribution_creation_date,
1550 t.supplier_id,
1551 t.supplier_site_id,
1552 t.po_item_id,
1553 t.category_id,
1554 t.buyer_id,
1555 t.code_combination_id,
1556 t.func_cur_code,
1557 t.global_cur_conv_rate,
1558 t.base_uom_conv_rate,
1559 t.purchase_amount_b,
1560 t.contract_amount_b,
1561 t.non_contract_amount_b,
1562 t.pot_contract_amount_b,
1563 t.pot_savings_amount_b,
1564 t.unit_price,
1565 t.quantity,
1566 t.creation_mode,
1567 t.order_type,
1568 t.catalog_type,
1569 t.destination_type_code,
1570 t.amt_billed,
1571 t.amt_financed,
1572 t.amt_recouped,
1573 t.qty_billed,
1574 t.qty_financed,
1575 t.qty_recouped,
1576 t.qty_cancelled,
1577 t.potential_contract_id,
1578 t.shipment_type,
1579 t.apps_source_code,
1580 t.from_document_type,
1581 t.from_document_id,
1582 t.consigned_code,
1583 t.base_uom,
1584 t.transaction_uom,
1585 t.requestor_id,
1586 t.start_date_active,
1587 t.last_update_login,
1588 t.creation_date,
1589 t.last_updated_by,
1590 t.last_update_date,
1591 t.func_cur_conv_rate,
1592 t.sglobal_cur_conv_rate,
1593 t.expected_date,
1594 t.days_late_receipt_allowed,
1595 t.days_early_receipt_allowed,
1596 t.price_override,
1597 t.line_location_id,
1598 t.item_id,
1599 t.matching_basis,
1600 t.receiving_routing_id,
1601 t.company_id,
1602 t.cost_center_id,
1603 t.payment_type,
1604 t.complex_work_flag,
1605 ---Begin Changes for Item Avg Price
1606 t.non_zero_quantity,
1607 ---End Changes for Item Avg Price
1608 t.auction_header_id,
1609 t.auction_line_number,
1610 t.bid_number,
1611 t.bid_line_number,
1612 t.negotiation_creator_id,
1613 t.doctype_id,
1614 t.neg_current_price,
1615 t.neg_func_cur_code,
1616 t.neg_func_cur_conv_rate,
1617 t.neg_global_cur_conv_rate,
1618 t.neg_sglobal_cur_conv_rate,
1619 t.neg_transaction_uom,
1620 t.neg_base_uom,
1621 t.neg_base_uom_conv_rate,
1622 t.negotiated_by_preparer_flag
1623 )
1624 SELECT
1625 s.po_distribution_id,
1626 s.po_header_id,
1627 s.po_line_id,
1628 s.po_release_id,
1629 s.org_id,
1630 s.ship_to_organization_id,
1631 s.approved_date,
1632 s.creation_date,
1633 s.vendor_id,
1634 s.vendor_site_id,
1635 s.po_item_id,
1636 s.category_id,
1637 s.agent_id,
1638 s.code_combination_id,
1639 s.currency_code,
1640 s.global_cur_conv_rate,
1641 s.base_uom_conv_rate,
1642 s.purchase_amount,
1643 decode(s.prepayment_flag,'Y',0,s.contract_amount) contract_amount,
1644 decode(s.prepayment_flag,'Y',0,s.non_contract_amount) non_contract_amount,
1645 decode(s.prepayment_flag,'Y',0,s.pot_contract_amount) pot_contract_amount,
1646 decode(s.prepayment_flag,'Y',0,s.pot_savings_amount) pot_savings_amount,
1647 s.price_override / s.base_uom_conv_rate,
1648 s.quantity * s.base_uom_conv_rate,
1649 s.creation_mode,
1650 s.order_type,
1651 s.catalog_type,
1652 s.destination_type_code,
1653 s.amount_billed,
1654 s.amount_financed,
1655 s.amount_recouped,
1656 s.quantity_billed * s.base_uom_conv_rate,
1657 s.quantity_financed * s.base_uom_conv_rate,
1658 s.quantity_recouped * s.base_uom_conv_rate,
1659 s.quantity_cancelled * s.base_uom_conv_rate,
1660 s.potential_contract_id,
1661 s.shipment_type,
1662 s.apps_source_code,
1663 s.from_document_type,
1664 s.from_document_id,
1665 s.consigned_code,
1666 s.base_uom,
1667 s.transaction_uom,
1668 s.requestor_id,
1669 s.current_time, -- not sure if this is what it means
1670 s.login_id,
1671 s.current_time,
1672 s.user_id,
1673 s.current_time,
1674 s.func_cur_conv_rate,
1675 s.sglobal_cur_conv_rate,
1676 s.expected_date,
1677 s.days_late_receipt_allowed,
1678 s.days_early_receipt_allowed,
1679 s.price_override,
1680 s.line_location_id,
1681 s.item_id,
1682 s.matching_basis,
1683 s.receiving_routing_id,
1684 s.company_id,
1685 s.cost_center_id,
1686 s.payment_type,
1687 s.complex_work_flag,
1688 ---Begin Changes for Item Avg Price
1689 s.non_zero_quantity * s.base_uom_conv_rate,
1690 ---End Changes for Item Avg Price
1691 s.auction_header_id,
1692 s.auction_line_number,
1693 s.bid_number,
1694 s.bid_line_number,
1695 s.negotiation_creator_id,
1696 s.doctype_id,
1697 s.neg_current_price / s.neg_base_uom_conv_rate,
1698 s.neg_func_cur_code,
1699 s.neg_func_cur_conv_rate,
1700 s.neg_global_cur_conv_rate,
1701 s.neg_sglobal_cur_conv_rate,
1702 s.neg_transaction_uom,
1703 s.neg_base_uom,
1704 s.neg_base_uom_conv_rate,
1705 s.negotiated_by_preparer_flag
1706 FROM
1707 ( SELECT /*+ PARALLEL(inc) PARALLEL(pll) PARALLEL(pol) PARALLEL(poh)
1708 PARALLEL(prl) PARALLEL(prd) PARALLEL(low) PARALLEL(match)
1709 PARALLEL(item) PARALLEL(por) PARALLEL(ref) PARALLEL(prh)
1710 PARALLEL(par) PARALLEL(poa_gl) PARALLEL(fsp)
1711 NO_MERGE USE_HASH(poh) use_hash(pol) use_hash(item)
1712 use_hash(prl) use_hash(prd) */
1713 pod.po_distribution_id,
1714 poh.po_header_id,
1715 pol.po_line_id,
1716 pod.po_release_id,
1717 pll.org_id,
1718 pll.ship_to_organization_id,
1719 -- Trunc(NVL(POA_OLTP_GENERIC_PKG.get_approved_date_pll(pod.creation_date, pll.line_location_id), pll.approved_date)) approved_date,
1720 Trunc(nvl(pod.approved_date,pll.approved_date)) approved_date,
1721 Trunc(pod.creation_date) creation_date,
1722 poh.vendor_id,
1723 poh.vendor_site_id,
1724 poa_dbi_items_pkg.getitemkey(pol.item_id, par.master_organization_id, pol.category_id,
1725 pol.vendor_product_num, poh.vendor_id, pol.item_description) po_item_id,
1726 pol.category_id,
1727 decode(por.po_release_id, null, poh.agent_id, por.agent_id) agent_id,
1728 pod.code_combination_id, -- not used for now
1729 Nvl(poa_gl.currency_code, 'DBI_ERR') CURRENCY_CODE,
1730 rat.GLOBAL_CUR_CONV_RATE,
1731 decode(pol.item_id, null, 1, decode(nvl(pll.unit_meas_lookup_code, pol.unit_meas_lookup_code), item.primary_unit_of_measure, 1,
1732 poa_dbi_uom_pkg.convert_to_item_base_uom(pol.item_id, par.master_organization_id, nvl(pll.unit_meas_lookup_code, pol.unit_meas_lookup_code) ,item.primary_uom_code ))) base_uom_conv_rate,
1733 decode(pll.matching_basis,
1734 'AMOUNT',
1735 Nvl(pod.amount_ordered,0) - Nvl(pod.amount_cancelled,0),
1736 (Nvl(pod.quantity_ordered,0) - Nvl(pod.quantity_cancelled,0)) * Nvl(pll.price_override,0)
1737 ) purchase_amount,
1738 ( case
1739 when (nvl(pol.negotiated_by_preparer_flag,'N')='Y') then
1740 decode(
1741 pll.matching_basis,
1742 'AMOUNT',
1743 nvl(pod.amount_ordered,0) - nvl(pod.amount_cancelled,0),
1744 (nvl(pod.quantity_ordered,0) - nvl(pod.quantity_cancelled,0)) * nvl(pll.price_override,0)
1745 )
1746 else 0 end
1747 ) contract_amount,
1748 ( case
1749 when (nvl(pol.negotiated_by_preparer_flag,'N')='N') then
1750 decode(
1751 pll.matching_basis,
1752 'AMOUNT',
1753 nvl(pod.amount_ordered,0) - nvl(pod.amount_cancelled,0),
1754 (nvl(pod.quantity_ordered,0) - nvl(pod.quantity_cancelled,0)) * nvl(pll.price_override,0)
1755 )
1756 else 0 end
1757 ) non_contract_amount,
1758 (CASE WHEN (pol.item_id IS NOT NULL
1759 AND pll.shipment_type = 'STANDARD'
1760 AND (nvl(ref.global_agreement_flag, 'N') = 'N')
1761 AND match.po_distribution_id IS NOT null
1762 and nvl(pol.negotiated_by_preparer_flag,'N')='N')
1763 THEN ((Nvl(pod.quantity_ordered,0) - Nvl(pod.quantity_cancelled,0)) * Nvl(pll.price_override,0))
1764 ELSE 0 END) pot_contract_amount,
1765 (CASE WHEN (pol.item_id IS NOT NULL
1766 AND pll.shipment_type = 'STANDARD'
1767 AND (nvl(ref.global_agreement_flag, 'N') = 'N')
1768 AND match.po_distribution_id IS NOT null
1769 AND nvl(pol.negotiated_by_preparer_flag,'N') = 'N')
1770 THEN ((Nvl(pod.quantity_ordered,0) - Nvl(pod.quantity_cancelled,0))
1771 * (Nvl(pll.price_override,0) - Nvl(low.lowest_price/nvl(pod.rate,1), Nvl(pll.price_override,0))
1772 )) ELSE 0 END ) pot_savings_amount, -- lowest price is already in the transactional currency of the PO
1773 pll.price_override,
1774 ( case
1775 when pll.value_basis = 'QUANTITY' then
1776 nvl(pod.quantity_ordered,0) - nvl(pod.quantity_cancelled,0)
1777 else null end
1778 ) quantity,
1779 null creation_mode, -- Automatic/Manual Manishas API not used for now
1780 pll.value_basis order_type,
1781 prl.catalog_type, -- not used for now
1782 pod.destination_type_code,
1783 pod.amount_billed,
1784 decode(
1785 pll.matching_basis,
1786 'AMOUNT',
1787 nvl(pod.amount_financed,0),
1788 (nvl(pod.quantity_financed,0) * nvl(pll.price_override,0))
1789 ) amount_financed,
1790 decode(
1791 pll.matching_basis,
1792 'AMOUNT',
1793 nvl(pod.amount_recouped,0),
1794 (nvl(pod.quantity_recouped,0) * nvl(pll.price_override,0))
1795 ) amount_recouped,
1796 ( case
1797 when pll.value_basis = 'QUANTITY' then
1798 pod.quantity_billed
1799 else null end
1800 ) quantity_billed,
1801 ( case
1802 when pll.value_basis = 'QUANTITY' then
1803 pod.quantity_financed
1804 else null end
1805 ) quantity_financed,
1806 ( case
1807 when pll.value_basis = 'QUANTITY' then
1808 pod.quantity_recouped
1809 else null end
1810 ) quantity_recouped,
1811 ( case
1812 when pll.matching_basis = 'QUANTITY' then
1813 pod.quantity_cancelled
1814 else null end
1815 ) quantity_cancelled,
1816 low.potential_contract_id,
1817 pll.shipment_type,
1818 nvl(prh.apps_source_code, 'PO') apps_source_code,
1819 ref.type_lookup_code from_document_type,
1820 pol.from_header_id from_document_id,
1821 (case when (pll.consigned_flag = 'Y') then 1
1822 when ((por.consigned_consumption_flag = 'Y') or (poh.consigned_consumption_flag = 'Y')) then 2 else 0 end) consigned_code,
1823 (case when pll.value_basis = 'QUANTITY'
1824 then decode(pol.item_id, null, nvl(pll.unit_meas_lookup_code, pol.unit_meas_lookup_code), item.primary_unit_of_measure)
1825 else null
1826 end) base_uom,
1827 (case when pll.value_basis = 'QUANTITY'
1828 then nvl(pll.unit_meas_lookup_code, pol.unit_meas_lookup_code)
1829 else null
1830 end) transaction_uom,
1831 prl.to_person_id requestor_id,
1832 l_start_time current_time,
1833 l_login login_id,
1834 l_user user_id,
1835 nvl(pod.rate,1) func_cur_conv_rate,
1836 rat.sglobal_cur_conv_rate,
1837 Nvl(pll.promised_date, pll.need_by_date) expected_date,
1838 Nvl(pll.days_late_receipt_allowed, 0) days_late_receipt_allowed,
1839 Nvl(pll.days_early_receipt_allowed, 0) days_early_receipt_allowed,
1840 pll.line_location_id,
1841 pol.item_id,
1842 pll.matching_basis,
1843 pll.receiving_routing_id,
1844 ccid.company_id,
1845 ccid.cost_center_id,
1846 pll.payment_type,
1847 ( case
1848 when nvl(style.progress_payment_flag,'N') = 'N' then 'N'
1849 else 'Y' end
1850 ) complex_work_flag,
1851 decode(pll.shipment_type, 'PREPAYMENT','Y','N') prepayment_flag,
1852 ---Begin Changes for Item Avg Price for computing non-zero price quantity
1853 decode(nvl(pll.price_override,0),0, 0, case when pll.value_basis =
1854 'QUANTITY' then nvl(pod.quantity_ordered,0) -
1855 nvl(pod.quantity_cancelled,0) else null end ) non_zero_quantity,
1856 ---End Changes for Item Avg Price
1857 negd.auction_header_id,
1858 negd.auction_line_number,
1859 negd.bid_number,
1860 negd.bid_line_number,
1861 nvl(negd.negotiation_creator_id,-1) negotiation_creator_id,
1862 nvl(negd.doctype_id,-1) doctype_id,
1863 negd.neg_current_price,
1864 negd.neg_func_cur_code,
1865 negd.neg_func_cur_conv_rate,
1866 negd.neg_global_cur_conv_rate,
1867 negd.neg_sglobal_cur_conv_rate,
1868 negd.neg_transaction_uom,
1869 negd.neg_base_uom,
1870 negd.neg_base_uom_conv_rate,
1871 nvl(pol.negotiated_by_preparer_flag,'N') negotiated_by_preparer_flag
1872 FROM
1873 poa_dbi_pod_inc inc,
1874 poa_dbi_neg_details negd,
1875 poa_dbi_pod_rates rat,
1876 po_doc_style_headers style,
1877 gl_sets_of_books poa_gl,
1878 ( select /*+ PARALLEL(a) PARALLEL(pod) NO_MERGE */
1879 pod.po_distribution_id,
1880 pod.creation_date,
1881 pod.req_distribution_id,
1882 pod.line_location_id,
1883 pod.org_id,
1884 pod.po_release_id,
1885 pod.code_combination_id,
1886 pod.set_of_books_id,
1887 pod.quantity_ordered,
1888 pod.quantity_cancelled,
1889 pod.amount_billed,
1890 pod.amount_financed,
1891 pod.amount_recouped,
1892 pod.quantity_billed,
1893 pod.quantity_financed,
1894 pod.quantity_recouped,
1895 pod.destination_type_code,
1896 pod.rate,
1897 min(approved_date) approved_date,
1898 pod.amount_ordered,
1899 pod.amount_cancelled
1900 from
1901 po_line_locations_archive_all a,
1902 po_distributions_all pod
1903 where pod.line_location_id = a.line_location_id(+)
1904 and a.approved_date(+) >= pod.creation_date
1905 and nvl(pod.distribution_type,'-99') <> 'AGREEMENT'
1906 group by
1907 pod.po_distribution_id,
1908 pod.creation_date,
1909 pod.req_distribution_id,
1910 pod.line_location_id,
1911 pod.org_id,
1912 pod.po_release_id,
1913 pod.code_combination_id,
1914 pod.set_of_books_id,
1915 pod.quantity_ordered,
1916 pod.quantity_cancelled,
1917 pod.amount_billed,
1918 pod.amount_financed,
1919 pod.amount_recouped,
1920 pod.quantity_billed,
1921 pod.quantity_financed,
1922 pod.quantity_recouped,
1923 pod.destination_type_code,
1924 pod.rate,
1925 pod.amount_ordered,
1926 pod.amount_cancelled
1927 ) pod,
1928 po_line_locations_all pll,
1929 po_lines_all pol,
1930 po_headers_all poh,
1931 po_requisition_lines_all prl,
1932 po_req_distributions_all prd,
1933 financials_system_params_all fsp,
1934 poa_dbi_pod_lowest_price_temp low,
1935 poa_dbi_pod_match_temp match,
1936 mtl_system_items item,
1937 po_releases_all por,
1938 po_headers_all ref,
1939 po_requisition_headers_all prh,
1940 mtl_parameters par,
1941 fii_gl_ccid_dimensions ccid,
1942 pon_auction_headers_all ponh
1943 WHERE inc.primary_key = pod.PO_DISTRIBUTION_ID
1944 and inc.func_cur_code = rat.func_cur_code
1945 and inc.rate_date = rat.rate_date
1946 and inc.txn_cur_code = rat.txn_cur_code
1947 and poh.po_header_id = pol.po_header_id
1948 and poh.style_id = style.style_id
1949 and pol.po_line_id = pll.po_line_id
1950 and por.po_release_id (+) = pll.po_release_id
1951 and ref.po_header_id (+) = pol.from_header_id
1952 and pll.line_location_id = pod.line_location_id
1953 and poa_gl.set_of_books_id = pod.set_of_books_id
1954 and pod.org_id = fsp.org_id
1955 AND pod.req_distribution_id = prd.distribution_id(+)
1956 and prd.requisition_line_id = prl.requisition_line_id(+)
1957 and prl.requisition_header_id = prh.requisition_header_id(+)
1958 and pll.approved_flag = 'Y'
1959 and pod.creation_date is not null
1960 AND inc.primary_key = match.po_distribution_id(+)
1961 AND inc.primary_key = low.po_distribution_id(+)
1962 and inc.primary_key = negd.po_distribution_id(+) /* Check for presence of Auction Details */
1963 and fsp.inventory_organization_id = par.organization_id
1964 and pol.auction_header_id = ponh.auction_header_id(+)
1965 and item.inventory_item_id(+) = pol.item_id
1966 and par.master_organization_id = nvl(item.organization_id, par.master_organization_id)
1967 and pod.code_combination_id = ccid.code_combination_id(+)
1968 ) s;
1969 COMMIT;
1970 else
1971
1972 FOR v_batch_no IN 1..l_no_batch LOOP
1973 bis_collection_utilities.log('batch no='||v_batch_no || ' Sysdate=' ||To_char(Sysdate, 'DD/MM/YYYY HH24:MI:SS'), 1);
1974
1975 merge INTO poa_dbi_pod_f T
1976 using
1977 (
1978 SELECT /*+ cardinality(inc,1) */
1979 pod.po_distribution_id,
1980 poh.po_header_id,
1981 pol.po_line_id,
1982 pll.line_location_id,
1983 pod.po_release_id,
1984 pll.org_id ,
1985 pll.ship_to_organization_id ,
1986 pll.approved_date approved_date,
1987 /* important that we are using pll.approved_date. In case of archiving on print, poh/por level approved_date
1988 may not be present. This is the true earliest approval date at distribution level */
1989 pod.creation_date creation_date,
1990 poh.vendor_id,
1991 poh.vendor_site_id,
1992 poa_dbi_items_pkg.getitemkey(pol.item_id, par.master_organization_id, pol.category_id,
1993 pol.vendor_product_num, poh.vendor_id, pol.item_description) po_item_id,
1994 pol.category_id,
1995 decode(por.po_release_id, null, poh.agent_id, por.agent_id) agent_id,
1996 pod.code_combination_id, -- not used for now
1997 Nvl(poa_gl.currency_code, 'DBI_ERR') CURRENCY_CODE,
1998 --Nvl(pod.rate, 1) FUNC_CUR_CONV_RATE,
1999 /* poa_dbi_currency_pkg.get_global_currency_rate(poh.rate_type, poh.currency_code, NVL(pod.rate_date,
2000 pod.creation_date), pod.rate) GLOBAL_CUR_CONV_RATE, */
2001 rat.GLOBAL_CUR_CONV_RATE,
2002 decode(pol.item_id, null, 1, poa_dbi_uom_pkg.convert_to_item_base_uom(pol.item_id, par.master_organization_id, nvl(pll.unit_meas_lookup_code, pol.unit_meas_lookup_code))) base_uom_conv_rate,
2003 (case when pll.matching_basis = 'AMOUNT'
2004 then (Nvl(pod.amount_ordered,0) - Nvl(pod.amount_cancelled,0))
2005 else (Nvl(pod.quantity_ordered,0) - Nvl(pod.quantity_cancelled,0)) * Nvl(pll.price_override,0)
2006 end) purchase_amount,
2007 ( case when nvl(pol.negotiated_by_preparer_flag,'N') = 'Y'
2008 then decode(pll.matching_basis,'AMOUNT',
2009 nvl(pod.amount_ordered,0) - nvl(pod.amount_cancelled,0),
2010 (nvl(pod.quantity_ordered,0) - nvl(pod.quantity_cancelled,0)) * nvl(pll.price_override,0))
2011 else 0
2012 end
2013 ) contract_amount,
2014 ( case when nvl(pol.negotiated_by_preparer_flag,'N') = 'N'
2015 then decode(pll.matching_basis, 'AMOUNT',
2016 nvl(pod.amount_ordered,0) - nvl(pod.amount_cancelled,0),
2017 (nvl(pod.quantity_ordered,0) - nvl(pod.quantity_cancelled,0)) * nvl(pll.price_override,0))
2018 else 0
2019 end
2020 ) non_contract_amount,
2021 (CASE WHEN (pol.item_id IS NOT NULL
2022 AND pll.shipment_type = 'STANDARD'
2023 AND (nvl(ref.global_agreement_flag, 'N') = 'N')
2024 AND match.po_distribution_id IS NOT null
2025 and nvl(pol.negotiated_by_preparer_flag,'N') = 'N')
2026 THEN ((Nvl(pod.quantity_ordered,0) - Nvl(pod.quantity_cancelled,0)) * Nvl(pll.price_override,0))
2027 ELSE 0 END) pot_contract_amount,
2028 (CASE WHEN (pol.item_id IS NOT NULL
2029 AND pll.shipment_type = 'STANDARD'
2030 AND (nvl(ref.global_agreement_flag, 'N') = 'N')
2031 AND match.po_distribution_id IS NOT null
2032 and nvl(pol.negotiated_by_preparer_flag,'N') = 'N')
2033 THEN ((Nvl(pod.quantity_ordered,0) - Nvl(pod.quantity_cancelled,0))
2034 * (Nvl(pll.price_override,0) - Nvl(poa_dbi_savings_pkg.get_lowest_possible_price(pod.creation_date,
2035 poh.org_id,
2036 pll.need_by_date,
2037 pll.quantity, --shipment quantity
2038 nvl(pll.unit_meas_lookup_code,pol.unit_meas_lookup_code),
2039 poh.currency_code,
2040 pol.item_id,
2041 pol.item_revision,
2042 pol.category_id,
2043 pll.ship_to_location_id,
2044 poa_gl.currency_code, -- standard PO functional currency
2045 nvl(pod.rate_date, pod.creation_date), -- rate date
2046 match.ship_to_ou_id, -- ship to OU
2047 pll.ship_to_organization_id,
2048 pod.po_distribution_id,
2049 'PRICE')/nvl(pod.rate,1),
2050 Nvl(pll.price_override,0) )
2051 )) ELSE 0 END ) pot_savings_amount,
2052 pll.price_override,
2053 (case when pll.value_basis = 'QUANTITY'
2054 then Nvl(pod.quantity_ordered,0) - Nvl(pod.quantity_cancelled,0)
2055 else null
2056 end) quantity,
2057 null creation_mode, -- Automatic/Manual Manishas API not used for now
2058 pll.value_basis order_type,
2059 prl.catalog_type, -- not used for now
2060 pod.destination_type_code,
2061 -- Decode(pod.destination_type_code, 'EXPENSE', 'I', 'D') spend_type, -- Indirect/Direct not used for now
2062 pod.amount_billed,
2063 pod.amount_financed,
2064 pod.amount_recouped,
2065 (case when pll.value_basis = 'QUANTITY'
2066 then pod.quantity_billed
2067 else null
2068 end) quantity_billed,
2069 (case when pll.value_basis = 'QUANTITY'
2070 then pod.quantity_financed
2071 else null
2072 end) quantity_financed,
2073 (case when pll.value_basis = 'QUANTITY'
2074 then pod.quantity_recouped
2075 else null
2076 end) quantity_recouped,
2077 (case when pll.value_basis = 'QUANTITY'
2078 then pod.quantity_cancelled
2079 else null
2080 end) quantity_cancelled,
2081 (case when (pol.item_id is not null and pll.shipment_type = 'STANDARD' and match.po_distribution_id is not null)
2082 then poa_dbi_savings_pkg.get_lowest_possible_price(pod.creation_date,
2083 poh.org_id,
2084 pll.need_by_date,
2085 pll.quantity, --shipment quantity
2086 nvl(pll.unit_meas_lookup_code,pol.unit_meas_lookup_code),
2087 poh.currency_code,
2088 pol.item_id,
2089 pol.item_revision,
2090 pol.category_id,
2091 pll.ship_to_location_id,
2092 poa_gl.currency_code, -- standard PO functional currency
2093 nvl(pod.rate_date, pod.creation_date), -- rate date
2094 match.ship_to_ou_id, -- ship to OU
2095 pll.ship_to_organization_id,
2096 pod.po_distribution_id,
2097 'BLANKET')
2098 else null end) potential_contract_id,
2099 pll.shipment_type,
2100 nvl(prh.apps_source_code, 'PO') apps_source_code,
2101 ref.type_lookup_code from_document_type,
2102 pol.from_header_id from_document_id,
2103 (case when (pll.consigned_flag = 'Y') then 1
2104 when ((por.consigned_consumption_flag = 'Y') or (poh.consigned_consumption_flag = 'Y')) then 2 else 0 end) consigned_code,
2105 (case when pll.value_basis = 'QUANTITY'
2106 then decode(pol.item_id, null, nvl(pll.unit_meas_lookup_code, pol.unit_meas_lookup_code), poa_dbi_uom_pkg.get_item_base_uom(pol.item_id, par.master_organization_id, nvl(pll.unit_meas_lookup_code, pol.unit_meas_lookup_code)))
2107 else null
2108 end) base_uom,
2109 (case when pll.value_basis = 'QUANTITY'
2110 then nvl(pll.unit_meas_lookup_code, pol.unit_meas_lookup_code)
2111 else null
2112 end) transaction_uom,
2113 prl.to_person_id requestor_id,
2114 -- null invoice_price_variance, -- IPV not used for now
2115 -- 0 po_approval_cycle_time, -- cycle time delayed calc
2116 l_start_time current_time,
2117 l_login login_id,
2118 l_user user_id,
2119 nvl(pod.rate,1) func_cur_conv_rate,
2120 rat.sglobal_cur_conv_rate,
2121 Nvl(pll.promised_date, pll.need_by_date) expected_date,
2122 Nvl(pll.days_late_receipt_allowed, 0) days_late_receipt_allowed,
2123 Nvl(pll.days_early_receipt_allowed, 0) days_early_receipt_allowed,
2124 pol.item_id,
2125 pll.matching_basis,
2126 pll.receiving_routing_id,
2127 ccid.company_id,
2128 ccid.cost_center_id,
2129 pll.payment_type,
2130 ( case
2131 when nvl(doc_style.progress_payment_flag,'N') = 'N' then 'N'
2132 else 'Y' end
2133 ) complex_work_flag,
2134 decode(pll.shipment_type, 'PREPAYMENT','Y','N') prepayment_flag,
2135 ---Begin Changes for Item Avg Price
2136 decode(nvl(pll.price_override,0),0,0,case when pll.value_basis =
2137 'QUANTITY' then nvl(pod.quantity_ordered,0) -
2138 nvl(pod.quantity_cancelled,0) else null end ) non_zero_quantity,
2139 ---End Changes for Item Avg Price
2140 negd.auction_header_id,
2141 negd.auction_line_number,
2142 negd.bid_number,
2143 negd.bid_line_number,
2144 nvl(negd.negotiation_creator_id,-1) negotiation_creator_id,
2145 nvl(negd.doctype_id,-1) doctype_id,
2146 negd.neg_current_price,
2147 negd.neg_func_cur_code,
2148 negd.neg_func_cur_conv_rate,
2149 negd.neg_global_cur_conv_rate,
2150 negd.neg_sglobal_cur_conv_rate,
2151 negd.neg_transaction_uom,
2152 negd.neg_base_uom,
2153 negd.neg_base_uom_conv_rate,
2154 nvl(pol.negotiated_by_preparer_flag,'N') negotiated_by_preparer_flag
2155 FROM
2156 poa_dbi_pod_inc inc,
2157 poa_dbi_neg_details negd,
2158 poa_dbi_pod_rates rat,
2159 gl_sets_of_books poa_gl,
2160 po_distributions_all pod,
2161 po_line_locations_all pll,
2162 po_lines_all pol,
2163 po_headers_all poh,
2164 po_requisition_lines_all prl,
2165 po_req_distributions_all prd,
2166 financials_system_params_all fsp,
2167 po_releases_all por,
2168 po_headers_all ref,
2169 po_requisition_headers_all prh,
2170 mtl_parameters par,
2171 fii_gl_ccid_dimensions ccid,
2172 po_doc_style_headers doc_style,
2173 pon_auction_headers_all ponh,
2174 ( SELECT /*+ cardinality(inc,1) */
2175 distinct pod.po_distribution_id, hro.ship_to_ou_id
2176 FROM
2177 po_distributions_all pod,
2178 po_line_locations_all psc,
2179 po_lines_all plc,
2180 poa_dbi_pod_inc inc,
2181 po_headers_all ga,
2182 (select /*+ no_merge */ to_number(hro.org_information3) ship_to_ou_id,organization_id
2183 from hr_organization_information hro where
2184 hro.org_information_context='Accounting Information') hro,
2185 po_doc_style_headers style,
2186 po_headers_all phc,
2187 ( SELECT
2188 pl.item_id,
2189 ph.start_date,
2190 ph.end_date,
2191 pl.expiration_date,
2192 ph.org_id,
2193 ph.global_agreement_flag,
2194 ph.po_header_id,
2195 pl.creation_date
2196 FROM
2197 po_lines_all pl,
2198 po_headers_all ph
2199 WHERE ph.type_lookup_code = 'BLANKET'
2200 AND pl.price_break_lookup_code IS NOT null
2201 AND ph.approved_flag IN ('Y', 'R')
2202 and ph.po_header_id = pl.po_header_id
2203 and nvl(ph.cancel_flag, 'N') = 'N'
2204 and nvl(pl.cancel_flag, 'N') = 'N'
2205 ) v1,
2206 ( select distinct po_header_id, purchasing_org_id
2207 from po_ga_org_assignments pgoa
2208 where enabled_flag = 'Y'
2209 ) pgoa
2210 WHERE plc.po_line_id = psc.po_line_id
2211 and psc.line_location_id = pod.line_location_id
2212 and phc.po_header_id = plc.po_header_id
2213 and phc.style_id = style.style_id
2214 and nvl(style.progress_payment_flag,'N') = 'N'
2215 and psc.shipment_type = 'STANDARD'
2216 and plc.from_header_id = ga.po_header_id (+)
2217 and nvl(ga.global_agreement_flag, 'N') = 'N'
2218 and psc.approved_flag = 'Y'
2219 and plc.item_id is not null
2220 and pod.creation_date is not null
2221 and nvl(pod.distribution_type,'-99') <> 'AGREEMENT'
2222 and v1.item_id = plc.item_id
2223 AND inc.primary_key = pod.po_distribution_id
2224 and v1.po_header_id = pgoa.po_header_id (+)
2225 and to_number(hro.organization_id) = psc.ship_to_organization_id
2226 and (
2227 ( pgoa.purchasing_org_id in
2228 ( select tfh.start_org_id
2229 from
2230 mtl_procuring_txn_flow_hdrs_v tfh,
2231 financials_system_params_all fsp1,
2232 financials_system_params_all fsp2
2233 where pod.creation_date between nvl(tfh.start_date, pod.creation_date) and nvl(tfh.end_date, pod.creation_date)
2234 and fsp1.org_id = tfh.start_org_id
2235 and fsp1.purch_encumbrance_flag = 'N'
2236 and fsp2.org_id = tfh.end_org_id
2237 and fsp2.purch_encumbrance_flag = 'N'
2238 and tfh.end_org_id = hro.ship_to_ou_id
2239 and ((tfh.qualifier_code is null) or (tfh.qualifier_code = 1 and tfh.qualifier_value_id = plc.category_id))
2240 and ((tfh.organization_id is null) or (tfh.organization_id = psc.ship_to_organization_id))
2241 )
2242 )
2243 or
2244 (
2245 nvl(pgoa.purchasing_org_id, hro.ship_to_ou_id) = hro.ship_to_ou_id
2246 )
2247 )
2248 and (
2249 ( v1.org_id = hro.ship_to_ou_id
2250 and nvl(v1.global_agreement_flag, 'N') = 'N'
2251 )
2252 or
2253 ( v1.global_agreement_flag = 'Y'
2254 and pgoa.purchasing_org_id is not null
2255 )
2256 )
2257 and Trunc(pod.creation_date) between nvl(v1.start_date, Trunc(pod.creation_date))
2258 and nvl(v1.end_date, pod.creation_date)
2259 and pod.creation_date >= v1.creation_date
2260 and Trunc(pod.creation_date) <= nvl(v1.expiration_date, pod.creation_date)
2261 ) match
2262 WHERE inc.primary_key = pod.PO_DISTRIBUTION_ID
2263 and inc.func_cur_code = rat.func_cur_code
2264 and inc.txn_cur_code = rat.txn_cur_code
2265 and inc.rate_date = rat.rate_date
2266 and inc.primary_key = negd.po_distribution_id(+) /* Check for presence of Auction Details */
2267 --and (inc.primary_key = negd.po_distribution_id or negd.po_distribution_id is null)
2268 and poh.po_header_id = pol.po_header_id
2269 and pol.po_line_id = pll.po_line_id
2270 and por.po_release_id (+) = pll.po_release_id
2271 and ref.po_header_id (+) = pol.from_header_id
2272 and pll.line_location_id = pod.line_location_id
2273 and poa_gl.set_of_books_id = pod.set_of_books_id
2274 and pod.org_id = fsp.org_id
2275 and poh.style_id = doc_style.style_id
2276 and nvl(pod.distribution_type,'-99') <> 'AGREEMENT'
2277 AND pod.req_distribution_id = prd.distribution_id(+)
2278 and prd.requisition_line_id = prl.requisition_line_id(+)
2279 and prl.requisition_header_id = prh.requisition_header_id(+)
2280 and fsp.inventory_organization_id = par.organization_id
2281 and pll.approved_flag = 'Y'
2282 and pod.creation_date is not NULL
2283 and pol.auction_header_id = ponh.auction_header_id(+)
2284 AND inc.batch_id = v_batch_no
2285 AND inc.primary_key = match.po_distribution_id(+)
2286 and pod.code_combination_id = ccid.code_combination_id(+)
2287 ) S
2288 ON (T.po_distribution_id = S.po_distribution_id)
2289 WHEN matched THEN UPDATE SET
2290 /* there are a few columns that we DONT update, such as earliest approval date and currency */
2291 t.ship_to_org_id = s.ship_to_organization_id,
2292 t.supplier_id = s.vendor_id,
2293 t.supplier_site_id = s.vendor_site_id,
2294 t.po_item_id = s.po_item_id,
2295 t.category_id = s.category_id,
2296 t.buyer_id = s.agent_id,
2297 t.global_cur_conv_rate = s.global_cur_conv_rate,
2298 t.base_uom_conv_rate = s.base_uom_conv_rate,
2299 t.purchase_amount_b = s.purchase_amount,
2300 t.contract_amount_b = decode(s.prepayment_flag,'Y',0,s.contract_amount),
2301 t.non_contract_amount_b = decode(s.prepayment_flag,'Y',0,s.non_contract_amount),
2302 t.pot_contract_amount_b = decode(s.prepayment_flag,'Y',0,s.pot_contract_amount),
2303 t.pot_savings_amount_b = decode(s.prepayment_flag,'Y',0,s.pot_savings_amount),
2304 t.unit_price = s.price_override / s.base_uom_conv_rate,
2305 t.quantity = s.quantity * s.base_uom_conv_rate,
2306 t.creation_mode = s.creation_mode,
2307 t.catalog_type = s.catalog_type,
2308 t.destination_type_code = s.destination_type_code,
2309 t.amt_billed = s.amount_billed,
2310 t.amt_financed = s.amount_financed,
2311 t.amt_recouped = s.amount_recouped,
2312 t.qty_billed = s.quantity_billed * s.base_uom_conv_rate,
2313 t.qty_financed = s.quantity_financed * s.base_uom_conv_rate,
2314 t.qty_recouped = s.quantity_recouped * s.base_uom_conv_rate,
2315 t.qty_cancelled = s.quantity_cancelled * s.base_uom_conv_rate,
2316 t.potential_contract_id = s.potential_contract_id,
2317 t.shipment_type = s.shipment_type,
2318 t.apps_source_code = s.apps_source_code,
2319 t.from_document_type = s.from_document_type,
2320 t.from_document_id = s.from_document_id,
2321 t.consigned_code = s.consigned_code,
2322 t.base_uom = s.base_uom,
2323 t.transaction_uom = s.transaction_uom,
2324 t.requestor_id = s.requestor_id,
2325 t.last_update_login = s.login_id,
2326 t.last_updated_by = s.user_id,
2327 t.last_update_date = s.current_time,
2328 t.func_cur_conv_rate = s.func_cur_conv_rate,
2329 t.sglobal_cur_conv_rate = s.sglobal_cur_conv_rate,
2330 t.expected_date = s.expected_date,
2331 t.days_late_receipt_allowed = s.days_late_receipt_allowed,
2332 t.days_early_receipt_allowed = s.days_early_receipt_allowed,
2333 t.price_override = s.price_override,
2334 t.line_location_id = s.line_location_id,
2335 t.item_id = s.item_id,
2336 t.matching_basis = s.matching_basis,
2337 t.receiving_routing_id = s.receiving_routing_id,
2338 t.company_id = s.company_id,
2339 t.cost_center_id = s.cost_center_id,
2340 t.payment_type = s.payment_type,
2341 t.complex_work_flag = s.complex_work_flag ,
2342 ---Begin Changes for Item Avg Price
2343 t.non_zero_quantity = s.non_zero_quantity * s.base_uom_conv_rate,
2344 ---End Changes for Item Avg Price
2345 t.neg_current_price = s.neg_current_price / s.neg_base_uom_conv_rate,
2346 t.neg_func_cur_code = s.neg_func_cur_code,
2347 t.neg_func_cur_conv_rate = s.neg_func_cur_conv_rate,
2348 t.neg_global_cur_conv_rate = s.neg_global_cur_conv_rate,
2349 t.neg_sglobal_cur_conv_rate = s.neg_sglobal_cur_conv_rate,
2350 t.neg_transaction_uom = s.neg_transaction_uom,
2351 t.neg_base_uom = s.neg_base_uom,
2352 t.neg_base_uom_conv_rate = s.neg_base_uom_conv_rate,
2353 t.negotiated_by_preparer_flag = s.negotiated_by_preparer_flag
2354 WHEN NOT matched THEN INSERT
2355 (
2356 t.po_distribution_id ,
2357 t.po_header_id ,
2358 t.po_line_id ,
2359 t.po_release_id,
2360 t.creation_operating_unit_id,
2361 t.ship_to_org_id,
2362 t.approved_date ,
2363 t.distribution_creation_date ,
2364 t.supplier_id ,
2365 t.supplier_site_id,
2366 t.po_item_id,
2367 t.category_id,
2368 t.buyer_id,
2369 t.code_combination_id,
2370 t.func_cur_code ,
2371 t.global_cur_conv_rate,
2372 t.base_uom_conv_rate,
2373 t.purchase_amount_b,
2374 t.contract_amount_b,
2375 t.non_contract_amount_b,
2376 t.pot_contract_amount_b,
2377 t.pot_savings_amount_b,
2378 t.unit_price,
2379 t.quantity,
2380 t.creation_mode,
2381 t.order_type ,
2382 t.catalog_type,
2383 t.destination_type_code ,
2384 t.amt_billed,
2385 t.amt_financed,
2386 t.amt_recouped,
2387 t.qty_billed,
2388 t.qty_financed,
2389 t.qty_recouped,
2390 t.qty_cancelled,
2391 t.potential_contract_id,
2392 t.shipment_type,
2393 t.apps_source_code,
2394 t.from_document_type,
2395 t.from_document_id,
2396 t.consigned_code,
2397 t.base_uom,
2398 t.transaction_uom,
2399 t.requestor_id,
2400 -- t.invoice_price_variance,
2401 -- t.po_approval_cycle_time,
2402 t.start_date_active ,
2403 t.last_update_login ,
2404 t.creation_date,
2405 t.last_updated_by,
2406 t.last_update_date,
2407 t.func_cur_conv_rate,
2408 t.sglobal_cur_conv_rate,
2409 t.expected_date,
2410 t.days_late_receipt_allowed,
2411 t.days_early_receipt_allowed,
2412 t.price_override,
2413 t.line_location_id,
2414 t.item_id,
2415 t.matching_basis,
2416 t.receiving_routing_id,
2417 t.company_id,
2418 t.cost_center_id,
2419 t.payment_type,
2420 t.complex_work_flag ,
2421 ---Begin Changes for Item Avg Price
2422 t.non_zero_quantity,
2423 ---End Changes for Item Avg Price
2424 t.auction_header_id,
2425 t.auction_line_number,
2426 t.bid_number,
2427 t.bid_line_number,
2428 t.negotiation_creator_id,
2429 t.doctype_id,
2430 t.neg_current_price,
2431 t.neg_func_cur_code,
2432 t.neg_func_cur_conv_rate,
2433 t.neg_global_cur_conv_rate,
2434 t.neg_sglobal_cur_conv_rate,
2435 t.neg_transaction_uom,
2436 t.neg_base_uom,
2437 t.neg_base_uom_conv_rate,
2438 t.negotiated_by_preparer_flag
2439 ) VALUES
2440 ( s.po_distribution_id,
2441 s.po_header_id,
2442 s.po_line_id,
2443 s.po_release_id,
2444 s.org_id ,
2445 s.ship_to_organization_id ,
2446 --s.approved_date,
2447 Trunc(NVL(POA_OLTP_GENERIC_PKG.get_approved_date_pll(s.creation_date, s.line_location_id), s.approved_date)),
2448 Trunc(s.creation_date) ,
2449 s.vendor_id,
2450 s.vendor_site_id,
2451 s.po_item_id,
2452 s.category_id,
2453 s.agent_id,
2454 s.code_combination_id,
2455 s.currency_code,
2456 s.global_cur_conv_rate,
2457 s.base_uom_conv_rate,
2458 s.purchase_amount,
2459 decode(s.prepayment_flag,'Y',0,s.contract_amount),
2460 decode(s.prepayment_flag,'Y',0,s.non_contract_amount),
2461 decode(s.prepayment_flag,'Y',0,s.pot_contract_amount),
2462 decode(s.prepayment_flag,'Y',0,s.pot_savings_amount),
2463 s.price_override / s.base_uom_conv_rate,
2464 s.quantity * s.base_uom_conv_rate,
2465 s.creation_mode,
2466 s.order_type,
2467 s.catalog_type,
2468 s.destination_type_code,
2469 s.amount_billed,
2470 s.amount_financed,
2471 s.amount_recouped,
2472 s.quantity_billed * s.base_uom_conv_rate,
2473 s.quantity_financed * s.base_uom_conv_rate,
2474 s.quantity_recouped * s.base_uom_conv_rate,
2475 s.quantity_cancelled * s.base_uom_conv_rate,
2476 s.potential_contract_id,
2477 s.shipment_type,
2478 s.apps_source_code,
2479 s.from_document_type,
2480 s.from_document_id,
2481 s.consigned_code,
2482 s.base_uom,
2483 s.transaction_uom,
2484 s.requestor_id,
2485 -- s.invoice_price_variance,
2486 -- s.approved_date - s.creation_date ,
2487 s.current_time, -- not sure if this is what it means
2488 s.login_id ,
2489 s.current_time,
2490 s.user_id,
2491 s.current_time,
2492 s.func_cur_conv_rate,
2493 s.sglobal_cur_conv_rate,
2494 s.expected_date,
2495 s.days_late_receipt_allowed,
2496 s.days_early_receipt_allowed,
2497 s.price_override,
2498 s.line_location_id,
2499 s.item_id,
2500 s.matching_basis,
2501 s.receiving_routing_id,
2502 s.company_id,
2503 s.cost_center_id,
2504 s.payment_type,
2505 s.complex_work_flag,
2506 ---Begin Changes for Item Avg Price
2507 s.non_zero_quantity * s.base_uom_conv_rate,
2508 ---End Changes for Item Avg Price
2509 s.auction_header_id,
2510 s.auction_line_number,
2511 s.bid_number,
2512 s.bid_line_number,
2513 s.negotiation_creator_id,
2514 s.doctype_id,
2515 s.neg_current_price / s.neg_base_uom_conv_rate,
2516 s.neg_func_cur_code,
2517 s.neg_func_cur_conv_rate,
2518 s.neg_global_cur_conv_rate,
2519 s.neg_sglobal_cur_conv_rate,
2520 s.neg_transaction_uom,
2521 s.neg_base_uom,
2522 s.neg_base_uom_conv_rate,
2523 s.negotiated_by_preparer_flag
2524 );
2525
2526 COMMIT;
2527 bis_collection_utilities.log('best price calculation hit='|| poa_dbi_savings_pkg.g_hit_count, 2);
2528 poa_dbi_savings_pkg.g_hit_count := 0;
2529
2530 DBMS_APPLICATION_INFO.SET_ACTION('batch ' || v_batch_no || ' done');
2531 END LOOP;
2532 END IF;
2533 END IF;
2534 /*
2535 if (l_no_batch is NOT NULL) then
2536 FOR v_batch_no IN 1..l_no_batch LOOP
2537 bis_collection_utilities.log('EAD batch no='||v_batch_no || ' Sysdate=' ||To_char(Sysdate, 'DD/MM/YYYY HH24:MI:SS'), 1);
2538 update poa_dbi_pod_f f
2539 set (approved_date, po_approval_cycle_time) = (SELECT min(approved_date), MIN(approved_date - pod.creation_date)
2540 from po_line_locations_archive_all pll
2541 ,po_distributions_all pod
2542 where pod.po_distribution_id = f.po_distribution_id
2543 and pll.line_location_id = pod.line_location_id
2544 and pll.approved_date >= pod.creation_date)
2545 where f.po_distribution_id in (select primary_key from poa_dbi_pod_inc
2546 where batch_id = v_batch_no);
2547 COMMIT;
2548 DBMS_APPLICATION_INFO.SET_ACTION('EAD batch ' || v_batch_no || ' done');
2549 END LOOP;
2550 END IF;
2551 */
2552
2553 bis_collection_utilities.log('Collection complete '|| 'Sysdate=' ||To_char(Sysdate, 'DD/MM/YYYY HH24:MI:SS'), 0);
2554 bis_collection_utilities.wrapup(TRUE, l_count, 'POA DBI PO DIST COLLECTION SUCEEDED', To_date(l_start_date, '''YYYY/MM/DD HH24:MI:SS'''), To_date(l_end_date, '''YYYY/MM/DD HH24:MI:SS'''));
2555 g_init := false;
2556 DBMS_APPLICATION_INFO.set_module(NULL, NULL);
2557 EXCEPTION
2558 WHEN OTHERS THEN
2559 DBMS_APPLICATION_INFO.SET_ACTION('error');
2560 errbuf:=sqlerrm;
2561 retcode:=sqlcode;
2562 bis_collection_utilities.log('Collection failed with '||errbuf||':'||retcode||' Sysdate=' ||To_char(Sysdate, 'DD/MM/YYYY HH24:MI:SS'), 0);
2563 -- dbms_output.put_line(l_start_date || l_end_date);
2564 bis_collection_utilities.wrapup(FALSE, l_count, errbuf||':'||retcode,
2565 To_date(l_start_date, '''YYYY/MM/DD HH24:MI:SS'''), To_date(l_end_date, '''YYYY/MM/DD HH24:MI:SS'''));
2566
2567
2568 RAISE;
2569 END populate_po_dist_facts;
2570
2571 END POA_DBI_PO_DIST_F_C;