SELECT a.*,
first_value(PAY_PYAUPYT_XMLP_PKG.CP_ot_message_p) over(partition BY state_code,state_desc) CP_ot_message,
first_value(PAY_PYAUPYT_XMLP_PKG.CP_MESSAGE_p) over(partition BY state_code,state_desc) CP_MESSAGE,
sum(nvl(PAY_PYAUPYT_XMLP_PKG.cf_state_taxformula(NVL ( to_number (no_of_states ) , 0 ), CS_DGE_STATE_summary, CS_dge_group_name_summary, state_code, CS_STATE_TAXABLE_INCOME_Summ, NVL ( to_number ( le_taxable_income ) , 0 ), CS_No_oF_States_summary, CS_Tot_Tax_Income_LE_summ, to_date('01-'||month, 'DD-MM-YYYY'), last_day(to_date('01-'||month, 'DD-MM-YYYY'))), 0)/nvl(CS_STATE_TAXABLE_INCOME_Count, 1)) over(partition BY legal_employer,state_code,state_desc) CF_STATE_TAX,
'X' dummy
FROM
(SELECT b.*,
SUM(NVL(Taxable_income,0)) over(partition BY NVL(le_taxable_income,0),NVL(no_of_states,0),state_code,state_desc,month) CS_STATE_TAXABLE_INCOME_Summ,
count(NVL(Taxable_income,0)) over(partition BY NVL(le_taxable_income,0),NVL(no_of_states,0),state_code,state_desc,month) CS_STATE_TAXABLE_INCOME_Count,
first_value(dge_state) over(partition BY NVL(le_taxable_income,0),NVL(no_of_states,0),state_code,state_desc order by NVL(le_taxable_income,0),NVL(no_of_states,0),state_code,state_desc,month) CS_DGE_STATE_summary,
first_value(dge_group_name) over(partition BY NVL(le_taxable_income,0),NVL(no_of_states,0),state_code,state_desc order by NVL(le_taxable_income,0),NVL(no_of_states,0),state_code,state_desc,month) CS_dge_group_name_summary,
COUNT(state_code) over(partition BY legal_employer,month) CS_No_oF_States_summary,
SUM(NVL(Taxable_income,0)) over(partition BY legal_employer,month) CS_Tot_Tax_Income_LE_summ
FROM
(SELECT DISTINCT pai_emp.action_information1 employee_number ,
pai_emp.assignment_id emp_assignment_id ,
pai_bal.action_context_id ,
pai_bal.assignment_id bal_assignment_id ,
pai_emp.action_information2 person_id ,
pai_emp.action_information3 full_name ,
pai_emp.action_information4 state_desc ,
pai_emp.action_information5 legal_employer ,
pai_emp.action_information6 state_code ,
NVL(to_number(pai_bal.action_information11),0) Taxable_income ,
NVL(to_number(pai_bal.action_information1),0) Salaries_Wages ,
NVL(to_number(pai_bal.action_information2),0) Commissions ,
NVL(to_number(pai_bal.action_information3),0) Bonus_Allowances ,
NVL(to_number(pai_bal.action_information4),0) Director_Fees ,
NVL(to_number(pai_bal.action_information5),0) Termination_Payments ,
NVL(to_number(pai_bal.action_information6),0) Eligible_Term_Payments ,
NVL(to_number(pai_bal.action_information7),0) Fringe_Benefits ,
NVL(to_number(pai_bal.action_information9),0) Contractor_Payments ,
NVL(to_number(pai_bal.action_information8),0) Superannuation ,
NVL(to_number(pai_bal.action_information10),0) Other_Taxable_Payments ,
NVL(to_number(pai_bal.action_information13),0) le_taxable_income ,
NVL(to_number(pai_bal.action_information14),0) no_of_states ,
to_char(to_date(pai_bal.action_information16, 'DD-MM-YYYY'), 'MM-YYYY') month ,
pai_emp.action_information7 dge_state ,
pai_emp.action_information8 dge_legal_employer ,
pai_emp.action_information9 dge_group_name
--PAY_PYAUPYT_XMLP_PKG.CP_ot_message_p CP_ot_message ,
--PAY_PYAUPYT_XMLP_PKG.CP_MESSAGE_p CP_MESSAGE ,
--'X' dummy
FROM pay_action_information pai_bal,
pay_assignment_actions paa ,
pay_action_information pai_emp
WHERE PAI_EMP.ACTION_CONTEXT_ID =:P_PAYROLL_ACTION_ID_LP
AND PAI_EMP.ACTION_INFORMATION_CATEGORY = 'AU_PAYROLL_TAX_EMPLOYEE_DETAILS'
AND PAI_EMP.ACTION_CONTEXT_TYPE = 'PA'
AND PAI_BAL.ACTION_CONTEXT_ID = PAA.ASSIGNMENT_ACTION_ID
AND PAA.PAYROLL_ACTION_ID =:P_PAYROLL_ACTION_ID_LP
AND PAI_BAL.ACTION_CONTEXT_ID = PAA.ASSIGNMENT_ACTION_ID
AND PAI_BAL.ACTION_INFORMATION_CATEGORY = 'AU_PAYROLL_TAX_BALANCE_DETAILS'
AND PAI_BAL.ACTION_CONTEXT_TYPE = 'AAP'
AND PAI_EMP.ACTION_INFORMATION6 = PAI_BAL.ACTION_INFORMATION15
AND PAI_EMP.ASSIGNMENT_ID = PAI_BAL.ASSIGNMENT_ID
)b
ORDER BY b.Legal_employer,
b.state_desc ,
b.full_name
)a
ORDER BY a.Legal_employer,
a.state_desc,
a.full_name |