, ,

Identify Concurrent Programs attached to Request Groups

SELECT fcpt.user_concurrent_program_name, fcpt.concurrent_program_id,
       frg.request_group_name
  FROM apps.fnd_concurrent_programs_tl fcpt,
       apps.fnd_request_group_units frgu,
       apps.fnd_request_groups frg
 WHERE frgu.request_unit_type = ‘P’           –Program
   AND frgu.request_unit_id = fcpt.concurrent_program_id
   AND frgu.request_group_id = frg.request_group_id
   AND upper(fcpt.user_concurrent_program_name) LIKE upper(‘CONCURRENT_PROGRAM_NAME%’);
/by
, ,

API – Assign Inventory Items to Warehouse

l_api_version   NUMBER := 1.0;
l_init_msg_list VARCHAR2(2) := FND_API.G_TRUE;
l_commit        VARCHAR2(2) := FND_API.G_FALSE;  
SELECT inventory_item_id,
        segment1,
        primary_uom_code
 FROM   mtl_system_items_b
 WHERE  segment1 = v_segment1;  -- INVENTORY ITEM CODE
 
EGO_ITEM_PUB.ASSIGN_ITEM_TO_ORG( 
   P_API_VERSION       =>  l_api_version
,  P_INIT_MSG_LIST     =>  l_INIT_MSG_LIST
,  P_COMMIT            =>  l_COMMIT
,  P_INVENTORY_ITEM_ID =>  itm.inventory_item_id --(item id from the above Query)            
,  P_ITEM_NUMBER       =>  itm.segment1          --(Item Code from the above Query)
,  P_ORGANIZATION_ID   =>  v_organization_id     --(Organization Id for assingment)
,  P_ORGANIZATION_CODE =>  NULL--v_organization_code 
,  P_PRIMARY_UOM_CODE  =>  itm.primary_uom_code  --(UOM from the above Query)
,  X_RETURN_STATUS     =>  X_RETURN_STATUS
,  X_MSG_COUNT         =>  X_MSG_COUNT
 ); 
 
  
/by
, ,

API – User Security Attribute Creation

declare
      x_return_status VARCHAR2(2000);
      x_msg_count NUMBER;
      x_msg_data VARCHAR2(2000);
      x_vendor_site_id number;
begin
icx_user_sec_attr_pub.create_user_sec_attr(
   p_api_version_number   => 1,
   p_return_status        => x_return_status,
   p_msg_count            => x_msg_count,
   p_msg_data             => x_msg_data,
   p_web_user_id          => 1394,
   p_attribute_code       => ‘ICX_SUPPLIER_ORG_ID’,
   p_attribute_appl_id    => 177, —-iSupplier Portal
   p_varchar2_value       => ”,
   p_date_value           => ”,
   p_number_value         => 493,  — Vendor_ID for ICX_SUPPLIER_ORG_ID’and Vendor_site_id for ICX_SUPPLIER_SITE_ID’ attribute code
   p_created_by           => -1,
   p_creation_date        => sysdate,
   p_last_updated_by      => -1,
   p_last_update_date     => sysdate,
   p_last_update_login    => -1);
              if (x_return_status <> ‘S’) then
                    dbms_output.put_line(‘Encountered ERROR in Attribute Creation!!!’);
                    dbms_output.put_line(‘————————————–‘);
                    dbms_output.put_line(x_msg_data);
                            IF x_msg_count > 1 THEN
                                FOR i IN 1..x_msg_count LOOP
                                        dbms_output.put_line(substr(FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE ),1,255));
                                END LOOP;
                            END IF;
            ELSE
                        dbms_output.put_line(‘Attribute has been attached !!!!!!’);
            end if; 
end;
/by
, ,

API – Vendor Site Update

l_vendor_site_rec AP_VENDOR_PUB_PKG.r_vendor_site_rec_type;


  l_vendor_site_rec.vendor_id := v_vendor_id;     — Supplier Id
  l_vendor_site_rec.org_id    := v_org_id;          — Operating Unit id
  x_vendor_site_id            :=v_site_id;             — Site Id to be updated
 
  l_vendor_site_rec.email_address := ‘[email protected]‘;  — eg: Email address update

