|
Hi 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); BEGIN 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; CLOSE cur0; RETURN; END itp_v; Many Thanks
(comments are locked)
|
|
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 );
(comments are locked)
|
When you post your question, there is a little button with 1's and 0's on it that is for "code." If you highlight a section of text and click it, it preserves your formatting. It would help greatly if you would edit your question and format your code to be more readable.