[Home] [Help]
PACKAGE BODY: APPS.GL_JOURNAL_IMPORT_PKG
Source
1 PACKAGE BODY GL_JOURNAL_IMPORT_PKG as
2 /* $Header: glujimnb.pls 120.6.12020000.2 2012/12/01 15:33:11 dthakker ship $ */
3
4 -- Indicates the point in which a create_table statement failed.
5 failpoint NUMBER := 0;
6
7 -- Buffer used for the create table and create index statements
8 cre_tab VARCHAR2(2000);
9
10
11 PROCEDURE create_table(table_name VARCHAR2,
12 tablespace VARCHAR2 DEFAULT NULL,
13 physical_attributes VARCHAR2 DEFAULT NULL,
14 create_n1_index BOOLEAN DEFAULT TRUE,
15 n1_tablespace VARCHAR2 DEFAULT NULL,
16 n1_physical_attributes VARCHAR2 DEFAULT NULL,
17 create_n2_index BOOLEAN DEFAULT TRUE,
18 n2_tablespace VARCHAR2 DEFAULT NULL,
19 n2_physical_attributes VARCHAR2 DEFAULT NULL,
20 create_n3_index BOOLEAN DEFAULT FALSE,
21 n3_tablespace VARCHAR2 DEFAULT NULL,
22 n3_physical_attributes VARCHAR2 DEFAULT NULL
23 ) IS
24 ind_name VARCHAR2(30);
25 fnd_schema VARCHAR2(30);
26 dummy1 VARCHAR2(30);
27 dummy2 VARCHAR2(30);
28 apps_schema VARCHAR2(30);
29 BEGIN
30
31 SELECT oracle_username into apps_schema FROM fnd_oracle_userid where read_only_flag = 'U';
32 failpoint := 1;
33 cre_tab :=
34 'CREATE TABLE ' || table_name;
35
36 IF (tablespace IS NOT NULL) THEN
37 cre_tab := cre_tab ||
38 ' TABLESPACE ' || tablespace;
39 END IF;
40
41 IF (physical_attributes IS NOT NULL) THEN
42 cre_tab := cre_tab || ' ' || physical_attributes;
43 END IF;
44
45 /* 15884240 added apps schema name */
46 cre_tab := cre_tab ||
47 ' AS SELECT * FROM ' || apps_schema || '.GL_INTERFACE WHERE ROWNUM < 1';
48
49 failpoint := 2;
50 IF (NOT fnd_installation.get_app_info('FND',
51 dummy1,dummy2,fnd_schema)) THEN
52 RAISE CANNOT_GET_APPLSYS_SCHEMA;
53 END IF;
54
55 IF (fnd_schema IS NULL) THEN
56 RAISE CANNOT_GET_APPLSYS_SCHEMA;
57 END IF;
58
59 failpoint := 1;
60 ad_ddl.do_ddl( fnd_schema,
61 'SQLGL',
62 AD_DDL.CREATE_TABLE,
63 cre_tab,
64 table_name );
65
66 failpoint := 3;
67 IF (create_n1_index) THEN
68 ind_name := substrb(table_name, 1, 27) || '_n1';
69 cre_tab :=
70 'CREATE INDEX ' || ind_name || ' ON ' || table_name ||
71 ' (user_je_source_name, group_id) ';
72
73 IF (n1_tablespace IS NOT NULL) THEN
74 cre_tab := cre_tab ||
75 ' TABLESPACE ' || n1_tablespace;
76 END IF;
77
78 IF (n1_physical_attributes IS NOT NULL) THEN
79 cre_tab := cre_tab ||
80 ' ' || n1_physical_attributes;
81 END IF;
82
83 ad_ddl.do_ddl( fnd_schema,
84 'SQLGL',
85 AD_DDL.CREATE_INDEX,
86 cre_tab,
87 ind_name );
88
89 END IF;
90
91 failpoint := 4;
92 IF (create_n2_index) THEN
93 ind_name := substrb(table_name, 1, 27) || '_n2';
94
95 cre_tab :=
96 'CREATE INDEX ' || ind_name || ' ON ' || table_name ||
97 ' (request_id, je_header_id, status, code_combination_id) ';
98
99 IF (n2_tablespace IS NOT NULL) THEN
100 cre_tab := cre_tab ||
101 ' TABLESPACE ' || n2_tablespace;
102 END IF;
103
104 IF (n2_physical_attributes IS NOT NULL) THEN
105 cre_tab := cre_tab ||
106 ' ' || n2_physical_attributes;
107 END IF;
108
109 ad_ddl.do_ddl( fnd_schema,
110 'SQLGL',
111 AD_DDL.CREATE_INDEX,
112 cre_tab,
113 ind_name );
114
115 END IF;
116
117 failpoint := 5;
118
119 IF (create_n3_index) THEN
120
121 ind_name := substrb(table_name, 1, 27) || '_n3';
122
123 cre_tab :=
124 'CREATE INDEX ' || ind_name || ' ON ' || table_name ||
125 ' (je_header_id) ';
126
127 IF (n3_tablespace IS NOT NULL) THEN
128 cre_tab := cre_tab ||
129 ' TABLESPACE ' || n3_tablespace;
130 END IF;
131
132 IF (n3_physical_attributes IS NOT NULL) THEN
133 cre_tab := cre_tab ||
134 ' ' || n3_physical_attributes;
135 END IF;
136
137 ad_ddl.do_ddl( fnd_schema,
138 'SQLGL',
139 AD_DDL.CREATE_INDEX,
140 cre_tab,
141 ind_name );
142
143 END IF;
144
145 failpoint := 0;
146 END create_table;
147
148 PROCEDURE drop_table(table_name VARCHAR2) IS
149 fnd_schema VARCHAR2(30);
150 dummy1 VARCHAR2(30);
151 dummy2 VARCHAR2(30);
152 BEGIN
153 failpoint := 2;
154 IF (NOT fnd_installation.get_app_info('FND',
155 dummy1,dummy2,fnd_schema)) THEN
156 RAISE CANNOT_GET_APPLSYS_SCHEMA;
157 END IF;
158
159 IF (fnd_schema IS NULL) THEN
160 RAISE CANNOT_GET_APPLSYS_SCHEMA;
161 END IF;
162
163 failpoint := 1;
164 cre_tab :=
165 'DROP TABLE ' || table_name;
166
167 ad_ddl.do_ddl( fnd_schema,
168 'SQLGL',
169 AD_DDL.DROP_TABLE,
170 cre_tab,
171 table_name );
172
173 END drop_table;
174
175 PROCEDURE populate_interface_control(
176 user_je_source_name VARCHAR2,
177 group_id IN OUT NOCOPY NUMBER,
178 set_of_books_id NUMBER,
179 interface_run_id IN OUT NOCOPY NUMBER,
180 table_name VARCHAR2 DEFAULT NULL,
181 processed_data_action VARCHAR2 DEFAULT NULL) IS
182 je_source_name VARCHAR2(25);
183 BEGIN
184
185 BEGIN
186 SELECT je_source_name
187 INTO je_source_name
188 FROM gl_je_sources
189 WHERE user_je_source_name
190 = populate_interface_control.user_je_source_name;
191 EXCEPTION
192 WHEN NO_DATA_FOUND THEN
193 RAISE INVALID_JE_SOURCE;
194 END;
195
196 IF (processed_data_action NOT IN (SAVE_DATA, DELETE_DATA,
197 DROP_INTERFACE_TABLE, NULL)) THEN
198 RAISE INVALID_PROCESSED_ACTION;
199 END IF;
200
201 IF ( (processed_data_action = DROP_INTERFACE_TABLE)
202 AND (nvl(upper(table_name),'GL_INTERFACE') = 'GL_INTERFACE')) THEN
203 RAISE CANNOT_DROP_GL_INTERFACE;
204 END IF;
205
206 IF (group_id IS NULL) THEN
207 SELECT gl_interface_control_s.NEXTVAL
208 INTO group_id
209 FROM DUAL;
210 END IF;
211
212 IF (interface_run_id IS NULL) THEN
213 SELECT gl_journal_import_s.NEXTVAL
214 INTO interface_run_id
215 FROM DUAL;
216 END IF;
217
218 INSERT INTO gl_interface_control
219 (status, je_source_name,
220 group_id, set_of_books_id,
221 interface_run_id, interface_table_name, processed_table_code)
222 VALUES
223 ('S', populate_interface_control.je_source_name,
224 populate_interface_control.group_id,
225 populate_interface_control.set_of_books_id,
226 populate_interface_control.interface_run_id,
227 table_name, processed_data_action);
228
229 END populate_interface_control;
230
231 FUNCTION get_last_sql RETURN VARCHAR2 IS
232 BEGIN
233 RETURN (cre_tab);
234 END get_last_sql;
235
236 FUNCTION get_error_msg RETURN VARCHAR2 IS
237 BEGIN
238 RETURN(ad_ddl.error_buf);
239 END get_error_msg;
240
241 END GL_JOURNAL_IMPORT_PKG;