1. Implementing Procurement
  2. How You Display Text Attachments in the Purchase Order PDFs

How You Display Text Attachments in the Purchase Order PDFs

You may want to attach large text attachments to purchase orders. Attach text content with over 4000 characters, configure the data model, and document in the .rtf format to retrieve the text attachments directly from Oracle WebCenter Content.

Do these tasks to modify the data model to retrieve text attachments from WebCenter Content and to display them in the Purchase Order PDF.

  1. Create two data sets at each entity level.

    • The first data set is created to retrieve the document ID for the corresponding entity.

    • The second data set is created to retrieve the text attachment from the corresponding document ID.

  2. Create data links based on the corresponding entity ID and document ID.

  3. Modify the .rtf document to concatenate text attachments, when multiple text attachments exist at a particular entity level.

  4. Create data sets at the header level and then create data sets for the rest of the entities. Here's the data model sample for reference.

Create Data Sets at Header level

Create data sets at the header level and then create data sets for the rest of the entities. Here's the data model sample for reference.

  1. Create a SQL data set DraftHeaderAttachmentDataset with the corresponding query.

    Where data source is ApplicationDB_FSCM (Default)

    Type of SQL is Standard SQL

    SQL Query is as follows:

    SELECT FndDocumentsEO.DM_VERSION_NUMBER as"DOCUMENT_ID",header.PO_HEADER_ID AS"PoHeaderId"

    FROM FND_ATTACHED_DOCUMENTS FndAttachedDocumentEO, PO_HEADERS_DRAFTS_ALL

    headers, PO_VERSIONS versions, FND_DOCUMENTS_VL FndDocumentsEO

    WHERE

    ((FndAttachedDocumentEO.PK1_VALUE = headers.vendor_site_id and FndAttachedDocumentEO.ENTITY_NAME = 'POZ_SUPPLIER_SITES_ALL_M') OR (FndAttachedDocumentEO.PK1_VALUE = headers.po_header_id and FndAttachedDocumentEO.PK2_VALUE = versions.version_id and FndAttachedDocumentEO.ENTITY_NAME = 'PO_HEADERS')) AND FndAttachedDocumentEO.CATEGORY_NAME = 'TO_SUPPLIER' and (FndAttachedDocumentEO.ENTITY_NAME = 'PO_HEADERS') and headers.PO_HEADER_ID = versions.po_header_id and versions.co_sequence = nyl(:param_co_num,0) AND (FndAttachedDocumentEO.DOCUMENT_ID = FndDocumentsEO.DOCUMENT_ID)

  2. Create a data link from header data set - PdfDraftPurchaseOrderHeaderVORow to this data set based on PoHeaderId.

  3. Create another data set based on content server and select the previous data set as the parent group and map the Document ID from the previous data set. In the Edit Data Set - DraftHeaderUCMDataset dialog box, the name is Draft DraftHeaderUCMDataset, data source is FA_UCM_PROVISIONED, parent group is DraftHeaderAttachmentDataset, document ID is DraftHeaderAttachmentDataset. DOCUMENT_ID

  4. Generate the XML for the purchase order, which has text attachments at each level and verify that the attachment content is visible in the XML under DOCUMENT.

  5. Modify the .rtf document as follows:

    Replace the existing condition for TextAttachmentsContent at the corresponding entity level with the DOCUMENT attribute using repeating group for node (for example, G_2 in case of header entity as shown in the preceding text.)

    Note: Replicate the same for all the other entities using the queries provided in the Reference.

Reference

