DECLARE 

IN_ConSigneeKey Varchar ( 20 char );/*收貨人代碼*/

IN_Type NUMBER;/*收貨人類型 1客戶和供應商 2部門*/

IN_ConSigneeKey_Count number;

IN_Dept_Count number;

IN_Supplier_Count number;

Out_Company VARCHAR(1000 char);

r_SQL_dep    Varchar(32767 char);

r_SQL_storer Varchar(32767 char);

r_SQL_ConSignee_Insert Varchar(32767 char);

WHSEID Varchar ( 20 char );/*數(shù)據(jù)庫代碼*/

STORERKEY Varchar ( 20 char );/*倉庫代碼*/

ADDRESS2 Varchar ( 400 char );

ADDWHO Varchar ( 20 char );

EDITWHO Varchar ( 20 char );

WHITESPACE Varchar ( 20 char );

STATUS Varchar ( 20 char );

type   cur_t   is   ref   cursor;

Cur_Storer   cur_t;

r_rowCount number:=0;

BEGIN

IN_ConSigneeKey :=[$收貨人Key];

if IN_Type is null then

IN_Type:=2;

end if;

r_SQL_dep :='SELECT count(*)  from EDIPROD.dsp_t_dep where Fnumber =:IN_ConSigneeKey and status=1';

EXECUTE IMMEDIATE r_SQL_dep  into IN_Dept_Count using  IN_ConSigneeKey;

--dbms_output.put_line(IN_Dept_Count);

if IN_Dept_Count>0 then

r_SQL_dep :='SELECT Fname  from EDIPROD.dsp_t_dep where Fnumber =:IN_ConSigneeKey and status=1';

EXECUTE IMMEDIATE r_SQL_dep  into Out_Company using  IN_ConSigneeKey;

IN_Type :=2;

--dbms_output.put_line(Out_Company);

else 

r_SQL_dep :='SELECT count(*)  from EDIPROD.dsp_t_supplier where Fnumber =:IN_ConSigneeKey and status=1';

EXECUTE IMMEDIATE r_SQL_dep  into IN_Supplier_Count using  IN_ConSigneeKey;

--dbms_output.put_line(IN_Supplier_Count);

if IN_Supplier_Count>0 then

r_SQL_dep :='select a.FName from (SELECT * from EDIPROD.dsp_t_supplier where Fnumber =:IN_ConSigneeKey and status =1) a where ROWNUM<2';

EXECUTE IMMEDIATE r_SQL_dep into Out_Company using IN_ConSigneeKey ;

IN_Type :=1;

--dbms_output.put_line(Out_Company);

end if;

end if;

if Out_Company is not null then

DBMS_OUTPUT.ENABLE(buffer_size => null) ;

WHSEID :='WH2';

ADDRESS2 :='車間';

ADDWHO :='某某某';

EDITWHO :='某某某';

WHITESPACE :=' ';

STATUS :='1';

r_SQL_storer:='select STORERKEY from wh2.STORER st where not exists(select * from wh2.CONSIGNEE cs where st.STORERKEY = cs.STORERKEY and cs.CONSIGNEEKEY=:IN_ConSigneeKey) and st.type = 1 and st.WHSEID=:WHSEID ORDER BY st.STORERKEY';

open Cur_Storer for r_SQL_storer using IN_ConSigneeKey,WHSEID;

loop

fetch Cur_Storer into STORERKEY;

exit when Cur_Storer%notfound;

r_rowCount :=r_rowCount+1;

r_SQL_ConSignee_Insert :='INSERT INTO WH2.CONSIGNEE

(WHSEID, STORERKEY, CONSIGNEEKEY, TYPE, COMPANY, VAT, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, CITY, STATE, ZIP, COUNTRY, ISOCNTRYCODE, CONTACT1, CONTACT2, PHONE1, PHONE2, FAX1, FAX2, EMAIL1, EMAIL2, B_CONTACT1, B_CONTACT2, B_COMPANY, B_ADDRESS1, B_ADDRESS2, B_ADDRESS3, B_ADDRESS4, B_CITY, B_STATE, B_ZIP, B_COUNTRY, B_ISOCNTRYCODE, B_PHONE1, B_PHONE2, B_FAX1, B_FAX2, B_EMAIL1, B_EMAIL2, CREDITLIMIT, STATUS, SUSR1, SUSR2, SUSR3, SUSR4, SUSR5, SUSR6, NOTES1, NOTES2, ADDDATE, ADDWHO, EDITDATE, EDITWHO) VALUES 

(:WHSEID, :STORERKEY, :IN_ConSigneeKey, :IN_Type, :Out_Company, NULL, NULL, :ADDRESS2, NULL, NULL, NULL,:WHITESPACE,:WHITESPACE,:WHITESPACE, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, :STATUS, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, sysdate, :ADDWHO, sysdate, :ADDWHO)';

dbms_output.put_line('-----開始插入第'||r_rowCount||'條------');

EXECUTE IMMEDIATE r_SQL_ConSignee_Insert using WHSEID,STORERKEY,IN_ConSigneeKey,IN_Type,Out_Company,ADDRESS2,WHITESPACE,WHITESPACE,WHITESPACE,STATUS,ADDWHO,ADDWHO;

end loop;

end if;

END;