Thieves are good ...

(First) Theft is not ...
Well poetically speaking, there are several expression in praise for those who have been accused of taking (certain "things") ...

The point, about my admiration here is of the idea for the skill of achieving the task by making use of the resources available to all (in general) potentially in plane sight or not so difficult to obtain, but crafting an elegance to fulfill the want, (I considered writing 'need', but felt that sounded imprudent), especially with the intent of keeping others away from harms way, during the act and for some "from the act too". I am discounting the fact that of the material aspect of belonging involved in the action, which people to more popular belief these days is often give greater importance of the act.

Some of you might find this expression to be oxymoron-ish, this is what I have had already earlier once heard of while offering contradictory thought to that of, I would say, a commonly expected notion (from the "word"). - So pardon me of hurting your feelings, on that ground.

Back - to thought-charting ...
The background of this inspiration, came from recent revisit to a series 'White collar'.
And of-course people landed on this page would prefer to get their solution, (in the course will) also be sharing in the analogy built with all the prologue.

Topic-

Compare record attributes from the same table (technical heading)

What an association ebsWiki, technical piece could be with the admiration to honing the skill in lets say, not commonly exploited - that's about it.

So, I recently figured out a new keyword in Oracle "unpivot". With its usage, quite easily long rows of data can be transposed, exposing the value by attributes, thus enabling an effective comparison between two sets of similar information - i.e. comparing two records field by field.

My need arose in context of, so the guiding solution, and of-course can invariably be used in other situations is, from the requirement of comparing 'inventory item data' in e-Business suite. Depending on which Oracle apps version you are on, this primary table for Inventory module has 200 - 400 attributes for each record that be of interest - I would even opt for this solution with mere 50+ fields in question.

So far I have mostly been exporting the records in excel and additionally come up with formula to compare two inventory item records, say for example. This also needed even after all this, a struggle to dig for differences.

What if - each inventory item records (with say 350 fields) is represented as attribute-value pair, along with its' key (item-org combination), the consumption of such layout makes it much more simpler. Thus, the only significant task, is to primarily achieve this transposition.

SELECT 
    ID_KEY1, ID_KEY2, 
    B_B_COL, B_B_VAL, LAST_UPDATE_DATE B_LAST_UPDATE_DATE,
    SEGMENT1
FROM 
    (SELECT INVENTORY_ITEM_ID ID_KEY1, ORGANIZATION_ID ID_KEY2, LAST_UPDATE_DATE, SEGMENT1,
        ENABLED_FLAG, ATTRIBUTE_CATEGORY, ATTRIBUTE9, CUSTOMER_ORDER_FLAG, SO_TRANSACTIONS_FLAG, RETURNABLE_FLAG, TO_CHAR(HAZARD_CLASS_ID) HAZARD_CLASS_ID, ENFORCE_SHIP_TO_LOCATION_CODE, TO_CHAR(RECEIVE_CLOSE_TOLERANCE) RECEIVE_CLOSE_TOLERANCE, TO_CHAR(SOURCE_TYPE) SOURCE_TYPE, TO_CHAR(UNIT_VOLUME) UNIT_VOLUME, TO_CHAR(CUM_MANUFACTURING_LEAD_TIME) CUM_MANUFACTURING_LEAD_TIME, REPETITIVE_PLANNING_FLAG, TO_CHAR(VARIABLE_LEAD_TIME) VARIABLE_LEAD_TIME, DEFAULT_INCLUDE_IN_ROLLUP_FLAG, TO_CHAR(PREPROCESSING_LEAD_TIME) PREPROCESSING_LEAD_TIME, TO_CHAR(FIXED_DAYS_SUPPLY) FIXED_DAYS_SUPPLY, TO_CHAR(ENGINEERING_DATE) ENGINEERING_DATE, TO_CHAR(SECONDARY_SPECIALIST_ID) SECONDARY_SPECIALIST_ID, TO_CHAR(WARRANTY_VENDOR_ID) WARRANTY_VENDOR_ID, CYCLE_COUNT_ENABLED_FLAG, TO_CHAR(MRP_PLANNING_CODE) MRP_PLANNING_CODE, CONTAINER_TYPE_CODE, GLOBAL_ATTRIBUTE_CATEGORY, GLOBAL_ATTRIBUTE9, USAGE_ITEM_FLAG, ORDERABLE_ON_WEB_FLAG, BULK_PICKED_FLAG, FINANCING_ALLOWED_FLAG, TO_CHAR(DUAL_UOM_DEVIATION_LOW) DUAL_UOM_DEVIATION_LOW, CREATE_SUPPLY_FLAG, TO_CHAR(CURRENT_PHASE_ID) CURRENT_PHASE_ID, TO_CHAR(SO_AUTHORIZATION_FLAG) SO_AUTHORIZATION_FLAG, TO_CHAR(DAYS_MAX_INV_WINDOW) DAYS_MAX_INV_WINDOW, ATTRIBUTE17, ATTRIBUTE26, CHILD_LOT_VALIDATION_FLAG, TO_CHAR(MATURITY_DAYS) MATURITY_DAYS, RECIPE_ENABLED_FLAG, GLOBAL_ATTRIBUTE15
        FROM MTL_SYSTEM_ITEMS_B MSIB
        WHERE 1=1
  /*Record limiting factor*/
        AND EXISTS (SELECT 1 FROM
            MTL_SYSTEM_ITEMS_INTERFACE MSII WHERE 1=1
            AND process_flag NOT IN 7 
            AND MSIB.INVENTORY_ITEM_ID = MSII.INVENTORY_ITEM_ID
            AND MSIB.ORGANIZATION_ID = MSII.ORGANIZATION_ID
            )
    ) 
UNPIVOT INCLUDE NULLS
    (b_B_VAL
        FOR b_B_COL
            IN (
        ENABLED_FLAG, ATTRIBUTE_CATEGORY, ATTRIBUTE9, CUSTOMER_ORDER_FLAG, SO_TRANSACTIONS_FLAG, RETURNABLE_FLAG, HAZARD_CLASS_ID, ENFORCE_SHIP_TO_LOCATION_CODE, RECEIVE_CLOSE_TOLERANCE, SOURCE_TYPE, UNIT_VOLUME, CUM_MANUFACTURING_LEAD_TIME, REPETITIVE_PLANNING_FLAG, VARIABLE_LEAD_TIME, DEFAULT_INCLUDE_IN_ROLLUP_FLAG, PREPROCESSING_LEAD_TIME, FIXED_DAYS_SUPPLY, ENGINEERING_DATE, SECONDARY_SPECIALIST_ID, WARRANTY_VENDOR_ID, CYCLE_COUNT_ENABLED_FLAG, MRP_PLANNING_CODE, CONTAINER_TYPE_CODE, GLOBAL_ATTRIBUTE_CATEGORY, GLOBAL_ATTRIBUTE9, USAGE_ITEM_FLAG, ORDERABLE_ON_WEB_FLAG, BULK_PICKED_FLAG, FINANCING_ALLOWED_FLAG, DUAL_UOM_DEVIATION_LOW, CREATE_SUPPLY_FLAG, CURRENT_PHASE_ID, SO_AUTHORIZATION_FLAG, DAYS_MAX_INV_WINDOW, ATTRIBUTE17, ATTRIBUTE26, CHILD_LOT_VALIDATION_FLAG, MATURITY_DAYS, RECIPE_ENABLED_FLAG, GLOBAL_ATTRIBUTE15
            )
    );

Some explanation, if you still care --- :), and obviously the illustrated solution derivation.

• Since there are too many fields to work with, so ideally it is important to break them in sizable chuck. With no particular important to specific nature of attributes / their classifications for grouping, I just picked up sets of 50 - 52 fields at a time.
• Significance of breaking such a huge matrices is also required for their in-comprehendible need of memory (if otherwise, all of them put together), especially when the data-table in question is basis of the module, hence the volume.
• NOTE - It might still remain huge memory hog. Important lesson that I learnt by distributing the 350+ fields and recombining by a use of "UNION ALL", although I might be running the same query on the same table over and over, is significant performance saving. The sample in play offered benefit of over factor of 200 folds. 
• Now, the other important factor, where your individual requirement pops-in, is to try to restrict the overall number of records being churned from the base table you are operating upon. Well, if I do not find anything else, "time" is always the savior, in ebs standard structures.
• Another key to note in restructuring the information, is the base rule of Oracle, to keep datatype consistent while representing data under the same column. A beautiful keyword of "TO_CHAR" come quite handy in delivery.

The above code happen to be, stage 1 where only one of (7) groups of field-sets (chuck) are restructured.

UNION ALL (stage 2)

• This might appear lame and as it actually is, only due to the size and repetitive nature of this entire Query, considered putting it here.

