[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT c.last_name || ' ' || c.first_name NAME,
c.phone,
c.email_address email
FROM jtf_rs_resource_extns rsc,
ap_supplier_sites_all pvs,
hz_party_sites hps,
hz_locations hl,
po_vendor_contacts c
WHERE rsc.CATEGORY = 'SUPPLIER_CONTACT'
AND c.vendor_contact_id = rsc.source_id
AND pvs.vendor_site_id = c.vendor_site_id
AND pvs.org_id = p_org_id
AND rsc.resource_id = TO_NUMBER (p_object1_id1)
AND '#' = p_object1_id2
AND pvs.location_id = hl.location_id(+)
AND pvs.party_site_id = hps.party_site_id(+)
UNION ALL
SELECT emp.full_name NAME,
emp.work_telephone phone,
emp.email_address email
FROM jtf_rs_resource_extns rsc,
per_all_people_f emp
WHERE rsc.CATEGORY = 'EMPLOYEE'
AND emp.person_id = rsc.source_id
AND rsc.resource_id = TO_NUMBER (p_object1_id1)
AND '#' = p_object1_id2
UNION ALL
SELECT rsctl.resource_name NAME,
NULL phone,
srp.email_address email
FROM jtf_rs_resource_extns rsc,
jtf_rs_resource_extns_tl rsctl, --Bug Fix #5456468 Dneetha
jtf_rs_salesreps srp
WHERE rsc.CATEGORY = 'OTHER'
AND srp.resource_id = rsc.resource_id
AND srp.org_id = p_org_id
AND rsc.resource_id = TO_NUMBER (p_object1_id1)
AND '#' = p_object1_id2
AND rsctl.RESOURCE_ID = rsc.RESOURCE_ID -- Bug Fix #5456468 Dneetha
AND rsctl.LANGUAGE = USERENV('LANG')
AND rsctl.CATEGORY = rsc.CATEGORY
UNION ALL
SELECT party.party_name NAME,
party.primary_phone_area_code || '-' || party.primary_phone_number
phone,
party.email_address email
FROM jtf_rs_resource_extns rsc,
hz_parties party
WHERE rsc.CATEGORY IN ('PARTY', 'PARTNER')
AND party.party_id = rsc.source_id
AND rsc.resource_id = TO_NUMBER (p_object1_id1)
AND '#' = p_object1_id2;
SELECT C.LAST_NAME ||' '||c.first_name name, c.phone, c.email_address email
FROM
JTF_RS_RESOURCE_EXTNS RSC ,
PO_VENDOR_SITES_ALL S ,
PO_VENDOR_CONTACTS C
WHERE
RSC.CATEGORY = 'SUPPLIER_CONTACT'
AND C.VENDOR_CONTACT_ID = RSC.SOURCE_ID
AND S.VENDOR_SITE_ID = C.VENDOR_SITE_ID
AND S.ORG_ID = p_org_id
AND RSC.RESOURCE_ID = to_number(p_object1_id1)
AND '#' = p_object1_id2
UNION ALL
SELECT EMP.FULL_NAME name , emp.work_telephone phone ,emp.email_address email
FROM JTF_RS_RESOURCE_EXTNS RSC ,
PER_ALL_PEOPLE_F EMP
WHERE
RSC.CATEGORY = 'EMPLOYEE'
AND EMP.PERSON_ID = RSC.SOURCE_ID
AND RSC.RESOURCE_ID = to_number(p_object1_id1)
AND '#' = p_object1_id2
UNION ALL
SELECT
SRP.NAME name, null phone ,srp.email_address email
FROM
JTF_RS_RESOURCE_EXTNS RSC ,
JTF_RS_SALESREPS SRP
WHERE
RSC.CATEGORY = 'OTHER'
AND SRP.RESOURCE_ID = RSC.RESOURCE_ID
AND SRP.ORG_ID = p_org_id
AND RSC.RESOURCE_ID = to_number(p_object1_id1)
AND '#' = p_object1_id2
UNION ALL
SELECT party.party_name name, party.primary_phone_area_code ||'-'||party.primary_phone_number phone ,party.email_address email
FROM JTF_RS_RESOURCE_EXTNS RSC ,HZ_PARTIES PARTY
WHERE RSC.CATEGORY IN ( 'PARTY', 'PARTNER')
AND PARTY.PARTY_ID = RSC.SOURCE_ID
AND RSC.RESOURCE_ID = to_number(p_object1_id1)
AND '#' = p_object1_id2 ;
SELECT
RSCTL.Resource_name name, null phone ,srp.email_address email
FROM
JTF_RS_RESOURCE_EXTNS RSC ,
JTF_RS_RESOURCE_EXTNS_tl RSCTL ,-- Bug Fix #5456468 dneetha
JTF_RS_SALESREPS SRP
WHERE
RSC.CATEGORY IN ('EMPLOYEE','OTHER','PARTY','PARTNER','SUPPLIER_CONTACT')
AND SRP.RESOURCE_ID = RSC.RESOURCE_ID
AND SRP.ORG_ID = p_org_id
AND SRP.SALESREP_ID = to_number(p_object1_id1)
AND '#' = p_object1_id2
AND RSCTL.RESOURCE_ID = RSC.RESOURCE_ID -- Bug Fix #5456468 dneetha
AND RSCTL.LANGUAGE = USERENV('LANG')
AND RSCTL.CATEGORY = RSC.CATEGORY;
SELECT COUNT (*)
FROM Oks_Instance_k_dtls_temp
WHERE contract_id = p_id;
SELECT COUNT (*)
FROM Oks_Instance_k_dtls_temp
WHERE topline_id = p_id;
SELECT COUNT (*)
FROM okc_k_lines_b
WHERE dnz_chr_id = p_id
AND lse_id IN (9, 18, 25);
SELECT COUNT (*)
FROM okc_k_lines_b
WHERE cle_id = p_id AND lse_id IN (9, 18, 25);
Select lse_id
From OKc_k_lines_b
Where id = p_line_id;
Select mtl.concatenated_segments, sys.name
From mtl_system_items_kfv mtl, okc_k_items_v itm
,csi_item_instances csi, csi_systems_tl sys
where itm.cle_id = P_line_id
And itm.jtot_object1_code = 'OKX_CUSTPROD'
And csi.instance_id = itm.object1_id1
And csi.inventory_item_id = mtl.inventory_item_id
And sys.system_id(+) = csi.system_id
And rownum < 2
;
Select mtl.concatenated_segments
From mtl_system_items_kfv mtl, csi_counter_associations ctrAsc
, okc_k_items_v itm
,csi_item_instances csi
Where itm.cle_id = P_line_id
And ctrAsc.counter_id = itm.object1_id1
And csi.instance_id = ctrAsc.source_object_id
And mtl.inventory_item_id = csi.inventory_item_id
And rownum < 2
;
Select Decode(SUBSTR (hz.person_last_name || ','|| hz.person_first_name, 1,255),',',null,SUBSTR (hz.person_last_name || ', '|| hz.person_first_name, 1,255)) Billing_contact
From hz_parties hz
, okc_contacts oc2
,hz_relationships hr
Where oc2.jtot_object1_code = 'OKX_PCONTACT'
And oc2.cro_code = 'BILLING'
AND oc2.dnz_chr_id = p_contract_id
AND hr.party_id = oc2.object1_id1
AND hz.party_id = hr.subject_id;
Select v.resource_name sales_person
From jtf_rs_salesreps jtf
, jtf_rs_resource_extns_vl v
, okc_contacts oc1
,Okc_k_headers_all_b kh
Where oc1.dnz_chr_id = P_contract_id
And Kh.id = oc1.dnz_chr_id
And Kh.authoring_org_id = jtf.org_id
AND oc1.jtot_object1_code = 'OKX_SALEPERS'
AND oc1.object1_id1 = jtf.salesrep_id
And v.resource_id = jtf.resource_id;
SELECT Minimum_Accountable_Unit,
Precision,
Extended_Precision
FROM FND_CURRENCIES
WHERE Currency_Code = P_currency_code;
SELECT Kl.start_date,
Kl.end_date,
nvl(Kl.price_negotiated,0),
Kl.currency_code,
Ks.price_uom,
Kh.period_start,
Kh.period_type
FROM okc_k_lines_b Kl
,Oks_k_headers_b Kh
,oks_k_lines_b Ks
WHERE Kl.ID = p_line_id
And Ks.cle_id = Kl.Id
And Kh.chr_id = kl.dnz_chr_id
;
Select nvl(instance_amt_new,0)
From Oks_instance_history ih
, Oks_inst_hist_details id
Where ih.batch_id = p_batch_id
And id.ins_id = ih.id
And id.old_subline_id = p_line_id
And id.old_subline_id <> id.new_subline_id;
Select nvl(sum(instance_amt_new),0)
From Oks_instance_history ih
, Oks_inst_hist_details id
Where ih.batch_id = p_batch_id
And id.ins_id = ih.id
And id.old_service_line_id = p_line_id
And id.old_service_line_id <> id.new_service_line_id ;
Select sum(instance_amt_new)
From Oks_instance_history ih
, Oks_inst_hist_details id
Where ih.batch_id = p_batch_id
And id.ins_id = ih.id
And id.old_contract_id = p_line_id
And id.old_contract_id <> id.new_contract_id ;
Select NVL (SUM (bill.amount), 0)
From Oks_bill_sub_lines bill
, Oks_instance_history ih
, Oks_inst_hist_details id
, Oks_bill_cont_lines bcl
Where bill.cle_id = id.old_subline_id
And ih.batch_id = p_batch_id
And id.ins_id = ih.id
And id.old_subline_id = p_line_id
And id.old_subline_id = id.new_subline_id
And bill.bcl_id = bcl.id
And bcl.bill_action = 'TR'
And nvl(bcl.btn_id,0) <> -44;
Select nvl(sum(bill.amount),0)
From Oks_bill_sub_lines bill
, Oks_instance_history ih
, Oks_inst_hist_details id
, Okc_k_lines_b Kl
, Oks_bill_cont_lines bcl
Where ih.batch_id = p_batch_id
And id.ins_id = ih.id
And bill.cle_id = Kl.Id
And Kl.cle_Id = id.old_service_line_id
And Kl.id = id.old_subline_id
And id.old_subline_id = id.new_subline_id
And id.old_service_line_id = p_line_id
And bill.bcl_id = bcl.id
And bcl.bill_action = 'TR'
And nvl(bcl.btn_id,0) <> -44;
Select nvl(sum(bill.amount),0)
From Okc_k_lines_b kl
, Oks_bill_sub_lines bill
, Oks_instance_history ih
, Oks_inst_hist_details id
, Oks_bill_cont_lines bcl
Where ih.batch_id = p_batch_id
And id.ins_id = ih.id
And bill.cle_id = kl.Id
And Kl.dnz_chr_id = id.old_contract_id
And Kl.id = id.old_subline_id
And id.old_subline_id = id.new_subline_id
And id.old_contract_id = p_line_id
And bill.bcl_id = bcl.id
And bcl.bill_action = 'TR'
And nvl(bcl.btn_id,0) <> -44;
Select NVL (SUM (bill.amount), 0)
From Oks_bill_sub_lines bill
, Oks_instance_history ih
, Oks_inst_hist_details id
, Oks_bill_cont_lines bcl
Where bill.cle_id = id.old_subline_id
And ih.batch_id = p_batch_id
And id.ins_id = ih.id
And id.old_subline_id = p_line_id
And bill.bcl_id = bcl.id
And bcl.bill_action = 'RI';
Select nvl(sum(bill.amount),0)
From Oks_bill_sub_lines bill
, Oks_instance_history ih
, Oks_inst_hist_details id
, Okc_k_lines_b Kl
, Oks_bill_cont_lines bcl
Where ih.batch_id = p_batch_id
And id.ins_id = ih.id
And bill.cle_id = Kl.Id
And Kl.cle_Id = id.old_service_line_id
And Kl.id = id.old_subline_id
And id.old_service_line_id = p_line_id
And bill.bcl_id = bcl.id
And bcl.bill_action = 'RI';
Select nvl(sum(bill.amount),0)
From Okc_k_lines_b kl
, Oks_bill_sub_lines bill
, Oks_instance_history ih
, Oks_inst_hist_details id
, Oks_bill_cont_lines bcl
Where ih.batch_id = p_batch_id
And id.ins_id = ih.id
And bill.cle_id = kl.Id
And Kl.dnz_chr_id = id.old_contract_id
And Kl.id = id.old_subline_id
And id.old_contract_id = p_line_id
And bill.bcl_id = bcl.id
And bcl.bill_action = 'RI';
SELECT NVL (SUM (amount), 0)
FROM oks_bill_sub_lines_v
WHERE cle_id = p_cle_id;
Select authoring_org_id,
inv_organization_id
From okc_k_headers_all_b kh
, okc_k_lines_b kl
Where kl.id = p_line_id
And kh.id = kl.dnz_chr_id;
Select distinct(okc.authoring_org_id) org_id
From Okc_k_headers_all_b okc, Oks_Instance_k_dtls_temp tmp
where tmp.Contract_id = okc.Id
And tmp.Parent_id = p_batch_Id;
Select party_id
From HZ_CUST_ACCOUNTS CA
Where CA.Cust_account_id = p_new_account_Id;
Select Credit_amount
From OKS_K_DEFAULTS
Where cdt_type = 'MDT'
AND segment_id1 IS NULL
AND segment_id2 IS NULL
AND jtot_object_code IS NULL;
SELECT CA1.Account_number AccountNumber
, CA1.CUST_ACCOUNT_ID AccountId,
Party.party_name PartyName
, Party.party_id PartyId
From HZ_CUST_ACCOUNTS CA1
, HZ_PARTIES party
WHERE CA1.party_id = P_party_id
And CA1. cust_account_id = p_account_id
And CA1.party_id = party.party_id
And CA1.status = 'A'
UNION
SELECT CA2.Account_number AccountNumber
, CA2.cust_account_id AccountId
, Party1.party_name PartyName
, Party1.party_id PartyId
FROM HZ_CUST_ACCOUNTS CA2
, HZ_PARTIES party1
, HZ_CUST_ACCT_RELATE_ALL A
, HZ_CUST_ACCOUNTS B
WHERE CA2.party_id = party1.party_id
And CA2.cust_account_id = A.RELATED_CUST_ACCOUNT_ID
And B.CUST_ACCOUNT_ID = A.CUST_ACCOUNT_ID
And Ca2.cust_account_id = p_account_id
And B.party_id = p_party_id and B.status = 'A'
And A.status = 'A'
And A.org_id = p_operating_unit
And CA2.status = 'A';
Select party_id
From HZ_CUST_ACCOUNTS CA
Where CA.Cust_account_id = p_new_account_Id;
Select Cs.Site_Use_Id
, Arp_Addr_Label_Pkg.Format_Address(Null,L.Address1,L.Address2,L.Address3, L.Address4, L.City, L.County, L.State, L.Province, L.Postal_Code, Null, L.Country, Null, Null, Null, Null, Null, Null, Null, 'N', 'N', 300, 1, 1) Address
From Hz_Party_Sites Ps,Hz_Locations L
,Hz_Cust_Acct_Sites_All Ca,
Hz_Cust_Site_Uses_All Cs
Where Ps.Location_Id = L.Location_Id
And L.Content_Source_Type = 'USER_ENTERED'
And Ps.Party_Site_Id = Ca.Party_Site_Id
And Ca.Cust_Acct_Site_Id = Cs.Cust_Acct_Site_Id
And Ps.Party_Id = l_party_id
And Ca.Cust_Account_Id = l_account_id
And Cs.Site_Use_Code = l_site_use_code
And Nvl (ca.Org_Id, -99) = l_org_id
And Cs.site_use_id = l_site_use_id
And Cs.Status = 'A'
And Trunc(Sysdate) Between Nvl(Trunc(Ps.Start_Date_Active),
Trunc(Sysdate)) And Nvl(Trunc(Ps.End_Date_Active), Trunc(Sysdate)) ;
Select code,Meaning
From Okc_statuses_v
Where Ste_code = 'ENTERED'
and default_yn = 'Y';
Select 'Y'
From Oks_batch_rules
Where new_account_id = P_new_account_id
And batch_id = p_batch_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 party_id
From OKX_CUSTOMER_ACCOUNTS_V
Where id1 = p_cust_id;
Select owner_party_id
From Csi_item_instances
Where instance_id = p_instance_id;
Select 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 party_id
From OKX_CUSTOMER_ACCOUNTS_V
Where id1 = p_cust_id;
Select owner_party_id
From Csi_item_instances
Where instance_id = p_instance_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 party_id
From OKX_CUSTOMER_ACCOUNTS_V
Where id1 = p_cust_id;
Select owner_party_id
From Csi_item_instances
Where instance_id = p_instance_id;
Select Tmp.Instance_Id
, KI.CLE_ID SubLine_id
, KI.Dnz_Chr_Id
, KL.Cle_Id
, nvl(KL.price_negotiated,0)
, get_transferred_amount(KI.CLE_ID,p_transaction_date) Transfer_amount
, get_terminate_amount(KI.CLE_ID, p_transaction_date) Credit_Amount
, get_full_terminate_amount(KI.CLE_ID, p_transaction_date,Kl.end_date) Full_terminate_amount
, get_billed_amount(Ki.cle_id) Billed_Amount
, Coverage_transfer_amount(KI.CLE_ID,Ks1.transfer_option, p_new_account_id,p_transaction_date,tmp.instance_id) --coverage transfer amount
, Coverage_terminate_amount(KI.CLE_ID,Ks1.transfer_option,p_new_account_id,p_transaction_date,tmp.instance_id) Coverage_terminate_amount --coverage terminate amount
, Coverage_term_full_amount(KI.Cle_id,Ks1.transfer_option,p_new_account_id,p_transaction_date,tmp.instance_id,kl.end_date) Coverage_full_amount
, Get_date_terminated(St.ste_code,p_transaction_date,kl.start_date, kl.end_date)
From OKC_K_ITEMS KI
, OKC_K_HEADERS_all_B KH
, OKC_K_LINES_B KL
, OKC_STATUSES_B ST
, Oks_Instance_k_dtls_temp tmp
, OKS_K_LINES_B KS
, OKS_K_LINES_B KS1
,OKC_STATUSES_B HST
Where tmp.parent_id = p_batch_id
And KI.Object1_id1 = to_char(tmp.instance_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 KL.sts_code = ST.code
And ST.ste_code not in ('TERMINATED','CANCELLED','HOLD')
And KH.sts_code = HST.code
And HST.ste_code <> 'HOLD'
And KL.date_terminated Is Null
And KH.template_yn = 'N'
And KS.cle_id = KL.cle_Id
And KS1.cle_id = KS.Coverage_id
And ( (trunc(p_transaction_date) <= trunc(KL.end_date)And trunc(p_transaction_date) >= trunc(KL.start_date))
OR (trunc(p_transaction_date) <= trunc(kl.start_date))
OR ( trunc(KL.end_date) < trunc(p_transaction_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)
)
);
Select Tmp.Instance_Id
, KI.CLE_ID SubLine_id
, KI.Dnz_Chr_Id
, KL.Cle_Id
, KL.price_negotiated
, 0 Transfer_amount
, get_terminate_amount(KI.CLE_ID, p_transaction_date) Credit_Amount
, get_full_terminate_amount(KI.CLE_ID, p_transaction_date,Kl.end_date) Full_terminate_amount
, get_billed_amount(KI.CLE_ID) Billed_Amount
, 0
, 0
, 0
, Get_date_terminated(St.ste_code,p_transaction_date,kl.start_date, Kl.end_date)
From OKC_K_ITEMS KI
, OKC_K_HEADERS_ALL_B KH
, OKC_K_LINES_B KL
, OKC_STATUSES_B ST
, Oks_Instance_k_dtls_temp tmp
, OKC_STATUSES_B HST
Where tmp.parent_id = p_batch_id
And KI.Object1_id1 = to_char(tmp.instance_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 KL.sts_code = ST.code
And ST.ste_code not in ('TERMINATED','CANCELLED','HOLD')
And KH.sts_code = HST.code
And HST.ste_code <> 'HOLD'
And KL.date_terminated Is Null
And KH.template_yn = 'N'
And ( (trunc(p_transaction_date) <= trunc(KL.end_date)And trunc(p_transaction_date) >= trunc(KL.start_date))
OR (trunc(p_transaction_date) <= trunc(kl.start_date))
OR ( trunc(KL.end_date) < trunc(p_transaction_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.Instance_Id
, KI.CLE_ID SubLine_id
, KI.Dnz_Chr_Id
, KL.Cle_Id
, nvl(KL.price_negotiated,0)
, 0 Transfer_amount
, get_terminate_amount(KI.CLE_ID, p_transaction_date) Credit_Amount
, get_full_terminate_amount(KI.CLE_ID, p_transaction_date,Kl.end_date) Full_terminate_amount
, get_billed_amount(KI.CLE_ID) Billed_Amount
, 0
, 0
, 0
, Get_date_terminated(St.ste_code,p_transaction_date,kl.start_date, Kl.end_date)
From OKC_K_ITEMS KI
,OKC_K_HEADERS_ALL_B KH
,OKC_K_LINES_B KL
,OKC_STATUSES_B ST
,csi_counter_associations ctrAsc
, Oks_Instance_k_dtls_temp tmp
, OKC_STATUSES_B HST
Where tmp.parent_id = p_batch_id
And KI.object1_id1 = to_char(ctrAsc.Counter_id)
And ctrAsc.source_object_id = tmp.instance_id
And 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 KL.sts_code = ST.code
And ST.ste_code not in ('TERMINATED','CANCELLED','HOLD')
And KH.sts_code = HST.code
And HST.ste_code <> 'HOLD'
And KL.date_terminated Is Null
And KH.template_yn = 'N'
And ( (trunc(p_transaction_date) <= trunc(KL.end_date)And trunc(p_transaction_date) >= trunc(KL.start_date))
OR (trunc(p_transaction_date) <= trunc(kl.start_date))
OR ( trunc(KL.end_date) < trunc(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=kl.id)
)
)
;
Select Tmp.Instance_Id
, KI.CLE_ID SubLine_id
, KI.Dnz_Chr_Id
, KL.Cle_Id
, nvl(KL.price_negotiated,0)
, 0 Transfer_amount
, 0 Credit_Amount
, 0 Full_terminate_amount
, 0 Billed_Amount
, 0
, 0
, 0
, null
From OKC_K_ITEMS_V KI
, OKC_K_HEADERS_ALL_B KH
, OKC_K_LINES_B KL
, OKC_STATUSES_B ST
, OKS_K_LINES_B KS
, Oks_k_lines_b KS1
, Oks_Instance_k_dtls_temp tmp
Where tmp.parent_id = p_batch_id
And KI.Object1_id1 = to_char(tmp.instance_id)
And KI.Jtot_Object1_code = 'OKX_CUSTPROD'
And KI.dnz_chr_id = KH.ID
And KH.scs_code ='WARRANTY'
And KI.Cle_id = KL.id
And KL.sts_code = ST.code
AND KL.CLE_ID = KS.CLE_ID
AND KS.Coverage_ID = KS1.Cle_id
And ST.ste_code not in ('TERMINATED','CANCELLED')
And KL.date_terminated Is Null
And KH.template_yn = 'N'
AND KL.lse_id = 18
AND nvl(ks1.sync_date_install,'N') = 'Y';
Delete Oks_Instance_k_dtls_temp ;
'Insert inot temp ');
INSERT INTO Oks_Instance_k_dtls_temp
(
parent_id ,
subline_id,
topline_id ,
contract_id,
billed_amount ,
transfer_amount ,
credit_amount ,
amount ,
new_subline_id ,
new_serviceline_id ,
new_contract_id ,
instance_id ,
cov_trf_amt ,
cov_trm_amount ,
cov_billed_amount ,
new_start_date ,
new_end_date ,
date_terminated ,
full_term_amount
)
Values (
p_batch_id,null,null,null,null,null,null,null,null,null,null,l_item_instance_tbl(i).instance_tbl(j) ,NULL,NULL,NULL,NULL,NULL,NULL ,Null);
Delete Oks_Instance_k_dtls_temp ;
INSERT INTO Oks_Instance_k_dtls_temp
(
parent_id ,
subline_id,
topline_id ,
contract_id,
billed_amount ,
transfer_amount ,
credit_amount ,
amount ,
new_subline_id ,
new_serviceline_id ,
new_contract_id ,
instance_id ,
cov_trf_amt ,
cov_trm_amount ,
cov_billed_amount ,
new_start_date ,
new_end_date ,
date_terminated ,
full_term_amount
)
Values (
p_batch_id,null,null,null,null,null,null,null,null,null,null,l_item_instance_tbl(i).instance_tbl(j) ,NULL,NULL,NULL,NULL,NULL,NULL,null );
Delete Oks_Instance_k_dtls_temp ;
INSERT INTO Oks_Instance_k_dtls_temp
(
parent_id ,
subline_id,
topline_id ,
contract_id,
billed_amount ,
transfer_amount ,
credit_amount ,
amount ,
new_subline_id ,
new_serviceline_id ,
new_contract_id ,
instance_id ,
cov_trf_amt ,
cov_trm_amount ,
cov_billed_amount ,
new_start_date ,
new_end_date ,
date_terminated ,
full_term_amount
)
Values (
p_batch_id,null,null,null,null,null,null,null,null,null,null,l_item_instance_tbl(i).instance_tbl(j) ,NULL,NULL,NULL,NULL,NULL,NULL,null );
Delete Oks_Instance_k_dtls_temp;-- where parent_id = p_batch_id;
INSERT INTO Oks_Instance_k_dtls_temp
(
parent_id ,
subline_id,
topline_id ,
contract_id,
billed_amount ,
transfer_amount ,
credit_amount ,
amount ,
new_subline_id ,
new_serviceline_id ,
new_contract_id ,
instance_id ,
cov_trf_amt ,
cov_trm_amount ,
cov_billed_amount ,
new_start_date ,
new_end_date ,
date_terminated ,
full_term_amount
)
Values
( p_Batch_id
, SubLine_tbl(i)
, Line_tbl(i)
, COntract_tbl(i)
, billed_amount_tbl(i)
, Transfer_amount_tbl(i)
, Credit_amount_tbl(i)
, Amount_tbl(i)
, Null
, Null
, Null
, Instance_tbl(i)
, Coverage_trf_amount_tbl(i)
, Coverage_credit_amount_tbl(i)
, Coverage_credit_fullamt_tbl(i)
, NULL
, Null
,Date_terminated_tbl(i)
, full_credit_amt_tbl(i)
);
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 max(date_terminated)
From OKC_K_LINES_B
Where cle_id = p_line_id
and lse_id in (8,7,9,10,11,18,13,25,35)
having Count(*) = count(decode(date_terminated, null, null, 'x')) ;
Select max(date_terminated)
From OKC_K_LINES_B
Where dnz_chr_id = p_line_id
and lse_id in (1,12,14,19)
having Count(*) = count(decode(date_terminated, null, null, 'x'));
Select max(date_cancelled)
From OKC_K_LINES_B
Where cle_id = p_line_id
and lse_id in (8,7,9,10,11,18,13,25,35)
having count(*) = Count(decode(term_cancel_source,'IBTRANSFER','x','IBTERMINATE','x','IBRETURN','x',null))
and Count(*) = count(decode(date_cancelled, null, null, 'x')) ;
Select max(date_cancelled)
From OKC_K_LINES_B
Where dnz_chr_id = p_line_id
and lse_id in (1,12,14,19)
having count(*) = Count(decode(term_cancel_source,'IBTRANSFER','x','IBTERMINATE','x','IBRETURN','x',null))
and Count(*) = count(decode(date_cancelled, null, null, 'x'));
SELECT credit_amount
FROM oks_k_defaults
WHERE ( segment_id1 = p_party_id
AND segment_id2 = '#'
AND jtot_object_code = 'OKX_PARTY'
AND cdt_type = 'SDT'
AND p_transaction_date BETWEEN start_date
AND NVL (end_date,
p_transaction_date)
)
OR ( segment_id1 = p_org_id
AND segment_id2 = '#'
AND jtot_object_code = 'OKX_OPERUNIT'
AND cdt_type = 'SDT'
AND p_transaction_date BETWEEN start_date
AND NVL (end_date,
p_transaction_date)
)
order by jtot_object_code desc;
Select credit_option
From oks_k_defaults
Where cdt_type = 'MDT'
AND segment_id1 IS NULL
AND segment_id2 IS NULL
AND jtot_object_code IS NULL
;
SELECT prl.object1_id1 party_id,
kh.authoring_org_id org_id,
kh.inv_organization_id
FROM okc_k_party_roles_b prl,
okc_k_headers_all_b kh,
okc_k_lines_b ksl
WHERE ksl.ID = p_line_id
AND ksl.dnz_chr_id = kh.ID
AND prl.dnz_chr_id = kh.ID
AND prl.chr_id IS NOT NULL
AND prl.cle_id IS NULL
AND prl.rle_code IN ('CUSTOMER', 'SUBSCRIBER')
AND prl.jtot_object1_code = 'OKX_PARTY';
SELECT csi.OWNER_PARTY_ID old_party_id,
tls1.TRANSFER_OPTION transfer_option
FROM okc_k_lines_b sl,
oks_k_lines_b tls,
oks_k_lines_b tls1,
okc_k_items im,
csi_item_instances csi
WHERE sl.id = p_line_id
AND sl.id = im.cle_id
AND im.jtot_object1_code = 'OKX_CUSTPROD'
AND im.object1_id1 = csi.instance_id
AND sl.cle_id = tls.cle_id
AND tls.coverage_id = tls1.CLE_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 party_id
FROM okx_customer_accounts_v
WHERE id1 = p_cust_id;
Select max(Kl.date_renewed)
From OKC_K_LINES_B Kl, OKc_k_lines_b Kl1
Where Kl1.id = p_line_id
and Kl.cle_id = Kl1.cle_id
And Kl.lse_id in (8,7,9,10,11,13,35, 18, 25)
having Count(*) = count(decode(kl.date_renewed, null, null, 'x')) ;
Select max(Kl.date_renewed)
From OKC_K_LINES_B Kl, Okc_k_lines_b Kl1
Where Kl1.Id = p_line_id
And Kl.dnz_chr_id = Kl1.dnz_chr_id
and Kl.lse_id in (1,12,19)
having Count(*) = count(decode(Kl.date_renewed, null, null, 'x')) ;
SELECT max(date_terminated)
FROM oks_Instance_k_dtls_temp temp
where topline_id = p_Line_id
having count(*) = (select count(*) from Okc_k_lines_b
WHERE cle_id = p_line_id AND lse_id IN (8,7,9,10,11,13,35, 18, 25)
);
SELECT max(date_terminated)
FROM oks_Instance_k_dtls_temp temp
where topline_id = p_Line_id;
Select max(line.date_terminated)
From OKC_K_LINES_B line
Where line.cle_id= p_line_id
and line.lse_id in (8,7,9,10,11,13,18,25,35)
And line.id not in (select subline_id from oks_instance_k_dtls_temp where topline_id = p_line_id)
having Count(line.id) = count(decode(line.date_terminated, null, null, 'x'));
SELECT max(date_terminated)
FROM oks_Instance_k_dtls_temp
where contract_id = p_Line_id
having count(*) = (select count(*) from Okc_k_lines_b
WHERE dnz_chr_id= p_line_id AND lse_id IN (8,7,9,10,11,13,35, 18, 25)
);
SELECT max(date_terminated)
FROM oks_Instance_k_dtls_temp temp
where Contract_id = p_Line_id;
Select max(line.date_terminated)
From OKC_K_LINES_B line
Where line.dnz_chr_id= p_line_id
and line.lse_id in (8,7,9,10,11,13,18,25,35)
And line.id not in (select subline_id from oks_instance_k_dtls_temp where Contract_id = p_line_id)
having Count(*) = count(decode(line.date_terminated, null, null, 'x'));
Select arp_addr_label_pkg.format_address (NULL,l.address1,l.address2,
l.address3,l.address4,l.city,l.county,
l.state,l.province,l.postal_code,
NULL,l.country,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,'N','N',300,1,1)
From hz_cust_site_uses_all cs
,hz_party_sites ps
,hz_locations l
Where cs.site_use_id (+) = p_site_use_id
AND cs.cust_acct_site_id = ps.party_site_id(+)
AND ps.location_id = l.location_id(+);
Select fnd_flex_server.get_kfv_concat_segs_by_rowid('COMPACT', 401, 'SERV', 101, mtl.rowid), description
From Mtl_system_items_b mtl
, okc_k_items itm
Where mtl.inventory_item_id = itm.object1_id1
and mtl.organization_id = itm.object1_id2
And itm.cle_id = p_line_id;
SELECT mtl.concatenated_segments
From mtl_system_items_kfv mtl,
okc_k_items_v itm
,csi_item_instances csi
where itm.object1_id1 = p_object1_id1
And itm.jtot_object1_code = 'OKX_CUSTPROD'
And csi.instance_id = itm.object1_id1
And csi.inventory_item_id = mtl.inventory_item_id
And rownum < 2;
SELECT DECODE(site.party_site_name
,NULL,site.party_site_number
,site.party_site_number || '-' ||
site.party_site_name ) NAME
FROM hz_party_sites site
WHERE site.party_site_id = p_object1_id1;