Migration job mapping : missing columns

Hi mduchaff,

a CREATE_TABLE_FROM_FILE job has the aim to upload data from a file into the Oracle DB but NOT into an IFS data object / IFS view.

The common approach is to divide the migration process into two separate steps:

1. Load data into oracle db (CREATE_TABLE_FROM_FILE) → a new table will be created, containing the data of source file
2. Migrate data from the table of A. by linking (mapping) the columns from the table to the columns in the target view (MIGRATE_SOURCE_DATA)

Pls. refer to:

A.

https://docs.ifs.com/techdocs/24r2/030_administration/050_data_management/050_data_migration/030_migration_types/010_file_migration/010_create_table_from_file/#execute_file_migration_job

and

B.

https://docs.ifs.com/techdocs/24r2/030_administration/050_data_management/050_data_migration/030_migration_types/020_source_migration/

BR

Hi Florian,

That’s what I have done:

When I said “a CREATE_TABLE_FROM_FILE migration job to upload data in EMPLOYEE_ACCESS_SUP.” I meant to upload data in a temporary table before migrating to EMPLOYEE_ACCESS_SUP.

Following your link A, on paragraph 5, it said :

 File Mappings Tab. ...or enter a suitable view in the View Name column in the Migration Job header, so that it populates the columns automatically to this tab. 

Unfortunately, in the case of view EMPLOYEE_ACCESS_SUP, this mapping seems unfinished, as I said in my first question.

Thank you Florian for your answer.

However, I don’t know where, in IFS cloud, you can see the attributes of a view?

Is it in the debug screen, the Query screen?

I’m not an IFS developer.

Hi Marc,

the attributes of a view a part of the view comments in the definition of the view.

I don’t know if you have direct access to the database or which tool you use to perform SQL.

We’re using the PL/SQL Developer and I can look at view definitions by RMB + Describe.

Which leads to the following info:

I do not know how or whether these definitions can also be called up from IFS, as these are the data objects themselves.

BR

I used to run SQL commands from Quick reports on IFS cloud.

I do have limited access to Oracle SQL Developer, but here is what I can see from there: 
No EMPLOYEE_ACCESS_SUP in the View list 🤔

Maybe I can ask IFS support to extend my rights there.

Hi Marc,

try the following steps in SQL Developer:

• Navigate to your database connection in the "Connections" pane and open the Views folder.

• Right-click on the desired view.

• Select "Edit" or "SQL" to see the SQL definition of the view. The "SQL" tab shows the CREATE VIEW statement for the view.

BR

Good.

Unfortunately, I see only a subset of the existing views. And my EMPLOYEE_ACCESS_SUP isn’t part of it.

I don’t see that view in the list of views, and the list of tables is empty.

However I get a result with the query 

select * from ifsapp.EMPLOYEE_ACCESS_SUP  🤔🤣

The ifsapp. in this context is related to the database scheme. 

Maybe this is a selection criteria on the listed views.

With a

select text from all_views where view_name = 'EMPLOYEE_ACCESS_SUP'

I was able to get the view definition :

