PJC_PAYROLL_PER_ASSIGNMENT

Details

Schema: FUSION
Object owner: PJC
Object type: VIEW

Columns

Name
RULE_HDR_ID PERSON_ID ASSIGNMENT_ID START_DATE_ACTIVE END_DATE_ACTIVE TYPE TIME_CARD_DISTR_FLAG LEGISLATIVE_DATA_GROUP_ID SEGMENT1 SEGMENT2 SEGMENT3 SEGMENT4 SEGMENT5 SEGMENT6 SEGMENT7 SEGMENT8 SEGMENT9 SEGMENT10 SEGMENT11 SEGMENT12 SEGMENT13 SEGMENT14 SEGMENT15 SEGMENT16 SEGMENT17 SEGMENT18 SEGMENT19 SEGMENT20 SEGMENT21 SEGMENT22 SEGMENT23 SEGMENT24 SEGMENT25 SEGMENT26 SEGMENT27 SEGMENT28 SEGMENT29 SEGMENT30

Name

RULE_HDR_ID

PERSON_ID

ASSIGNMENT_ID

START_DATE_ACTIVE

END_DATE_ACTIVE

TYPE

TIME_CARD_DISTR_FLAG

LEGISLATIVE_DATA_GROUP_ID

SEGMENT1

SEGMENT2

SEGMENT3

SEGMENT4

SEGMENT5

SEGMENT6

SEGMENT7

SEGMENT8

SEGMENT9

SEGMENT10

SEGMENT11

SEGMENT12

SEGMENT13

SEGMENT14

SEGMENT15

SEGMENT16

SEGMENT17

SEGMENT18

SEGMENT19

SEGMENT20

SEGMENT21

SEGMENT22

SEGMENT23

SEGMENT24

SEGMENT25

SEGMENT26

SEGMENT27

SEGMENT28

SEGMENT29

SEGMENT30

Query

