How To Identify Where An Expense Report Is In the Process

You wish to identify where in the Expenses Process an Expense Report is by looking at the EXPENSE_STATUS_CODE in the table ap_expense_report_headers_all

 

The values for the column EXPENSE_STATUS_CODE in the table ap_expense_report_headers_all come from the Payables Lookup Code EXPENSE REPORT STATUS

Code	Lookup Code Meaning
CANCELLED	Cancelled
EMPAPPR	Pending Individual’s Approval
ERROR	Pending System Administrator Action
HOLD_PENDING_RECEIPTS	Hold Pending Receipts
INPROGRESS	In Progress
INVOICED	Ready for Payment
MGRAPPR	Pending Payables Approval
MGRPAYAPPR	Ready for Invoicing
PAID	Paid
PARPAID	Partially Paid
PAYAPPR	Payables Approved
PENDMGR	Pending Manager Approval
PEND_HOLDS_CLEARANCE	Pending Payment Verification
REJECTED	Rejected
RESOLUTN	Pending Your Resolution
RETURNED	Returned
SAVED	Saved
SUBMITTED	Submitted
UNUSED	Unused
WITHDRAWN	Withdrawn

 

 

Table: AP_EXPENSE_REPORT_HEADERS_ALL

Column	Value	Meaning
SOURCE	‘NonValidatedWebExpense’	Expense report transaction has been inserted into the expense report transaction tables but have not passed the Payables Validation Function in the Server Side Validation Process of the Expense Report Workflow process. This transaction is not viewable in the Oracle Payables Enter Expense Reports Window.
	‘WebExpense’	Expense Report transactions has successfully passed the Payables Validation function in the Expense Report Workflow Server Side Validation Process and is viewable in the Oracle Payables Enter Expense Reports Window. Transaction has not completed the Expense Report Workflow yet.
	‘XpenseXpress’	(10.7 – 11.0.2) If Expense Report was created through the Web Interface (identified by the workflow_approved_flag), expense report transaction has successfully completed the Expense Report Workflow process and is ready to be picked up by Oracle Payables Invoice Import program.
	‘SelfService’	(11.0.2+) Expense Report transaction has successfully completed the expense Report Workflow process and is ready to be picked up by Oracle Payables Invoice Import program.
WORKFLOW_APPROVED_FLAG	NULL	If Expense Report transaction is created from the web (check the source), then expense report has been submitted, but not been manager or payables approved. Expense Report transactions not created from the web will have workflow_approved_flag = NULL.
	‘S’	Expense Report transaction has been saved and not yet fully completed or submitted.
	‘I’	Implicit save, in OIE.K/R12 there is an implicit save when navigating from one page to another.
	‘R’	Expense Report transaction has been Manager rejected.
	‘M’	Expense Report transaction has been Manager approved.
	‘P’	Expense Report transaction has been Payables approved.
	‘A’	Expense Report transaction has been ‘Automatically’ approved, i.e. Manager approved but Payables approval not needed because expense report didn’t need payables to check for receipts or policy.
	‘W’	Expense Report has been Withdrawn.
	‘Y’	Expense Report transaction has been both Manager and Payables approved.

Oracle Database – Technical Questions Answers

• What is Log Switch? – The point at which ORACLE ends writing to one online redo log file and begins writing to another is called a log switch.

 

• What is On-line Redo Log? – The On-line Redo Log is a set of tow or more on-line redo files that record all committed changes made to the database. Whenever a transaction is committed, the corresponding redo entries temporarily stores in redo log buffers of the SGA are written to an on-line redo log file by the background process LGWR. The on-line redo log files are used in cyclical fashion.

 

• Which parameter specified in the DEFAULT STORAGE clause of CREATE TABLESPACE cannot be altered after creating the tablespace? – All the default storage parameters defined for the tablespace can be changed using the ALTER TABLESPACE command. When objects are created their INITIAL and MINEXTENS values cannot be changed.

 

• What are the steps involved in Database Startup? – Start an instance, Mount the Database andOpen the Database.

 

• What are the steps involved in Instance Recovery? – Rolling forward to recover data that has not been recorded in data files, yet has been recorded in the on-line redo log, including the contents of rollback segments. Rolling back transactions that have been explicitly rolled back or have not been committed as indicated by the rollback segments regenerated in step a. Releasing any resources (locks) held by transactions in process at the time of the failure. Resolving any pending distributed transactions undergoing a two-phase commit at the time of the instance failure.

 

• Can Full Backup be performed when the database is open? – No.

 

• What are the different modes of mounting a Database with the Parallel Server? – Exclusive Mode If the first instance that mounts a database does so in exclusive mode, only that Instance can mount the database. Parallel Mode If the first instance that mounts a database is started in parallel mode, other instances that are started in parallel mode can also mount the database.
• What are the advantages of operating a database in ARCHIVELOG mode over operating it in NO ARCHIVELOG mode? – Complete database recovery from disk failure is possible only in ARCHIVELOG mode. Online database backup is possible only in ARCHIVELOG mode.

 

• What are the steps involved in Database Shutdown? – Close the Database, Dismount the Database and Shutdown the Instance.

 

• What is Archived Redo Log? – Archived Redo Log consists of Redo Log files that have archived before being reused.

 

• What is Restricted Mode of Instance Startup? – An instance can be started in (or later altered to be in) restricted mode so that when the database is open connections are limited only to those whose user accounts have been granted the RESTRICTED SESSION system privilege.

 

• What is Partial Backup? – A Partial Backup is any operating system backup short of a full backup, taken while the database is open or shut down.

 

• What is Mirrored on-line Redo Log? – A mirrored on-line redo log consists of copies of on-line redo log files physically located on separate disks, changes made to one member of the group are made to all members.

 

• What is Full Backup? – A full backup is an operating system backup of all data files, on- line redo log files and control file that constitute ORACLE database and the parameter.

 

• Can a View based on another View? – Yes.

 

• Can a Tablespace hold objects from different Schemes? – Yes.

 

• Can objects of the same Schema reside in different tablespaces? – Yes.

 

• What is the use of Control File? – When an instance of an ORACLE database is started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed. It is also used in database recovery.

 

• Do View contain Data? – Views do not contain or store data.

 

• What are the Referential actions supported by FOREIGN KEY integrity constraint? – UPDATE and DELETE Restrict – A referential integrity rule that disallows the update or deletion of referenced data. DELETE Cascade – When a referenced row is deleted all associated dependent rows are deleted.

 

• What are the type of Synonyms? – There are two types of Synonyms Private and Public

 

• What is a Redo Log? – The set of Redo Log files YSDATE,UID,USER or USERENV SQL functions, or the pseudo columns LEVEL or ROWNUM.

 

• What is an Index Segment? – Each Index has an Index segment that stores all of its data.

 

• Explain the relationship among Database, Tablespace and Data file.? – Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace

 

• What are the different type of Segments? – Data Segment, Index Segment, Rollback Segment and Temporary Segment.

 

• What are Clusters? – Clusters are groups of one or more tables physically stores together to share common columns and are often used together.

 

• What is an Integrity Constrains? – An integrity constraint is a declarative way to define a business rule for a column of a table.

 

• What is an Index? – An Index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.

 

• What is an Extent? – An Extent is a specific number of contiguous data blocks, obtained in a single allocation, and used to store a specific type of information.

 

• What is a View? – A view is a virtual table. Every view has a Query attached to it. (The Query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)

 

• What is Table? – A table is the basic unit of data storage in an ORACLE database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.

 

• What is a Synonym? – A synonym is an alias for a table, view, sequence or program unit.

 

• What is a Sequence? – A sequence generates a serial list of unique numbers for numerical columns of a database’s tables.

 

• What is a Segment? – A segment is a set of extents allocated for a certain logical structure.

 

• What is schema? – A schema is collection of database objects of a User.

 

• Describe Referential Integrity? – A rule defined on a column (or set of columns) in one table that allows the insert or update of a row only if the value for the column or set of columns (the dependent value) matches a value in a column of a related table (the referenced value). It also specifies the type of data manipulation allowed on referenced data and the action to be performed on dependent data as a result of any action on referenced data.

 

• What is Hash Cluster? – A row is stored in a hash cluster based on the result of applying a hash function to the row’s cluster key value. All rows with the same hash key value are stores together on disk.

 

• What is a Private Synonyms? – A Private Synonyms can be accessed only by the owner.

 

• What is Database Link? – A database link is a named object that describes a “path” from one database to another.

 

• What is a Tablespace? – A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical structures together

 

• What is Rollback Segment? – A Database contains one or more Rollback Segments to temporarily store “undo” information.

 

• What are the Characteristics of Data Files? – A data file can be associated with only one database. Once created a data file can’t change size. One or more data files form a logical unit of database storage called a tablespace.

 

• How to define Data Block size? – A data block size is specified for each ORACLE database when the database is created. A database users and allocated free database space in ORACLE datablocks. Block size is specified in INIT.ORA file and can’t be changed latter.

 

• What does a Control file Contain? – A Control file records the physical structure of the database. It contains the following information. Database Name Names and locations of a database’s files and redolog files. Time stamp of database creation.

 

• What is difference between UNIQUE constraint and PRIMARY KEY constraint? – A column defined as UNIQUE can contain Nulls while a column defined as PRIMARY KEY can’t contain Nulls. 47.What is Index Cluster? – A Cluster with an index on the Cluster Key 48.When does a Transaction end? – When it is committed or Rollbacked.

 

• What is the effect of setting the value “ALL_ROWS” for OPTIMIZER_GOAL parameter of the ALTER SESSION command? – What are the factors that affect OPTIMIZER in choosing an Optimization approach? – Answer The OPTIMIZER_MODE initialization parameter Statistics in the Data Dictionary the OPTIMIZER_GOAL parameter of the ALTER SESSION command hints in the statement.

 

• What is the effect of setting the value “CHOOSE” for OPTIMIZER_GOAL, parameter of the ALTER SESSION Command? – The Optimizer chooses Cost_based approach and optimizes with the goal of best throughput if statistics for atleast one of the tables accessed by the SQL statement exist in the data dictionary. Otherwise the OPTIMIZER chooses RULE_based approach.

 

• What is the function of Optimizer? – The goal of the optimizer is to choose the most efficient way to execute a SQL statement.

 

