CREATE OR REPLACE TYPE PIPACK."OBJFCST" 
AS OBJECT (JAN NUMBER(18,2),FEB NUMBER(18,2),MAR NUMBER(18,2),APR NUMBER(18,2),MAY NUMBER(18,2),JUN NUMBER(18,2),
JUL NUMBER(18,2),AUG NUMBER(18,2),SEP NUMBER(18,2),OCT NUMBER(18,2),NOV NUMBER(18,2), DEC NUMBER(18,2),
Q1 NUMBER(18,2), Q2 NUMBER(18,2), Q3 NUMBER(18,2),Q4 NUMBER(18,2), TOTAL NUMBER(18,2));
/

Type created.


CREATE OR REPLACE TYPE PIPACK.dept_vend_array 
AS table OF OBJDEPTVENDOR

/

Type created.

create or replace type pipack.wholesaler 
as object (dept number,
supp number,
channel varchar2(20),
basis varchar2(6),
cy_actuals objfcst,
ly_actuals objfcst,
fcst_object objfcst
)
/

Type created.

CREATE OR REPLACE TYPE PIPACK.wholesaler_array 
as table of pipack.wholesaler 

/

Type created.


1 create or replace PROCEDURE VPF_Load_Total_Fcst_wholesaler (i_dept_ven_list IN dept_vend_array
2 i_fcst_yr in number,
3 o_wholesaler OUT wholesaler_array,
4 o_comments out varchar2,
5 o_err_code out number,
6 o_err_msg out varchar2
7 ) is
8 c_chnl_cd department_vendor_list.chnl_cd%type;
9 l_vnd_typ varchar2(1);
10 l_chnl_cd varchar2(10);
11 l_basis_typ_cd department_vendor_list.basis_typ_cd%type;
12 cursor chnl_cursor(i_dept number,
13 i_vendor number)
14 is
15 select distinct chnl_cd, basis_typ_cd
16 from pipack.department_vendor_list
17 where dept_nbr = i_dept
18 and supplier_nbr = i_vendor
19 and fscl_yr = i_fcst_yr
20 and mstr_supplier_flg = 'N';
21 o_cy_actuals objfcst;
22 o_ly_actuals objfcst;
23 o_fcst_object objfcst;
24 o_saved_fcst_flg varchar2(1);
25 o_saved_pipackid number;
26 o_err_code number;
27 o_err_msg varchar2(2000);
28 j integer :=0;
29 k integer :=0;
30 begin
31 o_wholesaler := wholesaler(0,0,0,0,objfcst (0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
32 objfcst (0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
33 objfcst (0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
34 );
35 for i in i_dept_ven_list.first..i_dept_ven_list.last
36 loop
37 j:= j+1;
38 o_wholesaler(j).dept := i_dept_ven_list(i).dept;
39 o_wholesaler(j).supp := i_dept_ven_list(i).vendor;
40 for k in chnl_cursor(i_dept_ven_list(i).dept,i_dept_ven_list(i).vendor)
41 LOOP
42 l:= l+1;
43 l_chnl_cd := k.chnl_cd;
44 l_basis_typ_cd := k.basis_typ_cd;
45 select decode(l_chnl_cd,'R','RETAIL','D','DELIVERY') into c_chnl_cd from dual;
46 o_wholesaler(l).channel := c_chnl_cd;
47 o_wholesaler(l).basis := l_basis_typ_cd;
48 Od_Vpf_Totalfcst_Package.Vpf_Load_Total_Actuals (
49 i_dept_ven_list(i).dept,
50 i_dept_ven_list(i).vendor,
51 i_FCST_YR,
52 l_basis_typ_cd,
53 c_chnl_cd,
54 'N',
55 o_cy_actuals,
56 o_ly_actuals,
57 o_fcst_object,
58 o_comments,
59 o_saved_fcst_flg,
60 o_saved_pipackid,
61 o_err_code,
62 o_err_msg
63 );
64 end loop;
65 end loop;
66* end VPF_Load_Total_Fcst_wholesaler;
67 /

Warning: Procedure created with compilation errors.

SQL> sho err
Errors for PROCEDURE VPF_LOAD_TOTAL_FCST_WHOLESALER:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
1/1 PLS-00410: duplicate fields in RECORD,TABLE or argument list are
not permitted

CREATE OR REPLACE TYPE OBJFCST
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents


SQL> 
SQL> CREATE OR REPLACE TYPE dept_vend_array
  2  AS table OF OBJDEPTVENDOR
  3  /

Warning: Type created with compilation errors.

CREATE OR REPLACE TYPE PIPACK."OBJDEPTVENDOR"                                           AS OBJECT (dept NUMBER(4), vendor NUMBER(10));
/

26 o_err_code number;
27 o_err_msg varchar2(2000);