What is SQL*Loader?
SQL*loader is one of the Oracle tool which will be used to transfer the data from Flat-File to oracle Database table.

Which files in SQL*loader?
1. Flat or Data File
2. Control File
3. Bad File
4. Discard File
5. Log File

What is Flat Or Data File: This file contains the records in a special format; these records will be fetching for other legacy. The extension of these files might be .dat, .txt, or .csv (comma separated view).

What is Control File: This is SQL loader execution file, which will be used to transfer the date from file to table. In side of these control file, we will mention the Data file path, table name, column mapping. The extension of control file is .ctl

Control File Creation:

Load data
INFILE ‘Data File Path’
INSERT INTO ‘Table Name’
FIELD TERMINATED BY ‘,’
WHERE deptno = 10
TRAILING NULL COLS
(column1 , empno
column2, ename
column3, deptno)

Once we develop the control file we will execute this by using fallowing command
C:> sqlldr user/passward @ Database Control = name of control file (with extension .ctl)
This command will start the control file execution, and it will try to read the data and inserting into table. After completion of this execution, automatically three files will gets created
Bad file
Discard file
Log file

Bad File: Bad file contain the records, which are rejected by the SQL*loader. SQL*loader will reject the records, when ever the Flat file format is not correct or if any internal error occurs it will rejected. The extension of bad file is .bad

Discard File: Discard file contains the records which are rejected by the control file, control file reject the records, if record is not satisfying the conditions, which we have mentioned inside of control files the extension of discard file is .dis

Logfile: It contains the complete info of the process, like no of records successfully loaded in to the table
No of records successfully loaded in to the bad file & discard file.
And where the bad, discard file gets created and time taken to complete the process.
Taking the complete log.
SQL* Loader Modes:
INSERT
APPEND
REPLACE
We can replaced the data in to the table by using any one of the allowing method

INSERT: When we are using this statement, table should be empty. SQL * loader will insert the new data form the file.

APPEND: This mode will be use to attach the new record to the existing records.

REPLACE: This will replace the existing records with new records.
C:> sqlldr userid/passward@Database control=text1.ctl path=direct

SQL* Loader Paths: We can execution SQL* loader in two paths or nodes
Direct
Conventional

By default SQL*loader will be running in conventional mode, if we want to run in direct mode will use the fallowing syntax
C:> sqlldr userid/passward@Database control=text1.ctl path=direct
Direct mode will disable the table and column constrains and it will insert the data.
Conventional path will check every constrains, if it is satisfied it will insert the record
Conventional path is just like ‘insert statement’
SQL Commands Limitations:
to_date, to_char, upper, lower, Initcap, string, decode, nvl
when clause
sequence_name.next_value, Ref-Cursor
sysdate, ltrim, rtrim, constant

Functional Training  of Purchase Order Flow
What is flow of PO:

Step 1:We will be raising the Requisitions for the items which are needed.
Requisition is 2 types • Internal Requisition • Purchase
Internal Requisition is used when we are getting the items from one organization to another organization of the same business group.
Purchase Requisition is raised when we are getting the items from outside the business group.

Step 2:Once the Requisition has been approved, we will be sending (Request For Quotes) RFQ’s to different Suppliers. It contains the information regarding the type of RFQ, terms and conditions, shipments, currency.
RFQ is of 3 types
• Bid RFQ • Catalog • Standard
BID: Used for a fixed specific quantity, Location and date. This will be used for large or expensive price of equipments. That you have never order before. There won’t be price any breaks for a BID Quotation.
CATALOG: This will be used for high volume items for which your supplier sends information regularly. The CATALOG RFQ includes price breaks at different quantity levels.
STANDARD: It is used for items we need only once or not very often for a specific fixed quantity, location and date. It includes price breaks at different quantity levels.

Step 3:We will be receiving the quotations from different suppliers. Quotation is of 3 Types.
• Bid
• Catalog
• Standard

Step 4:Based on the quotations, we will be deciding the supplier and purchasing order is given to that supplier.
Purchasing is of 4 types
• Standard
• Planned
• Blanket
• Contract

Step 5:Once we receive the items from the Supplier we will issue the receipts to the Supplier. This is done in 3 ways.
• Two-Way: In 2-way we will compare PO and Invoice.• Three-Way: In 3-way we will compare the PO, Receipt and Invoice.• Four-Way: In 4-way we will compare PO, Receipt, Inspection and Invoice.

