PAY Payroll Tax Report by Legal Employer

Description
Categories: BI Publisher
Columns: Employee Number, Emp Assignment Id, Action Context Id, Bal Assignment Id, Person Id, Full Name, State Desc, Legal Employer, State Code, Taxable Income ...
Application: Payroll
Source: Payroll Tax Report by Legal Employer (XML)
Short Name: PYAUPYL_XML
DB package: PAY_PYAUPYT_XMLP_PKG
 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