[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_select Varchar2(2000);
l_select :='SELECT distinct okh.id,
okh.contract_number,
okh.contract_number_modifier,
okh.start_date,
okh.end_date,
okh.short_description,
okh.sts_code,
oxp.name party
,org.name
,okh.billed_at_source';
SELECT ole.subject_chr_id
FROM okc_operation_instances_v oie,
okc_operation_lines_v ole,
okc_class_operations_v oco
WHERE oie.id = ole.oie_id
AND oie.id = :p_ctr_rec_oie_id
AND oie.cop_id = oco.id AND opn_code = ''MASS_CHANGE'')
AND oas.sts_code = okh.sts_code
AND oas.opn_code = ''UPDATE''
AND oas.scs_code = okh.scs_code
AND ocl.chr_id = okh.id
AND nvl(ocl.upg_orig_system_ref,''X'') <> ''MIG_NOBILL''
AND okh.id = okp.chr_id
AND okp.rle_code in ( ''CUSTOMER'',''SUBSCRIBER'')
AND okp.object1_id1 = oxp.id1
And org.organization_id = okh.authoring_org_id
AND oxp.id2 = ''#''' ;
AND oas.opn_code = ''UPDATE''
AND oas.scs_code = okh.scs_code
AND ocl.chr_id = okh.id
AND nvl(ocl.upg_orig_system_ref,''X'') <> ''MIG_NOBILL''
AND okh.id = okp.chr_id
AND okp.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp.object1_id1 = oxp.id1
And org.organization_id = okh.authoring_org_id
AND oxp.id2 = ''#''';
AND okh.id not in ( SELECT ole.subject_chr_id
FROM okc_operation_instances_v oie,
okc_operation_lines_v ole,
okc_class_operations_v oco
WHERE oie.id = ole.oie_id
AND oie.id = :p_ctr_rec_oie_id
AND oie.cop_id = oco.id AND opn_code = ''MASS_CHANGE'')
AND oas.sts_code = okh.sts_code
AND oas.opn_code = ''UPDATE''
AND oas.scs_code = okh.scs_code
AND ocl.chr_id = okh.id
AND ocl.lse_id <> 14
AND nvl(ocl.upg_orig_system_ref,''X'') <> ''MIG_NOBILL''
AND okh.id = okp.chr_id
AND okp.rle_code in ( ''CUSTOMER'',''SUBSCRIBER'')
AND okp.object1_id1 = oxp.id1
And org.organization_id = okh.authoring_org_id
AND oxp.id2 = ''#''' ;
AND oas.opn_code = ''UPDATE''
AND oas.scs_code = okh.scs_code
AND ocl.chr_id = okh.id
AND ocl.lse_id <> 14
AND nvl(ocl.upg_orig_system_ref,''X'') <> ''MIG_NOBILL''
AND okh.id = okp.chr_id
AND okp.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp.object1_id1 = oxp.id1
And org.organization_id = okh.authoring_org_id
AND oxp.id2 = ''#''';
l_select :='SELECT okh.id,
okh.contract_number,
okh.contract_number_modifier,
okh.start_date,
okh.end_date,
okh.short_description,
okh.sts_code,
okh.qcl_id ,
okh.object_version_number,
ole.id ole_id,
okh.org_id,
mod.qa_check_yn,
org.name ,
okh.billed_at_source';
l_where:=' WHERE ole.select_yn = ''Y''
AND ole.process_flag IN (''A'',''E'')
AND okh.id = ole.subject_chr_id
AND ole.oie_id = :p_ctr_rec_oie_id
AND mod.ole_id = ole.id
And org.organization_id = okh.authoring_org_id ';
IF p_ctr_rec.update_level = 'OKS_K_HEADER' THEN
--------------------------------------------------------
-- Update Level : Contract , Attribute: Revenue Account(REV_ACCT)
--------------------------------------------------------
IF p_ctr_rec.attribute = 'REV_ACCT' THEN
-- Old Value: NULL (-9999)
IF p_ctr_rec.old_value = '-9999' THEN -- For old Value as NULL
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from OKS_REV_DISTRIBUTIONS_V rev
WHERE rev.chr_id = okh.id
AND rev.code_combination_id is NULL)
AND okh.id = to_number(:update_level_value)' ; --||to_number(p_ctr_rec.update_level_value);
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from OKS_REV_DISTRIBUTIONS_V rev
WHERE rev.chr_id = okh.id)
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from OKS_REV_DISTRIBUTIONS_V rev
WHERE rev.chr_id = okh.id
AND rev.code_combination_id = to_number(:p_ctr_rec_old_value))
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' ,NULL old_value' ||
l_from||l_where||
' AND okh.PAYMENT_TERM_ID is NULL
AND okh.id = to_number(:p_ctr_rec_old_value) ';
l_stmt := l_select||' ,okh.payment_term_id old_value' ||
l_from||l_where||
'AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' ,okh.payment_term_id old_value' ||
l_from||l_where||
' AND okh.PAYMENT_TERM_ID = to_number(:p_ctr_rec_old_value)
AND okh.id = to_number(:p_ctr_rec_old_value) ';
l_stmt := l_select||' ,NULL old_value' ||
l_from||l_where||
' AND okh.renewal_type_code is NULL
AND not exists(
select ol.object_chr_id
from okc_operation_lines ol
,okc_operation_instances oi
,okc_class_operations co
WHERE ol.object_chr_id = okh.id
AND ol.process_flag = ''P''
AND ol.ACTIVE_YN = ''Y''
AND oi.id = ol.oie_id
AND oi.cop_id = co.id
AND co.opn_code in (''RENEWAL'',''REN_CON'')
AND co.CLS_CODE = ''SERVICE'')
AND okh.id = to_number(:p_ctr_rec_old_value) ';
l_stmt := l_select||' , :l_old_value old_value' ||
l_from||l_where||
' AND not exists(
select ol.object_chr_id
from okc_operation_lines ol
,okc_operation_instances oi
,okc_class_operations co
WHERE ol.object_chr_id = okh.id
AND ol.process_flag = ''P''
AND ol.ACTIVE_YN = ''Y''
AND oi.id = ol.oie_id
AND oi.cop_id = co.id
AND co.opn_code in (''RENEWAL'',''REN_CON'')
AND co.CLS_CODE = ''SERVICE'')
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value' ||
l_from||', oks_k_headers_b oksh'||
l_where||
' AND oksh.chr_id = okh.id
AND (okh.renewal_type_code = ''NSR''
AND ''ERN'' = :p_ctr_rec_old_value
AND oksh.ELECTRONIC_RENEWAL_FLAG =''Y'')
AND not exists(
select ol.object_chr_id
from okc_operation_lines ol
,okc_operation_instances oi
,okc_class_operations co
WHERE ol.object_chr_id = okh.id
AND ol.process_flag = ''P''
AND ol.ACTIVE_YN = ''Y''
AND oi.id = ol.oie_id
AND oi.cop_id = co.id
AND co.opn_code in (''RENEWAL'',''REN_CON'')
AND co.CLS_CODE = ''SERVICE'')
AND okh.id = to_number(:p_ctr_rec_old_value) ';
l_stmt := l_select||' , :l_old_value old_value' ||
l_from||', oks_k_headers_b oksh'||
l_where||
' AND oksh.chr_id = okh.id
AND okh.renewal_type_code = :p_ctr_rec_old_value
AND nvl(oksh.ELECTRONIC_RENEWAL_FLAG,''N'') <>''Y''
AND not exists(
select ol.object_chr_id
from okc_operation_lines ol
,okc_operation_instances oi
,okc_class_operations co
WHERE ol.object_chr_id = okh.id
AND ol.process_flag = ''P''
AND ol.ACTIVE_YN = ''Y''
AND oi.id = ol.oie_id
AND oi.cop_id = co.id
AND co.opn_code in (''RENEWAL'',''REN_CON'')
AND co.CLS_CODE = ''SERVICE'')
AND okh.id = to_number(:p_ctr_rec_old_value) ';
l_stmt := l_select||' ,NULL old_value' ||
l_from||
l_where|| 'AND exists ( Select dnz_chr_id from okc_k_lines_b cln
Where cln.dnz_chr_id = okh.id
AND cln.lse_id in(3,16,21)
AND cln.price_list_id is NULL)
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value' ||
l_from||
l_where|| ' AND exists ( Select dnz_chr_id from okc_k_lines_b cln
Where cln.dnz_chr_id = okh.id
AND cln.lse_id in(3,16,21))
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value' ||
l_from||
l_where|| ' AND exists ( Select dnz_chr_id from okc_k_lines_b cln
Where cln.dnz_chr_id = okh.id
AND cln.lse_id in(3,16,21)
AND cln.price_list_id = to_number(:p_ctr_rec_old_value))
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' ,NULL old_value' ||
l_from||' ,oks_k_headers_v oksh '||
l_where|| 'AND oksh.chr_id = okh.id
AND oksh.acct_rule_id IS NULL
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' ,oksh.acct_rule_id old_value' ||
l_from||' ,oks_k_headers_v oksh '||
l_where|| 'AND oksh.chr_id = okh.id
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' ,oksh.acct_rule_id old_value' ||
l_from||' ,oks_k_headers_v oksh '||
l_where|| 'AND oksh.chr_id = okh.id
AND oksh.acct_rule_id = to_number(:p_ctr_rec_old_value)
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' ,NULL old_value' ||
l_from||l_where||
' AND okh.inv_rule_id IS NULL
AND okh.id = to_number(:update_level_value) ';
l_stmt := l_select||' ,okh.inv_rule_id old_value' ||
l_from||l_where||
' AND okh.id = to_number(:update_level_value) ';
l_stmt := l_select||' ,okh.inv_rule_id old_value' ||
l_from||l_where||
' AND okh.inv_rule_id = to_number(:p_ctr_rec_old_value)
AND okh.id = to_number(:update_level_value) ';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from OKC_K_LINES_V okl, oks_k_lines_v oksl
WHERE okl.dnz_chr_id = okh.id
AND oksl.cle_id = okl.id
AND oksl.coverage_type is NULL )
AND okh.id = to_number(:update_level_value)' ;
l_stmt := l_select||' , :l_old_value old_value'||
l_from||
l_where||'AND EXISTS (SELECT ''x'' from okc_k_lines_v okl, oks_k_lines_v oksl
WHERE okl.dnz_chr_id = okh.id
AND oksl.cle_id = okl.id )
AND okh.id = to_number(:update_level_value)' ;
l_stmt := l_select||' , :l_old_value old_value'||
l_from||
l_where||'AND EXISTS (SELECT ''x'' from okc_k_lines_v okl, oks_k_lines_v oksl
WHERE okl.dnz_chr_id = okh.id
AND oksl.cle_id = okl.id
AND oksl.coverage_type = :p_ctr_rec_old_value )
AND okh.id = to_number(:update_level_value)' ;
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from oks_coverage_timezones_v ctz
WHERE ctz.dnz_chr_id = okh.id
AND ctz.timezone_id IS NULL )
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||
l_where||'AND EXISTS (SELECT ''x'' from oks_coverage_timezones_v ctz
WHERE ctz.dnz_chr_id = okh.id
AND ctz.timezone_id IS NOT NULL )
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||
l_where||'AND EXISTS (SELECT ''x'' from oks_coverage_timezones_v ctz
WHERE ctz.dnz_chr_id = okh.id
AND ctz.timezone_id = to_number(:p_ctr_rec_old_value))
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||
l_where||'AND EXISTS (SELECT ''x'' from
okc_contacts oco
WHERE oco.dnz_chr_id = okh.id
AND oco.cro_code = ''ENGINEER''
AND oco.jtot_object1_code = ''OKX_RESOURCE'')
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||
l_where||'AND EXISTS (SELECT ''x'' from
okc_contacts oco
WHERE oco.dnz_chr_id = okh.id
AND oco.cro_code = ''ENGINEER''
AND oco.jtot_object1_code = ''OKX_RESOURCE''
AND oco.object1_id1 = :p_ctr_rec_old_value)
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||
l_where||'AND EXISTS (SELECT ''x'' from
okc_contacts oco
WHERE oco.dnz_chr_id = okh.id
AND oco.cro_code = ''RSC_GROUP''
AND oco.jtot_object1_code = ''OKS_RSCGROUP'')
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||
l_where||'AND EXISTS (SELECT ''x'' from
okc_contacts oco
WHERE oco.dnz_chr_id = okh.id
AND oco.cro_code = ''RSC_GROUP''
AND oco.jtot_object1_code = ''OKS_RSCGROUP''
AND oco.object1_id1 = :p_ctr_rec_old_value)
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||
l_where||'AND EXISTS (SELECT ''x'' from okc_governances_v ogv
WHERE ogv.dnz_chr_id = okh.id
AND ogv.isa_agreement_id = :p_ctr_rec_old_value)
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from OKC_K_LINES_V cle
WHERE cle.dnz_chr_id = okh.id
AND cle.lse_id IN (7,8,9,10,11,18,25,35)
AND cle.cognomen is NULL)
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from OKC_K_LINES_V cle
WHERE cle.dnz_chr_id = okh.id
AND cle.lse_id IN (7,8,9,10,11,18,25,35))
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from OKC_K_LINES_V cle
WHERE cle.dnz_chr_id = okh.id
AND cle.lse_id IN (7,8,9,10,11,18,25,35)
AND cle.cognomen = :p_ctr_rec_old_value)
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from OKC_K_LINES_V cle
WHERE cle.chr_id = okh.id
AND cle.lse_id IN (1,12,14,19)
AND cle.cognomen is NULL)
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from OKC_K_LINES_V cle
WHERE cle.chr_id = okh.id
AND cle.lse_id IN (1,12,14,19))
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from OKC_K_LINES_V cle
WHERE cle.chr_id = okh.id
AND cle.lse_id IN (1,12,14,19)
AND cle.cognomen = :p_ctr_rec_old_value)
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' ,NULL old_value' ||
l_from||l_where||
' AND okh.ship_to_site_use_id is NULL
AND okh.id = to_number(:p_ctr_rec_old_value) ';
l_stmt := l_select||' ,okh.ship_to_site_use_id old_value' ||
l_from||l_where||
' AND okh.ship_to_site_use_id = to_number(:p_ctr_rec_old_value)
AND okh.id = to_number(:p_ctr_rec_old_value) ';
l_stmt := l_select||' ,NULL old_value' ||
l_from||l_where||
' AND okh.bill_to_site_use_id is NULL
AND okh.id = to_number(:p_ctr_rec_old_value) ';
l_stmt := l_select||' ,okh.bill_to_site_use_id old_value' ||
l_from||l_where||
' AND okh.bill_to_site_use_id = to_number(:p_ctr_rec_old_value)
AND okh.id = to_number(:p_ctr_rec_old_value) ';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND (EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''SALESPERSON''
and oc.object1_id1 is null
and oc.jtot_object1_code = ''OKX_SALEPERS'')
OR EXISTS (Select ''x'' from oks_k_sales_credits_v osc
Where osc.chr_id = okh.id
and osc.ctc_id is NULL))
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' ,oc.object1_id1 old_value '||
l_from||', okc_contacts oc '||
l_where||' and oc.dnz_chr_id = okh.id
and oc.cro_code = ''SALESPERSON''
and oc.jtot_object1_code = ''OKX_SALEPERS''
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
' AND (EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''SALESPERSON''
and oc.object1_id1=:p_ctr_rec_old_value
and oc.jtot_object1_code = ''OKX_SALEPERS'')
OR EXISTS (Select ''x'' from oks_k_sales_credits_v osc
Where osc.chr_id = okh.id
and osc.ctc_id = to_number(:p_ctr_rec_old_value)))
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''SHIPPING''
and oc.object1_id1 is null
and oc.jtot_object1_code = ''OKX_PCONTACT'')
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' ,oc.object1_id1 old_value'||
l_from||', okc_contacts oc '||
l_where||
' and oc.dnz_chr_id = okh.id
and oc.cro_code = ''SHIPPING''
and oc.jtot_object1_code = ''OKX_PCONTACT'')
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''SHIPPING''
and oc.object1_id1=:p_ctr_rec_old_value
and oc.jtot_object1_code = ''OKX_PCONTACT'')
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''BILLING''
and oc.object1_id1 is null
and oc.jtot_object1_code = ''OKX_PCONTACT'')
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' ,oc.object1_id1 old_value'||
l_from||', okc_contacts oc '||
l_where||
' and oc.dnz_chr_id = okh.id
and oc.cro_code = ''BILLING''
and oc.jtot_object1_code = ''OKX_PCONTACT'')
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''BILLING''
and oc.object1_id1=:p_ctr_rec_old_value
and oc.jtot_object1_code = ''OKX_PCONTACT'')
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''CUST_SHIPPING''
and oc.object1_id1 is null
and oc.jtot_object1_code = ''OKX_CONTSHIP'')
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' ,oc.object1_id1 old_value'||
l_from||', okc_contacts oc '||
l_where||
' and oc.dnz_chr_id = okh.id
and oc.cro_code = ''CUST_SHIPPING''
and oc.jtot_object1_code = ''OKX_CONTSHIP'')
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''CUST_SHIPPING''
and oc.object1_id1=:p_ctr_rec_old_value
and oc.jtot_object1_code = ''OKX_CONTSHIP'')
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''CUST_BILLING''
and oc.object1_id1 is null
and oc.jtot_object1_code = ''OKX_CONTBILL'')
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' ,oc.object1_id1 old_value'||
l_from||', okc_contacts oc '||
l_where||
' and oc.dnz_chr_id = okh.id
and oc.cro_code = ''CUST_BILLING''
and oc.jtot_object1_code = ''OKX_CONTBILL'')
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''CUST_BILLING''
and oc.object1_id1 = :p_ctr_rec_old_value
and oc.jtot_object1_code = ''OKX_CONTBILL'')
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from oks_coverage_times_v oct
where oct.dnz_chr_id = okh.id
and oct.start_hour is null
and oct.start_minute is null)
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from oks_coverage_times_v oct
where oct.dnz_chr_id = okh.id
and oct.start_hour = trunc(to_number(:p_ctr_rec_old_value)/60)
and oct.start_minute = mod(to_number(:p_ctr_rec_old_value),60))
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from oks_coverage_times_v oct
where oct.dnz_chr_id = okh.id
and oct.end_hour is null
and oct.end_minute is null)
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from oks_coverage_times_v oct
where oct.dnz_chr_id = okh.id
and oct.end_hour = trunc(to_number(:p_ctr_rec_old_value)/60)
and oct.end_minute = mod(to_number(:p_ctr_rec_old_value),60))
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||', NULL old_value'||
l_from||',okc_k_lines_b kl, oks_action_time_types_v att , oks_action_times_v oat '|| l_where||
' AND kl.dnz_chr_id = okh.id
AND kl.id = att.cle_id
And kl.lse_id in (4,17,22)
AND att.action_type_code = ''RSN''
AND att.id = oat.cov_action_type_id
AND ( sun_duration IS NULL
OR mon_duration IS NULL
OR tue_duration IS NULL
OR wed_duration IS NULL
OR thu_duration IS NULL
OR fri_duration IS NULL
OR sat_duration IS NULL)
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||',okc_k_lines_b kl, oks_action_time_types_v att , oks_action_times_v oat '||l_where||
' AND kl.dnz_chr_id = okh.id
AND kl.id = att.cle_id
And kl.lse_id in (4,17,22)
AND att.action_type_code = ''RSN''
AND att.id = oat.cov_action_type_id
AND ( sun_duration = :p_ctr_rec_old_value
OR mon_duration = :p_ctr_rec_old_value
OR tue_duration = :p_ctr_rec_old_value
OR wed_duration = :p_ctr_rec_old_value
OR thu_duration = :p_ctr_rec_old_value
OR fri_duration = :p_ctr_rec_old_value
OR sat_duration = :p_ctr_rec_old_value)
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||', NULL old_value'||
l_from||', okc_k_lines_b kl, oks_action_time_types_v att , oks_action_times_v oat '||l_where||
' AND kl.dnz_chr_id = okh.id
AND kl.id = att.cle_id
And kl.lse_id in (4,17,22)
AND att.action_type_code = ''RCN''
AND att.id = oat.cov_action_type_id
AND ( sun_duration IS NULL
OR mon_duration IS NULL
OR tue_duration IS NULL
OR wed_duration IS NULL
OR thu_duration IS NULL
OR fri_duration IS NULL
OR sat_duration IS NULL)
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||', okc_k_lines_b kl, oks_action_time_types_v att , oks_action_times_v oat '||l_where||
' AND kl.dnz_chr_id = okh.id
AND kl.id = att.cle_id
And kl.lse_id in (4,17,22)
AND att.action_type_code = ''RCN''
AND att.id = oat.cov_action_type_id
AND ( sun_duration = :p_ctr_rec_old_value
OR mon_duration = :p_ctr_rec_old_value
OR tue_duration = :p_ctr_rec_old_value
OR wed_duration = :p_ctr_rec_old_value
OR thu_duration = :p_ctr_rec_old_value
OR fri_duration = :p_ctr_rec_old_value
OR sat_duration = :p_ctr_rec_old_value)
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND okh.price_list_id is NULL
AND okh.id = to_number(:update_level_value) ';
l_stmt := l_select||' ,okh.price_list_id old_value' ||
l_from||l_where||
' AND okh.id = to_number(:update_level_value) ';
l_stmt := l_select||' ,okh.price_list_id old_value' ||
l_from||l_where||
' AND okh.price_list_id = to_number(:p_ctr_rec_old_value)
AND okh.id = to_number(:update_level_value) ';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND okh.cognomen is null
AND okh.id = :update_level_value ' ;
l_stmt := l_select||' ,okh.cognomen old_value'||
l_from||l_where||
' AND okh.id = :update_level_value ' ;
l_stmt := l_select||', okh.cognomen old_value'||
l_from||l_where||
' AND okh.cognomen = :p_ctr_rec_old_value
AND okh.id = :update_level_value';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND okh.cust_po_number is null
AND ( okh.payment_instruction_type Is Null Or okh.payment_instruction_type = ''PON'')
AND okh.id = :update_level_value ' ;
l_stmt := l_select||' ,okh.cust_po_number old_value'||
l_from||l_where||
' AND okh.payment_instruction_type = ''PON''
AND okh.id = :update_level_value And okh.cust_po_number_req_yn <> ''Y''' ;
l_stmt := l_select||' ,okh.cust_po_number old_value'||
l_from||l_where||
' AND okh.payment_instruction_type = ''PON''
AND okh.id = :update_level_value ' ;
l_stmt := l_select||', okh.cust_po_number old_value'||
l_from||l_where||
' AND okh.cust_po_number = :p_ctr_rec_old_value
AND okh.payment_instruction_type = ''PON''
AND okh.id = :update_level_value And okh.cust_po_number_req_yn <>
''Y''';
l_stmt := l_select||', okh.cust_po_number old_value'||
l_from||l_where||
' AND okh.cust_po_number = :p_ctr_rec_old_value
AND okh.payment_instruction_type = ''PON''
AND okh.id = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
'AND okh.id = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||' ,oks_k_headers_v oksh '||
l_where||' AND oksh.chr_id = okh.id
AND nvl(oksh.renewal_po_required,''N'') = :p_ctr_rec_old_value
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||' ,oks_k_headers_v oksh '||
l_where||' AND oksh.chr_id = okh.id
AND okh.id = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||' ,oks_k_headers_v oksh '||
l_where||' AND oksh.chr_id = okh.id
AND nvl(oksh.inv_print_profile,''N'') = :p_ctr_rec_old_value
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' ,okg.cgp_parent_id old_value'||
l_from||',okc_k_groups_grpings_v okg'||
l_where||' AND okg.included_chr_id = okh.id
AND okg.cgp_parent_id is NULL
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' ,okg.cgp_parent_id old_value'||
l_from||',okc_k_groups_grpings_v okg'||
l_where||' AND okg.included_chr_id = okh.id
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||' ,okg.cgp_parent_id old_value'||
l_from||',okc_k_groups_grpings_v okg'||
l_where||' AND okg.included_chr_id = okh.id
AND okg.cgp_parent_id = to_number(:p_ctr_rec_old_value)
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||',to_char(okh.start_date) old_value'||
l_from||
l_where||' AND okh.start_date is NULL
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||',to_char(okh.start_date) old_value'||
l_from||
l_where||' AND trunc(okh.start_date) = trunc(to_date(:p_ctr_rec_old_value,''YYYY/MM/DD HH24:MI:SS''))
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||',to_char(okh.end_date) old_value'||
l_from||
l_where||' AND okh.end_date is NULL
AND okh.id = to_number(:update_level_value)';
l_stmt := l_select||',to_char(okh.end_date) old_value'||
l_from||
l_where||' AND trunc(okh.end_date) = trunc(to_date(:p_ctr_rec_old_value,''YYYY/MM/DD HH24:MI:SS''))
AND okh.id = to_number(:update_level_value)';
ELSIF p_ctr_rec.update_level = 'OKX_OPERUNIT' THEN --'ORGANIZATION' THEN
---------------------------------------------------------
-- Update Level : Organization , Attribute: Revenue Account (REV_ACCT)
--------------------------------------------------------
IF p_ctr_rec.attribute = 'REV_ACCT' THEN
-- Old Value: NULL (-9999)
IF p_ctr_rec.old_value = '-9999' THEN -- For old Value as NULL
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from OKS_REV_DISTRIBUTIONS_V rev
WHERE rev.chr_id = okh.id
AND rev.code_combination_id is NULL)
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from OKS_REV_DISTRIBUTIONS_V rev
WHERE rev.chr_id = okh.id)
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from OKS_REV_DISTRIBUTIONS_V rev
WHERE rev.chr_id = okh.id
AND rev.code_combination_id = to_number(:p_ctr_rec_old_value))
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,NULL old_value' ||
l_from||l_where||
' AND okh.PAYMENT_TERM_ID is NULL
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,okh.payment_term_id old_value' ||
l_from||l_where||
'AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,okh.payment_term_id old_value' ||
l_from||l_where||
' AND okh.payment_term_id = to_number(:p_ctr_rec_old_value)
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,NULL old_value' ||
l_from||
l_where||
' AND okh.renewal_type_code is NULL
AND not exists(
select ol.object_chr_id
from okc_operation_lines ol
,okc_operation_instances oi
,okc_class_operations co
WHERE ol.object_chr_id = okh.id
AND ol.process_flag = ''P''
AND ol.ACTIVE_YN = ''Y''
AND oi.id = ol.oie_id
AND oi.cop_id = co.id
AND co.opn_code in (''RENEWAL'',''REN_CON'')
AND co.CLS_CODE = ''SERVICE'')
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value' ||
l_from||
l_where||
' AND not exists(
select ol.object_chr_id
from okc_operation_lines ol
,okc_operation_instances oi
,okc_class_operations co
WHERE ol.object_chr_id = okh.id
AND ol.process_flag = ''P''
AND ol.ACTIVE_YN = ''Y''
AND oi.id = ol.oie_id
AND oi.cop_id = co.id
AND co.opn_code in (''RENEWAL'',''REN_CON'')
AND co.CLS_CODE = ''SERVICE'')
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value' ||
l_from||' ,oks_k_headers_b oksh'||
l_where||
' AND oksh.chr_id = okh.id
AND okh.renewal_type_code = ''NSR''
AND ''ERN'' = :p_ctr_rec_old_value
AND oksh.ELECTRONIC_RENEWAL_FLAG =''Y''
AND not exists(
select ol.object_chr_id
from okc_operation_lines ol
,okc_operation_instances oi
,okc_class_operations co
WHERE ol.object_chr_id = okh.id
AND ol.process_flag = ''P''
AND ol.ACTIVE_YN = ''Y''
AND oi.id = ol.oie_id
AND oi.cop_id = co.id
AND co.opn_code in (''RENEWAL'',''REN_CON'')
AND co.CLS_CODE = ''SERVICE'')
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value' ||
l_from||' ,oks_k_headers_b oksh'||
l_where||
' AND oksh.chr_id = okh.id
AND okh.renewal_type_code = :p_ctr_rec_old_value
AND nvl(oksh.ELECTRONIC_RENEWAL_FLAG,''N'') <> ''Y''
AND not exists(
select ol.object_chr_id
from okc_operation_lines ol
,okc_operation_instances oi
,okc_class_operations co
WHERE ol.object_chr_id = okh.id
AND ol.process_flag = ''P''
AND ol.ACTIVE_YN = ''Y''
AND oi.id = ol.oie_id
AND oi.cop_id = co.id
AND co.opn_code in (''RENEWAL'',''REN_CON'')
AND co.CLS_CODE = ''SERVICE'')
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,NULL old_value' ||
l_from||
l_where|| 'AND exists ( Select dnz_chr_id from okc_k_lines_b cln
Where cln.dnz_chr_id = okh.id
AND cln.lse_id in(3,16,21)
AND cln.price_list_id is NULL)
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value' ||
l_from||
l_where|| ' AND exists ( Select dnz_chr_id from okc_k_lines_b cln
Where cln.dnz_chr_id = okh.id
AND cln.lse_id in(3,16,21))
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value' ||
l_from||
l_where|| ' AND exists ( Select dnz_chr_id from okc_k_lines_b cln
Where cln.dnz_chr_id = okh.id
AND cln.lse_id in(3,16,21)
AND cln.price_list_id = to_number(:p_ctr_rec_old_value))
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,NULL old_value' ||
l_from||' ,oks_k_headers_v oksh '||
l_where|| 'AND oksh.chr_id = okh.id
AND oksh.acct_rule_id IS NULL
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,oksh.acct_rule_id old_value' ||
l_from||' ,oks_k_headers_v oksh '||
l_where|| 'AND oksh.chr_id = okh.id
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,oksh.acct_rule_id old_value' ||
l_from||' ,oks_k_headers_v oksh '||
l_where|| 'AND oksh.chr_id = okh.id
AND oksh.acct_rule_id = to_number(:p_ctr_rec_old_value)
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,NULL old_value' ||
l_from||l_where||
' AND okh.inv_rule_id IS NULL
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,okh.inv_rule_id old_value' ||
l_from||l_where||
' AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,okh.inv_rule_id old_value' ||
l_from||l_where||
' AND okh.inv_rule_id = to_number(:p_ctr_rec_old_value)
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from OKC_K_LINES_V okl, oks_k_lines_v oksl
WHERE okl.dnz_chr_id = okh.id
AND oksl.cle_id = okl.id
AND oksl.coverage_type is NULL )
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||
l_where||'AND EXISTS (SELECT ''x'' from okc_k_lines_v okl, oks_k_lines_v oksl
WHERE okl.dnz_chr_id = okh.id
AND oksl.cle_id = okl.id )
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||
l_where||'AND EXISTS (SELECT ''x'' from okc_k_lines_v okl, oks_k_lines_v oksl
WHERE okl.dnz_chr_id = okh.id
AND oksl.cle_id = okl.id
AND oksl.coverage_type = :p_ctr_rec_old_value )
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from oks_coverage_timezones_v ctz
WHERE ctz.dnz_chr_id = okh.id
AND ctz.timezone_id IS NULL )
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||
l_where||'AND EXISTS (SELECT ''x'' from oks_coverage_timezones_v ctz
WHERE ctz.dnz_chr_id = okh.id
AND ctz.timezone_id IS NOT NULL )
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||
l_where||'AND EXISTS (SELECT ''x'' from oks_coverage_timezones_v ctz
WHERE ctz.dnz_chr_id = okh.id
AND ctz.timezone_id = to_number(:p_ctr_rec_old_value))
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||
l_where||'AND EXISTS (SELECT ''x'' from
okc_contacts oco
WHERE oco.dnz_chr_id = okh.id
AND oco.cro_code = ''ENGINEER''
AND oco.jtot_object1_code = ''OKX_RESOURCE'')
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||
l_where||'AND EXISTS (SELECT ''x'' from
okc_contacts oco
WHERE oco.dnz_chr_id = okh.id
AND oco.cro_code = ''ENGINEER''
AND oco.jtot_object1_code = ''OKX_RESOURCE''
AND oco.object1_id1 = :p_ctr_rec_old_value)
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||
l_where||'AND EXISTS (SELECT ''x'' from
okc_contacts oco
WHERE oco.dnz_chr_id = okh.id
AND oco.cro_code = ''RSC_GROUP''
AND oco.jtot_object1_code = ''OKS_RSCGROUP'')
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||
l_where||'AND EXISTS (SELECT ''x'' from
okc_contacts oco
WHERE oco.dnz_chr_id = okh.id
AND oco.cro_code = ''RSC_GROUP''
AND oco.jtot_object1_code = ''OKS_RSCGROUP''
AND oco.object1_id1 = :p_ctr_rec_old_value)
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||
l_where||'AND EXISTS (SELECT ''x'' from okc_governances_v ogv
WHERE ogv.dnz_chr_id = okh.id
AND ogv.isa_agreement_id = :p_ctr_rec_old_value)
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from OKC_K_LINES_V cle
WHERE cle.dnz_chr_id = okh.id
AND cle.lse_id IN (7,8,9,10,11,18,25,35)
AND cle.cognomen is NULL)
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from OKC_K_LINES_V cle
WHERE cle.dnz_chr_id = okh.id
AND cle.lse_id IN (7,8,9,10,11,18,25,35))
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from OKC_K_LINES_V cle
WHERE cle.dnz_chr_id = okh.id
AND cle.lse_id IN (7,8,9,10,11,18,25,35)
AND cle.cognomen = :p_ctr_rec_old_value)
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from OKC_K_LINES_V cle
WHERE cle.chr_id = okh.id
AND cle.lse_id IN (1,12,14,19)
AND cle.cognomen is NULL)
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from OKC_K_LINES_V cle
WHERE cle.chr_id = okh.id
AND cle.lse_id IN (1,12,14,19))
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from OKC_K_LINES_V cle
WHERE cle.chr_id = okh.id
AND cle.lse_id IN (1,12,14,19)
AND cle.cognomen = :p_ctr_rec_old_value)
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,NULL old_value' ||
l_from||l_where||
' AND okh.ship_to_site_use_id is NULL
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,okh.ship_to_site_use_id old_value' ||
l_from||l_where||
' AND okh.ship_to_site_use_id = to_number(:p_ctr_rec_old_value)
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,NULL old_value' ||
l_from||l_where||
' AND okh.bill_to_site_use_id is NULL
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,okh.bill_to_site_use_id old_value' ||
l_from||l_where||
' AND okh.bill_to_site_use_id = to_number(:p_ctr_rec_old_value)
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''SALESPERSON''
and oc.object1_id1 is null
and oc.jtot_object1_code = ''OKX_SALEPERS'')
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,oc.object1_id1 old_value '||
l_from||', okc_contacts oc '||
l_where||' and oc.dnz_chr_id = okh.id
and oc.cro_code = ''SALESPERSON''
and oc.jtot_object1_code = ''OKX_SALEPERS''
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
' AND (EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''SALESPERSON''
and oc.object1_id1= :p_ctr_rec_old_value
and oc.jtot_object1_code = ''OKX_SALEPERS'')
OR EXISTS (Select ''x'' from oks_k_sales_credits_v osc
Where osc.chr_id = okh.id
and osc.ctc_id = to_number(:p_ctr_rec_old_value)))
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''SHIPPING''
and oc.object1_id1 is null
and oc.jtot_object1_code = ''OKX_PCONTACT'')
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,oc.object1_id1 old_value'||
l_from||', okc_contacts oc '||
l_where||
' and oc.dnz_chr_id = okh.id
and oc.cro_code = ''SHIPPING''
and oc.jtot_object1_code = ''OKX_PCONTACT'')
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''SHIPPING''
and oc.object1_id1= :p_ctr_rec_old_value
and oc.jtot_object1_code = ''OKX_PCONTACT'')
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''BILLING''
and oc.object1_id1 is null
and oc.jtot_object1_code = ''OKX_PCONTACT'')
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,oc.object1_id1 old_value'||
l_from||', okc_contacts oc '||
l_where||
' and oc.dnz_chr_id = okh.id
and oc.cro_code = ''BILLING''
and oc.jtot_object1_code = ''OKX_PCONTACT'')
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''BILLING''
and oc.object1_id1= :p_ctr_rec_old_value
and oc.jtot_object1_code = ''OKX_PCONTACT'')
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''CUST_SHIPPING''
and oc.object1_id1 is null
and oc.jtot_object1_code = ''OKX_CONTSHIP'')
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,oc.object1_id1 old_value'||
l_from||', okc_contacts oc '||
l_where||
' and oc.dnz_chr_id = okh.id
and oc.cro_code = ''CUST_SHIPPING''
and oc.jtot_object1_code = ''OKX_CONTSHIP'')
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''CUST_SHIPPING''
and oc.object1_id1= :p_ctr_rec_old_value
and oc.jtot_object1_code = ''OKX_CONTSHIP'')
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''CUST_BILLING''
and oc.object1_id1 is null
and oc.jtot_object1_code = ''OKX_CONTBILL'')
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,oc.object1_id1 old_value'||
l_from||', okc_contacts oc '||
l_where||
' and oc.dnz_chr_id = okh.id
and oc.cro_code = ''CUST_BILLING''
and oc.jtot_object1_code = ''OKX_CONTBILL'')
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''CUST_BILLING''
and oc.object1_id1= :p_ctr_rec_old_value
and oc.jtot_object1_code = ''OKX_CONTBILL'')
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from oks_coverage_times_v oct
where oct.dnz_chr_id = okh.id
and oct.start_hour is null
and oct.start_minute is null)
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from oks_coverage_times_v oct
where oct.dnz_chr_id = okh.id
and oct.start_hour = trunc(to_number(:p_ctr_rec_old_value)/60)
and oct.start_minute = mod(to_number(:p_ctr_rec_old_value),60))
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from oks_coverage_times_v oct
where oct.dnz_chr_id = okh.id
and oct.end_hour is null
and oct.end_minute is null)
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from oks_coverage_times_v oct
where oct.dnz_chr_id = okh.id
and oct.end_hour = trunc(to_number(:p_ctr_rec_old_value)/60)
and oct.end_minute = mod(to_number(:p_ctr_rec_old_value),60))
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||', NULL old_value'||
l_from||',okc_k_lines_b kl, oks_action_time_types_v att , oks_action_times_v oat '|| l_where||
' AND kl.dnz_chr_id = okh.id
AND kl.id = att.cle_id
And kl.lse_id in (4,17,22)
AND att.action_type_code = ''RSN''
AND att.id = oat.cov_action_type_id
AND ( sun_duration IS NULL
OR mon_duration IS NULL
OR tue_duration IS NULL
OR wed_duration IS NULL
OR thu_duration IS NULL
OR fri_duration IS NULL
OR sat_duration IS NULL)
AND okh.authoring_org_id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||',okc_k_lines_b kl, oks_action_time_types_v att , oks_action_times_v oat '||l_where||
' AND kl.dnz_chr_id = okh.id
AND kl.id = att.cle_id
And kl.lse_id in (4,17,22)
AND att.action_type_code = ''RSN''
AND att.id = oat.cov_action_type_id
AND ( sun_duration = :p_ctr_rec_old_value
OR mon_duration = :p_ctr_rec_old_value
OR tue_duration = :p_ctr_rec_old_value
OR wed_duration = :p_ctr_rec_old_value
OR thu_duration = :p_ctr_rec_old_value
OR fri_duration = :p_ctr_rec_old_value
OR sat_duration = :p_ctr_rec_old_value)
AND okh.authoring_org_id = to_number(:update_level_value)';
l_stmt := l_select||', NULL old_value'||
l_from||',okc_k_lines_b kl, oks_action_time_types_v att , oks_action_times_v oat '||l_where||
' AND kl.dnz_chr_id = okh.id
AND kl.id = att.cle_id
And kl.lse_id in (4,17,22)
AND att.action_type_code = ''RCN''
AND att.id = oat.cov_action_type_id
AND ( sun_duration IS NULL
OR mon_duration IS NULL
OR tue_duration IS NULL
OR wed_duration IS NULL
OR thu_duration IS NULL
OR fri_duration IS NULL
OR sat_duration IS NULL)
AND okh.authoring_org_id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||',okc_k_lines_b kl, oks_action_time_types_v att , oks_action_times_v oat '||l_where||
' AND kl.dnz_chr_id = okh.id
AND kl.id = att.cle_id
And kl.lse_id in (4,17,22)
AND att.action_type_code = ''RCN''
AND att.id = oat.cov_action_type_id
AND ( sun_duration = :p_ctr_rec_old_value
OR mon_duration = :p_ctr_rec_old_value
OR tue_duration = :p_ctr_rec_old_value
OR wed_duration = :p_ctr_rec_old_value
OR thu_duration = :p_ctr_rec_old_value
OR fri_duration = :p_ctr_rec_old_value
OR sat_duration = :p_ctr_rec_old_value)
AND okh.authoring_org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND okh.price_list_id is NULL
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,okh.price_list_id old_value' ||
l_from||l_where||
' AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,okh.price_list_id old_value' ||
l_from||l_where||
' AND okh.price_list_id = to_number(:p_ctr_rec_old_value)
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,NULL old_value '||
l_from||l_where||
'AND okh.cognomen IS NULL
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,okh.cognomen old_value'||
l_from||
l_where||' AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||', okh.cognomen old_value'||
l_from||
l_where||' AND okh.cognomen = :p_ctr_rec_old_value
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,NULL old_value '||
l_from||l_where||
'AND okh.cust_po_number IS NULL
AND ( okh.payment_instruction_type Is Null Or okh.payment_instruction_type = ''PON'')
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,okh.cust_po_number old_value'||
l_from||
l_where||' AND okh.org_id = to_number(:update_level_value) AND okh.payment_instruction_type = ''PON''
and okh.cust_po_number_req_yn <> ''Y''';
l_stmt := l_select||' ,okh.cust_po_number old_value'||
l_from||
l_where||' AND okh.payment_instruction_type = ''PON'' AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||', okh.cust_po_number old_value'||
l_from||
l_where||' AND okh.cust_po_number = :p_ctr_rec_old_value
AND okh.payment_instruction_type = ''PON'' AND okh.org_id = to_number(:update_level_value) and okh.cust_po_number_req_yn <> ''Y''';
l_stmt := l_select||', okh.cust_po_number old_value'||
l_from||
l_where||' AND okh.cust_po_number = :p_ctr_rec_old_value
AND okh.payment_instruction_type = ''PON'' AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
'AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||' ,oks_k_headers_v oksh '||
l_where||' AND oksh.chr_id = okh.id
AND nvl(oksh.renewal_po_required,''N'') = :p_ctr_rec_old_value
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||' ,oks_k_headers_v oksh '||
l_where||' AND oksh.chr_id = okh.id
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||' ,oks_k_headers_v oksh '||
l_where||' AND oksh.chr_id = okh.id
AND nvl(oksh.inv_print_profile,''N'') = :p_ctr_rec_old_value
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,okg.cgp_parent_id old_value'||
l_from||',okc_k_groups_grpings_v okg'||
l_where||' AND okg.included_chr_id = okh.id
AND okg.cgp_parent_id is NULL
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,okg.cgp_parent_id old_value'||
l_from||',okc_k_groups_grpings_v okg'||
l_where||' AND okg.included_chr_id = okh.id
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' ,okg.cgp_parent_id old_value'||
l_from||',okc_k_groups_grpings_v okg'||
l_where||' AND okg.included_chr_id = okh.id
AND okg.cgp_parent_id = to_number(:p_ctr_rec_old_value)
AND okh.org_id = to_number(:update_level_value)';
l_stmt:= l_select||',to_char(okh.start_date) old_value '||
l_from||
l_where||' AND okh.start_date is NULL
AND okh.org_id = to_number(:update_level_value)';
l_stmt:= l_select||',to_char(okh.start_date) old_value '||
l_from||
l_where||' AND trunc(okh.start_date) = trunc(to_date(:p_ctr_rec_old_value,''YYYY/MM/DD HH24:MI:SS''))
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||',to_char(okh.end_date) old_value'||
l_from||
l_where||' AND okh.end_date is NULL
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||',to_char(okh.end_date) old_value'||
l_from||
l_where||' AND trunc(okh.end_date) = trunc(to_date(:p_ctr_rec_old_value,''YYYY/MM/DD HH24:MI:SS''))
AND okh.org_id = to_number(:update_level_value)';
ELSIF p_ctr_rec.update_level = 'OKX_PARTY' THEN
--------------------------------------------------------
-- Update Level : Party , Attribute: Revenue Account(REV_ACCT)
--------------------------------------------------------
IF p_ctr_rec.attribute = 'REV_ACCT' THEN
IF p_ctr_rec.old_value = '-9999' THEN -- For old Value as NULL
l_stmt := l_select||' ,NULL old_value'||
l_from||' ,okc_k_party_roles_b okp1 '||l_where||
' AND EXISTS (SELECT ''x'' from OKS_REV_DISTRIBUTIONS_V rev
WHERE rev.chr_id = okh.id
AND rev.code_combination_id is NULL)
AND okh.id = okp1.chr_id
AND okp1.rle_code in ( ''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' ,NULL old_value'||
l_from||' ,okc_k_party_roles_b okp1 '||l_where||
' AND EXISTS (SELECT ''x'' from OKS_REV_DISTRIBUTIONS_V rev
WHERE rev.chr_id = okh.id)
AND okh.id = okp1.chr_id
AND okp1.rle_code in ( ''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||' ,okc_k_party_roles_b okp1 '||
l_where||' AND EXISTS (SELECT ''x'' from OKS_REV_DISTRIBUTIONS_V rev
WHERE rev.chr_id = okh.id
AND rev.code_combination_id = to_number(:p_ctr_rec_old_value))
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' ,NULL old_value' ||
l_from|| ' ,okc_k_party_roles_b okp1 '||
l_where||' AND okh.PAYMENT_TERM_ID is NULL
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value ' ;
l_stmt := l_select||' ,okh.payment_term_id old_value' ||
l_from||' ,okc_k_party_roles_b okp1 '||
l_where||'AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value ' ;
l_stmt := l_select||' ,okh.payment_term_id old_value' ||
l_from||' ,okc_k_party_roles_b okp1 '||
l_where|| ' AND okh.payment_term_id = to_number(:p_ctr_rec_old_value)
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value ' ;
l_stmt := l_select||' ,NULL old_value' ||
l_from||' ,okc_k_party_roles_b okp1 '||
l_where||
' AND okh.renewal_type_code is NULL
AND not exists(
select ol.object_chr_id
from okc_operation_lines ol
,okc_operation_instances oi
,okc_class_operations co
WHERE ol.object_chr_id = okh.id
AND ol.process_flag = ''P''
AND ol.ACTIVE_YN = ''Y''
AND oi.id = ol.oie_id
AND oi.cop_id = co.id
AND co.opn_code in (''RENEWAL'',''REN_CON'')
AND co.CLS_CODE = ''SERVICE'')
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value ' ;
l_stmt := l_select||' , :l_old_value old_value' ||
l_from||' ,okc_k_party_roles_b okp1 '||
l_where|| 'AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND not exists(
select ol.object_chr_id
from okc_operation_lines ol
,okc_operation_instances oi
,okc_class_operations co
WHERE ol.object_chr_id = okh.id
AND ol.process_flag = ''P''
AND ol.ACTIVE_YN = ''Y''
AND oi.id = ol.oie_id
AND oi.cop_id = co.id
AND co.opn_code in (''RENEWAL'',''REN_CON'')
AND co.CLS_CODE = ''SERVICE'')
AND okp1.object1_id1 = :update_level_value ' ;
l_stmt := l_select||' , :l_old_value old_value' ||
l_from||' ,oks_k_headers_b oksh ,okc_k_party_roles_b okp1'||
l_where||
' AND oksh.chr_id = okh.id
AND okh.renewal_type_code = ''NSR''
AND ''ERN'' = :p_ctr_rec_old_value
AND oksh.ELECTRONIC_RENEWAL_FLAG =''Y''
AND not exists(
select ol.object_chr_id
from okc_operation_lines ol
,okc_operation_instances oi
,okc_class_operations co
WHERE ol.object_chr_id = okh.id
AND ol.process_flag = ''P''
AND ol.ACTIVE_YN = ''Y''
AND oi.id = ol.oie_id
AND oi.cop_id = co.id
AND co.opn_code in (''RENEWAL'',''REN_CON'')
AND co.CLS_CODE = ''SERVICE'')
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value ' ;
l_stmt := l_select||' , :l_old_value old_value' ||
l_from||' ,oks_k_headers_b oksh ,okc_k_party_roles_b okp1'||
l_where||
' AND oksh.chr_id = okh.id
AND okh.renewal_type_code = :p_ctr_rec_old_value
AND nvl(oksh.ELECTRONIC_RENEWAL_FLAG,''N'') <> ''Y''
AND not exists(
select ol.object_chr_id
from okc_operation_lines ol
,okc_operation_instances oi
,okc_class_operations co
WHERE ol.object_chr_id = okh.id
AND ol.process_flag = ''P''
AND ol.ACTIVE_YN = ''Y''
AND oi.id = ol.oie_id
AND oi.cop_id = co.id
AND co.opn_code in (''RENEWAL'',''REN_CON'')
AND co.CLS_CODE = ''SERVICE'')
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value ' ;
l_stmt := l_select||' ,NULL old_value' ||
l_from||' ,okc_k_party_roles_b okp1 '||
l_where|| 'AND exists ( Select dnz_chr_id from okc_k_lines_b cln
Where cln.dnz_chr_id = okh.id
AND cln.lse_id in(3,16,21)
AND cln.price_list_id is NULL)
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value ';
l_stmt := l_select||' , :l_old_value old_value' ||
l_from||' ,okc_k_party_roles_b okp1 '||
l_where|| ' AND exists ( Select dnz_chr_id from okc_k_lines_b cln
Where cln.dnz_chr_id = okh.id
AND cln.lse_id in(3,16,21))
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value ';
l_stmt := l_select||' , :l_old_value old_value' ||
l_from||' ,okc_k_party_roles_b okp1 '||
l_where|| ' AND exists ( Select dnz_chr_id from okc_k_lines_b cln
Where cln.dnz_chr_id = okh.id
AND cln.lse_id in(3,16,21)
AND cln.price_list_id = to_number(:p_ctr_rec_old_value))
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value ';
l_stmt := l_select||' ,NULL old_value' ||
l_from||' ,oks_k_headers_v oksh ,okc_k_party_roles_b okp1 '||
l_where|| 'AND oksh.chr_id = okh.id
AND oksh.acct_rule_id IS NULL
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' ,oksh.acct_rule_id old_value' ||
l_from||' ,oks_k_headers_v oksh ,okc_k_party_roles_b okp1 '||
l_where|| 'AND oksh.chr_id = okh.id
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' ,oksh.acct_rule_id old_value' ||
l_from||' ,oks_k_headers_v oksh ,okc_k_party_roles_b okp1 '||
l_where|| 'AND oksh.chr_id = okh.id
AND oksh.acct_rule_id = to_number(:p_ctr_rec_old_value)
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' ,NULL old_value' ||
l_from||',okc_k_party_roles_b okp1 '||
l_where||
' AND okh.inv_rule_id IS NULL
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' ,okh.inv_rule_id old_value' ||
l_from||' ,okc_k_party_roles_b okp1 '||
l_where||
' AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' ,okh.inv_rule_id old_value' ||
l_from||',okc_k_party_roles_b okp1 '||
l_where||
' AND okh.inv_rule_id = to_number(:p_ctr_rec_old_value)
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' ,NULL old_value'||
l_from||',okc_k_party_roles_v okp1'||
l_where||' AND EXISTS (SELECT ''x'' from OKC_K_LINES_V okl, oks_k_lines_v oksl
WHERE okl.dnz_chr_id = okh.id
AND oksl.cle_id = okl.id
AND oksl.coverage_type is NULL )
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||',okc_k_party_roles_v okp1'||
l_where||'AND EXISTS (SELECT ''x'' from okc_k_lines_v okl, oks_k_lines_v oksl
WHERE okl.dnz_chr_id = okh.id
AND oksl.cle_id = okl.id )
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||',okc_k_party_roles_v okp1'||
l_where||'AND EXISTS (SELECT ''x'' from okc_k_lines_v okl, oks_k_lines_v oksl
WHERE okl.dnz_chr_id = okh.id
AND oksl.cle_id = okl.id
AND oksl.coverage_type = :p_ctr_rec_old_value )
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' ,NULL old_value'||
l_from||',okc_k_party_roles_v okp1'||
l_where||
' AND EXISTS (SELECT ''x'' from oks_coverage_timezones_v ctz
WHERE ctz.dnz_chr_id = okh.id
AND ctz.timezone_id IS NULL )
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||',okc_k_party_roles_v okp1'||
l_where||'AND EXISTS (SELECT ''x'' from oks_coverage_timezones_v ctz
WHERE ctz.dnz_chr_id = okh.id
AND ctz.timezone_id IS NOT NULL )
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||',okc_k_party_roles_v okp1'||
l_where||'AND EXISTS (SELECT ''x'' from oks_coverage_timezones_v ctz
WHERE ctz.dnz_chr_id = okh.id
AND ctz.timezone_id = to_number(:p_ctr_rec_old_value))
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||',okc_k_party_roles_v okp1'||
l_where||'AND EXISTS (SELECT ''x'' from
okc_contacts oco
WHERE oco.dnz_chr_id = okh.id
AND oco.cro_code = ''ENGINEER''
AND oco.jtot_object1_code = ''OKX_RESOURCE'')
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||',okc_k_party_roles_v okp1'||
l_where||'AND EXISTS (SELECT ''x'' from
okc_contacts oco
WHERE oco.dnz_chr_id = okh.id
AND oco.cro_code = ''ENGINEER''
AND oco.jtot_object1_code = ''OKX_RESOURCE''
AND oco.object1_id1 = :p_ctr_rec_old_value)
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||',okc_k_party_roles_v okp1'||
l_where||'AND EXISTS (SELECT ''x'' from
okc_contacts oco
WHERE oco.dnz_chr_id = okh.id
AND oco.cro_code = ''RSC_GROUP''
AND oco.jtot_object1_code = ''OKS_RSCGROUP'')
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||',okc_k_party_roles_v okp1'||
l_where||'AND EXISTS (SELECT ''x'' from
okc_contacts oco
WHERE oco.dnz_chr_id = okh.id
AND oco.cro_code = ''RSC_GROUP''
AND oco.jtot_object1_code = ''OKS_RSCGROUP''
AND oco.object1_id1 = :p_ctr_rec_old_value)
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||',okc_k_party_roles_v okp1'||
l_where||'AND EXISTS (SELECT ''x'' from okc_governances_v ogv
WHERE ogv.dnz_chr_id = okh.id
AND ogv.isa_agreement_id = :p_ctr_rec_old_value)
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' ,NULL old_value'||
l_from||' ,okc_k_party_roles_b okp1 '||l_where||
' AND EXISTS (SELECT ''x'' from OKC_K_LINES_V cle
WHERE cle.dnz_chr_id = okh.id
AND cle.lse_id in (7,8,9,10,11,18,25,35)
AND cle.cognomen is NULL)
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' ,NULL old_value'||
l_from||' ,okc_k_party_roles_b okp1 '||l_where||
' AND EXISTS (SELECT ''x'' from OKC_K_LINES_V cle
WHERE cle.dnz_chr_id = okh.id
AND cle.lse_id in (7,8,9,10,11,18,25,35))
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||' ,okc_k_party_roles_b okp1 '||l_where||
' AND EXISTS (SELECT ''x'' from OKC_K_LINES_V cle
WHERE cle.dnz_chr_id = okh.id
AND cle.lse_id in (7,8,9,10,11,18,25,35)
AND cle.cognomen = :p_ctr_rec_old_value)
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' ,NULL old_value'||
l_from||' ,okc_k_party_roles_b okp1 '||l_where||
' AND EXISTS (SELECT ''x'' from OKC_K_LINES_V cle
WHERE cle.chr_id = okh.id
AND cle.lse_id in (1,12,14,19)
AND cle.cognomen is NULL)
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' ,NULL old_value'||
l_from||' ,okc_k_party_roles_b okp1 '||l_where||
' AND EXISTS (SELECT ''x'' from OKC_K_LINES_V cle
WHERE cle.chr_id = okh.id
AND cle.lse_id in (1,12,14,19))
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||' ,okc_k_party_roles_b okp1 '||l_where||
' AND EXISTS (SELECT ''x'' from OKC_K_LINES_V cle
WHERE cle.chr_id = okh.id
AND cle.lse_id in (1,12,14,19)
AND cle.cognomen = :p_ctr_rec_old_value)
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' ,NULL old_value' ||
l_from||',okc_k_party_roles_b okp1'||
l_where||' AND okh.ship_to_site_use_id is NULL
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' ,okh.ship_to_site_use_id old_value' ||
l_from||',okc_k_party_roles_b okp1'||
l_where||' AND okh.ship_to_site_use_id = to_number(:p_ctr_rec_old_value)
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' ,NULL old_value' ||
l_from||',okc_k_party_roles_b okp1'||
l_where||
' AND okh.bill_to_site_use_id is NULL
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' ,okh.bill_to_site_use_id old_value' ||
l_from||',okc_k_party_roles_b okp1'||
l_where||
' AND okh.bill_to_site_use_id = to_number(:p_ctr_rec_old_value)
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' ,NULL old_value'||
l_from||',okc_k_party_roles_b okp1 '||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''SALESPERSON''
and oc.object1_id1 is null
and oc.jtot_object1_code = ''OKX_SALEPERS'')
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' ,oc.object1_id1 old_value'||
l_from||',okc_k_party_roles_b okp1, okc_contacts oc '||
l_where||
' and oc.dnz_chr_id = okh.id
and oc.cro_code = ''SALESPERSON''
and oc.jtot_object1_code = ''OKX_SALEPERS'')
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||',okc_k_party_roles_b okp1 '||l_where||
' AND (EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''SALESPERSON''
and oc.object1_id1= :p_ctr_rec_old_value
and oc.jtot_object1_code = ''OKX_SALEPERS'')
OR EXISTS (Select ''x'' from oks_k_sales_credits_v osc
Where osc.chr_id = okh.id
and osc.ctc_id = to_number(:p_ctr_rec_old_value)))
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' ,NULL old_value'||
l_from||',okc_k_party_roles_b okp1 '||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''SHIPPING''
and oc.object1_id1 is null
and oc.jtot_object1_code = ''OKX_PCONTACT'')
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' ,oc.object1_id1 old_value'||
l_from||',okc_k_party_roles_b okp1, okc_contacts oc '||
l_where||
' and oc.dnz_chr_id = okh.id
and oc.cro_code = ''SHIPPING''
and oc.jtot_object1_code = ''OKX_PCONTACT'')
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||',okc_k_party_roles_b okp1 '||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''SHIPPING''
and oc.object1_id1= :p_ctr_rec_old_value
and oc.jtot_object1_code = ''OKX_PCONTACT'')
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' ,NULL old_value'||
l_from||',okc_k_party_roles_b okp1 '||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''BILLING''
and oc.object1_id1 is null
and oc.jtot_object1_code = ''OKX_PCONTACT'')
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' ,oc.object1_id1 old_value'||
l_from||',okc_k_party_roles_b okp1, okc_contacts oc '||
l_where||
' and oc.dnz_chr_id = okh.id
and oc.cro_code = ''BILLING''
and oc.jtot_object1_code = ''OKX_PCONTACT'')
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||',okc_k_party_roles_b okp1 '||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''BILLING''
and oc.object1_id1= :p_ctr_rec_old_value
and oc.jtot_object1_code = ''OKX_PCONTACT'')
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' ,NULL old_value'||
l_from||',okc_k_party_roles_b okp1 '||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''CUST_SHIPPING''
and oc.object1_id1 is null
and oc.jtot_object1_code = ''OKX_CONTSHIP'')
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' ,oc.object1_id1 old_value'||
l_from||',okc_k_party_roles_b okp1, okc_contacts oc '||
l_where||
' and oc.dnz_chr_id = okh.id
and oc.cro_code = ''CUST_SHIPPING''
and oc.jtot_object1_code = ''OKX_CONTSHIP'')
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||',okc_k_party_roles_b okp1 '||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''CUST_SHIPPING''
and oc.object1_id1= :p_ctr_rec_old_value
and oc.jtot_object1_code = ''OKX_CONTSHIP'')
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' ,NULL old_value'||
l_from||',okc_k_party_roles_b okp1 '||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''CUST_BILLING''
and oc.object1_id1 is null
and oc.jtot_object1_code = ''OKX_CONTBILL'')
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' ,oc.object1_id1 old_value'||
l_from||',okc_k_party_roles_b okp1, okc_contacts oc '||
l_where||
' and oc.dnz_chr_id = okh.id
and oc.cro_code = ''CUST_BILLING''
and oc.jtot_object1_code = ''OKX_CONTBILL'')
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||',okc_k_party_roles_b okp1 '||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''CUST_BILLING''
and oc.object1_id1= :p_ctr_rec_old_value
and oc.jtot_object1_code = ''OKX_CONTBILL'')
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' ,NULL old_value'||
l_from||',okc_k_party_roles_b okp1 '||l_where||
' AND EXISTS (Select ''x'' from okc_time_ig_startend_val_v igs
where igs.dnz_chr_id = okh.id
and igs.start_hour is null
and igs.start_minute is null)
AND okh.id = okp1.chr_id
AND okp1.rle_code = ''CUSTOMER''
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||',okc_k_party_roles_b okp1 '||l_where||
' AND EXISTS (Select ''x'' from okc_time_ig_startend_val_v igs
where igs.dnz_chr_id = okh.id
and igs.start_hour = trunc(to_number(:p_ctr_rec_old_value)/60)
AND igs.start_minute = mod(to_number(:p_ctr_rec_old_value),60))
AND okh.id = okp1.chr_id
AND okp1.rle_code in = ''CUSTOMER''
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' ,NULL old_value'||
l_from||',okc_k_party_roles_b okp1 '||
l_where||
' AND EXISTS (Select ''x'' from oks_coverage_times_v oct
where oct.dnz_chr_id = okh.id
and oct.start_hour is null
and oct.start_minute is null)
AND okh.org_id = to_number(:update_level_value)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||',okc_k_party_roles_b okp1 '||
l_where||
' AND EXISTS (Select ''x'' from oks_coverage_times_v oct
where oct.dnz_chr_id = okh.id
and oct.start_hour = trunc(to_number(:p_ctr_rec_old_value)/60)
and oct.start_minute = mod(to_number(:p_ctr_rec_old_value),60))
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' ,NULL old_value'||
l_from||',okc_k_party_roles_b okp1 '||l_where||
' AND EXISTS (Select ''x'' from okc_time_ig_startend_val_v igs
where igs.dnz_chr_id = okh.id
and igs.end_hour is null
and igs.end_minute is null)
AND okh.id = okp1.chr_id
AND okp1.rle_code = ''CUSTOMER''
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||',okc_k_party_roles_b okp1 '||l_where||
' AND EXISTS (Select ''x'' from okc_time_ig_startend_val_v igs
where igs.dnz_chr_id = okh.id
and igs.end_hour = trunc(to_number(:p_ctr_rec_old_value)/60)
AND igs.end_minute = mod(to_number(:p_ctr_rec_old_value),60))
AND okh.id = okp1.chr_id
AND okp1.rle_code = ''CUSTOMER''
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' ,NULL old_value'||
l_from||',okc_k_party_roles_b okp1 '||
l_where||
' AND EXISTS (Select ''x'' from oks_coverage_times_v oct
where oct.dnz_chr_id = okh.id
and oct.end_hour is null
and oct.end_minute is null)
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||',okc_k_party_roles_b okp1 '||
l_where||
' AND EXISTS (Select ''x'' from oks_coverage_times_v oct
where oct.dnz_chr_id = okh.id
and oct.end_hour = trunc(to_number(:p_ctr_rec_old_value)/60)
and oct.end_minute = mod(to_number(:p_ctr_rec_old_value),60))
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||', NULL old_value'||
l_from||',okc_k_lines_b kl, oks_action_time_types_v att , oks_action_times_v oat,okc_k_party_roles_b okp1 '|| l_where||
' AND kl.dnz_chr_id = okh.id
AND kl.id = att.cle_id
And kl.lse_id in (4,17,22)
AND att.action_type_code = ''RSN''
AND att.id = oat.cov_action_type_id
AND ( sun_duration IS NULL
OR mon_duration IS NULL
OR tue_duration IS NULL
OR wed_duration IS NULL
OR thu_duration IS NULL
OR fri_duration IS NULL
OR sat_duration IS NULL)
AND okp1.dnz_chr_id = okh.id And okp1.cle_id Is Null
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||',okc_k_lines_b kl, oks_action_time_types_v att , oks_action_times_v oat,okc_k_party_roles_b okp1 '||l_where||
' AND kl.dnz_chr_id = okh.id
AND kl.id = att.cle_id
And kl.lse_id in (4,17,22)
AND att.action_type_code = ''RSN''
AND att.id = oat.cov_action_type_id
AND ( sun_duration = :p_ctr_rec_old_value
OR mon_duration = :p_ctr_rec_old_value
OR tue_duration = :p_ctr_rec_old_value
OR wed_duration = :p_ctr_rec_old_value
OR thu_duration = :p_ctr_rec_old_value
OR fri_duration = :p_ctr_rec_old_value
OR sat_duration = :p_ctr_rec_old_value)
AND okp1.dnz_chr_id = okh.id And okp1.cle_id Is Null
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||', NULL old_value'||
l_from||',okc_k_lines_b kl, oks_action_time_types_v att , oks_action_times_v oat,okc_k_party_roles_b okp1 '||l_where||
' AND kl.dnz_chr_id = okh.id
AND kl.id = att.cle_id
And kl.lse_id in (4,17,22)
AND att.action_type_code = ''RCN''
AND att.id = oat.cov_action_type_id
AND ( sun_duration IS NULL
OR mon_duration IS NULL
OR tue_duration IS NULL
OR wed_duration IS NULL
OR thu_duration IS NULL
OR fri_duration IS NULL
OR sat_duration IS NULL)
AND okp1.dnz_chr_id = okh.id And okp1.cle_id Is Null
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||',okc_k_lines_b kl, oks_action_time_types_v att , oks_action_times_v oat,okc_k_party_roles_b okp1 '||l_where||
' AND kl.dnz_chr_id = okh.id
AND kl.id = att.cle_id
And kl.lse_id in (4,17,22)
AND att.action_type_code = ''RCN''
AND att.id = oat.cov_action_type_id
AND ( sun_duration = :p_ctr_rec_old_value
OR mon_duration = :p_ctr_rec_old_value
OR tue_duration = :p_ctr_rec_old_value
OR wed_duration = :p_ctr_rec_old_value
OR thu_duration = :p_ctr_rec_old_value
OR fri_duration = :p_ctr_rec_old_value
OR sat_duration = :p_ctr_rec_old_value)
AND okp1.dnz_chr_id = okh.id And okp1.cle_id Is Null
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' ,NULL old_value'||
l_from||' ,okc_k_party_roles_b okp1'||
l_where||
' AND okh.price_list_id is NULL
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' ,okh.price_list_id old_value' ||
l_from||' ,okc_k_party_roles_b okp1'||
l_where||
' AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' ,okh.price_list_id old_value' ||
l_from||' ,okc_k_party_roles_b okp1'||
l_where||
' AND okh.price_list_id = to_number(:p_ctr_rec_old_value)
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' ,NULL old_value '||
l_from||', okc_k_party_roles_b okp1'||
l_where||'AND okh.cognomen IS NULL
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' ,okh.cognomen old_value'||
l_from||', okc_k_party_roles_b okp1 '||
l_where||' AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||', okh.cognomen old_value'||
l_from||', okc_k_party_roles_b okp1'||
l_where||' AND okh.cognomen = :p_ctr_rec_old_value
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' ,NULL old_value '||
l_from||', okc_k_party_roles_b okp1'||
l_where||'AND okh.cust_po_number IS NULL
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND ( okh.payment_instruction_type Is Null Or okh.payment_instruction_type = ''PON'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' ,okh.cust_po_number old_value'||
l_from||', okc_k_party_roles_b okp1 '||
l_where||' AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okh.payment_instruction_type = ''PON''
AND okp1.object1_id1 = :update_level_value and okh.cust_po_number_req_yn <> ''Y''';
l_stmt := l_select||' ,okh.cust_po_number old_value'||
l_from||', okc_k_party_roles_b okp1 '||
l_where||' AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okh.payment_instruction_type = ''PON''
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||', okh.cust_po_number old_value'||
l_from||', okc_k_party_roles_b okp1'||
l_where||' AND okh.cust_po_number = :p_ctr_rec_old_value
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okh.payment_instruction_type = ''PON''
AND okp1.object1_id1 = :update_level_value and okh.cust_po_number_req_yn <> ''Y''';
l_stmt := l_select||', okh.cust_po_number old_value'||
l_from||', okc_k_party_roles_b okp1'||
l_where||' AND okh.cust_po_number = :p_ctr_rec_old_value
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okh.payment_instruction_type = ''PON''
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||', okc_k_party_roles_b okp1'||
l_where||'AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||', okc_k_party_roles_b okp1 ,oks_k_headers_v oksh '||
l_where||' AND oksh.chr_id = okh.id
AND nvl(oksh.renewal_po_required,''N'') = :p_ctr_rec_old_value
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||', okc_k_party_roles_b okp1 ,oks_k_headers_v oksh '||
l_where||' AND oksh.chr_id = okh.id
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||', okc_k_party_roles_b okp1 ,oks_k_headers_v oksh '||
l_where||' AND oksh.chr_id = okh.id
AND nvl(oksh.inv_print_profile,''N'') = :p_ctr_rec_old_value
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' ,okg.cgp_parent_id old_value'||
l_from||',okc_k_groups_grpings_v okg, okc_k_party_roles_b okp1'||
l_where||' AND okg.included_chr_id = okh.id
AND okg.cgp_parent_id is NULL
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' ,NULL old_value'||
l_from||',okc_k_party_roles_b okp1'||
l_where||' AND EXISTS (SELECT id from okc_k_headers_v a
WHERE a.id = okh.id
MINUS
SELECT okg.included_chr_id
FROM okc_k_groups_grpings_v okg
WHERE okg.included_chr_id = okh.id)
AND okh.id = okp1.chr_id
AND okp1.rle_code = ''CUSTOMER''
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' ,okg.cgp_parent_id old_value'||
l_from||',okc_k_groups_grpings_v okg, okc_k_party_roles_b okp1'||
l_where||' AND okg.included_chr_id = okh.id
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||' ,okg.cgp_parent_id old_value'||
l_from||',okc_k_groups_grpings_v okg, okc_k_party_roles_b okp1'||
l_where||' AND okg.included_chr_id = okh.id
AND okg.cgp_parent_id = to_number(:p_ctr_rec_old_value)
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt:= l_select||',to_char(okh.start_date) old_value '||
l_from||', okc_k_party_roles_b okp1'||
l_where||' AND okh.start_date is NULL
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt:= l_select||',to_char(okh.start_date) old_value '||
l_from||', okc_k_party_roles_b okp1'||
l_where||' AND trunc(okh.start_date) = trunc(to_date(:p_ctr_rec_old_value,''YYYY/MM/DD HH24:MI:SS''))
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||',to_char(okh.end_date) old_value'||
l_from||', okc_k_party_roles_b okp1'||
l_where||' AND okh.end_date is NULL
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
l_stmt := l_select||',to_char(okh.end_date) old_value'||
l_from||', okc_k_party_roles_b okp1'||
l_where||' AND trunc(okh.end_date) = trunc(to_date(:p_ctr_rec_old_value,''YYYY/MM/DD HH24:MI:SS''))
AND okh.id = okp1.chr_id
AND okp1.rle_code in (''CUSTOMER'',''SUBSCRIBER'')
AND okp1.object1_id1 = :update_level_value';
ELSIF p_ctr_rec.update_level = 'OKS_K_CATEGORY' THEN
---------------------------------------------------------------------
-- Update Level : Category , Attribute: Revenue Account(REV_ACCT)
---------------------------------------------------------------------
IF p_ctr_rec.attribute = 'REV_ACCT' THEN
IF p_ctr_rec.old_value = '-9999' THEN -- For old Value as NULL
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from OKS_REV_DISTRIBUTIONS_V rev
WHERE rev.chr_id = okh.id
AND rev.code_combination_id is NULL)
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from OKS_REV_DISTRIBUTIONS_V rev
WHERE rev.chr_id = okh.id)
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from OKS_REV_DISTRIBUTIONS_V rev
WHERE rev.chr_id = okh.id
AND rev.code_combination_id = to_number(:p_ctr_rec_old_value))
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,NULL old_value' ||
l_from||l_where||
' AND okh.PAYMENT_TERM_ID is NULL
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,okh.payment_term_id old_value' ||
l_from||l_where||
'AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,okh.payment_term_id old_value' ||
l_from||l_where||
' AND okh.payment_term_id = to_number(:p_ctr_rec_old_value)
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,NULL old_value' ||
l_from||
l_where||
' AND okh.renewal_type_code is NULL
AND not exists(
select ol.object_chr_id
from okc_operation_lines ol
,okc_operation_instances oi
,okc_class_operations co
WHERE ol.object_chr_id = okh.id
AND ol.process_flag = ''P''
AND ol.ACTIVE_YN = ''Y''
AND oi.id = ol.oie_id
AND oi.cop_id = co.id
AND co.opn_code in (''RENEWAL'',''REN_CON'')
AND co.CLS_CODE = ''SERVICE'')
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value' ||
l_from||
l_where||
' AND not exists(
select ol.object_chr_id
from okc_operation_lines ol
,okc_operation_instances oi
,okc_class_operations co
WHERE ol.object_chr_id = okh.id
AND ol.process_flag = ''P''
AND ol.ACTIVE_YN = ''Y''
AND oi.id = ol.oie_id
AND oi.cop_id = co.id
AND co.opn_code in (''RENEWAL'',''REN_CON'')
AND co.CLS_CODE = ''SERVICE'')
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value' ||
l_from||' ,oks_k_headers_b oksh'||
l_where||
' AND oksh.chr_id = okh.id
AND okh.renewal_type_code = ''NSR''
AND ''ERN'' = :p_ctr_rec_old_value
AND oksh.ELECTRONIC_RENEWAL_FLAG = ''Y''
AND not exists(
select ol.object_chr_id
from okc_operation_lines ol
,okc_operation_instances oi
,okc_class_operations co
WHERE ol.object_chr_id = okh.id
AND ol.process_flag = ''P''
AND ol.ACTIVE_YN = ''Y''
AND oi.id = ol.oie_id
AND oi.cop_id = co.id
AND co.opn_code in (''RENEWAL'',''REN_CON'')
AND co.CLS_CODE = ''SERVICE'')
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value' ||
l_from||' ,oks_k_headers_b oksh'||
l_where||
' AND oksh.chr_id = okh.id
AND okh.renewal_type_code = :p_ctr_rec_old_value
AND nvl(oksh.ELECTRONIC_RENEWAL_FLAG,''N'') <> ''Y''
AND not exists(
select ol.object_chr_id
from okc_operation_lines ol
,okc_operation_instances oi
,okc_class_operations co
WHERE ol.object_chr_id = okh.id
AND ol.process_flag = ''P''
AND ol.ACTIVE_YN = ''Y''
AND oi.id = ol.oie_id
AND oi.cop_id = co.id
AND co.opn_code in (''RENEWAL'',''REN_CON'')
AND co.CLS_CODE = ''SERVICE'')
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,NULL old_value' ||
l_from||
l_where|| 'AND exists ( Select dnz_chr_id from okc_k_lines_b cln
Where cln.dnz_chr_id = okh.id
AND cln.lse_id in(3,16,21)
AND cln.price_list_id is NULL)
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value' ||
l_from||
l_where|| ' AND exists ( Select dnz_chr_id from okc_k_lines_b cln
Where cln.dnz_chr_id = okh.id
AND cln.lse_id in(3,16,21))
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value' ||
l_from||
l_where|| ' AND exists ( Select dnz_chr_id from okc_k_lines_b cln
Where cln.dnz_chr_id = okh.id
AND cln.lse_id in(3,16,21)
AND cln.price_list_id = to_number(:p_ctr_rec_old_value))
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,NULL old_value' ||
l_from||' ,oks_k_headers_v oksh '||
l_where|| 'AND oksh.chr_id = okh.id
AND oksh.acct_rule_id IS NULL
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,oksh.acct_rule_id old_value' ||
l_from||' ,oks_k_headers_v oksh '||
l_where|| 'AND oksh.chr_id = okh.id
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,oksh.acct_rule_id old_value' ||
l_from||' ,oks_k_headers_v oksh '||
l_where|| 'AND oksh.chr_id = okh.id
AND oksh.acct_rule_id = to_number(:p_ctr_rec_old_value)
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,NULL old_value' ||
l_from||l_where||
' AND okh.inv_rule_id IS NULL
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,okh.inv_rule_id old_value' ||
l_from||l_where||
' AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,okh.inv_rule_id old_value' ||
l_from||l_where||
' AND okh.inv_rule_id = to_number(:p_ctr_rec_old_value)
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from OKC_K_LINES_V okl, oks_k_lines_v oksl
WHERE okl.dnz_chr_id = okh.id
AND oksl.cle_id = okl.id
AND oksl.coverage_type is NULL )
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||
l_where||'AND EXISTS (SELECT ''x'' from okc_k_lines_v okl, oks_k_lines_v oksl
WHERE okl.dnz_chr_id = okh.id
AND oksl.cle_id = okl.id )
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||
l_where||'AND EXISTS (SELECT ''x'' from okc_k_lines_v okl, oks_k_lines_v oksl
WHERE okl.dnz_chr_id = okh.id
AND oksl.cle_id = okl.id
AND oksl.coverage_type = :p_ctr_rec_old_value )
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from oks_coverage_timezones_v ctz
WHERE ctz.dnz_chr_id = okh.id
AND ctz.timezone_id IS NULL )
AND okh.scs_code = :update_level_value' ;
l_stmt := l_select||' , :l_old_value old_value'||
l_from||
l_where||'AND EXISTS (SELECT ''x'' from oks_coverage_timezones_v ctz
WHERE ctz.dnz_chr_id = okh.id
AND ctz.timezone_id IS NOT NULL )
AND okh.scs_code = :update_level_value' ;
l_stmt := l_select||' , :l_old_value old_value'||
l_from||
l_where||'AND EXISTS (SELECT ''x'' from oks_coverage_timezones_v ctz
WHERE ctz.dnz_chr_id = okh.id
AND ctz.timezone_id = to_number(:p_ctr_rec_old_value))
AND okh.scs_code = :update_level_value' ;
l_stmt := l_select||' , :l_old_value old_value'||
l_from||
l_where||'AND EXISTS (SELECT ''x'' from
okc_contacts oco
WHERE oco.dnz_chr_id = okh.id
AND oco.cro_code = ''ENGINEER''
AND oco.jtot_object1_code = ''OKX_RESOURCE'')
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||
l_where||'AND EXISTS (SELECT ''x'' from
okc_contacts oco
WHERE oco.dnz_chr_id = okh.id
AND oco.cro_code = ''ENGINEER''
AND oco.jtot_object1_code = ''OKX_RESOURCE''
AND oco.object1_id1 = :p_ctr_rec_old_value)
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||
l_where||'AND EXISTS (SELECT ''x'' from
okc_contacts oco
WHERE oco.dnz_chr_id = okh.id
AND oco.cro_code = ''RSC_GROUP''
AND oco.jtot_object1_code = ''OKS_RSCGROUP'')
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||
l_where||'AND EXISTS (SELECT ''x'' from
okc_contacts oco
WHERE oco.dnz_chr_id = okh.id
AND oco.cro_code = ''RSC_GROUP''
AND oco.jtot_object1_code = ''OKS_RSCGROUP''
AND oco.object1_id1 = :p_ctr_rec_old_value)
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||
l_where||'AND EXISTS (SELECT ''x'' from okc_governances_v ogv
WHERE ogv.dnz_chr_id = okh.id
AND ogv.isa_agreement_id = :p_ctr_rec_old_value)
AND okh.scs_code=:update_level_value';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from OKC_K_LINES_V cle
WHERE cle.dnz_chr_id = okh.id
AND cle.lse_id IN (7,8,9,10,11,18,25,35)
AND cle.cognomen is NULL)
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from OKC_K_LINES_V cle
WHERE cle.dnz_chr_id = okh.id
AND cle.lse_id IN (7,8,9,10,11,18,25,35))
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from OKC_K_LINES_V cle
WHERE cle.dnz_chr_id = okh.id
AND cle.lse_id IN (7,8,9,10,11,18,25,35)
AND cle.cognomen = :p_ctr_rec_old_value)
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from OKC_K_LINES_V cle
WHERE cle.chr_id = okh.id
AND cle.lse_id IN (1,12,14,19)
AND cle.cognomen is NULL)
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from OKC_K_LINES_V cle
WHERE cle.chr_id = okh.id
AND cle.lse_id IN (1,12,14,19))
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from OKC_K_LINES_V cle
WHERE cle.chr_id = okh.id
AND cle.lse_id IN (1,12,14,19)
AND cle.cognomen = :p_ctr_rec_old_value)
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,NULL old_value' ||
l_from||l_where||
' AND okh.ship_to_site_use_id is NULL
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,okh.ship_to_site_use_id old_value' ||
l_from||l_where||
' AND okh.ship_to_site_use_id = to_number(:p_ctr_rec_old_value)
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,NULL old_value' ||
l_from||l_where||
' AND okh.bill_to_site_use_id is NULL
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,okh.bill_to_site_use_id old_value' ||
l_from||l_where||
' AND okh.bill_to_site_use_id = to_number(:p_ctr_rec_old_value)
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''SALESPERSON''
and oc.object1_id1 is null
and oc.jtot_object1_code = ''OKX_SALEPERS'')
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,oc.object1_id1 old_value '||
l_from||', okc_contacts oc '||
l_where||' and oc.dnz_chr_id = okh.id
and oc.cro_code = ''SALESPERSON''
and oc.jtot_object1_code = ''OKX_SALEPERS''
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
' AND (EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''SALESPERSON''
and oc.object1_id1=:p_ctr_rec_old_value
and oc.jtot_object1_code = ''OKX_SALEPERS'')
OR EXISTS (Select ''x'' from oks_k_sales_credits_v osc
Where osc.chr_id = okh.id
and osc.ctc_id = to_number(:p_ctr_rec_old_value)))
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''SHIPPING''
and oc.object1_id1 is null
and oc.jtot_object1_code = ''OKX_PCONTACT'')
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,oc.object1_id1 old_value'||
l_from||', okc_contacts oc '||
l_where||
' and oc.dnz_chr_id = okh.id
and oc.cro_code = ''SHIPPING''
and oc.jtot_object1_code = ''OKX_PCONTACT'')
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''SHIPPING''
and oc.object1_id1=:p_ctr_rec_old_value
and oc.jtot_object1_code = ''OKX_PCONTACT'')
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''BILLING''
and oc.object1_id1 is null
and oc.jtot_object1_code = ''OKX_PCONTACT'')
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,oc.object1_id1 old_value'||
l_from||', okc_contacts oc '||
l_where||
' and oc.dnz_chr_id = okh.id
and oc.cro_code = ''BILLING''
and oc.jtot_object1_code = ''OKX_PCONTACT'')
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''BILLING''
and oc.object1_id1=:p_ctr_rec_old_value
and oc.jtot_object1_code = ''OKX_PCONTACT'')
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''CUST_SHIPPING''
and oc.object1_id1 is null
and oc.jtot_object1_code = ''OKX_CONTSHIP'')
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,oc.object1_id1 old_value'||
l_from||', okc_contacts oc '||
l_where||
' and oc.dnz_chr_id = okh.id
and oc.cro_code = ''CUST_SHIPPING''
and oc.jtot_object1_code = ''OKX_CONTSHIP'')
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''CUST_SHIPPING''
and oc.object1_id1=:p_ctr_rec_old_value
and oc.jtot_object1_code = ''OKX_CONTSHIP'')
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''CUST_BILLING''
and oc.object1_id1 is null
and oc.jtot_object1_code = ''OKX_CONTBILL'')
AND okh.scs_code= = :update_level_value';
l_stmt := l_select||' ,oc.object1_id1 old_value'||
l_from||', okc_contacts oc '||
l_where||
' and oc.dnz_chr_id = okh.id
and oc.cro_code = ''CUST_BILLING''
and oc.jtot_object1_code = ''OKX_CONTBILL'')
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''CUST_BILLING''
and oc.object1_id1= :p_ctr_rec_old_value
and oc.jtot_object1_code = ''OKX_CONTBILL'')
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from okc_time_ig_startend_val_v igs
where igs.dnz_chr_id = okh.id
and ((igs.start_hour is null
and igs.start_minute is null)
OR
( igs.end_hour is null
and igs.end_minute is null))
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from okc_time_ig_startend_val_v igs
where igs.dnz_chr_id = okh.id
and (( igs.start_hour = trunc(to_number(:p_ctr_rec_old_value)/60)
AND igs.start_minute = mod(to_number(:p_ctr_rec_old_value),60))
OR
( igs.end_hour = trunc(to_number(:p_ctr_rec_old_value)/60)
AND igs.end_minute = mod(to_number(:p_ctr_rec_old_value),60)))
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from oks_coverage_times_v oct
where oct.dnz_chr_id = okh.id
and oct.start_hour is null
and oct.start_minute is null)
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from oks_coverage_times_v oct
where oct.dnz_chr_id = okh.id
and oct.start_hour = trunc(to_number(:p_ctr_rec_old_value)/60)
and oct.start_minute = mod(to_number(:p_ctr_rec_old_value),60))
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from oks_coverage_times_v oct
where oct.dnz_chr_id = okh.id
and oct.end_hour is null
and oct.end_minute is null)
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from oks_coverage_times_v oct
where oct.dnz_chr_id = okh.id
and oct.end_hour = trunc(to_number(:p_ctr_rec_old_value)/60)
and oct.end_minute = mod(to_number(:p_ctr_rec_old_value),60))
AND okh.scs_code = :update_level_value';
l_stmt := l_select||', NULL old_value'||
l_from||',okc_k_lines_b kl, oks_action_time_types_v att , oks_action_times_v oat '|| l_where||
' AND kl.dnz_chr_id = okh.id
AND kl.id = att.cle_id
And kl.lse_id in (4,17,22)
AND att.action_type_code = ''RSN''
AND att.id = oat.cov_action_type_id
AND ( sun_duration IS NULL
OR mon_duration IS NULL
OR tue_duration IS NULL
OR wed_duration IS NULL
OR thu_duration IS NULL
OR fri_duration IS NULL
OR sat_duration IS NULL)
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||',okc_k_lines_b kl, oks_action_time_types_v att , oks_action_times_v oat '||l_where||
' AND kl.dnz_chr_id = okh.id
AND kl.id = att.cle_id
And kl.lse_id in (4,17,22)
AND att.action_type_code = ''RSN''
AND att.id = oat.cov_action_type_id
AND ( sun_duration = :p_ctr_rec_old_value
OR mon_duration = :p_ctr_rec_old_value
OR tue_duration = :p_ctr_rec_old_value
OR wed_duration = :p_ctr_rec_old_value
OR thu_duration = :p_ctr_rec_old_value
OR fri_duration = :p_ctr_rec_old_value
OR sat_duration = :p_ctr_rec_old_value)
AND okh.scs_code = :update_level_value';
l_stmt := l_select||', NULL old_value'||
l_from||',okc_k_lines_b kl, oks_action_time_types_v att , oks_action_times_v oat '||l_where||
' AND kl.dnz_chr_id = okh.id
AND kl.id = att.cle_id
And kl.lse_id in (4,17,22)
AND att.action_type_code = ''RCN''
AND att.id = oat.cov_action_type_id
AND ( sun_duration IS NULL
OR mon_duration IS NULL
OR tue_duration IS NULL
OR wed_duration IS NULL
OR thu_duration IS NULL
OR fri_duration IS NULL
OR sat_duration IS NULL)
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||',okc_k_lines_b kl, oks_action_time_types_v att , oks_action_times_v oat '||l_where||
' AND kl.dnz_chr_id = okh.id
AND kl.id = att.cle_id
And kl.lse_id in (4,17,22)
AND att.action_type_code = ''RCN''
AND att.id = oat.cov_action_type_id
AND ( sun_duration = :p_ctr_rec_old_value
OR mon_duration = :p_ctr_rec_old_value
OR tue_duration = :p_ctr_rec_old_value
OR wed_duration = :p_ctr_rec_old_value
OR thu_duration = :p_ctr_rec_old_value
OR fri_duration = :p_ctr_rec_old_value
OR sat_duration = :p_ctr_rec_old_value)
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND okh.price_list_id is NULL
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,okh.price_list_id old_value' ||
l_from||l_where||
' AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,okh.price_list_id old_value' ||
l_from||l_where||
' AND okh.price_list_id = to_number(:p_ctr_rec_old_value)
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,NULL old_value '||
l_from||
l_where||'AND okh.cognomen IS NULL
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,okh.cognomen old_value'||
l_from||
l_where||' AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,okh.cognomen old_value'||
l_from||
l_where||' AND okh.cognomen = :p_ctr_rec_old_value
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,NULL old_value '||
l_from||
l_where||'AND okh.cust_po_number IS NULL
AND ( okh.payment_instruction_type Is Null Or okh.payment_instruction_type = ''PON'')
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,okh.cust_po_number old_value'||
l_from||
l_where||' AND okh.payment_instruction_type = ''PON'' AND okh.scs_code = :update_level_value and okh.cust_po_number_req_yn <> ''Y''';
l_stmt := l_select||' ,okh.cust_po_number old_value'||
l_from||
l_where||'AND okh.payment_instruction_type = ''PON'' AND okh.scs_code = :update_level_value ';
l_stmt := l_select||' ,okh.cust_po_number old_value'||
l_from||
l_where||' AND okh.cust_po_number = :p_ctr_rec_old_value
AND okh.payment_instruction_type = ''PON''
AND okh.scs_code = :update_level_value and okh.cust_po_number_req_yn <> ''Y''';
l_stmt := l_select||' ,okh.cust_po_number old_value'||
l_from||
l_where||' AND okh.cust_po_number = :p_ctr_rec_old_value AND okh.payment_instruction_type = ''PON''
AND okh.scs_code = :update_level_value ';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||
l_where||
'AND okh.scs_code = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||' ,oks_k_headers_v oksh '||
l_where||' AND oksh.chr_id = okh.id
AND nvl(oksh.renewal_po_required,''N'') = :p_ctr_rec_old_value
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||' ,oks_k_headers_v oksh '||
l_where||' AND oksh.chr_id = okh.id
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||' ,oks_k_headers_v oksh '||
l_where||' AND oksh.chr_id = okh.id
AND nvl(oksh.inv_print_profile,''N'') = :p_ctr_rec_old_value
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,okg.cgp_parent_id old_value'||
l_from||',okc_k_groups_grpings_v okg'||
l_where||' AND okg.included_chr_id = okh.id
AND okg.cgp_parent_id is NULL
AND okh.scs_code = :update_level_value';
/* l_stmt := l_select||' ,NULL old_value'||
l_from||
l_where||' AND EXISTS (SELECT id from okc_k_headers_v a
WHERE a.id = okh.id
MINUS
SELECT okg.included_chr_id
FROM okc_k_groups_grpings_v okg
WHERE okg.included_chr_id = okh.id)
AND okh.scs_code='''||p_ctr_rec.update_level_value||'''';
l_stmt := l_select||' ,okg.cgp_parent_id old_value'||
l_from||',okc_k_groups_grpings_v okg'||
l_where||' AND okg.included_chr_id = okh.id
AND okh.scs_code = :update_level_value';
l_stmt := l_select||' ,okg.cgp_parent_id old_value'||
l_from||',okc_k_groups_grpings_v okg'||
l_where||' AND okg.included_chr_id = okh.id
AND okg.cgp_parent_id = to_number(:p_ctr_rec_old_value)
AND okh.scs_code = :update_level_value';
l_stmt := l_select||',to_char(okh.start_date) old_value'||
l_from||
l_where||' AND okh.start_date is NULL
AND okh.scs_code = :update_level_value';
l_stmt := l_select||',to_char(okh.start_date) old_value'||
l_from||
l_where||' AND trunc(okh.start_date) = trunc(to_date(:p_ctr_rec_old_value,''YYYY/MM/DD HH24:MI:SS''))
AND okh.scs_code = :update_level_value';
l_stmt := l_select||',to_char(okh.end_date) old_value'||
l_from||
l_where||' AND okh.end_date is NULL
AND okh.scs_code = :update_level_value';
l_stmt := l_select||',to_char(okh.end_date) old_value'||
l_from||
l_where||' AND trunc(okh.end_date) = trunc(to_date(:p_ctr_rec_old_value,''YYYY/MM/DD HH24:MI:SS''))
AND okh.scs_code = :update_level_value';
ELSIF p_ctr_rec.update_level = 'OKS_K_GROUP' THEN
--------------------------------------------------------
-- Update Level : Contract Group , Attribute: Revenue Account(REV_ACCT)
--------------------------------------------------------
IF p_ctr_rec.attribute = 'REV_ACCT' THEN
IF p_ctr_rec.old_value = '-9999' THEN -- For old Value as NULL
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from OKS_REV_DISTRIBUTIONS_V rev
WHERE rev.chr_id = okh.id
AND rev.code_combination_id is NULL)
AND okh.id in (SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from OKS_REV_DISTRIBUTIONS_V rev
WHERE rev.chr_id = okh.id)
AND okh.id in (SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from OKS_REV_DISTRIBUTIONS_V rev
WHERE rev.chr_id = okh.id
AND rev.code_combination_id = to_number(:p_ctr_rec_old_value))
AND okh.id in (SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' ,NULL old_value' ||
l_from||l_where||
' AND okh.PAYMENT_TERM_ID is NULL
AND okh.id in( SELECT distinct okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)' ;
l_stmt := l_select||' ,okh.payment_term_id old_value' ||
l_from||l_where||
'AND okh.id in( SELECT distinct okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)' ;
l_stmt := l_select||' ,okh.payment_term_id old_value' ||
l_from||l_where||
' AND okh.payment_term_id = to_number(:p_ctr_rec_old_value)
AND okh.id in( SELECT distinct okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)' ;
l_stmt := l_select||' ,NULL old_value' ||
l_from||
l_where||
' AND okh.renewal_type_code is NULL
AND not exists(
select ol.object_chr_id
from okc_operation_lines ol
,okc_operation_instances oi
,okc_class_operations co
WHERE ol.object_chr_id = okh.id
AND ol.process_flag = ''P''
AND ol.ACTIVE_YN = ''Y''
AND oi.id = ol.oie_id
AND oi.cop_id = co.id
AND co.opn_code in (''RENEWAL'',''REN_CON'')
AND co.CLS_CODE = ''SERVICE'')
AND okh.id in( SELECT distinct okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)' ;
l_stmt := l_select||' , :l_old_value old_value' ||
l_from||
l_where||
' AND not exists(
select ol.object_chr_id
from okc_operation_lines ol
,okc_operation_instances oi
,okc_class_operations co
WHERE ol.object_chr_id = okh.id
AND ol.process_flag = ''P''
AND ol.ACTIVE_YN = ''Y''
AND oi.id = ol.oie_id
AND oi.cop_id = co.id
AND co.opn_code in (''RENEWAL'',''REN_CON'')
AND co.CLS_CODE = ''SERVICE'')
AND okh.id in( SELECT distinct okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)' ;
l_stmt := l_select||' , :l_old_value old_value' ||
l_from||' ,oks_k_headers_b oksh'||
l_where||
' AND oksh.chr_id = okh.id
AND okh.renewal_type_code = ''NSR''
AND ''ERN'' = :p_ctr_rec_old_value
AND oksh.ELECTRONIC_RENEWAL_FLAG =''Y''
AND not exists(
select ol.object_chr_id
from okc_operation_lines ol
,okc_operation_instances oi
,okc_class_operations co
WHERE ol.object_chr_id = okh.id
AND ol.process_flag = ''P''
AND ol.ACTIVE_YN = ''Y''
AND oi.id = ol.oie_id
AND oi.cop_id = co.id
AND co.opn_code in (''RENEWAL'',''REN_CON'')
AND co.CLS_CODE = ''SERVICE'')
AND okh.id in( SELECT distinct okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)' ;
l_stmt := l_select||' , :l_old_value old_value' ||
l_from||' ,oks_k_headers_b oksh'||
l_where||
' AND oksh.chr_id = okh.id
AND okh.renewal_type_code = :p_ctr_rec_old_value
AND nvl(oksh.ELECTRONIC_RENEWAL_FLAG,''N'') <>''Y''
AND not exists(
select ol.object_chr_id
from okc_operation_lines ol
,okc_operation_instances oi
,okc_class_operations co
WHERE ol.object_chr_id = okh.id
AND ol.process_flag = ''P''
AND ol.ACTIVE_YN = ''Y''
AND oi.id = ol.oie_id
AND oi.cop_id = co.id
AND co.opn_code in (''RENEWAL'',''REN_CON'')
AND co.CLS_CODE = ''SERVICE'')
AND okh.id in( SELECT distinct okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)' ;
l_stmt := l_select||' ,NULL old_value' ||
l_from||
l_where|| 'AND exists ( Select dnz_chr_id from okc_k_lines_b cln
Where cln.dnz_chr_id = okh.id
AND cln.lse_id in(3,16,21)
AND cln.price_list_id is NULL)
AND okh.id in( SELECT distinct okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' , :l_old_value old_value' ||
l_from||
l_where|| ' AND exists ( Select dnz_chr_id from okc_k_lines_b cln
Where cln.dnz_chr_id = okh.id
AND cln.lse_id in(3,16,21))
AND okh.id in( SELECT distinct okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' , :l_old_value old_value' ||
l_from||
l_where|| ' AND exists ( Select dnz_chr_id from okc_k_lines_b cln
Where cln.dnz_chr_id = okh.id
AND cln.lse_id in(3,16,21)
AND cln.price_list_id = to_number(:p_ctr_rec_old_value))
AND okh.id in( SELECT distinct okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' ,NULL old_value' ||
l_from||' ,oks_k_headers_v oksh '||
l_where|| 'AND oksh.chr_id = okh.id
AND oksh.acct_rule_id IS NULL
AND okh.id in( SELECT distinct okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' ,oksh.acct_rule_id old_value' ||
l_from||' ,oks_k_headers_v oksh '||
l_where|| 'AND oksh.chr_id = okh.id
AND okh.id in( SELECT distinct okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' ,oksh.acct_rule_id old_value' ||
l_from||' ,oks_k_headers_v oksh '||
l_where|| 'AND oksh.chr_id = okh.id
AND oksh.acct_rule_id = to_number(:p_ctr_rec_old_value)
AND okh.id in( SELECT distinct okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' ,NULL old_value' ||
l_from||l_where||
' AND okh.inv_rule_id IS NULL
AND okh.id in( SELECT distinct okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' ,okh.inv_rule_id old_value' ||
l_from||l_where||
' AND okh.id in( SELECT distinct okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' ,okh.inv_rule_id old_value' ||
l_from||l_where||
' AND okh.inv_rule_id = to_number(:p_ctr_rec_old_value)
AND okh.id in( SELECT distinct okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from OKC_K_LINES_V okl, oks_k_lines_v oksl
WHERE okl.dnz_chr_id = okh.id
AND oksl.cle_id = okl.id
AND oksl.coverage_type is NULL )
AND okh.id in( SELECT distinct okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||
l_where||'AND EXISTS (SELECT ''x'' from okc_k_lines_v okl, oks_k_lines_v oksl
WHERE okl.dnz_chr_id = okh.id
AND oksl.cle_id = okl.id )
AND okh.id in( SELECT distinct okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||
l_where||'AND EXISTS (SELECT ''x'' from okc_k_lines_v okl, oks_k_lines_v oksl
WHERE okl.dnz_chr_id = okh.id
AND oksl.cle_id = okl.id
AND oksl.coverage_type = :p_ctr_rec_old_value )
AND okh.id in( SELECT distinct okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from oks_coverage_timezones_v ctz
WHERE ctz.dnz_chr_id = okh.id
AND ctz.timezone_id IS NULL )
AND okh.id in( SELECT distinct okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||
l_where||'AND EXISTS (SELECT ''x'' from oks_coverage_timezones_v ctz
WHERE ctz.dnz_chr_id = okh.id
AND ctz.timezone_id IS NOT NULL )
AND okh.id in( SELECT distinct okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||
l_where||'AND EXISTS (SELECT ''x'' from oks_coverage_timezones_v ctz
WHERE ctz.dnz_chr_id = okh.id
AND ctz.timezone_id = to_number(:p_ctr_rec_old_value))
AND okh.id in( SELECT distinct okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||
l_where||'AND EXISTS (SELECT ''x'' from
okc_contacts oco
WHERE oco.dnz_chr_id = okh.id
AND oco.cro_code = ''ENGINEER''
AND oco.jtot_object1_code = ''OKX_RESOURCE'')
AND okh.id in( SELECT distinct okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||
l_where||'AND EXISTS (SELECT ''x'' from
okc_contacts oco
WHERE oco.dnz_chr_id = okh.id
AND oco.cro_code = ''ENGINEER''
AND oco.jtot_object1_code = ''OKX_RESOURCE''
AND oco.object1_id1 = :p_ctr_rec_old_value)
AND okh.id in( SELECT distinct okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||
l_where||'AND EXISTS (SELECT ''x'' from
okc_contacts oco
WHERE oco.dnz_chr_id = okh.id
AND oco.cro_code = ''RSC_GROUP''
AND oco.jtot_object1_code = ''OKS_RSCGROUP'')
AND okh.id in( SELECT distinct okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||
l_where||'AND EXISTS (SELECT ''x'' from
okc_contacts oco
WHERE oco.dnz_chr_id = okh.id
AND oco.cro_code = ''RSC_GROUP''
AND oco.jtot_object1_code = ''OKS_RSCGROUP''
AND oco.object1_id1 = :p_ctr_rec_old_value)
AND okh.id in( SELECT distinct okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||
l_where||'AND EXISTS (SELECT ''x'' from okc_governances_v ogv
WHERE ogv.dnz_chr_id = okh.id
AND ogv.isa_agreement_id = :p_ctr_rec_old_value)
AND okh.id in( SELECT distinct okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from OKC_K_LINES_V cle
WHERE cle.dnz_chr_id = okh.id
AND cle.lse_id IN (7,8,9,10,11,18,25,35)
AND cle.cognomen is NULL)
AND okh.id in (SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from OKC_K_LINES_V cle
WHERE cle.dnz_chr_id = okh.id
AND cle.lse_id IN (7,8,9,10,11,18,25,35))
AND okh.id in (SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from OKC_K_LINES_V cle
WHERE cle.dnz_chr_id = okh.id
AND cle.lse_id IN (7,8,9,10,11,18,25,35)
AND cle.cognomen = :p_ctr_rec_old_value)
AND okh.id in (SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from OKC_K_LINES_V cle
WHERE cle.chr_id = okh.id
AND cle.lse_id IN (1,12,14,19)
AND cle.cognomen is NULL)
AND okh.id in (SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from OKC_K_LINES_V cle
WHERE cle.chr_id = okh.id
AND cle.lse_id IN (1,12,14,19))
AND okh.id in (SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
' AND EXISTS (SELECT ''x'' from OKC_K_LINES_V cle
WHERE cle.chr_id = okh.id
AND cle.lse_id IN (1,12,14,19)
AND cle.cognomen = :p_ctr_rec_old_value)
AND okh.id in (SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' ,NULL old_value' ||
l_from||l_where||
' AND okh.ship_to_site_use_id is NULL
AND okh.id in (SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' ,okh.ship_to_site_use_id old_value' ||
l_from||l_where||
' AND okh.ship_to_site_use_id = to_number(:p_ctr_rec_old_value)
AND okh.id in (SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' ,NULL old_value' ||
l_from||l_where||
' AND okh.bill_to_site_use_id is NULL
AND okh.id in (SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' ,okh.bill_to_site_use_id old_value' ||
l_from||l_where||
' AND okh.bill_to_site_use_id = to_number(:p_ctr_rec_old_value)
AND okh.id in (SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''SALESPERSON''
and oc.object1_id1 is null
and oc.jtot_object1_code = ''OKX_SALEPERS'')
AND okh.id in (SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' ,oc.object1_id1 old_value '||
l_from||', okc_contacts oc '||
l_where||' and oc.dnz_chr_id = okh.id
and oc.cro_code = ''SALESPERSON''
and oc.jtot_object1_code = ''OKX_SALEPERS''
AND okh.id in (SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
' AND (EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''SALESPERSON''
and oc.object1_id1=:p_ctr_rec_old_value
and oc.jtot_object1_code = ''OKX_SALEPERS'')
OR EXISTS (Select ''x'' from oks_k_sales_credits_v osc
Where osc.chr_id = okh.id
and osc.ctc_id = to_number(:p_ctr_rec_old_value)))
AND okh.id in (SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''SHIPPING''
and oc.object1_id1 is null
and oc.jtot_object1_code = ''OKX_PCONTACT'')
AND okh.id in (SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' ,oc.object1_id1 old_value'||
l_from||', okc_contacts oc '||
l_where||
' and oc.dnz_chr_id = okh.id
and oc.cro_code = ''SHIPPING''
and oc.jtot_object1_code = ''OKX_PCONTACT'')
AND okh.id in (SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''SHIPPING''
and oc.object1_id1=:p_ctr_rec_old_value
and oc.jtot_object1_code = ''OKX_PCONTACT'')
AND okh.id in (SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''BILLING''
and oc.object1_id1 is null
and oc.jtot_object1_code = ''OKX_PCONTACT'')
AND okh.id in (SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' ,oc.object1_id1 old_value'||
l_from||', okc_contacts oc '||
l_where||
' and oc.dnz_chr_id = okh.id
and oc.cro_code = ''BILLING''
and oc.jtot_object1_code = ''OKX_PCONTACT'')
AND okh.id in (SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''BILLING''
and oc.object1_id1=:p_ctr_rec_old_value
and oc.jtot_object1_code = ''OKX_PCONTACT'')
AND okh.id in (SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''CUST_SHIPPING''
and oc.object1_id1 is null
and oc.jtot_object1_code = ''OKX_CONTSHIP'')
AND okh.id in (SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' ,oc.object1_id1 old_value'||
l_from||', okc_contacts oc '||
l_where||
' and oc.dnz_chr_id = okh.id
and oc.cro_code = ''CUST_SHIPPING''
and oc.jtot_object1_code = ''OKX_CONTSHIP'')
AND okh.id in (SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''CUST_SHIPPING''
and oc.object1_id1=:p_ctr_rec_old_value
and oc.jtot_object1_code = ''OKX_CONTSHIP'')
AND okh.id in (SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''CUST_BILLING''
and oc.object1_id1 is null
and oc.jtot_object1_code = ''OKX_CONTBILL'')
AND okh.id in (SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' ,oc.object1_id1 old_value'||
l_from||', okc_contacts oc '||
l_where||
' and oc.dnz_chr_id = okh.id
and oc.cro_code = ''CUST_BILLING''
and oc.jtot_object1_code = ''OKX_CONTBILL'')
AND okh.id in (SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from okc_contacts oc
where oc.dnz_chr_id = okh.id
and oc.cro_code = ''CUST_BILLING''
and oc.object1_id1=:p_ctr_rec_old_value
and oc.jtot_object1_code = ''OKX_CONTBILL'')
AND okh.id in (SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from okc_time_ig_startend_val_v igs
where igs.dnz_chr_id = okh.id
and ((igs.start_hour is null
and igs.start_minute is null)
OR
( igs.end_hour is null
and igs.end_minute is null))
AND okh.id in (SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from okc_time_ig_startend_val_v igs
where igs.dnz_chr_id = okh.id
and (( igs.start_hour = trunc(to_number(:p_ctr_rec_old_value)/60)
AND igs.start_minute = mod(to_number(:p_ctr_rec_old_value),60))
OR
( igs.end_hour = trunc(to_number(:p_ctr_rec_old_value)/60)
AND igs.end_minute = mod(to_number(:p_ctr_rec_old_value),60)))
AND okh.id in (SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from oks_coverage_times_v oct
where oct.dnz_chr_id = okh.id
and oct.start_hour is null
and oct.start_minute is null)
AND okh.id in (SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from oks_coverage_times_v oct
where oct.dnz_chr_id = okh.id
and oct.start_hour = trunc(to_number(:p_ctr_rec_old_value)/60)
and oct.start_minute = mod(to_number(:p_ctr_rec_old_value),60))
AND okh.id in (SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from oks_coverage_times_v oct
where oct.dnz_chr_id = okh.id
and oct.end_hour is null
and oct.end_minute is null)
AND okh.id in (SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||l_where||
' AND EXISTS (Select ''x'' from oks_coverage_times_v oct
where oct.dnz_chr_id = okh.id
and oct.end_hour = trunc(to_number(:p_ctr_rec_old_value)/60)
and oct.end_minute = mod(to_number(:p_ctr_rec_old_value),60))
AND okh.id in (SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||', NULL old_value'||
l_from||',okc_k_lines_b kl, oks_action_time_types_v att , oks_action_times_v oat '|| l_where||
' AND kl.dnz_chr_id = okh.id
AND kl.id = att.cle_id
And kl.lse_id in (4,17,22)
AND att.action_type_code = ''RSN''
AND att.id = oat.cov_action_type_id
AND ( sun_duration IS NULL
OR mon_duration IS NULL
OR tue_duration IS NULL
OR wed_duration IS NULL
OR thu_duration IS NULL
OR fri_duration IS NULL
OR sat_duration IS NULL)
AND okh.id in (SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||',okc_k_lines_b kl, oks_action_time_types_v att , oks_action_times_v oat '||l_where||
' AND kl.dnz_chr_id = okh.id
AND kl.id = att.cle_id
And kl.lse_id in (4,17,22)
AND att.action_type_code = ''RSN''
AND att.id = oat.cov_action_type_id
AND ( sun_duration = :p_ctr_rec_old_value
OR mon_duration = :p_ctr_rec_old_value
OR tue_duration = :p_ctr_rec_old_value
OR wed_duration = :p_ctr_rec_old_value
OR thu_duration = :p_ctr_rec_old_value
OR fri_duration = :p_ctr_rec_old_value
OR sat_duration = :p_ctr_rec_old_value)
AND okh.id in (SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||', NULL old_value'||
l_from||',okc_k_lines_b kl, oks_action_time_types_v att , oks_action_times_v oat '||l_where||
' AND kl.dnz_chr_id = okh.id
AND kl.id = att.cle_id
And kl.lse_id in (4,17,22)
AND att.action_type_code = ''RCN''
AND att.id = oat.cov_action_type_id
AND ( sun_duration IS NULL
OR mon_duration IS NULL
OR tue_duration IS NULL
OR wed_duration IS NULL
OR thu_duration IS NULL
OR fri_duration IS NULL
OR sat_duration IS NULL)
AND okh.id in(SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||',okc_k_lines_b kl, oks_action_time_types_v att , oks_action_times_v oat '||l_where||
' AND kl.dnz_chr_id = okh.id
AND kl.id = att.cle_id
And kl.lse_id in (4,17,22)
AND att.action_type_code = ''RCN''
AND att.id = oat.cov_action_type_id
AND ( sun_duration = :p_ctr_rec_old_value
OR mon_duration = :p_ctr_rec_old_value
OR tue_duration = :p_ctr_rec_old_value
OR wed_duration = :p_ctr_rec_old_value
OR thu_duration = :p_ctr_rec_old_value
OR fri_duration = :p_ctr_rec_old_value
OR sat_duration = :p_ctr_rec_old_value)
AND okh.id in(SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' ,NULL old_value'||
l_from||l_where||
' AND okh.price_list_id is NULL
AND okh.id in( SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' ,okh.price_list_id old_value' ||
l_from||l_where||
' AND okh.id in( SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' ,okh.price_list_id old_value' ||
l_from||l_where||
' AND okh.price_list_id = to_number(:p_ctr_rec_old_value)
AND okh.id in( SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' , NULL old_value'||
l_from||
l_where||' AND okh.cognomen IS NULL
AND okh.id in(
SELECT distinct okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' ,okh.cognomen old_value'||
l_from||
l_where||' AND okh.id in(
SELECT distinct okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||', okh.cognomen old_value'||
l_from||
l_where||' AND okh.cognomen = :p_ctr_rec_old_value
AND okh.id in(
SELECT distinct okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' , NULL old_value'||
l_from||
l_where||' AND okh.cust_po_number IS NULL
AND ( okh.payment_instruction_type Is Null Or okh.payment_instruction_type = ''PON'')
AND okh.id in(
SELECT distinct okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' ,okh.cust_po_number old_value'||
l_from||
l_where||' AND okh.payment_instruction_type = ''PON'' AND okh.id in(
SELECT distinct okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.inclued_cgp_id)
AND okh.cust_po_number_req_yn <> ''Y''';
l_stmt := l_select||' ,okh.cust_po_number old_value'||
l_from||
l_where||' AND okh.payment_instruction_type = ''PON'' AND okh.id in(
SELECT distinct okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.inclued_cgp_id)';
l_stmt := l_select||' ,okh.cust_po_number old_value'||
l_from||
l_where||' AND okh.cust_po_number = :p_ctr_rec_old_value
AND okh.payment_instruction_type = ''PON''
AND okh.id in(
SELECT distinct okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)
And okh.cust_po_number_req_yn <> ''Y''';
l_stmt := l_select||' ,okh.cust_po_number old_value'||
l_from||
l_where||' AND okh.cust_po_number = :p_ctr_rec_old_value
AND okh.payment_instruction_type = ''PON''
AND okh.id in(
SELECT distinct okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||
l_where||
'AND okh.id in(
SELECT distinct okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||' ,oks_k_headers_v oksh '||
l_where||' AND oksh.chr_id = okh.id
AND nvl(oksh.renewal_po_required,''N'') = :p_ctr_rec_old_value
AND okh.id in(
SELECT distinct okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||' ,oks_k_headers_v oksh '||
l_where||' AND oksh.chr_id = okh.id
AND okh.id in(
SELECT distinct okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' , :l_old_value old_value'||
l_from||' ,oks_k_headers_v oksh '||
l_where||' AND oksh.chr_id = okh.id
AND nvl(oksh.inv_print_profile,''N'') = :p_ctr_rec_old_value
AND okh.id in(
SELECT distinct okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' ,okg.cgp_parent_id old_value'||
l_from||',okc_k_groups_grpings_v okg'||
l_where||' AND okg.included_chr_id = okh.id
AND okg.cgp_parent_id is NULL
AND okh.id in(
SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' ,NULL old_value'||
l_from||
l_where||' AND EXISTS (SELECT id from okc_k_headers_v a
WHERE a.id = okh.id
MINUS
SELECT okg.included_chr_id
FROM okc_k_groups_grpings_v okg
WHERE okg.included_chr_id = okh.id)
AND okh.id in(
SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = '||to_number(p_ctr_rec.update_level_value)||'
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' ,okg.cgp_parent_id old_value'||
l_from||',okc_k_groups_grpings_v okg'||
l_where||' AND okg.included_chr_id = okh.id
AND (okg.cgp_parent_id in(
SELECT okg1.included_cgp_id from okc_k_grpings okg1
WHERE okg1.included_cgp_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id))
AND okh.id in(
SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' ,okg.cgp_parent_id old_value'||
l_from||',okc_k_groups_grpings_v okg'||
l_where||' AND okg.included_chr_id = okh.id
AND (okg.cgp_parent_id = to_number(:update_level_value)
OR okg.cgp_parent_id in(
SELECT okg1.included_cgp_id from okc_k_grpings okg1
WHERE okg1.included_cgp_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id))
AND okh.id in(
SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||' ,okg.cgp_parent_id old_value'||
l_from||',okc_k_groups_grpings_v okg'||
l_where||' AND okg.included_chr_id = okh.id
AND okg.cgp_parent_id = to_number(:p_ctr_rec_old_value)
AND okh.id in(
SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||',to_char(okh.start_date) old_value'||
l_from||
l_where||' AND okh.start_date is NULL
AND okh.id in(
SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||',to_char(okh.start_date) old_value'||
l_from||
l_where||' AND trunc(okh.start_date) = trunc(to_date(:p_ctr_rec_old_value,''YYYY/MM/DD HH24:MI:SS''))
AND okh.id in(
SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||',to_char(okh.end_date) old_value'||
l_from||
l_where||' AND okh.end_date is NULL
AND okh.id in(
SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
l_stmt := l_select||',to_char(okh.end_date) old_value'||
l_from||
l_where||' AND trunc(okh.end_date) = trunc(to_date(:p_ctr_rec_old_value,''YYYY/MM/DD HH24:MI:SS''))
AND okh.id in(
SELECT okg1.included_chr_id from okc_k_grpings okg1
WHERE okg1.included_chr_id is not null
START WITH okg1.cgp_parent_id = to_number(:update_level_value)
CONNECT BY okg1.cgp_parent_id = PRIOR okg1.included_cgp_id)';
, p_ctr_rec.update_level_value,l_org_id;
using p_ctr_rec.oie_id , p_ctr_rec.update_level_value,l_org_id;
, p_ctr_rec.update_level_value ,l_org_id;
,p_ctr_rec.update_level_value,l_org_id;
p_ctr_rec.old_value, p_ctr_rec.update_level_value,l_org_id;
p_ctr_rec.old_value, p_ctr_rec.update_level_value,l_org_id;
using l_old_value, p_ctr_rec.update_level_value,l_org_id;
using p_ctr_rec.update_level_value,l_org_id;
p_ctr_rec.old_value , p_ctr_rec.update_level_value,l_org_id;
p_ctr_rec.update_level_value,l_org_id;
p_ctr_rec.update_level_value,l_org_id;
p_ctr_rec.update_level_value,l_org_id;
using l_old_value, p_ctr_rec.oie_id , p_ctr_rec.update_level_value;
using p_ctr_rec.oie_id , p_ctr_rec.update_level_value;
, p_ctr_rec.update_level_value;
, p_ctr_rec.update_level_value, l_org_id;
p_ctr_rec.old_value, p_ctr_rec.update_level_value, l_org_id;
p_ctr_rec.old_value,p_ctr_rec.old_value,p_ctr_rec.update_level_value;
p_ctr_rec.old_value, p_ctr_rec.update_level_value;
p_ctr_rec.old_value, p_ctr_rec.update_level_value;
using l_old_value, p_ctr_rec.update_level_value;
OPEN v_CurContract FOR l_stmt using p_ctr_rec.update_level_value;
p_ctr_rec.old_value , p_ctr_rec.update_level_value;
p_ctr_rec.old_value , p_ctr_rec.update_level_value, l_org_id;
p_ctr_rec.update_level_value, l_org_id;
p_ctr_rec.update_level_value;
p_ctr_rec.update_level_value;
p_ctr_rec.update_level_value;
p_ctr_rec.update_level_value,l_org_id;
p_ctr_rec.update_level_value,l_org_id;
p_ctr_rec.old_value , p_ctr_rec.update_level_value,l_org_id;
p_ctr_rec.update_level_value,l_org_id;
p_ctr_rec.old_value,p_ctr_rec.update_level_value,l_org_id;
p_ctr_rec.old_value,p_ctr_rec.update_level_value,l_org_id;
p_ctr_rec.update_level_value;
p_ctr_rec.update_level_value;
p_ctr_rec.old_value , p_ctr_rec.update_level_value;
p_ctr_rec.old_value , p_ctr_rec.update_level_value, l_org_id;
p_ctr_rec.old_value, p_ctr_rec.update_level_value,l_org_id;
p_ctr_rec.update_level_value;
p_ctr_rec.old_value,p_ctr_rec.update_level_value;
p_ctr_rec.old_value,p_ctr_rec.update_level_value;
SELECT name, usr.user_name
FROM okc_operation_instances_v oie,
fnd_user usr
WHERE id = p_oie_id
AND oie.last_updated_by = usr.user_id;
SELECT oie.name,
oie.jtot_object1_code update_level,
oie.object1_id1 update_level_value,
omr.ATTRIBUTE_NAME attribute,
omr.OLD_VALUE old_value,
omr.NEW_VALUE new_value
FROM okc_operation_instances_v oie,
okc_masschange_req_dtls omr
WHERE oie.id = omr.oie_id
AND oie.id = p_oie_id;
SELECT id
FROM okc_k_grpings_v
WHERE included_chr_id = p_chr_id
AND cgp_parent_id = p_cgp_id;
SELECT oct.id,oct.OBJECT_VERSION_NUMBER,
oct.start_hour,oct.start_minute,oct.end_hour,oct.end_minute
FROM oks_coverage_timezones_v ctz ,
oks_coverage_times_v oct
WHERE oct.cov_tze_line_id = ctz.id
AND oct.dnz_chr_id = p_chr_id
AND oct.start_hour = p_hour
AND oct.start_minute = p_minute ;
SELECT oct.id ,oct.OBJECT_VERSION_NUMBER,
oct.start_hour,oct.start_minute,oct.end_hour,oct.end_minute
FROM oks_coverage_timezones_v ctz ,
oks_coverage_times_v oct
WHERE oct.cov_tze_line_id = ctz.id
AND oct.dnz_chr_id = p_chr_id
AND oct.end_hour = p_hour
AND oct.end_minute = p_minute ;
SELECT oct.id id
FROM oks_coverage_timezones_V oct
WHERE oct.dnz_chr_id = p_chr_id;
SELECT oct.id id,oct.OBJECT_VERSION_NUMBER,oct.cle_id cle_id
FROM oks_coverage_timezones_V oct
WHERE oct.dnz_chr_id = p_chr_id
AND oct.timezone_id = p_old_value ;
SELECT oksh.id , oksh.object_version_number
FROM oks_k_headers_b oksh
WHERE oksh.chr_id = p_chr_id
AND oksh.acct_rule_id = p_old_value ;
SELECT oksh.id , oksh.object_version_number
FROM oks_k_headers_b oksh
WHERE oksh.chr_id = p_chr_id
AND nvl(oksh.inv_print_profile,'N') = p_old_value ;
SELECT oksh.id , oksh.object_version_number
FROM oks_k_headers_b oksh
WHERE oksh.chr_id = p_chr_id;
SELECT oksh.id , oksh.object_version_number
FROM oks_k_headers_v oksh
WHERE oksh.chr_id = p_chr_id
AND nvl(oksh.renewal_po_required,'N') = p_old_value ;
SELECT ogv.id, ogv.object_version_number
FROM okc_governances_v ogv
WHERE ogv.dnz_chr_id = p_chr_id
AND ogv.isa_agreement_id = p_old_value ;
SELECT oc.id, oc.object_version_number, kh.start_date start_date
FROM okc_contacts oc , okc_k_headers_b kh
WHERE oc.dnz_chr_id = p_chr_id
AND oc.cro_code = p_cro_code
AND oc.object1_id1 = p_old_value
AND oc.jtot_object1_code = p_object_code
And Kh.id = oc.dnz_chr_id;
SELECT rev.id, rev.object_version_number
FROM oks_rev_distributions_v rev
WHERE rev.chr_id = p_chr_id
AND rev.code_combination_id = p_old_value;
SELECT srv.id, srv.object_version_number, kh.start_date
FROM OKS_K_SALES_CREDITS_V srv
, Okc_k_headers_b Kh
WHERE srv.cle_id Is Null
And kh.id = p_chr_id
And kh.id = srv.chr_id
AND srv.ctc_id = to_number(p_old_value);
SELECT srv.id, srv.object_version_number, kl.start_date
FROM OKS_K_SALES_CREDITS_V srv
, Okc_k_lines_b Kl
WHERE srv.chr_id = p_chr_id
And srv.Cle_id Is nOt null
And Kl.id = srv.cle_id
AND srv.ctc_id = to_number(p_old_value);
SELECT okh.id,
okh.contract_number,
okh.contract_number_modifier,
okh.short_description,
okh.sts_code,
ole.id ole_id,
mrd.old_value,
okh.billed_at_source
FROM okc_k_headers_v okh,
okc_operation_lines_v ole,
okc_masschange_req_dtls_v mrd
WHERE ole.select_yn = 'Y'
AND ole.process_flag is NULL
AND okh.id = ole.subject_chr_id
AND ole.id = mrd.ole_id
AND ole.oie_id = p_oie_id;
SELECT okl.id id
FROM okc_k_lines_b okl
,Oks_k_lines_b oks
WHERE okl.dnz_chr_id = p_chr_id
AND okl.date_cancelled is NULL
And okl.id = oks.cle_id
AND ( okl.lse_id in (1,14,19,46)
or (okl.lse_id = 12 and oks.usage_type in ('FRT','NPR'))) ;
SELECT okl.id id
FROM okc_k_lines_b okl
WHERE okl.dnz_chr_id = p_chr_id
AND okl.date_cancelled is NULL
AND okl.lse_id in (1,12,14,19,46) ;
SELECT count(*) FROM oks_pm_schedules_v
WHERE cle_id = p_cle_id;
SELECT oat.id id ,oat.sun_duration ,oat.mon_duration ,oat.tue_duration ,oat.wed_duration,
oat.thu_duration ,oat.fri_duration, oat.sat_duration, oat.object_version_number
FROM OKC_K_LINES_V okl, oks_action_times oat , oks_action_time_types att
WHERE okl.dnz_chr_id = p_chr_id
AND att.cle_id = okl.id
AND att.action_type_code = p_action_type_code
AND oat.cov_action_type_id = att.id
AND (oat.sun_duration = p_old_value
OR oat.mon_duration = p_old_value
OR oat.tue_duration = p_old_value
OR oat.wed_duration = p_old_value
OR oat.thu_duration = p_old_value
OR oat.fri_duration = p_old_value
OR oat.sat_duration = p_old_value ) ;
SELECT osl.id , osl.object_version_number
FROM okc_k_lines_v okl, oks_k_lines_v osl
WHERE okl.id = osl.cle_id
AND okl.dnz_chr_id = p_chr_id
AND okl.lse_id in( 2,15,20)
AND osl.coverage_type = p_old_value ;
SELECT cln.id , cln.object_version_number
FROM okc_k_lines_b cln
Where cln.dnz_chr_id = p_chr_id
AND cln.lse_id in(3,16,21)
AND cln.price_list_id = p_old_value ;
SELECT cln.id , cln.object_version_number
FROM okc_k_lines_b cln
Where cln.dnz_chr_id = p_chr_id
AND cln.lse_id in(3,16,21);
SELECT cln.id , cln.object_version_number
FROM okc_k_lines_b cln
Where cln.dnz_chr_id = p_chr_id
AND cln.lse_id in(3,16,21)
AND cln.price_list_id is null ;
Select okch.currency_code
From okc_k_headers_b okch
Where okch.id = p_id ;
l_restricted_update VARCHAR2(100) ;
l_can_update_yn VARCHAR2(1);
l_update_level_code VARCHAR2(300);
l_update_level_value_id VARCHAR2(300);
l_update_level VARCHAR2(360);
l_update_level_value VARCHAR2(360);
l_stmt:= 'SELECT cle.id,object_version_number,lse_id FROM okc_k_lines_v cle
WHERE cle.date_cancelled is NULL
AND cle.dnz_chr_id = :p_chr_id';
l_stmt:= 'SELECT ksl.id,ksl.object_version_number,ksl.invoice_text
FROM oks_k_lines_v ksl, okc_k_lines_b kcl
WHERE kcl.date_cancelled is NULL
AND kcl.dnz_chr_id = :p_chr_id
AND kcl.id = ksl.cle_id
AND kcl.lse_id IN (7,8,9,10,11,18,25,35,1,12,14,19)';
SELECT meaning FROM fnd_lookups
WHERE lookup_type like 'OKS_MSCHG_LEVEL%'
AND lookup_code = p_lookup_code
AND rownum =1;
SELECT estimated_amount FROM okc_k_headers_v WHERE id = p_chr_id;
SELECT ste_code FROM okc_statuses_v WHERE code = sts_code;
SELECT id FROM okc_k_headers_b okh
WHERE
Exists (select 'x' from OKC_K_LINES_B cle
where cle.dnz_chr_id = okh.id
and cle.lse_id = 14 )
AND okh.id = p_chr_id ;
Select code
From OKC_STATUSES_B
Where ste_code = p_ste_code
And default_yn = 'Y';
l_restricted_update := 'F' ;
,l_update_level_code
,l_update_level_value_id
,l_attribute_code
,l_old_value_id_tmp
,l_new_value_id;
l_criteria_rec.update_level := l_update_level_code;
l_criteria_rec.update_level_value := l_update_level_value_id;
l_update_level := Get_lookup_value(p_lookup_code => l_update_level_code);
l_update_level_value := OKC_UTIL.GET_NAME_FROM_JTFV(p_object_code =>l_update_level_code,
p_id1 => l_update_level_value_id,
p_id2 => '#');
LOG_MESSAGES('Update_level (Stored Code) :'||l_criteria_rec.update_level);
LOG_MESSAGES('Update_level_value (Stored Code) :'||l_criteria_rec.update_level_value);
LOG_MESSAGES('Update Level : '||l_update_level);
LOG_MESSAGES('Update Level value : '||l_update_level_value);
LOG_MESSAGES('Updating all selected (A,E) operation lines STATUS to NULL ...'||l_eligible_contracts_tbl.COUNT);
UPDATE_LINE_STATUS(p_oie_id => l_criteria_rec.oie_id);
l_chrv_tbl_in.DELETE;
LOG_MESSAGES('Fetching contract lines for update');
l_clev_tbl_in.DELETE;
LOG_MESSAGES('Locking contract lines for update, lines count:'||l_clev_tbl_in.COUNT);
l_klnv_tbl_type_in.DELETE;
LOG_MESSAGES('Locking oks contract lines for update, lines count:'||l_klnv_tbl_type_in.COUNT);
OKC_CONTRACT_PUB.update_contract_header (
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_chrv_rec => l_chrv_rec_in,
x_chrv_rec => l_chrv_rec_out );
IF l_return_status = G_RET_STS_SUCCESS THEN -- contract update
LOG_MESSAGES('CONTRACT_HEADER(Start_Date) update status: '||l_return_status);
OKC_CONTRACT_PUB.update_contract_line(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_clev_tbl => l_clev_tbl_in,
x_clev_tbl => l_clev_tbl_out);
LOG_MESSAGES('CONTRACT_HEADER_LINES(Start_Date) update status: '||l_return_status);
oks_contract_line_pub.update_line
(p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_klnv_tbl => l_klnv_tbl_type_in,
x_klnv_tbl => l_klnv_tbl_type_out,
p_validate_yn => 'N'
);
LOG_MESSAGES('CONTRACT_HEADER_OKS_LINES(Start_Date) update status: '||l_return_status);
LOG_MESSAGES('CONTRACT_OKS_LINES(Contract_Start_Date) update status: '||l_return_status);
LOG_MESSAGES('Contract oks Lines(Contract_Start_Date) Update failed;'||l_message);
LOG_MESSAGES('CONTRACT_LINES(Contract_Start_Date) update status: '||l_return_status);
LOG_MESSAGES('Contract Lines(Contract_Start_Date) Update failed;'||l_message);
END IF; -- contract line update status
LOG_MESSAGES('CONTRACT_HEADERS(Contract_Start_Date) update status: '||l_return_status);
LOG_MESSAGES('Contract Header(Contract_Start_Date) Update failed;'||l_message);
END IF; -- Contract update
l_chrv_tbl_in.DELETE;
LOG_MESSAGES('Fetching contract lines for update');
l_clev_tbl_in.DELETE;
LOG_MESSAGES('Locking contract lines for update, lines count:'||l_clev_tbl_in.COUNT);
l_klnv_tbl_type_in.DELETE;
LOG_MESSAGES('Locking oks contract lines for update, lines count:'||l_klnv_tbl_type_in.COUNT);
OKC_CONTRACT_PUB.update_contract_header (
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_chrv_rec => l_chrv_rec_in,
x_chrv_rec => l_chrv_rec_out );
IF l_return_status = G_RET_STS_SUCCESS THEN -- contract update
LOG_MESSAGES('CONTRACT_HEADER(End_Date) update status: '||l_return_status);
OKC_CONTRACT_PUB.update_contract_line(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_clev_tbl => l_clev_tbl_in,
x_clev_tbl => l_clev_tbl_out);
LOG_MESSAGES('CONTRACT_HEADER_LINES(End_Date) update status: '||l_return_status);
oks_contract_line_pub.update_line
(p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_klnv_tbl => l_klnv_tbl_type_in,
x_klnv_tbl => l_klnv_tbl_type_out,
p_validate_yn => 'N'
);
LOG_MESSAGES('CONTRACT_HEADER_OKS_LINES(Start_Date) update status: '||l_return_status);
LOG_MESSAGES('CONTRACT_OKS_LINES(Contract_Start_Date) update status: '||l_return_status);
LOG_MESSAGES('Contract oks Lines(Contract_Start_Date) Update failed;'||l_message);
LOG_MESSAGES('CONTRACT_LINES(Contract_End_Date) update status: '||l_return_status);
LOG_MESSAGES('Contract Lines(Contract_End_Date) Update failed;'||l_message);
END IF; -- contract line update status
LOG_MESSAGES('CONTRACT_HEADERS(Contract_End_Date) update status: '||l_return_status);
LOG_MESSAGES('Contract Header(Contract_End_Date) Update failed;'||l_message);
END IF; -- Contract update
End If; -- update attribute type( l_criteria_rec.attribute )
OKC_CTC_PVT.update_row(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_ctcv_rec => l_ctcv_rec_in,
x_ctcv_rec => l_ctcv_rec_out);
LOG_MESSAGES('SALES_PERSON in OKC_CONTACTS update status: '||l_return_status);
LOG_MESSAGES('SALESPERSON in OKC_CONTACTS update status: '||l_return_status);
LOG_MESSAGES('SALES Person in OKC_CONTACTS Update failed;'||l_message);
l_scrv_tbl_in.DELETE;
OKS_SALES_CREDIT_PUB.update_Sales_credit(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_scrv_tbl => l_scrv_tbl_in,
x_scrv_tbl => l_scrv_tbl_out);
LOG_MESSAGES('SALES_PERSON in OKS_SALES_CREDIT update status: '||l_return_status);
LOG_MESSAGES('SALES_PERSON in OKS_SALES_CREDIT update status: '||l_return_status);
LOG_MESSAGES('SALES_PERSON in OKS_SALES_CREDIT Update failed;'||l_message);
OKS_REV_DISTR_PUB.update_Revenue_Distr(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_rdsv_rec => l_rdsv_rec_in,
x_rdsv_rec => l_rdsv_rec_out);
LOG_MESSAGES('REVENUE_ACCOUNT update status: '||l_return_status);
LOG_MESSAGES('REVENUE_ACCOUNT update status: '||l_return_status);
LOG_MESSAGES('REVENUE_ACCOUNT Update failed;'||l_message);
OKC_CTC_PVT.update_row(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_ctcv_rec => l_ctcv_rec_in,
x_ctcv_rec => l_ctcv_rec_out);
LOG_MESSAGES('PARTY_SHIPPING_CONTACT update status: '||l_return_status);
LOG_MESSAGES('PARTY_SHIPPING_CONTACT update status: '||l_return_status);
LOG_MESSAGES('Party Shipping Contact Update failed;'||l_message);
OKC_CTC_PVT.update_row(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_ctcv_rec => l_ctcv_rec_in,
x_ctcv_rec => l_ctcv_rec_out);
LOG_MESSAGES('After OKC_CTC_PVT.update_row, status:'||l_return_status);
LOG_MESSAGES('PARTY_BILLING_CONTACT update status: '||l_return_status);
LOG_MESSAGES('PARTY_BILLING_CONTACT update status: '||l_return_status);
LOG_MESSAGES('Party Billing Contact Update failed;'||l_message);
l_ctcv_tbl_in.DELETE;
l_ctcv_tbl_out.DELETE;
OKC_CTC_PVT.update_row(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_ctcv_tbl => l_ctcv_tbl_in,
x_ctcv_tbl => l_ctcv_tbl_out);
LOG_MESSAGES('LINE_SHIPPING_CONTACT update status: '||l_return_status);
LOG_MESSAGES('LINE_SHIPPING_CONTACT update status: '||l_return_status);
LOG_MESSAGES('Line Shipping Contact Update failed;'||l_message);
l_ctcv_tbl_in.DELETE;
l_ctcv_tbl_out.DELETE;
OKC_CTC_PVT.update_row(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_ctcv_tbl => l_ctcv_tbl_in,
x_ctcv_tbl => l_ctcv_tbl_out);
LOG_MESSAGES('LINE_BILLING_CONTACT update status: '||l_return_status);
LOG_MESSAGES('LINE_BILLING_CONTACT update status: '||l_return_status);
LOG_MESSAGES('Line Billing Contact Update failed;'||l_message);
OKC_CONTRACT_PUB.update_contract_header(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_restricted_update => 'F' ,
p_chrv_rec => l_chrv_rec_in,
x_chrv_rec => l_chrv_rec_out);
LOG_MESSAGES('SHIP_TO_ADDRESS update status: '||l_return_status);
LOG_MESSAGES('SHIP_TO_ADDRESS update status: '||l_return_status);
LOG_MESSAGES('Ship-To Address Update failed;'||l_message);
OKC_CONTRACT_PUB.update_contract_header(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_restricted_update => 'F' ,
p_chrv_rec => l_chrv_rec_in,
x_chrv_rec => l_chrv_rec_out);
LOG_MESSAGES('PAYMENT_TERM update status: '||l_return_status);
LOG_MESSAGES('PAYMENT_TERM update status: '||l_return_status);
LOG_MESSAGES('PAYMENT_TERM Update failed;'||l_message);
OKC_CONTRACT_PUB.update_contract_header(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_restricted_update => 'F' ,
p_chrv_rec => l_chrv_rec_in,
x_chrv_rec => l_chrv_rec_out);
LOG_MESSAGES('CON_RENEWAL_TYPE update status: '||l_return_status);
LOG_MESSAGES('CON_RENEWAL_TYPE update status: '||l_return_status);
LOG_MESSAGES('CON_RENEWAL_TYPE Update failed;'||l_message);
OKS_CONTRACT_HDR_PUB.update_header(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_khrv_rec => l_khrv_rec_type_in,
x_khrv_rec => l_khrv_rec_type_out,
p_validate_yn => 'Y');
LOG_MESSAGES('CON_RENEWAL_TYPE update status: '||l_return_status);
LOG_MESSAGES('CON_RENEWAL_TYPE update status: '||l_return_status);
LOG_MESSAGES('CON_RENEWAL_TYPE Update failed;'||l_message);
OKS_CONTRACT_HDR_PUB.update_header(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_khrv_rec => l_khrv_rec_type_in,
x_khrv_rec => l_khrv_rec_type_out,
p_validate_yn => 'Y');
LOG_MESSAGES('ACCT_RULE update status: '||l_return_status);
LOG_MESSAGES('ACCT_RULE update status: '||l_return_status);
LOG_MESSAGES('ACCT_RULE Update failed;'||l_message);
OKS_CONTRACT_HDR_PUB.update_header(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_khrv_rec => l_khrv_rec_type_in,
x_khrv_rec => l_khrv_rec_type_out,
p_validate_yn => 'Y');
LOG_MESSAGES('SUMMARY_PRINT update status: '||l_return_status);
LOG_MESSAGES('SUMMARY_PRINT update status: '||l_return_status);
LOG_MESSAGES('SUMMARY_PRINT Update failed;'||l_message);
OKS_CONTRACT_HDR_PUB.update_header(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_khrv_rec => l_khrv_rec_type_in,
x_khrv_rec => l_khrv_rec_type_out,
p_validate_yn => 'Y');
LOG_MESSAGES('PO Required update status: '||l_return_status);
LOG_MESSAGES('PO Required update status: '||l_return_status);
LOG_MESSAGES('PO Required Update failed;'||l_message);
OKC_CONTRACT_PUB.update_contract_header(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_restricted_update => 'F' ,
p_chrv_rec => l_chrv_rec_in,
x_chrv_rec => l_chrv_rec_out);
LOG_MESSAGES('INV_RULE update status: '||l_return_status);
Update Okc_k_lines_b set inv_rule_id = l_new_value_id
Where id = rec_line_id.id;
oks_bill_sch.update_bs_interface_date(
p_top_line_id => rec_line_id.id,
p_invoice_rule_id => l_new_value_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
LOG_MESSAGES('INV_RULE update_bs_interface_date: '||l_return_status);
LOG_MESSAGES('INV_RULE update_bs_interface_date: '||l_return_status);
LOG_MESSAGES('INV_RULE update_bs_interface_date Update failed;'||l_message);
LOG_MESSAGES('INV_RULE update status: '||l_return_status);
LOG_MESSAGES('INV_RULE Update failed;'||l_message);
l_klnv_tbl_type_in.DELETE;
OKS_CONTRACT_LINE_PUB.update_line(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_klnv_tbl => l_klnv_tbl_type_in,
x_klnv_tbl => l_klnv_tbl_type_out,
p_validate_yn => 'Y');
LOG_MESSAGES('COV_TYPE update status: '||l_return_status);
LOG_MESSAGES('COV_TYPE update status: '||l_return_status);
LOG_MESSAGES('COV_TYPE Update failed;'||l_message);
l_clev_tbl_in.DELETE;
l_clev_tbl_out.DELETE;
OKC_CONTRACT_PUB.update_contract_line(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_clev_tbl => l_clev_tbl_in,
x_clev_tbl => l_clev_tbl_out);
LOG_MESSAGES('BP Price List update status: '||l_return_status);
LOG_MESSAGES('BP Price List update status: '||l_return_status);
LOG_MESSAGES('BP Price List Update failed;'||l_message);
l_ctz_tblType_in.delete;
l_ctz_tblType_out.delete;
OKS_CTZ_PVT.update_row(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_oks_coverage_timezones_v_tbl => l_ctz_tblType_in,
x_oks_coverage_timezones_v_tbl => l_ctz_tblType_out);
LOG_MESSAGES('Coverage Timezone update status: '||l_return_status);
LOG_MESSAGES('Coverage Timezone update status: '||l_return_status);
LOG_MESSAGES('Coverage Timezone Update failed;'||l_message);
l_ctcv_tbl_in.DELETE;
l_ctcv_tbl_out.DELETE;
OKC_CTC_PVT.update_row(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_ctcv_tbl => l_ctcv_tbl_in,
x_ctcv_tbl => l_ctcv_tbl_out);
LOG_MESSAGES('PREF_ENGG update status: '||l_return_status);
LOG_MESSAGES('PREF_ENGG update status: '||l_return_status);
LOG_MESSAGES('PREF_ENGG Update failed;'||l_message);
l_ctcv_tbl_in.DELETE;
l_ctcv_tbl_out.DELETE;
OKC_CTC_PVT.update_row(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_ctcv_tbl => l_ctcv_tbl_in,
x_ctcv_tbl => l_ctcv_tbl_out);
LOG_MESSAGES('RES_GROUP update status: '||l_return_status);
LOG_MESSAGES('RES_GROUP update status: '||l_return_status);
LOG_MESSAGES('RES_GROUP Update failed;'||l_message);
l_gvev_tbl_in.DELETE;
l_gvev_tbl_out.DELETE;
OKC_GVE_PVT.update_row(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_gvev_tbl => l_gvev_tbl_in,
x_gvev_tbl => l_gvev_tbl_out);
LOG_MESSAGES('AGREEMENT_NAME update status: '||l_return_status);
LOG_MESSAGES('AGREEMENT_NAME update status: '||l_return_status);
LOG_MESSAGES('AGREEMENT_NAME Update failed;'||l_message);
OKC_CONTRACT_PUB.update_contract_header(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_restricted_update => 'F' ,
p_chrv_rec => l_chrv_rec_in,
x_chrv_rec => l_chrv_rec_out);
LOG_MESSAGES('BILL_TO_ADDRESS update status: '||l_return_status);
LOG_MESSAGES('BILL_TO_ADDRESS update status: '||l_return_status);
LOG_MESSAGES('Bill-To Address Update failed;'||l_message);
OKC_CONTRACT_PUB.update_contract_header(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_restricted_update => 'F' ,
p_chrv_rec => l_chrv_rec_in,
x_chrv_rec => l_chrv_rec_out);
LOG_MESSAGES('PRICE_LIST update status: '||l_return_status);
LOG_MESSAGES('PRICE_LIST update status: '||l_return_status);
LOG_MESSAGES('Price List Update failed;'||l_message);
l_cvt_tbl_in.DELETE;
l_cvt_tbl_out.DELETE;
OKS_CVT_PVT.update_row( p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_oks_coverage_times_v_tbl => l_cvt_tbl_in,
x_oks_coverage_times_v_tbl => l_cvt_tbl_out);
LOG_MESSAGES('COVERAGE_START_TIME update status: '||l_return_status);
LOG_MESSAGES('Coverage Start Time Update failed;'||l_message);
LOG_MESSAGES('COVERAGE_START_TIME update status: '||l_return_status);
LOG_MESSAGES('Coverage Start Time Update failed;'||l_message);
l_cvt_tbl_in.DELETE;
l_cvt_tbl_out.DELETE;
OKS_CVT_PVT.update_row( p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_oks_coverage_times_v_tbl => l_cvt_tbl_in,
x_oks_coverage_times_v_tbl => l_cvt_tbl_out);
LOG_MESSAGES('COVERAGE_END_TIME update status: '||l_return_status);
LOG_MESSAGES('Coverage End Time Update failed;'||l_message);
LOG_MESSAGES('COVERAGE_END_TIME update status: '||l_return_status);
LOG_MESSAGES('Coverage End Time Update failed;'||l_message);
l_act_tbl_in.delete;
l_act_tbl_out.delete;
OKS_ACM_PVT.update_row (
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_oks_action_times_v_tbl => l_act_tbl_in,
x_oks_action_times_v_tbl => l_act_tbl_out );
LOG_MESSAGES('Reaction update status: '||l_return_status);
LOG_MESSAGES('Reaction Time update status: '||l_return_status);
LOG_MESSAGES('Reaction Time Update failed;'||l_message);
l_act_tbl_in.delete;
l_act_tbl_out.delete;
OKS_ACM_PVT.update_row (
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_oks_action_times_v_tbl => l_act_tbl_in,
x_oks_action_times_v_tbl => l_act_tbl_out );
LOG_MESSAGES('Resolution update status: '||l_return_status);
LOG_MESSAGES('Resolution Time update status: '||l_return_status);
LOG_MESSAGES('Resolution Time Update failed;'||l_message);
OKC_CONTRACT_PUB.update_contract_header (
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_chrv_rec => l_chrv_rec_in,
x_chrv_rec => l_chrv_rec_out );
LOG_MESSAGES('CONTRACT_HEADER(Cognomen) update status: '||l_return_status);
LOG_MESSAGES('CONTRACT_HEADER(Cognomen) update status: '||l_return_status);
LOG_MESSAGES('Contract Header(Cognomen) Update failed;'||l_message);
OKC_CONTRACT_PUB.update_contract_header (
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_chrv_rec => l_chrv_rec_in,
x_chrv_rec => l_chrv_rec_out );
LOG_MESSAGES('CONTRACT_HEADER(Cust PO Number) update status: '||l_return_status);
LOG_MESSAGES('CONTRACT_HEADER(Cust PO Number) update status: '||l_return_status);
LOG_MESSAGES('Contract Header(Cust PO Number) Update failed;'||l_message);
l_clev_tbl_in.delete;
LOG_MESSAGES('Fetching contract lines for update');
LOG_MESSAGES('Locking contract lines for update');
OKC_CONTRACT_PUB.update_contract_line(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_clev_tbl => l_clev_tbl_in,
x_clev_tbl => l_clev_tbl_out);
LOG_MESSAGES('PRODUCT_ALIAS update status: '||l_return_status);
LOG_MESSAGES('PRODUCT_ALIAS update status: '||l_return_status);
LOG_MESSAGES('PRODUCT_ALIAS Update failed;'||l_message);
l_clev_tbl_in.delete;
LOG_MESSAGES('Fetching contract lines for update');
LOG_MESSAGES('Locking contract lines for update');
OKC_CONTRACT_PUB.update_contract_line(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_clev_tbl => l_clev_tbl_in,
x_clev_tbl => l_clev_tbl_out);
LOG_MESSAGES('CONTRACT_LINE_REF update status: '||l_return_status);
LOG_MESSAGES('CONTRACT_LINE_REF(Cognomen) update status: '||l_return_status);
LOG_MESSAGES('CONTRACT_LINE_REF(Cognomen) Update failed;'||l_message);
LOG_MESSAGES('CONTRACT_LINE_REF(Cognomen) update status: '||l_return_status);
LOG_MESSAGES('CONTRACT_LINE_REF(Cognomen) Update failed;'||l_message);
OKC_CONTRACT_GROUP_PUB.update_contract_grpngs(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_cgcv_rec => l_cgcv_rec_in,
x_cgcv_rec => l_cgcv_rec_out);
LOG_MESSAGES('CONTRACT_GROUP update status: '||l_return_status);
LOG_MESSAGES('CONTRACT_GROUP update status: '||l_return_status);
LOG_MESSAGES('Contract Group Update failed;'||l_message);
l_chrv_tbl_in.DELETE;
LOG_MESSAGES('Fetching contract lines for update');
l_clev_tbl_in.DELETE;
LOG_MESSAGES('Locking contract lines for update, lines count:'||l_clev_tbl_in.COUNT);
l_klnv_tbl_type_in.DELETE;
LOG_MESSAGES('Locking oks contract lines for update, lines count:'||l_klnv_tbl_type_in.COUNT);
OKC_CONTRACT_PUB.update_contract_header (
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_chrv_rec => l_chrv_rec_in,
x_chrv_rec => l_chrv_rec_out );
LOG_MESSAGES('CONTRACT_HEADER(Start_Date) update status: '||l_return_status);
LOG_MESSAGES('CONTRACT_HEADERS(Contract_Start_Date) update status: '||l_return_status);
LOG_MESSAGES('Contract Header(Contract_Start_Date) Update failed;'||l_message);
OKC_CONTRACT_PUB.update_contract_line(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_clev_tbl => l_clev_tbl_in,
x_clev_tbl => l_clev_tbl_out);
LOG_MESSAGES('CONTRACT_HEADER_LINES(Start_Date) update status: '||l_return_status);
oks_contract_line_pub.update_line
(p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_klnv_tbl => l_klnv_tbl_type_in,
x_klnv_tbl => l_klnv_tbl_type_out,
p_validate_yn => 'N'
);
LOG_MESSAGES('CONTRACT_HEADER_OKS_LINES(Start_Date) update status: '||l_return_status);
LOG_MESSAGES('CONTRACT_OKS_LINES(Contract_Start_Date) update status: '||l_return_status);
LOG_MESSAGES('Contract oks Lines(Contract_Start_Date) Update failed;'||l_message);
LOG_MESSAGES('CONTRACT_START_DATE Update_CONTRACT_LINES status: '||l_return_status||',msg_count:'||l_msg_count);
LOG_MESSAGES('Contract Lines Update(CONTRACT_START_DATE) failed;'||l_message);
l_chrv_tbl_in.DELETE;
LOG_MESSAGES('Fetching contract lines for update');
l_clev_tbl_in.DELETE;
LOG_MESSAGES('Locking contract lines for update, lines count:'||l_clev_tbl_in.COUNT);
l_klnv_tbl_type_in.DELETE;
LOG_MESSAGES('Locking oks contract lines for update, lines count:'||l_klnv_tbl_type_in.COUNT);
OKC_CONTRACT_PUB.update_contract_header (
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_chrv_rec => l_chrv_rec_in,
x_chrv_rec => l_chrv_rec_out );
LOG_MESSAGES('CONTRACT_HEADER(End_Date) update status: '||l_return_status);
LOG_MESSAGES('CONTRACT_HEADERS(Contract_End_Date) update status: '||l_return_status);
LOG_MESSAGES('Contract Header(Contract_End_Date) Update failed;'||l_message);
OKC_CONTRACT_PUB.update_contract_line(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_clev_tbl => l_clev_tbl_in,
x_clev_tbl => l_clev_tbl_out);
LOG_MESSAGES('CONTRACT_HEADER_LINES(End_Date) update status: '||l_return_status);
oks_contract_line_pub.update_line
(p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_klnv_tbl => l_klnv_tbl_type_in,
x_klnv_tbl => l_klnv_tbl_type_out,
p_validate_yn => 'N'
);
LOG_MESSAGES('CONTRACT_HEADER_OKS_LINES(End_Date) update status: '||l_return_status);
LOG_MESSAGES('CONTRACT_OKS_LINES(Contract_End_Date) update status: '||l_return_status);
LOG_MESSAGES('Contract oks Lines(Contract_End_Date) Update failed;'||l_message);
LOG_MESSAGES('CONTRACT_End_DATE Update_CONTRACT_LINES status: '||l_return_status||',msg_count:'||l_msg_count);
LOG_MESSAGES('Contract Lines Update(CONTRACT_End_DATE) failed;'||l_message);
l_msg_tbl.DELETE;
OKC_OPER_INST_PUB.Update_Operation_Line (
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_olev_rec => l_olev_rec_in,
x_olev_rec => l_olev_rec_out);
OKC_OPER_INST_PUB.Update_Operation_Line (
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_olev_rec => l_olev_rec_in,
x_olev_rec => l_olev_rec_out);
OKC_OPER_INST_PUB.Update_Operation_Line (
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_olev_rec => l_olev_rec_in,
x_olev_rec => l_olev_rec_out);
OKC_OPER_INST_PUB.Update_Operation_Line (
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_olev_rec => l_olev_rec_in,
x_olev_rec => l_olev_rec_out);
OKC_OPER_INST_PUB.Update_Operation_Line (
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_olev_rec => l_olev_rec_in,
x_olev_rec => l_olev_rec_out);
OKC_OPER_INST_PUB.Update_Operation_Line (
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_olev_rec => l_olev_rec_in,
x_olev_rec => l_olev_rec_out);
OKC_OPER_INST_PUB.Update_Operation_Line (
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_olev_rec => l_olev_rec_in,
x_olev_rec => l_olev_rec_out);
LOG_MESSAGES(' OKC_OPER_INST_PUB.Update_Operation_Line: '||l_return_status);
LOG_MESSAGES('inel Update_Operation_Line: '||l_msg_data);
OKC_OPER_INST_PUB.Update_Operation_Instance (
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_oiev_rec => l_oiev_rec_in,
x_oiev_rec => l_oiev_rec_out);
||': '||l_update_level);
||': '|| l_update_level_value);
OKC_OPER_INST_PUB.Update_Operation_Instance (
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_oiev_rec => l_oiev_rec_in,
x_oiev_rec => l_oiev_rec_out);
LOG_MESSAGES('Update_Operation_instance status:'||l_return_status);
PROCEDURE UPDATE_CONTRACT(p_chrv_rec IN okc_contract_pub.chrv_rec_type,
x_return_status OUT NOCOPY VARCHAR2) IS
l_api_version CONSTANT NUMBER := 1.0;
LOG_MESSAGES(' UPDATEing Contract, header_id = ' ||p_chrv_rec.id);
okc_contract_pub.update_contract_header (
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_chrv_rec => p_chrv_rec,
x_chrv_rec => l_chrv_rec_out );
LOG_MESSAGES('okc_contract_pub.update_contract_header l_return_status = ' || l_return_status);
LOG_MESSAGES('okc_contract_pub.update_contract_header l_msg_data = ' || l_msg_data);
LOG_MESSAGES('okc_contract_pub.update_contract_header l_msg_data = ' || l_msg_data);
END UPDATE_CONTRACT;
SELECT ID from OKC_CLASS_OPERATIONS_V
WHERE OPN_CODE = 'MASS_CHANGE' and CLS_CODE = 'SERVICE';
l_oiev_tbl_in(1).object1_id1 := p_oie_rec.update_level_value;
l_oiev_tbl_in(1).jtot_object1_code := p_oie_rec.update_level;
l_oiev_tbl_in(1).last_updated_by := NULL ; --OKC_API.G_MISS_NUM;
l_oiev_tbl_in(1).last_update_date := SYSDATE;
l_oiev_tbl_in(1).last_update_login := NULL ; --OKC_API.G_MISS_NUM;
l_omrv_rec_in.last_update_date := SYSDATE;
l_omrv_rec_in.last_update_login := NULL ; --OKC_API.G_MISS_NUM;
l_mod_rec_in.last_updated_by := NULL ; --OKC_API.G_MISS_NUM;
l_mod_rec_in.last_update_date := SYSDATE;
OKS_MOD_PVT.insert_row( p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_OksMschgOperationsDtlsVRec => l_mod_rec_in,
XOksMschgOperationsDtlsVRec => l_mod_rec_out);
PROCEDURE UPDATE_OPERATION_INSTANCES (p_oie_rec IN opr_instance_rec_type,
p_mrd_rec IN masschange_request_rec_type,
x_return_status OUT NOCOPY Varchar2) is
------------------------------------------------------------------
---TAPI variables
------------------------------------------------------------------
l_api_version CONSTANT NUMBER := 1.0;
SELECT id from okc_masschange_req_dtls_v
WHERE oie_id = p_oie_id;
l_oiev_tbl_in(1).object1_id1 := p_oie_rec.update_level_value;
l_oiev_tbl_in(1).jtot_object1_code := p_oie_rec.update_level;
OKC_OPER_INST_PUB.UPdate_Operation_Instance(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_oiev_rec => l_oiev_tbl_in(1),
x_oiev_rec => l_oiev_tbl_out(1));
OKC_OPER_INST_PUB.Update_Masschange_Dtls(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_mrdv_rec => l_omrv_rec_in,
x_mrdv_rec => l_omrv_rec_out);
OKC_OPER_INST_PUB.UPdate_Operation_Instance(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_oiev_rec => l_oiev_tbl_in(1),
x_oiev_rec => l_oiev_tbl_out(1));
END UPDATE_OPERATION_INSTANCES;
PROCEDURE DELETE_OPERATION_INSTANCES (p_oie_rec IN opr_instance_rec_type,
x_return_status OUT NOCOPY Varchar2) is
------------------------------------------------------------------
---TAPI variables
------------------------------------------------------------------
l_api_version CONSTANT NUMBER := 1.0;
OKC_OPER_INST_PUB.Delete_Masschange_Dtls(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_mrdv_rec => l_omrv_rec_in);
OKC_OPER_INST_PUB.Delete_Operation_Instance(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_oiev_rec => l_oiev_tbl_in(1));
END DELETE_OPERATION_INSTANCES;
l_omrv_rec_in.last_update_date := SYSDATE;
l_omrv_rec_in.last_update_login := NULL ; --OKC_API.G_MISS_NUM;
l_mod_rec_in.last_updated_by := NULL ; --OKC_API.G_MISS_NUM;
l_mod_rec_in.last_update_date := SYSDATE;
OKS_MOD_PVT.insert_row( p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_OksMschgOperationsDtlsVRec => l_mod_rec_in,
XOksMschgOperationsDtlsVRec => l_mod_rec_out);
l_olev_tbl_in(i).select_yn := p_ole_tbl(j).select_yn;
l_olev_tbl_in(i).last_updated_by := NULL ; --OKC_API.G_MISS_NUM;
l_olev_tbl_in(i).last_update_date := SYSDATE;
l_olev_tbl_in(i).last_update_login := NULL ; --OKC_API.G_MISS_NUM;
l_olev_tbl_in(i).program_update_date := NULL ; --OKC_API.G_MISS_DATE;
PROCEDURE DELETE_OPERATION_LINES (p_oie_id IN Number,
x_return_status OUT NOCOPY Varchar2) IS
----------------------------------------------------------------------------
---TAPI variables
----------------------------------------------------------------------------
l_api_version CONSTANT NUMBER := 1.0;
SELECT ole.id ole_id,omr.id omr_id
from OKC_OPERATION_LINES_V ole, OKC_MASSCHANGE_REQ_DTLS_V omr
where ole.id = omr.ole_id
AND omr.oie_id IS NULL
AND ole.oie_id = p_oie_id;
OKC_OPER_INST_PUB.Delete_Masschange_Dtls (
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_mrdv_tbl => l_omrv_tbl_in);
OKC_OPER_INST_PUB.Delete_Operation_Line(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_olev_tbl => l_olev_tbl_in);
END DELETE_OPERATION_LINES;
PROCEDURE UPDATE_OPERATION_LINES(p_ole_id IN NUMBER,
p_select_yn IN VARCHAR2,
p_qa_check_yn IN VARCHAR2 ) IS
l_api_version CONSTANT NUMBER := 1.0;
l_olev_tbl_in(1).select_yn := p_select_yn ;
OKC_OPER_INST_PUB.Update_Operation_Line(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_olev_tbl => l_olev_tbl_in,
x_olev_tbl => l_olev_tbl_out );
LOG_MESSAGES('OKC_OPER_INST_PUB.Update_Operation_Line l_return_status = ' || l_return_status);
LOG_MESSAGES('OKC_OPER_INST_PUB.Update_Operation_Line l_msg_data = ' || l_msg_data);
LOG_MESSAGES('OKC_OPER_INST_PUB.Update_Operation_Line l_msg_data = ' || l_msg_data);
Select mcd.id , mcd.mrd_id into p_mod_id ,p_mod_mrd_id
from oks_mschg_operations_dtls mcd
where mcd.ole_id = p_ole_id ;
OKS_MOD_PVT.Update_row(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_OksMschgOperationsDtlsVRec => l_mod_tbl_in,
XOksMschgOperationsDtlsVRec => l_mod_tbl_out );
END UPDATE_OPERATION_LINES;
SELECT name
FROM okc_k_groups_v
WHERE id = p_id;
SELECT name
FROM okc_k_groups_v
WHERE id = p_id;
SELECT gcck.concatenated_segments
From gl_code_combinations_kfv gcck
, HR_ALL_ORGANIZATION_UNITS HOU
, HR_ORGANIZATION_INFORMATION HOI2
, GL_LEDGERS GSOB
WHERE HOU.ORGANIZATION_ID = HOI2.ORGANIZATION_ID
AND ( HOI2.ORG_INFORMATION_CONTEXT || '') ='Accounting Information'
AND HOI2.ORG_INFORMATION1 = TO_CHAR(GSOB.LEDGER_ID)
AND gcck.code_combination_id = p_ccid
and GSOB.chart_of_accounts_id = gcck.chart_of_accounts_id
and HOU.organization_id = p_organization_id
and GSOB.object_type_code = 'L'
AND nvl(GSOB.complete_flag, 'Y') = 'Y' ;
Select rep.name
From okx_salesreps_v rep
Where rep.id1 = p_id1
and rep.org_id = p_org_id ;
SELECT meaning
FROM oks_cov_types_v
WHERE code = p_attr_code ;
SELECT name
FROM okx_timezones_v
WHERE timezone_id = p_attr_code ;
SELECT EMP.FULL_NAME NAME
FROM JTF_RS_RESOURCE_EXTNS RSC
,FND_USER U
,OKX_PER_ALL_PEOPLE_V EMP
WHERE RSC.RESOURCE_ID = p_attr_code
AND RSC.CATEGORY = 'EMPLOYEE'
AND EMP.PERSON_ID = RSC.SOURCE_ID
AND U.USER_ID = RSC.USER_ID ;
SELECT name
FROM oks_resource_groups_v
WHERE id1 = p_attr_code ;
SELECT description
FROM oks_billing_profiles_v
WHERE id = p_attr_code ;
SELECT name
FROM okx_agreements_v
WHERE agreement_id = p_attr_code ;
SELECT SUBSTRB(P.PERSON_LAST_NAME,1,50) || ', ' ||
SUBSTRB(P.PERSON_FIRST_NAME,1,40) NAME
FROM HZ_CUST_ACCOUNT_ROLES CAR,
HZ_PARTIES P,
--NPALEPU
--29-JUN-2005
--TCA Project
--Replaced hz_party_relationships table with hz_relationships table
/* HZ_PARTY_RELATIONSHIPS PR, */
HZ_RELATIONSHIPS PR,
--END NPALEPU
HZ_ORG_CONTACTS OC
WHERE CAR.ROLE_TYPE = 'CONTACT'
AND PR.PARTY_ID = CAR.PARTY_ID
AND PR.CONTENT_SOURCE_TYPE = 'USER_ENTERED'
--NPALEPU
--29-JUN-2005
--TCA Project
--Replaced pr.party_relationship_id column with pr.relationship_id column and added new conditions
/* AND OC.PARTY_RELATIONSHIP_ID = PR.PARTY_RELATIONSHIP_ID */
AND OC.PARTY_RELATIONSHIP_ID = PR.RELATIONSHIP_ID
AND PR.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PR.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PR.DIRECTIONAL_FLAG = 'F'
--END NPALEPU
AND P.PARTY_ID = PR.SUBJECT_ID
--NPALEPU
--29-JUN-2005
--TCA Project
--Replaced dates check with status check as the 'Begin_date' and 'End_date' columns of hz_cust_account_roles table are migrated to 'Status' column
/* AND DECODE(SIGN(TRUNC(sysdate) -
TRUNC(NVL(car.begin_date,sysdate))),-1,'I', DECODE(SIGN(TRUNC(sysdate) -
TRUNC(NVL(car.end_date,sysdate))),1,'I','A')) = 'A' */
AND car.status = 'A'
--END NPALEPU
AND EXISTS
(SELECT 'X'
FROM hz_cust_acct_sites_all cas
WHERE cas.cust_account_id = car.cust_account_id)
AND CAR.CUST_ACCOUNT_ROLE_ID = p_attr_code ;
Select meaning
From fnd_lookups
Where lookup_code = p_attr_id
And lookup_type = 'OKS_SC_YES_NO' ;
Select meaning
From fnd_lookups
Where lookup_code = p_attr_id
And lookup_type in( 'OKC_RENEWAL_TYPE','OKS_RENEWAL_TYPE') ;
Select name
From okx_list_headers_v
Where id1 = p_attr_id
And list_type_code = 'PRL';
SELECT meaning FROM fnd_lookups
WHERE lookup_type = 'OKS_MASS_CHANGE_ATTRIBUTE'
AND lookup_code = p_lookup_code;
SELECT MEANING
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = p_type
AND LOOKUP_CODE = p_code ;
SELECT name FROM OKX_CUST_SITE_USES_V
WHERE id1 = p_attr_id
AND site_use_code = p_site_code
AND id2 = '#';
SELECT count(*) cnt FROM okc_class_operations_v
WHERE opn_code = 'MASS_CHANGE'
AND cls_code = 'SERVICE';
SELECT nvl(max(id),0) + 1 from okc_class_operations_v;
INSERT INTO okc_class_operations(
ID
,OPN_CODE
,CLS_CODE
,SEARCH_FUNCTION_ID
,DETAIL_FUNCTION_ID
,OBJECT_VERSION_NUMBER
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
--,SECURITY_GROUP_ID
,PDF_ID)
values
(l_opn_id
,'MASS_CHANGE'
,'SERVICE'
,NULL
,NULL
,10000
,1
,SYSDATE
,1
,SYSDATE
,0
--,NULL
,NULL);
PROCEDURE UPDATE_LINE_STATUS(p_oie_id IN Number) IS
PRAGMA AUTONOMOUS_TRANSACTION;
UPDATE okc_operation_lines
SET process_flag = NULL
WHERE oie_id = p_oie_id
AND select_yn = 'Y'
AND process_flag in ('A','E');
LOG_MESSAGES('UPDATE_LINE_STATUS: '||SQLERRM);
END UPDATE_LINE_STATUS;
PROCEDURE UPDATE_QA_CHECK_YN_COL IS
Cursor get_rec IS SELECT
mrd.id
,mrd.oie_id
,mrd.ole_id
,mrd.attribute_name
,mrd.old_value
,mrd.new_value
,mrd.object_version_number
,mrd.created_by
,mrd.creation_date
,mrd.last_updated_by
,mrd.last_update_date
,mrd.last_update_login
,mrd.security_group_id
,okh.sts_code status_code
FROM okc_masschange_req_dtls mrd,
okc_operation_lines opn,
okc_k_headers_b okh
WHERE mrd.ole_id = opn.id(+)
AND opn.subject_chr_id = okh.id(+) ;
l_mod_rec_in.last_updated_by := r1.last_updated_by ; --NULL ; --OKC_API.G_MISS_NUM;
l_mod_rec_in.last_update_date := r1.last_update_date ; --SYSDATE;
OKS_MOD_PVT.insert_row( p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_OksMschgOperationsDtlsVRec => l_mod_rec_in,
XOksMschgOperationsDtlsVRec => l_mod_rec_out);
END UPDATE_QA_CHECK_YN_COL ;