Wednesday 27 February 2013

Oracle FNDLOAD Scripts

1. Lookups 
-- ------------- 
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct XX_CUSTOM_LKP.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="XXCUST" LOOKUP_TYPE="XX_LOOKUP_TYPE"

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct XX_CUSTOM_LKP.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

2. Concurrent Program 
-- ----------------------------- 

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_CP.ldt PROGRAM APPLICATION_SHORT_NAME="XXCUST" CONCURRENT_PROGRAM_NAME="XX_CONCURRENT_PROGRAM"

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_CP.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

3. Profile 
-- --------- 

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct XX_CUSTOM_PRF.ldt PROFILE PROFILE_NAME="XX_PROFILE_NAME" APPLICATION_SHORT_NAME="XXCUST"

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct XX_CUSTOM_PRF.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

4. Request Set and Link 
-- ------------------------------ 

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XX_CUSTOM_RS.ldt REQ_SET REQUEST_SET_NAME='REQUEST_SET_NAME'

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct  XX_CUSTOM_RS.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

5. FND Message 
-- --------------------- 
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_CUSTOM_MESG.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME="XXCUST" MESSAGE_NAME="MESSAGE_NAME%"

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_CUSTOM_MESG.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

6. D2K FORMS 
-- ------------------ 

$FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FRM.ldt FORM FORM_NAME="FORM_NAME" 
       
$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FRM.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

7. Form Function 
-- --------------------- 

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FUNC.ldt FUNCTION FUNCTION_NAME="FORM_FUNCTION_NAME"

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FUNC.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

8. Alerts 
-- --------- 

FNDLOAD apps/apps 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct XX_CUSTOM_ALR.ldt ALR_ALERTS APPLICATION_SHORT_NAME=XXCUST ALERT_NAME="XX - Alert Name"

FNDLOAD apps/apps 0 Y UPLOAD $ALR_TOP/patch/115/import/alr.lct XX_CUSTOM_ALR.ldt CUSTOM_MODE=FORCE

9. Value Set 
-- -------------- 

$FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_CUSTOM_VS.ldt VALUE_SET FLEX_VALUE_SET_NAME="XX Value Set Name"

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct XX_CUSTOM_VS.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

10. Data Definition and Associated Template 
--- ---------------------------------------------------------- 

FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD  $XDO_TOP/patch/115/import/xdotmpl.lct XX_CUSTOM_DD.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME='XXCUST' DATA_SOURCE_CODE='XX_SOURCE_CODE' TMPL_APP_SHORT_NAME='XXCUST' TEMPLATE_CODE='XX_SOURCE_CODE'

Sample Code:-
---------------------
FNDLOAD apps/apps O Y DOWNLOAD  $XDO_TOP/patch/115/import/xdotmpl.lct XX_XML_TEMP_DATA.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME='MASAPP' DATA_SOURCE_CODE='MAS_AR_RCPT_RGTR_RPT' TMPL_APP_SHORT_NAME='MASAPP' TEMPLATE_CODE='MAS_AR_RCPT_RGTR_RPT'

FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XX_CUSTOM_DD.ldt

11. DATA_TEMPLATE (Data Source .xml file) 
--- ---------------------------------------------------------- 

java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD -DB_USERNAME apps -DB_PASSWORD apps -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX_HOST_NAME)(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=XX_SERVICE_NAME)))' -LOB_TYPE DATA_TEMPLATE -LOB_CODE XX_TEMPLATE -APPS_SHORT_NAME XXCUST -LANGUAGE en -lct_FILE $XDO_TOP/patch/115/import/xdotmpl.lct -LOG_FILE $LOG_FILE_NAME

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD apps -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX_HOST_NAME)(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=XX_SERVICE_NAME)))' -LOB_TYPE DATA_TEMPLATE -LOB_CODE XX_TEMPLATE -XDO_FILE_TYPE XML -FILE_NAME $DATA_FILE_PATH/$DATA_FILE_NAME.xml -APPS_SHORT_NAME XXCUST -NLS_LANG en -TERRITORY US -LOG_FILE $LOG_FILE_NAME

12. RTF TEMPLATE (Report Layout .rtf file) 
--- ------------------------------------------------------- 

java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD -DB_USERNAME apps -DB_PASSWORD apps -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX_HOST_NAME)(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=XX_SERVICE_NAME)))' -LOB_TYPE TEMPLATE -LOB_CODE XX_TEMPLATE -APPS_SHORT_NAME XXCUST -LANGUAGE en -TERRITORY US -lct_FILE $XDO_TOP/patch/115/import/xdotmpl.lct -LOG_FILE $LOG_FILE_NAME

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD apps -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX_HOST_NAME)(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=SERVICE_NAME)))' -LOB_TYPE TEMPLATE -LOB_CODE XX_TEMPLATE -XDO_FILE_TYPE RTF -FILE_NAME $RTF_FILE_PATH/$RTF_FILE_NAME.rtf -APPS_SHORT_NAME XXCUST -NLS_LANG en -TERRITORY US -LOG_FILE $LOG_FILE_NAME

Tuesday 26 February 2013

