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
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
Good Blog, Thanks For Sharing This Informative article.
ReplyDeleteOracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
Big Data and Hadoop Training In Hyderabad