1 PACKAGE BODY PA_CI_ACTIONS_UTIL AS
2 /* $Header: PACIACUB.pls 120.1 2005/08/01 03:09:56 raluthra noship $ */
3
4 Function action_with_reply(p_ci_action_id in number)
5 return varchar2
6 IS
7 Cursor with_reply is
8 select 'Y'
9 from pa_ci_comments
10 where ci_action_id = p_ci_action_id
11 and type_code <> 'REQUESTOR';
12
13 l_return_value VARCHAR2(1);
14
15 BEGIN
16 OPEN with_reply;
17 FETCH with_reply into l_return_value;
18 CLOSE with_reply;
19 if (l_return_value IS NULL) then
20 return 'N';
21 end if;
22 return l_return_value;
23 END action_with_reply;
24
25
26 Function get_next_ci_action_number(p_ci_id in number)
27 return number
28 IS
29 Cursor next_number is
30 select pci.last_action_number
31 from pa_control_items pci
32 where pci.ci_id = p_ci_id
33 for update of pci.last_action_number;
34
35 l_next_number number;
36
37 BEGIN
38 OPEN next_number;
39 FETCH next_number into l_next_number;
40 close next_number;
41 if (l_next_number IS NULL) then
42 l_next_number := 1;
43 end if;
44
45
46 UPDATE pa_control_items
47 set last_action_number = l_next_number + 1
48 where ci_id = p_ci_id;
49 return l_next_number;
50 EXCEPTION
51 WHEN OTHERS THEN -- catch the exceptins here
52 RAISE;
53 END get_next_ci_action_number;
54
55 Function get_party_id (
56 p_user_id in number )
57 return number
58 IS
59 -- Bug 4527617: Modified Cursor Definition.
60 Cursor external is
61 select person_party_id from fnd_user
62 where user_id = p_user_id;
63 /* Cursor external is
64 select customer_id from fnd_user
65 where user_id = p_user_id; */
66
67
68 -- Modified the cursor definiton for bug#4068669.
69 Cursor internal is
70 select pap.party_id
71 from per_all_people_f pap,
72 fnd_user fu
73 where fu.user_id = p_user_id
74 and fu.employee_id = pap.person_id
75 and trunc(sysdate) between trunc(pap.effective_start_date) and trunc(pap.effective_end_date) ;
76 /*select h.party_id
77 from hz_parties h
78 ,fnd_user f
79 where h.orig_system_reference = CONCAT('PER:',f.employee_id)
80 and f.user_id = p_user_id;*/
81
82 l_party_id number;
83
84 Begin
85 Open internal;
86 fetch internal into l_party_id;
87 if (internal%NOTFOUND) then
88 l_party_id := NULL;
89 end if;
90 close internal;
91
92 if (l_party_id IS NULL) then
93 Open external;
94 fetch external into l_party_id;
95 if (external%NOTFOUND) then
96 l_party_id := NULL;
97 end if;
98 close external;
99 end if;
100
101 return l_party_id;
102 Exception
103 When others then
104 RAISE;
105 End get_party_id;
106
107 PROCEDURE CheckHzPartyName_Or_Id( p_resource_id IN NUMBER,
108 p_resource_name IN VARCHAR2,
109 p_date IN DATE DEFAULT SYSDATE,
110 p_check_id_flag IN VARCHAR2,
111 p_resource_type_id IN NUMBER DEFAULT 101,
112 x_party_id OUT NOCOPY NUMBER,
113 x_resource_type_id OUT NOCOPY NUMBER,
114 x_return_status OUT NOCOPY VARCHAR2,
115 x_msg_count OUT NOCOPY NUMBER,
116 x_msg_data OUT NOCOPY VARCHAR2)IS
117
118 l_resource_id number;
119 l_error_message_code varchar2(30);
120 l_start_date_active date;
121
122 -- Modified the cursor defintion for bug#4068669.
123 Cursor C1 IS
124 select party_id
125 from per_all_people_f p
126 where p.person_id = l_resource_id
127 and trunc(p_date) between trunc(p.effective_start_date) and trunc(p.effective_end_date);
128 --select party_id from hz_parties
129 --where orig_system_reference = 'PER:'||TO_CHAR(l_resource_id);
130
131 BEGIN
132 -- Initialize the Error Stack
133 PA_DEBUG.init_err_stack('PA_CI_ACTIONS_UTIL.CHECKHZPARTYNAME_OR_ID');
134
135 -- Initialize the return status to success
136 x_return_status := FND_API.G_RET_STS_SUCCESS;
137
138 PA_RESOURCE_UTILS.Check_ResourceName_Or_Id ( p_resource_id => p_resource_id
139 ,p_resource_type_id => p_resource_type_id
140 ,p_resource_name => p_resource_name
141 ,p_check_id_flag => PA_STARTUP.G_Check_ID_Flag
142 ,p_date => p_date
143 ,x_resource_id => l_resource_id
144 ,x_resource_type_id => x_resource_type_id
145 ,x_return_status => x_return_status
146 ,x_error_message_code => l_error_message_code);
147
148 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
149 x_return_status := FND_API.G_RET_STS_ERROR;
150 PA_UTILS.Add_Message( p_app_short_name => 'PA'
151 ,p_msg_name => 'PA_CI_ACTION_INVALID_ASSIGNEE');
152 return;
153 end if;
154
155 If (x_return_status = fnd_api.g_ret_sts_success
156 AND l_resource_id <> -999) then
157
158 if (x_resource_type_id = 101) then
159 OPEN C1;
160 FETCH C1 into x_party_id;
161 IF C1%NOTFOUND THEN
162 x_return_status := FND_API.G_RET_STS_ERROR;
163 PA_UTILS.Add_Message( p_app_short_name => 'PA'
164 ,p_msg_name => 'PA_CI_ACTION_INVALID_ASSIGNEE');
165 return;
166 ELSE
167 x_return_status := fnd_api.g_ret_sts_success;
168 END IF;
169 CLOSE C1;
170 else
171 x_party_id := l_resource_id;
172 end if;
173 End If;
174 EXCEPTION
175
176 WHEN OTHERS THEN
177 -- Set the exception Message and the stack
178 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_CI_ACTIONS_UTILS.CheckHzPartyName_Or_Id'
179 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
180 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
181 RAISE;
182
183 END;
184
185
186 function GET_CI_OPEN_ACTIONS(
187 p_ci_id IN NUMBER := NULL) RETURN NUMBER IS
188
189 total_act NUMBER:=0;
190 BEGIN
191 select count(*)
192 into total_act
193 from pa_ci_actions
194 where ci_id = p_ci_id and
195 status_code = 'CI_ACTION_OPEN';
196 return total_act;
197
198 exception when others then
199 return 0;
200
201 END GET_CI_OPEN_ACTIONS;
202
203 function GET_MY_ACTIONS(p_action_status IN VARCHAR2,
204 p_ci_id IN NUMBER := NULL) RETURN NUMBER IS
205
206 BEGIN
207 return 0;
208 END GET_MY_ACTIONS;
209
210 function CHECK_OPEN_ACTIONS_EXIST(p_ci_id IN NUMBER := NULL)
211 RETURN VARCHAR2
212 IS
213 Cursor open_actions is
214 select open_action_num
215 from pa_control_items
216 where ci_id = p_ci_id;
217
218 --l_result varchar2(1); Commented and changed the data type for bug 4034873
219 l_result pa_control_items.open_action_num%type;
220
221 BEGIN
222 Open open_actions;
223 fetch open_actions into l_result;
224 if (open_actions%NOTFOUND) then
225 close open_actions;
226 return 'N';
227 end if;
228 close open_actions;
229 if (l_result > 0) then
230 return 'Y';
231 else
232 return 'N';
233 end if;
234 END CHECK_OPEN_ACTIONS_EXIST;
235
236 function GET_TOP_PARENT_ACTION(p_ci_action_id IN NUMBER)
237 RETURN NUMBER
238 IS
239 l_ci_action_id number;
240
241 Cursor action_source is
242 select ci_action_id
243 from pa_ci_actions
244 where source_ci_action_id is null
245 start with ci_action_id = p_ci_action_id
246 connect by prior source_ci_action_id = ci_action_id;
247
248 l_parent_ci_action_id number;
249
250 BEGIN
251 OPEN action_source;
252 FETCH action_source
253 INTO l_parent_ci_action_id;
254 if (l_parent_ci_action_id is null) then
255 l_parent_ci_action_id := p_ci_action_id;
256 end if;
257 CLOSE action_source;
258 return l_parent_ci_action_id;
259 END GET_TOP_PARENT_ACTION;
260
261 END; -- Package Body PA_CI_ACTIONS_UTIL
[Home] [Help]