[Home] [Help]
PACKAGE BODY: APPS.OKS_PM_ENTITLEMENTS_PVT
Source
1 PACKAGE BODY OKS_PM_ENTITLEMENTS_PVT AS
2 /* $Header: OKSRPMEB.pls 120.5 2006/07/10 13:15:30 jvorugan noship $ */
3 PROCEDURE Get_PMContracts_02_Format
4 (P_Contracts IN OKS_ENTITLEMENTS_PVT.GT_Contract_Ref
5 ,P_BusiProc_Id IN Gx_BusProcess_Id
6 ,P_Severity_Id IN Gx_Severity_Id
7 ,P_Request_TZone_Id IN Gx_TimeZoneId
8 ,P_Request_Date IN DATE
9 ,P_Request_Date_Start IN DATE
10 ,P_Request_Date_End IN DATE
11 ,P_Calc_RespTime_YN IN VARCHAR2
12 ,P_Validate_Eff IN VARCHAR2
13 ,P_Validate_Flag IN VARCHAR2
14 ,P_SrvLine_Flag IN VARCHAR2
15 ,P_Sort_Key IN VARCHAR2
16 ,X_Contracts_02 out nocopy OKS_ENTITLEMENTS_PUB.Get_ConTop_Tbl
17 ,X_Activities_02 out nocopy OKS_PM_ENTITLEMENTS_PUB.Get_Activityop_Tbl
18 ,X_Result out nocopy Gx_Boolean
19 ,X_Return_Status out nocopy Gx_Ret_Sts)
20 IS
21
22
23 --
24 -- Modified for 12.0 Coverage Rearch project (JVARGHES)
25 --
26 /*
27 CURSOR Lx_Csr_Contracts(Cx_Chr_Id IN Gx_OKS_Id, Cx_SrvLine_Id IN Gx_OKS_Id
28 ,Cx_CovLvlLine_Id IN Gx_OKS_Id, Cx_BP_Id IN Gx_BusProcess_Id,Cv_Cont_Pty_Id IN VARCHAR2) IS
29 SELECT HD.Id Contract_Id
30 ,HD.Contract_Number
31 ,HD.Contract_Number_Modifier
32 ,HD.Sts_Code
33 ,HD.Authoring_Org_Id
34 ,HD.Inv_Organization_Id
35 ,HD.End_Date HDR_End_Date --grace period changes
36 ,SV.Id Service_Line_Id
37 ,SV.Start_Date SV_Start_Date
38 ,OKS_ENTITLEMENTS_PVT.Get_End_Date_Time(SV.End_Date) SV_End_Date
39 ,OKS_ENTITLEMENTS_PVT.Get_End_Date_Time(SV.Date_Terminated) SV_Date_Terminated
40 ,CL.Sts_Code CL_Sts_Code
41 ,CL.Id CovLvl_Line_Id
42 ,CL.Start_Date CL_Start_Date
43 ,OKS_ENTITLEMENTS_PVT.Get_End_Date_Time(CL.End_Date) CL_End_Date
44 ,OKS_ENTITLEMENTS_PVT.Get_End_Date_Time(CL.Date_Terminated) CL_Date_Terminated
45 -- ,DECODE(SV.Lse_Id,14,'Y','N') Warranty_Flag
46 ,DECODE(SV.Lse_Id, 14, 'Y', 15, 'Y', 16, 'Y', 17, 'Y', 18, 'Y', 'N') Warranty_Flag
47 ,okscov.pm_program_id PM_Program_Id --rul.object1_id1 PM_Program_Id
48 ,okscov.pm_sch_exists_yn PM_Schedule_Exists --rul.rule_information2 PM_Schedule_Exists
49 FROM Okc_K_Headers_B HD
50 ,Okc_K_Lines_B SV
51 ,Okc_K_Lines_B CL
52 ,okc_k_lines_v cov
53 -- ,okc_rule_groups_b rgp -- 11.5.10 rule rearchitecture changes
54 -- ,okc_rules_b rul -- 11.5.10 rule rearchitecture changes
55 ,oks_k_lines_b okscov -- 11.5.10 rule rearchitecture changes
56 WHERE sv.id = cov.cle_id
57 -- rule rearchitecture changes
58 and cov.id = okscov.cle_id
59 and okscov.pm_program_id is not null
60 -- AND cov.id = rgp.cle_id
61 -- AND rgp.id = rul.rgp_id
62 -- AND cov.dnz_chr_id = rgp.dnz_chr_id
63 -- AND rul.rule_information_category = 'PMP'
64 -- rule rearchitecture changes
65 AND HD.Id = Cx_Chr_Id
66 AND HD.Scs_Code IN ('SERVICE','WARRANTY')
67 AND HD.Id > -1
68 AND HD.Template_YN <> 'Y'
69 AND SV.Dnz_Chr_Id = HD.Id
70 AND SV.Cle_Id IS NULL
71 AND SV.Chr_Id = HD.Id
72 AND SV.Lse_ID IN (1,14,19)
73 AND SV.Id = NVL(Cx_SrvLine_Id,SV.Id)
74 AND CL.Cle_Id = SV.Id
75 AND CL.Lse_ID IN (7,8,9,10,11,35,18,25)
76 AND CL.Id = NVL(Cx_CovLvlLine_Id, CL.Id)
77 AND (Cv_Cont_Pty_Id IS NULL
78 OR
79 EXISTS (SELECT '*'
80 FROM Okc_K_Party_Roles_B PR
81 WHERE PR.Chr_Id = HD.Id
82 AND PR.Cle_Id IS NULL
83 AND PR.Dnz_Chr_Id = HD.Id
84 AND PR.Object1_Id1 = Cv_Cont_Pty_Id
85 AND PR.Object1_Id2 = '#'
86 AND PR.Jtot_Object1_Code = 'OKX_PARTY'
87 AND PR.Rle_Code <> 'VENDOR'))
88 AND (Cx_BP_Id IS NULL
89 OR
90 EXISTS (SELECT '*'
91 FROM Okc_K_Items ITM
92 ,Okc_K_Lines_B BPL
93 ,Okc_K_Lines_B CV
94 WHERE CV.Cle_Id = SV.Id
95 AND CV.Lse_Id IN (2,15,20)
96 AND BPL.Cle_Id = CV.Id
97 AND ITM.Cle_Id = BPL.Id
98 AND ITM.Object1_Id1 = TO_CHAR(Cx_BP_Id)
99 AND ITM.Object1_Id2 = '#'
100 AND ITM.Jtot_Object1_Code = 'OKX_BUSIPROC'));
101 */
102
103 --
104 -- Modified for 12.0 Coverage Rearch project (JVARGHES)
105 --
106
107 CURSOR Lx_Csr_Contracts(Cx_Chr_Id IN Gx_OKS_Id, Cx_SrvLine_Id IN Gx_OKS_Id
108 ,Cx_CovLvlLine_Id IN Gx_OKS_Id, Cx_BP_Id IN Gx_BusProcess_Id,Cv_Cont_Pty_Id IN VARCHAR2) IS
109 SELECT HD.Id Contract_Id
110 ,HD.Contract_Number
111 ,HD.Contract_Number_Modifier
112 ,HD.Sts_Code
113 ,HD.Authoring_Org_Id
114 ,HD.Inv_Organization_Id
115 ,HD.End_Date HDR_End_Date --grace period changes
116 ,SV.Id Service_Line_Id
117 ,SV.Start_Date SV_Start_Date
118 ,OKS_ENTITLEMENTS_PVT.Get_End_Date_Time(SV.End_Date) SV_End_Date
119 ,OKS_ENTITLEMENTS_PVT.Get_End_Date_Time(SV.Date_Terminated) SV_Date_Terminated
120 ,CL.Sts_Code CL_Sts_Code
121 ,CL.Id CovLvl_Line_Id
122 ,CL.Start_Date CL_Start_Date
123 ,OKS_ENTITLEMENTS_PVT.Get_End_Date_Time(CL.End_Date) CL_End_Date
124 ,OKS_ENTITLEMENTS_PVT.Get_End_Date_Time(CL.Date_Terminated) CL_Date_Terminated
125 ,DECODE(SV.Lse_Id, 14, 'Y', 15, 'Y', 16, 'Y', 17, 'Y', 18, 'Y', 'N') Warranty_Flag
126 ,okssrv.pm_program_id PM_Program_Id --rul.object1_id1 PM_Program_Id
127 ,okssrv.pm_sch_exists_yn PM_Schedule_Exists --rul.rule_information2 PM_Schedule_Exists
128 FROM Okc_K_Headers_ALL_B HD -- Modified for 12.0 MOAC project (JVARGHES)
129 ,Okc_K_Lines_B SV
130 ,Okc_K_Lines_B CL
131 ,oks_k_lines_b okssrv
132 WHERE sv.id = okssrv.cle_id
133 and okssrv.pm_program_id is not null
134 AND HD.Id = Cx_Chr_Id
135 AND HD.Scs_Code IN ('SERVICE','WARRANTY')
136 AND HD.Id > -1
137 AND HD.Template_YN <> 'Y'
138 AND SV.Dnz_Chr_Id = HD.Id
139 AND SV.Cle_Id IS NULL
140 AND SV.Chr_Id = HD.Id
141 AND SV.Lse_ID IN (1,14,19)
142 AND SV.Id = NVL(Cx_SrvLine_Id,SV.Id)
143 AND CL.Cle_Id = SV.Id
144 AND CL.Lse_ID IN (7,8,9,10,11,35,18,25)
145 AND CL.Id = NVL(Cx_CovLvlLine_Id, CL.Id)
146 AND (Cv_Cont_Pty_Id IS NULL
147 OR
148 EXISTS (SELECT '*'
149 FROM Okc_K_Party_Roles_B PR
150 WHERE PR.Chr_Id = HD.Id
151 AND PR.Cle_Id IS NULL
152 AND PR.Dnz_Chr_Id = HD.Id
153 AND PR.Object1_Id1 = Cv_Cont_Pty_Id
154 AND PR.Object1_Id2 = '#'
155 AND PR.Jtot_Object1_Code = 'OKX_PARTY'
156 AND PR.Rle_Code <> 'VENDOR'))
157 AND (Cx_BP_Id IS NULL
158 OR
159 EXISTS (SELECT '*'
160 FROM Okc_K_Items ITM
161 ,Okc_K_Lines_B BPL
162 WHERE BPL.Cle_Id = OKSSRV.Coverage_ID
163 AND BPL.Lse_Id IN (3,16,21)
164 AND ITM.Cle_Id = BPL.Id
165 AND ITM.Object1_Id1 = TO_CHAR(Cx_BP_Id)
166 AND ITM.Object1_Id2 = '#'
167 AND ITM.Jtot_Object1_Code = 'OKX_BUSIPROC'));
168
169 --
170 --
171
172 --
173 -- Modified for 12.0 Coverage Rearch project (JVARGHES)
174 --
175 /*
176 CURSOR Lx_Csr_Activities(Cx_SrvLine_Id IN Gx_OKS_Id)
177 IS
178 SELECT
179 cov.cle_Id SrvLine_Id,
180 okscov.pm_program_id PROGRAM_ID, --pma.object3_id1 PROGRAM_ID,
181 pmact.activity_id ACTIVITY_ID, --pma.object1_id1 ACTIVITY_ID,
182 pmact.sch_exists_yn SCHEDULE_EXISTS --pma.rule_information3 SCHEDULE_EXISTS
183 FROM
184 OKC_K_lines_b cov,
185 -- rule rearchitecture changes
186 oks_pm_activities pmact,
187 oks_k_lines_b okscov
188 -- OKC_Rule_Groups_B rgp,
189 -- OKC_rules_b pma
190 -- rule rearchitecture changes
191 WHERE cov.cle_Id = Cx_SrvLine_Id
192 AND cov.lse_id in (2,15,20)
193 -- rule rearchitecture changes
194 -- AND cov.dnz_chr_id = rgp.dnz_chr_id
195 -- AND cov.id = rgp.cle_id
196 -- AND rgp.id = pma.rgp_id
197 -- AND pma.rule_information_category='PMA'
198 -- AND pma.rule_information1 = 'Y'
199 and cov.id = pmact.cle_id
200 and pmact.select_yn = 'Y'
201 and cov.id = okscov.cle_id;
202 -- rule rearchitecture changes
203 */
204
205 --
206 -- Modified for 12.0 Coverage Rearch project (JVARGHES)
207 --
208
209 CURSOR Lx_Csr_Activities(Cx_SrvLine_Id IN Gx_OKS_Id)
210 IS
211 SELECT
212 srv.Id SrvLine_Id,
213 okssrv.pm_program_id PROGRAM_ID,
214 pmact.activity_id ACTIVITY_ID,
215 pmact.sch_exists_yn SCHEDULE_EXISTS
216 FROM
217 OKC_K_lines_b srv,
218 oks_pm_activities pmact,
219 oks_k_lines_b okssrv
220 WHERE srv.Id = Cx_SrvLine_Id
221 AND srv.lse_id in (1,14,19)
222 and srv.id = pmact.cle_id
223 and pmact.select_yn = 'Y'
224 and srv.id = okssrv.cle_id;
225
226 --
227 --
228
229 Lx_Contracts OKS_ENTITLEMENTS_PVT.GT_Contract_Ref DEFAULT P_Contracts;
230 Ld_Request_Date CONSTANT DATE := P_Request_Date;
231 Ld_Request_Date_Start CONSTANT DATE := P_Request_Date_Start;
232 Ld_Request_Date_End CONSTANT DATE := P_Request_Date_End;
233 Lv_Validate_Flag VARCHAR2(1) := P_Validate_Flag;
234 Lv_SrvLine_Flag CONSTANT VARCHAR2(1) := P_SrvLine_Flag;
235 Lv_Sort_Key CONSTANT VARCHAR2(10):= P_Sort_Key;
236
237 Lx_BusiProc_Id CONSTANT Gx_BusProcess_Id := P_BusiProc_Id;
238 Lx_Severity_Id CONSTANT Gx_Severity_Id := P_Severity_Id;
239 Lx_Request_TZone_Id CONSTANT Gx_TimeZoneId := P_Request_TZone_Id;
240 Lv_Calc_RespTime_YN CONSTANT VARCHAR2(1) := P_Calc_RespTime_YN;
241 Lv_Validate_Eff CONSTANT VARCHAR2(1) := P_Validate_Eff;
242 Lv_Cont_Pty_Id VARCHAR2(100);
243
244 Lx_Contracts_02 OKS_ENTITLEMENTS_PUB.Get_ConTop_Tbl;
245 --ph2
246 Lx_Activities_02 OKS_PM_ENTITLEMENTS_PUB.Get_Activityop_Tbl;
247 Lx_Contracts_02_out OKS_ENTITLEMENTS_PUB.Get_ConTop_Tbl;
248 Lx_Contracts_02_Val OKS_ENTITLEMENTS_PUB.Get_ConTop_Tbl;
249
250 Lx_Idx_Rec OKS_ENTITLEMENTS_PVT.Idx_Rec;
251
252 Lx_Result Gx_Boolean DEFAULT G_TRUE;
253 Lx_Return_Status Gx_Ret_Sts DEFAULT G_RET_STS_SUCCESS;
254
255 Li_TableIdx BINARY_INTEGER;
256 Li_OutTab_Idx BINARY_INTEGER := 0;
257 --ph2
258 Li_ActOutTab_Idx BINARY_INTEGER := 0;
259 Lv_Entile_Flag VARCHAR2(1);
260 Lv_Effective_Falg VARCHAR2(1);
261
262 Lx_SrvLine_Id Gx_OKS_Id;
263 Lx_CovLvlLine_Id Gx_OKS_Id;
264
265 Ln_Msg_Count NUMBER;
266 Lv_Msg_Data VARCHAR2(2000);
267
268 L_EXCEP_UNEXPECTED_ERR EXCEPTION;
269
270 BEGIN
271
272 Li_TableIdx := Lx_Contracts.FIRST;
273 --dbms_output.put_line('Value of Li_TableIdx='||Li_TableIdx);
274 WHILE Li_TableIdx IS NOT NULL LOOP
275
276 IF Lv_SrvLine_Flag = 'T' THEN
277
278 Lx_SrvLine_Id := Lx_Contracts(Li_TableIdx).Rx_Cle_Id;
279 Lx_CovLvlLine_Id := NULL;
280
281 ELSE
282 Lx_SrvLine_Id := NULL;
283 Lx_CovLvlLine_Id := Lx_Contracts(Li_TableIdx).Rx_Cle_Id;
284 -- dbms_output.put_line('Value of cle_id '||Lx_Contracts(Li_TableIdx).Rx_Cle_Id);
285 END IF;
286
287
288 Lv_Cont_Pty_Id := TO_CHAR(Lx_Contracts(Li_TableIdx).Rx_Pty_Id);
289 FOR Idx IN Lx_Csr_Contracts(Lx_Contracts(Li_TableIdx).Rx_Chr_Id,Lx_SrvLine_Id,Lx_CovLvlLine_Id,Lx_BusiProc_Id,Lv_Cont_Pty_Id) LOOP
290
291 --Lx_Idx_Rec := Idx;
292 Lx_Idx_Rec.Contract_Id := Idx.Contract_Id;
293 Lx_Idx_Rec.Contract_Number := Idx.Contract_Number;
294 Lx_Idx_Rec.Contract_Number_Modifier := Idx.Contract_Number_Modifier;
295 Lx_Idx_Rec.Sts_Code := Idx.Sts_Code;
296 Lx_Idx_Rec.Authoring_Org_Id := Idx.Authoring_Org_Id;
297 Lx_Idx_Rec.Inv_Organization_Id := Idx.Inv_Organization_Id;
298 Lx_Idx_Rec.HDR_End_Date := Idx.HDR_End_Date;
299 Lx_Idx_Rec.Service_Line_Id := Idx.Service_Line_Id;
300 Lx_Idx_Rec.SV_Start_Date := Idx.SV_Start_Date;
301 Lx_Idx_Rec.SV_End_Date := Idx.SV_End_Date;
302 Lx_Idx_Rec.SV_Date_Terminated := Idx.SV_Date_Terminated;
303 Lx_Idx_Rec.CL_Sts_Code := Idx.CL_Sts_Code;
304 Lx_Idx_Rec.CovLvl_Line_Id := Idx.CovLvl_Line_Id;
305 Lx_Idx_Rec.CL_Start_Date := Idx.CL_Start_Date;
306 Lx_Idx_Rec.CL_End_Date := Idx.CL_End_Date;
307 Lx_Idx_Rec.CL_Date_Terminated := Idx.CL_Date_Terminated;
308 Lx_Idx_Rec.Warranty_Flag := Idx.Warranty_Flag;
309 OKS_ENTITLEMENTS_PVT.Get_Cont02Format_Validation
310 (P_Contracts => Lx_Idx_Rec
311 ,P_BusiProc_Id => Lx_BusiProc_Id
312 ,P_Severity_Id => Lx_Severity_Id
313 ,P_Request_TZone_Id => Lx_Request_TZone_Id
314 ,P_Dates_In_Input_TZ => 'Y' -- Added for 12.0 ENT-TZ project (JVARGHES)
315 ,P_Incident_Date => Ld_Request_Date -- Added for 12.0 ENT-TZ project (JVARGHES)
316 ,P_Request_Date => Ld_Request_Date
317 ,P_Request_Date_Start => Ld_Request_Date_Start
318 ,P_Request_Date_End => Ld_Request_Date_End
319 ,P_Calc_RespTime_YN => Lv_Calc_RespTime_YN
320 ,P_Validate_Eff => Lv_Validate_Eff
321 ,P_Validate_Flag => Lv_Validate_Flag
322 ,P_SrvLine_Flag => Lv_SrvLine_Flag
323 ,P_Sort_Key => Lv_Sort_Key
324 ,X_Contracts_02 => Lx_Contracts_02_Val
325 ,X_Result => Lx_Result
326 ,X_Return_Status => Lx_Return_Status);
327 -- dbms_output.put_line('Status '||Lx_Return_Status);
328 IF Lx_Contracts_02_Val.count >0 THEN --CK 12/21
329 Li_OutTab_Idx := Li_OutTab_Idx + 1;
330
331 Lx_Contracts_02(Li_OutTab_Idx).Contract_Id := Lx_Contracts_02_Val(1).Contract_Id;
332 -- dbms_output.put_line('Contract id '||Lx_Contracts_02_Val(1).Contract_Id);
333 Lx_Contracts_02(Li_OutTab_Idx).Contract_Number := Lx_Contracts_02_Val(1).Contract_Number;
334 Lx_Contracts_02(Li_OutTab_Idx).Contract_Number_Modifier := Lx_Contracts_02_Val(1).Contract_Number_Modifier;
335 -- Lx_Contracts_02(Li_OutTab_Idx).Sts_code := Lx_Contracts_02_Val(1).Sts_code;
336 Lx_Contracts_02(Li_OutTab_Idx).Sts_code := Lx_Contracts_02_Val(1).Sts_code;
337 Lx_Contracts_02(Li_OutTab_Idx).Service_Line_Id := Lx_Contracts_02_Val(1).Service_Line_Id;
338 Lx_Contracts_02(Li_OutTab_Idx).Service_Name := Lx_Contracts_02_Val(1).Service_Name;
339 Lx_Contracts_02(Li_OutTab_Idx).Service_Description := Lx_Contracts_02_Val(1).Service_Description;
340 -- Lx_Contracts_02(Li_OutTab_Idx).Service_Start_Date := Lx_Contracts_02_Val(1).Service_Start_Date;
341 -- Lx_Contracts_02(Li_OutTab_Idx).Service_End_Date := Lx_Contracts_02_Val(1).Service_End_Date;
342 Lx_Contracts_02(Li_OutTab_Idx).Service_Start_Date := Lx_Contracts_02_Val(1).Service_Start_Date;
343 Lx_Contracts_02(Li_OutTab_Idx).Service_End_Date := Lx_Contracts_02_Val(1).Service_End_Date;
344 Lx_Contracts_02(Li_OutTab_Idx).Coverage_Term_Line_Id := Lx_Contracts_02_Val(1).Coverage_Term_Line_Id;
345 Lx_Contracts_02(Li_OutTab_Idx).Coverage_Term_Name := Lx_Contracts_02_Val(1).Coverage_Term_Name;
346 Lx_Contracts_02(Li_OutTab_Idx).Coverage_Term_Description := Lx_Contracts_02_Val(1).Coverage_Term_Description;
347 Lx_Contracts_02(Li_OutTab_Idx).Warranty_Flag := Lx_Contracts_02_Val(1).Warranty_Flag;
348 Lx_Contracts_02(Li_OutTab_Idx).Eligible_For_Entitlement := Lx_Contracts_02_Val(1).Eligible_For_Entitlement;
349 Lx_Contracts_02(Li_OutTab_Idx).date_terminated := Lx_Contracts_02_Val(1).date_terminated;
350 Lx_Contracts_02(Li_OutTab_Idx).PM_Program_Id := Lx_Contracts_02_Val(1).PM_Program_Id;
351 Lx_Contracts_02(Li_OutTab_Idx).PM_Schedule_Exists := Lx_Contracts_02_Val(1).PM_Schedule_Exists;
352 Lx_Contracts_02(Li_OutTab_Idx).Exp_Reaction_Time := Lx_Contracts_02_Val(1).Exp_Reaction_Time;
353 Lx_Contracts_02(Li_OutTab_Idx).Exp_Resolution_Time := Lx_Contracts_02_Val(1).Exp_Resolution_Time;
354 Lx_Contracts_02(Li_OutTab_Idx).Status_Code := Lx_Contracts_02_Val(1).Status_Code;
355 Lx_Contracts_02(Li_OutTab_Idx).Status_Text := Lx_Contracts_02_Val(1).Status_Text;
356 Lx_Contracts_02(Li_OutTab_Idx).Coverage_Type_Code := Lx_Contracts_02_Val(1).Coverage_Type_Code;
357 Lx_Contracts_02(Li_OutTab_Idx).Coverage_Type_Meaning := Lx_Contracts_02_Val(1).Coverage_Type_Meaning;
358 Lx_Contracts_02(Li_OutTab_Idx).coverage_Type_Imp_Level := Lx_Contracts_02_Val(1).coverage_Type_Imp_Level;
359 Lx_Contracts_02(Li_OutTab_Idx).PM_Program_Id := Idx.PM_Program_Id;
360 --03/15/04 modified to return program schedule exists
361 Lx_Contracts_02(Li_OutTab_Idx).PM_Schedule_Exists := Idx.PM_Schedule_Exists;
362
363 Lx_Contracts_02_Val.DELETE;
364
365 IF Lx_Return_Status = G_RET_STS_UNEXP_ERROR THEN
366 RAISE L_EXCEP_UNEXPECTED_ERR;
367 END IF;
368
369 --Add code for ph2 here
370 -- IF Lx_SrvLine_Id is not NULL THEN
371 -- FOR ActIdx IN Lx_Csr_Activities(Lx_SrvLine_Id) LOOP
372 IF Lx_Contracts_02(Li_OutTab_Idx).Service_Line_Id is not NULL
373 AND Lx_Contracts_02(Li_OutTab_Idx).PM_Program_Id is not NULL
374 THEN
375
376 -- IF Lx_Contracts_02(Li_OutTab_Idx).Service_Line_Id is not NULL THEN
377 FOR ActIdx IN Lx_Csr_Activities(Lx_Contracts_02(Li_OutTab_Idx).Service_Line_Id) LOOP
378 Li_ActOutTab_Idx := Li_ActOutTab_Idx + 1;
379 -- dbms_output.put_line('Act id '||ActIdx.Activity_Id);
380 Lx_Activities_02(Li_ActOutTab_Idx).service_line_id := ActIdx.SrvLine_Id;
381 Lx_Activities_02(Li_ActOutTab_Idx).PM_program_id := ActIdx.Program_Id;
382 Lx_Activities_02(Li_ActOutTab_Idx).Activity_id := ActIdx.Activity_Id;
383 Lx_Activities_02(Li_ActOutTab_Idx).Act_Schedule_Exists := ActIdx.schedule_exists;
384
385 -- Added by Jvorugan for Bug:5357010
386 IF nvl(Idx.PM_Schedule_Exists,'!') = 'Y'
387 THEN
388 Lx_Activities_02(Li_ActOutTab_Idx).Act_Schedule_Exists := 'Y';
389 END IF;
390 -- End of changes by Jvorugan
391 END LOOP;
392 END IF;
393 END IF; --CK12/21
394 END LOOP;
395 /*--Add code for ph2 here
396 -- IF Lx_SrvLine_Id is not NULL THEN
397 -- FOR ActIdx IN Lx_Csr_Activities(Lx_SrvLine_Id) LOOP
398 IF Lx_Contracts_02(Li_OutTab_Idx).Service_Line_Id is not NULL THEN
399 FOR ActIdx IN Lx_Csr_Activities(Lx_Contracts_02(Li_OutTab_Idx).Service_Line_Id) LOOP
400 Li_ActOutTab_Idx := Li_ActOutTab_Idx + 1;
401 Lx_Activities_02(Li_ActOutTab_Idx).service_line_id := ActIdx.SrvLine_Id;
402 Lx_Activities_02(Li_ActOutTab_Idx).PM_program_id := ActIdx.Program_Id;
403 Lx_Activities_02(Li_ActOutTab_Idx).Activity_id := ActIdx.Activity_Id;
404 Lx_Activities_02(Li_ActOutTab_Idx).Act_Schedule_Exists := ActIdx.schedule_exists;
405 END LOOP;
406 END IF; */
407 Li_TableIdx := Lx_Contracts.NEXT(Li_TableIdx);
408 END LOOP;
409 -- dbms_output.put_line('Count after loop'||Lx_Contracts_02.count);
410 -- IF Lv_Sort_Key <> G_NO_SORT_KEY THEN
411 -- above IF commented and new one introduced , as sorting needs to be done when calculate response time flag
412 -- is 'Y' or sort key is sorting with importance level.
413 IF (((Lv_Calc_RespTime_YN = 'N') AND (Lv_Sort_Key = 'COVTYP_IMP')) OR (Lv_Calc_RespTime_YN = 'Y')) THEN
414
415 OKS_ENTITLEMENTS_PVT.Sort_Asc_GetContracts_02
416 (P_Input_Tab => Lx_Contracts_02
417 ,P_Sort_Key => Lv_Sort_Key
418 ,X_Output_Tab => Lx_Contracts_02_Out
419 ,X_Result => Lx_Result
420 ,X_Return_Status => Lx_Return_Status);
421 -- IF Lx_Result <> G_TRUE THEN
422 IF Lx_Return_Status <> G_TRUE THEN -- modified SP
423 RAISE L_EXCEP_UNEXPECTED_ERR;
424 END IF;
425
426 ELSE
427
428 Lx_Contracts_02_out := Lx_Contracts_02;
429
430 END IF;
431
432 -- dbms_output.put_line('Count in proc'||Lx_Contracts_02_Out.count);
433 -- dbms_output.put_line('Act Count in proc'||Lx_Activities_02.count);
434 X_Contracts_02 := Lx_Contracts_02_Out;
435 X_Activities_02 := Lx_Activities_02;
436 X_Result := Lx_Result;
437 X_Return_Status := Lx_Return_Status;
438
439
440 EXCEPTION
441
442 WHEN L_EXCEP_UNEXPECTED_ERR THEN
443
444 X_Result := Lx_Result;
445 X_Return_Status := Lx_Return_Status;
446
447 WHEN OTHERS THEN
448
449 OKC_API.SET_MESSAGE
450 (P_App_Name => G_APP_NAME_OKC
451 ,P_Msg_Name => G_UNEXPECTED_ERROR
452 ,P_Token1 => G_SQLCODE_TOKEN
453 ,P_Token1_Value => SQLCODE
454 ,P_Token2 => G_SQLERRM_TOKEN
455 ,P_Token2_Value => SQLERRM);
456
457 OKC_API.SET_MESSAGE
458 (P_App_Name => G_APP_NAME_OKC
459 ,P_Msg_Name => G_DEBUG_TOKEN
460 ,P_Token1 => G_PACKAGE_TOKEN
461 ,P_Token1_Value => G_PKG_NAME
462 ,P_Token2 => G_PROGRAM_TOKEN
463 ,P_Token2_Value => 'Get_PMContracts_02_Format');
464
465 X_Result := G_FALSE;
466 X_Return_Status := G_RET_STS_UNEXP_ERROR;
467
468 END Get_PMContracts_02_Format;
469
470 --ph 2
471 PROCEDURE Get_PMContracts_02
472 (P_API_Version IN NUMBER
473 ,P_Init_Msg_List IN VARCHAR2
474 ,P_Inp_Rec IN OKS_ENTITLEMENTS_PVT.Inp_rec_getcont02
475 ,X_Return_Status out nocopy VARCHAR2
476 ,X_Msg_Count out nocopy NUMBER
477 ,X_Msg_Data out nocopy VARCHAR2
478 ,X_Ent_Contracts out nocopy OKS_ENTITLEMENTS_PUB.Get_ConTop_Tbl
479 ,X_PM_Activities out nocopy OKS_PM_ENTITLEMENTS_PUB.get_activityop_tbl)
480 IS
481
482 CURSOR Lx_SrvLine(Cx_SrvLine_Id IN Gx_OKS_Id) IS
483 SELECT Dnz_Chr_Id
484 FROM Okc_K_lines_B
485 WHERE Id = Cx_SrvLine_Id;
486
487 Lx_Inp_Rec CONSTANT OKS_ENTITLEMENTS_PVT.Inp_rec_getcont02 := P_Inp_Rec;
488 Lx_Return_Status Gx_Ret_Sts DEFAULT G_RET_STS_SUCCESS;
489 Lx_Result Gx_Boolean DEFAULT G_TRUE;
490
491 Lx_Ent_Contracts OKS_ENTITLEMENTS_PUB.Get_ConTop_Tbl;
492 --ph2
493 Lx_PM_Activities OKS_PM_ENTITLEMENTS_PUB.Get_Activityop_tbl;
494 Lx_Contracts OKS_ENTITLEMENTS_PVT.GT_Contract_Ref;
495 Lx_Contracts_Temp OKS_ENTITLEMENTS_PVT.GT_Contract_Ref;
496 Lx_Contracts_out OKS_ENTITLEMENTS_PVT.GT_Contract_Ref;
497 Lv_SrvLine_Flag VARCHAR2(1):= 'F';
498
499 Ln_Organization_Id NUMBER;
500 Ln_Org_Id NUMBER;
501 Ln_Chr_Id NUMBER;
502
503 Li_TableIdx BINARY_INTEGER;
504
505 L_EXCEP_UNEXPECTED_ERR EXCEPTION;
506
507 BEGIN
508
509 -- Bug# 4735542
510 -- Ln_Organization_Id := SYS_CONTEXT('OKC_CONTEXT','ORGANIZATION_ID');
511 -- Ln_Org_Id := SYS_CONTEXT('OKC_CONTEXT','ORG_ID');
512
513 OKS_ENTITLEMENTS_PVT.G_GRACE_PROFILE_SET := fnd_profile.value('OKS_ENABLE_GRACE_PERIOD');
514
515 IF Lx_Inp_Rec.Service_Line_Id IS NOT NULL THEN
516
517 OPEN Lx_SrvLine(Lx_Inp_Rec.Service_Line_Id);
518 FETCH Lx_SrvLine INTO Ln_Chr_Id;
519 CLOSE Lx_SrvLine;
520
521 Li_TableIdx := NVL(Lx_Contracts.LAST,0) + 1;
522 Lx_Contracts(Li_TableIdx).Rx_Chr_Id := Ln_Chr_Id;
523 Lx_Contracts(Li_TableIdx).Rx_Cle_Id := Lx_Inp_Rec.Service_Line_Id;
524
525 Lv_SrvLine_Flag := 'T';
526
527 ELSIF Lx_Inp_Rec.Contract_Number IS NOT NULL THEN
528
529 OKS_ENTITLEMENTS_PVT.Get_Contracts_Id
530 (P_Contract_Num => Lx_Inp_Rec.Contract_Number
531 ,P_Contract_Num_Modifier => Lx_Inp_Rec.Contract_Number_Modifier
532 ,X_Contracts => Lx_Contracts
533 ,X_Result => Lx_Result
534 ,X_Return_Status => Lx_Return_Status);
535 -- dbms_output.put_line ('Contracts from outer '||Lx_Contracts.count);
536 IF Lx_Result <> G_TRUE THEN
537 RAISE L_EXCEP_UNEXPECTED_ERR;
538 END IF;
539
540 ELSIF Lx_Inp_Rec.Product_Id IS NOT NULL THEN
541
542 OKS_ENTITLEMENTS_PVT.Get_CovProd_Contracts
543 (P_CovProd_Obj_Id => Lx_Inp_Rec.Product_Id
544 ,P_Organization_Id => Ln_Organization_Id
545 ,P_Org_Id => Ln_Org_Id
546 ,X_CovProd_Contracts => Lx_Contracts
547 ,X_Result => Lx_Result
548 ,X_Return_Status => Lx_Return_Status);
549
550 -- dbms_output.put_line ('Contracts covprod from outer '||Lx_Contracts.count);
551 IF Lx_Result <> G_TRUE THEN
552 RAISE L_EXCEP_UNEXPECTED_ERR;
553 END IF;
554
555 ELSE
556
557 IF Lx_Inp_Rec.Item_Id IS NOT NULL THEN
558
559 OKS_ENTITLEMENTS_PVT.Get_CovItem_Contracts
560 (P_CovItem_Obj_Id => Lx_Inp_Rec.Item_Id
561 ,P_Organization_Id => Ln_Organization_Id
562 ,P_Party_Id => Lx_Inp_Rec.Party_Id
563 ,X_CovItem_Contracts => Lx_Contracts
564 ,X_Result => Lx_Result
565 ,X_Return_Status => Lx_Return_Status);
566
567 IF Lx_Result <> G_TRUE THEN
568 RAISE L_EXCEP_UNEXPECTED_ERR;
569 END IF;
570
571
572 END IF;
573
574 IF Lx_Inp_Rec.Party_Id IS NOT NULL THEN
575
576 OKS_ENTITLEMENTS_PVT.Get_CovParty_Contracts
577 (P_CovParty_Obj_Id => Lx_Inp_Rec.Party_Id
578 ,X_CovParty_Contracts => Lx_Contracts_Temp
579 ,X_Result => Lx_Result
580 ,X_Return_Status => Lx_Return_Status);
581
582 IF Lx_Result <> G_TRUE THEN
583 RAISE L_EXCEP_UNEXPECTED_ERR;
584 END IF;
585
586 END IF;
587
588 END IF;
589
590 OKS_ENTITLEMENTS_PVT.Append_Contract_PlSql_Table
591 (P_Input_Tab => Lx_Contracts_Temp
592 ,P_Append_Tab => Lx_Contracts
593 ,X_Output_Tab => Lx_Contracts_Out
594 ,X_Result => Lx_Result
595 ,X_Return_Status => Lx_Return_Status);
596
597 IF Lx_Result <> G_TRUE THEN
598 RAISE L_EXCEP_UNEXPECTED_ERR;
599 END IF;
600 Get_PMContracts_02_Format
601 (P_Contracts => Lx_Contracts_Out
602 ,P_BusiProc_Id => Lx_Inp_Rec.Business_Process_Id
603 ,P_Severity_Id => Lx_Inp_Rec.Severity_Id
604 ,P_Request_TZone_Id => Lx_Inp_Rec.Time_Zone_Id
605 ,P_Request_Date => Lx_Inp_Rec.Request_Date
606 ,P_Request_Date_Start => Lx_Inp_Rec.Request_Date_start
607 ,P_Request_Date_End => Lx_Inp_Rec.Request_Date_end
608 ,P_Calc_RespTime_YN => Lx_Inp_Rec.Calc_RespTime_Flag
609 ,P_Validate_Eff => Lx_Inp_Rec.Validate_Eff_Flag
610 ,P_Validate_Flag => Lx_Inp_Rec.Validate_Flag
611 ,P_SrvLine_Flag => Lv_SrvLine_Flag
612 ,P_Sort_Key => Lx_Inp_Rec.Sort_Key
613 ,X_Contracts_02 => Lx_Ent_Contracts
614 ,X_Activities_02 => Lx_PM_Activities
615 ,X_Result => Lx_Result
616 ,X_Return_Status => Lx_Return_Status);
617
618 X_Ent_Contracts := Lx_Ent_Contracts;
619 --ph2
620 X_PM_Activities := Lx_PM_Activities;
621 X_Return_Status := Lx_Return_Status;
622
623 EXCEPTION
624
625 WHEN L_EXCEP_UNEXPECTED_ERR THEN
626
627 --X_Result := Lx_Result;
628 X_Return_Status := Lx_Return_Status;
629
630 WHEN OTHERS THEN
631
632 OKC_API.SET_MESSAGE
633 (P_App_Name => G_APP_NAME_OKC
634 ,P_Msg_Name => G_UNEXPECTED_ERROR
635 ,P_Token1 => G_SQLCODE_TOKEN
636 ,P_Token1_Value => SQLCODE
637 ,P_Token2 => G_SQLERRM_TOKEN
638 ,P_Token2_Value => SQLERRM);
639
640 OKC_API.SET_MESSAGE
641 (P_App_Name => G_APP_NAME_OKC
642 ,P_Msg_Name => G_DEBUG_TOKEN
643 ,P_Token1 => G_PACKAGE_TOKEN
644 ,P_Token1_Value => G_PKG_NAME
645 ,P_Token2 => G_PROGRAM_TOKEN
646 ,P_Token2_Value => 'Get_PMContracts_02');
647
648 --X_Result := G_FALSE;
649 X_Return_Status := G_RET_STS_UNEXP_ERROR;
650
651 END Get_PMContracts_02;
652
653 --Ph2
654 PROCEDURE Get_PM_Contracts
655 (p_api_version IN Number
656 ,p_init_msg_list IN Varchar2
657 ,p_inp_rec IN Get_pmcontin_rec
658 ,x_return_status out nocopy Varchar2
659 ,x_msg_count out nocopy Number
660 ,x_msg_data out nocopy Varchar2
661 ,x_ent_contracts out nocopy OKS_ENTITLEMENTS_PUB.get_contop_tbl
662 ,x_pm_activities out nocopy OKS_PM_ENTITLEMENTS_PUB.get_activityop_tbl)
663 IS
664
665 Lx_Inp_Rec Get_pmContIn_Rec := P_Inp_Rec;
666 Lx_ContInp_Rec OKS_ENTITLEMENTS_PVT.Inp_rec_getCont02;
667
668 BEGIN
669
670 -- Bug# 4735542
671 -- okc_context.set_okc_org_context;
672
673 Lx_ContInp_Rec.Contract_Number := Lx_Inp_Rec.Contract_Number;
674 Lx_ContInp_Rec.Contract_Number_Modifier := Lx_Inp_Rec.contract_number_modifier ;
675 Lx_ContInp_Rec.Service_Line_Id := Lx_Inp_Rec.Service_Line_Id;
676 Lx_ContInp_Rec.Party_Id := Lx_Inp_Rec.Party_Id;
677 Lx_ContInp_Rec.Site_Id := NULL;
678 Lx_ContInp_Rec.Cust_Acct_Id := NULL;
679 Lx_ContInp_Rec.System_Id := NULL;
680 Lx_ContInp_Rec.Item_Id := Lx_Inp_Rec.Item_Id;
681 Lx_ContInp_Rec.Product_Id := Lx_Inp_Rec.Product_Id;
682 Lx_ContInp_Rec.Request_Date := Lx_Inp_Rec.Request_Date;
683 Lx_ContInp_Rec.Request_Date_Start := Lx_Inp_Rec.Request_Date_Start;
684 Lx_ContInp_Rec.Request_Date_End := Lx_Inp_Rec.Request_Date_End;
685 Lx_ContInp_Rec.Business_Process_Id := NULL;
686 Lx_ContInp_Rec.Severity_Id := NULL;
687 Lx_ContInp_Rec.Time_Zone_Id := NULL;
688 Lx_ContInp_Rec.Calc_RespTime_Flag := 'N';
689 Lx_ContInp_Rec.Validate_Flag := 'Y';
690 Lx_ContInp_Rec.Validate_Eff_Flag := 'T';
691 Lx_ContInp_Rec.Sort_Key := NVL(Lx_Inp_Rec.Sort_Key,G_RESOLUTION_TIME);
692
693 IF Lx_ContInp_Rec.Request_Date IS NULL THEN
694 Lx_ContInp_Rec.Request_Date := SYSDATE;
695 END IF;
696
697 Get_PMContracts_02
698 (P_API_Version => P_Api_Version
699 ,P_Init_Msg_List => P_Init_Msg_List
700 ,P_Inp_Rec => Lx_ContInp_Rec
701 ,X_Return_Status => X_Return_Status
702 ,X_Msg_Count => X_Msg_Count
703 ,X_Msg_Data => X_Msg_Data
704 ,X_Ent_Contracts => X_Ent_Contracts
705 ,X_PM_activities => X_PM_Activities);
706
707
708 EXCEPTION
709
710 WHEN OTHERS THEN
711
712 OKC_API.SET_MESSAGE
713 (P_App_Name => G_APP_NAME_OKC
714 ,P_Msg_Name => G_UNEXPECTED_ERROR
715 ,P_Token1 => G_SQLCODE_TOKEN
716 ,P_Token1_Value => SQLCODE
717 ,P_Token2 => G_SQLERRM_TOKEN
718 ,P_Token2_Value => SQLERRM);
719
720 OKC_API.SET_MESSAGE
721 (P_App_Name => G_APP_NAME_OKC
722 ,P_Msg_Name => G_DEBUG_TOKEN
723 ,P_Token1 => G_PACKAGE_TOKEN
724 ,P_Token1_Value => G_PKG_NAME
725 ,P_Token2 => G_PROGRAM_TOKEN
726 ,P_Token2_Value => 'Get_PM_Contracts');
727
728 --X_Result := G_FALSE;
729 X_Return_Status := G_RET_STS_UNEXP_ERROR;
730
731 END Get_PM_Contracts;
732
733
734 /* old PROCEDURE Get_PM_Schedule
735 (p_api_version IN Number
736 ,p_init_msg_list IN Varchar2
737 ,p_sch_rec IN inp_sch_rec
738 ,x_return_status out nocopy Varchar2
739 ,x_msg_count out nocopy Number
740 ,x_msg_data out nocopy Varchar2
741 ,x_pm_schedule out nocopy pm_sch_tbl_type)*/
742 --ph2
743 PROCEDURE Get_PM_Schedule
744 (p_api_version IN Number
745 ,p_init_msg_list IN Varchar2
746 ,p_sch_rec IN inp_sch_rec
747 ,x_return_status out nocopy Varchar2
748 ,x_msg_count out nocopy Number
749 ,x_msg_data out nocopy Varchar2
750 ,x_pm_schedule out nocopy pm_sch_tbl_type)
751 IS
752 --ph2
753
754 --
755 -- Modified for 12.0 Coverage Rearch project (JVARGHES)
756 --
757 /*
758 CURSOR Lx_PM_Sch(Cx_SrvLine_Id IN Gx_OKS_Id, Cx_Program_Id IN NUMBER) IS -- To fetch schedule only for the program
759 SELECT cov.cle_Id SrvLine_Id,
760 okscov.pm_program_id program_id, -- pmp.object1_id1 program_id,
761 null ACTIVITY_ID,
762 sch.SCHEDULE_DATE,
763 sch.SCHEDULE_DATE_FROM,
764 sch.SCHEDULE_DATE_TO,
765 cov.end_date cov_end_date
766 FROM Okc_K_lines_B cov,
767 Oks_PM_Schedules sch,
768 -- rule rearchitecure changes
769 -- okc_rule_groups_b rgp,
770 -- OKC_rules_b pmp
771 oks_k_lines_b okscov
772 -- rule rearchitecture changes
773 WHERE cov.cle_Id =Cx_SrvLine_Id --nvl(Cx_SrvLine_Id,cov.cle_Id)
774 AND cov.lse_id in (2,15,20)
775 AND cov.dnz_chr_id = sch.dnz_chr_id
776 AND cov.id = sch.cle_id
777 -- rule rearchitecture changes
778 and sch.activity_line_id is null
779 and cov.id = okscov.cle_id
780 and okscov.pm_program_id = nvl(Cx_Program_Id,okscov.pm_program_id)
781 --03/15 chkrishn added to sort schedules returned
782 order by nvl(schedule_date,schedule_date_from);
783 -- AND cov.dnz_chr_id = rgp.dnz_chr_id
784 -- AND cov.id = rgp.cle_id
785 -- AND rgp.id = pmp.rgp_id
786 -- AND pmp.object1_id1 = nvl(Cx_Program_Id,object1_id1)
787 -- AND pmp.object1_id2 = '#'
788 -- AND pmp.jtot_object1_code = 'OKX_PMPROG'
789 -- AND pmp.rule_information_category='PMP'
790 -- AND sch.pma_rule_id is null ;
791 -- rule rearchitecture changes
792 */
793
794 --
795 -- Modified for 12.0 Coverage Rearch project (JVARGHES)
796 --
797
798 CURSOR Lx_PM_Sch(Cx_SrvLine_Id IN Gx_OKS_Id, Cx_Program_Id IN NUMBER) IS
799 SELECT srv.Id SrvLine_Id,
800 okssrv.pm_program_id program_id,
801 null ACTIVITY_ID,
802 sch.SCHEDULE_DATE,
803 sch.SCHEDULE_DATE_FROM,
804 sch.SCHEDULE_DATE_TO,
805 decode(okssrv.STANDARD_COV_YN,'Y',srv.end_date,cov.end_date) cov_end_date
806 FROM Okc_K_lines_B srv,
807 Oks_PM_Schedules sch,
808 oks_k_lines_b okssrv,
809 okc_k_lines_b cov
810 WHERE srv.Id = Cx_SrvLine_Id
811 AND srv.lse_id in (1,14,19)
812 AND srv.dnz_chr_id = sch.dnz_chr_id
813 AND srv.id = sch.cle_id
814 and sch.activity_line_id is null
815 and srv.id = okssrv.cle_id
816 and okssrv.pm_program_id = nvl(Cx_Program_Id,okssrv.pm_program_id)
817 and cov.id = okssrv.coverage_id
818 and cov.lse_id in (2,15,20)
819 order by nvl(sch.schedule_date,sch.schedule_date_from);
820
821 --
822 --
823
824 --
825 -- Modified for 12.0 Coverage Rearch project (JVARGHES)
826 --
827 /*
828 CURSOR Lx_Activity_Sch(Cx_SrvLine_Id IN Gx_OKS_Id,Cx_Program_Id IN NUMBER, Cx_Activity_Id IN NUMBER) IS -- To fetch schedule only for activity
829 SELECT cov.cle_Id SrvLine_Id,
830 okscov.pm_program_id program_id,--pma.object3_id1 program_id,
831 pmact.activity_id activity_id, --pma.object1_id1 activity_id,
832 sch.SCHEDULE_DATE,
833 sch.SCHEDULE_DATE_FROM,
834 sch.SCHEDULE_DATE_TO,
835 cov.end_date cov_end_date
836 FROM Okc_K_lines_B cov,
837 Oks_PM_Schedules sch,
838 -- rule rearchitecure changes
839 -- okc_rule_groups_b rgp,
840 -- OKC_rules_b pma
841 oks_k_lines_b okscov,
842 oks_pm_activities pmact
843 -- rule rearchitecture changes
844 WHERE cov.cle_Id =Cx_SrvLine_Id --nvl(Cx_SrvLine_Id,cov.cle_Id)
845 AND cov.lse_id in (2,15,20)
846 AND cov.dnz_chr_id = sch.dnz_chr_id
847 AND cov.id = sch.cle_id
848 -- rule rearchitecure changes
849 and okscov.cle_id = cov.id
850 and pmact.cle_id = cov.id
851 and pmact.select_yn = 'Y'
852 and sch.activity_line_id = pmact.id
853 and okscov.pm_program_id = nvl(Cx_Program_Id,okscov.pm_program_id)
854 and pmact.activity_id = nvl(Cx_Activity_Id,pmact.activity_id)
855 --03/15 chkrishn added to sort schedules returned
856 order by nvl(schedule_date,schedule_date_from);
857 -- AND cov.dnz_chr_id = rgp.dnz_chr_id
858 -- AND cov.id = rgp.cle_id
859 -- AND rgp.id = pma.rgp_id
860 -- AND pma.rule_information_category='PMA'
861 -- AND pma.rule_information1 = 'Y'
862 -- AND pma.id= sch.pma_rule_id
863 -- AND pma.object1_id1= Cx_Activity_Id
864 -- AND pma.object3_id1= nvl(Cx_Program_Id,pma.object3_id1)
865 -- AND pma.id= sch.pma_rule_id;
866 -- rule rearchitecure changes
867 */
868 --
869 -- Modified for 12.0 Coverage Rearch project (JVARGHES)
870 --
871
872 CURSOR Lx_Activity_Sch(Cx_SrvLine_Id IN Gx_OKS_Id,Cx_Program_Id IN NUMBER, Cx_Activity_Id IN NUMBER) IS -- To fetch schedule only for activity
873 SELECT srv.Id SrvLine_Id,
874 okssrv.pm_program_id program_id,
875 pmact.activity_id activity_id,
876 sch.SCHEDULE_DATE,
877 sch.SCHEDULE_DATE_FROM,
878 sch.SCHEDULE_DATE_TO,
879 decode(okssrv.STANDARD_COV_YN,'Y',srv.end_date,cov.end_date) cov_end_date
880 FROM Okc_K_lines_B srv,
881 Okc_K_lines_B cov,
882 Oks_PM_Schedules sch,
883 oks_k_lines_b okssrv,
884 oks_pm_activities pmact
885 WHERE srv.Id = Cx_SrvLine_Id
886 AND srv.lse_id in (1,14,19)
887 and okssrv.cle_id = srv.id
888 and cov.id = okssrv.coverage_id
889 AND cov.lse_id in (2,15,20)
890 AND srv.dnz_chr_id = sch.dnz_chr_id
891 AND srv.id = sch.cle_id
892 and pmact.cle_id = srv.id
893 and pmact.select_yn = 'Y'
894 and sch.activity_line_id = pmact.id
895 and okssrv.pm_program_id = nvl(Cx_Program_Id,okssrv.pm_program_id)
896 and pmact.activity_id = nvl(Cx_Activity_Id,pmact.activity_id)
897 order by nvl(sch.schedule_date,sch.schedule_date_from);
898
899 --
900 --
901 --
902 -- Modified for 12.0 Coverage Rearch project (JVARGHES)
903 --
904
905 /*
906
907 CURSOR Lx_PM_act_Sch(Cx_SrvLine_Id IN Gx_OKS_Id, Cx_Program_Id IN NUMBER) IS -- To fetch schedules for all activities of a program
908 SELECT cov.cle_Id SrvLine_Id,
909 okscov.pm_program_id program_id, --pma.object3_id1 program_id,
910 pmact.activity_id activity_id, -- pma.object1_id1 activity_id,
911 sch.SCHEDULE_DATE,
912 sch.SCHEDULE_DATE_FROM,
913 sch.SCHEDULE_DATE_TO,
914 cov.end_date cov_end_date
915 FROM Okc_K_lines_B cov,
916 Oks_PM_Schedules sch,
917 -- rule rearchitecture changes
918 -- okc_rule_groups_b rgp,
919 -- OKC_rules_b pma
920 oks_k_lines_b okscov,
921 oks_pm_activities pmact
922 -- rule rearchitecture changes
923 WHERE cov.cle_Id =Cx_SrvLine_Id --nvl(Cx_SrvLine_Id,cov.cle_Id)
924 AND cov.lse_id in (2,15,20)
925 AND cov.dnz_chr_id = sch.dnz_chr_id
926 AND cov.id = sch.cle_id
927 -- rule rearchitecture changes
928 and okscov.cle_id = cov.id
929 and okscov.pm_program_id = Cx_Program_Id
930 and pmact.cle_id = cov.id
931 and pmact.select_yn = 'Y'
932 and pmact.id = sch.activity_line_id
933 --03/15 chkrishn added to sort schedules returned
934 order by nvl(schedule_date,schedule_date_from);
935 -- AND cov.dnz_chr_id = rgp.dnz_chr_id
936 -- AND cov.id = rgp.cle_id
937 -- AND rgp.id = pma.rgp_id
938 -- AND pma.rule_information_category='PMA'
939 -- AND pma.rule_information1 = 'Y'
940 -- AND pma.id= sch.pma_rule_id
941 -- AND pma.object3_id1= Cx_Program_Id
942 -- AND pma.id= sch.pma_rule_id;
943 -- rule rearchitecture changes
944 */
945 --
946 -- Modified for 12.0 Coverage Rearch project (JVARGHES)
947 --
948 CURSOR Lx_PM_act_Sch(Cx_SrvLine_Id IN Gx_OKS_Id, Cx_Program_Id IN NUMBER) IS -- To fetch schedules for all activities of a program
949 SELECT srv.Id SrvLine_Id,
950 okssrv.pm_program_id program_id,
951 pmact.activity_id activity_id,
952 sch.SCHEDULE_DATE,
953 sch.SCHEDULE_DATE_FROM,
954 sch.SCHEDULE_DATE_TO,
955 decode(okssrv.STANDARD_COV_YN,'Y',srv.end_date,cov.end_date) cov_end_date
956 FROM Okc_K_lines_B srv,
957 Okc_K_lines_B cov,
958 Oks_PM_Schedules sch,
959 oks_k_lines_b okssrv,
960 oks_pm_activities pmact
961 WHERE srv.Id = Cx_SrvLine_Id
962 AND srv.lse_id in (1,14,19)
963 and srv.id = okssrv.cle_id
964 and cov.id = okssrv.coverage_id
965 AND cov.lse_id in (2,15,20)
966 AND srv.dnz_chr_id = sch.dnz_chr_id
967 AND srv.id = sch.cle_id
968 and okssrv.pm_program_id = Cx_Program_Id
969 and pmact.cle_id = srv.id
970 and pmact.select_yn = 'Y'
971 and pmact.id = sch.activity_line_id
972 order by nvl(sch.schedule_date,sch.schedule_date_from);
973 --
974 --
975 -- Added by Jvorugan for Bug:5357010
976 cursor check_act_sch_exist(Cx_SrvLine_Id IN Gx_OKS_Id,Cx_Activity_Id IN NUMBER)
977 is
978 select pmact.sch_exists_yn
979 from Okc_K_lines_B srv,
980 oks_pm_activities pmact
981 where srv.id = Cx_SrvLine_Id
982 and srv.lse_id in (1,14,19)
983 and pmact.cle_id = srv.id
984 and pmact.select_yn = 'Y'
985 and pmact.activity_id = Cx_Activity_Id;
986
987 l_act_sch_exist varchar2(1);
988 -- End of changes by Jvorugan
989
990 Lx_Sch_rec CONSTANT inp_sch_rec := p_sch_rec;
991 Li_OutTab_Idx BINARY_INTEGER := 0;
992 Lx_PM_Schedule pm_sch_tbl_type;
993 Lx_Return_Status Gx_Ret_Sts DEFAULT G_RET_STS_SUCCESS;
994 Lx_Sch_Stdt DATE := nvl(Lx_Sch_rec.schedule_start_date,
995 trunc(to_date('1901/01/01','YYYY/MM/DD')));
996 Lx_Sch_Endt DATE := nvl(Lx_Sch_rec.schedule_end_date,
997 trunc(to_date('2099/12/31','YYYY/MM/DD')));
998
999 BEGIN
1000 --ph2
1001 IF Lx_Sch_rec.program_id is null AND Lx_Sch_rec.activity_id is null THEN
1002 -- Validate that the contract_line_id and program_id are associated
1003 FOR Sch_Rec IN Lx_PM_Sch(Lx_Sch_rec.service_line_id,Lx_Sch_rec.activity_id) LOOP
1004
1005 IF (trunc(Sch_Rec.SCHEDULE_DATE) <= trunc(Sch_Rec.cov_end_date) OR
1006 trunc(Sch_Rec.SCHEDULE_DATE_FROM) <= trunc(Sch_Rec.cov_end_date)) THEN
1007
1008 IF ((trunc(Sch_Rec.SCHEDULE_DATE) >= trunc(Lx_Sch_Stdt) AND
1009 trunc(Sch_Rec.SCHEDULE_DATE) <= trunc(Lx_Sch_Endt)))
1010 OR
1011 ((trunc(Sch_Rec.SCHEDULE_DATE_FROM) >= trunc(Lx_Sch_Stdt) AND
1012 trunc(Sch_Rec.SCHEDULE_DATE_FROM) <= trunc(Lx_Sch_Endt))) THEN
1013
1014 Li_OutTab_Idx := Li_OutTab_Idx + 1;
1015
1016 Lx_PM_Schedule(Li_OutTab_Idx).service_line_id := Sch_Rec.SrvLine_Id;
1017 --ph2
1018 Lx_PM_Schedule(Li_OutTab_Idx).Program_id := Sch_Rec.Program_Id;
1019 Lx_PM_Schedule(Li_OutTab_Idx).Activity_id := Sch_Rec.Activity_Id;
1020 Lx_PM_Schedule(Li_OutTab_Idx).schedule_on := Sch_Rec.SCHEDULE_DATE;
1021 Lx_PM_Schedule(Li_OutTab_Idx).schedule_from := Sch_Rec.SCHEDULE_DATE_FROM;
1022 IF Sch_Rec.SCHEDULE_DATE_TO is not null THEN
1023 IF trunc(Sch_Rec.SCHEDULE_DATE_TO) <= trunc(Sch_Rec.cov_end_date) THEN
1024 Lx_PM_Schedule(Li_OutTab_Idx).schedule_to := Sch_Rec.SCHEDULE_DATE_TO;
1025 ELSE
1026 Lx_PM_Schedule(Li_OutTab_Idx).schedule_to := Sch_Rec.cov_end_date;
1027 END IF;
1028 END IF;
1029 END IF;
1030
1031 END IF;
1032 END LOOP;
1033 ELSIF Lx_Sch_rec.activity_id is not null THEN
1034 -- Added by Jvorugan for Bug:5357010
1035 -- If no schedules are defined for an activity, then
1036 -- derive the activity schedule from the program
1037 open check_act_sch_exist(Lx_Sch_rec.service_line_id,Lx_Sch_rec.activity_id);
1038 fetch check_act_sch_exist into l_act_sch_exist;
1039 close check_act_sch_exist;
1040 IF nvl(l_act_sch_exist,'Y') = 'Y'
1041 THEN
1042
1043 FOR ActSch_Rec IN Lx_Activity_Sch(Lx_Sch_rec.service_line_id,Lx_Sch_rec.program_id,Lx_Sch_rec.activity_id) LOOP
1044
1045 IF (trunc(ActSch_Rec.SCHEDULE_DATE) <= trunc(ActSch_Rec.cov_end_date) OR
1046 trunc(ActSch_Rec.SCHEDULE_DATE_FROM) <= trunc(ActSch_Rec.cov_end_date)) THEN
1047
1048 IF ((trunc(ActSch_Rec.SCHEDULE_DATE) >= trunc(Lx_Sch_Stdt) AND
1049 trunc(ActSch_Rec.SCHEDULE_DATE) <= trunc(Lx_Sch_Endt)))
1050 OR
1051 ((trunc(ActSch_Rec.SCHEDULE_DATE_FROM) >= trunc(Lx_Sch_Stdt) AND
1052 trunc(ActSch_Rec.SCHEDULE_DATE_FROM) <= trunc(Lx_Sch_Endt))) THEN
1053
1054 Li_OutTab_Idx := Li_OutTab_Idx + 1;
1055
1056 Lx_PM_Schedule(Li_OutTab_Idx).service_line_id := ActSch_Rec.SrvLine_Id;
1057 --ph2
1058 Lx_PM_Schedule(Li_OutTab_Idx).Program_id := ActSch_Rec.Program_Id;
1059 Lx_PM_Schedule(Li_OutTab_Idx).Activity_id := ActSch_Rec.Activity_Id;
1060 Lx_PM_Schedule(Li_OutTab_Idx).schedule_on := ActSch_Rec.SCHEDULE_DATE;
1061 Lx_PM_Schedule(Li_OutTab_Idx).schedule_from := ActSch_Rec.SCHEDULE_DATE_FROM;
1062
1063 IF ActSch_Rec.SCHEDULE_DATE_TO is not null THEN
1064 IF trunc(ActSch_Rec.SCHEDULE_DATE_TO) <= trunc(ActSch_Rec.cov_end_date) THEN
1065 Lx_PM_Schedule(Li_OutTab_Idx).schedule_to := ActSch_Rec.SCHEDULE_DATE_TO;
1066 ELSE
1067 Lx_PM_Schedule(Li_OutTab_Idx).schedule_to := ActSch_Rec.cov_end_date;
1068 END IF;
1069 END IF;
1070 END IF;
1071 END IF;
1072 END LOOP;
1073 ELSE -- no schedules are defined for activity.
1074 FOR Sch_Rec IN Lx_PM_Sch(Lx_Sch_rec.service_line_id,Lx_Sch_rec.program_id) LOOP
1075
1076 IF (trunc(Sch_Rec.SCHEDULE_DATE) <= trunc(Sch_Rec.cov_end_date) OR
1077 trunc(Sch_Rec.SCHEDULE_DATE_FROM) <= trunc(Sch_Rec.cov_end_date)) THEN
1078
1079 IF ((trunc(Sch_Rec.SCHEDULE_DATE) >= trunc(Lx_Sch_Stdt) AND
1080 trunc(Sch_Rec.SCHEDULE_DATE) <= trunc(Lx_Sch_Endt)))
1081 OR
1082 ((trunc(Sch_Rec.SCHEDULE_DATE_FROM) >= trunc(Lx_Sch_Stdt) AND
1083 trunc(Sch_Rec.SCHEDULE_DATE_FROM) <= trunc(Lx_Sch_Endt))) THEN
1084
1085 Li_OutTab_Idx := Li_OutTab_Idx + 1;
1086
1087 Lx_PM_Schedule(Li_OutTab_Idx).service_line_id := Sch_Rec.SrvLine_Id;
1088 --ph2
1089 Lx_PM_Schedule(Li_OutTab_Idx).Program_id := Sch_Rec.Program_Id;
1090 Lx_PM_Schedule(Li_OutTab_Idx).Activity_id := Lx_Sch_rec.activity_id;
1091 Lx_PM_Schedule(Li_OutTab_Idx).schedule_on := Sch_Rec.SCHEDULE_DATE;
1092 Lx_PM_Schedule(Li_OutTab_Idx).schedule_from := Sch_Rec.SCHEDULE_DATE_FROM;
1093
1094 IF Sch_Rec.SCHEDULE_DATE_TO is not null THEN
1095 IF trunc(Sch_Rec.SCHEDULE_DATE_TO) <= trunc(Sch_Rec.cov_end_date) THEN
1096 Lx_PM_Schedule(Li_OutTab_Idx).schedule_to := Sch_Rec.SCHEDULE_DATE_TO;
1097 ELSE
1098 Lx_PM_Schedule(Li_OutTab_Idx).schedule_to := Sch_Rec.cov_end_date;
1099 END IF;
1100 END IF;
1101
1102 END IF;
1103 END IF;
1104 END LOOP;
1105
1106 END IF; -- End of check for act_sch_exist
1107
1108 ELSIF Lx_Sch_rec.program_id is not null AND Lx_Sch_rec.activity_id is null THEN
1109 FOR Sch_Rec IN Lx_PM_Sch(Lx_Sch_rec.service_line_id,Lx_Sch_rec.program_id) LOOP
1110
1111 IF (trunc(Sch_Rec.SCHEDULE_DATE) <= trunc(Sch_Rec.cov_end_date) OR
1112 trunc(Sch_Rec.SCHEDULE_DATE_FROM) <= trunc(Sch_Rec.cov_end_date)) THEN
1113
1114 IF ((trunc(Sch_Rec.SCHEDULE_DATE) >= trunc(Lx_Sch_Stdt) AND
1115 trunc(Sch_Rec.SCHEDULE_DATE) <= trunc(Lx_Sch_Endt)))
1116 OR
1117 ((trunc(Sch_Rec.SCHEDULE_DATE_FROM) >= trunc(Lx_Sch_Stdt) AND
1118 trunc(Sch_Rec.SCHEDULE_DATE_FROM) <= trunc(Lx_Sch_Endt))) THEN
1119
1120 Li_OutTab_Idx := Li_OutTab_Idx + 1;
1121
1122 Lx_PM_Schedule(Li_OutTab_Idx).service_line_id := Sch_Rec.SrvLine_Id;
1123 --ph2
1124 Lx_PM_Schedule(Li_OutTab_Idx).Program_id := Sch_Rec.Program_Id;
1125 Lx_PM_Schedule(Li_OutTab_Idx).Activity_id := Sch_Rec.Activity_Id;
1126 Lx_PM_Schedule(Li_OutTab_Idx).schedule_on := Sch_Rec.SCHEDULE_DATE;
1127 Lx_PM_Schedule(Li_OutTab_Idx).schedule_from := Sch_Rec.SCHEDULE_DATE_FROM;
1128
1129 IF Sch_Rec.SCHEDULE_DATE_TO is not null THEN
1130 IF trunc(Sch_Rec.SCHEDULE_DATE_TO) <= trunc(Sch_Rec.cov_end_date) THEN
1131 Lx_PM_Schedule(Li_OutTab_Idx).schedule_to := Sch_Rec.SCHEDULE_DATE_TO;
1132 ELSE
1133 Lx_PM_Schedule(Li_OutTab_Idx).schedule_to := Sch_Rec.cov_end_date;
1134 END IF;
1135 END IF;
1136
1137 END IF;
1138
1139 END IF;
1140 END LOOP;
1141 FOR PActSch_Rec IN Lx_PM_Act_Sch(Lx_Sch_rec.service_line_id,Lx_Sch_rec.program_id) LOOP
1142
1143 IF (trunc(PActSch_Rec.SCHEDULE_DATE) <= trunc(PActSch_Rec.cov_end_date) OR
1144 trunc(PActSch_Rec.SCHEDULE_DATE_FROM) <= trunc(PActSch_Rec.cov_end_date)) THEN
1145
1146 IF ((trunc(PActSch_Rec.SCHEDULE_DATE) >= trunc(Lx_Sch_Stdt) AND
1147 trunc(PActSch_Rec.SCHEDULE_DATE) <= trunc(Lx_Sch_Endt)))
1148 OR
1149 ((trunc(PActSch_Rec.SCHEDULE_DATE_FROM) >= trunc(Lx_Sch_Stdt) AND
1150 trunc(PActSch_Rec.SCHEDULE_DATE_FROM) <= trunc(Lx_Sch_Endt))) THEN
1151
1152 Li_OutTab_Idx := Li_OutTab_Idx + 1;
1153
1154 Lx_PM_Schedule(Li_OutTab_Idx).service_line_id := PActSch_Rec.SrvLine_Id;
1155 --ph2
1156 Lx_PM_Schedule(Li_OutTab_Idx).Program_id := PActSch_Rec.Program_Id;
1157 Lx_PM_Schedule(Li_OutTab_Idx).Activity_id := PActSch_Rec.Activity_Id;
1158 Lx_PM_Schedule(Li_OutTab_Idx).schedule_on := PActSch_Rec.SCHEDULE_DATE;
1159 Lx_PM_Schedule(Li_OutTab_Idx).schedule_from := PActSch_Rec.SCHEDULE_DATE_FROM;
1160
1161 IF PActSch_Rec.SCHEDULE_DATE_TO is not null THEN
1162 IF trunc(PActSch_Rec.SCHEDULE_DATE_TO) <= trunc(PActSch_Rec.cov_end_date) THEN
1163 Lx_PM_Schedule(Li_OutTab_Idx).schedule_to := PActSch_Rec.SCHEDULE_DATE_TO;
1164 ELSE
1165 Lx_PM_Schedule(Li_OutTab_Idx).schedule_to := PActSch_Rec.cov_end_date;
1166 END IF;
1167 END IF;
1168 END IF;
1169 END IF;
1170 END LOOP;
1171
1172 END IF;
1173 x_pm_schedule := Lx_PM_Schedule;
1174 X_Return_Status := Lx_Return_Status;
1175
1176
1177 -- END LOOP;
1178
1179 EXCEPTION
1180
1181 WHEN OTHERS THEN
1182
1183 OKC_API.SET_MESSAGE
1184 (P_App_Name => G_APP_NAME_OKC
1185 ,P_Msg_Name => G_UNEXPECTED_ERROR
1186 ,P_Token1 => G_SQLCODE_TOKEN
1187 ,P_Token1_Value => SQLCODE
1188 ,P_Token2 => G_SQLERRM_TOKEN
1189 ,P_Token2_Value => SQLERRM);
1190
1191 OKC_API.SET_MESSAGE
1192 (P_App_Name => G_APP_NAME_OKC
1193 ,P_Msg_Name => G_DEBUG_TOKEN
1194 ,P_Token1 => G_PACKAGE_TOKEN
1195 ,P_Token1_Value => G_PKG_NAME
1196 ,P_Token2 => G_PROGRAM_TOKEN
1197 ,P_Token2_Value => 'Get_PM_Schedule');
1198
1199 --X_Result := G_FALSE;
1200 X_Return_Status := G_RET_STS_UNEXP_ERROR;
1201
1202 END Get_PM_Schedule;
1203
1204
1205 PROCEDURE Get_PM_Confirmation
1206 (p_api_version IN Number
1207 ,p_init_msg_list IN Varchar2
1208 ,p_service_line_id IN Number
1209 ,p_program_id IN Number
1210 ,p_Activity_Id IN Number
1211 ,x_return_status out nocopy Varchar2
1212 ,x_msg_count out nocopy Number
1213 ,x_msg_data out nocopy Varchar2
1214 ,x_pm_conf_reqd out nocopy Varchar2)
1215 IS
1216
1217 --
1218 -- Modified for 12.0 Coverage Rearch project (JVARGHES)
1219 --
1220 /*
1221 CURSOR Lx_PM_ConfReq(Cx_SrvLine_Id IN Gx_OKS_Id,CX_Program_Id IN NUMBER) IS
1222 SELECT pm_conf_req_yn PM_ConfReq --Rule_Information1 PM_ConfReq
1223 FROM Okc_K_lines_B cle1,
1224 Okc_K_lines_B cle2,
1225 -- rule rearchitecture changes
1226 -- Okc_Rule_Groups_B rgp,
1227 -- Okc_Rules_B rul
1228 oks_k_lines_b okscov
1229 -- rule rearchitecture changes
1230 WHERE cle1.Id = Cx_SrvLine_Id
1231 AND cle2.cle_Id = cle1.Id
1232 AND cle2.lse_id in (2,15,20)
1233 -- rule rearchitecture changes
1234 and okscov.cle_id = cle2.id
1235 AND okscov.pm_program_id = nvl(CX_Program_Id,okscov.pm_program_id);
1236 -- AND cle2.dnz_chr_id = rgp.dnz_chr_id
1237 -- AND cle2.id = rgp.cle_id
1238 -- AND rul.rgp_id = rgp.id
1239 -- AND rul.object1_id1= nvl(CX_Program_Id,rul.object1_id1)
1240 -- AND rul.rule_information_category = 'PMP';
1241 -- rule rearchitecture changes
1242 */
1243 --
1244 -- Modified for 12.0 Coverage Rearch project (JVARGHES)
1245 --
1246
1247 CURSOR Lx_PM_ConfReq(Cx_SrvLine_Id IN Gx_OKS_Id,CX_Program_Id IN NUMBER) IS
1248 SELECT pm_conf_req_yn PM_ConfReq --Rule_Information1 PM_ConfReq
1249 FROM Okc_K_lines_B cle1,
1250 oks_k_lines_b okssrv
1251 WHERE cle1.Id = Cx_SrvLine_Id
1252 and okssrv.cle_id = cle1.id
1253 AND okssrv.pm_program_id = nvl(CX_Program_Id,okssrv.pm_program_id);
1254
1255 --
1256 --
1257 --
1258 -- Modified for 12.0 Coverage Rearch project (JVARGHES)
1259 --
1260 /*
1261 CURSOR Lx_Act_ConfReq(Cx_SrvLine_Id IN Gx_OKS_Id,CX_Program_Id IN NUMBER,CX_Activity_Id IN NUMBER) IS
1262 SELECT pmact.Conf_req_yn Act_ConfReq -- PMA.Rule_Information2 Act_ConfReq
1263 FROM Okc_K_lines_B cle1,
1264 Okc_K_lines_B cle2,
1265 -- rule rearchitecture changes
1266 oks_k_lines_b okscov,
1267 oks_pm_activities pmact
1268 -- Okc_Rule_Groups_B rgp,
1269 -- Okc_Rules_B pma
1270 -- rule rearchitecture changes
1271 WHERE cle1.Id = Cx_SrvLine_Id
1272 AND cle1.dnz_chr_id = cle2.dnz_chr_id
1273 AND cle2.cle_Id = cle1.Id
1274 AND cle2.lse_id in (2,15,20)
1275 -- rule rearchitecture changes
1276 and okscov.cle_id = cle2.id
1277 and okscov.pm_program_id = nvl(CX_Program_Id,okscov.pm_program_id)
1278 and pmact.cle_id = cle2.id
1279 and pmact.select_yn = 'Y'
1280 and pmact.activity_id = CX_Activity_Id;
1281 -- AND cle2.dnz_chr_id = rgp.dnz_chr_id
1282 -- AND cle2.id = rgp.cle_id
1283 -- AND rgp.id = pma.rgp_id
1284 -- AND pma.object3_id1 = nvl(CX_Program_Id,pma.object3_id1)
1285 -- and pma.object1_id1 = CX_Activity_Id
1286 -- AND pma.rule_information_category = 'PMA';
1287 -- rule rearchitecture changes
1288 */
1289 --
1290 -- Modified for 12.0 Coverage Rearch project (JVARGHES)
1291 --
1292 CURSOR Lx_Act_ConfReq(Cx_SrvLine_Id IN Gx_OKS_Id,CX_Program_Id IN NUMBER,CX_Activity_Id IN NUMBER) IS
1293 SELECT pmact.Conf_req_yn Act_ConfReq -- PMA.Rule_Information2 Act_ConfReq
1294 FROM Okc_K_lines_B cle1,
1295 oks_k_lines_b okssrv,
1296 oks_pm_activities pmact
1297 WHERE cle1.Id = Cx_SrvLine_Id
1298 and okssrv.cle_id = cle1.id
1299 and okssrv.pm_program_id = nvl(CX_Program_Id,okssrv.pm_program_id)
1300 and pmact.cle_id = cle1.id
1301 and pmact.select_yn = 'Y'
1302 and pmact.activity_id = CX_Activity_Id;
1303
1304 --
1305 -- Modified for 12.0 Coverage Rearch project (JVARGHES)
1306 --
1307
1308 Lx_SrvLine_Id NUMBER := p_service_line_id;
1309 --Added for ph2
1310 Lx_Program_Id NUMBER := p_program_id ;
1311 Lx_Activity_Id NUMBER := p_activity_id ;
1312
1313 Lx_Return_Status Gx_Ret_Sts DEFAULT G_RET_STS_SUCCESS;
1314
1315
1316 BEGIN
1317
1318 x_pm_conf_reqd := 'N';
1319
1320 IF Lx_Activity_Id is null THEN --Change for ph2
1321
1322 FOR PM_ConfReq_rec IN Lx_PM_ConfReq(Lx_SrvLine_Id,Lx_Program_Id) LOOP
1323
1324 x_pm_conf_reqd := PM_ConfReq_rec.PM_ConfReq;
1325
1326 END LOOP;
1327
1328 ELSE
1329
1330 FOR Act_ConfReq_rec IN Lx_Act_ConfReq(Lx_SrvLine_Id,Lx_Program_Id,Lx_Activity_Id) LOOP
1331
1332 x_pm_conf_reqd := Act_ConfReq_rec.Act_ConfReq;
1333
1334 END LOOP;
1335
1336 END IF;
1337 X_Return_Status := Lx_Return_Status;
1338
1339 EXCEPTION
1340
1341 WHEN OTHERS THEN
1342
1343 OKC_API.SET_MESSAGE
1344 (P_App_Name => G_APP_NAME_OKC
1345 ,P_Msg_Name => G_UNEXPECTED_ERROR
1346 ,P_Token1 => G_SQLCODE_TOKEN
1347 ,P_Token1_Value => SQLCODE
1348 ,P_Token2 => G_SQLERRM_TOKEN
1349 ,P_Token2_Value => SQLERRM);
1350
1351 OKC_API.SET_MESSAGE
1352 (P_App_Name => G_APP_NAME_OKC
1353 ,P_Msg_Name => G_DEBUG_TOKEN
1354 ,P_Token1 => G_PACKAGE_TOKEN
1355 ,P_Token1_Value => G_PKG_NAME
1356 ,P_Token2 => G_PROGRAM_TOKEN
1357 ,P_Token2_Value => 'Get_PM_Confirmation');
1358
1359 --X_Result := G_FALSE;
1360 X_Return_Status := G_RET_STS_UNEXP_ERROR;
1361
1362 END Get_PM_Confirmation;
1363 --chkrishn 02/25/2004 modified to accept p_pm_activity_id parameter
1364 PROCEDURE Check_PM_Exists
1365 (p_api_version IN Number
1366 ,p_init_msg_list IN Varchar2
1367 ,p_pm_program_id IN Number default null
1368 ,p_pm_activity_id IN Number default null
1369 ,x_return_status out nocopy Varchar2
1370 ,x_msg_count out nocopy Number
1371 ,x_msg_data out nocopy Varchar2
1372 ,x_pm_reference_exists out nocopy Varchar2)
1373 IS
1374
1375 --
1376 -- Modified for 12.0 Coverage Rearch project (JVARGHES)
1377 --
1378 /*
1379 CURSOR Lx_PM_exist(Cx_PM_Id IN NUMBER) IS
1380 SELECT sts.ste_code
1381 from
1382 okc_k_lines_b cle,
1383 okc_k_lines_b covcle,
1384 oks_k_lines_b cov,
1385 okc_statuses_b sts
1386 where
1387 cle.id=covcle.cle_id
1388 and covcle.id=cov.cle_id
1389 and cle.sts_code=sts.code
1390 and sts.ste_code in ('ACTIVE','ENTERED','HOLD')
1391 and cov.pm_program_id=Cx_PM_Id;
1392 */
1393 --
1394 -- Modified for 12.0 Coverage Rearch project (JVARGHES)
1395 --
1396 CURSOR Lx_PM_exist(Cx_PM_Id IN NUMBER) IS
1397 SELECT sts.ste_code
1398 from
1399 okc_k_lines_b cle,
1400 oks_k_lines_b ksl,
1401 okc_statuses_b sts
1402 where
1403 cle.id = ksl.cle_id
1404 and cle.sts_code=sts.code
1405 and sts.ste_code in ('ACTIVE','ENTERED','HOLD')
1406 and ksl.pm_program_id = Cx_PM_Id;
1407 --
1408 --
1409
1410 --
1411 -- Modified for 12.0 Coverage Rearch project (JVARGHES)
1412 --
1413 /*
1414 CURSOR Lx_Act_exist(Cx_Act_Id IN NUMBER) IS
1415 select
1416 sts.ste_code
1417 from
1418 okc_k_lines_b cle,
1419 okc_k_lines_b covcle,
1420 oks_pm_activities act,
1421 okc_statuses_b sts
1422 where
1423 cle.id=covcle.cle_id
1424 and cle.sts_code=sts.code
1425 and covcle.id=act.cle_id
1426 and sts.ste_code in ('ACTIVE','ENTERED','HOLD')
1427 and act.activity_id=Cx_Act_Id;
1428 */
1429 --
1430 -- Modified for 12.0 Coverage Rearch project (JVARGHES)
1431 --
1432 CURSOR Lx_Act_exist(Cx_Act_Id IN NUMBER) IS
1433 select
1434 sts.ste_code
1435 from
1436 okc_k_lines_b cle,
1437 oks_pm_activities act,
1438 okc_statuses_b sts
1439 where
1440 cle.sts_code = sts.code
1441 and cle.id = act.cle_id
1442 and sts.ste_code in ('ACTIVE','ENTERED','HOLD')
1443 and act.activity_id = Cx_Act_Id;
1444
1445 --
1446 --
1447 Lx_Return_Status Gx_Ret_Sts DEFAULT G_RET_STS_SUCCESS;
1448 Lx_pm_exists VARCHAR2(1) := 'N';
1449 L_EXCEP_UNEXPECTED_ERR EXCEPTION;
1450
1451 BEGIN
1452 --if neither activity id nor program id are passed , raise exception
1453
1454 IF p_pm_program_id IS NULL and p_pm_activity_id is NULL THEN
1455 raise L_EXCEP_UNEXPECTED_ERR;
1456 END IF;
1457
1458 IF p_pm_program_id IS NOT NULL THEN
1459 FOR PM_exists_rec IN Lx_PM_exist(p_pm_program_id) LOOP
1460 X_Return_Status := Lx_Return_Status;
1461 Lx_pm_exists := 'Y';
1462 exit;
1463 END LOOP;
1464
1465 x_pm_reference_exists := Lx_pm_exists;
1466 X_Return_Status := Lx_Return_Status;
1467 END IF;
1468 IF p_pm_activity_id IS NOT NULL AND p_pm_program_id is null THEN
1469 FOR Act_exists_rec IN Lx_Act_exist(p_pm_activity_id) LOOP
1470 X_Return_Status := Lx_Return_Status;
1471 Lx_pm_exists := 'Y';
1472 exit;
1473 END LOOP;
1474
1475 x_pm_reference_exists := Lx_pm_exists;
1476 X_Return_Status := Lx_Return_Status;
1477 END IF;
1478
1479
1480 EXCEPTION
1481
1482 WHEN L_EXCEP_UNEXPECTED_ERR THEN
1483 OKC_API.SET_MESSAGE
1484 (P_App_Name => G_APP_NAME_OKC
1485 ,P_Msg_Name => G_DEBUG_TOKEN
1486 ,P_Token1 => G_PACKAGE_TOKEN
1487 ,P_Token1_Value => G_PKG_NAME
1488 ,P_Token2 => G_PROGRAM_TOKEN
1489 ,P_Token2_Value => 'Check_PM_Exists');
1490
1491 X_Return_Status := G_RET_STS_ERROR;
1492
1493 WHEN OTHERS THEN
1494
1495 OKC_API.SET_MESSAGE
1496 (P_App_Name => G_APP_NAME_OKC
1497 ,P_Msg_Name => G_UNEXPECTED_ERROR
1498 ,P_Token1 => G_SQLCODE_TOKEN
1499 ,P_Token1_Value => SQLCODE
1500 ,P_Token2 => G_SQLERRM_TOKEN
1501 ,P_Token2_Value => SQLERRM);
1502
1503 OKC_API.SET_MESSAGE
1504 (P_App_Name => G_APP_NAME_OKC
1505 ,P_Msg_Name => G_DEBUG_TOKEN
1506 ,P_Token1 => G_PACKAGE_TOKEN
1507 ,P_Token1_Value => G_PKG_NAME
1508 ,P_Token2 => G_PROGRAM_TOKEN
1509 ,P_Token2_Value => 'Check_PM_Exists');
1510
1511 X_Return_Status := G_RET_STS_UNEXP_ERROR;
1512
1513 END Check_PM_Exists;
1514
1515
1516 END OKS_PM_ENTITLEMENTS_PVT;