SELECT 
    ID_KEY1, ID_KEY2,
    B_COL, B_VAL, LAST_UPDATE_DATE B_LAST_UPDATE_DATE,
    SEGMENT1
FROM 
    (SELECT INVENTORY_ITEM_ID ID_KEY1, ORGANIZATION_ID ID_KEY2, LAST_UPDATE_DATE, SEGMENT1,
        <"FIELD-SET 1, enlaced with TO_CHAR as necessary">
        FROM MTL_SYSTEM_ITEMS_B MSIB
        WHERE 1=1
        AND EXISTS (SELECT 1 FROM
            MTL_SYSTEM_ITEMS_INTERFACE MSII WHERE 1=1
            AND MSIB.INVENTORY_ITEM_ID = MSII.INVENTORY_ITEM_ID
            AND MSIB.ORGANIZATION_ID = MSII.ORGANIZATION_ID
            )
    ) 
UNPIVOT INCLUDE NULLS
    (B_VAL
        FOR B_COL
            IN (
   <"FIELD-SET 1, only the field labels">
            )
    )
UNION ALL /*n*/
SELECT 
    ID_KEY1, ID_KEY2,
    B_COL, B_VAL, LAST_UPDATE_DATE B_LAST_UPDATE_DATE,
    SEGMENT1
FROM 
    (SELECT INVENTORY_ITEM_ID ID_KEY1, ORGANIZATION_ID ID_KEY2, LAST_UPDATE_DATE, SEGMENT1,
        <"FIELD-SET n, enlaced with TO_CHAR as necessary">
        FROM MTL_SYSTEM_ITEMS_B MSIB
        WHERE 1=1
        AND EXISTS (SELECT 1 FROM
            MTL_SYSTEM_ITEMS_INTERFACE MSII WHERE 1=1
            AND MSIB.INVENTORY_ITEM_ID = MSII.INVENTORY_ITEM_ID
            AND MSIB.ORGANIZATION_ID = MSII.ORGANIZATION_ID
            )
    ) 
UNPIVOT INCLUDE NULLS
    (B_VAL
        FOR B_COL
            IN (
   <"FIELD-SET n, only the field labels">
            )
    )

One of the usages of comparing the inventory item data for me was not directly with itself but with its interface table. Hence, the phase (stage) 3, extrapolated here and then establishing the comparison gives the following (look to) illustration.

WITH ITEM_IFACE_VAL AS 
(SELECT 
    ID_KEY1, ID_KEY2, TRANSACTION_ID,
    COL, VAL, LAST_UPDATE_DATE, PROCESS_FLAG,
    NULL
FROM 
    (SELECT INVENTORY_ITEM_ID ID_KEY1, ORGANIZATION_ID ID_KEY2, LAST_UPDATE_DATE, TRANSACTION_ID, PROCESS_FLAG,
        <"FIELD-SET 1, enlaced with TO_CHAR as necessary">
        FROM MTL_SYSTEM_ITEMS_INTERFACE
        WHERE 1=1
        AND process_flag NOT IN 7 
    ) 
UNPIVOT
    (val
        FOR col
            IN (
   <"FIELD-SET 1, only the field labels">
            )
    )
UNION ALL /*n*/
SELECT 
    ID_KEY1, ID_KEY2, TRANSACTION_ID,
    COL, VAL, LAST_UPDATE_DATE, PROCESS_FLAG,
    NULL
FROM 
    (SELECT INVENTORY_ITEM_ID ID_KEY1, ORGANIZATION_ID ID_KEY2, LAST_UPDATE_DATE, TRANSACTION_ID, PROCESS_FLAG,
        <"FIELD-SET n, enlaced with TO_CHAR as necessary">
        FROM MTL_SYSTEM_ITEMS_INTERFACE
        WHERE 1=1
        AND process_flag NOT IN 7 
    ) 
UNPIVOT
    (val
        FOR col
            IN (
   <"FIELD-SET n, only the field labels">
            )
    )
)
, ITEM_BASE_VAL AS
(SELECT 
    ID_KEY1, ID_KEY2,
    B_COL, B_VAL, LAST_UPDATE_DATE B_LAST_UPDATE_DATE,
    SEGMENT1
FROM 
    (SELECT INVENTORY_ITEM_ID ID_KEY1, ORGANIZATION_ID ID_KEY2, LAST_UPDATE_DATE, SEGMENT1,
        <"FIELD-SET 1, enlaced with TO_CHAR as necessary">
        FROM MTL_SYSTEM_ITEMS_B MSIB
        WHERE 1=1
        AND EXISTS (SELECT 1 FROM
            MTL_SYSTEM_ITEMS_INTERFACE MSII WHERE 1=1
            AND process_flag NOT IN 7 
            AND MSIB.INVENTORY_ITEM_ID = MSII.INVENTORY_ITEM_ID
            AND MSIB.ORGANIZATION_ID = MSII.ORGANIZATION_ID
            )
    ) 
UNPIVOT INCLUDE NULLS
    (B_VAL
        FOR B_COL
            IN (
   <"FIELD-SET 1, only the field labels">
            )
    )
UNION ALL /*n*/
SELECT 
    ID_KEY1, ID_KEY2,
    B_COL, B_VAL, LAST_UPDATE_DATE B_LAST_UPDATE_DATE,
    SEGMENT1
FROM 
    (SELECT INVENTORY_ITEM_ID ID_KEY1, ORGANIZATION_ID ID_KEY2, LAST_UPDATE_DATE, SEGMENT1,
        <"FIELD-SET n, enlaced with TO_CHAR as necessary">
        FROM MTL_SYSTEM_ITEMS_B MSIB
        WHERE 1=1
        AND EXISTS (SELECT 1 FROM
            MTL_SYSTEM_ITEMS_INTERFACE MSII WHERE 1=1
            AND process_flag NOT IN 7 
            AND MSIB.INVENTORY_ITEM_ID = MSII.INVENTORY_ITEM_ID
            AND MSIB.ORGANIZATION_ID = MSII.ORGANIZATION_ID
            )
    ) 
UNPIVOT INCLUDE NULLS
    (B_VAL
        FOR B_COL
            IN (
   <"FIELD-SET n, only the field labels">
            )
    )
) SELECT
    i.transaction_id,
    i.ID_KEY1,
    i.ID_KEY2,
    i.COL,
    i.VAL,
    i.PROCESS_FLAG,
    b.B_COL,
    b.B_VAL,
    b.SEGMENT1,
    mie.MESSAGE_NAME
FROM 
    ITEM_IFACE_VAL I,
    ITEM_BASE_VAL B,
    inv.mtl_interface_errors mie
WHERE 1=1
AND I.ID_KEY1 = B.ID_KEY1
AND I.ID_KEY2 = B.ID_KEY2
AND I.COL = B.B_COL 
AND i.transaction_id = mie.transaction_id (+)
AND I.VAL != NVL(B.B_VAL,'NULL')

Enabler -
Now, with all these set of ginormous fields and the requirement of breaking them into groups, also the repetitive nature and I hate typing them manually all over, I thought of building myself an enabler. I have kept the below piece 'as-is' of my needs. (play with it)

  SELECT MOD (COLUMN_ID, 7) M,
   CHR (9) || CHR (9) ||
   LISTAGG (
      DECODE (DATA_TYPE,
        'VARCHAR2', COLUMN_NAME,
        'TO_CHAR(' || COLUMN_NAME || ') ' || COLUMN_NAME),
      ', ')
   WITHIN GROUP (ORDER BY COLUMN_ID) || 
   CHR (13)
   || '' COLS,
   CHR (9) || CHR (9) ||
   LISTAGG (COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY COLUMN_ID) ||
   CHR (13)
   || '' COLS_DISP,
   NULL
 FROM ALL_TAB_COLUMNS ATC
   WHERE  TABLE_NAME = 'MTL_SYSTEM_ITEMS_INTERFACE'
   /*exclusion list*/
   AND COLUMN_NAME NOT IN ('PROGRAM_APPLICATION_ID',
         'PROGRAM_ID',
         'REQUEST_ID',
         'ORGANIZATION_ID',
         'PROGRAM_UPDATE_DATE',
         'LAST_UPDATE_DATE',
         'LAST_UPDATED_BY',
         'OBJECT_VERSION_NUMBER',
         'CREATION_DATE',
         'CREATED_BY',
         'LAST_UPDATE_LOGIN',
         'INVENTORY_ITEM_ID',
         'ORGANIZATION_ID')
   AND COLUMN_NAME NOT LIKE 'SEGMENT%'
   AND EXISTS
    (SELECT 1
       FROM ALL_TAB_COLUMNS ATC_B
      WHERE  TABLE_NAME = 'MTL_SYSTEM_ITEMS_B'
      AND ATC_B.COLUMN_NAME = ATC.COLUMN_NAME)
GROUP BY MOD (COLUMN_ID, 7)

Happy New Year.