Date Tracking Problems in API:Oracle Apps

Date Tracking Problems in API

 if ((TRUNC (a.eff_date) < TRUNC (b.effective_start_date)) and
             (TRUNC (b.effective_end_date)!= TO_DATE ('31-DEC-4712'))) then
                   if (TRUNC (b.effective_end_date) > TRUNC (a.eff_date)) then
                   lc_  date_mode := 'CORRECTION';
                   lc_date := b.effective_start_date;
                    v_status := 'S';
                  DBMS_OUTPUT.put_line ('Condition 1');
               else
                   v_status := 'E';
                   v_error := 'No need to update';
                    DBMS_OUTPUT.put_line ('Condition 2');
               end if;
           
          elsif ((TRUNC (b.effective_start_date)  < TRUNC (a.eff_date))
              and (TRUNC (b.effective_end_date)!= TO_DATE ('31-DEC-4712'))) then
              
               if (TRUNC (b.effective_end_date) > TRUNC (a.eff_date)) then
                   lc_date_mode := 'UPDATE_CHANGE_INSERT';
                   lc_date := a.eff_date;
                    v_status := 'S';
                  DBMS_OUTPUT.put_line ('Condition 3');
               else
                   v_status := 'E';
                  v_error := 'No need to update';
                    DBMS_OUTPUT.put_line ('Condition 4');
               end if;
          
        
         else
             IF TRUNC (b.effective_start_date) = TRUNC (a.eff_date)
             THEN
                lc_date_mode := 'CORRECTION';
                lc_date := a.eff_date;
                v_status := 'S';
                DBMS_OUTPUT.put_line ('Condition 5');
             ELSE
                lc_date := a.eff_date;
                lc_date_mode := 'UPDATE';
                v_status := 'S';
                DBMS_OUTPUT.put_line ('Condition 6');
             END IF;
         end if;
       

         IF lc_date_mode = 'UPDATE' AND v_status = 'S'
         THEN
            IF b.effective_end_date != TO_DATE ('31-DEC-4712')
            -- need to chnage
            THEN
               lc_date_mode := 'UPDATE_CHANGE_INSERT';
               lc_date := a.eff_date;
               DBMS_OUTPUT.put_line ('Condition 7');
            elsif ((b.effective_end_date = TO_DATE ('31-DEC-4712')) and
                  (b.effective_start_date >= a.eff_date)) then
               lc_date_mode := 'CORRECTION';
               lc_date := b.effective_start_date;
               DBMS_OUTPUT.put_line ('Condition 8');
            elsif ((b.effective_end_date = TO_DATE ('31-DEC-4712')) and
                  (b.effective_start_date < a.eff_date)) then
              lc_date := a.eff_date;
                lc_date_mode := 'UPDATE';
                DBMS_OUTPUT.put_line ('Condition 9');
            END IF;
         END IF;

         DBMS_OUTPUT.put_line ('**** Before Calling API ******');
         DBMS_OUTPUT.put_line ('a.eff_date : ' || a.eff_date);
         DBMS_OUTPUT.put_line (   ' b.effective_start_date : '
                               || b.effective_start_date
                              );
         DBMS_OUTPUT.put_line ('lc_date_mode : ' || lc_date_mode);
         DBMS_OUTPUT.put_line (' lc_date : ' || lc_date);
 

"Team Work" More We Less Me.

            Sameer Patil