Oracle R12 Field Operation Track (CRM) Information

How to Activate Custom table for PPC in oracle
------------------------------------------------

Enable Profile ASG_CUSTOM_FLAG

Run Installation Manager for R12

Installation Manager

-----------------------

java -Xms32m -Xmx1536m -DJTFDBCFILE=/u000/oracle/DEV/inst/apps/DEV_rpc5009/appl/fnd/12.0.0/secure/DEV.dbc -DAFLOG_ENABLED=TRUE -DAFLOG_LEVEL=STATEMENT oracle.apps.asg.setup.InstallationMgr ibm5001 1525 DEV APPS apps MOBILEADMIN mobiledev JTM_TOP jtm/JTM.ini html/download/jtm.zip

java -Xms32m -Xmx1536m -DJTFDBCFILE=/u000/oracle/DEV/inst/apps/DEV_rpc5009/appl/fnd/12.0.0/secure/DEV.dbc -DAFLOG_ENABLED=TRUE -DAFLOG_LEVEL=STATEMENT oracle.apps.asg.setup.InstallationMgr ibm5001 1525 DEV APPS apps MOBILEADMIN mobiledev CSM_TOP CSW.ini html/download/CSW.zip

java -Xms32m -Xmx1536m -DJTFDBCFILE=/u000/oracle/DEV/inst/apps/DEV_rpc5009/appl/fnd/12.0.0/secure/DEV.dbc -DAFLOG_ENABLED=TRUE -DAFLOG_LEVEL=STATEMENT oracle.apps.asg.setup.InstallationMgr ibm5001 1525 DEV APPS apps MOBILEADMIN mobiledev CSM_TOP CSML.ini html/download/CSML.zip

java -Xms32m -Xmx1536m -DJTFDBCFILE=/u000/oracle/DEV/inst/apps/DEV_rpc5009/appl/fnd/12.0.0/secure/DEV.dbc -DAFLOG_ENABLED=TRUE -DAFLOG_LEVEL=STATEMENT oracle.apps.asg.setup.InstallationMgr ibm5001 1525 DEV APPS apps MOBILEADMIN mobiledev CSM_TOP CSW_2000_ARM.ini html/download/CSW.zip

java -Xms32m -Xmx1536m -DJTFDBCFILE=/u000/oracle/DEV/inst/apps/DEV_rpc5009/appl/fnd/12.0.0/secure/DEV.dbc -DAFLOG_ENABLED=TRUE -DAFLOG_LEVEL=STATEMENT oracle.apps.asg.setup.InstallationMgr ibm5001 1525 DEV APPS apps MOBILEADMIN mobiledev CSM_TOP CSW_2003_XSCALE.ini html/download/CSW.zip

Steps to enable downloading of data into custom tables- olite

The example uses the custom table CSM_CUSTOM_4_B. For this,

The PUBLICATION is : SERVICEP_CUSTOM

The PUBLICATION ITEM is : CSM_CUSTOM_4

The ACC TABLE is : CSM.CSM_CUSTOM_4_ACC

The BASE TABLE: CSM.CSM_CUSTOM_4_B

The INQ TABLE is : CSM_CUSTOM_4_INQ (used in the upward sync)

The PUBLICATION ITEM VIEW is : APPS.CSM_CUSTOM_4_V

1. First determine the custom base table/view that needs to be downloaded on to the PPC. For this example the table/view is MTL_SYSTEM_ITEMS_B1_KFV. Please note that the table/view owner must be APPS- determined using query "select * from dba_objects where object_name = 'MTL_SYSTEM_ITEMS_B1_KFV'"

2. Determine the primary key fields of this table. I have assumed them to be INVENTORY_ITEM_ID, ORGANIZATION_ID.

3. Determine the additional fields (other than primary) that need to be downloaded on to PPC. I have assumed it to be SHIPPABLE_ITEM_FLAG.

4. Identify a Custom table from CSM_CUSTOM_1, CSM_CUSTOM_2, CSM_CUSTOM_3, CSM_CUSTOM_4, CSM_CUSTOM_5, CSM_CUSTOM_6 that meets your criteria. Some of these have a single field as primary key and some have 2 fields set as primary key. I have assumed CSM_CUSTOM_4 since it contains 2 primary key fields.

5. Create the publication Item view. The publication item view is downloaded onto the olite (on PPC) custom table. This view is created/modified using the API ASG_CUSTOM_PUB.customize_pub_item. (The API was throwing error. I have created a new Package asg_custom_pvt_hc in order to make this work). The input parameters to this API are:

NCR_FO_ASG_CUSTOM_PUB

