1 PACKAGE BODY ECE_FLATFILE AS
2 -- $Header: ECEGENB.pls 115.2 99/08/23 15:39:44 porting ship $
3
4 --- PROCEDURE select_clause.
5 --- Creation Feb. 14, 1995
6 ---
7 --- Procedure select_clause builds a Select clause and a From
8 --- clause and a Where clause at run time for the dynamic SQL call.
9
10 --- It looks at all the data columns for the EC transaction,
11 --- based on stored info, sort the columns in ascending order
12 --- of which should be written in the output file first, then
13 --- store them in a PL/SQL table.
14 ---
15 --- It then builds a character string of the select clause
16 --- based on these stored columns and returns it. This select
17 --- clause had already add the TO_CHAR function to convert
18 --- data to character type.
19
20 --- It also returns the number and position of columns stored,
21 --- and width of each column. Furthmore, it returns the From
22 --- and Where clauses of the select statement.
23
24
25 PROCEDURE select_clause(
26 cTransaction_Type IN VARCHAR2,
27 cCommunication_Method IN VARCHAR2,
28 cInterface_Table IN VARCHAR2,
29 cExt_Table OUT VARCHAR2,
30 cInt_Table OUT CharTable,
31 cInt_Column OUT CharTable,
32 nRecord_Num OUT NumTable,
33 nData_Pos OUT NumTable,
34 nCol_Width OUT NumTable,
35 iRow_count OUT NUMBER,
36 cSelect_string OUT VARCHAR2,
37 cFrom_string OUT VARCHAR2,
38 cWhere_string OUT VARCHAR2)
39 IS
40 cSelect_stmt VARCHAR2(32000) := 'SELECT ';
41 cFrom_stmt VARCHAR2(32000) := ' FROM ';
42 cWhere_stmt VARCHAR2(32000) := ' WHERE ';
43
44 cTO_CHAR VARCHAR2(20) := 'TO_CHAR(';
45 cDATE VARCHAR2(40) := ',''YYYYMMDD HH24MISS'')';
46 cWord1 VARCHAR2(20) := ' ';
47 cWord2 VARCHAR2(40) := ' ';
48 cExtension_Table VARCHAR2(50);
49 iColumn_count INTEGER := 0;
50
51 CURSOR c1 is
52 Select min(eic.Table_Name) Table_Name,
53 eic.Column_Name Column_Name,
54 eic.Record_Number Record_Number,
55 eic.Position Position,
56 eic.Width New_width,
57 atc.Data_Length Width,
58 atc.Data_Type Col_Type
59 From all_tab_columns atc,
60 ece_interface_columns eic,
61 ece_interface_tables eit
62 Where eit.Transaction_Type = cTransaction_Type
63 and eit.Interface_Table_Name = cInterface_Table
64 and eit.interface_table_id = eic.interface_table_id
65 and eic.Column_Name = atc.Column_Name
66 and eic.Table_Name = atc.Table_Name
67 and eic.Position is not null
68 and eic.Record_Number is not null
69 Group by eic.Column_Name, eic.Record_Number, eic.Position, eic.Width, atc.Data_Length, atc.Data_Type
70 Order By Record_Number, Position;
71 /*
72 Select min(EIC.Table_Name) Table_Name, EIC.Column_Name Column_Name,
73 EIC.Record_Number Record_Number, EIC.Position Position,
74 EIC.Width New_width,
75 ATC.Data_Length Width, ATC.Data_Type Col_Type
76 From ALL_TAB_COLUMNS ATC, ECE_INTERFACE_COLUMNS EIC
77 Where EIC.Transaction_Type = cTransaction_Type
78 and EIC.Interface_Table_Name = cInterface_Table
79 and EIC.Column_Name = ATC.Column_Name
80 and EIC.Table_Name = ATC.Table_Name
81 Group by EIC.Column_Name, EIC.Record_Number, EIC.Position, ATC.Data_Length, ATC.Data_Type
82 Order By Record_Number, Position;
83 */
84 BEGIN
85
86 For c1_rec in c1 loop
87
88 -- **************************************
89 -- store data in PL/SQL tables
90 -- These tables will be returned to the main program
91 -- **************************************
92
93 iColumn_count := iColumn_count + 1;
94 cInt_Table(iColumn_count) := c1_rec.Table_Name;
95 cInt_Column(iColumn_count) := c1_rec.Column_Name;
96 nRecord_Num(iColumn_count) := c1_rec.Record_Number;
97 nData_Pos(iColumn_count) := c1_rec.Position;
98 nCol_Width(iColumn_count) := NVL(c1_rec.New_width, c1_rec.Width);
99
100 -- **************************************
101 -- apply appropriate data conversion
102 -- **************************************
103
104 if 'DATE' = c1_rec.Col_Type
105 Then
106 cWord1 := cTO_CHAR;
107 cWord2 := cDATE;
108 nCol_Width(iColumn_count) := 15;
109
110 elsif 'NUMBER' = c1_rec.Col_Type
111 Then
112 cWord1 := cTO_CHAR;
113 cWord2 := ')';
114 else
115 cWord1 := NULL;
116 cWord2 := NULL;
117 END if;
118
119 -- build SELECT statement
120
121 cSelect_stmt := cSelect_stmt || ' ' || cWord1 || c1_rec.Table_Name || '.' ||
122 c1_rec.Column_Name || cWord2 || ',';
123
124 End Loop;
125
126 Select EIT.Extension_Table_Name -- select extension table name
127 Into cExtension_Table
128 From ECE_INTERFACE_TABLES EIT
129 Where EIT.Transaction_Type = cTransaction_Type
130 And EIT.Interface_Table_Name = cInterface_Table;
131
132 -- build FROM, WHERE statements
133 cFrom_stmt := cFrom_stmt || cInterface_Table || ', '|| cExtension_Table;
134 cWhere_stmt := cWhere_stmt || cInterface_Table || '.' || 'TRANSACTION_RECORD_ID' ||
135 ' = ' || cExtension_table || '.'|| 'TRANSACTION_RECORD_ID(+)';
136
137 cSelect_string := RTRIM (cSelect_stmt, ',');
138 cFrom_string := cFrom_stmt;
139 cWhere_string := cWhere_stmt;
140 iRow_count := iColumn_count;
141 cExt_Table := cExtension_Table;
142
143 END select_clause;
144
145
146
147
148 --- PROCEDURE write_to_ece_output
149 --- Creation Feb. 15, 1995
150 --- This report procedure writes data to the ECE_OUTPUT table.
151 --- It put the appropriate record id at the beginning of each record
152 --- lines of data. The entire record line of data is inserted into the
153 --- TEXT column in the table.
154 ---
155 --- It expects a PL/SQL table of output data (in ASC order)!!!
156
157 PROCEDURE write_to_ece_output(
158 cTransaction_Type IN VARCHAR2,
159 cCommunication_Method IN VARCHAR2,
160 cInterface_Table IN VARCHAR2,
161 cColumn IN CharTable,
162 cReport_data IN CharTable,
163 nRecord_Num IN NumTable,
164 nData_pos IN NumTable,
165 nData_width IN NumTable,
166 iData_count IN NUMBER,
167 iOutput_width IN INTEGER,
168 iRun_id IN INTEGER)
169 IS
170 iLine_pos INTEGER;
171 iStart_num INTEGER;
172 cInsert_stmt VARCHAR2(32000);
173
174 BEGIN
175
176 iStart_num := nRecord_Num(1);
177 cInsert_stmt := ' ' || TO_CHAR(nRecord_Num(1));
178 iLine_pos := LENGTH(cInsert_stmt);
179
180 For i IN 1..iData_count Loop
181
182 cInsert_stmt := cInsert_stmt || substrb(rpad(nvl(cReport_data(i),' '),nData_width(i),' '),1,nData_width(i));
183
184 -- the following line is for testing/debug purpose
185 -- cInsert_stmt := cInsert_stmt || rpad(substrb(cColumn(i),1,nData_width(i)-2)||
186 -- substrb(TO_CHAR(nData_width(i)),1,2), TO_CHAR(nData_width(i)),' ');
187
188 if i < iData_count
189 then
190 If nRecord_Num(i) <> nRecord_Num(i+1)
191 then
192 insert into ece_output( RUN_ID, LINE_ID, TEXT) values
193 (iRun_id, ece_output_lines_s.nextval, substrb(cInsert_stmt,1,iOutput_width));
194 cInsert_stmt := '*' || TO_CHAR(nRecord_Num(i+1));
195 end if;
196 else
197 insert into ece_output( RUN_ID, LINE_ID, TEXT) values
198 (iRun_id, ece_output_lines_s.nextval, substrb(cInsert_stmt,1,iOutput_width));
199
200 end if;
201 end Loop;
202
203 END write_to_ece_output;
204
205
206 PROCEDURE Find_pos(
207 cColumn_Name IN CharTable,
208 nColumn_count IN NUMBER,
209 cIn_text IN VARCHAR2,
210 nPos OUT NUMBER)
211 IS
212 cIn_string VARCHAR2(1000);
213 BEGIN
214 cIn_string := UPPER(cIn_text);
215 For k in 1..nColumn_count loop
216 if UPPER(cColumn_Name(k)) = cIn_string then
217 nPos := k;
218 exit;
219 end if;
220 end loop;
221 END Find_pos;
222
223 END ECE_FLATFILE;
[Home] [Help]