The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT PSE.PARTY_SITE_ID ID1
FROM OKC_K_PARTY_ROLES_B rle,
OKC_K_HEADERS_ALL_V hdr1,
okc_k_items itm,
HZ_PARTY_SITES PSE,
HZ_LOCATIONS LCN
WHERE rle.jtot_object1_code ='OKX_PARTY'
and rle.object1_id1 =to_number(p_party_id)
and rle.rle_code in ('CUSTOMER', 'SUBSCRIBER')
AND RLE.CLE_ID IS NULL
and rle.chr_id= hdr1.id
and rle.dnz_chr_id= hdr1.id
and itm.jtot_object1_code = 'OKX_PARTYSITE'
and itm.dnz_chr_id = rle.chr_id
and PSE.PARTY_SITE_ID = itm.object1_id1
and LCN.LOCATION_ID = PSE.LOCATION_ID
and LCN.CONTENT_SOURCE_TYPE = 'USER_ENTERED'
and PSE.PARTY_SITE_NAME=p_site_name;
SELECT B.INVENTORY_ITEM_ID ID1
FROM OKC_K_PARTY_ROLES_B rle,
OKC_K_HEADERS_ALL_V hdr1,
okc_k_items itm,
MTL_SYSTEM_ITEMS_B_KFV B,
MTL_SYSTEM_ITEMS_TL T
WHERE rle.jtot_object1_code='OKX_PARTY'
and rle.object1_id1 =to_number(p_party_id)
and rle.rle_code in ('CUSTOMER', 'SUBSCRIBER')
AND RLE.CLE_ID IS NULL
and rle.chr_id=hdr1.id
and rle.dnz_chr_id=hdr1.id
and itm.dnz_chr_id = rle.chr_id
and itm.jtot_object1_code = 'OKX_COVITEM'
and B.INVENTORY_ITEM_ID = itm.object1_id1
and B.ORGANIZATION_ID = itm.object1_id2
and B.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID
and B.ORGANIZATION_ID = T.ORGANIZATION_ID
and T.LANGUAGE = userenv('LANG')
and T.DESCRIPTION=p_item_name;
SELECT B.SYSTEM_ID ID1
FROM OKC_K_PARTY_ROLES_B rle,
OKC_K_HEADERS_ALL_V hdr1,
okc_k_items itm,
CS_SYSTEMS_ALL_B B,
CS_SYSTEMS_ALL_TL T
WHERE rle.jtot_object1_code='OKX_PARTY'
and rle.object1_id1 =to_number(p_party_id)
and rle.rle_code in ('CUSTOMER', 'SUBSCRIBER')
AND RLE.CLE_ID IS NULL
and rle.chr_id=hdr1.id
and rle.dnz_chr_id=hdr1.id
and itm.jtot_object1_code = 'OKX_COVSYST'
and itm.dnz_chr_id = rle.chr_id
and B.SYSTEM_ID = itm.object1_id1
and B.SYSTEM_ID = T.SYSTEM_ID
and T.LANGUAGE = userenv('LANG')
and T.NAME=p_system_name;
SELECT CP.INSTANCE_ID ID1
FROM OKC_K_PARTY_ROLES_B rle,
OKC_K_HEADERS_ALL_B hdr1,
okc_k_items itm,
CSI_ITEM_INSTANCES CP,
MTL_SYSTEM_ITEMS_B_KFV BK
WHERE rle.jtot_object1_code = 'OKX_PARTY'
and rle.object1_id1 = p_party_id
and rle.rle_code in ('CUSTOMER', 'SUBSCRIBER')
AND RLE.CLE_ID IS NULL
and rle.dnz_chr_id = hdr1.id
and itm.dnz_chr_id = rle.chr_id
and itm.jtot_object1_code = 'OKX_CUSTPROD'
and CP.instance_id = itm.object1_id1
and BK.INVENTORY_ITEM_ID = CP.INVENTORY_ITEM_ID
and BK.ORGANIZATION_ID = CP.inv_master_organization_id
and BK.DESCRIPTION=p_product_name;
SELECT DISTINCT
PSE.PARTY_SITE_ID ID1,
'#' ID2,
PSE.PARTY_SITE_NAME NAME,
SUBSTR(arp_addr_label_pkg.format_address(NULL,LCN.ADDRESS1,LCN.ADDRESS2,LCN.ADDRESS3,LCN.ADDRESS4,
LCN.CITY,LCN.COUNTY,LCN.STATE,LCN.PROVINCE,LCN.POSTAL_CODE,NULL,LCN.COUNTRY,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'N','N',80,1,1),1,80) DESCRIPTION
FROM OKC_K_PARTY_ROLES_B rle,
OKC_K_HEADERS_ALL_V hdr1,
okc_k_items itm,
HZ_PARTY_SITES PSE,
HZ_LOCATIONS LCN
WHERE rle.jtot_object1_code = 'OKX_PARTY'
and rle.object1_id1 = to_number(p_party_id)
and rle.rle_code in ('CUSTOMER', 'SUBSCRIBER')
AND RLE.CLE_ID IS NULL
and rle.chr_id= hdr1.id
and rle.dnz_chr_id= hdr1.id
and itm.jtot_object1_code = 'OKX_PARTYSITE'
and itm.dnz_chr_id = rle.chr_id
and PSE.PARTY_SITE_ID = itm.object1_id1
and LCN.LOCATION_ID = PSE.LOCATION_ID
and LCN.CONTENT_SOURCE_TYPE = 'USER_ENTERED'
and (PSE.PARTY_SITE_NAME like p_site_name or PSE.PARTY_SITE_NAME is NULL);
SELECT DISTINCT
B.INVENTORY_ITEM_ID ID1,
B.ORGANIZATION_ID ID2,
T.DESCRIPTION NAME,
B.CONCATENATED_SEGMENTS DESCRIPTION
FROM OKC_K_PARTY_ROLES_B rle,
OKC_K_HEADERS_ALL_V hdr1,
okc_k_items itm,
MTL_SYSTEM_ITEMS_B_KFV B,
MTL_SYSTEM_ITEMS_TL T
WHERE rle.jtot_object1_code='OKX_PARTY'
and rle.object1_id1 =to_number(p_party_id)
and rle.rle_code in ('CUSTOMER', 'SUBSCRIBER')
AND RLE.CLE_ID IS NULL
and rle.chr_id=hdr1.id
and rle.dnz_chr_id=hdr1.id
and itm.dnz_chr_id = rle.chr_id
and itm.jtot_object1_code = 'OKX_COVITEM'
and B.INVENTORY_ITEM_ID = itm.object1_id1
and B.ORGANIZATION_ID = itm.object1_id2
and B.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID
and B.ORGANIZATION_ID = T.ORGANIZATION_ID
and T.LANGUAGE = userenv('LANG')
and T.DESCRIPTION like p_item_name;
SELECT DISTINCT
B.SYSTEM_ID ID1,
'#' ID2,
T.NAME NAME,
T.DESCRIPTION DESCRIPTION
FROM OKC_K_PARTY_ROLES_B rle,
OKC_K_HEADERS_ALL_V hdr1,
okc_k_items itm,
CS_SYSTEMS_ALL_B B,
CS_SYSTEMS_ALL_TL T
WHERE rle.jtot_object1_code='OKX_PARTY'
and rle.object1_id1 =to_number(p_party_id)
and rle.rle_code in ('CUSTOMER', 'SUBSCRIBER')
AND RLE.CLE_ID IS NULL
and rle.chr_id=hdr1.id
and rle.dnz_chr_id=hdr1.id
and itm.jtot_object1_code = 'OKX_COVSYST'
and itm.dnz_chr_id = rle.chr_id
and B.SYSTEM_ID = itm.object1_id1
and B.SYSTEM_ID = T.SYSTEM_ID
and T.LANGUAGE = userenv('LANG')
AND T.NAME like p_system_name;
SELECT DISTINCT
CP.INSTANCE_ID ID1,
'#' ID2,
BK.DESCRIPTION NAME,
BK.CONCATENATED_SEGMENTS || '-' || CP.instance_number DESCRIPTION
FROM OKC_K_PARTY_ROLES_B rle,
OKC_K_HEADERS_ALL_B hdr1,
OKC_K_ITEMS itm,
CSI_ITEM_INSTANCES CP,
MTL_SYSTEM_ITEMS_B_KFV BK
WHERE rle.jtot_object1_code = 'OKX_PARTY'
and rle.object1_id1 = p_party_id
and rle.rle_code in ('CUSTOMER', 'SUBSCRIBER')
AND RLE.CLE_ID IS NULL
and rle.dnz_chr_id = hdr1.id
and itm.dnz_chr_id = rle.chr_id
and itm.jtot_object1_code = 'OKX_CUSTPROD'
and CP.instance_id = itm.object1_id1
and BK.INVENTORY_ITEM_ID = CP.INVENTORY_ITEM_ID
and BK.ORGANIZATION_ID = CP.inv_master_organization_id
AND BK.DESCRIPTION like p_product_name;
SELECT party_name FROM hz_parties WHERE party_id=p_party_id;
SELECT CODE, MEANING FROM OKC_SUBCLASSES_V WHERE CLS_CODE = 'SERVICE' ORDER BY MEANING;
SELECT CODE, MEANING FROM OKC_STATUSES_V ORDER BY MEANING;
SELECT id FROM OKC_K_HEADERS_ALL_V
WHERE contract_number=p_contract_number
AND contract_number_modifier IS NULL;
SELECT id FROM OKC_K_HEADERS_ALL_V
WHERE contract_number=p_contract_number
AND contract_number_modifier=p_contract_modifier;
SELECT name
FROM oks_k_lines_v srv,
okc_k_lines_v cov
WHERE srv.cle_id = p_line_id
AND srv.coverage_id = cov.id;
SELECT
hdr.ID HeaderId,
hdr.CONTRACT_NUMBER ContractNumber,
hdr.CONTRACT_NUMBER_MODIFIER Modifier,
ver.MAJOR_VERSION||'.'||MINOR_VERSION Version,
hdr.COGNOMEN KnownAs,
hdr.SHORT_DESCRIPTION ShortDescription,
hdr.ESTIMATED_AMOUNT Amount,
hdr.CURRENCY_CODE Currency_code,
hdr.STS_CODE StatusCode,
sts.MEANING Status,
hdr.SCS_CODE CatCode,
cat.MEANING Category,
ord.ORDER_NUMBER OrderNumber,
hdr.START_DATE ContStartDate,
hdr.END_DATE ContEndDate
FROM
OKC_K_REL_OBJS_V rel,
OKX_ORDER_HEADERS_V ord,
OKC_STATUSES_V sts,
OKC_SUBCLASSES_V cat,
OKC_K_VERS_NUMBERS_V ver,
OKC_K_HEADERS_ALL_V hdr
WHERE
hdr.ID = to_number(p_contract_id_arg)
AND hdr.sts_code = sts.code
AND SYSDATE BETWEEN sts.start_date AND nvl(sts.end_date,SYSDATE)
AND hdr.scs_code = cat.code
AND hdr.id = ver.chr_id
AND hdr.id = rel.chr_id(+)
AND rel.cle_id IS NULL
AND rel.object1_id1 = ord.id1(+)
and rel.jtot_object1_code(+) = 'OKX_ORDERHEAD';
/*SELECT fndLkups.meaning
FROM fnd_lookups fndLkups
WHERE fndLkups.lookup_code = p_period_unit
and fndLkups.lookup_type = 'EGO_SRV_DURATION_PERIOD';*/
SELECT unit_of_measure_tl
FROM mtl_units_of_measure_tl
WHERE uom_code = p_period_unit
AND language = userenv('LANG');
SELECT okxCountry.Name BillToCountry
FROM HZ_CUST_SITE_USES_ALL CS,
HZ_PARTY_SITES PS,
HZ_LOCATIONS L,
HZ_CUST_ACCT_SITES_ALL CA,
OKX_CUSTOMER_ACCOUNTS_V cus_b,
OKC_K_HEADERS_ALL_B hdr,
OKX_COUNTRIES_V okxCountry
WHERE hdr.ID=p_contract_id
AND CS.SITE_USE_ID = hdr.BILL_TO_SITE_USE_ID
AND CA.CUST_ACCT_SITE_ID = CS.CUST_ACCT_SITE_ID
AND PS.PARTY_SITE_ID = CA.PARTY_SITE_ID
AND PS.LOCATION_ID = L.LOCATION_ID
AND CS.SITE_USE_CODE = 'BILL_TO'
AND cus_b.id1 = CA.CUST_ACCOUNT_ID
AND okxCountry.ID1=L.COUNTRY;
SELECT okxCountry.Name BillToCountry
FROM HZ_CUST_SITE_USES_ALL CS,
HZ_PARTY_SITES PS,
HZ_LOCATIONS L,
HZ_CUST_ACCT_SITES_ALL CA,
OKX_CUSTOMER_ACCOUNTS_V cus_b,
OKC_K_HEADERS_ALL_B hdr,
OKX_COUNTRIES_V okxCountry
WHERE
hdr.ID=p_contract_id
AND CS.SITE_USE_ID = hdr.SHIP_TO_SITE_USE_ID
AND CA.CUST_ACCT_SITE_ID = CS.CUST_ACCT_SITE_ID
AND PS.PARTY_SITE_ID = CA.PARTY_SITE_ID
AND PS.LOCATION_ID = L.LOCATION_ID
AND CS.SITE_USE_CODE = 'SHIP_TO'
AND cus_b.id1 = CA.CUST_ACCOUNT_ID
AND okxCountry.ID1=L.COUNTRY;
SELECT
hdr.ID ChrID,
cus_b.NAME BillToCustomer,
CS.LOCATION BillToSite,
L.ADDRESS1||
decode(L.ADDRESS2,NULL,NULL,' , '|| L.ADDRESS2)||
decode(L.ADDRESS3,NULL,NULL,' , '||L.ADDRESS3)||
decode(L.ADDRESS4,NULL,NULL,' , '||L.ADDRESS4)
BillToAddress,
L.CITY||
decode(L.STATE,NULL,NULL,' '||L.STATE)||
decode(L.POSTAL_CODE,NULL,NULL,' '||L.POSTAL_CODE)
BillToCityStateZip
FROM
HZ_CUST_SITE_USES_ALL CS,
HZ_PARTY_SITES PS,
HZ_LOCATIONS L,
HZ_CUST_ACCT_SITES_ALL CA,
OKX_CUSTOMER_ACCOUNTS_V cus_b,
OKC_K_HEADERS_ALL_V hdr
WHERE hdr.ID= p_contract_id
AND CS.SITE_USE_ID = hdr.BILL_TO_SITE_USE_ID
AND CS.SITE_USE_CODE = 'BILL_TO'
AND CA.CUST_ACCT_SITE_ID = CS.CUST_ACCT_SITE_ID
AND PS.PARTY_SITE_ID = CA.PARTY_SITE_ID
AND PS.LOCATION_ID = L.LOCATION_ID
AND cus_b.id1 = CA.CUST_ACCOUNT_ID;
SELECT
cus_s.NAME ShipToCustomer,
CS.LOCATION ShipToSite,
L.ADDRESS1||
decode(L.ADDRESS2,NULL,NULL,' , '||L.ADDRESS2)||
decode(L.ADDRESS3,NULL,NULL,' , '||L.ADDRESS3)||
decode(L.ADDRESS4,NULL,NULL,' , '||L.ADDRESS4)
ShipToAddress,
L.CITY||
decode(L.STATE,NULL,NULL,' '||L.STATE)||
decode(L.POSTAL_CODE,NULL,NULL,' '||L.POSTAL_CODE)
ShipToCityStateZip
FROM
OKX_CUSTOMER_ACCOUNTS_V cus_s,
HZ_CUST_SITE_USES_ALL CS,
HZ_PARTY_SITES PS,
HZ_LOCATIONS L,
HZ_CUST_ACCT_SITES_ALL CA,
OKC_K_HEADERS_ALL_V hdr
WHERE
hdr.ID=p_contract_id
AND CS.SITE_USE_ID = hdr.SHIP_TO_SITE_USE_ID
AND CS.SITE_USE_CODE = 'SHIP_TO'
AND CA.CUST_ACCT_SITE_ID = CS.CUST_ACCT_SITE_ID
AND PS.PARTY_SITE_ID = CA.PARTY_SITE_ID
AND PS.LOCATION_ID = L.LOCATION_ID
AND cus_s.ID1 = CA.CUST_ACCOUNT_ID;
SELECT bil.PROFILE_NUMBER BillProfileNumber
FROM OKS_BILLING_PROFILES_V bil, OKS_K_HEADERS_B oksHdr
WHERE oksHdr.CHR_ID = p_contract_id AND bil.ID = oksHdr.BILLING_PROFILE_ID;
SELECT
hdr.id chr_id,
pty.party_number PartyNumber,
rle.rle_code RleCode,
pty.name name,
fnd.meaning Role,
pty.gsa_indicator_flag Gsa
FROM
FND_LOOKUPS fnd,
OKX_PARTIES_V pty,
OKC_K_PARTY_ROLES_B rle,
OKC_K_HEADERS_ALL_B hdr
WHERE
hdr.id=p_contract_id
and rle.chr_id=hdr.id
and rle.dnz_chr_id=hdr.id
and rle.jtot_object1_code='OKX_PARTY'
AND pty.id1=rle.object1_id1
AND pty.id2='#'
AND fnd.lookup_type='OKC_ROLE'
AND fnd.lookup_code=rle.rle_code
UNION
SELECT
hdr.id chr_id,
pty.party_number PartyNumber,
rle.rle_code RleCode,
pty.name name,
fnd.meaning Role,
NULL Gsa
FROM
FND_LOOKUPS fnd,
OKX_PARTIES_V pty,
OKC_K_PARTY_ROLES_B rle,
OKC_K_HEADERS_ALL_B hdr
WHERE
hdr.id=p_contract_id
AND rle.chr_id=hdr.id
AND rle.dnz_chr_id=hdr.id
AND rle.jtot_object1_code='OKX_VENDOR'
AND pty.id1=rle.object1_id1
AND pty.id2='#'
AND fnd.lookup_type='OKC_ROLE'
AND fnd.lookup_code=rle.rle_code
UNION
SELECT
hdr.id chr_id,
NULL PartyNumber,
rle.rle_code RleCode,
pty.name name,
fnd.meaning Role,
NULL Gsa
FROM
FND_LOOKUPS fnd,
OKX_ORGANIZATION_DEFS_V pty,
OKC_K_PARTY_ROLES_B rle,
OKC_K_HEADERS_ALL_B hdr
WHERE
hdr.id=p_contract_id
AND rle.chr_id=hdr.id
AND rle.dnz_chr_id=hdr.id
AND rle.jtot_object1_code='OKX_OPERUNIT'
AND pty.id1=rle.object1_id1
AND pty.id2='#'
AND fnd.lookup_type='OKC_ROLE'
AND fnd.lookup_code=rle.rle_code;
select lnStyl.Name LineType
from okc_line_styles_v lnStyl
where lnStyl.id = p_lse_id;
SELECT
ln.DNZ_CHR_ID ChrId,
ln.id LineID,
ln.Start_Date lineStartDate,
ln.LINE_NUMBER lineNumber,
ln.End_Date lineEndDate,
ln.Exception_YN Exemption,
sys.concatenated_segments LineName,
sys.description LineDescription,
ln.lse_id LseID,
itm.number_of_items Quantity
FROM
MTL_SYSTEM_ITEMS_KFV sys,
okc_k_items itm,
okc_k_lines_v ln
WHERE
ln.DNZ_CHR_ID=p_contract_id
AND ln.lse_id IN (1,12,14,19)
AND itm.cle_id=ln.id
AND itm.JTOT_OBJECT1_CODE IN ('OKX_WARRANTY', 'OKX_SERVICE', 'OKX_USAGE') -- #4915688
AND sys.inventory_item_id=itm.object1_id1
AND sys.ORGANIZATION_ID=itm.object1_id2
-- AND (sys.service_item_flag='Y' OR sys.usage_item_flag='Y') #4915688
order by to_number(ln.line_number) ;
SELECT
cus.name AccountName,
cus.description AccountDesc,
cus.party_id Account
FROM
okx_customer_accounts_v cus,
okc_k_lines_v ln
WHERE
ln.DNZ_CHR_ID = p_contract_id
AND ln.id=p_line_id
AND ln.lse_id IN (1,12,14,19)
AND cus.id1=ln.CUST_ACCT_ID;
select okxCountry.Name CountryName
from OKX_COUNTRIES_V okxCountry
where okxCountry.ID1=p_country_code;
SELECT
CS.SITE_USE_CODE AddressType,
CS.LOCATION SiteName,
L.ADDRESS1 Address1,
L.ADDRESS2 Address2,
L.ADDRESS3 Address3,
L.ADDRESS4 Address4,
L.CITY || ' ' || L.COUNTY City,
L.STATE State,
L.POSTAL_CODE ZipCode,
L.COUNTRY Country
FROM
HZ_CUST_SITE_USES_ALL CS,
HZ_PARTY_SITES PS,
HZ_LOCATIONS L,
HZ_CUST_ACCT_SITES_ALL CA,
OKC_K_LINES_B okcLn
WHERE okcLn.id = p_line_id
AND CA.CUST_ACCT_SITE_ID = CS.CUST_ACCT_SITE_ID
AND PS.PARTY_SITE_ID = CA.PARTY_SITE_ID
AND PS.LOCATION_ID = L.LOCATION_ID
AND (CS.SITE_USE_ID = okcLn.BILL_TO_SITE_USE_ID
OR CS.SITE_USE_ID = okcLn.SHIP_TO_SITE_USE_ID);
SELECT
C.LAST_NAME name
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 = sys_context('OKC_CONTEXT', 'ORG_ID')
AND RSC.RESOURCE_ID = to_number(p_object1_id1)
AND '#' = p_object1_id2
UNION ALL
SELECT
EMP.FULL_NAME name
FROM JTF_RS_RESOURCE_EXTNS RSC ,
FND_USER U ,
OKX_PER_ALL_PEOPLE_V EMP
WHERE
RSC.CATEGORY = 'EMPLOYEE'
AND EMP.PERSON_ID = RSC.SOURCE_ID
AND U.USER_ID = RSC.USER_ID
AND RSC.RESOURCE_ID = to_number(p_object1_id1)
AND '#' = p_object1_id2
UNION ALL
SELECT
PARTY.PARTY_NAME name
FROM
JTF_RS_RESOURCE_EXTNS RSC ,
FND_USER U ,
HZ_PARTIES PARTY
WHERE
RSC.CATEGORY IN ( 'PARTY', 'PARTNER')
AND PARTY.PARTY_ID = RSC.SOURCE_ID
AND U.USER_ID = RSC.USER_ID
AND RSC.RESOURCE_ID = to_number(p_object1_id1)
AND '#' = p_object1_id2
UNION ALL
SELECT /*+ ordered */
TL.RESOURCE_NAME name
FROM
JTF_RS_RESOURCE_EXTNS RSC
,JTF_RS_SALESREPS SRP
,JTF_RS_RESOURCE_EXTNS_TL TL -- Bug Fix #5442182
,FND_USER U
WHERE
RSC.CATEGORY = 'OTHER'
AND SRP.RESOURCE_ID = RSC.RESOURCE_ID
AND U.USER_ID = RSC.USER_ID
AND SRP.ORG_ID = sys_context('OKC_CONTEXT', 'ORG_ID')
AND TL.RESOURCE_ID = SRP.RESOURCE_ID -- Bug Fix #5442182
AND TL.LANGUAGE = USERENV('LANG') -- Bug Fix #5442182
AND TL.CATEGORY = RSC.CATEGORY
AND RSC.RESOURCE_ID = to_number(p_object1_id1)
AND '#' = p_object1_id2;
Select name From OKX_PARTY_CONTACTS_V Where id1 = to_number(p_object1_id1) and id2 = p_object1_id2;
SELECT TL.RESOURCE_NAME
FROM JTF_RS_SALESREPS S
,JTF_RS_RESOURCE_EXTNS_TL TL -- Bug Fix #5442182
WHERE S.salesrep_id = TO_NUMBER(p_object1_id1)
AND S.org_id = p_org_id
AND S.RESOURCE_ID = TL.RESOURCE_ID
AND TL.LANGUAGE = USERENV('LANG');
Select EMAIL_ADDRESS Email
From OKS_CONTACT_POINTS_V
Where
OWNER_TABLE_ID = to_number(p_owner_table_id)
and (CONTACT_POINT_TYPE = 'EMAIL' or CONTACT_POINT_TYPE = 'Email');
Select
rol.DNZ_CHR_ID ChrID,
rol.RLE_CODE RleCode,
con.JTOT_OBJECT1_CODE OwnerTableID,
con.ROLE Role,
con.START_DATE StartDate,
con.END_DATE EndDate,
con.object1_id1 ContactID,
con.object1_id2 ID2
from
OKC_CONTACTS_V con,
OKC_K_PARTY_ROLES_B rol
where
rol.dnz_chr_id = to_number(p_contract_id_arg)
and rol.RLE_CODE = p_party_rle_code_arg
and rol.CLE_ID is NULL
and rol.JTOT_OBJECT1_CODE in ('OKX_PARTY','OKX_VENDOR','OKX_OPERUNIT')
and con.CPL_ID = rol.ID;
SELECT org_id
FROM OKC_K_HEADERS_ALL_B
WHERE id = contract_id;
SELECT email_address
FROM JTF_RS_SALESREPS
WHERE salesrep_id = p_contact_id
AND org_id = p_org_id;
SELECT email_address
FROM OKX_RESOURCES_V
WHERE id1 = p_contact_id;
SELECT email_address
FROM JTF_RS_GROUPS_B
WHERE group_id = p_contact_id;
Select
OWNER_TABLE_ID Id,
decode(
CONTACT_POINT_TYPE,
'EMAIL',
'Email',
'PHONE',
'Phone',
'FAX',
'Fax',
CONTACT_POINT_TYPE
) ContactType,
EMAIL_ADDRESS Email,
PHONE_LINE_TYPE PhoneLineType,
PHONE_COUNTRY_CODE CountryCode,
PHONE_AREA_CODE AreaCode,
PHONE_NUMBER pNumber,
PHONE_EXTENSION Extension
From
OKS_CONTACT_POINTS_V
Where OWNER_TABLE_ID = to_number(p_contact_id_arg);
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) Address
From
OKC_K_LINES_B okcLn,
OKC_K_HEADERS_ALL_B hdr,
HZ_CUST_SITE_USES_ALL CS,
HZ_PARTY_SITES PS,
HZ_LOCATIONS L,
HZ_CUST_ACCT_SITES_ALL CA,
OKX_CUSTOMER_ACCOUNTS_V cus
Where
okcLn.id = p_party_roles_cle_id
AND hdr.id = okcLn.dnz_chr_id
and cus.ID1 = okcLn.CUST_ACCT_ID
and cus.ID2 = '#'
and CA.CUST_ACCOUNT_ID = cus.PARTY_ID
AND CA.CUST_ACCT_SITE_ID = CS.CUST_ACCT_SITE_ID
AND PS.PARTY_SITE_ID = CA.PARTY_SITE_ID
AND PS.LOCATION_ID = L.LOCATION_ID
AND CS.SITE_USE_CODE = 'BILL_TO'
AND CS.ORG_ID = hdr.org_id
AND rownum < 2;
select name
from OKX_PARTIES_V
where id1 = to_number(p_itm_obj1_arg) and id2 = p_itm_obj2_arg;
select name
from OKX_CUSTOMER_ACCOUNTS_V
where id1 = to_number(p_itm_obj1_arg) and id2 = p_itm_obj2_arg;
select MTL.concatenated_segments
from MTL_SYSTEM_ITEMS_KFV MTL,
CSI_ITEM_INSTANCES CSI
where csi.instance_id = p_itm_obj1_arg
and mtl.inventory_item_id = csi.inventory_item_id
and mtl.organization_id =csi.inv_master_organization_id;
select concatenated_segments
from MTL_SYSTEM_ITEMS_KFV
where
inventory_item_id = to_number(p_itm_obj1_arg)
and organization_id = to_number(p_itm_obj2_arg)
and serviceable_product_flag='Y';
select name
from CS_SYSTEMS_ALL_TL
where system_id = to_number(p_itm_obj1_arg)
and language = userenv('lang');
select party_site_number||'-'||name
from OKX_PARTY_SITES_V
where
id1 = to_number(p_itm_obj1_arg)
and id2 = p_itm_obj2_arg;
Select
Fnd.Meaning RenewalType
From
OKC_K_LINES_B okcLn,
FND_LOOKUPS Fnd
Where
okcLn.ID = to_number(p_line_id_arg)
And Fnd.Lookup_Code = okcLn.LINE_RENEWAL_TYPE_CODE
And Fnd.Lookup_Type = 'OKC_LINE_RENEWAL_TYPE';
Select nvl(sum(line.PRICE_NEGOTIATED),0) LineAmount, line.currency_code Denomination
From OKC_K_LINES_B line
Where line.CLE_ID = to_number(p_line_id_arg)
Group By line.currency_code;
/* Select
oksLnTL.INVOICE_TEXT InvoiceText,
oksLnB.INV_PRINT_FLAG InvoicePrintFlg
From
OKS_K_LINES_TL oksLnTL, OKS_K_LINES_B oksLnB
Where
oksLnB.Cle_Id = to_number(p_line_id_arg)
And oksLnTL.ID = oksLnB.ID; */
Select
oksLnTL.INVOICE_TEXT InvoiceText
,fnd.MEANING InvoicePrintFlg
From
OKS_K_LINES_TL oksLnTL
,OKS_K_LINES_B oksLnB
,FND_LOOKUPS fnd
Where oksLnB.Cle_Id = to_number(p_line_id_arg)
And oksLnTL.ID = oksLnB.ID
And language = userenv('LANG')
And fnd.LOOKUP_TYPE = 'OKS_Y_N'
And fnd.LOOKUP_CODE = DECODE(oksLnB.INV_PRINT_FLAG,'N','N','Y') ;
Select oksLn.tax_classification_code TaxCode
,lok.meaning TaxStatus
,oksLn.EXEMPT_CERTIFICATE_NUMBER TaxExcemptCertificate
,oksLn.EXEMPT_REASON_CODE TaxExcemptCode
From OKS_K_LINES_B oksLn,
FND_LOOKUPS lok
Where oksLn.Cle_Id = to_number(p_line_id_arg)
and lok.lookup_type = 'ZX_EXEMPTION_CONTROL'
and lok.lookup_code = oksLn.tax_status;
/* Select
oksLn.TAX_STATUS TaxStatusCode,
lok.NAME TaxStatus,
to_char(oksLn.TAX_EXEMPTION_ID) TaxExcemptCode,
tcd.NAME TaxCode
From
OKX_TAX_CODES_V tcd,
OKX_LOOKUPS_V lok,
OKS_K_LINES_B oksLn
Where
oksLn.Cle_Id = to_number(p_line_id_arg)
and lok.Lookup_Code = oksLn.TAX_STATUS
and lok.Lookup_type = 'TAX_CONTROL_FLAG'
and oksLn.TAX_CODE = tcd.Id1(+); */
Select
ln.ID CoverageLineID,
ln.NAME CoverageName,
ln.ITEM_DESCRIPTION CoverageDescription,
ln.START_DATE CoverageStartDate,
ln.END_DATE CoverageEndDate,
decode(ln.lse_id,15,'Y','N') Warranty_YN
From
OKC_K_LINES_V ln
Where
ln.CLE_ID = to_number(p_line_id_arg)
and ln.LSE_ID in (2,15,20);
select
oks.coverage_ID CoverageLineID,
cov.NAME CoverageName,
cov.ITEM_DESCRIPTION CoverageDescription,
ln.START_DATE CoverageStartDate,
ln.END_DATE CoverageEndDate,
decode(cov.lse_id,15,'Y','N') Warranty_YN
From
OKC_K_LINES_b ln,
OKS_K_LINES_b oks,
OKC_K_LINES_V cov
Where
ln.ID = to_number(p_line_id_arg)
and ln.id = oks.cle_id
and cov.id = oks.coverage_id
and cov.LSE_ID in (2,15,20);
Select cvt.MEANING CoverageType
From
OKS_COV_TYPES_V cvt,
OKS_K_LINES_B oksLn
Where
oksLn.CLE_ID = p_coverage_id
and cvt.CODE = oksLn.COVERAGE_TYPE;
Select
ln_c.ID CoverageID,
to_char(oksLn.EXCEPTION_COV_ID) ExcCoverageLineID,
ln.NAME ExcCoverageName,
ln.item_description Description,
ln.START_DATE StartDate,
ln.END_DATE EndDate,
decode(ln.lse_id,15,'Y','N') Warranty_YN
From
OKC_K_LINES_V ln,
OKC_K_LINES_B ln_c,
OKS_K_LINES_B oksLn
Where
ln_c.CLE_ID = to_number(p_line_id_arg) and
ln.LSE_ID in (2,15,20)
and oksLn.CLE_ID = ln_c.ID
and ln.ID = oksLn.EXCEPTION_COV_ID;
SELECT
oksrv.Coverage_ID CoverageID,
to_char(okscov.EXCEPTION_COV_ID) ExcCoverageLineID,
expcov.NAME ExcCoverageName,
expcov.item_description Description,
expcov.START_DATE StartDate,
expcov.END_DATE EndDate,
decode(expcov.lse_id,15,'Y','N') Warranty_YN
from
okc_k_lines_b srv,
oks_k_lines_b oksrv,
oks_k_lines_b okscov,
okc_k_lines_v expcov
Where srv.id = to_number(p_line_id_arg)
AND srv.id = oksrv.cle_id
AND oksrv.coverage_id = okscov.cle_id
AND okscov.EXCEPTION_COV_ID = expcov.id;
Select
cvt.MEANING CoverageType
From
OKS_COV_TYPES_V cvt,
OKS_K_LINES_B oksLn
Where
oksLn.CLE_ID = p_excep_coverage_type_id
and cvt.CODE = oksLn.COVERAGE_TYPE;
SELECT unit_of_measure_tl
FROM mtl_units_of_measure_tl
WHERE uom_code = p_period_unit
AND language = userenv('LANG');
/*SELECT fndLkups.meaning
FROM fnd_lookups fndLkups
WHERE fndLkups.lookup_code = p_period_unit
and fndLkups.lookup_type = 'EGO_SRV_DURATION_PERIOD';*/
/* Select
ln.ID CoveredLevelId,
ln.LSE_ID lseID,
lnp.LINE_NUMBER||'.'||ln.LINE_NUMBER LineNumber,
ln.START_DATE StartDate,
ln.END_DATE EndDate,
ln.DATE_TERMINATED Terminated,
decode (
itm.JTOT_OBJECT1_CODE,
'OKX_CUSTPROD',
'Covered Product',
'OKX_COVITEM',
'Covered Item',
'OKX_PARTYSITE',
'Covered Site',
'OKX_COVSYST',
'Covered System',
'OKX_CUSTACCT',
'Covered Customer',
'OKX_PARTY',
'Covered Party',
itm.JTOT_OBJECT1_CODE
) Coverage,
itm.object1_id1 ObjId1,
itm.object1_id2 ObjId2,
itm.JTOT_OBJECT1_CODE JtotObj
From
okc_k_items itm,
okc_k_lines_b ln,
okc_k_lines_b lnp
where
lnp.ID = to_number(p_line_id_arg)
and ln.cle_id = lnp.ID
and ln.lse_id in (7,8,9,10,11,18,25,35)
and itm.cle_id = ln.id; */
Select
ln.ID CoveredLevelId,
ln.LSE_ID lseID,
lnp.LINE_NUMBER||'.'||ln.LINE_NUMBER LineNumber,
ln.START_DATE StartDate,
ln.END_DATE EndDate,
ln.DATE_TERMINATED Terminated,
style.name Coverage,
itm.object1_id1 ObjId1,
itm.object1_id2 ObjId2,
itm.JTOT_OBJECT1_CODE JtotObj
From
okc_k_items itm,
okc_k_lines_b ln,
okc_k_lines_b lnp,
okc_line_styles_tl style
where
lnp.ID = to_number(p_line_id_arg)
and ln.cle_id = lnp.ID
and ln.lse_id in (7,8,9,10,11,18,25,35)
and itm.cle_id = ln.id
and style.id = ln.lse_id
and style.language = userenv('LANG');
Select
Fnd.Meaning RenewalType
From
OKC_K_LINES_B okcLn,
FND_LOOKUPS Fnd
Where
okcLn.Id = p_covered_level_id
AND Fnd.Lookup_Code = okcLn.LINE_RENEWAL_TYPE_CODE
And Fnd.Lookup_Type = 'OKC_LINE_RENEWAL_TYPE';
/* Select
rol.CLE_ID ContractLineID,
con.role Role,
con.START_DATE StartDate,
con.END_DATE EndDate,
OKC_UTIL.GET_NAME_FROM_JTFV(con.jtot_object1_code,con.object1_id1,con.object1_id2) ContactName
from
OKC_CONTACTS_V con,
OKC_K_PARTY_ROLES_B rol,
OKC_K_LINES_B ln
where
ln.ID = to_number(p_line_id_arg)
and rol.cle_id = ln.ID
and rol.dnz_chr_id = ln.dnz_chr_id
and con.CPL_ID = rol.ID; */
SELECT FNDCONT.MEANING Role,
HZP.PARTY_NAME ContactName,
CONT.START_DATE StartDate,
CONT.END_DATE EndDate,
PR.CLE_ID ContractLineID
FROM OKC_K_LINES_B LINE,
OKC_K_PARTY_ROLES_B PR,
FND_LOOKUPS FNDCONT,
OKC_CONTACTS CONT,
HZ_PARTIES HZP,
HZ_RELATIONSHIPS HZR,
HZ_CUST_ACCOUNT_ROLES ACCROLE
WHERE LINE.ID = to_number(p_line_id_arg)
AND PR.CLE_ID = LINE.ID
AND PR.DNZ_CHR_ID = LINE.DNZ_CHR_ID
AND PR.RLE_CODE IN ('CUSTOMER','THIRD_PARTY','SUBSCRIBER')
AND CONT.CRO_CODE = FNDCONT.LOOKUP_CODE
AND CONT.JTOT_OBJECT1_CODE IN ('OKX_CONTADMN','OKX_CONTBILL','OKX_CONTSHIP','OKX_CONTTECH')
AND FNDCONT.LOOKUP_TYPE = 'OKC_CONTACT_ROLE'
AND PR.ID = CONT.CPL_ID
AND TO_NUMBER(CONT.OBJECT1_ID1) = ACCROLE.CUST_ACCOUNT_ROLE_ID
AND ACCROLE.PARTY_ID = HZR.PARTY_ID
AND ACCROLE.ROLE_TYPE = 'CONTACT'
AND HZR.RELATIONSHIP_CODE IN ('CONTACT_OF','EMPLOYEE_OF')
AND HZR.CONTENT_SOURCE_TYPE = 'USER_ENTERED'
AND HZR.SUBJECT_ID = HZP.PARTY_ID ;
Select
fnd.MEANING WarrantyInheritance
From
FND_LOOKUPS fnd,
OKS_K_LINEs_B oksLn
Where
oksLn.CLE_ID = to_number(p_coverage_ID_arg)
and fnd.LOOKUP_TYPE = 'OKSWHETYPE'
and fnd.LOOKUP_CODE = oksLn.INHERITANCE_TYPE;
Select
oksLn.PROD_UPGRADE_YN FreeUpgrade,
Fnd.MEANING TransferAllowed
From
OKS_K_LINES_B oksLn,
FND_LOOKUPS Fnd
Where
oksLn.CLE_ID = to_number(p_coverage_ID_arg)
AND Fnd.lookup_code = oksLn.TRANSFER_OPTION
AND Fnd.lookup_type='OKS_TRANSFER_OPTIONS';
Select
ln.ID BusProcessID,
bus.NAME Name
From
OKC_K_ITEMS itm,
OKX_BUS_PROCESSES_V bus,
OKC_K_LINES_V ln
Where
ln.CLE_ID = to_number(p_coverage_ID_arg)
and ln.LSE_ID in (3,16 ,21)
and ln.ID = itm.CLE_ID
and itm.JTOT_OBJECT1_CODE = 'OKX_BUSIPROC'
and bus.ID1 = itm.OBJECT1_ID1
and bus.ID2 = itm.OBJECT1_ID2;
Select
oksLn.OFFSET_DURATION OffSetDuration,
Fnd.Meaning OffsetPeriod
From
OKS_K_LINES_B oksLn,
FND_LOOKUPS Fnd
Where
-- oksLn.dnz_chr_id = to_number(p_contract_ID_arg)
oksLn.CLE_ID = business_proc_id
and Fnd.lookup_type = 'EGO_SRV_DURATION_PERIOD'
and Fnd.lookup_code = oksLn.OFFSET_PERIOD;
Select
prl.NAME PriceList
From
OKX_LIST_HEADERS_V prl,
OKC_K_LINES_v okCLn
Where
okcLn.ID = business_proc_id
and prl.ID1 = okcLn.Price_list_id;
Select
dis.NAME Discount
From
OKX_LIST_HEADERS_V dis,
OKS_K_LINES_B oksLn
Where
oksLn.CLE_ID = business_proc_id
and dis.ID1 = oksLn.DISCOUNT_LIST;
Select
tmz.NAME TimeZone
From
OKS_COVERAGE_TIMEZONES cvr,
OKX_TIMEZONES_V tmz
Where
cvr.CLE_ID = to_number(p_bus_proc_ID_arg)
and tmz.TIMEZONE_ID = cvr.TIMEZONE_ID
-- and rownum < 2;
select decode(length(CovTImes.start_hour),1,'0' ||CovTImes.start_hour,CovTImes.start_hour) ||':'||
decode(length(CovTImes.start_minute),1,CovTImes.start_minute || '0',CovTImes.start_minute) StartTime,
decode(length(CovTImes.end_hour),1,'0' ||CovTImes.end_hour,CovTImes.end_hour) ||':'||
decode(length(CovTImes.end_minute),1,CovTImes.end_minute || '0',CovTImes.end_minute) EndTime,
CovTImes.Sunday_YN Sun,
CovTImes.Monday_YN Mon,
CovTImes.Tuesday_YN Tue,
CovTImes.Wednesday_YN Wed,
CovTImes.Thursday_YN Thr,
CovTImes.Friday_YN Fri,
CovTImes.Saturday_YN Sat
from oks_coverage_timeZones CovTimeZones,
okx_timezones_v OkxTimeZones,
oks_coverage_times CovTimes
where CovTimeZones.timezone_id = OkxTimeZones.timezone_id
and CovTimeZones.cle_id = to_number(p_bus_proc_ID_arg)
and CovTImes.COV_TZE_LINE_ID=CovTimeZones.id
and CovTimeZones.default_yn = 'Y';
select IncidentSeverities.Name SeverityName,
ServiceLines.react_time_name Severity,
ServiceLines.work_thru_yn WorkThrough,
ServiceLines.react_active_yn Active,
ActTimes.uom_code UOM,
NVL2 (ActTimes.sun_duration, (floor(ActTimes.sun_duration/60))||':'||decode(LENGTH(mod (ActTimes.sun_duration, 60)), 1, '0'||(mod (ActTimes.sun_duration, 60)), (mod (ActTimes.sun_duration, 60))), null ) Sunday,
NVL2 (ActTimes.mon_duration, (floor(ActTimes.mon_duration/60))||':'||decode(LENGTH(mod (ActTimes.mon_duration, 60)), 1, '0'||(mod (ActTimes.mon_duration, 60)), (mod (ActTimes.mon_duration, 60))), null ) Monday,
NVL2 (ActTimes.tue_duration, (floor(ActTimes.tue_duration/60))||':'||decode(LENGTH(mod (ActTimes.tue_duration, 60)), 1, '0'||(mod (ActTimes.tue_duration, 60)), (mod (ActTimes.tue_duration, 60))), null ) Tuesday,
NVL2 (ActTimes.wed_duration, (floor(ActTimes.wed_duration/60))||':'||decode(LENGTH(mod (ActTimes.wed_duration, 60)), 1, '0'||(mod (ActTimes.wed_duration, 60)), (mod (ActTimes.wed_duration, 60))), null ) Wednesday,
NVL2 (ActTimes.thu_duration, (floor(ActTimes.thu_duration/60))||':'||decode(LENGTH(mod (ActTimes.thu_duration, 60)), 1, '0'||(mod (ActTimes.thu_duration, 60)), (mod (ActTimes.thu_duration, 60))), null ) Thursday,
NVL2 (ActTimes.fri_duration, (floor(ActTimes.fri_duration/60))||':'||decode(LENGTH(mod (ActTimes.fri_duration, 60)), 1, '0'||(mod (ActTimes.fri_duration, 60)), (mod (ActTimes.fri_duration, 60))), null ) Friday,
NVL2 (ActTimes.sat_duration, (floor(ActTimes.sat_duration/60))||':'||decode(LENGTH(mod (ActTimes.sat_duration, 60)), 1, '0'||(mod (ActTimes.sat_duration, 60)), (mod (ActTimes.sat_duration, 60))), null ) Saturday
from oks_action_time_types ActTimeTypes,
oks_action_times ActTimes,
okc_k_lines_v CoreLines,
oks_k_lines_v ServiceLines,
OKX_INCIDENT_SEVERITS_V IncidentSeverities
where ActTimeTypes.cle_id = CoreLines.id
and ActTimeTypes.action_type_code='RCN'
and ActTimeTypes.cle_id = ActTimes.cle_id
and ActTimeTypes.id = ActTimes.cov_action_type_id
and ServiceLines.cle_id = CoreLines.id
and IncidentSeverities.id1 = ServiceLines.incident_severity_id
and CoreLines.cle_id = to_number(p_bus_proc_ID_arg);
select IncidentSeverities.Name SeverityName,
ServiceLines.react_time_name Severity,
ServiceLines.work_thru_yn WorkThrough,
ServiceLines.react_active_yn Active,
ActTimes.uom_code UOM,
NVL2 (ActTimes.sun_duration, (floor(ActTimes.sun_duration/60))||':'||decode(LENGTH(mod (ActTimes.sun_duration, 60)), 1, '0'||(mod (ActTimes.sun_duration, 60)), (mod (ActTimes.sun_duration, 60))), null ) Sunday,
NVL2 (ActTimes.mon_duration, (floor(ActTimes.mon_duration/60))||':'||decode(LENGTH(mod (ActTimes.mon_duration, 60)), 1, '0'||(mod (ActTimes.mon_duration, 60)), (mod (ActTimes.mon_duration, 60))), null ) Monday,
NVL2 (ActTimes.tue_duration, (floor(ActTimes.tue_duration/60))||':'||decode(LENGTH(mod (ActTimes.tue_duration, 60)), 1, '0'||(mod (ActTimes.tue_duration, 60)), (mod (ActTimes.tue_duration, 60))), null ) Tuesday,
NVL2 (ActTimes.wed_duration, (floor(ActTimes.wed_duration/60))||':'||decode(LENGTH(mod (ActTimes.wed_duration, 60)), 1, '0'||(mod (ActTimes.wed_duration, 60)), (mod (ActTimes.wed_duration, 60))), null ) Wednesday,
NVL2 (ActTimes.thu_duration, (floor(ActTimes.thu_duration/60))||':'||decode(LENGTH(mod (ActTimes.thu_duration, 60)), 1, '0'||(mod (ActTimes.thu_duration, 60)), (mod (ActTimes.thu_duration, 60))), null ) Thursday,
NVL2 (ActTimes.fri_duration, (floor(ActTimes.fri_duration/60))||':'||decode(LENGTH(mod (ActTimes.fri_duration, 60)), 1, '0'||(mod (ActTimes.fri_duration, 60)), (mod (ActTimes.fri_duration, 60))), null ) Friday,
NVL2 (ActTimes.sat_duration, (floor(ActTimes.sat_duration/60))||':'||decode(LENGTH(mod (ActTimes.sat_duration, 60)), 1, '0'||(mod (ActTimes.sat_duration, 60)), (mod (ActTimes.sat_duration, 60))), null ) Saturday
from oks_action_time_types ActTimeTypes,
oks_action_times ActTimes,
okc_k_lines_v CoreLines,
oks_k_lines_v ServiceLines,
OKX_INCIDENT_SEVERITS_V IncidentSeverities
where ActTimeTypes.cle_id = CoreLines.id
and ActTimeTypes.action_type_code='RSN'
and ActTimeTypes.cle_id = ActTimes.cle_id
and ActTimeTypes.id = ActTimes.cov_action_type_id
and ServiceLines.cle_id = CoreLines.id
and IncidentSeverities.id1 = ServiceLines.incident_severity_id
and CoreLines.cle_id = to_number(p_bus_proc_ID_arg);
select
pty.cle_id businessprocessid,
pty.rle_code rlecode,
con.role resourcetype,
C.LAST_NAME name2
from
okc_contacts_v con,
okc_k_party_roles_b pty ,
JTF_RS_RESOURCE_EXTNS RSC ,
PO_VENDOR_SITES_ALL S ,
PO_VENDOR_CONTACTS C
where
pty.cle_id = to_number(p_bus_proc_ID_arg)
and pty.dnz_chr_id = to_number(p_contract_ID_arg)
and con.cpl_id = pty.id
and con.jtot_object1_code ='okx_resource'
AND con.object1_id1 = RSC.RESOURCE_ID
AND con.object1_id2 = '#'
AND 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 = sys_context('OKC_CONTEXT', 'ORG_ID')
union all
select
pty.cle_id businessprocessid,
pty.rle_code rlecode,
con.role resourcetype,
EMP.FULL_NAME name2
from
okc_contacts_v con,
okc_k_party_roles_b pty ,
JTF_RS_RESOURCE_EXTNS RSC ,
FND_USER U ,
OKX_PER_ALL_PEOPLE_V EMP
where
pty.cle_id = to_number(p_bus_proc_ID_arg)
and pty.dnz_chr_id = to_number(p_contract_ID_arg)
and con.cpl_id = pty.id
and con.jtot_object1_code ='okx_resource'
and RSC.CATEGORY = 'EMPLOYEE'
AND EMP.PERSON_ID = RSC.SOURCE_ID
AND U.USER_ID = RSC.USER_ID
AND con.object1_id1 = RSC.RESOURCE_ID
AND con.object1_id2 = '#'
union all
select
pty.cle_id businessprocessid,
pty.rle_code rlecode,
con.role resourcetype,
PARTY.PARTY_NAME name2
from
okc_contacts_v con,
okc_k_party_roles_b pty ,
JTF_RS_RESOURCE_EXTNS RSC ,
FND_USER U ,
HZ_PARTIES PARTY
where
pty.cle_id = to_number(p_bus_proc_ID_arg)
and pty.dnz_chr_id = to_number(p_contract_ID_arg)
and con.cpl_id = pty.id
and con.jtot_object1_code ='okx_resource'
AND RSC.CATEGORY IN ( 'PARTY', 'PARTNER')
AND PARTY.PARTY_ID = RSC.SOURCE_ID
AND U.USER_ID = RSC.USER_ID
AND con.object1_id1 = RSC.RESOURCE_ID
AND con.object1_id2 = '#'
union all
select
pty.cle_id businessprocessid,
pty.rle_code rlecode,
con.role resourcetype,
SRP.NAME name2
from
okc_contacts_v con,
okc_k_party_roles_b pty ,
JTF_RS_RESOURCE_EXTNS RSC ,
FND_USER U ,
JTF_RS_SALESREPS SRP
where
pty.cle_id = to_number(p_bus_proc_ID_arg)
and pty.dnz_chr_id = to_number(p_contract_ID_arg)
and con.cpl_id = pty.id
and con.jtot_object1_code ='okx_resource'
and RSC.CATEGORY = 'OTHER'
AND SRP.RESOURCE_ID = RSC.RESOURCE_ID
AND U.USER_ID = RSC.USER_ID
AND SRP.ORG_ID = sys_context('OKC_CONTEXT', 'ORG_ID')
AND con.object1_id1 = RSC.RESOURCE_ID
AND con.object1_id2 = '#'; */
SELECT PartyRoles.cle_id businessprocessid
,PartyRoles.rle_code rlecode
,RoleLookup.meaning resourcetype
,DECODE (RSC.CATEGORY
,'SUPPLIER_CONTACT', DECODE(substr(vendor_contact.PERSON_FIRST_NAME,1,15),
NULL, substr(vendor_contact.PERSON_LAST_NAME,1,15),
substr(vendor_contact.PERSON_LAST_NAME,1,15)||', '||substr(vendor_contact.PERSON_FIRST_NAME,1,15))
,'EMPLOYEE', employee.full_name
,'PARTNER', hz_party.party_name
,'PARTY', hz_party.party_name
) name2
FROM OKC_K_LINES_B okcline,
JTF_RS_RESOURCE_EXTNS RSC,
OKC_CONTACTS Contacts,
FND_LOOKUPS RoleLookup,
OKC_K_PARTY_ROLES_B PartyRoles,
AP_SUPPLIER_CONTACTS ap_supp_contact,
HZ_PARTIES vendor_contact,
PER_ALL_PEOPLE_F employee,
HZ_PARTIES hz_party
WHERE
okcline.ID = to_number(p_bus_proc_ID_arg)
AND PartyRoles.cle_id = okcline.ID
AND PartyRoles.dnz_chr_id = okcline.dnz_chr_id
AND Contacts.cpl_id = PartyRoles.id
AND Contacts.OBJECT1_ID1 = RSC.RESOURCE_ID
AND Contacts.JTOT_OBJECT1_CODE ='OKX_RESOURCE'
AND RoleLookup.lookup_type = 'OKC_CONTACT_ROLE'
AND Contacts.CRO_CODE = RoleLookup.lookup_code
AND RSC.SOURCE_ID = ap_supp_contact.vendor_contact_id (+)
AND ap_supp_contact.PER_PARTY_ID = vendor_contact.party_id (+)
AND RSC.SOURCE_ID = employee.person_id (+)
AND trunc(sysdate)between employee.effective_start_date (+) AND employee.effective_end_date (+)
AND RSC.SOURCE_ID = hz_party.PARTY_ID (+)
UNION ALL
SELECT PartyRoles.cle_id businessprocessid
,PartyRoles.rle_code rlecode
,RoleLookup.meaning resourcetype
,resource_group.group_name name2
FROM OKC_K_LINES_B okcline,
JTF_RS_GROUPS_TL resource_group,
OKC_CONTACTS Contacts,
FND_LOOKUPS RoleLookup,
OKC_K_PARTY_ROLES_B PartyRoles
WHERE okcline.ID = to_number(p_bus_proc_ID_arg)
AND PartyRoles.cle_id = okcline.ID
AND PartyRoles.dnz_chr_id = okcline.dnz_chr_id
AND Contacts.cpl_id = PartyRoles.id
AND Contacts.OBJECT1_ID1 = resource_group.group_id
AND Contacts.JTOT_OBJECT1_CODE ='OKS_RSCGROUP'
AND resource_group.language = USERENV ('LANG')
AND RoleLookup.lookup_type = 'OKC_CONTACT_ROLE'
AND Contacts.CRO_CODE = RoleLookup.lookup_code;
Select
ln.CLE_ID BusinessProceeID,
ln.ID BillTypeID,
bil.NAME,
trn.NAME||'-'||csl.meaning BillType,
csl.MEANING,
to_char(oksLn.DISCOUNT_AMOUNT) MaxAmount,
to_char(oksLn.DISCOUNT_PERCENT) Per_Covered
From
CS_LOOKUPS csl,
OKX_TRANSACTION_TYPES_V trn,
OKX_TXN_BILLING_TYPES_V bil,
OKC_K_ITEMS itm,
OKC_K_LINES_B ln,
OKS_K_LINES_B oksLn
Where
ln.CLE_ID = to_number(p_bus_proc_ID_arg)
AND ln.LSE_ID in (5,23,59) -- Fix #4238239
and itm.CLE_ID = ln.ID
and itm.jtot_object1_code = 'OKX_BILLTYPE'
and bil.ID1 = itm.OBJECT1_ID1
and bil.ID2 = itm.OBJECT1_ID2
and bil.TRANSACTION_TYPE_ID = trn.TRANSACTION_TYPE_ID
and csl.LOOKUP_CODE = bil.BILLING_TYPE
and csl.LOOKUP_TYPE = 'MTL_SERVICE_BILLABLE_FLAG'
and oksLn.Cle_Id = ln.ID;
SELECT fnd.meaning,
mtl.UNIT_OF_MEASURE,
bsh.FLAT_RATE,
bsh.PERCENT_OVER_LIST_PRICE
FROM oks_billrate_schedules bsh ,
okc_k_lines_b lines,
mtl_units_of_measure_tl mtl,
fnd_lookups fnd
WHERE bsh.cle_id = lines.id
and lines.lse_id in (6,24,60)
and lines.cle_id = p_bill_type_id
and mtl.UOM_CODE(+) = bsh.UOM
and mtl.language(+) = userenv('LANG')
and fnd.lookup_type(+) = 'OKS_BILLING_RATE'
and fnd.lookup_code(+) = bsh.bill_rate_code
and bsh.holiday_yn = 'N';
/* Select oksLn.INVOICE_TEXT InvoiceText,
oksLn.INV_PRINT_FLAG InvoicePrintFlg
From OKS_K_LINES_V oksLn
Where oksLn.Cle_Id = to_number(p_line_id_arg); */
Select
oksLnTL.INVOICE_TEXT InvoiceText
,fnd.MEANING InvoicePrintFlg
From
OKS_K_LINES_TL oksLnTL
,OKS_K_LINES_B oksLnB
,FND_LOOKUPS fnd
Where oksLnB.Cle_Id = to_number(p_line_id_arg)
And oksLnTL.ID = oksLnB.ID
And language = userenv('LANG')
And fnd.LOOKUP_TYPE = 'OKS_Y_N'
And fnd.LOOKUP_CODE = DECODE(oksLnB.INV_PRINT_FLAG,'N','N','Y') ;
Select nvl(sum(line.PRICE_NEGOTIATED),0) LineAmount
From OKC_K_LINES_B line
Where line.CLE_ID = to_number(p_line_id_arg);
Select hdr.org_id
FROM okc_k_lines_b ln,
okc_k_headers_All_b hdr
WHERE ln.id = to_number(p_line_id)
AND hdr.id = ln.dnz_chr_id;
Select lok.meaning TaxStatus
,oksLn.tax_classification_code TaxCode
From OKS_K_LINES_B oksLn,
FND_LOOKUPS lok
Where oksLn.Cle_Id = to_number(p_line_id_arg)
and lok.lookup_type = 'ZX_EXEMPTION_CONTROL'
and lok.lookup_code = oksLn.tax_status;
/* Select
lok.NAME TaxStatus,
tcd.NAME TaxCode
From
OKX_TAX_CODES_V tcd,
OKX_LOOKUPS_V lok,
OKS_K_LINES_B oksLn
Where
oksLn.Cle_Id = to_number(p_line_id_arg)
and lok.Lookup_Code = oksLn.TAX_STATUS
and lok.Lookup_type = 'TAX_CONTROL_FLAG'
and oksLn.TAX_CODE = tcd.Id1(+); */
Select
decode(to_char(oksLn.AVERAGING_INTERVAL),Null,'N','Y') AveragingAllowed,
to_char(oksLn.AVERAGING_INTERVAL) AveragingInterval,
decode(oksLn.SETTLEMENT_INTERVAL,1,'Y','N') SettlementAgainstActualUsage,
decode(
oksLn.USAGE_TYPE,
'FRT',
'Fixed Per Period',
'VRT',
'Actual Per Period',
'QTY',
'Actual By Quantity',
'NPR',
'Negotiated Price',
oksLn.USAGE_TYPE
)UsageType
From
OKX_UNITS_OF_MEASURE_V uom,
OKS_K_LINES_B oksLn
Where
oksLn.CLE_ID = to_number(p_line_id_arg)
and uom.UOM_CODE(+) = oksLn.USAGE_PERIOD;
SELECT sub_line.line_number LineNumber,
okcLnV.cognomen LineRef,
oksLnV.invoice_text InvoiceText,
rul.usage_period Period,
to_char(rul.minimum_quantity) Rate_Minimum,
to_char(rul.default_quantity) Rate_Default,
rul.amcv_flag AMCV_YN,
to_char(rul.fixed_quantity) Rate_Fixed,
to_char(rul.usage_duration) NoOf_TUOM_per,
rul.level_yn Level_YN,
mtl.Unit_of_measure UOM,
to_char(rul.base_reading) NetReading,
'#' Reading,
sub_line.price_negotiated Price,
okcItms.object1_id1 ItemObject1_Id1
FROM OKC_K_LINES_B sub_line,
OKS_K_LINES_B rul,
OKC_K_LINES_V okcLnV,
OKS_K_LINES_V oksLnV,
OKC_K_ITEMS okcItms,
MTL_UNITS_OF_MEASURE_TL mtl
WHERE sub_line.cle_id = to_number(p_line_id_arg)
AND rul.cle_id = sub_line.id
AND okcLnV.cle_id = sub_line.cle_id
AND oksLnV.cle_id = sub_line.id
AND sub_line.lse_id in (8,7,9,10,11,13,25,35)
AND okcItms.cle_id = sub_line.id
AND mtl.uom_code = okcItms.uom_code
AND mtl.language = USERENV('LANG')
AND not exists (select 1 from okc_k_rel_objs rel
WHERE rel.cle_id = sub_line.id );
/* Select
sys.NAME Name,
cgrp.SOURCE_OBJECT_CODE||';'||cp.CURRENT_SERIAL_NUMBER||';'||cp.REFERENCE_NUMBER SourceDetails,
Select
sys.NAME Name,
cgrp.SOURCE_OBJECT_CODE||';'||hdr.CONTRACT_NUMBER||';'||hdr.CONTRACT_NUMBER_MODIFIER SourceDetails,
Select
sys.concatenated_segments Name,
cca.SOURCE_OBJECT_CODE||';'||cp.SERIAL_NUMBER||';'||cp.INSTANCE_NUMBER SourceDetails,
Select
sys.concatenated_segments Name,
cca.SOURCE_OBJECT_CODE||';'||hdr.CONTRACT_NUMBER||';'||hdr.CONTRACT_NUMBER_MODIFIER SourceDetails,
/* SELECT
CG.NAME || '.' || CT.NAME name ,
CT.TYPE type ,
IT.NAME sourcedetails ,
CII.SERIAL_NUMBER serialnumber ,
CII.INSTANCE_NUMBER referencenumber,
CT.UOM_CODE uom ,
CT.NET_READING netreading ,
CT.VALUE_TIMESTAMP timestamp
FROM
OKX_COUNTER_GROUPS_V CG ,
OKX_COUNTERS_V CT ,
CSI_ITEM_INSTANCES CII ,
OKX_SYSTEM_ITEMS_V IT
WHERE
CG.COUNTER_GROUP_ID = CT.COUNTER_GROUP_ID
AND CT.USAGE_ITEM_ID IS NOT NULL
AND CG.SOURCE_OBJECT_ID = CII.INSTANCE_ID
AND CG.SOURCE_OBJECT_CODE = 'CP'
AND IT.ID1 = CII.INVENTORY_ITEM_ID
AND IT.ORGANIZATION_ID = SYS_CONTEXT('OKC_CONTEXT', 'ORGANIZATION_ID')
AND CT.COUNTER_ID = to_number(p_covered_prod_ID_arg)
UNION
SELECT
CG.NAME || '.' || CT.NAME name ,
CT.TYPE type ,
KL.NAME|| '-' ||KH.CONTRACT_NUMBER sourcedetails ,
NULL serialnumber ,
NULL referencenumber ,
CT.UOM_CODE uom ,
CT.NET_READING netreading ,
CT.VALUE_TIMESTAMP timestamp
FROM
OKX_COUNTER_GROUPS_V CG ,
OKX_COUNTERS_V CT ,
OKC_K_LINES_B KL ,
OKC_K_HEADERS_ALL_B KH
WHERE
CG.COUNTER_GROUP_ID = CT.COUNTER_GROUP_ID
AND CT.USAGE_ITEM_ID IS NOT NULL
AND CG.SOURCE_OBJECT_ID = KL.ID
AND CG.SOURCE_OBJECT_CODE = 'CONTRACT_LINE'
AND KH.ID = KL.DNZ_CHR_ID
AND CT.COUNTER_ID = to_number(p_covered_prod_ID_arg); */
SELECT CCG.NAME || '.' || CCT.NAME name ,
lkup.meaning type ,
sys.concatenated_segments sourcedetails ,
CII.SERIAL_NUMBER serialnumber ,
CII.INSTANCE_NUMBER referencenumber,
CCB.UOM_CODE uom ,
CV.NET_READING netreading ,
CV.VALUE_TIMESTAMP timestamp
FROM MTL_SYSTEM_ITEMS_B_KFV sys,
CSI_ITEM_INSTANCES CII,
CS_CSI_COUNTER_GROUPS CCG,
csi_counters_b ccb,
csi_counters_tl cct,
csi_counter_associations cca,
CSI_COUNTER_READINGS CV,
csi_lookups lkup
Where ccb.COUNTER_ID = to_number(p_covered_prod_ID_arg)
and sys.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
and sys.ORGANIZATION_ID = CII.INV_MASTER_ORGANIZATION_ID
AND ccg.template_flag = 'N'
AND ccg.counter_group_id = ccb.group_id
AND ccb.counter_id = cca.counter_id
and cca.SOURCE_OBJECT_CODE = 'CP'
and cca.source_object_id = cII.instance_id
and CV.COUNTER_ID (+) = CCB.COUNTER_ID
AND CV.COUNTER_VALUE_ID (+) = CSI_COUNTER_READINGS_PVT.get_latest_reading(CCB.COUNTER_ID)
AND ccb.counter_id = cct.counter_id
AND cct.language = USERENV('LANG')
AND lkup.lookup_type = 'CSI_COUNTER_TYPE'
AND ccb.counter_type = lkup.lookup_code
UNION
SELECT
CCG.NAME || '.' || CCT.NAME name ,
lkup.meaning type ,
SYS.CONCATENATED_SEGMENTS || '-' ||KH.CONTRACT_NUMBER || KH.CONTRACT_NUMBER_MODIFIER sourcedetails ,
NULL serialnumber ,
NULL referencenumber,
CCB.UOM_CODE uom ,
CV.NET_READING netreading ,
CV.VALUE_TIMESTAMP timestamp
FROM MTL_SYSTEM_ITEMS_B_KFV sys,
CS_CSI_COUNTER_GROUPS CCG,
csi_counters_b ccb,
csi_counters_tl cct,
csi_counter_associations cca,
CSI_COUNTER_READINGS CV,
csi_lookups lkup,
OKC_K_ITEMS KI,
OKC_K_LINES_B KL ,
OKC_K_HEADERS_ALL_B KH
WHERE sys.INVENTORY_ITEM_ID = KI.OBJECT1_ID1
and sys.ORGANIZATION_ID = KI.OBJECT1_ID2
AND ccg.template_flag = 'N'
AND ccg.counter_group_id = ccb.group_id
AND ccb.counter_id = cca.counter_id
and cca.SOURCE_OBJECT_CODE = 'CONTRACT_LINE'
and cca.source_object_id = KL.id
and CV.COUNTER_ID (+) = CCB.COUNTER_ID
AND CV.COUNTER_VALUE_ID (+) = CSI_COUNTER_READINGS_PVT.get_latest_reading(CCB.COUNTER_ID)
AND ccb.counter_id = cct.counter_id
AND cct.language = USERENV('LANG')
AND lkup.lookup_type = 'CSI_COUNTER_TYPE'
AND ccb.counter_type = lkup.lookup_code
AND KH.ID = KL.DNZ_CHR_ID
AND KL.ID = KI.CLE_ID
AND KI.JTOT_OBJECT1_CODE IN('OKX_SERVICE','OKX_WARRANTY','OKX_USAGE')
AND CCB.COUNTER_ID = to_number(p_covered_prod_ID_arg);