2-Way Matching verifies that Purchase order and invoice information match within your tolerances as follows:Quantity Billed <-Quantity Ordered Invoice Price <- Purchase Order Price (<- Sign is used because of tolerances)

3-Way Matching verifies that the receipt and invoice information match with the quantity tolerances defined:Quantity billed Quantity received

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
In ‘More’ alternative region of Shipments block, you define the Invoice Matching option, where you can choose 2-way, 3-way or 4-way match.

Step 6:Because of these Transactions Inventory and Payables get affected.

Training  of Account Payables -> Account Payables Flow:
PO -> Receipt -> AP -> GL

Invoice ->; Payments->; Move Transactions from AP to GLIn AP there are 2 Thumb rules.

• Without supplier there is no invoice.
• Without invoice there is no payment.

How many types of Invoices and What are Invoice Types:
1. Standard: We will make all the payments based on the standard invoice. It will have the information of Invoice Number, Invoice Date, Invoice Amount, and Currency.
2. Credit Memo: We will create credit memo invoice whenever supplier is giving the discount and it will be adjusted in standard invoice. It is always negative amount.
3. Debit Memo: Sometimes Company will deduct some amount from the invoice amount. This will be adjusted in standard invoice. It is always negative amount.
4. With-Holding TAX: This type of invoice will be created to make the invoice tax to the Govt. on behalf of supplier.
5. Pre-Payment: If we want to make some payments to the supplier in advance then we create the Pre-Payment invoice.
6. PO Default: If we want to make the invoice as per the PO then we create PO default. We will give Po Number. System will pick up the complete PO information.
7. Mixed: Includes both positive and negative amount. We can match this invoice with PO’s and other invoices.
8. Expense Report: This will be applicable for the employees who are working in the Company where payables and internet expense and project account. Expense will be included.
9. Recurring invoice: We can enter invoice for periodic expense for which we may not receive. Invoice from supplier. To create a Recurring invoice first we will take template.
As per that we will create the invoice.Once the invoice is successfully completed, we can go for payments. It is of 3 types.

Manual Payment: Here we will be mentioning the Invoice Number, Bank Account, and Document Number, Payment Date and Currency.

Refund payment: This is used for Employee expenses and for adjusting the Supplier account,

Quick payment: In this payment, system will automatically generate checks. To print Checks there will be a concurrent program for each check format.Once the payment was done, we will move all the transactions to GL.

Wo is Suppliers:
Set up suppliers in the Suppliers window to record information about individuals and companies from whom you purchase goods and services. You can also enter employees whom you reimburse for expense reports. When you enter a supplier that does business from multiple locations, you store supplier information only once, and enter supplier sites for each location. You can designate supplier sites as pay sites, purchasing sites, RFQ only sites, or procurement card sites. For example, for a single supplier, you can buy from several different sites and send payments to several different sites. Most supplier information automatically defaults to all supplier sites to facilitate supplier site entry. However, you can override these defaults and have unique information for each site. The system uses information you enter for suppliers and supplier sites to enter default values when you later enter transactions for a supplier site. Most information you enter in the Suppliers window is used only to enter defaults in the Supplier Sites window. When the system enters that information in a later transaction, it only uses supplier site information as a default, even if the supplier site value is null and the supplier has a value. If you update information at the supplier level, existing supplier sites are not updated. When you enter a supplier, you can also record information for your own reference, such as names of contacts or the customer number your supplier has assigned to you.

What is Invoices:
Invoice Type (LOV): The type of invoice. Standard and Credit are the only invoice types you can enter in this window. If you do not enter a value for this field then a value will be assigned during import based on the amount of the invoice.

Standard: A trade invoice you receive from a supplier. The amount of a Standard invoice must be zero or greater.

Credit: Credit Memo. A negative amount invoice you receive from a supplier representing a credit for goods or services purchased. Note that in the Invoice Gateway you can match a credit memo to a purchase order to perform a price correction, but you cannot match a credit memo to an invoice. If you want to match to an invoice, then use the Invoice Workbench.