Oracle iProcurement Tables



    ICX_AUDIT
    ICX_CART_DISTRIBUTIONS
    ICX_CART_LINE_DISTRIBUTIONS
    ICX_CATG_DETLS_INFO
    ICX_CATG_DETLS_INFO_TL
    ICX_CAT_ADV_SRCH_SETUP
    ICX_CAT_BROWSE_TREES
    ICX_CAT_CATEGORIES_TL
    ICX_CAT_CATEGORY_ITEMS
    ICX_CAT_DELETED_ATTRIBUTES
    ICX_CAT_DESCRIPTORS_TL
    ICX_CAT_EXT_ITEMS_TLP
    ICX_CAT_ITEMS_B
    ICX_CAT_ITEMS_CTX_TLP
    ICX_CAT_ITEMS_TLP
    ICX_CAT_ITEM_PRICES
    ICX_CAT_ITEM_SRC_DETAILS
    ICX_CAT_POPULATE_CTX_GT
    ICX_CAT_PRICE_HISTORY
    ICX_CAT_PRICE_LISTS
    ICX_CAT_SCHEMA_VERSIONS
    ICX_CAT_STORES_B
    ICX_CAT_STORES_TL
    ICX_CAT_STORE_CATALOGS
    ICX_CAT_UPLOAD_IT_DUMP
    ICX_CAT_UPLOAD_PRICE_DUMP
    ICX_COLORS
    ICX_CONTEXT_RESULTS_TEMP
    ICX_CUSTOM_MENU_ENTRIES
    ICX_ENABLED_PLSQL
    ICX_FAILURES
    ICX_ITEM_DETLS_INFO
    ICX_ITEM_DETLS_INFO_TL
    ICX_MARGIN_ANALYSIS
    ICX_MARGIN_ANALYSIS_ERR
    ICX_OE_CARTS
    ICX_OE_CART_ERRORS
    ICX_OE_CART_LINES
    ICX_PAGES
    ICX_PAGES_TL
    ICX_PAGE_COLORS
    ICX_PAGE_COLORS_TL
    ICX_PAGE_COLOR_SCHEME
    ICX_PAGE_PLUGS
    ICX_PANEL_SUMMARY
    ICX_PARAMETERS
    ICX_PORTLET_CUSTOMIZATIONS
    ICX_POR_BATCH_JOBS
    ICX_POR_BATCH_JOB_DETAILS
    ICX_POR_BROWSE_CATEGORIES
    ICX_POR_BUGSEYE_CONFIG
    ICX_POR_BUGSEYE_FLAGS
    ICX_POR_BUYER_APPROVAL_JOBS
    ICX_POR_CATALOG_ATTACHMENTS
    ICX_POR_CATALOG_PREFERENCES
    ICX_POR_CATEGORIES_TL
    ICX_POR_CATEGORY_DATA_SOURCES
    ICX_POR_CATEGORY_DEFAULTS
    ICX_POR_CATEGORY_ITEMS
    ICX_POR_CATEGORY_ORDER_MAP
    ICX_POR_CONFIG_PARAMETERS
    ICX_POR_CONTEXT_POLICY
    ICX_POR_CONTRACT_REFERENCES
    ICX_POR_CTX_TL
    ICX_POR_DAILY_NOTIFICATIONS
    ICX_POR_DESCRIPTORS_CACHE
    ICX_POR_DESCRIPTORS_TL
    ICX_POR_DOWNLOAD_JOB_DETAILS
    ICX_POR_EXT_QUERY
    ICX_POR_EXT_QUERY_TL
    ICX_POR_FAILED_ATTACHMENTS
    ICX_POR_FAILED_LINES
    ICX_POR_FAILED_LINE_MESSAGES
    ICX_POR_ITEMS
    ICX_POR_ITEMS_TL
    ICX_POR_ITEM_CONSTRAINT
    ICX_POR_ITEM_CTX_INDEX
    ICX_POR_ITEM_SITE
    ICX_POR_ITEM_SOURCES
    ICX_POR_ITEM_SOURCES_TL
    ICX_POR_LOADER_VALUES
    ICX_POR_LOAD_ITEM_MATCH
    ICX_POR_LOG
    ICX_POR_MANAGE_GROUPS_CACHE
    ICX_POR_ORACLE_ITEM_SUB
    ICX_POR_PRICE_BREAKS
    ICX_POR_PRICE_HISTORY
    ICX_POR_PRICE_LISTS
    ICX_POR_PRICE_LIST_LINES
    ICX_POR_PRICE_LIST_LINES_CACHE
    ICX_POR_REALMS
    ICX_POR_REALMS_TL
    ICX_POR_REALM_COMPONENTS
    ICX_POR_SEARCH_CONFIG
    ICX_POR_SEARCH_CONFIG_POLICY
    ICX_POR_SITE
    ICX_POR__OF_CONTENTS_TL
    ICX_POR_TEMPLATE_FILES
    ICX_POR_THIRD_PARTY_LOGS
    ICX_POR_THIRD_PARTY_LOG_VALUES
    ICX_POR_TITLE_ADMIN
    ICX_POR_TITLE_ADMIN_TL
    ICX_POR_TITLE_REGISTRY
    ICX_POR_UNIT
    ICX_POR_UNSPSC_CODES
    ICX_POR_UPLOADER_SUB
    ICX_POR_UPLOAD_ITEMS_IT
    ICX_POR_UPLOAD_IT_DUMP
    ICX_POR_UPLOAD_PRICES_IT
    ICX_POR_UPLOAD_PRICE_BREAKS_DP
    ICX_POR_UPLOAD_PRICE_BREAKS_IT
    ICX_POR_VERSION_INFO
    ICX_PO_REVISIONS_TEMP
    ICX_PROCUREMENT_SERVER_SETUP
    ICX_PROMPTS
    ICX_QUESTIONS
    ICX_QUESTIONS_TL
    ICX_QUESTION_FUNCTIONS
    ICX_REGIONS
    ICX_RELATED_CATEGORIES
    ICX_REQUISITIONER_INFO
    ICX_REQ_CART_ERRORS
    ICX_SESSIONS
    ICX_SESSION_ATTRIBUTES
    ICX_SHOPPING_CARTS
    ICX_SHOPPING_CART_LINES
    ICX_STORE_INDEX
    ICX_TEMPLATE_TAGS
    ICX_TEXT
    ICX_TRANSACTIONS
    ICX_UNSPSC_CODES
    ICX_USER_PROFILES
    ICX_VERSIONS
    ICX_WEB_STORE_OPTIONS
    POR_CONFIGURABLE_USER_DATA
    POR_CONTAINER_IMAGES
    POR_EMPLOYEE_LOADER_VALUES
    POR_FAVORITE_LIST_HEADERS
    POR_FAVORITE_LIST_LINES
    POR_FAV_CHARGE_ACCOUNTS
    POR_FEED_FIELD_FORMATS
    POR_FEED_HISTORY
    POR_FEED_RECORDS
    POR_IMAGES
    POR_IMAGE_LANG_SETTINGS
    POR_ITEM_ATTRIBUTE_VALUES
    POR_LOCATION_LOADER_VALUES
    POR_LOV_DISPLAY_RESULTS
    POR_LOV_RESULT_VALUES
    POR_TEMPLATE_ASSOC
    POR_TEMPLATE_INFO
    POR_USER_DATA_CONF_LIST
    POR_USER_DATA_CONF_LIST_ITEMS
    POS_ACK_SELECT
    POS_ASN_SEARCH_RESULT
    POS_ASN_SHOP_CART_DETAILS
    POS_ASN_SHOP_CART_HEADERS

