OPM lots

Description
Categories: Draft
Columns: Acct Period Id, Transaction Date, Transaction Id, Origin Txn Id, Parent Object Type, Parent Object Id, Object Type, Object Id, Trunc(Gbh.Actual Cmplt Date), Batch No ...
SELECT MMT.ACCT_PERIOD_ID,
       TRUNC(MMT.TRANSACTION_DATE) TRANSACTION_DATE,
       MMT.TRANSACTION_ID,MOG.ORIGIN_TXN_ID,
       MOG.PARENT_OBJECT_TYPE,
       MOG.PARENT_OBJECT_ID,
       MOG.OBJECT_TYPE,
       MOG.OBJECT_ID,
       TRUNC(GBH.ACTUAL_CMPLT_DATE),
       GBH.BATCH_NO,
       GBH.BATCH_ID,
       MMT.ORGANIZATION_ID,
       MMT.INVENTORY_ITEM_ID,
       MLN.PARENT_LOT_NUMBER,
       MLN.LOT_NUMBER,
       MLN.GRADE_CODE,
       ABS(MTLN.PRIMARY_QUANTITY) TRANSACTION_QTY
  FROM MTL_OBJECT_GENEALOGY MOG,
       MTL_LOT_NUMBERS MLN,
       MTL_TRANSACTION_LOT_NUMBERS MTLN,
       MTL_MATERIAL_TRANSACTIONS MMT,
       GME_BATCH_HEADER GBH
 WHERE 1=1
 --MLN.PARENT_LOT_NUMBER = 'FS-2023'
   AND MOG.PARENT_OBJECT_TYPE = 1 --1 IS FOR BATCH PRODUCT
   AND MLN.GEN_OBJECT_ID = MOG.PARENT_OBJECT_ID
   AND MTLN.INVENTORY_ITEM_ID = MLN.INVENTORY_ITEM_ID
   AND MTLN.ORGANIZATION_ID = MLN.ORGANIZATION_ID
   AND MTLN.LOT_NUMBER = MLN.LOT_NUMBER
   --AND MTLN.TRANSACTION_ID = MOG.ORIGIN_TXN_ID /* IS WE DIDN'T USE THIS JOIN because the LOT 2281-HT PROD QTY 4.12 COMES TWO TIMES TO AVOID DOUBLE QTY USING BELOW JOIN CONDT*/
   AND NVL(MTLN.TRANSACTION_ID, MOG.ORIGIN_TXN_ID) = NVL(MOG.ORIGIN_TXN_ID, MTLN.TRANSACTION_ID)  
         --OR MLN.PARENT_LOT_NUMBER IN('FS-0021', 'FS-0032', 'FS-0047', 'FS-0018', 'FS-0020', 'FS-2220', 'FS-2023', 'FS-0037', 'FS-0097', 'FS-0098', 'FS-0036', 'FS-0003', 'FS-0114'))
         /*ADD LOT NO IN ABOVE IN LIST IF SOME LOT NO RECORDS ARE NOT COMING*/
   AND MMT.TRANSACTION_ID = MTLN.TRANSACTION_ID
   AND MMT.TRANSACTION_TYPE_ID = 44 -- WIP COMPLETION
   AND MMT.TRANSACTION_ACTION_ID = 31 -- Assembly completion
   AND MMT.TRANSACTION_SOURCE_TYPE_ID = 5 -- JOB OR SCHEDULE .
   AND MMT.TRANSACTION_ID NOT IN
            (SELECT DISTINCT MMT1.SOURCE_LINE_ID
               FROM MTL_MATERIAL_TRANSACTIONS MMT1
                    /*TO TAKE FINAL WIP COMPLITION RECORD ONLY*/
              WHERE MMT1.TRANSACTION_SOURCE_ID = MMT.TRANSACTION_SOURCE_ID  
                AND MMT1.TRX_SOURCE_LINE_ID = MMT.TRX_SOURCE_LINE_ID
                AND MMT1.ORGANIZATION_ID = MMT.ORGANIZATION_ID  
                AND MMT1.SOURCE_LINE_ID IS NOT NULL
                AND MMT1.TRANSACTION_TYPE_ID = 17 --WIP COMPLETION RETURN
                AND MMT1.TRANSACTION_ACTION_ID = 32 -- Assembly RETURN
                AND MMT1.TRANSACTION_SOURCE_TYPE_ID = 5 -- JOB OR SCHEDULE .
            )
   AND GBH.BATCH_ID = MMT.TRANSACTION_SOURCE_ID
   AND GBH.ORGANIZATION_ID = MMT.ORGANIZATION_ID