[Home] [Help]
PACKAGE BODY: APPS.PO_CLM_CAR_UTIL
Source
1 PACKAGE BODY PO_CLM_CAR_UTIL AS
2 /*$Header: POCARUTB.pls 120.7.12020000.4 2013/03/22 11:31:15 smvinod ship $*/
3 PROCEDURE sync_clm_car
4 (
5 p_api_version IN NUMBER,
6 p_car_id IN NUMBER,
7 p_po_header_id IN NUMBER,
8 p_po_draft_id IN NUMBER,
9 p_award_idv_type IN VARCHAR2,
10 p_car_status IN VARCHAR2,
11 p_car_description IN VARCHAR2,
12 p_car_xml IN CLOB,
13 p_commit IN VARCHAR2,
14 p_validation_level IN VARCHAR2,
15 x_car_id OUT NOCOPY NUMBER,
16 x_return_status OUT NOCOPY VARCHAR2,
17 x_error_msg OUT NOCOPY VARCHAR2
18 ) IS
19 l_clm_car_row PO_CLM_CAR_REC;
20 BEGIN
21 l_clm_car_row := new PO_CLM_CAR_REC(p_car_id, p_po_header_id, p_po_draft_id, NULL, p_award_idv_type, p_car_status, NULL, p_car_description, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, SYSDATE, p_car_xml);
22 sync_clm_car(p_api_version => p_api_version,
23 p_clm_car_row => l_clm_car_row,
24 p_commit => p_commit,
25 p_validation_level => p_validation_level,
26 x_car_id => x_car_id,
27 x_return_status => x_return_status,
28 x_error_msg => x_error_msg);
29 END;
30
31 PROCEDURE sync_clm_car
32 (
33 p_api_version IN NUMBER,
34 p_clm_car_row IN PO_CLM_CAR_REC,
35 p_commit IN VARCHAR2,
36 p_validation_level IN VARCHAR2,
37 x_car_id OUT NOCOPY NUMBER,
38 x_return_status OUT NOCOPY VARCHAR2,
39 x_error_msg OUT NOCOPY VARCHAR2
40 ) IS
41 l_car_id NUMBER;
42 l_po_draft_id NUMBER;
43 l_reporting_method VARCHAR2(30) := 'SINGLE_CAR';
44 l_org_id NUMBER; --CLM Controls Project Changes
45 CURSOR c_dup (c_id NUMBER, p_h_id NUMBER, p_d_id NUMBER) IS
46 SELECT * FROM PO_CLM_CARS WHERE po_header_id = p_h_id AND po_draft_id = p_d_id AND car_id <> c_id AND reporting_method = 'SINGLE_CAR';
47 BEGIN
48 IF p_clm_car_row IS NOT NULL THEN
49 l_car_id := p_clm_car_row.car_id;
50 IF l_car_id IS NULL THEN
51 l_car_id := PO_CLM_CARS_S.NEXTVAL;
52 END IF;
53
54 l_po_draft_id := p_clm_car_row.po_draft_id;
55 IF l_po_draft_id IS NULL THEN
56 l_po_draft_id := -1;
57 END IF;
58 --DBMS_OUTPUT.PUT_LINE(l_car_id);
59 --DBMS_OUTPUT.PUT_LINE(p_clm_car_row.po_header_id);
60 --DBMS_OUTPUT.PUT_LINE(l_po_draft_id);
61 --default the reporting method of mod to its base award's reporting method
62 IF l_po_draft_id <> -1 THEN
63 SELECT DISTINCT REPORTING_METHOD
64 INTO l_reporting_method
65 FROM PO_CLM_CARS
66 WHERE PO_HEADER_ID = p_clm_car_row.po_header_id
67 AND PO_DRAFT_ID = -1;
68 END IF;
69
70 --CLM Controls Project Changes
71 BEGIN
72 SELECT ORG_ID
73 INTO l_org_id
74 FROM PO_HEADERS_MERGE_V
75 WHERE PO_HEADER_ID = p_clm_car_row.po_header_id
76 AND DRAFT_ID = l_po_draft_id;
77 EXCEPTION
78 WHEN No_Data_Found THEN
79 l_org_id := NULL;
80 END;
81 OPEN c_dup(l_car_id, p_clm_car_row.po_header_id, l_po_draft_id);
82 IF c_dup%FOUND THEN
83 RAISE e_single_car_violation;
84 END IF;
85 CLOSE c_dup;
86
87 MERGE INTO PO_CLM_CARS c using DUAL on (c.car_id = l_car_id AND
88 c.po_header_id = p_clm_car_row.po_header_id AND
89 c.po_draft_id = l_po_draft_id)
90 WHEN NOT MATCHED THEN INSERT (c.car_id,
91 c.po_header_id,
92 c.po_draft_id,
93 c.report_type,
94 c.award_idv_type,
95 c.car_status,
96 c.car_number,
97 c.car_description,
98 c.piid,
99 c.modification_segment,
100 c.reporting_method,
101 c.approved_without_reporting,
102 c.rel_without_rpt_reason,
103 c.exemption_reason,
104 c.transaction_number,
105 c.agency_id,
106 c.contracting_office_agency_id,
107 c.prepared_user,
108 c.date_signed,
109 c.car_xml,
110 c.last_update_date,
111 c.last_updated_by,
112 c.creation_date,
113 c.created_by,
114 c.last_update_login)
115 VALUES (l_car_id,
116 p_clm_car_row.po_header_id,
117 l_po_draft_id,
118 NVL(p_clm_car_row.report_type, get_report_type(p_po_header_id => p_clm_car_row.po_header_id)),
119 p_clm_car_row.award_idv_type,
120 NVL(p_clm_car_row.car_status, 'DRAFT'),
121 NVL(p_clm_car_row.car_number, get_car_number(p_po_header_id => p_clm_car_row.po_header_id,
122 p_draft_id => l_po_draft_id)),
123 p_clm_car_row.car_description,
124 NVL(p_clm_car_row.piid, get_PIID(p_po_header_id => p_clm_car_row.po_header_id,
125 p_draft_id => l_po_draft_id)),
126 NVL(p_clm_car_row.modification_segment, get_modification_segment(p_po_header_id => p_clm_car_row.po_header_id,
127 p_draft_id => l_po_draft_id)),
128 NVL(p_clm_car_row.reporting_method, l_reporting_method),
129 p_clm_car_row.approved_without_reporting,
130 p_clm_car_row.rel_without_rpt_reason,
131 p_clm_car_row.exemption_reason,
132 NVL(p_clm_car_row.transaction_number, 0),
133 NVL(p_clm_car_row.agency_id, get_agency_id(l_org_id)), --CLM Controls Project Changes
134 NVL(p_clm_car_row.contracting_office_agency_id, get_contr_office_agency_id(l_org_id)), --CLM Controls Project Changes
135 NVL(p_clm_car_row.prepared_user, get_prepared_user()),
136 p_clm_car_row.date_signed,
137 p_clm_car_row.car_xml,
138 sysdate,
139 fnd_global.user_id,
140 sysdate,
141 fnd_global.user_id,
142 fnd_global.login_id)
143 WHEN MATCHED THEN UPDATE SET c.report_type = NVL(p_clm_car_row.report_type, c.report_type),
144 c.award_idv_type = NVL(p_clm_car_row.award_idv_type, c.award_idv_type),
145 c.car_status = NVL(p_clm_car_row.car_status, c.car_status),
146 c.car_number = NVL(p_clm_car_row.car_number,
147 DECODE(c.car_status, 'NOT_REPORTED', get_car_number(p_po_header_id => c.po_header_id,
148 p_draft_id => c.po_draft_id), c.car_number)), -- if car is not reported, we refresh car number
149 c.car_description = NVL(p_clm_car_row.car_description, c.car_description),
150 c.piid = NVL(p_clm_car_row.piid, DECODE(c.car_status, 'NOT_REPORTED', get_PIID(p_po_header_id => c.po_header_id,
151 p_draft_id => c.po_draft_id), c.piid)), -- if car is not reported, we refresh piid
152 c.modification_segment = NVL(p_clm_car_row.modification_segment,
153 DECODE(c.car_status, 'NOT_REPORTED', get_modification_segment(p_po_header_id => c.po_header_id,
154 p_draft_id => c.po_draft_id), c.modification_segment)), -- if car is not reported, we refresh mod num
155 c.reporting_method = NVL(p_clm_car_row.reporting_method, 'SINGLE_CAR'), --Always set SINGLE_CAR for reporting method
156 c.approved_without_reporting = NVL(p_clm_car_row.approved_without_reporting, c.approved_without_reporting),
157 c.rel_without_rpt_reason = NVL(p_clm_car_row.rel_without_rpt_reason, c.rel_without_rpt_reason),
158 c.exemption_reason = NVL(p_clm_car_row.exemption_reason, c.exemption_reason),
159 c.transaction_number = NVL(p_clm_car_row.transaction_number, c.transaction_number),
160 c.agency_id = NVL(p_clm_car_row.agency_id, c.agency_id),
161 c.contracting_office_agency_id = NVL(p_clm_car_row.contracting_office_agency_id, c.contracting_office_agency_id),
162 c.prepared_user = NVL(p_clm_car_row.prepared_user, c.prepared_user),
163 c.date_signed = NVL(p_clm_car_row.date_signed, c.date_signed),
164 c.car_xml = NVL(p_clm_car_row.car_xml, c.car_xml),
165 c.last_update_date = sysdate,
166 c.last_updated_by = fnd_global.user_id,
167 c.creation_date = sysdate,
168 c.created_by = fnd_global.user_id,
169 c.last_update_login = fnd_global.login_id;
170 IF p_commit = 'T' THEN
171 COMMIT;
172 END IF;
173 x_car_id := l_car_id;
174 x_return_status := 'S';
175 END IF;
176
177 EXCEPTION
178 WHEN e_single_car_violation THEN
179 x_return_status := 'E';
180 x_error_msg := 'Cannot create more than one CAR for Single CAR reporting method. ';
181 WHEN OTHERS THEN
182 x_return_status := 'E';
183 x_error_msg := 'Cannot sync CAR: ' || dbms_utility.format_error_backtrace;
184 --DBMS_OUTPUT.PUT_LINE(dbms_utility.format_error_backtrace);
185 END;
186
187 PROCEDURE link_car
188 (
189 p_api_version IN NUMBER,
190 p_car_id IN NUMBER,
191 p_po_header_id IN NUMBER,
192 p_po_draft_id IN NUMBER,
193 p_award_idv_type IN VARCHAR2,
194 p_car_status IN VARCHAR2,
195 p_car_description IN VARCHAR2,
196 p_commit IN VARCHAR2,
197 p_validation_level IN VARCHAR2,
198 x_car_id OUT NOCOPY NUMBER,
199 x_return_status OUT NOCOPY VARCHAR2,
200 x_error_msg OUT NOCOPY VARCHAR2
201 ) IS
202 l_car_rec PO_CLM_CARS%ROWTYPE;
203 CURSOR c_car IS SELECT *
204 FROM PO_CLM_CARS
205 WHERE PO_HEADER_ID = p_po_header_id
206 AND PO_DRAFT_ID = NVL(p_po_draft_id, -1) FOR UPDATE;
207 BEGIN
208 OPEN c_car;
209 FETCH c_car INTO l_car_rec;
210 IF c_car%FOUND THEN
211 IF l_car_rec.CAR_STATUS = 'NOT_REPORTED' THEN
212 x_car_id := l_car_rec.CAR_ID;
213 UPDATE PO_CLM_CARS
214 SET CAR_STATUS = NVL(p_car_status, l_car_rec.car_status),
215 CAR_DESCRIPTION = NVL(p_car_description, l_car_rec.car_description)
216 WHERE CURRENT OF c_car;
217 x_return_status := 'S';
218 IF p_commit = 'T' THEN
219 COMMIT;
220 END IF;
221 ELSE
222 RAISE e_existing_car_in_clm;
223 END IF;
224 ELSE
225 sync_clm_car(p_api_version => 1.0,
226 p_car_id => p_car_id,
227 p_po_header_id => p_po_header_id,
228 p_po_draft_id => p_po_draft_id,
229 p_award_idv_type => p_award_idv_type,
230 p_car_status => NVL(p_car_status, 'NOT_REPORTED'),
231 p_car_description => p_car_description,
232 p_car_xml => NULL,
233 p_commit => p_commit,
234 p_validation_level => p_validation_level,
235 x_car_id => x_car_id,
236 x_return_status => x_return_status,
237 x_error_msg => x_error_msg);
238 END IF;
239 CLOSE c_car;
240
241 EXCEPTION
242 WHEN e_existing_car_in_clm THEN
243 x_return_status := 'E';
244 x_error_msg := 'Cannot link the CAR because the car has been existed in CLM CAR table.';
245 WHEN OTHERS THEN
246 x_return_status := 'E';
247 x_error_msg := 'Cannot link the CAR: ' || dbms_utility.format_error_backtrace;
248 END;
249
250 PROCEDURE delete_clm_car
251 (
252 p_api_version IN NUMBER,
253 p_car_id IN NUMBER,
254 p_po_header_id IN NUMBER,
255 p_po_draft_id IN NUMBER,
256 p_commit IN VARCHAR2,
257 p_validation_level IN VARCHAR2,
258 x_return_status OUT NOCOPY VARCHAR2,
259 x_error_msg OUT NOCOPY VARCHAR2
260
261 ) IS
262
263 BEGIN
264 -- delete the car
265 DELETE FROM PO_CLM_CARS
266 WHERE car_id = p_car_id
267 AND po_header_id = p_po_header_id
268 AND po_draft_id = NVL(p_po_draft_id, -1);
269
270 --delete the logs of the car
271 DELETE FROM PO_CLM_CAR_LOGS
272 WHERE car_id = p_car_id
273 AND po_header_id = p_po_header_id
274 AND po_draft_id = NVL(p_po_draft_id, -1);
275
276 IF p_commit = 'T' THEN
277 COMMIT;
278 END IF;
279 x_return_status := 'S';
280
281 EXCEPTION
282 WHEN OTHERS THEN
283 x_return_status := 'E';
284 x_error_msg := 'Cannot delete CAR: ' || dbms_utility.format_error_backtrace;
285 --DBMS_OUTPUT.PUT_LINE(dbms_utility.format_error_backtrace);
286 END;
287
288 PROCEDURE log_clm_car_messages
289 (
290 p_api_version IN NUMBER,
291 p_car_id IN NUMBER,
292 p_po_header_id IN NUMBER,
293 p_po_draft_id IN NUMBER,
294 p_clm_car_log_tab IN PO_CLM_CAR_LOG_TAB,
295 p_overwrite IN VARCHAR2,
296 p_commit IN VARCHAR2,
297 p_validation_level IN VARCHAR2,
298 x_return_status OUT NOCOPY VARCHAR2,
299 x_error_msg OUT NOCOPY VARCHAR2
300 ) IS
301
302 BEGIN
303 IF p_overwrite = 'T' THEN
304 --we don't keep the old log for the car
305 DELETE FROM PO_CLM_CAR_LOGS
306 WHERE car_id = p_car_id
307 AND po_header_id = p_po_header_id
308 AND po_draft_id = NVL(p_po_draft_id, -1);
309 END IF;
310
311 IF p_clm_car_log_tab IS NOT NULL AND p_clm_car_log_tab.COUNT > 0 THEN
312 FOR i in 1 .. p_clm_car_log_tab.COUNT
313 LOOP
314 INSERT INTO PO_CLM_CAR_LOGS ( car_id,
315 po_header_id,
316 po_draft_id,
317 source_transaction,
318 module,
319 message_level,
320 message_text,
321 last_update_date,
322 last_updated_by,
323 creation_date,
324 created_by,
325 last_update_login,
326 message_code
327 )
328 VALUES( p_clm_car_log_tab(i).car_id,
329 p_clm_car_log_tab(i).po_header_id,
330 NVL(p_clm_car_log_tab(i).po_draft_id, -1),
331 p_clm_car_log_tab(i).source_transaction,
332 p_clm_car_log_tab(i).module,
333 p_clm_car_log_tab(i).message_level,
334 p_clm_car_log_tab(i).message_text,
335 sysdate,
336 fnd_global.user_id,
337 sysdate,
338 fnd_global.user_id,
339 fnd_global.login_id,
340 p_clm_car_log_tab(i).message_code);
341 END LOOP;
342 END IF;
343
344 IF p_commit = 'T' THEN
345 COMMIT;
346 END IF;
347 x_return_status := 'S';
348
349 EXCEPTION
350 WHEN NO_DATA_FOUND THEN
351 x_return_status := 'E';
352 x_error_msg := 'No CAR ID was found. With ' ||
353 'PO HEADER ID: ' || p_po_header_id ||
354 ', PO DRAFT ID: ' || NVL(p_po_draft_id, -1) ||
355 'Cannot log CAR messages: ' || dbms_utility.format_error_backtrace;
356 --DBMS_OUTPUT.PUT_LINE(x_error_msg);
357 WHEN OTHERS THEN
358 x_return_status := 'E';
359 x_error_msg := 'With CAR ID: ' || p_car_id ||
360 ', PO HEADER ID: ' || p_po_header_id ||
361 ', PO DRAFT ID: ' || NVL(p_po_draft_id, -1) ||
362 'Cannot log CAR messages: ' || dbms_utility.format_error_backtrace;
363 --DBMS_OUTPUT.PUT_LINE(x_error_msg);
364 END;
365
366 PROCEDURE check_doc_number (
367 p_api_version IN NUMBER,
368 p_car_id IN NUMBER,
369 p_po_header_id IN NUMBER,
370 p_po_draft_id IN NUMBER,
371 p_validation_level IN VARCHAR2,
372 x_is_matching OUT NOCOPY VARCHAR2,
373 x_return_status OUT NOCOPY VARCHAR2,
374 x_error_msg OUT NOCOPY VARCHAR2
375 ) IS
376 l_piid_from_tab VARCHAR2(60);
377 l_mod_num_from_tab VARCHAR2(60);
378 l_piid_from_api VARCHAR2(60);
379 l_mod_num_from_api VARCHAR2(60);
380 BEGIN
381 IF p_car_id IS NOT NULL THEN
382 SELECT PIID, MODIFICATION_SEGMENT
383 INTO l_piid_from_tab, l_mod_num_from_tab
384 FROM PO_CLM_CARS
385 WHERE CAR_ID = p_car_id
386 AND PO_HEADER_ID = p_po_header_id
387 AND PO_DRAFT_ID = NVL(p_po_draft_id, -1);
388 ELSE
389 SELECT PIID, MODIFICATION_SEGMENT
390 INTO l_piid_from_tab, l_mod_num_from_tab
391 FROM PO_CLM_CARS
392 WHERE PO_HEADER_ID = p_po_header_id
393 AND PO_DRAFT_ID = NVL(p_po_draft_id, -1);
394 END IF;
395
396 l_piid_from_api := get_PIID(p_po_header_id => p_po_header_id,
397 p_draft_id => NVL(p_po_draft_id, -1));
398
399 l_mod_num_from_api := get_modification_segment(p_po_header_id => p_po_header_id,
400 p_draft_id => NVL(p_po_draft_id, -1));
401
402 IF((p_po_draft_id IS NULL OR p_po_draft_id = -1) AND
403 l_piid_from_tab = l_piid_from_api) THEN
404 x_is_matching := 'T';
405 ELSIF (l_piid_from_tab = l_piid_from_api AND
406 l_mod_num_from_tab = l_mod_num_from_api) THEN
407 x_is_matching := 'T';
408 ELSE
409 x_is_matching := 'F';
410 END IF;
411
412 x_return_status := 'S';
413
414 EXCEPTION
415 WHEN NO_DATA_FOUND THEN
416 x_is_matching := 'F';
417 x_return_status := 'E';
418 x_error_msg := 'No CAR record was found. With ' ||
419 ' CAR ID: ' || p_car_id ||
420 ', PO HEADER ID: ' || p_po_header_id ||
421 ', PO DRAFT ID: ' || NVL(p_po_draft_id, -1);
422 WHEN OTHERS THEN
423 x_is_matching := 'F';
424 x_return_status := 'E';
425 x_error_msg := 'Error in checking document number: ' || dbms_utility.format_error_backtrace;
426 END;
427
428 PROCEDURE get_referenced_IDV_info(
429 p_api_version IN NUMBER,
430 p_po_header_id IN NUMBER,
431 p_validation_level IN VARCHAR2,
432 x_ref_idv_agency_id OUT NOCOPY VARCHAR2,
433 x_ref_idv_piid OUT NOCOPY VARCHAR2,
434 x_return_status OUT NOCOPY VARCHAR2,
435 x_error_msg OUT NOCOPY VARCHAR2
436 ) IS
437 l_ref_idv_id NUMBER;
438 BEGIN
439 SELECT CLM_SOURCE_DOCUMENT_ID
440 INTO l_ref_idv_id
441 FROM PO_HEADERS_ALL
442 WHERE PO_HEADER_ID = p_po_header_id;
443
444 IF l_ref_idv_id IS NOT NULL THEN
445 BEGIN
446 SELECT AGENCY_ID
447 INTO x_ref_idv_agency_id
448 FROM PO_CLM_CARS
449 WHERE PO_HEADER_ID = l_ref_idv_id
450 AND PO_DRAFT_ID = -1;
451 EXCEPTION
452 WHEN NO_DATA_FOUND THEN
453 x_ref_idv_agency_id := NULL;
454 END;
455 x_ref_idv_piid := get_PIID( p_po_header_id => l_ref_idv_id,
456 p_draft_id => -1);
457 --Bug 13801428
458 ELSE
459
460 SELECT CLM_EXTERNAL_IDV
461 INTO x_ref_idv_piid
462 FROM PO_HEADERS_ALL
463 WHERE PO_HEADER_ID = p_po_header_id;
464
465 END IF;
466
467 x_return_status := 'S';
468
469 EXCEPTION
470 WHEN NO_DATA_FOUND THEN
471 x_return_status := 'E';
472 x_error_msg := 'No PO HEADER record was found. With ' ||
473 ', PO HEADER ID: ' || p_po_header_id;
474 END;
475
476 FUNCTION get_car_status(
477 p_car_id IN NUMBER,
478 p_po_header_id IN NUMBER,
479 p_po_draft_id IN NUMBER
480 ) RETURN VARCHAR2 IS
481 l_car_status VARCHAR2(60);
482 BEGIN
483 SELECT CAR_STATUS
484 INTO l_car_status
485 FROM PO_CLM_CARS
486 WHERE CAR_ID = p_car_id
487 AND PO_HEADER_ID = p_po_header_id
488 AND PO_DRAFT_ID = p_po_draft_id;
489 RETURN l_car_status;
490 EXCEPTION
491 WHEN NO_DATA_FOUND THEN
492 DBMS_OUTPUT.PUT_LINE('NO CAR WAS FOUND WITH CAR_ID = ' || p_car_id || ', PO_HEADER_ID = ' || p_po_header_id || ', PO_DRAFT_ID = ' || p_po_draft_id || '.');
493 WHEN OTHERS THEN
494 DBMS_OUTPUT.PUT_LINE(dbms_utility.format_error_backtrace);
495 END;
496
497 FUNCTION get_report_type
498 (
499 p_po_header_id IN NUMBER
500 ) RETURN VARCHAR2 IS
501 l_type_lookup_code VARCHAR2(60);
502 l_report_type VARCHAR2(60);
503 BEGIN
504 SELECT TYPE_LOOKUP_CODE
505 INTO l_type_lookup_code
506 FROM PO_HEADERS_ALL
507 WHERE PO_HEADER_ID = p_po_header_id;
508 IF l_type_lookup_code = 'STANDARD' THEN
509 l_report_type := 'AWARD';
510 ELSIF l_type_lookup_code = 'BLANKET' THEN
511 l_report_type := 'IDV';
512 ELSIF l_type_lookup_code = 'CONTRACT' THEN
513 l_report_type := 'IDV';
514 ELSE
515 l_report_type := null;
516 END IF;
517 RETURN l_report_type;
518 EXCEPTION
519 WHEN NO_DATA_FOUND THEN
520 DBMS_OUTPUT.PUT_LINE('There is no PO header for the PO_HEADER_ID ' || p_po_header_id);
521 RETURN l_report_type;
522 WHEN OTHERS THEN
523 DBMS_OUTPUT.PUT_LINE(dbms_utility.format_error_backtrace);
524 END;
525
526 FUNCTION get_car_number
527 (
528 p_po_header_id IN NUMBER,
529 p_draft_id IN NUMBER
530 ) RETURN VARCHAR2 IS
531 l_car_number VARCHAR2(60);
532 BEGIN
533 SELECT DECODE(POH.draft_id, -1, POH.clm_document_number, POD.modification_number)
534 INTO l_car_number
535 FROM PO_DRAFTS POD, PO_HEADERS_MERGE_V POH
536 WHERE POH.DRAFT_ID = POD.DRAFT_ID(+)
537 AND POH.po_header_id = p_po_header_id
538 AND poh.draft_id = NVL(p_draft_id, -1);
539
540 RETURN l_car_number;
541 END;
542
543 FUNCTION get_PIID
544 (
545 p_po_header_id IN NUMBER,
546 p_draft_id IN NUMBER
547 ) RETURN VARCHAR2 IS
548 l_PIID VARCHAR2(60);
549 BEGIN
550 SELECT replace(CLM_DOCUMENT_NUMBER, PO_DOC_NUMBERING_PKG.GET_DELIMITER_FOR_DOC (po_header_id, draft_id), '')
551 INTO l_piid
552 from PO_HEADERS_MERGE_V
553 WHERE po_header_id = p_po_header_id
554 AND draft_id = NVL(p_draft_id, -1);
555
556 RETURN l_piid;
557 END;
558
559 FUNCTION get_modification_segment
560 (
561 p_po_header_id IN NUMBER,
562 p_draft_id IN NUMBER
563 ) RETURN VARCHAR2 IS
564 l_modification_segment VARCHAR2(60);
565 BEGIN
566 SELECT REPLACE(MODIFICATION_NUMBER, CLM_DOCUMENT_NUMBER||PO_DOC_NUMBERING_PKG.GET_DELIMITER_FOR_DOC (POH.po_header_id, POH.draft_id), '')
567 INTO l_modification_segment
568 FROM PO_DRAFTS POD, PO_HEADERS_MERGE_V POH
569 WHERE POH.DRAFT_ID = POD.DRAFT_ID(+)
570 AND POH.po_header_id = p_po_header_id
571 AND poh.draft_id = NVL(p_draft_id, -1);
572
573 IF l_modification_segment IS NULL THEN
574 l_modification_segment := '0';
575 END IF;
576
577 RETURN l_modification_segment;
578 END;
579
580 --CLM Controls Project Changes
581 --Added p_org_id as parameter
582 FUNCTION get_agency_id (p_org_id IN Number) RETURN VARCHAR2 IS
583 l_agency_id VARCHAR(60);
584 BEGIN
585 l_agency_id := PO_CORE_S.retrieveOptionValue(p_org_id => p_org_id,
586 p_option_column => PO_CORE_S.g_FPDS_AGENCY_ID_COL);
587 RETURN l_agency_id;
588 END;
589
590 --CLM Controls Project Changes
591 --Added p_org_id as parameter
592 FUNCTION get_contr_office_agency_id (p_org_id IN Number) RETURN VARCHAR2 IS
593 l_contracting_office_agency_id VARCHAR(60);
594 BEGIN
595 l_contracting_office_agency_id := PO_CORE_S.retrieveOptionValue(p_org_id => p_org_id,
596 p_option_column => PO_CORE_S.g_CONTR_OFFICE_AGENCY_COL);
597 RETURN l_contracting_office_agency_id;
598 END;
599
600 FUNCTION get_prepared_user RETURN VARCHAR2 IS
601 l_prepared_user VARCHAR2(60);
602 BEGIN
603 l_prepared_user := FND_PROFILE.VALUE('PO_CLM_FPDS_USER_EDIT_ACCT');
604 RETURN l_prepared_user;
605 END;
606
607 --CLM Controls Project Changes
608 --Added p_org_id as parameter
609 FUNCTION GET_FPDSNG_IDV_MOD_XML_SRC (p_org_id IN Number) RETURN VARCHAR2 IS
610 l_fpdsng_idv_mod_xml_src VARCHAR2(60);
611 BEGIN
612 l_fpdsng_idv_mod_xml_src := PO_CORE_S.retrieveOptionValue(p_org_id => p_org_id,
613 p_option_column => PO_CORE_S.g_FPDSNG_IDV_MOD_XML_COL);
614 RETURN l_fpdsng_idv_mod_xml_src;
615 END;
616
617 --CLM Controls Project Changes
618 --Added p_org_id as parameter
619 FUNCTION GET_FPDSNG_IDV_XML_SOURCE (p_org_id IN Number) RETURN VARCHAR2 IS
620 l_fpdsng_idv_xml_source VARCHAR2(60);
621 BEGIN
622 l_fpdsng_idv_xml_source := PO_CORE_S.retrieveOptionValue(p_org_id => p_org_id,
623 p_option_column => PO_CORE_S.g_FPDSNG_IDV_XML_COL);
624 RETURN l_fpdsng_idv_xml_source;
625 END;
626
627 --CLM Controls Project Changes
628 --Added p_org_id as parameter
629 FUNCTION GET_FPDSNG_AWD_MOD_XML_SRC (p_org_id IN Number) RETURN VARCHAR2 IS
630 l_fpdsng_awd_mod_xml_src VARCHAR2(60);
631 BEGIN
632 l_fpdsng_awd_mod_xml_src := PO_CORE_S.retrieveOptionValue(p_org_id => p_org_id,
633 p_option_column => PO_CORE_S.g_FPDSNG_AWD_MOD_XML_COL);
634 RETURN l_fpdsng_awd_mod_xml_src;
635 END;
636
637 --CLM Controls Project Changes
638 --Added p_org_id as parameter
639 FUNCTION GET_FPDSNG_AWD_XML_SOURCE (p_org_id IN Number) RETURN VARCHAR2 IS
640 l_fpdsng_awd_xml_source VARCHAR2(60);
641 BEGIN
642 l_fpdsng_awd_xml_source := PO_CORE_S.retrieveOptionValue(p_org_id => p_org_id,
643 p_option_column => PO_CORE_S.g_FPDSNG_AWD_XML_COL);
644 RETURN l_fpdsng_awd_xml_source;
645 END;
646
647 PROCEDURE reset_car_status
648 (
649 p_po_header_id IN NUMBER,
650 p_po_draft_id IN NUMBER
651 ) IS
652 BEGIN
653 UPDATE PO_CLM_CARS
654 SET CAR_STATUS = 'DRAFT'
655 WHERE PO_HEADER_ID = p_po_header_id
656 AND PO_DRAFT_ID = p_po_draft_id
657 AND CAR_STATUS = 'AUTHENTICATED';
658 END;
659
660 PROCEDURE insert_mod_default_car
661 (
662 p_po_header_id IN NUMBER,
663 p_draft_id IN NUMBER,
664 p_invoked_from IN VARCHAR2 DEFAULT 'CREATE_MOD_UI',
665 p_rel_without_rpt_reason IN VARCHAR2 DEFAULT NULL
666 ) IS
667 l_car_id NUMBER;
668 l_award_idv_type VARCHAR2(20):=null;
669 l_report_type VARCHAR2(20):= po_clm_car_util.get_report_type(p_po_header_id);
670 l_exemption_reason VARCHAR2(100):=null;
671 l_org_id NUMBER; --CLM Controls Project Changes
672 BEGIN
673
674 IF p_draft_id IS NULL OR p_draft_id = -1 THEN
675 RETURN;
676 END IF;
677
678 --CLM Controls Project Changes
679 BEGIN
680 SELECT ORG_ID
681 INTO l_org_id
682 FROM PO_HEADERS_MERGE_V
683 WHERE PO_HEADER_ID = p_po_header_id
684 AND DRAFT_ID = p_draft_id;
685 EXCEPTION
686 WHEN No_Data_Found THEN
687 l_org_id := NULL;
688 END;
689
690 IF(p_invoked_from <> 'MULTI_MOD') THEN
691 BEGIN
692 SELECT exemption_reason INTO l_exemption_reason
693 FROM po_clm_cars
694 WHERE po_header_id = p_po_header_id
695 AND po_draft_id = -1
696 AND reporting_method = 'EXEMPT';
697 EXCEPTION
698 WHEN No_Data_Found THEN
699 RETURN;
700 END;
701 ELSE
702 l_exemption_reason := NULL;--Exemption reason is null when creating Mod for Multi-Mod
703 END IF;
704
705 BEGIN
706 SELECT car_id INTO l_car_id
707 FROM po_clm_cars
708 WHERE po_header_id = p_po_header_id
709 AND po_draft_id = p_draft_id;
710 EXCEPTION
711 WHEN no_data_found THEN
712
713 select ATM.FPDS_AWARD_TYPE INTO l_award_idv_type
714 from
715 PO_CLM_TO_FPDS_AWARD_TYP_MAP ATM,
716 PO_HEADERS_MERGE_V POH
717 WHERE
718 POH.PO_HEADER_ID = p_po_header_id AND
719 POH.DRAFT_ID = p_draft_id AND
720 ATM.REPORT_TYPE(+) = l_report_type AND
721 ATM.CLM_AWARD_TYPE(+) = POH.CLM_AWARD_TYPE;
722
723 INSERT INTO po_clm_cars(
724 car_id,
725 po_header_id,
726 po_draft_id,
727 report_type,
728 award_idv_type,
729 car_status,
730 car_number,
731 car_description,
732 modification_segment,
733 reporting_method,
734 approved_without_reporting,
735 rel_without_rpt_reason,
736 contracting_office_agency_id,
737 exemption_reason,
738 transaction_number,
739 agency_id,
740 prepared_user,
741 LAST_UPDATE_DATE,
742 LAST_UPDATED_BY,
743 CREATION_DATE,
744 CREATED_BY,
745 LAST_UPDATE_LOGIN,
746 PIID)
747 VALUES(
748 PO_CLM_CARS_S.NEXTVAL,
749 p_po_header_id,
750 p_draft_id,
751 l_report_type,
752 l_award_idv_type,
753 'NOT_REPORTED',
754 po_clm_car_util.get_car_number(p_po_header_id, p_draft_id),
755 NULL,
756 po_clm_car_util.get_modification_segment(p_po_header_id, p_draft_id),
757 Decode(p_invoked_from,'MULTI_MOD','NONE','EXEMPT'),
758 Decode(p_invoked_from,'MULTI_MOD',1,NULL),
759 Decode(p_invoked_from,'MULTI_MOD',p_rel_without_rpt_reason,NULL),
760 po_clm_car_util.get_contr_office_agency_id(l_org_id), --CLM Controls Project Changes
761 l_exemption_reason,
762 '0',
763 po_clm_car_util.get_agency_id(l_org_id), --CLM Controls Project Changes
764 po_clm_car_util.get_prepared_user(),
765 SYSDATE,
766 FND_GLOBAL.user_id,
767 SYSDATE,
768 FND_GLOBAL.user_id,
769 FND_GLOBAL.login_id,
770 po_clm_car_util.get_piid(p_po_header_id, p_draft_id));
771 END;
772 END;
773
774 /*
775 PROCEDURE TEST IS
776 car_row PO_CLM_CAR_REC;
777 car_log_row PO_CLM_CAR_LOG_REC;
778 car_log_tab PO_CLM_CAR_LOG_TAB;
779 car_id NUMBER;
780 is_matching VARCHAR2(1);
781 status VARCHAR2(1);
782 error_msg VARCHAR2(1000);
783 ref_idv_agency_id VARCHAR2(60);
784 ref_idv_piid VARCHAR2(60);
785 report_type VARCHAR2(60);
786 BEGIN
787
788 car_row := new PO_CLM_CAR_REC(NULL,
789 108834,
790 NULL,
791 'AWARD',
792 'AWARD',
793 'DRAFT',
794 NULL,
795 'Test',
796 NULL,
797 NULL,
798 'SINGLE_CAR',
799 NULL,
800 NULL,
801 NULL,
802 0,
803 NULL,
804 NULL,
805 NULL,
806 NULL);
807
808
809 sync_clm_car(p_api_version => 1.0,
810 p_car_id => 10023,
811 p_po_header_id => 112420,
812 p_po_draft_id => 28786,
813 p_award_idv_type => 'F',
814 p_car_status => 'NOT_REPORTED',
815 p_car_description => NULL,
816 p_car_xml => 'XML',
817 p_commit => 'T',
818 p_validation_level => NULL,
819 x_car_id => car_id,
820 x_return_status => status,
821 x_error_msg => error_msg);
822 DBMS_OUTPUT.PUT_LINE('CAR_ID: ' || car_id);
823
824
825 car_log_tab := new PO_CLM_CAR_LOG_TAB(); */
826 --car_log_tab.EXTEND();
827 --car_log_tab(car_log_tab.LAST) := new PO_CLM_CAR_LOG_REC(10078, 106440, -1, 'TEST_TXN', 'TEST_STAGE', 'WARNING', 'TEST1!');
828 /*log_clm_car_messages(p_api_version => 1.0,
829 p_car_id => 10078,
830 p_po_header_id => 106440,
831 p_po_draft_id => -1,
832 p_clm_car_log_tab => car_log_tab,
833 p_overwrite => 'T',
834 p_commit => 'T',
835 p_validation_level => NULL,
836 x_return_status => status,
837 x_error_msg => error_msg);*/
838 /*
839 delete_clm_car( p_api_version => 1.0,
840 p_car_id => 10076,
841 p_po_header_id => 106440,
842 p_po_draft_id => -1,
843 p_commit => 'T',
844 p_validation_level => NULL,
845 x_return_status => status,
846 x_error_msg => error_msg);*/
847 /*
848 link_car(p_api_version => 1.0,
849 p_car_id => NULL,
850 p_po_header_id => 106442,
851 p_po_draft_id => -1,
852 p_award_idv_type => 'AWARD',
853 p_car_status => NULL,
854 p_car_description => 'Test',
855 p_commit => 'T',
856 p_validation_level => NULL,
857 x_car_id => car_id,
858 x_return_status => status,
859 x_error_msg => error_msg);*/
860 /*
861 check_doc_number(p_api_version => 1.0,
862 p_car_id => NULL,
863 p_po_header_id => 106442,
864 p_po_draft_id => -1,
865 p_validation_level => NULL,
866 x_is_matching => is_matching,
867 x_return_status => status,
868 x_error_msg => error_msg);*/
869 /*
870 get_referenced_IDV_info(p_api_version => 1.0,
871 p_po_header_id => 105517,
872 p_validation_level => NULL,
873 x_ref_idv_agency_id => ref_idv_agency_id,
874 x_ref_idv_piid => ref_idv_piid,
875 x_return_status => status,
876 x_error_msg => error_msg);
877 DBMS_OUTPUT.PUT_LINE(get_report_type(p_po_header_id => 106440));
878
879 DBMS_OUTPUT.PUT_LINE(ref_idv_agency_id);
880 DBMS_OUTPUT.PUT_LINE(ref_idv_piid);
881 DBMS_OUTPUT.PUT_LINE(is_matching);
882 DBMS_OUTPUT.PUT_LINE(status);
883 DBMS_OUTPUT.PUT_LINE(error_msg);
884 END;*/
885
886 ------------------------------------------------------------------------------
887 --Start of Comments
888 --Name: car_for_control_type
889 --Modifies:
890 -- po_clm_cars.
891 --Function:
892 -- This procedure is to create or updat ecar when control type exists
893 -- on Award/ Mod/ IDV
894 --Parameters:
895 --IN:
896 -- p_po_header_id -- po_header_id
897 -- p_draft_id -- Draft id
898 -- p_special_contract_type -- Clm Specail Contract Type for Awards/IDV
899 -- or Draft Sub Type for Mod.
900 --OUT:
901 -- None
902 --End of Comments
903 -------------------------------------------------------------------------------
904 PROCEDURE car_for_control_type(p_po_header_id IN NUMBER,
905 p_draft_id IN NUMBER,
906 p_special_contract_type IN VARCHAR2)
907 IS
908 l_car_id NUMBER;
909 l_award_idv_type VARCHAR2(20):=null;
910 l_report_type VARCHAR2(20);
911 l_org_id NUMBER;
912 l_modification_segment VARCHAR2(60);
913 l_PIID VARCHAR2(60);
914 l_car_number VARCHAR2(60);
915 l_update_car VARCHAr2(1);
916 l_fpds_reporting_method PO_CLM_CARS.REPORTING_METHOD%TYPE;
917 l_fpds_reason PO_CLM_CARS.REL_WITHOUT_RPT_REASON%TYPE;
918 l_exemptiom_reason PO_CLM_CARS.EXEMPTION_REASON%TYPE;
919 d_progress NUMBER;
920 d_module VARCHAR2(70) := 'po.plsql.PO_CLM_CAR_UTIL.car_for_control_type';
921 BEGIN
922 d_progress := 0;
923 IF (PO_LOG.d_proc) THEN
924 PO_LOG.proc_begin(d_module);
925 PO_LOG.proc_begin(d_module, 'p_po_header_id', p_po_header_id);
926 PO_LOG.proc_begin(d_module, 'p_draft_id', p_draft_id);
927 PO_LOG.proc_begin(d_module, 'p_special_contract_type ', p_special_contract_type);
928 END IF;
929
930 d_progress := 10;
931 -- Get reporting method and reason from global contract table as per special contract type passed
932 l_fpds_reporting_method := PO_CORE_S3.g_object_special_contract_tbl(p_special_contract_type).FPDS_REPORTING_METHOD;
933 l_fpds_reason := PO_CORE_S3.g_object_special_contract_tbl(p_special_contract_type).FPDS_REASON;
934 l_exemptiom_reason := PO_CORE_S3.g_object_special_contract_tbl(p_special_contract_type).EXEMPTION_REASON;
935
936 IF (PO_LOG.d_stmt) THEN
937 PO_LOG.stmt(d_module, d_progress, 'Reporting Method ' || l_fpds_reporting_method || ' and Reason ' || l_fpds_reason || ' exemption_reason is ' || l_exemptiom_reason );
938 END IF;
939
940 IF l_fpds_reporting_method IS NOT NULL
941 AND l_fpds_reason IS NOT NULL THEN
942 -- getting org_id
943 SELECT org_id
944 INTO l_org_id
945 FROM po_headers_merge_v
946 WHERE po_header_id = p_po_header_id
947 AND NVL(draft_id, -1) = p_draft_id;
948 -- if car exists already, then update the car. Else create new car.
949 -- Update car only if special contract type is changed.
950 BEGIN
951 d_progress := 20;
952 SELECT car_id
953 INTO l_car_id
954 FROM po_clm_cars
955 WHERE po_header_id = p_po_header_id
956 AND NVL(po_draft_id, -1) = p_draft_id;
957 IF (PO_LOG.d_stmt) THEN
958 PO_LOG.stmt(d_module, d_progress, 'Car exists car_id '|| l_car_id);
959 END IF;
960
961 -- Null out in casse values are hard coded as 'NA'
962 IF l_fpds_reason = 'NA' THEN
963 l_fpds_reason := NULL;
964 END IF;
965
966 IF l_exemptiom_reason = 'NA' THEN
967 l_exemptiom_reason := NULL;
968 END IF;
969
970 UPDATE po_clm_cars
971 SET reporting_method = l_fpds_reporting_method,
972 approved_without_reporting = DECODE( l_fpds_reporting_method,
973 'NONE', 1,
974 2),
975 rel_without_rpt_reason = l_fpds_reason,
976 exemption_reason = l_exemptiom_reason,
977 last_update_date = sysdate,
978 last_updated_by = FND_GLOBAL.user_id,
979 last_update_login = FND_GLOBAL.login_id
980 WHERE car_id = l_car_id
981 AND reporting_method <> l_fpds_reporting_method
982 AND rel_without_rpt_reason <> l_fpds_reason;
983
984 d_progress := 30;
985 IF (PO_LOG.d_stmt) THEN
986 PO_LOG.stmt(d_module, d_progress, 'Updated car ' || SQL%ROWCOUNT);
987 END IF;
988
989 EXCEPTION
990 WHEN no_data_found THEN
991 d_progress := 40;
992 IF (PO_LOG.d_stmt) THEN
993 PO_LOG.stmt(d_module, d_progress, 'Inserting new car');
994 END IF;
995 l_report_type := po_clm_car_util.get_report_type(p_po_header_id);
996 SELECT ATM.FPDS_AWARD_TYPE
997 INTO l_award_idv_type
998 FROM PO_CLM_TO_FPDS_AWARD_TYP_MAP ATM,
999 PO_HEADERS_MERGE_V POH
1000 WHERE POH.PO_HEADER_ID = p_po_header_id
1001 AND NVL(POH.DRAFT_ID,-1) = p_draft_id
1002 AND ATM.REPORT_TYPE(+) = l_report_type
1003 AND ATM.CLM_AWARD_TYPE(+) = POH.CLM_AWARD_TYPE;
1004
1005 l_car_number := po_clm_car_util.get_car_number(p_po_header_id, p_draft_id);
1006 l_modification_segment := po_clm_car_util.get_modification_segment(p_po_header_id, p_draft_id);
1007 l_PIID := po_clm_car_util.get_piid(p_po_header_id, p_draft_id);
1008 -- create new car
1009 INSERT INTO po_clm_cars(
1010 car_id,
1011 po_header_id,
1012 po_draft_id,
1013 report_type,
1014 award_idv_type,
1015 car_status,
1016 car_number,
1017 car_description,
1018 modification_segment,
1019 reporting_method,
1020 approved_without_reporting,
1021 rel_without_rpt_reason,
1022 contracting_office_agency_id,
1023 exemption_reason,
1024 transaction_number,
1025 agency_id,
1026 prepared_user,
1027 last_update_date,
1028 last_updated_by,
1029 creation_date,
1030 created_by,
1031 last_update_login,
1032 piid)
1033 VALUES(
1034 PO_CLM_CARS_S.NEXTVAL,
1035 p_po_header_id,
1036 p_draft_id,
1037 l_report_type,
1038 l_award_idv_type,
1039 'NOT_REPORTED',
1040 l_car_number,
1041 NULL,
1042 l_modification_segment,
1043 l_fpds_reporting_method,
1044 DECODE( l_fpds_reporting_method,
1045 'NONE', 1,
1046 2),
1047 l_fpds_reason,
1048 po_clm_car_util.get_contr_office_agency_id(l_org_id), --CLM Controls Project Changes
1049 l_exemptiom_reason,
1050 '0',
1051 po_clm_car_util.get_agency_id(l_org_id), --CLM Controls Project Changes
1052 po_clm_car_util.get_prepared_user(),
1053 SYSDATE,
1054 FND_GLOBAL.user_id,
1055 SYSDATE,
1056 FND_GLOBAL.user_id,
1057 FND_GLOBAL.login_id,
1058 l_PIID);
1059 END; -- End for main begin
1060 END IF; -- End if for FPDS.REPORTING_METHOD and REASON
1061
1062 IF (PO_LOG.d_proc) THEN
1063 PO_LOG.proc_end(d_module);
1064 END IF;
1065
1066 EXCEPTION
1067 WHEN OTHERS THEN
1068 IF (PO_LOG.d_exc) THEN
1069 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
1070 PO_LOG.proc_end(d_module);
1071 END IF;
1072 RAISE;
1073 END car_for_control_type;
1074
1075 END PO_CLM_CAR_UTIL;