Sunday 22 June 2014

What is ASN?

It is an advanced Shipment Notice which is transmitted via Electronic Data Interchange (EDI) from a supplier to let the receiving organization know that the shipment is arriving.
The ASN contains the shipment date, time, identification number, packing slip data, freight information etc.

How is move order generated?

When the order is pick released.

When is an item eligible for customer order?

When the attributes ‘Customer Ordered’ and ‘Customer order enabled’ are checked in the order management tab of Master Items.

Tuesday 10 June 2014

Difference between DELETE, TRUNCATE and DROP in PLSQL

DELETE Statement: 
DELETE is a DML command.
Can rollback in DELETE.
Triggers get fired.
Can use conditions (WHERE clause) in DELETE.
DELETE does not reset the High Water Mark for the table
This command deletes only the rows from the table based on the condition given in the where clause or deletes all the rows from the table if no condition is specified. But it does not free the space containing the table.


TRUNCATE statement: 
TRUNCATE is a DDL command.
TRUNCATE is much faster than DELETE.
Reason: When you type DELETE all the data get copied into the Rollback Tablespace first. Then delete operation get performed. Thatswhy when you type ROLLBACK after deleting a table, you can get back the data (The system get it for you from the Rollback Tablespace).All this process take time. But when you type TRUNCATE, it removes data directly without copying it into the Rollback Tablespace. Thatswhy TRUNCATE is faster. Once you truncate you can’t get back the data.
Cannot rollback in TRUNCATE. TRUNCATE removes the record permanently.
Trigger doesn't get fired
Cannot use conditions (WHERE clause) in TRUNCATE.
TRUNCATE command resets the High Water Mark for the table
This command is used to delete all the rows from the table and free the space containing the table.
When a table is truncated all the references to the table will be valid.

DROP Statement: 
DROP is a DDL command.
No DML triggers will be fired.
The DROP command is used to remove an object from the data dictionary. If you drop a table, all the rows in the table is deleted and the table structure is removed from the database. Once a table is dropped we cannot get it back, so be careful while using DROP command. When a table is dropped all the references to the table will not be valid.
If a table is dropped, all the relationships with other tables will no longer be valid, the integrity constraints will be dropped, grant or access privileges on the table will also be dropped, if want use the table again it has to be recreated with the integrity constraints, access privileges and the relationships with other tables should be established again. 
We cannot recover the table before Oracle 10g. But Oracle 10g provide the command to recover it by using the command (FLASHBACK)

Thursday 5 June 2014

GL Period Delete query

delete from gl_period_statuses where start_date >= '31-JAN-2014' and closing_status in ('F','N');

delete from gl_periods where period_set_name ='Global' and start_date >= '31-JAN-2014'
and period_type ='21' ;

delete from pa_periods_all where org_id in (111,222)
and start_date >= '31-JAN-2014' and status='N';