,

API – Customer Additional Information (Price List , Payment Terms ,Sales Type )

CREATE OR REPLACE procedure APPS.XX_CUSTOMER_ADDITIONAL_LOAD
IS
p_cust_site_use_rec  HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_SITE_USE_REC_TYPE;
x_return_status      VARCHAR2(2000);
x_msg_count          NUMBER;
xio_p_object_version NUMBER;
x_msg_data           VARCHAR2(2000);
v_site_use_id        NUMBER;
V_OBJECT_VERSION     NUMBER;
BEGIN

--    FND_GLOBAL.APPS_INITIALIZE(<user_id>,<resp_id>,<resp_applicarion_id>);
--    MO_GLOBAL.INIT('AR');
--    MO_GLOBAL.SET_POLICY_CONTEXT('S', <org_id>);
     FND_GLOBAL.APPS_INITIALIZE(1535,50930,222);
     MO_GLOBAL.INIT('AR');
     MO_GLOBAL.SET_POLICY_CONTEXT('S', 1210);   
     BEGIN
            SELECT  HCSU.SITE_USE_ID,
                    HCSU.OBJECT_VERSION_NUMBER
            INTO    V_SITE_USE_ID,
                    V_OBJECT_VERSION
            FROM    HZ_PARTIES HP,
                    HZ_PARTY_SITES HPS,
                    HZ_CUST_ACCT_SITES_ALL HCAS,
                    HZ_CUST_SITE_USES_ALL HCSU
            WHERE   HP.PARTY_ID = HPS.PARTY_ID
            AND     HPS.PARTY_SITE_ID = HCAS.PARTY_SITE_ID
            AND     HCAS.CUST_ACCT_SITE_ID =HCSU.CUST_ACCT_SITE_ID
            AND     HCSU.SITE_USE_CODE = 'BILL_TO'
            AND     ltrim(rtrim(upper(HP.PARTY_NAME)))= ltrim(rtrim(upper('SSE Test Customer')));
     EXCEPTION WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('ERROR IS'||SUBSTR(SQLERRM,1,150));            
     END;
   
     p_cust_site_use_rec.site_use_id:=  V_SITE_USE_ID;             -- Site USe to be updated
     xio_p_object_version           :=  V_OBJECT_VERSION;          --xio_p_object_version := 1;
     p_cust_site_use_rec.price_list_id   := 98202;              -- SSE Standard
     p_cust_site_use_rec.payment_term_id := 1000 ;              -- 90 Days
     p_cust_site_use_rec.order_type_id   := 1193;               --SSE Duabi Showroom Cash Sales
     hz_cust_account_site_v2pub.update_cust_site_use(
                                                    'T',
                                                    p_cust_site_use_rec,
                                                    xio_p_object_version,
                                                    x_return_status,
                                                    x_msg_count,
                                                    x_msg_data);


    dbms_output.put_line('***************************');
    dbms_output.put_line('Output information ....');
    dbms_output.put_line('x_return_status: '||x_return_status);
    dbms_output.put_line('x_msg_count: '||x_msg_count);
    dbms_output.put_line('xio_p_object_version: '||xio_p_object_version);
    dbms_output.put_line('x_msg_data: '||x_msg_data);
    dbms_output.put_line('***************************');    
    COMMIT;
END;
/
/by
, ,

API – Update Inventory Item Template

declare
 l_item_table      ego_item_pub.item_tbl_type;
   x_item_table      ego_item_pub.item_tbl_type;
   x_return_status   VARCHAR2 (1);
   x_msg_count       NUMBER (10);
   x_msg_data        VARCHAR2 (1000);
   x_message_list    error_handler.error_tbl_type;
BEGIN
           l_item_table (1).transaction_type := ‘UPDATE’;
       l_item_table (1).inventory_item_id := 64;  –INVENTORY_ITEM_ID;
       l_item_table (1).organization_id := 101; — I.ORGANIZATION_ID;
       l_item_table (1).template_id := 1;– I.NEW_TEMPLATE_ID;
       ego_item_pub.process_items (p_api_version        => 1.0,
                       p_init_msg_list      => fnd_api.g_true,
                       p_commit             => fnd_api.g_true,
                       p_item_tbl           => l_item_table,
                       x_item_tbl           => x_item_table,
                       x_return_status      => x_return_status,
                       x_msg_count          => x_msg_count
                      );
     
       DBMS_OUTPUT.PUT_LINE (‘Return Status ==>’ || x_return_status);
       DBMS_OUTPUT.PUT_LINE (‘Error Messages :’);
      
           error_handler.get_message_list (x_message_list => x_message_list);

           FOR i IN 1 .. x_message_list.COUNT
           LOOP
              DBMS_OUTPUT.PUT_LINE (x_message_list (i).MESSAGE_TEXT);
           END LOOP;

END;

/by
, ,

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 – 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