Customer Item Cross Reference Conversion

Customer Item Cross Reference Conversion
A
Customer Items are Item Numbers used by customer for the items you sell to your
customers. Customer Item numbers are customer specific, so you can have same
item referenced differently by each customer. Before you can cross-reference,
you must define customers and customer items first.
Step1: Sample script to fetch the Customer
Item Cross Reference Conversion   details
is given below.
PROCEDURE INSERT_CUST_ITEM_XREF( errbut    OUT   VARCHAR2,
                                 retcode   OUT   NUMBER,
                                 p_org     IN    NUMBER)
IS
   
l_inv_item_id      
NUMBER;   
   
l_err_msg          
VARCHAR2 (1000);
   
l_org_id            mtl_parameters
.organization_id%TYPE; 
   
l_mst_org_id        mtl_parameters
.master_organization_id%TYPE;
   
l_item_type        
VARCHAR2(60);
   
l_cust_id          
NUMBER;
   
l_item_count       
NUMBER;
   
ln_exists          
NUMBER;
   
ln_request_id      
NUMBER;
   
lb_complete         
BOOLEAN;
   
lc_phase           
VARCHAR2 (20);
   
lc_status          
VARCHAR2 (20);
   
lc_dev_phase       
VARCHAR2 (20);
   
lc_dev_status      
VARCHAR2 (20);
   
lc_message         
VARCHAR2 (100);
   
ln_interval        
NUMBER := 2;
    ln_max_wait         NUMBER := 9999;
   
lc_status_code     
VARCHAR2(50);
   
lc_phase_code      
VARCHAR2(50);  
    –ln_exists           NUMBER;
CURSOR item_cur
IS
    SELECT ROWID, a.*
      FROM xx_cnv_cust_item_xref a
     WHERE x_record_status =‘CIP’;
    
BEGIN
   
  BEGIN
   SELECT organization_id, master_organization_id
     INTO l_org_id, l_mst_org_id
     FROM mtl_parameters
    WHERE organization_id = p_org;
  EXCEPTION
   WHEN OTHERS
   THEN   
    LOG (‘Unable to find the Org before
processing’
|| SQLERRM);
  END;
   
  IF l_mst_org_id IS NOT NULL
  THEN
 
    FOR item_rec IN item_cur
    LOOP
    l_err_msg := NULL;
      
    BEGIN   
        SELECT cust_account_id
          INTO l_cust_id
          FROM HZ_CUST_ACCOUNTS
         WHERE orig_system_reference = item_rec.legacy_customer_num;   
    EXCEPTION
        WHEN OTHERS
        THEN
          
l_err_msg
:= l_err_msg || ‘Cound not get Customer ID  ‘ || ITEM_REC.customer_item_number;    
    END;              
       
    BEGIN
       SELECT item_type
         INTO l_item_type
         FROM mtl_system_items_b
        WHERE organization_id = l_org_id
          AND segment1 = TRIM(ITEM_REC.item_number);            
    EXCEPTION    
     WHEN OTHERS
     THEN       
        l_err_msg
:= l_err_msg || ‘Inventory Item does not
exists,’
|| item_rec.item_number;
    END;
   
    IF l_item_type IS NULL
    THEN
      l_err_msg := l_err_msg || ‘Item Type does not
exists,’
|| item_rec.item_number;
    END IF;
       
    — added by Doyensys on
24-Aug-2016   
   
   ln_exists := 0;
   BEGIN
       SELECT 1
         INTO ln_exists
         FROM mtl_customer_items
        WHERE customer_item_number = trim(item_rec.customer_item_number);           
    EXCEPTION    
     WHEN OTHERS
     THEN
       l_err_msg
:= l_err_msg || ‘Customer Item does not
exists’
|| ITEM_REC.customer_item_number;
    END;
           
       
       
    IF l_err_msg IS NULL
    THEN                
      INSERT INTO mtl_ci_xrefs_interface
                                  (customer_id,
                                 
customer_item_number
,
                                  preference_number,
                                 
inventory_item
,
                                 
master_organization_id
,
                                 
item_definition_level
,
                                  process_flag,
                                  process_mode,
                                  inactive_flag,
                                 
transaction_type
,
                                  lock_flag,
                                 
last_updated_by
,
                                  created_by,
                                 
last_update_date
,
                                  creation_date
                                  )
                           VALUES (l_cust_id,
                                  item_rec.customer_item_number,
                                  1, –Preference Number
                                  item_rec.item_number,
                                  l_mst_org_id,
                                  1, –item_definition_level
                                  1, –process_flag
                                  1, –process_mode
                                  ‘2’, — inactive_mode
                                  ‘CREATE’, 
                                  ‘N’, –lock_flag
                                  1,
                                  1,
                                  SYSDATE,
                                  SYSDATE
                                  );
                                 
        UPDATE XX_CNV_CUST_ITEM_XREF
           SET X_RECORD_STATUS = ‘CXV’
         WHERE ROWID = item_rec.ROWID;
    ELSE        
         UPDATE XX_CNV_CUST_ITEM_XREF
            SET X_RECORD_STATUS = ‘CXE’,
               
