SQL-Loader:Loading Fixed Length Data

Loading Fixed Length Data

FLAT FILE:

1  Nani        CSE Final  A+

2  Indra Kanth ECE Final  B

3  Kalyan      CSE Third  A

4  Praveen     IT  Third  B

5  Badra       ECE Final  B+

6  Kali           EEE Second C

7  Siri Giri   E&I First  D

8  Sravan      MCA Third  A

9  Pradeep     MCA Second D

10 Joe-Rohith  CSE Third  D+

CONTROL  FILE:

load data

infile '/bin/flat_file_fixed.txt'

append into table student_data

(

Sno  position(1:1)  char(2),

sname  position(4:14)  char(11),

branch  position(16:18)  char(3),

year  position(20:25)  char(6),

grade  position(27:28)  char(2)

)

Staging Table:

create table stu_data(sno number,

                                sname varchar2(20),

                                branch varchar2(10),

                                year varchar2(10),

                                grade varchar2(3)

                                )

SQL-Loader:Loading variable length data

Loading Variable Length Data

The flat file is of comma seperated file(i.e csv). In real time we may have flat files with the extensions .csv, .dat, .txt etc

 

FLAT FILE:

1,"Nani","CSE","Final","A+"

2,"Indra Kanth","ECE","Final","B"

3,"Kalyan","CSE","Third","A"

4,"Praveen","IT","Third","B"

5,"Badra","ECE","Final","B+"

6,"Kali","EEE","Second","C"

7,"Giri","E&I","First","D"

8,"Sravan","MCA","Third","A"

9,"Pradeer","MCA","Second","D"

10,"Joe","CSE","Third","D+"

CONTROL  FILE:

load data

infile '/bin/flat_file_var.txt'   --Give your flatfile location

insert into table stu_data    --stu_data is the staging table

fields terminated by ','

optionally enclosed by '"'

trailing nullcols

(

sno,

sname,

branch,

year,

grade

)

Staging Table:

create table stu_data(sno number,

                                sname varchar2(20),

                                branch varchar2(10),

                                year varchar2(10),

                                grade varchar2(3)

                                )

Basic Tables in PO

Some Basic Tables in PO Module

Requisition:

 po_requisition_headers_all

 po_requisition_lines_all

 po_req_distributions_all

Rfq and Quotations:

po_document_types_all

PO:

Po_headers_all,

Po_lines_all,

Po_line_locations_all,

Po_distributions_all

Receipt:

Rcv_shipment_headers

Rcv_shipment_lines

Invoice:

Ap_invoices_all

Ap_invoice_distributions_all

Payment:

Ap_invoice_payments_all

Ap_payment_schedules_all

Ap_checks_all

Journal:

Gl_je_headers

Gl_je_lines

Some other basic tables that  you may require:

Po_vendors  

Po_vendor_sites_all

Po_lookup_codes
Ap_lookup_codes
Mtl_system_items_b 

Gl_code_combinations

Pulling PO details on PO number

Tables Used,
1)PO_HEADERS_ALL
2)PO_LINES_ALL
3)PO_LINE_LOCATIONS_ALL
4)PO_DISTRIBUTIONS_ALL
5)PO_LOOKUP_CODES
6)PO_VENDORS
7)PO_VENDOR_SITES_ALL
8)HR_LOCATIONS
9)PER_PEOPLE_F
10)MTL_SYSTEM_ITEMS_B

SELECT PHA.SEGMENT1 "PO_NUMBER",
       PLC.DESCRIPTION "TYPE",
       PV.VENDOR_NAME "SUPPLIER",
       PVSA.VENDOR_SITE_CODE "SITE",
       H1.LOCATION_CODE "SHIP_TO",
       H2.LOCATION_CODE "BILL_TO",
       PHA.CURRENCY_CODE "CURRENCY",
       PPF.FULL_NAME "BUYER",
       PHA.AUTHORIZATION_STATUS "STATUS",
       PLA.PO_LINE_ID,
       PLA.LINE_NUM,
       PLA.PURCHASE_BASIS "LINE_TYPE",
       MSIB.SEGMENT1"ITEM",
       PLA.ITEM_DESCRIPTION,
       PLA.UNIT_MEAS_LOOKUP_CODE "UOM",
       PLA.QUANTITY,
       PLA.UNIT_PRICE "PRICE",
       PLLA.LINE_LOCATION_ID,
       H3.LOCATION_CODE "LINE_SHIP_TO",
       PLLA.QUANTITY "lINE_LOCATION_QTY",
       PDA.PO_DISTRIBUTION_ID,
       PDA.REQ_HEADER_REFERENCE_NUM "REQ_NUM",
       GCC.SEGMENT1||'-'||GCC.SEGMENT2||'-'||GCC.SEGMENT3||'-'||GCC.SEGMENT4||'-'||GCC.SEGMENT5 "CHARGE_ACCOUNT"
  FROM PO_HEADERS_ALL PHA,
       PO_LINES_ALL PLA,
       PO_LINE_LOCATIONS_ALL PLLA,
       PO_DISTRIBUTIONS_ALL PDA,
       PO_LOOKUP_CODES PLC,
       PO_VENDORS PV,
       PO_VENDOR_SITES_ALL PVSA,
       HR_LOCATIONS H1,
       HR_LOCATIONS H2,
       HR_LOCATIONS H3,
       PER_PEOPLE_F PPF,
       GL_CODE_COMBINATIONS GCC,
       MTL_SYSTEM_ITEMS_B MSIB
 WHERE PHA.PO_HEADER_ID=PLA.PO_HEADER_ID
   AND UPPER(PHA.TYPE_LOOKUP_CODE)=UPPER(PLC.LOOKUP_CODE)
   AND PLC.LOOKUP_TYPE='PO TYPE'
   AND PHA.VENDOR_ID= PV.VENDOR_ID
   AND PHA.VENDOR_SITE_ID=PVSA.VENDOR_SITE_ID
   --AND PV.VENDOR_ID=PVSA.VENDOR_ID
   AND PHA.SHIP_TO_LOCATION_ID=H1.LOCATION_ID
   AND PHA.BILL_TO_LOCATION_ID=H2.LOCATION_ID
   AND PLLA.SHIP_TO_LOCATION_ID=H3.LOCATION_ID
   AND PPF.PERSON_ID=PHA.AGENT_ID
   AND PLA.ITEM_ID=MSIB.INVENTORY_ITEM_ID
   AND PLA.ORG_ID=MSIB.ORGANIZATION_ID
   AND PLA.PO_LINE_ID=PLLA.PO_LINE_ID
   AND PLLA.LINE_LOCATION_ID=PDA.LINE_LOCATION_ID
   AND GCC.CODE_COMBINATION_ID=PDA.CODE_COMBINATION_ID
   AND PHA.SEGMENT1='Enter your PO Number'  
  

