Find the employee distribution legal information table [hr_soft_coding_keyflex] and the contact with the master allocation table.

Hr_soft_coding_keyflex HS

The corresponding contact key is soft_coding_keyflex_id.

The associated table is: per_all_assignments_f

I confirmed my guess yesterday: This legal information is not in the employee's basic assignment table, so there must be a foreign key associated with it pointing to the table that stores legal information. This legal information table is: hr_soft_coding_keyflex.

We also got an inspiration: In the employee basic assignment information table [per_all_assignments_f], the field 'soft _ coding_keyflex_id 'is similar to the name of the legal information table [hr_soft_coding_keyflex: A '_ id' is added to the backend, and a' _ hr' is missing '.

Also, the method for finding this field is worth thinking and learning:

I need to find the table of information such as the area to be shared, tax area, and employer, and the connection with the employee basic assignment information table per_all_assignments_f.

First, I open 'hr seed (v2.6.1) 'from discoverer administration edition and find the 'hr _ employee assignment' folder, the field 'area pool region' is found. Right-click the folder and select 'properties'. Then, select'm m SQL 'to obtain the SQL source code of the folder. Then, analyze the source code to find the required tables, fields, and relationships.
Select distinct PAAF. person_id,
PAAF. assignment_id,
PAAF. organization_id,
PAAF. grade_id,
PAPF. last_name employee name,
To_number (PAPF. employee_number) employee ID,
PPT. user_person_type employee type,
PAAF. assignment_number allocation number,
Nvl (amdtl. user_status, sttl. user_status) allocation status,
Department to which hou. name belongs,
Hou. attribute20 Department abbreviation,
PPT. group_name personnel group,
Substr (PJ. Name, 1, instr (PJ. Name, '.')-1) job category,
Substr (PJ. Name, instr (PJ. Name, '.') + 1) Title name,
Pscode. pscode job category Code ,
Substr (pp. Name, 1, instr (pp. Name, '.')-1) job category,
Substr (pp. Name, instr (pp. Name, '.') + 1) Position name,
Pg. Sequence grade sorting,
Pg. Name,
Pays. payroll_name payroll name,
Pays. payroll_type payroll type,
Decode (PAAF. primary_flag, 'y', 'yes') is mainly allocated,
AB. Meaning area,
PAAF. last_update_date the last update date of the allocation information,
S01.last _ name: The last updated person in the allocation information,
Hal. location_code location,
H13.meaning allocation category,
H14.meaning employee category,
PAAF. interval tive_start_date "Assign valid period-from ",
PAAF. interval tive_end_date "Assign valid period-"
From per_people_f PAPF,
Per_assignments_f PAAF,
Pay_people_groups PPT,
Per_assignment_status_types past,
Per_assignment_status_types_tl sttl,
Per_ass_status_type_amends AMD,
Per_ass_status_type_amends_tl amdtl,
Hr_organization_units Hou,
Per_jobs PJ,
Per_positions PP,
Per_grades PG,
Per_person_types PPT,
Per_person_type_usages_f pptuf,
Hr_lookups H13,
Hr_lookups H14,
Hr_soft_coding_keyflex HS,
Fnd_lookup_values AB,
Pay_all_payrolls_f pays,
(Select distinct T. user_id, Tf. last_name
From fnd_user T, per_all_people_f TF
Where T. employee_id = TF. person_id (+)
And trunc (sysdate) between TF. inclutive_start_date and
TF. effective_end_date) S01,
(
Select C. flex_value_meaning pscode, trim (C. Description) psdesc
From fnd_flex_values A, fnd_flex_value_sets B, fnd_flex_values_tl C
Where a. flex_value_set_id = B. flex_value_set_id
And a. flex_value_id = C. flex_value_id
And B. flex_value_set_name = 'cas _ position_attribute_code'
And C. Language = 'zhs'
And a. enabled_flag = 'y'
) Pscode,
Hr_locations_all hal
Where PAAF. assignment_status_type_id = past. assignment_status_type_id
And past. assignment_status_type_id = sttl. assignment_status_type_id
And sttl. Language = userenv ('lang ')
And pays. payroll_id (+) = PAAF. payroll_id
And AMD. ass_status_type_amend_id = amdtl. ass_status_type_amend_id (+)
And decode (amdtl. ass_status_type_amend_id, null, '1', amdtl. Language) =
Decode (amdtl. ass_status_type_amend_id, null, '1', userenv ('lang '))
And PAAF. assignment_status_type_id = AMD. assignment_status_type_id (+)
And PAAF. person_id (+) = PAPF. person_id
And PAAF. people_group_id = PPT. people_group_id (+)
-- And PAAF. primary_flag = 'y'
And PAAF. assignment_status_type_id = past. assignment_status_type_id
And PAAF. organization_id = hou. organization_id
And PAAF. job_id = PJ. job_id (+)
And PAAF. position_id = pp. position_id (+)
And PAAF. grade_id = pg. grade_id (+)
And trunc (sysdate) between PAPF. inclutive_start_date and
PAPF. interval tive_end_date
And trunc (sysdate) between PAAF. interval tive_start_date and
PAAF. interval tive_end_date
-- And trunc (sysdate) between PPT. start_date_active and PPT. end_date_active
And h14.lookup _ type (+) = 'employee _ catg'
And h14.lookup _ code (+) = PAAF. employee_category
And h13.lookup _ type (+) = 'emp_cat'
And h13.lookup _ code (+) = PAAF. employment_category
And PPT. system_person_type = 'emp'
And PAPF. person_id = pptuf. person_id
And pptuf. person_type_id = PPT. person_type_id
And PAAF. last_updated_by = s01.user _ ID (+)
And PAAF. location_id = Hal. location_id (+)
And hs. soft_coding_keyflex_id = PAAF. soft_coding_keyflex_id
And hs. segment21 = AB. lookup_code --
-- And AB. lookup_type = 'cn _ sic_area 'and AB. Language = 'zhs'
And trim (substr (pp. Name, 1, instr (pp. Name, '.')-1) = pscode. psdesc (+)
And trunc (sysdate) between pptuf. inclutive_start_date and
Pptuf. inclutive_end_date
And trunc (sysdate) between nvl (pg. date_from, to_date ('2014/1/01', 'yyyy/MM/dd '))
And nvl (pg. date_to, to_date ('2017/31', 'yyyy/MM/dd '))
Order by employee ID

The analysis logic is as follows:

First, in the select section (marked in red), find the field I need: 'AB. Meaning amortized region'. The field is derived from the' AB 'table ';

Then in the from section (Green Flag) to obtain the actual table name 'fnd _ lookup_values '(' AB 'is the abbreviation of 'fnd _ lookup_values ), this table is only a table that stores code and actual value pairs, rather than a table that we really want to find to store personal legal information;

Then, in the where section (blue logo), we found the table that hs. segment21 = AB. lookup_code is associated with the table, abbreviated as 'hs. The corresponding table hr_soft_coding_keyflex is obtained from the from part. by querying this table, the table that stores the legal information is established as hr_soft_coding_keyflex,

However, the actual legal information in personal information cannot be determined, and per_all_assignments_f is related to personal distribution information, and the where section also finds the code HS. soft_coding_keyflex_id = PAAF. soft_coding_keyflex_id determines that the HS table and PAAF indicate that they are associated through the foreign key soft_coding_keyflex_id.

So far, we have found the table for storing personal legal information: hr_soft_coding_keyflex

And the contact key soft_coding_keyflex_id between the personal distribution information table per_all_assignments_f and the legal information table hr_soft_coding_keyflex.

Therefore, if I only want to retrieve the legal information, the Code is as follows:

select PAPF. last_name, PAPF. employee_number, PAPF. person_id, PAAF. assignment_id, hsck. segment1, hsck. segment20, hsck. segment21
from hr_soft_coding_keyflex hsck,
per_all_assignments_f PAAF,
per_all_people_f PAPF
where PAPF. person_id = PAAF. person_id and
PAAF. soft_coding_keyflex_id = hsck. soft_coding_keyflex_id and
PAPF. employee_number = '20140901' and
sysdate between PAPF. required tive_start_date and PAPF. required tive_end_date and
sysdate between PAAF. required tive_start_date and PAAF. required tive_end_date