📄 c_commission_process.sql
字号:
CREATE OR REPLACE PROCEDURE C_Commission_Process
(
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+CRM
* Copyright (C) 1999-2001 Jorg Janke, ComPiere, Inc. All Rights Reserved.
*************************************************************************
* $Id: C_Commission_Process.sql,v 1.3 2002/10/23 03:16:57 jjanke Exp $
***
* Title: Process Commission
* Description:
* - Determine Date range
* - Calculate quantities + amounts
************************************************************************/
AS
-- Logistice
v_ResultStr VARCHAR2(2000);
v_Message VARCHAR2(2000);
v_Record_ID NUMBER;
-- Parameter
CURSOR Cur_Parameter (pp_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=pp_PInstance
AND i.AD_PInstance_ID=p.AD_PInstance_ID(+)
ORDER BY p.SeqNo;
-- Parameter Variables
p_StartDate DATE;
--
v_AD_Client_ID NUMBER;
v_AD_Org_ID NUMBER;
v_Name VARCHAR2(60);
v_Currency VARCHAR2(10);
v_FrequencyType CHAR(1);
v_DocBasisType CHAR(1);
v_ListDetails CHAR(1);
v_SalesRep_ID NUMBER(10);
--
v_StartDate DATE;
v_EndDate DATE;
v_C_CommissionRun_ID NUMBER(10);
v_NextNo NUMBER(10);
v_DocumentNo VARCHAR(40);
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;
IF (p.ParameterName = 'StartDate') THEN
p_StartDate := p.P_Date;
DBMS_OUTPUT.PUT_LINE(' StartDate=' || p_StartDate);
ELSE
DBMS_OUTPUT.PUT_LINE('*** Unknown Parameter=' || p.ParameterName);
END IF;
END LOOP; -- Get Parameter
DBMS_OUTPUT.PUT_LINE(' Record_ID=' || v_Record_ID);
/**
* Create Header + Determine Date Range
*/
v_ResultStr := 'ReadingRecord';
SELECT AD_Client_ID, AD_Org_ID, Name, FrequencyType, DocBasisType, ListDetails, C_BPartner_ID
INTO v_AD_Client_ID, v_AD_Org_ID, v_Name, v_FrequencyType, v_DocBasisType, v_ListDetails, v_SalesRep_ID
FROM C_Commission
WHERE C_Commission_ID = v_Record_ID;
--
SELECT ISO_Code
INTO v_Currency
FROM C_Currency cur, C_Commission com
WHERE cur.C_Currency_ID=com.C_Currency_ID
AND com.C_Commission_ID = v_Record_ID;
--
v_ResultStr := 'CalculatingHeader';
IF (p_StartDate IS NULL) THEN
p_StartDate := SysDate;
END IF;
--
IF (v_FrequencyType = 'Q') THEN -- Quarter
v_StartDate := TRUNC(p_StartDate, 'Q');
v_EndDate := TRUNC(p_StartDate+92, 'Q')-1;
ELSIF (v_FrequencyType = 'W') THEN -- Weekly
v_StartDate := TRUNC(p_StartDate, 'DAY');
v_EndDate := TRUNC(p_StartDate+7, 'DAY')-1;
ELSE -- Month
v_StartDate := TRUNC(p_StartDate, 'MM');
v_EndDate := TRUNC(p_StartDate+31, 'MM')-1;
END IF;
-- Name 01-Jan-2000 - 31-Jan-2001 - USD
v_Name := v_Name || ' ' || TO_CHAR(v_StartDate) || ' - ' || TO_CHAR(v_EndDate)
|| ' - ' || v_Currency;
AD_Sequence_Next ('C_CommissionRun', v_AD_Client_ID, v_C_CommissionRun_ID);
AD_Sequence_Doc ('DocumentNo_C_CommissionRun', v_AD_Client_ID, v_DocumentNo);
DBMS_OUTPUT.PUT_LINE('Create: ' || v_DocumentNo || ' - ' || v_Name);
v_ResultStr := 'InsertingHeader';
INSERT INTO C_CommissionRun (C_CommissionRun_ID, C_Commission_ID,
AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
DocumentNo, Description,
StartDate, GrandTotal, Processing, Processed)
VALUES (v_C_CommissionRun_ID, v_Record_ID,
v_AD_Client_ID, v_AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,
v_DocumentNo, v_Name,
v_StartDate, 0, 'N', 'N');
--
v_ResultStr := 'Update Record';
UPDATE C_Commission
SET DateLastRun = SysDate --v_StartDate
WHERE C_Commission_ID = v_Record_ID;
/**
* Calculate Lines
*/
DECLARE
CURSOR CUR_CLine IS
SELECT *
FROM C_CommissionLine
WHERE C_Commission_ID = v_Record_ID;
TYPE TYPE_Ref IS REF CURSOR;
v_rc TYPE_Ref;
--
v_Cmd VARCHAR2(2000);
v_C_CommissionAmt_ID NUMBER(10);
--
v_C_Currency_ID C_CommissionDetail.C_Currency_ID%TYPE;
v_Amt C_CommissionDetail.ActualAmt%TYPE;
v_Qty C_CommissionDetail.ActualQty%TYPE;
v_C_OrderLine_ID NUMBER(10);
v_C_InvoiceLine_ID NUMBER(10);
v_Reference C_CommissionDetail.Reference%TYPE;
v_Info C_CommissionDetail.Info%TYPE;
--
BEGIN
FOR line IN CUR_CLine LOOP
v_ResultStr := 'InsertingAmt';
-- For every Commission Line create empty Amt line (updated by Detail)
AD_Sequence_Next ('C_CommissionAmt', v_AD_Client_ID, v_C_CommissionAmt_ID);
INSERT INTO C_CommissionAmt (C_CommissionAmt_ID,
C_CommissionRun_ID, C_CommissionLine_ID,
AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
ConvertedAmt, ActualQty, CommissionAmt)
VALUES (v_C_CommissionAmt_ID, v_C_CommissionRun_ID, line.C_CommissionLine_ID,
v_AD_Client_ID, v_AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,
0, 0, 0); -- Calculation done by Trigger
-- DBMS_OUTPUT.PUT_LINE('- ' || line.Line);
v_ResultStr := 'AssemblingDynSQL';
-- Receipt Basis
IF (v_DocBasisType = 'R') THEN
IF (v_ListDetails = 'Y') THEN
v_Cmd := 'SELECT h.C_Currency_ID, l.LineNetAmt, l.QtyInvoiced, '
|| 'NULL, l.C_InvoiceLine_ID, p.Value || '' '' || h.DocumentNo, NVL(prd.Name,l.Description) '
|| 'FROM C_Payment p, C_Invoice h, C_InvoiceLine l, M_Product prd '
|| 'WHERE p.C_Invoice_ID = h.C_Invoice_ID'
|| ' AND p.DocStatus IN (''CL'',''CO'')'
|| ' AND h.C_Invoice_ID = l.C_Invoice_ID'
|| ' AND h.AD_Client_ID = :1'
|| ' AND p.DateTrx BETWEEN :2 AND :3'
|| ' AND l.M_Product_ID = prd.M_Product_ID(+)';
ELSE
v_Cmd := 'SELECT h.C_Currency_ID, SUM(l.LineNetAmt) AS Amt, SUM(l.QtyInvoiced) AS Qty, '
|| 'NULL, NULL, NULL, NULL '
|| 'FROM C_Payment p, C_Invoice h, C_InvoiceLine l '
|| 'WHERE p.C_Invoice_ID = h.C_Invoice_ID'
|| ' AND p.DocStatus IN (''CL'',''CO'')'
|| ' AND h.C_Invoice_ID = l.C_Invoice_ID'
|| ' AND h.AD_Client_ID = :1'
|| ' AND p.DateTrx BETWEEN :2 AND :3';
END IF;
-- Invoice Basis
ELSIF (v_DocBasisType = 'I') THEN
IF (v_ListDetails = 'Y') THEN
v_Cmd := 'SELECT h.C_Currency_ID, l.LineNetAmt, l.QtyInvoiced, '
|| 'NULL, l.C_InvoiceLine_ID, h.DocumentNo, NVL(prd.Name,l.Description) '
|| 'FROM C_Invoice h, C_InvoiceLine l, M_Product prd '
|| 'WHERE h.C_Invoice_ID = l.C_Invoice_ID'
|| ' AND h.DocStatus IN (''CL'',''CO'')'
|| ' AND h.AD_Client_ID = :1'
|| ' AND h.DateInvoiced BETWEEN :2 AND :3'
|| ' AND l.M_Product_ID = prd.M_Product_ID(+)';
ELSE
v_Cmd := 'SELECT h.C_Currency_ID, SUM(l.LineNetAmt) AS Amt, SUM(l.QtyInvoiced) AS Qty, '
|| 'NULL, NULL, NULL, NULL '
|| 'FROM C_Invoice h, C_InvoiceLine l '
|| 'WHERE h.C_Invoice_ID = l.C_Invoice_ID'
|| ' AND h.DocStatus IN (''CL'',''CO'')'
|| ' AND h.AD_Client_ID = :1'
|| ' AND h.DateInvoiced BETWEEN :2 AND :3';
END IF;
-- Order Basis
ELSE
IF (v_ListDetails = 'Y') THEN
v_Cmd := 'SELECT h.C_Currency_ID, l.LineNetAmt, l.QtyOrdered, '
|| 'l.C_OrderLine_ID, NULL, h.DocumentNo, NVL(prd.Name,l.Description) '
|| 'FROM C_Order h, C_OrderLine l, M_Product prd '
|| 'WHERE h.C_Order_ID = l.C_Order_ID'
|| ' AND h.DocStatus IN (''CL'',''CO'')'
|| ' AND h.AD_Client_ID = :1'
|| ' AND h.DateOrdered BETWEEN :2 AND :3'
|| ' AND l.M_Product_ID = prd.M_Product_ID(+)';
ELSE
v_Cmd := 'SELECT h.C_Currency_ID, SUM(l.LineNetAmt) AS Amt, SUM(l.QtyOrdered) AS Qty, '
|| 'NULL, NULL, NULL, NULL '
|| 'FROM C_Order h, C_OrderLine l '
|| 'WHERE h.C_Order_ID = l.C_Order_ID'
|| ' AND h.DocStatus IN (''CL'',''CO'')'
|| ' AND h.AD_Client_ID = :1'
|| ' AND h.DateOrdered BETWEEN :2 AND :3';
END IF;
END IF;
-- CommissionOrders/Invoices
IF (line.CommissionOrders = 'Y') THEN
v_Cmd := v_Cmd || ' AND h.SalesRep_ID = (SELECT AD_User_ID FROM AD_User WHERE C_BPartner_ID=' || v_SalesRep_ID || ')';
END IF;
-- Organization
IF (line.Org_ID IS NOT NULL) THEN
v_Cmd := v_Cmd || ' AND h.AD_Org_ID=' || line.Org_ID;
END IF;
-- BPartner
IF (line.C_BPartner_ID IS NOT NULL) THEN
v_Cmd := v_Cmd || ' AND h.C_BPartner_ID=' || line.C_BPartner_ID;
END IF;
-- BPartner Group
IF (line.C_BP_Group_ID IS NOT NULL) THEN
v_Cmd := v_Cmd || ' AND h.C_BPartner_ID IN '
|| '(SELECT C_BPartner_ID FROM C_BPartner WHERE C_BP_Group_ID=' || line.C_BP_Group_ID || ')';
END IF;
-- Sales Region
IF (line.C_SalesRegion_ID IS NOT NULL) THEN
v_Cmd := v_Cmd || ' AND h.C_BPartner_Location_ID IN '
|| '(SELECT C_BPartner_Location_ID FROM C_BPartner_Location WHERE C_SalesRegion_ID=' || line.C_SalesRegion_ID || ')';
END IF;
-- Product
IF (line.M_Product_ID IS NOT NULL) THEN
v_Cmd := v_Cmd || ' AND l.M_Product_ID=' || line.M_Product_ID;
END IF;
-- Product Category
IF (line.M_Product_Category_ID IS NOT NULL) THEN
v_Cmd := v_Cmd || ' AND l.M_Product_ID IN '
|| '(SELECT M_Product_ID FROM M_Product WHERE M_Product_Category_ID=' || line.M_Product_Category_ID ||')';
END IF;
-- Grouping
IF (v_ListDetails <> 'Y') THEN
v_Cmd := v_Cmd || ' GROUP BY h.C_Currency_ID';
END IF;
--
-- DBMS_OUTPUT.PUT_LINE('- ' || line.Line || ' SQL=' || SUBSTR(v_Cmd, 1, 200));
-- DBMS_OUTPUT.PUT_LINE(SUBSTR(v_Cmd, 200,200));
-- DBMS_OUTPUT.PUT_LINE(SUBSTR(v_Cmd, 400));
--
v_ResultStr := 'OpenDynCursor';
OPEN v_rc FOR v_Cmd USING v_AD_Client_ID, v_StartDate, v_EndDate;
LOOP
v_ResultStr := 'FetchingData';
FETCH v_rc INTO v_C_Currency_ID, v_Amt, v_Qty, v_C_OrderLine_ID, v_C_InvoiceLine_ID, v_Reference, v_Info;
EXIT WHEN v_rc%NOTFOUND;
--
v_ResultStr := 'InsertingDetail';
AD_Sequence_Next ('C_CommissionDetail', v_AD_Client_ID, v_NextNo);
INSERT INTO C_CommissionDetail (C_CommissionDetail_ID, C_CommissionAmt_ID,
AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
C_Currency_ID, ActualAmt, ConvertedAmt, ActualQty,
C_OrderLine_ID, C_InvoiceLine_ID, Reference, Info)
VALUES (v_NextNo, v_C_CommissionAmt_ID,
v_AD_Client_ID, v_AD_Org_ID, 'Y', SysDate, 0, SysDate, 0,
v_C_Currency_ID, v_Amt, 0, v_Qty, -- Conversion done by Trigger
v_C_OrderLine_ID, v_C_InvoiceLine_ID, v_Reference, v_Info);
--
-- DBMS_OUTPUT.PUT_LINE(' ' || v_Reference || ' - ' || v_Amt || ' - ' || v_Qty);
END LOOP;
CLOSE v_rc;
--
END LOOP; -- For every Commission Line
END;
v_Message := '@C_CommissionRun_ID@ = ' || v_DocumentNo || ' - ' || v_Name;
<<FINISH_PROCESS>>
-- 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;
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_Commission_Process;
/
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -