VIEW: APPS.PSP_REP_ENC_SSP_V

Source

View Text - Preformatted

SELECT distinct a.payroll_id, a.time_period_id, a.person_id, a.assignment_id, a.enc_element_type_id, sum(a.encumbrance_amount), a.suspense_org_account_id, a.project_id, a.expenditure_organization_id, a.expenditure_type, a.task_id, a.award_id, a.gl_code_combination_id, a.business_group_id, a.set_of_books_id from PSP_ENC_LINES A where A.suspense_org_account_id is not null AND a.enc_control_id in (select enc_control_id from psp_enc_controls where action_code = 'N') GROUP BY a.business_group_id, a.set_of_books_id,a.payroll_id,a.time_period_id,a.person_id,a.assignment_id,a.enc_element_type_id, a.suspense_org_account_id,a.project_id,a.expenditure_organization_id,a.expenditure_type, a.task_id,a.award_id,a.gl_code_combination_id UNION ALL select distinct b.payroll_id,b.time_period_id,b.person_id,b.assignment_id, b.enc_element_type_id,sum(b.encumbrance_amount),b.suspense_org_account_id, b.project_id,b.expenditure_organization_id,b.expenditure_type,b.task_id,b.award_id, b.gl_code_combination_id,b.business_group_id,b.set_of_books_id from psp_enc_lines_HISTORY B where B.suspense_org_account_id is not null AND b.change_flag in ('U' , 'N') AND B.enc_control_id in (select enc_control_id from psp_enc_controls where action_code = 'P') GROUP BY b.business_group_id,b.set_of_books_id ,b.payroll_id,b.time_period_id,b.person_id,b.assignment_id, b.enc_element_type_id,b.suspense_org_account_id,b.project_id,b.expenditure_organization_id, b.expenditure_type,b.task_id,b.award_id,b.gl_code_combination_id

View Text - HTML Formatted

SELECT DISTINCT A.PAYROLL_ID
, A.TIME_PERIOD_ID
, A.PERSON_ID
, A.ASSIGNMENT_ID
, A.ENC_ELEMENT_TYPE_ID
, SUM(A.ENCUMBRANCE_AMOUNT)
, A.SUSPENSE_ORG_ACCOUNT_ID
, A.PROJECT_ID
, A.EXPENDITURE_ORGANIZATION_ID
, A.EXPENDITURE_TYPE
, A.TASK_ID
, A.AWARD_ID
, A.GL_CODE_COMBINATION_ID
, A.BUSINESS_GROUP_ID
, A.SET_OF_BOOKS_ID
FROM PSP_ENC_LINES A
WHERE A.SUSPENSE_ORG_ACCOUNT_ID IS NOT NULL
AND A.ENC_CONTROL_ID IN (SELECT ENC_CONTROL_ID
FROM PSP_ENC_CONTROLS
WHERE ACTION_CODE = 'N') GROUP BY A.BUSINESS_GROUP_ID
, A.SET_OF_BOOKS_ID
, A.PAYROLL_ID
, A.TIME_PERIOD_ID
, A.PERSON_ID
, A.ASSIGNMENT_ID
, A.ENC_ELEMENT_TYPE_ID
, A.SUSPENSE_ORG_ACCOUNT_ID
, A.PROJECT_ID
, A.EXPENDITURE_ORGANIZATION_ID
, A.EXPENDITURE_TYPE
, A.TASK_ID
, A.AWARD_ID
, A.GL_CODE_COMBINATION_ID UNION ALL SELECT DISTINCT B.PAYROLL_ID
, B.TIME_PERIOD_ID
, B.PERSON_ID
, B.ASSIGNMENT_ID
, B.ENC_ELEMENT_TYPE_ID
, SUM(B.ENCUMBRANCE_AMOUNT)
, B.SUSPENSE_ORG_ACCOUNT_ID
, B.PROJECT_ID
, B.EXPENDITURE_ORGANIZATION_ID
, B.EXPENDITURE_TYPE
, B.TASK_ID
, B.AWARD_ID
, B.GL_CODE_COMBINATION_ID
, B.BUSINESS_GROUP_ID
, B.SET_OF_BOOKS_ID
FROM PSP_ENC_LINES_HISTORY B
WHERE B.SUSPENSE_ORG_ACCOUNT_ID IS NOT NULL
AND B.CHANGE_FLAG IN ('U'
, 'N')
AND B.ENC_CONTROL_ID IN (SELECT ENC_CONTROL_ID
FROM PSP_ENC_CONTROLS
WHERE ACTION_CODE = 'P') GROUP BY B.BUSINESS_GROUP_ID
, B.SET_OF_BOOKS_ID
, B.PAYROLL_ID
, B.TIME_PERIOD_ID
, B.PERSON_ID
, B.ASSIGNMENT_ID
, B.ENC_ELEMENT_TYPE_ID
, B.SUSPENSE_ORG_ACCOUNT_ID
, B.PROJECT_ID
, B.EXPENDITURE_ORGANIZATION_ID
, B.EXPENDITURE_TYPE
, B.TASK_ID
, B.AWARD_ID
, B.GL_CODE_COMBINATION_ID