ÆĶó¹ÌÅÍ ¿¹ : /rv PBGN_DATE[2006-01-16] PEND_DATE[2006-02-15] PDEPT_CODE[0029]


SELECT * FROM(
       SELECT NULL EMP_NO,
       ' '  EMP_K_NAME,
       NULL EMP_SOC_ID,
       NULL ANN_KIND_NAME,
       NULL ANN_STAY_NAME,
       ' '  EMP_POS_CODE,
       NULL ANN_POS_NAME,
       ' '  EMP_CLS_CODE,
       NULL ANN_CLS_NAME,
       NULL ANN_AFF_DEPT_NAME, 
       NULL EMP_ENT_DATE, 
       NULL EMP_WORK_BGN_DATE, 
       NULL EMP_RET_BGN_DATE,   
       NULL EMP_RET_DATE, 
       NULL EMP_SEX_TYPE, 
       NULL EMP_SEX_TYPE_NAME, 
       NULL EMP_CNTRY_IO_TYPE, 
       NULL EMP_NO_1,TO_DATE(':(PBGN_DATE)', 'YYYY-MM-DD') + LEVEL - 1 AS WORK_DATE, NULL DETAIL_NAME
                              FROM   DUAL CONNECT BY LEVEL <= TO_DATE(':(PEND_DATE)', 'YYYY-MM-DD') - TO_DATE(':(PBGN_DATE)', 'YYYY-MM-DD') + 1)
       
UNION ALL

select /*+ rule */ 
 B.EMP_NO, 
 B.EMP_K_NAME, 
 B.EMP_SOC_ID, 
(
select /*+ rule */ 
(
select /*+ rule */ 
 DETAIL_NAME 

  from ZX010MS 
 where HEAD   = 'Q001' 
   and DETAIL > ' ' 
and DETAIL = ANH.ANN_KIND_CODE 

)
 ANN_KIND_NAME 

  from QA140HS ANH 
 where EMP_NO     = B.EMP_NO 
   and ANN_SEQ_NO = 
       (select max(ANN_SEQ_NO) 
          from QA140HS 
         where EMP_NO    = B.EMP_NO 
           and ANN_DATE <= nvl(':(PEND_DATE)', to_char(sysdate, 'YYYY-MM-DD')) 
and ANN_CODE not in ('16', '17', '18', '19') 
) 
)

 ANN_KIND_NAME, 
(
select /*+ rule */ 
(
select /*+ rule */ 
 DETAIL_NAME 

  from ZX010MS 
 where HEAD   = 'Q002' 
   and DETAIL > ' ' 
and DETAIL = ANH.ANN_STAY_CODE 

)
 ANN_STAY_NAME 

  from QA140HS ANH 
 where EMP_NO     = B.EMP_NO 
   and ANN_SEQ_NO = 
       (select max(ANN_SEQ_NO) 
          from QA140HS 
         where EMP_NO    = B.EMP_NO 
           and ANN_DATE <= nvl(':(PEND_DATE)', to_char(sysdate, 'YYYY-MM-DD')) 
and ANN_CODE not in ('16', '17', '18', '19') 
) 
)

 ANN_STAY_NAME, 
 B.EMP_POS_CODE, 
(
select /*+ rule */ 
(
select /*+ rule */ 
 DETAIL_NAME 

  from ZX010MS 
 where HEAD   = 'Q005' 
   and DETAIL > ' ' 
and DETAIL = ANH.ANN_POS_CODE 

)
 ANN_POS_NAME 

  from QA140HS ANH 
 where EMP_NO     = B.EMP_NO 
   and ANN_SEQ_NO = 
       (select max(ANN_SEQ_NO) 
          from QA140HS 
         where EMP_NO    = B.EMP_NO 
           and ANN_DATE <= nvl(':(PEND_DATE)', to_char(sysdate, 'YYYY-MM-DD')) 
and ANN_CODE not in ('16', '17', '18', '19') 
) 
)

 ANN_POS_NAME, 
 B.EMP_CLS_CODE, 
(
select /*+ rule */ 
(
select /*+ rule */ 
 DETAIL_NAME 

  from ZX010MS 
 where HEAD   = 'Q006' 
   and DETAIL > ' ' 
and DETAIL = ANH.ANN_CLS_CODE 

)
 ANN_CLS_NAME 

  from QA140HS ANH 
 where EMP_NO     = B.EMP_NO 
   and ANN_SEQ_NO = 
       (select max(ANN_SEQ_NO) 
          from QA140HS 
         where EMP_NO    = B.EMP_NO 
           and ANN_DATE <= nvl(':(PEND_DATE)', to_char(sysdate, 'YYYY-MM-DD')) 
and ANN_CODE not in ('16', '17', '18', '19') 
) 
)

 ANN_CLS_NAME, 
