Oracle ERP 過濾 Alert SQL Statement

在 Oracle ERP 中,

若要過濾 Alert 的 SQL Statement,

找出自己所需要的 Alerts,

可以參考以下語法 :

 程式碼

declare
  v_condition_value varchar2(100) := '&Condition';
  
  cursor cur_data is
  select ALERT_NAME
       , SQL_STATEMENT_TEXT
    from ALR_ALERTS;
begin
  for rec_data in cur_data loop
      if rec_data.SQL_STATEMENT_TEXT like '%'|| v_condition_value ||'%' then
         dbms_output.put_line( rec_data.ALERT_NAME );
      end if;
  end loop;
end;

昭佑.天翔

取得某個 Mail Address 所在的 Oracle ERP Alert

在 Oracle ERP Alert 中,

若想要知道在哪些 Alert 中,

有設定要發送給某個 Mail Address,

可以參考下面的語法 :

 語法

SELECT AA.ALERT_NAME
     , AA.DESCRIPTION
     , AAA.ACTION_ID
     , AAA.NAME           ACTION_NAME
     , AAA.SUBJECT 
     , AAA.TO_RECIPIENTS
     , AAA.CC_RECIPIENTS
     , AAA.BCC_RECIPIENTS 
  FROM ALR_ACTIONS AAA
     , ALR_ALERTS  AA 
 WHERE 1 = 1
   AND AA.APPLICATION_ID = AAA.APPLICATION_ID
   AND AA.ALERT_ID = AAA.ALERT_ID
   AND ( UPPER( AAA.TO_RECIPIENTS) LIKE UPPER('%&Mail_Address%') 
      or UPPER( AAA.CC_RECIPIENTS) LIKE UPPER('%&Mail_Address%') 
      or UPPER( AAA.BCC_RECIPIENTS) LIKE UPPER('%&Mail_Address%') );

Oracle ERP 線上人員在某個 Responsibility 中開啟哪些 Form

在 Oracle ERP 中, 可用下面語法得知 "線上人員在某個 Responsibility 中開啟哪些 Form" :

 程式碼

SELECT FF.USER_FORM_NAME
     , A.TIME
  FROM (SELECT FORM_ID
             , TIME
          FROM FND_SIGNON_AUDIT_VIEW
         WHERE RESPONSIBILITY_ID = &RESPONSIBILITY_ID
           AND USER_ID = &USER_ID
       ) A
     , FND_FORM_TL FF
 WHERE A.FORM_ID = FF.FORM_ID(+)
 ORDER BY FF.USER_FORM_NAME;

Oracle ERP 各廠的線上人員與其開啟的 Responsibility

在 Oracle ERP 中, 可用下面語法得知 "各廠的線上人員與其開啟的 Responsibility" :

 程式碼

SELECT A.USER_ID
     , A.USER_NAME
     , FU.DESCRIPTION  USER_DESC
     , FR.RESPONSIBILITY_ID
     , FR.RESPONSIBILITY_NAME
     , COUNT(*) FORM_COUNT
  FROM (SELECT RESPONSIBILITY_ID
             , USER_ID
             , USER_NAME
          FROM FND_SIGNON_AUDIT_VIEW
       ) A
     , ORG_ACCESS            OA
     , FND_RESPONSIBILITY_TL FR
     , FND_USER              FU
 WHERE A.USER_ID = FU.USER_ID
   AND A.RESPONSIBILITY_ID = OA.RESPONSIBILITY_ID(+)
   AND A.RESPONSIBILITY_ID = FR.RESPONSIBILITY_ID(+)
   AND OA.ORGANIZATION_ID = &ORGANIZATION_ID
 GROUP BY A.USER_NAME, A.USER_ID, FU.DESCRIPTION, FR.RESPONSIBILITY_ID, FR.RESPONSIBILITY_NAME
 ORDER BY A.USER_NAME, A.USER_ID, FU.DESCRIPTION, FR.RESPONSIBILITY_ID, FR.RESPONSIBILITY_NAME;

Oracle ERP 各廠的線上人數

在 Oracle ERP 中, 可用下面語法得知 "各廠的線上人數" :

 程式碼

