[Home] [Help]
PACKAGE BODY: APPS.PO_WAGE_DETERMINATION_ONLINE
Source
1 PACKAGE BODY PO_WAGE_DETERMINATION_ONLINE AS
2 /* $Header: PO_WAGE_DETERMINATION_ONLINE.plb 120.3.12020000.6 2013/03/06 06:17:49 harchand noship $ */
3
4
5 FUNCTION get_lookup_value (p_lookup_code VARCHAR2) RETURN VARCHAR2 IS
6
7 CURSOR c_get_lookup_value IS
8 SELECT meaning
9 FROM fnd_lookups
10 WHERE lookup_type = 'PO_WAGE_DETERMINATION_STATUS'
11 AND (SYSDATE BETWEEN start_date_active AND Nvl(end_date_active,SYSDATE+1))
12 AND lookup_code = p_lookup_code ;
13
14 l_lookup_value VARCHAR2(30):= NULL ;
15
16 BEGIN
17
18 OPEN c_get_lookup_value;
22 RETURN l_lookup_value;
19 FETCH c_get_lookup_value INTO l_lookup_value;
20 CLOSE c_get_lookup_value;
21
23
24 END get_lookup_value;
25
26 PROCEDURE GET_WDOL_STATEMENT (
27 x_return_status OUT NOCOPY VARCHAR2,
28 x_msg_count OUT NOCOPY NUMBER,
29 x_msg_data OUT NOCOPY VARCHAR2,
30 p_document_type IN VARCHAR2,
31 p_document_id IN NUMBER,
32 p_draft_id IN NUMBER,
33 p_doc_type_class IN VARCHAR2,
34 p_input_URL IN VARCHAR2
35 )
36
37 IS
38
39
40
41 l_event_key number;
42 l_event_data clob;
43 l_event_name varchar2(250);
44
45 ParamList wf_parameter_list_t := wf_parameter_list_t();
46 l_param wf_parameter_t;
47 l_parameter_index NUMBER := 0;
48 l_message VARCHAR2(500);
49
50 l_attachment_pk1 VARCHAR2(50);
51 l_lookup_code VARCHAR2(30);
52
53 CURSOR c_get_WDOL_attachment IS
54 SELECT ATTACHED_DOCUMENT_ID,
55 attach.DOCUMENT_ID,
56 ENTITY_NAME,
57 PK1_VALUE,
58 PK2_VALUE,
59 PK3_VALUE,
60 PK4_VALUE,
61 PK5_VALUE,
62 AUTOMATICALLY_ADDED_FLAG,
63 attach.PROGRAM_APPLICATION_ID,
64 attach.PROGRAM_ID,
65 attach.PROGRAM_UPDATE_DATE,
66 attach.REQUEST_ID,
67 attach.CATEGORY_ID,
68 ORIG_ATTACH_DOC_ID
69 FROM fnd_attached_documents attach, fnd_documents_tl doc
70 WHERE pk1_value = l_attachment_pk1
71 AND attach.document_id = doc.document_id
72 AND doc.title = p_input_url
73 AND doc.LANGUAGE = 'US'
74 AND attach.category_id = 1;
75
76 CURSOR c_last_attachment IS
77 SELECT * from
78 (SELECT ATTACHED_DOCUMENT_ID,
79 DOCUMENT_ID
80 FROM fnd_attached_documents
81 WHERE pk1_value = To_Char(p_document_id)
82 AND Nvl(pk2_value,'-1') = Nvl(To_Char(p_draft_id),'-1')
83 ORDER BY last_update_date DESC)
84 WHERE ROWNUM=1;
85
86 x_row_id ROWID;
87 l_attachment_found VARCHAR2(1):= 'N';
88 x_attached_document_id NUMBER:=null;
89 x_document_id NUMBER:=NULL;
90
91 CURSOR c_get_max_attach_seq IS
92 SELECT Nvl(Max(seq_num),0) maxseq
93 FROM fnd_attached_documents
94 WHERE pk1_value = l_attachment_pk1;
95
96 l_seq_num number := 0;
97 l_media_id NUMBER;
98 l_lang VARCHAR2(30);
99 l_entity_name varchar2(30);
100
101
102
103
104 BEGIN
105
106 /*SELECT po_wdol_evnt_s.NEXTVAL INTO l_event_key FROM dual;
107 l_event_key := 'WDOL'||l_event_key;
108 l_event_name := 'oracle.apps.po.integration.wagedetermination';
109 l_message := wf_event.test(l_event_name);
110
111 l_event_data := gen_xml_payload(p_document_type => p_document_type,
112 p_document_id => p_document_id,
113 p_draft_id => p_draft_id,
114 p_doc_type_class=> p_doc_type_class,
115 p_supplier_name => c.supplier_name ,
116 p_vendor_site_code => c.vendor_site_code,
117 p_duns_number => c.duns_number,
118 p_tin_number => c.tin_number
119 );
120
121 wf_event.AddParameterToList( p_name => 'PO_WDOL_DOC_ID'
122 , p_value => p_document_id
123 , p_parameterList => ParamList );
124
125 wf_event.AddParameterToList( p_name => 'PO_WDOL_DOC_TYPE'
126 , p_value => p_document_type
127 , p_parameterList => ParamList );
128
129 wf_event.AddParameterToList( p_name => 'PO_WDOL_DRAFT_ID'
130 , p_value => p_draft_id
131 , p_parameterList => ParamList );
132
133 wf_event.AddParameterToList( p_name => 'PO_WDOL_DOCTYPE_CLASS'
134 , p_value => p_doc_type_class
135 , p_parameterList => ParamList );
136
137 wf_event.AddParameterToList( p_name => 'PO_WDOL_INPUT_URL'
138 , p_value => p_input_URL
139 , p_parameterList => ParamList );
140
141
142 wf_event.Raise( p_event_name => l_event_name
143 , p_event_key => l_event_key
144 , p_parameters => ParamList );
145
146 ParamList.DELETE;
147
148 COMMIT;
149 */
150
151 IF Nvl(p_draft_id,-1) = -1 THEN
152 l_attachment_pk1 := p_document_id;
153 ELSE
154 l_attachment_pk1 := p_document_id||'-'||p_draft_id;
155 END IF;
156
157 SELECT fnd_attached_documents_s.nextval
158 INTO x_attached_document_id
159 FROM dual;
160
161 open c_get_max_attach_seq;
162 fetch c_get_max_attach_seq into l_seq_num;
163 close c_get_max_attach_seq;
164
165 l_seq_num := l_seq_num + 1;
166
167 select decode(p_doc_type_class,'PO','PO_HEADERS','SOURCING','PON_AUCTION_HEADERS_ALL') into l_entity_name from dual;
168
169 fnd_attached_documents_pkg.insert_row(X_Rowid => x_row_id,
170 X_attached_document_id => x_attached_document_id,
171 X_document_id => X_document_id,
172 X_creation_date => SYSDATE,
173 X_created_by => Fnd_Global.User_Id,
174 X_last_update_date => SYSDATE,
175 X_last_updated_by => Fnd_Global.User_Id,
176 X_last_update_login => Fnd_Global.Login_Id,
177 X_seq_num => l_seq_num,
178 X_entity_name => l_entity_name,
179 X_column1 => NULL,
180 X_pk1_value => l_attachment_pk1,
181 X_pk2_value => NULL,
182 X_pk3_value => NULL,
183 X_pk4_value => NULL,
184 X_pk5_value => NULL,
185 X_automatically_added_flag => 'Y',
186 /* columns necessary for creating a document on the fly */
187 X_datatype_id => 5,
188 X_category_id => 33,
189 X_security_type => 1,
190 X_security_id => 204,
191 X_publish_flag => 'Y',
192 X_language => l_lang,
193 X_description => 'WD Statement URL',
194 X_media_id => l_media_id,
195 X_create_doc => 'Y',
196 X_url => p_input_URL,
197 X_title => 'Wage Determination Statement');
198
199 IF x_row_id is not null THEN
200
201 l_lookup_code:=get_lookup_value('COMPLETE');
202
203 INSERT INTO po_wage_determination
204 (
205 id ,
206 doc_id ,
207 doc_type ,
208 draft_id ,
209 wd_statement_url ,
210 request_date ,
211 response_date ,
212 status ,
213 error ,
214 last_update_date ,
215 last_updated_by ,
216 creation_date ,
217 created_by ,
218 last_update_login
219 )
220 VALUES (po_wage_determination_s.NEXTVAL,
221 p_document_id,
222 p_document_type,
223 p_draft_id,
224 p_input_url,
225 SYSDATE,
226 SYSDATE,
227 l_lookup_code,
228 NULL,
229 SYSDATE,
230 Fnd_Global.User_Id,
231 SYSDATE,
232 Fnd_Global.User_Id,
233 Fnd_Global.Login_Id
234 );
235
236
237
238 ELSE
239
240 l_lookup_code:=get_lookup_value('FAIL');
241
242 INSERT INTO po_wage_determination
243 (
244 id ,
245 doc_id ,
246 doc_type ,
247 draft_id ,
248 wd_statement_url ,
249 request_date ,
250 response_date ,
251 status ,
252 error ,
253 last_update_date ,
254 last_updated_by ,
255 creation_date ,
256 created_by ,
257 last_update_login
258 )
259 VALUES (po_wage_determination_s.NEXTVAL,
260 p_document_id,
261 p_document_type,
262 p_draft_id,
263 p_input_url,
264 SYSDATE,
265 SYSDATE,
266 l_lookup_code,
267 'URL Not Found',
268 SYSDATE,
269 Fnd_Global.User_Id,
270 SYSDATE,
271 Fnd_Global.User_Id,
272 Fnd_Global.Login_Id
273 );
274
275
276
277 END IF;
278
279 COMMIT;
280
281
282
283 EXCEPTION WHEN OTHERS THEN
284 NULL;
285 END GET_WDOL_STATEMENT;
286
287 FUNCTION po_wage_determination_receive(
288 p_subscription_guid IN RAW
289 ,p_event IN OUT NOCOPY wf_event_t)
290 RETURN VARCHAR2 IS
291 l_doc_id NUMBER;
292 l_doc_type VARCHAR2(30);
293 l_draft_id NUMBER;
294 l_doc_type_class VARCHAR2(30);
295 l_input_url VARCHAR2(500);
296 l_attachment_pk1 VARCHAR2(50);
297 l_lookup_code VARCHAR2(30);
298
299 CURSOR c_get_WDOL_attachment IS
300 SELECT ATTACHED_DOCUMENT_ID,
301 attach.DOCUMENT_ID,
302 ENTITY_NAME,
303 PK1_VALUE,
304 PK2_VALUE,
305 PK3_VALUE,
306 PK4_VALUE,
307 PK5_VALUE,
308 AUTOMATICALLY_ADDED_FLAG,
309 attach.PROGRAM_APPLICATION_ID,
310 attach.PROGRAM_ID,
311 attach.PROGRAM_UPDATE_DATE,
312 attach.REQUEST_ID,
313 attach.CATEGORY_ID,
314 ORIG_ATTACH_DOC_ID
315 FROM fnd_attached_documents attach, fnd_documents_tl doc
316 WHERE pk1_value = l_attachment_pk1
317 AND attach.document_id = doc.document_id
318 AND doc.title = l_input_url
319 AND doc.LANGUAGE = 'US'
323 SELECT * from
320 AND attach.category_id = 1;
321
322 CURSOR c_last_attachment IS
324 (SELECT ATTACHED_DOCUMENT_ID,
325 DOCUMENT_ID
326 FROM fnd_attached_documents
327 WHERE pk1_value = To_Char(l_doc_id)
328 AND Nvl(pk2_value,'-1') = Nvl(To_Char(l_draft_id),'-1')
329 ORDER BY last_update_date DESC)
330 WHERE ROWNUM=1;
331
332 x_row_id ROWID;
333 l_attachment_found VARCHAR2(1):= 'N';
334 x_attached_document_id NUMBER:=null;
335 x_document_id NUMBER:=NULL;
336
337 CURSOR c_get_max_attach_seq IS
338 SELECT Max(seq_num) maxseq
339 FROM fnd_attached_documents
340 WHERE pk1_value = l_attachment_pk1;
341
342 l_seq_num number := 0;
343 l_media_id NUMBER;
344 l_lang VARCHAR2(30);
345 l_entity_name varchar2(30);
346
347 BEGIN
348 --read the parameters values passed to this event
349 l_doc_id := p_event.getvalueforparameter('PO_WDOL_DOC_ID');
350 l_doc_type := p_event.getvalueforparameter('PO_WDOL_DOC_TYPE');
351 l_draft_id := p_event.getvalueforparameter('PO_WDOL_DRAFT_ID');
352 l_doc_type_class := p_event.getvalueforparameter('PO_WDOL_DOCTYPE_CLASS');
353 l_input_url := p_event.getvalueforparameter('PO_WDOL_INPUT_URL');
354 IF Nvl(l_draft_id,-1) = -1 THEN
355 l_attachment_pk1 := l_doc_id;
356 ELSE
357 l_attachment_pk1 := l_doc_id||'-'||l_draft_id;
358 END IF;
359
360 SELECT fnd_attached_documents_s.nextval
361 INTO x_attached_document_id
362 FROM dual;
363
364 open c_get_max_attach_seq;
365 fetch c_get_max_attach_seq into l_seq_num;
366 close c_get_max_attach_seq;
367
368 l_seq_num := l_seq_num + 1;
369
370 select decode(l_doc_type_class,'PO','PO_HEADERS','SOURCING','PON_AUCTION_HEADERS_ALL') into l_entity_name from dual;
371
372
373 fnd_attached_documents_pkg.insert_row(X_Rowid => x_row_id,
374 X_attached_document_id => x_attached_document_id,
375 X_document_id => X_document_id,
376 X_creation_date => SYSDATE,
377 X_created_by => Fnd_Global.User_Id,
378 X_last_update_date => SYSDATE,
379 X_last_updated_by => Fnd_Global.User_Id,
380 X_last_update_login => Fnd_Global.Login_Id,
381 X_seq_num => l_seq_num,
382 X_entity_name => l_entity_name,
383 X_column1 => NULL,
384 X_pk1_value => l_attachment_pk1,
385 X_pk2_value => NULL,
386 X_pk3_value => NULL,
387 X_pk4_value => NULL,
388 X_pk5_value => NULL,
389 X_automatically_added_flag => 'Y',
390 /* columns necessary for creating a document on the fly */
391 X_datatype_id => 5,
392 X_category_id => 33,
393 X_security_type => 1,
394 X_security_id => 204,
395 X_publish_flag => 'Y',
396 X_language => l_lang,
397 X_description => 'WD Statement URL',
398 X_media_id => l_media_id,
399 X_create_doc => 'Y',
400 X_url => l_input_url,
401 X_title => 'Wage Determination Statement');
402
403
404 /*
405 FOR c IN c_get_WDOL_attachment LOOP
406
407 l_attachment_found := 'Y';
408
409 fnd_attached_documents2_pkg.copy_attachments(
410 X_from_entity_name => c.ENTITY_NAME,
411 X_from_pk1_value => c.pk1_value,
412 X_from_pk2_value => c.pk2_value,
413 X_from_pk3_value => c.pk3_value,
414 X_from_pk4_value => c.pk4_value,
415 X_from_pk5_value => c.pk2_value,
416 X_to_entity_name => c.ENTITY_NAME,
417 X_to_pk1_value => c.pk1_value,
418 X_to_pk2_value => c.pk2_value,
419 X_to_pk3_value => c.pk3_value,
420 X_to_pk4_value => c.pk4_value,
421 X_to_pk5_value => c.pk5_value,
422 X_created_by => Fnd_Global.User_Id,
423 X_last_update_login => Fnd_Global.Login_Id,
424 X_program_application_id => c.PROGRAM_APPLICATION_ID,
425 X_program_id => c.PROGRAM_ID,
426 X_request_id => c.REQUEST_ID,
427 X_automatically_added_flag => c.AUTOMATICALLY_ADDED_FLAG,
428 X_from_category_id => c.CATEGORY_ID,
429 X_to_category_id => 33,
430 X_orig_attach_doc_id => c.ATTACHED_DOCUMENT_ID
431 );
432
433 COMMIT;
434
435
436 FOR c2 IN c_last_attachment LOOP
437 UPDATE fnd_documents_tl
438 SET title = 'Wage Determination Statement',
439 description = 'WD Statement URL'
440 WHERE document_id = c2.DOCUMENT_ID;
441
442 END LOOP;
443
444 END LOOP;
445 */
446
447 IF x_row_id is not null THEN
448
449 l_lookup_code:=get_lookup_value('COMPLETE');
450
451 INSERT INTO po_wage_determination
452 (
453 id ,
454 doc_id ,
455 doc_type ,
456 draft_id ,
457 wd_statement_url ,
458 request_date ,
459 response_date ,
460 status ,
461 error ,
462 last_update_date ,
463 last_updated_by ,
464 creation_date ,
465 created_by ,
466 last_update_login
467 )
468 VALUES (po_wage_determination_s.NEXTVAL,
469 l_doc_id,
470 l_doc_type,
471 l_draft_id,
472 l_input_url,
473 SYSDATE,
474 SYSDATE,
475 l_lookup_code,
476 NULL,
477 SYSDATE,
478 Fnd_Global.User_Id,
479 SYSDATE,
480 Fnd_Global.User_Id,
481 Fnd_Global.Login_Id
482 );
483
484 ELSE
485
486 l_lookup_code:=get_lookup_value('FAIL');
487
488 INSERT INTO po_wage_determination
489 (
490 id ,
491 doc_id ,
492 doc_type ,
493 draft_id ,
494 wd_statement_url ,
495 request_date ,
496 response_date ,
497 status ,
498 error ,
499 last_update_date ,
500 last_updated_by ,
501 creation_date ,
502 created_by ,
503 last_update_login
504 )
505 VALUES (po_wage_determination_s.NEXTVAL,
506 l_doc_id,
507 l_doc_type,
508 l_draft_id,
509 l_input_url,
510 SYSDATE,
511 SYSDATE,
512 l_lookup_code,
513 'URL Not Found',
514 SYSDATE,
515 Fnd_Global.User_Id,
516 SYSDATE,
517 Fnd_Global.User_Id,
518 Fnd_Global.Login_Id
519 );
520
521
522 END IF;
523
524 COMMIT;
525
526 RETURN 'SUCCESS';
527 END po_wage_determination_receive;
528
529 PROCEDURE validate_wage_determination(
530 x_result OUT NOCOPY VARCHAR2,
531 x_error_code OUT NOCOPY VARCHAR2,
532 x_error_message OUT NOCOPY VARCHAR2,
533 p_auction_header_id IN NUMBER,
534 x_validation_status OUT NOCOPY VARCHAR2
535 )
536 IS
537
538 CURSOR c_get_org_id IS
539 SELECT org_id
540 FROM pon_auction_headers_all
541 WHERE auction_header_id = p_auction_header_id;
542
543 l_org_id NUMBER;
544
545 CURSOR c_wdol_params IS
546 SELECT ENABLE_WDOL
547 FROM po_system_parameters_all
548 WHERE org_id = l_org_id;
549
550 l_enable_wdol VARCHAR2(1);
551
552 CURSOR c_wdol_attachment_exists IS
553 SELECT 'Y'
554 FROM fnd_attached_documents attach, fnd_documents_tl doc
555 WHERE pk1_value = To_Char(p_auction_header_id)
556 AND attach.document_id = doc.document_id
557 AND doc.title = 'Wage Determination Statement'
558 AND doc.description = 'WD Statement URL'
559 AND doc.LANGUAGE = 'US'
560 AND attach.category_id = 33;
561
562 l_wdol_attachment_exists VARCHAR2(1):='N';
563
564 BEGIN
565
566 x_validation_status := 'Y';
567
568
569 x_result := FND_API.g_ret_sts_success;
570
571 OPEN c_get_org_id;
572 FETCH c_get_org_id INTO l_org_id;
573 CLOSE c_get_org_id;
574
575
576
577 OPEN c_wdol_params;
578 FETCH c_wdol_params INTO l_enable_wdol;
579 CLOSE c_wdol_params;
580
581
582
583 OPEN c_wdol_attachment_exists;
584 FETCH c_wdol_attachment_exists INTO l_wdol_attachment_exists;
585 CLOSE c_wdol_attachment_exists;
586
587
588
589 IF l_enable_wdol = 'Y' AND l_wdol_attachment_exists = 'N' THEN
590
591 x_validation_status := 'N';
592
593 END IF;
594
595 EXCEPTION
596 WHEN OTHERS THEN
597
598
599
600 x_result := FND_API.g_ret_sts_unexp_error;
601 x_error_code := SQLCODE;
602 x_error_message := SUBSTR(SQLERRM, 1, 100);
603
604 END validate_wage_determination;
605
606
607 PROCEDURE INIT_CURRENT_ROW (
608 x_return_status OUT NOCOPY VARCHAR2,
609 x_msg_count OUT NOCOPY NUMBER,
610 x_msg_data OUT NOCOPY VARCHAR2,
611 p_document_type IN VARCHAR2,
612 p_document_id IN NUMBER,
613 p_draft_id IN NUMBER,
614 p_doc_type_class IN VARCHAR2
615 )
616 IS
617
618 l_curr_row_exists VARCHAR2(1):='N';
619
620 CURSOR c_get_current_row IS
621 SELECT 'Y'
622 FROM po_wage_determination
623 WHERE doc_id = p_document_id
624 AND doc_type = p_document_type
625 AND Nvl(draft_id,-1) = Nvl(p_draft_id,-1);
626
627 BEGIN
628
629 OPEN c_get_current_row;
630 FETCH c_get_current_row INTO l_curr_row_exists;
631 CLOSE c_get_current_row;
632
633
634
635 IF l_curr_row_exists = 'N' THEN
636
637
638
639 INSERT INTO po_wage_determination
640 (
641 id ,
642 doc_id ,
643 doc_type ,
644 draft_id ,
645 wd_statement_url ,
646 request_date ,
647 response_date ,
648 status ,
649 error ,
650 last_update_date ,
651 last_updated_by ,
652 creation_date ,
653 created_by ,
654 last_update_login
655 )
656 VALUES (po_wage_determination_s.NEXTVAL,
657 p_document_id,
658 p_document_type,
659 p_draft_id,
660 null,
661 null,
662 null,
663 null,
664 NULL,
665 SYSDATE,
666 Fnd_Global.User_Id,
667 SYSDATE,
668 Fnd_Global.User_Id,
669 Fnd_Global.Login_Id
670 );
671
672
673
674 END IF;
675
676
677 COMMIT;
678
679 EXCEPTION WHEN OTHERS THEN
680 NULL;
681
682
683 END INIT_CURRENT_ROW ;
684
685 FUNCTION IS_ACTION_VALID (p_document_id NUMBER)
686 RETURN VARCHAR2
687 IS
688 CURSOR c_get_org_id IS
689 SELECT org_id
690 FROM pon_auction_headers_all
691 WHERE auction_header_id = p_document_id;
692
693 l_org_id NUMBER;
694
695 CURSOR c_wdol_params IS
696 SELECT ENABLE_WDOL
697 FROM po_system_parameters_all
698 WHERE org_id = l_org_id;
699
700 l_enable_wdol VARCHAR2(1);
701
702 CURSOR c_appropriate_status IS
703 SELECT 'Y'
704 FROM pon_auction_headers_all
705 WHERE Nvl(auction_status,'DRAFT') IN ('DRAFT','AUCTION_CLOSED','ACTIVE')
706 AND auction_header_id = p_document_id;
707
708 l_app_status VARCHAR2(1):='N';
709
710 BEGIN
711
712 OPEN c_get_org_id;
713 FETCH c_get_org_id INTO l_org_id;
714 CLOSE c_get_org_id;
715
716 OPEN c_wdol_params;
717 FETCH c_wdol_params INTO l_enable_wdol;
718 CLOSE c_wdol_params;
719
720 OPEN c_appropriate_status;
721 FETCH c_appropriate_status INTO l_app_status;
722 CLOSE c_appropriate_status;
723
724 IF Nvl(l_enable_wdol,'N') = 'Y' AND l_app_status = 'Y' THEN
725 RETURN 'Y';
726 END IF;
727
728 RETURN 'N';
729 END IS_ACTION_VALID;
730
731
732 END PO_WAGE_DETERMINATION_ONLINE;