Debit Memo: Negative amount invoice created by you and sent to a supplier to notify the supplier of a credit you are recording. Usually send with a note explaining the debit memo. Purchase Order Matched Invoices: You can match Payables invoices to purchase orders to ensure that you pay only for the goods that you have ordered, or you can match to purchase order receipts to ensure that you pay only for goods that you have received. Purchase order matched invoices are invoices that you match to any of the following:
• Purchase order shipments
• Purchase order receipts
• Purchase order receipt lines
• Purchase order distributions

Foreign Currency Invoices: When you enter an invoice in a currency other than your functional currency, Payables uses an exchange rate to convert the invoice and invoice distributions into your functional currency for creating journal entries. You define your functional currency during setup for your set of books.
Mixed Invoices: Mixed Invoices are invoices or credit/debit memos for which you can perform both positive and negative matching to purchase orders and to other invoices. For example, you can enter an invoice for –$100 with Invoice Type Mixed. You can match to an invoice for $–200, and match to a purchase order for $100.

Prepayments:A prepayment is a type of invoice you enter to make an advance payment to a supplier or employee. For example, you need to pay a deposit on a lease, or pay an employee an advance for travel expenses. You can later apply the prepayment to one or more invoices or expense reports you receive from the supplier or employee to offset the amount paid to them. The supplier might send an invoice that references a prepayment. The supplier has reduced the invoice amount by the amount of the prepayment and associated tax. You can use the Prepayment on Invoice feature to enter the invoice.
You can enter two types of prepayments:
Temporary prepayments can be applied to invoices or expense reports you receive. For example, you use a Temporary prepayment to pay a hotel a catering deposit. When the hotel’s invoice arrives, apply the prepayment to the invoice to reduce the invoice amount you pay.
Permanent prepayments cannot be applied to invoices. For example you use a Permanent prepayment to pay a lease deposit for which you do not expect to be invoiced.

What are types of Matching? 

2–way matching: The process of verifying that purchase order and invoice information matches within accepted tolerance levels.
Payables use the following criteria to verify two–way matching:

Invoice price <= Order price
Quantity billed <= Quantity ordered

3–way matching: The process of verifying that purchase order, invoice, and receiving information matches within accepted tolerance levels. Payables use the following criteria to verify three–way matching:

Invoice price <= Purchase Order price
Quantity billed <= Quantity ordered
Quantity billed <= Quantity received

4–way matching: The process of verifying that purchase order, invoice, and receiving information matches within accepted tolerance levels. Payables use the following criteria to verify four–way matching:

Invoice price <= Order price
Quantity billed <= Quantity ordered
Quantity billed <= Quantity received
Quantity billed <= Quantity accepted

What is FOB (Free On Board): The point or location where the ownership title of goods is transferred from the seller to the buyer. This indicates that delivery of a shipment will be made on board or into a carrier by the shipper without charge, and is usually followed by a shipping point or destination (e.g. ’FOB Our warehouse in New York’). The FOB code is currently available only for reference purposes. Revenue and cost recognition is not currently determined by the value entered in this field. (Receivables Lookup)

What is Purge : An Oracle Receivables Process, where you identify a group of records for Receivables to delete from the database. Receivables purge each record and its related records. Receivables maintain summary data for each record it purges.

Implementing User Profiles :

You should define user profile options whenever you want your application to react in different ways for different users, depending on specific user attributes.

You can decide whether your end users can view and update options you define at the User level or you can also define the option such that your end users could not see or change its value.

Predefined User Profile Options :

Database Profile Options :

Oracle Application Object Library provides many user profile options that the Oracle System Administrator or the users can see and update.

Internally Generated Profile Options :

Oracle Application Object Library also provides a set of profile options that you can access via the user profile routines. You can retrieve values for these profile options in your forms and programs.

However, except for the profiles CONC_PRINT_OUTPUT and CONC_PRINT_STYLE, you cannot change their values. System administrators and end users cannot see the values for, nor change the values of, these profile options.

  • FND_PROFILE: User Profile APIs :

You can use the user profile routines to manipulate the option values stored in client and server user profile caches.
On the client, a single user profile cache is shared by multiple form sessions. Thus, when Form A and Form B are both running on a single client, any changes Form A makes to the client’s user profile cache affect Form B’s run–time environment, and vice versa.

