, , , ,

SQL*Loader Basics

SQL*Loader Basics

SQL*Loader is an Oracle-supplied utility that allows you to load data from a flat file into one or more database tables. It has a powerful data parsing engine that supports data present in any format in the data files. It can load data from multiple datafiles during the same load session as well as can load data into multiple tables during the same load session. SQL*Loader can manipulate the data before loading it, using SQL functions.

SQL*Loader’s Capabilities:

SQL*Loader can read from multiple input files in a single load session.
SQL*Loader can handle files with fixed-length records, variable-length records, and stream-oriented data.
SQL*Loader supports a number of different datatypes, including text, numeric, zoned decimal, packed decimal, and various machine-specific binary types.
Not only can SQL*Loader read from multiple input files, but it can load that data into several different database tables, all in the same load session.
SQL*Loader allows you to use Oracle’s built-in SQL functions to manipulate the data being read from the input file.
SQL*Loader includes functionality for dealing with whitespace, delimiters, and null data.
In addition to standard relational tables, SQL*Loader can load data into object tables, varying arrays (VARRAYs), and nested tables.
SQL*Loader can load data into large object (LOB) columns.
SQL*Loader can handle character set translation between the input data file and the database.

/by
, , ,

Steps to create a XML Publisher Report

1] Add the “Xml Publisher Administrator” Responsibility to the user through the front end.

2] Create the Report(Data Model or we can say the .Rdf file) using Oracle Report Builder.

3] Set the user parameter as p_conc_request_id.

4] Add the default values to the Before Report and After Report triggers(not mandatory)

5] Ftp the Report to the Cust_Top/Report/Us.

6] Open the Oracle E-Business Suite then go to Sysadmin>Concurrent>Program>Executables, Here we have to create one executable file for that Rdf.

7] Then go to Sysadmin>Concurrent>Program>Define, Here we have to make a Concurrent Program for that Executable. Make sure that the output format must be XML.

8] Goto the Sysadmin>Security>Responsibility>Define. Query for the Xml Publisher Administrator. See the Request Group attached to this. Attach the Concurrent Program to this Request Group.

9] Design the template in Ms Word(Using the .Rtf file).

10] Goto responsibility XML PUBLISHER ADMINISTRATOR. Then Goto HOME>DATA DEFINITION>CREATE DATA DEFINITION and create a new data definition. Make sure that your Data Definition’s Code should be same as Concurrent Program’s Short Name used by you to create the Rdf file.

11] Now go to Xml publisher administrator>Home>Template. Create a new template with template type=’Rtf’. Then upload the RTF File by browsing the path.

12] Now go to the Responsibility and run the request.

/by
, , ,

When to enter a Value in to a Value Set ???

Everyone who has an experience of setting up a Set of Books would have come across the 3 C’s, Calendar, Currency and Chart of Accounts. For creating Chart of Accounts we will create segments and value sets for each of the segments to hold value. Then we will enter the values for each and every value set.

The normal order/steps involved in creating a Chart of Account is:
1) Create Value Sets First
2) Create Structure and assign the value sets created
3) Assign Flexfield Qualifiers
4) Compile the Flexfield
5) Enter Values in to Value sets
6) Define Segment Qualifiers

Why the above order becomes standard, who suggested it, does oracle recommend this order ?
All materials and books says that Value sets are the container of values, one can create a value set then enter values and then attach to the Structure.

A question got raised in my mind as to why one must enters the value in to the value set after the flexfield is compiled, why not we can enter values in to value set as a second step, that is, as soon as we create a Value set ?
Have asked few who were on the field, they said there is no difference , you can enter values as and when you please.

But later one fine day, have found the logic why it was done like this way.

The logic is….

Every one knows that every value in a value set will have a Segment Qualifier.
Standard segment Qualifiers for all the segments will be ” Allow Posting” and ” Allow Budgeting” and for Natural Account segment the additional qualifiers will be ” Account Type” , “Control Account” and “Reconciliation Flag”.

How the system identifies that whether a segment is an normal segment or a natural accounting segment ?
System will identify it only based on the Flexfield Qualifier attached to that segment. There is no setup that is related to the value set alone for this identification.

“Which means that a Value set will show those segment qualifiers only when that value set is assigned to a Segment which holds natural account flexfield qualifier”

When we try to enter a Value for a value set which is created for holding accounting segment values, without creating the structure, the segment qualifiers will show only “Allow Posting and Allow Budgeting ” qualifiers and not the others, since there is no way that system can assume that this value set is created for an Accounting Purpose.

So the accepted methodology is to enter the value in to value sets after it has got assigned to a Structure.

/by
, , ,