• What is Execution Plan? – The combinations of the steps the optimizer chooses to execute a statement is called an execution plan.

 

• What are the different approaches used by Optimizer in choosing an execution plan? – Rule-based and Cost-based.

Shell Script Tutorial for Oracle Applications

• Steps to Register Shell Script as a concurrent program
• Sample Shell Script to copy the file from source to destination
• Basic Shell Script Commands

Steps to Register Shell Script as a concurrent program

step 1:
=======
Place the <name>.prog script under the bin directory for your
applications top directory.

For example, call the script KO_DEMO.prog and place it under
$CUSTOM_TOP/bin

step 2:
=======
Make a symbolic link from your script to $FND_TOP/bin/fndcpesr
For example, if the script is called KO_DEMO.prog use this:

 ln -s $FND_TOP/bin/fndcpesr KO_DEMO

This link should be named the same as your script without the
.prog extension.

Put the link for your script in the same directory where the
script is located.

step 3:
=======
Register the concurrent program, using an execution method of
‘Host’. Use the name of your script without the .prog extension
as the name of the executable.

For the example above:
Use KO_DEMO

step 4:
=======
Your script will be passed at least 4 parameters, from $1 to $4.

  $1 = orauser/pwd
  $2 = userid(apps)
  $3 = username,
  $4 = request_id

Any other parameters you define will be passed in as $5 and higher.
Make sure your script returns an exit status also.

Sample Shell Script to copy the file from source to destination

#Note: If you see # in front of any line it means that its a comment line not the actual code
#** ********************************************************************
# Created By : Shivmohan P

# Creation Date : 19-Sep-2008
# Script Name : KO_demo.prog
# Description : This Script accepts three parameters
# 1)Data File Name 2)Source Directory Path 3)Target Directory Path
# Then copy the file from source location to target location.
# If copy fails send the error status/message to concurrent program so that user can see status.
#
#
# ========
# History
# ========
# Version 1 shivmohan 19-sep-2008 Created for knoworacle.com users
#
#** ********************************************************************
#Parameters from 1 to 4 i.e $1 $2 $3 $4 are standard parameters
# $1 : username/password of the database
# $2 : userid
# $3 : USERNAME
# $4 : Concurrent Request ID
DataFileName=$5  
SourceDirectory=$6
TargetDirectory=$7
echo “————————————————–”
echo “Parameters received from concurrent program ..”
echo ” Time : “`date`
echo “————————————————–”
echo “Arguments : “
echo ” Data File Name : “${DataFileName}
echo ” SourceDirectory : “${SourceDirectory}
echo ” TargetDirectory : “${TargetDirectory}
echo “————————————————–”
echo ” Copying the file from source directory to target directory…”
cp ${SourceDirectory}/${DataFileName} ${TargetDirectory}
if [ $? -ne 0 ]
# the $? will contain the result of previously executed statement.
#It will be 0 if success and 1 if fail in many cases
# -ne represents not “equal to”
then
echo “Entered Exception”
exit 1
# exit 1 represents concurrent program status. 1 for error, 2 for warning 0 for success
else
echo “File Successfully copied from source to destination”
exit 0
fi
echo “****************************************************************”

Basic Shell Script Commands

# Create Directory
mkdir <dirname>
# Remove Directory
rmdir <dirname>
#remove folder with files
rm -r -f <dirname>
# Change Directory
cd <newpath>
# Create new file
vi <newfile.ext>
#insert data into file
vi <openfilename.ext>
esc i <make changes>
#Save file
esc :wq enter
# exit with out saving changes
esc :q! enter
# open existing file
vi <existingfilename.ext>
#remove file
rm <filename.ext>
# copy file with same name
cp <sourcedir>/<sourcefilename.ext> <destinationdir>
# copy file with new name
cp <sourcedir>/<sourcefilename.ext> <destinationdir>/<newfilename.ext>
# Move file with same name
mv <sourcedir>/<sourcefilename.ext> <destinationdir>
# move file with data appended to filename in the front
mv <sourcedir>/<sourcefilename.ext> <destinationdir>/`date+%H%M%d%m%y`<filename.ext>
#print line
echo “your text here to print”
#print date
echo `date`

Interview Questions for Oracle 11i Apps (Manufacturing)

Hello Friends, here some of Inventory Questions , mostly in context on Item Setup. It has been long time i work on inventory module, it used to be one of my favorites and strongest modules sometime. still inventory bring lot of good challenging work. hope you will benefit from these Q & A.

 

Q. Once an item is assigned to an organization, is it possible to remove this association at a later time?
A. If you have assigned an item to an organization, it can only be deleted from that organization if the item has NOT been transacted. If it has been transacted against you cannot delete the item from the organization. You can, however, make the status of the item “inactive” on the main region of the Item Define form INVIDITM in ‘Item Status’ field. This will prevent users from transacting against the item although the item will continue to be included in LOV’s. 

Q. How do you setup Unit of Measure for an item?
A. When transacting an item, it will always default to the defined Primary Unit of Measure, so if you need to use another UOM, you must select it from the List of Values when you transact it. This implies that you want the Primary Unit of Measure to be that which is used most often and which is likely to be the smallest unit of that type of item. Once you have set the primary UOM for an item, it CANNOT be changed.

 

