Translate

Wednesday, February 6, 2019

OPM create operation api validations and Code



 PROCEDURE validate_oprn_data (p_validate_operation IN VARCHAR2)
   IS
--Loacal variables
      ln_act               VARCHAR2 (100);
      ln_oprn_vers         NUMBER;
      ln_bulk_error_cnt    NUMBER;
      ln_max_oprn_vers     NUMBER;
      ln_scale_type        NUMBER;
      ln_plan_type         NUMBER;
      ln_plan_type_val     VARCHAR2(30);
      ln_organization_id   NUMBER;
      l_count              NUMBER;
      l_transaction_id     NUMBER;     
      l_paln_type          VARCHAR2 (100);
      l_routing_no         VARCHAR2 (100);
      l_oprn_no            VARCHAR2 (100);
      l_activity           VARCHAR2 (100);
      l_resources          VARCHAR2 (100);
     
      lc_activity_data     VARCHAR2 (25);     
      lc_process_qty_uom   VARCHAR2 (25);
      lc_resource          VARCHAR2 (25);
      lc_act_rsrce         NUMBER;
      lc_oprn_class        VARCHAR2 (7);
      ln_oprn_id           NUMBER;
      lc_usage_uom         VARCHAR2 (10);
      lc_cost_alys_code    VARCHAR2 (4);
      lt_rsrc_first        VARCHAR2 (25);
      lt_rsrc_last         VARCHAR2 (25);

--===============================
--Cursor to get Operation Data
--===============================
      CURSOR lcu_oprn_data (cp_status_flag VARCHAR2)
      IS                                                 
        SELECT  transaction_id
              , new_oprn_no oprn_no                                       
              , oprn_vers                                           
              , oprn_desc
              , oprn_class
              , oprn_class_desc
              , owner_org_code
              , oper_valid_from  
              , process_qty_uom
              , activity
           --   , resources            
              , resource_count
              , resource_usage
              , DECODE(usage_uom,'hr','Hrs',usage_uom) usage_uom
              , scale_type
              , process_output_qty
              , component_class
              , cost_analysis_code
              , DECODE(UPPER(plan_type),'OPTIONAL',NULL,plan_type) plan_type
              , offset_interval
              , organization_id
              , error_code
              , record_status
              , error_message
          FROM  xxblr_opm_routing_stg                         
         WHERE  1=1
           AND (   NVL(record_status, 'N') = gc_newrecord_flag
                OR NVL(record_status, 'N') = gc_validation_error_flag
               )                        
      ORDER BY  transaction_id;--oprn_no;

--===============================
--Cursor to get Operation Number
--===============================
      CURSOR lcu_oprn_no (cp_oprn_no VARCHAR2)
      IS
         SELECT oprn_no
           FROM apps.gmd_operations_b
          WHERE 1=1
            AND UPPER(oprn_no) = UPPER(cp_oprn_no);

--===============================
--Cursor to get Operation Number
--===============================                  
      CURSOR lcu_organization_id(p_org_code  varchar2)
      IS      
          SELECT organization_id
            FROM apps.org_organization_definitions
           WHERE 1=1
             AND UPPER (organization_code) =UPPER (p_org_code);

--===============================
--Cursor to get Operation Version
--===============================
      CURSOR lcu_oprn_vers (cp_oprn_no VARCHAR2, cp_oprn_vers NUMBER)
      IS
         SELECT oprn_id
           FROM apps.gmd_operations_b
          WHERE 1=1
            AND UPPER(oprn_no)  = (cp_oprn_no)
            AND oprn_vers       = NVL(cp_oprn_vers,1);

--===================================
--Cursor to get Operation Max Version
--===================================
      CURSOR max_oprn_vers
      IS
         SELECT MAX (oprn_vers)
           FROM apps.gmd_operations_b;

--===============================
--Cursor to get Operation Desc
--===============================
      CURSOR lcu_oprn_desc (cp_oprn_desc NUMBER)
      IS
         SELECT oprn_desc
           FROM apps.gmd_operations_b
          WHERE 1=1
            AND UPPER(oprn_desc) = UPPER(cp_oprn_desc);

--===============================
--Cursor to get Oprn Valid From
--===============================
      CURSOR lcu_valid_from (cp_oprn_no VARCHAR2)
      IS
         SELECT effective_start_date
           FROM apps.gmd_operations_b
          WHERE 1=1
            AND oprn_no = cp_oprn_no;

--=========================================
--Cursor to get Process Output Qty and UOM
--=========================================
      CURSOR lcu_process_qty_uom (cp_uom_code VARCHAR2)
      IS     
         SELECT DISTINCT uom_code
           FROM apps.mtl_units_of_measure                   
          WHERE 1=1
            AND UPPER(uom_code) = TRIM(UPPER(cp_uom_code));
                  
--=========================================
--Cursor to get Plan Type
--=========================================
      CURSOR lcu_plan_type (p_plan_type VARCHAR2)
      IS                                         
         SELECT lookup_code
           FROM apps.fnd_lookup_values
          WHERE 1=1
            AND lookup_type = 'GMD_PRIM_RSRC_IND'
            AND (   UPPER(meaning)      = UPPER (p_plan_type)                   
                 OR UPPER(lookup_code)  = UPPER (p_plan_type)
                );

--===============================
--Cursor to get Operation Class
--===============================
      CURSOR lcu_oprn_class (cp_oprn_class_desc VARCHAR2)
      IS
         SELECT oprn_class
           FROM apps.fm_oprn_cls foc
          WHERE 1=1
            AND (   UPPER(foc.oprn_class_desc) = UPPER (cp_oprn_class_desc)
                 OR UPPER(foc.oprn_class)      = UPPER (cp_oprn_class_desc)
                );

--===============================
--Cursor to get Resource
--===============================
      CURSOR lcu_resource (cp_resource_code VARCHAR2)
      IS
         SELECT resources
           FROM apps.cr_rsrc_mst_vl
          WHERE 1=1
            AND UPPER(resources) = UPPER(TRIM(cp_resource_code));

/*
--===============================
--Cursor to get Resource Usage
--===============================
      CURSOR lcu_resource_usage (cp_resources VARCHAR2)
      IS
         SELECT resource_usage
           FROM apps.gmd_operation_resources
          WHERE 1=1
            AND UPPER(resources) = UPPER(cp_resources);
*/
--===============================
--Cursor to get Scale Type
--===============================
      CURSOR lcu_scale_type (p_scale_type VARCHAR2)
      IS
         SELECT lookup_code
           FROM apps.fnd_lookup_values
          WHERE 1=1
            AND UPPER(lookup_type)      = 'SCALE_TYPE'
            AND (   UPPER(meaning)      = UPPER(p_scale_type)
                 OR UPPER(lookup_code)  = UPPER(p_scale_type)
                );

--=================================
--Cursor to get Resource Usage UOM
--=================================
      CURSOR lcu_rers_usage_uom (p_usage_uom VARCHAR2)
      IS     
         SELECT uom_code
           FROM apps.mtl_units_of_measure
          WHERE 1=1
            AND (   UPPER(uom_code)        =  UPPER (p_usage_uom)
                 OR UPPER(unit_of_measure) =  UPPER (p_usage_uom)
                );

      --===============================
--Cursor to get Cost Analysis Code
--===============================
      CURSOR lcu_cost_alys_code (p_cost_analysis_code VARCHAR2)
      IS
         SELECT cost_analysis_code
           FROM apps.cm_alys_mst
          WHERE 1=1
            AND cost_analysis_code = p_cost_analysis_code;

--===============================
--Cursor to get Activity
--===============================
      CURSOR lcu_act_data (cp_activity VARCHAR2)
      IS
         SELECT activity
           FROM apps.gmd_activities_vl
          WHERE 1=1
            AND UPPER (activity) = UPPER (cp_activity);

 --===============================
 --Cursor to get Valid Record Count
--===============================
      CURSOR lcu_valrec_cnt (cp_record_status VARCHAR2)
      IS
         SELECT COUNT (1)
           FROM xxblr_opm_routing_stg
          WHERE 1=1
            AND UPPER(record_status) = UPPER(cp_record_status);
                             
--============================
--Cursor to get Plan Type
--============================
CURSOR lcu_plan_type_val(cp_oprn_no VARCHAR2,cp_oprn_vers NUMBER,cp_activity VARCHAR2) --(cp_transaction_id  NUMBER)
   IS     
  SELECT DECODE(rsrc_cnt,0,'Primary','Auxiliary') Plan_Type
       FROM (        
            SELECT COUNT(1) rsrc_cnt
              FROM apps.gmd_operation_resources gmor
                  ,apps.gmd_operation_activities gmoa
                  ,apps.gmd_operations_b   gmob
             WHERE 1=1
               AND gmor.oprn_line_id = gmoa.oprn_line_id
               AND gmoa.oprn_id      = gmob.oprn_id
               AND UPPER(gmoa.activity) = UPPER(cp_activity)
               AND UPPER(gmob.oprn_no)      = UPPER(cp_oprn_no)
               AND gmob.oprn_vers    = cp_oprn_vers
           ); 
 
  /*   SELECT plan_type
       FROM
           (
               (SELECT CASE
                           WHEN ronum = 1 THEN 'Primary'
                           ELSE 'Auxiliary'     
                        END plan_type
                      , transaction_id
                      , routing_no
                      , oprn_no
                      , activity
                      , resources
                      , oprn_vers
                  FROM
                      (
                       SELECT routing_no
                            , oprn_no
                            , activity
                            , resources
                            , oprn_vers
                            , transaction_id
                            , ROW_NUMBER () OVER (PARTITION BY activity,oprn_no ORDER BY resources) ronum
                         FROM xxblr_opm_routing_stg b
                     ORDER BY  oprn_no
                             , activity
                             , resources
                             , transaction_id
                       )
               )
           ) pln_typ
    WHERE 1=1
      AND transaction_id = cp_transaction_id;
    */      
               