SQL_Statement
SELECT rules.rule_hdr_id, rules.person_id, rules.assignment_id, rules.start_date_active, rules.end_date_active, rules.type, rules.timecard_dist_flag time_card_distr_flag, hdr.legislative_data_group_id, hcm_acct.segment1, hcm_acct.segment2, hcm_acct.segment3, hcm_acct.segment4, hcm_acct.segment5, hcm_acct.segment6, hcm_acct.segment7, hcm_acct.segment8, hcm_acct.segment9, hcm_acct.segment10, hcm_acct.segment11, hcm_acct.segment12, hcm_acct.segment13, hcm_acct.segment14, hcm_acct.segment15, hcm_acct.segment16, hcm_acct.segment17, hcm_acct.segment18, hcm_acct.segment19, hcm_acct.segment20, hcm_acct.segment21, hcm_acct.segment22, hcm_acct.segment23, hcm_acct.segment24, hcm_acct.segment25, hcm_acct.segment26, hcm_acct.segment27, hcm_acct.segment28, hcm_acct.segment29, hcm_acct.segment30 FROM pjc_alloc_rules_all rules, pjc_rules_hdr hdr, ( SELECT alloc.source_id, alloc.cost_allocation_id, alloc.legislative_data_group_id, alloc.cost_allocation_record_id, acct.cost_alloc_account_id, acct.segment1, acct.segment2, acct.segment3, acct.segment4, acct.segment5, acct.segment6, acct.segment7, acct.segment8, acct.segment9, acct.segment10, acct.segment11, acct.segment12, acct.segment13, acct.segment14, acct.segment15, acct.segment16, acct.segment17, acct.segment18, acct.segment19, acct.segment20, acct.segment21, acct.segment22, acct.segment23, acct.segment24, acct.segment25, acct.segment26, acct.segment27, acct.segment28, acct.segment29, acct.segment30 FROM pay_cost_allocations_f alloc, pay_cost_alloc_accounts acct WHERE alloc.cost_allocation_record_id = acct.cost_allocation_record_id AND alloc.source_type = 'PPMLD' ) hcm_acct WHERE rules.ld_version_status = 'A' AND rules.rule_hdr_id = hdr.rule_hdr_id AND nvl(rules.person_id, 1) = nvl(hdr.person_id, 1) AND nvl(rules.element_type_id, 1) = nvl(hdr.element_type_id, 1) AND nvl(rules.assignment_id, 1) = nvl(hdr.assignment_id, 1) AND hdr.type IN ( 'ASE', 'ASG', 'KFF') AND hdr.rule_hdr_id = hcm_acct.source_id (+) union all SELECT asd_ver.rule_hdr_id, asg.person_id, asg.assignment_id, greatest(asg.effective_start_date, asd_ver.start_date_active) start_date_active, least(asg.effective_end_date, asd_ver.end_date_active) end_date_active, 'ASG' type, asd_ver.timecard_dist_flag time_card_distr_flag, to_number(null) as legislative_data_group_id, to_char(null) as segment1, to_char(null) as segment2, to_char(null) as segment3, to_char(null) as segment4, to_char(null) as segment5, to_char(null) as segment6, to_char(null) as segment7, to_char(null) as segment8, to_char(null) as segment9, to_char(null) as segment10, to_char(null) as segment11, to_char(null) as segment12, to_char(null) as segment13, to_char(null) as segment14, to_char(null) as segment15, to_char(null) as segment16, to_char(null) as segment17, to_char(null) as segment18, to_char(null) as segment19, to_char(null) as segment20, to_char(null) as segment21, to_char(null) as segment22, to_char(null) as segment23, to_char(null) as segment24, to_char(null) as segment25, to_char(null) as segment26, to_char(null) as segment27, to_char(null) as segment28, to_char(null) as segment29, to_char(null) as segment30 FROM per_all_assignments_m asg, ( SELECT rul.rule_hdr_id, ver.rule_id, ver.timecard_dist_flag, hy.child_organization_id AS organization_id, pal.org_id, rul.type, rul.tree_structure_flag, ver.start_date_active, ver.end_date_active FROM pjf_org_hierarchy_denorm hy, pjf_bu_impl_all tr, pjf_all_organizations pal, pjc_rules_hdr rul, pjc_alloc_rules_all ver WHERE hy.parent_organization_id = pal.organization_id AND pal.org_id = tr.org_id AND tr.exp_tree_structure_code = hy.tree_structure_code AND tr.exp_tree_version_id = hy.tree_version_id AND tr.exp_tree_code = hy.tree_code AND hy.parent_organization_id = rul.organization_id AND pal.org_id = rul.org_id AND pal.pa_org_use_type = 'EXPENDITURES' AND rul.rule_hdr_id = ver.rule_hdr_id AND ver.ld_version_status = 'A' AND rul.tree_structure_flag = 'Y' AND rul.type = 'ASD' UNION ALL SELECT rul.rule_hdr_id, ver.rule_id, ver.timecard_dist_flag, rul.organization_id, rul.org_id, rul.type, rul.tree_structure_flag, ver.start_date_active, ver.end_date_active FROM pjc_rules_hdr rul, pjc_alloc_rules_all ver WHERE rul.rule_hdr_id = ver.rule_hdr_id AND ver.ld_version_status = 'A' AND rul.tree_structure_flag = 'N' AND rul.type = 'ASD' ) asd_ver WHERE asg.business_unit_id = asd_ver.org_id and (asg.organization_id = asd_ver.organization_id or asd_ver.organization_id is null ) AND greatest(asg.effective_start_date, asd_ver.start_date_active) BETWEEN asd_ver.start_date_active AND asd_ver.end_date_active AND least(asg.effective_end_date, asd_ver.end_date_active) BETWEEN asd_ver.start_date_active AND asd_ver.end_date_active AND asg.effective_latest_change = 'Y'

SQL_Statement

SELECT

rules.rule_hdr_id,

rules.person_id,

rules.assignment_id,

rules.start_date_active,

rules.end_date_active,

rules.type,

rules.timecard_dist_flag time_card_distr_flag,

hdr.legislative_data_group_id,

hcm_acct.segment1,

hcm_acct.segment2,

hcm_acct.segment3,

hcm_acct.segment4,

hcm_acct.segment5,

hcm_acct.segment6,

hcm_acct.segment7,

hcm_acct.segment8,

hcm_acct.segment9,

hcm_acct.segment10,

hcm_acct.segment11,

hcm_acct.segment12,

hcm_acct.segment13,

hcm_acct.segment14,

hcm_acct.segment15,

hcm_acct.segment16,

hcm_acct.segment17,

hcm_acct.segment18,

hcm_acct.segment19,

hcm_acct.segment20,

hcm_acct.segment21,

hcm_acct.segment22,

hcm_acct.segment23,

hcm_acct.segment24,

hcm_acct.segment25,

hcm_acct.segment26,

hcm_acct.segment27,

hcm_acct.segment28,

hcm_acct.segment29,

hcm_acct.segment30

FROM

pjc_alloc_rules_all rules,

pjc_rules_hdr hdr,

