Global Temporary Tables

Applications often use some form of temporary data store for processes that are to complicated to complete in a single pass. Often, these temporary stores are defined as database tables or PL/SQL tables. In Oracle 8i, the maintenance and management of temporary tables can be delegated to the server by using Global Temporary Tables.

Creation of Global Temporary Tables

The data in a global temporary table is private, such that data inserted by a session can only be accessed by that session. The session-specific rows in a global temporary table can be preserved for the whole session, or just for the current transaction. The ON COMMIT DELETE ROWS clause indicates that the data should be deleted at the end of the transaction.

CREATE GLOBAL TEMPORARY TABLE my_temp_table (

  column1  NUMBER,

  column2  NUMBER

) ON COMMIT DELETE ROWS;

In contrast, the ON COMMIT PRESERVE ROWS clause indicates that rows should be preserved until the end of the session.

CREATE GLOBAL TEMPORARY TABLE my_temp_table (

  column1  NUMBER,

  column2  NUMBER

) ON COMMIT PRESERVE ROWS;

Miscellaneous Features

If the TRUNCATE statement is issued against a temporary table, only the session specific data is trucated. There is no affect on the data of other sessions.
Data in temporary tables is stored in temp segments in the temp tablespace.
Data in temporary tables is automatically deleted at the end of the database session, even if it ends abnormally.
Indexes can be created on temporary tables. The content of the index and the scope of the index is the same as the database session.
Views can be created against temporary tables and combinations of temporary and permanent tables.
Temporary tables can have triggers associated with them.
Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.
Statistics on temporary tables are common to all sessions. Oracle 12c allows session specific statistics.
There are a number of restrictions related to temporary tables but these are version specific.

Purchasing: PO Life Cycle

Online (Manual) PO:

Purchase Order Created in the Purchasing Module

Status: “Open”

PO Closed and Reconciled:

Closing a PO sets the status of the PO to “Complete” and signals that the PO is completely processed. Reconciling a PO releases any unused encumbrances associated with the PO. Agencies should perform this weekly: Status: “Complete”

PO Submitted for Approval:

Initial Status: “Pending Approval”

Resulting Status: “Approved”

Amount: ChartField:

Amt 1 (All POs) PO Budget Approver

Amt 2 (>$10,000)

PO Budget Checked:

Validates sufficient funds are available for the proposed purchase by coding string. If sufficient funds exist, an encumbrance is created for the PO.

Batch Frequency: 10am, 12pm, 2pm and once overnight.

Budget Status: “Valid”

Start

End

Invoice Processed by AP and Payment Issued to the Vendor:

The vouchers are created, approved and “Matched”. Additionally, the Pay Cycle Runs and payments are issued to the vendor.

Return to Vendor:

Optional – If items delivered by the vendor do not meet State expectations, the items can be returned to the vendor.

Order delivered by vendor and received in Core-CT:

Both Goods and Services are received and keyed into Core-CT.

Resulting Status: “Receiv ed”

PO Dispatched to the Vendor:

PO delivered to the vendor by fax, email, EDX, phone, or print method.

Status: “Dispatched”

Batch Frequency: 10am, 12pm, 2pm and once overnight.

· Purchase Order Types:-

1. Standard Purchase Orders

2. Blanket Purchase Agreements

3. Planned Purchase Orders

4. Contract Purchase Agreements

· There are 4 main tables associated with PO:-

1. PO_HEADERS_ALL

2. PO_LINES_ALL

3. PO_LINE_LOCATIONS_ALL

4. PO_DISTRIBUTIONS_ALL

· PO_HEADERS_ALL:

1. It contains information for your purchasing documents.

2. Oracle Purchasing uses this information to record information related to a complete document. The primary key is PO-HEADER_ID.

There are six types of documents that use PO_HEADERS_ALL

1. RFQs

2. Quotations

3. Standard purchase orders

4. Planned purchase orders

5. Blanket purchase orders

6. Contracts

Important data that are populated in this table are=>

· Buyer Information

· Supplier Information

· Brief Notes

· Terms and Conditions

· Status of the document

· PO_LINES_ALL:

1. Is a detail of headers table.

