-- ============================================================
-- Database name: FOLHA
-- Created on: 18/09/2019
-- ============================================================
-- ============================================================
--   TABLE: BANCOHORAS_TABLE                                          
-- ============================================================

--TIPOS
DROP TYPE "BANCOHORAS_TABLE"; /*Apaga a tabela do tipo "PONTODEMONST_TYPE" para que o mesmo possa ser modificado*/
/

create or replace TYPE "BANCOHORAS_TYPE" AS
OBJECT (NRVINCULOM    NUMBER,
		NMVINCULOM    VARCHAR2(100),
		NRESTRUTGEREN NUMBER,
		NRPARCLIDER   NUMBER,
		SALDO_FOLGAR_PER_BHR_NORM     VARCHAR2(50),
		SALDO_FOLGAR_ACR_PER_BHR_NORM VARCHAR2(50)
        ); /*Indica se o dia foi apurado ou nÃ£o*/
/

create or replace TYPE "BANCOHORAS_TABLE" AS TABLE OF BANCOHORAS_TYPE;
/

create or replace FUNCTION BANCOHORAS_DADOS( P_NRORG             IN NUMBER,
                                             P_NRPERIODOAPURACAO IN NUMBER,
                                             P_DTFINALAPURACAO   IN VARCHAR2, --DATE,
											 P_NRESTRUTLEGAL     IN NUMBER,
											 P_NRESTRUTGEREN     IN NUMBER,
											 P_NRESTRUTSIND      IN NUMBER,
											 P_NRVINCULOM        IN NUMBER,
											 P_OPERADORLIDER     IN NUMBER
											 )
RETURN BANCOHORAS_TABLE PIPELINED
IS
   V_NRPARCRESPINF NUMBER;	
   OUT_RECORD BANCOHORAS_TYPE := BANCOHORAS_TYPE ( NULL, NULL, NULL, NULL, NULL, NULL);
