API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Mar 21st, 2018
87
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PL/SQL 7.91 KB | None | 0 0
raw download clone embed print report
  1. DROP TABLE vtmp_rpse_by_acc1;
  2.  
  3. CREATE TABLE vtmp_rpse_by_acc1
  4. PARALLEL 4
  5. AS
  6.      SELECT v.ACCOUNT_ID,
  7.             v.ROY_GEN_RIGHT_ID,
  8.             v.ROY_GEN_RIGHT_AT_SOURCE,
  9.             v.ROY_GEN_STEP_PERC_COMM_USED,
  10.             v.ROY_GEN_IS_COVER_RECORDING,
  11.             v.ROY_GEN_RIGHT_HAS_COVER_SPLIT,
  12.             SUM (v.WHT_ADJUSTED_AMOUNT * v.RECONCILIATION_EXCHANGE_RATE * v.ACCOUNT_EXCHANGE_RATE) AS CLIENT_AMOUNT_SUM,
  13.             SUM ( (v.ROY_GEN_CHAIN_SOURCE_AMOUNT * (1 - (NVL (v.INTERNAL_WHT_RATE_USED, 0) / 100)) * v.RECONCILIATION_EXCHANGE_RATE * v.ACCOUNT_EXCHANGE_RATE)) AS ROY_GEN_CHAIN_SOURCE_AMT_SUM,
  14.             SUM ( (v.ROY_GEN_CHAIN_RECEIPTS_AMOUNT * (1 - (NVL (v.INTERNAL_WHT_RATE_USED, 0) / 100)) * v.RECONCILIATION_EXCHANGE_RATE * v.ACCOUNT_EXCHANGE_RATE)) AS ROY_GEN_CHAIN_RECEIPTS_AMT_SUM, --normal clients
  15.             v.ROY_GEN_STEP_DIR_COLL_PERC,
  16.             v.ROY_GEN_STEP_DIR_COLL_FEE_USED,
  17.             SUM (v.ROY_GEN_STEP_DIR_COLL_FEE * v.RECONCILIATION_EXCHANGE_RATE * v.ACCOUNT_EXCHANGE_RATE) AS ROY_GEN_STEP_DIR_COLL_FEE_SUM
  18.        FROM ROY_PAY_SUMS_EXT PARTITION (rp_ex_2017_m04) v, get_agreements_ext t
  19.       WHERE v.ACCOUNT_ID = t.id AND t.should_extract != 'N'
  20.    GROUP BY v.ACCOUNT_ID,
  21.             v.ROY_GEN_RIGHT_ID,
  22.             v.ROY_GEN_RIGHT_AT_SOURCE,
  23.             v.ROY_GEN_STEP_PERC_COMM_USED,
  24.             v.ROY_GEN_IS_COVER_RECORDING,
  25.             v.ROY_GEN_RIGHT_HAS_COVER_SPLIT,
  26.             v.ROY_GEN_STEP_DIR_COLL_PERC,
  27.             v.ROY_GEN_STEP_DIR_COLL_FEE_USED;
  28.  
  29. ALTER TABLE vtmp_rpse_by_acc1
  30.    NOPARALLEL;
  31.  
  32. DROP TABLE vtmp_rpse_by_acc2;
  33.  
  34. CREATE TABLE vtmp_rpse_by_acc2
  35. PARALLEL 8
  36. AS
  37.      SELECT v.ACCOUNT_ID,
  38.             v.ROY_GEN_RIGHT_ID,
  39.             v.ROY_GEN_RIGHT_AT_SOURCE,
  40.             v.ROY_GEN_STEP_PERC_COMM_USED,
  41.             v.ROY_GEN_IS_COVER_RECORDING,
  42.             v.ROY_GEN_RIGHT_HAS_COVER_SPLIT,
  43.             SUM (v.WHT_ADJUSTED_AMOUNT * v.RECONCILIATION_EXCHANGE_RATE * v.ACCOUNT_EXCHANGE_RATE) AS CLIENT_AMOUNT_SUM,
  44.             SUM ( (v.ROY_GEN_CHAIN_SOURCE_AMOUNT * (1 - (NVL (v.INTERNAL_WHT_RATE_USED, 0) / 100)) * v.RECONCILIATION_EXCHANGE_RATE * v.ACCOUNT_EXCHANGE_RATE)) AS ROY_GEN_CHAIN_SOURCE_AMT_SUM,
  45.             SUM ( (v.ROY_GEN_CHAIN_RECEIPTS_AMOUNT * (1 - (NVL (v.INTERNAL_WHT_RATE_USED, 0) / 100)) * v.RECONCILIATION_EXCHANGE_RATE * v.ACCOUNT_EXCHANGE_RATE)) AS ROY_GEN_CHAIN_RECEIPTS_AMT_SUM, --normal clients
  46.             v.ROY_GEN_STEP_DIR_COLL_PERC,
  47.             v.ROY_GEN_STEP_DIR_COLL_FEE_USED,
  48.             SUM (v.ROY_GEN_STEP_DIR_COLL_FEE * v.RECONCILIATION_EXCHANGE_RATE * v.ACCOUNT_EXCHANGE_RATE) AS ROY_GEN_STEP_DIR_COLL_FEE_SUM
  49.        FROM ROY_PAY_SUMS_EXT PARTITION (rp_ex_2017_m05) v, get_agreements_ext t
  50.       WHERE v.ACCOUNT_ID = t.id AND t.should_extract != 'N'
  51.    GROUP BY v.ACCOUNT_ID,
  52.             v.ROY_GEN_RIGHT_ID,
  53.             v.ROY_GEN_RIGHT_AT_SOURCE,
  54.             v.ROY_GEN_STEP_PERC_COMM_USED,
  55.             v.ROY_GEN_IS_COVER_RECORDING,
  56.             v.ROY_GEN_RIGHT_HAS_COVER_SPLIT,
  57.             v.ROY_GEN_STEP_DIR_COLL_PERC,
  58.             v.ROY_GEN_STEP_DIR_COLL_FEE_USED;
  59.  
  60. ALTER TABLE vtmp_rpse_by_acc2
  61.    NOPARALLEL;
  62.  
  63. DROP TABLE vtmp_rpse_by_acc3;
  64.  
  65. CREATE TABLE vtmp_rpse_by_acc3
  66. PARALLEL 8
  67. AS
  68.      SELECT v.ACCOUNT_ID,
  69.             v.ROY_GEN_RIGHT_ID,
  70.             v.ROY_GEN_RIGHT_AT_SOURCE,
  71.             v.ROY_GEN_STEP_PERC_COMM_USED,
  72.             v.ROY_GEN_IS_COVER_RECORDING,
  73.             v.ROY_GEN_RIGHT_HAS_COVER_SPLIT,
  74.             SUM (v.WHT_ADJUSTED_AMOUNT * v.RECONCILIATION_EXCHANGE_RATE * v.ACCOUNT_EXCHANGE_RATE) AS CLIENT_AMOUNT_SUM,
  75.             SUM ( (v.ROY_GEN_CHAIN_SOURCE_AMOUNT * (1 - (NVL (v.INTERNAL_WHT_RATE_USED, 0) / 100)) * v.RECONCILIATION_EXCHANGE_RATE * v.ACCOUNT_EXCHANGE_RATE)) AS ROY_GEN_CHAIN_SOURCE_AMT_SUM,
  76.             SUM ( (v.ROY_GEN_CHAIN_RECEIPTS_AMOUNT * (1 - (NVL (v.INTERNAL_WHT_RATE_USED, 0) / 100)) * v.RECONCILIATION_EXCHANGE_RATE * v.ACCOUNT_EXCHANGE_RATE)) AS ROY_GEN_CHAIN_RECEIPTS_AMT_SUM, --normal clients
  77.             v.ROY_GEN_STEP_DIR_COLL_PERC,
  78.             v.ROY_GEN_STEP_DIR_COLL_FEE_USED,
  79.             SUM (v.ROY_GEN_STEP_DIR_COLL_FEE * v.RECONCILIATION_EXCHANGE_RATE * v.ACCOUNT_EXCHANGE_RATE) AS ROY_GEN_STEP_DIR_COLL_FEE_SUM
  80.        FROM ROY_PAY_SUMS_EXT PARTITION (rp_ex_2017_m06) v, get_agreements_ext t
  81.       WHERE v.ACCOUNT_ID = t.id AND t.should_extract != 'N'
  82.    GROUP BY v.ACCOUNT_ID,
  83.             v.ROY_GEN_RIGHT_ID,
  84.             v.ROY_GEN_RIGHT_AT_SOURCE,
  85.             v.ROY_GEN_STEP_PERC_COMM_USED,
  86.             v.ROY_GEN_IS_COVER_RECORDING,
  87.             v.ROY_GEN_RIGHT_HAS_COVER_SPLIT,
  88.             v.ROY_GEN_STEP_DIR_COLL_PERC,
  89.             v.ROY_GEN_STEP_DIR_COLL_FEE_USED;
  90.  
  91. ALTER TABLE vtmp_rpse_by_acc3
  92.    NOPARALLEL;
  93.  
  94. DROP TABLE vtmp_rpse_by_acc;
  95.  
  96. CREATE TABLE vtmp_rpse_by_acc
  97. AS
  98.      SELECT ACCOUNT_ID,
  99.             ROY_GEN_RIGHT_ID,
  100.             ROY_GEN_RIGHT_AT_SOURCE,
  101.             ROY_GEN_STEP_PERC_COMM_USED,
  102.             ROY_GEN_IS_COVER_RECORDING,
  103.             ROY_GEN_RIGHT_HAS_COVER_SPLIT,
  104.             SUM (CLIENT_AMOUNT_SUM) CLIENT_AMOUNT_SUM,
  105.             SUM (ROY_GEN_CHAIN_SOURCE_AMT_SUM) ROY_GEN_CHAIN_SOURCE_AMT_SUM,
  106.             SUM (ROY_GEN_CHAIN_RECEIPTS_AMT_SUM) ROY_GEN_CHAIN_RECEIPTS_AMT_SUM,
  107.             ROY_GEN_STEP_DIR_COLL_PERC,
  108.             ROY_GEN_STEP_DIR_COLL_FEE_USED,
  109.             SUM (ROY_GEN_STEP_DIR_COLL_FEE_SUM) ROY_GEN_STEP_DIR_COLL_FEE_SUM
  110.        FROM (SELECT * FROM vtmp_rpse_by_acc1
  111.              UNION ALL
  112.              SELECT * FROM vtmp_rpse_by_acc2
  113.              UNION ALL
  114.              SELECT * FROM vtmp_rpse_by_acc3)
  115.    GROUP BY ACCOUNT_ID,
  116.             ROY_GEN_RIGHT_ID,
  117.             ROY_GEN_RIGHT_AT_SOURCE,
  118.             ROY_GEN_STEP_PERC_COMM_USED,
  119.             ROY_GEN_IS_COVER_RECORDING,
  120.             ROY_GEN_RIGHT_HAS_COVER_SPLIT,
  121.             ROY_GEN_STEP_DIR_COLL_PERC,
  122.             ROY_GEN_STEP_DIR_COLL_FEE_USED;
  123.  
  124. DROP TABLE vtmp_rpse_by_acc_adj;
  125.  
  126. CREATE TABLE vtmp_rpse_by_acc_adj
  127. AS
  128.      SELECT ACCOUNT_ID,
  129.             ROY_GEN_RIGHT_ID,
  130.             ROY_GEN_RIGHT_AT_SOURCE,
  131.             ROY_GEN_STEP_PERC_COMM_USED,
  132.             ROY_GEN_IS_COVER_RECORDING,
  133.             ROY_GEN_RIGHT_HAS_COVER_SPLIT,
  134.             SUM (CLIENT_AMOUNT_SUM) CLIENT_AMOUNT_SUM,
  135.             SUM (ROY_GEN_CHAIN_SOURCE_AMT_SUM) ROY_GEN_CHAIN_SOURCE_AMT_SUM,
  136.             SUM (ROY_GEN_CHAIN_RECEIPTS_AMT_SUM) ROY_GEN_CHAIN_RECEIPTS_AMT_SUM,
  137.             ROY_GEN_STEP_DIR_COLL_PERC,
  138.             ROY_GEN_STEP_DIR_COLL_FEE_USED,
  139.             SUM (ROY_GEN_STEP_DIR_COLL_FEE_SUM) ROY_GEN_STEP_DIR_COLL_FEE_SUM
  140.        FROM (SELECT * FROM vtmp_rpse_by_acc
  141.              UNION ALL
  142.                SELECT ROYALTY_ADJ_PAYMENTS.ACCOUNT_ID,
  143.                       ROY_GEN_RIGHT_ID,
  144.                       ROY_GEN_AT_SOURCE,
  145.                       (100 - ROY_GEN_COMMISSION_RATE_TAKEN),
  146.                       'N',
  147.                       'N',
  148.                       SUM (WHT_ADJUSTED_AMOUNT * RECONCILIATION_EXCHANGE_RATE * ACCOUNT_EXCHANGE_RATE),
  149.                       SUM (ROY_GEN_SOURCE_AMOUNT * RECONCILIATION_EXCHANGE_RATE * ACCOUNT_EXCHANGE_RATE),
  150.                       SUM (ROY_GEN_RECEIVED_AMOUNT * RECONCILIATION_EXCHANGE_RATE * ACCOUNT_EXCHANGE_RATE),
  151.                       NULL,
  152.                       NULL,
  153.                       NULL
  154.                  FROM ROYALTY_ADJ_PAYMENTS, AGREEMENTS
  155.                 WHERE ROYALTY_ADJ_PAYMENTS.PAYMENT_DATE BETWEEN TO_DATE ('01APR17', 'ddmonyy') AND TO_DATE ('30JUN17 23:59:59', 'ddmonyy HH24:MI:SS') AND ROYALTY_ADJ_PAYMENTS.ACCOUNT_ID = AGREEMENTS.ID
  156.              GROUP BY ROYALTY_ADJ_PAYMENTS.ACCOUNT_ID,
  157.                       ROY_GEN_RIGHT_ID,
  158.                       ROY_GEN_AT_SOURCE,
  159.                       ROY_GEN_COMMISSION_RATE_TAKEN)
  160.    GROUP BY ACCOUNT_ID,
  161.             ROY_GEN_RIGHT_ID,
  162.             ROY_GEN_RIGHT_AT_SOURCE,
  163.             ROY_GEN_STEP_PERC_COMM_USED,
  164.             ROY_GEN_IS_COVER_RECORDING,
  165.             ROY_GEN_RIGHT_HAS_COVER_SPLIT,
  166.             ROY_GEN_STEP_DIR_COLL_PERC,
  167.             ROY_GEN_STEP_DIR_COLL_FEE_USED;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!