Infolinks

Friday 6 July 2012

AR TRANSACTION TYPES

AR TRANSACTION TYPES

DECLARE
--apps intilization
   G_USER_NAME                          VARCHAR2(100)    := 'SYSADMIN';
   G_RESP_KEY                           VARCHAR2(100)    := 'APPLICATION_DEVELOPER';
   l_user_id                            NUMBER;
   l_resp_id                            NUMBER;
   l_resp_appl_id                       NUMBER;
--Declaring variables for ra_cust_trx_types_all
U_CUST_TRX_TYPE_ID         NUMBER(15) :=ra_cust_trx_types_s.nextval;                                                                                                                                                                                  
U_LAST_UPDATE_DATE               DATE       :=sysdate;                                                                                                                                                                                 
U_LAST_UPDATED_BY                NUMBER(15) :=null;
U_CREATION_DATE                  DATE       :=sysdate;                                                                                                                                                                                
U_CREATED_BY                     NUMBER(15) :=null;                                                                                                                                                                                   
U_LAST_UPDATE_LOGIN                       NUMBER(15) :=null;                                                                                                                                                                                  
U_POST_TO_GL                     VARCHAR2(1):='Y';                                                                                                                                                                                 
U_ACCOUNTING_AFFECT_FLAG         VARCHAR2(1):='Y';
U_CREDIT_MEMO_TYPE_ID                     NUMBER(15) :=null;                                                                                                                                                                                
U_STATUS                                  VARCHAR2(30):=null;                                                                                                                                                                                 
U_NAME                                    VARCHAR2(20):='Test2';
U_DESCRIPTION                             VARCHAR2(80):='This is sample transaction type';                                                                                                                                                                                 
U_TYPE                                    VARCHAR2(20):='INV';                                                                                                                                                                               
U_DEFAULT_TERM                            NUMBER(15)  :=null;                                                                                                                                                                               
U_DEFAULT_PRINTING_OPTION         VARCHAR2(20) :='NOT';                                                                                                                                                                                
U_DEFAULT_STATUS                 VARCHAR2(30) :='OP';                                                                                                                                                                             
U_GL_ID_REV                               NUMBER(15)   :=null;                                                                                                                                                                               
U_GL_ID_FREIGHT                           NUMBER(15)   :=null;                                                                                                                                                                              
U_GL_ID_REC                               NUMBER(15)   :=null;                                                                                                                                                                                
U_SUBSEQUENT_TRX_TYPE_ID                  NUMBER(15)   :=null;                                                                                                                                                                              
U_SET_OF_BOOKS_ID                         NUMBER(15)   :=1;                                                                                                                                                                               
U_ATTRIBUTE_CATEGORY                      VARCHAR2(30) :=null;                                                                                                                                                                              
U_ATTRIBUTE1                              VARCHAR2(150):=null;                                                                                                                                                                                
U_ATTRIBUTE2                              VARCHAR2(150):=null;                                                                                                                                                                               
U_ATTRIBUTE3                              VARCHAR2(150):=null;
U_ATTRIBUTE4                              VARCHAR2(150):=null;                                                                                                                                                                               
U_ATTRIBUTE5                              VARCHAR2(150):=null;                                                                                                                                                                                
U_ATTRIBUTE6                              VARCHAR2(150):=null;                                                                                                                                                                               
U_ATTRIBUTE7                              VARCHAR2(150):=null;                                                                                                                                                                               
U_ATTRIBUTE8                              VARCHAR2(150):=null;                                                                                                                                                                               
U_ATTRIBUTE9                              VARCHAR2(150):=null;                                                                                                                                                                              
U_ATTRIBUTE10                             VARCHAR2(150):=null;                                                                                                                                                                               
U_ALLOW_FREIGHT_FLAG             VARCHAR2(1)  :='N';                                                                                                                                                                               
U_ALLOW_OVERAPPLICATION_FLAG      VARCHAR2(10)  :='N';                                                                                                                                                                               
U_CREATION_SIGN                  VARCHAR2(30) :='A';                                                                                                                                                                               
U_END_DATE                                DATE         :=null;                                                                                                                                                                                
U_GL_ID_CLEARING                          NUMBER(15)   :=null;                                                                                                                                                                               
U_GL_ID_TAX                               NUMBER(15)   :=null;                                                                                                                                                                               
U_GL_ID_UNBILLED                          NUMBER(15)   :=null;                                                                                                                                                                                
U_GL_ID_UNEARNED                          NUMBER(15)   :=null;                                                                                                                                                                                
U_START_DATE                      DATE          :=sysdate;                                                                                                                                                                               
U_TAX_CALCULATION_FLAG           VARCHAR2(1)   :='N';                                                                                                                                                                              
U_ATTRIBUTE11                             VARCHAR2(150) :=null;                                                                                                                                                                              
U_ATTRIBUTE12                             VARCHAR2(150) :=null;                                                                                                                                                                               
U_ATTRIBUTE13                             VARCHAR2(150) :=null;                                                                                                                                                                              
U_ATTRIBUTE14                             VARCHAR2(150) :=null;                                                                                                                                                                              
U_ATTRIBUTE15                             VARCHAR2(150) :=null;                                                                                                                                                                              
U_NATURAL_APP_ONLY_FLAG   VARCHAR2(10)          :='Y';                                                                                                                                                                              
U_ORG_ID                                  NUMBER(15)    :=204;                                                                                                                                                                               
U_GLOBAL_ATTRIBUTE1                       VARCHAR2(150) :=null;                                                                                                                                                                              
U_GLOBAL_ATTRIBUTE2                       VARCHAR2(150) :=null;                                                                                                                                                                               
U_GLOBAL_ATTRIBUTE3                       VARCHAR2(150) :=null;                                                                                                                                                                               
U_GLOBAL_ATTRIBUTE4                       VARCHAR2(150) :=null;                                                                                                                                                                               
U_GLOBAL_ATTRIBUTE5                       VARCHAR2(150) :=null;                                                                                                                                                                              
U_GLOBAL_ATTRIBUTE6                       VARCHAR2(150) :=null;                                                                                                                                                                               
U_GLOBAL_ATTRIBUTE7                       VARCHAR2(150) :=null;                                                                                                                                                                               
U_GLOBAL_ATTRIBUTE8                       VARCHAR2(150) :=null;                                                                                                                                                                              
U_GLOBAL_ATTRIBUTE9                       VARCHAR2(150) :=null;                                                                                                                                                                             
U_GLOBAL_ATTRIBUTE10                      VARCHAR2(150) :=null;                                                                                                                                                                              
U_GLOBAL_ATTRIBUTE11                      VARCHAR2(150) :=null;                                                                                                                                                                              
U_GLOBAL_ATTRIBUTE12                      VARCHAR2(150)  :=null;                                                                                                                                                                              
U_GLOBAL_ATTRIBUTE13                      VARCHAR2(150)  :=null;                                                                                                                                                                              
U_GLOBAL_ATTRIBUTE14                      VARCHAR2(150)  :=null;                                                                                                                                                                              
U_GLOBAL_ATTRIBUTE15                      VARCHAR2(150)  :=null;                                                                                                                                                                               
U_GLOBAL_ATTRIBUTE16                      VARCHAR2(150)  :=null;                                                                                                                                                                              
U_GLOBAL_ATTRIBUTE17                      VARCHAR2(150)  :=null;                                                                                                                                                                              
U_GLOBAL_ATTRIBUTE18                      VARCHAR2(150)  :=null;                                                                                                                                                                              
U_GLOBAL_ATTRIBUTE19                      VARCHAR2(150)  :=null;                                                                                                                                                                             
U_GLOBAL_ATTRIBUTE20                      VARCHAR2(150)  :=null;                                                                                                                                                                             
U_GLOBAL_ATTRIBUTE_CATEGORY               VARCHAR2(30)   :=null;                                                                                                                                                                             
U_RULE_SET_ID                             NUMBER         :=null;                                                                                                                                                                               
U_SIGNED_FLAG                             VARCHAR2(1)    :='N';                                                                                                                                                                              
U_DRAWEE_ISSUED_FLAG                      VARCHAR2(1)    :='N';                                                                                                                                                                             
U_MAGNETIC_FORMAT_CODE                    VARCHAR2(30)   :=null;                                                                                                                                                                              
U_FORMAT_PROGRAM_ID                       NUMBER(15)     :=null;                                                                                                                                                                             
U_GL_ID_UNPAID_REC                        NUMBER(15)     :=null;                                                                                                                                                                            
U_GL_ID_REMITTANCE                        NUMBER(15)     :=null;                                                                                                                                                                              
U_GL_ID_FACTOR                            NUMBER(15)     :=null;                                                                                                                                                                             
U_ALLOCATE_TAX_FREIGHT                    VARCHAR2(1)    :=null;                                                                                                                                                                              
U_LEGAL_ENTITY_ID                         NUMBER(15)     :=null;                                                                                                                                                                            
U_EXCLUDE_FROM_LATE_CHARGES               VARCHAR2(30)   :='N';                                                                                                                                                                              
U_ADJ_POST_TO_GL                          VARCHAR2(1)    :='Y';
X_NAME                                    VARCHAR2(150);
X_COUNT                                   NUMBER         :=0;
BEGIN
BEGIN
select name into x_name
from ra_cust_trx_types_all
where name=u_name;
EXCEPTION WHEN NO_DATA_FOUND THEN
X_COUNT:=X_COUNT+1;
WHEN TOO_MANY_ROWS THEN
NUll;
END;
 BEGIN
      SELECT user_id
      INTO   l_user_id
      FROM   fnd_user
      WHERE  user_name=G_USER_NAME;
   EXCEPTION WHEN OTHERS THEN
      null;
   END;
   BEGIN
      SELECT responsibility_id
            ,application_id
      INTO   l_resp_id
            ,l_resp_appl_id
      FROM   fnd_responsibility_vl
      WHERE  responsibility_key=G_RESP_KEY;
   EXCEPTION WHEN OTHERS THEN
      null;
   END;
   FND_GLOBAL.APPS_INITIALIZE(user_id=>l_user_id,
                              resp_id=>l_resp_id,
                              resp_appl_id=>l_resp_appl_id);
                             
    U_LAST_UPDATED_BY :=l_user_id;
    U_CREATED_BY      :=l_user_id;
    U_LAST_UPDATE_LOGIN:=5590465;
   
    U_LAST_UPDATED_BY  :=l_user_id;
    U_CREATED_BY       :=l_user_id;
    U_LAST_UPDATE_LOGIN :=5092465;
   
    IF (x_count !=1) THEN
 dbms_output.put_line('Trying to insert duplicate value');
