MASTER_SQL/Back_orders.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
	select IM_CUST AS "Customer",n1.NI_DATE,
	  IM_CUST AS "Parent",
	  IM_XX_COST_CENTRE01     AS "CostCentre",
		n1.NI_STOCK AS "Item",
		IM_LEVEL_UNIT AS "UOI", /*UOI*/
	 -- n1.IL_NOTE_2 AS "Pallet/Space", /*Pallet/Space*/
		n1.NI_LOCN AS "Locn", /*Locn*/
	  n1.NI_AVAIL_ACTUAL AS "Avail SOH",
    n1.NI_ENTRY,
    n1.NI_QUANTITY,n1.NI_TRAN_TYPE,n1.NI_SD_ORDER,NULL,'BO' AS "Kind"

	FROM NI n1 INNER JOIN IM ON IM_STOCK = n1.NI_STOCK
	--INNER JOIN SH ON SH_ORDER = n1.NI_SL_ORDER
--INNER JOIN IL l1 ON l1.IL_LOCN = n1.NI_LOCN
  ---INNER JOIN Tmp_Locn_Cnt_By_Cust ON sLocn = l1.IL_LOCN
	WHERE IM_ACTIVE = 1
			AND IM_CUST = 'CGU'
      AND IM_STOCK = 'CV230CID0122'
	AND n1.NI_SD_ORDER IS NOT NULL
	AND n1.NI_STATUS IN (2)
  AND n1.NI_TRAN_TYPE = 6
  AND n1.NI_QUANTITY < 0
  --ORDER BY  n1.NI_DATE, n1.NI_STOCK    Asc


  UNION ALL


	select IM_CUST AS "Customer",n1.NI_DATE,
	  IM_CUST AS "Parent",
	  IM_XX_COST_CENTRE01     AS "CostCentre",
		n1.NI_STOCK AS "Item",
		IM_LEVEL_UNIT AS "UOI", /*UOI*/
	 -- n1.IL_NOTE_2 AS "Pallet/Space", /*Pallet/Space*/
		n1.NI_LOCN AS "Locn", /*Locn*/
	  n1.NI_AVAIL_ACTUAL AS "Avail SOH",
    n1.NI_ENTRY,
    n1.NI_QUANTITY,n1.NI_TRAN_TYPE,n1.NI_SL_ORDER,SH_STATUS,'D' AS "Kind"

	FROM NI n1 INNER JOIN IM ON IM_STOCK = n1.NI_STOCK
	INNER JOIN SH ON SH_ORDER = n1.NI_SL_ORDER
--INNER JOIN IL l1 ON l1.IL_LOCN = n1.NI_LOCN
  ---INNER JOIN Tmp_Locn_Cnt_By_Cust ON sLocn = l1.IL_LOCN
	WHERE IM_ACTIVE = 1
			AND IM_CUST = 'CGU'
      AND IM_STOCK = 'CV230CID0122'
	AND n1.NI_SL_ORDER IS NOT NULL
	AND n1.NI_STATUS IN (2,4)
  AND n1.NI_TRAN_TYPE = 6
  AND n1.NI_QUANTITY < 0
   AND SH_STATUS = 0
   --ORDER BY  n1.NI_DATE, n1.NI_STOCK    Asc

  UNION ALL


  	select IM_CUST AS "Customer",n1.NI_DATE,
	  IM_CUST AS "Parent",
	  IM_XX_COST_CENTRE01     AS "CostCentre",
		n1.NI_STOCK AS "Item",
		IM_LEVEL_UNIT AS "UOI", /*UOI*/
	 -- n1.IL_NOTE_2 AS "Pallet/Space", /*Pallet/Space*/
		n1.NI_LOCN AS "Locn", /*Locn*/
	  n1.NI_AVAIL_ACTUAL AS "Avail SOH",
    n1.NI_ENTRY,
    n1.NI_QUANTITY,n1.NI_TRAN_TYPE,n1.NI_SL_ORDER,NULL,'SH'   AS "Kind"

	FROM NI n1 INNER JOIN IM ON IM_STOCK = n1.NI_STOCK
	--INNER JOIN SH ON SH_ORDER = n1.NI_SL_ORDER
  ---INNER JOIN Tmp_Locn_Cnt_By_Cust ON sLocn = l1.IL_LOCN
	WHERE IM_ACTIVE = 1
			AND IM_CUST = 'CGU'
      AND IM_STOCK = 'CV230CID0122'
	AND n1.NI_AVAIL_ACTUAL >= '1'
	AND n1.NI_STATUS IN (1,2)
  --AND SH_STATUS = 0
  --AND n1.NI_TRAN_TYPE = 6
  --AND n1.NI_QUANTITY < 0

 -- ORDER BY  n1.NI_STOCK    Asc


SELECT SH_ORDER, SD_STOCK, NI_AVAIL_ACTUAL
FROM SH INNER JOIN SD ON SD_ORDER = SH_ORDER
     NI INNER JOIN SH ON SH_ORDER = NI_SL_ORDER
WHERE  SH_ORDER = '1741279'
AND SD_STATUS = 0


SELECT SD_ORDER, RM_CUST, RM_PARENT, SH_ADD_DATE, SD_QTY_ORDER, SD_QTY_BACK, SD_STOCK, NI_AVAIL_ACTUAL,
CASE
  WHEN SH_STATUS = 0 THEN 'AWAITING '
  WHEN SH_STATUS = 1 THEN 'LIVE '
  WHEN SH_STATUS = 2 THEN 'DESPATCHED '
  WHEN SH_STATUS = 3 THEN 'CANCELLED '
  WHEN SH_STATUS = 4 THEN 'CLOSED '
END AS SH_STATUS,
CASE
  WHEN SD_STATUS = 0 THEN 'AWAITING '
  WHEN SD_STATUS = 1 THEN 'LIVE '
  WHEN SD_STATUS = 2 THEN 'DESPATCHED '
  WHEN SD_STATUS = 3 THEN 'CANCELLED '
  WHEN SD_STATUS = 4 THEN 'CLOSED '
END AS SD_STATUS,   SD_LINE
FROM SH, RM ,SD, NI
WHERE SH_CUST = RM_CUST
AND SD_ORDER = SH_ORDER
AND NI_SL_ORDER = SH_ORDER
AND NI_STOCK = SD_STOCK
--AND RM_PARENT = 'VHAAUS'
AND SD_STATUS = 0
AND SD_QTY_BACK > 0
--AND SD_STOCK NOT LIKE 'COURIER'
--AND SD_STOCK NOT LIKE 'FEE*'
--AND NI_AVAIL_ACTUAL > 0
AND SD_STOCK = 'VHAV00459'
--AND SH_ADD_DATE  <= (SYSDATE - 3)
GROUP BY SD_ORDER, RM_CUST, RM_PARENT, SH_ADD_DATE, SD_QTY_ORDER, SD_QTY_BACK, SD_STOCK, SH_STATUS, SD_STATUS, SD_LINE,NI_AVAIL_ACTUAL,SD_ADD_DATE
ORDER BY NI_AVAIL_ACTUAL,SH_ADD_DATE, SD_ORDER, SD_LINE DESC

SELECT * FROM SD WHERE SD_STOCK = 'VHAV00459'
AND SD_STATUS = 0
AND SD_QTY_BACK > 0