Oracle Apps API's

Oracle Apps API's

Contents Included in this Page:

1- API to Create Supplier

2- API to Update Salary - PER_PAY_PROPOSALS

3- API to create apps user.

4- API to create bank account.

5- API to create Employee.

6- API to reset application password

7- API to get description of GL Account

8- API to initiate Apps session from Toad or sql.

9- API to register Executable

10- API to register Concurrent Program

11- API to attach Concurrent Porgram to Request Group
12- API to create inventory item.
13-API to update inventory item's Template

** We can get complete details of every module's API in its Implementation guide.
** Tables related information and its constraints can get from etrm.oracle.com

Details:

1- API to Create Supplier 

DECLARE

   l_vendor_rec       ap_vendor_pub_pkg.r_vendor_rec_type;

   l_return_status   VARCHAR2(10);

   l_msg_count       NUMBER;

   l_msg_data         VARCHAR2(1000);

   l_vendor_id        NUMBER;

   l_party_id           NUMBER;

BEGIN

   -- Required

   l_vendor_rec.segment1          := '0000235916';    

  --* If the segment1 is auto generated than this no will not be

  -- updated and the auto generated will come.

   l_vendor_rec.vendor_name   := 'TEST_SUPP';

   -- Optional

   l_vendor_rec.match_option  :='R';

   pos_vendor_pub_pkg.create_vendor

   (

        -- Input Parameters

        p_vendor_rec      => l_vendor_rec,

        -- Output Parameters

        x_return_status   => l_return_status,

        x_msg_count       => l_msg_count,

        x_msg_data         => l_msg_data,

        x_vendor_id        => l_vendor_id,

        x_party_id           => l_party_id

   );

 

Commit;

Exception   When Others then Rollback;

Dbms_output.put_line(Sqlerrm);

End;

2- API to Update Salary - PER_PAY_PROPOSALS

DECLARE
   l_inv_next_sal_date_warning   BOOLEAN;

   DBMS_OUTPUT.put_line (i.pay_proposal_id || ' has been Updated !!!!');

CURSOR c1

      IS

      SELECT * FROM per_pay_proposals;

   l_proposed_salary_warning     BOOLEAN;

   l_approved_warning            BOOLEAN;

   l_payroll_warning             BOOLEAN;

BEGIN

   
   FOR i IN c1 LOOP

      l_inv_next_sal_date_warning := NULL;

      l_proposed_salary_warning := NULL;

      l_approved_warning := NULL;

      l_payroll_warning := NULL;

      hr_maintain_proposal_api.update_salary_proposal 

      (

         p_pay_proposal_id             => i.pay_proposal_id,

         p_change_date                 => i.change_date,

         p_proposed_salary_n           => 10000,

         p_object_version_number       => i.object_version_number,

         p_multiple_components         => 'N',

         p_approved                    => 'Y',

         p_validate                    => FALSE,

         p_inv_next_sal_date_warning   => l_inv_next_sal_date_warning,

         p_proposed_salary_warning     => l_proposed_salary_warning,

         p_approved_warning            => l_approved_warning,

         p_payroll_warning             => l_payroll_warning

      );

   End Loop;

       

         Exception   When Others then Rollback;

Dbms_output.put_line(Sqlerrm);

End;

3 - API to CREATE FND User

Declare

lc_user_name                      VARCHAR2(100)   := 'Mrahman';

lc_user_password               VARCHAR2(100)   := 'Oracle123';         

ld_user_start_date               DATE                     := TO_DATE('23-JUN-2012');  

ld_user_end_date                VARCHAR2(100)  := NULL;  

ld_password_date               VARCHAR2(100)  := TO_DATE('23-JUN-2012');  

ld_password_lifespan_days  NUMBER              := 90;  

ln_person_id                         NUMBER              := 32979;
lc_email_address                  VARCHAR2(100) := 'mrahman@test.com';

BEGIN

fnd_user_pkg.createuser   

( 

x_user_name    => lc_user_name,     

x_owner  => NULL,    

x_unencrypted_password     => lc_user_password,    

x_start_date                              => ld_user_start_date,    

x_end_date                               => ld_user_end_date,    

x_password_date                    => ld_password_date,    

x_password_lifespan_days   => ld_password_lifespan_days,    

x_employee_id                        => ln_person_id,    

x_email_address                     => lc_email_address 

); 

Commit;

Exception   When Others then Rollback;

Dbms_output.put_line(Sqlerrm);

End;

4 -- API to Create Bank

DECLARE    lc_output                VARCHAR2(3000);
   lc_msg_dummy     VARCHAR2(3000);
   lc_return_status     VARCHAR2(3000);
   lc_msg_data           VARCHAR2(3000);

   ln_bank_id             NUMBER;
   ln_msg_count        NUMBER;
   lr_extbank_rec       apps.iby_ext_bankacct_pub.extbank_rec_type;
   lr_response_rec     apps.iby_fndcpt_common_pub.result_rec_type;