Auto Invoice

Auto Invoice??
Auto Invoice is a tool that can be used to import and validate transaction data from other financial systems from which one can create invoices, debit memos, credit memos, and on-account credits. It rejects transactions with invalid information to insure the integrity of the data. This fits well with in Oracle ERP or to integrate with any third party application.

What Module data can be integrated?

Oracle Order Management
Oracle Project Accounting
Oracle services

To make fully functional what else required?
Loader program
Validation program

Top 10 reasons for using Auto Invoice
1. Powerful Interface Tool
2. Supports Oracle & Non-Oracle Systems
3. Import Large Amount of Data
4. Calculate or Import Tax
5. Group Lines & Invoices
6. Online Error Correction
7 .Lines Validation
8. Derive GL Date
9 .Import Flex fields
10.Import or Derive Accounting Info

What is inside AutoInvoice?

AutoInvoice is a tool consists of 3 main programs. Each program will have unique nature of work to do and they are called internally except Purge program whose execution is derived on the setup otherwise ready to execute stand alone.

Master (RAXMTR)
Import (RAXTRX)
Purge (RAXDEL)

1. Auto Invoice Master program RAXMTR

Selects and marks records in the interface tables to be processed based on the parameters the user entered and then calls the AutoInvoice Import program. Auto Invoice Master program has no report output.

•Gathers statistics, it means it gathers the stats on interface tables and set the stats on certain indices on interface tables
•Marks interface records for processing by marking request_id
•Submits multiple workers for Parallel Processing by creating instances for request.

2. Auto Invoice Import Program Validates the selected record and creates transaction if it passes validation. Any record that fails validation is left in the interface table with an error code. Depending on the setup, related records may be rejected as well. This program has an output file called Auto Invoice Execution report, which you can view by clicking the View Report button in the Requests window.

Working of Auto invoice 
Validates data
Inserts records
Deletes interface data
Only when system option purge set to ‘Y’

3. Auto Invoice Purge Program Deletes records from the interface tables. If you set the Purge Interface Table system option to No in Define System Option window, Auto Invoice does not delete processed records from the interface tables after each run,and we must submit Auto Invoice Purge Program periodically to clean up the interface tables. This program only deletes transaction lines that have been successfully imported.

•Deletes all rows where interface_status =‘P’

•Ra_interface_lines

•Ra_interface_distributions

•Ra_interface_salescredits

Oracle Receivable’s Auto Invoice program will be used to import and validate Invoices.

A custom feeder program is required to transfer data from the Advantage extract files and populate the Auto Invoice interface tables (RA_INTERFACE_LINES_ALL and RA_INTERFACE_DISTRIBUTIONS_ALL).If there is need to run populate sales credit into RA_INTERFACE_SALESCREDITS_ALL table.

When run, AutoInvoice produces the AutoInvoice Execution Report and the AutoInvoice Validation Report.
Any entries which failed validation can be reviewed in Oracle Receivables’ AutoInvoice Interface Exceptions window. Depending on the error, changes may need to be made in Receivables, the feeder program or the imported records in the interface tables.

How Autoinvoice Execution works
Normally, Auto Invoice can be divided into three major phases

Pre-grouping: here the validates all of the line level data takes place
Grouping: groups lines based on the grouping rules and validates header level data
Transfer :validates information that exists in Receivables tables

What happen when AutoInvoice run?
Once the Auto invoice Program gets called, the following activity takes place is part of execution process. This can be analyzed by debug options.

Line, accounting, and sales credit information for each line populates 3 interface tables
Lines are ordered and grouped
Tax is calculated
GL date is determined
GL accounts are assigned using Auto Accounting
Tax, freight, commitments, and credit memos are linked to transaction lines
All transactions are batched
Validated lines are used to create the transaction

How Data is flowing?

Select, insert and update and delete take place on certain tables once it is logged out.

Selects
– RA_INTERFACE_LINES_ALL
– RA_INTERFACE_DISTRIBUTIONS_ALL
– RA_INTERFACE_SALESCREDITS_ALL

Updates/Insert

– RA_INTERFACE_ERRORS_ALL
– RA_CUSTOMER_TRX_ALL
– RA_CUSTOMER_TRX_LINES_ALL
– AR_PAYMENT_SCHEDULES_ALL
– AR_RECEIVABLE_APPLICATIONS_ALL

Inserts
– RA_INTERFACE_ERRORS_ALL

AutoInvoice Exception Handling
Records that fail validation are called ‘Exceptions’

Exceptions stay in Interface Tables which is RA_INTERFACE_ERRORS_ALL
Errors can be corrected in the Exception Handling window
Once corrections are made, Auto invoice must be resubmitted
Records that pass validation get transferred to Receivables tables

