Extending System Generated Description (SGD)

Overview

You can customize the System Generated Description (SGD) to meet your agency's specific requirements. The following diagram outlines how SGD is generated in CLM:

Generating SGD

the picture is described in the document text

In order to create simple customizations, you can change one or more entries in the Configuration Table (A) that serves as a registry.

In order to perform complex customizations, you can add/modify Source Tables / Views (B), add/modify entries in Configuration Table (A) , modify the Data Template (C) to get the values to XML, or customize the layout in RTF Template (D).

To create your own customized entries for generating SGD, you may not use any of the above steps and obtain the required differences directly on the data template from custom source (E).

The following steps outline the process by which you can perform the customization:

  1. Review the SGD output from the predefined template.

  2. Run the Generate XML for CLM Documents concurrent program to get the XML generated for SGD. Review the XML output to identify what to customize, that is, source to difference generator, data template or printing layout.

  3. Create/Edit appropriate supporting views (this becomes the data sources for the difference generator). You should create customized sources if any change is required in predefined sources.

    Configure the table that feeds the difference generator to pick the new attribute/source or to control predefined attribute/source.

  4. Edit the appropriate data template to pick the new source/attribute. If it is a predefined source, and an attribute has been enabled in the configuration table, the difference generator will automatically report this difference.

    Edit the RTF template to print the differences of the attribute added, if required.

Customizing SGD

Source Views

Difference generator relies on source views to give the base and modified (updated) records for a given entity as attribute name-value pair. Two views are required one for base and one for mod/amendment records. Depending on the entity, you can select appropriate values as primary key values (Upto 5 values are supported) to uniquely identify a record.

The following are the columns that are expected out of the source views:

Column Data Type Description
PK1_VALUE Number Primary key value. This first value is mandatory.
PK2_VALUE Number Primary key value. Can be left null based on your entity.
PK3_VALUE Number Primary key value. Can be left null based on your entity.
PK4_VALUE Number Primary key value. Can be left null based on your entity.
PK5_VALUE Number Primary key value. Can be left null based on your entity.
COL_NAME Varchar2(40) Column/attribute name
COL_VALUE Varchar2(4000) Column/attribute value
COL_DESC Varchar2(4000) Column/attribute description. This is primarily for non-numeric columns.

A sample definition is given below, however any definition can be adopted as long as the above metadata can be used.

with po_header_unpivot_data as (
(
SELECT ph.po_header_id,
ph.draft_id,
ph.org_id,
to_char(po_datatemplate_pkg.get_header_amount_ordered(ph.po_header_id, ph.draft_id)) total_amount,
ph.acceptance_required_flag,
to_char(ph.acceptance_due_date) acceptance_due_date,
null as clm_noofcopies,
to_char(ph.clm_contract_officer) clm_contract_officer,
ph.user_document_status,
to_char(ph.vendor_site_id) vendor_site_id,
to_char(ph.vendor_contact_id) vendor_contact_id,
ph.supplier_notif_method,
ph.fax,
ph.email_address,
ph.clm_external_idv,
ph.clm_vendor_offer_number,
to_char(ph.clm_effective_date) clm_effective_date,
to_char(ph.agent_id) agent_id,
to_char(ph.clm_award_administrator) clm_award_administrator,
ph.comments,
ph.confirming_order_flag,
to_char(ph.terms_id) terms_id,
to_char(ph.clm_no_signed_copies_to_return) clm_no_signed_copies_to_return,
to_char(ph.ship_to_location_id) ship_to_location_id,
ph.ship_via_lookup_code,
ph.freight_terms_lookup_code,
ph.shipping_control,
ph.note_to_vendor,
ph.note_to_receiver,
ph.change_status

FROM po_headers_draft_all ph
     
WHERE ph.po_header_id = PO_GEN_DIFF_PKG.getModPK1
      and ph.draft_id = PO_GEN_DIFF_PKG.getModPK2 )
)

