I have bene trying to complete this task for about 2 days now and i need help. I want to show my function in tabular format. Any code samples would be very much appreciated.
The problem is i want a pass in a parameter into an oracle function such as '+' or a '-' depending on these values i can calculate fields.
if '+' then (SUM(A.COST_MSO)) + (SUM(A.COST_MSO)) * B.VARIANCE_PER/100; if '-' then (SUM(A.COST_MSO)) + (SUM(A.COST_MSO)) * B.VARIANCE_PER/100;
I am using a cursor where i have a select state that joins to tables (table a and table b)
for example (i have written this if there is a easier way to do this any help would be appreciated) CREATE OR REPLACE FUNCTION itp_v (p_spccode IN VARCHAR2,p_dealership IN VARCHAR2,p_manufacturer IN VARCHAR2,p_varaiance IN VARCHAR2) RETURN varaiant IS TYPE ref0 IS REF CURSOR; cur0 ref0; v_value number; out_rec varaiant_values := varaiant_values(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
OPEN cur0 FOR 'SELECT replace(replace(A.part_number,'-',''),'/','') as PartNumber, A.DEALERSHIP, A.SOURCE, A.RETAIL_PRICE, '|| 'A.QTY_ON_HAND, B.SOURCE, B.VARIANCE_SIGN, A.AVERAGE_COST, a.COST_MSO, A.MANUFACTURER, '|| 'B.BASE_PRICE, B.VARIANCE_PER, A.PART_DESCRIPTION, 0 as VARIANCE_CALC '|| 'FROM RWG_STOCK A, RWG_PARTS_SPC B '|| 'WHERE A.SOURCE=B.SOURCE '|| 'AND B.SPC_CODE = :1 '|| 'AND A.DEALERSHIP=B.DEALERSHIP '|| 'AND A.DEALERSHIP= :2 '|| 'AND B.MANUFACTURER IN ( :3 ) '|| 'AND B.MANUFACTURER=A.MANUFACTURER '|| 'AND B.VARIANCE_SIGN = :4 '|| 'AND ( ( ( A.QTY_ON_HAND NOT IN (( 0 )) '|| 'OR A.QTY_BACK_ORDER NOT IN (( 0 )) '|| 'OR A.QTY_ON_ORDER NOT IN (( 0 )) ) ) ) '|| 'GROUP BY '|| 'a.part_number, '|| 'A.DEALERSHIP, '|| 'A.RETAIL_PRICE, '|| 'A.QTY_ON_HAND, '|| 'A.SOURCE, '|| 'B.SOURCE, '|| 'B.VARIANCE_SIGN, '|| 'A.AVERAGE_COST, '|| 'A.COST_MSO, '|| 'A.MANUFACTURER, '|| 'B.BASE_PRICE, '|| 'B.VARIANCE_PER, '|| 'A.PART_DESCRIPTION ' USING p_spccode,p_dealership, p_manufacturer, p_varaiance;
LOOP FETCH cur0 INTO out_rec.PARTNUMBER, out_rec.DEALERSHIP out_rec.SOURCE, out_rec.RETAIL_PRICE out_rec.QTY_ON_DEMAND, out_rec.SOURCE out_rec.VARIANCE_SIGN, out_rec.AVERAGE_COST out_rec.COST_MSO, out_rec.MANUFACTURER out_rec.BASE_PRICE, out_rec.VARIANCE_PER out_rec.PART_DESCRIPTION, out_rec.v_value; EXIT WHEN cur0%NOTFOUND if(p_varaiance == '+') THEN v_value := (SUM(out_rec.COST_MSO)) - (SUM(out_rec.COST_MSO)) * out_rec.VARIANCE_PER/100;
RETURN; END itp_v;
asked Feb 24, 2010 at 07:45 AM in Default
If you could provide some of the following information it would help us help you.
SELECT replace(replace(A.part_number,' - ',''),' /','') as PartNumber, A.DEALERSHIP, A.SOURCE, A.RETAIL_PRICE, A.QTY_ON_HAND, B.SOURCE, B.VARIANCE_SIGN, A.AVERAGE_COST, a.COST_MSO, A.MANUFACTURER, B.BASE_PRICE, B.VARIANCE_PER, A.PART_DESCRIPTION, 0 as VARIANCE_CALC FROM RWG_STOCK A, RWG_PARTS_SPC B WHERE A.SOURCE=B.SOURCE AND B.SPC_CODE = :spccode AND A.DEALERSHIP = B.DEALERSHIP AND A.DEALERSHIP = :dealership AND B.MANUFACTURER = :manufacturer AND B.MANUFACTURER=A.MANUFACTURER AND B.VARIANCE_SIGN = :varaiance AND ( A.QTY_ON_HAND 0 OR A.QTY_BACK_ORDER 0 OR A.QTY_ON_ORDER 0 );
answered Feb 24, 2010 at 03:42 PM