Thursday, April 28, 2016

Items getting created with same inventory item id for different items

1) query the Item in Master Org. See the Item name

2) Then scroll to the different Org ( Organization item) and open organization item to see the different item name coming there

3) Now to fix it first change the value of Profile option "INV: Updateable item name" to YES at user level. this ideally should be done by support team only and NOT business users

4) Then again Reopen the Master Item form so that the changes of the profile option take effect.

5) Query the item again in the Master Org. This time you will see the Name field as editable Field

6) Add any character or Dash (-) in front of the Item Name. and Save it.

7) Re query the item again with the new name ( with extra character / Dash) in Master Org , then go to the same problematic Org and see the item name. The New Item name with Dash will be coming in that org also.

8) then Go back to Master Org, Remove the Dash from the Name ( to bring back the original item name) and again Save it.

9) Re query it again in Master Org with this new name (which now will be the original item name) and then go to problematic org again.. See the Item name there, it will be fixed.

10) Change the Value of Profile option Back to NO.

Wednesday, April 13, 2016

How To Check Cross Validation Rules



The cross validation rule setup can be very complex depending on the complexity of the Accounting Flexfield Structure.
There are a number of ways to get the wrong results and the best way to investigate is listed below:

1. Make sure that you include all the values you want the rule to apply to. If they are not included then they are automatically excluded.
Where the segment values are not of a fixed length start with 0 not 00 otherwise 0 is not in the rule.
Example
Include 01-00-00-0000 to 01-ZZ-ZZZZ-ZZZZ
should be
Include 01-0-0-0 to 01-zz-zzzz-zzzz
Otherwise the Exclude 01-0-01-0 to  01-AA-ZZZZ-ZZZZ
is not going to affect the outcome since it is not within the range that the rule will be applied to.

Note: Lower case letters are looked at after upper case hence 0 to z includes Z

2. Apply the same logic to the exclude rules
so the exclude should be 01-0-0-0 to  01-AA-ZZZZ-ZZZZ

In order to check whether the range will appear in a rule we have some sql below.
a. First find your chart of account details using script 1 or 2.
b. Adjust the sql to work for your chart of accounts by adding or taking away any statement relating to segmentn in scripts 3 and 4
c. check that the combination in your exclude lines exists in the include using script 3, in the example above test 01-0-0-0 and 01-AA-ZZZZ-ZZZZ

For combinations failing when they should not:
d. check that the combination is in the include using sql 3   -  it should be
e. check that the combination is in the exclude using sql 4  -  it should not be if it is restructure the rule until it is excluded.

For a combination passing when it should fail
f. check that the combination is in the include using sql 3 -   it should be if it is not restructure the rule until it is included
g. check that the combination is in the exclude using sql 4 - it should not be if it is restructure the rule until it is excluded.
SCRIPTS
1. To find the details of the Accounting Flexfield structure you are working with in R12 run this sql
SELECT gls.name,
idfs.id_flex_num chart_of_accounts_id,
idfs.segment_num,
idfs.flex_value_set_id,
fvs.flex_value_set_name,
idfs.application_id,
idfs.id_flex_code,
idfs.application_column_name,
idfs.segment_name,
fvs.security_enabled_flag,
(CASE
WHEN fvs.validation_type = 'F' THEN 'Table'
WHEN fvs.validation_type = 'I' THEN 'Independent'
WHEN fvs.validation_type = 'D' THEN 'Dependent'
WHEN fvs.validation_type = 'N' THEN 'None'
WHEN fvs.validation_type = 'P' THEN 'Pair'
WHEN fvs.validation_type = 'U' THEN 'Special'
ELSE 'Unknown Type'
END) validation_type,
(CASE
WHEN fvs.validation_type = 'F' then fvt.application_table_name
ELSE 'Not Applicable' END) validation_table_name
FROM gl_ledgers gls,
fnd_id_flex_segments idfs,
fnd_flex_value_sets fvs,
fnd_flex_validation_tables fvt
WHERE gls.chart_of_accounts_id (+) = idfs.id_flex_num
AND fvs.flex_value_set_id = idfs.flex_value_set_id
AND gls.ledger_id = &LedgerId
AND idfs.application_id = 101
AND idfs.id_flex_code IN ('GL#', 'GLLE')
AND fvs.flex_value_set_id = fvt.flex_value_set_id (+)
ORDER BY idfs.id_flex_code, idfs.id_flex_num , idfs.segment_num ASC;

2. To find the details of the Accounting Flexfield structure you are working with in R11i run this sql:
SELECT glsob.name,
idfs.id_flex_num chart_of_accounts_id,
idfs.segment_num,
idfs.flex_value_set_id,
idfs.application_id,
idfs.application_column_name,
idfs.segment_name,
fvs.security_enabled_flag
FROM gl_sets_of_books glsob,
fnd_id_flex_segments idfs,
fnd_flex_value_sets fvs
WHERE glsob.chart_of_accounts_id = idfs.id_flex_num
AND fvs.flex_value_set_id = idfs.flex_value_set_id
AND glsob.set_of_books_id = &SoBID
AND idfs.application_id = 101
AND idfs.id_flex_code = 'GL#'
ORDER BY idfs.segment_num ASC;rt code here]

You will need to modify the ranges in the where clause from ('&s1' between segment1_low and segment1_high) onwards so that you have the segment values returned above in the order of use. If you have only 5 segments for example and they are segment1, segment2, segment3,segment4,segment5 then you would have
(('&s1' between segment1_low and segment1_high) and
('&s2' between segment2_low and segment2_high) and
('&s3' between segment3_low and segment3_high) and
('&s4' between segment4_low and segment4_high) and
('s&5' between segment5_low and segment5_high));

3. To find if a code combination will be in the include range in either version use this giving the segment value for each prompt:
select
rule_line_id,
application_id,
id_flex_code,
id_flex_num,
flex_validation_rule_name,
enabled_flag
from fnd_flex_include_rule_lines
where application_id = 101
and id_flex_code = 'GL#'
and id_flex_num = 101
and (('&s1' between segment1_low and segment1_high) and
('&s2' between segment2_low and segment2_high) and
('&s3' between segment3_low and segment3_high) and
('&s4' between segment4_low and segment4_high) and
('s&5' between segment5_low and segment5_high) and
('&s6' between segment6_low and segment6_high) and
('&s7' between segment7_low and segment7_high));

4. To find if a combination is in the exclude range in either version use this giving the segment value for each prompt:
select
rule_line_id,
application_id,
id_flex_code,
id_flex_num,
flex_validation_rule_name,
enabled_flag
from fnd_flex_exclude_rule_lines
where application_id = 101
and id_flex_code = 'GL#'
and id_flex_num = 101
and (('&s1' between segment1_low and segment1_high) and
('&s2' between segment2_low and segment2_high) and
('&s3' between segment3_low and segment3_high) and
('&s4' between segment4_low and segment4_high) and
('s&5' between segment5_low and segment5_high) and
('&s6' between segment6_low and segment6_high) and
('&s7' between segment7_low and segment7_high));

Friday, April 1, 2016

Unable To Access HR Integrators Using Oracle Web ADI?



Unable To Access HR Integrators Using Oracle Web ADI?

Please attach the 2 submenu's in Web ADI responsibility menu (or the Responsibility Menu where you are trying to access the seeded integrators - Example: 'HRMS Manager Responsibility' Menu) and retest the issue.



Example when using 'Web ADI Responsibility:

Navigation: System Administrator > Application > Menu > Query Web ADI Responsibility Menu



Add the following Submenu with a Seq Number (you will not need to enter any other info) ->

HR ADI Seeded Integrator Form Functions

HRMS ADI Create Document

"The Desktop Integration - Create Document function is not available under the US Super HRMS Manager responsibility."

"The Desktop Integration - Create Document function is not available under the US Super HRMS Manager responsibility."

