Puchasing and Payables Module: Technical Details

PO_VENDORS
This table stores information about your suppliers. Oracle Purchasing uses this information to determine active suppliers. The primary key is VENDOR_ID.

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.

PO_VENDOR_CONTACTS
This table stores information about supplier site contacts. The primary key is VENDOR_CONTACT_ID.

PO_REQUISITION_HEADERS_ALL
PO_REQUISITION_HEADERS_ALL stores information about requisition headers. Each row contains the requisition number, preparer status, and description. It is one of three tables that stores requisition information. The primary key is REQUISITION_HEADER_ID.

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. This table is one of three tables that stores requisition information. The primary key is REQUISITION_LINE_ID.

PO_REQ_DISTRIBUTIONS_ALL
PO_REQ_DISTRIBUTIONS_ALL 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. This table is one of three tables that stores requisition information. The primary key is DISTRIBUTION_ID.

PO_HEADERS_ALL
PO_HEADERS_ALL contains information for your purchasing documents. Each row contains buyer information, supplier information, notes, foreign currency information, terms and conditions information, and the document status. Oracle Purchasing uses this information to record information related to a complete document. The primary key is PO-HEADER_ID.

PO_LINES_ALL
PO_LINES_ALL stores current information about each purchase order line. You need one row for each line you attach to a document. Each row includes the line number, item number and category unit, price, tax information, and quantity ordered for the line. Oracle Purchasing uses this information to record and update item and price information for purchase orders, quotations, and RFQs. The primary key is PO_LINE_ID.

PO_LINE_LOCATIONS_ALL
This table contains information about purchase order shipment schedules and blanket agreement price breaks. You must have one row for each schedule or price break you attach to a document line. Each row contains the location, quantity, and dates for each shipment schedule. Oracle Purchasing uses this information to record delivery schedule information for purchase orders and price break information for blanket purchase orders, quotations, and RFQs. The primary key is LINE_LOCATION_ID.

PO_DISTRIBUTIONS_ALL
PO_DISTRIBUTIONS_ALL contains accounting information for a purchase order shipment line. Oracle Purchasing uses this information to record accounting and requisition information for purchase orders and releases. It is one of five tables that stores purchase orders and releases. The primary key is PO_DISTRIBUTION_ID.

RCV_SHIPMENT_HEADERS
This table stores common information about the source of your receipts or expected receipts. You group your receipts by the source type and the source of the receipt. Oracle Purchasing does not allow you to group receipts from different sources under one receipt header. The primary key is SHIPMENT_HEADER_ID.

RCV_SHIPMENT_LINES
This table stores information about items that have been shipped or received from a specific receipt source. This table also stores information about the default destination for in-transit shipments. The primary key is SHIPMENT_LINE_ID.

RCV_TRANSACTIONS
This table stores historical information about receiving transactions that you have performed. When you enter a receiving transaction and the receiving transaction processor processes your transaction, the transaction is recorded in this table.
Once a row has been inserted into this table, it will never be updated. When you correct a transaction, the net transaction quantity is maintained in RCV_SUPPLY. The original transaction does not get updated. You can only delete rows from this table using the Purge feature of Oracle Purchasing. The primary key is TRANSACTION_ID.

PO_RELEASES_ALL
This table contains information about blanket and planned purchase order releases. You need one row for each release you issue for a blanket or planned purchase order. Each row includes the buyer, date, release status, and release number. Each release must have at least one purchase order shipment (PO_LINE_LOCATIONS_ALL). The primary key is PO_RELEASE_ID.

AP_INVOICES_ALL
This table contains records for supplier invoices and has one row for each invoice.
To pass Invoice Validation, the INVOICE_AMOUNT in the AP_INVOICES_ALL table must equal the sum of the AMOUNT columns in the AP_DISTRIBUTIONS_ALL table. The primary key is INVOICE_ID.

AP_INVOICE_DISTRIBUTIONS_ALL
This table holds the distribution line information.
There is a row for each invoice distribution. If matching is used, distribution information is copied from PO_DISTRIBUTIONS_ALL during the matching process.

AP_PAYMENT_SCHEDULES_ALL
This table contains information about scheduled payments for an invoice.
Oracle Payables uses this information to determine when to make payments on an invoice and how much to pay in an automatic payment batch.

AP_HOLDS_ALL
This table contains a record for each hold placed on an invoice.
An invoice might have multiple records in this table.
An invoice cannot be paid until all holds placed on it have been released.

AP_CHECKS_ALL
This table stores information about payments issued to suppliers.
Each row includes the supplier name, address, and bank account name for auditing purposes, in case any of them change after you make the payment.
If the payment is electronic, the supplier bank account information is stored in the payment record.

