VIEW: APPS.HRI_CS_SALARY_CMPNT_V

Source

View Text - Preformatted

SELECT pcp.change_amount_n cmpnt_change , pcp_sub.total_change total_change , pro.proposed_salary_n salary , pro.proposed_salary_n - pcp_sub.total_change prev_salary , DECODE(pcp_sub.total_change ,0 , to_number(NULL) ,pcp.change_amount_n/pcp_sub.total_change) cmpnt_of_ttl_change_prcnt , DECODE((pro.proposed_salary_n - pcp_sub.total_change) ,0 , to_number(NULL) ,(pcp.change_amount_n) /(pro.proposed_salary_n - pcp_sub.total_change)) cmpnt_of_prev_sal_prcnt , pcp.pay_proposal_id pay_proposal_id , NVL(pcp.component_reason,'NA_EDW') salary_cmpnt_reason_code , pro.change_date event_date , pcp.component_id component_id , pro.multiple_components multiple_cmpnt_flag_code , pcp.business_group_id business_group_id , GREATEST(pcp.last_update_date ,pcp_sub.last_update_date) last_change_date FROM per_pay_proposal_components pcp , per_pay_proposals pro , (SELECT SUM(pcp_ttl.change_amount_n) total_change , pcp_ttl.pay_proposal_id pay_proposal_id , MAX(pcp_ttl.last_update_date) last_update_date FROM per_pay_proposal_components pcp_ttl GROUP BY pcp_ttl.pay_Proposal_id ) pcp_sub WHERE pcp.approved = 'Y' AND pro.approved = 'Y' AND pro.multiple_components = 'Y' AND pcp.pay_proposal_id = pro.pay_proposal_id AND pcp.pay_proposal_id = pcp_sub.pay_proposal_id UNION ALL SELECT pro.proposed_salary_n - NVL(pro_prev.proposed_salary_n,0) cmpnt_change , pro.proposed_salary_n - NVL(pro_prev.proposed_salary_n,0) total_change , pro.proposed_salary_n salary , pro_prev.proposed_salary_n prev_salary , 1 cmpnt_of_ttl_change_prcnt , DECODE(NVL(pro_prev.proposed_salary_n,0) ,0, to_number(NULL) ,(pro.proposed_salary_n - pro_prev.proposed_salary_n) /pro_prev.proposed_salary_n) cmpnt_of_prev_sal_prcnt , pro.pay_proposal_id pay_proposal_id , NVL(pro.proposal_reason,'NA_EDW') salary_cmpnt_reason_code , pro.change_date event_date , -1 component_id , pro.multiple_components multiple_cmpnt_flag_code , pro.business_group_id business_group_id , pro.last_update_date last_update_date FROM per_pay_proposals pro , per_pay_proposals pro_prev WHERE pro.approved = 'Y' AND pro.multiple_components = 'N' AND pro.assignment_id = pro_prev.assignment_id(+) AND pro.last_change_date = pro_prev.change_date(+) WITH READ ONLY

View Text - HTML Formatted

SELECT PCP.CHANGE_AMOUNT_N CMPNT_CHANGE
, PCP_SUB.TOTAL_CHANGE TOTAL_CHANGE
, PRO.PROPOSED_SALARY_N SALARY
, PRO.PROPOSED_SALARY_N - PCP_SUB.TOTAL_CHANGE PREV_SALARY
, DECODE(PCP_SUB.TOTAL_CHANGE
, 0
, TO_NUMBER(NULL)
, PCP.CHANGE_AMOUNT_N/PCP_SUB.TOTAL_CHANGE) CMPNT_OF_TTL_CHANGE_PRCNT
, DECODE((PRO.PROPOSED_SALARY_N - PCP_SUB.TOTAL_CHANGE)
, 0
, TO_NUMBER(NULL)
, (PCP.CHANGE_AMOUNT_N) /(PRO.PROPOSED_SALARY_N - PCP_SUB.TOTAL_CHANGE)) CMPNT_OF_PREV_SAL_PRCNT
, PCP.PAY_PROPOSAL_ID PAY_PROPOSAL_ID
, NVL(PCP.COMPONENT_REASON
, 'NA_EDW') SALARY_CMPNT_REASON_CODE
, PRO.CHANGE_DATE EVENT_DATE
, PCP.COMPONENT_ID COMPONENT_ID
, PRO.MULTIPLE_COMPONENTS MULTIPLE_CMPNT_FLAG_CODE
, PCP.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, GREATEST(PCP.LAST_UPDATE_DATE
, PCP_SUB.LAST_UPDATE_DATE) LAST_CHANGE_DATE
FROM PER_PAY_PROPOSAL_COMPONENTS PCP
, PER_PAY_PROPOSALS PRO
, (SELECT SUM(PCP_TTL.CHANGE_AMOUNT_N) TOTAL_CHANGE
, PCP_TTL.PAY_PROPOSAL_ID PAY_PROPOSAL_ID
, MAX(PCP_TTL.LAST_UPDATE_DATE) LAST_UPDATE_DATE
FROM PER_PAY_PROPOSAL_COMPONENTS PCP_TTL GROUP BY PCP_TTL.PAY_PROPOSAL_ID ) PCP_SUB
WHERE PCP.APPROVED = 'Y'
AND PRO.APPROVED = 'Y'
AND PRO.MULTIPLE_COMPONENTS = 'Y'
AND PCP.PAY_PROPOSAL_ID = PRO.PAY_PROPOSAL_ID
AND PCP.PAY_PROPOSAL_ID = PCP_SUB.PAY_PROPOSAL_ID UNION ALL SELECT PRO.PROPOSED_SALARY_N - NVL(PRO_PREV.PROPOSED_SALARY_N
, 0) CMPNT_CHANGE
, PRO.PROPOSED_SALARY_N - NVL(PRO_PREV.PROPOSED_SALARY_N
, 0) TOTAL_CHANGE
, PRO.PROPOSED_SALARY_N SALARY
, PRO_PREV.PROPOSED_SALARY_N PREV_SALARY
, 1 CMPNT_OF_TTL_CHANGE_PRCNT
, DECODE(NVL(PRO_PREV.PROPOSED_SALARY_N
, 0)
, 0
, TO_NUMBER(NULL)
, (PRO.PROPOSED_SALARY_N - PRO_PREV.PROPOSED_SALARY_N) /PRO_PREV.PROPOSED_SALARY_N) CMPNT_OF_PREV_SAL_PRCNT
, PRO.PAY_PROPOSAL_ID PAY_PROPOSAL_ID
, NVL(PRO.PROPOSAL_REASON
, 'NA_EDW') SALARY_CMPNT_REASON_CODE
, PRO.CHANGE_DATE EVENT_DATE
, -1 COMPONENT_ID
, PRO.MULTIPLE_COMPONENTS MULTIPLE_CMPNT_FLAG_CODE
, PRO.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PRO.LAST_UPDATE_DATE LAST_UPDATE_DATE
FROM PER_PAY_PROPOSALS PRO
, PER_PAY_PROPOSALS PRO_PREV
WHERE PRO.APPROVED = 'Y'
AND PRO.MULTIPLE_COMPONENTS = 'N'
AND PRO.ASSIGNMENT_ID = PRO_PREV.ASSIGNMENT_ID(+)
AND PRO.LAST_CHANGE_DATE = PRO_PREV.CHANGE_DATE(+) WITH READ ONLY