Description:
This query fetches the detailed information related to those assets which are added newly for a particular period.
Script to New Asset Addition
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
Queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
