Item Category Conversion Categoriesare the method …

Item Category Conversion
Categories
are the method by which the items in inventory can be separated logically and
functionally for planning, purchasing and other activities.You can use
categories and category sets to group your items for various reports and
programs. A category is a logical classification of items that have similar
characteristics. A category set is a distinct grouping scheme and consists of
categories. The flexibility of category sets allows you to report and inquire
on items in a way that best suits your needs. This article will describe how to
create categories and category set in oracle inventory.
Step1: Sample script to fetch the Item
Category Conversion detail is given below.
PROCEDURE item_category_conv (errbut      
OUT VARCHAR2
                             ,retcode  
   
OUT NUMBER
                             ,p_org_id    
IN  NUMBER
                             ,p_log_level 
IN  NUMBER
                             )
IS
   l_category_rec    INV_ITEM_CATEGORY_PUB.category_rec_type;  
   l_api_version     NUMBER;
   o_return_status   VARCHAR2(10);
   o_msg_count       NUMBER;
   o_msg_data        VARCHAR2(20000);
   o_errorcode       VARCHAR2(20000);
   l_error_code      VARCHAR2(20000);
   l_error_desc      VARCHAR2(20000);
   l_conv_status     VARCHAR2(20000);
   v_category_id     NUMBER;   
   l_count           NUMBER;
   l_err_count       NUMBER;
   ln_count          NUMBER;
   x_msg             VARCHAR2(20000);
   lc_status_flag    VARCHAR2(1);
   –l_msg_data       
VARCHAR2(20000);
CURSOR CAT_STRUCTURE_CUR
IS
 SELECT structure_id, category_set_name
   FROM mtl_category_sets_vl
  WHERE category_set_name IN (SELECT DISTINCT category_type
                                    FROM xx_cnv_item_category
                                  WHERE NVL(x_record_status,‘X’) != ‘IAS’
                                   )                          
 ORDER BY 1;
CURSOR CAT_CUR(p_cat_type 
VARCHAR2)
IS
 SELECT DISTINCT ROWID,category_type,
        (UPPER(TRIM(cat_segment1))) cat_segment1,
        UPPER((TRIM(cat_segment2))) cat_segment2,
        ((TRIM(cat_segment3))) cat_segment3,
        UPPER((TRIM(cat_segment4))) cat_segment4,
        UPPER((TRIM(cat_segment5))) cat_segment5,
        UPPER((TRIM(cat_segment6))) cat_segment6,
        UPPER((TRIM(cat_segment7))) cat_segment7,
        UPPER((TRIM(cat_segment8))) cat_segment8,
        UPPER((TRIM(cat_segment9))) cat_segment9,
        UPPER((TRIM(cat_segment10))) cat_segment10
   FROM xx_CNV_ITEM_CATEGORY
  WHERE category_type = p_cat_type
    AND NVL(x_record_status,‘X’) != ‘IAS’
  ORDER BY 1;
BEGIN
    fnd_global.apps_initialize ( user_id     
=> gn_user_id        —1 — ANONYMOUS
                                ,resp_id     
=> gn_resp_id     –31175 — W (BG)_Inventory  — 50791
                                ,resp_appl_id => gn_resp_appl_id   –401); — Inventory
                                );                               
    mo_global.init (‘INV’);
    gn_inv_org_id := p_org_id;
   
    BEGIN
    SELECT operating_unit
      INTO gn_org_id
      FROM
org_organization_definitions
     WHERE organization_id = p_org_id;
    EXCEPTION WHEN OTHERS
    THEN
     gn_org_id := 82;
    END;
   
    IF p_log_level IS NOT NULL THEN
       g_log_level := p_log_level;
    END IF;
   
    –fnd_file.put_line
(fnd_file.LOG,’g_log_level->’||g_log_level);
    /* Setting the org context for the particular
session */
    mo_global.set_policy_context(‘S’,gn_org_id);
    –fnd_client_info.set_org_context