ELSE
   
    -- inserting values into ra_cust_trx_types_all
   
    insert into ra_cust_trx_types_all(
                                      CUST_TRX_TYPE_ID,                                                                                                                                                                   
                                      LAST_UPDATE_DATE,                                                                                                                                                                                      
                                      LAST_UPDATED_BY ,                                                                                                                                                                             
                                      CREATION_DATE  ,                                                                                                                                                                                         
                                      CREATED_BY  ,                                                                                                                                                                               
                                      LAST_UPDATE_LOGIN  ,                                                                                                                                                                                     
                                      POST_TO_GL  ,                                                                                                                                                                            
                                      ACCOUNTING_AFFECT_FLAG ,                                                                                                                                                        
                                      CREDIT_MEMO_TYPE_ID,                                                                                                                                                                                          
                                      STATUS ,                                                                                                                                                                                                     
                                      NAME   ,                                                                                                                                                                                        
                                      DESCRIPTION   ,                                                                                                                                                                                                      
                                      TYPE   ,                                                                                                                                                                                                               
                                      DEFAULT_TERM ,                                                                                                                                                                                                         
                                      DEFAULT_PRINTING_OPTION  ,                                                                                                                                                                                     
                                      DEFAULT_STATUS  ,                                                                                                                                                                                        
                                      GL_ID_REV  ,                                                                                                                                                                                                               
                                      GL_ID_FREIGHT  ,                                                                                                                                                                                                         
                                      GL_ID_REC   ,                                                                                                                                                                                                           
                                      SUBSEQUENT_TRX_TYPE_ID  ,                                                                                                                                                                                                  
                                      SET_OF_BOOKS_ID   ,                                                                                                                                                                                                     
                                      ATTRIBUTE_CATEGORY  ,                                                                                                                                                                                                
                                      ATTRIBUTE1 ,                                                                                                                                                                                                         
                                      ATTRIBUTE2  ,                                                                                                                                                                                                          
                                      ATTRIBUTE3   ,                                                                                                                                                                                                        
                                      ATTRIBUTE4   ,                                                                                                                                                                                                       
                                      ATTRIBUTE5    ,                                                                                                                                                                                                   
                                      ATTRIBUTE6  ,                                                                                                                                                                                                       
                                      ATTRIBUTE7    ,                                                                                                                                                                                                      
                                      ATTRIBUTE8  ,                                                                                                                                                                                                   
                                      ATTRIBUTE9   ,                                                                                                                                                                                                       
                                      ATTRIBUTE10   ,                                                                                                                                                                                                      
                                      ALLOW_FREIGHT_FLAG  ,                                                                                                                                                                                         
                                      ALLOW_OVERAPPLICATION_FLAG ,                                                                                                                                                                                  
                                      CREATION_SIGN  ,                                                                                                                                                                                             
                                      END_DATE   ,                                                                                                                                                                                                              
                                      GL_ID_CLEARING  ,                                                                                                                                                                                                         
                                      GL_ID_TAX     ,                                                                                                                                                                                                         
                                      GL_ID_UNBILLED   ,                                                                                                                                                                                                    
                                      GL_ID_UNEARNED   ,                                                                                                                                                                                                       
                                      START_DATE       ,                                                                                                                                                                                             
                                      TAX_CALCULATION_FLAG   ,                                                                                                                                                                                     
                                      ATTRIBUTE11   ,                                                                                                                                                                                                     
                                      ATTRIBUTE12   ,                                                                                                                                                                                                   
                                      ATTRIBUTE13    ,                                                                                                                                                                                                      
                                      ATTRIBUTE14   ,                                                                                                                                                                                                    
                                      ATTRIBUTE15   ,                                                                                                                                                                                                       
                                      NATURAL_APPLICATION_ONLY_FLAG,                                                                                                                                                                              
                                      ORG_ID ,                                                                                                                                                                                                                
                                      GLOBAL_ATTRIBUTE1  ,                                                                                                                                                                                               
                                      GLOBAL_ATTRIBUTE2 ,                                                                                                                                                                                                 
                                      GLOBAL_ATTRIBUTE3  ,                                                                                                                                                                                               
                                      GLOBAL_ATTRIBUTE4  ,                                                                                                                                                                                               
                                      GLOBAL_ATTRIBUTE5  ,                                                                                                                                                                                                
                                      GLOBAL_ATTRIBUTE6  ,                                                                                                                                                                                            
                                      GLOBAL_ATTRIBUTE7  ,                                                                                                                                                                                             
                                      GLOBAL_ATTRIBUTE8  ,                                                                                                                                                                                              
                                      GLOBAL_ATTRIBUTE9  ,                                                                                                                                                                                               
                                      GLOBAL_ATTRIBUTE10  ,                                                                                                                                                                                            
                                      GLOBAL_ATTRIBUTE11  ,                                                                                                                                                                                              
                                      GLOBAL_ATTRIBUTE12 ,                                                                                                                                                                                            
                                      GLOBAL_ATTRIBUTE13  ,                                                                                                                                                                                       
                                      GLOBAL_ATTRIBUTE14  ,                                                                                                                                                                                             
                                      GLOBAL_ATTRIBUTE15  ,                                                                                                                                                                                               
                                      GLOBAL_ATTRIBUTE16  ,                                                                                                                                                                                          
                                      GLOBAL_ATTRIBUTE17  ,                                                                                                                                                                                               
                                      GLOBAL_ATTRIBUTE18  ,                                                                                                                                                                                           
                                      GLOBAL_ATTRIBUTE19  ,                                                                                                                                                                                              
                                      GLOBAL_ATTRIBUTE20  ,                                                                                                                                                                                                
                                      GLOBAL_ATTRIBUTE_CATEGORY   ,                                                                                                                                                                                       
                                      RULE_SET_ID       ,                                                                                                                                                                                                    
                                      SIGNED_FLAG        ,                                                                                                                                                                                                  
                                      DRAWEE_ISSUED_FLAG   ,                                                                                                                                                                                                
                                      MAGNETIC_FORMAT_CODE  ,                                                                                                                                                                                           
                                      FORMAT_PROGRAM_ID    ,                                                                                                                                                                                             
                                      GL_ID_UNPAID_REC    ,                                                                                                                                                                                                
                                      GL_ID_REMITTANCE    ,                                                                                                                                                                                                  
                                      GL_ID_FACTOR       ,                                                                                                                                                                                                       
                                      ALLOCATE_TAX_FREIGHT  ,                                                                                                                                                                                               
                                      LEGAL_ENTITY_ID       ,                                                                                                                                                                                             
                                      EXCLUDE_FROM_LATE_CHARGES   ,                                                                                                                                                                                         
                                      ADJ_POST_TO_GL    )
                                     
             VALUES         (
                                      U_CUST_TRX_TYPE_ID,                                                                                                                                                                   
                                      U_LAST_UPDATE_DATE,                                                                                                                                                                                      
                                      U_LAST_UPDATED_BY ,                                                                                                                                                                             
                                      U_CREATION_DATE  ,                                                                                                                                                                                         
                                      U_CREATED_BY  ,                                                                                                                                                                               
                                      U_LAST_UPDATE_LOGIN  ,                                                                                                                                                                                     
                                      U_POST_TO_GL  ,                                                                                                                                                                            
                                      U_ACCOUNTING_AFFECT_FLAG ,                                                                                                                                                        
                                      U_CREDIT_MEMO_TYPE_ID,                                                                                                                                                                                          
                                      U_STATUS ,                                                                                                                                                                                                     
                                      U_NAME   ,                                                                                                                                                                                        
                                      U_DESCRIPTION   ,                                                                                                                                                                                                      
                                      U_TYPE   ,                                                                                                                                                                                                               
                                      U_DEFAULT_TERM ,                                                                                                                                                                                                         
                                      U_DEFAULT_PRINTING_OPTION  ,                                                                                                                                                                                     
                                      U_DEFAULT_STATUS  ,                                                                                                                                                                                        
                                      U_GL_ID_REV  ,                                                                                                                                                                                                               
                                      U_GL_ID_FREIGHT  ,                                                                                                                                                                                                         
                                      U_GL_ID_REC   ,                                                                                                                                                                                                           
                                      U_SUBSEQUENT_TRX_TYPE_ID  ,                                                                                                                                                                                                  
                                      U_SET_OF_BOOKS_ID   ,                                                                                                                                                                                                     
                                      U_ATTRIBUTE_CATEGORY  ,                                                                                                                                                                                                
                                      U_ATTRIBUTE1 ,                                                                                                                                                                                                         
                                      U_ATTRIBUTE2  ,                                                                                                                                                                                                          
                                      U_ATTRIBUTE3   ,                                                                                                                                                                                                        
                                      U_ATTRIBUTE4   ,                                                                                                                                                                                                       
                                      U_ATTRIBUTE5    ,                                                                                                                                                                                                   
                                      U_ATTRIBUTE6  ,                                                                                                                                                                                                       
                                      U_ATTRIBUTE7    ,                                                                                                                                                                                                      
                                      U_ATTRIBUTE8  ,                                                                                                                                                                                                   
                                      U_ATTRIBUTE9   ,                                                                                                                                                                                                       
                                      U_ATTRIBUTE10   ,                                                                                                                                                                                                      
                                      U_ALLOW_FREIGHT_FLAG  ,                                                                                                                                                                                         
                                      U_ALLOW_OVERAPPLICATION_FLAG ,                                                                                                                                                                                  
                                      U_CREATION_SIGN  ,                                                                                                                                                                                             
                                      U_END_DATE   ,                                                                                                                                                                                                              
                                      U_GL_ID_CLEARING  ,                                                                                                                                                                                                         
                                      U_GL_ID_TAX     ,                                                                                                                                                                                                         
                                      U_GL_ID_UNBILLED   ,                                                                                                                                                                                                    
                                      U_GL_ID_UNEARNED   ,                                                                                                                                                                                                       
                                      U_START_DATE       ,                                                                                                                                                                                             
                                      U_TAX_CALCULATION_FLAG   ,                                                                                                                                                                                     
                                      U_ATTRIBUTE11   ,                                                                                                                                                                                                     
                                      U_ATTRIBUTE12   ,                                                                                                                                                                                                   
                                      U_ATTRIBUTE13    ,                                                                                                                                                                                                      
                                      U_ATTRIBUTE14   ,                                                                                                                                                                                                    
                                      U_ATTRIBUTE15   ,                                                                                                                                                                                                       
                                      U_NATURAL_APP_ONLY_FLAG,                                                                                                                                                                              
                                      U_ORG_ID ,                                                                                                                                                                                                                
                                      U_GLOBAL_ATTRIBUTE1  ,                                                                                                                                                                                               
                                      U_GLOBAL_ATTRIBUTE2 ,                                                                                                                                                                                                 
                                      U_GLOBAL_ATTRIBUTE3  ,                                                                                                                                                                                               
                                      U_GLOBAL_ATTRIBUTE4  ,                                                                                                                                                                                               
                                      U_GLOBAL_ATTRIBUTE5  ,                                                                                                                                                                                                
                                      U_GLOBAL_ATTRIBUTE6  ,                                                                                                                                                                                            
                                      U_GLOBAL_ATTRIBUTE7  ,                                                                                                                                                                                             
                                      U_GLOBAL_ATTRIBUTE8  ,                                                                                                                                                                                              
                                      U_GLOBAL_ATTRIBUTE9  ,                                                                                                                                                                                               
                                      U_GLOBAL_ATTRIBUTE10  ,                                                                                                                                                                                            
                                      U_GLOBAL_ATTRIBUTE11  ,                                                                                                                                                                                              
                                      U_GLOBAL_ATTRIBUTE12 ,                                                                                                                                                                                            
                                      U_GLOBAL_ATTRIBUTE13  ,                                                                                                                                                                                       
                                      U_GLOBAL_ATTRIBUTE14  ,                                                                                                                                                                                             
                                      U_GLOBAL_ATTRIBUTE15  ,                                                                                                                                                                                               
                                      U_GLOBAL_ATTRIBUTE16  ,                                                                                                                                                                                          
                                      U_GLOBAL_ATTRIBUTE17  ,                                                                                                                                                                                               
                                      U_GLOBAL_ATTRIBUTE18  ,                                                                                                                                                                                           
                                      U_GLOBAL_ATTRIBUTE19  ,                                                                                                                                                                                              
                                      U_GLOBAL_ATTRIBUTE20  ,                                                                                                                                                                                                
                                      U_GLOBAL_ATTRIBUTE_CATEGORY   ,                                                                                                                                                                                       
                                      U_RULE_SET_ID       ,                                                                                                                                                                                                    
                                      U_SIGNED_FLAG        ,                                                                                                                                                                                                  
                                      U_DRAWEE_ISSUED_FLAG   ,                                                                                                                                                                                                
                                      U_MAGNETIC_FORMAT_CODE  ,                                                                                                                                                                                           
                                      U_FORMAT_PROGRAM_ID    ,                                                                                                                                                                                             
                                      U_GL_ID_UNPAID_REC    ,                                                                                                                                                                                                
                                      U_GL_ID_REMITTANCE    ,                                                                                                                                                                                                  
                                      U_GL_ID_FACTOR       ,                                                                                                                                                                                                       
                                      U_ALLOCATE_TAX_FREIGHT  ,                                                                                                                                                                                               
                                      U_LEGAL_ENTITY_ID       ,                                                                                                                                                                                             
                                      U_EXCLUDE_FROM_LATE_CHARGES   ,                                                                                                                                                                                         
                                      U_ADJ_POST_TO_GL  );
                                     
   COMMIT;                                  
 DBMS_OUTPUT.PUT_LINE('Records inserted successfully');


   end if;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('The error is:'||SQLERRM);
END;

No comments:

Post a Comment