=========================  ***********************   =====================


Table Name

Feature Area

ICX_CAT_ATTRIBUTES_TL

Catalog Authoring, Shopping

ICX_CAT_BATCH_JOBS_ALL

Catalog Authoring

ICX_CAT_CONTENT_ZONES_B

Catalog Authoring, Shopping

ICX_CAT_CONTENT_ZONES_TL

Catalog Authoring, Shopping

ICX_CAT_CONVERTER_DETAILS

Catalog Authoring

ICX_CAT_DELETED_SQES

Catalog Authoring

ICX_CAT_FAV_LIST_HEADERS

Catalog Authoring, Shopping

ICX_CAT_FAV_LIST_LINES_TLP

Catalog Authoring, Shopping

ICX_CAT_ITEMS_CTX_DTLS_TLP

Catalog Authoring, Shopping

ICX_CAT_ITEMS_CTX_HDRS_TLP

Catalog Authoring, Shopping

ICX_CAT_PARSE_ERRORS

Catalog Authoring

ICX_CAT_PUNCHOUT_ZONE_DETAILS

Catalog Authoring, Shopping

ICX_CAT_R12_UPGRADE

R12 Upgrade

ICX_CAT_R12_UPGRADE_JOBS

R12 Upgrade

ICX_CAT_R12_UPG_AUTOSOURCE

R12 Upgrade

ICX_CAT_R12_UPG_EXCEP_FILES

R12 Upgrade

ICX_CAT_R12_UPG_ERROR_MSGS

R12 Upgrade

ICX_CAT_SECURE_CONTENTS

Catalog Authoring, Shopping

ICX_CAT_SHOP_STORES_B

Catalog Authoring, Shopping

ICX_CAT_SHOP_STORES_TL

Catalog Authoring, Shopping

ICX_CAT_STORE_CONTENTS

Catalog Authoring, Shopping

ICX_CAT_ZONE_ATTRIBUTES

Catalog Authoring, Shopping

ICX_CAT_ZONE_SECURE_ATTRIBUTES

Catalog Authoring, Shopping

Changed Tables

N/A

Obsolete Tables

Table Name

Feature Area

Optional - Replaced By

ICX_CAT_CATEGORY_ITEMS

Catalog Authoring, Shopping

ICX_CAT_EXT_ITEMS_TLP

Catalog Authoring, Shopping

PO_ATTRIBUTE_VALUES,

PO_ATTRIBUTE_VALUES_TLP

ICX_CAT_ITEMS_B

Catalog Authoring, Shopping

ICX_CAT_ITEMS_TLP

Catalog Authoring, Shopping

