[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ID
FROM okc_class_operations
WHERE cls_code = (SELECT cls_code
FROM okc_subclasses_b
WHERE code = 'SERVICE')
AND opn_code = p_opn_code;
SELECT ID
FROM okc_operation_instances
WHERE cop_id = p_cop_id
AND target_chr_id = p_chr_id
AND batch_id = p_batch_id
AND status_code = 'PROCESSED';
Select 'X'
From Okc_operation_lines ol
,okc_operation_instances Ins
Where ol.subject_chr_id = P_target_chr_id
And ol.object_chr_id = p_source_chr_id
And ol.subject_cle_id Is Null
And ol.object_cle_id Is Null
And ins.id = p_id;
INSERT INTO OKC_OPERATION_INSTANCES(
id,
cop_id,
status_code,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
name,
target_chr_id,
request_id,
program_application_id,
program_id,
program_update_date,
jtot_object1_code,
object1_id1,
object1_id2,
batch_id
)
VALUES (
l_oper_instance_id,
l_cop_id,
'PROCESSED',
1,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
null,
p_target_chr_id,
decode(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,FND_GLOBAL.CONC_REQUEST_ID),
decode(FND_GLOBAL.PROG_APPL_ID,-1,NULL,FND_GLOBAL.PROG_APPL_ID),
decode(FND_GLOBAL.CONC_PROGRAM_ID,-1,NULL,FND_GLOBAL.CONC_PROGRAM_ID),
decode(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,SYSDATE),
null,
null,
null,
p_batch_id
);
INSERT INTO OKC_OPERATION_LINES(
id,
select_yn,
process_flag,
active_yn,
oie_id,
parent_ole_id,
subject_chr_id,
object_chr_id,
subject_cle_id,
object_cle_id,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
message_code)
VALUES (
okc_p_util.raw_to_number (SYS_GUID ()),
null,
'P',
'Y',
l_oper_instance_id,
null,
p_target_chr_id,
p_source_chr_id,
p_target_line_id,
p_source_line_id,
1,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
decode(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,FND_GLOBAL.CONC_REQUEST_ID),
decode(FND_GLOBAL.PROG_APPL_ID,-1,NULL,FND_GLOBAL.PROG_APPL_ID),
decode(FND_GLOBAL.CONC_PROGRAM_ID,-1,NULL,FND_GLOBAL.CONC_PROGRAM_ID),
decode(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,SYSDATE),
null);
INSERT INTO OKC_OPERATION_LINES(
id,
select_yn,
process_flag,
active_yn,
oie_id,
parent_ole_id,
subject_chr_id,
object_chr_id,
subject_cle_id,
object_cle_id,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
message_code)
VALUES (
okc_p_util.raw_to_number (SYS_GUID ()),
null,
'P',
'Y',
l_oper_instance_id,
null,
p_target_chr_id,
p_source_chr_id,
Null,
Null,
1,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
decode(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,FND_GLOBAL.CONC_REQUEST_ID),
decode(FND_GLOBAL.PROG_APPL_ID,-1,NULL,FND_GLOBAL.PROG_APPL_ID),
decode(FND_GLOBAL.CONC_PROGRAM_ID,-1,NULL,FND_GLOBAL.CONC_PROGRAM_ID),
decode(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,SYSDATE),
null);
Select ste_code
From Okc_statuses_b
Where code = p_sts_code;
Select code
From Okc_statuses_b
Where ste_code = p_ste_code
And default_yn = 'Y';
SELECT code
FROM okc_statuses_b
WHERE ste_code = p_ste_code
AND default_yn = 'Y';
SELECT a.code, a.ste_code
FROM okc_statuses_b a, okc_statuses_b b
WHERE b.code = p_sts_code
AND b.ste_code = a.ste_code
AND a.default_yn = 'Y';
g_module_current || 'oks_mass_update.initialize_okc_tbl',
'Begin'
);
x_okc_hdr_tbl (p_index).deleted_yn := okc_api.g_miss_char;
x_okc_hdr_tbl (p_index).program_update_date := okc_api.g_miss_date;
x_okc_hdr_tbl (p_index).last_updated_by := okc_api.g_miss_num;
x_okc_hdr_tbl (p_index).last_update_date := okc_api.g_miss_date;
x_okc_hdr_tbl (p_index).last_update_login := okc_api.g_miss_num;
g_module_current || 'oks_mass_update.initialize_oks_tbl',
'Begin'
);
x_oks_hdr_tbl (p_index).last_updated_by := okc_api.g_miss_num;
x_oks_hdr_tbl (p_index).last_update_date := okc_api.g_miss_date;
x_oks_hdr_tbl (p_index).last_update_login := okc_api.g_miss_num;
SELECT ID
FROM oks_stream_levels_v
WHERE cle_id = p_cle_id;
fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'oks_mass_update.check_strmlvl_exists',
'Begin' );
SELECT party_id
FROM hz_cust_accounts
WHERE cust_account_id = p_cust_id;
SELECT t.description NAME,
b.concatenated_segments description
FROM mtl_system_items_b_kfv b,
mtl_system_items_tl t
WHERE b.inventory_item_id = t.inventory_item_id
AND b.organization_id = t.organization_id
AND t.LANGUAGE = USERENV ('LANG')
AND b.inventory_item_id = p_product_item
AND ROWNUM < 2;
fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'oks_mass_update.get_invoice_text',
'l_formatted_invoice_text = ('|| l_formatted_invoice_text || ')' );
SELECT TO_CHAR (major_version)
FROM okc_k_vers_numbers
WHERE chr_id = p_chr_id;
SELECT application_id
FROM okc_k_headers_all_b
WHERE ID = p_chr_id;
g_module_current || 'oks_mass_update.set_attach_ses_vars',
'Begin'
);
p_last_update_date => SYSDATE,
p_last_updated_by => fnd_global.user_id,
p_last_update_login => fnd_global.login_id,
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL,
p_context => NULL,
p_jtf_note_contexts_tab => l_jtf_note_contexts_tab
);
SELECT b.jtf_note_id jtf_note_id,
b.source_object_code source_object_code,
b.note_status note_status,
b.note_type note_type,
b.notes notes,
b.notes_detail notes_detail
FROM jtf_notes_vl b
WHERE b.source_object_id = l_id;
g_module_current || 'oks_mass_update.get_notes_details',
'Begin'
);
l_notes_tbl.DELETE;
SELECT 'x'
FROM DUAL
WHERE p_acct_id IN (
SELECT ca1.cust_account_id id1
FROM hz_cust_accounts ca1,
hz_parties party
WHERE ca1.party_id = p_party_id
AND ca1.party_id = party.party_id
AND ca1.status = 'A'
UNION
SELECT ca2.cust_account_id id1
FROM hz_cust_accounts ca2,
hz_parties party1
WHERE ca2.party_id = party1.party_id
AND ca2.cust_account_id IN (
SELECT a.related_cust_account_id
FROM hz_cust_acct_relate_all a,
hz_cust_accounts b
WHERE b.cust_account_id = a.cust_account_id
AND b.party_id = p_party_id
AND b.status = 'A'
AND a.status = 'A'
AND a.org_id = p_org_id)
AND ca2.status = 'A');
g_module_current || 'oks_mass_update.create_csi_note',
'Account = '|| p_acct_id || 'is related to '|| p_party_id );
SELECT CS.SITE_USE_ID ID1
FROM HZ_CUST_SITE_USES_all CS
,HZ_PARTY_SITES PS
,HZ_CUST_ACCT_SITES_ALL CA
WHERE PS.PARTY_SITE_ID = CA.PARTY_SITE_ID
AND CA.CUST_ACCT_SITE_ID = CS.CUST_ACCT_SITE_ID
And Ps.party_id = p_party_id
And Ca.cust_account_id = p_customer_id
And Cs.site_use_code = p_code
AND Ps.identifying_address_flag = 'Y'
AND cs.status = 'A'
AND cs.org_id = p_org_id
;
SELECT 'x'
FROM okc_k_headers_all_b
WHERE contract_number = p_contract_number
AND contract_number_modifier = p_contract_number_modifier;
SELECT 'x'
FROM okc_k_groups_b
WHERE ID = p_group_id
AND public_yn = 'Y'
OR user_id = fnd_global.user_id;
SELECT 'x'
FROM hz_parties party
WHERE EXISTS (SELECT 'x'
FROM hz_cust_accounts acct
WHERE party.party_id = acct.party_id
AND acct.status = 'A')
AND party.party_id = p_party_id;
SELECT 'x'
FROM hz_relationships r,
hz_parties p3,
hz_parties p2,
hz_org_contacts oc
WHERE p2.party_id = r.subject_id
AND r.relationship_code IN ('CONTACT_OF', 'EMPLOYEE_OF')
AND r.content_source_type = 'USER_ENTERED'
AND p3.party_id = r.party_id
AND oc.party_relationship_id = r.relationship_id
AND r.object_id = p_party_id
AND trunc(p_trxn_date) BETWEEN NVL (r.start_date, SYSDATE)
AND NVL (r.end_date, SYSDATE)
AND r.status = 'A'
AND r.party_id = p_contact_id;
SELECT 'Y'
FROM hz_cust_site_uses_all a,
hz_cust_acct_sites_all b
WHERE a.site_use_id = p_site_use_id
AND a.site_use_code = p_site_use_code
AND a.cust_acct_site_id = b.cust_acct_site_id
AND a.status = 'A'
AND b.status = 'A'
AND a.org_id = p_org_id
AND b.cust_account_id = p_cust_acct_id;
SELECT 'Y'
From HZ_CUST_ACCOUNTS CA1
, HZ_PARTIES party
WHERE CA1.party_id = P_party_id
And CA1. cust_account_id = p_account_id
And CA1.party_id = party.party_id
And CA1.status = 'A'
UNION
SELECT 'Y'
FROM HZ_CUST_ACCOUNTS CA2
, HZ_CUST_ACCT_RELATE_ALL A
, HZ_CUST_ACCOUNTS B
WHERE CA2.cust_account_id = A.RELATED_CUST_ACCOUNT_ID
And B.CUST_ACCOUNT_ID = A.CUST_ACCOUNT_ID
And Ca2.cust_account_id = p_account_id
And B.party_id = p_party_id and B.status = 'A'
And A.status = 'A'
And A.org_id = p_org_id
And CA2.status = 'A';
SELECT object_chr_id
FROM okc_operation_instances op
, okc_class_operations cls
, okc_subclasses_b sl
, okc_operation_lines ol
WHERE ol.subject_chr_id = contract_id
And ol.subject_cle_id is null
And op.id = ol.oie_id
AND op.cop_id = cls.id
And cls.cls_code = sl.cls_code
And sl.code = 'SERVICE'
And cls.opn_code in ('RENEWAL','REN_CON');
Select 'x'
From Oks_instance_k_dtls_temp
Where contract_id = p_contract_id
And new_contract_id is not null;
SELECT DISTINCT (temp.new_contract_id) contract_id,
temp.contract_id,
kh.scs_code hdr_scs,
st.ste_code hdr_sts,
kh.authoring_org_id hdr_authorg,
kh.inv_organization_id hdr_invorg,
kh.price_list_id hdr_pl,
kh.currency_code hdr_curr,
kh.payment_term_id hdr_payment,
kh.conversion_type hdr_conv_type,
kh.conversion_rate hdr_conv_rate,
kh.conversion_rate_date hdr_conv_date,
kh.conversion_euro_rate hdr_euro_rate,
ks.hold_billing hdr_hold_bill,
ks.summary_trx_yn hdr_sum_trx,
ks.payment_type hdr_payment_type,
ks.inv_trx_type hdr_inv_trx,
ks.period_start hdr_period_start,
ks.period_type hdr_period_type,
gov.isa_agreement_id agreement_id,
(SELECT MIN (temp1.new_start_date)
FROM oks_instance_k_dtls_temp temp1
WHERE temp1.contract_id = temp.contract_id) hdr_sdate,
(SELECT MAX (temp1.new_end_date)
FROM oks_instance_k_dtls_temp temp1
WHERE temp1.contract_id = temp.contract_id) hdr_edate,
kh.contract_number contract_number,
kh.contract_number_modifier,
get_modifier(temp.contract_id),
(SELECT okc_p_util.raw_to_number (SYS_GUID ())
FROM okc_k_headers_all_b where id = contract_id ) oks_id,
ks.price_uom
FROM okc_k_headers_all_b kh,
oks_k_headers_b ks,
oks_instance_k_dtls_temp temp,
okc_statuses_b st,
okc_governances gov
WHERE kh.ID = temp.contract_id
AND temp.new_contract_id is not null
AND st.code = kh.sts_code
AND ks.chr_id(+) = kh.ID
AND gov.chr_id(+) = kh.ID
AND gov.dnz_chr_id(+) = kh.ID;
SELECT DISTINCT (temp.new_contract_id),
sc.ctc_id,
sc.sales_credit_type_id1,
sc.PERCENT,
sc.sales_group_id
FROM oks_k_sales_credits_v sc,
oks_instance_k_dtls_temp temp
WHERE sc.chr_id = temp.contract_id
AND sc.cle_id IS NULL
AND temp.new_contract_id IS NOT NULL;
SELECT DISTINCT (temp.new_contract_id),
ac.GROUP_ID,
ac.resource_id,
ac.access_level
FROM okc_k_accesses_v ac,
oks_instance_k_dtls_temp temp
WHERE chr_id = temp.contract_id;
SELECT application_id
FROM okc_classes_b cls,
okc_subclasses_b scs
WHERE cls.code = scs.cls_code
AND scs.code = p_scs_code;
SELECT Salesrep_id
FROM jtf_rs_salesreps Sales
WHERE sales.resource_id = P_resource_id
And sales.org_id = p_org_id;
SELECT 'x'
FROM okc_contact_sources_v
WHERE cro_code = p_code
AND buy_or_sell = 'S'
AND rle_code = 'VENDOR'
AND jtot_object_code = 'OKX_SALEPERS';
Select ste_code
From okc_statuses_b
WHere code = p_sts_code;
Select lse_id
From Okc_k_lines_b kl
Where chr_id = p_hdr_id
And lse_id in (1,19,14);
g_module_current || 'oks_mass_update.creat_contract_header',
'Begin'
);
g_module_current || 'oks_mass_update.create_contract_header',
'Contracts count = (' || hdr_id.COUNT ||')');
l_chrv_tbl_in.DELETE;
l_khrv_tbl_in.DELETE;
g_module_current || 'oks_mass_update.create_contract_header',
'Process Contract id = (' ||i|| '--->'|| hdr_id(i)
||'). Set org context( authoring_org_id ='|| hdr_authoring_org_id(i)
||', org id = '||hdr_inv_org_id (i)|| ')' );
g_module_current || 'oks_mass_update.create_contract_header',
'Application org id = '||l_chrv_tbl_in (i).application_id );
g_module_current || 'oks_mass_update.create_contract_header',
'After generate contract number, status = ('
|| l_return_status|| '). Contract Number = ('
||l_chrv_tbl_in (i).contract_number || ')');
g_module_current || 'oks_mass_update.create_contract_header',
'After contract Modifier ('
|| l_chrv_tbl_in (i).contract_number_modifier || ')');
g_module_current || 'oks_mass_update.create_contract_header',
'after Validate contract number status = ('
|| l_return_status || ')');
l_chrv_tbl_in (i).deleted_yn := 'N';
l_chrv_tbl_in (i).program_update_date := '';
l_chrv_tbl_in (i).last_updated_by := fnd_global.user_id;
l_chrv_tbl_in (i).last_update_date := SYSDATE;
l_chrv_tbl_in (i).last_update_login := fnd_global.login_id;
g_module_current || 'oks_mass_update.create_contract_header',
'Inside pdf assignment');
l_cpsv_tbl_in (i).last_updated_by := fnd_global.user_id;
l_cpsv_tbl_in (i).last_update_date := SYSDATE;
l_cpsv_tbl_in (i).last_update_login := fnd_global.login_id;
g_module_current || 'oks_mass_update.create_contract_header',
'Inside contract group assignment');
l_cgcv_tbl_in (i).last_updated_by := fnd_global.user_id;
l_cgcv_tbl_in (i).last_update_date := SYSDATE;
l_cgcv_tbl_in (i).last_update_login := fnd_global.login_id;
g_module_current || 'oks_mass_update.create_contract_header',
'Party roles assignment, Vendor = '|| hdr_authoring_org_id (i)
|| 'Customer = '|| p_batch_rules.new_party_id );
l_cpl_tbl_in (j).last_updated_by := fnd_global.user_id;
l_cpl_tbl_in (j).last_update_date := SYSDATE;
l_cpl_tbl_in (j).last_update_login := fnd_global.login_id;
l_ctcv_tbl_in (k).last_updated_by := fnd_global.user_id;
l_ctcv_tbl_in (k).last_update_date := SYSDATE;
l_ctcv_tbl_in (k).last_update_login := fnd_global.login_id;
l_cpl_tbl_in (j + 1).last_updated_by := fnd_global.user_id;
l_cpl_tbl_in (j + 1).last_update_date := SYSDATE;
l_cpl_tbl_in (j + 1).last_update_login := fnd_global.login_id;
g_module_current || 'oks_mass_update.create_contract_header',
'Billing account not related, create billing contact.'
|| l_third_party_id);
l_cpl_tbl_in (j + 2).last_updated_by := fnd_global.user_id;
l_cpl_tbl_in (j + 2).last_update_date := SYSDATE;
l_cpl_tbl_in (j + 2).last_update_login := fnd_global.login_id;
l_ctcv_tbl_in (k).last_updated_by := fnd_global.user_id;
l_ctcv_tbl_in (k).last_update_date := SYSDATE;
l_ctcv_tbl_in (k).last_update_login := fnd_global.login_id;
g_module_current || 'oks_mass_update.create_contract_header',
'Billing account are related, create billing contact.');
l_ctcv_tbl_in (k).last_updated_by := fnd_global.user_id;
l_ctcv_tbl_in (k).last_update_date := SYSDATE;
l_ctcv_tbl_in (k).last_update_login := fnd_global.login_id;
fnd_file.put_line(fnd_file.log,' insert_row_upg');
okc_chr_pvt.insert_row_upg (x_return_status => l_return_status,
p_chrv_tbl => l_chrv_tbl_in
);
fnd_file.put_line(fnd_file.log,'(OKS) -> Call to okc_chr_pvt.insert_row_upg , status = ( '
|| l_return_status || ' )');
g_module_current || 'oks_mass_update.create_contract_header',
'okc_chr_pvt.insert_row_upg, status = ('
|| l_return_status || ')');
INSERT INTO OKS_K_HEADERS_B(
id,
chr_id,
acct_rule_id,
tax_code,
billing_profile_id,
inv_trx_type,
inv_print_profile,
ar_interface_yn,
hold_billing,
summary_trx_yn,
object_version_number,
period_start,
period_type,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
renewal_status,
price_uom)
VALUES (
hdr_oks_id (i),
hdr_id (i),
Accting_rule(i),
'',
Billing_profile(i),
hdr_inv_trx (i),
'Y',
ar_interface_yn(i),
hdr_hold_bill (i),
hdr_sum_trx (i),
1,
hdr_period_start(i),
hdr_period_type(i),
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
renewal_status(i),
price_uom(i));
g_module_current || 'oks_mass_update.create_contract_header',
'oks_chr_pvt.insert_row_upg, status = ('
|| l_return_status || ')');
okc_cpl_pvt.insert_row_upg (x_return_status => l_return_status,
p_cplv_tbl => l_cpl_tbl_in
);
fnd_file.put_line(fnd_file.log,'(OKS) -> Call to okc_cpl_pvt.insert_row_upg , status = ( '
|| l_return_status || ' )');
g_module_current || 'oks_mass_update.create_contract_header',
'okc_cpl_pvt.insert_row_upg, status = ('
|| l_return_status || ')');
okc_ctc_pvt.insert_row_upg (x_return_status => x_return_status,
p_ctcv_tbl => l_ctcv_tbl_in
);
fnd_file.put_line(fnd_file.log,'(OKS) -> Call to okc_ctc_pvt.insert_row_upg , status = ( '
|| l_return_status || ' )');
g_module_current || 'oks_mass_update.create_contract_header',
'okc_ctc_pvt.insert_row_upg, status = ('
|| l_return_status || ')');
okc_gve_pvt.insert_row_upg (x_return_status => x_return_status,
p_gvev_tbl => l_gvev_tbl_in
);
fnd_file.put_line(fnd_file.log,'(OKS) -> Call to okc_gve_pvt.insert_row_upg , status = ( '
|| l_return_status || ' )');
g_module_current || 'oks_mass_update.create_contract_header',
'okc_gve_pvt.insert_row_upg, status = ('
|| l_return_status || ')');
okc_cps_pvt.insert_row_upg (x_return_status => l_return_status,
p_cpsv_tbl => l_cpsv_tbl_in
);
fnd_file.put_line(fnd_file.log,'(OKS) -> Call to okc_cps_pvt.insert_row_upg , status = ( '
|| l_return_status || ' )');
g_module_current || 'oks_mass_update.create_contract_header',
'okc_cps_pvt.insert_row_upg, status = ('
|| l_return_status || ')');
okc_cgc_pvt.insert_row_upg (x_return_status => l_return_status,
p_cgcv_tbl => l_cgcv_tbl_in
);
fnd_file.put_line(fnd_file.log,'(OKS) -> Call to okc_cgc_pvt.insert_row_upg , status = ( '
|| l_return_status || ' )');
g_module_current || 'oks_mass_update.create_contract_header',
'okc_cgc_pvt.insert_row_upg, status = ('
|| l_return_status || ')');
INSERT INTO okc_k_accesses
(ID,
chr_id,
GROUP_ID,
resource_id,
access_level,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES (okc_p_util.raw_to_number (SYS_GUID ()),
hdr_id (i),
groupid (i),
resource_id (i),
access_level (i),
1,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id
);
g_module_current || 'oks_mass_update.create_contract_header',
'AFter creating the access for each contract');
INSERT INTO oks_k_sales_credits
(ID,
PERCENT,
sales_group_id,
chr_id,
cle_id,
ctc_id,
sales_credit_type_id1,
sales_credit_type_id2,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date
)
VALUES (okc_p_util.raw_to_number (SYS_GUID ()),
PERCENT (i),
sales_group_id (i),
hdr_id (i),
NULL,
ctc_id (i),
sales_credit_type_id1 (i),
'#',
1,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE
);
g_module_current || 'oks_mass_update.create_contract_header',
'AFter inserting sales credits for header');
SELECT Lines.*,
CASE
WHEN (lines.contract_id <>
LAG (lines.contract_id) OVER (ORDER BY lines.contract_id,lines.line_no)
or LAG (lines.contract_id) OVER (ORDER BY lines.contract_id,lines.line_no) Is null
)
THEN get_Topline_number ('NEW')
ELSE get_Topline_number ('OLD')
END line_number
from (Select DISTINCT (temp.new_serviceline_id) srvline_id,
itm.object1_id1 srv_itm,
itm.object1_id2 srv_invorg,
itm.jtot_object1_code srv_jtot_code,
kl.price_list_id srv_pl,
kl.currency_code srv_curr,
temp.new_contract_id contract_id,
itm.number_of_items no_items,
itm.uom_code uom_code,
ks.tax_code tax_code,
kl.lse_id lse_id,
kl.line_renewal_type_code renewal_type,
kl.price_unit unit_price,
(SELECT MIN (new_start_date)
FROM oks_instance_k_dtls_temp temp1
WHERE temp1.topline_id = temp.topline_id) new_sdt,
(SELECT MAX (new_end_date)
FROM oks_instance_k_dtls_temp temp1
WHERE temp1.topline_id = temp.topline_id) new_edt,
Ks.invoice_text,
ks.coverage_id,
ks.standard_cov_yn,
st.ste_code line_sts,
(SELECT okc_p_util.raw_to_number (SYS_GUID ())
FROM okc_k_lines_b WHERE id = topline_id) oks_id,
topline_id,
Ks.price_uom,
kh.authoring_org_id,
Kl.Line_Number Line_no
FROM oks_instance_k_dtls_temp temp,
okc_k_lines_b kl,
okc_k_items itm,
oks_k_lines_v ks,
okc_statuses_b st,
okc_k_headers_all_b kh
WHERE temp.topline_id = kl.ID
AND temp.new_serviceline_id IS NOT NULL
AND itm.cle_id = kl.ID
AND itm.jtot_object1_code IN ('OKX_WARRANTY', 'OKX_SERVICE')
AND ks.cle_id(+) = kl.ID
AND st.code = kl.sts_code
And Kh.id = kl.dnz_chr_id) lines
;
SELECT DISTINCT (temp.new_serviceline_id),
temp.new_contract_id,
sc.ctc_id,
sc.sales_credit_type_id1,
sc.PERCENT,
sc.sales_group_id
FROM oks_k_sales_credits_v sc,
oks_instance_k_dtls_temp temp
WHERE sc.cle_id = temp.topline_id
AND temp.new_serviceline_id IS NOT NULL;
g_module_current || 'oks_mass_update.create_contract_Line',
'Begin');
g_module_current || 'oks_mass_update.create_contract_Line',
'Impacted lines count = ( ' || srvline_id.COUNT ||')');
INSERT INTO okc_k_lines_b
(ID,
line_number,
chr_id,
cle_id,
dnz_chr_id,
display_sequence,
sts_code,
lse_id,
exception_yn,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
price_negotiated,
price_level_ind,
price_unit,
currency_code,
last_update_login,
start_date,
end_date,
price_list_id,
cust_acct_id,
bill_to_site_use_id,
inv_rule_id,
line_renewal_type_code,
ship_to_site_use_id,
annualized_factor
)
VALUES (srvline_id (i),
line_number (i),
contract_id (i),
NULL,
contract_id (i),
1,
get_line_status(lse_id(i),new_sdt(i),new_edt(i),line_sts(i),p_batch_rules.contract_status),
--DECODE(lse_id(i),14,get_status(new_sdt(i), new_edt(i)),
-- DECODE(line_sts(i),'ENTERED',get_status_code('ENTERED'),p_batch_rules.contract_status)),
lse_id (i),
'N',
1,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
0,
'N',
0,
srv_curr (i),
fnd_global.user_id,
new_sdt (i),
new_edt (i),
srv_pl (i),
validate_account_id(nvl(p_batch_rules.bill_account_id,p_batch_rules.new_customer_id),p_batch_rules.new_party_id,org_id (i)),
get_address(p_batch_rules.bill_address_id,
nvl(p_batch_rules.bill_account_id,p_batch_rules.new_customer_id),
p_batch_rules.new_party_id,
'BILL_TO',
org_id (i))
,
p_batch_rules.invoicing_rule,
renewal_type (i),
get_address(p_batch_rules.Ship_address_id,
nvl(p_batch_rules.Ship_account_id,p_batch_rules.new_customer_id),
p_batch_rules.new_party_id,
'SHIP_TO',
org_id (i)),
Oks_setup_util_pub.Get_Annualized_Factor(new_sdt(i),
new_edt(i),
lse_id(i))
);
g_module_current || 'oks_mass_update.create_contract_Line',
'After insert into okc_k_lines_b table ');
INSERT INTO okc_k_lines_tl
(ID,
LANGUAGE,
source_lang,
sfwt_flag,
NAME,
item_description,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES (srvline_id (i),
okc_util.g_language_code (lang_i),
okc_util.get_userenv_lang,
'N',
null,
null,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id
);
g_module_current || 'oks_mass_update.create_contract_Line',
'After insert into okc_k_lines_tl table ');
INSERT INTO okc_k_items
(ID,
cle_id,
--chr_id,
cle_id_for,
dnz_chr_id,
object1_id1,
object1_id2,
jtot_object1_code,
uom_code,
exception_yn,
number_of_items,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
upg_orig_system_ref,
upg_orig_system_ref_id,
priced_item_yn,
request_id,
program_application_id,
program_id,
program_update_date
)
VALUES (okc_p_util.raw_to_number (SYS_GUID ()),
srvline_id (i),
--contract_id (i),
NULL,
contract_id (i),
srv_itm (i),
srv_invorg (i),
srv_jtot_code (i),
uom_code (i),
'N',
number_of_items (i),
1,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
);
g_module_current || 'oks_mass_update.create_contract_Line',
'After insert into okc_k_items table ');
INSERT INTO oks_k_lines_b
(ID,
cle_id,
dnz_chr_id,
acct_rule_id,
tax_code,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
coverage_id,
standard_cov_yn,
price_uom
)
VALUES (oks_id (i),
srvline_id (i),
contract_id (i),
p_batch_rules.accounting_rule,
tax_code (i),
1,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id,
coverage_id (i),
stand_cov_yn (i),
price_uom(i)
);
g_module_current || 'oks_mass_update.create_contract_Line',
'After insert into oks_lines table ');
INSERT INTO oks_k_lines_tl
(ID,
LANGUAGE,
source_lang,
sfwt_flag,
invoice_text,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES (oks_id (i),
okc_util.g_language_code (lang_i),
okc_util.get_userenv_lang,
'N',
(substr(srv_inv_text(i),1,instr(srv_inv_text(i),':',1,1))|| new_sdt (i)||' - '|| new_edt (i)),
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id
);
g_module_current || 'oks_mass_update.create_contract_Line',
'After insert into okc_lines_tl table ');
INSERT INTO oks_k_sales_credits
(ID,
PERCENT,
sales_group_id,
chr_id,
cle_id,
ctc_id,
sales_credit_type_id1,
sales_credit_type_id2,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date
)
VALUES (okc_p_util.raw_to_number (SYS_GUID ()),
PERCENT (i),
sales_group_id (i),
contract_id (i),
srvline_id(i),
ctc_id (i),
sales_credit_type_id1 (i),
'#',
1,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE
);
g_module_current || 'oks_mass_update.create_contract_header',
'AFter inserting sales credits for header');
SELECT temp.new_subline_id subline_id,
temp.new_start_date subline_sdate,
temp.new_end_date subline_edate,
temp.new_serviceline_id srvline_id,
temp.new_contract_id contract_id,
temp.instance_id instance_id,
itm.number_of_items number_of_items,
itm.uom_code uom_code,
kl.lse_id lse_id,
kl.line_renewal_type_code renewal_type,
kl.currency_code urr_code,
kl.price_unit unit_price,
DECODE(st.ste_code, 'CANCELLED',Negotiated_amount(kl.start_date,kl.end_date,ks.price_uom,kh.period_type,kh.period_start,temp.new_start_date,kl.price_negotiated, kl.currency_code ) ,
(temp.amount - kl.price_negotiated)) price_negotiated,
ks.tax_code tax_code,
Ks.invoice_text,
(CASE
WHEN (temp.new_serviceline_id <>
(LAG (temp.new_serviceline_id) OVER (ORDER BY temp.new_serviceline_id)
)
)
THEN get_line_number ('NEW')
ELSE get_line_number ('OLD')
END
) line_number,
kl1.start_date srv_sdate,
kl1.end_date srv_edate,
st.ste_code subline_sts,
(SELECT okc_p_util.raw_to_number (SYS_GUID ())
FROM okc_k_lines_b WHERE id = subline_id) oks_id,
ks.price_uom,
ks.toplvl_price_qty,
ks.toplvl_uom_code
FROM oks_instance_k_dtls_temp temp,
okc_k_lines_b kl,
okc_k_lines_b kl1,
okc_k_items itm,
oks_k_lines_v ks,
okc_statuses_b st,
oks_k_headers_b kh
WHERE temp.subline_id = kl.ID
AND temp.new_subline_id IS NOT NULL
AND itm.cle_id = kl.ID
AND itm.jtot_object1_code IN ('OKX_CUSTPROD')
AND ks.cle_id(+) = kl.ID
AND kl1.ID = temp.new_serviceline_id
AND st.code = kl.sts_code
And kl.dnz_chr_id = Kh.chr_Id;
SELECT billing_type,
INTERVAL,
interface_offset,
invoice_offset,
billing_level
FROM oks_billing_profiles_b
WHERE ID = p_bf_id;
g_module_current || 'oks_mass_update.create_contract_subLine',
'Begin');
INSERT INTO okc_k_lines_b
(ID,
line_number,
--chr_id,
cle_id,
dnz_chr_id,
display_sequence,
sts_code,
lse_id,
exception_yn,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
price_negotiated,
price_level_ind,
price_unit,
currency_code,
last_update_login,
start_date,
end_date,
line_renewal_type_code,
annualized_factor
)
VALUES (subline_id (i),
line_number (i),
--contract_id (i),
srvline_id (i),
contract_id (i),
2,
get_line_status(lse_id(i),subline_sdate (i),subline_edate (i),subline_sts(i),p_batch_rules.contract_status),
-- DECODE(lse_id(i),18,get_status(subline_sdate (i),subline_edate (i)),
-- DECODE(subline_sts(i),'ENTERED',get_status_code('ENTERED'),p_batch_rules.contract_status)),
lse_id (i),
'N',
1,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
NVL(price_negotiated (i),0), -- price_negotiated
'Y',
NVL(unit_price (i),0), -- unit price
subline_curr (i),
fnd_global.user_id,
subline_sdate (i),
subline_edate (i),
renewal_type (i),
Oks_setup_util_pub.Get_Annualized_Factor(subline_sdate (i),
subline_edate (i),
lse_id(i))
);
g_module_current || 'oks_mass_update.create_contract_Line',
'after insert into okc_k_lines table');
INSERT INTO okc_k_lines_tl
(ID,
LANGUAGE,
source_lang,
sfwt_flag,
NAME,
item_description,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES (subline_id (i),
okc_util.g_language_code (lang_i),
okc_util.get_userenv_lang,
'N',
null,
null,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id
);
g_module_current || 'oks_mass_update.create_contract_Line',
'after insert into okc_k_lines_tl table');
INSERT INTO okc_k_items
(ID,
cle_id,
--chr_id,
cle_id_for,
dnz_chr_id,
object1_id1,
object1_id2,
jtot_object1_code,
uom_code,
exception_yn,
number_of_items,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
upg_orig_system_ref,
upg_orig_system_ref_id,
priced_item_yn,
request_id,
program_application_id,
program_id,
program_update_date
)
VALUES (okc_p_util.raw_to_number (SYS_GUID ()),
subline_id (i),
--contract_id (i),
NULL,
contract_id (i),
instance_id (i),
'#',
'OKX_CUSTPROD',
uom_code (i),
'N',
number_of_items (i),
1,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
);
g_module_current || 'oks_mass_update.create_contract_Line',
'after insert into okc_k_items table');
INSERT INTO oks_k_lines_b
(ID,
cle_id,
dnz_chr_id,
tax_code,
price_uom,
toplvl_price_qty,
toplvl_uom_code,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES (oks_id (i),
subline_id (i),
contract_id (i),
tax_code (i),
price_uom(i),
toplvl_price(i),
toplvl_uom(i),
1,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id
);
g_module_current || 'oks_mass_update.create_contract_Line',
'after insert into oks_k_lines table');
INSERT INTO oks_k_lines_tl
(ID,
LANGUAGE,
source_lang,
sfwt_flag,
status_text,
invoice_text,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES (oks_id (i),
okc_util.g_language_code (lang_i),
okc_util.get_userenv_lang,
'N',
'Subline created from transfers',
(substr(sl_inv_text(i),1,instr(sl_inv_text(i),':',1,3))||subline_sdate(i)||' - '|| subline_edate(i)),
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id
);
g_module_current || 'oks_mass_update.create_contract_Line',
'after insert into oks_k_lines_tl table');
l_chrv_tbl_in.DELETE;
oks_change_status_pvt.Update_header_status(
x_return_status => l_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
p_init_msg_list => 'F',
p_id => contract_id ,
p_new_sts_code => get_status_code('CANCELLED'),--code fix for bug 6350309
p_canc_reason_code => Cancel_reason, --batch_rules_rec.termination_reason_code,
p_old_sts_code => P_Contract_Status,
p_comments => null,
p_term_cancel_source => Term_cancel_source,
p_date_cancelled => l_cancel_date,
p_validate_status => 'N');
fnd_file.put_line(fnd_file.log,'(OKS) -> Update contract Header status = ( '
|| l_return_status || ' )');
okc_contract_pub.update_contract_header
(p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
p_restricted_update => okc_api.g_true,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_chrv_tbl => l_chrv_tbl_in,
x_chrv_tbl => l_chrv_tbl_out
);
fnd_file.put_line(fnd_file.log,'(OKS) -> Update contract Header status = ( '
|| l_return_status || ' )');
l_clev_tbl_in.DELETE;
oks_change_status_pvt.Update_line_status (
x_return_status => l_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
p_init_msg_list => 'F',
p_id => contract_id ,
p_cle_id => Service_line_id ,
p_new_sts_code => get_status_code('CANCELLED'),--code fix for bug 6350309
p_canc_reason_code => cancel_reason,--batch_rules_rec.termination_reason_code,
p_old_sts_code => line_status ,
p_old_ste_code => 'ENTERED',
p_new_ste_code => 'CANCELLED',
p_term_cancel_source => Term_cancel_source,
p_date_cancelled => l_cancel_date,
p_comments => NULL,
p_validate_status => 'N') ;
okc_contract_pub.update_contract_line
(p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
p_restricted_update => okc_api.g_true,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_clev_tbl => l_clev_tbl_in,
x_clev_tbl => l_clev_tbl_out
);
fnd_file.put_line(fnd_file.log,'(OKS) -> Update contract Line status = ( '
|| l_return_status ||' )' );
Select Distinct temp.new_serviceline_Id
,temp.topline_id
,line.start_date
,line.end_date
,ks.coverage_id
,ks.standard_cov_yn
,ki.object1_id1 srv_itm
,St.ste_code
,kl.start_date
From Oks_instance_k_dtls_temp temp
, okc_k_lines_b line
, Okc_k_lines_b Kl
, oks_k_lines_b Ks
, Okc_k_items Ki
, Okc_statuses_b St
Where line.id = temp.new_serviceline_id
And Kl.Id = temp.topline_id
And Ks.cle_id = line.Id
And Ki.cle_id = Line.Id
And temp.new_contract_id = p_contract_id
And St.code = kl.sts_code;
Select distinct temp.new_contract_id
, temp.contract_id
, kh.authoring_org_id
, kh.inv_organization_id
, St.ste_code
, Ost.ste_code
,Decode(kl.lse_id,18,'WARRANTY','OTHERS')
,Kh.qcl_id
,oKh.start_date
,ks.period_start
From OKs_instance_k_dtls_temp temp
, Okc_k_headers_all_b Kh
, Okc_k_headers_all_b OKH
, Okc_statuses_b St
, Okc_statuses_b OSt
, Okc_k_lines_b Kl
, Oks_k_headers_b Ks
Where Kh.Id = temp.new_contract_id
And OKH.Id = temp.contract_id
And Ks.chr_id = Kh.id
And St.code = Kh.sts_code
And OSt.code = OKH.sts_code
And Kl.dnz_chr_id = temp.contract_id
And Kl.Id = temp.subline_id;
Select temp.new_subline_Id
From Oks_instance_k_dtls_temp temp
Where temp.new_contract_id = p_contract_id;
SELECT DESCRIPTION --NAME
FROM csi_mass_edit_entries_tl
WHERE source_lang = USERENV ('LANG')
AND entry_id = p_batch_id
AND ROWNUM < 2;
SELECT counter_group_id
FROM cs_ctr_associations
WHERE source_object_id = p_id;
select uom_code
from okc_time_code_units_b
where tce_code='DAY'
and quantity=1;
g_module_current || 'oks_mass_update.create_contract_Line',
'create coverage status = ('|| x_return_status || ')');
Update Oks_k_lines_b set coverage_id = l_coverage_id
Where cle_id = srvline_id (Line_ctr);
g_module_current || 'oks_mass_update.create_contract_Line',
'create coverage extension status = ('|| x_return_status || ')');
g_module_current || 'oks_mass_update.create_contract_Line',
'Instantiate counters status = ('|| l_return_status || ')');
g_module_current || 'oks_mass_update.create_contract_Line',
'create events status = ('|| l_return_status || ')');
g_module_current || 'oks_mass_update.create_billing_schedule',
'get_billing_schedule'|| x_return_status);
g_module_current || 'oks_mass_update.create_billing_schedule',
'get_duration'|| l_return_status);
g_module_current || 'oks_mass_update.create_billing_schedule',
'create_bill_sch_rules'|| x_return_status);
l_sll_tbl_out.delete;
l_sll_tbl.delete;
Update oks_k_lines_b
set tax_amount = l_tax_amount, tax_inclusive_yn = l_tax_inclusive_yn
Where cle_id = subline_id(subline_ctr );
Update oks_k_lines_b
set tax_amount = (select nvl(sum(ks.tax_amount),0) from oks_k_lines_b ks, okc_k_lines_b kl
where kl.cle_id = srvline_id(l) and ks.cle_id = kl.id)
Where cle_id = srvline_id(l);
UPDATE okc_k_headers_all_b
SET sts_code = l_sts_code,
date_approved = NULL,
date_signed = NULL
WHERE ID = qa_contract_id ;
UPDATE oks_k_headers_b
SET renewal_status= 'DRAFT'
WHERE CHR_ID = qa_contract_id ;
fnd_file.put_line(fnd_file.log,'(OKS) -> Header status updated to ( '
|| l_sts_code ||' ) successfully');
UPDATE okc_k_lines_b
SET sts_code = l_sts_code
WHERE dnz_chr_id = qa_contract_id ;
fnd_file.put_line(fnd_file.log,'(OKS) -> Line status updated to ( '
|| l_sts_code ||' ) successfully');
p_last_update_date => SYSDATE,
p_last_updated_by => fnd_global.user_id,
p_last_update_login => fnd_global.login_id,
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL,
p_context => NULL,
p_jtf_note_contexts_tab => l_jtf_note_contexts_tab
);
Update oks_k_headers_b
Set tax_amount = (select Nvl(sum(ks.tax_amount),0) from oks_k_lines_b ks, okc_k_lines_b kl
where kl.dnz_chr_id = contract_id(k) and ks.cle_id = kl.id and kl.lse_id in (9,25))
Where chr_id = contract_id(k);
oks_change_status_pvt.Update_line_status (
x_return_status => l_return_status,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
p_init_msg_list => 'F',
p_id => p_hdr_id,
p_cle_id => p_cle_id,
p_new_sts_code => get_status_code('CANCELLED'),--code fix for bug 6350309
p_canc_reason_code => l_cancel_reason,
p_old_sts_code => l_sts_code,
p_old_ste_code => l_ste_code,
p_new_ste_code => 'CANCELLED',
p_term_cancel_source => P_termination_source,
p_date_cancelled => p_cancellation_date,
p_comments => NULL,
p_validate_status => 'N') ;
SELECT okc_k_headers_b_s.NEXTVAL
FROM DUAL;
SELECT object_cle_id, OBJECT_CHR_ID
FROM okc_operation_instances op,
okc_operation_lines ol,
okc_class_operations cls,
okc_subclasses_b sl
WHERE ol.oie_id = op.ID
AND cls.cls_code = sl.cls_code
And sl.code = 'SERVICE'
And op.cop_id = cls.id
And cls.opn_code in ('RENEWAL','REN_CON')
AND ol.subject_cle_id = p_line_id;
SELECT ol.subject_cle_id, ol.subject_chr_id
FROM okc_operation_instances op,
okc_operation_lines ol,
okc_class_operations cls,
okc_subclasses_b sl
WHERE ol.oie_id = op.ID
AND cls.cls_code = sl.cls_code
And sl.code = 'SERVICE'
And op.cop_id = cls.id
And cls.opn_code in ('TRANSFER')
AND ol.object_cle_id = p_line_id;
SELECT DISTINCT relationship_type
FROM hz_relationships
WHERE ( ( object_id = l_new_customer
AND subject_id = l_old_customer
)
OR ( object_id = l_old_customer
AND subject_id = l_new_customer
)
)
AND relationship_type = l_relation
AND status = 'A'
AND TRUNC (p_transfer_date) BETWEEN TRUNC (start_date)
AND TRUNC (end_date);
Select Distinct temp.topline_id
,St.ste_code
,line.start_date
From Oks_instance_k_dtls_temp temp
, okc_k_lines_b line
, Okc_statuses_b St
Where line.id = temp.Topline_id
And temp.contract_id = p_contract_id
And St.code = line.sts_code;
Select distinct temp.contract_id
, kh.authoring_org_id
, kh.inv_organization_id
, St.ste_code
, Kh.start_date
From OKs_instance_k_dtls_temp temp
, Okc_k_headers_all_b Kh
, Okc_statuses_b St
Where Kh.Id = temp.contract_id
And St.code = Kh.sts_code;
contract_id.delete;
org_id.delete;
organization_id.delete;
hdr_sts.delete;
hdr_start_date.delete;
topline_id.delete;
topline_sts.delete;
line_start_date.delete;
PROCEDURE update_contracts (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_batch_type IN VARCHAR2,
p_batch_id IN NUMBER,
p_new_acct_id IN NUMBER,
p_old_acct_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
CURSOR get_k_for_transfer_csr (
p_transfer_rule VARCHAR2,
l_relationship VARCHAR2,
p_party_id NUMBER,
P_credit_option Varchar2
)
IS
SELECT ki.dnz_chr_id contract_id,
kh.start_date k_sdate,
kh.end_date k_edate,
kh.sts_code k_sts_code,
Hst.ste_code Hdr_ste,
kh.contract_number,
kh.contract_number_modifier,
kl.cle_id topline_id,
kl2.start_date l_sdate,
kl2.end_date l_edate,
ki.cle_id subline_id,
kl.start_date sl_sdate,
TRUNC(CASE
WHEN trunc(tmp.transfer_date) < trunc(kl.start_date)
THEN trunc(kl.start_date)
ELSE tmp.transfer_date
END
) subline_sdate,
kl.end_date subline_edate,
(decode(row_number() over (partition by kl.dnz_chr_id order by kl.cle_id), 1,
oks_mass_update_pvt.get_seq_no ('H', 'NEW',kh.end_date)
, oks_mass_update_pvt.get_seq_no ('H', 'OLD', kh.end_date))
) newcontractid,
(decode(row_number() over (partition by kl.dnz_chr_id, kl.cle_id order by kl.rowid), 1
, oks_mass_update_pvt.get_seq_no ('L', 'NEW',kl.end_date)
, oks_mass_update_pvt.get_seq_no ('L', 'OLD',kl.end_date))
) newlineid,
oks_mass_update_pvt.get_seq_no ('SL', 'NEW',kl.end_date) newsublineid,
st.ste_code subline_status,
tmp.transfer_date transfer_date,
tmp.old_customer_product_id custprod_id,
kl.price_negotiated,
kl.price_unit,
ki.number_of_items,
DECODE (kl2.lse_id ,14,'WARRANTY','OTHERS'),
tmp.transaction_date,
Party.object1_id1,
Kh.authoring_org_id,
NVL(P_credit_option
,OKS_IB_UTIL_PVT.get_credit_option(party.object1_id1,kh.authoring_org_id,tmp.transaction_date)),
Kh.inv_organization_id
FROM okc_k_items ki,
okc_k_headers_all_b kh,
oks_k_headers_b ks,
okc_k_lines_b kl,
okc_statuses_b st,
okc_statuses_b Hst,
oks_k_lines_b ks1,
oks_k_lines_b ks2,
okc_k_lines_b kl2,
oks_instance_temp tmp,
okc_k_party_roles_b party
WHERE ki.object1_id1 = to_char(tmp.old_customer_product_id)
AND tmp.trf = 'Y'
AND ki.jtot_object1_code = 'OKX_CUSTPROD'
AND ki.dnz_chr_id = kh.ID
AND kh.scs_code IN ('WARRANTY', 'SERVICE', 'SUBSCRIPTION')
And Kh.sts_code = Hst.code
AND ki.cle_id = kl.ID
AND kl.sts_code = st.code
AND st.ste_code NOT IN ('TERMINATED', 'CANCELLED')
AND kl.date_terminated IS NULL
AND kh.template_yn = 'N'
--AND kl.cle_id = kl1.cle_id
--AND ks1.cle_id = kl1.ID
--AND kl1.lse_id IN (2, 20, 15)
AND kl2.ID = kl.cle_id
AND kl2.cle_id IS NULL
AND kl2.id = ks1.cle_id -- Added for coverage
and ks1.coverage_id = ks2.cle_id(+) -- Added for coverage
AND party.dnz_chr_id = kh.ID
AND party.chr_id IS NOT NULL
AND party.cle_id IS NULL
AND party.rle_code IN ('CUSTOMER', 'SUBSCRIBER')
AND party.jtot_object1_code = 'OKX_PARTY'
AND party.object1_id1 <> p_party_id
AND kh.ID = ks.chr_id(+)
AND ( ( TRUNC (tmp.transfer_date) <= TRUNC (kl.end_date)
AND TRUNC (tmp.transfer_date) >= TRUNC (kl.start_date)
)
OR (TRUNC (tmp.transfer_date) <= TRUNC (kl.start_date))
OR ( TRUNC (kl.end_date) < TRUNC (tmp.transfer_date)
AND kl.Date_renewed is Null and kl.lse_id <> 18
)
)
AND ( ('TRANSFER' = p_transfer_rule)
OR ( 'COVERAGE' = p_transfer_rule
AND ( ks2.transfer_option = 'TRANS'
OR ( ks2.transfer_option = 'TRANS_NO_REL'
AND NVL (l_relationship, 'Y') = 'Y'
)
)
)
)
;
SELECT ki.dnz_chr_id contract_id,
ki.cle_id subline_id,
sl.start_date subline_sdate,
sl.end_date subline_edate,
st.ste_code subline_sts,
kh.start_date hdr_sdt,
kh.end_date hdr_edt,
kh.sts_code hdr_sts,
Hst.ste_code hdr_ste,
kh.contract_number,
kh.contract_number_modifier,
Tl.id top_line_id,
Tl.start_date top_line_sdt,
Tl.end_date Top_line_edt,
party.object1_id1 party_id,
tmp.transfer_date,
tmp.old_customer_product_id custprod_id,
sl.price_negotiated,
sl.price_unit,
ki.number_of_items,
tmp.transaction_date,
Kh.authoring_org_id,
NVL(P_credit_option
,OKS_IB_UTIL_PVT.get_credit_option(party.object1_id1,kh.authoring_org_id,tmp.transaction_date)),
Kh.inv_organization_id
FROM okc_k_items ki,
okc_k_headers_all_b kh,
oks_k_headers_b ks,
okc_k_lines_b sl,
okc_k_lines_b Tl,
oks_k_lines_b okl,
oks_k_lines_b okl1,
okc_statuses_b st,
okc_k_party_roles_b party,
oks_instance_temp tmp,
okc_statuses_b Hst
WHERE tmp.trf = 'Y'
AND ki.object1_id1 = to_char(tmp.old_customer_product_id)
AND ki.jtot_object1_code = 'OKX_CUSTPROD'
AND ki.dnz_chr_id = kh.ID
AND ks.chr_id(+) = kh.ID
AND kh.scs_code IN ('WARRANTY', 'SERVICE', 'SUBSCRIPTION')
And kh.sts_code = Hst.code
AND ki.cle_id = sl.ID
AND sl.cle_id = Tl.id -- Added for coverage re-arc
AND Tl.cle_id IS NULL -- Added for coverage re-arc
AND Tl.id = okl.cle_id -- Added for coverage re-arc
AND okl.coverage_id = okl1.cle_id(+) -- Added for coverage re-arc
AND sl.sts_code = st.code
AND st.ste_code NOT IN ('TERMINATED', 'CANCELLED')
AND sl.date_terminated IS NULL
AND kh.template_yn = 'N'
AND party.dnz_chr_id = kh.ID
AND party.chr_id IS NOT NULL
AND party.cle_id IS NULL
AND party.rle_code IN ('CUSTOMER', 'SUBSCRIBER')
AND party.jtot_object1_code = 'OKX_PARTY'
AND party.object1_id1 <> p_party_id
AND ( ( TRUNC (tmp.transfer_date) <= TRUNC (sl.end_date)
AND TRUNC (tmp.transfer_date) >= TRUNC (sl.start_date)
)
OR (TRUNC (tmp.transfer_date) <= TRUNC (sl.start_date))
OR ( TRUNC (sl.end_date) < TRUNC (tmp.transfer_date)
AND sl.date_renewed is null and sl.lse_id <> 18
)
)
AND ( ('TERMINATE' = p_transfer_rule)
OR ( 'COVERAGE' = p_transfer_rule
AND ( okl1.transfer_option = 'TERM'
OR ( okl1.transfer_option = 'TERM_NO_REL'
AND NVL (l_relationship, 'Y') = 'Y'
)
)
)
)
;
SELECT ki.dnz_chr_id AS contract_id,
kl.cle_id AS topline_id,
ki.cle_id AS subline_id,
kh.start_date AS hdr_sdt,
kh.end_date AS hdr_edt,
Kh.sts_code AS hdr_sts,
HSt.ste_code AS hdr_ste,
kh.contract_number,
kh.contract_number_modifier,
tl.start_date AS srv_sdt,
tl.end_date AS srv_edt,
Lst.ste_code AS srv_sts,
kl.start_date AS prod_sdate,
kl.end_date AS prod_edate,
st.ste_code AS prod_sts,
tmp.termination_date AS term_date,
tmp.old_customer_product_id AS instance_id,
tmp.transaction_date AS transaction_date,
ki.number_of_items AS qty,
kl.price_negotiated AS price_negotiated,
party.object1_id1 party_id,
kh.authoring_org_id,
NVL(P_credit_option
,OKS_IB_UTIL_PVT.get_credit_option(party.object1_id1,kh.authoring_org_id,tmp.transaction_date)),
Kh.inv_organization_id
FROM okc_k_items ki,
okc_k_headers_all_b kh,
okc_k_lines_b kl,
okc_statuses_b st,
oks_instance_temp tmp,
okc_k_lines_b tl,
okc_k_party_roles_b party,
Okc_statuses_b Hst,
Okc_statuses_b Lst
WHERE tmp.trm = 'Y'
AND ki.object1_id1 = to_char(tmp.old_customer_product_id)
AND ki.jtot_object1_code = 'OKX_CUSTPROD'
AND ki.dnz_chr_id = kh.ID
AND kh.scs_code IN ('WARRANTY', 'SERVICE', 'SUBSCRIPTION')
And Kh.sts_code = Hst.code
AND ki.cle_id = kl.ID
AND tl.ID = kl.cle_id
And tl.sts_code = Lst.code
AND kl.sts_code = st.code
AND st.ste_code NOT IN ('TERMINATED', 'CANCELLED')
AND kl.date_terminated IS NULL
AND kh.template_yn = 'N'
AND party.dnz_chr_id = kh.ID
AND party.chr_id IS NOT NULL
AND party.cle_id IS NULL
AND party.rle_code IN ('CUSTOMER', 'SUBSCRIBER')
AND party.jtot_object1_code = 'OKX_PARTY'
AND ( ( TRUNC (tmp.Termination_date) <= TRUNC (kl.end_date)
AND TRUNC (tmp.Termination_date) >= TRUNC (kl.start_date)
)
OR (TRUNC (tmp.Termination_date) <= TRUNC (kl.start_date))
OR ( TRUNC (kl.end_date) < TRUNC (tmp.Termination_date)
AND Kl.date_renewed is null and kl.lse_id <> 18
)
)
UNION
SELECT ki.dnz_chr_id AS contract_id,
kl.cle_id AS topline_id,
ki.cle_id AS subline_id,
kh.start_date AS hdr_sdt,
kh.end_date AS hdr_edt,
kh.sts_code AS hdr_sts,
HSt.ste_code AS hdr_ste,
kh.contract_number,
kh.contract_number_modifier,
tl.start_date AS srv_sdt,
tl.end_date AS srv_edt,
Lst.ste_code AS srv_sts,
kl.start_date AS prod_sdate,
kl.end_date AS prod_edate,
st.ste_code AS prod_sts,
tmp.termination_date AS term_date,
tmp.old_customer_product_id AS instance_id,
tmp.transaction_date AS transaction_date,
ki.number_of_items AS qty,
kl.price_negotiated AS price_negotiated,
party.object1_id1 party_id,
kh.authoring_org_id,
NVL(P_credit_option
,OKS_IB_UTIL_PVT.get_credit_option(party.object1_id1,kh.authoring_org_id,tmp.transaction_date)),
Kh.inv_organization_id
FROM okc_k_items ki,
okc_k_headers_all_b kh,
okc_k_lines_b kl,
okc_k_lines_b tl,
okc_statuses_b st,
csi_counter_associations ctrAsc,
oks_instance_temp tmp,
okc_k_party_roles_b party,
okc_statuses_b Hst,
okc_statuses_b Lst
WHERE tmp.trm = 'Y'
AND ki.object1_id1 = to_char(ctrAsc.counter_id)
AND ctrAsc.source_object_id = tmp.old_customer_product_id
And ctrAsc.source_object_code = 'CP'
AND ki.jtot_object1_code = 'OKX_COUNTER'
AND ki.dnz_chr_id = kh.ID
AND kh.scs_code IN ('SERVICE', 'SUBSCRIPTION')
And Kh.sts_code = Hst.code
AND ki.cle_id = kl.ID
AND tl.ID = kl.cle_id
And tl.sts_code = Lst.code
AND kl.sts_code = st.code
AND st.ste_code NOT IN ('TERMINATED', 'CANCELLED')
AND kl.date_terminated IS NULL
AND kh.template_yn = 'N'
AND party.dnz_chr_id = kh.ID
AND party.chr_id IS NOT NULL
AND party.cle_id IS NULL
AND party.rle_code IN ('CUSTOMER', 'SUBSCRIBER')
AND party.jtot_object1_code = 'OKX_PARTY'
AND ( ( TRUNC (tmp.Termination_date) <= TRUNC (kl.end_date)
AND TRUNC (tmp.Termination_date) >= TRUNC (kl.start_date)
)
OR (TRUNC (tmp.Termination_date) <= TRUNC (kl.start_date))
OR ( TRUNC (kl.end_date) < TRUNC (tmp.Termination_date)
AND Kl.date_renewed is null and kl.lse_id <> 18
)
);
select a.*, cs.creation_date
from ( SELECT ki.dnz_chr_id AS contract_id,
kl.cle_id AS topline_id,
ki.cle_id AS subline_id,
kh.start_date AS hdr_sdt,
kh.end_date AS hdr_edt,
kh.sts_code AS hdr_sts,
tl.start_date AS srv_sdt,
tl.end_date AS srv_edt,
tl.sts_code AS srv_sts,
kl.start_date AS prod_sdate,
kl.end_date AS prod_edate,
kl.sts_code AS prod_sts,
tmp.installation_date AS idc_date,
TRUNC (oks_mass_update_pvt.get_end_date (kl.start_date,
kl.end_date,
tmp.installation_date
)
) AS new_edt,
ki.number_of_items,
tmp.transaction_date,
tmp.old_customer_product_id,
(Kh.COntract_number||' '||Kh.Contract_number_Modifier)COntract_number,
kl.line_number
FROM okc_k_items ki,
okc_k_headers_all_b kh,
okc_k_lines_b kl,
okc_statuses_b st,
oks_instance_temp tmp,
okc_k_lines_b tl,
oks_k_lines_v ksl,
oks_k_lines_b ks11
WHERE tmp.idc = 'Y'
AND NVL(tmp.trm, 'N') = 'N'
AND ki.object1_id1 = to_char(tmp.old_customer_product_id)
AND ki.jtot_object1_code = 'OKX_CUSTPROD'
AND ki.dnz_chr_id = kh.ID
AND kh.scs_code IN ('WARRANTY')
AND ki.cle_id = kl.ID
AND tl.ID = kl.cle_id
AND kl.sts_code = st.code
AND st.ste_code NOT IN ('TERMINATED', 'CANCELLED', 'HOLD')
AND kl.date_terminated IS NULL
AND kh.template_yn = 'N'
AND kl.lse_id = 18
And Ksl.cle_id = Tl.id
And ks11.cle_id = Ksl.coverage_id
And Nvl(ks11.sync_date_install,'N') = 'Y'
) a, cs_incidents_all_b cs
where cs.customer_product_id(+) = a.old_customer_product_id
AND cs.contract_service_id(+) = a.topline_ID;
SELECT credit_option,
nvl(termination_reason_code,'EXP')
FROM oks_batch_rules
WHERE batch_id = p_batch_id;
SELECT b.ID biling_profile_id,
NVL (b.invoice_object1_id1, -2) invoicing_rule,
NVL (b.account_object1_id1, 1) accounting_rule,
a.transaction_date transfer_date,
a.credit_option,
nvl(a.termination_reason_code,'TRF') termination_reason_code,
a.retain_contract_number_flag,
a.contract_modifier,
a.contract_status,
a.transfer_notes_flag,
a.transfer_attachments_flag,
a.bill_lines_flag,
a.transfer_option_code transfer_option,
a.bill_account_id,
a.ship_account_id,
a.bill_address_id,
a.ship_address_id,
a.bill_contact_id,
-- a.ship_contact_id,
NVL(a.new_account_id,p_new_acct_id) new_customer_id,
c.party_id new_party_id,
d.party_name party_name,
a.batch_id batch_id
FROM oks_batch_rules a,
oks_billing_profiles_b b,
hz_cust_accounts c,
hz_parties d
WHERE a.batch_id = p_batch_id
AND b.ID(+) = a.billing_profile_id
AND c.cust_account_id = NVL(a.new_account_id, p_new_acct_id)
AND c.party_id = d.party_id;
SELECT distinct temp.topline_id
,line.start_date,
line.end_date
FROM okc_k_lines_b line
, oks_instance_k_dtls_temp temp
WHERE line.ID = temp.topline_id;
SELECT subject_cle_id
FROM okc_operation_instances op,
okc_operation_lines ol
WHERE ol.oie_id = op.ID
AND op.cop_id = 41
AND ol.subject_cle_id = p_line_id;
SELECT qcl_id
FROM okc_k_headers_all_b
WHERE ID = p_id;
SELECT DISTINCT a.relationship_type
FROM hz_relationships a,
hz_cust_accounts b1,
hz_cust_accounts b2,
oks_instance_temp c
WHERE ( ( a.object_id = b1.party_id
AND a.subject_id = b2.party_id
)
OR ( a.object_id = b2.party_id
AND a.subject_id = b1.party_id
)
)
AND a.relationship_type =
fnd_profile.VALUE ('OKS_TRF_PARTY_REL')
AND a.status = 'A'
AND TRUNC (c.transfer_date) BETWEEN TRUNC (a.start_date)
AND TRUNC (a.end_date)
AND b1.cust_account_id = c.new_customer_acct_id
AND b2.cust_account_id = c.old_customer_acct_id
AND ROWNUM < 2;
SELECT 'x'
FROM oks_batch_rules
WHERE batch_id = p_batch_id;
SELECT b.ID,
(SELECT start_date
FROM okc_k_headers_all_b
WHERE ID = new_contract_id),
(SELECT end_date
FROM okc_k_headers_all_b
WHERE ID = new_contract_id),
(SELECT sts_code
FROM okc_k_headers_all_b
WHERE ID = new_contract_id),
(SELECT start_date
FROM okc_k_lines_b
WHERE ID = new_serviceline_id),
(SELECT end_date
FROM okc_k_lines_b
WHERE ID = new_serviceline_id),
(SELECT price_negotiated
FROM okc_k_lines_b
WHERE ID = new_subline_id)
FROM oks_instance_k_dtls_temp a,
oks_instance_history b
WHERE b.batch_id = p_batch_id
AND a.instance_id = b.instance_id
AND b.transaction_type = 'TRF';
Select Kl.Id
,(substr(ksl.invoice_text,1,instr(ksl.invoice_text,':',1,3))||kl.start_date||' - '|| kl.end_date)
From Okc_k_lines_b Kl
,Oks_k_lines_v Ksl
,OKs_instance_k_dtls_temp temp
Where kl.id = temp.subline_id
And Ksl.cle_id = Kl.id;
Select distinct a.Topline_id, a.start_date, a.end_date
From (select line.cle_id topline_id
, min(line.start_date) start_date
, max(line.end_date) end_date
From OKs_instance_k_dtls_temp temp
, okc_K_lines_b line
Where line.cle_id = temp.topline_id
And line.lse_id = 18
group by line.cle_id) a;
Select distinct Kl.Id
,(substr(ksl.invoice_text,1,instr(ksl.invoice_text,':',1,1))||kl.start_date||' - '|| kl.end_date)
From Okc_k_lines_b Kl
,Oks_k_lines_v Ksl
,OKs_instance_k_dtls_temp temp
Where kl.id = temp.topline_id
And Ksl.cle_id = Kl.id;
Select distinct a.Contract_id, a.start_date, a.end_date
From (Select line.dnz_chr_id contract_id
,min(line.start_date) start_date
, max(line.end_date) end_date
From OKs_instance_k_dtls_temp temp
, okc_k_lines_b line
Where line.dnz_chr_id = temp.contract_id
And line.lse_id = 14
group by line.dnz_chr_id) a;
Select Distinct temp.topline_id
,St.ste_code
From Oks_instance_k_dtls_temp temp
, okc_k_lines_b line
, Okc_statuses_b St
Where line.id = temp.Topline_id
And temp.contract_id = p_contract_id
And St.code = line.sts_code;
Select distinct temp.contract_id
, kh.authoring_org_id
, kh.inv_organization_id
, St.ste_code
From OKs_instance_k_dtls_temp temp
, Okc_k_headers_all_b Kh
, Okc_statuses_b St
Where Kh.Id = temp.contract_id
And St.code = Kh.sts_code;
g_module_current || 'oks_mass_update.Update_contracts',
'No Batch rules defined' );
g_module_current || 'oks_mass_update.Update_contracts',
'No Batch rules created' );
g_module_current || 'oks_mass_update.Update_contracts',
'No Batch rules created' );
g_module_current || 'oks_mass_update.Update_contracts',
'Batch Type = ( '|| p_batch_type || ') Batch id = ( '
|| p_batch_id || ')' );
fnd_log.string(FND_LOG.LEVEL_ERROR,G_MODULE_CURRENT||'.oks_mass_update.Update_contracts',
' Contract '||contract_number(i) ||' in QA_HOLD status' );
INSERT INTO oks_instance_k_dtls_temp
(parent_id,
contract_id,
topline_id,
subline_id,
instance_id
)
VALUES (p_batch_id,
contract_id (i),
topline_id (i),
subline_id (i),
custprod_id (i)
);
fnd_file.put_line(fnd_file.log,'(OKS) -> Insert into global temp table Successful');
g_module_current || 'oks_mass_update.Update_contracts',
'Terminate_subline status = ( '|| l_return_status);
INSERT INTO oks_instance_history
(ID,
object_version_number,
instance_id,
transaction_type,
transaction_date,
reference_number,
PARAMETERS,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
batch_id
)
(SELECT okc_p_util.raw_to_number (SYS_GUID ()),
1,
b.instance_id,
'TRF',
a.transaction_date,
b.instance_number,
NULL, -- parameter
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id,
p_batch_id
FROM oks_instance_temp a,
csi_item_instances b
WHERE a.old_customer_product_id = b.instance_id);
INSERT INTO oks_inst_hist_details
(ID,
ins_id,
transaction_date,
transaction_type,
instance_id_new,
instance_qty_old,
instance_qty_new,
instance_amt_old,
instance_amt_new,
old_contract_id,
old_contact_start_date,
old_contract_end_date,
new_contract_id,
new_contact_start_date,
new_contract_end_date,
old_service_line_id,
old_service_start_date,
old_service_end_date,
new_service_line_id,
new_service_start_date,
new_service_end_date,
old_subline_id,
old_subline_start_date,
old_subline_end_date,
new_subline_id,
new_subline_start_date,
new_subline_end_date,
old_customer,
new_customer,
old_k_status,
new_k_status,
subline_date_terminated,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
Date_Cancelled,
transfer_option
)
(SELECT
okc_p_util.raw_to_number (SYS_GUID ()),
ins_hist.id ,
transaction_date(1),
'TRF',
custprod_id (i),
number_of_items (i),
number_of_items (i),
price_negotiated (i),
lines_b.PRICE_NEGOTIATED ,
contract_id (i),
hdr_sdt (i),
hdr_edt (i),
contract_id (i),
hdr_sdt (i),
hdr_edt (i),
topline_id(i),
topline_sdate (i),
topline_edate (i),
topline_id (i),
topline_sdate(i),
topline_edate (i),
subline_id (i),
subline_sdate (i),
subline_edate (i),
subline_id (i),
subline_sdate (i),
subline_edate (i),
p_old_acct_id, -- old_customer,
p_old_acct_id, -- new_customer,
hdr_sts (i),
hdr.sts_code,
lines_b.date_terminated, -- subline_date_terminated,
1,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id,
lines_b.date_cancelled, --Date cancelled,
'TERM'
from oks_instance_history ins_hist,
Okc_k_lines_b lines_b,
okc_k_headers_all_b hdr
Where ins_hist.batch_id = p_batch_id
and instance_id = custprod_id(i) and lines_b.id = subline_id (i)
And hdr.id =contract_id (i) ) ;
fnd_log.string(FND_LOG.LEVEL_ERROR,G_MODULE_CURRENT||'.oks_mass_update.Update_contracts',
' Contract '||contract_number(i) ||' in QA_HOLD status' );
Delete Oks_Instance_k_dtls_temp where parent_id = p_batch_id;
INSERT INTO oks_instance_k_dtls_temp
(parent_id,
contract_id,
topline_id,
subline_id,
new_contract_id,
new_serviceline_id,
new_subline_id,
instance_id,
new_start_date,
new_end_date,
amount
)
VALUES (p_batch_id,
contract_id (i),
topline_id (i),
subline_id (i),
new_contract_id (i),
new_line_id (i),
new_subline_id (i),
custprod_id (i),
subline_sdate (i),
subline_edate (i),
price_negotiated (i)
);
fnd_file.put_line(fnd_file.log,'(OKS) -> Insert into global temp table Successful');
g_module_current || 'oks_mass_update.Update_contracts',
'Terminate_subline status = ( '|| l_return_status);
g_module_current || 'oks_mass_update.Update_contracts',
'create_contract status = ( '|| l_return_status || ')');
UPDATE okc_k_lines_b
SET price_negotiated =
(SELECT NVL (SUM (NVL (price_negotiated, 0)), 0)
FROM okc_k_lines_b
WHERE cle_id = new_line_id (i)
AND lse_id IN (9, 25))
WHERE ID = new_line_id (i);
fnd_file.put_line(fnd_file.log,'(OKS) -> Topline amounts updated successfully');
UPDATE okc_k_headers_all_b
SET estimated_amount =
(SELECT NVL (SUM (NVL (price_negotiated, 0)), 0)
FROM okc_k_lines_b
WHERE dnz_chr_id = new_contract_id (i)
AND lse_id IN (1, 19))
WHERE ID = new_contract_id (i);
fnd_file.put_line(fnd_file.log,'(OKS) -> Header amounts updated successfully');
g_module_current || 'oks_mass_update.Update_contracts',
'create_transaction_source(transfer) status = ( '|| l_return_status || ')');
g_module_current || 'oks_mass_update.Update_contracts',
'create_transaction_source (Renewal) status = ( '|| l_return_status || ')');
Update okc_k_lines_b set date_renewed = trf_date(1)
Where id = l_object_line_id
And date_renewed is null;
Update okc_k_lines_b set date_renewed = l_line_date_renewed
Where id = (select cle_id from okc_k_lines_b where id = l_object_line_id)
And date_renewed Is Null;
Update okc_k_headers_all_b set date_renewed = l_line_date_renewed
Where id = (select dnz_chr_id from okc_k_lines_b where id = l_object_line_id)
And date_renewed Is Null;
INSERT INTO oks_instance_history
(ID,
object_version_number,
instance_id,
transaction_type,
transaction_date,
reference_number,
PARAMETERS,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
batch_id
)
(SELECT okc_p_util.raw_to_number (SYS_GUID ()),
1,
b.instance_id,
'TRF',
a.transaction_date,
b.instance_number,
NULL, -- parameter
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id,
p_batch_id
FROM oks_instance_temp a,
csi_item_instances b
WHERE a.old_customer_product_id = b.instance_id);
INSERT INTO oks_inst_hist_details
(ID,
ins_id,
transaction_date,
transaction_type,
instance_id_new,
instance_qty_old,
instance_qty_new,
instance_amt_old,
instance_amt_new,
old_contract_id,
old_contact_start_date,
old_contract_end_date,
new_contract_id,
new_contact_start_date,
new_contract_end_date,
old_service_line_id,
old_service_start_date,
old_service_end_date,
new_service_line_id,
new_service_start_date,
new_service_end_date,
old_subline_id,
old_subline_start_date,
old_subline_end_date,
new_subline_id,
new_subline_start_date,
new_subline_end_date,
old_customer,
new_customer,
old_k_status,
new_k_status,
subline_date_terminated,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
Date_Cancelled
)
(Select okc_p_util.raw_to_number (SYS_GUID ()),
inshist.id ,
transaction_date(i),
'TRF',
custprod_id (i),
number_of_items (i),
number_of_items (i),
price_negotiated (i),
Line.PRICE_NEGOTIATED ,
contract_id (i),
hdr_sdt (i),
hdr_edt (i),
contract_id (i),
hdr_sdt (i),
hdr_edt (i),
topline_id(i),
topline_sdate (i),
topline_edate (i),
topline_id (i),
topline_sdate(i),
topline_edate (i),
subline_id (i),
subline_old_sdate (i),
subline_edate (i),
subline_id (i),
subline_old_sdate (i),
subline_edate (i),
p_old_acct_id,-- old_customer,
p_old_acct_id, -- new_customer
hdr_sts (i),
hdr.sts_code,
line.date_terminated, -- subline_date_terminated,
1,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id,
line.date_cancelled --Date cancelled
from Oks_instance_history inshist, Okc_k_lines_b line
,okc_k_headers_all_b hdr
Where inshist.instance_id = custprod_id(i)
and batch_id = p_batch_id
And line.id = subline_id(i)
And hdr.id = contract_id(i)
);
INSERT INTO oks_inst_hist_details
(ID,
ins_id,
transaction_date,
transaction_type,
instance_id_new,
instance_qty_old,
instance_qty_new,
instance_amt_old,
instance_amt_new,
old_contract_id,
old_contact_start_date,
old_contract_end_date,
new_contract_id,
new_contact_start_date,
new_contract_end_date,
old_service_line_id,
old_service_start_date,
old_service_end_date,
new_service_line_id,
new_service_start_date,
new_service_end_date,
old_subline_id,
old_subline_start_date,
old_subline_end_date,
new_subline_id,
new_subline_start_date,
new_subline_end_date,
old_customer,
new_customer,
old_k_status,
new_k_status,
subline_date_terminated,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
(Select okc_p_util.raw_to_number (SYS_GUID ()),
inshist.id,
transaction_date(i),
'TRF',
custprod_id (i),
number_of_items (i),
number_of_items (i),
price_negotiated (i),
subline.price_negotiated,--new_price_negotiated (i),
contract_id (i),
hdr_sdt (i),
hdr_edt (i),
new_contract_id (i),
hdr.start_date,--new_k_sdate (i),
hdr.end_date,--new_k_edate (i),
topline_id (i),
topline_sdate (i),
topline_edate (i),
new_line_id (i),
line.start_date,--new_l_sdate (i),
line.end_date,--new_l_edate (i),
subline_id (i),
subline_old_sdate (i),
subline_edate (i),
new_subline_id (i),
subline_sdate (i),
subline_edate (i),
p_old_acct_id, -- old_customer,
p_new_acct_id, -- new_customer,
hdr_sts (i),
hdr.sts_code,--new_k_status (i),
NULL, -- subline_date_terminated,
1,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id
from Oks_instance_history inshist
, okc_k_headers_all_b hdr
, okc_k_lines_b line
, okc_k_lines_b subline
Where inshist.instance_id = custprod_id(i)
and batch_id = p_batch_id
and hdr.id = new_contract_id(i)
and line.id = new_line_id(i)
and subline.id = new_subline_id(i)
);
INSERT INTO oks_instance_k_dtls_temp
(parent_id,
contract_id,
topline_id,
subline_id,
new_start_date,
new_end_date,
instance_id
)
(Select p_batch_id,
contract_id (i),
topline_id (i),
subline_id (i),
subline_sdate (i),
subline_edate (i),
custprod_id (i)
from dual
Where TRUNC (nvl(sr_date (i),idc_date (i))) >= TRUNC (idc_date (i))
OR TRUNC (nvl(sr_date (i),new_edt (i))) <= TRUNC (new_edt (i))
);
fnd_log.string(FND_LOG.LEVEL_ERROR,G_MODULE_CURRENT||'.UPDATE_CONTRACT_IDC.ERROR',
'SR is logged '||',status = ' || l_return_status);
UPDATE okc_k_lines_b
SET start_date = idc_date (i),
end_date = new_edt (i),
sts_code = get_status (idc_date (i), new_edt (i))
WHERE ID = subline_id(i)
And (trunc(nvl(sr_date(i),idc_date(i))) >= trunc(idc_date(i))
And TRUNC(nvl(sr_date(i),new_edt(i))) <= TRUNC(new_edt(i)));
UPDATE oks_k_lines_v
SET invoice_text = inv_text(i)
WHERE id = (select id from oks_k_lines_b
where cle_id = sub_line_id(i));
UPDATE okc_k_lines_b
SET start_date = line_new_Sdate (i),
end_date = line_new_edate (i),
sts_code = get_status (line_new_Sdate(i), line_new_edate(i))
--invoice_text = inv_text(i)
WHERE ID = top_line_id(i);
Top_line_id.delete;
UPDATE oks_k_lines_tl
SET invoice_text = inv_text(i)
WHERE id = (select id from oks_k_lines_b
where cle_id = top_line_id(i));
header_id.delete;
UPDATE okc_k_headers_all_b
SET start_date = Hdr_new_Sdate(i),
end_date = Hdr_new_edate(i),
sts_code = get_status (Hdr_new_Sdate(i), Hdr_new_edate(i))
WHERE ID = header_id(i);
INSERT INTO OKC_K_VERS_NUMBERS_H (
chr_id,
major_version,
minor_version,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
(
SELECT max(chr_id ),
max(major_version) major_version,
max(minor_version) minor_version,
max(object_version_number) object_version_number
, fnd_global.user_id
, SYSDATE
, fnd_global.user_id
, SYSDATE
, fnd_global.login_id
FROM OKC_K_VERS_NUMBERS
WHERE chr_id = header_id(i)
);
UPDATE OKC_K_VERS_NUMBERS
SET minor_version = minor_version+1,
object_version_number = object_version_number+1
WHERE chr_ID = header_id(i);
INSERT INTO oks_instance_history
(ID,
object_version_number,
instance_id,
transaction_type,
transaction_date,
reference_number,
PARAMETERS,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
batch_id
)
(SELECT okc_p_util.raw_to_number (SYS_GUID ()),
1,
b.instance_id,
'IDC',
transaction_date (1),
b.instance_number,
NULL, -- parameter
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id,
p_batch_id
FROM oks_instance_k_dtls_temp a,
csi_item_instances b
WHERE a.instance_id = b.instance_id);
INSERT INTO oks_inst_hist_details
(ID,
ins_id,
transaction_date,
transaction_type,
instance_id_new,
instance_qty_old,
instance_qty_new,
instance_amt_old,
instance_amt_new,
old_contract_id,
old_contact_start_date,
old_contract_end_date,
new_contract_id,
new_contact_start_date,
new_contract_end_date,
old_service_line_id,
old_service_start_date,
old_service_end_date,
new_service_line_id,
new_service_start_date,
new_service_end_date,
old_subline_id,
old_subline_start_date,
old_subline_end_date,
new_subline_id,
new_subline_start_date,
new_subline_end_date,
old_customer,
new_customer,
old_k_status,
new_k_status,
subline_date_terminated,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
date_cancelled
)
(SELECT okc_p_util.raw_to_number (SYS_GUID ()),
a.ID,
transaction_date (i),
'IDC',
custprod_id (i),
number_of_items (i),
number_of_items (i),
null,
null,
contract_id (i),
hdr_sdt (i),
hdr_edt (i),
contract_id (i),
hdr.start_date,
hdr.end_date,
topline_id (i),
topline_sdate (i),
topline_edate (i),
topline_id (i),
line.start_date,
line.end_date,
subline_id (i),
subline_sdate (i),
subline_edate (i),
subline_id (i),
subline.start_date,
subline.end_date,
p_old_acct_id, -- old_customer,
p_old_acct_id, -- new_customer,
hdr_sts (i),
hdr.sts_code,
null, -- subline_date_terminated,
1,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id,
null
FROM oks_instance_history a, Okc_k_headers_all_b hdr
, okc_k_lines_b line
, okc_k_lines_b subline
WHERE a.batch_id = p_batch_id
AND a.transaction_type = 'IDC'
AND a.instance_id = custprod_id(i)
And hdr.id = contract_id(i)
And line.id = topline_id (i)
And subline.id = subline_id (i)
And TRUNC (nvl(sr_date (i),idc_date (i))) >= TRUNC (idc_date (i))
And TRUNC (nvl(sr_date (i),new_edt (i))) <= TRUNC (new_edt (i))
);
INSERT INTO oks_instance_k_dtls_temp
(parent_id,
contract_id,
topline_id,
subline_id,
new_start_date,
new_end_date,
instance_id
)
Values
( p_batch_id,
contract_id (i),
topline_id (i),
subline_id (i),
subline_sdate (i),
subline_edate (i),
custprod_id (i)
);
fnd_log.string(FND_LOG.LEVEL_ERROR,G_MODULE_CURRENT||'.oks_mass_update.Update_contracts',
' Contract '||contract_number(i) ||' in QA_HOLD status' );
INSERT INTO oks_instance_history
(ID,
object_version_number,
instance_id,
transaction_type,
transaction_date,
reference_number,
PARAMETERS,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
batch_id
)
(SELECT okc_p_util.raw_to_number (SYS_GUID ()),
1,
b.instance_id,
'TRM',
transaction_date (1),
b.instance_number,
NULL, -- parameter
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id,
p_batch_id
FROM oks_instance_temp a,
csi_item_instances b
WHERE a.old_customer_product_id = b.instance_id);
INSERT INTO oks_inst_hist_details
(ID,
ins_id,
transaction_date,
transaction_type,
instance_id_new,
instance_qty_old,
instance_qty_new,
instance_amt_old,
instance_amt_new,
old_contract_id,
old_contact_start_date,
old_contract_end_date,
new_contract_id,
new_contact_start_date,
new_contract_end_date,
old_service_line_id,
old_service_start_date,
old_service_end_date,
new_service_line_id,
new_service_start_date,
new_service_end_date,
old_subline_id,
old_subline_start_date,
old_subline_end_date,
new_subline_id,
new_subline_start_date,
new_subline_end_date,
old_customer,
new_customer,
old_k_status,
new_k_status,
subline_date_terminated,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
date_cancelled
)
(SELECT okc_p_util.raw_to_number (SYS_GUID ()),
a.ID,
transaction_date (i),
'TRM',
custprod_id (i),
number_of_items (i),
number_of_items (i),
price_negotiated (i),
price_negotiated (i),
contract_id (i),
hdr_sdt (i),
hdr_edt (i),
contract_id (i),
hdr_sdt (i),
hdr_edt (i),
topline_id (i),
topline_sdate (i),
topline_edate (i),
topline_id (i),
topline_sdate (i),
topline_edate (i),
subline_id (i),
subline_sdate (i),
subline_edate (i),
subline_id (i),
subline_sdate (i),
subline_edate (i),
p_old_acct_id, -- old_customer,
p_old_acct_id, -- new_customer,
hdr_sts (i),
hdr.sts_code,
line.date_terminated, -- subline_date_terminated,
1,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id,
line.date_cancelled
FROM oks_instance_history a,
Okc_k_lines_b line,
Okc_k_headers_all_b hdr
WHERE a.batch_id = p_batch_id
AND a.transaction_type = 'TRM'
AND a.instance_id = custprod_id(i)
AND line.id = subline_id(i)
And hdr.id = contract_id(i));
END;-- End Update Contracts