· 9 years ago · Oct 03, 2016, 07:52 AM
1DELIMITER $$
2
3CREATE DEFINER=`root`@`localhost` PROCEDURE `SQLINSERTITEM`(pITE_CODE VARCHAR(24) ,
4 pQUANTITY DOUBLE(15,3),
5 pIS_FREE_ITEM INT(1),
6 pTER_CODE CHAR(3),
7 pBRN_CODE VARCHAR(4),
8 pORDER_TYPE INT(1))
9INSERT_ITEM: BEGIN
10
11
12
13
14DECLARE vROW_NUM INT(10) DEFAULT 0;
15
16DECLARE vITE_CODE VARCHAR(24) DEFAULT NULL;
17DECLARE vIN_CODE VARCHAR(24) DEFAULT NULL;
18DECLARE vUNT_CODE CHAR(4) DEFAULT NULL;
19DECLARE vITE_DESC VARCHAR(80) DEFAULT NULL;
20DECLARE vECR_DESC VARCHAR(28) DEFAULT NULL;
21DECLARE vDEP_CODE CHAR(2) DEFAULT NULL;
22DECLARE vSEC_CODE CHAR(6) DEFAULT NULL;
23DECLARE vDIV_CODE CHAR(6) DEFAULT NULL;
24
25DECLARE vTAX_VALUE1 DOUBLE(15,3) DEFAULT 0.000;
26DECLARE vTAX_VALUE2 DOUBLE(15,3) DEFAULT 0.000;
27DECLARE vTAX_VALUE3 DOUBLE(15,3) DEFAULT 0.000;
28DECLARE vTAX_INC1 INT(1) DEFAULT 1;
29DECLARE vTAX_INC2 DOUBLE(15,3) DEFAULT 0.000;
30DECLARE vTAX_INC3 DOUBLE(15,3) DEFAULT 0.000;
31DECLARE vTAXABLE1 DOUBLE(15,3) DEFAULT 0.000;
32DECLARE vTAXABLE2 DOUBLE(15,3) DEFAULT 0.000;
33DECLARE vTAXABLE3 DOUBLE(15,3) DEFAULT 0.000;
34DECLARE vAMOUNT DOUBLE(15,3) DEFAULT 0.000;
35
36DECLARE vTAX_AMOUNT1 DOUBLE(15,3) DEFAULT 0.000;
37DECLARE vTAX_AMOUNT2 DOUBLE(15,3) DEFAULT 0.000;
38DECLARE vTAX_AMOUNT3 DOUBLE(15,3) DEFAULT 0.000;
39
40DECLARE vSUP_CODE VARCHAR(5) DEFAULT NULL;
41DECLARE vINV_FLAG INT(1) DEFAULT 1;
42DECLARE vVAT_INC_FLAG INT(1) DEFAULT 1;
43DECLARE vBRD_CODE VARCHAR(5) DEFAULT NULL;
44DECLARE vSENIOR_ITEM INT(1) DEFAULT 0;
45DECLARE vVAT_INC INT(1) DEFAULT 1;
46DECLARE vACTIVE INT(1) DEFAULT 0;
47
48DECLARE vUNT_PRIC DOUBLE(15,3) DEFAULT 0.000;
49DECLARE vUNT_COST DOUBLE(15,3) DEFAULT 0.000;
50DECLARE vLOC_CODE CHAR(4) DEFAULT NULL;
51DECLARE vITEM_EXISTS INT(1) DEFAULT 0;
52DECLARE vCHOICES INT(1) DEFAULT 0;
53DECLARE vPRICELEVEL INT(2) DEFAULT 0;
54DECLARE vORDER_TYPE INT(1) DEFAULT 0;
55DECLARE vTAX_EXEMPT INT(1) DEFAULT 0;
56DECLARE vDIPLOMAT INT(1) DEFAULT 0;
57
58DECLARE vDISC_CODE CHAR(20) DEFAULT NULL;
59DECLARE vAUTO_DISC_CODE CHAR(20) DEFAULT NULL;
60DECLARE vDISC_ROW INT(10) DEFAULT NULL;
61DECLARE vMETHOD INT(1) DEFAULT 0;
62
63DROP TABLE IF EXISTS `RS_INSERT_ITEM`;
64CREATE TABLE `RS_INSERT_ITEM` (
65 `STATUS` INT(1) DEFAULT 0,
66 `MSG_TITLE` VARCHAR(50) NULL,
67 `MSG_STATUS` LONGTEXT NULL
68);
69
70SELECT
71BC.ITE_CODE AS ITE_CODE,
72BC.IN_CODE AS IN_CODE,
73BC.UNT_CODE AS UNT_CODE,
74BC.ITE_DESC AS ITE_DESC,
75MI.ECR_DESC AS ECR_DESC,
76MI.DEP_CODE AS DEP_CODE,
77MI.SEC_CODE AS SEC_CODE,
78MI.DIV_CODE AS DIV_CODE,
79MI.TAX_VALUE1 AS TAX_VALUE1,
80MI.TAX_VALUE2 AS TAX_VALUE2,
81MI.TAX_VALUE3 AS TAX_VALUE3,
82MI.TAX_INC1 AS TAX_INC1,
83MI.TAX_INC2 AS TAX_INC2,
84MI.TAX_INC3 AS TAX_INC3,
85MI.TAXABLE1 AS TAXABLE1,
86MI.TAXABLE2 AS TAXABLE2,
87MI.TAXABLE3 AS TAXABLE3,
88MI.`SUP_CODE` AS SUP_CODE,
89MI.`INV_FLAG` AS INV_FLAG,
90MI.`BRD_CODE` AS BRD_CODE,
91MI.`SENIOR_ITEM` AS SENIOR_ITEM,
92MI.`VAT_INCLUSIVE_FLAG` AS VAT_INCLUSIVE_FLAG,
93MI.`ACTIVE` AS ACTIVE,
94MI.`DISC_CODE` AS DISC_CODE,
95
96(PR.UNT_PRIC-PR.TAX_AMOUNT1-PR.TAX_AMOUNT2-PR.TAX_AMOUNT3)*pQUANTITY AS AMOUNT,
97(PR.TAX_AMOUNT1)*pQUANTITY AS TAX_AMOUNT1,
98(PR.TAX_AMOUNT2)*pQUANTITY AS TAX_AMOUNT2,
99(PR.TAX_AMOUNT3)*pQUANTITY AS TAX_AMOUNT3,
100PR.UNT_PRIC AS UNT_PRIC,
101PR.UNT_COST AS UNT_COST,
102IF(COUNT(*)<1,0,1) AS ITEM_EXISTS
103
104INTO
105
106vITE_CODE,
107vIN_CODE,
108vUNT_CODE,
109vITE_DESC,
110vECR_DESC,
111vDEP_CODE,
112vSEC_CODE,
113vDIV_CODE,
114vTAX_VALUE1,
115vTAX_VALUE2,
116vTAX_VALUE3,
117vTAX_INC1,
118vTAX_INC2,
119vTAX_INC3,
120vTAXABLE1,
121vTAXABLE2,
122vTAXABLE3,
123vSUP_CODE,
124vINV_FLAG,
125vBRD_CODE,
126vSENIOR_ITEM,
127vVAT_INC_FLAG,
128vACTIVE,
129vAUTO_DISC_CODE,
130vAMOUNT,
131vTAX_AMOUNT1,
132vTAX_AMOUNT2,
133vTAX_AMOUNT3,
134vUNT_PRIC,
135vUNT_COST,
136vITEM_EXISTS
137
138FROM RD0500 BC
139LEFT JOIN RD5700 PR ON BC.IN_CODE=PR.IN_CODE AND BC.UNT_CODE=PR.UNT_CODE
140LEFT JOIN RD5500 MI ON PR.IN_CODE=MI.IN_CODE
141WHERE BC.ITE_CODE=pITE_CODE LIMIT 1;
142
143
144SELECT LOC_CODE INTO vLOC_CODE FROM POSTERM WHERE TER_CODE = pTER_CODE AND BRN_CODE = pBRN_CODE ;
145
146CASE
147 WHEN vITEM_EXISTS AND (vACTIVE OR pIS_FREE_ITEM) THEN
148 BEGIN
149 SELECT COUNT(*) + 1 INTO vROW_NUM FROM RD5300;
150 UPDATE RD5300 SET MARKED=0;
151 IF pIS_FREE_ITEM THEN
152 SET vAMOUNT=0,
153 vTAX_AMOUNT1=0,
154 vTAX_AMOUNT2=0,
155 vTAX_AMOUNT3=0;
156 ELSE
157
158 SET vUNT_PRIC = GET_PRICECHANGE_VALUE( vITE_CODE, pBRN_CODE, vUNT_PRIC );
159 CALL PROC_GET_ITEM_AMOUNT_TAX( vUNT_PRIC, vTAXABLE1, vTAX_VALUE1, vTAXABLE2, vTAX_VALUE2, vTAXABLE3, vTAX_VALUE3, 1 );
160 SELECT `AMOUNT`*pQUANTITY, `TAX_AMOUNT1`*pQUANTITY, `TAX_AMOUNT2`*pQUANTITY, `TAX_AMOUNT3`*pQUANTITY
161 INTO vAMOUNT, vTAX_AMOUNT1, vTAX_AMOUNT2, vTAX_AMOUNT3 FROM TAX_COMPUTATION;
162 INSERT INTO CUSTOMER_DISPLAY (`INFO1`, `INFO2`, `INFO3`) VALUES (vITE_DESC,CONCAT('x',pQUANTITY),CONCAT('@', vAMOUNT + vTAX_AMOUNT1 + vTAX_AMOUNT2 + vTAX_AMOUNT3));
163 END IF;
164
165 INSERT INTO RD5300 (
166 `ROW_NUM`, `ITE_CODE`, `IN_CODE`, `AMOUNT`, `QUANTITY`,
167 `COST`, `PRICE`, `ITE_DESC`, `UNT_CODE`, `DEP_CODE`,
168 `SEC_CODE`, `DIV_CODE`, `SUP_CODE`, `LOC_CODE`, `BRD_CODE`,
169 `TAX_VALUE1`, `TAX_VALUE2`, `TAX_VALUE3`, `TAX_INC1`, `TAX_INC2`,
170 `TAX_INC3`, `TAXABLE1`, `TAXABLE2`, `TAXABLE3`, `TAX_AMOUNT1`,
171 `TAX_AMOUNT2`, `TAX_AMOUNT3`, `ITEM_TYPE`, `DATE`, `TIME`,
172 `INV_FLAG`, `SENIOR_ITEM`, `TAX_EXEMPT`, `IS_BUNDLE`, `FREE`,
173 `PROMO`, `DISC_CODE`, `ORDER_TYPE`, `IS_ADDONS`,
174 `DIPLOMAT`, `METHOD`, `MARKED`, `MAIN_IN_CODE`
175 )
176 VALUES
177 (
178 vROW_NUM, vITE_CODE, vIN_CODE, vAMOUNT, pQUANTITY,
179 vUNT_COST, vUNT_PRIC, vITE_DESC, vUNT_CODE, vDEP_CODE,
180 vSEC_CODE, vDIV_CODE, vSUP_CODE, vLOC_CODE, vBRD_CODE,
181 vTAX_VALUE1, vTAX_VALUE2, vTAX_VALUE3, vTAX_INC1, vTAX_INC2,
182 vTAX_INC3, vTAXABLE1, vTAXABLE2, vTAXABLE3, vTAX_AMOUNT1,
183 vTAX_AMOUNT2, vTAX_AMOUNT3, 0, DATE_FORMAT( CURDATE(), '%Y-%m-%d'), CURTIME(),
184 vINV_FLAG, vSENIOR_ITEM, vTAX_EXEMPT, 0, pIS_FREE_ITEM,
185 0, vDISC_CODE, pORDER_TYPE, 0,
186 vDIPLOMAT, vMETHOD, 1, vIN_CODE
187 );
188 INSERT INTO RS_INSERT_ITEM VALUES (1,'Item','Item successfully added.');
189 END;
190 WHEN !vACTIVE THEN
191 BEGIN
192 INSERT INTO RS_INSERT_ITEM VALUES (0,'Item is not active.',CONCAT('Item ',IFNULL(pITE_CODE,'') ,' is not active.'));
193 END;
194 ELSE
195 INSERT INTO RS_INSERT_ITEM VALUES (0,'Item not found.',CONCAT('Item ',IFNULL(pITE_CODE,'') ,' not found.'));
196END CASE;
197SELECT * FROM RS_INSERT_ITEM;
198
199END INSERT_ITEM