Q. How do I change an item’s locator control method once I have transacted against it, for example from No Control to Prespecified?
A. You cannot change this attribute if there is onhand quantity for the item. You must first perform a miscellaneous issue of all quantity. Then the attribute can be changed and once the locator(s) are set up, you can perform a miscellaneous receipt to receive the quantity back into a specified subinventory/locator.

 

Q. How do I change an item’s Unit of Measure?
A. You CANNOT change the UOM for an item once the UOM is assigned to the item. The suggested solution is as follows: If the item has not been transacted you can delete the item and then re-add it with the correct UOM. If the item has been transacted and has no outstanding orders, purchase orders or WIP jobs you can do the following if approved by your management.
1. Rename the existing item ‘123′ to ‘123-Bad UOM’ and save the item. Any prior history for item ‘123′ will now be associated with item ‘123-Bad UOM’.
2. Add a new item ‘123′ with the correct UOM.

 

Q. How do I delete an Item Template I no longer use?
A. Suggested steps to follow to delete unwanted Item Templates:
1. Navigate Setup: Items: Templates – Find Item Templates form appears.
2. Click on the LOV, and select the Item Template that is to be removed.
3. Click the [Find] button on the Find Item Templates form. The Item Templates Summary form appears, with the Item Template selected.
4. Click the [Open] button on the Item Templates Summary form, and the Item Templates form appears.
5. From the menu, select Edit | Delete Record, then select Action | Save and Proceed. 6. Close the Item Templates Summary form. Be careful not to delete Item Templates you may need again as they are not retrievable once deleted.

 

Q. Is there a way to create custom item attributes?
A. No but you can create and define the descriptive flexfield for this form. This will allows you to associate additional data with an item such as an attribute. See the Oracle System Administrators
Users Guide and the Oracle Flexfields Manual for information on creating Descriptive Flexfields.

 

Q. Why does the application prompt you for Organization when you enter the Master Item form for the very first time even though you have only one Master Organization.
A. Once you setup a Multi-Org environment and you enter the form for the first time, there is the possibility there can be more than one Master Organization. The application
therefore prompts you for an organization which is then used to determine the Master Organization associated with it. In other words, you are choosing the Master Organization by choosing one of the Orgs that belongs to it.

 

Q. How do you change the control level for item attributes from master level to organization level for example. When I try to change this in the Attribute Control Form, I get the message: frm-40200: field protected against update.
A. First change value of the field called “status” to “not used”. You should then be able to change the control level of the attribute from master level to org level. Make sure and change to status back to what it was before the change and save the record.

 

Q. How do I inactivate an item?
A. On the Master Item Form (Inventory->Items->Master Items), change the item status field to “Inactive” or any another defined status which disables all attributes that make an item usable (stockable, shippable, orderable, etc.). The “Inactivate” status comes pre-seeded with the Application and is for this purpose.

Q. What characters can I use in an item name (including special characters)?
A. The only hard fast rule is not to use the character designated in the segment delimiter for the System Item Flexfield. Also, you will not be able to enter any characters not allowed by the valueset associate with the flexfield. It is recommended that you avoid the use of any char that can be used as a delimiter to avoid future problems. It is best to not use special characters like #@%& if at all possible as these may have a negative impact down the road. Some characters, like percent (%), underscore (_), and pound(#), have special meaning when performing queries by form as do the query operator characters (=!><).

 

Q. Can you change the costing enabled and inventory asset flags on an item that has already been transacted against even if there is no on-hand quantities for the item?
A. Yes, you can change the flags. If you are changing the flags from checked to unchecked you will receive a warning that the associated cost records will be deleted alerting you that the Cost of Goods will not be calculated any longer for transactions of this item. In effect what you are doing is changing the item from an asset item to an expense item. If you are changing the flags from being unchecked to checked then a database
trigger will fire and cause cost records to be added for this item. There is no warning that is given.

 

Q. When I create a new item, and choose to assign to All orgs, the application allows me to even though some of those organizations are restricted to my responsibility. Is this the way it is supposed to work? If I assign in an Organization by Organization fashion, I am not allowed to assign to the restricted organizations.
A. The situation described is standard functionality. Even if a responsibility has limited access to a certain number of organizations, the “Assign to All Organizations” check box will assign the item to all orgs, not just the ones limited to the user’s responsibility.

 

Q. How do you tell that an item is an expense item or asset item?
A. Navigate to the Organization Items form: Inventory->Items->Organization Items. Query up the item in question. Change to the “Costing” alternative region. The value of the “Inventory Asset Value” flag determines this. If checked, the item is an asset item.

 

Q. Is there a way to delete or disable an item revision once entered?
A. There is no way to delete a revision nor is there a way to disable it. It must remain there for historical data. The suggested workaround is to create a new item, transfer any Quantity on-hand from the old item to new item and delete the old item. The only down side is you will lose some transaction history.

For more interview Question click here :  http://knoworacle.wordpress.com/2008/06/26/oracle-applications-technical-interview-questions/

 Thanks – Shivmohan Purohit

 

How to find invalid invoice distributions for a ‘DIST ACCT INVALID’ hold

How to find invalid invoice distributions for a ‘DIST ACCT INVALID’ hold