On the server, each form session has its own user profile cache. Thus, even if Form A and Form B are running on the same client, they have separate server profile caches. Server profile values changed from Form A’s session do not affect Form B’s session, and vice versa.

Similarly, profile caches on the server side for concurrent programs are separate.

Also, note that the server–side profile cache accessed by these PL/SQL routines is not synchronized with the C–code cache. If you put a value using the PL/SQL routines, it will not be readable with the C–code routines.

Any changes you make to profile option values using these routines affect only the run–time environment.

The effect of these settings ends when the program ends, because the database session (which holds the profile cache) is terminated.

  • FND_PROFILE.PUT :

Summary procedure FND_PROFILE.PUT

(name IN varchar2,

value IN varchar2);

Location FNDSQF library and database (stored procedure)

Description Puts a value to the specified user profile option. If the option does not exist, you can also create it with PUT.

Arguments (input)

name The (developer) name of the profile option you want to set.

Value The value to set in the specified profile option.

  • FND_PROFILE.GET

Summary procedure FND_PROFILE.GET

(name IN varchar2,

value OUT varchar2);

Location FNDSQF library and database (stored procedure)

Description Gets the current value of the specified user profile option, or NULL if the profile does not exist.

Arguments (input)

name The (developer) name of the profile optionwhose value you want to retrieve.

Arguments (output)

Value The current value of the specified user profile option as last set by PUT or as defaulted in the current user’s profile.

Example FND_PROFILE.GET (’USER_ID’, user_id);

  • FND_PROFILE.VALUE

Summary function FND_PROFILE.VALUE

(name IN varchar2) return varchar2;

Location FNDSQF library and database (stored function)

Description VALUE works exactly like GET, except it returns the value of the specified profile option as a function result.

Arguments (input)

Name The (developer) name of the profile option whose value you want to retrieve.

Q. AME is possible in PO approval ??
NO. AME for Req approval
Q. Can we create Position heiracchy for Requisition Approval
Yes
Q. vacation rule has to be created by the same user…if being as a first person in the approver  goup the user not able to create vacation rule and goes on leave…then any other way, by  which we can handle that out of scope
Q. Can I get the recording version of yesterda’s session?
No.
Q. Where did we do setups for taxes
Ebiz Tax
Q. Is AME diff in 11i and R12
Conceptually, AME is same or 11i and R12 but the look and feel and navigation is  completely different.
Q. Any profile for confidentiality of Quotation?
No. Anybody with access to quote and quote analysis screen has access to quotations
Q. when we delete approved record, if it is by other user, will he be notified for the same
No.
Q. How to protct cofidentialiy and modification of quot from others
– to prevent access to quote screen
– make the quote screen read only
Q. is there any work flow proces behind this?
no
Q. Can we have PO without RFQ or quotes for that matter.
Yes. We can have PO with RFQ and quotes. But its not a good practice.
Purchase Manager can be questioned why he gave order to some supplier. He will always have 2-3 quotes with him.
Q. but for blanket type of PO do you think quotes are required?
2 ways to determine price for an item
            – Quotations  – document provided by vendor to supply at a price
            – Blanket PO – agreement between vendor and us
