CREATE OR REPLACE FUNCTION

CREATE OR REPLACE FUNCTION SF_Taishou_Ksai_Date(v_receiptNum IN CHAR,
                                                v_his        IN CHAR)
RETURN VARCHAR2 DETERMINISTIC IS
RESULT              VARCHAR2(50);
v_result_t          VARCHAR2(50);
v_tmp_code          VARCHAR2(100);
v_uriage_sha_code   VARCHAR2(50);
v_Adv_Sha_Code      VARCHAR2(50);
v_Taishou_Ksai_Date date;
BEGIN

select *
    INTO v_tmp_code, v_uriage_sha_code
    from (select REPLACE(atom_concat(Ksai_Sha_Code), ',', ''),
                 tafd.uriage_sha_code

            from TB_ADV_FEE_DETAIL tafd
           where tafd.receipt_num = v_receiptNum
             and tafd.his = v_his
           group by tafd.receipt_num, tafd.his, tafd.uriage_sha_code)
   where rownum = 1;

IF (INSTR(v_tmp_code, v_uriage_sha_code) = 0) THEN

    IF (INSTR(v_tmp_code, '1') = 0) THEN
      v_result_t := '';
    ELSE
      v_result_t := '1';
    END IF;

    IF (INSTR(v_tmp_code, '2') = 0) THEN
      v_result_t := v_result_t || '';
    ELSE
      v_result_t := v_result_t || '2';
    END IF;

    IF (INSTR(v_tmp_code, '3') = 0) THEN
      v_result_t := v_result_t || '';
    ELSE
      v_result_t := v_result_t || '3';
    END IF;

    IF (INSTR(v_tmp_code, '4') = 0) THEN
      v_result_t := v_result_t || '';
    ELSE
      v_result_t := v_result_t || '4';
    END IF;

    IF (INSTR(v_tmp_code, '5') = 0) THEN
      v_result_t := v_result_t || '';
    ELSE
      v_result_t := v_result_t || '5';
    END IF;

    v_uriage_sha_code := substr(v_result_t, 0, 1);

END IF;

/**対象掲載日*/

SELECT Ksai_Date, Adv_Sha_Code
    INTO v_Taishou_Ksai_Date, v_Adv_Sha_Code
    FROM TB_ADV_FEE_DETAIL
   WHERE Receipt_Num = v_receiptNum
     AND His = v_his
     AND Ksai_Sha_Code = v_uriage_sha_code
     AND rownum = 1;

IF (v_Taishou_Ksai_Date = null) THEN
v_Taishou_Ksai_Date := sysdate;
END IF;

/**広告会社名略２ Adv_Sha_Name_Ryaku2*/
SELECT Adv_Sha_Name_Ryaku2
    INTO RESULT
    FROM TB_ADV_SHA
   WHERE Adv_Sha_Code = v_Adv_Sha_Code
     AND v_Taishou_Ksai_Date between Apply_Start_Date and Apply_End_Date;

RETURN(RESULT);
END SF_Taishou_Ksai_Date;

/*SELECT BEFORE HOSEI*/
select tafd.Adv_Sha_Name,
       SF_Taishou_Ksai_Date(tafd.receipt_num, tafd.his),
       tafd.*
from TB_ADV_FEE_DETAIL tafd
where tafd.Adv_Sha_Name <>
       SF_Taishou_Ksai_Date(tafd.receipt_num, tafd.his)

/*HOSEI SQL*/
update TB_ADV_FEE_DETAIL tafd
set tafd.Adv_Sha_Name = SF_Taishou_Ksai_Date(tafd.receipt_num, tafd.his),
tafd.Del_Per_Code = 'founder'

where tafd.Adv_Sha_Name <>
SF_Taishou_Ksai_Date(tafd.receipt_num, tafd.his),

/*SELECT AFTER HOSEI*/
select tafd.Adv_Sha_Name,
        SF_Taishou_Ksai_Date(tafd.receipt_num, tafd.his),
        tafd.*
   from TB_ADV_FEE_DETAIL tafd
where tafd.Adv_Sha_Name <>
        SF_Taishou_Ksai_Date(tafd.receipt_num, tafd.his),
    and tafd.Del_Per_Code = 'founder';

/*CLEAR HOSEI FLG*/
update TB_ADV_FEE_DETAIL tafd
set tafd.Del_Per_Code = null
where tafd.Del_Per_Code = 'founder';

/*HOSEI SQL*/

巴特西

CREATE OR REPLACE FUNCTION

最新文章

热门文章