1 PACKAGE CAC_SR_OBJECT_CAPACITY_PUB AUTHID CURRENT_USER AS
2 /* $Header: cacsrocps.pls 120.1.12010000.5 2009/06/01 08:41:21 anangupt ship $ */
3
4 --Default AVAILABILITY_TYPE
5 DEFAULT_AVAILABILITY_TYPE CONSTANT VARCHAR2(30) := 'REGULAR';
6
7 /*******************************************************************************
8 ** Datatypes used in APIs
9 *******************************************************************************/
10 TYPE OBJECT_CAPACITY_REC_TYPE IS
11 RECORD( OBJECT_CAPACITY_ID NUMBER -- primary key
12
13 , OBJECT_VERSION_NUMBER NUMBER -- The version number of the row
14 -- after any updates. Initially it
15 -- is set to 1 and incremented
16 -- whenever any change in the data
17 -- happens. This should be used to
18 -- check if another user updated
19 -- the same row.
20
21 , OBJECT_TYPE VARCHAR2(30) -- JTF OBJECTS type of the Object.
22 -- It should considered same as
23 -- Resource Type
24
25 , OBJECT_ID NUMBER -- JTF OBJECTS select ID of the
26 -- Object Instance. It should be
27 -- considered same as Resource Id
28
29 , START_DATE_TIME DATE -- Start date and time of the
30 -- Object Capacity record
31
32 , END_DATE_TIME DATE -- End date and time of the
33 -- Object Capacity record
34
35 , AVAILABLE_HOURS NUMBER -- The available hours in this
36 -- record. Initially, this will
37 -- be (END_DATE_TIME -
38 -- START_DATE_TIME). As the tasks
39 -- are scheduled, it will decrease.
40
41 , AVAILABLE_HOURS_BEFORE NUMBER -- The hours that can be made
42 -- available before the first task
43 -- in this record. It will be NULL
44 -- if there are no tasks
45 -- associated with this record.
46 -- The calculation of this field
47 -- will be driven by the business
48 -- rules of individual product.
49
50 , AVAILABLE_HOURS_AFTER NUMBER -- The hours that can be made
51 -- available after the last task
52 -- in this record. It will be NULL
53 -- if there are no tasks
54 -- associated with this record.
55 -- The calculation of this field
56 -- will be driven by the business
57 -- rules of individual product.
58
59 , SCHEDULE_DETAIL_ID NUMBER -- The schedule detail row that
60 -- was used to create this record
61 -- It is a foreign key to the
62 -- table CAC_SR_SCHDL_DETAILS.
63 -- If this record was created
64 -- using old JTF Shifts model
65 -- then this field will be set
66 -- to the value
67 -- (0 - SHIFT_CONSTRUCT_ID).
68 -- It will be set to NULL if the
69 -- record as created manually
70 -- without any corresponding shift
71
72 , STATUS NUMBER -- It will indicate if the record
73 -- can be used for scheduling or
74 -- not. Initially it will be set
75 -- 1 (Available) and different
76 -- application can set different
77 -- values based on their need.
78
79 , AVAILABILITY_TYPE VARCHAR2(240) -- This field indicates if the record
80 -- has regular shift or stand by
81 -- shift.Possible values are
82 -- "REGULAR" or "STANDBY"
83 -- Default value for AVAILABILITY_TYPE
84 -- is "REGULAR"
85
86 , SOURCE_TYPE VARCHAR2(30) -- This filed indicates from where
87 -- this trip is created.Possible values
88 -- are "GEN" for shifts created using
89 -- concurrent program and "MAN" for
90 -- shifts created manually
91 );
92
93 TYPE OBJECT_CAPACITY_TBL_TYPE IS
94 TABLE of OBJECT_CAPACITY_REC_TYPE INDEX BY BINARY_INTEGER;
95
96 TYPE OBJECT_TASKS_REC_TYPE IS
97 RECORD( OBJECT_CAPACITY_TBL_IDX NUMBER -- The pl/sql table index of the
98 -- object capacity record where
99 -- this tasks lies
100 , TASK_ASSIGNMENT_ID NUMBER -- The primary key of the task
101 -- assignment table record
102 , TASK_ASSIGNMENT_OVN NUMBER -- The object version no.of the
103 -- task assignment table record
104 , ASSIGNMENT_STATUS_ID NUMBER -- The status id in the task
105 -- assignment table record
106 , FREE_BUSY_TYPE VARCHAR2(30) -- If this task is making this
107 -- resource Free or Busy
108 , TASK_ID NUMBER -- The primary key of the task
109 -- table record
110 , TASK_OVN NUMBER -- The object version no. of the
111 -- task table record
112 , TASK_TYPE_ID NUMBER -- The task type
113 , TASK_STATUS_ID NUMBER -- The task status
114 , SCHEDULED_START_DATE DATE -- The schedule start date of
115 -- the task table record
116 , SCHEDULED_END_DATE DATE -- The schedule end date of
117 -- the task table record
118 , PLANNED_START_DATE DATE -- The planned start date of
119 -- the task table record
120 , PLANNED_END_DATE DATE -- The planned end date of
121 -- the task table record
122 , CUSTOMER_ID NUMBER -- The party id of the customer
123 , ADDRESS_ID NUMBER -- Id of the party address
124 );
125
126 TYPE OBJECT_TASKS_TBL_TYPE IS
127 TABLE of OBJECT_TASKS_REC_TYPE INDEX BY BINARY_INTEGER;
128
129 /*******************************************************************************
130 ** Public APIs
131 *******************************************************************************/
132
133 PROCEDURE generate_object_capacity
134 /*******************************************************************************
135 ** generate_object_capacity
136 **
137 ** This API calls JTF_CALENDAR_PUB_24HR.Get_Resource_Shifts API and builds the
138 ** pl/sql table with object capacity records. It populates object_capacity_id
139 ** for each record with the sequence cac_sr_object_capacity_s value if the
140 ** parameter p_PopulateID is 'T'.
141 ** It will return a list of tasks also if the p_FetchTasks parameter is set to
142 ** true. This is needed only if you want to fetch all the tasks at one shot as
143 ** it will be more performant than fetching tasks for each object capacity
144 ** record one by one.
145 **
146 *******************************************************************************/
147 ( p_api_version IN NUMBER -- API version you coded against
148 , p_init_msg_list IN VARCHAR2 DEFAULT 'F' -- Create a new error stack?
149 , p_Object_Type IN VARCHAR2 -- JTF OBJECTS type of the Object being queried
150 , p_Object_ID IN NUMBER -- JTF OBJECTS select ID of the Object Instance being queried
151 , p_Start_Date IN DATE -- start date and time of period of interest
152 , p_End_Date IN DATE -- end date and time of period of interest
153 , p_Populate_ID IN VARCHAR2 DEFAULT 'F' -- Populate the object_capacity_id of the record?
154 -- Should be set to 'T' if the ids are needed for foreign key reference
155 -- 'F' means the ids will be genrated while inserting these records in the table
156 , p_Fetch_Tasks IN VARCHAR2 DEFAULT 'F' -- Fetch tasks for the time period too?
157 , x_Object_Capacity OUT NOCOPY OBJECT_CAPACITY_TBL_TYPE
158 -- return table of object capacity records
159 , x_Object_Tasks OUT NOCOPY OBJECT_TASKS_TBL_TYPE
160 -- return table of object task records
161 , x_return_status OUT NOCOPY VARCHAR2 -- 'S': API completed without errors
162 -- 'E': API completed with recoverable errors; explanation on errorstack
163 -- 'U': API completed with UN recoverable errors: error message on error stack
164 , x_msg_count OUT NOCOPY NUMBER -- Number of messages on the errorstack
165 , x_msg_data OUT NOCOPY VARCHAR2 -- contains message if x_msg_count = 1
166 );
167
168 PROCEDURE insert_object_capacity
169 /*******************************************************************************
170 ** insert_object_capacity
171 **
172 ** This API calls table handler to insert data into cac_sr_object_capacity
173 ** using pl/sql table passed. It populates object_capacity_id for each record
174 ** if with the sequence cac_sr_object_capacity_s value if it is NULL in the
175 ** record.
176 ** It updates the task assignment with the corresponding object_capacity_id
177 ** if the p_Update_Tasks parameter is set to 'T'.
178 **
179 *******************************************************************************/
180 ( p_api_version IN NUMBER -- API version you coded against
181 , p_init_msg_list IN VARCHAR2 DEFAULT 'F' -- Create a new error stack?
182 , p_Object_Capacity IN OUT NOCOPY OBJECT_CAPACITY_TBL_TYPE
183 -- table of object capacity records which should be inserte
184 , p_Update_Tasks IN VARCHAR2 DEFAULT 'F' -- Update task assignments too?
185 , p_Object_Tasks IN OBJECT_TASKS_TBL_TYPE
186 -- table of object task records to be updated
187 , x_return_status OUT NOCOPY VARCHAR2 -- 'S': API completed without errors
188 -- 'E': API completed with recoverable errors; explanation on errorstack
189 -- 'U': API completed with UN recoverable errors: error message on error stack
190 , x_msg_count OUT NOCOPY NUMBER -- Number of messages on the errorstack
191 , x_msg_data OUT NOCOPY VARCHAR2 -- contains message if x_msg_count = 1
192 );
193
194 PROCEDURE update_object_capacity
195 /*******************************************************************************
196 ** update_object_capacity New Version
197 **
198 ** This API calls table handler to update data into cac_sr_object_capacity.
199 ** Only the available hours fields and status can be updated.
200 **
201 *******************************************************************************/
202 ( p_api_version IN NUMBER -- API version you coded against
203 , p_init_msg_list IN VARCHAR2 DEFAULT 'F' -- Create a new error stack?
204 , p_object_capacity_id IN NUMBER -- Primary Key ID of the row to be updated
205 , p_object_version_number IN NUMBER -- Object Version of the row to be updated
206 -- If this doesn't match the database value then that means someone else has updated the same row
207 , p_available_hours IN NUMBER DEFAULT NULL -- The new value of available hours
208 -- If it is NULL then no change is done to the existing data
209 , p_available_hours_before IN NUMBER DEFAULT NULL -- The new value of available before hours.
210 -- If it is NULL then no change is done to the existing data
211 -- If it is FND_API.G_MISS_NUM then the value will be set to NULL in the database
212 , p_available_hours_after IN NUMBER DEFAULT NULL -- The new value of available before hours.
213 -- If it is NULL then no change is done to the existing data
214 -- If it is FND_API.G_MISS_NUM then the value will be set to NULL in the database
215 , p_status IN NUMBER DEFAULT NULL -- The new value of the status
216 -- If it is NULL then no change is done to the existing data
217 , p_availability_type IN VARCHAR2 DEFAULT NULL -- The new value of availability_type
218 --If it is NULL then no change is done to the existing data
219 , p_start_date_time IN DATE DEFAULT NULL -- New value for start date time
220 -- if this value is NULL then no change is done to the existing
221 -- value
222 , p_end_date_time IN DATE DEFAULT NULL -- New value for end date time
223 -- if this value is NULL then no change is done to the existing
224 -- value
225 , p_source_type IN VARCHAR2 DEFAULT NULL -- new value for source type of trip
226 -- If NULL is passed for this value then no change is done
227 -- to exsiting value
228 , x_return_status OUT NOCOPY VARCHAR2 -- 'S': API completed without errors
229 -- 'E': API completed with recoverable errors; explanation on errorstack
230 -- 'U': API completed with UN recoverable errors: error message on error stack
231 , x_msg_count OUT NOCOPY NUMBER -- Number of messages on the errorstack
232 , x_msg_data OUT NOCOPY VARCHAR2 -- contains message if x_msg_count = 1
233 );
234
235 PROCEDURE delete_object_capacity
236 /*******************************************************************************
237 ** delete_object_capacity
238 **
239 ** This API calls table handler to delete data from cac_sr_object_capacity.
240 ** It will also update the task assignments and remove the object capacity id
241 ** if the p_update_tasks is true
242 **
243 *******************************************************************************/
244 ( p_api_version IN NUMBER -- API version you coded against
245 , p_init_msg_list IN VARCHAR2 DEFAULT 'F' -- Create a new error stack?
246 , p_object_capacity_id IN NUMBER -- Primary Key ID of the row to be updated
247 , p_object_version_number IN NUMBER -- Object Version of the row to be updated
248 -- If this doesn't match the database value then that means someone else has updated the same row
249 , p_Update_Tasks IN VARCHAR2 DEFAULT 'T' -- Update task assignments too?
250 , x_return_status OUT NOCOPY VARCHAR2 -- 'S': API completed without errors
251 -- 'E': API completed with recoverable errors; explanation on errorstack
255 );
252 -- 'U': API completed with UN recoverable errors: error message on error stack
253 , x_msg_count OUT NOCOPY NUMBER -- Number of messages on the errorstack
254 , x_msg_data OUT NOCOPY VARCHAR2 -- contains message if x_msg_count = 1
256
257 PROCEDURE build_object_capacity
258 /*******************************************************************************
259 ** build_object_capacity
260 **
261 ** This API calls generate_object_capacity to get the object capacity records.
262 ** It then checks if there are overlapping data in the database for the same
263 ** period and then calls insert_object_capacity to insert data.
264 **
265 *******************************************************************************/
266 ( p_api_version IN NUMBER -- API version you coded against
267 , p_init_msg_list IN VARCHAR2 DEFAULT 'F' -- Create a new error stack?
268 , p_Object_Type IN VARCHAR2 -- JTF OBJECTS type of the Object being queried
269 , p_Object_ID IN NUMBER -- JTF OBJECTS select ID of the Object Instance being queried
270 , p_Start_Date_Time IN DATE -- start date and time of period of interest
271 , p_End_Date_Time IN DATE -- end date and time of period of interest
272 , p_Build_Mode IN VARCHAR2 -- operation mode of the build
273 -- 'ADD' - New object capacity records are generated and inserted
274 -- 'REPLACE' - Existing object capacity records are deleted and new ones are inserted
275 -- 'DELETE' - Existing object capacity records are deleted
276 , p_Update_Tasks IN VARCHAR2 DEFAULT 'F' -- Should the existing task assignments be updated with the object capacity ids?
277 , x_return_status OUT NOCOPY VARCHAR2 -- 'S': API completed without errors
278 -- 'E': API completed with recoverable errors; explanation on errorstack
279 -- 'U': API completed with UN recoverable errors: error message on error stack
280 , x_msg_count OUT NOCOPY NUMBER -- Number of messages on the errorstack
281 , x_msg_data OUT NOCOPY VARCHAR2 -- contains message if x_msg_count = 1
282 );
283
284 END CAC_SR_OBJECT_CAPACITY_PUB;
[Home] [Help]