Guest User

Untitled

a guest
Jul 24th, 2019
74
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION public.skici_internalconsumptionpost1(pinstance_id character varying, p_internal_cosumption_id character varying, p_action character varying)
  2. RETURNS void
  3. LANGUAGE plpgsql
  4. SET search_path TO '$user', 'public'
  5. AS $function$ DECLARE
  6. -- Logistice
  7. v_ResultStr VARCHAR(2000):=''; --OBTG:VARCHAR2--
  8. v_Message VARCHAR(2000):=''; --OBTG:VARCHAR2--
  9. Record_ID VARCHAR(32); --OBTG:VARCHAR2--
  10. v_Result NUMERIC:=1;
  11. -- Parameter
  12. --TYPE RECORD IS REFCURSOR;
  13. Cur_Parameter RECORD;
  14.  
  15. -- Parameter Variables
  16. v_IsProcessing CHAR(1) ;
  17. v_IsProcessed VARCHAR(60) ; --OBTG:VARCHAR2--
  18. v_MoveDate TIMESTAMP;
  19. v_Client_ID VARCHAR(32) ; --OBTG:VARCHAR2--
  20. v_Org_ID VARCHAR(32); --OBTG:VARCHAR2--
  21. v_User VARCHAR(32); --OBTG:VARCHAR2--
  22. v_line NUMERIC;
  23. v_Count NUMERIC:=0;
  24. v_action VARCHAR(60):='CO'; --OBTG:VARCHAR2--
  25. v_status M_Internal_Consumption.status%TYPE;
  26. v_name M_Internal_Consumption.name%TYPE;
  27. v_doctype_id M_Internal_consumption.em_skici_doctype_id%type;
  28. v_doctypereturn_id M_Internal_consumption.em_skici_doctype_id%type;
  29. v_productname m_product.name%TYPE;
  30.  
  31. --accounting dimension for header
  32. v_orgtrx_id m_internal_consumption.ad_orgtrx_id%type;
  33. v_project_id m_internal_consumption.c_project_id%type;
  34. v_campaign_id m_internal_consumption.c_campaign_id%type;
  35. v_activity_id m_internal_consumption.c_activity_id%type;
  36. v_user1_id m_internal_consumption.user1_id%type;
  37. v_user2_id m_internal_consumption.user2_id%type;
  38. v_costcenter_id m_internal_consumption.c_costcenter_id%type;
  39.  
  40. BEGIN
  41. -- Update AD_PInstance
  42. IF (pinstance_id IS NOT NULL) THEN
  43. RAISE NOTICE '%','Updating PInstance - Processing ' || PInstance_ID ;
  44. v_ResultStr:='PInstanceNotFound';
  45. PERFORM AD_UPDATE_PINSTANCE(PInstance_ID, NULL, 'Y', NULL, NULL) ;
  46. END IF;
  47. BEGIN --BODY
  48. -- Get Parameters
  49. IF (pinstance_id IS NOT NULL) THEN
  50. v_ResultStr:='ReadingParameters';
  51. FOR Cur_Parameter IN (
  52. SELECT i.Record_ID, i.AD_User_ID, p.ParameterName,
  53. p.P_String, p.P_Number, p.P_Date
  54. FROM AD_PInstance i
  55. LEFT JOIN AD_PInstance_Para p ON i.AD_PInstance_ID=p.AD_PInstance_ID
  56. WHERE i.AD_PInstance_ID=PInstance_ID
  57. ORDER BY p.SeqNo
  58. ) LOOP
  59. IF (upper(cur_parameter.parametername) LIKE 'ACTION') THEN
  60. v_action := cur_parameter.p_string;
  61. END IF;
  62. Record_ID := Cur_Parameter.Record_ID;
  63. v_User := Cur_Parameter.AD_User_ID;
  64. END LOOP; -- Get Parameter
  65. ELSE
  66. record_id := p_internal_cosumption_id;
  67. v_action := p_action;
  68. END IF;
  69. RAISE NOTICE '%',' Record_ID=' || Record_ID ;
  70. -- Reading Internal_Consumption
  71. SELECT MovementDate, Processing, Processed, AD_Client_ID,
  72. AD_Org_ID, COALESCE(v_user, updatedby), status, name, em_skici_doctype_id,
  73. ad_orgtrx_id, c_project_id, c_campaign_id, c_activity_id, user1_id, user2_id, c_costcenter_id
  74. INTO v_MoveDate, v_IsProcessing, v_IsProcessed, v_Client_ID,
  75. v_Org_ID, v_user, v_status, v_name, v_doctype_id,
  76. v_orgtrx_id, v_project_id, v_campaign_id, v_activity_id, v_user1_id, v_user2_id, v_costcenter_id
  77. FROM M_Internal_Consumption
  78. WHERE M_Internal_Consumption_ID=Record_ID FOR UPDATE;
  79. IF (v_IsProcessing = 'Y') THEN
  80. RAISE EXCEPTION '%', '@OtherProcessActive@' ; --OBTG:-20000--
  81. END IF;
  82. IF (v_IsProcessed = 'Y' AND v_action <> 'VO') THEN
  83. RAISE EXCEPTION '%', '@AlreadyPosted@' ; --OBTG:-20000--
  84. END IF;
  85.  
  86. if (v_doctype_id is null) then
  87. raise exception '%', '@SKICI_NoDocumentTypeFound@';
  88. end if;
  89.  
  90. select c_doctype_reversed_id into v_doctypereturn_id
  91. from c_doctype
  92. where c_doctype_id=v_doctype_id;
  93.  
  94. if (v_doctypereturn_id is null) then
  95. raise exception '%', '@SKICI_NoDocumentTypeReturnFound@';
  96. end if;
  97.  
  98. v_ResultStr:='CheckingRestrictions';
  99. SELECT COUNT(*), MAX(line)
  100. INTO v_Count, v_line
  101. FROM M_Internal_ConsumptionLine M, M_Product P
  102. WHERE M.M_PRODUCT_ID=P.M_PRODUCT_ID
  103. AND P.M_ATTRIBUTESET_ID IS NOT NULL
  104. AND (P.ATTRSETVALUETYPE IS NULL OR P.ATTRSETVALUETYPE <> 'F')
  105. AND (SELECT ISONEATTRSETVALREQUIRED FROM M_ATTRIBUTESET WHERE M_ATTRIBUTESET_ID = P.M_ATTRIBUTESET_ID) = 'Y'
  106. AND COALESCE(M.M_ATTRIBUTESETINSTANCE_ID, '0') = '0'
  107. AND M.M_Internal_Consumption_ID=Record_ID;
  108. IF (v_Count <> 0) THEN
  109. RAISE EXCEPTION '%', '@Inline@'||' '||v_line||' '||'@productWithoutAttributeSet@' ; --OBTG:-20000--
  110. END IF;
  111.  
  112. --Checking if the Internal consumption has lines
  113. SELECT count(*)
  114. INTO v_Count
  115. FROM M_Internal_ConsumptionLine
  116. WHERE M_Internal_Consumption_ID=Record_ID;
  117. IF (v_Count = 0) THEN
  118. RAISE EXCEPTION '%', '@InternalConsuptionNoLines@' ; --OBTG:-20000--
  119. END IF;
  120.  
  121. SELECT count(*) INTO v_count
  122. FROM dual
  123. WHERE EXISTS (
  124. SELECT 1
  125. FROM m_internal_consumptionline icl JOIN m_product p ON icl.m_product_id = p.m_product_id
  126. WHERE icl.m_internal_consumption_id = record_id
  127. AND p.isgeneric = 'Y');
  128. IF (v_count > 0) THEN
  129. SELECT max(p.name) INTO v_productname
  130. FROM m_internal_consumptionline icl JOIN m_product p ON icl.m_product_id = p.m_product_id
  131. WHERE icl.m_internal_consumption_id = record_id
  132. AND p.isgeneric = 'Y';
  133. RAISE EXCEPTION '%', '@CannotUseGenericProduct@ ' || v_productName; --OBTG:-20000--
  134. END IF;
  135.  
  136. IF (v_action IN ('CO')) THEN
  137. /**
  138. * Complete action: Create records on m_transaction and update status
  139. */
  140. DECLARE
  141. Cur_MoveLine RECORD;
  142. BEGIN
  143. FOR Cur_MoveLine IN (
  144. SELECT icl.*
  145. FROM M_Internal_ConsumptionLine icl
  146. JOIN M_Product p
  147. ON icl.M_Product_ID = p.M_Product_ID
  148. WHERE icl.M_Internal_Consumption_ID = Record_ID
  149. AND p.IsStocked = 'Y'
  150. ORDER BY icl.Line
  151. ) LOOP
  152. v_ResultStr:='Transaction for line' || Cur_MoveLine.Line;
  153.  
  154. INSERT INTO M_Transaction (
  155. M_Transaction_ID, AD_Client_ID, AD_Org_ID, IsActive,
  156. Created, CreatedBy, Updated, UpdatedBy,
  157. MovementType, M_Locator_ID, M_Product_ID, M_AttributeSetInstance_ID,
  158. MovementDate, MovementQty, M_Internal_ConsumptionLine_ID, M_Product_UOM_ID,
  159. QuantityOrder, C_UOM_ID
  160. ) VALUES (
  161. get_uuid(), Cur_MoveLine.AD_Client_ID, Cur_MoveLine.AD_Org_ID, 'Y',
  162. TO_DATE(NOW()), v_User, TO_DATE(NOW()), v_User,
  163. 'D-', Cur_MoveLine.M_Locator_ID, Cur_MoveLine.M_Product_ID, COALESCE(Cur_MoveLine.M_AttributeSetInstance_ID, '0'),
  164. v_MoveDate, (Cur_MoveLine.MovementQty * -1), Cur_MoveLine.M_Internal_ConsumptionLine_ID, Cur_MoveLine.M_Product_UOM_ID,
  165. (Cur_MoveLine.QuantityOrder * -1), Cur_MoveLine.C_UOM_ID
  166. );
  167. SELECT * INTO v_Result, v_Message FROM M_Check_Stock(Cur_MoveLine.M_Product_ID, v_Client_ID, v_Org_ID, Cur_MoveLine.M_Locator_ID) ;
  168. IF (v_Result = 0) THEN
  169. RAISE EXCEPTION '%', v_Message||' '||'@line@'||' '||Cur_MoveLine.line ; --OBTG:-20000--
  170. END IF;
  171. END LOOP;
  172. v_ResultStr:='ProcessingMovement';
  173. UPDATE M_Internal_Consumption
  174. SET Processed = 'Y',
  175. Status = 'CO',
  176. Updated = TO_DATE(NOW()),
  177. UpdatedBy = v_User
  178. WHERE M_Internal_Consumption_ID = Record_ID;
  179.  
  180. END;
  181. ELSIF (v_action IN ('VO')) THEN
  182. /*
  183. * Void Internal consumption.
  184. *
  185. * Create a new internal consumption negating the quantities and post it.
  186. */
  187. DECLARE
  188. v_voidintconsumption_id VARCHAR(32); --OBTG:VARCHAR2--
  189. BEGIN
  190. -- Checks
  191. IF (v_status <> 'CO') THEN
  192. RAISE EXCEPTION '%', '@ActionNotSupported@'; --OBTG:-20000--
  193. END IF;
  194. v_voidintconsumption_id := get_uuid();
  195. -- Create void internal consumption.
  196. INSERT INTO m_internal_consumption (
  197. m_internal_consumption_id, ad_client_id, ad_org_id, isactive,
  198. created, createdby, updated, updatedby,
  199. name, movementdate, em_skici_doctype_id, em_skici_documentno,
  200. ad_orgtrx_id, c_project_id, c_campaign_id, c_activity_id, user1_id, user2_id, c_costcenter_id
  201. ) VALUES (
  202. v_voidintconsumption_id, v_client_id, v_org_id, 'Y',
  203. TO_DATE(NOW()), v_user, TO_DATE(NOW()), v_user,
  204. 'VO: '|| v_name, v_MoveDate, v_doctypereturn_id, '<>',
  205. v_orgtrx_id, v_project_id, v_campaign_id, v_activity_id, v_user1_id, v_user2_id, v_costcenter_id
  206. );
  207.  
  208. INSERT INTO m_internal_consumptionline (
  209. m_internal_consumptionline_id, ad_client_id, ad_org_id, isactive,
  210. created, createdby, updated, updatedby,
  211. m_internal_consumption_id, line, m_locator_id,
  212. m_product_id, m_attributesetinstance_id,
  213. movementqty, c_uom_id, m_product_uom_id, quantityorder,
  214. description, void_intconsumption_line_id, em_skici_glitem_id
  215. )
  216. SELECT get_uuid(), ad_client_id, ad_org_id, isactive,
  217. TO_DATE(NOW()), v_user, TO_DATE(NOW()), v_user,
  218. v_voidintconsumption_id, line, m_locator_id,
  219. m_product_id, m_attributesetinstance_id,
  220. movementqty * -1, c_uom_id, m_product_uom_id, quantityorder * -1,
  221. description, m_internal_consumptionline_id, em_skici_glitem_id
  222. FROM m_internal_consumptionline
  223. WHERE m_internal_consumption_id = record_id;
  224.  
  225. --Update void
  226. UPDATE m_internal_consumption
  227. SET status = 'VO',
  228. updated = TO_DATE(NOW()),
  229. updatedby = v_user
  230. WHERE m_internal_consumption_id = record_id;
  231. -- post reveral internal consumption
  232. PERFORM M_INTERNAL_CONSUMPTION_POST1(NULL, v_voidintconsumption_id, 'CO');
  233. -- update reversal internal consumption
  234. UPDATE m_internal_consumption
  235. SET status = 'VO',
  236. updated = TO_DATE(NOW()),
  237. updatedby = v_user
  238. WHERE m_internal_consumption_id = v_voidintconsumption_id;
  239.  
  240. -- transactions related with original inout and with voided inout will be mark as is cost permanent
  241. UPDATE M_TRANSACTION TRX
  242. SET ISCOSTPERMANENT='Y'
  243. WHERE TRX.M_INTERNAL_CONSUMPTIONLINE_ID IN (SELECT M_INTERNAL_CONSUMPTIONLINE_ID
  244. FROM M_INTERNAL_CONSUMPTIONLINE
  245. WHERE (M_INTERNAL_CONSUMPTION_ID = record_id
  246. OR M_INTERNAL_CONSUMPTION_ID = v_voidintconsumption_id));
  247. END;
  248. END IF;
  249. v_ResultStr := 'UnLockingMovement';
  250. ---- <<END_PROCESSING>>
  251. -- Update AD_PInstance
  252. RAISE NOTICE '%','Updating PInstance - Finished ' || v_Message ;
  253. IF (pinstance_id IS NOT NULL) THEN
  254. PERFORM AD_UPDATE_PINSTANCE(PInstance_ID, v_User, 'N', v_Result, v_Message) ;
  255. END IF;
  256. RETURN;
  257. END; --BODY
  258. EXCEPTION
  259. WHEN OTHERS THEN
  260. v_ResultStr:= '@ERROR=' || SQLERRM;
  261. RAISE NOTICE '%',v_ResultStr ;
  262. IF (pinstance_id IS NOT NULL) THEN
  263. -- ROLLBACK;
  264. PERFORM AD_UPDATE_PINSTANCE(PInstance_ID, NULL, 'N', 0, v_ResultStr) ;
  265. ELSE
  266. RAISE EXCEPTION '%', SQLERRM;
  267. END IF;
  268. RETURN;
  269. END ; $function$
  270. ;
RAW Paste Data