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

Source

View Text - Preformatted


SELECT PLRL.ROWID , PLRL.LETTER_REQUEST_LINE_ID , PLRL.BUSINESS_GROUP_ID , PLRL.LETTER_REQUEST_ID , PLRL.PERSON_ID , PLRL.ASSIGNMENT_ID , PLRL.ASSIGNMENT_STATUS_TYPE_ID , PLRL.DATE_FROM , PLRL.REQUEST_ID , PLRL.PROGRAM_APPLICATION_ID , PLRL.PROGRAM_ID , PLRL.PROGRAM_UPDATE_DATE , PLRL.LAST_UPDATE_DATE , PLRL.LAST_UPDATED_BY , PLRL.LAST_UPDATE_LOGIN , PLRL.CREATED_BY , PLRL.CREATION_DATE , PP.FULL_NAME , PP.EMPLOYEE_NUMBER , PP.APPLICANT_NUMBER , PP.NPW_NUMBER , HR_PERSON_TYPE_USAGE_INFO.GET_USER_PERSON_TYPE(PLRL.DATE_FROM, PLRL.PERSON_ID) , ORGTL.NAME , PLRL.OTA_BOOKING_STATUS_TYPE_ID , PLRL.OTA_BOOKING_ID , PLRL.OTA_EVENT_ID , PLRL.CONTRACT_ID FROM PER_LETTER_REQUEST_LINES PLRL, PER_ASSIGNMENTS ASS, HR_ALL_ORGANIZATION_UNITS ORG, HR_ALL_ORGANIZATION_UNITS_TL ORGTL, PER_ALL_PEOPLE PP WHERE PLRL.PERSON_ID = PP.PERSON_ID AND ASS.ORGANIZATION_ID = ORG.ORGANIZATION_ID AND ASS.ASSIGNMENT_ID = PLRL.ASSIGNMENT_ID AND ORG.ORGANIZATION_ID = ORGTL.ORGANIZATION_ID AND ORGTL.LANGUAGE = userenv('LANG') UNION /* outer case */ SELECT PLRL.ROWID , PLRL.LETTER_REQUEST_LINE_ID , PLRL.BUSINESS_GROUP_ID , PLRL.LETTER_REQUEST_ID , PLRL.PERSON_ID , PLRL.ASSIGNMENT_ID , PLRL.ASSIGNMENT_STATUS_TYPE_ID , PLRL.DATE_FROM , PLRL.REQUEST_ID , PLRL.PROGRAM_APPLICATION_ID , PLRL.PROGRAM_ID , PLRL.PROGRAM_UPDATE_DATE , PLRL.LAST_UPDATE_DATE , PLRL.LAST_UPDATED_BY , PLRL.LAST_UPDATE_LOGIN , PLRL.CREATED_BY , PLRL.CREATION_DATE , PP.FULL_NAME , PP.EMPLOYEE_NUMBER , PP.APPLICANT_NUMBER , PP.NPW_NUMBER , HR_PERSON_TYPE_USAGE_INFO.GET_WORKER_USER_PERSON_TYPE(PLRL.DATE_FROM, PLRL.PERSON_ID) , null , PLRL.OTA_BOOKING_STATUS_TYPE_ID , PLRL.OTA_BOOKING_ID , PLRL.OTA_EVENT_ID , PLRL.CONTRACT_ID FROM PER_LETTER_REQUEST_LINES PLRL , PER_PEOPLE PP WHERE PLRL.PERSON_ID = PP.PERSON_ID and not exists (select 1 from PER_ASSIGNMENTS ASS, HR_ALL_ORGANIZATION_UNITS ORG where ASS.ORGANIZATION_ID = ORG.ORGANIZATION_ID AND ASS.ASSIGNMENT_ID = PLRL.ASSIGNMENT_ID ) UNION SELECT /* --------------------------------------------------------- THIS HALF OF THE VIEW RETRIEVES LETTER REQUEST INFO FOR STUDENT ENROLLMENTS IN OTA --------------------------------------------------------- */ PLRL.ROWID, PLRL.LETTER_REQUEST_LINE_ID, PLRL.BUSINESS_GROUP_ID, PLRL.LETTER_REQUEST_ID, PLRL.PERSON_ID, PLRL.ASSIGNMENT_ID, PLRL.ASSIGNMENT_STATUS_TYPE_ID, PLRL.DATE_FROM, PLRL.REQUEST_ID, PLRL.PROGRAM_APPLICATION_ID, PLRL.PROGRAM_ID, PLRL.PROGRAM_UPDATE_DATE, PLRL.LAST_UPDATE_DATE, PLRL.LAST_UPDATED_BY, PLRL.LAST_UPDATE_LOGIN, PLRL.CREATED_BY, PLRL.CREATION_DATE, NULL, NULL, NULL, NULL, NULL, NULL, PLRL.OTA_BOOKING_STATUS_TYPE_ID, PLRL.OTA_BOOKING_ID, PLRL.OTA_EVENT_ID, PLRL.CONTRACT_ID FROM PER_LETTER_REQUEST_LINES PLRL WHERE (PLRL.OTA_EVENT_ID IS NOT NULL OR PLRL.OTA_BOOKING_ID IS NOT NULL) AND PLRL.PERSON_ID IS NULL

View Text - HTML Formatted

