Untitled

/*
************************* DATA WAREHOUSE SQL SCRART ***************************
SCRIPT-TYPE:     STORED PROCEDURE
USE:             INPUT FEED
NAME:            PI_AR59C
LIBRARY:         DWHCDE
PROCESS:         846
STEP SEQUENCE:   2
DESCRIPTION:     Proxima Invoice ARs Current Full Feed
AFFECTED TABLES: DWHINPSTG.AR59C
PARAMETERS:      @I_BSNDT: BUSINESS DATE, DATE
AUTHOR:          A.Seira
DATE CREATED:    2023-05-08
DATE MODIFIED:   -
*******************************************************************************
*/


---------- DROP PROCEDURE IF IT EXISTS ----------
CALL DWHCDE.PU_DROP('PROCEDURE','DWHCDE.PI_AR59C');

---------- PROCEDURE CODE ----------
CREATE PROCEDURE DWHCDE.PI_AR59C (
     IN @I_BSNDT  DATE
	)
    LANGUAGE SQL
    SPECIFIC DWHCDE.PI_AR59C
    NOT DETERMINISTIC
    MODIFIES SQL DATA
BEGIN

---------- MAIN PROGRAM  ----------
INSERT INTO DWHINPSTG.AR59C
(
     ARCD,
     IPCD,
     INVCPDCD, 
     INVCPDDSC,
     VLDLCTVFLG,
     VLDDT,
     ISFNFLG,
     FNNCDT,
     ACTOVRDFLG,
     ISSDT,
     DDT,
     NOTSTLDFLG,
     PMTDT,
     INVCCCYCD,
     FNNCPRD,
     SPPLRCD,
     SPPLRIPCD,
     SPPLRNM,
     SPPLRIBAN,
     BYRACCCD,
     SPBYRACCCD
)

SELECT
     PRXMINVC.INVOICEUNIQID                              AS ARCD,
     IFNULL(PRXMINVC.CRSBUYER,'')                        AS IPCD,
     IFNULL(PRXMINVC.PRODUCTCODE,'')                     AS INVCPDCD,
     IFNULL(PRXMINVC.PRODUCTDESCRIPTION,'')              AS INVCPDDSC,
     CASE WHEN UPPER(PRXMINVC.VALIDATEDFORELECTIVESCHEME) = 'Y'
          THEN 1
          WHEN UPPER(PRXMINVC.VALIDATEDFORELECTIVESCHEME) = 'N'
          THEN 0
          ELSE -1
     END                                                 AS VLDLCTVFLG,
     IFNULL(PRXMINVC.VALIDATIONDATE,'0001-01-01')        AS VLDDT,
     CASE WHEN UPPER(PRXMINVC.ISFINANCED) = 'Y'
          THEN 1
          WHEN UPPER(PRXMINVC.ISFINANCED) = 'N'
          THEN 0
          ELSE -1
     END                                                 AS ISFNFLG,
     IFNULL(PRXMINVC.FINANCEDATE,'0001-01-01')           AS FNNCDT,
     CASE WHEN UPPER(PRXMINVC.ACTIVEOVERDUE) = 'Y'
          THEN 1
          WHEN UPPER(PRXMINVC.ACTIVEOVERDUE) = 'N'
          THEN 0
          ELSE -1
     END                                                 AS ACTOVRDFLG,
     IFNULL(PRXMINVC.ISSUEDATE,'0001-01-01')             AS ISSDT,
     IFNULL(PRXMINVC.DUEDATE,'0001-01-01')               AS DDT,
     CASE WHEN UPPER(PRXMINVC.NOTSETTLED) = 'Y'
          THEN 1
          WHEN UPPER(PRXMINVC.NOTSETTLED) = 'N'
          THEN 0
          ELSE -1
     END                                                 AS NOTSTLDFLG,
     IFNULL(PRXMINVC.PAYMENTDATE,'0001-01-01')           AS PMTDT,
     IFNULL(PRXMINVC.INVOICECCY,'UNK')                   AS INVCCCYCD,
     IFNULL(PRXMINVC.FINANCEPERIOD,0)                    AS FNNCPRD,
     IFNULL(PRXMINVC.SUPPLIERID,-100)                    AS SPPLRCD,
     IFNULL(PRXMINVC.COUNTERPARTYCRS,-100)               AS SPPLRIPCD,
     IFNULL(PRXMINVC.SUPPLIERNAME,'')                    AS SPPLRNM,
     IFNULL(PRXMINVC.S_IBAN_NO,'')                       AS SPPLRIBAN,
     IFNULL(PRXMINVC.BUYERACCOUNTID,-100)                AS BYRACCCD,
     IFNULL(PRXMINVC.SUPPLIERACCOUNTID,-100)             AS SPBYRACCCD

   FROM DWHINPSTG.PRXMINVC PRXMINVC
;

END;

---------- PROCEDURE COMMENT ----------
COMMENT ON SPECIFIC PROCEDURE DWHCDE.PI_AR59C
     IS 'Proxima Invoice ARs Current Full Feed';