SHARE
TWEET

Untitled

a guest Jul 24th, 2019 70 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
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top