How to add Compare feature in Oracle Endeca Information Discovery UI or Dashboard

This is short video presentation about how to How to add Compare feature in Oracle Endeca Information Discovery UI or Dashboard

Analytical UI/Dashboard for Oracle EBS with Oracle Endeca Information Discovery

This is short post supported with you tube video about Analytical UI/Dashboard for Oracle EBS with Oracle Endeca Information Discovery .

This is about Oracle EBS Order to Cash  Analytical Dashboard /UI  desgined and developed with Oracle Endeca Information Discovery. With Oracle Endeca we can designed  powerful Analytical dashboard that gives business user , all they need and freedom to explore the data in interactive manner , and helps organization to take decisions(based on data).

oracle information discovery adapts to changes automatically it doesn't matter where the information resides or if it structured or unstructured. Another plus about endeca is you can load new data into oracle interactive information discovery extremely quickly. 

oracle endeca information discovery has a database specifically designed with a highly flexible data model it supports virtually any kind of data from any source. We don't even need to define a unified schema before loading data because oracle interactive information discovery derives a model directly from the data .Every record has its own schema based on its own generated attributes.

Feel free to contact me at sgupta@bizinsightinc.com or leave comments at my blog eoracleapps.blogspot.com

Custom Status for Work Order in Oracle eAM

In R12i Oracle Enterprise management ( eAM) Oracle has Introduced functionality to create custom Status for the Work Order.

User Can Define Custom Work Order Status in "Define Work Order Statuses" , Once define these custom work order status has to link to System status.Once Linked these custom work order status act like the linked system status ( only difference is now we have a customized name for the status , though functionality will be same as linked system status).

Few Concepts in Oracle eAM Preventive Maintenance

Few Concepts in Oracle eAM Preventive Maintenance

You are not setup as a worker , while trying to access PO Related Form.

You are not setup as a worker , while trying to access PO Related Form from Oracle Enterprise Asset Management.

Please note that this error is similar to 11i error "You are not setup as a employee". With the addition of expanded Contingent Worker functionality available in R12, the error message to validate employee access to the forms has been changed from ' You are not setup as a employee' to ' You must be a worker to access this page'.

To Fix the Issue , associate employee with the users who are getting this error message.

Navigate to Oracle Sysadmin responsibility 
Security > user > 

Query for the user and assign employee to the user by populating the field "Person" as shown below

API to Copy an existing Workorder in Oracle eAM

Below is sample code to copy an existing WorkOrder in Oracle Enterprise Asset management ( eAM) , by using public API  EAM_PROCESS_WO_PUB.COPY_WORKORDER


Declare

X_status VARCHAR2(4000);
X_Msg_Cnt Number;
X_Msg_Data Varchar2(4000);
L_Eam_Wo_Rec_Out      Eam_Process_Wo_Pub.Eam_Wo_Rec_Type;
L_Eam_Op_Tbl          Eam_Process_Wo_Pub.Eam_Op_Tbl_Type;
L_Eam_Op_Network_Tbl  Eam_Process_Wo_Pub.Eam_Op_Network_Tbl_Type;
L_Eam_Res_Tbl         Eam_Process_Wo_Pub.Eam_Res_Tbl_Type;
L_Eam_Res_Inst_Tbl    Eam_Process_Wo_Pub.Eam_Res_Inst_Tbl_Type;
L_Eam_Res_Usage_Tbl   Eam_Process_Wo_Pub.Eam_Res_Usage_Tbl_Type;
L_Eam_Mat_Req_Tbl     Eam_Process_Wo_Pub.Eam_Mat_Req_Tbl_Type;
L_Eam_Direct_Items_Tbl    Eam_Process_Wo_Pub.Eam_Direct_Items_Tbl_Type;
L_Return_Status   Varchar2(100);
L_Msg_Count Number;
x_return_status   varchar2(100);

       
Begin
EAM_PROCESS_WO_PUB.COPY_WORKORDER (p_bo_identifier =>'EAM'
,p_api_version_number => 1.0
,p_init_msg_list => 'TRUE'
,P_Commit => 'N'
,P_Wip_Entity_Id => 1863991--p_wip_entity_id
,p_organization_id => 804--p_organization_id
,x_eam_wo_rec => L_EAM_WO_REC_OUT
,x_eam_op_tbl => L_EAM_OP_TBL
,x_eam_op_network_tbl => L_EAM_OP_NETWORK_TBL
,x_eam_res_tbl => L_EAM_RES_TBL
,x_eam_res_inst_tbl => L_EAM_RES_INST_TBL
,x_eam_res_usage_tbl => L_EAM_RES_USAGE_TBL
,x_eam_mat_req_tbl => L_EAM_MAT_REQ_TBL
,x_eam_direct_items_tbl => L_EAM_DIRECT_ITEMS_TBL
,x_return_status => L_RETURN_STATUS
,X_Msg_Count => L_Msg_Count
);
  If X_Msg_Cnt > 0 Then
    DBMS_OUTPUT.PUT_LINE('Error Occurred in WO Creation : '||SQLERRM||' : '||SQLCODE);
  End If;
  Dbms_Output.Put_Line('x_return_status.......  '||X_Return_Status);
