P2P(Procure-to-Pay) Cycle Tables with Joins

Pre Requisites for Purchase Order:

1.Item Creation:

2.Supplier Creation:

3.Buyer Creation:

-------------------------------------------------

1.Item Creation:

1)Attach the Responsibility called "Inventory Vision Operations (USA)"

2)Open the Items form

  Items=>Master Item

3)Select the Organization name - Vision Operations

4)Enter the Item Name , Item Description 

  goto Inventory tab check the checkbox called Inventory 

  goto purchasing tab check the check box called Purchasing

5)Save

6)Goto  Tools Menu => Organization Assignment option to assign for the multiple

  organizations.

SELECT * FROM MTL_SYSTEM_ITEMS_B WHERE SEGMENT1='APACHE'  --INVENTORY_ITEM_ID=20817

2.Supplier Creation:

1)Attach the Responsibility called "Purchasing Vision Operations (USA)"

2)Goto the Supplier Form

  Supply Base=>Suppliers

3)Enter the Supplier Name . Save supplier number will be created automatically.

4)Select the Sites button enter the supplier site address and other details

5)Goto the Contacts tab enter the Contact details 

    Name

    Phno

    Postion and so on.....

SELECT * FROM PO_VENDORS WHERE SEGMENT1='20067' --VENDOR_ID=7930

SELECT * FROM PO_VENDOR_SITES_ALL WHERE VENDOR_ID=7930 --VENDOR_SITE_ID IN (4638,4639)

SELECT * FROM PO_VENDOR_CONTACTS WHERE VENDOR_SITE_ID IN (4638,4639)

3.Buyer Creation:

Buyer Creation:

==================

1)attach the Responsibility called "US HRMS Manager"

2)Create Employee  

  People => Enter and Maintain=>Select New button

3)Enter Emp name 

  select action option select "create Employement" select the optiona s "Buyer"

4)enter Data of Birth 

  save => Ok  = > Empoyee number will be generated.

5)Goto  System Administrator open the User form create or query user

  select the Person field attach the emp name (Which we have created)

6)Save.

7)Goto Purchasing Responsibility 

  open the Buyers form 

  enter the employee name and save the transactions.

 Setup => Personnal =>Buyers

SELECT * FROM PER_ALL_PEOPLE_F --WHERE --PERSON_ID='25'--EMPLOYEE_NUMBER='1289' --PERSON_ID=13496

Purchase Order Flow:

1.Requisition
2.RFQ(Request for Quatation)

1.REQUISITION:

Requisition: is one of the purchasing document will be prepared by the employee when
   ever he required the materials or Services or Training and so on.
we have two types of Requisitions 1)Internal
                    2)Purchase

Internal requisition will be created if materials are receiving from another Inventory
inside of the organization.
Purchase requisition will be created while purchasing the materials from the Suppliers.

Requisitions=>Requisitions

We will enter the Requisition at three level 1)Header
                        2)Line
                        3)Distributions.

Open the Requisition form enter the Reqno and select the type at Header level
 Enter the Items information at line level like Item name,qty,unitprice,tax and so on
select Distributions button enter the Distributions details.
Save
Select the Button called Approve button to go for approving the Requisition Document
Open the Requisition summary form.
Enter the Reqno select find button we can find the Requisition status wether it is
approved or not.
select Tools menu => View Action History to find the history details
Select Tools Menu  =>Control option to Cancel the requisition.


SELECT * FROM PO_REQUISITION_HEADERS_ALL WHERE SEGMENT1='5716' --REQUISITION_HEADER_ID=56885

SELECT * FROM PO_REQUISITION_LINES_ALL WHERE REQUISITION_HEADER_ID=56885  --REQUISITION_LINE_ID=60797

SELECT * FROM PO_REQ_DISTRIBUTIONS_ALL WHERE REQUISITION_LINE_ID=60797

2.RFQ(REQUEST FOR QUOTATION)



Once the Requisition is Approved Buyer will prepare thre RFQ document which will be
delivered to the supplier. Supplier will respond for that with quotation.
we have Three types of RFQ documents

BID RFQ:This will be prepared for the secific fixed quantity and there won't be any
        PriceBraeaks(Discounts).

catalog RFQ: This will be create for te materials which we will purchase from the
        suppliers regularley , and large number of quantity. Here we can specify the
        Price Breaks.

Standard RFQ:  This will be prepared for the Items which we will purchase only once
        not very often,Here we can include the Discounts information at different
        auantity levels.
RFQ Information will be entered at 3 Level
  1)Headers
  2)Lines
  3)Price Breaks(CATALOG,STANDARD) or Shippments (Only for Bid RFQ)

Terms And Conditions:
While creation of the RFQ documents we will select the Terms button and we will enter
the terms abd condition details.

Payment Terms: When Organization is going to make the payment and Interest rates
Fright  Terms: Who is going to Bear the Tansportation chargers wether Buyer or Supplier
FOB(FreeOnBoard): If any materials damage or any missing quantity is there then the
             the responsiboility of those materials.
Carrier     : In which Transportation Company Organization Required Materials
              Transportation company Name.

Open the RFQ Form

RFQ and Quotations=>RFQ's
select TYpe and Dates and so on
enter the Items details at line level
select terms button enter the Terms and Condition Details
Select the Price Braks button enter the Price break details
Save
Select the suppliers button enter the suplier details (Who are receiving this Document)
Select the Button called Add from List to Include the supplier list automatically.


SELECT * FROM PO_HEADERS_ALL WHERE SEGMENT1='347' AND TYPE_LOOKUP_CODE='RFQ' --PO_HEADER_ID=32876

SELECT * FROM PO_LINES_ALL WHERE PO_HEADER_ID=32876 -- PO_LINE_ID=38063

SELECT * FROM PO_LINE_LOCATIONS_ALL WHERE PO_LINE_ID=38063 --LINE_LOCATION_ID=72425

3.QUOTATION:



Quotation is another purchasing document we will receive from the Supplier which
contains the supplier quote details , Price, Payment terms and so on.

Whatever the quotations we have received from the supplier we will enter in the system
through form.

We have three types of Quotations 1)Bid 2)Catalog 3)Standard

For Bid RFQ      we will  receive Bid      quotation from the Supplier
For Catalog RFQ  we will  receive Catalog  quotation from the Supplier
For Standard RFQ we will  receive Standard quotation from the Supplier.

After enter all the quotations in the system management will do quote analysis as per
that one best quotation will be elected as Purchase Order.

Quotation Report

Item Name   (Table Value set MTL_SYSTEM_ITEMS_B   Segment1)

QuoteNo Type  Cdate Supplier Site ContactPerson Buyer   Created(UserName)

4.PURCHASE ORDER:

PO is one of the Main document which will be prepared and approved by the buyer and
send it to the supplier. which contains the following information
terms and Conditions
Items deails
Qty,Price
Distiribution and Shipment Details and so on.

We have four types of Purchase Order       1)STANDARD
     2)PLANNED
     3)BLANKET
     4)CONTRACT

Purchase Orders=> Purchase Orders
Open the PO form enter the Inforamtion at header level select line level inforamtion
enter the items and quantity,price details
select shippments button enter the shippment details select the Distributions button
enter the Distribution Detauils.
Save
Select the Button called Approve (Uncheck Email Check Box) , Document will be submitted
for approval.
open the Purchase Order summary form  enter PO number Select Find button we can find
the status of the Purchase order.
Goto Tools menu
Action History => We can find who hs submitted for Approve /Reject /Cancel details
Copy Document  => To Create Another PO based on this PO
Control        => To Close the Purchase Order or to cancel the Purchase Order.

Types of Purchase Order: -

	Standard P.O	Planned P.O	Blanket P.O	ContractP.O
Terms and Conditions Goods or Service Known Pricing Known Quantity known Account Distributions Known Delivery Schedule Known Can be Encumbered Can Encumber releases	Yes Yes Yes Yes Yes Yes Yes N/A	Yes Yes Yes Yes Yes May be Yes Yes	Yes Yes May be No No No No Yes	Yes No No No No No No N/A

SELECT * FROM PO_HEADERS_ALL WHERE SEGMENT1='4514' AND TYPE_LOOKUP_CODE='STANDARD' --PO_HEADER_ID =32878
 --TYPE_LOOKUP_CODE

SELECT * FROM PO_LINES_ALL WHERE PO_HEADER_ID =32879 --PO_LINE_ID=38065

