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
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:
Review the SGD output from the predefined template.
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.
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.
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.
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));
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:
To query predefined values from configuration table:
select * from po_diff_config where diff_config_id < 10000 order by document_type, entity_name, sub_entity_name, display_seq_number
To enable/disable predefined attribute differences from being reported, you need to set the IS_PRINTABLE_FLAG to Y/N for a given attribute, document type, and entity in the configuration table:
update po_diff_config set IS_PRINTABLE_FLAG = 'Y' , last_update_date = to_date(('17-JAN-12','DD-MON-RR'), last_updated_by = &your_login where document_type = &required_doc_type and entity_name = &required_entity_name and mod_doc_source_name = &mod_doc_source_name and COLUMN_NAME = &required_column_name;
To enable/disable predefined attribute differences from being reported as rolled-up values, you need to set the ROLLUP_ELIGIBILITY_FLAG to Y/N for a given attribute, document type, and entity in the configuration table:
update po_diff_config set ROLLUP_ELIGIBILITY_FLAG = 'N' -- set to 'Y'/'N' based on the setting you need last_update_date = to_date(('17-JAN-12','DD-MON-RR'), last_updated_by = &your_login where document_type = &required_doc_type and entity_name = &required_entity_name and mod_doc_source_name = &mod_doc_source_name and COLUMN_NAME = &required_column_name;
To override existing source and replace with custom sources for reporting the differences for a document type, entity: set the IGNORE_SOURCE_FLAG for all entries in the configuration table for the given document type and entity:
update po_diff_config set ignore_source_flag = 'Y' , last_update_date = to_date(('17-JAN-12','DD-MON-RR'), last_updated_by = &your_login where document_type = &required_doc_type and entity_name = &required_entity_name and mod_doc_source_name = &mod_doc_source_name; --(if a specific source is to be overridden)
To insert values into the configuration table:
Given below is a sample insert for AWARD document type and DISTRIBUTION entity name:
INSERT INTO PO_DIFF_CONFIG( DIFF_CONFIG_ID , DOCUMENT_TYPE , ENTITY_NAME , MOD_DOC_SOURCE_NAME , COLUMN_NAME , BASE_DOC_SOURCE_NAME , SUB_ENTITY_NAME , CMP_FILTER_PRED_FLAG , IS_PRINTABLE_FLAG , ROLLUP_ELIGIBILITY_FLAG, DISPLAY_SEQ_NUMBER , DISP_DATA_TYPE , LABEL_MESSAGE_CODE , IGNORE_SOURCE_FLAG , CREATED_BY , CREATION_DATE , LAST_UPDATED_BY , LAST_UPDATE_DATE , LAST_UPDATE_LOGIN ) VALUES ( PO_DIFF_CONFIG_S.nextval , 'AWARD' , 'DISTRIBUTION' , 'PO_SGD_CUSTOM_MOD_DIST_V' , 'FUNDED_VALUE' , 'PO_SGD_CUSTOM_DIST_V' , 'BILLING' , 'N' , 'Y' , 'N' , 1 , 'M' , -- data type (C, D, N, M) 'PO_SGD_AMT_FUNDED' , -- required if IS_PRINTABLE_FLAG ='Y' NULL , 1314 , -- User id from fnd_user to_date('17-JAN-12','DD-MON-RR'), 1314 , -- User id from fnd_user to_date('17-JAN-12','DD-MON-RR'), 0);
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.
Refer to the Appendix for a list of data definitions and print templates used for SGD.
New and deleted records are not identified via difference generator framework. The new and deleted records are easier to identify without the difference generator. The difference generator reports the updated records and attributes only. This has to be done in the BI Publisher data template.
It is not essential to use the difference generator always to obtain the required differences in XML. If the differences can be obtained from a custom source, then BI Publisher data template can be customized to pick the appropriate values.
To customize the BI publisher data template, refer to the Oracle Business Intelligence Publisher User's Guide for more details on the usage of data templates.
To customize the BI Publisher RTF template, refer to the Oracle Business Intelligence Publisher Report Designer's Guide for detailed instructions.