(
select /*+ rule */ 
(
 select /*+ rule */ 
 XYZ1.AFF_DEPT_NAME DEPT_NAME 

   from QA160MS XYZ1, 
        QA170MS XYZ2, 
        QA170DT XYZ3  
  where XYZ1.AFF_DEPT_CODE                  = XYZ3.AFF_DEPT_CODE 
    and XYZ2.BGN_DATE                      <= nvl(ANH.ANN_DATE, to_char(sysdate, 'YYYY-MM-DD')) 
    and nvl(XYZ2.END_DATE, '2100-01-01') >= nvl(ANH.ANN_DATE, to_char(sysdate, 'YYYY-MM-DD')) 
    and XYZ2.BGN_DATE                       = XYZ3.BGN_DATE 
and XYZ1.AFF_DEPT_CODE = ANH.ANN_AFF_DEPT_CODE 
and XYZ1.DEPT_AFF_YN = 'Y' 

)
 ANN_AFF_DEPT_NAME 

  from QA140HS ANH 
 where EMP_NO     = B.EMP_NO 
   and ANN_SEQ_NO = 
       (select max(ANN_SEQ_NO) 
          from QA140HS 
         where EMP_NO    = B.EMP_NO 
           and ANN_DATE <= nvl(':(PEND_DATE)', to_char(sysdate, 'YYYY-MM-DD')) 
and ANN_CODE not in ('16', '17', '18', '19') 
) 
)

 ANN_AFF_DEPT_NAME, 
 B.EMP_ENT_DATE, 
 B.EMP_WORK_BGN_DATE, 
 B.EMP_RET_BGN_DATE,   
 B.EMP_RET_DATE, 
 B.EMP_SEX_TYPE, 
 decode(B.EMP_SEX_TYPE,'M','³²', 
                        'F','¿©') 
 EMP_SEX_TYPE_NAME, 
 B.EMP_CNTRY_IO_TYPE, 

       A.EMP_NO AS EMP_NO_1, nvl(WORK_DATE, ':(PBGN_DATE)') AS WORK_DATE, DETAIL_NAME
  from ( 
       select 
              EMP_NO, WORK_DATE, 
(
select /*+ rule */ 
 DETAIL_NAME 

  from ZX010MS 
 where HEAD   = 'Q045' 
   and DETAIL > ' ' 
and DETAIL = A.WORK_KIND 

) DETAIL_NAME
         from QB040MS A 
        where WORK_DATE >= ':(PBGN_DATE)'
          and WORK_DATE <= ':(PEND_DATE)' 
   and WORK_KIND <> '01' 
          and ':(PDEPT_CODE)' = 
(
select /*+ rule */ 
 ANN_AFF_DEPT_CODE 

  from QA140HS ANH 
 where EMP_NO     = A.EMP_NO 
   and ANN_SEQ_NO = 
       (select max(ANN_SEQ_NO) 
          from QA140HS 
         where EMP_NO    = A.EMP_NO 
           and ANN_DATE <= nvl(':(PEND_DATE)', to_char(sysdate, 'YYYY-MM-DD')) 
and ANN_CODE not in ('16', '17', '18', '19') 
) 
)

 and not exists 
(
select /*+ rule */ 
       WE1.EMP_NO  
  from QB030MS WE1, 
       QA010MS WE2  
 where WE1.EMP_NO                              = WE2.EMP_NO             
   and WE1.EMP_NO                              = A.EMP_NO     
   and ((WE1.WKEX_BGN_DATE                    <= A.WORK_DATE)    
    or  (WE1.WKEX_BGN_DATE                    <= WE2.EMP_ENT_DATE     
   and   ((WE2.EMP_ENT_DATE                 >= A.WORK_DATE     
   and     WE2.EMP_ENT_DATE                 <= A.WORK_DATE)    
    or    (WE2.EMP_ENT_DATE                 >= A.WORK_DATE     
   and     WE2.EMP_ENT_DATE                 <= A.WORK_DATE)))) 
   and nvl(WE1.WKEX_END_DATE, '2100-01-01') >= A.WORK_DATE     
 and ( WKEX_WORK_EXCP_YN = 'Y' ) 

)
       ) A, 
       QA010MS B 
 where A.EMP_NO(+) = B.EMP_NO 
 and B.EMP_ENT_DATE <= ':(PEND_DATE)' 
 and nvl(B.EMP_RET_DATE, '2100-01-01') >= ':(PBGN_DATE)' 

 and B.EMP_KIND_CODE not in ('01') 

 and not exists 
(
select /*+ rule */ 
       WE1.EMP_NO  
  from QB030MS WE1, 
       QA010MS WE2  
 where WE1.EMP_NO                              = WE2.EMP_NO             
   and WE1.EMP_NO                              = B.EMP_NO     
   and ((WE1.WKEX_BGN_DATE                    <= ':(PBGN_DATE)')    
    or  (WE1.WKEX_BGN_DATE                    <= WE2.EMP_ENT_DATE     
   and   ((WE2.EMP_ENT_DATE                 >= ':(PEND_DATE)'     
   and     WE2.EMP_ENT_DATE                 <= ':(PBGN_DATE)')    
    or    (WE2.EMP_ENT_DATE                 >= ':(PBGN_DATE)'     
   and     WE2.EMP_ENT_DATE                 <= ':(PEND_DATE)')))) 
   and nvl(WE1.WKEX_END_DATE, '2100-01-01') >= ':(PEND_DATE)'     
 and ( WKEX_WORK_EXCP_YN = 'Y' ) 

)

   and ':(PDEPT_CODE)' = 
(
select /*+ rule */ 
 ANN_AFF_DEPT_CODE 

  from QA140HS ANH 
 where EMP_NO     = B.EMP_NO 
   and ANN_SEQ_NO = 
       (select max(ANN_SEQ_NO) 
          from QA140HS 
         where EMP_NO    = B.EMP_NO 
           and ANN_DATE <= nvl(':(PEND_DATE)', to_char(sysdate, 'YYYY-MM-DD')) 
and ANN_CODE not in ('16', '17', '18', '19') 
) 
)

 order by 6, 
          8, 
          2