"SELECT s.org_bu_id                          org_bu_id,
       s.person_id                          supervisor_person_id, 
       s.company_id                         supervisor_company_id,
       s.emp_no                             supervisor_emp_no,
       Decode(s.emp_no, NULL, 'PERSON', 'EMPLOYEE')  supervisor_bu_type_db,
       Business_Unit_Type_API.Decode(Decode(s.emp_no, NULL, 'PERSON', 'EMPLOYEE')) supervisor_bu_type,
       b.company_id                         emp_company_id,
       b.emp_no                             emp_no,    
       b.business_unit_id                   emp_bu_id,
       r.position_id                        emp_pos_code,
       s.access_role_id                     access_role,
       GREATEST(r.valid_from,s.valid_from)  valid_from,
       LEAST(r.valid_to, s.valid_to)        valid_to,
       DECODE((CASE WHEN trunc(sysdate) BETWEEN r.valid_from AND r.valid_to THEN orig_r.general_access
                         ELSE 'FALSE' END),'TRUE', s.trans_from, GREATEST(s.trans_from, r.valid_from)) trans_from,
       DECODE((CASE WHEN trunc(sysdate) BETWEEN r.valid_from AND r.valid_to THEN orig_r.general_access
                         ELSE 'FALSE' END),'TRUE', s.trans_to, LEAST(s.trans_to, r.valid_to)) trans_to,
       r.general_access                     general_access,
       o.business_unit_type                 org_bu_type,
       DECODE (o.business_unit_type, 'CUSTOM', o.business_unit_code, 'ORGANIZATION', o.org_code) org_code,
       o.employee_group_id                  employee_group,
       o.person_group_id                    person_group,
       r.relation_id                        relation_id,
       s.relation_id                        supervisor_relation_id,
       s.pos_code                           supervisor_pos_code,
       s.structure_id                       structure_id,
       Business_Unit_API.Get_Structure_Type(s.structure_id) structure_type,
       DECODE (orig_r.relation_type, 'ACCESS', orig_r.related_to, orig_r.business_unit_id) orig_bu_id,
       orig_r.include_sub  include_sub,
       DECODE (orig_r.relation_type, 'ACCESS', 'FALSE', 'TRUE') organization_assignment       
  FROM business_unit_relation_tab r,
       business_unit_tab b,
       business_unit_tab o,
       business_unit_relation_tab orig_r,
       company_emp ce,
       (SELECT a.emp_no,
               a.company_id,
               a.person_id, 
               a.org_bu_id,
               NVL(p.access_role_id, a.access_role_id) access_role_id,
               GREATEST(a.valid_from, NVL(p.valid_from,a.valid_from)) valid_from,
               LEAST(a.valid_to, NVL(p.valid_to,a.valid_to)) valid_to,
               GREATEST(a.trans_from, NVL(p.valid_from,a.trans_from)) trans_from,
               LEAST(a.trans_to, NVL(p.valid_to,a.trans_to)) trans_to,
               p.avail_pos_code emp_pos_code,
               a.relation_id,
               a.pos_code,
               Business_Unit_API.Get_Struct_Id_By_Access(a.org_bu_id) structure_id
          FROM bu_access_temp_tab a,
               business_unit_tab s,
               company_authorization_temp_tab p
         WHERE s.business_unit_id = a.org_bu_id
           AND BITAND(nvl(p.access_area, a.access_area), 2 /*User_Access_API.DB_EMPLOYEE_DATA_ACCESS*/) > 0
           AND (a.relation_type = 'ACCESS' AND a.pos_code IS NULL OR p.struct_bu_id = s.struct_bu_id)
           AND p.pos_code(+) = a.pos_code
           AND p.valid_from(+) <= a.valid_to
           AND p.valid_to(+) >= a.valid_from) s
WHERE r.business_unit_id = s.org_bu_id
  AND r.relation_type = 'OPERATIONAL'
  AND r.related_to = b.business_unit_id
  AND b.business_unit_type = 'EMPLOYEE'
  AND ce.company = b.company_id
  AND ce.employee_id = b.emp_no
  AND ce.person_id <> s.person_id
  AND EXISTS (SELECT u.company
      FROM user_finance_tab u, person_info_tab p
         WHERE u.company = b.company_id
         AND u.userid = p.user_id
         AND p.person_id = s.person_id)
  AND (s.emp_pos_code IS NULL OR s.emp_pos_code = NVL(r.position_id,' '))
  AND r.valid_from <= s.valid_to
  AND r.valid_to >= s.valid_from
  AND o.business_unit_id = r.business_unit_id
  AND orig_r.relation_id = s.relation_id
WITH   READ ONLY
"

Good to know.

The comment section still missing; probaly because it is with “with read only”.

I’ve found another query which will finally lead to the comments:

select * from USER_COL_COMMENTS
where table_name = 'EMPLOYEE_ACCESS_SUP';

This table_name is not visible for me 😑

I can get some results (table_name = EMPLOYEE_ANALYSIS_RLS) with :

select * from USER_COL_COMMENTS
where table_name like 'EMPL%';

In fact, with “select distinct table_name from USER_COL_COMMENTS order by 1”
I get results only for Views that I can see in the connexions tree:

Try the same select but use ALL_COL_COMMENTS.

The user_col_comments is based on logged in user.

You’re the best ! 😀 :

IFSAPP    EMPLOYEE_ACCESS_SUP    ACCESS_ROLE    PROMPT=Access Role^
IFSAPP    EMPLOYEE_ACCESS_SUP    VALID_FROM    FLAGS=AMIUL^DATATYPE=DATE/DATE^PROMPT=Valid From^
IFSAPP    EMPLOYEE_ACCESS_SUP    VALID_TO    FLAGS=AMIUL^DATATYPE=DATE/DATE^PROMPT=Valid To^
IFSAPP    EMPLOYEE_ACCESS_SUP    TRANS_FROM    FLAGS=A-IU-^DATATYPE=DATE/DATE^PROMPT=Trans From^
IFSAPP    EMPLOYEE_ACCESS_SUP    TRANS_TO    FLAGS=A-IU-^DATATYPE=DATE/DATE^PROMPT=Trans To^
IFSAPP    EMPLOYEE_ACCESS_SUP    GENERAL_ACCESS    FLAGS=A-IU-^DATATYPE=STRING(5)^PROMPT=General Access^
IFSAPP    EMPLOYEE_ACCESS_SUP    ORG_BU_TYPE    PROMPT=Org Bu Type^

...

So with 

select column_name,SUBSTR(comments,7,5) from ALL_COL_COMMENTS
where table_name = 'EMPLOYEE_ACCESS_SUP'
and comments like 'FLAGS=A%'

I get the list of columns to add and the proper flag