WorkScripts/CMP_chonggu_OrderImport_2013-08-23.sql at master · shinexus/WorkScripts · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
--DELETE FROM tOrdCgHead;
--DELETE FROM tOrdCgBody
--SELECT * FROM tOrdCgHead@hsczbcmp WHERE SupCode = '00501' ORDER BY BillNo DESC

--select * FROM tordCgHead
--SELECT * FROM tOrdCgBody
--SELECT * FROM LOG_tOrdCgHead
--WHERE lrdate >= '2013-08-21' ORDER BY LrDate
begin
INSERT INTO tOrdCgHead
SELECT *
/***
BILLNO,
TO_DATE(LRDATE, 'yyyy-mm-dd hh24:mi:ss'),
USERID,
USERCODE,
USERNAME,
TO_DATE(JZDATE, 'yyyy-mm-dd hh24:mi:ss'),
JZRID,
JZRCODE,
JZRNAME,
JZTYPE,
RPTDATE,
PRNTIMES,
TO_DATE(PRNDATE, 'yyyy-mm-dd hh24:mi:ss'),
TIMEMARK,
YWTYPE,
YWIOTYPE,
ORGCODE,
ORGNAME,
HORGCODE,
HORGNAME,
INORGCODE,
YSORGCODE,
YSORGNAME,
DEPID,
DEPCODE,
DEPNAME,
SUPCODE,
SUPNAME,
SUPMAILADDR,
EMAIL,
TELEPHONE,
FAX,
CNTID,
HTCODE,
HTNAME,
PSCNTID,
PSHTCODE,
PSHTNAME,
JYMODE,
JSCODE,
PSJYMODE,
PSJSCODE,
CGYID,
CGYCODE,
CGYNAME,
CGTYPE,
CXBILLNO,
PSTYPE,
ISZC,
ISZS,
CGCOUNT,
CGHCOST,
CGWCOST,
CGJTAXTOTAL,
PSCOST,
STOTAL,
CJTOTAL,
SDCOUNT,
XDDATE,
ZXDATE,
ZDDATE,
YXDATE,
DHDATE,
YWSTATUS,
YSTIMES,
CGQKFLAG,
REMARK,
DATASTATUS,
QRCOUNT,
LINKMAN,
LKMTEL,
YSORGADDRESS,
YSORGLINKMAN,
YSORGLKMTEL
***/
FROM
(SELECT * FROM tOrdCgHead@hsczbcmp
WHERE SupCode = '00501'
AND InOrgCode <> 'ZB'
AND JzDate IS NOT NULL
AND LrDate >= TO_CHAR(sysdate,'yyyy-mm-dd'))
WHERE BillNo NOT IN (SELECT BillNo FROM tOrdCgHead);
--COMMIT ;
INSERT INTO tordcgbody
SELECT
  *
FROM
  tOrdCgBody@hsczbcmp
WHERE
  BillNo IN
  (
    SELECT
      billno
    FROM
      (
        SELECT
          *
        FROM
          tOrdCgHead@hsczbcmp
        WHERE
          SupCode      = '00501'
        AND InOrgCode <> 'ZB'
        AND JzDate    IS NOT NULL
        AND LrDate    >= TO_CHAR(sysdate,'yyyy-mm-dd')
      )
    WHERE
      BillNo NOT IN
      (
        SELECT
          BillNo
        FROM
          tOrdCgHead
          --tOrdCgBody
      )
  );
--COMMIT ;
end;