SELECT * FROM PO_LINE_LOCATIONS_ALL WHERE PO_LINE_ID=38066 --LINE_LOCATION_ID=72427

SELECT * FROM PO_DISTRIBUTIONS_ALL WHERE LINE_LOCATION_ID=72428

SELECT * FROM PO_LOOKUP_CODES --LOOKUP_CODE

AUTO CREATE:


It is one of the Purchasing feature to create the RFQ and  PO documents automatically
by using requisition lines.

1)Create Requisition and approve
2)Open the AutoCreate form
3)Select Clear button enter the RequisitionNO
4)Select find button which will shows all the requisition lines
  select the lines whatever we want to include into the RFQ
5)select Action = Create to create new RFQ
  AddTo  to add lines to exisiting to RFQ
6)Select DocumentType = RFQ  
7)select Automatic button which will create RFQ document automatically .

5.RECEIPTS:

           Receipts are one of the documents it will be used to find out how much quantity Supplier has supplied. We will find out Purchase Order status if it is successfully approved then we will create the Receipt. We will give the PO Number select Findbutton check the PO lines right mark and save. It will create the Receipt number select Header button it will shows the receipt number and date.           

                                                                                                                                                               SELECT * FROM RCV_SHIPMENT_HEADERS WHERE RECEIPT_NUM=7472 --SHIPMENT_HEADER_ID=61421

SELECT * FROM RCV_SHIPMENT_LINES WHERE SHIPMENT_HEADER_ID=61421 -- SHIPMENT_LINE_ID=68368

SELECT * FROM RCV_TRANSACTIONS WHERE SHIPMENT_HEADER_ID=61421

Once Receipt will created go Inventory module and check whether the requested items are received or not by using these tables.

SELECT * FROM MTL_SYSTEM_ITEMS_B WHERE SEGMENT1='APACHE'

SELECT * FROM MTL_ONHAND_QUANTITIES WHERE INVENTORY_ITEM_ID=20817 AND ORGANIZATION_ID=204

SELECT SEGMENT1 FROM PO_HEADERS_ALL WHERE PO_HEADER_ID=32881 AND TYPE_LOOKUP_CODE='STANDARD'

Match Approval Level :- While creating the Purchase Order we will mention the Match Approval Level at Shipments we will have 3 types they are

2-way:- Purchase Order and Invoice Quantities must match within tolerance before the corresponding invoice can be paid.

3-way:- Purchase Order, Receipts and Invoice Quantities must match with in tolerance before the corresponding invoice can be paid.

4-way:- Purchase Order, Receipts, Inspection and Invoice Quantities must match with in tolerance before the corresponding invoice can be paid.

Account Payables (A.P) Module:-

           Account payables will be used to do the payment transactions. A.P Module is integrated with both P.O and G.L Modules. In Account Payables we will create the invoices and we will approve once invoice is approved successfully we will make the payment. Once payment is over we will move the transactions from A.P to G.l.

1.  Without supplier we cannot create Invoice.

2.  Without invoice we cannot make Payment.                                      

From the company point of view a person or Organization who is going to receive amount we will call as Supplier.

Types of Invoices:-

1.     Standard

2.     Credit Memo

3.     Debit Memo

4.     With Holding Tax                                                                                   

5.     Po Default

6.     Mixed

7.     Pre Payment

8.     Expense Report

9.     Recurring Invoices

10.  Quick Match                                                                                

Standard Invoice:-    We will create the Standard Invoice to particular Supplier and Supplier site we will enter the invoice amount, invoice date and soon……..

Credit Memo & Debit Memo Invoices:- Both Invoices has got negative (-ve) amount and adjusted against Standard Invoice. Credit Memo will be created whenever Supplier is giving discount. Debit Memo will be created if buyer is going to deduct the amount.

With Holding Tax Invoice:-      If supplier is not registered supplier then buyer will make the Income Tax to the government on behalf of supplier.

Po Default Invoice:-    Here we will create the Invoice as per Purchase Order amount. We will give the Po number system will retrieve PO amount and Invoice will be created as per PO details.

Prepayment Invoice:-    When ever we want make payment to supplier in advance that tome we will create this Prepayment Invoice and we make the Payment.

