[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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
,OKS_ENTITLEMENTS_PVT.Get_End_Date_Time(SV.End_Date) SV_End_Date
,OKS_ENTITLEMENTS_PVT.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
,OKS_ENTITLEMENTS_PVT.Get_End_Date_Time(CL.End_Date) CL_End_Date
,OKS_ENTITLEMENTS_PVT.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
,okscov.pm_program_id PM_Program_Id --rul.object1_id1 PM_Program_Id
,okscov.pm_sch_exists_yn PM_Schedule_Exists --rul.rule_information2 PM_Schedule_Exists
FROM Okc_K_Headers_B HD
,Okc_K_Lines_B SV
,Okc_K_Lines_B CL
,okc_k_lines_v cov
-- ,okc_rule_groups_b rgp -- 11.5.10 rule rearchitecture changes
-- ,okc_rules_b rul -- 11.5.10 rule rearchitecture changes
,oks_k_lines_b okscov -- 11.5.10 rule rearchitecture changes
WHERE sv.id = cov.cle_id
-- rule rearchitecture changes
and cov.id = okscov.cle_id
and okscov.pm_program_id is not null
-- AND cov.id = rgp.cle_id
-- AND rgp.id = rul.rgp_id
-- AND cov.dnz_chr_id = rgp.dnz_chr_id
-- AND rul.rule_information_category = 'PMP'
-- rule rearchitecture changes
AND 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 = 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 (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'))
AND (Cx_BP_Id IS NULL
OR
EXISTS (SELECT '*'
FROM Okc_K_Items ITM
,Okc_K_Lines_B BPL
,Okc_K_Lines_B CV
WHERE CV.Cle_Id = SV.Id
AND CV.Lse_Id IN (2,15,20)
AND BPL.Cle_Id = CV.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'));
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
,OKS_ENTITLEMENTS_PVT.Get_End_Date_Time(SV.End_Date) SV_End_Date
,OKS_ENTITLEMENTS_PVT.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
,OKS_ENTITLEMENTS_PVT.Get_End_Date_Time(CL.End_Date) CL_End_Date
,OKS_ENTITLEMENTS_PVT.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
,okssrv.pm_program_id PM_Program_Id --rul.object1_id1 PM_Program_Id
,okssrv.pm_sch_exists_yn PM_Schedule_Exists --rul.rule_information2 PM_Schedule_Exists
FROM Okc_K_Headers_ALL_B HD -- Modified for 12.0 MOAC project (JVARGHES)
,Okc_K_Lines_B SV
,Okc_K_Lines_B CL
,oks_k_lines_b okssrv
WHERE sv.id = okssrv.cle_id
and okssrv.pm_program_id is not null
AND 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 = 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 (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'))
AND (Cx_BP_Id IS NULL
OR
EXISTS (SELECT '*'
FROM Okc_K_Items ITM
,Okc_K_Lines_B BPL
WHERE BPL.Cle_Id = OKSSRV.Coverage_ID
AND BPL.Lse_Id IN (3,16,21)
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'));
SELECT
cov.cle_Id SrvLine_Id,
okscov.pm_program_id PROGRAM_ID, --pma.object3_id1 PROGRAM_ID,
pmact.activity_id ACTIVITY_ID, --pma.object1_id1 ACTIVITY_ID,
pmact.sch_exists_yn SCHEDULE_EXISTS --pma.rule_information3 SCHEDULE_EXISTS
FROM
OKC_K_lines_b cov,
-- rule rearchitecture changes
oks_pm_activities pmact,
oks_k_lines_b okscov
-- OKC_Rule_Groups_B rgp,
-- OKC_rules_b pma
-- rule rearchitecture changes
WHERE cov.cle_Id = Cx_SrvLine_Id
AND cov.lse_id in (2,15,20)
-- rule rearchitecture changes
-- AND cov.dnz_chr_id = rgp.dnz_chr_id
-- AND cov.id = rgp.cle_id
-- AND rgp.id = pma.rgp_id
-- AND pma.rule_information_category='PMA'
-- AND pma.rule_information1 = 'Y'
and cov.id = pmact.cle_id
and pmact.select_yn = 'Y'
and cov.id = okscov.cle_id;
SELECT
srv.Id SrvLine_Id,
okssrv.pm_program_id PROGRAM_ID,
pmact.activity_id ACTIVITY_ID,
pmact.sch_exists_yn SCHEDULE_EXISTS
FROM
OKC_K_lines_b srv,
oks_pm_activities pmact,
oks_k_lines_b okssrv
WHERE srv.Id = Cx_SrvLine_Id
AND srv.lse_id in (1,14,19)
and srv.id = pmact.cle_id
and pmact.select_yn = 'Y'
and srv.id = okssrv.cle_id;
Lx_Contracts_02_Val.DELETE;
SELECT Dnz_Chr_Id
FROM Okc_K_lines_B
WHERE Id = Cx_SrvLine_Id;
SELECT cov.cle_Id SrvLine_Id,
okscov.pm_program_id program_id, -- pmp.object1_id1 program_id,
null ACTIVITY_ID,
sch.SCHEDULE_DATE,
sch.SCHEDULE_DATE_FROM,
sch.SCHEDULE_DATE_TO,
cov.end_date cov_end_date
FROM Okc_K_lines_B cov,
Oks_PM_Schedules sch,
-- rule rearchitecure changes
-- okc_rule_groups_b rgp,
-- OKC_rules_b pmp
oks_k_lines_b okscov
-- rule rearchitecture changes
WHERE cov.cle_Id =Cx_SrvLine_Id --nvl(Cx_SrvLine_Id,cov.cle_Id)
AND cov.lse_id in (2,15,20)
AND cov.dnz_chr_id = sch.dnz_chr_id
AND cov.id = sch.cle_id
-- rule rearchitecture changes
and sch.activity_line_id is null
and cov.id = okscov.cle_id
and okscov.pm_program_id = nvl(Cx_Program_Id,okscov.pm_program_id)
--03/15 chkrishn added to sort schedules returned
order by nvl(schedule_date,schedule_date_from);
SELECT srv.Id SrvLine_Id,
okssrv.pm_program_id program_id,
null ACTIVITY_ID,
sch.SCHEDULE_DATE,
sch.SCHEDULE_DATE_FROM,
sch.SCHEDULE_DATE_TO,
decode(okssrv.STANDARD_COV_YN,'Y',srv.end_date,cov.end_date) cov_end_date
FROM Okc_K_lines_B srv,
Oks_PM_Schedules sch,
oks_k_lines_b okssrv,
okc_k_lines_b cov
WHERE srv.Id = Cx_SrvLine_Id
AND srv.lse_id in (1,14,19)
AND srv.dnz_chr_id = sch.dnz_chr_id
AND srv.id = sch.cle_id
and sch.activity_line_id is null
and srv.id = okssrv.cle_id
and okssrv.pm_program_id = nvl(Cx_Program_Id,okssrv.pm_program_id)
and cov.id = okssrv.coverage_id
and cov.lse_id in (2,15,20)
order by nvl(sch.schedule_date,sch.schedule_date_from);
SELECT cov.cle_Id SrvLine_Id,
okscov.pm_program_id program_id,--pma.object3_id1 program_id,
pmact.activity_id activity_id, --pma.object1_id1 activity_id,
sch.SCHEDULE_DATE,
sch.SCHEDULE_DATE_FROM,
sch.SCHEDULE_DATE_TO,
cov.end_date cov_end_date
FROM Okc_K_lines_B cov,
Oks_PM_Schedules sch,
-- rule rearchitecure changes
-- okc_rule_groups_b rgp,
-- OKC_rules_b pma
oks_k_lines_b okscov,
oks_pm_activities pmact
-- rule rearchitecture changes
WHERE cov.cle_Id =Cx_SrvLine_Id --nvl(Cx_SrvLine_Id,cov.cle_Id)
AND cov.lse_id in (2,15,20)
AND cov.dnz_chr_id = sch.dnz_chr_id
AND cov.id = sch.cle_id
-- rule rearchitecure changes
and okscov.cle_id = cov.id
and pmact.cle_id = cov.id
and pmact.select_yn = 'Y'
and sch.activity_line_id = pmact.id
and okscov.pm_program_id = nvl(Cx_Program_Id,okscov.pm_program_id)
and pmact.activity_id = nvl(Cx_Activity_Id,pmact.activity_id)
--03/15 chkrishn added to sort schedules returned
order by nvl(schedule_date,schedule_date_from);
SELECT srv.Id SrvLine_Id,
okssrv.pm_program_id program_id,
pmact.activity_id activity_id,
sch.SCHEDULE_DATE,
sch.SCHEDULE_DATE_FROM,
sch.SCHEDULE_DATE_TO,
decode(okssrv.STANDARD_COV_YN,'Y',srv.end_date,cov.end_date) cov_end_date
FROM Okc_K_lines_B srv,
Okc_K_lines_B cov,
Oks_PM_Schedules sch,
oks_k_lines_b okssrv,
oks_pm_activities pmact
WHERE srv.Id = Cx_SrvLine_Id
AND srv.lse_id in (1,14,19)
and okssrv.cle_id = srv.id
and cov.id = okssrv.coverage_id
AND cov.lse_id in (2,15,20)
AND srv.dnz_chr_id = sch.dnz_chr_id
AND srv.id = sch.cle_id
and pmact.cle_id = srv.id
and pmact.select_yn = 'Y'
and sch.activity_line_id = pmact.id
and okssrv.pm_program_id = nvl(Cx_Program_Id,okssrv.pm_program_id)
and pmact.activity_id = nvl(Cx_Activity_Id,pmact.activity_id)
order by nvl(sch.schedule_date,sch.schedule_date_from);
SELECT cov.cle_Id SrvLine_Id,
okscov.pm_program_id program_id, --pma.object3_id1 program_id,
pmact.activity_id activity_id, -- pma.object1_id1 activity_id,
sch.SCHEDULE_DATE,
sch.SCHEDULE_DATE_FROM,
sch.SCHEDULE_DATE_TO,
cov.end_date cov_end_date
FROM Okc_K_lines_B cov,
Oks_PM_Schedules sch,
-- rule rearchitecture changes
-- okc_rule_groups_b rgp,
-- OKC_rules_b pma
oks_k_lines_b okscov,
oks_pm_activities pmact
-- rule rearchitecture changes
WHERE cov.cle_Id =Cx_SrvLine_Id --nvl(Cx_SrvLine_Id,cov.cle_Id)
AND cov.lse_id in (2,15,20)
AND cov.dnz_chr_id = sch.dnz_chr_id
AND cov.id = sch.cle_id
-- rule rearchitecture changes
and okscov.cle_id = cov.id
and okscov.pm_program_id = Cx_Program_Id
and pmact.cle_id = cov.id
and pmact.select_yn = 'Y'
and pmact.id = sch.activity_line_id
--03/15 chkrishn added to sort schedules returned
order by nvl(schedule_date,schedule_date_from);
SELECT srv.Id SrvLine_Id,
okssrv.pm_program_id program_id,
pmact.activity_id activity_id,
sch.SCHEDULE_DATE,
sch.SCHEDULE_DATE_FROM,
sch.SCHEDULE_DATE_TO,
decode(okssrv.STANDARD_COV_YN,'Y',srv.end_date,cov.end_date) cov_end_date
FROM Okc_K_lines_B srv,
Okc_K_lines_B cov,
Oks_PM_Schedules sch,
oks_k_lines_b okssrv,
oks_pm_activities pmact
WHERE srv.Id = Cx_SrvLine_Id
AND srv.lse_id in (1,14,19)
and srv.id = okssrv.cle_id
and cov.id = okssrv.coverage_id
AND cov.lse_id in (2,15,20)
AND srv.dnz_chr_id = sch.dnz_chr_id
AND srv.id = sch.cle_id
and okssrv.pm_program_id = Cx_Program_Id
and pmact.cle_id = srv.id
and pmact.select_yn = 'Y'
and pmact.id = sch.activity_line_id
order by nvl(sch.schedule_date,sch.schedule_date_from);
select pmact.sch_exists_yn
from Okc_K_lines_B srv,
oks_pm_activities pmact
where srv.id = Cx_SrvLine_Id
and srv.lse_id in (1,14,19)
and pmact.cle_id = srv.id
and pmact.select_yn = 'Y'
and pmact.activity_id = Cx_Activity_Id;
SELECT pm_conf_req_yn PM_ConfReq --Rule_Information1 PM_ConfReq
FROM Okc_K_lines_B cle1,
Okc_K_lines_B cle2,
-- rule rearchitecture changes
-- Okc_Rule_Groups_B rgp,
-- Okc_Rules_B rul
oks_k_lines_b okscov
-- rule rearchitecture changes
WHERE cle1.Id = Cx_SrvLine_Id
AND cle2.cle_Id = cle1.Id
AND cle2.lse_id in (2,15,20)
-- rule rearchitecture changes
and okscov.cle_id = cle2.id
AND okscov.pm_program_id = nvl(CX_Program_Id,okscov.pm_program_id);
SELECT pm_conf_req_yn PM_ConfReq --Rule_Information1 PM_ConfReq
FROM Okc_K_lines_B cle1,
oks_k_lines_b okssrv
WHERE cle1.Id = Cx_SrvLine_Id
and okssrv.cle_id = cle1.id
AND okssrv.pm_program_id = nvl(CX_Program_Id,okssrv.pm_program_id);
SELECT pmact.Conf_req_yn Act_ConfReq -- PMA.Rule_Information2 Act_ConfReq
FROM Okc_K_lines_B cle1,
Okc_K_lines_B cle2,
-- rule rearchitecture changes
oks_k_lines_b okscov,
oks_pm_activities pmact
-- Okc_Rule_Groups_B rgp,
-- Okc_Rules_B pma
-- rule rearchitecture changes
WHERE cle1.Id = Cx_SrvLine_Id
AND cle1.dnz_chr_id = cle2.dnz_chr_id
AND cle2.cle_Id = cle1.Id
AND cle2.lse_id in (2,15,20)
-- rule rearchitecture changes
and okscov.cle_id = cle2.id
and okscov.pm_program_id = nvl(CX_Program_Id,okscov.pm_program_id)
and pmact.cle_id = cle2.id
and pmact.select_yn = 'Y'
and pmact.activity_id = CX_Activity_Id;
SELECT pmact.Conf_req_yn Act_ConfReq -- PMA.Rule_Information2 Act_ConfReq
FROM Okc_K_lines_B cle1,
oks_k_lines_b okssrv,
oks_pm_activities pmact
WHERE cle1.Id = Cx_SrvLine_Id
and okssrv.cle_id = cle1.id
and okssrv.pm_program_id = nvl(CX_Program_Id,okssrv.pm_program_id)
and pmact.cle_id = cle1.id
and pmact.select_yn = 'Y'
and pmact.activity_id = CX_Activity_Id;
SELECT sts.ste_code
from
okc_k_lines_b cle,
okc_k_lines_b covcle,
oks_k_lines_b cov,
okc_statuses_b sts
where
cle.id=covcle.cle_id
and covcle.id=cov.cle_id
and cle.sts_code=sts.code
and sts.ste_code in ('ACTIVE','ENTERED','HOLD')
and cov.pm_program_id=Cx_PM_Id;
SELECT sts.ste_code
from
okc_k_lines_b cle,
oks_k_lines_b ksl,
okc_statuses_b sts
where
cle.id = ksl.cle_id
and cle.sts_code=sts.code
and sts.ste_code in ('ACTIVE','ENTERED','HOLD')
and ksl.pm_program_id = Cx_PM_Id;
select
sts.ste_code
from
okc_k_lines_b cle,
okc_k_lines_b covcle,
oks_pm_activities act,
okc_statuses_b sts
where
cle.id=covcle.cle_id
and cle.sts_code=sts.code
and covcle.id=act.cle_id
and sts.ste_code in ('ACTIVE','ENTERED','HOLD')
and act.activity_id=Cx_Act_Id;
select
sts.ste_code
from
okc_k_lines_b cle,
oks_pm_activities act,
okc_statuses_b sts
where
cle.sts_code = sts.code
and cle.id = act.cle_id
and sts.ste_code in ('ACTIVE','ENTERED','HOLD')
and act.activity_id = Cx_Act_Id;