[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
insert /*+ APPEND PARALLEL(F) */ into isc_dbi_tmp_bsa_order_lines f (
order_line_id,
order_line_header_id,
order_number,
line_number,
inventory_item_id,
item_inv_org_id,
blanket_line_id,
blanket_header_id,
blanket_number,
blanket_line_number,
org_id,
salesrep_id,
agreement_type_id,
sold_to_org_id,
time_activation_date_id,
time_expiration_date_id,
time_termination_date_id,
time_fulfilled_date_id,
time_effective_end_date_id,
h_start_date_active,
l_start_date_active,
h_end_date_active,
l_end_date_active,
termination_date,
blanket_min_amt,
blanket_line_min_amt,
fulfilled_amt_g,
fulfilled_amt_g1,
accumulated_fulfilled_amt_g,
accumulated_fulfilled_amt_g1,
h_cnt,
l_cnt,
transactional_curr_code,
transaction_phase_code,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
program_id,
program_login_id,
program_application_id,
request_id
)
select /*+ USE_HASH(bh,bhe,bl,ble,r,book) PARALLEL(bh) PARALLEL(bhe) PARALLEL(bl) PARALLEL(ble) PARALLEL(r) PARALLEL(book) FULL(bh) FULL(bhe)*/
book.line_id,
book.header_id,
book.order_number,
book.line_number,
book.inventory_item_id,
book.item_inv_org_id,
bl.line_id,
bh.header_id,
bh.order_number,
ble.line_number,
bh.org_id,
bh.salesrep_id,
bh.order_type_id,
bh.sold_to_org_id,
trunc(decode(bhe.blanket_min_amount, null, ble.start_date_active, bhe.start_date_active)),
trunc(decode(bhe.blanket_min_amount, null, ble.end_date_active, bhe.end_date_active)),
trunc(r.creation_date),
book.time_fulfilled_date_id,
least(nvl(trunc(r.creation_date), trunc(decode(bhe.blanket_min_amount, null, ble.end_date_active, bhe.end_date_active))),
nvl(trunc(decode(bhe.blanket_min_amount, null, ble.end_date_active, bhe.end_date_active)), trunc(r.creation_date))),
bhe.start_date_active,
ble.start_date_active,
bhe.end_date_active,
ble.end_date_active,
r.creation_date,
bhe.blanket_min_amount,
ble.blanket_line_min_amount,
book.fulfilled_amt_g,
book.fulfilled_amt_g1,
sum(book.fulfilled_amt_g) over (partition by bh.header_id order by book.time_fulfilled_date_id, book.line_id range unbounded preceding),
sum(book.fulfilled_amt_g1) over (partition by bh.header_id order by book.time_fulfilled_date_id, book.line_id range unbounded preceding),
count(1) over (partition by bh.header_id),
count(1) over (partition by bl.line_id),
bh.transactional_curr_code,
bh.transaction_phase_code,
bh.created_by,
bh.creation_date,
bh.last_updated_by,
bh.last_update_date,
bh.last_update_login,
null,
null,
null,
null
from oe_blanket_headers_all bh,
oe_blanket_headers_ext bhe,
oe_blanket_lines_all bl,
oe_blanket_lines_ext ble,
oe_reasons r,
isc_book_sum2_f book
where bh.order_number = bhe.order_number
and bh.header_id = bl.header_id
and bl.line_id = ble.line_id
and r.entity_code(+) = 'BLANKET_HEADER'
and r.reason_type(+) = 'CONTRACT_TERMINATION'
and r.entity_id(+) = bh.header_id
and book.blanket_number(+) = ble.order_number
and book.blanket_line_number(+) = ble.line_number
and bh.transaction_phase_code = 'F'
and bh.sold_to_org_id is not null
and (bhe.blanket_min_amount is not null or ble.blanket_line_min_amount is not null)
and nvl(r.creation_date,bhe.start_date_active+1) >= bhe.start_date_active
and book.line_category_code(+) <> 'RETURN'
and book.order_source_id(+) <> 10
and book.order_source_id(+) <> 27
and book.ordered_quantity(+) <> 0
and book.unit_selling_price(+) <> 0
and book.charge_periodicity_code(+) is null;
insert /*+ APPEND */ into isc_curr_bsa_order_lines f (
from_currency,
conversion_date,
rate1,
rate2
)
select transactional_curr_code from_currency,
time_activation_date_id conversion_date,
decode(transactional_curr_code, g_global_currency, 1,
fii_currency.get_global_rate_primary(transactional_curr_code, time_activation_date_id)) rate1,
decode(transactional_curr_code, g_sec_global_currency, 1,
fii_currency.get_global_rate_secondary(transactional_curr_code, time_activation_date_id)) rate2
from (select /*+ PARALLEL(tmp) */ distinct transactional_curr_code, time_activation_date_id
from isc_dbi_tmp_bsa_order_lines tmp);
select distinct decode(rate1, -3, to_date('01/01/1999','MM/DD/RRRR'), conversion_date) curr_conv_date,
from_currency,
g_global_currency to_currency,
g_global_rate_type rate_type,
decode(rate1, -1, 'RATE NOT AVAILABLE', -2, 'INVALID CURRENCY') status
FROM isc_curr_bsa_order_lines tmp
WHERE rate1 < 0
UNION
SELECT distinct decode(rate2, -3, to_date('01/01/1999','MM/DD/RRRR'), conversion_date) CURR_CONV_DATE,
from_currency,
g_sec_global_currency to_currency,
g_sec_global_rate_type rate_type,
decode(rate2, -1, 'RATE NOT AVAILABLE', -2, 'INVALID CURRENCY') status
FROM isc_curr_bsa_order_lines tmp
WHERE rate2 < 0
AND g_sec_curr_def = 'Y';
select order_number,
line_number,
order_line_id,
blanket_number,
to_char(time_activation_date_id, 'MM/DD/YYYY') time_activation_date_id,
to_char(time_expiration_date_id, 'MM/DD/YYYY') time_expiration_date_id,
to_char(time_termination_date_id,'MM/DD/YYYY') time_termination_date_id
from isc_dbi_tmp_bsa_order_lines
where (least(time_activation_date_id,
nvl(time_expiration_date_id,time_activation_date_id),
nvl(time_termination_date_id,time_activation_date_id)) < l_time_min
or greatest(time_activation_date_id,
nvl(time_expiration_date_id,time_activation_date_id),
nvl(time_termination_date_id,time_activation_date_id)) > l_time_max);
select /*+ PARALLEL(tmp) */
min(time_activation_date_id), max(time_activation_date_id),
min(time_expiration_date_id), max(time_expiration_date_id),
min(time_termination_date_id), max(time_termination_date_id)
into l_min_act_date, l_max_act_date,
l_min_exp_date, l_max_exp_date,
l_min_trm_date, l_max_trm_date
from isc_dbi_tmp_bsa_order_lines tmp;
select min(report_date), max(report_date)
into l_time_min, l_time_max
from fii_time_day;
function insert_fact return number is
l_bsa_count number;
bis_collection_utilities.put_line('Inserting data into isc_dbi_bsa_order_lines_f');
insert /*+ APPEND PARALLEL(F) */ into isc_dbi_bsa_order_lines_f f (
order_line_id,
order_line_header_id,
order_number,
line_number,
inventory_item_id,
item_inv_org_id,
blanket_line_id,
blanket_header_id,
blanket_number,
blanket_line_number,
org_id,
salesrep_id,
sales_grp_id,
agreement_type_id,
sold_to_org_id,
customer_id,
time_activation_date_id,
time_expiration_date_id,
time_termination_date_id,
time_fulfilled_date_id,
time_effective_end_date_id,
h_start_date_active,
l_start_date_active,
h_end_date_active,
l_end_date_active,
termination_date,
blanket_min_amt,
blanket_line_min_amt,
fulfilled_amt_g,
fulfilled_amt_g1,
h_cnt,
l_cnt,
commit_prorated_amt_g,
commit_prorated_amt_g1,
fulfilled_outstand_amt_g,
fulfilled_outstand_amt_g1,
transaction_phase_code,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
program_id,
program_login_id,
program_application_id,
request_id
)
select /*+ use_hash(curr,tmp,sg,cust_acct) PARALLEL(curr) PARALLEL(tmp) PARALLEL(sg) PARALLEL(cust_acct) */
tmp.order_line_id,
tmp.order_line_header_id,
tmp.order_number,
tmp.line_number,
tmp.inventory_item_id,
tmp.item_inv_org_id,
tmp.blanket_line_id,
tmp.blanket_header_id,
tmp.blanket_number,
tmp.blanket_line_number,
tmp.org_id,
sg.resource_id,
sg.group_id,
tmp.agreement_type_id,
tmp.sold_to_org_id,
cust_acct.party_id,
tmp.time_activation_date_id,
tmp.time_expiration_date_id,
tmp.time_termination_date_id,
tmp.time_fulfilled_date_id,
tmp.time_effective_end_date_id,
tmp.h_start_date_active,
tmp.l_start_date_active,
tmp.h_end_date_active,
tmp.l_end_date_active,
tmp.termination_date,
tmp.blanket_min_amt,
tmp.blanket_line_min_amt,
tmp.fulfilled_amt_g,
tmp.fulfilled_amt_g1,
tmp.h_cnt,
tmp.l_cnt,
nvl(tmp.blanket_min_amt*curr.rate1/tmp.h_cnt, tmp.blanket_line_min_amt*curr.rate1/tmp.l_cnt),
nvl(tmp.blanket_min_amt*curr.rate2/tmp.h_cnt, tmp.blanket_line_min_amt*curr.rate2/tmp.l_cnt),
decode((tmp.accumulated_fulfilled_amt_g - nvl(tmp.blanket_min_amt*curr.rate1,tmp.blanket_line_min_amt*curr.rate1) - tmp.fulfilled_amt_g),
abs(tmp.accumulated_fulfilled_amt_g - nvl(tmp.blanket_min_amt*curr.rate1,tmp.blanket_line_min_amt*curr.rate1) - tmp.fulfilled_amt_g),
0,
decode((nvl(tmp.blanket_min_amt*curr.rate1, tmp.blanket_line_min_amt*curr.rate1) - tmp.accumulated_fulfilled_amt_g),
abs(nvl(tmp.blanket_min_amt*curr.rate1, tmp.blanket_line_min_amt*curr.rate1) - tmp.accumulated_fulfilled_amt_g),
tmp.fulfilled_amt_g,
(nvl(tmp.blanket_min_amt*curr.rate1, tmp.blanket_line_min_amt*curr.rate1) - tmp.accumulated_fulfilled_amt_g + tmp.fulfilled_amt_g))),
decode((tmp.accumulated_fulfilled_amt_g1 - nvl(tmp.blanket_min_amt*curr.rate2,tmp.blanket_line_min_amt*curr.rate2) - tmp.fulfilled_amt_g1),
abs(tmp.accumulated_fulfilled_amt_g1 - nvl(tmp.blanket_min_amt*curr.rate2,tmp.blanket_line_min_amt*curr.rate2) - tmp.fulfilled_amt_g1),
0,
decode((nvl(tmp.blanket_min_amt*curr.rate2, tmp.blanket_line_min_amt*curr.rate2) - tmp.accumulated_fulfilled_amt_g1),
abs(nvl(tmp.blanket_min_amt*curr.rate2, tmp.blanket_line_min_amt*curr.rate2) - tmp.accumulated_fulfilled_amt_g1),
tmp.fulfilled_amt_g1,
(nvl(tmp.blanket_min_amt*curr.rate2, tmp.blanket_line_min_amt*curr.rate2) - tmp.accumulated_fulfilled_amt_g1 + tmp.fulfilled_amt_g1))),
tmp.transaction_phase_code,
tmp.created_by,
tmp.creation_date,
tmp.last_updated_by,
tmp.last_update_date,
tmp.last_update_login,
tmp.program_id,
tmp.program_login_id,
tmp.program_application_id,
tmp.request_id
from isc_dbi_tmp_bsa_order_lines tmp,
isc_curr_bsa_order_lines curr,
jtf_rs_srp_groups sg,
hz_cust_accounts cust_acct
where tmp.transactional_curr_code = curr.from_currency
and tmp.time_activation_date_id = curr.conversion_date
and tmp.salesrep_id = sg.salesrep_id
and tmp.org_id = sg.org_id
and tmp.h_start_date_active between sg.start_date and sg.end_date
and tmp.sold_to_org_id = cust_acct.cust_account_id;
fii_util.print_timer('Inserted '|| l_bsa_count ||' rows into isc_dbi_bsa_order_lines_f in');
g_errbuf := 'Error in Function INSERT_FACT : '||sqlerrm;
end insert_fact;
g_row_count := INSERT_FACT;
insert /*+ APPEND PARALLEL(F) */ into isc_dbi_tmp_bsa_order_lines f (
order_line_id,
order_line_header_id,
order_number,
line_number,
inventory_item_id,
item_inv_org_id,
blanket_line_id,
blanket_header_id,
blanket_number,
blanket_line_number,
org_id,
salesrep_id,
agreement_type_id,
sold_to_org_id,
time_activation_date_id,
time_expiration_date_id,
time_termination_date_id,
time_fulfilled_date_id,
time_effective_end_date_id,
h_start_date_active,
l_start_date_active,
h_end_date_active,
l_end_date_active,
termination_date,
blanket_min_amt,
blanket_line_min_amt,
fulfilled_amt_g,
fulfilled_amt_g1,
accumulated_fulfilled_amt_g,
accumulated_fulfilled_amt_g1,
h_cnt,
l_cnt,
transactional_curr_code,
transaction_phase_code,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
program_id,
program_login_id,
program_application_id,
request_id
)
select /*+ USE_HASH(bh,bhe,bl,ble,r,book) PARALLEL(bh) PARALLEL(bhe) PARALLEL(bl) PARALLEL(ble) PARALLEL(r) PARALLEL(book) */
book.line_id,
book.header_id,
book.order_number,
book.line_number,
book.inventory_item_id,
book.item_inv_org_id,
bl.line_id,
bh.header_id,
bh.order_number,
ble.line_number,
bh.org_id,
bh.salesrep_id,
bh.order_type_id,
bh.sold_to_org_id,
trunc(decode(bhe.blanket_min_amount, null, ble.start_date_active, bhe.start_date_active)),
trunc(decode(bhe.blanket_min_amount, null, ble.end_date_active, bhe.end_date_active)),
trunc(r.creation_date),
book.time_fulfilled_date_id,
least(nvl(trunc(r.creation_date), trunc(decode(bhe.blanket_min_amount, null, ble.end_date_active, bhe.end_date_active))),
nvl(trunc(decode(bhe.blanket_min_amount, null, ble.end_date_active, bhe.end_date_active)), trunc(r.creation_date))),
bhe.start_date_active,
ble.start_date_active,
bhe.end_date_active,
ble.end_date_active,
r.creation_date,
bhe.blanket_min_amount,
ble.blanket_line_min_amount,
book.fulfilled_amt_g,
book.fulfilled_amt_g1,
sum(book.fulfilled_amt_g) over (partition by bh.header_id order by book.time_fulfilled_date_id, book.line_id range unbounded preceding),
sum(book.fulfilled_amt_g1) over (partition by bh.header_id order by book.time_fulfilled_date_id, book.line_id range unbounded preceding),
count(1) over (partition by bh.header_id),
count(1) over (partition by bl.line_id),
bh.transactional_curr_code,
bh.transaction_phase_code,
bh.created_by,
bh.creation_date,
bh.last_updated_by,
bh.last_update_date,
bh.last_update_login,
null,
null,
null,
null
from oe_blanket_headers_all bh,
oe_blanket_headers_ext bhe,
oe_blanket_lines_all bl,
oe_blanket_lines_ext ble,
oe_reasons r,
isc_book_sum2_f book
where bh.order_number = bhe.order_number
and bh.header_id = bl.header_id
and bl.line_id = ble.line_id
and r.entity_code(+) = 'BLANKET_HEADER'
and r.reason_type(+) = 'CONTRACT_TERMINATION'
and r.entity_id(+) = bh.header_id
and book.blanket_number(+) = ble.order_number
and book.blanket_line_number(+) = ble.line_number
and bh.transaction_phase_code = 'F'
and bh.sold_to_org_id is not null
and (bhe.blanket_min_amount is not null or ble.blanket_line_min_amount is not null)
and nvl(r.creation_date,bhe.start_date_active+1) >= bhe.start_date_active
and book.line_category_code(+) <> 'RETURN'
and book.order_source_id(+) <> 10
and book.order_source_id(+) <> 27
and book.ordered_quantity(+) <> 0
and book.unit_selling_price(+) <> 0
and book.charge_periodicity_code(+) is null;
insert /*+ APPEND */ into isc_curr_bsa_order_lines f (
from_currency,
conversion_date,
rate1,
rate2
)
select transactional_curr_code from_currency,
time_activation_date_id conversion_date,
decode(transactional_curr_code, g_global_currency, 1,
fii_currency.get_global_rate_primary(transactional_curr_code, time_activation_date_id)) rate1,
decode(transactional_curr_code, g_sec_global_currency, 1,
fii_currency.get_global_rate_secondary(transactional_curr_code, time_activation_date_id)) rate2
from (select /*+ PARALLEL(tmp) */ distinct transactional_curr_code, time_activation_date_id
from isc_dbi_tmp_bsa_order_lines tmp);
update isc_dbi_tmp_bsa_order_lines set batch_id = ceil(rownum/g_batch_size);
fii_util.print_timer('Updated the batch id for '|| l_total || ' rows in');
select order_number,
line_number,
order_line_id,
blanket_number,
to_char(time_activation_date_id, 'MM/DD/YYYY') time_activation_date_id,
to_char(time_expiration_date_id, 'MM/DD/YYYY') time_expiration_date_id,
to_char(time_termination_date_id,'MM/DD/YYYY') time_termination_date_id
from isc_dbi_tmp_bsa_order_lines
where (least(time_activation_date_id,
nvl(time_expiration_date_id,time_activation_date_id),
nvl(time_termination_date_id,time_activation_date_id)) < l_time_min
or greatest(time_activation_date_id,
nvl(time_expiration_date_id,time_activation_date_id),
nvl(time_termination_date_id,time_activation_date_id)) > l_time_max);
select /*+ PARALLEL(tmp) */
min(time_activation_date_id), max(time_activation_date_id),
min(time_expiration_date_id), max(time_expiration_date_id),
min(time_termination_date_id), max(time_termination_date_id)
into l_min_act_date, l_max_act_date,
l_min_exp_date, l_max_exp_date,
l_min_trm_date, l_max_trm_date
from isc_dbi_tmp_bsa_order_lines tmp;
select min(report_date), max(report_date)
into l_time_min, l_time_max
from fii_time_day;
select blanket_line_id,
order_line_id
from (select f.blanket_line_id,
t.order_line_id,
rank() over (partition by f.blanket_line_id order by t.order_line_id) rnk
from isc_dbi_bsa_order_lines_f f,
isc_dbi_tmp_bsa_order_lines t
where f.blanket_line_id = t.blanket_line_id
and f.order_line_id is null
and t.order_line_id is not null)
where rnk = 1;
update isc_dbi_bsa_order_lines_f
set order_line_id = l_record.order_line_id
where blanket_line_id = l_record.blanket_line_id;
(select new.*
from (select tmp.batch_id,
tmp.order_line_id,
tmp.order_line_header_id,
tmp.order_number,
tmp.line_number,
tmp.inventory_item_id,
tmp.item_inv_org_id,
tmp.blanket_line_id,
tmp.blanket_header_id,
tmp.blanket_number,
tmp.blanket_line_number,
tmp.org_id,
sg.resource_id salesrep_id,
sg.group_id sales_grp_id,
tmp.agreement_type_id,
tmp.sold_to_org_id,
cust_acct.party_id customer_id,
tmp.time_activation_date_id,
tmp.time_expiration_date_id,
tmp.time_termination_date_id,
tmp.time_fulfilled_date_id,
tmp.time_effective_end_date_id,
tmp.h_start_date_active,
tmp.l_start_date_active,
tmp.h_end_date_active,
tmp.l_end_date_active,
tmp.termination_date,
tmp.blanket_min_amt,
tmp.blanket_line_min_amt,
tmp.fulfilled_amt_g,
tmp.fulfilled_amt_g1,
tmp.h_cnt,
tmp.l_cnt,
nvl(tmp.blanket_min_amt*curr.rate1/tmp.h_cnt, tmp.blanket_line_min_amt*curr.rate1/tmp.l_cnt) commit_prorated_amt_g,
nvl(tmp.blanket_min_amt*curr.rate2/tmp.h_cnt, tmp.blanket_line_min_amt*curr.rate2/tmp.l_cnt) commit_prorated_amt_g1,
decode((tmp.accumulated_fulfilled_amt_g - nvl(tmp.blanket_min_amt*curr.rate1,tmp.blanket_line_min_amt*curr.rate1) - tmp.fulfilled_amt_g),
abs(tmp.accumulated_fulfilled_amt_g - nvl(tmp.blanket_min_amt*curr.rate1,tmp.blanket_line_min_amt*curr.rate1) - tmp.fulfilled_amt_g),
0,
decode((nvl(tmp.blanket_min_amt*curr.rate1, tmp.blanket_line_min_amt*curr.rate1) - tmp.accumulated_fulfilled_amt_g),
abs(nvl(tmp.blanket_min_amt*curr.rate1, tmp.blanket_line_min_amt*curr.rate1) - tmp.accumulated_fulfilled_amt_g),
tmp.fulfilled_amt_g,
(nvl(tmp.blanket_min_amt*curr.rate1, tmp.blanket_line_min_amt*curr.rate1) - tmp.accumulated_fulfilled_amt_g + tmp.fulfilled_amt_g)))
fulfilled_outstand_amt_g,
decode((tmp.accumulated_fulfilled_amt_g1 - nvl(tmp.blanket_min_amt*curr.rate2,tmp.blanket_line_min_amt*curr.rate2) - tmp.fulfilled_amt_g1),
abs(tmp.accumulated_fulfilled_amt_g1 - nvl(tmp.blanket_min_amt*curr.rate2,tmp.blanket_line_min_amt*curr.rate2) - tmp.fulfilled_amt_g1),
0,
decode((nvl(tmp.blanket_min_amt*curr.rate2, tmp.blanket_line_min_amt*curr.rate2) - tmp.accumulated_fulfilled_amt_g1),
abs(nvl(tmp.blanket_min_amt*curr.rate2, tmp.blanket_line_min_amt*curr.rate2) - tmp.accumulated_fulfilled_amt_g1),
tmp.fulfilled_amt_g1,
(nvl(tmp.blanket_min_amt*curr.rate2, tmp.blanket_line_min_amt*curr.rate2) - tmp.accumulated_fulfilled_amt_g1 + tmp.fulfilled_amt_g1)))
fulfilled_outstand_amt_g1,
tmp.transaction_phase_code,
tmp.created_by,
tmp.creation_date,
tmp.last_updated_by,
tmp.last_update_date,
tmp.last_update_login,
tmp.program_id,
tmp.program_login_id,
tmp.program_application_id,
tmp.request_id
from isc_dbi_tmp_bsa_order_lines tmp,
isc_curr_bsa_order_lines curr,
jtf_rs_srp_groups sg,
hz_cust_accounts cust_acct
where tmp.transactional_curr_code = curr.from_currency
and tmp.time_activation_date_id = curr.conversion_date
and tmp.salesrep_id = sg.salesrep_id
and tmp.org_id = sg.org_id
and tmp.h_start_date_active between sg.start_date and sg.end_date
and tmp.sold_to_org_id = cust_acct.cust_account_id) new, isc_dbi_bsa_order_lines_f old
where new.batch_id = v_batch_id
and new.blanket_line_id = old.blanket_line_id(+)
and nvl(new.order_line_id,-1) = nvl(old.order_line_id(+),-1)
and (old.blanket_line_id is null
or nvl(new.order_line_header_id,-1) <> nvl(old.order_line_header_id,-1)
or nvl(new.order_number,-1) <> nvl(old.order_number,-1)
or nvl(new.line_number,'na') <> nvl(old.line_number,'na')
or nvl(new.inventory_item_id,-1) <> nvl(old.inventory_item_id,-1)
or nvl(new.item_inv_org_id,-1) <> nvl(old.item_inv_org_id,-1)
or nvl(new.org_id,-1) <> nvl(old.org_id,-1)
or nvl(new.salesrep_id,-1) <> nvl(old.salesrep_id,-1)
or nvl(new.sales_grp_id,-1) <> nvl(old.sales_grp_id,-1)
or nvl(new.agreement_type_id,-1) <> nvl(old.agreement_type_id,-1)
or nvl(new.sold_to_org_id,-1) <> nvl(old.sold_to_org_id,-1)
or nvl(new.customer_id,-1) <> nvl(old.customer_id,-1)
or nvl(new.time_activation_date_id,l_date) <> nvl(old.time_activation_date_id,l_date)
or nvl(new.time_expiration_date_id,l_date) <> nvl(old.time_expiration_date_id,l_date)
or nvl(new.time_termination_date_id,l_date) <> nvl(old.time_termination_date_id,l_date)
or nvl(new.time_fulfilled_date_id,l_date) <> nvl(old.time_fulfilled_date_id,l_date)
or nvl(new.time_effective_end_date_id,l_date) <> nvl(old.time_effective_end_date_id,l_date)
or nvl(new.h_start_date_active,l_date) <> nvl(old.h_start_date_active,l_date)
or nvl(new.l_start_date_active,l_date) <> nvl(old.l_start_date_active,l_date)
or nvl(new.h_end_date_active,l_date) <> nvl(old.h_end_date_active,l_date)
or nvl(new.l_end_date_active,l_date) <> nvl(old.l_end_date_active,l_date)
or nvl(new.termination_date,l_date) <> nvl(old.termination_date,l_date)
or nvl(new.blanket_min_amt,0) <> nvl(old.blanket_min_amt,0)
or nvl(new.blanket_line_min_amt,0) <> nvl(old.blanket_line_min_amt,0)
or nvl(new.fulfilled_amt_g,0) <> nvl(old.fulfilled_amt_g,0)
or nvl(new.fulfilled_amt_g1,0) <> nvl(old.fulfilled_amt_g1,0)
or nvl(new.h_cnt,0) <> nvl(old.h_cnt,0)
or nvl(new.l_cnt,0) <> nvl(old.l_cnt,0)
or nvl(new.commit_prorated_amt_g,0) <> nvl(old.commit_prorated_amt_g,0)
or nvl(new.commit_prorated_amt_g1,0) <> nvl(old.commit_prorated_amt_g1,0)
or nvl(new.fulfilled_outstand_amt_g,0) <> nvl(old.fulfilled_outstand_amt_g,0)
or nvl(new.fulfilled_outstand_amt_g1,0) <> nvl(old.fulfilled_outstand_amt_g1,0)
or nvl(new.transaction_phase_code,'na') <> nvl(old.transaction_phase_code,'na'))) v
ON ( nvl(f.order_line_id,-1) = nvl(v.order_line_id,-1)
and f.blanket_line_id = v.blanket_line_id)
WHEN MATCHED THEN UPDATE SET
f.order_line_header_id = v.order_line_header_id,
f.order_number = v.order_number,
f.line_number = v.line_number,
f.inventory_item_id = v.inventory_item_id,
f.item_inv_org_id = v.item_inv_org_id,
f.org_id = v.org_id,
f.salesrep_id = v.salesrep_id,
f.sales_grp_id = v.sales_grp_id,
f.agreement_type_id = v.agreement_type_id,
f.sold_to_org_id = v.sold_to_org_id,
f.customer_id = v.customer_id,
f.time_activation_date_id = v.time_activation_date_id,
f.time_expiration_date_id = v.time_expiration_date_id,
f.time_termination_date_id = v.time_termination_date_id,
f.time_fulfilled_date_id = v.time_fulfilled_date_id,
f.time_effective_end_date_id = v.time_effective_end_date_id,
f.h_start_date_active = v.h_start_date_active,
f.l_start_date_active = v.l_start_date_active,
f.h_end_date_active = v.h_end_date_active,
f.l_end_date_active = v.l_end_date_active,
f.termination_date = v.termination_date,
f.blanket_min_amt = v.blanket_min_amt,
f.blanket_line_min_amt = v.blanket_line_min_amt,
f.fulfilled_amt_g = v.fulfilled_amt_g,
f.fulfilled_amt_g1 = v.fulfilled_amt_g1,
f.h_cnt = v.h_cnt,
f.l_cnt = v.l_cnt,
f.commit_prorated_amt_g = v.commit_prorated_amt_g,
f.commit_prorated_amt_g1 = v.commit_prorated_amt_g1,
f.fulfilled_outstand_amt_g = v.fulfilled_outstand_amt_g,
f.fulfilled_outstand_amt_g1 = v.fulfilled_outstand_amt_g1,
f.transaction_phase_code = v.transaction_phase_code
WHEN NOT MATCHED THEN INSERT(
f.order_line_id,
f.order_line_header_id,
f.order_number,
f.line_number,
f.inventory_item_id,
f.item_inv_org_id,
f.blanket_line_id,
f.blanket_header_id,
f.blanket_number,
f.blanket_line_number,
f.org_id,
f.salesrep_id,
f.sales_grp_id,
f.agreement_type_id,
f.sold_to_org_id,
f.customer_id,
f.time_activation_date_id,
f.time_expiration_date_id,
f.time_termination_date_id,
f.time_fulfilled_date_id,
f.time_effective_end_date_id,
f.h_start_date_active,
f.l_start_date_active,
f.h_end_date_active,
f.l_end_date_active,
f.termination_date,
f.blanket_min_amt,
f.blanket_line_min_amt,
f.fulfilled_amt_g,
f.fulfilled_amt_g1,
f.h_cnt,
f.l_cnt,
f.commit_prorated_amt_g,
f.commit_prorated_amt_g1,
f.fulfilled_outstand_amt_g,
f.fulfilled_outstand_amt_g1,
f.transaction_phase_code,
f.created_by,
f.creation_date,
f.last_updated_by,
f.last_update_date,
f.last_update_login,
f.program_id,
f.program_login_id,
f.program_application_id,
f.request_id
)
VALUES (
v.order_line_id,
v.order_line_header_id,
v.order_number,
v.line_number,
v.inventory_item_id,
v.item_inv_org_id,
v.blanket_line_id,
v.blanket_header_id,
v.blanket_number,
v.blanket_line_number,
v.org_id,
v.salesrep_id,
v.sales_grp_id,
v.agreement_type_id,
v.sold_to_org_id,
v.customer_id,
v.time_activation_date_id,
v.time_expiration_date_id,
v.time_termination_date_id,
v.time_fulfilled_date_id,
v.time_effective_end_date_id,
v.h_start_date_active,
v.l_start_date_active,
v.h_end_date_active,
v.l_end_date_active,
v.termination_date,
v.blanket_min_amt,
v.blanket_line_min_amt,
v.fulfilled_amt_g,
v.fulfilled_amt_g1,
v.h_cnt,
v.l_cnt,
v.commit_prorated_amt_g,
v.commit_prorated_amt_g1,
v.fulfilled_outstand_amt_g,
v.fulfilled_outstand_amt_g1,
v.transaction_phase_code,
-1,
g_incre_start_date,
-1,
g_incre_start_date,
-1,
-1,
-1,
-1,
-1);
procedure update_fact(errbuf in out nocopy varchar2,
retcode in out nocopy varchar2) is
l_failure exception;
bis_collection_utilities.put_line('Last updated date is '|| to_char(g_incre_start_date,'MM/DD/YYYY HH24:MI:SS'));
end update_fact;