[Home] [Help]
PACKAGE BODY: APPS.BSC_PERIODICITY_PVT
Source
1 PACKAGE BODY BSC_PERIODICITY_PVT AS
2 /* $Header: BSCVPITB.pls 120.0 2005/07/21 23:35:41 appldev noship $ */
3 /*
4 REM +=======================================================================+
5 REM | Copyright (c) 2004 Oracle Corporation, Redwood Shores, CA, USA |
6 REM | All rights reserved. |
7 REM +=======================================================================+
8 REM | FILENAME |
9 REM | BSCVPCTB.pls |
10 REM | |
11 REM | DESCRIPTION |
12 REM | Module: Private package for populating the calendar tables |
13 REM | NOTES |
14 REM | 07-JUN-2005 Krishna Created. |
15 REM +=======================================================================+
16 */
17
18 G_PKG_NAME CONSTANT VARCHAR2(30):='BSC_PERIODICITY_PVT';
19
20
21 PROCEDURE Create_Periods
22 ( p_Api_Version IN NUMBER
23 , p_Commit IN VARCHAR2 := FND_API.G_FALSE
24 , p_Period_Record IN BSC_PERIODS_PUB.Period_Record
25 , x_Return_Status OUT NOCOPY VARCHAR2
26 , x_Msg_Count OUT NOCOPY NUMBER
27 , x_Msg_Data OUT NOCOPY VARCHAR2
28 )IS
29 l_Is_Daily_Periodicity BOOLEAN := FALSE;
30 l_Fiscal_Year NUMBER;
31 --l_period_Varray_Record BSC_PERIODS_PUB.Start_End_Period_Varray;
32 l_Start_Date DATE;
33 l_End_Date DATE;
34 l_Start_Period NUMBER;
35 l_End_Period NUMBER;
36 x_Start_Period NUMBER;
37 x_End_Period NUMBER;
38 l_No_Base_Periods NUMBER;
39 BEGIN
40 SAVEPOINT CreatePeriodsPVTSP;
41 FND_MSG_PUB.Initialize;
42 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
43
44 IF(p_Period_Record.Base_Periodicity_Id IS NOT NULL) THEN
45 l_Is_Daily_Periodicity := BSC_PERIODS_UTILITY_PKG.Is_Base_Periodicity_Daily(p_Period_Record.Base_Periodicity_Id);
46 END IF;
47
48 l_Fiscal_Year := BSC_PERIODS_UTILITY_PKG.Get_FiscalYear_By_Calendar(p_Period_Record.Calendar_Id);
49
50 IF(p_Period_Record.Periods IS NOT NULL) THEN
51
52 FOR i IN 1..p_Period_Record.period_Varry.COUNT LOOP
53 IF(l_Is_Daily_Periodicity) THEN
54 --dbms_output.put_line('INSIDE DAILY PERIODICITY');
55 l_Start_Date := TO_DATE(p_Period_Record.period_Varry(i).Start_Period,'MM/DD/YY');
56 l_End_Date := TO_DATE(p_Period_Record.period_Varry(i).End_Period,'MM/DD/YY');
57 --dbms_output.put_line(' l_Start_Date:-' || l_Start_Date);
58 --dbms_output.put_line(' l_End_Date:-' || l_End_Date);
59 l_Start_Period := 0;
60 l_End_Period := 0;
61 IF(i = 1) THEN
62 l_Start_Period := 1;
63 END IF;
64 IF(i = p_Period_Record.period_Varry.COUNT) THEN
65 l_End_Period := 365;
66 END IF;
67 ELSE
68 --dbms_output.put_line('INSIDE OUT OF DAILY PERIODICITY');
69 l_Start_Period := p_Period_Record.period_Varry(i).Start_Period;
70 l_End_Period := p_Period_Record.period_Varry(i).End_Period;
71 l_Start_Date := NULL;
72 l_End_Date := NULL;
73 END IF;
74
75 INSERT INTO bsc_sys_periods
76 ( periodicity_id
77 , year
78 , period_id
79 , start_date
80 , end_date
81 , start_period
82 , end_period
83 , created_by
84 , creation_date
85 , last_updated_by
86 , last_update_date
87 , last_update_login
88 )VALUES
89 (
90 p_Period_Record.Periodicity_Id
91 , l_Fiscal_Year
92 , i
93 , l_Start_Date
94 , l_End_Date
95 , l_Start_Period
96 , l_End_Period
97 , NVL(p_Period_Record.Created_By,FND_GLOBAL.USER_ID)
98 , NVL(p_Period_Record.Creation_Date,SYSDATE)
99 , NVL(p_Period_Record.Last_Updated_By,FND_GLOBAL.USER_ID)
100 , NVL(p_Period_Record.Last_Update_Date,SYSDATE)
101 , NVL(p_Period_Record.Last_Update_Login,FND_GLOBAL.LOGIN_ID)
102 );
103
104 END LOOP;
105 END IF;
106
107 IF ((p_Commit IS NOT NULL) AND (p_Commit = FND_API.G_TRUE)) THEN
108 COMMIT;
109 END IF;
110
111 EXCEPTION
112 WHEN NO_DATA_FOUND THEN
113 ROLLBACK TO CreatePeriodsPVTSP;
114 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
115 IF (x_msg_data IS NOT NULL) THEN
116 x_msg_data := x_msg_data||' -> BSC_PERIODICITY_PVT.Create_Periods ';
117 ELSE
118 x_msg_data := SQLERRM||' at BSC_PERIODICITY_PVT.Create_Periods ';
119 END IF;
120 WHEN OTHERS THEN
121 ROLLBACK TO CreatePeriodsPVTSP;
122 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
123 IF (x_msg_data IS NOT NULL) THEN
124 x_msg_data := x_msg_data||' -> BSC_PERIODICITY_PVT.Create_Periods ';
125 ELSE
126 x_msg_data := SQLERRM||' at BSC_PERIODICITY_PVT.Create_Periods ';
127 END IF;
128 END Create_Periods;
129
130 /****************************************************************************************/
131
132 PROCEDURE Update_Periods
133 ( p_Api_Version IN NUMBER
134 , p_Commit IN VARCHAR2 := FND_API.G_FALSE
135 , p_Period_Record IN BSC_PERIODS_PUB.Period_Record
136 , x_Return_Status OUT NOCOPY VARCHAR2
137 , x_Msg_Count OUT NOCOPY NUMBER
138 , x_Msg_Data OUT NOCOPY VARCHAR2
139 )IS
140 l_System_Stage bsc_sys_init.property_value%TYPE;
141 BEGIN
142 SAVEPOINT UpdatePeriodsPVTSP;
143 FND_MSG_PUB.Initialize;
144 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
145
146 BSC_PERIODICITY_PVT.Delete_Periods
147 ( p_Api_Version => p_Api_Version
148 , p_Commit => p_Commit
149 , p_Period_Record => p_Period_Record
150 , x_Return_Status => x_Return_Status
151 , x_Msg_Count => x_Msg_Count
152 , x_Msg_Data => x_Msg_Data
153 );
154 IF(x_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
155 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
156 END IF;
157
158 BSC_PERIODICITY_PVT.Create_Periods
159 ( p_Api_Version => p_Api_Version
160 , p_Commit => p_Commit
161 , p_Period_Record => p_Period_Record
162 , x_Return_Status => x_Return_Status
163 , x_Msg_Count => x_Msg_Count
164 , x_Msg_Data => x_Msg_Data
165 );
166 IF(x_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
167 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
168 END IF;
169
170 IF ((p_Commit IS NOT NULL) AND (p_Commit = FND_API.G_TRUE)) THEN
171 COMMIT;
172 END IF;
173
174 EXCEPTION
175 WHEN NO_DATA_FOUND THEN
176 ROLLBACK TO UpdatePeriodsPVTSP;
177 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
178 IF (x_msg_data IS NOT NULL) THEN
179 x_msg_data := x_msg_data||' -> BSC_PERIODICITY_PVT.Update_Periods ';
180 ELSE
181 x_msg_data := SQLERRM||' at BSC_PERIODICITY_PVT.Update_Periods ';
182 END IF;
183 WHEN OTHERS THEN
184 ROLLBACK TO UpdatePeriodsPVTSP;
185 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
186 IF (x_msg_data IS NOT NULL) THEN
187 x_msg_data := x_msg_data||' -> BSC_PERIODICITY_PVT.Update_Periods ';
188 ELSE
189 x_msg_data := SQLERRM||' at BSC_PERIODICITY_PVT.Update_Periods ';
190 END IF;
191 END Update_Periods;
192
193 /**********************************************************************************/
194
195 PROCEDURE Delete_Periods
196 ( p_Api_Version IN NUMBER
197 , p_Commit IN VARCHAR2 := FND_API.G_FALSE
198 , p_Period_Record IN BSC_PERIODS_PUB.Period_Record
199 , x_Return_Status OUT NOCOPY VARCHAR2
200 , x_Msg_Count OUT NOCOPY NUMBER
201 , x_Msg_Data OUT NOCOPY VARCHAR2
202 )IS
203
204 BEGIN
205 SAVEPOINT DeletePeriodsPVTSP;
206 FND_MSG_PUB.Initialize;
207 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
208
209 DELETE bsc_sys_periods
210 WHERE periodicity_id = p_Period_Record.Periodicity_Id;
211
212 DELETE bsc_sys_periods_tl
213 WHERE periodicity_id = p_Period_Record.Periodicity_Id;
214
215 IF ((p_Commit IS NOT NULL) AND (p_Commit = FND_API.G_TRUE)) THEN
216 COMMIT;
217 END IF;
218
219 EXCEPTION
220 WHEN NO_DATA_FOUND THEN
221 ROLLBACK TO DeletePeriodsPVTSP;
222 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
223 IF (x_msg_data IS NOT NULL) THEN
224 x_msg_data := x_msg_data||' -> BSC_PERIODICITY_PVT.Delete_Periods ';
225 ELSE
226 x_msg_data := SQLERRM||' at BSC_PERIODICITY_PVT.Delete_Periods ';
227 END IF;
228 WHEN OTHERS THEN
229 ROLLBACK TO DeletePeriodsPVTSP;
230 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
231 IF (x_msg_data IS NOT NULL) THEN
232 x_msg_data := x_msg_data||' -> BSC_PERIODICITY_PVT.Delete_Periods ';
233 ELSE
234 x_msg_data := SQLERRM||' at BSC_PERIODICITY_PVT.Delete_Periods ';
235 END IF;
236 END Delete_Periods;
237 /**********************************************************************************/
238
239
240 END BSC_PERIODICITY_PVT;