Customer related tables in Oracle R12

HZ_PARTIES :
This table holds important information related to party like party name, party number and party type. Party type can be organization , person or relationship between organization and person. The primary key of the table is PARTY_ID.

HZ_CUST_ACCOUNTS

This table hold information related to customer account like account_number. The primary key in this table is cust_account_id. We can join this table with HZ_PARTIES using partying.

HZ_LOCATIONS:
This table holds the address information related to party . Important fields in this table are address, address2 , address3 , city, state, county , country, address_style. Primary key in this table location_id .

HZ_PARTY_SITES
HZ_PARTY_SITE_USES_ALL
HZ_CUST_ACCT_SITES_ALL
Hz_CUST_SITE_USES_ALL
HZ_RELATIONSHIPS
HZ_CONTACT_POINTS
HZ_CUST_ACCOUNT_ROLES
HZ_CUSTOMER_PROFILES
HZ_CUST_PROFILE_CLASSES

Query:

Select hp.party_name,hp.party_id,hca.account_number
,hca.cust_account_id
,hl.address1
, hl.address2
, hl.city
, hl.country
,hl.state
,hps.party_site_id
From
HZ_PARTIES hp
, HZ_CUST_ACCOUNTS hca
, HZ_LOCATIONS hl
, HZ_PARTY_SITES hps
Where 1=1
and hp.party_id=hca.party_id
and hp.party_id=hps.party_id
and hl.location_id=hps.location_id

Order management tables in Oracle R12

Order Management:

OE_ORDER_HEADERS_ALL
OE_ORDER_LINES_ALL
OE_ORDER_HOLDS_ALL
OE_PRICE_ADJUSTMENTS
OE_TRANSACTION_TYPES_ALL
OE_TRANSACTION_TYPES_TL
WSH_DELIVERY_DETAILS
WSH_DELIVERY_ASSIGNMENTS
OE_ORDER_SOURCES
WSH_NEW_DELIVERIES
Wsh_carriers


PRICING TABLES:

QP_LIST_HEADERS_B
QP_LIST_LINES
QP_QUALIFIERS

Interview questions on Oracle Apps PL/SQL

1)Difference between truncate , delete and drop?

2)Types of cursors?
Implicit cursors
Explicit cursors

3)what is user defined and pre defined exceptions?

4)difference between view and materialized view?

5)what are the cursor attributes?

%NOTFOUND
%FOUND
%ROWCOUNT
%ISOPEN

6)explain few predefined exceptions in Oracle?

TOO_MANY_ROWS
NO_DATA_FOUND
INVALID_CURSOR
ZERO_DIVIDE

7)what is the purpose of bulk collect?

8)explain difference between case and decode function in Oracle

9) explain group by and having in Oracle ?

10)difference between union and union all?

11)What are aggregate functions and character functions in Oracle ?

12) what is the purpose of explain plan in sql developer?
Explain plan is used during performance tuning of sql Query. Explain plan explain the cost of the query, any full table scan.

13)explain different constraints in Oracle ?
NOT NULL
UNIQUE
PRIMARY KEY
FORIEGN KEY
CHECK

14)what is the difference between fast refresh and complete refresh in materialized views

15)how to refresh a materialized view in Oracle.

16) what is the purpose of trigger and different types of triggers in Oracle?

17)what is mutating error ?

18)what is pragma autonomous transaction ?

19)Difference between pre Query and post Query in Oracle Forms?

20)Difference between form personalisation and custom pll?

21)Difference between request group and request set in Oracle apps?

22)Have you worked on developing concurrent program? Explain complete process?

24)How to generate trace file for concurrent program?

25)how to call concurrent program from plsql block?

26)what are mandatory parameters for package which called from concurrent program?

27)Explain xml bursting concept in Oracle apps?

28)what is the purpose of utl_file?

29)what is the link between concurrent program and xml publisher template and data definition?

30)which will be fired first whether form personalisation or custom pll?

First form personalisation will be fired.

31)explain KFF and DFF in Oracle apps ?

32)Explain different triggers in Oracle RDF Reports?

33)How to display messages in rdf reports?

SRW.MESSAGE()

34)What are the profile options we need to check to enable Oaf personalisation?

35)What is difference between form or function level in form personalisation?

36)what are different types of valuesets in Oracle apps?

37)what are the tables which store data definition and data templates information in Oracle apps?

Customer Conversion in Oracle Apps

validation:

1)Party Name already exist or not. If party already exist do no load party, just load only address site information.
2)Address validation based on address style setup like address 1 , state , country.
3)Party name should not be null.
4)Account number should not be null.
5)Tax code validation.
6)VAT REGISTRATION number validation.
7)Sales rep validation if field data exist in data file.
8)Price list validation
9)Customer Profile class validation.
10)Address should be either billto or shipto or can be both.

Query to get Source System Id related to Employee in Fusion HCM Cloud

Please change the source system owner for which you want to check data before running below queries.

Person:

select source_system_id
from fusion.HRC_INTEGRATION_KEY_MAP h
,PER_PERSON_NAMES_F_V P
where 1=1
and h.object_name='Person'
and h.source_system_owner='WORKDAY'
and h.surrogate_id=p."PERSON_ID";

PersonName:

select source_system_id
from fusion.HRC_INTEGRATION_KEY_MAP H
,PER_PERSON_NAMES_F_V P
WHERE 1=1
and h.object_name ='PersonName'
and h.surrogate_id=p.person_name_id
and h.SOURCE_SYSTEM_OWNER ='WORKDAY';


Assignment:

select source_system_id
from fusion.HRC_INTEGRATION_KEY_MAP H
,PER_ALL_ASSIGNMENTS_M PA
where 1=1
and H.object_name='Assignment'
and H.source_system_owner='WORKDAY'
and H.surrogate_id=PA."ASSIGNMENT_ID"

Assignment Supervisor:

select source_system_id
from fusion.HRC_INTEGRATION_KEY_MAP H
,PER_ASSIGNMENT_SUPERVISORS_F_V PA
,PER_PERSON_NAMES_F_V PER_PERSON_NAMES_F_V
,PER_ALL_ASSIGNMENTS_M PER_ALL_ASSIGNMENTS_M
where 1=1
and H.object_name='AssignmentSupervisor'
and H.source_system_owner='WORKDAY'
and H.surrogate_id=pa.assignment_supervisor_id
and pa.person_id="PER_PERSON_NAMES_F_V"."PERSON_ID"
and pa.assignment_id="PER_ALL_ASSIGNMENTS_M"."ASSIGNMENT_ID"

Work Term Assignment

select source_system_id
from fusion.HRC_INTEGRATION_KEY_MAP H
,PER_ALL_ASSIGNMENTS_M PER_ALL_ASSIGNMENTS_M
where 1=1
and H.object_name='Assignment'
and H.source_system_owner='WORKDAY'
and H.surrogate_id="PER_ALL_ASSIGNMENTS_M".work_terms_assignment_id;

Address:

select source_system_id
from fusion.HRC_INTEGRATION_KEY_MAP H
,PER_PERSON_ADDRESSES_V PER_PERSON_ADDRESSES_V
WHERE 1=1
and h.object_name = 'Address'
and h.surrogate_id=PER_PERSON_ADDRESSES_V.address_id
and h.SOURCE_SYSTEM_OWNER ='WORKDAY';

Email Address:

select H.source_system_id
from fusion.HRC_INTEGRATION_KEY_MAP H
,PER_EMAIL_ADDRESSES_V PER_EMAIL_ADDRESSES_V
,PER_PERSON_NAMES_F_V PER_PERSON_NAMES_F_V
WHERE 1=1
and h.object_name = 'EmailAddress'
and h.SOURCE_SYSTEM_OWNER ='WORKDAY'
and h.surrogate_id=PER_EMAIL_ADDRESSES_V.email_address_id
and PER_EMAIL_ADDRESSES_V.PERSON_ID="PER_PERSON_NAMES_F_V"."PERSON_ID"
and TRUNC(SYSDATE) between DATE_FROM and NVL(DATE_TO,SYSDATE+1)