(gn_org_id);
    LOG(1,‘Setting the Context’);
   
    fnd_file.put_line (fnd_file.LOG,‘gn_org_id->’||gn_org_id);
   
    l_count := 0;
    l_err_count := 0;
    FOR lcr_cat_structure_rec IN cat_structure_cur
    LOOP       
        FOR lcr_cat_rec IN cat_cur (lcr_cat_structure_rec.category_set_name)
        LOOP
          x_msg := NULL;
          lc_status_flag := ‘S’;
         
          IF LENGTH(LCR_CAT_REC.cat_segment1)>40 OR LENGTH(LCR_CAT_REC.cat_segment2)>40 OR LENGTH(LCR_CAT_REC.cat_segment3)>40 OR LENGTH(LCR_CAT_REC.cat_segment4)>40
             OR LENGTH(LCR_CAT_REC.cat_segment5)>40 OR LENGTH(LCR_CAT_REC.cat_segment6)>40 OR LENGTH(LCR_CAT_REC.cat_segment7)>40
          THEN
          lc_status_flag := ‘E’;
          x_msg := ‘Segment values should not
exceed 40 Char’
;
          END IF;
         
          IF  lc_status_flag != ‘E’
          THEN
           
            ln_count := 0;        
            BEGIN
           
             SELECT count(1)
               INTO ln_count
               FROM mtl_category_sets_vl MCS                   
                   ,mtl_categories_v MC 
              WHERE MCS.structure_id                = MC.structure_id                
                AND MCS.category_set_name           = LCR_CAT_REC.category_type
                AND UPPER(MC.segment1)              = UPPER(LCR_CAT_REC.cat_segment1)        
                AND UPPER(MC.segment2)              = UPPER(LCR_CAT_REC.cat_segment2)       
                AND UPPER(NVL(MC.segment3,‘X’))     = UPPER(NVL(LCR_CAT_REC.cat_segment3,‘X’))
                AND UPPER(NVL(MC.segment4,‘X’))     = UPPER(NVL(LCR_CAT_REC.cat_segment4,‘X’))
                AND UPPER(NVL(MC.segment5,‘X’))     = UPPER(NVL(LCR_CAT_REC.cat_segment5,‘X’))
                AND UPPER(NVL(MC.segment6,‘X’))     = UPPER(NVL(LCR_CAT_REC.cat_segment6,‘X’))
                AND UPPER(NVL(MC.segment7,‘X’))     = UPPER(NVL(LCR_CAT_REC.cat_segment7,‘X’));
            END;                                   
                           
            IF ln_count = 0
            THEN
               l_category_rec                := NULL;              
               l_category_rec.structure_id  
:= lcr_cat_structure_rec.structure_id;
            — l_category_rec.structure_code :=
g_cat_flex_code;
               l_category_rec.summary_flag  
:= ‘N’;
               l_category_rec.enabled_flag  
:= ‘Y’;
               l_category_rec.segment1      
:= lcr_cat_rec.cat_segment1;
               l_category_rec.segment2      
:= lcr_cat_rec.cat_segment2;
               l_category_rec.segment3      
:= lcr_cat_rec.cat_segment3;
               l_category_rec.segment4      
:= lcr_cat_rec.cat_segment4;
               l_category_rec.segment5      
:= lcr_cat_rec.cat_segment5;
               l_category_rec.segment6      
:= lcr_cat_rec.cat_segment6;
               l_category_rec.segment7      
:= lcr_cat_rec.cat_segment7;
               l_category_rec.segment8      
:= lcr_cat_rec.cat_segment8;
               l_category_rec.segment9      
:= lcr_cat_rec.cat_segment9;
               l_category_rec.segment10     
:= lcr_cat_rec.cat_segment10;
               
               INV_ITEM_CATEGORY_PUB.create_category (p_api_version     => 1.0
                                                   
, p_init_msg_list   => fnd_api.g_TRUE
                                                   
, p_commit          => fnd_api.g_true
                                                   
, x_return_status   => o_return_status
                                                   
, x_errorcode       => o_errorcode
                                                   
, x_msg_count       => o_msg_count
                                                   
, x_msg_data  
     
=> o_msg_data
                                                   
, p_category_rec    => l_category_rec
                                                   
, x_category_id     => v_category_id);
              
                IF o_return_status = ‘S’
                THEN   
                    l_count := l_count + 1;                      
                     
                    UPDATE xx_cnv_item_category
                       SET x_record_status = ‘ICS’
                          ,x_record_msg = ‘Category Creation
Successful’
                     WHERE rowid = lcr_cat_rec.rowid;
                                           
                ELSE                   
                     l_err_count := l_err_count + 1;                     
                     FOR i IN 1 .. fnd_msg_pub.count_msg
                     LOOP
                        x_msg := x_msg || fnd_msg_pub.get (p_msg_index      => i,
                                              
p_encoded       
=> fnd_api.g_false
                                              );
                     END LOOP;
                    fnd_file.put_line (fnd_file.LOG,  ‘API
error :’
||LCR_CAT_REC.category_type||‘~~’||LCR_CAT_REC.CAT_SEGMENT1||‘~~’||LCR_CAT_REC.CAT_SEGMENT2||‘~~’                                  
                                        ||
x_msg
                                       );
                     
                   UPDATE xx_cnv_item_category
                      SET x_record_status = ‘ICE’
                         ,x_record_msg = x_msg
                    WHERE rowid = lcr_cat_rec.rowid;
                                        
                END IF;      
            ELSE
               UPDATE xx_cnv_item_category
                  SET x_record_status = ‘ICS’
                     ,x_record_msg = ‘Category Already Exists’
                WHERE rowid = lcr_cat_rec.rowid;
            END IF;  
           
          ELSE
            UPDATE xx_cnv_item_category
               SET x_record_status = ‘ICE’
                  ,x_record_msg = x_msg
             WHERE rowid = lcr_cat_rec.rowid;
          END IF;                                                             
                                                
        END LOOP;                                                     
    END LOOP;
   
    COMMIT;
   
    LOG(1,‘Valid Count: ‘||l_count);
    LOG(1,‘Error Count: ‘||l_err_count);   
    LOG(1,‘*** Calling Assignment Procedure’);
    item_cat_assign_proc;
   
