IQMS-PLSQL-Snippets/InventoryStdCostDetail.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
45
46
47
48
49
SELECT
        CLASS,
        ITEMNO,
        REV,
        DESCRIP,
        ELEM_DESCRIP,
        STD_COST,
        EPLANT_ID,
        COMPANY,
        PK_HIDE,
        STDQUAN,
        ONHAND,
        ELEM_TYPE,
        STD_PRICE,
        EXTENDED_RAW_MATERIAL,
        EXTENDED_LABOR,
        EXTENDED_OVERHEAD,
        EXTENDED_FIXED_OVERHEAD,
        EXTENDED_OUTSOURCED,
        (EXTENDED_RAW_MATERIAL + EXTENDED_LABOR + EXTENDED_OVERHEAD + EXTENDED_FIXED_OVERHEAD + EXTENDED_OUTSOURCED) AS TOTAL_COST,
        EXTENDED_STD_PRICE
    FROM (
        SELECT
            ARINVT.CLASS,
            ARINVT.ITEMNO,
            ARINVT.REV,
            ARINVT.DESCRIP,
            ELEMENTS.ELEM_DESCRIP,
            ARINVT_ELEMENTS.STD_COST,
            ARINVT.EPLANT_ID,
            EPLANT.COMPANY,
            ARINVT.PK_HIDE,
            ARINVT.STDQUAN,
            ARINVT.ONHAND,
            ELEMENTS.ELEM_TYPE,
            ARINVT.STD_PRICE,
            (case when nvl(ELEMENTS.ELEM_TYPE,'N') = 'N' then ARINVT_ELEMENTS.STD_COST * ARINVT.ONHAND else 0 end) as extended_raw_material,
            (case when ELEMENTS.ELEM_TYPE = 'LABOR' then ARINVT_ELEMENTS.STD_COST * ARINVT.ONHAND else 0 end) as extended_labor,
            (case when ELEMENTS.ELEM_TYPE = 'OVERHEAD' then ARINVT_ELEMENTS.STD_COST * ARINVT.ONHAND else 0 end) as extended_overhead,
            (case when ELEMENTS.ELEM_TYPE = 'OVERHEADF' then ARINVT_ELEMENTS.STD_COST * ARINVT.ONHAND else 0 end) as extended_fixed_overhead,
            (case when ELEMENTS.ELEM_TYPE = 'OUTSOURCED' then ARINVT_ELEMENTS.STD_COST * ARINVT.ONHAND else 0 end) as extended_outsourced,
            (NVL(ARINVT.STD_PRICE,0) * NVL(ARINVT.ONHAND,0)) AS EXTENDED_STD_PRICE
        FROM   ((IQMS.ARINVT ARINVT LEFT OUTER JOIN IQMS.ARINVT_ELEMENTS ARINVT_ELEMENTS ON ARINVT.ID=ARINVT_ELEMENTS.ARINVT_ID) LEFT OUTER JOIN IQMS.EPLANT EPLANT ON ARINVT.EPLANT_ID=EPLANT.ID) LEFT OUTER JOIN IQMS.ELEMENTS ELEMENTS ON ARINVT_ELEMENTS.ELEMENTS_ID=ELEMENTS.ID
        WHERE  ((ARINVT.PK_HIDE IS  NULL ) OR ((ARINVT.PK_HIDE=' ' OR ARINVT.PK_HIDE='N')))
)
ORDER BY
CLASS,
ITEMNO,
REV