Technical Interview Questions – Oracle Financials Consultant


Technical Interview Questions – Oracle Financials Consultant

Structured from the following three dimensions – Technology, People and Processes

Technology

General Ledger

Accounts Payable

Accounts Receivable


Fixed Assets

Cash Management

All Modules


People



Process


Oracle XML Publisher


                                           Oracle XML Publisher
why we are going for xml publisher instead we are having oracle reports because of these advantages
1) reduce complexity
2) reduced cost
3) development productivity
4) rich formatted reports
normally we go for layout in reports 6i but now we go for xml publisher and remaining is the same procedure
steps to develop a xml publisher report and register it as a concurrent program
1) develop the report as .rdf file as per the client requirement but no need of layout
go to data model and write the query
2) save the .rdf file
3) open the ms word application and design the layout enter the labels(boiler plate text )like
https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg4YlwDG0FJmLMBHkVkSaeB6eLDNKqWnd9joddEmtwtpSlyaNpV8PeOA3uPCrxaXMZ5DPQovr-Uu_fh62S9DDvbPc4LX8A7UjUZe4pPMJDm6WxVZsmZfR65jm2P9v9udRv3Aj20xe_69j8/s320/1.JPG
4)take the text formfield into the begin space , double click on it and enter the name,select the button called "add help text" and enter the following code
note: g_vendor_id nothing but report builder guery group name
5) Take the text form field into the place,double click there and enter the name,select the button called "add help text" and enter the following code
< ? vendor_id ? >------to dispaly vendor_id
Repeat the same process for the remaining columns
6) Take the text formfield into the place and enter the following code for end
< ? end for-each ? > ---- to end the repeating frame
7) Save the word fiel with extension as .rtf (rich text format)
8) Now we have to register both the .rdf and .rtf files
9) Register the rdf file as a concurrent program by using standard process only change is the concurrent program format is XML
10) Copy the concurrent program name
11) Now go to xml publisher administrator
12) Select the data definition form from the xml publisher responsibility
13) Select the button called create data definition and enter the following fields
Name: any name
Application: oracle purchasing
Code: concurrent program short name
Start-date: today date
14) Select apply button and remember the data definition name
15) select the template tab and select create template button enter the following
Name: any name
Code: concurrent program short name
Data definition: data definition name what ever you have created
Start date: today date
Type: rtf
File: browse and select the .rtf that you developed
Language: English
Territory: united States
16) Select the apply button
17) Go to responsibility and submit the concurrent program from SRS window, when we select the program in SRS window it will automatically select the template we can find that in the field called layout
18) Submit button --> find --> view report

Open Interfaces in APPS EBS - Best Practices

Open Interfaces in APPS EBS - Best Practices

This article discusses the best practices & strategies for developing Interface Programs in Oracle Financials / APPS/ EBS.
This article assumes that you have already read the article on data migration, if not the click here.

What is common between Data Migration and interfaces?
Both essentially use API or open interface tables. See Data Migration Article

What is the difference between data migration and conversion?
These are essentially the same.

What is the difference between data migration and interfaces?
Data Migration is a one-time activity however interfaces are ongoing processes that run regularly. Hence error handling must be well thought when designing interfaces.

Best practices for interfaces?
1. Usually an interface must have well defined error reporting mechanism.
2. Data errors must be fixed at the source once the transaction gets rejected.
3. If your support team is often modifying transactions in interface tables using sql, then your interface design is flawed.
4. If you log support issues with any interface on a relatively regular basis, then its time to re-visit your design.
5. Interfaces must be designed in a manner such that, once the original errors are fixed, those transactions must get re-processed during the next run (or on demand)
6. If possible an interface mechanism/infrastructure must be in place, so that all interfaces are written in similar manner.
7. There must not be any hard coding for mapping from source system data to EBS. You can either use oracle's lookup screen to define mappings, or use a custom mapping screen.
8. In some cases you will have a generic screen which facilitates one-to-one, one-to-many, many-to-one, many-to-many mappings.
9. There must exist a simple mechanism to identify the duplicate processing of any transaction must exist. One way this can either be achieved is by making your source system specify a unique identifier for each record.
10. Do not store references to rowid in any part of your interfaces.
11. Think upfront, design and write your interface in such a manner that once it goes to production, you never receive an email to fix any stuck records. If at all you do receive data fixing requests, then ensure that interface program is changed in a manner it gets handled without programmers intervention in future.
12. Have proper debugging. You could add a parameter for debug flag, so that debug messages aren’t generated unnecessarily. Surely, don't forget to use FND Logging, which is delivered out of the box by Oracle.
13. Make the concurrent program end with warning in case of errors. This can be done by passing retcode=1. Use the out-of-the-box concurrent program notification facility can be used to inform end users of the errors encountered during processing.
By doing so, you can make the monitoring automatic, i.e. when submitting/scheduling interface concurrent program, you can attach a workflow role.


