[Home] [Help]
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;
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,
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)
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: