[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT tablespace_name,initial_extent, next_extent
FROM all_indexes
WHERE table_name = b_tab_name
AND table_owner = b_own_name
AND index_name = b_ind_name
;
SELECT index_name
FROM all_indexes
WHERE table_name = b_tab_name
AND table_owner = 'OKI'
;
SELECT MAX(jrn.job_run_id) job_run_id
FROM oki_job_runs jrn
;
SELECT tspace.tablespace_name,
tspace.initial_extent,
tspace.next_extent
FROM dba_users usr, dba_tablespaces tspace
WHERE usr.username = 'OKI'
AND usr.default_tablespace = tspace.tablespace_name;
FOR r_rec IN (SELECT dnz_chr_id, count(1) chr_count
FROM okc_k_party_roles_b
WHERE rle_code IN ('CUSTOMER','LICENSEE','BUYER')
AND cle_id is null
GROUP BY dnz_chr_id
HAVING COUNT(1) > 1)
LOOP
SELECT contract_number ||' '||contract_number_modifier
INTO l_contract_number
FROM okc_k_headers_b
WHERE id = r_rec.dnz_chr_id;
PROCEDURE update_oki_refresh ( p_object_name IN VARCHAR2
, x_retcode OUT NOCOPY VARCHAR2
, p_job_run_id IN NUMBER DEFAULT NULL ) IS
l_sqlcode VARCHAR2(100);
UPDATE OKI_REFRESHS
SET REQUEST_ID = g_request_id ,
PROGRAM_APPLICATION_ID = g_program_application_id,
PROGRAM_ID = g_program_id,
PROGRAM_UPDATE_DATE = g_program_update_date,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
JOB_RUN_ID = p_job_run_id
WHERE OBJECT_NAME = p_object_name;
END update_oki_refresh;
PROCEDURE update_refresh_job_run(
p_object_name IN VARCHAR2
, p_job_run_id IN NUMBER
, x_retcode OUT NOCOPY VARCHAR2 ) IS
-- Location within the program before the error was encountered.
l_loc VARCHAR2(200) ;
UPDATE OKI_REFRESHS
SET REQUEST_ID = g_request_id ,
PROGRAM_APPLICATION_ID = g_program_application_id,
PROGRAM_ID = g_program_id,
-- PROGRAM_UPDATE_DATE = g_program_update_date,
JOB_RUN_ID = p_job_run_id,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE OBJECT_NAME = p_object_name;
, value => 'oki_refresh_pvt.update_refresh_job_run ' ) ;
END update_refresh_job_run ;
SELECT oki_job_runs_s1.nextval seq
FROM dual
;
l_loc := 'Inserting into ' || l_table_name ;
INSERT INTO oki_job_runs (
job_run_id
, job_start_date
, job_end_date
, job_curr_start_date
, job_curr_end_date
, creation_date
, created_by
, last_update_date
, last_updated_by
, security_group_id
, request_id
) VALUES (
l_sequence
, p_job_start_date
, p_job_end_date
, least(p_job_curr_start_date,l_sysdate)
, least(p_job_curr_end_date,l_sysdate)
, l_sysdate
, FND_GLOBAL.USER_ID
, l_sysdate
, FND_GLOBAL.USER_ID
, NULL
, g_request_id );
SELECT count(*) jbrn_count
FROM oki_job_runs jrn ;
update_oki_refresh( p_object_name => l_table_name
, p_job_run_id => l_job_run_id
, x_retcode => l_retcode ) ;
update_oki_refresh( p_object_name => l_table_name
, p_job_run_id => l_job_run_id
, x_retcode => l_retcode ) ;
update_oki_refresh( p_object_name => l_table_name
, p_job_run_id => l_job_run_id
, x_retcode => l_retcode ) ;
update_oki_refresh( p_object_name => l_table_name
, p_job_run_id => l_job_run_id
, x_retcode => l_retcode ) ;
update_oki_refresh( p_object_name => l_table_name
, p_job_run_id => l_job_run_id
, x_retcode => l_retcode ) ;
update_oki_refresh( p_object_name => l_table_name
, p_job_run_id => l_job_run_id
, x_retcode => l_retcode ) ;
SELECT
job_curr_start_date
, job_curr_end_date
FROM oki_job_runs jrn
WHERE jrn.job_run_id = p_job_run_id ;
l_loc := 'Inserting into oki_job_run_dtl.' ;
INSERT INTO oki_job_run_dtl (
job_run_id
, chr_id
, action_flag
, sob_id
, period_set_name
, accounted_period_type
, func_currency
, trx_func_rate
, trx_base_rate
, conversion_date
, creation_date
, created_by
, last_update_date
, last_updated_by
, security_group_id
, request_id
, major_version
, minor_version
) (SELECT
p_job_run_id
, shd.chr_id
, 'D'
, NULL --sob_id
, NULL --period_set_name
, NULL --accounted_period_type
, NULL --func_currency
, NULL --trx_func_rate
, NULL --trx_base_rate
, NULL --conversion_date
, l_sysdate
, FND_GLOBAL.USER_ID
, l_sysdate
, FND_GLOBAL.USER_ID
, NULL
, g_request_id
, NULL --Major Version
, NULL --Minor Version
FROM oki_sales_k_hdrs shd
MINUS
SELECT
p_job_run_id
, khr.id
, 'D'
, NULL --sob_id
, NULL --period_set_name
, NULL --accounted_period_type
, NULL --func_currency
, NULL --trx_func_rate
, NULL --trx_base_rate
, NULL --conversion_date
, l_sysdate
, FND_GLOBAL.USER_ID
, l_sysdate
, FND_GLOBAL.USER_ID
, NULL
, g_request_id
, NULL --Major Version
, NULL --Minor Version
FROM okc_k_headers_b khr)
UNION
SELECT
p_job_run_id
, vnm.chr_id
, 'I'
, SOB.set_of_books_id
, SOB.period_set_name
, SOB.accounted_period_type
, SOB.currency_code
, decode (sts.ste_code,'ACTIVE',
get_conversion_rate( trunc(khr.start_date),khr.currency_code,sob.currency_code)
, 'HOLD',
get_conversion_rate( trunc(khr.start_date),khr.currency_code,sob.currency_code)
,'TERMINATED',
get_conversion_rate( trunc(khr.start_date),khr.currency_code,sob.currency_code)
, 'EXPIRED',
get_conversion_rate( trunc(khr.start_date),khr.currency_code,sob.currency_code)
, get_conversion_rate(l_sysdate,khr.currency_code,sob.currency_code)
) trx_func_rate
, decode (sts.ste_code,'ACTIVE',
get_conversion_rate( trunc(khr.start_date),khr.currency_code,l_base_currency)
, 'HOLD',
get_conversion_rate( trunc(khr.start_date),khr.currency_code,l_base_currency)
,'TERMINATED',
get_conversion_rate( trunc(khr.start_date),khr.currency_code,l_base_currency)
, 'EXPIRED',
get_conversion_rate( trunc(khr.start_date),khr.currency_code,l_base_currency)
, get_conversion_rate(l_sysdate,khr.currency_code,l_base_currency)
) trx_base_rate
, decode (sts.ste_code,'ACTIVE',
trunc(khr.start_date)
, 'HOLD',
trunc(khr.start_date)
,'TERMINATED',
trunc(khr.start_date)
, 'EXPIRED',
trunc(khr.start_date)
, trunc(sysdate)
) conversion_date
, l_sysdate
, FND_GLOBAL.USER_ID
, l_sysdate
, FND_GLOBAL.USER_ID
, NULL
, g_request_id
, vnm.major_version
, vnm.minor_version
FROM okc_k_headers_b khr
,okc_k_vers_numbers vnm
,hr_organization_information oin
,gl_sets_of_books sob
,okc_statuses_b sts
WHERE 1 = 1
AND khr.buy_or_sell = 'S'
AND khr.template_yn = 'N'
AND khr.application_id = 515
AND khr.scs_code IN ('SERVICE','WARRANTY') -- 11510 Change
AND vnm.chr_id = khr.id
AND oin.organization_id = khr.authoring_org_id
AND oin.org_information_context = 'Operating Unit Information'
AND sob.set_of_books_id = TO_NUMBER(oin.org_information3)
AND vnm.last_update_date BETWEEN p_job_curr_start_date
AND p_job_curr_end_date
AND khr.sts_code = sts.code;
l_no_update_refresh exception;
SELECT khr.id chr_id,
decode (sts.ste_code,'ACTIVE',
trunc(khr.start_date)
, 'HOLD',
trunc(khr.start_date)
,'TERMINATED',
trunc(khr.start_date)
, 'EXPIRED',
trunc(khr.start_date)
, l_sysdate
) conversion_date,
khr.currency_code currency_code,
fnd_profile.value('OKI_BASE_CURRENCY') base_currency_code,
sob.currency_code sob_currency_code,
sob.accounted_period_type,
sob.set_of_books_id,
sob.period_set_name,
vnm.major_version,
vnm.minor_version
FROM
okc_k_headers_b khr,
okc_k_vers_numbers vnm,
okc_statuses_b sts,
gl_sets_of_books sob,
hr_organization_information oin
WHERE 1 = 1
AND khr.buy_or_sell = 'S'
AND khr.template_yn = 'N'
AND khr.application_id = 515
AND khr.scs_code IN ('SERVICE','WARRANTY') -- 11510 Change
AND khr.sts_code = sts.code
AND khr.authoring_org_id = oin.organization_id
AND vnm.chr_id = khr.id
AND oin.org_information_context = 'Operating Unit Information'
AND sob.set_of_books_id = oin.org_information3;
Select conversion_rate from gl_daily_rates
Where from_currency = l_from_curr
And to_currency = l_to_curr
And conversion_date = (SELECT MAX(conversion_date)
FROM gl_daily_rates
WHERE from_currency = l_from_curr
AND to_currency = l_to_curr
AND conversion_date <= l_curr_date
AND conversion_type = l_conv_type)
And conversion_type = l_conv_type;
l_max_select INTEGER := 20000;
SELECT count(*) INTO l_remainder
FROM
okc_k_headers_b khr,
okc_statuses_b sts,
gl_sets_of_books sob,
hr_organization_information oin
WHERE 1 = 1
AND khr.buy_or_sell = 'S'
AND khr.template_yn = 'N'
AND khr.application_id = 515
AND khr.scs_code IN ('SERVICE','WARRANTY') -- 11510 Change
AND khr.sts_code = sts.code
AND khr.authoring_org_id = oin.organization_id
AND oin.org_information_context = 'Operating Unit Information'
AND sob.set_of_books_id = oin.org_information3;
IF l_remainder > l_max_select THEN
l_limit := l_max_select;
l_remainder := l_remainder - l_max_select;
INSERT INTO oki_job_run_dtl (
job_run_id
,chr_id
,action_flag
,sob_id
,period_set_name
,accounted_period_type
,func_currency
,trx_func_rate
,trx_base_rate
,conversion_date
,creation_date
,created_by
,last_update_date
,last_updated_by
,security_group_id
,request_id
,major_version
,minor_version
)
values
( p_job_run_id
,chr_id_tab(j)
,'I'
,sob_id_tab(j)
,period_set_name_tab(j)
,sob_acct_period_tab(j)
,func_currency_tab(j)
,l_trx_func_rate_tab(j)
,l_trx_base_rate_tab(j)
, conversion_date_tab(j)
, l_sysdate
, FND_GLOBAL.USER_ID
, l_sysdate
, FND_GLOBAL.USER_ID
, NULL
, g_request_id
,major_version_tab(j)
,minor_version_tab(j));
IF l_remainder > l_max_select THEN
l_limit := l_max_select;
l_remainder := l_remainder - l_max_select;
update_oki_refresh(p_object_name => 'OKI_K_CONV_RATE'
,x_retcode => x_retcode);
RAISE l_no_update_refresh;
when l_no_update_refresh then
fnd_message.set_name(application => 'OKI'
,name => 'OKI_TABLE_LOAD_FAILURE');
,buff => 'Update of OKI_REFRESHS failed');
SELECT oki_job_runs_s1.nextval seq
FROM dual
;
SELECT least(jrn.job_curr_end_date + (1/(24 * 60 * 60)), l_sysdate)
INTO l_job_start_date
FROM oki_job_runs jrn
WHERE jrn.job_run_id = (
SELECT MAX(jrn1.job_run_id)
FROM oki_job_runs jrn1
WHERE jrn1.job_run_id < l_sequence ) ;
l_loc := 'Inserting into oki_job_runs' ;
INSERT INTO oki_job_runs (
job_run_id
, job_start_date
, job_end_date
, job_curr_start_date
, job_curr_end_date
, creation_date
, created_by
, last_update_date
, last_updated_by
, security_group_id
, request_id
) SELECT
l_sequence
, l_sysdate
, NULL
-- Add 1 second to the start time so the time
-- does not overlap with the previous job run
, l_job_start_date
, l_job_end_date
, l_sysdate
, FND_GLOBAL.USER_ID
, l_sysdate
, FND_GLOBAL.USER_ID
, NULL
, g_request_id
FROM oki_job_runs jrn1
WHERE jrn1.job_run_id = (
SELECT MAX(jrn3.job_run_id)
FROM oki_job_runs jrn3
WHERE jrn3.job_run_id < l_sequence ) ;
SELECT oki_job_runs_s1.nextval seq
FROM dual
;
l_loc := 'Inserting into oki_job_runs' ;
INSERT INTO oki_job_runs (
job_run_id
, job_start_date
, job_end_date
, job_curr_start_date
, job_curr_end_date
, creation_date
, created_by
, last_update_date
, last_updated_by
, security_group_id
, request_id
) values
( l_sequence
, l_sysdate
, NULL
, l_job_start_date
, l_sysdate
, l_sysdate
, FND_GLOBAL.USER_ID
, l_sysdate
, FND_GLOBAL.USER_ID
, NULL
, g_request_id);
SELECT conversion_rate FROM gl_daily_rates
WHERE from_currency = p_from_currency
AND to_currency = p_to_currency
AND conversion_date = (SELECT MAX (conversion_date)
FROM gl_daily_rates
WHERE from_currency = p_from_currency
AND to_currency = p_to_currency
AND conversion_date <= p_curr_date
AND conversion_type = l_conv_type)
AND conversion_type = l_conv_type;
SELECT jrn1.job_run_id, jrn1.job_end_date
FROM oki_job_runs jrn1
WHERE jrn1.job_run_id = ( SELECT MAX(jrn2.job_run_id)
FROM oki_job_runs jrn2)
;
UPDATE oki_job_runs jrn1
SET job_end_date = sysdate
, last_update_date = sysdate
WHERE job_run_id = l_job_run_id ;
SELECT max(jrn.job_run_id) job_run_id
FROM oki_job_runs jrn
;
SELECT rfh.job_run_id job_run_id
FROM oki_refreshs rfh
WHERE rfh.object_name = p_object_name
;
l_no_update_refresh EXCEPTION;
SELECT a.from_currency,
a.to_currency,
a.conversion_rate,
a.conversion_date
FROM gl_daily_rates a
WHERE (a.from_currency, a.to_currency, a.conversion_date) IN
( SELECT b.from_currency, b.to_currency, MAX(b.conversion_date) conversion_date
FROM gl_daily_rates b
WHERE b.conversion_type = l_conversion_type
GROUP BY b.from_currency, b.to_currency)
AND a.conversion_type = l_conversion_type;
/* Update oki_daily_rates table with the latest conversion rates
If no record is found in OKI table insert a new record */
UPDATE oki_daily_rates
SET conversion_rate = r_conversion.conversion_rate
, conversion_date = r_conversion.conversion_date
WHERE from_currency = r_conversion.from_currency
AND to_currency = r_conversion.to_currency ;
INSERT INTO oki_daily_rates
(from_currency
, to_currency
, conversion_rate
, conversion_date
)
VALUES
(r_conversion.from_currency
, r_conversion.to_currency
, r_conversion.conversion_rate
, r_conversion.conversion_date
) ;
/* Check and insert a record for the conversion rate between the same currency with a
conversion rate of 1 */
BEGIN
SELECT 'x'
INTO v_dummy
FROM oki_daily_rates
WHERE from_currency = r_conversion.from_currency
AND to_currency = r_conversion.from_currency ;
INSERT INTO oki_daily_rates
(from_currency
, to_currency
, conversion_rate
, conversion_date
)
VALUES
(r_conversion.from_currency
, r_conversion.from_currency
, 1
, TRUNC(sysdate)
) ;
update_oki_refresh(p_object_name => 'OKI_DAILY_RATES'
,x_retcode => retcode);
RAISE l_no_update_refresh;
when l_no_update_refresh then
fnd_message.set_name(application => 'OKI'
,name => 'OKI_TABLE_LOAD_FAILURE');
,buff => 'Update of OKI_REFRESHS failed');
l_no_update_refresh EXCEPTION;
select max(job_run_id)
into l_job_run_id
from oki_job_runs;
INSERT /*+ append */ INTO oki_sales_k_hdrs
(
chr_id,
contract_number,
contract_number_modifier,
complete_contract_number,
order_number,
authoring_org_id,
organization_name,
scs_code,
sts_code,
ste_code,
customer_party_id,
customer_name,
customer_number,
contract_amount,
currency_code,
contract_amount_renewed,
currency_code_renewed,
win_percent,
forecast_amount,
sob_id,
sob_contract_amount,
sob_forecast_amount,
sob_contract_amount_renewed,
sob_currency_code,
base_contract_amount,
base_forecast_amount,
base_contract_amount_renewed,
base_currency_code,
close_date,
start_date,
end_date,
duration,
period,
date_approved,
date_signed,
date_renewed,
date_canceled,
date_terminated,
start_period_num,
start_period_name,
start_quarter,
start_year,
close_period_num,
close_period_name,
close_quarter,
close_year,
trn_code,
inventory_organization_id,
is_new_yn,
is_latest_yn,
orig_system_source_code,
orig_system_id1,
orig_system_reference1,
contract_type,
application_id,
creation_date,
last_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
major_version,
minor_version,
/* 11510 Changes Start
Added the new columns from oki_pricing_rules and oki_qto
Also, terminated amount is now calculated during insertion */
agreement_id,
acct_rule_id,
payment_term_id,
inv_rule_id,
list_header_id,
grace_duration,
grace_period_code,
quote_to_contact_id,
quote_to_site_id,
quote_to_email_id,
quote_to_phone_id,
quote_to_fax_id,
terminated_amount,
sob_terminated_amount,
base_terminated_amount
/* 11510 Changes End */
)
SELECT /*+ leading(khr) use_hash(khr, hoks,jrd,cpl, pty, sts,vnm , spd,spd1, ro, oh.oh, r.ol,og,terminated.okscle) */
khr.id
,khr.contract_number
,khr.contract_number_modifier
,khr.contract_number ||
decode(khr.contract_number_modifier
, null, null, '-'|| khr.contract_number_modifier )
,oh.order_number
,khr.authoring_org_id
,NULL organization_name -- 11510 Changes
,khr.scs_code
,khr.sts_code
,sts.ste_code
,to_number(cpl.object1_id1)
,NULL customer_name -- 11510 Changes
,NULL customer_number -- 11510 Changes
,khr.estimated_amount
,khr.currency_code
,null
,khr.currency_code_renewed
,hoks.est_rev_percent win_percent -- 11510 Changes
,((khr.estimated_amount * hoks.est_rev_percent ) / 100 ) -- 11510 Changes
,jrd.sob_id -- 11510 Changes
,(khr.estimated_amount * jrd.trx_func_rate) -- 11510 Changes
,(((khr.estimated_amount * jrd.trx_func_rate) * hoks.est_rev_percent ) / 100 ) -- 11510 Changes
,null
,jrd.func_currency -- 11510 Changes
,(khr.estimated_amount * jrd.trx_base_rate) -- 11510 Changes
, (((khr.estimated_amount * jrd.trx_base_rate) * hoks.est_rev_percent) / 100 ) -- 11510 Changes
,null
,l_base_currency
,hoks.est_rev_date close_date -- 11510 Changes
,trunc(khr.start_date)
,trunc(khr.end_date)
,oki_disco_util_pub.get_duration(khr.start_date,khr.end_date)
,oki_disco_util_pub.get_period(khr.start_date,khr.end_date)
,trunc(khr.date_approved)
,trunc(khr.date_signed)
,trunc(khr.date_renewed)
,trunc(khr.datetime_cancelled)
,trunc(khr.date_terminated)
,spd.period_num
,spd.period_name
,spd.quarter_num
,spd.period_year
/* 11510 Changes Start */
,spd1.period_num
,spd1.period_name
,spd1.quarter_num
,spd1.period_year
/* 11510 Changes End */
,khr.trn_code
,khr.inv_organization_id
-- ,DECODE(r.is_new_yn,'Y','Y',NULL)
,DECODE( nvl(r.is_new_yn,'Y'),'Y','Y',NULL)
, decode(khr.datetime_cancelled,null,null,'N')
,khr.orig_system_source_code
,khr.orig_system_id1
,khr.orig_system_reference1
,decode( nvl(r.is_new_yn,'Y'),'Y','NEW','REN')
,khr.application_id
,khr.creation_date
,khr.last_update_date -- 11510 Changes
,khr.attribute_category
,khr.attribute1
,khr.attribute2
,khr.attribute3
,khr.attribute4
,khr.attribute5
,khr.attribute6
,khr.attribute7
,khr.attribute8
,khr.attribute9
,khr.attribute10
,khr.attribute11
,khr.attribute12
,khr.attribute13
,khr.attribute14
,khr.attribute15
/* 11510 Changes Start */
,jrd.major_version major_version
,jrd.minor_version minor_version
,og.ISA_AGREEMENT_ID agreement_id -- From oki_pricing_rules
,hoks.acct_rule_id
,khr.payment_term_id -- From oki_pricing_rules
,khr.inv_rule_id
,khr.price_list_id list_header_id -- From oki_pricing_rules
,hoks.grace_duration -- From oki_pricing_rules
,hoks.grace_period -- From oki_pricing_rules
,hoks.quote_to_contact_id quote_to_contact_id -- From oki_qto
,hoks.quote_to_site_id quote_to_site_id -- From oki_qto
,hoks.quote_to_email_id quote_to_email_id -- From oki_qto
,hoks.quote_to_phone_id quote_to_phone_id -- From oki_qto
,hoks.quote_to_fax_id quote_to_fax_id -- From oki_qto
,terminated.terminated_amount
,terminated.terminated_amount * jrd.trx_func_rate
,terminated.terminated_amount * jrd.trx_base_rate
/* 11510 Changes End */
FROM
okc_k_headers_b khr
, oks_k_headers_b hoks -- 11510 Changes
, oki_job_run_dtl jrd -- 11510 Changes
, okc_k_party_roles_b cpl
, okc_statuses_b sts
, gl_periods spd
, gl_periods spd1 -- 11510 Changes
, okc_k_rel_objs ro
, okx_order_headers_v oh
, okc_governances og -- 11510 Changes
, ( SELECT ol.subject_chr_id, decode(count(1),0,'Y','N') is_new_yn
FROM okc_operation_lines ol
WHERE 1 = 1
AND ol.object_chr_id is not null
GROUP BY ol.subject_chr_id
) r
/* 11510 Changes Start */
, (SELECT okscle.dnz_chr_id
, SUM (NVL(ubt_amount,0) + NVL(credit_amount,0) +
NVL(suppressed_credit,0)) terminated_amount
FROM oks_k_lines_b okscle,okc_k_lines_b okccle
WHERE okccle.id = okscle.cle_id
AND okccle.price_level_ind='Y'
GROUP BY okscle.dnz_chr_id
) terminated
/* 11510 Changes End */
WHERE 1 = 1
and khr.buy_or_sell = 'S'
and khr.template_yn = 'N'
and khr.application_id = 515
and cpl.rle_code in ('CUSTOMER','LICENSEE','BUYER')
and cpl.dnz_chr_id = khr.id
and cpl.cle_id IS NULL
and sts.code = khr.sts_code
and spd.period_set_name = jrd.period_set_name
and spd.period_type = jrd.accounted_period_type
and spd.adjustment_period_flag = 'N'
and khr.start_date BETWEEN spd.start_date
AND spd.end_date+0.99999
/* 11510 Changes Start */
and nvl(spd1.period_set_name,jrd.period_set_name) = jrd.period_set_name
and nvl(spd1.period_type,jrd.accounted_period_type) = jrd.accounted_period_type
and spd1.adjustment_period_flag(+) = 'N'
and hoks.est_rev_date BETWEEN spd1.start_date(+)
AND spd1.end_date(+) + 0.99999
/* 11510 Changes End */
and ro.chr_id (+) = khr.id
AND ro.jtot_object1_code (+) = 'OKX_ORDERHEAD'
/*Bug Fix 3675638
AND ro.rty_code (+) = 'CONTRACTSERVICESORDER'
*/
AND oh.id1 (+) = ro.object1_id1
AND r.subject_chr_id(+) = khr.id
/* 11510 Changes Start */
AND hoks.chr_id = khr.id
AND og.chr_id(+) = hoks.chr_id -- From oki_pricing_rules
AND jrd.chr_id = khr.id
AND terminated.dnz_chr_id(+) = khr.id
/* 11510 Changes End */
;
UPDATE oki_sales_k_hdrs shd
SET (close_period_num
,close_period_name
,close_quarter
,close_year) =
(SELECT cpd.period_num
,cpd.period_name
,cpd.quarter_num
,cpd.period_year
FROM gl_periods cpd
,gl_sets_of_books sob
WHERE shd.close_date between cpd.start_date and cpd.end_date+0.99999
and cpd.period_type = sob.accounted_period_type
and cpd.period_set_name = sob.period_set_name
and cpd.adjustment_period_flag = 'N'
and sob.set_of_books_id = shd.sob_id)
WHERE shd.close_date IS NOT NULL
;
UPDATE oki_sales_k_hdrs shd
SET (terminated_amount
,sob_terminated_amount
,base_terminated_amount) = (
SELECT -SUM(bcl.amount),
-SUM(bcl.amount) * shd.sob_contract_amount/ DECODE(shd.contract_amount,0,NULL,shd.contract_amount),
-SUM(bcl.amount) * shd.base_contract_amount/ DECODE(shd.contract_amount,0,NULL,shd.contract_amount)
FROM oks_bill_cont_lines bcl,
okc_k_lines_b cle
WHERE bcl.bill_action = 'TR'
AND cle.id = bcl.cle_id
AND cle.chr_id = shd.chr_id )
WHERE shd.date_terminated IS NOT NULL;
/* Fetches distinct qualifiers before issuing update with subquery. */
l_errpos := 6.25;
update_oki_refresh( p_object_name => 'OKI_SALES_K_HDRS'
, p_job_run_id => l_job_run_id
, x_retcode => retcode);
RAISE l_no_update_refresh;
when l_no_update_refresh then
fnd_message.set_name(application => 'OKI'
,name => 'OKI_TABLE_LOAD_FAILURE');
,buff => 'Update of OKI_REFRESHS failed');
l_no_update_refresh EXCEPTION;
DELETE FROM oki_sales_k_hdrs shd
WHERE chr_id in ( SELECT /*+ index_ffs(jrd oki_job_run_dtl_u1)*/
jrd.chr_id
FROM oki_job_run_dtl jrd
WHERE jrd.job_run_id = l_job_run_id ) ;
INSERT INTO oki_sales_k_hdrs (
chr_id
, contract_number
, contract_number_modifier
, complete_contract_number
, order_number
, authoring_org_id
, organization_name
, scs_code
, sts_code
, ste_code
, customer_party_id
, customer_name
, customer_number
, contract_amount
, currency_code
, contract_amount_renewed
, currency_code_renewed
, win_percent
, forecast_amount
, sob_id
, sob_contract_amount
, sob_forecast_amount
, sob_contract_amount_renewed
, sob_currency_code
, base_contract_amount
, base_forecast_amount
, base_contract_amount_renewed
, base_currency_code
, close_date
, start_date
, end_date
, duration
, period
, date_approved
, date_signed
, date_renewed
, date_canceled
, date_terminated
, start_period_num
, start_period_name
, start_quarter
, start_year
, close_period_num
, close_period_name
, close_quarter
, close_year
, trn_code
, inventory_organization_id
, is_new_yn
, is_latest_yn
, orig_system_source_code
, orig_system_id1
, orig_system_reference1
, contract_type
, application_id
, creation_date
, last_update_date
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, major_version
, minor_version
/* 11510 Changes Start
Added the new columns from oki_pricing_rules and oki_qto
Also, terminated amount is now calculated during insertion */
, agreement_id -- From oki_pricing_rule
, acct_rule_id
, payment_term_id -- From oki_pricing_rule
, inv_rule_id
, list_header_id -- From oki_pricing_rule
, grace_duration -- From oki_pricing_rule
, grace_period_code -- From oki_pricing_rule
, quote_to_contact_id -- From oki_qto
, quote_to_site_id -- From oki_qto
, quote_to_email_id -- From oki_qto
, quote_to_phone_id -- From oki_qto
, quote_to_fax_id -- From oki_qto
, terminated_amount
, sob_terminated_amount
, base_terminated_amount
/* 11510 Changes End */
) SELECT /*+ leading(jrd) full(jrd) cardinality(jrd,1) use_nl(r) */
khr.id
, khr.contract_number
, khr.contract_number_modifier
, khr.contract_number || decode(khr.contract_number_modifier
,null, null
,'-' || khr.contract_number_modifier )
, oh.order_number
, khr.authoring_org_id
, NULL organization_name -- 11510 Changes
, khr.scs_code
, khr.sts_code
, sts.ste_code
, to_number(cpl.object1_id1)
, NULL customer_name -- 11510 Changes
, NULL customer_number -- 11510 Changes
, khr.estimated_amount
, khr.currency_code
, khr.estimated_amount_renewed
, khr.currency_code_renewed
, hoks.est_rev_percent win_percent -- 11510 Changes
, ((khr.estimated_amount * hoks.est_rev_percent ) / 100 ) -- 11510 Changes
, jrd.sob_id -- 11510 Changes
, (khr.estimated_amount * jrd.trx_func_rate) -- 11510 Changes
,(((khr.estimated_amount * jrd.trx_func_rate) * hoks.est_rev_percent ) / 100 ) -- 11510 Changes
, NULL
, jrd.func_currency -- 11510 Changes
, (khr.estimated_amount * jrd.trx_base_rate) -- 11510 Changes
, (((khr.estimated_amount * jrd.trx_base_rate) * hoks.est_rev_percent) / 100 ) -- 11510 Changes
, NULL
, l_base_currency
, hoks.est_rev_date close_date -- 11510 Changes
, TRUNC(khr.start_date)
, TRUNC(khr.end_date)
, oki_disco_util_pub.get_duration(khr.start_date,khr.end_date)
, oki_disco_util_pub.get_period(khr.start_date,khr.end_date)
, TRUNC(khr.date_approved)
, TRUNC(khr.date_signed)
, TRUNC(khr.date_renewed)
, TRUNC(khr.datetime_cancelled)
, TRUNC(khr.date_terminated)
, spd.period_num
, spd.period_name
, spd.quarter_num
, spd.period_year
/* 11510 Changes Start */
,spd1.period_num
,spd1.period_name
,spd1.quarter_num
,spd1.period_year
/* 11510 Changes End */
, khr.trn_code
, khr.inv_organization_id
, decode( nvl(r.is_new_yn,'Y'),'Y','Y',null) is_new_yn
, decode(khr.datetime_cancelled,null,null,'N') is_latest_yn
, khr.orig_system_source_code
, khr.orig_system_id1
, khr.orig_system_reference1
, decode( nvl(r.is_new_yn,'Y'),'Y','NEW','REN') ren_type
, khr.application_id
, khr.creation_date
, khr.last_update_date
, khr.attribute_category
, khr.attribute1
, khr.attribute2
, khr.attribute3
, khr.attribute4
, khr.attribute5
, khr.attribute6
, khr.attribute7
, khr.attribute8
, khr.attribute9
, khr.attribute10
, khr.attribute11
, khr.attribute12
, khr.attribute13
, khr.attribute14
, khr.attribute15
/* 11510 Changes Start */
, jrd.major_version major_version
, jrd.minor_version minor_version
, og.ISA_AGREEMENT_ID agreement_id -- From oki_pricing_rules
, hoks.acct_rule_id
, khr.payment_term_id -- From oki_pricing_rules
, khr.inv_rule_id
, khr.price_list_id list_header_id -- From oki_pricing_rules
, hoks.grace_duration -- From oki_pricing_rules
, hoks.grace_period -- From oki_pricing_rules
, hoks.quote_to_contact_id quote_to_contact_id -- From oki_qto
, hoks.quote_to_site_id quote_to_site_id -- From oki_qto
, hoks.quote_to_email_id quote_to_email_id -- From oki_qto
, hoks.quote_to_phone_id quote_to_phone_id -- From oki_qto
, hoks.quote_to_fax_id quote_to_fax_id -- From oki_qto
, terminated.terminated_amount
, terminated.terminated_amount * jrd.trx_func_rate
, terminated.terminated_amount * jrd.trx_base_rate
/* 11510 Changes End */
FROM
oki_job_run_dtl jrd
, okc_k_headers_b khr
, oks_k_headers_b hoks -- 11510 Changes
, okc_k_party_roles_b cpl
, okc_statuses_b sts
, gl_periods spd
, gl_periods spd1 -- 11510 Changes
, okc_k_rel_objs ro
, okx_order_headers_v oh
, okc_governances og -- From oki_pricing_rule
, ( SELECT /*+ leading(jrd) full(jrd) cardinality(jrd,1)*/
ol.subject_chr_id, decode(count(1),0,'Y','N') is_new_yn
FROM okc_operation_lines ol, oki_job_run_dtl jrd
WHERE 1 = 1
AND jrd.job_run_id = l_job_run_id
AND jrd.action_flag = 'I'
AND ol.subject_chr_id = jrd.chr_id
AND ol.object_chr_id is not null
GROUP by ol.subject_chr_id
) r
/* 11510 Changes Start */
,(SELECT okscle.dnz_chr_id /*+ leading(jrd) full(jrd) cardinality(jrd,1)*/
, SUM (NVL(ubt_amount,0) + NVL(credit_amount,0) +
NVL(suppressed_credit,0)) terminated_amount
FROM oks_k_lines_b okscle,okc_k_lines_b okccle,oki_job_run_dtl jrd
WHERE okccle.id = okscle.cle_id
AND okscle.dnz_chr_id = jrd.chr_id
AND jrd.job_run_id = l_job_run_id
AND jrd.action_flag = 'I'
AND okccle.price_level_ind='Y'
GROUP BY okscle.dnz_chr_id
) terminated
/* 11510 Changes End */
WHERE 1 = 1
AND jrd.job_run_id = l_job_run_id
AND jrd.action_flag = 'I'
AND khr.id = jrd.chr_id
AND cpl.rle_code in ('CUSTOMER','LICENSEE','BUYER')
AND cpl.dnz_chr_id = khr.id
AND cpl.cle_id IS NULL
AND sts.code = khr.sts_code
AND spd.period_set_name = jrd.period_set_name
AND spd.period_type = jrd.accounted_period_type
AND spd.adjustment_period_flag = 'N'
AND khr.start_date BETWEEN spd.start_date
AND spd.end_date + 0.99999
/* 11510 Changes Start */
AND nvl(spd1.period_set_name,jrd.period_set_name) = jrd.period_set_name
AND nvl(spd1.period_type,jrd.accounted_period_type) = jrd.accounted_period_type
AND spd1.adjustment_period_flag(+) = 'N'
AND hoks.est_rev_date BETWEEN spd1.start_date(+)
AND spd1.end_date(+) + 0.99999
/* 11510 Changes End */
AND ro.chr_id(+) = jrd.chr_id
AND ro.jtot_object1_code (+) = 'OKX_ORDERHEAD'
/* Bug Fix 3675638
AND ro.rty_code (+) = 'CONTRACTSERVICESORDER'
*/
AND oh.id1 (+) = ro.object1_id1
AND r.subject_chr_id(+) = khr.id
/* 11510 Changes Start */
AND hoks.chr_id = khr.id
AND og.chr_id(+) = hoks.chr_id -- From oki_pricing_rule
AND terminated.dnz_chr_id(+) = khr.id;
UPDATE oki_sales_k_hdrs shd
SET (close_period_num
,close_period_name
,close_quarter
,close_year) =
(SELECT cpd.period_num
,cpd.period_name
,cpd.quarter_num
,cpd.period_year
FROM gl_periods cpd
,gl_sets_of_books sob
WHERE shd.close_date between cpd.start_date and cpd.end_date+0.99999
and cpd.period_type = sob.accounted_period_type
and cpd.period_set_name = sob.period_set_name
and cpd.adjustment_period_flag = 'N'
and sob.set_of_books_id = shd.sob_id)
WHERE shd.chr_id IN (
SELECT chr_id
FROM oki_job_run_dtl jrd
WHERE 1 = 1
AND jrd.job_run_id = l_job_run_id
AND jrd.action_flag = 'I'
)
AND shd.close_date IS NOT NULL;
UPDATE oki_sales_k_hdrs shd
SET (terminated_amount
,sob_terminated_amount
,base_terminated_amount) = (
SELECT -SUM(bcl.amount),
-SUM(bcl.amount) * shd.sob_contract_amount/ DECODE(shd.contract_amount,0,NULL,shd.contract_amount),
-SUM(bcl.amount) * shd.base_contract_amount/ DECODE(shd.contract_amount,0,NULL,shd.contract_amount)
FROM oks_bill_cont_lines bcl,
okc_k_lines_b cle
WHERE bcl.bill_action = 'TR'
AND cle.id = bcl.cle_id
AND cle.chr_id = shd.chr_id )
WHERE shd.date_terminated IS NOT NULL
AND shd.chr_id IN (
SELECT chr_id
FROM oki_job_run_dtl jrd
WHERE 1 = 1
AND jrd.job_run_id = l_job_run_id
AND jrd.action_flag = 'I'
);
update_oki_refresh( p_object_name => l_table_name
, p_job_run_id => l_job_run_id
, x_retcode => l_retcode ) ;
RAISE l_no_update_refresh;
WHEN l_no_update_refresh then
fnd_message.set_name(application => 'OKI'
,name => 'OKI_TABLE_LOAD_FAILURE');
,buff => 'Update of OKI_REFRESHS failed');
l_no_update_refresh EXCEPTION;
INSERT /*+ append */ INTO OKI_SOLD_ITM_LINES
(
CLE_ID,
CHR_ID,
CONTRACT_NUMBER,
CONTRACT_NUMBER_MODIFIER,
COMPLETE_CONTRACT_NUMBER,
SCS_CODE,
LINE_NUMBER,
START_DATE,
END_DATE,
DURATION,
PERIOD,
STS_CODE,
ste_code,
TRN_CODE,
DATE_TERMINATED,
DATE_RENEWED,
NUMBER_OF_ITEMS,
UOM_CODE,
UNIT_PRICE,
UNIT_PRICE_PERCENT,
PRICE_NEGOTIATED,
CURRENCY_CODE,
PRICE_NEGOTIATED_RENEWED,
CURRENCY_CODE_RENEWED,
SOB_PRICE_UNIT,
SOB_PRICE_NEGOTIATED,
SOB_PRICE_NEGOTIATED_RENEWED,
SOB_CURRENCY_CODE,
BASE_PRICE_UNIT,
BASE_PRICE_NEGOTIATED,
BASE_PRICE_NEGOTIATED_RENEWED,
BASE_CURRENCY_CODE,
SOLD_ITEM,
ITEM_ID,
CONCATENATED_SEGMENTS,
AUTHORING_ORG_ID,
INVENTORY_ORGANIZATION_ID,
CREATION_DATE,
LAST_UPDATE_DATE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
BILL_TO_SITE_USE_ID, -- 11510 Changes
SHIP_TO_SITE_USE_ID -- 11510 Changes
)
SELECT
cle.id
,shd.chr_id
,shd.contract_number
,shd.contract_number_modifier
,shd.complete_contract_number
,shd.scs_code
,cle.line_number
,trunc(cle.start_date)
,trunc(cle.end_date)
,oki_disco_util_pub.get_duration(cle.start_date,cle.end_date)
,oki_disco_util_pub.get_period(cle.start_date,cle.end_date)
,cle.sts_code
,sts.ste_code
,cle.trn_code
,trunc(cle.date_terminated)
,trunc(cle.date_renewed)
,cim.number_of_items
,cim.uom_code
,cle.price_unit
,cle.price_unit_percent
,cle.price_negotiated
,shd.currency_code
,cle.price_negotiated_renewed
,cle.currency_code_renewed
,(cle.price_unit * jrd.trx_func_rate) -- 11510 Changes
,(cle.price_negotiated * jrd.trx_func_rate) -- 11510 Changes
,null
,shd.sob_currency_code
,(cle.price_unit * jrd.trx_base_rate) -- 11510 Changes
,(cle.price_negotiated * jrd.trx_base_rate) -- 11510 Changes
,null
,l_base_currency
, null
,cim.object1_id1
, null
,shd.authoring_org_id
,cim.object1_id2
,cle.creation_date
,cle.last_update_date
,cle.attribute_category
,cle.attribute1
,cle.attribute2
,cle.attribute3
,cle.attribute4
,cle.attribute5
,cle.attribute6
,cle.attribute7
,cle.attribute8
,cle.attribute9
,cle.attribute10
,cle.attribute11
,cle.attribute12
,cle.attribute13
,cle.attribute14
,cle.attribute15
,cle.bill_to_site_use_id -- 11510 Changes
,cle.ship_to_site_use_id -- 11510 Changes
FROM
okc_k_lines_b cle
,oki_sales_k_hdrs shd
,okc_k_items cim
,okc_statuses_b sts
,oki_job_run_dtl jrd -- 11510 Changes
WHERE 1=1
and cim.cle_id = cle.id
and sts.code = cle.sts_code
and cle.chr_id is not null
and cle.dnz_chr_id = shd.chr_id
and jrd.chr_id = shd.chr_id -- 11510 Changes
and cim.jtot_object1_code IN ('OKX_SERVICE','OKX_WARRANTY') -- 11510 Changes
and cle.lse_id IN (1,19,14) -- 11510 Changes
;
update_oki_refresh( p_object_name => 'OKI_SOLD_ITM_LINES'
, p_job_run_id => l_job_run_id
, x_retcode => retcode ) ;
RAISE l_no_update_refresh;
WHEN l_no_update_refresh then
fnd_message.set_name(application => 'OKI'
,name => 'OKI_TABLE_LOAD_FAILURE');
,buff => 'Update of OKI_REFRESHS failed');
l_no_update_refresh EXCEPTION;
DELETE FROM oki_sold_itm_lines sil
WHERE chr_id in ( SELECT jrd.chr_id
FROM oki_job_run_dtl jrd
WHERE jrd.job_run_id = l_job_run_id ) ;
l_loc := 'Inserting into ' || l_table_name ;
INSERT INTO oki_sold_itm_lines(
cle_id
, chr_id
, contract_number
, contract_number_modifier
, complete_contract_number
, scs_code
, line_number
, start_date
, end_date
, duration
, period
, sts_code
, ste_code
, trn_code
, date_terminated
, date_renewed
, number_of_items
, uom_code
, unit_price
, unit_price_percent
, price_negotiated
, currency_code
, price_negotiated_renewed
, currency_code_renewed
, sob_price_unit
, sob_price_negotiated
, sob_price_negotiated_renewed
, sob_currency_code
, base_price_unit
, base_price_negotiated
, base_price_negotiated_renewed
, base_currency_code
, sold_item
, item_id
, concatenated_segments
, authoring_org_id
, inventory_organization_id
, creation_date
, last_update_date
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, bill_to_site_use_id -- 11510 Changes
, ship_to_site_use_id -- 11510 Changes
) SELECT
cle.id
, shd.chr_id
, shd.contract_number
, shd.contract_number_modifier
, shd.complete_contract_number
, shd.scs_code
, cle.line_number
, TRUNC(cle.start_date)
, TRUNC(cle.end_date)
, oki_disco_util_pub.get_duration(cle.start_date,cle.end_date)
, oki_disco_util_pub.get_period(cle.start_date,cle.end_date)
, cle.sts_code
, sts.ste_code
, cle.trn_code
, TRUNC(cle.date_terminated)
, TRUNC(cle.date_renewed)
, cim.number_of_items
, cim.uom_code
, cle.price_unit
, cle.price_unit_percent
, cle.price_negotiated
, shd.currency_code
, cle.price_negotiated_renewed
, cle.currency_code_renewed
, (cle.price_unit * jrd.trx_func_rate) -- 11510 Changes
, (cle.price_negotiated * jrd.trx_func_rate) -- 11510 Changes
, null
, shd.sob_currency_code
, (cle.price_unit * jrd.trx_base_rate) -- 11510 Changes
, (cle.price_negotiated * jrd.trx_base_rate) -- 11510 Changes
, null
, l_base_currency
, null
, cim.object1_id1
, null
, shd.authoring_org_id
, cim.object1_id2
, cle.creation_date
, cle.last_update_date
, cle.attribute_category
, cle.attribute1
, cle.attribute2
, cle.attribute3
, cle.attribute4
, cle.attribute5
, cle.attribute6
, cle.attribute7
, cle.attribute8
, cle.attribute9
, cle.attribute10
, cle.attribute11
, cle.attribute12
, cle.attribute13
, cle.attribute14
, cle.attribute15
, cle.bill_to_site_use_id -- 11510 Changes
, cle.ship_to_site_use_id -- 11510 Changes
FROM
okc_k_lines_b cle
, oki_sales_k_hdrs shd
, okc_k_items cim
, okc_statuses_b sts
, oki_job_run_dtl jrd
WHERE 1=1
AND cim.cle_id = cle.id
and sts.code = cle.sts_code
AND cle.chr_id IS NOT NULL
AND cle.dnz_chr_id = shd.chr_id
AND jrd.chr_id = shd.chr_id
AND jrd.job_run_id = l_job_run_id
AND jrd.action_flag = 'I'
AND cim.jtot_object1_code IN ('OKX_SERVICE','OKX_WARRANTY') -- 11510 Changes
;
update_oki_refresh(p_object_name => l_table_name
, p_job_run_id => l_job_run_id
, x_retcode => l_retcode ) ;
RAISE l_no_update_refresh;
when l_no_update_refresh then
fnd_message.set_name(application => 'OKI'
,name => 'OKI_TABLE_LOAD_FAILURE');
,buff => 'Update of OKI_REFRESHS failed');
l_no_update_refresh EXCEPTION;
INSERT /*+ append */ INTO OKI_COV_PRD_LINES
(
CLE_ID,
CHR_ID,
PARENT_CLE_ID,
SCS_CODE,
CONTRACT_NUMBER,
CONTRACT_NUMBER_MODIFIER,
COMPLETE_CONTRACT_NUMBER,
LINE_NUMBER,
START_DATE,
END_DATE,
DURATION,
PERIOD,
STS_CODE,
ste_code,
TRN_CODE,
DATE_TERMINATED,
DATE_RENEWED,
NUMBER_OF_ITEMS,
UOM_CODE,
UNIT_PRICE,
UNIT_PRICE_PERCENT,
PRICE_NEGOTIATED,
CURRENCY_CODE,
PRICE_NEGOTIATED_RENEWED,
CURRENCY_CODE_RENEWED,
SOB_PRICE_UNIT,
SOB_PRICE_NEGOTIATED,
SOB_PRICE_NEGOTIATED_RENEWED,
SOB_CURRENCY_CODE,
BASE_PRICE_UNIT,
BASE_PRICE_NEGOTIATED,
BASE_PRICE_NEGOTIATED_RENEWED,
BASE_CURRENCY_CODE,
SERVICE_ITEM,
COVERED_PRODUCT_ID,
CUSTOMER_PRODUCT_ITEM_ID,
CONCATENATED_SEGMENTS,
SERIAL_NUMBER,
REFERENCE_NUMBER,
COV_PROD_QUANTITY,
COV_PROD_UOM,
INSTALLATION_DATE,
COV_PROD_ORDER_DATE,
COV_PROD_ORDER_NUMBER,
COV_PROD_ORDER_LINE,
COV_PROD_NET_AMOUNT,
COV_PROD_ORDER_LINE_ID,
SYSTEM_ID,
SYSTEM_NAME,
PRODUCT_AGREEMENT_ID,
COV_PROD_BILL_TO_SITE_ID,
COV_PROD_SHIP_TO_SITE_ID,
COV_PROD_INSTALL_SITE_ID,
COV_PROD_BILL_TO_CONTACT_ID,
COV_PROD_SHIP_TO_CONTACT_ID,
AUTHORING_ORG_ID,
INVENTORY_ORGANIZATION_ID,
CREATION_DATE,
LAST_UPDATE_DATE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15 ,
PRICING_ATTRIBUTE1,
PRICING_ATTRIBUTE2,
PRICING_ATTRIBUTE3,
PRICING_ATTRIBUTE4,
PRICING_ATTRIBUTE5,
PRICING_ATTRIBUTE6,
PRICING_ATTRIBUTE7,
PRICING_ATTRIBUTE8,
PRICING_ATTRIBUTE9,
PRICING_ATTRIBUTE10,
/* 11510 changes start added these columns*/
END_PERIOD_NUM ,
END_PERIOD_NAME ,
END_QUARTER ,
END_YEAR ,
IS_EXP_NOT_RENEWED_YN
/* 11510 changes end */
)
SELECT /*+ leading(sil) */
cle.id
,sil.chr_id
,cle.cle_id
,sil.scs_code
,sil.contract_number
,sil.contract_number_modifier
,sil.complete_contract_number
,sil.line_number ||'.'|| cle.line_number
,trunc(cle.start_date)
,trunc(cle.end_date)
,oki_disco_util_pub.get_duration(cle.start_date,cle.end_date)
,oki_disco_util_pub.get_period(cle.start_date,cle.end_date)
,cle.sts_code
,sts.ste_code
,cle.trn_code
,trunc(cle.date_terminated)
,trunc(cle.date_renewed)
,cim.number_of_items
,cim.uom_code
,cle.price_unit
,cle.price_unit_percent
,cle.price_negotiated
,sil.currency_code
,cle.price_negotiated_renewed
,cle.currency_code_renewed
/* 11510 changes start */
,cle.price_unit * jrd.trx_func_rate --(cle.price_unit * nvl(kcr.sob_currency_conv_rate,dre1.conversion_rate))
,cle.price_negotiated * jrd.trx_func_rate --(cle.price_negotiated * nvl(kcr.sob_currency_conv_rate,dre1.conversion_rate))
/* 11510 changes end */
,null
,sil.sob_currency_code
/* 11510 changes start */
,cle.price_unit * jrd.trx_base_rate --(cle.price_unit * nvl(kcr.base_currency_conv_rate,dre3.conversion_rate))
,cle.price_negotiated * jrd.trx_base_rate --(cle.price_negotiated * nvl(kcr.base_currency_conv_rate,dre3.conversion_rate))
/* 11510 changes end */
,null
,l_base_currency
,sil.sold_item
,cii.instance_id
,cii.inventory_item_id
,null
,cii.serial_number
,cii.instance_number
,cii.quantity
,cii.unit_of_measure
,cii.install_date
,null
,null
,null
,null
,cii.last_oe_order_line_id
,cii.system_id
,null
,cii.last_oe_agreement_id
,null
,null
,cii.install_location_id
,null
,null
,sil.authoring_org_id
,sil.inventory_organization_id
,cle.creation_date
,cle.last_update_date
,cle.attribute_category
,cle.attribute1
,cle.attribute2
,cle.attribute3
,cle.attribute4
,cle.attribute5
,cle.attribute6
,cle.attribute7
,cle.attribute8
,cle.attribute9
,cle.attribute10
,cle.attribute11
,cle.attribute12
,cle.attribute13
,cle.attribute14
,cle.attribute15
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
/*11510 changes start*/
, epd.period_num
, epd.period_name
, epd.quarter_num
, epd.period_year
--Bug Fix 3469671 code changes-------------------------------------------------
, DECODE(sts.ste_code,'EXPIRED' -- If contract is expired then
,DECODE(cle.price_level_ind,'Y' --check if line is priced
,DECODE(cle.date_renewed,NULL,'Y' --check if renewed or record from operation line exists
,DECODE(exp.cle_id,NULL,'Y','N') --if so 'N' else 'Y'
)
,'N')--if the line is not priced 'N'
,'N')--if the contract is not expired 'N'
is_exp_not_renewed_yn
--Bug Fix 3469671 code changes-------------------------------------------------
from
oki_sold_itm_lines sil
/*11510 changes added*/
,oki_job_run_dtl jrd
,okc_k_lines_b cle
,okc_k_items cim
,csi_item_instances cii
,okc_statuses_b sts
/*11510 changes added*/
,gl_periods epd
,(select distinct object_cle_id cle_id from okc_operation_lines
where active_yn = 'Y' ) exp
where 1 = 1
and cii.instance_id = to_number(cim.object1_id1)
and cim.cle_id = cle.id
and sts.code = cle.sts_code
and cle.cle_id = sil.cle_id
/* 11510 changes added join for expired inline view */
and exp.cle_id(+) = cle.cle_id
and cle.lse_id in (9, 18, 25)
/*11510 changes start*/
and jrd.chr_id = sil.chr_id
and cle.end_date between epd.start_date and epd.end_date+0.99999
and epd.period_type = jrd.accounted_period_type
and epd.period_set_name = jrd.period_set_name
and epd.adjustment_period_flag = 'N'
/*11510 changes end */
;
update_oki_refresh( p_object_name => 'OKI_COV_PRD_LINES'
, p_job_run_id => l_job_run_id
, x_retcode => retcode);
raise l_no_update_refresh;
WHEN l_no_update_refresh THEN
fnd_message.set_name( application => 'OKI'
, name => 'OKI_TABLE_LOAD_FAILURE');
, buff =>'Update of OKI_REFRESHS failed' );
l_no_update_refresh EXCEPTION;
DELETE FROM oki_cov_prd_lines cpl
WHERE chr_id in ( SELECT /*+ index_ffs(jrd oki_job_run_dtl_u1)*/
jrd.chr_id
FROM oki_job_run_dtl jrd
WHERE jrd.job_run_id = l_job_run_id ) ;
l_loc := 'Inserting into ' || l_table_name ;
INSERT INTO oki_cov_prd_lines (
cle_id
, chr_id
, parent_cle_id
, scs_code
, contract_number
, contract_number_modifier
, complete_contract_number
, line_number
, start_date
, end_date
, duration
, period
, sts_code
, ste_code
, trn_code
, date_terminated
, date_renewed
, number_of_items
, uom_code
, unit_price
, unit_price_percent
, price_negotiated
, currency_code
, price_negotiated_renewed
, currency_code_renewed
, sob_price_unit
, sob_price_negotiated
, sob_price_negotiated_renewed
, sob_currency_code
, base_price_unit
, base_price_negotiated
, base_price_negotiated_renewed
, base_currency_code
, service_item
, covered_product_id
, customer_product_item_id
, concatenated_segments
, serial_number
, reference_number
, cov_prod_quantity
, cov_prod_uom
, installation_date
, cov_prod_order_date
, cov_prod_order_number
, cov_prod_order_line
, cov_prod_net_amount
, cov_prod_order_line_id
, system_id
, system_name
, product_agreement_id
, cov_prod_bill_to_site_id
, cov_prod_ship_to_site_id
, cov_prod_install_site_id
, cov_prod_bill_to_contact_id
, cov_prod_ship_to_contact_id
, authoring_org_id
, inventory_organization_id
, creation_date
, last_update_date
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, pricing_attribute1
, pricing_attribute2
, pricing_attribute3
, pricing_attribute4
, pricing_attribute5
, pricing_attribute6
, pricing_attribute7
, pricing_attribute8
, pricing_attribute9
, pricing_attribute10
/* 11510 changes start */
, end_period_num
, end_period_name
, end_quarter
, end_year
, is_exp_not_renewed_yn
/* 11510 changes start */
) SELECT /*+ leading(jrd) full(jrd) cardinality(jrd,1) */
cle.id
, sil.chr_id
, cle.cle_id
, sil.scs_code
, sil.contract_number
, sil.contract_number_modifier
, sil.complete_contract_number
, sil.line_number ||'.'|| cle.line_number
, TRUNC(cle.start_date)
, TRUNC(cle.end_date)
,oki_disco_util_pub.get_duration(cle.start_date,cle.end_date)
,oki_disco_util_pub.get_period(cle.start_date,cle.end_date)
, cle.sts_code
, sts.ste_code
, cle.trn_code
, TRUNC(cle.date_terminated)
, TRUNC(cle.date_renewed)
, cim.number_of_items
, cim.uom_code
, cle.price_unit
, cle.price_unit_percent
, cle.price_negotiated
, sil.currency_code
, cle.price_negotiated_renewed
, cle.currency_code_renewed
/* 11510 changes start */
, cle.price_unit * jrd.trx_func_rate --(cle.price_unit * NVL(kcr.sob_currency_conv_rate, dre1.conversion_rate))
, cle.price_negotiated * jrd.trx_func_rate --(cle.price_negotiated * NVL(kcr.sob_currency_conv_rate,dre1.conversion_rate))
/* 11510 changes start */
, null--(cle.price_negotiated_renewed * NVL(kcr.renewed_sob_conv_rate,
-- dre2.conversion_rate))
, sil.sob_currency_code
/* 11510 changes start */
, cle.price_unit * jrd.trx_base_rate --(cle.price_unit * NVL(kcr.base_currency_conv_rate,dre3.conversion_rate))
, cle.price_negotiated * jrd.trx_base_rate --(cle.price_negotiated * NVL(kcr.base_currency_conv_rate,dre3.conversion_rate))
/* 11510 changes start */
, null --(cle.price_negotiated_renewed * NVL(kcr.renewed_base_conv_rate,
-- dre4.conversion_rate))
, l_base_currency
, sil.sold_item
, cii.instance_id
, cii.inventory_item_id
, null
, cii.serial_number
, cii.instance_number
, cii.quantity
, cii.unit_of_measure
, cii.install_date
, null
, null
, null
, null
, cii.last_oe_order_line_id
, cii.system_id
, null
, cii.last_oe_agreement_id
, null
, null
, cii.install_location_id
, null
, null
, sil.authoring_org_id
, sil.inventory_organization_id
, cle.creation_date
, cle.last_update_date
, cle.attribute_category
, cle.attribute1
, cle.attribute2
, cle.attribute3
, cle.attribute4
, cle.attribute5
, cle.attribute6
, cle.attribute7
, cle.attribute8
, cle.attribute9
, cle.attribute10
, cle.attribute11
, cle.attribute12
, cle.attribute13
, cle.attribute14
, cle.attribute15
, null
, null
, null
, null
, null
, null
, null
, null
, null
, null
, epd.period_num
, epd.period_name
, epd.quarter_num
, epd.period_year
--Bug Fix 3469671 code changes-------------------------------------------------
, DECODE(sts.ste_code,'EXPIRED' -- If contract is expired then
,DECODE(cle.price_level_ind,'Y' --check if line is priced
,DECODE(cle.date_renewed,NULL,'Y' --check if renewed or record from operation line exists
,DECODE(exp.cle_id,NULL,'Y','N') --if so 'N' else 'Y'
)
,'N')--if the line is not priced 'N'
,'N')--if the contract is not expired 'N'
is_exp_not_renewed_yn
--Bug Fix 3469671 code changes-------------------------------------------------
FROM
okc_k_lines_b cle
, oki_sold_itm_lines sil
, okc_k_items cim
, csi_item_instances cii
, okc_statuses_b sts
, oki_job_run_dtl jrd
/*11510 changes added*/
, gl_periods epd
,(select distinct object_cle_id cle_id from okc_operation_lines
where active_yn = 'Y' ) exp --Bug Fix 3469671 code changes
WHERE
cii.instance_id = to_number(cim.object1_id1)
AND exp.cle_id(+) = cle.id --Bug Fix 3469671 code changes
AND cim.cle_id = cle.id
AND sts.code = cle.sts_code
AND cle.cle_id = sil.cle_id
AND cle.lse_id IN (9, 18, 25)
AND jrd.chr_id = sil.chr_id
AND jrd.job_run_id = l_job_run_id
AND jrd.action_flag = 'I'
/*11510 changes start*/
AND cle.end_date between epd.start_date and epd.end_date+0.99999
AND epd.period_type = jrd.accounted_period_type
AND epd.period_set_name = jrd.period_set_name
AND epd.adjustment_period_flag = 'N'
/*11510 changes end*/
;
update_oki_refresh(p_object_name => 'OKI_COV_PRD_LINES'
, p_job_run_id => l_job_run_id
, x_retcode => l_retcode ) ;
RAISE l_no_update_refresh;
when l_no_update_refresh then
fnd_message.set_name(application => 'OKI'
,name => 'OKI_TABLE_LOAD_FAILURE');
,buff => 'Update of OKI_REFRESHS failed');
l_no_update_refresh EXCEPTION;
INSERT /*+ append */ INTO OKI_K_SALESREPS
(
PARTY_CONTACT_ID,
CONTRACT_ID,
PARTY_ROLE_ID,
CONTACT_ROLE_CODE,
CONTACT_ID,
SALESREP_NAME ,
CREATION_DATE,
LAST_UPDATE_DATE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
)
SELECT /*+ use_hash(shd) use_hash(ctt) use_hash(srp) use_hash(jrs) */
ctt.id
,ctt.dnz_chr_id
,ctt.cpl_id
,ctt.cro_code
,ctt.object1_id1
,NULL salesrep_name -- 11510 Changes
,ctt.creation_date
,ctt.last_update_date
,ctt.attribute_category
,ctt.attribute1
,ctt.attribute2
,ctt.attribute3
,ctt.attribute4
,ctt.attribute5
,ctt.attribute6
,ctt.attribute7
,ctt.attribute8
,ctt.attribute9
,ctt.attribute10
,ctt.attribute11
,ctt.attribute12
,ctt.attribute13
,ctt.attribute14
,ctt.attribute15
FROM
oki_sales_k_hdrs shd
,okc_contacts ctt
WHERE 1=1
AND ctt.cro_code = l_salesperson_code -- 11510 Changes
AND ctt.dnz_chr_id = shd.chr_id
;
update_oki_refresh( p_object_name => 'OKI_K_SALESREPS'
, p_job_run_id => l_job_run_id
, x_retcode => retcode ) ;
raise l_no_update_refresh;
when l_no_update_refresh then
fnd_message.set_name(application => 'OKI'
,name => 'OKI_TABLE_LOAD_FAILURE');
,buff => 'Update of OKI_REFRESHS failed');
l_no_update_refresh EXCEPTION;
DELETE FROM oki_k_salesreps ksr
WHERE contract_id in ( SELECT jrd.chr_id
FROM oki_job_run_dtl jrd
WHERE jrd.job_run_id = l_job_run_id ) ;
l_loc := 'Inserting into ' || l_table_name ;
INSERT INTO OKI_K_SALESREPS(
PARTY_CONTACT_ID
, CONTRACT_ID
, PARTY_ROLE_ID
, CONTACT_ROLE_CODE
, CONTACT_ID
, SALESREP_NAME
, CREATION_DATE
, LAST_UPDATE_DATE
, ATTRIBUTE_CATEGORY
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
) SELECT
ctt.id
, ctt.dnz_chr_id
, ctt.cpl_id
, ctt.cro_code
, ctt.object1_id1
, NULL salesrep_name -- 11510 Changes
, ctt.creation_date
, ctt.last_update_date
, ctt.attribute_category
, ctt.attribute1
, ctt.attribute2
, ctt.attribute3
, ctt.attribute4
, ctt.attribute5
, ctt.attribute6
, ctt.attribute7
, ctt.attribute8
, ctt.attribute9
, ctt.attribute10
, ctt.attribute11
, ctt.attribute12
, ctt.attribute13
, ctt.attribute14
, ctt.attribute15
FROM
oki_sales_k_hdrs shd
, okc_contacts ctt
, oki_job_run_dtl jrd
WHERE 1=1
AND ctt.cro_code = l_salesperson_code -- 11510 Changes
AND ctt.dnz_chr_id = shd.chr_id
and jrd.chr_id = shd.chr_id
and jrd.job_run_id = l_job_run_id
and jrd.action_flag = 'I' ;
update_oki_refresh( p_object_name => l_table_name
, p_job_run_id => l_job_run_id
, x_retcode => l_retcode ) ;
raise l_no_update_refresh;
when l_no_update_refresh then
fnd_message.set_name(application => 'OKI'
,name => 'OKI_TABLE_LOAD_FAILURE');
,buff => 'Update of OKI_REFRESHS failed');
PROCEDURE update_service_line( x_errbuf OUT NOCOPY VARCHAR2
, x_retcode OUT NOCOPY VARCHAR2 ) IS
-- Exception to immediately exit the procedure
l_excp_no_job_run_id EXCEPTION ;
l_no_update_refresh EXCEPTION;
UPDATE /*+ parallel(v) bypass_ujvc */ oki_sold_itm_lines_cpl_v v
SET price_negotiated = cpl_price_negotiated
, sob_price_negotiated = cpl_sob_price_negotiated
, base_price_negotiated = cpl_base_price_negotiated ;
l_loc := ' Succesfully Updated price negotiated for Service Contracts ';
update_oki_refresh( p_object_name => 'OKI_SOLD_ITM_LINES_UPDATE'
, p_job_run_id => l_job_run_id
, x_retcode => x_retcode ) ;
raise l_no_update_refresh;
when l_no_update_refresh then
fnd_message.set_name(application => 'OKI'
,name => 'OKI_TABLE_LOAD_FAILURE');
,buff => 'Update of OKI_REFRESHS failed');
END update_service_line;
PROCEDURE fast_update_service_line( x_errbuf OUT NOCOPY VARCHAR2
, x_retcode OUT NOCOPY VARCHAR2 ) IS
-- Exception to immediately exit the procedure
l_excp_exit_immediate EXCEPTION ;
l_no_update_refresh EXCEPTION;
l_table_name := 'OKI_SOLD_ITM_LINES_UPDATE';
UPDATE OKI_SOLD_ITM_LINES sil
SET (price_negotiated,
sob_price_negotiated,
base_price_negotiated)
= (SELECT
SUM(price_negotiated),
SUM(sob_price_negotiated),
SUM(base_price_negotiated)
FROM oki_cov_prd_lines cpl
where cpl.parent_cle_id = sil.cle_id
)
where sil.chr_id in (select shd.chr_id
from oki_job_run_dtl jrd, oki_sales_k_hdrs shd
where 1 =1
and jrd.action_flag = 'I'
and jrd.job_run_id = l_job_run_id
and jrd.chr_id = shd.chr_id
and shd.application_id = 515);
update_oki_refresh(p_object_name => l_table_name
, p_job_run_id => l_job_run_id
, x_retcode => l_retcode ) ;
raise l_no_update_refresh;
l_loc := ' Succesfully Updated price negotiated for Service Contracts ';
when l_no_update_refresh then
fnd_message.set_name(application => 'OKI'
,name => 'OKI_TABLE_LOAD_FAILURE');
,buff => 'Update of OKI_REFRESHS failed');
END fast_update_service_line;
g_program_update_date := SYSDATE;