A ‘DIST ACCT INVALID’ hold is being placed on an invoice because the invoice distribution is invalid. The Invoice on Hold Report shows the invoice has the hold ‘DIST ACCT INVALID’. If you run the following SQL, it will give the INVALID distributions on the invoice.

 SELECT invoice_distribution_id,distribution_line_number

FROM ap_invoice_distributions D

WHERE D.invoice_id = <invoice_id>for which the distribution is

INVALID

AND D.posted_flag||” in (‘N’, ‘P’)

AND ((EXISTS (select ‘x’

from gl_code_combinations C

where D.dist_code_combination_id = C.code_combination_id (+)

and (C.code_combination_id is null

or C.detail_posting_allowed_flag = ‘N’

or C.start_date_active > D.accounting_date

or C.end_date_active < D.accounting_date

or C.template_id is not null

or C.enabled_flag <> ‘Y’

or C.summary_flag <> ‘N’

)))

OR

(D.dist_code_combination_id = -1))

Overview – Oracle Projects

Last few days I have been involved with the Oracle Projects module. I’ll try and put forth some basic learning of this module.

Oracle Projects is meant primarily for organizations that are project-oriented. Using this module, it becomes easy to track costs, budget and track the project status.

 

Oracle Projects consists of the following products:

• Oracle Project Costing

• Oracle Project Billing

• Oracle Project Connect for Microsoft Project

• Oracle Activity Management Gateway

• Oracle Project Analysis Collection Pack

 

Prior to Oracle Projects Setup, one has to setup the Set of Books in GL, setup Organization and Organization hierarchy in Oracle HRMS, define employees and job in HRMS and create customer in Oracle Receivables. However if Oracle Projects is being installed as a standalone package then one needs to define all above in Oracle Projects itself. Some other mandatory setups include defining locations, defining implementation options, defining Project Accounting periods, defining expenditure types and categories, define revenue categories, etc. Also one has to create a burdening hierarchy in Oracle HRMS which may vary from the Project or Task Organization hierarchy.

 

Invariably a Project is broken into a hierarchy of tasks as per Work Breakdown Structure (WBS, a hierarchy of tasks that rollup into a project) to manage project and task related information. One can define as many levels of tasks in a hierarchy in Oracle Projects. However, proper naming conventions need to be followed while naming projects, tasks and sub-tasks. An organization has to be associated with a project and a Task, which may be same or different.

 

One can have three different Project Types for managing the cost of a project: Indirect, Capital and Contract. An Indirect Project Type is used to track the overhead costs and labor hours for overhead activities like Admin, Legal, etc. Capital Project Type is selected to track costs and labor hours related to asset development activities which ultimately results in an asset for the organization. A Contract Project Type is selected in case the costs are reimbursed by a client.

 

Oracle Costing is the used for processing of expenditures for finding out costs which can be attributed to projects and tasks which can then be posted to GL corresponding to different account lines. There are two cost amounts associated to each transaction: Raw and Burdened. The raw cost is the actual cost of the work performed, and the burden cost is the indirect cost or overhead of work performed, like administrative cost. The Burden cost is calculated by multiplying Raw Cost with the Burden Multiplier. The burdened cost is the total cost that is incurred, i.e., the sum of raw cost and burden cost.

Burden Cost = Raw Cost x Burden Multiplier

Burdened Cost = Raw Cost + Burden Cost

More on Oracle Projects, primarily I’ll try and provide inputs on Burdening Cost and Oracle Project Billing in subsequent days.

Understanding Oracle SQL*Loader

SQL*Loader is an Oracle utility that enables you to efficiently load large amounts of data into a database. If you have data in a flat file, such as a comma-delimited text file, and you need to get that data into an Oracle database, SQL*Loader is the tool to use. This chapter introduces you to the SQL*Loader utility, discusses its control file, provides the syntax for using the SQL*Loader command, and provides examples of using SQL*Loader to load data into databases.

 

Using SQL*Loader, you can do the following:

Ø       Load data from a delimited text file, such as a comma delimited file

Ø       Load data from a fixed-width text file

Ø       Load data from a binary file

Ø       Combine multiple input records into one logical record

Ø       Store data from one logical record into one table or into several tables

Ø       Write SQL expressions to validate and transform data as it is being read from a file

Ø       Combine data from multiple data files into one

Ø       Filter the data in the input file, loading only selected records

Ø       Collect bad records—that is, those records that won’t load—into a separate file where you can fix them

 

Control Files

Control files, contain a number of commands and clauses describing the data that SQL*Loader is reading. Control files also tell SQL*Loader where to store that data, and they can define validation expressions for the data. Understanding control file syntax is crucial to using SQL*Loader effectively. The control file is aptly named, because it controls almost every aspect of how SQL*Loader operates. The control file describes the format of the data in the input file and tells SQL*Loader which tables and columns to populate with that data.

 

Understanding the SQL*Loader Commands

The syntax for

the SQL*Loader command looks like this:   sqlldr [param=value[, param=value...]]

 

SQL*Loader Parameters

Parameter Description

userid :   Passes in your username, password, and Net8 service name. The syntax to use is the same as for any other command-line utility, and looks like this: userid=username[/password][@service]

 

control: Passes in the control file name. Here’s an example: control=[path]filename[.ext] The default extension for control files is .ctl.

 

log:  Passes in the log file name. Here’s an example: log=[path]filename[.ext] The default extension used for log files is .log. If you don’t supply a file name, the log file will be named to match the control file.

 

bad Passes in the bad file name. Here’s an example: bad=[path]filename[.ext] The default extension for bad files is .bad. If you don’t supply a file name, the bad file will be named to match the control file. Using this parameter overrides any file name that may be specified in the control file.

 

data Passes in the data file name. Here’s an example: data=[path]filename[.ext] The default extension used for data files is .dat. Specifying a data file name on the command line overrides the name specified in the  control file. If no data file name is specified anywhere, it defaults to the same name as the control file, but with the .dat extension.

 

discard Passes in the discard file name. Here’s an example: discard=[path]filename[.ext]

The default extension used for discard files is .dis. If you don’t supply a file name, the discard file will be named to match the control file. Using this parameter overrides any discard file name that may be specified in the control file.

 

discardmax Optionally places a limit on the number of discarded records that will

be allowed. The syntax looks like this: discardmax=number_of_records If the number of discarded records exceeds this limit, the load is aborted.

 

skip Allows you to skip a specified number of logical records. The syntax looks like this:

skip=number_of_records Use the skip parameter when you want to continue a load that has

been aborted and when you know how far into the file you want to go before you restart.

load Optionally places a limit on the number of logical records to load into the database. The syntax looks like this:

 

load=number_of_records Once the specified limit has been reached, SQL*Loader will stop.

 

Errors Specifies the number of errors to allow before SQL*Loader aborts the load. The syntax looks like this: errors=number_of_records SQL*Loader will stop the load if more than the specified number of errors has been received. The default limit is 50. There is no way to allow an unlimited number. The best you can do is to specify a very high value, such as 999999999.

 

Rows indirectly controls how often commits occur during the load process. The rows parameter specifies the size of the bind array used for conventional-path loads in terms of rows. SQL*Loader will round that value off to be some multiple of the I/O block size. The syntax for the rows parameter looks like this: rows=number_of_rows The default value is 64 for conventional-path loads. Direct-path loads, by default, are saved only when the entire load is done. However, when a direct-path load is done, this parameter can be used to control the commit frequency directly.

4. Run the applypreferences script. 

 

Ø       SQL*Loader is a versatile utility for loading large amounts of data into an Oracle database.

Ø       SQL*Loader control files are used to describe the data being loaded and to specify the table(s) into which that data is stored.

Ø       You can use the INFILE clause to identify the file, or files, that you want SQL*Loader to read.

Ø       You can use the INTO TABLE clause to identify the table, and the columns within that table that you wish to populate using the data read from the input file.

Ø       You can use the APPEND option after the INFILE clause to tell SQL*Loader to insert data into a table that already contains data to begin with.

Ø       You can use SQL*Loader to load delimited data, such as comma-delimited data, or you can use it to load data stored in fixed-width columns.

Ø       SQL*Loader fully supports all of Oracle8i’s datatypes, even to the point of allowing you to populate LOB columns.

 

Thanks Shivmohan Purohit

 

Oracle General Ledger – Functional Interview Questions

Oracle General  Ledger – Functional Interview Questions

 Hello Friends, As a series to get improve on Financials , here some more General Ledger Questions, I consider these as functional and advance questions.

Can a flexfield qualifier be changed after it has been created?

No. Once a segment qualifier has been designated for a specific segment and has been saved, it will permanently have the attributes with that qualifier.

For example, you accidentally designate the cost center segment as the natural account segment. Even though you do not compile this, the system saves the changes. And once it has been saved, it will have all the attributes designated for the natural account qualifier, even after it has been changed back, resaved with the correct qualifier and compiled. This is the inherent functionality of the software.

Unfortunately, there is no real easy solution for this issue. The only option is to create a new chart of accounts and attach a new set of books.

 

 

 

 

Does the Accounting Flexfield needs one segment flagged with the Intercompany qualifier?

The intercompany segment is an optional Intercompany feature for the Intercompany Segment Balancing.

It is not required in order to do intercompany balancing.

It is just another way to do the intercompany balancing, instead of using different natural accounts to track intercompany balances, you can use the intercompany segment in the Chart of Accounts to record the same detail.

It is more just a matter of preference of how you want to track the intercompany transactions.

 

 

 

 

 

Can different Sets of Books share the same Value Set?

If there is a set of books that uses a particular value set and a new book is created, it can use the existing value set.

When the Account Flexfield Structure is created, simply choose the existing value set from the List of Values.

However,if you use an existing value set, the new structure will also inherit the existing values in the value set. It does not create an empty value set to be populated.

 

 

 

 

Can the Accounting Flexfield Segment name be changed?

Nothing about a flexfield structure should be changed since this could seriously impact your existing data and cause inconsistencies

The LOV (list of values) prompt and window prompt that appear on the segment form can be changed so the users can see the segment with a different name.

1.      Navigate to Setup: Financial: Flexfield: key: segments

2.      Query all

3.      Unfreeze your flexfield

4.      Open the segment summary screen

5.      Choose the segment you wish to change

6.      Change the LOV and window Prompts and insert the new name.

7.      Save your work

