Guest User

Untitled

a guest
Jul 18th, 2018
76
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 6.06 KB | None | 0 0
  1. CREATE OR REPLACE PACKAGE BODY R5.EX_AUTO_ORDER_CONF AS
  2.  
  3. PROCEDURE ORDER_AUTO_CONF_P1(P_GR NUMBER, P_TYP NUMBER DEFAULT 0)
  4. IS
  5.  
  6. CURSOR C1 IS
  7. SELECT FOUCFIN, FOUCNUF, FOUTYPE FROM FOUDGENE, FOUATTRI
  8. WHERE FOUCFIN = FATCFIN
  9. AND FATCLA = 'AORD'
  10. AND FATATT = P_GR
  11. AND ((FOUTYPE = P_TYP) OR (P_TYP = 0));
  12.  
  13. CURSOR C2(P_FIN NUMBER) IS
  14. SELECT ECDCINCDE, ECDCEXCDE, ECDSITE, ECDCFIN, ECDETAT, ECDTYPE, ECDDCOM FROM CDEENTCDE
  15. WHERE ECDCFIN = P_FIN
  16. AND ECDETAT IN (2,3)
  17. AND ECDTYPE IN (1,2,5) --- DO SPRAWDZENIA
  18. --and ecdcincde in (1888) --DO testow
  19. AND NOT EXISTS (SELECT 1 FROM EX_ORD_AUTO_CONF WHERE ECDCINCDE = CINCDE AND INDI < 2)
  20. AND ECDDCOM >= TRUNC(SYSDATE)
  21. ;
  22.  
  23.  
  24. V_STATUS NUMBER;
  25. V_CHECK NUMBER;
  26. V_SEQ NUMBER(12);
  27. V_QTY NUMBER;
  28. V_SITLI NUMBER;--GRU
  29.  
  30. BEGIN
  31.  
  32. SELECT EX_AUTO_CONF_ORD_SEQ.NEXTVAL
  33. INTO V_SEQ
  34. FROM DUAL;
  35.  
  36.  
  37.  
  38. FOR REC1 IN C1 LOOP
  39. --DBMS_OUTPUT.PUT_LINE(REC1.FOUCFIN);
  40.  
  41.  
  42. FOR REC2 IN C2(REC1.FOUCFIN) LOOP
  43. V_STATUS := 0;
  44. BEGIN
  45. SELECT ecdetat
  46. INTO V_CHECK
  47. FROM cdeentcde
  48. WHERE ECDCINCDE = REC2.ECDCINCDE
  49. for update of ecdetat nowait;
  50. EXCEPTION
  51. WHEN OTHERS THEN V_STATUS := 99; -- zmówienie w trakcie modyfikacji przez usera
  52. END;
  53.  
  54. V_QTY :=0;
  55. IF V_STATUS = 0 THEN
  56. BEGIN
  57. SELECT SUM(DCDQTEC)
  58. INTO V_QTY
  59. FROM CDEDETCDE
  60. WHERE DCDCINCDE = REC2.ECDCINCDE;
  61. EXCEPTION
  62. WHEN OTHERS THEN V_QTY := 0;
  63. END;
  64.  
  65. IF V_QTY = 0 THEN
  66. V_STATUS := 98; ---------ilosc w zamówieniu = 0
  67. END IF;
  68.  
  69.  
  70. END IF;
  71.  
  72. /*GRU*/
  73. V_SITLI :=0;
  74. IF V_STATUS = 0 THEN
  75. BEGIN
  76. SELECT ECDSITLI
  77. INTO V_SITLI
  78. FROM CDEENTCDE
  79. WHERE ECDCINCDE = REC2.ECDCINCDE;
  80. EXCEPTION
  81. WHEN OTHERS THEN V_QTY := 0;
  82. END;
  83.  
  84. IF V_QTY = 0 THEN
  85. V_STATUS := 999; --------- bledny nr magazynu
  86. END IF;
  87.  
  88.  
  89. END IF;
  90.  
  91. /*GRU*/
  92.  
  93. dBMS_OUTPUT.PUT_LINE(REC2.ECDCINCDE||' - '||V_STATUS);
  94. --DBMS_OUTPUT.PUT_LINE(REC2.ECDCINCDE);
  95.  
  96. BEGIN
  97. INSERT INTO EX_ORD_AUTO_CONF
  98. (CFIN, CNUF, FTYPE, CINCDE, CEXCDE,
  99. CDSITE, CDETAT, CDTYPE, CDDCOM, DCRE,
  100. PROG1, STAT1, DMAJ1,
  101. PROG2, STAT2, DMAJ2,
  102. PROG3, STAT3, DMAJ3, INDI, PROC_ID)
  103. VALUES (REC1.FOUCFIN, REC1.FOUCNUF, REC1.FOUTYPE, REC2.ECDCINCDE, REC2.ECDCEXCDE,
  104. REC2.ECDSITE, REC2.ECDETAT, REC2.ECDTYPE, REC2.ECDDCOM, SYSDATE,
  105. 'psgcf01p psgcf01p $USERID '||REC2.ECDCINCDE||' 2 GB',0,NULL,
  106. 'psccl30p psccl30p $USERID 0 '||V_SITLI||' -1 -1 -1 -1 -1 '||REC2.ECDCEXCDE||' '||TO_CHAR(SYSDATE,'dd/mm/yy hh24mi')||' 0 0000000 0 1 0 0 PL',0,NULL,
  107. NULL,NULL,NULL, V_STATUS, V_SEQ);
  108.  
  109. /*GRU
  110. VALUES (REC1.FOUCFIN, REC1.FOUCNUF, REC1.FOUTYPE, REC2.ECDCINCDE, REC2.ECDCEXCDE,
  111. REC2.ECDSITE, REC2.ECDETAT, REC2.ECDTYPE, REC2.ECDDCOM, SYSDATE,
  112. 'psgcf01p psgcf01p $USERID '||REC2.ECDCINCDE||' 2 GB',0,NULL,
  113. 'psccl30p psccl30p $USERID 0 10 -1 -1 -1 -1 -1 '||REC2.ECDCEXCDE||' '||TO_CHAR(SYSDATE,'dd/mm/yy hh24mi')||' 0 0000000 0 1 0 0 PL',0,NULL,
  114. NULL,NULL,NULL, V_STATUS, V_SEQ);
  115. GRU*/
  116.  
  117. EXCEPTION
  118. WHEN OTHERS THEN V_STATUS := 2;
  119. END;
  120.  
  121. DBMS_OUTPUT.PUT_LINE(REC1.FOUCNUF||' '||REC2.ECDCEXCDE||' '||V_STATUS);
  122.  
  123. COMMIT;
  124.  
  125.  
  126. END LOOP;
  127. END LOOP;
  128. END ORDER_AUTO_CONF_P1;
  129.  
  130.  
  131.  
  132.  
  133. PROCEDURE ORDER_AUTO_CONF_P2
  134. IS
  135.  
  136. CURSOR C1 IS
  137. SELECT CINCDE,ECDETAT FROM EX_ORD_AUTO_CONF,CDEENTCDE
  138. WHERE ECDCINCDE = CINCDE
  139. AND STAT1 = 0
  140. AND INDI = 0 ;
  141.  
  142. CURSOR C2 IS
  143. SELECT CINCDE,ECDETAT,INDI,CNUF, CEXCDE FROM EX_ORD_AUTO_CONF,CDEENTCDE
  144. WHERE ECDCINCDE = CINCDE
  145. AND STAT1 = 1
  146. AND INDI = 0 ;
  147.  
  148. V_STATUS NUMBER;
  149. V_INDI NUMBER;
  150.  
  151. BEGIN
  152. FOR REC1 IN C1 LOOP
  153. V_INDI := 0;
  154. IF REC1.ECDETAT = 3 THEN
  155. V_STATUS := 1;
  156. ELSE
  157. V_STATUS := 0;
  158. V_INDI := 3;
  159. END IF;
  160.  
  161.  
  162. UPDATE EX_ORD_AUTO_CONF
  163. SET STAT1 = V_STATUS, INDI = V_INDI
  164. WHERE CINCDE = REC1.CINCDE
  165. AND INDI = 0;
  166.  
  167. COMMIT;
  168. END LOOP;
  169.  
  170. FOR REC2 IN C2 LOOP
  171. IF REC2.ECDETAT = 3 AND REC2.INDI = 0 THEN
  172. V_INDI := 0;
  173. BEGIN
  174. UPDATE CDEDETCDE
  175. SET DCDETAT = 5
  176. WHERE DCDCINCDE = REC2.CINCDE;
  177. EXCEPTION
  178. WHEN OTHERS THEN V_INDI := 4;
  179. END;
  180.  
  181. IF V_INDI = 0 THEN
  182. BEGIN
  183. UPDATE CDEENTCDE
  184. SET ECDETAT = 5
  185. WHERE ECDCINCDE = REC2.CINCDE;
  186. EXCEPTION
  187. WHEN OTHERS THEN V_INDI := 5;
  188. END;
  189.  
  190.  
  191.  
  192. IF V_INDI = 0 THEN
  193. COMMIT;
  194. DBMS_OUTPUT.PUT_LINE(REC2.CNUF||' '||REC2.CEXCDE||' '||V_STATUS);
  195. ELSE
  196. ROLLBACK;
  197. END IF;
  198. END IF;
  199. END IF;
  200.  
  201. UPDATE EX_ORD_AUTO_CONF
  202. SET INDI = V_INDI
  203. WHERE CINCDE = REC2.CINCDE
  204. AND INDI = 0;
  205.  
  206. COMMIT;
  207.  
  208. END LOOP;
  209.  
  210. END;
  211.  
  212.  
  213. PROCEDURE ORDER_AUTO_CONF_P3
  214. IS
  215.  
  216. CURSOR C1(P_ID NUMBER) IS
  217. SELECT CINCDE, FTYPE, ROWID
  218. FROM EX_ORD_AUTO_CONF
  219. WHERE PROC_ID = P_ID
  220. AND INDI = 0;
  221.  
  222. V_ID NUMBER;
  223. V_INDI NUMBER;
  224. V_CHK NUMBER;
  225.  
  226.  
  227. BEGIN
  228.  
  229.  
  230. SELECT MAX(PROC_ID)
  231. INTO V_ID
  232. FROM EX_ORD_AUTO_CONF;
  233.  
  234.  
  235. FOR REC1 IN C1(V_ID) LOOP
  236. V_INDI := 1;
  237.  
  238. BEGIN
  239. SELECT 1
  240. INTO V_CHK
  241. FROM CDEENTCDE
  242. WHERE ECDCINCDE = REC1.CINCDE
  243. AND ECDETAT >= 5;
  244. EXCEPTION
  245. WHEN NO_DATA_FOUND THEN V_INDI := 97;
  246. END;
  247.  
  248. IF V_INDI = 1 AND REC1.FTYPE = 3 THEN
  249. BEGIN
  250. SELECT 1
  251. INTO V_CHK
  252. FROM CCLDETCCL
  253. WHERE CCLCINCDE = REC1.CINCDE
  254. AND ROWNUM =1;
  255. EXCEPTION
  256. WHEN NO_DATA_FOUND THEN V_INDI := 96;
  257. END;
  258. END IF;
  259.  
  260.  
  261. UPDATE EX_ORD_AUTO_CONF
  262. SET INDI = V_INDI
  263. WHERE ROWID = REC1.ROWID;
  264.  
  265. COMMIT;
  266.  
  267. END LOOP;
  268. END;
  269.  
  270. END EX_AUTO_ORDER_CONF;
  271. /
Add Comment
Please, Sign In to add comment