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