ICX_CAT_ITEMS_CTX_HDRS_TLP

ICX_CAT_ITEM_PRICES

Catalog Authoring, Shopping

ICX_CAT_ITEM_SRC_DETAILS

Catalog Authoring, Shopping

ICX_CAT_ZONE_SECURE_ATTRIBUTES,

ICX_CAT_ZONE_ATTRIBUTES

ICX_POR_ITEM_SOURCES

Catalog Authoring, Shopping

ICX_CAT_CONTENT_ZONES_B

ICX_POR_ITEM_SOURCES_TL

Catalog Authoring, Shopping

ICX_CAT_CONTENT_ZONES_TL

ICX_CAT_PRICE_LISTS

Catalog Authoring, Shopping

ICX_CAT_STORES_B

Catalog Authoring, Shopping

ICX_CAT_SHOP_STORES_B

ICX_CAT_STORES_TL

Catalog Authoring, Shopping

ICX_CAT_SHOP_STORES_TL

ICX_CAT_STORE_CATALOGS

Catalog Authoring, Shopping

ICX_CAT_STORE_CONTENTS

ICX_CAT_STORE_ORG_ASSIGNMENTS

Catalog Authoring, Shopping

ICX_CAT_SECURE_CONTENTS

ICX_CAT_UPLOAD_IT_DUMP

Catalog Authoring

ICX_CAT_UPLOAD_PRICE_DUMP

Catalog Authoring

POR_FAVORITE_LIST_HEADERS

Catalog Authoring, Shopping

ICX_CAT_FAV_LIST_HEADERS

POR_FAVORITE_LIST_LINES

Catalog Authoring, Shopping

ICX_CAT_FAV_LIST_LINES_TLP

New Views

View Name

Feature Area

ICX_CAT_AGREEMENT_ATTRS_V

Catalog Authoring, Shopping

ICX_CAT_CATEGORIES_V

Catalog Authoring, Shopping

ICX_CAT_CONTENT_ZONES_VL

Catalog Authoring, Shopping

ICX_CAT_LATEST_BATCH_JOBS_V

Catalog Authoring

ICX_CAT_PURCHASING_CAT_MAP_V

Catalog Authoring, Shopping

ICX_CAT_SHOPPING_CAT_MAP_V

Catalog Authoring, Shopping

ICX_CAT_SHOP_STORES_VL

Catalog Authoring, Shopping

ICX_CAT_STORE_CONTENTS_V

Catalog Authoring,

Monday 25 February 2013

Add item images in iProcurement catalogue

In iProcurement, items can be queried and selected for raising requisitions. Whenever a user queries for a catalog item, the item is displayed in the catalog search screen along with item details, like category, price, supplier, etc.

Oracle gives the option to display item images as well. There are 2 options,
  1. Enter items in a BPA for a supplier and attach image names
  2. Enter items in Quotation for a supplier and attach image names
Both options are demonstrated below along with the configuration steps and the usage.
 
Configuration steps

Set profile option
Responsibility: System Administrator
Navigation: Profile System
POR: Extract BPA/Quote Images = Yes


POR: Hosted Images Directory

Set the value to the directory where you will be storing the image files to be used by iProcurement catalogues. We shall use the directory, $OA_MEDIA/POR. Set the real path of this directory as the profile option value.

Important: The directory path will contain the URL extension of the image. For instance, after we drop the image in the $OA_MEDIA/POR directory on the server the actual directory path to the image file would be something like, /d06/appltest/testcomn/java/oracle/apps/media/POR/>. We can view the image from the front end as well if we type in the image URL as http://<<server name>> :<<port>>/OA_MEDIA/<<image file>>, in a web browser.

POR: Show Thumbnail Images = Yes


Unix

Create a directory, POR, under $OA_MEDIA to store the images as we will store the images in $OA_MEDIA/POR

Copy images to this directory. We shall use 1 image for the time being, dustbin.JPG.
Now enable DFF for PO Lines.
Navigation: Application > Flexfield > Descriptive > Segments
Query for Application = Purchasing and Title = PO Lines.

Create a new Context, Picture Upload.

Click on Segments button. Add an attribute, ATTRIBUTE13 for Picture Upload.

Go to the Segment details form,

Save and compile the DFF.
 
Add images using Blanket Purchase Agreement

Before the raising the BPA we shall check item CMS002 for which we shall add the image. Before the image is added the item looks like the following. Note the Supplier attached to the item.

Now we need to create a Blanket Purchase Agreement or BPA for item CMS002.

Check Global box.
Click on PO Line DFF. Select Context, “Picture Upload“.

Enter the image file name.

Save the BPA. Send the BPA for approval by clicking on Approve button.

Press OK button. Ensure that BPA is approved before you go to the next step.
Let us check the data from the backend. Connect to Apps database.
Execute the query to see the PO Header
1SELECT po_header_id, segment1
2 FROM po_headers_all
3WHERE segment1 = '411005548'

Now check the PO Line
1SELECT item_description, attribute_category, attribute13
2  FROM po_lines_all
3 WHERE po_header_id = 108681