End;

R12 - Profiles to Set the Credit Card authorization in Oracle iStore /Order Management

In R12i Oracle has introduced new profile option to trigger Credit Card authorization in iStore.

IBE:Perform Payment authorization in iStore  -
When set to Yes , Oracle application will perform Credit Card authorization in Oracle iStore.

But if requirement is to do Credit Card authorization in Oracle Order Management , then set
IBE:Perform Payment authorization in iStore  - No
ASO: Default Order Status = Booked
ASO: Credit Card Authorization = No
with the above set up system will perform credit card authorization in Order Management.

Please note that when ever System Book the Order in Order Management , it will perform the credit card authorization.

Note - No Credit card Authorization will be performed with below profile option Setup
ASO: Default Order Status =Entered
ASO: Credit Card Authorization =Yes

Query to Check if a particular e E-Business Suite Module Installed in your Instance or Not

Query to Check if a particular e E-Business Suite Module Installed in your Instance or Not 

SELECT substr(a.application_name,1,30) MODULE,
a.application_id, substr(c.application_short_name,1,7) SN,
DECODE(b.status,'I','Installed','S','Shared','N','NOT Installed','UNKNOWN') STATUS
FROM fnd_application_tl a, fnd_product_installations b, fnd_application c
WHERE a.application_id = b.application_id
AND a.application_id = c.application_id
AND c.application_short_name like '%ONT%';

Query to Check  Ebusiness Suite version , latest patch Set , Boxname for a particulra_module
SELECT i.instance_name, i.host_name, f.release_name release,
fpi.patch_level, fpi.status, i.version db_version
FROM v$instance i, fnd_product_groups f,
applsys.fnd_product_installations fpi
WHERE UPPER(SUBSTR(i.instance_name,1,4)) =
UPPER(SUBSTR(f.applications_system_name,1,4))
AND fpi.patch_level LIKE '%ONT%';

Query to get Resource and Operations Details associated with Work Order (in Oracle eAM)

Below is simple Query to return Resource and Operations Details associated with Work Order ( in Oracle eAM)
It has 2 parameters

1. Work order ID
2. Organization ID

SELECT wo.operation_seq_num ,
          wo.description,
          br.resource_code,
          NVL(wori.serial_number,ppf.full_name) Resource_details  ,
        TO_CHAR(wo.first_unit_start_date,'DD-MON-YYYY') first_unit_start_date,
        TO_CHAR(wo.last_unit_completion_date,'DD-MON-YYYY') last_unit_completion_date,
        NVL(wo.operation_completed,'N') operation_complete
    FROM wip_operations wo,
      wip_operation_resources wor,
      bom_resources br,
      wip_op_resource_instances wori,
      bom_resource_employees bre,
      PER_ALL_PEOPLE_F ppf
    WHERE wo.wip_entity_id             =wor.wip_entity_id(+)
    AND wo.operation_seq_num         =wor.operation_seq_num(+)
    AND wor.resource_id              =br.resource_id(+)

    AND wo.wip_entity_id             =wori.wip_entity_id(+)
    AND wo.operation_seq_num         =wori.operation_seq_num(+)
    AND wori.instance_id             = bre.instance_id(+)

    AND bre.person_id                = ppf.person_id(+)

    AND wo.wip_entity_id             = p_wip_entity_id

    AND wo.organization_id           = p_org_id
    Order By operation_seq_num ;

Run to failure and Preventive Maintenance in Oracle eAM

2 new concept I learn about Preventive Maintenance

1. Run to Failure
2. Generate Next WO

Run to Failure  -  When Run to Failure selected for an Asset's Preventive Maintenance definitions,  system will not generate any PM work order till asset  fail.Once Asset fails,  system will let you create a repair WO for Asset.

Generate Next WO - 

If generate WO set to YES , Preventive Maintenance Scheduler will not Create WO till previous/existing work order got completed.

If generate WO set to YES , Preventive Maintenance Scheduler will create WO even if their exists a open workorder.

