IQMS-PLSQL-Snippets/std_future_cost_comparison.sql at master · 061375/IQMS-PLSQL-Snippets · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
select * from (
    select
        itemno,
        class,
        descrip,
        std_cost,
        std_cost_future,
        budgetcost,
        forecastcost,
        cost1000,
        (case when std_cost_future > 0 then (std_cost - std_cost_future) else 0 end) as std_cost_comparison
        from (
        select
            itemno,
            class,
            descrip,
            nvl(std_cost,0) as std_cost,
            nvl(std_cost_future,0) as std_cost_future,
            nvl(budgetcost,0) as budgetcost,
            nvl(forecastcost,0) as forecastcost,
            nvl(cost1000,0) as cost1000
        from (
            SELECT
                    itemno,
                    class,
                    descrip,
                    std_cost,
                   (SELECT SUM(std_cost) + inv_misc.get_nonremovable_elements_cost('arinvt_cost_tmp', arinvt.id)
                      FROM arinvt_cost_tmp
                     WHERE arinvt_id = arinvt.id)
                      AS std_cost_future,
                   (SELECT SUM(std_cost) + inv_misc.get_nonremovable_elements_cost('arinvt_elem_budget', arinvt.id)
                      FROM arinvt_elem_budget
                     WHERE arinvt_id = arinvt.id)
                      AS budgetcost,
                   (SELECT SUM(std_cost) + inv_misc.get_nonremovable_elements_cost('arinvt_elem_forecast', arinvt.id)
                      FROM arinvt_elem_forecast
                     WHERE arinvt_id = arinvt.id)
                      AS forecastcost,
                   (arinvt.std_cost * 1000) AS cost1000
             FROM arinvt where nvl(pk_hide,'N') = 'N'
        )
    )
) order by std_cost_comparison desc;