package template

CREATE OR REPLACE PACKAGE CREATE_PKG AS
PROCEDURE PURCHASE_ORDER(P_PO_HEADERS_OBJ IN OUT WEB_PO_HEADERS_ALL_OBJ,
                         P_PO_LINES_TBL   IN OUT WEB_PO_LINES_ALL_TBL,
                         OUT_PUT          OUT VARCHAR2,
                         RETCODE          OUT VARCHAR2);
END CREATE_PKG;
/

CREATE OR REPLACE PACKAGE BODY CREATE_PKG AS
PROCEDURE PURCHASE_ORDER(P_PO_HEADERS_OBJ IN OUT WEB_PO_HEADERS_ALL_OBJ,
                         P_PO_LINES_TBL   IN OUT WEB_PO_LINES_ALL_TBL,
                         OUT_PUT          OUT VARCHAR2,
                         RETCODE          OUT VARCHAR2) IS

V_PO_NUMBER NUMBER;
BEGIN

BEGIN
SELECT NEXT_PO INTO V_PO_NUMBER FROM PO_VENDORS_ALL;
P_PO_HEADERS_OBJ.PO_NUMBER := V_PO_NUMBER;
EXCEPTION WHEN no_data_found THEN
OUT_PUT:='Vendor Not Defined';
RETCODE:='-1';
RETURN;
END;

BEGIN
SELECT PO_HEADERS_ALL_S1.NEXTVAL
INTO P_PO_HEADERS_OBJ.PO_HEADER_ID
FROM DUAL;
END;
INSERT INTO PO_HEADERS_ALL
(PO_HEADER_ID,
 PO_STATUS,
 PO_NUMBER,
 CREATED_BY,
 CREATION_DATE,
 EXPECTATION_DATE,
 VENDOR_ID,
 DELIVER_TO_LOCATION)
VALUES
(P_PO_HEADERS_OBJ.PO_HEADER_ID,
 P_PO_HEADERS_OBJ.PO_STATUS,
 P_PO_HEADERS_OBJ.PO_NUMBER,
 P_PO_HEADERS_OBJ.CREATED_BY,
 P_PO_HEADERS_OBJ.CREATION_DATE,
 P_PO_HEADERS_OBJ.EXPECTATION_DATE,
 P_PO_HEADERS_OBJ.VENDOR_ID,
 P_PO_HEADERS_OBJ.DELIVER_TO_LOCATION);
COMMIT;

FOR INDX IN NVL(P_PO_LINES_TBL.FIRST, 0) .. NVL(P_PO_LINES_TBL.LAST, -1)
LOOP
P_PO_LINES_TBL(INDX).PO_LINE_NUMBER := INDX;
P_PO_LINES_TBL(INDX).PO_HEADER_ID := P_PO_HEADERS_OBJ.PO_HEADER_ID;
INSERT INTO PO_LINES_ALL
(PO_HEADER_ID,
 PO_LINE_ID,
 PO_LINE_NUMBER,
 ITEM_ID,
 ITEM_NUM,
 QTY,
 UOM,
 UNIT_PRICE)
VALUES
(P_PO_LINES_TBL(INDX).PO_HEADER_ID,
 P_PO_LINES_TBL(INDX).PO_LINE_ID,
 P_PO_LINES_TBL(INDX).PO_LINE_NUMBER,
 P_PO_LINES_TBL(INDX).ITEM_ID,
 P_PO_LINES_TBL(INDX).ITEM_NUM,
 P_PO_LINES_TBL(INDX).QTY,
 P_PO_LINES_TBL(INDX).UOM,
 P_PO_LINES_TBL(INDX).UNIT_PRICE);
END LOOP;

BEGIN                                       
V_PO_NUMBER:=V_PO_NUMBER+1;
UPDATE PO_VENDORS_ALL SET NEXT_PO=V_PO_NUMBER WHERE PO_VENDOR_ID=P_PO_HEADERS_OBJ.VENDOR_ID;
END;  

COMMIT;
RETCODE := '0';
OUT_PUT := 'PO#' || P_PO_HEADERS_OBJ.PO_NUMBER || ' created';

END;
END CREATE_PKG;
/