[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT /*+ leading (kl) use_nl (kl ki) */ kl.ID cle_id
FROM okc_k_lines_b kl, okc_k_items ki, okc_statuses_b ks
WHERE kl.dnz_chr_id = p_chrid
AND kl.lse_id IN (1, 14, 19)
AND kl.ID = ki.cle_id
AND ki.object1_id1 = TO_CHAR (p_invserviceid)
AND kl.upg_orig_system_ref = 'ORDER'
AND ks.code = kl.sts_code -- 04-jun-2002 merging service lines from OM
AND ks.ste_code NOT IN ('TERMINATED', 'CANCELLED') -- Removed EXPIRED
AND kl.date_terminated is NULL; -- Modified for fix of bug 4690982
SELECT invoicing_rule_id, accounting_rule_id, price_list_id,
commitment_id, invoice_to_org_id, ship_to_org_id
FROM oe_order_lines_all
WHERE line_id = p_source_line_id;
SELECT 'x'
FROM okc_k_lines_b kl, oks_k_lines_b sl
WHERE kl.ID = p_k_line_id
AND kl.ID = sl.cle_id
AND NVL (sl.acct_rule_id, -99) = NVL (l_acct_id, -99)
AND NVL (kl.inv_rule_id, -99) = NVL (l_inv_id, -99)
AND NVL (sl.commitment_id, -99) = NVL (l_commit_id, -99)
AND NVL (kl.price_list_id, -99) = NVL (l_prl_id, -99)
AND NVL (kl.bill_to_site_use_id, -99) = NVL (l_invorg_id, -99)
AND NVL (kl.ship_to_site_use_id, -99) = NVL (l_shiporg_id, -99);
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';
SELECT ordhdr.order_number
,ordhdr.invoice_to_org_id
,pmt.trxn_extension_id
FROM
oe_order_headers_all ordhdr
,oe_payments pmt
WHERE ordhdr.header_id = c_order_header_id
AND ordhdr.header_id = pmt.header_id
AND pmt.line_id IS NULL
AND pmt.payment_type_code = G_PAYMENT_CREDIT_CARD;
SELECT ordline.invoice_to_org_id
,pmt.trxn_extension_id
FROM
oe_order_lines_all ordline
,oe_payments pmt
WHERE ordline.line_id = c_order_line_id
AND ordline.header_id = pmt.header_id
AND ordline.line_id = pmt.line_id
AND pmt.payment_type_code = G_PAYMENT_CREDIT_CARD;
SELECT instr_assignment_id
FROM iby_trxn_extensions_v
WHERE trxn_extension_id = c_trxn_extension_id;
SELECT ca.cust_account_id
FROM hz_cust_acct_sites_all ca, hz_cust_site_uses_all cs
WHERE ca.cust_acct_site_id = cs.cust_acct_site_id
AND cs.site_use_id = p_bill_to_site_use_id;
SELECT ca.party_id party_id
FROM hz_cust_accounts_all ca
WHERE ca.cust_account_id = c_cust_acct_id;
SELECT ID
FROM okc_k_party_roles_b
WHERE dnz_chr_id = p_chrid
AND cle_id IS NULL
AND chr_id = p_chrid
AND rle_code = p_rle_code;
SELECT ID
FROM okc_k_party_roles_b
WHERE dnz_chr_id = p_chrid
AND chr_id IS NULL
AND cle_id = p_cleid
AND rle_code = p_rle_code;
SELECT jtot_object1_code,
object1_id1,
object1_id2,
number_of_items
FROM okc_k_items
WHERE cle_id = p_cle_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;
SELECT chr_id
FROM okc_k_rel_objs
WHERE object1_id1 = TO_CHAR (p_object_id)
AND jtot_object1_code = p_jtf_id;
SELECT chr_id
FROM okc_k_rel_objs
WHERE object1_id1 = TO_CHAR (p_object_id)
AND jtot_object1_code = p_jtf_id
AND rty_code = 'CONTRACTWARRANTYORDER';
SELECT ID chr_id
FROM okc_k_headers_v
WHERE attribute1 = p_object_id AND end_date = p_enddate;
SELECT priced_yn
FROM okc_line_styles_b
WHERE ID = p_lse_id;
SELECT ID
FROM oks_stream_levels_v
WHERE cle_id = p_cle_id;
SELECT 'x'
FROM oks_stream_levels_v sll, oks_level_elements lvl
WHERE lvl.rul_id = sll.ID AND sll.cle_id = p_cle_id;
and Update transactions.
**************************************************************/
PROCEDURE create_operation_instance (
p_target_chr_id NUMBER,
p_transaction VARCHAR2,
x_oper_instance_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
CURSOR cop_csr (p_opn_code VARCHAR2)
IS
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;
for Split, replace, transfer and update transactions
***********************************************/
PROCEDURE create_operation_lines (
p_source_line_id NUMBER,
p_target_line_id NUMBER,
p_source_chr_id NUMBER,
p_target_chr_id NUMBER,
p_opr_instance_id NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_version CONSTANT NUMBER := 1.0;
SELECT a.payment_term_id, a.price_list_id, c.ste_code, --a.STS_CODE,
a.authoring_org_id, a.currency_code, a.template_yn,
a.conversion_type, a.conversion_rate, a.conversion_rate_date,
a.conversion_euro_rate, b.inv_trx_type, b.ar_interface_yn,
b.summary_trx_yn, b.hold_billing, a.inv_organization_id,
a.scs_code, c.ste_code source_ste_code, b.period_start,
b.period_type, b.price_uom, a.billed_at_source
FROM okc_k_headers_all_b a, oks_k_headers_b b, okc_statuses_b c
WHERE a.ID = l_chr_id AND a.ID = b.chr_id AND p_sts_code = c.code;
SELECT a.ID
FROM okc_k_headers_all_b a, oks_k_headers_b b, okc_statuses_b c
WHERE a.ID = l_target_chr_id
AND a.ID = b.chr_id
AND a.sts_code= c.code --Bug fix 5614310
AND c.ste_code IN ('ACTIVE', 'ENTERED', 'SIGNED')
AND ( DECODE (DECODE (l_source_ste_code,
'ACTIVE', 1,
'SIGNED', 1,
'HOLD', 1,
0
),
DECODE (l_profile, 'ACTIVE', 1, 2), 1,
3
) =
DECODE (c.ste_code,
'ACTIVE', 1,
'SIGNED', 1,
4
)
OR DECODE (DECODE (l_source_ste_code,
'ACTIVE', 1,
'SIGNED', 1,
'HOLD', 1,
0
),
DECODE (l_profile, 'ENTERED', 1, 2), 1,
3
) = DECODE (c.ste_code, 'ENTERED', 1, 4)
OR DECODE (l_source_ste_code, 'ENTERED', 1, 2) =
DECODE (c.ste_code,
'ENTERED', 1,
3
)
)
AND NVL (a.payment_term_id, -99) = NVL (l_pay_term, -99)
AND NVL (a.price_list_id, -99) = NVL (l_price_list, -99)
AND a.authoring_org_id = l_org_id
AND a.currency_code = l_curr_code
AND a.template_yn = l_temp_yn
AND NVL (a.conversion_type, -99) = NVL (l_conv_type, -99)
AND NVL (a.conversion_rate, -99) = NVL (l_conv_rate, -99)
AND DECODE (a.conversion_rate_date,
NULL, -99,
SYSDATE - TRUNC (a.conversion_rate_date)
) =
DECODE (l_conv_rate_date,
NULL, -99,
SYSDATE - TRUNC (l_conv_rate_date)
)
AND NVL (a.conversion_euro_rate, -99) = NVL (l_conv_euro, -99)
AND NVL (b.inv_trx_type, -99) = NVL (l_trx_type, -99)
AND NVL (b.ar_interface_yn, -99) = NVL (l_ar_int, -99)
AND NVL (b.summary_trx_yn, -99) = NVL (l_sum_trx, -99)
AND NVL (b.hold_billing, -99) = NVL (l_hold_bill, -99)
AND NVL (a.inv_organization_id, -99) = NVL (l_inv_org_id, -99)
AND NVL (a.scs_code, -99) = NVL (l_scs_code, -99)
AND NVL (b.period_start, -99) = NVL (l_period_start, -99)
AND NVL (b.period_type, -99) = NVL (l_period_type, -99)
AND NVL (b.price_uom, -99) = NVL (l_price_uom, -99)
AND NVL (a.billed_at_source, '-99') = NVL(l_billed_at_source, '-99');
SELECT b.acct_rule_id, a.price_list_id, c.ste_code, a.lse_id,
a.bill_to_site_use_id, a.line_renewal_type_code,
b.tax_code --Fix for bug 4121175
,
b.price_uom
FROM okc_k_lines_b a, oks_k_lines_b b, okc_statuses_b c
WHERE a.ID = l_line_id AND a.ID = b.cle_id AND a.sts_code = c.code;
SELECT object1_id1
FROM okc_k_items
WHERE cle_id = l_line_id AND jtot_object1_code LIKE 'OKX_SYSITEM';
SELECT a.date_completed
FROM oks_level_elements a, oks_stream_levels_b b
WHERE b.cle_id = l_line_id AND a.rul_id = b.ID;
SELECT a.ID
FROM okc_k_lines_b a, oks_k_lines_b b, okc_statuses_b c
WHERE a.ID = l_line_id
AND a.ID = b.cle_id
AND a.sts_code = c.code
AND c.ste_code IN ('ACTIVE', 'ENTERED', 'SIGNED')
AND NVL (b.acct_rule_id, -99) = NVL (l_acct_rule_id, -99)
AND NVL (b.tax_code, -99) = NVL (l_tax_code, -99)
--Fix for bug 4121175
AND NVL (b.price_uom, -99) = NVL (l_price_uom, -99)
AND NVL (a.price_list_id, -99) = NVL (l_price_list_id, -99)
/*
AND ( c.ste_code = l_ste_code
OR decode (l_ste_code, 'SIGNED', 1, 'ACTIVE', 1, 4) =
decode(c.ste_code, 'SIGNED', 1, 'ACTIVE', 1, 3)
)
*/
AND a.lse_id = l_lse_id
AND ( DECODE (l_flag, 'N', 0, 1) = 0
OR ( DECODE (l_flag, 'Y', 1, 0) = 1
AND NVL (a.bill_to_site_use_id, -99) =
NVL (l_bill_to_site_use_id,
-99)
AND NVL (a.line_renewal_type_code, -99) =
NVL (l_line_renewal_type_code,
-99)
)
);
SELECT PERCENT, sc.sales_credit_type_id1, sct.quota_flag,
sc.ctc_id sales_person_id, sc.sales_group_id
FROM oks_k_sales_credits sc, oe_sales_credit_types sct
WHERE sc.sales_credit_type_id1 = sct.sales_credit_type_id
AND sc.cle_id = l_line_id;
SELECT PERCENT, sc.sales_credit_type_id1, sct.quota_flag,
sc.ctc_id sales_person_id, sc.sales_group_id
FROM oks_k_sales_credits sc, oe_sales_credit_types sct
WHERE sc.sales_credit_type_id1 = sct.sales_credit_type_id
AND sc.cle_id = l_line_id
AND PERCENT = l_percent
AND sc.sales_credit_type_id1 = l_sales_credit_type_id1
AND sct.quota_flag = l_quota_flag
AND sc.ctc_id = l_sales_person_id
AND NVL (sc.sales_group_id, -99) = NVL (l_sales_group_id, -99);
SELECT COUNT (*)
FROM oks_k_sales_credits
WHERE cle_id = l_line_id;
SELECT start_date, end_date
FROM okc_k_lines_b
WHERE ID = p_cle_id;
PROCEDURE update_line_dates (
p_cle_id IN NUMBER,
p_chr_id IN NUMBER,
p_new_sdt IN DATE,
p_new_edt IN DATE,
p_sts_flag IN VARCHAR2,
p_warranty_flag IN VARCHAR2,
x_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
CURSOR l_rulegroup_csr
IS
SELECT inv_rule_id
FROM okc_k_lines_b
WHERE cle_id = p_cle_id AND dnz_chr_id = p_chr_id;
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
);
g_module_current || '.Update_Line_Dates.external_call.after',
'okc_contract_pub.update_contract_line(Return status = '
|| l_return_status
|| ')'
);
|| '.Update_Line_Dates.UNEXPECTED',
'sqlcode = ' || SQLCODE || ', sqlerrm = '
|| SQLERRM
);
SELECT start_date, end_date
FROM okc_k_headers_v
WHERE ID = p_chr_id;
PROCEDURE update_hdr_dates (
p_chr_id IN NUMBER,
p_new_sdt IN DATE,
p_new_edt IN DATE,
p_sts_flag IN VARCHAR2,
x_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
--General
l_api_version CONSTANT NUMBER := 1.0;
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
);
g_module_current || '.Update_Hdr_Dates.external_call.after',
'okc_contract_pub.update_contract_header(Return status = '
|| l_return_status
|| ')'
);
SELECT contract_number
FROM okc_k_headers_v
WHERE ID = p_hdrid;
SELECT oks_wf_item_key_number_s1.NEXTVAL
INTO l_itemkey_seq
FROM DUAL;
PROCEDURE update_cov_level (
p_covered_line_id IN NUMBER,
p_new_end_date IN DATE,
p_k_item_id IN NUMBER,
p_new_negotiated_amt IN NUMBER,
p_new_cp_qty IN NUMBER,
p_list_price IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
CURSOR l_parent_line_csr
IS
SELECT cle_id
FROM okc_k_lines_b
WHERE ID = p_covered_line_id;
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
);
|| '.update_contract_line.external_call.after',
'okc_contract_pub.update_contract_line(Return status = '
|| l_return_status
|| ')'
);
'Contract Line Update(UPDATE SUB LINE)'
);
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
);
g_module_current || '.Update_Cov_level.external_call.after',
'okc_contract_pub.update_contract_line(Return status = '
|| l_return_status
|| ')'
);
okc_contract_item_pub.update_contract_item
(p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cimv_tbl => l_cimv_tbl_in,
x_cimv_tbl => l_cimv_tbl_out
);
g_module_current || '.Update_Cov_level.external_call.after',
'okc_contract_item_pub.update_contract_item(Return status = '
|| l_return_status
|| ')'
);
SELECT ca.party_id
FROM okx_customer_accounts_v ca, okx_cust_site_uses_v cs
WHERE ca.id1 = cs.cust_account_id AND cs.id1 = p_id;
SELECT party_id
FROM okx_cust_contacts_v
WHERE id1 = p_contactid AND id2 = '#';
SELECT hzr.object_id --, subject_id
,
hzr.party_id
--NPALEPU
--18-JUN-2005,09-AUG-2005
--TCA Project
--Replaced hz_party_relationships table with hz_relationships table and ra_hcontacts view with OKS_RA_HCONTACTS_V.
--Replaced hzr.party_relationship_id column with hzr.relationship_id column and added new conditions
/* FROM ra_hcontacts rah, hz_party_relationships hzr
WHERE rah.contact_id = p_contact_id
AND rah.party_relationship_id = hzr.party_relationship_id;*/
SELECT ste_code
FROM okc_statuses_b, okc_k_headers_v kh
WHERE code = kh.sts_code AND kh.ID = p_chr_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 a.id1
FROM okx_cust_sites_v a, okx_cust_contacts_v b
WHERE b.id1 = p_contact_id
AND a.id1 = b.cust_acct_site_id
AND a.org_id = okc_context.get_okc_org_id;
SELECT hzr.party_id
--NPALEPU
--18-JUN-2005,09-AUG-2005
--TCA Project
--Replaced hz_party_relationships table with hz_relationships table and ra_hcontacts view with OKS_RA_HCONTACTS_V.
--Replaced hzr.party_relationship_id column with hzr.relationship_id column and added new conditions
/* FROM ra_hcontacts rah,
hz_party_relationships hzr
WHERE rah.contact_id = p_contact_id
AND rah.party_relationship_id = hzr.party_relationship_id;*/
SELECT contact_point_id
FROM okx_contact_points_v
WHERE contact_point_type = 'EMAIL'
AND primary_flag = 'Y'
AND owner_table_id = p_party_id;
SELECT contact_point_id
FROM hz_contact_points
WHERE contact_point_type = 'PHONE'
AND NVL (phone_line_type, 'GEN') = 'GEN'
AND primary_flag = 'Y'
AND owner_table_id = p_party_id;
SELECT contact_point_id
FROM hz_contact_points
WHERE contact_point_type = 'PHONE'
AND phone_line_type = 'FAX'
AND owner_table_id = p_party_id;
SELECT GROUP_ID
FROM jtf_rs_srp_groups
WHERE salesrep_id = p_id
AND org_id = okc_context.get_okc_org_id
AND p_start_date BETWEEN start_date AND end_date
AND p_end_date BETWEEN start_date AND end_date;
SELECT booked_date, order_firmed_date
FROM oe_order_headers_all
WHERE header_id = p_ord_hdrid;
SELECT fu.user_id
FROM jtf_rs_defresources_vl jrd, fnd_user fu
WHERE jrd.resource_id = p_resource_id AND fu.user_id = jrd.user_id;
SELECT salesrep_id
FROM jtf_rs_salesreps
WHERE resource_id = p_res_id AND org_id = p_org_id;
UPDATE okc_k_headers_b
SET sts_code = l_sts_code
WHERE ID = l_chrid;
l_chrv_tbl_in (1).deleted_yn := 'N';
oks_extwar_util_pvt.update_contract_details
(l_chrid,
p_k_header_rec.order_line_id,
l_return_status
);
oks_sales_credit_pub.insert_sales_credit
(p_api_version => 1.0,
p_init_msg_list => okc_api.g_false,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_scrv_tbl => l_scrv_tbl_in,
x_scrv_tbl => l_scrv_tbl_out
);
'oks_sales_credit_pub.insert_sales_credit(Return status = '
|| l_return_status
|| ')'
);
l_ctcv_tbl_in.DELETE;
l_cgcv_tbl_in (1).last_updated_by := okc_api.g_miss_num;
l_cgcv_tbl_in (1).last_update_date := okc_api.g_miss_date;
l_cgcv_tbl_in (1).last_update_login := okc_api.g_miss_num;
l_cpsv_tbl_in (1).last_updated_by := okc_api.g_miss_num;
l_cpsv_tbl_in (1).last_update_date := okc_api.g_miss_date;
l_cpsv_tbl_in (1).last_update_login := okc_api.g_miss_num;
SELECT NVL (MAX (TO_NUMBER (line_number)), 0) + 1
FROM okc_k_lines_b
WHERE dnz_chr_id = p_chr_id AND lse_id IN (1, 12, 14, 19);
SELECT NVL (MAX (TO_NUMBER (line_number)), 0) + 1
FROM okc_k_lines_b
WHERE dnz_chr_id = p_chr_id
AND cle_id = p_cle_id
AND lse_id IN (35, 7, 8, 9, 10, 11, 13, 18, 25);
SELECT counter_group_id
FROM okx_ctr_associations_v
WHERE source_object_id = p_id;
SELECT cust_account_id
FROM okx_cust_site_uses_v
WHERE id1 = p_billto AND id2 = '#';
SELECT hzr.object_id, hzr.party_id
--NPALEPU
--18-JUN-2005,09-AUG-2005
--TCA Project
--Replaced hz_party_relationships table with hz_relationships table and ra_hcontacts view with OKS_RA_HCONTACTS_V.
--Replaced hzr.party_relationship_id column with hzr.relationship_id column and added new conditions
/* FROM ra_hcontacts rah, hz_party_relationships hzr
WHERE rah.contact_id = p_contact_id
AND rah.party_relationship_id = hzr.party_relationship_id; */
SELECT os.ste_code
FROM okc_statuses_b os, okc_k_lines_b ol
WHERE ol.ID = l_line_id AND ol.sts_code = os.code;
SELECT GROUP_ID
FROM jtf_rs_srp_groups
WHERE salesrep_id = p_id
AND org_id = okc_context.get_okc_org_id
AND p_start_date BETWEEN start_date AND end_date
AND p_end_date BETWEEN start_date AND end_date;
UPDATE okc_k_lines_b
SET sts_code = l_sts_code
WHERE ID = l_line_id;
update_hdr_dates (p_chr_id => p_k_line_rec.k_id,
p_new_sdt => l_hdrsdt,
p_new_edt => l_hdredt,
p_sts_flag => l_sts_flag,
x_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
'update_hdr_dates(Return status = '
|| l_return_status
);
'Header Effectivity Update (LINE)'
);
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
);
oks_sales_credit_pub.insert_sales_credit
(p_api_version => 1.0,
p_init_msg_list => okc_api.g_false,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_scrv_tbl => l_scrv_tbl_in,
x_scrv_tbl => l_scrv_tbl_out
);
'oks_sales_credit_pub.insert_sales_credit(Return status = '
|| l_return_status
|| ')'
);
x_update_line OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_version CONSTANT NUMBER := 1.0;
SELECT kl.start_date, kl.end_date, kl.inv_rule_id
FROM okc_k_lines_b kl
WHERE kl.ID = p_line_id;
l_update_top_line VARCHAR2 (1);
x_update_line := 'N';
update_hdr_dates (p_chr_id => p_k_covd_rec.k_id,
p_new_sdt => l_hdrsdt,
p_new_edt => l_hdredt,
p_sts_flag => l_sts_flag,
x_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
|| '.Create_K_Covered_Levels.afterupdatehdrdtaes',
'update_hdr_dates(Return status = '
|| l_return_status
|| ')'
);
'Header Effectivity Update (SUB LINE)'
);
update_line_dates (p_cle_id => p_k_covd_rec.attach_2_line_id,
p_chr_id => p_k_covd_rec.k_id,
p_new_sdt => l_line_sdt,
p_new_edt => l_line_edt,
p_sts_flag => l_sts_flag,
p_warranty_flag => p_k_covd_rec.warranty_flag,
x_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
|| '.Create_K_Covered_Levels.after_update.line_dtaes',
'update_hdr_dates(Return status = '
|| l_return_status
|| ')'
);
'LINE Effectivity Update (SUB LINE)'
);
x_update_line := 'Y';
oks_coverages_pub.update_cov_eff
(p_api_version => 1.0,
p_init_msg_list => 'F',
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_service_line_id => p_k_covd_rec.attach_2_line_id,
p_new_start_date => l_line_sdt,
p_new_end_date => l_line_edt
);
'Coverage Effectivity Update (SUB LINE)'
);
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
);
l_cimv_tbl (1).last_updated_by := fnd_global.user_id;
l_cimv_tbl (1).last_update_date := SYSDATE;
l_cimv_tbl (1).last_update_login := NULL;
l_cimv_tbl (1).program_update_date := NULL;
okc_cim_pvt.insert_row_upg (x_return_status => l_return_status,
p_cimv_tbl => l_cimv_tbl
);
'okc_cim_pvt.insert_row_upg(Return status = '
|| l_return_status
|| ')'
);
SELECT NAME
FROM okx_parties_v
WHERE id1 = p_extwar_rec.hdr_party_id;
SELECT start_date, end_date
FROM okc_k_lines_b
WHERE ID = p_id;
SELECT start_date, end_date, sts_code
FROM okc_k_headers_b
WHERE ID = p_id;
l_update_line VARCHAR2 (1);
x_update_line => l_update_line,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
IF l_update_line = 'Y'
THEN
oks_bill_sch.update_om_sll_date
(p_top_line_id => l_lineid,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
'oks_bill_sch.update_om_sll_date(Return 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 = l_lineid AND dnz_chr_id = l_chrid)
WHERE ID = l_lineid;
UPDATE okc_k_headers_b
SET estimated_amount =
(SELECT NVL (SUM (NVL (price_negotiated, 0)), 0)
FROM okc_k_lines_b
WHERE dnz_chr_id = l_chrid AND lse_id IN (1, 19))
WHERE ID = l_chrid;
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 object_cle_id, object_chr_id
FROM okc_operation_instances op,
okc_operation_lines ol,
okc_class_operations classopr,
okc_subclasses_b subclass
WHERE ol.oie_id = op.ID
AND subclass.code = 'SERVICE'
AND classopr.cls_code = subclass.cls_code
AND classopr.opn_code IN ('RENEWAL', 'REN_CON')
AND op.cop_id = classopr.ID
AND ol.subject_cle_id = p_line_id;
SELECT subject_cle_id, subject_chr_id
FROM okc_operation_instances op,
okc_operation_lines ol,
okc_class_operations cl,
okc_subclasses_b sl
WHERE ol.oie_id = op.ID
AND op.cop_id = cl.ID
AND cl.cls_code = sl.cls_code
AND sl.code = 'SERVICE'
AND cl.opn_code = p_source_code
AND ol.object_cle_id = p_line_id;
SELECT subject_cle_id, subject_chr_id
FROM okc_operation_instances op,
okc_operation_lines ol,
okc_class_operations cl,
okc_subclasses_b sl,
okc_k_items a
WHERE ol.oie_id = op.ID
AND op.cop_id = cl.ID
AND cl.cls_code = sl.cls_code
AND sl.code = 'SERVICE'
AND cl.opn_code = p_source_code
AND ol.object_cle_id = p_line_id
AND a.cle_id = ol.subject_cle_id
AND a.object1_id1 = (Select b.object1_id1
from okc_k_items b
where b.jtot_object1_code = 'OKX_CUSTPROD'
and b.cle_id = p_target_line_id)
AND a.jtot_object1_code = 'OKX_CUSTPROD';
SELECT 'Y'
FROM okc_operation_instances op,
okc_operation_lines ol,
okc_class_operations classopr,
okc_subclasses_b subclass
WHERE ol.oie_id = op.ID
AND subclass.code = 'SERVICE'
AND classopr.cls_code = subclass.cls_code
AND classopr.opn_code IN ('RENEWAL', 'REN_CON')
AND op.cop_id = classopr.ID
AND 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;
UPDATE okc_k_lines_b
SET date_renewed = p_txn_date
WHERE ID = l_source_line_id;
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_source_line_id)
AND date_renewed IS NULL;
UPDATE okc_k_headers_all_b
SET date_renewed = l_hdr_date_renewed
WHERE ID = (SELECT dnz_chr_id
FROM okc_k_lines_b
WHERE ID = l_source_line_id)
AND date_renewed IS NULL;
SELECT NVL (SUM (amount), 0)
FROM oks_bill_sub_lines_v
WHERE cle_id = p_cle_id;
SELECT elmnts.ID
FROM oks_stream_levels_b strm, oks_level_elements elmnts
WHERE strm.cle_id = p_cle_id AND elmnts.rul_id = strm.ID;
SELECT NVL (SUM (amount), 0)
FROM oks_bill_sub_lines bsl
WHERE bsl.cle_id = p_cle_id
AND EXISTS (SELECT 1
FROM oks_bill_cont_lines bcl
WHERE bcl.ID = bsl.bcl_id AND bill_action = 'TR');
SELECT ID, object_version_number
FROM oks_k_lines_b
WHERE cle_id = p_id;
SELECT NVL (SUM (trx_line_tax_amount), 0)
FROM oks_bill_txn_lines
WHERE bsl_id IN (SELECT ID
FROM oks_bill_sub_lines
WHERE cle_id = p_cle_id);
SELECT b.concatenated_segments description
FROM mtl_system_items_b_kfv b
WHERE b.inventory_item_id = p_serv_id AND ROWNUM < 2;
SELECT object1_id1
FROM okc_k_rel_objs
WHERE cle_id = p_line_id;
SELECT instance_number
FROM csi_item_instances
WHERE instance_id = p_cp_id;
l_update_line VARCHAR2 (1);
l_inst_dtls_tbl.DELETE;
UPDATE okc_k_items
SET number_of_items = l_old_qty
WHERE cle_id = p_kdtl_tbl (l_ctr).object_line_id;
UPDATE okc_k_lines_b
SET price_negotiated = NVL (l_oldamt, 0),
price_unit = NVL (p_kdtl_tbl (l_ctr).service_unit_price, 0)
WHERE ID = p_kdtl_tbl (l_ctr).object_line_id;
|| '.CREATE_CONTRACT_IBSPLIT.after_update.cov_lvl',
' update_cov_level(Return 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 => x_msg_count,
x_msg_data => x_msg_data,
p_klnv_tbl => l_klnv_tbl_in,
x_klnv_tbl => l_klnv_tbl_out,
p_validate_yn => 'N'
);
'oks_contract_line_pub.update_line(Return status = '
|| l_return_status
|| ')'
);
x_update_line => l_update_line,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
UPDATE okc_k_lines_b
SET price_negotiated =
(SELECT NVL (SUM (NVL (price_negotiated, 0)), 0)
FROM okc_k_lines_b
WHERE cle_id = p_kdtl_tbl (l_ctr).service_line_id
AND dnz_chr_id = p_kdtl_tbl (l_ctr).hdr_id)
WHERE ID = p_kdtl_tbl (l_ctr).service_line_id;
UPDATE okc_k_headers_b
SET estimated_amount =
(SELECT NVL (SUM (NVL (price_negotiated, 0)), 0)
FROM okc_k_lines_b
WHERE dnz_chr_id = p_kdtl_tbl (l_ctr).hdr_id
AND lse_id IN (1, 19))
WHERE ID = p_kdtl_tbl (l_ctr).hdr_id;
oks_ins_pvt.insert_row (p_api_version => 1.0,
p_init_msg_list => 'T',
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_insv_rec => l_insthist_rec,
x_insv_rec => x_insthist_rec
);
'oks_ins_pvt.insert_row(Return status = '
|| l_return_status
|| ')'
);
oks_ihd_pvt.insert_row (p_api_version => 1.0,
p_init_msg_list => 'T',
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_ihdv_tbl => l_inst_dtls_tbl,
x_ihdv_tbl => x_inst_dtls_tbl
);
'oks_ihd_pvt.insert_row(Return status = '
|| l_return_status
|| ')'
);
PROCEDURE update_contract_ibreplace (
p_kdtl_tbl IN contract_tbl,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
--billed upto date
CURSOR l_billend_csr (p_cle_id NUMBER)
IS
SELECT MAX (date_billed_to) date_billed_to
FROM oks_bill_sub_lines
WHERE cle_id = p_cle_id;
SELECT ksl1.inheritance_type
FROM oks_k_lines_b ksl, oks_k_lines_b ksl1
WHERE ksl.cle_id = p_cle_id AND ksl1.cle_id = ksl.coverage_id;
SELECT elmnts.ID
FROM oks_stream_levels_b strm, oks_level_elements elmnts
WHERE strm.cle_id = p_cle_id AND elmnts.rul_id = strm.ID;
SELECT NVL (SUM (amount), 0)
FROM oks_bill_sub_lines_v
WHERE cle_id = p_cle_id;
SELECT NVL (SUM (amount), 0)
FROM oks_bill_sub_lines bsl
WHERE bsl.cle_id = p_cle_id
AND EXISTS (SELECT 1
FROM oks_bill_cont_lines bcl
WHERE bcl.ID = bsl.bcl_id AND bill_action = 'TR');
SELECT ID, object_version_number
FROM oks_k_lines_b
WHERE cle_id = p_id;
SELECT NVL (SUM (trx_line_tax_amount), 0)
FROM oks_bill_txn_lines
WHERE bsl_id IN (SELECT ID
FROM oks_bill_sub_lines
WHERE cle_id = p_cle_id);
SELECT b.concatenated_segments description
FROM mtl_system_items_b_kfv b
WHERE b.inventory_item_id = p_serv_id AND ROWNUM < 2;
SELECT object1_id1
FROM okc_k_rel_objs
WHERE cle_id = p_line_id;
SELECT object_cle_id
FROM okc_operation_instances op, okc_operation_lines ol
WHERE ol.oie_id = op.ID
AND op.cop_id IN (41, 40)
AND ol.subject_cle_id = p_line_id;
SELECT object_cle_id
FROM okc_operation_instances op, okc_operation_lines ol
WHERE ol.oie_id = op.ID
AND op.cop_id = 11017
AND ol.subject_cle_id = p_line_id;
SELECT instance_number
FROM csi_item_instances
WHERE instance_id = p_cp_id;
l_update_line VARCHAR2 (1);
l_inst_dtls_tbl.DELETE;
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
);
|| '.CREATE_CONTRACT_IBREPLACE.update_cov_lvl',
'okc_contract_pub.update_contract_line(Return status = '
|| l_return_status
|| 'count = '
|| p_kdtl_tbl.COUNT
|| ')'
);
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 => x_msg_count,
x_msg_data => x_msg_data,
p_klnv_tbl => l_klnv_tbl_in,
x_klnv_tbl => l_klnv_tbl_out,
p_validate_yn => 'N'
);
|| '.CREATE_CONTRACT_IBREPLACE.after_update.cov_lvl_tax',
'oks_contract_line_pub.update_line(Return status = '
|| l_return_status
|| ')'
);
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 => p_kdtl_tbl (l_ctr).hdr_id,
p_cle_id => p_kdtl_tbl (l_ctr).object_line_id,
p_new_sts_code => l_sts_code,
p_canc_reason_code => 'REPLACE',
p_old_sts_code => p_kdtl_tbl (l_ctr).prod_sts_code,
p_old_ste_code => 'ENTERED',
p_new_ste_code => 'CANCELLED',
p_term_cancel_source => 'IBREPLACE',
p_date_cancelled => TRUNC (l_termdt),
p_comments => NULL,
p_validate_status => 'N'
);
'okc_contract_pub.update_contract_line(Return status ='
|| l_return_status
|| ')'
);
x_update_line => l_update_line,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
UPDATE okc_k_lines_b
SET price_negotiated = price_negotiated + l_newamt
WHERE ID = l_lineid;
UPDATE okc_k_headers_b
SET estimated_amount = estimated_amount + l_newamt
WHERE ID = p_kdtl_tbl (l_ctr).hdr_id;
UPDATE oks_k_lines_b
SET tax_amount = tax_amount + l_newtax
WHERE cle_id = l_lineid;
UPDATE oks_k_headers_b
SET tax_amount = tax_amount + l_newtax
WHERE chr_id = p_kdtl_tbl (l_ctr).hdr_id;
oks_ins_pvt.insert_row (p_api_version => 1.0,
p_init_msg_list => 'T',
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_insv_rec => l_insthist_rec,
x_insv_rec => x_insthist_rec
);
'oks_ins_pvt.insert_row(Return status = '
|| l_return_status
|| ')'
);
oks_ihd_pvt.insert_row (p_api_version => 1.0,
p_init_msg_list => 'T',
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_ihdv_tbl => l_inst_dtls_tbl,
x_ihdv_tbl => x_inst_dtls_tbl
);
'oks_ihd_pvt.insert_row(Return status = '
|| l_return_status
|| ')'
);
SELECT t.description NAME
FROM mtl_system_items_tl t
WHERE t.inventory_item_id = p_serv_id
AND t.LANGUAGE = USERENV ('LANG')
AND ROWNUM < 2;
SELECT instance_number
FROM csi_item_instances
WHERE instance_id = p_cp_id;
Select sts_code
From Okc_k_headers_all_b
WHere id = p_hdr_id;
l_clev_tbl_in.delete;
l_chrv_tbl_in.delete;
l_inst_dtls_tbl.DELETE;
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
);
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
);
'okc_contract_pub.update_contract_header(Return status =
'
|| l_return_status
|| ')'
);
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 => p_kdtl_tbl (l_ctr).hdr_id,
p_cle_id => p_kdtl_tbl (l_ctr).object_line_id,
p_new_sts_code => l_sts_code,
p_canc_reason_code => 'RETURN',
p_old_sts_code => p_kdtl_tbl (l_ctr).prod_sts_code,
p_old_ste_code => 'ENTERED',
p_new_ste_code => 'CANCELLED',
p_term_cancel_source => 'IBRETURN',
p_date_cancelled => TRUNC (l_retdt),
p_comments => NULL,
p_validate_status => 'N'
);
'oks_change_status_pvt.Update_line_status (Return status ='
|| l_return_status
|| ')'
);
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 => p_kdtl_tbl (l_ctr).hdr_id,
p_cle_id => p_kdtl_tbl (l_ctr).service_line_id,
p_new_sts_code => l_sts_code,
p_canc_reason_code => 'RETURN',
p_old_sts_code => p_kdtl_tbl (l_ctr).prod_sts_code,
p_old_ste_code => 'ENTERED',
p_new_ste_code => 'CANCELLED',
p_term_cancel_source => 'IBRETURN',
p_date_cancelled => TRUNC (date_cancelled),
p_comments => NULL,
p_validate_status => 'N'
);
'oks_change_status_pvt.Update_line_status (Return status = '
|| l_return_status
|| ')'
);
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 => p_kdtl_tbl (l_ctr).hdr_id,
p_new_sts_code => l_sts_code,
p_canc_reason_code => 'RETURN',
p_old_sts_code => p_kdtl_tbl (l_ctr).prod_sts_code,
p_comments => NULL,
p_term_cancel_source => 'IBRETURN',
p_date_cancelled => TRUNC (date_cancelled),
p_validate_status => 'N'
);
oks_ins_pvt.insert_row (p_api_version => 1.0,
p_init_msg_list => 'T',
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_insv_rec => l_insthist_rec,
x_insv_rec => x_insthist_rec
);
'oks_ins_pvt.insert_row(Return status = '
|| l_return_status
|| ')'
);
oks_ihd_pvt.insert_row (p_api_version => 1.0,
p_init_msg_list => 'T',
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_ihdv_tbl => l_inst_dtls_tbl,
x_ihdv_tbl => x_inst_dtls_tbl
);
'oks_ihd_pvt.insert_row(Return status = '
|| l_return_status
|| ')'
);
Update oks_inst_hist_details set new_k_status = l_hdr_status
Where ins_id = l_instparent_id and new_contract_id = p_kdtl_tbl (l_ctr).hdr_id;
SELECT LN.transfer_option
FROM oks_k_lines_b LN, okc_k_lines_b kl, oks_k_lines_b ks
WHERE kl.ID = p_cle_id
AND ks.cle_id = kl.ID
AND LN.cle_id = ks.coverage_id;
SELECT DISTINCT new_contract_id
FROM oks_inst_hist_details
WHERE system_id = p_system_id
AND transaction_date = p_trxn_date
AND transaction_type = 'TRF'
AND old_customer = p_cust_id
AND new_contract_id <> old_contract_id;
SELECT qcl_id
FROM okc_k_headers_b
WHERE ID = p_id;
SELECT start_date, end_date
FROM okc_k_headers_b
WHERE ID = p_hdr_id;
SELECT start_date, end_date
FROM okc_k_lines_b
WHERE ID = p_line_id;
SELECT DISTINCT relationship_type
FROM hz_relationships
WHERE ( ( object_id = p_new_customer
AND subject_id = p_old_customer
)
OR ( object_id = p_old_customer
AND subject_id = p_new_customer
)
)
AND relationship_type = p_relation
AND status = 'A'
AND TRUNC (p_transfer_date) BETWEEN TRUNC (start_date)
AND TRUNC (end_date);
SELECT sts_code
FROM okc_k_headers_b
WHERE ID = p_hdr_id;
SELECT 'Y'
FROM okc_k_headers_b Kh, oks_k_headers_b Ks
, okc_statuses_b sts
WHERE Kh.ID = p_hdr_id
And Ks.chr_id = Kh.id
And kh.sts_code = sts.code
And sts.ste_code = 'ENTERED'
And ks.wf_item_key is null;
SELECT date_terminated, price_negotiated
FROM okc_k_lines_b
WHERE ID = p_id;
SELECT kl.ID cle_id
FROM okc_k_lines_b kl, okc_k_items ki, okc_statuses_b st
WHERE kl.dnz_chr_id = p_chr_id
AND kl.lse_id IN (1, 14, 19)
AND kl.ID = ki.cle_id
AND ki.object1_id1 = TO_CHAR (p_service_item_id)
AND st.code = kl.sts_code
AND st.ste_code NOT IN ('TERMINATED', 'CANCELLED');
SELECT instance_number
FROM csi_item_instances
WHERE instance_id = p_cp_id;
SELECT t.description NAME
FROM mtl_system_items_tl t
WHERE t.inventory_item_id = p_serv_id
AND t.LANGUAGE = USERENV ('LANG')
AND ROWNUM < 2;
l_update_line VARCHAR2 (1);
SELECT op.ID
FROM okc_operation_instances op,
okc_class_operations classopr,
okc_subclasses_b subclass
WHERE target_chr_id = p_target_chr_id
AND subclass.code = 'SERVICE'
AND classopr.cls_code = subclass.cls_code
AND classopr.opn_code IN ('TRANSFER')
AND op.cop_id = classopr.ID;
SELECT op.ID
FROM okc_operation_instances op,
okc_class_operations classopr,
okc_subclasses_b subclass
WHERE target_chr_id = p_target_chr_id
AND subclass.code = 'SERVICE'
AND classopr.cls_code = subclass.cls_code
AND classopr.opn_code IN ('RENEWAL', 'REN_CON')
AND op.cop_id = classopr.ID;
Select id
From Okc_operation_instances
Where target_chr_id = p_target_chr_id
And cop_id in (40,41);
SELECT party_id, NAME
FROM okx_customer_accounts_v
WHERE id1 = p_cust_id;
SELECT id1
FROM okx_cust_site_uses_v
WHERE cust_account_id = p_customer_id
AND party_id = p_party_id
AND site_use_code = p_code
AND identifying_address_flag = 'Y'
AND status = 'A'
AND org_id = p_org_id;
SELECT party_id, NAME
FROM okx_customer_accounts_v
WHERE id1 = p_id;
SELECT isa_agreement_id
FROM okc_governances
WHERE chr_id = p_chr_id AND dnz_chr_id = p_chr_id;
SELECT bill_to_address, ship_to_address
FROM csi_instance_party_v
WHERE instance_id = p_cp_id;
SELECT 'x'
FROM okx_cust_site_uses_v
WHERE id1 = p_id
AND site_use_code = p_site_use_code
AND org_id = p_org_id
AND status = 'A';
SELECT resource_id, GROUP_ID, access_level
FROM okc_k_accesses_v
WHERE chr_id = p_hdr_id;
SELECT ctc_id, sales_credit_type_id1, PERCENT, sales_group_id
FROM oks_k_sales_credits_v
WHERE chr_id = p_chr_id AND cle_id IS NULL;
SELECT authoring_org_id
INTO l_org_id
FROM okc_k_headers_b
WHERE ID = l_chrid;
SELECT oks.acct_rule_id, okc.inv_rule_id, okc.price_list_id
FROM okc_k_lines_b okc, oks_k_lines_b oks
WHERE okc.ID = p_line_id AND oks.cle_id = okc.ID;
SELECT party_id, NAME
FROM okx_customer_accounts_v
WHERE id1 = p_id;
SELECT st.ste_code, kh.sts_code
FROM okc_k_headers_b kh, okc_statuses_b st
WHERE kh.ID = p_hdr_id AND st.code = kh.sts_code;
SELECT bill_to_address, ship_to_address
FROM csi_instance_party_v
WHERE instance_id = p_cp_id;
SELECT DECODE (fnd_profile.VALUE ('OKS_ITEM_DISPLAY_PREFERENCE'), 'DISPLAY_DESC', t.description , b.concatenated_segments )
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_serv_id
AND ROWNUM < 2;
SELECT ctc_id, sales_credit_type_id1, PERCENT, sales_group_id
FROM oks_k_sales_credits_v
WHERE cle_id = p_cle_id;
x_update_top_line OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER
)
IS
/* Cursor l_Cust_csr IS --29-apr-03
Select csi.last_oe_order_line_id Original_order_line_id
,csi.inventory_item_id
,csi.quantity
,csi.unit_of_measure uom_code
From csi_item_instances csi
Where csi.instance_id = p_kdtl_rec.old_cp_id;
SELECT st.ste_code, kl.sts_code
FROM okc_k_lines_b kl, okc_statuses_b st
WHERE kl.ID = p_line_id AND st.code = kl.sts_code;
SELECT price_negotiated
FROM okc_k_lines_b
WHERE ID = p_line_id;
SELECT DECODE (fnd_profile.VALUE ('OKS_ITEM_DISPLAY_PREFERENCE'), 'DISPLAY_DESC', t.description , b.concatenated_segments )
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_serv_id
AND ROWNUM < 2;
SELECT b.concatenated_segments description
FROM mtl_system_items_b_kfv b
WHERE b.inventory_item_id = p_serv_id AND ROWNUM < 2;
l_update_top_line VARCHAR2 (1);
x_update_line => l_update_top_line,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
x_update_top_line := l_update_top_line;
p_update_line IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER
)
IS
l_strmlvl_id NUMBER;
select uom_code
from okc_time_code_units_v
where tce_code='DAY'
and quantity=1;
IF p_update_line = 'Y'
THEN
oks_bill_sch.update_om_sll_date
(p_top_line_id => p_line_id,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
'oks_bill_sch.update_om_sll_date(Return status = '
|| l_return_status
|| ')'
);
l_clev_tbl_in.delete;
l_chrv_tbl_in.delete;
l_inst_dtls_tbl.DELETE;
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
);
'okc_contract_pub.update_contract_line(Return 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
);
|| '.Update_Hdr_Dates.external_call.after',
'okc_contract_pub.update_contract_header(Return status = '
|| l_return_status
|| ')'
);
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 => p_kdtl_tbl (l_ctr).hdr_id,
p_cle_id => p_kdtl_tbl (l_ctr).object_line_id,
p_new_sts_code => l_sts_code,
p_canc_reason_code => 'TRANSFER',
p_old_sts_code => p_kdtl_tbl (l_ctr).prod_sts_code,
p_old_ste_code => 'ENTERED',
p_new_ste_code => 'CANCELLED',
p_term_cancel_source => 'IBTRANSFER',
p_date_cancelled => TRUNC (l_trfdt),
p_comments => NULL,
p_validate_status => 'N'
);
'okc_contract_pub.update_contract_line(Return status ='
|| l_return_status
|| ')'
);
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 => p_kdtl_tbl (l_ctr).hdr_id,
p_cle_id => p_kdtl_tbl (l_ctr).service_line_id,
p_new_sts_code => l_sts_code,
p_canc_reason_code => 'TRANSFER',
p_old_sts_code => p_kdtl_tbl (l_ctr).prod_sts_code,
p_old_ste_code => 'ENTERED',
p_new_ste_code => 'CANCELLED',
p_term_cancel_source => 'IBTRANSFER',
p_date_cancelled => TRUNC
(date_cancelled),
p_comments => NULL,
p_validate_status => 'N'
);
'okc_contract_pub.update_contract_line(Return status = '
|| l_return_status
|| ')'
);
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 => p_kdtl_tbl (l_ctr).hdr_id,
p_new_sts_code => l_sts_code,
p_canc_reason_code => 'TRANSFER',
p_old_sts_code => p_kdtl_tbl (l_ctr).prod_sts_code,
p_comments => NULL,
p_term_cancel_source => 'IBTRANSFER',
p_date_cancelled => TRUNC
(date_cancelled),
p_validate_status => 'N'
);
'oks_change_status_pvt.Update_header_status(Return status = '
|| l_return_status
|| ')'
);
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
);
'okc_contract_pub.update_contract_line(Return 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
);
|| '.Update_Hdr_Dates.external_call.after',
'okc_contract_pub.update_contract_header(Return status = '
|| l_return_status
|| ')'
);
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 => p_kdtl_tbl (l_ctr).hdr_id,
p_cle_id => p_kdtl_tbl (l_ctr).object_line_id,
p_new_sts_code => l_sts_code,
p_canc_reason_code => 'TRANSFER',
p_old_sts_code => p_kdtl_tbl (l_ctr).prod_sts_code,
p_old_ste_code => 'ENTERED',
p_new_ste_code => 'CANCELLED',
p_term_cancel_source => 'IBTRANSFER',
p_date_cancelled => TRUNC (l_trfdt),
p_comments => NULL,
p_validate_status => 'N'
);
'oks_change_status_pvt.Update_line_status(Return status ='
|| l_return_status
|| ')'
);
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 => p_kdtl_tbl (l_ctr).hdr_id,
p_cle_id => p_kdtl_tbl (l_ctr).service_line_id,
p_new_sts_code => l_sts_code,
p_canc_reason_code => 'TRANSFER',
p_old_sts_code => p_kdtl_tbl (l_ctr).prod_sts_code,
p_old_ste_code => 'ENTERED',
p_new_ste_code => 'CANCELLED',
p_term_cancel_source => 'IBTRANSFER',
p_date_cancelled => TRUNC
(date_cancelled),
p_comments => NULL,
p_validate_status => 'N'
);
'okc_contract_pub.update_contract_line(Return status = '
|| l_return_status
|| ')'
);
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 => p_kdtl_tbl (l_ctr).hdr_id,
p_new_sts_code => l_sts_code,
p_canc_reason_code => 'TRANSFER',
p_old_sts_code => p_kdtl_tbl (l_ctr).prod_sts_code,
p_comments => NULL,
p_term_cancel_source => 'IBTRANSFER',
p_date_cancelled => TRUNC
(date_cancelled),
p_validate_status => 'N'
);
x_update_top_line => l_update_line,
x_return_status => l_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count
);
p_update_line => l_update_line,
x_return_status => l_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count
);
UPDATE okc_k_lines_b
SET price_negotiated =
(SELECT NVL
(SUM
(NVL
(price_negotiated,
0
)
),
0
)
FROM okc_k_lines_b
WHERE cle_id =
l_merge_line_id
AND dnz_chr_id =
l_merge_chr_id)
WHERE ID = l_merge_line_id;
UPDATE oks_k_lines_b
SET tax_amount =
NVL (tax_amount, 0)
+ NVL ((SELECT tax_amount
FROM oks_k_lines_b
WHERE cle_id =
l_subline_id),
0
)
WHERE cle_id = l_merge_line_id;
UPDATE okc_k_headers_b
SET estimated_amount =
(SELECT NVL
(SUM
(NVL
(price_negotiated,
0
)
),
0
)
FROM okc_k_lines_b
WHERE dnz_chr_id =
l_merge_chr_id
AND lse_id IN (1, 19))
WHERE ID = l_merge_chr_id;
UPDATE oks_k_headers_b
SET tax_amount =
NVL (tax_amount, 0)
+ NVL ((SELECT tax_amount
FROM oks_k_lines_b
WHERE cle_id =
l_subline_id),
0
)
WHERE chr_id = l_merge_chr_id;
UPDATE okc_k_headers_b
SET sts_code = l_sts_code,
date_approved = NULL,
date_signed = NULL
WHERE ID = l_merge_chr_id;
UPDATE okc_k_lines_b
SET sts_code = l_sts_code
WHERE dnz_chr_id =
l_merge_chr_id;
UPDATE oks_k_lines_b
SET coverage_id = l_coverage_id,
standard_cov_yn = 'N'
WHERE cle_id = l_line_id;
x_update_top_line => l_update_line,
p_line_id => l_line_id,
x_return_status => l_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count
);
p_update_line => l_update_line,
x_return_status => l_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count
);
UPDATE okc_k_lines_b
SET price_negotiated =
(SELECT NVL
(SUM
(NVL
(price_negotiated,
0
)
),
0
)
FROM okc_k_lines_b
WHERE cle_id = l_line_id
AND dnz_chr_id =
l_merge_chr_id)
WHERE ID = l_line_id;
UPDATE oks_k_lines_b
SET tax_amount =
NVL (tax_amount, 0)
+ NVL ((SELECT tax_amount
FROM oks_k_lines_b
WHERE cle_id =
l_subline_id),
0
)
WHERE cle_id = l_line_id;
UPDATE okc_k_headers_b
SET estimated_amount =
(SELECT NVL
(SUM
(NVL
(price_negotiated,
0
)
),
0
)
FROM okc_k_lines_b
WHERE dnz_chr_id =
l_merge_chr_id
AND lse_id IN (1, 19))
WHERE ID = l_merge_chr_id;
UPDATE oks_k_headers_b
SET tax_amount =
NVL (tax_amount, 0)
+ NVL ((SELECT tax_amount
FROM oks_k_lines_b
WHERE cle_id =
l_subline_id),
0
)
WHERE chr_id = l_merge_chr_id;
UPDATE okc_k_headers_b
SET sts_code = l_sts_code,
date_approved = NULL,
date_signed = NULL
WHERE ID = l_merge_chr_id;
UPDATE okc_k_lines_b
SET sts_code = l_sts_code
WHERE dnz_chr_id = l_merge_chr_id;
UPDATE oks_k_lines_b
SET coverage_id = l_coverage_id,
standard_cov_yn = 'N'
WHERE cle_id = l_line_id;
x_update_top_line => l_update_line,
p_line_id => l_line_id,
x_return_status => l_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count
);
UPDATE okc_k_lines_b
SET price_negotiated =
(SELECT NVL (SUM (NVL (price_negotiated,
0
)
),
0
)
FROM okc_k_lines_b
WHERE cle_id = l_line_id
AND dnz_chr_id = l_chr_id)
WHERE ID = l_line_id;
UPDATE oks_k_lines_b
SET tax_amount =
NVL (tax_amount, 0)
+ NVL ((SELECT tax_amount
FROM oks_k_lines_b
WHERE cle_id = l_subline_id), 0)
WHERE cle_id = l_line_id;
UPDATE okc_k_headers_b
SET estimated_amount =
(SELECT NVL (SUM (NVL (price_negotiated,
0
)
),
0
)
FROM okc_k_lines_b
WHERE dnz_chr_id = l_chr_id
AND lse_id IN (1, 19))
WHERE ID = l_chr_id;
UPDATE oks_k_headers_b
SET tax_amount =
NVL (tax_amount, 0)
+ NVL ((SELECT tax_amount
FROM oks_k_lines_b
WHERE cle_id = l_subline_id), 0)
WHERE chr_id = l_chr_id;
p_update_line => l_update_line,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
x_return_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 = l_line_id
AND dnz_chr_id = l_chr_id)
WHERE id = l_line_id;
UPDATE okc_k_headers_b
SET estimated_amount = ( SELECT NVL( SUM( NVL(price_negotiated,0) ), 0 )
FROM okc_k_lines_b
WHERE dnz_chr_id = l_chr_id
AND lse_id in (1,19) )
WHERE id = l_chr_id;*/
UPDATE okc_k_headers_b
SET sts_code = l_sts_code,
date_approved = NULL,
date_signed = NULL
WHERE ID = l_chr_id;
UPDATE okc_k_lines_b
SET sts_code = l_sts_code
WHERE dnz_chr_id = l_chr_id;
oks_ins_pvt.insert_row
(p_api_version => 1.0,
p_init_msg_list => 'T',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_insv_rec => l_insthist_rec,
x_insv_rec => x_insthist_rec
);
'oks_ins_pvt.insert_row(Return status = '
|| l_return_status
|| ')'
);
oks_ihd_pvt.insert_row (p_api_version => 1.0,
p_init_msg_list => 'T',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_ihdv_tbl => l_inst_dtls_tbl,
x_ihdv_tbl => x_inst_dtls_tbl
);
'oks_ihd_pvt.insert_row(Return status = '
|| l_return_status
|| ')'
);
Update oks_inst_hist_details set new_k_status = l_hdr_sts
Where ins_id = l_instparent_id and new_contract_id = p_kdtl_tbl (l_ctr).hdr_id;
Update oks_inst_hist_details set new_k_status = l_hdr_sts
Where system_id = p_kdtl_tbl (l_ctr).system_id and new_contract_id = p_kdtl_tbl (l_ctr).hdr_id
and transaction_date = p_kdtl_tbl (l_ctr).transaction_date;
SELECT t.description NAME
FROM mtl_system_items_tl t
WHERE t.inventory_item_id = p_serv_id
AND t.LANGUAGE = USERENV ('LANG')
AND ROWNUM < 2;
SELECT instance_number
FROM csi_item_instances
WHERE instance_id = p_cp_id;
Select sts_code
From Okc_k_headers_all_b
WHere id = p_hdr_id;
l_clev_tbl_in.delete;
l_chrv_tbl_in.delete;
l_inst_dtls_tbl.DELETE;
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
);
'oks_bill_rec_pub.update_contract_line(Return 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
);
|| '.Update_Hdr_Dates.external_call.after',
'okc_contract_pub.update_contract_header(Return status = '
|| l_return_status
|| ')'
);
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 => p_kdtl_tbl (l_ctr).hdr_id,
p_cle_id => p_kdtl_tbl (l_ctr).object_line_id,
p_new_sts_code => l_sts_code,
p_canc_reason_code => 'TERMINATED',
p_old_sts_code => p_kdtl_tbl (l_ctr).prod_sts_code,
p_old_ste_code => 'ENTERED',
p_new_ste_code => 'CANCELLED',
p_term_cancel_source => 'IBTERMINATE',
p_date_cancelled => TRUNC (l_trmdt),
p_comments => NULL,
p_validate_status => 'N'
);
'okc_contract_pub.update_contract_line(Return status ='
|| l_return_status
|| ')'
);
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 => p_kdtl_tbl (l_ctr).hdr_id,
p_cle_id => p_kdtl_tbl (l_ctr).service_line_id,
p_new_sts_code => l_sts_code,
p_canc_reason_code => 'TERMINATED',
p_old_sts_code => p_kdtl_tbl (l_ctr).prod_sts_code,
p_old_ste_code => 'ENTERED',
p_new_ste_code => 'CANCELLED',
p_term_cancel_source => 'IBTERMINATE',
p_date_cancelled => TRUNC (date_cancelled),
p_comments => NULL,
p_validate_status => 'N'
);
'okc_contract_pub.update_contract_line(Return status = '
|| l_return_status
|| ')'
);
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 => p_kdtl_tbl (l_ctr).hdr_id,
p_new_sts_code => l_sts_code,
p_canc_reason_code => 'TERMINATED',
p_old_sts_code => p_kdtl_tbl (l_ctr).prod_sts_code,
p_comments => NULL,
p_term_cancel_source => 'IBTERMINATE',
p_date_cancelled => TRUNC (date_cancelled),
p_validate_status => 'N'
);
oks_ins_pvt.insert_row (p_api_version => 1.0,
p_init_msg_list => 'T',
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_insv_rec => l_insthist_rec,
x_insv_rec => x_insthist_rec
);
'oks_ins_pvt.insert_row(Return status = '
|| l_return_status
|| ')'
);
oks_ihd_pvt.insert_row (p_api_version => 1.0,
p_init_msg_list => 'T',
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_ihdv_tbl => l_inst_dtls_tbl,
x_ihdv_tbl => x_inst_dtls_tbl
);
'oks_ihd_pvt.insert_row(Return status = '
|| l_return_status
|| ')'
);
Update oks_inst_hist_details set new_k_status = l_hdr_status
Where ins_id = l_instparent_id and new_contract_id = p_kdtl_tbl (l_ctr).hdr_id;
PROCEDURE update_contract_idc (
p_kdtl_tbl IN contract_tbl,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
-- Cursor to check cov attribute set
CURSOR l_cov_csr (p_cle_id NUMBER)
IS
SELECT KL.sync_date_install
FROM oks_k_lines_b LN, oks_k_lines_b kl
WHERE LN.cle_id = p_cle_id AND kl.cle_id = LN.coverage_id;
SELECT 'X'
FROM cs_incidents_all_b
WHERE customer_product_id = p_cp_id
AND contract_service_id = p_line_id
AND ( TRUNC (creation_date) <= TRUNC (p_sdt)
OR TRUNC (creation_date) >= TRUNC (p_edt)
);
SELECT MIN (start_date) sdt, MAX (end_date) edt
FROM okc_k_lines_b
WHERE cle_id = p_topline_id
AND dnz_chr_id = p_hdr_id
AND lse_id = 18
AND ID <> p_subline_id;
SELECT MIN (start_date) sdt, MAX (end_date) edt
FROM okc_k_lines_b
WHERE dnz_chr_id = p_hdr_id AND cle_id IS NULL
AND ID <> p_topline_id;
SELECT start_date, end_date
FROM okc_k_lines_b
WHERE ID = p_id;
SELECT start_date, end_date, sts_code
FROM okc_k_headers_b
WHERE ID = p_id;
SELECT ID, object_version_number
FROM oks_k_lines_b
WHERE cle_id = p_id;
SELECT t.description NAME, b.concatenated_segments description
FROM mtl_system_items_b_kfv b,
mtl_system_items_tl t,
okc_k_items ki
WHERE ki.cle_id = p_serv_line_id
AND b.inventory_item_id = TO_CHAR (ki.object1_id1)
AND t.inventory_item_id = b.inventory_item_id
AND t.organization_id = b.organization_id
AND t.LANGUAGE = USERENV ('LANG')
AND ROWNUM < 2;
SELECT instance_number
FROM csi_item_instances
WHERE instance_id = p_cp_id;
l_update_line VARCHAR2 (1);
g_module_current || '.UPDATE_CONTRACT_IDC.after.',
'count = ' || p_kdtl_tbl.COUNT || ')'
);
l_inst_dtls_tbl.DELETE;
|| '.UPDATE_CONTRACT_IDC.after.get_k_dtls',
'Okc_time_util_pub.get_duration(Return status = '
|| l_return_status
|| ',Duration = '
|| l_duration
|| ',Time units = '
|| l_timeunits
|| ')'
);
g_module_current || '.UPDATE_CONTRACT_IDC',
'Coverage attribute = ' || l_cov_att
);
|| '.UPDATE_CONTRACT_IDC.ERROR',
'SR is logged '
|| ',status = '
|| l_return_status
);
|| '.UPDATE_CONTRACT_IDC',
'Header start date = '
|| l_hdr_sdt
|| ',End date = '
|| l_hdr_edt
|| ',status = '
|| l_sts_flag
);
update_hdr_dates
(p_chr_id => p_kdtl_tbl (l_ctr).hdr_id,
p_new_sdt => l_hdr_sdt,
p_new_edt => l_hdr_edt,
p_sts_flag => l_sts_flag,
x_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
|| '.UPDATE_CONTRACT_IDC',
'Update_hdr_dates(Return status = '
|| l_return_status
|| ')'
);
|| '.UPDATE_CONTRACT_IDC',
'Top line start date = '
|| l_top_sdt
|| ',End date = '
|| l_top_edt
|| ',status = '
|| l_sts_flag
);
update_line_dates
(p_cle_id => p_kdtl_tbl (l_ctr).service_line_id,
p_chr_id => p_kdtl_tbl (l_ctr).hdr_id,
p_new_sdt => l_top_sdt,
p_new_edt => l_top_edt,
p_sts_flag => l_sts_flag,
p_warranty_flag => 'W',
x_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
|| '.UPDATE_CONTRACT_IDC',
'Update_Line_dates(Return 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 => x_msg_count,
x_msg_data => x_msg_data,
p_klnv_tbl => l_klnv_tbl_in,
x_klnv_tbl => l_klnv_tbl_out,
p_validate_yn => 'N'
);
g_module_current || '.UPDATE_CONTRACT_IDC',
'oks_contract_line_pub.update_line(Return status = '
|| l_return_status
|| ')'
);
oks_coverages_pub.update_cov_eff
(p_api_version => 1.0,
p_init_msg_list => 'T',
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_service_line_id => p_kdtl_tbl (l_ctr).service_line_id,
p_new_start_date => l_top_sdt,
p_new_end_date => l_top_edt
);
g_module_current || '.UPDATE_CONTRACT_IDC',
'oks_coverages_pub.update_cov_eff(Return status = '
|| l_return_status
|| ')'
);
g_module_current || '.UPDATE_CONTRACT_IDC',
'oks_pm_programs_pvt.ADJUST_PM_PROGRAM_SCHEDULE(Return status = '
|| l_return_status
|| ')'
);
|| '.UPDATE_CONTRACT_IDC',
'sub line start date = '
|| l_sub_sdt
|| ',End date'
|| l_sub_edt
);
update_line_dates
(p_cle_id => p_kdtl_tbl (l_ctr).object_line_id,
p_chr_id => p_kdtl_tbl (l_ctr).hdr_id,
p_new_sdt => l_sub_sdt,
p_new_edt => l_sub_edt,
p_sts_flag => 'Y',
p_warranty_flag => 'W',
x_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
|| '.UPDATE_CONTRACT_IDC',
'Update_line_dates(Return 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 => x_msg_count,
x_msg_data => x_msg_data,
p_klnv_tbl => l_klnv_tbl_in,
x_klnv_tbl => l_klnv_tbl_out,
p_validate_yn => 'N'
);
g_module_current || '.UPDATE_CONTRACT_IDC',
'oks_contract_line_pub.update_line(Return status = '
|| l_return_status
|| ')'
);
g_module_current || '.UPDATE_CONTRACT_IDC',
'coverage attribute not set'
);
oks_ins_pvt.insert_row (p_api_version => 1.0,
p_init_msg_list => 'T',
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_insv_rec => l_insthist_rec,
x_insv_rec => x_insthist_rec
);
g_module_current || '.UPDATE_CONTRACT_IDC',
'oks_ins_pvt.insert_row(Return status = '
|| l_return_status
|| ')'
);
oks_ihd_pvt.insert_row (p_api_version => 1.0,
p_init_msg_list => 'T',
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_ihdv_tbl => l_inst_dtls_tbl,
x_ihdv_tbl => x_inst_dtls_tbl
);
g_module_current || '.UPDATE_CONTRACT_IDC',
'oks_ihd_pvt.insert_row(Return status = '
|| l_return_status
|| ')'
);
PROCEDURE create_contract_ibupdate (
p_kdtl_tbl IN contract_tbl,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
CURSOR l_cust_csr (p_inventory_item_id NUMBER)
IS
SELECT mtl.NAME, mtl.description
FROM okx_system_items_v mtl
WHERE mtl.inventory_item_id = p_inventory_item_id
AND mtl.organization_id = okc_context.get_okc_organization_id;
SELECT SUM (amount)
FROM oks_bill_sub_lines_v
WHERE cle_id = p_cle_id;
SELECT b.concatenated_segments description
FROM mtl_system_items_b_kfv b
WHERE b.inventory_item_id = p_serv_id AND ROWNUM < 2;
SELECT instance_number
FROM csi_item_instances
WHERE instance_id = p_cp_id;
l_update_top_line VARCHAR2 (1);
SELECT price_negotiated
FROM okc_k_lines_b
WHERE ID = p_cle_id;
l_update_id NUMBER;
g_module_current || '.CREATE_CONTRACT_IBUPDATE.',
'count = ' || p_kdtl_tbl.COUNT || ')'
);
l_inst_dtls_tbl.DELETE;
|| '.CREATE_CONTRACT_IBUPDATE',
'lse_id= ' || p_kdtl_tbl (l_ctr).lse_id
);
UPDATE okc_k_items
SET number_of_items = p_kdtl_tbl (l_ctr).new_quantity
WHERE cle_id = p_kdtl_tbl (l_ctr).object_line_id;
|| '.CREATE_CONTRACT_IBUPDATE',
'Billed amount = '
|| l_billed_amount
|| ',Service amount = '
|| p_kdtl_tbl (l_ctr).service_amount
);
UPDATE okc_k_items
SET number_of_items =
p_kdtl_tbl (l_ctr).new_quantity
WHERE cle_id = p_kdtl_tbl (l_ctr).object_line_id;
g_module_current || '.CREATE_CONTRACT_IBUPDATE',
'oks_qp_int_pvt.compute_price(Return status = '
|| l_return_status
|| ',Repriced amount = '
|| l_amount
|| ')'
);
g_module_current || '.CREATE_CONTRACT_IBUPDATE',
'Oks_bill_sch.Create_Bill_Sch_CP(Return 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 = p_kdtl_tbl (l_ctr).service_line_id
AND dnz_chr_id = p_kdtl_tbl (l_ctr).hdr_id
and date_cancelled is null)
WHERE ID = p_kdtl_tbl (l_ctr).service_line_id;
UPDATE oks_k_lines_b
SET tax_amount =
(SELECT (NVL (SUM (NVL(tax_amount,0)),0))
FROM oks_k_lines_b
WHERE cle_id IN ( SELECT id
FROM okc_k_lines_b
WHERE cle_id = p_kdtl_tbl (l_ctr).service_line_id
AND lse_id IN (9,25)
AND date_cancelled IS NULL ))
WHERE cle_id =p_kdtl_tbl (l_ctr).service_line_id;
UPDATE okc_k_headers_b
SET estimated_amount =
(SELECT NVL (SUM (NVL (price_negotiated, 0)), 0 )
FROM okc_k_lines_b
WHERE dnz_chr_id = p_kdtl_tbl (l_ctr).hdr_id
AND lse_id IN (1, 19)
AND date_cancelled IS NULL )
WHERE ID = p_kdtl_tbl (l_ctr).hdr_id;
UPDATE oks_k_headers_b
SET tax_amount = ( SELECT (NVL (SUM (NVL(tax_amount,0)),0))
FROM oks_k_lines_b
WHERE cle_id IN (SELECT id
FROM okc_k_lineS_b
WHERE dnz_chr_id = p_kdtl_tbl (l_ctr).hdr_id
AND date_cancelled IS NULL
AND lse_id IN (1,19)))
WHERE chr_id = p_kdtl_tbl (l_ctr).hdr_id;
|| '.CREATE_CONTRACT_IBUPDATE',
'The Line is fully billed'
);
|| '.CREATE_CONTRACT_IBUPDATE.ERROR',
' Contract in QA_HOLD status'
);
'Quantity Update not allowed. Contract '
|| p_kdtl_tbl (l_ctr).contract_number
|| ' is in QA_HOLD status'
);
p_term_cancel_source => 'IBUPDATE',
x_return_status => l_return_status
);
|| '.CREATE_CONTRACT_IBUPDATE',
' oks_bill_rec_pub.Pre_terminate_cp(Retun status = '
|| l_return_status
|| ')'
);
x_update_line => l_update_top_line,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
|| '.CREATE_CONTRACT_IBUPDATE',
'oks_extwarprgm_pvt.create_k_covered_levels(Retun status = '
|| l_return_status
|| ')'
);
p_source_code => 'IBUPDATE',
p_target_chr_id => p_kdtl_tbl (l_ctr).hdr_id,
p_source_line_id => p_kdtl_tbl (l_ctr).object_line_id,
p_source_chr_id => p_kdtl_tbl (l_ctr).hdr_id,
p_target_line_id => l_covlvl_id,
x_oper_instance_id => l_opr_instance_id,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
|| '.CREATE_CONTRACT_IBUPDATE',
'Create_transaction_source(Retun status = '
|| l_return_status
|| ')'
);
p_source_code => 'IBUPDATE',
p_target_chr_id => p_kdtl_tbl (l_ctr).hdr_id,
p_line_id => p_kdtl_tbl (l_ctr).object_line_id,
p_target_line_id => l_covlvl_id,
p_txn_date => p_kdtl_tbl (l_ctr).transaction_date,
x_oper_instance_id => l_renewal_opr_instance_id,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
|| '.CREATE_CONTRACT_IBUPDATE',
'Create_source_links(Retun status = '
|| l_return_status
|| ')'
);
|| '.CREATE_CONTRACT_IBUPDATE',
'oks_qp_int_pvt.compute_price(Retun status = '
|| l_return_status
|| ',Repriced amount = '
|| l_output_details.serv_ext_amount
|| ')'
);
|| '.CREATE_CONTRACT_IBUPDATE',
'Subline Amount = '
|| l_subline_amount
);
UPDATE okc_k_lines_b
SET price_negotiated =
(SELECT NVL (SUM (NVL (price_negotiated,0 ) ), 0 )
FROM okc_k_lines_b
WHERE cle_id = p_kdtl_tbl (l_ctr).service_line_id
AND dnz_chr_id = p_kdtl_tbl (l_ctr).hdr_id
AND date_cancelled is NULL)
WHERE ID = p_kdtl_tbl (l_ctr).service_line_id;
UPDATE oks_k_lines_b
SET tax_amount =
(SELECT (NVL (SUM (NVL(tax_amount,0)),0))
FROM oks_k_lines_b
WHERE cle_id IN ( SELECT id
FROM okc_k_lines_b
WHERE cle_id = p_kdtl_tbl (l_ctr).service_line_id
AND lse_id IN (9,25)
AND date_cancelled IS NULL ))
WHERE cle_id =p_kdtl_tbl (l_ctr).service_line_id;
UPDATE okc_k_headers_b
SET estimated_amount =
(SELECT NVL (SUM (NVL (price_negotiated,
0
)
),
0
)
FROM okc_k_lines_b
WHERE dnz_chr_id =
p_kdtl_tbl (l_ctr).hdr_id
AND lse_id IN (1, 19)
AND date_cancelled IS NULL)
WHERE ID = p_kdtl_tbl (l_ctr).hdr_id;
UPDATE oks_k_headers_b
SET tax_amount = ( SELECT (NVL (SUM (NVL(tax_amount,0)),0))
FROM oks_k_lines_b
WHERE cle_id IN (SELECT id
FROM okc_k_lineS_b
WHERE dnz_chr_id = p_kdtl_tbl (l_ctr).hdr_id
AND date_cancelled IS NULL
AND lse_id IN (1,19)))
WHERE chr_id = p_kdtl_tbl (l_ctr).hdr_id;
|| '.CREATE_CONTRACT_IBUPDATE',
'oks_bill_sch.create_bill_sch_cp(Retun status = '
|| l_return_status
|| ')'
);
UPDATE okc_k_items
SET number_of_items =
p_kdtl_tbl (l_ctr).new_quantity
WHERE cle_id = p_kdtl_tbl (l_ctr).object_line_id;
|| '.CREATE_CONTRACT_IBUPDATE',
'oks_qp_int_pvt.compute_price(Retun status = '
|| l_return_status
|| ',Repriced amount = '
|| l_output_details.serv_ext_amount
|| ')'
);
UPDATE okc_k_lines_b
SET price_negotiated = l_billed_amount
WHERE ID = p_kdtl_tbl (l_ctr).object_line_id;
UPDATE okc_k_lines_b
SET price_negotiated =
(SELECT NVL (SUM (NVL (price_negotiated,
0
)
),
0
)
FROM okc_k_lines_b
WHERE cle_id =
p_kdtl_tbl (l_ctr).service_line_id
AND dnz_chr_id =
p_kdtl_tbl (l_ctr).hdr_id
AND date_cancelled IS NULL)
WHERE ID = p_kdtl_tbl (l_ctr).service_line_id;
UPDATE oks_k_lines_b
SET tax_amount =
(SELECT (NVL (SUM (NVL(tax_amount,0)),0))
FROM oks_k_lines_b
WHERE cle_id IN ( SELECT id
FROM okc_k_lines_b
WHERE cle_id = p_kdtl_tbl (l_ctr).service_line_id
AND lse_id IN (9,25)
AND date_cancelled IS NULL ))
WHERE cle_id =p_kdtl_tbl (l_ctr).service_line_id;
UPDATE okc_k_headers_b
SET estimated_amount =
(SELECT NVL (SUM (NVL (price_negotiated,
0
)
),
0
)
FROM okc_k_lines_b
WHERE dnz_chr_id =
p_kdtl_tbl (l_ctr).hdr_id
AND lse_id IN (1, 19)
AND date_cancelled IS NULL)
WHERE ID = p_kdtl_tbl (l_ctr).hdr_id;
UPDATE oks_k_headers_b
SET tax_amount = ( SELECT (NVL (SUM (NVL(tax_amount,0)),0))
FROM oks_k_lines_b
WHERE cle_id IN (SELECT id
FROM okc_k_lineS_b
WHERE dnz_chr_id = p_kdtl_tbl (l_ctr).hdr_id
AND date_cancelled IS NULL
AND lse_id IN (1,19)))
WHERE chr_id = p_kdtl_tbl (l_ctr).hdr_id;
|| '.CREATE_CONTRACT_IBUPDATE',
'oks_bill_sch.create_bill_sch_cp(Retun status = '
|| l_return_status
|| ')'
);
oks_ins_pvt.insert_row
(p_api_version => 1.0,
p_init_msg_list => 'T',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_insv_rec => l_insthist_rec,
x_insv_rec => x_insthist_rec
);
|| '.CREATE_CONTRACT_IBUPDATE',
'oks_ins_pvt.insert_row(Return status = '
|| l_return_status
|| ')'
);
oks_ihd_pvt.insert_row (p_api_version => 1.0,
p_init_msg_list => 'T',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_ihdv_tbl => l_inst_dtls_tbl,
x_ihdv_tbl => x_inst_dtls_tbl
);
|| '.CREATE_CONTRACT_IBUPDATE',
'oks_ihd_pvt.insert_row(Return status = '
|| l_return_status
|| ')'
);
SELECT a.party_name, b.country, b.region_2
FROM hr_locations b, hr_all_organization_units c, hz_parties a
WHERE b.location_id = c.location_id
AND c.organization_id = p_authorg_id
AND a.party_id = p_party_id;
SELECT hz.party_name, hzl.country, hzl.state
FROM hz_parties hz, hz_party_sites hzs, hz_locations hzl
WHERE hz.party_id = p_party_id
AND hzs.party_id = hz.party_id
AND hzs.identifying_address_flag = 'Y'
AND hzl.location_id = hzs.location_id;
SELECT user_name
FROM fnd_user
WHERE user_id = p_user_id;
SELECT order_number
FROM oe_order_headers_all
WHERE header_id = p_order_id;
SELECT contract_number || ' ' || contract_number_modifier
FROM okc_k_headers_all_b
WHERE ID = p_contract_id;