Advertisement
liam_dao

25103 main

Feb 25th, 2021
3,370
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 35.19 KB | None | 0 0
  1. (SELECT
  2.   NULL SALES_PERSON_CODE
  3. , 'Inter-Company' EMPLOYEE_NAME
  4. , COALESCE(INV_3_PAST.AMT,0) + COALESCE(D_INV_3_PAST.AMT,0) SALES_AMT_3_PAST
  5. , COALESCE(INV_2_PAST.AMT,0) + COALESCE(D_INV_2_PAST.AMT,0) SALES_AMT_2_PAST
  6. , COALESCE(INV_1_PAST.AMT,0) + COALESCE(D_INV_1_PAST.AMT,0) SALES_AMT_1_PAST
  7. , COALESCE(INV1.AMT,0) + COALESCE(D_INV1.AMT,0) SALES_AMT1
  8. , COALESCE(INV2.AMT,0) + COALESCE(D_INV2.AMT,0) SALES_AMT2
  9. , COALESCE(INV3.AMT,0) + COALESCE(D_INV3.AMT,0) SALES_AMT3
  10. , COALESCE(INV4.AMT,0) + COALESCE(D_INV4.AMT,0) SALES_AMT4
  11. , COALESCE(INV5.AMT,0) + COALESCE(D_INV5.AMT,0) SALES_AMT5
  12. , COALESCE(INV6.AMT,0) + COALESCE(D_INV6.AMT,0) SALES_AMT6
  13. , COALESCE(INV7.AMT,0) + COALESCE(D_INV7.AMT,0) SALES_AMT7
  14. , COALESCE(INV8.AMT,0) + COALESCE(D_INV8.AMT,0) SALES_AMT8
  15. , COALESCE(INV9.AMT,0) + COALESCE(D_INV9.AMT,0) SALES_AMT9
  16. , COALESCE(INV10.AMT,0) + COALESCE(D_INV10.AMT,0) SALES_AMT10
  17. , COALESCE(INV11.AMT,0) + COALESCE(D_INV11.AMT,0) SALES_AMT11
  18. , COALESCE(INV12.AMT,0) + COALESCE(D_INV12.AMT,0) SALES_AMT12
  19. , COALESCE(INV_3_PAST.AMT,0) + COALESCE(D_INV_3_PAST.AMT,0) - COALESCE(PJ_3_PAST.AMT,0) PROFIT_AMT_3_PAST
  20. , COALESCE(INV_2_PAST.AMT,0) + COALESCE(D_INV_2_PAST.AMT,0) - COALESCE(PJ_2_PAST.AMT,0) PROFIT_AMT_2_PAST
  21. , COALESCE(INV_1_PAST.AMT,0) + COALESCE(D_INV_1_PAST.AMT,0) - COALESCE(PJ_1_PAST.AMT,0) PROFIT_AMT_1_PAST
  22. , COALESCE(INV1.AMT,0) + COALESCE(D_INV1.AMT,0) - COALESCE(PJ1.AMT,0) PROFIT_AMT1
  23. , COALESCE(INV2.AMT,0) + COALESCE(D_INV2.AMT,0) - COALESCE(PJ2.AMT,0) PROFIT_AMT2
  24. , COALESCE(INV3.AMT,0) + COALESCE(D_INV3.AMT,0) - COALESCE(PJ3.AMT,0) PROFIT_AMT3
  25. , COALESCE(INV4.AMT,0) + COALESCE(D_INV4.AMT,0) - COALESCE(PJ4.AMT,0) PROFIT_AMT4
  26. , COALESCE(INV5.AMT,0) + COALESCE(D_INV5.AMT,0) - COALESCE(PJ5.AMT,0) PROFIT_AMT5
  27. , COALESCE(INV6.AMT,0) + COALESCE(D_INV6.AMT,0) - COALESCE(PJ6.AMT,0) PROFIT_AMT6
  28. , COALESCE(INV7.AMT,0) + COALESCE(D_INV7.AMT,0) - COALESCE(PJ7.AMT,0) PROFIT_AMT7
  29. , COALESCE(INV8.AMT,0) + COALESCE(D_INV8.AMT,0) - COALESCE(PJ8.AMT,0) PROFIT_AMT8
  30. , COALESCE(INV9.AMT,0) + COALESCE(D_INV9.AMT,0) - COALESCE(PJ9.AMT,0) PROFIT_AMT9
  31. , COALESCE(INV10.AMT,0) + COALESCE(D_INV10.AMT,0) - COALESCE(PJ10.AMT,0) PROFIT_AMT10
  32. , COALESCE(INV11.AMT,0) + COALESCE(D_INV11.AMT,0) - COALESCE(PJ11.AMT,0) PROFIT_AMT11
  33. , COALESCE(INV12.AMT,0) + COALESCE(D_INV12.AMT,0) - COALESCE(PJ12.AMT,0) PROFIT_AMT12
  34. FROM
  35.   (SELECT DISTINCT INV.SALES_PERSON_CODE FROM AR_INV_HST_HDR INV LEFT JOIN AR_INV_HST_DET DET ON INV.INVOICE_NO = DET.INVOICE_NO WHERE INV.YEAR_POSTED_TO IN ($P{FINANCIAL_YEAR}, $P{FINANCIAL_YEAR}-1, $P{FINANCIAL_YEAR}-2, $P{FINANCIAL_YEAR}-3)  AND (INV.INVOICE_TYPE IN ('R','P','Q') OR (INV.INVOICE_TYPE = 'D' AND DET.SERVICE_CODE IN ('INTERCO','51000'))) AND INV.SALES_PERSON_CODE IS NULL) INV
  36. LEFT JOIN
  37.   (SELECT SUM(INV.TOTAL_PRE_TAX_HOME_AMT) AMT FROM AR_INV_HST_HDR INV WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 1 AND INV.INVOICE_TYPE IN ('R','P','Q') AND INV.SALES_PERSON_CODE IS NULL) INV1 ON 1 = 1
  38. LEFT JOIN
  39.   (SELECT SUM(INV.TOTAL_PRE_TAX_HOME_AMT) AMT FROM AR_INV_HST_HDR INV WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 2 AND INV.INVOICE_TYPE IN ('R','P','Q') AND INV.SALES_PERSON_CODE IS NULL) INV2 ON 1 = 1
  40. LEFT JOIN
  41.   (SELECT SUM(INV.TOTAL_PRE_TAX_HOME_AMT) AMT FROM AR_INV_HST_HDR INV WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 3 AND INV.INVOICE_TYPE IN ('R','P','Q') AND INV.SALES_PERSON_CODE IS NULL) INV3 ON 1 = 1
  42. LEFT JOIN
  43.   (SELECT SUM(INV.TOTAL_PRE_TAX_HOME_AMT) AMT FROM AR_INV_HST_HDR INV WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 4 AND INV.INVOICE_TYPE IN ('R','P','Q') AND INV.SALES_PERSON_CODE IS NULL) INV4 ON 1 = 1
  44. LEFT JOIN
  45.   (SELECT SUM(INV.TOTAL_PRE_TAX_HOME_AMT) AMT FROM AR_INV_HST_HDR INV WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 5 AND INV.INVOICE_TYPE IN ('R','P','Q') AND INV.SALES_PERSON_CODE IS NULL) INV5 ON 1 = 1
  46. LEFT JOIN
  47.   (SELECT SUM(INV.TOTAL_PRE_TAX_HOME_AMT) AMT FROM AR_INV_HST_HDR INV WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 6 AND INV.INVOICE_TYPE IN ('R','P','Q') AND INV.SALES_PERSON_CODE IS NULL) INV6 ON 1 = 1
  48. LEFT JOIN
  49.   (SELECT SUM(INV.TOTAL_PRE_TAX_HOME_AMT) AMT FROM AR_INV_HST_HDR INV WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 7 AND INV.INVOICE_TYPE IN ('R','P','Q') AND INV.SALES_PERSON_CODE IS NULL) INV7 ON 1 = 1
  50. LEFT JOIN
  51.   (SELECT SUM(INV.TOTAL_PRE_TAX_HOME_AMT) AMT FROM AR_INV_HST_HDR INV WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 8 AND INV.INVOICE_TYPE IN ('R','P','Q') AND INV.SALES_PERSON_CODE IS NULL) INV8 ON 1 = 1
  52. LEFT JOIN
  53.   (SELECT SUM(INV.TOTAL_PRE_TAX_HOME_AMT) AMT FROM AR_INV_HST_HDR INV WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 9 AND INV.INVOICE_TYPE IN ('R','P','Q') AND INV.SALES_PERSON_CODE IS NULL) INV9 ON 1 = 1
  54. LEFT JOIN
  55.   (SELECT SUM(INV.TOTAL_PRE_TAX_HOME_AMT) AMT FROM AR_INV_HST_HDR INV WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 10 AND INV.INVOICE_TYPE IN ('R','P','Q') AND INV.SALES_PERSON_CODE IS NULL) INV10 ON 1 = 1
  56. LEFT JOIN
  57.   (SELECT SUM(INV.TOTAL_PRE_TAX_HOME_AMT) AMT FROM AR_INV_HST_HDR INV WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 11 AND INV.INVOICE_TYPE IN ('R','P','Q') AND INV.SALES_PERSON_CODE IS NULL) INV11 ON 1 = 1
  58. LEFT JOIN
  59.   (SELECT SUM(INV.TOTAL_PRE_TAX_HOME_AMT) AMT FROM AR_INV_HST_HDR INV WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 12 AND INV.INVOICE_TYPE IN ('R','P','Q') AND INV.SALES_PERSON_CODE IS NULL) INV12 ON 1 = 1
  60. LEFT JOIN
  61.   (SELECT SUM(INV.TOTAL_PRE_TAX_HOME_AMT) AMT FROM AR_INV_HST_HDR INV WHERE INV.YEAR_POSTED_TO = ($P{FINANCIAL_YEAR} - 3) AND INV.INVOICE_TYPE IN ('R','P','Q') AND INV.SALES_PERSON_CODE IS NULL) INV_3_PAST ON 1 = 1
  62. LEFT JOIN
  63.   (SELECT SUM(INV.TOTAL_PRE_TAX_HOME_AMT) AMT FROM AR_INV_HST_HDR INV WHERE INV.YEAR_POSTED_TO = ($P{FINANCIAL_YEAR} - 2) AND INV.INVOICE_TYPE IN ('R','P','Q') AND INV.SALES_PERSON_CODE IS NULL) INV_2_PAST ON 1 = 1
  64. LEFT JOIN
  65.   (SELECT SUM(INV.TOTAL_PRE_TAX_HOME_AMT) AMT FROM AR_INV_HST_HDR INV WHERE INV.YEAR_POSTED_TO = ($P{FINANCIAL_YEAR} - 1) AND INV.INVOICE_TYPE IN ('R','P','Q') AND INV.SALES_PERSON_CODE IS NULL) INV_1_PAST ON 1 = 1
  66. LEFT JOIN
  67.   (SELECT SUM(COALESCE(CB.TOTAL_ACTUAL_COST_HOME,0)) AMT FROM PJ_EST_OST_HDR PJ LEFT JOIN PJ_EST_OST_CST_BRKDWN CB ON PJ.PROJECT_NO = CB.PROJECT_NO WHERE CB.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND CB.PERIOD_POSTED_TO = 1 AND PJ.SALES_PERSON_CODE IS NULL) PJ1 ON 1 = 1
  68. LEFT JOIN
  69.   (SELECT SUM(COALESCE(CB.TOTAL_ACTUAL_COST_HOME,0)) AMT FROM PJ_EST_OST_HDR PJ LEFT JOIN PJ_EST_OST_CST_BRKDWN CB ON PJ.PROJECT_NO = CB.PROJECT_NO WHERE CB.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND CB.PERIOD_POSTED_TO = 2 AND PJ.SALES_PERSON_CODE IS NULL) PJ2 ON 1 = 1
  70. LEFT JOIN
  71.   (SELECT SUM(COALESCE(CB.TOTAL_ACTUAL_COST_HOME,0)) AMT FROM PJ_EST_OST_HDR PJ LEFT JOIN PJ_EST_OST_CST_BRKDWN CB ON PJ.PROJECT_NO = CB.PROJECT_NO WHERE CB.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND CB.PERIOD_POSTED_TO = 3 AND PJ.SALES_PERSON_CODE IS NULL) PJ3 ON 1 = 1
  72. LEFT JOIN
  73.   (SELECT SUM(COALESCE(CB.TOTAL_ACTUAL_COST_HOME,0)) AMT FROM PJ_EST_OST_HDR PJ LEFT JOIN PJ_EST_OST_CST_BRKDWN CB ON PJ.PROJECT_NO = CB.PROJECT_NO WHERE CB.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND CB.PERIOD_POSTED_TO = 4 AND PJ.SALES_PERSON_CODE IS NULL) PJ4 ON 1 = 1
  74. LEFT JOIN
  75.   (SELECT SUM(COALESCE(CB.TOTAL_ACTUAL_COST_HOME,0)) AMT FROM PJ_EST_OST_HDR PJ LEFT JOIN PJ_EST_OST_CST_BRKDWN CB ON PJ.PROJECT_NO = CB.PROJECT_NO WHERE CB.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND CB.PERIOD_POSTED_TO = 5 AND PJ.SALES_PERSON_CODE IS NULL) PJ5 ON 1 = 1
  76. LEFT JOIN
  77.   (SELECT SUM(COALESCE(CB.TOTAL_ACTUAL_COST_HOME,0)) AMT FROM PJ_EST_OST_HDR PJ LEFT JOIN PJ_EST_OST_CST_BRKDWN CB ON PJ.PROJECT_NO = CB.PROJECT_NO WHERE CB.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND CB.PERIOD_POSTED_TO = 6 AND PJ.SALES_PERSON_CODE IS NULL) PJ6 ON 1 = 1
  78. LEFT JOIN
  79.   (SELECT SUM(COALESCE(CB.TOTAL_ACTUAL_COST_HOME,0)) AMT FROM PJ_EST_OST_HDR PJ LEFT JOIN PJ_EST_OST_CST_BRKDWN CB ON PJ.PROJECT_NO = CB.PROJECT_NO WHERE CB.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND CB.PERIOD_POSTED_TO = 7 AND PJ.SALES_PERSON_CODE IS NULL) PJ7 ON 1 = 1
  80. LEFT JOIN
  81.   (SELECT SUM(COALESCE(CB.TOTAL_ACTUAL_COST_HOME,0)) AMT FROM PJ_EST_OST_HDR PJ LEFT JOIN PJ_EST_OST_CST_BRKDWN CB ON PJ.PROJECT_NO = CB.PROJECT_NO WHERE CB.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND CB.PERIOD_POSTED_TO = 8 AND PJ.SALES_PERSON_CODE IS NULL) PJ8 ON 1 = 1
  82. LEFT JOIN
  83.   (SELECT SUM(COALESCE(CB.TOTAL_ACTUAL_COST_HOME,0)) AMT FROM PJ_EST_OST_HDR PJ LEFT JOIN PJ_EST_OST_CST_BRKDWN CB ON PJ.PROJECT_NO = CB.PROJECT_NO WHERE CB.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND CB.PERIOD_POSTED_TO = 9 AND PJ.SALES_PERSON_CODE IS NULL) PJ9 ON 1 = 1
  84. LEFT JOIN
  85.   (SELECT SUM(COALESCE(CB.TOTAL_ACTUAL_COST_HOME,0)) AMT FROM PJ_EST_OST_HDR PJ LEFT JOIN PJ_EST_OST_CST_BRKDWN CB ON PJ.PROJECT_NO = CB.PROJECT_NO WHERE CB.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND CB.PERIOD_POSTED_TO = 10 AND PJ.SALES_PERSON_CODE IS NULL) PJ10 ON 1 = 1
  86. LEFT JOIN
  87.   (SELECT SUM(COALESCE(CB.TOTAL_ACTUAL_COST_HOME,0)) AMT FROM PJ_EST_OST_HDR PJ LEFT JOIN PJ_EST_OST_CST_BRKDWN CB ON PJ.PROJECT_NO = CB.PROJECT_NO WHERE CB.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND CB.PERIOD_POSTED_TO = 11 AND PJ.SALES_PERSON_CODE IS NULL) PJ11 ON 1 = 1
  88. LEFT JOIN
  89.   (SELECT SUM(COALESCE(CB.TOTAL_ACTUAL_COST_HOME,0)) AMT FROM PJ_EST_OST_HDR PJ LEFT JOIN PJ_EST_OST_CST_BRKDWN CB ON PJ.PROJECT_NO = CB.PROJECT_NO WHERE CB.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND CB.PERIOD_POSTED_TO = 12 AND PJ.SALES_PERSON_CODE IS NULL) PJ12 ON 1 = 1
  90. LEFT JOIN
  91.   (SELECT SUM(COALESCE(CB.TOTAL_ACTUAL_COST_HOME,0)) AMT FROM PJ_EST_OST_HDR PJ LEFT JOIN PJ_EST_OST_CST_BRKDWN CB ON PJ.PROJECT_NO = CB.PROJECT_NO WHERE CB.YEAR_POSTED_TO = ($P{FINANCIAL_YEAR} - 3) AND PJ.SALES_PERSON_CODE IS NULL) PJ_3_PAST ON 1 = 1
  92. LEFT JOIN
  93.   (SELECT SUM(COALESCE(CB.TOTAL_ACTUAL_COST_HOME,0)) AMT FROM PJ_EST_OST_HDR PJ LEFT JOIN PJ_EST_OST_CST_BRKDWN CB ON PJ.PROJECT_NO = CB.PROJECT_NO WHERE CB.YEAR_POSTED_TO = ($P{FINANCIAL_YEAR} - 2) AND PJ.SALES_PERSON_CODE IS NULL) PJ_2_PAST ON 1 = 1
  94. LEFT JOIN
  95.   (SELECT SUM(COALESCE(CB.TOTAL_ACTUAL_COST_HOME,0)) AMT FROM PJ_EST_OST_HDR PJ LEFT JOIN PJ_EST_OST_CST_BRKDWN CB ON PJ.PROJECT_NO = CB.PROJECT_NO WHERE CB.YEAR_POSTED_TO = ($P{FINANCIAL_YEAR} - 2) AND PJ.SALES_PERSON_CODE IS NULL) PJ_1_PAST ON 1 = 1
  96. LEFT JOIN
  97.   (SELECT SUM(DET.PRE_TAX_EXTENDED_HOME_AMT) AMT FROM AR_INV_HST_HDR INV INNER JOIN AR_INV_HST_DET DET ON INV.INVOICE_NO = DET.INVOICE_NO WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 1 AND INV.INVOICE_TYPE = 'D' AND DET.SERVICE_CODE IN ('INTERCO','51000') AND INV.SALES_PERSON_CODE IS NULL) D_INV1 ON 1 = 1
  98. LEFT JOIN
  99.   (SELECT SUM(DET.PRE_TAX_EXTENDED_HOME_AMT) AMT FROM AR_INV_HST_HDR INV INNER JOIN AR_INV_HST_DET DET ON INV.INVOICE_NO = DET.INVOICE_NO WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 2 AND INV.INVOICE_TYPE = 'D' AND DET.SERVICE_CODE IN ('INTERCO','51000') AND INV.SALES_PERSON_CODE IS NULL) D_INV2 ON 1 = 1
  100. LEFT JOIN
  101.   (SELECT SUM(DET.PRE_TAX_EXTENDED_HOME_AMT) AMT FROM AR_INV_HST_HDR INV INNER JOIN AR_INV_HST_DET DET ON INV.INVOICE_NO = DET.INVOICE_NO WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 3 AND INV.INVOICE_TYPE = 'D' AND DET.SERVICE_CODE IN ('INTERCO','51000') AND INV.SALES_PERSON_CODE IS NULL) D_INV3 ON 1 = 1
  102. LEFT JOIN
  103.   (SELECT SUM(DET.PRE_TAX_EXTENDED_HOME_AMT) AMT FROM AR_INV_HST_HDR INV INNER JOIN AR_INV_HST_DET DET ON INV.INVOICE_NO = DET.INVOICE_NO WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 4 AND INV.INVOICE_TYPE = 'D' AND DET.SERVICE_CODE IN ('INTERCO','51000') AND INV.SALES_PERSON_CODE IS NULL) D_INV4 ON 1 = 1
  104. LEFT JOIN
  105.   (SELECT SUM(DET.PRE_TAX_EXTENDED_HOME_AMT) AMT FROM AR_INV_HST_HDR INV INNER JOIN AR_INV_HST_DET DET ON INV.INVOICE_NO = DET.INVOICE_NO WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 5 AND INV.INVOICE_TYPE = 'D' AND DET.SERVICE_CODE IN ('INTERCO','51000') AND INV.SALES_PERSON_CODE IS NULL) D_INV5 ON 1 = 1
  106. LEFT JOIN
  107.   (SELECT SUM(DET.PRE_TAX_EXTENDED_HOME_AMT) AMT FROM AR_INV_HST_HDR INV INNER JOIN AR_INV_HST_DET DET ON INV.INVOICE_NO = DET.INVOICE_NO WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 6 AND INV.INVOICE_TYPE = 'D' AND DET.SERVICE_CODE IN ('INTERCO','51000') AND INV.SALES_PERSON_CODE IS NULL) D_INV6 ON 1 = 1
  108. LEFT JOIN
  109.   (SELECT SUM(DET.PRE_TAX_EXTENDED_HOME_AMT) AMT FROM AR_INV_HST_HDR INV INNER JOIN AR_INV_HST_DET DET ON INV.INVOICE_NO = DET.INVOICE_NO WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 7 AND INV.INVOICE_TYPE = 'D' AND DET.SERVICE_CODE IN ('INTERCO','51000') AND INV.SALES_PERSON_CODE IS NULL) D_INV7 ON 1 = 1
  110. LEFT JOIN
  111.   (SELECT SUM(DET.PRE_TAX_EXTENDED_HOME_AMT) AMT FROM AR_INV_HST_HDR INV INNER JOIN AR_INV_HST_DET DET ON INV.INVOICE_NO = DET.INVOICE_NO WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 8 AND INV.INVOICE_TYPE = 'D' AND DET.SERVICE_CODE IN ('INTERCO','51000') AND INV.SALES_PERSON_CODE IS NULL) D_INV8 ON 1 = 1
  112. LEFT JOIN
  113.   (SELECT SUM(DET.PRE_TAX_EXTENDED_HOME_AMT) AMT FROM AR_INV_HST_HDR INV INNER JOIN AR_INV_HST_DET DET ON INV.INVOICE_NO = DET.INVOICE_NO WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 9 AND INV.INVOICE_TYPE = 'D' AND DET.SERVICE_CODE IN ('INTERCO','51000') AND INV.SALES_PERSON_CODE IS NULL) D_INV9 ON 1 = 1
  114. LEFT JOIN
  115.   (SELECT SUM(DET.PRE_TAX_EXTENDED_HOME_AMT) AMT FROM AR_INV_HST_HDR INV INNER JOIN AR_INV_HST_DET DET ON INV.INVOICE_NO = DET.INVOICE_NO WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 10 AND INV.INVOICE_TYPE = 'D' AND DET.SERVICE_CODE IN ('INTERCO','51000') AND INV.SALES_PERSON_CODE IS NULL) D_INV10 ON 1 = 1
  116. LEFT JOIN
  117.   (SELECT SUM(DET.PRE_TAX_EXTENDED_HOME_AMT) AMT FROM AR_INV_HST_HDR INV INNER JOIN AR_INV_HST_DET DET ON INV.INVOICE_NO = DET.INVOICE_NO WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 11 AND INV.INVOICE_TYPE = 'D' AND DET.SERVICE_CODE IN ('INTERCO','51000') AND INV.SALES_PERSON_CODE IS NULL) D_INV11 ON 1 = 1
  118. LEFT JOIN
  119.   (SELECT SUM(DET.PRE_TAX_EXTENDED_HOME_AMT) AMT FROM AR_INV_HST_HDR INV INNER JOIN AR_INV_HST_DET DET ON INV.INVOICE_NO = DET.INVOICE_NO WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 12 AND INV.INVOICE_TYPE = 'D' AND DET.SERVICE_CODE IN ('INTERCO','51000') AND INV.SALES_PERSON_CODE IS NULL) D_INV12 ON 1 = 1
  120. LEFT JOIN
  121.   (SELECT SUM(DET.PRE_TAX_EXTENDED_HOME_AMT) AMT FROM AR_INV_HST_HDR INV INNER JOIN AR_INV_HST_DET DET ON INV.INVOICE_NO = DET.INVOICE_NO WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} - 3 AND INV.INVOICE_TYPE = 'D' AND DET.SERVICE_CODE IN ('INTERCO','51000') AND INV.SALES_PERSON_CODE IS NULL) D_INV_3_PAST ON 1 = 1
  122. LEFT JOIN
  123.   (SELECT SUM(DET.PRE_TAX_EXTENDED_HOME_AMT) AMT FROM AR_INV_HST_HDR INV INNER JOIN AR_INV_HST_DET DET ON INV.INVOICE_NO = DET.INVOICE_NO WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} - 2 AND INV.INVOICE_TYPE = 'D' AND DET.SERVICE_CODE IN ('INTERCO','51000') AND INV.SALES_PERSON_CODE IS NULL) D_INV_2_PAST ON 1 = 1
  124. LEFT JOIN
  125.   (SELECT SUM(DET.PRE_TAX_EXTENDED_HOME_AMT) AMT FROM AR_INV_HST_HDR INV INNER JOIN AR_INV_HST_DET DET ON INV.INVOICE_NO = DET.INVOICE_NO WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} - 1 AND INV.INVOICE_TYPE = 'D' AND DET.SERVICE_CODE IN ('INTERCO','51000') AND INV.SALES_PERSON_CODE IS NULL) D_INV_1_PAST ON 1 = 1)
  126. UNION ALL
  127. (SELECT
  128.   INV.SALES_PERSON_CODE SALES_PERSON_CODE
  129. , EMP.EMPLOYEE_NAME EMPLOYEE_NAME
  130. , COALESCE(INV_3_PAST.AMT,0) + COALESCE(D_INV_3_PAST.AMT,0) SALES_AMT_3_PAST
  131. , COALESCE(INV_2_PAST.AMT,0) + COALESCE(D_INV_2_PAST.AMT,0) SALES_AMT_2_PAST
  132. , COALESCE(INV_1_PAST.AMT,0) + COALESCE(D_INV_1_PAST.AMT,0) SALES_AMT_1_PAST
  133. , COALESCE(INV1.AMT,0) + COALESCE(D_INV1.AMT,0) SALES_AMT1
  134. , COALESCE(INV2.AMT,0) + COALESCE(D_INV2.AMT,0) SALES_AMT2
  135. , COALESCE(INV3.AMT,0) + COALESCE(D_INV3.AMT,0) SALES_AMT3
  136. , COALESCE(INV4.AMT,0) + COALESCE(D_INV4.AMT,0) SALES_AMT4
  137. , COALESCE(INV5.AMT,0) + COALESCE(D_INV5.AMT,0) SALES_AMT5
  138. , COALESCE(INV6.AMT,0) + COALESCE(D_INV6.AMT,0) SALES_AMT6
  139. , COALESCE(INV7.AMT,0) + COALESCE(D_INV7.AMT,0) SALES_AMT7
  140. , COALESCE(INV8.AMT,0) + COALESCE(D_INV8.AMT,0) SALES_AMT8
  141. , COALESCE(INV9.AMT,0) + COALESCE(D_INV9.AMT,0) SALES_AMT9
  142. , COALESCE(INV10.AMT,0) + COALESCE(D_INV10.AMT,0) SALES_AMT10
  143. , COALESCE(INV11.AMT,0) + COALESCE(D_INV11.AMT,0) SALES_AMT11
  144. , COALESCE(INV12.AMT,0) + COALESCE(D_INV12.AMT,0) SALES_AMT12
  145. , COALESCE(INV_3_PAST.AMT,0) + COALESCE(D_INV_3_PAST.AMT,0) - COALESCE(PJ_3_PAST.AMT,0) PROFIT_AMT_3_PAST
  146. , COALESCE(INV_2_PAST.AMT,0) + COALESCE(D_INV_2_PAST.AMT,0) - COALESCE(PJ_2_PAST.AMT,0) PROFIT_AMT_2_PAST
  147. , COALESCE(INV_1_PAST.AMT,0) + COALESCE(D_INV_1_PAST.AMT,0) - COALESCE(PJ_1_PAST.AMT,0) PROFIT_AMT_1_PAST
  148. , COALESCE(INV1.AMT,0) + COALESCE(D_INV1.AMT,0) - COALESCE(PJ1.AMT,0) PROFIT_AMT1
  149. , COALESCE(INV2.AMT,0) + COALESCE(D_INV2.AMT,0) - COALESCE(PJ2.AMT,0) PROFIT_AMT2
  150. , COALESCE(INV3.AMT,0) + COALESCE(D_INV3.AMT,0) - COALESCE(PJ3.AMT,0) PROFIT_AMT3
  151. , COALESCE(INV4.AMT,0) + COALESCE(D_INV4.AMT,0) - COALESCE(PJ4.AMT,0) PROFIT_AMT4
  152. , COALESCE(INV5.AMT,0) + COALESCE(D_INV5.AMT,0) - COALESCE(PJ5.AMT,0) PROFIT_AMT5
  153. , COALESCE(INV6.AMT,0) + COALESCE(D_INV6.AMT,0) - COALESCE(PJ6.AMT,0) PROFIT_AMT6
  154. , COALESCE(INV7.AMT,0) + COALESCE(D_INV7.AMT,0) - COALESCE(PJ7.AMT,0) PROFIT_AMT7
  155. , COALESCE(INV8.AMT,0) + COALESCE(D_INV8.AMT,0) - COALESCE(PJ8.AMT,0) PROFIT_AMT8
  156. , COALESCE(INV9.AMT,0) + COALESCE(D_INV9.AMT,0) - COALESCE(PJ9.AMT,0) PROFIT_AMT9
  157. , COALESCE(INV10.AMT,0) + COALESCE(D_INV10.AMT,0) - COALESCE(PJ10.AMT,0) PROFIT_AMT10
  158. , COALESCE(INV11.AMT,0) + COALESCE(D_INV11.AMT,0) - COALESCE(PJ11.AMT,0) PROFIT_AMT11
  159. , COALESCE(INV12.AMT,0) + COALESCE(D_INV12.AMT,0) - COALESCE(PJ12.AMT,0) PROFIT_AMT12
  160. FROM
  161.   (SELECT DISTINCT INV.SALES_PERSON_CODE FROM AR_INV_HST_HDR INV LEFT JOIN AR_INV_HST_DET DET ON INV.INVOICE_NO = DET.INVOICE_NO WHERE INV.YEAR_POSTED_TO IN ($P{FINANCIAL_YEAR}, $P{FINANCIAL_YEAR}-1, $P{FINANCIAL_YEAR}-2, $P{FINANCIAL_YEAR}-3)  AND (INV.INVOICE_TYPE IN ('R','P','Q') OR (INV.INVOICE_TYPE = 'D' AND DET.SERVICE_CODE IN ('INTERCO','51000'))) AND INV.SALES_PERSON_CODE IS NOT NULL) INV
  162. LEFT JOIN
  163.   (SELECT INV.SALES_PERSON_CODE, SUM(INV.TOTAL_PRE_TAX_HOME_AMT) AMT FROM AR_INV_HST_HDR INV WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 1 AND INV.INVOICE_TYPE IN ('R','P','Q') AND INV.SALES_PERSON_CODE IS NOT NULL GROUP BY INV.SALES_PERSON_CODE) INV1 ON INV.SALES_PERSON_CODE = INV1.SALES_PERSON_CODE
  164. LEFT JOIN
  165.   (SELECT INV.SALES_PERSON_CODE, SUM(INV.TOTAL_PRE_TAX_HOME_AMT) AMT FROM AR_INV_HST_HDR INV WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 2 AND INV.INVOICE_TYPE IN ('R','P','Q') AND INV.SALES_PERSON_CODE IS NOT NULL GROUP BY INV.SALES_PERSON_CODE) INV2 ON INV.SALES_PERSON_CODE = INV2.SALES_PERSON_CODE
  166. LEFT JOIN
  167.   (SELECT INV.SALES_PERSON_CODE, SUM(INV.TOTAL_PRE_TAX_HOME_AMT) AMT FROM AR_INV_HST_HDR INV WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 3 AND INV.INVOICE_TYPE IN ('R','P','Q') AND INV.SALES_PERSON_CODE IS NOT NULL GROUP BY INV.SALES_PERSON_CODE) INV3 ON INV.SALES_PERSON_CODE = INV3.SALES_PERSON_CODE
  168. LEFT JOIN
  169.   (SELECT INV.SALES_PERSON_CODE, SUM(INV.TOTAL_PRE_TAX_HOME_AMT) AMT FROM AR_INV_HST_HDR INV WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 4 AND INV.INVOICE_TYPE IN ('R','P','Q') AND INV.SALES_PERSON_CODE IS NOT NULL GROUP BY INV.SALES_PERSON_CODE) INV4 ON INV.SALES_PERSON_CODE = INV4.SALES_PERSON_CODE
  170. LEFT JOIN
  171.   (SELECT INV.SALES_PERSON_CODE, SUM(INV.TOTAL_PRE_TAX_HOME_AMT) AMT FROM AR_INV_HST_HDR INV WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 5 AND INV.INVOICE_TYPE IN ('R','P','Q') AND INV.SALES_PERSON_CODE IS NOT NULL GROUP BY INV.SALES_PERSON_CODE) INV5 ON INV.SALES_PERSON_CODE = INV5.SALES_PERSON_CODE
  172. LEFT JOIN
  173.   (SELECT INV.SALES_PERSON_CODE, SUM(INV.TOTAL_PRE_TAX_HOME_AMT) AMT FROM AR_INV_HST_HDR INV WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 6 AND INV.INVOICE_TYPE IN ('R','P','Q') AND INV.SALES_PERSON_CODE IS NOT NULL GROUP BY INV.SALES_PERSON_CODE) INV6 ON INV.SALES_PERSON_CODE = INV6.SALES_PERSON_CODE
  174. LEFT JOIN
  175.   (SELECT INV.SALES_PERSON_CODE, SUM(INV.TOTAL_PRE_TAX_HOME_AMT) AMT FROM AR_INV_HST_HDR INV WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 7 AND INV.INVOICE_TYPE IN ('R','P','Q') AND INV.SALES_PERSON_CODE IS NOT NULL GROUP BY INV.SALES_PERSON_CODE) INV7 ON INV.SALES_PERSON_CODE = INV7.SALES_PERSON_CODE
  176. LEFT JOIN
  177.   (SELECT INV.SALES_PERSON_CODE, SUM(INV.TOTAL_PRE_TAX_HOME_AMT) AMT FROM AR_INV_HST_HDR INV WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 8 AND INV.INVOICE_TYPE IN ('R','P','Q') AND INV.SALES_PERSON_CODE IS NOT NULL GROUP BY INV.SALES_PERSON_CODE) INV8 ON INV.SALES_PERSON_CODE = INV8.SALES_PERSON_CODE
  178. LEFT JOIN
  179.   (SELECT INV.SALES_PERSON_CODE, SUM(INV.TOTAL_PRE_TAX_HOME_AMT) AMT FROM AR_INV_HST_HDR INV WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 9 AND INV.INVOICE_TYPE IN ('R','P','Q') AND INV.SALES_PERSON_CODE IS NOT NULL GROUP BY INV.SALES_PERSON_CODE) INV9 ON INV.SALES_PERSON_CODE = INV9.SALES_PERSON_CODE
  180. LEFT JOIN
  181.   (SELECT INV.SALES_PERSON_CODE, SUM(INV.TOTAL_PRE_TAX_HOME_AMT) AMT FROM AR_INV_HST_HDR INV WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 10 AND INV.INVOICE_TYPE IN ('R','P','Q') AND INV.SALES_PERSON_CODE IS NOT NULL GROUP BY INV.SALES_PERSON_CODE) INV10 ON INV.SALES_PERSON_CODE = INV10.SALES_PERSON_CODE
  182. LEFT JOIN
  183.   (SELECT INV.SALES_PERSON_CODE, SUM(INV.TOTAL_PRE_TAX_HOME_AMT) AMT FROM AR_INV_HST_HDR INV WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 11 AND INV.INVOICE_TYPE IN ('R','P','Q') AND INV.SALES_PERSON_CODE IS NOT NULL GROUP BY INV.SALES_PERSON_CODE) INV11 ON INV.SALES_PERSON_CODE = INV11.SALES_PERSON_CODE
  184. LEFT JOIN
  185.   (SELECT INV.SALES_PERSON_CODE, SUM(INV.TOTAL_PRE_TAX_HOME_AMT) AMT FROM AR_INV_HST_HDR INV WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 12 AND INV.INVOICE_TYPE IN ('R','P','Q') AND INV.SALES_PERSON_CODE IS NOT NULL GROUP BY INV.SALES_PERSON_CODE) INV12 ON INV.SALES_PERSON_CODE = INV12.SALES_PERSON_CODE
  186. LEFT JOIN
  187.   (SELECT INV.SALES_PERSON_CODE, SUM(INV.TOTAL_PRE_TAX_HOME_AMT) AMT FROM AR_INV_HST_HDR INV WHERE INV.YEAR_POSTED_TO = ($P{FINANCIAL_YEAR} - 3) AND INV.INVOICE_TYPE IN ('R','P','Q') AND INV.SALES_PERSON_CODE IS NOT NULL GROUP BY INV.SALES_PERSON_CODE) INV_3_PAST ON INV.SALES_PERSON_CODE = INV_3_PAST.SALES_PERSON_CODE
  188. LEFT JOIN
  189.   (SELECT INV.SALES_PERSON_CODE, SUM(INV.TOTAL_PRE_TAX_HOME_AMT) AMT FROM AR_INV_HST_HDR INV WHERE INV.YEAR_POSTED_TO = ($P{FINANCIAL_YEAR} - 2) AND INV.INVOICE_TYPE IN ('R','P','Q') AND INV.SALES_PERSON_CODE IS NOT NULL GROUP BY INV.SALES_PERSON_CODE) INV_2_PAST ON INV.SALES_PERSON_CODE = INV_2_PAST.SALES_PERSON_CODE
  190. LEFT JOIN
  191.   (SELECT INV.SALES_PERSON_CODE, SUM(INV.TOTAL_PRE_TAX_HOME_AMT) AMT FROM AR_INV_HST_HDR INV WHERE INV.YEAR_POSTED_TO = ($P{FINANCIAL_YEAR} - 1) AND INV.INVOICE_TYPE IN ('R','P','Q') AND INV.SALES_PERSON_CODE IS NOT NULL GROUP BY INV.SALES_PERSON_CODE) INV_1_PAST ON INV.SALES_PERSON_CODE = INV_1_PAST.SALES_PERSON_CODE
  192. LEFT JOIN
  193.   (SELECT PJ.SALES_PERSON_CODE, SUM(COALESCE(CB.TOTAL_ACTUAL_COST_HOME,0)) AMT FROM PJ_EST_OST_HDR PJ LEFT JOIN PJ_EST_OST_CST_BRKDWN CB ON PJ.PROJECT_NO = CB.PROJECT_NO WHERE CB.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND CB.PERIOD_POSTED_TO = 1 AND PJ.SALES_PERSON_CODE IS NOT NULL GROUP BY PJ.SALES_PERSON_CODE) PJ1 ON INV.SALES_PERSON_CODE = PJ1.SALES_PERSON_CODE
  194. LEFT JOIN
  195.   (SELECT PJ.SALES_PERSON_CODE, SUM(COALESCE(CB.TOTAL_ACTUAL_COST_HOME,0)) AMT FROM PJ_EST_OST_HDR PJ LEFT JOIN PJ_EST_OST_CST_BRKDWN CB ON PJ.PROJECT_NO = CB.PROJECT_NO WHERE CB.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND CB.PERIOD_POSTED_TO = 2 AND PJ.SALES_PERSON_CODE IS NOT NULL GROUP BY PJ.SALES_PERSON_CODE) PJ2 ON INV.SALES_PERSON_CODE = PJ2.SALES_PERSON_CODE
  196. LEFT JOIN
  197.   (SELECT PJ.SALES_PERSON_CODE, SUM(COALESCE(CB.TOTAL_ACTUAL_COST_HOME,0)) AMT FROM PJ_EST_OST_HDR PJ LEFT JOIN PJ_EST_OST_CST_BRKDWN CB ON PJ.PROJECT_NO = CB.PROJECT_NO WHERE CB.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND CB.PERIOD_POSTED_TO = 3 AND PJ.SALES_PERSON_CODE IS NOT NULL GROUP BY PJ.SALES_PERSON_CODE) PJ3 ON INV.SALES_PERSON_CODE = PJ3.SALES_PERSON_CODE
  198. LEFT JOIN
  199.   (SELECT PJ.SALES_PERSON_CODE, SUM(COALESCE(CB.TOTAL_ACTUAL_COST_HOME,0)) AMT FROM PJ_EST_OST_HDR PJ LEFT JOIN PJ_EST_OST_CST_BRKDWN CB ON PJ.PROJECT_NO = CB.PROJECT_NO WHERE CB.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND CB.PERIOD_POSTED_TO = 4 AND PJ.SALES_PERSON_CODE IS NOT NULL GROUP BY PJ.SALES_PERSON_CODE) PJ4 ON INV.SALES_PERSON_CODE = PJ4.SALES_PERSON_CODE
  200. LEFT JOIN
  201.   (SELECT PJ.SALES_PERSON_CODE, SUM(COALESCE(CB.TOTAL_ACTUAL_COST_HOME,0)) AMT FROM PJ_EST_OST_HDR PJ LEFT JOIN PJ_EST_OST_CST_BRKDWN CB ON PJ.PROJECT_NO = CB.PROJECT_NO WHERE CB.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND CB.PERIOD_POSTED_TO = 5 AND PJ.SALES_PERSON_CODE IS NOT NULL GROUP BY PJ.SALES_PERSON_CODE) PJ5 ON INV.SALES_PERSON_CODE = PJ5.SALES_PERSON_CODE
  202. LEFT JOIN
  203.   (SELECT PJ.SALES_PERSON_CODE, SUM(COALESCE(CB.TOTAL_ACTUAL_COST_HOME,0)) AMT FROM PJ_EST_OST_HDR PJ LEFT JOIN PJ_EST_OST_CST_BRKDWN CB ON PJ.PROJECT_NO = CB.PROJECT_NO WHERE CB.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND CB.PERIOD_POSTED_TO = 6 AND PJ.SALES_PERSON_CODE IS NOT NULL GROUP BY PJ.SALES_PERSON_CODE) PJ6 ON INV.SALES_PERSON_CODE = PJ6.SALES_PERSON_CODE
  204. LEFT JOIN
  205.   (SELECT PJ.SALES_PERSON_CODE, SUM(COALESCE(CB.TOTAL_ACTUAL_COST_HOME,0)) AMT FROM PJ_EST_OST_HDR PJ LEFT JOIN PJ_EST_OST_CST_BRKDWN CB ON PJ.PROJECT_NO = CB.PROJECT_NO WHERE CB.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND CB.PERIOD_POSTED_TO = 7 AND PJ.SALES_PERSON_CODE IS NOT NULL GROUP BY PJ.SALES_PERSON_CODE) PJ7 ON INV.SALES_PERSON_CODE = PJ7.SALES_PERSON_CODE
  206. LEFT JOIN
  207.   (SELECT PJ.SALES_PERSON_CODE, SUM(COALESCE(CB.TOTAL_ACTUAL_COST_HOME,0)) AMT FROM PJ_EST_OST_HDR PJ LEFT JOIN PJ_EST_OST_CST_BRKDWN CB ON PJ.PROJECT_NO = CB.PROJECT_NO WHERE CB.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND CB.PERIOD_POSTED_TO = 8 AND PJ.SALES_PERSON_CODE IS NOT NULL GROUP BY PJ.SALES_PERSON_CODE) PJ8 ON INV.SALES_PERSON_CODE = PJ8.SALES_PERSON_CODE
  208. LEFT JOIN
  209.   (SELECT PJ.SALES_PERSON_CODE, SUM(COALESCE(CB.TOTAL_ACTUAL_COST_HOME,0)) AMT FROM PJ_EST_OST_HDR PJ LEFT JOIN PJ_EST_OST_CST_BRKDWN CB ON PJ.PROJECT_NO = CB.PROJECT_NO WHERE CB.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND CB.PERIOD_POSTED_TO = 9 AND PJ.SALES_PERSON_CODE IS NOT NULL GROUP BY PJ.SALES_PERSON_CODE) PJ9 ON INV.SALES_PERSON_CODE = PJ9.SALES_PERSON_CODE
  210. LEFT JOIN
  211.   (SELECT PJ.SALES_PERSON_CODE, SUM(COALESCE(CB.TOTAL_ACTUAL_COST_HOME,0)) AMT FROM PJ_EST_OST_HDR PJ LEFT JOIN PJ_EST_OST_CST_BRKDWN CB ON PJ.PROJECT_NO = CB.PROJECT_NO WHERE CB.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND CB.PERIOD_POSTED_TO = 10 AND PJ.SALES_PERSON_CODE IS NOT NULL GROUP BY PJ.SALES_PERSON_CODE) PJ10 ON INV.SALES_PERSON_CODE = PJ10.SALES_PERSON_CODE
  212. LEFT JOIN
  213.   (SELECT PJ.SALES_PERSON_CODE, SUM(COALESCE(CB.TOTAL_ACTUAL_COST_HOME,0)) AMT FROM PJ_EST_OST_HDR PJ LEFT JOIN PJ_EST_OST_CST_BRKDWN CB ON PJ.PROJECT_NO = CB.PROJECT_NO WHERE CB.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND CB.PERIOD_POSTED_TO = 11 AND PJ.SALES_PERSON_CODE IS NOT NULL GROUP BY PJ.SALES_PERSON_CODE) PJ11 ON INV.SALES_PERSON_CODE = PJ11.SALES_PERSON_CODE
  214. LEFT JOIN
  215.   (SELECT PJ.SALES_PERSON_CODE, SUM(COALESCE(CB.TOTAL_ACTUAL_COST_HOME,0)) AMT FROM PJ_EST_OST_HDR PJ LEFT JOIN PJ_EST_OST_CST_BRKDWN CB ON PJ.PROJECT_NO = CB.PROJECT_NO WHERE CB.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND CB.PERIOD_POSTED_TO = 12 AND PJ.SALES_PERSON_CODE IS NOT NULL GROUP BY PJ.SALES_PERSON_CODE) PJ12 ON INV.SALES_PERSON_CODE = PJ12.SALES_PERSON_CODE
  216. LEFT JOIN
  217.   (SELECT PJ.SALES_PERSON_CODE, SUM(COALESCE(CB.TOTAL_ACTUAL_COST_HOME,0)) AMT FROM PJ_EST_OST_HDR PJ LEFT JOIN PJ_EST_OST_CST_BRKDWN CB ON PJ.PROJECT_NO = CB.PROJECT_NO WHERE CB.YEAR_POSTED_TO = ($P{FINANCIAL_YEAR} - 3) AND PJ.SALES_PERSON_CODE IS NOT NULL GROUP BY PJ.SALES_PERSON_CODE) PJ_3_PAST ON INV.SALES_PERSON_CODE = PJ_3_PAST.SALES_PERSON_CODE
  218. LEFT JOIN
  219.   (SELECT PJ.SALES_PERSON_CODE, SUM(COALESCE(CB.TOTAL_ACTUAL_COST_HOME,0)) AMT FROM PJ_EST_OST_HDR PJ LEFT JOIN PJ_EST_OST_CST_BRKDWN CB ON PJ.PROJECT_NO = CB.PROJECT_NO WHERE CB.YEAR_POSTED_TO = ($P{FINANCIAL_YEAR} - 2) AND PJ.SALES_PERSON_CODE IS NOT NULL GROUP BY PJ.SALES_PERSON_CODE) PJ_2_PAST ON INV.SALES_PERSON_CODE = PJ_2_PAST.SALES_PERSON_CODE
  220. LEFT JOIN
  221.   (SELECT PJ.SALES_PERSON_CODE, SUM(COALESCE(CB.TOTAL_ACTUAL_COST_HOME,0)) AMT FROM PJ_EST_OST_HDR PJ LEFT JOIN PJ_EST_OST_CST_BRKDWN CB ON PJ.PROJECT_NO = CB.PROJECT_NO WHERE CB.YEAR_POSTED_TO = ($P{FINANCIAL_YEAR} - 2) AND PJ.SALES_PERSON_CODE IS NOT NULL GROUP BY PJ.SALES_PERSON_CODE) PJ_1_PAST ON INV.SALES_PERSON_CODE = PJ_1_PAST.SALES_PERSON_CODE
  222. LEFT JOIN
  223.   (SELECT INV.SALES_PERSON_CODE, SUM(DET.PRE_TAX_EXTENDED_HOME_AMT) AMT FROM AR_INV_HST_HDR INV INNER JOIN AR_INV_HST_DET DET ON INV.INVOICE_NO = DET.INVOICE_NO WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 1 AND INV.INVOICE_TYPE = 'D' AND DET.SERVICE_CODE IN ('INTERCO','51000') AND INV.SALES_PERSON_CODE IS NOT NULL GROUP BY INV.SALES_PERSON_CODE) D_INV1 ON INV.SALES_PERSON_CODE = D_INV1.SALES_PERSON_CODE
  224. LEFT JOIN
  225.   (SELECT INV.SALES_PERSON_CODE, SUM(DET.PRE_TAX_EXTENDED_HOME_AMT) AMT FROM AR_INV_HST_HDR INV INNER JOIN AR_INV_HST_DET DET ON INV.INVOICE_NO = DET.INVOICE_NO WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 2 AND INV.INVOICE_TYPE = 'D' AND DET.SERVICE_CODE IN ('INTERCO','51000') AND INV.SALES_PERSON_CODE IS NOT NULL GROUP BY INV.SALES_PERSON_CODE) D_INV2 ON INV.SALES_PERSON_CODE = D_INV2.SALES_PERSON_CODE
  226. LEFT JOIN
  227.   (SELECT INV.SALES_PERSON_CODE, SUM(DET.PRE_TAX_EXTENDED_HOME_AMT) AMT FROM AR_INV_HST_HDR INV INNER JOIN AR_INV_HST_DET DET ON INV.INVOICE_NO = DET.INVOICE_NO WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 3 AND INV.INVOICE_TYPE = 'D' AND DET.SERVICE_CODE IN ('INTERCO','51000') AND INV.SALES_PERSON_CODE IS NOT NULL GROUP BY INV.SALES_PERSON_CODE) D_INV3 ON INV.SALES_PERSON_CODE = D_INV3.SALES_PERSON_CODE
  228. LEFT JOIN
  229.   (SELECT INV.SALES_PERSON_CODE, SUM(DET.PRE_TAX_EXTENDED_HOME_AMT) AMT FROM AR_INV_HST_HDR INV INNER JOIN AR_INV_HST_DET DET ON INV.INVOICE_NO = DET.INVOICE_NO WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 4 AND INV.INVOICE_TYPE = 'D' AND DET.SERVICE_CODE IN ('INTERCO','51000') AND INV.SALES_PERSON_CODE IS NOT NULL GROUP BY INV.SALES_PERSON_CODE) D_INV4 ON INV.SALES_PERSON_CODE = D_INV4.SALES_PERSON_CODE
  230. LEFT JOIN
  231.   (SELECT INV.SALES_PERSON_CODE, SUM(DET.PRE_TAX_EXTENDED_HOME_AMT) AMT FROM AR_INV_HST_HDR INV INNER JOIN AR_INV_HST_DET DET ON INV.INVOICE_NO = DET.INVOICE_NO WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 5 AND INV.INVOICE_TYPE = 'D' AND DET.SERVICE_CODE IN ('INTERCO','51000') AND INV.SALES_PERSON_CODE IS NOT NULL GROUP BY INV.SALES_PERSON_CODE) D_INV5 ON INV.SALES_PERSON_CODE = D_INV5.SALES_PERSON_CODE
  232. LEFT JOIN
  233.   (SELECT INV.SALES_PERSON_CODE, SUM(DET.PRE_TAX_EXTENDED_HOME_AMT) AMT FROM AR_INV_HST_HDR INV INNER JOIN AR_INV_HST_DET DET ON INV.INVOICE_NO = DET.INVOICE_NO WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 6 AND INV.INVOICE_TYPE = 'D' AND DET.SERVICE_CODE IN ('INTERCO','51000') AND INV.SALES_PERSON_CODE IS NOT NULL GROUP BY INV.SALES_PERSON_CODE) D_INV6 ON INV.SALES_PERSON_CODE = D_INV6.SALES_PERSON_CODE
  234. LEFT JOIN
  235.   (SELECT INV.SALES_PERSON_CODE, SUM(DET.PRE_TAX_EXTENDED_HOME_AMT) AMT FROM AR_INV_HST_HDR INV INNER JOIN AR_INV_HST_DET DET ON INV.INVOICE_NO = DET.INVOICE_NO WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 7 AND INV.INVOICE_TYPE = 'D' AND DET.SERVICE_CODE IN ('INTERCO','51000') AND INV.SALES_PERSON_CODE IS NOT NULL GROUP BY INV.SALES_PERSON_CODE) D_INV7 ON INV.SALES_PERSON_CODE = D_INV7.SALES_PERSON_CODE
  236. LEFT JOIN
  237.   (SELECT INV.SALES_PERSON_CODE, SUM(DET.PRE_TAX_EXTENDED_HOME_AMT) AMT FROM AR_INV_HST_HDR INV INNER JOIN AR_INV_HST_DET DET ON INV.INVOICE_NO = DET.INVOICE_NO WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 8 AND INV.INVOICE_TYPE = 'D' AND DET.SERVICE_CODE IN ('INTERCO','51000') AND INV.SALES_PERSON_CODE IS NOT NULL GROUP BY INV.SALES_PERSON_CODE) D_INV8 ON INV.SALES_PERSON_CODE = D_INV8.SALES_PERSON_CODE
  238. LEFT JOIN
  239.   (SELECT INV.SALES_PERSON_CODE, SUM(DET.PRE_TAX_EXTENDED_HOME_AMT) AMT FROM AR_INV_HST_HDR INV INNER JOIN AR_INV_HST_DET DET ON INV.INVOICE_NO = DET.INVOICE_NO WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 9 AND INV.INVOICE_TYPE = 'D' AND DET.SERVICE_CODE IN ('INTERCO','51000') AND INV.SALES_PERSON_CODE IS NOT NULL GROUP BY INV.SALES_PERSON_CODE) D_INV9 ON INV.SALES_PERSON_CODE = D_INV9.SALES_PERSON_CODE
  240. LEFT JOIN
  241.   (SELECT INV.SALES_PERSON_CODE, SUM(DET.PRE_TAX_EXTENDED_HOME_AMT) AMT FROM AR_INV_HST_HDR INV INNER JOIN AR_INV_HST_DET DET ON INV.INVOICE_NO = DET.INVOICE_NO WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 10 AND INV.INVOICE_TYPE = 'D' AND DET.SERVICE_CODE IN ('INTERCO','51000') AND INV.SALES_PERSON_CODE IS NOT NULL GROUP BY INV.SALES_PERSON_CODE) D_INV10 ON INV.SALES_PERSON_CODE = D_INV10.SALES_PERSON_CODE
  242. LEFT JOIN
  243.   (SELECT INV.SALES_PERSON_CODE, SUM(DET.PRE_TAX_EXTENDED_HOME_AMT) AMT FROM AR_INV_HST_HDR INV INNER JOIN AR_INV_HST_DET DET ON INV.INVOICE_NO = DET.INVOICE_NO WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 11 AND INV.INVOICE_TYPE = 'D' AND DET.SERVICE_CODE IN ('INTERCO','51000') AND INV.SALES_PERSON_CODE IS NOT NULL GROUP BY INV.SALES_PERSON_CODE) D_INV11 ON INV.SALES_PERSON_CODE = D_INV11.SALES_PERSON_CODE
  244. LEFT JOIN
  245.   (SELECT INV.SALES_PERSON_CODE, SUM(DET.PRE_TAX_EXTENDED_HOME_AMT) AMT FROM AR_INV_HST_HDR INV INNER JOIN AR_INV_HST_DET DET ON INV.INVOICE_NO = DET.INVOICE_NO WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.PERIOD_POSTED_TO = 12 AND INV.INVOICE_TYPE = 'D' AND DET.SERVICE_CODE IN ('INTERCO','51000') AND INV.SALES_PERSON_CODE IS NOT NULL GROUP BY INV.SALES_PERSON_CODE) D_INV12 ON INV.SALES_PERSON_CODE = D_INV12.SALES_PERSON_CODE
  246. LEFT JOIN
  247.   (SELECT INV.SALES_PERSON_CODE, SUM(DET.PRE_TAX_EXTENDED_HOME_AMT) AMT FROM AR_INV_HST_HDR INV INNER JOIN AR_INV_HST_DET DET ON INV.INVOICE_NO = DET.INVOICE_NO WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} - 3 AND INV.INVOICE_TYPE = 'D' AND DET.SERVICE_CODE IN ('INTERCO','51000') AND INV.SALES_PERSON_CODE IS NOT NULL GROUP BY INV.SALES_PERSON_CODE) D_INV_3_PAST ON INV.SALES_PERSON_CODE = D_INV_3_PAST.SALES_PERSON_CODE
  248. LEFT JOIN
  249.   (SELECT INV.SALES_PERSON_CODE, SUM(DET.PRE_TAX_EXTENDED_HOME_AMT) AMT FROM AR_INV_HST_HDR INV INNER JOIN AR_INV_HST_DET DET ON INV.INVOICE_NO = DET.INVOICE_NO WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} - 2 AND INV.INVOICE_TYPE = 'D' AND DET.SERVICE_CODE IN ('INTERCO','51000') AND INV.SALES_PERSON_CODE IS NOT NULL GROUP BY INV.SALES_PERSON_CODE) D_INV_2_PAST ON INV.SALES_PERSON_CODE = D_INV_2_PAST.SALES_PERSON_CODE
  250. LEFT JOIN
  251.   (SELECT INV.SALES_PERSON_CODE, SUM(DET.PRE_TAX_EXTENDED_HOME_AMT) AMT FROM AR_INV_HST_HDR INV INNER JOIN AR_INV_HST_DET DET ON INV.INVOICE_NO = DET.INVOICE_NO WHERE INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} - 1 AND INV.INVOICE_TYPE = 'D' AND DET.SERVICE_CODE IN ('INTERCO','51000') AND INV.SALES_PERSON_CODE IS NOT NULL GROUP BY INV.SALES_PERSON_CODE) D_INV_1_PAST ON INV.SALES_PERSON_CODE = D_INV_1_PAST.SALES_PERSON_CODE
  252. INNER JOIN
  253.   MT_EMPLOYEE EMP ON INV.SALES_PERSON_CODE = EMP.EMPLOYEE_CODE
  254. ORDER BY EMPLOYEE_NAME, SALES_PERSON_CODE)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement