eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.HR_ADP_COSTING_V

Source

View Text - Preformatted


SELECT ref.company_code_equivalent , hou.name , hs.segment1 , pasg.organization_id , pp.employee_number , pp.business_group_id , pp.person_id , decode(ptp.number_per_fiscal_year, 52,'W',24,'S',26,'B', 12,'M', ptp.number_per_fiscal_year) , pasg.assignment_id , pasg.assignment_number , pasg.primary_flag , pcf.effective_start_date , pcf.effective_end_date , decode(pasg.primary_flag, 'Y',0,pasg.assignment_sequence) , (pcf.proportion*100) , 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 , pca.segment1||pca.segment2||pca.segment3 , greatest ( decode( greatest( trunc(hr_adp.get_adp_extract_date), nvl(ref.final_process_date, to_date ('1900/01/01','YYYY/MM/DD'))), trunc(hr_adp.get_adp_extract_date), greatest(nvl(ref.last_update_date, to_date('1900/01/01', 'YYYY/MM/DD')), nvl(ref.final_process_date, to_date('1900/01/01', 'YYYY/MM/DD')), nvl(pp.last_update_date, to_date('1900/01/01', 'YYYY/MM/DD')), pp.effective_start_date, nvl(pasg.last_update_date, to_date('1900/01/01', 'YYYY/MM/DD')), pasg.effective_start_date, pcf.effective_start_date, nvl(pcf.last_update_date, to_date('1900/01/01', 'YYYY/MM/DD'))), greatest(nvl(ref.last_update_date, to_date('1900/01/01', 'YYYY/MM/DD')), nvl(pp.last_update_date, to_date('1900/01/01', 'YYYY/MM/DD')), pp.effective_start_date, nvl(pasg.last_update_date, to_date('1900/01/01', 'YYYY/MM/DD')), pasg.effective_start_date, pcf.effective_start_date, nvl(pcf.last_update_date, to_date('1900/01/01', 'YYYY/MM/DD')))), ref.date_start) from hr_adp_emp_ref_v ref , per_all_people_f pp , per_periods_of_service pps , pay_cost_allocation_keyflex pca , pay_cost_allocations_f pcf , hr_all_organization_units hou , hr_soft_coding_keyflex hs , per_all_assignments_f pasg , per_time_period_types ptp , pay_payrolls_x ppr WHERE pasg.person_id = pp.person_id and pasg.assignment_type = 'E' and pasg.assignment_id = ref.assignment_id and pasg.rowid = ref.asg_rowid and pps.person_id = pasg.person_id and pps.period_of_service_id= pasg.period_of_service_id and ptp.period_type = ppr.period_type and ppr.payroll_id = pasg.payroll_id and hs.soft_coding_keyflex_id = pasg.soft_coding_keyflex_id and hs.segment1 = hou.organization_id and pca.cost_allocation_keyflex_id = pcf.cost_allocation_keyflex_id and pcf.assignment_id = pasg.assignment_id and pp.employee_number is not null and trunc(hr_adp.get_adp_extract_date) between pp.effective_start_date and pp.effective_end_date and pcf.effective_start_date=(select max(pcf2.effective_start_date) from pay_cost_allocations_f pcf2 where (pcf2.effective_start_date <=trunc(hr_adp.get_adp_extract_date ) and pcf2.assignment_id = pcf.assignment_id)) and pasg.effective_start_date = (select max(a2.effective_start_date) from per_all_assignments_f a2 where (a2.effective_start_date <= trunc(hr_adp.get_adp_extract_date) and a2.assignment_id=pasg.assignment_id))

View Text - HTML Formatted

SELECT REF.COMPANY_CODE_EQUIVALENT
, HOU.NAME
, HS.SEGMENT1
, PASG.ORGANIZATION_ID
, PP.EMPLOYEE_NUMBER
, PP.BUSINESS_GROUP_ID
, PP.PERSON_ID
, DECODE(PTP.NUMBER_PER_FISCAL_YEAR
, 52
, 'W'
, 24
, 'S'
, 26
, 'B'
, 12
, 'M'
, PTP.NUMBER_PER_FISCAL_YEAR)
, PASG.ASSIGNMENT_ID
, PASG.ASSIGNMENT_NUMBER
, PASG.PRIMARY_FLAG
, PCF.EFFECTIVE_START_DATE
, PCF.EFFECTIVE_END_DATE
, DECODE(PASG.PRIMARY_FLAG
, 'Y'
, 0
, PASG.ASSIGNMENT_SEQUENCE)
, (PCF.PROPORTION*100)
, 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
, PCA.SEGMENT1||PCA.SEGMENT2||PCA.SEGMENT3
, GREATEST ( DECODE( GREATEST( TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
, NVL(REF.FINAL_PROCESS_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD')))
, TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
, GREATEST(NVL(REF.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(REF.FINAL_PROCESS_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PP.EFFECTIVE_START_DATE
, NVL(PASG.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PASG.EFFECTIVE_START_DATE
, PCF.EFFECTIVE_START_DATE
, NVL(PCF.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, GREATEST(NVL(REF.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PP.EFFECTIVE_START_DATE
, NVL(PASG.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PASG.EFFECTIVE_START_DATE
, PCF.EFFECTIVE_START_DATE
, NVL(PCF.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))))
, REF.DATE_START)
FROM HR_ADP_EMP_REF_V REF
, PER_ALL_PEOPLE_F PP
, PER_PERIODS_OF_SERVICE PPS
, PAY_COST_ALLOCATION_KEYFLEX PCA
, PAY_COST_ALLOCATIONS_F PCF
, HR_ALL_ORGANIZATION_UNITS HOU
, HR_SOFT_CODING_KEYFLEX HS
, PER_ALL_ASSIGNMENTS_F PASG
, PER_TIME_PERIOD_TYPES PTP
, PAY_PAYROLLS_X PPR
WHERE PASG.PERSON_ID = PP.PERSON_ID
AND PASG.ASSIGNMENT_TYPE = 'E'
AND PASG.ASSIGNMENT_ID = REF.ASSIGNMENT_ID
AND PASG.ROWID = REF.ASG_ROWID
AND PPS.PERSON_ID = PASG.PERSON_ID
AND PPS.PERIOD_OF_SERVICE_ID= PASG.PERIOD_OF_SERVICE_ID
AND PTP.PERIOD_TYPE = PPR.PERIOD_TYPE
AND PPR.PAYROLL_ID = PASG.PAYROLL_ID
AND HS.SOFT_CODING_KEYFLEX_ID = PASG.SOFT_CODING_KEYFLEX_ID
AND HS.SEGMENT1 = HOU.ORGANIZATION_ID
AND PCA.COST_ALLOCATION_KEYFLEX_ID = PCF.COST_ALLOCATION_KEYFLEX_ID
AND PCF.ASSIGNMENT_ID = PASG.ASSIGNMENT_ID
AND PP.EMPLOYEE_NUMBER IS NOT NULL
AND TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE) BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
AND PCF.EFFECTIVE_START_DATE=(SELECT MAX(PCF2.EFFECTIVE_START_DATE)
FROM PAY_COST_ALLOCATIONS_F PCF2
WHERE (PCF2.EFFECTIVE_START_DATE <=TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE )
AND PCF2.ASSIGNMENT_ID = PCF.ASSIGNMENT_ID))
AND PASG.EFFECTIVE_START_DATE = (SELECT MAX(A2.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F A2
WHERE (A2.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND A2.ASSIGNMENT_ID=PASG.ASSIGNMENT_ID))