[Home] [Help]
PACKAGE: APPS.AHL_LTP_SPACE_SCHEDULE_PVT
Source
1 PACKAGE AHL_LTP_SPACE_SCHEDULE_PVT AUTHID CURRENT_USER AS
2 /* $Header: AHLVSPSS.pls 115.16 2004/01/06 00:57:34 ssurapan noship $ */
3 --
4 ---------------------------------------------------------------------
5 -- Define Record Types for record structures needed by the APIs --
6 ---------------------------------------------------------------------
7 TYPE Search_Visits_Rec_Type IS RECORD (
8 ORG_NAME VARCHAR2(240),
9 ORG_ID NUMBER,
10 DEPARTMENT_NAME VARCHAR2(80),
11 DEPARTMENT_ID NUMBER,
12 DEPARTMENT_CODE VARCHAR2(30),
13 SPACE_NAME VARCHAR2(30),
14 SPACE_ID NUMBER,
15 SPACE_CATEGORY VARCHAR2(30),
16 SPACE_CATEGORY_MEAN VARCHAR2(80),
17 VISIT_TYPE_CODE VARCHAR2(30),
18 VISIT_TYPE_MEAN VARCHAR2(80),
19 ITEM_ID NUMBER ,
20 ITEM_DESCRIPTION VARCHAR2(40),
21 PLAN_ID NUMBER,
22 PLAN_NAME VARCHAR2(30),
23 DISPLAY_PERIOD_CODE VARCHAR2(30),
24 DISPLAY_PERIOD_MEAN VARCHAR2(80),
25 START_DATE DATE,
26 START_PERIOD DATE,
27 END_PERIOD DATE
28 );
29
30 -- Scheduled vists record
31 TYPE Scheduled_Visits_Rec_Type IS RECORD (
32 ORG_NAME VARCHAR2(240),
33 DEPARTMENT_NAME VARCHAR2(80),
34 DEPARTMENT_ID NUMBER,
35 DEPARTMENT_CODE VARCHAR2(30),
36 SPACE_NAME VARCHAR2(30),
37 SPACE_ID NUMBER,
38 SPACE_CATEGORY VARCHAR2(30),
39 SPACE_CATEGORY_MEAN VARCHAR2(80),
40 VALUE_1 VARCHAR2(10),
41 VALUE_2 VARCHAR2(10),
42 VALUE_3 VARCHAR2(10),
43 VALUE_4 VARCHAR2(10),
44 VALUE_5 VARCHAR2(10),
45 VALUE_6 VARCHAR2(10),
46 VALUE_7 VARCHAR2(10),
47 VALUE_8 VARCHAR2(10),
48 VALUE_9 VARCHAR2(10),
49 VALUE_10 VARCHAR2(10),
50 VALUE_11 VARCHAR2(10),
51 VALUE_12 VARCHAR2(10),
52 VALUE_13 VARCHAR2(10),
53 VALUE_14 VARCHAR2(10)
54 );
55 -- Display Record
56 TYPE Display_Rec_Type IS RECORD (
57 FIELD_1 VARCHAR2(10),
58 START_PERIOD_1 DATE,
59 END_PERIOD_1 DATE,
60 FIELD_2 VARCHAR2(10),
61 START_PERIOD_2 DATE,
62 END_PERIOD_2 DATE,
63 FIELD_3 VARCHAR2(10),
64 START_PERIOD_3 DATE,
65 END_PERIOD_3 DATE,
66 FIELD_4 VARCHAR2(10),
67 START_PERIOD_4 DATE,
68 END_PERIOD_4 DATE,
69 FIELD_5 VARCHAR2(10),
70 START_PERIOD_5 DATE,
71 END_PERIOD_5 DATE,
72 FIELD_6 VARCHAR2(10),
73 START_PERIOD_6 DATE,
74 END_PERIOD_6 DATE,
75 FIELD_7 VARCHAR2(10),
76 START_PERIOD_7 DATE,
77 END_PERIOD_7 DATE,
78 FIELD_8 VARCHAR2(10),
79 START_PERIOD_8 DATE,
80 END_PERIOD_8 DATE,
81 FIELD_9 VARCHAR2(10),
82 START_PERIOD_9 DATE,
83 END_PERIOD_9 DATE,
84 FIELD_10 VARCHAR2(10),
85 START_PERIOD_10 DATE,
86 END_PERIOD_10 DATE,
87 FIELD_11 VARCHAR2(10),
88 START_PERIOD_11 DATE,
89 END_PERIOD_11 DATE,
90 FIELD_12 VARCHAR2(10),
91 START_PERIOD_12 DATE,
92 END_PERIOD_12 DATE,
93 FIELD_13 VARCHAR2(10),
94 START_PERIOD_13 DATE,
95 END_PERIOD_13 DATE,
96 FIELD_14 VARCHAR2(10),
97 START_PERIOD_14 DATE,
98 END_PERIOD_14 DATE,
99 START_PERIOD DATE,
100 END_PERIOD DATE
101 );
102 -- Define visit end date rec
103 TYPE Visits_End_Date_Rec_Type IS RECORD (
104 VISIT_ID NUMBER,
105 VISIT_END_DATE DATE
106 );
107
108 --Define visit detail record type
109 TYPE Visit_Details_Rec_Type IS RECORD (
110 VISIT_NUMBER NUMBER,
111 VISIT_TYPE VARCHAR2(80),
112 VISIT_NAME VARCHAR2(80),
113 VISIT_ID NUMBER,
114 VISIT_STATUS VARCHAR2(30),
115 ITEM_DESCRIPTION VARCHAR2(40),
116 SERIAL_NUMBER VARCHAR2(30),
117 UNIT_NAME VARCHAR2(240),
118 YES_NO_TYPE VARCHAR2(80),
119 PLAN_FLAG VARCHAR2(1),
120 START_DATE DATE,
121 END_DATE DATE,
122 DUE_BY DATE
123 );
124
125 ----------------------------------------------
126 -- Define Table Type for records structures --
127 ----------------------------------------------
128 TYPE Search_Visits_tbl IS TABLE OF Search_Visits_Rec_Type
129 INDEX BY BINARY_INTEGER;
130
131 TYPE Scheduled_Visits_tbl IS TABLE OF Scheduled_Visits_Rec_Type
132 INDEX BY BINARY_INTEGER;
133 TYPE Visits_End_Date_tbl IS TABLE OF Visits_End_Date_Rec_Type
134 INDEX BY BINARY_INTEGER;
135 TYPE Visit_Details_tbl IS TABLE OF Visit_Details_Rec_Type
136 INDEX BY BINARY_INTEGER;
137 -- Function to get visit duration
138 PROCEDURE Get_Visit_Duration
139 (p_visit_id IN NUMBER,
140 x_visit_duration OUT NOCOPY NUMBER,
141 x_return_status OUT NOCOPY VARCHAR2,
142 x_msg_count OUT NOCOPY NUMBER,
143 x_msg_data OUT NOCOPY VARCHAR2 );
144 -- To get visit end date
145 PROCEDURE Get_Visit_End_Date
146 (p_visit_id IN NUMBER,
147 x_visit_end_date OUT NOCOPY DATE,
148 x_return_status OUT NOCOPY VARCHAR2,
149 x_msg_count OUT NOCOPY NUMBER,
150 x_msg_data OUT NOCOPY VARCHAR2 );
151 -- To get visit due by date
152 PROCEDURE Get_Visit_Due_by_Date(
153 p_visit_id IN NUMBER,
154 x_due_by_date OUT NOCOPY DATE,
155 x_return_status OUT NOCOPY VARCHAR2,
156 x_msg_count OUT NOCOPY NUMBER,
157 x_msg_data OUT NOCOPY VARCHAR2 );
158 -- To get derieve visit end date
159 PROCEDURE Derive_Visit_End_Date
160 (p_visits_end_date_tbl IN OUT NOCOPY visits_end_date_tbl,
161 x_return_status OUT NOCOPY VARCHAR2,
162 x_msg_count OUT NOCOPY NUMBER,
163 x_msg_data OUT NOCOPY VARCHAR2 );
164 -- Function to get derieved end date
165
166 FUNCTION Get_Derived_end_date
167 (p_visit_id NUMBER)
168
169 RETURN DATE;
170
171 ------------------------
172 -- Declare Procedures --
173 ------------------------
174 -- Start of Comments --
175 -- Procedure name : Search_Scheduled_Visits
176 -- Type : Private
177 -- Function : This procedure calculates number of visits scheduled at department or space level
178 -- based on start date, and various combinations of search criteria UOM (Days,Weeks, Months).
179 -- Restricted to 14 days, 14 weeks , 14 months due to technical reasons.
180 -- Pre-reqs :
181 -- Parameters :
182 --
183 -- Standard IN Parameters :
184 -- p_api_version IN NUMBER Required
185 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
186 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
187 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
188 -- Based on this flag, the API will set the default attributes.
189 -- p_module_type In VARCHAR2 Default NULL
190 -- This will be null.
191 -- Standard out Parameters :
192 -- x_return_status OUT VARCHAR2 Required
193 -- x_msg_count OUT NUMBER Required
194 -- x_msg_data OUT VARCHAR2 Required
195 --
196 -- Search Scheduled vists Parameters :
197 -- p_search_visits_rec IN Search_visits_rec_type Required
198 -- X_Scheduled_visits_tbl OUT Scheduled_visits_tbl
199 --
200 --
201 PROCEDURE Search_Scheduled_Visits (
202 p_api_version IN NUMBER,
203 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
204 p_commit IN VARCHAR2 := FND_API.g_false,
205 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
206 p_module_type IN VARCHAR2 := 'JSP',
207 p_search_visits_Rec IN search_visits_rec_type,
208 x_scheduled_visit_tbl OUT NOCOPY scheduled_visits_tbl,
209 x_display_rec OUT NOCOPY display_rec_type,
210 x_return_status OUT NOCOPY VARCHAR2,
211 x_msg_count OUT NOCOPY NUMBER,
212 x_msg_data OUT NOCOPY VARCHAR2
213 );
214 -- Procedure name : Get_Visit_Details
215 -- Type : Private
216 -- Function : This procedure shows all the visits scheduled at department or space level
217 -- based on start date, and various combinations of search criteria UOM (Days,Weeks, Months).
218 -- Pre-reqs :
219 -- Parameters :
220 --
221 -- Standard IN Parameters :
222 -- p_api_version IN NUMBER Required
223 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
224 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
225 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
226 -- Based on this flag, the API will set the default attributes.
227 -- p_module_type In VARCHAR2 Default NULL
228 -- This will be null.
229 -- Standard out Parameters :
230 -- x_return_status OUT VARCHAR2 Required
231 -- x_msg_count OUT NUMBER Required
232 -- x_msg_data OUT VARCHAR2 Required
233 --
234 -- Search Scheduled vists Parameters :
235 -- p_search_visits_rec IN Search_visits_rec_type Required
236 -- X_Visit_details_tbl OUT visit_details_tbl
237 --
238 --
239 --
240 PROCEDURE Get_Visit_Details (
241 p_api_version IN NUMBER,
242 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
243 p_commit IN VARCHAR2 := FND_API.g_false,
244 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
245 p_module_type IN VARCHAR2 := 'JSP',
246 p_search_visits_Rec IN search_visits_rec_type,
247 x_visit_details_tbl OUT NOCOPY visit_details_tbl,
248 x_return_status OUT NOCOPY VARCHAR2,
249 x_msg_count OUT NOCOPY NUMBER,
250 x_msg_data OUT NOCOPY VARCHAR2
251 );
252
253 --
254 END AHL_LTP_SPACE_SCHEDULE_PVT;