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

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

R12 - Merging Suppliers

Use the Supplier Merge program to maintain your supplier records. You can use it to merge duplicate suppliers into a single, consolidated supplier and to merge transactions within the same supplier from one supplier site to a different supplier site. You can choose to merge all transactions for a supplier into a new supplier, or you can just choose to merge unpaid invoices.

Merging suppliers is an irreversible action. You cannot reverse the merge once it has completed. Before you initiate the merge program, you should be sure that the action you want to take is final.

Refer the below screen schot. supplier XXDell is having 2 supplier sire Marina and Sam Francisco. Now we are going to merge Marina supplier site.

Run the Supplier Merge Program :

Navigation : Payables responsibility --> Suppliers --> Suppliers Merge

Select the From and To Information and Click on Merge Button. Once you click on Merge, " Supplier Merge Program " will run in back end.  Once Concurrent program " Supplier Merge Program " was ran succesfully verify the supplier information. Now you can find only one site " San Francisco ".

While merging, we need to decide the effect the merge on existing purchase orders and invoices. 

•         For purchase orders, we need indicate whether we want all or none of the existing purchase orders to be updated with the new supplier/site names by checking or unchecking the PO Check Box.

•         For invoices, we can indicate whether we want all, only unpaid, or none of the invoices to be updated with the new supplier/site names.

 

Supplier Level Vs Supplier Site Level Entries

Refer the above diagram and notice that information on the Organization, Contact Directory, Business Classification, Products and Services, Surveys, Receiving, and Payment Details pages is entered only at the supplier level.

Information on the Address Book page is entered only at the supplier site level.

If Multiple Organization Support feature has been enabled, the Accounting page is also entered only at the site level.