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

R12 - API to Create Oracle Sub Inventories

Hi All,

There is no API available to create Sub Inventories in oracle. We have to directly update the   mtl_secondary_inventories by using API callled : mtl_secondary_inventories_pkg.insert_row.

R12 - API To Create Locators In Inventory

Hi,
Today i am posting the sample code to create Locators in Inventory.


Data File :
------------------------------


ORGANIZATION_CODE|SEGMENT1|SEGMENT2|SEGMENT3|SEGMENT4|DECRIPTION|SUBINVENTORY|
M1|2|2|2|2|DESCRIPTION_1|Stores|
M1|3|3|3|3|DESCRIPTION_1|Stores|
M1|4|4|4|4|DESCRIPTION_1|Staging|
M1|5|5|5|5|DESCRIPTION_1|Staging|

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



DECLARE
l_msg_data              VARCHAR2(100);
l_msg_count             NUMBER;
l_return_status         VARCHAR2(1);
l_locator_id            NUMBER;
l_locator_exists        VARCHAR2(1);
l_org_id                NUMBER := 207;
l_organization_code     VARCHAR2(10) := 'M1';
l_sub_code              VARCHAR2(10) ;
l_concatenated_segments VARCHAR2(100); /

BEGIN

    l_concatenated_segments := '2.2.2.2';  --  SEGMENT1.SEGMENT2.SEGMENT3.SEGMENT4
    l_sub_code := 'Stores';
    l_org_id:= 141;

    INV_LOC_WMS_PUB.CREATE_LOCATOR(
            x_return_status             => l_return_status,
            x_msg_count                 => l_msg_count,
            x_msg_data                  => l_msg_data,
            x_inventory_location_id     => l_locator_id,
            x_locator_exists            => l_locator_exists,
            p_organization_id           => l_org_id,
            p_organization_code         => l_organization_code,
            p_concatenated_segments     => l_concatenated_segments,
            p_description               => 'TEST',
            p_inventory_location_type   => 3,         -- Storage locator
            p_picking_order             => NULL,
            p_location_maximum_units    => NULL,
            p_subinventory_code         => l_sub_code,
            p_location_weight_uom_code  => NULL,
            p_max_weight                => NULL,
            p_volume_uom_code           => NULL,
            p_max_cubic_area            => NULL,
            p_x_coordinate              => NULL,
            p_y_coordinate              => NULL,
            p_z_coordinate              => NULL,
            p_physical_location_id      => NULL,
            p_pick_uom_code             => NULL,
            p_dimension_uom_code        => NULL,
            p_length                    => NULL,
            p_width                     => NULL,
            p_height                    => NULL,
            p_status_id                 => 1, -- Default status 'Active'
            p_dropping_order            => NULL
            );
         
         
       DBMS_OUTPUT.PUT_LINE('Return Status '||l_concatenated_segments||' - '||l_return_status);
     
END;

R12 - AP Auto Invoice Creation

Hi friends,

Today we will see how to allow system to create invoice automatically upon reciept.

1. By Using Self Billing :

By Using selg Billing tab in supplier form, we can create standard invoice upon receipt.

Navigation : Payable responsibility --> Suppliers --> Entry

Query with supplier name . Ex : Supplier Name : Consolidated Supplies

After query with supplier, clisk on purchasing  --> Self Billing Tab

Select Pay On = Reciept  --  This means upon completion of reciepts , system automatically create the STANDARD Invoice.

System will create standard invoice automaticaly and invoice number generation is depends on the value entered in Profile :  PO: ERS Invoice Number Prefix

2. By Using  "Create Debit Memo from RTS Transaction" :

By using this option we can create DEBIT Invoice automatically after returning the material to supplier.

Step1 : Create PO against supplier : Consolidated Supplies and approve the PO

PO Number : 6047  --  Quantity = 9

Step 2 : Reciepts

Navigation : Purchasing responsibility --> recieving --> Reciepts

Query with PO Number and create reciept.

Receipt Nimber : 8462

Step 3 : Return The material . Qty : 4

Navigation : Purchasing responsibility --> recieving --> Return 

Query With receipt = 8462

Enter Qty = 4 and Return To = Supplier  and save -- We are return 4 qty of material to supplier.

Now, Use below Query to find Invoices :

SELECT ph.segment1 PO_NUMBER,ai.invoice_num,
,ai.sourceai.invoice_type_lookup_code,ai.description,ai.invoice_amount,rh.receipt_numFROM
po_headers_all ph ,po_lines_all pl,po_distributions_all pd,rcv_shipment_headers rh,rcv_shipment_lines rl,ap_invoices_all ai,ap_invoice_distributions_all aidWHERE ai.invoice_id = aid.invoice_idAND aid.po_distribution_id = pd.po_distribution_idAND pd.po_line_id = pl.po_line_idAND pl.po_header_id = ph.po_header_idAND rl.po_header_id = ph.po_header_idAND rh.shipment_header_id = rl.shipment_header_idAND rl.po_distribution_id = pd.po_distribution_idAND ph.segment1 = '6047';