📄 m_inout_post.sql
字号:
CREATE OR REPLACE PROCEDURE M_InOut_Post
(
p_PInstance_ID IN NUMBER,
p_InOut_ID IN NUMBER -- DEFAULT NULL
)
/*************************************************************************
* The contents of this file are subject to the Compiere License. You may
* obtain a copy of the License at http://www.compiere.org/license.html
* Software is on an "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either
* express or implied. See the License for details. Code: Compiere ERP+CRM
* Copyright (C) 1999-2001 Jorg Janke, ComPiere, Inc. All Rights Reserved.
*************************************************************************
* $Id: M_InOut_Post.sql,v 1.5 2003/01/27 06:22:11 jjanke Exp $
***
* Title: Post M_InOut_ID
* Description:
* Action: COmplete
* - Create Transaction
* (only stocked products)
* - Update Inventory (QtyReserved, QtyOnHand)
* (only stocked products)
* - Update OrderLine (QtyDelivered)
*
* Action: Reverse Correction
* - Create Header and lines with negative Quantities (and header amounts)
* - Post it
************************************************************************/
AS
-- Logistice
v_ResultStr VARCHAR2(2000);
v_Message VARCHAR2(2000);
v_Record_ID NUMBER;
-- Parameter
CURSOR Cur_Parameter (PInstance NUMBER) IS
SELECT i.Record_ID, p.ParameterName, p.P_String, p.P_Number, p.P_Date
FROM AD_PInstance i, AD_PInstance_Para p
WHERE i.AD_PInstance_ID=PInstance
AND i.AD_PInstance_ID=p.AD_PInstance_ID(+)
ORDER BY p.SeqNo;
--
CURSOR Cur_InOut (ID NUMBER) IS
SELECT *
FROM M_InOut
WHERE (M_InOut_ID=ID OR (ID IS NULL AND DocAction='CO'))
AND IsActive='Y'
FOR UPDATE;
CURSOR Cur_InOutLine (ID NUMBER) IS
SELECT *
FROM M_InOutLine
WHERE M_InOut_ID=ID AND IsActive='Y'
FOR UPDATE;
--
v_NextNo NUMBER;
v_Qty NUMBER;
v_QtyPO NUMBER;
v_QtySO NUMBER;
v_RDocumentNo VARCHAR2(40);
v_RInOut_ID NUMBER;
v_IsStocked NUMBER;
BEGIN
IF (p_PInstance_ID IS NOT NULL) THEN
-- Update AD_PInstance
DBMS_OUTPUT.PUT_LINE('Updating PInstance - Processing ' || p_PInstance_ID);
v_ResultStr := 'PInstanceNotFound';
UPDATE AD_PInstance
SET Created = SysDate,
IsProcessing = 'Y'
WHERE AD_PInstance_ID=p_PInstance_ID;
COMMIT;
-- Get Parameters
v_ResultStr := 'ReadingParameters';
FOR p IN Cur_Parameter (p_PInstance_ID) LOOP
v_Record_ID := p.Record_ID;
END LOOP; -- Get Parameter
DBMS_OUTPUT.PUT_LINE(' Record_ID=' || v_Record_ID);
ELSE
DBMS_OUTPUT.PUT_LINE('<<M_InOut_Post>>');
v_Record_ID := p_InOut_ID;
END IF;
-- Process Shipments
FOR sh IN Cur_InOut (v_Record_ID) LOOP
DBMS_OUTPUT.PUT_LINE('Shipment_ID=' || sh.M_InOut_ID || ', Doc=' || sh.DocumentNo ||
', Status=' || sh.DocStatus || ', Action=' || sh.DocAction);
v_ResultStr := 'HeaderLoop';
/**
* Shipment not processed
*/
IF (sh.Processed = 'N' AND sh.DocStatus = 'DR' AND sh.DocAction = 'CO') THEN
-- For all active shipment lines
v_ResultStr := 'HeaderLoop-1';
FOR sl IN Cur_InOutLine (sh.M_InOut_ID) LOOP
-- Incomming or Outgoing ??
v_Qty := sl.MovementQty;
IF (SUBSTR(sh.MovementType, 2) = '-') THEN
v_Qty := - sl.MovementQty;
END IF;
IF (sh.IsSOTrx = 'N') THEN
v_QtySO := 0;
v_QtyPO := sl.MovementQty;
ELSE
v_QtySO := sl.MovementQty;
v_QtyPO := 0;
END IF;
-- UOM Conversion
-- Is it a standard stocked product?
SELECT COUNT(*) INTO v_IsStocked
FROM M_Product
WHERE M_Product_ID=sl.M_Product_ID
AND IsStocked = 'Y' AND ProductType = 'I';
-- Create Transaction for stocked product
IF (sl.M_Product_ID IS NOT NULL AND v_IsStocked = 1) THEN
v_ResultStr := 'CreateTransaction';
AD_Sequence_Next('M_Transaction', sl.AD_Org_ID, v_NextNo);
INSERT INTO M_Transaction
(M_Transaction_ID, M_InOutLine_ID,
AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
MovementType, M_Locator_ID, M_Product_ID,
MovementDate, MovementQty)
VALUES
(v_NextNo, sl.M_InOutLine_ID,
sl.AD_Client_ID, sl.AD_Org_ID, 'Y', SysDate, sl.UpdatedBy, SysDate, sl.UpdatedBy,
sh.MovementType, sl.M_Locator_ID, sl.M_Product_ID,
sh.MovementDate, v_Qty);
END IF;
-- Create Asset
IF (sl.M_Product_ID IS NOT NULL AND sh.IsSOTrx = 'Y') THEN
A_Asset_Create (null, sl.M_InOutLine_ID);
END IF;
v_ResultStr := 'UpdateOrderLine';
IF (sl.C_OrderLine_ID IS NOT NULL) THEN
-- stocked product
IF (sl.M_Product_ID IS NOT NULL AND v_IsStocked = 1) THEN
-- Update OrderLine (if C-, Qty is negative)
UPDATE C_OrderLine
SET QtyReserved = QtyReserved - v_QtyPO - v_QtySO,
QtyDelivered = QtyDelivered - v_Qty,
Updated = SysDate
WHERE C_OrderLine_ID = sl.C_OrderLine_ID;
-- Update Inventory Storage
v_ResultStr := 'UpdateInventory';
UPDATE M_Storage
SET QtyOnHand = QtyOnHand + v_Qty,
QtyReserved = QtyReserved - v_QtySO,
QtyOrdered = QtyOrdered - v_QtyPO,
Updated = SysDate
WHERE M_Locator_ID = sl.M_Locator_ID
AND M_Product_ID = sl.M_Product_ID;
-- Products not stocked
ELSE
-- Update OrderLine (if C-, Qty is negative)
UPDATE C_OrderLine
SET QtyDelivered = QtyDelivered - v_Qty,
Updated = SysDate
WHERE C_OrderLine_ID = sl.C_OrderLine_ID;
END IF;
-- Direct entry of Shipment line for stocked product
ELSIF (sl.M_Product_ID IS NOT NULL AND v_IsStocked = 1) THEN
-- Only Update Inventory Storage
v_ResultStr := 'UpdateInventory';
UPDATE M_Storage
SET QtyOnHand = QtyOnHand + v_Qty,
Updated = SysDate
WHERE M_Locator_ID = sl.M_Locator_ID
AND M_Product_ID = sl.M_Product_ID;
-- Product not on Stock yet
IF (SQL%ROWCOUNT = 0) THEN
INSERT INTO M_Storage
(M_Product_ID, M_Locator_ID,
AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
QtyOnHand, QtyReserved, QtyOrdered)
VALUES
(sl.M_Product_ID, sl.M_Locator_ID,
sl.AD_Client_ID, sl.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,
v_Qty, 0, 0);
END IF;
END IF;
END LOOP; -- For all InOut Lines
/*******************
* PO Matching
******************/
IF (sh.IsSOTrx = 'N') THEN
DECLARE
CURSOR Cur_SLines IS
SELECT sl.AD_Client_ID,sl.AD_Org_ID,
ol.C_OrderLine_ID, sl.M_InOutLine_ID, sl.M_Product_ID,
sl.MovementQty, ol.QtyOrdered
FROM M_InOutLine sl, C_OrderLine ol
WHERE sl.C_OrderLine_ID=ol.C_OrderLine_ID
AND sl.M_Product_ID=ol.M_Product_ID
AND sl.M_InOut_ID=sh.M_InOut_ID;
v_Qty NUMBER;
v_MatchPO_ID NUMBER(10);
BEGIN
v_ResultStr := 'MatchPO';
FOR ml IN Cur_SLines LOOP
AD_Sequence_Next('M_MatchPO', ml.AD_Org_ID, v_MatchPO_ID);
-- The min qty
v_Qty := ml.MovementQty;
IF (ABS(ml.MovementQty) > ABS(ml.QtyOrdered)) THEN
v_Qty := ml.QtyOrdered;
END IF;
v_ResultStr := 'InsertMatchPO ' || v_MatchPO_ID;
INSERT INTO M_MatchPO
(M_MatchPO_ID,
AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy,
M_InOutLine_ID,C_OrderLine_ID,
M_Product_ID,DateTrx,Qty,
Processing,Processed,Posted)
VALUES
(v_MatchPO_ID,
ml.AD_Client_ID,ml.AD_Org_ID,'Y',SysDate,0,SysDate,0,
ml.M_InOutLine_ID,ml.C_OrderLine_ID,
ml.M_Product_ID,SysDate,v_Qty,
'N','Y','N');
END LOOP;
END;
END IF;
-- Close Shipment
v_ResultStr := 'CloseShipment';
UPDATE M_InOut
SET Processed='Y',
DocStatus = 'CO',
DocAction = '--',
Updated = SysDate
WHERE CURRENT OF Cur_InOut;
--
v_ResultStr := 'LogEntry';
IF (p_PInstance_ID IS NOT NULL) THEN
INSERT INTO AD_PInstance_Log (AD_PInstance_ID, Log_ID, P_ID, P_Msg)
VALUES (p_PInstance_ID, sh.M_InOut_ID, sh.M_InOut_ID, sh.DocAction || ': ' || sh.DocumentNo);
END IF;
-- Not Processed + Complete --
/**
* Reverse Correction
*/
ELSIF (sh.DocStatus = 'CO' AND sh.DocAction = 'RC') THEN
v_ResultStr := 'CreateInOut';
AD_Sequence_Next('M_InOut', sh.M_InOut_ID, v_RInOut_ID); -- Get RInOut_ID
AD_Sequence_DocType(sh.C_DocType_ID, sh.M_InOut_ID, v_RDocumentNo); -- Get RDocumentNo
-- Indicate that it is invoiced (i.e. not printed on invoices)
v_ResultStr := 'SetInvoiced';
UPDATE M_InOutLine
SET IsInvoiced='Y'
WHERE M_InOut_ID = sh.M_InOut_ID;
--
DBMS_OUTPUT.PUT_LINE('Reverse InOut_ID=' || v_RInOut_ID || ' DocumentNo=' || v_RDocumentNo);
v_ResultStr := 'InsertInOut Reverse ' || v_RInOut_ID;
INSERT INTO M_InOut
(M_InOut_ID, C_Order_ID, IsSOTrx,
AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
DocumentNo, C_DocType_ID, Description, IsPrinted,
MovementType, MovementDate, DateAcct,
C_BPartner_ID, C_BPartner_Location_ID, C_BPartner_Contact_ID,
M_Warehouse_ID, POReference, DateOrdered, DeliveryRule,
FreightCostRule, FreightAmt,
DeliveryViaRule, M_Shipper_ID, C_Charge_ID, ChargeAmt, PriorityRule,
DocStatus, DocAction, Processing, Processed)
VALUES
(v_RInOut_ID, sh.C_Order_ID, sh.IsSOTrx,
sh.AD_Client_ID, sh.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,
v_RDocumentNo, sh.C_DocType_ID, '(*R*: ' || sh.DocumentNo || ') ' || sh.Description, 'N',
sh.MovementType, sh.MovementDate, sh.DateAcct,
sh.C_BPartner_ID, sh.C_BPartner_Location_ID, sh.C_BPartner_Contact_ID,
sh.M_Warehouse_ID, sh.POReference, sh.DateOrdered, sh.DeliveryRule,
sh.FreightCostRule, sh.FreightAmt * -1,
sh.DeliveryViaRule, sh.M_Shipper_ID, sh.C_Charge_ID, sh.ChargeAmt * -1, sh.PriorityRule,
'DR', 'CO', 'N', 'N');
v_ResultStr := 'InsertInOutLine';
FOR sl IN Cur_InOutLine (sh.M_InOut_ID) LOOP
-- Create InOut Line
AD_Sequence_Next('M_InOutLine', sh.M_InOut_ID, v_NextNo);
v_ResultStr := 'CreateInOutLine';
INSERT INTO M_InOutLine
(M_InOutLine_ID, Line, M_InOut_ID, C_OrderLine_ID,
AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
M_Product_ID, C_UOM_ID, M_Locator_ID,
MovementQty, Description, IsInvoiced, Lot, SerNo)
VALUES
(v_NextNo, sl.Line, v_RInOut_ID, sl.C_OrderLine_ID,
sh.AD_Client_ID, sh.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,
sl.M_Product_ID, sl.C_UOM_ID, sl.M_Locator_ID,
sl.MovementQty * -1, '*R*: ' || sl.Description, sl.IsInvoiced, sl.Lot, sl.SerNo);
END LOOP;
-- Close Order
v_ResultStr := 'CloseInOut';
UPDATE M_InOut
SET Description = NVL(Description, '') || ' (*R*=' || v_RDocumentNo || ')',
Processed='Y',
DocStatus = 'RE', -- it IS reversed
DocAction = '--',
Updated = SysDate
WHERE CURRENT OF Cur_InOut;
v_ResultStr := 'LogEntry';
IF (p_PInstance_ID IS NOT NULL) THEN
INSERT INTO AD_PInstance_Log (AD_PInstance_ID, Log_ID, P_ID, P_Msg)
VALUES (p_PInstance_ID, sh.M_InOut_ID, sh.M_InOut_ID, sh.DocAction || ': ' || sh.DocumentNo);
END IF;
-- Post Reversal
v_ResultStr := 'PostReversal';
M_InOut_Post (NULL, v_RInOut_ID);
-- Indicate as Reversal Transaction
v_ResultStr := 'IndicateReversal';
UPDATE M_InOut
SET DocStatus = 'RE' -- the reversal transaction
WHERE M_InOut_ID = v_RInOut_ID;
END IF; -- ReverseCorrection
END LOOP; -- InOut Header
/**
* Transaction End
*/
v_ResultStr := 'Fini';
<<FINISH_PROCESS>>
IF (p_PInstance_ID IS NOT NULL) THEN
-- Update AD_PInstance
DBMS_OUTPUT.PUT_LINE('Updating PInstance - Finished ' || v_Message);
UPDATE AD_PInstance
SET Updated = SysDate,
IsProcessing = 'N',
Result = 1, -- success
ErrorMsg = v_Message
WHERE AD_PInstance_ID=p_PInstance_ID;
COMMIT;
ELSE
DBMS_OUTPUT.PUT_LINE('<<M_InOut_Post finished>>');
END IF;
RETURN;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
v_ResultStr := v_ResultStr || ': ' || SQLERRM || ' - ' || v_Message;
DBMS_OUTPUT.PUT_LINE(v_ResultStr);
IF (p_InOut_ID = 0) THEN
UPDATE AD_PInstance
SET Updated = SysDate,
IsProcessing = 'N',
Result = 0, -- failure
ErrorMsg = v_ResultStr
WHERE AD_PInstance_ID=p_PInstance_ID;
COMMIT;
ELSE
DECLARE
v_Code NUMBER := SQLCode;
BEGIN
INSERT INTO DBA_ErrorLog (DBA_ErrorLog_ID,Created,Code,Msg,Info)
VALUES (DBA_ErrorLog_Seq.NextVal,SysDate,v_Code,v_ResultStr,'M_InOut_Post');
END;
END IF;
RETURN;
END M_InOut_Post;
/
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -