Oracle Fusion Asset Details by Location sql Query
It gives data for Fixed Asset details based on Locations
and also Retrieve Asset Inquiry details plus Purchasing details and Payables batch details
SELECT ASSET_DETAIL.ASSET_NUM
,ASSET_DETAIL.CREA_DATE
,ASSET_DETAIL.CUR_UNIT_SUM
,ASSET_DETAIL.ASS_TYP
,ASSET_DETAIL.ASSET_KEY
,ASSET_DETAIL.TAG_NUM
,ASSET_DETAIL.ASSET_DESC
,ASSET_DETAIL.CAT_TYP
,ASSET_DETAIL.MAN_NAME
,ASSET_DETAIL.SERIAL_NUM
,ASSET_DETAIL.MODEL_NUM
,ASSET_DETAIL.PRO_TYPE
,ASSET_DETAIL.IN_USE
,ASSET_DETAIL.OWN_LEA
,ASSET_DETAIL.LEASE_NUM
,ASSET_DETAIL.ASSET_AREA
,ASSET_DETAIL.TEACH_STAT
,ASSET_DETAIL.BANK_LEASE_NUM
,ASSET_DETAIL.LESSOR
,NVL(ASSET_DETAIL.LEASE_DESC,' ') LEASE_DESC
,ASSET_DETAIL.NEW_USED
,ASSET_DETAIL.UNIT_ADJ
,ASSET_DETAIL.ADD_COST
,ASSET_DETAIL.ATT_CAT
,ASSET_DETAIL.DATE_RETIRED
,TO_CHAR(ASSET_DETAIL.DATE_PLACED,'MM/DD/YYYY') DATE_PLACED
,ASSET_DETAIL.DEPRN_MTHD
,ASSET_DETAIL.LIFE_IN_MON
,ASSET_DETAIL.ADJ_COST
,ASSET_DETAIL.COST
,ASSET_DETAIL.ORIG_COST
,ASSET_DETAIL.RETIR_PEND
,ASSET_DETAIL.SITE_NUM
,ASSET_DETAIL.ROOM
,ASSET_DETAIL.CONC_SEG
,ASSET_DETAIL.INV_NUM
,ASSET_DETAIL.PO_NUM
,ASSET_DETAIL.PROJ_NUM
,ASSET_DETAIL.FA_COST
,ASSET_DETAIL.PAY_COST
,ASSET_DETAIL.PAY_BATCH
,ASSET_DETAIL.LOC
,ASSET_DETAIL.LOC_NAME
,ASSET_DETAIL.FUND
,ASSET_DETAIL.DEPT
,ASSET_DETAIL.CAT
,ASSET_DETAIL.ACC
,ASSET_DETAIL.MAJOR
,ASSET_DETAIL.MINOR
,RESERVE_COST.COSTADDITION
,RESERVE_COST.COSTADJUSTMENT
,RESERVE_COST.COSTREVALUATION
,RESERVE_COST.COSTRETIREMENT
,RESERVE_COST.COSTRECLASS
,RESERVE_COST.COSTTRANSFERR
,RESERVE_COST.COSTENDBAL
,RESERVE_COST.COST_ACC
,RESERVE_COST.RESERVEADDITION
,RESERVE_COST.RESERVEADJUSTMENT
,RESERVE_COST.RESERVEDEPRN
,RESERVE_COST.RESERVERETIREMENT
,RESERVE_COST.RESERVERECLASS
,RESERVE_COST.RESERVETRANSFER
,RESERVE_COST.RESERVEENDBAL
,RESERVE_COST.DEPRN_ACC
FROM (SELECT
NVL(FAV.ASSET_NUMBER,' ') ASSET_NUM
,NVL(TO_CHAR(FAV.CREATION_DATE,'MM/DD/YYYY'),' ') CREA_DATE
,NVL(FAV.CURRENT_UNITS,'') CUR_UNIT_SUM
,(SELECT NVL(FLVV.MEANING,' ')
FROM FND_LOOKUP_VALUES_VL FLVV
WHERE FAV.ASSET_TYPE = FLVV.LOOKUP_CODE
AND FLVV.LOOKUP_TYPE = 'FA_ASSET_TYPE') ASS_TYP
,(SELECT NVL(FAK.SEGMENT1,' ')
FROM FA_ASSET_KEYWORDS FAK
WHERE FAV.ASSET_KEY_CCID = FAK.CODE_COMBINATION_ID(+)) ASSET_KEY
,NVL(FAV.TAG_NUMBER,' ') TAG_NUM
,NVL(FAV.DESCRIPTION,' ') ASSET_DESC
,(SELECT NVL(FLVV.MEANING,' ')
FROM FND_LOOKUP_VALUES_VL FLVV
WHERE FACV.CATEGORY_TYPE = FLVV.LOOKUP_CODE
AND FLVV.LOOKUP_TYPE = 'FA_CATEGORY_TYPE') CAT_TYP
,NVL(FAV.MANUFACTURER_NAME,' ') MAN_NAME
,NVL(FAV.SERIAL_NUMBER,' ') SERIAL_NUM
,NVL(FAV.MODEL_NUMBER,' ') MODEL_NUM
,(SELECT NVL(FLVV.MEANING,' ')
FROM FND_LOOKUP_VALUES_VL FLVV
WHERE FAV.PROPERTY_TYPE_CODE = FLVV.LOOKUP_CODE
AND FLVV.LOOKUP_TYPE = 'FA_PROPERTY_TYPE') PRO_TYPE
,(SELECT NVL(FLVV.MEANING,' ')
FROM FND_LOOKUP_VALUES_VL FLVV
WHERE FAV.IN_USE_FLAG = FLVV.LOOKUP_CODE
AND FLVV.LOOKUP_TYPE = 'FA_YESNO') IN_USE
,(SELECT NVL(FLVV.MEANING,' ')
FROM FND_LOOKUP_VALUES_VL FLVV
WHERE FAV.OWNED_LEASED = FLVV.LOOKUP_CODE
AND FLVV.LOOKUP_TYPE = 'FA_OWNLEASE') OWN_LEA
,(SELECT NVL(FLEA.LEASE_NUMBER,' ')
FROM FA_LEASES FLEA
WHERE FAV.LEASE_ID = FLEA.LEASE_ID) LEASE_NUM
,NVL(FAV.ATTRIBUTE1,' ') ASSET_AREA
,NVL(FAV.ATTRIBUTE2,' ') TEACH_STAT
,NVL(FAV.ATTRIBUTE3,' ') BANK_LEASE_NUM
,NVL((SELECT NVL(PSV.VENDOR_NAME,' ')
FROM POZ_SUPPLIERS_V PSV
,HZ_PARTIES HPAR
,FA_LEASES FLEA
WHERE FAV.LEASE_ID = FLEA.LEASE_ID
AND FLEA.LESSOR_ID = PSV.VENDOR_ID),' ') LESSOR
,(SELECT NVL(FLEA.DESCRIPTION,' ')
FROM FA_LEASES FLEA
WHERE FAV.LEASE_ID = FLEA.LEASE_ID) LEASE_DESC
,(SELECT NVL(FLVV.MEANING,' ')
FROM FND_LOOKUP_VALUES_VL FLVV
WHERE FAV.NEW_USED = FLVV.LOOKUP_CODE
AND FLVV.LOOKUP_TYPE = 'FA_NEWUSE') NEW_USED
,NVL(FAB.REDUCE_ADJUSTMENT_FLAG,' ') UNIT_ADJ
,NVL(FAB.FULLY_RESERVE_ON_ADD_FLAG,' ') ADD_COST
,NVL(FAV.ATTRIBUTE_CATEGORY_CODE,' ') ATT_CAT
,NVL((SELECT TO_CHAR(FRET.DATE_RETIRED,'MM/DD/YYYY')
FROM FA_RETIREMENTS FRET
WHERE FDH.RETIREMENT_ID = FRET.RETIREMENT_ID
AND FAV.ASSET_ID = FRET.ASSET_ID),' ') DATE_RETIRED
,FAB.DATE_PLACED_IN_SERVICE DATE_PLACED
,(SELECT NVL(FMTHD.METHOD_CODE,' ')
FROM FA_METHODS FMTHD
WHERE FAB.METHOD_ID = FMTHD.METHOD_ID) DEPRN_MTHD
,(SELECT NVL(FMTHD.LIFE_IN_MONTHS,'')
FROM FA_METHODS FMTHD
WHERE FAB.METHOD_ID = FMTHD.METHOD_ID) LIFE_IN_MON
,NVL(FAB.ADJUSTED_COST,0) ADJ_COST
,NVL(FAB.COST,0) COST
,NVL(FAB.ORIGINAL_COST,0) ORIG_COST
,NVL(FAB.REDUCE_RETIREMENT_FLAG,' ') RETIR_PEND
,NVL(FAL.SEGMENT1,' ') SITE_NUM
,NVL(FAL.SEGMENT2,' ') ROOM
,NVL((GCC.SEGMENT1|| '-'|| GCC.SEGMENT2|| '-'|| GCC.SEGMENT3|| '-'|| GCC.SEGMENT4|| '-'|| GCC.SEGMENT5||'-'|| GCC.SEGMENT6|| '-'|| GCC.SEGMENT7
|| '-'|| GCC.SEGMENT8|| '-'|| GCC.SEGMENT9),' ') CONC_SEG
,NVL(FAI.INVOICE_NUMBER,' ') INV_NUM
,NVL(FAI.PO_NUMBER,' ') PO_NUM
,NVL((SELECT NVL(PPAB.SEGMENT1,' ')
FROM PJF_PROJECTS_ALL_B PPAB
WHERE FAI.PROJECT_ID = PPAB.PROJECT_ID),' ') PROJ_NUM
,NVL(FAI.FIXED_ASSETS_COST,0) FA_COST
,NVL(FAI.PAYABLES_COST,0) PAY_COST
,NVL(FAI.PAYABLES_BATCH_NAME,' ') PAY_BATCH
,NVL(GCC.SEGMENT4,' ') LOC
,(SELECT NVL(FVVL.DESCRIPTION,' ')
FROM FND_VS_VALUES_VL FVVL
,FND_VS_VALUE_SETS FVVS
WHERE GCC.SEGMENT4 = FVVL.VALUE
AND FVVL.VALUE_SET_ID = FVVS.VALUE_SET_ID
AND FVVS.VALUE_SET_CODE = 'Location Segment') LOC_NAME
,NVL(GCC.SEGMENT1,' ') FUND
,NVL(GCC.SEGMENT2,' ') DEPT
,NVL(GCC.SEGMENT5,' ') CAT
,NVL(GCC.SEGMENT6,' ') ACC
,NVL(FACV.SEGMENT1,' ') MAJOR
,NVL(FACV.SEGMENT2,' ') MINOR
,FAV.ASSET_ID ASSET_ID
FROM FA_ADDITIONS_VL FAV
,FA_BOOKS FAB
,FA_CATEGORIES_VL FACV
,FA_DISTRIBUTION_HISTORY FDH
,FA_LOCATIONS FAL
,GL_CODE_COMBINATIONS GCC
,FA_ASSET_INVOICES FAI
WHERE FAV.ASSET_ID = FAB.ASSET_ID
AND FAB.TRANSACTION_HEADER_ID_OUT IS NULL
AND FAV.ASSET_CATEGORY_ID = FACV.CATEGORY_ID
AND FAV.ASSET_ID = FDH.ASSET_ID
AND FAB.BOOK_TYPE_CODE = FDH.BOOK_TYPE_CODE
AND FDH.TRANSACTION_HEADER_ID_OUT IS NULL
AND FDH.LOCATION_ID = FAL.LOCATION_ID
AND FDH.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND FAV.ASSET_ID = FAI.ASSET_ID(+)
) ASSET_DETAIL,
(SELECT FAV.ASSET_ID ASSET_ID
,FAV.ASSET_NUMBER ASSET_NUMBER
--Cost Details Report
,NVL(COST_ADDITION.ADJUSTMENT_AMOUNT,0) COSTADDITION
,NVL(COST_ADJUSTMENT.ADJUSTMENT_AMOUNT,0) COSTADJUSTMENT
,NVL(COST_REVALUATION.ADJUSTMENT_AMOUNT,0) COSTREVALUATION
,NVL(COST_RETIREMENT.ADJUSTMENT_AMOUNT,0) COSTRETIREMENT
,NVL(COST_RECLASS.ADJUSTMENT_AMOUNT,0) COSTRECLASS
,NVL(COST_TRANSFER.ADJUSTMENT_AMOUNT,0) COSTTRANSFERR
,(NVL(COST_ADDITION.ADJUSTMENT_AMOUNT,0) + NVL(COST_ADJUSTMENT.ADJUSTMENT_AMOUNT,0) + NVL(COST_REVALUATION.ADJUSTMENT_AMOUNT,0) + NVL(COST_RETIREMENT.ADJUSTMENT_AMOUNT,0) + NVL(COST_RECLASS.ADJUSTMENT_AMOUNT,0) + NVL(COST_TRANSFER.ADJUSTMENT_AMOUNT,0)) COSTENDBAL
,(SELECT DISTINCT GCC.SEGMENT1|| '-'|| GCC.SEGMENT2|| '-'|| GCC.SEGMENT3|| '-'|| GCC.SEGMENT4|| '-'|| GCC.SEGMENT5||'-'|| GCC.SEGMENT6|| '-'|| GCC.SEGMENT7|| '-'|| GCC.SEGMENT8|| '-'|| GCC.SEGMENT9
FROM GL_CODE_COMBINATIONS GCC
,XLA_AE_LINES XAL
,XLA_AE_HEADERS XAH
,FA_TRANSACTION_HEADERS FTH
WHERE GCC.CODE_COMBINATION_ID = XAL.CODE_COMBINATION_ID
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND FAV.ASSET_ID = FTH.ASSET_ID
AND FAB.TRANSACTION_HEADER_ID_IN = FTH.TRANSACTION_HEADER_ID
AND XAH.EVENT_ID = FTH.EVENT_ID
AND GCC.ACCOUNT_TYPE = 'A'
AND XAL.ACCOUNTING_CLASS_CODE = 'COST'
AND XAL.ENTERED_DR IS NOT NULL) COST_ACC
--Reserve Details Report
,NVL((SELECT NVL(FDD.DEPRN_RESERVE,0)
FROM FA_DEPRN_DETAIL FDD
WHERE FAV.ASSET_ID = FDD.ASSET_ID
AND DEPRN_SOURCE_CODE = 'B'
AND FDH.DISTRIBUTION_ID = FDD.DISTRIBUTION_ID),0) RESERVEADDITION
,NVL(RESERVE_ADJUSTMENT.ADJUSTMENT_AMOUNT,0) RESERVEADJUSTMENT
,NVL((SELECT NVL(SUM(FDD.DEPRN_AMOUNT),0)
FROM FA_DEPRN_DETAIL FDD
--,FA_DEPRN_PERIODS FDP
WHERE FAV.ASSET_ID = FDD.ASSET_ID
AND FAB.BOOK_TYPE_CODE = FDD.BOOK_TYPE_CODE
AND FDH.DISTRIBUTION_ID = FDD.DISTRIBUTION_ID)
,0) RESERVEDEPRN
--AND FDD.PERIOD_COUNTER = FDP.PERIOD_COUNTER
--AND FAB.BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE
--AND FDP.FISCAL_YEAR = (SELECT TO_CHAR(SYSDATE,'RRRR') FROM DUAL)
--AND FDP.DEPRN_RUN = 'Y')
,NVL(RESERVE_RETIREMENT.ADJUSTMENT_AMOUNT,0) RESERVERETIREMENT
,NVL(RESERVE_RECLASS.ADJUSTMENT_AMOUNT,0) RESERVERECLASS
,NVL(RESERVE_TRANSFER.ADJUSTMENT_AMOUNT,0) RESERVETRANSFER
,(NVL((SELECT NVL(FDD.DEPRN_RESERVE,0)
FROM FA_DEPRN_DETAIL FDD
WHERE FAV.ASSET_ID = FDD.ASSET_ID
AND DEPRN_SOURCE_CODE = 'B'
AND FDH.DISTRIBUTION_ID = FDD.DISTRIBUTION_ID),0) + NVL(RESERVE_ADJUSTMENT.ADJUSTMENT_AMOUNT,0) + NVL(RESERVE_RECLASS.ADJUSTMENT_AMOUNT,0)
+ NVL((SELECT NVL(SUM(FDD.DEPRN_AMOUNT),0)
FROM FA_DEPRN_DETAIL FDD
WHERE FAV.ASSET_ID = FDD.ASSET_ID
AND FAB.BOOK_TYPE_CODE = FDD.BOOK_TYPE_CODE
AND FDH.DISTRIBUTION_ID = FDD.DISTRIBUTION_ID)
,0) + NVL(RESERVE_RETIREMENT.ADJUSTMENT_AMOUNT,0) + NVL(RESERVE_TRANSFER.ADJUSTMENT_AMOUNT,0)) RESERVEENDBAL
,NVL((SELECT DISTINCT GCC.SEGMENT1|| '-'|| GCC.SEGMENT2|| '-'|| GCC.SEGMENT3|| '-'|| GCC.SEGMENT4|| '-'|| GCC.SEGMENT5||'-'|| GCC.SEGMENT6|| '-'|| GCC. SEGMENT7|| '-'|| GCC.SEGMENT8|| '-'|| GCC.SEGMENT9
FROM GL_CODE_COMBINATIONS GCC
,XLA_AE_LINES XAL
,XLA_AE_HEADERS XAH
,FA_TRANSACTION_HEADERS FTH
WHERE GCC.CODE_COMBINATION_ID = XAL.CODE_COMBINATION_ID
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND FAV.ASSET_ID = FTH.ASSET_ID
AND FAB.TRANSACTION_HEADER_ID_IN = FTH.TRANSACTION_HEADER_ID
AND XAH.EVENT_ID = FTH.EVENT_ID
AND GCC.ACCOUNT_TYPE = 'A'
AND XAL.ACCOUNTING_CLASS_CODE = 'DEPRECIATION_RESERVE'
AND XAL.ENTERED_DR IS NOT NULL),' ') DEPRN_ACC
FROM FA_ADDITIONS_VL FAV
,FA_BOOKS FAB
,FA_DISTRIBUTION_HISTORY FDH
,(SELECT *
FROM FA_ADJUSTMENTS
WHERE SOURCE_TYPE_CODE = 'ADDITION'
AND ADJUSTMENT_TYPE = 'COST') COST_ADDITION
,(SELECT *
FROM FA_ADJUSTMENTS
WHERE SOURCE_TYPE_CODE = 'ADJUSTMENT'
AND ADJUSTMENT_TYPE = 'COST') COST_ADJUSTMENT
,(SELECT *
FROM FA_ADJUSTMENTS
WHERE SOURCE_TYPE_CODE = 'REVALUATION'
AND ADJUSTMENT_TYPE = 'COST') COST_REVALUATION
,(SELECT *
FROM FA_ADJUSTMENTS
WHERE SOURCE_TYPE_CODE = 'RETIREMENT'
AND ADJUSTMENT_TYPE = 'COST') COST_RETIREMENT
,(SELECT *
FROM FA_ADJUSTMENTS
WHERE SOURCE_TYPE_CODE = 'RECLASS'
AND ADJUSTMENT_TYPE = 'COST') COST_RECLASS
,(SELECT *
FROM FA_ADJUSTMENTS
WHERE SOURCE_TYPE_CODE = 'TRANSFER'
AND ADJUSTMENT_TYPE = 'COST') COST_TRANSFER
,(SELECT *
FROM FA_ADJUSTMENTS
WHERE SOURCE_TYPE_CODE = 'ADJUSTMENT'
AND ADJUSTMENT_TYPE = 'RESERVE') RESERVE_ADJUSTMENT
,(SELECT *
FROM FA_ADJUSTMENTS
WHERE SOURCE_TYPE_CODE = 'RETIREMENT'
AND ADJUSTMENT_TYPE = 'RESERVE') RESERVE_RETIREMENT
,(SELECT *
FROM FA_ADJUSTMENTS
WHERE SOURCE_TYPE_CODE = 'RECLASS'
AND ADJUSTMENT_TYPE = 'RESERVE') RESERVE_RECLASS
,(SELECT *
FROM FA_ADJUSTMENTS
WHERE SOURCE_TYPE_CODE = 'TRANSFER'
AND ADJUSTMENT_TYPE = 'RESERVE') RESERVE_TRANSFER
WHERE FAV.ASSET_ID = COST_ADDITION.ASSET_ID(+)
AND FAV.ASSET_ID = COST_ADJUSTMENT.ASSET_ID(+)
AND FAV.ASSET_ID = COST_REVALUATION.ASSET_ID(+)
AND FAV.ASSET_ID = COST_RETIREMENT.ASSET_ID(+)
AND FAV.ASSET_ID = COST_RECLASS.ASSET_ID(+)
AND FAV.ASSET_ID = COST_TRANSFER.ASSET_ID(+)
AND FAV.ASSET_ID = RESERVE_ADJUSTMENT.ASSET_ID(+)
AND FAV.ASSET_ID = RESERVE_RETIREMENT.ASSET_ID(+)
AND FAV.ASSET_ID = RESERVE_RECLASS.ASSET_ID(+)
AND FAV.ASSET_ID = RESERVE_TRANSFER.ASSET_ID(+)
AND FAV.ASSET_ID = FAB.ASSET_ID
AND FAB.TRANSACTION_HEADER_ID_OUT IS NULL
AND FAV.ASSET_ID = FDH.ASSET_ID
AND FAB.BOOK_TYPE_CODE = FDH.BOOK_TYPE_CODE
AND FDH.TRANSACTION_HEADER_ID_OUT IS NULL
AND FAB.TRANSACTION_HEADER_ID_IN = COST_ADJUSTMENT.TRANSACTION_HEADER_ID(+)
AND FAB.TRANSACTION_HEADER_ID_IN = COST_REVALUATION.TRANSACTION_HEADER_ID(+)
AND FAB.TRANSACTION_HEADER_ID_IN = COST_RETIREMENT.TRANSACTION_HEADER_ID(+)
AND FAB.TRANSACTION_HEADER_ID_IN = COST_RECLASS.TRANSACTION_HEADER_ID(+)
AND FAB.TRANSACTION_HEADER_ID_IN = COST_TRANSFER.TRANSACTION_HEADER_ID(+)
AND FAB.TRANSACTION_HEADER_ID_IN = RESERVE_ADJUSTMENT.TRANSACTION_HEADER_ID(+)
AND FAB.TRANSACTION_HEADER_ID_IN = RESERVE_RETIREMENT.TRANSACTION_HEADER_ID(+)
AND FAB.TRANSACTION_HEADER_ID_IN = RESERVE_RECLASS.TRANSACTION_HEADER_ID(+)
AND FAB.TRANSACTION_HEADER_ID_IN = RESERVE_TRANSFER.TRANSACTION_HEADER_ID(+)
AND FDH.DISTRIBUTION_ID = COST_ADJUSTMENT.DISTRIBUTION_ID(+)
AND FDH.DISTRIBUTION_ID = COST_REVALUATION.DISTRIBUTION_ID(+)
AND FDH.DISTRIBUTION_ID = COST_RETIREMENT.DISTRIBUTION_ID(+)
AND FDH.DISTRIBUTION_ID = COST_RECLASS.DISTRIBUTION_ID(+)
AND FDH.DISTRIBUTION_ID = COST_TRANSFER.DISTRIBUTION_ID(+)
AND FDH.DISTRIBUTION_ID = RESERVE_ADJUSTMENT.DISTRIBUTION_ID(+)
AND FDH.DISTRIBUTION_ID = RESERVE_RETIREMENT.DISTRIBUTION_ID(+)
AND FDH.DISTRIBUTION_ID = RESERVE_RECLASS.DISTRIBUTION_ID(+)
AND FDH.DISTRIBUTION_ID = RESERVE_TRANSFER.DISTRIBUTION_ID(+)
) RESERVE_COST
WHERE ASSET_DETAIL.ASSET_ID = RESERVE_COST.ASSET_ID
AND ASSET_DETAIL.DATE_PLACED BETWEEN NVL(:P_DATE_PLC_IN_SVC_GREAT,ASSET_DETAIL.DATE_PLACED) AND NVL(:P_DATE_PLC_IN_SVC_LESS,ASSET_DETAIL.DATE_PLACED)
AND ASSET_DETAIL.ASSET_DESC IN NVL(:P_ASSET_DESC,ASSET_DETAIL.ASSET_DESC)
AND ASSET_DETAIL.SITE_NUM IN NVL(:P_LOC,ASSET_DETAIL.SITE_NUM)
AND ASSET_DETAIL.ASSET_NUM IN NVL(:P_ASSET_NUM,ASSET_DETAIL.ASSET_NUM)
AND ASSET_DETAIL.MINOR IN NVL(:P_MINOR,ASSET_DETAIL.MINOR)
AND ASSET_DETAIL.MAJOR IN NVL(:P_MAJOR,ASSET_DETAIL.MAJOR)
AND ASSET_DETAIL.TAG_NUM IN NVL(:P_TAG_NUM,ASSET_DETAIL.TAG_NUM)