Expense Reports Invoice:-     It will be created for employee expenses as per the employee grade, position this Invoices will be calculated.

Recurring Invoice:-      For some of the Invoices we will not be having supplier invoice that time we will create Recurring Invoices.

Ex:-  For rent account we will be creating Invoice which has got fixed amount and fixed rate (duration).

 Quick Match Invoice:- While creating Purchase Order we will be giving the match approval option as per that match approval we will create the Invoice and the Invoice type is Quick Match Invoice.

Mixed Invoice:- Mixed Invoices will be created for miscellaneous expenses. Once we create the invoice you have to do following 3 activities.

1.     Validate Invoice

2.     Approve the Invoice

3.     Create Accounting entries for Invoice 

 INVOICES

Here we will select the Invoice type and we will give the Supplier number, name, site invoice date, invoice number, invoice currencies, and amount. Select Distributions button to distribute the Invoice amount into different accounts.

1.     Invoice total should be equal to the distributions total then we will call it as Invoice validated successfully.

2.     Select Actions…1 button chooses approve check box press OK then system will approve the Invoice.

3.     Select Actions…1 button choose create accounting check box press OK button it will create the accounting entries we can see all this accounting transactions from tools view accounting option.

SELECT * FROM AP_INVOICES_ALL WHERE INVOICE_NUM='INV4516'  --INVOICE_ID=63379 ,--VENDOR_ID(LINK B/WAP INVOICE AND PO_VENDORS
)
SELECT * FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE INVOICE_ID=63379

Invoice Holds:-    If invoice is not approved then that invoice will be keeping under hold status. By selecting holds button in invoice form we can see the holds details.

For view Invoice holds details:

           Select * from ap_holds_all

For view release the Invoice holds names:

           Select * from ap_holds_release_name_v;

PAYMENTS:

Payments:-     Once the Invoice is approved then we can go for payments. The Payments are or 3 types. They were

1.     Manual

2.     Quick

3.     Refund

Manual:-    Here we will issue the checks manually to the supplier and we will capture that information in the payment scheme by using manual payment option.

Quick:-     Through the Quick Payment type we can generate checks through the system and we can have the transactions directly in the system.

Refund:-   When ever company is going to give advance back to the customer that time we will select payment type as Refund.

Navigation steps for Payments:-

                 payments  ==> payments

For view list of payments:

           Select * from ap_invoice_payments_all;

           Select * from ap_payment_schedules_all;

For check’s information:

           Select * from ap_checks_all;

For check format:

           Select * from ap_check_formats;

           Select * from ap_checkrun_conc_processes_all;

Distribution Set:-     It is one of the option is available in Invoices Screen. While creating the Invoice we will attach distribution set. System will automatically create the transactions in distributions forms as per the distribution set.

 Navigation:

 set-up =>invoice=> distribution set

To view Distribution sets at header level:

           Select * from ap_distribution_sets_all;

To view Distribution sets at lines level:

           Select * from ap_distribution_set_lines_all;

Transferring Transactions from AP to GL:-

           We will execute the concurrent program from SRS Window. This program will transfer all the payment transactions into the G.L Module. It will take following parameters.

Program Name:-   Payables Transfer to General Ledger

Parameters:-

           Set of Books Name

           Transfer Reporting Book(s)

           From Date

           To Date

           Journal Category

Validate Accounts

Transfer To GL Interface

Submit Journal Import :  yes  (It should be always YES)

To view from AP to GL:

           Select * from gl_interface;

To view journal import details:

           Select * from gl_je_headers  à       for Headers

           Select * from gl_je_lines      à       for Lines

           Select * from gl_je_batches  à       for Batches

To view posting:

          

           Select * from gl_balances;

          

           After submitting the request select viewà output button. It will shows number of transactions has been transferred to G.L. then select G.L Module (General Ledger, Vision Operations (USA)).

SELECT * FROM GL_JE_HEADERS

SELECT * FROM GL_JE_LINES

SELECT * FROM GL_JE_BATCHES

SELECT * FROM GL_BALANCES

FlexSQL,FlexID Code in Reports

QUERY:-

SELECT AI.INVOICE_NUM,
               AID.DISTRIBUTION_LINE_NUMBER,
               &SELECT_ALL    FLEX_DATA
  FROM AP_INVOICES_ALL AI,
              AP_INVOICE_DISTRIBUTIONS_ALL AID,
              GL_CODE_COMBINATIONS  GCC
 WHERE AI.INVOICE_ID=AID.INVOICE_ID
     AND  GCC.CODE_COMBINATION_ID=AID.DIST_CODE_COMBINATION_ID
     AND AI.ORG_ID=:P_ORG_ID

WE HAD WRITTEN ONE LEXICAL PARAMETER AS &SELECT_ALL (IT IS NOTHING BUT PLACE HOLDER COLUMN(REPORT LEVEL)  AND IT CONTAINS SEGMENTS STRUCTURE AND DATATYPE AS CHAR AND  INITIAL VALUE AS -->VALUE IF NULL-->GCC.SEGMENT1) ).

NOW PLACE TWO FORMULA COLUMNS .... ONE FOR FLEX DATA AND ONE FOR FLEX DESC WHICH SHUD BE IN GROUP LEVEL BY USING FLEXIDVAL USEREXITS....

NOW WE WANT TO DISPLAY STRUCTURE  BY USING FNDFLEXSQL ... FOR DISPLAYING THE STRUCTURE WE SHUD WRITE CODE IN " BEFORE REPORT TRIGGER" WHICH IS SHOWN BELOW..................

BEFORE THIS WE SHUD CREATE TWO USER PARAMETERS i.e P_COA_ID AND P_CONC_REQUEST_ID...............

--FND FLEXSQL
--------------
BEGIN
SRW.USER_EXIT('FND SRWINIT');                               }
SRW.USER_EXIT('FND FLEXSQL                                  }
                             CODE="GL#"                              }-- THIS WE SHUD WRITE IN " BEFORE
                                                                                                  REPORT TRIGGER                      
                             NUM=":P_COA_ID"                      }-- THIS IS FOR DISPLAYING STRUCTURE AND RETRIVE THE DATA FROM DATABASE
         APPL_SHORT_NAME="SQLGL"
                       OUTPUT=":SELECT_ALL"
                TABLE_ALIAS="GCC"
                          MODE="SELECT"
                       DISPLAY="ALL" ');
RETURN(TRUE);
END;
------------------------------------------------------------------------------------------------------------
NOW WE WANT TO DISPLAY THE DATA WHICH IS PRESENT IN THE STRUCTURE .... FOR THAT WE USE  " FLEXID"

FOR THAT  PLACE TWO FORMULA COLUMNS IN GROUP LEVEL AND NAME IT  AS "CF_FLEXDATA" AND  "CF_FLEXDESC"

DATATYPE -- CHAR
WIDTH --1000
--FND FLEXID
-------------
"CF_FLEXDATA--->"

  SRW.USER_EXIT('FND FLEXIDVAL                                                    ---OBUL
                 CODE="GL#"
                 NUM=":P_COA_ID"
                 APPL_SHORT_NAME="SQLGL"
                 DATA=":FLEXDATA"
                 VALUE=":CF_DATA"
                 DISPLAY="ALL"
                 IDISPLAY="ALL" ');
              RETURN(:CF_DATA);                }
                                                         }    -- THIS WE SHUD WRITE IN FORMAULA COLUMN WHICH NAMED AS :CF_FLEXDATA
                                                         }
                                                         }
                       
                                          -- THESE TWO FOR DISPLAY DATA WICH IS PRESENT IN DAT FLEXSQL

-------------------------------------------------------

"CF_FLEXDESC"--->

    SRW.USER_EXIT('FND FLEXIDVAL                                              ---OBUL
                 CODE="GL#"
                 NUM=":P_COA_ID"
                 APPL_SHORT_NAME="SQLGL"
                 DATA=":FLEXDATA"
                 DESCRIPTION=":CF_DESC"
                 DISPLAY="ALL"
                 IDISPLAY="ALL" ');                                       }
                                                                                 }
                                                                                 }-- THIS IS ALSO WE SHUD WRITE IN FORMULA COLUMN WHICH NAMED AS :CF_FLEXDESC
                                                                               
               
         
                               

------ THESE 2 FORMULA COLUMNS WE SHUD TAKE IN GROUP LEVEL

