Example of SQL DATA LOADER

As Salamo alaikum wa rahmatullah

I have created one control file by the name stk.ctl which has following parameter
---------------------------

LOAD DATA
INFILE "D:/upload/stk.csv"
BADFILE "D:/upload/stk.bad"
DISCARDFILE "D:/upload/stk.dsc"
Insert into table tbl_mst_stock 
Fields terminated by "," 

(stock_id,stk_code,stk_desc,created_dt date 'mm/dd/yyyy',updated_dt date 'mm/dd/yyyy')

After that open the command prompt and put the following command
c:/>sqlldr userid=scott@orcl/tiger  control=stk.ctl log=stk.log <etner>

--
MA Asalaam
Passion for Oracle

Create sequence in oracle

As salamo alaikum wa rahmatullah

CREATE SEQUENCE customers_seq
 START WITH     1000
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

--

Ma Asalaam

Passion for Oracle

Package Example in Oracle

As salamo alaikum wa rahmatullah

create or replace package pck_test
is
procedure prc_inst(stc in varchar2,sdesc in varchar2,errcode out varchar2,errbuf out varchar2);
procedure prc_upd(id in number,stc in varchar2,sdesc in varchar2,errcode out varchar2,errbuf out varchar2);
procedure prc_del(id in number,errcode out varchar2,errbuf out varchar2);
end pck_test;

create or replace package body pck_test
is
procedure prc_inst(stc in varchar2,sdesc in varchar2,errcode out varchar2,errbuf out varchar2) 
is
begin
insert into tbl_mst_stock(stk_code,stk_desc,created_dt,updated_dt)
values(stc,sdesc,sysdate,sysdate);
commit;
errcode:='0';
errbuf:='row inserted successfully';
exception
when dup_val_on_index then
errcode:='1';
errbuf:= 'duplicate key found';
end prc_inst;

procedure prc_upd(id in number,stc in varchar2,sdesc in varchar2,errcode out varchar2,errbuf out varchar2) is
begin
update tbl_mst_stock
set stk_code=stc, stk_desc=sdesc, updated_dt=sysdate where stock_id=id;
errcode:='0';
errbuf:='record updated successfully';
exception
when others then
errcode:='1';
errbuf:= 'duplicate key found';
end prc_upd;

procedure prc_del(id in number,errcode out varchar2,errbuf out varchar2) is
begin
delete from tbl_mst_stock 
where stock_id=id;
commit;
errcode:='0';
errbuf:='record deleted successfully';
exception
when others then
errcode:='1';
errbuf:='no row exist';
end prc_del;


end pck_test;

--

Ma Asalaam

Passion for Oracle

Variable example in Oracle SQL

As Salamo alaikum wa rahmatullah

sql> var rc varchar2(100);
sql>begin :rc:='Passion for Oracle'; end;
Sql>/
Sql> pl/sql Procedure successfully completed
Sql> print rc
Sql> rc
--------------
Passion for Oracle 


---
Ma Asalaam
Passion for Oracle

Package With Function

As salamo alaikum wa rahmatullah

Example of package which has function

create or replace package pck_fuc
as
function today_date  return date ;
end pck_fuc;

create or replace package body pck_fuc
as

function today_date return date
is 
dt date;
begin
select sysdate into dt from dual;
return(dt);
end today_date;
end pck_fuc;
show errors

declare 
d date;
begin
d:=pck_fuc.today_date();
dbms_output.put_line(d);

end;

--

Ma Asalaam
Passion for Oracle

Package with Procedure In Out Parameter Example

As salamo alaikum wa rahmatullah,



create or replace package test_proc2
AS
Procedure show_name(ucod in varchar2,fnm out varchar2,lnm out varchar2);
Procedure show_phone(ucod in varchar2,ph out varchar2);
Procedure show_email(ucod in varchar2,eml out varchar2);
end test_proc2;

create or replace package body test_proc2
as
Procedure show_name(ucod in varchar2,fnm out varchar2,lnm out varchar2)
as
Begin
Select Fname,Lname into fnm,lnm from tbl_mst_user where ucode=ucod; 
End show_name;
Procedure show_phone(ucod in varchar2,ph out varchar2)
as 
Begin
Select phone into ph from tbl_mst_user where ucode=ucod;
end show_phone;
Procedure show_email (ucod in varchar2,eml out varchar2)
is
begin
select email into eml from tbl_mst_user where ucode=ucod;
end show_email;
end test_proc2;

set serveroutput on;

declare
f varchar2(100);
l varchar2(100);
begin

