1 PACKAGE AHL_AVF_OPER_VSTS_PVT AUTHID CURRENT_USER AS
2 /* $Header: AHLVOPVS.pls 120.1.12020000.2 2012/12/07 01:22:00 sareepar noship $ */
3
4 -- object names mapped to the Workflow process, as defined in the lookup AHL_APPR_OBJECT_TYPE
5 G_WF_CANC_OBJ CONSTANT VARCHAR2(30) := 'V_DELE'; -- Visit Deletion
6 G_WF_VSCE_OBJ CONSTANT VARCHAR2(30) := 'V_USCE'; -- Visit UE's Service Catergory Exception
7 G_WF_DISC_OBJ CONSTANT VARCHAR2(30) := 'V_DISC'; -- Visit Disconnect
8 G_WF_VDUR_OBJ CONSTANT VARCHAR2(30) := 'V_DURA'; -- Visit Downtime Duration
9
10 -- AOL message codes for the notification subjects
11 G_VISIT_CANC_SBJ CONSTANT VARCHAR2(30) := 'AHL_VISIT_DELETE_NTF_SUB'; -- Visit Deletion Review Notification
12 G_VISIT_USCE_SBJ CONSTANT VARCHAR2(30) := 'AHL_VISIT_USCE_NTF_SUB'; -- Visit Service Category Exception Review Notificaiton
13 G_VISIT_DISC_SBJ CONSTANT VARCHAR2(30) := 'AHL_VISIT_DISCON_NTF_SUB'; -- Visit Disconnect Review Notification
14 G_VISIT_DURA_SBJ CONSTANT VARCHAR2(30) := 'AHL_VISIT_DT_DUR_NTF_SUB'; -- Visit Downtime Duration Review Notification
15
16 -- AOL function names for the OA regions in the notification body
17 G_VISIT_CANCEL_FN CONSTANT VARCHAR2(30) := 'AHL_VISIT_DELETE_NTFCN'; -- Visit Deletion Notification
18 G_VISIT_USCE_FN CONSTANT VARCHAR2(30) := 'AHL_VISIT_USCE_NTFCN'; -- Visit Service Category Exception Notificaiton
19 G_VISIT_DISC_FN CONSTANT VARCHAR2(30) := 'AHL_VISIT_DISCON_NTFCN'; -- Visit Disconnect Notification
20 G_VISIT_DURA_FN CONSTANT VARCHAR2(30) := 'AHL_VISIT_DT_DUR_NTFCN'; -- Visit Downtime Duration Notification
21
22 -- names of the parameters, if any, required by the OA regions in the notification body
23 G_VISIT_ID_FN_PARAM1 CONSTANT VARCHAR2(30) := 'VisitIds'; -- Visit ID
24 G_UE_IDS_FN_PARAM2 CONSTANT VARCHAR2(30) := 'UeIds'; -- Unit Effectivity ID
25 G_UNIT_SCH_ID_FN_PARAM2 CONSTANT VARCHAR2(30) := 'UnitSchId'; -- Unit Schedule ID
26 G_PAGE_FUNC_PARAM3 CONSTANT VARCHAR2(30) := 'PageFunc'; -- Page Function
27
28 --------------------------------------------------------------------
29 -- START: Defining local functions and procedures --
30 --------------------------------------------------------------------
31
32
33 ----------------------------------------------------------------------------------------------------------------
34 -- Procedure name : Create_Oper_Visit
35 -- Type : Private
36 -- Function : Procedure to create visit based on operational params
37 -- Parameters :
38 --
39 -- Standard IN Parameters :
40 -- p_api_version IN NUMBER Required
41 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
42 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
43 --
44 -- Standard OUT Parameters :
45 -- x_return_status OUT VARCHAR2 Required
46 -- x_msg_count OUT NUMBER Required
47 -- x_msg_data OUT VARCHAR2 Required
48 --------------------------------------------------------------------------------------------------------------------
49 PROCEDURE Create_Oper_Visit (
50 p_api_version IN NUMBER := 1.0,
51 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
52 p_commit IN VARCHAR2 := FND_API.G_FALSE,
53 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
54 x_return_status OUT NOCOPY VARCHAR2,
55 x_msg_count OUT NOCOPY NUMBER,
56 x_msg_data OUT NOCOPY VARCHAR2);
57
58 ----------------------------------------------------------------------------------------------------------------
59 -- Procedure name : Update_Oper_Visit
60 -- Type : Private
61 -- Function : Procedure to Update visit based on operational params
62 -- Parameters :
63 --
64 -- Standard IN Parameters :
65 -- p_api_version IN NUMBER Required
66 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
67 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
68 --
69 -- Standard OUT Parameters :
70 -- x_return_status OUT VARCHAR2 Required
71 -- x_msg_count OUT NUMBER Required
72 -- x_msg_data OUT VARCHAR2 Required
73 --------------------------------------------------------------------------------------------------------------------
74 PROCEDURE Update_Oper_Visit (
75 p_api_version IN NUMBER := 1.0,
76 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
77 p_commit IN VARCHAR2 := FND_API.G_FALSE,
78 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
79 x_return_status OUT NOCOPY VARCHAR2,
80 x_msg_count OUT NOCOPY NUMBER,
81 x_msg_data OUT NOCOPY VARCHAR2);
82
83
84 ---------------------------------------------------------------------------------------
85 -- PROCEDURE
86 -- Get_Visit_Type
87 -- Type : Public
88 -- PURPOSE
89 -- To find out whether visit associated to the organization is arrival or departure or downtime
90 ----------------------------------------------------------------------------------------
91
92 PROCEDURE Get_Visit_Type(
93 p_fs_id IN NUMBER,
94 p_fs_type IN VARCHAR2,
95 x_vst_typ OUT NOCOPY VARCHAR2,
96 x_visit_id OUT NOCOPY NUMBER,
97 x_visit_id2 OUT NOCOPY NUMBER
98 );
99 --------------------------------------------------------------------
100 -- PROCEDURE
101 -- Process_Operational_visit
102 --
103 -- PURPOSE
104 -- Made as an executable for the P2P CP
105 -- Process_Operational_visit Parameters :
106 -- errbuf OUT VARCHAR2 Required
107 -- Defines in pl/sql to store procedure to get error messages into log file
108 -- retcode OUT NUMBER Required
109 -- To get the status of the concurrent program
110
111 --------------------------------------------------------------------
112 PROCEDURE Process_Operational_visits(
113 errbuf OUT NOCOPY VARCHAR2,
114 retcode OUT NOCOPY NUMBER,
115 p_api_version IN NUMBER,
116 p_oper_flag IN VARCHAR2
117 );
118
119 ------------------------------------------------------------------------------------
120 -- Start of Comments
121 -- Procedure name : Launch_Visit_Can_Notification
122 -- Type : Public
123 -- Function : Launches a Workflow notification when a Visit is deleted.
124 -- Pre-reqs :
125 -- Parameters :
126 --
127 -- Launch_Visit_Can_Notification Parameters:
128 -- p_visit_id IN Visit Id Required
129 -- p_ue_ids IN Unit Effectivity Ids concatenated by using ',' Required
130 -- p_commit IN Commit flag. Workflow will be launched only Required
131 -- after a commit.
132 -- x_item_key OUT Item key of the launched notification Required
133 -- x_return_status OUT Return status. Item key to be used only if Required
134 -- this status is FND_API.G_RET_STS_SUCCESS.
135 --
136 -- End of Comments
137
138 PROCEDURE Launch_Visit_Can_Notification (
139 p_visit_id IN NUMBER,
140 p_ue_ids IN VARCHAR2,
141 p_commit IN VARCHAR2 := FND_API.G_FALSE,
142 x_item_key OUT NOCOPY VARCHAR2,
143 x_return_status OUT NOCOPY VARCHAR2
144 );
145
146 ------------------------------------------------------------------------------------
147 -- Start of Comments
148 -- Procedure name : Launch_Visit_SCE_Notification
149 -- Type : Public
150 -- Function : Launches a Workflow notification when a Visit UE's Service Category is greater
151 -- than the visit's department service category
152 -- Pre-reqs :
153 -- Parameters :
154 --
155 -- Launch_Visit_SCE_Notification Parameters:
156 -- p_visit_id IN Visit Id Required
157 -- p_ue_ids IN Unit Effectivity Ids concatenated by using ',' Required
158 -- p_commit IN Commit flag. Workflow will be launched only Required
159 -- after a commit.
160 -- x_item_key OUT Item key of the launched notification Required
161 -- x_return_status OUT Return status. Item key to be used only if Required
162 -- this status is FND_API.G_RET_STS_SUCCESS.
163 --
164 -- End of Comments
165
166 PROCEDURE Launch_Visit_SCE_Notification (
167 p_visit_id IN NUMBER,
168 p_ue_ids IN VARCHAR2,
169 p_commit IN VARCHAR2 := FND_API.G_FALSE,
170 x_item_key OUT NOCOPY VARCHAR2,
171 x_return_status OUT NOCOPY VARCHAR2
172 );
173
174 ------------------------------------------------------------------------------------
175 -- Start of Comments
176 -- Procedure name : Launch_Visit_Disc_Notification
177 -- Type : Public
178 -- Function : Launches a Workflow notification when a visit is disconnected from the flight
179 -- Pre-reqs :
180 -- Parameters :
181 --
182 -- Launch_Visit_Disc_Notification Parameters:
183 -- p_unit_schedule_id IN Unit Schedule Id Required
184 -- p_visit_ids IN Visit Ids affected by the flight Required
185 -- p_commit IN Commit flag. Workflow will be launched only Required
186 -- after a commit.
187 -- x_item_key OUT Item key of the launched notification Required
188 -- x_return_status OUT Return status. Item key to be used only if Required
189 -- this status is FND_API.G_RET_STS_SUCCESS.
190 --
191 -- End of Comments
192
193 PROCEDURE Launch_Visit_Disc_Notification (
194 p_unit_schedule_id IN NUMBER,
195 p_visit_ids IN VARCHAR2,
196 p_commit IN VARCHAR2 := FND_API.G_FALSE,
197 x_item_key OUT NOCOPY VARCHAR2,
198 x_return_status OUT NOCOPY VARCHAR2
199 );
200
201 ------------------------------------------------------------------------------------
202 -- Start of Comments
203 -- Procedure name : Launch_Visit_Dur_Notification
204 -- Type : Public
205 -- Function : Launches a Workflow notification when a visit is disconnected from the flight
206 -- Pre-reqs :
207 -- Parameters :
208 --
209 -- Launch_Visit_Dur_Notification Parameters:
210 -- p_unit_schedule_id IN Unit Schedule Id Required
211 -- p_visit_ids IN Visit Ids affected by the flight Required
212 -- p_commit IN Commit flag. Workflow will be launched only Required
213 -- after a commit.
214 -- x_item_key OUT Item key of the launched notification Required
215 -- x_return_status OUT Return status. Item key to be used only if Required
216 -- this status is FND_API.G_RET_STS_SUCCESS.
217 --
218 -- End of Comments
219
220 PROCEDURE Launch_Visit_Dur_Notification (
221 p_unit_schedule_id IN NUMBER,
222 p_visit_ids IN VARCHAR2,
223 p_commit IN VARCHAR2 := FND_API.G_FALSE,
224 x_item_key OUT NOCOPY VARCHAR2,
225 x_return_status OUT NOCOPY VARCHAR2
226 );
227 END AHL_AVF_OPER_VSTS_PVT;