8.      Freeze the Flexfield which should launch a concurrent request.

9.      Check the log and make sure the compile worked OK.

 

How to add a new value to an existing segment?

On the Segment Values form, insert a new row with your new value.

Make sure the segment qualifiers are set correctly (Allow Posting and Budgeting).

If this is the natural account segment, specify the Account Type also.

 

 

 

 

 

How to delete a segment value?

 There is no supported way to delete a segment value. Segment values can only be disabled not deleted.

 

How to enable an existing Future-Use accounting flexfield segment?

 Do the following, on the Key Flexfield Segments form:

1. Unfreeze the accounting flexfield
2. Change the Window Prompt name to a valid name (do NOT change the segment name)
3. Update the Default Type and Default Value (if necessary)
4. Freeze the accounting flexfield and recompile

How to re-enable an account value?

 In order to re-enable an account value or remove an end date, do the following: 

1. Unfreeze the accounting flexfield structure
2. Re-enable the account value or remove the end date of the segment value.
3. Re-freeze the accounting flexfield structure and recompile.
4. Log out of the applications and log back in.

 

Is there a way to load values for a specific segment outside of the form?

 

iSetup is the Oracle product that provides supported APIs to load values into Oracle Applications flexfields.

To load code combinations ADI may be used. Uploading zero amount journals will create new code combinations.

In this case Dynamic Insertion should be enabled and all account segment values need to exist before the new account code combinations will be dynamically created.

What is the difference between Hierarchical and Non-hierarchical Security Type?

 

 

 

• Hierarchical Security: This feature combines Flex Value Security and Flex Value Hierarchy.
  The end result is ?a flex value is secured if one of it?s parents is secured?, i.e. if a parent value is excluded then all the children are also excluded.

 

 

With non-hierarchical security, the child values do not inherit the parent security.

 

Which tables store segment values and descriptions? 

• FND_FLEX_VALUES_TL
• FND_FLEX_VALUES.

 

Thanks - Shivmohan Purohit 

 

 

 

Oracle Projects – Revenue Generations and Credit Memo Questions

Why does the Invoice Class show as Invoice instead of Credit Memo when an invoice reduction event is created?
A:  This is the intended functionality.  In the case of Invoice Reduction Events, there is no specific invoice to credit, therefore they are created with the class of Invoice instead of Credit Memo.

What accounting is performed when an invoice write-off is initiated in Projects?

A:   The Revenue and Invoice Accounts function is called by AutoAccounting when the credit memo type is a Write-Off.  The accounting that is performed is a credit to the Receivable Account and a Debit to the Expense Account that has been set up for Write-Offs in the AutoAccounting setup. Revenue would not be affected by a Write-Off.  The expense would offset the revenue in the income statement.

 What happens when an invoice is canceled?

 

A:  When an invoice is canceled, the transactions that make up the invoice are returned to an unbilled status and made available for rebilling during the next billing cycle.  After an invoice is canceled, the PRC: Generate Draft Invoice process creating the Credit Memo replaces the columns      PA_CUST_REV_DIST_LINES_ALL.DRAFT_INVOICE_NUM and PA_CUST_REV_DIST_LINES.DRAFT_INVOICE_LINE_NUM with NULLs for the expenditure items being credited.  This results in the “missing” details in the original invoice and the credit memo.

How does Oracle Projects handle on account credit memos?

A:  From a Projects perspective, the only way to manage an “on account” credit memo is to create the independent crediting invoice using a negative manual event.When the time comes to apply the credit to the proper invoice, create a positive event (debit memo) to offset the negative invoice then create a standard credit memo against the appropriate invoice.

 

How are partial write-offs handled when the invoice has tax?

 A:  When you do a partial write-off in Projects, it credits a small portion of each invoice line. If you need to write-off a particular line of a Projects invoice, a partial write-off should be done in Projects and interfaced to Receivables so that the two modules are in synch.  From Receivables, incomplete the invoice and adjust it.  Using this method will ensure that the modules stay in sync and only the lines that you want to write-off will be written off.

 

Under what circumstances would I delete an invoice rather than canceling it?

 A:  An invoice can only be deleted if it has not been released. Once the invoice is released, it would need to be canceled to create an offsetting credit memo. Deletions do not create credit memos.

 

Is it possible to apply a credit memo to an invoice that has already been paid in Receivables?

A:  If the Invoice Type in Receivables has Overapplication = Yes, you should be able to interface the credit to Receivables.  If you do not wish to set Overapplication to Yes, you can unapply the cash receipt in Receivables and refund it to the customer or apply it to another invoice.

 

Why does the Generate Draft Invoices for a Range of Projects process automatically release revenue?

A:  When revenue is generated for a range of projects, revenue items are given a status of Released; when it is generated for a single project, the status assigned is Pending.  Because the status is released for revenue generated for a range, Projects automatically releases this revenue when the Interface Revenue to General Ledger (GL) process is run.  If there is a need to manually release revenue, you will have to run the PRC:  Generate Draft Revenue for a Single Project.  When ready, the revenue can be released manually from the Revenue Review window.

Why is revenue automatically released when releasing some invoices?

A:  When you release an invoice which is based on revenue details (such as a Time & Materials (T&M) invoice), Oracle Projects automatically releases the associated revenue.

