[Home] [Help]
PACKAGE BODY: APPS.PA_SCH_EXCEPT_HIST_PKG
Source
1 PACKAGE BODY PA_SCH_EXCEPT_HIST_PKG as
2 /* $Header: PARGHISB.pls 120.1 2005/08/19 16:53:04 mwasowic noship $*/
3
4 l_empty_tab_record EXCEPTION; -- Variable to raise the exception if the passing table of records is empty
5
6 -- This procedure will insert the record in pa_schedule_except_history
7 -- Input parameters
8 -- Parameters Type Required Description
9 -- P_Sch_Except_Record_Tab SchExceptTabTyp YES It contains the exception record
10 --
11
12 PROCEDURE insert_rows (
13 p_sch_except_record_tab IN PA_SCHEDULE_GLOB.SchExceptTabTyp,
14 p_change_id IN PA_SCHEDULE_EXCEPT_HISTORY.change_id%type,
15 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
16 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
17 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
18 IS
19
20 l_schedule_exception_id PA_PLSQL_DATATYPES.IdTabTyp;
21 l_calendar_id PA_PLSQL_DATATYPES.IdTabTyp;
22 l_assignment_id PA_PLSQL_DATATYPES.IdTabTyp;
23 l_project_id PA_PLSQL_DATATYPES.IdTabTyp;
24 l_schedule_type_code PA_PLSQL_DATATYPES.Char30TabTyp;
25 l_assignment_status_code PA_PLSQL_DATATYPES.Char30TabTyp;
26 l_exception_type_code PA_PLSQL_DATATYPES.Char30TabTyp;
27 l_start_date PA_PLSQL_DATATYPES.DateTabTyp;
28 l_end_date PA_PLSQL_DATATYPES.DateTabTyp;
29 l_resource_calendar_percent PA_PLSQL_DATATYPES.NumTabTyp;
30 l_non_working_day_flag PA_PLSQL_DATATYPES.Char1TabTyp;
31 l_change_hours_type_code PA_PLSQL_DATATYPES.Char30TabTyp;
32 l_change_calendar_type_code PA_PLSQL_DATATYPES.Char30TabTyp;
33 l_change_calendar_id PA_PLSQL_DATATYPES.NumTabTyp;
34 l_monday_hours PA_PLSQL_DATATYPES.NumTabTyp;
35 l_tuesday_hours PA_PLSQL_DATATYPES.NumTabTyp;
36 l_wednesday_hours PA_PLSQL_DATATYPES.NumTabTyp;
37 l_thursday_hours PA_PLSQL_DATATYPES.NumTabTyp;
38 l_friday_hours PA_PLSQL_DATATYPES.NumTabTyp;
39 l_saturday_hours PA_PLSQL_DATATYPES.NumTabTyp;
40 l_sunday_hours PA_PLSQL_DATATYPES.NumTabTyp;
41
42
43 BEGIN
44
45 x_return_status := FND_API.G_RET_STS_SUCCESS;
46
47 /* Checking for the empty table of record */
48 IF (p_sch_except_record_tab.count = 0 ) THEN
49 raise l_empty_tab_record;
50 END IF;
51
52 FOR l_J IN p_sch_except_record_tab.first..p_sch_except_record_tab.last LOOP
53
54 l_schedule_exception_id(l_j) := p_sch_except_record_tab(l_j).schedule_exception_id;
55 l_calendar_id(l_J) := p_sch_except_record_tab(l_J).calendar_id;
56 l_assignment_id(l_J) := p_sch_except_record_tab(l_J).assignment_id;
57 l_project_id(l_J) := p_sch_except_record_tab(l_J).project_id;
58 l_schedule_type_code(l_J) := p_sch_except_record_tab(l_J).schedule_type_code;
59 l_assignment_status_code(l_J) := p_sch_except_record_tab(l_J).assignment_status_code;
60 l_exception_type_code(l_J) := p_sch_except_record_tab(l_J).exception_type_code;
61 l_start_date(l_J) := p_sch_except_record_tab(l_J).start_date;
62 l_end_date(l_J) := p_sch_except_record_tab(l_J).end_date;
63 l_resource_calendar_percent(l_J) := p_sch_except_record_tab(l_J).resource_calendar_percent;
64 l_non_working_day_flag(l_J) := p_sch_except_record_tab(l_J).non_working_day_flag;
65 l_change_hours_type_code(l_J) := p_sch_except_record_tab(l_J).change_hours_type_code;
66 l_change_calendar_type_code(l_J) := p_sch_except_record_tab(l_J).change_calendar_type_code;
67 l_change_calendar_id(l_J) := p_sch_except_record_tab(l_J).change_calendar_id;
68 l_monday_hours(l_J) := p_sch_except_record_tab(l_J).monday_hours;
69 l_tuesday_hours(l_J) := p_sch_except_record_tab(l_J).tuesday_hours;
70 l_wednesday_hours(l_J) := p_sch_except_record_tab(l_J).wednesday_hours;
71 l_thursday_hours(l_J) := p_sch_except_record_tab(l_J).thursday_hours;
72 l_friday_hours(l_J) := p_sch_except_record_tab(l_J).friday_hours;
73 l_saturday_hours(l_J) := p_sch_except_record_tab(l_J).saturday_hours;
74 l_sunday_hours(l_J) := p_sch_except_record_tab(l_J).sunday_hours;
75
76 END LOOP;
77
78
79 FORALL l_J IN p_sch_except_record_tab.first..p_sch_except_record_tab.last
80 INSERT INTO PA_SCHEDULE_EXCEPT_HISTORY
81 ( schedule_exception_id ,
82 calendar_id ,
83 assignment_id ,
84 project_id ,
85 schedule_type_code ,
86 status_code ,
87 exception_type_code ,
88 start_date ,
89 end_date ,
90 resource_calendar_percent ,
91 non_working_day_flag ,
92 change_hours_type_code ,
93 change_calendar_type_code,
94 change_calendar_id ,
95 monday_hours ,
96 tuesday_hours ,
97 wednesday_hours ,
98 thursday_hours ,
99 friday_hours ,
100 saturday_hours ,
101 sunday_hours ,
102 change_id ,
103 creation_date ,
104 created_by ,
105 last_update_date ,
106 last_update_by ,
107 last_update_login )
108 VALUES
109 ( l_schedule_exception_id(l_J) ,
110 l_calendar_id(l_J) ,
111 l_assignment_id(l_J) ,
112 l_project_id(l_J) ,
113 l_schedule_type_code(l_J) ,
114 l_assignment_status_code(l_J) ,
115 l_exception_type_code(l_J) ,
116 l_start_date(l_J) ,
117 l_end_date(l_J) ,
118 l_resource_calendar_percent(l_J) ,
119 l_non_working_day_flag(l_J) ,
120 l_change_hours_type_code(l_J) ,
121 l_change_calendar_type_code(l_J),
122 l_change_calendar_id(l_J) ,
123 l_monday_hours(l_J) ,
124 l_tuesday_hours(l_J) ,
125 l_wednesday_hours(l_J) ,
126 l_thursday_hours(l_J) ,
127 l_friday_hours(l_J) ,
128 l_saturday_hours(l_J) ,
129 l_sunday_hours(l_J) ,
130 p_change_id ,
131 sysdate ,
132 fnd_global.user_id ,
133 sysdate ,
134 fnd_global.user_id ,
135 fnd_global.login_id );
136
137 EXCEPTION
138 WHEN l_empty_tab_record THEN
139 NULL;
140 WHEN OTHERS THEN
141 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
142 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCH_EXCEPT_HIST_PKG',
143 p_procedure_name => 'insert_rows');
144 raise;
145
146 END insert_rows;
147
148 -- This procedure will insert the record in pa_schedule_except_history
149 -- Input parameters
150 -- Parameters Type Required Description
151 -- P_Schedule_Exception_Id NUMBER YES Id for the exception record in schedule
152 -- P_Calendar_Id NUMBER YES Id for that calendar which is associated to this assignment
153 -- P_Assignment_Id NUMBER YES Assignment id of the exception records
154 -- P_Project_Id NUMBER YES project id of the associated calendar
155 -- P_Schedule_Type_Code VARCHAR2 YES It is schedule type code e.g changed hours/changed duration
156 -- P_Assignment_Status_Code VARCHAR2 YES Status of the assignment e.g OPEN/CONFIRM/PROVISIONAL
157 -- P_Exception_Type_Code VARCHAR2 YES It is exception type code e.g changed hours/changed duration
158 -- P_Start_Date DATE YES stat date of the exceptions
159 -- P_End_Date DATE YES end date of the exception
160 -- P_Resource_Calendar_Percent NUMBER YES it is the resource calendar percentage
161 -- P_Non_Working_Flag VARCHAR2 YES It is non working day flag which means should include or no
162 -- t i.e Y,N.
163 -- P_Change_Hours_Type_Code VARCHAR2 YES It is change hours type code which is used when changeing t
164 -- he hours e.g. HOURS or PERCENTAGE
165 -- P_Monday_Hours NUMBER YES No. of hours of this day
166 -- P_Tuesday_Hours NUMBER YES No. of hours of this day
167 -- P_Wednesday_Hours NUMBER YES No. of hours of this day
168 -- P_Thursday_Hours NUMBER YES No. of hours of this day
169 -- P_Friday_Hours NUMBER YES No. of hours of this day
170 -- P_Saturday_Hours NUMBER YES No. of hours of this day
171 -- P_Sunday_Hours NUMBER YES No. of hours of this day
172 --
173
174 PROCEDURE insert_rows
175 (p_schedule_exception_id IN Number ,
176 p_calendar_id IN Number DEFAULT NULL ,
177 p_assignment_id IN Number DEFAULT NULL ,
178 p_project_id IN Number DEFAULT NULL ,
179 p_schedule_type_code IN varchar2 ,
180 p_assignment_status_code IN varchar2 DEFAULT NULL ,
181 p_exception_type_code IN varchar2 ,
182 p_start_date IN date ,
183 p_end_date IN date ,
184 p_resource_calendar_percent IN Number ,
185 p_non_working_day_flag IN varchar2 ,
186 p_change_hours_type_code IN varchar2 ,
187 p_monday_hours IN Number DEFAULT NULL ,
188 p_tuesday_hours IN Number DEFAULT NULL ,
189 p_wednesday_hours IN Number DEFAULT NULL ,
190 p_thursday_hours IN Number DEFAULT NULL ,
191 p_friday_hours IN Number DEFAULT NULL ,
192 p_saturday_hours IN Number DEFAULT NULL ,
193 p_sunday_hours IN Number DEFAULT NULL ,
194 p_change_id IN PA_SCHEDULE_EXCEPT_HISTORY.change_id%type ,
195 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
196 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
197 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
198 IS
199
200 BEGIN
201
202 x_return_status := FND_API.G_RET_STS_SUCCESS;
203
204 INSERT INTO PA_SCHEDULE_EXCEPT_HISTORY
205 ( schedule_exception_id ,
206 calendar_id ,
207 assignment_id ,
208 project_id ,
209 schedule_type_code ,
210 status_code ,
211 exception_type_code ,
212 start_date ,
213 end_date ,
214 resource_calendar_percent ,
215 non_working_day_flag ,
216 change_hours_type_code ,
217 monday_hours ,
218 tuesday_hours ,
219 wednesday_hours ,
220 thursday_hours ,
221 friday_hours ,
222 saturday_hours ,
223 sunday_hours ,
224 change_id ,
225 creation_date ,
226 created_by ,
227 last_update_date ,
228 last_update_by ,
229 last_update_login )
230 VALUES(p_schedule_exception_id ,
231 p_calendar_id ,
232 p_assignment_id ,
233 p_project_id ,
234 p_schedule_type_code ,
235 p_assignment_status_code ,
236 p_exception_type_code ,
237 p_start_date ,
238 p_end_date ,
239 p_resource_calendar_percent ,
240 p_non_working_day_flag ,
241 p_change_hours_type_code ,
242 p_monday_hours ,
243 p_tuesday_hours ,
244 p_wednesday_hours ,
245 p_thursday_hours ,
246 p_friday_hours ,
247 p_saturday_hours ,
248 p_sunday_hours ,
249 p_change_id ,
250 sysdate ,
251 fnd_global.user_id ,
252 sysdate ,
253 fnd_global.user_id ,
254 fnd_global.login_id);
255
256 EXCEPTION
257 WHEN OTHERS THEN
258 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
259 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCH_EXCEPT_HIST_PKG',
260 p_procedure_name => 'insert_rows');
261 raise;
262
263 END insert_rows;
264
265
266 END PA_SCH_EXCEPT_HIST_PKG;