API tools faq
paste
Advertisement
psi_mmobile

Untitled

psi_mmobile
Oct 19th, 2020
3,501
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PL/SQL 8.57 KB | None | 0 0
raw download clone embed print report
  1. WITH ptd AS (
  2.         SELECT pd.person_id,
  3.            pd.date_day,
  4.            pd.is_locked,
  5.            pd.work_code_id,
  6.            pd.REF_NUMBER,
  7.            SUM(pd.TRF_DHR) SUM      
  8.         FROM pd_trf_day pd
  9.         WHERE   pd.trf_dhr IS NOT NULL
  10.                 AND pd.trf_dhr != 0
  11.                 AND pd.date_day BETWEEN SYSDATE - 1 AND SYSDATE
  12.         GROUP BY pd.person_id,
  13.            pd.date_day,
  14.            pd.is_locked,
  15.            pd.work_code_id,
  16.            pd.person_id,
  17.            pd.REF_NUMBER
  18.         )  
  19.     SELECT
  20.         z.person_id,    
  21.         z.date_day,
  22.         p.vo_person_category_id,
  23.         z.is_locked,
  24.         LPAD(NVL(p.company_nr,'0'), 6, '0') -- person number            
  25.         || TO_CHAR(z.date_day, 'YYMMDD') -- date of the activity
  26.         || RPAD  (wc.short_code,9, ' ') ||' +'    -- work code        
  27.         || LPAD(z.SUM * 100, 9, '0')  
  28.         || LPAD('0', 9, '0') -- unit value (always 9 x 0) + percentage (always 5 x 0)    
  29.         || LPAD(NVL(wc.COST_PER_HOUR_COEFICIENT, 0)*10000, 5, '0')  -- percentage (always 5 x 0)
  30.         || RPAD(' ', 10, ' ') -- cost center reference
  31.         || LPAD(' ', 6, ' ') -- project reference
  32.         || LPAD(' ', 5, ' ') -- activity reference
  33.         || LPAD('51101', 5,'0') -- management State level 1
  34.         || LPAD(NVL(regexp_replace(z.REF_NUMBER, '[A-Za-z]', ''),'0'),5,'0') -- management State level 2        
  35.         || LPAD('0', 5,'0') -- management State level 3
  36.         || LPAD(' ', 1, ' ')  -- reason
  37.         || LPAD(' ', 1, ' ')  -- empty character
  38.         || LPAD(' ', 9, ' ') -- activity code 2
  39.        
  40.         || LPAD(DECODE(ROW_NUMBER() OVER (PARTITION BY  pdd.pd_day_id ORDER BY wc.work_code_std_category_id ASC, z.date_day, z.person_id), 1,
  41.                         DECODE(wc.work_code_std_category_id,1,NVL(pdd.declared_vehicle_km,0) + NVL(pdd.declared_private_km,0), '0'),  
  42.                             '0'),  5, '0') -- KM values
  43.         || LPAD(DECODE(ROW_NUMBER() OVER (PARTITION BY  pdd.pd_day_id ORDER BY wc.work_code_std_category_id ASC, z.date_day, z.person_id), 1,
  44.                         DECODE(wc.work_code_std_category_id,1,NVL(pdd.DECLARED_PRIVATE_KM,0), '0'),                    
  45.                             '0'), 5, '0') -- frais de deplacement KM value
  46.         || LPAD(DECODE(ROW_NUMBER() OVER (PARTITION BY  pdd.pd_day_id ORDER BY wc.work_code_std_category_id ASC, z.date_day, z.person_id), 1,
  47.                         DECODE(wc.work_code_std_category_id,1, DECODE(pdd.day_journey_role,'DA','43', 'D ', '23','13'), '0'),
  48.                            '0'), 2, '0') -- mobility code
  49.         || LPAD(DECODE(ROW_NUMBER() OVER (PARTITION BY  pdd.pd_day_id ORDER BY wc.work_code_std_category_id ASC, z.date_day, z.person_id), 1,
  50.                         DECODE(wc.work_code_std_category_id,1, NVL2(NULLIF (pdd.declared_private_km, 0),'73','0'), '0'),
  51.                             '0'), 2, '0')  -- frais de deplacement code
  52.         || LPAD(' ', 5, ' ')
  53.         || LPAD(' ',10,' ')  str
  54.         FROM ptd z,
  55.            person p,
  56.            pd_day pdd,
  57.            work_code wc
  58.         WHERE z.person_id = p.person_id
  59.           AND z.date_day = pdd.date_day
  60.           AND z.person_id = pdd.person_id
  61.           AND z.work_code_id = wc.work_code_id
  62.           AND wc.work_code_std_type_id = 1
  63.     UNION ALL
  64.     SELECT pd.person_id,
  65.       pd.date_day,
  66.       p.vo_person_category_id,
  67.       pd.is_locked,
  68.       LPAD(NVL(p.company_nr,'0'), 6, '0') -- person number
  69.    
  70.       || TO_CHAR(pd.date_day, 'YYMMDD') -- date of the activity
  71.    
  72.       || RPAD  ('441',9, ' ') ||' +'    -- work code
  73.    
  74.       || LPAD(NVL (7.5 * 100, 0), 9, '0')  
  75.    
  76.       || LPAD('0', 9, '0') -- unit value (always 9 x 0) + percentage (always 5 x 0)
  77.    
  78.       || LPAD('0', 5,'0') -- percentage (always 5 x 0)
  79.    
  80.       || RPAD(' ', 10, ' ') -- cost center reference
  81.    
  82.       || LPAD(' ', 6, ' ') -- project reference
  83.    
  84.       || LPAD(' ', 5, ' ') -- activity reference
  85.    
  86.       || LPAD('51101', 5,'0') -- management State level 1
  87.    
  88.       || LPAD(NVL(regexp_replace(pd.REF_NUMBER, '[A-Za-z]', ''),'0'),5,'0') -- management State level 2
  89.    
  90.       || LPAD('0', 5,'0') -- management State level 3
  91.    
  92.       || LPAD(' ', 1, ' ')  -- reason
  93.    
  94.       || LPAD(' ', 1, ' ')  -- empty character
  95.    
  96.       || LPAD(' ', 9, ' ') -- activity code 2
  97.    
  98.       || LPAD('0', 5, '0') -- KM values
  99.    
  100.       || LPAD('0', 5, '0') -- frais de deplacement KM value
  101.    
  102.       || LPAD('0', 2, '0') -- mobility code
  103.    
  104.       || LPAD('0', 2, '0')  -- frais de deplacement code
  105.    
  106.       || LPAD(' ', 5, ' ')
  107.       || LPAD(' ',10,' ')   str
  108.     FROM ptd pd,
  109.       person p,
  110.       pd_day pdd,
  111.       work_code wc
  112.     WHERE pd.person_id                      = pdd.person_id
  113.     AND pd.date_day = pdd.date_day
  114.     AND pdd.person_id = p.person_id
  115.     AND pd.work_code_id                     = wc.work_code_id
  116.     AND NVL(pdd.maintenance,'N') = 'Y'
  117.     AND wc.work_code_std_type_id = 1
  118.     UNION ALL
  119.       SELECT pd.person_id,
  120.       pd.date_day,
  121.       p.vo_person_category_id,
  122.       pd.is_locked,
  123.       LPAD(NVL(p.company_nr,'0'), 6, '0') -- person number
  124.    
  125.       || TO_CHAR(pd.date_day, 'YYMMDD') -- date of the activity
  126.    
  127.       || RPAD  ('438',9, ' ') ||' +'    -- work code
  128.    
  129.       || LPAD(NVL (0.5 * 100, 0), 9, '0')  --activity value
  130.    
  131.       || LPAD('0', 9, '0') -- unit value (always 9 x 0) + percentage (always 5 x 0)
  132.    
  133.       || LPAD('0', 5,'0') -- percentage (always 5 x 0)
  134.    
  135.       || RPAD(' ', 10, ' ') -- cost center reference
  136.    
  137.       || LPAD(' ', 6, ' ') -- project reference
  138.    
  139.       || LPAD(' ', 5, ' ') -- activity reference
  140.    
  141.       || LPAD('51101', 5,'0') -- management State level 1
  142.    
  143.       || LPAD(NVL(regexp_replace(pd.REF_NUMBER, '[A-Za-z]', ''),'0'),5,'0') -- management State level 2
  144.    
  145.       || LPAD('0', 5,'0') -- management State level 3
  146.    
  147.       || LPAD(' ', 1, ' ')  -- reason
  148.    
  149.       || LPAD(' ', 1, ' ')  -- empty character
  150.    
  151.       || LPAD(' ', 9, ' ') -- activity code 2
  152.    
  153.       || LPAD('0', 5, '0') -- KM values
  154.    
  155.       || LPAD('0', 5, '0') -- frais de deplacement KM value
  156.    
  157.       || LPAD('0', 2, '0') -- mobility code
  158.    
  159.       || LPAD('0', 2, '0')  -- frais de deplacement code
  160.    
  161.       || LPAD(' ', 5, ' ')
  162.       || LPAD(' ',10,' ')   str
  163.     FROM pd_trf_day pd,
  164.       person p,
  165.       pd_day pdd,
  166.       work_code wc,
  167.       wbs w
  168.     WHERE pd.person_id                      = pdd.person_id
  169.     AND pd.date_day = pdd.date_day
  170.     AND pdd.person_id = p.person_id
  171.     AND pd.work_code_id                     = wc.work_code_id
  172.     AND ('N' = (SELECT LAUNDRY_IS_NEVER_PAID FROM WBS WHERE WBS_ID =P_WBS.getlvl0WbsID(pd.wbs_id)) OR (SELECT LAUNDRY_IS_NEVER_PAID FROM WBS WHERE WBS_ID =P_WBS.getlvl0WbsID(pd.wbs_id)) IS NULL)
  173.     AND wc.work_code_std_type_id = 1
  174.  
  175. UNION ALL
  176.       SELECT pd.person_id,
  177.       pd.date_day,
  178.       p.vo_person_category_id,
  179.       pd.is_locked,
  180.       LPAD(NVL(p.company_nr,'0'), 6, '0') -- person number
  181.    
  182.       || TO_CHAR(pd.date_day, 'YYMMDD') -- date of the activity
  183.    
  184.       || RPAD  ('440',9, ' ') ||' +'    -- work code
  185.    
  186.       || LPAD(NVL (p.transport_allowance * 100, 0), 9, '0')  --activity value
  187.    
  188.       || LPAD('0', 9, '0') -- unit value (always 9 x 0) + percentage (always 5 x 0)
  189.    
  190.       || LPAD('0', 5,'0') -- percentage (always 5 x 0)
  191.    
  192.       || RPAD(' ', 10, ' ') -- cost center reference
  193.    
  194.       || LPAD(' ', 6, ' ') -- project reference
  195.    
  196.       || LPAD(' ', 5, ' ') -- activity reference
  197.    
  198.       || LPAD('51101', 5,'0') -- management State level 1
  199.    
  200.       || LPAD(NVL(regexp_replace(pd.REF_NUMBER, '[A-Za-z]', ''),'0'),5,'0') -- management State level 2
  201.    
  202.       || LPAD('0', 5,'0') -- management State level 3
  203.    
  204.       || LPAD(' ', 1, ' ')  -- reason
  205.    
  206.       || LPAD(' ', 1, ' ')  -- empty character
  207.    
  208.       || LPAD(' ', 9, ' ') -- activity code 2
  209.    
  210.       || LPAD('0', 5, '0') -- KM values
  211.    
  212.       || LPAD('0', 5, '0') -- frais de deplacement KM value
  213.    
  214.       || LPAD('0', 2, '0') -- mobility code
  215.    
  216.       || LPAD('0', 2, '0')  -- frais de deplacement code
  217.    
  218.       || LPAD(' ', 5, ' ')
  219.       || LPAD(' ',10,' ')   str
  220.     FROM pd_trf_day pd,
  221.       person p,
  222.       pd_day pdd,
  223.       work_code wc,
  224.       wbs w
  225.     WHERE pd.person_id                      = pdd.person_id
  226.     AND pd.date_day = pdd.date_day
  227.     AND pdd.person_id = p.person_id
  228.     AND pd.work_code_id                     = wc.work_code_id
  229.     AND NVL(pdd.public_transport,'N') = 'Y'
  230.     AND wc.work_code_std_type_id = 1;
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!