001.CREATE OR REPLACE PROCEDURE WMWHSE8.IRPC_TRNUPDATE_S_SP
002.(
003. I_TRANSFERNUM IN VARCHAR2,
004. I_TRANSFERLINE IN VARCHAR2,
005. I_LPN IN VARCHAR2,
006. O_STATUS OUT VARCHAR2,
007. O_ERRDESC OUT VARCHAR2
008.)
009.IS
010.
011./* #################################################### */
012.
013.V_PROCDESC VARCHAR2(200):=' IRPC_TRNUPDATE_S_SP ';
014.V_RECCOUNT NUMBER(2);
015.
016.V_FROMSTORERKEY VARCHAR2(15);
017.V_FROMSKU VARCHAR2(50);
018.V_FROMLOC VARCHAR2(10);
019.V_FROMLOT VARCHAR2(10);
020.V_FROMID VARCHAR2(18);
021.V_FROMQTY NUMBER(22,5);
022.V_FROMPACKKEY VARCHAR2(50);
023.V_FROMUOM VARCHAR2(10);
024.
025.V_FROMLOC2 VARCHAR2(10);
026.V_QTYONHAND NUMBER(22,5);
027.V_QTYALLOCATED NUMBER(22,5);
028.BEGIN
029.
030.
031. SELECT
032. COUNT(1)
033. INTO V_RECCOUNT
034. FROM WMWHSE8.TRANSFER
035. WHERE TRANSFERKEY = I_TRANSFERNUM;
036. IF V_RECCOUNT = 0 THEN
037. O_STATUS := 'E';
038. O_ERRDESC := 'ERROR CANNOT FIND TRANSFER';
039. RETURN;
040. END IF;
041.
042. SELECT
043. COUNT(1)
044. INTO V_RECCOUNT
045. FROM WMWHSE8.TRANSFERDETAIL
046. WHERE TRANSFERKEY = I_TRANSFERNUM
047. AND TRANSFERLINENUMBER = I_TRANSFERLINE
048. AND STATUS IN ('0','3');
049.
050. IF V_RECCOUNT = 0 THEN
051. O_STATUS := 'E';
052. O_ERRDESC := 'ERROR CANNOT FIND TRANSFER';
053. RETURN;
054. ELSE
055. SELECT
056. FROMSTORERKEY ,
057. FROMSKU ,
058. FROMLOC ,
059. FROMLOT ,
060. FROMID ,
061. FROMQTY ,
062. FROMPACKKEY ,
063. FROMUOM
064. INTO
065. V_FROMSTORERKEY ,
066. V_FROMSKU ,
067. V_FROMLOC ,
068. V_FROMLOT ,
069. V_FROMID ,
070. V_FROMQTY ,
071. V_FROMPACKKEY ,
072. V_FROMUOM
073.
074. FROM WMWHSE8.TRANSFERDETAIL
075. WHERE TRANSFERKEY = I_TRANSFERNUM
076. AND TRANSFERLINENUMBER = I_TRANSFERLINE
077. AND STATUS IN ('0','3') ;
078. END IF;
079.
080.
081. IF V_FROMID = I_LPN THEN
082.
083. SELECT
084. LOC ,
085. QTY ,
086. QTYALLOCATED
087. INTO
088. V_FROMLOC2,
089. V_QTYONHAND,
090. V_QTYALLOCATED
091. FROM WMWHSE8.LOTXLOCXID
092. WHERE ID = I_LPN
093. AND SKU = V_FROMSKU
094. AND STORERKEY = V_FROMSTORERKEY
095. AND LOC = V_FROMLOC;
096.
097. IF V_QTYALLOCATED > 0 THEN
098. O_STATUS := 'E';
099. O_ERRDESC := 'ERROR LPN IS ALLOCATE';
100. RETURN;
101. END IF;
102.
103. IF V_FROMQTY <> V_QTYONHAND THEN
104. O_STATUS := 'E';
105. O_ERRDESC := 'ERROR QTY TRANSFER <> QTY LPN';
106. RETURN;
107. END IF;
108.
109. UPDATE WMWHSE8.TRANSFER
110. SET STATUS = 3,
111. EDITWHO = 'ADMINISTRATOR' ,
112. EDITDATE = SYSTIMESTAMP - INTERVAL '7' HOUR
113. WHERE TRANSFERKEY = I_TRANSFERNUM
114. AND STATUS IN ('0','3');
115.
116. UPDATE WMWHSE8.TRANSFERDETAIL
117. SET STATUS = 3,
118. EDITWHO = 'ADMINISTRATOR' ,
119. EDITDATE = SYSTIMESTAMP - INTERVAL '7' HOUR
120. WHERE TRANSFERKEY = I_TRANSFERNUM
121. AND TRANSFERLINENUMBER = I_TRANSFERLINE
122. AND STATUS IN ('0','3');
123.
124.
125. END IF;
126.
127. SELECT
128. COUNT(1)
129. INTO V_RECCOUNT
130. FROM WMWHSE8.LOTXLOCXID
131. WHERE ID = I_LPN
132. AND SKU = V_FROMSKU
133. AND STORERKEY = V_FROMSTORERKEY
134. AND LOC = V_FROMLOC;
135.
136. IF V_RECCOUNT <> 1 THEN
137. O_STATUS := 'E';
138. O_ERRDESC := 'ERROR CANNOT FIND PALLET IN SYSTEM';
139. ROLLBACK;
140. RETURN;
141. END IF;
142.
143. SELECT
144. LOC ,
145. QTY ,
146. QTYALLOCATED
147. INTO
148. V_FROMLOC2,
149. V_QTYONHAND,
150. V_QTYALLOCATED
151. FROM WMWHSE8.LOTXLOCXID
152. WHERE ID = I_LPN
153. AND SKU = V_FROMSKU
154. AND STORERKEY = V_FROMSTORERKEY
155. AND LOC = V_FROMLOC;
156.
157. IF V_QTYALLOCATED > 0 THEN
158. O_STATUS := 'E';
159. O_ERRDESC := 'ERROR LPN IS ALLOCATE';
160. RETURN;
161. END IF;
162.
163. IF V_FROMQTY <> V_QTYONHAND THEN
164. O_STATUS := 'E';
165. O_ERRDESC := 'ERROR QTY TRANSFER <> QTY LPN';
166. RETURN;
167. END IF;
168.
169.
170. IF V_FROMQTY = V_QTYONHAND THEN
171. UPDATE WMWHSE8.TRANSFER
172. SET STATUS = 3,
173. EDITWHO = 'ADMINISTRATOR' ,
174. EDITDATE = SYSTIMESTAMP - INTERVAL '7' HOUR
175. WHERE TRANSFERKEY = I_TRANSFERNUM
176. AND STATUS IN ('0','3');
177.
178. UPDATE WMWHSE8.TRANSFERDETAIL
179. SET STATUS = 3,
180. EDITWHO = 'ADMINISTRATOR' ,
181. EDITDATE = SYSTIMESTAMP - INTERVAL '7' HOUR,
182. FROMID = I_LPN,
183. TOID = I_LPN
184. WHERE TRANSFERKEY = I_TRANSFERNUM
185. AND TRANSFERLINENUMBER = I_TRANSFERLINE
186. AND STATUS IN ('0','3');
187.
188. O_STATUS := 'S';
189.
190. RETURN;
191. ELSE
192. O_STATUS := 'E';
193. O_ERRDESC := ' QTY TRANSFER > QTY ONHAND';
194. ROLLBACK;
195. RETURN;
196. END IF;
197.
198.
199.
200. DBMS_OUTPUT.PUT_LINE('SUCCESS TRANSFER');
201.
202.EXCEPTION
203. WHEN OTHERS THEN
204. O_STATUS := 'E';
205. O_ERRDESC := TO_CHAR(SYSTIMESTAMP ,'YYYY/MM/DD HH24:MI:SS') || ' => ' || 'ERPP ERROR ' || ' ' || V_PROCDESC || ' : ' || SUBSTR(TO_CHAR(SQLCODE) || ': ' || SQLERRM, 1, 2000);
206. DBMS_OUTPUT.PUT_LINE(O_ERRDESC);
207. ROLLBACK;
208. RETURN;
209.END;
210./