What tools can I use for point to point interfaces?
Point to Point interface methodology is not ideal for a large Organization. However to keep the costs low, when number of interfaces are very low, then Point to Point Interface approach could be justified. However please note that Organizations do grow in size , specially after implementing Oracle ERP. Hence Point 2 Point Interfaces can not be a long term strategy.

Please find p2p interface approach listed:-
1. File tables..
--You can map the structure of an ASCII file to table structure.
2. XML Gateway
--You can use XML Gateway and XML Message designer if you have too many interfaces having XML source. Although keep in mind that there may not be any future for XML Gateway in Fusion applications.
3. Sql*loader
--Avoid for interfaces as error reporting is not user friendly. Use this for data migration as SQL*Loader is very efficient.
4. DB link for intra company different Oracle systems
--DBA's often raise questions regarding security of database links. However security concerns can be minimized by making them non-public and make them connect to not specific schemas with minimum privileges.
5. Java concurrent program with jdbc
If your source system is in a database like SQL*Server, or DB2 or any other database which supports JDBC, this approach could be ideal.
The source system can create a specialized schema and expose the necessary views or tables/synonyms, such that you can pull the data from that system, load it into your system using API. All this can happen seamlessly, all within one program.
6. Java concurrent program with FTP, csv parser
All the above steps can be done within one single program.


Usual bad practices:-
1. Each developer writing their own mapping tables and screens.
2. Hard coding in interface
3. No thoughtful process for Error Reporting and Error Correction(deviation from fix at source philosophy)
4. Calling SQL*Plus sessions from Host programs, without due considerations for APPS Password Security
5. No debugging
6. Each interface program using its own File Dequeue mechanism.

Long term interface strategy using Oracle EBS Delivered tools
Oracle Warehouse Builder [Preferably 10g Version]
This strategy means that you move away from point to point interface design. I think a simple tool like Warehouse builder can be used as a simple single point interface control. Some powerful features of Warehouse builder means that you can do mappings, transformation of data structures without writing your own code. Warehouse Builder appears to be a part of Fusion strategy, hence it is a good investment. Warehouse builder can be used to pull the data from various sources using ODBC, JDBC, DB Links etc. Hence you can leverage a Warehouse Builder interface design run interface end-to-end that includes submission of the Interface API.
Limitation:- This is a batch mechanism, and not messaging based.

XML Gateway
If you want an Oracle delivered tool to implement interfaces using messaging, you can use XML Gateway with Message Maps. But investment in this tool may be lost when you move to BPEL based architecture.

Web Services
Wait for Fusion that integrates BPEL, unless you wish to manage Web Services in a standalone manner.

Joins Between Oracle Apps Tables Like General Ledger(GL) , Account Payable(AP), Account Receivables(AR), Purchasing Orader(PO) ETC

Joins:
GL   AND   AP
GL_CODE_COMBINATIONS                         AP_INVOICES_ALL
code_combination_id                          =              acct_pay_code_combination_id
GL_CODE_COMBINATIONS                         AP_INVOICES_DISTRIBUTIONS_ALL
code_combination_id                           =             dist_code_combination_id
GL_SETS_OF_BOOKS                                      AP_INVOICES_ALL
set_of_books_id                                   =             set_of_books_id
GL   AND AR
GL_CODE_COMBINATIONS                        RA_CUST_TRX_LINE__GL_DIST_ALL
code_combination_id                            =             code_combination_id
GL   AND INV
GL_CODE_COMBINATIONS                        MTL_SYSTEM_ITEMS_B
code_combination_id                              =          cost_of_sales_account
GL   AND PO
GL_CODE_COMBINATIONS                        PO_DISTRIBUTIONS_ALL
code_combination_id                             =           code_combination_id
PO AND AP
PO_DISTRIBUTIONS_ALL                              AP_INVOICE_DISTRIBUTIONS_ALL
Po_distribution_id                                  =             po_distribution_id
PO_VENDORS                                                   AP_INVOICES_ALL
vendor_id                                              =              vendor_id
PO AND SHIPMENTS
PO_HEADERS_ALL                                           RCV_TRANSACTIONS
Po_header_id                                       =              po_header_id
PO_DISTRIBUTIONS_ALL                              RCV_TRANSACTIONS
Po_distribution_id                                 =             po_distribution_id
SHIPMENTS AND INVOICE
RCV_TRANSACTIONS                                   AP_INVOICE_DISTRIBUTIONS_ALL
RCV_TRANSACTION_ID                  =          RCV_TRANSACTION_ID
PO AND  INV
PO_REQUISITION_LINES_ALL                  MTL_SYSTEM_ITEMS_B
item_id                                     =           inventory_item_id
org_id                                                 =           organization_id
PO AND HRMS
PO_HEADERS_ALL                                        HR_EMPLOYEES
Agent_id                                             =             employee_id
PO AND REQUISITION
PO_DISTRIBUTIONS_ALL                            PO_REQ_DISTRIBUTIONS_ALL
req_distribution_id                               =             distribution_id
SHIPMENTS AND INV
RCV_TRANSACTIONS                                  MTL_SYSTEM_ITEMS_B
Organization_id                                   =             organization_id
INV AND HRMS
MTL_SYSTEM_ITEMS_B                                HR_EMPLOYEES
buyer_id                                              =             employee_id
OM  AND  AR
OE_ORDER_HEADERS_ALL                          RA_CUSTOMER_TRX_LINES_ALL
TO_CHAR( Order_number)              =               interface_line_attribute1
OE_ORDER_LINES_ALL                                RA_CUSTOMER_TRX_LINES_ALL
TO_CHAR(Line_id)                         =                 interface_line_attribute6
OE_ORDER_LINES_ALL                                 RA_CUSTOMER_TRX_LINES_ALL
reference_customer_trx_line_id          =                customer_trx_line_id
OM AND SHIPPING
OE_ORDER_HEADERS_ALL                             WSH_DELIVARY_DETAILS
HEADER_ID                                        =             SOURCE_HEADER_ID
OE_ORDER_HEADERS_ALL                            WSH_DELIVARY_DETAILS
LINE_ID                                             =              SOURCE_LINE_ID
AP AND AR (BANKS)
AR_CASH_RECEIPTS_ALL                               AP_BANK_ACCOUNTS
REMITTANCE_BANK_ACCOUNT_ID   =                ABA.BANK_ACCOUNT_ID
AP AND AR
HZ_PARTIES                                                   AP_INVOICES_ALL
PARTY_ID                                        =               PARTY_ID
OM AND CRM
OE_ORDER_LINES_ALL                              CSI_ITEM_INSTANCES(Install Base)
LINE_ID                                       =               LAST_OE_ORDER_LINE_ID
Po_Requisition_Headers_All 
Column Names                   Table Name                                  Column Name
REQUISITION_HEADER_IDPO_REQUISITION_LINES_ALL    REQUISITION_HEADER_ID
TYPE_LOOKUP_CODE        PO_DOCUMENT_TYPES             DOCUMENT_SUBTYPE
PREPARER_ID                   PER_PEOPLE_F                           PERSON_ID
ORG_ID                             MTL_SYSTEM_ITEMS               ORGANIZATION_ID
ORG_ID                             MTL_ORGANIZATIONS            ORGANIZATION_ID
Po_Requisition_Lines_All
Column Names                   Table Name                              Column Name
REQUISITION_HEADER_ID   PO_REQUISITION_HEADERS_ALL  REQUISITION_HEADER_ID
REQUISITION_LINE_ID      PO_REQ_DISTRIBUTIONS_ALL          REQUISITION_LINE_ID
LINE_TYPE_ID               PO_LINE_TYPES                                    LINE_TYPE_ID
ITEM_ID                          MTL_SYSTEM_ITEMS                        INVENTORY_ITEM_ID
ORG_ID                           MTL_SYSTEM_ITEMS                        ORGANIZATION_ID
Po_Requisition_Distributions_All
Column Names                      Table Name                                Column Name
REQUISITION_LINE_ID      PO_REQUISITION_LINES_ALL     REQUISITION_LINE_ID
DISTRIBUTION_ID            PO_DISTRIBUTIONS_ALL            REQ_DISTRIBUTION_ID
SET_OF_BOOKS_ID          GL_SETS_OF_BOOKS                     SET_OF_BOOKS_ID
CODE_COMBINATION_ID  GL_CODE-COMBINATIONS            CODE_COMBINATION_ID
Po_Distributions_All
Column Names                   Table Name                                         Column Name
PO_LINE_ID                   PO_LINES                                          PO_LINE_ID
REQ_DISTRIBUTION_ID   PO_REQ_DISTRIBUTIONS_ALL       DISTRIBUTION_ID
PO_DISTRIBUTION_ID    AP_INVOICE_DISTRIBUTIONS_ALL PO_DISTRIBUTION_ID
Po_Headers_All
Column Names                   Table Name                              Column Name
PO_HEADER_ID                PO_LINES                                 PO_HEADER_ID
PO_HEADER_ID                RCV_SHIPMENT_LINES           PO_HEADER_ID
VENDOR_ID                      PO_VENDORS                          VENDOR_ID
AGENT_ID                       PER_PEOPLE                           PERSON_ID
TYPE_LOOK_UP_CODE     PO_DOCUMENT_TYPES            DOCUMENT_SUBTYPE
Po_Lines_All
Column Names                   Table Name                                    Column Name
PO_HEADER_ID               PO_HEADERS                                PO_HEADER_ID
PO_LINE_ID                     PO_DISTRIBUTIONS_ALL           PO_LINE_ID
ITEM_ID                          MTL_SYSTEM_ITEMS                   ITEM_ID
Rcv_Shipment_Lines
Column Names                   Table Name                                  Column Name
PO_HEADER_ID                PO_HEADERS                              PO_HEADER_ID
SHIPMENT_HEADER_ID   RCV_SHIPMENT_HEADERS  SHIPMENT_HEADER_ID
Ap_Invoices_All
Column Names       Table Name                                          Column Name
INVOICE_ID             AP_INVOICE_DISTRIBUTIONS_ALL  INVOICE_ID
Oe_Order_Headers_All
Column Names                   Table Name                      Column Name
HEADER_ID                        OE_ORDER_LINES                   HEADER_ID
SOURCE_HEADER_ID          WISH_DELIVERY_DETAILS     SOURCE_HEADER_ID
PRICE_LIST_ID                   QP_LIST_HEADERS_TL           LIST_HEADER_ID
ORG_ID                              MTL_ORGANIZATIONS           ORGANIZATION_ID
SALESREP_ID                      JTF_RS_SALESREPS               SALESREP_ID
ORDER_TYPE_ID                  OE_TRANSACTION_TYPES     TRANSACTION_TYPE_ID
ORDER_SOURCE_ID             OE_ORDER_SOURCES             ORDER_SOURCE_ID
ACCOUNTING_RULE_ID        RA_RULES                               RULE_ID
PAYMENT_TERM_ID              RA_TERMS                              TERM_ID
SOLD_TO_ORG_ID               HZ_CUST_ACCOUNTS             CUST_ACCOUNT_ID
SHIP_FROM_ORG_ID            MTL_PARAMETERS                 ORGANIZATION_ID
SHIP_TO_ORG_ID                HZ_CUST_SITE_USES_ALL    SITE_USE_ID
Oe_Order_Lines_All
Column Names       Table Name                             Column Name
LINE_TYPE_ID            OE_TRANSACTION_TYPES_TL     TRANSACTION_TYPE_ID
INVOICING_RULE_ID  RA_RULES                                     RULE_ID
Hz_Parties
Column Names       Table Name                         Column Name
PATY_ID                    HZ_CUST_ACCOUNTS        PATY_ID
CUST_ACCOUNT_ID   OE_ORDER_LINES             SOLD_TO_ORG_ID
Hz_Party_Sites_All
Column Names                   Table Name                Column Name
PATY_ID                              HZ_PARTIES               PATY_ID
 LOCATION_ID                     HZ_LOCATIONS         LOCATION_ID
Wsh_delivery_details
Column Names                 Table Name                              Column Name
SOURCE_HEADER_ID         OE_ORDER_HEADERS                    SOURCE_HEADER_ID
DELIVERY_DETAIL_ID        WSH_DELIVERY_ASSIGNMENTS   DELIVERY_DETAIL_ID
DELIVERY_ID                    WSH_NEW_DELIVERIES                DELIVERY_ID
INVENTORY_ITEM_ID        MTL_SYSTEM_ITEMS                     INVENTORY_ITEM_ID
RA_CUSTOMER_TRX_ALL
Column Names           Table Name                                    Column Name
CUSTOMER_TRX_ID     AR_RECEIVABLE_APPLICATIONS_ALL APPLIED_CUSTOMER_TRX_ID
TERM_ID                     RA_TERMS                                           TERM_ID
CUSTOMER_TRX_ID    RA_CUST_TRX_LINE_GL_DIST        CUSTOMER_TRX_ID
AR_CASH_REC EIPTS_ALL
Column Names                   Table Name                                   Column Name
CASH_RECEIPT_ID               AR_RECEIVABLE_APPLICATIONS_ALL  CASH_RECEIPT_ID
SET_OF_BOOKS_ID             GL_SETS_OF_BOOKS                               SET_OF_BOOKS_ID



How to find all cancel Requisitions

SELECT prha . *   FROM po_Requisition_headers_all prha , po_action_history pah   WHERE      1 = 1        AND pah . object_id ...