TEST_PROC2.SHOW_NAME('u001',f,l);
dbms_output.put_line(f||'  '||l);
end;

declare
ph varchar2(100);
begin

TEST_PROC2.show_phone('u001',ph);
dbms_output.put_line(ph);
end;


declare
em varchar2(100);
begin

TEST_PROC2.Show_email('u001',em);
dbms_output.put_line(em);

end;


--
Ma Asalaam
Passion for Oracle

Simple Cursor example for Chart of Account

As salamo alaikum wa rahmatullah

Set Serveroutput on;
declare
cursor my_cursor is select * from GLFV_charts_of_accounts;
begin
for item in my_cursor
loop
 DBMS_OUTPUT.PUT_LINE(item.CHART_OF_ACCOUNTS_NAME);
end loop;
end;

--
Ma Asalaam
Passion for Oracle

Important Interface Tables in Oracle Apps

As salamo alaikum wa rahmatullah

GL INTERFACE TABLES

GL_BUDGET_INTERFACE
GL_DAILY_RATES_INTERFACE
GL_IEA_INTERFACE
GL_INTERFACE
GL_INTERFACE_CONTROL
GL_INTERFACE_HISTORY 

AP INTERFACE TABLES 

AP_INTERFACE_CONTROLS
AP_INTERFACE_REJECTIONS
AP_INVOICES_INTERFACE
AP_INVOICE_LINES_INTERFACE

AR INTERFACE TABLES

AR_PAYMENTS_INTERFACE_ALL
AR_TAX_INTERFACE
HZ_DQM_SYNC_INTERFACE
HZ_PARTY_INTERFACE
HZ_PARTY_INTERFACE_ERRORS
RA_CUSTOMERS_INTERFACE_ALL
RA_INTERFACE_DISTRIBUTIONS_ALL
RA_INTERFACE_ERRORS_ALL
RA_INTERFACE_LINES_ALL
RA_INTERFACE_SALESCREDITS_ALL

FA INTERFACE TABLES 

FA_BUDGET_INTERFACE
FA_INV_INTERFACE
FA_PRODUCTION_INTERFACE
FA_TAX_INTERFACE 

INVENTORY INTERFACE TABLES 

MTL_CC_ENTRIES_INTERFACE
MTL_CC_INTERFACE_ERRORS
MTL_CI_INTERFACE
MTL_CI_XREFS_INTERFACE
MTL_COPY_ORG_INTERFACE
MTL_CROSS_REFERENCES_INTERFACE
MTL_DEMAND_INTERFACE
MTL_DESC_ELEM_VAL_INTERFACE
MTL_EAM_ASSET_NUM_INTERFACE
MTL_EAM_ATTR_VAL_INTERFACE
MTL_INTERFACE_ERRORS
MTL_INTERFACE_PROC_CONTROLS
MTL_ITEM_CATEGORIES_INTERFACE
MTL_ITEM_CHILD_INFO_INTERFACE
MTL_ITEM_REVISIONS_INTERFACE
MTL_ITEM_SUB_INVS_INTERFACE
MTL_OBJECT_GENEALOGY_INTERFACE
MTL_RELATED_ITEMS_INTERFACE
MTL_RESERVATIONS_INTERFACE
MTL_RTG_ITEM_REVS_INTERFACE
MTL_SECONDARY_LOCS_INTERFACE
MTL_SERIAL_NUMBERS_INTERFACE
MTL_SO_RMA_INTERFACE
MTL_SYSTEM_ITEMS_INTERFACE
MTL_TRANSACTIONS_INTERFACE
MTL_TRANSACTION_LOTS_INTERFACE
MTL_TXN_COST_DET_INTERFACE

PO INTERFACE TABLES

 PO_DISTRIBUTIONS_INTERFACE
PO_HEADERS_INTERFACE
PO_INTERFACE_ERRORS
PO_LINES_INTERFACE
PO_REQUISITIONS_INTERFACE_ALL
PO_REQ_DIST_INTERFACE_ALL
PO_RESCHEDULE_INTERFACE
RCV_HEADERS_INTERFACE
RCV_LOTS_INTERFACE
RCV_SERIALS_INTERFACE
RCV_TRANSACTIONS_INTERFACE

BOM INTERFACE TABLES 