Quick Fix - Inventory Transaction for Asset not appears in IB

Many a time we notice that Create and Update to the Asset  belong to Asset Group where Install base Tracking Flag = Yes appears in Install base , But any type of transactions like

1. Misc Receipt
2. Misc Issue
3. Sub Inventory transfer etc

not appears in Install base.
Solution - Please Make Sure that Transactable flag for the Asset Group  = Yes ( Checked).

Oracle eAM records Created in Inventory Master Vs Oracle Install Base

In this post I have lists in what modules Oracle Application create records for

1. Asset Group
2. Asset Number
3. Serialized rebuildable Items
4. Asset Activity 

Oracle Inv


                                                       Records Created in  
Category Name           Inventory Master                         Install Base

Asset Group                        YES                                       NO

Asset Number                      NO                                       YES

Serialized Rebuildable           YES                                      YES
       Items

Asset Activity                       YES                                      YES

eAM Integration with Oracle Install base

eAM Integration with Oracle Install base is another new feature of R12. 
With Integration with Install base (IB) , IB can now track the eAM assets as well as store the eAM asset transactions.

Key Setup Required to track eAM assets in  IB

• Installed Base Trackable Flag  ( Service Tab in Item master UI)
•  Transactable Flag for the Asset Group ( Inventory tab in Item master UI)

Install base Trackable flag is mandatory for Asset Group and rebuildable items  ( with out that system will not track the eAM Asset in IB).

• Note – Once set this flag is not Updatable if you have On hand quantity for the asset and transaction exists for the asset.

Translatable Flag  - This flag is mandatory if you want to track the eAM Asset Transaction details in IB.

Sample Test Case

1. Create Asset Group in Oracle Inventory ( Master Org).
2. Check  the “Track in Install base” Check box  Inder Service tab.
3. Create Asset
4. Once Asset got created in eAM , system will create record in IB too with Status = Created

Description Items in Oracle Enterprise Asset management (eAM)

Description items in Oracle eAM are the Items that are -

1. Non Stockable 
2. Not defined in Inventory 
3. No tracking required 
4. It is kind of Service item that is not Standard

These are the items that are only need on the Sport basis ( once a while) .

Example - 
One Example of description item  is " BANNER" that says " Asset on Sales on Cyber Monday"  during November , but same description is not appropriate in the Month of December , In December BANNER might have description " Asset on Sales on Xmas".

1 Step / 2 Step Material Issue @ MTL_MATERIAL_TRANSACTION table in Oracle eAM

How to Identity Oracle enterprise Asset management 9 eAM) 1 Step material issue and 2 material Issue in MTL_MATERIAL_TRANSACTION table.

Answer -
For Two Step Material Issue ( Allocation + Issue) , we have move_order_line_id populated in MTL_MATERIAL_TRANSACTION table.

For 1 Step Material Issue , move_order_line_is in MTL_MATERIAL_TRANSACTION table remains NULL.

Do Move Order Transaction Created /Allowed in 2 Step Material issue ???

Question - Do Move Order Transaction Created /Allowed in 2 Step Material issue ???

Answer - Yes Move Order transactions are allowed in 2 Step Material issue , but you can't use Inventory Move Order to progress these Move Order transaction .eAM Move Order transaction should be transacted through material issue Onle ( In Self Service  > Stores(tab) > Material issue.

Two Step Material Issue Vs One Step material issue in Oracle eAM

In TWO Step Material Issue - System will Perform

1. Material Allocation and then 
2. Material Issue 

Material Allocation will be performed as soon as eAM Work Order got Released and after that user need to perform the material issue for Self Service Page .. Store >Material issue > Issue

Note - For Allocation to be triggered at the Time of Work Order Release , please make sure that Auto Allocate Flag is ON at the Material Order Line as well as Work Order Header ( In WO header it got defaulted from Parameter - Enable Material issue request ) 

On the other hand In One Step Material Issue we don't perform Allocation, user can directly issue the Material.

Another Advantage of One Step Material issue is , it let you do Over Issue.

For How to Spot One Step and Two Step Material issue in MTL_MATERIAL_TRSNACTION table , please tune in to my Next blog post.

How to identity eAM record in MTL_MATERIAL_TRANSACTION table

Question - How to identity eAM record in MTL_MATERIAL_TRANSACTION table

For eAM records we will have following data in   MTL_MATERIAL_TRANSACTION

• TRANSACTION_SOURCE_TYPE_ID = 5 ( please note that even for WIP records it is 5).
• TRANSACTION_SOURCE_ID = WIP_ENTITY_ID ( Work Order ID).