Order Management (OM) Product Categories Query EBS

Order Management (OM) PRODUCT CATEGORIES Query 

SELECT --MC.CONCATENATED_SEGMENTS  ,
MC.SEGMENT1 CAT_1 ,
MC.SEGMENT2 CAT_2 ,
MC.SEGMENT3 CAT_3,
MSI.SEGMENT1 PART# ,
CATEGORY_SET_NAME,
MP.ORGANIZATION_CODE ,
MIC.CREATION_DATE,
MIC.LAST_UPDATE_DATE,
MCS.CATEGORY_SET_ID||'~'||MC.CATEGORY_ID INV_CAT2_ID,
MSI.INVENTORY_ITEM_ID||'~'||MSI.ORGANIZATION_ID,
MIC.LAST_UPDATE_DATE ITEM_UPDATE_DT,
MSI.LAST_UPDATE_DATE SYSTEMS_UPDATE_DT
FROM
apps.MTL_CATEGORIES_B MC,
apps.MTL_ITEM_CATEGORIES MIC,
apps.MTL_SYSTEM_ITEMS_B MSI ,
apps.MTL_CATEGORY_SETS MCS,
apps.MTL_PARAMETERS MP
WHERE MIC.CATEGORY_ID     = MC.CATEGORY_ID
AND MIC.ORGANIZATION_ID   = MSI.ORGANIZATION_ID
AND MIC.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MCS.CATEGORY_SET_ID   = MIC.CATEGORY_SET_ID
AND MSI.SEGMENT1 in ('6047901')
AND MIC.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND CATEGORY_SET_NAME   ='PRODUCT'
AND MP.ORGANIZATION_CODE  = '100'

Connecting two servers using SSH public keys.

Creating shake hand between servers using SSH public keys.

 

Use the below method to generate Public keys:

On source host server:

Goto to user directory

    cd ~

    mkdir .ssh

    chmod 700 .ssh

    cd .ssh

Execute the below command

 

/usr/local/bin/ssh-keygen -t rsa -b 1024(or) ssh-keygen -t rsa -b 1024

cat id_rsa.pub

 copy contents of id_rsa.pub (file)

On destination host server:

Goto to user directory 

    cd ~

    mkdir .ssh

    chmod 700 .ssh

    cd .ssh

    vi authorized_keys

Paste contents of id_rsa.pub  into authorized keys (to which ever connecting server)
 

chmod 600 authorized_keys --(don’t miss this permission, this should be and must be 600)

After completion of above steps execute below command from destination host:

ssh xyz.alpha.com

Till Next time..:)

How to Upgrade DAC from 10.1.3.4.1 to 11x when the BI Applications

How to Upgrade DAC from 10.1.3.4.1 to 11x when the BI Applications and Source System (OLTP) are upgraded. 

SCENARIO:

An environment is being upgraded such that the Oracle BI Applications, the Oracle DAC and the Source System are all moving to newer releases.  

For example, see an example upgrade below: 

Oracle DAC:                    From 10.1.3.4.1 to 11.1.1

Siebel (OLTP)                  From 8.0 to 8.1.1

Oracle BI Applications:     From 7.9.6.3 to 7.9.6.4

In the 10.1.3.4.1 DAC Repository, there is a custom source system container (for example,"My Custom Container") that is a copy of a pre-configured source system container (for example, "Siebel 8.0 Vertical") with changes applied.  

The aim is to upgrade the DAC to 10.1.3.4.1 and merge with the new "Siebel 8.1.1 Vertical" container that is provided with Oracle BI Applications 7.9.6.4

Upgrading, Comparing and Merging DAC Repositories"the Upgrade/Merge Wizard includes the following options for upgrading and merging DAC repositories:

---Refresh Base: Use this option to upgrade the DAC Repository when you are upgrading from an older release of Oracle BI Applications to a new release.

---Replace Base: Use this option to upgrade the DAC Repository when you are phasing out an older release of a transactional application and moving to a newer release, for example, phasing out Siebel 8.0 and moving to Siebel 8.1.1.

But The Best Solution:

Replace Base is the appropriate option to use whenever the version of the transactional application moves to a newer release.

Even Oracle BI Applications is also moving to a newer release, it is sufficient to use only the Replace Base option.

When using the Replace Base option, the following containers (at a minimum) should be present in the DAC working repository prior to invoking.

The Upgrade/Merge Wizard from the DAC Tools > DAC Repository Management menu:

a. The custom container from the DAC 7963  repository (for example, "My Custom Container")

b. The original base container from the DAC 7963 repository (for example, "Siebel 8.0 Vertical")

The DAC working repository is the DAC 7963 repository (with the custom and original base container) upgraded to the DAC 11x  format, which is done automatically the first time that the DAC 11x Client is used to connect and login to the DAC 10.1.3.4.1 repository. 

Steps For Replace Base Option:

During the Replace Base procedure for upgrading, the following containers should be specified at the appropriate steps:

DAC Tools > DAC Repository Management > Upgrade/Merge Wizard

In the "Import Source System Container" dialog box: 

Source System Container = Siebel 8.1.1 Vertical 

Where "Siebel 8.1.1 Vertical" is the new base container supplied with Oracle BI Applications 7.9.6.1 / DAC 10.1.3.4.1

 

In the "Create Difference Report" dialog box:

Existing Base Container = Siebel 8.0 Vertical

New Container = Siebel 8.1.1 Vertical

In the "Merge - Replace Base" dialog box:

New Container = Siebel 8.1.1 Vertical

Existing Base Container = Siebel 8.0 Vertical

When the merge process is complete, the following Containers would be visible:

"My Custom Container" (the custom container)

"Siebel 8.1.1 Vertical" (the new base container)

After the upgrade / merge process is complete, it is very important to reassemble all the subject areas and rebuild all the execution plans in the customized application to include any new changes in the object dependency etc.. 

Till Next Time.. :)