Guest User

Untitled

a guest
Jul 18th, 2018
80
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 3.24 KB | None | 0 0
  1. SELECT ENGAGED.*, REALIZED.* from
  2. (SELECT SUM(total_sum) AS SUM_ENGAGED
  3. FROM (SELECT mo.mission_id, SUM(ex.amount_total) AS total_sum
  4. FROM fc_bs_mission_order mo INNER JOIN
  5. fc_bs_expense ex ON mo.mission_id = ex.mission_id INNER JOIN
  6. su_an_expense_kind ek ON ex.EXPENSE_KIND_ID = ek.EXPENSE_KIND_ID INNER JOIN
  7. su_an_kinds_group gr ON gr.GROUP_ID = ek.KINDS_GROUP_ID
  8. inner join fc_bs_mo_event ev on ev.MISSION_ID = mo.MISSION_ID
  9. WHERE
  10. ev.EVENT_TYPE=11 and ev.event_date >= to_date('01.01.2009','DD.MM.YYYY') and ev.event_date <= to_date('31.12.2009','DD.MM.YYYY') and
  11. gr.GROUP_ID = 3621 AND mo.ACTIVITY_ID IN
  12. (SELECT ACTIVITY_ID
  13. FROM SU_AN_ACTIVITY
  14. WHERE code IN
  15. (SELECT code
  16. FROM SU_ST_SERVICE
  17. WHERE attribute1 = 'STR')) AND mo.mission_id IN
  18. (SELECT mission_id
  19. FROM fc_bs_split_pattern
  20. WHERE mission_id != 0 AND rubric1_id =8310 AND rubric2_id = 2090 AND rubric3_id = 2120 and rubric4_id = 1000)
  21. GROUP BY mo.mission_id) ) ENGAGED,
  22. (select sum(inv.AMOUNT_TTC) AS SUM_REALIZED from fc_ii_invoice inv
  23. inner join fc_bs_mission_order mo on mo.MISSION_ID=inv.MISSION_ID
  24. inner join fc_bs_inv_event ev on ev.INVOICE_ID = inv.INVOICE_ID
  25. where mo.ACTIVITY_ID IN
  26. (SELECT ACTIVITY_ID
  27. FROM SU_AN_ACTIVITY
  28. WHERE code IN
  29. (SELECT code
  30. FROM SU_ST_SERVICE
  31. WHERE attribute1 = 'STR'))
  32. and ev.EVENT_TYPE=4 and ev.event_date >= to_date('01.01.2009','DD.MM.YYYY') and ev.event_date <= to_date('31.12.2009','DD.MM.YYYY')
  33. and inv.MISSION_ID in (
  34. SELECT mo.mission_id
  35. FROM fc_bs_mission_order mo INNER JOIN
  36. fc_bs_expense ex ON mo.mission_id = ex.mission_id INNER JOIN
  37. su_an_expense_kind ek ON ex.EXPENSE_KIND_ID = ek.EXPENSE_KIND_ID INNER JOIN
  38. su_an_kinds_group gr ON gr.GROUP_ID = ek.KINDS_GROUP_ID
  39. inner join fc_bs_mo_event ev on ev.MISSION_ID = mo.MISSION_ID
  40. WHERE
  41. ev.EVENT_TYPE=11 and ev.event_date >= to_date('01.01.2009','DD.MM.YYYY') and ev.event_date <= to_date('31.12.2009','DD.MM.YYYY') and
  42. gr.GROUP_ID =3621 AND mo.ACTIVITY_ID IN
  43. (SELECT ACTIVITY_ID
  44. FROM SU_AN_ACTIVITY
  45. WHERE code IN
  46. (SELECT code
  47. FROM SU_ST_SERVICE
  48. WHERE attribute1 = 'STR')) AND mo.mission_id IN
  49. (SELECT mission_id
  50. FROM fc_bs_split_pattern
  51. WHERE mission_id != 0 AND rubric1_id =8310 AND rubric2_id =2090 AND rubric3_id =2120 and rubric4_id =1000)
  52. )
  53. ) REALIZED
Add Comment
Please, Sign In to add comment