Soumyadeep Paul Soumyadeep Paul - 2 months ago 13
SQL Question

Missing or incorrect data in Oracle EBS backend query (SQL)

I am learning Oracle E-Business Suite. Using an SQL Query, I want to fetch all items (ASSEMBLY_ITEM) which are BOM enabled and whose job creation date is within the last 3 years. The query I have written gives me the desired results but somehow, the component item list (COMPONENT_ITEM) has incomplete data or (Null) for some items. For example, some items which I know has 5 components but in the SQL query output, that same item shows up as having a single component. I am verifying the data from the Oracle Apps frontend part.
Here is my query so far:

SELECT DISTINCT BOM.BILL_SEQUENCE_ID,
MSI.ORGANIZATION_ID,
MSI.SEGMENT1 ASSEMBLY_ITEM,
(SELECT SEGMENT1
FROM MTL_SYSTEM_ITEMS_B
WHERE INVENTORY_ITEM_ID=BIC.COMPONENT_ITEM_ID
AND rownum =1
) "COMPONENT_ITEM"
FROM BOM_OPERATIONAL_ROUTINGS BOR,
BOM_BILL_OF_MATERIALS BOM,
BOM_INVENTORY_COMPONENTS BIC,
MTL_SYSTEM_ITEMS_B MSI
WHERE BOR.ASSEMBLY_ITEM_ID = BOM.ASSEMBLY_ITEM_ID
AND BOR.ORGANIZATION_ID = BOM.ORGANIZATION_ID
AND BOM.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID(+)
AND BOM.ASSEMBLY_ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
AND BOM.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSI.ORGANIZATION_ID IN (203, 204, 328)
AND MSI.BOM_ENABLED_FLAG = 'Y'
AND NVL (MSI.ENABLED_FLAG, 'X') = 'Y'
AND BOR.ASSEMBLY_ITEM_ID IN
(SELECT DISTINCT PRIMARY_ITEM_ID
FROM WIP_DISCRETE_JOBS WDJ
WHERE BOR.ASSEMBLY_ITEM_ID = WDJ.PRIMARY_ITEM_ID
AND WDJ.CREATION_DATE >= ADD_MONTHS(SYSDATE, -12*3)
)


My query should return unique assembly items, each of which may have one or more components.

This is what I'm getting so far

Ideally, I should be getting this, all unique assembly items along with their component items. I have achieved this by removing the join on BOM.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID (+), and also removing the semi-join at the end of the query, the "AND BOR.ASSEMBLY_ITEM_ID IN" part, which esssentially filters the results since the last 3 years.

The join part isn't the real issue. Is there any way to filter the result based on items created in the last 3 years, using something other than this method?
What am I missing here?

Answer

There are several things you could do better here.

First, use ANSI JOINS. I see at least some of your problems are due to incorrect attempt as LEFT OUTER joins. Using the ANSI syntax will help you avoid such problems.

Second, don't use DISTINCT unless you understand WHY it is necessary. It should not be necessary to write the query you say you want.

Third, You don't need to join BOM_OPERATIONAL_ROUTINGS -- you're not using it for anything (unless you are trying to use it as a filter -- to limit to BOMs that have a routing?)

Fourth, use EXISTS instead of IN. Oracle's optimizer is mostly smart enough to protect you from the performance penalty that IN would have hit you with in the past. Nevertheless, EXISTS better represents your intent (imo) and places less reliance on the optimizer being smart.

Finally, the ROWNUM=1 to get the component item isn't necessary. You just need to limit by ORGANIZATION_ID as well.

Putting it all together, the following query should be pretty close to what you are looking for:

SELECT bom.bill_sequence_id,
       bom.organization_id,
       msi.segment1 assembly_item_id,
       bic.operation_seq_num,
       msic.segment1 component_item
FROM   bom_bill_of_materials bom
       LEFT JOIN mtl_system_items msi
         ON msi.organization_id = bom.organization_id
         AND msi.inventory_item_id = bom.assembly_item_id
         AND msi.enabled_flag = 'Y'
         AND msi.bom_enabled_flag = 'Y'
       LEFT JOIN bom_inventory_components bic ON bic.bill_sequence_id = bom.common_bill_sequence_id
       LEFT JOIN mtl_system_items msic
         ON msic.organization_id = bom.organization_id
         AND msic.inventory_item_id = bic.component_item_id
WHERE  bom.organization_id IN (203, 204, 328)
AND    EXISTS
         (SELECT 'job for item in last 3 years'
          FROM   wip_discrete_jobs wdj
          WHERE  wdj.primary_item_id = bom.assembly_item_id
          AND    wdj.creation_date >= ADD_MONTHS (SYSDATE, -36))
-- Unless you are loading a table with this, you may want to ORDER BY...
;