Untitled

/*
************************* DATA WAREHOUSE SQL SCRIPT ***************************
SCRIPT-TYPE:     STORED PROCEDURE
USE:             TARGET
NAME:            PR_0137T01
LIBRARY:         DWHCDE
PROCESS:         ....
STEP SEQUENCE:   1
DESCRIPTION:     LOAD Internal to GL mapping
AFFECTED TABLES: DWREPORTS.RPT0137T01
PARAMETERS:
DEPENDENCIES:
AUTHOR:          A.Seira
DATE CREATED:    2023-03-02
*/
---------- DROP PROCEDURE IF IT EXISTS ----------
CALL DWHCDE.PU_DROP('PROCEDURE','DWHCDE.PR_0137T01');

---------- PROαCEDURE CODE ----------

CREATE PROCEDURE DWHCDE.PR_0137T01 (
    )
    LANGUAGE SQL
    SPECIFIC DWHCDE.PR_0137T01
    NOT DETERMINISTIC
    MODIFIES SQL DATA
BEGIN

DECLARE @VN_CL016RLINTBSSFXXGL  INT;


SET @VN_CL016RLINTBSSFXXGL = DWHCDE.FU_GETCLID(16, 'INTBSSFXXGL');


INSERT INTO DWREPORTS.RPT0137T01
(		
	INTARID,
	INTARCD,
	INTCCY,
	INTACTP,
	GLARID, 
	GLARCD, 
	GLACCCY,
	GLACTP 
)

SELECT 
     RL01T.SBJARID                               AS INTARID,
     '9999' || SUBSTR(ARGEN_INT.UNQID,1,9)       AS INTARCD,
     SUBSTR(ARGEN_INT.UNQID,11,3)                AS INTCCY,
     SUBSTR(ARGEN_INT.UNQID,15,2)                AS INTACTP,
     RL01T.OBJARID                               AS GLARID,
     IFNULL(SUBSTR(ARGEN_GL.UNQID,1,16),'-100')  AS GLARCD,
     IFNULL(SUBSTR(ARGEN_GL.UNQID,18,3),'')      AS GLCCY,
     IFNULL(SUBSTR(ARGEN_GL.UNQID,22,3),'')      AS GLACTP


  FROM DWHSORTRG.RL01T                              RL01T
											        
 LEFT OUTER						                    
 JOIN DWHSORSTG.ARGEN                               ARGEN_INT
   ON ARGEN_INT.ARID = RL01T.SBJARID                 
 											        
 LEFT OUTER                                          
 JOIN DWHSORSTG.ARGEN                               ARGEN_GL
   ON ARGEN_GL.ARID = RL01T.OBJARID
 
 WHERE RL01T.ARARRLTID = @VN_CL016RLINTBSSFXXGL
 AND RL01T.ENDDT = '9999-12-31'

;
END;

---------- PROCEDURE COMMENT RL01T ---------- 
COMMENT  ON SPECIFIC PROCEDURE DWHCDE.PR_0137T01
    IS 'LOAD Internal to GL mapping';