📄 c_invoice_create.sql
字号:
CREATE OR REPLACE PROCEDURE C_Invoice_Create
(
p_PInstance_ID IN NUMBER,
p_Order_ID IN NUMBER DEFAULT NULL,
p_Invoice_ID OUT NUMBER
)
/*************************************************************************
* 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: C_Invoice_Create.sql,v 1.8 2003/01/27 06:22:11 jjanke Exp $
***
* Title: Create Invoice
* Description:
* - Based on Invoice Rules create Invoice
* - Update Order while creating the lines
************************************************************************/
AS
-- Logistice
v_ResultStr VARCHAR2(2000) := '';
v_Message VARCHAR2(2000) := '';
-- 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;
Record_ID NUMBER := NULL;
p_AD_Org_ID NUMBER := NULL;
p_BPartner_ID NUMBER := NULL;
p_Selection VARCHAR2(1) := 'N';
p_DateInvoiced DATE := NULL;
--
v_DateInvoiced DATE;
v_NoRecords NUMBER := 0;
-- Orders to process
CURSOR Cur_Order (Order_ID NUMBER, BPartner_ID NUMBER, Org_ID NUMBER) IS
SELECT *
FROM C_Order o
-- Specific InProgress Order
WHERE (C_Order_ID = Order_ID AND o.DocStatus IN ('IP', 'CO'))
-- OR all completed and closed orders
OR (Order_ID IS NULL AND o.DocStatus IN ('CO', 'CL') AND IsSOTrx='Y'
-- not for InvoiceSchedule
AND InvoiceRule <> 'S'
-- of all or a specific business partner / organization
AND (BPartner_ID IS NULL OR C_BPartner_ID = BPartner_ID)
AND (Org_ID IS NULL OR AD_Org_ID = Org_ID)
-- where there is something to invoice
AND EXISTS (SELECT * FROM C_OrderLine l
WHERE o.C_Order_ID=l.C_Order_ID
AND l.QtyOrdered <> l.QtyInvoiced) )
ORDER BY PriorityRule, C_BPartner_ID, DocumentNo;
--
v_NextNo NUMBER;
v_DocType_ID NUMBER;
v_DocumentNo VARCHAR2(40);
v_Qty NUMBER;
v_Approved CHAR(1);
v_Reference VARCHAR2(256);
v_DocSubTypeSO CHAR(2);
--
v_LineNo NUMBER := 0;
-- Order Lines not invoiced Invoice Rule: Immediate
CURSOR Cur_OrderLine (Order_ID NUMBER) IS
SELECT *
FROM C_OrderLine l
WHERE QtyOrdered <> QtyInvoiced
AND C_Order_ID = Order_ID
ORDER BY Line
FOR UPDATE;
ptr_ol Cur_OrderLine%ROWTYPE;
-- Shipment Lines of Order Lines Invoice Rule: Delivery
CURSOR Cur_InOutLine (Order_ID NUMBER) IS
SELECT ol.AD_Client_ID, ol.AD_Org_ID, sl.M_InOut_ID, sl.M_InOutLine_ID, ol.C_OrderLine_ID,
NVL(sl.Description, ol.Description) AS Description,
NVL(sl.M_Product_ID, ol.M_Product_ID) AS M_Product_ID,
NVL(sl.MovementQty, ol.QtyOrdered-ol.QtyInvoiced) AS MovementQty,
ol.PriceList, ol.PriceActual, ol.PriceLimit,
ol.C_Charge_ID, ol.ChargeAmt,
NVL(sl.C_UOM_ID, ol.C_UOM_ID) C_UOM_ID,
ol.C_Tax_ID, --ol.IsTaxIncluded,
ol.Line, ol.DirectShip
FROM M_InOutLine sl, C_OrderLine ol
WHERE sl.C_OrderLine_ID(+)=ol.C_OrderLine_ID
AND sl.M_InOut_ID IS NOT NULL -- We need to have a shipment
AND ol.C_Order_ID=Order_ID -- parameter
AND (sl.IsInvoiced IS NULL OR sl.IsInvoiced='N')
ORDER BY ol.Line -- single Order
FOR UPDATE;
ptr_sl Cur_InOutLine%ROWTYPE;
BEGIN
-- Process Parameters
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';
Record_ID := NULL;
FOR p IN Cur_Parameter (p_PInstance_ID) LOOP
-- Record_ID := p.Record_ID;
IF (p.ParameterName = 'DateInvoiced') THEN
p_DateInvoiced := p.P_Date;
DBMS_OUTPUT.PUT_LINE(' DateInvoiced=' || p_DateInvoiced);
ELSIF (p.ParameterName = 'AD_Org_ID') THEN
p_AD_Org_ID := p.P_Number;
DBMS_OUTPUT.PUT_LINE(' AD_Org_ID=' || p_AD_Org_ID);
ELSIF (p.ParameterName = 'C_Order_ID') THEN
Record_ID := p.P_Number;
DBMS_OUTPUT.PUT_LINE(' Record_ID=' || Record_ID);
ELSIF (p.ParameterName = 'C_BPartner_ID') THEN
p_BPartner_ID := p.P_Number;
DBMS_OUTPUT.PUT_LINE(' C_BPartner_ID=' || p_BPartner_ID);
ELSIF (p.ParameterName = 'Selection') THEN
p_Selection := p.P_String;
DBMS_OUTPUT.PUT_LINE(' Selection=' || p_Selection);
ELSE
DBMS_OUTPUT.PUT_LINE('*** Unknown Parameter=' || p.ParameterName);
END IF;
END LOOP; -- Get Parameter
ELSE
DBMS_OUTPUT.PUT_LINE('<<C_Invoive_Create>>');
Record_ID := p_Order_ID;
p_DateInvoiced := NULL;
p_BPartner_ID := NULL;
p_Selection := 'N';
END IF;
/**
* Order Loop == all not completely invoiced orders == No Summary ==
*/
IF (p_Selection <> 'Y') THEN
DBMS_OUTPUT.PUT_LINE(' Record_ID=' || Record_ID
|| ', BPartner_ID=' || p_BPartner_ID || ', AD_Org_ID=' || p_AD_Org_ID);
-- For all Orders
FOR o IN Cur_Order (Record_ID, p_BPartner_ID, p_AD_Org_ID) LOOP
DBMS_OUTPUT.PUT_LINE('Order ' || o.DocumentNo || ', ID=' || o.C_Order_ID);
-- Parameter Order Date => Invoice Date, Accounting Date
v_DateInvoiced := NVL(p_DateInvoiced, o.DateOrdered);
/**
* Invoice Rules
*/
-- (I)mmediate -- Invoice the full/remaining order
IF (o.InvoiceRule = 'I') THEN
DBMS_OUTPUT.PUT_LINE('Invoice Rule: Immediate');
v_ResultStr := 'Fetching_OrderLine';
OPEN Cur_OrderLine (o.C_Order_ID);
FETCH Cur_OrderLine INTO ptr_ol;
IF (Cur_OrderLine%NOTFOUND) THEN
DBMS_OUTPUT.PUT_LINE('- No Lines -');
GOTO Next_Order;
END IF;
-- Shipment Reference (optional)
BEGIN
SELECT dt.PrintName || ' ' || m.DocumentNo
INTO v_Reference
FROM M_InOut m, C_DocType dt, C_Order ox
WHERE m.C_DocType_ID=dt.C_DocType_ID
AND m.C_Order_ID=ox.C_Order_ID
AND ROWNUM=1 -- last shipment
AND ox.C_Order_ID=o.C_Order_ID
ORDER BY m.DocumentNo DESC;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('- No ShipReference -');
END;
-- After (D)elivery -- Check M_InOut for invoice quantity
ELSIF (o.InvoiceRule = 'D') THEN
DBMS_OUTPUT.PUT_LINE('Invoice Rule: Delivery');
v_ResultStr := 'Fetching_ShipmentLine';
OPEN Cur_InOutLine (o.C_Order_ID);
FETCH Cur_InOutLine INTO ptr_sl;
IF (Cur_InOutLine%NOTFOUND) THEN
DBMS_OUTPUT.PUT_LINE('- No Lines -');
GOTO Next_Order;
END IF;
-- Shipment Reference
v_ResultStr := 'Ship Reference Order_ID=' || o.C_Order_ID;
SELECT dt.PrintName || ' ' || m.DocumentNo
INTO v_Reference
FROM M_InOut m, C_DocType dt
WHERE m.C_DocType_ID=dt.C_DocType_ID
AND m.M_InOut_ID=ptr_sl.M_InOut_ID;
-- After (O)rder completely delivered
ELSIF (o.InvoiceRule = 'O') THEN
DBMS_OUTPUT.PUT_LINE('Invoice Rule: Order - not implemented yet ');
GOTO Next_Order;
-- (S)chedule after Delivery -> Invoice Schedule (ignore here)
ELSE
GOTO Next_Order;
END IF;
/**
* Create Invoice Header ---------------------------------------
*/
-- Get Order DocType Info - approved from Invoice DocType
v_ResultStr := 'GetDocTypeInfo - ' || o.C_DocType_ID;
SELECT od.C_DocTypeInvoice_ID, DECODE(id.IsApproved, 'Y', 'N', 'Y'), od.DocSubTypeSO
INTO v_DocType_ID, v_Approved, v_DocSubTypeSO
FROM C_DocType od, C_DocType id
WHERE od.C_DocType_ID=o.C_DocType_ID
AND od.C_DocTypeInvoice_ID = id.C_DocType_ID;
--
AD_Sequence_Next('C_Invoice', o.AD_Client_ID, p_Invoice_ID);
AD_Sequence_DocType(v_DocType_ID, o.AD_Client_ID, v_DocumentNo);
--
DBMS_OUTPUT.PUT_LINE(' Invoice_ID=' || p_Invoice_ID || ' DocumentNo=' || v_DocumentNo);
v_ResultStr := 'InsertInvoice ' || p_Invoice_ID;
INSERT INTO C_Invoice
(C_Invoice_ID, C_Order_ID,
AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
IsSOTrx, DocumentNo, DocStatus, DocAction, Processing, Processed,
C_DocType_ID, C_DocTypeTarget_ID, Description,
IsApproved, IsTransferred, SalesRep_ID,
DateInvoiced, DatePrinted, IsPrinted, DateAcct,
C_PaymentTerm_ID, C_BPartner_ID, C_BPartner_Location_ID, C_BPartner_Contact_ID,
POReference, DateOrdered, IsDiscountPrinted,
C_Currency_ID, PaymentRule, C_Charge_ID, ChargeAmt,
TotalLines, GrandTotal,
M_PriceList_ID, C_Campaign_ID, C_Project_ID, C_Activity_ID, C_Payment_ID, C_CashLine_ID)
VALUES
(p_Invoice_ID, o.C_Order_ID,
o.AD_Client_ID, o.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,
o.IsSOTrx, v_DocumentNo, 'DR', 'CO', 'N', 'N',
v_DocType_ID, v_DocType_ID, o.Description,
v_Approved, 'N', o.SalesRep_ID,
v_DateInvoiced, NULL, 'N', v_DateInvoiced, -- DateInvoiced=DateAcct
o.C_PaymentTerm_ID, o.C_BPartner_ID, o.BillTo_ID, o.C_BPartner_Contact_ID,
o.POReference, o.DateOrdered, o.IsDiscountPrinted,
o.C_Currency_ID, o.PaymentRule, o.C_Charge_ID, o.ChargeAmt,
0, 0,
o.M_PriceList_ID, o.C_Campaign_ID, o.C_Project_ID, o.C_Activity_ID, o.C_Payment_ID, o.C_CashLine_ID);
--
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, o.C_Order_ID, p_Invoice_ID, '@Created@ @Invoice@ ' || v_DocumentNo);
END IF;
v_NoRecords := v_NoRecords + 1;
v_LineNo := 0;
-- Insert Reference/Comment to Shipment for Warehouse Order/Pickup
IF (v_Reference IS NOT NULL AND v_DocSubTypeSO = 'WP') THEN
v_ResultStr := 'InsertShipmentReference';
AD_Sequence_Next('C_InvoiceLine', o.C_Order_ID, v_NextNo);
INSERT INTO C_InvoiceLine
(C_InvoiceLine_ID,
AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
C_Invoice_ID, C_OrderLine_ID, M_InOutLine_ID,
Line, Description,
M_Product_ID, QtyInvoiced, PriceList, PriceActual, PriceLimit, LineNetAmt,
C_Charge_ID, ChargeAmt, C_UOM_ID, C_Tax_ID)
VALUES
(v_NextNo,
o.AD_Client_ID, o.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,
p_Invoice_ID, null, null,
v_LineNo, v_Reference,
null, 0, 0, 0, 0, 0,
null, 0, null, null);
END IF;
/**
* Create Lines
*/
v_ResultStr := 'InsertLines InvRule=' || o.InvoiceRule;
-- (I)mmediate -- Invoice the full/remaining order
IF (o.InvoiceRule = 'I') THEN
/**
* Create Invoice Lines from Order Lines -------------------------
*/
LOOP
v_Qty := ptr_ol.QtyOrdered - ptr_ol.QtyInvoiced;
-- Don't copy zero product lines
IF (v_Qty = 0 AND ptr_ol.M_Product_ID IS NOT NULL) THEN
DBMS_OUTPUT.PUT_LINE('- Skip 0 Qty line -');
GOTO Next_O_Line;
END IF;
--
AD_Sequence_Next('C_InvoiceLine', o.C_Order_ID, v_NextNo);
v_LineNo := v_LineNo + 10;
DBMS_OUTPUT.PUT_LINE(' Line ' || ptr_ol.Line || ' Qty=' || v_Qty);
v_ResultStr := 'CreateInvoiceLine from Order';
INSERT INTO C_InvoiceLine
(C_InvoiceLine_ID,
AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
C_Invoice_ID, C_OrderLine_ID, M_InOutLine_ID,
Line, Description,
M_Product_ID, QtyInvoiced, PriceList, PriceActual,
PriceLimit, LineNetAmt,
C_Charge_ID, ChargeAmt, C_UOM_ID, C_Tax_ID)
VALUES
(v_NextNo,
ptr_ol.AD_Client_ID, ptr_ol.AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,
p_Invoice_ID, ptr_ol.C_OrderLine_ID, null,
v_LineNo, ptr_ol.Description,
ptr_ol.M_Product_ID, v_Qty, ptr_ol.PriceList, ptr_ol.PriceActual,
ptr_ol.PriceLimit, v_Qty*ptr_ol.PriceActual,
ptr_ol.C_Charge_ID, ptr_ol.ChargeAmt, ptr_ol.C_UOM_ID, ptr_ol.C_Tax_ID);
--
UPDATE C_OrderLine
SET QtyInvoiced = QtyInvoiced + v_Qty,
DateInvoiced = v_DateInvoiced,
Updated = SysDate
WHERE CURRENT OF Cur_OrderLine;
-- Tag Shipments as invoiced
UPDATE M_InOutLine
SET IsInvoiced = 'Y'
WHERE C_OrderLine_ID = ptr_ol.C_OrderLine_ID AND MovementQty = v_Qty;
<<Next_O_Line>>
v_ResultStr := 'Fetching_OrderLine(*)';
FETCH Cur_OrderLine INTO ptr_ol;
EXIT WHEN Cur_OrderLine%NOTFOUND;
END LOOP; -- Invoice Line from Order Lines
-- After (D)elivery -- Check M_InOut for invoice quantity
ELSIF (o.InvoiceRule = 'D') THEN
/**
* Create Invoice Lines from Shipment Lines
*/
LOOP
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -