-----------------------------------------------------------
--- ----
---Program: ----
---This program is use to post order to all store. ----
---History: ----
---By MT.sh 20110710 first version! ----
--- ----
-----------------------------------------------------------
conn ops$edpman/panmanedp;
-set serveroutput on
-----------------------------------------------------------
---START TO POST ST_ORDER_YH TO PS_ORDER_YH@STORE ----
-----------------------------------------------------------
DECLARE
CURSOR st_order IS
SELECT distinct
o.area_no,
o.storege_no,
o.ord_no,
o.suppl_no,
o.recode_date,
o.st_flag,
o.mmail_no,
trunc(sysdate) trans_date,
to_char(sysdate,'HH24MI') trans_time
FROM st_order_yh o
WHERE
o.recode_date>=to_date(20110710,'yyyymmdd')
AND o.mmail_no is not null
AND o.storege_no=91087;
TYPE strecord IS RECORD
(
area_no st_order_yh.area_no%TYPE,
storege_no st_order_yh.storege_no%TYPE,
ord_no st_order_yh.ord_no%TYPE,
suppl_no st_order_yh.suppl_no%TYPE,
recode_date st_order_yh.recode_date%TYPE,
st_flag st_order_yh.st_flag%TYPE,
mmail_no st_order_yh.mmail_no%TYPE,
trans_date date,
trans_time number(4)
);
real_record strecord;
BEGIN
OPEN st_order;
LOOP
FETCH st_order INTO real_record;
EXIT WHEN st_order%NOTFOUND;
insert into peisong.ps_order_yh@&store_no
(area_no,storege_no,ord_no,suppl_no,recode_date,deldate,
del_time,cashier_no,ord_qty,gor_qty,flag,st_flag,
tot_amt,sto_ord_no,sto_gor_no,trans_date,trans_time)
VALUES(real_record.area_no,real_record.storege_no,
real_record.ord_no,real_record.suppl_no,
real_record.recode_date,null,null,4444,
0,0,0,real_record.st_flag,0,null,null,
real_record.trans_date,real_record.trans_time);
commit;
END LOOP;
CLOSE st_order;
END;
/
------------------------------------------------------------------
---START TO POST ST_ORDER_LINE_YH TO PS_ORDER_LINE_YH@STORE------
------------------------------------------------------------------
DECLARE
CURSOR st_order_line IS
SELECT O.MMAIL_NO,
OO.CONT_SELL_UNIT,
OO.SEQ_NO,
OO.AREA_NO,
OO.STOREGE_NO,
OO.ORD_NO,
OO.MUT_ART_NO,
OO.ORD_QTY,
OO.GOR_QTY,
OO.RECODE_DATE,
OO.SELL_PR
FROM st_order_line_yh OO,ST_ORDER_YH O
WHERE O.ORD_NO=OO.ORD_NO
AND O.MMAIL_NO iS NOT NULL
AND O.STOREGE_NO=OO.STOREGE_NO
AND OO.STOREGE_NO=91087
AND OO.RECODE_DATE>=TO_DATE(20110710,'YYYYMMDD');
TYPE strecord_line IS RECORD(
MMAIL_NO st_order_yh.mmail_no%TYPE,
CONT_SELL_UNIT st_order_line_yh.cont_sell_unit%TYPE,
SEQ_NO st_order_line_yh.seq_no%TYPE,
AREA_NO st_order_yh.area_no%TYPE,
STOREGE_NO st_order_yh.storege_no%TYPE,
ORD_NO st_order_yh.ord_no%TYPE,
MUT_ART_NO st_order_line_yh.mut_arT_no%TYPE,
ORD_QTY s