Product: | PAY - Payroll |
---|---|
Description: | This view returns value, user_name of ff_archive_items based on context1.The column 'context1' is stored assignment_action_id.'user_name' is stored database item name, 'value' is stoted its value. |
Implementation/DBA Data: | APPS.PAY_KR_YER_ARCHIVES_V |
SELECT V.ROW_ID
, V.ARCHIVE_ITEM_ID
, V.USER_ENTITY_ID
, V.ASSIGNMENT_ACTION_ID CONTEXT1
, V.VALUE
, V.ARCHIVE_TYPE
, ITM.UPDATABLE_FLAG
, ITM.DISPLAY_SEQUENCE
, V.LAST_UPDATE_DATE
, V.LAST_UPDATED_BY
, V.LAST_UPDATE_LOGIN
, V.CREATED_BY
, V.CREATION_DATE
, V.OBJECT_VERSION_NUMBER
, V.USER_NAME
, V.DATA_TYPE
FROM PAY_REPORT_FORMAT_ITEMS_F ITM
, ( SELECT PPA.REPORT_TYPE
, PPA.REPORT_QUALIFIER
, PPA.REPORT_CATEGORY
, PPA.EFFECTIVE_DATE
, PAA.ASSIGNMENT_ACTION_ID
, ACH.ROWID ROW_ID
, ACH.ARCHIVE_ITEM_ID
, ACH.USER_ENTITY_ID
, ACH.VALUE
, ACH.ARCHIVE_TYPE
, ACH.LAST_UPDATE_DATE
, ACH.LAST_UPDATED_BY
, ACH.LAST_UPDATE_LOGIN
, ACH.CREATED_BY
, ACH.CREATION_DATE
, ACH.OBJECT_VERSION_NUMBER
, DBI.USER_NAME
, DBI.DATA_TYPE
FROM FF_DATABASE_ITEMS DBI
, FF_ARCHIVE_ITEMS ACH
, PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAA
WHERE PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND ACH.CONTEXT1 = PAA.ASSIGNMENT_ACTION_ID
AND NVL(ACH.ARCHIVE_TYPE
, 'AAP') IN ('AAP'
, 'AAC')
AND DBI.USER_ENTITY_ID = ACH.USER_ENTITY_ID ) V
WHERE ITM.REPORT_TYPE(+) = V.REPORT_TYPE
AND ITM.REPORT_QUALIFIER(+) = V.REPORT_QUALIFIER
AND ITM.REPORT_CATEGORY(+) = V.REPORT_CATEGORY
AND ITM.USER_ENTITY_ID(+) = V.USER_ENTITY_ID
AND V.EFFECTIVE_DATE BETWEEN ITM.EFFECTIVE_START_DATE(+)
AND ITM.EFFECTIVE_END_DATE(+)