for finding the CODE="GL#" -- GO TO APPLICATION DEVELOPER RESP---> FLEXFIELD-->REGISTER-->QUERY-->General Ledger



NOW AS USUAL WE REGISTER IT IN APPS.......

FOR DISPLAYING CHART_OF_ACCOUNTS_ID DYNAMICALLY WE SHUD USE DEFAULT TYPE AS SQL FRO P_COA_ID PARAMETER AND IN DEFAULT VALUE WRITE BELOW CODE...

SELECT CHART_OF_ACCOUNTS_ID FROM GL_SETS_OF_BO0OKS WHERE SET_OF_BOOKS_ID=:$PROFILES$.GL_SET_OF_BKS_ID

Nested Sub-Queries

A query with in a Sub-Query is nothing but Nested Sub Query.

Syntax:-

SQL>Select ,  … from  where  (select  from where  (select  from where ));  
 
Example:-
Display the Manager of ‘TURNER’ and display the ‘MANAGER’ of ‘TURNER’ manager.
 
SQL> select *from EMP where empno in (select mgr from EMP where empno in (select mgr from EMP where ename ='TURNER'));
 

Query for display the total no.of employees and year wise total no.of employees joined in to company

select count(*),count(decode(to_char(hiredate,'yyyy'),'1981',ename))"1981"
                ,count(decode(to_char(hiredate,'yyyy'),'1982',empno)) "1982"
                ,count(decode(to_char(hiredate,'yyyy'),'1983',empno)) "1983" from jagan;

Query for display the first two highest salaries in dept wise

select  deptno,sal,ename from emp e
where 2>(select count(d.sal) from emp d where e.deptno=d.deptno and e.sal<d.sal) order by 1,3 desc

Oracle Applications R12 Features

Oracle Applications R12 Features:
-------------------------------------------

1. MOAC (Multiorg Access Control):  By Using Single Responsibility we can be able to access multiple operating units at a time.

2. Mutiorg views were replaced with MOAC Based synonyms.

3. MO: Security Profile is a new profile added in R12.

4. VPD(Virtual Private Database) System will take care of Data security in R12

5.Org Initialization process in R12 :
  ----------------------------------
   begin
     mo_global.set_policy_context('S',204);
   end;
 

Here 204 is the org_id

  MOAC Synonym Initialization:
  ---------------------------------------
   begin
     FND_GLOBAL.APPS_INTIALIZE ('APPLICATION_ID','RESPONSIBILTY_ID,'USER_ID');
     MO_GLOBAL.INIT ('SQLAP'); -- Application Short Name
   end;

  Module wise Changes in Oracle Apps R12:
  -----------------------------------------------------
  General Ledger (GL):
  --------------------------
  1. Sub ledger Accounting module was added in R12.
  2. gl_sets_of_books table was replaced with gl_ledgers and gl_ledger_Sets.
  3. In Addition to Currency, Calendar, Chart of accounts,
     Convention was added in R12.

   Accounts Payable (AP):
   ------------------------------
   1. po_vendors tables replaced with ap_suppliers tables.
   2. ap_invoice_distributions_all table populates the data  when ever invoice gets accounted.
   3. Supplier form was converted from form based solution to web based solution.
   4. ap_invoice_lines_all table added in R12.
   5. Supplier and customer information was defined together Under TCA(Trading Community Architecture).
   6. Accounting Tables were modified.
   7. Both supplier and customer bank information was defined
     under payments (New application in R12) Application.
   
      11i Table               R12 Table
      -----------             ------------
      ap_ae_accounting_events xla_events

      po_vendor_sites_all     ap_supplier_sites_all
      po_vendors              ap_suppliers
      ap_banks                ce_banks
      ap_bank_branches        ce_bank_branches
      po_vendor_contacts      ap_supplier_contacts
      ap_ae_headers_all       xla_ae_headers
      ap_ae_lines_all         xla_ae_lines
   

Order Management (OM):
------------------------------------
  i. ra_customers ,ra_site_uses_all ,ra_adderesses_all views were removed in R12.
  ii. Move Order Fautures were added in R12.
  iii. In place of ra_customers,ra_site_uses_all,ra_addersses_all
  views need to use the below mentioned base tables.
 
hz_locations

hz_parties
hz_cust_accounts

hz_cust_acct_sites_all

hz_cust_site_uses_all
hz_party_sites
   

SQL * LOADER

SQL* Loader

           SQL* Loader is a tool in Oracle. It is used to transfer data from flat file to Oracle database tables.

There are 5 types of files in SQL * Loader given below.

1)      Flat File: -     It is also called as data File. It contains the data in specific format. This format may be fixed length or ‘,’ separated or variable format. Extensions of the flat files are ‘.txt’,’.dat’,’.csv’ (comma separated view) or Excel sheet and .sql.

2)      Control File: -          It is the SQL * Loader file or program. It is used to Load the data from Flat file to table. It contains flat file path and table name and column mappings. Extension of control file is ‘.ctl’.

Syntax of Control file:

OPTIONS(SKIP=1)

LOAD DATA

infile '*'

REPLACE/INSERT/APPEND into table table_name

fields terminated by ','

optionally enclosed by '"'

trailing nullcols

(

Column1,

Column2,

Column3,

Column n

 "REPLACE(:Column n,CHR(13),'')"

)

3)      Bad File: -     whenever we execute the control file, if SQL * Loader rejects the records then the bad file will be created automatically. Extension of bad file is ‘.bad’. Bad file will have the rejected records which are rejected by SQL * Loader. SQL * Loader will rejects the records if data is not incorrect format and if any internal error occurs.

4)      Discard File: - Discard file contains the records, which are rejected by control file. Control file will rejects the records if we write any conditions, system will check each record based on whether it is satisfies the condition or not . If record is not satisfying the condition it will come into the discard file. Extension for discard file is ‘.dis’.

5)      Log File: - Log File will be created automatically after completion of control file execution process. It contains the information like count of successful records and bad file discard file and so on. Extension of log file is ‘.log’.

SQL * Loader Methods: -     SQL *Loader methods are 3 types. They were shown below

INSERT: - In INSERT mode system will insert the data into the table, but table should be empty.

REPLACE: - In REPLACE mode system will delete or remove the existing data and inserts the new data into the table.

APPEND: - In APPEND mode system will add the new data for the existing data.

SQL * Loader Command:-  After completing the control file we will execute the control file by using following command in putty.

1.          Login to command prompt where the SQL *Loader was installed (Putty).

After setting the environment variable (.env file)

2.     sqlldr userid/password@database name press ENTER then

control = <control filename>

ex:-  c:\orant\bin>   sql ldr apps/apps@prod

      control  =  emp.ctl

1. Before going to work with SQL * Loader we should have to verify two  things
1. Data file
2. Table
2. Develop the control file as per the flat file and table format.
3. Go to the command prompt execute the control file by using following syntax.

Example:-

          Create table XXST_VENDOR_SITE_UPDATE (VENDOR_ID NUMBER,

                                                                               VENDOR_NAME VARCHAR2 (4000),

                                                                               SEGMENT1 VARCHAR2 (4000),

                                                                                VENDOR_SITE_ID NUMBER,

                                                                                VENDOR_SITE_CODE VARCHAR2 (4000),

                                                                                TERMS_DATE_BASIS VARCHAR2 (4000),

                                                                                TERMS_NAME VARCHAR2 (4000)

                                                                               );

Data file:-

VENDOR_ID	VENDOR_NAME	SEGMENT1	VENDOR_SITE_ID	VENDOR_SITE_CODE	TERMS_DATE_BASIS	Terms Name
2	Service Tax Authority(India)	100002	1	INDIA	Invoice Received	30 NET
3	VAT Authorities(India)	100003	4	INDIA	Goods Received	60 NET
4	TDS Authorities (India)	100004	41006	192B_INDV	Current	IMMEDIATE
6	Synechron Limited (UK)	100006	67	ST_UK	Goods Received	5 NET
2011	HDFC Bank Credit Card No. 4050 2810 0036 4759 ( Parag Deshpande)	101019	1985	IND_SITE	Invoice Received	10 NET
2012	Hammer Publishers Private Limited	101020	1986	IND_SITE	Current	20 NET
2013	Hindustan Advertising Agencies	101021	1987	IND_MH_SITE	Current	15 NET
2014	Hinjewadi Industries Association	101022	1988	IND_SITE	Goods Received	45 NET

