Search Results notified_type_cd

Overview

The HR.PQH_FYI_NOTIFY table is a core data repository within the Oracle E-Business Suite (EBS) Human Resources (HR) module, specifically supporting the PeopleSoft HRMS (PQH) product lines. Its primary function is to manage the audit trail and status of "For Your Information" (FYI) notifications generated by various HR transactional workflows. These workflows include approvals, overrides, rejections, and other business process events in modules such as Position Management and Workforce Sourcing. The table acts as a log, recording each notification instance, its intended recipient, the associated transaction, and its current delivery status, thereby ensuring transparency and traceability in HR process communications.

Key Information Stored

The table's structure is designed to capture the lifecycle of a notification. The primary key, FYI_NOTIFIED_ID, uniquely identifies each notification record. Critical transactional context is stored in TRANSACTION_CATEGORY_ID and the generic TRANSACTION_ID, which together pinpoint the specific business document (e.g., a position or requisition) that triggered the alert. The NOTIFICATION_EVENT_CD and NOTIFIED_TYPE_CD columns classify the nature of the event (e.g., 'Approval', 'Rejection') and the role of the recipient (e.g., 'Approver', 'Last User'), respectively. For ad-hoc recipients, the NOTIFIED_NAME field is populated. The STATUS column (e.g., 'Send', 'Pending') tracks the notification's processing state, while NOTIFICATION_DATE records when it was generated. Standard EBS audit columns (OBJECT_VERSION_NUMBER, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) are maintained for data integrity and compliance.

Common Use Cases and Queries

A primary use case is diagnosing workflow notification issues or auditing approval histories. Support personnel or system administrators often query this table to verify if a notification was generated and sent for a specific transaction. Common reporting needs include listing all pending notifications or summarizing notification activity by event type or recipient over a period.

Sample SQL patterns include retrieving notifications for a specific transaction category and ID: SELECT * FROM hr.pqh_fyi_notify WHERE transaction_category_id = &cat_id AND transaction_id = &trans_id ORDER BY creation_date DESC; Or, finding all pending notifications: SELECT notified_name, notification_event_cd, creation_date FROM hr.pqh_fyi_notify WHERE status = 'Pending' AND notification_date > SYSDATE - 7; The standard query provided in the ETRM serves as the foundational SELECT statement for any custom report or data extraction related to FYI notifications.

Related Objects

The HR.PQH_FYI_NOTIFY table has defined relationships with other key EBS objects, primarily through foreign key constraints. Its primary key, PQH_FYI_NOTIFY_PK on the FYI_NOTIFIED_ID column, ensures each record's uniqueness. A significant foreign key relationship exists via the TRANSACTION_CATEGORY_ID column, which references the PQH_TRANSACTION_CATEGORIES table (HR.PQH_TRANSACTION_CATEGORIES). This link provides the descriptive meaning and context for the transaction category identifiers stored in PQH_FYI_NOTIFY. The table is also referenced by an internal database object named PQH_FYI_NOTIFY#, which is typically an associated index or snapshot. This structure indicates that PQH_FYI_NOTIFY is a central point in the notification subsystem, receiving its category definitions from a master table and being accessed by other system components for data retrieval.