ERP 2.50:QA test plan 2.50/Modularity/Report new payment functions(Oracle)
FUNCTION PAYREPNEW_IMP_SALES_TOT
create or replace FUNCTION PAYREPNEW_AMT_SALES_TOT(p_clientID IN VARCHAR2, p_orgID IN VARCHAR2, p_productID IN VARCHAR2, p_partner IN VARCHAR2, p_year IN NUMBER, p_month IN NUMBER,salesrep IN VARCHAR2) RETURN NUMBER
v_amount NUMBER(10,2) ;
BEGIN
SELECT COALESCE(SUM(C_CURRENCY_CONVERT(COALESCE(C_ORDERLINE.LINENETAMT,0), C_ORDER.c_currency_id, '102', C_ORDER.dateordered, null, c_order.AD_CLIENT_ID, c_order.AD_ORG_ID)),0) into v_amount
FROM C_ORDER, C_ORDERLINE
WHERE C_ORDER.C_ORDER_ID=C_ORDERLINE.C_ORDER_ID
AND C_ORDERLINE.M_PRODUCT_ID=p_productID
AND C_ORDER.AD_ORG_ID=p_orgID
AND C_ORDER.AD_CLIENT_ID=p_clientID
AND C_ORDER.ISSOTRX = 'Y'
AND C_ORDER.PROCESSED = 'Y'
AND COALESCE(C_ORDER.SALESREP_ID,'0')=salesrep
AND C_ORDER.C_BPARTNER_ID=p_partner
AND TO_CHAR(C_ORDER.DATEORDERED,'YYYY')=TO_CHAR(p_year);
RETURN v_amount;
END PAYREPNEW_AMT_SALES_TOT;
FUNCTION PAYREPNEW_AMT_SALES
create or replace FUNCTION PAYREPNEW_AMT_SALES(p_clientID IN VARCHAR2, p_orgID IN VARCHAR2, p_productID IN VARCHAR2,p_partner IN VARCHAR2, p_year IN NUMBER, p_month IN NUMBER,salesrep IN VARCHAR2) RETURN NUMBER
v_amount NUMBER(10,2) ;
BEGIN
SELECT COALESCE(SUM(C_CURRENCY_CONVERT(COALESCE(C_ORDERLINE.LINENETAMT,0), C_ORDER.c_currency_id, '102', C_ORDER.dateordered, null, c_order.AD_CLIENT_ID, c_order.AD_ORG_ID)),0) into v_amount
FROM C_ORDER, C_ORDERLINE
WHERE C_ORDER.C_ORDER_ID=C_ORDERLINE.C_ORDER_ID
AND C_ORDERLINE.M_PRODUCT_ID=p_productID
AND C_ORDER.AD_ORG_ID=p_orgID
AND C_ORDER.AD_CLIENT_ID=p_clientID
AND C_ORDER.ISSOTRX = 'Y'
AND C_ORDER.PROCESSED = 'Y'
AND COALESCE(C_ORDER.SALESREP_ID,'0')=salesrep
AND C_ORDER.C_BPARTNER_ID=p_partner
AND TO_CHAR(C_ORDER.DATEORDERED,'YYYY')=TO_CHAR(p_year)
AND TO_NUMBER(TO_CHAR(C_ORDER.DATEORDERED,'MM'))=p_month;
RETURN v_amount;
END PAYREPNEW_AMT_SALES;