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,
AND rownum =1
FROM BOM_OPERATIONAL_ROUTINGS BOR,
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)
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?)
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.
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... ;