Sql Programming: How to Create Aga Ana

CREATE OR REPLACE PACKAGE BODY STOCK_AGE_ANA_SP_RPI AS

PROCEDURE Report_Overview (

report_attr_ IN VARCHAR2,

parameter_attr_ IN VARCHAR2 )

IS

result_key_ NUMBER;

row_no_ NUMBER := 1;

part_no_ VARCHAR2(25);

input_part_no_ VARCHAR2(25);

part_no__ VARCHAR2(25);

prodline_ VARCHAR2(25);

site_ VARCHAR2(20);

whouse_ VARCHAR2(25);

bay_ VARCHAR2(25);

date_ DATE;

tot1 NUMBER:=0;

tot2 NUMBER:=0;

tot3 NUMBER:=0;

tot4 NUMBER:=0;

tot5 NUMBER:=0;

tot6 NUMBER:=0;

temp1 NUMBER:=0;

temp2 NUMBER:=0;

temp3 NUMBER:=0;

temp4 NUMBER:=0;

temp5 NUMBER:=0;

temp6 NUMBER:=0;

val1 NUMBER:=0;

val2 NUMBER:=0;

val3 NUMBER:=0;

val4 NUMBER:=0;

val5 NUMBER:=0;

val6 NUMBER:=0;

val NUMBER:=0;

tot_minus NUMBER:=0;

tot_minus1 NUMBER:=0;

tot_minus2 NUMBER:=0;

cost NUMBER:=0;

from_date_ VARCHAR2(12);

to_date_ VARCHAR2(12);

date_1 VARCHAR2(50);

date_2 VARCHAR2(12);

date_3 VARCHAR2(12);

date_4 VARCHAR2(12);

date_5 VARCHAR2(12);

date_6 VARCHAR2(12);

dated_ VARCHAR2(12);

tot_qty_hand NUMBER := 0;

tot_qty NUMBER := 0;

state_ NUMBER := 0;

no_days1_ NUMBER := 0; --(+) 080829 HeBaLK DIS-054 (Start)

no_days2_ NUMBER:=0;

no_days3_ NUMBER:=0;

no_days4_ NUMBER:=0;

no_days5_ NUMBER:=0;

day1_ NUMBER:=0;

day2_ NUMBER:=0;

day3_ NUMBER:=0;

day4_ NUMBER:=0;

day5_ NUMBER:=0;

day6_ NUMBER:=0; --(+) 080829 HeBaLK DIS-054 (End)

CURSOR get_parts (part_no_ IN VARCHAR2,site_ IN VARCHAR2) IS

SELECT SUM(loc.qty_onhand) qty,

loc.part_no,

loc.contract ,

Inventory_Part_API.Get_Description(loc.contract,loc.part_no) description,

Inventory_Part_Cost_API.Get_Part_Cost(loc.contract,loc.part_no) Cost

FROM Inventory_Part_In_Stock_Tab loc,

Inventory_Part ip

WHERE ip.second_commodity LIKE prodline_

AND loc.part_no LIKE part_no_

AND loc.contract LIKE site_

AND loc.location_no NOT LIKE '%INSPECT%'

AND ip.part_no = loc.part_no

AND ip.contract = loc.contract

GROUP BY loc.part_no,

Inventory_Part_API.Get_Description(loc.contract,loc.part_no),

Inventory_Part_Cost_API.Get_Part_Cost(loc.contract,loc.part_no),

loc.contract

ORDER BY loc.part_no;

CURSOR get_minus_with_bay(part_no_ IN VARCHAR2, site_ IN VARCHAR2, from_date_ IN VARCHAR2) IS

--(+/-) 140915 Miwelk R_G1426959-1 (START)

--(+/-) 140306 Thualk R_G1358412-1 (START)

SELECT t.quantity , t.transaction_id

--SELECT SUM(t.quantity) quantity --,t.transaction_id

--(+/-) 140306 Thualk R_G1358412-1 (END)

--(+/-) 140915 Miwelk R_G1426959-1 (END)

FROM Inventory_Transaction_Hist2 t

WHERE part_no = part_no_

AND contract = site_

AND direction = '-'

AND location_no NOT LIKE '%INSPECT%'

AND date_created <= (TO_DATE(from_date_,'DD/MM/YYYY') + ( 1 - 1/ ( 60*60*24 )));