瑞信问题排查

来自ling
跳转至: 导航搜索
select *
  from tms_tax_form_cell t
 where t.f_cell_sql like '%AFFRIM%'
    or t.f_cell_sql like '%ENTRY%'
    or t.f_cell_sql like '%affrim%'
    or t.f_cell_sql like '%entry%';

select t.table_name, t.num_rows from user_tables t ORDER BY NUM_ROWS DESC;

SELECT NVL(ROUND((SELECT SUM((TE.S_CREDIT_VAL - TE.S_DEBIT_VAL) *
                         TE.EXCHANGE_RATE -
                         (TE.S_CREDIT_VAL - TE.S_DEBIT_VAL) *
                         TE.EXCHANGE_RATE * 0.11 / 1.11)
                FROM TMS_INV_OUTTAX_TRADE_AFFRIM TE
               WHERE TE.TRADE_TYPE_ID IN
                     (SELECT TA.TRADE_CATEGORY_ID
                        FROM TMS_BA_TRADE_AFFIRM TA, TMS_BA_TAX_RULES_L RL
                       WHERE TA.ITEM_ID = RL.ITEM_ID
                         AND TA.ORG_ID = '@org_id'
                         AND TA.IS_ACCOUNT = 'Y'
                         AND TA.TAX_BASE = 'S'
                         AND RL.TAX_ID = '11')
                 AND TO_CHAR(TE.ACCOUNT_DATE, 'yyyy-MM-dd') LIKE
                     '@report_period%') -
             (SELECT NVL(SUM(L.NET_VAL), 0)
                FROM TMS_INV_OUTTAX_PRINT_POOL_L L
               WHERE L.PRINT_POOL_H_ID IN
                     (SELECT H.PRINT_POOL_H_ID
                        FROM TMS_INV_OUTTAX_PRINT_POOL_H H
                       WHERE TO_CHAR(H.OPEN_DATE, 'yyyy-MM-dd') LIKE
                             '@report_period%'
                         AND H.INVOICE_CODE IS NOT NULL
                         AND H.ORG_ID = '@org_id'
                         AND H.INVOICE_TYPE = 1)
                 AND L.TAX_RATE = 11) -
             (SELECT NVL(SUM(L.NET_VAL), 0)
                FROM TMS_INV_OUTTAX_PRINT_POOL_L L
               WHERE L.PRINT_POOL_H_ID IN
                     (SELECT H.PRINT_POOL_H_ID
                        FROM TMS_INV_OUTTAX_PRINT_POOL_H H
                       WHERE TO_CHAR(H.OPEN_DATE, 'yyyy-MM-dd') LIKE
                             '@report_period%'
                         AND H.INVOICE_CODE IS NOT NULL
                         AND H.ORG_ID = '@org_id'
                         AND H.INVOICE_TYPE = 2)
                 AND L.TAX_RATE = 11),
             2),0)
  FROM DUAL


SELECT NVL(ROUND((SELECT SUM((TE.S_CREDIT_VAL - TE.S_DEBIT_VAL) *
                         TE.EXCHANGE_RATE -
                         (TE.S_CREDIT_VAL - TE.S_DEBIT_VAL) *
                         TE.EXCHANGE_RATE * 0.13 / 1.13)
                FROM TMS_INV_OUTTAX_TRADE_AFFRIM TE
               WHERE TE.TRADE_TYPE_ID IN
                     (SELECT TA.TRADE_CATEGORY_ID
                        FROM TMS_BA_TRADE_AFFIRM TA, TMS_BA_TAX_RULES_L RL
                       WHERE TA.ITEM_ID = RL.ITEM_ID
                         AND TA.ORG_ID = '@org_id'
                         AND TA.IS_ACCOUNT = 'Y'
                         AND TA.TAX_BASE = 'S'
                         AND RL.TAX_ID = '13')
                 AND TO_CHAR(TE.ACCOUNT_DATE, 'yyyy-MM-dd') LIKE
                     '@report_period%') -
             (SELECT NVL(SUM(L.NET_VAL), 0)
                FROM TMS_INV_OUTTAX_PRINT_POOL_L L
               WHERE L.PRINT_POOL_H_ID IN
                     (SELECT H.PRINT_POOL_H_ID
                        FROM TMS_INV_OUTTAX_PRINT_POOL_H H
                       WHERE TO_CHAR(H.OPEN_DATE, 'yyyy-MM-dd') LIKE
                             '@report_period%'
                         AND H.INVOICE_CODE IS NOT NULL
                         AND H.ORG_ID  = '@org_id'
                         AND H.INVOICE_TYPE = 1)
                 AND L.TAX_RATE = 13) -
             (SELECT NVL(SUM(L.NET_VAL), 0)
                FROM TMS_INV_OUTTAX_PRINT_POOL_L L
               WHERE L.PRINT_POOL_H_ID IN
                     (SELECT H.PRINT_POOL_H_ID
                        FROM TMS_INV_OUTTAX_PRINT_POOL_H H
                       WHERE TO_CHAR(H.OPEN_DATE, 'yyyy-MM-dd') LIKE
                             '@report_period%'
                         AND H.INVOICE_CODE IS NOT NULL
                         AND H.ORG_ID = '@org_id'
                         AND H.INVOICE_TYPE = 2)
                 AND L.TAX_RATE = 13),
             2),0)
  FROM DUAL


SELECT  SUM((NVL(A.S_CREDIT_VAL, 0) - NVL(A.S_DEBIT_VAL, 0)))  S_VAL
  FROM TMS_INV_OUTTAX_TRADE_AFFRIM A
 WHERE A.TRADE_TYPE_ID = '91bd9b65-708f-4661-8e47-6513bb9c57d0'
   AND ORG_ID = '@org_id'
   AND BUS_CLASS_CODE = 'BUS_SELL'
   AND TO_DATE(TO_CHAR(A.TRADE_DATE, 'yyyy-MM-dd'), 'YYYY-MM-DD') >=
       TO_DATE('@s_date', 'YYYY-MM-DD')
   AND TO_DATE(TO_CHAR(A.TRADE_DATE, 'yyyy-MM-dd'), 'YYYY-MM-DD') <=
       TO_DATE('@e_date', 'YYYY-MM-DD')



  SELECT NVL(ROUND((SELECT SUM((TE.S_CREDIT_VAL - TE.S_DEBIT_VAL) *
                            TE.EXCHANGE_RATE)
                   FROM TMS_INV_OUTTAX_TRADE_AFFRIM TE
                  WHERE TE.BUS_CLASS_CODE ='BUS_SELL' 
                       AND TE.tax_method = '2'
                     AND TE.ORG_ID= '@org_id'
                    AND TO_DATE(TO_CHAR(TE.ACCOUNT_DATE, 'yyyy-MM-dd'),
                                'YYYY-MM-DD') >=
                        TO_DATE('@s_date', 'YYYY-MM-DD')
                    AND TO_DATE(TO_CHAR(TE.ACCOUNT_DATE, 'yyyy-MM-dd'),
                                'YYYY-MM-DD') <=
                        TO_DATE('@e_date', 'YYYY-MM-DD')),2),0)
            FROM DUAL    

 SELECT SUM(NVL(AMT,0)) S_VAL FROM (   
   SELECT SUM(NVL(S_CREDIT_NET_VAL,0)-nvl(TE.S_DEBIT_NET_VAL,0)) AMT
  FROM TMS_INV_OUTTAX_TRADE_AFFRIM TA, TMS_INV_OUTTAX_TRADE_ENTRY TE
 WHERE TA.TRADE_AFFRIM_ID = TE.TRADE_AFFRIM_ID
   AND TA.IS_ACCOUNT = 'Y'
   and ta.bus_class_code = 'BUS_SELL'
   AND TO_DATE(TO_CHAR(TA.TRADE_DATE, 'yyyy-MM-dd'), 'YYYY-MM-DD') >=
       TO_DATE('@s_date', 'YYYY-MM-DD')
   AND TO_DATE(TO_CHAR(TA.TRADE_DATE, 'yyyy-MM-dd'), 'YYYY-MM-DD') <=
       TO_DATE('@e_date', 'YYYY-MM-DD')
       UNION ALL
       SELECT  SUM((NVL(A.S_CREDIT_VAL, 0) - NVL(A.S_DEBIT_VAL, 0)))  AMT
  FROM TMS_INV_OUTTAX_TRADE_AFFRIM A
 WHERE A.TRADE_TYPE_ID = '91bd9b65-708f-4661-8e47-6513bb9c57d0'
   AND ORG_ID = '@org_id'
   AND BUS_CLASS_CODE = 'BUS_SELL'
   AND TO_DATE(TO_CHAR(A.TRADE_DATE, 'yyyy-MM-dd'), 'YYYY-MM-DD') >=
       TO_DATE('@s_date', 'YYYY-MM-DD')
   AND TO_DATE(TO_CHAR(A.TRADE_DATE, 'yyyy-MM-dd'), 'YYYY-MM-DD') <=
       TO_DATE('@e_date', 'YYYY-MM-DD')
       UNION ALL       
       SELECT SUM((NVL(A.S_CREDIT_VAL, 0) - NVL(A.S_DEBIT_VAL, 0))) AMT
  FROM TMS_INV_OUTTAX_TRADE_AFFRIM A
 WHERE A.TRADE_TYPE_ID IN
       (SELECT T2.TRADE_TYPE_ID
          FROM (SELECT TA.TRADE_CATEGORY_ID
                  FROM TMS_BA_TRADE_AFFIRM TA, TMS_BA_TAX_ITEMS TI
                 WHERE TA.ITEM_ID = TI.ITEM_ID
                   AND TI.ITEM_NAME = '金融同业往来业务'
                   AND TA.BUS_CLASS_CODE = 'BUS_SELL'
                   AND TI.IS_USED = 'Y'
                   AND TA.IS_USED = 'Y') T1,
               TMS_BA_TAX_TRADE_TYPE T2
         WHERE T1.TRADE_CATEGORY_ID = T2.TRADE_TYPE_ID)
   AND ORG_ID = '@org_id'
   AND BUS_CLASS_CODE = 'BUS_SELL'
   AND TO_DATE(TO_CHAR(A.TRADE_DATE, 'yyyy-MM-dd'), 'YYYY-MM-DD') >=
       TO_DATE('@s_date', 'YYYY-MM-DD')
   AND TO_DATE(TO_CHAR(A.TRADE_DATE, 'yyyy-MM-dd'), 'YYYY-MM-DD') <=
       TO_DATE('@e_date', 'YYYY-MM-DD'))

SELECT SUM((NVL(A.S_CREDIT_VAL, 0) - NVL(A.S_DEBIT_VAL, 0))) S_VAL
  FROM TMS_INV_OUTTAX_TRADE_AFFRIM A
 WHERE A.TRADE_TYPE_ID IN
       (SELECT T2.TRADE_TYPE_ID
          FROM (SELECT TA.TRADE_CATEGORY_ID
                  FROM TMS_BA_TRADE_AFFIRM TA, TMS_BA_TAX_ITEMS TI
                 WHERE TA.ITEM_ID = TI.ITEM_ID
                   AND TI.ITEM_NAME = '金融同业往来业务'
                   AND TA.BUS_CLASS_CODE = 'BUS_SELL'
                   AND TI.IS_USED = 'Y'
                   AND TA.IS_USED = 'Y') T1,
               TMS_BA_TAX_TRADE_TYPE T2
         WHERE T1.TRADE_CATEGORY_ID = T2.TRADE_TYPE_ID)
   AND ORG_ID = '@org_id'
   AND BUS_CLASS_CODE = 'BUS_SELL'
   AND TO_DATE(TO_CHAR(A.TRADE_DATE, 'yyyy-MM-dd'), 'YYYY-MM-DD') >=
       TO_DATE('@s_date', 'YYYY-MM-DD')
   AND TO_DATE(TO_CHAR(A.TRADE_DATE, 'yyyy-MM-dd'), 'YYYY-MM-DD') <=
       TO_DATE('@e_date', 'YYYY-MM-DD')