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.

VIEW: APPS.PAY_JP_WIC_ASSACTS_V1

Source

View Text - Preformatted


SELECT /*+ USE_NL(PPA, PAA, PAI) INDEX(PPA) INDEX(PAI PAY_ACTION_INFORMATION_N3) */ paa.assignment_action_id, paa.assignment_id, paa.action_sequence, ppa.payroll_action_id, ppa.business_group_id, ppa.payroll_id, ppa.effective_date, ppa.date_earned, fnd_number.canonical_to_number(pai.action_information21), pai.action_information13, pai.action_information22, pai.action_information23, fnd_number.canonical_to_number(pai.action_information4), fnd_number.canonical_to_number(pai.action_information15), fnd_date.canonical_to_date(pai.action_information16), pai.action_information17, fnd_date.canonical_to_date(pai.action_information18), fnd_number.canonical_to_number(pai.action_information2) + fnd_number.canonical_to_number(pai.action_information3), fnd_number.canonical_to_number(pai.action_information6) + fnd_number.canonical_to_number(pai.action_information9) + fnd_number.canonical_to_number(pai.action_information12) + fnd_number.canonical_to_number(pai.action_information20) + fnd_number.canonical_to_number(pai.action_information14), fnd_number.canonical_to_number(pai.action_information14), fnd_number.canonical_to_number(pai.action_information24), fnd_number.canonical_to_number(pai.action_information25), fnd_number.canonical_to_number(pai.action_information29), pai.action_information30, ppa.legislative_parameters from pay_payroll_actions ppa, pay_assignment_actions paa, pay_action_information pai where ppa.action_type in ('R', 'Q', 'B', 'I') and paa.payroll_action_id = ppa.payroll_action_id and paa.action_status = 'C' and pai.action_information_category = 'JP_PRE_TAX_1' and pai.action_context_type = 'AAP' and pai.action_information1 = to_char(paa.assignment_action_id) and pai.assignment_id + 0 = paa.assignment_id and pai.action_information13 in ('SALARY', 'BONUS', 'SP_BONUS', 'YEA', 'RE_YEA') and pai.action_information22 in ('M_KOU', 'D_KOU', 'M_OTSU', 'D_OTSU', 'D_HEI') /* Exclude payments after date of death (exluding YEA assacts). */ and (pai.action_information13 in ('YEA', 'RE_YEA') or pai.action_information17 is null /* leaving_reason */ or pai.action_information17 not like 'D%' or pai.action_information18 is null /* actual_termination_date */ or ppa.effective_date <= fnd_date.canonical_to_date(pai.action_information18)) /* Exclude Reversed actions. */ and not exists( select /* ORDERED USE_NL(PAIL, PAAV, PPAV) INDEX(PAIL PAY_ACTION_INTERLOCKS_FK2) INDEX(PAAV PAY_ASSIGNMENT_ACTIONS_PK) INDEX(PPAV PAY_PAYROLL_ACTIONS_PK) */ null from pay_action_interlocks pail, pay_assignment_actions paav, pay_payroll_actions ppav where pail.locked_action_id = decode(pai.action_information_category, 'JP_PRE_TAX_1',fnd_number.canonical_to_number(action_information1), -1) and paav.assignment_action_id = pail.locking_action_id and ppav.payroll_action_id = paav.payroll_action_id and ppav.action_type = 'V')

View Text - HTML Formatted

