I was trying to revamp a “fully functional” custom module for our Oracle EBS R12 Order Management & came across “FRM-41826: Cannot Replace Group;columns Don’t Match Lov” while a new record group was set for an existing LOV during runtime based on the user choice.

I was pretty sure that the query is flawless as all I did was to removing some UNIONs and thus stripping off rows from the result set!.

After spending almost hour time, Finally I decided to check the columns returned by the record group. My first record group has the below SQL

select CUSTOMER_ID party_id, CUSTOMER_NUMBER, CUSTOMER_NUMBER ACCOUNT_NUMBER, CUSTOMER_NAME, null CASH_CUSTOMER_ID
from OMS_SHIPTO_ADDRESS_V where org_id = :PARAMETER.ORG_ID
AND CUSTOMER_ID NOT IN (Select account_id from OMS_ORGS_CASH_ACCOUNTS where organization_id = :PARAMETER.ORG_ID)
UNION ALL
SELECT oocc.account_id party_id, occ.PHONE_1 CUSTOMER_NUMBER, TO_CHAR(oocc.ACCOUNT_NUMBER) ACCOUNT_NUMBER, occ.CUSTOMER_NAME, occ.CUSTOMER_ID CASH_CUSTOMER_ID FROM OMS_CASH_CUSTOMERS occ, OMS_ORGS_CASH_ACCOUNTS oocc
WHERE occ.PHONE_1 IS NOT NULL
AND occ.ORGANIZATION_ID = :PARAMETER.ORG_ID
AND oocc.organization_id = occ.ORGANIZATION_ID
AND PRINT_PHONE_1 = 'Y'
UNION ALL
SELECT oocc.account_id party_id, occ.PHONE_2 CUSTOMER_NUMBER, TO_CHAR(oocc.ACCOUNT_NUMBER) ACCOUNT_NUMBER, occ.CUSTOMER_NAME, occ.CUSTOMER_ID CASH_CUSTOMER_ID FROM OMS_CASH_CUSTOMERS occ, OMS_ORGS_CASH_ACCOUNTS oocc
WHERE occ.PHONE_2 IS NOT NULL
AND occ.ORGANIZATION_ID = :PARAMETER.ORG_ID
AND oocc.organization_id = occ.ORGANIZATION_ID
AND PRINT_PHONE_2 = 'Y'
ORDER BY 2

while the 2nd record group has the below SQL

select CUSTOMER_ID party_id, CUSTOMER_NUMBER, CUSTOMER_NUMBER ACCOUNT_NUMBER, CUSTOMER_NAME, null CASH_CUSTOMER_ID
from OMS_SHIPTO_ADDRESS_V where org_id=:PARAMETER.ORG_ID ORDER BY 2

As simple as it is.

After wasting almost an hour time to figure out what went wrong, finally I decided to go through the record group columns, hoping some kind of mismatch between the columns returned by both SQL blocks! (Just hoping)

First RG

Second RG

The first SQL block was returning Customer ID values, which were numeric and the 2nd SQL block was returning NULL, mapping the datatype as Character!

and this was causing FRM-41826.

I fixed it by change the NULL to 0 with my second block.

select CUSTOMER_ID party_id, CUSTOMER_NUMBER, CUSTOMER_NUMBER ACCOUNT_NUMBER, CUSTOMER_NAME, 0 CASH_CUSTOMER_ID
from OMS_SHIPTO_ADDRESS_V where org_id=:PARAMETER.ORG_ID ORDER BY 2

If you ever get stuck with such situation, do a quick analysis on the Column Specification & insure that both record groups are returning the columns with same datatypes!

regards,

rajesh