VIEW: APPS.PER_ES_TAX_DETAILS_V

Source

View Text - Preformatted

SELECT e.rowid as Row_Id ,e.assignment_id as Assignment_Id ,e.element_entry_id as Element_Entry_Id ,l.element_link_id as Element_Link_Id ,t.element_type_id ,max(e.OBJECT_VERSION_NUMBER) as Object_Version_Number ,min(decode(i.name,'Worker Status',v.screen_entry_value,null)) as screen_entry_value0 ,hr_general.decode_fnd_comm_lookup('ES_WORKER_STATUS',min(decode(i.name,'Worker Status',v.screen_entry_value,null))) as WORKER_STATUS ,min(decode(i.name,'Payment Key',v.screen_entry_value,null)) as screen_entry_value1 ,hr_general.decode_fnd_comm_lookup('ES_PAYMENT_KEY',min(decode(i.name,'Payment Key',v.screen_entry_value,null))) as PAYMENT_KEY ,min(decode(i.name,'Sub Payment Key',v.screen_entry_value,null)) as screen_entry_value2 ,decode(min(decode(i.name,'Payment Key',v.screen_entry_value,null)),'B',hr_general.decode_lookup('ES_SUB_PAYMENT_KEY_B',min(decode(i.name,'Sub Payment Key',v.screen_entry_value,null))) ,'F',hr_general.decode_lookup('ES_SUB_PAYMENT_KEY_F',min(decode(i.name,'Sub Payment Key',v.screen_entry_value,null))) ,'G',hr_general.decode_lookup('ES_SUB_PAYMENT_KEY_G',min(decode(i.name,'Sub Payment Key',v.screen_entry_value,null))) ,'H',hr_general.decode_lookup('ES_SUB_PAYMENT_KEY_H',min(decode(i.name,'Sub Payment Key',v.screen_entry_value,null))) ,'L',hr_general.decode_lookup('ES_SUB_PAYMENT_KEY_L',min(decode(i.name,'Sub Payment Key',v.screen_entry_value,null))),null) SUB_PAYMENT_KEY ,min(decode(i.name,'Change of Residency Date',v.screen_entry_value,null)) as Change_of_residency_Date ,min(decode(i.name,'Any Other Situations',v.screen_entry_value,null)) as Other_situation ,min(decode(i.name,'Tax Withholding Rate',v.screen_entry_value,null)) as tax_withholding_rate ,e.effective_start_date ,e.effective_end_date FROM pay_element_entries_f e ,pay_input_values_f i ,pay_element_entry_values_f v ,pay_element_types_f t ,pay_element_links_f l ,fnd_sessions f WHERE e.element_entry_id = v.element_entry_id AND v.input_value_id = i.input_value_id AND i.element_type_id = t.element_type_id AND t.element_type_id = l.element_type_id AND l.element_link_id = e.element_link_id AND t.element_name = 'Tax Details' AND t.legislation_code = 'ES' AND f.session_id = userenv('sessionid') AND f.effective_date BETWEEN e.effective_start_date AND e.effective_end_date AND f.effective_date BETWEEN v.effective_start_date AND v.effective_end_date AND f.effective_date BETWEEN i.effective_start_date AND i.effective_end_date AND f.effective_date BETWEEN t.effective_start_date AND t.effective_end_date AND f.effective_date BETWEEN l.effective_start_date AND l.effective_end_date GROUP BY e.rowid ,e.element_entry_id ,l.element_link_id ,t.element_type_id ,e.assignment_id ,e.effective_start_date ,e.effective_end_date

View Text - HTML Formatted

