[Home] [Help]
PACKAGE BODY: APPS.BIM_PREV_PERIODS_PKG
Source
1 PACKAGE BODY BIM_PREV_PERIODS_PKG AS
2 /* $Header: bimbppb.pls 115.4 2000/07/27 17:30:12 pkm ship $ */
3
4 PROCEDURE BIM_PREV_PERIODS(
5 p_period_type IN VARCHAR2
6 ,p_start_date IN DATE
7 ,p_end_date IN DATE
8 ,p_prev_start_date IN OUT DATE
9 ,p_prev_END_date IN OUT DATE
10 )
11 AS
12 l_first number;
13 l_second number;
14 l_result number;
15 l_current_start_num number(15);
16 l_current_END_num number(15);
17
18 l_current_start_year number(15);
19 l_current_END_year number(15);
20
21 l_prev_start_num number;
22 l_prev_END_num number;
23
24 l_prev_start_year number(15);
25 l_prev_END_year number(15);
26
27
28 l_no_of_quarters number(2);
29
30 l_current_start_quarter_num number(15);
31 l_current_END_quarter_num number(15);
32
33 BEGIN
34
35 BEGIN
36 SELECT period_num
37 ,period_year
38 ,quarter_num
39 INTO l_current_start_num
40 ,l_current_start_year
41 ,l_current_start_quarter_num
42 FROM gl_periods
43 WHERE start_date = p_start_date
44 AND period_type = p_period_type
45 AND rownum < 2;
46 EXCEPTION
47 WHEN NO_DATA_FOUND THEN
48 NULL;
49 END;
50
51 BEGIN
52 SELECT period_num
53 ,period_year
54 ,quarter_num
55 INTO l_current_END_num
56 ,l_current_END_year
57
58 ,l_current_END_quarter_num
59 FROM gl_periods
60 WHERE END_date = p_END_date
61 AND period_type = p_period_type
62 AND rownum < 2;
63 EXCEPTION
64 WHEN NO_DATA_FOUND THEN
65 NULL;
66 END;
67
68 /*----------------------------- FOR THE PERIOD TYPE MONTH -----------------------*/
69
70 IF (p_period_type = 'Month') THEN
71
72 l_prev_start_num := l_current_start_num - 1;
73 l_prev_END_num := l_current_END_num - 1;
74
75
76 IF (l_prev_start_num = 0) THEN
77 l_prev_start_year := l_current_start_year - 1;
78 l_prev_start_num := 12;
79 ELSE
80 l_prev_start_year := l_current_start_year;
81 END IF;
82
83
84 IF (l_prev_END_num = 0) THEN
85
86 l_prev_END_year := l_current_END_year - 1;
87 l_prev_END_num := 12;
88 ELSE
89 l_prev_END_year := l_current_END_year;
90 END IF;
91
92 END IF; /* END OF MONTH LOGIC */
93
94 /*----------------------------- FOR THE PERIOD TYPE QUARTER -----------------------*/
95
96 IF (p_period_type = 'Quarter') THEN
97 l_no_of_quarters := (l_current_start_num - l_current_END_num) + 1;
98
99 l_prev_start_num := l_current_start_num - l_no_of_quarters;
100 l_prev_END_num := l_current_END_num - l_no_of_quarters;
101
102
103 IF (l_prev_start_num = 0) THEN
104 l_prev_start_num := 4;
105 l_prev_start_year := l_current_start_year - 1;
106 ELSE
107 l_prev_start_year := l_current_start_year;
108
109 END IF;
110
111 IF (l_prev_END_num = 0) THEN
112 l_prev_END_num := 4;
113 l_prev_END_year := l_current_END_year - 1;
114 ELSE
115 l_prev_END_year := l_current_END_year;
116 END IF;
117
118 END IF; /* END OF QUARTER LOGIC */
119
120 /*----------------------------- FOR THE PERIOD TYPE YEAR -----------------------*/
121
122 IF (p_period_type = 'Year') THEN
123 l_prev_start_year := l_current_start_year - 1;
124 l_prev_END_year := l_current_END_year - 1;
125
126 l_prev_start_num := l_current_start_num;
127 l_prev_END_num := l_current_END_num;
128 END IF; /* END OF YEAR LOGIC */
129
130
131 /*------------------------------------------------------------------------------*/
132
133 BEGIN
134 SELECT start_date
135 INTO p_prev_start_date
136 FROM gl_periods
137 WHERE period_num = l_prev_start_num
138 AND period_year = l_prev_start_year
139 AND period_type = p_period_type
140 AND rownum < 2;
141 EXCEPTION
142 WHEN NO_DATA_FOUND THEN
143 NULL;
144 END;
145
146 BEGIN
147 SELECT END_date
148 INTO p_prev_END_date
149 FROM gl_periods
150 WHERE period_num = l_prev_END_num
151 AND period_year = l_prev_END_year
152 AND period_type = p_period_type
153 AND rownum < 2;
154 EXCEPTION
155 WHEN NO_DATA_FOUND THEN
156 NULL;
157 END;
158
159
160 END BIM_PREV_PERIODS;
161
162 END BIM_PREV_PERIODS_PKG ;