SELECT /*+ USE_NL(PPA
, PAA
, PAI) INDEX(PPA) INDEX(PAI PAY_ACTION_INFORMATION_N3) */ PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PAA.ACTION_SEQUENCE
, PPA.PAYROLL_ACTION_ID
, PPA.BUSINESS_GROUP_ID
, PPA.PAYROLL_ID
, PPA.EFFECTIVE_DATE
, PPA.DATE_EARNED
, FND_NUMBER.CANONICAL_TO_NUMBER(PAI.ACTION_INFORMATION21)
, PAI.ACTION_INFORMATION13
, PAI.ACTION_INFORMATION22
, PAI.ACTION_INFORMATION23
, FND_NUMBER.CANONICAL_TO_NUMBER(PAI.ACTION_INFORMATION4)
, FND_NUMBER.CANONICAL_TO_NUMBER(PAI.ACTION_INFORMATION15)
, FND_DATE.CANONICAL_TO_DATE(PAI.ACTION_INFORMATION16)
, PAI.ACTION_INFORMATION17
, FND_DATE.CANONICAL_TO_DATE(PAI.ACTION_INFORMATION18)
, FND_NUMBER.CANONICAL_TO_NUMBER(PAI.ACTION_INFORMATION2) + FND_NUMBER.CANONICAL_TO_NUMBER(PAI.ACTION_INFORMATION3)
, FND_NUMBER.CANONICAL_TO_NUMBER(PAI.ACTION_INFORMATION6) + FND_NUMBER.CANONICAL_TO_NUMBER(PAI.ACTION_INFORMATION9) + FND_NUMBER.CANONICAL_TO_NUMBER(PAI.ACTION_INFORMATION12) + FND_NUMBER.CANONICAL_TO_NUMBER(PAI.ACTION_INFORMATION20) + FND_NUMBER.CANONICAL_TO_NUMBER(PAI.ACTION_INFORMATION14)
, FND_NUMBER.CANONICAL_TO_NUMBER(PAI.ACTION_INFORMATION14)
, FND_NUMBER.CANONICAL_TO_NUMBER(PAI.ACTION_INFORMATION24)
, FND_NUMBER.CANONICAL_TO_NUMBER(PAI.ACTION_INFORMATION25)
, FND_NUMBER.CANONICAL_TO_NUMBER(PAI.ACTION_INFORMATION29)
, PAI.ACTION_INFORMATION30
, PPA.LEGISLATIVE_PARAMETERS
FROM PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_ACTION_INFORMATION PAI
WHERE PPA.ACTION_TYPE IN ('R'
, 'Q'
, 'B'
, 'I')
AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PAA.ACTION_STATUS = 'C'
AND PAI.ACTION_INFORMATION_CATEGORY = 'JP_PRE_TAX_1'
AND PAI.ACTION_CONTEXT_TYPE = 'AAP'
AND PAI.ACTION_INFORMATION1 = TO_CHAR(PAA.ASSIGNMENT_ACTION_ID)
AND PAI.ASSIGNMENT_ID + 0 = PAA.ASSIGNMENT_ID
AND PAI.ACTION_INFORMATION13 IN ('SALARY'
, 'BONUS'
, 'SP_BONUS'
, 'YEA'
, 'RE_YEA')
AND PAI.ACTION_INFORMATION22 IN ('M_KOU'
, 'D_KOU'
, 'M_OTSU'
, 'D_OTSU'
, 'D_HEI') /* EXCLUDE PAYMENTS AFTER DATE OF DEATH (EXLUDING YEA ASSACTS). */
AND (PAI.ACTION_INFORMATION13 IN ('YEA'
, 'RE_YEA') OR PAI.ACTION_INFORMATION17 IS NULL /* LEAVING_REASON */ OR PAI.ACTION_INFORMATION17 NOT LIKE 'D%' OR PAI.ACTION_INFORMATION18 IS NULL /* ACTUAL_TERMINATION_DATE */ OR PPA.EFFECTIVE_DATE <= FND_DATE.CANONICAL_TO_DATE(PAI.ACTION_INFORMATION18)) /* EXCLUDE REVERSED ACTIONS. */
AND NOT EXISTS( SELECT /* ORDERED USE_NL(PAIL
, PAAV
, PPAV) INDEX(PAIL PAY_ACTION_INTERLOCKS_FK2) INDEX(PAAV PAY_ASSIGNMENT_ACTIONS_PK) INDEX(PPAV PAY_PAYROLL_ACTIONS_PK) */ NULL
FROM PAY_ACTION_INTERLOCKS PAIL
, PAY_ASSIGNMENT_ACTIONS PAAV
, PAY_PAYROLL_ACTIONS PPAV
WHERE PAIL.LOCKED_ACTION_ID = DECODE(PAI.ACTION_INFORMATION_CATEGORY
, 'JP_PRE_TAX_1'
, FND_NUMBER.CANONICAL_TO_NUMBER(ACTION_INFORMATION1)
, -1)
AND PAAV.ASSIGNMENT_ACTION_ID = PAIL.LOCKING_ACTION_ID
AND PPAV.PAYROLL_ACTION_ID = PAAV.PAYROLL_ACTION_ID
AND PPAV.ACTION_TYPE = 'V')