[SSRS] 利用Reporting Service開發Oracle ERP 報表的注意事項

1.若是與Sharepoint整合，Server端的Oracle Client安裝目錄記得給予IIS user讀取權限，例如ASP.NET帳戶。 否則會一直告訴你Oracle Client版本要8.1.7以上。

2.若需要用到含OU的View(含有USERENV('CLIENT_INFO'))，登入檔的NLS_LANG記得把TRADITIONAL CHINESE_TAIWAN.ZHT16BIG5改掉，一般我都習慣改成 AMERICAN_AMERICA.ZHT16BIG5。

Report裡需要Calll DBMS_APPLICATION_INFO.SET_CLIENT_INFO 這個Procedure，個人習慣把他放在DataSet裡執行，因為也找不到其他方法，除非放到SSIS執行，那又很麻煩。

例如我們若需要對OE_ORDER_HEADERS_V這的View做查詢，則我們第一個DataSet放入一個Procedure，執行DBMS_APPLICATION_INFO.SET_CLIENT_INFO，第二個DataSet再放入我們要的SQL，操作如下：

a.使用單一交易要勾選

b.新增一個DataSet，執行DBMS_APPLICATION_INFO.SET_CLIENT_INFO

c.參數預設值記得設定

d.新增報表所需要的資料SQL

e.設計報表

f.執行測試預覽，成功。

[Cost] MTA - ACCOUNTING_LINE_TYPE

ACCOUNTING_LINE_TYPE

1 Inv valuation
2 Account
3 Overhead absorption
4 Resource absorption
5 Receiving Inspection
6 Purchase price variance or rate variance
7 WIP valuation
8 WIP variance
9 Inter-org payables
10 Inter-org receivables
11 Inter-org transfer credit
12 Inter-org freight charge
13 Average cost variance
14 Intransit Inventory
15 Encumbrance Reversal
99 Unknown

範例

SELECT MTT.TRANSACTION_TYPE_NAME,
       MMT.TRANSACTION_DATE,
       GCC.SEGMENT1||','||GCC.SEGMENT2||','||GCC.SEGMENT3||','||GCC.SEGMENT4 ACCT,
       ROUND(SUM(NVL(MTA.BASE_TRANSACTION_VALUE,0)), 1) NET_ACTIVITY,
       ROUND(SUM(DECODE( NVL(MTA.BASE_TRANSACTION_VALUE,0) /
                         ABS(DECODE(
                                    NVL(MTA.BASE_TRANSACTION_VALUE,0),
                                    0, 1, NVL( MTA.BASE_TRANSACTION_VALUE,0  )  )), 
                  1, NVL(MTA.BASE_TRANSACTION_VALUE,0), 0 )  ), 1) SUM_DEBITS,
       ROUND(SUM(DECODE( NVL(MTA.BASE_TRANSACTION_VALUE,0) /
                         ABS(DECODE(
                                     NVL(MTA.BASE_TRANSACTION_VALUE,0),
                                     0, 1, NVL( MTA.BASE_TRANSACTION_VALUE,0  )  )), 
                 -1, NVL(ABS(MTA.BASE_TRANSACTION_VALUE),0), 0 )  ), 1) SUM_CREDITS
  FROM MTL_MATERIAL_TRANSACTIONS MMT,    
       --MTL_SYSTEM_ITEMS MSI,
       GL_CODE_COMBINATIONS GCC,
       MTL_TRANSACTION_ACCOUNTS MTA,
       MTL_TRANSACTION_TYPES MTT,
       ORG_GL_BATCHES OGB
WHERE MTA.TRANSACTION_ID = MMT.TRANSACTION_ID
   AND MMT.TRANSACTION_TYPE_ID = MTT.TRANSACTION_TYPE_ID
   --AND    MTA.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
   --AND    MSI.ORGANIZATION_ID  =  MMT.ORGANIZATION_ID
   AND MTA.REFERENCE_ACCOUNT = GCC.CODE_COMBINATION_ID
   AND MMT.ORGANIZATION_ID = MTA.ORGANIZATION_ID
   AND MTA.ORGANIZATION_ID = :P_ORG_ID
   AND MTA.ACCOUNTING_LINE_TYPE <> 15
   AND MTA.TRANSACTION_DATE BETWEEN ...
   AND OGB.GL_BATCH_ID (+) = MTA.GL_BATCH_ID
GROUP BY MTT.TRANSACTION_TYPE_NAME,
       MMT.TRANSACTION_DATE,
       GCC.SEGMENT1||','||GCC.SEGMENT2||','||GCC.SEGMENT3||','||GCC.SEGMENT4