ERRBUF and RETCODE in ORACLE APPS CONCURRENT PROGRAMS

 First time while registering the concurrent programs we must forget the 2 mandatory parameters. After the error we can know that we need to you those mandatory parameters those are ERRBUF and RETCODE.
These are really good if you use properly.

ERRBUF: It return the error message. For you program if you get any error in exception block you can assign the error message to this parameter. This error message you can see after concurrent program run go to details button it will open details in that Completion Text filed will show your errbuf.

RETCODE: This parameter returns the status of the concurrent program.
0- Success --Completed
1- Warning -- Yellow color
2- Error -- Red

These parameters we call as a first parameters for the program.

Ex:
Create procedure  concurren1(ERRBUF out varchar2, RETCODE  out varchar2, v_order_id in varchar2)
as
begin...
...
begin
..
exception
when no_data_found then
retcode := 1;
errbuf:= 'No data found for this query';
end;
...
...
...
retcode:= 0;
commit;
exception
when others then

retcode := 2;

errbuf:= 'Unexpected Error '||SQLERRM;

end;

I tried to explain the steps to IMPORT an ITEM with its attributes based on ITEM TEMPLATE in R12 Oracle Apps.

STEP1: Get the template id using the below query 

SELECT template_id

      ,template_name

      ,description

FROM   MTL_ITEM_TEMPLATES; 

STEP2: Check the choosed template's related item attribute values using the below query

SELECT template_id

      ,attribute_name

      ,enabled_flag

      ,report_user_value

FROM   MTL_ITEM_TEMPL_ATTRIBUTES

WHERE  template_id = 107; 

STEP3:  Run the below insert script to create a record in the standard item interface table to create a item based on item template 

INSERT

INTO

  MTL_SYSTEM_ITEMS_INTERFACE

  (

    process_flag,

    set_process_id,

    transaction_type,

    organization_id,

    segment1,

    description,

    TEMPLATE_ID

  )

  VALUES

  (

    1,

    1,

    'CREATE',

    204,

    'TESTITEM01',

    'Testing Item Import With Template',

    107

  );

COMMIT; 

STEP4: Run the wrapper script given in the below link to submit the "ITEM IMPORT" concurrent program from backend  Wrapper Script to Submit Item Import 

  

STEP5: Run the below query to verify the creation of the item 

SELECT *

FROM   mtl_system_items_b

WHERE  segment1 = 'TESTITEM01';

Changing Password for an Oracle User in R12 Oracle Apps using API--

DECLARE
   v_user_name          VARCHAR2 (100) := 'TEST_USER';
   v_new_password   VARCHAR2 (100) := :NEWPASSWORD;
   v_status                   BOOLEAN              := NULL;
BEGIN
   v_status := fnd_user_pkg.changepassword (v_user_name, v_new_password);

  COMMIT;
   DBMS_OUTPUT.put_line (   'Password is changed successfully for the user '
                         || v_user_name
                        );
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line
         (   'Error encountered while setting new password to the user and the error is '
          || SQLERRM
         );
END;

Output after executing the API:
Password is changed successfully for the user TEST_USER

Supplier One time insert

Text Data

insert into AP_SUPPlIERS_INT(VENDOR_INTERFACE_ID,VENDOR_NAME) values (ap_suppliers_int_s.NEXTVAL,'ICICI222')

insert into ap_supplier_sites_int(vendor_interface_id,vendor_site_interface_id,VENDOR_SITE_CODE,COUNTRY,ADDRESS_LINE1,org_id)

values(ap_suppliers_int_s.currval,ap_supplier_sites_int_s.NEXTVAL,'Hyderabad','IN','SRNAGAR',204)

select status from ap_suppliers_int where vendor_name = 'ICICI222' --and status is null

select * from ap_supplier_sites_int where vendor_interface_id = 13006 --vendor_site_code = 'Hyderabad'

select * from fnd_user where user_name = 'OPERATIONS'

select * from fnd_responsibility where RESPONSIBILITY_KEY = 'PAYABLES_OPERATION'

select * from ap_suppliers_int

delete from ap_suppliers_int

select * from ap_supplier_sites_int

delete from ap_supplier_sites_int

 concurrent program -- supplier open interface import

 short name-----APXSUIMP

 resp id --50554

 resp_app_id--200

 user_id--1318

 

commit

select * from ap_suppliers where vendor_name='ICICI999'

select * from ap_supplier_sites_all where vendor_id =47189

Submit Request for supplier

=======================================

declare

v_request_id number;

begin

FND_GLOBAL.APPS_INITIALIZE(1318,50554,200);

v_request_id := FND_REQUEST.SUBMIT_REQUEST

                ('SQLAP',

                 'APXSUIMP',

                 null,

                 sysdate,

                 TRUE,

                  'ALL',

                 1000,

                 'N',

                 'N',

                 'N');

if v_request_id > 0 then

dbms_output.put_line('Request submitted successfully');

dbms_output.put_line(v_request_id);

else

dbms_output.put_line('Request submission failure');

dbms_output.put_line('errmsg:-'||sqlerrm);

dbms_output.put_line('errcode:-'||sqlcode);

end if;

commit;

end;

2) submit request for supplier sites

===============================================

declare

v_request_id number;

begin

FND_GLOBAL.APPS_INITIALIZE(1318,50554,200);

v_request_id := FND_REQUEST.SUBMIT_REQUEST

                ('SQLAP',

                 'APXSSIMP',

                 null,

                 sysdate,

                 TRUE,

                  'ALL',

                 1000,

                 'N',

                 'N',

                 'N');

if v_request_id > 0 then

dbms_output.put_line('Request submitted successfully');

dbms_output.put_line(v_request_id);

else

dbms_output.put_line('Request submission failure');

dbms_output.put_line('errmsg:-'||sqlerrm);

dbms_output.put_line('errcode:-'||sqlcode);

end if;

commit;end;

insert into AP_SUP_SITE_CONTACT_INT(LAST_NAME,ORG_ID,VENDOR_CONTACT_INTERFACE_ID,VENDOR_INTERFACE_ID,PHONE,vendor_id,vendor_site_id)                                     

values('nalam',204,AP_SUP_SITE_CONTACT_INT_S.NEXTVAL,ap_suppliers_int_s.currval,9959666537,47192,) 

3) Submit Request for contact Information

===================================================

declare

v_request_id number;

begin

FND_GLOBAL.APPS_INITIALIZE(1318,50554,200);

v_request_id := FND_REQUEST.SUBMIT_REQUEST

                ('SQLAP',

                 'APXSCIMP',

                 null,

                 sysdate,

                 TRUE,

                  'ALL',

                 1000,

                 'N',

                 'N',

                 'N');

if v_request_id > 0 then

dbms_output.put_line('Request submitted successfully');

dbms_output.put_line(v_request_id);

else

dbms_output.put_line('Request submission failure');

dbms_output.put_line('errmsg:-'||sqlerrm);

dbms_output.put_line('errcode:-'||sqlcode);

end if;

commit;end;

supplier Api

===================================================

ap_vendor_pub_pkg

POS_SUPP_CONTACT_PKG

fnd_api

select * from all_tables where table_name like 'FND%REQUEST%'

SELECT * FROM FND_CONCURRENT_REQUESTS WHERE TRUNC(SYSDATE)=TRUNC(REQUEST_DATE)

AND CONCURRENT_PROGRAM_ID = 47176

ORDER BY 1 DESC