Pulling Requisition Details on Requisition Number

Tables Used
1)PO_REQUISITION_HEADERS_ALL
2)PO_REQUISITION_LINES_ALL
3)PO_REQ_DISTRIBUTIONS_ALL
4)PO_LOOKUP_CODES
5)MTL_SYSTEM_ITEMS_B
6)GL_CODE_COMBINATION
7)PER_PEOPLE_F

SELECT PRHA.SEGMENT1 "REQUISITION_NUMBER",
       PLC.DESCRIPTION "REQ_TYPE",
       PRHA.AUTHORIZATION_STATUS "REQ_STATUS",
       PPF.FULL_NAME "PREPARER",
       PRLA.LINE_NUM,
       PRLA.PURCHASE_BASIS "TYPE",
       MSIB.SEGMENT1 "ITEM",
       PRLA.ITEM_DESCRIPTION,
       PRLA.UNIT_MEAS_LOOKUP_CODE "UOM",
       PRLA.QUANTITY"LINE_QTY",
       PRLA.UNIT_PRICE "PRICE",
       PRLA.NEED_BY_DATE "NEED-BY",
       PRDA.DISTRIBUTION_ID,
       PRDA.REQ_LINE_QUANTITY "DISTRIBUTION_LINE_QTY",
       GCC.SEGMENT1||'-'||GCC.SEGMENT2||'-'||GCC.SEGMENT3||'-'||GCC.SEGMENT4||'-'||GCC.SEGMENT5 "CHARGE_ACCOUNT"
  FROM PO_REQUISITION_HEADERS_ALL PRHA,
       PO_REQUISITION_LINES_ALL PRLA,
       PO_REQ_DISTRIBUTIONS_ALL PRDA,
       PO_LOOKUP_CODES PLC,
       MTL_SYSTEM_ITEMS_B MSIB,
       GL_CODE_COMBINATIONS GCC,
       PER_PEOPLE_F PPF
 WHERE PRHA.REQUISITION_HEADER_ID=PRLA.REQUISITION_HEADER_ID
   AND PRDA.REQUISITION_LINE_ID=PRLA.REQUISITION_LINE_ID
   AND MSIB.INVENTORY_ITEM_ID=PRLA.ITEM_ID
   AND GCC.CODE_COMBINATION_ID=PRDA.CODE_COMBINATION_ID
   AND PPF.PERSON_ID=PRHA.PREPARER_ID
   AND PRHA.ORG_ID=MSIB.ORGANIZATION_ID
   AND UPPER(PRHA.TYPE_LOOKUP_CODE)=UPPER(PLC.LOOKUP_CODE)
   AND PLC.LOOKUP_TYPE='REQUISITION TYPE'
   AND PRHA.SEGMENT1='Enter your Requisition Number'
  
  

concurrent_programs & executables that are created by a user

Tables used
1)FND_USER--Holds user Information.
2)FND_CONCURRENT_PROGRAMS_VL-Holds the concurrent programs defined in the system.

3)FND_EXECUTABLES_VL-Holds the concurrent executables information.

 

SELECT FU.USER_NAME,
       FCPL.USER_CONCURRENT_PROGRAM_NAME,                                                          FCPL.DESCRIPTION,
       FEV.USER_EXECUTABLE_NAME,
       FEV.EXECUTABLE_NAME,
       FEV.EXECUTION_FILE_NAME 
  FROM FND_USER FU,  
       FND_CONCURRENT_PROGRAMS_VL FCPL,
       FND_EXECUTABLES_VL FEV 
 WHERE FCPL.EXECUTABLE_ID=FEV.EXECUTABLE_ID 
   AND FEV.CREATED_BY=FU.USER_ID 
   AND FU.USER_NAME='Enter User_name'

User_name and the responsibilities to which he assigned

Tables used
1)FND_USER--holds the information of a user.
2)FND_RESPONSIBILITY--holds the information of a responsibility
3)FND_USER_RESP_GROUPS_ALL--holds the information regarding   
                            resposibility assignments to the users.

SELECT FU.USER_ID,
       FU.USER_NAME,
       FR.RESPONSIBILITY_KEY 
  FROM FND_USER FU, 
       FND_RESPONSIBILITY FR,
       FND_USER_RESP_GROUPS_ALL FUR 
WHERE  FR.RESPONSIBILITY_ID=FUR.RESPONSIBILITY_ID 
  AND  FU.USER_ID=FUR.USER_ID
  AND  FU.USER_NAME=UPPER('Enter USER_NAME');