Ø My sample complex file :
|
HINT15221223 6042016CAMZEUS
D003JOHN56FIN
D002KAPI22PWD
D001RAVI32OMG
TINT1523
HINT10122223 6022016CONZEUS
D
4 BIM29MAN
D
5 CHO22MAN
TINT1012
|
Ø Following NXSD created for the
complex file :
|
<xsd:element
name="ROOT">
<xsd:complexType>
<xsd:choice
minOccurs="1" maxOccurs="unbounded"
nxsd:choiceCondition="terminated"
nxsd:terminatedBy="">
<xsd:element
name="Header" nxsd:conditionValue="H"
minOccurs="1" maxOccurs="1"
nxsd:terminatedBy="">
<xsd:complexType>
<xsd:sequence>
<xsd:element
name="InterfaceID" type="xsd:string" nxsd:style="fixedLength"
nxsd:length="6"/>
<xsd:element
name="FileSeqNo" type="xsd:int"
nxsd:style="fixedLength" nxsd:length="5"/>
<xsd:element
name="Date" type="xsd:int"
nxsd:style="fixedLength" nxsd:length="8"/>
<xsd:element
name="FromSystem" type="xsd:string"
nxsd:style="fixedLength" nxsd:length="3" />
<xsd:element
name="ToSystem" type="xsd:string"
nxsd:style="terminated" nxsd:terminatedBy="${eol}"
minOccurs="0"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element
name="DataLine1" nxsd:conditionValue="D" minOccurs="1"
nxsd:terminatedBy="">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="EmpNo"
type="xsd:string" nxsd:style="fixedLength"
nxsd:length="3" minOccurs="0"/>
<xsd:element
name="EmpName" type="xsd:string" nxsd:style="fixedLength"
nxsd:length="4" minOccurs="0"/>
<xsd:element
name="Age" type="xsd:int"
nxsd:style="fixedLength" nxsd:length="2" />
<xsd:element
name="Stream" type="xsd:string"
nxsd:style="terminated" nxsd:terminatedBy="${eol}"
minOccurs="0"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element
name="Trailer" nxsd:conditionValue="T"
minOccurs="1" maxOccurs="1" nxsd:terminatedBy="">
<xsd:complexType>
<xsd:sequence>
<xsd:element
name="InterfaceID" type="xsd:string"
nxsd:style="fixedLength" nxsd:length="6"
minOccurs="0"/>
<xsd:element
name="RecordCount" type="xsd:int"
nxsd:style="terminated"
nxsd:terminatedBy="${eol}" minOccurs="0"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:choice>
</xsd:complexType>
</xsd:element>
|
Ø First we need to create a New Data
Server, Physical Schema & Logical Schema for the Complex File & DB
schema. PFB sample JDBC connection for Complex File :
Ø Now need to create Model for the
Complex File as well for the Database schema. You can check the hierarchy for
the following schema. You can validate the data once you done Reverse Engineer.
Ø Create a New Project (SDG_POC_PURPOSE) where we need perform
below activities :
I.
Need
to import knowledge Modules: LKM SQL to
SQL & IKM XML Control Append.
II.
Now
create 3 variables :
Counter: This is a counter, which will be used
to fetch the records from DB one by one.
Header_Count: This total number of interface in
Header.
My_Var: Refresh Interface ID in every loop.
III.
Now
need to create interfaces for Header, Data & Trailer.
PFB Mapping
of Header copy:
For source
LKL will be used as LKL SQL to SQL
& for the Target IKM XML Control
Append will be used
Similarly
Mapping need to done for Dataline Copy & Trailer Copy interface:
IV.
Then
3 procedure need to create :
Drop Table: This will be used to clear the
schema if required.
Write to File: This procedure will be used to write
to a Complex file
Populate Root variable:
This will be used to initiate the Root variable with 0.
V.
Now
Final Package need to create :
a.
Refresh
the entire variables.
b.
Truncate
existing schema.
c.
Populate
Root values.
d.
Execute
all the interfaces.
e.
Write
to a file zeus.txt.
f.
Append
the same to Zeus1.txt using OdiFileAppend tool.
g.
Increase
the counter & refresh the My_Var variable.
h.
Now
repeat the steps until all interface id from header has been executed.
EXECUTION:
PFB Diagram
for the project:
PFB execution steps:
Source table:
Header:
Dateline:
Trailer:
Target Sample file Created:
