ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223F3103869E053B46B63588994 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 Y PAY PYAUPYL_XML US PAY Payroll Tax Report by Legal Employer Application: Payroll Source: Payroll Tax Report by Legal Employer (XML) Short Name: PYAUPYL_XML DB package: PAY_PYAUPYT_XMLP_PKG BI Publisher :cp_currency :cp_dge :cp_legal_employer :cp_message :cp_ot_message :cp_report_type :cp_tax_state :final_date :p_act :p_act_lp :p_business_group_id :p_business_group_id_lp :p_conc_request_id :p_end_date :p_end_date_lp :p_end_date_lp_1 :p_legal_employer :p_legal_employer_lp :p_nsw :p_nsw_lp :p_nt :p_nt_lp :p_payroll_action_id :p_payroll_action_id_lp :p_period :p_period_end_lp :p_period_end_lp_1 :p_period_lp :p_period_lp_1 :p_qld :p_qld_lp :p_report_name :p_report_name_lp :p_report_type :p_report_type_lp :p_sa :p_sa_lp :p_start_date :p_start_date_lp :p_start_date_lp_1 :p_state_code :p_tas :p_tas_lp :p_tax_state :p_tax_state_lp :p_vic :p_vic_lp :p_wa :p_wa_lp