EXCEPTION
WHEN OTHERS
THEN       
    retcode := 1;
    LOG(1,‘Unexpected error @ item_category_conv ->
||SQLERRM);   
END;
PROCEDURE ITEM_CAT_ASSIGN_PROC
 IS
  l_template_id       mtl_item_templates.template_id%TYPE;
  l_locator_id        mtl_item_locations_kfv.inventory_location_id%TYPE;
  l_locator           mtl_item_locations_kfv.concatenated_segments%TYPE;
  l_asset_category    fa_categories_b_kfv.category_id%TYPE;
  l_item_type         fnd_lookup_values.lookup_code%TYPE;
  l_mrp_plan_code     fnd_lookup_values.lookup_code%TYPE;
  l_wip_type          fnd_lookup_values.lookup_code%TYPE;
  l_forecast_cntr     fnd_lookup_values.lookup_code%TYPE;
  l_inventory_plan    fnd_lookup_values.lookup_code%TYPE;
  l_assembly_pegg     fnd_lookup_values.lookup_code%TYPE;
  l_planning_time     fnd_lookup_values.lookup_code%TYPE;
  l_demand_time       fnd_lookup_values.lookup_code%TYPE;
  l_release_time      fnd_lookup_values.lookup_code%TYPE;
  l_bom_type          fnd_lookup_values.lookup_code%TYPE;
  l_reduce_mps        fnd_lookup_values.lookup_code%TYPE;
  l_overcomp_tol      fnd_lookup_values.lookup_code%TYPE;
  l_locator_cntr      fnd_lookup_values.lookup_code%TYPE;
  l_loc_cntr          fnd_lookup_values.lookup_code%TYPE;
  l_lot_shelf         fnd_lookup_values.lookup_code%TYPE;
  l_serv_code         fnd_lookup_values.lookup_code%TYPE;
  l_temp              fnd_lookup_values.lookup_code%TYPE;
  l_pln_time          fnd_lookup_values.meaning%TYPE;
  l_dem_time          fnd_lookup_values.meaning%TYPE;
  l_rel_time          fnd_lookup_values.meaning%TYPE;
  l_bom               fnd_lookup_values.meaning%TYPE;
  l_map_locator       fnd_lookup_values.meaning%TYPE;
  l_un_number         po_un_numbers_tl.un_number_id%TYPE;
  l_hazard_id         po_hazard_classes_tl.hazard_class_id%TYPE;
  l_wip_sub           VARCHAR2 (1000);
  l_so_sub            VARCHAR2 (1000);
  l_wip               fnd_lookup_values.meaning%TYPE;
  l_pick_rule         mtl_picking_rules.picking_rule_id%TYPE;
  l_pick_rl           mtl_picking_rules.picking_rule_name%TYPE;
  l_request_id        fnd_concurrent_requests.request_id%TYPE;
  l_atp_rule          mtl_atp_rules.rule_id%TYPE;
  l_atp_rl            mtl_atp_rules.rule_name%TYPE;
  l_payment_term      ra_terms_tl.term_id%TYPE;
  l_pay_tr            ra_terms_tl.NAME%TYPE;
  l_destination_org   mtl_parameters.organization_code%TYPE;
  l_inv_org           mtl_parameters.organization_code%TYPE;
  l_buyer_id          po_agents_v.agent_id%TYPE;
  l_forecast          VARCHAR2 (1000);
  l_asm_pegging       VARCHAR2 (1000);
  l_master_org        mtl_parameters.organization_code%TYPE;
  l_org_code          mtl_parameters.organization_code%TYPE;
  l_mast_org_id       mtl_parameters.organization_id%TYPE;
  l_org_id            mtl_parameters.organization_id%TYPE;
  l_buyer             VARCHAR2 (100);
  l_user_id           fnd_user.user_id%TYPE;
  l_temp_name         mtl_item_templates.description%TYPE;
  l_item_typ          fnd_lookup_values.meaning%TYPE;
  l_tax_code          ap_tax_codes_all.NAME%TYPE;
  l_tax_name          ap_tax_codes_all.NAME%TYPE;
  l_uom_code          mtl_units_of_measure.unit_of_measure%TYPE;
  l_uom_name          mtl_units_of_measure.unit_of_measure%TYPE;
  l_cover_temp        oks_coverages_v.NAME%TYPE;
  l_coverage_name     oks_coverages_v.NAME%TYPE;
  l_coverage_id       oks_coverages_v.ID%TYPE;
  l_revision          mtl_item_revisions_interface.revision%TYPE;
  l_volume_uom        mtl_units_of_measure.uom_code%TYPE;
  l_weight_uom        mtl_units_of_measure.uom_code%TYPE;
  l_planner           mtl_planners.planner_code%TYPE;
  l_exception_set     mtl_system_items_interface.planning_exception_set%TYPE;
  l_source_org        mtl_parameters.organization_code%TYPE;
  l_record_count      NUMBER;
  l_commit_count      NUMBER;
  l_error_count       NUMBER;
  l_prt_item_exist    NUMBER;
  l_duplicate_item    NUMBER                                             := 0;
  l_item_exists       NUMBER                                             := 0;
  l_err_msg           VARCHAR2 (2000);
  l_conv_id           NUMBER;
  l_val_count         NUMBER                                             := 0;
  l_item_count        NUMBER; 
  l_error_message     VARCHAR2 (2000);
  l_process_id        mtl_system_items_interface.set_process_id%TYPE;
  l_count             NUMBER                                             := 0;
  l_org               mtl_parameters.organization_id%TYPE;
  l_source            VARCHAR2 (2);
  l_item_cnt          NUMBER                                             := 0;
  l_valerr_count      NUMBER                                             := 0;
  l_status            VARCHAR2 (1);
  ——
  l_inv_item_id       NUMBER;   
  l_struc_id          NUMBER;
  l_cat_set_id        NUMBER;
  l_cat_id            NUMBER;
  l_trx_type          VARCHAR2(30);
  l_old_cat_id        NUMBER;
   
  CURSOR item_cat_cur
  IS
  SELECT a.*, ROWID
  FROM xx_cnv_item_category a
  WHERE NVL(x_record_status,‘X’) = ‘ICS’;
    
 BEGIN
  LOG(1,‘Start Item Category Assignment Valiation’);
  l_record_count := 0;
  l_commit_count := 0;
  l_error_count := 0;
  l_error_message := NULL;
  l_err_msg := NULL;
  l_val_count := 0;
  l_valerr_count := 0;
  l_status := ‘S’;         
   
    DELETE FROM mtl_item_categories_interface WHERE set_process_id = 1;
    COMMIT;
        
    FOR lcr_cat_rec IN item_cat_cur
    LOOP
     l_err_msg      := NULL;
     l_struc_id     := NULL;
     l_cat_set_id   := NULL;
     l_cat_id       := NULL;
     l_inv_item_id  := NULL;
     l_old_cat_id   := NULL;
     l_item_count   := NULL;
     — 
Validating Category set name
     LOG(5,‘Validating Category Name ‘);
     IF (LCR_CAT_REC.category_type IS NULL)
     THEN
       l_err_msg := l_err_msg || ‘Category
Type For An Item Cant Be Null, ‘
;
     ELSE            
       BEGIN   
           SELECT structure_id,category_set_id
             INTO l_struc_id,l_cat_set_id
             FROM mtl_category_sets_vl
            WHERE UPPER(category_set_name) = UPPER(lcr_cat_rec.category_type);      
       EXCEPTION
       WHEN OTHERS
       THEN
          l_err_msg := l_err_msg || ‘Could
not find Category, ‘
;    
       END;        
     END IF; 
    
—     —  Validating Category Set
—     LOG(5,’Validating Category
Set’);    
—     BEGIN   
—     SELECT mcs.category_set_id
—       INTO l_cat_set_id
—       FROM mtl_category_sets
mcs
—           ,mtl_category_sets_tl
mcst
—      WHERE mcs.category_set_id
= mcst.category_set_id
—        AND
mcs.structure_id      = l_struc_id;        
—     EXCEPTION
—         WHEN OTHERS
—         THEN
—            l_err_msg :=
l_err_msg || ‘Could not find Category Set ID, 
‘;    
—     END;           
      
     IF UPPER(LCR_CAT_REC.category_type) = ‘FORECAST_CATEGORY’
     THEN       
         BEGIN              
         SELECT category_id
           INTO l_cat_id
           FROM mtl_categories_b
          WHERE structure_id             = l_struc_id
            AND UPPER(segment1)         
= UPPER(TRIM(LCR_CAT_REC.cat_segment1))
            AND UPPER(segment2)         
= UPPER(TRIM(LCR_CAT_REC.cat_segment2))
            AND UPPER(NVL(segment3,‘X’)) = UPPER(TRIM(NVL(LCR_CAT_REC.cat_segment3,‘X’)))
            AND UPPER(NVL(segment4,‘X’)) = UPPER(TRIM(NVL(LCR_CAT_REC.cat_segment4,‘X’)))
            AND UPPER(NVL(segment5,‘X’)) = UPPER(TRIM(NVL(LCR_CAT_REC.cat_segment5,‘X’)))
            AND UPPER(NVL(segment6,‘X’)) = UPPER(TRIM(NVL(LCR_CAT_REC.cat_segment6,‘X’)))
            AND UPPER(NVL(segment7,‘X’)) = UPPER(TRIM(NVL(LCR_CAT_REC.cat_segment7,‘X’)));    
         EXCEPTION
         WHEN OTHERS
         THEN
           l_err_msg := l_err_msg || ‘Cound