Data set Queries :

  1. Draft Header Attachment Data set

    SELECT FndDocumentsEO.dm_version_number AS "DOCUMENT_ID", headers.po_header_id AS "PoHeaderId" FROM fnd_attached_documents FndAttachedDocumentsEO, po_headers_draft_all headers, po_versions versions, fnd_documents_vl FndDocumentsEO WHERE ( ( FndAttachedDocumentsEO.pk1_value = headers.vendor_site_id AND FndAttachedDocumentsEO.entity_name = 'POZ_SUPPLIER_SITES_ALL_M' ) OR ( FndAttachedDocumentsEO.pk1_value = headers.po_header_id AND FndAttachedDocumentsEO.pk2_value = versions.version_id AND FndAttachedDocumentsEO.entity_name = 'PO_HEADERS' ) ) AND FndAttachedDocumentsEO.category_name = 'TO_SUPPLIER' AND ( FndAttachedDocumentsEO.entity_name = 'PO_HEADERS' ) AND headers.po_header_id = versions.po_header_id AND versions.co_sequence = Nvl(:param_co_num, 0) AND ( FndAttachedDocumentsEO.document_id = FndDocumentsEO.document_id )

  2. Draft Line Attachment Data set

    SELECT FndDocumentsEO.dm_version_number AS "DOCUMENT_ID", Lines.po_line_id AS "PoLineId", Nvl(Item.organization_id, PurchasingSystemParameterPEO.inventory_organization_id) AS "OrgId" FROM fnd_attached_documents FndAttachedDocumentsEO, fnd_documents_vl FndDocumentsEO, po_lines_draft_all Lines, po_versions Versions, egp_system_items_vl Item, po_headers_all SrcDocHeader, po_system_parameters_all PurchasingSystemParameterPEO WHERE ( ( FndAttachedDocumentsEO.pk1_value = Lines.po_line_id AND FndAttachedDocumentsEO.pk2_value = Versions.version_id AND FndAttachedDocumentsEO.entity_name = 'PO_LINES' ) OR ( FndAttachedDocumentsEO.pk2_value = Lines.item_id AND FndAttachedDocumentsEO.pk1_value = Item.organization_id AND FndAttachedDocumentsEO.entity_name = 'ITEM_ENTITY' ) OR ( FndAttachedDocumentsEO.pk1_value = Nvl(Lines.from_header_id, Lines.contract_id) AND FndAttachedDocumentsEO.pk2_value = SrcDocHeader.current_version_id AND FndAttachedDocumentsEO.entity_name = 'PO_PA_HEADERS' ) OR ( FndAttachedDocumentsEO.pk1_value = Lines.from_line_id AND FndAttachedDocumentsEO.pk2_value = SrcDocHeader.current_version_id AND FndAttachedDocumentsEO.entity_name = 'PO_PA_LINES' ) ) AND FndAttachedDocumentsEO.category_name = 'TO_SUPPLIER' AND ( FndAttachedDocumentsEO.document_id = FndDocumentsEO.document_id ) AND Lines.po_header_id = Versions.po_header_id AND Versions.co_sequence = Nvl(:param_co_num, 0) AND Lines.item_id = Item.inventory_item_id(+) AND SrcDocHeader.po_header_id = Nvl(Lines.from_header_id, Lines.contract_id) AND Lines.prc_bu_id = PurchasingSystemParameterPEO.prc_bu_id

  3. Draft Schedule Attachment Data set

    SELECT FndDocumentsEO.dm_version_number AS "DOCUMENT_ID", Schedules.line_location_id AS "LineLocationId" FROM fnd_attached_documents FndAttachedDocumentsEO, fnd_documents_vl FndDocumentsEO, po_line_locations_draft_all Schedules, po_versions versions WHERE FndAttachedDocumentsEO.pk1_value = Schedules.line_location_id AND FndAttachedDocumentsEO.pk2_value = versions.version_id AND FndAttachedDocumentsEO.entity_name = 'PO_LINE_LOCATIONS' AND FndAttachedDocumentsEO.category_name = 'TO_SUPPLIER' AND ( FndAttachedDocumentsEO.document_id = FndDocumentsEO.document_id ) AND Schedules.po_header_id = versions.po_header_id

  4. SELECT FndDocumentsEO.dm_version_number AS "DOCUMENT_ID", headers.po_header_id AS "PoHeaderId" FROM fnd_attached_documents FndAttachedDocumentsEO, po_headers_draft_all headers, po_versions versions, fnd_documents_vl FndDocumentsEO WHERE ( ( FndAttachedDocumentsEO.pk1_value = headers.vendor_site_id AND FndAttachedDocumentsEO.entity_name = 'POZ_SUPPLIER_SITES_ALL_M' ) OR ( FndAttachedDocumentsEO.pk1_value = headers.po_header_id AND FndAttachedDocumentsEO.pk2_value = versions.version_id AND FndAttachedDocumentsEO.entity_name = 'PO_HEADERS' ) ) AND FndAttachedDocumentsEO.category_name = 'TO_SUPPLIER' AND ( FndAttachedDocumentsEO.entity_name = 'PO_HEADERS' ) AND headers.po_header_id = versions.po_header_id AND versions.co_sequence = Nvl(:param_co_num, 0) AND ( FndAttachedDocumentsEO.document_id = FndDocumentsEO.document_id ) AND ( headers.from_co_seq <= versions.co_sequence AND headers.to_co_seq > versions.co_sequence )

  5. Archive Line Attachment Data set

    SELECT FndDocumentsEO.dm_version_number AS "DOCUMENT_ID", Lines.po_line_id AS "PoLineId", Nvl(Item.organization_id, PurchasingSystemParameterPEO.inventory_organization_id) AS "OrgId" FROM fnd_attached_documents FndAttachedDocumentsEO, fnd_documents_vl FndDocumentsEO, po_lines_draft_all Lines, po_versions Versions, egp_system_items_vl Item, po_headers_all SrcDocHeader, po_system_parameters_all PurchasingSystemParameterPEO WHERE ( ( FndAttachedDocumentsEO.pk1_value = Lines.po_line_id AND FndAttachedDocumentsEO.pk2_value = Versions.version_id AND FndAttachedDocumentsEO.entity_name = 'PO_LINES' ) OR ( FndAttachedDocumentsEO.pk2_value = Lines.item_id AND FndAttachedDocumentsEO.pk1_value = Item.organization_id AND FndAttachedDocumentsEO.entity_name = 'ITEM_ENTITY' ) OR ( FndAttachedDocumentsEO.pk1_value = Nvl(Lines.from_header_id, Lines.contract_id) AND FndAttachedDocumentsEO.pk2_value = SrcDocHeader.current_version_id AND FndAttachedDocumentsEO.entity_name = 'PO_PA_HEADERS' ) OR ( FndAttachedDocumentsEO.pk1_value = Lines.from_line_id AND FndAttachedDocumentsEO.pk2_value = SrcDocHeader.current_version_id AND FndAttachedDocumentsEO.entity_name = 'PO_PA_LINES' ) ) AND FndAttachedDocumentsEO.category_name = 'TO_SUPPLIER' AND ( FndAttachedDocumentsEO.document_id = FndDocumentsEO.document_id ) AND Lines.po_header_id = Versions.po_header_id AND Versions.co_sequence = Nvl(:param_co_num, 0) AND Lines.item_id = Item.inventory_item_id(+) AND SrcDocHeader.po_header_id = Nvl(Lines.from_header_id, Lines.contract_id) AND Lines.prc_bu_id = PurchasingSystemParameterPEO.prc_bu_id AND ( Lines.from_co_seq <= Versions.co_sequence AND Lines.to_co_seq > Versions.co_sequence )

  6. Archive Schedule Attachment Data set

    SELECT FndDocumentsEO.dm_version_number AS "DOCUMENT_ID", Schedules.line_location_id AS "LineLocationId" FROM fnd_attached_documents FndAttachedDocumentsEO, fnd_documents_vl FndDocumentsEO, po_line_locations_draft_all Schedules, po_versions versions WHERE FndAttachedDocumentsEO.pk1_value = Schedules.line_location_id AND FndAttachedDocumentsEO.pk2_value = versions.version_id AND FndAttachedDocumentsEO.entity_name = 'PO_LINE_LOCATIONS' AND FndAttachedDocumentsEO.category_name = 'TO_SUPPLIER' AND ( FndAttachedDocumentsEO.document_id = FndDocumentsEO.document_id ) AND Schedules.po_header_id = versions.po_header_id AND ( Schedules.from_co_seq <= versions.co_sequence AND Schedules.to_co_seq > versions.co_sequence )

Data Links

  • Draft Header Attachment Data set (G_1)

    PdfDraftPurchaseOrderHeaderVORow.PoHeaderId -> G_1.PoHeaderId

  • Draft Line Attachment Data set (G_3)

    PdfDraftPurchaseOrderLineVORow.PoLineId -> G_3.PoLineId

    PdfDraftPurchaseOrderLineVORow.OrgId -> G_3.PoLineId

  • Draft Schedule Attachment Data set (G_5)

    PdfDraftPurchaseOrderLineLocationVORow.LineLocationId -> G_5. LineLocationId

  • Archive Header Attachment (G_7)

    PdfArchivePurchaseOrderHeaderVORow.PoHeaderId -> G_7.PoHeaderId

  • Archive Line Attachment Data set (G_9)

    PdfArchivePurchaseOrderLineVORow.PoLineId -> G_9.PoLineId

    PdfArchivePurchaseOrderLineVORow.OrgId -> G_9.PoLineId

  • Archive Schedule Attachment Data set (G_11)

    PdfArchivePurchaseOrderLineLocationVORow.LineLocationId -> G_11. LineLocationId