SELECT PLRL.ROWID
, PLRL.LETTER_REQUEST_LINE_ID
, PLRL.BUSINESS_GROUP_ID
, PLRL.LETTER_REQUEST_ID
, PLRL.PERSON_ID
, PLRL.ASSIGNMENT_ID
, PLRL.ASSIGNMENT_STATUS_TYPE_ID
, PLRL.DATE_FROM
, PLRL.REQUEST_ID
, PLRL.PROGRAM_APPLICATION_ID
, PLRL.PROGRAM_ID
, PLRL.PROGRAM_UPDATE_DATE
, PLRL.LAST_UPDATE_DATE
, PLRL.LAST_UPDATED_BY
, PLRL.LAST_UPDATE_LOGIN
, PLRL.CREATED_BY
, PLRL.CREATION_DATE
, PP.FULL_NAME
, PP.EMPLOYEE_NUMBER
, PP.APPLICANT_NUMBER
, PP.NPW_NUMBER
, HR_PERSON_TYPE_USAGE_INFO.GET_USER_PERSON_TYPE(PLRL.DATE_FROM
, PLRL.PERSON_ID)
, ORGTL.NAME
, PLRL.OTA_BOOKING_STATUS_TYPE_ID
, PLRL.OTA_BOOKING_ID
, PLRL.OTA_EVENT_ID
, PLRL.CONTRACT_ID
FROM PER_LETTER_REQUEST_LINES PLRL
, PER_ASSIGNMENTS ASS
, HR_ALL_ORGANIZATION_UNITS ORG
, HR_ALL_ORGANIZATION_UNITS_TL ORGTL
, PER_ALL_PEOPLE PP
WHERE PLRL.PERSON_ID = PP.PERSON_ID
AND ASS.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND ASS.ASSIGNMENT_ID = PLRL.ASSIGNMENT_ID
AND ORG.ORGANIZATION_ID = ORGTL.ORGANIZATION_ID
AND ORGTL.LANGUAGE = USERENV('LANG') UNION /* OUTER CASE */ SELECT PLRL.ROWID
, PLRL.LETTER_REQUEST_LINE_ID
, PLRL.BUSINESS_GROUP_ID
, PLRL.LETTER_REQUEST_ID
, PLRL.PERSON_ID
, PLRL.ASSIGNMENT_ID
, PLRL.ASSIGNMENT_STATUS_TYPE_ID
, PLRL.DATE_FROM
, PLRL.REQUEST_ID
, PLRL.PROGRAM_APPLICATION_ID
, PLRL.PROGRAM_ID
, PLRL.PROGRAM_UPDATE_DATE
, PLRL.LAST_UPDATE_DATE
, PLRL.LAST_UPDATED_BY
, PLRL.LAST_UPDATE_LOGIN
, PLRL.CREATED_BY
, PLRL.CREATION_DATE
, PP.FULL_NAME
, PP.EMPLOYEE_NUMBER
, PP.APPLICANT_NUMBER
, PP.NPW_NUMBER
, HR_PERSON_TYPE_USAGE_INFO.GET_WORKER_USER_PERSON_TYPE(PLRL.DATE_FROM
, PLRL.PERSON_ID)
, NULL
, PLRL.OTA_BOOKING_STATUS_TYPE_ID
, PLRL.OTA_BOOKING_ID
, PLRL.OTA_EVENT_ID
, PLRL.CONTRACT_ID
FROM PER_LETTER_REQUEST_LINES PLRL
, PER_PEOPLE PP
WHERE PLRL.PERSON_ID = PP.PERSON_ID
AND NOT EXISTS (SELECT 1
FROM PER_ASSIGNMENTS ASS
, HR_ALL_ORGANIZATION_UNITS ORG
WHERE ASS.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND ASS.ASSIGNMENT_ID = PLRL.ASSIGNMENT_ID ) UNION SELECT /* --------------------------------------------------------- THIS HALF OF THE VIEW RETRIEVES LETTER REQUEST INFO FOR STUDENT ENROLLMENTS IN OTA --------------------------------------------------------- */ PLRL.ROWID
, PLRL.LETTER_REQUEST_LINE_ID
, PLRL.BUSINESS_GROUP_ID
, PLRL.LETTER_REQUEST_ID
, PLRL.PERSON_ID
, PLRL.ASSIGNMENT_ID
, PLRL.ASSIGNMENT_STATUS_TYPE_ID
, PLRL.DATE_FROM
, PLRL.REQUEST_ID
, PLRL.PROGRAM_APPLICATION_ID
, PLRL.PROGRAM_ID
, PLRL.PROGRAM_UPDATE_DATE
, PLRL.LAST_UPDATE_DATE
, PLRL.LAST_UPDATED_BY
, PLRL.LAST_UPDATE_LOGIN
, PLRL.CREATED_BY
, PLRL.CREATION_DATE
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, PLRL.OTA_BOOKING_STATUS_TYPE_ID
, PLRL.OTA_BOOKING_ID
, PLRL.OTA_EVENT_ID
, PLRL.CONTRACT_ID
FROM PER_LETTER_REQUEST_LINES PLRL
WHERE (PLRL.OTA_EVENT_ID IS NOT NULL OR PLRL.OTA_BOOKING_ID IS NOT NULL)
AND PLRL.PERSON_ID IS NULL