1 PACKAGE BODY PO_HXC_INTERFACE_PVT AS
2 /* $Header: POXVIHXB.pls 120.2 2007/12/24 16:10:24 cvardia ship $*/
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'PO_HXC_INTERFACE_PVT';
5 g_log_head CONSTANT VARCHAR2(40) := 'po.plsql.' || g_pkg_name || '.';
6 g_debug_unexp BOOLEAN := PO_DEBUG.is_debug_unexp_on;
7 g_debug_stmt BOOLEAN := PO_DEBUG.is_debug_stmt_on;
8
9 -------------------------------------------------------------------------------
10 --Start of Comments
11 --Name: get_timecard_amount
12 --Pre-reqs:
13 -- None.
14 --Modifies:
15 -- None.
16 --Locks:
17 -- None.
18 --Function:
19 -- Calls an OTL API to obtain the total amount of submitted/approved
20 -- timecards associated with the given PO line.
21 --Parameters:
22 --IN:
23 --p_po_line_id
24 -- Identifier of the Standard PO line
25 --OUT:
26 --x_amount
27 -- Total timecard amount for the PO line; 0 if no contractor is associated
28 -- with the line.
29 --x_return_status
30 -- FND_API.G_RET_STS_SUCCESS if the API completed successfully.
31 -- FND_API.G_RET_STS_UNEXP_ERROR if an unexpected error occurred.
32 --End of Comments
33 -------------------------------------------------------------------------------
34 PROCEDURE get_timecard_amount (
35 p_api_version IN NUMBER,
36 x_return_status OUT NOCOPY VARCHAR2,
37 p_po_line_id IN PO_LINES_ALL.po_line_id%TYPE,
38 x_amount OUT NOCOPY NUMBER
39 ) IS
40 l_api_name CONSTANT VARCHAR2(30) := 'GET_TIMECARD_AMOUNT';
41 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
42 l_api_version CONSTANT NUMBER := 1.0;
43 l_progress VARCHAR2(3) := '000';
44 l_person_id PER_ALL_PEOPLE_F.person_id%TYPE;
45 BEGIN
46 IF g_debug_stmt THEN
47 PO_DEBUG.debug_begin(l_log_head);
48 END IF;
49
50 x_return_status := FND_API.G_RET_STS_SUCCESS;
51
52 -- Standard API initialization:
53 IF NOT FND_API.compatible_api_call (
54 p_current_version_number => l_api_version,
55 p_caller_version_number => p_api_version,
56 p_api_name => l_api_name,
57 p_pkg_name => g_pkg_name ) THEN
58 RAISE FND_API.g_exc_unexpected_error;
59 END IF;
60
61 -- Retrieve the contractor associated with this Standard PO Temp Labor line.
62 l_person_id := HR_PO_INFO.get_person_id_for_po_line (
63 p_po_line_id => p_po_line_id );
64
65 IF g_debug_stmt THEN
66 PO_DEBUG.debug_stmt(l_log_head, l_progress,
67 'Calling OTL API get_mappingvalue_sum for'
68 ||' po_line_id: '||p_po_line_id
69 ||', person_id: '||l_person_id);
70 END IF;
71
72 IF (l_person_id IS NULL) THEN
73 x_amount := 0;
74 ELSE -- l_person_id IS NOT NULL
75 -- Bug 3518004: Changed the package name from HXC_MAPPING_UTILITIES
76 -- to HXC_INTEGRATION_LAYER_V1_GRP.
77 x_amount := HXC_INTEGRATION_LAYER_V1_GRP.get_mappingvalue_sum (
78 p_bld_blk_info_type => g_bld_blk_info_type_PO,
79 p_field_name1 => g_field_AMOUNT,
80 p_field_name2 => g_field_PO_LINE_ID,
81 p_field_value2 => p_po_line_id,
82 p_status => g_status_SUBMITTED,
83 p_resource_id => l_person_id);
84
85 IF g_debug_stmt THEN
86 PO_DEBUG.debug_stmt(l_log_head, l_progress,
87 'get_mappingvalue_sum returned: '||x_amount);
88 END IF;
89 END IF;
90
91 EXCEPTION
92 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
93 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
94 IF g_debug_unexp THEN
95 PO_DEBUG.debug_exc(l_log_head, l_progress);
96 END IF;
97 WHEN OTHERS THEN
98 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
99 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
100 IF g_debug_unexp THEN
101 PO_DEBUG.debug_exc(l_log_head, l_progress);
102 END IF;
103 END get_timecard_amount;
104
105 -------------------------------------------------------------------------------
106 --Start of Comments
107 --Name: check_timecard_exists
108 --Pre-reqs:
109 -- None.
110 --Modifies:
111 -- None.
112 --Locks:
113 -- None.
114 --Function:
115 -- Calls an OTL API to check whether there are any timecards associated with
116 -- the given standard PO header or line.
117 --Parameters:
118 --IN:
119 --p_field_name
120 -- Use g_field_PO_HEADER_ID or g_field_PO_LINE_ID.
121 -- From a PLD, use the functions field_po_header_id and field_po_line_id.
122 --p_field_value
123 -- Identifier of the header/line to check
124 --p_end_date
125 -- If not NULL, we will only check for timecards whose end dates are on
126 -- p_end_date or later.
127 --OUT:
128 --x_timecard_exists
129 -- TRUE if there are timecards matching the given conditions, FALSE otherwise.
130 --x_return_status
131 -- FND_API.G_RET_STS_SUCCESS if the API completed successfully.
132 -- FND_API.G_RET_STS_UNEXP_ERROR if an unexpected error occurred.
133 --End of Comments
134 -------------------------------------------------------------------------------
135 PROCEDURE check_timecard_exists (
136 p_api_version IN NUMBER,
137 x_return_status OUT NOCOPY VARCHAR2,
138 p_field_name IN VARCHAR2,
139 p_field_value IN VARCHAR2,
140 p_end_date IN PO_LINES_ALL.expiration_date%TYPE,
141 x_timecard_exists OUT NOCOPY BOOLEAN
142 ) IS
143 l_api_name CONSTANT VARCHAR2(30) := 'CHECK_TIMECARD_EXISTS';
144 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
145 l_api_version CONSTANT NUMBER := 1.0;
146 l_progress VARCHAR2(3) := '000';
147 BEGIN
148 IF g_debug_stmt THEN
149 PO_DEBUG.debug_begin(l_log_head);
150 END IF;
151
152 x_return_status := FND_API.G_RET_STS_SUCCESS;
153
154 -- Standard API initialization:
155 IF NOT FND_API.compatible_api_call (
156 p_current_version_number => l_api_version,
157 p_caller_version_number => p_api_version,
158 p_api_name => l_api_name,
159 p_pkg_name => g_pkg_name ) THEN
160 RAISE FND_API.g_exc_unexpected_error;
161 END IF;
162
163 IF g_debug_stmt THEN
164 PO_DEBUG.debug_stmt(l_log_head, l_progress,
165 'Calling OTL API chk_mapping_exists for'
166 ||' field_name: '||p_field_name
167 ||' field_value: '||p_field_value
168 ||', end_date: '||p_end_date);
169 END IF;
170
171 -- Bug# 3569255: For the parameter 'p_retrieval_process_name', pass on the
172 -- value 'Purchasing Retrieval Process' instead of 'NONE'. Otherwise, the API
173 -- will return TRUE, even if all approved timecards have been interfaced into
174 -- PO. What we really want to check is, if there exist any submitted/approved
175 -- timecards that have NOT yet been interfaced into PO.
176
177 -- Bug 3518004: Changed the package name from HXC_MAPPING_UTILITIES
178 -- to HXC_INTEGRATION_LAYER_V1_GRP.
179 x_timecard_exists := HXC_INTEGRATION_LAYER_V1_GRP.chk_mapping_exists (
180 p_bld_blk_info_type => g_bld_blk_info_type_PO,
181 p_field_name => p_field_name,
182 p_field_value => p_field_value,
183 p_scope => g_scope_DETAIL,
184 --p_retrieval_process_name => g_retrieval_process_NONE, -- Bug# 3569255
185 p_retrieval_process_name => RCV_HXT_GRP.purchasing_retrieval_process, --'Purchasing Retrieval Process'
186 p_status => g_status_SUBMITTED,
187 p_end_date => p_end_date
188 );
189
190 IF g_debug_stmt THEN
191 IF (x_timecard_exists) THEN
192 PO_DEBUG.debug_stmt(l_log_head, l_progress,
193 'chk_mapping_exists returned true.');
194 ELSE
195 PO_DEBUG.debug_stmt(l_log_head, l_progress,
196 'chk_mapping_exists returned false.');
197 END IF;
198 END IF;
199
200 EXCEPTION
201 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
202 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
203 IF g_debug_unexp THEN
204 PO_DEBUG.debug_exc(l_log_head, l_progress);
205 END IF;
206 WHEN OTHERS THEN
207 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
208 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
209 IF g_debug_unexp THEN
210 PO_DEBUG.debug_exc(l_log_head, l_progress);
211 END IF;
212 END check_timecard_exists;
213
214 -------------------------------------------------------------------------------
215 --Start of Comments
216 --Name: field_po_header_id
217 --Pre-reqs:
218 -- None.
219 --Modifies:
220 -- None.
221 --Locks:
222 -- None.
223 --Function:
224 -- Returns the value of the g_field_PO_HEADER_ID constant. This should be used
225 -- by PLD callers, which cannot access the constants directly.
226 --End of Comments
227 -------------------------------------------------------------------------------
228 FUNCTION field_po_header_id RETURN VARCHAR2 IS
229 BEGIN
230 RETURN g_field_PO_HEADER_ID;
231 END;
232
233 -------------------------------------------------------------------------------
234 --Start of Comments
235 --Name: field_po_line_id
236 --Pre-reqs:
237 -- None.
238 --Modifies:
239 -- None.
240 --Locks:
241 -- None.
242 --Function:
243 -- Returns the value of the g_field_PO_LINE_ID constant. This should be used
244 -- by PLD callers, which cannot access the constants directly.
245 --End of Comments
246 -------------------------------------------------------------------------------
247 FUNCTION field_po_line_id RETURN VARCHAR2 IS
248 BEGIN
249 RETURN g_field_PO_LINE_ID;
250 END;
251
252 -------------------------------------------------------------------------------
253 --Start of Comments
254 --Name: check_timecard_exists
255 --Pre-reqs:
256 -- None.
257 --Modifies:
258 -- None.
259 --Locks:
260 -- None.
261 --Function:
262 -- Execute a Cursor Query on HXC Building Block table to search for timecards
263 -- against Contingent Worker and Po Line Id.
264 --Parameters:
265 --IN:
266 --po_line_id
267 -- Identifier of the Standard PO line:
268 --p_person_id
269 -- Identifier of the Contingnet worker:
270 --OUT:
271 -- 'True'/'False'
272 -- Return whether the timecard exists or not.
273 --End of Comments
274 -------------------------------------------------------------------------------
275
276 FUNCTION check_timecard_exists (
277 p_person_id IN NUMBER,
278 po_line_id IN NUMBER)
279 RETURN VARCHAR2
280 IS
281
282 CURSOR csr_chk_tc_exists_ss
283 IS
284 SELECT 'Y'
285 FROM hxc_Time_Building_Blocks htb,
286 hxc_Time_Attributes hta,
287 hxc_Time_Attribute_Usages htau
288 WHERE hta.Time_Attribute_Id = htau.Time_Attribute_Id
289 AND htau.Time_Building_Block_Id = htb.Time_Building_Block_Id
290 AND htau.Time_Building_Block_ovn = htb.Object_Version_Number
291 AND htb.Date_To = hr_General.End_Of_Time
292 AND htb.Scope = 'DETAIL'
293 AND hta.Attribute_Category = 'PURCHASING'
294 AND hta.Attribute2 = To_Char(po_line_id)
295 AND htb.Resource_Id = p_person_id
296 AND ROWNUM = 1;
297
298 l_dummy VARCHAR2 (1);
299 BEGIN
300
301
302 OPEN csr_chk_tc_exists_ss;
303 FETCH csr_chk_tc_exists_ss INTO l_dummy;
304
305 IF (csr_chk_tc_exists_ss%FOUND) THEN
306 CLOSE csr_chk_tc_exists_ss;
307 return 'true' ;
308 ELSE
309 return 'false' ;
310 END IF;
311
312 CLOSE csr_chk_tc_exists_ss;
313
314 END check_timecard_exists;
315
316 -------------------------------------------------------------------------------
317 --Start of Comments
318 --Name: get_pa_timecard_amount
319 --Pre-reqs:
320 -- None.
321 --Modifies:
322 -- None.
323 --Locks:
324 -- None.
325 --Function:
326 -- Calls an OTL API to obtain the total amount of submitted/approved
327 -- timecards associated with the given PO line, project and task
328 --Parameters:
329 --IN:
330 --p_po_line_id
331 -- Identifier of the Standard PO line:
332 --p_project_id
333 -- Identifier of the project on the PO line:
334 --p_task_id
335 -- Identifier of the task on the Standard PO line
336 --OUT:
337 --x_amount
338 -- Total timecard amount for the PO line project /task; 0 if no contractor is associated
339 -- with the line.
340 --x_return_status
341 -- FND_API.G_RET_STS_SUCCESS if the API completed successfully.
342 -- FND_API.G_RET_STS_UNEXP_ERROR if an unexpected error occurred.
343 --End of Comments
344 -------------------------------------------------------------------------------
345 PROCEDURE get_pa_timecard_amount (
346 p_api_version IN NUMBER,
347 x_return_status OUT NOCOPY VARCHAR2,
348 p_po_line_id IN PO_LINES_ALL.po_line_id%TYPE,
349 p_project_id IN PO_DISTRIBUTIONS_ALL.project_id%TYPE ,
350 p_task_id IN PO_DISTRIBUTIONS_ALL.task_id%TYPE,
351 x_amount OUT NOCOPY NUMBER
352 ) IS
353 l_api_name CONSTANT VARCHAR2(30) := 'GET_PA_TIMECARD_AMOUNT';
354 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
355 l_api_version CONSTANT NUMBER := 1.0;
356 l_progress VARCHAR2(3) := '000';
357 l_person_id PER_ALL_PEOPLE_F.person_id%TYPE;
358 BEGIN
359 IF g_debug_stmt THEN
360 PO_DEBUG.debug_begin(l_log_head);
361 END IF;
362
363 x_return_status := FND_API.G_RET_STS_SUCCESS;
364
365 -- Standard API initialization:
366 IF NOT FND_API.compatible_api_call (
367 p_current_version_number => l_api_version,
368 p_caller_version_number => p_api_version,
369 p_api_name => l_api_name,
370 p_pkg_name => g_pkg_name ) THEN
371 RAISE FND_API.g_exc_unexpected_error;
372 END IF;
373
374 -- Retrieve the contractor associated with this Standard PO Temp Labor line.
375 l_person_id := HR_PO_INFO.get_person_id_for_po_line (
376 p_po_line_id => p_po_line_id );
377
378 IF g_debug_stmt THEN
379 PO_DEBUG.debug_stmt(l_log_head, l_progress,
380 'Calling OTL API get_mappingvalue_sum for'
381 ||' po_line_id: '||p_po_line_id
382 ||' project_id: '||p_project_id
383 ||' task_id: '||p_task_id
384 ||', person_id: '||l_person_id);
385 END IF;
386
387 IF (l_person_id IS NULL) THEN
388 x_amount := 0;
389 ELSE -- l_person_id IS NOT NULL
390 -- Bug 3518004: Changed the package name from HXC_MAPPING_UTILITIES
391 -- to HXC_INTEGRATION_LAYER_V1_GRP.
392 x_amount := HXC_INTEGRATION_LAYER_V2_GRP.get_mappingvalue_sum (
393 p_bld_blk_info_type => g_bld_blk_info_type_PO,
394 p_field_name1 => g_field_AMOUNT,
395 p_bld_blk_info_type2 => g_bld_blk_info_type_PO,
396 p_field_name2 => g_field_PO_LINE_ID,
397 p_field_value2 => p_po_line_id,
398 p_bld_blk_info_type3 => g_bld_blk_info_type_PA,
399 p_field_name3 => g_field_PROJECT_ID,
400 p_field_value3 => p_project_id,
401 p_bld_blk_info_type4 => g_bld_blk_info_type_PA,
402 p_field_name4 => g_field_TASK_ID,
403 p_field_value4 => p_task_id,
404 p_status => g_status_SUBMITTED,
405 p_resource_id => l_person_id);
406
407 IF g_debug_stmt THEN
408 PO_DEBUG.debug_stmt(l_log_head, l_progress,
409 'get_mappingvalue_sum for projects returned: '||x_amount);
410 END IF;
411 END IF;
412
413 EXCEPTION
414 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
415 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
416 IF g_debug_unexp THEN
417 PO_DEBUG.debug_exc(l_log_head, l_progress);
418 END IF;
419 WHEN OTHERS THEN
420 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
421 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
422 IF g_debug_unexp THEN
423 PO_DEBUG.debug_exc(l_log_head, l_progress);
424 END IF;
425 END get_pa_timecard_amount;
426
427 END PO_HXC_INTERFACE_PVT;
[Home] [Help]