VIEW: APPS.PAY_IN_FORM_24Q_WEB_ADI_V

Source

View Text - Preformatted

SELECT asg.business_group_id business_group_id, pai.action_information1 assessment_year, pai.action_information13 payroll_name, pai.action_information2 period, nvl(TRUNC(to_date(pai.action_information10,'DD/MM/YYYY')),pai.effective_date) date_earned, pai.effective_date pre_payment_date, pep.employee_number emp_number, pep.full_name full_name, pai.action_information4 taxable_income, to_char(to_number(pai.action_information5) + to_number(pai.action_information6)) income_tax_deducted, pai.action_information7 surcharge_deducted, to_char(to_number(pai.action_information8) + to_number(pai.action_information9)) education_cess, to_char(to_number(pai.action_information5) + to_number(pai.action_information6) + to_number(pai.action_information7) + to_number(pai.action_information8) + to_number(pai.action_information9)) total_tax_deducted, NULL amount_deposited, NULL voucher_number, NULL flag, NULL last_updated_date, NULL element_entry_id, pai.action_information3 TAN, NULL purge_flag, asg.assignment_id assignment_id FROM pay_payroll_actions ppa, pay_assignment_actions paa, per_assignments_f asg, pay_element_types_f types, pay_element_links_f links, per_people_f pep, pay_action_information pai WHERE ppa.payroll_action_id = paa.payroll_action_id AND ppa.action_type = 'X' AND ppa.report_type = 'IN_PAYSLIP_ARCHIVE' AND ppa.action_status = 'C' AND ppa.business_group_id = asg.business_group_id AND ppa.business_group_id = pep.business_group_id AND pep.business_group_id = links.business_group_id AND asg.assignment_id = pai.assignment_id AND pai.assignment_id = asg.assignment_id AND pai.action_context_id = paa.assignment_action_id AND pep.person_id = asg.person_id AND pai.action_information_category = 'IN_TAX_BALANCES' AND types.element_name = 'Income Tax Challan Information' AND types.legislation_code = 'IN' AND links.element_type_id = types.element_type_id AND pai.effective_date BETWEEN types.effective_start_date AND types.effective_end_date AND pai.effective_date BETWEEN links.effective_start_date AND links.effective_end_date AND pai.effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date AND pai.effective_date BETWEEN pep.effective_start_date AND pep.effective_end_date AND NOT EXISTS (SELECT 1 FROM pay_element_entries_f entry WHERE entry.assignment_id = pai.assignment_id AND entry.element_type_id = types.element_type_id AND entry.element_link_id = links.element_link_id AND pai.effective_date BETWEEN entry.effective_start_date AND entry.effective_end_date) UNION ALL SELECT DISTINCT asg.business_group_id business_group_id, to_char(pay_in_utils.next_tax_year(nvl(ppa.date_earned, ppa.effective_date)), 'YYYY') || '-' || to_char(to_number(to_char(pay_in_utils.next_tax_year(nvl(ppa.date_earned, ppa.effective_date)), 'YYYY')) + 1) assessment_year, payroll.payroll_name payroll_name, to_char(pay_in_tax_utils.get_period_number(payroll.payroll_id, decode(ppa.action_type, 'I', nvl(ppa.date_earned, ppa.effective_date), pay_in_form_24q_web_adi.get_date_earned(paa.assignment_action_id)))) period, decode(ppa.action_type, 'I', nvl(ppa.date_earned, ppa.effective_date), pay_in_form_24q_web_adi.get_date_earned_ee(entry.element_entry_id)) date_earned, fnd_date.canonical_to_date(pay_in_form_24q_web_adi.get_ee_value(entry.element_entry_id, 'Payment Date', ppa.effective_date)) pre_payment_date, pep.employee_number emp_number, pep.full_name full_name, pay_in_form_24q_web_adi.get_ee_value(entry.element_entry_id, 'Taxable Income', ppa.effective_date) taxable_income, pay_in_form_24q_web_adi.get_ee_value(entry.element_entry_id, 'Income Tax Deducted', ppa.effective_date) income_tax_deducted, pay_in_form_24q_web_adi.get_ee_value(entry.element_entry_id, 'Surcharge Deducted', ppa.effective_date) surcharge_deducted, pay_in_form_24q_web_adi.get_ee_value(entry.element_entry_id, 'Education Cess Deducted', ppa.effective_date) education_cess, to_char(nvl(pay_in_form_24q_web_adi.get_ee_value(entry.element_entry_id, 'Income Tax Deducted', ppa.effective_date), 0) + nvl(pay_in_form_24q_web_adi.get_ee_value(entry.element_entry_id, 'Surcharge Deducted', ppa.effective_date), 0) + nvl(pay_in_form_24q_web_adi.get_ee_value(entry.element_entry_id, 'Education Cess Deducted', ppa.effective_date), 0)) total_tax_deducted, pay_in_form_24q_web_adi.get_ee_value(entry.element_entry_id, 'Amount Deposited', ppa.effective_date) amount_deposited, pay_in_form_24q_web_adi.get_ee_value(entry.element_entry_id, 'Challan or Voucher Number', ppa.effective_date) voucher_number, hr_general.decode_lookup('YES_NO', pay_in_form_24q_web_adi.get_ee_value(entry.element_entry_id, 'Correction Flag', ppa.effective_date)) flag, fnd_date.canonical_to_date(pay_in_form_24q_web_adi.get_ee_value(entry.element_entry_id, 'Last Update Date', ppa.effective_date)) last_updated_date, entry.element_entry_id element_entry_id, pay_in_form_24q_web_adi.get_tan_number_ee(entry.element_entry_id) TAN, NULL purge_flag, asg.assignment_id assignment_id FROM pay_payroll_actions ppa, pay_assignment_actions paa, per_assignments_f asg, pay_element_types_f types, pay_element_links_f links, pay_payrolls_f payroll, per_people_f pep, pay_element_entries_f entry WHERE ppa.payroll_action_id = paa.payroll_action_id AND ppa.action_type IN('P', 'U', 'I') AND ppa.action_status = 'C' AND ppa.business_group_id = asg.business_group_id AND asg.business_group_id = ppa.business_group_id AND asg.business_group_id = pep.business_group_id AND asg.business_group_id = links.business_group_id AND asg.assignment_id = paa.assignment_id AND pep.person_id = asg.person_id AND asg.payroll_id = payroll.payroll_id AND asg.payroll_id = ppa.payroll_id AND asg.business_group_id = payroll.business_group_id AND types.element_name = 'Income Tax Challan Information' AND types.legislation_code = 'IN' AND links.element_type_id = types.element_type_id AND entry.assignment_id = asg.assignment_id AND entry.element_type_id = types.element_type_id AND entry.element_link_id = links.element_link_id AND ppa.effective_date BETWEEN types.effective_start_date AND types.effective_end_date AND ppa.effective_date BETWEEN links.effective_start_date AND links.effective_end_date AND ppa.effective_date BETWEEN entry.effective_start_date AND entry.effective_end_date AND ppa.effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date AND ppa.effective_date BETWEEN pep.effective_start_date AND pep.effective_end_date AND ppa.effective_date BETWEEN payroll.effective_start_date AND payroll.effective_end_date

