How to Extract data from database and populate it as Header and Detail style into Fixed length file?

Scenario:
Let's take one scenario where we need to populate Department Name (Header) and correspondent Employee information (Detail) under that Department.

Assumption:
You should aware of Topology Configuration for Oracle and File Technology
You should aware of Creating Oracle Model & File Model
You should aware of Creating variable

Steps:

1. Creating File Datastore (Header)-- Right click on File Model and select new datastore.
Mention as per below screenshot - Defintion , File and Attributes Tab

Definition Tab



Files Tab



Attributes:

2. Create file datastore (Detail) -- Right click on file Model and select new datastore

3. Create Mapping
Drag and drop Source Datastores (Employees and departments) from oracle Model
Drag and drop Target Datastores (Header and Details)

Create Variable - v_department_id

Drag and Drop filter component and join Out connector of department datastore with in connector of filter component and specify as below.

DEPARTMENTS.DEPARTMENT_ID = #PRACTICE_PROJECT.v_department_id

Drag and Drop filter component and join Out connector of department datastore with in connector of filter component and specify as below.

EMPLOYEES.DEPARTMENT_ID = #PRACTICE_PROJECT.v_department_id

Connect out connector of Department Filter Component with in connector of datastore (header) component and disable the "Create attributes of Target" and keep remaining as it

Connect out connector of Employee Filter Component with in connector of datastore (detail) component and disable the "Create attributes of Target" and keep remaining as it

Do mapping for name as "EMPLOYEES.FIRST_NAME||'.'||EMPLOYEES.LAST_NAME"
Dept_name as "DEPARTMENTS.DEPARTMENT_NAME"

Create a sequence for Department
seq_dept

Now map Department_Seq with "#PRACTICE_PROJECT.seq_dept_NEXTVAL"
Department_seq with

Save Mapping

4. Create Package -- Right click on package and select new package
Drag and drop Variable and Mapping into package as mention below

5. Create Scenario -- Right click on package and select generate Scenario
Click on ok

(Make a note of Scenario name)

5. Create Procedure -- Right click on Procedure Object and select new procedure
Create one task
Under target side enter following code and select technology as ODI tools
OdiStartScen "-SCEN_NAME=<SCENARIO_NAME>" "-SYNC_MODE=1" "-VERSION=001" "-PRACTICE_PROJECT.v_department_id=#DEPT_ID"

Source Side enter following code and select technology as Oracle and logical Schema related to that Schema

SELECT DEPARTMENT_ID DEPT_ID FROM HR.DEPARTMENTS


Output: