Infolinks

Showing posts with label Apps-11i. Show all posts
Showing posts with label Apps-11i. Show all posts

Friday, 13 July 2012

Retrieves all PO transaction quantities

Retrieves all PO transaction quantities

Retrieves all PO transaction quantities:-
----------------------------------------------

Following Query would get all the PO Numbers, Item ID, Quantity Order, Received, Canceled etc. The same information can be found in the PO order form from the Front end Application.

SELECT
POL.ITEM_ID "Inventory Item ID",
POLL.SHIP_TO_LOCATION_ID "Ship to Location ID",
poll.quantity "Quantity",
poll.Quantity_received "Quantity Received",
poll.QUANTITY_cancelled "Quantity Canceled",
POH.SEGMENT1 "PO Order Number"
FROM po_headers_all poh
, po_lines_all pol
, po_line_locations_all poll
WHERE poh.po_header_id=pol.po_header_id
AND pol.po_line_id=poll.po_line_id
AND poll.po_header_id=pol.po_header_id
AND poh.authorization_status='APPROVED'
AND poh.type_lookup_code='STANDARD'
AND NVL(poh.cancel_flag,'N') ='N'
AND POL.ITEM_ID IS NOT NULL
AND NVL (poll.quantity, 0) > (NVL (Quantity_received,0) + NVL (Quantity_cancelled,0));

INV On-hand Quatity of one Particular Item in whole Organization

INV On-hand Quatity of one Particular Item in whole Organization

INV On-hand Quatity of one Particular Item in whole Organization:-
-----------------------------------------------------------------------------------

The following Query will On-hand Quatity of particular item in the whole Organization

select sum(primary_transaction_quantity) from mtl_onhand_quantities_detail where inventory_item_id = 'Your Inventory Organization ID';

Example:-

Note:- 123456 is the item ID of the particular item. This can be found in the base table of the item (MTL_SYSTEM_ITEMS_B). SEGMENT1 column of this table would have Item Name and Inventory_item_id is the primary column of the table. The follow query is using this value.

select sum(primary_transaction_quantity) from mtl_onhand_quantities_detail where inventory_item_id = 123456;

If you want to know the on-hand Quantity of particular item at all the Inventory Organization then use the following the Query.

select sum(primary_transaction_quantity), organization_id from mtl_onhand_quantities_detail where inventory_item_id = 'Your Inventory Organization ID'
group by organization_id;

INV On-hand Quatity of one Particular Item in whole Organization

INV On-hand Quatity of one Particular Item in whole Organization

INV On-hand Quatity of one Particular Item in whole Organization:-
-----------------------------------------------------------------------------------

The following Query will On-hand Quatity of particular item in the whole Organization

select sum(primary_transaction_quantity) from mtl_onhand_quantities_detail where inventory_item_id = 'Your Inventory Organization ID';

Example:-

Note:- 123456 is the item ID of the particular item. This can be found in the base table of the item (MTL_SYSTEM_ITEMS_B). SEGMENT1 column of this table would have Item Name and Inventory_item_id is the primary column of the table. The follow query is using this value.

select sum(primary_transaction_quantity) from mtl_onhand_quantities_detail where inventory_item_id = 123456;

If you want to know the on-hand Quantity of particular item at all the Inventory Organization then use the following the Query.

select sum(primary_transaction_quantity), organization_id from mtl_onhand_quantities_detail where inventory_item_id = 'Your Inventory Organization ID'
group by organization_id;

Operating Unit name based on the Operating Unit ID

Operating Unit name based on the Operating Unit ID

Operating Unit name based on the Operating Unit ID:-

In the following Query you can get Operating Unit Name based on the Operating Unit ID.

select 
Business_group_id,
Organization_id, 
name,
date_from,
date_to,
legal_entity_id,
set_of_books_id 
from 
hr_operating_units 
where organization_id = 'your Operating Unit ID';

Query to get the Number of Seconds or Minutes or Hours between 2 given days(SQL)

Query to get the Number of Seconds or Minutes or Hours between 2 given days(SQL)

Query to get the Number of Seconds or Minutes or Hours between 2 given days:-
----------------------------------------------------------------------------------------

Note:- I have prepared this query since I could not find any inbuilt functions or procedures in Oracle to get this. I had the requirement to treat 2 dates are same if differences is not more then 2 seconds.

For the Seconds:-
-------------------

SELECT ('Your first Date'-'Your another Date') * DECODE( UPPER('SS')
, 'SS', 24*60*60
, 'MI', 24*60
, 'HH', 24, NULL )
FROM DUAL;

Example:-

SELECT (SYSDATE+1-SYSDATE) * DECODE( UPPER('SS')
, 'SS', 24*60*60
, 'MI', 24*60
, 'HH', 24, NULL )
FROM DUAL;

For Minutes:-
---------------

SELECT ('Your first Date'-'Your another Date') * DECODE( UPPER('MI')
, 'SS', 24*60*60
, 'MI', 24*60
, 'HH', 24, NULL )
FROM DUAL;

Example:-

SELECT (SYSDATE+1-SYSDATE) * DECODE( UPPER('MI')
, 'SS', 24*60*60
, 'MI', 24*60
, 'HH', 24, NULL )
FROM DUAL;

For the Hours:-
-----------------

SELECT ('Your first Date'-'Your another Date') * DECODE( UPPER('HH')
, 'SS', 24*60*60
, 'MI', 24*60
, 'HH', 24, NULL )
FROM DUAL;

Example:-

SELECT (SYSDATE+1-SYSDATE) * DECODE( UPPER('HH')
, 'SS', 24*60*60
, 'MI', 24*60
, 'HH', 24, NULL )
FROM DUAL;

To compile all the objects in given schema

To compile all the objects in given schema

To compile all the objects in given schema :-
--------------------------------------------------

You may have the requirement to compile all the invalid objects. in particular schema like 'APPS' or 'SCOTT' etc. This can be done in may ways. There is option to compile the Invalid objects in APPS schema in Admin utility I have documented about the Adadmin utility in the Unix section. Check out the Unix for the Adadmin utility.

begin
SYS.UTL_RECOMP.RECOMP_PARALLEL('N', 'Schema_name');
end;
/

To compile all of the invalid objects owned by APPS
-------------------------------------------------------------

Note: must use UPPER case on the schema name

begin
dbms_utility.compile_schema('APPS');
end;
/

Find out which concurrent program attached to which all responsibility

Find out which concurrent program attached to which all responsibility

findout which concurrent program attached to which all responsibility:-
-------------------------------------------------------------------------------------

You know the concurrent program name, but you do not know to what all responsibilities it is attached to. In that case, you can use the following select statement to know the responsibilities names to which your concurrent program is attached.

select responsibility_name
from fnd_responsibility_tl rsp_tl, fnd_responsibility fr, --fnd_request_groups frg,
fnd_request_group_units frgu, fnd_concurrent_programs_tl fcpt
where rsp_tl.responsibility_id = fr.responsibility_id
--and frg.request_group_id = fr.request_group_id
and fr.request_group_id = frgu.request_group_id
and fcpt.concurrent_program_id = frgu.request_unit_id
and upper(fcpt.USER_CONCURRENT_PROGRAM_NAME) = upper('concurrent program name');

Example:-

select responsibility_name
from fnd_responsibility_tl rsp_tl, fnd_responsibility fr, --fnd_request_groups frg,
fnd_request_group_units frgu, fnd_concurrent_programs_tl fcpt
where rsp_tl.responsibility_id = fr.responsibility_id
--and frg.request_group_id = fr.request_group_id
and fr.request_group_id = frgu.request_group_id
and fcpt.concurrent_program_id = frgu.request_unit_id
and upper(fcpt.USER_CONCURRENT_PROGRAM_NAME) = upper('ALLOracleTech Test Program');

API: Customer API -Create a Location

API: Customer API -Create a Location

DECLARE
p_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
x_location_id NUMBER;
x_return_status VARCHAR2(2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
BEGIN
p_location_rec.country := ’US’;
p_location_rec.address1 := ’300 Oracle Parkway’;
p_location_rec.address2 := ’13th Floor’;
p_location_rec.city := ’Redwood Shores’;
p_location_rec.postal_code := ’94065’;
p_location_rec.state := ’CA’;
p_location_rec.created_by_module := ’TCA_EXAMPLE’;
hz_location_v2pub.create_location(
’T’,
p_location_rec,
x_location_id,
x_return_status,
x_msg_count,
x_msg_data);
dbms_output.put_line('location id '||x_location_id);
dbms_output.put_line(SubStr(’x_return_status = ’||x_return_status,1,255));
dbms_output.put_line(’x_msg_count = ’||TO_CHAR(x_msg_count));
dbms_output.put_line(SubStr(’x_msg_data = ’||x_msg_data,1,255));
IF x_msg_count >1 THEN
FOR I IN 1..x_msg_count
LOOP
dbms_output.put_line(I||’. ’||SubStr(FND_MSG_PUB.Get(p_encoded =>FND_API.G_FALSE ), 1, 255));
END LOOP;
END IF;
END

Record get created in hz_locations table

AP Suppliers in R12

AP Suppliers in R12


AP Suppliers in R12


Buzz It
In R12 Accounts Payables, that there is no more supplier form. The Suppliers have gone to self-service now. This is not the only change in the supplier. The suppliers objects have moved from AP product to TCA (Trading Community Architecture) DataModel. Due to this, even the underlying tables have changed. Supplier information is no more stored in PO_VENDORS Table now.
3 new tables have been introduced.
• AP_SUPPLIERS
• AP_SUPPLIER_SITES_ALL
• AP_SUPPLIER_CONTACTS
But don't panic as your customizations can still work as there are views created with names of PO_VENDORS, PO_VENDOR_SITES_ALL and PO_VENDOR_CONTACTS for backward compatibility.
Being a part of the TCA, these tables are closely linked to the hz tables. Here is the list of few imp HZ Tables that are affected when a new supplier is added.
TABLE NAME
DESCRIPTION
HZ_PARTIES
Master table along with AP_SUPPLIERS instead of PO_VENDORS
HZ_PARTY_SITES
Master table for supplier sites along with AP_SUPPLIER_SITES_ALL instead of PO_VENDORS_SITES_ALL
HZ_LOCATIONS
Contains the party sites information
HZ_PARTY_USG_ASSIGNMENTS
Stores party usages
HZ_ORGANIZATION_PROFILES
Captures additional Supplier information, e.g. credit scoring details of Supplier or the Number of Employees working in Supplier Organization
IBY_EXTERNAL_PAYEES_ALL
Captures Payment related details of the Supplier
POS_SUPPLIER_MAPPINGS
It holds the mapping between the AP_SUPPLIERS.VENDOR_ID and HZ_PARTIES.PARTY_ID
This is useful in cases whereby two vendors effectively belong the same HZ_Party Record.
See the diagram below for Supplier/Sites Mappings to TCA Objects.


So whenever a supplier is added in R12, an entry is made in all these tables. Functionally also, creating the supplier is different from 11i. Here are the steps to create a new supplier.
1. Hit the CREATE SUPPLIER button

2. Enter a unique supplier name (Organization Name) along with other optional other information like Alias, Tax Registration Number, D-U-N-S number.
3. If the Supplier Number Entry option in the Payables System Setup window is set to Automatic, Payables automatically enters a Supplier Number for you. If this option is set to Manual, you must enter a unique Supplier Number.
4. Click Apply. The system creates the supplier record and accesses the Suppliers: Quick Update page.
5. To create Supplier Sites, you will have to create the locations for that supplier. For that, click on the "Address Book" Button.

6. On the Address Book page, click the Create button.
7. Fill all the address details and address purpose. i.e. Purchasing, Payment or RFQ Only.
8. You can add as many locations as you wish.

9. When created, you can manage the addresses for other information.




<< Click to view the image in large size >>
10. The system only displays sites that are in your MOAC profile
11. The address status indicates whether the supplier has provided any updates for the address. Using iSupplier Portal's Supplier Profile Management tools, suppliers can enter address book information online, creating any number of new addresses, modifying the details for existing addresses, and indicating how each address is used.
12. Suppliers can also inactivate addresses that are obsolete. Buyer administrators need to approve any changes in order to update the master supplier details.
13. Address statuses include:
• New. A supplier has created a new address.
• Change Pending. A supplier has changed the address details. Click the Update icon to review the changes that have been made. The page displays the original address details and the changes, indicated by a blue dot. Buyer administrators can approve or make additional modifications to the changes before approving or rejecting the change. If the supplier has indicated that the address should be removed, there is a status change from Active to Inactive.
• Current. There are no pending updates for the address.
14. Since suppliers are stored in TCA, the address details for the supplier may be used by other Oracle products so be careful if removing supplier addresses. If the address is inactivated, the system no longer associates it to any contacts, and any bank account assignments to the address are inactivated. Methods to inactivate addresses include:
• You can click the Remove button on the Address Book page. This sets the address status to inactive and sets the Inactive Date for every site that is associated with the address in all operating units to today's date.
• You can update the address and set its status to Inactive. This changes the address status and does not inactivate any of the sites that are using the address.
• You can use the Manage Sites page to manually update the Inactive Date for each site.
 

R12 Oracle Application Footprint


R12 Oracle Application Footprint


R12 Oracle Application Footprint

Release 12 is defined as “The Global Business Release.” Global is
not just a geographic perspective, but also a comprehensive
perspective; release 12 functionality spans across both industries
and business functions.

R12 Foot Print:
============






Key Business Flows :
---------------------
Oracle business flows are a collection of application components

designed for end-to-end business processes. They identify the
critical business processes an organization utilizes to support a
complete business strategy for managing operations, customers,
suppliers, partners, and employees.


Oracle business flows map business processes across multiple
organizations and many applications to represent a streamlined,
efficiently integrated information flow throughout business
organizations and across geographies.




R12 Architecture:
----------------------
Business Architecture: The R12 EBS has 5 principles that drives its business architecture:
-Modern Foundation
-Complete
-End to end Intergration
-Global
-Rapid Implementation.

1. Oracle has embedded all of its new R12 development into open,
scalable standards. These standards include using Java/J2EE,
HTML, JavaScript (JSP), Internet-accessibility, and centralized
management.

2. R12 E-Business Suite is accessible via global networks. It
accommodates multiple languages and currencies; supports
international features, such as flexible date formats and multiple
radix support; supports data in the Unicode Character Set (UTF-8)
and has accounting and business localizations built into it.

Technical Architecture:
PHP (Personal Home Page) or Portal becomes the gateway through
which the user has rights to access all the information to which they
have been granted access. Thus, R12 administrative tasks are
simplified while operations costs are reduced.

- Form based: Forms-based users are typically people involved
in the transactional operations of an organization.
- HTML/JSP's (Self Service): Self-service users are infrequent
users who want their interface with R12 to be as simple and as quick
as possible.
- Business Intelligence: Business intelligence users are senior
executives and managers who want an easy-to-use interface that
can be used to reveal critical business information and reports.
- Mobile: users whose jobs are likely to keep them away from a
readily available, network-connected computer. example : sales
representatives, field representative. By utilizing the mobile
interface, they are able to send and receive information at points
where it is important and convenient for them.

Wednesday, 11 July 2012

Customer Interface Tables Descriptions and Validation-11i

Customer Interface Tables

Customer Interface Table Descriptions and Validation

1- RA_CUSTOMERS_INTERFACE
This table stores customer, address, and business purpose information. You do not have to enter values in this table if you do not want to import customers, addresses, or business purposes.

1- ADDRESS1 through 4: Enter the address for your customer in these four columns. You can enter up to four lines of an address.
Validation: If you enter a value in ORIG_SYSTEM_ADDRESS_REF, you must enter a value in ADDRESS1. For multiple rows with the same address reference, insert values in address 1-4.
Destination:
1- RA_ADDRESSES.ADDRESS1,
2- RA_ADDRESSES.ADDRESS2,
3- RA_ADDRESSES.ADDRESS3,
4- RA_ADDRESSES.ADDRESS4

2- CITY STATE PROVINCE COUNTY POSTAL_CODE COUNTRY: Enter the city, state, province, county, and postal code for this customer's address. You must enter values that have already been defined in AR_LOCATION_VALUES if Address Validation is set to 'Error' and you are calculating sales tax. You must do this for each address component on which your tax location flexfield structure is based.

If Address Validation is set to 'No Validation' and you pass a value that does not exist in AR_LOCATION_VALUES, Customer Interface inserts this value into AR_LOCATION_VALUES.
You must always enter a value for Country.
Validation: The county must exist in FND_TERRITORIES.
Destination:
RA_ADDRESSES.CITY,
RA_ADDRESSES.STATE,
RA_ADDRESSES.PROVINCE,
RA_ADDRESSES.COUNTY
RA_ADDRESSES.POSTAL_CODE,
RA_ADDRESSES.COUNTRY

3- ADDRESS_KEY: This column is not currently used by Customer Interface.

4- BILL_TO_ORIG_ ADDRESS_REF: Enter the Bill-To location that you want to associate with the Ship-To address on this record.
Validation: You can enter a value only if the SITE_USE_CODE column in this record is 'Ship-To'. Also, the Bill-To address must exist for this customer or any related customers.
Destination: RA_SITE_USES.BILL_TO_SITE_USE_ID

5- CUSTOMER_ ATTRIBUTE_ CATEGORY: Enter Descriptive Flexfield category information. These columns are optional.

6- ADDRESS_ ATTRIBUTE_ CATEGORY
7- SITE_USE_ ATTRIBUTE_ CATEGORY
Validation:
None
Destination:
RA_CUSTOMERS.ATTRIBUTE_CATEGORY,
RA_ADDRESSES.ATTRIBUTE_CATEGORY,
RA_SITE_USES.ATTRIBUTE_CATEGORY

8- CUSTOMER_ ATTRIBUTE_1 to 15: Enter Descriptive Flexfield information. These columns are optional.

9- ADDRESS_ ATTRIBUTE_1 to 15
10- SITE_USE_ ATTRIBUTE_1 to 25
Validation: None
Destination:RA_CUSTOMERS.ATTRIBUTE1 to 15,
RA_ADDRESSES.ATTRIBUTE1 to 15,
RA_SITE_USES.ATTRIBUTE1 to 25

10- CUSTOMER_ CATEGORY_CODE: Enter a category to categorize your customer. Use customer categories that you previously defined in the QuickCodes window. This column is required.
Validation:
AR_LOOKUPS.LOOKUP_CODE where LOOKUP_TYPE = 'CUSTOMER_CATEGORY'
Destination: RA_CUSTOMERS.CUSTOMER_CATEGORY_ CODE

11-CUSTOMER_ CLASS_CODE: Enter the customer class for this customer. Use customer classes that you previously defined in the QuickCodes window. This column is optional.
Validation:
AR_LOOKUPS.LOOKUP_CODE where LOOKUP_TYPE = 'CUSTOMER CLASS'
Destination: RA_CUSTOMERS.CUSTOMER_CLASS

12-CUSTOMER_KEY: This column is not currently used by Customer Interface.

13-CUSTOMER_NAME: Enter the name of your customer. This column is required.
Validation:The same customer reference cannot have different customer names within this table.
Destination: RA_CUSTOMERS.CUSTOMER_NAME

14- CUSTOMER_ NUMBER: Enter this customer's number.
Validation: Must be null if you are using Automatic Customer Numbering. Must exist if you are not using Automatic Customer Numbering. This value must be unique within RA_CUSTOMERS.
Destination: RA_CUSTOMERS.CUSTOMER_NUMBER

15- CUSTOMER_STATUS: Enter the status of this customer. This column is required.
Validation: Must equal 'A' for Active or 'I' for Inactive.
Destination: RA_CUSTOMERS.STATUS

16- CUSTOMER_TYPE: Enter 'Internal' or 'External' to indicate customer type for this customer. This column is optional.
Validation: AR_LOOKUPS.LOOKUP_CODE where LOOKUP_TYPE = 'CUSTOMER_TYPE' Multiple rows with the same customer reference must have the same customer type.

17- CUST_TAX_CODE SITE_USE_TAX_ CODE: Enter the tax code that you want to assign to this customer or business purpose, depending on the column you choose. You must use tax codes that you previously defined in the Tax Codes and Rates window. These columns are optional.
Validation: Must exist in AR_VAT_TAX.
Destination: RA_CUSTOMERS.TAX_CODE RA_SITE_USES.TAX_CODE

18- CUST_TAX_ EXEMPT_NUM: This column is not currently used by Customer Interface and must be left blank.
Validation: None
Destination: None

19- CUST_TAX_ REFERENCE: Enter the tax registration number for this customer. This column is optional.
Validation: None
Destination: RA_CUSTOMERS.TAX_REFERENCE

20- CUST_SHIP_ VIA_CODE SITE_SHIP_ VIA_CODE: Enter the freight carrier you want to assign to this customer or business purpose, depending on the column you choose. Use freight carriers that you previously defined in the Freight Carriers window. This column is optional.
Validation: Must exist in ORG_FREIGHT.
Destination: RA_CUSTOMERS.SHIP_VIA, RA_SITE_USES.SHIP_VIA

21- CREATED_BY:Enter the user id that is creating this row. This column is required.
Validation: None
Destination: None

22- CREATION_DATE: Enter the system date. This column is required.
Validation: Must be a valid date format.
Destination: None

23- DEMAND_ CLASS_CODE: Enter the demand class for this address. Use demand classes that you previously defined in the Demand Classes window. This column is optional.
Validation: Must exist in FND_COMMON_LOOKUPS
Destination: RA_SITE_USES.DEMAND_CLASS_CODE

24- INSERT_UPDATE_ FLAG: Specify whether you are inserting a new record or updating an existing record. This column is required.
Validation: 'I' for insert or 'U' for update.
Destination: None

25- INTERFACE_STATUS: This column is used by Customer Interface and should be left null. The Customer Interface program updates this column with all error messages that apply to this interface record. If an interface record has several problems, the Customer Interface program updates this column with multiple error codes.

26- LOCATION: Enter a shorthand name for this business purpose. You use this value to quickly refer to a business purpose during data entry.
Validation: If automatic site numbering is set to No, you must enter a value in this column. If not, do not enter a value. Values for this column must be unique.
Destination: RA_SITE_USES.LOCATION
[Attention: This column is not updatable.]

27- LOCATION_CCID: This column is used by Customer Interface and should be left null. Customer Interface stores the code combination id of valid addresses in this column.
Validation: None
Destination: None

28- LAST_UPDATED_ BY: Enter the user id that is updating this row. This column is required.
Validation: None
Destination: None

29- LAST_UPDATE_ DATE: Enter the system date. This column is required.
Validation: Must be a valid date format.
Destination: None

30- LAST_UPDATE_ LOGIN: Enter the login id. This column is optional.
Validation: None
Destination: None

31- LANGUAGE: Enter the language used by this customer site. This column is optional.
Validation: Must exist in FND_LANGUAGES.NLS_LANGUAGE
Destination: RA_ADDRESSES.LANGUAGE

32- MESSAGE_ TEXT: This column is used by Customer Interface and should be left null.
Validation: None
Destination: None

33- ORIG_SYSTEM_ ADDRESS_REF: Enter a value you can use to uniquely identify this address in your original system.
This column forms part of the primary key for RA_CUSTOMERS_INTERFACE. The primary key is a combination of ORIG_SYSTEM_CUSTOMER_REF, ORIG_SYSTEM_ADDRESS_REF, and SITE_USE_CODE.
To enter multiple addresses for a customer, enter multiple records in RA_CUSTOMERS_INTERFACE with identical customer information, but with different address information.
This column is required if you are either inserting or updating address information.
Validation: Must not already exist in RA_ADDRESSES for insert. Must already exist in RA_ADDRESSES for update.
Destination:RA_ADDRESSES.ORIG_SYSTEM_REFERENCE

34- ORIG_SYSTEM_ CUSTOMER_REF: Enter a value that uniquely identifies this customer in your original system.
This column forms part of the primary key for RA_CUSTOMERS_INTERFACE. The primary key is a combination of ORIG_SYSTEM_CUSTOMER_REF, ORIG_SYSTEM_ADDRESS_REF, and SITE_USE_CODE.
If you are entering a new customer, you must also enter a customer level profile in RA_CUSTOMER_PROFILES_INTERFACE. This column is required.
Validation: Must not exist in RA_CUSTOMERS for insert. Must exist in RA_CUSTOMERS for update. The same customer reference cannot have different customer names within this table. Inserts for this column must be unique.
Destination: RA_CUSTOMERS.ORIG_SYSTEM_REFERENCE

35- ORIG_SYSTEM_ PARENT_REF: Enter the original system reference of the related customer, if there is one. If you enter a value in this column, the system checks RA_SYSTEM_PARAMETERS.CREATE_RECIPROCAL_FLAG to determine whether the application should automatically create the reciprocal relationship. If RA_SYSTEM_PARAMETERS.CREATE_RECIPROCAL_FLAG is set to 'Y', the system creates an additional, opposite entry in RA_CUSTOMER_RELATIONSHIPS. This column is optional.
Validation: Must exist in RA_CUSTOMERS. ORIG_SYSTEM_REFERENCE. Multiple rows with the same customer reference must have the same ORIG_SYSTEM_PARENT_REF.
Destination: Inserts into RA_CUSTOMER_RELATIONSHIPS. CUSTOMER_ID select CUSTOMER_ID from RA_CUSTOMERS where ORIG_SYSTEM_ REFERENCE = RA_CUSTOMERS_INTERFACE. ORIG_SYSTEM_PARENT_REF.

36- PRIMARY_SITE_ USE_FLAG: Enter 'Y' or 'N' to indicate whether this is the primary business purpose. Enter a value in this column only if the INSERT_UPDATE_FLAG is 'Y' and you enter a value in ORIG_SYSTEM_ADDRESS_REF.
Validation: Must have only one primary business purpose for each usage (Bill-To, Ship-To, etc.). Must be null, 'Y,' or 'N'. Mandatory when inserting an address and must be null when the record is for updating purposes. Not updateable.
Destination: RA_SITE_USES.PRIMARY_FLAG

37- REQUEST_ID: This column is used by Customer Interface and should be left null.

38- SITE_USE_CODE: Enter the business purpose for this customer's address. Use business purposes you previously defined in the Receivables QuickCodes window with a QuickCode type of 'Business purposes for a customer address.'
This column forms part of the primary key for RA_CUSTOMERS_INTERFACE. The primary key is a combination of ORIG_SYSTEM_CUSTOMER_REF, ORIG_SYSTEM_ADDRESS_REF, and SITE_USE_CODE.
If you enter a value in ORIG_SYSTEM_ ADDRESS_REF, you must enter a value in this column. To enter multiple business purposes for an address, enter multiple records in RA_CUSTOMERS_INTERFACE with identical customer and address information, but with different site uses. You can only assign one type of business purpose to each address.
Validation: Must equal a value in AR_LOOKUPS. LOOKUP_CODE where LOOKUP_TYPE = 'SITE_USE_CODE'. This column must be null if you are updating PRIMARY_SITE_USE_FLAG, LOCATION, SITE_SHIP_VIA_CODE, SITE_USE_TAX_CODE or SITE_USE_TAX_REFERENCE. Inserts for this column must be unique.
Destination: RA_SITE_USES.SITE_USE_CODE

39- SITE_USE_TAX_ EXEMPT_NUM: This column is not currently used by Customer Interface and must be left blank.
Validation: None
Destination: None

40- SITE_USE_TAX_ REFERENCE: Enter the tax registration number for this site. This column is optional.
Validation: None
Destination: RA_SITE_USES.TAX_REFERENCE

41- VALIDATED_FLAG: This column is used by Customer Interface and should be left null.
Destination: RA_CUSTOMERS.CUSTOMER_TYPE

42- WARNING_TEXT: This column is not currently used by Customer Interface.

43- RA_CUSTOMER_PROFILES_INTERFACE: This table stores customer profile information. If you are entering a new customer in RA_CUSTOMERS_INTERFACE, you must either pass a customer profile class that already exists or customer profile values. You do not have to enter values in this table if you are not entering a new customer or assigning customer profile information to customer addresses.

ACCOUNT_STATUS: Enter the status of this customer's account. Use account statuses you previously defined in the Receivables QuickCodes window with a QuickCode type of 'Account Status.' This column is optional.
Validation: AR_LOOKUPS.LOOKUP_CODE where LOOKUP_TYPE = 'ACCOUNT_STATUS'
Destination: AR_CUSTOMER_PROFILES.ACCOUNT_STATUS

44- ATTRIBUTE_ CATEGORY , AMOUNT_ ATTRIBUTE_ CATEGORY: Enter Descriptive Flexfield category information. These columns are optional.
Validation: None
Destination: AR_CUSTOMER_PROFILES. ATTRIBUTE_CATEGORY AR_CUSTOMER_PROFILE_AMOUNTS. ATTRIBUTE_CATEGORY

45- ATRIBUTE 1 through 15 AMOUNT_ ATTRIBUTE 1 through 15: Enter Descriptive Flexfield information. These columns are optional.
Validation: None
Destination: AR_CUSTOMER_PROFILES. ATTRIBUTE1 through 15 AR_CUSTOMER_PROFILE_AMOUNTS. ATTRIBUTE1 through 15

46- AUTO_REC_INCL_ DISPUTED_FLAG: Specify whether to include debit items that have been placed in dispute when you create automatic receipts for your customers. Defaults to No if null.
Validation: Must equal 'Y' (Yes) or 'N' (No)'. Mandatory when profile class is null.
Destination: AR_CUSTOMER_PROFILES.AUTO_REC_ INCL_DISPUTED_FLAG

47- AUTOCASH_ HIERARCHY_NAME: Enter the AutoCash Rule set to assign to this customer. Use AutoCash Rules sets that you previously defined in the AutoCash Rule Sets window.
Validation: Must exist in AR_AUTOCASH_HIERARCHIES. Mandatory when no profile class specified.
Destination: AR_CUSTOMER_PROFILES. AUTOCASH_HIERARCHY_ID

48- AUTO_REC_MIN_ RECEIPT_AMOUNT: Enter the minimum receipt amount that must be specified for this customer when you create automatic receipts in this currency. This column is optional.
Validation: None
Destination: AR_CUSTOMER_PROFILE_AMOUNTS. AUTO_REC_MIN_RECEIPT_AMOUNT

49- CHARGE_ON_ FINANCE_ CHARGE_FLAG: Specify whether you want to compound interest for this customer.
Validation: Must equal 'Y' (Yes) or 'N' (No). Required if INTEREST_CHARGES is set to 'Y' and no profile class is specified. Do not enter a value if INTEREST_CHARGES is null or set to 'N'.
Destination: AR_CUSTOMER_PROFILES.CHARGE_ON_ FINANCE_CHARGE_FLAG

50- CLEARING_DAYS: Enter the number of clearing days for this customer profile.
Validation: Must be an integer greater than or equal to zero.
Destination: AR_CUSTOMER_PROFILES.CLEARING_DAYS

51-COLLECTOR_NAME: Enter the collector assigned to this customer profile.
Validation: Must be unique in AR_COLLECTORS and STATUS = 'A' (Active). Mandatory when no profile class specified.
Destination: AR_CUSTOMER_PROFILES.COLLECTOR_ID (derived from CUSTOMER_NAME)

52- CREATED_BY: Enter the user id that is creating this row. This column is required.
Validation: None
Destination: None

53- CREATION_DATE: Enter the system date. This column is required.
Validation: Must be a valid date format.
Destination: None

54- CREDIT_BALANCE_ STATEMENTS: Specify whether to send statements to customers with credit balances.
Validation: Must equal 'Y' (Yes) or 'N' (No) Must be 'N' when STATEMENTS = 'N.' Mandatory when no profile class specified. Mandatory when STATEMENTS = Yes. Must be null when STATEMENTS is null.
Destination: AR_CUSTOMER_PROFILES.CREDIT_BALANCE_STATEMENTS

55- CREDIT_HOLD: Specify whether to put a hold on your customer's credit.
Validation: Must equal 'Y' (Yes) or 'N' (No)'.
Destination: AR_CUSTOMER_PROFILES.CREDIT_HOLD

56- CREDIT_RATING: Enter the credit rating for this customer. Use credit ratings you previously defined in the Receivables QuickCodes window using the QuickCode Type 'Credit rating for customers.' This column is optional.
Validation: AR_LOOKUPS.LOOKUP_CODE where LOOKUP_TYPE = 'CREDIT_RATING'
Destination: AR_CUSTOMER_PROFILES.CREDIT_RATING

57- CURRENCY_CODE: Enter a currency code to define customer profile amounts for this customer. Use currency codes previously defined in the Currencies window. Regardless of the value stored in INSERT_UPDATE_FLAG, Customer Interface always inserts and updates customer profile amount values you pass in this table. You must enter a value if any one of the following columns have values:
INTEREST_RATE
MIN_DUNNING_AMOUNT
MIN_DUNNING_INVOICE_AMOUNT
MIN_FC_BALANCE_AMOUNT
MIN_FC_INVOICE_AMOUNT
MIN_STATEMENT_AMOUNT
OVERALL_CREDIT_LIMIT
TRX_CREDIT_LIMIT
Validation: Must exist in FND_CURRENCIES. Mandatory when a profile amount value is populated. (Profile amount columns are listed above.)
Destination: AR_CUSTOMER_PROFILE_AMOUNTS. CURRENCY_CODE

58- CUSTOMER_PROFILE_CLASS_NAME: Enter the name of the customer profile class you want to assign to this customer or Bill-To address.
If you enter a value in this column, the system will use the profile values associated with this customer profile class. However, you can override these default values by passing new values in the appropriate columns. This column is optional.
If you do not enter a value in this column you must enter values in the following columns:
COLLECTOR_NAME
CREDIT_BALANCE_STATEMENTS
CREDIT_CHECKING
AUTO_REC_INCL_DISPUTED_FLAG
DISCOUNT_TERMS
DUNNING_LETTERS (if 'Y', you must also enter a value in DUNNING_LETTER_SET_NAME)
INTEREST_CHARGES (if 'Y', you must also enter values in INTEREST_PERIOD_DAYS and CHARGE_ON_FINANCE_CHARGE_FLAG.)
STATEMENTS (if 'Y', you must also enter a value in STATEMENT_CYCLE_NAME.)
TOLERANCE
TAX_PRINTING_OPTION
OVERRIDE_TERMS
GROUPING_RULE_NAME
Validation: Must equal AR_CUSTOMER_PROFILE_CLASSES. NAME and STATUS = 'A' (Active)
Destination: AR_CUSTOMER_PROFILES. CUSTOMER_PROFILE_CLASS_ID (derived from CUSTOMER_PROFILE_CLASS_NAME)

59- DISCOUNT_TERMS, CREDIT_CHECKING, DUNNING_LETTERS, INTEREST_CHARGES, STATEMENTS: Specify whether to allow discounts, check credit, send dunning letters, charge interest or and send statements.
Validation: Must equal 'Y' (Yes) or 'N' (No). Mandatory when no profile class specified.
Destination: AR_CUSTOMER_PROFILES.DISCOUNT_TERMS AR_CUSTOMER_PROFILES.CREDIT_CHECKING
AR_CUSTOMER_PROFILES. DUNNING_LETTERS
AR_CUSTOMER_PROFILES. INTEREST_CHARGES AR_CUSTOMER_PROFILES.STATEMENTS

60- DISCOUNT_ GRACE_DAYS: Enter the number of days after the discount date that this customer can still take discounts.
Validation: Number must be non-negative (must be >=0). Must be null when DISCOUNT_TERMS is null or 'No'.
Destination: AR_CUSTOMER_PROFILES.DISCOUNT_ GRACE_DAYS

61- DUNNING_LETTER_ SET_NAME: Enter the dunning letter set to associate with this customer. Use dunning letters that you previously defined in the Dunning Letter Sets window.
Validation: Must exist in AR_DUNNING_LETTER_SETS. Mandatory when DUNNING_LETTERS is Yes. Must be null when DUNNING_LETTERS is No or null.
Destination: AR_CUSTOMER_PROFILES.DUNNING_ LETTER_SET_ID (derived from DUNNING_LETTER_SET_NAME)

62- GROUPING_ RULE_NAME: Enter the grouping rule to assign to this customer. Use grouping rules you previously defined in the Grouping Rules window.
Validation: Must exist in RA_GROUPING_RULES. Mandatory when no profile class is specified.
Destination: AR_CUSTOMER_PROFILES.GROUPING_ RULE_ID (derived from GROUPING_RULE_NAME)

63- INTERFACE_STATUS: This column is used by Customer Interface and should be left null. The Customer Interface program updates this column with all error messages which apply to this interface record. If an interface record has several problems, the Customer Interface program updates this column with multiple error codes which are described later in this section.
Validation: None
Destination: None

64- INSERT_ UPDATE_FLAG: Enter a value to indicate whether you are inserting a new record or updating an existing record.
Regardless of the value you enter in this column, you cannot insert or update profile information in the following columns:
CURRENCY_CODE,
AUTO_REC_MIN_RECEIPT_AMOUNT,
INTEREST_RATE,
MAX_INTEREST_CHARGE,
MIN_DUNNING_AMOUNT,
MIN_DUNNING_INVOICE_AMOUNT,
MIN_FC_BALANCE_AMOUNT,
MIN_FC_INVOICE_AMOUNT,
MIN_STATEMENT_AMOUNT,
OVERALL_CREDIT_LIMIT,
TRX_CREDIT_LIMIT,
AMOUNT_ATTRIBUTE_CATEGORY and
AMOUNT_ATTRIBUTE1 through AMOUNT_ATTRIBUTE15.
If you are tying to insert new profile amount information, Customer Interface will automatically insert this information even if this column is set to 'U.' For example, if you want to update the tax printing option value for a record that you have already inserted and at the same time enter a new currency code for this customer profile, enter 'U' in this column. Customer Interface will automatically update the tax printing option value and automatically insert the new currency code.
This column is required.
Validation: I' for insert, 'U' for update
Destination: None

65- INTEREST_ PERIOD_DAYS: Enter the number of days to which the interest rate refers.
Validation: Number must be positive. Mandatory when INTEREST_CHARGES is Yes. Must be null when INTEREST_CHARGES is No or null.
Destination: AR_CUSTOMER_PROFILES.INTEREST_ PERIOD_DAYS

66- INTEREST_RATE: Enter the interest rate to charge this customer for this currency. This column is optional.
Validation: None
Destination: AR_CUSTOMER_PROFILE_AMOUNTS. INTEREST_RATE

67- LAST_UPDATED_ BY: Enter the user id that is updating this row. This column is required.
Validation: None
Destination: None

68- LAST_UPDATE_ DATE: Enter the system date. This column is required.
Validation: Must be a valid date format.
Destination: None

69- LAST_UPDATE_ LOGIN: Enter the login id. This column is optional.
Validation: None
Destination: None

70- MAX_INTEREST_ CHARGE: Enter the maximum amount of interest to charge this customer in this currency for each invoice. This column is optional.
Validation: None
Destination: AR_CUSTOMER_PROFILE_AMOUNTS. MAX_INTEREST_CHARGE

71- MIN_DUNNING_ AMOUNT: Enter the minimum amount in this currency that must be past due for this customer before you select these customers for dunning. This column is optional.
Validation: None
Destination: AR_CUSTOMER_PROFILE_AMOUNTS. MIN_DUNNING_AMOUNT

72- MIN_DUNNING_ INVOICE_AMOUNT: Enter the minimum invoice amount in this currency that must be past due for this customer before you select these customers for dunning. This column is optional.
Validation: None
Destination: AR_CUSTOMER_PROFILE_AMOUNTS. MIN_DUNNING_INVOICE_AMOUNT

73- MIN_FC_BALANCE_ AMOUNT: Enter the minimum customer balance that you require before you charge this customer finance charges for past due items in this currency. This column is optional.
Validation: None
Destination: AR_CUSTOMER_PROFILE_AMOUNTS. MIN_FC_BALANCE_AMOUNT

74- MIN_FC_INVOICE_ AMOUNT: Enter the minimum invoice balance that you require before you charge this customer finance charges for past due items in this currency. This column is optional.
Validation: None
Destination: AR_CUSTOMER_PROFILE_AMOUNTS. MIN_FC_INVOICE_AMOUNT

75- MIN_STATEMENT_ AMOUNT: Enter the minimum outstanding balance in this currency that this customer must exceed in order for the system to generate a statement. This column is optional.
Validation: None
Destination: AR_CUSTOMER_PROFILE_AMOUNTS. MIN_STATEMENT_AMOUNT

76- ORIG_SYSTEM_ CUSTOMER_REF: Enter the value that represents the customer or Bill-To site for which you are inserting or updating customer profile information.
Validation: For insert, this customer reference must exist in RA_CUSTOMERS or be successfully validated in RA_CUSTOMERS_INTERFACE. For update, this customer reference must exist in RA_CUSTOMERS.
Destination: AR_CUSTOMER_PROFILES.CUSTOMER_ID (Derived from ORIG_SYSTEM_CUSTOMER_REF)

77- ORIG_SYSTEM_ ADDRESS_REF: Enter the value that represents the customer Bill-To address for which you are inserting or updating customer profile information. An active Bill-To business purpose must be associated with this address.
Validation: For insert, this address reference must exist in RA_ADDRESSES or be successfully validated in RA_CUSTOMERS_INTERFACE. For update, this address reference must exist in RA_ADDRESSES.
Destination: AR_CUSTOMER_PROFILES.SITE_USE_ID (derived from ORIG_SYSTEM_ADDRESS_REF)

78- OVERALL_ CREDIT_LIMIT: Enter the total amount of credit to give to this customer in this currency. This column is optional.
Validation: TRX_CREDIT_LIMIT and OVERALL_CREDIT_ LIMIT must both be filled in, or both be null. TRX_CREDIT_LIMIT may not be greater than the OVERALL_CREDIT_LIMIT.

79- OVERRIDE_TERMS: Specify whether you want to be able to enter payment terms that are different from the payment term you enter in STANDARD_TERM_NAME.
Validation: Must equal 'Y' (Yes) or 'N' (No). Mandatory when no profile class is specified.
Destination: AR_CUSTOMER_PROFILES.OVERRIDE_TERMS

80- PAYMENT_ GRACE_DAYS: Enter the number of days you will allow this customer's receipt to be overdue before you will initiate collection action.
Validation: Number must be non-negative (must be >=0).
Destination: AR_CUSTOMER_PROFILES.PAYMENT_ GRACE_DAYS

81- PERCENT_ COLLECTABLE: Enter the percentage of this customer's account balance that you expect to collect regularly. This column is optional.
Validation: Must be between 0 to 100.
Destination: AR_CUSTOMER_PROFILES.PERCENT_ COLLECTABLE

82- REQUEST_ID: This column is used by Customer Interface, and should be left null.
Validation: None
Destination: None

83- RISK_CODE: Enter the risk code for this customer. Use risk codes you previously defined in the QuickCodes window with a QuickCode type of 'Customer credit risk.' This column is optional.
Validation: AR_LOOKUPS.LOOKUP_CODE where LOOKUP_TYPE = 'RISK_CODE'
Destination: AR_CUSTOMER_PROFILES.RISK_CODE

84- STANDARD_ TERM_NAME: Enter the standard payment terms for this customer. Use payment terms that you previously defined in the Payment Terms window. This column is optional.
Validation: Must exist in RA_TERMS. Must have a unique value. Mandatory when no profile class is specified.
Destination: AR_CUSTOMER_PROFILES.STANDARD_ TERMS. (derived from STANDARD_TERM_NAME)

85- STATEMENT_ CYCLE_NAME: Enter the statement cycle to associate with this customer. Use statement cycles that you previously defined in the Statement Cycles window.
Validation: Must exist in AR_STATEMENT_CYCLES. Must be null when STATEMENTS is No or null. Mandatory when STATEMENTS is Yes. Must have a unique value.
Destination: AR_CUSTOMER_PROFILES.STATEMENT_ CYCLE_ID (derived from STATEMENT_CYCLE_NAME)

86- TAX_PRINTING_ OPTION: Enter a tax printing option to indicate how you want to print tax information for this customer's invoices.
Validation: AR_LOOKUPS.LOOKUP_CODE where LOOKUP_TYPE = 'TAX_PRINTING_OPTION'. Mandatory when no profile class specified.
Destination: AR_CUSTOMER_PROFILES. TAX_PRINTING_OPTION

87- TOLERANCE: Enter the percent over the credit limit that this customer can exceed before you will act.
Validation: Must be between -100 and 100. Mandatory when no profile class specified.
Destination: RA_CUSTOMER_PROFILES.TOLERANCE

88- TRX_CREDIT_ LIMIT: Enter the amount of credit for each order that you want to give to this customer in this currency.
Validation: TRX_CREDIT_LIMIT and OVERALL_CREDIT_ LIMIT must both be filled in, or both be null. TRX_CREDIT_LIMIT may not be greater than the OVERALL_CREDIT_LIMIT.
Destination: AR_CUSTOMER_PROFILE_AMOUNTS. TRX_CREDIT_LIMIT

89- VALIDATED_FLAG: This column is used by Customer Interface, and should be left null.
Validation: None
Destination: None
Destination:
AR_CUSTOMER_PROFILE_AMOUNTS. OVERALL_CREDIT_LIMIT



RA_CONTACT_PHONES_INTERFACE
This table stores telephone numbers for customers, addresses and contacts as well as contacts for customers and addresses. You do not have to enter values in this table if you do not want to pass telephone or contact information.

1- CONTACT_ ATTRIBUTE_ CATEGORY,
PHONE_ ATTRIBUTE_ CATEGORY: Enter Descriptive Flexfield category information. These columns are optional.
Validation: None
Destination: RA_CONTACTS.ATTRIBUTE_CATEGORY RA_PHONES.ATTRIBUTE_CATEGORY

2- CONTACT_ ATTRIBUTE 1 through 25, PHONE_ ATTRIBUTE 1 through 15: Enter Descriptive Flexfield information. These columns are optional.
Validation: None
Destination: RA_CONTACTS.ATTRIBUTE1 through 25

3- CONTACT_ FIRST_NAME: Enter the contact's first name. This column is optional.
Validation: None
Destination: RA_CONTACTS.FIRST_NAME

4- CONTACT_ JOB_TITLE: Enter the job title or responsibility for this contact. Use contact job titles that you previously defined in the QuickCodes window. This column is optional.
Validation: AR_LOOKUPS.LOOKUP_CODE where LOOKUP_TYPE = 'JOB_TITLE'
Destination: RA_CONTACTS.JOB_TITLE.

5- CONTACT_ LAST_NAME: Enter the contact's last name. If ORIG_SYSTEM_CONTACT_REF is filled in, then you must enter a value in this column. Otherwise, this column is optional.
Validation: None
Destination: RA_CONTACTS.LAST_NAME

6- CONTACT_TITLE: Enter the title for this contact. This column is optional.
Validation: AR_LOOKUPS.LOOKUP_CODE where LOOKUP_TYPE = 'CONTACT_TITLE'
Destination: RA_CONTACTS.TITLE

7- CREATED_BY: Enter the user id that is creating this row. This column is required.
Validation: None
Destination: None

8- CREATION_DATE: Enter the system date. This column is required.
Validation: Must be a valid date format.
Destination: None

9- INTERFACE_STATUS: This column is used by Customer Interface and should be left null. The Customer Interface program updates this column with all error messages that apply to this interface record. If an interface record has several problems, the Customer Interface program updates this column with multiple error codes.

10- INSERT_ UPDATE_FLAG: Enter a value to indicate whether you are inserting a new record or updating an existing record. This column is required.
Validation: 'I' for insert, 'U' for update.
Destination: None

11- LAST_UPDATED_ BY: Enter the user id that is updating this row. This column is required.
Validation: None
Destination: RA_PHONES.ATTRIBUTE1 through 15

12- LAST_UPDATE_ DATE: Enter the system date. This column is required.
Validation: Must be a valid date format.
Destination: None

13- LAST_UPDATE_ LOGIN: Enter the login id. This column is optional.
Validation: None
Destination: None

14- ORIG_SYSTEM_ CUSTOMER_REF: Enter a value that you can use to uniquely identify this customer in your original system.
Validation: Must equal RA_CUSTOMERS.ORIG_SYSTEM_ REFERENCE for update. If you are entering either contact or telephone information, you must enter a value in this column.
Destination: RA_CUSTOMERS.ORIG_SYSTEM_REFERENCE

15- ORIG_SYSTEM_ ADDRESS_REF: Enter a value that you can use to uniquely identify this address in your original system.
If this column is null, the phone or contact that you enter refers to the customer.
Validation: Must equal RA_ADDRESSES.ORIG_SYSTEM_ REFERENCE for update. If you are entering information that refers to an address, such as a contact or telephone, then you must enter a value in this column.
Destination: RA_ADDRESSES.ORIG_SYSTEM_REFERENCE

16- ORIG_SYSTEM_ CONTACT_REF: Enter a value that you can use to uniquely identify this contact in your original system.
This column forms part of the primary key for RA_CONTACT_PHONES_ INTERFACE. The primary key is a combination of ORIG_SYSTEM_CONTACT_REF and ORIG_SYSTEM_TELEPHONE_ REF.
To enter a contact for a customer, do not enter a value in ORIG_SYSTEM_ ADDRESS_REF.
If you are entering a contact for a specific address, then enter values in both ORIG_ SYSTEM_CUSTOMER_REF and ORIG_SYSTEM_ADDRESS_REF.
Validation: Must equal RA_CONTACTS.ORIG_SYSTEM_ REFERENCE for update. If you are entering contact information or information that refers to a contact, such as a telephone number assigned to a contact, you must enter a value in this column.
Destination: RA_CONTACTS.ORIG_SYSTEM_REFERENCE

17- ORIG_SYSTEM_ TELEPHONE_REF: Enter a value that you can use to uniquely identify this telephone in your original system.
This column forms part of the primary key for RA_CONTACT_PHONES_ INTERFACE. The primary key is a combination of ORIG_SYSTEM_CONTACT_REF and ORIG_SYSTEM_TELEPHONE_REF.
To enter a telephone for a customer, do not enter values in ORIG_SYSTEM_ADDRESS_REF or ORIG_SYSTEM_CONTACT_REF.
To enter a telephone for a specific address, enter values in ORIG_SYSTEM_CUSTOMER_REF and ORIG_SYSTEM_ADDRESS_ REF.
To enter telephones for a specific contact, enter values in ORIG_SYSTEM_CUSTOMER_REF, ORIG_SYSTEM_CONTACT_REF, and ORIG_SYSTEM_ADDRESS_REF, if the contact is associated with an address.
Validation: Must equal RA_PHONES.ORIG_SYSTEM_ REFERENCE. Mandatory when specifying telephone information.
Destination: RA_PHONES.ORIG_SYSTEM_REFERENCE

18- REQUEST_ID: This column is used by Customer Interface and should be left null.

19- TELEPHONE: Enter the telephone number for the customer, address, or contact.
Validation: Mandatory when specifying telephone information (for example, if ORIG_SYSTEM_TELEPHONE_REF is filled in).
Destination: RA_PHONES.PHONE_NUMBER

20- TELEPHONE_ AREA_CODE, TELEPHONE_ EXTENSION: Enter the area code or extension for the telephone number, depending on the column you choose. These columns are optional.
Validation: None
Destination: RA_PHONES.AREA_CODE RA_PHONES.EXTENSION

21- TELEPHONE_TYPE: Enter the type of telephone number such as General, Fax, or Telex. Use telephone types that you previously defined in the QuickCodes window with a QuickCode type of 'Types of communication used in contacting customers.'
Validation: AR_LOOKUPS.LOOKUP_CODE where LOOKUP_TYPE = 'COMMUNICATION_TYPE.' Mandatory when specifying telephone information (for example, if ORIG_SYSTEM_TELEPHONE_REF is filled in).
Destination: RA_CONTACTS.PHONE_TYPE

22- VALIDATED_FLAG: This column is used by Customer Interface and should be left null.



RA_CUSTOMER_BANKS_INTERFACEThis table stores bank information for a customer or for a specific Bill-To address. You do not have to enter values in this table if you do not want to insert or assign customer bank information. If you associate an automatic payment method to a customer or a customer's Bill-To business purpose, you must enter a bank account for this customer.

1- ATTRIBUTE_ CATEGORY: Enter Descriptive Flexfield category information. This column is optional.
Validation: None
Destination: AP_BANK_ACCOUNT_USES.ATTRIBUTE_ CATEGORY

2- ATTRIBUTE 1 through 15: Enter Descriptive Flexfield information. This column is optional.
Validation: None
Destination: AP_BANK_ACCOUNT_USES.ATTRIBUTE 1 through 15

3- BANK_ ACCOUNT_NUM, BANK_ACCOUNT_ CURRENCY_CODE: Enter the account number or currency code for this bank account, depending on the column you choose. This number must be unique within a bank branch.
Validation: If the bank account already exists, do not enter a value. If the bank account does not exist, you must enter a value.
Destination: AP_BANK_ACCOUNT.BANK_ACCOUNT_NUM AP_BANK_ACCOUNTS.CURRENCY_CODE

4- BANK_ACCOUNT_ INACTIVE_DATE: Enter the date that this bank account becomes inactive. This column is optional.
Validation: Must be a valid date format.
Destination: AP_BANK_ACCOUNTS.INACTIVE_DATE

5- BANK_ACCOUNT_ DESCRIPTION: Enter a description for this bank account. This column is optional.
Validation: None
Destination: AP_BANK_ACCOUNTS.DESCRIPTION

6- BANK_ ACCOUNT_CHECK_ DIGITS: Enter the number this bank account prints on checks. This column is optional.
Validation: None
Destination: AP_BANK_ACCOUNT.CHECK_DIGITS

7- BANK_ ACCOUNT_NAME: Enter the bank account name to assign to this customer or Bill-To address. Use the Banks window to define banks and bank accounts for your customers. If the bank account has not already been defined, Customer Interface will try to create it in AP_BANK_ACCOUNTS. This column is required.
Validation: Must exist in AP_BANK_ACCOUNTS or, if it does not exist, values must exist for BANK_ACCOUNT_CURRENCY_CODE, BANK_ACCOUNT_NUM, BANK_NAME, and BANK_BRANCH_NAME.
Destination: AP_BANK_ACCOUNTS.BANK_ACCOUNT_ NAME

8- BANK_NAME, BANK_ BRANCH_NAME: Enter the name of the bank or bank branch for the account you are inserting. If the bank account does not exist, you must enter a value.
Validation: BANK_NAME together with BANK_BRANCH_NAME must be unique. If the bank account already exists, do not enter a value. Bank_Branch_Name is mandatory when the bank account is not defined.
Destination: AP_BANK_ACCOUNTS.BANK_NAME AP_BANK_BRANCHES.BANK_BRANCH_NAME

9- BANK_NUMBER: Enter the number of the bank associated with the bank account.
Validation: Must be unique. If the bank account already exists, do not enter a value. If the bank account does not exist, this column is optional.
Destination: AP_BANK_BRANCHES.BANK_NUMBER

10- BANK_NUM: Enter the number of the bank branch associated with the bank account you are inserting.
Validation: Must be unique. If the bank account already exists, do not enter a value. If the bank account does not exist, this column is optional.
Destination: AP_BANK_BRANCHES.BANK_NUM

11- BANK_BRANCH_ DESCRIPTION: Enter a description for this bank branch. This column is optional.
Validation: None
Destination: AP_BANK_BRANCHES.DESCRIPTION

12- BANK_BRANCH_ ADDRESS 1 through 4: Enter the street address, city, county, state, postal code, or province for this bank branch. These columns are optional.
BANK_ BRANCH_CITY
BANK_ BRANCH_COUNTY
BANK_ BRANCH_STATE
BANK_ BRANCH_ZIP
BANK_ BRANCH_PROVINCE
Validation: None
Destination: AP_BANK_BRANCHES.ADDRESS_LINE1-3
AP_BANK_BRANCHES.CITY
AP_BANK_BRANCHES.STATE
AP_BANK_BRANCHES.ZIP
AP_BANK_BRANCHES.PROVINCE

13- BANK_ BRANCH_COUNTRY: Enter the country for this bank branch. This column is optional.
Validation: Must exist in FND_TERRITORIES.TERRITORY_CODE.
Destination: AP_BANK_BRANCHES.COUNTRY

14- BANK_ BRANCH_PHONE : Enter the telephone number or telephone area code for this bank branch. These columns are optional.
BANK_ BRANCH_ AREA_CODE
Validation: None
Destination: AP_BANK_BRANCHES.PHONE
AP_BANK_BRANCHES.AREA_CODE

15- BANK_BRANCH_EFT_USER_NUMBER: Enter the Electronic Funds Transfer user id. This column is optional.
Validation: None
Destination: AP_BANK_BRANCHES.EFT_USER_NUMBER

16- BANK_ACCOUNT_ ATT_CATEGORY : Enter Descriptive Flexfield category information. These columns are optional.
BANK_ BRANCH_ ATT_CATEGORY
Validation: None
Destination: AP_BANK_ACCOUNTS.ATTRIBUTE_ CATEGORY AP_BANK_BRANCHES.ATTRIBUTE_CATEGORY

17- BANK_ACCOUNT_ ATTRIBUTE 1 through 15, BANK_ BRANCH_ ATTRIBUTE 1 through 15: Enter Descriptive Flexfield information. These columns are optional.
Validation: None
Destination: AP_BANK_ACCOUNTS.ATTRIBUTE1 through 15 AP_BANK_BRANCHES.ATTRIBUTE1 through 15

18- CREATED_BY: Enter the user id that is creating this row. This column is required.
Validation: None
Destination: None

19- CREATION_DATE: Enter the system date. This column is required.
Validation: Must be a valid date format.
Destination: None

20- END_DATE: Enter the date that this bank account becomes inactive. This column is optional.
Validation: End Date cannot be before the start date. Customers can be assigned to multiple bank accounts as long as there is no overlapping date range. Must be a valid date format.
Destination: AP_BANK_ACCOUNT_USES.END_DATE

21- INTERFACE_STATUS: This column is used by Customer Interface and should be left null. The Customer Interface program updates this column with all error messages that apply to this interface record. If an interface record has several problems, the Customer Interface program updates this column with multiple error codes.
Validation: None
Destination: None

22- LAST_UPDATED_ BY: Enter the user id that is updating this row. This column is required.
Validation: None
Destination: None

23- LAST_UPDATE_ DATE: Enter the system date. This column is required.
Validation: Must be a valid date format.
Destination: None

24- LAST_UPDATE_ LOGIN: Enter the login id. This column is optional.
Validation: None
Destination: None

25- ORIG_SYSTEM_ CUSTOMER_REF: Enter the value that represents the customer for which you are inserting bank information. This column is required.
Validation: The customer reference must exist in RA_CUSTOMERS or be successfully validated in RA_CUSTOMERS_INTERFACE.
Destination: AP_BANK_ACCOUNT_USES.CUSTOMER_ID (derived from ORIG_SYSTEM_CUSTOMER_REF)

26- ORIG_SYSTEM_ ADDRESS_REF: Enter the value that represent the customer address for which you are inserting bank information. An active Bill-To business purpose must be associated with this address. This column is required only if you want to insert bank information for a specific Bill-To address.
Validation: For insert, the address reference must exist in RA_ADDRESSES or be successfully validated in RA_CUSTOMERS_INTERFACE (derived from ORIG_SYSTEM_CUSTOMER_REF)
Destination: AP_BANK_ACCOUNT_USES.CUSTOMER_SITE_USE_ID

27- PRIMARY_FLAG: Indicates whether this is the primary bank account for this customer or Bill-To address. This column is required.
Validation: Enter 'Y' or 'N.' Only one primary bank account can exist at either the customer level or address level.
Destination: AP_BANK_ACCOUNT_USES.PRIMARY_FLAG

28- REQUEST_ID: This column is used by Customer Interface, and should be left null.
Validation: None
Destination: None

29- START_DATE: Enter the starting date that this bank account becomes active. This column is required.
Validation: End Date cannot be before the start date. Customers can be assigned to multiple bank accounts as long as there is no overlapping date range. Must be a valid date format.
Destination: AP_BANK_ACCOUNT_USES.START_DATE

30- VALIDATED_FLAG: This column is used by Customer Interface, and should be left null.
Validation: None
Destination: None


RA_CUST_PAY_METHOD_INTERFACEThis table stores payment method information for a customer or for a specific Bill-To address. If you associate an automatic payment method to a customer or a customer's Bill-To address, a bank account must exist for this customer. You do not have to enter values in this table if you do not want to assign a payment method to a customer. You cannot insert payment methods using Customer Interface. You must use the Payment Methods window to create new payment methods.

1- ATTRIBUTE_ CATEGORY, ATTRIBUTE 1 through 15: Enter Descriptive Flexfield category information. This column is optional.
Validation: None
Destination: RA_CUST_RECEIPT_METHODS. ATTRIBUTE_CATEGORY
RA_CUST_RECEIPT_METHODS.ATTRIBUTE 1 through 15

2- CREATED_BY: Enter the user id that is creating this row. This column is required.
Validation: None
Destination: None

3- CREATION_DATE: Enter the system date. This column is required.
Validation: Must be a valid date format.
Destination: None

4- END_DATE: Enter the date that this payment method becomes inactive. This column is optional.
Validation: Customers can be assigned to multiple payment methods as long as there is no overlapping date range. Must be a valid date format.
Destination: RA_CUST_RECEIPT_METHODS.END_DATE

5- INTERFACE_STATUS: This column is used by Customer Interface and should be left null. The Customer Interface program updates this column with all error messages that apply to this interface record. If an interface record has several problems, the Customer Interface program updates this column with multiple error codes.
Validation: None
Destination: None

6- LAST_UPDATED_ BY: Enter the user id that is updating this row. This column is required.
Validation: None
Destination: None

7- LAST_UPDATE_ DATE: Enter the system date. This column is required.
Validation: Must be a valid date format.
Destination: None

8- LAST_UPDATE_ LOGIN: Enter the login id. This column is optional.
Validation: None
Destination: None

9- ORIG_SYSTEM_ CUSTOMER_REF: ATTRIBUTE 1 through 15: Enter the value that represents the customer for which you are inserting a payment method. This column is required.
Validation: The customer reference must exist in RA_CUSTOMERS or be successfully validated in RA_CUSTOMERS_INTERFACE.
Destination: RA_CUST_RECEIPTS_METHODS. CUSTOMER_ID (derived from ORIG_SYSTEM_CUSTOMER_REF)

10- ORIG_SYSTEM_ ADDRESS_REF: Enter the value that represents the customer address for which you are inserting a payment method. An active Bill-To business purpose must be associated with this address. This column is required only if you are inserting a payment method for a specific Bill-To address.
Validation: This address reference must exist in RA_ADDRESSES or be successfully validated in RA_CUSTOMERS_INTERFACE.
Destination: RA_CUST_RECEIPT_METHODS.SITE_USE_ID (derived from ORIG_SYSTEM_ADDRESS_REF)

11- PAYMENT_ METHOD_NAME: Enter the name of the payment method that you want to assign to this customer or Bill-To address. This column is required.
Validation: Must exist in AR_RECEIPT_METHODS.
Destination: RA_CUST_RECEIPTS_METHODS. RECEIPT_METHOD_ID (derived from PAYMENT_METHOD_NAME)

12- PRIMARY_FLAG: Enter 'Y' or 'N' to indicate whether this is the primary payment method for this customer or Bill-To address. This column is required.
Validation: Only one primary payment method can exist at either the customer level or Bill-To address level.
Destination: RA_CUST_RECEIPTS_METHODS. PRIMARY_FLAG

13- REQUEST_ID: This column is used by Customer Interface and should be left null.
Validation: None
Destination: None

14-START_DATE: Enter the starting date that this payment method becomes active. This column is required.
Validation: Customers can be assigned to multiple payment methods as long as there is no overlapping date range. Must be a valid date format.
Destination: RA_CUST_RECEIPT_METHODS.START_DATE

15- VALIDATED_FLAG: This column is used by Customer Interface and should be left null.
Validation: None
Destination: None

Oracle app’s technical interview questions

Oracle app’s technical interview questions
    1) What is ERP? Architecture of apps?
             A packaged business software system that lets a company automate and integrate the majority of its business processes; share common data and practices across the enterprise; [and] produce and access information in a real-time environment.
    2) Tell me some thing about SQL-LOADER.
    Sql * loader is a bulk loader utility used for moving data from external files into the oracle database.
    Sql * loader supports various load formats, selective loading, and multi-tables loads.             
        1) conventional
           --The conventional path loader essentially loads the data by using standard ‘insert’ statement.
        2) direct
                 -- the direct path loader (direct = true) by possess of logic involved with that, and loads directly in to the oracle data files.
EX:-
    My data.csv file
                     1001, “scott tiger”,1000,40
                     1002,”gvreddy”,2345,50
    Load data
    Infile ‘c:\data\mydata.csv’
    Into table emp
    Fields terminated by “,” optionally enclosed by ‘”’
    (empno, empname,sal,deptno)
>sqlldr scott/tiger@vis
    control=loader.ctl  log= gvlog.log  bad=gvbad.bad  discard=gvdis.dsc .
3) how do u dump data from pl/sql block to flat files?
       Using utl_file package, we can dump data from pl/sql block to flat file.
       PRE-REQUIREMENTS for UTL_FILE is specify the accessible directories for the UTL_FILE function in the initialization file (INIT.ORA) Using the UTL_FILE_DIR parameters.
       Ex: UTL_FILE_DIR = <Directory name>
EX:-
        --remember to update INITSID.ORA,
        --utl_file_dir = ‘c:\oradata’
Declare
Fp utl_file.file_type;
Begin
Fp := utl_file.fopen(c:\oradata’,tab1.txt’,’w’);
Utl_file.putf(fp,’%s %s \n ‘text field’, 55);
Utl_file.fclose(fp);
End;
4) What is SET-OF-BOOKS?
    Collection of Chat of Accounts and Currency and Calendars is called SOB
  1. What is the interface?
             Interface Table is a table which is used as medium for transfer of data between two systems.
  1. What is invoice?
          Send you a request for payment
  1. What is INBOUND and OUT BOUND? (Different types of interfaces)
        Inbound Interface:
             For inbound interfaces, where these products are the destination, interface tables as well as supporting validation, processing, and maintenance programs are provided.

     
      Outbound Interface:
             For outbound interfaces, where these products are the source, database views are provided and the destination application should provide the validation, processing, and maintenance programs
  1. Tell me what r the Base tables in the AR?
    hz_parties      (party_id)     (store info about org, groups and people)
                     HZ_PARTIES stores information about parties such as organizations, people, and groups, including the identifying address information for the party.
      hz_cust_accounts (cust_account_id)
                HZ_CUST_ACCOUNTS stores information about customer relationships. If a party becomes a customer, information about the customer account is stored in this table. You can establish multiplecustomer relationships with a single party, so each party can have multiple customer account records in this table.
      hz_cust_acct_sites_all  (cust_acct_site_id)
                HZ_CUST_ACCT_SITES_ALL stores information about customer sites.One customer account can have multiple sites. The address is maintained in HZ_LOCATIONS.
      hz_cust_site_uses_all   (site_use_id)
                HZ_CUST_SITE_USES_ALL stores information about site uses or business purposes. A single customer site can have multiple site uses, such as bill to or ship to, and each site use is stored as a record in this table.
      hz_party_sites (party_site_id)
              HZ_PARTY_SITES stores information about the relationship between Parties and Locations. The same party can have multiple party sites.Physical addresses are stored in HZ_LOCATIONS.
      hz_locations (location_id)
                HZ_LOCATIONS stores information about physical locations.
      hz_Person_Profiles (person_profile_id)
                   HZ_PERSON_PROFILES stores detail information about people.
      hz_Organization_Profiles (organization_profile_id)
              HZ_ORGANIZATION_PROFILES stores credit rating, financial statistics, socioeconomic and corporate linkage information for business sites. The primary key for this table is ORGANIZATION_PROFILE_ID.




  1. What r the table’s interface tables in the customer interface tables?
  2. Ra_customers_interface_all
               This table stores customer, address, and business purpose information. You do not have to enter values in this table if you do not want to import customers, addresses, or business purposes.
ADDRESS1 through 4
                       Enter the address for your customer in these four columns. You can enter up to four lines of an address.
Validation:
      If you enter a value in ORIG_SYSTEM_ADDRESS_REF, you must enter a value in ADDRESS1. For multiple rows with the same address reference, insert values in address 1–4.
  Destination:                      HZ_LOCATIONS.ADDRESS1,
                                             HZ_LOCATIONS.ADDRESS2,
                                             HZ_LOCATIONS.ADDRESS3,
                                             HZ_LOCATIONS.ADDRESS4
           2) Ra_customer_profile_int_all
A customer level profile must exist in A_CUSTOMER_PROFILES_INTERFACE for new customers and each bill–to business purpose.
           3) Ra_contact_phones_int_all
This table stores telephone numbers for customers, addresses and contacts as well as contacts for customers and addresses.
           4) Ra_customer_banks_int_all
This table stores bank information for a customer or for a specific Bill–To address, you must enter a bank account for this customer,
           5) Ra_cust_paymethod_int_all
To import payment methods for customers and bill–to business purposes,
  1. What r the staging tables in the customer interface?
                    Ra_customers_stg
                   Ra_customers_address_stg
                    Ra_customers_point_stg
                    Ra_customers_contact points_stg
                    Ra_customers_relate_stg
                    Ra_customer_error.
  1. Tell me some mandatory columns in the customer interface tables?
    a) Ra_customers_interface_all
             1) Orig_system_customer_ref
            2) insert_update_flag
            3) customer_number 
             4) customer_status
                 5)last_updated_by    

                 6) last_updated_date 
                 7)created_by             
                  8) creation_date.       
         b) Ra_customer_profile_int_all
                    1) customer_profile_class_name
         c) Ra_contact_phones_int_all
                    1)orig_system_telephone_ref
                    2)telephone
                    3) telephone_type
         d) Ra_customer_banks_int_all
                    1) bank_a/c_name     
                    2)bank_a/c_no  
                    3)bank_a/c_currency_code
         e) Ra_cust_paymethod_int_all
                     1) payment_method_name
  1. Tell me the Navigation for customer interface?
                 Receivables à Interface à Customers
     
1. What tables cannot be updated through customer interface?
No updates will be allowed to be made through customer interface for 
Following tables
RA_SITE_USES_ALL
RA_CUSTOMER_RELATIONSHIPS_ALL
RA_CUST_RECEIPT_METHODS
AP_BANK_BRANCHES
AP_BANK_ACCOUNTS_ALL
AP_BANK_ACCOUNT_USES_ALL
2. How to send additional customer and address information through customer interafce 
Here is where attribute columns are used.
* Additional Customer data can be populated in 
RA_CUSTOMERS_INTERFACE_ALL.customer_attribute1 to 15
This will go into RA_CUSTOMERS.attribute1 to 15
* Additional address information can be populated in 
RA_CUSTOMERS_INTERFACE_ALL.address_attribute1 to 15
This will go into RA_ADDRESSES_ALL.attribute1 to 15
And so on.
5. What should be the batch size (number of customer records) general guidelines for optimal performance
About 10,000 records per bacth is ideal, it is suggested to keep the batch
size small.
   6. Why does customer interface error out if there is mismatch in address information?
Because the code validates against these mismatches. 
arplscin.sql and arplbcin.sql 
It validates the address being inserted or updated with respect to the tax 
location flexfield structure.  For each row being inserted or updated in 
RA_CUSTOMERS_INTERFACE, and that has not already been marked in error, 
the set_location_ccid function is called to return either an existing ccid 
for the address entered or to create a new entry in AR_LOCATION_VALUES for 
this new address.  The RA_CUSTOMERS_INTERFACE record is then updated with the 
value of the ccid returned.                                
7. How do you send records at customer level profile and address/site level profile?
For every customer record in RA_CUSTOMERS_INTERFACE_ALL, insert two records  in table RA_CUSTOMER_PROFILES_INT_ALL.    (Refer Note: 1070800.6 )
9. Does Customer Interface import salesperson data? 
No, Refer Enhancement Bug: 147495 
11. Customer Interface process can be used for updating the customer information How does the UPDATE work?
Current functionality of Customer Interface is to update all the data.  You cannot run Customer Interface to update only changed data. (Refer Bug: 879121 for the intended functinality)

12. What are some of the important fields that Customer Interface does not load?
Not all fields in RA_CUSTOMERS are being loaded by the interface. 
SIC_CODE                
GSA_INDICATOR                
FOB_POINT                
SALES_CHANNEL_CODE                
FREIGHT_TERM                
WAREHOUSE_ID                
PRICE LIST     
SHIP_PARTIAL                               
PAYMENT_TERM_ID   in RA_SITE_USES.PAYMENT_TERM_ID 
(Refer Enhancement Request Bug: 245300)
13. New TCA/Customer Model, how to load customer as PERSON or ORGANIZATION through Customer Interface?
a) Populate ra_customers_interface_all . person_flag = 'Y'
   Run Customer Interface process will load this record as PERSON
b) Populate ra_customers_interface_all . person_flag = 'N' ( or NULL ) 
   Run Customer Interface process will load this record as ORGANIZATION
  1. Tell me what is the procedure to develop an interface?           
    1. First we will get the Requirement document.
    2. We will create control file based on that plot file.
    3. Then the control files which loads the data into staging tables.
    4. Through pl/sql programs we will mapping and validate the data and then dump into the interface tables.
    5. Through the standard programs we will push the data from interface tables to Base tables.
  2. What validations u did in the customer interface?
    1. customer name : the same customer reference can’t have different customer names with in this table
      HZ_PARTIES.PARTY_NAME
    1. customer number : must be null if your r using automatic customer numbering, must exit if you are not using automatic customer numbering. This value much be unique with in HZ_PARTIES
    2. customer status : must be ‘A’ for active or ‘I’ for inactive
                             HZ_PARTIES_STATUS
    1. bank account num or bank account currency code :
                         if the bank a/c already exist do not enter a value
                         if the bank a/c does not exist  you must enter a value
    1. bank a/c name : it must exist in AP_BANK_ACCOUNTS or if it does not exist values must exist for BANK_A/C_CURRENCY_CODE
                                                                        BANK_A/C_NUM
                                                                        BANK_NAME
                                                                        BANK_BRANCH_NAME
               Note : every interface table has two error msg
        1. Error code.
        2. Error msg.

  1. How can u call a standard interface program from sql or pl/sql code?
        FND_REQUEST.SUBMIT_REQUEST (‘PO’,’EXECUTABLE NAME’,,,,PARAMETERS)
  1. API’s FOR CUSTOMER INTERFACE?
    HZ_CUST_A/C_VZPUB.UPDATE_CUST_A/C
    HZ_CUST_A/C_VZPUB.CREATE_CUST_A/C
           FND_PROFILES
           FND_APPLICATIONS
           FND_GLOBAL
           FND-FILE
           FND_CONCSUB(can submit conc program in host invironment)
  1. Tell me some API?
    FND_FILE.PUTLINE(FND_FILE.LOG)
    FND_FILE.PUTLINE(FND_FILE.OUTPUT)
    Is the Program exits, delete conc program and its executables.
    IF      FND_PROGRAM.PROGRAM_EXITS(‘EMP’,APPLICATION_NAME_IN) THEN
              FND_PROGRAM.DELETE_PROGRAM(‘EMP’,APPLICATION_NAME_IN)
              FND_PROGRAM.DELETE_EXECUTABLE(‘EMP’,APPLICATION_NAME_IN)
    END;
  1. What are profile options?
    Is the Functional and Technical behavior of Oracle Applications Package.
           EX: - I want to assign the user3 responsibility to p4 printer then
                             System Administrator àProfile àSystem
                                    (FND_PROFILE_OPTIONS)
  1. Oracle E-Business suite?
    Oracle apps + analytical components software.
                                (Oracle discover)
                               (Oracle sales analyzer)
                                (Oracle financial analyzer)
                               (Oracle marketing analyzer)
24) What is multi org?
        “Legal entity has more than one operating unit is called as multi org”
          a) Business group --- Human resources information is secured by 
                                           Business group
           b) Legal entity.    --- inter-company and fiscal/tax reporting.
                          Security à responsibility à operating unit.        
  c) Operating unit --- secures AR, OE, AP, PA and PO Information.
  d) Organizations  --- is a specialize unit of work at particular locations

25) What are the User PARAMETERS in the Reports?
              P_CONC_REQUEST_ID
              P_FLEX_VALUE
26)  FND USER EXITS:-
FND SRWINIT        sets your profile option values, multiple organizations and allows Oracle Application Object Library user exits to detect that they have been called by an Oracle Reports program. 
FND SRWEXIT       ensures that all the memory allocated for AOL user exits have been freed up properly.
FND FLEXIDVAL  are used to display flex field information like prompt, value etc
FND FLEXSQL      these user exits allow you to use flex fields in your reports
FND FORMAT_CURRENCY is used to print currency in various formats by using formula column
26) PL/SQL stored procedure parameters?
       or      what are the two parameters that are mandatory for pl/sql type concurrent program?
               Procedure/function (ERRBUF   OUT
                                                RETCODE OUT
                                               ………………….)
       ERRBUF :-    Used to write the error message to log or request file.
       RETCODE :- Populate log request file with program submission details info.
27) What is Value Set?
       --The value set is a collection (or) container of values.
       --When ever the value set associated with any report parameters. It provides list of values to the end user to accept one of the values as report parameter value.
       -- If the list of values needed to be dynamic and ever changing and define a table based values set.

27) What r the validation types?
      1) None          -------- validation is minimal.
      2) Independent ------input must exist on previously defined list of values
      3) Dependent    ------input is checked against a subset of values based on a
                                       prior value.
      3) Table             ----- input is checked against values in an application table
      4) Special          ------values set uses a flex field itself.
      5) Pair              ------ two flex fields together specify a range of valid values.
      6) Translatable independent ----- input must exist on previously defined list
                                                           of values; translated values can be used.
      7) Translatable dependent ------- input is checked against a subset of values
                                         based on a prior values; translated value can be used.
28) Form development process?
      a) open template form
      b) Save as <your form>.fmb
     c) Change the form module name as form name.
     d) Delete the default blocks, window, and canvas
      e) Create a window.
      f) Assign the window property class to window
      g) Create a canvas   (subclass info)
      h) Assign canvas property class to the canvas
      I) assign the window to the canvas and canvas to the window
       j) Create a data block       
       k) Modify the form level properties. (sub class item à Text item)
       l) Modify the app_cusom package. In the program unit.
       m) Modify the pre-form trigger (form level)
      n) Modify the module level properties ((console window, First navigation
       p) Save and compile the form.
           Place the .fmx in the server directory.
       Q) Register in the AOL
                                    APPLICATION à FORM
                                    APPLICATION à FUNCTION
                                    APPLICATION à MENU
  1. How do u customize the Reports?
    1. Identify the Short name of the standard report in which module we have to customize
         Ex: - if u want to customize in the AR module path is
                    Appl top\ar\11.5.0\reports\US\ .rdf
    1. Open the .rdf file in Report builder and change the name of the module.
    2. Open the data module and modify the query (what is client requirements) assign the columns to the attributes.
    3. Go to report wizard and select, what r the newly created columns.
    4. Then Compile it. Then u will get a .rep file in the specified module. If it is not in the specified directory then we have to put in the server directory.
    5. Then Register in the AOL   Concurrent àexecutable.
                                                        Concurrent à program.
    1. go to system administrator SecurityàResponsibilityàrequest
                 h)  Add and assign a concurrent program to a request group
30) Registering parametric report?
               Any applications will have two parameters,
                              1) from-period   2) to-period
      a) Go to object navigator and create the parameters.
                User parameters à from_no, to_no (data type char and width 30)
     b) Open the report layout and write the query(we have to reg the table in AOL)
          Select empno,ename,from g_emp where empno between :from_no and to_no
      c) Compile and put the .RDP file in the server directory.
     d) Registering in AOL.
                Concurrent à executable
                Concurrent à program   then go to “PARAMETERS”
    e) Go to Application à validation àset.  then go to “EDIT INTO”
               After entering the VALUE SETS (1) And TOKENS (2)
  1. Go to Administrations
                  Sequrity à responsibility à Request
      h) Add and assign a concurrent program to a request group
31) Tell me some report names and their table names in GL, AP, AR, and PO?
    1) ra_customer_trx_all   
                 customer_trx_id
                 trx_number          (invoice no, debit memo no, credit memo no)
                 cust_trx_type_id 
    2) ra_customer_lines_all (details of invoice)
                 cutomer_trx_id 
    3) ar_payment_schdules_all
                 check_id
                              This table stores all transactions except adjustments and miscellaneous cash receipts. Oracle Receivables updates this table when activity occurs against an invoice, debit memo, chargeback, credit memo, on account credit, or receipt.
    4) ra_cust_trx_types_all       (invoice types)
                 cust_trx_type_id 
    5) ra_batches_all
                  Batch_id
                          This table stores information about each receipt batch that you create in
Oracle Receivables. Each row includes information about a specific batch such as batch source, status, batch type, control count, and control amount
    6) ra_receivable_application_all 
    7) ra_adjustments_all
             This table stores information about your invoice adjustments. Each row includes general information about the adjustment you are making such as activity name, amount, accounting information, reason, and type of adjustment. You need one row for each adjustment you are making to an invoice.
     

8) ra_cash_receiots_all
                 Cash_receipt_id
                This table stores one record for each receipt that you enter. Oracle Receivables creates records concurrently in the AR_CASH_RECEIPT_HISTORY_ALL, AR_PAYMENT_SCHEDULES_ALL, and AR_RECEIVABLE_APPLICATIONS tables for invoice–related receipts.
                           
  1. ap_invoice_all
                     invoice_amount, base_amount, payment_status_flag(‘y’ –fully paid
                                                                                                           ‘n’—unpaid
                                                                                                            ‘p’ –partially paid)
  1. ap_invoice_payments_all
                      invoice_id,
  1. ap_invoice_distibutions_All
                     amount, base_amount, dist_code_combination_id, line_type_lookup_code
  1. ap_payment_schdules
                     payment_status_flag(“ ‘’’’’”)
  1. ap_payment_dustributions_all
  2. ap_checks_all
                     check_id,
                 AP_CHECKS_ALL stores information about payments issued to suppliers or refunds received from suppliers. You need one row for each payment you issue to a supplier or refund received from a supplier. Your Oracle Payables application uses this information to record payments you make to suppliers or refunds you receive from suppliers.
  1. ap_accounting_events_all

  1. ap_bank_accounts_all
                    AP_BANK_ACCOUNTS_ALL contains information about your bank accounts. You need one row for each bank account you define. Each bank account must be affiliated with one bank branch. When you initiate an automatic payment batch, enter a manual check, or create a Quick payment, you can select a bank account that you define in this table.
    9)  ap_bank_accounts_uses_all
               AP_BANK_ACCOUNT_USES_ALL stores information for the internal and external bank accounts you define in Oracle Payables and Oracle
Receivables applications.
                 
  1. po_vendors_all
  2. po_vendors_sites_all
  3. po_headers_all
                 po_header_id
  1. po_lines_all
                 po_line_id
  1. po_line_locations_All
  2. po_distributions_all
                 po_distribution_id, 
         
  1. Gl_code_combinations
                  GL_CODE_COMBINATIONS stores valid account combinations for each Accounting Flexfield structure within your Oracle General Ledger application. Associated with each account are certain codes and flags, including whether the account is enabled, whether detail posting or detail budgeting is allowed, and others.
  1. Gl_je_batches.
               GL_JE_BATCHES stores journal entry batches.
  1. Gl_je_headers
           GL_JE_HEADERS stores journal entries. There is a one–to–many relationship between journal entry batches and journal entries. Each row in this table includes the associated batch ID, the journal entry name and description, and other information about the journal entry. This table corresponds to the Journals window of the Enter Journals form. STATUS is ’U’ for unposted, ’P’ for posted. Other statuses indicate that an error condition was found. A complete list is below.
  1. Gl_je_lines.
             GL_JE_LINES stores the journal entry lines that you enter in the Enter Journals form. There is a one–to–many relationship between journal entries and journal entry lines. Each row in this table stores the associated journal entry header ID, the line number, the associated code combination ID, and the debits or credits associated with the journal line. STATUS is ’U’ for unposted or ’P’ for posted
  1. Gl_set of books
         GL_SETS_OF_BOOKS stores information about the sets of books you define in your Oracle General Ledger application. Each row includes the set of books name, description, functional currency, and other information. This table corresponds to the Set of Books form.
  1. Gl_periods
          GL_PERIODS stores information about the accounting periods you define using the Accounting Calendar form. Each row includes the start date and end date of the period, the period type, the fiscal year, the period number, and other information. There is a one–to–many relationship between a row in the GL_PERIOD_SETS table and rows in this table.
  1. OPEN-DEBIT MEMO REPORT?
      This report shows all the open-debit memo transactions, based on customer number and dates.
            Columns  :- type, customer_no, trx_no, amt_due, remaining.
             Parameter :- type, customer, from_date, to_date.
  1. GENERATING POSITIVE PAY FILE FOR BANK REPORT?
    Basically this report generates a flat file of all the payments in order to send in to the bank.
  1. UPDATE POSITIVEPAY CHECKS REPORT?
    This report which updates the data into the (AP) account payables system from the plot file, the file which is sent by bank
  1. UPDATE POSITIVEPAY OUT STANDING CHECKS?
    This report which shows the out standing checks  
     
  1. CUSTOMER PAYMENT DETAILS REPORT?
    Which shows each customer original amount, amount pay and due amount based on transaction type (books, pens) 
Transaction types in AR
Credit memo transaction types
Invoice, debit memo, and charge back transaction types
Commitment transaction types
Q) HOW DO YOU RECTIFY THE ERRORS IN INTERFACE TABLES?
Depending on the naming convention used, errors appear in either alphabetical order or by error code number.
31) How do u identity its name of report?
                 System administrator à concurrent à program à define
                 System administrator à concurrent à program àexecutable 
32) Who information’s?
        1) Created by
        2) Creation date
        3) Last _updated by
        4) last_update_date
        5) last_update_value
33) FLEX FIELDS?
            Used to capture the additional business information.

                    DFF                    KFF
    AdditionalUnique Info, Mandatory
    Captured in attribute prefixed columnsSegment prefixed
    Not reported on standard reportsIs reported on standard reports
    To provide expansion space on your form With the help of []. [] Represents descriptive Flex field.
    FLEX FILED : DESCRIPTIVE : REGIGSTER
    Used for entering and displaying key information For example Oracle General uses a key Flex field called Accounting Flex field to
    uniquely identifies a general account.
    FLEX FILED : KEY : REGIGSTER

Oracle Applications KEY FLEX FIELDS
1) GL :- ACCOUNTING
2) AR :- SALES TAX LOCATION, TERRITORY,
3) AP :- BANK DETAILS, COST ALLOCATION, PEOPLE GROUP
Oracle Applications DESCRIPTIVE FLEX FIELDS (Partial)
  1. GL :-  daily rates
  2. AR :- credit history, information
       3) PA :- bank branch, payment terms, site address,  
      
34) What are the requests groups?
           a) Single request: - this allows you to submit an individual request.
           b) Request set    : - this allows you to submit a pre-defined set of requests.
35) Sys Admin Module?
    a) Define Custom Users, b) Define Login Users, c) Register oracle DB users,
d) Define Concurrent Programs, e) Register Concurrent Executables, f) Setting Profile Option Values, g) Define Request Types.
36) AOL?
   a) Registering tables. b) Registering views c) Registering db sequences
   d) Registering profile options e) Registering lookups and lookup codes
   f) Registering forms g) Registering Form and Non-Form functions i) registering 
       Menus and sub-menus. j) Registering DFF and KFF. k) Libraries 
37) What r the type Models in the system parameters of the report?
      1) Bit map       2) Character mode
38) .What is SRW Package? (Sql Report Writer)  
The Report builder Built in package know as SRW Package This package extends reports ,Control report execution, output message at runtime, Initialize layout fields, Perform  DDL statements  used to create or Drop  temporary table,  Call User Exist, to format width of the columns, to page break the column, to set the colors
Ex: SRW.DO_SQL, It’s like DDL command, we can create table, views , etc.,
   SRW.SET_FIELD_NUM
      SRW. SET_FILED_CHAR
      SRW. SET FILED _DATE
37) Difference between Bind and Lexical parameters?
BIND VARIABLE :
-- are used to replace a single value in sql, pl/sql
-- bind variable may be used to replace expressions in select, where, group, order
    by, having, connect by, start with cause of queries.
-- bind reference may not be referenced in FROM clause (or) in place of
    reserved words or clauses.
LEXICAL REFERENCE:
-- you can use lexical reference to replace the clauses appearing AFTER select,
    from, group by, having, connect by, start with.
-- you can’t make lexical reference in a pl/sql statmetns.
38) Matrix Report: Simple, Group above, Nested  
Simple Matrix Report : 4 groups
  1.Cross Product Group
            2. Row and Column Group
            3. Cell Group
            4. Cell column is the source of a cross product summary that
                           becomes the cell content.
Frames: 1.Repeating frame for rows(down direction)
           2.Repeating frame for columns(Across )
          3.Matrix object the intersection of the two repeating frames

39) what is Flex mode and Confine mode?    
Confine mode
      On:  child objects cannot be moved outside their enclosing parent objects.
      Off:  child objects can be moved outside their enclosing parent objects.
Flex mode:
      On:  parent borders "stretch" when child objects are moved against them.
      Off:  parent borders remain fixed when child objects are moved against
                    them.



40) What is Place holder Columns?      
   A placeholder is a column is an empty container at design time. The placeholder can hold a value at run time has been calculated and placed in to It by pl/sql code from anther object.
You can set the value of a placeholder column is  in  a Before Report trigger.
   Store a Temporary value for future reference. EX.  Store the current max salary as records are retrieved.
23) What is Formula Column?      
A formula column performs a user-defined computation on another column(s) data, including placeholder columns.
24) What is Summary columns?      
A summary column performs a computation on another column's data.  Using the Report Wizard or Data Wizard, you can create the following summaries:  sum, average, count, minimum, maximum, % total.  You can also create a summary column manually in the Data Model view, and use the Property Palette to create the following additional summaries:  first, last, standard deviation, variance.
50) What is cursor?
      A Cursor is a pointer, which works on active set, I.e. which points to only one row at a time in the context area’s ACTIVE SET. A cursor is a construct of pl/sql, used to process multiple rows using a pl/sql block.
28) Types of cursors?
       1) Implicit: declared for all DML and pl/sql statements.
                           By default it selects one row only.
       2) Explicit:  Declared and named by the programmer.
                           Use explicit cursor to individually process each row returned by a 
                                         Multiple statements, is called ACTIVE SET.
                            Allows the programmer to manually control explicit cursor in the
                                         Pl/sql block
  1. declare: create a named sql area
  2. Open: identify the active set.
  3. Fetch: load the current row in to variables.
  4. Close: release the active set.
                          
      CURSOR ATTRIBUTES
  1. %is open: evaluates to true if the cursor is open.
  2. %not found: evaluates to true if the most recent fetch does not return a row
  3. %found: evaluates to true if the most recent fetch returns a row.
  4. %row count: evaluates to the total number of rows returned to far.

Example for cursor:
1) Declare
            Vno emp.empno%type;
            Vname emp.ename %type;
            Cursor emp_cursor is
                   Select empno,ename
                            From emp;
          Begin
            Open cursor;
            For I in 1..10 loop
              Fetch emp_cursor into vno,vname;
              Dbms_output.putline(to_char(vno) ||’ ‘||vname);
            End if;
         E nd;
 
2)         Begin
              Open emp_cursor;
             Loop
                  Fetch when emp_cursor % rowcount >10 or
                                                                      Emp_curor % not found;
                 Bdms_output_put_line(to_char(vno)||’ ‘|| vname);
             End loop;
           Close emp_cursor;
           End;
       CURSOR FOR LOOP
  1. cursor for loop is a short cut to process explicit cursors
  2. it has higher performance
  3. cursor for loop requires only the declaration of the cursor, remaining things like opening, fetching and close are automatically take by the cursor for loop
   
Example:
  1)     Declare
            Cursor emp_cursor is
                   Select empno,ename
                            From emp;
          Begin
            For emp_record in emp_cursor loop
              Dbms_output.putline(emp_record.empno);
               Dbms_output.putline(emp_record.ename) 
           End loop       
         End;
 


Can we create a cursor without declaring it?
    Yes – by using cursor for loop using subqueries. 
    BEGIN
      FOR emp_record IN ( SELECT empno, ename
                                FROM   emp) LOOP
             -- implicit open and implicit fetch occur
        IF emp_record.empno = 7839 THEN
          ...
      END LOOP; -- implicit close occurs
    END; 
  a) for update clause:
       1) use explicit locking to deny access for the duration of a transaction
       2) lock the rows before update or delete
Ex : select …….
              From…….
                    For update[ of column ref] [no_wait]

  b) where current of clause?
        1) use cursor to update or delete the current row
                     Where current of < column ref>
29) Attribute data types?
       1) %type 2) %row type.
30) Exception Handilings?
       Is a mechanism provided by pl/sql to detect runtime errors and process them with out halting the program abnormally
    1. pre-defined
    2. user-defined.
PRE-DEFINED:
  1. cursor_already_open--------attempted to open an already open cursor.
  2. Dup_val_on_index   --------attempted to insert a duplicate values.
  3. Invalid_cursor          -------- illegal cursor operation occurred.
  4. Invalid_number        -------- conversion of character string to number fails.
  5. Login_denied           ---------loging on to oracle with an invalid user name
                                                       and password.
    6) program_error           -------- pl/sql has an internal problem.
    7) storage_error             -------- pl/sql ran out of memory or memory is
                                                       corrupted.
    8) to_many_row            ---------single row select returned more than one row.
    9) value_error                -------- arithmetic,conversion,truncation or size
                                                       constraint error occurred.
    10) zero_devided           -------- attempted to divided by zero.
     
USER-DEFINED:
       Declare     : name the exception
       Raise         : explicitly raise the exception by using the raise statements
       Reference: exception handing section.
The Raise_Application_Error_Procedure:
  • You can use this procedure to issue user-defined error messages from stored sub programs.
  • You can report errors to your applications and avoid returning unhandled exceptions.
          Raise_Application_Error(error_number,message[,{true/false}]
                     Error number è between -20000 to -20999 
pragma exception_init?
       It tells the compiler to associate an exception with an oracle error. To get an error message of a specific oracle error.
       Ex: pragma exception_init(exception name, oracle error number)
Example for Exceptions?
      1) Check the record is exist or not?
             Declare
                     E  emp% rowtype
             Begin 
                     e.empno := &empno;
                     select * into e from emp where empno =e.empno;
                     Dbms_output.putline(‘empno’ || e.empno);
            Exception
                    When no_data_found then
                    Dbms_output.putline(e.empno ||’doest exist’);
            End;
     2) User defined exceptions?
                   Define p_dept_desc =’gvreddy’
                   Define p_dept_number =1236
                 Declare
                           E_invalid_dept exception;
                      Begin
                          Update departments
                          Set dept_name=’&p_dept_desc’
                          Where dept_id =’&p_dept_number’;
                        If sql% not found then
                            Raise e_invalid_departments;
                        End if;
                        Commit;
                    Exception
                           When e_invalid_departments then
                           Dbms_output.putline(‘no such dept’);
                    End;                 
       
52) what is REF Cursor?
        To execute a multi-row query, oracle opens an unnamed work area that stores processing information, to access the information, an explicit, which names the work area or, a cursor variable, which points to the work area.
where as a cursor always refers to the same query work area,  a cursor variable can refer to a different work areas, cursor variable area like ‘c’ or ‘pascal’ pointers, which hold the memory location(address) of some object instead of the object itself.
So, declaring a cursor variable creates a pointers, not an object.
32) Can u define exceptions twice in same block?
       No
33) Can you have two functions with the same name in a pl/sql block?
       Yes
34) Can you have two stored functions with in the same name?
       Yes
35) Can function be overload?
       Yes
  1. What is the maximum number of statements that can be specified in a trigger statement?
    One.
32) Stored procedure?
       Stored procedure is a sequence of statements that perform specific function.
53) What is procedure?
    ----    is a named pl/sql block to perform a specific task.
    ----    A procedure may have DML statements.
  ----    It may or may not return a value.
    ----    Procedure can return more than one value.
Example for procedure

  1. To accept the year as a parameter and list emp belong to the year?
                  Create or replace
                          Procedure empy(y number) is
                      Cursor emp_cursor is
                              Select * from emp where to_char(hiredate,’yyyy’)=’y’;
                     Emp_record  emp%rowtype;
                    Begin
                      For emp_record in emp_cursor loop
                         Print (emp_record.empno);
                        Print (emp_record.ename);
                        Print (emp_record.sal);
                     End loop;
                    End;
Output :
              var empx number;
               Begin
                    :empx := ‘1234’;
               End;
       Exec empy(:empx);
        Print empy;
54) What is function?
   ---- is a named pl/sql block to perform a specific task, is mainly used for calculation purpose.
   ---- A function is called as part of an exception.
   ---- Every function should return a value
Example for function
       Create or replace
                          Function get_sal(p_id in emp.emp_no% type)
                                          Return number
                          Is
                     v_sal emp.sal%type :=0;
                    Begin
                           Select salary into v_salary
                                                                From emp
                                                                        Where emp_no = p_id;
                            Return v_salary
                            End get_sal;                    
                    End;
Output :
              var g_sal number;
               Exec :g_sal := get_sal(99);
               Print g_salary;
9.Can functions be overloaded ?
Yes.
10.Can 2 functions have same name & input parameters but differ only by return datatype
No.
55) What is the package? 
    ---- Group logically related pl/sql types, items and subprograms.
      1. package specification
      2. package body
Advantages of a package:
    • Modularity
    • Easier Application Design
    • Information Hiding
    • Overloading

You cannot overload:
      •Two subprograms if their formal parameters differ only in name or parameter mode. (datatype and their total number is same).
      •Two subprograms if their formal parameters differ only in datatype and the different datatypes are in the same family (number and decimal belong to the same family)
      •Two subprograms if their formal parameters differ only in subtype and the different subtypes are based on types in the same family (VARCHAR and STRING are subtypes of VARCHAR2)
•Two functions that differ only in return type, even if the types are in different families.
56) What is FORWARD DECLARATION in Packages?
    PL/SQL allows for a special subprogram declaration called a forward declaration. It consists of the subprogram specification in the package body terminated by a semicolon. You can use forward declarations to do the following:
    • Define subprograms in logical or alphabetical order.
    • Define mutually recursive subprograms.(both calling each other).
    • Group subprograms in a package 
    Example of forward Declaration: 
      CREATE OR REPLACE PACKAGE BODY forward_pack
      IS
      PROCEDURE calc_rating(. . .);    -- forward declaration 
      PROCEDURE award_bonus(. . .)
      IS     -- subprograms defined
      BEGIN                     -- in alphabetical order
        calc_rating(. . .);      
      . . .
      END;
      PROCEDURE calc_rating(. . .)
      IS
    BEGIN
      . . .
    END;
    END forward_pack;
     
56) What are triggers?
---- triggers are similar to procedures, in that they are the named pl/sql blocks with declarative, executable and exception-handling sections, how ever a procedure is executed explicitly from another block via a procedure call, which can also pass arguments.
---- A trigger is executed implicitly when ever a particular event task places. And is nothing but a event.
---- The triggering event is a DML (insert, update, delete) operations on a data base table
----- fires whenever a data event(such as DML) or system event(such as login or shutdown) occurs on a schema or database
Trigger timing :  1) before
                            2) after
                            3) instead of ( this is used for views)
Triggering events : 1) insert
                                2)update
                                3) delete
Trigger type :  1) statement level
                          2) row level.
Firing sequence of database triggers
    1. before statement trigger
    2. before row trigger
    3. after row trigger
    4. after statement trigger
Ex:
1)            Create or replace trigger secure_emp
                     Before
                     Insert  on emp
          Begin
           If (to_char(sysdate,’dy’) in(‘sat’,’sun’)) or
                 To_char(sysdate,’hh24:mi’)
                 Not between ’08:00’ and ’18:00’)
Then raise_application_error(-20500,’u can insert in the office timings’)
End if;
End;
Ex :-  2)  write a program to all transitions with name smith?
                Create or replace
                Trigger trigger_name
                Before insert or update or delete
                On emp
           For each row
                          When (old.ename =’smith’ or
                                      New.ename =’smith’)
               Begin
                           Raise_application_error(-20003,’smith’);
               End;
57) Difference between triggers and procedures?

      Defined with create triggerDefined with create procedure
      The data dictionary contains source code in the user_triggers.Data dictionary contains source code in user_source
      Implicitly invokedExplicitly invoked
      Commit, save point and rollback are not allowed(TCL)Those are allowed

58) LOCKS?
           -- Is to reduce concurrency
      1) share lock
                ---it allows the other users for only reading not to insert or update or delete.
      2) exclusive lock
                 --- only one user can have the privileges of insert or update and delete of particular object
                --- others can only read.
     3) update lock
               ----multiple user can read, update delete
Lock levels :
                       1) table level  2) table space 3) data base level.
                   
58) What is template?
   a) The TEMPLATE form is the required starting point for all development of new
           Forms.
   b) The TEMPLATE form includes platform–independent attachments of several
           Libraries.
      APPSCORE :- It contains package and procedures that are required of all forms to support  the MENUS ,TOOLBARS.
      APPSDAYPK :- It contains packages that control the oracle applications CALENDER FEATURES.
      FNDSQF  :- it contains packages and procedures for MESSAGE DICTONARY, FLEX FIELDS, PROFILES AND CONCURRENT PROCESSING.
     CUSTOM :- it allows extension of oracle applications forms with out modification of oracle application code, you can use the custom library for customization such as zoom    ( such as moving to another form and querying up specific records)
59)     What are ad-hoc reports?
Ans.:  Ad-hoc Report is made to meet one-time reporting needs. Concerned with or formed for a  
           particular purpose. For example, ad hoc tax codes or an ad hoc database query
60) What is responsibility?
       Is collection of menus, request security groups and data groups
Menus: collection of forms is nothing but menus
  Request security groups: collection of programs.
  Data groups: is a group of modules to be made accessible by the user through
                         Responsibility
           System adminàsecurityàdefine
                                      Securityàuseràdefine
61) What are different execution methods of executabls?
FlexRpt             The execution file is wrnitten using the FlexReport API.
FlexSql             The execution file is written using the FlexSql API.
Host             The execution file is a host script.
Oracle Reports            The execution file is an Oracle Reports file.
PL/SQL Stored Procedure       The execution file is a stored procedure.
SQL*Loader            The execution file is a SQL script.
SQL*Plus            The execution file is a SQL*Plus script.
SQL*Report            The execution file is a SQL*Report script.
Spawned            The execution file is a C or Pro*C program.
          Immediate                               The execution file is a program written to run as a subroutine of the concurrent manager. We recommend against defining new immediate concurrent programs, and suggest you use either a PL/SQL Stored Procedure or a Spawned C Program instead. 
Composite Datatypes :
    –   PL/SQL TABLES
    –   PL/SQL RECORDS
  • Nested TABLE
  • VARRAY

What is the sequence of functions – group by,having,orderby in a select statements ?
    Select…..
    Group by…
    Having…
    Orderby.. 
Difference between User and Super User?
    User : login user or front end user
    Super user : it has full access of particular module