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_ITWB_ASSACT_LATEST_V

Source

View Text - Preformatted


SELECT /*+ ORDERED */ pai_a.rowid, pai_a.action_information_id, pai_a.object_version_number, pai_a.action_context_id, pai_a.action_context_type, pai_a.assignment_id, pai_a.effective_date, pai_a.action_information_category, pai_a.action_information1, fnd_number.canonical_to_number(decode(pai_a.action_information_category,'JP_ITWB_ASSACT',pai_a.action_information2,null)), fnd_number.canonical_to_number(decode(pai_a.action_information_category,'JP_ITWB_ASSACT',pai_a.action_information3,null)), fnd_number.canonical_to_number(decode(pai_a.action_information_category,'JP_ITWB_ASSACT',pai_a.action_information4,null)), fnd_number.canonical_to_number(decode(pai_a.action_information_category,'JP_ITWB_ASSACT',pai_a.action_information5,null)), fnd_number.canonical_to_number(decode(pai_a.action_information_category,'JP_ITWB_ASSACT',pai_a.action_information6,null)), fnd_date.canonical_to_date(decode(pai_a.action_information_category,'JP_ITWB_ASSACT',pai_a.action_information7,null)), pai_a.action_information8, fnd_number.canonical_to_number(decode(pai_a.action_information_category,'JP_ITWB_ASSACT',pai_a.action_information9,null)), pai_a.last_update_login, pai_a.last_updated_by, pai_a.last_update_date, pai_a.created_by, pai_a.creation_date from pay_action_information pai_a, pay_assignment_actions paa, pay_payroll_actions ppa, pay_action_information pai_es where pai_a.action_context_type = 'AAP' and pai_a.action_information_category = 'JP_ITWB_ASSACT' and paa.assignment_action_id = pai_a.action_context_id and ppa.payroll_action_id = paa.payroll_action_id and pai_es.action_context_id = pai_a.action_context_id and pai_es.action_context_type = 'AAP' and pai_es.action_information_category = 'JP_ITWB_EMP_SI' and pai_es.effective_date = pai_a.effective_date and not exists( select /*+ ORDERED */ null from pay_action_information pai_a2, pay_assignment_actions paa2, pay_payroll_actions ppa2, pay_action_information pai_es2 where pai_a2.assignment_id = paa.assignment_id and pai_a2.action_context_type = 'AAP' and pai_a2.action_information_category = 'JP_ITWB_ASSACT' and paa2.assignment_action_id = pai_a2.action_context_id and ppa2.payroll_action_id = paa2.payroll_action_id and pai_es2.action_context_id = pai_a2.action_context_id and pai_es2.action_context_type = 'AAP' and pai_es2.action_information_category = 'JP_ITWB_EMP_SI' and pai_es2.effective_date = pai_a2.effective_date and pai_es2.action_information1 = pai_es.action_information1 /* itax_org_id */ and ppa2.effective_date between trunc(ppa.effective_date,'YYYY') and add_months(trunc(ppa.effective_date,'YYYY'),12) - 1 and paa2.action_sequence > paa.action_sequence)

View Text - HTML Formatted

