===== Merge Statement =====

<code sql>
MERGE INTO SALED T
          USING (
            SELECT c.cstmr_id, item_id, color_id, 24 mgn_rate, 0 mgr_rate,
                   hal_rate, a.CUR_PAN_PRICE pan_price, b.saleh_id
                 , to_char(sysdate, 'yyyymmdd') add_date, rUserCode user_id
              FROM TABLE(CAST(m_SALE_DTL_TBL AS SALE_DTL_TBL)) a,
                  (select id saleh_id
                    from saleh
                   where (pSaleSdate between sale_sdate and sale_edate
                         or
                         pSaleSdate < sale_sdate)) b,
                  (select case when rownum = 1 then '*****' else 'C****' end cstmr_id
                     from dual connect by level < 3) c
             --where a.id = :lv_saleh_id
           ) s
           ON (t.saleh_id = s.saleh_id and t.cstmr_id = s.cstmr_id
               and t.item_id  = s.item_id and t.color_id = s.color_id)
         WHEN MATCHED THEN
           UPDATE SET t.mgn_rate = s.mgn_rate
                    , t.hal_rate = s.hal_rate
                    , t.pan_price = s.pan_price
                    , t.add_date = s.add_date
                    , t.user_id = s.user_id
         WHEN NOT MATCHED THEN
           INSERT (cstmr_id, item_id, color_id, mgn_rate, mgr_rate,
                   hal_rate, pan_price, saleh_id, add_date, user_id)
           values (s.cstmr_id, s.item_id, s.color_id, s.mgn_rate, s.mgr_rate,
                   s.hal_rate, s.pan_price, s.saleh_id, s.add_date, s.user_id);
</code>