SELECT HOU.ORGANIZATION_ID
     , HOU.NAME ORGANIZATION_NAME
     , COUNT(*) ONLINE_NUM
  FROM (SELECT DISTINCT
               RESPONSIBILITY_ID
             , USER_ID
          FROM FND_SIGNON_AUDIT_VIEW
       ) A
     , ORG_ACCESS OA
     , HR_ALL_ORGANIZATION_UNITS_TL HOU
 WHERE A.RESPONSIBILITY_ID = OA.RESPONSIBILITY_ID(+)
   AND OA.ORGANIZATION_ID = HOU.ORGANIZATION_ID(+)
 GROUP BY HOU.NAME, HOU.ORGANIZATION_ID;

取得 Oracle ERP Apps 版本與 Workflow 版本

取得 Oracle ERP Apps 版本

 程式碼

select RELEASE_NAME
  from FND_PRODUCT_GROUPS;

取得 Oracle ERP Workflow 版本

 程式碼

select TEXT
  from WF_RESOURCES
 where NAME = 'WF_VERSION';

Oracle ERP 在某段時間內執行的 Alert - 查詢 SQL 語法

在 Oracle ERP 中, 可利用 ALR_ALERTS, 來取得 "在某段時間內執行的 Alert",

其中, CHECK_START_TIME / SECONDS_BETWEEN_CHECKS / CHECK_END_TIME 的欄位值, 都是以 "秒" 為單位,

語法, 如下 :

 語法: 早上 6 ~ 8 點執行的 Alert

select ALERT_NAME
     , ALERT_CONDITION_TYPE                    "TYPE"
     , round( CHECK_START_TIME / 3600, 2 )     "START_TIME (H)"
     , round( SECONDS_BETWEEN_CHECKS / 60, 2 ) "BETWEEN (M)"
     , round( CHECK_END_TIME / 3600, 2 )       "END_TIME (H)"
  from ALR_ALERTS
 where ENABLED_FLAG = 'Y'
   and (CHECK_START_TIME / 3600) >= 6
   and (CHECK_START_TIME / 3600) <= 8;

取得某個 USER 的所有 Supervisor

在 Oracle ERP 中, 如何取得某個 User, 他的 "所有 Employee Supervisor" 是誰, 這些 Supervisor 對應的 User Name 是什麼 ?

可以利用 Oracle Treeview 語法得知.

語法, 如下 :

 語法

SELECT FU.USER_NAME
  FROM FND_USER FU
     , (SELECT SUPERVISOR_ID
          FROM PER_ALL_ASSIGNMENTS_F
         WHERE SUPERVISOR_ID IS NOT NULL
         START WITH PERSON_ID = (SELECT EMPLOYEE_ID
                                   FROM FND_USER
                                  WHERE USER_NAME = UPPER('&USER_NAME')
                                )
        CONNECT BY PRIOR SUPERVISOR_ID = PERSON_ID
       ) EMP
 WHERE FU.EMPLOYEE_ID = EMP.SUPERVISOR_ID;

"某料號" 在 "某帳本期間" 的 "在製成本, 工單成本更新"

在 Oracle ERP 中, 可利用 WIP_TRANSACTIONS 與 WIP_TRANSACTION_ACCOUNTS, 來取得 "某料號" 在 "某帳本期間" 的 "在製成本, 工單成本更新",

語法, 如下 :

 語法