SELECT /*+ ORDERED */ PAI_A.ROWID
, PAI_A.ACTION_INFORMATION_ID
, PAI_A.OBJECT_VERSION_NUMBER
, PAI_A.ACTION_CONTEXT_ID
, PAI_A.ACTION_CONTEXT_TYPE
, PAI_A.ASSIGNMENT_ID
, PAI_A.EFFECTIVE_DATE
, PAI_A.ACTION_INFORMATION_CATEGORY
, PAI_A.ACTION_INFORMATION1
, FND_NUMBER.CANONICAL_TO_NUMBER(DECODE(PAI_A.ACTION_INFORMATION_CATEGORY
, 'JP_ITWB_ASSACT'
, PAI_A.ACTION_INFORMATION2
, NULL))
, FND_NUMBER.CANONICAL_TO_NUMBER(DECODE(PAI_A.ACTION_INFORMATION_CATEGORY
, 'JP_ITWB_ASSACT'
, PAI_A.ACTION_INFORMATION3
, NULL))
, FND_NUMBER.CANONICAL_TO_NUMBER(DECODE(PAI_A.ACTION_INFORMATION_CATEGORY
, 'JP_ITWB_ASSACT'
, PAI_A.ACTION_INFORMATION4
, NULL))
, FND_NUMBER.CANONICAL_TO_NUMBER(DECODE(PAI_A.ACTION_INFORMATION_CATEGORY
, 'JP_ITWB_ASSACT'
, PAI_A.ACTION_INFORMATION5
, NULL))
, FND_NUMBER.CANONICAL_TO_NUMBER(DECODE(PAI_A.ACTION_INFORMATION_CATEGORY
, 'JP_ITWB_ASSACT'
, PAI_A.ACTION_INFORMATION6
, NULL))
, FND_DATE.CANONICAL_TO_DATE(DECODE(PAI_A.ACTION_INFORMATION_CATEGORY
, 'JP_ITWB_ASSACT'
, PAI_A.ACTION_INFORMATION7
, NULL))
, PAI_A.ACTION_INFORMATION8
, FND_NUMBER.CANONICAL_TO_NUMBER(DECODE(PAI_A.ACTION_INFORMATION_CATEGORY
, 'JP_ITWB_ASSACT'
, PAI_A.ACTION_INFORMATION9
, NULL))
, PAI_A.LAST_UPDATE_LOGIN
, PAI_A.LAST_UPDATED_BY
, PAI_A.LAST_UPDATE_DATE
, PAI_A.CREATED_BY
, PAI_A.CREATION_DATE
FROM PAY_ACTION_INFORMATION PAI_A
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PAY_ACTION_INFORMATION PAI_ES
WHERE PAI_A.ACTION_CONTEXT_TYPE = 'AAP'
AND PAI_A.ACTION_INFORMATION_CATEGORY = 'JP_ITWB_ASSACT'
AND PAA.ASSIGNMENT_ACTION_ID = PAI_A.ACTION_CONTEXT_ID
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PAI_ES.ACTION_CONTEXT_ID = PAI_A.ACTION_CONTEXT_ID
AND PAI_ES.ACTION_CONTEXT_TYPE = 'AAP'
AND PAI_ES.ACTION_INFORMATION_CATEGORY = 'JP_ITWB_EMP_SI'
AND PAI_ES.EFFECTIVE_DATE = PAI_A.EFFECTIVE_DATE
AND NOT EXISTS( SELECT /*+ ORDERED */ NULL
FROM PAY_ACTION_INFORMATION PAI_A2
, PAY_ASSIGNMENT_ACTIONS PAA2
, PAY_PAYROLL_ACTIONS PPA2
, PAY_ACTION_INFORMATION PAI_ES2
WHERE PAI_A2.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PAI_A2.ACTION_CONTEXT_TYPE = 'AAP'
AND PAI_A2.ACTION_INFORMATION_CATEGORY = 'JP_ITWB_ASSACT'
AND PAA2.ASSIGNMENT_ACTION_ID = PAI_A2.ACTION_CONTEXT_ID
AND PPA2.PAYROLL_ACTION_ID = PAA2.PAYROLL_ACTION_ID
AND PAI_ES2.ACTION_CONTEXT_ID = PAI_A2.ACTION_CONTEXT_ID
AND PAI_ES2.ACTION_CONTEXT_TYPE = 'AAP'
AND PAI_ES2.ACTION_INFORMATION_CATEGORY = 'JP_ITWB_EMP_SI'
AND PAI_ES2.EFFECTIVE_DATE = PAI_A2.EFFECTIVE_DATE
AND PAI_ES2.ACTION_INFORMATION1 = PAI_ES.ACTION_INFORMATION1 /* ITAX_ORG_ID */
AND PPA2.EFFECTIVE_DATE BETWEEN TRUNC(PPA.EFFECTIVE_DATE
, 'YYYY')
AND ADD_MONTHS(TRUNC(PPA.EFFECTIVE_DATE
, 'YYYY')
, 12) - 1
AND PAA2.ACTION_SEQUENCE > PAA.ACTION_SEQUENCE)