BOM_BILL_OF_MTLS_INTERFACE
BOM_INTERFACE_DELETE_GROUPS
BOM_INVENTORY_COMPS_INTERFACE
BOM_OP_RESOURCES_INTERFACE
BOM_OP_ROUTINGS_INTERFACE
BOM_OP_SEQUENCES_INTERFACE
BOM_REF_DESGS_INTERFACE
BOM_SUB_COMPS_INTERFACE
CST_COMP_SNAP_INTERFACE
CST_INTERFACE_ERRORS
CST_ITEM_COSTS_INTERFACE
CST_ITEM_CST_DTLS_INTERFACE
CST_PC_COST_DET_INTERFACE
CST_PC_ITEM_COST_INTERFACE

WIP INTERFACE TABLES

WIP_COST_TXN_INTERFACE
WIP_INTERFACE_ERRORS
WIP_JOB_DTLS_INTERFACE
WIP_JOB_SCHEDULE_INTERFACE
WIP_MOVE_TXN_INTERFACE
WIP_SCHEDULING_INTERFACE
WIP_TXN_INTERFACE_ERRORS TABLE

ORDER MANAGEMENT INTERFACE TABLES

SO_CONFIGURATIONS_INTERFACE
SO_HEADERS_INTERFACE_ALL
SO_HEADER_ATTRIBUTES_INTERFACE
SO_LINES_INTERFACE_ALL
SO_LINE_ATTRIBUTES_INTERFACE
SO_LINE_DETAILS_INTERFACE
SO_PRICE_ADJUSTMENTS_INTERFACE
SO_SALES_CREDITS_INTERFACE
SO_SERVICE_DETAILS_INTERFACE
WSH_DELIVERIES_INTERFACE
WSH_FREIGHT_CHARGES_INTERFACE
WSH_PACKED_CONTAINER_INTERFACE 

Cash Management 

CE_STATEMENT_HEADERS_INT
CE_STATEMENT_LINES_INTERFACE

--

Ma Asalaam

Passion for Oracle

Key Tables of General Ledger in Oracle Apps

As Salamo alaikum wa rahmatullah

1- GL_SETS_OF_BOOKS

2- GL_IMPORT_REFERENCES

3- GL_DAILY_RATES

4- GL_JE_LINES

5- GL_PERIODS

6- GL_JE_HEADERS

7- GL_JE_BATCHES

8- GL_BALANCES

9- GL_CODE_COMBINATIONS

GL_SETS_OF_BOOKS

Stores information about the sets of books you define in your Oracle General Ledger application. Each row includes the set of books name, description, functional currency, and other information. This table corresponds to the Set of Books form.

GL_IMPORT_REFERENCES

Stores individual transactions from subledgers that have been summarized into Oracle General Ledger journal entry lines through the Journal Import process. You can specify the journal entry sources for which you want to maintain your transaction’s origin by entering ’Yes’ in the Import Journal References field of the Journal Sources form.
For each source that has Import Journal References set to ’Yes’, Oracle General Ledger will populate GL_IMPORT_REFERENCES with one record for each transaction in your feeder system.

GL_DAILY_RATES

Stores the daily conversion rates for foreign currency transactions. It replaces the GL_DAILY_CONVERSION_RATES table. It stores the rate to use when converting between two currencies for a given conversion date and conversion type. Each row in this table has a corresponding inverse row in which the from and to currencies are switched.
For example, if this table contains a row with a from_currency of YEN, a to_currency of CND, a conversion_type of Spot, and a conversion_date of January 1, 1997, it will also contain a row with a from_currency of CND, a to_currency of YEN, a conversion_type of Spot, and a conversion_date of January 1, 1997.
In general, this row will contain a rate that is the inverse of the matching row. One should never insert directly into this table. They should instead insert into the DAILY_RATES_INTERFACE table. Data inserted into the GL_DAILY_RATES_INTERFACE table will be automatically copied into this table.

GL_JE_LINES

Stores the journal entry lines that you enter in the Enter Journals form. There is a one–to–many relationship between journal entries and journal entry lines. Each row in this table stores the associated journal entry header ID, the line number, the associated code combination ID, and the debits or credits associated with the journal line. STATUS is ’U’ for unposted or ’P’ for posted.

GL_PERIODS

Stores information about the accounting periods you define using the Accounting Calendar form. Each row includes the start date and end date of the period, the period type, the fiscal year, the period number, and other information. There is a one–to–many relationship between a row in the GL_PERIOD_SETS table and rows inthis table.

GL_JE_HEADERS

