Scribd
Upload
476 views

Extraction Query On Fusion

This document contains two SQL queries. The first query selects item details like the inventory item ID, item number, description, unit of measure, and other attributes from various inventory tables where the item number equals 'M660200350100'. The second query selects asset details like the asset number, description, category, book type, and depreciation cost from financial asset tables where the asset number equals 150.

Uploaded by

Siddiq Mohammed
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
476 views

Extraction Query On Fusion

This document contains two SQL queries. The first query selects item details like the inventory item ID, item number, description, unit of measure, and other attributes from various inventory tables where the item number equals 'M660200350100'. The second query selects asset details like the asset number, description, category, book type, and depreciation cost from financial asset tables where the asset number equals 150.

Uploaded by

Siddiq Mohammed
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 3

Extraction Query on Item Details

select

esi.INVENTORY_ITEM_ID

,esi.ITEM_NUMBER

,esi.organization_id

,iop.organization_code

,esi.description

,UOMT.UNIT_OF_MEASURE

,esi.item_type

,esi.ENABLED_FLAG

,esi.planner_code planner

,esi.SAFETY_STOCK_PLANNING_METHOD

from

INV_ORG_PARAMETERS iop

,EGP_SYSTEM_ITEMS esi

,INV_UNITS_OF_MEASURE_TL UOMT

,INV_UNITS_OF_MEASURE_B UOMB

where

esi.ITEM_NUMBER='M660200350100'

and iop.organization_id=esi.organization_id

and UOMB.UOM_CODE = esi.PRIMARY_UOM_CODE

and UOMT.UNIT_OF_MEASURE_ID (+)= UOMB.UNIT_OF_MEASURE_ID

and UOMT.language='US'
Extraction Query on Asset Details :

select asset_number

,fab.asset_id

,fat.description

,fab.asset_type

,fcb.segment1||'-'||fcb.segment2 category

,FAB.TAG_NUMBER

,fb.BOOK_TYPE_CODE

,fabc.book_class

,fb.cost

,fb.RECOVERABLE_COST

,(SELECT

SUM(DEPRN_RESERVE)

FROM

FA_DEPRN_DETAIL

WHERE

ASSET_ID = FAB.ASSET_ID

AND DEPRN_RUN_DATE = (

SELECT

MAX(DEPRN_RUN_DATE)

FROM

FA_DEPRN_DETAIL

WHERE

ASSET_ID = FAB.ASSET_ID) Depriciation_Cost

from FA_ADDITIONS_B FAB

,FA_ADDITIONS_TL FAT
,fa_categories_b fcb

,fa_books fb

,FA_BOOK_CONTROLS FABC

where 1=1

and fab.asset_number=150

AND FAB.ASSET_ID = FAT.ASSET_ID

AND FAT.LANGUAGE = USERENV('LANG')

and fcb.CATEGORY_ID=fab.ASSET_CATEGORY_ID

and fb.asset_id=fab.asset_id

and fb.DEPRECIATE_FLAG='NO'

You might also like