select  po_header_id pk1_value, draft_id pk2_value,  null pk3_value, null pk4_value, null pk5_value, 
  col_name , col_value, 
       decode(col_name, 
       'ACCEPTANCE_REQUIRED_FLAG', (select meaning from fnd_lookup_values 
                                    where lookup_code = col_value 
                                    and lookup_type = 'ACCEPTANCE_REQUIRED' 
                                    and language = userenv('lang') and enabled_flag = 'Y'),
                        'CLM_CONTRACT_OFFICER', (select full_name from per_all_people_f 
                              where person_id = col_value and trunc(sysdate) between effective_start_date 
                              and effective_end_date),
                        'USER_DOCUMENT_STATUS', (select meaning from fnd_lookup_values 
                              where lookup_code  = col_value  
                              and lookup_type = 'PO_USER_DOCUMENT_STATUS' and language  = userenv('lang')
                              and enabled_flag = 'Y'),
                        'VENDOR_SITE_ID', (select vendor_site_code from po_vendor_sites_all 
                         where vendor_site_id = col_value),
                        'VENDOR_CONTACT_ID', (select last_name ||', '||prefix||' '||first_name 
                            from po_vendor_contacts where vendor_contact_id  = col_value),
                        'SUPPLIER_NOTIF_METHOD', (select meaning from fnd_lookup_values 
                             where lookup_code  = col_value and lookup_type = 'DOCUMENT_COMMUNICATION_METHOD' and language = userenv('lang')
                             and enabled_flag = 'Y'),
                        'AGENT_ID', (select full_name from per_all_people_f 
                  where person_id = col_value and trunc(sysdate) between effective_start_date and effective_end_date),
                        'CLM_AWARD_ADMINISTRATOR', (select full_name from per_all_people_f where person_id = col_value  
                                 and trunc(sysdate) between effective_start_date and effective_end_date),
                        'CONFIRMING_ORDER_FLAG', (select meaning from fnd_lookups where lookup_code = col_value and lookup_type = 'YES_NO'),
                        'TERMS_ID', (select name from ap_terms where term_id  = col_value),
                        'SHIP_TO_LOCATION_ID', (select location_code from hr_locations_all where location_id  = col_value),
                        'FREIGHT_TERMS_LOOKUP_CODE', (select meaning from fnd_lookup_values where lookup_code = col_value and lookup_type  = 'FREIGHT TERMS'
                                    and language = userenv('lang') and enabled_flag = 'Y'),
                        'SHIPPING_CONTROL', (select meaning from fnd_lookup_values where lookup_code  = col_value 
                           and lookup_type = 'SHIPPING CONTROL' and language = userenv('lang') and enabled_flag = 'Y'),
      'SHIP_VIA_LOOKUP_CODE' ,      (  select nvl(ofc.freight_code_tl, col_value) 
                                      from  org_freight_tl ofc 
                                      where ofc.freight_code = col_value
                                      and ofc.organization_id = org_id 
                                       and ofc.language = userenv('lang') )  ,           
                         null) col_desc
from po_header_unpivot_data
unpivot include nulls
( col_value
  for col_name in 
  (
TOTAL_AMOUNT,
ACCEPTANCE_REQUIRED_FLAG,
ACCEPTANCE_DUE_DATE,
CLM_NOOFCOPIES,
CLM_CONTRACT_OFFICER,
USER_DOCUMENT_STATUS,
VENDOR_SITE_ID,
VENDOR_CONTACT_ID,
SUPPLIER_NOTIF_METHOD,
FAX,
EMAIL_ADDRESS,
CLM_EXTERNAL_IDV,
CLM_VENDOR_OFFER_NUMBER,
CLM_EFFECTIVE_DATE,
AGENT_ID,
CLM_AWARD_ADMINISTRATOR,
COMMENTS,
CONFIRMING_ORDER_FLAG,
TERMS_ID,
CLM_NO_SIGNED_COPIES_TO_RETURN,
SHIP_TO_LOCATION_ID,
SHIP_VIA_LOOKUP_CODE,
FREIGHT_TERMS_LOOKUP_CODE,
SHIPPING_CONTROL,
NOTE_TO_VENDOR,
NOTE_TO_RECEIVER,
CHANGE_STATUS));

Technical Considerations while Customizing SGD

Configuration table

The difference generator framework gets its input from the configuration table PO_DIFF_CONFIG. This configuration table controls the source and attribute that are eligible for reporting the differences. Refer to e-TRM to obtain a detailed explanation of the columns of this table. Multiple sources are supported for a given document type-entity combination. For a given document type and entity, you can define multiple sources to get the required information.

Following are some of the common actions (with sample sql) that you will need to perform on the configuration table:

The differences generated are inserted into PO_ENTITY_DIFFERENCES table. Once the configuration table is configured, the values are automatically populated into PO_ENTITY_DIFFERENCES table. Refer to e-TRM for details on the columns of this table.

Based on this table entry, you can fetch data into the BI Publisher data template for a given document type and entity which will appropriately generate XML.

Data XML and Print templates

Refer to the Appendix for a list of data definitions and print templates used for SGD.