[Home] [Help]
PACKAGE BODY: APPS.GL_JOURNAL_IMPORT_PKG
Source
1 PACKAGE BODY GL_JOURNAL_IMPORT_PKG as
2 /* $Header: glujimnb.pls 120.5 2005/05/05 01:40:09 kvora 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 ) IS
21 ind_name VARCHAR2(30);
22 fnd_schema VARCHAR2(30);
23 dummy1 VARCHAR2(30);
24 dummy2 VARCHAR2(30);
25 BEGIN
26 failpoint := 1;
27 cre_tab :=
28 'CREATE TABLE ' || table_name;
29
30 IF (tablespace IS NOT NULL) THEN
31 cre_tab := cre_tab ||
32 ' TABLESPACE ' || tablespace;
33 END IF;
34
35 IF (physical_attributes IS NOT NULL) THEN
36 cre_tab := cre_tab || ' ' || physical_attributes;
37 END IF;
38
39 cre_tab := cre_tab ||
40 ' AS SELECT * FROM GL_INTERFACE WHERE ROWNUM < 1';
41
42 failpoint := 2;
43 IF (NOT fnd_installation.get_app_info('FND',
44 dummy1,dummy2,fnd_schema)) THEN
45 RAISE CANNOT_GET_APPLSYS_SCHEMA;
46 END IF;
47
48 IF (fnd_schema IS NULL) THEN
49 RAISE CANNOT_GET_APPLSYS_SCHEMA;
50 END IF;
51
52 failpoint := 1;
53 ad_ddl.do_ddl( fnd_schema,
54 'SQLGL',
55 AD_DDL.CREATE_TABLE,
56 cre_tab,
57 table_name );
58
59 failpoint := 3;
60 IF (create_n1_index) THEN
61 ind_name := substrb(table_name, 1, 27) || '_n1';
62 cre_tab :=
63 'CREATE INDEX ' || ind_name || ' ON ' || table_name ||
64 ' (user_je_source_name, group_id) ';
65
66 IF (n1_tablespace IS NOT NULL) THEN
67 cre_tab := cre_tab ||
68 ' TABLESPACE ' || n1_tablespace;
69 END IF;
70
71 IF (n1_physical_attributes IS NOT NULL) THEN
72 cre_tab := cre_tab ||
73 ' ' || n1_physical_attributes;
74 END IF;
75
76 ad_ddl.do_ddl( fnd_schema,
77 'SQLGL',
78 AD_DDL.CREATE_INDEX,
79 cre_tab,
80 ind_name );
81
82 END IF;
83
84 failpoint := 4;
85 IF (create_n2_index) THEN
86 ind_name := substrb(table_name, 1, 27) || '_n2';
87
88 cre_tab :=
89 'CREATE INDEX ' || ind_name || ' ON ' || table_name ||
90 ' (request_id, je_header_id, status, code_combination_id) ';
91
92 IF (n2_tablespace IS NOT NULL) THEN
93 cre_tab := cre_tab ||
94 ' TABLESPACE ' || n2_tablespace;
95 END IF;
96
97 IF (n2_physical_attributes IS NOT NULL) THEN
98 cre_tab := cre_tab ||
99 ' ' || n2_physical_attributes;
100 END IF;
101
102 ad_ddl.do_ddl( fnd_schema,
103 'SQLGL',
104 AD_DDL.CREATE_INDEX,
105 cre_tab,
106 ind_name );
107
108 END IF;
109
110 failpoint := 0;
111 END create_table;
112
113 PROCEDURE drop_table(table_name VARCHAR2) IS
114 fnd_schema VARCHAR2(30);
115 dummy1 VARCHAR2(30);
116 dummy2 VARCHAR2(30);
117 BEGIN
118 failpoint := 2;
119 IF (NOT fnd_installation.get_app_info('FND',
120 dummy1,dummy2,fnd_schema)) THEN
121 RAISE CANNOT_GET_APPLSYS_SCHEMA;
122 END IF;
123
124 IF (fnd_schema IS NULL) THEN
125 RAISE CANNOT_GET_APPLSYS_SCHEMA;
126 END IF;
127
128 failpoint := 1;
129 cre_tab :=
130 'DROP TABLE ' || table_name;
131
132 ad_ddl.do_ddl( fnd_schema,
133 'SQLGL',
134 AD_DDL.DROP_TABLE,
135 cre_tab,
136 table_name );
137
138 END drop_table;
139
140 PROCEDURE populate_interface_control(
141 user_je_source_name VARCHAR2,
142 group_id IN OUT NOCOPY NUMBER,
143 set_of_books_id NUMBER,
144 interface_run_id IN OUT NOCOPY NUMBER,
145 table_name VARCHAR2 DEFAULT NULL,
146 processed_data_action VARCHAR2 DEFAULT NULL) IS
147 je_source_name VARCHAR2(25);
148 BEGIN
149
150 BEGIN
151 SELECT je_source_name
152 INTO je_source_name
153 FROM gl_je_sources
154 WHERE user_je_source_name
155 = populate_interface_control.user_je_source_name;
156 EXCEPTION
157 WHEN NO_DATA_FOUND THEN
158 RAISE INVALID_JE_SOURCE;
159 END;
160
161 IF (processed_data_action NOT IN (SAVE_DATA, DELETE_DATA,
162 DROP_INTERFACE_TABLE, NULL)) THEN
163 RAISE INVALID_PROCESSED_ACTION;
164 END IF;
165
166 IF ( (processed_data_action = DROP_INTERFACE_TABLE)
167 AND (nvl(upper(table_name),'GL_INTERFACE') = 'GL_INTERFACE')) THEN
168 RAISE CANNOT_DROP_GL_INTERFACE;
169 END IF;
170
171 IF (group_id IS NULL) THEN
172 SELECT gl_interface_control_s.NEXTVAL
173 INTO group_id
174 FROM DUAL;
175 END IF;
176
177 IF (interface_run_id IS NULL) THEN
178 SELECT gl_journal_import_s.NEXTVAL
179 INTO interface_run_id
180 FROM DUAL;
181 END IF;
182
183 INSERT INTO gl_interface_control
184 (status, je_source_name,
185 group_id, set_of_books_id,
186 interface_run_id, interface_table_name, processed_table_code)
187 VALUES
188 ('S', populate_interface_control.je_source_name,
189 populate_interface_control.group_id,
190 populate_interface_control.set_of_books_id,
191 populate_interface_control.interface_run_id,
192 table_name, processed_data_action);
193
194 END populate_interface_control;
195
196 FUNCTION get_last_sql RETURN VARCHAR2 IS
197 BEGIN
198 RETURN (cre_tab);
199 END get_last_sql;
200
201 FUNCTION get_error_msg RETURN VARCHAR2 IS
202 BEGIN
203 RETURN(ad_ddl.error_buf);
204 END get_error_msg;
205
206 END GL_JOURNAL_IMPORT_PKG;