MASTER_SQL/EOMCartonIn.sql at master · shmiko/MASTER_SQL · 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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
--Admin Order Data
/*decalre variables*/
var cust varchar2(20)
exec :cust := 'CONNECTVIC'
var ordernum varchar2(20)
exec :ordernum := '1359866'
var stock varchar2(20)
exec :stock := 'COURIER'

var stockexclude  varchar2(20)
exec :stockexclude := 'FEE%'


var source varchar2(20)
exec :source := 'BSPRINTNSW'
var anal varchar2(20)
exec :anal := '21VICP'
var start_date varchar2(20)
exec :start_date := To_Date('1-Jul-2013')
var end_date varchar2(20)
exec :end_date := To_Date('31-Jul-2013')


INSERT into Tmp_Admin_Data(
            Customer,
            Parent,
            CostCentre,
            OrderNum,
            OrderwareNum,
            CustRef,
            Pickslip,
            PickNum,
            DespatchNote,
            DespatchDate,
            FeeType,
            Item,
            Description,
            Qty,
            UOI,
            UnitPrice,
            DExcl,
            OWUnitPrice,
            Excl_Total,
            DIncl,
            Incl_Total,
            ReportingPrice,
            Address,
            Address2,
            Suburb,
            State,
            Postcode,
            DeliverTo,
            AttentionTo,
            Weight,
            Packages,
            OrderSource,
            ILNOTE2,
            NILOCN,
            NIAVAILACTUAL,
            CountOfStocks

            )


/*Get Carton In Fee*/
SELECT    IM_CUST                AS "Customer",
          RM_PARENT              AS "Parent",
          IM_XX_COST_CENTRE01       AS "CostCentre",
          NI_QJ_NUMBER               AS "Order",
          NULL         AS "OrderwareNum",
          NULL            AS "CustomerRef",
          NULL                     AS "Pickslip",
          NULL                     AS "PickNum",
          NULL                     AS "DespatchNote",
          NULL                     AS "DespatchDate",
  CASE    WHEN NE_ENTRY IS NOT NULL THEN 'Carton In Fee '
          ELSE ''
          END                      AS "FeeType",
          IM_STOCK               AS "Item",
          IM_DESC                AS "Description",
  CASE    WHEN NE_ENTRY IS NOT NULL THEN  '1'
          ELSE ''
          END                     AS "Qty",
  CASE    WHEN NE_ENTRY IS NOT NULL THEN  '1'
          ELSE ''
          END                     AS "UOI",
  CASE    WHEN NE_ENTRY IS NOT NULL THEN '' ||  (Select RM_XX_FEE13 from RM where RM_CUST = :cust)
          ELSE ''
          END                      AS "UnitPrice",
   CASE    WHEN NE_ENTRY IS NOT NULL THEN '' ||  (Select RM_XX_FEE13 from RM where RM_CUST = :cust)
          ELSE ''
          END                      AS "DExcl",
   CASE    WHEN NE_ENTRY IS NOT NULL THEN '' ||  (Select RM_XX_FEE13 from RM where RM_CUST = :cust)
          ELSE ''
          END                      AS "OWUnitPrice",
   CASE    WHEN NE_ENTRY IS NOT NULL THEN '' ||  (Select RM_XX_FEE13 from RM where RM_CUST = :cust)
          ELSE ''
          END                      AS "Excl_Total",
    CASE    WHEN NE_ENTRY IS NOT NULL THEN '' ||  (Select RM_XX_FEE13 from RM where RM_CUST = :cust) * 1.1
          ELSE ''
          END                      AS "DIncl",
   CASE    WHEN NE_ENTRY IS NOT NULL THEN '' || (Select RM_XX_FEE13 from RM where RM_CUST = :cust) * 1.1
          ELSE ''
          END                      AS "Incl_Total",
          NULL                     AS "ReportingPrice",
          NULL                     AS "Address",
          NULL                     AS "Address2",
          NULL                     AS "Suburb",
          NULL                     AS "State",
          NULL                     AS "Postcode",
          NULL                     AS "DeliverTo",
          NULL                     AS "AttentionTo" ,
          NULL                     AS "Weight",
          NULL                     AS "Packages",
          NULL                     AS "OrderSource",
          NULL AS "Pallet/Shelf Space",
	        NULL AS "Locn",
	        NULL AS "AvailSOH",
	        NULL AS "CountOfStocks"

FROM      PWIN175.IM
          INNER JOIN PWIN175.NA  ON NA_STOCK = IM_STOCK
          INNER JOIN PWIN175.IL  ON IL_UID = NA_EXT_KEY
          INNER JOIN PWIN175.NE  ON NE_ACCOUNT  = NA_ACCOUNT
          INNER JOIN PWIN175.NI  ON NI_ENTRY = NE_ENTRY
          INNER JOIN PWIN175.RM  ON RM_CUST  = IM_CUST
WHERE     (Select rmP.RM_XX_FEE13
           from RM rmP
            where To_Number(regexp_substr(rmP.RM_XX_FEE13, '^[-]?[[:digit:]]*\.?[[:digit:]]*$')) > 0 AND rmp.RM_CUST = :cust)  > 0
AND     IM_CUST IN (SELECT RM_CUST FROM RM WHERE RM_PARENT = ' '  AND RM_ANAL = :anal AND RM_TYPE = 0 AND RM_ACTIVE = 1 )

AND       NA_EXT_TYPE = 1210067
AND       NE_TRAN_TYPE = 1
--AND       RM_ANAL = :anal
AND       (NE_STATUS = 1 OR NE_STATUS = 3)
AND       NE_DATE >= :start_date AND NE_DATE <= :end_date
AND       IL_NOTE_2 = 'No' AND IL_PHYSICAL = 1
GROUP BY  IM_CUST,
          IM_XX_COST_CENTRE01,
          NI_QJ_NUMBER,
          NE_ENTRY,
          IM_STOCK,
          IM_DESC,
          NE_DATE,
          RM_PARENT