Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In the following code premium_codes_object_type is an object and this object having nested object and this object send from other schema procedure.

CREATE OR REPLACE PROCEDURE modify_sync_prod (
   pcot   IN     gh079.premium_codes_object_type,
   msg       OUT VARCHAR2)
AS
   p_code                 NUMBER (6);
   l_part_no              VARCHAR2 (25);
   l_priority_code_type   NUMBER (11);
   l_premium_code         VARCHAR2 (6);
   r_code                 NUMBER (6);
   l_premium_rate  NUMBER(20,4);
BEGIN
   SELECT COUNT (1)
     INTO p_code
     FROM prem_codes
    WHERE premium_code = pcot.premium_code;

    IF (p_code > 0)
   THEN
      UPDATE prem_codes r
         SET r.premium_desc = pcot.premium_desc,
             r.premium_pct = pcot.premium_pct,
             r.min_premium = pcot.min_premium,
             r.max_premium = pcot.max_premium
       WHERE premium_code = pcot.premium_code
             AND priority_code_type = pcot.priority_code_type;

      --msg := 'UPDATE ON PREM_CODES SUCCESS';
   ELSE
      INSERT INTO prem_codes (premium_code,
                              priority_code_type,
                              premium_desc,
                              premium_pct,
                              min_premium,
                              max_premium)
           VALUES (pcot.premium_code,
                   pcot.priority_code_type,
                   pcot.premium_desc,
                   pcot.premium_pct,
                   pcot.min_premium,
                   pcot.max_premium);

      --msg := 'INSERT ON PREM _CODES SUCCESS';
   END IF;


IF(pcot.PREMIUM_RATE_TYPE IS NOT NULL AND pcot.PREMIUM_RATE_TYPE.COUNT>0) THEN
   FOR i IN pcot.PREMIUM_RATE_TYPE.FIRST .. pcot.PREMIUM_RATE_TYPE.LAST
   LOOP
      l_premium_code := pcot.PREMIUM_RATE_TYPE (i).premium_code;
      l_part_no := pcot.PREMIUM_RATE_TYPE (i).part_no;
      l_priority_code_type := pcot.PREMIUM_RATE_TYPE (i).priority_code_type;
      l_premium_rate := pcot.PREMIUM_RATE_TYPE (i).premium_rate;


     SELECT COUNT (1)
     INTO r_code
     FROM premium_rates
    WHERE part_no = l_part_no
          AND premium_code = l_premium_code
          AND priority_code_type = l_priority_code_type; 

   IF (r_code > 0)
   THEN
      UPDATE premium_rates pr
         SET pr.premium_rate = l_premium_rate
       WHERE     part_no = l_part_no
             AND premium_code = l_premium_code
             AND priority_code_type = l_priority_code_type;

      msg := 'UPDATE ON PREMIUM_RATES SUCCESS';
   ELSE
      INSERT INTO premium_rates (premium_code,
                                 priority_code_type,
                                 part_no,
                                 premium_rate)
           VALUES (l_premium_code,
                   l_priority_code_type,
                   l_part_no,
                   pcot.PREMIUM_RATE_TYPE (i).premium_rate);

      --msg := 'INSERT ON PREMIUM_RATES SUCCESS';
   END IF;
   END LOOP;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      msg := 'ERROR OCCUR';
      DBMS_OUTPUT.put_line ('Error occured in sync procedure : ' || SQLERRM);


END;
/
share|improve this question
1  
What is your question? It is unclear. –  FreshPhilOfSO Oct 4 '13 at 13:15
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.