VIEW: APPS.HR_PAY_INTERFACE_ASG_COSTING_V

Source

View Text - Preformatted

SELECT
  pp.employee_number
, asg.primary_flag
, asg.assignment_number
, pca.segment1
, pca.segment2
, pca.segment3
, pca.segment4
, pca.segment5
, pca.segment6
, pca.segment7
, pca.segment8
, pca.segment9
, pca.segment10
, pca.segment11
, pca.segment12
, pca.segment13
, pca.segment14
, pca.segment15
, pca.segment16
, pca.segment17
, pca.segment18
, pca.segment19
, pca.segment20
, pca.segment21
, pca.segment22
, pca.segment23
, pca.segment24
, pca.segment25
, pca.segment26
, pca.segment27
, pca.segment28
, pca.segment29
, pca.segment30
, bgr.name
, org.name
, pp.person_id
, asg.assignment_id
, bgr.business_group_id
, org.organization_id
, pcf.cost_allocation_id
,GREATEST(
  GREATEST(NVL(pp.last_update_date,
           TO_DATE('1900/01/01','YYYY/MM/DD'))
          ,pp.effective_start_date)
, GREATEST(NVL(asg.last_update_date,
           TO_DATE('1900/01/01','YYYY/MM/DD'))
           ,asg.effective_start_date)
, GREATEST(NVL(pcf.last_update_date,
           TO_DATE('1900/01/01','YYYY/MM/DD'))
           ,pcf.effective_start_date)
 )
, GREATEST(NVL(pp.last_update_date,
           TO_DATE('1900/01/01','YYYY/MM/DD'))
          ,pp.effective_start_date)
, GREATEST(NVL(asg.last_update_date,
           TO_DATE('1900/01/01','YYYY/MM/DD'))
           ,asg.effective_start_date)
, GREATEST(NVL(pcf.last_update_date,
           TO_DATE('1900/01/01','YYYY/MM/DD'))
           ,pcf.effective_start_date)
FROM
  per_all_people_f                pp
, hr_all_organization_units       bgr
, hr_all_organization_units       org
, per_all_assignments_f           asg
, pay_cost_allocation_keyflex     pca
, pay_cost_allocations_f          pcf
WHERE
  pp.business_group_id = bgr.organization_id
  and asg.organization_id = org.organization_id
  and pca.cost_allocation_keyflex_id = pcf.cost_allocation_keyflex_id
  and pcf.assignment_id       = asg.assignment_id
  and pcf.effective_start_date=(select max(pcf2.effective_start_date)
                                from pay_cost_allocations_f pcf2
                                where (pcf2.effective_start_date <=
                                  HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE
                                and pcf2.assignment_id  = pcf.assignment_id))
  and HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE between
      pp.effective_start_date and pp.effective_end_date
-- Join to the assignments table.
-- Can return more than one row.
  and pp.person_id = asg.person_id
  and asg.effective_start_date=(select max(asgi2.effective_start_date)
                                from per_all_assignments_f asgi2
                                where (asgi2.effective_start_date <=
                                  HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE
                                and asgi2.assignment_id  = asg.assignment_id))

View Text - HTML Formatted

SELECT PP.EMPLOYEE_NUMBER
, ASG.PRIMARY_FLAG
, ASG.ASSIGNMENT_NUMBER
, PCA.SEGMENT1
, PCA.SEGMENT2
, PCA.SEGMENT3
, PCA.SEGMENT4
, PCA.SEGMENT5
, PCA.SEGMENT6
, PCA.SEGMENT7
, PCA.SEGMENT8
, PCA.SEGMENT9
, PCA.SEGMENT10
, PCA.SEGMENT11
, PCA.SEGMENT12
, PCA.SEGMENT13
, PCA.SEGMENT14
, PCA.SEGMENT15
, PCA.SEGMENT16
, PCA.SEGMENT17
, PCA.SEGMENT18
, PCA.SEGMENT19
, PCA.SEGMENT20
, PCA.SEGMENT21
, PCA.SEGMENT22
, PCA.SEGMENT23
, PCA.SEGMENT24
, PCA.SEGMENT25
, PCA.SEGMENT26
, PCA.SEGMENT27
, PCA.SEGMENT28
, PCA.SEGMENT29
, PCA.SEGMENT30
, BGR.NAME
, ORG.NAME
, PP.PERSON_ID
, ASG.ASSIGNMENT_ID
, BGR.BUSINESS_GROUP_ID
, ORG.ORGANIZATION_ID
, PCF.COST_ALLOCATION_ID
, GREATEST( GREATEST(NVL(PP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PP.EFFECTIVE_START_DATE)
, GREATEST(NVL(ASG.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, ASG.EFFECTIVE_START_DATE)
, GREATEST(NVL(PCF.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PCF.EFFECTIVE_START_DATE) )
, GREATEST(NVL(PP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PP.EFFECTIVE_START_DATE)
, GREATEST(NVL(ASG.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, ASG.EFFECTIVE_START_DATE)
, GREATEST(NVL(PCF.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PCF.EFFECTIVE_START_DATE) FROM PER_ALL_PEOPLE_F PP
, HR_ALL_ORGANIZATION_UNITS BGR
, HR_ALL_ORGANIZATION_UNITS ORG
, PER_ALL_ASSIGNMENTS_F ASG
, PAY_COST_ALLOCATION_KEYFLEX PCA
, PAY_COST_ALLOCATIONS_F PCF WHERE PP.BUSINESS_GROUP_ID = BGR.ORGANIZATION_ID
AND ASG.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND PCA.COST_ALLOCATION_KEYFLEX_ID = PCF.COST_ALLOCATION_KEYFLEX_ID
AND PCF.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND PCF.EFFECTIVE_START_DATE=(SELECT MAX(PCF2.EFFECTIVE_START_DATE)
FROM PAY_COST_ALLOCATIONS_F PCF2
WHERE (PCF2.EFFECTIVE_START_DATE <= HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE
AND PCF2.ASSIGNMENT_ID = PCF.ASSIGNMENT_ID))
AND HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE -- JOIN TO THE ASSIGNMENTS TABLE. -- CAN RETURN MORE THAN ONE ROW.
AND PP.PERSON_ID = ASG.PERSON_ID
AND ASG.EFFECTIVE_START_DATE=(SELECT MAX(ASGI2.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F ASGI2
WHERE (ASGI2.EFFECTIVE_START_DATE <= HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE
AND ASGI2.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID))