Extensible Atrributes

Here is the query to list out all extensible attribute group names, attribute names and its data types.

SELECT   *
    FROM (SELECT --egoattributeeo.attr_id,
                 egoattributeeo.application_id,
                 egoattributeeo.attr_group_type,
                 egoattributeeo.attr_group_name,
                  egoattributeeo.attr_name,
                 egoattributeeo.attr_display_name,
                 decode(egoattributeeo.data_type_code,'C','CHAR','N','NUM','X','DATE') data_type_code,
                 egoattributeeo.DEFAULT_VALUE,
                 egoattributeeo.value_set_name,
                 egoattributeeo.maximum_size,
                  egoattributeeo.enabled_flag,
                  egoattributeeo.required_flag,
                 egoattributeeo.database_column,
                 egoattributeeo.read_only_flag
            FROM ego_attrs_v egoattributeeo, ego_fnd_dsc_flx_ctx_ext ext
           WHERE egoattributeeo.application_id = ext.application_id
             AND egoattributeeo.attr_group_type =
                                                ext.descriptive_flexfield_name
             AND egoattributeeo.attr_group_name =
                                             ext.descriptive_flex_context_code) qrslt
   WHERE (application_id = AND attr_group_type like '%'
        )
ORDER BY attr_group_type, attr_group_name



-- All the attribute groups --
SELECT FL_CTX_EXT.ATTR_GROUP_ID ATTR_GROUP_ID ,
FL_CTX.APPLICATION_ID APPLICATION_ID ,
FL_CTX.DESCRIPTIVE_FLEXFIELD_NAME ATTR_GROUP_TYPE ,
FL_CTX.DESCRIPTIVE_FLEX_CONTEXT_CODE ATTR_GROUP_NAME ,
TL.DESCRIPTIVE_FLEX_CONTEXT_NAME ATTR_GROUP_DISP_NAME ,
TL.DESCRIPTION DESCRIPTION ,
FL_CTX.ENABLED_FLAG ENABLED_CODE ,
L1.MEANING ENABLED_MEANING ,
FL_CTX_EXT.MULTI_ROW MULTI_ROW_CODE ,L2.MEANING MULTI_ROW_MEANING ,
FL_CTX_EXT.VIEW_PRIVILEGE_ID VIEW_PRIVILEGE ,
FUNC_VIEW_TL.USER_FUNCTION_NAME VIEW_PRIVILEGE_NAME ,
FL_CTX_EXT.EDIT_PRIVILEGE_ID EDIT_PRIVILEGE ,
FUNC_EDIT_TL.USER_FUNCTION_NAME EDIT_PRIVILEGE_NAME ,
FL_CTX_EXT.AGV_NAME AGV_NAME ,
FL_CTX_EXT.REGION_CODE REGION_CODE ,
FL_CTX_EXT.BUSINESS_EVENT_FLAG BUSINESS_EVENT_FLAG ,
L3.MEANING BUSINESS_EVENT_MEANING ,
'N' IS_EDITABLE ,
FL_TL.TITLE AGT_DISP_NAME ,
FL_CTX_EXT.PRE_BUSINESS_EVENT_FLAG PRE_BUSINESS_EVENT_FLAG ,
L4.MEANING PRE_BUSINESS_EVENT_MEANING ,'N' IS_DELETEABLE
FROM FND_DESCR_FLEX_CONTEXTS FL_CTX ,
EGO_FND_DSC_FLX_CTX_EXT FL_CTX_EXT ,
FND_DESCR_FLEX_CONTEXTS_TL TL ,
FND_DESCRIPTIVE_FLEXS_TL FL_TL ,
FND_LOOKUP_VALUES L1 ,
FND_LOOKUP_VALUES L2 ,
FND_LOOKUP_VALUES L3 ,
FND_LOOKUP_VALUES L4 ,
FND_FORM_FUNCTIONS_TL FUNC_VIEW_TL ,
FND_FORM_FUNCTIONS_TL FUNC_EDIT_TL
WHERE FL_CTX.APPLICATION_ID = FL_CTX_EXT.APPLICATION_ID AND
FL_CTX.APPLICATION_ID = TL.APPLICATION_ID AND
FL_CTX.DESCRIPTIVE_FLEXFIELD_NAME = FL_CTX_EXT.DESCRIPTIVE_FLEXFIELD_NAME AND
FL_CTX.DESCRIPTIVE_FLEXFIELD_NAME = TL.DESCRIPTIVE_FLEXFIELD_NAME AND
FL_CTX.DESCRIPTIVE_FLEXFIELD_NAME = FL_TL.DESCRIPTIVE_FLEXFIELD_NAME AND
FL_CTX.DESCRIPTIVE_FLEX_CONTEXT_CODE = FL_CTX_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE AND
FL_CTX.DESCRIPTIVE_FLEX_CONTEXT_CODE = TL.DESCRIPTIVE_FLEX_CONTEXT_CODE AND
TL.LANGUAGE = USERENV('LANG') AND FL_TL.LANGUAGE = USERENV('LANG') AND
L1.LOOKUP_TYPE = 'YES_NO' AND
L1.LOOKUP_CODE = FL_CTX.ENABLED_FLAG AND
L1.LANGUAGE = USERENV('LANG') AND
L1.VIEW_APPLICATION_ID = 0 AND
L2.LOOKUP_TYPE = 'YES_NO' AND
L2.LOOKUP_CODE = FL_CTX_EXT.MULTI_ROW AND
L2.LANGUAGE = USERENV('LANG') AND
L2.VIEW_APPLICATION_ID = 0 AND
L3.LOOKUP_TYPE(+) = 'YES_NO' AND
L3.LOOKUP_CODE (+)= FL_CTX_EXT.BUSINESS_EVENT_FLAG AND
L3.LANGUAGE (+)= USERENV('LANG') AND
L3.VIEW_APPLICATION_ID(+) = 0 AND
L4.LOOKUP_TYPE(+) = 'YES_NO' AND
L4.LOOKUP_CODE (+)= FL_CTX_EXT.PRE_BUSINESS_EVENT_FLAG AND
L4.LANGUAGE (+)= USERENV('LANG') AND
L4.VIEW_APPLICATION_ID(+) = 0 AND
FL_CTX_EXT.VIEW_PRIVILEGE_ID = FUNC_VIEW_TL.FUNCTION_ID(+) AND
FUNC_VIEW_TL.LANGUAGE (+)= userenv('LANG') AND
FL_CTX_EXT.EDIT_PRIVILEGE_ID = FUNC_EDIT_TL.FUNCTION_ID(+) AND
FUNC_EDIT_TL.LANGUAGE (+)= userenv('LANG') AND
FL_CTX_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE NOT IN ('ItemDetailImage', 'ItemDetailDesc')
--and FL_CTX.DESCRIPTIVE_FLEX_CONTEXT_CODE = 'PIM_CLF'