SELECT WDJ.ORGANIZATION_ID
     , WDJ.WIP_ENTITY_ID
     , WE.WIP_ENTITY_NAME
     , WDJ.PRIMARY_ITEM_ID ASSEMBLY_ITEM_ID
     , MSI.SEGMENT1        ASSEMBLY_ITEM_CODE
     , WT.TRANSACTION_TYPE
     , SUM(DECODE( WT.TRANSACTION_TYPE, 5, WTA.BASE_TRANSACTION_VALUE
                                      , 6, WTA.BASE_TRANSACTION_VALUE
                                      )) WIP_VAR          -- 在本期關工單的差異金額
     , SUM(DECODE( WT.TRANSACTION_TYPE, 4, WTA.BASE_TRANSACTION_VALUE
                                      )) WIP_COST_UPDATE  -- 工單成本更新的金額
     , SUM(DECODE( WT.TRANSACTION_TYPE, 1, WTA.BASE_TRANSACTION_VALUE
                                      , 2, WTA.BASE_TRANSACTION_VALUE
                                      , 3, WTA.BASE_TRANSACTION_VALUE
                                      )) PERIOD_WIP_AMT   -- 本期工單製程投入的金額匯總
  FROM WIP_TRANSACTION_ACCOUNTS WTA
     , WIP_TRANSACTIONS         WT
     , WIP_DISCRETE_JOBS        WDJ
     , WIP_ENTITIES             WE
     , MTL_SYSTEM_ITEMS_B       MSI
 WHERE WE.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
   AND WE.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
   AND WDJ.ORGANIZATION_ID = MSI.ORGANIZATION_ID
   AND WDJ.PRIMARY_ITEM_ID = MSI.INVENTORY_ITEM_ID
   AND WDJ.WIP_ENTITY_ID = WT.WIP_ENTITY_ID
   AND WT.TRANSACTION_ID = WTA.TRANSACTION_ID
   AND WTA.ACCOUNTING_LINE_TYPE = 7
   AND WT.ORGANIZATION_ID = &ORGANIZATION_ID
   AND WE.WIP_ENTITY_NAME = '&JOB_NAME'
   AND WT.ACCT_PERIOD_ID IN (SELECT ACCT_PERIOD_ID FROM ORG_ACCT_PERIODS WHERE PERIOD_NAME = '&PERIOD_NAME' AND ORGANIZATION_ID = &ORGANIZTION_ID) -- 以 PERIOD 做條件
   --AND WTA.TRANSACTION_DATE BETWEEN TO_DATE('&P_START_DATE', 'YYYYMMDD') AND TO_DATE('&END_DATE 23:59:59','YYYYMMDD HH24:MI:SS')                 -- 日期區間做條件
 GROUP BY WDJ.ORGANIZATION_ID
        , WDJ.WIP_ENTITY_ID
        , WE.WIP_ENTITY_NAME
        , WDJ.PRIMARY_ITEM_ID
        , MSI.SEGMENT1
        , WT.TRANSACTION_TYPE;

物料在 "某個日期以前" 的交易量

在 Oracle ERP 中, 要計算某物料在某個日期以前的交易量, 有兩種方式 :

方式 1 : (反推法 : QOH - 某個日期以後的交易量) (速度快很多)

 語法

SELECT A.QTY - B.QTY
  FROM (SELECT NVL(SUM(MOQ.TRANSACTION_QUANTITY), 0) QTY
          FROM MTL_ONHAND_QUANTITIES_DETAIL MOQ
             , MTL_SECONDARY_INVENTORIES    MSI
         WHERE MOQ.ORGANIZATION_ID = MSI.ORGANIZATION_ID
           AND MOQ.SUBINVENTORY_CODE = MSI.SECONDARY_INVENTORY_NAME
           AND MOQ.ORGANIZATION_ID = &ORGANIZATION_ID
           AND MOQ.INVENTORY_ITEM_ID = &INVENTORY_ITEM_ID
       ) A
     , (SELECT NVL(SUM(MMT.PRIMARY_QUANTITY), 0) QTY
          FROM MTL_MATERIAL_TRANSACTIONS MMT
             , MTL_SECONDARY_INVENTORIES MSI
         WHERE MMT.ORGANIZATION_ID = MSI.ORGANIZATION_ID
           AND MMT.SUBINVENTORY_CODE = MSI.SECONDARY_INVENTORY_NAME
           AND MMT.ORGANIZATION_ID = &ORGANIZATION_ID
           AND MMT.INVENTORY_ITEM_ID = &INVENTORY_ITEM_ID
           AND MMT.TRANSACTION_DATE >= TRUNC(TO_DATE('&CHECK_DATE','YYYYMMDD')+1)
       ) B;

方式 2 : (速度會較慢)

 語法

SELECT NVL(SUM(MMT.PRIMARY_QUANTITY), 0) QTY
  FROM MTL_MATERIAL_TRANSACTIONS MMT
     , MTL_SECONDARY_INVENTORIES MSI
 WHERE MMT.ORGANIZATION_ID = MSI.ORGANIZATION_ID
   AND MMT.SUBINVENTORY_CODE = MSI.SECONDARY_INVENTORY_NAME
   AND MMT.ORGANIZATION_ID = &ORGANIZATION_ID
   AND MMT.INVENTORY_ITEM_ID = &INVENTORY_ITEM_ID
   AND MMT.TRANSACTION_DATE < TRUNC(TO_DATE('&CHECK_DATE','YYYYMMDD')+1);

如何抓到 Oracle 目前程式所在的 Host Name

在 Oracle Database or Oracle ERP 中, 要取得目前程式所在的 Host Name, 可以有兩種方式 :

第一種, 由 V$INSTANCE 得知, 如下 :

 語法

SELECT UPPER(HOST_NAME)
  FROM V$INSTANCE;

第二種, 由 System Profile : Site Name 得知, 如下 :

 語法

SELECT FPOV.PROFILE_OPTION_VALUE
  FROM FND_PROFILE_OPTIONS FPO
     , FND_PROFILE_OPTION_VALUES FPOV
 WHERE FPO.PROFILE_OPTION_ID = FPOV.PROFILE_OPTION_ID
   AND FPO.PROFILE_OPTION_NAME = 'SITENAME';

"某料號" 在 "某期間內" 的 "SO 已銷退數量"

在 Oracle ERP 中, 可利用 RCV_TRANSACTIONS, 來取得 ""某料號" 在 "某期間內" 的 "SO 已銷退數量"",

語法, 如下 :

 語法

SELECT RT.TRANSACTION_DATE
     , RT.QUANTITY 
  FROM OE_ORDER_HEADERS_ALL  OOH
     , OE_ORDER_LINES_ALL    OOL
     , RCV_SHIPMENT_LINES    RSL
     , RCV_TRANSACTIONS      RT
 WHERE OOH.HEADER_ID = OOL.HEADER_ID
   AND OOL.LINE_ID = RSL.OE_ORDER_LINE_ID
   AND RSL.SHIPMENT_LINE_ID = RT.SHIPMENT_LINE_ID
   AND OOL.ORG_ID = &ORG_ID
   AND OOL.INVENTORY_ITEM_ID = &INVENTORY_ITEM_ID
   AND OOL.FLOW_STATUS_CODE <> 'CANCELLED'
   AND RSL.QUANTITY_RECEIVED <> 0
   AND RT.TRANSACTION_DATE BETWEEN TO_DATE('&START_DATE','YYYYMMDD') and (TO_DATE('&END_DATE','YYYYMMDD')+ 1)
   AND RT.TRANSACTION_TYPE = 'DELIVER'
   AND RT.SOURCE_DOCUMENT_CODE = 'RMA';

"某料號" 在 "某期間內" 的 "SO 訂單數量"

在 Oracle ERP 中, 如何取得 ""某料號" 在 "某期間內" 的 "SO 訂單數量"",

語法, 如下 :

 語法

SELECT NVL(SUM(OOL.ORDERED_QUANTITY), 0)  ORDERED_QTY
  FROM OE_ORDER_HEADERS_ALL      OOH
     , OE_ORDER_LINES_ALL        OOL
 WHERE OOH.HEADER_ID = OOL.HEADER_ID
   AND OOH.ORG_ID = &ORG_ID
   AND OOL.INVENTORY_ITEM_ID = &INVENTORY_ITEM_ID
   AND OOH.ORDERED_DATE BETWEEN TO_DATE('&START_DATE','YYYYMMDD') and (TO_DATE('&END_DATE','YYYYMMDD')+ 1)
   AND OOL.LINE_CATEGORY_CODE = 'ORDER'
   AND OOL.FLOW_STATUS_CODE <> 'CANCELLED'
   AND OOL.ORDERED_QUANTITY > 0;

"某料號" 在 "某期間內" 的 "SO 出貨數量"

在 Oracle ERP 中, 可以利用 MMT, 來取得 ""某料號" 在 "某期間內" 的 "SO 出貨數量"",

語法, 如下 :

 語法

SELECT NVL(SUM(MMT.PRIMARY_QUANTITY), 0)*-1
  FROM MTL_MATERIAL_TRANSACTIONS MMT
 WHERE MMT.ORGANIZATION_ID = &ORGANIZATION_ID
   AND MMT.INVENTORY_ITEM_ID = &INVENTORY_ITEM_ID
   AND MMT.TRANSACTION_DATE BETWEEN TO_DATE('&START_DATE','YYYYMMDD') and (TO_DATE('&END_DATE','YYYYMMDD')+ 1)
   AND MMT.TRANSACTION_SOURCE_TYPE_ID = 2
   AND MMT.TRANSACTION_ACTION_ID <> 28;