BEGIN
	FOR C_ESTRUTURA IN
	(
	SELECT NRESTRUTURAMREL
      FROM HIERARQUIAORG
     WHERE NRORG = P_NRORG
       AND (NRESTRUTURAMSUP IN (SELECT ESTRUTURAM.NRESTRUTURAM                            
                                  FROM ESTRUTURAM
                                  JOIN ESTRUTURAH
                                    ON ESTRUTURAH.NRESTRUTURAM  = ESTRUTURAM.NRESTRUTURAM
                                   AND ESTRUTURAH.NRORG         = ESTRUTURAM.NRORG
                                  JOIN PARCNEGOCIO
                                    ON PARCNEGOCIO.NRPARCNEGOCIO = ESTRUTURAH.NRPARCRESPINF
                                   AND PARCNEGOCIO.NRORG         = ESTRUTURAM.NRORG
   								   AND ESTRUTURAH.NRPARCRESPINF IS NOT NULL	
                                  JOIN OPERADOR
                                    ON OPERADOR.NRPARCNEGOCIO  = PARCNEGOCIO.NRPARCNEGOCIO
                                   AND OPERADOR.NRORG          = PARCNEGOCIO.NRORG
                                 WHERE ESTRUTURAM.NRORG        = P_NRORG
								   AND (ESTRUTURAM.NRESTRUTURAM = P_NRESTRUTGEREN OR P_NRESTRUTGEREN IS NULL)
                                   AND (OPERADOR.CDOPERADOR = LPAD(P_OPERADORLIDER, 12, 0) OR OPERADOR.CDOPERADOR = P_OPERADORLIDER OR P_OPERADORLIDER is null))
                 OR NRESTRUTURAMREL IN (SELECT
                                          ESTRUTURAM.NRESTRUTURAM                            
                                         FROM ESTRUTURAM
                                         JOIN ESTRUTURAH
                                           ON ESTRUTURAH.NRESTRUTURAM  = ESTRUTURAM.NRESTRUTURAM
                                          AND ESTRUTURAH.NRORG         = ESTRUTURAM.NRORG
                                         JOIN PARCNEGOCIO
                                           ON PARCNEGOCIO.NRPARCNEGOCIO = ESTRUTURAH.NRPARCRESPINF
                                          AND PARCNEGOCIO.NRORG         = ESTRUTURAM.NRORG
                                         JOIN OPERADOR
                                           ON OPERADOR.NRPARCNEGOCIO    = PARCNEGOCIO.NRPARCNEGOCIO
                                          AND OPERADOR.NRORG            = PARCNEGOCIO.NRORG
                                        WHERE ESTRUTURAM.NRORG          = P_NRORG
                                          AND (OPERADOR.CDOPERADOR      = LPAD(P_OPERADORLIDER, 12, 0) OR OPERADOR.CDOPERADOR = P_OPERADORLIDER)))
     ORDER BY NRESTRUTURAMREL
	)
	LOOP
		SELECT MAX(NRPARCRESPINF)
		  INTO V_NRPARCRESPINF	
		  FROM ESTRUTURAH
		 WHERE NRORG = P_NRORG
           AND NRESTRUTURAM = C_ESTRUTURA.NRESTRUTURAMREL
		   AND DTMESCOMPETENC = (SELECT MAX(DTMESCOMPETENC)
									FROM ESTRUTURAH HH
								   WHERE HH.NRORG = P_NRORG
									 AND HH.NRESTRUTURAM = ESTRUTURAH.NRESTRUTURAM
									 AND HH.DTMESCOMPETENC <= TO_DATE(P_DTFINALAPURACAO, 'DD/MM/YYYY'));  				
		FOR C_VINCULO IN
		(
		SELECT
			NRVINCULOM,
			NMVINCULOM,
			--NVL(TO_CHAR(SALDO_A_FOLGAR_NORMAL),         0) SALDO_A_FOLGAR_NORMAL,
			NVL(TO_CHAR(SALDO_FOLGAR_PER_BHR_NORM),     0) SALDO_FOLGAR_PER_BHR_NORM,
			NVL(TO_CHAR(SALDO_FOLGAR_ACR_PER_BHR_NORM), 0) SALDO_FOLGAR_ACR_PER_BHR_NORM
		FROM (
			SELECT * FROM (
				SELECT NRSITUFREQUENCIA,
						(ROUND(QTREFAPURSITUFREQ, 2)) QTREFAPURSITUFREQ,
						VM.NMVINCULOM,
						VM.NRVINCULOM,
						NRPERIODOAPURACAO,
						(SELECT NVL(ROUND(QTREFAPURSITUFREQ, 7), '0000')
							FROM (SELECT QTREFAPURSITUFREQ
									FROM PTO_RESUMOSITUFREQ
									WHERE NRVINCULOM        = VM.NRVINCULOM
									AND NRPERIODOAPURACAO = P_NRPERIODOAPURACAO
									AND NRORG             = P_NRORG
									AND NRSITUFREQUENCIA  = 18020
								ORDER BY DTAPURACAOSITUFREQ DESC)
							WHERE ROWNUM = 1) AS SALDO_FOLGAR_PER_BHR_NORM,
						(SELECT NVL(ROUND(QTREFAPURSITUFREQ, 7), '0000')
							FROM (SELECT QTREFAPURSITUFREQ
									FROM PTO_RESUMOSITUFREQ
									WHERE NRVINCULOM        = VM.NRVINCULOM
									AND NRPERIODOAPURACAO = P_NRPERIODOAPURACAO
									AND NRORG             = P_NRORG
									AND NRSITUFREQUENCIA  = 18420
								ORDER BY DTAPURACAOSITUFREQ DESC)
							WHERE ROWNUM = 1) AS SALDO_FOLGAR_ACR_PER_BHR_NORM
					FROM PTO_RESUMOSITUFREQ
					JOIN GPE_VINCULOM VM
					  ON VM.NRVINCULOM = PTO_RESUMOSITUFREQ.NRVINCULOM
					 AND (VM.NRVINCULOM = P_NRVINCULOM OR P_NRVINCULOM IS NULL)
					 AND VM.NRORG      = PTO_RESUMOSITUFREQ.NRORG
					 AND EXISTS (select 1 
					               from gpe_vinculoh
                                  where gpe_vinculoh.nrorg = vm.nrorg
                                    and gpe_vinculoh.nrvinculom    = vm.nrvinculom 
									and (gpe_vinculoh.NRESTRUTLEGAL = P_NRESTRUTLEGAL or P_NRESTRUTLEGAL is null)
									and (gpe_vinculoh.nrestrutgeren = P_NRESTRUTGEREN or P_NRESTRUTGEREN is null)
									and (gpe_vinculoh.NRESTRUTSIND  = P_NRESTRUTSIND  or P_NRESTRUTSIND  is null)
                                    and gpe_vinculoh.dtmescompetenc = (select max(dtmescompetenc) 
									                                     from gpe_vinculoh hh 
																		where hh.nrorg      = vm.nrorg 
																		  AND HH.NRVINCULOM	= VM.NRVINCULOM
                                                                          and hh.dtmescompetenc <= TO_DATE(P_DTFINALAPURACAO, 'DD/MM/YYYY')))																		  
				WHERE PTO_RESUMOSITUFREQ.NRPERIODOAPURACAO = P_NRPERIODOAPURACAO
                  AND PTO_RESUMOSITUFREQ.NRORG             = P_NRORG
                  AND EXISTS (SELECT 1
					     		FROM GPE_MOVIMENTACAO MOV
								JOIN ESTRUTURAM EM
									ON EM.NRESTRUTURAM = MOV.NRESTRUTURAM
									AND EM.NRORG       = MOV.NRORG
								WHERE MOV.NRORG            = VM.NRORG
									AND MOV.NRVINCULOM     = VM.NRVINCULOM
									AND MOV.NRTIPOTRANSFER = 2 /* movimentação gerencial */
									AND MOV.NRESTRUTURAM   = C_ESTRUTURA.NRESTRUTURAMREL
									AND MOV.DTINIMOVIMENT = (SELECT MAX(DTINIMOVIMENT)
																FROM GPE_MOVIMENTACAO
															WHERE NRORG           = MOV.NRORG
   															  AND NRVINCULOM      = MOV.NRVINCULOM
															  AND NRTIPOESTRUTURA = MOV.NRTIPOESTRUTURA
															  AND DTINIMOVIMENT  <= TO_DATE(P_DTFINALAPURACAO, 'DD/MM/YYYY')))
			) PTO_RESUMOSITUFREQ
		) PIVOT (SUM(QTREFAPURSITUFREQ) FOR NRSITUFREQUENCIA IN (18000 SALDO_A_FOLGAR_NORMAL))
		WHERE TO_NUMBER(SALDO_FOLGAR_ACR_PER_BHR_NORM) > 0
		ORDER BY TO_NUMBER(SALDO_FOLGAR_ACR_PER_BHR_NORM) DESC)
		LOOP
			OUT_RECORD.NRVINCULOM    					:= C_VINCULO.NRVINCULOM;
			OUT_RECORD.NMVINCULOM    					:= C_VINCULO.NMVINCULOM;			
			OUT_RECORD.NRESTRUTGEREN                    := C_ESTRUTURA.NRESTRUTURAMREL; 
			OUT_RECORD.NRPARCLIDER                      := V_NRPARCRESPINF;
			OUT_RECORD.SALDO_FOLGAR_PER_BHR_NORM    	:= C_VINCULO.SALDO_FOLGAR_PER_BHR_NORM;
			OUT_RECORD.SALDO_FOLGAR_ACR_PER_BHR_NORM    := C_VINCULO.SALDO_FOLGAR_ACR_PER_BHR_NORM; 
			/*grava registro*/
			PIPE ROW (OUT_RECORD);
		END LOOP;			
	END LOOP;
END ;
/