Navigate to Purchasing administrator responsibility and execute the following programs,
Catalog Data Extract – Classifications

Set parameters as
Log level = 5
Batch size = 5000

Catalog Data Extract – Items

Set parameters as
Log level = 5
Batch size = 5000

Rebuild Catalog Item InterMedia Index

This request does not take any parameters.

Connect to Apps database and check the iProcurement tables to see if the item is updated or not.
Check the Procurement item number ‘CMS002‘, i.e. the same item for which the BPA has been raised and approved.
1SELECT rt_item_id, supplier, internal_item_num
2  FROM icx_cat_items_b
3 WHERE internal_item_num = 'CMS002'

The 2nd line is important for us as we are looking at the item, CMS002, that will be supplied by AL GURG STAIONERY. Note the RT_ITEM_ID value.
1SELECT primary_category_name, supplier, internal_item_num, picture, thumbnail_image
2  FROM icx_cat_items_tlp
3 WHERE internal_item_num = 'CMS002'

Now check the item in iProcurement
The item, for the supplier for whom the BPA was raised, displays the image.
 
Add images using Quotations

In iProcurement, check the catalog items CMS005 and CMS006
Search for item CMS005

Search for item CMS006

In the database query for the items in iProcurement tables,
1SELECT rt_item_id, supplier, internal_item_num
2  FROM icx_cat_items_b
3 WHERE internal_item_num IN ('CMS005', 'CMS006')
1SELECT primary_category_name, supplier, internal_item_num, picture, thumbnail_image
2  FROM icx_cat_items_tlp
3 WHERE internal_item_num IN ('CMS005', 'CMS006')

Now go to Purchasing responsibility
Navigation: RFQ’s and Quotations > Quotations
Create a quotation
Supplier: Select a supplier
Type: Catalog Quotation
Effectivity: Enter a date range so that the current date falls within the range
Check the box, Approval Required.
Enter the items for which you want to enter images

Enter the details for item, CMS005.

Enter the DFF values for the item with image file name

Now enter the data for item CMS006

Enter the DFF

Click OK to close this window. On the main quotation form select each item line and click on Price Break button. Enter the Quantity for both items as shown below.

Save and close this window. Now the Approve button on the main quotation form is enabled.

Click on Approve. Enter the Type, Reason and Effective dates.

Click on OK button to approve the quotation. A popup is shown with the approval message.

Click OK. Now the quotation is approved. Change the Status field of the quotation header to Active.

Save the quotation.
Now execute the 3 programs in series,
  1. Catalog Data Extract – Classifications
  2. Catalog Data Extract – Items
  3. Rebuild Catalog Item InterMedia Index
