Cycle Counting Overview

Hi Friends,

Today i am posting Overview Of Cycle Counting in Inventory. In next post, We will discuss in detail.

R12 - Upload Item Specific UOM Conversion

Hi,

Today i am posting how to upload item specific UOM conversion rates by using API.

Procedure : CREATE_UOM_CONVERSION  Is introduced in R12.1.3.

DECLARE
v_from_uom       VARCHAR2(200);
v_to_uom         VARCHAR2(200);
v_item_id        NUMBER;
v_uom_rate       NUMBER;
x_return_status  VARCHAR2(99);

BEGIN

  v_from_uom := 'DOZEN'; 
  v_item_id := '9999';
  v_uom_rate := '9';
  v_to_uom := 'EA';


  INV_CONVERT.CREATE_UOM_CONVERSION
                  (
                    p_from_uom_code => v_from_uom,
                    p_to_uom_code   => v_to_uom,
                    p_item_id       => v_item_id,
                    p_uom_rate      => v_uom_rate,
                    x_return_status => x_return_status
                  );
END;

R12 - Query to find Supplier, Sites and Contact Information

Hi Friends,

Today i am posting Query to find Supplier, Sites and Contact Information.

AP_SUPPLIERS                    
AP_SUPPLIER_SITES_ALL
AP_SUPPLIER_CONTACTS

In R12, Contact person information is stored in HZ_PARTIES table not in ap_supplier_contacts.

SELECT asp.vendor_id ,
asp.segment1 "Supplier Num" ,
asp.vendor_name "Supplier Name" ,
ass.vendor_site_code "site name" ,
hou.name "Operating Unit Name" ,
ass.address_line1 ,
ass.city ,
ass.state ,
ass.zip ,
ass.country ,
ass.phone ,
person.person_first_name ,
person.person_last_name ,
pty_rel.primary_phone_number ,
pty_rel.email_address
FROM ap_suppliers asp ,
ap_supplier_sites_all ass ,
ap_supplier_contacts apsc ,
hz_parties person ,
hz_parties pty_rel,
hr_operating_units hou
WHERE ass.vendor_id = asp.vendor_id
AND apsc.per_party_id = person.party_id
AND apsc.rel_party_id = pty_rel.party_id
AND ass.org_id = hou.organization_id
AND apsc.org_party_site_id = ass.party_site_id
AND asp.vendor_name = 'LS Supplier';

R12 - API To Assign Catalog Group To Item

Hi All,

Here is the process to attach catalog group to an item through interface.

1. Insert the data into mtl_system_items_interface.

      INSERT INTO mtl_system_items_interface
                 (inventory_item_id,
                  organization_id,
                  process_flag,
                  set_process_id,
                  transaction_type,
                  item_catalog_group_id
                  )
            VALUES
                 (9999,
                  207,
                  1,
                  1,
                  'UPDATE',
                 9
                  );

2. Run Item Import Program.

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
                       );