Wednesday, March 31, 2010

FAQ's on Subledger to GL Transfer R12

1. What are the important tables for sub-ledger accounting?
 xla_transaction_entities
 xla_events
 xla_ae_headers
 xla_ae_lines
 xla_distribution_links
 xla_control_balances

2. How the SLA events get generated?
 Through the SLA API's in the subledgers' code

3. What are the steps to transfer the data to GL from the subledgers?
 Submit Create Accounting / Transfer Journal Entries to GL.
 1. Create Accounting
 2. Tranfer to GL (includes Journal Import)
 3. Post to GL

4. Which process push the data into which tables?
 Create Accounting     - Subledger tables and xla_events  -> xla_ae_headers, xla_ae_lines and xla_distribution_links
 Transfer to GL              - xla_ae_headers and xla_ae_lines  -> gl_je_headers and gl_je_lines
 Post to GL                     - gl_je_headers and gl_je_lines    -> gl_balances
 Subledger Accounting Balances Update - xla_ae_headers and xla_ae_lines  -> xla_control_balances

5. What data will be there in xla_control_balances?
 Balances related to Third Party Control Accounts

Wednesday, March 31, 2010 by Team search · 0

How to Drilldown from General Ledger(GL) to Account Receivables(AR) [or] AR-> GL in R12

It can be used to drilldown GL to AR (or) in the reverse way. Here let us try to collect the General Ledger information from the Account receivables information (Receipt Number). All the queries used below were tested in R12.1.1 instance. 

Collecting the sub ledger information (Receipt Information)
Here we collect all the information about the Receipt number from sub-ledger (AR) tables. The most important information and used to link with GL tables is AR_DISTRIBUTIONS_ALL.line_id

Read More -->

by Team search · 0

API to populate the Descriptive element Value of an item ( inv_item_catalog_elem_pub.process_item_descr_elements)

DECLARE
 v_inv_item_id            NUMBER:= 0;
 v_catalog_Group_id       NUMBER:= 0;
 v_organization_id        NUMBER: = 0;
 x_generated_descr        VARCHAR2(240);
 x_return_status          VARCHAR2 (1);
 x_errorcode              NUMBER;
 x_msg_count              NUMBER;
 x_msg_data               VARCHAR2 (1000);
 x_msg_index_out          NUMBER: =0;
 l_item_desc_elem_table   INV_ITEM_CATALOG_ELEM_PUB.ITEM_DESC_ELEMENT_TABLE;
 l_current_item_desc_elem INV_ITEM_CATALOG_ELEM_PUB.ITEM_DESC_ELEMENT;
 l_error_message          VARCHAR2 (1000);
 j                        NUMBER  := 0;
BEGIN
   --Getting the item id for the existing item ABCTEST
   BEGIN
          SELECT inventory_item_id
          INTO   v_inv_item_id
          FROM   mtl_system_items_b msi,
                 mtl_parameters mp
          WHERE msi.organization_id   = mp.organization_id
          AND    msi.segment1         = 'ABCTEST'
          AND    mp.organization_code = 'V1';
   EXCEPTION
      WHEN OTHERS THEN
         fnd_file.put_line (fnd_file.LOG,'Error in getting the item id for Item ABCTEST and error is '||SUBSTR (SQLERRM, 1,200));
   END;

   -- Populating the pl/Sql table. In this for Element Name CatalogElement1, Element value ABCValue1 is Mapped.

  l_current_item_desc_elem.DESCRIPTION_DEFAULT := 'Y';
  l_current_item_desc_elem.ELEMENT_NAME := 'CatalogElement1';
  l_current_item_desc_elem.ELEMENT_VALUE := 'ABCValue1';
  l_item_desc_elem_table (j) := l_current_item_desc_elem;

      BEGIN
           FND_MSG_PUB.INITIALIZE;
          
         --API to assign Element Value to the Item--
         inv_item_catalog_elem_pub.process_item_descr_elements
          ( p_api_version             => 1.0
          , p_inventory_item_id       => v_inv_item_id
          , p_item_desc_element_table => l_item_desc_elem_table
          , x_generated_descr         => x_generated_descr
          , x_return_status           => x_return_status
          , x_msg_count               => x_msg_count
          , x_msg_data                => x_msg_data
          );
          COMMIT;
         
          IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN   
                   fnd_file.put_line (fnd_file.LOG,'Element value ABCValue1 is Successfully assigned to the element name CatalogElement1 for the Item ABCTEST ');
        ELSE
               --Getting the Error Reason if the element value is not assigned
               -- If there are multiple errors
           IF (FND_MSG_PUB.Count_Msg > 1) THEN
                FOR k IN 1..FND_MSG_PUB.Count_Msg LOOP
                  FND_MSG_PUB.Get (p_msg_index     => k,
                                   p_encoded       => 'F',
                                   p_data          => x_msg_data,
                                   p_msg_index_out => x_msg_index_out);
                                 
                  IF x_msg_data IS NOT NULL THEN
                    l_error_message:= l_error_message ||'-'||x_msg_data;
                  END IF;
                END LOOP;
             ELSE
                  --Only one error
                  FND_MSG_PUB.Get (p_msg_index     => 1,
                                   p_encoded       => 'F',
                                   p_data          => x_msg_data,
                                   p_msg_index_out => x_msg_index_out);
                                
                  l_error_message:= x_msg_data;
                
           END IF;
        Fnd_file.put_line(fnd_file.LOG,'Error in assigning Element value ABCValue1 to the element name CatalogElement1 for the Item ABCTEST and reason is '||l_error_message);
        END IF;
      END;

EXCEPTION
   WHEN OTHERS THEN
           fnd_file.put_line (fnd_file.LOG,'Error '|| SUBSTR (SQLERRM, 1,200));

END; 

by Team search · 0

How to Assign catalog group to the Item through PL/SQL

DECLARE
 V_item_id                   NUMBER: = 0;
 V_catalog_Group_id          NUMBER: = 0;
 V_organization_id           NUMBER: = 0;
 V_request_id                NUMBER: = 0; 
BEGIN 
  --Getting the Organization id   
  BEGIN
      SELECT Organization_id
      INTO   v_organization_id
      FROM   mtl_parameters mp
      WHERE  mp.organization_code = 'V1';
   EXCEPTION
      WHEN OTHERS THEN
         fnd_file.put_line (fnd_file.LOG,'Error in getting the Organization id for Organization code V1 and error is '||SUBSTR (SQLERRM, 1,200));
   END;

   --Getting the item id for the existing item ABCTEST
   BEGIN
      SELECT inventory_item_id
      INTO   v_item_id
      FROM   mtl_system_items_b msi
      WHERE  msi.segment1 = 'ABCTEST'
      AND    msi.organization_id = v_organization_id;
   EXCEPTION
      WHEN OTHERS THEN
         fnd_file.put_line (fnd_file.LOG,'Error in getting the item id for Item ABCTEST and error is '||SUBSTR (SQLERRM, 1,200));
   END;


   --Getting the catalog group id for the existing Catalog Group Name 'NewCatalog'
   BEGIN
      SELECT item_catalog_group_id
      INTO   v_catalog_group_id
      FROM   mtl_item_catalog_groups
      WHERE segment1='NewCatalog';
   EXCEPTION
      WHEN OTHERS THEN
         fnd_file.put_line (fnd_file.LOG,'Error in getting the catalog group id for catalog group Newcatalog and error is '||SUBSTR (SQLERRM, 1,200));
   END;

   --Inserting into Item interface table
   BEGIN
      INSERT INTO mtl_system_items_interface
                 (inventory_item_id,
                  organization_id,
                  process_flag,
                  set_process_id,
                  transaction_type,
                  item_catalog_group_id
                  )
            VALUES
                 (v_item_id,
                  v_organization_id,
                  1,
                  1,
                  'UPDATE',
                  v_catalog_group_id
                  );
        COMMIT;
   EXCEPTION
      WHEN OTHERS THEN
         fnd_file.put_line (fnd_file.LOG,'Error in inserting record in interface table and error is '||SUBSTR (SQLERRM, 1,200));
   END;
  
   --Submit the item import program in Update Mode to update the item catalog group information
   BEGIN
     
        fnd_file.put_line (fnd_file.LOG,'--Submitting Item Import Program for Item--');
     
     
        v_request_id:= Fnd_Request.submit_request (
                       application   => 'INV',
                       Program       => 'INCOIN',
                       description   => NULL,
                       start_time    => SYSDATE,
                       sub_request   => FALSE,
                       argument1     => v_organization_id,
                       argument2     => 1,            
                       argument3     => 1,  --Group ID option (All)
                       argument4     => 1,  -- Group ID Dummy
                       argument5     => 1,  -- Delete processed Record  
                       argument6     => 1,  -- Set Process id                    
                       argument7     => 2   -- Update item
                       ); 
         COMMIT;                      
         IF (v_request_id = 0) THEN        
             fnd_file.put_line (fnd_file.LOG,'Item Import Program Not Submitted');
         ELSE
             fnd_file.put_line (fnd_file.LOG,'Item Import Program submitted');            
         END IF;
  END;

END;

by Team search · 0

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.