When updating Supplier Address, oracle.apps.ap.supplier.event was not raised

Problem: Business event "oracle.apps.ap.supplier.event" may not be raised when you updating Supplier Address at site level.

Solution:  You need to check the checkbox 'Update to all sites using this address'.

This business event "oracle.apps.ap.supplier.event" will be raised when you do following actions on suppliers/vendors
- Create Vendor
- Update Vendor
- Create Vendor Site
- Update Vendor Site
- Create Vendor Contact
- Update Vendor Contact
- Update Address Assignments

Critical Tasks in Project Management - typically ERP Implementation

Hi All,

I have put this list together from my past and previous experience and exposure as Project Lead and Manager. Many of these may or may not be applicable depending Project type, Rollout or upgrade, Implemetation. But ideally these points mentioned below are usually missed and or not under consideration until the situation arises. But project management practise says that one should always be prepared for the worst even before it shows up at the door step. I have seen on several occasions when we as a team had to go back and review the strategy to mitigate the risk of missing the timeline

Project plan should include the below activities

1 Check for prerequisite version of 11i needed for R12 upgrade

2 DB upgrade for supporting/as needed to 10g or 11g

3 Server capacity ( RAM, HDD) increase as needed to support 10g or 11g accordingly

4 Necessary pre-requisite patches to be applied for R12 as given for the corresponding version and research on the new patches released for that version and modules related.

5 Data Conversion/Migration planning

6 Instance planning and availability plan

7 Requirement gathering

8 Requirement signoff

9 Module Related Patches Verification

10 Cut Over Activities

11 Seeded Functional Setup steps.

12 Custom components functional design documents

13 Data Conversion/Migration Functional Design

14 Customization Technical design documents

15 Data Conversion/Migration Technical Design

16 Code Build

17 Unit Testing

18 System Integration Testing

19 User acceptance testing

20 Regression Testing

21 Volume Testing

22 Pre Production activities

23 Cut Over Activities

To top it all these should have bookmarks in the Risk Management document. Ideally each step can be flagged off red depending on the stage or the current status of the project affairs.

Finding Guarantors Case Folder for a Parent case folder

Below is the query to find Guarantors/Child case folder for a Parent/Dealer case folder along credit analyst assigned to it.



select child_cf.case_folder_number child_case_folder, parent_cf.case_folder_number  parent_case_folder,  res.source_name
from
ar_cmgt_credit_requests re,
ar_cmgt_case_folders child_cf,
ar_cmgt_case_folders parent_cf,
jtf_rs_resource_extns res
where
parent_credit_request_id = parent_cf.credit_request_id
--and parent_cf.case_folder_number = '14225'
and res.source_name = 'Credit Analyst Name Here'
and parent_cf.review_type is not null
and res.resource_id = parent_cf.credit_analyst_id
and child_cf.credit_request_id = re.credit_request_id
and child_cf.review_type is not null
order by 2 desc

