Oracle Apps Miscellaneous Issue (RG Update)


Step 1   :  get the data from User ( Item name (Segment1), Organization code )


Step 2   : Move the legacy data into Oracle apps temp table.
               Example : xx_item_temp


Step 2   : Check what are the mandatory column in front end ( Oracle Apps from level )


Mandatory column : source_code, source_line_id, source_header_id, process_flag,
                   last_update_date, last_updated_by, creation_date, created_by,
                   last_update_login, inventory_item_id, organization_id,
                   transaction_quantity,PRIMARY_QUANTITY, transaction_uom, transaction_date,
                   subinventory_code, locator_id, transaction_type_id,TRANSACTION_MODE
                 
Column value    : 1, 1, 1, 1, sysdate, 1150, SYSDATE, 1150,1150,
                  misb.inventory_item_id, misb.organization_id,
                  CONCAT('-',SUM (moqd.primary_transaction_quantity)) qty,    ------Quantity must be negative value
                  SUM(NVL(MOQD.TRANSACTION_QUANTITY,0)),
                  transaction_uom_code, SYSDATE, moqd.subinventory_code,
                  inventory_location_id, 100001,3         
      

Step 3: Check what are the mandatory column in 'mtl_transactions_interface'  table level


step 4: update account id

        account id - distribution_account_id column in 'mtl_transactions_interface'  table
       ( select * from gl_code_combinations_kfv where CONCATENATED_SEGMENTS = '14.000000.51117.00.000.00.0000')
         select * from mtl_transaction_types  where TRANSACTION_TYPE_NAME = 'Miscellaneous Issue(RG Update)'





sample insert statement
~~~~~~~~~~~~~~~~~~~~~~~~

---  Check primary_transaction_quantity is ON HAND QUANTITY or not


INSERT INTO mtl_transactions_interface
            (source_code, source_line_id, source_header_id, process_flag,
             last_update_date, last_updated_by, creation_date, created_by,
             last_update_login, inventory_item_id, organization_id,
             transaction_quantity,PRIMARY_QUANTITY, transaction_uom, transaction_date,
             subinventory_code, locator_id, transaction_type_id,TRANSACTION_MODE)
   ( 
     SELECT  1, 1, 1, 1, sysdate, 1150, SYSDATE, 1150,1150,
             misb.inventory_item_id, misb.organization_id,
             CONCAT('-',SUM (moqd.primary_transaction_quantity)) qty,
SUM(NVL(MOQD.TRANSACTION_QUANTITY,0)),
             transaction_uom_code, SYSDATE, moqd.subinventory_code,
             inventory_location_id, 100001,3
        FROM mtl_system_items_b misb,
             mtl_onhand_quantities_detail moqd,
             mtl_item_locations_kfv mil,
             org_organization_definitions ood
       WHERE misb.inventory_item_id = moqd.inventory_item_id
         AND misb.organization_id = moqd.organization_id
         AND ood.organization_id = misb.organization_id
         AND moqd.locator_id = mil.inventory_location_id
         AND misb.segment1 IN (SELECT part_no
                                 FROM xx_item_temp )
         AND ood.organization_code IN (SELECT org_name
                                        FROM xx_item_temp)
         AND misb.inventory_item_id = 66661
          GROUP BY 1, 1, 1, 1, sysdate, 1150, SYSDATE, 1150,1150,
             misb.inventory_item_id, misb.organization_id,
             NVL ( apps.xxhmi_interface_utility_pkg.check_on_hand (
                     misb.organization_id,
                     misb.inventory_item_id),
                  0
               ),
             transaction_uom_code, SYSDATE, moqd.subinventory_code,
             inventory_location_id, 100001,3  
            ) 

Step 4: Run the concurren program Name   ---  Go to particulare responsibility
                     
       'Process Transaction Interface'

---------------------------------------------------------------------------------------------------------------