BEGIN    lc_return_status    := '';
   ln_msg_count       := '';
   lc_msg_data          := '';

   lr_extbank_rec.bank_name       := 'Test Supp Bank';
   lr_extbank_rec.bank_number   := 'TSB0000001';
   lr_extbank_rec.country_code    := 'US';

   apps.fnd_msg_pub.delete_msg(NULL);
   apps.fnd_msg_pub.initialize();

   IBY_EXT_BANKACCT_PUB.create_ext_bank
   (       -- ------------------------------            -- Input data elements
           -- ------------------------------            p_api_version       => 1.0,
           p_init_msg_list     => FND_API.G_TRUE,
           p_ext_bank_rec     => lr_extbank_rec,
           -- --------------------------------
           -- Output data elements
           -- --------------------------------            x_bank_id            => ln_bank_id,
           x_return_status   => lc_return_status,
           x_msg_count       => ln_msg_count,
           x_msg_data         => lc_msg_data,
           x_response          => lr_response_rec      );

     lc_output := ' ';

     IF (lc_return_status <> 'S')
     THEN
               FOR i IN 1 .. ln_msg_count
               LOOP
                            apps.fnd_msg_pub.get
                            (       i,
                                    apps.fnd_api.g_false,
                                    lc_msg_data,
                                    lc_msg_dummy
                             );

                            lc_output := lc_output ||
                                                  (TO_CHAR (i) ||
                                                                       ': '  ||
                                                   SUBSTR (lc_msg_data, 1, 250));
             END LOOP;

             apps.fnd_file.put_line
            (apps.fnd_file.output, 'Error Occured while Creating Bank: ');

       END IF;

     COMMIT; 

EXCEPTION           WHEN OTHERS THEN
                        ROLLBACK; 
                        dbms_output.put_line(SQLERRM); 

END; 

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

5 - Oracle HRMS API – Create Employee

API - hr_employee_api.create_employee

Example --

 -- Create Employee
 -- ------------------------- 

DECLARE

 lc_employee_number                       PER_ALL_PEOPLE_F.EMPLOYEE_NUMBER%TYPE    := 'TestEmp';
 ln_person_id                                      PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
 ln_assignment_id                             PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID%TYPE;
 ln_object_ver_number                     PER_ALL_ASSIGNMENTS_F.OBJECT_VERSION_NUMBER%TYPE;
 ln_asg_ovn                                          NUMBER;

 ld_per_effective_start_date             PER_ALL_PEOPLE_F.EFFECTIVE_START_DATE%TYPE;
 ld_per_effective_end_date              PER_ALL_PEOPLE_F.EFFECTIVE_END_DATE%TYPE;
 lc_full_name                                        PER_ALL_PEOPLE_F.FULL_NAME%TYPE;
 ln_per_comment_id                          PER_ALL_PEOPLE_F.COMMENT_ID%TYPE;
 ln_assignment_sequence                 PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_SEQUENCE%TYPE;
 lc_assignment_number                    PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_NUMBER%TYPE;

 lb_name_combination_warning   BOOLEAN;
 lb_assign_payroll_warning           BOOLEAN;
 lb_orig_hire_warning                       BOOLEAN; 

BEGIN 
           hr_employee_api.create_employee
           (   -- Input data elements
               -- ------------------------------
               p_hire_date                                         => TO_DATE('08-JUN-2011'),
               p_business_group_id                      => fnd_profile.value_specific('PER_BUSINESS_GROUP_ID'),
               p_last_name                                       => 'TEST',
               p_first_name                                       => 'Rahman',
               p_middle_names                              => NULL,
               p_sex                                                     => 'M',
               p_national_identifier                       => '183-09-6723',
               p_date_of_birth                                 => TO_DATE('03-DEC-1988'),
               p_known_as                                       => 'PRAJ',
               -- Output data elements
               -- --------------------------------
               p_employee_number                         => lc_employee_number,
               p_person_id                                         => ln_person_id,
               p_assignment_id                                => ln_assignment_id,
               p_per_object_version_number       => ln_object_ver_number,
               p_asg_object_version_number       => ln_asg_ovn,
               p_per_effective_start_date               => ld_per_effective_start_date,
               p_per_effective_end_date                => ld_per_effective_end_date,
               p_full_name                                         => lc_full_name,
               p_per_comment_id                            => ln_per_comment_id,
               p_assignment_sequence                  => ln_assignment_sequence,
               p_assignment_number                     => lc_assignment_number,
               p_name_combination_warning    => lb_name_combination_warning,
               p_assign_payroll_warning            => lb_assign_payroll_warning,
               p_orig_hire_warning                        => lb_orig_hire_warning          );

    COMMIT;

EXCEPTION
      WHEN OTHERS THEN
                    ROLLBACK;
                    dbms_output.put_line(SQLERRM);
END;
/

SHOW ERR;

6 - To reset application password.

v_flag BOOLEAN;
BEGIN
v_flag := fnd_user_pkg.ChangePassword(‘Test’,’abcd′);
END;

7 - To get the description of GL Account.

Parameters:
1. Chart of Account id
2. Number of the Segment
3. Segment value for which you need the description

SELECT gcc.code_combination_id,apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,1,gcc.segment1) Segment1_desc,  apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,2,gcc.segment2) Segment2_desc,

  apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,3,gcc.segment3) Segment3_desc,

  apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,4,gcc.segment4) Segment4_desc,

  apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,5,gcc.segment5) Segment5_desc

from gl_code_combinations gcc

where gcc.code_combination_id = 12854 -- code combination id

;

8 - API to initiate Apps session from Toad or sql.

begin

FND_GLOBAL.apps_initialize(user_id =>1654,  resp_id =>20434,  resp_appl_id =>101);

end;

9- API to register Executable

BEGIN

        FND_PROGRAM.executable ('XXREQNOTIFTOBUYER' -- executable name

       , 'Payables' -- application

       , 'XX_REQNOTBUY_API' -- short_name

       , 'Executable for Approved requisition notif to Buyer' -- description

       , 'PL/SQL Stored Procedure' -- execution_method

       , 'XXREQNOTIFTOBUYER' -- execution_file_name

       , ''-- subroutine_name

       , '' -- Execution File Path

       , 'US' -- language_code

       ,'');

       COMMIT;

   END;

10- API to register Concurrent Program

BEGIN
 FND_PROGRAM.register('XXREQNOTIFTOBUYER' -- program
, 'Payables' -- application
, 'Y' -- enabled
, 'XX_REQNOTBUY_API' -- short_name
, 'Approved requisition notif to Buyer' -- description
, 'XX_REQNOTBUY_API' -- executable_short_name
, 'Payables' -- executable_application
, ''  -- execution_options
, ''  -- priority
, 'Y' -- save_output
, 'Y' -- print
,  '' -- cols
, ''  -- rows
, ''  -- style
, 'N' -- style_required
, ''  -- printer
, ''  -- request_type
, ''  -- request_type_application
, 'Y' -- use_in_srs
, 'N' -- allow_disabled_values
, 'N' -- run_alone
, 'TEXT' -- output_type
, 'N' -- enable_trace
, 'Y' -- restart
, 'Y' -- nls_compliant
, '' -- icon_name
, 'US'); -- language_code
 COMMIT;
            END;

11- API to attach Concurrent Porgram to Request Group

BEGIN

        FND_PROGRAM.add_to_group('XX_REQNOTBUY_API' -- program_short_name

                , 'Payables' -- application

        , 'All Reports' -- Report Group Name

        , 'SQLAP'); -- Report Group Application

        COMMIT;

 END;

12 - API to create inventory item

DECLARE
l_inventory_item_id number;
l_organization_id number;
l_return_status varchar2(4000);
l_msg_data varchar2(4000);
l_msg_count number;
l_msg_index number;
API_ERROR EXCEPTION;
BEGIN
FND_GLOBAL.APPS_INITIALIZE (USER_ID =>1873,
RESP_ID =>20634,
RESP_APPL_ID =>401);
EGO_ITEM_PUB.PROCESS_ITEM
(
p_api_version  => 1.0
,p_init_msg_list => 'T'
,p_commit => 'T' 
,p_transaction_type =>'CREATE'
,p_segment1 =>'xx_testing'
,p_description =>'xx_testing_detail'
,p_long_description =>'same as before'
,p_organization_id =>121
,p_template_id =>19
,p_inventory_item_status_code  =>'Active'
,p_approval_status =>'A'
,x_inventory_item_id =>l_inventory_item_id
,x_organization_id =>l_organization_id
,x_return_status =>l_return_status
,x_msg_count =>l_msg_count
,x_msg_data =>l_msg_data
);
if l_return_status = FND_API.G_RET_STS_SUCCESS
THEN
DBMS_OUTPUT.PUT_LINE('Item is created successfully, Inventory Item Id : '||l_inventory_item_id);
commit;
else
DBMS_OUTPUT.PUT_LINE('Item  creatION is failed');
RAISE API_ERROR;
ROLLBACK;
END IF;
-- HANDLE EXCEPTION
EXCEPTION
WHEN API_ERROR
THEN
FOR I IN 1 .. l_msg_count
loop
DBMS_OUTPUT.PUT_LINE(SUBSTR (FND_MSG_PUB.Get(p_encoded=>FND_API.G_FALSE),1,255));
DBMS_OUTPUT.PUT_LINE('MESSAGE IS :'||l_msg_data);
end loop;
when others
THEN
FOR I IN 1 .. l_msg_count
loop
DBMS_OUTPUT.PUT_LINE(SUBSTR (FND_MSG_PUB.Get(p_encoded=>FND_API.G_FALSE),1,255));
DBMS_OUTPUT.PUT_LINE('MESSAGE IS :'||l_msg_data);
end loop;
end;

13 - API to update inventory item's 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 :=242008;  --INVENTORY_ITEM_ID;
       l_item_table (1).organization_id :=127; -- I.ORGANIZATION_ID;
       l_item_table (1).template_id :=19;-- 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;

Oracle Workflow

Oracle Workflow

Topics Covered:

1- Download Workflow Setup File.
2- Workflow Error after Installation.
3- Workflow creation with different conditions.


1- Link to download the Workflow setup file:

http://www.oracle.com/technetwork/database/options/winclient-101059.html


2- For Worfklow error after its installation

+1300: Could not load.+
+1114: Could not load from database.+
+1101: Could not load item types from database. FILTER=%+
+333: Could not set item type properties for 'IBYOPCC'.+
+332: 'SET' validation failed for item type 'IBYOPCC'.+
+306: Invalid display name 'Tarjeta de Crédito/Tarjeta de Compra de Operaciones de iPayment '.+
+203: Value contains leading or trailing spaces.+

The Solution is:

1- update WF_ITEM_TYPES_TL set DISPLAY_NAME = RTRIM(DISPLAY_NAME);
2-  update WF_ITEM_TYPES_TL set DESCRIPTION = RTRIM(DESCRIPTION);
3-Always set the nls parameter in the reqistry editor for workflow
AMERICAN_AMERICA.AR8MSWIN1256

1- Basic Workflow Creation:

http://www.youtube.com/watch?v=bMvhTDhTqjI&list=PLUbvcYGBBSd6Ta9EsrJ20ffxEXySqEIER

2- Workflow with Approve or Reject:

http://www.youtube.com/watch?v=Wkre2JjeYfQ&list=PLUbvcYGBBSd6Ta9EsrJ20ffxEXySqEIER&index=2

3- Workflow with Approve & Reject Notification:

http://www.youtube.com/watch?v=UGeCf4rQi5I&index=2&list=PLUbvcYGBBSd6Ta9EsrJ20ffxEXySqEIER

4- Initiate Workflow from Toad:

http://www.youtube.com/watch?v=diz_bOaDG_c&list=PLUbvcYGBBSd6Ta9EsrJ20ffxEXySqEIER&index=4

Code to run it from toad.

1- First create a sequence.

Create sequence xxTESTSEQ start with 10 increment by 1;

2- Create a plsql block to run the workflow.

begin

wf_engine.createprocess(itemtype => 'TESTWF',
                                       itemkey  => xxTESTSEQ.nextval,
                                       process  => 'TESTPROC');

wf_engine.startprocess(itemtype  => 'TESTWF',
            itemkey   => xxTESTSEQ.CURRVAL);

commit;

end;
/

5- Creating attributes and running workflow in oracle apps:

http://www.youtube.com/watch?v=f2GZdyJqfkc&list=PLUbvcYGBBSd6Ta9EsrJ20ffxEXySqEIER&index=4


6- Workflow with attributes, running from sql:

http://www.youtube.com/watch?v=Yb4TKW9FHeU&list=PLUbvcYGBBSd6Ta9EsrJ20ffxEXySqEIER&index=4

Procedure to run it from toad or sql;

begin

wf_engine.createprocess(itemtype => 'TESTWF',
                                       itemkey  => xxTESTSEQ.nextval,
                                       process  => 'TESTPROC');

wf_engine.setitemattrtext(itemtype => 'TESTWF',
                                       itemkey  => xxTESTSEQ.currval,
                       aname    => 'ATTAPPROVER',
                       avalue   => 'MATLOOB');
                 
wf_engine.setitemattrtext(itemtype => 'TESTWF',
                                       itemkey  => xxTESTSEQ.currval,
       aname    => 'ATTINITIATOR',
       avalue   => 'SYSADMIN');


wf_engine.startprocess(itemtype  => 'TESTWF',
               itemkey   => xxTESTSEQ.CURRVAL);

commit;

end;

7- Running Workflow from plsql with attribute in message as dynamic value:

http://www.youtube.com/watch?v=eIawpsUbWK0&list=PLUbvcYGBBSd6Ta9EsrJ20ffxEXySqEIER&index=4

8- Worfklow running by plsql more dynamically.

http://www.youtube.com/watch?v=Xy8QVnUkOkk&index=8&list=PLUbvcYGBBSd6Ta9EsrJ20ffxEXySqEIER&spfreload=1

Step 1: Create Table to hold dummy data of leaves

create table xx_lvwf_appr_tab (name varchar2(30), mgr varchar2(30), leave_bal number);

insert into xx_lvwf_appr_tab values ('MATLOOB','SYSADMIN',20);
insert into xx_lvwf_appr_tab values ('USER1','MATLOOB',20);
insert into xx_lvwf_appr_tab values ('USER2','MATLOOB',20);
insert into xx_lvwf_appr_tab values ('USER3','SYSADMIN',20);

COMMIT;

Step 2: Create Apps session in Toad

begin
FND_GLOBAL.apps_initialize(user_id =>1654,
   resp_id =>20434,
   resp_appl_id =>101);
end;

Step 3: Run the block to run the process

declare
    x_initiator varchar2(30);
    x_mgr       varchar2(30);
begin
    select user_name into x_initiator from fnd_user
    where user_id =FND_PROFILE.VALUE('user_id');
 
    select mgr into x_mgr from xx_lvwf_appr_tab where name = x_initiator;
 
    wf_engine.createprocess(itemtype => 'TESTWF',
                                       itemkey  => xxTESTSEQ.nextval,
                                       process  => 'TESTPROC');

    wf_engine.setitemattrtext(itemtype => 'TESTWF',
                                       itemkey  => xxTESTSEQ.currval,
                       aname    => 'ATTAPPROVER',
                       avalue   => 'MATLOOB');
                 
    wf_engine.setitemattrtext(itemtype => 'TESTWF',
                                       itemkey  => xxTESTSEQ.currval,
       aname    => 'ATTINITIATOR',
       avalue   => 'SYSADMIN');


    wf_engine.startprocess(itemtype  => 'TESTWF',
               itemkey   => xxTESTSEQ.CURRVAL);
 
    commit;
 
end;


9- Workflow with function:

http://www.youtube.com/watch?v=5LYDvpgUHxk&index=9&list=PLUbvcYGBBSd6Ta9EsrJ20ffxEXySqEIER

10- Adding another function:

http://www.youtube.com/watch?v=9lYdrGxrJRE&index=10&list=PLUbvcYGBBSd6Ta9EsrJ20ffxEXySqEIER

Apps General Scripts

Apps General Scripts

List of Contents:

1- Query to get Business Group, Legal Entity Name, Operating Unit Details.

2- Organizations in Oracle Apps

3- P2P & O2C Cycle
4- Inventory Organization and corresponding Operating Unit, Ledger and Legal Entity:
5- Business Group & Legal Entities.
6- Business Group, Legal Entity, Operating Unit, Organizations
7- Business Group, Legal Entity, Operating Unit, Organizations & SubInventories
8- Application ID & Shortname & Name
9- Application Object Libraries (Tables)
10- Script to run before populating ap_invoices_v table data.
11- Script for Application short name, Responsibility_name & Key etc
12- FND Tables
13- In order to get Phone number of supplier/customer site the query will be (HZ Tables)

14- In order to get Phone number of supplier/customer  the query will be (HZ Tables)
15- Inventory items with categories

1- Query to get Business Group, Legal Entity Name, Operating Unit Details

SELECT distinct hrl.country, hroutl_bg.NAME bg, hroutl_bg.organization_id,

       lep.legal_entity_id, lep.NAME legal_entity,
       hroutl_ou.NAME ou_name, hroutl_ou.organization_id org_id,
       hrl.location_id,
       hrl.location_code,
       glev.FLEX_SEGMENT_VALUE
  FROM xle_entity_profiles lep,
       xle_registrations reg,
       hr_locations_all hrl,
       hz_parties hzp,
       fnd_territories_vl ter,
       hr_operating_units hro,
       hr_all_organization_units_tl hroutl_bg,
       hr_all_organization_units_tl hroutl_ou,
       hr_organization_units gloperatingunitseo,
       gl_legal_entities_bsvs glev
 WHERE lep.transacting_entity_flag = 'Y'
   AND lep.party_id = hzp.party_id
   AND lep.legal_entity_id = reg.source_id
   AND reg.source_table = 'XLE_ENTITY_PROFILES'
   AND hrl.location_id = reg.location_id
   AND reg.identifying_flag = 'Y'
   AND ter.territory_code = hrl.country
   AND lep.legal_entity_id = hro.default_legal_context_id
   AND gloperatingunitseo.organization_id = hro.organization_id
   AND hroutl_bg.organization_id = hro.business_group_id
   AND hroutl_ou.organization_id = hro.organization_id
   AND glev.legal_entity_id = lep.legal_entity_id;

2- Organizations in Oracle Apps

1- org_organization_definitions (All orgs & corresponding OU's & SOB's & LE)
2- hr_organization_units ( All Orgs including OU's & Names of orgs with Business Group)
3- mtl_parameters  (All Organizations not OU's)
4- hr_all_organization_units_tl      (All details of OP & Orgs)

3- P2P & O2C Cycle

http://www.slideshare.net/venugopalram/p2-p-and-o2c


4- Inventory Organization and corresponding Operating Unit, Ledger and Legal Entity:

Select a.organization_id, a.organization_code, a.organization_name,
a.operating_unit, b.name OU, a.set_of_books_id,d.name LEDGER,
a.legal_entity,c.name LE_NAME
From  apps. ORG_ORGANIZATION_DEFINITIONS a,
apps. HR_OPERATING_UNITS b,
apps. xle_entity_profiles c,
apps. gl_ledgers d
Where a.operating_unit=b.organization_id
AND c.legal_entity_id=a.legal_entity
AND d.ledger_id=a.set_of_books_id;

5- Business Group & Legal Entities

SELECT pg.business_group_name, pg.working_hours_frequency,
       pg.business_group_short_name, pg.default_currency_code,
       pg.enabled_flag, pg.competence_structure, pg.business_group_id,
       pg.location_id, le.organization_id, le.NAME
  FROM hrfv_business_groups pg, hr_legal_entities le
 WHERE pg.business_group_id = le.business_group_id
   AND pg.business_group_id =81;

6- Business Group, Legal Entity, Operating Unit, Organizations

SELECT PG.BUSINESS_GROUP_ID, pg.business_group_name, pg.working_hours_frequency,
       pg.business_group_short_name, pg.default_currency_code,
       pg.enabled_flag, pg.competence_structure, pg.business_group_id,
       pg.location_id, le.organization_id, le.NAME,
       ORGS.ORGANIZATION_iD ORGS, orgs.organization_code, ORG_ORG_NAMES.NAME ORGANIZATION_NAME,
        ORGS.OPERATING_UNIT OU, ORG_OU_NAME.NAME OPERATING_UNIT_NAME
  FROM hrfv_business_groups pg, hr_legal_entities le, hr_organization_units  ORG_ORG_NAMES,
        hr_organization_units ORG_OU_NAME ,
       (select business_group_id , organization_id , organization_code,  operating_unit from org_organization_definitions
       where business_group_id=81) Orgs
 WHERE pg.business_group_id = le.business_group_id
   AND pg.business_group_id =81
   AND PG.BUSINESS_GROUP_ID = ORGS.BUSINESS_GROUP_ID
   AND PG.BUSINESS_GROUP_ID = ORG_ORG_NAMES.BUSINESS_GROUP_ID
   AND ORGS.ORGANIZATION_ID = ORG_ORG_NAMES.ORGANIZATION_ID
   AND PG.BUSINESS_GROUP_ID = ORG_OU_NAME.BUSINESS_GROUP_ID
   AND ORGS.OPERATING_UNIT = ORG_OU_NAME.ORGANIZATION_ID;

7- Business Group, Legal Entity, Operating Unit, Organizations & Sub Inventories

SELECT PG.BUSINESS_GROUP_ID, pg.business_group_name, pg.working_hours_frequency,
       pg.business_group_short_name, pg.default_currency_code,
       pg.enabled_flag, pg.competence_structure, pg.business_group_id,
       pg.location_id, le.organization_id, le.NAME,
       ORGS.ORGANIZATION_iD ORGS, orgs.organization_code, ORG_ORG_NAMES.NAME ORGANIZATION_NAME,
        ORGS.OPERATING_UNIT OU, ORG_OU_NAME.NAME OPERATING_UNIT_NAME,
        msi.SECONDARY_INVENTORY_NAME, msi.DESCRIPTION
  FROM hrfv_business_groups pg, hr_legal_entities le, hr_organization_units  ORG_ORG_NAMES,
        hr_organization_units ORG_OU_NAME , MTL_SECONDARY_INVENTORIES msi,
       (select business_group_id , organization_id , organization_code,  operating_unit from org_organization_definitions
       where business_group_id=81) Orgs
 WHERE pg.business_group_id = le.business_group_id
   AND pg.business_group_id =81
   AND PG.BUSINESS_GROUP_ID = ORGS.BUSINESS_GROUP_ID
   AND PG.BUSINESS_GROUP_ID = ORG_ORG_NAMES.BUSINESS_GROUP_ID
   AND ORGS.ORGANIZATION_ID = ORG_ORG_NAMES.ORGANIZATION_ID
   AND PG.BUSINESS_GROUP_ID = ORG_OU_NAME.BUSINESS_GROUP_ID
   AND ORGS.OPERATING_UNIT = ORG_OU_NAME.ORGANIZATION_ID
   AND ORGS.ORGANIZATION_ID = MSI.ORGANIZATION_ID;

8- Application ID & Shortname & Name

SELECT fa.application_id           "Application ID",
       fat.application_name        "Application Name",
       fa.application_short_name   "Application Short Name",
       fa.basepath                 "Basepath"
  FROM fnd_application     fa,
       fnd_application_tl  fat
 WHERE fa.application_id = fat.application_id
   AND fat.language      = 'US';

9- AOL (Application Object Libraries) Tables

FND_APPLICATION Applications registered with Oracle Application Object Library
FND_CONCURRENT_PROGRAMS Concurrent programs
FND_CONCURRENT_REQUESTS Concurrent requests information
FND_CURRENCIES Currencies enabled for use at your site
FND_DATA_GROUPS Data groups registered with Oracle Application Object Library
FND_FLEX_VALUES Valid values for flexfield segments
FND_FLEX_VALUE_HIERARCHIES Child value ranges for key flexfield segment values
FND_FLEX_VALUE_SETS Value sets used by both key and descriptive flexfields
FND_FORM Application forms registered with Oracle Application Object Library
FND_FORM_FUNCTIONS Functionality groupings
FND_ID_FLEXS Registration information about key flexfields
FND_ID_FLEX_SEGMENTS Key flexfield segments setup information and correspondences between table columns and key flexfield segments
FND_ID_FLEX_STRUCTURES Key flexfield structure information
FND_LOOKUP_TYPES Oracle Application Object Library QuickCodes
FND_LOOKUP_VALUES QuickCode values
FND_MENUS New menu tabl for Release 10SC
FND_PROFILE_OPTIONS User profile options
FND_PROFILE_OPTION_VALUES Values of user profile options defined at different profile levels
FND_REQUEST_SETS Reports sets
FND_REQUEST_SET_PROGRAMS Reports within report sets
FND_REQUEST_SET_STAGES Stores request set stages
FND_RESPONSIBILITY Responsibilities
FND_RESP_FUNCTIONS Function Security
FND_USER Application users

10 - Script to run before query view ap_invoices_v

begin 
MO_GLOBAL.SET_ORG_CONTEXT('101',NULL,'SQLAP'); 

end; 

101 is operating unit which data will be populated in it.

11 - Script for Application short name, Responsibility_name & Key etc

SELECT A.APPLICATION_ID, A.APPLICATION_SHORT_NAME , A.PRODUCT_CODE, AA.APPLICATION_NAME,
       R.RESPONSIBILITY_ID, R.RESPONSIBILITY_KEY , RR.RESPONSIBILITY_NAME
FROM
FND_APPLICATION A , FND_APPLICATION_TL AA ,
FND_RESPONSIBILITY R,  FND_RESPONSIBILITY_TL RR
WHERE
A.APPLICATION_ID = AA.APPLICATION_ID AND
A.APPLICATION_ID = R.APPLICATION_ID AND
R.RESPONSIBILITY_ID = RR.RESPONSIBILITY_ID AND
R.APPLICATION_ID = RR.APPLICATION_ID
    AND AA.LANGUAGE='US';

12 - FND Tables 

FND_APPLICATION: 
 Stores applications registered with Oracle Application Object Library.
FND_APPLICATION_TL: 
Stores translated information about all the applications registered with Oracle Application Object Library.
FND_APP_SERVERS: 
This table will track the servers used by the E-Business Suite system.
FND_ATTACHED_DOCUMENTS: 
Stores information relating a document to an application entity.
FND_CONCURRENT_PROCESSES: 
Stores information about concurrent managers.
FND_CONCURRENT_PROCESSORS: 
Stores information about immediate (subroutine) concurrent program libraries.
FND_CONCURRENT_PROGRAMS: 
Stores information about concurrent programs. Each row includes a name and description of the concurrent program.
FND_CONCURRENT_PROGRAMS_TL: 
Stores translated information about concurrent programs in each of the installed languages.
FND_CONCURRENT_QUEUES: 
Stores information about concurrent managers.

FND_CONCURRENT_QUEUE_SIZE: 
Stores information about the number of requests a concurrent manager can process at once, according to its work shift.
FND_CONCURRENT_REQUESTS: 
Stores information about individual concurrent requests.
FND_CONCURRENT_REQUEST_CLASS: 
Stores information about concurrent request types.
FND_CONC_REQ_OUTPUTS: 
This table stores output files created by Concurrent Request.
FND_CURRENCIES: 
Stores information about currencies.
FND_DATABASES: 
It tracks the databases employed by the eBusiness suite. This table stores information about the database that is not instance specific.
FND_DATABASE_INSTANCES: 
Stores instance specific information. Every database has one or more instance.
FND_DESCRIPTIVE_FLEXS: 
Stores setup information about descriptive flexfields.
FND_DESCRIPTIVE_FLEXS_TL: 
Stores translated setup information about descriptive flexfields.
FND_DOCUMENTS: 
Stores language-independent information about a document.
FND_EXECUTABLES: 
Stores information about concurrent program executables.
FND_FLEX_VALUES: 
Stores valid values for key and descriptive flexfield segments.
FND_FLEX_VALUE_SETS: 
Stores information about the value sets used by both key and descriptive flexfields.
FND_LANGUAGES: 
Stores information regarding languages and dialects.
FND_MENUS: 
It lists the menus that appear in the Navigate Window, as determined by the System Administrator when defining responsibilities for function security.
FND_MENUS_TL: 
Stores translated information about the menus in FND_MENUS.
FND_MENU_ENTRIES: 
Stores information about individual entries in the menus in FND_MENUS.
FND_PROFILE_OPTIONS: 
Stores information about user profile options.
FND_REQUEST_GROUPS: 
Stores information about report security groups.
FND_REQUEST_SETS: 
Stores information about report sets.
FND_RESPONSIBILITY: 
Stores information about responsibilities. Each row includes the name and description of the responsibility, the application it belongs to, and values that identify the main menu, and the first form that it uses.
FND_RESPONSIBILITY_TL: 
Stores translated information about responsibilities.
FND_RESP_FUNCTIONS: 
Stores security exclusion rules for function security menus. Security exclusion rules are lists of functions and menus inaccessible to a particular responsibility.
FND_SECURITY_GROUPS: 
Stores information about security groups used to partition data in a Service Bureau architecture.
FND_SEQUENCES: 
Stores information about the registered sequences in your applications.
FND_TABLES: 
Stores information about the registered tables in your applications.
FND_TERRITORIES: 
Stores information for countries, alternatively known as territories.
FND_USER: 
Stores information about application users.
FND_VIEWS: 
Stores information about the registered views in your applications.

13- In order to get Phone number of supplier/Customer site the query will be (HZ Tables)

Select hz.party_id, hz.party_name, hps.party_site_name, hcp.phone_number
from      hz_contact_points hcp,
    hz_party_sites hps,
hz_parties hz
where
hz.party_id=77042 and
hz.party_id = hps.party_id(+) and
hps.party_site_id = hcp.owner_table_id(+) and
hcp.owner_table_name='HZ_PARTY_SITES' and
 hz.application_id=200;

* If you replace application_id to 222 then you can get the detail of Customer

14- In order to get Phone number of supplier/Customer  the query will be (HZ Tables)

Select hz.party_id, hz.party_name,  hcp.phone_number

from   hz_contact_points hcp,

hz_parties hz,

hz_relationships hr

where 

hz.party_id=77042 and

    hz.party_id = hr.object_id(+) and

    hr.party_id = hcp.owner_table_id(+) and

hcp.owner_table_name='HZ_PARTIES' 

 hz.application_id=200;

* If you replace application_id to 222 then you can get the detail of Customer

14- Inventory items with categories

SELECT I.INVENTORY_ITEM_ID, I.SEGMENT1 ITEM_CODE, I.DESCRIPTION ITEM_DETAIL,

i.PRIMARY_UNIT_OF_MEASURE
, C.CATEGORY_ID, CD.DESCRIPTION
FROM MTL_SYSTEM_ITEMS_B I , MTL_ITEM_CATEGORIES C, MTL_CATEGORIES CD
WHERE I.ORGANIZATION_ID=127 AND I.ORGANIZATION_ID = C.ORGANIZATION_ID AND

I.INVENTORY_ITEM_ID = C.INVENTORY_ITEM_ID  AND C.CATEGORY_ID = CD.CATEGORY_ID;

Transaction Sources

Transaction Source Overview

Transaction Source setup plays a key role in determining the behavior of the Transactions form as well as how AutoInvoice validates data to be imported. When you create a transaction, and associate it to a Transaction Source, several attributes of the Source are defaulted into the transaction The following functionality is controlled by your transaction source setup:

• Transaction Numbering – Automatic or Manual
• GL Date Derivation
• Grouping Rules
• Customer Information – Values Passed (ID, Value, Segment)
  • Bill To and Ship To
  • Address
  • Contact
  • Payment Method
• Accounting Information – Values Passed (ID, Value, Segment)
  • Invoicing Rule
  • Payment Terms
• Sales Credit Validation
• How to process Invalid Lines
• How to Process GL Dates in a Closed Period

Active transaction sources appear as list of values choices in the Transactions, Transactions Summary, and Credit Transactions windows.  For Bills Receivable, the transaction batch sources appear in the Bills Receivable and Bills Receivable Transaction Batches windows.

There are two types of transaction sources:

• Manual: Used for transactions created manually in the Transactions Workbench.
• Imported: Used for importing transactions using AutoInvoice.

The following lists the 6 tabbed regions in the Transaction Source form. Only the first tab is enabled when creating Manual  transaction sources. For Imported transaction sources, all 6 tabbed regions are enabled.

1. Batch Source
2. AutoInvoice Options
3. Customer Information
4. Accounting Information
5. Other Information
6. Sales Credit Validation

Oracle Receivables provides the following seeded Transaction Sources:

Imported:

• Global Intercompany
• INDIA INVOICES
• Intercompany
• Order Entry Delivery Invoicing
• PA Internal Invoices
• PROJECTS INVOICES
• Property Manager Batch Source
• SERVICE
• Trade Management
• Work Order Billing

Manual:

• Chargeback
• DM Reversal
• Loans
• MANUAL-OTHER

Batch sources define default information such as transaction type and automatic numbering. There are two types available: Manual and Imported. AutoInvoice requires a batch of type Imported.

Responsibility: Receivables Manager
Navigation: Setup > Transactions > Sources

• Batch Source Name –Tip: If you have installed multiple organization support (multi-org), define an imported batch source with the same name in each organization (these sources can have the same or different settings). This enables you to import  order lines that belong to different organizations in Oracle Order Management “into corresponding organizations” in Receivables.

Note:  The Batch Source parameter selected must match the value in the interface table.  For this reason, local language names are not displayed in the LOV when running AutoInvoice

• Automatic Batch Numbering
  • Checked – indicates batches are automatically numbered, using the sequence provided in Last Number
  • Unchecked – indicates batches will require users to enter a batch number manually

• Automatic Transaction Numbering
  • Checked – indicates the transactions within this batch will be numbered automatically from the sequence value provided in Last Number
  • Unchecked – indicates manual transaction numbering, which will require users to enter a transaction number manually during data entry

• Copy Document Number to Transaction Number – indicates whether you want the document number generated to overlay the transaction number. This feature is necessary to implement gapless transaction numbers
• • Allow Duplicate Transaction Numbers – indicates whether duplicate numbers should be prevented or not during data entry
  • Copy Transaction Information Flexfield to Credit Memo – indicates whether the descriptive flexfield information from an Invoice should be copied into the Credit memo that credits the transaction
  • Generate Line Level Balances – indicates whether you want transactions in this batch to maintain line level balances useful for Line Level Cash Applications
  • Receipt Handling for Credits – for cases where you are interfacing a credit memo against an Invoice that has already been fully paid, you have the option of unapplying the receipts so that AutoInvoice can continue crediting the transaction. Available options are:
    1) Refund – to issue a credit card refund for the receipt or
    2) On-Account – put the receipt in On-Account
  • Reference Field Default Value – indicates which line transaction flexfield segment, i.e. RA_INTERFACE_LINES_ALL.INTERFACE_* field, you want to appear in the Reference field in the Transaction form
  • Standard Transaction Type – is the default transaction type for transactions in this batch
  • Credit Memo Batch Source – is the default batch source Receivables will use when creating Credit Memos against transactions within this batch source

