FND_GLOBAL.APPS_INITIALIZE() process in R12

FND_GLOBAL.APPS_INITIALIZE

begin
     fnd_client_info.set_org_context(l_org_id);
end;

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

l_org_id := fnd_profile.VALUE('ORG_ID');

mo_global.init ('ONT');
mo_global.set_policy_context('S',l_org_id);



FND_GLOBAL.APPS_INITIALIZE is used for initializing the session before calling any public or private API's in Oracle Ebusiness suite. Its not required for all the API's but its recommended that you set this profile before making any calls to either private or public API. 
Listed below is a sample call to FND_GLOBAL.APPS_INITIALIZE function
fnd_global.APPS_INITIALIZE(user_id=>l_user_id, 
                                                   resp_id=>l_resp_id, 
                                                resp_appl_id=>l_resp_appl_id);

1. l_user_id is the fnd user ID which will be utilized during the call.
2. l_resp_id is the responsibility ID
3. l_resp_appl_id is the responsibility application ID.

You can use either sysadmin or use some user who has all the above listed responsibilities.
For SYSADMIN, utilize the following query to get the respective values
select fnd.user_id , 
       fresp.responsibility_id, 
       fresp.application_id 
from   fnd_user f
,      fnd_responsibility_tl res
where  f.user_name = 'SYSADMIN' 
and    res.responsibility_name = 'Oracle Inventory';

How to Create Code Combination ID by API

--Create Function
CREATE OR REPLACE FUNCTION APPS.XXX_CREATE_CCID
( P_CONCAT_SEGS IN VARCHAR2
) RETURN VARCHAR2
IS
L_STATUS BOOLEAN;
L_COA_ID NUMBER;
BEGIN
SELECT CHART_OF_ACCOUNTS_ID
INTO L_COA_ID
FROM GL_SETS_OF_BOOKS
WHERE SET_OF_BOOKS_ID = 2021; –UPDATE THIS WITH SET OF BOOKS ID

L_STATUS := FND_FLEX_KEYVAL.VALIDATE_SEGS(
‘CREATE_COMBINATION’,
‘SQLGL’,
‘GL#’,
L_COA_ID,
P_CONCAT_SEGS,
‘V’,
SYSDATE,
‘ALL’, NULL, NULL, NULL, NULL,
FALSE,FALSE, NULL, NULL, NULL);
IF L_STATUS THEN
RETURN ‘S’;
ELSE
RETURN ‘F’;
END IF;
END ;
/

--EXECUTE FUNCTION
DECLARE
RETVAL VARCHAR2(200);
P_CONCAT_SEGS VARCHAR2(200); /* ’10.2001.2211101.987872.001.0000′ THIS COMBINATION I WANT TO CREATE */
BEGIN
RETVAL := APPS.XXX_CREATE_CCID ( P_CONCAT_SEGS );
COMMIT;
END;

How to find error message comming in oracle API's

After the calling of API give the following
IF (fnd_msg_pub.count_msg > 0)THEN
FOR i IN 1..fnd_msg_pub.count_msg
LOOP

fnd_msg_pub.get
( p_msg_index => i,
p_encoded => 'F',
p_data => x_msg_data,
p_msg_index_out => X_msg_count
);

DBMS_OUTPUT.PUT_LINE('API ERROR: ' || x_msg_data);
END LOOP;
dbms_output.put_line(x_jtf_note_id ||'--'|| x_return_status ||'--'|| X_msg_count ||'--'|| x_msg_data);
ELSE
DBMS_OUTPUT.PUT_LINE('Updated sr : ' || to_char(ChildIncId));
End if;

http://programmerslounge.blogspot.com/2009/04/how-to-find-error-message-comming-in.html

Oracle APIs and Script to find Oracle APIs

You can find all oracle standard API at following link.  http://irep.oracle.com/index.html

Following script and get all the packages related to API in Oracle applications, from which you can select APIs that pertain to AP. You can change the name like to PA or AR and can check for different modules

select substr(a.OWNER,1,20)
, substr(a.NAME,1,30)
, substr(a.TYPE,1,20)
, substr(u.status,1,10) Stat
, u.last_ddl_time
, substr(text,1,80) Description
from dba_source a, dba_objects u
WHERE 2=2
and u.object_name = a.name
and a.text like ‘%Header%’
and a.type = u.object_type
and a.name like ‘PA_%API%’
order by
a.owner, a.name;

Creating Customer Address in TCA - Step by Step

To create an address for Customer using APIs [so that you can invoice them], is a multi-step task as outlined in brief below.

Addresses in Oracle TCA are stored in table named HZ_LOCATIONS.

To create Locations, Oracle TCA provides an API named hz_location_v2pub.create_location
This API returns Location_id once a physical address has been created.

The location_id can then be used to create Party Site. 
To create party site, use hz_party_site_v2pub.create_party_site.
When calling this API, we can pass "Party Site name" alongside the location_id to which this site is attached..
This API then returns Party_site_id.

This Party_site_id can further be passed to another API hz_party_site_v2pub.create_party_site_use, which is where we specify Site Usage like Correspondance/Bilable etc.


Next thing is to make a Customer Site, so that you can start billing to the customer.
Lets say you already know the customer Account Id[assuming it already exists ] and the Party Site id(as created above).
Now to create the Customer Site, use the API call as below.

declare
px_customer_acount_site_record hz_cust_account_site_v2pub.cust_acct_site_rec_type;
begin
         px_customer_acount_site_record.cust_account_id := 90000;--lets assume
         px_customer_acount_site_record.party_site_id := 4323342;--the party site created above 
         px_customer_acount_site_record.LANGUAGE := 'US';
         px_customer_acount_site_record.created_by_module := 'XX_SIEBEL';
         px_customer_acount_site_record.orig_system_reference :=
                                                  v_sibel_custom_site_num || 'CALLCENTRE';
         hz_cust_account_site_v2pub.create_cust_acct_site (FND_API.G_TRUE,
                                                           px_customer_acount_site_record,
                                                           n_cust_acct_site_id, --returned
                                                           v_return_status, --To indicagte success
                                                           n_msg_count, --
                                                           v_msg_data);
end ;
      
The last step would be to create Customer Site Usage by calling API hz_cust_account_site_v2pub.create_cust_site_use

WEB ADI Creation (using API)

1: Create a table
   Where WEB ADI will store the data. Table might be created in Custom schema.

2. Create a Package.
   (It has to be in APPS Schema)
   In this case XX_TRAVEL_EXPENSE_ADI_PKG package,
   procedure XX_TRAVEL_EXPENSE_PRC.
** Note XX_TRAVEL_EXPENSE_PRC parameters should start with p_
(

      p_company             VARCHAR2,
      p_vendor_name         VARCHAR2,
      p_invoice_num         VARCHAR2,
      p_inv_currency        VARCHAR2,
      p_invoice_date        DATE,

)
then only prompt will automatically appear company , vendor_name, invoice_num, inv_currency, invoice_date.

3. Once the database object is created,  then create integrator.
DECLARE
   ln_application_id    NUMBER;
   lc_integtr_code      VARCHAR2 (50);
   lx_interface_code    VARCHAR2 (50);
   lx_param_list_code   VARCHAR2 (50);
   ln_application_id    NUMBER;
   ln_ret       number;
  
  --Create integrator 
   BEGIN
      bne_integrator_utils.create_integrator
          (p_application_id            => 20003,
           p_object_code               => 'XX_TRAVEL_INV',
           p_integrator_user_name      => 'XX Travel Invoice Upload WEB ADI',
           p_language                  => 'US',
           p_source_language           => 'US',
           p_user_id                   => -1,
           p_integrator_code           => lc_integtr_code
          );
      DBMS_OUTPUT.put_line ('lc_integtr_code =  ' || lc_integtr_code);
   END;

Check it once it is created

SELECT *
  FROM bne_integrators_b
 WHERE integrator_code LIKE 'XX_TRAVEL_INV%';

4. Create Interface
DECLARE
   ln_application_id    NUMBER;
   lc_integtr_code      VARCHAR2 (50);
   lx_interface_code    VARCHAR2 (50);
   lx_param_list_code   VARCHAR2 (50);
   ln_application_id    NUMBER;
   BEGIN
      bne_integrator_utils.create_interface_for_api
           (p_application_id           => 20003,
            p_object_code              => 'XX_TRAVEL_INV',
            p_integrator_code          => 'XX_TRAVEL_INV_INTG',
            p_api_package_name         => 'XX_TRAVEL_EXPENSE_ADI_PKG',
            p_api_procedure_name       => 'XX_TRAVEL_EXPENSE_PRC',
            p_interface_user_name      => 'XX Travel Invoice Upload WEB ADI',
            p_param_list_name          => 'XX Travel Invoice PL',
            p_api_type                 => 'PROCEDURE',
            p_api_return_type          => NULL,
            p_upload_type              => 2,
            p_language                 => 'US',
            p_source_lang              => 'US',
            p_user_id                  => -1,
            p_param_list_code          => lx_param_list_code,
            p_interface_code           => lx_interface_code
           );
      DBMS_OUTPUT.put_line ('lx_interface_code  =   ' || lx_interface_code);
      DBMS_OUTPUT.put_line ('lx_param_list_code =   ' || lx_param_list_code);
   --EXCEPTION
   --   WHEN OTHERS
   --   THEN
   --      DBMS_OUTPUT.put_line ('Error  =      ' || SQLERRM);
   END;