AutoInvoice Exception Handling Windows

-Interface Exception window displays exception messages associated with all invalid records
-Interface Lines window displays records that fail validation, provides an error message and can be used to correct the errors
-The Line Errors windows displays errors associated with a specific line, and can only be opened from Interface Lines window
-Interface Exceptions window displays Interface Id, Exception Type, Error Message and Invalid Value associated to the error
-Data cannot be edited in this window, but error can be viewed and corrected by clicking the Details button
-Error Message and Column name with invalid data are displayed in the Message column, and the invalid value that needs to be corrected is displayed in the Invalid Value column

/by
, ,

Steps to Setup Accounts Payables

Setup Steps [39]

1. Check GL Prerequisites 
2. Define Profiles 
3. Choose Set of Books 
4. Define Payment Terms 
5. Define Payables Lookups – Pay Group, Vendor Types, Source, TAX TYPE 
6. Define Tax Codes 
7. Define Financial Options
8. Define Payables Options 
9. Define Withholding Tax Groups 
10. Maintain Tax Certificates and Exceptions 
11. Define Tolerances 
12. Define Invoice Approvals 
13. Maintain Distribution Sets 
14. Define Automatic Payment Programs 
15. Define Payment Formats 
16. Define Payment Interest Rates 
17. Define Countries & Territories 
18. Set Up Bank Information 
19. Define Expense Report Template 
20. Run Template Update Script 
21. Define Locations 
22. Enter Employees 
23. Define Reporting Entities 
24. Define Income Tax Regions 
26. Set Up Aging Periods 
27. Define Special Calendars
28. Define Report Sets 
29. Control Period Statuses 
30. Implement Sequential Numbering 
31. Descriptive Flexfields
32. Enter Suppliers 
33. Assigning Benefits Defaults
34. Set-up P-Card for Procurement 
35. Define Recovery Rules 
36. Define Tax Groups 
37. Define Reporting Rules (Regional Localizations) 
38. Define VAT Registers (Regional Localizations) 
39. Define Bank Charges 

Step 1 [Oracle Required / ERP Required]
Check GL Prerequisites
Level: Enterprise &Operating Unit
Purpose: To define AP system guidelines to control how AP works.

Step 2 [Oracle Required / ERP Required]
Define Profiles 

Level: Enterprise

Change responsibility to System Administrator
Navigator: Profile > System
Purpose: To set profile options.

Step 3 [Oracle Required / ERP Required]

Choose Set of Books

Level: Operating Unit

Change responsibility to Payables Manager

Navigator: Setup > Set of Books > Choose
Purpose: This is to ensure AP can use the accounting calendar for day to day transactions.

Step 4 [Oracle Required / ERP Required ]

Define Payment Terms 

Level: Enterprise
Navigator: Setup > Invoice > Payment Terms
Purpose: Define payment terms to reflect the way your business is done

Step 5.1 [Oracle Required / ERP Required]
Define Payables Lookups – Pay Group

Level: Enterprise
Navigator: Setup > Lookups > Purchasing
Purpose: Create and maintain Lookups for PAY GROUP. Create additional lookups as business requires.

Step 5.2 [Oracle Required / ERP Required]

Define Payables Lookups – Vendor Types

Level: Enterprise
Navigator: Setup > Lookups > Purchasing
Purpose: Create and maintain Lookups for VENDOR TYPE – this step is already completed for standard Oracle Vendor Types.

Step 5.3 [Oracle Required / ERP Required]
Define Payables Lookups – Source 

Level: Enterprise
Navigator: Setup > Lookups > Payables
Purpose: Create and maintain Lookups for SOURCE – this step is already completed for standard Oracle Source Types.

Step 5.4 [Oracle Required / ERP Required]
Define Payables Lookups – TAX TYPE 

Level: Enterprise
Navigator: Setup > Lookups > Payables
Purpose: Create and maintain Lookups for TAX TYPE – this step is already completed for standard Oracle Tax Types.

Step 6 [Oracle Optional / ERP Mandatory ]

Define Tax Codes 

Level: Operating Unit
Navigator: Setup > Tax > Codes
Purpose: To define the taxes used to record invoice taxes paid to vendors and to tax authorities

Step 7 [Oracle Required / ERP Required ]
Define Financial Options 

Level: Operating Unit
Navigator: Setup > Options > Financials
Purpose: Define the options and defaults used throughout Oracle Applications such as Accounts Payable, Purchasing, and Assets. Defaults can be overridden at the time thevendors, employees and other information are entered.

Step 8 [Oracle Required / ERP Required]