Q. If we dont want other buyer can see qout of other buyer even they have access to RFQ screen ??
only related buyer only can see his Quot not other one which is not related to him ??
Personalization. Without personalization, its not possible.
Q. Can we copy Catalog RFQ to Standard quotation?
Yes. Copy Catalog RFQ to Catalaog quote or Standard quotation. Copy Bid RFQ to Bid quotation.
Q. Can you show us Creating PO from Requisition having RFQ as Source Doc.
RFQ cannot be source doc. We can have Quote as Source and we will be creating PO from Req using Quote as Source.
Q. Yes but not for release.
Question not clear
Q. What about negotion process in this ??
Negotiation is possible in Oracle Sourcing. Out of Scope.
Q. Are u going to take Planned Purchase order
Yes. That will be covered later.
Q. Will we cover Schedule release VMIand Consigned.?
Out of scope. Inventory related. Kindly attend Inventory training.
Q. Is there  any analytics available in this release to freeze the minimum quote automatically?
No. Quote analysis and approval is a manual process. No automation is provided.
Q. Can you cover the Advance Pricing intergration in Purchasing?
This will be covered conceptually. There wont be any practicals on the same.
Q. What about Bill of distribution??
Sourcing Rules  – ASL is required. Will cover Sourcing rules later.
Q. Sourcing means for RFQ ??
Selecting the Source of Supply of material.
Sourcing – Sourcing Rules – automatically create PO from requisitions
Q. While using ASL for Commodity theall items listed under that commodity will get imapcted . believe it’s true can u confirm .
Yes. I will show you guys a chart
Q. How the Supplier status behave differently in Global & Local ASL?
Local ASL will override Global
Local (M1) – debarred Global – Approved
PO for M1 – will not allow
PO for M2 – should allow
But local is specific to that org not for others. so for M2 it should allowed.
Q. what is category level supplier?
The supplier will be ASL for all items which fall under that category.
Q. then where do we define that?
That is defined in ASL
Q. Where do we define the range of items under that category?
Linked while defining Items
Q. AND is approved for Item not for category so Item shoud be presidance. and it should allowed. Item shoud have more presidance then category.
NO. Category is having higher precedence. So, if a Supplier is debarred for a category then he is debarred for all items under that category.
Q. Can we create Req to Release Item if is not matching on Requisition and BPA. but ASL is set.
Question is not clear
Q. what if allocation for ASL is <100 %
For manual PR, 100% allocation does not matter. 100% allocation is required only for planned orders. For manual PR the PO will be raised against the supplier with highest allocation.
Q. What does Sourcing Assignment signify?
Sourcing Assignment will determine which supplier to source an item for a given ORG
Q. If we have one org then can we do transaction in Master Org
Yes. No restriction. Best practice that we should not do any transaction in Master Inv Org.
Q. What isTCA??
Trading Community Architecture. Covered in AR. This is party, customer relationship and stored in HZ tables.
Q. How intercompany sourcing happens and how PO will be managed in two companies?
Internal Requisition/Internal Sales Orders
Q. What is the need to add the price into price list…when we have given price for the item in Inventory setup?
Price that we mentioned is the Purchase list price. But for OM, it picks the price from Price list.
Q. While giving the value in Price list…do we add our all the cost and profit in that
For internal items, the transfer price is calculated based on the cost + the transfer options.
Q. What does create internal order conc request do?
Create internal order request will transfer the IR info to OM interface tables.
Q. Why is the cost zero for Internal Orders?
We did not enter any cost for the item, so the cost was zero.
Q. could you please show this cost thing by raising external sales order?
Out of Scope. OM class is required.
Q. Cost is zero for internal sales order, however you told that it will be 10% addition of the cost of that item then why it is not 10 % addition in the cost?
Q. Why is the cost of the Item zero?
The cost of the Item was zero because we did not enter the cost of the item.
Q. Where do we enter the cost?
We enter the cost while defining the Item.
Q. How does the price default while creating IR?
The price of the item while creating IR, is the cost of the Item.
Q. Can you clarify where we set up zero cost for an Item for IR?
We did not specify the zero for IR. If we do not enter the cost, it will be considered as zero.
Q. Can we change PO Currency?
Yes. We can change PO currency. It defaults from supplier setup but we can change it.
Q. Can you explain what is confirming order ?
Sometimes, the Purchase manager requests for certain items over phone. Then he sends the PO just to confirm the verbal order. If the vendor thinks that this is a duplicate order and delivers double, it could cause problem, so he sends a PO with a message that it is confirming order.
What is Difference Between Blanket release and Planned Release?
Blanket release is against blanket purchase agreement and planned release is against planned orders.
Under what Circumstance we’ll use Blanket and Planned Agreement
In blanket, the price of the agreement is fixed but the quantity is not fixed. We get price breaks in Blanket for volume discounts.
In planned, the shipment is tentative. It does not have any price breaks so no volume discounts.
So, blanket is for high volume transactions.
Q. For planned orders, Schedule is available but blanket is for as and when required?
The schedule in planned Orders is just tentative. The actual schedule is determined later.
Blanket can have an effective date range. Planned does not have any effective date range.
Q. Can you cover Advance Pricing?
Not in scope.
Q. What is the way for adding items or modify BPA from supplier point of view..are they need have oracle apps installed with Oracle iSupplier portal?
They will have access to Oracle iSupplier portal which is a web based application. No installation is required.