AP_VENDOR_PUB_PKG.Update_Vendor_Site
                    (   p_api_version     => 1,
                        x_return_status   => x_return_status,
                        x_msg_count       => x_msg_count,
                        x_msg_data        => x_msg_data,
                        p_vendor_site_rec => l_vendor_site_rec,
                        p_vendor_site_id  => x_vendor_site_id
                    );

/by
, ,

API – Supplier & Supplier Site Creation

declare
        l_vendor_rec AP_VENDOR_PUB_PKG.r_vendor_rec_type;
        l_vendor_site_rec AP_VENDOR_PUB_PKG.r_vendor_site_rec_type;
        x_return_status VARCHAR2(2000);
        x_msg_count NUMBER;
        x_msg_data VARCHAR2(2000);
        l_upd number := 0;
        x_vendor_id number;
        x_party_id number;
        x_vendor_site_id number;
        x_party_site_id number;
        x_location_id number;
        l_party_site_id number;
        l_organization_id number;
begin
     –mo_global.set_policy_context(‘S’,1650);
          l_vendor_rec.SEGMENT1     := ‘1234577’;
          l_vendor_rec.VENDOR_NAME  := ‘TESTSUPPLIER’;
          l_vendor_rec.SUMMARY_FLAG := ‘N’;
          l_vendor_rec.ENABLED_FLAG := ‘Y’;
         

 AP_VENDOR_PUB_PKG.Create_Vendor
            ( p_api_version    => 1,
              x_return_status  => x_return_status,
              x_msg_count      => x_msg_count,
              x_msg_data       => x_msg_data,
              p_vendor_rec     => l_vendor_rec,
              x_vendor_id      => x_vendor_id,
              x_party_id       => x_party_id
            );

            if (x_return_status <> ‘S’) then
                    dbms_output.put_line(‘Encountered ERROR in supplier creation!!!’);
                    dbms_output.put_line(‘————————————–‘);
                    dbms_output.put_line(x_msg_data);

                    IF x_msg_count > 1 THEN
                        FOR i IN 1..x_msg_count LOOP
                             dbms_output.put_line(substr(FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE ),1,255));
                        END LOOP;
                    END IF;
            else
            l_vendor_site_rec.vendor_id            := x_vendor_id;
            l_vendor_site_rec.VENDOR_SITE_CODE     := ‘HOME’;
            l_vendor_site_rec.org_id               := 1650;
            l_vendor_site_rec.COUNTRY              := ‘AE’;                
            l_vendor_site_rec.ADDRESS_LINE1        :=’Abu Dhabi’;
            l_vendor_site_rec.PURCHASING_SITE_FLAG := ‘Y’;   
            l_vendor_site_rec.PAY_SITE_FLAG        := ‘Y’;   
             

 AP_VENDOR_PUB_PKG.Create_Vendor_Site
            ( p_api_version    => 1,
            x_return_status    => x_return_status,
            x_msg_count        => x_msg_count,
            x_msg_data         => x_msg_data,
            p_vendor_site_rec  => l_vendor_site_rec,
            x_vendor_site_id   => x_vendor_site_id,
            x_party_site_id    => x_party_site_id,
            x_location_id      => x_location_id
            );

            if (x_return_status <> ‘S’) then
                    dbms_output.put_line(‘Encountered ERROR in supplier site creation!!!’);
                    dbms_output.put_line(‘————————————–‘);
                    dbms_output.put_line(x_msg_data);
                            IF x_msg_count > 1 THEN
                                FOR i IN 1..x_msg_count LOOP
                                        dbms_output.put_line(substr(FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE ),1,255));
                                END LOOP;
                            END IF;
            ELSE
                        dbms_output.put_line(‘Supplier Site Created!!!’);

            end if; 
           end if;  
end;

/by