SELECT E.ROWID AS ROW_ID
, E.ASSIGNMENT_ID AS ASSIGNMENT_ID
, E.ELEMENT_ENTRY_ID AS ELEMENT_ENTRY_ID
, L.ELEMENT_LINK_ID AS ELEMENT_LINK_ID
, T.ELEMENT_TYPE_ID
, MAX(E.OBJECT_VERSION_NUMBER) AS OBJECT_VERSION_NUMBER
, MIN(DECODE(I.NAME
, 'WORKER STATUS'
, V.SCREEN_ENTRY_VALUE
, NULL)) AS SCREEN_ENTRY_VALUE0
, HR_GENERAL.DECODE_FND_COMM_LOOKUP('ES_WORKER_STATUS'
, MIN(DECODE(I.NAME
, 'WORKER STATUS'
, V.SCREEN_ENTRY_VALUE
, NULL))) AS WORKER_STATUS
, MIN(DECODE(I.NAME
, 'PAYMENT KEY'
, V.SCREEN_ENTRY_VALUE
, NULL)) AS SCREEN_ENTRY_VALUE1
, HR_GENERAL.DECODE_FND_COMM_LOOKUP('ES_PAYMENT_KEY'
, MIN(DECODE(I.NAME
, 'PAYMENT KEY'
, V.SCREEN_ENTRY_VALUE
, NULL))) AS PAYMENT_KEY
, MIN(DECODE(I.NAME
, 'SUB PAYMENT KEY'
, V.SCREEN_ENTRY_VALUE
, NULL)) AS SCREEN_ENTRY_VALUE2
, DECODE(MIN(DECODE(I.NAME
, 'PAYMENT KEY'
, V.SCREEN_ENTRY_VALUE
, NULL))
, 'B'
, HR_GENERAL.DECODE_LOOKUP('ES_SUB_PAYMENT_KEY_B'
, MIN(DECODE(I.NAME
, 'SUB PAYMENT KEY'
, V.SCREEN_ENTRY_VALUE
, NULL)))
, 'F'
, HR_GENERAL.DECODE_LOOKUP('ES_SUB_PAYMENT_KEY_F'
, MIN(DECODE(I.NAME
, 'SUB PAYMENT KEY'
, V.SCREEN_ENTRY_VALUE
, NULL)))
, 'G'
, HR_GENERAL.DECODE_LOOKUP('ES_SUB_PAYMENT_KEY_G'
, MIN(DECODE(I.NAME
, 'SUB PAYMENT KEY'
, V.SCREEN_ENTRY_VALUE
, NULL)))
, 'H'
, HR_GENERAL.DECODE_LOOKUP('ES_SUB_PAYMENT_KEY_H'
, MIN(DECODE(I.NAME
, 'SUB PAYMENT KEY'
, V.SCREEN_ENTRY_VALUE
, NULL)))
, 'L'
, HR_GENERAL.DECODE_LOOKUP('ES_SUB_PAYMENT_KEY_L'
, MIN(DECODE(I.NAME
, 'SUB PAYMENT KEY'
, V.SCREEN_ENTRY_VALUE
, NULL)))
, NULL) SUB_PAYMENT_KEY
, MIN(DECODE(I.NAME
, 'CHANGE OF RESIDENCY DATE'
, V.SCREEN_ENTRY_VALUE
, NULL)) AS CHANGE_OF_RESIDENCY_DATE
, MIN(DECODE(I.NAME
, 'ANY OTHER SITUATIONS'
, V.SCREEN_ENTRY_VALUE
, NULL)) AS OTHER_SITUATION
, MIN(DECODE(I.NAME
, 'TAX WITHHOLDING RATE'
, V.SCREEN_ENTRY_VALUE
, NULL)) AS TAX_WITHHOLDING_RATE
, E.EFFECTIVE_START_DATE
, E.EFFECTIVE_END_DATE
FROM PAY_ELEMENT_ENTRIES_F E
, PAY_INPUT_VALUES_F I
, PAY_ELEMENT_ENTRY_VALUES_F V
, PAY_ELEMENT_TYPES_F T
, PAY_ELEMENT_LINKS_F L
, FND_SESSIONS F
WHERE E.ELEMENT_ENTRY_ID = V.ELEMENT_ENTRY_ID
AND V.INPUT_VALUE_ID = I.INPUT_VALUE_ID
AND I.ELEMENT_TYPE_ID = T.ELEMENT_TYPE_ID
AND T.ELEMENT_TYPE_ID = L.ELEMENT_TYPE_ID
AND L.ELEMENT_LINK_ID = E.ELEMENT_LINK_ID
AND T.ELEMENT_NAME = 'TAX DETAILS'
AND T.LEGISLATION_CODE = 'ES'
AND F.SESSION_ID = USERENV('SESSIONID')
AND F.EFFECTIVE_DATE BETWEEN E.EFFECTIVE_START_DATE
AND E.EFFECTIVE_END_DATE
AND F.EFFECTIVE_DATE BETWEEN V.EFFECTIVE_START_DATE
AND V.EFFECTIVE_END_DATE
AND F.EFFECTIVE_DATE BETWEEN I.EFFECTIVE_START_DATE
AND I.EFFECTIVE_END_DATE
AND F.EFFECTIVE_DATE BETWEEN T.EFFECTIVE_START_DATE
AND T.EFFECTIVE_END_DATE
AND F.EFFECTIVE_DATE BETWEEN L.EFFECTIVE_START_DATE
AND L.EFFECTIVE_END_DATE GROUP BY E.ROWID
, E.ELEMENT_ENTRY_ID
, L.ELEMENT_LINK_ID
, T.ELEMENT_TYPE_ID
, E.ASSIGNMENT_ID
, E.EFFECTIVE_START_DATE
, E.EFFECTIVE_END_DATE