[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT SVL.Start_Date
,Get_End_Date_Time(SVL.End_Date) End_Date
,Get_End_Date_Time(SVL.Date_Terminated) Date_Terminated
,KSL.Coverage_Id
,KSL.Standard_Cov_YN
FROM Okc_K_Lines_B SVL
,Oks_K_Lines_B KSL
WHERE SVL.Id = Cx_SVL_Id
AND SVL.Id = KSL.Cle_ID
AND SVL.Lse_Id IN (G_LINE_STYLE_SERVICE
,G_LINE_STYLE_WARRANTY
,G_LINE_STYLE_EXT_WARRANTY) ;
SELECT CVL.Start_Date
,Get_End_Date_Time(CVL.End_Date) End_Date
,Get_End_Date_Time(CVL.Date_Terminated) Date_Terminated
FROM Okc_K_Lines_B CVL
WHERE CVL.Id = Cx_CVL_Id
AND Lse_Id IN (G_LINE_STYLE_SRV_COVERAGE
,G_LINE_STYLE_WAR_COVERAGE
,G_LINE_STYLE_EWT_COVERAGE);
SELECT BPL.Offset_Duration
,BPL.Offset_period
FROM Oks_K_Lines_B BPL
WHERE BPL.Cle_Id = Cx_BPL_Id;
SELECT BPL.Start_Date
,Get_End_Date_Time(BPL.End_Date) End_Date
,Get_End_Date_Time(BPL.Date_Terminated) Date_Terminated
FROM Okc_K_Lines_B BPL
WHERE BPL.Id = Cx_BPL_Id
AND BPL.Lse_Id IN (3,16,21);
SELECT BPL.Id
,BPL.Start_Date
,Get_End_Date_Time(BPL.End_Date) End_Date
,Get_End_Date_Time(BPL.Date_Terminated) Date_Terminated
FROM Okc_K_Lines_B BPL,
Okc_K_Lines_B CVL
WHERE CVL.id = Cx_CVL_Id
AND BPL.Cle_Id = CVL.Id
AND EXISTS ( SELECT '*'
FROM Okc_K_Items ITM
WHERE ITM.Cle_id = BPL.Id
AND ITM.Object1_Id1 = Cx_BP_Id -- TO_CHAR(Cx_BP_Id) commented due to bug 3202650
AND ITM.Object1_Id2 = '#'
AND ITM.Jtot_Object1_Code = Cx_BP_ObjCode)
AND Cx_Req_Date BETWEEN nvl(BPL.Start_Date,Cx_Req_Date) AND nvl(BPL.End_Date,Cx_Req_Date); --- ansraj 13795417
SELECT OCT.Id
,OCT.TimeZone_Id BP_Tzone_Id
FROM Oks_Coverage_Timezones OCT
WHERE OCT.CLE_ID = Cx_BPL_Id
AND OCT.Default_YN = 'Y';
SELECT RTL.Id
,RTL.Start_Date
,Get_End_Date_Time(RTL.End_Date) End_Date
,Get_End_Date_Time(RTL.Date_Terminated) Date_Terminated
,OKSRTL.Work_Thru_YN Work_Through_YN
FROM Okc_K_Lines_B RTL
,Okc_K_Lines_B BPL
,oks_k_lines_b OKSRTL
,oks_action_time_types ACT
,oks_action_times ACM
WHERE BPL.Id = Cx_BPL_Id
AND RTL.Cle_Id = BPL.Id
AND OKSRTL.Cle_Id = RTL.Id
AND RTL.lse_id in (4,17,22)
AND OKSRTL.incident_severity_id = Cx_Severity_Id
AND OKSRTL.react_active_yn = Cx_Active_YN
AND ACT.cle_id = RTL.id
AND ACT.action_type_code = Cx_TimeType_Category
and ACT.id = ACM.cov_action_type_id
and (ACM.sun_duration is not null or
ACM.mon_duration is not null or
ACM.tue_duration is not null or
ACM.wed_duration is not null or
ACM.thu_duration is not null or
ACM.fri_duration is not null or
ACM.sat_duration is not null);
SELECT MAX(Start_Date), MIN(End_Date)
FROM ( SELECT Cd_SVL_Start Start_Date, Cd_SVL_End End_Date FROM DUAL
UNION
SELECT Cd_CVL_Start Start_Date, Cd_CVL_End End_Date FROM DUAL
UNION
SELECT Cd_BPL_Start Start_Date, Cd_BPL_End End_Date FROM DUAL
UNION
SELECT Cd_RTL_Start Start_Date, Cd_RTL_End End_Date FROM DUAL);
SELECT LPAD(TO_CHAR(CVT.START_HOUR), 2, '0')||LPAD(TO_CHAR(CVT.START_MINUTE), 2, '0') BP_Cover_From
,LPAD(TO_CHAR(CVT.END_HOUR), 2, '0')||LPAD(TO_CHAR(CVT.END_MINUTE), 2, '0') BP_Cover_To
,((CVT.START_HOUR)*60+(CVT.START_MINUTE)) BP_Cover_From_num
FROM Oks_Coverage_Times CVT
WHERE CVT.cov_tze_line_id = Cx_BP_CVTLine_Id
And (
decode(Cx_sunday_flag,'Y',CVT.sunday_yn,'N','#') = decode(Cx_sunday_flag,'Y','Y','N','#')
and decode(Cx_monday_flag,'Y', CVT.monday_yn,'N','#') = decode(Cx_monday_flag,'Y','Y','N','#')
and decode(Cx_tuesday_flag,'Y', CVT.tuesday_yn,'N','#') = decode(Cx_tuesday_flag,'Y','Y','N','#')
and decode(Cx_wednesday_flag,'Y', CVT.wednesday_yn,'N','#') = decode(Cx_wednesday_flag,'Y','Y','N','#')
and decode(Cx_thursday_flag,'Y', CVT.thursday_yn,'N','#') = decode(Cx_thursday_flag,'Y','Y','N','#')
and decode(Cx_friday_flag,'Y', CVT.friday_yn,'N','#') = decode(Cx_friday_flag,'Y','Y','N','#')
and decode(Cx_saturday_flag,'Y', CVT.saturday_yn,'N','#') = decode(Cx_saturday_flag,'Y','Y','N','#')
)
order by BP_Cover_From_num;
SELECT OAT.SUN_DURATION SUN_DURATION,
OAT.MON_DURATION MON_DURATION,
OAT.TUE_DURATION TUE_DURATION,
OAT.WED_DURATION WED_DURATION,
OAT.THU_DURATION THU_DURATION,
OAT.FRI_DURATION FRI_DURATION,
OAT.SAT_DURATION SAT_DURATION,
OAT.UOM_CODE UOM_CODE
FROM Oks_action_time_types OATT
,Oks_action_times OAT
WHERE OATT.Cle_Id = Cx_RTLLine_Id
AND OATT.action_type_code = Cx_TimeType_Category
AND OAT.cov_action_type_id = OATT.Id;
SELECT TO_CHAR(NEXT_DAY(Cd_Request_Date,Get_NLS_Day_Of_Week(Cv_USA_DY_Char)),'DY')React_Day_Char
,TO_CHAR(NEXT_DAY(Cd_Request_Date,Get_NLS_Day_Of_Week(Cv_USA_DY_Char)),'D') React_Day_Num
, DECODE(SIGN((TO_NUMBER(TO_CHAR(NEXT_DAY(Cd_Request_Date,Get_NLS_Day_Of_Week(Cv_USA_DY_Char)),'D')) - TO_NUMBER(TO_CHAR(Cd_Request_Date,'D')))+1),1
,TO_NUMBER(TO_CHAR(NEXT_DAY(Cd_Request_Date,Get_NLS_Day_Of_Week(Cv_USA_DY_Char)),'D')) - TO_NUMBER(TO_CHAR(Cd_Request_Date,'D'))
,TO_NUMBER(TO_CHAR(NEXT_DAY(Cd_Request_Date,Get_NLS_Day_Of_Week(Cv_USA_DY_Char)),'D')) - TO_NUMBER(TO_CHAR(Cd_Request_Date,'D')) + 7) ReqDay_Relative
FROM DUAL; */
SELECT TO_CHAR(NEXT_DAY(Cd_Request_Date,Cv_USA_DY_Char),'DY')React_Day_Char
,TO_CHAR(NEXT_DAY(Cd_Request_Date,Cv_USA_DY_Char),'D') React_Day_Num
, DECODE(SIGN((TO_NUMBER(TO_CHAR(NEXT_DAY(Cd_Request_Date,Cv_USA_DY_Char),'D')) - TO_NUMBER(TO_CHAR(Cd_Request_Date,'D')))+1),1
,TO_NUMBER(TO_CHAR(NEXT_DAY(Cd_Request_Date,Cv_USA_DY_Char),'D')) - TO_NUMBER(TO_CHAR(Cd_Request_Date,'D'))
,TO_NUMBER(TO_CHAR(NEXT_DAY(Cd_Request_Date,Cv_USA_DY_Char),'D')) - TO_NUMBER(TO_CHAR(Cd_Request_Date,'D')) + 7) ReqDay_Relative
FROM DUAL;
Lx_Reaction_Attribs.DELETE;
SELECT TCU.TCE_CODE Tce_Code
FROM Okx_Units_Of_Measure_V UOM
,Okc_Time_Code_Units_V TCU
WHERE UOM.UOM_CODE = Lx_React_UOM
AND TCU.UOM_CODE = UOM.UOM_CODE
AND TCU.quantity = 1
AND TCU.tce_code = 'MINUTE';
select oct.id,
oct.timezone_id,
oct.default_yn,
okslb.APPLY_DEFAULT_TIMEZONE
from okc_k_lines_b okclb,
oks_k_lines_b okslb,
oks_coverage_timezones oct
where okclb.id = P_BPL_Id
and okslb.cle_id = okclb.id
and oct.cle_id = okclb.id;
select oct.id,
oct.timezone_id,
oct.default_yn,
okslb.APPLY_DEFAULT_TIMEZONE
from okc_k_lines_b okclb,
oks_k_lines_b okslb,
oks_coverage_timezones oct
where okclb.id = P_BPL_Id
and okslb.cle_id = okclb.id
and oct.cle_id = okclb.id;
SELECT AUTHORING_ORG_ID
,HDR.ID
,HDR.CONTRACT_NUMBER
,HDR.CONTRACT_NUMBER_MODIFIER
,HDT.SHORT_DESCRIPTION
,HDR.STS_CODE
,HDR.CHR_TYPE
,HDR.TEMPLATE_YN
,HDR.TEMPLATE_USED
,HDR.START_DATE
,Get_End_Date_Time(HDR.END_DATE) End_Date
,HDR.CHR_ID_AWARD
,HDR.CUST_PO_NUMBER
,HDR.AUTO_RENEW_DAYS
,HDR.QCL_ID
,HDR.ESTIMATED_AMOUNT -- 11.5.10 changes
,PTY.OBJECT1_ID1 PARTY_ID
,HDR.bill_to_site_use_id bill_to_site_use_id -- 11.5.10 changes
,HDR.ship_to_site_use_id ship_to_site_use_id -- 11.5.10 changes
,HDR.currency_code currency_code -- 11.5.10 changes
,OKSHDR.acct_rule_id acct_rule_id -- 11.5.10 changes
,HDR.inv_rule_id inv_rule_id -- 11.5.10 changes
,HDR.payment_term_id payment_term_id -- 11.5.10 changes
,OKSHDR.billing_profile_id billing_profile_id -- 11.5.10 changes
,OKSHDR.tax_exemption_id tax_exemption_id -- 11.5.10 changes
,OKSHDR.tax_status tax_status -- 11.5.10 changes
,HDR.conversion_type conversion_type -- 11.5.10 changes
FROM OKC_K_PARTY_ROLES_B PTY
,OKC_K_HEADERS_TL HDT
,OKC_K_HEADERS_ALL_B HDR --,OKC_K_HEADERS_B HDR -- Modified for 12.0 MOAC project (JVARGHES)
,OKS_K_HEADERS_B OKSHDR -- 11.5.10 changes
WHERE HDR.ID = NVL(Cx_Chr_Id,HDR.ID)
AND HDR.END_DATE = NVL(Cd_Chr_EndDate,HDR.END_DATE)
AND HDR.STS_CODE = NVL(Cx_Chr_Sts_Code,HDR.STS_CODE)
AND HDR.CHR_TYPE = NVL(Cx_Chr_Type,HDR.CHR_TYPE)
AND HDR.START_DATE IS NOT NULL
AND HDR.END_DATE IS NOT NULL
AND HDR.TEMPLATE_YN = G_NO
AND HDT.ID = HDR.ID
AND HDT.LANGUAGE = USERENV('LANG')
AND PTY.CHR_ID = HDR.ID
AND PTY.OBJECT1_ID1 = NVL(Cx_Chr_PartyId, PTY.OBJECT1_ID1)
AND HDR.ID = OKSHDR.CHR_ID
AND PTY.JTOT_OBJECT1_CODE = G_JTOT_OBJ_PARTY;
SELECT LIN.ID
,LIN.CLE_ID
,LIN.DNZ_CHR_ID
,LIN.STS_CODE
,LIN.START_DATE
,Get_End_Date_Time(LIN.END_DATE) End_Date
,Get_End_Date_Time(LIN.DATE_TERMINATED) Date_Terminated
,LIN.PRICE_NEGOTIATED
,STL.NAME
,LIN.bill_to_site_use_id bill_to_site_use_id -- 11.5.10 changes
,LIN.ship_to_site_use_id ship_to_site_use_id -- 11.5.10 changes
,OKSLIN.discount_list discount_list -- 11.5.10 changes
,LIN.price_list_id price_list_id -- 11.5.10 changes
FROM OKC_K_HEADERS_ALL_B HDR -- OKC_K_HEADERS_B HDR -- Modified for 12.0 MOAC project (JVARGHES)
,OKC_LINE_STYLES_V STL
,OKC_K_LINES_B LIN
,OKS_K_LINES_B OKSLIN -- 11.5.10 changes
WHERE LIN.ID = Cx_Cle_Id --Commented by Jvorugan NVL(Cx_Cle_Id, LIN.ID)
AND LIN.START_DATE IS NOT NULL
AND LIN.END_DATE IS NOT NULL
AND STL.ID = LIN.LSE_ID
AND HDR.ID = LIN.DNZ_CHR_ID
AND HDR.TEMPLATE_YN = G_NO
AND OKSLIN.CLE_ID = LIN.Id;
SELECT LIN.ID
,LIN.CLE_ID
,LIN.DNZ_CHR_ID
,LIN.STS_CODE
,LIN.START_DATE
,Get_End_Date_Time(LIN.END_DATE) End_Date
,Get_End_Date_Time(LIN.DATE_TERMINATED) Date_Terminated
,LIN.PRICE_NEGOTIATED
,STL.NAME
,LIN.bill_to_site_use_id bill_to_site_use_id -- 11.5.10 changes
,LIN.ship_to_site_use_id ship_to_site_use_id -- 11.5.10 changes
,OKSLIN.discount_list discount_list -- 11.5.10 changes
,LIN.price_list_id price_list_id -- 11.5.10 changes
FROM OKC_K_HEADERS_ALL_B HDR -- OKC_K_HEADERS_B HDR -- Modified for 12.0 MOAC project (JVARGHES)
,OKC_LINE_STYLES_V STL
,OKC_K_LINES_B LIN
,OKS_K_LINES_B OKSLIN -- 11.5.10 changes
WHERE -- LIN.ID = NVL(Cx_Cle_Id, LIN.ID)
LIN.START_DATE IS NOT NULL
AND LIN.END_DATE IS NOT NULL
AND STL.ID = LIN.LSE_ID
AND HDR.ID = LIN.DNZ_CHR_ID
AND HDR.TEMPLATE_YN = G_NO
AND OKSLIN.CLE_ID = LIN.Id;
SELECT LIN.ROWID
,LIN.ID
,LIN.DNZ_CHR_ID
,LIN.CLE_ID
,LIN.PRICE_NEGOTIATED
,STL.NAME
,HDR.AUTHORING_ORG_ID ORG_ID
,HDR.INV_ORGANIZATION_ID ORGANIZATION_ID
FROM OKC_K_HEADERS_ALL_B HDR -- OKC_K_HEADERS_B HDR -- Modified for 12.0 MOAC project (JVARGHES)
,OKC_LINE_STYLES_V STL
,OKC_K_LINES_B LIN
,OKS_K_LINES_B OKSLIN --11.5.10 changes
WHERE LIN.ID = Cx_Cle_Id -- COmmented by Jvorugan for bug:4998337 NVL(Cx_Cle_Id, LIN.ID)
AND LIN.LSE_ID IN (7, 8, 9, 10, 11, 13, 18, 25, 35)
AND STL.ID = LIN.LSE_ID
AND HDR.ID = LIN.DNZ_CHR_ID
AND OKSLIN.CLE_ID = LIN.ID;
SELECT LIN.ROWID
,LIN.ID
,LIN.DNZ_CHR_ID
,LIN.CLE_ID
,LIN.PRICE_NEGOTIATED
,STL.NAME
,HDR.AUTHORING_ORG_ID ORG_ID
,HDR.INV_ORGANIZATION_ID ORGANIZATION_ID
FROM OKC_K_HEADERS_ALL_B HDR -- OKC_K_HEADERS_B HDR -- Modified for 12.0 MOAC project (JVARGHES)
,OKC_LINE_STYLES_V STL
,OKC_K_LINES_B LIN
,OKS_K_LINES_B OKSLIN --11.5.10 changes
WHERE -- LIN.ID = NVL(Cx_Cle_Id, LIN.ID)
LIN.LSE_ID IN (7, 8, 9, 10, 11, 13, 18, 25, 35)
AND STL.ID = LIN.LSE_ID
AND HDR.ID = LIN.DNZ_CHR_ID
AND OKSLIN.CLE_ID = LIN.ID;
SELECT CP.Name
FROM OKX_CUSTOMER_PRODUCTS_V CP
WHERE CP.Id1 = Cn_Prod_Id
AND CP.Org_Id = Cn_Org_Id
AND CP.Organization_Id = Ln_Organization_Id;
SELECT MSI.DESCRIPTION Name
FROM CSI_ITEM_INSTANCES CSI,
CSI_I_ORG_ASSIGNMENTS CIOA,
MTL_SYSTEM_ITEMS_B_KFV MSI
WHERE CSI.INSTANCE_ID = Cn_Prod_Id
AND CSI.INSTANCE_ID = CIOA.INSTANCE_ID(+)
AND CIOA.RELATIONSHIP_TYPE_CODE (+) = 'SOLD_FROM'
AND CSI.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND CIOA.OPERATING_UNIT_ID = Cn_Org_Id
AND CSI.INV_MASTER_ORGANIZATION_ID = Ln_Organization_Id;
SELECT CVT.Code
,CVT.Meaning
,CVT.Description
,CVT.Importance_Level
FROM OKS_K_Lines_B OKSLB
,OKS_Cov_Types_V CVT
WHERE OKSLB.Cle_Id = Cx_CVL_Id
AND OKSLB.Coverage_Type = CVT.Code;
SELECT RSC.resource_id Resource_Id
,decode(RSC.category,'EMPLOYEE','RS_EMPLOYEE',RSC.category) Resource_Type
-- ,decode(RSC.Resource_Type,'EMPLOYEE','RS_EMPLOYEE',RSC.Resource_Type) Resource_Type
,cimbp.object1_id1
,con.primary_yn
,con.resource_class
FROM JTF_RS_RESOURCE_EXTNS RSC --OKX_Resources_V RSC
,OKC_Contacts CON
,OKC_K_Party_Roles_B ROL
,OKC_K_Lines_B BPL
,OKC_K_Lines_B SBL
,OKC_K_Lines_B COV
,okc_k_items cimbp
WHERE SBL.Id = Cx_SBL_Id
AND SBL.Lse_Id IN (1,14,19)
and cov.cle_id = sbl.id
and cov.lse_id in (2,15,20)
AND BPL.cle_id = COV.id
AND BPL.Lse_Id IN (3,16,21)
and bpl.id = cimbp.cle_id
and cimbp.object1_id1 = nvl(p_business_process_id,cimbp.object1_id1)
AND trunc(nvl(Ld_Request_Date,sysdate)) >= trunc(bpl.start_date)
and trunc(nvl(Ld_Request_Date,sysdate)) <= trunc(oks_entitlements_pvt.get_final_end_date(bpl.dnz_chr_id,bpl.end_date)) -- uptake grace period
AND ROL.Cle_Id = BPL.Id
AND CON.Cpl_Id = ROL.Id
AND CON.Jtot_Object1_Code = 'OKX_RESOURCE'
AND RSC.resource_id = CON.Object1_Id1
AND SBL.dnz_chr_id = BPL.dnz_chr_id
AND SBL.dnz_chr_id = COV.dnz_chr_id
AND BPL.dnz_chr_id = ROL.dnz_chr_id
AND ROL.dnz_chr_id = CON.dnz_chr_id
UNION ALL
SELECT RSG.Id1 Resource_Id
,RSG.Resource_Type Resource_Type
,cimbp.object1_id1
,con.primary_yn
,con.resource_class
FROM OKS_Resource_Groups_V RSG
,OKC_Contacts CON
,OKC_K_Party_Roles_B ROL
,OKC_K_Lines_B BPL
,OKC_K_Lines_B SBL
,OKC_K_Lines_B COV
,okc_k_items cimbp
WHERE SBL.Id = Cx_SBL_Id
AND SBL.Lse_Id IN (1,14,19)
and cov.cle_id = sbl.id
and cov.lse_id in (2,15,20)
AND BPL.cle_id = COV.id
AND BPL.Lse_Id IN (3,16,21)
and bpl.id = cimbp.cle_id
and cimbp.object1_id1 = nvl(p_business_process_id,cimbp.object1_id1)
AND trunc(nvl(Ld_Request_Date,sysdate)) >= trunc(bpl.start_date)
and trunc(nvl(Ld_Request_Date,sysdate)) <= trunc(get_final_end_date(bpl.dnz_chr_id,bpl.end_date)) -- uptake grace period
AND ROL.Cle_Id = BPL.Id
AND CON.Cpl_Id = ROL.Id
AND CON.Jtot_Object1_Code = 'OKS_RSCGROUP'
AND RSG.Id1 = CON.Object1_Id1
AND RSG.Id2 = CON.Object1_Id2
AND SBL.dnz_chr_id = BPL.dnz_chr_id
AND SBL.dnz_chr_id = COV.dnz_chr_id
AND BPL.dnz_chr_id = ROL.dnz_chr_id
AND ROL.dnz_chr_id = CON.dnz_chr_id;
SELECT RSC.resource_id Resource_Id
,decode(RSC.category,'EMPLOYEE','RS_EMPLOYEE',RSC.category) Resource_Type
-- ,decode(RSC.Resource_Type,'EMPLOYEE','RS_EMPLOYEE',RSC.Resource_Type) Resource_Type
,cimbp.object1_id1
,con.primary_yn
,con.resource_class
,bpl.start_date bpl_start_date
,bpl.end_date bpl_end_date
,ksl.Standard_Cov_YN Standard_Cov_YN
,bpl.id bpl_id
,ksl.dnz_chr_id ksl_dnz_chr_id
FROM JTF_RS_RESOURCE_EXTNS RSC --OKX_Resources_V RSC
,OKC_Contacts CON
,OKC_K_Party_Roles_B ROL
,OKC_K_Lines_B BPL
,okc_k_items cimbp
,oks_k_lines_b ksl
WHERE ksl.cle_id = Cx_SBL_Id
AND BPL.cle_id = ksl.Coverage_Id
AND BPL.Lse_Id IN (3,16,21)
and bpl.id = cimbp.cle_id
and cimbp.object1_id1 = nvl(p_business_process_id,cimbp.object1_id1)
AND ROL.Cle_Id = BPL.Id
AND CON.Cpl_Id = ROL.Id
AND CON.Jtot_Object1_Code = 'OKX_RESOURCE'
AND RSC.resource_id = CON.Object1_Id1
-- AND BPL.dnz_chr_id = COV.dnz_chr_id
AND BPL.dnz_chr_id = ROL.dnz_chr_id
AND ROL.dnz_chr_id = CON.dnz_chr_id
UNION ALL
SELECT RSG.Id1 Resource_Id
,RSG.Resource_Type Resource_Type
,cimbp.object1_id1
,con.primary_yn
,con.resource_class
,bpl.start_date bpl_start_date
,bpl.end_date bpl_end_date
,ksl.Standard_Cov_YN Standard_Cov_YN
,bpl.id bpl_id
,ksl.dnz_chr_id ksl_dnz_chr_id
FROM OKS_Resource_Groups_V RSG
,OKC_Contacts CON
,OKC_K_Party_Roles_B ROL
,OKC_K_Lines_B BPL
,okc_k_items cimbp
,oks_k_lines_b ksl
WHERE ksl.cle_id = Cx_SBL_Id
AND BPL.cle_id = ksl.Coverage_Id
AND BPL.Lse_Id IN (3,16,21)
and bpl.id = cimbp.cle_id
and cimbp.object1_id1 = nvl(p_business_process_id,cimbp.object1_id1)
AND ROL.Cle_Id = BPL.Id
AND CON.Cpl_Id = ROL.Id
AND CON.Jtot_Object1_Code = 'OKS_RSCGROUP'
AND RSG.Id1 = CON.Object1_Id1
AND RSG.Id2 = CON.Object1_Id2
-- AND BPL.dnz_chr_id = COV.dnz_chr_id
AND BPL.dnz_chr_id = ROL.dnz_chr_id
AND ROL.dnz_chr_id = CON.dnz_chr_id;
SELECT PR.DNZ_CHR_ID CONTRACT_ID
,CS.RESOURCE_ID CONTACT_ID
,CS.CATEGORY RSC_CATEGORY
,NULL CONTACT_NAME
,PR.ID CONTACT_ROLE_ID
,CO.CRO_CODE CONTACT_ROLE_CODE
,HDR.ORG_ID ORG_ID -- Modified for 12.0 MOAC project (JVARGHES)
FROM JTF_RS_RESOURCE_EXTNS CS --OKX_RESOURCES_V CS
,OKC_CONTACTS CO
,OKC_K_PARTY_ROLES_B PR
,OKC_K_HEADERS_ALL_B HDR -- Modified for 12.0 MOAC project (JVARGHES)
WHERE PR.DNZ_CHR_ID = Cx_Chr_Id
-- Modified for 12.0 MOAC project (JVARGHES)
AND HDR.ID = Cx_Chr_Id
AND HDR.ID = PR.Dnz_Chr_Id
and co.dnz_chr_id = pr.dnz_chr_id
AND CO.CPL_ID = PR.ID
AND CO.OBJECT1_ID1 = CS.RESOURCE_ID --CS.ID1
AND CO.OBJECT1_ID2 = '#' --CS.ID2
AND CO.JTOT_OBJECT1_CODE = 'OKX_RESOURCE';
SELECT PR.DNZ_CHR_ID CONTRACT_ID
,POA.AGENT_ID CONTACT_ID
,'PO_AGENT' RSC_CATEGORY
,PER.FULL_NAME CONTACT_NAME
,PR.ID CONTACT_ROLE_ID
,CO.CRO_CODE CONTACT_ROLE_CODE
FROM PO_AGENTS POA --OKX_BUYERS_V CS
,PER_ALL_PEOPLE_F PER
,OKC_CONTACTS CO
,OKC_K_PARTY_ROLES_B PR
WHERE PR.DNZ_CHR_ID = Cx_Chr_Id
and co.dnz_chr_id = pr.dnz_chr_id
AND CO.CPL_ID = PR.ID
AND CO.OBJECT1_ID1 = POA.AGENT_ID --CS.ID1
AND CO.OBJECT1_ID2 = '#' --CS.ID2
AND CO.JTOT_OBJECT1_CODE = 'OKX_BUYER'
and POA.AGENT_ID = PER.PERSON_ID
and PER.EFFECTIVE_START_DATE = (SELECT MAX(A.EFFECTIVE_START_DATE) FROM PER_ALL_PEOPLE_F A
WHERE A.PERSON_ID = PER.PERSON_ID);
SELECT PR.DNZ_CHR_ID CONTRACT_ID
,S.SALESREP_ID CONTACT_ID
,CS.CATEGORY RSC_CATEGORY
,TL.RESOURCE_NAME CONTACT_NAME
,PR.ID CONTACT_ROLE_ID
,CO.CRO_CODE CONTACT_ROLE_CODE
FROM JTF_RS_RESOURCE_EXTNS CS
,JTF_RS_SALESREPS S
,JTF_RS_RESOURCE_EXTNS_TL TL -- Bug Fix #5442182 hmnair
,OKC_CONTACTS CO
,OKC_K_PARTY_ROLES_B PR
,OKC_K_HEADERS_ALL_B HDR -- Modified for 12.0 MOAC project (JVARGHES)
WHERE PR.DNZ_CHR_ID = Cx_Chr_Id
AND co.dnz_chr_id = pr.dnz_chr_id
AND CO.CPL_ID = PR.ID
AND CO.OBJECT1_ID1 = S.SALESREP_ID --CS.ID1
AND CO.OBJECT1_ID2 = '#' --CS.ID2
AND CO.JTOT_OBJECT1_CODE = 'OKX_SALEPERS'
-- Modified for 12.0 MOAC project (JVARGHES)
AND HDR.ID = Cx_Chr_Id
AND HDR.ID = PR.Dnz_Chr_Id
AND S.ORG_ID = HDR.ORG_ID
-- AND (S.ORG_ID = SYS_CONTEXT('OKC_CONTEXT','ORG_ID') OR NVL(SYS_CONTEXT('OKC_CONTEXT','ORG_ID'),-99) = -99)
AND S.RESOURCE_ID = CS.RESOURCE_ID
AND CS.CATEGORY in ('EMPLOYEE','OTHER','PARTY','PARTNER','SUPPLIER_CONTACT')
AND TL.RESOURCE_ID = CS.RESOURCE_ID -- Bug Fix #5442182 hmnair
AND TL.LANGUAGE = USERENV('LANG')
AND TL.CATEGORY = CS.CATEGORY;
SELECT PR.DNZ_CHR_ID CONTRACT_ID
,CS.RESOURCE_ID CONTACT_ID
,CS.CATEGORY RSC_CATEGORY
,NULL CONTACT_NAME
,PR.ID CONTACT_ROLE_ID
,CO.CRO_CODE CONTACT_ROLE_CODE
,HDR.ORG_ID ORG_ID -- Modified for 12.0 MOAC project (JVARGHES)
FROM JTF_RS_RESOURCE_EXTNS CS --OKX_RESOURCES_V CS
,OKC_CONTACTS CO
,OKC_K_PARTY_ROLES_B PR
,OKC_K_HEADERS_ALL_B HDR -- Modified for 12.0 MOAC project (JVARGHES)
WHERE PR.DNZ_CHR_ID = Cx_Chr_Id
and PR.CLE_ID = Cx_Cle_Id
-- Modified for 12.0 MOAC project (JVARGHES)
AND HDR.ID = Cx_Chr_Id
AND HDR.ID = PR.Dnz_Chr_Id
--
and co.dnz_chr_id = pr.dnz_chr_id
AND CO.CPL_ID = PR.ID
AND CO.OBJECT1_ID1 = CS.RESOURCE_ID --CS.ID1
AND CO.OBJECT1_ID2 = '#' --CS.ID2
AND CO.JTOT_OBJECT1_CODE = 'OKX_RESOURCE';
SELECT PR.DNZ_CHR_ID CONTRACT_ID
,POA.AGENT_ID CONTACT_ID
,'PO_AGENT' RSC_CATEGORY
,PER.FULL_NAME CONTACT_NAME
,PR.ID CONTACT_ROLE_ID
,CO.CRO_CODE CONTACT_ROLE_CODE
FROM PO_AGENTS POA --OKX_BUYERS_V CS
,PER_ALL_PEOPLE_F PER
,OKC_CONTACTS CO
,OKC_K_PARTY_ROLES_B PR
WHERE PR.DNZ_CHR_ID = Cx_Chr_Id
and PR.CLE_ID = Cx_Cle_Id
and co.dnz_chr_id = pr.dnz_chr_id
AND CO.CPL_ID = PR.ID
AND CO.OBJECT1_ID1 = POA.AGENT_ID --CS.ID1
AND CO.OBJECT1_ID2 = '#' --CS.ID2
AND CO.JTOT_OBJECT1_CODE = 'OKX_BUYER'
and POA.AGENT_ID = PER.PERSON_ID
and PER.EFFECTIVE_START_DATE = (SELECT MAX(A.EFFECTIVE_START_DATE) FROM PER_ALL_PEOPLE_F A
WHERE A.PERSON_ID = PER.PERSON_ID);
SELECT PR.DNZ_CHR_ID CONTRACT_ID
,S.SALESREP_ID CONTACT_ID
,CS.CATEGORY RSC_CATEGORY
,TL.RESOURCE_NAME CONTACT_NAME -- Bug Fix #5442182 hmnair
,PR.ID CONTACT_ROLE_ID
,CO.CRO_CODE CONTACT_ROLE_CODE
FROM JTF_RS_RESOURCE_EXTNS CS
,JTF_RS_SALESREPS S
,JTF_RS_RESOURCE_EXTNS_TL TL -- Bug Fix #5442182 hmnair
,OKC_CONTACTS CO
,OKC_K_PARTY_ROLES_B PR
,OKC_K_HEADERS_ALL_B HDR -- Modified for 12.0 MOAC project (JVARGHES)
WHERE PR.DNZ_CHR_ID = Cx_Chr_Id
and PR.CLE_ID = Cx_Cle_Id
and co.dnz_chr_id = pr.dnz_chr_id
AND CO.CPL_ID = PR.ID
AND CO.OBJECT1_ID1 = S.SALESREP_ID --CS.ID1
AND CO.OBJECT1_ID2 = '#' --CS.ID2
AND CO.JTOT_OBJECT1_CODE = 'OKX_SALEPERS'
-- Modified for 12.0 MOAC project (JVARGHES)
AND HDR.ID = Cx_Chr_Id
AND HDR.ID = PR.Dnz_Chr_Id
AND S.ORG_ID = HDR.Org_ID
-- AND (S.ORG_ID = SYS_CONTEXT('OKC_CONTEXT','ORG_ID') OR NVL(SYS_CONTEXT('OKC_CONTEXT','ORG_ID'),-99) = -99)
AND S.RESOURCE_ID = CS.RESOURCE_ID
AND CS.CATEGORY in ('EMPLOYEE','OTHER','PARTY','PARTNER','SUPPLIER_CONTACT')
AND TL.RESOURCE_ID = CS.RESOURCE_ID -- Bug Fix #5442182 hmnair
AND TL.LANGUAGE = USERENV('LANG')
AND TL.CATEGORY = CS.CATEGORY;
SELECT CLE.DNZ_CHR_ID CONTRACT_ID
FROM Okc_K_lines_B cle
WHERE cle.id = Cx_Cle_Id
AND rownum = 1;
SELECT C.LAST_NAME NAME
FROM JTF_RS_RESOURCE_EXTNS RSC ,
PO_VENDOR_SITES_ALL S ,
PO_VENDOR_CONTACTS C
WHERE RSC.RESOURCE_ID = Cx_Resource_Id
and C.VENDOR_CONTACT_ID = RSC.SOURCE_ID
and S.VENDOR_SITE_ID = C.VENDOR_SITE_ID
AND S.ORG_ID = cn_org_id ; -- Modified for 12.0 MOAC project (JVARGHES)
SELECT PER.FULL_NAME NAME
FROM JTF_RS_RESOURCE_EXTNS RSC ,
FND_USER U ,
PER_ALL_PEOPLE_F PER
WHERE PER.PERSON_ID = RSC.SOURCE_ID
and U.USER_ID = RSC.USER_ID
and RSC.RESOURCE_ID = Cx_Resource_Id
and PER.EFFECTIVE_START_DATE = (SELECT MAX(A.EFFECTIVE_START_DATE) FROM PER_ALL_PEOPLE_F A
WHERE A.PERSON_ID = PER.PERSON_ID);
SELECT PARTY.PARTY_NAME NAME
FROM JTF_RS_RESOURCE_EXTNS RSC ,
FND_USER U ,
HZ_PARTIES PARTY
WHERE RSC.CATEGORY IN ( 'PARTNER', 'PARTY')
AND PARTY.PARTY_ID = RSC.SOURCE_ID
AND U.USER_ID = RSC.USER_ID
and RSC.RESOURCE_ID = Cx_Resource_Id;
SELECT TL.RESOURCE_NAME NAME -- Bug Fix #5442182 hmnair
FROM JTF_RS_RESOURCE_EXTNS RSC ,
FND_USER U ,
JTF_RS_SALESREPS SRP
,JTF_RS_RESOURCE_EXTNS_TL TL -- Bug Fix #5442182 hmnair
WHERE RSC.CATEGORY = 'OTHER'
AND SRP.RESOURCE_ID = RSC.RESOURCE_ID
AND U.USER_ID = RSC.USER_ID
AND SRP.Org_ID = Cn_Org_Id -- Modified for 12.0 MOAC project (JVARGHES)
--AND SRP.ORG_ID = sys_context ('OKC_CONTEXT', 'ORG_ID') -- Modified for 12.0 MOAC project (JVARGHES)
AND TL.RESOURCE_ID = RSC.RESOURCE_ID -- Bug Fix #5442182 hmnair
AND TL.LANGUAGE = USERENV('LANG')
AND TL.CATEGORY = RSC.CATEGORY
and RSC.RESOURCE_ID = Cx_Resource_Id;
SELECT LU.LOOKUP_CODE CODE,
LU.MEANING MEANING
FROM fnd_lookups lu
WHERE LU.LOOKUP_TYPE = 'OKC_CONTACT_ROLE';
SELECT '*'
FROM Okx_System_Items_V CI
WHERE CI.Id1 = Cx_CovItem_Id
-- AND CI.Id2 = Cn_Organization_Id -- Bug# 4735542
AND CI.Serviceable_Product_Flag ='Y';
SELECT '*'
FROM Okx_Parties_V PY
WHERE PY.Id1 = Cx_CovParty_Id
AND PY.Id2 = '#';
SELECT CA.Party_Id
FROM Okx_Customer_Accounts_V CA
WHERE CA.Id1 = Cx_CovCust_Id
AND CA.Id2 = '#';
SELECT PS.Id1
,PS.Id2
,PS.Party_Id
FROM Okx_Party_Sites_V PS
WHERE PS.Id1 = Cx_CovSite_Id;
SELECT CSISYS.System_Id System_Id,
CSISYS.Customer_Id,
CSISYS.Install_Site_Use_Id
FROM CSI_SYSTEMS_B CSISYS
connect by prior parent_system_id = system_id
start with system_id = Cx_CovSys_Id;
select relationship_id,object_id
into l_relationship_id,l_object_id
from CSI_II_RELATIONSHIPS
where subject_id = l_subject_id
and relationship_type_code = l_rel_type_code
and ((active_end_date is null) or (active_end_date > sysdate));
SELECT object_id,
subject_id
FROM csi_ii_relationships
connect by prior object_id = subject_id
start with subject_id = Cx_CovProd_Id
UNION
SELECT instance_id object_id,
-- null subject_id
-1 subject_id --bug 2740241
FROM csi_item_instances
where instance_id = Cx_CovProd_Id;
SELECT CSI.Inventory_item_id Inventory_item_id,
CSI.System_id System_id,
CSI.Owner_party_account_id Owner_party_account_id, --CSI.Owner_party_id Owner_party_id
CSI.install_location_id install_location_id,
CSI.INSTALL_LOCATION_TYPE_CODE install_location_type_code
FROM CSI_ITEM_INSTANCES CSI
WHERE CSI.INSTANCE_ID = Cx_CovProd_Id;
Lx_DeDup_Tab.DELETE(Li_TableIdx);
SELECT DISTINCT IT.Dnz_Chr_Id Dnz_Chr_Id, IT.Cle_Id
FROM OKC_K_ITEMS IT
WHERE IT.Object1_Id1 = TO_CHAR(Cx_CovLevel_Obj_Id1)
AND (IT.Object1_Id2 = Cv_CovLevel_Obj_Id2 OR Cv_CovLevel_Obj_Id2 = '#')
AND IT.Jtot_Object1_Code = Cx_CovLevel_Obj_Code;
SELECT /*+ index(IT OKC_K_ITEMS_N2) */ DISTINCT IT.Dnz_Chr_Id Dnz_Chr_Id, IT.Cle_Id
FROM OKC_K_ITEMS IT
WHERE IT.Object1_Id1 = TO_CHAR(Cx_CovLevel_Obj_Id1)
-- AND (IT.Object1_Id2 = Cv_CovLevel_Obj_Id2 OR Cv_CovLevel_Obj_Id2 = '#') -- BUG# 4735542
AND IT.Jtot_Object1_Code = Cx_CovLevel_Obj_Code
AND EXISTS (SELECT/*+ push_subq no_unnest */ '*'
FROM OKC_K_PARTY_ROLES_B PR
WHERE PR.CHR_ID = IT.DNZ_CHR_ID
AND PR.CLE_ID IS NULL
AND PR.DNZ_CHR_ID = IT.DNZ_CHR_ID
AND PR.OBJECT1_ID1 = TO_CHAR(p_party_id)
AND PR.OBJECT1_ID2 = '#'
AND PR.JTOT_OBJECT1_CODE = 'OKX_PARTY'
AND PR.RLE_CODE <> 'VENDOR');
SELECT /*+ index(IT OKC_K_ITEMS_N2) */ DISTINCT IT.Dnz_Chr_Id Dnz_Chr_Id, IT.Cle_Id
FROM OKC_K_ITEMS IT
WHERE IT.Object1_Id1 = TO_CHAR(Cx_CovLevel_Obj_Id1)
-- AND (IT.Object1_Id2 = Cv_CovLevel_Obj_Id2 OR Cv_CovLevel_Obj_Id2 = '#') -- BUG# 4735542
AND IT.Jtot_Object1_Code = Cx_CovLevel_Obj_Code
AND EXISTS (SELECT/*+ push_subq no_unnest */ '*'
FROM OKC_K_PARTY_ROLES_B PR
WHERE PR.CHR_ID = IT.DNZ_CHR_ID
AND PR.CLE_ID IS NULL
AND PR.DNZ_CHR_ID = IT.DNZ_CHR_ID
AND PR.OBJECT1_ID1 = TO_CHAR(p_party_id)
AND PR.OBJECT1_ID2 = '#'
AND PR.JTOT_OBJECT1_CODE = 'OKX_PARTY'
AND PR.RLE_CODE not in ('VENDOR','MERCHANT')); ---Modified condition for ER9681794
SELECT Dnz_Chr_Id, Id
FROM Okc_K_Lines_B
WHERE Id = Cx_CovLvlLine_Id
AND Lse_Id In (7,8,9,10,11,35,18,25);
SELECT Dnz_Chr_Id, Id
FROM Okc_K_Lines_B
WHERE Id = Cx_SrvLine_Id
AND Lse_Id In (1,14,19);
SELECT Id
FROM OKC_K_HEADERS_ALL_B -- OKC_K_HEADERS_B HDR -- Modified for 12.0 MOAC project (JVARGHES)
WHERE Contract_Number = Cv_Contract_Num
--AND (Cv_Contract_Num_Modifier IS NULL OR Contract_Number_Modifier = Cv_Contract_Num_Modifier);
SELECT XI.Name
,XI.Description
,SV.Start_Date
,Get_End_Date_Time(SV.End_Date) End_Date
,Get_End_Date_Time(SV.Date_Terminated) Date_Terminated
FROM Okx_System_Items_V XI
,Okc_K_Items IT
,Okc_K_Lines_B SV
WHERE SV.Id = Cx_SrvLine_Id
AND SV.Lse_Id IN (1,14,19)
AND IT.Cle_Id = SV.Id
-- AND IT.Jtot_Object1_Code IN ('OKX_SERVICE','OKX_WARRANTY')
AND XI.Id1 = IT.Object1_Id1
AND XI.Id2 = IT.Object1_Id2
AND XI.Service_Item_Flag = 'Y'
AND XI.Organization_Id = Cn_Organization_Id;
SELECT COV.Id
,COV.Name
,COV.Item_Description
,COV.Start_Date
,Get_End_Date_Time(COV.End_Date) End_Date
,Get_End_Date_Time(COV.Date_Terminated) Date_Terminated
,KSL.Standard_COV_YN
,SVL.Start_Date
,Get_End_Date_Time(SVL.End_Date) End_Date
,Get_End_Date_Time(SVL.Date_Terminated) Date_Terminated
FROM Okc_K_Lines_B SVL
,Oks_K_Lines_B KSL
,Okc_K_Lines_V COV
WHERE SVL.Id = Cx_SrvLine_Id
AND SVL.Lse_Id in (1,14,19)
AND KSL.Cle_Id = SVL.Id
AND COV.ID = KSL.Coverage_Id
AND COV.Lse_Id IN (2,15,20);
SELECT HD.Id Contract_Id
,HD.Contract_Number
,HD.Contract_Number_Modifier
,HD.Authoring_Org_Id
,HD.Inv_Organization_Id
,HD.End_Date HDR_End_Date --grace period changes
,CL.Cle_Id Service_Line_Id
,CL.Id Coverage_Level_Line_Id
,SB.Lty_Code Coverage_Level_Code
,ST.Name Coverage_Level
,CL.Start_Date
,Get_End_Date_Time(CL.End_Date) End_Date
,Get_End_Date_Time(CL.Date_Terminated) Date_Terminated
,IT.Object1_Id1 Coverage_Level_Id
,DECODE(SB.Id, 14, 'Y', 15, 'Y', 16, 'Y', 17, 'Y', 18, 'Y', 'N') Warranty_Flag
FROM OKC_K_HEADERS_ALL_B HD -- OKC_K_HEADERS_B HDR -- Modified for 12.0 MOAC project (JVARGHES)
,Okc_Line_Styles_B SB
,Okc_Line_Styles_TL ST
,Okc_K_Items IT
,Okc_K_Lines_B CL
WHERE CL.Id = NVL(Cx_Cle_Id, CL.Id)
AND CL.Dnz_Chr_Id = Cx_Chr_Id
AND CL.Lse_ID IN (7,8,9,10,11,35,18,25)
AND IT.Cle_Id = CL.Id
AND SB.Id = CL.Lse_Id
AND ST.Id = SB.Id
and st.language = Cx_Lang
AND HD.Id = CL.Dnz_Chr_Id
AND HD.Scs_Code IN ('SERVICE','WARRANTY')
AND HD.Id > -1
AND HD.Template_YN <> 'Y'
AND (Cv_Cont_Pty_Id IS NULL
OR
EXISTS (SELECT '*'
FROM Okc_K_Party_Roles_B PR
WHERE PR.Chr_Id = HD.Id
AND PR.Cle_Id IS NULL
AND PR.Dnz_Chr_Id = HD.Id
AND PR.Object1_Id1 = Cv_Cont_Pty_Id
AND PR.Object1_Id2 = '#'
AND PR.Jtot_Object1_Code = 'OKX_PARTY'
AND PR.RLE_CODE <> 'VENDOR'));
SELECT HD.Id Contract_Id
,HD.Contract_Number
,HD.Contract_Number_Modifier
,HD.Authoring_Org_Id
,HD.Inv_Organization_Id
,HD.End_Date HDR_End_Date --grace period changes
,CL.Cle_Id Service_Line_Id
,CL.Id Coverage_Level_Line_Id
,SB.Lty_Code Coverage_Level_Code
,ST.Name Coverage_Level
,CL.Start_Date
,Get_End_Date_Time(CL.End_Date) End_Date
,Get_End_Date_Time(CL.Date_Terminated) Date_Terminated
,IT.Object1_Id1 Coverage_Level_Id
,DECODE(SB.Id, 14, 'Y', 15, 'Y', 16, 'Y', 17, 'Y', 18, 'Y', 'N') Warranty_Flag
FROM OKC_K_HEADERS_ALL_B HD -- OKC_K_HEADERS_B HDR -- Modified for 12.0 MOAC project (JVARGHES)
,Okc_Line_Styles_B SB
,Okc_Line_Styles_TL ST
,Okc_K_Items IT
,Okc_K_Lines_B CL
WHERE CL.Id = NVL(Cx_Cle_Id, CL.Id)
AND CL.Dnz_Chr_Id = Cx_Chr_Id
AND CL.Lse_ID IN (7,8,9,10,11,35,18,25)
AND IT.Cle_Id = CL.Id
AND SB.Id = CL.Lse_Id
AND ST.Id = SB.Id
and st.language = Cx_Lang
AND HD.Id = CL.Dnz_Chr_Id
AND HD.Scs_Code IN ('SERVICE','WARRANTY','SUBSCRIPTION') ---Modified condition for ER9681794
AND HD.Id > -1
AND HD.Template_YN <> 'Y'
AND (Cv_Cont_Pty_Id IS NULL
OR
EXISTS (SELECT '*'
FROM Okc_K_Party_Roles_B PR
WHERE PR.Chr_Id = HD.Id
AND PR.Cle_Id IS NULL
AND PR.Dnz_Chr_Id = HD.Id
AND PR.Object1_Id1 = Cv_Cont_Pty_Id
AND PR.Object1_Id2 = '#'
AND PR.Jtot_Object1_Code = 'OKX_PARTY'
AND PR.RLE_CODE not in ('VENDOR','MERCHANT'))); ---Modified condition for ER9681794
SELECT HD.Id Contract_Id
,HD.Contract_Number
,HD.Contract_Number_Modifier
,HD.Sts_Code
,HD.Authoring_Org_Id
,HD.Inv_Organization_Id
,HD.End_Date HDR_End_Date --grace period changes
,SV.Id Service_Line_Id
,SV.Start_Date SV_Start_Date
,Get_End_Date_Time(SV.End_Date) SV_End_Date
,Get_End_Date_Time(SV.Date_Terminated) SV_Date_Terminated
,CL.Sts_Code CL_Sts_Code
,CL.Id CovLvl_Line_Id
,CL.Start_Date CL_Start_Date
,Get_End_Date_Time(CL.End_Date) CL_End_Date
,Get_End_Date_Time(CL.Date_Terminated) CL_Date_Terminated
-- ,DECODE(SV.Lse_Id,14,'Y','N') Warranty_Flag
,DECODE(SV.Lse_Id, 14, 'Y', 15, 'Y', 16, 'Y', 17, 'Y', 18, 'Y', 'N') Warranty_Flag
FROM OKC_K_HEADERS_ALL_B HD -- OKC_K_HEADERS_B HDR -- Modified for 12.0 MOAC project (JVARGHES)
,Okc_K_Lines_B SV
,Okc_K_Lines_B CL
WHERE HD.Id = Cx_Chr_Id
AND HD.Scs_Code IN ('SERVICE','WARRANTY')
AND HD.Id > -1
AND HD.Template_YN <> 'Y'
AND SV.Dnz_Chr_Id = HD.Id
AND SV.Cle_Id IS NULL
AND SV.Chr_Id = HD.Id
AND SV.Lse_ID IN (1,14,19)
AND SV.Id = Cx_SrvLine_Id --NVL(Cx_SrvLine_Id,SV.Id)
AND CL.Cle_Id = SV.Id
AND CL.Lse_ID IN (7,8,9,10,11,35,18,25)
AND (Cx_BP_Id IS NULL
OR
EXISTS (SELECT '*' -- Modified for 12.0 Coverage Rearch project (JVARGHES)
FROM Okc_K_Items ITM
,Okc_K_Lines_B BPL
,Oks_K_Lines_B KSL
WHERE KSL.Cle_Id = SV.Id
AND BPL.Cle_Id = KSL.Coverage_ID
AND ITM.Cle_Id = BPL.Id
AND ITM.OBJECT1_ID1||'' = TO_CHAR(Cx_BP_Id)
AND ITM.Object1_Id2 = '#'
AND ITM.JTOT_OBJECT1_CODE||'' = 'OKX_BUSIPROC'
AND (Cv_ValFlg <> 'T' OR (Cd_req_dt BETWEEN nvl(BPL.START_DATE,Cd_req_dt) AND nvl(BPL.END_DATE,Cd_req_dt))) --Cv_ValFlg validation added for bugfix 14596603 by ansraj ----added for 13795417 by ansraj
));
SELECT HD.Id Contract_Id
,HD.Contract_Number
,HD.Contract_Number_Modifier
,HD.Sts_Code
,HD.Authoring_Org_Id
,HD.Inv_Organization_Id
,HD.End_Date HDR_End_Date --grace period changes
,SV.Id Service_Line_Id
,SV.Start_Date SV_Start_Date
,Get_End_Date_Time(SV.End_Date) SV_End_Date
,Get_End_Date_Time(SV.Date_Terminated) SV_Date_Terminated
,CL.Sts_Code CL_Sts_Code
,CL.Id CovLvl_Line_Id
,CL.Start_Date CL_Start_Date
,Get_End_Date_Time(CL.End_Date) CL_End_Date
,Get_End_Date_Time(CL.Date_Terminated) CL_Date_Terminated
-- ,DECODE(SV.Lse_Id,14,'Y','N') Warranty_Flag
,DECODE(SV.Lse_Id, 14, 'Y', 15, 'Y', 16, 'Y', 17, 'Y', 18, 'Y', 'N') Warranty_Flag
FROM OKC_K_HEADERS_ALL_B HD -- OKC_K_HEADERS_B HDR -- Modified for 12.0 MOAC project (JVARGHES)
,Okc_K_Lines_B SV
,Okc_K_Lines_B CL
WHERE HD.Id = Cx_Chr_Id
AND HD.Scs_Code IN ('SERVICE','WARRANTY','SUBSCRIPTION') ----Modified condition for ER9681794
AND HD.Id > -1
AND HD.Template_YN <> 'Y'
AND SV.Dnz_Chr_Id = HD.Id
AND SV.Cle_Id IS NULL
AND SV.Chr_Id = HD.Id
AND SV.Lse_ID IN (1,14,19)
AND SV.Id = Cx_SrvLine_Id --NVL(Cx_SrvLine_Id,SV.Id)
AND CL.Cle_Id = SV.Id
AND CL.Lse_ID IN (7,8,9,10,11,35,18,25)
AND (Cx_BP_Id IS NULL
OR
EXISTS (SELECT '*' -- Modified for 12.0 Coverage Rearch project (JVARGHES)
FROM Okc_K_Items ITM
,Okc_K_Lines_B BPL
,Oks_K_Lines_B KSL
WHERE KSL.Cle_Id = SV.Id
AND BPL.Cle_Id = KSL.Coverage_ID
AND ITM.Cle_Id = BPL.Id
AND ITM.OBJECT1_ID1||'' = TO_CHAR(Cx_BP_Id)
AND ITM.Object1_Id2 = '#'
AND ITM.JTOT_OBJECT1_CODE||'' = 'OKX_BUSIPROC'
AND (Cv_ValFlg <> 'T' OR (Cd_req_dt BETWEEN nvl(BPL.START_DATE,Cd_req_dt) AND nvl(BPL.END_DATE,Cd_req_dt))) --Cv_ValFlg validation added for bugfix 14596603 by ansraj ----added for 13795417 by ansraj
));
SELECT HD.Id Contract_Id
,HD.Contract_Number
,HD.Contract_Number_Modifier
,HD.Sts_Code
,HD.Authoring_Org_Id
,HD.Inv_Organization_Id
,HD.End_Date HDR_End_Date --grace period changes
,SV.Id Service_Line_Id
,SV.Start_Date SV_Start_Date
,Get_End_Date_Time(SV.End_Date) SV_End_Date
,Get_End_Date_Time(SV.Date_Terminated) SV_Date_Terminated
,CL.Sts_Code CL_Sts_Code
,CL.Id CovLvl_Line_Id
,CL.Start_Date CL_Start_Date
,Get_End_Date_Time(CL.End_Date) CL_End_Date
,Get_End_Date_Time(CL.Date_Terminated) CL_Date_Terminated
-- ,DECODE(SV.Lse_Id,14,'Y','N') Warranty_Flag
,DECODE(SV.Lse_Id, 14, 'Y', 15, 'Y', 16, 'Y', 17, 'Y', 18, 'Y', 'N') Warranty_Flag
FROM OKC_K_HEADERS_ALL_B HD -- OKC_K_HEADERS_B HDR -- Modified for 12.0 MOAC project (JVARGHES)
,Okc_K_Lines_B SV
,Okc_K_Lines_B CL
WHERE HD.Id = Cx_Chr_Id
AND HD.Scs_Code IN ('SERVICE','WARRANTY')
AND HD.Id > -1
AND HD.Template_YN <> 'Y'
AND SV.Dnz_Chr_Id = HD.Id
AND SV.Cle_Id IS NULL
AND SV.Chr_Id = HD.Id
AND SV.Lse_ID IN (1,14,19)
AND CL.Cle_Id = SV.Id
AND CL.Lse_ID IN (7,8,9,10,11,35,18,25)
AND CL.Id = Cx_CovLvlLine_Id
AND (Cx_BP_Id IS NULL
OR
EXISTS (SELECT '*' -- Modified for 12.0 Coverage Rearch project (JVARGHES)
FROM Okc_K_Items ITM
,Okc_K_Lines_B BPL
,Oks_K_Lines_B KSL
WHERE KSL.Cle_Id = SV.Id
AND BPL.Cle_Id = KSL.Coverage_ID
AND ITM.Cle_Id = BPL.Id
AND ITM.OBJECT1_ID1||'' = TO_CHAR(Cx_BP_Id)
AND ITM.Object1_Id2 = '#'
AND ITM.JTOT_OBJECT1_CODE||'' = 'OKX_BUSIPROC'
AND (Cv_ValFlg <> 'T' OR (Cd_req_dt BETWEEN nvl(BPL.START_DATE,Cd_req_dt) AND nvl(BPL.END_DATE,Cd_req_dt))) --Cv_ValFlg validation added for bugfix 14596603 by ansraj ----added for 13795417 by ansraj
));
SELECT HD.Id Contract_Id
,HD.Contract_Number
,HD.Contract_Number_Modifier
,HD.Sts_Code
,HD.Authoring_Org_Id
,HD.Inv_Organization_Id
,HD.End_Date HDR_End_Date --grace period changes
,SV.Id Service_Line_Id
,SV.Start_Date SV_Start_Date
,Get_End_Date_Time(SV.End_Date) SV_End_Date
,Get_End_Date_Time(SV.Date_Terminated) SV_Date_Terminated
,CL.Sts_Code CL_Sts_Code
,CL.Id CovLvl_Line_Id
,CL.Start_Date CL_Start_Date
,Get_End_Date_Time(CL.End_Date) CL_End_Date
,Get_End_Date_Time(CL.Date_Terminated) CL_Date_Terminated
-- ,DECODE(SV.Lse_Id,14,'Y','N') Warranty_Flag
,DECODE(SV.Lse_Id, 14, 'Y', 15, 'Y', 16, 'Y', 17, 'Y', 18, 'Y', 'N') Warranty_Flag
FROM OKC_K_HEADERS_ALL_B HD -- OKC_K_HEADERS_B HDR -- Modified for 12.0 MOAC project (JVARGHES)
,Okc_K_Lines_B SV
,Okc_K_Lines_B CL
WHERE HD.Id = Cx_Chr_Id
AND HD.Scs_Code IN ('SERVICE','WARRANTY','SUBSCRIPTION') -------Modified condition for ER9681794
AND HD.Id > -1
AND HD.Template_YN <> 'Y'
AND SV.Dnz_Chr_Id = HD.Id
AND SV.Cle_Id IS NULL
AND SV.Chr_Id = HD.Id
AND SV.Lse_ID IN (1,14,19)
AND CL.Cle_Id = SV.Id
AND CL.Lse_ID IN (7,8,9,10,11,35,18,25)
AND CL.Id = Cx_CovLvlLine_Id
AND (Cx_BP_Id IS NULL
OR
EXISTS (SELECT '*' -- Modified for 12.0 Coverage Rearch project (JVARGHES)
FROM Okc_K_Items ITM
,Okc_K_Lines_B BPL
,Oks_K_Lines_B KSL
WHERE KSL.Cle_Id = SV.Id
AND BPL.Cle_Id = KSL.Coverage_ID
AND ITM.Cle_Id = BPL.Id
AND ITM.OBJECT1_ID1||'' = TO_CHAR(Cx_BP_Id)
AND ITM.Object1_Id2 = '#'
AND ITM.JTOT_OBJECT1_CODE||'' = 'OKX_BUSIPROC'
AND (Cv_ValFlg <> 'T' OR (Cd_req_dt BETWEEN nvl(BPL.START_DATE,Cd_req_dt) AND nvl(BPL.END_DATE,Cd_req_dt))) --Cv_ValFlg validation added for bugfix 14596603 by ansraj ----added for 13795417 by ansraj
));
SELECT HD.Id Contract_Id
,HD.Contract_Number
,HD.Contract_Number_Modifier
,HD.Sts_Code
,HD.Authoring_Org_Id
,HD.Inv_Organization_Id
,HD.End_Date HDR_End_Date --grace period changes
,SV.Id Service_Line_Id
,SV.Start_Date SV_Start_Date
,Get_End_Date_Time(SV.End_Date) SV_End_Date
,Get_End_Date_Time(SV.Date_Terminated) SV_Date_Terminated
,CL.Sts_Code CL_Sts_Code
,CL.Id CovLvl_Line_Id
,CL.Start_Date CL_Start_Date
,Get_End_Date_Time(CL.End_Date) CL_End_Date
,Get_End_Date_Time(CL.Date_Terminated) CL_Date_Terminated
-- ,DECODE(SV.Lse_Id,14,'Y','N') Warranty_Flag
,DECODE(SV.Lse_Id, 14, 'Y', 15, 'Y', 16, 'Y', 17, 'Y', 18, 'Y', 'N') Warranty_Flag
FROM OKC_K_HEADERS_ALL_B HD -- OKC_K_HEADERS_B HDR -- Modified for 12.0 MOAC project (JVARGHES)
,Okc_K_Lines_B SV
,Okc_K_Lines_B CL
WHERE HD.Id = Cx_Chr_Id
AND HD.Scs_Code IN ('SERVICE','WARRANTY')
AND HD.Id > -1
AND HD.Template_YN <> 'Y'
AND SV.Dnz_Chr_Id = HD.Id
AND SV.Cle_Id IS NULL
AND SV.Chr_Id = HD.Id
AND SV.Lse_ID IN (1,14,19)
AND SV.Id = Cx_SrvLine_Id
AND CL.Cle_Id = SV.Id
AND CL.Lse_ID IN (7,8,9,10,11,35,18,25)
AND CL.Id = Cx_CovLvlLine_Id
AND (Cx_BP_Id IS NULL
OR
EXISTS (SELECT '*' -- Modified for 12.0 Coverage Rearch project (JVARGHES)
FROM Okc_K_Items ITM
,Okc_K_Lines_B BPL
,Oks_K_Lines_B KSL
WHERE KSL.Cle_Id = SV.Id
AND BPL.Cle_Id = KSL.Coverage_ID
AND ITM.Cle_Id = BPL.Id
AND ITM.OBJECT1_ID1||'' = TO_CHAR(Cx_BP_Id)
AND ITM.Object1_Id2 = '#'
AND ITM.JTOT_OBJECT1_CODE||'' = 'OKX_BUSIPROC'
AND (Cv_ValFlg <> 'T' OR (Cd_req_dt BETWEEN nvl(BPL.START_DATE,Cd_req_dt) AND nvl(BPL.END_DATE,Cd_req_dt))) --Cv_ValFlg validation added for bugfix 14596603 by ansraj ----added for 13795417 by ansraj
));
SELECT HD.Id Contract_Id
,HD.Contract_Number
,HD.Contract_Number_Modifier
,HD.Sts_Code
,HD.Authoring_Org_Id
,HD.Inv_Organization_Id
,HD.End_Date HDR_End_Date --grace period changes
,SV.Id Service_Line_Id
,SV.Start_Date SV_Start_Date
,Get_End_Date_Time(SV.End_Date) SV_End_Date
,Get_End_Date_Time(SV.Date_Terminated) SV_Date_Terminated
,CL.Sts_Code CL_Sts_Code
,CL.Id CovLvl_Line_Id
,CL.Start_Date CL_Start_Date
,Get_End_Date_Time(CL.End_Date) CL_End_Date
,Get_End_Date_Time(CL.Date_Terminated) CL_Date_Terminated
-- ,DECODE(SV.Lse_Id,14,'Y','N') Warranty_Flag
,DECODE(SV.Lse_Id, 14, 'Y', 15, 'Y', 16, 'Y', 17, 'Y', 18, 'Y', 'N') Warranty_Flag
FROM OKC_K_HEADERS_ALL_B HD -- OKC_K_HEADERS_B HDR -- Modified for 12.0 MOAC project (JVARGHES)
,Okc_K_Lines_B SV
,Okc_K_Lines_B CL
WHERE HD.Id = Cx_Chr_Id
AND HD.Scs_Code IN ('SERVICE','WARRANTY','SUBSCRIPTION') ----Modified condition for ER9681794
AND HD.Id > -1
AND HD.Template_YN <> 'Y'
AND SV.Dnz_Chr_Id = HD.Id
AND SV.Cle_Id IS NULL
AND SV.Chr_Id = HD.Id
AND SV.Lse_ID IN (1,14,19)
AND SV.Id = Cx_SrvLine_Id
AND CL.Cle_Id = SV.Id
AND CL.Lse_ID IN (7,8,9,10,11,35,18,25)
AND CL.Id = Cx_CovLvlLine_Id
AND (Cx_BP_Id IS NULL
OR
EXISTS (SELECT '*' -- Modified for 12.0 Coverage Rearch project (JVARGHES)
FROM Okc_K_Items ITM
,Okc_K_Lines_B BPL
,Oks_K_Lines_B KSL
WHERE KSL.Cle_Id = SV.Id
AND BPL.Cle_Id = KSL.Coverage_ID
AND ITM.Cle_Id = BPL.Id
AND ITM.OBJECT1_ID1||'' = TO_CHAR(Cx_BP_Id)
AND ITM.Object1_Id2 = '#'
AND ITM.JTOT_OBJECT1_CODE||'' = 'OKX_BUSIPROC'
AND (Cv_ValFlg <> 'T' OR (Cd_req_dt BETWEEN nvl(BPL.START_DATE,Cd_req_dt) AND nvl(BPL.END_DATE,Cd_req_dt))) --Cv_ValFlg validation added for bugfix 14596603 by ansraj ----added for 13795417 by ansraj
));
SELECT HD.Id Contract_Id
,HD.Contract_Number
,HD.Contract_Number_Modifier
,HD.Sts_Code
,HD.Authoring_Org_Id
,HD.Inv_Organization_Id
,HD.End_Date HDR_End_Date --grace period changes
,SV.Id Service_Line_Id
,SV.Start_Date SV_Start_Date
,Get_End_Date_Time(SV.End_Date) SV_End_Date
,Get_End_Date_Time(SV.Date_Terminated) SV_Date_Terminated
,CL.Sts_Code CL_Sts_Code
,CL.Id CovLvl_Line_Id
,CL.Start_Date CL_Start_Date
,Get_End_Date_Time(CL.End_Date) CL_End_Date
,Get_End_Date_Time(CL.Date_Terminated) CL_Date_Terminated
-- ,DECODE(SV.Lse_Id,14,'Y','N') Warranty_Flag
,DECODE(SV.Lse_Id, 14, 'Y', 15, 'Y', 16, 'Y', 17, 'Y', 18, 'Y', 'N') Warranty_Flag
FROM OKC_K_HEADERS_ALL_B HD -- OKC_K_HEADERS_B HDR -- Modified for 12.0 MOAC project (JVARGHES)
,Okc_K_Lines_B SV
,Okc_K_Lines_B CL
WHERE HD.Id = Cx_Chr_Id
AND HD.Scs_Code IN ('SERVICE','WARRANTY')
AND HD.Id > -1
AND HD.Template_YN <> 'Y'
AND SV.Dnz_Chr_Id = HD.Id
AND SV.Cle_Id IS NULL
AND SV.Chr_Id = HD.Id
AND SV.Lse_ID IN (1,14,19)
AND CL.Cle_Id = SV.Id
AND CL.Lse_ID IN (7,8,9,10,11,35,18,25)
AND (Cx_BP_Id IS NULL
OR
EXISTS (SELECT '*' -- Modified for 12.0 Coverage Rearch project (JVARGHES)
FROM Okc_K_Items ITM
,Okc_K_Lines_B BPL
,Oks_K_Lines_B KSL
WHERE KSL.Cle_Id = SV.Id
AND BPL.Cle_Id = KSL.Coverage_ID
AND ITM.Cle_Id = BPL.Id
AND ITM.OBJECT1_ID1||'' = TO_CHAR(Cx_BP_Id)
AND ITM.Object1_Id2 = '#'
AND ITM.JTOT_OBJECT1_CODE||'' = 'OKX_BUSIPROC'
AND (Cv_ValFlg <> 'T' OR (Cd_req_dt BETWEEN nvl(BPL.START_DATE,Cd_req_dt) AND nvl(BPL.END_DATE,Cd_req_dt))) --Cv_ValFlg validation added for bugfix 14596603 by ansraj ----added for 13795417 by ansraj
));
SELECT HD.Id Contract_Id
,HD.Contract_Number
,HD.Contract_Number_Modifier
,HD.Sts_Code
,HD.Authoring_Org_Id
,HD.Inv_Organization_Id
,HD.End_Date HDR_End_Date --grace period changes
,SV.Id Service_Line_Id
,SV.Start_Date SV_Start_Date
,Get_End_Date_Time(SV.End_Date) SV_End_Date
,Get_End_Date_Time(SV.Date_Terminated) SV_Date_Terminated
,CL.Sts_Code CL_Sts_Code
,CL.Id CovLvl_Line_Id
,CL.Start_Date CL_Start_Date
,Get_End_Date_Time(CL.End_Date) CL_End_Date
,Get_End_Date_Time(CL.Date_Terminated) CL_Date_Terminated
-- ,DECODE(SV.Lse_Id,14,'Y','N') Warranty_Flag
,DECODE(SV.Lse_Id, 14, 'Y', 15, 'Y', 16, 'Y', 17, 'Y', 18, 'Y', 'N') Warranty_Flag
FROM OKC_K_HEADERS_ALL_B HD -- OKC_K_HEADERS_B HDR -- Modified for 12.0 MOAC project (JVARGHES)
,Okc_K_Lines_B SV
,Okc_K_Lines_B CL
WHERE HD.Id = Cx_Chr_Id
AND HD.Scs_Code IN ('SERVICE','WARRANTY','SUBSCRIPTION') ----Modified condition for ER9681794
AND HD.Id > -1
AND HD.Template_YN <> 'Y'
AND SV.Dnz_Chr_Id = HD.Id
AND SV.Cle_Id IS NULL
AND SV.Chr_Id = HD.Id
AND SV.Lse_ID IN (1,14,19)
AND CL.Cle_Id = SV.Id
AND CL.Lse_ID IN (7,8,9,10,11,35,18,25)
AND (Cx_BP_Id IS NULL
OR
EXISTS (SELECT '*' -- Modified for 12.0 Coverage Rearch project (JVARGHES)
FROM Okc_K_Items ITM
,Okc_K_Lines_B BPL
,Oks_K_Lines_B KSL
WHERE KSL.Cle_Id = SV.Id
AND BPL.Cle_Id = KSL.Coverage_ID
AND ITM.Cle_Id = BPL.Id
AND ITM.OBJECT1_ID1||'' = TO_CHAR(Cx_BP_Id)
AND ITM.Object1_Id2 = '#'
AND ITM.JTOT_OBJECT1_CODE||'' = 'OKX_BUSIPROC'
AND (Cv_ValFlg <> 'T' OR (Cd_req_dt BETWEEN nvl(BPL.START_DATE,Cd_req_dt) AND nvl(BPL.END_DATE,Cd_req_dt))) --Cv_ValFlg validation added for bugfix 14596603 by ansraj ----added for 13795417 by ansraj
));
Lx_Contracts_02_Val.DELETE;
Lx_Contracts_02_Val.DELETE;
Lx_Contracts_02_Val.DELETE;
Lx_Contracts_02_Val.DELETE;
Lx_Contracts_02_Val.DELETE;
Lx_Contracts_02_Val.DELETE;
Lx_Contracts_02_Val.DELETE;
Lx_Contracts_02_Val.DELETE;
SELECT Dnz_Chr_Id
FROM Okc_K_lines_B
WHERE Id = Cx_SrvLine_Id;
SELECT HB.Id Contract_Id
,HB.Contract_Number Contract_Number
,HB.Contract_Number_Modifier Contract_Number_Modifier
,HT.Cognomen Contract_Known_As
,HT.Short_Description Contract_Short_Description
,HB.Sts_Code Contract_Status_Code
,HB.Start_Date Contract_Start_Date
,Get_End_Date_Time(HB.End_Date) Contract_End_Date
,Get_End_Date_Time(HB.Date_Terminated) Contract_Terminated_Date
FROM Okc_K_Headers_TL HT
,OKC_K_HEADERS_ALL_B HB -- OKC_K_HEADERS_B HB -- Modified for 12.0 MOAC project (JVARGHES)
WHERE HB.Id = NVL(Cx_Chr_Id,HB.Id)
AND HB.Contract_Number = NVL(Cv_Con_Number,HB.Contract_Number)
AND (Cv_Con_Number_Modifier IS NULL OR HB.Contract_Number_Modifier = Cv_Con_Number_Modifier)
AND (Cv_Con_Cusomer_Id IS NULL
OR
HB.Id IN ( SELECT PR.Chr_Id
FROM Okx_Parties_V PX
,Okc_K_Party_Roles_B PR
WHERE PR.Object1_Id1 = NVL(Cv_Con_Cusomer_Id,PR.Object1_Id1) --PR.Chr_Id = HB.Id
AND PR.Rle_Code in ('CUSTOMER')
AND PR.Jtot_Object1_Code = 'OKX_PARTY'
AND PX.Id1 = TO_NUMBER(PR.Object1_Id1)
AND PX.Id2 = PR.Object1_Id2 ))
AND (Cv_Service_Item_Id IS NULL
OR
HB.Id IN ( SELECT SV.Chr_Id
FROM Okx_System_Items_V XI
,Okc_K_Items IT
,Okc_K_Lines_B SV
WHERE SV.Lse_ID IN (1,14,19) --SV.Chr_Id = HB.Id
AND IT.Cle_Id = SV.Id
AND IT.Object1_Id1 = NVL(Cv_Service_Item_Id,IT.Object1_Id1)
AND IT.Jtot_Object1_Code IN ('OKX_SERVICE','OKX_WARRANTY')
AND XI.Id1 = TO_NUMBER(IT.Object1_Id1)
AND XI.Id2 = IT.Object1_Id2
AND XI.Service_Item_Flag = 'Y'
-- AND XI.Organization_Id = Cn_Organization_Id --Bug# 4735542.
))
AND HB.Scs_Code IN ('SERVICE','WARRANTY')
AND HB.Id > -1
AND HB.Template_YN <> 'Y'
AND HB.Id = HT.Id
AND HT.Language = USERENV('LANG');
SELECT HB.Id Contract_Id
,HB.Contract_Number Contract_Number
,HB.Contract_Number_Modifier Contract_Number_Modifier
,HT.Cognomen Contract_Known_As
,HT.Short_Description Contract_Short_Description
,HB.Sts_Code Contract_Status_Code
,HB.Start_Date Contract_Start_Date
,Get_End_Date_Time(HB.End_Date) Contract_End_Date
,Get_End_Date_Time(HB.Date_Terminated) Contract_Terminated_Date
FROM Okc_K_Headers_TL HT
,OKC_K_HEADERS_ALL_B HB -- OKC_K_HEADERS_B HB -- Modified for 12.0 MOAC project (JVARGHES)
WHERE HB.Id = NVL(Cx_Chr_Id,HB.Id)
AND HB.Contract_Number = NVL(Cv_Con_Number,HB.Contract_Number)
AND (Cv_Con_Number_Modifier IS NULL OR HB.Contract_Number_Modifier = Cv_Con_Number_Modifier)
AND (Cv_Con_Cusomer_Id IS NULL
OR
HB.Id IN ( SELECT PR.Chr_Id
FROM Okx_Parties_V PX
,Okc_K_Party_Roles_B PR
WHERE PR.Object1_Id1 = NVL(Cv_Con_Cusomer_Id,PR.Object1_Id1) --PR.Chr_Id = HB.Id
AND PR.Rle_Code in ('CUSTOMER','SUBSCRIBER') ---Modified condition for ER9681794
AND PR.Jtot_Object1_Code = 'OKX_PARTY'
AND PX.Id1 = TO_NUMBER(PR.Object1_Id1)
AND PX.Id2 = PR.Object1_Id2 ))
AND (Cv_Service_Item_Id IS NULL
OR
HB.Id IN ( SELECT SV.Chr_Id
FROM Okx_System_Items_V XI
,Okc_K_Items IT
,Okc_K_Lines_B SV
WHERE SV.Lse_ID IN (1,14,19) --SV.Chr_Id = HB.Id
AND IT.Cle_Id = SV.Id
AND IT.Object1_Id1 = NVL(Cv_Service_Item_Id,IT.Object1_Id1)
AND IT.Jtot_Object1_Code IN ('OKX_SERVICE','OKX_WARRANTY')
AND XI.Id1 = TO_NUMBER(IT.Object1_Id1)
AND XI.Id2 = IT.Object1_Id2
AND XI.Service_Item_Flag = 'Y'
-- AND XI.Organization_Id = Cn_Organization_Id --Bug# 4735542.
))
AND HB.Scs_Code IN ('SERVICE','WARRANTY','SUBSCRIPTION') ---Modified condition for ER9681794
AND HB.Id > -1
AND HB.Template_YN <> 'Y'
AND HB.Id = HT.Id
AND HT.Language = USERENV('LANG');
Lx_DeDup_Tab.DELETE(Li_TableIdx);
4.Returns only one row- system picked, even if there are multiple rows selected
for the criteria.
*/
PROCEDURE Get_HighImp_CP_Contract
(P_API_Version IN NUMBER
,P_Init_Msg_List IN VARCHAR2
,P_Customer_product_Id IN NUMBER
,X_Return_Status OUT NOCOPY VARCHAR2
,X_Msg_Count out nocopy NUMBER
,X_Msg_Data out nocopy VARCHAR2
,X_Importance_Lvl OUT NOCOPY OKS_ENTITLEMENTS_PUB.High_Imp_level_K_rec)
IS
CURSOR Cur_ImplvlExists is
select count(*) cnt
from oks_cov_types_v
where importance_level is NOT NULL;
select okh.contract_number,
okh.contract_number_modifier,
okh.start_date,
okh.end_date,
cvt.meaning,
cvt.importance_level,
okh.estimated_amount,
okh.sts_code
from OKC_K_HEADERS_ALL_B okh, -- OKC_K_HEADERS_B HDR -- Modified for 12.0 MOAC project (JVARGHES)
okc_k_lines_b cle,
oks_k_lines_b cle_ksl,
oks_k_lines_b okscle_cov,
okc_k_lines_b cle_cvl,
okc_k_items cim,
okc_statuses_v sts,
oks_cov_types_v cvt
where cle.chr_id = okh.id
and okh.sts_code = sts.code
and sts.ste_code = 'ACTIVE'
and cle_ksl.cle_id = cle.id
and okscle_cov.cle_id = cle_ksl.coverage_id
and okscle_cov.coverage_type = cvt.code
and cle_cvl.cle_id = cle.id
and cle_cvl.lse_id in (9,18,25)
and cle_cvl.id = cim.cle_id
and cim.object1_id1 = P_Customer_product_Id
order by cvt.importance_level;
select okh.contract_number,
okh.contract_number_modifier,
okh.start_date,
okh.end_date,
covtyp.meaning,
covtyp.importance_level importance_level,
okh.estimated_amount,
okh.sts_code
from OKC_K_HEADERS_ALL_B okh, -- OKC_K_HEADERS_B okh -- Modified for 12.0 MOAC project (JVARGHES)
okc_k_lines_b cle,
oks_k_lines_b cle_ksl,
oks_k_lines_b okscle_cov,
okc_k_lines_b cle_cvl,
okc_k_items cim,
okc_statuses_v sts,
oks_cov_types_v covtyp
where cle.chr_id = okh.id
and okh.sts_code = sts.code
and sts.ste_code = 'ACTIVE'
and cle_ksl.cle_id = cle.id
and okscle_cov.cle_id = cle_ksl.coverage_id
and okscle_cov.coverage_type = covtyp.code
and cle_cvl.cle_id = cle.id
and cle_cvl.lse_id in (9,18,25)
and cle_cvl.id = cim.cle_id
and cim.object1_id1 = P_Customer_product_Id
and rownum =1;
SELECT Grace_Duration Duration
,Grace_Period TimeUnit
FROM Oks_K_Headers_B OKH
WHERE OKH.chr_Id = Cx_HDR_Id;
,P_Update_Only_Check IN VARCHAR2
,X_Return_Status OUT NOCOPY VARCHAR2
,X_Msg_Count out nocopy NUMBER
,X_Msg_Data out nocopy VARCHAR2
,X_System_Valid OUT NOCOPY VARCHAR2) IS
CURSOR Lx_Csr_SysProd(Cx_System_id IN VARCHAR2) IS
SELECT CSI.instance_id CP_Id
,CSI.System_Id System_Id
FROM CSI_ITEM_INSTANCES CSI
WHERE CSI.System_id = (Cx_System_Id);
CURSOR Check_update_only(p_coverage_id IN Gx_OKS_Id) IS
SELECT 'Y'
FROM okc_k_lines_v bp,
okc_k_items cim,
cs_business_processes cbp
WHERE bp.cle_id = p_coverage_id
AND cim.cle_id = bp.id
AND cbp.business_process_id = cim.object1_id1
AND cim.jtot_object1_code = 'OKX_BUSIPROC'
AND (cbp.service_request_flag = 'Y'
OR cbp.depot_repair_flag = 'Y'
OR cbp.field_service_flag = 'Y')
AND ROWNUM = 1;
l_update_only_check CONSTANT VARCHAR2(1) := nvl(p_update_only_check,'N');
IF l_update_only_check <> 'Y' then
l_out_validate_csi := 'Y';
FOR bp_rec IN Check_update_only(Lx_Ent_Contracts(i).coverage_term_line_id)
LOOP
l_out_validate_csi := 'Y';
IF l_update_only_check <> 'Y' THEN
l_out_validate_csi := 'Y';
FOR bp_rec IN Check_update_only(Lx_Ent_Contracts(i).coverage_term_line_id)
LOOP
l_out_validate_csi := 'Y';
Lx_Sort_Tab.DELETE;
Lx_DeDup_Tab.DELETE(Li_TableIdx);
Lx_Contracts_01.DELETE;
Lx_Contracts_01_Out.DELETE;
SELECT COUNT(*) Pref_Grp_Cnt --CON.OBJECT1_ID1
FROM OKC_K_LINES_B CVL,
OKC_K_LINES_B BPL,
OKC_K_PARTY_ROLES_B CPL,
OKC_CONTACTS CON
WHERE CVL.ID = p_coverage_id
AND BPL.CLE_ID = CVL.ID
AND BPL.LSE_ID in (3,16,21)
AND BPL.ID = CPL.CLE_ID
AND CPL.ID = CON.CPL_ID
and con.dnz_chr_id = p_chr_id
AND CON.JTOT_OBJECT1_CODE = 'OKS_RSCGROUP';
Lx_Contracts_02.DELETE;
Lx_Contracts.DELETE;
SELECT CSI.instance_id CP_Id,
CSI.System_Id System_Id
FROM CSI_ITEM_INSTANCES CSI
WHERE CSI.System_id = (Cx_System_Id);
Lx_Ent_Contracts.DELETE;
Lx_Ent_Contracts2.DELETE;
Lx_Ent_Contracts.DELETE;
Lx_Ent_Contracts.DELETE;
Lx_Ent_Contracts_01.DELETE;
Lx_Ent_Contracts_02.DELETE;
Lx_CustProd_Contracts.DELETE;
Lx_Contracts_Out.DELETE;
Lx_Ent_Contracts.DELETE;
Lx_Ent_Contracts2.DELETE;
Lx_Ent_Contracts.DELETE;
SELECT CVL.Name
,CVL.Item_Description
,CVL.Id Coverage_Line_Id
,CVL.Start_Date CV_Start_Date
,Get_End_Date_Time(CVL.End_Date) Cv_End_Date
,Get_End_Date_Time(CVL.Date_Terminated) CV_Date_Terminated
,KSL.Standard_Cov_YN
FROM OKS_K_LINES_B KSL
,Okc_K_Lines_V CVL
WHERE KSL.Cle_Id = Cx_SrvLine_Id
AND KSL.Coverage_Id = CVL.Id
AND CVL.Lse_Id IN (2,15,20);
SELECT mtl.concatenated_segments Name
,mtl.description Description
FROM mtl_system_items_b_kfv mtl
,Okc_K_Items IT
WHERE IT.Cle_Id = Cx_SrvLine_Id
AND IT.Jtot_Object1_Code IN ('OKX_SERVICE','OKX_WARRANTY')
AND mtl.inventory_item_id = IT.Object1_Id1
AND mtl.organization_id = IT.Object1_Id2
AND mtl.Service_Item_Flag = 'Y';
select lines2.id id,
decode(ksl.Standard_Cov_YN, 'Y',lines.start_date,lines2.start_date) start_date,
decode(ksl.Standard_Cov_YN, 'Y',lines.end_date,lines2.end_date) end_date,
to_number(items.object1_id1) object1_id1,
lines.dnz_chr_id,
ksl.Standard_Cov_YN Standard_Cov_YN
from okc_k_lines_b lines,
oks_k_lines_b KSL,
okc_k_lines_v lines2,
okc_k_items items,
cs_business_processes bp
where lines.id = p_line_id
and lines.lse_id in (1,14,19)
and KSL.cle_id = lines.id
and KSL.Coverage_Id = lines2.cle_Id
and lines2.id = items.cle_id
and lines2.lse_id in (3,16,21)
and items.object1_id1 = bp.business_process_id
and items.object1_id2 = '#'
and items.jtot_object1_code = 'OKX_BUSIPROC'
and bp.service_request_flag =
decode(p_chk_def,'Y',
decode(p_sr_enabled,null,bp.service_request_flag,p_sr_enabled),
bp.service_request_flag)
and bp.depot_repair_flag =
decode(p_chk_def,'Y',
decode(p_dr_enabled,null,bp.depot_repair_flag,p_dr_enabled),
bp.depot_repair_flag)
and bp.field_service_flag =
decode(p_chk_def,'Y',
decode(p_fs_enabled,null,bp.field_service_flag,p_fs_enabled),
bp.field_service_flag);
select hdr.id id,
hdr.start_date start_date,
hdr.end_date end_date
from OKC_K_HEADERS_ALL_B hdr, --,OKC_K_HEADERS_B HDR -- Modified for 12.0 MOAC project (JVARGHES)
okc_k_lines_v lines
where lines.chr_id = hdr.id
and lines.dnz_chr_id = hdr.id
and lines.id = p_line_id;
select lines.id id,
lines2.id bt_line_id,
lines.start_date start_date,
lines.end_date end_date ,
items2.object1_id1 bt_object1_id1,
items2.jtot_object1_code jtot_object1_code,
okslines2.discount_amount, -- rul.rule_information2 rule_information2,
okslines2.discount_percent -- rul.rule_information4 rule_information4
from okc_k_lines_v lines,
okc_k_lines_v lines2,
okc_k_items items2,
oks_k_lines_b okslines2
-- okc_rule_groups_b rgp,
-- okc_rules_b rul
where lines.id = p_txn_grp_line_id
and lines.lse_id in (3,16,21)
and lines2.cle_id = lines.id
and lines2.id = items2.cle_id
and lines2.lse_id in (5,23,59)
and items2.jtot_object1_code = 'OKX_BILLTYPE'
and lines2.id = okslines2.cle_id;
select oksbsch.BT_CLE_ID bt_line_id,
oksbsch.CLE_ID br_line_id,
oksbsch.ID br_schedule_id,
oksbsch.BILL_RATE_CODE bill_rate,
oksbsch.FLAT_RATE flat_rate,
oksbsch.UOM uom,
oksbsch.PERCENT_OVER_LIST_PRICE percent_over_list_price,
oksbsch.START_HOUR start_hour,
oksbsch.START_MINUTE start_minute,
oksbsch.END_HOUR end_hour,
oksbsch.END_MINUTE end_minute,
oksbsch.MONDAY_FLAG monday_flag,
oksbsch.TUESDAY_FLAG tuesday_flag,
oksbsch.WEDNESDAY_FLAG wednesday_flag,
oksbsch.THURSDAY_FLAG thursday_flag,
oksbsch.FRIDAY_FLAG friday_flag,
oksbsch.SATURDAY_FLAG saturday_flag,
oksbsch.SUNDAY_FLAG sunday_flag,
to_number(oksbsch.OBJECT1_ID2) labor_item_org_id,
to_number(oksbsch.OBJECT1_ID1) labor_item_id,
oksbsch.HOLIDAY_YN holiday_yn
from okc_k_lines_v lines,
okc_k_lines_v lines2,
oks_billrate_schedules oksbsch
-- okc_rule_groups_v rgp,
-- okc_rules_b rules
where lines.id = p_bill_type_id
and lines.id = lines2.cle_id
and lines2.id = oksbsch.cle_id;
SELECT HD.Id Contract_Id
,HD.Contract_Number
,HD.Contract_Number_Modifier
,HD.Sts_Code
,HD.Authoring_Org_Id
,HD.Inv_Organization_Id
,HD.End_Date HDR_End_Date --grace period changes
,SV.Id Service_Line_Id
,SV.Start_Date SV_Start_Date
,Get_End_Date_Time(SV.End_Date) SV_End_Date
,Get_End_Date_Time(SV.Date_Terminated) SV_Date_Terminated
,CL.Sts_Code CL_Sts_Code
,CL.Id CovLvl_Line_Id
,CL.Start_Date CL_Start_Date
,Get_End_Date_Time(CL.End_Date) CL_End_Date
,Get_End_Date_Time(CL.Date_Terminated) CL_Date_Terminated
,DECODE(SV.Lse_Id, 14, 'Y', 15, 'Y', 16, 'Y', 17, 'Y', 18, 'Y', 'N') Warranty_Flag
,HD.Scs_Code Scs_Code
,HD.Estimated_Amount Estimated_Amount
,HD.Start_Date HD_Start_Date
,HD.End_Date HD_End_Date
,HD.Date_Terminated HD_Date_Terminated
,HDT.Cognomen Cognomen -- HD.Cognomen Cognomen -- Modified for 12.0 MOAC project (JVARGHES)
,HDT.short_description short_description -- HD.short_description -- Modified for 12.0 MOAC project (JVARGHES)
,HD.currency_code HD_currency_code
,CAT.meaning HD_cat_meaning
,STS.meaning HD_sts_meaning
FROM OKC_K_HEADERS_ALL_B HD -- Modified for 12.0 MOAC project (JVARGHES)
,OKC_K_HEADERS_TL HDT -- Okc_K_Headers_V HD -- Modified for 12.0 MOAC project (JVARGHES)
,Okc_K_Lines_B SV
,Okc_K_Lines_B CL
,okc_subclasses_v CAT
,okc_statuses_v STS
WHERE HD.Id = Cx_Chr_Id
AND HD.Scs_Code IN ('SERVICE','WARRANTY')
AND HDT.ID = HD.ID -- Modified for 12.0 MOAC project (JVARGHES)
AND HDT.LANGUAGE = USERENV('LANG') -- Modified for 12.0 MOAC project (JVARGHES)
AND HD.Sts_code = STS.code
AND HD.Scs_code = CAT.code
AND HD.Id > -1
AND HD.Template_YN <> 'Y'
AND (trunc(HD.Start_Date) between
nvl(trunc(Cx_Start_Date_From),nvl(trunc(Cx_Start_Date_To),trunc(HD.Start_Date))) and
nvl(trunc(Cx_Start_Date_To),nvl(trunc(Cx_Start_Date_From),trunc(HD.Start_Date))))
AND (trunc(HD.End_Date) between
nvl(trunc(Cx_End_Date_From),nvl(trunc(Cx_End_Date_To),trunc(HD.End_Date))) and
nvl(trunc(Cx_End_Date_To),nvl(trunc(Cx_End_Date_From),trunc(HD.End_Date))))
AND ((trunc(HD.Date_Terminated) IS NULL)OR
((trunc(HD.Date_Terminated) between
nvl(trunc(Cx_Date_Terminated_From),nvl(trunc(Cx_Date_Terminated_To),trunc(HD.Date_Terminated))) and
nvl(trunc(Cx_Date_Terminated_To),nvl(trunc(Cx_Date_Terminated_From),trunc(HD.Date_Terminated))))))
AND HD.Sts_code = nvl(Cx_HD_Sts_Code,HD.Sts_code)
AND SV.Dnz_Chr_Id = HD.Id
AND SV.Cle_Id IS NULL
AND SV.Chr_Id = HD.Id
AND SV.Lse_ID IN (1,14,19)
AND SV.Id = NVL(Cx_SrvLine_Id,SV.Id)
AND CL.Cle_Id = SV.Id
AND CL.Lse_ID IN (7,8,9,10,11,35,18,25)
AND CL.Id = NVL(Cx_CovLvlLine_Id, CL.Id)
AND EXISTS (SELECT '*'
FROM Okc_K_Party_Roles_B PR
WHERE PR.Chr_Id = HD.Id
AND PR.Cle_Id IS NULL
AND PR.Dnz_Chr_Id = HD.Id
AND PR.Object1_Id1 = Cv_Cont_Pty_Id
AND PR.Object1_Id2 = '#'
AND PR.Jtot_Object1_Code = 'OKX_PARTY'
AND PR.RLE_CODE <> 'VENDOR');
SELECT HD.Id Contract_Id
,HD.Contract_Number
,HD.Contract_Number_Modifier
,HD.Sts_Code
,HD.Authoring_Org_Id
,HD.Inv_Organization_Id
,HD.End_Date HDR_End_Date --grace period changes
,SV.Id Service_Line_Id
,SV.Start_Date SV_Start_Date
,Get_End_Date_Time(SV.End_Date) SV_End_Date
,Get_End_Date_Time(SV.Date_Terminated) SV_Date_Terminated
,CL.Sts_Code CL_Sts_Code
,CL.Id CovLvl_Line_Id
,CL.Start_Date CL_Start_Date
,Get_End_Date_Time(CL.End_Date) CL_End_Date
,Get_End_Date_Time(CL.Date_Terminated) CL_Date_Terminated
,DECODE(SV.Lse_Id, 14, 'Y', 15, 'Y', 16, 'Y', 17, 'Y', 18, 'Y', 'N') Warranty_Flag
,HD.Scs_Code Scs_Code
,HD.Estimated_Amount Estimated_Amount
,HD.Start_Date HD_Start_Date
,HD.End_Date HD_End_Date
,HD.Date_Terminated HD_Date_Terminated
,HDT.Cognomen Cognomen -- HD.Cognomen Cognomen -- Modified for 12.0 MOAC project (JVARGHES)
,HDT.short_description short_description -- HD.short_description -- Modified for 12.0 MOAC project (JVARGHES)
,HD.currency_code HD_currency_code
,CAT.meaning HD_cat_meaning
,STS.meaning HD_sts_meaning
FROM OKC_K_HEADERS_ALL_B HD -- Modified for 12.0 MOAC project (JVARGHES)
,OKC_K_HEADERS_TL HDT -- Okc_K_Headers_V HD -- Modified for 12.0 MOAC project (JVARGHES)
,Okc_K_Lines_B SV
,Okc_K_Lines_B CL
,okc_subclasses_v CAT
,okc_statuses_v STS
WHERE HD.Id = Cx_Chr_Id
AND HD.Scs_Code IN ('SERVICE','WARRANTY','SUBSCRIPTION') ----Modified condition for ER9681794
AND HDT.ID = HD.ID -- Modified for 12.0 MOAC project (JVARGHES)
AND HDT.LANGUAGE = USERENV('LANG') -- Modified for 12.0 MOAC project (JVARGHES)
AND HD.Sts_code = STS.code
AND HD.Scs_code = CAT.code
AND HD.Id > -1
AND HD.Template_YN <> 'Y'
AND (trunc(HD.Start_Date) between
nvl(trunc(Cx_Start_Date_From),nvl(trunc(Cx_Start_Date_To),trunc(HD.Start_Date))) and
nvl(trunc(Cx_Start_Date_To),nvl(trunc(Cx_Start_Date_From),trunc(HD.Start_Date))))
AND (trunc(HD.End_Date) between
nvl(trunc(Cx_End_Date_From),nvl(trunc(Cx_End_Date_To),trunc(HD.End_Date))) and
nvl(trunc(Cx_End_Date_To),nvl(trunc(Cx_End_Date_From),trunc(HD.End_Date))))
AND ((trunc(HD.Date_Terminated) IS NULL)OR
((trunc(HD.Date_Terminated) between
nvl(trunc(Cx_Date_Terminated_From),nvl(trunc(Cx_Date_Terminated_To),trunc(HD.Date_Terminated))) and
nvl(trunc(Cx_Date_Terminated_To),nvl(trunc(Cx_Date_Terminated_From),trunc(HD.Date_Terminated))))))
AND HD.Sts_code = nvl(Cx_HD_Sts_Code,HD.Sts_code)
AND SV.Dnz_Chr_Id = HD.Id
AND SV.Cle_Id IS NULL
AND SV.Chr_Id = HD.Id
AND SV.Lse_ID IN (1,14,19)
AND SV.Id = NVL(Cx_SrvLine_Id,SV.Id)
AND CL.Cle_Id = SV.Id
AND CL.Lse_ID IN (7,8,9,10,11,35,18,25)
AND CL.Id = NVL(Cx_CovLvlLine_Id, CL.Id)
AND EXISTS (SELECT '*'
FROM Okc_K_Party_Roles_B PR
WHERE PR.Chr_Id = HD.Id
AND PR.Cle_Id IS NULL
AND PR.Dnz_Chr_Id = HD.Id
AND PR.Object1_Id1 = Cv_Cont_Pty_Id
AND PR.Object1_Id2 = '#'
AND PR.Jtot_Object1_Code = 'OKX_PARTY'
AND PR.RLE_CODE not in ('VENDOR','MERCHANT')); ----Modified condition for ER9681794
SELECT HD.Id Contract_Id
,HD.Contract_Number
,HD.Contract_Number_Modifier
,HD.Sts_Code
,HD.End_Date HDR_End_Date --grace period changes
,HD.Scs_Code Scs_Code
,HD.Estimated_Amount Estimated_Amount
,HD.Start_Date HD_Start_Date
,HD.End_Date HD_End_Date
,HD.Date_Terminated HD_Date_Terminated
,HDT.Cognomen Cognomen -- HD.Cognomen Cognomen -- Modified for 12.0 MOAC project (JVARGHES)
,HDT.short_description short_description -- HD.short_description -- Modified for 12.0 MOAC project (JVARGHES)
,HD.currency_code HD_currency_code
,CAT.meaning HD_cat_meaning
,STS.meaning HD_sts_meaning
FROM OKC_K_HEADERS_ALL_B HD -- Modified for 12.0 MOAC project (JVARGHES)
,OKC_K_HEADERS_TL HDT -- Okc_K_Headers_V HD -- Modified for 12.0 MOAC project (JVARGHES)
,okc_subclasses_v CAT
,okc_statuses_v STS
WHERE HD.Id = Cx_Chr_Id -- Modified for Bug#13840327
AND HD.Scs_Code IN ('SERVICE','WARRANTY')
AND HDT.ID = HD.ID -- Modified for 12.0 MOAC project (JVARGHES)
AND HDT.LANGUAGE = USERENV('LANG') -- Modified for 12.0 MOAC project (JVARGHES)
AND HD.Sts_code = STS.code
AND HD.Scs_code = CAT.code
AND HD.Id > -1
AND HD.Template_YN <> 'Y'
AND (trunc(HD.Start_Date) between
nvl(trunc(Cx_Start_Date_From),nvl(trunc(Cx_Start_Date_To),trunc(HD.Start_Date))) and
nvl(trunc(Cx_Start_Date_To),nvl(trunc(Cx_Start_Date_From),trunc(HD.Start_Date))))
AND (trunc(HD.End_Date) between
nvl(trunc(Cx_End_Date_From),nvl(trunc(Cx_End_Date_To),trunc(HD.End_Date))) and
nvl(trunc(Cx_End_Date_To),nvl(trunc(Cx_End_Date_From),trunc(HD.End_Date))))
AND ((trunc(HD.Date_Terminated) IS NULL)OR
((trunc(HD.Date_Terminated) between
nvl(trunc(Cx_Date_Terminated_From),nvl(trunc(Cx_Date_Terminated_To),trunc(HD.Date_Terminated))) and
nvl(trunc(Cx_Date_Terminated_To),nvl(trunc(Cx_Date_Terminated_From),trunc(HD.Date_Terminated))))))
AND HD.Sts_code = nvl(Cx_HD_Sts_Code,HD.Sts_code)
AND EXISTS (SELECT '*'
FROM Okc_K_Party_Roles_B PR
WHERE PR.Chr_Id = HD.Id
AND PR.Cle_Id IS NULL
AND PR.Dnz_Chr_Id = HD.Id
AND PR.Object1_Id1 = Cv_Cont_Pty_Id
AND PR.Object1_Id2 = '#'
AND PR.Jtot_Object1_Code = 'OKX_PARTY'
AND PR.RLE_CODE <> 'VENDOR');
SELECT HD.Id Contract_Id
,HD.Contract_Number
,HD.Contract_Number_Modifier
,HD.Sts_Code
,HD.End_Date HDR_End_Date --grace period changes
,HD.Scs_Code Scs_Code
,HD.Estimated_Amount Estimated_Amount
,HD.Start_Date HD_Start_Date
,HD.End_Date HD_End_Date
,HD.Date_Terminated HD_Date_Terminated
,HDT.Cognomen Cognomen -- HD.Cognomen Cognomen -- Modified for 12.0 MOAC project (JVARGHES)
,HDT.short_description short_description -- HD.short_description -- Modified for 12.0 MOAC project (JVARGHES)
,HD.currency_code HD_currency_code
,CAT.meaning HD_cat_meaning
,STS.meaning HD_sts_meaning
FROM OKC_K_HEADERS_ALL_B HD -- Modified for 12.0 MOAC project (JVARGHES)
,OKC_K_HEADERS_TL HDT -- Okc_K_Headers_V HD -- Modified for 12.0 MOAC project (JVARGHES)
,okc_subclasses_v CAT
,okc_statuses_v STS
WHERE HD.Id = nvl(Cx_Chr_Id,HD.Id)
AND HD.Scs_Code IN ('SERVICE','WARRANTY','SUBSCRIPTION') ------Modified condition for ER9681794
AND HDT.ID = HD.ID -- Modified for 12.0 MOAC project (JVARGHES)
AND HDT.LANGUAGE = USERENV('LANG') -- Modified for 12.0 MOAC project (JVARGHES)
AND HD.Sts_code = STS.code
AND HD.Scs_code = CAT.code
AND HD.Id > -1
AND HD.Template_YN <> 'Y'
AND (trunc(HD.Start_Date) between
nvl(trunc(Cx_Start_Date_From),nvl(trunc(Cx_Start_Date_To),trunc(HD.Start_Date))) and
nvl(trunc(Cx_Start_Date_To),nvl(trunc(Cx_Start_Date_From),trunc(HD.Start_Date))))
AND (trunc(HD.End_Date) between
nvl(trunc(Cx_End_Date_From),nvl(trunc(Cx_End_Date_To),trunc(HD.End_Date))) and
nvl(trunc(Cx_End_Date_To),nvl(trunc(Cx_End_Date_From),trunc(HD.End_Date))))
AND ((trunc(HD.Date_Terminated) IS NULL)OR
((trunc(HD.Date_Terminated) between
nvl(trunc(Cx_Date_Terminated_From),nvl(trunc(Cx_Date_Terminated_To),trunc(HD.Date_Terminated))) and
nvl(trunc(Cx_Date_Terminated_To),nvl(trunc(Cx_Date_Terminated_From),trunc(HD.Date_Terminated))))))
AND HD.Sts_code = nvl(Cx_HD_Sts_Code,HD.Sts_code)
AND EXISTS (SELECT '*'
FROM Okc_K_Party_Roles_B PR
WHERE PR.Chr_Id = HD.Id
AND PR.Cle_Id IS NULL
AND PR.Dnz_Chr_Id = HD.Id
AND PR.Object1_Id1 = Cv_Cont_Pty_Id
AND PR.Object1_Id2 = '#'
AND PR.Jtot_Object1_Code = 'OKX_PARTY'
AND PR.RLE_CODE not in ('VENDOR','MERCHANT')); ----Modified condition for ER9681794
SELECT HD.Id Contract_Id
,HD.Contract_Number
,HD.Contract_Number_Modifier
,HD.Sts_Code
,HD.End_Date HDR_End_Date --grace period changes
,HD.Scs_Code Scs_Code
,HD.Estimated_Amount Estimated_Amount
,HD.Start_Date HD_Start_Date
,HD.End_Date HD_End_Date
,HD.Date_Terminated HD_Date_Terminated
,HDT.Cognomen Cognomen -- HD.Cognomen Cognomen -- Modified for 12.0 MOAC project (JVARGHES)
,HDT.short_description short_description -- HD.short_description -- Modified for 12.0 MOAC project (JVARGHES)
,HD.currency_code HD_currency_code
,CAT.meaning HD_cat_meaning
,STS.meaning HD_sts_meaning
FROM OKC_K_HEADERS_ALL_B HD -- Modified for 12.0 MOAC project (JVARGHES)
,OKC_K_HEADERS_TL HDT -- Okc_K_Headers_V HD -- Modified for 12.0 MOAC project (JVARGHES)
,okc_subclasses_v CAT
,okc_statuses_v STS
WHERE HD.Scs_Code IN ('SERVICE','WARRANTY')
AND HDT.ID = HD.ID -- Modified for 12.0 MOAC project (JVARGHES)
AND HDT.LANGUAGE = USERENV('LANG') -- Modified for 12.0 MOAC project (JVARGHES)
AND HD.Sts_code = STS.code
AND HD.Scs_code = CAT.code
AND HD.Id > -1
AND HD.Template_YN <> 'Y'
AND (trunc(HD.Start_Date) between
nvl(trunc(Cx_Start_Date_From),nvl(trunc(Cx_Start_Date_To),trunc(HD.Start_Date))) and
nvl(trunc(Cx_Start_Date_To),nvl(trunc(Cx_Start_Date_From),trunc(HD.Start_Date))))
AND (trunc(HD.End_Date) between
nvl(trunc(Cx_End_Date_From),nvl(trunc(Cx_End_Date_To),trunc(HD.End_Date))) and
nvl(trunc(Cx_End_Date_To),nvl(trunc(Cx_End_Date_From),trunc(HD.End_Date))))
AND ((trunc(HD.Date_Terminated) IS NULL)OR
((trunc(HD.Date_Terminated) between
nvl(trunc(Cx_Date_Terminated_From),nvl(trunc(Cx_Date_Terminated_To),trunc(HD.Date_Terminated))) and
nvl(trunc(Cx_Date_Terminated_To),nvl(trunc(Cx_Date_Terminated_From),trunc(HD.Date_Terminated))))))
AND HD.Sts_code = nvl(Cx_HD_Sts_Code,HD.Sts_code)
AND EXISTS (SELECT '*'
FROM Okc_K_Party_Roles_B PR
WHERE PR.Chr_Id = HD.Id
AND PR.Cle_Id IS NULL
AND PR.Dnz_Chr_Id = HD.Id
AND PR.Object1_Id1 = Cv_Cont_Pty_Id
AND PR.Object1_Id2 = '#'
AND PR.Jtot_Object1_Code = 'OKX_PARTY'
AND PR.RLE_CODE <> 'VENDOR' );
Lx_Contracts_02_Val.DELETE;
Lx_Contracts_02_Val.DELETE;
Lx_Contracts_Temp.DELETE;
Lx_Contracts.DELETE;
Lx_Contracts_Out.DELETE;
Lx_Contracts_Prev.DELETE;
Lx_Contracts_Prev.DELETE;
Lx_Contracts.DELETE;
Lx_Contracts_Out.DELETE;
Lx_Contracts_Out.DELETE;
Lx_Contracts_02.DELETE;
Lx_Contracts_Out.DELETE;
Lx_Contracts_02.DELETE;
select end_date
from OKC_K_HEADERS_ALL_B -- OKC_K_HEADERS_B -- Modified for 12.0 MOAC project (JVARGHES)
where id = p_contract_id;
SELECT service_po_number,service_po_required
FROM OKS_K_Headers_B CHR
WHERE CHR.chr_Id = Cx_CHR_Id;
SELECT Grace_Duration Duration
,Grace_Period TimeUnit
FROM Oks_K_Headers_B OKH
WHERE OKH.chr_Id = Cx_HDR_Id;
SELECT hdr.AUTHORING_ORG_ID, hdr.INV_ORGANIZATION_ID
FROM OKC_K_HEADERS_ALL_B hdr, OKC_K_LINES_B lines
WHERE lines.id = l_contract_line_id
AND lines.chr_id = hdr.id ;
SELECT lines1.chr_id chr_id,
lines2.id bp_line_id,
lines2.start_date start_date,
lines2.end_date end_date,
items.object1_id1
FROM OKC_K_LINES_B lines1,
OKC_K_LINES_B lines2,
OKC_K_LINES_B lines3,
OKC_K_ITEMS items
WHERE lines1.id = p_contract_line_id
AND lines2.cle_id = lines1.id
AND lines2.lse_id IN (2,15,20)
AND lines3.cle_id = lines2.id
AND lines3.lse_id IN (3,16,21)
AND items.cle_id = lines3.id
AND items.dnz_chr_id = lines3.dnz_chr_id
AND items.jtot_object1_code = 'OKX_BUSIPROC'
AND items.object1_id1 = p_busiproc_id
and items.object1_id2 = '#' -- new where clause added to address performance bug 3755019
AND trunc(p_effective_date) BETWEEN NVL(lines3.start_date, trunc(sysdate))
AND NVL(lines3.end_date, trunc(sysdate)) ;
SELECT /*+ ordered use_nl(lines1,KSL,lines3,items) index(items okc_k_items_n1) */ -- Bug Fix:5694209
decode(KSL.Standard_Cov_YN,'Y', lines1.start_date,lines3.start_date) start_date
,decode(KSL.Standard_Cov_YN,'Y', lines1.end_date,lines3.end_date) end_date
, lines3.id bpl_id
, ksl.Standard_Cov_YN Standard_Cov_YN
FROM OKC_K_LINES_B lines1,
OKS_K_LINES_B KSL,
OKC_K_LINES_B lines3,
OKC_K_ITEMS items
WHERE lines1.id = p_contract_line_id
AND lines1.lse_id IN (1,14,19)
and ksl.cle_id = lines1.id
AND lines3.cle_id = KSL.Coverage_Id
AND lines3.lse_id IN (3,16,21)
AND items.cle_id = lines3.id
AND items.dnz_chr_id = lines3.dnz_chr_id
AND items.jtot_object1_code = 'OKX_BUSIPROC'
AND items.object1_id1 = p_busiproc_id
and items.object1_id2 = '#' -- new where clause added to address performance bug 3755019
AND ROWNUM <= 1;
SELECT 'X'
FROM OKC_K_LINES_B lines1,
OKC_K_LINES_B lines2,
OKC_K_ITEMS items
WHERE lines1.id = l_contract_line_id
AND lines2.cle_id = lines1.id
AND lines2.dnz_chr_id = lines1.chr_id
AND items.cle_id = lines2.id
AND items.object1_id1 = l_object1_id1
AND items.object1_id2 = l_object1_id2
AND items.jtot_object1_code = l_object_code ;
LX_PRODUCT_COVLEVELS.DELETE;
LX_ITEM_COVLEVELS.DELETE;
LX_SYSTEM_COVLEVELS.DELETE;
LX_CUST_COVLEVELS.DELETE;
LX_SITE_COVLEVELS.DELETE;
LX_PARTY_COVLEVELS.DELETE;
SELECT
DECODE(Cv_Day_of_Week
,TO_CHAR(SYSDATE, 'DY','NLS_DATE_LANGUAGE = AMERICAN'),TO_CHAR(SYSDATE, 'DY')
,TO_CHAR(SYSDATE+1,'DY','NLS_DATE_LANGUAGE = AMERICAN'),TO_CHAR(SYSDATE+1,'DY')
,TO_CHAR(SYSDATE+2,'DY','NLS_DATE_LANGUAGE = AMERICAN'),TO_CHAR(SYSDATE+2,'DY')
,TO_CHAR(SYSDATE+3,'DY','NLS_DATE_LANGUAGE = AMERICAN'),TO_CHAR(SYSDATE+3,'DY')
,TO_CHAR(SYSDATE+4,'DY','NLS_DATE_LANGUAGE = AMERICAN'),TO_CHAR(SYSDATE+4,'DY')
,TO_CHAR(SYSDATE+5,'DY','NLS_DATE_LANGUAGE = AMERICAN'),TO_CHAR(SYSDATE+5,'DY')
,TO_CHAR(SYSDATE+6,'DY','NLS_DATE_LANGUAGE = AMERICAN'),TO_CHAR(SYSDATE+6,'DY')
,'-1')
FROM DUAL;
SELECT /*+ leading(b) use_nl(b oksb) */ b.Id Id, -- 11.5.10 rule rearchitecture changes
b.renewal_type_code, -- Added by Jyothi for perf bug:4991724
oksb.electronic_renewal_flag -- Added by Jyothi for perf bug:4991724
FROM OKC_K_HEADERS_ALL_B B, --Okc_K_Headers_B B, /*Bug:6767455*/
oks_k_headers_b oksb -- 11.5.10 rule rearchitecture changes
WHERE b.id = oksb.chr_id -- 11.5.10 rule rearchitecture changes
AND B.Start_Date BETWEEN Cv_Start_Date_From AND Cv_Start_Date_to
AND B.End_Date BETWEEN Cv_End_Date_From AND Cv_End_Date_To
AND B.Date_Terminated BETWEEN Cv_Date_Terminate_From AND Cv_Date_Terminate_To
AND sts_code = nvl(Cv_STATUS,B.Sts_Code)
and authoring_org_id = nvl(cv_authoring_org_id,authoring_org_id); -- multi org security check
SELECT /*+ leading(b) use_nl(b oksb) */ b.Id Id, -- 11.5.10 rule rearchitecture changes
b.renewal_type_code, -- Added by Jyothi for perf bug:4991724
oksb.electronic_renewal_flag -- Added by Jyothi for perf bug:4991724
FROM OKC_K_HEADERS_ALL_B B, -- OKC_K_HEADERS_B B -- Modified for 12.0 MOAC project /*Bug:6767455*/
oks_k_headers_b oksb -- 11.5.10 rule rearchitecture changes
WHERE b.id = oksb.chr_id -- 11.5.10 rule rearchitecture changes
AND B.Start_Date BETWEEN Cv_Start_Date_From AND Cv_Start_Date_to
AND B.End_Date BETWEEN Cv_End_Date_From AND Cv_End_Date_To
AND sts_code = nvl(Cv_STATUS,B.Sts_Code)
and authoring_org_id = nvl(cv_authoring_org_id,authoring_org_id); -- multi org security check
SELECT /*+ leading(b) use_nl(b oksb) */
b.Id Id, -- 11.5.10 rule rearchitecture changes
b.renewal_type_code, -- Added by Jvorugan for perf bug:4991724
oksb.electronic_renewal_flag -- Added by Jvorugan for perf bug:4991724
FROM OKC_K_HEADERS_ALL_B B, -- OKC_K_HEADERS_B B -- Modified for 12.0 MOAC project (JVARGHES)
oks_k_headers_b oksb -- 11.5.10 rule rearchitecture changes
WHERE B.Id = Cv_Contract_Id --B.Id --nvl(Cv_Contract_Id,B.Id) /*Added for Bug:6767455*/
and Contract_Number = nvl(Cv_Contract_Num,Contract_Number)
--AND nvl(Contract_Number_Modifier,-99) = nvl(Cv_Contract_Num_Modifier,nvl(Contract_Number_Modifier,-99))
AND ( (Cv_Contract_Num_Modifier IS NULL)
OR (Contract_Number_Modifier = Cv_Contract_Num_Modifier))
and b.id = oksb.chr_id -- 11.5.10 rule rearchitecture changes
--- truncating the dates to remove accidental existence of time components in dates.
/*Modified for Bug:6767455*/
/* AND trunc(B.Start_Date) >= nvl(trunc(Cv_Start_Date_From),trunc(B.Start_Date))
and trunc(B.Start_Date) <= nvl(trunc(Cv_Start_Date_to),trunc(B.Start_Date))
AND trunc(B.End_Date) >= nvl(trunc(Cv_End_Date_From),trunc(B.End_Date))
AND trunc(B.End_Date) <= nvl(trunc(Cv_End_Date_To),trunc(B.End_Date))
AND nvl(trunc(B.Date_Terminated),nvl(trunc(Cv_Date_Terminate_From)-1,nvl(trunc(Cv_Date_Terminate_To)+1,trunc(sysdate)))) >=
nvl(trunc(Cv_Date_Terminate_From),nvl(trunc(B.Date_Terminated),trunc(sysdate)))
AND nvl(trunc(B.Date_Terminated),nvl(trunc(Cv_Date_Terminate_From)-1,nvl(trunc(Cv_Date_Terminate_To)+1,trunc(sysdate)))) <=
nvl(trunc(Cv_Date_Terminate_To),nvl(trunc(B.Date_Terminated),trunc(sysdate)))*/
AND B.Start_Date >= NVL(trunc(Cv_Start_Date_From),B.Start_Date)
AND B.Start_Date <= NVL((trunc(Cv_Start_Date_to)+0.99998843),B.Start_Date)
AND B.End_Date >= NVL(trunc(Cv_End_Date_From),B.End_Date)
AND B.End_Date <= NVL((trunc(Cv_End_Date_To)+0.99998843),B.End_Date)
AND ((Cv_Date_Terminate_From IS NULL) OR
(B.Date_Terminated >= trunc(Cv_Date_Terminate_From)))
AND ((Cv_Date_Terminate_To IS NULL) OR
(B.Date_Terminated <= (trunc(Cv_Date_Terminate_To)+0.99998843)))
/*Modified for Bug:6767455*/
AND Sts_Code = nvl(Cv_STATUS,B.Sts_Code)
and authoring_org_id = nvl(cv_authoring_org_id,authoring_org_id) -- multi org security check
AND (Cv_Cont_Pty_Id IS NULL
OR
EXISTS
(SELECT 'x'
FROM Okc_K_Party_Roles_B PR
WHERE pr.Cle_Id IS NULL
AND PR.Dnz_Chr_Id = b.Id
-- AND to_number(PR.Object1_Id1) = nvl(Cv_Cont_Pty_Id,to_number(PR.Object1_Id1))
AND PR.Object1_Id1 = TO_CHAR(Cv_Cont_Pty_Id)
AND PR.Object1_Id2 = '#'
AND PR.Jtot_Object1_Code = 'OKX_PARTY'
AND PR.RLE_CODE <> 'VENDOR'))
-- where clause for contract group added dtd Dec 17th, 2003
AND (Cv_contract_grp_id is null
OR
EXISTS (SELECT 'x'
FROM Okc_K_grpings grpng
WHERE grpng.included_chr_id = b.id
and grpng.cgp_parent_id = Cv_contract_grp_id ));
SELECT /*+ leading(b) use_nl(b oksb) */
b.Id Id, -- 11.5.10 rule rearchitecture changes
b.renewal_type_code, -- Added by Jvorugan for perf bug:4991724
oksb.electronic_renewal_flag -- Added by Jvorugan for perf bug:4991724
FROM OKC_K_HEADERS_ALL_B B, -- OKC_K_HEADERS_B B -- Modified for 12.0 MOAC project (JVARGHES)
oks_k_headers_b oksb -- 11.5.10 rule rearchitecture changes
WHERE B.Id = Cv_Contract_Id --B.Id --nvl(Cv_Contract_Id,B.Id) /*Added for Bug:6767455*/
and Contract_Number = nvl(Cv_Contract_Num,Contract_Number)
AND ( (Cv_Contract_Num_Modifier IS NULL)
OR (Contract_Number_Modifier = Cv_Contract_Num_Modifier))
and b.id = oksb.chr_id -- 11.5.10 rule rearchitecture changes
AND B.Start_Date >= NVL(trunc(Cv_Start_Date_From),B.Start_Date)
AND B.Start_Date <= NVL((trunc(Cv_Start_Date_to)+0.99998843),B.Start_Date)
AND B.End_Date >= NVL(trunc(Cv_End_Date_From),B.End_Date)
AND B.End_Date <= NVL((trunc(Cv_End_Date_To)+0.99998843),B.End_Date)
AND ((Cv_Date_Terminate_From IS NULL) OR
(B.Date_Terminated >= trunc(Cv_Date_Terminate_From)))
AND ((Cv_Date_Terminate_To IS NULL) OR
(B.Date_Terminated <= (trunc(Cv_Date_Terminate_To)+0.99998843)))
AND Sts_Code = nvl(Cv_STATUS,B.Sts_Code)
and authoring_org_id = nvl(cv_authoring_org_id,authoring_org_id) -- multi org security check
AND (Cv_Cont_Pty_Id IS NULL
OR
EXISTS
(SELECT 'x'
FROM Okc_K_Party_Roles_B PR
WHERE pr.Cle_Id IS NULL
AND PR.Dnz_Chr_Id = b.Id
AND PR.Object1_Id1 = TO_CHAR(Cv_Cont_Pty_Id)
AND PR.Object1_Id2 = '#'
AND PR.Jtot_Object1_Code = 'OKX_PARTY'
AND PR.RLE_CODE not in ('VENDOR','MERCHANT'))) ---Modified condition for ER9681794
AND (Cv_contract_grp_id is null
OR
EXISTS (SELECT 'x'
FROM Okc_K_grpings grpng
WHERE grpng.included_chr_id = b.id
and grpng.cgp_parent_id = Cv_contract_grp_id ));
SELECT /*+ leading(pr) use_nl(pr b oksb) */ b.Id Id, -- 11.5.10 rule rearchitecture changes
b.renewal_type_code, -- Added by Jyothi for perf bug:4991724
oksb.electronic_renewal_flag -- Added by Jyothi for perf bug:4991724
FROM OKC_K_HEADERS_ALL_B B, -- OKC_K_HEADERS_B B -- Modified for 12.0 MOAC project (JVARGHES)
oks_k_headers_b oksb, -- 11.5.10 rule rearchitecture changes
Okc_K_Party_Roles_B PR
WHERE PR.Object1_Id1 = TO_CHAR(Cv_Cont_Pty_Id)
AND PR.Object1_Id2 = '#'
AND PR.Jtot_Object1_Code = 'OKX_PARTY'
AND PR.RLE_CODE <> 'VENDOR'
AND PR.Cle_Id IS NULL
AND B.ID = PR.DNZ_CHR_ID
AND B.ID = PR.CHR_ID
-- AND Contract_Number = nvl(Cv_Contract_Num,Contract_Number)
-- AND ( (Cv_Contract_Num_Modifier IS NULL)
-- OR (Contract_Number_Modifier = Cv_Contract_Num_Modifier))
AND B.id = oksb.chr_id -- 11.5.10 rule rearchitecture changes
AND B.Start_Date >= NVL(trunc(Cv_Start_Date_From),B.Start_Date)
AND B.Start_Date <= NVL((trunc(Cv_Start_Date_to)+0.99998843),B.Start_Date)
AND B.End_Date >= NVL(trunc(Cv_End_Date_From),B.End_Date)
AND B.End_Date <= NVL((trunc(Cv_End_Date_To)+0.99998843),B.End_Date)
AND ((Cv_Date_Terminate_From IS NULL) OR
(B.Date_Terminated >= trunc(Cv_Date_Terminate_From)))
AND ((Cv_Date_Terminate_To IS NULL) OR
(B.Date_Terminated <= (trunc(Cv_Date_Terminate_To)+0.99998843)))
AND sts_code = nvl(Cv_STATUS,B.Sts_Code)
AND authoring_org_id = nvl(cv_authoring_org_id,authoring_org_id); -- multi org security check
SELECT /*+ leading(pr) use_nl(pr b oksb) */ b.Id Id, -- 11.5.10 rule rearchitecture changes
b.renewal_type_code, -- Added by Jyothi for perf bug:4991724
oksb.electronic_renewal_flag -- Added by Jyothi for perf bug:4991724
FROM OKC_K_HEADERS_ALL_B B, -- OKC_K_HEADERS_B B -- Modified for 12.0 MOAC project (JVARGHES)
oks_k_headers_b oksb, -- 11.5.10 rule rearchitecture changes
Okc_K_Party_Roles_B PR
WHERE PR.Object1_Id1 = TO_CHAR(Cv_Cont_Pty_Id)
AND PR.Object1_Id2 = '#'
AND PR.Jtot_Object1_Code = 'OKX_PARTY'
AND PR.RLE_CODE not in ('VENDOR','MERCHANT') ---Modified condition for ER9681794
AND PR.Cle_Id IS NULL
AND B.ID = PR.DNZ_CHR_ID
AND B.ID = PR.CHR_ID
-- AND Contract_Number = nvl(Cv_Contract_Num,Contract_Number)
-- AND ( (Cv_Contract_Num_Modifier IS NULL)
-- OR (Contract_Number_Modifier = Cv_Contract_Num_Modifier))
AND B.id = oksb.chr_id -- 11.5.10 rule rearchitecture changes
AND B.Start_Date >= NVL(trunc(Cv_Start_Date_From),B.Start_Date)
AND B.Start_Date <= NVL((trunc(Cv_Start_Date_to)+0.99998843),B.Start_Date)
AND B.End_Date >= NVL(trunc(Cv_End_Date_From),B.End_Date)
AND B.End_Date <= NVL((trunc(Cv_End_Date_To)+0.99998843),B.End_Date)
AND ((Cv_Date_Terminate_From IS NULL) OR
(B.Date_Terminated >= trunc(Cv_Date_Terminate_From)))
AND ((Cv_Date_Terminate_To IS NULL) OR
(B.Date_Terminated <= (trunc(Cv_Date_Terminate_To)+0.99998843)))
AND sts_code = nvl(Cv_STATUS,B.Sts_Code)
AND authoring_org_id = nvl(cv_authoring_org_id,authoring_org_id); -- multi org security check
SELECT /*+ leading(grpng) use_nl(grpng b oksb) */ b.Id Id, -- 11.5.10 rule rearchitecture changes
b.renewal_type_code, -- Added by Jyothi for perf bug:4991724
oksb.electronic_renewal_flag -- Added by Jyothi for perf bug:4991724
FROM OKC_K_HEADERS_ALL_B B, -- OKC_K_HEADERS_B B -- Modified for 12.0 MOAC project (JVARGHES)
oks_k_headers_b oksb, -- 11.5.10 rule rearchitecture changes
Okc_K_grpings grpng
WHERE grpng.cgp_parent_id = Cv_contract_grp_id
AND B.ID = grpng.included_chr_id
-- AND Contract_Number = nvl(Cv_Contract_Num,Contract_Number)
-- AND ( (Cv_Contract_Num_Modifier IS NULL)
-- OR (Contract_Number_Modifier = Cv_Contract_Num_Modifier))
AND B.id = oksb.chr_id -- 11.5.10 rule rearchitecture changes
AND B.Start_Date >= NVL(trunc(Cv_Start_Date_From),B.Start_Date)
AND B.Start_Date <= NVL((trunc(Cv_Start_Date_to)+0.99998843),B.Start_Date)
AND B.End_Date >= NVL(trunc(Cv_End_Date_From),B.End_Date)
AND B.End_Date <= NVL((trunc(Cv_End_Date_To)+0.99998843),B.End_Date)
AND ((Cv_Date_Terminate_From IS NULL) OR
(B.Date_Terminated >= trunc(Cv_Date_Terminate_From)))
AND ((Cv_Date_Terminate_To IS NULL) OR
(B.Date_Terminated <= (trunc(Cv_Date_Terminate_To)+0.99998843)))
AND sts_code = nvl(Cv_STATUS,B.Sts_Code)
AND authoring_org_id = nvl(cv_authoring_org_id,authoring_org_id); -- multi org security check
SELECT /*+ ordered use_nl(pr grpng b oksb) */ b.Id Id, -- 11.5.10 rule rearchitecture changes
b.renewal_type_code, -- Added by Jyothi for perf bug:4991724
oksb.electronic_renewal_flag -- Added by Jyothi for perf bug:4991724
FROM Okc_K_Party_Roles_B PR,
Okc_K_grpings grpng,
OKC_K_HEADERS_ALL_B B, -- OKC_K_HEADERS_B B -- Modified for 12.0 MOAC project (JVARGHES)
oks_k_headers_b oksb -- 11.5.10 rule rearchitecture changes
WHERE PR.Object1_Id1 = TO_CHAR(Cv_Cont_Pty_Id)
AND PR.Object1_Id2 = '#'
AND PR.Jtot_Object1_Code = 'OKX_PARTY'
AND PR.RLE_CODE <> 'VENDOR'
AND PR.Cle_Id IS NULL
AND B.ID = PR.DNZ_CHR_ID
AND B.ID = PR.CHR_ID
-- AND Contract_Number = nvl(Cv_Contract_Num,Contract_Number)
-- AND ( (Cv_Contract_Num_Modifier IS NULL)
-- OR (Contract_Number_Modifier = Cv_Contract_Num_Modifier))
AND B.id = oksb.chr_id -- 11.5.10 rule rearchitecture changes
AND B.Start_Date >= NVL(trunc(Cv_Start_Date_From),B.Start_Date)
AND B.Start_Date <= NVL((trunc(Cv_Start_Date_to)+0.99998843),B.Start_Date)
AND B.End_Date >= NVL(trunc(Cv_End_Date_From),B.End_Date)
AND B.End_Date <= NVL((trunc(Cv_End_Date_To)+0.99998843),B.End_Date)
AND ((Cv_Date_Terminate_From IS NULL) OR
(B.Date_Terminated >= trunc(Cv_Date_Terminate_From)))
AND ((Cv_Date_Terminate_To IS NULL) OR
(B.Date_Terminated <= (trunc(Cv_Date_Terminate_To)+0.99998843)))
AND sts_code = nvl(Cv_STATUS,B.Sts_Code)
AND authoring_org_id = nvl(cv_authoring_org_id,authoring_org_id) -- multi org security check
AND grpng.included_chr_id = b.ID
AND grpng.included_chr_id = PR.DNZ_CHR_ID
AND grpng.cgp_parent_id = Cv_contract_grp_id;
SELECT /*+ ordered use_nl(pr grpng b oksb) */ b.Id Id, -- 11.5.10 rule rearchitecture changes
b.renewal_type_code, -- Added by Jyothi for perf bug:4991724
oksb.electronic_renewal_flag -- Added by Jyothi for perf bug:4991724
FROM Okc_K_Party_Roles_B PR,
Okc_K_grpings grpng,
OKC_K_HEADERS_ALL_B B, -- OKC_K_HEADERS_B B -- Modified for 12.0 MOAC project (JVARGHES)
oks_k_headers_b oksb -- 11.5.10 rule rearchitecture changes
WHERE PR.Object1_Id1 = TO_CHAR(Cv_Cont_Pty_Id)
AND PR.Object1_Id2 = '#'
AND PR.Jtot_Object1_Code = 'OKX_PARTY'
AND PR.RLE_CODE not in ('VENDOR','MERCHANT') ------Modified condition for ER9681794
AND PR.Cle_Id IS NULL
AND B.ID = PR.DNZ_CHR_ID
AND B.ID = PR.CHR_ID
-- AND Contract_Number = nvl(Cv_Contract_Num,Contract_Number)
-- AND ( (Cv_Contract_Num_Modifier IS NULL)
-- OR (Contract_Number_Modifier = Cv_Contract_Num_Modifier))
AND B.id = oksb.chr_id -- 11.5.10 rule rearchitecture changes
AND B.Start_Date >= NVL(trunc(Cv_Start_Date_From),B.Start_Date)
AND B.Start_Date <= NVL((trunc(Cv_Start_Date_to)+0.99998843),B.Start_Date)
AND B.End_Date >= NVL(trunc(Cv_End_Date_From),B.End_Date)
AND B.End_Date <= NVL((trunc(Cv_End_Date_To)+0.99998843),B.End_Date)
AND ((Cv_Date_Terminate_From IS NULL) OR
(B.Date_Terminated >= trunc(Cv_Date_Terminate_From)))
AND ((Cv_Date_Terminate_To IS NULL) OR
(B.Date_Terminated <= (trunc(Cv_Date_Terminate_To)+0.99998843)))
AND sts_code = nvl(Cv_STATUS,B.Sts_Code)
AND authoring_org_id = nvl(cv_authoring_org_id,authoring_org_id) -- multi org security check
AND grpng.included_chr_id = b.ID
AND grpng.included_chr_id = PR.DNZ_CHR_ID
AND grpng.cgp_parent_id = Cv_contract_grp_id;
SELECT /*+ leadin(b) use_nl(b oksb) */ b.Id Id, -- 11.5.10 rule rearchitecture changes
b.renewal_type_code, -- Added by Jyothi for perf bug:4991724
oksb.electronic_renewal_flag -- Added by Jyothi for perf bug:4991724
FROM OKC_K_HEADERS_ALL_B B, -- OKC_K_HEADERS_B B -- Modified for 12.0 MOAC project (JVARGHES)
oks_k_headers_b oksb -- 11.5.10 rule rearchitecture changes
WHERE Contract_Number = Cv_Contract_Num
-- AND ( (Cv_Contract_Num_Modifier IS NULL)
AND Contract_Number_Modifier = Cv_Contract_Num_Modifier
AND b.id = oksb.chr_id -- 11.5.10 rule rearchitecture changes
--
-- Modified by JVARGHES on 23/May/2006 for fix of bug# 4991724
-- AND trunc(B.Start_Date) >= nvl(trunc(Cv_Start_Date_From),trunc(B.Start_Date)) -- truncating the dates to remove
-- and trunc(B.Start_Date) <= nvl(trunc(Cv_Start_Date_to),trunc(B.Start_Date)) -- accidental existence of time components in dates.
-- AND trunc(B.End_Date) >= nvl(trunc(Cv_End_Date_From),trunc(B.End_Date))
-- AND trunc(B.End_Date) <= nvl(trunc(Cv_End_Date_To),trunc(B.End_Date))
-- AND nvl(trunc(B.Date_Terminated),nvl(trunc(Cv_Date_Terminate_From)-1,nvl(trunc(Cv_Date_Terminate_To)+1,trunc(sysdate)))) >=
-- nvl(trunc(Cv_Date_Terminate_From),nvl(trunc(B.Date_Terminated),trunc(sysdate)))
-- AND nvl(trunc(B.Date_Terminated),nvl(trunc(Cv_Date_Terminate_From)-1,nvl(trunc(Cv_Date_Terminate_To)+1,trunc(sysdate)))) <=
-- nvl(trunc(Cv_Date_Terminate_To),nvl(trunc(B.Date_Terminated),trunc(sysdate)))
AND B.Start_Date >= NVL(trunc(Cv_Start_Date_From),B.Start_Date)
AND B.Start_Date <= NVL((trunc(Cv_Start_Date_to)+0.99998843),B.Start_Date)
AND B.End_Date >= NVL(trunc(Cv_End_Date_From),B.End_Date)
AND B.End_Date <= NVL((trunc(Cv_End_Date_To)+0.99998843),B.End_Date)
AND ((Cv_Date_Terminate_From IS NULL) OR
(B.Date_Terminated >= trunc(Cv_Date_Terminate_From)))
AND ((Cv_Date_Terminate_To IS NULL) OR
(B.Date_Terminated <= (trunc(Cv_Date_Terminate_To)+0.99998843)))
--
-- Modified by JVARGHES on 23/May/2006 for fix of bug# 4991724
--
AND sts_code = nvl(Cv_STATUS,B.Sts_Code)
AND authoring_org_id = nvl(cv_authoring_org_id,authoring_org_id) -- multi org security check
AND (Cv_Cont_Pty_Id IS NULL
OR
EXISTS (SELECT 'x'
FROM Okc_K_Party_Roles_B PR
WHERE pr.Cle_Id IS NULL
AND PR.Dnz_Chr_Id = b.Id
-- AND to_number(PR.Object1_Id1) = nvl(Cv_Cont_Pty_Id,to_number(PR.Object1_Id1))
AND PR.Object1_Id1 = TO_CHAR(Cv_Cont_Pty_Id)
AND PR.Object1_Id2 = '#'
AND PR.Jtot_Object1_Code = 'OKX_PARTY'
AND PR.RLE_CODE <> 'VENDOR'))
AND (Cv_contract_grp_id is null -- Where clause for contract group added dtd Dec 17th, 2003
OR
EXISTS (SELECT 'x'
FROM Okc_K_grpings grpng
WHERE grpng.included_chr_id = b.id
AND grpng.cgp_parent_id = Cv_contract_grp_id ));
SELECT /*+ leadin(b) use_nl(b oksb) */ b.Id Id, -- 11.5.10 rule rearchitecture changes
b.renewal_type_code, -- Added by Jyothi for perf bug:4991724
oksb.electronic_renewal_flag -- Added by Jyothi for perf bug:4991724
FROM OKC_K_HEADERS_ALL_B B, -- OKC_K_HEADERS_B B -- Modified for 12.0 MOAC project (JVARGHES)
oks_k_headers_b oksb -- 11.5.10 rule rearchitecture changes
WHERE Contract_Number = Cv_Contract_Num
-- AND ( (Cv_Contract_Num_Modifier IS NULL)
AND Contract_Number_Modifier = Cv_Contract_Num_Modifier
AND b.id = oksb.chr_id -- 11.5.10 rule rearchitecture changes
--
-- Modified by JVARGHES on 23/May/2006 for fix of bug# 4991724
-- AND trunc(B.Start_Date) >= nvl(trunc(Cv_Start_Date_From),trunc(B.Start_Date)) -- truncating the dates to remove
-- and trunc(B.Start_Date) <= nvl(trunc(Cv_Start_Date_to),trunc(B.Start_Date)) -- accidental existence of time components in dates.
-- AND trunc(B.End_Date) >= nvl(trunc(Cv_End_Date_From),trunc(B.End_Date))
-- AND trunc(B.End_Date) <= nvl(trunc(Cv_End_Date_To),trunc(B.End_Date))
-- AND nvl(trunc(B.Date_Terminated),nvl(trunc(Cv_Date_Terminate_From)-1,nvl(trunc(Cv_Date_Terminate_To)+1,trunc(sysdate)))) >=
-- nvl(trunc(Cv_Date_Terminate_From),nvl(trunc(B.Date_Terminated),trunc(sysdate)))
-- AND nvl(trunc(B.Date_Terminated),nvl(trunc(Cv_Date_Terminate_From)-1,nvl(trunc(Cv_Date_Terminate_To)+1,trunc(sysdate)))) <=
-- nvl(trunc(Cv_Date_Terminate_To),nvl(trunc(B.Date_Terminated),trunc(sysdate)))
AND B.Start_Date >= NVL(trunc(Cv_Start_Date_From),B.Start_Date)
AND B.Start_Date <= NVL((trunc(Cv_Start_Date_to)+0.99998843),B.Start_Date)
AND B.End_Date >= NVL(trunc(Cv_End_Date_From),B.End_Date)
AND B.End_Date <= NVL((trunc(Cv_End_Date_To)+0.99998843),B.End_Date)
AND ((Cv_Date_Terminate_From IS NULL) OR
(B.Date_Terminated >= trunc(Cv_Date_Terminate_From)))
AND ((Cv_Date_Terminate_To IS NULL) OR
(B.Date_Terminated <= (trunc(Cv_Date_Terminate_To)+0.99998843)))
--
-- Modified by JVARGHES on 23/May/2006 for fix of bug# 4991724
--
AND sts_code = nvl(Cv_STATUS,B.Sts_Code)
AND authoring_org_id = nvl(cv_authoring_org_id,authoring_org_id) -- multi org security check
AND (Cv_Cont_Pty_Id IS NULL
OR
EXISTS (SELECT 'x'
FROM Okc_K_Party_Roles_B PR
WHERE pr.Cle_Id IS NULL
AND PR.Dnz_Chr_Id = b.Id
-- AND to_number(PR.Object1_Id1) = nvl(Cv_Cont_Pty_Id,to_number(PR.Object1_Id1))
AND PR.Object1_Id1 = TO_CHAR(Cv_Cont_Pty_Id)
AND PR.Object1_Id2 = '#'
AND PR.Jtot_Object1_Code = 'OKX_PARTY'
AND PR.RLE_CODE not in ('VENDOR','MERCHANT'))) ---Modified condition for ER9681794
AND (Cv_contract_grp_id is null -- Where clause for contract group added dtd Dec 17th, 2003
OR
EXISTS (SELECT 'x'
FROM Okc_K_grpings grpng
WHERE grpng.included_chr_id = b.id
AND grpng.cgp_parent_id = Cv_contract_grp_id ));
SELECT /*+ leadin(b) use_nl(b oksb) */ b.Id Id, -- 11.5.10 rule rearchitecture changes
b.renewal_type_code, -- Added by Jyothi for perf bug:4991724
oksb.electronic_renewal_flag -- Added by Jyothi for perf bug:4991724
FROM OKC_K_HEADERS_ALL_B B, -- OKC_K_HEADERS_B B -- Modified for 12.0 MOAC project (JVARGHES)
oks_k_headers_b oksb -- 11.5.10 rule rearchitecture changes
WHERE Contract_Number = Cv_Contract_Num
/*and Contract_Number = nvl(Cv_Contract_Num,Contract_Number)
AND nvl(Contract_Number_Modifier,-99) = nvl(Cv_Contract_Num_Modifier,nvl(Contract_Number_Modifier,-99))*/
and b.id = oksb.chr_id
--- truncating the dates to remove accidental existence of time components in dates.
/*Commented for Bug:6767455
AND trunc(B.Start_Date) >= nvl(trunc(Cv_Start_Date_From),trunc(B.Start_Date))
and trunc(B.Start_Date) <= nvl(trunc(Cv_Start_Date_to),trunc(B.Start_Date))
AND trunc(B.End_Date) >= nvl(trunc(Cv_End_Date_From),trunc(B.End_Date))
AND trunc(B.End_Date) <= nvl(trunc(Cv_End_Date_To),trunc(B.End_Date))
AND nvl(trunc(B.Date_Terminated),nvl(trunc(Cv_Date_Terminate_From)-1,nvl(trunc(Cv_Date_Terminate_To)+1,trunc(sysdate)))) >=
nvl(trunc(Cv_Date_Terminate_From),nvl(trunc(B.Date_Terminated),trunc(sysdate)))
AND nvl(trunc(B.Date_Terminated),nvl(trunc(Cv_Date_Terminate_From)-1,nvl(trunc(Cv_Date_Terminate_To)+1,trunc(sysdate)))) <=
nvl(trunc(Cv_Date_Terminate_To),nvl(trunc(B.Date_Terminated),trunc(sysdate)))*/
AND B.Start_Date >= NVL(trunc(Cv_Start_Date_From),B.Start_Date)
AND B.Start_Date <= NVL((trunc(Cv_Start_Date_to)+0.99998843),B.Start_Date)
AND B.End_Date >= NVL(trunc(Cv_End_Date_From),B.End_Date)
AND B.End_Date <= NVL((trunc(Cv_End_Date_To)+0.99998843),B.End_Date)
AND ((Cv_Date_Terminate_From IS NULL) OR
(B.Date_Terminated >= trunc(Cv_Date_Terminate_From)))
AND ((Cv_Date_Terminate_To IS NULL) OR
(B.Date_Terminated <= (trunc(Cv_Date_Terminate_To)+0.99998843)))
AND Sts_Code = nvl(Cv_STATUS,B.Sts_Code)
and authoring_org_id = nvl(cv_authoring_org_id,authoring_org_id) -- multi org security check
AND (Cv_Cont_Pty_Id IS NULL
OR
EXISTS
(SELECT 'x'
FROM Okc_K_Party_Roles_B PR
WHERE pr.Cle_Id IS NULL
AND PR.Dnz_Chr_Id = b.Id
--AND to_number(PR.Object1_Id1) = nvl(Cv_Cont_Pty_Id,to_number(PR.Object1_Id1))
AND PR.Object1_Id1 = TO_CHAR(Cv_Cont_Pty_Id)
AND PR.Object1_Id2 = '#'
AND PR.Jtot_Object1_Code = 'OKX_PARTY'
AND PR.RLE_CODE <> 'VENDOR')) ---Modified condition for ER9681794
-- where clause for contract group added dtd Dec 17th, 2003
AND (Cv_contract_grp_id is null
OR
EXISTS (SELECT 'x'
FROM Okc_K_grpings grpng
WHERE grpng.included_chr_id = b.id
and grpng.cgp_parent_id = Cv_contract_grp_id ));
SELECT /*+ leadin(b) use_nl(b oksb) */ b.Id Id, -- 11.5.10 rule rearchitecture changes
b.renewal_type_code, -- Added by Jyothi for perf bug:4991724
oksb.electronic_renewal_flag -- Added by Jyothi for perf bug:4991724
FROM OKC_K_HEADERS_ALL_B B, -- OKC_K_HEADERS_B B -- Modified for 12.0 MOAC project (JVARGHES)
oks_k_headers_b oksb -- 11.5.10 rule rearchitecture changes
WHERE Contract_Number = Cv_Contract_Num
and b.id = oksb.chr_id
AND B.Start_Date >= NVL(trunc(Cv_Start_Date_From),B.Start_Date)
AND B.Start_Date <= NVL((trunc(Cv_Start_Date_to)+0.99998843),B.Start_Date)
AND B.End_Date >= NVL(trunc(Cv_End_Date_From),B.End_Date)
AND B.End_Date <= NVL((trunc(Cv_End_Date_To)+0.99998843),B.End_Date)
AND ((Cv_Date_Terminate_From IS NULL) OR
(B.Date_Terminated >= trunc(Cv_Date_Terminate_From)))
AND ((Cv_Date_Terminate_To IS NULL) OR
(B.Date_Terminated <= (trunc(Cv_Date_Terminate_To)+0.99998843)))
AND Sts_Code = nvl(Cv_STATUS,B.Sts_Code)
and authoring_org_id = nvl(cv_authoring_org_id,authoring_org_id) -- multi org security check
AND (Cv_Cont_Pty_Id IS NULL
OR
EXISTS
(SELECT 'x'
FROM Okc_K_Party_Roles_B PR
WHERE pr.Cle_Id IS NULL
AND PR.Dnz_Chr_Id = b.Id
AND PR.Object1_Id1 = TO_CHAR(Cv_Cont_Pty_Id)
AND PR.Object1_Id2 = '#'
AND PR.Jtot_Object1_Code = 'OKX_PARTY'
AND PR.RLE_CODE not in ('VENDOR','MERCHANT'))) ---Modified condition for ER9681794
AND (Cv_contract_grp_id is null
OR
EXISTS (SELECT 'x'
FROM Okc_K_grpings grpng
WHERE grpng.included_chr_id = b.id
and grpng.cgp_parent_id = Cv_contract_grp_id ));
Lx_DeDup_Tab.DELETE;
SELECT Hd.Id Id,
Hd.Contract_Number Contract_Number,
Hd.Contract_Number_Modifier Contract_Number_Modifier,
Hd.Short_Description Short_Description,
Hd.Start_Date Hd_Start_Date,
Hd.End_Date Hd_End_Date,
hd.scs_code hd_scs_code,
Sv.Id Service_ID,
Sv.Line_Number Line_Number,
--- truncating the dates to remove accidental existence of time components in dates.
trunc(Sv.Start_Date) Start_Date,
trunc(Sv.End_Date) End_Date,
trunc(Sv.Date_Terminated) Date_Terminated,
Sv.Sts_code Sts_code,
SYS.description Name,
--- truncating the dates to remove accidental existence of time components in dates.
trunc(Clv.Start_Date) Item_Start_Date,
trunc(Clv.End_Date) Item_End_Date,
trunc(Clv.Date_Terminated) Item_Date_Terminated,
cov.id cov_line_id,
Cov.Name Cov_Name,
--- truncating the dates to remove accidental existence of time components in dates.
trunc(Cov.Start_Date) Cov_Start_Date,
trunc(Cov.End_Date) Cov_End_Date,
trunc(Cov.Date_Terminated) Cov_Date_Terminated,
Sv.Lse_Id Service_Lse_Id
FROM OKC_K_HEADERS_V HD,
OKC_K_LINES_V Sv,
OKC_K_LINES_V Cov,
oks_k_lines_b okscov, --11.5.10 rule rearchitecture changes
OKC_K_LINES_V Clv,
OKC_K_ITEMS items,
Okx_System_Items_V sys
WHERE Hd.Id = Sv.chr_id
AND Hd.Id = nvl(P_Id,Hd.Id)
and hd.authoring_org_id = nvl(p_authoring_org_id,hd.authoring_org_id) -- multi org security check
AND Sv.lse_id in (1,14,19)
AND Sv.id = items.cle_id
AND Sv.dnz_chr_id = items.dnz_chr_id
AND sys.id1 = to_number(items.object1_id1)
AND sys.id2 = to_number(items.object1_id2)
AND items.dnz_chr_id = Hd.Id
AND to_number(items.object1_id1) = nvl(P_service_Item_id,to_number(items.object1_id1))
AND items.JTOT_OBJECT1_CODE in ('OKX_SERVICE','OKX_WARRANTY')
AND items.object1_id2 = sys.id2 --'204'
AND Sv.Sts_code = nvl(p_Sts_Code,Sv.Sts_code)
--- truncating the dates to remove accidental existence of time components in dates.
AND trunc(sv.Start_Date) >= nvl(trunc(P_Start_Date_From),trunc(sv.Start_Date))
and trunc(sv.Start_Date) <= nvl(trunc(P_Start_Date_to),trunc(sv.Start_Date))
AND trunc(sv.End_Date) >= nvl(trunc(P_End_Date_From),trunc(sv.End_Date))
and trunc(sv.End_Date) <= nvl(trunc(P_End_Date_To),trunc(sv.End_Date))
AND Cov.lse_id in (2,15,20)
AND Cov.cle_id = Sv.id
AND Cov.dnz_chr_Id = Sv.dnz_chr_Id
and okscov.cle_id = cov.id --11.5.10 rule rearchitecture changes
AND clv.id = P_Cle_Id --nvl(P_Cle_Id,clv.id)
AND clv.lse_id in (7,8,9,10,11,18,25,35)
AND clv.dnz_chr_id = Sv.dnz_chr_id
AND clv.dnz_chr_id = Hd.Id
AND clv.cle_id = Sv.Id
and nvl(okscov.coverage_type,'#') = nvl(p_cov_type,nvl(okscov.coverage_type,'#'))--11.5.10 rule rearchitecture changes
and nvl(sv.bill_to_site_use_id,-99) = nvl(p_bill_to,nvl(sv.bill_to_site_use_id,-99))--11.5.10 rule rearchitecture changes
and nvl(sv.ship_to_site_use_id,-99) = nvl(p_ship_to,nvl(sv.ship_to_site_use_id,-99))--11.5.10 rule rearchitecture changes
and nvl(sv.line_renewal_type_code,'#') = nvl(p_renewal_code,nvl(sv.line_renewal_type_code,'#'))--11.5.10 rule rearchitecture changes
order by sv.id;
SELECT /*+ leading(clv) use_nl(clv sv items sys hd cov okscov ) */
Hd.Id Id,
Hd.Contract_Number Contract_Number,
Hd.Contract_Number_Modifier Contract_Number_Modifier,
HdT.Short_Description Short_Description, /*bug 7412576*/
Hd.Start_Date Hd_Start_Date,
Hd.End_Date Hd_End_Date,
hd.scs_code hd_scs_code,
hd.org_id org_id, /*bug:7363217*/
Sv.Id Service_ID,
Sv.Line_Number Line_Number,
--- truncating the dates to remove accidental existence of time components in dates.
trunc(Sv.Start_Date) Start_Date,
trunc(Sv.End_Date) End_Date,
trunc(Sv.Date_Terminated) Date_Terminated,
Sv.Sts_code Sts_code,
SYS.concatenated_segments Name,
--- truncating the dates to remove accidental existence of time components in dates.
trunc(Clv.Start_Date) Item_Start_Date,
trunc(Clv.End_Date) Item_End_Date,
trunc(Clv.Date_Terminated) Item_Date_Terminated,
cov.id cov_line_id,
Cov.Name Cov_Name,
--- truncating the dates to remove accidental existence of time components in dates.
/*trunc(Cov.Start_Date) Cov_Start_Date,
trunc(Cov.End_Date) Cov_End_Date,
trunc(Cov.Date_Terminated) Cov_Date_Terminated,*/ /*bug 7412576*/
trunc(DECODE(KSL.Standard_Cov_YN,'Y',SV.Start_Date,Cov.Start_Date)) Cov_Start_Date,
trunc(DECODE(KSL.Standard_Cov_YN,'Y',SV.END_Date,Cov.END_Date)) Cov_END_Date,
trunc(DECODE(KSL.Standard_Cov_YN,'Y',SV.Date_Terminated,Cov.Date_Terminated)) Cov_Date_Terminated,
Sv.Lse_Id Service_Lse_Id
FROM OKC_K_HEADERS_ALL_B HD,
OKC_K_HEADERS_TL HDT, /*bug 7412576 OKC_K_HEADERS_V HD,*/
OKC_K_LINES_B Sv,
OKC_K_LINES_V Cov,
oks_k_lines_b ksl,
oks_k_lines_B okscov, --11.5.10 rule rearchitecture changes
OKC_K_LINES_B Clv,
OKC_K_ITEMS items,
MTL_SYSTEM_ITEMS_B_KFV sys
WHERE HDT.ID = HD.ID
AND HDT.LANGUAGE = USERENV('LANG')
AND Hd.Id = Sv.chr_id
AND Hd.Id = nvl(P_Id,Hd.Id)
and hd.authoring_org_id = nvl(p_authoring_org_id,hd.authoring_org_id) -- multi org security check
AND Sv.lse_id in (1,14,19)
AND Sv.id = items.cle_id
AND Sv.dnz_chr_id = items.dnz_chr_id
AND sys.inventory_item_id = to_number(items.object1_id1)
AND sys.organization_id = to_number(items.object1_id2)
AND items.dnz_chr_id = Hd.Id
AND items.JTOT_OBJECT1_CODE in ('OKX_SERVICE','OKX_WARRANTY')
-- AND items.object1_id2 = sys.id2 --'204'
AND Sv.Sts_code = nvl(p_Sts_Code,Sv.Sts_code)
--- truncating the dates to remove accidental existence of time components in dates.
-- AND trunc(sv.Start_Date) >= nvl(trunc(P_Start_Date_From),trunc(sv.Start_Date))
-- and trunc(sv.Start_Date) <= nvl(trunc(P_Start_Date_to),trunc(sv.Start_Date))
-- AND trunc(sv.End_Date) >= nvl(trunc(P_End_Date_From),trunc(sv.End_Date))
-- and trunc(sv.End_Date) <= nvl(trunc(P_End_Date_To),trunc(sv.End_Date))
AND sv.Start_Date between P_Start_Date_From and P_Start_Date_to
AND sv.End_Date between P_End_Date_From and P_End_Date_To
AND ksl.cle_id = sv.id
and ksl.Coverage_Id = COV.Id /*bug 7412576*/
AND Cov.lse_id in (2,15,20)
/* AND Cov.cle_id = Sv.id
AND Cov.dnz_chr_Id = Sv.dnz_chr_Id */ /*bug 7412576*/
and okscov.cle_id = cov.id -- 11.5.10 rule rearchitecture changes
AND clv.id = P_Cle_Id -- nvl(P_Cle_Id,clv.id)
AND clv.lse_id in (7,8,9,10,11,18,25,35)
AND clv.dnz_chr_id = Sv.dnz_chr_id
AND clv.dnz_chr_id = Hd.Id
AND clv.cle_id = Sv.Id
and nvl(okscov.coverage_type,'#') = nvl(p_cov_type,nvl(okscov.coverage_type,'#'))--11.5.10 rule rearchitecture changes
-- and nvl(sv.bill_to_site_use_id,-99) = nvl(p_bill_to,nvl(sv.bill_to_site_use_id,-99))--11.5.10 rule rearchitecture changes
-- and nvl(sv.ship_to_site_use_id,-99) = nvl(p_ship_to,nvl(sv.ship_to_site_use_id,-99))--11.5.10 rule rearchitecture changes
and ((p_bill_to IS NULL) OR (sv.bill_to_site_use_id = p_bill_to)) --11.5.10 rule rearchitecture changes
and ((p_ship_to IS NULL) OR (sv.ship_to_site_use_id = p_ship_to))
and nvl(sv.line_renewal_type_code,'#') = nvl(p_renewal_code,nvl(sv.line_renewal_type_code,'#'))--11.5.10 rule rearchitecture changes
order by sv.id;
SELECT /*+ leading(clv) use_nl(clv sv items sys hd cov okscov) index(items okc_k_items_n1)*/
Hd.Id Id,
Hd.Contract_Number Contract_Number,
Hd.Contract_Number_Modifier Contract_Number_Modifier,
HdT.Short_Description Short_Description, -- HdT.Short_Description -- Modified for 12.0 MOAC project (JVARGHES)
Hd.Start_Date Hd_Start_Date,
Hd.End_Date Hd_End_Date,
hd.scs_code hd_scs_code,
hd.org_id org_id, -- Modified for 12.0 MOAC project (JVARGHES)
Sv.Id Service_ID,
Sv.Line_Number Line_Number,
--- truncating the dates to remove accidental existence of time components in dates.
trunc(Sv.Start_Date) Start_Date,
trunc(Sv.End_Date) End_Date,
trunc(Sv.Date_Terminated) Date_Terminated,
Sv.Sts_code Sts_code,
SYS.concatenated_segments Name,
--- truncating the dates to remove accidental existence of time components in dates.
trunc(Clv.Start_Date) Item_Start_Date,
trunc(Clv.End_Date) Item_End_Date,
trunc(Clv.Date_Terminated) Item_Date_Terminated,
cov.id cov_line_id,
Cov.Name Cov_Name,
--- truncating the dates to remove accidental existence of time components in dates.
trunc(DECODE(KSL.Standard_Cov_YN,'Y',SV.Start_Date,Cov.Start_Date)) Cov_Start_Date,
trunc(DECODE(KSL.Standard_Cov_YN,'Y',SV.END_Date,Cov.END_Date)) Cov_END_Date,
trunc(DECODE(KSL.Standard_Cov_YN,'Y',SV.Date_Terminated,Cov.Date_Terminated)) Cov_Date_Terminated,
Sv.Lse_Id Service_Lse_Id
FROM OKC_K_HEADERS_ALL_B HD, -- Modified for 12.0 MOAC project (JVARGHES)
OKC_K_HEADERS_TL HDT, -- Okc_K_Headers_V HD -- Modified for 12.0 MOAC project (JVARGHES)
OKC_K_LINES_B Sv,
OKS_K_LINES_B KSL,
OKC_K_LINES_V Cov,
oks_k_lines_B okscov, --11.5.10 rule rearchitecture changes
OKC_K_LINES_B Clv,
OKC_K_ITEMS items,
MTL_SYSTEM_ITEMS_B_KFV sys
WHERE Hd.Id = Sv.chr_id
AND HDT.ID = HD.ID -- Modified for 12.0 MOAC project (JVARGHES)
AND HDT.LANGUAGE = USERENV('LANG') -- Modified for 12.0 MOAC project (JVARGHES)
AND Hd.Id = nvl(P_Id,Hd.Id)
and hd.authoring_org_id = nvl(p_authoring_org_id,hd.authoring_org_id) -- multi org security check
AND Sv.lse_id in (1,14,19)
AND Sv.id = items.cle_id
AND Sv.dnz_chr_id = items.dnz_chr_id
/*AND sys.id1 = to_number(items.object1_id1)
AND sys.id2 = to_number(items.object1_id2)*/
AND sys.inventory_item_id = to_number(items.object1_id1)
AND sys.organization_id = to_number(items.object1_id2)
AND items.dnz_chr_id = Hd.Id
--AND to_number(items.object1_id1) = nvl(P_service_Item_id,to_number(items.object1_id1))
AND items.object1_id1 = to_number(P_service_Item_id)
AND items.JTOT_OBJECT1_CODE in ('OKX_SERVICE','OKX_WARRANTY')
--AND items.object1_id2 = sys.id2 --'204'
AND Sv.Sts_code = nvl(p_Sts_Code,Sv.Sts_code)
--- truncating the dates to remove accidental existence of time components in dates.
/*AND trunc(sv.Start_Date) >= nvl(trunc(P_Start_Date_From),trunc(sv.Start_Date))
and trunc(sv.Start_Date) <= nvl(trunc(P_Start_Date_to),trunc(sv.Start_Date))
AND trunc(sv.End_Date) >= nvl(trunc(P_End_Date_From),trunc(sv.End_Date))
and trunc(sv.End_Date) <= nvl(trunc(P_End_Date_To),trunc(sv.End_Date))*/
AND sv.Start_Date between P_Start_Date_From and P_Start_Date_to
AND sv.End_Date between P_End_Date_From and P_End_Date_To
AND ksl.cle_id = sv.id
and ksl.Coverage_Id = COV.Id
AND Cov.lse_id in (2,15,20)
and okscov.cle_id = cov.id --11.5.10 rule rearchitecture changes
AND clv.id = P_Cle_Id --nvl(P_Cle_Id,clv.id)
AND clv.lse_id in (7,8,9,10,11,18,25,35)
AND clv.dnz_chr_id = Sv.dnz_chr_id
AND clv.dnz_chr_id = Hd.Id
AND clv.cle_id = Sv.Id
and nvl(okscov.coverage_type,'#') = nvl(p_cov_type,nvl(okscov.coverage_type,'#'))--11.5.10 rule rearchitecture changes
/*and nvl(sv.bill_to_site_use_id,-99) = nvl(p_bill_to,nvl(sv.bill_to_site_use_id,-99))--11.5.10 rule rearchitecture changes
and nvl(sv.ship_to_site_use_id,-99) = nvl(p_ship_to,nvl(sv.ship_to_site_use_id,-99))--11.5.10 rule rearchitecture changes*/
and ((p_bill_to IS NULL) OR (sv.bill_to_site_use_id = p_bill_to)) --11.5.10 rule rearchitecture changes
and ((p_ship_to IS NULL) OR (sv.ship_to_site_use_id = p_ship_to))
and nvl(sv.line_renewal_type_code,'#') = nvl(p_renewal_code,nvl(sv.line_renewal_type_code,'#'))--11.5.10 rule rearchitecture changes
order by sv.id;
SELECT /*+ leading(sv) use_nl(sv items sys hd cov okscov) */
Hd.Id Id,
Hd.Contract_Number Contract_Number,
Hd.Contract_Number_Modifier Contract_Number_Modifier,
HdT.Short_Description Short_Description, /*bug 7412576*/
Hd.Start_Date Hd_Start_Date,
Hd.End_Date Hd_End_Date,
hd.scs_code hd_scs_code,
hd.org_id org_id, /*bug:7363217*/
Sv.Id Service_ID,
Sv.Line_Number Line_Number,
--- truncating the dates to remove accidental existence of time components in dates.
trunc(Sv.Start_Date) Start_Date,
trunc(Sv.End_Date) End_Date,
trunc(Sv.Date_Terminated) Date_Terminated,
Sv.Sts_code Sts_code,
SYS.concatenated_segments Name,
cov.id cov_line_id,
Cov.Name Cov_Name,
--- truncating the dates to remove accidental existence of time components in dates.
/*trunc(Cov.Start_Date) Cov_Start_Date,
trunc(Cov.End_Date) Cov_End_Date,
trunc(Cov.Date_Terminated) Cov_Date_Terminated,*/ /*bug 7412576*/
trunc(DECODE(KSL.Standard_Cov_YN,'Y',SV.Start_Date,Cov.Start_Date)) Cov_Start_Date,
trunc(DECODE(KSL.Standard_Cov_YN,'Y',SV.END_Date,Cov.END_Date)) Cov_END_Date,
trunc(DECODE(KSL.Standard_Cov_YN,'Y',SV.Date_Terminated,Cov.Date_Terminated)) Cov_Date_Terminated,
Sv.Lse_Id Service_Lse_Id
FROM OKC_K_HEADERS_ALL_B HD,
OKC_K_HEADERS_TL HDT, /*OKC_K_HEADERS_V HD,*/ /*bug 7412576*/
OKC_K_LINES_B Sv,
OKS_K_LINES_B KSL,
OKC_K_LINES_V Cov,
oks_k_lines_b okscov,--11.5.10 rule rearchitecture changes
OKC_K_ITEMS items,
MTL_SYSTEM_ITEMS_B_KFV sys
WHERE Hd.Id = Sv.chr_id
AND HDT.ID = HD.ID
AND HDT.LANGUAGE = USERENV('LANG') /*bug 7412576*/
AND Hd.id = SV.DNZ_CHR_ID
AND Hd.Id = nvl(P_Id,Hd.Id)
and hd.authoring_org_id = nvl(p_authoring_org_id,hd.authoring_org_id) -- multi org security check
AND Sv.lse_id in (1,14,19)
AND Sv.id = items.cle_id
AND Sv.dnz_chr_id = items.dnz_chr_id
AND sys.inventory_item_id = to_number(items.object1_id1)
AND sys.organization_id = to_number(items.object1_id2)
AND items.dnz_chr_id = Hd.Id
AND items.JTOT_OBJECT1_CODE in ('OKX_SERVICE','OKX_WARRANTY')
-- AND items.object1_id2 = sys.id2 --'204'
AND Sv.Sts_code = nvl(p_Sts_Code,Sv.Sts_code)
--- truncating the dates to remove accidental existence of time components in dates.
-- AND trunc(sv.Start_Date) >= nvl(trunc(P_Start_Date_From),trunc(sv.Start_Date))
-- and trunc(sv.Start_Date) <= nvl(trunc(P_Start_Date_to),trunc(sv.Start_Date))
-- AND trunc(sv.End_Date) >= nvl(trunc(P_End_Date_From),trunc(sv.End_Date))
-- and trunc(sv.End_Date) <= nvl(trunc(P_End_Date_To),trunc(sv.End_Date))
AND sv.Start_Date between P_Start_Date_From and P_Start_Date_to
AND sv.End_Date between P_End_Date_From and P_End_Date_To
AND ksl.cle_id = sv.id
and ksl.Coverage_Id = COV.Id
AND Cov.lse_id in (2,15,20)
/* AND Cov.cle_id = Sv.id
AND Cov.dnz_chr_Id = Sv.dnz_chr_Id*/ /*bug 7412576*/
and okscov.cle_id = cov.id --11.5.10 rule rearchitecture changes
and nvl(okscov.coverage_type,'#') = nvl(p_cov_type,nvl(okscov.coverage_type,'#'))--11.5.10 rule rearchitecture changes
-- and nvl(sv.bill_to_site_use_id,-99) = nvl(p_bill_to,nvl(sv.bill_to_site_use_id,-99))--11.5.10 rule rearchitecture changes
-- and nvl(sv.ship_to_site_use_id,-99) = nvl(p_ship_to,nvl(sv.ship_to_site_use_id,-99))--11.5.10 rule rearchitecture changes
and ((p_bill_to IS NULL) OR (sv.bill_to_site_use_id = p_bill_to)) --11.5.10 rule rearchitecture changes
and ((p_ship_to IS NULL) OR (sv.ship_to_site_use_id = p_ship_to))
and nvl(sv.line_renewal_type_code,'#') = nvl(p_renewal_code,nvl(sv.line_renewal_type_code,'#'))--11.5.10 rule rearchitecture changes
order by sv.id;
SELECT /*+ leading(hd) use_nl(hd sv items sys cov okscov) */
Hd.Id Id,
Hd.Contract_Number Contract_Number,
Hd.Contract_Number_Modifier Contract_Number_Modifier,
HdT.Short_Description Short_Description, /*bug 7412576*/
Hd.Start_Date Hd_Start_Date,
Hd.End_Date Hd_End_Date,
hd.scs_code hd_scs_code,
hd.org_id org_id, /*bug:7363217*/
Sv.Id Service_ID,
Sv.Line_Number Line_Number,
--- truncating the dates to remove accidental existence of time components in dates.
trunc(Sv.Start_Date) Start_Date,
trunc(Sv.End_Date) End_Date,
trunc(Sv.Date_Terminated) Date_Terminated,
Sv.Sts_code Sts_code,
SYS.concatenated_segments Name,
cov.id cov_line_id,
Cov.Name Cov_Name,
--- truncating the dates to remove accidental existence of time components in dates.
/* trunc(Cov.Start_Date) Cov_Start_Date,
trunc(Cov.End_Date) Cov_End_Date,
trunc(Cov.Date_Terminated) Cov_Date_Terminated,*/ /*bug 7412576*/
trunc(DECODE(KSL.Standard_Cov_YN,'Y',SV.Start_Date,Cov.Start_Date)) Cov_Start_Date,
trunc(DECODE(KSL.Standard_Cov_YN,'Y',SV.END_Date,Cov.END_Date)) Cov_END_Date,
trunc(DECODE(KSL.Standard_Cov_YN,'Y',SV.Date_Terminated,Cov.Date_Terminated)) Cov_Date_Terminated,
Sv.Lse_Id Service_Lse_Id
FROM OKC_K_HEADERS_ALL_B HD,
OKC_K_HEADERS_TL HDT,/*OKC_K_HEADERS_V HD,*/ /*bug 7412576*/
OKC_K_LINES_B Sv,
OKS_K_LINES_B KSL,
OKC_K_LINES_V Cov,
oks_k_lines_b okscov, --11.5.10 rule rearchitecture changes
OKC_K_ITEMS items,
MTL_SYSTEM_ITEMS_B_KFV sys
WHERE Hd.Id = Sv.chr_id
AND HDT.ID = HD.ID
AND HDT.LANGUAGE = USERENV('LANG')
AND Hd.id = SV.DNZ_CHR_ID
AND Hd.Id = P_ID -- nvl(P_Id,Hd.Id)
and hd.authoring_org_id = nvl(p_authoring_org_id,hd.authoring_org_id) -- multi org security check
AND Sv.lse_id in (1,14,19)
AND Sv.id = items.cle_id
AND Sv.dnz_chr_id = items.dnz_chr_id
AND sys.inventory_item_id = to_number(items.object1_id1)
AND sys.organization_id = to_number(items.object1_id2)
AND items.dnz_chr_id = Hd.Id
AND items.JTOT_OBJECT1_CODE in ('OKX_SERVICE','OKX_WARRANTY')
-- AND items.object1_id2 = sys.id2 --'204'
AND Sv.Sts_code = nvl(p_Sts_Code,Sv.Sts_code)
--- truncating the dates to remove accidental existence of time components in dates.
-- AND trunc(sv.Start_Date) >= nvl(trunc(P_Start_Date_From),trunc(sv.Start_Date))
-- and trunc(sv.Start_Date) <= nvl(trunc(P_Start_Date_to),trunc(sv.Start_Date))
-- AND trunc(sv.End_Date) >= nvl(trunc(P_End_Date_From),trunc(sv.End_Date))
-- and trunc(sv.End_Date) <= nvl(trunc(P_End_Date_To),trunc(sv.End_Date))
AND sv.Start_Date between P_Start_Date_From and P_Start_Date_to
AND sv.End_Date between P_End_Date_From and P_End_Date_To
AND ksl.cle_id = sv.id
and ksl.Coverage_Id = COV.Id
AND Cov.lse_id in (2,15,20)
/* AND Cov.cle_id = Sv.id
AND Cov.dnz_chr_Id = Sv.dnz_chr_Id*/ /*bug 7412576*/
and okscov.cle_id = cov.id --11.5.10 rule rearchitecture changes
and nvl(okscov.coverage_type,'#') = nvl(p_cov_type,nvl(okscov.coverage_type,'#'))--11.5.10 rule rearchitecture changes
-- and nvl(sv.bill_to_site_use_id,-99) = nvl(p_bill_to,nvl(sv.bill_to_site_use_id,-99))--11.5.10 rule rearchitecture changes
-- and nvl(sv.ship_to_site_use_id,-99) = nvl(p_ship_to,nvl(sv.ship_to_site_use_id,-99))--11.5.10 rule rearchitecture changes
and ((p_bill_to IS NULL) OR (sv.bill_to_site_use_id = p_bill_to)) --11.5.10 rule rearchitecture changes
and ((p_ship_to IS NULL) OR (sv.ship_to_site_use_id = p_ship_to))
and nvl(sv.line_renewal_type_code,'#') = nvl(p_renewal_code,nvl(sv.line_renewal_type_code,'#'))--11.5.10 rule rearchitecture changes
order by sv.id;
SELECT /*+ leading(hd) use_nl(hd sv items sys cov okscov) */
Hd.Id Id,
Hd.Contract_Number Contract_Number,
Hd.Contract_Number_Modifier Contract_Number_Modifier,
HDT.SHORT_DESCRIPTION SHORT_DESCRIPTION,
Hd.Start_Date Hd_Start_Date,
Hd.End_Date Hd_End_Date,
hd.scs_code hd_scs_code,
hd.org_id org_id,
Sv.Id Service_ID,
Sv.Line_Number Line_Number,
trunc(Sv.Start_Date) Start_Date,
trunc(Sv.End_Date) End_Date,
trunc(Sv.Date_Terminated) Date_Terminated,
Sv.Sts_code Sts_code,
SYS.concatenated_segments Name,
cov.id cov_line_id,
COVTL.NAME COV_NAME,
/* trunc(Cov.Start_Date) Cov_Start_Date,
trunc(Cov.End_Date) Cov_End_Date,
trunc(Cov.Date_Terminated) Cov_Date_Terminated,*/
trunc(DECODE(KSL.Standard_Cov_YN,'Y',SV.Start_Date,Cov.Start_Date)) Cov_Start_Date,
trunc(DECODE(KSL.Standard_Cov_YN,'Y',SV.END_Date,Cov.END_Date)) Cov_END_Date,
trunc(DECODE(KSL.Standard_Cov_YN,'Y',SV.Date_Terminated,Cov.Date_Terminated)) Cov_Date_Terminated,
Sv.Lse_Id Service_Lse_Id
FROM OKC_K_HEADERS_ALL_B HD, /*modified for bug:8700389*/
OKC_K_HEADERS_TL HDT,
OKC_K_LINES_B Sv,
OKS_K_LINES_B KSL,
OKC_K_LINES_B Cov,
OKC_K_LINES_TL COVTL,
oks_k_lines_b okscov, --11.5.10 rule rearchitecture changes
OKC_K_ITEMS items,
MTL_SYSTEM_ITEMS_B_KFV sys
WHERE Hd.Id = Sv.chr_id
AND Hd.id = SV.DNZ_CHR_ID
AND Hd.Id = P_ID -- nvl(P_Id,Hd.Id)
AND HD.ID=HDT.ID
AND HDT.LANGUAGE = USERENV('LANG')
AND Sv.lse_id in (1,14,19)
AND Sv.id = items.cle_id
AND Sv.dnz_chr_id = items.dnz_chr_id
AND sys.inventory_item_id = to_number(items.object1_id1)
AND sys.organization_id = to_number(items.object1_id2)
AND items.dnz_chr_id = Hd.Id
AND items.JTOT_OBJECT1_CODE in ('OKX_SERVICE','OKX_WARRANTY')
AND sv.Start_Date between P_Start_Date_From and P_Start_Date_to
AND sv.End_Date between P_End_Date_From and P_End_Date_To
AND ksl.cle_id = sv.id
AND ksl.Coverage_Id = COV.Id
AND Cov.lse_id in (2,15,20)
/*AND Cov.cle_id = Sv.id
AND Cov.dnz_chr_Id = Sv.dnz_chr_Id*/
AND COVTL.ID=COV.ID
AND COVTL.LANGUAGE = USERENV('LANG')
and okscov.cle_id = cov.id --11.5.10 rule rearchitecture changes
order by sv.id;
SELECT /*+ leading(sv) use_nl(sv items sys hd cov okscov) index(items okc_k_items_n1)*/
Hd.Id Id,
Hd.Contract_Number Contract_Number,
Hd.Contract_Number_Modifier Contract_Number_Modifier,
HdT.Short_Description Short_Description, -- HdT.Short_Description -- Modified for 12.0 MOAC project (JVARGHES)
Hd.Start_Date Hd_Start_Date,
Hd.End_Date Hd_End_Date,
hd.scs_code hd_scs_code,
hd.org_id org_id, -- Modified for 12.0 MOAC project (JVARGHES)
Sv.Id Service_ID,
Sv.Line_Number Line_Number,
--- truncating the dates to remove accidental existence of time components in dates.
trunc(Sv.Start_Date) Start_Date,
trunc(Sv.End_Date) End_Date,
trunc(Sv.Date_Terminated) Date_Terminated,
Sv.Sts_code Sts_code,
SYS.concatenated_segments Name,
cov.id cov_line_id,
Cov.Name Cov_Name,
--- truncating the dates to remove accidental existence of time components in dates.
trunc(DECODE(KSL.Standard_Cov_YN,'Y',SV.Start_Date,Cov.Start_Date)) Cov_Start_Date,
trunc(DECODE(KSL.Standard_Cov_YN,'Y',SV.END_Date,Cov.END_Date)) Cov_END_Date,
trunc(DECODE(KSL.Standard_Cov_YN,'Y',SV.Date_Terminated,Cov.Date_Terminated)) Cov_Date_Terminated,
Sv.Lse_Id Service_Lse_Id
FROM OKC_K_HEADERS_ALL_B HD, -- Modified for 12.0 MOAC project (JVARGHES)
OKC_K_HEADERS_TL HDT, -- Okc_K_Headers_V HD -- Modified for 12.0 MOAC project (JVARGHES)
OKC_K_LINES_B Sv,
OKC_K_LINES_V Cov,
oks_k_lines_b ksl,
oks_k_lines_b okscov, --11.5.10 rule rearchitecture changes
OKC_K_ITEMS items,
MTL_SYSTEM_ITEMS_B_KFV sys
WHERE Hd.Id = Sv.chr_id
AND Hd.id = SV.DNZ_CHR_ID
AND HDT.ID = HD.ID -- Modified for 12.0 MOAC project (JVARGHES)
AND HDT.LANGUAGE = USERENV('LANG') -- Modified for 12.0 MOAC project (JVARGHES)
AND Hd.Id = nvl(P_Id,Hd.Id)
and hd.authoring_org_id = nvl(p_authoring_org_id,hd.authoring_org_id) -- multi org security check
AND Sv.lse_id in (1,14,19)
AND Sv.id = items.cle_id
AND Sv.dnz_chr_id = items.dnz_chr_id
/*AND sys.id1 = to_number(items.object1_id1)
AND sys.id2 = to_number(items.object1_id2)*/ /*Bug:6767455*/
AND sys.inventory_item_id = to_number(items.object1_id1)
AND sys.organization_id = to_number(items.object1_id2)
AND items.dnz_chr_id = Hd.Id
AND items.object1_id1 = to_number(P_service_Item_id)
AND items.JTOT_OBJECT1_CODE in ('OKX_SERVICE','OKX_WARRANTY')
-- AND items.object1_id2 = sys.id2 --'204'
AND Sv.Sts_code = nvl(p_Sts_Code,Sv.Sts_code)
--- truncating the dates to remove accidental existence of time components in dates.
-- AND trunc(sv.Start_Date) >= nvl(trunc(P_Start_Date_From),trunc(sv.Start_Date))
-- and trunc(sv.Start_Date) <= nvl(trunc(P_Start_Date_to),trunc(sv.Start_Date))
-- AND trunc(sv.End_Date) >= nvl(trunc(P_End_Date_From),trunc(sv.End_Date))
-- and trunc(sv.End_Date) <= nvl(trunc(P_End_Date_To),trunc(sv.End_Date))
AND sv.Start_Date between P_Start_Date_From and P_Start_Date_to
AND sv.End_Date between P_End_Date_From and P_End_Date_To
AND ksl.cle_id = sv.id
and ksl.Coverage_Id = COV.Id
AND Cov.lse_id in (2,15,20)
/*AND Cov.cle_id = Sv.id
AND Cov.dnz_chr_Id = Sv.dnz_chr_Id*/ /*bug 7412576*/
and okscov.cle_id = cov.id --11.5.10 rule rearchitecture changes
and nvl(okscov.coverage_type,'#') = nvl(p_cov_type,nvl(okscov.coverage_type,'#'))--11.5.10 rule rearchitecture changes
-- and nvl(sv.bill_to_site_use_id,-99) = nvl(p_bill_to,nvl(sv.bill_to_site_use_id,-99))--11.5.10 rule rearchitecture changes
-- and nvl(sv.ship_to_site_use_id,-99) = nvl(p_ship_to,nvl(sv.ship_to_site_use_id,-99))--11.5.10 rule rearchitecture changes
and ((p_bill_to IS NULL) OR (sv.bill_to_site_use_id = p_bill_to)) --11.5.10 rule rearchitecture changes
and ((p_ship_to IS NULL) OR (sv.ship_to_site_use_id = p_ship_to))
and nvl(sv.line_renewal_type_code,'#') = nvl(p_renewal_code,nvl(sv.line_renewal_type_code,'#'))--11.5.10 rule rearchitecture changes
order by sv.id;
SELECT /*+ leading(hd) use_nl(hd sv items sys cov okscov) index(items okc_k_items_n1)*/
Hd.Id Id,
Hd.Contract_Number Contract_Number,
Hd.Contract_Number_Modifier Contract_Number_Modifier,
HdT.Short_Description Short_Description, /*bug 7412576*/
Hd.Start_Date Hd_Start_Date,
Hd.End_Date Hd_End_Date,
hd.scs_code hd_scs_code,
hd.org_id org_id, /*bug:7363217*/
Sv.Id Service_ID,
Sv.Line_Number Line_Number,
--- truncating the dates to remove accidental existence of time components in dates.
trunc(Sv.Start_Date) Start_Date,
trunc(Sv.End_Date) End_Date,
trunc(Sv.Date_Terminated) Date_Terminated,
Sv.Sts_code Sts_code,
SYS.concatenated_segments Name,
cov.id cov_line_id,
Cov.Name Cov_Name,
--- truncating the dates to remove accidental existence of time components in dates.
/*trunc(Cov.Start_Date) Cov_Start_Date,
trunc(Cov.End_Date) Cov_End_Date,
trunc(Cov.Date_Terminated) Cov_Date_Terminated,*/ /*bug 7412576*/
trunc(DECODE(KSL.Standard_Cov_YN,'Y',SV.Start_Date,Cov.Start_Date)) Cov_Start_Date,
trunc(DECODE(KSL.Standard_Cov_YN,'Y',SV.END_Date,Cov.END_Date)) Cov_END_Date,
trunc(DECODE(KSL.Standard_Cov_YN,'Y',SV.Date_Terminated,Cov.Date_Terminated)) Cov_Date_Terminated,
Sv.Lse_Id Service_Lse_Id
FROM OKC_K_HEADERS_ALL_B HD,
OKC_K_HEADERS_TL HDT, /*OKC_K_HEADERS_V HD,*/ /*bug 7412576*/
OKC_K_LINES_B Sv,
OKC_K_LINES_V Cov,
oks_k_lines_b ksl,
oks_k_lines_b okscov, --11.5.10 rule rearchitecture changes
OKC_K_ITEMS items,
MTL_SYSTEM_ITEMS_B_KFV sys
WHERE Hd.Id = Sv.chr_id
AND HDT.ID = HD.ID
AND HDT.LANGUAGE = USERENV('LANG') /*bug 7412576*/
AND Hd.id = SV.DNZ_CHR_ID
AND Hd.Id = p_id -- nvl(P_Id,Hd.Id)
and hd.authoring_org_id = nvl(p_authoring_org_id,hd.authoring_org_id) -- multi org security check
AND Sv.lse_id in (1,14,19)
AND Sv.id = items.cle_id
AND Sv.dnz_chr_id = items.dnz_chr_id
AND sys.inventory_item_id = to_number(items.object1_id1)
AND sys.organization_id = to_number(items.object1_id2)
AND items.dnz_chr_id = Hd.Id
--AND to_number(items.object1_id1) = nvl(P_service_Item_id,to_number(items.object1_id1))
AND items.object1_id1 = to_number(P_service_Item_id)
AND items.JTOT_OBJECT1_CODE in ('OKX_SERVICE','OKX_WARRANTY')
--AND items.object1_id2 = sys.id2 --'204'
AND Sv.Sts_code = nvl(p_Sts_Code,Sv.Sts_code)
--- truncating the dates to remove accidental existence of time components in dates.
/*AND trunc(sv.Start_Date) >= nvl(trunc(P_Start_Date_From),trunc(sv.Start_Date))
and trunc(sv.Start_Date) <= nvl(trunc(P_Start_Date_to),trunc(sv.Start_Date))
AND trunc(sv.End_Date) >= nvl(trunc(P_End_Date_From),trunc(sv.End_Date))
and trunc(sv.End_Date) <= nvl(trunc(P_End_Date_To),trunc(sv.End_Date))*/
AND sv.Start_Date between P_Start_Date_From and P_Start_Date_to
AND sv.End_Date between P_End_Date_From and P_End_Date_To
AND ksl.cle_id = sv.id
and ksl.Coverage_Id = COV.Id /*commented for bug:6767455*/ /*bug 7412576*/
AND Cov.lse_id in (2,15,20)
/* AND Cov.cle_id = Sv.id
AND Cov.dnz_chr_Id = Sv.dnz_chr_Id*/
and okscov.cle_id = cov.id --11.5.10 rule rearchitecture changes
and nvl(okscov.coverage_type,'#') = nvl(p_cov_type,nvl(okscov.coverage_type,'#'))--11.5.10 rule rearchitecture changes
/*and nvl(sv.bill_to_site_use_id,-99) = nvl(p_bill_to,nvl(sv.bill_to_site_use_id,-99))--11.5.10 rule rearchitecture changes
and nvl(sv.ship_to_site_use_id,-99) = nvl(p_ship_to,nvl(sv.ship_to_site_use_id,-99))--11.5.10 rule rearchitecture changes*/
and ((p_bill_to IS NULL) OR (sv.bill_to_site_use_id = p_bill_to)) --11.5.10 rule rearchitecture changes
and ((p_ship_to IS NULL) OR (sv.ship_to_site_use_id = p_ship_to))
and nvl(sv.line_renewal_type_code,'#') = nvl(p_renewal_code,nvl(sv.line_renewal_type_code,'#'))--11.5.10 rule rearchitecture changes
order by sv.id;
IS SELECT OU.Name
FROM HR_ALL_ORGANIZATION_UNITS_TL OU
WHERE OU.ORGANIZATION_ID = c_Org_Id
AND OU.LANGUAGE = USERENV('LANG');
SELECT Id,NAME
FROM OKC_LINE_STYLES_V
WHERE ID = lse_id;
SELECT CODE,MEANING
FROM OKC_STATUSES_V ;
l_status_tab.DELETE;
Lv_Contracts_02.DELETE;
Lx_Contracts.DELETE;
Lx_Contracts_Prev.DELETE;
Lx_Contracts_Out.DELETE;
Lx_Contracts.DELETE;
Lx_Contracts.DELETE;
Lx_Contracts_Out.DELETE;
Lx_Contracts_02.DELETE;
Lx_Contracts_Out.DELETE;
Lx_Contracts_Out.DELETE;
Lx_Contracts_02.DELETE;
Lx_Contracts_Out.DELETE;