X_RECORD_MSG
= l_err_msg
          WHERE ROWID = item_rec.ROWID;         
    END IF; 
   
  END LOOP;
  COMMIT;
 
  –*** Standard Program Call
***–
 
    ln_exists := 0;
      
      BEGIN
      SELECT COUNT(1)
        INTO ln_exists
        FROM XX_CNV_CUST_ITEM_XREF
       WHERE X_RECORD_STATUS = ‘CXV’;
      END;
     
  IF ln_exists > 0
 
  THEN
 
    BEGIN
     FND_GLOBAL.APPS_INITIALIZE(gn_user_id, gn_resp_id, gn_resp_appl_id);
    
     mo_global.set_policy_context(‘S’,gn_org_id);
    
     fnd_request.set_org_id(gn_org_id);
    
     fnd_file.put_line(fnd_file.log,(‘*** Submitting Customer Item
Cross Reference Import Program ***’
));
    
    
      
    
ln_request_id
:=FND_REQUEST.SUBMIT_REQUEST (application  => ‘INV’
                                                
,program     => ‘INVCIINTX’
                                                
,description => NULL
                                                
,start_time  => NULL
                                                
,sub_request => NULL
                  
                              
,argument1   => ‘N’                    — Abort On Error
                                                
,argument2   => ‘Y’                    — Delete Record
                                                
);
                                                 
       fnd_file.put_line(fnd_file.LOG,‘Request Id- ‘||ln_request_id);
        IF 
ln_request_id
> 0 THEN
            COMMIT;
           
lb_complete
:=
                fnd_concurrent.wait_for_request (request_id      => ln_request_id
                                            ,interval            => ln_interval
                                            ,max_wait            => ln_max_wait
                                            ,phase               => lc_phase
                                            ,status              => lc_status
                                            ,dev_phase           => lc_dev_phase
                                            ,dev_status          => lc_dev_status
                
                           
,message             => lc_message
                            );
            COMMIT;
            IF UPPER (lc_dev_phase) IN (‘COMPLETE’) THEN
           
               fnd_file.put_line(fnd_file.log,(‘*** Import Customer Items Cross
Reference Program Completed Successfully ***’
));
              
            END IF;
        END IF;
       
       l_err_msg:= SQLERRM;
      
            UPDATE XX_CNV_CUST_ITEM_XREF a
               SET X_RECORD_STATUS = ‘CXP’
             WHERE EXISTS (SELECT 1
                             FROM mtl_customer_items b
                            WHERE a.customer_item_number = b.customer_item_number)
               AND X_RECORD_STATUS = ‘CXV’;
       UPDATE XX_CNV_CUST_ITEM_XREF a
          SET X_RECORD_STATUS  = ‘CXE’
        WHERE NOT EXISTS (SELECT 1
                            FROM mtl_customer_items b
                           WHERE a.customer_item_number = b.customer_item_number)
          AND X_RECORD_STATUS  = ‘CXV’;
       
       
    END;   
   
      
    END IF;
  COMMIT;
 
  END IF;
EXCEPTION
    WHEN OTHERS
    THEN
    dbms_output.put_line
(‘Outermost Exception:’||SQLERRM);
       
END INSERT_CUST_ITEM_XREF;
Step2: The sample table structure is given below
CREATE TABLE XX_CNV_CUST_ITEM_XREF
(
 
ITEM_NUMBER          
VARCHAR2(240 BYTE),
 
LEGACY_CUSTOMER_NUM  
VARCHAR2(25 BYTE),
 
CUSTOMER_ITEM_NUMBER 
VARCHAR2(240 BYTE),
 
X_RECORD_STATUS      
VARCHAR2(3 BYTE),
 
X_RECORD_MSG         
VARCHAR2(2000 BYTE)

)

By

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