Check the log of the program, Catalog Data Extract – Items.
001+-----------------------------
002| Starting concurrent program execution...
003+-----------------------------
004 
005Arguments
006------------
0072
0082500
009------------
010 
011+---------------------------------------------------------------------------+
012Start of log messages from FND_FILE
013+---------------------------------------------------------------------------+
014Commented out openLog
01504/08/12 15:30:07:3   BEGIN Extractor
01604/08/12 15:30:07:3   Start to extract ITEM: job number 13527542, system process id 11707
01704/08/12 15:30:07:3   Commit size: 2500, Debug level: 2
01804/08/12 15:30:07:3 Loader Values[Load Catalog Groups: N, Load Categories: Y, Load Template Headers: N, Load Contracts: Y, Load Item Master: Y, Load Template Lines: N, Load Internal Items: Y, Cleanup Flag: N, Catalog Groups Last Run Date: , Categories Last Run Date: 04/08/12 15:29:06, Template Headers Last Run Date: , Contracts Last Run Date: 04/08/12 15:26:09, Item Master Last Run Date: 04/08/12 15:26:09, Template Lines Last Run Date: , Vendor Last Run Date: 04/08/12 15:26:09, Internal Item Last Run Date: 04/08/12 15:26:09, Load One Time Items In all Langs: N]
01904/08/12 15:30:07:3   Extract item data
02004/08/12 15:30:07:3   Multi Org Flag: Y
02104/08/12 15:30:07:3   Profile option POR_EXTRACT_A13_AND_A14: Y
02204/08/12 15:30:07:3   Handling vendors with name changed.
02304/08/12 15:30:07:5   Database Version: 11.2
02404/08/12 15:30:09:91   Number of processed items in batch: 1, for vendor name change: 1
02504/08/12 15:30:09:91   Total Number of batches processed for vendor name change : 1
02604/08/12 15:30:09:91   Handling vendor sites with name changed.
02704/08/12 15:30:09:93   Update last run dates
02804/08/12 15:30:09:96   Cache hash size is 32768
02904/08/12 15:30:09:96   Start to process TEMPLATE
03004/08/12 15:30:09:97   Database Version: 11.2
03104/08/12 15:30:09:98   Processed records: 0
03204/08/12 15:30:09:98   Processed records: 0
03304/08/12 15:30:09:98   Processed records: 0
03404/08/12 15:30:09:98   Processed records: 0
03504/08/12 15:30:09:98 Total processed price rows: 0
03604/08/12 15:30:09:98   Cleanup items without price.
03704/08/12 15:30:09:98   Total deleted items without price : 0
03804/08/12 15:30:09:98   Set active flags.
03904/08/12 15:30:09:98 Total processed rows to set active flag: 0
04004/08/12 15:30:09:98   Update WHO.
04104/08/12 15:30:09:98 Total processed rows to update WHO columns: 0
04204/08/12 15:30:09:99   Database Version: 11.2
04304/08/12 15:30:09:0   Update last run dates
04404/08/12 15:30:09:0   Start to process CONTRACT
04504/08/12 15:30:09:2   Database Version: 11.2
04604/08/12 15:30:12:21   Database Version: 11.2
04704/08/12 15:30:12:23   Processed records: 2
04804/08/12 15:30:12:23   Processed records: 2
04904/08/12 15:30:12:23   Processed records: 2
05004/08/12 15:30:12:23   Processed records: 2
05104/08/12 15:30:12:23 Total processed price rows: 2
05204/08/12 15:30:12:23   Cleanup items without price.
05304/08/12 15:30:12:24   Total deleted items without price : 0
05404/08/12 15:30:12:24   Set active flags.
05504/08/12 15:30:12:24 Total processed rows to set active flag: 4
05604/08/12 15:30:12:24   Update WHO.
05704/08/12 15:30:12:24 Total processed rows to update WHO columns: 4
05804/08/12 15:30:12:26   Database Version: 11.2
05904/08/12 15:30:12:27   Start to process GLOBAL_AGREEMENT
06004/08/12 15:30:12:29   Database Version: 11.2
06104/08/12 15:30:12:30   Processed records: 0
06204/08/12 15:30:12:30   Processed records: 0
06304/08/12 15:30:12:30   Processed records: 0
06404/08/12 15:30:12:30   Processed records: 0
06504/08/12 15:30:12:30 Total processed price rows: 0
06604/08/12 15:30:12:30   Cleanup items without price.
06704/08/12 15:30:12:30   Total deleted items without price : 0
06804/08/12 15:30:12:30   Set active flags.
06904/08/12 15:30:12:30 Total processed rows to set active flag: 0
07004/08/12 15:30:12:30   Update WHO.
07104/08/12 15:30:12:30 Total processed rows to update WHO columns: 0
07204/08/12 15:30:12:31   Database Version: 11.2
07304/08/12 15:30:12:33   Update last run dates
07404/08/12 15:30:12:33   Start to process ASL
07504/08/12 15:30:12:34   Database Version: 11.2
07604/08/12 15:30:12:35   Processed records: 0
07704/08/12 15:30:12:35   Processed records: 0
07804/08/12 15:30:12:35   Processed records: 0
07904/08/12 15:30:12:35   Processed records: 0
08004/08/12 15:30:12:35 Total processed price rows: 0
08104/08/12 15:30:12:35   Cleanup items without price.
08204/08/12 15:30:12:35   Total deleted items without price : 0
08304/08/12 15:30:12:35   Set active flags.
08404/08/12 15:30:12:35 Total processed rows to set active flag: 0
08504/08/12 15:30:12:35   Update WHO.
08604/08/12 15:30:12:35 Total processed rows to update WHO columns: 0
08704/08/12 15:30:12:36   Database Version: 11.2
08804/08/12 15:30:12:37   Start to process ITEM
08904/08/12 15:30:12:39   Database Version: 11.2
09004/08/12 15:30:12:73   Processed records: 0
09104/08/12 15:30:12:73   Processed records: 0
09204/08/12 15:30:12:73   Processed records: 0
09304/08/12 15:30:12:73   Processed records: 0
09404/08/12 15:30:12:73 Total processed price rows: 0
09504/08/12 15:30:12:73   Cleanup items without price.
09604/08/12 15:30:12:73   Total deleted items without price : 0
09704/08/12 15:30:12:73   Set active flags.
09804/08/12 15:30:12:73 Total processed rows to set active flag: 0
09904/08/12 15:30:12:73   Update WHO.
10004/08/12 15:30:12:73 Total processed rows to update WHO columns: 0
10104/08/12 15:30:12:74   Database Version: 11.2
10204/08/12 15:30:12:76   Update last run dates
10304/08/12 15:30:12:76 All updated price rows processing done: 2
10404/08/12 15:30:12:80   END Extractor
10504/08/12 15:30:12:80   Populate interMedia index BEGIN
10604/08/12 15:30:42:22   Populate interMedia index END
107+---------------------------------------------------------------------------+
108End of log messages from FND_FILE
109+---------------------------------------------------------------------------+
110 
111+---------------------------------------------------------------------------+
112Executing request completion options...


Note that 2 records have been updated in the Catalog section of the log file. This means both the items have been updated with the supplier and the images. Let us query the iProcurement table,
1SELECT primary_category_name, supplier, internal_item_num, picture, thumbnail_image
2FROM icx_cat_items_tlp
3WHERE internal_item_num IN ('CMS005', 'CMS006')



We see that the item CMS005 and CMS006 are attached with the supplier and picture as well as the thumbnail image in the database. Let us check on the iProcurement catalog page now.
Search for CMS005