not get Category ID, ‘
;           
         END;   
     ELSIF  UPPER(LCR_CAT_REC.category_type) = ‘REPORTING_CATEGORY’
     THEN
         BEGIN              
         SELECT category_id
           INTO l_cat_id
           FROM mtl_categories_b
          WHERE structure_id    = l_struc_id
            AND UPPER(segment1) = UPPER(TRIM(LCR_CAT_REC.cat_segment1))
            AND UPPER(segment2) = UPPER(TRIM(LCR_CAT_REC.cat_segment2));    
         EXCEPTION
         WHEN OTHERS
         THEN
           l_err_msg := l_err_msg || ‘Cound
not get Category ID, ‘
;           
         END;            
     ELSE
         BEGIN              
         SELECT category_id
           INTO l_cat_id
           FROM mtl_categories_b
          WHERE structure_id             = l_struc_id
            AND UPPER(segment1)         
= UPPER(TRIM(LCR_CAT_REC.cat_segment1))
            AND UPPER(segment2)         
= UPPER(TRIM(LCR_CAT_REC.cat_segment2))
            AND UPPER(NVL(segment3,‘X’)) = UPPER(NVL(TRIM(LCR_CAT_REC.cat_segment3),‘X’));
         EXCEPTION
         WHEN OTHERS
         THEN
           l_err_msg := l_err_msg || ‘Cound
not get Category ID, ‘
;           
         END;   
     END IF;              
    
     IF UPPER(LCR_CAT_REC.category_type) = ‘_PURCHASING_CATEGORY’
     THEN
         l_trx_type := ‘UPDATE’;            
         BEGIN
         SELECT category_id
           INTO l_old_cat_id
           FROM mtl_categories_b
          WHERE structure_id = l_struc_id
            AND UPPER(segment1) = UPPER(‘UNASSIGNED’)
            AND UPPER(segment2) = UPPER(‘UNASSIGNED’)
            AND UPPER(segment3) = UPPER(‘UNASSIGNED’);
         EXCEPTION
         WHEN OTHERS
         THEN
           l_old_cat_id := NULL;   
           l_err_msg := l_err_msg || ‘Old
Category ID was not found, ‘
;   
         END;
     ELSE   
         l_trx_type := ‘CREATE’;
     END IF;
     — 
Validating Item Number
     LOG(5,‘Validating Item Number’);    
     BEGIN     
     SELECT inventory_item_id
       INTO l_inv_item_id
       FROM mtl_system_items_b           
      WHERE segment1 = TRIM(LCR_CAT_REC.item_number)
        AND organization_id = gn_inv_org_id;
     EXCEPTION WHEN OTHERS
     THEN      
       l_err_msg := l_err_msg ||
Item does not exist In Oracle, ‘
;
      END;
     — 
Validating Item Number Duplication for a category
     IF l_cat_id IS NOT NULL AND l_inv_item_id IS NOT NULL
     THEN
      LOG(5,‘Validating Item Number Duplication for a
category’
);
      l_item_count:= 0;
      BEGIN
      SELECT COUNT(1)
        INTO l_item_count
        FROM mtl_item_categories_v
       WHERE inventory_item_id  = l_inv_item_id
         AND organization_id    = gn_inv_org_id
         AND category_id        = l_cat_id –NVL(l_cat_id,l_old_cat_id)
         AND structure_id       = l_struc_id;
       IF (l_item_count > 0)
       THEN
        l_err_msg := l_err_msg || ‘Item/Category
combination already exists, ‘
;
       END IF;
      END;
     END IF; 
                        
       IF l_err_msg IS NULL
       THEN
        fnd_file.put_line (fnd_file.LOG,( ‘Item categories Interface Insert’ ));
        LOG(5,‘Validation of Item categories Succedded’);        
         —– Insert in to interface table starts
         
          IF UPPER(LCR_CAT_REC.category_type) = ‘PURCHASING_CATEGORY’
          THEN           
            INSERT INTO
