GL Tables
GL_ACCOUNT_HIERARCHIES stores lists of the detail accounts associated with each summary account. When you create a summary account, the list of its detail accounts is stored in this table, one detail account per row. This table stores one such list for each summary account.
GL_ALLOC_BATCHES stores information about MassAllocation and MassBudget batches. Each row includes a batch’s unique ID, name, status, and description. This table corresponds to the Define MassAllocations and Define MassBudget windows of the Define MassAllocations and Define MassBudgets forms.
GL_ALLOC_FORMULAS stores information about MassAllocation and MassBudget formulas.
GL_ALLOC_FORMULA_LINES stores information about MassAllocation and MassBudget formula lines. This table corresponds to the Formula window of the Define MassAllocations and Define MassBudgets forms. There are four or five lines associated with each MassBudget formula, and there are five lines associated with each MassAllocation formula.
GL_ALLOC_HISTORY stores one row for each batch produced by generating MassAllocation and MassBudget journals. Use information in this table to determine when you generated your MassAllocation and MassBudget journals, and for which accounting periods.
GL_BALANCES stores actual, budget, and encumbrance balances for detail and summary accounts. This table stores functional currency, foreign currency, and statistical balances for each accounting period that has ever been opened.
GL_BUDGETS stores information about your budgets. Each row includes a budget’s name, first and last periods, date created, and status. This table corresponds to the Define Budget form. Oracle General Ledger supports only one budget type (’STANDARD’), so you can uniquely identify a row with only the budget name. The CURRENT_VERSION_ID column is not currently used.
GL_BUDGET_ASSIGNMENTS stores the accounts that are assigned to each budget organization.
GL_BUDGET_ASSIGNMENT_RANGES stores the account ranges that you want to assign to a budget organization.
GL_DAILY_RATES_INTERFACE is the interface you use to create, update, and delete daily conversion rates. Customers should use this table to load rates into the GL_DAILY_RATES table.
Foreign Keys
Primary Key Table Primary Key Column Foreign Key Column
FND_CURRENCIES CURRENCY_CODE FROM_CURRENCY
FND_CURRENCIES CURRENCY_CODE TO_CURRENCY
FND_USER USER_ID USER_ID
GL_DAILY_CONVERSION_TYPES USER_CONVERSION_TYPE USER_CONVERSION_TYPE
Not null columns
FROM_CURRENCY
TO_CURRENCY
FROM_CONVERSION_DATE
TO_CONVERSION_DATE
USER_CONVERSION_TYPE
CONVERSION_RATE
MODE_FLAG
GL_IEA_INTERFACE is used to import data into the Global Intercompany System from external systems. The data entered into this table is processed by the GIS Import program.
Foreign Keys
Primary Key Table Primary Key Column Foreign Key Column
FND_CONCURRENT _REQUESTS REQUEST_ID REQUEST_ID
FND_CURRENCIES CURRENCY_CODE CURRENCY_CODE
GL_CODE_COMBINATIONS CODE_COMBINATION_ID SENDER_CODE_COMBINATION_ID
GL_CODE_COMBINATIONS CODE_COMBINATION_ID SENDER_CODE_COMBINATION_ID
GL_IEA_SUBSIDIARIES SUBSIDIARY_ID SENDER_SUBSIDIARY_ID
GL_IEA_SUBSIDIARIES SUBSIDIARY_ID RECEIVER_SUBSIDIARY_ID
GL_IEA_TRANSACTION_TYPES TRANSACTION_TYPE_ID TRANSACTION_TYPE_ID
NOT NULL Columns
GROUP_ID
TRANSACTION_TYPE_ID
TRANSACTION_STATUS_CODE
CURRENCY_CODE
GL_DATE
SENDER_SUBSIDIARY_ID
RECEIVER_SUBSIDIARY_ID
LINE_TYPE
GL_INTERFACE is the table you use to import journal entry batches through Journal Import. You insert rows in this table and then use the Import Journals form to create journal batches. You must supply values for all NOT NULL columns.
GL Flow
GENERAL LEDGER
1. Creating Journal ( Batch, Header and Lines).
2. Creating Code Combination,
3. Creating Journal Source
4. Creating Journal Category
5. Understanding Accounting Period,
6. Understanding Key Flexfileds,
7. Understanding Descriptive Flexfields
8. Understanding SetUp of General Ledger
9. Understanding Account Inquiry form
10. Understanding GL Interface Program - Journal Import
11. Understanding Chart of Accounts.
12. Understanding Set of Books
Once you create a journal look for the data in below mentioned tables
Journal countains a Batch - GL_JE_BATCHES,
Header -GL_JE_HEADERS
Lines - GL_JE_LINES,
Once you create a Code Combination look for the data in GL_CODE_COMBINATIONS
Once you understand Account Inquiry Form look for the data in
GL_BALANCES
Once you create a Journal Source look for the data in
GL_JE_SOURCES
Once you create a Journal Category look for the data in GL_JE_CATEGORIES
Once you understand Accounting Period then check the data in
GL_PERIODS,
- GL_PERIOD_STATUSES,Once you understand Chart of Accounts structure then look for the data in,- GL_CHART_OF_ACCOUNTS
Once you understand Set of Books structure then look for the data in
- GL_SETS_OF_BOOKS
Once you understand GL Interface program then check tables
- GL_INTERFACE
- GL_IMPORT_REFERENCES
Go through the all the above tables again and again and be conversant about the joins.
GENERAL LEDGER (GL)
1. Tell me about your Experiences?
A) Tell about your experience and the projects you handled in your experience and what sort of projects you handled etc..........
2. What are the Various Interfaces Conversions that you have done?
A) Journal Import, Budget Upload, Daily Rate Conversions.
3. What are various Reports and Forms you have done?
A) Developed FSG Reports in GL.
4. What is Journal Import?
A) Journal import is an interface used to bring journal entries from legacy systems and other modules into the General Ledger. (Specifically Journal Import gets entries from legacy data into the GL base tables. The tables populated during journal Import are GL_JE_BATCHES, GL_JE_HEADERS, GL_JE_LINES, GL_IMPORT_REFERENCES etc.
5. What is the use of GL_Interface?
A) Gl_Interface is the primary interface table of General ledger. It acts as an interface between data originating from other modules such as AP,AR, Legacy data and the Gl Base tables.
6. What is the significance of status column, accounting column and Reference column?
A) Status Column – Symbolizes the Journal entry in the table as either New/Update.
Accounting Date – Represents the date using which the corresponding period in the General Ledger is identified and accounting is applied.
Reference Columns – Represents additional/optional information pertaining to the batch/Journal Entrys belonging to sub-ledger modules such as AP & AR.
(REF.COLUMNS 1-10 -> Store Batch and Journal Entry names and description,)
(REF.COLUMNS 21-30-> Store information pertaining to sub-ledger modules. These Col’s are also populated as Ref. Col’s 1-10 in GL_ IMPORT_REFERENCES, GL_JE_LINES.)
7. What is Actual Flag?
A) Actual flag represents the Journal type. A-Actual, B-Budget, E- Encumbrance.
8. What is Encumbrance?
A) It is a process of Reservation of funds for anticipated expenditure from a budget. Encumbrance integrates GL, Purchasing and Payables modules.
10. How many Key Flex Fields are there in General Ledger?
A) One. Accounting Key Flex Field.
11. What is a Value Set?
A) Value set is a collection of values that could be represented by a segment.
12. How do we create Set of Books?
A) The creation of Set of books involves four basic steps.
i) Define Functional Currency
ii) Define Calendar
iii) Define the Chart of Accounts
iv) Enter code combination of Retained Earnings account.
13. How many types of Budgets are there?
A) Two Types. Expenditure Budgets, Revenue Budgets.
14. What are the Base Tables and Interface Tables for Journal Import?
Interface Tables: GL_INTERFACE, GL_BUDGET_INTERFACE
Base Tables: GL_JE_BATCHES, GL_JE_HEADERS, GL_JE_LINES,
GL_JE_SOURCES, GL_JE_CATEGORIES,
GL_SETS_OF_BOOKS, GL_DAILY_RATES, GL_BALANCES,
GL_PERIODS, GL_PERIOD_SETS, GL_CODE_COMBINATIONS
15. What is the process of kicking Journal Import from PL/SQL?
A) Using the procedure FND_SUBMIT.SUBMIT_REQUEST.
16. How do we error out a concurrent program from PL/SQL?
A) Using API_SET=VALUE/STATUS (API is a Package).
17. What are Spot Rate, Corporate Rate, Transaction Calendar and Accounting Calendar?
Spot Rate: An exchange rate which you enter to perform conversion based on the rate on a specific date. It applies to the immediate delivery of currency.
Corporate Rate: An Exchange rate that we define to standardize rates for our company. This rate is the standard market rate determined by the senior financial management for use through out the organization.
User Rate: Conversion rate that is defined by the user.
EMU Fixed Rate: An exchange rate that is provided automatically by the General Ledger while entering journals. It uses a foreign currency that has a fixed relationship with the euro.
Transaction Calendar: Defines the business days and holidays for any calendar.
Accounting Calendar: Defines different types of calendars namely Fiscal, Federal Fiscal, Month etc.
18. What are Segment Qualifiers?
Segment qualifiers hold extra information about individual segments such as if the account is an Asset, Liability or Expense, if you can post to the account and more.
When we define a segment value in the Segment Values window, we must also assign qualifiers which determine the account type (asset, liability, or expense), whether budgeting is allowed, whether posting is allowed and other information specific to the segment value.
Allow Budgeting, Allow Posting, Account Type, Control Account, Reconciliation Flag.
19. What are Flex Field Qualifiers?
A) They determine the hierarchy of the segments with in a flex field structure. They are of four different types, Balancing Segment Qualifier, Natural Accounting segment Qualifier, Cost Center Segment Qualifier, Inter Company Segment Qualifier.
20. What is MRC (Multiple Reporting Currency)?
Multiple Reporting Currency is a feature used to maintain transactions and account balances in multiple currencies. MRC is implemented at the transaction level where as Currency Translation is implemented in the Account Balances level. MRC in functionality replaces the usage of DUAL Currency and Translation processes in Oracle GL module.
21. What is Security Rule?
Security Rules are defined to control the access of a flexfield segment value (Financial information) at a responsibility level.
22. What are Cross Validation & ADI?
CVS – Cross validate segments – Allows only valid code combinations.
ADI – Allow dynamic inserts. – Allows any code combination irrespective of validity.
ADI would prevail if both of CVS and ADI are checked.
23. What is Translation?
A) Translation is a process used to convert functional currency to other reporting currencies at the account balances level.
24. What is Revaluation?
A) It is process used to revalue assets and liabilities denominated in foreign currency into functional currency based on period end exchange rate we specify. Unrealized gains/losses are resulted because of exchange rate fluctuations which are recorded in unrealized gain/loss account in GL.
25. What is FSG (Financial Statement Generator)?
A) Financial statement generator feature helps us to generate reports such as balance sheets and income statements with out programming. It also provides a high degree of control on the rows, columns, contents and calculations on the report. Different components such as row set, column set, content set, row order, display set have to be defined before a statement is generated, of which row set and column set are mandatory.
26. What is Consolidation?
A) Consolidation is a period-end process of combining the financial results of separate business subsidiaries with the parent company to form a single combined statement of financial results.
27. At what level General Ledger data is secured?
A) GL data is secured at Set of Book level. Subledger module data is secured at Responsibility level (i.e., at Operating Unit Level).
Accounts Payable (AP)
1) What are the different types of Interfaces?
Ans Refer Laxman Vendor import notes in scanned documents
a) Vendor conversion / Import
A vendor is any company or person that we buy goods or services from.
Interface table --- we need to create our own custom interface table.
Base tables
PO_VENDORS (Segment1-Vendor number is unique)
PO_VENDOR_CONTACTS
PO_VENDOR_SITES_ALL
b) Legacy Invoice
Interface Tables
AP_INVOICE _INTERFACE is the header info
AP_INVOICES_LINES_INTERFACE is the lines table.
RUN PAYABLES INVOICE IMPORT CONCURRENT PROGRAM
Base Tables
AP_INVOICE_ALL is also the header information is stored.
AP_INVOICES_DISTRIBUTIONS_ALL is the lines information table.
AP_PAYMENTS_SCHEDULES_ALL (AMOUNT_REMAINING stores balance amount to be paid)
AP_INVOICE_PAYMENTS_ALL (INVOICE_ID)
AP_CHECKS_ALL (CHECK_ID)
AP_INTERFACE_REJECTIONS (REJECT_LOOKUP_CODE stores the error occurred during the invoice import ex: account required, invalid supplier site )
2) What is the process of Vendor Conversion?
This is always custom work. There is no predefined program to import the vendors/supplier from legacy system to oracle. We need write the SQL*Loader file to custom interface table and write pl/sql programs to hit the base tables directly into the po_vendors table,po_vendor_sites_table and po_vendors_contacts.
3) In which table and column Vendor Number stores?
In PO_VENDORS table SEGMENT1 stores the vendor number. User doesn’t know about the (vendor id) ids only the developer knows them.
4) After conversions how do you get the Next Vendor column?
We need to resync the PO_UNIQUE_IDENTIFIER_CONTROL (field called CURRENT_MAX_UNIQUE_IDENTIFIER) with the sequence called PO_VENDORS_S. This resync was run after inserting into vendors and updating the info in the sites table. The current max unique identifier should be equal to the nextval in the po_vendors_s sequence.
5) What are the setups required for vendor conversions?
Before loading from the legacy system to base table we need to define
Supplier type, Payment type, Payment terms, Payment group.
6) Which module is the owner for vendor (supplier) tables?
Ans--- Purchasing module is the owner of the supplier table.
7) What is the process of creating an Invoices and transferring it to GL?
1. create batch
2. create invoice
3. create distribution
4. validate the invoice
5. actions -à approve
6. if individual create accounting click ok
7. If batch go to batch create accounting.
8. Create accounting hits Payable Accounting(Transfer) ??Program which will create accounting.
9. Run Transfer to GL Concurrent Program
10. Journal Import
11. Post journals
12. Hits balances.
8) How do u Transfer from AP to GL?
Ans---“Payables transfer to GL program” is used to transfer from AP to GL.
10) How many types of Transactions are there in AP?
1. Standard Invoice : The amount is g
2. Debit memo (increases balances owed to supplier)
1. Raised by organization
2. Raised by Supplier.
3. Credit memo
4. Prepayment
5. Mixed Invoices both debit & credit
6. Expense Report employees
7. Quick Match
8. P.O.Default
11) Tell me about PO cycle( Procure To Pay )?
1. Requisition
2. Manager
3. Approval
4. Request For Quote (RFQ)
5. Quotation
6. Quote Analysis (Track/check record)
7. Issue Purchase Order (PO)
8. Goods Receipt Note(GRN)
9. Invoice
10. Transfer To GL (Payables transfer to GL program)
11. Journal Import
12. GL Balances
12) How many types of purchase order types/agreements are there?
a) Standard Purchase Order
b) Planned PO : A planned purchase order is a long-term agreement committing to buy it
items or services from a single source. You must specify tentative delivery schedules and all details for goods or services that you want to buy, including charge account, quantities and estimated cost.
EX: Buying goods for Christmas from a specific dealer.
c) Contract PO : You create contract purchase agreement with your supplier to agree on specific terms and conditions without indicating the goods and services that you will be purchasing i.e. for $ amount you must supply this much quantity. You can later issue standard PO referencing your contracts and you can encumber these purchase orders if you use encumbrance accounting.
d) Blanket PO : You create blanket purchase agreements when you know the detail of goods or services you plan to buy from a specific supplier in a period , but you do not yet know the detail of your delivery schedules. You can use blanket purchase agreements to specify negotiated prices for your items before actually purchasing them.
A Blanket Purchase Agreement is a sort of contract between the you and ur supplier about the price at which you will purchase the items from the supplier in future. Here you enter the price of the item not the quantity of the items. When you create the release you enter the quantity of the items. The price is not updatable in the release. The quantity * price makes the Released Amount. Now suppose your contract with your supplier is such that you can only purchase the items worth a fixed amount against the contract.
13) What is 2-way, 3-way, 4-way matching?
2-way matching: 2-way matching verifies that Purchase order and invoice quantities must match within your tolerances as follows:
Quantity billed <= Quantity Ordered Invoice price <= Purchase order price (<= sign is used because of tolerances) Often used for services where no receiver is generated.
3-way matching: 3-way matching verifies that the receipt and invoice information match with the quantity tolerances defined:
Quantity billed <= Quantity received 4-way matching: 4-way matching verifies that acceptance documents and invoice information match within the quantity tolerances defined: Quantity billed <= Quantity accepted. (Acceptance is done at the time of Inspecting goods).
Whether a PO shipment has 2-way, 3-way or 4-way matching can be setup in the Shipment Details zone of the Enter PO form (character)
Receipt required Inspection required Matching
Yes Yes 4-way
Yes No 3-way
No No 2-way
INVENTORY (INV)
1. What is item import? How is it done?
A) The process of converting inventory items from another inventory system, migrating assembly and component items from a legacy manufacturing system, converting purchase items from a custom purchasing system and importing new items from a product data management package into Oracle Inventory. This import mechanism is achieved through a concurrent program called Open Item Interface.
Custom programs are executed prior to item interface and this gets data from the external systems into the interface tables such as MTL_SYSTEM_ITEMS _INTERFACE and MTL_ITEM_REVISIONS_INTERFACE. Item Interface program is then run which actually imports the items and revision information from the above mentioned interface face tables into the base tables such as MTL_SYSTEM_ITEMS_B, MTL_ITEM_REVISIONS. Item Interface assigns defaults and validates the data to ensure data integrity before feeding data into base tables.
2. What are Interface and Base Tables?
Interface Tables : MTL_SYSTEM_ITEMS_INTERFACE
MTL_ITEM_REVISIONS_INTERFACE (Oracle Defaults)
MTL_ITEM_CATEGORIES_INTERFACE (Oracle has Provided certain default categories, if not specified)
MTL_INTERFACE_ERRORS
Base Tables : MTL_SYSTEM_ITEMS_B (Segment 1 stores Model # of Item)
MTL_ITEM_ATTRIBUTES, MTL_ITEM_CATEGORIES, MTL_ITEM_LOCATIONS, MTL_ITEM_REVISIONS
MTL_ITEM_STATUS (Status Active/Engineer etc)
MTL_ITEM_SUB_INVENTORIES
3. What are Item Attributes?
A) Attributes are the specific characteristics associated to every item, namely order cost, item status, revision control, COGS account etc.
4. What are Templates?
Templates are the defined set of attributes that can be used over and over to create similar items. Templates initial definition of items easier. Oracle has provided certain predefined templates such as (ATO MODEL, ATO OPTION CLASS, and FINISHED GOOD etc). Templates can also be User defined .
5) What are Status Codes?
A) Statuses are used to provide default values to certain item attributes to control the functionality of an item. Statuses typically default 8 item attributes namely, BOM allowed, build in WIP, Customer orders enabled, internal orders enabled, invoice enabled, transactable, purchasable, stockable.
Different status types include Active, Inactive, Engineer, obsolete, Phase-out, Prototype, OPM.
6. What are Categories and Category Sets?
A) Category is a code used to group items with similar characteristics such as plastics, metals or glass items etc.
A subset of categories grouped together is termed as a Category set. Typical category sets include purchasing, materials, costing and planning.
8. What are Lot Numbers and Serial Numbers?
A) Lot number is a number that identifies a specific batch of items.
Serial Number is a number assigned to each unit of an item and used to track the item.
9. What are Locators?
A) A locator is a physical area with in the sub inventory where you store material such as a row, aisle, shelf, or a bin etc.
10. What is a Sub Inventory?
It is a subdivision of an organization representing a physical area or a logical grouping of items such as store room or a receiving dock.
11. What are the flexfields in Inventory module?
A) Item Key Flexfield, Category Key Flexfield.
12. While importing items from the legacy system through items interface what profile options do u set.
There are two profile options that we need to check, before running the Item Import. They are
i) PRIMARY_UNIT_OF_MEASURE from INV: Define Primary Unit of Measure
ii) INVENTORY_ITEM_STATUS_CODE from INV: Define Item Status
Order Management (OM)
1) What are the Base Tables and Interface Tables for Order Management?
Interface Tables : OE_HEADERS_IFACE_ALL, OE_LINES_IFACE_ALL
OE_PRICE_ADJS_IFACE_ALL, OE_ACTIONS_IFACE_ALL
OE_CREDITS_IFACE_ALL (Order holds like credit check holds etc)
Base Tables : OE_ORDER_HEADERS_ALL: Order Header Information
OE_ORDER_LINES_ALL: Items Information
OE_PRICE_ADJUSTMENTS: Discounts Information
OE_SALES_CREDITS: Sales Representative Credits.
Shipping Tables :WSH_NEW_DELIVERIES, WSH_DELIVERY_DETAILS, WSH_DELIVERY_ASSIGNMENTS, WSH_DELIVERIES.
2) What are the Base Tables and Interface Tables for Order Management?
Interface Tables : OE_HEADERS_IFACE_ALL, OE_LINES_IFACE_ALL
OE_PRICE_ADJS_IFACE_ALL, OE_ACTIONS_IFACE_ALL
OE_CREDITS_IFACE_ALL (Order holds like credit check holds etc)
Base Tables : OE_ORDER_HEADERS_ALL: Order Header Information
OE_ORDER_LINES_ALL: Items Information
OE_PRICE_ADJUSTMENTS: Discounts Information
OE_SALES_CREDITS: Sales Representative Credits.
Shipping Tables :WSH_NEW_DELIVERIES, WSH_DELIVERY_DETAILS, WSH_DELIVERY_ASSIGNMENTS, WSH_DELIVERIES.
3) What is Order Import and What are the Setup's involved in Order Import?
A) Order Import is an open interface that consists of open interface tables and a set of API’s. It imports New, updated, or changed sales orders from other applications such as Legacy systems. Order Import features include validations, Defaulting, Processing Constraints checks, Applying and releasing of order holds, scheduling of shipments, then ultimately inserting, updating or deleting orders from the OM base tables. Order management checks all the data during the import process to ensure its validity with OM. Valid Transactions are then converted into orders with lines, reservations ,price adjustments, and sales credits in the OM base tables.
B) Setups:
· Setup every aspect of order management that we want to use with imported orders, including customers, pricing, items, and bills.
· Define and enable the order import sources using the order import source window.
4) Explain the Order Cycle?
i) Enter the Sales Order
ii) Book the Sales Order(SO will not be processed until booked(Inventory confirmation))
iii) Release sales order(Pickslip Report is generated and Deliveries are created)
(Deliveries – details about the delivery. Belongs to shipping module (wsh_deliveries, wsh_new_deliveries, wsh_delivery_assignments etc) they explain how many items are being shipped and such details.
iv) Transaction Move Order (creates reservations determines the source and transfers the inventory into the staging areas)
v) Launch Pick Release (
vi) Ship Confirm (Shipping Documents(Pickslip report, Performa Invoice, Shipping Lables))
vii) Auto invoice and closed
5) Explain the Order to Cash Flow?
I. Enter the Sales Order
II. Book the Sales Order(SO will not be processed until booked(Inventory confirmation))
III. Release sales order(Pickslip Report is generated and Deliveries are created)
(Deliveries – details about the delivery. Belongs to shipping module (wsh_deliveries, wsh_new_deliveries, wsh_delivery_assignments etc) they explain how many items are being shipped and such details.
IV. Transaction Move Order (Selects the serial number of the product which has to be moved/ shipped)
V. Launch Pick Release
VI. Ship Confirm (Shipping Documents(Pickslip report, Performa Invoice, Shipping Lables))
VII. AutoInvoice (Creation of Invoice in Accounts Receivable Module)
VIII.Autolockbox ( Appling Receipts to Invoices In AR)
IX. Transfer to General Ledger ( Populates GL interface tables)
X. Journal Import ( Populates GL base tables)
XI. Posting ( Account Balances Updated).
5. What are the Process Constraints?
A. Process Constraints prevent users from adding updating, deleting, splitting lines and canceling order or return information beyond certain points in the order cycle. Oracle has provided certain process constraints which prevent data integrity violations.
Process constraints are defined for entities and attributes. Entities include regions on the sales order window such as order, line, order price adjustments, line price adjustments, order sales credits and line sales credits. Attributes include individual fields (of a particular entity) such as warehouse, shit to location, or agreement.
6. What are Validation Templates?
A) Validation Templates are used to define the validation conditions in process constraints. A validation template names a conditions and defines the semantic of how to validate that condition. These are used in processing constraints framework to specify the constraining conditions for a given constraint. These conditions are based on
1 Where the entity is in its work flow.
2 The state of attributes on an entity.
3 Any other validation condition that cannot be modeled using the above condition.
7. What are different types of Holds?
1 GSA(General Services Administration) Violation Hold(Ensures that specific customers always get better pricing for example Govt. Customers)
2 Credit Checking Hold( Used for credit checking feature Ex: Credit Limit)
3 Configurator Validation Hold ( Cause: If we invalidate a configuration after booking)
8. What is Document Sequence?
A) Document sequence is defined to automatically generate numbers for your orders or returns as you enter them. Single / multiple document sequences can be defined for different order types.
Document sequences can be defined as three types Automatic (Does not ensure that the numbers are contiguous), Gapless (Ensures that the numbering is contiguous), Manual Numbering. Order Management validates that the number specified is unique for order type.
9. What are Defaulting Rules?
A) A defaulting rule is a value that OM automatically places in an order field of the sales order window. Defaulting rules reduce the amount of information one must enter. A defaulting rule is a collection of defaulting sources for objects and their attributes.
It involves the following steps
1 Defaulting Conditions - Conditions for Defaulting
2 Sequence – Priority for search
3 Source – Entity ,Attribute, Value
4 Defaulting source/Value
10. When an order cannot be cancelled?
A) An order cannot be cancelled if,
1 It has been closed
2 It has already been cancelled
3 A work order is open for an ATO line
4 Any part of the line has been shipped or invoiced
5 Any return line has been returned or credited.
11. When an order cannot be deleted?
A) you cannot delete an order line until there is a need for recording reason.
12. What is order type?
A) An order type is the classification of order. It controls the order work flow activity, order number sequence, credit check point and transaction type. Order Type is associated to a work flow process which drives the processing of the order.
13. What are primary and secondary price lists?
A) Every order is associated to a price list as each item on the order ought to have a price. A price list is contains basic list information and one or more pricing lines, pricing attributes, qualifiers, and secondary price lists. The price list that is primarily associated to an order is termed as Primary price list.
The pricing engine uses a Secondary Price list if it cannot determine the price of the item ordered in the Primary price list.
14. What is pick slip? Types?
A) It is an internal shipping document that pickers use to locate items to ship for an order.
1 Standard Pick Slip – Each order will have its own pick slip with in each picking batch.
2 Consolidated Pickslip – Pick slip will have all the orders released in the each picking batch.
15. What is packing slip?
A) It is an external shipping document that accompanies the shipment itemizing the contents of the shipment.
16. What are picking rules?
A) Picking rules define the sources and prioritization of sub inventories, lots, revisions and locators when the item is pick released by order management. They are user defined set of rules to define the priorities order management must use when picking items from finished goods inventory to ship to a customer.
17. Where do you find the order status column?
A) In the base tables, Order Status is maintained both at the header and line level. The field that maintains the Order status is FLOW_STATUS_CODE. This field is available in both the OE_ORDER_HEADERS_ALL and OE_ORDER_LINES_ALL.
18. When the order import program is run it validates and the errors occurred can be seen in?
A) Responsibility: Order Management Super User
Navigation: Order, Returns > Import Orders > Corrections
Accounts Receivable (AR)
1. What is TCA? Tables?
A) Trading Community Architecture. It is a centralized repository of business entities such as Partners, Customers, and Organizations etc. It is a new framework developed in Oracle 11i.
HZ_PARTIES: The HZ_PARTIES table stores basic information about parties that can be shared with any relationship that the party might establish with another party. Although a record in the HZ_PARTIES table represents a unique party, multiple parties can have the same name. The parties can be one of four types:
Organization for example, Oracle CorporationPerson for example, Jane DoeGroup for example, World Wide Web ConsortiumRelationship for example, Jane Doe at Oracle Corporation.
HZ_LOCATIONS: The HZ_LOCATIONS table stores information about a delivery or postal address such as building number, street address, postal code, and directions to a location. This table provides physical location information about parties (organizations and people) and customer accounts.
HZ_PARTY_SITES: The HZ_PARTY_SITES table links a party (see HZ_PARTIES) and a location (see HZ_LOCATIONS) and stores location-specific party information. One party can optionally have one or more party sites. One location can optionally be used by one or more parties. This party site can then be used for multiple customer accounts within the same party.
HZ_CUST_ACCT_SITES_ALL
HZ_CUST_SITE_USES_ALL
HZ_CUST_CONTACT_POINTS etc.
2. What are Base Tables or Interface Tables for Customer Conversions, Autolockbox, Auto Invoice?
A) Customer Conversion:
Interface Tables : RA_CUSTOMERS_INTERFACE_ALL, RA_CUSTOMER_PROFILES_INT_ALL,
RA_CONTACT_PHONES_INT_ALL,
RA_CUSTOMER_BANKS_INT_ALL,
RA_CUST_PAY_METHOD_INT_ALL
Base Tables : RA_CUSTOMERS, RA_ADDRESSES, RA_SITE_USES_ALL,
RA_CUSTOMER_PROFILES_ALL, RA_PHONES etc
B) Auto Invoice:
Interface Tables : RA_INTERFACE_LINES_ALL, RA_INTERFACE_DISTRIBUTIONS_ALL
RA_INTERFACE_SALESCREDITS_ALL, RA_INTERFACE_ERRORS_ALL
Base Tables : RA_CUSTOMER_TRX_ALL, RA_CUSTOMER_TRX_LINES_ALL,
RA_CUST_TRX_LINE_GL_DIST_ALL, RA_CUST_TRX_LINE_SALESREPS_ALL, RA_CUST_TRX_TYPES_ALL
C) AutoLockBox:
Interface Tables : AR_PAYMENTS_INTERFACE_ALL (POPULATED BY IMPORT PROCESS)
Interim tables : AR_INTERIM_CASH_RECEIPTS_ALL (All Populated by Submit Validation)
: AR_INTERIM_CASH_RCPT_LINES_ALL,
AR_INTERIM_POSTING
Base Tables : AR_CASH_RECEIPTS_ALL, AR_RECEIVABLE_APPLICATIONS_ALL,
AR_PAYMENT_SCHEDULES_ALL ( All Populated by post quick cash)
3. What are the tables in which Invoices/transactions information is stored?
A) RA_CUSTOMER_TRX_ALL, The RA_CUSTOMER_TRX_ALL table stores invoice, debit memo, commitment, bills receivable, and credit memo header information. Each row in this table includes general invoice information such as customer, transaction type, and printing instructions.
RA_CUSTOMER_TRX_LINES_ALL, The RA_CUSTOMER_TRX_LINES_ALL table stores information about invoice, debit memo, credit memo, bills receivable, and commitment lines (LINE, FREIGHT and TAX).
RA_CUST_TRX_LINE_SALESREPS_ALL, The RA_CUST_TRX_LINE_SALESREPS_ALL table stores sales credit assignments for invoice lines. If Receivables bases your invoice distributions on sales credits, a mapping exists between the sales credit assignments in this table with the RA_CUST_TRX_LINE_GL_DIST_ALL table.
The RA_CUST_TRX_LINE_GL_DIST_ALL table stores the accounting records for revenue, unearned revenue, and unbilled receivables for each invoice or credit memo line. Oracle Receivables creates one row for each accounting distribution, and at least one accounting distribution must exist for each invoice or credit memo line. Each row in this table includes the General Ledger account and the amount of the accounting entry.
The RA_CUST_TRX_LINE_SALESREPS_ALL table stores sales credit assignments for invoice lines. If Receivables bases your invoice distributions on sales credits, a mapping exists between the sales credit assignments in this table with the RA_CUST_TRX_LINE_GL_DIST_ALL table.
4. What are the tables In which Receipt information is stored?
A) AR_PAYMENT_SCHEDULES_ALL, The AR_PAYMENT_SCHEDULES_ALL 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.
Transaction classes determine if a transaction relates to either the RA_CUSTOMER_TRX_ALL table or the AR_CASH_RECEIPTS_ALL table. Using the CUSTOMER_TRX_ID foreign key column, the AR_PAYMENT_SCHEDULES_ALL table joins to the RA_CUSTOMER_TRX_ALL table for non-payment transaction entries, such as the creation of credit memos, debit memos, invoices, chargebacks, or deposits. Using the CASH_RECEIPT_ID foreign key column, the AR_PAYMENT_SCHEDULES_ALL table joins to the AR_CASH_RECEIPTS_ALL table for invoice-related payment transactions.
AR_CASH_RECEIPTS_ALL, The AR_CASH_RECEIPTS_ALL table stores one record for each receipt that you enter. Oracle Receivables concurrently creates records in the AR_CASH_RECEIPT_HISTORY_ALL, AR_PAYMENT_SCHEDULES_ALL, and AR_RECEIVABLE_APPLICATIONS_ALL tables for invoice-related receipts. For receipts that are not related to invoices, such as miscellaneous receipts, Receivables creates records in the AR_MISC_CASH_DISTRIBUTIONS_ALL table instead of the AR_RECEIVABLE_APPLICATIONS_ALL table.
AR_RECEIVABLE_APPLICATIONS_ALL, The AR_CASH_RECEIPTS_ALL table stores one record for each receipt that you enter. Oracle Receivables concurrently creates records in the AR_CASH_RECEIPT_HISTORY_ALL, AR_PAYMENT_SCHEDULES_ALL, and AR_RECEIVABLE_APPLICATIONS_ALL tables for invoice-related receipts. For receipts that are not related to invoices, such as miscellaneous receipts, Receivables creates records in the AR_MISC_CASH_DISTRIBUTIONS_ALL table instead of the AR_RECEIVABLE_APPLICATIONS_ALL table. Cash receipts proceed through the confirmation, remittance, and clearance steps. Each step creates rows in the AR_CASH_RECEIPT_HISTORY table.
5. What are the tables in which Accounts information is stored?
RA_CUST_TRX_LINE_GL_DIST_ALL
6. What are the different statuses for Receipts?
A) Unidentified – Lack of Customer Information
Unapplied – Lack of Transaction/Invoice specific information (Ex- Invoice Number)
Applied – When all the required information is provided.
On-Account, Non-Sufficient Funds, Stop Payment, and Reversed receipt.
8. What is Autolockbox?
A) Auto lockbox is a service that commercial banks offer corporate customers to enable them to out source their account receivable payment processing. Auto lockbox can also be used to transfer receivables from previous accounting systems into current receivables. It eliminates manual data entry by automatically processing receipts that are sent directly to banks. It involves three steps
1 Import (Formats data from bank file and populates the Interface Table),
2 Validation(Validates the data and then Populates data into Interim Tables),
3 Post Quick Cash(Applies Receipts and updates Balances in BaseTables).
9. What is Transmission Format?
A) Transmission Format specifies how data in the lockbox bank file should be organized such that it can be successfully imported into receivables interface tables. Example, Default, Convert, Cross Currency, Zengen are some of the standard formats provided by oracle.
10. What is Auto Invoice?
A) Autoinvoice is a tool used to import and validate transaction data from other financial systems and create invoices, debit-memos, credit memos, and on account credits in Oracle receivables. Using Custom Feeder programs transaction data is imported into the autoinvoice interface tables.
Autoinvoice interface program then selects data from interface tables and creates transactions in receivables (Populates receivable base tables) . Transactions with invalid information are rejected by receivables and are stored in RA_INTERFACE_ERRORS_ALL interface table.
11. What are the Mandatory Interface Tables in Auto Invoice?
RA_INTERFACE_LINES_ALL, RA_INTERFACE_DISTRIBUTIONS_ALL
RA_INTERFACE_SALESCREDITS_ALL.
12. What are the Set up required for Custom Conversion, Autolockbox and Auto Invoice?
A) Autoinvoice program Needs AutoAccounting to be defined prior to its execution.
13. What is AutoAccounting?
A) By defining AutoAccounting we specify how the receivables should determine the general ledger accounts for transactions manually entered or imported using Autoinvoice. Receivables automatically creates default accounts(Accounting Flex field values) for revenue, tax, freight, financial charge, unbilled receivable, and unearned revenue accounts using the AutoAccounting information.
14. What are Autocash rules?
A) Autocash rules are used to determine how to apply the receipts to the customers outstanding debit items. Autocash Rule Sets are used to determine the sequence of Autocash rules that Post Quickcash uses to update the customers account balances.
15. What are Grouping Rules? (Used by Autoinvoice)
A) Grouping rules specify the attributes that must be identical for lines to appear on the same transaction. After the grouping rules are defined autoinvoice uses them to group revenues and credit transactions into invoices debit memos, and credit memos.
16. What are Line Ordering Rules? (Used by Autoinvoice)
A) Line ordering rules are used to order transaction lines when grouping the transactions into invoices, debit memos and credit memos by autoinvoice program. For instance if transactions are being imported from oracle order management , and an invoice line ordering rule for sales_order _line is created then the invoice lists the lines in the same order of lines in sales order.
17. In which table you can see the amount due of a customer?
A) AR_PAYMENT_SCHEDULES_ALL
18. How do you tie Credit Memo to the Invoice?
At table level, In RA_CUSTOMER_TRX_ALL, If you entered a credit memo, the PREVIOUS_CUSTOMER_TRX_ID column stores the customer transaction ID of the invoice that you credited. In the case of on-account credits, which are not related to any invoice when the credits are created, the PREVIOUS_CUSTOMER_TRX_ID column is null.
19. What are the available Key Flex Fields in Oracle Receivables?
A) Sales Tax Location Flex field, It’s used for sales tax calculations.
Territory Flex field is used for capturing address information.
20. What are Transaction types? Types of Transactions in AR?
A) Transaction types are used to define accounting for different transactions such as Debit Memo, Credit Memo, On-Account Credits, Charge Backs, Commitments and invoices.
REPORTS
1. What is a Lexical Parameter?
Lexical parameters are used to substitute multiple values at runtime and are identified by a preceding ‘&’. Lexicals can consist of as little a one line where clause to an entire select statement
Lexical Parameters are used to execute query dynamically.
Example: An example of a lexical parameter usage in a select statement is as follows
Select * from emp, deptno
&where.
In the properties of the 'where' user parameter, make sure that the data type of the 'where' user parameter is set as character. If you know the maximum length that your where clause is going be, You can set the width of the where parameter to be slightly greater than that number. Otherwise, set it to some number like 100.
If your lexical parameter ('where') width is not enough to hold the where condition assigned to it, you will receive one of the following errors depending on your Reports version.
REP-0450 - Unhandled exception,
and ORA-6502- PL/SQL numeric or value error.
or
REP-1401 - Fatal PL/SQL error in after trigger
and ORA-6502-PL/SQL numeric or value error.
2. What is a Bind Variable?
Bind parameters are used to substitute single value at runtime for evaluation and are identified by a preceding ‘:’. An example of a bind parameter in a select statement is provided below, where :P_EMP is the bind parameter reference.
Select ename,empno
From emp
Where empno= :P_EMP
These are used as tokens while registering concurrent program.
3. Difference between lexical and bind variable?
Bind references are used to replace a single value in SQL or PL/SQL. Specifically, bind references may be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH clauses of queries. Binds may not be referenced in the FROM clause. An example is:
SELECT ORDID, TOTAL
FROM ORD
WHERE CUSTID = :CUST
Lexical references are placeholders for text that you embed in a SELECT statement. You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY , ORDER BY , HAVING, CONNECT BY, and START WITH. You cannot make lexical references in PL/SQL. Before you reference a lexical parameter in a query you must have predefined the parameter and given it an initial value. An example is:
SELECT ORDID, TOTAL
FROM &ATABLE
4. How many types of Triggers are there and what are they? Tell their sequence of execution.
Report triggers execute PL/SQL functions at specific times during the execution and formatting of your report. Using the conditional processing capabilities of PL/SQL for these triggers, you can do things such as customize the formatting of your report, perform initialization tasks, and access the database. To create or modify a report trigger, use Report Triggers in the Object Navigator. Report triggers must explicitly return TRUE or FALSE. Report Builder has five global report triggers (you cannot create new global report triggers):
Before Parameter Form trigger
After Parameter Form trigger
Before Report trigger
Between Pages trigger
After Report trigger
Before Report trigger and After Report trigger should be declared compulsory. In the Before Report trigger we declare the srw.user_exit(‘ fnd srwinit’) user exist and in the After Report trigger srw.user_exit (‘fnd srwexit’)
The sequence/order of events when a report is executed is as follows:Before Parameter Form trigger is fired.
1 Runtime Parameter Form appears (if not suppressed).
2 After Parameter Form trigger is fired (unless the user cancels from the Runtime ParameterForm).
3 Report is "compiled."
4 Queries are parsed.
5 Before Report trigger is fired.
6 SET TRANSACTION READONLY is executed (if specified via the READONLY argumentor setting).
7 The report is executed and the Between Pages trigger fires for each page except the last one.(Note that data can be fetched at any time while the report is being formatted.) COMMITscan occur during this time due to any of the following--user exit with DDL, SRW.DO_SQLwith DDL, or if ONFAILURE=COMMIT, and the report fails.
8 COMMIT is executed (if READONLY is specified) to end the transaction.
9 After Report trigger is fired.10 COMMIT/ROLLBACK/NOACTION is executed based on what was specified via theONSUCCESS argument or setting.
Cautions=========
1. In steps 4 through 9, avoid DDL statements that would modify the tables on which thereport is based. Step 3 takes a snapshot of the tables and the snapshot must remain validthroughout the execution of the report. In steps 7 through 9, avoid DML statements thatwould modify the contents of the tables on which the report is based. Queries may beexecuted in any order, which makes DML statements unreliable (unless performed on tablesnot used by the report).2. If you specify READONLY, you should avoid DDL altogether. When you execute a DDLstatement (e.g., via SRW.DO_SQL or a user exit), a COMMIT is automatically issued. If youare using READONLY, this will prematurely end the transaction begun by SETTRANSACTION READONLY.
Report trigger restrictions=============================
1. If you are sending your report output to the Runtime Previewer or Live Previewer, youshould note that some or all of the report triggers may be fired before you see the reportoutput. For example, suppose that you use SRW.MESSAGE to issue a message in theBetween Pages trigger when a condition is met. If there are forward references in the report(e.g., a total number of pages displayed before the last page), Report Builder may have toformat ahead to compute the forward references. Hence, even though you have not yet seena page, it may already have been formatted and the trigger fired.
2. In report triggers, you can use the values of report-level columns and parameters. Forexample, you might need to use the value of a parameter called COUNT1 in a condition(e.g., IF :COUNT1 = 10). Note, though, that you cannot reference any page-dependent columns (i.e., a column with a Reset At of Page) or columns that rely on page-dependent columns.
3. In the Before and After Parameter Form, and Before and After Report triggers, you can setthe values of parameters (e.g., give them a value in an assignment statement, :COUNT1 =15). In the Before and After Report triggers, you can also set the values of report-level,placeholder columns.
4. In the Between Pages trigger, you cannot set the values of any data model objects. Note alsothat the use of PL/SQL global variables to indirectly set the values of columns or parametersis not recommended. If you do this, you may get unpredictable results.
5. If you run a report from Report Builder Runtime (i.e., not the command line orSRW.RUN_REPORT), you should commit database changes you make in the BeforeParameter Form, After Parameter Form, and Validation triggers before the report runs.When running in this way, these triggers will share the parent process’ database connection.When the report is actually executed, however, it will establish its own database connection.
6. A lexical reference cannot be used to create additional bind variables after the AfterParameter Form trigger fires. For example, suppose you have a query like the following(note that the WHERE clause is replaced by a lexical reference):SELECT ENAME, SAL FROM EMP&where_clauseIf the value of the WHERE_CLAUSE parameter contains a reference to a bind variable, youmust specify the value in the After Parameter Form trigger or earlier. You would get anerror if you supplied the following value for the parameter in the Before Report trigger. Ifyou supplied this same value in the After Parameter Form trigger, the report would run.WHERE SAL = :new_bind
5. What is a Format Trigger?
Format triggers are PL/SQL functions executed before the object is formatted. The trigger can be used to dynamically change the formatting attributes of the object. The function must return a Boolean value (TRUE or FALSE). Depending on whether the function returns TRUE or FALSE, the current instance of the object is included or excluded from the report output. You can access format triggers from the Object Navigator, the Property Palette, or the PL/SQL Editor.
A format trigger is a PL/SQL function executed before an object is formatted. A trigger can be used to dynamically change the formatting attributes of the object.
6. What is Anchoring?
It is a feature thru which we can control the position of the boiler plate or data fields in layout.
Anchors are used to determine the vertical and horizontal positioning of a child object relative to its parent. The end of the anchor with a symbol is attached to the parent object.
When you create a default layout, Reports will create some of its own implicit anchors. These are not visible. There may be occasions when you want to create your own explicit anchors to force objects to be positioned together or to conditionally specify when the object prints.
You create an explicit anchor as follows:
1. Select the Anchor tool in the Layout Tool Palette.
2. Click on an edge of the Child object.
3. Move the cursor to the edge of the Parent object and double click to fix the anchor.
You can position the anchor at any distance down the edge of the object. The distance is a percentage of the total length of the edge. You can adjust this position in the anchor property sheet.
Examples of using explicit anchors:
ANCHORING BOILERPLATE TO A FRAME
--------------------------------
You may want to display some boiler plate to the right of, and half way down a vertical list of records.
In this case, you would create an anchor from the child boilerplate to the parent, group or repeating frame. Ensure the parent end point is 50% down the right edge of the frame.
ANCHORING CONDITIONAL OBJECTS
----------------------------
To adjust the position of a layout object if the anchoring parent does not display, you can define your explicit anchor as collapsible either horizontally or vertically. The child layout object then collapses, to suppress additional spacing, if the parent object does not print.
An example of where you might use this would be on Mailing Labels.
Mailing Labels often include optional fields to allow variable number of lines in an address. You may want to suppress the fields that are null, so that the address in the labels does not have gaps between the lines.
For example:
f_name
f_address1
f_address2
f_address3
f_address4
where f_address2 is an optional field.
1. Select f_address2 in the layout editor and go into the property sheet.
2. In Reports V2.5, under the general layout tab, click on the Format Trigger
Edit button to create the following format trigger.
In other versions of Reports, under advanced layout, click on the Format
Trigger to create the following format trigger.
FUNCTION f_address2 RETURN BOOLEAN IS
BEGIN
IF :address2 IS NULL THEN
RETURN (FALSE);
ELSE
RETURN (TRUE);
END IF;
END;
3. Then create an anchor from f_address3 (the field below) upto to f_address2 (the optional field). In the anchor properties place a check in the collapse vertically check box.
4. Create another anchor, this time from f_address4 to f_address3, again setting it to collapse vertically. This process needs to be done for all the fields below the optional field to avoid any unwanted spaces.
7. What is Frame and Repeating Frame?
Frames are used to surround other objects and protect them from being overwritten or pushed by other objects. For example, a frame might be used to surround all objects owned by a group, to surround column headings, or to surround summaries.
Repeating frames are place holders for records. Repeating frames print once for each record of a group and control record-level formatting. Reports will generate one repeating frame for each group when you create a default layout.
Reports will place containers of columns inside of the frames. Each repeating frame retrieves only one row in its fetch cycle for any one repetition. Until it is constrained by another frame, it will repeat itself until the while loop condition can no longer be satisfied.
We give group in data model as source to repeating frame.
8. What are Confined Mode and Flex Mode?
Confined mode allows objects to be locked into the place in the layout. Objects are maintained within their containers.
CONFINE mode is not for a specific object, but applies to all objects on the layout when it is enabled (locked).When it is turned off (unlocked), you are allowed to move an object outside its surrounding frame. When it is turned on (locked), you are unable to move an object outside its surrounding frame. This is to prevent unnecessary 'Frequency Errors'.
Flex mode preserves the layout structure while allowing expanding and shrinking of the layout.
FLEX mode, when enabled, allows surrounding frames to grow as an object is resized or moved. Only one object at a time can be moved either vertically or horizontally, not diagonally.
9. What are User Exits?
You build user exits when you want to pass control from Report Builder to a program you have written, which performs some function, and then returns control to Report Builder.
You can write the following types of user exits:
* ORACLE Precompiler user exits
* OCI (ORACLE Call Interface) user exits
* Non-ORACLE user exits.
User exits can perform the following tasks:
* Perform complex data manipulation
* Pass data to Report Builder from operating system text files
* Manipulate LONG RAW data
* Support PL/SQL blocks
* Control real time devices, such as a printer or a robot
You can use user exits for other tasks, such as mathematical processing.
However, it is recommended that you perform such tasks with PL/SQL within Report Builder itself.
Ex: FNDSRWINIT, FNDSRWEXIT.
10. How do I Register a Custom Report?
Step 1: Register a concurrent program executable
Navigate to the Define Executable form (AOL Reference manual pg 9-84)
This determines the type of program being run,ie an Oracle Report. Fill in the executable name, application and execution method. For the Execution File, fill in just the filename. The concurrent manager will look in the appropriate directory under the application's top directory.
For spawned programs, the file must be in the bin directory, for Oracle Reports the rdf file must be in the srw directory.
For PLSQL concurrent programs, put the name of the stored procedure.
Step 2: Define the concurrent program
Navigate to the Define Concurrent Program form (AOL Reference manual pg 9-87)
This form links a concurrent program to the executable you just defined, as well as defines the programs parameters, incompatibilities, and other options.
Enter the concurrent program name, application, short name and description. Check Standard Submission if you want to be able to submit this program from the Standard Report Submission form.
Enter the name of the executable you defined and any report information if necessary. Also define any parameters your program needs here and any incompatibilities.
Step 3: Add the concurrent program to a Report Group
First you will need to find the name of the Report Group to use.
Go to Security->Responsibility and query the responsibility you want to run the program with.
It should show a Report Group name. Query this name in Security->Responsibility->Report
Add your new program to the list of available programs. Now when you go to submit a request with this responsibility, you will be able to submit your custom program.
11. What is a Token?
Token is used to attach a bindvariable to a report parameter while registering the report as concurrent program.
12. What is the use of ‘Send to Back’ and ‘Bring to Front’?
To change the order in which objects are layered on top of each other.
Send to Back to move the object behind all other objects.
Bring to Front to move the object in front of all other objects.
13. If 2nd parameter value is based on 1st parameter then how do u declare it?
Let v2 be the value set definition of 2nd parameter and v1 be the value set definition for the first parameter then
In the value set definition of v2 = value $FLEX$.v1
14. What are Summary Column, Place holder Column, and Formula Column?
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.
A placeholder is a column for which you set the data type and value in PL/SQL that you define. You can set the value of a placeholder column in the following places. A place holder column stores a value which we can refer in the layout.
A formula column performs a user-defined computation on another column(s) data, including placeholder columns. Formula columns should not be used to set values for parameters.
15. How do u hide fields in a Report?
Ans: Using the Format Trigger we can hide the fields.
/* Suppose that you are building a master/detail report
** and, if no detail records are retrieved for a master
** record, you do not want the boilerplate labels to
** appear. To do this, you first create a summary
** column called MYCOUNT with a Function of Count in
** the source group of the master repeating frame.
** In the format trigger for the group frame that
** surrounds the detail repeating frame and its labels,
** you enter the following:
*/
function my_formtrig return BOOLEAN is
begin
if :mycount = 0 then
return (false);
else
return (true);
end if;
end;
16. What kinds of reports u have worked on?
Custom Reports and Standard reports
17. Name Custom Reports and…-------------------------------------
Tell some of reports that you did
18. How many types of Report formats we have?
Custom Reports and Standard reports
19. What is the minimum number of groups required for a Matrix type report?
To create a matrix report, you need at least four groups: one group must be a cross-product group, two of the groups must be within the cross-product group to furnish the "labels," and at least one group must provide the information to fill the cells. The groups can belong to a single query or to multiple queries.
A matrix (cross tab) report contains one row of labels, one column of labels, and information in a grid format that is related to the row and column labels. A distinguishing feature of matrix reports is that the number of columns is not known until the data is fetched from the database.
View the video report builder help
20. What is the difference between Bitmap and Character based reports? Explain in detail.
Bitmap vs. Character-Mode Report Design
Here is an example to help explain how Oracle Reports are designed and printed in both the bitmap and character-mode environments.
Assume you wish to print "Cc" where "C" is a different font and a larger point size than "c" and is in boldface type (where "c" is not).
In Oracle Reports Designer, bitmap mode, you can make "C" bold and in a different font and point size than "c". This is because you are generating postscript output. Postscript is a universal printer language and any postscript printer is able to interpret your different design instructions.
In Oracle Reports Designer, character mode, the APPLICATIONS STANDARDS EQUIRE the report to be designed in ONE FONT/ ONE CHARACTER SIZE. Character mode reports generate ASCII output. In ASCII you cannot dynamically change the font and character size. The standard is in effect so a report prints as identically as possible from both conventional and postscript printers.
Bitmap vs. Character-Mode Report Printing
These sequences contrast the two printing environments. In postscript, "C" can be in a different font and point size than "c". Both or either could also be bold, for example.
In ASCII, "C" must be in the same font and character size as "c". Both or either could also be bold, for example.
Oracle Reports
Designer
----- ar20runb ------ Postscript ---- Postscript
--- "Cc"
executable language printer output
"Cc"---
----- ar20run ----*-- ASCII
--------- Printer ------ "cc"
executable characters output
SRW driver
(for bold, underline,
page break escape sequences)
21. What Printer Styles are used for? Did you develop any printer styles?
Srw.driver
22. How do you fix a performance problem in a Report?
Check Report main query and fine tune it.
Create indexes on columns used in where condition (eliminate full table scan)
Enable Trace(set trace on in before report and set trace off in after report)
Before Report:
srw.do_sql('alter session set sql_trace=true');
After Report:
srw.do_sql('alter session set sql_trace=false');
Trace file will be generated at location:
select value from v$parameter
where name = 'user_dump_dest';
To better see execution plans in a trace file, you need to format the
generated trace file with tkprof statement.
23. What is the significance of p_conc_request_id?
P_conc_request_id is declared as the user parameter for reports which will get org specific data. P_conc_request_id datatype is character and length is 15.
24. How to call a stored procedure in the report? What is the use of that?
Package.prcedure
26. How do you set ORG_ID in a SQL*Plus session?
Call the Below Anonymous pl/sql block.
BEGIN
fnd_client_info.set_org_context(‘204');
END;
Or
exec dbms_application_info.set_client_info(‘org_id’);
27. While registering a report and a pl/sql block we pass some parameters, for any pl/sql block we pass two additional parameters. Can u list them?
p_errorcode and p_errorbuffer as out parameters in main procedure.
It requires 2 IN parameters for a PL/SQL procedure that's registered as a concurrent program in Apps. They are
1. errcode IN VARCHAR2
2. errbuff IN VARCHAR2
28. How we can call from form to form, form to report?
Calling a Form from another Form: FND_EXECUTE(…);
NOTE: The calling and called Forms must be registered with Applications.
Calling a Report from a Form: FND_REQUEST.SUBMIT_REQUEST(…);
NOTE: This method can be used to call any concurrent program.
29. What are logical page and physical page?
In the Runtime Previewer, you can scroll though a single page of report output, page through the entire report, and split the screen to view different sections of the same report concurrently.
A physical page (or panel) is the size of a page that will be output by your printer. A logical page is the size of one page of your actual report (it can be any number of physical pages wide or long). The Runtime Previewer displays the logical pages of your report output, one at a time.
30. Why is ref cursor is used in the reports?
Dynamic refcursor
31. When we create a report we use the tables, there is some difference when we use the multi-org tables and ordinary tables, can u tell the difference?
Set p_conc_request_id for org specific tables.
32. We have 2 different databases, and each system has 2 tables. Know there is a link provided between them. The client want a report to be developed based on the 4 tables that r there in the 2 different databases. The solution must be efficient?
Assume that the two databases be DB1 and DB2. At one time I could connect to only one database say DB1. Now I should able to access the tables in DB2 from DB1. First I create a DBlink in DB1 that access the tables in DB2. Using the DBlink, create snapshots for each of the tables in DB2. Now we can use these Snapshots in query, as if like tables in DB1.
The purpose for creating snapshot is both security and to reduce the network load, for each access of the tables in DB2.
No comments:
Post a Comment