Again search for item CMS006
 Both images have been applied through a single quotation.

Important: Loading images using quotations is the better method, as a quotation does not need to be fulfilled and for a single supplier there can be multiple quotations. Whereas when we upload images through a BPA this will be a dummy BPA for which no Releases will be created but if genuine BPAs are raised for the same supplier in Oracle the dummy BPA will be displayed in reports and forms which will be confusing to users.

Modify the label on a Text box

I tried my hand at OAF personalization so I thought of giving a walkthrough for all. I have put in a series of articles on OAF personalization. In this article I have demonstrated how a field label is changed.

Step 1: To personalize we need to enable the personalization links for OAF pages. You can refer to this article to see how to do this.
We shall use Oracle iExpenses responsibility to demonstrate the personalization process.
Login to Oracle and navigate to the iExpense page. Note that the Approver field is editable. We shall personalize this page to make this field non editable.

You should see the Personalization links are enabled if Step 1 has been completed.

Personalization steps


We can rename field labels within OAF pages using personalization. This is done to make the field labels represent business process as closely as possible
In this example we shall change the label of the field, Approver to Line Manager.

Login to Oracle and go to the iExpense page

Now the field is shown as Approver.

Click on the link Personalize “General Information Page”. It will take you to Personalization Structure page.

Select Complete View and scroll down so that you can view all the elements.

Click on the personalize icon for Name: Message Lov Input: Approver.

Now we will be taken to the Personalization properties page.

On the properties, Prompt, note that Original definition is shown as Approver. We need to change this to Line Manager.

We shall change the Name of Line Manager on the Function level.
Note that personalization can be done on the following levels,
  • Function
  • Site
  • Organization (Operating Unit)
  • Responsibility

By making the change on the Function level it will be visible to all users across the instance.

Click on Apply. You will be taken to Personalization Structure.

Notice that the field Message Lov Input has changed to Line Manager. This value was Approver earlier. Click on Return to Application button on the bottom left.
Now you can see that the field prompt has been changed to Line Manager from Approver.

How to migrate personalization on OAF pages



In other articles I have mentioned how to personalize OAF pages. Now we shall discuss how personalization is migrated from one instance to another.
We shall migrate the personalization we had developed in previous articles. The migration steps are given below.
  
Step 1: Retrieve the page hierarchy

 

We shall identify where and which the personalization will be migrated.
Navigate to the OAF Page on which personalization is done. In our case the page is Create Expense report page.
The notable personalizations done on this page are,

  • The message on top of the field, Name, saying “Please note that iExpense responsibilities will not be available on 31-Jul-2012 to mark the occassion of the Great Day.

  • The prompt, Manager, has been changed to Line Manager.

Now click on About this Page link on the bottom left of the page.

Note the line underneath the page name, Create Expense Report: General Information. It is
/oracle/apps/ap/oie/entry/header/webui/GeneralInformationPG

The document name will be used in the next step as this is where the OAF page, GeneralInformationPG, is located and the location of the personalization on this page within the personalization repository.
Note the function name on the page, OIEENTRYFLOW.

Note:

The personalizations have been done at Function level. So we need to export the personalization done at this level
To check the personalizations on an OAF page you can execute a PL/SQL code in the database.
1BEGIN
2jdr_utils.listcustomizations ('/oracle/apps/ap/oie/entry/header/webui/GeneralInformationPG');
3END;

The output will look like the following,


Output:
01/oracle/apps/ap/oie/entry/header/webui/customizations/responsibility/54174/GeneralInformationPG
02/oracle/apps/ap/oie/entry/header/webui/customizations/site/0/GeneralInformationPG
03/oracle/apps/ap/oie/entry/header/webui/customizations/responsibility/50299/GeneralInformationPG
04/oracle/apps/ap/oie/entry/header/webui/customizations/responsibility/54173/GeneralInformationPG
05/oracle/oracle/apps/ap/oie/entry/header/webui/customizations/site/0/GeneralInformationPG
06/oracle/apps/ap/oie/entry/header/webui/customizations/responsibility/55925/GeneralInformationPG
07/oracle/apps/ap/oie/entry/header/webui/customizations/responsibility/55922/GeneralInformationPG
08/oracle/apps/ap/oie/entry/header/webui/customizations/responsibility/56191/GeneralInformationPG
09/oracle/apps/ap/oie/entry/header/webui/customizations/responsibility/56271/GeneralInformationPG
10/oracle/apps/ap/oie/entry/header/webui/customizations/function/OIEENTRYFLOW/GeneralInformationPG
11/oracle/apps/ap/oie/entry/header/webui/customizations/responsibility/56322/GeneralInformationPG
12..
13..
14..
15/oracle/apps/ap/oie/entry/header/webui/customizations/responsibility/55230/GeneralInformationPG
 
 

Step 2: Download the personalization
 

Now we shall download the personalization after locating it on the repository.
Go to Responsibility: Functional Administration
Navigation: Personalization > Import/Export > Personalization Repository

Then drill down as per the page hierarchy you had found in the earlier step.