ASG_CUSTOM_PUB.customize_pub_item(

p_api_version_number =>1.0 ,

p_init_msg_list =>FND_API.G_TRUE, (to get output error message)

p_pub_item_name =>'CSM_CUSTOM_4', (specify the custom table name in which this would be downloaded on to PPC)

p_base_table_name =>'MTL_SYSTEM_ITEMS_B1_KFV', (specify the base table/view name)

p_primary_key_columns =>'INVENTORY_ITEM_ID, ORGANIZATION_ID', (specify the primary key fields of the base table/view, These would be part of publication item)

p_data_columns =>'SHIPPABLE_ITEM_FLAG', (specify the additional fields of the base table/view. These would be part of publication item)

p_additional_filter =>null, (any query criteria could be specified)

x_msg_count =>x_msg_count,

x_return_status =>x_return_status,

x_error_message =>x_error_message

Once this API is executed, the Publication Item view query changes from

CREATE OR REPLACE FORCE VIEW csm_custom_4_v (

access_id, attribute1, attribute2, attribute3, attribute4,

attribute5, attribute6, attribute7, attribute8, attribute9,

attribute10, attribute11, attribute12, attribute13, attribute14,

attribute15, attribute16, attribute17, attribute18, attribute19,

attribute20, attribute21, attribute22, attribute23, attribute24,

attribute25, attribute26, attribute27, attribute28, attribute29,

attribute30 )

AS

SELECT ACC.ACCESS_ID, b.attribute1, b.attribute2, b.attribute3, b.attribute4, b.attribute5, b.attribute6, b.attribute7, b.attribute8, b.attribute9, b.attribute10, b.attribute11, b.attribute12, b.attribute13, b.attribute14, b.attribute15, b.attribute16, b.attribute17, b.attribute18, b.attribute19, b.attribute20, b.attribute21, b.attribute22, b.attribute23, b.attribute24, b.attribute25, b.attribute26, b.attribute27, b.attribute28, b.attribute29, b.attribute30

FROM csm.csm_custom_4_acc acc, csm.csm_custom_4_b b

WHERE ACC.USER_ID = ASG_BASE.GET_USER_ID

AND ACC.ATTRIBUTE1 = B.ATTRIBUTE1

AND ACC.ATTRIBUTE2 = B.ATTRIBUTE2

To

CREATE OR REPLACE FORCE VIEW APPS.CSM_CUSTOM_4_V

(ACCESS_ID, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4,

ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9,

ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14,

ATTRIBUTE15, ATTRIBUTE16, ATTRIBUTE17, ATTRIBUTE18, ATTRIBUTE19,

ATTRIBUTE20, ATTRIBUTE21, ATTRIBUTE22, ATTRIBUTE23, ATTRIBUTE24,

ATTRIBUTE25, ATTRIBUTE26, ATTRIBUTE27, ATTRIBUTE28, ATTRIBUTE29,

ATTRIBUTE30)

AS

select acc.access_id , to_char(MTL_SYSTEM_ITEMS_B1_KFV.INVENTORY_ITEM_ID) attribute1, to_char(MTL_SYSTEM_ITEMS_B1_KFV.ORGANIZATION_ID) attribute2 , MTL_SYSTEM_ITEMS_B1_KFV.SHIPPABLE_ITEM_FLAG attribute3, NULL ATTRIBUTE4, NULL ATTRIBUTE5, NULL ATTRIBUTE6, NULL ATTRIBUTE7, NULL ATTRIBUTE8, NULL ATTRIBUTE9, NULL ATTRIBUTE10, NULL ATTRIBUTE11, NULL ATTRIBUTE12, NULL ATTRIBUTE13, NULL ATTRIBUTE14, NULL ATTRIBUTE15, NULL ATTRIBUTE16, NULL ATTRIBUTE17, NULL ATTRIBUTE18, NULL ATTRIBUTE19, NULL ATTRIBUTE20, NULL ATTRIBUTE21, NULL ATTRIBUTE22, NULL ATTRIBUTE23, NULL ATTRIBUTE24, NULL ATTRIBUTE25, NULL ATTRIBUTE26, NULL ATTRIBUTE27, NULL ATTRIBUTE28, NULL ATTRIBUTE29, NULL ATTRIBUTE30

from MTL_SYSTEM_ITEMS_B1_KFV, CSM_CUSTOM_4_ACC acc

where acc.user_id = asg_base.get_user_id

and to_char(MTL_SYSTEM_ITEMS_B1_KFV.INVENTORY_ITEM_ID) = acc.ATTRIBUTE1

and to_char(MTL_SYSTEM_ITEMS_B1_KFV.ORGANIZATION_ID) = acc.ATTRIBUTE2

6. In order to download the data onto the PPC, first the data needs to be entered into the Access Tables and then mark dirty API needs to be called.

To insert into access tables,

6.1 Determine the record's primary key fields that you want to download to PPC, in sample I used 88, 1222 (select INVENTORY_ITEM_ID, ORGANIZATION_ID, SHIPPABLE_ITEM_FLAG from MTL_SYSTEM_ITEMS_B1_KFV)

6.2 Determine the user_id to which this data should be downloaded using SELECT * FROM asg_user where user_id = 1222- (for US114H71)1226- US114H82

6.3 Determine the apps user that trigerred the change on APPS side- select * from fnd_user where user_id = 1159 (for HC185011)

6.4 Determine the last login - select * from FND_LOGINS where user_id = 1159 (login for HC185011)

6.5 insert record into access table-

insert into CSM_CUSTOM_4_ACC (access_id, ATTRIBUTE1, ATTRIBUTE2, user_id, counter, created_by, creation_date, last_updated_by, last_update_date, last_update_login) values (1, 2001, 88, 1222, 1, 1159, sysdate, 1159, sysdate, 1191116)

Access Id is the primary key field for this table.

6.6 Invoke API corresponding rto the data inserted into access table.

ASG_CUSTOM_PUB.mark_dirty (

p_api_version_number =>1.0,

p_init_msg_list =>FND_API.G_TRUE, (to get output error message)

p_pub_item =>'CSM_CUSTOM_4',

p_accessid =>1,

p_userid =>1222,

p_dml => 'I', (use I- Insert, D delete and U for update)

p_timestamp => SYSDATE,

x_return_status =>x_return_status

);

You would need to commit the data after this API runs. This inserts data into asg_system_dirty_queue to be sent to the PPC user.

7. Sync on the PPC to get the record on olite.