FA_SQL(抓固定資產相關信息)

SELECT dept.DEPARTMENT_CNAME,
       gcc.segment2 dept,
       fdh.ASSIGNED_TO_NAME full_name,
       fl.segment1 || '.' || fl.segment2,
       fa.ASSET_NUMBER,
       fa.description,
       fdh.UNITS_ASSIGNED,
       fb.date_placed_in_service,
       fdh.BOOK_TYPE_CODE,
       fai.invoice_number,
       fa.ATTRIBUTE_CATEGORY_CODE,
       fa.SERIAL_NUMBER,
       fa.TAG_NUMBER,
       fa.ASSET_TYPE,
       fa.INVENTORIAL,
       pv.vendor_name,
      
       fdh.assigned_to_number employee_num,
      
       fdh.LAST_UPDATE_DATE,
       fdh.LAST_UPDATED_BY,
       hr.status_name
  FROM FA_BOOKS                                       FB,
       fa_additions                                   fa,
       fa_locations                                   fl,
   
       FA_ASSET_INVOICES                              fai,
       po_vendors                                     pv,
       FA_DISTRIBUTION_HISTORY_V                      fdh,
       GL_CODE_COMBINATIONS_KFV                       GCC,
       guid.interface_employee@linkwebdb.askey.com.tw HR, --客製
       GUID.GUID_DEPARTMENT@linkwebdb.askey.com.tw    DEPT--客製
 WHERE fa.asset_id = fb.asset_id
   AND fb.asset_id = fdh.asset_id
   AND fdh.ASSIGNED_TO_NUMBER = hr.EMPNO
   AND HR.DEPTNO = DEPT.DEPARTMENT_ID
   AND FDH.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
   AND FDH.TRANSACTION_HEADER_ID_OUT IS NULL
   AND fb.TRANSACTION_HEADER_ID_OUT IS NULL
   AND FB.DATE_INEFFECTIVE IS NULL
   AND FB.PERIOD_COUNTER_FULLY_RETIRED IS NULL
      --and fdh.ASSIGNED_TO=4122 
      --AND FDH.ASSIGNED_TO_NAME  like '%張晶%'
      --AND hr.status_name='離職'
   AND hr.site = 'WJ'
   AND FDH.BOOK_TYPE_CODE = '江蘇固定資產'
   AND fdh.LOCATION_ID = fl.location_id

 
   AND fa.ASSET_ID = fai.asset_id
   AND fai.po_vendor_id = pv.vendor_id

转载于:https://www.cnblogs.com/aimin/archive/2011/05/19/2051289.html