To implement the solution, please execute the following steps:
Add this function to the menu attached to your responsibility and retest the issue .
'Desktop Integration - Create Document' function to Navigator.
System Administrator > Application > Menu
User Menu Name: US SHRMS Navigator
Ensure that function 'Create Document' exists else add to the menu
Also ensure that the submenu: HR ADI Seeded Integrator Form Functions is also available / displayed on the menu: US SHRMS Navigator

Wednesday, March 16, 2016

Party / Account structure in Trading Community Architecture.

1. What Is A Party?
One of the most important things to understand about the TCA model is that  the concept of “customer” is separated into two layers:
The Party layer and the Account layer
When CRM applications refer to “Customer” they are referring to the Party Layer. On the other hand, when ERP applications refer to “Customer” they are referring  to the Account Layer. Thus, confusion arises because both are using the word “Customer” to refer to two different things.
Party Layer
The Party layer consists of the Party itself, and the attributes of the  Party (such as the Party's addresses and contacts). A Party is defined as an entity that can enter into a business relationship. A Party can either be a Person or an Organization.
Examples of Parties:
Legacy Communications, Inc. (Organization)
University of California (Organization)
Girl Scouts of America (Organization)
Jim Johnson (Person)
Parties may have one or more addresses, and each address may have one or more uses (e.g., Bill To, Ship To, etc.). Additionally, Parties may have one or more contacts. Contacts are created by linking two Parties together via a Relationship (e.g., Jim Johnson Contact of Legacy Communications, Inc). It is important to note that the Party Layer can exist independently
without the presence of a corresponding Account Layer.
Account Layer
The Account layer consists of the Account and the Account attributes (such as the Accounts addresses and contacts). An Account is a financial roll-up point for a Party that defines the customer relationship between a Party and the implementing organization. Orders and invoices, for example, are created and processed at the Account layer, not the Party layer.
The Account layer cannot exist without a corresponding Party layer. Accounts must have one and only one owner, which must be a Person Party or and Organization Party. It is the Account owner that is financially responsible for the Account.
To illustrate this, think of a credit card as an account. A credit card cannot exist without a Person to own it and be financially responsible for it. However, a Person can exist without a credit card.
If the entire E-Business Suite is used, the Party layer is usually created first in CRM, and then the Account layer is added when it becomes necessary. In many cases, the Account layer is added when a Party wishes to place an order.  When an Account is created, it must be linked to one and only one Party. This Party is the Account owner.
Once an Account is created, Addresses can be defined for the Account. Account Address must have a corresponding Party Address in order to exist. The corresponding Party address must be associated to the Party that owns the Account. Account Addresses are a subset of Party Addresses. Similarly, an Account Contact must first be defined as a Party Contact for the Party
that owns the Account. Party Contacts are defined through Relationships.


2. What Is The Difference Between A Party and A Customer Account?

A party is an entity that can enter into business and can be of the type Organization or Person. A party exists separately from any business relationship that it enters in to with another party. Information about a party such as addresses and contacts can be shared with the customer accounts of the party. For example, Vision Distribution could be a party within your trading community.
A customer is an organization or person with whom you have a selling relationship. This selling relationship can result from the purchase of products and services or from the negotiation of terms and conditions that provide the basis for future purchases. For example, a division of Vision Distribution could become one of your customers.
A customer account represents the business relationship that a party can enter in to with another party. The account has information about the terms and conditions of doing business with the party. For example, you could open a commercial account for purchases to be made by Vision Distribution for its internal use and a reseller account for purchases made by Vision Distribution for sales of your products to end–users .
You can create multiple customer accounts for a party to maintain information about categories of business activities. For example, to track invoices for different types of purchases, you can maintain an account for purchasing office supplies and another account for purchasing furniture.
You can also maintain multiple customer accounts for a customer that transacts business with more than one line of business in your organization. You maintain separate customer profiles, addresses, and contacts for each customer account.
What is the difference between a Person and an Organization when setting up a Customer Account.
When setting up a customer in AR, you have the option of selecting if the customer is an Organization or a Person.
Select Person if the customer is an individual. This option is available as a setup when using the CRM products. If you have CRM products installed you may select the type Person. The Customer Standard Form will then display fields for entering Person information as it pertains to an individual in many CRM applications.
All Customers in the ERP applications are of the type Organization. The Customer Standard Form will then display fields for entering Organization information for setting up customers in the ERP applications.
In the above setup the Person setup will be reflected in the Person information tables in the HZ module. The “Organization” information will go against the HZ organization information tables.
When searching on an Organizations, the customer type of Person will never show up in the Match results and vice versa.

3. What Is The Difference Between A Party Site And A Party Address

A party site is the location where a particular party is physically located. Every party has only one identifying address, but a party can have multiple party sites. A customer's address is a party site used in the context of a customer account for billing, shipping, or other purposes. For example:  The customer may have a billing site, a shipping site etc.
4. What Is An Identifying Address?

You can designate one address per party as an Identifying Address. A party can have multiple party sites, but each party has one identifying address. The identifying address is used as the default search criteria in the database.
5. What Is A Party Id?

This is a unique number that identifies a party
6. What Is A Site Number?

This is a unique number that identifies a location.
7. In Release 11.5 Customer form, why Doesn't The First Customer Have An Address In The Find Results Window?

The Match Results window displays both Party and Account information.
Rows that are flush to the left, with a blank Customer Number field are the Party Records.
Rows underneath the Party Record, that are indented to the right, and contain a value in Customer Number field are Accounts.
The use of indentation is a graphic way of illustrating that the Accounts fall under the Party. When querying for customers that return multiple match results, this format makes it easier to identify whether you are looking at a Party or an Account.

Wednesday, February 24, 2016

Jobs with Approval Assignments

/* Formatted on 2016/02/24 11:37 (Formatter Plus v4.8.0) */
SELECT   hou.NAME op_unit,
         DECODE (pj.business_group_id,
                 81, 'US',
                 141, 'DK',
                 142, 'DE',
                 pj.business_group_id
                ) business_group,
         pj.job_id, pjt.NAME job_name, pcga.control_group_name,
         pcf.control_function_name, pcf.description, pcf.document_type_code,
         pcf.document_subtype, ppca.*                     ---ppca.* --- pcga.*
    FROM apps.po_position_controls_all ppca,
         apps.per_jobs_tl pjt,
         apps.po_control_groups_all pcga,
         apps.po_control_functions pcf,
         apps.hr_all_organization_units hou,
         apps.per_jobs pj
   WHERE ppca.job_id = pjt.job_id                                  ---1=1 ----
     AND pjt.job_id = pj.job_id
     AND ppca.control_group_id = pcga.control_group_id
     AND ppca.org_id = pcga.org_id
     AND ppca.control_function_id = pcf.control_function_id
     AND ppca.org_id = hou.organization_id
---and ppca.org_id = 202 and pj.business_group_id <> 142
     AND ppca.org_id = 202                 --- and pj.business_group_id <> 141
---and pjt.name = 'Senior Director.Executive/Senior-Level Officials and Managers' ---'Coordinator.Administrative Support Workers'
---and ppca.creation_date > trunc(sysdate)
     AND pjt.LANGUAGE = 'US'
     AND document_subtype IN ('INTERNAL', 'PURCHASE', 'STANDARD')
     AND pcf.control_function_name = 'Approve Purchase Requisitions'
---and pcf.control_function_name = 'Approve Standard Purchase Orders'
---and pjt.name = 'Specialist.Professionals'
---and pjt.name = 'Analyst.Professionals'
---order by hou.name, pjt.name, pcf.document_type_code, pcf.document_subtype
     AND pjt.NAME LIKE
                'Vice President.Executive/Senior-Level Officials and Managers'
ORDER BY hou.NAME,
         pcf.control_function_name,
         pjt.NAME,
         pcf.document_type_code,
         pcf.document_subtype;

Friday, February 19, 2016

Create Accounting - Cost Management with Error 95325 The GL Date DD-MON-YYYY is not in an Open or a Future Enterable Period. Please select a valid Period

Note: 
If reopening a closed periods not feasible, then review the following Action Plan:

1. Events created in closed GL period can be identified using below query:   

   SELECT  xe.* 
     FROM xla_ae_headers xah, 
          xla_events xe, 
          xla_ae_lines xal, 
          xla_accounting_errors xae, 
          gl_code_combinations_kfv gcc 
 WHERE xae.application_id=707 
   AND xe.application_id=707 
   AND xal.application_id=707 
   AND xah.application_id=707 
   AND xae.ledger_id=&ledger_id 
   AND xae.ae_header_id=xah.ae_header_Id 
   AND xah.ae_header_Id=xal.ae_header_id 
   AND xae.event_id=xe.event_id 
   AND xe.event_id=xah.event_id 
   AND xah.GL_TRANSFER_STATUS_CODE='N' 
   AND xal.code_combination_id=gcc.code_combination_id 

2. A Note of Caution : It is to be understood that sweeping of transactions with the above approach would not change the transaction date in Material/WIP subledgers and hence there could be discrepancy between these periods in the Materials Subledger Vs SLA/GL.
    It could also impact drilldown for these transactions.
3. Please use sweep datafix from Note 883557.1 for updating records whose xah.accounting_entry_status_code='F'. 

    Use the following for pending transactions have xah.accounting_entry_status_code='I'

4. The following is to transfer (Sweep) these pending to current period.

    a. Take back up of the relevant events that are in error with message 95325
     This will only fix events that have errored due to corresponding period being closed.  Customer could choose to add other filtering criteria to narrow down further, if required.

       CREATE TABLE XE_Sweep_bkup_orcl AS
             SELECT xe.*
                     FROM xla_events xe,  xla_accounting_errors xae
                    WHERE xe.application_id = 707
              AND xae.application_id = 707
                    --  AND ENTITY_CODE  =
              AND xe.event_id = xae.event_id
              AND xe.upg_batch_id IS NULL
              AND xae.message_number = 95325
                      AND xae.ledger_id = &Ledger_id
                      AND xe.event_status_code =  'U'
                      AND xe.process_status_code = 'I'

  b. Verify the events from the backup table to ensure they are the ones that would like to sweep.

  c. Sweep them to next period by providing appropriate date in the next open period 
     Update xla_events
     SET EVENT_DATE = '&New_date',
      transaction_date = '&New_date',
      REFERENCE_DATE_1  =  '&New_date',
      CREATED_BY = -11111
     WHERE application_id = 707
     AND Event_id IN (SELECT event_id FROM XE_Sweep_bkup_orcl);

   d. Verify the Numbers of records updated. 
                                                   
   e. COMMIT

   f. Rerun Create Accounting - Cost Management and verify that these transactions get accounted successfully.

Note
-----
If the SLA events already in Final mode  (accounting_entry_status_code='F').
Then also use the below data fix  in xla_ae_headers table :

The following is an example:

update xla_ae_headers xah
 set xah.accounting_date = to_date('01.01.2016', 'dd.mm.yyyy'),
     xah.period_name     = '???-16'  ----its JAN-16
where xah.application_id = 707
 and xah.event_type_code = 'RECEIVE'
 and xah.event_id in (300586735, 300586736, 300586739, 300586740)
 and xah.ledger_id = &ledger_id
 and xah.gl_transfer_status_code = 'N';
-------------------------------------------------------------------------------------------------------------------------
   EVENT_STATUS_CODE MEANINGS: 
              I - Incomplete, N - No action, P - Processed, U – Unprocessed

    PROCESS_STATUS_CODE MEANINGS: 
              D - Draft, E - Error, I - Invalid, P - Processed, R - Related event in error, U - Unprocessed, F - Final