Function to calculate Header fill rate in OM

CREATE

OR REPLACE FUNCTION APPS.xx_mother_rep(p_order_number varchar2)


RETURN NUMBER

IS




/*==================================================================================

||

|| PL/SQL Code

||

|| Code Type: function Body

||

|| Filename: APPS.xx_mother_rep

||

|| Author: Swapnil Waydande

||

|| Purpose: Caluculate Header Fill Rate For Mother Report

||

|| Parameters:

|| p_order_number in VARCHAR2

||

|| Version History:

||

|| Name Version Reason for Change Date

|| ------------ --------- --------------------- --------------------

|| Swapnil Waydande 1.0 Creation 12-Dec-2014

==================================================================================*/




l4
NUMBER;

l2
NUMBER;

l3
NUMBER;

l5
VARCHAR2 (20);


CURSOR cur


IS


SELECT SUM (a.reserved_quantity) l2, SUM (a.ordered_quantity) l3--, --a.order_number l5


FROM kvont_compr_so_rpt_line a


WHERE order_number = p_order_number--10043691


GROUP BY a.order_number;

BEGIN




FOR i IN cur


LOOP


if i.l2 != 0


THEN

l4
:=


TO_CHAR ((((i.l2) / (i.l3)


)

*
100


),


'9999999.99'


);



DBMS_OUTPUT.put_line
('fillrate'||i.l2);


ELSE

l4
:= (i.l2) * 100;


END if;


-- DBMS_OUTPUT.put_line ('order_number' || i.l5);

DBMS_OUTPUT.put_line
(l4);


END LOOP;


RETURN l4;

END;

/