Oracle E-Business Suite Electronic Technical Reference Manuals (eTRM)

eTRM is a pl/sql utility that reads design information in an Oracle database and displays its output in html format. It shows database design and dependency information for the Oracle eBusiness Suite. Enter the name of a database object (example: per_people_f) or a search condition (example: contract compliance) then press 'Search' to search for information. Alternatively you can browse the FND Model or Oracle Data Dictionary from the FND or DBA tabs

CREATE AN INVENTORY ITEM IN ORACLE APPS FROM BACKEND(ITEM IMPORT)

CREATE AN INVENTORY ITEM IN ORACLE APPS FROM BACKEND(ITEM IMPORT) INSERT SCRIPT

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; --finished_goods


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,
    'SS123',
    'Sample SS Item Testing',
    107
  );

STEP4: Run the wrapper script given in the below link to submit the "ITEM IMPORT" concurrent program from backend

DECLARE
v_sub_status BOOLEAN;

FUNCTION submit_item_import
RETURN BOOLEAN
IS
 v_organization_id   NUMBER := 0;
 v_request_id        NUMBER := 0;
 v_phase             VARCHAR2(240);
 v_status            VARCHAR2(240);
 v_request_phase     VARCHAR2(240);
 v_request_status    VARCHAR2(240);
 v_finished          BOOLEAN;
 v_message           VARCHAR2(240);
 v_sub_status        BOOLEAN := FALSE;

BEGIN
  FND_GLOBAL.APPS_INITIALIZE(1318,50583,401);
  MO_GLOBAL.SET_POLICY_CONTEXT('S','204');
  FND_GLOBAL.SET_NLS_CONTEXT('AMERICAN');  --This is for Language specific view
  MO_GLOBAL.INIT('INV');

  v_request_id := Fnd_Request.submit_request
                       (
                         application => 'INV',
                         program     => 'INCOIN',
                         description => NULL,
                         start_time  => SYSDATE,
                         sub_request => FALSE,
                         argument1 => 204,  -- Organization id
                         argument2 => 1,    -- All organizations
                         argument3 => 1,    -- Validate Items
                         argument4 => 1,    -- Process Items
                         argument5 => 1,    -- Delete Processed Rows
                         argument6 => NULL, -- Process Set (Null for All)
                         argument7 => 1,    -- Create or Update Items
                         argument8 => 1     -- Gather Statistics
                       );
  COMMIT; 

  IF ( v_request_id = 0 ) THEN
    dbms_output.put_line( 'Item Import Program Not Submitted');
        v_sub_status := FALSE;
  ELSE
    v_finished := fnd_concurrent.wait_for_request
                            (
                             request_id => v_request_id,
                             interval   => 0,
                             max_wait   => 0,
                             phase      => v_phase,
                             status     => v_status,
                             dev_phase  => v_request_phase,
                             dev_status => v_request_status,
                             message    => v_message
                             );

    DBMS_OUTPUT.PUT_LINE('Request Phase  : '|| v_request_phase );
    DBMS_OUTPUT.PUT_LINE('Request Status : '|| v_request_status );
    DBMS_OUTPUT.PUT_LINE('Request id     : '|| v_request_id );

  --Testing end statusv_request_id
    IF ( UPPER(v_request_status) = 'NORMAL') THEN    
          v_sub_status := TRUE;
    END IF;
  END IF;
  RETURN (v_sub_status);
EXCEPTION
  WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE('Error in Submitting Item Import Program and error is '||SUBSTR(SQLERRM,1,200));
   RETURN (FALSE); 
END submit_item_import;

BEGIN
  v_sub_status  := submit_item_import();
  IF v_sub_status THEN
    DBMS_OUTPUT.PUT_LINE( 'Item Import Status: Sucessful');      
  ELSE
    DBMS_OUTPUT.PUT_LINE( 'Item Import Status: Failed');
  END IF;

END;   
/
commit;


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

SELECT *
FROM   mtl_system_items_b
WHERE  segment1 = ' SS123';

Customers in TCA Architecture in Release 12

Scope and Application

This note is intended for anyone who must  upgrade queries or customizations from 11i which refer to any of the following views or anyone who requires an understanding of the TCA architecture  to create new queries or customizations:

·                     RA_CUSTOMERS

·                     RA_ADDRESSES

·                     RA_SITE_USES

·                     RA_CONTACTS

·                     RA_CONTACT_ROLES

Projects Uptake of the TCA Architecture in Release 12

1. The TCA Architecture

The following sections describe the individual views that are being replaced by the TCA (HZ) tables along with a mapping of view columns to HZ table columns and the query conditions that should be used to retrieve them

2.1 RA_CUSTOMERS

·                     In the 11i architecture RA_CUSTOMERS is a synonym in the APPS schema which points to the view RA_HCUSTOMERS. 

·                     The source file for RA_HCUSTOMERS in 11i is archz.odf.

·                     The table below lists the corresponding HZ table and column for various columns in the current RA_CUSTOMERS view:

Column in RA_CUSTOMERS	Corresponding Table	Column
customer_name	hz_parties	substrb(party_name,1,50)
customer_id	hz_cust_accounts	cust_account_id
customer_number	hz_cust_accounts	account_number
status	hz_cust_accounts	status

·                     Source Tables:  HZ_PARTIES, HZ_CUST_ACCOUNTS

·                     Join Conditions:

HZ_CUST_ACCOUNTS..Party_Id = HZ_PARTIES.Party_Id

2.2 RA_ADDRESSES

·                     In the 11i architecture RA_ADDRESSES is a multi-org striped view based on RA_ADDRESSES_ALL.  RA_ADDRESSES_ALL is a synonym for the view RA_ADDRESSES_MORG.

·                     The source file for view RA_ADDRESSES_MORG in 11i is archz.odf.

·                     The table below lists the corresponding HZ table and column for various columns in the current RA_ADDRESSES view:

Column in RA_ADDRESSES	Corresponding Table	Column
address_id	hz_cust_acct_sites_all	cust_acct_site_id
status	hz_cust_acct_sites_all	status
address1	hz_locations	address1
address2	hz_locations	address2
address3	hz_locations	address3
address4	hz_locations	address4
city	hz_locations	city
state	hz_locations	state
postal_code	hz_locations	postal_code
county	hz_locations	county
country	hz_locations	country
language	hz_locations	language

·                     Source Tables:  HZ_PARTY_SITES, HZ_LOCATIONS, HZ_CUST_ACCT_SITES_ALL

·                     Join Conditions:

HZ_CUST_ACCT_SITES_ALL.party_site_id = HZ_PARTY_SITES.party_site_id 
HZ_LOCATIONS.location_id = HZ_PARTY_SITES.location_id

2.3 RA_SITE_USES

·                     In the 11i architecture RA_SITE_USES is a multi-org striped view based on RA_SITE_USES_ALL. RA_SITE_USES_ALL is a synonym for the view RA_SITE_USES_MORG.

·                     The source file for view RA_SITE_USES_MORG in 11i is archz.odf

·                      The table below lists the corresponding HZ table and column for various columns in the current RA_SITE_USES view:

Column in RA_SITE_USES	Corresponding Table	Column
site_use_id	hz_cust_site_uses	site_use_id
site_use_code	hz_cust_site_uses	site_use_code
status	hz_cust_site_uses	status
address_id	hz_cust_site_uses	cust_acct_site_id

·                     Source Tables:  HZ_CUST_SITE_USES

·                     Join Conditions:  none

2.4 RA_CONTACTS

·                     In the 11i architecture RA_CONTACTS is a synonym for the view RA_HCONTACTS

·                     The source file for view RA_HCONTACTS in 11i is archz.odf

·                     The table below lists the corresponding HZ table and column for various columns in the current RA_CONTACTS view:

Column in RA_CONTACTS	Corresponding Table	Column
contact_id	hz_cust_account_roles	cust_account_role_id
status	hz_cust_account_roles	status
customer_id	hz_cust_account_roles	cust_account_id
address_id	hz_cust_account_roles	cust_acct_site_id
first_name	hz_parties	substrb(person_first_name,1,40)
last_name	hz_parties	substrb(person_last_name,1,50)

·                     Source Tables: HZ_CUST_ACCOUNT_ROLES, HZ_PARTY_RELATIONSHIPS, HZ_PARTIES

·                     Join Conditions:

HZ_CUST_ACCOUNT_ROLES.party_id = HZ_PARTY_RELATIONSHIPS.party_id 
HZ_CUST_ACCOUNT_ROLES.role_type = 'CONTACT'
HZ_PARTIES.party_id = HZ_PARTY_RELATIONSHIPS.subject_id

2.5 RA_CONTACT_ROLES

·                     In the 11i architecture RA_CONTACT_ROLES is a synonym for the view RA_HCONTACT_ROLES

·                     The source file for view RA_HCONTACT_ROLES in 11i is archz.odf

·                     The table below lists the corresponding HZ table and column for various columns in the current RA_CONTACT_ROLES view:

Column in RA_CONTACT_ROLES	Corresponding Table	Column
contact_id	hz_role_responsibility	cust_account_role_id
usage_code	hz_role_responsibility	responsibility_type
primary_flag	hz_role_responsibility	primary_flag

·                     Source Tables: HZ_ROLE_RESPONSIBILITY

·                     Join Conditions:  none.

11i Table	R12 Change
ra_addresses_all	SELECT acct_site.cust_account_id customer_id,     acct_site.cust_acct_site_id address_id FROM hz_party_sites party_site,                  hz_loc_assignments loc_assign,                  hz_locations loc,                  hz_cust_acct_sites_all acct_site            WHERE acct_site.party_site_id = party_site.party_site_id              AND loc.location_id = party_site.location_id              AND loc.location_id = loc_assign.location_id              AND NVL (acct_site.org_id, -99) = NVL (loc_assign.org_id, -99)
ra_site_uses_all	SELECT site_use_id, LOCATION, attribute1  FROM hz_cust_site_uses_all
ra_customers	SELECT cust_acct.cust_account_id customer_id,                  SUBSTRB (party.party_name, 1, 50) customer_name,                  cust_acct.account_number customer_number             FROM hz_parties party, hz_cust_accounts cust_acct            WHERE cust_acct.party_id = party.party_id

RA_ADDRESSES_ALL is obsolete table in Release 11.5.10 and Release 12. You can get the customer address information from the following tables in Release 12.

HZ_CUST_ACCT_SITES_ALL
HZ_PARTY_SITES
HZ_LOCATIONS

Here is the Linking information:

- HZ_CUST_ACCT_SITES_ALL & HZ_PARTY_SITES - Link column PARTY_SITE_ID
- HZ_PARTY_SITES & HZ_LOCATIONS - Link column is LOCATION_ID.