Untitled

mail@pastecode.io avatar
unknown
plain_text
a year ago
2.2 kB
0
Indexable
Never
/*
INSERT INTO DWHINPTRG.EV02I21
(
     TXNCD,
     SSCD,
     PPNDT,
     TXNTPCD,
     TXNSTSTPCD,
     TXNCCYCD,
     PRMARCD,
     CSHFLOAMT,
     EURAMT,
     TXNDT,
     TXNBSNDT,
     TXNVALDT,
     EXECSTCD,
     TXNREF,
     CRDBIND
)*/

SELECT
     CAST(PRXMTRN.UNIQUEID AS CHAR(40)) ||
       TRIM(REPLACE(CHAR(PRXMTRN.TRANSACTDATE),'-','')) || 
       PRXMTRN.INVOICEID                                         AS TXNCD,
 --  CAST(PRXMTRN.UNIQUEID AS CHAR(40)) || PRXMTRN.INVOICEID     AS TXNCD,
     'PRXMINVC'                                                  AS SSCD,
     '2023-06-30'                                                    AS PPNDT,
     'PRXMINVC-' || TRIM(PRXMTRN.TRANSTYPENUMERIC)               AS TXNTPCD,
     'SUCCESS'                                                   AS TXNSTSTPCD,
     IFNULL(PRXMTRN.TRANSCCY,'')                                 AS TXNCCYCD,
     IFNULL(PRXMTRN.INVOICEID,-100)                              AS PRMARCD,
     IFNULL(PRXMTRN.TRANSACTAMNT,0.0)
     * CASE WHEN PRXMTRN.DEBITORCREDIT = 'D'
            THEN -1  
            ELSE  1 
       END                                                       AS CSHFLOAMT,
     CASE
       WHEN C8PF.C8SEI = 'Y'
       THEN ROUND(1.00*(PRXMTRN.TRANSACTAMNT)*IFNULL(NULLIF(C8PF.C8USR,0),1),2)
       ELSE ROUND(1.00*(PRXMTRN.TRANSACTAMNT)/IFNULL(NULLIF(C8PF.C8USR,0),1),2)
     END
     * CASE WHEN PRXMTRN.DEBITORCREDIT = 'D'
            THEN -1  
            ELSE  1 
       END                                                       AS EURAMT,
     IFNULL(PRXMTRN.TRANSACTDATE,'0001-01-01')                   AS TXNDT,
     IFNULL(PRXMTRN.TRANSACTDATE,'0001-01-01')                   AS TXNBSNDT,
     IFNULL(PRXMTRN.VALUEDATE,'0001-01-01')                      AS TXNVALDT,
     IFNULL(PRXMTRN.DEBTORCRS,'')                                AS EXECSTCD,
     IFNULL(PRXMTRN.REVERSINGID,'')                              AS TXNREF,
     IFNULL(PRXMTRN.DEBITORCREDIT,'')	                         AS CRDBIND

   FROM DWHINPSTG.PRXMTRN         PRXMTRN

   LEFT OUTER
   JOIN DWHINPSTG.C8PF            C8PF
     ON C8PF.C8CCY = PRXMTRN.TRANSCCY

;