(

SELECT

alloc.source_id,

alloc.cost_allocation_id,

alloc.legislative_data_group_id,

alloc.cost_allocation_record_id,

acct.cost_alloc_account_id,

acct.segment1,

acct.segment2,

acct.segment3,

acct.segment4,

acct.segment5,

acct.segment6,

acct.segment7,

acct.segment8,

acct.segment9,

acct.segment10,

acct.segment11,

acct.segment12,

acct.segment13,

acct.segment14,

acct.segment15,

acct.segment16,

acct.segment17,

acct.segment18,

acct.segment19,

acct.segment20,

acct.segment21,

acct.segment22,

acct.segment23,

acct.segment24,

acct.segment25,

acct.segment26,

acct.segment27,

acct.segment28,

acct.segment29,

acct.segment30

FROM

pay_cost_allocations_f alloc,

pay_cost_alloc_accounts acct

WHERE

alloc.cost_allocation_record_id = acct.cost_allocation_record_id

AND alloc.source_type = 'PPMLD'

) hcm_acct

WHERE

rules.ld_version_status = 'A'

AND rules.rule_hdr_id = hdr.rule_hdr_id

AND nvl(rules.person_id, 1) = nvl(hdr.person_id, 1)

AND nvl(rules.element_type_id, 1) = nvl(hdr.element_type_id, 1)

AND nvl(rules.assignment_id, 1) = nvl(hdr.assignment_id, 1)

AND hdr.type IN ( 'ASE', 'ASG', 'KFF')

AND hdr.rule_hdr_id = hcm_acct.source_id (+)

union all

SELECT

asd_ver.rule_hdr_id,

asg.person_id,

asg.assignment_id,

greatest(asg.effective_start_date, asd_ver.start_date_active) start_date_active,

least(asg.effective_end_date, asd_ver.end_date_active) end_date_active,

'ASG' type,

asd_ver.timecard_dist_flag time_card_distr_flag,

to_number(null) as legislative_data_group_id,

to_char(null) as segment1,

to_char(null) as segment2,

to_char(null) as segment3,

to_char(null) as segment4,

to_char(null) as segment5,

to_char(null) as segment6,

to_char(null) as segment7,

to_char(null) as segment8,

to_char(null) as segment9,

to_char(null) as segment10,

to_char(null) as segment11,

to_char(null) as segment12,

to_char(null) as segment13,

to_char(null) as segment14,

to_char(null) as segment15,

to_char(null) as segment16,

to_char(null) as segment17,

to_char(null) as segment18,

to_char(null) as segment19,

to_char(null) as segment20,

to_char(null) as segment21,

to_char(null) as segment22,

to_char(null) as segment23,

to_char(null) as segment24,

to_char(null) as segment25,

to_char(null) as segment26,

to_char(null) as segment27,

to_char(null) as segment28,

to_char(null) as segment29,

to_char(null) as segment30

FROM

per_all_assignments_m asg,

(

SELECT

rul.rule_hdr_id,

ver.rule_id,

ver.timecard_dist_flag,

hy.child_organization_id AS organization_id,

pal.org_id,

rul.type,

rul.tree_structure_flag,

ver.start_date_active,

ver.end_date_active

FROM

pjf_org_hierarchy_denorm hy,

pjf_bu_impl_all tr,

pjf_all_organizations pal,

pjc_rules_hdr rul,

pjc_alloc_rules_all ver

WHERE

hy.parent_organization_id = pal.organization_id

AND pal.org_id = tr.org_id

AND tr.exp_tree_structure_code = hy.tree_structure_code

AND tr.exp_tree_version_id = hy.tree_version_id

AND tr.exp_tree_code = hy.tree_code

AND hy.parent_organization_id = rul.organization_id

AND pal.org_id = rul.org_id

AND pal.pa_org_use_type = 'EXPENDITURES'

AND rul.rule_hdr_id = ver.rule_hdr_id

AND ver.ld_version_status = 'A'

AND rul.tree_structure_flag = 'Y'

AND rul.type = 'ASD'

UNION ALL

SELECT

rul.rule_hdr_id,

ver.rule_id,

ver.timecard_dist_flag,

rul.organization_id,

rul.org_id,

rul.type,

rul.tree_structure_flag,

ver.start_date_active,

ver.end_date_active

FROM

pjc_rules_hdr rul,

pjc_alloc_rules_all ver

WHERE

rul.rule_hdr_id = ver.rule_hdr_id

AND ver.ld_version_status = 'A'

AND rul.tree_structure_flag = 'N'

AND rul.type = 'ASD'

) asd_ver

WHERE

asg.business_unit_id = asd_ver.org_id

and (asg.organization_id = asd_ver.organization_id or asd_ver.organization_id is null )

AND greatest(asg.effective_start_date, asd_ver.start_date_active) BETWEEN asd_ver.start_date_active AND asd_ver.end_date_active

AND least(asg.effective_end_date, asd_ver.end_date_active) BETWEEN asd_ver.start_date_active AND asd_ver.end_date_active

AND asg.effective_latest_change = 'Y'