Check Interface

SELECT *
  FROM bne_interface_cols_tl
 WHERE interface_code LIKE 'XX%' ;

SELECT *
  FROM bne_interface_cols_vl
 WHERE interface_code LIKE 'XX%';

SELECT *
  FROM bne_interface_cols_b
 WHERE interface_code LIKE 'XX%'


4. Create an ADI function with following values:

FunctionName UserFunctionName Description         Type                MaintainanceMode ContextDependence
************************************************************************************************************************
<User defined>  <User defined> <User defined>  SSWA servlet function None Responsibility

Form Application
*********************************************************************************
Parameter
***************************************
<none> <none> bne:page=BneCreateDoc&bne:noreview=true&bne:integrator=20003:GENERAL%25&bne:reporting=N

HTML Call Host Name
*********************************************************************************
BneApplicationService http://le0003.oracleads.com:80


5. Use the ORACLE WEB ADI responsibility and go to DEFINE LAYOUT option

Give LAYOUT name
From the drop down select the INTERGRATOR USER NAME as defined above and click on 'GO'
A screen with all the procedure parameters will appear.
Select 'LINE' as placement value for all the parameters and APPLY

7. Create and entry against the USER FUNCTION NAME in the menu under which ADI needs to be accessed.
ADI is ready to use.



Additional Steps:
Add Date LOV in the WEB ADI Column


DECLARE
   ln_application_id    NUMBER;
   lc_integtr_code      VARCHAR2 (50);
   lx_interface_code    VARCHAR2 (50);
   lx_param_list_code   VARCHAR2 (50);
   ln_application_id    NUMBER;
BEGIN
   bne_integrator_utils.create_calendar_lov
                                (p_application_id          => 20003,
                                 p_interface_code          => 'XX_TRAVEL_INV_INTF',
                                 p_interface_col_name      => 'P_INVOICE_DATE',--proc params
                                 p_window_caption          => 'Select Date',
                                 p_window_width            => 400,
                                 p_window_height           => 300,
                                 p_table_columns           => 'INVOICE_DATE',
                                 p_user_id                 => 10803
                                );
END;

ADD Lov in the fields,

DECLARE
   ln_application_id    NUMBER;
   lc_integtr_code      VARCHAR2 (50);
   lx_interface_code    VARCHAR2 (50);
   lx_param_list_code   VARCHAR2 (50);
   ln_application_id    NUMBER;  
BEGIN
   bne_integrator_utils.create_table_lov
                                (p_application_id          => 20003,
                                 p_interface_code          => 'XX_TRAVEL_INV_INTF',
                                 p_interface_col_name      => 'P_INV_CURRENCY',
                                 p_id_col                  => 'CURRENCY_CODE',
                                 p_mean_col                => 'CURRENCY_CODE',
                                 p_desc_col                => 'CURRENCY_CODE',                               
                                 p_table                   => 'GL_CURRENCIES',
                                 p_addl_w_c                => 'NVL (ENABLED_FLAG, ''N'') = ''Y''',
                                 p_window_caption          => 'Select Currency',
                                 p_window_width            => 400,
                                 p_window_height           => 300,
                                 p_table_block_size        => 10,
                                 p_table_sort_order        => 'Yes',
                                 p_user_id                 => 0
                                );
END;

Other usefull API:

1. Delete Integrator

DECLARE

   v_value   NUMBER;

BEGIN

   v_value :=

      bne_integrator_utils.delete_integrator (20003, 'XX_TRAVEL_INV_INTG');

   DBMS_OUTPUT.put_line (v_value);

END;

2. delete Interface

DECLARE

   v_value   NUMBER;

BEGIN

   v_value :=

      bne_integrator_utils.DELETE_INTERFACE (20003, 'XX_TRAVEL_INV_INTF');

   DBMS_OUTPUT.put_line (v_value);

END; 

Change the Prompt lebel,

UPDATE bne_interface_cols_tl

      SET prompt_left = 'Company',

          prompt_above = 'Company',

          user_hint = '*List - Text'

    WHERE prompt_left = 'COMPANY'

      AND interface_code = 'XX_TRAVEL_INV_INTF'

      AND LANGUAGE = 'US';

 UPDATE bne_interface_cols_tl

      SET prompt_left = 'Invoice Date',

          prompt_above = 'Invoice Date',

          user_hint = '*List - Date'

    WHERE prompt_left = 'INVOICE_DATE'

      AND interface_code = 'XX_TRAVEL_INV_INTF'

      AND LANGUAGE = 'US';