Define Payables Options

Level: Operating Unit
Navigator: Setup > Options > Payables
Purpose: Set control options and defaults used throughout, the system of AP, which will simplify vendor entry, invoice entry, and initiating automatic payment processing.
Defaults can be overridden at the time that vendors, invoices, expense report, or payments are entered.

Step 9 [Oracle Optional / ERP Optional]

Define Withholding Tax Groups

Level: Operational
Navigator: Setup > Tax > Withholding > Groups
Purpose: To define a Withholding Tax Group to be used to assign one or more Withholding Tax type tax names to an invoice or distribution line. Must use this form if there is more than one Withholding Tax type tax name to a tax group, otherwise choose Yes in the Create Tax Group in the Withholding Tax Details region of the Define Tax Names form.

Step 10 [Oracle Optional / ERP Required]
Maintain Tax Certificates and Exceptions

Level: Operational
Navigator: Setup > Tax > Withholding > Certificates
Purpose: Define a Withholding Tax type tax name rate exception for a vendor site. Define a certificate to specify a rate exception for the Withholding Tax type tax name for all invoices of a vendor site.

Step 11 [Oracle Optional / ERP Required ]

Define Tolerances

Level: Operating Unit
Navigator: Setup > Invoice > Tolerances
Purpose: Define the matching and tax tolerances to be used to allow for variances between invoice, purchase order, receipt, and tax information. Tolerances determine whether or not the system places matching or tax holds on an invoice during the AutoApproval process.

Step 12 [Oracle Optional / ERP Required ]

Define Invoice Approvals

Level: Enterprise
Navigator: Setup > Invoice > Hold and Release Names
Purpose: Define the invoice approval codes that are used to hold or release invoice payments. Oracle has many seeded values. Use this form to add any additional approval codes that you will need to run your business.

Step 13 [Oracle Optional / ERP Optional ]

Maintain Distribution Sets

Level: Operating Unit
Navigator: Setup > Invoice > Distribution Sets
Purpose: Create and maintain Distribution Sets. Distribution Sets are used to facilitate invoice entry by reducing or eliminating manual entry of invoice distribution lines.

Step 14 [Oracle Required / ERP Required ]

Define Automatic Payment Programs

Level: Enterprise
Navigator: Setup > Payment > Programs
Purpose: Define and view payment programs that are used to create payment documents such as check, or electronic funds transfers (EFT).

Step 15 [Oracle Required / ERP Required]

Define Payment Formats

Level: Enterprise
Navigator: Setup > Payment > Formats
Purpose: Define the payment formats needed to create payment documents

Step 16 [Oracle Optional / ERP Required ]
Define Payment Interest Rates

Level: Enterprise
Navigator: Setup > Payment > Interest Rates
Purpose: Define the interest rate the system uses to calculate and pay interest on overdue invoices.

Step 17 [Oracle Optional / ERP Required]
Define Countries & Territories

Level: Enterprise
Navigator: Setup > Countries
Purpose: Define the interest rate the system uses to calculate and pay interest on overdue invoices.

Step 18 [Oracle Required/ERP Required ]
Set Up Bank Information

Level: Operating Unit WAVE 1 and 2 SITES ONLY!!
Navigator: Setup > Payment > Banks
Purpose: To enter bank branches, bank accounts, and payment documents for your set of books. If you are a Titan site, please refer to the COS/Titan/Financials AP Setup Document for details on mandatory bank setup for use with Titan. Any other additional banks can be defined here.

Step 19 [Oracle Optional / ERP Optional]
Define Expense Report Template

Level: Operating Unit
Navigator: Setup > Invoice > Expense Report Templates
Purpose: To define expense report templates.

Step 20 [Oracle Optional / ERP Optional ]

Run Template Update Script

Level: Operating Unit
Purpose: To set the includes tax flag on the custom expense report templates created using dataload.

Step 21 [Oracle Optional/ERP Required]

Define Locations

Level: Enterprise
Navigator: Employees > Locations
Purpose: Enter each physical site where employees work, and/or addresses for other organizations

Step 22 [Oracle Required / ERP Required ]

Enter Employees

Level: Operating Unit
Navigator: Employees > Enter Employees
Purpose: Enter, maintain, and view basic personal employee information and addresses. Can only use this form if Oracle HRMS is not fully installed.

Step 23 [Oracle Optional / ERP Optional ]

Define Reporting Entities

Level: Operating Unit
Navigator: Setup > Tax > ReportingEntities
Purpose: Define as many reporting entities as your business needs. A reporting entity is any person or organization that has a unique Tax Identification Number (TIN).

Step 24 [Oracle Optional / ERP Optional ]

