[Home] [Help]
PACKAGE BODY: APPS.PO_AME_SETUP_PVT
Source
1 PACKAGE BODY PO_AME_SETUP_PVT AS
2 /* $Header: POXAMESB.pls 120.1 2005/06/29 18:28:33 shsiung noship $*/
3
4 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 --------------------------------------------------------------------------------
7 --Start of Comments
8 --Name: get_function_currency
9 --Pre-reqs:
10 -- None.
11 --Modifies:
12 -- None.
13 --Locks:
14 -- None.
15 --Function:
16 -- Returns the function currency of requisition preparing org
17 --Parameters:
18 --IN:
19 --reqHeaderId
20 -- Requisition Header ID
21 --OUT:
22 --
23 --Testing:
24 --
25 --End of Comments
26 -------------------------------------------------------------------------------
27 FUNCTION get_function_currency(reqHeaderId IN NUMBER)
28 RETURN VARCHAR2 IS
29 l_currency_code gl_sets_of_books.currency_code%TYPE;
30
31 BEGIN
32 SELECT gls.currency_code
33 INTO l_currency_code
34 FROM financials_system_params_all fsp,
35 gl_sets_of_books gls,
36 po_requisition_headers_all prh
37 WHERE fsp.set_of_books_id = gls.set_of_books_id and
38 fsp.org_id = prh.org_id and -- <R12 MOAC>
39 prh.requisition_header_id = reqHeaderId;
40 RETURN l_currency_code;
41 EXCEPTION
42 when others then
43 raise;
44 END;
45
46 --------------------------------------------------------------------------------
47 --Start of Comments
48 --Name: get_rate_type
49 --Pre-reqs:
50 -- None.
51 --Modifies:
52 -- None.
53 --Locks:
54 -- None.
55 --Function:
56 -- Returns the default rate type of requisition preprarer.
57 --Parameters:
58 --IN:
59 --reqHeaderId
60 -- Requisition Header ID
61 --OUT:
62 --
63 --Testing:
64 --
65 --End of Comments
66 -------------------------------------------------------------------------------
67 FUNCTION get_rate_type(reqHeaderId IN NUMBER)
68 RETURN VARCHAR2 IS
69 l_user_id fnd_user.user_id%TYPE;
70 l_rate_type fnd_profile_option_values.profile_option_value%TYPE;
71
72 BEGIN
73 BEGIN
74 SELECT fu.user_id
75 INTO l_user_id
76 FROM fnd_user fu, po_requisition_headers_all prh
77 WHERE prh.requisition_header_id = reqHeaderId and
78 prh.preparer_id = fu.employee_id;
79 EXCEPTION
80 when others then
81 fnd_profile.get('POR_DEFAULT_RATE_TYPE', l_rate_type);
82 RETURN l_rate_type;
83 END;
84 l_rate_type := fnd_profile.value_specific('POR_DEFAULT_RATE_TYPE', l_user_id);
85 RETURN l_rate_type;
86 EXCEPTION
87 when others then
88 raise;
89 END;
90
91 --------------------------------------------------------------------------------
92 --Start of Comments
93 --Name: get_accounting_flex
94 --Pre-reqs:
95 -- None.
96 --Modifies:
97 -- None.
98 --Locks:
99 -- None.
100 --Function:
101 -- Returns the accounting flex segment's value for a distribution segment
102 --Parameters:
103 --IN:
104 --segmentName
105 -- Segment name
106 --distributionId
107 -- Requisition distribution ID
108 --OUT:
109 --Testing:
110 --
111 --End of Comments
112 -------------------------------------------------------------------------------
113 FUNCTION get_accounting_flex(segmentName IN VARCHAR2, distributionId IN NUMBER)
114 RETURN VARCHAR2 IS
115
116 l_segments FND_FLEX_EXT.SEGMENTARRAY;
117 l_result BOOLEAN;
118 l_chart_of_accounts_id NUMBER;
119 l_num_segments NUMBER;
120 l_segment_num NUMBER;
121 l_segment_delimiter VARCHAR2(1);
122 l_seg_val VARCHAR2(50);
123 l_ccid NUMBER;
124 l_sob NUMBER;
125
126 BEGIN
127
128 /* find set of book id and code combination id from distribution */
129
130 SELECT code_combination_id, set_of_books_id
131 INTO l_ccid, l_sob
132 FROM po_req_distributions_all
133 WHERE distribution_id= distributionId;
134
135 /* find chart of account id from set of book */
136 SELECT chart_of_accounts_id
137 INTO l_chart_of_accounts_id
138 FROM gl_sets_of_books
139 WHERE set_of_books_id = l_sob;
140
141 /* get the all the segment array */
142 l_result := FND_FLEX_EXT.GET_SEGMENTS(
143 'SQLGL',
144 'GL#',
145 l_chart_of_accounts_id,
146 l_ccid,
147 l_num_segments,
148 l_segments);
149
150 IF (NOT l_result) THEN
151 APP_EXCEPTION.RAISE_EXCEPTION;
152 END IF;
153
154 /* get the segment number for the given segment name */
155 l_result := FND_FLEX_APIS.GET_QUALIFIER_SEGNUM(
156 101,
157 'GL#',
158 l_chart_of_accounts_id,
159 segmentName,
160 l_segment_num);
161
162 IF (NOT l_result) THEN
163 APP_EXCEPTION.RAISE_EXCEPTION;
164 END IF;
165
166 l_seg_val := l_segments(l_segment_num);
167
168 RETURN l_seg_val;
169 EXCEPTION
170 when others then
171 -- TODO: log error
172 raise;
173 END;
174
175 --------------------------------------------------------------------------------
176 --Start of Comments
177 --Name: get_changed_req_total
178 --Pre-reqs:
179 -- None.
180 --Modifies:
181 -- None.
182 --Locks:
183 -- None.
184 --Function:
185 -- Returns the requisition total, which includes requester changes.
186 --Parameters:
187 --IN:
188 --reqHeaderId
189 -- Requisition Header ID
190 --OUT:
191 --Testing:
192 --
193 --End of Comments
194 -------------------------------------------------------------------------------
195 FUNCTION get_changed_req_total(ReqHeaderId IN NUMBER)
196 RETURN NUMBER IS
197 l_req_total NUMBER := 0;
198 l_org_id po_requisition_headers_all.org_id%TYPE;
199
200 BEGIN
201 select org_id
202 into l_org_id
203 from po_requisition_headers_all
204 where requisition_header_id = ReqHeaderId;
205
206 IF l_org_id is NOT NULL THEN
207 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ; -- <R12 MOAC>
208 END IF;
209
210 SELECT sum(po_calculatereqtotal_pvt.get_req_distribution_total(
211 PORL.requisition_header_id,PORL.requisition_line_id,
212 PORD.distribution_id))
213 into l_req_total
214 FROM
215 PO_REQ_DISTRIBUTIONS_ALL PORD, -- <R12 MOAC>
216 PO_REQUISITION_LINES PORL
217 WHERE PORL.requisition_header_id = ReqHeaderId
218 AND PORL.requisition_line_id = PORD.requisition_line_id
219 AND nvl(PORL.cancel_flag, 'N') = 'N'
220 AND nvl(PORL.modified_by_agent_flag, 'N') = 'N';
221
222 return l_req_total;
223 END;
224
225 --------------------------------------------------------------------------------
226 --Start of Comments
227 --Name: get_new_req_header_id
228 --Pre-reqs:
229 -- None.
230 --Modifies:
231 -- None.
232 --Locks:
233 -- None.
234 --Function:
235 -- Given a requisition header ID, this function first identifies if there a working copy for this requisition
236 -- If yes then return the working copy's requisition header ID
237 -- Otherewise, return the input requisition header ID.
238 --Parameters:
239 --IN:
240 --oldReqHeaderId
241 -- Requisition Header ID
242 --OUT:
243 --Testing:
244 --
245 --End of Comments
246 -------------------------------------------------------------------------------
247 function get_new_req_header_id (oldReqHeaderId IN NUMBER) return number IS
248
249 l_api_name varchar2(50):= 'get_new_req_header_id';
250 reqNumber po_requisition_headers.segment1%TYPE;
251 orgId po_requisition_headers.org_id%TYPE;
252 newReqHeaderId po_requisition_headers.requisition_header_id%TYPE;
253 newReqNumber po_requisition_headers.segment1%TYPE;
254
255 begin
256 select segment1, org_id into reqNumber, orgId
257 from po_requisition_headers_all
258 where requisition_header_id = oldReqHeaderId;
259
260 newReqNumber := '##' || reqNumber;
261
262 begin
263 select requisition_header_id into newReqHeaderId
264 from po_requisition_headers_all
265 where segment1 = newReqNumber
266 and org_id = orgId ; -- <R12 MOAC>
267 exception
268 when NO_DATA_FOUND then
269 return oldReqHeaderId;
270 end;
271 return newReqHeaderId;
272
273 exception
274 when others then
275 if g_fnd_debug = 'Y' then
276 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
277 FND_LOG.string(FND_LOG.level_unexpected, 'icx.plsql.PO_AME_SETUP_PVT' ||
278 l_api_name || '.others_exception', sqlerrm);
279 END IF;
280 end if;
281 raise;
282 end;
283
284 --------------------------------------------------------------------------------
285 --Start of Comments
286 --Name: is_system_approver_mandatory
287 --Pre-reqs:
288 -- None.
289 --Modifies:
290 -- None.
291 --Locks:
292 -- None.
293 --Function:
294 -- Returns value based on profile POR_SYS_GENERATED_APPROVERS_MANDATORY.
295 --Parameters:
296 --IN:
297 --reqHeaderId
298 -- Requisition Header ID
299 --OUT:
300 --
301 --Testing:
302 --
303 --End of Comments
304 -------------------------------------------------------------------------------
305 FUNCTION is_system_approver_mandatory(reqHeaderId IN NUMBER)
306 RETURN VARCHAR2 IS
307 l_user_id fnd_user.user_id%TYPE;
308 l_option_value fnd_profile_option_values.profile_option_value%TYPE;
309
310 BEGIN
311 BEGIN
312 SELECT fu.user_id
313 INTO l_user_id
314 FROM fnd_user fu, po_requisition_headers_all prh
315 WHERE prh.requisition_header_id = reqHeaderId and
316 prh.preparer_id = fu.employee_id;
317 EXCEPTION
318 when others then
319 fnd_profile.get('POR_SYS_GENERATED_APPROVERS_MANDATORY', l_option_value);
320 RETURN l_option_value;
321
322 END;
323 l_option_value := fnd_profile.value_specific('POR_SYS_GENERATED_APPROVERS_MANDATORY', l_user_id);
324
325 RETURN l_option_value;
326
327 EXCEPTION
328 when others then
329 RETURN 'Y';
330 END;
331
332 --------------------------------------------------------------------------------
333 --Start of Comments
334 --Name: can_preparer_approve
335 --Pre-reqs:
336 -- None.
337 --Modifies:
338 -- None.
339 --Locks:
340 -- None.
341 --Function:
342 -- Returns 'Y' if preparer can approve requisition;
343 -- Returns 'N' if preparer cannot approve requisition;
344 -- based on po document set up.
345 --Parameters:
346 --IN:
347 --reqHeaderId
348 -- Requisition Header ID
349 --OUT:
350 --
351 --Testing:
352 --
353 --End of Comments
354 -------------------------------------------------------------------------------
355 FUNCTION can_preparer_approve(reqHeaderId IN NUMBER)
356 RETURN VARCHAR2 IS
357 l_can_approve po_document_types_all_b.can_preparer_approve_flag%TYPE;
358
359 BEGIN
360 SELECT NVL(pdt.can_preparer_approve_flag, 'N')
361 INTO l_can_approve
362 FROM po_document_types_all_b pdt, po_requisition_headers_all prh
363 WHERE prh.requisition_header_id = reqHeaderId and
364 pdt.org_id = prh.org_id and -- <R12 MOAC>
365 prh.type_lookup_code = pdt.document_subtype and
366 pdt.DOCUMENT_TYPE_CODE='REQUISITION';
367
368 RETURN l_can_approve;
369 EXCEPTION
370 when others then
371 RETURN 'N';
372 END;
373
374
375 END PO_AME_SETUP_PVT;