View Text - HTML Formatted

SELECT ASG.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PAI.ACTION_INFORMATION1 ASSESSMENT_YEAR
, PAI.ACTION_INFORMATION13 PAYROLL_NAME
, PAI.ACTION_INFORMATION2 PERIOD
, NVL(TRUNC(TO_DATE(PAI.ACTION_INFORMATION10
, 'DD/MM/YYYY'))
, PAI.EFFECTIVE_DATE) DATE_EARNED
, PAI.EFFECTIVE_DATE PRE_PAYMENT_DATE
, PEP.EMPLOYEE_NUMBER EMP_NUMBER
, PEP.FULL_NAME FULL_NAME
, PAI.ACTION_INFORMATION4 TAXABLE_INCOME
, TO_CHAR(TO_NUMBER(PAI.ACTION_INFORMATION5) + TO_NUMBER(PAI.ACTION_INFORMATION6)) INCOME_TAX_DEDUCTED
, PAI.ACTION_INFORMATION7 SURCHARGE_DEDUCTED
, TO_CHAR(TO_NUMBER(PAI.ACTION_INFORMATION8) + TO_NUMBER(PAI.ACTION_INFORMATION9)) EDUCATION_CESS
, TO_CHAR(TO_NUMBER(PAI.ACTION_INFORMATION5) + TO_NUMBER(PAI.ACTION_INFORMATION6) + TO_NUMBER(PAI.ACTION_INFORMATION7) + TO_NUMBER(PAI.ACTION_INFORMATION8) + TO_NUMBER(PAI.ACTION_INFORMATION9)) TOTAL_TAX_DEDUCTED
, NULL AMOUNT_DEPOSITED
, NULL VOUCHER_NUMBER
, NULL FLAG
, NULL LAST_UPDATED_DATE
, NULL ELEMENT_ENTRY_ID
, PAI.ACTION_INFORMATION3 TAN
, NULL PURGE_FLAG
, ASG.ASSIGNMENT_ID ASSIGNMENT_ID
FROM PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAA
, PER_ASSIGNMENTS_F ASG
, PAY_ELEMENT_TYPES_F TYPES
, PAY_ELEMENT_LINKS_F LINKS
, PER_PEOPLE_F PEP
, PAY_ACTION_INFORMATION PAI
WHERE PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PPA.ACTION_TYPE = 'X'
AND PPA.REPORT_TYPE = 'IN_PAYSLIP_ARCHIVE'
AND PPA.ACTION_STATUS = 'C'
AND PPA.BUSINESS_GROUP_ID = ASG.BUSINESS_GROUP_ID
AND PPA.BUSINESS_GROUP_ID = PEP.BUSINESS_GROUP_ID
AND PEP.BUSINESS_GROUP_ID = LINKS.BUSINESS_GROUP_ID
AND ASG.ASSIGNMENT_ID = PAI.ASSIGNMENT_ID
AND PAI.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND PAI.ACTION_CONTEXT_ID = PAA.ASSIGNMENT_ACTION_ID
AND PEP.PERSON_ID = ASG.PERSON_ID
AND PAI.ACTION_INFORMATION_CATEGORY = 'IN_TAX_BALANCES'
AND TYPES.ELEMENT_NAME = 'INCOME TAX CHALLAN INFORMATION'
AND TYPES.LEGISLATION_CODE = 'IN'
AND LINKS.ELEMENT_TYPE_ID = TYPES.ELEMENT_TYPE_ID
AND PAI.EFFECTIVE_DATE BETWEEN TYPES.EFFECTIVE_START_DATE
AND TYPES.EFFECTIVE_END_DATE
AND PAI.EFFECTIVE_DATE BETWEEN LINKS.EFFECTIVE_START_DATE
AND LINKS.EFFECTIVE_END_DATE
AND PAI.EFFECTIVE_DATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE
AND PAI.EFFECTIVE_DATE BETWEEN PEP.EFFECTIVE_START_DATE
AND PEP.EFFECTIVE_END_DATE
AND NOT EXISTS (SELECT 1
FROM PAY_ELEMENT_ENTRIES_F ENTRY
WHERE ENTRY.ASSIGNMENT_ID = PAI.ASSIGNMENT_ID
AND ENTRY.ELEMENT_TYPE_ID = TYPES.ELEMENT_TYPE_ID
AND ENTRY.ELEMENT_LINK_ID = LINKS.ELEMENT_LINK_ID
AND PAI.EFFECTIVE_DATE BETWEEN ENTRY.EFFECTIVE_START_DATE
AND ENTRY.EFFECTIVE_END_DATE) UNION ALL SELECT DISTINCT ASG.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, TO_CHAR(PAY_IN_UTILS.NEXT_TAX_YEAR(NVL(PPA.DATE_EARNED
, PPA.EFFECTIVE_DATE))
, 'YYYY') || '-' || TO_CHAR(TO_NUMBER(TO_CHAR(PAY_IN_UTILS.NEXT_TAX_YEAR(NVL(PPA.DATE_EARNED
, PPA.EFFECTIVE_DATE))
, 'YYYY')) + 1) ASSESSMENT_YEAR
, PAYROLL.PAYROLL_NAME PAYROLL_NAME
, TO_CHAR(PAY_IN_TAX_UTILS.GET_PERIOD_NUMBER(PAYROLL.PAYROLL_ID
, DECODE(PPA.ACTION_TYPE
, 'I'
, NVL(PPA.DATE_EARNED
, PPA.EFFECTIVE_DATE)
, PAY_IN_FORM_24Q_WEB_ADI.GET_DATE_EARNED(PAA.ASSIGNMENT_ACTION_ID)))) PERIOD
, DECODE(PPA.ACTION_TYPE
, 'I'
, NVL(PPA.DATE_EARNED
, PPA.EFFECTIVE_DATE)
, PAY_IN_FORM_24Q_WEB_ADI.GET_DATE_EARNED_EE(ENTRY.ELEMENT_ENTRY_ID)) DATE_EARNED
, FND_DATE.CANONICAL_TO_DATE(PAY_IN_FORM_24Q_WEB_ADI.GET_EE_VALUE(ENTRY.ELEMENT_ENTRY_ID
, 'PAYMENT DATE'
, PPA.EFFECTIVE_DATE)) PRE_PAYMENT_DATE
, PEP.EMPLOYEE_NUMBER EMP_NUMBER
, PEP.FULL_NAME FULL_NAME
, PAY_IN_FORM_24Q_WEB_ADI.GET_EE_VALUE(ENTRY.ELEMENT_ENTRY_ID
, 'TAXABLE INCOME'
, PPA.EFFECTIVE_DATE) TAXABLE_INCOME
, PAY_IN_FORM_24Q_WEB_ADI.GET_EE_VALUE(ENTRY.ELEMENT_ENTRY_ID
, 'INCOME TAX DEDUCTED'
, PPA.EFFECTIVE_DATE) INCOME_TAX_DEDUCTED
, PAY_IN_FORM_24Q_WEB_ADI.GET_EE_VALUE(ENTRY.ELEMENT_ENTRY_ID
, 'SURCHARGE DEDUCTED'
, PPA.EFFECTIVE_DATE) SURCHARGE_DEDUCTED
, PAY_IN_FORM_24Q_WEB_ADI.GET_EE_VALUE(ENTRY.ELEMENT_ENTRY_ID
, 'EDUCATION CESS DEDUCTED'
, PPA.EFFECTIVE_DATE) EDUCATION_CESS
, TO_CHAR(NVL(PAY_IN_FORM_24Q_WEB_ADI.GET_EE_VALUE(ENTRY.ELEMENT_ENTRY_ID
, 'INCOME TAX DEDUCTED'
, PPA.EFFECTIVE_DATE)
, 0) + NVL(PAY_IN_FORM_24Q_WEB_ADI.GET_EE_VALUE(ENTRY.ELEMENT_ENTRY_ID
, 'SURCHARGE DEDUCTED'
, PPA.EFFECTIVE_DATE)
, 0) + NVL(PAY_IN_FORM_24Q_WEB_ADI.GET_EE_VALUE(ENTRY.ELEMENT_ENTRY_ID
, 'EDUCATION CESS DEDUCTED'
, PPA.EFFECTIVE_DATE)
, 0)) TOTAL_TAX_DEDUCTED
, PAY_IN_FORM_24Q_WEB_ADI.GET_EE_VALUE(ENTRY.ELEMENT_ENTRY_ID
, 'AMOUNT DEPOSITED'
, PPA.EFFECTIVE_DATE) AMOUNT_DEPOSITED
, PAY_IN_FORM_24Q_WEB_ADI.GET_EE_VALUE(ENTRY.ELEMENT_ENTRY_ID
, 'CHALLAN OR VOUCHER NUMBER'
, PPA.EFFECTIVE_DATE) VOUCHER_NUMBER
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, PAY_IN_FORM_24Q_WEB_ADI.GET_EE_VALUE(ENTRY.ELEMENT_ENTRY_ID
, 'CORRECTION FLAG'
, PPA.EFFECTIVE_DATE)) FLAG
, FND_DATE.CANONICAL_TO_DATE(PAY_IN_FORM_24Q_WEB_ADI.GET_EE_VALUE(ENTRY.ELEMENT_ENTRY_ID
, 'LAST UPDATE DATE'
, PPA.EFFECTIVE_DATE)) LAST_UPDATED_DATE
, ENTRY.ELEMENT_ENTRY_ID ELEMENT_ENTRY_ID
, PAY_IN_FORM_24Q_WEB_ADI.GET_TAN_NUMBER_EE(ENTRY.ELEMENT_ENTRY_ID) TAN
, NULL PURGE_FLAG
, ASG.ASSIGNMENT_ID ASSIGNMENT_ID
FROM PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAA
, PER_ASSIGNMENTS_F ASG
, PAY_ELEMENT_TYPES_F TYPES
, PAY_ELEMENT_LINKS_F LINKS
, PAY_PAYROLLS_F PAYROLL
, PER_PEOPLE_F PEP
, PAY_ELEMENT_ENTRIES_F ENTRY
WHERE PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PPA.ACTION_TYPE IN('P'
, 'U'
, 'I')
AND PPA.ACTION_STATUS = 'C'
AND PPA.BUSINESS_GROUP_ID = ASG.BUSINESS_GROUP_ID
AND ASG.BUSINESS_GROUP_ID = PPA.BUSINESS_GROUP_ID
AND ASG.BUSINESS_GROUP_ID = PEP.BUSINESS_GROUP_ID
AND ASG.BUSINESS_GROUP_ID = LINKS.BUSINESS_GROUP_ID
AND ASG.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PEP.PERSON_ID = ASG.PERSON_ID
AND ASG.PAYROLL_ID = PAYROLL.PAYROLL_ID
AND ASG.PAYROLL_ID = PPA.PAYROLL_ID
AND ASG.BUSINESS_GROUP_ID = PAYROLL.BUSINESS_GROUP_ID
AND TYPES.ELEMENT_NAME = 'INCOME TAX CHALLAN INFORMATION'
AND TYPES.LEGISLATION_CODE = 'IN'
AND LINKS.ELEMENT_TYPE_ID = TYPES.ELEMENT_TYPE_ID
AND ENTRY.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND ENTRY.ELEMENT_TYPE_ID = TYPES.ELEMENT_TYPE_ID
AND ENTRY.ELEMENT_LINK_ID = LINKS.ELEMENT_LINK_ID
AND PPA.EFFECTIVE_DATE BETWEEN TYPES.EFFECTIVE_START_DATE
AND TYPES.EFFECTIVE_END_DATE
AND PPA.EFFECTIVE_DATE BETWEEN LINKS.EFFECTIVE_START_DATE
AND LINKS.EFFECTIVE_END_DATE
AND PPA.EFFECTIVE_DATE BETWEEN ENTRY.EFFECTIVE_START_DATE
AND ENTRY.EFFECTIVE_END_DATE
AND PPA.EFFECTIVE_DATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE
AND PPA.EFFECTIVE_DATE BETWEEN PEP.EFFECTIVE_START_DATE
AND PEP.EFFECTIVE_END_DATE
AND PPA.EFFECTIVE_DATE BETWEEN PAYROLL.EFFECTIVE_START_DATE
AND PAYROLL.EFFECTIVE_END_DATE