Control file:-

OPTIONS(SKIP=1)

LOAD DATA

infile '*'

REPLACE into table XXST_VENDOR_SITE_UPDATE

fields terminated by ','

optionally enclosed by '"'

trailing nullcols

(

VENDOR_ID   ,

VENDOR_NAME ,

SEGMENT1    ,

VENDOR_SITE_ID    ,

VENDOR_SITE_CODE  ,

TERMS_DATE_BASIS,

TERMS_NAME

 "REPLACE(:TERMS_NAME,CHR(13),'')"

)

open the command prompt

sqlldr apps/apps@databasename control='$AP_TOP/bin/vendor_site_update.ctl' data='$AP_TOP/bin/vendor_site_update.csv' log='$AP_TOP/bin/vendor_site_update.log'

Example1:-

           Create table csv_emp (empno number(3), ename varchar2(100), joindate date, deptno number(3));

Control file:-

           Load data

           infile ‘c:/sloader\emp_details.csv’

           discard file ‘c:\sloader\emp_details.dis’

Insert into table csv_emp

Where deptno=’10’

Fields terminated by ‘,’

(startdate,enddate,deptno,dname,project)

Trialing NULL Columns:-   We can use this syntax into the null columns to insert null values if data file is not having data.

Syntax:-

           Load data

           infile ‘c:/sloader\emp_details.csv’

           discard file ‘c:\sloader\emp_details.dis’

Insert into table csv_emp

Where deptno=’10’

Fields terminated by ‘,’

Trailing null cols

(empno “sempno.nextval”,

 ename “init cap(:ename)”,

Deptno,

Joindate   sysdate)

Whenever the client gave data file in fixed format like (data is not separated by commas(‘,’)) tempo is first 5 digits, ename is 10 digits and deptno is 2 digits. Then the control file is looks like this

Load data

Infile ‘c:\sloader\fixed_emp.dat’

Insert into table fixed_emp

(empno position  (1:5);

 ename position (6:15);

 deptno position(16:17))

Inserting Data Into Multiple Tables:-

           load data

infile ‘c:\sloader\fixed.dat’

insert into table fixed_emp

when empno = ‘’

(emp no  position (1:5),

 ename position(6:15),

 dept no (16:17))

into table  fixed_dept

where dname = ‘’

(dname position (18:25).,

 project position (26:28),

startdate position(29 : 39)

Inserting Data into Single Table from Multiple Files:-

           LOAD DATA

infile ‘c:\first.dat’

infile ‘c:\second.dat’

insert into table fixed_emp

fieldes terminated by ‘,’

(empno, ename, deptno)

Without data file creating the control file:-

           Load data

Infile *

Insert into table fixed_emp

Fieldes terminated by ‘,’

(empno, ename, deptno)

           BEGIN DATA

           1014,operations,10

           1234,Scott,10

           456,urman,20

           789,sysadmin,10

          

FILLER: -      If you want to ignore complete data in a column we can used the ‘FILLER’. It is reserved word for SQL * Loader. System will not consider the particular column.

SQL * Loader program registration in Oracle Apps:-

1.     First we will develop the SQL* Loader file as per data file and table format.

2.     Then move this control file into the server.

3.     Connect to System Administrator Responsibility then create executable and execution method as SQL * Loader and execution file name as control file name without extinction.

4.     Create Concurrent Program and attach executable parameters and incompatibilities.

5.     Create Request Group and attach Concurrent Program.

6.     Create Responsibility and attach Request Group to responsibility.

7.     Create User and attach Responsibility to User.

8.     User will submit the Request from SRS Window.

SQL * Script Registration into Oracle Apps:-

1.     First we will develop the SQL* Script as per client requirement which has got SQL* Plus commands.

2.     Then we will move this ‘.sql’ file into the server.

3.     Create Executable with execution method as SQL* Plus and we will give the SQL filename.

4.     Create Concurrent Program and attach executable parameters and incompatibilities.

5.     Create Request Group attaches Concurrent Program.

6.     Create Responsibility and attach Request Group to Responsibility.

7.     Create User and attach Responsibility to User.

8.     User will submit the Request from SRS Window.

Develop the SQL* script save it as .sql file move this file into sql folder and create executable