"某料號" 在 "某期間內" 的 "PO 採購數量"

在 Oracle ERP 中, 可以利用 MMT, 來取得 ""某料號" 在 "某期間內" 的 "PO 採購數量"",

語法, 如下 :

 語法

SELECT NVL(SUM(MMT.PRIMARY_QUANTITY), 0)
  FROM MTL_MATERIAL_TRANSACTIONS MMT
 WHERE MMT.ORGANIZATION_ID = &ORGANIZATION_ID
   AND MMT.INVENTORY_ITEM_ID = &INVENTORY_ITEM_ID
   AND MMT.TRANSACTION_DATE BETWEEN TO_DATE('&START_DATE','YYYYMMDD') and (TO_DATE('&END_DATE','YYYYMMDD')+ 1)
   AND MMT.TRANSACTION_SOURCE_TYPE_ID = 1;

"某料號" 在 "某期間內" 對 "各倉庫" 的 "WIP 交易數量"

在 Oracle ERP 中, 可以利用 MMT, 來取得 ""某料號" 在 "某期間內" 對 "各倉庫" 的 "WIP 交易數量"",

語法, 如下 :

 語法

SELECT MSI.SECONDARY_INVENTORY_NAME
     , NVL(SUM(MMT.PRIMARY_QUANTITY), 0)
  FROM MTL_MATERIAL_TRANSACTIONS MMT
     , MTL_SECONDARY_INVENTORIES MSI
 WHERE MMT.ORGANIZATION_ID = MSI.ORGANIZATION_ID
   AND MMT.SUBINVENTORY_CODE = MSI.SECONDARY_INVENTORY_NAME
   AND MMT.ORGANIZATION_ID = &ORGANIZATION_ID
   AND MMT.INVENTORY_ITEM_ID = &INVENTORY_ITEM_ID
   AND MMT.TRANSACTION_DATE BETWEEN TO_DATE('&START_DATE','YYYYMMDD') and (TO_DATE('&END_DATE','YYYYMMDD')+ 1)
   AND MMT.TRANSACTION_SOURCE_TYPE_ID = 5
   AND MMT.TRANSACTION_ACTION_ID = &ACTION_ID -- 1 (領料)
                                              -- 27 (退料)
                                              -- 33 (拆解工單領料)
                                              -- 34 (拆解工單退料)
                                              -- 31 (成品繳庫)
                                              -- 32 (成品退回)
 GROUP BY MSI.SECONDARY_INVENTORY_NAME;

某帳本在某月的人工費用

在 Oracle ERP 中, 如何取得 "某帳本在某月的人工費用",

語法, 如下 :

 語法

SELECT GC.SEGMENT1 "公司別"
     , GC.SEGMENT2 "廠別"
     , GC.SEGMENT3 "會計主目"
     , GC.SEGMENT4 "會計子目"
     , SUM(GB.PERIOD_NET_DR - GB.PERIOD_NET_CR) "金額"                 -- (借方 - 貸方)
  FROM GL_BALANCES GB
     , GL_CODE_COMBINATIONS GC
 WHERE GB.CODE_COMBINATION_ID = GC.CODE_COMBINATION_ID
   AND GB.SET_OF_BOOKS_ID = &SOB                                       -- 帳本
   AND GB.PERIOD_NAME = to_char(to_date('&YYYYMM', 'YYYYMM'),'MON-YY') -- 作帳期間
   AND GB.TRANSLATED_FLAG is null                                      -- 作帳幣別
   AND GC.ACCOUNT_TYPE = 'E'                                           -- 費用類會科
   AND SUBSTRB( GC.SEGMENT3, 1, 2 ) IN ( '62', '63' )                  -- 人工費用
 GROUP BY GC.SEGMENT1
     , GC.SEGMENT2
     , GC.SEGMENT3
     , GC.SEGMENT4
HAVING SUM(GB.PERIOD_NET_DR - GB.PERIOD_NET_CR) <> 0;

某帳本在某月的製造費用

在 Oracle ERP 中, 如何取得 "某帳本在某月的製造費用",

語法, 如下 :

 語法

