●單據編號未扣帳未確認檢查表aoou702●
DROP VIEW DS.CV_"+dbs+"PNO;
CREATE OR REPLACE FORCE VIEW ds.cv_"+dbs+"pno (x1,
x2,
x3,
x4,
x5,
x6,
x7,
x8,
x9,
x10
)
AS
SELECT 'AIM' AS x1, ina01 AS x2, smydesc AS x3,
TO_CHAR (ina02, 'yyyy/mm/dd') AS x4,
TO_CHAR (ina03, 'yyyy/mm/dd') AS x5, inaconf AS x6, inapost AS x7,
inauser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".ina_file, "+dbs+".smy_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE ina01 LIKE TRIM (smyslip) || '-%'
AND inapost = 'N'
AND inaconf <> 'X'
AND inauser = zx01(+)
AND zx03 = gem01(+)
UNION ALL
SELECT 'AIM' AS x1, imm01 AS x2, smydesc AS x3,
TO_CHAR (imm02, 'yyyy/mm/dd') AS x4,
TO_CHAR (imm12, 'yyyy/mm/dd') AS x5, immconf AS x6, imm03 AS x7,
immuser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".imm_file, "+dbs+".smy_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE imm01 LIKE TRIM (smyslip) || '-%'
AND imm10 = '1'
AND (immconf = 'N' AND imm03 = 'N')
AND immuser = zx01(+)
AND zx03 = gem01(+)
UNION ALL
SELECT 'AIM' AS x1, imm01 AS x2, smydesc AS x3,
TO_CHAR (imm02, 'yyyy/mm/dd') AS x4,
TO_CHAR (imm12, 'yyyy/mm/dd') AS x5, immconf AS x6, imm03 AS x7,
immuser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".imm_file, "+dbs+".smy_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE imm01 LIKE TRIM (smyslip) || '-%'
AND imm10 IN ('2', '3', '4')
AND (imm04 = 'N' AND imm03 = 'N')
AND immuser = zx01(+)
AND zx03 = gem01(+)
UNION ALL
SELECT 'AIM' AS x1, imo01 AS x2, smydesc AS x3,
TO_CHAR (imo02, 'yyyy/mm/dd') AS x4, '' AS x5, imoconf AS x6,
imopost AS x7, imouser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".imo_file, "+dbs+".smy_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE imo01 LIKE TRIM (smyslip) || '-%'
AND imopost = 'N'
AND imoconf <> 'X'
AND imouser = zx01(+)
AND zx03 = gem01(+)
-----------
UNION ALL
SELECT 'APM' AS x1, pmk01 AS x2, smydesc AS x3,
TO_CHAR (pmk04, 'yyyy/mm/dd') AS x4, '' AS x5, pmk18 AS x6,
' ' AS x7, pmkuser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".pmk_file, "+dbs+".smy_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE pmk01 LIKE TRIM (smyslip) || '-%'
AND pmk25 <> '6'
AND pmk18 = 'N'
AND pmkuser = zx01(+)
AND zx03 = gem01(+)
UNION ALL
SELECT 'APM' AS x1, pmi01 AS x2, smydesc AS x3,
TO_CHAR (pmi02, 'yyyy/mm/dd') AS x4, '' AS x5, pmiconf AS x6,
' ' AS x7, pmiuser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".pmi_file, "+dbs+".smy_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE pmi01 LIKE TRIM (smyslip) || '-%'
AND pmiconf = 'N'
AND pmiuser = zx01(+)
AND zx03 = gem01(+)
UNION ALL
SELECT 'APM' AS x1, pmm01 AS x2, smydesc AS x3,
TO_CHAR (pmm04, 'yyyy/mm/dd') AS x4, '' AS x5, pmm18 AS x6,
' ' AS x7, pmmuser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".pmm_file, "+dbs+".smy_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE pmm01 LIKE TRIM (smyslip) || '-%'
AND pmm25 <> '6'
AND pmm18 = 'N'
AND pmmuser = zx01(+)
AND zx03 = gem01(+)
UNION ALL
SELECT 'APM' AS x1, pna01 AS x2, smydesc AS x3,
TO_CHAR (pna04, 'yyyy/mm/dd') AS x4, '' AS x5, pna05 AS x6,
pnaconf AS x7, pnauser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".pna_file, "+dbs+".smy_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE pna01 LIKE TRIM (smyslip) || '-%'
AND (pna05 != 'X' AND pnaconf = 'N')
AND pnauser = zx01(+)
AND zx03 = gem01(+)
UNION ALL
SELECT 'APM' AS x1, rvu01 AS x2, smydesc AS x3,
TO_CHAR (rvu03, 'yyyy/mm/dd') AS x4, '' AS x5, rvuconf AS x6,
' ' AS x7, rvuuser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".rvu_file, "+dbs+".smy_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE rvu01 LIKE TRIM (smyslip) || '-%'
AND rvuconf = 'N'
AND rvuuser = zx01(+)
AND zx03 = gem01(+)
UNION ALL
SELECT 'APM' AS x1, rva01 AS x2, smydesc AS x3,
TO_CHAR (rva06, 'yyyy/mm/dd') AS x4, '' AS x5, rvaconf AS x6,
' ' AS x7, rvauser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".rva_file, "+dbs+".smy_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE rva01 LIKE TRIM (smyslip) || '-%'
AND rvaconf = 'N'
AND rvauser = zx01(+)
AND zx03 = gem01(+)
------------
UNION ALL
SELECT 'AXM' AS x1, oea01 AS x2, oaydesc AS x3,
TO_CHAR (oea02, 'yyyy/mm/dd') AS x4, '' AS x5, oeaconf AS x6,
' ' AS x7, oeauser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".oea_file, "+dbs+".oay_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE oea01 LIKE TRIM (oayslip) || '-%'
AND oeaconf = 'N'
AND oeauser = zx01(+)
AND zx03 = gem01(+)
UNION ALL
SELECT 'AXM' AS x1, oep01 AS x2, oaydesc AS x3,
TO_CHAR (oep04, 'yyyy/mm/dd') AS x4, '' AS x5, oepconf AS x6,
' ' AS x7, oepuser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".oep_file, "+dbs+".oay_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE oep01 LIKE TRIM (oayslip) || '-%'
AND oep09 <> '2'
AND oepconf <> 'X'
AND oepuser = zx01(+)
AND zx03 = gem01(+)
UNION ALL
SELECT 'AXM' AS x1, oga01 AS x2, oaydesc AS x3,
TO_CHAR (oga02, 'yyyy/mm/dd') AS x4, '' AS x5, ogaconf AS x6,
ogapost AS x7, ogauser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".oga_file, "+dbs+".oay_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE oga01 LIKE TRIM (oayslip) || '-%'
AND ogaconf <> 'X'
AND ( ( oga09 IN ('2', '3', '4', '7', '8', '9')
AND (ogaconf = 'N' OR ogapost = 'N')
)
OR (oga09 IN ('1', '5') AND ogaconf = 'N')
)
AND ogauser = zx01(+)
AND zx03 = gem01(+)
UNION ALL
SELECT 'AXM' AS x1, ofa01 AS x2, 'INVOICE ' AS x3,
TO_CHAR (ofa02, 'yyyy/mm/dd') AS x4, '' AS x5, ofaconf AS x6,
' ' AS x7, ofauser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".ofa_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE ofaconf = 'N' AND ofauser = zx01(+) AND zx03 = gem01(+)
UNION ALL
SELECT 'AXM' AS x1, oha01 AS x2, oaydesc AS x3,
TO_CHAR (oha02, 'yyyy/mm/dd') AS x4, '' AS x5, ohaconf AS x6,
ohapost AS x7, ohauser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".oha_file, "+dbs+".oay_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE oha01 LIKE TRIM (oayslip) || '-%'
AND (ohaconf = 'N' OR (ohaconf != 'X' AND ohapost = 'N'))
AND ohauser = zx01(+)
AND zx03 = gem01(+)
--------------------
UNION ALL
SELECT 'ASF' AS x1, sfb01 AS x2, smydesc AS x3,
TO_CHAR (sfb81, 'yyyy/mm/dd') AS x4, '' AS x5, sfb87 AS x6,
' ' AS x7, sfbuser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".sfb_file, "+dbs+".smy_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE sfb01 LIKE TRIM (smyslip) || '-%'
AND sfb04 <> '8'
AND sfb28 IS NOT NULL
AND sfb28 <> ' '
AND sfb87 = 'N'
AND sfbuser = zx01(+)
AND zx03 = gem01(+)
UNION ALL
SELECT 'ASF' AS x1, sfp01 AS x2, smydesc AS x3,
TO_CHAR (sfp03, 'yyyy/mm/dd') AS x4, '' AS x5, sfpconf AS x6,
sfp04 AS x7, sfpuser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".sfp_file, "+dbs+".smy_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE sfp01 LIKE TRIM (smyslip) || '-%'
AND (sfpconf != 'X' AND sfp04 = 'N')
AND sfpuser = zx01(+)
AND zx03 = gem01(+)
UNION ALL
SELECT 'ASF' AS x1, sfu01 AS x2, smydesc AS x3,
TO_CHAR (sfu02, 'yyyy/mm/dd') AS x4, '' AS x5, sfuconf AS x6,
sfupost AS x7, sfuuser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".sfu_file, "+dbs+".smy_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE sfu01 LIKE TRIM (smyslip) || '-%'
AND (sfuconf != 'X' AND sfupost = 'N')
AND sfuuser = zx01(+)
AND zx03 = gem01(+)
UNION ALL
SELECT 'ASF' AS x1, sfk01 AS x2, smydesc AS x3,
TO_CHAR (sfk02, 'yyyy/mm/dd') AS x4, '' AS x5, sfkconf AS x6,
sfkpost AS x7, sfkuser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".sfk_file, "+dbs+".smy_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE sfk01 LIKE TRIM (smyslip) || '-%'
AND (sfkconf != 'X' AND sfkpost = 'N')
AND sfkuser = zx01(+)
AND zx03 = gem01(+)
UNION ALL
SELECT 'ASF' AS x1, ksa01 AS x2, smydesc AS x3,
TO_CHAR (ksa02, 'yyyy/mm/dd') AS x4, '' AS x5, ' ' AS x6,
ksapost AS x7, ksauser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".ksa_file, "+dbs+".smy_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE ksa01 LIKE TRIM (smyslip) || '-%'
AND ksapost = 'N'
AND ksauser = zx01(+)
AND zx03 = gem01(+)
UNION ALL
SELECT 'ASF' AS x1, ksc01 AS x2, smydesc AS x3,
TO_CHAR (ksc02, 'yyyy/mm/dd') AS x4, '' AS x5, kscconf AS x6,
kscpost AS x7, kscuser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".ksc_file, "+dbs+".smy_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE ksc01 LIKE TRIM (smyslip) || '-%'
AND (kscconf != 'X' AND kscpost = 'N')
AND kscuser = zx01(+)
AND zx03 = gem01(+)
------------------------------
UNION ALL
SELECT 'AAP' AS x1, apa01 AS x2, apydesc AS x3,
TO_CHAR (apa02, 'yyyy/mm/dd') AS x4, '' AS x5, apa41 AS x6,
apa42 AS x7, apauser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".apa_file, "+dbs+".apy_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE apa01 LIKE TRIM (apyslip) || '-%'
AND apa41 = 'N'
AND apa42 = 'N'
AND apauser = zx01(+)
AND zx03 = gem01(+)
UNION ALL
SELECT 'AAP' AS x1, apf01 AS x2, apydesc AS x3,
TO_CHAR (apf02, 'yyyy/mm/dd') AS x4,
TO_CHAR (apfinpd, 'yyyy/mm/dd') AS x5, apf41 AS x6, ' ' AS x7,
apfuser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".apf_file, "+dbs+".apy_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE apf01 LIKE TRIM (apyslip) || '-%'
AND apf41 = 'N'
AND apfuser = zx01(+)
AND zx03 = gem01(+)
UNION ALL
SELECT 'AAP' AS x1, ala01 AS x2, '' AS x3,
TO_CHAR (ala08, 'yyyy/mm/dd') AS x4,
TO_CHAR (alainpd, 'yyyy/mm/dd') AS x5, alafirm AS x6, '' AS x7,
alauser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".ala_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE (alaclos IS NULL OR alaclos <> 'Y')
AND alafirm = 'N'
AND alauser = zx01(+)
AND zx03 = gem01(+)
UNION ALL
SELECT 'AAP' AS x1, ala01 AS x2, '' AS x3,
TO_CHAR (ala08, 'yyyy/mm/dd') AS x4,
TO_CHAR (alainpd, 'yyyy/mm/dd') AS x5, ala78 AS x6, alafirm AS x7,
alauser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".ala_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE ala78 = 'N'
AND (alaclos IS NULL OR alaclos <> 'Y')
AND alafirm != 'X'
AND alauser = zx01(+)
AND zx03 = gem01(+)
UNION ALL
SELECT 'AAP' AS x1, alc01 AS x2, '' AS x3,
TO_CHAR (alc08, 'yyyy/mm/dd') AS x4,
TO_CHAR (alcinpd, 'yyyy/mm/dd') AS x5, alcfirm AS x6, ' ' AS x7,
alcuser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".alc_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE alcfirm = 'N' AND alcuser = zx01(+) AND zx03 = gem01(+)
UNION ALL
SELECT 'AAP' AS x1, alc01 AS x2, '' AS x3,
TO_CHAR (alc08, 'yyyy/mm/dd') AS x4,
TO_CHAR (alcinpd, 'yyyy/mm/dd') AS x5, alc78 AS x6, ' ' AS x7,
alcuser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".alc_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE alc78 = 'N' AND alcuser = zx01(+) AND zx03 = gem01(+)
UNION ALL
SELECT 'AAP' AS x1, alk01 AS x2, '' AS x3,
TO_CHAR (alk02, 'yyyy/mm/dd') AS x4,
TO_CHAR (alkinpd, 'yyyy/mm/dd') AS x5, alkfirm AS x6, ' ' AS x7,
alkuser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".alk_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE alkfirm = 'N' AND alkuser = zx01(+) AND zx03 = gem01(+)
UNION ALL
SELECT 'AAP' AS x1, alh01 AS x2, '' AS x3,
TO_CHAR (alh021, 'yyyy/mm/dd') AS x4,
TO_CHAR (alhinpd, 'yyyy/mm/dd') AS x5, alhfirm AS x6, ' ' AS x7,
alhuser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".alh_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE alhfirm = 'N' AND alhuser = zx01(+) AND zx03 = gem01(+)
UNION ALL
SELECT 'AAP' AS x1, als01 AS x2, '' AS x3,
TO_CHAR (als02, 'yyyy/mm/dd') AS x4,
TO_CHAR (alsinpd, 'yyyy/mm/dd') AS x5, alsfirm AS x6, ' ' AS x7,
alsuser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".als_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE alsfirm = 'N' AND alsuser = zx01(+) AND zx03 = gem01(+)
----------------------
UNION ALL
SELECT 'ANM' AS x1, nmd01 AS x2, nmydesc AS x3,
TO_CHAR (nmd07, 'yyyy/mm/dd') AS x4, '' AS x5, nmd30 AS x6,
' ' AS x7, nmduser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".nmd_file, "+dbs+".nmy_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE nmd01 LIKE TRIM (nmyslip) || '-%'
AND nmd30 = 'N'
AND nmduser = zx01(+)
AND zx03 = gem01(+)
UNION ALL
SELECT 'ANM' AS x1, npl01 AS x2, nmydesc AS x3,
TO_CHAR (npl02, 'yyyy/mm/dd') AS x4, '' AS x5, nplconf AS x6,
' ' AS x7, npluser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".npl_file, "+dbs+".nmy_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE npl01 LIKE TRIM (nmyslip) || '-%'
AND nplconf = 'N'
AND npluser = zx01(+)
AND zx03 = gem01(+)
UNION ALL
SELECT 'ANM' AS x1, nmh01 AS x2, nmydesc AS x3,
TO_CHAR (nmh04, 'yyyy/mm/dd') AS x4, '' AS x5, nmh38 AS x6,
' ' AS x7, nmhuser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".nmh_file, "+dbs+".nmy_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE nmh01 LIKE TRIM (nmyslip) || '-%'
AND nmh38 = 'N'
AND nmhuser = zx01(+)
AND zx03 = gem01(+)
UNION ALL
SELECT 'ANM' AS x1, npn01 AS x2, nmydesc AS x3,
TO_CHAR (npn02, 'yyyy/mm/dd') AS x4, '' AS x5, npnconf AS x6,
' ' AS x7, npnuser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".npn_file, "+dbs+".nmy_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE npn01 LIKE TRIM (nmyslip) || '-%'
AND npnconf = 'N'
AND npnuser = zx01(+)
AND zx03 = gem01(+)
UNION ALL
SELECT 'ANM' AS x1, nmg00 AS x2, nmydesc AS x3,
TO_CHAR (nmg01, 'yyyy/mm/dd') AS x4, '' AS x5, nmgconf AS x6,
' ' AS x7, nmguser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".nmg_file, "+dbs+".nmy_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE nmg00 LIKE TRIM (nmyslip) || '-%'
AND nmgconf = 'N'
AND nmguser = zx01(+)
AND zx03 = gem01(+)
UNION ALL
SELECT 'ANM' AS x1, nne01 AS x2, nmydesc AS x3,
TO_CHAR (nne02, 'yyyy/mm/dd') AS x4, '' AS x5, nneconf AS x6,
' ' AS x7, nneuser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".nne_file, "+dbs+".nmy_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE nne01 LIKE TRIM (nmyslip) || '-%'
AND nneconf = 'N'
AND nneuser = zx01(+)
AND zx03 = gem01(+)
UNION ALL
SELECT 'ANM' AS x1, nng01 AS x2, nmydesc AS x3,
TO_CHAR (nng02, 'yyyy/mm/dd') AS x4, '' AS x5, nngconf AS x6,
' ' AS x7, nnguser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".nng_file, "+dbs+".nmy_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE nng01 LIKE TRIM (nmyslip) || '-%'
AND nngconf = 'N'
AND nnguser = zx01(+)
AND zx03 = gem01(+)
UNION ALL
SELECT 'ANM' AS x1, nni01 AS x2, nmydesc AS x3,
TO_CHAR (nni02, 'yyyy/mm/dd') AS x4,
TO_CHAR (nniinpd, 'yyyy/mm/dd') AS x5, nniconf AS x6, ' ' AS x7,
nniuser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".nni_file, "+dbs+".nmy_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE nni01 LIKE TRIM (nmyslip) || '-%'
AND nniconf = 'N'
AND nniuser = zx01(+)
AND zx03 = gem01(+)
UNION ALL
SELECT 'ANM' AS x1, nnk01 AS x2, nmydesc AS x3,
TO_CHAR (nnk02, 'yyyy/mm/dd') AS x4,
TO_CHAR (nnkinpd, 'yyyy/mm/dd') AS x5, nnkconf AS x6, ' ' AS x7,
nnkuser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".nnk_file, "+dbs+".nmy_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE nnk01 LIKE TRIM (nmyslip) || '-%'
AND nnkconf = 'N'
AND nnkuser = zx01(+)
AND zx03 = gem01(+)
---------------------
UNION ALL
SELECT 'AXR' AS x1, ola01 AS x2, ooydesc AS x3,
TO_CHAR (ola02, 'yyyy/mm/dd') AS x4, '' AS x5, olaconf AS x6,
'' AS x7, olauser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".ola_file, "+dbs+".ooy_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE ola01 LIKE TRIM (ooyslip) || '-%'
AND (ola40 IS NULL OR ola40 <> 'Y')
AND olaconf = 'N'
AND olauser = zx01(+)
AND zx03 = gem01(+)
UNION ALL
SELECT 'AXR' AS x1, ole01 AS x2, ooydesc AS x3,
TO_CHAR (ole03, 'yyyy/mm/dd') AS x4, '' AS x5, oleconf AS x6,
ole28 AS x7, oleuser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".ole_file, "+dbs+".ooy_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE ole01 LIKE TRIM (ooyslip) || '-%'
AND (oleconf = 'N' AND ole28 = 'N')
AND oleuser = zx01(+)
AND zx03 = gem01(+)
UNION ALL
SELECT 'AXR' AS x1, olc01 AS x2, 'INVOICE ' AS x3,
TO_CHAR (olc02, 'yyyy/mm/dd') AS x4, '' AS x5, olcconf AS x6,
' ' AS x7, olcuser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".olc_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE olcconf = 'N' AND olcuser = zx01(+) AND zx03 = gem01(+)
UNION ALL
SELECT 'AXR' AS x1, oma01 AS x2, ooydesc AS x3,
TO_CHAR (oma02, 'yyyy/mm/dd') AS x4, '' AS x5, omaconf AS x6,
omavoid AS x7, omauser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".oma_file, "+dbs+".ooy_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE oma01 LIKE TRIM (ooyslip) || '-%'
AND omaconf = 'N'
AND omavoid = 'N'
AND omauser = zx01(+)
AND zx03 = gem01(+)
UNION ALL
SELECT 'AXR' AS x1, ooa01 AS x2, ooydesc AS x3,
TO_CHAR (ooa02, 'yyyy/mm/dd') AS x4,
TO_CHAR (ooa021, 'yyyy/mm/dd') AS x5, ooaconf AS x6, ' ' AS x7,
ooauser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".ooa_file, "+dbs+".ooy_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE ooa01 LIKE TRIM (ooyslip) || '-%'
AND ooaconf = 'N'
AND ooauser = zx01(+)
AND zx03 = gem01(+)
---------------------
UNION ALL
SELECT 'AQC' AS x1, qcf01 AS x2, smydesc AS x3,
TO_CHAR (qcf04, 'yyyy/mm/dd') AS x4, '' AS x5, qcf14 AS x6,
'' AS x7, qcfuser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".qcf_file, "+dbs+".smy_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE qcf01 LIKE TRIM (smyslip) || '-%'
AND qcf14 = 'N'
AND qcfuser = zx01(+)
AND zx03 = gem01(+)
UNION ALL
SELECT 'AQC' AS x1, qcm01 AS x2, smydesc AS x3,
TO_CHAR (qcm04, 'yyyy/mm/dd') AS x4, '' AS x5, qcm14 AS x6,
'' AS x7, qcmuser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".qcm_file, "+dbs+".smy_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE qcm01 LIKE TRIM (smyslip) || '-%'
AND qcm14 = 'N'
AND qcmuser = zx01(+)
AND zx03 = gem01(+)
UNION ALL
SELECT 'AQC' AS x1, qcs01 AS x2, smydesc AS x3,
TO_CHAR (qcs04, 'yyyy/mm/dd') AS x4, '' AS x5, qcs14 AS x6,
'' AS x7, qcsuser AS x8, zx02 AS x9, gem02 AS x10
FROM "+dbs+".qcs_file, "+dbs+".smy_file, "+dbs+".zx_file, "+dbs+".gem_file
WHERE qcs01 LIKE TRIM (smyslip) || '-%'
AND qcs14 = 'N'
AND qcsuser = zx01(+)
AND zx03 = gem01(+);
留言列表
站台介紹 (1)