1 package body PA_DATE_OVERLAP_CHECKS as
2 -- $Header: PAXDTCHB.pls 120.2 2005/08/08 12:40:10 sbharath noship $
3
4
5 --
6 -- PROCEDURE
7 -- date_overlap_check_lcm
8 -- PURPOSE
9 -- This procedure checks if there are any labor
10 -- cost multipliers whose dates are overlapping.
11 -- If overlapping dates exists then it returns
12 -- 0 along with the labor cost multiplier name.
13 -- HISTORY
14 -- 14-FEB-96 Sandeep Created
15 --
16 Procedure date_overlap_check_lcm
17 ( X_Status Out NOCOPY Number,
18 X_Error_Text Out NOCOPY Varchar2,
19 X_Labor_Cost_Multiplier_Name Out NOCOPY Varchar2 ) is
20
21
22 cursor c1 is
23 select a.labor_cost_multiplier_name
24 from pa_labor_cost_multipliers a,
25 pa_labor_cost_multipliers b
26 where a.labor_cost_multiplier_name = b.labor_cost_multiplier_name
27 and (a.start_date_active between b.start_date_active
28 and nvl(b.end_date_active, a.start_date_active + 1)
29 or a.end_date_active between b.start_date_active
30 and nvl(b.end_date_active, a.end_date_active + 1)
31 or b.start_date_active between a.start_date_active
32 and nvl(a.end_date_active, b.start_date_active + 1))
33 and a.rowid <> b.rowid ;
34
35 X_LCM_Name Varchar2(100);
36
37 Begin
38 open c1;
39 fetch c1
40 into X_Labor_Cost_Multiplier_Name;
41
42 if c1%found then
43 X_Status := 0 ;
44 -- X_Labor_Cost_Multiplier_Name := X_LCM_Name ;
45 else
46 close c1;
47 raise no_data_found;
48 end if;
49 close c1;
50
51 Exception
52 When Others Then
53 X_Status := SQLCODE;
54 X_Error_Text := SQLERRM;
55 X_Labor_Cost_Multiplier_Name := NULL;
56 End date_overlap_check_lcm;
57
58
59 END PA_DATE_OVERLAP_CHECKS;
[Home] [Help]