SELECT GC.SEGMENT1 "公司別"
     , GC.SEGMENT2 "廠別"
     , GC.SEGMENT3 "會計主目"
     , GC.SEGMENT4 "會計子目"
     , SUM(GB.PERIOD_NET_DR - GB.PERIOD_NET_CR) "金額"                 -- (借方 - 貸方)
  FROM GL_BALANCES GB
     , GL_CODE_COMBINATIONS GC
 WHERE GB.CODE_COMBINATION_ID = GC.CODE_COMBINATION_ID
   AND GB.SET_OF_BOOKS_ID = &SOB                                       -- 帳本
   AND GB.PERIOD_NAME = to_char(to_date('&YYYYMM', 'YYYYMM'),'MON-YY') -- 作帳期間
   AND GB.TRANSLATED_FLAG is null                                      -- 作帳幣別
   AND GC.ACCOUNT_TYPE = 'E'                                           -- 費用類會科
   AND SUBSTRB( GC.SEGMENT3, 1, 1 ) = '6'                              -- 製造費用 (不含人工費用)
   AND SUBSTRB( GC.SEGMENT3, 1, 2 ) NOT IN ( '62', '63')
 GROUP BY GC.SEGMENT1
     , GC.SEGMENT2
     , GC.SEGMENT3
     , GC.SEGMENT4
HAVING SUM(GB.PERIOD_NET_DR - GB.PERIOD_NET_CR) <> 0;

某帳本在某月的銷售費用

在 Oracle ERP 中, 如何取得 "某帳本在某月的銷售費用",

語法, 如下 :

 語法

SELECT GC.SEGMENT1 "公司別"
     , GC.SEGMENT2 "廠別"
     , GC.SEGMENT3 "會計主目"
     , GC.SEGMENT4 "會計子目"
     , SUM(GB.PERIOD_NET_DR - GB.PERIOD_NET_CR) "金額"                 -- (借方 - 貸方)
  FROM GL_BALANCES GB
     , GL_CODE_COMBINATIONS GC
 WHERE GB.CODE_COMBINATION_ID = GC.CODE_COMBINATION_ID
   AND GB.SET_OF_BOOKS_ID = &SOB                                       -- 帳本
   AND GB.PERIOD_NAME = to_char(to_date('&YYYYMM', 'YYYYMM'),'MON-YY') -- 作帳期間
   AND GB.TRANSLATED_FLAG is null                                      -- 作帳幣別
   AND GC.ACCOUNT_TYPE = 'E'                                           -- 費用類會科
   AND SUBSTRB( GC.SEGMENT3, 1, 1 ) = '7'                              -- 銷售費用
 GROUP BY GC.SEGMENT1
     , GC.SEGMENT2
     , GC.SEGMENT3
     , GC.SEGMENT4
HAVING SUM(GB.PERIOD_NET_DR - GB.PERIOD_NET_CR) <> 0;

某帳本在某月的銷貨收入

在 Oracle ERP 中, 如何取得 "某帳本在某月的銷貨收入",

語法, 如下 :

 語法

SELECT GC.SEGMENT1 "公司別"
     , GC.SEGMENT2 "廠別"
     , GC.SEGMENT3 "會計主目"
     , GC.SEGMENT4 "會計子目"
     , SUM(GB.PERIOD_NET_CR - GB.PERIOD_NET_DR) "金額"                 -- (貸方 - 借方)
  FROM GL_BALANCES GB
     , GL_CODE_COMBINATIONS GC
 WHERE GB.CODE_COMBINATION_ID = GC.CODE_COMBINATION_ID
   AND GB.SET_OF_BOOKS_ID = &SOB                                       -- 帳本
   AND GB.PERIOD_NAME = to_char(to_date('&YYYYMM', 'YYYYMM'),'MON-YY') -- 作帳期間
   AND GB.TRANSLATED_FLAG is null                                      -- 作帳幣別
   AND GC.ACCOUNT_TYPE = 'R'                                           -- 收入類會科
   AND SUBSTRB( GC.SEGMENT3, 1, 1 ) = '4'                              -- 銷貨收入
 GROUP BY GC.SEGMENT1
        , GC.SEGMENT2
        , GC.SEGMENT3
        , GC.SEGMENT4
HAVING SUM(GB.PERIOD_NET_CR - GB.PERIOD_NET_DR) <> 0;