Stores journal entries. There is a one–to–many relationship between journal entry batches and journal entries. Each row in this table includes the associated batch ID, the journal entry name and description, and other information about the journal entry. This table corresponds to the Journals window of the Enter Journals form. STATUS is ’U’ for unposted, ’P’ for posted. Other statuses indicate that an error condition was found. CONVERSION_FLAG equal to ’N’ indicates that you manually changed a converted amount in the Journal Entry Lines zone of a foreign currency journal entry. In this case, the posting program does not re–convert your foreign amounts. This can happen only if your user profile option MULTIPLE_RATES_PER_JE is ’Yes’. BALANCING_SEGMENT_VALUE is null if there is only one balancing segment value in your journal entry. If there is more than one, BALANCING_SEGMENT_VALUE is the greatest balancing segment value in your journal entry.

GL_JE_BATCHES

Stores journal entry batches. Each row includes the batch name, description, status, running total debits and credits, and other information. This table corresponds to the Batch window of the Enter Journals form. STATUS is ’U’ for unposted, ’P’ for posted, ’S’ for selected, ’I’ for in the process of being posted. Other values of status indicate an error condition. STATUS_VERIFIED is ’N’ when you create or modify an unposted journal entry batch.
The posting program changes STATUS_VERIFIED to ’I’ when posting is in process and ’Y’ after posting is complete.

GL_BALANCES

Stores actual, budget, and encumbrance balances for detail and summary accounts. This table stores functional currency, foreign currency, and statistical balances for each accounting period that has ever been opened.
ACTUAL_FLAG is either ’A’, ’B’, or ’E’ for actual, budget, or encumbrance balances, respectively. If ACTUAL_FLAG is ’B’, then BUDGET_VERSION_ID is required. If ACTUAL_FLAG is ’E’, then ENCUMBRANCE_TYPE_ID is required.
GL_BALANCES stores period activity for an account in the PERIOD_NET_DR and PERIOD_NET_CR columns. The table stores the period beginning balances in BEGIN_BALANCE_DR and BEGIN_BALANCE_CR.
An account’s year–to–date balance is calculated as BEGIN_BALANCE_DR – BEGIN_BALANCE_CR + PERIOD_NET_DR – PERIOD_NET_CR. Detail and summary foreign currency balances that are the result of posted foreign currency journal entries have TRANSLATED_FLAG set to ’R’, to indicate that the row is a candidate for revaluation.
For foreign currency rows, the begin balance and period net columns contain the foreign currency balance, while the begin balance and period net BEQ columns contain the converted functional currency balance. Detail foreign currency balances that are the result of foreign currency translation have TRANSLATED_FLAG set to ’Y’ or ’N’. ’N’ indicates that the translation is out of date (i.e., the account needs to be re–translated). ’Y’ indicates that the translation is current.
Summary foreign currency balances that are the result of foreign currency translation have TRANSLATED_FLAG set to NULL. All summary account balances have TEMPLATE_ID not NULL. The columns that end in ADB are not used. Also, the REVALUATION_STATUS column is notused.

GL_CODE_COMBINATIONS

Stores valid account combinations foreach Accounting Flexfield structure within your Oracle General Ledger application. Associated with each account are certain codes and flags, including whether the account is enabled, whether detail posting ordetail budgeting is allowed, and others. Segment values are stored in the SEGMENT columns. Note that each Accounting Flexfield structure may use different SEGMENT columns within the table to store the flexfield value combination. Moreover, the SEGMENT columns that are used are not guaranteed to be in any order. The Oracle Application Object Library table FND_ID_FLEX_SEGMENTS stores information about which column in this table is used for each segment of each Accounting Flexfield structure. Summary accounts have SUMMARY_FLAG = ’Y’ and TEMPLATE_ID not NULL. Detail accounts have SUMMARY_FLAG = ’N’ and TEMPLATE_ID NULL.

--
Ma Asalaam

Passion for Oracle

Oracle SQL*Loader

As salamo alaikum wa rahmatullah

If you are using oracle database at the same time you need to upload data into oracle table from text file.
Here is sample example how to upload the data from the text file.
For example, you have table named Employee which has columns Empno,Ename,Hiredate,Deptno.
I created a text file name employee.txt here under
1200,'Clark','12-Dec-1980',20
1201,'Alen','1-Jan-1985',30
1202,'Ramanivash','2-Feb-1990',40 
now employee text file is ready, after that i have to create a control file.
My control file name is example.ctl and the content is here under
load data
infile 'D:\upload\employee.txt'
into table employee
fileds terminated by ","
(Empno,Ename,Hiredate,Deptno)

Thanking you
--
Ma Asalaam
Mohammad Shahnawaz
Passion for Oracle