Play this when get lazy for work... It will change ur mode...

Just wanted to share this with you friends and readers, as when getting bore playing this to cheer me and give some more energy for work...

http://www.google.com/doodles/30th-anniversary-of-pac-man

Auto Spare Parts, Stock Tracking System

Here comes a new database design for Auto Spare Parts Business...

The software comes with following really reliable and effective features...

• Auto Spare Parts Stock Detail
• Car wise Spare Parts Detail
• Spare Parts search system, the address of item
• Part number, name and type wise search
• Car Wise Income Report
• Item Wise Income Report
• Auto Quantity Maintenance after purchase or sale of Items
• Sale Invoice and Customer Record Maintenance

Recovery from new Virus of USB Drive

Yesterday my cousin came to me with a problem that when he opens his USB drive in my computer it shows  nothing but below image:

When he tries to open the shown shortcut it shows nothing and the data is still there...

If you notice the banner of Microsoft Security Essentials, it shows and removes the virus but the files seems to be deleted from the USB Drive...

Simply copy the USB Drive and paste it any where in your system drives...  Better if you create new folder...

Now enable the hidden files and folders from folder options as shown below...

Go to views tab as shows below...

Select Show Hidden Files, Folders and Drives
and click on 'OK'
Now if you go the the folder where you copied the files, under the name of your USB Drive folder...

Open the folder and you will find the following files and folder...

The drive sign which is highlighted, if you open it... Bingo... You will find you files there...

Simply Cut and paste the files from it to your desktop or anywhere you like and format the USB Drive, quick format will also work...

And you are done with....

Paste the files back into your USB Drive and done...

I hope the the posting will be helpful to the readers...

How Triggers work in Oracle...

Lets talk about triggers as I mentioned in my last post that I will come online to write about this feature of PL/SQL is to make you understand that how much easily you can control things in your database...

Okay now lets talk about below trigger that what really its doing...

If you remember about my previous posting and especially about Stock Tracking System... The question is that how it will automatically track and keep record of stock after each and every sale, purchase and wastage if any.  Also sale return and purchase return puts effect on stock...

Lets see below trigger and talk about it...


create or replace trigger price_history
before update of cost_price
on stock
for each row
begin
insert into price_history
values
(:old.stcode,:old.stname,:old.capacity,:old.qty,:old.cost_price,:old.open_price,:old.sal_qty);
end;
/

Don't be confused about this trigger as I was when saw this coding for the first time...

create or replace trigger price_history

it will create trigger and will overwrite the trigger name 'price_history', if one already there...

before update of cost_price

Here we are asking oracle to fire the trigger before changes on cost_price are saved...

on stock

The table name in which changes are being made...

for each row

The trigger will fire for each row, whether you change one record or many...

begin

after this part the trigger body will start...

insert into price_history

It will insert record in table name price_history which is already created in the database...

values
part of command...

(:old.stcode,:old.stname,:old.capacity,:old.qty,:old.cost_price,:old.open_price,:old.sal_qty);
if you note I am using record type as old because I want to record the history of cost_price of items...

end;

Will close the trigger...

Whenever the cost_price of existing item is updated or changed, the trigger will fire and it will add old details of the items with cost_price to table named price_history...

Thank you for your time and next time we will meet for something complex to calculate data...

Placing Buttons in Oracle Forms 6i

Okay apologies for the delay but here I am with method to add buttons to your forms...

Above screen with form field but without buttons on it...

Simply click on new block and create the block but create it manually because you have to enter the buttons in this block.  Better if you name this block as 'BUTTONS'...

Simply click on the name of block and change it to 'BUTTONS' as shown above...

Design button as shown above, from tools menu shown at left of form...

Rename the button as per your requirement...

Okay now if simply selecting the button and pressing 'Ctrl+D' will duplicate the buttons...
Duplicate as many as you require...

After renaming them place them where ever you want on your form...

Right click on button and click on PL/SQL Editor which will give you below screen to enter the codes...

Now please note the coding for different buttons given below...

for INSERT Button:


DECLARE
A NUMBER;
BEGIN
GO_BLOCK('VENDOR');
CLEAR_BLOCK(NO_COMMIT);
SELECT NVL(MAX(VID),0) INTO A FROM TRAINING.VENDOR;
:VENDOR.VID:=(A+1);
GO_ITEM('VENDOR.VNAME');
END;

Let me explain to you the Insert Button coding above.
This is a body of the trigger which will be fired when you will click on the Insert button.  What button will execute is that it will go to the block named 'Vendor' and will clear the block for, if you have inserted anything by mistake or intentionally in the form fields.  Then it will execute the select statement of Oracle Database which will store highest number of Vendor Id into a (which is our variable to store number temporary so that we can perform additional calculations on that number) and then at next level it will put higher number + 1 into vid field automatically.  Every time when you save and make an addition to the record it will increase.  After inserting max number into vid field it will jump to vendor name.

for DELETE button:

GO_BLOCK('VENDOR');

DELETE_RECORD;

for SAVE Button:

COMMIT;

for EXIT Button:

GO_BLOCK('VENDOR');

CLEAR_BLOCK(NO_COMMIT);

EXIT_FORM;

for Query Button:

GO_BLOCK('VENDOR');

EXECUTE_QUERY;

for Clear Button:

GO_BLOCK('VENDOR');

CLEAR_BLOCK(NO_COMMIT);

I hope that you will do practice this method and will make yourself more and more efficient with this.

Please do contact me if you have any problems, and yes the next thing we will talk about is trigger which plays a great and important role in Oracle Database to automatically calculate things and make changes to data when you Insert, Delete or Update the records.

See you soon again for further training...

Billing System for Star Link Graphics & Advertising Solutions

The software is designed for billing of Graphics and Printing work, which automatically keeps track of invoices and vendor balances and the payments that are received and business expenses made.

End of the day it gives reports based on daily, weekly and monthly progress of the printing jobs for owner to know about what is paid and received and what really the owner getting from the business.

Designing with Oracle (Database and Forms)

How to create your first database software with Oracle Database 11g and 6i forms...

I know that 6i forms are old enough but to work with but believe me that still they are awesome and really efficient to design any kind of Database Application...

I will show you in steps that how to:

·         Plan your database

·         Write Database Script for designing tables

·         Design Forms

First 'Plan your Database':

You must think about all the aspects of designing a database for your client, better if you can get some written information like copies of sample stock reports (manually maintained), expense sheets etc...

After you finish with your client sit back relax and think about putting the raw information into tables... for example:

If you need to record vendors of the business and you are trying to record the following fields:

Vendor Table:

Vid

vname

contact

contperson

address

city

email

debbalance

crebalance

where:

Vid	Vendor ID
Vname	Vendor name
Contact	Vendor's contact number
Contperson	The person we directly contact at vendor (our contact)
Address	Vendor's address
City	The city from which our vendor belongs
Email	Email address of the vendor
Debbalance	Debit Balance of the vendor
Crebalance	Credit Balance of the vendor

Now if you notice each and every information about vendor 'ABC' will be recorded once.

Next record the purchase information, now the case will get different in the database as I will show the purchase transaction once but the stock items can be more than one...so i will try to design the table with relationships like this:

Purchase Table:

Purno

pdate

vid

vname

Amount

where:

Purno	PurchaseID
Pdate	Date of purchase
Vid	ID of the vendor
Vname	Name of the Vendor
Amount	Total amount of items purchased

I think I know what you are thinking that where the client will record the purchased item record, information about the purchased items.  The next table will be recording stock...

Purchase_Item Table:

Purno

sno

Stcode

stname

capacity

company

made

Rate

Qty

Amount

Purno	Same PurcahseID (in this table to put a foreign key)
Sno	Serial no.
Stcode	Stock Code
Stname	Stock Name
Capacity	Details of the stock (length, weight etc)
Company	Which company item or stock
Made	Which country make
Rate	Purchase price of the item
Qty	How much quantity we are about to purchase
Amount	Total of quantity * rate = this column

Now if you notice and as I mentioned earlier, under purchase transaction I can purchase more than one item into my stock... so the entries in the database that are recorded once will be recorded in first table 'Purchase', but the next table will record multiple rows of records.  Except purno in 'Purchase_Item' table as it will be reference to connect with 'Purchase' which is parent table...

