[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select csi.instance_id
from csi_item_instances csi , oe_order_lines_all ol
where ol.line_id = csi.last_oe_order_line_id
and ol.inventory_item_id = csi.inventory_item_id
and ol.line_id = p_order_line_id ;
Select 'x'
From okc_k_rel_objs rel
,okc_k_lines_b line
,okc_k_items item
Where rel.Object1_Id1 = to_char(p_srvord_line_id)
And rel.jtot_object1_code = 'OKX_ORDERLINE'
And item.cle_id = line.id
And item.object1_id1 = to_char(p_item_id)
And item.jtot_object1_code = 'OKX_CUSTPROD'
And line.id = rel.cle_id
And line.lse_id in (9,25)
And line.dnz_chr_id = item.dnz_chr_id;
Select Party_Id from OKX_CUSTOMER_ACCOUNTS_V
Where Id1 = p_custid;
Procedure Update_Contract_Details
( p_hdr_id Number,
p_order_line_id number,
x_return_status Out NOCOPY Varchar2
)
Is
Cursor l_link_csr1 Is
Select NVL(link_ord_line_id1, order_line_id1)
From Oks_k_order_details
Where order_line_id1 = to_char(p_ordeR_line_id);
Select id ,ordeR_line_id1,object_version_number
From Oks_k_order_details
Where link_ord_line_id1 = l_link_ord_id
And Chr_id Is NULL;
Select id ,ordeR_line_id1,object_version_number
From Oks_k_order_details
Where order_line_id1 = l_link_ord_id
And Chr_id Is NULL;
OKS_COD_PVT.update_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_codv_tbl => l_codv_tbl_in,
x_codv_tbl => l_codv_tbl_out
);
fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.UPDATE_CONTRACT_DETAILS',
'oks_cod_pvt.update_row(Return status = '|| l_return_status || ')');
OKS_COD_PVT.update_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_codv_tbl => l_codv_tbl_in,
x_codv_tbl => l_codv_tbl_out
);
fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.UPDATE_CONTRACT_DETAILS',
'oks_cod_pvt.update_row(Return status = '|| l_return_status || ')');
fnd_log.string(FND_LOG.LEVEL_UNEXPECTED,G_MODULE_CURRENT||'.UPDATE_CONTRACT_DETAILS.UNEXPECTED',
'sqlcode = '||sqlcode||', sqlerrm = '||sqlerrm);
Select Header_id from OKX_ORDER_LINES_V
Where Id1 = p_order_line_id;
Select Chr_id
,Renewal_type
,po_required_yn
,Renewal_pricing_type
,Markup_percent
,Price_list_id1
,link_chr_id
,contact_id
,email_id
,phone_id
,fax_id
,site_id
,cod_type
,billing_profile_id --new parameter added -vigandhi (May29-02)
,line_renewal_type
From Oks_K_Order_Details_V
Where ORDER_LINE_ID1 = to_char(p_order_line_id);
Select MTL.Name
,MTL.Description
,MTL.Primary_UOM_Code
,MTL.Service_starting_delay
From OKX_SYSTEM_ITEMS_V MTL
Where MTL.id1 = p_Inventory_id
And MTL.Organization_id = okc_context.get_okc_organization_id;
select org_id
,sold_from_org_id
,ship_from_org_id
from oe_order_lines_all
where Line_id = p_line_id;
Select master_organization_id
From oe_system_parameters_all
where org_id = p_org_id;
select instance_number
from csi_item_instances
where instance_id = p_cp_id;
Select instance_id
From Oks_subscr_header_b
Where instance_id = p_instance_id
And rownum < 2;
Select csi.last_oe_agreement_id Product_agreement_id
,oh.transactional_curr_code Original_order_currency_code
From CSI_ITEM_INSTANCES Csi,
OE_ORDER_HEADERS_ALL OH,
OE_ORDER_LINES_ALL OL
Where csi.instance_id = p_id
And csi.last_oe_order_line_id = ol.line_id
And oh.header_id = ol.header_id;
Select OC.Object1_id1
,OC.cro_code
From OKS_K_ORDER_CONTACTS_V OC
,OKS_K_ORDER_DETAILS_V OD
Where OC.cod_id = OD.id
-- And OD.order_line_id1 = p_line_id;
Select fulfilled_quantity
From OKX_ORDER_LINES_V
Where Id1 = p_id;
Select Chr_id
From OKC_K_REL_OBJS RO, OkC_K_HEADERS_V OH
Where RO.OBJECT1_ID1 = to_char(P_Object_id)
And RO.jtot_object1_code = 'OKX_ORDERHEAD'
And RO.rty_code = 'CONTRACTWARRANTYORDER'
And RO.chr_id = OH.id
And OH.sts_code not in ('TERMINATED','CANCELLED');
Select scs_code
From OKC_K_HEADERS_V
Where id = p_chr_id;
Select rel.cle_id
From okc_k_rel_objs rel
,okc_k_lines_b line
,okc_k_items item
Where rel.Object1_Id1 in (to_char(p_ordlineid),to_char(p_serv_ordline_id))
And rel.jtot_object1_code = 'OKX_ORDERLINE'
And item.cle_id = line.id
And item.object1_id1 = to_char(p_item_id)
And item.jtot_object1_code = 'OKX_CUSTPROD'
And line.id = rel.cle_id
And line.lse_id in (9, 25)
And line.dnz_chr_id = item.dnz_chr_id;
Select rel.cle_id
From okc_k_rel_objs_v rel
,okc_k_lines_b line
Where rel.Object1_Id1 in (to_char(p_ordlineid),to_char(p_serv_ordline_id))
And rel.jtot_object1_code = 'OKX_ORDERLINE'
And line.id = rel.cle_id
And line.lse_id in (1,19);
Select line.id
From Okc_k_lines_b line
,okc_k_items item
WHere item.cle_id = line.id
ANd line.cle_id = p_cle_id
And line.lse_id in (9, 25)
And item.object1_id1 = to_char(p_cp_id)
ANd item.jtot_object1_code = 'OKX_CUSTPROD'
And item.dnz_chr_id = line.dnz_chr_id;
Select OH.SOLD_TO_ORG_ID
From OE_Order_Headers_ALL OH,
OE_ORDER_LINES_ALL OL
Where OH.Header_id = OL.Header_id
AND OL.line_id = p_line_id;
Update Okc_k_headers_all_b
Set last_updated_by = last_updated_by
Where id = -1;
' Order Line Routine. '|| ';'|| 'Order header id = '||p_order_header_id ||'Before update statement' );
To avoid that a update statement is included, so that the second order
line waits until the first OL is processed.
*/
-- Commented out for the fix of bug# 5088409 (JVARGHES)
--
-- Update Oe_Order_Headers_all
-- Set last_updated_by = last_updated_by
-- Where header_id = p_order_header_id;
' After update statement' );
p_contact_tbl.delete;
Select old_customer_product_id
,old_quantity
,Bom_explosion_flag
,Old_Unit_of_measure
,Old_Inventory_item_id
,Old_Customer_acct_id
,Organization_id
,Bill_to_site_use_id
,Ship_to_site_use_id
,Org_id
,Order_line_id
,Shipped_date
,Installation_date
,transaction_date
From Oks_instance_temp
Where New = 'Y';
Select KI.Dnz_Chr_Id
,KH.authoring_org_id
,KH.start_date hdr_sdt
,KH.end_date hdr_edt
,KH.sts_code hdr_sts
,KH.Contract_number
,KH.scs_code
,OL.Id LineId
,OL.line_NUMBER
,KIS.object1_id1
,KI.CLE_ID
,OL.Start_date Line_start_date
,OL.End_Date line_end_date
,OL.bill_to_site_use_id
,OL.Ship_to_site_use_id
,KL.Price_Negotiated Service_amount
,OKL.tax_amount
,KSL.tax_code
,KL.Price_unit
,OL.Currency_Code Service_Currency
,KI.NUMBER_OF_ITEMS
,OL.cust_acct_id
,KSL.Acct_rule_id line_acct_rule_id
,OL.inv_rule_id line_inv_rule_id
,Kh.price_list_id
,KH.payment_term_id
,KS.acct_rule_id
,KH.Inv_rule_id
,KS.AR_interface_yn
,KS.Summary_trx_yn
,KS.Hold_billing
,KS.Inv_trx_type
,KS.Payment_type
,KH.inv_organization_id
,KH.Conversion_type
,KH.Conversion_rate
,KH.COnversion_rate_date
,KH.Conversion_euro_rate
,KH.Billed_at_source
,Kl.cle_id_renewed
,OL.sts_code line_sts_code
,KL.sts_code
,KL.start_date
,KL.end_date
,KL.date_terminated
,KL.lse_id
,KL.Name
,KL.Item_description
,KL.line_renewal_type_code
,KL.upg_orig_system_ref
,KL.upg_orig_system_ref_id
,KH.cust_po_number
,KH.currency_code
,PARTY.Object1_id1 Party_id
,tmp.old_customer_product_id
,tmp.old_inventory_item_id
,tmp.transfer_date
,tmp.transaction_date
,tmp.old_customer_acct_id
,tmp.new_customer_acct_id
,tmp.System_id
,tmp.old_customer_product_id
,KSL.Coverage_Id
,KSL.standard_cov_yn
,KS.Period_Start
,KS.Period_type
,tmp.old_unit_of_measure
,okl.price_uom sl_price_uom
,ksl.price_uom tl_price_uom
,ks.price_uom hdr_price_uom
,okl.toplvl_uom_code
,okl.toplvl_price_qty
From OKC_K_ITEMS KI
,OKC_K_HEADERS_ALL_B KH
,OKS_K_HEADERS_B KS
,OKC_K_LINES_V KL
,OKS_K_LINES_B OKL
,OKC_STATUSES_B ST
,OKC_K_PARTY_ROLES_B PARTY
, OKC_K_LINES_B OL
,OKS_K_LINES_B KSL
,OKC_K_ITEMS KIS
, OKS_INSTANCE_TEMP tmp
Where tmp.trf = '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 KS.Chr_id(+) = KH.ID
And KH.scs_code in ('WARRANTY','SERVICE','SUBSCRIPTION' ) -- supp
And KI.Cle_id = KL.id
And OKL.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 OL.Id = KL.cle_id
And KSL.cle_id(+) = OL.Id
And KIS.cle_id = OL.Id
And KIS.dnz_chr_id = OL.dnz_chr_id
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.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.lse_id <> 18
And not exists (Select 'x'
from okc_operation_instances ois,
okc_operation_lines opl,
okc_class_operations cls,
okc_subclasses_b sl
where ois.id=opl.oie_id
And cls.opn_code in ('RENEWAL','REN_CON')
And sl.code= 'SERVICE'
And sl.cls_code = cls.cls_code
and ois.cop_id = cls.id
and object_cle_id=kl.id
)
)
)
order by tmp.old_customer_product_id, kh.creation_date; --KI.Dnz_Chr_Id;
Select tmp.old_customer_product_id
,tmp.termination_date
,tmp.installation_date
,tmp.transaction_date
,tmp.old_Customer_acct_id
,tmp.new_customer_acct_id
,tmp.System_id
,tmp.old_quantity
,tmp.new_quantity
,tmp.new_customer_product_id
,KI.CLE_ID SubLine_id
, KI.Dnz_Chr_Id
,KH.start_date hdr_sdt
,KH.end_date hdr_edt
,KH.sts_code hdr_sts
, KL.Cle_Id
,KL.Price_negotiated
, KL.Start_date
, KL.end_date
, KL.sts_code prod_sts
, KL.Cust_acct_id
, TL.start_date Srv_sdt
, TL.end_date Srv_edt
, KH.sts_code
, KH.Contract_number
, KI.number_of_items
, TL.price_negotiated
, KL.date_terminated
,tmp.old_inventory_item_id
, KH.authoring_org_id
,KH.inv_organization_id
,KL.lse_id
, KH.scs_code
, tmp.new_customer_product_id
, KIS.Object1_id1
, TL.Currency_code
,tmp.old_unit_of_measure
, KL.line_renewal_type_code
, tmp.raise_credit
,PARTY.Object1_id1 Party_id
, Null
, Null
, Null
, Null
, Null
, Null
, Null
, tmp.return_reason_code
, tmp.order_line_id
, Null
, Null
, Null
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_ITEMS KIS
,OKC_K_PARTY_ROLES_B PARTY
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 KI.Cle_id = KL.id
And TL.Id = KL.cle_id
And KIS.cle_id = TL.id
And KIS.dnz_chr_id = TL.dnz_chr_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 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.lse_id <> 18
And not exists (Select 'x'
from okc_operation_instances ois,
okc_operation_lines opl,
okc_class_operations cls,
okc_subclasses_b sl
where ois.id=opl.oie_id
And cls.opn_code in ('RENEWAL','REN_CON')
And sl.code= 'SERVICE'
And sl.cls_code = cls.cls_code
and ois.cop_id = cls.id
and object_cle_id=kl.id)
))
Union
Select tmp.old_customer_product_id Instance_id
,tmp.termination_date
,tmp.installation_date
,tmp.transaction_date
,tmp.old_Customer_acct_id
,tmp.new_customer_acct_id
,tmp.System_id
,tmp.old_quantity
,tmp.new_quantity
,tmp.new_customer_product_id
,KI.CLE_ID SubLine_id
, KI.Dnz_Chr_Id
,KH.start_date hdr_sdt
,KH.end_date hdr_edt
,KH.sts_code hdr_sts
, KL.Cle_Id
,KL.Price_negotiated
, KL.Start_date
, KL.end_date
, KL.sts_code prod_sts
, KL.Cust_acct_id
, TL.start_date Srv_sdt
, TL.end_date Srv_edt
, KH.sts_code
, KH.Contract_number
, KI.number_of_items
, TL.price_negotiated
, KL.date_terminated
,tmp.old_inventory_item_id
, KH.authoring_org_id
,KH.inv_organization_id
,KL.lse_id
, KH.scs_code
, tmp.new_customer_product_id
, KIS.Object1_id1
, TL.Currency_code
,tmp.old_unit_of_measure
, KL.line_renewal_type_code
, tmp.raise_credit
,PARTY.Object1_id1 Party_id
, Null
, Null
, Null
, Null
, Null
, Null
, Null
, tmp.return_reason_code
, tmp.order_line_id
, Null
, Null
, Null
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_ITEMS KIS
,OKC_K_PARTY_ROLES_B PARTY
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 KI.Cle_id = KL.id
And TL.Id = KL.cle_id
And KIS.cle_id = TL.id
And KIS.dnz_chr_id = TL.dnz_chr_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 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 not exists (Select 'x'
from okc_operation_instances ois,
okc_operation_lines opl,
okc_class_operations cls,
okc_subclasses_b sl
where ois.id=opl.oie_id
And cls.opn_code in ('RENEWAL','REN_CON')
And sl.code= 'SERVICE'
And sl.cls_code = cls.cls_code
and ois.cop_id = cls.id
and object_cle_id=kl.id)
));
Select tmp.old_customer_product_id Instance_id
,tmp.termination_date
,tmp.installation_date
,tmp.transaction_date
,tmp.old_Customer_acct_id
,tmp.new_customer_acct_id
,tmp.System_id
,tmp.old_quantity
,tmp.new_quantity
,tmp.new_customer_product_id
,KI.CLE_ID SubLine_id
, KI.Dnz_Chr_Id
,KH.start_date hdr_sdt
,KH.end_date hdr_edt
,KH.sts_code hdr_sts
, KL.Cle_Id
,KL.Price_negotiated
, KL.Start_date
, KL.end_date
, KL.sts_code prod_sts
, KL.Cust_acct_id
, TL.start_date Srv_sdt
, TL.end_date Srv_edt
, KH.sts_code
, KH.Contract_number
, KI.number_of_items
, TL.price_negotiated
, KL.date_terminated
,tmp.old_inventory_item_id
, KH.authoring_org_id
,KH.inv_organization_id
,KL.lse_id
, KH.scs_code
, tmp.new_customer_product_id
, KIS.Object1_id1
, TL.Currency_code
,tmp.old_unit_of_measure
, KL.line_renewal_type_code
, tmp.raise_credit
,PARTY.Object1_id1 Party_id
, Null
, Null
, Null
, Null
, Null
, Null
, Null
, tmp.return_reason_code
, tmp.order_line_id
, Null
, Null
, Null
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_ITEMS KIS
,OKC_K_PARTY_ROLES_B PARTY
Where tmp.ret = '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 KI.Cle_id = KL.id
And TL.Id = KL.cle_id
And KIS.cle_id = TL.id
And KIS.dnz_chr_id = TL.dnz_chr_id
And KL.sts_code = ST.code
And ST.ste_code not in ('TERMINATED','CANCELLED')
And KL.date_terminated Is Null
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 KH.template_yn = 'N'
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.lse_id <> 18
And not exists (Select 'x'
from okc_operation_instances ois,
okc_operation_lines opl,
okc_class_operations cls,
okc_subclasses_b sl
where ois.id=opl.oie_id
And cls.opn_code in ('RENEWAL','REN_CON')
And sl.code= 'SERVICE'
And sl.cls_code = cls.cls_code
and ois.cop_id = cls.id
and object_cle_id=kl.id)
))
Union
Select tmp.old_customer_product_id
,tmp.termination_date
,tmp.installation_date
,tmp.transaction_date
,tmp.old_Customer_acct_id
,tmp.new_customer_acct_id
,tmp.System_id
,tmp.old_quantity
,tmp.new_quantity
,tmp.new_customer_product_id
,KI.CLE_ID SubLine_id
, KI.Dnz_Chr_Id
,KH.start_date hdr_sdt
,KH.end_date hdr_edt
,KH.sts_code hdr_sts
, KL.Cle_Id
,KL.Price_negotiated
, KL.Start_date
, KL.end_date
, KL.sts_code prod_sts
, KL.Cust_acct_id
, TL.start_date Srv_sdt
, TL.end_date Srv_edt
, KH.sts_code
, KH.Contract_number
, KI.number_of_items
, TL.price_negotiated
, KL.date_terminated
,tmp.old_inventory_item_id
, KH.authoring_org_id
,KH.inv_organization_id
,KL.lse_id
, KH.scs_code
, tmp.new_customer_product_id
, KIS.Object1_id1
, TL.Currency_code
,tmp.old_unit_of_measure
, KL.line_renewal_type_code
, tmp.raise_credit
, PARTY.Object1_id1 Party_id
, Null
, Null
, Null
, Null
, Null
, Null
, Null
, tmp.return_reason_code
, tmp.order_line_id
, null
, null
, null
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_ITEMS KIS
,OKC_K_PARTY_ROLES_B PARTY
Where tmp.ret = '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 KI.Cle_id = KL.id
And TL.Id = KL.cle_id
And KIS.cle_id = TL.id
And KIS.dnz_chr_id = TL.dnz_chr_id
And KL.sts_code = ST.code
And ST.ste_code not in ('TERMINATED','CANCELLED')
And KL.date_terminated Is Null
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 KH.template_yn = 'N'
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 not exists (Select 'x'
from okc_operation_instances ois,
okc_operation_lines opl,
okc_class_operations cls,
okc_subclasses_b sl
where ois.id=opl.oie_id
And cls.opn_code in ('RENEWAL','REN_CON')
And sl.code= 'SERVICE'
And sl.cls_code = cls.cls_code
and ois.cop_id = cls.id
and object_cle_id=kl.id)
));
Select tmp.old_customer_product_id
,tmp.termination_date
,tmp.installation_date
,tmp.transaction_date
,tmp.old_Customer_acct_id
,tmp.new_customer_acct_id
,tmp.System_id
,tmp.old_quantity
,tmp.new_quantity
,tmp.new_customer_product_id
,KI.CLE_ID SubLine_id
, KI.Dnz_Chr_Id
,KH.start_date hdr_sdt
,KH.end_date hdr_edt
,KH.sts_code hdr_sts
, KL.Cle_Id
,KL.Price_negotiated
, KL.Start_date
, KL.end_date
, KL.sts_code prod_sts
, KL.Cust_acct_id
, TL.start_date Srv_sdt
, TL.end_date Srv_edt
, KH.sts_code
, KH.Contract_number
, KI.number_of_items
, TL.price_negotiated
, KL.date_terminated
,tmp.old_inventory_item_id
, KH.authoring_org_id
,KH.inv_organization_id
,KL.lse_id
, KH.scs_code
, tmp.new_customer_product_id
, KIS.Object1_id1
, TL.Currency_code
,tmp.old_unit_of_measure
, KL.line_renewal_type_code
, tmp.raise_credit
, null
, Null
, Null
, Null
, Null
, Null
, null
, Null
, tmp.return_reason_code
, tmp.order_line_id
, null
, null
, null
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_ITEMS KIS
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 KIS.cle_id = TL.id
And KIS.dnz_chr_id = TL.dnz_chr_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.lse_id = 18;
Select tmp.old_customer_product_id
,tmp.termination_date
,tmp.installation_date
,tmp.transaction_date
,tmp.old_Customer_acct_id
,tmp.new_customer_acct_id
,tmp.System_id
,tmp.old_quantity
,tmp.new_quantity
,tmp.new_customer_product_id
,KI.CLE_ID SubLine_id
, KI.Dnz_Chr_Id
,KH.start_date hdr_sdt
,KH.end_date hdr_edt
,KH.sts_code hdr_sts
, KL.Cle_Id
,KL.Price_negotiated
, KL.Start_date
, KL.end_date
, KL.sts_code prod_sts
, KL.Cust_acct_id
, TL.start_date Srv_sdt
, TL.end_date Srv_edt
, KH.sts_code
, KH.Contract_number
, KI.number_of_items
, TL.price_negotiated
, KL.date_terminated
,tmp.old_inventory_item_id
, KH.authoring_org_id
,KH.inv_organization_id
,KL.lse_id
, KH.scs_code
, tmp.new_customer_product_id
, KIS.Object1_id1
, TL.Currency_code
,tmp.old_unit_of_measure
, KL.line_renewal_type_code
, tmp.raise_credit
, null
, OKL.tax_amount
, KL.Price_unit
, KL.Name
, KL.Item_description
, KL.upg_orig_system_ref
, KL.upg_orig_system_ref_id
, tmp.new_inventory_item_id
, tmp.return_reason_code
, tmp.order_line_id
, okl.price_uom
, okl.toplvl_uom_code
, okl.toplvl_price_qty
From OKC_K_ITEMS KI
, OKC_K_HEADERS_ALL_B KH
, OKC_K_LINES_v KL
, OKC_STATUSES_B ST
, OKS_INSTANCE_TEMP tmp
, OKC_K_LINES_B TL
,OKC_K_ITEMS KIS
, OKS_K_LINES_B OKL
Where tmp.spl = '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', 'SERVICE', 'SUBSCRIPTION')
And KI.Cle_id = KL.id
And TL.Id = KL.cle_id
And KIS.cle_id = TL.id
And KIS.dnz_chr_id = TL.dnz_chr_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 OKL.cle_id = kl.id
And ((trunc(tmp.transaction_date) <= trunc(KL.end_date)And trunc(tmp.transaction_date) >= trunc(KL.start_date))
OR (trunc(tmp.transaction_date) <= trunc(kl.start_date)) )
order by tmp.old_customer_product_id, kh.creation_date; --KI.Dnz_Chr_Id;
Select tmp.old_customer_product_id Instance_id
,tmp.termination_date
,tmp.installation_date
,tmp.transaction_date
,tmp.old_Customer_acct_id
,tmp.new_customer_acct_id
,tmp.System_id
,tmp.old_quantity
,tmp.new_quantity
,tmp.new_customer_product_id
,KI.CLE_ID SubLine_id
, KI.Dnz_Chr_Id
,KH.start_date hdr_sdt
,KH.end_date hdr_edt
,KH.sts_code hdr_sts
, KL.Cle_Id
,KL.Price_negotiated
, KL.Start_date
, KL.end_date
, KL.sts_code prod_sts
, KL.Cust_acct_id
, TL.start_date Srv_sdt
, TL.end_date Srv_edt
, KH.sts_code
, KH.Contract_number
, KI.number_of_items
, TL.price_negotiated
, KL.date_terminated
, tmp.old_inventory_item_id
, KH.authoring_org_id
, KH.inv_organization_id
, KL.lse_id
, KH.scs_code
, tmp.new_customer_product_id
, KIS.Object1_id1
, TL.Currency_code
, tmp.old_unit_of_measure
, KL.line_renewal_type_code
, tmp.raise_credit
, null
, OKL.tax_amount
, KL.Price_unit
, KL.Name
, KL.Item_description
, KL.upg_orig_system_ref
, KL.upg_orig_system_ref_id
, tmp.new_inventory_item_id
, tmp.return_reason_code
, tmp.order_line_id
, okl.price_uom
, okl.toplvl_uom_code
, okl.toplvl_price_qty
From OKC_K_ITEMS KI
, OKC_K_HEADERS_ALL_B KH
, OKC_K_LINES_v KL
, OKC_STATUSES_B ST
, OKS_INSTANCE_TEMP tmp
, OKC_K_LINES_B TL
,OKC_K_ITEMS KIS
, OKS_K_LINES_B OKL
Where tmp.rpl = '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 KI.Cle_id = KL.id
And TL.Id = KL.cle_id
And KIS.cle_id = TL.id
And KIS.dnz_chr_id = TL.dnz_chr_id
And KL.sts_code = ST.code
And ST.ste_code not in ('TERMINATED','CANCELLED','HOLD')
And KL.date_terminated Is Null
And OKL.cle_id = kl.id
And KH.template_yn = 'N'
And ((trunc(tmp.transaction_date) <= trunc(KL.end_date)And trunc(tmp.transaction_date) >= trunc(KL.start_date))
OR (trunc(tmp.transaction_date) <= trunc(kl.start_date)) )
order by tmp.old_customer_product_id, kh.creation_date; -- KI.Dnz_Chr_Id;
Select tmp.old_customer_product_id Instance_id
,tmp.termination_date
,tmp.installation_date
,tmp.transaction_date
,tmp.old_Customer_acct_id
,tmp.new_customer_acct_id
,tmp.System_id
,KI.number_of_items
,tmp.new_quantity
,tmp.new_customer_product_id
,KI.CLE_ID SubLine_id
, KI.Dnz_Chr_Id
,KH.start_date hdr_sdt
,KH.end_date hdr_edt
,KH.sts_code hdr_sts
, KL.Cle_Id
,KL.Price_negotiated
, KL.Start_date
, KL.end_date
, KL.sts_code prod_sts
, KL.Cust_acct_id
, TL.start_date Srv_sdt
, TL.end_date Srv_edt
, KH.sts_code
, KH.Contract_number
, KI.number_of_items
, TL.price_negotiated
, KL.date_terminated
, tmp.old_inventory_item_id
, KH.authoring_org_id
, KH.inv_organization_id
, KL.lse_id
, KH.scs_code
, tmp.old_customer_product_id
, KIS.object1_id1
, TL.Currency_code
, tmp.old_unit_of_measure
, KL.line_renewal_type_code
, tmp.raise_credit
, null
, Null
, Null
, Null
, Null
, Null
, Null
, Null
, tmp.return_reason_code
, tmp.order_line_id
, okl.price_uom
, okl.toplvl_uom_code
, okl.toplvl_price_qty
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_ITEMS KIS
, OKS_K_LINES_B OKL
Where tmp.upd = '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', 'SERVICE', 'SUBSCRIPTION')
And KI.Cle_id = KL.id
And TL.Id = KL.cle_id
And KIS.cle_id = TL.id
And KIS.dnz_chr_id = TL.dnz_chr_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 OKL.cle_id = KI.cle_id
And ((trunc(tmp.transaction_date) <= trunc(KL.end_date)And trunc(tmp.transaction_date) >= trunc(KL.start_date))
OR (trunc(tmp.transaction_date) <= trunc(kl.start_date)) );
Select 'Y'
From Oks_subscr_header_b oks, Oks_instance_temp tmp
Where oks.instance_id = tmp.old_customer_product_id;
select instance_number
from csi_item_instances
where instance_id = p_cp_id;
Delete from Oks_Instance_temp;
INSERT INTO oks_instance_temp
Values P_oks_txn_inst_tbl(i);
OKS_MASS_UPDATE_PVT.update_contracts (
p_api_version => 1.0,
p_init_msg_list => 'T',
p_batch_type => P_batch_type,
p_batch_id => P_batch_id,
p_new_acct_id => P_oks_txn_inst_tbl(1).New_Customer_acct_id,
p_old_acct_id => P_oks_txn_inst_tbl(1).Old_Customer_acct_id,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
'OKS_MASS_UPDATE.update_contracts(Return 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 => 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 ||')' );
'Instance quantity Update Profile=' ||fnd_profile.value('OKS_INSTANCE_QUANTITY_UPDATE'));
If NVL(fnd_profile.value('OKS_INSTANCE_QUANTITY_UPDATE'),'N') = 'Y' Then
Open get_k_for_upd_csr;
'Number of instances with transaction Update=' ||k_upd_tbl.count);
OKS_EXTWARPRGM_PVT.Create_Contract_IBupdate
(
p_kdtl_tbl => k_upd_tbl,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
'Create_contract_IBupdate(Return status = '||l_return_status ||')' );
OKS_EXTWARPRGM_PVT.Update_Contract_IDC
(
p_kdtl_tbl => k_idc_tbl,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
'Update_contract_idc(Return status = '||l_return_status ||')' );
OKS_EXTWARPRGM_PVT.Update_Contract_IBReplace
(
p_kdtl_tbl => k_rpl_tbl,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
'Update_Contract_IBReplace(Return status = '||l_return_status ||')' );
procedure delete_batch
(
P_Api_Version IN NUMBER,
P_init_msg_list IN VARCHAR2,
P_Batch_ID IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
Is
Begin
x_return_status := 'S';
DELETE FROM OKS_BATCH_RULES
WHERE batch_ID = P_Batch_ID;
SELECT 'x'
FROM oks_batch_rules
WHERE batch_id = p_batch_id
AND NVL(new_account_id, p_new_owner_id) = p_new_owner_id;
delete_batch(
P_Api_Version => p_api_version,
P_init_msg_list => p_init_msg_list,
P_Batch_ID => p_batch_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
oks_brl_pvt.insert_row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_oks_batch_rules_v_rec => l_batch_rule,
x_oks_batch_rules_v_rec => x_batch_rule);
'oks_brl_pvt.insert_row(Return status = '|| l_return_status || ')');
Select 'Y' subscr_instance
From Oks_subscr_header_b
Where instance_id = p_instance_id
And rownum < 2;
delete from oks_instance_k_dtls_temp;
INSERT INTO oks_instance_k_dtls_temp (parent_id,instance_id)
values(p_instance_id,p_instance_id);
INSERT INTO oks_instance_k_dtls_temp (parent_id,instance_id)
values(p_instance_id,l_txn_inst_tbl(1).instance_tbl(i));
Select tl.line_number|| '. '||sl.line_number line_number,
lst.name cov_level_type,
oks_ib_util_pvt.get_covlvl_name(sli.jtot_object1_code,
sli.object1_id1,
sli.object1_id2
) cov_level_name,
tlst.name service_type,
sts.meaning status,
sl.start_date,
sl.end_date,
(NVL(sl.price_negotiated,0)+NVL(ksl.tax_amount,0)) price_negotiated,
sl.currency_code ,
sl.date_terminated
from okc_k_lines_b sl,
okc_k_lines_b tl,
okc_statuses_v sts,
okc_k_items sli,
okc_line_styles_v lst,
okc_line_styles_v tlst,
oks_k_lines_b ksl
where sl.id = p_covered_line_id
and sl.sts_code=sts.code
and sl.lse_id = lst.id
and tl.lse_id = tlst.id
and sl.id = sli.cle_id
and ksl.cle_id = sl.id
and tl.id = p_service_line_id;
delete OKS_INSTANCE_CONTRACTS_TEMP;
INSERT INTO OKS_INSTANCE_CONTRACTS_TEMP
(CONTRACT_NUMBER ,
CONTRACT_NUMBER_MODIFIER ,
CHR_ID ,
LINE_NUMBER ,
COVERED_LINE_ID ,
COVERED_LEVEL_TYPE ,
COVERED_LEVEL_NAME ,
SERVICE_TYPE ,
SERVICE_NAME ,
SERVICE_LINE_ID ,
STATUS_MEANING ,
START_DATE ,
END_DATE ,
AMOUNT ,
CURRENCY_CODE ,
DATE_TERMINATED)
VALUES
( k_number_tbl(j) ,
k_modifier_tbl(j) ,
k_id_tbl(j) ,
k_line_num_tbl(j) ,
k_line_id_tbl(j) ,
k_cov_level_type_tbl(j) ,
k_cov_level_name_tbl(j) ,
k_serv_type_tbl (j) ,
k_serv_name_tbl(j) ,
k_serv_line_id(j) ,
k_line_status(j) ,
k_line_start_date(j) ,
k_line_end_date(j) ,
k_line_amount(j) ,
k_line_curr(j) ,
k_line_trm_date(j) );
Select CL.id
From OKC_K_ITEMS KI
,OKC_K_HEADERS_ALL_B KH
,OKC_K_LINES_B KL
,OKC_K_LINES_B CL
,OKC_STATUSES_b ST
Where
KI.Jtot_Object1_code = 'OKX_CUSTPROD'
AND KI.object1_id1 = to_char(p_instance_id)
And KI.dnz_chr_id = KH.ID
And KH.scs_code in ('WARRANTY')
And KI.Cle_id = CL.id
And CL.CLE_ID = KL.ID
And CL.sts_code = ST.code
And ST.ste_code not in ('TERMINATED','CANCELLED')
And CL.date_terminated Is Null
AND KL.date_terminated is null
-- AND sysdate between cl.start_date and cl.end_date
And KH.template_yn = 'N';
Select CL.id
From OKC_K_ITEMS KI
,OKC_K_HEADERS_ALL_B KH
,OKC_K_LINES_B CL
,OKC_STATUSES_b ST
Where KI.Jtot_Object1_code = 'OKX_CUSTPROD'
AND KI.object1_id1 = to_char(p_instance_id )
And KH.scs_code in ('WARRANTY','SERVICE','SUBSCRIPTION' )
And KI.Cle_id = CL.id
and cl.dnz_chr_id = kh.id
And CL.sts_code = ST.code
And ST.ste_code not in ('TERMINATED','CANCELLED')
And CL.date_terminated Is Null
And KH.template_yn = 'N'
AND (( cl.end_date >= p_transaction_date) OR
(cl.end_date < p_transaction_date
and not exists (Select 'x'
from okc_operation_instances ois,
okc_operation_lines opl,
okc_class_operations cls,
okc_subclasses_b sl
where ois.id=opl.oie_id
And cls.opn_code in ('RENEWAL','REN_CON')
And sl.code= 'SERVICE'
And sl.cls_code = cls.cls_code
and ois.cop_id = cls.id
and object_cle_id=cl.id)));
Select CL.id
From OKC_K_ITEMS KI
,OKC_K_HEADERS_ALL_B KH
,OKC_K_LINES_B CL
,OKC_STATUSES_b ST
,oks_instance_k_dtls_temp temp
Where KI.Jtot_Object1_code = 'OKX_CUSTPROD'
AND KI.object1_id1 = to_char(temp.instance_id)
And KH.scs_code in ('WARRANTY','SERVICE','SUBSCRIPTION' )
And KI.Cle_id = CL.id
and cl.dnz_chr_id = kh.id
And CL.sts_code = ST.code
And ST.ste_code not in ('TERMINATED','CANCELLED')
And CL.date_terminated Is Null
And KH.template_yn = 'N'
AND (( cl.end_date >= p_transaction_date) OR
(cl.end_date < p_transaction_date
and not exists (Select 'x'
from okc_operation_instances ois,
okc_operation_lines opl,
okc_class_operations cls,
okc_subclasses_b sl
where ois.id=opl.oie_id
And cls.opn_code in ('RENEWAL','REN_CON')
And sl.code= 'SERVICE'
And sl.cls_code = cls.cls_code
and ois.cop_id = cls.id
and object_cle_id=cl.id)));
Select CL.id
From OKC_K_ITEMS KI
,OKC_K_HEADERS_ALL_B KH
,OKC_K_LINES_B CL
,OKC_STATUSES_B ST
,csi_counter_associations CTRASC
where KI.object1_id1 = to_char(CTRASC.COunter_id)
And KI.jtot_object1_code = 'OKX_COUNTER'
And ctrasc.source_object_id = p_instance_id
And ctrasc.source_object_code = 'CP'
and kh.id=ki.dnz_chr_id
And KH.scs_code in ('WARRANTY','SERVICE','SUBSCRIPTION' )
And KH.template_yn = 'N'
And KI.Cle_id = CL.id
And CL.sts_code = ST.code
And ST.ste_code not in ('TERMINATED','CANCELLED','ENTERED')
And CL.date_terminated Is Null
AND (( cl.end_date >= p_transaction_date) OR
(cl.end_date < p_transaction_date
and not exists (select 'x'
from okc_operation_instances ois,
okc_operation_lines opl,
okc_class_operations cls,
okc_subclasses_b sl
where ois.id=opl.oie_id
And cls.opn_code in ('RENEWAL','REN_CON')
And sl.code= 'SERVICE'
And sl.cls_code = cls.cls_code
and ois.cop_id = cls.id
and object_cle_id=cl.id)));
Select CL.id
From OKC_K_ITEMS KI
,OKC_K_HEADERS_ALL_B KH
,OKC_K_LINES_B CL
,OKC_STATUSES_B ST
,csi_counter_associations CTRASC
,oks_instance_k_dtls_temp temp
where KI.object1_id1 = to_char(CTRASC.COunter_id)
And KI.jtot_object1_code = 'OKX_COUNTER'
And ctrasc.source_object_id = temp.instance_id
And ctrasc.source_object_code = 'CP'
and kh.id=ki.dnz_chr_id
And KH.scs_code in ('WARRANTY','SERVICE','SUBSCRIPTION' )
And KH.template_yn = 'N'
And KI.Cle_id = CL.id
And CL.sts_code = ST.code
And ST.ste_code not in ('TERMINATED','CANCELLED','ENTERED')
And CL.date_terminated Is Null
AND (( cl.end_date >= p_transaction_date) OR
(cl.end_date < p_transaction_date
and not exists (select 'x'
from okc_operation_instances ois,
okc_operation_lines opl,
okc_class_operations cls,
okc_subclasses_b sl
where ois.id=opl.oie_id
And cls.opn_code in ('RENEWAL','REN_CON')
And sl.code= 'SERVICE'
And sl.cls_code = cls.cls_code
and ois.cop_id = cls.id
and object_cle_id=cl.id)));
Select CL.id
From OKC_K_ITEMS KI
,OKC_K_HEADERS_ALL_B KH
,OKC_K_LINES_B KL
,OKC_K_LINES_B CL
,OKC_STATUSES_b ST
Where
KI.Jtot_Object1_code = 'OKX_CUSTPROD'
AND KI.object1_id1 = to_char(p_instance_id)
And KI.dnz_chr_id = KH.ID
And KH.scs_code in ('WARRANTY','SERVICE','SUBSCRIPTION' )
And KI.Cle_id = CL.id
And CL.CLE_ID = KL.ID
And CL.sts_code = ST.code
And ST.ste_code not in ('TERMINATED','CANCELLED')
And CL.date_terminated Is Null
AND KL.date_terminated is null
AND ((p_transaction_date between cl.start_date and cl.end_date)
OR (p_transaction_date <= cl.start_date))
And KH.template_yn = 'N';
Select cl.id
From OKC_K_ITEMS KI
,OKC_K_HEADERS_ALL_B KH
,OKC_K_LINES_B CL
Where
KI.Jtot_Object1_code = 'OKX_CUSTPROD'
AND KI.object1_id1 = to_char(p_instance_id)
And KI.dnz_chr_id = KH.ID
And KH.scs_code in ('WARRANTY','SERVICE','SUBSCRIPTION' )
And KI.Cle_id = CL.id
And CL.date_terminated Is not Null
And KH.template_yn = 'N';
SELECT sts.ste_code
FROM okc_k_headers_all_b okc,
okc_statuses_b sts
WHERE okc.id = p_chr_id
AND okc.sts_code = sts.code
AND sts.ste_code ='ENTERED';
SELECT start_date, end_date, sts_code, lse_id,
currency_code, line_renewal_type_code
FROM okc_k_lines_b
WHERE chr_id = p_chr_id
AND id = p_attach2_line_id;
SELECT 'Y'
FROM CSI_ITEM_INSTANCES CII,
CSI_I_ORG_ASSIGNMENTS CIOA,
OE_ORDER_LINES_ALL OOL,
OKC_K_PARTY_ROLES_B k_party,
okc_k_headers_all_b okh
WHERE CII.last_oe_order_line_id = OOL.line_id( + )
AND CII.instance_id = CIOA.instance_id( + )
AND CIOA.relationship_type_code ( + ) = 'SOLD_FROM'
AND k_party.chr_id=p_chr_id
AND k_party.JTOT_OBJECT1_CODE IN ('OKX_PARTY')
AND okh.id=p_chr_id
AND okh.id=k_party.dnz_chr_id
AND EXISTS
(SELECT 1
FROM HZ_CUST_ACCOUNTS CA1
WHERE CA1.party_id = k_party.object1_id1
AND CA1.status = 'A'
AND CA1.CUST_ACCOUNT_ID = CII.Owner_party_account_id
UNION ALL
SELECT 1
FROM HZ_CUST_ACCOUNTS CA2,
HZ_CUST_ACCT_RELATE_ALL REL
WHERE CA2.party_id = k_party.object1_id1
AND REL.cust_account_id = CA2.CUST_ACCOUNT_ID
AND REL.org_id = okh.AUTHORING_ORG_ID
AND REL.status = 'A'
AND CA2.status = 'A'
AND REL.related_cust_account_id = CII.Owner_party_account_id
)
AND CII.instance_id=p_cust_prod_id;
SELECT 'Y'
FROM CSI_ITEM_INSTANCES CII,
(SELECT UNIT_OF_MEASURE , UOM_CODE ,DISABLE_DATE
FROM MTL_UNITS_OF_MEASURE_TL
WHERE LANGUAGE = USERENV('LANG')) UOM
WHERE CII.Unit_Of_Measure = UOM.uom_code
AND TRUNC(NVL(UOM.disable_date, SYSDATE)) >= TRUNC(SYSDATE)
AND cii.instance_id=p_cust_prod_id;
SELECT 'Y'
FROM CSI_ITEM_INSTANCES CII
WHERE CII.instance_status_id IN
(SELECT CIS.instance_status_id
FROM CSI_INSTANCE_STATUSES CIS
WHERE CIS.service_order_allowed_flag = 'Y'
)
AND cii.instance_id=p_cust_prod_id;
SELECT 'Y'
FROM CSI_ITEM_INSTANCES CII,
MTL_SYSTEM_ITEMS_B_KFV IT,
OKC_K_HEADERS_ALL_B OKH
WHERE IT.inventory_item_id = CII.inventory_item_id
AND IT.organization_id = OKH.inv_organization_id
AND IT.serviceable_product_flag = 'Y'
AND OKH.id = p_chr_id
AND CII.instance_id = p_cust_prod_id;
SELECT priced_yn
FROM okc_line_styles_b
WHERE ID = p_lse_id;
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 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 MTL.Name,MTL.Description
FROM okx_system_items_v mtl,
okc_k_items oki
WHERE mtl.id1 = oki.object1_id1
AND mtl.organization_id = oki.object1_id2
AND oki.cle_id = p_svc_line_id;
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
);
/*Insertion of OKS_DETAILS- Start*/
l_klnv_tbl_in (1).tax_amount := 0;
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
|| ')'
);
x_msg_data := 'Cannot add the product as subline to the selected line';