1 PACKAGE PA_CONTROL_ITEMS_UTILS AUTHID CURRENT_USER AS
2 --$Header: PACICIUS.pls 120.4.12020000.2 2012/07/19 09:29:34 admarath ship $
3
4 g_ci_id Number := NULL ;
5 g_ci_type_id Number := NULL ;
6 g_ci_status varchar2(30) := NULL ;
7 g_ci_type_has_impact varchar2(1) := 'N' ;
8 g_type_has_impact varchar2(1) := 'N' ;
9
10
11 function GET_OBJECT_NAME(
12 p_project_id IN NUMBER
13 ,p_object_id IN NUMBER := NULL
14 ,p_object_type IN VARCHAR2 := NULL
15 ) RETURN VARCHAR2;
16
17 function GET_INITIAL_CI_STATUS(
18 p_ci_type_id IN NUMBER := NULL
19 ) RETURN VARCHAR2;
20
21 -- removed and replaced by API in PA_UTILS
22 --function GET_PARTY_ID (
23 -- p_resource_id in NUMBER,
24 -- p_resource_type_id in NUMBER)
25 --RETURN NUMBER;
26
27 Function IsImpactOkToInclude(p_ci_type_id_1 IN NUMBER,
28 p_ci_type_id_2 IN NUMBER,
29 p_ci_id_2 IN NUMBER) return VARCHAR2;
30
31 /* This Function will determine the specific action is allowed on the
32 control item based on the current status.The return values are
33 Y - Allows the specified action
34 N - Specified action is not allowed
35 */
36 Function CheckCIActionAllowed(p_status_type IN VARCHAR2 default null,
37 p_status_code IN VARCHAR2 default null,
38 p_action_code IN VARCHAR2 default null,
39 p_ci_id IN NUMBER default null) return VARCHAR2;
40
41 Function CheckValidNextCISysStatus( p_curr_sys_status in varchar2
42 ,p_next_sys_status in varchar2
43 ,p_ci_type_class in varchar2
44 ,p_approval_req_flag in varchar2)
45 return Boolean;
46
47 Function CheckValidNextCIStatus( p_ci_id in Number
48 ,p_next_status in varchar2)
49 return Boolean;
50
51 /*----------------------------------------------------------------------------
52 Function to return status of control Item
53 -----------------------------------------------------------------------------*/
54 FUNCTION getCIStatus ( p_CI_id IN NUMBER)
55 return VARCHAR2 ;
56
57 /*-----------------------------------------------------------------------------
58 Function to check whether CI type has any impact
59 This function retrieves impact flag for a control item
60 -----------------------------------------------------------------------------*/
61 FUNCTION isCITypehasimpact ( p_CI_id IN NUMBER)
62 return VARCHAR2 ;
63
64 /*-----------------------------------------------------------------------------
65 Function to check whether a type has any impact
66 This one requires a TYPE ID as IN parm
67 -----------------------------------------------------------------------------*/
68 FUNCTION TypeHasImpact ( p_ci_type_id IN NUMBER)
69 return VARCHAR2 ;
70
71 /*-------------------------------------------------------------------------------------
72 Function Name: CheckValidNextPage
73 Usage: Used with with lookup type to determine valid list of next pages to navigate
74 Rules: 1. If CI id is null it is the create page. Allow all Next page
75 2. Exclude the current page from the next page list.
76
77 ---------------------------------------------------------------------------------------*/
78 -- !!! NOT USED - REPLACED with CheckNextPageValid
79 -- FUNCTION CheckValidNextPage( p_ci_id IN NUMBER
80 -- ,p_type_id IN VARCHAR2
81 -- ,p_status_control IN VARCHAR2
82 -- ,p_page_code IN VARCHAR2
83 -- ,p_currpage_code IN VARCHAR2
84 -- ,p_action_list IN VARCHAR2 := 'N')
85 -- return VARCHAR2;
86
87
88 PROCEDURE checkandstartworkflow
89 (
90 p_api_version IN NUMBER := 1.0,
91 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
92 p_commit IN VARCHAR2 := FND_API.g_false,
93 p_validate_only IN VARCHAR2 := FND_API.g_true,
94 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
95
96 p_ci_id in NUMBER,
97 p_status_code IN VARCHAR2,
98
99 x_msg_count out NOCOPY NUMBER,
100 x_msg_data out NOCOPY VARCHAR2,
101 x_return_status OUT NOCOPY VARCHAR2
102 ) ;
103
104 /*-------------------------------------------------------------------------------------
105 This function returns hz_parties.party_name for fnd_user.user_id (IN parameter)
106 -------------------------------------------------------------------------------------*/
107 FUNCTION GetUserName( p_user_id in Number)
108 return Varchar2;
109 /*-------------------------------------------------------------------------------------
110 This function returns hz_parties.party_id for fnd_user.user_id (IN parameter)
111 -------------------------------------------------------------------------------------*/
112 FUNCTION GetPartyId( p_user_id in Number)
113 return NUMBER;
114
115 FUNCTION CheckApprovalRequired(p_ci_id in Number)
116 return Varchar2;
117
118 FUNCTION CheckResolutionRequired(p_ci_id in Number)
119 return Varchar2;
120
121 FUNCTION CheckHasResolution(p_ci_id in Number)
122 return Varchar2;
123
124 FUNCTION GetCITypeClassCode(p_ci_id in Number)
125 return Varchar2;
126
127 FUNCTION getCISystemStatus ( p_CI_id IN NUMBER)
128 return VARCHAR2;
129
130 FUNCTION getSystemStatus ( p_status_code IN VARCHAR2)
131 return VARCHAR2;
132
133
134 FUNCTION submitAllowed ( p_ci_id IN NUMBER := NULL
135 ,p_owner_id IN NUMBER := NULL
136 ,p_created_by_id IN NUMBER := NULL
137 ,p_system_status IN VARCHAR2 := NULL)
138 return VARCHAR2;
139
140 FUNCTION deleteAllowed ( p_ci_id IN NUMBER := NULL
141 ,p_owner_id IN NUMBER := NULL
142 ,p_created_by_id IN NUMBER := NULL
143 ,p_system_status IN VARCHAR2 := NULL)
144 return VARCHAR2;
145 FUNCTION closeAllowed ( p_ci_id IN NUMBER := NULL
146 ,p_owner_id IN NUMBER := NULL
147 ,p_created_by_id IN NUMBER := NULL
148 ,p_system_status IN VARCHAR2 := NULL)
149 return VARCHAR2;
150
151 PROCEDURE ChangeCIStatus (
152 p_init_msg_list IN VARCHAR2 := fnd_api.g_true
153 ,p_commit IN VARCHAR2 := FND_API.g_false
154 ,p_validate_only IN VARCHAR2 := FND_API.g_true
155 ,p_max_msg_count IN NUMBER := FND_API.g_miss_num
156 ,p_ci_id in number
157 ,p_status in varchar2
158 ,p_comment in VARCHAR2 := null
159 ,p_enforce_security in Varchar2 DEFAULT 'Y'
160 ,p_record_version_number IN NUMBER
161 ,x_num_of_actions OUT NOCOPY NUMBER
162 ,x_return_status OUT NOCOPY VARCHAR2
163 ,x_msg_count OUT NOCOPY NUMBER
164 ,x_msg_data OUT NOCOPY VARCHAR2 );
165
166
167 PROCEDURE CancelWorkflow
168 (
169 p_api_version IN NUMBER := 1.0,
170 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
171 p_commit IN VARCHAR2 := FND_API.g_false,
172 p_validate_only IN VARCHAR2 := FND_API.g_true,
173 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
174
175 p_ci_id in NUMBER,
176
177 x_msg_count out NOCOPY NUMBER,
178 x_msg_data out NOCOPY VARCHAR2,
179 x_return_status OUT NOCOPY VARCHAR2
180 ) ;
181
182 FUNCTION CheckNonDraftCI(p_project_id in Number)
183 return Varchar2;
184
185 function GET_PARTY_ID_FROM_NAME(p_name IN VARCHAR2
186 ) return NUMBER;
187
188 FUNCTION check_control_item_exists(
189 p_project_id IN NUMBER,
190 p_task_id IN NUMBER default NULL)
191 RETURN NUMBER;
192
193 FUNCTION check_class_category_in_use(
194 p_class_category IN VARCHAR2)
195 RETURN NUMBER;
196
197 FUNCTION check_class_code_in_use(
198 p_class_category IN VARCHAR2,
199 p_class_code IN VARCHAR2)
200 RETURN NUMBER;
201
202 FUNCTION check_role_in_use(
203 p_project_role_id IN NUMBER)
204 RETURN NUMBER;
205
206 FUNCTION check_project_type_in_use(
207 p_project_type_id IN NUMBER)
208 RETURN NUMBER;
209 /*-------------------------------------------------------------------------------------
210 Function Name: CheckNextPageValid
211 Usage: Used with with lookup type to determine valid list of next pages to navigate
212 Rules: 1. If CI id is null it is the create page.
213 2. Exclude the current page from the next page list.
214
215 ---------------------------------------------------------------------------------------*/
216 FUNCTION CheckNextPageValid( p_ci_id IN NUMBER := NULL
217 ,p_type_id IN VARCHAR2
218 ,p_status_control IN VARCHAR2
219 ,p_page_code IN VARCHAR2
220 ,p_currpage_code IN VARCHAR2
221 ,p_type_class_code IN VARCHAR2)
222
223 return VARCHAR2;
224
225 function get_open_control_items(p_project_id IN NUMBER,
226 p_object_type IN VARCHAR2,
227 p_object_id IN NUMBER,
228 p_item_type IN VARCHAR2) return number;
229
230 Procedure GetDiagramUrl(p_project_id IN NUMBER,
231 p_ci_id IN NUMBER,
232 x_diagramurl OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
233 x_return_status OUT NOCOPY VARCHAR2,
234 x_msg_count OUT NOCOPY NUMBER,
235 x_msg_data OUT NOCOPY VARCHAR2);
236
237 Procedure AbortWorkflow(p_project_id IN NUMBER,
238 p_ci_id IN NUMBER,
239 p_record_version_number IN NUMBER,
240 x_return_status OUT NOCOPY VARCHAR2,
241 x_msg_count OUT NOCOPY NUMBER,
242 x_msg_data OUT NOCOPY VARCHAR2);
243
244 Procedure ADD_STATUS_CHANGE_COMMENT(
245 p_object_type IN VARCHAR2
246 ,p_object_id IN NUMBER
247 ,p_type_code IN VARCHAR2
248 ,p_status_type IN VARCHAR2
249 ,p_new_project_status IN VARCHAR2
250 ,p_old_project_status IN VARCHAR2
251 ,p_comment IN VARCHAR2 := null
252 ,P_CREATED_BY IN NUMBER default fnd_global.user_id
253 ,P_CREATION_DATE IN DATE default sysdate
254 ,P_LAST_UPDATED_BY IN NUMBER default fnd_global.user_id
255 ,P_LAST_UPDATE_DATE IN DATE default sysdate
256 ,P_LAST_UPDATE_LOGIN IN NUMBER default fnd_global.user_id
257 ,x_return_status OUT NOCOPY VARCHAR2
258 ,x_msg_count OUT NOCOPY NUMBER
259 ,x_msg_data OUT NOCOPY VARCHAR2 );
260
261
262 --Bug 4716789 Added an API to delete the data from pa_obj_status_changes
263 Procedure DELETE_OBJ_STATUS_CHANGES(
264 p_object_type IN VARCHAR2
265 ,p_object_id IN NUMBER
266 ,x_return_status OUT NOCOPY VARCHAR2
267 ,x_msg_count OUT NOCOPY NUMBER
268 ,x_msg_data OUT NOCOPY VARCHAR2 );
269
270 PROCEDURE ChangeCIStatusValidate (
271 p_init_msg_list IN VARCHAR2 := fnd_api.g_true
272 ,p_commit IN VARCHAR2 := FND_API.g_false
273 ,p_validate_only IN VARCHAR2 := FND_API.g_true
274 ,p_max_msg_count IN NUMBER := FND_API.g_miss_num
275 ,p_ci_id IN NUMBER
276 ,p_status IN VARCHAR2
277 ,p_enforce_security IN VARCHAR2 DEFAULT 'Y'
278 ,p_resolution_check IN VARCHAR2 DEFAULT 'UI'
279 ,x_resolution_req OUT NOCOPY VARCHAR2
280 ,x_resolution_req_cls OUT NOCOPY VARCHAR2
281 ,x_start_wf OUT NOCOPY VARCHAR2
282 ,x_new_status OUT NOCOPY VARCHAR2
283 ,x_num_of_actions OUT NOCOPY NUMBER
284 ,x_return_status OUT NOCOPY VARCHAR2
285 ,x_msg_count OUT NOCOPY NUMBER
286 ,x_msg_data OUT NOCOPY VARCHAR2 );
287
288
289 PROCEDURE PostChangeCIStatus (
290 p_init_msg_list IN VARCHAR2 := fnd_api.g_true
291 ,p_commit IN VARCHAR2 := FND_API.g_false
292 ,p_validate_only IN VARCHAR2 := FND_API.g_true
293 ,p_max_msg_count IN NUMBER := FND_API.g_miss_num
294 ,p_ci_id in number
295 ,p_curr_status in varchar2
296 ,p_new_status in varchar2
297 ,p_start_wf in VARCHAR2
298 ,p_enforce_security in Varchar2 DEFAULT 'Y'
299 ,x_num_of_actions OUT NOCOPY NUMBER
300 ,x_return_status OUT NOCOPY VARCHAR2
301 ,x_msg_count OUT NOCOPY NUMBER
302 ,x_msg_data OUT NOCOPY VARCHAR2 );
303
304 -- Bug#13683760 changes start.
305 PROCEDURE ChangeCIStatusWorking (
306 p_init_msg_list IN VARCHAR2 := fnd_api.g_true
307 ,p_commit IN VARCHAR2 := FND_API.g_false
308 ,p_max_msg_count IN NUMBER := FND_API.g_miss_num
309 ,p_validate_only IN VARCHAR2 := FND_API.g_true
310 ,p_ci_id in number
311 ,p_status in varchar2
312 ,p_record_version_number IN NUMBER
313 ,x_return_status OUT NOCOPY VARCHAR2
314 ,x_msg_count OUT NOCOPY NUMBER
315 ,x_msg_data OUT NOCOPY VARCHAR2 );
316 -- Bug#13683760 changes end.
317
318 END PA_CONTROL_ITEMS_UTILS;