Search Results attach_pdf

Overview

PSP_XMLGEN is an Oracle E-Business Suite PL/SQL package owned by the APPS schema and classified as an "OTHER" API within the ETRM repository. It belongs to the Oracle Time and Labor / Professional Services product family (PSP prefix) and serves as the XML generation and document-handling engine for the Effort Reporting and approver notification workflow. The package is declared AUTHID CURRENT_USER, meaning its SQL statements execute with the privileges of the invoking user rather than the package owner.

The package's principal business function is to construct XML payloads representing approvers, persons, and effort-report details; to convert those XML documents into PDF output; and to store and attach the resulting documents to Oracle Workflow items using the standard FND attachment and LOB infrastructure. It also maintains the Effort Reporting person-level XML data store and reconciles project/expenditure attributes against GL segment definitions.

Key Procedures and Functions

The package exposes thirteen documented program units, of which the following are the core generation and maintenance routines:

• GENERATE_APPROVER_HEADER_XML — a function returning a CLOB containing the header-level XML for approver output, optionally scoped by concurrent request ID.
• GENERATE_APPROVER_XML — a function returning a CLOB of approver XML keyed by Workflow item key.
• GENERATE_PERSON_XML — a function returning a CLOB of person-level XML, driven by person, template, effort report, set of books, and related descriptive attributes.
• STORE_PDF — a procedure that persists generated PDF content, returning a file identifier and taking the Workflow item key, receiver flag, and Workflow role name.
• ATTACH_PDF — a procedure that attaches a BLOB document to a Workflow item type, resolving content type and document type as IN OUT parameters.
• UPDATE_ER_PERSON_XML — an overloaded procedure (three signatures) that refreshes stored Effort Reporting person XML, variously by person range, by request ID, or by Workflow item key, returning a status.
• UPDATE_ER_DETAILS — a procedure that refreshes Effort Reporting detail records across a person range for a given request.
• COPY_PTAOE_FROM_GL_SEGMENTS — a procedure that populates project/expenditure attribute values from GL segment definitions for a person range and business group.
• CONVERT_XML_CONTROLS — a function that escapes or transforms control characters in an input string, added to address a specific UVA-related defect (Bug 4429787).

The metadata additionally lists UPDATE_ER_ERROR_DETAILS and UPDATE_GROUPING_CATEGORY as documented units, completing the set of thirteen.

Tables Accessed

PSP_XMLGEN reads and writes through APPS synonyms across several EBS schemas. Workflow and attachment processing relies on FND_ATTACHED_DOCUMENTS, FND_ATTACHED_DOCUMENTS_S, FND_DOCUMENT_CATEGORIES, FND_LOBS, and FND_NEW_MESSAGES. Concurrent request context and application metadata come from FND_CONCURRENT_REQUESTS and FND_APPLICATION. User and key flexfield lookups draw on FND_USER, FND_LOOKUP_VALUES, and FND_ID_FLEX_SEGMENTS.

Effort Reporting and HR data are sourced from PER_ALL_ASSIGNMENTS_F, HR_ALL_ORGANIZATION_UNITS, GMS_PERSONNEL, and GMS_AWARDS_ALL. Project and expenditure attributes are read from PA_PROJECTS_ALL. Together these support XML generation, PDF storage, attachment linkage, and the GL-segment copy routine.

Usage Notes

PSP_XMLGEN is invoked primarily by the Effort Reporting concurrent programs and by Oracle Workflow notification activity handlers that deliver approver packets. The XML generator functions are called to build CLOB payloads, which are then converted to PDF and routed through STORE_PDF and ATTACH_PDF for persistence into FND_LOBS and the attachment tables. Its overloaded UPDATE_ER_* procedures are typically scheduled as batch maintenance to refresh stored XML after person or assignment changes. Custom code should call these units only through the documented APPS synonyms and avoid direct table DML, as internal orchestration of request IDs and Workflow item keys is required. The package is referenced by two other documented packages, confirming it is a dependent utility rather than an entry-point API.