PAY Payroll Tax Report by Legal Employer
Description
Categories: BI Publisher
Application: Payroll
Source: Payroll Tax Report by Legal Employer (XML)
Short Name: PYAUPYL_XML
DB package: PAY_PYAUPYT_XMLP_PKG
Source: Payroll Tax Report by Legal Employer (XML)
Short Name: PYAUPYL_XML
DB package: PAY_PYAUPYT_XMLP_PKG
Run
PAY Payroll Tax Report by Legal Employer and other Oracle EBS reports with Blitz Report™ on our demo environment
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 |