MTL_ITEM_CATEGORIES_INTERFACE
(set_process_id
                                                     
,process_flag
                                                     
,organization_id
                                                      ,inventory_item_id
                                                     
,category_id
                                                     
,category_set_id
                                                     
,old_category_id
                                                      ,transaction_type                  
                                                     
,last_update_date
                                                     
,last_updated_by
                                                      ,creation_date
                                                     
,created_by
                                                     
,last_update_login                
                                                      
)
                                               
VALUES(1                         — set_process_id
                                                     
,1                         — process_flag        
                                                     
,gn_inv_org_id             — organization_id    
                                                     
,l_inv_item_id             –inventory_item_id
                                                 
    
,l_cat_id                  — category_id
                                                     
,l_cat_set_id              — category_set_id
                                                     
,l_old_cat_id              –2321,                 
                                                      ,l_trx_type                –transaction_type,                    
                                                     
,SYSDATE                   –last_update_date,
                                                      ,gn_user_id                –last_updated_by,
                                                     
,SYSDATE                   –creation_date,
                                                     
,gn_user_id                –created_by,
                                                     
,gn_user_id                –last_update_login
                                                      
);
          ELSE
                    
                INSERT INTO
MTL_ITEM_CATEGORIES_INTERFACE
                            (set_process_id,
                             process_flag,
                             organization_id,
                             inventory_item_id,
                             category_id,
                             category_set_id, 
                           —  old_category_id,
                             transaction_type,                  
                             last_update_date,
                             last_updated_by,
                             creation_date,
                             created_by,
                            
last_update_login                
                            )
                     VALUES (1, — set_process_id
                             1, — process_flag        
                             gn_inv_org_id,  — organization_id    
                             l_inv_item_id, –inventory_item_id
                             l_cat_id, — category_id
                             l_cat_set_id, — category_set_id 
                           —  2321,                 
                             l_trx_type, –‘CREATE’, –transaction_type,                    
                             Sysdate , –last_update_date,
                             gn_user_id, –last_updated_by,
                             Sysdate, –creation_date,
                             gn_user_id, –created_by,
                             gn_user_id –last_update_login
                            );         
          END IF;
          
        —– Record Status Update
       
            UPDATE xx_CNV_ITEM_CATEGORY
               SET X_RECORD_STATUS = ‘IAV’
                  ,x_record_msg = ‘Assignment validated’
             WHERE ROWID = LCR_CAT_REC.ROWID;
       ELSE        
            UPDATE xx_CNV_ITEM_CATEGORY
               SET X_RECORD_STATUS = ‘IAE’
                   ,X_RECORD_MSG = ‘Assignment Validation Error – ‘||l_err_msg
            WHERE ROWID = LCR_CAT_REC.ROWID;         
       END IF; 
                  
    END LOOP;   
 
    COMMIT;
 EXCEPTION WHEN OTHERS
 THEN   
    LOG (1,‘Unexpected error @ item_cat_assign_proc
->’
|| SQLERRM);
 END item_cat_assign_proc;
Step2: The sample table structure is given below
CREATE TABLE XX_CNV_ITEM_CATEGORY
(
  ITEM_NUMBER      VARCHAR2(240 BYTE),
  CATEGORY_TYPE    VARCHAR2(240 BYTE),
  CAT_SEGMENT1     VARCHAR2(240 BYTE),
  CAT_SEGMENT2     VARCHAR2(240 BYTE),
  CAT_SEGMENT3     VARCHAR2(240 BYTE),
  CAT_SEGMENT4     VARCHAR2(240 BYTE),
  CAT_SEGMENT5     VARCHAR2(240 BYTE),
  CAT_SEGMENT6     VARCHAR2(240 BYTE),
  CAT_SEGMENT7     VARCHAR2(240 BYTE),
  CAT_SEGMENT8     VARCHAR2(240 BYTE),
  CAT_SEGMENT9     VARCHAR2(240 BYTE),
  CAT_SEGMENT10    VARCHAR2(240 BYTE),
  X_RECORD_STATUS  VARCHAR2(3 BYTE),
  X_RECORD_MSG     VARCHAR2(2000 BYTE)

)

By

Deepak J
  • October 3, 2016 | 20 views
  • Comments