1 PACKAGE BODY EGO_PUB_WS_DEST AS
2 /* $Header: EGOPDESB.pls 120.1 2011/06/09 19:18:53 vijoshi noship $ */
3
4 FUNCTION Trigger_Import_Program(
5 p_set_process_id IN NUMBER
6 ,p_fnd_user_name IN VARCHAR2
7 ,p_resp_name IN VARCHAR2
8 ,p_resp_appl_name IN VARCHAR2
9 ,x_request_id OUT NOCOPY NUMBER
10 ) RETURN VARCHAR2 IS
11
12 l_req_id NUMBER;
13 l_req_succeeded VARCHAR2(1);
14 l_req_status BOOLEAN;
15 l_phase VARCHAR2(15);
16 l_status VARCHAR2(15);
17 l_dev_phase VARCHAR2(15);
18 l_dev_status VARCHAR2(15);
19 l_message VARCHAR2(240);--FND_CONCURRENT_REQUESTS%COMPLETION_TEXT;
20 l_fnd_user_id NUMBER;
21 l_resp_id NUMBER;
22 l_resp_appl_id NUMBER;
23 l_param_valid VARCHAR2(1);
24
25
26 BEGIN
27 l_req_id :=0;
28 l_req_succeeded :='N';
29 l_req_status :=FALSE;
30 l_fnd_user_id :=0;
31 l_resp_id :=0;
32 l_resp_appl_id :=0;
33 l_param_valid :='Y';
34 x_request_id := -1;
35
36 BEGIN
37 SELECT USER_ID
38 INTO l_fnd_user_id
39 FROM FND_USER
40 WHERE USER_NAME = p_fnd_user_name;
41 EXCEPTION
42 WHEN OTHERS THEN
43 l_param_valid := 'P';
44 END;
45
46 IF (l_param_valid ='Y') THEN
47 BEGIN
48 SELECT RESPONSIBILITY_ID
49 INTO l_resp_id
50 FROM FND_RESPONSIBILITY
51 WHERE RESPONSIBILITY_KEY = p_resp_name;
52 EXCEPTION
53 WHEN OTHERS THEN
54 l_param_valid := 'P';
55 END;
56 END IF;
57
58 IF (l_param_valid ='Y') THEN
59 BEGIN
60 SELECT APPLICATION_ID
61 INTO l_resp_appl_id
62 FROM FND_APPLICATION
63 WHERE APPLICATION_SHORT_NAME = p_resp_appl_name;
64 EXCEPTION
65 WHEN OTHERS THEN
66 l_param_valid := 'P';
67 END;
68 END IF;
69
70 /* if the user_name or responsibility name or the responsibility application name are invalid,
71 then Apps context cannot be initialized and the concurrent program cannot be launched.
72 So, return back. */
73 IF (l_param_valid = 'P') THEN
74 RETURN l_param_valid;
75 END IF;
76
77 /* Initiate an APPS session */
78 fnd_global.apps_initialize(user_id => l_fnd_user_id
79 ,resp_id => l_resp_id
80 ,resp_appl_id => l_resp_appl_id );
81 --
82 -- Submit the concurrent Request
83 --
84 /* Currently we are passing arguments 'Y' to all, hence it will check for all the entities
85 and if get any record then will process those else no action. */
86 l_req_id := FND_REQUEST.SUBMIT_REQUEST( application => p_resp_appl_name
87 ,program =>'EGOIMDCP'
88 ,description => NULL
89 ,start_time=> NULL
90 ,sub_request => FALSE
91 ,argument1 =>'Y'
92 ,argument2 =>'Y'
93 ,argument3 =>'Y'
94 ,argument4 =>p_set_process_id
95 ,argument5 => 'N');
96 --
97 -- Get the actual error in case concurrent program fails.
98 --
99 IF (l_req_id <= 0) THEN
100 l_req_succeeded :='N';
101 ELSE
102 COMMIT;
103 x_request_id := l_req_id;
104
105 /*We have harcoded the max_wait to 300 seconds (5mins) for now.
106 If the concurrent program does not complete in 300 secs, we
107 return back a T value.*/
108 l_req_status :=
109 fnd_concurrent.wait_for_request (request_id => l_req_id,
110 INTERVAL => 20,
111 max_wait => 300,
112 phase => l_phase,
113 STATUS => l_status,
114 dev_phase => l_dev_phase,
115 dev_status => l_dev_status,
116 MESSAGE => l_message
117 );
118 IF l_req_status=TRUE THEN
119 IF ( (Upper(l_dev_phase) <> 'COMPLETE') OR (l_dev_status IN ('CANCELLED','ERROR','TERMINATED')) ) THEN
120 l_req_succeeded :='F';
121 ELSE
122 l_req_succeeded :='S';
123 END IF;
124 ELSE
125 l_req_succeeded :='T';
126 END IF;
127 END IF;
128
129 RETURN l_req_succeeded;
130 END Trigger_Import_Program;
131 END EGO_PUB_WS_DEST;