Furthermore after explaining the procedure of tables that how it works, now it comes to give u the script for designing tables...but with forms design I would like to explain the different constraints of tables...mostly primary key and foreign key is used...

The first table of vendor coding will be:

create table vendor(vid number, vname varchar2(100), contact number, contperson varchar2(100), address varchar2(255), city varchar2(100), email varchar2(100), debbalance number(8,2), crebalance number(8,2), constraint pk_vendor_vid primary key (vid));

Now explanation of the table...

create table - (command to create table)

vendor - (table name defined)

vid number, vname varchar2(100) - (column names and data types to define whether the data type recorded will be number or text)

constraint pk_vendor_vid primary key (vid) - (constraint name, type and the column that will be used as primary key)

As u can see that under training user in Oracle Database the table vendor is created...

Stock table script:

create table stock (stcode number, stname varchar2(100), capacity varchar2(100), company varchar2(100), made varchar2(100), qty number, cost_price number, sal_price number, constraint pk_stock_stcode primary key(stcode));

Purchase table script:

create table purchase(purno number, pdate date, vid number, vname varchar2(100), amount number, constraint pk_purchase_purno primary key(purno), constraint fk_purchase_vender_vid foreign key (vid) references vendor(vid));

Purchase_Item scipt:

create table purchase_item(purno number, serialno number, stcode number, stname varchar2(100), capacity varchar2(100), company varchar2(100), made varchar2(100), rate number, qty number, amount number, constraint pk_pur_item_sno_purno_stcode primary key (purno, serialno, stcode), constraint fk_purchaseitem_purno foreign key (purno) references purchase(purno));

alter table purchase_item add constraint fk_stock_stcode foreign key (stcode) references stock(stcode);

Now if you notice the constrains i have used in above table script...

constraint pk_pur_item_sno_purno_stcode primary key (purno, serialno, stcode), constraint fk_purchaseitem_purno foreign key (purno) references purchase(purno)

let me define them for you..

The first one will make columns purno, sno and stcode a combine primary key which will record the reference data from Purchase table and 'Stock' table and will record the unique entries of data which can be multiple records...

alter table purchase_item add constraint fk_stock_stcode foreign key (stcode) references stock(stcode);

Above alter table command will add another constraint of foreign key to apply relation between stock and purchase_item table...

The effect of foreign key constraints are to validate the data you are trying to enter in your referred table... i.e. Whenever you will try to enter items to purchase_item table, on each row it will automatically make sure that the item you entered is already there in stock table, further it will be cleared to you in designing forms session...

Now come to form design...

First we will design stock and vendor form to record this initial data cause whenever we need to purchase or sale the items we will need to select a vendor as well as stock...

 When opening the Oracle Form Builder for the first time will open the dialogue box on the screen...

The data block wizard is used to import the table columns to canvas...

The table or view option is already selected so simply click 'Next'...

On above screen you will browse for the table to use columns on canvas...

Give your username password and service name of your Oracle Database...

We are creating form for vendor that is why we will select the vendor table...

Here you can see all the columns that you will select in the Data Block...

Select all the columns by clicking on >>...

Now it will ask about the create the canvas for placing your Data Block items where you will see the text fields and where you will enter the data or modify your data...

Click next to continue..

Simply click 'Next' if you want to continue with content style and not tab style...

Again select all columns or selective items that you want to show on canvas...

Set column width which can be set later also...

Select Form type of data or Tabular type of data entry...

Give frame name and number of records you want to display...

Simply click on Finish to see your finished canvas...

This is your canvas ready with details...

Now you need to run the form to complete the process of at least one complete form in working condition...
Here are the steps:

Once you press Ctrl+R the form will give above message and when you press 'Yes' it will ask for username password and service name again...

Give username and password as you have given to select the table columns when you were creating the form...

Here it is the final form running to accept the database records...

There are buttons on the top to insert the new record and save the records...
Click on icons to add and delete records...
Click on icon to save the record...
Click on icons to navigate the records...
Click onicons to execute the records when next time you open the form after saving the records...

Now the same way you can create other forms to enter and view your entries...

Please feel free to ask questions and make queries...