NEW ASSETS ADDITION QUERY

This query fetches the detailed information related to those assets which are added newly for a particular period.



 SELECT  FAL.segment1 SOL_ID
            ,FFV.description BRANCH_NAME
            ,FAA.tag_number  ASSET_ID
            ,FAA.description ASSET_DESCRIPTION
            ,FCB.SEGMENT1 ASSET_CLASS      --major_category
            ,FCB.segment2  ASSET_SUB_GROUP -- minor_category
            ,(FB.life_in_months)/12  DEP_YEAR
            ,FAA.creation_date ASSET_CREATION_DATE
            ,FB.date_placed_in_service INSERVICE_DATE
            ,FB.original_cost  COST
            ,APS.vendor_name MANUFACTURER_NAME
            ,'ADDITION' REMARKS
            ,FAA.asset_number DOCUMENT_NUM
            ,(SELECT FMA.create_batch_date
               FROM fa_mass_additions FMA
               WHERE 1=1
                 AND TRIM(FMA.asset_number) = TRIM(FAA.asset_number)
                 AND FMA.book_type_code     = FB.book_type_code) DOCUMENT_DATE
            ,FU.user_name AUTHORIZED_BY
            ,NULL COST_CENTER
            ,FAA.serial_number SL_NO
            ,SUBSTR(AIA.ATTRIBUTE5,1,INSTR(AIA.attribute5,'/')-1) PO
            ,(SELECT PPF1.full_name
                FROM per_all_people_f PPF1
               WHERE PPF1.person_id = FDH.assigned_to
                 AND TRUNC(sysdate) BETWEEN TRUNC(PPF1.effective_start_date) AND TRUNC(PPF1.effective_end_date)) CUSTODAIN
            ,GLCC.segment3 DEPRICIATION_ACCOUNT
            ,GLCC.segment4  DEP_LOC_SEGMENT
            ,GLCC.segment5  DEP_COST_CENTER
            ,FAK.segment1 CSR
FROM         fa_locations FAL
            ,fnd_flex_values_vl FFV
            ,fnd_flex_value_sets FFVS
            ,fa_distribution_history FDH
            ,fa_additions FAA
            ,fa_categories_b FCB
            ,fa_category_books FCBK
            ,fa_books FB
            ,fa_adjustments FADJ
            ,fa_asset_invoices FAI
            ,fa_asset_keywords FAK
            ,ap_suppliers APS
            ,ap_invoices_all AIA
            ,gl_code_combinations GLCC
            ,fnd_user FU
            ,per_all_people_f PPF
      WHERE 1=1
      AND FAL.segment1             = FFV.flex_value
      AND FFV.flex_value_set_id    = FFVS.flex_value_set_id
      AND FFVS.flex_value_set_name ='Axis_LOC_Value set'
      AND FAL.location_id         = FDH.location_id
      AND FDH.asset_id            = FAA.asset_id
      AND FCB.category_id         = FAA.asset_category_id
      AND FAA.asset_id            = FB.asset_id
      AND FB.book_type_code       = FADJ.book_type_code
      AND FDH.book_type_code      = FB.book_type_code
      AND FB.asset_id             = FADJ.asset_id
      AND FADJ.source_line_id     = FAI.source_line_id
      AND FCBK.category_id        = FCB.category_id
      AND APS.vendor_id           = FAI.po_vendor_id
      AND FAI.asset_id            = FAA.asset_id
      AND FAA.asset_key_ccid      = FAK.code_combination_id
      AND FAI.invoice_id          = AIA.invoice_id
      AND FDH.code_combination_id = GLCC.code_combination_id
      AND FAA.created_by   = FU.user_id
      AND FU.employee_id   = PPF.person_id
     -- AND FDH.assigned_to   = PPF.person_id
      AND FADJ.source_type_code   = 'ADDITION'
      AND FAA.parent_asset_id IS NULL
      AND fdh.transaction_header_id_out is null
      AND FCBK.book_type_code     = :p_asset_book 
      AND FAA.creation_date between TO_DATE(:p_date_from, 'YYYY/MM/DD HH24:MI:SS') AND TO_DATE(:p_date_to,'YYYY/MM/DD HH24:MI:SS')
GROUP BY FAL.segment1
        ,FFV.description
        ,FAA.tag_number
        ,FAA.description
        ,FCB.SEGMENT1  --major_category
        ,FCB.segment2   -- minor_category
        ,(FB.life_in_months)
        ,FAA.creation_date
        ,FB.date_placed_in_service
        ,FB.original_cost 
        ,APS.vendor_name
        ,FAA.asset_number
        ,FU.user_name
        ,FAA.serial_number
        ,aia.attribute5   
        ,GLCC.segment3
        ,GLCC.segment4
        ,GLCC.segment5
        ,FAK.segment1
        , FB.book_type_code
        ,FDH.assigned_to