API to load Item Cross References

API to load Item Cross References

Oracle haven't provided standard interface program to load the item cross reference. Below API will help to load the Item cross references into oracle tables.

Note : Standard Interface program is available for the Customer Item Cross reference. IN this post we are refers the Item Cross Reference.

If anybody aware of Standard interface program for "Item Cross Reference", Please share with us, we will update the same in the blog.

      SELECT msi.inventory_item_id
        INTO vl_inventory_item_id,
        FROM mtl_system_items_b msi
       WHERE msi.segment1          = pi_item
         AND msi.organization_id   = pi_org_id
         AND msi.enabled_flag      = 'Y'
         AND TRUNC(SYSDATE) BETWEEN NVL(start_date_active, TRUNC(SYSDATE)) AND NVL(end_date_active, TRUNC(SYSDATE+1)) ;

   vl_item_segment1     := 'XXPET_CROSS_REF_ITEM';
   vl_items_description  := 'XXPET_CROSS_REF_ITEM_DESC';

                  vl_xref_table (1).transaction_type          := 'CREATE';
                  vl_xref_table (1).cross_reference_type  := 'XXPET_CROSS_REF_TYPE';
                  vl_xref_table (1).inventory_item_id        := vl_inventory_item_id;
                  vl_xref_table (1).cross_reference          := vl_item_segment1;
                  vl_xref_table (1).description                 := vl_items_description;
                  vl_xref_table (1).org_independent_flag := 'Y';

mtl_cross_references_pub.process_xref (p_api_version       => 1.0,
                                                              p_init_msg_list      => fnd_api.g_true,
                                                              p_commit             => fnd_api.g_true,
                                                              p_xref_tbl             => vl_xref_table,
                                                              x_return_status     => vl_return_status,
                                                              x_msg_count        => vl_msg_count,
                                                              x_message_list      => vl_message_list
                                                             );

              IF vl_return_status = fnd_api.g_ret_sts_success  THEN
                      -- Means all records are loaded successfully.
              ELSIF vl_return_status = fnd_api.g_ret_sts_unexp_error  THEN
                     -- Means all records are Failed.
              ELSE
                 -- Means partial load and partial fail.
                 END IF;


Thanks and Regards ,
Peter Amal Raj

How to Convert Row to Column using SQL query

Many times we get requirement to show the values which are in the same row having different values in the different columns.

Below example can help you on this....

1. Below is the Item price details table, having price for different items for different seller under different currency.

2. The requirement is to display the price of each item based on the seller and currency in a single row as shown below.

3. For this requirement, we could use the MAX function to get the result in simple query.
Below is the query to be used for it.


   SELECT item,
               MAX(DECODE(seller||currency , 'SELLER1USD', price, ''))  "SELLER1-USD",
               MAX(DECODE(seller||currency , 'SELLER1EUR', price, ''))  "SELLER1-EUR",
               MAX(DECODE(seller||currency , 'SELLER1INR', price, ''))  "SELLER1-INR",
               MAX(DECODE(seller||currency , 'SELLER2USD', price, ''))  "SELLER2-USD",
               MAX(DECODE(seller||currency , 'SELLER2EUR', price, ''))  "SELLER2-EUR",
               MAX(DECODE(seller||currency , 'SELLER2INR', price, ''))  "SELLER2-INR"
      FROM xxpet_item_price_details
GROUP BY item
ORDER BY item

Thanks and Regards,
Peter Amal Raj

Intro

I am Peter Ama Raj, having 7 years of experience in Oracle Apps technical.