The AutoInvoice Options tab highlights the Grouping rule created earlier.  The setup indicates this batch will group data as specified in this grouping rule. The Payment Details Defaulting Hierarchy is set to Parent of Bill-To Customer.

• Invalid Line – indicates the action to take when an interface line has an error, valid values are:
  Reject Invoice and Create Invoice
• GL Date in a Closed Period – indcates the action to take when the GL date provided in interface falls in a closed period, valid values are: Adjust and Reject
• Grouping Rule – indicates the default Grouping rule to use for interface lines using this batch source.
• Payment Details Defaulting Hierarchy – this field has 2 possible values:

• Parent of Bill-To Customer – this is the default value and uses the defaulting hierarchy:
  1. Primary receipt method of the parent primary bill-to site
2. Primary receipt method of the parent customer

3. Primary receipt method of the bill-to site

4. Primary receipt method of the bill-to customer

• Bill-To Customer – this uses the following defaulting hierarchy:
  1. Primary receipt method of the bill-to site
  2. Primary receipt method of the bill-to customer
  3. Primary receipt method of the parent primary bill-to site
  4. Primary receipt method of the parent customer

• Create Clearing – when checked, any differences between the specified revenue amount and the price multiplied by the quantity for imported invoice lines are stored in a clearing account. You must define a clearing
  account if you enable this.
• Allow Sales Credit – when checked, indicates that sales credits can be entered for transactions using this source

The following screenshots show the rest of the sub-tabs of this form, including Customer, Accounting, Salescredit and other information. You need to define whether the data you will interface provides the Value or the ID so that AutoInvoice can validate the data.

Customer Information tab:

Accounting Information tab:

Other Information tab:

Sales Credit Validation tab:

Batch Source for use in the Transactions Workbench

Responsibility: Receivables Manager
Navigation: Setup > Transactions > Sources

Reference: metalink.oracle.com