1 PACKAGE PA_CONTROL_ITEMS_PVT AS
2 --$Header: PACICIVS.pls 120.2 2007/02/08 11:42:55 sukhanna ship $
3
4
5 procedure ADD_CONTROL_ITEM (
6 p_api_version IN NUMBER := 1.0
7 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_true
8 ,p_commit IN VARCHAR2 := FND_API.g_false
9 ,p_validate_only IN VARCHAR2 := FND_API.g_true
10 ,p_max_msg_count IN NUMBER := FND_API.g_miss_num
11 ,p_ci_type_id IN NUMBER
12 ,p_summary IN VARCHAR2
13 ,p_status_code IN VARCHAR2
14 ,p_owner_id IN NUMBER
15 ,p_highlighted_flag IN VARCHAR2 :='N'
16 ,p_progress_status_code IN VARCHAR2 := NULL
17 ,p_progress_as_of_date IN DATE := NULL
18 ,p_classification_code IN NUMBER
19 ,p_reason_code IN NUMBER
20 ,p_project_id IN NUMBER
21 ,p_last_modified_by_id IN NUMBER
22 ,p_object_type IN VARCHAR2 := NULL
23 ,p_object_id IN NUMBER := NULL
24 ,p_ci_number IN VARCHAR2 := NULL
25 ,p_date_required IN DATE := NULL
26 ,p_date_closed IN DATE := NULL
27 ,p_closed_by_id IN NUMBER := NULL
28 ,p_description IN VARCHAR2 := NULL
29 ,p_status_overview IN VARCHAR2 := NULL
30 ,p_resolution IN VARCHAR2 := NULL
31 ,p_resolution_code IN NUMBER := NULL
32 ,p_priority_code IN VARCHAR2 := NULL
33 ,p_effort_level_code IN VARCHAR2 := NULL
34 ,p_open_action_num IN NUMBER := NULL
35 ,p_price IN NUMBER := NULL
36 ,p_price_currency_code IN VARCHAR2 := NULL
37 ,p_source_type_code IN VARCHAR2 := NULL
38 ,p_source_comment IN VARCHAR2 := NULL
39 ,p_source_number IN VARCHAR2 := NULL
40 ,p_source_date_received IN DATE := NULL
41 ,p_source_organization IN VARCHAR2 := NULL
42 ,p_source_person IN VARCHAR2 := NULL
43 ,p_attribute_category IN VARCHAR2 := NULL
44 ,p_attribute1 IN VARCHAR2 := NULL
45 ,p_attribute2 IN VARCHAR2 := NULL
46 ,p_attribute3 IN VARCHAR2 := NULL
47 ,p_attribute4 IN VARCHAR2 := NULL
48 ,p_attribute5 IN VARCHAR2 := NULL
49 ,p_attribute6 IN VARCHAR2 := NULL
50 ,p_attribute7 IN VARCHAR2 := NULL
51 ,p_attribute8 IN VARCHAR2 := NULL
52 ,p_attribute9 IN VARCHAR2 := NULL
53 ,p_attribute10 IN VARCHAR2 := NULL
54 ,p_attribute11 IN VARCHAR2 := NULL
55 ,p_attribute12 IN VARCHAR2 := NULL
56 ,p_attribute13 IN VARCHAR2 := NULL
57 ,p_attribute14 IN VARCHAR2 := NULL
58 ,p_attribute15 IN VARCHAR2 := NULL
59 ,px_ci_id IN OUT NOCOPY NUMBER
60 ,x_ci_number OUT NOCOPY VARCHAR2
61 ,x_return_status OUT NOCOPY VARCHAR2
62 ,x_msg_count OUT NOCOPY NUMBER
63 ,x_msg_data OUT NOCOPY VARCHAR2
64 );
65
66
67 procedure UPDATE_CONTROL_ITEM (
68 p_api_version IN NUMBER := 1.0
69 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_true
70 ,p_commit IN VARCHAR2 := FND_API.g_false
71 ,p_validate_only IN VARCHAR2 := FND_API.g_true
72 ,p_max_msg_count IN NUMBER := FND_API.g_miss_num
73
74 ,p_ci_id IN NUMBER
75 ,p_ci_type_id IN NUMBER
76 ,p_summary IN VARCHAR2
77 ,p_status_code IN VARCHAR2 := NULL
78 ,p_owner_id IN NUMBER
79 ,p_highlighted_flag IN VARCHAR2 := 'N'
80 ,p_progress_status_code IN VARCHAR2
81 ,p_progress_as_of_date IN DATE := NULL
82 ,p_classification_code IN NUMBER
83 ,p_reason_code IN NUMBER
84 ,p_record_version_number IN NUMBER
85 ,p_project_id IN NUMBER
86 ,p_last_modified_by_id IN NUMBER
87 ,p_object_type IN VARCHAR2 := NULL
88 ,p_object_id IN NUMBER := NULL
89 ,p_ci_number IN VARCHAR2 := NULL
90 ,p_date_required IN DATE := NULL
91 ,p_date_closed IN DATE := NULL
92 ,p_closed_by_id IN NUMBER := NULL
93
94 ,p_description IN VARCHAR2 := NULL
95 ,p_status_overview IN VARCHAR2 := NULL
96 ,p_resolution IN VARCHAR2 := NULL
97 ,p_resolution_code IN NUMBER := NULL
98 ,p_priority_code IN VARCHAR2 := NULL
99 ,p_effort_level_code IN VARCHAR2 := NULL
100 ,p_open_action_num IN NUMBER := NULL
101 ,p_price IN NUMBER := NULL
102 ,p_price_currency_code IN VARCHAR2 := NULL
103 ,p_source_type_code IN VARCHAR2 := NULL
104 ,p_source_comment IN VARCHAR2 := NULL
105 ,p_source_number IN VARCHAR2 := NULL
106 ,p_source_date_received IN DATE := NULL
107 ,p_source_organization IN VARCHAR2 := NULL
108 ,p_source_person IN VARCHAR2 := NULL
109 ,p_attribute_category IN VARCHAR2 := NULL
110 ,p_attribute1 IN VARCHAR2 := NULL
111 ,p_attribute2 IN VARCHAR2 := NULL
112 ,p_attribute3 IN VARCHAR2 := NULL
113 ,p_attribute4 IN VARCHAR2 := NULL
114 ,p_attribute5 IN VARCHAR2 := NULL
115 ,p_attribute6 IN VARCHAR2 := NULL
116 ,p_attribute7 IN VARCHAR2 := NULL
117 ,p_attribute8 IN VARCHAR2 := NULL
118 ,p_attribute9 IN VARCHAR2 := NULL
119 ,p_attribute10 IN VARCHAR2 := NULL
120 ,p_attribute11 IN VARCHAR2 := NULL
121 ,p_attribute12 IN VARCHAR2 := NULL
122 ,p_attribute13 IN VARCHAR2 := NULL
123 ,p_attribute14 IN VARCHAR2 := NULL
124 ,p_attribute15 IN VARCHAR2 := NULL
125 ,x_return_status OUT NOCOPY VARCHAR2
126 ,x_msg_count OUT NOCOPY NUMBER
127 ,x_msg_data OUT NOCOPY VARCHAR2
128
129 );
130
131 procedure DELETE_CONTROL_ITEM (
132 p_api_version IN NUMBER := 1.0
133 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_true
134 ,p_commit IN VARCHAR2 := FND_API.g_false
135 ,p_validate_only IN VARCHAR2 := FND_API.g_true
136 ,p_max_msg_count IN NUMBER := FND_API.g_miss_num
137
138 ,p_ci_id IN NUMBER
139 ,p_record_version_number IN NUMBER
140 ,x_return_status OUT NOCOPY VARCHAR2
141 ,x_msg_count OUT NOCOPY NUMBER
142 ,x_msg_data OUT NOCOPY VARCHAR2
143 );
144
145 procedure DELETE_ALL_CONTROL_ITEMS(
146 p_api_version IN NUMBER := 1.0
147 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_true
148 ,p_commit IN VARCHAR2 := FND_API.g_false
149 ,p_validate_only IN VARCHAR2 := FND_API.g_true
150 ,p_max_msg_count IN NUMBER := FND_API.g_miss_num
151
152 ,p_project_id IN NUMBER
153 ,x_return_status OUT NOCOPY VARCHAR2
154 ,x_msg_count OUT NOCOPY NUMBER
155 ,x_msg_data OUT NOCOPY VARCHAR2
156 );
157
158 procedure COPY_CONTROL_ITEM (
159 p_api_version IN NUMBER := 1.0
160 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_true
161 ,p_commit IN VARCHAR2 := FND_API.g_false
162 ,p_validate_only IN VARCHAR2 := FND_API.g_true
163 ,p_max_msg_count IN NUMBER := FND_API.g_miss_num
164
165 ,p_project_id IN NUMBER
166 ,p_ci_id_from IN NUMBER -- copy from this
167 ,p_ci_type_id IN NUMBER -- copy to this
168 ,p_classification_code_id IN NUMBER
169 ,p_reason_code_id IN NUMBER
170
171 ,p_include IN VARCHAR2 := 'N'
172 ,p_record_version_number_from IN NUMBER
173 ,x_ci_id OUT NOCOPY NUMBER
174 ,x_ci_number OUT NOCOPY VARCHAR2
175 ,x_return_status OUT NOCOPY VARCHAR2
176 ,x_msg_count OUT NOCOPY NUMBER
177 ,x_msg_data OUT NOCOPY VARCHAR2
178 );
179
180 procedure INCLUDE_CONTROL_ITEM(
181 p_api_version IN NUMBER := 1.0
182 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_true
183 ,p_commit IN VARCHAR2 := FND_API.g_false
184 ,p_validate_only IN VARCHAR2 := FND_API.g_true
185 ,p_max_msg_count IN NUMBER := FND_API.g_miss_num
186
187 ,p_from_ci_id IN NUMBER
188 ,p_to_ci_id IN NUMBER
189 ,p_record_version_number_to IN NUMBER
190 ,x_relationship_id OUT NOCOPY NUMBER
191 ,x_return_status OUT NOCOPY VARCHAR2
192 ,x_msg_count OUT NOCOPY NUMBER
193 ,x_msg_data OUT NOCOPY VARCHAR2
194 );
195
196 procedure UPDATE_NUMBER_OF_ACTIONS (
197 p_api_version IN NUMBER := 1.0
198 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_true
199 ,p_commit IN VARCHAR2 := FND_API.g_false
200 ,p_validate_only IN VARCHAR2 := FND_API.g_true
201 ,p_max_msg_count IN NUMBER := FND_API.g_miss_num
202
203 ,p_ci_id IN NUMBER
204 ,p_num_of_actions IN NUMBER
205 ,p_record_version_number IN NUMBER
206
207 ,x_num_of_actions OUT NOCOPY NUMBER
208 ,x_return_status OUT NOCOPY VARCHAR2
209 ,x_msg_count OUT NOCOPY NUMBER
210 ,x_msg_data OUT NOCOPY VARCHAR2
211 ,p_last_updated_by in NUMBER default fnd_global.user_id
212 ,p_last_update_date in DATE default sysdate
213 ,p_last_update_login in NUMBER default fnd_global.user_id
214 );
215
216 procedure UPDATE_CONTROL_ITEM_STATUS (
217 p_api_version IN NUMBER := 1.0
218 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_true
219 ,p_commit IN VARCHAR2 := FND_API.g_false
220 ,p_validate_only IN VARCHAR2 := FND_API.g_true
221 ,p_max_msg_count IN NUMBER := FND_API.g_miss_num
222
223 ,p_ci_id IN NUMBER
224 ,p_status_code IN VARCHAR2
225 ,p_record_version_number IN NUMBER
226
227 ,x_return_status OUT NOCOPY VARCHAR2
228 ,x_msg_count OUT NOCOPY NUMBER
229 ,x_msg_data OUT NOCOPY VARCHAR2
230
231 );
232
233
234 FUNCTION ASSIGN_CONTROL_ITEM_NUMBER(
235 p_project_id IN NUMBER
236 ,p_ci_type_id IN NUMBER
237 ) RETURN VARCHAR2;
238
239
240 FUNCTION has_null_data (
241 p_ci_type_id IN NUMBER
242 ,p_project_id IN NUMBER
243 ,p_status_code IN VARCHAR2
244 ,p_owner_id IN NUMBER
245 ,p_summary IN VARCHAR2
246
247 ) RETURN BOOLEAN;
248
249 FUNCTION ci_number_exists(p_project_id IN NUMBER
250 ,p_ci_number IN VARCHAR2
251 ,p_ci_type_id IN NUMBER)
252
253 RETURN BOOLEAN;
254
255
256 FUNCTION is_lookup_valid (
257 p_lookup_type IN VARCHAR2
258 ,p_lookup_code IN VARCHAR2
259 ) RETURN BOOLEAN;
260
261 PROCEDURE change_included_cr_status(p_ci_id IN NUMBER
262 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
263 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
264 ,x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
265
266 PROCEDURE add_related_item (
267 p_api_version IN NUMBER := 1.0,
268 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
269 p_commit IN VARCHAR2 := FND_API.g_false,
270 p_validate_only IN VARCHAR2 := FND_API.g_true,
271 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
272 p_ci_id IN NUMBER,
273 p_related_ci_id IN NUMBER,
274 x_return_status OUT NOCOPY VARCHAR2,
275 x_msg_count OUT NOCOPY NUMBER,
276 x_msg_data OUT NOCOPY VARCHAR2
277 );
278
279 PROCEDURE delete_related_item (
280 p_api_version IN NUMBER := 1.0,
281 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
282 p_commit IN VARCHAR2 := FND_API.g_false,
283 p_validate_only IN VARCHAR2 := FND_API.g_true,
284 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
285 p_ci_id IN NUMBER,
286 p_related_ci_id IN NUMBER,
287 x_return_status OUT NOCOPY VARCHAR2,
288 x_msg_count OUT NOCOPY NUMBER,
289 x_msg_data OUT NOCOPY VARCHAR2
290 );
291
292 PROCEDURE delete_all_related_items (
293 p_api_version IN NUMBER := 1.0,
294 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
295 p_commit IN VARCHAR2 := FND_API.g_false,
296 p_validate_only IN VARCHAR2 := FND_API.g_true,
297 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
298 p_ci_id IN NUMBER,
299 x_return_status OUT NOCOPY VARCHAR2,
300 x_msg_count OUT NOCOPY NUMBER,
301 x_msg_data OUT NOCOPY VARCHAR2
302 );
303
304 PROCEDURE copy_related_items (
305 p_api_version IN NUMBER := 1.0,
306 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
307 p_commit IN VARCHAR2 := FND_API.g_false,
308 p_validate_only IN VARCHAR2 := FND_API.g_true,
309 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
310 p_from_ci_id IN NUMBER,
311 p_to_ci_id IN NUMBER,
312 x_return_status OUT NOCOPY VARCHAR2,
313 x_msg_count OUT NOCOPY NUMBER,
314 x_msg_data OUT NOCOPY VARCHAR2
315 );
316
317 PROCEDURE delete_all_included_crs (
318 p_api_version IN NUMBER := 1.0,
319 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
320 p_commit IN VARCHAR2 := FND_API.g_false,
321 p_validate_only IN VARCHAR2 := FND_API.g_true,
322 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
323 p_ci_id IN NUMBER,
324 x_return_status OUT NOCOPY VARCHAR2,
325 x_msg_count OUT NOCOPY NUMBER,
326 x_msg_data OUT NOCOPY VARCHAR2);
327
328 END PA_CONTROL_ITEMS_PVT;