1 PACKAGE BODY GL_CI_PRE_CROSS_PKG as
2 /* $Header: gluciprb.pls 120.3 2005/12/08 10:31:56 mikeward noship $ */
3
4
5 own CONSTANT varchar2(30) := 'GL';
6 appSchema CONSTANT varchar2(30) := 'APPS';
7
8 TYPE t_RefCur IS REF CURSOR;
9 GLOBAL_ERROR exception; --+something is wrong - general handler
10 --+ Tell me what's wrong
11 global_retcode number;
12 global_errbuf varchar2(2000);
13 section_number number(5); --+where did I go wrong?
14
15 --+ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
16 --+ Debug/Diagnostic routine
17 --+ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
18 procedure debug_message (
19 message_string in varchar2 ,
20 verbose_mode in boolean default FALSE,
21 line_size in number default 255 ,
22 which_file in varchar2 default FND_FILE.log
23 ) is
24 msg_text varchar2(10000) := substr(message_string, 1, 32000);
25 new_line number := 0;
26 begin
27 if verbose_mode then
28 if message_string = fnd_global.local_chr(10) then
29 FND_FILE.put(which_file, message_string);
30 else
31 while msg_text is not null loop
32 new_line := instr(msg_text, fnd_global.local_chr(10));
33 if new_line = 0 then new_line := line_size;
34 elsif new_line > line_size then new_line := line_size;
35 end if;
36 FND_FILE.put_line(which_file, substr(msg_text, 1, new_line-1));
37 msg_text := substr(msg_text, new_line+1);
38 end loop;
39 end if;
40 end if;
41 exception
42 when OTHERS then
43 if SQLCODE = 1 then
44 --+ Raised if you call FND_FILE from a SQL*Plus session
45 --+ without initializing user_id, resp_id, login_resp_id.
46 --+ dbms_output.enable(1000000);
47 while msg_text is not null loop
48 --+can not use chr(10) any more, can not use dbms_output any more
49 new_line := instr(msg_text, fnd_global.local_chr(10));
50 if new_line = 0 then new_line := line_size;
51 elsif new_line > line_size then new_line := line_size;
52 end if;
53 raise_application_error(-20000, substr(msg_text, 1, new_line-1));
54 --+DBMS_OUTPUT.put_line( substr(msg_text, 1, new_line-1) );
55 msg_text := substr(msg_text, new_line+1);
56 end loop;
57 else
58 RAISE;
59 end if;
60 end debug_message;
61
62 --+ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
63 --+ Debug/Diagnostic routine
64 --+ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
65 procedure DD (
66 message_string in varchar2,
67 mode_override in boolean default TRUE,
68 line_size in number default 255,
69 which_file in varchar2 default FND_FILE.log
70 ) is
71 begin
72 if mode_override then
73 debug_message(message_string, TRUE, line_size, which_file);
74 end if;
75 end DD;
76
77 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
78 --+get the group ID from the source dtaabase after the consolidation data
79 --+has been transfered into gl_interface table
80 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
81 function Get_Source_Group_ID(
82 cons_id IN number,
83 cons_run_id IN number
84 ) return number
85 IS
86 v_SQL varchar2(500);
87 l_group_id number;
88 BEGIN
89 v_SQL := 'select group_id from gl_consolidation_history ' ||
90 'where consolidation_id = :cons_id ' ||
91 'and consolidation_run_id = :cons_run_id';
92 EXECUTE IMMEDIATE v_SQL INTO l_group_id USING cons_id, cons_run_id;
93 l_group_id := l_group_id;
94 return l_group_id;
95 END Get_Source_Group_id;
96
97 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
98 --+this is the concurrent program that will be submitted right after
99 --+consolidation program is submitted. It will wait for consolidation to complete
100 --+then launches the Cross Instance Data Transfer program.
101 --+the maximum time it waits is 10 hours.
102 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
103 procedure pre_run_CI_transfer(
104 errbuf in out NOCOPY varchar2,
105 retcode in out NOCOPY varchar2,
106 p_resp_name IN varchar2,
107 p_cons_request_id IN number,
108 consolidation_id IN number,
109 run_id IN number,
110 to_period_token IN varchar2,
111 to_sob_id IN number,
112 p_user_name IN varchar2,
113 p_dblink IN varchar2,
114 from_group_id IN number,
115 from_sob_id IN number,
116 p_pd_name IN varchar2,
117 p_budget_name IN varchar2,
118 p_j_import IN VARCHAR2,
119 p_j_post IN varchar2,
120 p_actual_flag IN varchar2,
121 p_request_id IN number,
122 p_csj_flag IN VARCHAR2,
123 p_debug IN varchar2)
124
125 IS
126 dblink varchar2(30);
127 l_result varchar2(100);
128 v_WaitSQL varchar2(1000);
129 l_source_group_id number;
130 l_ci_request_id number;
131 section_number number;
132 phase varchar2(80);
133 status varchar2(80);
134 dev_phase varchar2(30);
135 dev_status varchar2(30);
136 message varchar2(240);
137 success boolean;
138 l_user_id number;
139 l_resp_id number;
140 l_count number := 0;
141 l_domainName varchar2(150);
142 BEGIN
143 section_number := 1;
144 --+bug#2712006, cannot use hardcoded domain name
145 v_WaitSQL := 'select domain_name ' ||
146 'from rg_database_links ' ||
147 'where name = :pd';
148 EXECUTE IMMEDIATE v_WaitSQL INTO l_domainName USING p_dblink;
149 dblink := p_dblink || '.' || l_domainName;
150
151 --dblink := p_dblink || '.WORLD';
152 --+Wait for Consolidation program to complete, the most is 10 hours
153 if (p_cons_request_id <> 0) then
154 success := fnd_concurrent.wait_for_request(p_cons_request_id,
155 30, 36000, phase, status, dev_phase, dev_status,
156 message);
157 If dev_phase = 'COMPLETE' AND
158 dev_status In ('NORMAL','WARNING' ) Then
159 l_result := 'COMPLETE:PASS';
160 Else
161 l_result := 'COMPLETE:FAIL';
162 End If;
163
164 if p_debug = 'Y' then
165 debug_message('Section number is ' || section_number,TRUE);
166 debug_message('Pre run CI transfer has started, status is ' || l_result,TRUE);
167 end if;
168 --+debug_message('Pre run CI transfer has started, status is ' || l_result,TRUE);
169 if l_result = 'COMPLETE:PASS' then
170 --+debug_message('status is complete:pass',TRUE);
171 l_source_group_id := Get_source_Group_id(consolidation_id, run_id);
172 --+debug_message('source group id is ' || l_source_group_id,TRUE);
173
174 l_ci_request_id := fnd_request.submit_request(
175 'SQLGL',
176 'GLCCIT',
177 '',
178 '',
179 FALSE,
180 p_Resp_Name,
181 p_User_Name,
182 p_dblink,
183 l_source_group_id,
184 from_sob_id, --bug#2602596, to make sure subsidiary set of books name is in email
185 To_Period_token,
186 p_budget_name,
187 p_j_import,
188 p_j_post,
189 p_Actual_Flag,
190 p_Request_Id,
191 p_csj_flag,
192 'N',
193 chr(0),'','','','','','','','','','', -- 24 arguments so far
194 '','','','','','','','','','','','','','','','',
195 '','','','','','','','','','','','','','','','',
196 '','','','','','','','','','','','','','','','',
197 '','','','','','','','','','','','','','','','', -- 16 in a row
198 '','','','','','','','','','','','');
199
200 IF l_ci_request_Id <> 0 THEN
201 --+cross instance data transfer program is submitted okay
202 FND_MESSAGE.set_name('SQLGL', 'GL_US_CI_ENTER_PRE');
203 FND_MESSAGE.set_token('REQUEST', l_ci_request_id, FALSE);
204 debug_message('==>' || FND_MESSAGE.get, TRUE);
205 ELSE
206 --+Cross instance data transfer program fails to start
207 FND_MESSAGE.set_name('SQLGL', 'GL_US_CI_FAIL_STARTCI');
208 FND_MESSAGE.set_token('RESULT', SUBSTR(SQLERRM,1,200), FALSE);
209 debug_message('==>' || FND_MESSAGE.get, TRUE);
210 END IF;
211 ELSE
212 --+consolidation program fails for any reason.
213 FND_MESSAGE.set_name('SQLGL', 'GL_US_CI_PRE_FAIL');
214 debug_message('==>' || FND_MESSAGE.get, TRUE);
215 END IF; --+ end if complete:pass
216 END IF; --+End if p_cons_request_id is not zero
217 END pre_run_CI_transfer;
218
219 end gl_ci_pre_cross_pkg;
220
221
222 --+ End of DDL script for GL_TEST_FND_REQUEST_PKG
223
224
225 --+ End of DDL script for GL_TEST_FND_REQUEST_PKG
[Home] [Help]