--======================================
--Cursor to get Activity  data
--======================================
      CURSOR lcu_act_rsrc_data (cp_oprn_no VARCHAR2, cp_oprn_vers NUMBER,cp_activity VARCHAR2,cp_resource VARCHAR2)
      IS
        SELECT  COUNT(1) act_rsrc_cnt
          FROM  apps.gmd_operation_resources gmor
              , apps.gmd_operation_activities gmoa
              , apps.gmd_operations_b   gmob
         WHERE  1=1
           AND  gmor.oprn_line_id       =   gmoa.oprn_line_id
           AND  gmoa.oprn_id            =   gmob.oprn_id
           AND  UPPER(gmoa.activity)    =   UPPER(cp_activity)
           AND  UPPER(gmor.resources)   =   UPPER(cp_resource)
           AND  gmob.oprn_no            =   cp_oprn_no
           AND  gmob.oprn_vers          =   cp_oprn_vers;              
          
                
      TYPE per_oprn_type IS TABLE OF lcu_oprn_data%ROWTYPE INDEX BY BINARY_INTEGER;
      lt_oprn_tbl          per_oprn_type;
      lc_prev_activity     VARCHAR2(16) := 'NA';
      lc_prev_oprn         VARCHAR2(16) := 'NA';
         
 BEGIN
      apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD ('*', 80, '*'));
      apps.fnd_file.put_line(apps.fnd_file.LOG, '   --------- Executing VALIDATE_OPRN_DATA ---------');
      apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD (' ', 80, ' '));
      apps.fnd_file.put_line (apps.fnd_file.output, RPAD ('*', 80, '*'));
      apps.fnd_file.put_line(apps.fnd_file.output,'   --------- Executing VALIDATE_OPRN_DATA ---------');
      apps.fnd_file.put_line (apps.fnd_file.output, RPAD (' ', 80, ' '));
    
     /*--  not required -- Commented by srikanth. on 09/04/2013--
      BEGIN
                   OPEN lcu_plan_type_val;
                   loop                 
                      FETCH lcu_plan_type_val
                       INTO  l_paln_type;--,l_transaction_id,l_routing_no, l_oprn_no, l_activity,l_resources,l_,oprn_vers;                      
                    
                      UPDATE xxblr_opm_routing_stg
                         SET plan_type      = l_paln_type
                       WHERE transaction_id = l_transaction_id
                         AND routing_no     = l_routing_no
                         AND oprn_vers      = l_oprn_vers;      -- added
                      EXIT WHEN lcu_plan_type1%NOTFOUND;
                   END loop;
                     COMMIT;
                   CLOSE lcu_plan_type1;
      EXCEPTION
           WHEN OTHERS
           THEN
      apps.fnd_file.put_line(apps.fnd_file.LOG,'NO DATA FOUND FOR PLAN TYPE'||SQLERRM);                                 
      END;
       
        apps.fnd_file.put_line(apps.fnd_file.LOG,' PLAN TYPE'||l_paln_type);
      */
   OPEN lcu_oprn_data (p_validate_operation);
   LOOP
        lt_oprn_tbl.DELETE;

        FETCH lcu_oprn_data BULK COLLECT INTO lt_oprn_tbl LIMIT 50000;        
        l_count :=0;
             
        IF lt_oprn_tbl.count > 0 THEN

             apps.fnd_file.put_line (apps.fnd_file.LOG,'OPRN Loop COUNT:' || lt_oprn_tbl.count);

            FOR i IN lt_oprn_tbl.FIRST .. lt_oprn_tbl.LAST
            LOOP
                 l_count :=l_count+1;         
                
                 apps.fnd_file.put_line (apps.fnd_file.LOG, '*******************');        
                 apps.fnd_file.put_line (apps.fnd_file.LOG, 'Loop lcu_oprn_data - '||l_count);        
                     --===============================
                     -- Applying Transformation rules
                     --===============================
                 gc_error_code                   := NULL;
                 gc_error_message                := NULL;           
                 lt_oprn_tbl (i).error_code      := gc_error_code;
                 lt_oprn_tbl (i).error_message   := gc_error_message;
                
                 apps.fnd_file.put_line (apps.fnd_file.LOG,'Validation for operation no: '||lt_oprn_tbl (i).oprn_no);
                
                 IF lt_oprn_tbl (i).oprn_no IS NULL
                 THEN
                    lt_oprn_tbl (i).record_status    := gc_error_flag;
                    gc_error_code                    := '-PAT04';
                    gc_error_message                 := ',OPERATION NUM IS NULL';
                    lt_oprn_tbl (i).ERROR_CODE       := gc_error_code;
                    lt_oprn_tbl (i).error_message    := gc_error_message;
                 END IF;

                 IF lt_oprn_tbl (i).oprn_vers IS NULL
                 THEN
                  
                    lt_oprn_tbl (i).oprn_vers := 1;
                 END IF;

                 apps.fnd_file.put_line (apps.fnd_file.LOG,'Operation version :'|| lt_oprn_tbl (i).oprn_vers);

                  OPEN lcu_oprn_vers (lt_oprn_tbl (i).oprn_no,lt_oprn_tbl (i).oprn_vers);
                 FETCH lcu_oprn_vers
                  INTO ln_oprn_id;
                 CLOSE lcu_oprn_vers;

                 IF ln_oprn_id IS NOT NULL
                 THEN
                    lt_oprn_tbl (i).record_status    := gc_error_flag;
                    gc_error_code                    := '-PAT04';
                    gc_error_message                 := 'OPERATION NO ALREADY EXISTED ';
                    lt_oprn_tbl (i).error_code       := gc_error_code;
                    lt_oprn_tbl (i).error_message    := gc_error_message;
                 END IF;

                 IF lt_oprn_tbl (i).oprn_desc IS NULL
                 THEN
                    lt_oprn_tbl (i).record_status    := gc_error_flag;
                    gc_error_code                    := '-PAT04';
                    gc_error_message                 := ',OPRN DESC IS NULL ';
                    lt_oprn_tbl (i).error_code       := gc_error_code;
                    lt_oprn_tbl (i).error_message    := gc_error_message;
                 END IF;

                 IF lt_oprn_tbl (i).oper_valid_from IS NULL THEN
                  --                    lt_oprn_tbl (i).record_status    := gc_error_flag;
                  --                    gc_error_code                    := '-PAT04';
                  --                    gc_error_message                 := ',Valid From  is NULL ';
                  --                    lt_oprn_tbl (i).error_code       := gc_error_code;
                  --                    lt_oprn_tbl (i).error_message    := gc_error_message;
                    lt_oprn_tbl (i).oper_valid_from := TO_DATE('01-Jan-2011');
                 END IF;
                
                 IF lt_oprn_tbl (i).owner_org_code IS NULL THEN
                    lt_oprn_tbl (i).record_status    := gc_error_flag;
                    gc_error_code                    := '-PAT04';
                    gc_error_message                 := ',ORG CODE IS NULL ';
                    lt_oprn_tbl (i).error_code       := gc_error_code;
                    lt_oprn_tbl (i).error_message    := gc_error_message;
                    ELSE
                    ln_organization_id :=NULL;
                   
                     OPEN lcu_organization_id(lt_oprn_tbl (i).owner_org_code);
                    FETCH lcu_organization_id
                     INTO ln_organization_id;
                    CLOSE lcu_organization_id;
                   
                    IF ln_organization_id IS NULL THEN
                       lt_oprn_tbl (i).record_status    := gc_error_flag;
                       gc_error_code                    := '-PAT04';
                       gc_error_message                 := ',ORG CODE IS INVALID ';
                       lt_oprn_tbl (i).error_code       := gc_error_code;
                       lt_oprn_tbl (i).error_message    := gc_error_message;                         
                       ELSE
                       lt_oprn_tbl (i).organization_id  := ln_organization_id;
                    END IF;
                 END IF;

                 IF lt_oprn_tbl (i).process_qty_uom IS NULL
                 THEN
                    lt_oprn_tbl (i).record_status    := gc_error_flag;
                    gc_error_code                    := '-PAT04';
                    gc_error_message                 := ',PROCESS QTY UOM IS NULL';
                    lt_oprn_tbl (i).error_code       := gc_error_code;
                    lt_oprn_tbl (i).error_message    := gc_error_message;
                 ELSE
                    lc_process_qty_uom := NULL;

                     OPEN lcu_process_qty_uom (lt_oprn_tbl (i).process_qty_uom);
                    FETCH lcu_process_qty_uom
                     INTO lc_process_qty_uom;
                    CLOSE lcu_process_qty_uom;

                    IF lc_process_qty_uom IS NULL
                    THEN
                       lt_oprn_tbl (i).record_status     := gc_error_flag;
                       gc_error_code                     := '-PAT04';
                       gc_error_message                  := ',PROCESS QTY UOM IS NOT DEFINED';
                       lt_oprn_tbl (i).error_code        := gc_error_code;
                       lt_oprn_tbl (i).error_message     := gc_error_message;
                    ELSE
                       lt_oprn_tbl (i).process_qty_uom := lc_process_qty_uom;
                    END IF;
                 END IF;

                 IF lt_oprn_tbl (i).oprn_class_desc IS NULL
                 THEN
                    lt_oprn_tbl (i).record_status    := gc_error_flag;
                    gc_error_code                    := '-PAT04';
                    gc_error_message                 := ',OPERATION CLASS IS NULL';
                    lt_oprn_tbl (i).ERROR_CODE       := gc_error_code;
                    lt_oprn_tbl (i).error_message    := gc_error_message;
                 ELSE
                     OPEN lcu_oprn_class (lt_oprn_tbl (i).oprn_class_desc);
                    FETCH lcu_oprn_class
                     INTO lc_oprn_class;
                    CLOSE lcu_oprn_class;

                    IF lc_oprn_class IS NULL
                    THEN
                       lt_oprn_tbl (i).record_status := gc_error_flag;
                       gc_error_code                 := '-PAT04';
                       gc_error_message              := 'OPERATION CLASS IS INVALID';
                       lt_oprn_tbl (i).ERROR_CODE    := gc_error_code;
                       lt_oprn_tbl (i).error_message := gc_error_message;
                    ELSE
                       lt_oprn_tbl (i).oprn_class    := lc_oprn_class;
                    END IF;
                 END IF;

                 apps.fnd_file.put_line (apps.fnd_file.LOG,'Track 8:' || lc_process_qty_uom);
                 apps.fnd_file.put_line (apps.fnd_file.LOG, 'Validation for Activity');

                 IF lt_oprn_tbl (i).activity IS NULL
                 THEN
                    lt_oprn_tbl (i).record_status   := gc_error_flag;
                    gc_error_code                  := '-PAT04';
                    gc_error_message               := ',ACTIVITY IS NULL ';
                    lt_oprn_tbl (i).error_code     := gc_error_code;
                    lt_oprn_tbl (i).error_message  := gc_error_message;
                 ELSE
                     OPEN lcu_act_data (lt_oprn_tbl (i).activity);
                    FETCH lcu_act_data
                     INTO lc_activity_data;
                    CLOSE lcu_act_data;

                    IF lc_activity_data IS NULL
                    THEN
                       lt_oprn_tbl (i).record_status    := gc_error_flag;
                       gc_error_code                   := '-PAT04';
                       gc_error_message                := ',ACTIVITY IS INVALID ';
                       lt_oprn_tbl (i).error_code      := gc_error_code;
                       lt_oprn_tbl (i).error_message   := gc_error_message;
                    ELSE
                       lt_oprn_tbl (i).activity         := lc_activity_data;
                    END IF;
                 END IF;

                  apps.fnd_file.put_line (apps.fnd_file.LOG,'Track 9:'|| 'Calling Detail Loop');
                  apps.fnd_file.put_line (apps.fnd_file.LOG,'Track 9:'|| 'Validation Operating =   '|| p_validate_operation);
                  apps.fnd_file.put_line (apps.fnd_file.LOG,'Track 9:'|| 'Oprn No  =   ');
                  apps.fnd_file.put_line (apps.fnd_file.LOG,'Track 9:'|| 'Oprn Activity    =   '|| lt_oprn_tbl (i).activity);
                              
                  apps.fnd_file.put_line (apps.fnd_file.LOG,'Validation for Resource'||lt_oprn_tbl (i).resource_code);

                  IF lt_oprn_tbl (i).resource_code IS NULL THEN
                     lt_oprn_tbl (i).record_status    := gc_error_flag;
                     gc_error_code                    := '-PAT04';
                     gc_error_message                 := ',RESOURCE IS NULL ';
                     lt_oprn_tbl (i).error_code       := gc_error_code;
                     lt_oprn_tbl (i).error_message    := gc_error_message;
                  ELSE
                     lc_resource := NULL;

                      OPEN lcu_resource (lt_oprn_tbl (i).resource_code);
                     FETCH lcu_resource
                      INTO lc_resource;
                     CLOSE lcu_resource;

                     IF lc_resource IS NULL THEN
                        lt_oprn_tbl (i).record_status  := gc_error_flag;
                        gc_error_code                  := '-PAT04';
                        gc_error_message               := ',RESOURCE IS NOT DEFINED ';
                        lt_oprn_tbl (i).ERROR_CODE     := gc_error_code;
                        lt_oprn_tbl (i).error_message  :=gc_error_message;
                     ELSE
                        lt_oprn_tbl (i).resource_code  := lc_resource;
                     END IF;

                     apps.fnd_file.put_line (apps.fnd_file.LOG,' Resource' || lc_resource);
                  END IF;

                  -----------------            
                 apps.fnd_file.put_line (apps.fnd_file.LOG,'Track 9_A: Validation for Activity '||lt_oprn_tbl (i).activity||' & '||lt_oprn_tbl (i).resources);

                  IF lt_oprn_tbl (i).resources IS NULL OR lt_oprn_tbl (i).activity IS NULL THEN
                     apps.fnd_file.put_line (apps.fnd_file.LOG,'Track 9_A.1');
                     lt_oprn_tbl (i).record_status    := gc_error_flag;
                     gc_error_code                    := '-PAT04';
                     gc_error_message                 := ',RESOURCE OR ACTIVITY IS NULL ';
                     lt_oprn_tbl (i).error_code       := gc_error_code;
                     lt_oprn_tbl (i).error_message    := gc_error_message;
                     apps.fnd_file.put_line (apps.fnd_file.LOG,'Track 9_B ');
                  ELSE
                     lc_act_rsrce := NULL;
                     apps.fnd_file.put_line (apps.fnd_file.LOG,'Track 9_C');

                   --  OPEN lcu_act_rsrc_data (lt_oprn_tbl (i).oprn_no,lt_oprn_tbl (i).oprn_vers,lt_oprn_tbl (i).activity,lt_oprn_tbl (i).resources);
                   --  FETCH lcu_act_rsrc_data INTO lc_act_rsrce;
                   --  CLOSE lcu_act_rsrc_data;
                    
                    SELECT  COUNT(1) --gmor.oprn_line_id
                      INTO  lc_act_rsrce
                      FROM  apps.gmd_operation_resources  gmor
                          , apps.gmd_operation_activities gmoa
                          , apps.gmd_operations_b         gmob
                     WHERE  1=1
                       AND  gmor.oprn_line_id     =  gmoa.oprn_line_id
                       AND  gmoa.oprn_id          =  gmob.oprn_id
                       AND  UPPER(gmoa.activity)  =  UPPER(lt_oprn_tbl (i).activity)
                       AND  UPPER(gmor.resources) =  UPPER(lt_oprn_tbl (i).resources)
                       AND  gmob.oprn_no          =  lt_oprn_tbl (i).oprn_no
                       AND  gmob.oprn_vers        =  lt_oprn_tbl (i).oprn_vers;              

                     IF lc_act_rsrce != 0 THEN
                        lt_oprn_tbl (i).record_status  := gc_error_flag;
                        gc_error_code                  := '-PAT04';
                        gc_error_message               := ',RESOURCE ALREADY EXISTS';
                        lt_oprn_tbl (i).ERROR_CODE     := gc_error_code;
                        lt_oprn_tbl (i).error_message  := gc_error_message;
                     ELSE
                        lc_act_rsrce := NULL;                     
                     END IF;
                  END IF;
                 
                  ------------------

                  IF lt_oprn_tbl (i).scale_type IS NULL THEN
                     lt_oprn_tbl (i).record_status := gc_error_flag;
                     gc_error_code                 := '-PAT04';
                     gc_error_message              := ',SCALE TYPE IS NULL ';
                     lt_oprn_tbl (i).ERROR_CODE    := gc_error_code;
                     lt_oprn_tbl (i).error_message := gc_error_message;
                  ELSE
                     ln_scale_type := NULL;
                     
                      OPEN lcu_scale_type (lt_oprn_tbl (i).scale_type);
                     FETCH lcu_scale_type
                      INTO ln_scale_type;
                     CLOSE lcu_scale_type;

                     IF ln_scale_type IS NULL THEN
                        lt_oprn_tbl (i).record_status := gc_error_flag;
                        gc_error_code                 := '-PAT04';
                        gc_error_message              := ',SCALE TYPE INVALID ';
                        lt_oprn_tbl (i).error_code    := gc_error_code;
                        lt_oprn_tbl (i).error_message := gc_error_message;
                     ELSE
                        lt_oprn_tbl (i).scale_type    := ln_scale_type;
                     END IF;
                  END IF;

                  /*
                  IF lt_oprn_tbl (i).plan_type IS NULL THEN
                     lt_oprn_tbl (i).record_status    := gc_error_flag;
                     gc_error_code                    := '-PAT04';
                     gc_error_message                 := ',PLAN TYPE IS NULL';
                     lt_oprn_tbl (i).error_code       := gc_error_code;
                     lt_oprn_tbl (i).error_message    := gc_error_message;
                  ELSE                                                        */
                  IF lt_oprn_tbl (i).plan_type IS NOT NULL THEN
                     OPEN lcu_plan_type (lt_oprn_tbl (i).plan_type);
                     FETCH lcu_plan_type INTO ln_plan_type;
                     CLOSE lcu_plan_type;

                     IF ln_plan_type IS NULL THEN
                        lt_oprn_tbl (i).record_status  := gc_error_flag;
                        gc_error_code                  := '-PAT04';
                        gc_error_message               := ',PLAN TYPE IS INVALID ';
                        lt_oprn_tbl (i).ERROR_CODE     := gc_error_code;
                        lt_oprn_tbl (i).error_message  := gc_error_message;
                     END IF;

                  ELSE             ---- 

                     --commented by Srikanth on 25-Apr-2013
                     --OPEN lcu_plan_type_val(lt_oprn_tbl (i).oprn_no,lt_oprn_tbl (i).oprn_vers,lt_oprn_tbl (i).activity);--(lt_oprn_tbl (i).transaction_id);
                     --FETCH lcu_plan_type_val INTO ln_plan_type_val;
                     --CLOSE lcu_plan_type_val;
                     --lt_oprn_tbl (i).plan_type       := ln_plan_type_val;                       
                     if lc_prev_activity = lt_oprn_tbl (i).activity AND lc_prev_oprn = lt_oprn_tbl (i).oprn_no then
                        lt_oprn_tbl (i).plan_type := 'Auxiliary';
                     else
                        lt_oprn_tbl (i).plan_type := 'Primary';
                     end if;
                     lc_prev_activity := lt_oprn_tbl (i).activity;
                     lc_prev_oprn     := lt_oprn_tbl (i).oprn_no;
                     /*
                     BEGIN
                        SELECT 'Auxilary'
                        INTO   lt_oprn_tbl (i).plan_type
                        from   dual
                        where  exists ( Select 1 from xxblr_opm_routing_stg
                                          where  new_oprn_no = lt_oprn_tbl (i).oprn_no
                                          and    oprn_vers   = lt_oprn_tbl (i).oprn_vers
                                          and    activity    = lt_oprn_tbl (i).activity
                                          and    plan_type   = 'Primary');
                     EXCEPTION
                        WHEN OTHERS THEN
                           lt_oprn_tbl (i).plan_type := 'Primary';
                     END;
                     */
                          
                  END IF;          ----

                  IF lt_oprn_tbl (i).usage_uom IS NULL THEN
                     lt_oprn_tbl (i).record_status     := gc_error_flag;
                     gc_error_code                     := '-PAT04';
                     gc_error_message                  := ',USAGE UOM IS NULL ';
                     lt_oprn_tbl (i).error_code        := gc_error_code;
                     lt_oprn_tbl (i).error_message     := gc_error_message;
                  ELSE
                     lc_usage_uom := NULL;

                      OPEN lcu_rers_usage_uom (lt_oprn_tbl (i).usage_uom);
                     FETCH lcu_rers_usage_uom
                      INTO lc_usage_uom;
                     CLOSE lcu_rers_usage_uom;

                     IF lc_usage_uom IS NULL THEN
                        lt_oprn_tbl (i).record_status  := gc_error_flag;
                        gc_error_code                  := '-PAT04';
                        gc_error_message               := ',USAGE UOM IS INVALID ';
                        lt_oprn_tbl (i).error_code     := gc_error_code;
                        lt_oprn_tbl (i).error_message  := gc_error_message;
                     ELSE
                        lt_oprn_tbl (i).usage_uom      := lc_usage_uom;
                     END IF;
                  END IF;

                  apps.fnd_file.put_line (apps.fnd_file.LOG, 'TRACK 8');
                  /*
                  IF lt_oprn_tbl (i).cost_analysis_code IS NOT NULL THEN
                     --lt_oprn_tbl (i).record_status    := gc_error_flag;
                     --gc_error_code                    := '-PAT04';
                     --gc_error_message                 :=',COST ANALYSIS CODE  IS NULL ';
                     --lt_oprn_tbl (i).ERROR_CODE       := gc_error_code;
                     --lt_oprn_tbl (i).error_message    := gc_error_message;
                  --ELSE                     
                     lc_cost_alys_code := NULL;

                      OPEN lcu_cost_alys_code(lt_oprn_tbl (i).cost_analysis_code);
                     FETCH lcu_cost_alys_code
                      INTO lc_cost_alys_code;
                     CLOSE lcu_cost_alys_code;

                     IF lc_cost_alys_code IS NULL THEN
                        lt_oprn_tbl (i).record_status  := gc_error_flag;
                        gc_error_code                  := '-PAT04';
                        gc_error_message               :=',COST ANALYSIS CODE IS INVALID ';
                        lt_oprn_tbl (i).ERROR_CODE     := gc_error_code;
                        lt_oprn_tbl (i).error_message  := gc_error_message;
                     ELSE
                        lt_oprn_tbl (i).cost_analysis_code :=lc_cost_alys_code;
                     END IF;
                  END IF;
                  */
                  lt_oprn_tbl (i).cost_analysis_code := 'DIR';

                  apps.fnd_file.put_line(apps.fnd_file.LOG,'Track 9:'|| lt_oprn_tbl (i).cost_analysis_code);

                  IF lt_oprn_tbl (i).resource_usage IS NULL THEN
                     lt_oprn_tbl (i).record_status    := gc_error_flag;
                     gc_error_code                    := '-PAT04';
                     gc_error_message                 := ',RESOURCE USAGE IS NULL ';
                     lt_oprn_tbl (i).error_code       := gc_error_code;
                     lt_oprn_tbl (i).error_message    := gc_error_message;
                  END IF;

                  apps.fnd_file.put_line (apps.fnd_file.LOG,'Track 10:'|| lt_oprn_tbl (i).resource_usage);
                  apps.fnd_file.put_line (apps.fnd_file.LOG,'Track 12:');                                                             
         
                   --=================================
                    -- Identifying the success records
                   --=================================
               IF lt_oprn_tbl (i).record_status <> gc_error_flag THEN
                  lt_oprn_tbl (i).record_status := gc_validation_flag;
                  lt_oprn_tbl (i).error_message := 'All Validations Passed';
               ELSE
                  lt_oprn_tbl (i).record_status := gc_validation_error_flag;
               END IF;
         
    
            END LOOP;             
        END IF;
                      --===================================================================
                      --Bulk Update the records withe status flag, validation error message
                      --===================================================================                               
                     apps.fnd_file.put_line (apps.fnd_file.LOG,'Track 10:' || 'Debug-10');
                     apps.fnd_file.put_line (apps.fnd_file.LOG, 'track 11:' || 'debug-11');
      
                   ln_bulk_error_cnt := 0;
                   apps.fnd_file.put_line (apps.fnd_file.LOG,'Track 9:' || 'Debug-9');

                  BEGIN
                    
                     FORALL i IN lt_oprn_tbl.FIRST .. lt_oprn_tbl.LAST SAVE EXCEPTIONS
                     
                       UPDATE  xxblr_opm_routing_stg
                          SET  record_status      =     lt_oprn_tbl (i).record_status
                             , error_message      =     lt_oprn_tbl (i).error_message
                             , error_code         =     lt_oprn_tbl (i).error_code
                             , oprn_vers          =     lt_oprn_tbl (i).oprn_vers
                             , oprn_class         =     lt_oprn_tbl (i).oprn_class
                             , activity           =     lt_oprn_tbl (i).activity
                             , resource_code      =     lt_oprn_tbl (i).resource_code
                             , usage_uom          =     lt_oprn_tbl (i).usage_uom                            
                             , scale_type         =     lt_oprn_tbl (i).scale_type
                             , plan_type          =     lt_oprn_tbl (i).plan_type
                             , cost_analysis_code =     lt_oprn_tbl (i).cost_analysis_code
                             , process_qty_uom    =     lt_oprn_tbl (i).process_qty_uom
                             , organization_id    =     lt_oprn_tbl (i).organization_id
                             , last_update_date   =     gd_current_date
                             , last_updated_by    =     gn_user_id
                             , last_update_login  =     gn_login_id
                        WHERE  transaction_id     =     lt_oprn_tbl (i).transaction_id;
                    
                       COMMIT;
                 
                  EXCEPTION
                     WHEN OTHERS
                     THEN
                        apps.fnd_file.put_line(apps.fnd_file.LOG,'   Bulk Update Error Message :-> '|| SQLERRM);
                        ln_bulk_error_cnt := SQL%BULK_EXCEPTIONS.COUNT;

                        FOR i IN 1 .. ln_bulk_error_cnt
                        LOOP
                           apps.fnd_file.put_line(apps.fnd_file.LOG,'Error# '|| i|| ' at iteration# '|| SQL%BULK_EXCEPTIONS (i).ERROR_INDEX);
                           apps.fnd_file.put_line(apps.fnd_file.LOG,'Error Message is '|| SQLERRM(-SQL%BULK_EXCEPTIONS (i).ERROR_CODE));
                        END LOOP;                  
                  END;
                 apps.fnd_file.put_line (apps.fnd_file.LOG,'Track 10:' || 'DebugT-10');   

   EXIT WHEN lcu_oprn_data%NOTFOUND;
   END LOOP;
   apps.fnd_file.put_line (apps.fnd_file.LOG, 'track 12:' || 'debug-12');
   CLOSE lcu_oprn_data;
 
 EXCEPTION
    WHEN OTHERS THEN
       apps.fnd_file.put_line (apps.fnd_file.LOG,'MAIN-ERR-VLD-OPRN :> ' || SQLERRM || ', ' || SQLCODE);
       apps.fnd_file.put_line(apps.fnd_file.LOG,'   --------- Procedure VALIDATE_OPRN_DATA Exit ---------');
       apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD (' ', 80, ' '));
       apps.fnd_file.put_line (apps.fnd_file.LOG, RPAD ('*', 80, '*'));
 END validate_oprn_data;

Accounts Receivable-AR setups in Oracle EBS Functional Financials - Apps R12

   1. Oracle Receivables -> Setup -> System ->System Options     a. Receivable GL related setup    b. Transaction and Customer rela...