eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

View: PAY_IN_FORM_24Q_WEB_ADI_V

Product:	PAY - Payroll
Description:
Implementation/DBA Data:	APPS.PAY_IN_FORM_24Q_WEB_ADI_V

View Text

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
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
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

Columns

Name
BUSINESS_GROUP_ID
ASSESSMENT_YEAR
PAYROLL_NAME
PERIOD
DATE_EARNED
PRE_PAYMENT_DATE
EMP_NUMBER
FULL_NAME
TAXABLE_INCOME
INCOME_TAX_DEDUCTED
SURCHARGE_DEDUCTED
EDUCATION_CESS
TOTAL_TAX_DEDUCTED
AMOUNT_DEPOSITED
VOUCHER_NUMBER
FLAG
LAST_UPDATED_DATE
ELEMENT_ENTRY_ID
TAN
PURGE_FLAG