1 PACKAGE BODY PSP_ORG_DLS_PKG AS
2 /* $Header: PSPLSDLB.pls 120.1 2006/11/07 06:08:58 tbalacha noship $ */
3 -- Package level variable
4 p_Batch_Sequence_number Number;
5
6 /***************************************************************************************************/
7 -- PRIVATE PROCEDURE AND FUNCTION DEFINITIONS
8 Function Process_Assgn_Level_Schedule(l_Time_Period_Start DATE, l_Time_Period_End DATE,
9 l_Assignment_ID Number, l_Element_Type_ID Number,l_AS_Period1_Start IN OUT NOCOPY DATE,
10 l_AS_Period1_End IN OUT NOCOPY DATE, l_AS_Period2_Start IN OUT NOCOPY DATE, l_AS_Period2_End IN OUT NOCOPY DATE) return NUMBER;
11
12 Function Process_Element_Group_Schedule(l_Time_Period_Start DATE, l_Time_Period_End DATE,
13 l_Assignment_ID Number, l_Element_Type_ID Number,l_EG_Period1_Start IN OUT NOCOPY DATE,
14 l_EG_Period1_End IN OUT NOCOPY DATE, l_EG_Period2_Start IN OUT NOCOPY DATE, l_EG_Period2_End IN OUT NOCOPY DATE)
15 return NUMBER;
16
17 Function Process_Glob_Elem_Schedule(l_Assignment_ID Number, l_Element_Type_ID Number,
18 l_Time_Period_Start DATE, l_Time_Period_End DATE,
19 l_GE_Period1_Start IN OUT NOCOPY DATE, l_GE_Period1_End IN OUT NOCOPY DATE,
20 l_GE_Period2_Start IN OUT NOCOPY DATE, l_GE_Period2_End IN OUT NOCOPY DATE) return NUMBER;
21
22 PROCEDURE Calculate_Split_Periods(l_Assignment_Id Number, l_Schedule_Begin_Date DATE, l_Schedule_End_Date DATE ,
23 l_Time_Period_Start DATE, l_Time_Period_End DATE, l_Period1_Start IN OUT NOCOPY DATE, l_Period1_End IN OUT NOCOPY
24 DATE, l_Period2_Start IN OUT NOCOPY DATE, l_Period2_End IN OUT NOCOPY DATE);
25
26 PROCEDURE Get_Split_Before(l_Assignment_id Number, l_Schedule_begin_date DATE, l_schedule_end_date DATE,
27 l_Time_period_Start DATE, l_Time_Period_End DATE, l_Period1_start IN OUT NOCOPY DATE,
28 l_Period1_End IN OUT NOCOPY DATE);
29
30 PROCEDURE Get_Split_After(l_Assignment_Id Number, l_Schedule_begin_date DATE, l_schedule_end_date DATE,
31 l_Time_Period_Start DATE, l_Time_Period_End DATE,
32 l_Period2_Start IN OUT NOCOPY DATE, l_Period2_End IN OUT NOCOPY DATE);
33
34 PROCEDURE Table_Insert(Organization_ID Number, Element_Type_ID Number DEFAULT NULL, Person_ID Number,
35 Assignment_ID Number, Begin_Date Date, End_Date Date, Original_Sch_Code Varchar2,
36 Schedule_Line_ID Number);
37
38 PROCEDURE Process_All_ET_Schedules(p_template_id number, p_start_date DATE,p_end_date DATE);
39 PROCEDURE Process_All_EG_Schedules(p_template_id number, p_start_date DATE,p_end_date DATE);
40 PROCEDURE Process_All_ASSGN_Schedules(p_template_id number, p_start_date DATE,p_end_date DATE);
41 PROCEDURE Add_If_Within_DLS(v_Organization_ID Number, v_Begin_Date DATE,
42 v_End_Date DATE, v_Assignment_ID Number, v_Person_ID Number, v_Element_Type_ID Number,
43 v_Original_Sch_Code varchar2, v_Schedule_Line_ID Number);
44 /***************************************************************************************************/
45
46 Function Insert_Records_To_Table(p_template_id number, p_start_date DATE,p_end_date DATE,p_set_of_books_id number,p_business_group_id number) return NUMBER IS
47
48 l_cur_handle1 INTEGER;
49 l_total_rows INTEGER := 0;
50
51 l_Assignment_ID NUMBER(22,2) := 0;
52 l_Organization_ID Number(22,2) := 0;
53 l_Person_ID Number(22,0) := 0;
54 l_Element_Type_ID Number(22,0) DEFAULT NULL;
55 l_Schedule_Begin_Date DATE;
56 l_Schedule_End_Date DATE;
57 l_Time_Period_Start_Date DATE;
58 l_Time_Period_End_Date DATE;
59
60 v_Assgn_List_To_Add varchar2(200);
61
62 v_Scheduling_type_code varchar2(2);
63 b_Goto_Next_Assignment boolean;
64 b_Goto_NExt_Element_Type boolean;
65
66 l_organization_str Varchar2(2000);
67 l_org_count Number;
68
69
70
71 cursor Org_count is
72 select count(1)
73 from psp_report_template_details
74 Where template_id = p_template_id
75 and CRITERIA_LOOKUP_TYPE = 'PSP_SELECTION_CRITERIA'
76 and CRITERIA_LOOKUP_CODE = 'ORG';
77
78 retVal Number := 0;
79
80
81
82 Begin
83 --Resetting the Batch Sequence Number
84 p_Batch_Sequence_Number := NULL;
85
86 g_element_type_id_str := '( Select DISTINCT a.ELEMENT_TYPE_ID
87 from PSP_ELEMENT_TYPES a, PAY_ELEMENT_TYPES_F b
88 where a.ELEMENT_TYPE_ID = b.ELEMENT_TYPE_ID
89 AND a.business_group_id = ' || p_business_group_id || '
90 AND a.set_of_books_id = ' || p_set_of_books_id || ')' ;
91
92
93
94
95 /*******************************************************************************************************
96 First, need to check if the assignments from the selected organizations exist in the PSP_SCHEDULES table.
97 If they do not, then, we need to add these assignments to the report to be displayed.
98 This is pretty straight-forward. If they do, then, we need to proceed to the next step.
99 *******************************************************************************************************/
100 -- First, obtain the Start and End Dates for assignments not existing in
101 -- PSP_SCHEDULE_HIERARCHY=> Time Period Begin and End Dates
102 /* For bug
103 Select Start_Date, End_Date
104 into l_Time_Period_Start_Date, l_Time_Period_End_Date
105 from PER_TIME_PERIODS
106 whee Time_Period_ID = v_Time_Period_ID; */
107
108 l_Time_Period_Start_Date := p_start_date ;
109 l_Time_Period_End_Date := p_end_date;
110
111 If (p_template_id is null ) Then
112
113 g_organization_str := ' 1 = 1 ' ;
114
115 Else
116 Open Org_count;
117 Fetch Org_count into l_org_count;
118 Close Org_count;
119
120 If l_org_count<> 0 then
121
122 g_organization_str := ' a.Organization_id in (select to_number(criteria_value1)
123 from psp_report_template_details
124 where template_id = '|| p_template_id || '
125 and CRITERIA_LOOKUP_TYPE = ''PSP_SELECTION_CRITERIA''
126 and CRITERIA_LOOKUP_CODE = ''ORG'' ) ' ;
127
128
129 else
130
131 g_organization_str := ' 1 = 1 ' ;
132
133 end if ;
134
135 End If;
136
137
138
139
140 -- dbms_output.put_line('start date is ' || to_char(l_Time_Period_Start_Date));
141
142 l_Cur_Handle1 := dbms_sql.open_cursor;
143
144 --dbms_output.put_line('tried opening cursor,....valueis ' || to_char(l_Cur_Handle1));
145
146 dbms_sql.parse(l_cur_handle1, 'Select DISTINCT a.Assignment_ID ASSIGNMENT_ID,
147 a.Organization_ID ORGANIZATION_ID,
148 a.Person_ID PERSON_ID
149 from PER_ASSIGNMENTS_F a
150 where ' || g_organization_str || '
151 and a.business_group_id = '|| p_business_group_id ||'
152 and a.payroll_id in ( select payroll_id from
153 pay_payrolls_f where gl_set_of_books_id = '|| p_set_of_books_id || ')
154 and ( :p_end_date between a.EFFECTIVE_START_DATE
155 and a.EFFECTIVE_END_DATE)
156 and Not(Exists(Select ''X''
157 from PSP_SCHEDULE_HIERARCHY SCHI
158 ,PSP_SCHEDULE_LINES SCHL
159 where SCHI.Assignment_ID = a.ASSIGNMENT_ID
160 and SCHI.SCHEDULE_HIERARCHY_ID = SCHL.SCHEDULE_HIERARCHY_ID
161 and ((SCHL.SCHEDULE_BEGIN_DATE between :p_start_date and :p_end_date )
162 or (SCHL.SCHEDULE_END_DATE between :p_start_date and :p_end_date )
163 or (SCHL.SCHEDULE_BEGIN_DATE <= :p_start_date and SCHL.SCHEDULE_END_DATE
164 >= :p_end_date ))))', dbms_sql.V7);
165
166 dbms_sql.bind_variable(l_cur_handle1, ':p_start_date', p_start_date);
167 dbms_sql.bind_variable(l_cur_handle1, ':p_end_date', p_end_date);
168
169 dbms_sql.define_column(l_cur_handle1, 1, l_Assignment_ID);
170 dbms_sql.define_column(l_cur_handle1, 2, l_Organization_ID);
171 dbms_sql.define_column(l_cur_handle1, 3, l_Person_ID);
172
173 l_total_rows := dbms_sql.execute(l_cur_handle1);
174 while dbms_sql.fetch_rows(l_cur_handle1) > 0
175 LOOP
176 dbms_sql.column_value(l_cur_handle1, 1, l_Assignment_ID);
177 dbms_sql.column_value(l_cur_handle1, 2, l_Organization_ID);
178 dbms_sql.column_value(l_cur_handle1, 3, l_Person_ID);
179 -- Obtain the Part of Period1 that is covered by a PSP
180 -- Default Labor Schedule. Add corresponding records to PSP_REP_ORG_DLS
181 -- table
182 Add_If_Within_DLS(v_Organization_ID => l_Organization_ID,
183 v_Begin_Date => l_Time_Period_Start_Date, v_End_Date => l_Time_Period_End_Date,
184 v_Assignment_ID => l_Assignment_ID, v_Person_ID => l_Person_ID,
185 v_Element_Type_ID => l_Element_Type_ID, v_Original_Sch_Code => 'O',
186 v_Schedule_Line_ID => '');
187 --dbms_output.put_line('After calling the Table_Insert Procedure');
188 END LOOP;
189 --dbms_output.put_line('Have to insert Assignments : ' || v_Assgn_List_To_Add || ' to Report');
190
191 dbms_sql.close_cursor(l_cur_handle1);
192
193 /*******************************************************************************************************
194 Next, we need to check if all assignments that have scheduling code of 'ET', have schedule begin
195 and end dates that fully encapsulate the selected time period. If they do, then, we NEED NOT
196 display these assignments.
197 If they do not, then we should obtain the time periods that are not covered and proceed to the
198 next step (viz Check if the periods are covered by Element Group schedules and Assignment Level
199 Schedules).
200 *******************************************************************************************************/
201 Process_All_ET_Schedules(p_template_id , p_start_date ,p_end_date);
202 --dbms_output.put_line('Successfully processed ET Schedules');
203
204 /*******************************************************************************************************
205 Next, we need to check if all assignments that have scheduling code of 'EG', have schedule begin
206 and end dates that fully encapsulate the selected time period. If they do, then, we NEED NOT
207 display these assignments.
208 If they do not, then we should obtain the time periods that are not covered and proceed to the
209 next step (viz Check if the periods are covered by Assignment Level Schedules).
210 *******************************************************************************************************/
211 Process_All_EG_Schedules(p_template_id , p_start_date ,p_end_date);
212 -- dbms_output.put_line('Successfully processed EG Schedules');
213
214 /*******************************************************************************************************
215 Finally, we need to check if all assignments that have scheduling code of 'AS', have schedule
216 begin and end dates that fully encapsulate the selected time period. If they do, then, we NEED
217 NOT display these assignments.
218 If they do not, then we should display these records.
219 *******************************************************************************************************/
220 Process_All_ASSGN_Schedules(p_template_id , p_start_date ,p_end_date);
221 --dbms_output.put_line('Successfully processed ASSGN Schedules..p_batch seq is ' || to_char(p_Batch_Sequence_Number));
222
223 return p_Batch_Sequence_Number;
224
225 Exception
226 when OTHERS Then
227 --dbms_output.put_line('ERROR ENCOUNTERED WHILE PROCESSING PACKAGE:' || sqlerrm);
228 return 0;
229
230 End Insert_Records_To_Table;
231
232 Procedure Process_All_ASSGN_Schedules(p_template_id number, p_start_date DATE,p_end_date DATE) IS
233 l_cur_handle INTEGER ;
234 l_total_rows INTEGER := 0;
235
236 l_Assignment_ID NUMBER(22,2) := 0;
237 l_Organization_ID Number(22,2) := 0;
238 l_Person_ID Number(22,0) := 0;
239 l_Element_Type_ID Number(22,0) := 0;
240 l_Schedule_Begin_Date DATE;
241 l_Schedule_End_Date DATE;
242 l_Time_Period_Start_Date DATE;
243 l_Time_Period_End_Date DATE;
244 l_Schedule_Line_ID Number(22,0) := 0;
245
246 -- Variables used to store the start and the end dates of two possible periods
247 -- (Period1 => Before the Selected Time Period Start Date,
248 -- Period2 => After the Selected Time Period End Date) for 'GLOBAL ELEMENT LEVEL SCHEDULES'
249 l_GE_Period1_Start Date;
250 l_GE_Period1_End Date;
251 l_GE_Period2_Start Date;
252 l_GE_Period2_End Date;
253
254 -- Variables used to store the start and the end dates of two possible periods
255 -- (Period1 => Before the Selected Time Period Start Date,
256 -- Period2 => After the Selected Time Period End Date) for 'ASSIGNMENT LEVEL SCHEDULES'
257 l_AS_Period1_Start Date;
258 l_AS_Period1_End Date;
259 l_AS_Period2_Start Date;
260 l_AS_Period2_End Date;
261
262 v_Assgn_List_To_Add varchar2(200);
263
264 v_Scheduling_type_code varchar2(2);
265 b_Goto_Next_Assignment boolean;
266 b_Goto_NExt_Element_Type boolean;
267 retVal Number := 0;
268 Begin
269
270
271 l_cur_handle := dbms_sql.open_cursor;
272 -- dbms_output.put_line('About to parse Assignment Level SQL');
273 dbms_sql.parse(l_cur_handle, 'SELECT DISTINCT a.ASSIGNMENT_ID ASSIGNMENT_ID,
274 a.ORGANIZATION_ID ORGANIZATION_ID,
275 b.PERSON_ID PERSON_ID,
276 c.ELEMENT_TYPE_ID ELEMENT_TYPE_ID,
277 d.SCHEDULE_LINE_ID SCHEDULE_LINE_ID,
278 d.SCHEDULE_BEGIN_DATE SCHEDULE_BEGIN_DATE,
279 d.SCHEDULE_END_DATE SCHEDULE_END_DATE,
280 fnd_date.canonical_to_date('''|| fnd_date.date_to_canonical(p_start_date) ||''') TIME_PERIOD_START_DATE,
281 fnd_date.canonical_to_date('''|| fnd_date.date_to_canonical(p_end_date) ||''') TIME_PERIOD_END_DATE
282 from PER_ASSIGNMENTS_F a,
283 PSP_SCHEDULES b,
284 PSP_SCHEDULE_HIERARCHY c,
285 PSP_SCHEDULE_LINES d,
286 PSP_DEFAULT_LABOR_SCHEDULES f
287 where a.ASSIGNMENT_ID = b.ASSIGNMENT_ID
288 and b.ASSIGNMENT_ID = c.ASSIGNMENT_ID
289 and c.SCHEDULE_HIERARCHY_ID = d.SCHEDULE_HIERARCHY_ID
290 and NOT(d.SCHEDULE_BEGIN_DATE <= :p_start_date AND
291 d.SCHEDULE_END_DATE >= :p_end_date )
292 and NOT(d.SCHEDULE_BEGIN_DATE > :p_end_date OR
293 d.SCHEDULE_END_DATE < :p_start_date )
294 and (:p_end_date BETWEEN a.EFFECTIVE_START_DATE and
295 a.EFFECTIVE_END_DATE)
296 and c.SCHEDULING_TYPES_CODE = ''A''
297 and a.ORGANIZATION_ID = f.ORGANIZATION_ID
298 and (:p_start_date >= f.SCHEDULE_BEGIN_DATE
299 or :p_end_date <= f.SCHEDULE_END_DATE
300 or ( :p_start_date <= f.SCHEDULE_BEGIN_DATE and
301 :p_END_DATE >= f.SCHEDULE_END_DATE))
302 and ' || g_organization_str ,
303 dbms_sql.V7);
304 dbms_sql.bind_variable(l_cur_handle, ':p_start_date', p_start_date);
305 dbms_sql.bind_variable(l_cur_handle, ':p_end_date', p_end_date);
306
307
308 -- dbms_output.put_line('About to define numeric columns.');
309 dbms_sql.define_column(l_cur_handle, 1, l_Assignment_ID);
310 dbms_sql.define_column(l_cur_handle, 2, l_Organization_ID);
311 dbms_sql.define_column(l_cur_handle, 3, l_Person_ID);
312 dbms_sql.define_column(l_cur_handle, 4, l_Element_Type_ID);
313 dbms_sql.define_column(l_cur_handle, 5, l_Schedule_Line_ID);
314
315 -- dbms_output.put_line('About to define Date columns.');
316 dbms_sql.define_column(l_cur_handle, 6, l_Schedule_Begin_Date);
317 dbms_sql.define_column(l_cur_handle, 7, l_Schedule_End_Date);
318 dbms_sql.define_column(l_cur_handle, 8, l_Time_Period_Start_Date);
319 dbms_sql.define_column(l_cur_handle, 9, l_Time_Period_End_Date);
320
321 l_total_rows := dbms_sql.execute(l_cur_handle);
322 while dbms_sql.fetch_rows(l_cur_handle) > 0
323 LOOP
324 -- dbms_output.put_line('Inside loop of Assignment Level Schedules');
325 dbms_sql.column_value(l_cur_handle, 1, l_Assignment_ID);
326 dbms_sql.column_value(l_cur_handle, 2, l_Organization_ID);
327 dbms_sql.column_value(l_cur_handle, 3, l_Person_ID);
328 dbms_sql.column_value(l_cur_handle, 4, l_Element_Type_ID);
329 dbms_sql.column_value(l_cur_handle, 5, l_Schedule_Line_ID);
330 dbms_sql.column_value(l_cur_handle, 6, l_Schedule_Begin_Date);
331 dbms_sql.column_value(l_cur_handle, 7, l_Schedule_End_Date);
332 dbms_sql.column_value(l_cur_handle, 8, l_Time_Period_Start_Date);
333 dbms_sql.column_value(l_cur_handle, 9, l_Time_Period_End_Date);
334
335
336 retVal := Process_Glob_Elem_Schedule(l_Assignment_ID, l_Element_Type_ID,
337 l_Time_Period_Start_Date, l_Time_Period_End_Date, l_GE_Period1_Start,
338 l_GE_Period1_End, l_GE_Period2_Start, l_GE_Period2_End);
339
340 If l_GE_Period1_Start IS NOT NULL Then
341
342 -- dbms_output.put_line('Before Calculating Split Periods for Assignment Level
343 -- Schedules');
344 Calculate_Split_Periods(l_Assignment_Id, l_Schedule_Begin_Date, l_Schedule_End_Date,
345 l_GE_Period1_Start, l_GE_Period1_End, l_AS_Period1_Start,
346 l_AS_Period1_End, l_AS_Period2_Start, l_AS_Period2_End);
347
348 If l_AS_Period1_Start IS NOT NULL then
349 -- Obtain the Part of Period1 that is covered by a PSP
350 -- Default Labor Schedule. Add corresponding records to PSP_REP_ORG_DLS
351 -- table
352 Add_If_Within_DLS(v_Organization_ID => l_Organization_ID,
353 v_Begin_Date => l_AS_Period1_Start, v_End_Date => l_AS_Period1_End,
354 v_Assignment_ID => l_Assignment_ID, v_Person_ID => l_Person_ID,
355 v_Element_Type_ID => l_Element_Type_ID, v_Original_Sch_Code => 'A',
356 v_Schedule_Line_ID => l_Schedule_Line_ID);
357 -- dbms_output.put_line('After calling the Table_Insert Procedure');
358 End If;
359
360 If l_AS_Period2_Start IS NOT NULL then
361 -- Obtain the Part of Period1 that is covered by a PSP
362 -- Default Labor Schedule. Add corresponding records to PSP_REP_ORG_DLS
363 -- table
364 Add_If_Within_DLS(v_Organization_ID => l_Organization_ID,
365 v_Begin_Date => l_AS_Period2_Start, v_End_Date => l_AS_Period2_End,
366 v_Assignment_ID => l_Assignment_ID, v_Person_ID => l_Person_ID,
367 v_Element_Type_ID => l_Element_Type_ID, v_Original_Sch_Code => 'A',
368 v_Schedule_Line_ID => l_Schedule_Line_ID);
369 -- dbms_output.put_line('After calling the Table_Insert Procedure');
370 End If;
371 End If;
372
373 If l_GE_Period2_Start IS NOT NULL Then
374
375 -- dbms_output.put_line('Before Calculating Split Periods for Assignment Level
376 -- Schedules');
377 Calculate_Split_Periods(l_Assignment_Id, l_Schedule_Begin_Date, l_Schedule_End_Date,
378 l_GE_Period2_Start, l_GE_Period2_End, l_AS_Period1_Start,
379 l_AS_Period1_End, l_AS_Period2_Start, l_AS_Period2_End);
380
381 If l_AS_Period1_Start IS NOT NULL then
382 -- Obtain the Part of Period1 that is covered by a PSP
383 -- Default Labor Schedule. Add corresponding records to PSP_REP_ORG_DLS
384 -- table
385 Add_If_Within_DLS(v_Organization_ID => l_Organization_ID,
386 v_Begin_Date => l_AS_Period1_Start, v_End_Date => l_AS_Period1_End,
387 v_Assignment_ID => l_Assignment_ID, v_Person_ID => l_Person_ID,
388 v_Element_Type_ID => l_Element_Type_ID, v_Original_Sch_Code => 'A',
389 v_Schedule_Line_ID => l_Schedule_Line_ID);
390 --dbms_output.put_line('After calling the Table_Insert Procedure');
391 End If;
392
393 If l_AS_Period2_Start IS NOT NULL then
394 -- Obtain the Part of Period1 that is covered by a PSP
395 -- Default Labor Schedule. Add corresponding records to PSP_REP_ORG_DLS
396 -- table
397 Add_If_Within_DLS(v_Organization_ID => l_Organization_ID,
398 v_Begin_Date => l_AS_Period2_Start, v_End_Date => l_AS_Period2_End,
399 v_Assignment_ID => l_Assignment_ID, v_Person_ID => l_Person_ID,
400 v_Element_Type_ID => l_Element_Type_ID, v_Original_Sch_Code => 'A',
401 v_Schedule_Line_ID => l_Schedule_Line_ID);
402 --dbms_output.put_line('After calling the Table_Insert Procedure');
403 End If;
404 End If;
405
406 END LOOP;
407
408 End Process_All_ASSGN_Schedules;
409
410 Procedure Process_All_EG_Schedules(p_template_id number, p_start_date DATE,p_end_date DATE) IS
411 l_cur_handle INTEGER ;
412 l_total_rows INTEGER := 0;
413
414 l_Assignment_ID NUMBER(22,2) := 0;
415 l_Organization_ID Number(22,2) := 0;
416 l_Person_ID Number(22,0) := 0;
417 l_Element_Type_ID Number(22,0) := 0;
418 l_Schedule_Begin_Date DATE;
419 l_Schedule_End_Date DATE;
420 l_Time_Period_Start_Date DATE;
421 l_Time_Period_End_Date DATE;
422 l_Schedule_Line_ID Number(22,0) := 0;
423
424 -- Variables used to store the start and the end dates of two possible periods
425 -- (Period1 => Before the Selected Time Period Start Date,
426 -- Period2 => After the Selected Time Period End Date) for 'GLOBAL ELEMENT LEVEL SCHEDULES'
427 l_GE_Period1_Start Date;
428 l_GE_Period1_End Date;
429 l_GE_Period2_Start Date;
430 l_GE_Period2_End Date;
431
432 -- Variables used to store the start and the end dates of two possible periods
433 -- (Period1 => Before the Selected Time Period Start Date,
434 -- Period2 => After the Selected Time Period End Date) for 'ELEMENT_GROUP LEVEL SCHEDULES'
435 l_EG_Period1_Start Date;
436 l_EG_Period1_End Date;
437 l_EG_Period2_Start Date;
438 l_EG_Period2_End Date;
439
440 -- Variables used to store the start and the end dates of two possible periods
441 -- (Period1 => Before the Selected Time Period Start Date,
442 -- Period2 => After the Selected Time Period End Date) for 'ASSIGNMENT LEVEL SCHEDULES'
443 l_AS_Period1_Start Date;
444 l_AS_Period1_End Date;
445 l_AS_Period2_Start Date;
446 l_AS_Period2_End Date;
447
448 v_Assgn_List_To_Add varchar2(200);
449
450 v_Scheduling_type_code varchar2(2);
451 b_Goto_Next_Assignment boolean;
452 b_Goto_NExt_Element_Type boolean;
453 retVal Number := 0;
454 Begin
455 l_cur_handle := dbms_sql.open_cursor;
456 dbms_sql.parse(l_cur_handle, 'SELECT DISTINCT a.ASSIGNMENT_ID ASSIGNMENT_ID,
457 a.ORGANIZATION_ID ORGANIZATION_ID,
458 b.PERSON_ID PERSON_ID,
459 f.ELEMENT_TYPE_ID ELEMENT_TYPE_ID,
460 d.SCHEDULE_LINE_ID SCHEDULE_LINE_ID,
461 d.SCHEDULE_BEGIN_DATE SCHEDULE_BEGIN_DATE,
462 d.SCHEDULE_END_DATE SCHEDULE_END_DATE,
463 fnd_date.canonical_to_date('''|| fnd_date.date_to_canonical(p_start_date) ||''') TIME_PERIOD_START_DATE,
464 fnd_date.canonical_to_date('''|| fnd_date.date_to_canonical(p_end_date) ||''') TIME_PERIOD_END_DATE
465 from PER_ASSIGNMENTS_F a,
466 PSP_SCHEDULES b,
467 PSP_SCHEDULE_HIERARCHY c,
468 PSP_SCHEDULE_LINES d,
469 PSP_GROUP_ELEMENT_LIST f,
470 PSP_DEFAULT_LABOR_SCHEDULES g
471 where a.ASSIGNMENT_ID = b.ASSIGNMENT_ID
472 and b.ASSIGNMENT_ID = c.ASSIGNMENT_ID
473 and c.SCHEDULE_HIERARCHY_ID = d.SCHEDULE_HIERARCHY_ID
474 and f.ELEMENT_GROUP_ID = c.ELEMENT_GROUP_ID
475 and NOT(d.SCHEDULE_BEGIN_DATE <= :p_start_date AND
476 d.SCHEDULE_END_DATE >= :p_end_date )
477 and NOT(d.SCHEDULE_BEGIN_DATE > :p_end_date OR
478 d.SCHEDULE_END_DATE < :p_start_date )
479 and (:p_end_date BETWEEN a.EFFECTIVE_START_DATE and
480 a.EFFECTIVE_END_DATE)
481 and c.SCHEDULING_TYPES_CODE = ''EG''
482 and f.ELEMENT_GROUP_ID IN (Select Distinct
483 PGEL.ELEMENT_GROUP_ID
484 from PSP_GROUP_ELEMENT_LIST PGEL,
485 PSP_ELEMENT_GROUPS PEG
486 where PGEL.Element_Type_ID IN ' ||
487 g_element_type_id_str || '
488 and PGEL.Element_Group_ID =
489 PEG.Element_Group_ID)
490 and a.ORGANIZATION_ID = g.ORGANIZATION_ID
491 and f.ELEMENT_TYPE_ID IN ' || g_element_type_id_str || '
492 and (:p_start_date >= g.SCHEDULE_BEGIN_DATE
493 or :p_end_date <= g.SCHEDULE_END_DATE
494 or (:p_start_date <= g.SCHEDULE_BEGIN_DATE and
495 :p_end_date >= g.SCHEDULE_END_DATE))
496 and ' || g_organization_str,
497 dbms_sql.V7);
498
499 dbms_sql.bind_variable(l_cur_handle, ':p_start_date', p_start_date);
500 dbms_sql.bind_variable(l_cur_handle, ':p_end_date', p_end_date);
501
502 -- dbms_output.put_line('About to define numeric columns.');
503 dbms_sql.define_column(l_cur_handle, 1, l_Assignment_ID);
504 dbms_sql.define_column(l_cur_handle, 2, l_Organization_ID);
505 dbms_sql.define_column(l_cur_handle, 3, l_Person_ID);
506 dbms_sql.define_column(l_cur_handle, 4, l_Element_Type_ID);
507 dbms_sql.define_column(l_cur_handle, 5, l_Schedule_Line_ID);
508
509 -- dbms_output.put_line('About to define Date columns.');
510 dbms_sql.define_column(l_cur_handle, 6, l_Schedule_Begin_Date);
511 dbms_sql.define_column(l_cur_handle, 7, l_Schedule_End_Date);
512 dbms_sql.define_column(l_cur_handle, 8, l_Time_Period_Start_Date);
513 dbms_sql.define_column(l_cur_handle, 9, l_Time_Period_End_Date);
514
515 l_total_rows := dbms_sql.execute(l_cur_handle);
516 while dbms_sql.fetch_rows(l_cur_handle) > 0
517 LOOP
518 -- dbms_output.put_line('Inside Loop of Element Group Schedules');
519 dbms_sql.column_value(l_cur_handle, 1, l_Assignment_ID);
520 dbms_sql.column_value(l_cur_handle, 2, l_Organization_ID);
521 dbms_sql.column_value(l_cur_handle, 3, l_Person_ID);
522 dbms_sql.column_value(l_cur_handle, 4, l_Element_Type_ID);
523 dbms_sql.column_value(l_cur_handle, 5, l_Schedule_Line_ID);
524 dbms_sql.column_value(l_cur_handle, 6, l_Schedule_Begin_Date);
525 dbms_sql.column_value(l_cur_handle, 7, l_Schedule_End_Date);
526 dbms_sql.column_value(l_cur_handle, 8, l_Time_Period_Start_Date);
527 dbms_sql.column_value(l_cur_handle, 9, l_Time_Period_End_Date);
528
529 retVal := Process_Glob_Elem_Schedule(l_Assignment_ID, l_Element_Type_ID,
530 l_Time_Period_Start_Date, l_Time_Period_End_Date, l_GE_Period1_Start,
531 l_GE_Period1_End, l_GE_Period2_Start, l_GE_Period2_End);
532
533 If l_GE_Period1_Start IS NOT NULL Then
534
535
536 Calculate_Split_Periods(l_Assignment_Id, l_Schedule_Begin_Date, l_Schedule_End_Date,
537 l_GE_Period1_Start, l_GE_Period1_End, l_EG_Period1_Start,
538 l_EG_Period1_End, l_EG_Period2_Start, l_EG_Period2_End);
539
540 If l_EG_Period1_Start IS NOT NULL Then
541 -- Period1 is not encompassed in Element Group schedule. So, do assignment level
542 -- schedule check
543 retVal := Process_Assgn_Level_Schedule(l_EG_Period1_Start, l_EG_Period1_End,
544 l_Assignment_ID, l_Element_Type_ID, l_AS_Period1_Start,
545 l_AS_Period1_End, l_AS_Period2_Start, l_AS_Period2_End);
546 If l_AS_Period1_Start IS NOT NULL then
547 -- Obtain the Part of Period1 that is covered by a PSP
548 -- Default Labor Schedule. Add corresponding records to PSP_REP_ORG_DLS
549 -- table
550 Add_If_Within_DLS(v_Organization_ID => l_Organization_ID,
551 v_Begin_Date => l_AS_Period1_Start, v_End_Date => l_AS_Period1_End,
552 v_Assignment_ID => l_Assignment_ID, v_Person_ID => l_Person_ID,
553 v_Element_Type_ID => l_Element_Type_ID, v_Original_Sch_Code => 'EG',
554 v_Schedule_Line_ID => l_Schedule_Line_ID);
555 End If;
556
557 If l_AS_Period2_Start IS NOT NULL then
558 -- Obtain the Part of Period1 that is covered by a PSP
559 -- Default Labor Schedule. Add corresponding records to PSP_REP_ORG_DLS
560 -- table
561 Add_If_Within_DLS(v_Organization_ID => l_Organization_ID,
562 v_Begin_Date => l_AS_Period2_Start, v_End_Date => l_AS_Period2_End,
563 v_Assignment_ID => l_Assignment_ID, v_Person_ID => l_Person_ID,
564 v_Element_Type_ID => l_Element_Type_ID, v_Original_Sch_Code => 'EG',
565 v_Schedule_Line_ID => l_Schedule_Line_ID);
566 End If;
567
568 End If;
569
570 If l_EG_Period2_Start IS NOT NULL Then
571 -- Period2 is not encompassed in Element Group Schedule. So, do assignment level
572 -- schedule check
573 retVal := Process_Assgn_Level_Schedule(l_EG_Period2_Start, l_EG_Period2_End,
574 l_Assignment_ID, l_Element_Type_ID, l_AS_Period1_Start,
575 l_AS_Period1_End, l_AS_Period2_Start, l_AS_Period2_End);
576 If l_AS_Period1_Start IS NOT NULL then
577 -- Obtain the Part of Period1 that is covered by a PSP
578 -- Default Labor Schedule. Add corresponding records to PSP_REP_ORG_DLS
579 -- table
580 Add_If_Within_DLS(v_Organization_ID => l_Organization_ID,
581 v_Begin_Date => l_AS_Period1_Start, v_End_Date => l_AS_Period1_End,
582 v_Assignment_ID => l_Assignment_ID, v_Person_ID => l_Person_ID,
583 v_Element_Type_ID => l_Element_Type_ID, v_Original_Sch_Code => 'EG',
584 v_Schedule_Line_ID => l_Schedule_Line_ID);
585 End If;
586
587 If l_AS_Period2_Start IS NOT NULL then
588 -- Obtain the Part of Period1 that is covered by a PSP
589 -- Default Labor Schedule. Add corresponding records to PSP_REP_ORG_DLS
590 -- table
591 Add_If_Within_DLS(v_Organization_ID => l_Organization_ID,
592 v_Begin_Date => l_AS_Period2_Start, v_End_Date => l_AS_Period2_End,
593 v_Assignment_ID => l_Assignment_ID, v_Person_ID => l_Person_ID,
594 v_Element_Type_ID => l_Element_Type_ID, v_Original_Sch_Code => 'EG',
595 v_Schedule_Line_ID => l_Schedule_Line_ID);
596 End If;
597 End If;
598 End If;
599
600 If l_GE_Period2_Start IS NOT NULL Then
601
602
603 Calculate_Split_Periods(l_Assignment_Id, l_Schedule_Begin_Date, l_Schedule_End_Date,
604 l_GE_Period2_Start, l_GE_Period2_End, l_EG_Period1_Start,
605 l_EG_Period1_End, l_EG_Period2_Start, l_EG_Period2_End);
606
607 If l_EG_Period1_Start IS NOT NULL Then
608 -- Period1 is not encompassed in Element Group schedule. So, do assignment level
609 -- schedule check
610 retVal := Process_Assgn_Level_Schedule(l_EG_Period1_Start, l_EG_Period1_End,
611 l_Assignment_ID, l_Element_Type_ID, l_AS_Period1_Start,
612 l_AS_Period1_End, l_AS_Period2_Start, l_AS_Period2_End);
613 If l_AS_Period1_Start IS NOT NULL then
614 -- Obtain the Part of Period1 that is covered by a PSP
615 -- Default Labor Schedule. Add corresponding records to PSP_REP_ORG_DLS
616 -- table
617 Add_If_Within_DLS(v_Organization_ID => l_Organization_ID,
618 v_Begin_Date => l_AS_Period1_Start, v_End_Date => l_AS_Period1_End,
619 v_Assignment_ID => l_Assignment_ID, v_Person_ID => l_Person_ID,
620 v_Element_Type_ID => l_Element_Type_ID, v_Original_Sch_Code => 'EG',
621 v_Schedule_Line_ID => l_Schedule_Line_ID);
622 End If;
623
624 If l_AS_Period2_Start IS NOT NULL then
625 -- Obtain the Part of Period1 that is covered by a PSP
626 -- Default Labor Schedule. Add corresponding records to PSP_REP_ORG_DLS
627 -- table
628 Add_If_Within_DLS(v_Organization_ID => l_Organization_ID,
629 v_Begin_Date => l_AS_Period2_Start, v_End_Date => l_AS_Period2_End,
630 v_Assignment_ID => l_Assignment_ID, v_Person_ID => l_Person_ID,
631 v_Element_Type_ID => l_Element_Type_ID, v_Original_Sch_Code => 'EG',
632 v_Schedule_Line_ID => l_Schedule_Line_ID);
633 End If;
634
635 End If;
636
637 If l_EG_Period2_Start IS NOT NULL Then
638 -- Period2 is not encompassed in Element Group Schedule. So, do assignment level
639 -- schedule check
640 retVal := Process_Assgn_Level_Schedule(l_EG_Period2_Start, l_EG_Period2_End,
641 l_Assignment_ID, l_Element_Type_ID, l_AS_Period1_Start,
642 l_AS_Period1_End, l_AS_Period2_Start, l_AS_Period2_End);
643 If l_AS_Period1_Start IS NOT NULL then
644 -- Obtain the Part of Period1 that is covered by a PSP
645 -- Default Labor Schedule. Add corresponding records to PSP_REP_ORG_DLS
646 -- table
647 Add_If_Within_DLS(v_Organization_ID => l_Organization_ID,
648 v_Begin_Date => l_AS_Period1_Start, v_End_Date => l_AS_Period1_End,
649 v_Assignment_ID => l_Assignment_ID, v_Person_ID => l_Person_ID,
650 v_Element_Type_ID => l_Element_Type_ID, v_Original_Sch_Code => 'EG',
651 v_Schedule_Line_ID => l_Schedule_Line_ID);
652 End If;
653
654 If l_AS_Period2_Start IS NOT NULL then
655 -- Obtain the Part of Period1 that is covered by a PSP
656 -- Default Labor Schedule. Add corresponding records to PSP_REP_ORG_DLS
657 -- table
658 Add_If_Within_DLS(v_Organization_ID => l_Organization_ID,
659 v_Begin_Date => l_AS_Period2_Start, v_End_Date => l_AS_Period2_End,
660 v_Assignment_ID => l_Assignment_ID, v_Person_ID => l_Person_ID,
661 v_Element_Type_ID => l_Element_Type_ID, v_Original_Sch_Code => 'EG',
662 v_Schedule_Line_ID => l_Schedule_Line_ID);
663 End If;
664 End If;
665 End If;
666
667
668 END LOOP;
669 End Process_All_EG_Schedules;
670
671 Procedure Process_All_ET_Schedules(p_template_id number, p_start_date DATE,p_end_date DATE) IS
672 l_cur_handle INTEGER ;
673 l_total_rows INTEGER := 0;
674
675 l_Assignment_ID NUMBER(22,2) := 0;
676 l_Organization_ID Number(22,2) := 0;
677 l_Person_ID Number(22,0) := 0;
678 l_Element_Type_ID Number(22,0) := 0;
679 l_Schedule_Begin_Date DATE;
680 l_Schedule_End_Date DATE;
681 l_Time_Period_Start_Date DATE;
682 l_Time_Period_End_Date DATE;
683 l_Schedule_Line_ID Number(22,0) := 0;
684
685 -- Variables used to store the start and the end dates of two possible periods
686 -- (Period1 => Before the Selected Time Period Start Date,
687 -- Period2 => After the Selected Time Period End Date) for 'GLOBAL ELEMENT LEVEL SCHEDULES'
688 l_GE_Period1_Start Date;
689 l_GE_Period1_End Date;
690 l_GE_Period2_Start Date;
691 l_GE_Period2_End Date;
692
693 -- Variables used to store the start and the end dates of two possible periods
694 -- (Period1 => Before the Selected Time Period Start Date,
695 -- Period2 => After the Selected Time Period End Date) for 'ELEMENT_TYPE LEVEL SCHEDULES'
696 l_Period1_Start Date;
697 l_Period1_End Date;
698 l_Period2_Start Date;
699 l_Period2_End Date;
700
701 -- Variables used to store the start and the end dates of two possible periods
702 -- (Period1 => Before the Selected Time Period Start Date,
703 -- Period2 => After the Selected Time Period End Date) for 'ELEMENT_GROUP LEVEL SCHEDULES'
704 l_EG_Period1_Start Date;
705 l_EG_Period1_End Date;
706 l_EG_Period2_Start Date;
707 l_EG_Period2_End Date;
708
709 -- Variables used to store the start and the end dates of two possible periods
710 -- (Period1 => Before the Selected Time Period Start Date,
711 -- Period2 => After the Selected Time Period End Date) for 'ASSIGNMENT LEVEL SCHEDULES'
712 l_AS_Period1_Start Date;
713 l_AS_Period1_End Date;
714 l_AS_Period2_Start Date;
715 l_AS_Period2_End Date;
716
717 v_Assgn_List_To_Add varchar2(200);
718
719 v_Scheduling_type_code varchar2(2);
720 b_Goto_Next_Assignment boolean;
721 b_Goto_NExt_Element_Type boolean;
722 retVal Number := 0;
723 Begin
724 l_cur_handle := dbms_sql.open_cursor;
725 dbms_sql.parse(l_cur_handle, 'SELECT DISTINCT a.ASSIGNMENT_ID ASSIGNMENT_ID,
726 a.ORGANIZATION_ID ORGANIZATION_ID,
727 b.PERSON_ID PERSON_ID,
728 c.ELEMENT_TYPE_ID ELEMENT_TYPE_ID,
729 d.SCHEDULE_LINE_ID SCHEDULE_LINE_ID,
730 d.SCHEDULE_BEGIN_DATE SCHEDULE_BEGIN_DATE,
731 d.SCHEDULE_END_DATE SCHEDULE_END_DATE,
732 fnd_date.canonical_to_date('''|| fnd_date.date_to_canonical(p_start_date) ||''') TIME_PERIOD_START_DATE,
733 fnd_date.canonical_to_date('''|| fnd_date.date_to_canonical(p_end_date) ||''') TIME_PERIOD_END_DATE
734 from PER_ASSIGNMENTS_F a,
735 PSP_SCHEDULES b,
736 PSP_SCHEDULE_HIERARCHY c,
737 PSP_SCHEDULE_LINES d,
738 PSP_DEFAULT_LABOR_SCHEDULES f
739 where a.ASSIGNMENT_ID = b.ASSIGNMENT_ID
740 and b.ASSIGNMENT_ID = c.ASSIGNMENT_ID
741 and c.SCHEDULE_HIERARCHY_ID = d.SCHEDULE_HIERARCHY_ID
742 and NOT(d.SCHEDULE_BEGIN_DATE <= :p_start_date AND
743 d.SCHEDULE_END_DATE >= :p_end_date )
744 and NOT(d.SCHEDULE_BEGIN_DATE > :p_end_date OR
745 d.SCHEDULE_END_DATE < :p_start_date )
746 and ( :p_end_date BETWEEN a.EFFECTIVE_START_DATE and
747 a.EFFECTIVE_END_DATE)
748 and c.SCHEDULING_TYPES_CODE = ''ET''
749 and ' || g_organization_str || '
750 and a.ORGANIZATION_ID = f.ORGANIZATION_ID
751 and ( :p_start_date >= f.SCHEDULE_BEGIN_DATE
752 or :p_end_date <= f.SCHEDULE_END_DATE
753 or ( :p_start_date <= f.SCHEDULE_BEGIN_DATE and
754 :p_end_date >= f.SCHEDULE_END_DATE))
755 and c.ELEMENT_TYPE_ID IN ' || g_element_type_id_str,
756 dbms_sql.V7);
757
758 dbms_sql.bind_variable(l_cur_handle, ':p_start_date', p_start_date);
759 dbms_sql.bind_variable(l_cur_handle, ':p_end_date', p_end_date);
760
761 --dbms_output.put_line('About to define numeric columns.');
762 dbms_sql.define_column(l_cur_handle, 1, l_Assignment_ID);
763 dbms_sql.define_column(l_cur_handle, 2, l_Organization_ID);
764 dbms_sql.define_column(l_cur_handle, 3, l_Person_ID);
765 dbms_sql.define_column(l_cur_handle, 4, l_Element_Type_ID);
766 dbms_sql.define_column(l_cur_handle, 5, l_Schedule_Line_ID);
767
768 -- dbms_output.put_line('About to define Date columns.');
769 --dbms_output.put_line('After calling the Table_Insert Procedure');
770 dbms_sql.define_column(l_cur_handle, 6, l_Schedule_Begin_Date);
771 dbms_sql.define_column(l_cur_handle, 7, l_Schedule_End_Date);
772 dbms_sql.define_column(l_cur_handle, 8, l_Time_Period_Start_Date);
773 dbms_sql.define_column(l_cur_handle, 9, l_Time_Period_End_Date);
774
775 l_total_rows := dbms_sql.execute(l_cur_handle);
776 while dbms_sql.fetch_rows(l_cur_handle) > 0
777 LOOP
778 -- dbms_output.put_line('Inside Loop of ET Schedules');
779 dbms_sql.column_value(l_cur_handle, 1, l_Assignment_ID);
780 dbms_sql.column_value(l_cur_handle, 2, l_Organization_ID);
781 dbms_sql.column_value(l_cur_handle, 3, l_Person_ID);
782 dbms_sql.column_value(l_cur_handle, 4, l_Element_Type_ID);
783 dbms_sql.column_value(l_cur_handle, 5, l_Schedule_Line_ID);
784 dbms_sql.column_value(l_cur_handle, 6, l_Schedule_Begin_Date);
785 dbms_sql.column_value(l_cur_handle, 7, l_Schedule_End_Date);
786 dbms_sql.column_value(l_cur_handle, 8, l_Time_Period_Start_Date);
787 dbms_sql.column_value(l_cur_handle, 9, l_Time_Period_End_Date);
788
789 retVal := Process_Glob_Elem_Schedule(l_Assignment_ID, l_Element_Type_ID,
790 l_Time_Period_Start_Date, l_Time_Period_End_Date, l_GE_Period1_Start,
791 l_GE_Period1_End, l_GE_Period2_Start, l_GE_Period2_End);
792
793 If l_GE_Period1_Start IS NOT NULL Then
794
795 Calculate_Split_Periods(l_Assignment_Id, l_Schedule_Begin_Date, l_Schedule_End_Date,
796 l_GE_Period1_Start, l_GE_Period1_End, l_Period1_Start,
797 l_Period1_End, l_Period2_Start, l_Period2_End);
798 /*dbms_output.put_line('Split Periods are as follows: Period1 Start - ' ||
799 to_char(l_Period1_start) || ', Period1 End - ' || to_char(l_Period1_End) || ',
800 Period2 Start - ' || to_char(l_Period2_Start) || ', Period2 End - ' ||
801 to_char(l_Period2_End));*/
802
803 -- To check if the current schedule falls within the Time Period for any records with
804 -- scheduling type code of 'EG'
805
806 /****************************************************************************
807 For the current Assignment and Element Type, check if there is a schedule with
808 Scheduling type code of 'EG'. If there is not any record. Then will have to do
809 assignment level checking. If there is one, then check the current date range(s)
810 to see if they are covered in the Schedule.
811
812 If they are, then go to next assignment.
813 If they are not, then will have to do assignment level checking. ****************************************************************************/
814
815 If l_Period1_Start IS NOT NULL Then
816 -- dbms_output.put_line('About to execute Process Element Group Schedule.');
817 retVal := Process_Element_Group_Schedule(l_Period1_Start, l_Period1_End,
818 l_Assignment_ID, l_Element_Type_ID, l_EG_Period1_Start,
819 l_EG_Period1_End, l_EG_Period2_Start, l_EG_Period2_End);
820 If l_EG_Period1_Start IS NOT NULL Then
821 -- Period1 is not encompassed in Element Group schedule. So, do
822 -- assignment level schedule check
823 retVal := Process_Assgn_Level_Schedule(l_EG_Period1_Start,
824 l_EG_Period1_End, l_Assignment_ID, l_Element_Type_ID,
825 l_AS_Period1_Start, l_AS_Period1_End,
826 l_AS_Period2_Start, l_AS_Period2_End);
827 If l_AS_Period1_Start IS NOT NULL then
828 -- Obtain the Part of Period1 that is covered by a PSP
829 -- Default Labor Schedule. Add corresponding records to PSP_REP_ORG_DLS
830 -- table
831 Add_If_Within_DLS(v_Organization_ID => l_Organization_ID,
832 v_Begin_Date => l_AS_Period1_Start, v_End_Date => l_AS_Period1_End,
833 v_Assignment_ID => l_Assignment_ID, v_Person_ID => l_Person_ID,
834 v_Element_Type_ID => l_Element_Type_ID, v_Original_Sch_Code => 'ET',
835 v_Schedule_Line_ID => l_Schedule_Line_ID);
836 End If;
837
838 If l_AS_Period2_Start IS NOT NULL then
839 -- Obtain the Part of Period1 that is covered by a PSP
840 -- Default Labor Schedule. Add corresponding records to PSP_REP_ORG_DLS
841 -- table
842 Add_If_Within_DLS(v_Organization_ID => l_Organization_ID,
843 v_Begin_Date => l_AS_Period2_Start, v_End_Date => l_AS_Period2_End,
844 v_Assignment_ID => l_Assignment_ID, v_Person_ID => l_Person_ID,
845 v_Element_Type_ID => l_Element_Type_ID, v_Original_Sch_Code => 'ET',
846 v_Schedule_Line_ID => l_Schedule_Line_ID);
847 End If;
848 End If;
849
850 If l_EG_Period2_Start IS NOT NULL Then
851 -- Period2 is not encompassed in Element Group Schedule. So, do
852 -- assignment level schedule check
853 retVal := Process_Assgn_Level_Schedule(l_EG_Period2_Start,
854 l_EG_Period2_End, l_Assignment_ID, l_Element_Type_ID,
855 l_AS_Period1_Start, l_AS_Period1_End,
856 l_AS_Period2_Start, l_AS_Period2_End);
857
858 If l_AS_Period1_Start IS NOT NULL then
859 -- Obtain the Part of Period1 that is covered by a PSP
860 -- Default Labor Schedule. Add corresponding records to PSP_REP_ORG_DLS
861 -- table
862 Add_If_Within_DLS(v_Organization_ID => l_Organization_ID,
863 v_Begin_Date => l_AS_Period1_Start, v_End_Date => l_AS_Period1_End,
864 v_Assignment_ID => l_Assignment_ID, v_Person_ID => l_Person_ID,
865 v_Element_Type_ID => l_Element_Type_ID, v_Original_Sch_Code => 'ET',
866 v_Schedule_Line_ID => l_Schedule_Line_ID);
867 End If;
868
869 If l_AS_Period2_Start IS NOT NULL then
870 -- Obtain the Part of Period1 that is covered by a PSP
871 -- Default Labor Schedule. Add corresponding records to PSP_REP_ORG_DLS
872 -- table
873 Add_If_Within_DLS(v_Organization_ID => l_Organization_ID,
874 v_Begin_Date => l_AS_Period2_Start, v_End_Date => l_AS_Period2_End,
875 v_Assignment_ID => l_Assignment_ID, v_Person_ID => l_Person_ID,
876 v_Element_Type_ID => l_Element_Type_ID, v_Original_Sch_Code => 'ET',
877 v_Schedule_Line_ID => l_Schedule_Line_ID);
878 End If;
879 End If;
880 End If;
881
882 If l_Period2_Start IS NOT NULL Then
883 retVal := Process_Element_Group_Schedule(l_Period2_Start, l_Period2_End,
884 l_Assignment_ID, l_Element_Type_ID, l_EG_Period1_Start,
885 l_EG_Period1_End, l_EG_Period2_Start, l_EG_Period2_End);
886 If l_EG_Period1_Start IS NOT NULL Then
887 -- Period1 is not encompassed in Element Group schedule. So, do
888 -- assignment level schedule check
889 retVal := Process_Assgn_Level_Schedule(l_EG_Period1_Start,
890 l_EG_Period1_End, l_Assignment_ID, l_Element_Type_ID,
891 l_AS_Period1_Start, l_AS_Period1_End,
892 l_AS_Period2_Start, l_AS_Period2_End);
893 If l_AS_Period1_Start IS NOT NULL then
894 -- Obtain the Part of Period1 that is covered by a PSP
895 -- Default Labor Schedule. Add corresponding records to PSP_REP_ORG_DLS
896 -- table
897 Add_If_Within_DLS(v_Organization_ID => l_Organization_ID,
898 v_Begin_Date => l_AS_Period1_Start, v_End_Date => l_AS_Period1_End,
899 v_Assignment_ID => l_Assignment_ID, v_Person_ID => l_Person_ID,
900 v_Element_Type_ID => l_Element_Type_ID, v_Original_Sch_Code => 'ET',
901 v_Schedule_Line_ID => l_Schedule_Line_ID);
902 End If;
903
904 If l_AS_Period2_Start IS NOT NULL then
905 -- Obtain the Part of Period1 that is covered by a PSP
906 -- Default Labor Schedule. Add corresponding records to PSP_REP_ORG_DLS
907 -- table
908 Add_If_Within_DLS(v_Organization_ID => l_Organization_ID,
909 v_Begin_Date => l_AS_Period2_Start, v_End_Date => l_AS_Period2_End,
910 v_Assignment_ID => l_Assignment_ID, v_Person_ID => l_Person_ID,
911 v_Element_Type_ID => l_Element_Type_ID, v_Original_Sch_Code => 'ET',
912 v_Schedule_Line_ID => l_Schedule_Line_ID);
913 End If;
914
915 End If;
916
917 If l_EG_Period2_Start IS NOT NULL Then
918 -- Period2 is not encompassed in Element Group Schedule. So, do
919 -- assignment level schedule check
920 retVal := Process_Assgn_Level_Schedule(l_EG_Period2_Start,
921 l_EG_Period2_End, l_Assignment_ID, l_Element_Type_ID,
922 l_AS_Period1_Start, l_AS_Period1_End,
923 l_AS_Period2_Start, l_AS_Period2_End);
924 If l_AS_Period1_Start IS NOT NULL then
925 -- Obtain the Part of Period1 that is covered by a PSP
926 -- Default Labor Schedule. Add corresponding records to PSP_REP_ORG_DLS
927 -- table
928 Add_If_Within_DLS(v_Organization_ID => l_Organization_ID,
929 v_Begin_Date => l_AS_Period1_Start, v_End_Date => l_AS_Period1_End,
930 v_Assignment_ID => l_Assignment_ID, v_Person_ID => l_Person_ID,
931 v_Element_Type_ID => l_Element_Type_ID, v_Original_Sch_Code => 'ET',
932 v_Schedule_Line_ID => l_Schedule_Line_ID);
933 End If;
934
935
936 If l_AS_Period2_Start IS NOT NULL then
937 -- Obtain the Part of Period1 that is covered by a PSP
938 -- Default Labor Schedule. Add corresponding records to PSP_REP_ORG_DLS
939 -- table
940 Add_If_Within_DLS(v_Organization_ID => l_Organization_ID,
941 v_Begin_Date => l_AS_Period2_Start, v_End_Date => l_AS_Period2_End,
942 v_Assignment_ID => l_Assignment_ID, v_Person_ID => l_Person_ID,
943 v_Element_Type_ID => l_Element_Type_ID, v_Original_Sch_Code => 'ET',
944 v_Schedule_Line_ID => l_Schedule_Line_ID);
945 End If;
946 End If;
947 End If;
948 End If;
949
950 If l_GE_Period2_Start IS NOT NULL Then
951
952 Calculate_Split_Periods(l_Assignment_Id, l_Schedule_Begin_Date, l_Schedule_End_Date,
953 l_GE_Period2_Start, l_GE_Period2_End, l_Period1_Start,
954 l_Period1_End, l_Period2_Start, l_Period2_End);
955 /**dbms_output.put_line('Split Periods are as follows: Period1 Start - ' ||
956 to_char(l_Period1_start) || ', Period1 End - ' || to_char(l_Period1_End) || ',
957 Period2 Start - ' || to_char(l_Period2_Start) || ', Period2 End - ' ||
958 to_char(l_Period2_End));**/
959
960 -- To check if the current schedule falls within the Time Period for any records with
961 -- scheduling type code of 'EG'
962 /**************************************************************************************
963 For the current Assignment and Element Type, check if there is a schedule with
964 Scheduling type code of 'EG'. If there is not any record. Then will have to do
965 assignment level checking. If there is one, then check the current date range(s)
966 to see if they are covered in the Schedule.
967
968 If they are, then go to next assignment.
969 If they are not, then will have to do assignment level checking. **************************************************************************************/
970 If l_Period1_Start IS NOT NULL Then
971 -- dbms_output.put_line('About to execute Process Element Group Schedule.');
972 retVal := Process_Element_Group_Schedule(l_Period1_Start, l_Period1_End,
973 l_Assignment_ID, l_Element_Type_ID, l_EG_Period1_Start,
974 l_EG_Period1_End, l_EG_Period2_Start, l_EG_Period2_End);
975 If l_EG_Period1_Start IS NOT NULL Then
976 -- Period1 is not encompassed in Element Group schedule. So, do
977 -- assignment level schedule check
978 retVal := Process_Assgn_Level_Schedule(l_EG_Period1_Start,
979 l_EG_Period1_End, l_Assignment_ID, l_Element_Type_ID,
980 l_AS_Period1_Start, l_AS_Period1_End,
981 l_AS_Period2_Start, l_AS_Period2_End);
982 If l_AS_Period1_Start IS NOT NULL then
983 -- Obtain the Part of Period1 that is covered by a PSP
984 -- Default Labor Schedule. Add corresponding records to PSP_REP_ORG_DLS
985 -- table
986 Add_If_Within_DLS(v_Organization_ID => l_Organization_ID,
987 v_Begin_Date => l_AS_Period1_Start, v_End_Date => l_AS_Period1_End,
988 v_Assignment_ID => l_Assignment_ID, v_Person_ID => l_Person_ID,
989 v_Element_Type_ID => l_Element_Type_ID, v_Original_Sch_Code => 'ET',
990 v_Schedule_Line_ID => l_Schedule_Line_ID);
991 End If;
992
993 If l_AS_Period2_Start IS NOT NULL then
994 -- Obtain the Part of Period1 that is covered by a PSP
995 -- Default Labor Schedule. Add corresponding records to PSP_REP_ORG_DLS
996 -- table
997 Add_If_Within_DLS(v_Organization_ID => l_Organization_ID,
998 v_Begin_Date => l_AS_Period2_Start, v_End_Date => l_AS_Period2_End,
999 v_Assignment_ID => l_Assignment_ID, v_Person_ID => l_Person_ID,
1000 v_Element_Type_ID => l_Element_Type_ID, v_Original_Sch_Code => 'ET',
1001 v_Schedule_Line_ID => l_Schedule_Line_ID);
1002 End If;
1003 End If;
1004
1005 If l_EG_Period2_Start IS NOT NULL Then
1006 -- Period2 is not encompassed in Element Group Schedule. So, do
1007 -- assignment level schedule check
1008 retVal := Process_Assgn_Level_Schedule(l_EG_Period2_Start,
1009 l_EG_Period2_End, l_Assignment_ID, l_Element_Type_ID,
1010 l_AS_Period1_Start, l_AS_Period1_End,
1011 l_AS_Period2_Start, l_AS_Period2_End);
1012
1013 If l_AS_Period1_Start IS NOT NULL then
1014 -- Obtain the Part of Period1 that is covered by a PSP
1015 -- Default Labor Schedule. Add corresponding records to PSP_REP_ORG_DLS
1016 -- table
1017 Add_If_Within_DLS(v_Organization_ID => l_Organization_ID,
1018 v_Begin_Date => l_AS_Period1_Start, v_End_Date => l_AS_Period1_End,
1019 v_Assignment_ID => l_Assignment_ID, v_Person_ID => l_Person_ID,
1020 v_Element_Type_ID => l_Element_Type_ID, v_Original_Sch_Code => 'ET',
1021 v_Schedule_Line_ID => l_Schedule_Line_ID);
1022 End If;
1023
1024 If l_AS_Period2_Start IS NOT NULL then
1025 -- Obtain the Part of Period1 that is covered by a PSP
1026 -- Default Labor Schedule. Add corresponding records to PSP_REP_ORG_DLS
1027 -- table
1028 Add_If_Within_DLS(v_Organization_ID => l_Organization_ID,
1029 v_Begin_Date => l_AS_Period2_Start, v_End_Date => l_AS_Period2_End,
1030 v_Assignment_ID => l_Assignment_ID, v_Person_ID => l_Person_ID,
1031 v_Element_Type_ID => l_Element_Type_ID, v_Original_Sch_Code => 'ET',
1032 v_Schedule_Line_ID => l_Schedule_Line_ID);
1033 End If;
1034 End If;
1035 End If;
1036
1037 If l_Period2_Start IS NOT NULL Then
1038 retVal := Process_Element_Group_Schedule(l_Period2_Start, l_Period2_End,
1039 l_Assignment_ID, l_Element_Type_ID, l_EG_Period1_Start,
1040 l_EG_Period1_End, l_EG_Period2_Start, l_EG_Period2_End);
1041 If l_EG_Period1_Start IS NOT NULL Then
1042 -- Period1 is not encompassed in Element Group schedule. So, do
1043 -- assignment level schedule check
1044 retVal := Process_Assgn_Level_Schedule(l_EG_Period1_Start,
1045 l_EG_Period1_End, l_Assignment_ID, l_Element_Type_ID,
1046 l_AS_Period1_Start, l_AS_Period1_End,
1047 l_AS_Period2_Start, l_AS_Period2_End);
1048 If l_AS_Period1_Start IS NOT NULL then
1049 -- Obtain the Part of Period1 that is covered by a PSP
1050 -- Default Labor Schedule. Add corresponding records to PSP_REP_ORG_DLS
1051 -- table
1052 Add_If_Within_DLS(v_Organization_ID => l_Organization_ID,
1053 v_Begin_Date => l_AS_Period1_Start, v_End_Date => l_AS_Period1_End,
1054 v_Assignment_ID => l_Assignment_ID, v_Person_ID => l_Person_ID,
1055 v_Element_Type_ID => l_Element_Type_ID, v_Original_Sch_Code => 'ET',
1056 v_Schedule_Line_ID => l_Schedule_Line_ID);
1057 End If;
1058
1059 If l_AS_Period2_Start IS NOT NULL then
1060 -- Obtain the Part of Period1 that is covered by a PSP
1061 -- Default Labor Schedule. Add corresponding records to PSP_REP_ORG_DLS
1062 -- table
1063 Add_If_Within_DLS(v_Organization_ID => l_Organization_ID,
1064 v_Begin_Date => l_AS_Period2_Start, v_End_Date => l_AS_Period2_End,
1065 v_Assignment_ID => l_Assignment_ID, v_Person_ID => l_Person_ID,
1066 v_Element_Type_ID => l_Element_Type_ID, v_Original_Sch_Code => 'ET',
1067 v_Schedule_Line_ID => l_Schedule_Line_ID);
1068 End If;
1069
1070 End If;
1071
1072 If l_EG_Period2_Start IS NOT NULL Then
1073 -- Period2 is not encompassed in Element Group Schedule. So, do
1074 -- assignment level schedule check
1075 retVal := Process_Assgn_Level_Schedule(l_EG_Period2_Start,
1076 l_EG_Period2_End, l_Assignment_ID, l_Element_Type_ID,
1077 l_AS_Period1_Start, l_AS_Period1_End,
1078 l_AS_Period2_Start, l_AS_Period2_End);
1079 If l_AS_Period1_Start IS NOT NULL then
1080 -- Obtain the Part of Period1 that is covered by a PSP
1081 -- Default Labor Schedule. Add corresponding records to PSP_REP_ORG_DLS
1082 -- table
1083 Add_If_Within_DLS(v_Organization_ID => l_Organization_ID,
1084 v_Begin_Date => l_AS_Period1_Start, v_End_Date => l_AS_Period1_End,
1085 v_Assignment_ID => l_Assignment_ID, v_Person_ID => l_Person_ID,
1086 v_Element_Type_ID => l_Element_Type_ID, v_Original_Sch_Code => 'ET',
1087 v_Schedule_Line_ID => l_Schedule_Line_ID);
1088 End If;
1089
1090
1091 If l_AS_Period2_Start IS NOT NULL then
1092 -- Obtain the Part of Period1 that is covered by a PSP
1093 -- Default Labor Schedule. Add corresponding records to PSP_REP_ORG_DLS
1094 -- table
1095 Add_If_Within_DLS(v_Organization_ID => l_Organization_ID,
1096 v_Begin_Date => l_AS_Period2_Start, v_End_Date => l_AS_Period2_End,
1097 v_Assignment_ID => l_Assignment_ID, v_Person_ID => l_Person_ID,
1098 v_Element_Type_ID => l_Element_Type_ID, v_Original_Sch_Code => 'ET',
1099 v_Schedule_Line_ID => l_Schedule_Line_ID);
1100 End If;
1101 End If;
1102 End If;
1103 End If;
1104
1105 END LOOP;
1106 dbms_sql.close_cursor(l_cur_handle);
1107
1108 End Process_All_ET_Schedules;
1109 /*******************************************************************************************************
1110 This is a private procedure that is called to actually insert data to the intermediate
1111 PSP_REP_ORG_DLS_PKG table that is used by the Organization Level DLS Report. *******************************************************************************************************/
1112 Procedure Table_Insert(Organization_ID Number, Element_Type_ID Number DEFAULT NULL, Person_ID Number,
1113 Assignment_ID Number, Begin_Date Date, End_Date Date, Original_Sch_Code Varchar2,
1114 Schedule_Line_ID Number) IS
1115
1116 l_RowID varchar2(30);
1117 l_Line_ID Number;
1118 Begin
1119 -- dbms_output.put_line('About to Insert record to table');
1120
1121 /**dbms_output.put_line('Values to be inserted are : Org ID - ' || to_char(Organization_ID) ||
1122 ', Elem. Type ID - ' || to_char(Element_Type_ID) || ', Person ID - ' ||
1123 to_char(Person_ID) || ', Assignment ID - ' || to_char(Assignment_ID)
1124 || ', Begin Date - ' || to_char(Begin_Date) || ', End Date - ' || to_char(End_Date));**/
1125
1126 If Organization_ID IS NULL or Person_ID IS NULL or Assignment_ID IS NULL or Begin_Date IS NULL
1127 or End_Date IS NULL Then
1128 -- dbms_output.put_line('One of the primary key fields is null....');
1129 return;
1130 End If;
1131
1132 If p_Batch_Sequence_Number IS NULL Then
1133 Select PSP_REP_ORG_DLS_S.NextVal
1134 into p_Batch_Sequence_Number
1135 from DUAL;
1136 End If;
1137
1138 Select PSP_REP_ORG_DLS2_S.NextVal
1139 into l_Line_ID
1140 from DUAL;
1141
1142 PSP_REP_ORG_DLS_PKG.INSERT_ROW (
1143 X_ROWID => l_RowID,
1144 X_ORG_DLS_BATCH_ID => p_Batch_Sequence_Number,
1145 X_ORG_DLS_LINE_ID => l_Line_ID,
1146 X_ORGANIZATION_ID => Organization_ID,
1147 X_ASSIGNMENT_ID => Assignment_ID,
1148 X_SCHEDULE_LINE_ID => Schedule_Line_ID,
1149 X_ORIGINAL_SCH_CODE => Original_Sch_Code,
1150 X_SCHEDULE_BEGIN_DATE => Begin_Date,
1151 X_SCHEDULE_END_DATE => End_Date,
1152 X_PERSON_ID => Person_ID,
1153 X_ELEMENT_TYPE_ID => Element_Type_ID,
1154 X_MODE => 'R'
1155 );
1156 COMMIT;
1157
1158 End Table_Insert;
1159
1160 /*******************************************************************************************************
1161 This is a private procedure that is called while processing Assignment Level Schedules. This
1162 procedure checks if any uncovered Element Group schedule is covered by Assignment Schedules. If
1163 they are, then a value of 0 is returned, else, a value of 1 is returned.
1164 *******************************************************************************************************/
1165 Function Process_Assgn_Level_Schedule(l_Time_Period_Start DATE, l_Time_Period_End DATE,
1166 l_Assignment_ID Number, l_Element_Type_ID Number, l_AS_Period1_Start IN OUT NOCOPY DATE,
1167 l_AS_Period1_End IN OUT NOCOPY DATE, l_AS_Period2_Start IN OUT NOCOPY DATE, l_AS_Period2_End IN OUT NOCOPY DATE)
1168 return NUMBER IS
1169
1170 /* Commented for Bug 3263333
1171
1172 Cursor C1 is
1173 select DISTINCT c.ASSIGNMENT_ID,
1174 d.SCHEDULE_BEGIN_DATE,
1175 d.SCHEDULE_END_DATE
1176 from PER_ASSIGNMENTS_F a,
1177 PSP_SCHEDULES b,
1178 PSP_SCHEDULE_HIERARCHY c,
1179 PSP_SCHEDULE_LINES d,
1180 PSP_GROUP_ELEMENT_LIST e
1181 where a.assignment_id = b.assignment_id
1182 and b.assignment_id = c.assignment_id
1183 and c.schedule_hierarchy_id = d.schedule_hierarchy_id
1184 and a.assignment_id = l_Assignment_ID
1185 and not (d.schedule_begin_date <= l_Time_Period_Start
1186 or d.schedule_end_date >= l_Time_Period_End)
1187 and l_Time_Period_End Between a.EFFECTIVE_START_DATE and a.EFFECTIVE_END_DATE
1188 and c.SCHEDULING_TYPES_CODE = 'A';*/
1189
1190 -- Introduced the following for Bug 3263333
1191
1192 Cursor C1 is
1193 select DISTINCT c.ASSIGNMENT_ID,
1194 d.SCHEDULE_BEGIN_DATE,
1195 d.SCHEDULE_END_DATE
1196 from PER_ASSIGNMENTS_F a,
1197 PSP_SCHEDULES b,
1198 PSP_SCHEDULE_HIERARCHY c,
1199 PSP_SCHEDULE_LINES d
1200 where a.assignment_id = b.assignment_id
1201 and b.assignment_id = c.assignment_id
1202 and c.schedule_hierarchy_id = d.schedule_hierarchy_id
1203 and a.assignment_id = l_Assignment_ID
1204 and not (d.schedule_begin_date <= l_Time_Period_Start
1205 or d.schedule_end_date >= l_Time_Period_End)
1206 and l_Time_Period_End Between a.EFFECTIVE_START_DATE and a.EFFECTIVE_END_DATE
1207 and c.SCHEDULING_TYPES_CODE = 'A';
1208
1209 C1_Row C1%RowType;
1210 retVal Number;
1211 Begin
1212 -- First, set Element Group Period's start and end dates to NULL
1213 l_AS_Period1_Start := NULL;
1214 l_AS_Period1_End := NULL;
1215 l_AS_Period2_Start := NULL;
1216 l_AS_Period2_End := NULL;
1217
1218 /***dbms_output.put_line('About to search for Assignment labor schedules with assignment of ' ||
1219 to_char(l_assignment_id) || ' and element type of ' || to_char(l_Element_Type_ID) || ' and dates
1220 in ' || to_char(l_Time_Period_Start) || ' and ' || to_char(l_Time_Period_End)); ***/
1221 Open C1;
1222 Fetch C1 into C1_Row;
1223 If C1%NotFound Then
1224 -- dbms_output.put_line('No data found');
1225 Close C1;
1226 l_AS_Period1_Start := l_Time_Period_Start;
1227 l_AS_Period1_End := l_Time_Period_End;
1228 return 0;
1229 Else
1230 -- dbms_output.put_line('Data found');
1231 Calculate_Split_Periods(l_Assignment_Id, C1_Row.Schedule_Begin_Date, C1_Row.Schedule_End_Date,
1232 l_Time_Period_Start, l_Time_Period_End, l_AS_Period1_Start, l_AS_Period1_End,
1233 l_AS_Period2_Start, l_AS_Period2_End);
1234 /***dbms_output.put_line('Split Periods are as follows: Period1 Start - ' ||
1235 to_char(l_AS_Period1_start) || ', Period1 End - ' || to_char(l_AS_Period1_End)
1236 || ', Period2 Start - ' || to_char(l_AS_Period2_Start) || ', Period2 End - ' ||
1237 to_char(l_AS_Period2_End)); ***/
1238 return 1;
1239 End If;
1240 End Process_Assgn_Level_Schedule;
1241
1242 /***************************************************************************************************
1243 Function: PROCESS_GLOB_ELEM_SCHEDULE
1244 ***************************************************************************************************/
1245 Function Process_Glob_Elem_Schedule(l_Assignment_ID Number, l_Element_Type_ID Number,
1246 l_Time_Period_Start DATE, l_Time_Period_End DATE,
1247 l_GE_Period1_Start IN OUT NOCOPY DATE, l_GE_Period1_End IN OUT NOCOPY DATE,
1248 l_GE_Period2_Start IN OUT NOCOPY DATE, l_GE_Period2_End IN OUT NOCOPY DATE) return NUMBER IS
1249 Cursor C1 is
1250 Select DISTINCT e.Start_Date_Active, e.End_Date_Active
1251 from PER_ASSIGNMENTS_F a,
1252 PAY_ELEMENT_ENTRIES_F b,
1253 PAY_ELEMENT_LINKS_F c,
1254 PAY_ELEMENT_TYPES_F d,
1255 PSP_ELEMENT_TYPE_ACCOUNTS e
1256 where a.ASSIGNMENT_ID = b.ASSIGNMENT_ID
1257 and b.ELEMENT_LINK_ID = c.ELEMENT_LINK_ID
1258 and c.ELEMENT_TYPE_ID = d.ELEMENT_TYPE_ID
1259 and d.ELEMENT_TYPE_ID = e.ELEMENT_TYPE_ID
1260 and e.ELEMENT_TYPE_ID = l_Element_Type_ID
1261 and (l_Time_Period_End Between a.EFFECTIVE_START_DATE AND a.EFFECTIVE_END_DATE)
1262 and (l_Time_Period_End Between b.EFFECTIVE_START_DATE AND b.EFFECTIVE_END_DATE)
1263 and (l_Time_Period_End Between c.EFFECTIVE_START_DATE AND c.EFFECTIVE_END_DATE)
1264 and (l_Time_Period_End Between d.EFFECTIVE_START_DATE AND d.EFFECTIVE_END_DATE)
1265 and a.ASSIGNMENT_ID = l_Assignment_ID;
1266
1267 C1_Row C1%RowType;
1268 Begin
1269
1270 l_GE_Period1_Start := NULL;
1271 l_GE_Period1_End := NULL;
1272 l_GE_Period2_Start := NULL;
1273 l_GE_Period2_End := NULL;
1274
1275 Open C1;
1276 Fetch C1 into C1_Row;
1277 /**dbms_output.put_line('Finding Global Element Schedule...for Assignment : ' ||
1278 to_char(l_Assignment_ID) || '. Element Type : ' || to_char(l_Element_Type_ID) || '
1279 Period Begin and End Dates are : ' || to_char(l_Time_Period_Start) || ', ' ||
1280 to_char(l_Time_Period_End)); **/
1281 If C1%NOTFOUND Then
1282 /**dbms_output.put_line('No Entry found for Global Element Schedule for Assignment : '
1283 || to_char(l_Assignment_ID)); **/
1284 l_GE_Period1_Start := l_Time_Period_Start;
1285 l_GE_Period1_End := l_Time_Period_End;
1286 return 0;
1287 Else
1288 -- A Record exists in the Global Elements table for user selected Element Type ID.
1289 -- Need to calculate the portion of the Time Period that is not covered by the Element
1290 Calculate_Split_Periods(l_Assignment_Id, C1_Row.Start_Date_Active, C1_Row.End_Date_Active,
1291 l_Time_Period_Start, l_Time_Period_End, l_GE_Period1_Start, l_GE_Period1_End,
1292 l_GE_Period2_Start, l_GE_Period2_End);
1293 /**dbms_output.put_line('Found Global Schedule. GE_Period1_Start, GE_Period1_End,
1294 Period2 Start and End are :' || to_char(l_GE_Period1_Start) || ', ' ||
1295 to_char(l_GE_Period1_End) || ', ' || to_char(l_GE_Period2_start) || ', ' ||
1296 to_char(l_GE_Period2_End)); **/
1297 return 1;
1298 End If;
1299
1300 End Process_Glob_Elem_Schedule;
1301
1302 /*******************************************************************************************************
1303 This is a private procedure that is called while processing Element Type Schedules. This
1304 procedure checks if any uncovered Element Type schedule is covered by Element Group Schedules.
1305 If they are, then a value of 0 is returned, else, a value of 1 is returned.
1306 *******************************************************************************************************/
1307 Function Process_Element_Group_Schedule(l_Time_Period_Start DATE, l_Time_Period_End DATE,
1308 l_Assignment_ID Number, l_Element_Type_ID Number, l_EG_Period1_Start IN OUT NOCOPY DATE,
1309 l_EG_Period1_End IN OUT NOCOPY DATE, l_EG_Period2_Start IN OUT NOCOPY DATE, l_EG_Period2_End IN OUT NOCOPY DATE)
1310 return NUMBER IS
1311
1312 Cursor C1 is
1313 select DISTINCT c.ASSIGNMENT_ID,
1314 c.ELEMENT_GROUP_ID,
1315 d.SCHEDULE_BEGIN_DATE,
1316 d.SCHEDULE_END_DATE
1317 from PER_ASSIGNMENTS_F a,
1318 PSP_SCHEDULES b,
1319 PSP_SCHEDULE_HIERARCHY c,
1320 PSP_SCHEDULE_LINES d,
1321 PSP_GROUP_ELEMENT_LIST e
1322 where a.assignment_id = b.assignment_id
1323 and b.assignment_id = c.assignment_id
1324 and c.schedule_hierarchy_id = d.schedule_hierarchy_id
1325 and a.assignment_id = l_Assignment_ID
1326 and l_Time_Period_End Between a.EFFECTIVE_START_DATE and a.EFFECTIVE_END_DATE
1327 and not (d.schedule_begin_date <= l_Time_Period_Start
1328 or d.schedule_end_date >= l_Time_Period_End)
1329 and c.SCHEDULING_TYPES_CODE = 'EG'
1330 and e.element_group_id = c.ELEMENT_GROUP_ID
1331 and e.ELEMENT_GROUP_ID = (Select Distinct PGEL.ELEMENT_GROUP_ID
1332 from PSP_GROUP_ELEMENT_LIST PGEL,
1333 PSP_ELEMENT_GROUPS PEG
1334 where PGEL.Element_Type_ID = l_Element_Type_ID
1335 and PGEL.Element_Group_ID = PEG.Element_Group_ID
1336 and (PEG.Start_Date_Active Between
1337 l_Time_Period_Start and
1338 l_Time_Period_End)
1339 and (PEG.End_Date_Active Between l_Time_Period_Start and
1340 l_Time_Period_End));
1341 C1_Row C1%RowType;
1342 retVal Number;
1343 Begin
1344 -- First, set Element Group Period's start and end dates to NULL
1345 l_EG_Period1_Start := NULL;
1346 l_EG_Period1_End := NULL;
1347 l_EG_Period2_Start := NULL;
1348 l_EG_Period2_End := NULL;
1349
1350 /*** dbms_output.put_line('About to search for EG labor schedules with assignment of ' ||
1351 to_char(l_assignment_id) || ' and element type of ' || to_char(l_Element_Type_ID) || '
1352 and dates in ' || to_char(l_Time_Period_Start) || ' and ' ||
1353 to_char(l_Time_Period_End)); ***/
1354 Open C1;
1355 Fetch C1 into C1_Row;
1356 If C1%NotFound Then
1357 -- dbms_output.put_line('No data found. Have to do Assignment level Labor Scheduling');
1358 Close C1;
1359 l_EG_Period1_Start := l_Time_Period_Start;
1360 l_EG_Period1_End := l_Time_Period_End;
1361 return 0;
1362 Else
1363 -- dbms_output.put_line('Data found');
1364 Calculate_Split_Periods(l_Assignment_Id, C1_Row.Schedule_Begin_Date, C1_Row.Schedule_End_Date,
1365 l_Time_Period_Start, l_Time_Period_End, l_EG_Period1_Start, l_EG_Period1_End,
1366 l_EG_Period2_Start, l_EG_Period2_End);
1367 /**** dbms_output.put_line('Split Periods are as follows: Period1 Start - ' ||
1368 to_char(l_EG_Period1_start) || ', Period1 End - ' || to_char(l_EG_Period1_End)
1369 || ', Period2 Start - ' || to_char(l_EG_Period2_Start) || ', Period2 End - ' ||
1370 to_char(l_EG_Period2_End));***/
1371 return 1;
1372 End If;
1373 End Process_Element_Group_Schedule;
1374
1375 /*******************************************************************************************************
1376 PRIVATE PROCEDURE TO CALCULATE THE PERIODS TO BE SPLIT INTO BASED ON SCHEDULE BEGIN AND END
1377 DATES AND TIME PERIOD
1378 *******************************************************************************************************/
1379 PROCEDURE Calculate_Split_Periods(l_Assignment_Id NUMBER, l_Schedule_Begin_Date DATE, l_Schedule_End_Date DATE ,
1380 l_Time_Period_Start DATE, l_Time_Period_End DATE, l_Period1_Start IN OUT NOCOPY DATE, l_Period1_End IN OUT NOCOPY
1381 DATE, l_Period2_Start IN OUT NOCOPY DATE, l_Period2_End IN OUT NOCOPY DATE) IS
1382 Begin
1383 -- First, ensure that all variables that have to be returned are set to NULL
1384 l_Period1_Start := NULL;
1385 l_Period1_End := NULL;
1386 l_Period2_start := NULL;
1387 l_Period2_End := NULL;
1388 -- Case when the Time Period is fully encompassed within the Schedule Period.
1389 If l_Schedule_Begin_Date <= l_Time_Period_Start AND l_Schedule_End_Date >= l_Time_Period_End Then
1390 -- This record should not be displayed
1391 return;
1392 End If;
1393
1394 -- Case when The Schedule Period does not coincide with the Time Period at all
1395 If l_Schedule_End_Date < l_Time_Period_Start OR l_Schedule_Begin_Date > l_Time_Period_End Then
1396 l_Period1_Start := l_Time_Period_Start;
1397 l_Period1_End := l_Time_Period_End;
1398 return;
1399 End If;
1400
1401 -- Case when the Schedule Period is fully encompassed within the Time Period. =>(Two Split
1402 -- Periods)
1403 If l_Schedule_Begin_Date >= l_Time_Period_Start AND l_Schedule_End_Date <= l_Time_Period_End
1404 Then
1405 if l_schedule_begin_date = l_Time_period_Start AND l_schedule_end_date = l_Time_Period_End then
1406
1407 l_Period1_Start := NULL;
1408 l_Period1_End := NULL;
1409 end if;
1410
1411 if l_schedule_begin_date = l_Time_period_Start AND l_schedule_end_date < l_Time_Period_End then
1412 GET_SPLIT_AFTER(l_assignment_id, l_schedule_begin_date, l_schedule_end_date, l_time_period_start,
1413 l_time_period_end, l_period1_start, l_period1_end);
1414 end if;
1415
1416 if l_schedule_begin_date > l_Time_period_Start AND l_schedule_end_date = l_Time_Period_End then
1417 --dbms_output.put_line('Calculate split records condition 1 C');
1418 GET_SPLIT_BEFORE(l_assignment_id, l_schedule_begin_date, l_schedule_end_date, l_time_period_start,
1419 l_time_period_end, l_period1_start, l_period1_end);
1420 end if;
1421
1422 if l_schedule_begin_date > l_Time_period_Start AND l_schedule_end_date < l_Time_Period_End then
1423 --dbms_output.put_line('Calculate split records condition 1 D');
1424 GET_SPLIT_BEFORE(l_assignment_id, l_schedule_begin_date, l_schedule_end_date, l_time_period_start,
1425 l_time_period_end, l_period1_start, l_period1_end);
1426 GET_SPLIT_AFTER(l_assignment_id, l_schedule_begin_date, l_schedule_end_date, l_time_period_start,
1427 l_time_period_end, l_period2_start, l_period2_end);
1428 end if;
1429 If l_Period1_End < l_Period1_Start Then -- This split period is less than 1 day. So, set it to NULL.
1430 l_Period1_Start := NULL;
1431 l_Period1_End := NULL;
1432 End If;
1433
1434 If l_Period2_Start > l_Period2_End Then
1435 l_Period2_Start := NULL;
1436 l_Period2_End := NULL;
1437 End If;
1438
1439 return;
1440 End If;
1441
1442 -- Case when the Schedule Period Starts before Time Period Start and Ends within the Time Period
1443 If l_Schedule_Begin_Date <= l_Time_Period_Start AND l_Schedule_End_Date <= l_Time_Period_End
1444 Then
1445 if l_schedule_begin_date < l_Time_period_Start AND l_schedule_end_date = l_Time_Period_End then
1446 --dbms_output.put_line('Calculate split records condition 2 A');
1447 l_Period1_Start := NULL;
1448 l_Period1_End := NULL;
1449 end if;
1450 if l_schedule_begin_date < l_Time_period_Start AND l_schedule_end_date < l_Time_Period_End then
1451 --dbms_output.put_line('Calculate split records condition 2 B');
1452 GET_SPLIT_AFTER(l_assignment_id, l_schedule_begin_date, l_schedule_end_date, l_time_period_start,
1453 l_time_period_end, l_period1_start, l_period1_end);
1454 end if;
1455 If l_Period1_End < l_Period1_Start Then -- This split period is less than 1 day. So, set it to NULL.
1456 l_Period1_Start := NULL;
1457 l_Period1_End := NULL;
1458 End If;
1459
1460 return;
1461 End If;
1462
1463 -- Case when the Schedule Period Ends after Time Period End and starts within the Time Period
1464 If l_Schedule_Begin_Date >= l_Time_Period_Start AND l_Schedule_End_Date >= l_Time_Period_End
1465 Then
1466 if l_schedule_begin_date = l_Time_period_Start AND l_schedule_end_date > l_Time_Period_End then
1467 --dbms_output.put_line('Calculate split records condition 3 A');
1468 l_Period1_Start := NULL;
1469 l_Period1_End := NULL;
1470 end if;
1471 if l_schedule_begin_date > l_Time_period_Start AND l_schedule_end_date > l_Time_Period_End then
1472 --dbms_output.put_line('Calculate split records condition 3 B');
1473 GET_SPLIT_BEFORE(l_assignment_id, l_schedule_begin_date, l_schedule_end_date, l_time_period_start,
1474 l_time_period_end, l_period1_start, l_period1_end);
1475 end if;
1476 If l_Period1_End < l_Period1_Start Then -- This split period is less than 1 day. So, set it to NULL.
1477 l_Period1_Start := NULL;
1478 l_Period1_End := NULL;
1479 End If;
1480
1481 return;
1482 End If;
1483
1484 Exception
1485 when OTHERS Then
1486 --dbms_output.put_line('Error occured while processing Split Period Calculator');
1487 return;
1488 End Calculate_Split_Periods;
1489 /********************************************************************************************************
1490 PRIVATE PROCEDURE TO CALCULATE THE SPLIT BEFORE THE SCHEDULE BASED UPON THE
1491 ASSIGNMENT, SCHEDULE BEGIN DATE, SCHEDULE END DATE and TIME PERIOD
1492 *******************************************************************************************************/
1493 PROCEDURE GET_SPLIT_BEFORE(l_Assignment_Id Number, l_Schedule_Begin_Date DATE, l_Schedule_End_Date DATE,
1494 l_Time_Period_Start DATE, l_Time_Period_End DATE, l_Period1_Start IN OUT NOCOPY DATE,
1495 l_Period1_End IN OUT NOCOPY DATE) is
1496 l_max_schedule_end_date DATE;
1497 l_temp_schedule_begin_date DATE;
1498 begin
1499 -- First, ensure that all variables that have to be returned are set to NULL
1500 l_Period1_Start := NULL;
1501 l_Period1_End := NULL;
1502 Select MAX(SCHEDULE_END_DATE) into l_max_schedule_end_date
1503 from PSP_REP_SCH_EMP_V
1504 where assignment_id = l_assignment_id
1505 and schedule_end_date < l_schedule_begin_date
1506 and schedule_end_date >= l_time_period_start
1507 and not exists (select assignment_id from PSP_REP_SCH_EMP_V
1508 where assignment_id = l_assignment_id and
1509 l_schedule_begin_date between schedule_begin_date and schedule_end_date
1510 and schedule_begin_date < l_schedule_begin_date);
1511 if l_schedule_begin_date - l_max_schedule_end_date <=1 then
1512 return;
1513 end if;
1514 --dbms_output.put_line('max end date : '||to_char(l_max_schedule_end_date));
1515 if l_max_schedule_end_date is not NULL then
1516 l_Period1_Start := l_max_schedule_end_date + 1;
1517 l_Period1_End := l_Schedule_begin_date -1;
1518 --dbms_output.put_line('Split before.. l_Period1_start ' || to_char(l_Period1_Start));
1519 --dbms_output.put_line('Split before.. l_Period1_end ' || to_char(l_Period1_End));
1520 return;
1521 else
1522 begin
1523 select min(schedule_begin_date) into l_temp_schedule_begin_date
1524 from PSP_REP_SCH_EMP_V
1525 where assignment_id = l_assignment_id and
1526 l_schedule_begin_date between schedule_begin_date and schedule_end_date
1527 and schedule_begin_date < l_schedule_begin_date;
1528 if l_temp_schedule_begin_date is not NULL then
1529 if l_Time_Period_Start < l_temp_Schedule_Begin_Date then
1530 l_Period1_Start := l_Time_Period_Start;
1531 l_Period1_End := l_temp_Schedule_Begin_Date -1;
1532 return;
1533 end if;
1534 else
1535 l_Period1_Start := l_Time_Period_Start;
1536 l_Period1_End := l_Schedule_Begin_Date -1;
1537 --dbms_output.put_line(to_char(l_period1_start)||' '||to_char(l_period1_end));
1538 return;
1539 end if;
1540 end;
1541 end if;
1542 return;
1543 exception
1544 when no_data_found then
1545 return;
1546 end;
1547 /********************************************************************************************************
1548 PRIVATE PROCEDURE TO CALCULATE THE SPLIT AFTER THE SCHEDULE BASED UPON THE
1549 ASSIGNMENT, SCHEDULE BEGIN DATE, SCHEDULE END DATE and TIME PERIOD
1550 *******************************************************************************************************/
1551
1552 PROCEDURE GET_SPLIT_AFTER(l_Assignment_Id Number, l_Schedule_Begin_Date DATE, l_Schedule_End_Date DATE,
1553 l_Time_Period_Start DATE, l_Time_Period_End DATE,
1554 l_Period2_Start IN OUT NOCOPY DATE, l_Period2_End IN OUT NOCOPY DATE) is
1555 l_min_schedule_begin_date DATE;
1556 l_temp_schedule_end_date date;
1557 begin
1558 -- First, ensure that all variables that have to be returned are set to NULL
1559 l_Period2_start := NULL;
1560 l_Period2_End := NULL;
1561 Select MIN(SCHEDULE_BEGIN_DATE) into l_min_schedule_begin_date
1562 from PSP_REP_SCH_EMP_V
1563 where assignment_id = l_assignment_id
1564 and schedule_begin_date > l_schedule_end_date
1565 and schedule_begin_date <= l_time_period_end
1566 and not exists (select assignment_id from PSP_REP_SCH_EMP_V
1567 where assignment_id = l_assignment_id and
1568 l_schedule_end_date between schedule_begin_date and schedule_end_date
1569 and schedule_end_date > l_schedule_end_date);
1570 if l_min_schedule_begin_date - l_schedule_begin_date <=1 then
1571 return;
1572 end if;
1573 if l_min_schedule_begin_date is not NULL then
1574 l_Period2_Start := l_Schedule_End_date + 1;
1575 l_Period2_End := l_min_schedule_begin_date - 1;
1576 --dbms_output.put_line('Split after.. l_Period2_start ' || to_char(l_Period2_Start));
1577 --dbms_output.put_line('Split after.. l_Period2_end ' || to_char(l_Period2_End));
1578 return;
1579 else
1580 begin
1581 select max(schedule_end_date) into l_temp_schedule_end_date
1582 from PSP_REP_SCH_EMP_V
1583 where assignment_id = l_assignment_id and
1584 l_schedule_end_date between schedule_begin_date and schedule_end_date
1585 and schedule_end_date > l_schedule_end_date;
1586 if l_temp_schedule_end_date is not NULL then
1587 if l_time_period_end > l_temp_schedule_end_date then
1588 l_Period2_Start := l_temp_schedule_end_date + 1;
1589 l_Period2_End := l_Time_period_end;
1590 return;
1591 end if;
1592 else
1593 l_Period2_Start := l_Schedule_End_Date + 1;
1594 l_Period2_End := l_Time_Period_End;
1595 return;
1596 end if;
1597 end;
1598 end if;
1599 return;
1600 exception
1601 when no_data_found then
1602 return;
1603 end;
1604 /********************************************************************************************************
1605 Though this procedure accepts Schedule Line Numbers, this is no longer adding these line
1606 numbers to the PSP_REP_ORG_DLS table. Instead, the Org_Schedule_ID from PSP_DEFAULT_LABOR_SCHEDULES
1607 is being added. The report will have to obtain the Default Labor Schedule Charging Instruction from
1608 this.
1609 *********************************************************************************************************/
1610 PROCEDURE Add_If_Within_DLS(v_Organization_ID Number, v_Begin_Date DATE,
1611 v_End_Date DATE, v_Assignment_ID Number, v_Person_ID Number, v_Element_Type_ID Number,
1612 v_Original_Sch_Code varchar2, v_Schedule_Line_ID Number) IS
1613 Cursor C1 is
1614 Select Org_Schedule_ID, Schedule_Begin_Date, Schedule_End_Date
1615 from PSP_DEFAULT_LABOR_SCHEDULES
1616 where Organization_ID = v_Organization_ID;
1617 DLS_Begin_Date DATE;
1618 DLS_End_Date DATE;
1619 Begin
1620
1621 /*** dbms_output.put_line ('Processing Record with Schedule of ' || v_Original_Sch_Code || ' for
1622 Org : ' || to_char(v_Organization_ID));
1623 dbms_output.put_line('Processing record for Schedule Begin and End Dates of ' ||
1624 to_char(v_Begin_Date) || ' and ' || to_char(v_End_Date)); ***/
1625 For C1_Row IN C1 LOOP
1626 /***dbms_output.put_line('Inside cursor loop. DLS Schedule Begin and End Dates are ' ||
1627 to_char(C1_Row.Schedule_Begin_Date) || ' and ' || to_char(C1_Row.Schedule_End_Date));***/
1628 If C1_Row.Schedule_Begin_Date <= v_Begin_Date AND C1_Row.Schedule_End_Date >= v_End_Date
1629 Then
1630 DLS_Begin_Date := v_Begin_Date;
1631 DLS_End_Date := v_End_Date;
1632 /*dbms_output.put_line('Adding record with Begin and End Dates of :' ||
1633 to_char(DLS_Begin_Date) || ' and ' || to_char(DLS_End_Date));*/
1634 Table_Insert(Assignment_ID => v_Assignment_ID, Organization_ID
1635 => v_Organization_ID, Person_ID => v_Person_ID,
1636 Element_Type_ID => v_Element_Type_ID, Begin_Date =>
1637 DLS_Begin_Date, End_Date => DLS_End_Date,
1638 Original_Sch_Code => v_Original_Sch_Code, Schedule_Line_ID =>
1639 C1_Row.Org_Schedule_ID);
1640 Elsif C1_Row.Schedule_Begin_Date >= v_Begin_Date AND C1_Row.Schedule_End_Date <= v_End_Date
1641 Then
1642 DLS_Begin_Date := C1_Row.Schedule_Begin_Date;
1643 DLS_End_Date := C1_Row.Schedule_End_Date;
1644 /*dbms_output.put_line('Adding record with Begin and End Dates of :' ||
1645 to_char(DLS_Begin_Date) || ' and ' || to_char(DLS_End_Date));*/
1646 Table_Insert(Assignment_ID => v_Assignment_ID, Organization_ID
1647 => v_Organization_ID, Person_ID => v_Person_ID,
1648 Element_Type_ID => v_Element_Type_ID, Begin_Date =>
1649 DLS_Begin_Date, End_Date => DLS_End_Date,
1650 Original_Sch_Code => v_Original_Sch_Code, Schedule_Line_ID =>
1651 C1_Row.Org_Schedule_ID);
1652 Elsif C1_Row.Schedule_Begin_Date <= v_Begin_Date AND C1_Row.Schedule_End_Date <= v_End_Date
1653 AND NOT (C1_Row.Schedule_End_Date <= v_Begin_Date) Then
1654 DLS_Begin_Date := v_Begin_Date;
1655 DLS_End_Date := C1_Row.Schedule_End_Date;
1656
1657 /*dbms_output.put_line('Adding record with Begin and End Dates of :' ||
1658 to_char(DLS_Begin_Date) || ' and ' || to_char(DLS_End_Date));*/
1659 Table_Insert(Assignment_ID => v_Assignment_ID, Organization_ID
1660 => v_Organization_ID, Person_ID => v_Person_ID,
1661 Element_Type_ID => v_Element_Type_ID, Begin_Date =>
1662 DLS_Begin_Date, End_Date => DLS_End_Date,
1663 Original_Sch_Code => v_Original_Sch_Code, Schedule_Line_ID =>
1664 C1_Row.Org_Schedule_ID);
1665 Elsif C1_Row.Schedule_Begin_Date >= v_Begin_Date AND C1_Row.Schedule_End_Date >= v_End_Date
1666 AND Not(C1_Row.Schedule_Begin_Date >= v_End_Date) Then
1667 DLS_Begin_Date := C1_Row.Schedule_Begin_Date;
1668 DLS_End_Date := v_End_Date;
1669 /**dbms_output.put_line('Adding record with Begin and End Dates of :' ||
1670 to_char(DLS_Begin_Date) || ' and ' || to_char(DLS_End_Date)); **/
1671 Table_Insert(Assignment_ID => v_Assignment_ID, Organization_ID
1672 => v_Organization_ID, Person_ID => v_Person_ID,
1673 Element_Type_ID => v_Element_Type_ID, Begin_Date =>
1674 DLS_Begin_Date, End_Date => DLS_End_Date,
1675 Original_Sch_Code => v_Original_Sch_Code, Schedule_Line_ID =>
1676 C1_Row.Org_Schedule_ID);
1677 End If;
1678 End LOOP;
1679
1680 return;
1681
1682 End Add_If_Within_DLS;
1683
1684 END;
[Home] [Help]