1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Stored Procedure

Discussion in 'SQL PL/SQL' started by yashk111, Nov 4, 2009.

  1. yashk111

    yashk111 Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Hello,
    I am sorry for directly going in to my issue without introducing myself.
    I am new to oracle.
    I have a issue
    Can anyone explain what the issue is and how to modify the code according to the requiremnts given
    I have attached the code


    Fix DMS process to update Derived ODS table for LAST_SPONSOR_DATE and FIRST_SPONSOR_DATE
    --Our ETL calls a stored procedure DWSODS01.DWP11200_IMC_DMS_MAIN
    You will need to update the second merge statement into DWSODS01.DWT00102_DERV_IMC_MISC.
    FIRST_SPON_DT_KEY_NO = CASE WHEN (load.FIRST_SPON_DT_KEY_NO = 19000101 or (load.INMKT_FIRST_SPON_DT_KEY_NO is null)) then stg.INMKT_FIRST_SPON_DT_KEY_NO else load.FIRST_SPON_DT_KEY_NO END
    LAST_SPON_DT_KEY_NO = stg.INMKT_LAST_SPON_DT_KEY_NO

    --We will need you to also write a script that will fix/update the data in DWSODS01.DWT00102_DERV_IMC_MISC. I believe our initial load scripts loaded this table correctly back in June when we created it, but our new etl wasn't maintaining it for legacy (DMS) data feeds. The data is correct in the DWSODS01.DWT00002_IMC_DMS_MAIN table. You can use the following logic in your update statement:

    LAST_SPON_DT_KEY_NO =DWT00002_IMC_DMS_MAIN.INMKT_LAST_SPON_DT_KEY_NO
    FIRST_SPON_DT_KEY_NO =CASE WHEN (DWT00102_DERV_IMC_MISC.INMKT_FIRST_SPON_DT_KEY_NO = 19000101 or (DWT00102_DERV_IMC_MISC.INMKT_FIRST_SPON_DT_KEY_NO is null)) then DWT00002_IMC_DMS_MAIN.INMKT_FIRST_SPON_DT_KEY_NO else DWT00102_DERV_IMC_MISC.INMKT_FIRST_SPON_DT_KEY_NO END
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    This is not a do my homework forum. Show us what you have done, what code you have written, where exactly are you facing a problem?
     
  3. yashk111

    yashk111 Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    As i am new to oracle i dont know in which syntax should i write.
    Anyways the code is below

    create or replace PROCEDURE "DWP11200_IMC_DMS_MAIN"

    AS

    v_sp_name varchar2(30);
    v_err_msg varchar2(200);
    v_count number;
    v_stmt varchar2(10000);
    v_counter number;
    v_part_name varchar2(30);
    v_wrk_table VARCHAR2(100);
    v_start_dt date;

    BEGIN
    -- store in variable to update the same row later
    v_start_dt:=sysdate;
    v_sp_name:='DWP11200_IMC_DMS_MAIN';
    insert into dwt10430_sp_log
    values(v_sp_name,v_start_dt,null,'started');

    insert into dwt10440_sp_log_detail
    values(v_sp_name,sysdate,'DMS Main MERGE starting');
    commit;


    MERGE /*+ index(load DWI00002_IMC_DMS_MAIN_PK) */ INTO DWSODS01.DWT00002_IMC_DMS_MAIN load
    USING (select
    STG.IMC_KEY_NO,
    STG.IMC_AFF_ID,
    STG.IMC_AFF_KEY_NO,
    STG.IMC_NO,
    STG.IMC_AMWAY_CNTRY_CD,
    STG.IMC_AMWAY_CNTRY_NM,
    STG.IMC_ISO_CNTRY_CD,
    STG.INMKT_IMC_CLASS_CD,
    STG.INMKT_IMC_CLASS_DESC,
    STG.IMC_CNTRY_KEY_NO,
    STG.IMC_DERIVED_NM,
    STG.INMKT_BUS_NM,
    STG.INMKT_APPL_DT_KEY_NO,
    STG.INMKT_ORGNL_APPL_DT_KEY_NO,
    STG.INMKT_UPLN_IMC_NO,
    STG.INMKT_UPLN_IMC_KEY_NO,
    STG.INMKT_ORGNL_UPLN_IMC_KEY_NO,
    STG.INMKT_IMC_LANG_CD,
    STG.INMKT_IMC_LANG_NM,
    STG.INMKT_IMC_STAT_CD,
    STG.INMKT_IMC_STAT_DESC,
    STG.INMKT_LAST_ORD_DT_KEY_NO,
    STG.INMKT_LAST_SPON_DT_KEY_NO,
    STG.INMKT_FIRST_SPON_DT_KEY_NO,
    STG.INMKT_IMC_TYPE_CD,
    STG.INMKT_IMC_TYPE_DESC,
    STG.INMKT_BUS_NATR_CD,
    STG.INMKT_BUS_NATR_DESC,
    STG.INMKT_LOA_CD,
    STG.INMKT_LOA_DESC,
    STG.GLOBL_IMC_TYPE_CD,
    STG.GLOBL_IMC_TYPE_DESC,
    STG.TABLE_NO,
    STG.ROW_ID_NO,
    STG.UPDATE_FLAG,
    STG.INMKT_EXPIRE_DT_KEY_NO,
    NVL(post.POSTL_CD_KEY_NO,0)
    FROM DWSSTG01.AWT20090_DMS_MAIN STG
    LEFT OUTER JOIN dwsods01.DWT00007_IMC_DMS_CNTAC cntac ON stg.IMC_KEY_NO = cntac.IMC_KEY_NO
    LEFT OUTER JOIN dwsods01.AWT00140_POSTL_CD_DIM post ON nvl(trim(both ' ' from cntac.PRM_POST_CODE),0) = post.POSTL_CD
    AND stg.IMC_CNTRY_KEY_NO = post.CNTRY_KEY_NO
    ) stg
    ON (stg.IMC_KEY_NO= load.IMC_KEY_NO)

    WHEN MATCHED THEN
    UPDATE SET
    IMC_AFF_KEY_NO =stg.IMC_AFF_KEY_NO
    ,IMC_AMWAY_CNTRY_CD =stg.IMC_AMWAY_CNTRY_CD
    ,IMC_AMWAY_CNTRY_NM =stg.IMC_AMWAY_CNTRY_NM
    ,IMC_ISO_CNTRY_CD =stg.IMC_ISO_CNTRY_CD
    ,INMKT_IMC_CLASS_CD =stg.INMKT_IMC_CLASS_CD
    ,INMKT_IMC_CLASS_DESC =stg.INMKT_IMC_CLASS_DESC
    ,IMC_CNTRY_KEY_NO =stg.IMC_CNTRY_KEY_NO
    ,IMC_DERIVED_NM =CASE WHEN length(stg.IMC_DERIVED_NM)>0 Then stg.IMC_DERIVED_NM else load.IMC_DERIVED_NM end
    ,INMKT_BUS_NM =CASE WHEN length(stg.INMKT_BUS_NM)>0 Then stg.INMKT_BUS_NM else load.INMKT_BUS_NM end
    ,INMKT_APPL_DT_KEY_NO =CASE WHEN stg.imc_aff_id = '010' Then load.inmkt_appl_dt_key_no Else stg.INMKT_APPL_DT_KEY_NO END
    ,INMKT_ORGNL_APPL_DT_KEY_NO =CASE WHEN (load.inmkt_orgnl_appl_dt_key_no = 19000101 or (load.inmkt_orgnl_appl_dt_key_no is null)) then stg.INMKT_ORGNL_APPL_DT_KEY_NO else load.inmkt_orgnl_appl_dt_key_no END
    ,INMKT_UPLN_IMC_NO =stg.INMKT_UPLN_IMC_NO
    ,INMKT_UPLN_IMC_KEY_NO =stg.INMKT_UPLN_IMC_KEY_NO
    ,INMKT_ORGNL_UPLN_IMC_KEY_NO =stg.INMKT_ORGNL_UPLN_IMC_KEY_NO
    ,INMKT_IMC_LANG_CD =stg.INMKT_IMC_LANG_CD
    ,INMKT_IMC_LANG_NM =stg.INMKT_IMC_LANG_NM
    ,INMKT_IMC_STAT_CD =stg.INMKT_IMC_STAT_CD
    ,INMKT_IMC_STAT_DESC =stg.INMKT_IMC_STAT_DESC
    ,INMKT_LAST_ORD_DT_KEY_NO =stg.INMKT_LAST_ORD_DT_KEY_NO
    ,INMKT_LAST_SPON_DT_KEY_NO =stg.INMKT_LAST_SPON_DT_KEY_NO
    ,INMKT_FIRST_SPON_DT_KEY_NO =CASE WHEN (load.INMKT_FIRST_SPON_DT_KEY_NO = 19000101 or (load.INMKT_FIRST_SPON_DT_KEY_NO is null)) then stg.INMKT_FIRST_SPON_DT_KEY_NO else load.INMKT_FIRST_SPON_DT_KEY_NO END
    ,INMKT_IMC_TYPE_CD =stg.INMKT_IMC_TYPE_CD
    ,INMKT_IMC_TYPE_DESC =stg.INMKT_IMC_TYPE_DESC
    ,INMKT_BUS_NATR_CD =stg.INMKT_BUS_NATR_CD
    ,INMKT_BUS_NATR_DESC =stg.INMKT_BUS_NATR_DESC
    ,INMKT_LOA_CD =stg.INMKT_LOA_CD
    ,INMKT_LOA_DESC =stg.INMKT_LOA_DESC
    ,GLOBL_IMC_TYPE_CD =stg.GLOBL_IMC_TYPE_CD
    ,GLOBL_IMC_TYPE_DESC =stg.GLOBL_IMC_TYPE_DESC
    ,INMKT_EXPIRE_DT_KEY_NO =CASE WHEN NVL(stg.INMKT_EXPIRE_DT_KEY_NO, 19000101) > 19000101 THEN stg.INMKT_EXPIRE_DT_KEY_NO Else 19000101 END
    WHEN NOT MATCHED THEN INSERT
    (IMC_KEY_NO,IMC_AFF_ID,IMC_AFF_KEY_NO,IMC_NO,IMC_AMWAY_CNTRY_CD,IMC_AMWAY_CNTRY_NM,IMC_ISO_CNTRY_CD,INMKT_IMC_CLASS_CD,INMKT_IMC_CLASS_DESC,IMC_CNTRY_KEY_NO,IMC_DERIVED_NM,INMKT_BUS_NM,INMKT_APPL_DT_KEY_NO,INMKT_ORGNL_APPL_DT_KEY_NO,INMKT_UPLN_IMC_NO,INMKT_UPLN_IMC_KEY_NO,INMKT_ORGNL_UPLN_IMC_KEY_NO,INMKT_IMC_LANG_CD,INMKT_IMC_LANG_NM,INMKT_IMC_STAT_CD,INMKT_IMC_STAT_DESC,INMKT_LAST_ORD_DT_KEY_NO,INMKT_LAST_SPON_DT_KEY_NO,INMKT_FIRST_SPON_DT_KEY_NO,INMKT_IMC_TYPE_CD,INMKT_IMC_TYPE_DESC,INMKT_BUS_NATR_CD,INMKT_BUS_NATR_DESC,INMKT_LOA_CD,INMKT_LOA_DESC,GLOBL_IMC_TYPE_CD,GLOBL_IMC_TYPE_DESC,INMKT_EXPIRE_DT_KEY_NO)
    VALUES(stg.IMC_KEY_NO,stg.IMC_AFF_ID,stg.IMC_AFF_KEY_NO,stg.IMC_NO,stg.IMC_AMWAY_CNTRY_CD,stg.IMC_AMWAY_CNTRY_NM,stg.IMC_ISO_CNTRY_CD, stg.INMKT_IMC_CLASS_CD,stg.INMKT_IMC_CLASS_DESC,stg.IMC_CNTRY_KEY_NO,stg.IMC_DERIVED_NM,stg.INMKT_BUS_NM,stg.INMKT_APPL_DT_KEY_NO, stg.INMKT_ORGNL_APPL_DT_KEY_NO,stg.INMKT_UPLN_IMC_NO,stg.INMKT_UPLN_IMC_KEY_NO,stg.INMKT_ORGNL_UPLN_IMC_KEY_NO,stg.INMKT_IMC_LANG_CD,stg.INMKT_IMC_LANG_NM, stg.INMKT_IMC_STAT_CD,stg.INMKT_IMC_STAT_DESC,stg.INMKT_LAST_ORD_DT_KEY_NO,stg.INMKT_LAST_SPON_DT_KEY_NO,stg.INMKT_FIRST_SPON_DT_KEY_NO,stg.INMKT_IMC_TYPE_CD, stg.INMKT_IMC_TYPE_DESC,stg.INMKT_BUS_NATR_CD,stg.INMKT_BUS_NATR_DESC,stg.INMKT_LOA_CD,stg.INMKT_LOA_DESC,stg.GLOBL_IMC_TYPE_CD,
    stg.GLOBL_IMC_TYPE_DESC,stg.INMKT_EXPIRE_DT_KEY_NO);


    commit;

    insert into dwt10440_sp_log_detail
    values(v_sp_name,sysdate,'DMS Main MERGE Complete');
    commit;

    MERGE INTO DWSODS01.DWT00102_DERV_IMC_MISC load
    USING (select
    STG.IMC_KEY_NO,
    STG.INMKT_EXPIRE_DT_KEY_NO
    FROM DWSSTG01.AWT20090_DMS_MAIN STG

    ) stg
    ON (stg.IMC_KEY_NO= load.IMC_KEY_NO )
    WHEN MATCHED THEN
    UPDATE SET
    EXPIRE_DT_KEY_NO =CASE WHEN NVL(stg.INMKT_EXPIRE_DT_KEY_NO, 19000101) > 19000101 THEN stg.INMKT_EXPIRE_DT_KEY_NO Else 19000101 END
    ,UPDT_DT_KEY_NO = TO_CHAR(SYSDATE,'YYYYMMDD')
    WHEN NOT MATCHED THEN INSERT
    (IMC_KEY_NO,EXPIRE_DT_KEY_NO,UPDT_DT_KEY_NO,INSRT_DT_KEY_NO,UPDT_USER_ID)
    VALUES(stg.IMC_KEY_NO,stg.INMKT_EXPIRE_DT_KEY_NO,TO_CHAR(SYSDATE,'YYYYMMDD'),TO_CHAR(SYSDATE,'YYYYMMDD'),'CMND827');

    COMMIT;

    insert into dwt10440_sp_log_detail
    values(v_sp_name,sysdate,'DERV_IMC_MISC MERGE Complete');
    commit;


    update dwt10430_sp_log
    set (end_dt, status) = (select sysdate,'finished' from dual)
    where sp_name=v_sp_name
    and start_dt=v_start_dt;

    commit;


    EXCEPTION
    WHEN OTHERS THEN

    v_err_msg := substr(SQLERRM, 1, 200);
    insert into dwt10440_sp_log_detail
    values(v_sp_name,sysdate,v_err_msg);

    insert into dwt10440_sp_log_detail
    values(v_sp_name,sysdate,DBMS_UTILITY.format_error_backtrace);

    update dwt10430_sp_log
    set (end_dt, status) = (select sysdate,'ended with errors' from dual)
    where sp_name=v_sp_name
    and start_dt=v_start_dt;
    commit;

    RAISE_APPLICATION_ERROR(-20001, v_sp_name);


    END;


    I have to modify this code according to requirements given.
    I am unable to know what is happening in the code even. So i am unable to make changes also. Please help me.
     
  4. yashk111

    yashk111 Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    The above shown is the code given to me.
    I have to make changes as said above and rewrtie the code.
    According to changes i wrote code like this. Please see if any syntax errors are there and if possible please say how to test it.

    create or replace PROCEDURE "DWP11200_IMC_DMS_MAIN"

    AS

    v_sp_name varchar2(30);
    v_err_msg varchar2(200);
    v_count number;
    v_stmt varchar2(10000);
    v_counter number;
    v_part_name varchar2(30);
    v_wrk_table VARCHAR2(100);
    v_start_dt date;

    BEGIN
    -- store in variable to update the same row later
    v_start_dt:=sysdate;
    v_sp_name:='DWP11200_IMC_DMS_MAIN';
    insert into dwt10430_sp_log
    values(v_sp_name,v_start_dt,null,'started');

    insert into dwt10440_sp_log_detail
    values(v_sp_name,sysdate,'DMS Main MERGE starting');
    commit;


    MERGE /*+ index(load DWI00002_IMC_DMS_MAIN_PK) */ INTO DWSODS01.DWT00002_IMC_DMS_MAIN load
    USING (select
    STG.IMC_KEY_NO,
    STG.IMC_AFF_ID,
    STG.IMC_AFF_KEY_NO,
    STG.IMC_NO,
    STG.IMC_AMWAY_CNTRY_CD,
    STG.IMC_AMWAY_CNTRY_NM,
    STG.IMC_ISO_CNTRY_CD,
    STG.INMKT_IMC_CLASS_CD,
    STG.INMKT_IMC_CLASS_DESC,
    STG.IMC_CNTRY_KEY_NO,
    STG.IMC_DERIVED_NM,
    STG.INMKT_BUS_NM,
    STG.INMKT_APPL_DT_KEY_NO,
    STG.INMKT_ORGNL_APPL_DT_KEY_NO,
    STG.INMKT_UPLN_IMC_NO,
    STG.INMKT_UPLN_IMC_KEY_NO,
    STG.INMKT_ORGNL_UPLN_IMC_KEY_NO,
    STG.INMKT_IMC_LANG_CD,
    STG.INMKT_IMC_LANG_NM,
    STG.INMKT_IMC_STAT_CD,
    STG.INMKT_IMC_STAT_DESC,
    STG.INMKT_LAST_ORD_DT_KEY_NO,
    STG.INMKT_LAST_SPON_DT_KEY_NO,
    STG.INMKT_FIRST_SPON_DT_KEY_NO,
    STG.INMKT_IMC_TYPE_CD,
    STG.INMKT_IMC_TYPE_DESC,
    STG.INMKT_BUS_NATR_CD,
    STG.INMKT_BUS_NATR_DESC,
    STG.INMKT_LOA_CD,
    STG.INMKT_LOA_DESC,
    STG.GLOBL_IMC_TYPE_CD,
    STG.GLOBL_IMC_TYPE_DESC,
    STG.TABLE_NO,
    STG.ROW_ID_NO,
    STG.UPDATE_FLAG,
    STG.INMKT_EXPIRE_DT_KEY_NO,
    NVL(post.POSTL_CD_KEY_NO,0)
    FROM DWSSTG01.AWT20090_DMS_MAIN STG
    LEFT OUTER JOIN dwsods01.DWT00007_IMC_DMS_CNTAC cntac ON stg.IMC_KEY_NO = cntac.IMC_KEY_NO
    LEFT OUTER JOIN dwsods01.AWT00140_POSTL_CD_DIM post ON nvl(trim(both ' ' from cntac.PRM_POST_CODE),0) = post.POSTL_CD
    AND stg.IMC_CNTRY_KEY_NO = post.CNTRY_KEY_NO
    ) stg
    ON (stg.IMC_KEY_NO= load.IMC_KEY_NO)

    WHEN MATCHED THEN
    UPDATE SET
    IMC_AFF_KEY_NO =stg.IMC_AFF_KEY_NO
    ,IMC_AMWAY_CNTRY_CD =stg.IMC_AMWAY_CNTRY_CD
    ,IMC_AMWAY_CNTRY_NM =stg.IMC_AMWAY_CNTRY_NM
    ,IMC_ISO_CNTRY_CD =stg.IMC_ISO_CNTRY_CD
    ,INMKT_IMC_CLASS_CD =stg.INMKT_IMC_CLASS_CD
    ,INMKT_IMC_CLASS_DESC =stg.INMKT_IMC_CLASS_DESC
    ,IMC_CNTRY_KEY_NO =stg.IMC_CNTRY_KEY_NO
    ,IMC_DERIVED_NM =CASE WHEN length(stg.IMC_DERIVED_NM)>0 Then stg.IMC_DERIVED_NM else load.IMC_DERIVED_NM end
    ,INMKT_BUS_NM =CASE WHEN length(stg.INMKT_BUS_NM)>0 Then stg.INMKT_BUS_NM else load.INMKT_BUS_NM end
    ,INMKT_APPL_DT_KEY_NO =CASE WHEN stg.imc_aff_id = '010' Then load.inmkt_appl_dt_key_no Else stg.INMKT_APPL_DT_KEY_NO END
    ,INMKT_ORGNL_APPL_DT_KEY_NO =CASE WHEN (load.inmkt_orgnl_appl_dt_key_no = 19000101 or (load.inmkt_orgnl_appl_dt_key_no is null)) then stg.INMKT_ORGNL_APPL_DT_KEY_NO else load.inmkt_orgnl_appl_dt_key_no END
    ,INMKT_UPLN_IMC_NO =stg.INMKT_UPLN_IMC_NO
    ,INMKT_UPLN_IMC_KEY_NO =stg.INMKT_UPLN_IMC_KEY_NO
    ,INMKT_ORGNL_UPLN_IMC_KEY_NO =stg.INMKT_ORGNL_UPLN_IMC_KEY_NO
    ,INMKT_IMC_LANG_CD =stg.INMKT_IMC_LANG_CD
    ,INMKT_IMC_LANG_NM =stg.INMKT_IMC_LANG_NM
    ,INMKT_IMC_STAT_CD =stg.INMKT_IMC_STAT_CD
    ,INMKT_IMC_STAT_DESC =stg.INMKT_IMC_STAT_DESC
    ,INMKT_LAST_ORD_DT_KEY_NO =stg.INMKT_LAST_ORD_DT_KEY_NO
    ,INMKT_LAST_SPON_DT_KEY_NO =stg.INMKT_LAST_SPON_DT_KEY_NO
    ,INMKT_FIRST_SPON_DT_KEY_NO =CASE WHEN (load.INMKT_FIRST_SPON_DT_KEY_NO = 19000101 or (load.INMKT_FIRST_SPON_DT_KEY_NO is null)) then stg.INMKT_FIRST_SPON_DT_KEY_NO else load.INMKT_FIRST_SPON_DT_KEY_NO END
    ,INMKT_IMC_TYPE_CD =stg.INMKT_IMC_TYPE_CD
    ,INMKT_IMC_TYPE_DESC =stg.INMKT_IMC_TYPE_DESC
    ,INMKT_BUS_NATR_CD =stg.INMKT_BUS_NATR_CD
    ,INMKT_BUS_NATR_DESC =stg.INMKT_BUS_NATR_DESC
    ,INMKT_LOA_CD =stg.INMKT_LOA_CD
    ,INMKT_LOA_DESC =stg.INMKT_LOA_DESC
    ,GLOBL_IMC_TYPE_CD =stg.GLOBL_IMC_TYPE_CD
    ,GLOBL_IMC_TYPE_DESC =stg.GLOBL_IMC_TYPE_DESC
    ,INMKT_EXPIRE_DT_KEY_NO =CASE WHEN NVL(stg.INMKT_EXPIRE_DT_KEY_NO, 19000101) > 19000101 THEN stg.INMKT_EXPIRE_DT_KEY_NO Else 19000101 END
    WHEN NOT MATCHED THEN INSERT
    (IMC_KEY_NO,IMC_AFF_ID,IMC_AFF_KEY_NO,IMC_NO,IMC_AMWAY_CNTRY_CD,IMC_AMWAY_CNTRY_NM,IMC_ISO_CNTRY_CD,INMKT_IMC_CLASS_CD,INMKT_IMC_CLASS_DESC,IMC_CNTRY_KEY_NO,IMC_DERIVED_NM,INMKT_BUS_NM,INMKT_APPL_DT_KEY_NO,INMKT_ORGNL_APPL_DT_KEY_NO,INMKT_UPLN_IMC_NO,INMKT_UPLN_IMC_KEY_NO,INMKT_ORGNL_UPLN_IMC_KEY_NO,INMKT_IMC_LANG_CD,INMKT_IMC_LANG_NM,INMKT_IMC_STAT_CD,INMKT_IMC_STAT_DESC,INMKT_LAST_ORD_DT_KEY_NO,INMKT_LAST_SPON_DT_KEY_NO,INMKT_FIRST_SPON_DT_KEY_NO,INMKT_IMC_TYPE_CD,INMKT_IMC_TYPE_DESC,INMKT_BUS_NATR_CD,INMKT_BUS_NATR_DESC,INMKT_LOA_CD,INMKT_LOA_DESC,GLOBL_IMC_TYPE_CD,GLOBL_IMC_TYPE_DESC,INMKT_EXPIRE_DT_KEY_NO)
    VALUES(stg.IMC_KEY_NO,stg.IMC_AFF_ID,stg.IMC_AFF_KEY_NO,stg.IMC_NO,stg.IMC_AMWAY_CNTRY_CD,stg.IMC_AMWAY_CNTRY_NM,stg.IMC_ISO_CNTRY_CD, stg.INMKT_IMC_CLASS_CD,stg.INMKT_IMC_CLASS_DESC,stg.IMC_CNTRY_KEY_NO,stg.IMC_DERIVED_NM,stg.INMKT_BUS_NM,stg.INMKT_APPL_DT_KEY_NO, stg.INMKT_ORGNL_APPL_DT_KEY_NO,stg.INMKT_UPLN_IMC_NO,stg.INMKT_UPLN_IMC_KEY_NO,stg.INMKT_ORGNL_UPLN_IMC_KEY_NO,stg.INMKT_IMC_LANG_CD,stg.INMKT_IMC_LANG_NM, stg.INMKT_IMC_STAT_CD,stg.INMKT_IMC_STAT_DESC,stg.INMKT_LAST_ORD_DT_KEY_NO,stg.INMKT_LAST_SPON_DT_KEY_NO,stg.INMKT_FIRST_SPON_DT_KEY_NO,stg.INMKT_IMC_TYPE_CD, stg.INMKT_IMC_TYPE_DESC,stg.INMKT_BUS_NATR_CD,stg.INMKT_BUS_NATR_DESC,stg.INMKT_LOA_CD,stg.INMKT_LOA_DESC,stg.GLOBL_IMC_TYPE_CD,
    stg.GLOBL_IMC_TYPE_DESC,stg.INMKT_EXPIRE_DT_KEY_NO);


    commit;

    insert into dwt10440_sp_log_detail
    values(v_sp_name,sysdate,'DMS Main MERGE Complete');
    commit;

    MERGE INTO DWSODS01.DWT00102_DERV_IMC_MISC load
    USING (select
    STG.IMC_KEY_NO,
    STG.INMKT_EXPIRE_DT_KEY_NO,
    STG.INMKT_FIRST_SPON_DT_KEY_NO,
    STG.INMKT_LAST_SPON_DT_KEY_NO
    FROM DWSSTG01.AWT20090_DMS_MAIN STG

    ) stg
    ON (stg.IMC_KEY_NO= load.IMC_KEY_NO )
    WHEN MATCHED THEN
    UPDATE SET
    EXPIRE_DT_KEY_NO =CASE WHEN NVL(stg.INMKT_EXPIRE_DT_KEY_NO, 19000101) > 19000101 THEN stg.INMKT_EXPIRE_DT_KEY_NO Else 19000101 END
    ,UPDT_DT_KEY_NO = TO_CHAR(SYSDATE,'YYYYMMDD'),
    FIRST_SPON_DT_KEY_NO = CASE WHEN (load.FIRST_SPON_DT_KEY_NO = 19000101 or (load.INMKT_FIRST_SPON_DT_KEY_NO is null)) then stg.INMKT_FIRST_SPON_DT_KEY_NO else load.FIRST_SPON_DT_KEY_NO END
    ,LAST_SPON_DT_KEY_NO = stg.INMKT_LAST_SPON_DT_KEY_NO

    WHEN NOT MATCHED THEN INSERT
    (IMC_KEY_NO,EXPIRE_DT_KEY_NO,UPDT_DT_KEY_NO,INSRT_DT_KEY_NO,UPDT_USER_ID)
    VALUES(stg.IMC_KEY_NO,stg.INMKT_EXPIRE_DT_KEY_NO,TO_CHAR(SYSDATE,'YYYYMMDD'),TO_CHAR(SYSDATE,'YYYYMMDD'),'CMND827');

    COMMIT;

    insert into dwt10440_sp_log_detail
    values(v_sp_name,sysdate,'DERV_IMC_MISC MERGE Complete');
    commit;


    update dwt10430_sp_log
    set (end_dt, status) = (select sysdate,'finished' from dual)
    where sp_name=v_sp_name
    and start_dt=v_start_dt;

    commit;


    EXCEPTION
    WHEN OTHERS THEN

    v_err_msg := substr(SQLERRM, 1, 200);
    insert into dwt10440_sp_log_detail
    values(v_sp_name,sysdate,v_err_msg);

    insert into dwt10440_sp_log_detail
    values(v_sp_name,sysdate,DBMS_UTILITY.format_error_backtrace);

    update dwt10430_sp_log
    set (end_dt, status) = (select sysdate,'ended with errors' from dual)
    where sp_name=v_sp_name
    and start_dt=v_start_dt;
    commit;

    RAISE_APPLICATION_ERROR(-20001, v_sp_name);


    END;
     
  5. salmankhalid

    salmankhalid Forum Advisor

    Messages:
    116
    Likes Received:
    6
    Trophy Points:
    260
    Location:
    Lahore, Pakistan
    as i have read the whole code i don't found any syntax error in it, pls clarify your problem a bit more so that i am able to resolve it, identify errors that you got while compiling the above mentioned code.....
     
  6. debasisdas

    debasisdas Active Member

    Messages:
    46
    Likes Received:
    3
    Trophy Points:
    90
    Location:
    Bangalore, India
    Kindly post what exactly is the error message (oracle error code) that you are getting .