[Home] [Help]
PACKAGE BODY: APPS.POREQ
Source
1 package body poreq as
2 --$Header: ICXPORQB.pls 115.1 99/07/17 03:20:22 porting ship $
3 --
4 --
5 /*------------------------------------------------------------------------
6 | |
7 | PRIVATE FUNCTIONS |
8 | |
9 --------------------------------------------------------------------------*/
10 --
11 --
12 function GetApproverDirect(p_requisition_header_id in number, p_employee_id in number,p_approval_path_id in number ) return number;
13 --
14 --
15 function GetApproverHier ( p_employee_id in number , p_approval_path_id in number ) return number;
16 --
17 --
18 /*------------------------------------------------------------------------
19 | |
20 | PRIVATE VARIABLES |
21 | |
22 --------------------------------------------------------------------------*/
23 --
24 fsp financials_system_parameters%ROWTYPE;
25 --
26 --
27 Procedure GetReqInfo( p_requisition_header_id in number,
28 p_req_info out ReqInfoType ) is
29 --
30 begin
31 --
32 --
33 SELECT porh.preparer_id,
34 'REQUISITION',
35 porh.type_lookup_code,
36 podt.forwarding_mode_code,
37 nvl(podt.default_approval_path_id,0),
38 nvl(can_preparer_approve_flag,'N')
39 INTO p_req_info
40 FROM po_requisition_headers porh,
41 po_document_types podt
42 WHERE porh.requisition_header_id = p_requisition_header_id
43 AND podt.document_type_code = 'REQUISITION'
44 AND podt.document_subtype = porh.type_lookup_code;
45 --
46 --
47 end GetReqInfo;
48 --
49 --
50 function GetCurrentOwner( p_requisition_header_id in number ) return number is
51 --
52 l_employee_id number;
53 --
54 begin
55 --
56 SELECT nvl(poac.employee_id,porh.preparer_id)
57 INTO l_employee_id
58 FROM po_action_history poac,
59 po_requisition_headers porh
60 WHERE porh.requisition_header_id = p_requisition_header_id
61 AND porh.requisition_header_id = poac.object_id(+)
62 AND poac.object_type_code(+) = 'REQUISITION'
63 AND poac.action_code is null;
64 --
65 return(l_employee_id);
66 exception
67 when NO_DATA_FOUND then
68 return(null);
69 --
70 end GetCurrentOwner;
71 --
72 --
73 function GetApprover( p_requisition_header_id in number,
74 p_employee_id in number,
75 p_approval_path_id in number,
76 p_forwarding_mode_code in varchar2 ) return number is
77 --
78 l_approver_id number;
79 --
80 begin
81 if ( p_forwarding_mode_code = 'DIRECT' ) then
82 --
83 l_approver_id := GetApproverDirect( p_requisition_header_id,p_employee_id, p_approval_path_id);
84 --
85 elsif ( p_forwarding_mode_code = 'HIERARCHY' ) then
86 --
87 l_approver_id := GetApproverHier(p_employee_id,p_approval_path_id);
88 --
89 end if;
90 --
91 return(l_approver_id);
92 --
93 end GetApprover;
94 --
95 --
96 Function GetApproverDirect( p_requisition_header_id in number,
97 p_employee_id in number,
98 p_approval_path_id in number ) return number is
99 --
100 cursor GetApproverPos( b_employee_id number, b_approval_path_id number ) is
101 --
102 SELECT poeh.superior_id forward_to_id
103 FROM hr_employees_current_v hrec,
104 po_employee_hierarchies poeh
105 WHERE poeh.position_structure_id = b_approval_path_id
106 AND poeh.employee_id = b_employee_id
107 AND hrec.employee_id = poeh.superior_id
108 AND poeh.superior_level > 0
109 ORDER
110 BY poeh.superior_level, hrec.full_name;
111 --
112 cursor GetApproverSup( b_employee_id number ) is
113 --
114 SELECT pera.supervisor_id forward_to_id
115 FROM per_assignments_f pera
116 WHERE pera.business_group_id = fsp.business_group_id
117 AND TRUNC(sysdate) BETWEEN pera.effective_start_date
118 AND pera.effective_end_date
119 CONNECT
120 BY pera.person_id = prior pera.supervisor_id
121 START
122 WITH pera.person_id = b_employee_id;
123 --
124 begin
125 --
126 if ( fsp.use_positions_flag = 'Y' ) then
127 --
128 for pos_rec in GetApproverPos(p_employee_id,p_approval_path_id) loop
129 --
130 if (VerifyAuthority(p_requisition_header_id,pos_rec.forward_to_id)) then
131 --
132 return(pos_rec.forward_to_id);
133 --
134 else
135 null;
136 end if;
137 --
138 end loop;
139 else
140 for sup_rec in GetApproverSup( p_employee_id ) loop
141 --
142 if (VerifyAuthority(p_requisition_header_id,sup_rec.forward_to_id) )then
143 --
144 return(sup_rec.forward_to_id);
145 --
146 else
147 null;
148 end if;
149 --
150 end loop;
151 end if;
152 --
153 return(null);
154 --
155 end GetApproverDirect;
156 --
157 function GetApproverHier ( p_employee_id in number , p_approval_path_id in number ) return number is
158 --
159 l_approver_id number;
160 --
161 cursor GetApproverPos( b_employee_id number, b_approval_path_id number ) is
162 --
163 SELECT poeh.superior_id
164 FROM po_employee_hierarchies poeh,
165 hr_employees_current_v hremp
166 WHERE poeh.employee_id = b_employee_id
167 AND poeh.position_structure_id = b_approval_path_id
168 AND poeh.superior_level = 1
169 AND hremp.employee_id = poeh.superior_id
170 ORDER
171 BY hremp.full_name;
172 --
173 cursor GetApproverSup ( b_employee_id number ) is
174 --
175 SELECT hremp.supervisor_id
176 FROM hr_employees_current_v hremp
177 WHERE hremp.employee_id = b_employee_id;
178 --
179 begin
180 --
181 if ( fsp.use_positions_flag = 'Y' ) then
182 --
183 open GetApproverPos ( b_employee_id => p_employee_id,
184 b_approval_path_id => p_approval_path_id );
185 --
186 fetch GetApproverPos into l_approver_id;
187 close GetApproverPos;
188 --
189 else
190 --
191 open GetApproverSup ( b_employee_id => p_employee_id );
192 fetch GetApproverSup into l_approver_id;
193 close GetApproverSup;
194 --
195 end if;
196 --
197 return(l_approver_id);
198 --
199 end GetApproverHier;
200 --
201 --
202 function VerifyAuthority( p_requisition_header_id in number,
203 p_employee_id in number ) return boolean is
204 --
205 l_control_function_id number;
206 l_position_id number;
207 l_job_id number;
208 l_doc_approval_limit number;
209 l_ReqInfo ReqInfoType;
210 --
211 begin
212 --
213 GetReqInfo(p_requisition_header_id,l_ReqInfo);
214 --
215 SELECT pocf.control_function_id
216 INTO l_control_function_id
217 FROM po_control_functions pocf
218 WHERE pocf.document_type_code = 'REQUISITION'
219 AND pocf.document_subtype = l_reqinfo.sub_type
220 AND pocf.action_type_code = 'APPROVE'
221 AND pocf.enabled_flag = 'Y';
222 --
223 if ( fsp.use_positions_flag = 'Y' ) then
224 --
225 SELECT nvl(PAF.position_id,0)
226 INTO l_position_id
227 FROM per_assignments_f paf
228 WHERE paf.person_id = p_employee_id
229 AND paf.assignment_type = 'E'
230 AND paf.primary_flag = 'Y'
231 AND sysdate BETWEEN paf.effective_start_date AND paf.effective_end_date;
232 --
233 --
234 if ( l_position_id = 0 ) then
235 --
236 return(FALSE);
237 end if;
238 --
239 --
240 SELECT Min(pocr.amount_limit)
241 INTO l_doc_approval_limit
242 FROM po_control_rules pocr,
243 po_control_groups pocg,
244 po_position_controls popc
245 WHERE popc.position_id = l_position_id
246 AND sysdate BETWEEN NVL(popc.start_date, sysdate - 1 )
247 AND NVL(popc.end_date,sysdate + 1 )
248 AND popc.control_function_id = l_control_function_id
249 AND pocg.enabled_flag = 'Y'
250 AND pocg.control_group_id = popc.control_group_id
251 AND pocr.control_group_id = pocg.control_group_id
252 AND pocr.object_code = 'DOCUMENT_TOTAL'
253 AND NVL(pocr.inactive_date, sysdate+1) > sysdate;
254 --
255 --
256 else -- Not using positions
257 --
258 SELECT nvl(paf.job_id, 0)
259 INTO l_job_id
260 FROM per_assignments_f PAF
261 WHERE paf.person_id = p_employee_id
262 AND paf.assignment_type = 'E'
263 AND paf.primary_flag = 'Y'
264 AND sysdate BETWEEN paf.effective_start_date AND paf.effective_end_date;
265 --
266 if ( l_job_id = 0 ) then
267 --
268 return(FALSE);
269 --
270 end if;
271 --
272 SELECT Min(pocr.amount_limit)
273 INTO l_doc_approval_limit
274 FROM po_control_rules pocr,
275 po_control_groups pocg,
276 po_position_controls popc
277 WHERE popc.job_id = l_job_id
278 AND sysdate BETWEEN NVL(popc.start_date, sysdate - 1 )
279 AND NVL(popc.end_date,sysdate + 1 )
280 AND popc.control_function_id = l_control_function_id
281 AND pocg.enabled_flag = 'Y'
282 AND pocg.control_group_id = popc.control_group_id
283 AND pocr.control_group_id = pocg.control_group_id
284 AND pocr.object_code = 'DOCUMENT_TOTAL'
285 AND NVL(pocr.inactive_date, sysdate+1) > sysdate;
286 --
287 --
288 end if;
289 --
290 if l_doc_approval_limit >= pogot_s.get_total('E',P_requisition_header_id,TRUE) then
291 --
292 return(TRUE);
293 --
294 else
295 --
296 return(FALSE);
297 --
298 end if;
299 --
300 exception
301 when NO_DATA_FOUND then
302 return(FALSE);
303 --
304 end VerifyAuthority;
305 --
306 begin -- initialize section
307 --
308 select *
309 into fsp
310 from financials_system_parameters;
311 --
312 select currency_code
313 into poreq.functional_currency
314 from gl_sets_of_books
315 where set_of_books_id = fsp.set_of_books_id;
316 --
317 end poreq;