AP_INVOICE_PAYMENTS_ALL
This table provides the link between a payment (CHECK_ID) and the invoice(s) paid by that payment.
This table contains records of invoice payments that you make to suppliers. Oracle Payables updates this table when you confirm an automatic payment batch, enter a manual payment, or process a Quick Payment. This table contains one row for each payment made for each invoice.
Void payments are also entered into this table as a negative record of the original payment line.

AP_BANK_ACCOUNTS_ALL
This table stores information about your bank accounts and your suppliers’ and customers’ bank accounts. This table contains one row for every bank account, whether it is an internal (supplier) or external (customer) account.

AP_BANK_BRANCHES
This table stores information about the bank branches and details about the branches.



Ur's
Amar Alam

Types of Invoices in Oracle Payables

Types of Invoices in AP:

The different types of invoices available in Payables are:

1. Standard Invoices: Standard invoices are the invoices issued by a supplier to the buyer, representing the amount due for the products or services the supplier has provided to the buyer.

Standard invoices can be either matched to a purchase order or not matched.

A standard invoice must be positive amount.

2. Mixed Invoices: Mixed invoices are the invoices which can have either positive or negative amounts and can be matched to both purchase orders and invoices.

For example, if there is a mixed invoice for $-1000, you can either match it to an invoice with $-1000 or to a purchase order with an amount $1000.

3. Credit Memo: Credit memo is an invoice raised by the supplier to the buyer with negative amount. It reduces the supplier balance and reduces the liability.

For example the customer has returned some of the goods that he purchased, the supplier sends a credit memo to the buyer to adjust the balance.

4. Debit Memo: Debit memo is an invoice raised by the customer to supplier with negative amount.

The functionality of Debit Memo is same as Credit Memo. Both are to reduce the liability.

The purpose of Debit Memos is to record a credit for a supplier who does not send you a credit memo.

Unlike in AR, both Credit memo and Debit memo are with negative signs in Payables.

5. Prepayment: Prepayments are the invoices raised to record advance payments to a supplier or employee.

6. Expense Reports: Expense reports are the invoices that represent amount due to an employee for all his business related expenses.

7. Retainage Release Invoices: Retainage release is the act of releasing, or paying, that portion of a payment that was withheld until a substantial portion or all of the service procurement work is completed. The amounts retained during the life of the contract must be released and paid to the supplier or sub-contractor once all or a substantial portion of the work is completed.

Oracle Payables uses the Retainage Release Request to create a type of invoice called Retainage Release. A retainage release invoice has lines, which are copied from the original standard progress invoices, which show an amount left to be released.

Retainage release invoices can only be entered manually in the Invoice Workbench window.


8. Withholding Tax:  After you apply withholding tax to an invoice, you can optionally create invoices to remit withheld tax to the tax authority.
                         
Payables can automatically create withholding tax invoices, or you can perform this
task manually. If you choose to automatically create withholding tax invoices, you must choose whether to do this during Invoice Validation or during payment processing.


9. PO Price Adjustment Invoices:  PO Price Adjustment Invoices are used for recording the difference in price between the original invoice and the new purchase order price.

For example, If a supplier sends an invoice for a change in unit price for an invoice you have matched to a purchase order, PO Price Adjustment Invoices can be used to adjust the invoiced unit price of previously matched purchase order shipments or distributions without adjusting the quantity billed.


PO price adjustment invoices can be matched to both purchase orders and invoices.

10. Quick invoices: Used for quick, high-volume invoice entry for invoices that do not require extensive validation and defaults. After entry, you import these into the Payables system. Validation and defaulting occur during import


Ur's
Amar Alam

FriendShip

sql>> SELECT UPPER( 'happy friendship day to all') friends FROM DUAL;

FRIENDS
--------------------------------------
HAPPY FRIENDSHIP DAY TO ALL

THANKS
AMAR ALAM

Logical SQL Query

SQL> SELECT SYS_CONNECT_BY_PATH(LEVEL,' ') "TRIANGLE SHAPE"
  2  FROM DUAL
  3  CONNECT BY LEVEL<=10
  4  ;

TRIANGLE SHAPE
------------------------------------------------------------
 1
 1 2
 1 2 3
 1 2 3 4
 1 2 3 4 5
 1 2 3 4 5 6
 1 2 3 4 5 6 7
 1 2 3 4 5 6 7 8
 1 2 3 4 5 6 7 8 9
 1 2 3 4 5 6 7 8 9 10

10 rows selected.

SQL>


Ur's
AmarAlam

Diff b/w on delete cascade and on delete set null..

We use the ON DELETE CASCADE clause with a FOREIGN KEY constraint to specify that 
when a row in the parent table is deleted, any matching rows in the child table are also deleted.

You use the ON DELETE SET NULL clause with a FOREIGN KEY constraint to specify that
when a row in the parent table is deleted, the foreign key column for the row (or rows) in the
child table is set to null.


