📄 c_invoice_writeoff.sql
字号:
CREATE OR REPLACE PROCEDURE C_Invoice_WriteOff
(
p_PInstance_ID IN 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+CPM
* Copyright (C) 1999-2001 Jorg Janke, ComPiere, Inc. All Rights Reserved.
*************************************************************************
* $Id: C_Invoice_WriteOff.sql,v 1.4 2002/10/21 04:49:46 jjanke Exp $
***
* Title: Mass Invoice Write-off
* Description:
* Get all invoices matching the criteria
* and write them off
************************************************************************/
AS
-- Logistice
v_ResultStr VARCHAR2(2000);
v_Message VARCHAR2(2000);
v_Result NUMBER := 1; -- 0=failure
v_Record_ID NUMBER;
v_AD_User_ID NUMBER;
-- Parameter
CURSOR Cur_Parameter (pp_PInstance NUMBER) IS
SELECT i.Record_ID, i.AD_User_ID,
p.ParameterName, p.P_String, p.P_Number, p.P_Date, p.P_Date_To
FROM AD_PInstance i, AD_PInstance_Para p
WHERE i.AD_PInstance_ID=pp_PInstance
AND i.AD_PInstance_ID=p.AD_PInstance_ID(+)
ORDER BY p.SeqNo;
-- Parameter Variables
p_C_BPartner_ID NUMBER(10);
p_C_Invoice_ID NUMBER(10);
p_C_BankAccount_ID NUMBER(10);
p_MaxInvWriteOffAmt NUMBER := 0;
p_DateFrom DATE := SysDate;
p_DateTo DATE := SysDate;
p_DateAcct DATE := SysDate;
p_IsSimulation VARCHAR(1) := 'Y';
-- The Main Query
CURSOR Cur_Invoices IS
SELECT AD_Client_ID, AD_Org_ID,
C_Invoice_ID, DocumentNo, DateInvoiced,
C_Order_ID, C_Currency_ID,
C_Invoice_Open (C_Invoice_ID) AS Amount
FROM C_Invoice_v
WHERE C_BPartner_ID = p_C_BPartner_ID
-- open invoice amounts (not null)
AND ABS(C_Invoice_Open (C_Invoice_ID)) BETWEEN 0.01 AND ABS(p_MaxInvWriteOffAmt)
-- if invoice identified - ignore date
AND (p_C_Invoice_ID IS NOT NULL OR TRUNC(DateInvoiced) BETWEEN p_DateFrom AND p_DateTo)
-- specific invoice or all
AND (p_C_Invoice_ID IS NULL OR C_Invoice_ID = p_C_Invoice_ID)
ORDER BY C_Invoice_ID;
--
v_C_DocType_ID NUMBER(10);
v_NextNo NUMBER(10) := 0;
v_C_Payment_ID NUMBER(10) := NULL;
v_PaymentDocumentNo VARCHAR2(60) := '** WriteOff **';
--
v_no NUMBER(10) := 0;
v_total NUMBER := 0;
BEGIN
-- 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;
v_AD_User_ID := p.AD_User_ID;
IF (p.ParameterName = 'C_BPartner_ID') THEN
p_C_BPartner_ID := p.P_Number;
DBMS_OUTPUT.PUT_LINE(' C_BPartner_ID=' || p_C_BPartner_ID);
ELSIF (p.ParameterName = 'C_Invoice_ID') THEN
p_C_Invoice_ID := p.P_Number;
DBMS_OUTPUT.PUT_LINE(' C_Invoice_ID=' || p_C_Invoice_ID);
ELSIF (p.ParameterName = 'C_BankAccount_ID') THEN
p_C_BankAccount_ID := p.P_Number;
DBMS_OUTPUT.PUT_LINE(' C_BankAccount_ID=' || p_C_BankAccount_ID);
ELSIF (p.ParameterName = 'MaxInvWriteOffAmt') THEN
p_MaxInvWriteOffAmt := p.P_Number;
DBMS_OUTPUT.PUT_LINE(' MaxInvWriteOffAmt=' || p_MaxInvWriteOffAmt);
ELSIF (p.ParameterName = 'DateInvoiced') THEN
p_DateFrom := p.P_Date;
DBMS_OUTPUT.PUT_LINE(' DateFrom=' || p_DateFrom);
p_DateTo := p.P_Date_To;
DBMS_OUTPUT.PUT_LINE(' DateFrom=' || p_DateTo);
ELSIF (p.ParameterName = 'DateAcct') THEN
p_DateAcct := p.P_Date;
DBMS_OUTPUT.PUT_LINE(' DateAcct=' || p_DateAcct);
ELSIF (p.ParameterName = 'IsSimulation') THEN
p_IsSimulation := p.P_String;
DBMS_OUTPUT.PUT_LINE(' IsSimulation=' || p_IsSimulation);
ELSE
DBMS_OUTPUT.PUT_LINE('*** Unknown Parameter=' || p.ParameterName);
END IF;
END LOOP; -- Get Parameter
DBMS_OUTPUT.PUT_LINE(' Record_ID=' || v_Record_ID);
-- For all Invoices
FOR i IN Cur_Invoices LOOP
-- Create C_Payment
IF (v_C_Payment_ID IS NULL) THEN
IF (p_IsSimulation = 'N') THEN
v_ResultStr := 'GetDocType';
SELECT C_DocType_ID
INTO v_C_DocType_ID
FROM C_DocType
WHERE DocBaseType = 'ARR'
AND AD_Client_ID=i.AD_Client_ID
AND ROWNUM=1;
--
v_ResultStr := 'CreatePayment';
AD_Sequence_Next ('C_Payment', i.AD_Client_ID, v_C_Payment_ID);
INSERT INTO C_Payment
(C_Payment_ID, AD_Client_ID, AD_Org_ID,
IsActive, Created, CreatedBy, Updated, UpdatedBy,
DateTrx, TrxType, TenderType, IsReceipt,
C_DocType_ID, DocStatus, DocAction, IsAllocated,
DocumentNo,
C_BankAccount_ID, C_BPartner_ID,
C_Currency_ID, PayAmt, DiscountAmt, WriteOffAmt,
Processed, Posted)
VALUES
(v_C_Payment_ID, i.AD_Client_ID, i.AD_Org_ID,
'Y', SysDate, v_AD_User_ID, SysDate, v_AD_User_ID,
p_DateAcct, 'S', 'K', 'Y', -- Sales,Check
v_C_DocType_ID, 'CO', '--', 'Y',
v_PaymentDocumentNo,
p_C_BankAccount_ID, p_C_BPartner_ID,
i.C_Currency_ID, 0, 0, 0,
'Y', 'N');
DBMS_OUTPUT.PUT_LINE('Payment_ID=' || v_C_Payment_ID
|| ', DocType_ID=' || v_C_DocType_ID );
-- Log info
INSERT INTO AD_PInstance_Log (AD_PInstance_ID, Log_ID,
P_ID, P_Date, p_Number, P_Msg)
VALUES (p_PInstance_ID, AD_PInstance_Seq.NextVal,
v_C_Payment_ID, p_DateAcct, 0, '@Created@ @C_Payment_ID@: ' || v_PaymentDocumentNo);
ELSE
v_C_Payment_ID := 0;
INSERT INTO AD_PInstance_Log (AD_PInstance_ID, Log_ID,
P_Date, p_Number, P_Msg)
VALUES (p_PInstance_ID, AD_PInstance_Seq.NextVal,
p_DateAcct, 0, '@IsSimulation@ @C_Payment_ID@: ' || v_PaymentDocumentNo);
END IF;
END IF;
--
DBMS_OUTPUT.PUT_LINE('Invoice ID=' || i.C_Invoice_ID
|| ', Amt=' || i.Amount);
IF (p_IsSimulation = 'N') THEN
-- Create Allocation
v_ResultStr := 'CreateAllocation';
AD_Sequence_Next ('C_Allocation', i.AD_Client_ID, v_NextNo);
INSERT INTO C_Allocation
(C_Allocation_ID, AD_Client_ID, AD_Org_ID,
IsActive, Created, CreatedBy, Updated, UpdatedBy,
AllocationNo, DateTrx, IsManual,
C_BPartner_ID, C_Currency_ID, C_Invoice_ID, C_Order_ID,
C_Payment_ID, C_CashLine_ID,
Amount, DiscountAmt, WriteOffAmt,
Processing, Processed, Posted)
VALUES
(v_NextNo, i.AD_Client_ID, i.AD_Org_ID,
'Y', SysDate, v_AD_User_ID, SysDate, v_AD_User_ID,
C_Allocation_Seq.NextVal, p_DateAcct, 'N',
p_C_BPartner_ID, i.C_Currency_ID, i.C_Invoice_ID, i.C_Order_ID,
v_C_Payment_ID, NULL,
0, 0, i.Amount,
'N', 'Y', 'N');
-- Update Invoice / Order
v_ResultStr := 'UpdateDocuments';
UPDATE C_Invoice
SET IsPaid = DECODE(C_Invoice_Paid(C_Invoice_ID, C_Currency_ID, 1),
GrandTotal, 'Y','N'), -- hardcoded multiplier
C_Payment_ID = v_C_Payment_ID
WHERE C_Invoice_ID = i.C_Invoice_ID;
IF (i.C_Order_ID IS NOT NULL) THEN
UPDATE C_Order o
SET C_Payment_ID = v_C_Payment_ID
WHERE C_Order_ID = i.C_Order_ID;
END IF;
-- Log info
INSERT INTO AD_PInstance_Log (AD_PInstance_ID, Log_ID,
P_ID, P_Date, P_Number, P_Msg)
VALUES (p_PInstance_ID, AD_PInstance_Seq.NextVal,
v_NextNo, i.DateInvoiced, i.Amount,
'@Created@ @C_Allocation_ID@ ' || i.DocumentNo);
ELSE
INSERT INTO AD_PInstance_Log (AD_PInstance_ID, Log_ID,
P_ID, P_Date, P_Number, P_Msg)
VALUES (p_PInstance_ID, AD_PInstance_Seq.NextVal,
v_NextNo, i.DateInvoiced, i.Amount,
'@IsSimulation@ @C_Allocation_ID@ ' || i.DocumentNo);
END IF;
--
v_total := v_total + i.Amount;
v_no := v_no + 1;
END LOOP; -- for all invoices
v_Message := '@C_Invoice_ID@ @Quantity@=' || v_no || ', @Totals@=' || v_total;
<<FINISH_PROCESS>>
-- Update AD_PInstance
DBMS_OUTPUT.PUT_LINE('Updating PInstance - Finished ' || v_Message);
UPDATE AD_PInstance
SET Updated = SysDate,
IsProcessing = 'N',
Result = v_Result, -- 1=success
ErrorMsg = v_Message
WHERE AD_PInstance_ID=p_PInstance_ID;
COMMIT;
RETURN;
EXCEPTION
WHEN OTHERS THEN
v_ResultStr := v_ResultStr || ': ' || SQLERRM || ' - ' || v_Message;
DBMS_OUTPUT.PUT_LINE(v_ResultStr);
ROLLBACK;
UPDATE AD_PInstance
SET Updated = SysDate,
IsProcessing = 'N',
Result = 0, -- failure
ErrorMsg = v_ResultStr
WHERE AD_PInstance_ID=p_PInstance_ID;
COMMIT;
RETURN;
END C_Invoice_WriteOff;
/⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -