API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Aug 22nd, 2017
91
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 8.37 KB | None | 0 0
raw download clone embed print report
  1. DELETE FROM rms_.dwh$delta_snapshot_data;
  2.  
  3. DELETE FROM rms_.dwh$delta_snapshot;
  4.  
  5. INSERT INTO rms_.dwh$delta_snapshot
  6.    (SELECT snapshot_date
  7.       FROM (SELECT     (SELECT TRUNC(MIN(close_date))
  8.                           FROM rms_.act) + LEVEL - 1
  9.                           AS snapshot_date
  10.                   FROM DUAL
  11.             CONNECT BY LEVEL <= TRUNC(SYSDATE) - (SELECT TRUNC(MIN(close_date))
  12.                                                     FROM rms_.act))
  13.      WHERE snapshot_date < TRUNC(SYSDATE));
  14.  
  15. INSERT INTO rms_.dwh$delta_snapshot
  16.      VALUES (TRUNC(SYSDATE));
  17.  
  18. INSERT INTO rms_.dwh$delta_snapshot_data
  19.    (SELECT *
  20.       FROM (WITH acts
  21.                  AS (SELECT TRUNC(a.close_date) AS close_date
  22.                            ,a.id AS act_id
  23.                            ,a.source_id
  24.                            ,a.destination_id
  25.                            ,a.transfer_type_id
  26.                            ,ae.resource_type_id
  27.                            ,NVL(ae.heap_amount, 0) * NVL(rm.factor, 1) AS heap_amount
  28.                            ,NVL(aed.amount, 0) * NVL(rm.factor, 1) AS batch_amount
  29.                            ,(CASE WHEN aer.act_id IS NOT NULL THEN 1 ELSE 0 END) AS enum_amount
  30.                            ,NVL(aer.used, 0) AS used
  31.                            ,NVL(aer.prev_used, 0) AS prev_used
  32.                            ,NVL(aer.STATUS, 'READY') AS STATUS
  33.                            ,NVL(aer.prev_status, 'READY') AS prev_status
  34.                            ,NVL(aer.bookkeeping_status, 'NEW') AS bookkeeping_status
  35.                            ,NVL(aer.prev_bookkeeping_status, 'NEW') AS prev_bookkeeping_status
  36.                            ,aer.required_transfer_type_id AS required_transfer_type_id
  37.                            ,aer.prev_required_transfer_type_id AS prev_required_transfer_type_id
  38.                            ,aer.warranty_valid AS warranty_valid
  39.                            ,aer.prev_warranty_valid AS prev_warranty_valid
  40.                            ,NVL(aed.consignment_id, aer.consignment_id) AS consignment_id
  41.                            ,NVL(aed.consignment_id, prev_consignment_id) AS prev_consignment_id
  42.                            ,resource_rp_id
  43.                            ,prev_resource_rp_id
  44.                        FROM rms_.act a
  45.                             JOIN rms_.act_entry ae ON ae.act_id = a.id
  46.                             JOIN argus_sys.resource_measure rm ON ae.measure_id = rm.id
  47.                             LEFT OUTER JOIN rms_.act_entry_details aed
  48.                                ON (    aed.act_id = ae.act_id
  49.                                    AND aed.resource_type_id = ae.resource_type_id)
  50.                             LEFT OUTER JOIN
  51.                             (SELECT act_id
  52.                                    ,resource_type_id
  53.                                    ,LAG( used, 1) OVER (PARTITION BY resource_unit_id ORDER BY close_date, a2.id) AS prev_used
  54.                                    ,used
  55.                                    ,LAG( STATUS, 1) OVER (PARTITION BY resource_unit_id ORDER BY close_date, a2.id) AS prev_status
  56.                                    ,STATUS
  57.                                    ,LAG( bookkeeping_status, 1) OVER (PARTITION BY resource_unit_id ORDER BY close_date, a2.id) AS prev_bookkeeping_status
  58.                                    ,bookkeeping_status
  59.                                    ,LAG( required_transfer_type_id, 1) OVER (PARTITION BY resource_unit_id ORDER BY close_date, a2.id)
  60.                                        AS prev_required_transfer_type_id
  61.                                    ,required_transfer_type_id
  62.                                    ,LAG( warranty_valid, 1) OVER (PARTITION BY resource_unit_id ORDER BY close_date, a2.id) AS prev_warranty_valid
  63.                                    ,warranty_valid
  64.                                    ,consignment_id
  65.                                    ,LAG( consignment_id, 1) OVER (PARTITION BY resource_unit_id ORDER BY close_date, a2.id) AS prev_consignment_id
  66.                                    ,worker_id AS resource_rp_id
  67.                                    ,LAG( worker_id, 1) OVER (PARTITION BY resource_unit_id ORDER BY close_date, a2.id) AS prev_resource_rp_id
  68.                                FROM rms_.act_entry_resource, rms_.act a2
  69.                               WHERE act_id = a2.id) aer
  70.                                ON (    aer.act_id = a.id
  71.                                    AND aer.resource_type_id = ae.resource_type_id)
  72.                       WHERE     a.close_date IS NOT NULL
  73.                             AND a.close_date <= TRUNC(SYSDATE))
  74.             SELECT close_date AS snapshot_date
  75.                   ,acc.enterprise_branch_id AS branch_id
  76.                   ,delta.account_id
  77.                   ,DECODE(acc.scope_id, 9, DECODE(e.parent_id, NULL, 'C', 'D'), 'O') AS account_grade
  78.                   ,delta.resource_type_id
  79.                   ,delta.used
  80.                   ,delta.STATUS
  81.                   ,delta.required_transfer_type_id
  82.                   ,delta.warranty_valid
  83.                   ,delta.amount
  84.                   ,delta.consignment_id
  85.                   ,delta.resource_rp_id
  86.                   ,delta.transfer_type_id
  87.                   ,delta.bookkeeping_status
  88.                   ,delta.source_id
  89.                   ,delta.destination_id
  90.                   ,delta.act_id
  91.               FROM (SELECT   close_date
  92.                             ,account_id
  93.                             ,resource_type_id
  94.                             ,used
  95.                             ,STATUS
  96.                             ,bookkeeping_status
  97.                             ,required_transfer_type_id
  98.                             ,warranty_valid
  99.                             ,consignment_id
  100.                             ,resource_rp_id
  101.                             ,transfer_type_id
  102.                             ,source_id
  103.                             ,destination_id
  104.                             ,act_id
  105.                             ,SUM(amount) AS amount
  106.                         FROM (SELECT close_date
  107.                                     ,source_id AS account_id
  108.                                     ,resource_type_id
  109.                                     ,prev_used AS used
  110.                                     ,prev_status AS STATUS
  111.                                     ,prev_bookkeeping_status AS bookkeeping_status
  112.                                     ,prev_required_transfer_type_id AS required_transfer_type_id
  113.                                     ,prev_warranty_valid AS warranty_valid
  114.                                     ,prev_consignment_id AS consignment_id
  115.                                     ,prev_resource_rp_id AS resource_rp_id
  116.                                     ,transfer_type_id
  117.                                     ,source_id
  118.                                     ,destination_id
  119.                                     ,act_id
  120.                                     ,-1 * (heap_amount + batch_amount + enum_amount) AS amount
  121.                                 FROM acts
  122.                               UNION ALL
  123.                               SELECT close_date
  124.                                     ,destination_id AS account_id
  125.                                     ,resource_type_id
  126.                                     ,used
  127.                                     ,STATUS
  128.                                     ,bookkeeping_status
  129.                                     ,required_transfer_type_id
  130.                                     ,warranty_valid
  131.                                     ,consignment_id
  132.                                     ,resource_rp_id
  133.                                     ,transfer_type_id
  134.                                     ,source_id
  135.                                     ,destination_id
  136.                                     ,act_id
  137.                                     ,+1 * (heap_amount + batch_amount + enum_amount) AS amount
  138.                                 FROM acts)
  139.                     GROUP BY close_date, account_id, source_id, destination_id, act_id, resource_type_id, consignment_id, resource_rp_id, transfer_type_id
  140.                             ,used, STATUS, bookkeeping_status, required_transfer_type_id, warranty_valid) delta
  141.                   ,rms_.account acc
  142.                   ,argus_sys.enterprise_branch e
  143.              WHERE     acc.scope_id IN (2, 3, 4, 5, 9)
  144.                    AND delta.account_id = acc.id
  145.                    AND acc.enterprise_branch_id = e.id));
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!