1 PACKAGE CSF_ACCESS_HOURS_PUB AUTHID CURRENT_USER as
2 /* $Header: CSFPACHS.pls 120.3.12020000.2 2013/02/12 13:26:18 aditysin ship $ */
3 -- Start of Comments
4 --
5 -- Package name : CSF_ACCESS_HOURS_PUB
6 -- Purpose :
7 -- History :
8 -- NOTE :
9 -- End of Comments
10 /*TYPE Param_Rec_Type IS RECORD
11 (
12
13 MONDAY_FIRST_START DATE := FND_API.G_MISS_DATE,
14 MONDAY_FIRST_END DATE := FND_API.G_MISS_DATE,
15 TUESDAY_FIRST_START DATE := FND_API.G_MISS_DATE,
16 TUESDAY_FIRST_END DATE := FND_API.G_MISS_DATE,
17 WEDNESDAY_FIRST_START DATE := FND_API.G_MISS_DATE,
18 WEDNESDAY_FIRST_END DATE := FND_API.G_MISS_DATE,
19 THURSDAY_FIRST_START DATE := FND_API.G_MISS_DATE,
20 THURSDAY_FIRST_END DATE := FND_API.G_MISS_DATE,
21 FRIDAY_FIRST_START DATE := FND_API.G_MISS_DATE,
22 FRIDAY_FIRST_END DATE := FND_API.G_MISS_DATE,
23 SATURDAY_FIRST_START DATE := FND_API.G_MISS_DATE,
24 SATURDAY_FIRST_END DATE := FND_API.G_MISS_DATE,
25 SUNDAY_FIRST_START DATE := FND_API.G_MISS_DATE,
26 SUNDAY_FIRST_END DATE := FND_API.G_MISS_DATE,
27 MONDAY_SECOND_START DATE := FND_API.G_MISS_DATE,
28 MONDAY_SECOND_END DATE := FND_API.G_MISS_DATE,
29 TUESDAY_SECOND_START DATE := FND_API.G_MISS_DATE,
30 TUESDAY_SECOND_END DATE := FND_API.G_MISS_DATE,
31 WEDNESDAY_SECOND_START DATE := FND_API.G_MISS_DATE,
32 WEDNESDAY_SECOND_END DATE := FND_API.G_MISS_DATE,
33 THURSDAY_SECOND_START DATE := FND_API.G_MISS_DATE,
34 THURSDAY_SECOND_END DATE := FND_API.G_MISS_DATE,
35 FRIDAY_SECOND_START DATE := FND_API.G_MISS_DATE,
36 FRIDAY_SECOND_END DATE := FND_API.G_MISS_DATE,
37 SATURDAY_SECOND_START DATE := FND_API.G_MISS_DATE,
38 SATURDAY_SECOND_END DATE := FND_API.G_MISS_DATE,
39 SUNDAY_SECOND_START DATE := FND_API.G_MISS_DATE,
40 SUNDAY_SECOND_END DATE := FND_API.G_MISS_DATE
41 );
42
43 TYPE PARAM_REC_TBL_TYPE is table of Param_Rec_Type;
44
45 */
46
47
48 -- This procedure is for creating the access hour record for a task .
49 -- Please note that access hours record can be created only for records that in IN PLANNING or PLANNED status
50 -- When the task is in PLANNED STATUS,the only possibility is to add the description field
51 -- Parameter related info
52
53 -- param p_api_version the standard API version number eg. 1.0
54
55 -- param p_init_msg_list the standard API flag allows API callers to request
56 -- that the API does the initialization of the message list on their behalf.
57 -- By default, the message list will not be initialized.
58
59 -- All date related parameters such as MONDAY_FIRST_START should be entered as to_date('10:30','hh24:mi:ss')
60 -- By default a value of NULL would be passed to the date fields
61
62 -- After_hours_flag and access_hours_reqd can be set to value Y or N but both cant be Y together
63 -- When access_hours_reqd is set as Y ,atleast one time slot has to be entered
64
65 -- param p_CREATED_BY need not be entered a default value of -1 is finally inserted in the table
66 -- param p_CREATION_DATE need not be entered a default value of sysdate is finally inserted in the table
67 -- param p_LAST_UPDATED_BY need not be entered a default value of -1 is finally inserted in the table
68 -- param p_LAST_UPDATE_DATE need not be entered a default value of sysdate is finally inserted in the table
69 -- param p_LAST_UPDATE_LOGIN need not be entered a default value of -1 is finally inserted in the table
70
71 PROCEDURE CREATE_ACCESS_HOURS(
72 x_ACCESS_HOUR_ID OUT NOCOPY NUMBER,
73 p_API_VERSION NUMBER ,
74 p_init_msg_list varchar2 default NULL,
75 p_TASK_ID NUMBER,
76 p_ACCESS_HOUR_REQD VARCHAR2 default null,
77 p_AFTER_HOURS_FLAG VARCHAR2 default null,
78 p_MONDAY_FIRST_START DATE default TO_DATE(NULL) ,
79 p_MONDAY_FIRST_END DATE default TO_DATE(NULL),
80 p_MONDAY_SECOND_START DATE default TO_DATE(NULL) ,
81 p_MONDAY_SECOND_END DATE default TO_DATE(NULL),
82 p_TUESDAY_FIRST_START DATE default TO_DATE(NULL),
83 p_TUESDAY_FIRST_END DATE default TO_DATE(NULL) ,
84 p_TUESDAY_SECOND_START DATE default TO_DATE(NULL),
85 p_TUESDAY_SECOND_END DATE default TO_DATE(NULL) ,
86 p_WEDNESDAY_FIRST_START DATE default TO_DATE(NULL),
87 p_WEDNESDAY_FIRST_END DATE default TO_DATE(NULL),
88 p_WEDNESDAY_SECOND_START DATE default TO_DATE(NULL),
89 p_WEDNESDAY_SECOND_END DATE default TO_DATE(NULL),
90 p_THURSDAY_FIRST_START DATE default TO_DATE(NULL),
91 p_THURSDAY_FIRST_END DATE default TO_DATE(NULL),
92 p_THURSDAY_SECOND_START DATE default TO_DATE(NULL),
93 p_THURSDAY_SECOND_END DATE default TO_DATE(NULL),
94 p_FRIDAY_FIRST_START DATE default TO_DATE(NULL),
95 p_FRIDAY_FIRST_END DATE default TO_DATE(NULL),
96 p_FRIDAY_SECOND_START DATE default TO_DATE(NULL),
97 p_FRIDAY_SECOND_END DATE default TO_DATE(NULL),
98 p_SATURDAY_FIRST_START DATE default TO_DATE(NULL),
99 p_SATURDAY_FIRST_END DATE default TO_DATE(NULL),
100 p_SATURDAY_SECOND_START DATE default TO_DATE(NULL),
101 p_SATURDAY_SECOND_END DATE default TO_DATE(NULL),
102 p_SUNDAY_FIRST_START DATE default TO_DATE(NULL),
103 p_SUNDAY_FIRST_END DATE default TO_DATE(NULL),
104 p_SUNDAY_SECOND_START DATE default TO_DATE(NULL),
105 p_SUNDAY_SECOND_END DATE default TO_DATE(NULL),
106 p_DESCRIPTION VARCHAR2 DEFAULT null,
107 px_object_version_number in out nocopy number,
108 p_CREATED_BY NUMBER default null,
109 p_CREATION_DATE DATE default null,
110 p_LAST_UPDATED_BY NUMBER default null,
111 p_LAST_UPDATE_DATE DATE default null,
112 p_LAST_UPDATE_LOGIN NUMBER default null,
113 p_commit in varchar2 default fnd_api.g_true,
114 x_return_status OUT NOCOPY VARCHAR2,
115 x_msg_data OUT NOCOPY VARCHAR2,
116 x_msg_count OUT NOCOPY NUMBER
117
118
119 );
120
121
122 -- THis is for updation of already existing access hour related record for a task
123 -- for a IN PLANNING task all fields are updatable
124 -- FOr a PLANNED status task,only the access hour and after hours flag can be set from a value 'Y' to 'N' and the description can be modified
125
126 -- param info
127
128 -- param p_api_version the standard API version number e.g 1.0
129
130 -- param p_init_msg_list the standard API flag allows API callers to request
131 -- that the API does the initialization of the message list on their behalf.
132 -- By default, the message list will not be initialized.
133
134 -- All date related parameters such as MONDAY_FIRST_START should be entered as to_date('10:30','hh24:mi:ss')
135
136 -- After_hours_flag and access_hours_reqd can be set to value Y or N but both cant be Y together
137 -- When access_hours_reqd is set as Y ,atleast one time slot has to be entered
138
139 -- param p_LAST_UPDATED_BY need not be entered a default value of -1 is finally inserted in the table
140 -- param p_LAST_UPDATE_DATE need not be entered a default value of sysdate is finally inserted in the table
141 -- param p_LAST_UPDATE_LOGIN need not be entered a default value of -1 is finally inserted in the table
142
143 PROCEDURE UPDATE_ACCESS_HOURS(
144 p_ACCESS_HOUR_ID IN NUMBER,
145 p_TASK_ID NUMBER,
146 p_API_VERSION NUMBER,
147 p_init_msg_list varchar2 default NULL,
148 p_commit varchar2 default NULL,
149 p_ACCESS_HOUR_REQD VARCHAR2 default NULL,
150 p_AFTER_HOURS_FLAG VARCHAR2 default NULL,
151 p_MONDAY_FIRST_START DATE, --default TO_DATE(NULL),
152 p_MONDAY_FIRST_END DATE, --default TO_DATE(NULL),
153 p_MONDAY_SECOND_START DATE, --default TO_DATE(NULL),
154 p_MONDAY_SECOND_END DATE, --default TO_DATE(NULL),
155 p_TUESDAY_FIRST_START DATE, --default TO_DATE(NULL),
156 p_TUESDAY_FIRST_END DATE, --default TO_DATE(NULL) ,
157 p_TUESDAY_SECOND_START DATE, --default TO_DATE(NULL),
158 p_TUESDAY_SECOND_END DATE, --default TO_DATE(NULL) ,
159 p_WEDNESDAY_FIRST_START DATE, --default TO_DATE(NULL),
160 p_WEDNESDAY_FIRST_END DATE, -- default TO_DATE(NULL),
161 p_WEDNESDAY_SECOND_START DATE, --default TO_DATE(NULL),
162 p_WEDNESDAY_SECOND_END DATE,-- default TO_DATE(NULL),
163 p_THURSDAY_FIRST_START DATE, --default TO_DATE(NULL),
164 p_THURSDAY_FIRST_END DATE, --default TO_DATE(NULL),
165 p_THURSDAY_SECOND_START DATE, --default TO_DATE(NULL),
166 p_THURSDAY_SECOND_END DATE, --default TO_DATE(NULL),
167 p_FRIDAY_FIRST_START DATE,-- default TO_DATE(NULL),
168 p_FRIDAY_FIRST_END DATE,-- default TO_DATE(NULL),
169 p_FRIDAY_SECOND_START DATE, --default TO_DATE(NULL),
170 p_FRIDAY_SECOND_END DATE,-- default TO_DATE(NULL),
171 p_SATURDAY_FIRST_START DATE, --default TO_DATE(NULL),
172 p_SATURDAY_FIRST_END DATE, --default TO_DATE(NULL),
173 p_SATURDAY_SECOND_START DATE,-- default TO_DATE(NULL),
174 p_SATURDAY_SECOND_END DATE, --default TO_DATE(NULL),
175 p_SUNDAY_FIRST_START DATE, --default TO_DATE(NULL),
176 p_SUNDAY_FIRST_END DATE, -- default TO_DATE(NULL),
177 p_SUNDAY_SECOND_START DATE, --default TO_DATE(NULL),
178 p_SUNDAY_SECOND_END DATE, --default TO_DATE(NULL),
179 p_DESCRIPTION VARCHAR2 DEFAULT null,
180 px_object_version_number in out nocopy number,
181 p_CREATED_BY NUMBER default null,
182 p_CREATION_DATE DATE default null,
183 p_LAST_UPDATED_BY NUMBER default null,
184 p_LAST_UPDATE_DATE DATE default null,
185 p_LAST_UPDATE_LOGIN NUMBER default null,
186 x_return_status OUT NOCOPY VARCHAR2,
187 x_msg_data OUT NOCOPY VARCHAR2,
188 x_msg_count OUT NOCOPY NUMBER
189 );
190
191
192
193 -- This is for deletion of access hour record for a task.
194 -- Possible only for In Planning task
195 -- param p_api_version the standard API version number e.g 1.0
196 -- param p_init_msg_list the standard API flag allows API callers to request
197 -- that the API does the initialization of the message list on their behalf.
198 -- By default, the message list will not be initialized.
199
200 PROCEDURE DELETE_ACCESS_HOURS(
201 p_TASK_ID NUMBER,
202 p_ACCESS_HOUR_ID NUMBER,
203 p_API_VERSION NUMBER,
204 p_init_msg_list varchar2 default null,
205 x_return_status OUT NOCOPY VARCHAR2,
206 x_msg_data OUT NOCOPY VARCHAR2,
207 x_msg_count OUT NOCOPY NUMBER);
208
209 -- Overloaded update_access_hours procedure for use in schedule advice logic
210
211 PROCEDURE UPDATE_ACCESS_HOURS(
212 p_api_version number
213 , p_init_msg_list varchar2 default null
214 , p_commit varchar2 default null
215 , p_task_id number
216 , p_access_hour_reqd varchar2
217 , x_object_version_number in out nocopy number
218 , x_return_status out nocopy varchar2
219 , x_msg_data out nocopy varchar2
220 , x_msg_count out nocopy number
221 );
222
223 PROCEDURE ADD_LANGUAGE;
224
225
226 function get_task_status_flag (p_task_id in number) return varchar2;
227
228 END CSF_ACCESS_HOURS_PUB;