Finding Credit Analyst Name in Oracle Credit Management

-- To find Credit analyst name for the given case folder name. ------

select source_name credit_analyst_ name, source_job_title Job_Title, case_folder_number, usr.user_name User, usr.description
from jtf_rs_resource_extns res ,
ar_cmgt_case_folders cf,
fnd_user usr
where
res.resource_id = cf.credit_analyst_id
and case_folder_number = '' 
and res.user_id = usr.user_id


  -- ====== to find out credit analyst  assigned to Rule Name -----

select rule_name, result_value , usr.user_name credit_analyst_name , res.resource_id
from fun_rule_details  fun, jtf_rs_resource_extns res, fnd_user usr
where rule_name = '' and
fun.result_value = res.resource_id
and res.user_id = usr.user_id

How to fix - Credit usage rule has not been setup

Credit usage rule has not been setup
When creating credit applications, Guarantor or child workflow may throw an error message - Credit usage rule has not been setup.

Reason could be - Credit classification for the Guarantor might not be setup properly.
Following query would help you to find out list of potential guarantors which would cause an issue while creating credit application.

This query list down the information such as Guarantor Name, Dealer Name and the Customer account profile id and object version number.
--
select p.party_type , p.party_number, p.party_name guarantor_name, p.party_id, hrel.object_id, hrel.object_table_name, hrel.object_type,
orgp.party_name dealer_name, orgp.party_number delaer_number,hzp.cust_account_profile_id, hzp.object_version_number,
p.creation_date, p.last_update_date, p.last_updated_by,ppf.full_name user_
from hz_parties p, hz_relationships hrel, hz_parties orgp,  hz_customer_profiles hzp, per_people_f ppf, fnd_user usr
where p.party_name  in (select
party_name
from hz_parties hzp where party_id in (select party_id from hz_customer_profiles hz,hz_cust_profile_classes cp
where cp.name = 'DEFAULT' and cp.profile_class_id = hz.profile_class_id and hz.cust_account_id = -1 )
and hzp.party_type = 'PERSON'    )
and hrel.subject_id = p.party_id
and orgp.party_id = hrel.object_id
and hzp.party_id = p.party_id
and hzp.cust_account_id = -1
and hrel.object_table_name = 'HZ_PARTIES'
and p.party_type = 'PERSON'
and p.last_updated_by = usr.user_id
and ppf.person_id = usr.employee_id

----

Execute following API to set credit classification correctly.

declare
p_customer_profile_rec_type
hz_customer_profile_v2pub.customer_profile_rec_type;
p_cust_account_profile_id number;
p_object_version_number number;
x_return_status varchar2(2000);
x_msg_count number;
x_msg_data varchar2(2000);
begin

fnd_client_info.set_org_context('&Org_Id');
p_customer_profile_rec_type.cust_account_profile_id := '&Cust_Account_Profile_Id';
p_customer_profile_rec_type.profile_class_id := '&New_profile_class_id';
p_object_version_number := '&object_version_number';

hz_customer_profile_v2pub.update_customer_profile( 'T',p_customer_profile_rec_type, p_object_version_number, x_return_status, x_msg_count, x_msg_data);

dbms_output.put_line('x_return_status = '||substr(x_return_status,1,255));
dbms_output.put_line('Object Version Number = '||to_char(p_object_version_number));
dbms_output.put_line('profile_class_id = '||p_customer_profile_rec_type.profile_class_id);
dbms_output.put_line('x_msg_count = '||to_char(x_msg_count));
dbms_output.put_line('x_msg_data = '|| to_char (x_msg_data,1,255));

if x_msg_count >1 then
for i in 1..x_msg_count loop
dbms_output.put_line(i||'.'||substr(fnd_msg_pub.get(p_encoded=> fnd_api.g_false ), 1, 255)); end loop; end if; end;

commit ;

After executing this, you may retest credit application process again and verify the results.
Hope this helps...

OCM - Dealer Names and Its Guarantor Names

select p.party_type , p.party_number, p.party_name guarantor_name, p.party_id, hrel.object_id, hrel.object_table_name, hrel.object_type,
orgp.party_name dealer_name, orgp.party_number delaer_number
from hz_parties p, hz_relationships hrel, hz_parties orgp
where p.party_name  in (select
party_name
from hz_parties hzp where party_id in (select party_id from HZ_CUSTOMER_PROFILES hz,hz_cust_profile_classes cp
where cp.name = 'DEFAULT' and cp.profile_class_id = hz.profile_class_id and hz.cust_account_id = -1 )
and hzp.party_type = 'PERSON'    )
and hrel.subject_id = p.party_id
and orgp.party_id = hrel.object_id