1 PACKAGE AHL_UA_FLIGHT_SCHEDULES_PUB AUTHID CURRENT_USER AS
2 /* $Header: AHLPUFSS.pls 120.2.12020000.3 2013/03/28 18:10:47 shnatu ship $ */
3 /*#
4 * Package containing APIs to process flight schedules and following transit visits. It allows users to create, update
5 * and delete flight schedules for active units. It also allows users to create a transit visit following a flight schedule.
6 * @rep:scope public
7 * @rep:product AHL
8 * @rep:displayname Unit Flight Schedules
9 * @rep:lifecycle active
10 * @rep:category BUSINESS_ENTITY AHL_UNIT_SCHEDULES
11 */
12
13 -- Flag to indicate that search criteria dates must be applied only to the flight arrival dates
14 G_APPLY_TO_ARRIVAL CONSTANT VARCHAR2(1) := 'A';
15
16 -- Flag to indicate that search criteria dates must be applied only to the flight departure dates
17 G_APPLY_TO_DEPARTURE CONSTANT VARCHAR2(1) := 'D';
18
19 TYPE FLIGHT_SEARCH_REC_TYPE IS RECORD
20 (
21 UNIT_SCHEDULE_ID NUMBER,
22
23 -- Bug #16566070: shnatu: March 2013
24 -- Changing the size of UNIT_NAME from 30 to 80
25 /*
26 UNIT_NAME VARCHAR2(30),
27 */
28 UNIT_NAME VARCHAR2(80),
29
30 FLIGHT_NUMBER VARCHAR2(80),
31 ITEM_NUMBER VARCHAR2(40),
32 SERIAL_NUMBER VARCHAR2(30),
33 ARRIVAL_ORG_CODE VARCHAR2(3),
34 ARRIVAL_DEPT_CODE VARCHAR2(10),
35 DEPARTURE_ORG_CODE VARCHAR2(3),
36 DEPARTURE_DEPT_CODE VARCHAR2(10),
37 START_DATE DATE,
38 END_DATE DATE,
39 DATE_APPLY_TO_FLAG VARCHAR2(1)
40 );
41
42 TYPE FLIGHT_VISIT_SCH_REC_TYPE IS RECORD
43 (
44 UNIT_SCHEDULE_ID NUMBER,
45 FLIGHT_NUMBER VARCHAR2(30),
46 SEGMENT VARCHAR2(30),
47 EST_DEPARTURE_TIME DATE,
48 ACTUAL_DEPARTURE_TIME DATE,
49 DEPARTURE_DEPT_ID NUMBER,
50 DEPARTURE_DEPT_CODE VARCHAR2(10),
51 DEPARTURE_ORG_ID NUMBER,
52 DEPARTURE_ORG_CODE VARCHAR2(3),
53 EST_ARRIVAL_TIME DATE,
54 ACTUAL_ARRIVAL_TIME DATE,
55 ARRIVAL_DEPT_ID NUMBER,
56 ARRIVAL_DEPT_CODE VARCHAR2(10),
57 ARRIVAL_ORG_ID NUMBER,
58 ARRIVAL_ORG_CODE VARCHAR2(3),
59 PRECEDING_US_ID NUMBER,
60 UNIT_CONFIG_HEADER_ID NUMBER,
61 UNIT_CONFIG_NAME VARCHAR2(80),
62 CSI_INSTANCE_ID NUMBER,
63 INSTANCE_NUMBER VARCHAR2(30),
64 ITEM_NUMBER VARCHAR2(40),
65 SERIAL_NUMBER VARCHAR2(30),
66 VISIT_RESCHEDULE_MODE VARCHAR2(30),
67 VISIT_RESCHEDULE_MEANING VARCHAR2(80),
68 OBJECT_VERSION_NUMBER NUMBER,
69 IS_UPDATE_ALLOWED VARCHAR2(1),
70 IS_DELETE_ALLOWED VARCHAR2(1),
71 -- MANESING::Auto Visit Forecasting, 17-Oct-2011
72 -- added variables for flight category and spaces
73 FLIGHT_CATEGORY_CODE VARCHAR2(30),
74 FLIGHT_CATEGORY_MEANING VARCHAR2(80),
75 DEPARTURE_SPACE_ID NUMBER,
76 DEPARTURE_SPACE_NAME VARCHAR2(80),
77 ARRIVAL_SPACE_ID NUMBER,
78 ARRIVAL_SPACE_NAME VARCHAR2(80),
79 VISIT_ID NUMBER,
80 VISIT_TYPE_CODE VARCHAR2(30),
81 VISIT_TYPE_MEANING VARCHAR2(80),
82 VISIT_CREATE_TYPE VARCHAR2(30),
83 VISIT_CREATE_MEANING VARCHAR2(80),
84 ATTRIBUTE_CATEGORY VARCHAR2(30),
85 ATTRIBUTE1 VARCHAR2(150),
86 ATTRIBUTE2 VARCHAR2(150),
87 ATTRIBUTE3 VARCHAR2(150),
88 ATTRIBUTE4 VARCHAR2(150),
89 ATTRIBUTE5 VARCHAR2(150),
90 ATTRIBUTE6 VARCHAR2(150),
91 ATTRIBUTE7 VARCHAR2(150),
92 ATTRIBUTE8 VARCHAR2(150),
93 ATTRIBUTE9 VARCHAR2(150),
94 ATTRIBUTE10 VARCHAR2(150),
95 ATTRIBUTE11 VARCHAR2(150),
96 ATTRIBUTE12 VARCHAR2(150),
97 ATTRIBUTE13 VARCHAR2(150),
98 ATTRIBUTE14 VARCHAR2(150),
99 ATTRIBUTE15 VARCHAR2(150),
100 DML_OPERATION VARCHAR2(1)
101 );
102
103 -- Table of flight schedule recs
104 TYPE FLIGHT_VISIT_SCH_TBL_TYPE IS TABLE OF FLIGHT_VISIT_SCH_REC_TYPE INDEX BY BINARY_INTEGER;
105
106 -----------------------
107 -- Define procedures --
108 -----------------------
109 -- Start of Comments --
110 --
111 -- Procedure name : Get_Flight_Schedule_Details
112 -- Type : Private
113 -- Function : API to retrieve flight schedule details for the given search criteria.
114 -- Pre-reqs :
115 --
116 -- Standard IN Parameters :
117 -- p_api_version IN NUMBER Required
118 -- p_init_msg_list IN VARCHAR2 Required, default FND_API.G_FALSE
119 -- p_commit IN VARCHAR2 Required, default FND_API.G_FALSE
120 -- p_validation_level IN NUMBER Required, default FND_API.G_VALID_LEVEL_FULL
121 -- p_default IN VARCHAR2 Required, default FND_API.G_FALSE
122 -- p_module_type IN VARCHAR2 Required, default NULL
123
124 -- Standard OUT Parameters :
125 -- x_return_status OUT VARCHAR2 Required
126 -- x_msg_count OUT NUMBER Required
127 -- x_msg_data OUT VARCHAR2 Required
128 --
129 -- Procedure Parameters :
130 -- p_flight_search_rec IN FLIGHT_SEARCH_REC Required
131 -- x_flight_schedule_tbl OUT AHL_UA_FLIGHT_SCHEDULES_PVT.FLIGHT_SCHEDULES_TBL_TYPE
132 --
133 -- Version :
134 -- Initial Version 1.0
135 --
136 -- End of Comments --
137 /*#
138 * Use this procedure to retrieve flight schedule details for the given search criteria.
139 * @param p_api_version API Version Number.
140 * @param p_init_msg_list Initialize the message stack, Standard API parameter, default value FND_API.G_FALSE
141 * @param p_commit Parameter to decide whether to commit the transaction or not, Standard API parameter, default value FND_API.G_FALSE
142 * @param p_validation_level Validation level, Standard API parameter, default value FND_API.G_VALID_LEVEL_FULL
143 * @param p_default Parameter to decide whether to default attributes or not, valid values are FND_API.G_TRUE or FND_API.G_FALSE, default value NULL
144 * @param p_module_type For Internal use only, should always be NULL, default value NULL
145 * @param x_return_status API Return status. Standard API parameter
146 * @param x_msg_count API Return message count, if any. Standard API parameter
147 * @param x_msg_data API Return message data, if any. Standard API parameter
148 * @param p_flight_search_rec Search criteria record of type FLIGHT_SEARCH_REC_TYPE
149 * @param x_flight_schedules_tbl Flight schedules details table of type AHL_UA_FLIGHT_SCHEDULES_PVT.FLIGHT_SCHEDULES_TBL_TYPE
150 * @rep:scope private
151 * @rep:lifecycle active
152 * @rep:displayname Get Flight Schedule Details
153 */
154 PROCEDURE Get_Flight_Schedule_Details
155 (
156 -- standard IN params
157 p_api_version IN NUMBER,
158 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
159 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
160 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
161 p_default IN VARCHAR2 :=FND_API.G_FALSE,
162 p_module_type IN VARCHAR2 :=NULL,
163 -- standard OUT params
164 x_return_status OUT NOCOPY VARCHAR2,
165 x_msg_count OUT NOCOPY NUMBER,
166 x_msg_data OUT NOCOPY VARCHAR2,
167 -- procedure params
168 p_flight_search_rec IN FLIGHT_SEARCH_REC_TYPE,
169 x_flight_schedules_tbl OUT NOCOPY AHL_UA_FLIGHT_SCHEDULES_PVT.FLIGHT_SCHEDULES_TBL_TYPE
170 );
171
172 -- Start of Comments --
173 --
174 -- Procedure name : Process_Flight_Schedules
175 -- Type : Private
176 -- Function : API to retrieve flight schedule details for the given search criteria.
177 -- Pre-reqs :
178 --
179 -- Standard IN Parameters :
180 -- p_api_version IN NUMBER Required
181 -- p_init_msg_list IN VARCHAR2 Required, default FND_API.G_FALSE
182 -- p_commit IN VARCHAR2 Required, default FND_API.G_FALSE
183 -- p_validation_level IN NUMBER Required, default FND_API.G_VALID_LEVEL_FULL
184 -- p_default IN VARCHAR2 Required, default FND_API.G_FALSE
185 -- p_module_type IN VARCHAR2 Required, default NULL
186
187 -- Standard OUT Parameters :
188 -- x_return_status OUT VARCHAR2 Required
189 -- x_msg_count OUT NUMBER Required
190 -- x_msg_data OUT VARCHAR2 Required
191 --
192 -- Procedure Parameters :
193 -- p_x_flight_schedules_tbl IN OUT AHL_UA_FLIGHT_SCHEDULES_PVT.FLIGHT_SCHEDULES_TBL_TYPE Required
194 --
195 -- Version :
196 -- Initial Version 1.0
197 --
198 -- End of Comments --
199 /*#
200 * Use this procedure to create, update and delete flight schedules.
201 * @param p_api_version API Version Number.
202 * @param p_init_msg_list Initialize the message stack, Standard API parameter, default value FND_API.G_FALSE
203 * @param p_commit Parameter to decide whether to commit the transaction or not, Standard API parameter, default value FND_API.G_FALSE
204 * @param p_validation_level Validation level, Standard API parameter, default value FND_API.G_VALID_LEVEL_FULL
205 * @param p_default Parameter to decide whether to default attributes or not, valid values are FND_API.G_TRUE or FND_API.G_FALSE, default value NULL
206 * @param p_module_type For Internal use only, should always be NULL, default value NULL
207 * @param x_return_status API Return status. Standard API parameter
208 * @param x_msg_count API Return message count, if any. Standard API parameter
209 * @param x_msg_data API Return message data, if any. Standard API parameter
210 * @param p_x_flight_schedules_tbl Flight schedules table of type AHL_UA_FLIGHT_SCHEDULES_PVT.FLIGHT_SCHEDULES_TBL_TYPE
211 * @rep:scope public
212 * @rep:lifecycle active
213 * @rep:displayname Process Flight Schedules
214 */
215 PROCEDURE Process_Flight_Schedules
216 (
217 -- standard IN params
218 p_api_version IN NUMBER,
219 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
220 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
221 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
222 p_default IN VARCHAR2 :=FND_API.G_FALSE,
223 p_module_type IN VARCHAR2 :=NULL,
224 -- standard OUT params
225 x_return_status OUT NOCOPY VARCHAR2,
226 x_msg_count OUT NOCOPY NUMBER,
227 x_msg_data OUT NOCOPY VARCHAR2,
228 -- procedure params
229 p_x_flight_schedules_tbl IN OUT NOCOPY AHL_UA_FLIGHT_SCHEDULES_PVT.FLIGHT_SCHEDULES_TBL_TYPE
230 );
231
232 -- Start of Comments --
233 --
234 -- Procedure name : Process_Flight_Schedules
235 -- Type : Private
236 -- Function : API to retrieve flight schedule details for the given search criteria.
237 -- Pre-reqs :
238 --
239 -- Standard IN Parameters :
240 -- p_api_version IN NUMBER Required
241 -- p_init_msg_list IN VARCHAR2 Required, default FND_API.G_FALSE
242 -- p_commit IN VARCHAR2 Required, default FND_API.G_FALSE
243 -- p_validation_level IN NUMBER Required, default FND_API.G_VALID_LEVEL_FULL
244 -- p_default IN VARCHAR2 Required, default FND_API.G_FALSE
245 -- p_module_type IN VARCHAR2 Required, default NULL
246
247 -- Standard OUT Parameters :
248 -- x_return_status OUT VARCHAR2 Required
249 -- x_msg_count OUT NUMBER Required
250 -- x_msg_data OUT VARCHAR2 Required
251 --
252 -- Procedure Parameters :
253 -- p_x_flight_visit_sch_tbl IN OUT FLIGHT_VISIT_SCH_TBL_TYPE Required
254 --
255 -- Version :
256 -- Initial Version 1.0
257 --
258 -- End of Comments --
259 /*#
260 * Use this procedure to create flight schedules with following transit visits, using parameters from the created flight schedules. It can also be used to update and delete flight schedules.
261 * @param p_api_version API Version Number.
262 * @param p_init_msg_list Initialize the message stack, Standard API parameter, default value FND_API.G_FALSE
263 * @param p_commit Parameter to decide whether to commit the transaction or not, Standard API parameter, default value FND_API.G_FALSE
264 * @param p_validation_level Validation level, Standard API parameter, default value FND_API.G_VALID_LEVEL_FULL
265 * @param p_default Parameter to decide whether to default attributes or not, valid values are FND_API.G_TRUE or FND_API.G_FALSE, default value NULL
266 * @param p_module_type For Internal use only, should always be NULL, default value NULL
267 * @param x_return_status API Return status. Standard API parameter
268 * @param x_msg_count API Return message count, if any. Standard API parameter
269 * @param x_msg_data API Return message data, if any. Standard API parameter
270 * @param p_x_flight_visit_sch_tbl Flight and Visit schedules table of type FLIGHT_VISIT_SCH_TBL_TYPE
271 * @rep:scope public
272 * @rep:lifecycle active
273 * @rep:displayname Process Flight and Visit Schedules
274 */
275 PROCEDURE Process_FlightVisit_Sch
276 (
277 -- standard IN params
278 p_api_version IN NUMBER,
279 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
280 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
281 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
282 p_default IN VARCHAR2 :=FND_API.G_FALSE,
283 p_module_type IN VARCHAR2 :=NULL,
284 -- standard OUT params
285 x_return_status OUT NOCOPY VARCHAR2,
286 x_msg_count OUT NOCOPY NUMBER,
287 x_msg_data OUT NOCOPY VARCHAR2,
288 -- procedure params
289 p_x_flight_visit_sch_tbl IN OUT NOCOPY FLIGHT_VISIT_SCH_TBL_TYPE
290 );
291
292 END AHL_UA_FLIGHT_SCHEDULES_PUB;
293