/* test 
MERGE INTO gppp001 tb_im
     USING (SELECT '2000' AS comp_cd, 'RC10501' AS sty_cd FROM DUAL) tb_sys
        ON (tb_im.comp_cd = tb_sys.comp_cd AND tb_im.sty_cd = tb_sys.sty_cd)
WHEN NOT MATCHED
THEN
   INSERT     (comp_cd,
               org_comp_cd,
               sty_cd,
               plan_seq,
               in_user,
               in_time,
               upd_user,
               upd_time)
       VALUES ('2000',
               '2000',
               'RC10501',
               '001',
               'Z000034',
               SYSDATE,
               'Z000034',
               SYSDATE);


/* Formatted on 2018/08/20 ¿ÀÈÄ 2:43:05 (QP5 v5.294) */
MERGE INTO gppp001 tb_im
     USING (SELECT '1000' AS comp_cd, '6E75455' AS sty_cd FROM DUAL) tb_sys
        ON (tb_im.comp_cd = tb_sys.comp_cd AND tb_im.sty_cd = tb_sys.sty_cd)
WHEN NOT MATCHED
THEN
   INSERT     (comp_cd,
               org_comp_cd,
               sty_cd,
               plan_seq,
               in_user,
               in_time,
               upd_user,
               upd_time)
       VALUES ('1000',
               '1000',
               '6E75455',
               '001',
               'Z000034',
               SYSDATE,
               'Z000034',
               SYSDATE)



CREATE OR REPLACE TRIGGER TEST.tr_gppp001_bf_insert_coll_gb
  before insert ON TEST.gppp001 for each row
begin

    pkg_gppp001_insert_coll_gb.lt := pkg_gppp001_insert_coll_gb.lc;  -- ÃʱâÈ­
    
    pkg_gppp001_insert_coll_gb.i_ix := 0;
    
end tr_gppp001_bf_insert_coll_gb;
/


CREATE OR REPLACE TRIGGER TEST.tr_gppp001_af_insert_coll_gb
  after insert ON TEST.gppp001  referencing  new as new
                                         old as old
  for each row
begin

    pkg_gppp001_insert_coll_gb.lt(pkg_gppp001_insert_coll_gb.i_ix).rid         := rowidtochar(:NEW.rowid);
    
    pkg_gppp001_insert_coll_gb.lt(pkg_gppp001_insert_coll_gb.i_ix).comp_cd    := :NEW.comp_cd;
    pkg_gppp001_insert_coll_gb.lt(pkg_gppp001_insert_coll_gb.i_ix).sty_cd    := :NEW.sty_cd;
    pkg_gppp001_insert_coll_gb.lt(pkg_gppp001_insert_coll_gb.i_ix).cf_qc_yn    := :NEW.cf_qc_yn;
    pkg_gppp001_insert_coll_gb.lt(pkg_gppp001_insert_coll_gb.i_ix).CF_QC_DT    := :NEW.CF_QC_DT; 

end tr_gppp001_af_insert_coll_gb;
/




CREATE OR REPLACE PACKAGE TEST.PKG_GPPP001_INSERT_COLL_GB is


  -- Purpose : gppp001 Table ¿¡ cf_qc_yn,cf_qc_dt Trigger ¿¡ ¿¬°áµÇ¾îÀÖÀ½.
  
    type r is record( rid         varchar2(500)               -- ½Å±ÔÃß°¡µÉ¶§ÀÇ row_id
                    , comp_cd     gppp001.comp_cd%type       -- comp_cd 
                    , sty_cd     gppp001.sty_cd%type       -- sty_cd 
                    , cf_qc_yn    gppp001.cf_qc_yn%type       -- qc_yn 
                    , cf_qc_dt    gppp001.cf_qc_dt%type       -- cf_qc_dt  
                    );
  
    type t is table of r index by binary_integer;
        lt t;
        lc t;
    
    i_ix  binary_integer;
    
    procedure ps_null;

end PKG_GPPP001_INSERT_COLL_GB;
/



-- DDL Script was generated by Orange for ORACLE
-- using session 'SYSTEM@TEST_NEW' on '2018/08/27 15:57:06'.

CREATE OR REPLACE PACKAGE BODY TEST.PKG_GPPP001_INSERT_COLL_GB is


procedure ps_null is
begin

    null;

end ps_null;


end PKG_GPPP001_INSERT_COLL_GB;
/





CREATE OR REPLACE TRIGGER TEST.TR_GPPP001_CUST
AFTER INSERT
ON TEST.GPPP001
REFERENCING NEW AS NEW OLD AS OLD
DECLARE

tmpVar NUMBER;
  s_qc_yn    GPPP001.cf_qc_yn%type    default null;
  s_qc_dt    GPPP001.CF_QC_DT%type default null;
  s_qc_user    GPPP001.CF_QC_USER%type default null;
  
BEGIN
 
    if inserting then

         
         --if pkg_gppp001_insert_coll_gb.lt(pkg_gppp001_insert_coll_gb.i_ix).sty_cd  = '1D61299' then
         

         if pkg_gppp001_insert_coll_gb.lt(pkg_gppp001_insert_coll_gb.i_ix).comp_cd  = '1000' and nvl(pkg_gppp001_insert_coll_gb.lt(pkg_gppp001_insert_coll_gb.i_ix).cf_qc_yn,'N') = 'N'  then
         
            
           select max(decode(qc7_fix_gb,1,'Y','N')) , max(decode(qc7_fix_gb,1,qc7_fix_cf_dt,'')), max(decode(qc7_fix_gb,1,QC7_IN_USER,'')) 
             into s_qc_yn , s_qc_dt, s_qc_user
             from gppp057
           where comp_cd  = pkg_gppp001_insert_coll_gb.lt(pkg_gppp001_insert_coll_gb.i_ix).comp_cd
             and sty_cd =  pkg_gppp001_insert_coll_gb.lt(pkg_gppp001_insert_coll_gb.i_ix).sty_cd ;
               
               
            update gppp001
               set cf_qc_yn = s_qc_yn
                  ,cf_qc_dt = s_qc_dt 
                  ,CF_QC_USER = s_qc_user 
               where rowid     = chartorowid(pkg_gppp001_insert_coll_gb.lt(pkg_gppp001_insert_coll_gb.i_ix).rid);
               
              
        end if; 


     end if;     

 
END TR_GPPP001_CUST;
/