1 PACKAGE BODY gl_interface_control_pkg AS
2 /* $Header: glijictb.pls 120.8 2005/06/17 23:21:53 djogg ship $ */
3
4 --
5 -- PUBLIC FUNCTIONS
6 --
7
8 FUNCTION get_unique_id RETURN NUMBER IS
9 new_id number;
10 BEGIN
11 SELECT gl_interface_control_s.NEXTVAL
12 INTO new_id
13 FROM dual;
14
15 return(new_id);
16 END get_unique_id;
17
18 FUNCTION get_unique_run_id RETURN NUMBER IS
19 new_id number;
20 BEGIN
21 SELECT gl_journal_import_s.NEXTVAL
22 INTO new_id
23 FROM dual;
24
25 return(new_id);
26 END get_unique_run_id;
27
28 PROCEDURE check_unique(x_interface_run_id NUMBER,
29 x_user_je_source_name VARCHAR2,
30 x_je_source_name VARCHAR2,
31 x_ledger_id NUMBER,
32 x_group_id NUMBER DEFAULT NULL,
33 row_id VARCHAR2) IS
34 CURSOR chk_duplicates is
35 SELECT 'Duplicate'
36 FROM GL_INTERFACE_CONTROL ic
37 WHERE ic.interface_run_id = x_interface_run_id
38 AND ic.je_source_name = x_je_source_name
39 AND ic.set_of_books_id = x_ledger_id
40 AND nvl(ic.group_id,-1) = nvl(x_group_id, -1)
41 AND ( row_id is null
42 OR ic.rowid <> row_id);
43 dummy VARCHAR2(100);
44 BEGIN
45 OPEN chk_duplicates;
46 FETCH chk_duplicates INTO dummy;
47
48 IF chk_duplicates%FOUND THEN
49 CLOSE chk_duplicates;
50 IF (x_group_id IS NULL) THEN
51 fnd_message.set_name('SQLGL', 'GL_DUPLICATE_JI_SOURCE');
52 ELSE
53 fnd_message.set_name('SQLGL', 'GL_DUPLICATE_JI_SOURCE_COMBO');
54 END IF;
55 app_exception.raise_exception;
56 END IF;
57
58 CLOSE chk_duplicates;
59
60 EXCEPTION
61 WHEN app_exceptions.application_exception THEN
62 RAISE;
63 WHEN OTHERS THEN
64 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
65 fnd_message.set_token('PROCEDURE',
66 'gl_interface_pkg.check_unique');
67 RAISE;
68 END check_unique;
69
70 FUNCTION used_in_alternate_table(
71 x_int_je_source_name VARCHAR2) RETURN VARCHAR2 IS
72 CURSOR chk_usage is
73 SELECT 'Other table'
74 FROM GL_JE_SOURCES s, GL_INTERFACE_CONTROL ic
75 WHERE ( ( s.user_je_source_name = x_int_je_source_name
76 AND s.import_using_key_flag = 'N')
77 OR ( s.je_source_key = x_int_je_source_name
78 AND s.import_using_key_flag = 'Y'))
79 AND ic.je_source_name = s.je_source_name
80 AND ic.status <> 'S'
81 AND nvl(upper(ic.interface_table_name), 'GL_INTERFACE')
82 <> 'GL_INTERFACE';
83 dummy VARCHAR2(100);
84 BEGIN
85 OPEN chk_usage;
86 FETCH chk_usage INTO dummy;
87
88 IF chk_usage%FOUND THEN
89 CLOSE chk_usage;
90 RETURN('Y');
91 ELSE
92 CLOSE chk_usage;
93 RETURN('N');
94 END IF;
95
96 EXCEPTION
97 WHEN app_exceptions.application_exception THEN
98 RAISE;
99 WHEN OTHERS THEN
100 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
101 fnd_message.set_token('PROCEDURE',
102 'gl_interface_control_pkg.used_in_alternate_table');
103 RAISE;
104 END used_in_alternate_table;
105
106 FUNCTION get_interface_table(
107 x_int_je_source_name VARCHAR2,
108 x_group_id NUMBER) RETURN VARCHAR2 IS
109 CURSOR get_table is
110 SELECT interface_table_name
111 FROM GL_JE_SOURCES s, GL_INTERFACE_CONTROL ic
112 WHERE ( ( s.user_je_source_name = x_int_je_source_name
113 AND s.import_using_key_flag = 'N')
114 OR ( s.je_source_key = x_int_je_source_name
115 AND s.import_using_key_flag = 'Y'))
116 AND ic.je_source_name = s.je_source_name
117 AND ic.group_id = x_group_id
118 AND ic.status <> 'S';
119 itable VARCHAR2(30);
120 BEGIN
121
122 OPEN get_table;
123 FETCH get_table INTO itable;
124
125 IF get_table%FOUND THEN
126 CLOSE get_table;
127 RETURN(itable);
128 ELSE
129 CLOSE get_table;
130 RETURN('GL_INTERFACE');
131 END IF;
132
133 EXCEPTION
134 WHEN app_exceptions.application_exception THEN
135 RAISE;
136 WHEN OTHERS THEN
137 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
138 fnd_message.set_token('PROCEDURE',
139 'gl_interface_control_pkg.get_interface_table');
140 RAISE;
141 END get_interface_table;
142
143 PROCEDURE insert_row(xinterface_run_id NUMBER,
144 xje_source_name VARCHAR2,
145 xledger_id NUMBER,
146 xgroup_id NUMBER,
147 xpacket_id NUMBER DEFAULT NULL) IS
148 CURSOR C IS
149 SELECT rowid
150 FROM gl_interface_control
151 WHERE interface_run_id = xinterface_run_id
152 AND je_source_name = xje_source_name
153 AND set_of_books_id = xledger_id
154 AND nvl(group_id, -1) = nvl(xgroup_id, -1);
155
156 X_Rowid VARCHAR2(18);
157 BEGIN
158
159 -- Do the insert
160 INSERT INTO gl_interface_control(
161 interface_run_id,
162 je_source_name,
163 set_of_books_id,
164 group_id,
165 status,
166 packet_id
167 ) VALUES (
168 xinterface_run_id,
169 xje_source_name,
170 xledger_id,
171 xgroup_id,
172 'S',
173 xpacket_id
174 );
175
176 OPEN C;
177 FETCH C INTO X_Rowid;
178 if (C%NOTFOUND) then
179 CLOSE C;
180 RAISE NO_DATA_FOUND;
181 end if;
182 CLOSE C;
183 END insert_row;
184
185 END gl_interface_control_pkg;
[Home] [Help]