Ur's
AmarAlam

example of varray in oracle pl sql

declare
Type varray_demo is varray(30) of emp.ename%type;
demovarray varray_demo;
begin
demovarray:=varray_demo('hari','raju','nari','madhu','lalitha');
end;

create type address is varray(30) of varchar2(40);
create type location is varray(30) of varchar2(40);
create type Dname is varray(30) of varchar2(40);

alter table emp add address address;
alter table emp add Loc location;
alter table emp add Dname Dname;

insert into emp values(110,'user1',15000,20,20,address('sargar society','roadno 2','Banjarahills'),location('HYD','Vijag'),Dname('accounts','software'));

select * from emp where 'software' not in (select * from table(emp.dname))
select * from emp where 'software' in (select * from table(emp.dname))

update emp
set address=address('NRP','roadno 2','KPHB')
where deptno=40

drop type address
drop type location
drop type Dname

Varrays:
=========

 The Varray is short for Variable Array. A Varray stores elements of the same type in the order
 in which they are added. The number of elements in a Varray must be known at the time of its declaration.
 In other words, a Varray has a fixed lower and upper bounds, making it most similar to collection types
 from other programming languages. Once it is created and populated, each element can be accessed
 by a numeric index.



DECLARE
    TYPE genres IS VARRAY(4) OF book_genre.genre_name%TYPE;
    Fiction_genres genres;
BEGIN
    fiction_genres := genres('MYSTERY','SUSPENSE', 'ROMANCE','HORROR');
END;


The following code sample demonstrates how to use a few of these methods.
We are using a Varray in the example, but the methods function similarly on all collection types.
 We mentioned that a Varray differs from Nested Tables and Associative Arrays in that you must supply a size during its declaration.
This example usese the EXTENDS method to demonstrate that it is possible to modify a Varray's size programmatically.

--Add a new genre.
IF adding_new_genre  THEN

     --Is this genre id already in the collection?
     IF NOT fiction_genres.EXISTS(v_genre_id)     THEN
       --**Add** another element to the varray.
       fiction_genres.EXTENDS(1);
       fiction_genres(v_genre_id) := v_genre;
   END IF;
    --Display the total # of elements.
    DBMS_OUTPUT.PUT_LINE('Total # of entries in fiction_genres is :
                         '||fiction_genres.COUNT();

END IF;
...
...
--Remove all entries.
IF deleting_all_genres THEN
     Fiction_genres.DELETE();
END IF;



The advantage that Varrays (and Nested Tables) have over Associative Arrays is their ability to be added to the database.
 For example, you could add the genres type, a Varray, to a DML statement on the library table.

CREATE TYPE genres IS VARRAY(4) OF book_genre.genre_name%TYPE;
/
CREATE TABLE book_library (
    library_id    NUMBER,
    name          VARCHAR2(30),
    book_genres   genres);
/


When a new library record is added, we can supply values to our genres type, book_genres, by using its constructor:


--Insert a new collection into the column on our book_library table.
INSERT INTO book_library (library_id, name, book_genres)
  VALUES (book_library_seq.NEXTVAL,'Brand New Library',
          Genres('FICTION','NON-FICTION', 'HISTORY',
                 'BUSINESS AND FINANCE'));


SELECT name, book_genres from book_library;



Ur's
AmarAlam

associative array in oracle

Associative Array:

Earlier, we reviewed the definition of a PL/SQL Table (also know as an index-by table). The statement

TYPE book_title_tab IS TABLE OF book.title%TYPE
    INDEX BY BINARY_INTEGER;
book_titles   book_title_tab;


defines a collection of book titles, accessible by a numeric index. Although it is feasible to
locate an element by its numeric index, the limitation to this approach is that the value
 we have to search by is often not an integer.


SELECT title FROM book;


Above are values from the title column of the book table. If we needed to remove an entry,
given only the book title,we would have to search the entire collection in a somewhat inefficient manner.
The following is code illustrates this:


FOR cursor_column IN  book_titles.FIRST..book_titles.LAST LOOP
       IF book_titles(cursor_column) = 'A Farewell to Arms' THEN
          book_titles.DELETE(cursor_column);
       END IF;
   END LOOP;


With Associative Arrays, it is now possible to index by the title of the book.
In fact, there are numerous different indexing options, including by VARCHAR2,
 using the %TYPE keyword, and more. This is a improvement over indexing everything by an integer
 then having to shuffle through entries to find what you're looking for.
 Now, if we want to remove the book A Farewell to Arms, we can use an Associative Array:


DECLARE
   TYPE book_title_tab IS TABLE OF book.title%TYPE
       INDEX BY book.title%TYPE;
   book_titles book_title_tab;
BEGIN
      book_titles.DELETE('A Farewell to Arms');
END;



Ur's
AmarAlam