68 lines
1.3 KiB
SQL
68 lines
1.3 KiB
SQL
CREATE OR REPLACE VIEW rlarp.plcore_upload AS
|
|
WITH
|
|
hdr AS (
|
|
SELECT DISTINCT
|
|
listcode listcode
|
|
,'00000' skey2
|
|
,'HDR' c1
|
|
,'2' c2
|
|
,p.listcode c3
|
|
,i.JAPLDS c4
|
|
,i.JAPLD1 c5
|
|
,i.JAPLD2 c6
|
|
,'Y' c7
|
|
,'N' c8
|
|
,'' c9
|
|
,'' c10
|
|
,'' c11
|
|
,'' c12
|
|
FROM
|
|
rlarp.plcore_build_fullcode p
|
|
INNER JOIN LGDAT.iprca i ON
|
|
i.japlcd = p.listcode
|
|
WHERE
|
|
p.item IS NOT NULL
|
|
AND p.errorm IS NULL
|
|
UNION ALL
|
|
SELECT
|
|
listcode listcode
|
|
,p.item||repeat(' ',20-length(p.item))||to_char(p.vb_m*1000,'FM0000000.0') skey2
|
|
,'DTL' c1
|
|
,p.listcode c2
|
|
,p.item c3
|
|
,p.vbm_uom c4
|
|
,p.vb_m::text c5
|
|
,p.price::text c6
|
|
,'' c7
|
|
,'' c8
|
|
,'' c9
|
|
,'' c10
|
|
,'' c11
|
|
,'1' c12
|
|
FROM
|
|
rlarp.plcore_build_fullcode p
|
|
WHERE
|
|
p.item IS NOT NULL
|
|
AND p.errorm IS NULL
|
|
)
|
|
--SELECT * FROM hdr
|
|
SELECT
|
|
listcode
|
|
,c1
|
|
,c2
|
|
,c3
|
|
,c4
|
|
,c5
|
|
,c6
|
|
,c7
|
|
,c8
|
|
,c9
|
|
,c10
|
|
,c11
|
|
,c12
|
|
FROM
|
|
hdr
|
|
ORDER BY
|
|
listcode
|
|
,skey2
|