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