Why is the project level bill rate schedule not recognized when generating draft revenue?

A:  Revenue is generated based on the Bill Rate Schedule saved at the task level.  Entering the Employee Bill Rate Schedule at project level is only allowed for default purposes; not for calculations.

What are the possible values for TRANSFER_STATUS_CODE in the PA_DRAFT_REVENUES_ALL table?

A:  When revenue is successfully generated, the status is set to P (Pending).  After running the Interface Revenue to General Ledger process, the status is set to A (Accepted) for successfully transferred revenue or X (Rejected in Transfer) for revenue that is rejected.  When the Tieback Revenue From General Ledger process is run, there will be no change to the A (Accepted) items but those items rejected by the GL will be set to R (Rejected) and will require correction and re-interface.

Is it possible to unrelease revenue?

A:  It is only possible to unrelease revenue if none of the following actions have been performed:

1. Released draft invoices for this draft revenue
2. Subsequently generated draft revenue
3. Summarized draft revenue for the project

Is it possible to generate partial revenue when a Hard Limit exists on an Agreement?

A:  No.  Projects accrues revenue for an event only if enough funding is available to accrue the full event amount. You can, however, accrue expenditure item revenue up to hard revenue limits by partially accruing the potential revenue.  The expenditure items are marked as partially accrued; they can be fully accrued by adding more funding prior to the next revenue generation run.

How to Review and Adjust Oracle Project Invoices

How to Review and Adjust Oracle Project Invoices
—————————–

Reviewing Invoices

You should review each invoice before you approve and release it for
billing. Use the Invoice Summary window or the Invoice Review
report to review invoices.
You can review invoice information such as:

- invoice amount
- invoice lines
- currency attributes
- expenditure items that back up invoice items
- invoice customer

In addition to reviewing invoice information, you should also review
an invoice to ensure that it did not encounter any generation errors or
distribution warnings during generation and to monitor the status of
your invoices. If you encounter a draft invoice with a generation error,
you should correct the error and regenerate the invoice.

Invoice Review Windows

The Invoice Summary, Invoice Lines, and Invoice Line Details windows
are folder-type windows. In these windows, many of the fields,
including the currency fields, are not displayed in the default folder.
You may want to create folders that display the fields you need, for the
types of entries you need to make.

To review invoices:

1. Navigate to the Find Invoices window.
2. Enter selection criteria and choose Find to view invoices in the
Invoice Summary window.

To review detailed invoice information:

In the Invoice Summary window, select an invoice and choose the
Open button to review detailed invoice information.

Adjusting Project Invoices

You can adjust anything on draft invoices before you approve and
release them. You can perform many of the same actions on an invoice
that you can on project expenditures.

If you perform an adjustment on an invoice?s expenditure
items and events, you need to submit the appropriate process
to reflect those adjustments. For example, if you change an
expenditure item?s status from billable to non?billable, you
need to submit the appropriate processes to recalculate cost,
regenerate revenue, and regenerate the invoice.

When you regenerate a project?s unreleased draft revenue and
unreleased draft invoices, Oracle Projects deletes the project?s
unreleased draft revenue and invoices and creates new draft revenue
and invoices.

To adjust an invoice:

1. Navigate to the Invoice Review window.
2. Find the invoice you want to adjust.
3. Choose the Open, Lines, and Details buttons to open the Invoice
Line Details window.
4. From the Tools menu, choose the adjustment you want to make.
5. Choose Run Request from the Invoice window to process the
adjustment.

To split an invoice or invoice line:

Choose Split from the Tools menu for the invoice or line you want
to split. In the Split Invoice (Line) window, enter the Split Quantity
you want to allocate to the first invoice or line, and choose OK to
mark the invoice or line for a split adjustment.
Distribute costs, and generate draft invoice to process the
adjustment.

To transfer an invoice or invoice line:

Choose Transfer from the Tools menu for the invoice or line you
want to transfer. In the Transfer Invoice (Line) window, enter the
Project Number and Task Number to which you want to transfer
the invoice or line, and choose OK to mark it for transfer.
Distribute costs, and generate draft invoice to process the
adjustment.

To cancel a released invoice:

You cannot delete a cancellation (credit memo) once Oracle
Projects performs the cancellation.

1. Choose the Credit button in either the Invoice Summary or Invoice
window.
2. Choose Cancel Invoices.
3. When you choose OK, Oracle Projects submits a process to create a
credit invoice.

To write off an invoice:

You can write off an invoice only if it has a status of Accepted.
1. Choose the Credit button from either the Invoice Summary or
Invoice window.
2. Enter the write off amount for the invoice.
3. Choose OK. Oracle Projects submits a process to write off the
invoice.

To create an independent crediting invoice:

1. Find the project for which you want to create the credit invoice in
the Projects, Templates Summary window.
2. Open the project and select the Events option under Billing
Information.
3. Enter an Invoice Reduction type event for the project or top task, as
appropriate based on your invoice format.
4. Save. When the Generate Draft Invoices process is run for your
project, a credit invoice will be created.

To delete an unreleased draft invoice:

1. Find the invoice you want in the Invoice Summary window.
2. Choose Run Request.
3. Click on the Delete Invoices option button.
4. Choose OK.