2. This table contain information of line number, the item number and category, unit, price, tax information, matching information, and quantity ordered for the line.

· PO_LINE_LOCATIONS_ALL:

1. This table contains information about purchase order shipment schedules and blanket agreement price breaks.

2. Important data that are populated in this table are=>

- Destination Type

- Requestor Id

- Quantity Ordered and Delivered to Destination.

· PO_DISTRIBUTIONS_ALL:

1. It contains accounting information for a purchase order shipment line.

2. Oracle Purchasing uses this information to record accounting and requisition information for purchase orders and releases

3. The primary key is PO_DISTRIBUTION_ID.

IMPORTANT COLUMNS OF PURCHASING TABLES

PO_HEADERS_ALL	PO_LINES_ALL	PO_LINE_LOCATIONS_ALL	PO_DISTRIBUTIONS_ALL
-PO_HEADER_ID -SEGMENT1 (PO NUMBER) -VENDOR_ID -VENDOR_SITE_ID -SHIP_TO_LOCATION_ID -CURRENCY_CODE -CLOSED CODE 1. OPEN 2. CLOSED 3. FINALLY 4. HOLD 5. FREEZE	-PO_LINE_ID -CATEGORY_ID -ITEM_ID -PO_HEADER_ID -QUANTITY -CLOSED_CODE 1. OPEN 2. CLOSED	-LINE_LOCATION_ID -SHIP_TO_LOCATION_ID -PO_HEADER_ID -PO_LINE_ID -QUANTITY_RECEIVED -QUANTITY_ACCEPTED -QUANTITY_REJECTED -CLOSED_CODE 1. OPEN 2. CLOSED FOR INVOICE 3. CLOSED FOR RECEIVING 4. FINALLY CLOSED	-PO_DISTRIBUTION_ID -PO_HEADER_ID -PO_LINE_ID -LINE_LOCATION_ID -PO_RELEASE_ID -DISTRIBUTION_NUM

· Interface Tables Used IN PO and importent columns:-

PO_HEADERS_INTERFACE	PO_LINES_INTERFACE	PO_LINE_LOCATIONS_INTERFACE	PO_DISTRIBUTIONS_INTERFACE
-INTERFACE_HEADER_ID -BATCH_ID -INTERFACE_SOURCE_CODE -PROCESS_CODE -ACTION -GROUP_CODE -ORG_ID -DOCUMENT_TYPE_CODE -DOCUMENT_NUM	-INTERFACE_LINE_ID -INTERFACE_HEADER_ID -ACTION	-INTERFACE_LINE_LOCATION_ID -INTERFACE_HEADER_ID -INTERFACE_LINE_ID -PROCESSING_ID	- INTERFACE_HEADER_ID - INTERFACE_LINE_ID -INTERFACE_DISTRIBUTI ON_ID

· Another Tables Used In PO:-

1. PO_VENDORS
-This table stores information about your suppliers.

-Oracle Purchasing uses this information to determine active suppliers.

-The primary key is VENDOR_ID.

2. PO_VENDOR_SITES_ALL
-This table stores information about supplier sites.

-Oracle Purchasing uses this information to store supplier address information.

- The primary key is VENDOR_SITE_ID.

3. PO_VENDOR_CONTACTS
-This table stores information about supplier site contacts.

-The primary key is VENDOR_CONTACT_ID

4. PO_REQUISITION_HEADERS_ALL
-This table stores information about requisition headers.

-Each row contains the requisition number, preparer status, and description.

-The primary key is REQUISITION_HEADER_ID.

5. PO_REQUISITION_LINES_ALL
-This table stores information about requisition lines.

-Each row contains the line number, item number, item category, item description, need-by date, deliver-to location, item quantities, units, prices, requestor, notes, and suggested supplier information for the requisition line.

-The primary key is REQUISITION_LINE_ID.

6. PO_REQ_DISTRIBUTIONS_ALL
-This Table stores information about the accounting distributions associated with each requisition line.

- Each requisition line must have at least one accounting distribution.

- Each row includes the Accounting Flex field ID and requisition line quantity.

- The primary key is DISTRIBUTION_ID.

only for Oracle Fresher

Sunday, 14 April 2013