We shall take the personalization done on the Function level by checking the “Select” box on the Page (The page name is GeneralInformationPG as shown in the previous step). You will notice the Last Updated date on the right hand side. The date also tells us whether we are looking at the correct OAF page or not.
Check the box for the OAF page whose personalization will be exported.

Now scroll down to the bottom.

Click on the button, Export to File System.


Now you will get a message telling you where the exported personalization file has been dropped. Note the message,
1Exported 1 document(s) to /d01/applprod/prodappl/XXCUST/11.5.0/bin/planphase successfully.
2/oracle/apps/ap/oie/entry/header/webui/customizations/function/OIEENTRYFLOW/GeneralInformationPG

Important:

The personalization file will be dropped on the path defined in the profile option, FND: Personalization Document Root Path. In this example the path is set to /d01/applprod/prodappl/XXCUST/11.5.0/bin/planphase and so the file is dropped there. The profile option is set as the following,

Hit Ctrl+E to open the value in the Editor.
  
Step 3: Locate the personalization file on the server

 

Now we shall see where the file has been generated.
Login to Unix and go to the path /d01/applprod/prodappl/XXCUST/11.5.0/bin/planphase (This path is given by Oracle in the previous step)
cd /d01/applprod/prodappl/XXCUST/11.5.0/bin/planphase


Now go the personalization path, /oracle/apps/ap/oie/entry/header/webui/customizations/site/0/.

cd oracle/apps/ap/oie/entry/header/webui/customizations/function/OIEENTRYFLOW/

Now check the contents of the directory.


As you can see a file, GeneralInformationPG.xml, exists in this directory. This xml file contains the personalization set.
  
Step 4: Review the exported file

 

We shall FTP the entire directory structure to a local PC and review the XML file. For this task I prefer to use a tool called Core FTP Lite as it is free and I can transfer an entire directory structure from and to the local pc.
After transferring the directory structure to your local pc,

Now open the file, GeneralInformationPG.xml in any text editor.
01<?xml version = '1.0' encoding = 'UTF-8'?>
02xmlns="http://xmlns.oracle.com/jrad" version="9.0.5.4.89_562" xml:lang="en-US" customizes="/oracle/apps/ap/oie/entry/header/webui/GeneralInformationPG" xmlns:oa="http://xmlns.oracle.com/oa">
03<modifications>
04<move element="OIEGeneralInformation" after="pbb"/>
05<insert before="OIEGeneralInformationMsgCLayout">
06<oa:formattedText id="Test_msg" adminCustomizable="true" cellNoWrapFormat="false" dataType="VARCHAR2" initSortSeq="none" queryable="false" rendered="true" scope="." sortState="no" styleClass="OraErrorNameText" text="Please note that iExpense responsibilities will not be available on 31-Jul-2012 to mark the occassion of the Great Day." totalValue="false" userCustomizable="false" xmlns:oa="http://xmlns.oracle.com/oa"/>
07</insert>
08<move element="ApproverId" after="OIEGeneralInformationMsgCLayout"/>
09<move element="AdditionalInformationHeader" after="ApproverId"/>
10<modify element="OverrideApprover" prompt="Line Manager"/>
11</modifications>
12</customization>

You can see the customizations in the xml file. You can also view the file contents from the database by executing the following PL/SQL commands.
1BEGIN
2jdr_utils.printdocument ('/oracle/apps/ap/oie/entry/header/webui/customizations/function/OIEENTRYFLOW/GeneralInformationPG');
3END;

On executing this anonymous block the output will be the same as the exported XML file.

  
 

Step 5: Move the exported personalization
 

We need to migrate the file, GeneralInformation.xml, along with the entire path, /oracle/apps/ap/oie/entry/header/webui/customizations/function/OIEENTRYFLOW/ to the target Oracle instance.

First let us locate the value of the profile option, FND: Personalization Document Root Path, on the target Oracle instance.

The path is /d03/appltest/testcomn/temp. This is the path where we need to drop the xml file with its directory structure.
Drop the entire structure, /oracle/apps/ap/oie/entry/header/webui/customizations/function/OIEENTRYFLOW/ GeneralInformationPG.xml in the directory, /d03/appltest/testcomn/temp.
  
 

Step 6: Check the OAF page in the target Oracle instance before importing personalization

Go to the iExpense page
This is the view of the page in the target instance before migrating the personalization
  
Step 7: Import the personalization in the target instance

 

On the target instance, log in to Functional Administrator responsibility.
Navigate to Personalization > Import/Export > Exported Personalizations
The page will look like the following if Step 4 has been completed properly.

Expand the nodes all the way down to the XML file

Select the checkbox for GeneralInformationPG.xml.

Click on Import from File System and you will get the following screen.

Once import is complete the screen will automatically change to the following
Now import is complete and we have to bounce the web server
  
Step 8: Bounce the web server in the target instance

 

We need to either bounce the web server (Apache) or we can clear the cache. For bouncing the web server you can check out this link and for clearing cache you check this article.
  
Step 9: Check the personalization on the target instance

 

Login to Oracle and navigate to the same page in the target instance. That is the Expense report creation page, where we had done our personalizations.
We can see the message in Red along with the new label “Line Manager”. This means that the personalization have been successfully migrated to the new instance.