Define Income Tax Regions

Level: Operating Unit
Navigator: Setup > Tax > Regions
Purpose: Define tax regions if using 1099 Combined Filing Program Reporting. This is only applicable to the US.

Step 26 [Oracle Required / ERP Required ]

Set Up Aging Periods

Level: Enterprise
Navigator: Setup > Calendar > Aging Periods
Purpose: Define the aging periods to use for the Invoice aging Report

Step 27 [Oracle Optional / ERP Optional ]

Define Special Calendars

Level: Enterprise
Navigator: Setup > Calendar > Special Calendar
Purpose: Define period types which the system uses for special functions such as Key Indicator Reporting, recurring payment, period definitions, or period-to-date reporting for withholding taxes. This is completely separate from the periods defined in the Accounting Calendar window for the AP Accounting periods.

Step 28 [Oracle Optional / ERP Optional ]

Define Report Sets

Level: Operating Unit – operational specific to user and country
Navigator: Other > Requests > Set
Purpose: Define and maintain private report sets. A report set consists of reports that you specify to run as a group, either one at a time in order (sequential), or at the same time in any order (parallel). You can view and update your private report sets. Private reports sets are report sets with a defined owner.

Step 29 [Oracle Required / ERP Required ]

Control Period Statuses

Level: Operating Unit – operational – initial set-ups must show the current period all future periods are controlled within country.
Navigator: Accounting > Control Payables Periods
Purpose: Change and review the status of the accounting periods in AP or your set of books.

Step 30 [Oracle Optional / ERP Optional ]

Implement Sequential Numbering

Level: Operating Unit

Change Responsibility to Sysadmin

Navigator:
Purpose: Assigns sequential numbers as voucher numbers to the invoices and payments created. A voucher number is a unique reference to an invoice or payment.
Must be completed by an associate with a System Administrator responsibility.

Step 31 [Oracle Optional / ERP Optional ]

Descriptive Flexfields

Level: Enterprise
Navigator: Setup > Flexfields > Descriptive > Segments

Step 32 [Oracle Required / ERP Required ]

Enter Suppliers

Level: Operating Unit

Change Responsibility to Payables Manager

Navigator: Suppliers > Entry
Purpose: Enter vendors, vendor sites, and vendor site contacts. Define multiple vendor sites for each vendor and multiple personal contacts for each vendor site.

Step 33 [Oracle Optional / ERP Required]

Assigning Benefits Defaults

Level: Enterprise
Navigator: Setup > Organizations
Purpose: To add a default value of NCR Payroll Default to the Setup Business Group organization

Step 34 [Oracle Optional / ERP Required] — for countries with multiple taxes on a single expense receipt

Set-up P-Card for Procurement
Level: Inventory Org
Navigator: Suppliers > Entry
Purpose: To enable the use of Procurement Cards In order to use P-Cards for Internet Procurement, you must check the box for ‘Procurement Card’ by site under the General Tab.

Step 35 [Oracle Optional / ERP Required] — for countries with multiple taxes on a single expense receipt

Define Recovery Rules

Level: Operating Unit
Navigator: Setup > Tax > Recovery Rules (MUST BE DONE PRIOR TO TAX CODES (STEP 6)
Purpose: Use a tax recovery rule if the recovery rate varies or there are multiple tax rates on a single receipt or invoice, depending on the following distribution attributes: Distribution account, Invoice date, or Condition (for example, supplier type)

Step 36 [Oracle Optional / ERP Required] — for countries with multiple taxes on a single expense receipt

Define Tax Groups
Level: Operating Unit
Navigator: Setup > Tax > Groups
Purpose: Use the Tax Groups window to group multiple, conditional taxes. Tax groups let countries with multiple taxes automatically calculate each applicable tax within Payables, Receivables and Oracle Order Management.

Step 37 [Oracle Required / ERP Required ]

Define Reporting Rules (Regional Localizations)

Level: Operating Unit

Change Responsibility to Belgian AP Localizations

Navigator: VAT > Reporting Rules

Purpose: OU specific VAT reporting rules. Load the rules using the dataload sheet attached.

Step 38 [Oracle Required / ERP Required ]

Define VAT Registers (Regional Localizations)

Level: Operating Unit
Change Responsibility to Italian AP Localizations

Navigator: VAT Register
Purpose: OU specific VAT registers. Enter the 4 different VAT Registers.

Step 39 [Oracle Required / ERP Required]

Define Bank Charges

As Payables Manager responsibility

Level: Operating Unit
Navigator: Setup > Payment > Bank Charges
Purpose: OU specific bank charges. Enter the bank charge transfer rules as documented below.

/by