Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- (SELECT
- NULL SALES_PERSON_CODE
- , 'Inter-Company' EMPLOYEE_NAME
- , COALESCE(INV_3_PAST.AMT,0) + COALESCE(D_INV_3_PAST.AMT,0) SALES_AMT_3_PAST
- , COALESCE(INV_2_PAST.AMT,0) + COALESCE(D_INV_2_PAST.AMT,0) SALES_AMT_2_PAST
- , COALESCE(INV_1_PAST.AMT,0) + COALESCE(D_INV_1_PAST.AMT,0) SALES_AMT_1_PAST
- , COALESCE(INV1.AMT,0) + COALESCE(D_INV1.AMT,0) SALES_AMT1
- , COALESCE(INV2.AMT,0) + COALESCE(D_INV2.AMT,0) SALES_AMT2
- , COALESCE(INV3.AMT,0) + COALESCE(D_INV3.AMT,0) SALES_AMT3
- , COALESCE(INV4.AMT,0) + COALESCE(D_INV4.AMT,0) SALES_AMT4
- , COALESCE(INV5.AMT,0) + COALESCE(D_INV5.AMT,0) SALES_AMT5
- , COALESCE(INV6.AMT,0) + COALESCE(D_INV6.AMT,0) SALES_AMT6
- , COALESCE(INV7.AMT,0) + COALESCE(D_INV7.AMT,0) SALES_AMT7
- , COALESCE(INV8.AMT,0) + COALESCE(D_INV8.AMT,0) SALES_AMT8
- , COALESCE(INV9.AMT,0) + COALESCE(D_INV9.AMT,0) SALES_AMT9
- , COALESCE(INV10.AMT,0) + COALESCE(D_INV10.AMT,0) SALES_AMT10
- , COALESCE(INV11.AMT,0) + COALESCE(D_INV11.AMT,0) SALES_AMT11
- , COALESCE(INV12.AMT,0) + COALESCE(D_INV12.AMT,0) SALES_AMT12
- , COALESCE(INV_3_PAST.AMT,0) + COALESCE(D_INV_3_PAST.AMT,0) - COALESCE(PJ_3_PAST.AMT,0) PROFIT_AMT_3_PAST
- , COALESCE(INV_2_PAST.AMT,0) + COALESCE(D_INV_2_PAST.AMT,0) - COALESCE(PJ_2_PAST.AMT,0) PROFIT_AMT_2_PAST
- , COALESCE(INV_1_PAST.AMT,0) + COALESCE(D_INV_1_PAST.AMT,0) - COALESCE(PJ_1_PAST.AMT,0) PROFIT_AMT_1_PAST
- , COALESCE(INV1.AMT,0) + COALESCE(D_INV1.AMT,0) - COALESCE(PJ1.AMT,0) PROFIT_AMT1
- , COALESCE(INV2.AMT,0) + COALESCE(D_INV2.AMT,0) - COALESCE(PJ2.AMT,0) PROFIT_AMT2
- , COALESCE(INV3.AMT,0) + COALESCE(D_INV3.AMT,0) - COALESCE(PJ3.AMT,0) PROFIT_AMT3
- , COALESCE(INV4.AMT,0) + COALESCE(D_INV4.AMT,0) - COALESCE(PJ4.AMT,0) PROFIT_AMT4
- , COALESCE(INV5.AMT,0) + COALESCE(D_INV5.AMT,0) - COALESCE(PJ5.AMT,0) PROFIT_AMT5
- , COALESCE(INV6.AMT,0) + COALESCE(D_INV6.AMT,0) - COALESCE(PJ6.AMT,0) PROFIT_AMT6
- , COALESCE(INV7.AMT,0) + COALESCE(D_INV7.AMT,0) - COALESCE(PJ7.AMT,0) PROFIT_AMT7
- , COALESCE(INV8.AMT,0) + COALESCE(D_INV8.AMT,0) - COALESCE(PJ8.AMT,0) PROFIT_AMT8
- , COALESCE(INV9.AMT,0) + COALESCE(D_INV9.AMT,0) - COALESCE(PJ9.AMT,0) PROFIT_AMT9
- , COALESCE(INV10.AMT,0) + COALESCE(D_INV10.AMT,0) - COALESCE(PJ10.AMT,0) PROFIT_AMT10
- , COALESCE(INV11.AMT,0) + COALESCE(D_INV11.AMT,0) - COALESCE(PJ11.AMT,0) PROFIT_AMT11
- , COALESCE(INV12.AMT,0) + COALESCE(D_INV12.AMT,0) - COALESCE(PJ12.AMT,0) PROFIT_AMT12
- FROM
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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)
- UNION ALL
- (SELECT
- INV.SALES_PERSON_CODE SALES_PERSON_CODE
- , EMP.EMPLOYEE_NAME EMPLOYEE_NAME
- , COALESCE(INV_3_PAST.AMT,0) + COALESCE(D_INV_3_PAST.AMT,0) SALES_AMT_3_PAST
- , COALESCE(INV_2_PAST.AMT,0) + COALESCE(D_INV_2_PAST.AMT,0) SALES_AMT_2_PAST
- , COALESCE(INV_1_PAST.AMT,0) + COALESCE(D_INV_1_PAST.AMT,0) SALES_AMT_1_PAST
- , COALESCE(INV1.AMT,0) + COALESCE(D_INV1.AMT,0) SALES_AMT1
- , COALESCE(INV2.AMT,0) + COALESCE(D_INV2.AMT,0) SALES_AMT2
- , COALESCE(INV3.AMT,0) + COALESCE(D_INV3.AMT,0) SALES_AMT3
- , COALESCE(INV4.AMT,0) + COALESCE(D_INV4.AMT,0) SALES_AMT4
- , COALESCE(INV5.AMT,0) + COALESCE(D_INV5.AMT,0) SALES_AMT5
- , COALESCE(INV6.AMT,0) + COALESCE(D_INV6.AMT,0) SALES_AMT6
- , COALESCE(INV7.AMT,0) + COALESCE(D_INV7.AMT,0) SALES_AMT7
- , COALESCE(INV8.AMT,0) + COALESCE(D_INV8.AMT,0) SALES_AMT8
- , COALESCE(INV9.AMT,0) + COALESCE(D_INV9.AMT,0) SALES_AMT9
- , COALESCE(INV10.AMT,0) + COALESCE(D_INV10.AMT,0) SALES_AMT10
- , COALESCE(INV11.AMT,0) + COALESCE(D_INV11.AMT,0) SALES_AMT11
- , COALESCE(INV12.AMT,0) + COALESCE(D_INV12.AMT,0) SALES_AMT12
- , COALESCE(INV_3_PAST.AMT,0) + COALESCE(D_INV_3_PAST.AMT,0) - COALESCE(PJ_3_PAST.AMT,0) PROFIT_AMT_3_PAST
- , COALESCE(INV_2_PAST.AMT,0) + COALESCE(D_INV_2_PAST.AMT,0) - COALESCE(PJ_2_PAST.AMT,0) PROFIT_AMT_2_PAST
- , COALESCE(INV_1_PAST.AMT,0) + COALESCE(D_INV_1_PAST.AMT,0) - COALESCE(PJ_1_PAST.AMT,0) PROFIT_AMT_1_PAST
- , COALESCE(INV1.AMT,0) + COALESCE(D_INV1.AMT,0) - COALESCE(PJ1.AMT,0) PROFIT_AMT1
- , COALESCE(INV2.AMT,0) + COALESCE(D_INV2.AMT,0) - COALESCE(PJ2.AMT,0) PROFIT_AMT2
- , COALESCE(INV3.AMT,0) + COALESCE(D_INV3.AMT,0) - COALESCE(PJ3.AMT,0) PROFIT_AMT3
- , COALESCE(INV4.AMT,0) + COALESCE(D_INV4.AMT,0) - COALESCE(PJ4.AMT,0) PROFIT_AMT4
- , COALESCE(INV5.AMT,0) + COALESCE(D_INV5.AMT,0) - COALESCE(PJ5.AMT,0) PROFIT_AMT5
- , COALESCE(INV6.AMT,0) + COALESCE(D_INV6.AMT,0) - COALESCE(PJ6.AMT,0) PROFIT_AMT6
- , COALESCE(INV7.AMT,0) + COALESCE(D_INV7.AMT,0) - COALESCE(PJ7.AMT,0) PROFIT_AMT7
- , COALESCE(INV8.AMT,0) + COALESCE(D_INV8.AMT,0) - COALESCE(PJ8.AMT,0) PROFIT_AMT8
- , COALESCE(INV9.AMT,0) + COALESCE(D_INV9.AMT,0) - COALESCE(PJ9.AMT,0) PROFIT_AMT9
- , COALESCE(INV10.AMT,0) + COALESCE(D_INV10.AMT,0) - COALESCE(PJ10.AMT,0) PROFIT_AMT10
- , COALESCE(INV11.AMT,0) + COALESCE(D_INV11.AMT,0) - COALESCE(PJ11.AMT,0) PROFIT_AMT11
- , COALESCE(INV12.AMT,0) + COALESCE(D_INV12.AMT,0) - COALESCE(PJ12.AMT,0) PROFIT_AMT12
- FROM
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- LEFT JOIN
- (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
- INNER JOIN
- MT_EMPLOYEE EMP ON INV.SALES_PERSON_CODE = EMP.EMPLOYEE_CODE
- ORDER BY EMPLOYEE_NAME, SALES_PERSON_CODE)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement